Tuesday, 17 July 2012

Script to open Inventory Accouting Periods - Oracle Apps Inventory


Background: 
An accounting period must be open for you to complete a transaction; that is, the transaction date you enter must fall within the beginning and ending dates you define for the period.

1. How will we Open Inventory Accounting period from the application?
Inventory > Accounting Close Cycle > Inventory Accounting Periods > Select Organization > navigate to Open Accounting Period Form > Select Period you want to Open > Select the button "Change Status"

2. What this script does?
This script is useful when we need to open a bunch of Inventory  accounting periods for an Organization. We need to provide ORGANIZATION_ID as the input parameter to the script. This opens all unopened inventory accounting periods from the past till the current period.

declare
l_organization_id             NUMBER :=&org_id;
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;

No comments:

Post a Comment

Post a Comment