Symptoms

• The AP/PO Accrual Reconciliation Report shows persistent accrual variances on PO_ACCRUAL_RECONCILE_TEMP_ALL where abs(net_po_line_amount) equals a specified threshold (typically a small rounding amount such as 1 cent) and net_po_line_quantity = 0, indicating fully received but unresolved monetary variances that cannot be cleared through re-matching.
• Unlike case 00059272 which addressed single-transaction-per-distribution scenarios, this case addresses PO distributions where multiple PO accrual transaction rows may exist under the same po_distribution_id, requiring write-off records to be created for each PO transaction within the distribution group rather than for a single row.
• An optional override parameter allows write-offs to proceed even when the sum of AP transaction amounts does not exactly match the PO transaction amount, enabling handling of multi-invoice distributions where individual AP amounts differ but the aggregate matches.

Root Cause

The same root cause as case 00059272 applies: rounding or conversion differences between PO receipt amounts and AP invoice amounts accumulate in net_po_line_amount and cannot be resolved through re-matching. This case adds a more sophisticated grouping approach — processing by po_distribution_id rather than by individual transaction row — to correctly handle distributions with multiple PO receipt transaction rows. The write-off logic evolved through five iterative phases (G through M), each refining the AP matching criteria, amount comparison function, and idempotency guards.

The five fix scripts represent a progressive refinement:

• Phase G (Script 02) — Per-PO-row processing; AP matched via po_distribution_id; amount comparison uses round().
• Phase H (Script 03) — Distribution-level outer key; inner loop over all PO transactions per distribution creates one write-off per PO transaction row; amount comparison changes to floor().
• Phase K (Script 04) — Identical to Phase H with SELECT UNIQUE added to inner cursors to deduplicate rows.
• Phase L (Script 05) — Removes po_transaction_id from the outer driver key (cleaner distribution-level deduplication only).
• Phase M (Script 06) — Adds write_off_flag <> 'Y' guard to prevent reprocessing of already written-off distributions. Most defensive and preferred version for subsequent runs.

Resolution — Script 01: Create Backup and Error Tables

Run 01_setup_driver_tables.sql first. Creates:

• RSI_PO_ACCR_77357 — mirrors PO_ACCRUAL_RECONCILE_TEMP_ALL structure with unique index on (PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, INVENTORY_ITEM_ID, TRANSACTION_SOURCE_CODE). Used as backup for affected accrual reconciliation rows before write-off creation.
• RSI_AP_PO_ERRORS_77357 — error tracking table with columns: transaction_id, distribution_id (added vs. case 59272), trans_type, error_message, error_block. The distribution_id column enables error attribution at the distribution level, not just the transaction level.

Resolution — Script 02: Write-Off Creation (Phase G)

Accepts three runtime parameters: &p_variable1 (org_id), &p_variable2 (variance amount), &p_variable3 (override Y/N, default N).

Outer cursor getPOReceipts: selects all columns from PO_ACCRUAL_RECONCILE_TEMP_ALL where abs(net_po_line_amount) = &p_variable2, org_id = &p_variable1, transaction_source_code = 'PO', net_po_line_quantity = 0, write_off_id IS NULL.

For each PO row:

1. Backs up matching rows from PO_ACCRUAL_RECONCILE_TEMP_ALL into RSI_PO_ACCR_77357 (DUP_VAL_ON_INDEX protected).
2. Creates one PO write-off record in PO_ACCRUAL_WRITE_OFFS_ALL using PO_ACCRUAL_WRITE_OFFS_S.NEXTVAL; updates the PO row (write_off_id, write_off_flag='Y', net_po_line_amount=0).
3. Sets matching variables: v_po_distribution_id, v_transaction_amount = round(abs(rec1.transaction_amount)), v_transaction_quantity = abs(rec1.transaction_quantity).
4. Loops getAPRecs cursor (matches on po_distribution_id, transaction_source_code='AP'): for each AP row, creates an AP write-off record and updates the AP row.
5. Amount validation (controlled by override parameter):
   • override = 'N': if round(abs(sum_AP_amounts)) <> round(abs(PO_amount)) — rolls back all changes for this distribution; logs error.
   • override = 'Y': proceeds regardless of amount mismatch; logs warning only.
   • If no AP rows found: rolls back; logs error.
6. Commits per PO row. Errors logged to RSI_AP_PO_ERRORS_77357.

Resolution — Script 03: Write-Off Creation (Phase H)

Key changes from Phase G:

• Outer cursor getPOReceipts selects UNIQUE (po_header_id, po_line_id, line_location_id, po_distribution_id, po_transaction_id) — groups at the distribution level rather than fetching all columns per row.
• Inner cursor getPOTransactions: fetches all PO transaction rows for PO_DISTRIBUTION_ID = v_po_distribution_id, transaction_source_code='PO'. Creates one write-off record per PO transaction in the inner loop; accumulates v_transaction_amount and v_transaction_quantity across all PO transactions in the distribution.
• Amount comparison uses floor(abs(...)) instead of round(abs(...)), providing a more conservative match threshold.
• AP cursor getAPRecs matches on po_distribution_id = v_po_distribution_id, transaction_source_code='AP'.

Per-distribution processing: back up → inner PO loop (write-off per transaction) → inner AP loop (write-off per AP row) → amount validation with override → commit or rollback.

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

Identical to Phase H with one change: both getPOTransactions and getAPRecs cursors use SELECT UNIQUE ROWNUM, ... to deduplicate rows within each distribution group before creating write-off records. This prevents duplicate write-off insertions when the underlying data contains duplicate rows in PO_ACCRUAL_RECONCILE_TEMP_ALL.

Resolution — Script 05: Write-Off Creation (Phase L)

Identical to Phase K with one change: outer cursor getPOReceipts selects UNIQUE (po_header_id, po_line_id, line_location_id, po_distribution_id) — po_transaction_id is removed from the outer driver key. This ensures the outer loop deduplicates strictly at the distribution level, eliminating any risk of the same distribution being processed multiple times due to different po_transaction_id values within the same distribution group.

Resolution — Script 06: Write-Off Creation (Phase M)

Identical to Phase L with one additional filter added to both the preview query and the outer cursor getPOReceipts: AND write_off_flag <> 'Y'. This ensures the script is fully idempotent — distributions that were already written off in a previous run (where write_off_flag was set to 'Y') are excluded from reprocessing even if write_off_id IS NULL due to a partial prior update. Phase M is the preferred version for new runs.

Script Inventory

Key Tables Affected