CREATE OR REPLACE PROCEDURE SP_C_TXYYMM(VAPPLY_NO IN VARCHAR2,VUSER_NM IN VARCHAR2,VFAC_NO IN VARCHAR2)

AS

BEDATE VARCHAR2(10);

VTXKQ_NO VARCHAR2(2);

VITEM VARCHAR2(1);

EDDATE1 VARCHAR2(10);

VYEARPER VARCHAR2(4);

BEDATE1 VARCHAR2(10);

EDDATE VARCHAR2(10);

I INT;

ZZ INT;

NN INT;

CC INT;

DD INT;

CURSOR TXKQ IS SELECT START_DAY,END_DAY ,TXKQ_NO,YEARPER,APPLY_ITEM FROM A_QH_LEAVEAPPLY_DETAIL WHERE APPLY_NO=VAPPLY_NO;

BEGIN

OPEN TXKQ;

LOOP

FETCH TXKQ INTO BEDATE,EDDATE,VTXKQ_NO,VYEARPER,VITEM;

EXIT WHEN TXKQ%NOTFOUND;

SELECT MONTHS_BETWEEN (TO_DATE(SUBSTR(EDDATE,1,7),'YYYY/MM'), TO_DATE(SUBSTR(BEDATE,1,7),'YYYY/MM') ) INTO ZZ FROM DUAL;

FOR I IN 0..ZZ LOOP

BEDATE1:=TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(BEDATE,1,7),'YYYY/MM'),I),'YYYY/MM')||'/01';

EDDATE1:=TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR(BEDATE,1,7),'YYYY/MM'),I),'YYYY/MM')||'/'||TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(SUBSTR(BEDATE,1,8)||'01','YYYY/MM/DD'),I)),'DD');

IF I=0 THEN

BEDATE1:=BEDATE;

END IF;

IF I=ZZ THEN

EDDATE1:=EDDATE;

END IF;

SELECT COUNT(*) A INTO CC FROM C_TXYYMM TX, A_QH_LEAVEAPPLY_DETAIL QH

WHERE (TX.FAC_NO=VFAC_NO OR TX.FAC_NO IN('HR','HGHR')) AND TX.FAC_NO=QH.FAC_NO AND TX.PNL=QH.PNL

AND BEDATE1||QH.START_TIME=SUBSTR(TX.YYMM,1,7)||'/'||TX.DAY_START

AND QH.APPLY_NO=VAPPLY_NO;

IF CC=0 THEN

IF VTXKQ_NO='3' THEN

INSERT INTO C_TXYYMM

SELECT DISTINCT A.FAC_NO,SUBSTR(BEDATE1,1,7)YYMM,A.PNL,B.SEC_NO,A.TXKQ_NO,

SUBSTR(BEDATE1,9,2)||A.START_TIME DAY_STAR,SUBSTR(EDDATE1,9,2)||A.END_TIME DAY_END

,NULL STATEMENT,DECODE(A.YEARPER,'',NULL,A.YEARPER) YEARPER,

DECODE(A.PERDAY1,'',NULL,A.PERDAY1) PERDAY1,

DECODE(A.PERDAY2,'',NULL,A.PERDAY2) PERDAY2 ,VUSER_NM,SYSDATE

FROM A_QH_LEAVEAPPLY_DETAIL A,P_PERSON B

WHERE (B.FAC_NO=VFAC_NO OR B.FAC_NO IN('HR','HGHR')) AND A.FAC_NO=B.FAC_NO AND A.PNL=B.PNL

AND APPLY_NO IN (SELECT APPLY_NO FROM APPLY

WHERE APPLY_NO=VAPPLY_NO)

AND B.INOUT_MK<>'Y' AND A.TXKQ_NO=VTXKQ_NO AND A.YEARPER=VYEARPER;

COMMIT;

ELSE

INSERT INTO C_TXYYMM

SELECT DISTINCT A.FAC_NO,SUBSTR(BEDATE1,1,7)YYMM,A.PNL,B.SEC_NO,A.TXKQ_NO,

SUBSTR(BEDATE1,9,2)||A.START_TIME DAY_STAR,SUBSTR(EDDATE1,9,2)||A.END_TIME DAY_END

,NULL STATEMENT,DECODE(A.YEARPER,'',NULL,A.YEARPER) YEARPER,

DECODE(A.PERDAY1,'',NULL,A.PERDAY1) PERDAY1,

DECODE(A.PERDAY2,'',NULL,A.PERDAY2) PERDAY2 ,VUSER_NM,SYSDATE

FROM A_QH_LEAVEAPPLY_DETAIL A,P_PERSON B

