Search Results jai_retro_line_changes_u1

Overview

JA.JAI_RETRO_LINE_CHANGES is a transactional table in the Oracle E-Business Suite Procurement and Payables family, owned by the JA (Oracle Financials for India – ETRM / India Localization) schema. It stores the retroactive pricing changes applied to procurement documents—releases, standard purchase orders, and receipts—when a retro price update is executed. It is a core staging and audit object in the India-localized retroactive price adjustment process tied to excise, VAT, and receipt processing.

The table is classified as satellite-leaning in a Data Vault heuristic model. This classification reflects the fact that the row carries descriptive attribute history (original versus modified unit price, version numbers, action flags) keyed to a business entity. Each row represents one retroactive change instance for a given document line location and version, making it a natural satellite surrounding the document/line/location business key.

Key Information Stored

The surrogate primary key is LINE_CHANGE_ID (backed by unique index JAI_RETRO_LINE_CHANGES_U1), which uniquely identifies each retroactive change record. The primary business-key candidate is the composite unique index JAI_RETRO_LINE_CHANGES_U2, defined over DOC_TYPE, DOC_HEADER_ID, DOC_LINE_ID, LINE_LOCATION_ID, and DOC_VERSION_NUMBER. This composite ensures a document line location can only carry one retro change per version.

• DOC_TYPE – the document category; documented values are 'RELEASE', 'STANDARD PO', and 'RECEIPT'.
• DOC_HEADER_ID / DOC_LINE_ID / LINE_LOCATION_ID – the document header, line, and shipment line location identifiers.
• DOC_VERSION_NUMBER – the retroactive update version for the document.
• FROM_HEADER_ID / FROM_LINE_ID – the source header and line, used primarily for global blanket purchase agreements.
• SOURCE_LINE_CHANGE_ID – self-referencing identifier (typically the shipment for the current receipt).
• PRICE_CHANGE_DATE – the date the retroactive update was run.
• ORIGINAL_UNIT_PRICE / MODIFIED_UNIT_PRICE – the unit price before and after the retro price update.
• INVENTORY_ITEM_ID / ORGANIZATION_ID – the item and inventory organization affected.
• RECEIPT_PROCESSED_FLAG – whether the receipt was processed for retro price updates.
• EXCISE_ACTION / VAT_ACTION – the action to be taken with respect to excise and VAT regime taxes.
• RETRO_REQUEST_ID – links to the concurrent request that drove the retro run.
• VENDOR_ID / VENDOR_SITE_ID – the supplier and supplier site.

Common Use Cases and Queries

The table supports reconciliation and audit of retroactive price adjustments, determination of which receipts still require processing, and India excise/VAT reporting. A typical query retrieving all retro changes for a purchase order is:

• SELECT LINE_CHANGE_ID, DOC_TYPE, DOC_HEADER_ID, DOC_LINE_ID, DOC_VERSION_NUMBER, ORIGINAL_UNIT_PRICE, MODIFIED_UNIT_PRICE FROM JA.JAI_RETRO_LINE_CHANGES WHERE DOC_HEADER_ID = :p_po_header_id ORDER BY DOC_VERSION_NUMBER;
• Identifying unprocessed receipts: filter on RECEIPT_PROCESSED_FLAG to find changes where the receipt has not yet been processed for the retro update.
• Price impact analysis: compare ORIGINAL_UNIT_PRICE to MODIFIED_UNIT_PRICE grouped by INVENTORY_ITEM_ID and ORGANIZATION_ID.
• Version-history review: use the U2 business key to trace successive DOC_VERSION_NUMBER values for a single line location.

Related Objects

The table integrates tightly with procurement, payables, and India tax objects through documented foreign keys:

• PO_HEADERS_ALL – via DOC_HEADER_ID and FROM_HEADER_ID.
• PO_LINES_ALL – via DOC_LINE_ID and FROM_LINE_ID.
• PO_LINE_LOCATIONS_ALL – via LINE_LOCATION_ID.
• MTL_SYSTEM_ITEMS_B – via INVENTORY_ITEM_ID.
• FND_CONCURRENT_REQUESTS – via RETRO_REQUEST_ID, linking each change to its retro run.
• AP_SUPPLIERS / AP_SUPPLIER_SITES_ALL – via VENDOR_ID and VENDOR_SITE_ID.
• JA.JAI_RETRO_TAX_CHANGES – referencing LINE_CHANGE_ID, capturing the corresponding tax-level changes.
• JA.JAI_RETRO_LINE_CHANGES – self-referencing via SOURCE_LINE_CHANGE_ID.