VIEW: APPS.MSD_DEM_SAFETY_STOCKS_V

Source

View Text - Preformatted

SELECT /*+ ORDERED */ plans.plan_id plan_id, plans.compile_designator supply_plan, mtp.organization_code org_code, msi.item_name item_code, saf.safety_stock_quantity quantity, cal.calendar_date sdate FROM msc_safety_stocks saf, msc_plans plans, msc_trading_partners mtp, msc_calendar_dates cal, msc_system_items msi, (SELECT NVL(fnd_profile.VALUE('MSD_DEM_TWO_LEVEL_PLANNING' ), '2') profile_value FROM dual ) tlp WHERE upper(msd_dem_common_utilities.dm_time_level) = 'DAY' AND saf.plan_id = plans.plan_id AND mtp.sr_tp_id = saf.organization_id AND mtp.sr_instance_id = saf.sr_instance_id AND mtp.partner_type = 3 AND cal.sr_instance_id = saf.sr_instance_id AND cal.calendar_code = mtp.calendar_code AND cal.exception_set_id = -1 AND cal.calendar_date BETWEEN saf.period_start_date AND msd_dem_common_utilities.get_safety_stock_enddate(saf.plan_id, saf.sr_instance_id, saf.organization_id, saf.inventory_item_id, saf.period_start_date) AND saf.organization_id = msi.organization_id AND saf.inventory_item_id = msi.inventory_item_id AND saf.plan_id = msi.plan_id AND saf.sr_instance_id = msi.sr_instance_id AND msi.mrp_planning_code <> 6 AND saf.plan_id <> -1 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 UNION ALL SELECT /* + NO_MERGE(iv) */ iv.plan_id, iv.supply_plan, iv.org_code, iv.item_code, iv.quantity, md.bucket_date sdate FROM (SELECT /*+ ORDERED INDEX(msi MSC_SYSTEM_ITEMS_U1)*/ plans.plan_id plan_id, plans.compile_designator supply_plan , mtp.organization_code org_code, msi.item_name item_code, saf.safety_stock_quantity quantity, TRUNC(saf.period_start_date) period_start_date, TRUNC(msd_dem_common_utilities.get_safety_stock_enddate(saf.plan_id, saf.sr_instance_id, saf.organization_id, saf.inventory_item_id, saf.period_start_date)) period_end_date, saf.sr_instance_id sr_instance_id, mtp.calendar_code calendar_code FROM msc_plans plans, msc_safety_stocks saf, msc_trading_partners mtp, msc_system_items msi, (SELECT NVL(fnd_profile.VALUE('MSD_DEM_TWO_LEVEL_PLANNING'), '2') profile_value FROM dual ) tlp WHERE upper(msd_dem_common_utilities.dm_time_level) <> 'DAY' AND saf.plan_id = plans.plan_id AND mtp.sr_tp_id = saf.organization_id AND mtp.sr_instance_id = saf.sr_instance_id and mtp.partner_type = 3 AND msi.organization_id = saf.organization_id AND msi.inventory_item_id = saf.inventory_item_id AND msi.plan_id = saf.plan_id AND msi.sr_instance_id = saf.sr_instance_id AND msi.mrp_planning_code <> 6 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 ) iv, msd_dem_day_dates md WHERE md.day_date BETWEEN iv.period_start_date AND iv.period_end_date AND md.day_date = TRUNC(md.bucket_end_date)

View Text - HTML Formatted

SELECT /*+ ORDERED */ PLANS.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, MTP.ORGANIZATION_CODE ORG_CODE
, MSI.ITEM_NAME ITEM_CODE
, SAF.SAFETY_STOCK_QUANTITY QUANTITY
, CAL.CALENDAR_DATE SDATE
FROM MSC_SAFETY_STOCKS SAF
, MSC_PLANS PLANS
, MSC_TRADING_PARTNERS MTP
, MSC_CALENDAR_DATES CAL
, MSC_SYSTEM_ITEMS MSI
, (SELECT NVL(FND_PROFILE.VALUE('MSD_DEM_TWO_LEVEL_PLANNING' )
, '2') PROFILE_VALUE
FROM DUAL ) TLP
WHERE UPPER(MSD_DEM_COMMON_UTILITIES.DM_TIME_LEVEL) = 'DAY'
AND SAF.PLAN_ID = PLANS.PLAN_ID
AND MTP.SR_TP_ID = SAF.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = SAF.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND CAL.SR_INSTANCE_ID = SAF.SR_INSTANCE_ID
AND CAL.CALENDAR_CODE = MTP.CALENDAR_CODE
AND CAL.EXCEPTION_SET_ID = -1
AND CAL.CALENDAR_DATE BETWEEN SAF.PERIOD_START_DATE
AND MSD_DEM_COMMON_UTILITIES.GET_SAFETY_STOCK_ENDDATE(SAF.PLAN_ID
, SAF.SR_INSTANCE_ID
, SAF.ORGANIZATION_ID
, SAF.INVENTORY_ITEM_ID
, SAF.PERIOD_START_DATE)
AND SAF.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SAF.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND SAF.PLAN_ID = MSI.PLAN_ID
AND SAF.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MSI.MRP_PLANNING_CODE <> 6
AND SAF.PLAN_ID <> -1
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 UNION ALL SELECT /* + NO_MERGE(IV) */ IV.PLAN_ID
, IV.SUPPLY_PLAN
, IV.ORG_CODE
, IV.ITEM_CODE
, IV.QUANTITY
, MD.BUCKET_DATE SDATE
FROM (SELECT /*+ ORDERED INDEX(MSI MSC_SYSTEM_ITEMS_U1)*/ PLANS.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, MTP.ORGANIZATION_CODE ORG_CODE
, MSI.ITEM_NAME ITEM_CODE
, SAF.SAFETY_STOCK_QUANTITY QUANTITY
, TRUNC(SAF.PERIOD_START_DATE) PERIOD_START_DATE
, TRUNC(MSD_DEM_COMMON_UTILITIES.GET_SAFETY_STOCK_ENDDATE(SAF.PLAN_ID
, SAF.SR_INSTANCE_ID
, SAF.ORGANIZATION_ID
, SAF.INVENTORY_ITEM_ID
, SAF.PERIOD_START_DATE)) PERIOD_END_DATE
, SAF.SR_INSTANCE_ID SR_INSTANCE_ID
, MTP.CALENDAR_CODE CALENDAR_CODE
FROM MSC_PLANS PLANS
, MSC_SAFETY_STOCKS SAF
, MSC_TRADING_PARTNERS MTP
, MSC_SYSTEM_ITEMS MSI
, (SELECT NVL(FND_PROFILE.VALUE('MSD_DEM_TWO_LEVEL_PLANNING')
, '2') PROFILE_VALUE
FROM DUAL ) TLP
WHERE UPPER(MSD_DEM_COMMON_UTILITIES.DM_TIME_LEVEL) <> 'DAY'
AND SAF.PLAN_ID = PLANS.PLAN_ID
AND MTP.SR_TP_ID = SAF.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = SAF.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND MSI.ORGANIZATION_ID = SAF.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = SAF.INVENTORY_ITEM_ID
AND MSI.PLAN_ID = SAF.PLAN_ID
AND MSI.SR_INSTANCE_ID = SAF.SR_INSTANCE_ID
AND MSI.MRP_PLANNING_CODE <> 6
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 ) IV
, MSD_DEM_DAY_DATES MD
WHERE MD.DAY_DATE BETWEEN IV.PERIOD_START_DATE
AND IV.PERIOD_END_DATE
AND MD.DAY_DATE = TRUNC(MD.BUCKET_END_DATE)