Symptoms

• Multiple fixed assets have incorrect depreciation amounts in FA_DEPRN_SUMMARY and FA_DEPRN_DETAIL for the affected depreciation period.
• The deprn_amount, ytd_deprn, and deprn_reserve values in the summary and detail tables do not match the expected depreciation calculated from the asset cost and life.
• The FA_BOOKS table has incorrect adjusted_cost, eofy_adj_cost, eofy_reserve, and prior_eofy_reserve values for the affected assets.
• FA reports and asset book values are incorrect for the affected depreciation period.

Root Cause

An incomplete or incorrect depreciation run produced wrong deprn_amount values in the depreciation summary and detail tables for a set of assets. The fix recalculates the correct depreciation amounts using the prior period values as a base (prior period's YTD, reserve, and adjusted cost), adds the correct current-period depreciation amount from a driver table, and updates the FA depreciation and book tables accordingly. A separate one-off correction is also applied directly for a specific asset distribution where the depreciation detail values required a targeted fix.

Resolution

Run the companion driver table creation script (rsi_fa_driver_table_create.sql, referenced but not included in this case folder). Create the driver table RSI_FA_ASSET_DTLS_47887 with columns asset_id, book_code, depreciation (the correct current-period amount), and period_counter. Populate it with one row per affected asset containing the correct depreciation amount. Also create and compile the RSI_FA_DFIX_LOG logging package.

Run data_fix.sql. For each asset in RSI_FA_ASSET_DTLS_47887, insert the current rows for the affected period into backup tables:

• RSI_FA_BOOKS_47887 — current effective FA_BOOKS row
• RSI_FA_DEPRN_SUM_47887 — FA_DEPRN_SUMMARY row for the affected period
• RSI_FA_DEPRN_DTLS_47887 — FA_DEPRN_DETAIL rows for the affected period

Select deprn_amount, ytd_deprn, deprn_reserve, adjusted_cost, and system_deprn_amount from FA_DEPRN_SUMMARY for the prior period counter. These values are used as the base for calculating the correct current-period amounts.

Compute the correct values:

• deprn_amount = correct depreciation from the driver table
• ytd_deprn = prior YTD + current depreciation amount
• deprn_reserve = prior reserve + current depreciation amount
• adjusted_cost = prior adjusted cost (unchanged)
• eofy_adj_cost = adjusted cost − YTD depreciation
• eofy_reserve = updated depreciation reserve

Update FA_DEPRN_SUMMARY with the recalculated deprn_amount, ytd_deprn, deprn_reserve, adjusted_cost, and system_deprn_amount for the affected asset, book type code, deprn_source_code = 'DEPRN', and current period counter.

Update FA_DEPRN_DETAIL with the recalculated deprn_amount, ytd_deprn, and deprn_reserve for the affected asset, book type code, deprn_source_code = 'D', and current period counter.

Update FA_BOOKS (current effective row — date_ineffective IS NULL) with the recalculated adjusted_cost, eofy_adj_cost, eofy_reserve, and prior_eofy_reserve.

After the cursor loop, apply a direct update to FA_DEPRN_DETAIL for a specific asset and its two distribution rows that require targeted deprn_amount, ytd_deprn, and deprn_reserve values not derivable from the standard formula.

Commit all changes. Verify depreciation amounts in FA reports for the affected period.

Script Inventory

Key Tables Affected