Symptoms

• The FA Create Journal Entries (or GL Transfer) process for a depreciation period fails or cannot be re-run.
• FA_DEPRN_PERIODS shows non-null batch IDs and GL_TRANSFER_FLAG for a period that needs to be re-transferred.
• FA_DEPRN_DETAIL and FA_ADJUSTMENTS rows for the period have je_header_id values pointing to journal headers that were not properly created.
• FA_JOURNAL_ENTRIES records for the period have an incorrect je_status that indicates they were already processed.
• FA_JOURNALS_INTERIM_* temporary tables exist for the failed transfer run and must be removed before re-running.

Root Cause

A failed or incomplete GL Transfer run left batch ID and journal header references on the Fixed Assets period and detail tables, and created interim journal entry tables that were not cleaned up. Additionally, FA_JOURNAL_ENTRIES records have a je_status that signals completion, when in fact the transfer was not completed. These references must be cleared and the interim tables dropped before the GL Transfer can be re-run successfully.

Resolution

Run data_fix.sql. Backup tables are created for:

• FA_DEPRN_PERIODS — the affected period record
• FA_ADJUSTMENTS — adjustments for the period
• FA_DEPRN_DETAIL — depreciation detail records for the period
• FA_JOURNAL_ENTRIES — the stuck journal entry records for the specific CJE IDs

Update FA_DEPRN_PERIODS for the affected book and period counter to NULL out all batch ID columns (depreciation_batch_id, retirement_batch_id, reclass_batch_id, transfer_batch_id, addition_batch_id, ADJUSTMENT_BATCH_ID, deferred_deprn_batch_id, reval_batch_id, deprn_adjustment_batch_id, all CIP batch IDs) and set GL_TRANSFER_FLAG = NULL.

Update FA_DEPRN_DETAIL for the affected book and period counter to NULL out deprn_expense_je_line_num, deprn_reserve_je_line_num, bonus journal line numbers, and je_header_id.

Update FA_ADJUSTMENTS for the affected book and period counter to NULL out je_header_id and je_line_num.

Update FA_JOURNAL_ENTRIES to set je_status = 'B' (Build — not yet transferred) for the specific CJE IDs associated with the failed transfer. This allows the GL Transfer to reprocess these journal entries.

Query ALL_TABLES for any FA_JOURNALS_INTERIM_* tables associated with the failed run:

SELECT table_name FROM all_tables WHERE table_name LIKE 'FA_JOURNALS_INTERIM%';

Create a backup of each interim table found, then drop the interim tables.

Issue COMMIT. Re-run the FA Create Journal Entries (GL Transfer) process for the affected book and period.

Script Inventory

Key Tables Affected