VIEW: APPS.MSC_CONSTRAINED_FORECAST_V

Source

View Text - Preformatted

SELECT total.demand_id, total.plan_id, total.compile_designator, total.sr_instance_id , total.organization_id, total.inventory_item_id, total.customer_id , total.customer_site_id, total.item_name, total.description, total.uom_code , total.planner_code,total.abc_class_name, total.quantity quantity , total.end_date, total.project_id, total.task_id, total.planning_group , total.origination_type, total.order_number,total.planned_arrival_date, total.dmd_satisfied_date from (SELECT dem.demand_id, dem.plan_id plan_id, plans.compile_designator , dem.sr_instance_id, dem.organization_id, dem.inventory_item_id, dem.customer_id , dem.customer_site_id, msi.item_name, msi.description, msi.uom_code, msi.planner_code ,msi.abc_class_name, dem.quantity_by_due_date quantity, dem.using_assembly_demand_date end_date , dem.project_id, dem.task_id, dem.planning_group , dem.origination_type, dem.order_number,dem.planned_arrival_date, dem.dmd_satisfied_date FROM msc_plans plans, msc_system_items msi, msc_demands dem WHERE dem.plan_id != -1 and dem.plan_id = plans.plan_id and dem.inventory_item_id = dem.using_assembly_item_id and dem.origination_type in (6,7,8,9,11,29,30,42) and dem.plan_id = msi.plan_id and dem.sr_instance_id = msi.sr_instance_id and dem.organization_id = msi.organization_id and dem.inventory_item_id = msi.inventory_item_id and nvl(dem.quantity_by_due_date,0) <> 0 UNION ALL SELECT dem.demand_id, dem.plan_id plan_id, plans.compile_designator , dem.sr_instance_id, dem.organization_id, dem.inventory_item_id, dem.customer_id , dem.customer_site_id, msi.item_name, msi.description, msi.uom_code, msi.planner_code ,msi.abc_class_name, (dem.USING_REQUIREMENT_QUANTITY - nvl(dem.quantity_by_due_date,0)) quantity , dem.dmd_satisfied_date due_date, dem.project_id, dem.task_id, dem.planning_group , dem.origination_type , dem.order_number,dem.planned_arrival_date, dem.dmd_satisfied_date FROM msc_plans plans, msc_system_items msi, msc_demands dem WHERE dem.plan_id != -1 and dem.plan_id = plans.plan_id and dem.inventory_item_id = dem.using_assembly_item_id and dem.origination_type in (6,7,8,9,11,29,30,42) and dem.plan_id = msi.plan_id and dem.inventory_item_id = msi.inventory_item_id and dem.sr_instance_id = msi.sr_instance_id and dem.organization_id = msi.organization_id and dem.dmd_satisfied_date is not null and (dem.USING_REQUIREMENT_QUANTITY - nvl(dem.quantity_by_due_date,0)) <> 0 ) total

View Text - HTML Formatted

SELECT TOTAL.DEMAND_ID
, TOTAL.PLAN_ID
, TOTAL.COMPILE_DESIGNATOR
, TOTAL.SR_INSTANCE_ID
, TOTAL.ORGANIZATION_ID
, TOTAL.INVENTORY_ITEM_ID
, TOTAL.CUSTOMER_ID
, TOTAL.CUSTOMER_SITE_ID
, TOTAL.ITEM_NAME
, TOTAL.DESCRIPTION
, TOTAL.UOM_CODE
, TOTAL.PLANNER_CODE
, TOTAL.ABC_CLASS_NAME
, TOTAL.QUANTITY QUANTITY
, TOTAL.END_DATE
, TOTAL.PROJECT_ID
, TOTAL.TASK_ID
, TOTAL.PLANNING_GROUP
, TOTAL.ORIGINATION_TYPE
, TOTAL.ORDER_NUMBER
, TOTAL.PLANNED_ARRIVAL_DATE
, TOTAL.DMD_SATISFIED_DATE
FROM (SELECT DEM.DEMAND_ID
, DEM.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR
, DEM.SR_INSTANCE_ID
, DEM.ORGANIZATION_ID
, DEM.INVENTORY_ITEM_ID
, DEM.CUSTOMER_ID
, DEM.CUSTOMER_SITE_ID
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, MSI.UOM_CODE
, MSI.PLANNER_CODE
, MSI.ABC_CLASS_NAME
, DEM.QUANTITY_BY_DUE_DATE QUANTITY
, DEM.USING_ASSEMBLY_DEMAND_DATE END_DATE
, DEM.PROJECT_ID
, DEM.TASK_ID
, DEM.PLANNING_GROUP
, DEM.ORIGINATION_TYPE
, DEM.ORDER_NUMBER
, DEM.PLANNED_ARRIVAL_DATE
, DEM.DMD_SATISFIED_DATE
FROM MSC_PLANS PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_DEMANDS DEM
WHERE DEM.PLAN_ID != -1
AND DEM.PLAN_ID = PLANS.PLAN_ID
AND DEM.INVENTORY_ITEM_ID = DEM.USING_ASSEMBLY_ITEM_ID
AND DEM.ORIGINATION_TYPE IN (6
, 7
, 8
, 9
, 11
, 29
, 30
, 42)
AND DEM.PLAN_ID = MSI.PLAN_ID
AND DEM.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND DEM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND DEM.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND NVL(DEM.QUANTITY_BY_DUE_DATE
, 0) <> 0 UNION ALL SELECT DEM.DEMAND_ID
, DEM.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR
, DEM.SR_INSTANCE_ID
, DEM.ORGANIZATION_ID
, DEM.INVENTORY_ITEM_ID
, DEM.CUSTOMER_ID
, DEM.CUSTOMER_SITE_ID
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, MSI.UOM_CODE
, MSI.PLANNER_CODE
, MSI.ABC_CLASS_NAME
, (DEM.USING_REQUIREMENT_QUANTITY - NVL(DEM.QUANTITY_BY_DUE_DATE
, 0)) QUANTITY
, DEM.DMD_SATISFIED_DATE DUE_DATE
, DEM.PROJECT_ID
, DEM.TASK_ID
, DEM.PLANNING_GROUP
, DEM.ORIGINATION_TYPE
, DEM.ORDER_NUMBER
, DEM.PLANNED_ARRIVAL_DATE
, DEM.DMD_SATISFIED_DATE
FROM MSC_PLANS PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_DEMANDS DEM
WHERE DEM.PLAN_ID != -1
AND DEM.PLAN_ID = PLANS.PLAN_ID
AND DEM.INVENTORY_ITEM_ID = DEM.USING_ASSEMBLY_ITEM_ID
AND DEM.ORIGINATION_TYPE IN (6
, 7
, 8
, 9
, 11
, 29
, 30
, 42)
AND DEM.PLAN_ID = MSI.PLAN_ID
AND DEM.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND DEM.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND DEM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND DEM.DMD_SATISFIED_DATE IS NOT NULL
AND (DEM.USING_REQUIREMENT_QUANTITY - NVL(DEM.QUANTITY_BY_DUE_DATE
, 0)) <> 0 ) TOTAL