VIEW: APPS.MSD_SR_MFG_FCST_V

Source

View Text - Preformatted

SELECT mfd.FORECAST_DESIGNATOR, nvl(mfd.ORGANIZATION_ID, msd_sr_util.get_null_pk), null, nvl(mfd.INVENTORY_ITEM_ID, msd_sr_util.get_null_pk), null, nvl(CUST_ACCT.CUST_ACCOUNT_ID , msd_sr_util.get_null_pk), null, msd_sr_util.get_null_pk, null, decode( decode(decode(lower(filtercust.parameter_value), null, '1', decode(mfde.customer_id, null, '1', lower(filtercust.parameter_value)) ), '1', '1', 'attribute1', CUST_ACCT.ATTRIBUTE1, 'attribute2', CUST_ACCT.ATTRIBUTE2, 'attribute3', CUST_ACCT.ATTRIBUTE3, 'attribute4', CUST_ACCT.ATTRIBUTE4, 'attribute5', CUST_ACCT.ATTRIBUTE5, 'attribute6', CUST_ACCT.ATTRIBUTE6, 'attribute7', CUST_ACCT.ATTRIBUTE7, 'attribute8', CUST_ACCT.ATTRIBUTE8, 'attribute9', CUST_ACCT.ATTRIBUTE9, 'attribute10', CUST_ACCT.ATTRIBUTE10, 'attribute11', CUST_ACCT.ATTRIBUTE11, 'attribute12', CUST_ACCT.ATTRIBUTE12, 'attribute13', CUST_ACCT.ATTRIBUTE13, 'attribute14', CUST_ACCT.ATTRIBUTE14, 'attribute15', CUST_ACCT.ATTRIBUTE15, '2'), '1', nvl(HCS.SITE_USE_ID, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), null, mfd.bucket_type, mfd.forecast_date, mfd.rate_end_date, mfd.original_forecast_quantity, mfd.current_forecast_quantity, msd_sr_util.get_null_pk, null, msd_sr_util.get_null_pk, null, nvl(mfde.demand_class, msd_sr_util.get_null_pk) , decode(itm.bom_item_type, 5, 3, 1) FROM msd_app_instance_orgs morg, mtl_system_items itm, mrp_forecast_designators mfde, mrp_forecast_dates mfd, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_CUST_SITE_USES_ALL HCS, msd_setup_parameters msp, msd_setup_parameters msp2, msd_setup_parameters morg2, (select parameter_value from msd_setup_parameters WHERE parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust WHERE mfde.forecast_designator = mfd.forecast_designator and morg2.parameter_name = 'MSD_MASTER_ORG' and msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(itm.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', decode(itm.bom_item_type, 2, 1, msd_sr_util.is_product_family_forecastable(morg2.parameter_value, itm.inventory_item_id, 1) ), msd_sr_util.is_product_family_forecastable(morg2.parameter_value, itm.inventory_item_id, 2) ), 1), decode( nvl(itm.ato_forecast_control, 3), 3, decode(nvl(msp2.parameter_value,'1'), '4', decode(itm.bom_item_type, 2, 1, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(itm.inventory_item_id ) ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( itm.bom_item_type, 2, 2, 1), 1) = 1 and mfde.organization_id = mfd.organization_id and mfd.organization_id = morg.organization_id and mfd.organization_id = itm.organization_id and mfd.inventory_item_id = itm.inventory_item_id and HCS.SITE_USE_ID (+)= mfde.ship_id and HCS. SITE_USE_CODE (+) = 'SHIP_TO' and ( itm.mrp_planning_code <> 6 or (itm.mrp_planning_code = 6 and itm.pick_components_flag = 'Y')) and CUST_ACCT.CUST_ACCOUNT_ID (+)= mfde.customer_id and mfd.origination_type <> 10

View Text - HTML Formatted

SELECT MFD.FORECAST_DESIGNATOR
, NVL(MFD.ORGANIZATION_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(MFD.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(CUST_ACCT.CUST_ACCOUNT_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, DECODE( DECODE(DECODE(LOWER(FILTERCUST.PARAMETER_VALUE)
, NULL
, '1'
, DECODE(MFDE.CUSTOMER_ID
, NULL
, '1'
, LOWER(FILTERCUST.PARAMETER_VALUE)) )
, '1'
, '1'
, 'ATTRIBUTE1'
, CUST_ACCT.ATTRIBUTE1
, 'ATTRIBUTE2'
, CUST_ACCT.ATTRIBUTE2
, 'ATTRIBUTE3'
, CUST_ACCT.ATTRIBUTE3
, 'ATTRIBUTE4'
, CUST_ACCT.ATTRIBUTE4
, 'ATTRIBUTE5'
, CUST_ACCT.ATTRIBUTE5
, 'ATTRIBUTE6'
, CUST_ACCT.ATTRIBUTE6
, 'ATTRIBUTE7'
, CUST_ACCT.ATTRIBUTE7
, 'ATTRIBUTE8'
, CUST_ACCT.ATTRIBUTE8
, 'ATTRIBUTE9'
, CUST_ACCT.ATTRIBUTE9
, 'ATTRIBUTE10'
, CUST_ACCT.ATTRIBUTE10
, 'ATTRIBUTE11'
, CUST_ACCT.ATTRIBUTE11
, 'ATTRIBUTE12'
, CUST_ACCT.ATTRIBUTE12
, 'ATTRIBUTE13'
, CUST_ACCT.ATTRIBUTE13
, 'ATTRIBUTE14'
, CUST_ACCT.ATTRIBUTE14
, 'ATTRIBUTE15'
, CUST_ACCT.ATTRIBUTE15
, '2')
, '1'
, NVL(HCS.SITE_USE_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, MFD.BUCKET_TYPE
, MFD.FORECAST_DATE
, MFD.RATE_END_DATE
, MFD.ORIGINAL_FORECAST_QUANTITY
, MFD.CURRENT_FORECAST_QUANTITY
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, NVL(MFDE.DEMAND_CLASS
, MSD_SR_UTIL.GET_NULL_PK)
, DECODE(ITM.BOM_ITEM_TYPE
, 5
, 3
, 1)
FROM MSD_APP_INSTANCE_ORGS MORG
, MTL_SYSTEM_ITEMS ITM
, MRP_FORECAST_DESIGNATORS MFDE
, MRP_FORECAST_DATES MFD
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_SITE_USES_ALL HCS
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MSP2
, MSD_SETUP_PARAMETERS MORG2
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE MFDE.FORECAST_DESIGNATOR = MFD.FORECAST_DESIGNATOR
AND MORG2.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, DECODE(ITM.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG2.PARAMETER_VALUE
, ITM.INVENTORY_ITEM_ID
, 1) )
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG2.PARAMETER_VALUE
, ITM.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, DECODE(ITM.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(ITM.INVENTORY_ITEM_ID ) )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( ITM.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND MFDE.ORGANIZATION_ID = MFD.ORGANIZATION_ID
AND MFD.ORGANIZATION_ID = MORG.ORGANIZATION_ID
AND MFD.ORGANIZATION_ID = ITM.ORGANIZATION_ID
AND MFD.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND HCS.SITE_USE_ID (+)= MFDE.SHIP_ID
AND HCS. SITE_USE_CODE (+) = 'SHIP_TO'
AND ( ITM.MRP_PLANNING_CODE <> 6 OR (ITM.MRP_PLANNING_CODE = 6
AND ITM.PICK_COMPONENTS_FLAG = 'Y'))
AND CUST_ACCT.CUST_ACCOUNT_ID (+)= MFDE.CUSTOMER_ID
AND MFD.ORIGINATION_TYPE <> 10