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
| CREATE OR REPLACE PROCEDURE MY_PRO
IS code_error
varchar2(500);
msg_error varchar2(500);
z varchar2(3276);
idcontrat number;
CURSOR CURS IS
SELECT *
FROM MY_VIEW
WHERE (DATE_S, SEQ, CODESTRUC) NOT IN
(SELECT DATE_S, SEQ, CODESTRUC
FROM TABLE_TEMP
where DATE_S > '01/11/2019')
AND nvl(SI_VALID(GET_ID(CODESTRUC,CLIENT)),0) = 1 ;
BEGIN
FOR i IN CURS LOOP
IDCON:=GET_ID(i.CODESTRUC,i.CLIENT);
IF SI_VALID (IDCON)=1 THEN
IF IDCON=15655 THEN -- Ajouté le 17/06/2019
z:=GEN_CLI(REPLACE(REPLACE(REPLACE(GET_TEXT(101),'MON',i.ALTC),'x.xxx.xxx',sep_decim(i.MONTANT)),'xxxxxxxxxxxxxxxxxxxx',i.CODEACC),IDCON);
ELSE
z:=GEN_CLI(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TEXT8SEND(101),'MON',i.ALTC),'x.xxx.xxx',sep_decim(i.MONTANT)),'xxxxxxxxxxxxxxxxxxxx',i.CODEACC),'MY_PRO',i.des_eng),'CPT',i.TEXT_ABR),IDCON);
END IF;
INSERT INTO MY_TAB (TXT,IDD,DH,HH,id_contrat,AG,NUM,STAT)
VALUES(z,101,trunc(sysdate),to_char(sysdate,'HH24:MI:SS'),IDCON,i.CODESTRUC,i.CLIENT,1);
INSERT INTO TABLE_TEMP (SEQ,DATE_S,CODESTRUC,CLIENT,XDE,XE,XCV,MONTANT,DES,date_r,heure_r)
VALUES (i.SEQ, i.DATE_S, i.CODESTRUC, i.CLIENT, i.XDE,i.XE,i.XCV,i.MONTANT,i.DES,trunc(sysdate),to_char(sysdate,'HH24:MI:SS'));
COMMIT;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
code_error := SQLCODE;
msg_error := substr(SQLERRM(SQLCODE),1,100);
INSERT INTO SMS_ERRORS VALUES ('MY_PRO',code_error,msg_error,0,sysdate);
COMMIT;
END MY_PRO; |
Partager