inv_period_opening

declare 
l_organization_id             NUMBER :=3688;
l_period_set_name             varchar2(50);
l_accounted_period_type       varchar2(50);
l_last_scheduled_close_date   date;
l_prior_period_open           BOOLEAN;   
l_new_acct_period_id          NUMBER;     
l_duplicate_open_period          BOOLEAN;
l_commit_complete             BOOLEAN:=TRUE;  
l_return_status               VARCHAR2(1);
l_last_period_end_date        date;

cursor acct_period_list
( org_id         in number,
  periodsetname  in varchar2,
  acctperiodtype in varchar2,
  l_last_scheduled_close_date in date
)
is 
SELECT 
    glp.PERIOD_SET_NAME
,  glp.PERIOD_NAME
,  glp.START_DATE
,  glp.END_DATE
,  glp.PERIOD_TYPE ACCOUNTED_PERIOD_TYPE
,  glp.PERIOD_YEAR
,  glp.PERIOD_NUM PERIOD_NUMBER
FROM
  GL_PERIODS glp
WHERE glp.ADJUSTMENT_PERIOD_FLAG        = 'N' 
  AND glp.period_type                   = acctperiodtype
  AND glp.PERIOD_SET_NAME               = periodsetname
  AND glp.PERIOD_NAME NOT IN 
        (  SELECT OAP.PERIOD_NAME  
            FROM  ORG_ACCT_PERIODS OAP
            WHERE OAP.PERIOD_SET_NAME   = glp.PERIOD_SET_NAME
            AND   OAP.PERIOD_NAME       = glp.PERIOD_NAME
            AND   OAP.organization_id   = org_id
        )
  AND glp.end_date >= l_last_scheduled_close_date 
  ORDER BY glp.start_date ASC;

begin

SELECT glsob.period_set_name,accounted_period_type
  into l_period_set_name,l_accounted_period_type
  FROM cst_organization_definitions cod,
       gl_sets_of_books             glsob
WHERE glsob.set_of_books_id       =cod.set_of_books_id
   and cod.organization_id         =l_organization_id;         

SELECT Max(schedule_close_date) 
INTO l_last_scheduled_close_date
FROM org_acct_periods
WHERE organization_id         =l_organization_id; 

dbms_output.put_line('l_period_set_name : '||l_period_set_name);
dbms_output.put_line('l_accounted_period_type : '||l_accounted_period_type);
dbms_output.put_line('l_last_scheduled_close_date : '||l_last_scheduled_close_date);


for per_rec in acct_period_list(l_organization_id,l_period_set_name,l_accounted_period_type,l_last_scheduled_close_date) loop
  
dbms_output.put_line('ACCOUNTED_PERIOD_TYPE : '||per_rec.ACCOUNTED_PERIOD_TYPE);
dbms_output.put_line('PERIOD_SET_NAME : '||per_rec.PERIOD_SET_NAME);
dbms_output.put_line('PERIOD_NAME : '||per_rec.PERIOD_NAME);
dbms_output.put_line('PERIOD_YEAR : '||per_rec.PERIOD_YEAR);
dbms_output.put_line('PERIOD_NUMBER : '||per_rec.PERIOD_NUMBER);

         CST_AccountingPeriod_PUB.open_period
            (  p_api_version                => 1.0
            ,  p_org_id                     => l_organization_id  
            ,  p_user_id                    => -1  
            ,  p_login_id                   => -1  
            ,  p_acct_period_type           => per_rec.ACCOUNTED_PERIOD_TYPE
            ,  p_org_period_set_name        => per_rec.PERIOD_SET_NAME 
            ,  p_open_period_name        => per_rec.PERIOD_NAME
            ,  p_open_period_year        => per_rec.PERIOD_YEAR
            ,  p_open_period_num            => per_rec.PERIOD_NUMBER
            ,  x_last_scheduled_close_date  => l_last_scheduled_close_date
            ,  p_period_end_date           =>  per_rec.end_date                   
            ,  x_prior_period_open          => l_prior_period_open           
            ,  x_new_acct_period_id         => l_new_acct_period_id          
            ,  x_duplicate_open_period        => l_duplicate_open_period    
            ,  x_commit_complete            => l_commit_complete  
            ,  x_return_status              => l_return_status            
            ) ;

      IF l_return_status <> FND_API.g_ret_sts_success THEN
         dbms_output.put_line('Error while opening period ');
      ELSE
         dbms_output.put_line('Period(s) Opened Successfully');
     COMMIT;
      END IF;

    end LOOP;
   end;