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
| CREATE OR REPLACE PROCEDURE "UPDATE_LOT_PRE"
AS
BEGIN
DECLARE
/*
** Variables
*/
Wrk_Traitement VARCHAR2(60);
Wrk_NomTraite VARCHAR2(60);
Wrk_FlagTraite CHAR(1); -- Statut du traitement ("0"=Ok, "1"=Warning, "2"=Erreur passante, "3"=Erreur bloquante
-- )
Wrk_LibelAction VARCHAR2(255);
Wrk_ObjetAction VARCHAR2(255);
Wrk_LibErrAction VARCHAR2(255);
Wrk_LibErrObjet VARCHAR2(255);
TYPE TYPE_TAB IS VARRAY(50) OF VARCHAR2(3);
ma_TABle TYPE_TAB := TYPE_TAB('ACH', 'ACN', 'FBN', 'FTN', 'DDN', 'CLN',
'FIN', 'ECH', 'QDV', 'PKN', 'QCF', 'FFN',
'CEE', 'CFF', 'TBL', 'FFB', 'FOT', 'TMP',
'PKB', 'TPF', 'ECN', 'FFC', 'ACM', 'FRB',
'FRT', 'CIF', 'DOU', 'CLE', 'FIE', 'CDV',
'CFE', 'SRP', 'FRF', 'PRE', 'FIB', 'SKB',
'REC', 'FBB', 'PTD', 'FSA', 'AFT', 'FTB',
'INS', 'DDB', 'TCO', 'RCP', 'IN2', 'RE2');
LC$Requete VARCHAR2(256);
/*
** PROCEDURE
*/
BEGIN
DBMS_OUTPUT.PUT_LINE('debut traitement');
/*
** Initialisation des valeurs
*/
Wrk_Traitement := 'Chargement DWH';
Wrk_NomTraite := 'UPDATE_LOT_PRE';
BEGIN
FOR I IN 1 .. 48 LOOP
BEGIN
LC$Requete := 'UPDATE LOT_PRE SET COUT_E' ||
trim(TO_CHAR(I + 1, '000')) ||
' = F_EW_COUT_PRE_LOT(CODSOC, CODPRO, ''' ||
ma_TABLE(I) ||
''', NUMLOT, OST001, OST002, OST003);';
DBMS_OUTPUT.PUT_LINE(LC$Requete);
-- execute immediate 'select sysdate from dual';
BEGIN
EXECUTE immediate LC$Requete;
DBMS_OUTPUT.PUT_LINE('execution de la requete');
EXCEPTION
WHEN OTHERS THEN
BEGIN
Wrk_FlagTraite := '3';
Wrk_LibelAction := 'MAJ COUT_E' ||
trim(TO_CHAR(I + 1, '000')) ||
' DANS LOT_PRE';
Wrk_ObjetAction := 'MAJ COUT_E' ||
trim(TO_CHAR(I + 1, '000')) ||
'DANS LOT_PRE';
Wrk_LibErrAction := 'ERREUR DANS MAJ DE LOT_PRE';
Wrk_LibErrObjet := SUBSTR(SQLERRM, 1, 255);
INSERT_LOGALIM(Wrk_Traitement, Wrk_NomTraite,
Wrk_FlagTraite, Wrk_LibelAction,
Wrk_ObjetAction, Wrk_LibErrAction,
Wrk_LibErrObjet);
END;
END;
DBMS_OUTPUT.PUT_LINE('COMMIT');
COMMIT;
END;
END LOOP;
END;
END;
end; |
Partager