sql1——视图

CREATE OR REPLACE VIEW PROC_UNCREATE_ITEM_INFO_V AS

SELECT
PTI.TASK_NO ENTITY_NAME, /*任务号*/
PTI.TASK_TYPE, /*任务类型*/
PTI.ORGANIZATION_ID, /*组织ID*/
PTI.EXTERNAL_FACTORY,/*外协厂*/
DECODE(PTI.TASK_TYPE,'整机任务',WMES.FIND_PROC_CONTRACT_NUMBER(PTI.TASK_NO)) CONTRACT_NUMBER, /*合同号*/
WDJ.START_QUANTITY TOTAL_NUM,/*总套数(数量)*/
WDJ.START_QUANTITY PICK_NUM,/*领用套数*/
EPP.PLANNING_GROUP_DESC PLANNING_GROUP, /*计划组*/
EPP.PLANNING_GROUP PLANNING_GROUP_CODE, /*计划组代码*/
EPP.PROJECT_NAME PRODUCT_TYPE, /*产品大类*/
EPP.PROJECT_NUMBER PRODUCT_TYPE_CODE,/*产品大类代码*/
EPP.PROJECT_ID,/*计划组ID*/
EII.ITEM_TYPE, /*物料属性*/
NVL(WRO.SEGMENT1,MSI.SEGMENT1) ITEM_CODE_OLD, /*原配置代码*/
MSI.SEGMENT1 ITEM_CODE, /*物料代码*/
MSI.DESCRIPTION ITEM_NAME, /*物料名称*/
MSI.ATTRIBUTE1 MODEL_TYPE, /*型号规格*/
WRO.OPERATION_SEQ_NUM PROCESS_ID, /*工序ID*/
EDSP.PROCESS_NO, /*工序号*/
EDSP.PROCESS_NAME, /*工序*/
ED.DEPT_NO,/*部门编码*/
ED.DEPT_NAME, /*部门*/
ED.PRODUCT_ADDRESS, /*生产地点*/
ED.PRODUCT_ADDRESS_CODE,/*生产地点代码*/
MSI.ATTRIBUTE14 ITEM_UNIT,/*单位*/
WRO.INVENTORY_ITEM_ID, /*物料ID*/
WRO.REQUIRED_QUANTITY, /*需求数*/
WRO.QUANTITY_ISSUED, /*已发数*/
DECODE(PTI.TASK_TYPE,'整机任务','',WRO.QUANTITY_PER_ASSEMBLY) SINGLE_NUM--, /*单个数量*/
--WMES.FIND_PROC_SUB_STOCK(WRO.INVENTORY_ITEM_ID,MSI.SEGMENT1,EPP.PLANNING_GROUP_DESC,ED.PRODUCT_ADDRESS_CODE,EII.ITEM_TYPE,EDSP.DEPT_NO,EPP.PROJECT_NAME,PTI.ORGANIZATION_ID) SUB_STOCK /*子库存*/
FROM WMES.PROC_TASK_INFO PTI
INNER JOIN WMES.WIP_REQUIREMENT_OPERATIONS WRO
ON WRO.WIP_ENTITY_ID = PTI.WIP_ENTITY_ID AND WRO.ORGANIZATION_ID = PTI.ORGANIZATION_ID AND OPERATION_SEQ_NUM > 0
INNER JOIN WMES.ERP_DEPT_STAND_PROC EDSP
ON EDSP.DEPARTMENT_ID = WRO.DEPARTMENT_ID AND EDSP.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND EDSP.ENABLED_FLAG = 'Y'
LEFT JOIN WMES.ERP_PROJECT_PLANGROUP EPP
ON EPP.PROJECT_ID = PTI.PROJECT_ID AND EPP.ORGANIZATION_ID = PTI.ORGANIZATION_ID
LEFT JOIN WMES.MTL_SYSTEM_ITEMS MSI
ON MSI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = PTI.ORGANIZATION_ID AND MSI.ENABLED_FLAG = 'Y'
LEFT JOIN WMES.ERP_ITEMTYPE_INFO EII
ON EII.ITEM_ID = WRO.INVENTORY_ITEM_ID
AND EII.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND EII.ENABLED_FLAG = 'Y'
LEFT JOIN WMES.WIP_DISCRETE_JOBS WDJ
ON WDJ.WIP_ENTITY_ID=PTI.ENTITY_ID AND WDJ.ORGANIZATION_ID=PTI.ORGANIZATION_ID
LEFT JOIN WMES.ERP_DEPT ED
ON ED.DEPT_NO = EDSP.DEPT_NO AND ED.ORGANIZATION_ID = EDSP.ORGANIZATION_ID AND ED.ENABLED_FLAG = 'Y';