Sp_approvalset_overtime inserting a single

CREATE OR REPLACE PROCEDURE sp_approvalset_overtime (vapply_no varchar2,
Vapplykind_no varchar2,vfac_no varchar2,vappdept_no varchar2
, Vdutyrank_no varchar2,vbuilding varchar2, Vsec_no varchar2,vapply_date varchar2
, Vapplyuser_no varchar2,vexception_mk varchar2)
As
Vvapplykind_no VARCHAR2 (2);
Vvfac_no VARCHAR2 (4);
Vvbuilding_no VARCHAR2 (3);
Vvdeptkind_no VARCHAR2 (5);
Vvdutyrank_no varchar2 (1);
Vvapproval_sort number;
Vvapprovalrank_no VARCHAR2 (2);
Vvappdept_no varchar2 (20);
Vvapproval_type varchar2 (1);
VVAPPROVAL_MK varchar2 (1);
Vappdept VARCHAR2 (5);

CURSOR MyCursor is
SELECT A.applykind_no,a.fac_no,a.building_no,a.deptkind_no,d.approval_sort,d.appdept_no,
D.approval_type,d.approval_mk,d.approvalrank_no
From Approvalset_master A,approvalset_detail D
WHERE A.applykind_no=vapplykind_no
and A.fac_no=vfac_no
and a.building_no=vbuilding
and A.EXCEPTION_MK=VEXCEPTION_MK
and a.deptkind_no= (SELECT deptkind_no from appdept WHERE appdept_no=vappdept_no and Fac_no=vfac_no)
--and a.dutyrank_no= '
and A.applykind_no=d.applykind_no
and A.fac_no=d.fac_no
and A.building_no=d.building_no
and A.deptkind_no=d.deptkind_no
and A.EXCEPTION_MK=D.EXCEPTION_MK;
BEGIN
OPEN MyCursor;
LOOP
FETCH mycursor into Vvapplykind_no,vvfac_no,vvbuilding_no,vvdeptkind_no,[email protected]_no,
Vvapproval_sort,vvappdept_no,vvapproval_type,vvapproval_mk,vvapprovalrank_no;
EXIT when Mycursor%notfound;
DELETE from Approval_set WHERE apply_no=vapply_no and Appdept_sort=vvapproval_sort;
DELETE from the APPLY WHERE apply_no=vapply_no;
COMMIT;
IF vvapproval_mk= ' Y ' then
Vappdept:=vappdept_no;
ELSE
Vappdept:= Vvappdept_no;
END IF;

INSERT into Approval_set (APPLY_NO,APPDEPT_SORT,APPLYKIND_NO,APPROVALRANK_NO,FAC_NO,APPDEPT_NO,APPROVAL_MK, Approval_type)
VALUES (Vapply_no,vvapproval_sort,vvapplykind_no,vvapprovalrank_no,vvfac_no,vappdept,vvapproval_mk,vvapproval_ TYPE);
COMMIT;
INSERT into APPLY (Apply_no,apply_content,apply_date,fac_no,sec_no,applydept_no,applykind_no,dutyrank_no, APPLYUSER_NO,APPROVALFINISH_MK)
VALUES (vapply_no,vvfac_no| | vsec_no| | Vappdept,vapply_date,vvfac_no,vsec_no,vappdept,vvapplykind_no,vdutyrank_no,vapplyuser_no, ' N ');
COMMIT;

END LOOP;
CLOSE MyCursor;
COMMIT;
End

Sp_approvalset_overtime inserting a single