select
a.*,
b.BOM定额,
a.WIP定额 - b.BOM定额 差异
from
(select
WE.WIP_ENTITY_NAME 任务号,
Decode(wdj.JOB_TYPE,1,'标准',3,'非标准') 作业类型,
MSI1.SEGMENT1 项目编码,
Msi1.Description 项目描述,
nvl(WDJ.START_QUANTITY,0) 任务数量,
nvl(WDJ.QUANTITY_COMPLETED,0) 完成数量,
MSI2.SEGMENT1 物料编码,
Msi2.Description 物料描述,
nvl(WRO.REQUIRED_QUANTITY,0) 需求数量,
nvl(WRO.QUANTITY_ISSUED,0) 发送数量,
nvl(WRO.REQUIRED_QUANTITY,0)-nvl(WRO.QUANTITY_ISSUED,0) 未发,
wro.quantity_per_assembly WIP定额
from
WIP.WIP_DISCRETE_JOBS WDJ,
WIP.WIP_REQUIREMENT_OPERATIONS WRO,
apps.Mtl_System_Items MSI1,
apps.Mtl_System_Items MSI2,
WIP.WIP_ENTITIES we
where
WE.ORGANIZATION_ID =x
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND Msi1.INVENTORY_ITEM_ID(+) = We.PRIMARY_ITEM_ID
AND Msi1.ORGANIZATION_ID = We.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID(+)= WRO.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID
AND msi2.item_type(+) <>'PH'
)a,
(select
msi.segment1 项目编码,
msi1.segment1 物料编码,
msi1.description 物料描述,
COM.COMPONENT_QUANTITY BOM定额
from
apps.MTL_SYSTEM_ITEMS msi,
apps.MTL_SYSTEM_ITEMS msi1,
apps.BOM_BILL_OF_MATERIALS BOM,
apps.BOM_INVENTORY_COMPONENTS COM
where
msi.ORGANIZATION_ID=x
AND msi.segment1 in
(select
MSI1.SEGMENT1
from
WIP.WIP_DISCRETE_JOBS WDJ,
WIP.WIP_REQUIREMENT_OPERATIONS WRO,
apps.Mtl_System_Items MSI1,
apps.Mtl_System_Items MSI2,
WIP.WIP_ENTITIES we
where
AND WE.ORGANIZATION_ID = x
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND Msi1.INVENTORY_ITEM_ID(+) = We.PRIMARY_ITEM_ID
AND Msi1.ORGANIZATION_ID(+) = We.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID(+)= WRO.INVENTORY_ITEM_ID
AND msi2.item_type(+) <>'PH'
AND MSI2.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID
)
AND bom.assembly_item_id=msi.inventory_item_id
AND bom.bill_sequence_id=COM.bill_sequence_id
AND COM.component_item_id=msi1.inventory_item_id
AND msi1.organization_id=msi.organization_id
AND COM.DISABLE_DATE is null
)b
where
a.项目编码(+)=b.项目编码
AND a.物料编码(+)=b.物料编码
AND (a.WIP定额 - b.BOM定额 <> 0 or a.WIP定额 - b.BOM定额 is null)