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
| as PROCEDURE ca_proprio(
c_real IN OUT curs_ca_prop,
site in number,
annee IN NUMBER,
mois IN NUMBER,
now IN number
)
IS
BEGIN
OPEN c_real
FOR
SELECT DISTINCT
-- sites.cell_denomination as site_nom,
enseignes.ens_libelle AS enseigne,
secteurs_activites.sect_act_libelle as secteur,
contrats.cont_pk,
natures_contrats.nat_cont_libelle,
contrats_cell_surf.sup_loc as sup_loc,
(SELECT contrats1.cont_avenant_prisedeffet
FROM contrats contrats1
WHERE (contrats1.cont_initial = contrats.cont_initial AND contrats1.cont_avenant = contrats.cont_avenant))as cont_date_debut,
--((NVL((SELECT contrats1.cont_avenant_prisedeffet
-- FROM contrats contrats1
-- WHERE (contrats1.cont_initial = contrats.cont_initial AND contrats1.cont_avenant = contrats.cont_avenant + 1)),NULL)))cont_date_fin,
contrats.cont_code,
round(dev_get_cumul12(mois,annee,contrats.cont_PK)) as cumul12
FROM
contrats,
--contrats_ca,
enseignes,
secteurs_activites,
contrats_cell_surf,
contrats_tiers,
natures_contrats
WHERE
((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 (ca_proprio.cont_pk = contrats.cont_pk)
-- and (contrats_ca.cont_ca_site_fk = sites.cell_pk)
-- AND (contrats.cont_pk = contrats_ca.cont_ca_cont_fk(+))
AND (secteurs_activites.sect_act_pk(+) = contrats.cont_sect_act_fk)
AND (cont_site_fk = site)
AND (cont_nat_cont_fk IN (11, 12, 34, 36, 37, 38, 54)) -- contrats commerciaux et emphythéotiques
--AND (contrats.cont_nat_cont_fk IN (33,56)) -- contrats reserves
--AND (now BETWEEN '2004'and '2005')
AND (enseignes.ens_libelle IS NOT NULL));
end ca_proprio; |
