学步园

模块： WIP

相关表：inv.mtl_material_transactions 物料事务处理表 （transaction_source_id 与 we.wip_entity_id 连接）

            inv.mtl_transaction_lot_numbers  物料事务处理批次号表（transaction_id 与 mmt.transaction_id 连接）

            inv.mtl_system_items_b   物料表 （inventory_item_id与mmt.inventory_item_id连接）

            wip.wip_entities               离散任务表 （包括任务名称，可用wip_entity_id与wip_discrete_jobs 离散任务明细表的WIP_ENTITY_ID相连接）

其他：来源类型 - Job or Schedule     MMT.TRANSACTION_SOURCE_TYPE_ID

         事务处理类型 - WIP Completion    MMT.TRANSACTION_TYPE_ID

SQL:

select mmt.organization_id,
       mmt.transaction_id,
       mmt.transaction_type_id,
       msi.segment1 item_no,
       msi.description,
       mtl.lot_number,
       mmt.subinventory_code,
       mmt.locator_id,
       mmt.transaction_uom,
       nvl(mtl.transaction_quantity, mmt.transaction_quantity) transaction_quantity,
       we.wip_entity_name transaction_source,
       trunc(mmt.transaction_date) transaction_date,
       mmt.transaction_reference
  from inv.mtl_material_transactions   mmt,
       inv.mtl_transaction_lot_numbers mtl,
       inv.mtl_system_items_b          msi,
       wip.wip_entities                we
 where mmt.transaction_id = mtl.transaction_id(+)
   and mmt.organization_id = mtl.organization_id(+)
   and mmt.organization_id = msi.organization_id
   and mmt.inventory_item_id = msi.inventory_item_id
   and mmt.transaction_type_id in (44, 17)
   and mmt.transaction_source_id = we.wip_entity_id
   and mmt.organization_id = we.organization_id
   and mmt.organization_id = :p_org_id
   and (trunc(mmt.transaction_date) >= trunc(:fm_date) or :fm_date is null)
   and (trunc(mmt.transaction_date) <= trunc(:to_date) or :to_date is null)
 order by msi.segment1