Previous new Org Periods if needed to close

Previous new Org Periods if needed to close  :


exec apps.mpfl_OldPeriod_cancelation();


CREATE OR REPLACE procedure apps.mpfl_OldPeriod_cancelation
as

    l_verify_flag char(1);
    l_error_message varchar2(3000);


    cursor h_ORGANIZATION is
   
 select distinct o.ORGANIZATION_CODE,o.ORGANIZATION_ID, o.ORGANIZATION_NAME from org_acct_periods p, org_organization_definitions o
where p.OPEN_FLAG='Y'
and p.ORGANIZATION_ID=o.ORGANIZATION_ID
AND trunc(SCHEDULE_CLOSE_DATE) <TRUNC(ADD_MONTHS(sysdate,-1))

    ;


cursor l_ACCT_PERIOD_ID (ORGANIZATION_ID varchar2) is
   
  SELECT * FROM org_acct_periods p WHERE p.ORGANIZATION_ID=ORGANIZATION_ID AND trunc(SCHEDULE_CLOSE_DATE) <TRUNC(ADD_MONTHS(sysdate,-1)) and p.OPEN_FLAG='Y'
order by ACCT_PERIOD_ID asc

;


begin

for h1 in h_ORGANIZATION loop

    l_verify_flag := 'Y';
    l_error_message := null;


begin

for l1 in l_ACCT_PERIOD_ID (h1.ORGANIZATION_ID) loop

begin



update org_acct_periods p set OPEN_FLAG='N', PERIOD_CLOSE_DATE=to_char(to_date(sysdate,'dd-mon-yyyy')), SUMMARIZED_FLAG='Y'
where  ACCT_PERIOD_ID=l1.ACCT_PERIOD_ID;

commit;

exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Data not migrated into ap_invoices_interface table...' ;

end;
end loop;
end;
end loop;
end;