WHERE (A.FAC_NO=VFAC_NO OR A.FAC_NO IN('HR','HGHR')) AND A.FAC_NO=B.FAC_NO AND A.PNL=B.PNL

AND APPLY_NO IN (SELECT APPLY_NO FROM APPLY

WHERE APPLY_NO=VAPPLY_NO AND APPLY_ITEM=VITEM)

AND B.INOUT_MK<>'Y' AND A.TXKQ_NO=VTXKQ_NO;

COMMIT;

END IF;

ELSE

UPDATE C_TXYYMM C

SET (C.DAY_END,DATE_TIME,USER_NAME)=

(SELECT SUBSTR(EDDATE1,9,2)||END_TIME,SYSDATE,VUSER_NM

FROM A_QH_LEAVEAPPLY_DETAIL A

WHERE (C.FAC_NO=VFAC_NO OR C.FAC_NO IN('HR','HGHR')) AND C.FAC_NO=A.FAC_NO AND C.PNL=A.PNL AND A.APPLY_NO=VAPPLY_NO)

WHERE ( C.FAC_NO=VFAC_NO OR C.FAC_NO IN('HR','HGHR'))

AND EXISTS ( SELECT A.PNL FROM A_QH_LEAVEAPPLY_DETAIL A

WHERE (A.FAC_NO=VFAC_NO OR A.FAC_NO IN('HR','HGHR') )AND A.APPLY_NO=VAPPLY_NO

AND A.FAC_NO=C.FAC_NO AND A.PNL=C.PNL AND

C.YYMM||'/'||C.DAY_START=BEDATE1||A.START_TIME );

COMMIT;

END IF;

END LOOP;

END LOOP;

CLOSE TXKQ;

SELECT MONTHS_BETWEEN (TO_DATE(SUBSTR(EDDATE,1,7),'YYYY/MM'), TO_DATE(SUBSTR(BEDATE,1,7),'YYYY/MM') ) INTO NN FROM DUAL;

SELECT COUNT(*) INTO DD FROM A_QH_LEAVEAPPLY_DETAIL WHERE APPLY_NO=VAPPLY_NO AND (START_TIME='1300' OR END_TIME='1200') ;

-- IF (NN>0) THEN

IF (NN>0 AND DD>0) THEN

UPDATE C_TXYYMM C

SET (C.DAY_END)=

(SELECT SUBSTR(C.DAY_END,1,2)||'1700'

FROM A_QH_LEAVEAPPLY_DETAIL A

WHERE (C.FAC_NO=VFAC_NO OR C.FAC_NO IN('HR','HGHR')) AND C.FAC_NO=A.FAC_NO AND C.PNL=A.PNL AND A.APPLY_NO=VAPPLY_NO)

WHERE (C.FAC_NO=VFAC_NO OR C.FAC_NO IN('HR','HGHR'))

AND EXISTS ( SELECT A.PNL FROM A_QH_LEAVEAPPLY_DETAIL A

WHERE (A.FAC_NO=VFAC_NO OR A.FAC_NO IN('HR','HGHR')) AND A.APPLY_NO=VAPPLY_NO

AND A.FAC_NO=C.FAC_NO AND A.PNL=C.PNL

AND C.TXKQ_NO=A.TXKQ_NO AND SUBSTR(A.START_DAY,1,7)=C.YYMM

);

UPDATE C_TXYYMM C

SET (C.DAY_START)=

(SELECT SUBSTR(C.DAY_START,1,2)||'0730'

FROM A_QH_LEAVEAPPLY_DETAIL A

WHERE (C.FAC_NO=VFAC_NO OR C.FAC_NO IN('HR','HGHR')) AND C.FAC_NO=A.FAC_NO AND C.PNL=A.PNL AND A.APPLY_NO=VAPPLY_NO)

WHERE (C.FAC_NO=VFAC_NO OR C.FAC_NO IN('HR','HGHR'))

AND EXISTS ( SELECT A.PNL FROM A_QH_LEAVEAPPLY_DETAIL A

WHERE (A.FAC_NO=VFAC_NO OR C.FAC_NO IN('HR','HGHR')) AND A.APPLY_NO=VAPPLY_NO

AND A.FAC_NO=C.FAC_NO AND A.PNL=C.PNL

AND C.TXKQ_NO=A.TXKQ_NO AND SUBSTR(A.START_DAY,1,7)<C.YYMM

);

END IF;

/* UPDATE d_free_meat SET MEAT_MK='1' where free_mk<>'A' AND MEAT_MK='0';

UPDATE d_free_meat SET MEAT_MK='0' where free_mk='A' AND MEAT_MK<>'0' ; */

END;