oracle 欠料报表,MRP:欠料分析报表

代码：SELECT MSI.ORGANIZATION_ID,

MSI.INVENTORY_ITEM_ID,

MSI1.SEGMENT1,

MSI.DESCRIPTION,

MSI.COMPILE_DESIGNATOR,

MSI.BUYER_NAME,

MSI.PLANNER_CODE,

MIN(CUX_ITEM_CHECK_IN_QTY(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID)) ITEM_CHECK_IN_QTY,

MIN(CUX_ITEM_CHECK_REJECT_QTY(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID)) ITEM_CHECK_REJECT_QTY,

MIN(CUX_ITEM_NOCHECK_HK_QTY(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID)) ITEM_NOCHECK_HK_QTY,

MIN(CUX_ITEM_NOCHECK_SZ_QTY(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID)) ITEM_NOCHECK_SZ_QTY,

MIN(CUX_ITEM_REQUISITION_QTY(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID)) ITEM_REQUISITION_QTY,

MIN(CUX_ITEM_ONROAD_QTY(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID)) ITEM_ONROAD_QTY,

MIN(NVL(CUX_ITEM_SUB_ONHAND(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID,'102001','','',''),0)) INV_MATERIAL,

MIN(NVL(CUX_ITEM_SUB_ONHAND(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID,'102091','','',''),0)) INV_PRE,

MIN(NVL(CUX_ITEM_SUB_ONHAND(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID,'102101','','',''),0)) INV_SMT,

MIN(NVL(CUX_ITEM_SUB_ONHAND(MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID,'105002','','',''),0)) INV_ASS,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE)),-1,USING_REQUIREMENTS_QUANTITY,0)) DUE_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*1)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*0)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) WEEK1_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*2)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*1)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) WEEK2_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*3)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*2)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) WEEK3_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*4)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*3)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) WEEK4_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*5)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*4)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) WEEK5_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*6)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*5)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) WEEK6_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*7)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*6)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) WEEK7_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*8)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*7)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) WEEK8_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*8+29)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*8)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) MONTH3_QTY,

SUM(DECODE(SIGN(MGR.USING_ASSEMBLY_DEMAND_DATE-TRUNC(SYSDATE+7*8+58)),-1,DECODE(SIGN(TRUNC(SYSDATE-1+7*8+29)-MGR.USING_ASSEMBLY_DEMAND_DATE),-1,USING_REQUIREMENTS_QUANTITY,0),0)) MONTH4_QTY--,

FROM MRP_GROSS_REQUIREMENTS MGR,

MRP_SYSTEM_ITEMS MSI,

MTL_SYSTEM_ITEMS_B MSI1

WHERE 1=1

AND MGR.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR

AND MGR.ORGANIZATION_ID = MSI.ORGANIZATION_ID

AND MGR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID

AND MSI1.ORGANIZATION_ID = MSI.ORGANIZATION_ID

AND MSI1.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID

--AND MSI.INVENTORY_ITEM_ID=4689

AND MSI1.ORGANIZATION_ID=:P_ORG_ID

AND MSI.COMPILE_DESIGNATOR=:P_PLAN_NAME

GROUP BY MSI.ORGANIZATION_ID,

MSI.INVENTORY_ITEM_ID,

MSI1.SEGMENT1,

MSI.DESCRIPTION,

MSI.COMPILE_DESIGNATOR,

MSI.BUYER_NAME,

MSI.PLANNER_CODE