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
| MERGE INTO intervention c
USING (
SELECT DISTINCT
s.C_INTER col1,
s.C_OBJET col2,
s.C_UTIL_DEM col3,
decode(s.C_SOCIETE,NULL,'(VIDE)',s.C_SOCIETE) col4,
decode(s.DE_DEMINTER,NULL,'(VIDE)',s.DE_DEMINTER) col5,
decode(s.DE_RESOINTER,NULL,'(VIDE)',s.DE_RESOINTER) col6,
s.D_DEMINTER col7,
s.D_PCHARGE col8,
s.MTU_INTER col9,
SI.N_SITE_1 col10,
SI.N_SITE_2 col11,
SI.N_SITE_3 col12,
decode(SE.N_SERVICE_1,NULL,'(VIDE)',SE.N_SERVICE_1) col13,
decode(SE.N_SERVICE_2,NULL,'(VIDE)',SE.N_SERVICE_2) col14,
decode(s.L_INTER,NULL,'(VIDE)',s.L_INTER) col15
FROM intervention@isiparc s,qry_histmvtobj@isiparc HD,qry_histmvtobj@isiparc HF,imp_service_annuaire@isiparc SE,imp_site_annuaire@isiparc SI,objet@isiparc O
WHERE s.C_OBJET=HD.C_OBJET(+)
AND s.C_OBJET=O.C_OBJET(+)
AND decode(HD.C_SERVICE_NEW,NULL,O.C_SERVICE,HD.C_SERVICE_NEW)=SE.C_SERVICE_ORI
AND decode(HD.C_SITE_NEW,NULL,O.C_SITE,HD.C_SITE_NEW)=SI.C_SITE_ORI
AND HD.C_HISTMVT_NEW=HF.C_HISTMVTOBJ(+)
AND s.D_DEMINTER >= HD.D_HIST
AND s.D_DEMINTER <= decode(HF.D_HIST,NULL,CURRENT_DATE,HF.D_HIST)
) t
ON (c.id_inter = t.col1)
WHEN MATCHED THEN
UPDATE SET c.id_obj = t.col2
, c.id_util = t.col3
, c.nom_prestataire = t.col4
, c.libelle_panne = t.col5
, c.libelle_inter = t.col6
, c.date_panne = t.col7
, c.date_inter = t.col8
, c.montant_inter = t.col9
, c.nom_site_inter_niv1 = t.col10
, c.nom_site_inter_niv2 = t.col11
, c.nom_site_inter_niv3 = t.col12
, c.nom_service_inter_niv1 = t.col13
, c.nom_service_inter_niv2 = t.col14
, c.type_panne = t.col15
WHERE c.id_obj != t.col2 or (c.id_obj is null and t.col2 is not null) or (c.id_obj is not null and t.col2 is null)
or c.id_util != t.col3 or (c.id_util is null and t.col3 is not null) or (c.id_util is not null and t.col3 is null)
or c.nom_prestataire != t.col4 or (c.nom_prestataire is null and t.col4 is not null) or (c.nom_prestataire is not null and t.col4 is null)
or c.libelle_panne != t.col5 or (c.libelle_panne is null and t.col5 is not null) or (c.libelle_panne is not null and t.col5 is null)
or c.libelle_inter != t.col6 or (c.libelle_inter is null and t.col6 is not null) or (c.libelle_inter is not null and t.col6 is null)
or c.date_panne != t.col7 or (c.date_panne is null and t.col7 is not null) or (c.date_panne is not null and t.col7 is null)
or c.date_inter != t.col8 or (c.date_inter is null and t.col8 is not null) or (c.date_inter is not null and t.col8 is null)
or c.montant_inter != t.col9 or (c.montant_inter is null and t.col9 is not null) or (c.montant_inter is not null and t.col9 is null)
or c.nom_site_inter_niv1 != t.col10 or (c.nom_site_inter_niv1 is null and t.col10 is not null) or (c.nom_site_inter_niv1 is not null and t.col10 is null)
or c.nom_site_inter_niv2 != t.col11 or (c.nom_site_inter_niv2 is null and t.col11 is not null) or (c.nom_site_inter_niv2 is not null and t.col11 is null)
or c.nom_site_inter_niv3 != t.col12 or (c.nom_site_inter_niv3 is null and t.col12 is not null) or (c.nom_site_inter_niv3 is not null and t.col12 is null)
or c.nom_service_inter_niv1 != t.col13 or (c.nom_service_inter_niv1 is null and t.col13 is not null) or (c.nom_service_inter_niv1 is not null and t.col13 is null)
or c.nom_service_inter_niv2 != t.col14 or (c.nom_service_inter_niv2 is null and t.col14 is not null) or (c.nom_service_inter_niv2 is not null and t.col14 is null)
or c.type_panne != t.col15 or (c.type_panne is null and t.col15 is not null) or (c.type_panne is not null and t.col15 is null)
WHEN NOT MATCHED THEN
INSERT (c.id_inter,c.id_obj,c.id_util,c.nom_prestataire,c.libelle_panne,c.libelle_inter,c.date_panne,c.date_inter,c.montant_inter,c.nom_site_inter_niv1,c.nom_site_inter_niv2,c.nom_site_inter_niv3,c.nom_service_inter_niv1,c.nom_service_inter_niv2,c.type_panne)
VALUES (t.col1,t.col2,t.col3,t.col4,t.col5,t.col6,t.col7,t.col8,t.col9,t.col10,t.col11,t.col12,t.col13,t.col14,t.col15)
; |
Partager