Symptoms

• The AP/PO Accrual Reconciliation Report (PO_ACCRUAL_RECONCILE_TEMP_ALL) shows a persistent 1-cent (net_po_line_amount = 0.01 or -0.01) variance between matched PO receipt amounts and AP invoice amounts for a set of foreign currency PO lines.
• The variance is caused by a rounding difference in the currency conversion rate stored on RCV_TRANSACTIONS — the rate on the receipt transaction slightly differs from the rate implied by the AP invoice amount, unit price, and quantity, resulting in a 1-cent functional currency difference that does not resolve through normal processes.
• Because the variance cannot be resolved through re-matching, the accrual reconciliation lines cannot be cleared, causing persistent open accrual balances on the report.

Root Cause

In Oracle 11i, the PO accrual reconciliation matches receipt transactions to AP invoice distributions using functional currency amounts computed from the receipt's currency_conversion_rate on RCV_TRANSACTIONS. When the rate on the receipt was stored with slightly less precision than the rate implied by the AP invoice (computed as AP_TRANSACTION_AMOUNT / (AP_TRANSACTION_QUANTITY × AP_TRANSACTION_UNIT_PRICE)), a 1-cent rounding difference appears in NET_PO_LINE_AMOUNT on the reconciliation temp table. Two alternative resolution approaches are provided:

• Phase A (Script 02) — Recalculate and update RCV_TRANSACTIONS.currency_conversion_rate to match the rate implied by the AP invoice, eliminating the variance at source.
• Phases B and C (Scripts 03 and 04) — Create write-off records in PO_ACCRUAL_WRITE_OFFS_ALL for both the PO and AP sides of the variance, and mark the PO_ACCRUAL_RECONCILE_TEMP_ALL rows as written off (write_off_flag = 'Y', net_po_line_amount = 0).

Resolution — Script 01: Create Work and Error Tables

Run 01_setup_driver_tables.sql first. Creates:

• RSI_PO_ACCR_59272 — mirrors PO_ACCRUAL_RECONCILE_TEMP_ALL structure with a unique index on (PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, INVENTORY_ITEM_ID, TRANSACTION_SOURCE_CODE). Used as a backup for affected accrual reconciliation rows before write-off creation.
• RSI_AP_PO_ERRORS_59272 — error tracking table (transaction_id, trans_type, error_message, error_block) used by Phases B and C to log per-transaction processing errors without halting the full run.

Note: Script 02 (Phase A) uses a separate backup table RSI_RCV_TRX_59272 (created inline with CREATE TABLE AS SELECT on first use, protected by a DUP_VAL_ON_INDEX handler for subsequent runs).

Resolution — Script 02: Currency Rate Correction (Phase A)

Selects pairs of matching PO and AP accrual reconciliation rows from PO_ACCRUAL_RECONCILE_TEMP_ALL where both sides have net_po_line_amount = 0.01 and org_id = 119. Joins PO rows (transaction_source_code = 'PO') to AP rows (transaction_source_code = 'AP') on matching PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, and INVENTORY_ITEM_ID.

For each matched pair:

1. Calculates the corrected rate: v_new_rate := AP_TRANSACTION_AMOUNT / (AP_TRANSACTION_QUANTITY × AP_TRANSACTION_UNIT_PRICE).
2. Backs up matching rows from RCV_TRANSACTIONS (receipt transaction_id and its parent_transaction_id chain) into RSI_RCV_TRX_59272. Skips backup if already present (DUP_VAL_ON_INDEX).
3. Updates RCV_TRANSACTIONS.currency_conversion_rate to the new calculated rate for the PO transaction ID and its parent transaction.
4. Commits per record. Logs errors to RSI_AP_PO_ERRORS_59272 and proceeds to the next record on failure.

Resolution — Script 03: Write-Off Creation (Phase B — Hardcoded Parameters)

Selects PO accrual records from PO_ACCRUAL_RECONCILE_TEMP_ALL where abs(net_po_line_amount) = 0.01, org_id = 119, transaction_source_code = 'PO', and write_off_id IS NULL (not yet written off).

For each PO record:

1. Backs up the PO and matching AP rows from PO_ACCRUAL_RECONCILE_TEMP_ALL into RSI_PO_ACCR_59272 (protected by DUP_VAL_ON_INDEX).
2. Creates a PO write-off record: inserts into PO_ACCRUAL_WRITE_OFFS_ALL using PO_ACCRUAL_WRITE_OFFS_S.NEXTVAL, copying all relevant columns from the PO accrual row, with WRITE_OFF_CODE = 'WRITE OFF' and REASON_ID = 232.
3. Updates the PO row in PO_ACCRUAL_RECONCILE_TEMP_ALL: sets write_off_id, write_off_flag = 'Y', net_po_line_amount = 0.
4. Finds the matching AP accrual row: queries PO_ACCRUAL_RECONCILE_TEMP_ALL for transaction_source_code = 'AP' matching on PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, INVENTORY_ITEM_ID, and round(abs(transaction_amount)). Requires exactly one AP match — rolls back if zero or more than one AP row found.
5. Creates an AP write-off record: inserts into PO_ACCRUAL_WRITE_OFFS_ALL for the AP accrual row with a new sequence ID.
6. Updates the AP row in PO_ACCRUAL_RECONCILE_TEMP_ALL: sets write_off_id, write_off_flag = 'Y', net_po_line_amount = 0.
7. Commits per PO/AP pair. Logs errors per transaction type (PO or AP) to RSI_AP_PO_ERRORS_59272.

Resolution — Script 04: Write-Off Creation (Phase C — Parametric)

Same logic as Phase B (Script 03), but accepts two user-supplied parameters at runtime:

• &p_variable1 — Operating unit (org_id) to process.
• &p_variable2 — Variance amount threshold (e.g., 0.01 for 1-cent variances).

Phase C adds an additional filter on PO_DISTRIBUTION_ID when locating the matching AP accrual row, improving accuracy when multiple AP lines exist for the same PO/line/location combination. Otherwise identical to Phase B in its write-off insertion and update logic. Use Phase C as the preferred general-purpose version when the org or variance amount differs from the hardcoded Phase B values.

Script Inventory

Key Tables Affected