[转]批量打开 Inventory Periods Script

转自： http://blog.csdn.net/pan_tian/article/details/8935481

 

某个库存组织下，发现有很多库存期间都未打开，而一个一个的开期间比较麻烦，可以使用这个脚本来批量打开库存期间

[sql] view plain copy print ?

--------------------------------------------------------- 
--l_org_id为 organization's Organization_ID 
--eg. select * from mtl_parameters where organization_code = 'PR3'; 
--------------------------------------------------------- 
 
DECLARE 
l_org_id NUMBER:=1382; --Replace Your Organization_ID to here. 
l_org_code VARCHAR2(5); 
l_acct_period_id NUMBER; 
l_duplicate_open_period BOOLEAN; 
l_commit_complete BOOLEAN; 
l_date DATE; 
l_open BOOLEAN; 
l_period_set_name varchar2(20); 
l_period_type varchar2(20); 
l_return_status VARCHAR2(3); 
CURSOR l_period IS SELECT   STATUS               , 
         PERIOD_NAME          , 
         PERIOD_NUMBER        , 
         PERIOD_YEAR          , 
         START_DATE           , 
         END_DATE             , 
         CLOSE_DATE           , 
         REC_TYPE             , 
         ORGANIZATION_ID      , 
         ACCT_PERIOD_ID       , 
         ROW_ID               , 
         ACCOUNTED_PERIOD_TYPE, 
         PERIOD_SET_NAME      , 
         LAST_UPDATE_DATE     , 
         CREATION_DATE        , 
         LAST_UPDATED_BY      , 
         CREATED_BY           , 
         LAST_UPDATE_LOGIN 
FROM     ORG_ACCT_PERIODS_V 
WHERE 
         ( 
                  ( 
                           rec_type        = 'ORG_PERIOD' 
                       AND organization_id = l_org_id 
                  ) 
               OR 
                  ( 
                           rec_type              = 'GL_PERIOD' 
                       AND period_set_name       = l_period_set_name 
                       AND accounted_period_type = l_period_type 
                       AND 
                           ( 
                                    PERIOD_YEAR,PERIOD_NAME 
                           ) 
                           NOT IN 
                           (SELECT PERIOD_YEAR, 
                                  PERIOD_NAME 
                           FROM   ORG_ACCT_PERIODS 
                           WHERE  ORGANIZATION_ID = l_org_id 
                           ) 
                       AND to_date(end_date) >= (SELECT Max(end_date) FROM ORG_ACCT_PERIODS_V WHERE   rec_type        = 'ORG_PERIOD' 
                       AND organization_id = l_org_id AND status='Open') 
                  ) 
         ) ORDER BY start_date ; 
BEGIN 
   IF(l_org_code IS NOT NULL) THEN 
   SELECT ORGANIZATION_ID INTO l_org_id FROM mtl_parameters WHERE ORGANIZATION_CODE=l_org_code; 
    END IF; 
     select B.PERIOD_SET_NAME, 
            B.ACCOUNTED_PERIOD_TYPE 
     into l_period_set_name, 
           l_period_type 
     from   ORG_ORGANIZATION_DEFINITIONS A, 
            GL_SETS_OF_BOOKS B 
     where  A.ORGANIZATION_ID = l_org_id 
       and  A.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID; 
 
FOR l_rec IN l_period LOOP 
 
 IF(l_rec.status='Future' AND l_rec.start_date 
 cst_accountingperiod_pub.OPEN_PERIOD 
 (1, 
 l_org_id, 
1068, 
1068, 
l_rec.ACCOUNTED_PERIOD_TYPE, 
l_rec.PERIOD_SET_NAME, 
l_rec.period_name, 
l_rec.PERIOD_YEAR, 
l_rec.period_number, 
l_date, 
l_rec.end_date, 
l_open, 
l_acct_period_id, 
l_duplicate_open_period, 
l_commit_complete, 
l_return_status); 
Dbms_Output.put_line('Opened Period:'||l_rec.period_name); 
END IF; 
END LOOP; 
END; 
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24627116/viewspace-772189/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24627116/viewspace-772189/