Module: WIP
Related table: Inv. mtl_material_transactions Material Transaction Processing table (transaction_source_id is connected to We. wip_entity_id)
Inv. mtl_transaction_lot_numbers table of batch numbers for Material Transaction Processing (transaction_id is connected to Mt. transaction_id)
Inv. mtl_system_items_ B table of materials (inventory_item_id is connected to Mt. inventory_item_id)
WIP. wip_entities discrete task table (including the task name, which can be connected with wip_entity_id of the wip_discrete_jobs discrete task list and wip_entity_id)
Others: Source Type-job or schedule Mt. transaction_source_type_id
Transaction Processing type-wip completion Mt. transaction_type_id
SQL:
Select Mt. organization_id,
Mt. transaction_id,
Mt. transaction_type_id,
MSI. segment1 item_no,
MSI. description,
MTL. lot_number,
Mt. subinventory_code,
Mt. locator_id,
Mt. transaction_uom,
nvl (MTL. transaction_quantity, MT. transaction_quantity) transaction_quantity,
WE. wip_entity_name transaction_source,
trunc (MT. transaction_date) transaction_date,
Mt. transaction_reference
From Inv. mtl_material_transactions Mt,
Inv. mtl_transaction_lot_numbers MTL,
Inv. mtl_system_items_ B MSI,
WIP. wip_entities we
Where Mt. transaction_id = MTL. transaction_id ( + )
And Mt. organization_id = MTL. organization_id ( + )
And Mt. organization_id = MSI. organization_id
And Mt. inventory_item_id = MSI. inventory_item_id
And Mt. transaction_type_id In ( 44 , 17 )
And Mt. transaction_source_id = We. wip_entity_id
And Mt. organization_id = We. organization_id
And Mt. organization_id = : P_org_id
And (Trunc (mt. transaction_date) > = Trunc (: fm_date) Or : Fm_date Is Null )
And (Trunc (mt. transaction_date) <= Trunc (: to_date) Or : To_date Is Null )
Order By MSI. segment1