Oracle EBS-SQL (WIP-16):检查期间手工下达的车间任务数.sql
select
WE.DESCRIPTION 任务说明,
--DECODE(WE.SOURCE_CODE,'MRP','工作台','','手工') 下达方式,
DECODE(WT.ENTITY_TYPE, 1, '手工', 3, 'MRP工作台下达' ) 下达方式,
WT.WIP_ENTITY_NAME 任务名称,
MSI.SEGMENT1 装配件,
MSI.DESCRIPTION 描述,
FLV.MEANING 车间状态,
WE.CLASS_CODE 车间类别,
TO_CHAR(WE.CREATION_DATE,'YYYY-MM-DD') 创建日期,
TO_CHAR(WE.SCHEDULED_START_DATE,'YYYY-MM-DD') 计划开始日期,
TO_CHAR(WE.SCHEDULED_COMPLETION_DATE,'YYYY-MM-DD') 计划完成日期,
TO_CHAR(WE.DATE_RELEASED,'YYYY-MM-DD') 发放日期,
TO_CHAR(WE.DATE_COMPLETED,'YYYY-MM-DD') 完成日期,
TO_CHAR(WE.DATE_CLOSED,'YYYY-MM-DD') 关闭日期,
WE.START_QUANTITY 开始数量,
we.QUANTITY_COMPLETED 完成数量
from
WIP.WIP_DISCRETE_JOBS WE,
WIP.WIP_ENTITIES WT,
APPS.FND_LOOKUP_VALUES FLV,
INV.MTL_SYSTEM_ITEMS_B MSI
where
MSI.ORGANIZATION_ID=X
AND MSI.ORGANIZATION_ID=WE.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID=WT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID=we.PRIMARY_ITEM_ID
AND WE.WIP_ENTITY_ID=WT.WIP_ENTITY_ID
AND FLV.LOOKUP_TYPE ='WIP_JOB_STATUS'
AND WE.STATUS_TYPE = FLV.LOOKUP_CODE(+)
--AND WE.JOB_TYPE=1
AND WT.ENTITY_TYPE=1
--AND WE.CLASS_CODE NOT LIKE ('A%')
AND WE.SOURCE_CODE IS NULL
AND FLV.LANGUAGE='ZHS'
AND (TRUNC(we.creation_date) BETWEEN TO_DATE('20**-01-01','YYYY-MM-DD') AND TO_DATE('20**-01-31','YYYY-MM-DD'))