Case 00076674 – GL Period Status Reset from Frozen to Never Opened

GL Period Status Reset from Frozen to Never Opened

Case Number	00076674
Oracle Module	General Ledger (GL)
EBS Version	R12
Functional Area	Period Management / GL Period Statuses
Related Patches	None

Symptoms

GL periods for a range of future or unused accounting periods were incorrectly set to status F (Frozen) in GL_PERIOD_STATUSES.
Transactions cannot be posted to these periods because they are frozen, but the periods should not yet have been opened or have had no activity.
The periods need to be reset to N (Never Opened) so they can be opened normally through the period management process.

Root Cause

GL periods were erroneously placed into Frozen status (closing_status = 'F') — possibly through a bulk period close operation or administrative error — for periods that contain no actual transaction data. Resetting the closing status to N (Never Opened) allows the periods to be opened through normal GL period management.

Resolution

Step 1 – Verify no transaction data exists

Run the data check queries to confirm all counts return zero for the affected periods across all transaction sources:

SELECT COUNT(*) FROM ap_invoices_all WHERE gl_date >= '<start_date>';
SELECT COUNT(*) FROM ra_cust_trx_line_gl_dist_all WHERE gl_date >= '<start_date>';
SELECT COUNT(*) FROM ap_invoice_distributions_all WHERE accounting_date >= '<start_date>';
SELECT COUNT(*) FROM mtl_material_transactions WHERE transaction_date >= '<start_date>';
SELECT COUNT(*) FROM rcv_transactions WHERE transaction_date >= '<start_date>';
SELECT COUNT(*) FROM pa_cost_distribution_lines_all WHERE gl_date >= '<start_date>';

All queries must return 0 before proceeding. Do not apply this fix if any transaction data exists in the affected periods.

Step 2 – Create backup table

Run data_fix.sql. A backup of all affected GL_PERIOD_STATUSES rows is created before any changes are made.

Step 3 – Reset period statuses to Never Opened

Update GL_PERIOD_STATUSES to set closing_status = 'N' for all periods that are currently Frozen (closing_status = 'F') starting from the affected date range.

Step 4 – Commit and validate

Issue COMMIT. Verify the affected periods now show status Never Opened in the GL Open/Close Periods form and can be opened normally.

Note: This fix must only be applied when all data check queries return zero. If any transaction data exists in the frozen periods, do not reset the period status — the transactions would need to be handled separately first.

Script Inventory

File	Description
data_fix.sql	Pre-check queries to verify no transaction data exists in affected periods (AP, AR, inventory, receiving, PA); creates backup of GL_PERIOD_STATUSES rows; resets closing_status from 'F' (Frozen) to 'N' (Never Opened) for the affected period range.

Key Tables Affected

Table	Action
GL_PERIOD_STATUSES	Update closing_status from 'F' to 'N' for affected periods