Oracle Procedure

CREATE OR REPLACE PROCEDURE MOMQM.MMM_APP_INSP
(
V_DIVISION in VARCHAR2,
V_JISOCD in VARCHAR2,
V_IMPBNO in VARCHAR2,
V_WORKGBN in VARCHAR2,
V_SEQ in NUMBER,
V_STATUS in VARCHAR2,
V_YPBNO in VARCHAR2,
V_EMPCD1 in VARCHAR2,
V_EMPCD2 in VARCHAR2,
V_EMPCD3 in VARCHAR2,
V_PXYSTATUS1 in VARCHAR2,
V_PXYSTATUS2 in VARCHAR2,
V_APPLDT in VARCHAR2,
V_PASSDT in VARCHAR2,
V_APPRDT in VARCHAR2
)
IS
ncount NUMBER;
MY_OLD_STATUS VARCHAR2(2);
MY_USER VARCHAR2(20);
MY_DATE VARCHAR2(32);
MY_SEQ VARCHAR2(64);

BEGIN

MY_OLD_STATUS :='';
MY_USER :='';
MY_DATE :='';
MY_SEQ :='';

SELECT count(*) INTO ncount
FROM APPROVAL_INSP
WHERE DIVISION = V_DIVISION
AND JISOCD = V_JISOCD
AND IMPBNO = V_IMPBNO
AND WORKGBN = V_WORKGBN
AND SEQ = V_SEQ;



SELECT LOWER(USER_ID)
INTO MY_USER
FROM MQM_QRNT_OFCR
WHERE QRNT_OFCR_CD = V_EMPCD1;

SELECT TO_CHAR(SYSDATE, 'YYMMDDHHMISS') INTO MY_DATE FROM dual;

MY_SEQ := V_JISOCD || V_IMPBNO || 'APRV' || MY_DATE ;

IF ncount>0 THEN


-- 예전이 상태
SELECT STATUS INTO MY_OLD_STATUS
FROM APPROVAL_INSP
WHERE DIVISION = V_DIVISION
AND JISOCD = V_JISOCD
AND IMPBNO = V_IMPBNO
AND WORKGBN = V_WORKGBN
AND SEQ = V_SEQ;


UPDATE APPROVAL_INSP
SET
STATUS = V_STATUS,
YPBNO =V_YPBNO,
EMPCD1 =V_EMPCD1,
EMPCD2 =V_EMPCD2,
EMPCD3 =V_EMPCD3,
PXYSTATUS1 = V_PXYSTATUS1,
PXYSTATUS2 =V_PXYSTATUS2,
APPLDT =V_APPLDT,
PASSDT =V_PASSDT,
APPRDT =V_APPRDT
WHERE DIVISION = V_DIVISION
AND JISOCD = V_JISOCD
AND IMPBNO = V_IMPBNO
AND WORKGBN = V_WORKGBN
AND SEQ = V_SEQ;

COMMIT;

-- 결재가 완료 된거임
IF MY_OLD_STATUS <> 'SD' AND V_STATUS = 'SD' THEN

MOMQM.Insp_Req_Call(V_DIVISION, MY_SEQ, V_JISOCD, V_IMPBNO, MY_USER, 'APPROVAL_AUTO_REQ');
COMMIT;

END IF;

-- 경유면서 완료된것
IF MY_OLD_STATUS <> 'PD' AND V_STATUS = 'PD' THEN

IF V_PXYSTATUS2= 'X' THEN

MOMQM.Insp_Req_Call(V_DIVISION, MY_SEQ, V_JISOCD, V_IMPBNO, MY_USER, 'APPROVAL_AUTO_REQ');
COMMIT;

END IF;

END IF;


ELSE


INSERT INTO APPROVAL_INSP
(DIVISION, JISOCD , IMPBNO , WORKGBN , SEQ , STATUS , YPBNO , EMPCD1 , EMPCD2 , EMPCD3, PXYSTATUS1 , PXYSTATUS2, APPLDT , PASSDT , APPRDT)
VALUES
(V_DIVISION, V_JISOCD , V_IMPBNO , V_WORKGBN , V_SEQ , V_STATUS , V_YPBNO , V_EMPCD1 , V_EMPCD2 , V_EMPCD3, V_PXYSTATUS1 , V_PXYSTATUS2, V_APPLDT , V_PASSDT , V_APPRDT);

IF V_STATUS = 'SD' THEN

MOMQM.Insp_Req_Call(V_DIVISION, MY_SEQ, V_JISOCD, V_IMPBNO, MY_USER, 'APPROVAL_AUTO_REQ');
COMMIT;

END IF;



END IF;

COMMIT;

END;
/