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 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134
| PROCEDURE import_INFO_WS_ASCII (stAction IN VARCHAR2,
Imp_Param IN OUT CMST_READ_ASCII.Import_ParamStruct,
Enreg_Param IN OUT CMST_READ_ASCII.Enreg_ParamSTRUCT,
TablField IN CMST_READ_ASCII.FieldTabTyp,
nuRet IN OUT INTEGER,
nuWarning IN OUT INTEGER)
IS
hFile UTL_FILE.FILE_TYPE;
-- etat BT
WS_PREPARE NUMBER := 2;
WS_WAIT_LAUNCH NUMBER := 4;
WS_INPROGRESS NUMBER := 5;
WS_CLOSED NUMBER := 6;
dtDTTM_IN_PROGRESS DATE;
Rapport UTL_FILE.FILE_TYPE ;
stRap VARCHAR2(10000);
stMsg VARCHAR2(250);
stWOWS VARCHAR2(8);
nuWO NUMBER;
nuWS NUMBER;
nuROW_ID NUMBER;
nuOldStatus NUMBER;
dtWS02 DATE;
nudebug NUMBER := 0;
/* CURSEUR */
CURSOR Cur_SEL_WS( pnucd_WS IN NUMBER, pnucd_WO IN NUMBER)
IS
SELECT * FROM WS WHERE CD_WOWS_STATUS < WS_CLOSED AND CD_WOWS_STATUS > WS_PREPARE AND CD_WS = pnucd_WS AND CD_WO = pnucd_WO;
BEGIN
nuRet :=0;
-- Ouverture du fichier en sortie
IF stAction ='AfterFileOpening'
THEN
/* juste apres ouverture des fichier et avant lecture du premier enregistrement */
UTL_FILE.PUTF( Imp_Param.hFileError, IMPORT_INFO_WS_TITLE, TO_CHAR(SYSDATE,'YYYYMMDD/HH24:MI:SS'));
UTL_FILE.NEW_LINE( Imp_Param.hFileError);
nuRet :=0;
BEGIN
Rapport := UTL_FILE.FOPEN ('F:\HOTLINE-ACCUEIL\07-traitement CARL\Rapport', 'S04-Rapport_annulation-'||TO_CHAR(SYSDATE,'YYYYMMDD_HH24MI')||'.txt', 'w');
UTL_FILE.FCLOSE(Rapport);
END;
ELSIF stAction ='AfterRecordControl'
THEN
/* apres controle des champs de l'enregistrement
|| si refus de l'enregistrement par le controle nuRet = 1 sinon = 0
||
*/
nuWarning := 0;
Enreg_Param.Rejected := FALSE;
BEGIN
/*On separe les code wo et ws contenus dans FIT*/
stWOWS := RTRIM(TO_CHAR (TablField(1)));
nuWO := TO_NUMBER(SUBSTR( stWOWS, 1 ,LENGTH( stWOWS) -2));
nuWS := TO_NUMBER(SUBSTR( stWOWS, LENGTH( nuWO) + 1 ,LENGTH( nuWO) + 3));
IF TRIM(TablField(3)) IS NOT NULL THEN
dtWS02 := TO_DATE(TablField(3),'DDMMYYHH24MI');
ELSE
dtWS02 :='';
END IF;
FOR CUR IN Cur_SEL_WS(nuWS, nuWO )
LOOP
nuOldStatus := CUR.CD_WOWS_STATUS;
dtDTTM_IN_PROGRESS := NULL;
IF nuOldStatus IN ('5','6','7','8') AND TablField(5) = 4
THEN
------------------------- rapport : intervention annulée
IF CUR.DT_WS04 IS NOT NULL THEN
stRap := 'OT ' || TO_CHAR(CUR.CD_WO) || ' : intervention déplanifiée (ancienne date :' || CUR.DT_WS04 || ')\n' || chr(10) || chr(13) ;
Rapport := UTL_FILE.FOPEN ('F:\HOTLINE-ACCUEIL\07-traitement CARL\Rapport', 'S04-Rapport_annulation'||TO_CHAR(SYSDATE,'YYYYMMDD_HH24MI')||'.txt', 'a');
UTL_FILE.PUTF( Rapport , stRap);
UTL_FILE.FCLOSE(Rapport);
END IF;
nudebug:=nudebug+1;
UPDATE WS SET
VAL_WS02 = TablField(6),
WHERE CD_WO = CUR.CD_WO AND CD_WS = CUR.CD_WS;
ELSE
nudebug:=nudebug+100;
IF TablField(5) = 5
THEN
dtDTTM_IN_PROGRESS := SYSDATE;
END IF;
nuROW_ID := NULL;
CMST_WO_STATUS.Status_WO(CUR.CD_WO, nuOldStatus, nuROW_ID);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
nuRet := SQLCODE;
stMsg := SQLERRM;
END;
ELSIF stAction ='Close' THEN
/* appel avant destruction du fichier d'import
|| le fichier d'erreur et celui d'import sont deja fermes
|| si retourne nuRet<>0 le fichier d'import n'est pas supprime
*/
nuRet := 0;
Rapport := UTL_FILE.FOPEN ('F:\CNT\HOTLINE-ACCUEIL\07-traitement CARL\Rapport Planning SPIE', 'DEBUG')||'.txt', 'w');
UTL_FILE.PUTF( Rapport , nudebug);
UTL_FILE.FCLOSE(Rapport);
END IF;
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(Imp_Param.hFileError)
THEN
UTL_FILE.PUTF(Imp_Param.hFileError, IMPORT_BIG_ERR, TO_CHAR (Enreg_Param.Line ));
UTL_FILE.NEW_LINE(Imp_Param.hFileError);
UTL_FILE.PUT(Imp_Param.hFileError,IMPORT_ERROR);
UTL_FILE.PUT(Imp_Param.hFileError,SQLCODE);
UTL_FILE.PUT(Imp_Param.hFileError,' - ' || SUBSTR(SQLERRM,1,150));
UTL_FILE.NEW_LINE(Imp_Param.hFileError);
Enreg_Param.Rejected := TRUE;
nuret := 1;
ROLLBACK;
END IF;
RAISE_APPLICATION_ERROR(-20001,'Erreur dans import_INFO_WS_ASCII'||TO_CHAR(SQLCODE));
END; |
Partager