Search Results jai_cmn_lines_pk

Overview

JAI_CMN_LINES is a transactional table owned by the JA schema within the Asia/Pacific Localizations (JA) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description states that it stores ASBN (Advance Shipping and Billing Notice) information, positioning it as the localization extension that captures excise-related receipt and shipment data used primarily for Indian statutory compliance and related reporting flows. The table maintains 25 physical columns and is anchored by the unique index JAI_CMN_LINES_PK on CMN_LINE_ID.

From a Data Vault modeling perspective, the inferred classification is a link entity. This classification is a heuristic derived from the six foreign key relationships embedded in the table, which connect purchasing documents (PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL) with receiving documents (RCV_SHIPMENT_HEADERS, RCV_SHIPMENT_LINES, RCV_TRANSACTIONS_INTERFACE). The presence of excise invoice attributes alongside these cross-domain references reinforces the role of the table as an associative bridge capturing transaction-level detail across the procure-to-receive lifecycle.

Key Information Stored

The column set spans purchasing references, receiving references, item detail, pricing, and localization-specific excise attributes. The most significant columns are summarized below.

• CMN_LINE_ID — Surrogate primary key defined by the JAI_CMN_LINES_PK unique index; uniquely identifies each ASBN line record.
• SHIPMENT_NUMBER — Business identifier of the shipment associated with the ASBN line.
• PO_HEADER_ID, PO_LINE_ID, PO_LINE_LOCATION_ID — Foreign keys to PO_HEADERS_ALL, PO_LINES_ALL, and PO_LINE_LOCATIONS_ALL respectively, linking the ASBN line to the originating purchase order header, line, and shipment schedule.
• PO_NUMBER, LINE_NUM — Denormalized purchasing document number and line number for reporting convenience.
• SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID — Foreign keys to RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES, tying the record to the actual receipt.
• SHIPMENT_LINE_NUM — Display line number of the corresponding receiving shipment line.
• INTERFACE_TRANSACTION_ID — Foreign key to RCV_TRANSACTIONS_INTERFACE, used during the receiving interface import lifecycle.
• HEADER_INTERFACE_ID — Interface header reference for the inbound conversion or receipt process.
• ITEM_ID — Inventory item reference for the received material.
• QUANTITY, UOM_CODE — Received/ASBN quantity and its unit of measure.
• PO_UNIT_PRICE, CURRENCY_CODE — Purchase price and currency used for value computation, typically for excise assessment.
• EXCISE_INV_NUMBER, EXCISE_INV_DATE — Localization-specific excise invoice number and date, central to Indian excise reporting.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER — Standard EBS audit and concurrency-control columns; OBJECT_VERSION_NUMBER supports optimistic locking in OA Framework-based extensions.

Common Use Cases and Queries

The table is queried primarily for excise reporting, ASBN reconciliation, and purchasing-to-receiving traceability. A typical pattern joins back to purchasing and receiving tables using the documented foreign keys.

• Reconcile ASBN lines to purchase orders: SELECT l.cmn_line_id, l.po_number, l.line_num, l.quantity, p.authorization_status FROM jai_cmn_lines l, po_headers_all p WHERE l.po_header_id = p.po_header_id
• Trace receipts for excise reporting: joining JAI_CMN_LINES to RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES on SHIPMENT_HEADER_ID and SHIPMENT_LINE_ID to obtain receipt dates that pair with EXCISE_INV_DATE.
• Interface error investigation: selecting rows by INTERFACE_TRANSACTION_ID or HEADER_INTERFACE_ID where the RCV interface has rejected or held a receipt, using the ID to correlate with the receiving interface tables.
• Period-end excise register: aggregating QUANTITY × PO_UNIT_PRICE by CURRENCY_CODE and EXCISE_INV_DATE to feed statutory filings.

Related Objects

The following objects are the most significant dependencies based on the documented foreign key layout. Each is joined on the column named above.

• PO_HEADERS_ALL — joined via JAI_CMN_LINES.PO_HEADER_ID; supplies PO header context.
• PO_LINES_ALL — joined via JAI_CMN_LINES.PO_LINE_ID; supplies PO line detail.
• PO_LINE_LOCATIONS_ALL — joined via JAI_CMN_LINES.PO_LINE_LOCATION_ID; supplies shipment schedule detail.
• RCV_SHIPMENT_HEADERS — joined via JAI_CMN_LINES.SHIPMENT_HEADER_ID; supplies receipt header information.
• RCV_SHIPMENT_LINES — joined via JAI_CMN_LINES.SHIPMENT_LINE_ID; supplies receipt line detail.
• RCV_TRANSACTIONS_INTERFACE — joined via JAI_CMN_LINES.INTERFACE_TRANSACTION_ID; supports interface processing and reconciliation.

Collectively, these relationships confirm that JAI_CMN_LINES functions as the localization bridge between Oracle Purchasing and Oracle Receiving, extending standard receipt data with the excise attributes required for Asia/Pacific statutory compliance.