Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PACKAGE BODY R5.EX_AUTO_ORDER_CONF AS
- PROCEDURE ORDER_AUTO_CONF_P1(P_GR NUMBER, P_TYP NUMBER DEFAULT 0)
- IS
- CURSOR C1 IS
- SELECT FOUCFIN, FOUCNUF, FOUTYPE FROM FOUDGENE, FOUATTRI
- WHERE FOUCFIN = FATCFIN
- AND FATCLA = 'AORD'
- AND FATATT = P_GR
- AND ((FOUTYPE = P_TYP) OR (P_TYP = 0));
- CURSOR C2(P_FIN NUMBER) IS
- SELECT ECDCINCDE, ECDCEXCDE, ECDSITE, ECDCFIN, ECDETAT, ECDTYPE, ECDDCOM FROM CDEENTCDE
- WHERE ECDCFIN = P_FIN
- AND ECDETAT IN (2,3)
- AND ECDTYPE IN (1,2,5) --- DO SPRAWDZENIA
- --and ecdcincde in (1888) --DO testow
- AND NOT EXISTS (SELECT 1 FROM EX_ORD_AUTO_CONF WHERE ECDCINCDE = CINCDE AND INDI < 2)
- AND ECDDCOM >= TRUNC(SYSDATE)
- ;
- V_STATUS NUMBER;
- V_CHECK NUMBER;
- V_SEQ NUMBER(12);
- V_QTY NUMBER;
- V_SITLI NUMBER;--GRU
- BEGIN
- SELECT EX_AUTO_CONF_ORD_SEQ.NEXTVAL
- INTO V_SEQ
- FROM DUAL;
- FOR REC1 IN C1 LOOP
- --DBMS_OUTPUT.PUT_LINE(REC1.FOUCFIN);
- FOR REC2 IN C2(REC1.FOUCFIN) LOOP
- V_STATUS := 0;
- BEGIN
- SELECT ecdetat
- INTO V_CHECK
- FROM cdeentcde
- WHERE ECDCINCDE = REC2.ECDCINCDE
- for update of ecdetat nowait;
- EXCEPTION
- WHEN OTHERS THEN V_STATUS := 99; -- zmówienie w trakcie modyfikacji przez usera
- END;
- V_QTY :=0;
- IF V_STATUS = 0 THEN
- BEGIN
- SELECT SUM(DCDQTEC)
- INTO V_QTY
- FROM CDEDETCDE
- WHERE DCDCINCDE = REC2.ECDCINCDE;
- EXCEPTION
- WHEN OTHERS THEN V_QTY := 0;
- END;
- IF V_QTY = 0 THEN
- V_STATUS := 98; ---------ilosc w zamówieniu = 0
- END IF;
- END IF;
- /*GRU*/
- V_SITLI :=0;
- IF V_STATUS = 0 THEN
- BEGIN
- SELECT ECDSITLI
- INTO V_SITLI
- FROM CDEENTCDE
- WHERE ECDCINCDE = REC2.ECDCINCDE;
- EXCEPTION
- WHEN OTHERS THEN V_QTY := 0;
- END;
- IF V_QTY = 0 THEN
- V_STATUS := 999; --------- bledny nr magazynu
- END IF;
- END IF;
- /*GRU*/
- dBMS_OUTPUT.PUT_LINE(REC2.ECDCINCDE||' - '||V_STATUS);
- --DBMS_OUTPUT.PUT_LINE(REC2.ECDCINCDE);
- BEGIN
- INSERT INTO EX_ORD_AUTO_CONF
- (CFIN, CNUF, FTYPE, CINCDE, CEXCDE,
- CDSITE, CDETAT, CDTYPE, CDDCOM, DCRE,
- PROG1, STAT1, DMAJ1,
- PROG2, STAT2, DMAJ2,
- PROG3, STAT3, DMAJ3, INDI, PROC_ID)
- VALUES (REC1.FOUCFIN, REC1.FOUCNUF, REC1.FOUTYPE, REC2.ECDCINCDE, REC2.ECDCEXCDE,
- REC2.ECDSITE, REC2.ECDETAT, REC2.ECDTYPE, REC2.ECDDCOM, SYSDATE,
- 'psgcf01p psgcf01p $USERID '||REC2.ECDCINCDE||' 2 GB',0,NULL,
- 'psccl30p psccl30p $USERID 0 '||V_SITLI||' -1 -1 -1 -1 -1 '||REC2.ECDCEXCDE||' '||TO_CHAR(SYSDATE,'dd/mm/yy hh24mi')||' 0 0000000 0 1 0 0 PL',0,NULL,
- NULL,NULL,NULL, V_STATUS, V_SEQ);
- /*GRU
- VALUES (REC1.FOUCFIN, REC1.FOUCNUF, REC1.FOUTYPE, REC2.ECDCINCDE, REC2.ECDCEXCDE,
- REC2.ECDSITE, REC2.ECDETAT, REC2.ECDTYPE, REC2.ECDDCOM, SYSDATE,
- 'psgcf01p psgcf01p $USERID '||REC2.ECDCINCDE||' 2 GB',0,NULL,
- 'psccl30p psccl30p $USERID 0 10 -1 -1 -1 -1 -1 '||REC2.ECDCEXCDE||' '||TO_CHAR(SYSDATE,'dd/mm/yy hh24mi')||' 0 0000000 0 1 0 0 PL',0,NULL,
- NULL,NULL,NULL, V_STATUS, V_SEQ);
- GRU*/
- EXCEPTION
- WHEN OTHERS THEN V_STATUS := 2;
- END;
- DBMS_OUTPUT.PUT_LINE(REC1.FOUCNUF||' '||REC2.ECDCEXCDE||' '||V_STATUS);
- COMMIT;
- END LOOP;
- END LOOP;
- END ORDER_AUTO_CONF_P1;
- PROCEDURE ORDER_AUTO_CONF_P2
- IS
- CURSOR C1 IS
- SELECT CINCDE,ECDETAT FROM EX_ORD_AUTO_CONF,CDEENTCDE
- WHERE ECDCINCDE = CINCDE
- AND STAT1 = 0
- AND INDI = 0 ;
- CURSOR C2 IS
- SELECT CINCDE,ECDETAT,INDI,CNUF, CEXCDE FROM EX_ORD_AUTO_CONF,CDEENTCDE
- WHERE ECDCINCDE = CINCDE
- AND STAT1 = 1
- AND INDI = 0 ;
- V_STATUS NUMBER;
- V_INDI NUMBER;
- BEGIN
- FOR REC1 IN C1 LOOP
- V_INDI := 0;
- IF REC1.ECDETAT = 3 THEN
- V_STATUS := 1;
- ELSE
- V_STATUS := 0;
- V_INDI := 3;
- END IF;
- UPDATE EX_ORD_AUTO_CONF
- SET STAT1 = V_STATUS, INDI = V_INDI
- WHERE CINCDE = REC1.CINCDE
- AND INDI = 0;
- COMMIT;
- END LOOP;
- FOR REC2 IN C2 LOOP
- IF REC2.ECDETAT = 3 AND REC2.INDI = 0 THEN
- V_INDI := 0;
- BEGIN
- UPDATE CDEDETCDE
- SET DCDETAT = 5
- WHERE DCDCINCDE = REC2.CINCDE;
- EXCEPTION
- WHEN OTHERS THEN V_INDI := 4;
- END;
- IF V_INDI = 0 THEN
- BEGIN
- UPDATE CDEENTCDE
- SET ECDETAT = 5
- WHERE ECDCINCDE = REC2.CINCDE;
- EXCEPTION
- WHEN OTHERS THEN V_INDI := 5;
- END;
- IF V_INDI = 0 THEN
- COMMIT;
- DBMS_OUTPUT.PUT_LINE(REC2.CNUF||' '||REC2.CEXCDE||' '||V_STATUS);
- ELSE
- ROLLBACK;
- END IF;
- END IF;
- END IF;
- UPDATE EX_ORD_AUTO_CONF
- SET INDI = V_INDI
- WHERE CINCDE = REC2.CINCDE
- AND INDI = 0;
- COMMIT;
- END LOOP;
- END;
- PROCEDURE ORDER_AUTO_CONF_P3
- IS
- CURSOR C1(P_ID NUMBER) IS
- SELECT CINCDE, FTYPE, ROWID
- FROM EX_ORD_AUTO_CONF
- WHERE PROC_ID = P_ID
- AND INDI = 0;
- V_ID NUMBER;
- V_INDI NUMBER;
- V_CHK NUMBER;
- BEGIN
- SELECT MAX(PROC_ID)
- INTO V_ID
- FROM EX_ORD_AUTO_CONF;
- FOR REC1 IN C1(V_ID) LOOP
- V_INDI := 1;
- BEGIN
- SELECT 1
- INTO V_CHK
- FROM CDEENTCDE
- WHERE ECDCINCDE = REC1.CINCDE
- AND ECDETAT >= 5;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN V_INDI := 97;
- END;
- IF V_INDI = 1 AND REC1.FTYPE = 3 THEN
- BEGIN
- SELECT 1
- INTO V_CHK
- FROM CCLDETCCL
- WHERE CCLCINCDE = REC1.CINCDE
- AND ROWNUM =1;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN V_INDI := 96;
- END;
- END IF;
- UPDATE EX_ORD_AUTO_CONF
- SET INDI = V_INDI
- WHERE ROWID = REC1.ROWID;
- COMMIT;
- END LOOP;
- END;
- END EX_AUTO_ORDER_CONF;
- /
Add Comment
Please, Sign In to add comment