eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSD_DEM_CONSTRAINED_FORECAST_V

Source

View Text - Preformatted


SELECT total.plan_id, total.supply_plan, total.item_code, total.org_code, total.site_code, total.quantity, total.sdate, total.demand_class_code FROM (SELECT plans.plan_id, plans.compile_designator supply_plan, nvl(msib.item_name, msi.item_name) item_code, mtp_org.organization_code org_code, msd_dem_common_utilities.get_site_for_csf (dem.sr_instance_id, dem.customer_id, dem.customer_site_id) site_code, dem.quantity_by_due_date quantity, TRUNC(dem.using_assembly_demand_date) sdate, nvl(mdc.meaning, msd_dem_sr_util.get_null_code) demand_class_code FROM msc_demands dem, msc_plans plans, msc_system_items msi, msc_system_items msia, msc_trading_partners mtp_org, msc_demand_classes mdc, (SELECT nvl(fnd_profile.VALUE('MSD_DEM_TWO_LEVEL_PLANNING'), '2') profile_value FROM dual) tlp, msc_system_items msib WHERE dem.plan_id != -1 AND dem.plan_id = plans.plan_id AND dem.plan_id = msia.plan_id AND dem.sr_instance_id = msia.sr_instance_id AND dem.organization_id = msia.organization_id AND dem.using_assembly_item_id = msia.inventory_item_id AND((dem.inventory_item_id != dem.using_assembly_item_id AND msia.bom_item_type = 5) OR(dem.inventory_item_id = dem.using_assembly_item_id)) AND dem.origination_type IN(6, 7, 8, 9, 11, 29, 30, 42, 22) 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 msi.mrp_planning_code <> 6 AND ((dem.origination_type IN (6,30) AND nvl(dem.source_organization_id, -23453) = -23453) OR dem.origination_type NOT IN (6,30)) AND mtp_org.sr_instance_id = dem.sr_instance_id AND mtp_org.sr_tp_id = dem.organization_id AND mtp_org.partner_type = 3 AND nvl(dem.quantity_by_due_date, 0) != 0 AND mdc.sr_instance_id (+) = dem.sr_instance_id AND mdc.demand_class (+) = dem.demand_class AND decode(nvl(tlp.profile_value, '2'), '1', decode(nvl(msi.ato_forecast_control, 3), 3, msd_dem_common_utilities.is_product_family_forecastable(msi.product_family_id, msi.sr_instance_id), 1), decode(nvl(msi.ato_forecast_control, 3), 3, 2, 1)) = 1 AND dem.plan_id = msib.plan_id (+) AND dem.original_inst_id = msib.sr_instance_id (+) AND dem.original_org_id = msib.organization_id (+) AND dem.original_item_id = msib.inventory_item_id (+) AND msib.mrp_planning_code (+) <> 6 UNION ALL SELECT plans.plan_id, plans.compile_designator supply_plan, nvl(msib.item_name, msi.item_name) item_code, mtp_org.organization_code org_code, msd_dem_common_utilities.get_site_for_csf (dem.sr_instance_id, dem.customer_id, dem.customer_site_id) site_code, (dem.using_requirement_quantity -nvl(dem.quantity_by_due_date, 0)) quantity, TRUNC(dem.dmd_satisfied_date) sdate, nvl(mdc.meaning, msd_dem_sr_util.get_null_code) demand_class_code FROM msc_demands dem, msc_plans plans, msc_system_items msi, msc_system_items msia, msc_trading_partners mtp_org, msc_demand_classes mdc, (SELECT nvl(fnd_profile.VALUE('MSD_DEM_TWO_LEVEL_PLANNING'), '2') profile_value FROM dual) tlp, msc_system_items msib WHERE dem.plan_id != -1 AND dem.plan_id = plans.plan_id AND dem.plan_id = msia.plan_id AND dem.sr_instance_id = msia.sr_instance_id AND dem.using_assembly_item_id = msia.inventory_item_id AND dem.organization_id = msia.organization_id AND((dem.inventory_item_id != dem.using_assembly_item_id AND msia.bom_item_type = 5) OR(dem.inventory_item_id = dem.using_assembly_item_id)) AND dem.origination_type IN(6, 7, 8, 9, 11, 29, 30, 42, 22) 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 msi.mrp_planning_code <> 6 AND ((dem.origination_type IN (6,30) AND nvl(dem.source_organization_id, -23453) = -23453) OR dem.origination_type NOT IN (6,30)) AND mtp_org.sr_instance_id = dem.sr_instance_id AND mtp_org.sr_tp_id = dem.organization_id AND mtp_org.partner_type = 3 AND(dem.using_requirement_quantity -nvl(dem.quantity_by_due_date, 0) != 0) AND mdc.sr_instance_id (+) = dem.sr_instance_id AND mdc.demand_class (+) = dem.demand_class AND decode(nvl(tlp.profile_value, '2'), '1', decode(nvl(msi.ato_forecast_control, 3), 3, msd_dem_common_utilities.is_product_family_forecastable(msi.product_family_id, msi.sr_instance_id), 1), decode(nvl(msi.ato_forecast_control, 3), 3, 2, 1)) = 1 AND dem.plan_id = msib.plan_id (+) AND dem.original_inst_id = msib.sr_instance_id (+) AND dem.original_org_id = msib.organization_id (+) AND dem.original_item_id = msib.inventory_item_id (+) AND msib.mrp_planning_code (+) <> 6 ) total

