Oracle EBS-SQL (WIP-13):检查任务组件未选MRP净值.sql

select WE.WIP_ENTITY_NAME                                              任务号,
MFG_LOOKUPS_WJS.MEANING                                 作业状态,
WDJ.CLASS_CODE                                                   任务类型,
MSI1.SEGMENT1                                                      装配编码,
Msi1.Description                                                       装配描述,
WDJ.SCHEDULED_START_DATE                           计划完成日期,
WDJ.SCHEDULED_COMPLETION_DATE                 计划完成日期,
nvl(WDJ.START_QUANTITY, 0)                                  计划数量,
nvl(WDJ.QUANTITY_COMPLETED, 0)                          完成数量,
MSI2.SEGMENT1                                                      物料编码,
Msi2.Description                                                       物料描述,

CIC.ITEM_COST                                                             成本,
nvl(WRO.REQUIRED_QUANTITY, 0)                            需求数量,
nvl(WRO.QUANTITY_ISSUED, 0)                                发送数量,
--  nvl(WRO.REQUIRED_QUANTITY, 0) - nvl(WRO.QUANTITY_ISSUED, 0) 未发,
WRO.MRP_NET_FLAG mrp                                              净值,
wro.supply_subinventory                                          供应子库,
ML.MEANING                                                           供应类型
from  WIP.WIP_DISCRETE_JOBS                                             WDJ,
WIP.WIP_REQUIREMENT_OPERATIONS                          WRO,
INV.Mtl_System_Items_B                                             MSI1,
INV.Mtl_System_Items_B                                             MSI2,
WIP.WIP_ENTITIES                                                         we,
BOM.CST_ITEM_COSTS                                                  CIC,
applsys.fnd_LOOKUP_values                                            ML,
applsys.fnd_LOOKUP_values

MFG_LOOKUPS_WJS
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 ML.LOOKUP_TYPE = 'WIP_SUPPLY'
AND ML.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
AND (MFG_LOOKUPS_WJS.LOOKUP_TYPE = 'WIP_JOB_STATUS')
AND (wdj.STATUS_TYPE = MFG_LOOKUPS_WJS.LOOKUP_CODE)
AND CIC.INVENTORY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
AND CIC.ORGANIZATION_ID = MSI2.ORGANIZATION_ID
AND CIC.COST_TYPE_ID = 1
--AND MFG_LOOKUPS_WJS.MEANING<>'已关闭'
AND MFG_LOOKUPS_WJS.MEANING<>'已取消'
AND trunc(WDJ.SCHEDULED_COMPLETION_DATE) between to_date('20**/01/01','yyyy-mm-dd') and to_date('20**/01/31','yyyy-mm-dd')
AND MFG_LOOKUPS_WJS.Language='ZHS'
AND ML.Language='ZHS'
AND WRO.MRP_NET_FLAG <> 1
--AND nvl(WRO.REQUIRED_QUANTITY, 0) - nvl(WRO.QUANTITY_ISSUED, 0) > 0
--AND WRO.REQUIRED_QUANTITY < WRO.QUANTITY_ISSUED
--AND nvl(WDJ.START_QUANTITY, 0)< nvl(WDJ.QUANTITY_COMPLETED, 0)
--AND MFG_LOOKUPS_WJS.MEANING = '完成'
--AND nvl(WDJ.START_QUANTITY, 0)=nvl(WDJ.QUANTITY_COMPLETED, 0)
--AND WRO.WIP_SUPPLY_TYPE <> 6