Oracle_ Autograph proc

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 a 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) | | ' Yyyy/mm/dd '), (I)), ' DD ');
IF I=0 Then
Bedate1:=bedate;
END IF;
IF I=zz Then
Eddate1:=eddate;
END IF;
SELECT COUNT (*) A to 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 ')) to 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 an D 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;

Oracle_ Autograph proc