vue lot ope

Retour à Recette SQL

CREATE OR REPLACE VIEW migado.vue_lot_ope AS 
 SELECT t_operation_ope.ope_identifiant, t_lot_lot.lot_identifiant, t_operation_ope.ope_dic_identifiant, t_lot_lot.lot_lot_identifiant AS lot_pere, t_operation_ope.ope_date_debut, t_operation_ope.ope_date_fin, t_lot_lot.lot_effectif, t_lot_lot.lot_quantite, t_lot_lot.lot_tax_code, t_lot_lot.lot_std_code, tr_taxon_tax.tax_nom_latin, tr_stadedeveloppement_std.std_libelle, tr_devenirlot_dev.dev_code, tr_devenirlot_dev.dev_libelle
   FROM migado.t_operation_ope
   JOIN migado.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
   LEFT JOIN ref.tr_typequantitelot_qte ON tr_typequantitelot_qte.qte_code::text = t_lot_lot.lot_qte_code::text
   LEFT JOIN ref.tr_devenirlot_dev ON tr_devenirlot_dev.dev_code::text = t_lot_lot.lot_dev_code::text
   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = t_lot_lot.lot_tax_code::text
   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = t_lot_lot.lot_std_code::text
  ORDER BY t_operation_ope.ope_date_debut;

-- DROP VIEW azti.vue_lot_ope_car;

CREATE OR REPLACE VIEW azti.vue_lot_ope_car AS 
 SELECT t_operation_ope.ope_identifiant, t_lot_lot.lot_identifiant, t_operation_ope.ope_dic_identifiant, t_lot_lot.lot_lot_identifiant AS lot_pere, t_operation_ope.ope_date_debut, t_operation_ope.ope_date_fin, t_lot_lot.lot_effectif, t_lot_lot.lot_quantite, t_lot_lot.lot_tax_code, t_lot_lot.lot_std_code, tr_taxon_tax.tax_nom_latin, tr_stadedeveloppement_std.std_libelle, tr_devenirlot_dev.dev_code, tr_devenirlot_dev.dev_libelle, tg_parametre_par.par_nom, tj_caracteristiquelot_car.car_par_code, tj_caracteristiquelot_car.car_methode_obtention, tj_caracteristiquelot_car.car_val_identifiant, tj_caracteristiquelot_car.car_valeur_quantitatif, tr_valeurparametrequalitatif_val.val_libelle
   FROM azti.t_operation_ope
   JOIN azti.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
   LEFT JOIN ref.tr_typequantitelot_qte ON tr_typequantitelot_qte.qte_code::text = t_lot_lot.lot_qte_code::text
   LEFT JOIN ref.tr_devenirlot_dev ON tr_devenirlot_dev.dev_code::text = t_lot_lot.lot_dev_code::text
   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = t_lot_lot.lot_tax_code::text
   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = t_lot_lot.lot_std_code::text
   JOIN azti.tj_caracteristiquelot_car ON tj_caracteristiquelot_car.car_lot_identifiant = t_lot_lot.lot_identifiant
   LEFT JOIN ref.tg_parametre_par ON tj_caracteristiquelot_car.car_par_code::text = tg_parametre_par.par_code::text
   LEFT JOIN ref.tr_parametrequalitatif_qal ON tr_parametrequalitatif_qal.qal_par_code::text = tg_parametre_par.par_code::text
   LEFT JOIN ref.tr_valeurparametrequalitatif_val ON tj_caracteristiquelot_car.car_val_identifiant = tr_valeurparametrequalitatif_val.val_identifiant
  ORDER BY t_operation_ope.ope_date_debut;

ALTER TABLE azti.vue_lot_ope_car
  OWNER TO postgres;
GRANT ALL ON TABLE azti.vue_lot_ope_car TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE azti.vue_lot_ope_car TO azti;
GRANT SELECT ON TABLE azti.vue_lot_ope_car TO invite;