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
| SELECT DISTINCT
s.Sal_Noss,
Cs.Sal_Matr AS Matricule,
(To_Date('30/12/1899', 'DD/MM/YYYY') + Cs.Pot_Du + 2) AS Appo,
MAX(Cs.Pot_Val) OVER (PARTITION BY s.Sal_Noss, Cs.Sal_Matr, (To_Date('30/12/1899', 'DD/MM/YYYY') + Cs.Pot_Du + 2)) AS Remu_Theo_Base,
Decode(FIRST_VALUE(h.Cat_Id) OVER (ORDER BY To_Date('30/12/1899', 'DD/MM/YYYY') + h.Hca_Date + 2 DESC), 'C',Cs.Pot_Val) AS Remu_Theo_Cadre_13
,Decode(FIRST_VALUE(h.Cat_Id) OVER (ORDER BY To_Date('30/12/1899', 'DD/MM/YYYY') + h.Hca_Date + 2 DESC), 'E',Cs.Pot_Val) AS Remu_Theo_Etam_13
,'31/12/' || (To_Char(SYSDATE, 'yyyy')) AS Temps_Id
FROM Salarie s,
Const_Sal Cs,
h_Categ h
WHERE (To_Date('30/12/1899', 'DD/MM/YYYY') + h.Hca_Date + 2) <=
To_Date('31/12/' || (To_Char(SYSDATE, 'yyyy')), 'DD/MM/YYYY')
AND h.Sal_Matr = s.Sal_Matr
AND 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
NOT EXISTS
(SELECT 1
FROM Const_Sal Cs1
WHERE Cs.Sal_Matr = Cs1.Sal_Matr
AND Cs1.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
NOT EXISTS
(SELECT 1
FROM Ccmx.h_Es_Sauv Hs
WHERE Cs.Sal_Matr = Hs.Sal_Matr
AND 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 ALL
SELECT 1
FROM Ccmx.h_Es h
WHERE h.Sal_Matr = Cs.Sal_Matr
AND 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')) |
Partager