Open Closed inventory period

GOAL

If an Inventory Accounting Period has been closed prematurely by accident the following scripts can be used to re-open the accounting period if the corresponding GL period is open.  Re-Opening a closed period will allow transactions to be process for that period.

The re-opening of a closed period should not be used to back date transactions, the system allow back dated transactions but this may cause discrepancies between inventory and GL. Any discrepancies caused by back dated transactions are not supported by Oracle and would have to be resolved with a manual adjustment to the General Ledger.

SOLUTION

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 in 11.5.10 
-- 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.

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;



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;



commit

Accounting Period does not exist in the value set CST_SRS_ORG_PERIODS.

CAUSE

Issue with CST_SRS_ORGANIZATIONS value set.

The following justifies how the issue is related to this specific customer:
 In Oracle Cost Management, not able to submit Period Close
 Pending Txn report with the intended parameters. Get error:
" ID 1 for the flexfield segment Accounting Period does not exist in the value set CST_SRS_ORG_PERIODS."

This is explained in the following bug:
 Bug 10638664 : TST212:GETTING ERROR UNABLE TO RUN PERIOD CLOSE PENDING TXN REPORT
 

SOLUTION

To implement the solution, please execute the following steps:

1. Download and review the readme and pre-requisites for Patch. 10638664:R12.BOM.C

2. Ensure that you have taken a backup of your system before applying
the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
        bomprg.ldt 120.117.12010000.32

You can use the commands like the following:
        strings -a $XX_TOP/filename |grep '$Header'

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.