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 物料描述 ,nvl(wro.required_quantity ,0) 需求数量 ,nvl(wro.quantity_issued ,0) 发料数量 ,nvl(wro.required_quantity ,0) - nvl(wro.quantity_issued ,0) 未发数量 ,wro.supply_subinventory 供应子库 ,ml.meaning 供应类型 ,onhand.subinventory_code 子库 ,onhand.TRANSACTION_QUANTITY 子库数量 ,(select nvl(sum(moq.TRANSACTION_QUANTITY),0) from apps.mtl_onhand_quantities moq where moq.organization_id=851 and moq.inventory_item_id=msi2.inventory_item_id) 现有量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 ,(select moq.inventory_item_id, moq.subinventory_code, sum(moq.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY from apps.mtl_onhand_quantities moq where moq.organization_id=x group by moq.inventory_item_id, moq.subinventory_code) onhand 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 onhand.inventory_item_id(+)=msi2.inventory_item_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 ml.meaning not like '虚拟件' 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 nvl(wro.required_quantity ,0) - nvl(wro.quantity_issued ,0) > 0 and nvl(wdj.start_quantity ,0) = nvl(wdj.quantity_completed ,0) order by 1,10