cursor c1 is select qtr_type, b.locality, sector, block, qtrno, area, c.hid, form_no, acc_status, doi, doo, dov
	    from house_mirror a, locality b, OL_LCY_NDC_OCC c 
	    where a.lcode=b.lcode and a.hid=c.hid and form_no in 
	    (select form_no from OL_LCY_NDC where aan=:select.aan and event_id='ACL') order by doi; 

select count(*) into cnt from OL_LCY_NDC_occ 
      where form_no in (select form_no from ol_lcy_ndc where aan=:select.aan and event_id='ACL');
if cnt > 0 then
go_block('OL_LCY_NDC_OCC');
     clear_block(no_validate);
       first_record;
         open c1;
            loop
	  fetch c1 into
	 :OL_LCY_NDC_OCC.qtrtype,
	 :OL_LCY_NDC_OCC.locality,
	 :OL_LCY_NDC_OCC.sector,
	 :OL_LCY_NDC_OCC.block,
	 :OL_LCY_NDC_OCC.house_no,
	 :OL_LCY_NDC_OCC.area, 
	 :OL_LCY_NDC_OCC.hid,
	 :OL_LCY_NDC_OCC.form_no,
	 :OL_LCY_NDC_OCC.acc_status,
	 :OL_LCY_NDC_OCC.doi,
   :OL_LCY_NDC_OCC.doo,
	 :OL_LCY_NDC_OCC.dov;
	 
	 exit when c1%notfound;
			 next_record;
	      end loop;
	       close c1;
         first_record;
end if;  

Declare
	cnt number;
	form_ndc number;
 -- your varchar2(50);

Begin
--	:global.ur:= get_application_property(username);

	-------UPDATE OR INSERT INTO TABLE OL_LCY_REFERENCE-------------
--	select count(*) into cnt from ol_lcy_ndc where aan=:select.aan and event_id= 'ACL';

 select count(*) into cnt from ol_lcy_reference where rent_section='ACS' and year=:control.year 
                                                   and volume_no=:control.volume and page_no=:control.page_no;
	if cnt = 0  then
	 	insert into OL_LCY_REFERENCE (form_no, rent_section, year, volume_no, page_no)
	 	values (GAMS. OL_REG_FORMNO_SEQ.NEXTVAL, 'ACS', :control.year, :control.volume, :control.page_no);
	 commit;
	 	
	 
	 	
	 	select form_no into form_ndc from OL_LCY_REFERENCE where rent_section='ACS' and year=:control.year 
                                                   and volume_no=:control.volume and page_no=:control.page_no;
    
    
	 	insert into OL_LCY_NDC (form_no, aan, regno, event_id, doev, status, edt, ludt, username)
	 	values (form_ndc, :select.aan,:select.regno,'ACL', SYSDATE, 'DRAFT', SYSDATE, SYSDATE,'' );
	 commit;  
	 	
	 	go_block('OL_LCY_NDC');
    execute_query;
	 	
	 	go_block('CONTROL');
	 	
	 	message('Now you can proceed with selecting house details '); message('.');                                               
  
 :masterbuttons.section:='ACS';
 :masterbuttons.year:=:control.YEAR;
 :masterbuttons.volume:=:control.volume;
 :masterbuttons.page_no:=:control.page_no;
	 
	else 

	 	message('This Ledger Reference is already recorded. '); message('.');
	 
	end if;
  end;