Inventory – Overview of Completed Transactions and SQL Query to get the Completed Transaction Export

Oracle Inventory Cloud, along with Order Management, Purchasing, Work Execution, Maintenance Asset, and Warehouse Management, provides a comprehensive set of transactions and reports for effective inventory control. This allows you to control the flow of material from the time you receive items to the time you ship finished goods to the customer, including production activities.

Inventory transactions record material movements and cost updates performed within the system, including receipts, issues, transfers, adjustments, and shipments etc.,

We use Review Completed Transactions page to search for and view details of completed inventory transactions. This table provides a summary of the completed transaction details, includng lot and serial information when applicable.

Here is the SQL query used to get completed inventory transactions for inventory movement analysis, accuracy verification, and further analysis. The query allows exporting inventory transaction data based on date range, transaction type, transaction source, and other filtering criteria. This is very useful for the operations and costing teams to analyze the inventory data.

SELECT
    imt.transaction_id                                     id,
    iodv.organization_code                                 org,
    iodv.organization_name                                 org_name,
    esi.item_number                                        item,
    esi.description                                        item_descr,
    ittt.transaction_type_name                             trx_type,
    to_char(imt.transaction_date, 'MM/DD/YYYY HH24:MI:SS') trx_date,
    flv_action.meaning                                     trx_action,
    itstt.transaction_source_type_name                     trx_source_type,
    imt.source_code,
    imt.transaction_source_name                            source_reference,
    imt.transaction_reference                              reference,
    imt.subinventory_code                                  subinv,
    iil.segment1|| '.'|| iil.segment2|| '.'|| iil.segment3|| '.'|| iil.segment4 locator,
-- Lots /Serial Numbers
    imt.transaction_quantity                               trx_qty,
    imt.transaction_uom                                    trx_uom,

    NVL(imt.costed_flag,'Y')                                        costed,            --??
    imt.transaction_cost,                                                       -- if populated it means "Use Current Item Cost" was set to No and user entered Unit Cost instead of using Item's defined Unit Cost
    imt.actual_cost,
    imt.variance_amount,
    imt.prior_cost,
    imt.new_cost,
    imt.rcv_transaction_id                                 receiving_trx,
    imt.request_id,                                                             -- Request ID of the job that created or last updated the row
    imt.load_request_id,                                                        -- Request ID of the interface job that created the row
    imt.mvt_stat_status,                                                        -- Flag to indicate that the transaction is updated/processed/new
    gcc.segment1|| '.'|| gcc.segment2|| '.'|| gcc.segment3|| '.'|| gcc.segment4|| '.'|| gcc.segment5|| '.'|| gcc.segment6|| '.'|| gcc.segment7 code_comb,
    to_char(imt.creation_date, 'MM/DD/YYYY HH24:MI:SS')    creation_date,
    imt.created_by                                         created_by,
    to_char(imt.last_update_date, 'MM/DD/YYYY HH24:MI:SS') last_update_date,
    imt.last_updated_by                                    last_updated_by,
    ppav.segment1                                          proj,
    ptv.task_number                                        task,
    pett.expenditure_type_name                             exp_type,
    haou.name                                              exp_org,
    to_char(peia.creation_date, 'MM/DD/YYYY HH24:MI:SS')   exp_item_creation_date,
    to_char(peia.expenditure_item_date, 'MM/DD/YYYY')      exp_item_date,
    peia.project_raw_cost                                  proj_cost,
    peia.raw_cost_rate,
    peia.quantity,
    peia.unit_of_measure                                   uom
FROM inv_material_txns imt
    JOIN egp_system_items_vl esi                ON esi.inventory_item_id = imt.inventory_item_id AND esi.organization_id = imt.organization_id
    JOIN inv_organization_definitions_v iodv    ON iodv.organization_id = imt.organization_id
    LEFT JOIN inv_item_locations iil            ON iil.inventory_location_id = imt.locator_id
    JOIN inv_transaction_types_tl ittt          ON ittt.transaction_type_id = imt.transaction_type_id AND ittt.language = userenv('lang')
    JOIN inv_txn_source_types_tl itstt          ON itstt.transaction_source_type_id = imt.transaction_source_type_id AND itstt.language = userenv('lang')
    LEFT JOIN pjf_projects_all_vl ppav          ON ppav.project_id = imt.pjc_project_id
    LEFT JOIN pjf_tasks_v  ptv                  ON ptv.project_id = ppav.project_id AND imt.pjc_task_id = ptv.task_id
    LEFT JOIN pjf_exp_types_tl pett             ON pett.expenditure_type_id = imt.pjc_expenditure_type_id AND pett.language = userenv('lang')
    LEFT JOIN hr_all_organization_units haou    ON haou.organization_id = imt.pjc_organization_id
    LEFT JOIN gl_code_combinations gcc          ON gcc.code_combination_id = imt.distribution_account_id
    LEFT JOIN pjc_exp_items_all peia            ON '#'|| peia.doc_ref_id1 = '#'|| imt.transaction_id
    LEFT JOIN fnd_lookup_values_vl flv_action   ON flv_action.lookup_code = imt.transaction_action_id AND flv_action.lookup_type = 'INV_TRANSACTION_ACTION' AND flv_action.view_application_id = 0
WHERE 1 = 1
-- AND imt.transaction_id = :p_trx_id -- 23310067 -- 23780174
 and imt.transaction_date between :P_TRX_DATE_FROM and :P_TRX_DATE_TO
ORDER BY imt.creation_date DESC

Report Sample Output