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
| SELECT NULL AS CODEFILS, NULL AS CODEEXTERNEFILS, CODEPERE, CODE_GP, CODE_DPT, CODEEXTERNEPERE,MONTANT,LIBELLE from
(SELECT ROWNUM as RNUM, CODEPERE, CODE_GP, CODE_DPT, CODEEXTERNEPERE,MONTANT,LIBELLE from
(SELECT nmc_ue_cdnmccmr AS CODEPERE,
nmc_gp_cdnmccmr AS CODE_GP,
nmc_dpt_cdnmccmr AS CODE_DPT,
nmc_ue_cdextnmccmr AS CODEEXTERNEPERE,
sum(mvc.montant) as MONTANT,
nmc_ue_lbnmccmr AS LIBELLE
FROM
( SELECT nmc_dpt.cdresdis nmc_dpt_cdresdis,
nmc_fam.cdnmccmr nmc_fam_cdnmccmr,
nmc_ue.cdstrnmccmr nmc_ue_cdstrnmccmr,
nmc_ue.cdextnmccmr nmc_ue_cdextnmccmr,
nmc_gp.cdextnmccmr nmc_gp_cdextnmccmr,
nmc_dpt.cdextnmccmr nmc_dpt_cdextnmccmr,
nmc_gp.cdnmccmr nmc_gp_cdnmccmr,
nmc_dpt.cdnmccmr nmc_dpt_cdnmccmr,
nmc_ue.lbnmccmr nmc_ue_lbnmccmr,
nmc_ue.cdnmccmr nmc_ue_cdnmccmr
FROM rgxtnmc nmc_mag
INNER JOIN rgxthnm hnm_mag_dpt
ON hnm_mag_dpt.cdnmccmrfll =nmc_mag.cdnmccmr
AND((hnm_mag_dpt.dtfinlienmc IS NOT NULL
AND TO_TIMESTAMP('08/01/2010','dd/MM/yyyy') BETWEEN hnm_mag_dpt.dtdeblienmc AND hnm_mag_dpt.dtfinlienmc)
OR (hnm_mag_dpt.dtfinlienmc IS NULL
AND TO_TIMESTAMP('08/01/2010','dd/MM/yyyy') >=hnm_mag_dpt.dtdeblienmc))
INNER JOIN rgxtnmc nmc_dpt
ON nmc_dpt.cdnmccmr =hnm_mag_dpt.cdnmccmr
AND nmc_dpt.cdresdis = 8
AND((nmc_dpt.dtfinacvnmc IS NULL
OR (nmc_dpt.dtfinacvnmc >=TO_TIMESTAMP('08/01/2010','dd/MM/yyyy')))
AND (nmc_dpt.dtdebacvnmc <=TO_TIMESTAMP('08/01/2010','dd/MM/yyyy')))
INNER JOIN rgxthnm hnm_dpt_gp
ON hnm_dpt_gp.cdnmccmrfll =nmc_dpt.cdnmccmr
AND((hnm_dpt_gp.dtfinlienmc IS NOT NULL
AND TO_TIMESTAMP('08/01/2010','dd/MM/yyyy') BETWEEN hnm_dpt_gp.dtdeblienmc AND hnm_dpt_gp.dtfinlienmc)
OR(hnm_dpt_gp.dtfinlienmc IS NULL
AND TO_TIMESTAMP('08/01/2010','dd/MM/yyyy') >= hnm_dpt_gp.dtdeblienmc))
INNER JOIN rgxtnmc nmc_gp
ON nmc_gp.cdnmccmr =hnm_dpt_gp.cdnmccmr
AND ((nmc_gp.dtfinacvnmc IS NULL
OR (nmc_gp.dtfinacvnmc >= TO_TIMESTAMP('08/01/2010','dd/MM/yyyy')))
AND (nmc_gp.dtdebacvnmc <=TO_TIMESTAMP('08/01/2010','dd/MM/yyyy')))
INNER JOIN rgxthnm hnm_gp_ue
ON hnm_gp_ue.cdnmccmrfll =nmc_gp.cdnmccmr
AND ((hnm_gp_ue.dtfinlienmc IS NOT NULL
AND TO_TIMESTAMP('08/01/2010','dd/MM/yyyy') BETWEEN hnm_gp_ue.dtdeblienmc AND hnm_gp_ue.dtfinlienmc)
OR (hnm_gp_ue.dtfinlienmc IS NULL
AND TO_TIMESTAMP('08/01/2010','dd/MM/yyyy') >= hnm_gp_ue.dtdeblienmc))
INNER JOIN rgxtnmc nmc_ue
ON nmc_ue.cdnmccmr =hnm_gp_ue.cdnmccmr
AND nmc_ue.cdstrnmccmr = 2
AND ((nmc_ue.dtfinacvnmc IS NULL
OR (nmc_ue.dtfinacvnmc >=TO_TIMESTAMP('08/01/2010','dd/MM/yyyy')))
AND (nmc_ue.dtdebacvnmc <=TO_TIMESTAMP('08/01/2010','dd/MM/yyyy')))
INNER JOIN rgxthnm hnm_ue_fam
ON hnm_ue_fam.cdnmccmrfll =nmc_ue.cdnmccmr
AND ((hnm_ue_fam.dtfinlienmc IS NOT NULL
AND TO_TIMESTAMP('08/01/2010','dd/MM/yyyy') BETWEEN hnm_ue_fam.dtdeblienmc AND hnm_ue_fam.dtfinlienmc)
OR (hnm_ue_fam.dtfinlienmc IS NULL
AND TO_TIMESTAMP('08/01/2010','dd/MM/yyyy') >= hnm_ue_fam.dtdeblienmc))
INNER JOIN rgxtnmc nmc_fam
ON nmc_fam.cdnmccmr =hnm_ue_fam.cdnmccmr
AND ((nmc_fam.dtfinacvnmc IS NULL
OR (nmc_fam.dtfinacvnmc >=TO_TIMESTAMP('08/01/2010','dd/MM/yyyy')))
AND (nmc_fam.dtdebacvnmc <=TO_TIMESTAMP('08/01/2010','dd/MM/yyyy')))
WHERE NOT EXISTS
(SELECT etn.CDNMCCMR
FROM rgxtetn etn
WHERE ((etn.dtfinlieeat IS NOT NULL
AND TO_TIMESTAMP ('08/01/2010', 'dd/MM/yyyy') BETWEEN etn.dtdebeatnmc AND etn.dtfinlieeat)
OR(etn.dtfinlieeat IS NULL
AND TO_TIMESTAMP ('08/01/2010', 'dd/MM/yyyy') >= etn.dtdebeatnmc))
AND etn.cdeatobjrgx IN (10,12)
AND ( etn.cdnmccmr = nmc_dpt.cdnmccmr
OR etn.cdnmccmr = nmc_gp.cdnmccmr
OR etn.cdnmccmr = nmc_ue.cdnmccmr)
)
) STRUCTURE,
(SELECT mvc.vlmntmvt, mvc.cdnmccmr, mvc.orvalmnt,
(case
when mvc.orvalmnt = 'C' then mvc.vlmntmvt
else -mvc.vlmntmvt
end ) as montant
FROM rgxtmvc mvc
WHERE mvc.cdmagdis = 4264
AND mvc.dtjurcpt = TO_TIMESTAMP ('08/01/2010', 'dd/MM/yyyy')
AND mvc.cdtypmvt IN (100,102,110,111)
) mvc
WHERE STRUCTURE.nmc_fam_cdnmccmr = mvc.cdnmccmr(+)
GROUP BY nmc_ue_cdnmccmr,
nmc_ue_cdextnmccmr,
nmc_ue_lbnmccmr,
nmc_dpt_cdnmccmr,
nmc_dpt_cdextnmccmr,
nmc_gp_cdnmccmr,
nmc_gp_cdextnmccmr
ORDER BY nmc_ue_cdextnmccmr ASC
)
where ROWNUM <= 8)
Where RNUM > 0; |
