박스 스캔작업시 프로시져 호출을 하여 작업합니다.
1번 작업대에서 스캔하면 프로시져 수행하는데 5초 걸립니다.
그런데, 2,3번 작업대에서 같은 프로시져를 호출하여 수행하면 1,2,3 작업대가 동시에 2~5분까지 작업시간이 걸립니다.
Table LOCK이 걸린다고 해도 이렇게 오래 걸린다는게 이해가 가지 않아 문의 드립니다.
오라클 초보라 프로시져를 잘못 작성했나 싶어 프로스저 내용도 함께 올립니다.
CREATE PROCEDURE SP_SERIAL_BOX_SCAN2
(
IN_BAR_CODE IN VARCHAR2,
IN_SUBDLVQTY IN NUMBER,
IN_ORDER_NO IN VARCHAR2,
IN_TAB920_REAL_SEQ IN NUMBER,
IN_TAB341B_TAX_PRT IN VARCHAR2,
IN_TAB341B_BILL_PRT IN VARCHAR2,
IN_TAB341B_LOGS_PRT IN VARCHAR2,
IN_COM_NAME IN VARCHAR2,
IN_USR_ID IN VARCHAR2
)
IS
--변수선언부
T_SERIAL_TEMP VARCHAR2(20);
T341_SAL_QTY NUMBER;
T341_DLV_QTY NUMBER;
T341_ITEM_CODE VARCHAR2(15);
T341_CUST_CODE VARCHAR2(10);
T341_SALE_DATE DATE;
T341_DEPT_CODE VARCHAR2(10);
T341_ORDER_NO VARCHAR2(15);
T341_SEQ_NO NUMBER;
T341_IP_ADDR VARCHAR2(20);
T110_ITEM_GBN VARCHAR2(1);
TP_DLV_QTY NUMBER;
TP_CTOTQTY NUMBER;
T20C_jg_qty NUMBER;
T20C_order_no VARCHAR2(15);
T20C_item_code VARCHAR2(15);
T20C_prod_order VARCHAR2(25);
TP_CTRQTY NUMBER;
T290_LOT_NO VARCHAR2(25);
T290_PROD_NO VARCHAR2(25);
T290_PACK_NO VARCHAR2(25);
T290_PROD_DATE DATE;
T290_OT_TEST_NO VARCHAR2(15);
T290_VALIDITY_DATE DATE;
T340_SAL_QTY NUMBER;
T340_CMP_QTY NUMBER;
T340_SALE_PRC NUMBER;
TP_TRF_GBN VARCHAR2(2);
L_SERIAL_NUMBER_COPY SERIAL_NUMBER_COPY%rowtype;
cursor C_SERIAL_NUMBER_COPY is
SELECT * FROM SERIAL_NUMBER_COPY WHERE SNT_PARENT = IN_BAR_CODE;
BEGIN
update SERIAL_NUMBER_COPY set SNT_USED_DT = sysdate, SNT_USED_STATUS = 'Y' where (SNT_PARENT = IN_BAR_CODE) or (SNT_ITEM = IN_BAR_CODE) ;
open C_SERIAL_NUMBER_COPY;
while 1 > 0 Loop
fetch C_SERIAL_NUMBER_COPY into L_SERIAL_NUMBER_COPY;
Exit When C_SERIAL_NUMBER_COPY%NOTFOUND;
T_SERIAL_TEMP := L_SERIAL_NUMBER_COPY.SNT_SERIAL;
select sale_qty, dlv_qty, item_code, CUST_CODE, sale_date, dept_code, order_no, seq_no, ip_addr
into T341_SAL_QTY, T341_DLV_QTY, T341_ITEM_CODE, T341_CUST_CODE, T341_SALE_DATE, T341_DEPT_CODE, T341_ORDER_NO, T341_SEQ_NO, T341_IP_ADDR
from TAB341
where STS = '6' and ORDER_NO = IN_ORDER_NO and ITEM_CODE = L_SERIAL_NUMBER_COPY.SNT_PRD_CODE;
select ITEM_GBN INTO T110_ITEM_GBN from TAB110 where ITEM_CODE = T341_ITEM_CODE;
if (T341_SAL_QTY - T341_DLV_QTY) > IN_SUBDLVQTY then
TP_DLV_QTY := IN_SUBDLVQTY;
else
TP_DLV_QTY := (T341_SAL_QTY - T341_DLV_QTY);
end if;
TP_CTOTQTY := TP_DLV_QTY;
select jg_qty,order_no,item_code,prod_order
into T20C_jg_qty,T20C_order_no,T20C_item_code,T20C_prod_order
from TAB20C
where ITEM_CODE = T341_ITEM_CODE and LOT_NO = L_SERIAL_NUMBER_COPY.SNT_LOT_NUMBER and ORDER_NO = IN_ORDER_NO
order by ORDER_NO, ITEM_CODE, PROD_ORDER;
IF TP_CTOTQTY > 0 THEN
IF T20C_jg_qty > TP_CTOTQTY THEN
TP_CTRQTY := TP_CTOTQTY;
ELSE
TP_CTRQTY := T20C_jg_qty;
END IF;
END IF;
UPDATE TAB20C set JG_QTY = JG_QTY - TP_CTRQTY where ORDER_NO = T20C_order_no and ITEM_CODE = T20C_item_code and PROD_ORDER = T20C_prod_order;
TP_CTOTQTY := TP_CTOTQTY - TP_CTRQTY;
select LOT_NO,PROD_NO,PROD_DATE,OT_TEST_NO,PACK_NO,VALIDITY_DATE
INTO T290_LOT_NO,T290_PROD_NO,T290_PROD_DATE,T290_OT_TEST_NO,T290_PACK_NO, T290_VALIDITY_DATE
from TAB290
where PROD_NO = T20C_prod_order AND ITEM_CODE = L_SERIAL_NUMBER_COPY.SNT_PRD_CODE AND LOT_NO = L_SERIAL_NUMBER_COPY.SNT_LOT_NUMBER;
insert into TAB920 (REAL_DATE, REAL_SEQ, PLT_NO, IN_NO,
INNO_SEQ, PACK_NO, JOB_DATE, WH_GBN,
JOB_GBN1, JOB_GBN2, LOC, ITEM_CODE,
ITEM_GBN, CUST_CODE, LOT_NO, JG_QTY,
JOB_QTY, SALE_ITEM, PROD_ORDER, PACK_ORDER,
PROD_DATE, TEST_NO, SALE_DATE, DEPT_CODE,
ORDER_NO, SEQ_NO, REC_GBN, MOVE_WH,
REMARK, COM_NAME, USR_ID)
values (sysdate, p_sysmgr.fn_get_date_seq, '0000', '0',
0, '0', sysdate, 'A11',
'41', '0', null, T341_ITEM_CODE,
T110_ITEM_GBN, T341_CUST_CODE, T290_LOT_NO, 1,
TP_DLV_QTY, null, T290_PROD_NO, T290_PACK_NO,
T290_PROD_DATE, T290_OT_TEST_NO, T341_SALE_DATE, T341_DEPT_CODE,
T341_ORDER_NO, T341_SEQ_NO, '+', null,
T_SERIAL_TEMP, IN_COM_NAME, IN_USR_ID);
update TAB341 set DLV_QTY = DLV_QTY + TP_DLV_QTY
where IP_ADDR = T341_IP_ADDR and SALE_DATE = T341_SALE_DATE and CUST_CODE = T341_CUST_CODE and
DEPT_CODE = T341_DEPT_CODE and ORDER_NO = T341_ORDER_NO and SEQ_NO = T341_SEQ_NO and
ITEM_CODE = T341_ITEM_CODE;
update TAB341 set STS = 'P', TAX_PRT = IN_TAB341B_TAX_PRT,
BILL_PRT = IN_TAB341B_BILL_PRT, LOGS_PRT = IN_TAB341B_LOGS_PRT
where IP_ADDR = T341_IP_ADDR and SALE_DATE = T341_SALE_DATE and CUST_CODE = T341_CUST_CODE and
DEPT_CODE = T341_DEPT_CODE and ORDER_NO = T341_ORDER_NO and SEQ_NO = T341_SEQ_NO and
ITEM_CODE = T341_ITEM_CODE and STS <= '9' and SALE_QTY <= DLV_QTY;
update TAB340 set CMP_QTY = CMP_QTY + TP_DLV_QTY, END_EMP = IN_USR_ID,
END_DATE = sysdate, COM_NAME = IN_COM_NAME,
USR_ID = IN_USR_ID
where SALE_DATE = T341_SALE_DATE and CUST_CODE = T341_CUST_CODE and DEPT_CODE = T341_DEPT_CODE and
ORDER_NO = T341_ORDER_NO and SEQ_NO = T341_SEQ_NO and ITEM_CODE = T341_ITEM_CODE;
select SALE_QTY, CMP_QTY, SALE_PRC INTO T340_SAL_QTY, T340_CMP_QTY, T340_SALE_PRC
from TAB340 where SALE_DATE = T341_SALE_DATE
AND PLANT_CODE = '1000' and CUST_CODE = T341_CUST_CODE and
ORDER_NO = T341_ORDER_NO and SEQ_NO = T341_SEQ_NO and ITEM_CODE = T341_ITEM_CODE;
IF T340_SALE_PRC > 0 THEN
TP_TRF_GBN := '2D';
ELSE
TP_TRF_GBN := '2P';
END IF;
insert into TAB348 (LOG_DATE, LOG_SEQ, TRF_GBN, PLANT_CODE,
ORDER_NO, CUST_CODE, SALE_GBN, SEQ_NO,
LOT_NO, LOT_DATE, EXP_DATE, ITEM_CODE,
TRF_QTY, TRF_PRC, TRF_AMT, STS_GBN,
REMARK, HOST_FLG, BACK_FLG, UPD_DATE,
COM_NAME, USR_ID)
values (sysdate, p_sysmgr.fn_get_date_seq, TP_TRF_GBN, '1000',
T341_ORDER_NO, T341_CUST_CODE, null, T341_SEQ_NO,
T290_LOT_NO, T290_PROD_DATE, T290_VALIDITY_DATE, T341_ITEM_CODE,
1, null, null, null,
T_SERIAL_TEMP, '-', '0', sysdate,
IN_COM_NAME, IN_USR_ID);
insert into SERIAL_TEMP (LOG_DATE, ORDER_NO, ITEM_CODE, LOT_NO, TRF_QTY, serial_temp, COM_ID )
values (sysdate, T341_ORDER_NO, T341_ITEM_CODE, T290_LOT_NO, TP_DLV_QTY, T_SERIAL_TEMP, IN_COM_NAME );
if T340_SAL_QTY <= T340_CMP_QTY then
update TAB340 set STS = '9', COM_NAME = IN_COM_NAME,
USR_ID = IN_USR_ID, END_EMP = IN_COM_NAME
where PLANT_CODE = '1000' and SALE_DATE = T341_SALE_DATE and
CUST_CODE = T341_CUST_CODE and ORDER_NO = T341_ORDER_NO and
SEQ_NO = T341_SEQ_NO and ITEM_CODE = T341_ITEM_CODE;
end if;
delete TAB20C where JG_QTY < 1;
END LOOP;
CLOSE C_SERIAL_NUMBER_COPY;
END;
/
|