Results for “transaction_source_id”

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

Overview

WIP_JOB_LOT_COMPOSITION_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, part of the WIP (Work in Process) product family. Its documented purpose is to expose the discrete job lot composition — that is, the relationship between a discrete work order (job) and the material lots consumed against or associated with it, expressed through lot-level quantity data. The view is defined as a standalone SQL view text object and is marked VALID in the ETRM repository, confirming it is a supported dictionary object for query and integration purposes.

The central value of the view is the TOTAL_LOT_QUANTITY column, which is the column most frequently targeted when users search for "total_lot_quantity." This column represents the aggregated lot quantity derived from material transaction lot records, with a sign inversion applied during aggregation. Because the view consolidates discrete job headers, inventory lot attributes, and transaction lot details into a single row set, it removes the need for report authors and integrators to hand-join four or more base tables to arrive at a job-to-lot quantity picture.

Underlying Base Objects

The view is defined over the following documented base objects, all of which are referenced as synonyms in the APPS schema:

• WIP_ENTITIES — supplies the WIP entity identifier and entity name.
• WIP_DISCRETE_JOBS — supplies the discrete job start quantity, completed quantity, lot number, primary item, and organization context.
• MTL_TRANSACTION_LOT_NUMBERS — the core transaction-level lot table contributing PRIMARY_QUANTITY, LOT_NUMBER, transaction source type and source id.
• MTL_LOT_NUMBERS — contributes lot expiration date.
• MTL_SYSTEM_ITEMS_KFV — contributes the concatenated item segment string and item description.

Important join conditions include an outer join from WIP_DISCRETE_JOBS to WIP_ENTITIES, and outer joins from WIP_DISCRETE_JOBS to MTL_TRANSACTION_LOT_NUMBERS on organization, lot number, and primary item. The view restricts MTL_TRANSACTION_LOT_NUMBERS to TRANSACTION_SOURCE_TYPE_ID = 5, which corresponds to WIP-related transaction sourcing, and excludes rows where the resolved WIP entity id equals the transaction source id. A DISTINCT clause is applied to the select, and a GROUP BY aggregates PRIMARY_QUANTITY into TOTAL_LOT_QUANTITY. Where WIP_ENTITY_ID is null, the view substitutes the sentinel value -999.

Key Columns

• WIP_ENTITY_ID — the discrete job identifier, or -999 when no matching job is resolved.
• START_QUANTITY / QUANTITY_COMPLETED — the job's planned start and completed quantities.
• WIP_ENTITY_NAME — the human-readable discrete job name.
• ORGANIZATION_ID / INVENTORY_ITEM_ID — inventory organization and item identifiers.
• CONCATENATED_SEGMENTS / DESCRIPTION — the flexfield key and description of the item.
• LOT_NUMBER / EXPIRATION_DATE — the consumed or associated lot and its shelf-life date.
• TOTAL_LOT_QUANTITY — the aggregated lot quantity, computed as SUM(PRIMARY_QUANTITY) * -1.
• TRANSACTION_SOURCE_TYPE_ID / TRANSACTION_SOURCE_ID — the sourcing transaction classification and identifier.

Common Use Cases and Queries

Typical uses include lot traceability reports, discrete job consumption analysis, and reconciliation of lot-controlled components against work order quantities.

To retrieve total lot quantity per job:

• SELECT wip_entity_name, lot_number, total_lot_quantity FROM apps.wip_job_lot_composition_v WHERE wip_entity_name = :job_name;

To find lot consumption by item and organization:

• SELECT organization_id, concatenated_segments, lot_number, SUM(total_lot_quantity) total_qty FROM apps.wip_job_lot_composition_v GROUP BY organization_id, concatenated_segments, lot_number;

To review expiration exposure across open jobs:

• SELECT wip_entity_name, lot_number, expiration_date, total_lot_quantity FROM apps.wip_job_lot_composition_v WHERE expiration_date < SYSDATE ORDER BY expiration_date;