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) |
Partager