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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
(select DISTINCT
CS.SAL_MATR as MATRICULE
,S.SAL_NOSS
,(TO_DATE('30/12/1899', 'DD/MM/YYYY') + CS.POT_DU + 2) as appo
, MAX(CS.POT_VAL) as appo_val
, decode(CAT_ID,'C',CS.POT_VAL*13) as REMU_THEO_CADRE_13
, decode(CAT_ID,'E',CS.POT_VAL*13) as REMU_THEO_ETAM_13
, '31/12/' || (to_char(sysdate,'yyyy')) as TEMPS_ID
FROM CONST_SAL CS, SALARIE S
WHERE CS.ARC_ID ='APPO'
and s.SAL_MATR=CS.SAL_MATR
and CS.MON_ID = 'EUR'
and (CS.POT_DU) = (select max(APPO.POT_DU)
FROM CONST_SAL APPO
WHERE APPO.ARC_ID ='APPO'
and APPO.MON_ID = 'EUR'
and APPO.SAL_MATR=CS.SAL_MATR
and (TO_DATE('30/12/1899', 'DD/MM/YYYY') + APPO.POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')),'DD/MM/YYYY')
)
AND CS.SAL_MATR not in(SELECT SAL_MATR
FROM CONST_SAL
WHERE ARC_ID ='OBAN'
AND (TO_DATE('30/12/1899', 'DD/MM/YYYY') + POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')),'DD/MM/YYYY')
)
AND CS.SAL_MATR NOT IN (SELECT SAL_MATR
FROM CCMX.H_ES_SAUV HS
WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + HS.E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')))
AND HS.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
UNION
(SELECT SAL_MATR
FROM CCMX.H_ES H
WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')))
AND H.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
))
)
UNION
(select DISTINCT
CS.SAL_MATR as MATRICULE
,S.SAL_NOSS
,(TO_DATE('30/12/1899', 'DD/MM/YYYY') + CS.POT_DU + 2) as appo
, MAX(CS.POT_VAL) as appo_val
, decode(CAT_ID,'C',CS.POT_VAL*13) as REMU_THEO_CADRE_13
, decode(CAT_ID,'E',CS.POT_VAL*13) as REMU_THEO_ETAM_13
, '31/12/' || (to_char(sysdate,'yyyy')-1) as TEMPS_ID
FROM CONST_SAL CS, SALARIE S
WHERE CS.ARC_ID ='APPO'
and s.SAL_MATR=CS.SAL_MATR
and CS.MON_ID = 'EUR'
and (CS.POT_DU) = (select max(APPO.POT_DU)
FROM CONST_SAL APPO
WHERE APPO.ARC_ID ='APPO'
and APPO.MON_ID = 'EUR'
and APPO.SAL_MATR=CS.SAL_MATR
and (TO_DATE('30/12/1899', 'DD/MM/YYYY') + APPO.POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')-1),'DD/MM/YYYY')
)
AND CS.SAL_MATR not in(SELECT SAL_MATR
FROM CONST_SAL
WHERE ARC_ID ='OBAN'
AND (TO_DATE('30/12/1899', 'DD/MM/YYYY') + POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')-1),'DD/MM/YYYY')
)
AND CS.SAL_MATR NOT IN (SELECT SAL_MATR
FROM CCMX.H_ES_SAUV HS
WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + HS.E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')-1))
AND HS.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
UNION
(SELECT SAL_MATR
FROM CCMX.H_ES H
WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')-1))
AND H.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
))
)
UNION
select DISTINCT
CS.SAL_MATR as MATRICULE
,S.SAL_NOSS
,(TO_DATE('30/12/1899', 'DD/MM/YYYY') + CS.POT_DU + 2) as appo
, MAX(CS.POT_VAL) as appo_val
, decode(CAT_ID,'C',CS.POT_VAL*13) as REMU_THEO_CADRE_13
, decode(CAT_ID,'E',CS.POT_VAL*13) as REMU_THEO_ETAM_13
, '31/12/' || (to_char(sysdate,'yyyy')-2) as TEMPS_ID
FROM CONST_SAL CS, SALARIE S
WHERE CS.ARC_ID ='APPO'
and s.SAL_MATR=CS.SAL_MATR
and CS.MON_ID = 'EUR'
and (CS.POT_DU) = (select max(APPO.POT_DU)
FROM CONST_SAL APPO
WHERE APPO.ARC_ID ='APPO'
and APPO.MON_ID = 'EUR'
and APPO.SAL_MATR=CS.SAL_MATR
and (TO_DATE('30/12/1899', 'DD/MM/YYYY') + APPO.POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')-2),'DD/MM/YYYY')
)
AND CS.SAL_MATR not in(SELECT SAL_MATR
FROM CONST_SAL
WHERE ARC_ID ='OBAN'
AND (TO_DATE('30/12/1899', 'DD/MM/YYYY') + POT_DU + 2)<=to_date('31/12/' || (to_char(sysdate,'yyyy')-2),'DD/MM/YYYY')
)
AND CS.SAL_MATR NOT IN (SELECT SAL_MATR
FROM CCMX.H_ES_SAUV HS
WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + HS.E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')-2))
AND HS.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
UNION
(SELECT SAL_MATR
FROM CCMX.H_ES H
WHERE TO_DATE('30/12/1899', 'DD/MM/YYYY') + E_S_DATES + 2 <= ('31/12/' || (to_char(sysdate,'yyyy')-2))
AND H.MES_IDS IN ('RS','LG','RA','LE','FX','DE','CN','LL','LF','LI','LM','RG','AU','LC','DM','LA','FD','RT','ES','RC','RE','EE','FS')
))
GROUP BY
CS.SAL_MATR
,S.SAL_NOSS
,(TO_DATE('30/12/1899', 'DD/MM/YYYY') + CS.POT_DU + 2)
,decode(CAT_ID,'C',CS.POT_VAL*13)
, decode(CAT_ID,'E',CS.POT_VAL*13) |
