VIEW: APPS.MSD_DEM_PLANNED_PRODUCTION_V

Source

View Text - Preformatted

SELECT plans.plan_id plan_id, plans.compile_designator supply_plan, msi.item_name item_code, mtp_org.organization_code org_code, nvl(mfp.allocated_quantity, decode(sup.transaction_id, mfp.transaction_id, 0, sup.new_order_quantity)) quantity, TRUNC(sup.new_schedule_date) sdate, nvl(mdc.meaning, msd_dem_sr_util.get_null_code) demand_class_code FROM msc_supplies sup, msc_plans plans, msc_demands dem, msc_full_pegging mfp, msc_system_items msi, 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 WHERE sup.plan_id != -1 AND sup.plan_id = plans.plan_id AND nvl(sup.new_order_quantity, 0) != 0 AND sup.order_type IN(1, 2, 3, 4, 5, 7, 8, 11, 12, 14, 15, 16, 17, 27, 28, 29, 30, 41) AND sup.organization_id = msi.organization_id AND sup.inventory_item_id = msi.inventory_item_id AND sup.plan_id = msi.plan_id AND sup.sr_instance_id = msi.sr_instance_id AND msi.mrp_planning_code <> 6 AND mtp_org.sr_instance_id = sup.sr_instance_id AND mtp_org.sr_tp_id = sup.organization_id AND mtp_org.partner_type = 3 AND sup.sr_instance_id = mfp.sr_instance_id(+) AND sup.plan_id = mfp.plan_id(+) AND sup.transaction_id = mfp.transaction_id(+) AND dem.sr_instance_id(+) = mfp.sr_instance_id AND dem.plan_id(+) = mfp.plan_id AND dem.demand_id(+) = mfp.demand_id 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

View Text - HTML Formatted

SELECT PLANS.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, MSI.ITEM_NAME ITEM_CODE
, MTP_ORG.ORGANIZATION_CODE ORG_CODE
, NVL(MFP.ALLOCATED_QUANTITY
, DECODE(SUP.TRANSACTION_ID
, MFP.TRANSACTION_ID
, 0
, SUP.NEW_ORDER_QUANTITY)) QUANTITY
, TRUNC(SUP.NEW_SCHEDULE_DATE) SDATE
, NVL(MDC.MEANING
, MSD_DEM_SR_UTIL.GET_NULL_CODE) DEMAND_CLASS_CODE
FROM MSC_SUPPLIES SUP
, MSC_PLANS PLANS
, MSC_DEMANDS DEM
, MSC_FULL_PEGGING MFP
, MSC_SYSTEM_ITEMS MSI
, 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
WHERE SUP.PLAN_ID != -1
AND SUP.PLAN_ID = PLANS.PLAN_ID
AND NVL(SUP.NEW_ORDER_QUANTITY
, 0) != 0
AND SUP.ORDER_TYPE IN(1
, 2
, 3
, 4
, 5
, 7
, 8
, 11
, 12
, 14
, 15
, 16
, 17
, 27
, 28
, 29
, 30
, 41)
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND SUP.PLAN_ID = MSI.PLAN_ID
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MSI.MRP_PLANNING_CODE <> 6
AND MTP_ORG.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MTP_ORG.SR_TP_ID = SUP.ORGANIZATION_ID
AND MTP_ORG.PARTNER_TYPE = 3
AND SUP.SR_INSTANCE_ID = MFP.SR_INSTANCE_ID(+)
AND SUP.PLAN_ID = MFP.PLAN_ID(+)
AND SUP.TRANSACTION_ID = MFP.TRANSACTION_ID(+)
AND DEM.SR_INSTANCE_ID(+) = MFP.SR_INSTANCE_ID
AND DEM.PLAN_ID(+) = MFP.PLAN_ID
AND DEM.DEMAND_ID(+) = MFP.DEMAND_ID
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