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
| create or replace procedure proc_paie_doublpaie
AS
BEGIN
DECLARE
tab_sql long;
BEGIN
tab_sql := 'CREATE GLOBAL TEMPORARY TABLE DOUBLONS
(
AGENT_PAIE_CLE char(6),
AGENT_DATE number(8,0),
IDENT_IDENT char(6),
AGENT_PAIE_CIGAP char(6),
REFCSO_CODE char(8),
AGENT_PAIE_NUMSS char(13),
REFUP_CODE char(2),
AGENT_PAIE_ENTITE char(6),
REFGRADE_CODE char(5),
REFFONCTION_CODE char(5)
) ON COMMIT PRESERVE ROWS ';
EXECUTE IMMEDIATE tab_sql;
END;
BEGIN
SELECT AGENT_PAIE_IDENT,
AGENT_PAIE_AAAAMM,
AGENT_PAIE_ORDRE,
AGENT_PAIE_DATE,
AGENT_PAIE_CIGAP ,
REFCSO_CODE,
AGENT_PAIE_NUMSS,
REFUP_CODE,
AGENT_PAIE_ENTITE,
REFGRADE_CODE,
REFFONCTION_CODE
INTO DOUBLONS
FROM AGENT_PAIE
GROUP BY AGENT_PAIE_IDENT
HAVING count(*) > 1;
END;
INSERT INTO DOUBLON_PAIE (
IDENT_NOM,
AGENT_PAIE_CIGAP,
AGENT_DATE,
IDENTIFICATION.IDENT_IDENT,
AGENT_PAIE_NUMSS,
REFELT_CODE,
doublons.REFUP_CODE,
to_char(PAIE_MONTANT,999999999.99) AS PAIE_MONTANT,
REFGRADE_CODE,
doublons.REFCSO_CODE,
AGENT_PAIE_ENTITE,
REFBUR_LIBELLE,
REFFONCTION_CODE )
SELECT DISTINCT IDENT_NOM,
AGENT_PAIE_CIGAP,
AGENT_DATE,
IDENTIFICATION.IDENT_IDENT,
AGENT_PAIE_NUMSS,
REFELT_CODE,
doublons.REFUP_CODE,
to_char(PAIE_MONTANT,999999999.99) AS PAIE_MONTANT,
REFGRADE_CODE,
doublons.REFCSO_CODE,
AGENT_PAIE_ENTITE,
REFBUR_LIBELLE,
REFFONCTION_CODE
FROM IDENTIFICATION, doublons, PAIE, REFBUR
WHERE IDENTIFICATION.IDENT_IDENT = doublons.IDENT_IDENT
and AGENT_PAIE_IDENT = PAIE_IDENT
and AGENT_PAIE_AAAAMM = PAIE_AAAAMM
and AGENT_PAIE_ORDRE = PAIE_ORDRE
AND REFBUR.REFCSO_CODE=doublons.REFCSO_CODE
AND REFBUR.REFBUR_CODE=AGENT_PAIE_ENTITE
AND PAIE_TYPE='1'
AND REFELT_CODE in ('1004','2330','2340','2730')
order by IDENT_NOM;
END;
END proc_paie_doublpaie; |
Partager