View: MSC_MONTHLY_SUPPLY_DEMAND_V

View Text

SELECT DEM.PLAN_ID
, DEM.CUSTOMER_SITE_ID
, DEM.ORGANIZATION_ID
, CS.REF_ITEM_ID
, DEM.INVENTORY_ITEM_ID
, TO_CHAR(DEM.USING_ASSEMBLY_DEMAND_DATE
, 'MM/YYYY')
, SUM(DEM.USING_REQUIREMENT_QUANTITY)
FROM MSC_DEMANDS DEM
, MSC_ITEM_CROSS_REFS_V CS
WHERE CS.ITEM_ID = DEM.INVENTORY_ITEM_ID
AND CS.PARTY_ID = DEM.ORGANIZATION_ID
AND CS.REF_PARTY_ID = DEM.CUSTOMER_SITE_ID
AND DEM.SR_INSTANCE_ID = -1
AND DEM.ORIGINATION_TYPE IN (6
, 7)
AND TO_CHAR(DEM.USING_ASSEMBLY_DEMAND_DATE
, 'MM/YYYY') >= TO_CHAR(SYSDATE
, 'MM/YYYY') GROUP BY DEM.PLAN_ID
, DEM.CUSTOMER_SITE_ID
, DEM.ORGANIZATION_ID
, CS.REF_ITEM_ID
, DEM.INVENTORY_ITEM_ID
, TO_CHAR(DEM.USING_ASSEMBLY_DEMAND_DATE
, 'MM/YYYY') UNION ALL SELECT SUP.PLAN_ID
, SUP.ORGANIZATION_ID
, SUP.SUPPLIER_SITE_ID
, SUP.INVENTORY_ITEM_ID
, CROSS.ITEM_ID
, TO_CHAR(SUP.NEW_SCHEDULE_DATE
, 'MM/YYYY')
, -SUM(SUP.NEW_ORDER_QUANTITY)
FROM MSC_SUPPLIES SUP
, MSC_ITEM_CROSS_REFS_V CROSS
WHERE SUP.INVENTORY_ITEM_ID = CROSS.REF_ITEM_ID
AND SUP.ORGANIZATION_ID = CROSS.REF_PARTY_ID
AND SUP.SUPPLIER_SITE_ID = CROSS.PARTY_ID
AND SUP.SR_INSTANCE_ID = -1
AND SUP.ORDER_TYPE IN (33
, 35
, 36
, 37
, 38
, 39
, 44)
AND TO_CHAR(SUP.NEW_SCHEDULE_DATE
, 'MM/YYYY') >= TO_CHAR(SYSDATE
, 'MM/YYYY') GROUP BY SUP.PLAN_ID
, SUP.ORGANIZATION_ID
, SUP.SUPPLIER_SITE_ID
, SUP.INVENTORY_ITEM_ID
, CROSS.ITEM_ID
, TO_CHAR(SUP.NEW_SCHEDULE_DATE
, 'MM/YYYY')

Columns