Case 00035635 – AR Projects Invoice Date Incorrect — Correct Transaction and GL Dates Across AR, PA, ZX, and XLA

AR Projects Invoice Date Incorrect — Correct Transaction and GL Dates Across AR, PA, ZX, and XLA

Case Number	00035635
Oracle Module	Accounts Receivable (AR) / Projects (PA)
EBS Version	R12
Functional Area	AR Customer Transactions / Projects Draft Invoices / ZX Tax / XLA Accounting Dates
Related Patches	None

Symptoms

A Projects-generated AR invoice has incorrect transaction dates and GL dates across multiple tables — the invoice date in PA_DRAFT_INVOICES_ALL, the transaction date in RA_CUSTOMER_TRX_ALL, the GL line date in RA_CUST_TRX_LINE_GL_DIST_ALL, the ZX tax dates, and the XLA accounting dates are inconsistent with the intended invoice date.
The AR_PAYMENT_SCHEDULES_ALL due date is also incorrect, preventing the invoice from aging correctly.
The XLA accounting entries (XLA_AE_HEADERS, XLA_AE_LINES) carry the wrong accounting date, causing potential period mismatches in the GL.

Root Cause

A Projects draft invoice (draft_invoice_num = 13, project_id = 2656807) was interfaced to AR with incorrect date values. The date must be corrected consistently across all related tables: the PA invoice, the AR customer transaction header, the AR distribution GL date, the AR payment schedule transaction and due dates, the ZX tax line dates, and the XLA event and accounting entry dates. Six iterative script phases were developed to determine and apply the correct date set; the final phase (Phase G, script 06) is the definitive applied version.

Resolution

Definitive Fix — Script 06 (Phase G)

Run 06_data_fix_phase_g.sql. This is the final, consolidated version that applies all date corrections for the affected transaction. It:

Creates backup tables for all affected rows: PA_DRAFT_INVOICES_ALL, ZX_LINES, ZX_LINES_DET_FACTORS, AR_PAYMENT_SCHEDULES_ALL, XLA_EVENTS, RA_CUST_TRX_LINE_GL_DIST_ALL, XLA_AE_HEADERS, XLA_AE_LINES.
Updates PA_DRAFT_INVOICES_ALL: sets INVOICE_DATE = 01/23/2014 for project_id = 2656807, draft_invoice_num = 13.
Updates ZX_LINES_DET_FACTORS: sets trx_date = 01/23/2014 for trx_id = 4790815.
Updates AR_PAYMENT_SCHEDULES_ALL: sets trx_date = 01/23/2014 and due_date = 02/22/2014 for customer_trx_id = 4790815.
Updates XLA_EVENTS: sets event_date = 01/23/2014 and transaction_date = 01/23/2014 for event_id = 64367271.
Updates XLA_AE_HEADERS: sets accounting_date = 01/23/2014 for ae_header_id = 100289410.
Updates XLA_AE_LINES: sets accounting_date = 01/23/2014 for ae_header_id = 100289410.
Updates RA_CUSTOMER_TRX_ALL: sets trx_date = 01/23/2014 for customer_trx_id = 4790815.
Updates ZX_LINES: sets tax_currency_conversion_date, trx_date, tax_date, tax_determine_date, and tax_point_date all to 01/23/2014 for trx_id = 4790815.
Commits.

Note: The RA_CUST_TRX_LINE_GL_DIST_ALL GL date update (setting to 12/28/2013 per script 05) is present in intermediate phases but omitted from Phase G — the final correct GL date was determined to remain as-is for that table.

Earlier Script Phases (01–05) — For reference only

Scripts 01 through 05 represent iterative attempts to determine the correct combination of date values. Each phase adds or adjusts which date columns are updated and to what values. Script 01 (Phase A) updates the fewest tables (PA invoice + ZX det factors + RA_CUST_TRX_LINE_GL_DIST + AR_PAYMENT_SCHEDULES + XLA_EVENTS + XLA_AE_HEADERS + XLA_AE_LINES). Scripts 02 and 03 (Phases C and D) add RA_CUSTOMER_TRX_ALL and ZX_LINES date updates. Scripts 04 and 05 (Phases E and F) refine the payment schedule and RA_CUST_TRX_LINE_GL_DIST dates.

Use script 06 (Phase G) as the definitive reference. Earlier phases are retained for audit trail only.

Note: The ra_cust_trx_line_gl_dist_bri trigger is disabled before and re-enabled after updates to RA_CUST_TRX_LINE_GL_DIST_ALL in phases that touch that table, to prevent trigger interference during the date update. The affected transaction is customer_trx_id = 4790815, XLA event = 64367271, AE header = 100289410. Always verify the target dates against the correct accounting period before applying.

Script Inventory

File	Description
01_data_fix_phase_a.sql	Phase A: Initial date correction. Updates PA_DRAFT_INVOICES_ALL, ZX_LINES_DET_FACTORS (gl_date only), RA_CUST_TRX_LINE_GL_DIST_ALL, AR_PAYMENT_SCHEDULES_ALL (gl_date), XLA_EVENTS (event_date), XLA_AE_HEADERS, XLA_AE_LINES to 01/23/2014.
02_data_fix_phase_c.sql	Phase C: Adds RA_CUSTOMER_TRX_ALL trx_date and ZX_LINES date corrections to Phase A changes.
03_data_fix_phase_d.sql	Phase D: Duplicate of Phase C content — same date corrections for the same transaction.
04_data_fix_phase_e.sql	Phase E: Adds due_date correction on AR_PAYMENT_SCHEDULES_ALL (02/22/2014) to Phase C changes.
05_data_fix_phase_f.sql	Phase F: Consolidated version combining all prior phase corrections including both gl_date and trx_date changes.
06_data_fix_phase_g.sql	Phase G (DEFINITIVE): Final applied version. Corrects all relevant date columns across PA_DRAFT_INVOICES_ALL, ZX_LINES_DET_FACTORS, AR_PAYMENT_SCHEDULES_ALL, XLA_EVENTS, XLA_AE_HEADERS, XLA_AE_LINES, RA_CUSTOMER_TRX_ALL, and ZX_LINES to 01/23/2014 (due_date 02/22/2014).

Key Tables Affected

Table	Action
PA_DRAFT_INVOICES_ALL	Update INVOICE_DATE (and GL_DATE in some phases) to 01/23/2014
RA_CUSTOMER_TRX_ALL	Update trx_date to 01/23/2014
RA_CUST_TRX_LINE_GL_DIST_ALL	Update gl_date (trigger disabled during update)
AR_PAYMENT_SCHEDULES_ALL	Update trx_date to 01/23/2014; update due_date to 02/22/2014; update gl_date in some phases
ZX_LINES	Update tax_currency_conversion_date, trx_date, tax_date, tax_determine_date, tax_point_date to 01/23/2014
ZX_LINES_DET_FACTORS	Update trx_line_gl_date and/or trx_date to 01/23/2014
XLA_EVENTS	Update event_date and transaction_date to 01/23/2014
XLA_AE_HEADERS	Update accounting_date to 01/23/2014
XLA_AE_LINES	Update accounting_date to 01/23/2014