Please how can i create procedure with the below PL/SQL


Set termout off
set heading off
set verify off
set pagesize 0
SET pages 1000
set lines 500
set trims on
set feedback off
set serveroutput on size 1000000
set colsep |


spool 'C:\Users\olumuyiwa.akusebo\Desktop\SQL_SCRIPTS\pr oxy.txt'

Declare

vforacid tbaadm.gam.foracid%type;
vproxyacid tbaadm.ott.acid%type;
vtranid tbaadm.ott.tran_id%type;
vtrandate date;
vparttype tbaadm.ott.PART_TRAN_TYPE%type;
vorgacid tbaadm.ott.ORG_ACID%type;
vpartsrlnum tbaadm.ott.PART_TRAN_SRL_NUM%type;
vproxyacct tbaadm.gam.foracid%type;
vorgaccount tbaadm.gam.foracid%type;
vuserid tbaadm.ott.RCRE_USER_ID%type;
vstaffname tbaadm.get.EMP_NAME%type;
vacctname tbaadm.gam.acct_name%type;
vorgamt tbaadm.ott.org_tran_amt%type;
vsolid tbaadm.ott.sol_id%type;
vcheck number;
vnarratn tbaadm.ott.tran_particular%type;

CURSOR cot_maint IS
select gam.sol_id,ott.acid,tran_id,tran_date,PART_TRAN_TY PE,ORG_ACID,PART_TRAN_SRL_NUM,org_tran_amt,tran_pa rticular
from tbaadm.ott,tbaadm.gam
where ott.acid=gam.acid
---and ott.sol_id='014'
and gam.sol_id in (select sol_id from tbaadm.sst where set_id='ALL')
and bacid='4545230001'
---and tran_date='11-sep-2012'
and org_tran_amt <>total_offset_amt;

BEGIN

open cot_maint;
loop
fetch cot_maint into vsolid,vproxyacid,vtranid,vtrandate,vparttype,vorg acid,vpartsrlnum,vorgamt,vnarratn;
exit when cot_maint%notfound;

---Confirm that Records have not been reversed
Begin
select count(1) into vcheck from tbaadm.tct
where trim(tran_id)=trim(vtranid)
and tran_date=vtrandate
and trim(part_tran_srl_num) =trim(vpartsrlnum)
and AMT_OFFSET=vorgamt;
End;

If vcheck = 0 then

--Get Accounts
begin
select foracid into vproxyacct from tbaadm.gam
where acid=vproxyacid;
exception
when no_data_found then
vproxyacct:='null';
end;

begin
select foracid,acct_name into vorgaccount,vacctname from tbaadm.gam
where acid=vorgacid;
exception
when no_data_found then
vorgaccount:='null';
vacctname:='null';
end;

--Staff Name
Begin
select ENTRY_USER_ID into vuserid from tbaadm.htd
where trim(tran_id)=trim(vtranid)
and tran_date=vtrandate
and trim(PART_TRAN_SRL_NUM)=trim(vpartsrlnum);
exception
when no_data_found then
vuserid:='null';
End;

if vuserid ='null' then
vstaffname:=null;
else
Begin
select EMP_NAME into vstaffname FROM tbaadm.GET
where EMP_ID= vuserid;
exception
when no_data_found then
vstaffname:=vuserid;
End;
end if;
dbms_output.put_line(vsolid||'|'||vproxyacct||'|'| |vorgaccount||'|'||vacctname||'|'||vorgamt||'|'||v tranid||'|'||vtrandate||'|'||vparttype||'|'||vpart srlnum||'|'||vuserid||'|'||vstaffname||'|'||vnarra tn);

End If;
end loop;
close cot_maint;
End;
/
spo off