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