VIEW: APPS.MSD_DEM_SAFETY_STOCKS_V

Source

View Text - Preformatted

SELECT 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 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 msd_dem_common_utilities.is_last_date_in_bucket(cal.sr_instance_id, cal.calendar_code, cal.calendar_date) = 1 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

View Text - HTML Formatted

SELECT 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 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 MSD_DEM_COMMON_UTILITIES.IS_LAST_DATE_IN_BUCKET(CAL.SR_INSTANCE_ID
, CAL.CALENDAR_CODE
, CAL.CALENDAR_DATE) = 1
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