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
| SELECT DISTINCT
b_ope.ope_code14 code_14,
b_ope.ope_nomgpnoeud nom,
b_ope.ope_typeoperation type_operation_bodega ,
b_ope.ope_etatoperation etat_bodega
FROM BOD_SIM_OPERATION b_ope
INNER JOIN BOD_SIM_CANDIDAT b_can ON b_ope.ope_id = bod_sim_candidat.ope_id
WHERE b_ope.code_ur = 'PA'
AND NOT EXISTS ( SELECT 1
FROM ADO_T_DR dr,
ADO_T_BASE_PGM pgm,
BDE_SIM_BASE_DR sim
WHERE dr.n = pgm.site_dr
AND sim.urm_code = 'PA'
AND dr.libelle_dr = sim.dr_code
AND pgm.site_etat <> 'ANNULE'
AND pgm.PURGE = 0
AND pgm.site_code_pg = bod_sim_operation.ope_code14
)
AND (
EXISTS (
SELECT 1
FROM BOD_SIM_OPERATION ope, BOD_SIM_CANDIDAT can
WHERE ope.ope_typeoperation IN ('Aménagement BTS', 'Réaménagement BTS','Aménagement MIXTE', 'Réaménagement MIXTE')
AND ope.ope_etatoperation IN ('EN COURS', 'GELEE')
AND ope.code_ur = 'PA'
AND ope.ope_id = can.ope_id
AND can.can_ranking = 'Principal'
AND (
(can.recetteconstructeur_900_r IS NOT NULL AND can.recetteconstructeur_900_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
OR (can.recetteconstructeur_1800_r IS NOT NULL AND can.recetteconstructeur_1800_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
OR (can.recetteconstructeur_900_r IS NULL AND can.recetteconstructeur_1800_r IS NULL
AND can.editionfn73_900_r IS NULL AND can.editionfn73_1800_r IS NULL )
)
AND ope.ope_code14 = b_ope.ope_code14
)
OR EXISTS (
SELECT 1
FROM BOD_SIM_OPERATION ope, BOD_SIM_CANDIDAT can
WHERE ope.ope_typeoperation IN ('Aménagement UMTS', 'Réaménagement UMTS','Aménagement MIXTE', 'Réaménagement MIXTE')
AND ope.ope_etatoperation IN ('EN COURS', 'GELEE')
AND ope.code_ur = 'PA'
AND ope.ope_id = can.ope_id
AND can.can_ranking = 'Principal'
AND (
(can.recetteconstructeur_umts_r IS NOT NULL AND can.recetteconstructeur_umts_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
OR (can.recetteconstructeur_umts_r IS NULL AND can.editionfn73_umts_r IS NULL )
)
AND ope.ope_code14 = b_ope.ope_code14
)
OR EXISTS (
SELECT 1
FROM BOD_SIM_OPERATION ope, BOD_SIM_CANDIDAT can
WHERE ope.ope_typeoperation IN ('Répéteurs UMTS', 'Répéteurs GSM')
AND ope.ope_etatoperation IN ('EN COURS', 'GELEE')
AND ope.ope_id = can.ope_id
AND BOD_SIM_CANDIDAT.can_ranking = 'Principal'
AND ope.code_ur = 'PA'
AND ope.ope_fn1 > '01/12/2005'
AND ope.ope_code14 = b_ope.ope_code14
)
)
ORDER BY b_ope.ope_nomgpnoeud; |
Partager