View Text - HTML Formatted

SELECT TOTAL.PLAN_ID
, TOTAL.SUPPLY_PLAN
, TOTAL.ITEM_CODE
, TOTAL.ORG_CODE
, TOTAL.SITE_CODE
, TOTAL.QUANTITY
, TOTAL.SDATE
, TOTAL.DEMAND_CLASS_CODE
FROM (SELECT PLANS.PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, NVL(MSIB.ITEM_NAME
, MSI.ITEM_NAME) ITEM_CODE
, MTP_ORG.ORGANIZATION_CODE ORG_CODE
, MSD_DEM_COMMON_UTILITIES.GET_SITE_FOR_CSF (DEM.SR_INSTANCE_ID
, DEM.CUSTOMER_ID
, DEM.CUSTOMER_SITE_ID) SITE_CODE
, DEM.QUANTITY_BY_DUE_DATE QUANTITY
, TRUNC(DEM.USING_ASSEMBLY_DEMAND_DATE) SDATE
, NVL(MDC.MEANING
, MSD_DEM_SR_UTIL.GET_NULL_CODE) DEMAND_CLASS_CODE
FROM MSC_DEMANDS DEM
, MSC_PLANS PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_SYSTEM_ITEMS MSIA
, MSC_TRADING_PARTNERS MTP_ORG
, MSC_DEMAND_CLASSES MDC
, (SELECT NVL(FND_PROFILE.VALUE('MSD_DEM_TWO_LEVEL_PLANNING')
, '2') PROFILE_VALUE
FROM DUAL) TLP
, MSC_SYSTEM_ITEMS MSIB
WHERE DEM.PLAN_ID != -1
AND DEM.PLAN_ID = PLANS.PLAN_ID
AND DEM.PLAN_ID = MSIA.PLAN_ID
AND DEM.SR_INSTANCE_ID = MSIA.SR_INSTANCE_ID
AND DEM.ORGANIZATION_ID = MSIA.ORGANIZATION_ID
AND DEM.USING_ASSEMBLY_ITEM_ID = MSIA.INVENTORY_ITEM_ID AND((DEM.INVENTORY_ITEM_ID != DEM.USING_ASSEMBLY_ITEM_ID
AND MSIA.BOM_ITEM_TYPE = 5) OR(DEM.INVENTORY_ITEM_ID = DEM.USING_ASSEMBLY_ITEM_ID))
AND DEM.ORIGINATION_TYPE IN(6
, 7
, 8
, 9
, 11
, 29
, 30
, 42
, 22)
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 MSI.MRP_PLANNING_CODE <> 6
AND ((DEM.ORIGINATION_TYPE IN (6
, 30)
AND NVL(DEM.SOURCE_ORGANIZATION_ID
, -23453) = -23453) OR DEM.ORIGINATION_TYPE NOT IN (6
, 30))
AND MTP_ORG.SR_INSTANCE_ID = DEM.SR_INSTANCE_ID
AND MTP_ORG.SR_TP_ID = DEM.ORGANIZATION_ID
AND MTP_ORG.PARTNER_TYPE = 3
AND NVL(DEM.QUANTITY_BY_DUE_DATE
, 0) != 0
AND MDC.SR_INSTANCE_ID (+) = DEM.SR_INSTANCE_ID
AND MDC.DEMAND_CLASS (+) = DEM.DEMAND_CLASS
AND DECODE(NVL(TLP.PROFILE_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, MSD_DEM_COMMON_UTILITIES.IS_PRODUCT_FAMILY_FORECASTABLE(MSI.PRODUCT_FAMILY_ID
, MSI.SR_INSTANCE_ID)
, 1)
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, 2
, 1)) = 1
AND DEM.PLAN_ID = MSIB.PLAN_ID (+)
AND DEM.ORIGINAL_INST_ID = MSIB.SR_INSTANCE_ID (+)
AND DEM.ORIGINAL_ORG_ID = MSIB.ORGANIZATION_ID (+)
AND DEM.ORIGINAL_ITEM_ID = MSIB.INVENTORY_ITEM_ID (+)
AND MSIB.MRP_PLANNING_CODE (+) <> 6 UNION ALL SELECT PLANS.PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, NVL(MSIB.ITEM_NAME
, MSI.ITEM_NAME) ITEM_CODE
, MTP_ORG.ORGANIZATION_CODE ORG_CODE
, MSD_DEM_COMMON_UTILITIES.GET_SITE_FOR_CSF (DEM.SR_INSTANCE_ID
, DEM.CUSTOMER_ID
, DEM.CUSTOMER_SITE_ID) SITE_CODE
, (DEM.USING_REQUIREMENT_QUANTITY -NVL(DEM.QUANTITY_BY_DUE_DATE
, 0)) QUANTITY
, TRUNC(DEM.DMD_SATISFIED_DATE) SDATE
, NVL(MDC.MEANING
, MSD_DEM_SR_UTIL.GET_NULL_CODE) DEMAND_CLASS_CODE
FROM MSC_DEMANDS DEM
, MSC_PLANS PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_SYSTEM_ITEMS MSIA
, MSC_TRADING_PARTNERS MTP_ORG
, MSC_DEMAND_CLASSES MDC
, (SELECT NVL(FND_PROFILE.VALUE('MSD_DEM_TWO_LEVEL_PLANNING')
, '2') PROFILE_VALUE
FROM DUAL) TLP
, MSC_SYSTEM_ITEMS MSIB
WHERE DEM.PLAN_ID != -1
AND DEM.PLAN_ID = PLANS.PLAN_ID
AND DEM.PLAN_ID = MSIA.PLAN_ID
AND DEM.SR_INSTANCE_ID = MSIA.SR_INSTANCE_ID
AND DEM.USING_ASSEMBLY_ITEM_ID = MSIA.INVENTORY_ITEM_ID
AND DEM.ORGANIZATION_ID = MSIA.ORGANIZATION_ID AND((DEM.INVENTORY_ITEM_ID != DEM.USING_ASSEMBLY_ITEM_ID
AND MSIA.BOM_ITEM_TYPE = 5) OR(DEM.INVENTORY_ITEM_ID = DEM.USING_ASSEMBLY_ITEM_ID))
AND DEM.ORIGINATION_TYPE IN(6
, 7
, 8
, 9
, 11
, 29
, 30
, 42
, 22)
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 MSI.MRP_PLANNING_CODE <> 6
AND ((DEM.ORIGINATION_TYPE IN (6
, 30)
AND NVL(DEM.SOURCE_ORGANIZATION_ID
, -23453) = -23453) OR DEM.ORIGINATION_TYPE NOT IN (6
, 30))
AND MTP_ORG.SR_INSTANCE_ID = DEM.SR_INSTANCE_ID
AND MTP_ORG.SR_TP_ID = DEM.ORGANIZATION_ID
AND MTP_ORG.PARTNER_TYPE = 3 AND(DEM.USING_REQUIREMENT_QUANTITY -NVL(DEM.QUANTITY_BY_DUE_DATE
, 0) != 0)
AND MDC.SR_INSTANCE_ID (+) = DEM.SR_INSTANCE_ID
AND MDC.DEMAND_CLASS (+) = DEM.DEMAND_CLASS
AND DECODE(NVL(TLP.PROFILE_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, MSD_DEM_COMMON_UTILITIES.IS_PRODUCT_FAMILY_FORECASTABLE(MSI.PRODUCT_FAMILY_ID
, MSI.SR_INSTANCE_ID)
, 1)
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, 2
, 1)) = 1
AND DEM.PLAN_ID = MSIB.PLAN_ID (+)
AND DEM.ORIGINAL_INST_ID = MSIB.SR_INSTANCE_ID (+)
AND DEM.ORIGINAL_ORG_ID = MSIB.ORGANIZATION_ID (+)
AND DEM.ORIGINAL_ITEM_ID = MSIB.INVENTORY_ITEM_ID (+)
AND MSIB.MRP_PLANNING_CODE (+) <> 6 ) TOTAL