Results for “act_quantity”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

JAI_OPM_OSP_DTLS is a transactional detail table owned by the JA (Asia/Pacific Localizations) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the line-level Other Service Provider (OSP) details associated with an OSP header record. In the context of Indian localization requirements for process manufacturing, the table captures the itemized particulars of processing, receipt, and excise-related transactions performed by third-party service providers. Each row represents one detail line tied to a single header, enabling a normalized one-to-many relationship between an OSP transaction and its constituent service or material lines.

The ETRM metadata classifies this object heuristically as a link table in a Data Vault modeling sense. This classification reflects its role as an associative entity that resolves relationships between the OSP header (JAI_OPM_OSP_HDRS) and item master records (MTL_SYSTEM_ITEMS_B), while also carrying descriptive and quantitative measures. A physical design might therefore separate the business keys into a link structure and the measurable attributes into a satellite.

Key Information Stored

The table contains 21 documented columns. The most significant include:

• OSP_DETAIL_ID — surrogate primary key, defined by the unique index JAI_OPM_OSP_DTLS_PK. No alternate business-key unique index is documented beyond this.
• OSP_HEADER_ID — foreign key to JAI_OPM_OSP_HDRS, anchoring each detail line to its parent OSP header.
• ITEM_ID and INVENTORY_ITEM_ID — foreign keys to MTL_SYSTEM_ITEMS_B, identifying the item involved.
• ORGANIZATION_ID — the inventory organization context, required alongside the item keys to resolve the composite foreign key to MTL_SYSTEM_ITEMS_B.
• TRANS_DATE — transaction date for the OSP detail line.
• UOM_CODE — unit of measure for the quantities recorded.
• PLAN_QUANTITY and ACT_QUANTITY — planned versus actual quantities processed.
• UNIT_PRICE — price applied to the line for valuation or charge computation.
• MODVAT_CLAIMED and EXCISE_PAYABLE — tax-related amounts supporting Indian excise and MODVAT accounting.
• PROCESSING_CHARGE — the charge levied for the OSP service.
• ISSUE_RECPT_FLAG and MAIN_RCPT_FLAG — flags indicating issue/receipt direction and main receipt status.
• OBJECT_VERSION_NUMBER — optimistic locking column inherited from the Oracle Applications framework.

Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) provide audit lineage.

Common Use Cases and Queries

Typical reporting scenarios include reconciliation of planned versus actual OSP quantities, excise liability reporting, and processing charge analysis. A representative join retrieves detail lines with their parent header:

SELECT d.osp_detail_id, d.item_id, d.trans_date,
       d.plan_quantity, d.act_quantity, d.unit_price,
       d.excise_payable, d.modvat_claimed
FROM   jai_opm_osp_dtls d,
       jai_opm_osp_hdrs h
WHERE  d.osp_header_id = h.osp_header_id
AND    d.organization_id = :org_id;

To enrich detail lines with item descriptions, join to MTL_SYSTEM_ITEMS_B on both ITEM_ID and ORGANIZATION_ID. Aggregation queries grouped by TRANS_DATE or UOM_CODE support period-end excise summaries.

Related Objects

• JAI_OPM_OSP_HDRS — parent header table joined on OSP_HEADER_ID.
• MTL_SYSTEM_ITEMS_B — item master, joined via ITEM_ID/INVENTORY_ITEM_ID with ORGANIZATION_ID.
• JAI_OPM_OSP_DTLS_PK — unique index enforcing the primary key.
• MTL_ITEM_LOCATIONS and MTL_TRANSACTIONS may reference the resulting OSP transactions for inventory movement reconciliation.
• Oracle Process Manufacturing (OPM) costing and excise APIs frequently read this table during valuation and statutory reporting.