Oracle EBS: reopen closed Inventory period

 標準功能不支援, 但可用SQL達成

--

DISCLAIMER: THE RE-OPENING OF A CLOSED INVENTORY PERIOD COULD POTENTIALLY CAUSE DATA CORRUPTION AND ANY DATA CORRUPTION CAUSED BY RE-OPENING A CLOSED INVENTORY PERIOD WILL BE THE RESPONSIBILITY OF THE CUSTOMER AND NO DATA FIX WILL BE PROVIDED FOR ANY DATA CORRUPTION THAT HAS BEEN CAUSED BY RE-OPENING A CLOSED PERIOD.

TEST THOROUGHLY ALL SCRIPTS ON A NON-PRODUCTION INSTANCE, FIRST BACKING UP ALL TABLE DATA PRIOR TO IMPLEMENTING IN PRODUCTION.

IF THERE IS CONCERN THAT RE-OPENING A CLOSED PERIOD MAY CAUSE DATA CORRUPTION PLEASE OPEN AN SR WITH ORACLE SUPPORT PRIOR TO RE-OPENING A CLOSED PERIOD.

-- A script to list all inventory periods for a specific organization

-- A script to reopen closed inventory accounting periods

-- The script will reopen all inventory periods for the specified 

-- Delete scripts to remove the rows created during the period close process to prevent duplicate rows

-- organization starting from the specified accounting period. 

-- The organization_id can be obtained from the MTL_PARAMETERS table. 

-- The acct_period_id can be obtained from the ORG_ACCT_PERIODS table.

1. Backup the following tables:

 org_acct_periods, mtl_period_summary, mtl_period_cg_summary, mtl_per_close_dtls and cst_period_close_summary.

 

2. SELECT acct_period_id period, open_flag, period_name name,

period_start_date, schedule_close_date, period_close_date

FROM org_acct_periods

WHERE organization_id = &org_id

order by 1,2;

3. UPDATE org_acct_periods

SET open_flag = 'Y',

period_close_date = NULL,

summarized_flag = 'N'

WHERE organization_id = &&org_id

AND acct_period_id >= &&acct_period_id;

DELETE mtl_period_summary

WHERE organization_id = &org_id

AND acct_period_id >= &acct_period_id;

DELETE mtl_period_cg_summary

WHERE organization_id = &org_id

AND acct_period_id >= &acct_period_id;

DELETE mtl_per_close_dtls

WHERE organization_id = &org_id

AND acct_period_id >= &acct_period_id;

DELETE cst_period_close_summary

WHERE organization_id = &org_id

AND acct_period_id >= &acct_period_id;

4. commit

5.Re-summarize all periods after problematic period again in order by running 'Period Close Reconciliation Report'.

Note:

The tables,  mtl_period_summary, mtl_period_cg_summary and mtl_per_close_dtls are designed in 11i. After R12 upgrade they were not used.

But as the script is available from earlier days, those tables were kept like that without deleting from the resummarization script.

they can ignore these tables  now.

The table cst_period_close_summary is the only table used in R12. The PCRR report picks the records from this table.

Ref:

1.Re-Open a Closed Inventory Accounting Period (Doc ID 472631.1)