Search Results jai_retro_line_changes_pk

Overview

JAI_RETRO_LINE_CHANGES is a table owned by the JA schema (Asia/Pacific Localizations) within Oracle E-Business Suite. Its documented purpose is to store retroactive changes made to documents, primarily purchasing documents such as purchase orders and their lines and shipments. The table supports localization requirements for Oracle Purchasing, where price and tax adjustments often need to be applied retroactively after a document has already been processed, received, or invoiced.

Each row captures a discrete retroactive change event at the line level, retaining both the current document reference and the originating (pre-change) document reference. This dual-reference design allows the application to reconcile what a line looked like before the retroactive action with its post-change state. In particular, the EXCISE_ACTION and VAT_ACTION columns indicate that the table plays a central role in India and broader Asia/Pacific excise and value-added tax localization processing, where retroactive price corrections must generate corresponding tax adjustments.

Based on the foreign key topology, the heuristic Data Vault classification for this object leans toward satellite. It is not a pure hub because it carries descriptive and transactional attributes tied to events rather than serving as a unique business key registry, and it is not a clean link despite its many foreign key references. From a modeling suggestion standpoint, it behaves as a satellite attached to the purchase order line and shipment hubs, with the recursive SOURCE_LINE_CHANGE_ID reference preserving a lineage chain of successive changes.

Key Information Stored

The table contains 31 documented columns. The most significant are summarized below.

• LINE_CHANGE_ID — Surrogate primary key, enforced by JAI_RETRO_LINE_CHANGES_PK and the unique index JAI_RETRO_LINE_CHANGES_U1. This value is returned to callers searching for "line_change_id".
• DOC_TYPE, DOC_HEADER_ID, DOC_LINE_ID, LINE_LOCATION_ID, DOC_VERSION_NUMBER — Collectively form the business-key candidate unique index JAI_RETRO_LINE_CHANGES_U2, uniquely identifying a change against a specific document version and line location.
• DOC_HEADER_ID, DOC_LINE_ID, LINE_LOCATION_ID — Foreign keys to PO_HEADERS_ALL, PO_LINES_ALL, and PO_LINE_LOCATIONS_ALL respectively, anchoring the change to the purchasing document structure.
• FROM_HEADER_ID, FROM_LINE_ID — The originating header and line references, allowing comparison between pre-change and post-change values.
• SOURCE_LINE_CHANGE_ID — Recursive self-reference linking a change to the prior change in the chain.
• ORIGINAL_UNIT_PRICE and MODIFIED_UNIT_PRICE — The price before and after the retroactive adjustment.
• PRICE_CHANGE_DATE — Effective date of the retroactive price change.
• EXCISE_ACTION, VAT_ACTION, EXCISE_INVOICE_NO, VAT_INVOICE_NO — Localization tax action codes and related invoice references.
• INVENTORY_ITEM_ID and ORGANIZATION_ID — Item and inventory organization to which the change applies.
• RETRO_REQUEST_ID — Foreign key to FND_CONCURRENT_REQUESTS, identifying the concurrent program run that generated the change.
• VENDOR_ID and VENDOR_SITE_ID — Supplier and supplier site references.
• RECEIPT_PROCESSED_FLAG — Indicates whether the change has been reflected in receipt processing.

Common Use Cases and Queries

Typical use cases include auditing retroactive price corrections, reporting excise and VAT adjustments, tracing the lineage of successive changes, and reconciling supplier invoices against modified purchase order lines. A common pattern retrieves all changes for a document header:

SELECT line_change_id, doc_line_id, original_unit_price, modified_unit_price, price_change_date FROM jai_retro_line_changes WHERE doc_header_id = :p_header_id ORDER BY doc_line_id;

To trace a change lineage, a recursive query follows SOURCE_LINE_CHANGE_ID upward. Reporting on concurrent processing joins RETRO_REQUEST_ID to FND_CONCURRENT_REQUESTS. Tax reporting joins JAI_RETRO_TAX_CHANGES on LINE_CHANGE_ID.

Related Objects

• PO_HEADERS_ALL — joined via DOC_HEADER_ID and FROM_HEADER_ID.
• PO_LINES_ALL — joined via DOC_LINE_ID and FROM_LINE_ID.
• PO_LINE_LOCATIONS_ALL — joined via LINE_LOCATION_ID.
• MTL_SYSTEM_ITEMS_B — joined via INVENTORY_ITEM_ID and ORGANIZATION_ID.
• FND_CONCURRENT_REQUESTS — joined via RETRO_REQUEST_ID.
• AP_SUPPLIERS / AP_SUPPLIER_SITES_ALL — joined via VENDOR_ID and VENDOR_SITE_ID.
• JAI_RETRO_LINE_CHANGES (self) — recursive join via SOURCE_LINE_CHANGE_ID.
• JAI_RETRO_TAX_CHANGES — child table referencing LINE_CHANGE_ID.