1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139
|
SELECT
P.cd_env AS cd_env
,P.no_partition AS no_partition
,P.no_jeu_donnees AS no_jeu_donnees
,P.dt_arrete AS dt_arrete
,P.lb_env AS lb_env
,P.lb_desc AS lb_desc
,P.cd_pge AS cd_pge
,IP.lb_pge AS lb_pge
,P.tp_gel AS tp_gel
,TG.lb_tp_gel AS lb_tp_gel
,P.ind_tracker_niv0 AS ind_tracker_niv0
,P.ind_tracker_niv123 AS ind_tracker_niv123
,P.no_lst_flux_mod_init AS no_lst_flux_mod_init
,P.no_lst_flux_mod_fin AS no_lst_flux_mod_fin
,PE.no_stock_fem AS no_stock_fem
,P.cd_traitement AS cd_traitement
,IT.lb_traitement AS lb_traitement
,P.no_chainage AS no_chainage
,P.no_traitement AS no_traitement
,P.no_batch AS no_batch
,P.no_lot AS no_lot
,P.cd_perim_gen AS cd_perim_gen
,' ' AS lb_perim_gen
,P.cd_perim_spec AS cd_perim_spec
,' ' AS lb_type_perim_spec
,' ' AS lb_perim_spec
,P.lb_commentaire AS lb_commentaire
,P.dt_debut_trait AS dt_debut_trait
,P.dt_fin_trait AS dt_fin_trait
,LPAD(TRUNC(P.dt_fin_trait - P.dt_debut_trait), 8, 0)
||' '
||LPAD(TRUNC(MOD((P.dt_fin_trait - P.dt_debut_trait) * 24, 24)), 2, 0)
||':'
||LPAD(TRUNC(MOD((P.dt_fin_trait - P.dt_debut_trait) * 24 * 60, 60)), 2, 0)
||':'
||LPAD(TRUNC(MOD((P.dt_fin_trait - P.dt_debut_trait) * 24 * 60 * 60, 60)), 2, 0)
||'' AS dt_duree
,P.cd_statut_trait AS cd_statut_trait
,ST.lb_statut_trait AS lb_statut_trait
,P.nb_flux AS nb_flux
,P.nb_flux_ok AS nb_flux_ok
,P.nb_flux_ko AS nb_flux_ko
,P.cd_user AS cd_user
,NVL2(IU.lb_nom, IU.lb_prenom
|| ' '
|| IU.lb_nom, P.cd_user) AS lb_user
,P.cd_user_select AS cd_user_select
,P.ind_histo AS ind_histo
,DECODE(P.no_lst_flux_mod_init
,P.no_lst_flux_mod_fin
-- Si la liste n'a pas changé, l'indicateur est valorisé à 0
,0
-- Sinon, l'indicatezur est valorisé à 1
,1) AS ind_mod_lst_flux
FROM
(
SELECT PG.cd_env
,PG.no_partition
,PG.no_jeu_donnees
,PG.dt_arrete
,PG.lb_env
,PG.lb_desc
,PG.cd_pge
,PG.tp_gel
,PG.ind_tracker_niv0
,PG.ind_tracker_niv123
,PG.no_lst_flux_mod_init
,PG.no_lst_flux_mod_fin
,PG.cd_traitement
,PG.no_chainage
,PG.no_traitement
,PG.no_batch
,PG.no_lot
,PG.cd_perim_gen
,PG.cd_perim_spec
,PG.lb_commentaire
,PG.dt_debut_trait
,PG.dt_fin_trait
,PG.cd_statut_trait
,PG.nb_flux
,PG.nb_flux_ok
,PG.nb_flux_ko
,PG.cd_user
,PG.ind_histo
,RANK() OVER(PARTITION BY SEL.cd_user_select,PG.no_partition
,PG.cd_traitement
ORDER BY PG.dt_debut_trait DESC) AS ind_rang
,SEL.cd_user_select
,SEL.id_type_trt_select
,SEL.ind_dernier_select
FROM
PG
INNER JOIN
SEL
ON (TRUNC(PG.dt_debut_trait) BETWEEN TRUNC(SEL.dt_debut_select) AND TRUNC(SEL.dt_fin_select))
WHERE 0 = 0
-- Si l'indicateur des traitements chaînés est coché dans l'IHM, on limite aux enregistrements ayant un no_chainage
AND (NVL(PG.no_chainage,NVL(SEL.ind_chaine_select,0)) = 0
OR PG.no_chainage IS NOT NULL)
-- Si une PA historisée a été sélectionnée dans l'IHM, on ne prend que les lignes dont le cd_env correspond
AND SEL.cd_env_select IS NULL
OR (SEL.cd_env_select IS NOT NULL
AND PG.ind_histo = 1
AND PG.cd_env = SEL.cd_env_select)
-- Transcodification du code statut du traitement pour correspondre à ceux sélectionnés
AND SEL.cd_statut_trait_select IS NULL
OR (DECODE(PG.cd_statut_trait
-- Si le statut est "OK", code statut correspondant à "OK" pour l'IHM PAT
,2,'2'
-- Si le statut est "KO", code statut correspondant à "KO" pour l'IHM PAT
,3,'3'
-- Ai le statut est "En cours", code statut correspondant à "En cours" pour l'IHM PAT
,8,'1'
-- Sinon, code statut "Autre" pour l'IHM PAT
,'4') = SEL.cd_statut_trait_select)
) P
INNER JOIN propriete_env PE
ON (P.no_partition = PE.no_partition
AND P.cd_env = PE.cd_env)
INNER JOIN ihm_pge IP
ON NVL(P.cd_pge,0) = IP.cd_pge
INNER JOIN type_gel TG
ON NVL(P.tp_gel,0) = TG.tp_gel
INNER JOIN (SELECT DISTINCT
cd_traitement
,lb_traitement
,id_type_trt
FROM info_traitement) IT
ON (P.cd_traitement = IT.cd_traitement)
LEFT JOIN ihm_user IU
ON (P.cd_user = IU.cd_user)
LEFT JOIN ihm_pat_statut_trait ST
ON (P.cd_statut_trait = ST.cd_statut_trait)
WHERE 0 = 0
-- Sélection des enregistrements de rang = 1 si la case des derniers traitements est cochée dans l'IHM
AND DECODE(P.ind_dernier_select, 0, 1, P.ind_rang) = 1
; |
Partager