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
| SELECT distinct
sites.cell_denomination as site,
ENSEIGNES.ens_libelle as enseigne,
SECTEURS_ACTIVITES.sect_act_libelle as secteur,
CONTRATS.cont_pk as contrat,
NATURES_CONTRATS.nat_cont_libelle as nat_cont,
round(contrats_cell_surf.sup_loc) as sup_loc,
nvl(contrats_ca_liens2.liens,0) as lien,
pack_turnover_keys.dev_get_rem(3,2007,CONTRATS.cont_PK) as rem,
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007,CONTRATS.cont_pk)) as ca_1,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007,CONTRATS.cont_pk)) as ca_1b,
(NVL(Pack_Loyer.dev_get_ca(3-2,2007,CONTRATS.cont_pk),0)) as ca_1c,
round(dev_get_ca_test(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007,CONTRATS.cont_pk)) as ca_1_test,
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3-1,2007,CONTRATS.cont_pk)) as ca_2,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3-1,2007,CONTRATS.cont_pk)) as ca_2b,
(NVL(Pack_Loyer.dev_get_ca(3-1,2007,CONTRATS.cont_pk),0)) as ca_2c,
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3,2007,CONTRATS.cont_pk)) as ca_3,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3,2007,CONTRATS.cont_pk)) as ca_3b,
(NVL(Pack_Loyer.dev_get_ca(3,2007,CONTRATS.cont_pk),0)) as ca_3c,
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007-1,CONTRATS.cont_pk)) as ca_11,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007-1,CONTRATS.cont_pk)) as ca_11b,
(NVL(Pack_Loyer.dev_get_ca(3-2,2007-1,CONTRATS.cont_pk),0)) as ca_11c,
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3-1,2007-1,CONTRATS.cont_pk)) as ca_22,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3-1,2007-1,CONTRATS.cont_pk)) as ca_22b,
(NVL(Pack_Loyer.dev_get_ca(3-1,2007-1,CONTRATS.cont_pk),0)) as ca_22c,
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3,2007-1,CONTRATS.cont_pk)) as ca_33,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3,2007-1,CONTRATS.cont_pk)) as ca_33b,
(NVL(Pack_Loyer.dev_get_ca(3,2007-1,CONTRATS.cont_pk),0)) as ca_33c,
nvl(pack_turnover_trim.dev_get_ca_Q(nvl(contrats_ca_liens2.liens,0),2007,3,CONTRATS.cont_PK),0) as Q,
nvl(pack_turnover_trim.dev_get_ca_Q(nvl(contrats_ca_liens2.liens,0),2007-1,3,CONTRATS.cont_PK),0) as Qprev,
nvl((PACK_PROPRIO.dev_get_cumul121(nvl(contrats_ca_liens2.liens,0),3,2007,CONTRATS.cont_PK)),0) AS cumul12,
nvl((PACK_PROPRIO.dev_get_cumul121(nvl(contrats_ca_liens2.liens,0),3,2007-1,CONTRATS.cont_PK)),0) AS cumul12prev
FROM
CONTRATS,
contrats_ca_liens2,
ENSEIGNES,
SECTEURS_ACTIVITES,
contrats_cell_surf,
CONTRATS_TIERS,
NATURES_CONTRATS,
sites
WHERE
(contrats_ca_liens2.cont_fk(+) = contrats.cont_pk)
and (ENSEIGNES.ens_pk(+) = CONTRATS.cont_ens_fk)
AND (CONTRATS.cont_pk = CONTRATS_TIERS.cont_trs_cont_fk)
AND (NATURES_CONTRATS.nat_cont_pk = CONTRATS.cont_nat_cont_fk)
AND (CONTRATS_TIERS.cont_trs_typ_trs = '1')
AND (CONTRATS.cont_pk = contrats_cell_surf.cont_pk)
AND (SECTEURS_ACTIVITES.sect_act_pk(+) = CONTRATS.cont_sect_act_fk)
AND (cont_site_fk = 2754)
and (sites.cell_pk = contrats.cont_site_fk)
--AND (cont_nat_cont_fk IN (11, 12)) -- emph, com
--and CONTRATS.cont_avenant_prisedeffet = (select max(CONTRATS.cont_avenant_prisedeffet) from contrats contrats1 where contrats1.cont_ens_fk = contrats.cont_ens_fk)
AND (ENSEIGNES.ens_libelle IS NOT NULL)
AND TO_DATE(TO_CHAR(CONTRATS.cont_date_resiliation,'DD/MM/YYYY'),'DD/mm/yyyy') > TO_DATE(CONCAT((CONCAT(TO_CHAR(15)||'/',TO_CHAR(3)||'/')),TO_CHAR(2007-1)),'DD/mm/yyyy')
AND TO_DATE(TO_CHAR(CONTRATS.cont_avenant_prisedeffet,'DD/MM/YYYY'),'DD/mm/yyyy') < TO_DATE(CONCAT((CONCAT(TO_CHAR(15)||'/',TO_CHAR(3)||'/')),TO_CHAR(2007)),'DD/mm/yyyy')
and cont_avenant = F_GetContAvenant(cont_initial,cont_instance,TO_DATE(CONCAT((CONCAT(TO_CHAR(15)||'/',TO_CHAR(3)||'/')),TO_CHAR(2007)),'DD-MM-YYYY'))
--and (to_char(CONTRATS.cont_avenant_prisedeffet,'dd-mm-yyyy') = Pack_Loyer.dev_get_last_contrat(enseignes.ens_libelle, sites.cell_pk, NATURES_CONTRATS.nat_cont_pk, 15, 3, 2007))
--AND (
--TO_DATE(CONCAT((CONCAT(TO_CHAR(15)||'/',TO_CHAR(3)||'/')),TO_CHAR(2007)),'DD/mm/yyyy')
--BETWEEN
--TO_DATE(TO_CHAR(CONTRATS.cont_avenant_prisedeffet,'DD/MM/YYYY'),'DD/mm/yyyy') AND TO_DATE(TO_CHAR(CONTRATS.cont_date_resiliation,'DD/MM/YYYY'),'DD/mm/yyyy')
--)
--and contrats.cont_actif = '1'
order by enseigne; |
Partager