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_SR_PROMOTIONAL_DATA_V

Source

View Text - Preformatted


SELECT nvl(l.ship_from_org_id, msd_sr_util.get_null_pk), null, nvl(l.inventory_item_id, msd_sr_util.get_null_pk), null, decode( decode( nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1', hca.attribute1, 'attribute2', hca.attribute2, 'attribute3', hca.attribute3, 'attribute4', hca.attribute4, 'attribute5', hca.attribute5, 'attribute6', hca.attribute6, 'attribute7', hca.attribute7, 'attribute8', hca.attribute8, 'attribute9', hca.attribute9, 'attribute10', hca.attribute10, 'attribute11', hca.attribute11, 'attribute12', hca.attribute12, 'attribute13', hca.attribute13, 'attribute14', hca.attribute14, 'attribute15', hca.attribute15, '2'), '1', nvl(hca.cust_account_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), null, nvl(sc.lookup_code, msd_sr_util.get_null_pk), null, nvl(srep.salesrep_id, msd_sr_util.get_null_pk), null, decode( decode( nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1', hca.attribute1, 'attribute2', hca.attribute2, 'attribute3', hca.attribute3, 'attribute4', hca.attribute4, 'attribute5', hca.attribute5, 'attribute6', hca.attribute6, 'attribute7', hca.attribute7, 'attribute8', hca.attribute8, 'attribute9', hca.attribute9, 'attribute10', hca.attribute10, 'attribute11', hca.attribute11, 'attribute12', hca.attribute12, 'attribute13', hca.attribute13, 'attribute14', hca.attribute14, 'attribute15', hca.attribute15, '2'), '1', nvl(hcs.site_use_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), null, nvl(l.demand_class_code, msd_sr_util.get_null_pk), nvl(trunc(h.booked_date, 'dd'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.request_date, 'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.schedule_ship_date, 'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.schedule_arrival_date, 'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.actual_shipment_date, 'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), msd_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id) * l.ordered_quantity * nvl(l.unit_selling_price, nvl(l.unit_list_price,0)) * msd_sr_util.convert_global_amt( h.transactional_curr_code, h.booked_date), msd_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id) * l.ordered_quantity, msd_sr_util.get_null_pk, null, msd_sr_util.get_null_pk, null, nvl(l.original_inventory_item_id, nvl(l.inventory_item_id, msd_sr_util.get_null_pk)), '1' FROM msd_app_instance_orgs morg, msd_setup_parameters morg2, so_lookups sc, ra_salesreps_all srep, HZ_CUST_ACCOUNTS hca, HZ_CUST_SITE_USES_ALL hcs, oe_order_lines_all l, mtl_system_items itm, oe_order_headers_all h, msd_setup_parameters msp, (select parameter_value from msd_setup_parameters WHERE parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust WHERE l.header_id = h.header_id and msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and morg2.parameter_name = 'MSD_MASTER_ORG' and exists (select 1 from oe_price_adjustments p where l.header_id = p.header_id and l.line_id = nvl(p.line_id, l.line_id) and p.list_line_type_code in ('DIS', 'PRG', 'PBH') and p.applied_flag ='Y' ) and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(itm.ato_forecast_control,3), 3, decode( l.item_type_code, 'MODEL', msd_sr_util.is_product_family_forecastable (morg2.parameter_value, l.inventory_item_id, 2), 'STANDARD', msd_sr_util.is_product_family_forecastable (morg2.parameter_value, l.inventory_item_id , 2), 'KIT', msd_sr_util.is_product_family_forecastable (morg2.parameter_value, l.inventory_item_id, 2), 2), 1), decode( nvl(itm.ato_forecast_control,3), 3, 2, 1) ) = 1 and decode( l.item_type_code, 'CONFIG', decode( l.ato_line_id, l.link_to_line_id, 1, 2), 'MODEL', decode(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (l.header_id, l.org_id, l.ato_line_id), 1, 2, 1), 'STANDARD', decode(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (l.header_id, l.org_id, l.ato_line_id), 1, 2, 1), 'KIT', 1, 2 ) = 1 and nvl(h.order_source_id, 0) <> 10 and h.booked_flag = 'Y' and itm.organization_id = l.ship_from_org_id and itm.inventory_item_id = l.inventory_item_id and ( itm.mrp_planning_code <> 6 or (itm.mrp_planning_code = 6 and itm.pick_components_flag = 'Y')) and h.sales_channel_code = sc.lookup_code (+) and sc.lookup_type (+) = 'SALES_CHANNEL' and h.salesrep_id = srep.salesrep_id (+) and h.org_id = srep.org_id (+) and hca.cust_account_id = h.sold_to_org_id and hcs.site_use_id (+) = l.ship_to_org_id and hcs.site_use_code (+) = 'SHIP_TO' and morg.organization_id = l.ship_from_org_id and l.line_category_code <> 'RETURN' UNION ALL SELECT nvl(l.ship_from_org_id, msd_sr_util.get_null_pk), null, nvl(l.inventory_item_id, msd_sr_util.get_null_pk), null, decode( decode( nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1', hca.attribute1, 'attribute2', hca.attribute2, 'attribute3', hca.attribute3, 'attribute4', hca.attribute4, 'attribute5', hca.attribute5, 'attribute6', hca.attribute6, 'attribute7', hca.attribute7, 'attribute8', hca.attribute8, 'attribute9', hca.attribute9, 'attribute10', hca.attribute10, 'attribute11', hca.attribute11, 'attribute12', hca.attribute12, 'attribute13', hca.attribute13, 'attribute14', hca.attribute14, 'attribute15', hca.attribute15, '2'), '1', nvl(hca.cust_account_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), null, nvl(sc.lookup_code, msd_sr_util.get_null_pk), null, nvl(srep.salesrep_id, msd_sr_util.get_null_pk), null, decode( decode( nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1', hca.attribute1, 'attribute2', hca.attribute2, 'attribute3', hca.attribute3, 'attribute4', hca.attribute4, 'attribute5', hca.attribute5, 'attribute6', hca.attribute6, 'attribute7', hca.attribute7, 'attribute8', hca.attribute8, 'attribute9', hca.attribute9, 'attribute10',hca.attribute10, 'attribute11', hca.attribute11, 'attribute12', hca.attribute12, 'attribute13', hca.attribute13, 'attribute14', hca.attribute14, 'attribute15', hca.attribute15, '2'), '1', nvl(hcs.site_use_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), null, nvl(l.demand_class_code, msd_sr_util.get_null_pk), nvl(trunc(h.booked_date,'dd'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.request_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.schedule_ship_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.schedule_arrival_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.actual_shipment_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), msd_sr_util.uom_conv(l.shipping_quantity_uom, l.inventory_item_id) * nvl(l.shipping_quantity, l.fulfilled_quantity) * nvl(l.unit_selling_price, nvl(l.unit_list_price, 0)) * msd_sr_util.convert_global_amt (h.transactional_curr_code, h.booked_date), msd_sr_util.uom_conv(l.shipping_quantity_uom, l.inventory_item_id) * nvl(l.shipping_quantity,l.fulfilled_quantity), msd_sr_util.get_null_pk, null, msd_sr_util.get_null_pk, null, nvl( l.original_inventory_item_id, nvl( l.inventory_item_id, msd_sr_util.get_null_pk)), '2' FROM msd_app_instance_orgs morg, msd_setup_parameters morg2, so_lookups sc, ra_salesreps_all srep, HZ_CUST_ACCOUNTS hca, HZ_CUST_SITE_USES_ALL hcs, oe_order_lines_all l, mtl_system_items itm, oe_order_headers_all h, msd_setup_parameters msp, (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust WHERE l.header_id = h.header_id and msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and morg2.parameter_name = 'MSD_MASTER_ORG' and exists (select 1 from oe_price_adjustments p where l.header_id = p.header_id and l.line_id = nvl(p.line_id, l.line_id) and p.list_line_type_code in ('DIS', 'PRG', 'PBH') and p.applied_flag = 'Y' ) and decode( nvl( msp.parameter_value, '2'), '1', decode( nvl( itm.ato_forecast_control,3), 3, decode( l.item_type_code, 'MODEL', msd_sr_util.is_product_family_forecastable (morg2.parameter_value, l.inventory_item_id, 2), 'STANDARD', msd_sr_util.is_product_family_forecastable (morg2.parameter_value, l.inventory_item_id, 2), 'KIT', msd_sr_util.is_product_family_forecastable (morg2.parameter_value, l.inventory_item_id, 2), 2), 1), decode( nvl(itm.ato_forecast_control,3), 3, 2, 1) ) = 1 and decode( l.item_type_code, 'CONFIG', decode( l.ato_line_id, l.link_to_line_id, 1, 2), 'MODEL', decode(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (l.header_id, l.org_id, l.ato_line_id), 1, 2, 1), 'STANDARD', decode(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (l.header_id, l.org_id, l.ato_line_id), 1, 2, 1), 'KIT', 1, 2 ) = 1 and nvl(h.order_source_id, 0) <> 10 and nvl(l.shipping_quantity, l.fulfilled_quantity) is not NULL and itm.organization_id = l.ship_from_org_id and itm.inventory_item_id = l.inventory_item_id and (itm.mrp_planning_code<> 6 or (itm.mrp_planning_code = 6 and itm.pick_components_flag = 'Y')) and h.sales_channel_code = sc.lookup_code (+) and sc.lookup_type (+) = 'SALES_CHANNEL' and h.salesrep_id = srep.salesrep_id (+) and h.org_id = srep.org_id (+) and hca.cust_account_id = h.sold_to_org_id and hcs.site_use_id (+) = l.ship_to_org_id and hcs.site_use_code (+) = 'SHIP_TO' and morg.organization_id = l.ship_from_org_id and l.line_category_code <> 'RETURN' UNION ALL SELECT nvl(l.ship_from_org_id, msd_sr_util.get_null_pk), null, nvl(l.inventory_item_id, msd_sr_util.get_null_pk), null, decode( decode(nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1', hca.attribute1, 'attribute2', hca.attribute2, 'attribute3', hca.attribute3, 'attribute4', hca.attribute4, 'attribute5', hca.attribute5, 'attribute6', hca.attribute6, 'attribute7', hca.attribute7, 'attribute8', hca.attribute8, 'attribute9', hca.attribute9, 'attribute10', hca.attribute10, 'attribute11', hca.attribute11, 'attribute12', hca.attribute12, 'attribute13', hca.attribute13, 'attribute14', hca.attribute14, 'attribute15', hca.attribute15, '2'), '1', nvl(hca.cust_account_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), null, nvl(sc.lookup_code, msd_sr_util.get_null_pk), null, nvl(srep.salesrep_id, msd_sr_util.get_null_pk), null, decode( decode( nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1', hca.attribute1, 'attribute2', hca.attribute2, 'attribute3', hca.attribute3, 'attribute4', hca.attribute4, 'attribute5', hca.attribute5, 'attribute6', hca.attribute6, 'attribute7', hca.attribute7, 'attribute8', hca.attribute8, 'attribute9', hca.attribute9, 'attribute10', hca.attribute10, 'attribute11', hca.attribute11, 'attribute12', hca.attribute12, 'attribute13', hca.attribute13, 'attribute14', hca.attribute14, 'attribute15', hca.attribute15, '2'), '1', nvl(hcs.site_use_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk), null, nvl(l.demand_class_code, msd_sr_util.get_null_pk), nvl(trunc(h.booked_date,'dd'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.request_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.schedule_ship_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.schedule_arrival_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), nvl(trunc(l.actual_shipment_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')), msd_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id) * l.ordered_quantity * nvl(l.unit_selling_price, nvl(l.unit_list_price,0)) * msd_sr_util.convert_global_amt (h.transactional_curr_code, h.booked_date), msd_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id) * l.ordered_quantity, msd_sr_util.get_null_pk, null, msd_sr_util.get_null_pk, null, nvl(l.original_inventory_item_id, nvl(l.inventory_item_id, msd_sr_util.get_null_pk)), '3' FROM msd_app_instance_orgs morg, msd_setup_parameters morg2, so_lookups sc, ra_salesreps_all srep, HZ_CUST_ACCOUNTS hca, HZ_CUST_SITE_USES_ALL hcs, oe_order_lines_all l, mtl_system_items itm, oe_order_headers_all h, msd_setup_parameters msp, (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust WHERE l.header_id = h.header_id and msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and morg2.parameter_name = 'MSD_MASTER_ORG' and exists (select 1 from oe_price_adjustments p where l.header_id = p.header_id and l.line_id = nvl(p.line_id, l.line_id) and p.list_line_type_code in ('DIS', 'PRG', 'PBH') and p.applied_flag = 'Y' ) and decode( nvl(msp.parameter_value, '2'), '1', decode( nvl( itm.ato_forecast_control,3), 3, decode( l.item_type_code, 'MODEL', msd_sr_util.is_product_family_forecastable (morg2.parameter_value, l.inventory_item_id, 2), 'STANDARD', msd_sr_util.is_product_family_forecastable (morg2.parameter_value, l.inventory_item_id, 2), 'KIT', msd_sr_util.is_product_family_forecastable (morg2.parameter_value, l.inventory_item_id, 2), 2), 1), decode( nvl(itm.ato_forecast_control,3), 3, 2, 1) ) = 1 and decode( l.item_type_code, 'CONFIG', decode(l.ato_line_id, l.link_to_line_id, 1, 2), 'MODEL', decode(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (l.header_id, l.org_id, l.ato_line_id), 1, 2, 1), 'STANDARD', decode(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (l.header_id, l.org_id, l.ato_line_id), 1, 2, 1), 'KIT', 1, 2 ) = 1 and nvl(h.order_source_id, 0) <> 10 and l.SCHEDULE_STATUS_CODE = 'SCHEDULED' and l.cancelled_flag = 'N' and itm.organization_id = l.ship_from_org_id and itm.inventory_item_id = l.inventory_item_id and ( itm.mrp_planning_code <> 6 or (itm.mrp_planning_code = 6 and itm.pick_components_flag = 'Y')) and h.sales_channel_code = sc.lookup_code (+) and sc.lookup_type (+) = 'SALES_CHANNEL' and h.salesrep_id = srep.salesrep_id (+) and h.org_id = srep.org_id (+) and hca.cust_account_id = h.sold_to_org_id and hcs.site_use_id (+) = l.ship_to_org_id and hcs.site_use_code (+) = 'SHIP_TO' and morg.organization_id = l.ship_from_org_id and l.line_category_code <> 'RETURN'

View Text - HTML Formatted

SELECT NVL(L.SHIP_FROM_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(L.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, DECODE( DECODE( NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, HCA.ATTRIBUTE1
, 'ATTRIBUTE2'
, HCA.ATTRIBUTE2
, 'ATTRIBUTE3'
, HCA.ATTRIBUTE3
, 'ATTRIBUTE4'
, HCA.ATTRIBUTE4
, 'ATTRIBUTE5'
, HCA.ATTRIBUTE5
, 'ATTRIBUTE6'
, HCA.ATTRIBUTE6
, 'ATTRIBUTE7'
, HCA.ATTRIBUTE7
, 'ATTRIBUTE8'
, HCA.ATTRIBUTE8
, 'ATTRIBUTE9'
, HCA.ATTRIBUTE9
, 'ATTRIBUTE10'
, HCA.ATTRIBUTE10
, 'ATTRIBUTE11'
, HCA.ATTRIBUTE11
, 'ATTRIBUTE12'
, HCA.ATTRIBUTE12
, 'ATTRIBUTE13'
, HCA.ATTRIBUTE13
, 'ATTRIBUTE14'
, HCA.ATTRIBUTE14
, 'ATTRIBUTE15'
, HCA.ATTRIBUTE15
, '2')
, '1'
, NVL(HCA.CUST_ACCOUNT_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(SC.LOOKUP_CODE
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(SREP.SALESREP_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, DECODE( DECODE( NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, HCA.ATTRIBUTE1
, 'ATTRIBUTE2'
, HCA.ATTRIBUTE2
, 'ATTRIBUTE3'
, HCA.ATTRIBUTE3
, 'ATTRIBUTE4'
, HCA.ATTRIBUTE4
, 'ATTRIBUTE5'
, HCA.ATTRIBUTE5
, 'ATTRIBUTE6'
, HCA.ATTRIBUTE6
, 'ATTRIBUTE7'
, HCA.ATTRIBUTE7
, 'ATTRIBUTE8'
, HCA.ATTRIBUTE8
, 'ATTRIBUTE9'
, HCA.ATTRIBUTE9
, 'ATTRIBUTE10'
, HCA.ATTRIBUTE10
, 'ATTRIBUTE11'
, HCA.ATTRIBUTE11
, 'ATTRIBUTE12'
, HCA.ATTRIBUTE12
, 'ATTRIBUTE13'
, HCA.ATTRIBUTE13
, 'ATTRIBUTE14'
, HCA.ATTRIBUTE14
, 'ATTRIBUTE15'
, HCA.ATTRIBUTE15
, '2')
, '1'
, NVL(HCS.SITE_USE_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(L.DEMAND_CLASS_CODE
, MSD_SR_UTIL.GET_NULL_PK)
, NVL(TRUNC(H.BOOKED_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.REQUEST_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.SCHEDULE_SHIP_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.SCHEDULE_ARRIVAL_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.ACTUAL_SHIPMENT_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * L.ORDERED_QUANTITY * NVL(L.UNIT_SELLING_PRICE
, NVL(L.UNIT_LIST_PRICE
, 0)) * MSD_SR_UTIL.CONVERT_GLOBAL_AMT( H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE)
, MSD_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * L.ORDERED_QUANTITY
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, NVL(L.ORIGINAL_INVENTORY_ITEM_ID
, NVL(L.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK))
, '1'
FROM MSD_APP_INSTANCE_ORGS MORG
, MSD_SETUP_PARAMETERS MORG2
, SO_LOOKUPS SC
, RA_SALESREPS_ALL SREP
, HZ_CUST_ACCOUNTS HCA
, HZ_CUST_SITE_USES_ALL HCS
, OE_ORDER_LINES_ALL L
, MTL_SYSTEM_ITEMS ITM
, OE_ORDER_HEADERS_ALL H
, MSD_SETUP_PARAMETERS MSP
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE L.HEADER_ID = H.HEADER_ID
AND MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MORG2.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND EXISTS (SELECT 1
FROM OE_PRICE_ADJUSTMENTS P
WHERE L.HEADER_ID = P.HEADER_ID
AND L.LINE_ID = NVL(P.LINE_ID
, L.LINE_ID)
AND P.LIST_LINE_TYPE_CODE IN ('DIS'
, 'PRG'
, 'PBH')
AND P.APPLIED_FLAG ='Y' )
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE( L.ITEM_TYPE_CODE
, 'MODEL'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE (MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 'STANDARD'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE (MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 'KIT'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE (MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 2)
, 1)
, DECODE( NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, 2
, 1) ) = 1
AND DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, DECODE( L.ATO_LINE_ID
, L.LINK_TO_LINE_ID
, 1
, 2)
, 'MODEL'
, DECODE(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (L.HEADER_ID
, L.ORG_ID
, L.ATO_LINE_ID)
, 1
, 2
, 1)
, 'STANDARD'
, DECODE(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (L.HEADER_ID
, L.ORG_ID
, L.ATO_LINE_ID)
, 1
, 2
, 1)
, 'KIT'
, 1
, 2 ) = 1
AND NVL(H.ORDER_SOURCE_ID
, 0) <> 10
AND H.BOOKED_FLAG = 'Y'
AND ITM.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID
AND ITM.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND ( ITM.MRP_PLANNING_CODE <> 6 OR (ITM.MRP_PLANNING_CODE = 6
AND ITM.PICK_COMPONENTS_FLAG = 'Y'))
AND H.SALES_CHANNEL_CODE = SC.LOOKUP_CODE (+)
AND SC.LOOKUP_TYPE (+) = 'SALES_CHANNEL'
AND H.SALESREP_ID = SREP.SALESREP_ID (+)
AND H.ORG_ID = SREP.ORG_ID (+)
AND HCA.CUST_ACCOUNT_ID = H.SOLD_TO_ORG_ID
AND HCS.SITE_USE_ID (+) = L.SHIP_TO_ORG_ID
AND HCS.SITE_USE_CODE (+) = 'SHIP_TO'
AND MORG.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID
AND L.LINE_CATEGORY_CODE <> 'RETURN' UNION ALL SELECT NVL(L.SHIP_FROM_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(L.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, DECODE( DECODE( NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, HCA.ATTRIBUTE1
, 'ATTRIBUTE2'
, HCA.ATTRIBUTE2
, 'ATTRIBUTE3'
, HCA.ATTRIBUTE3
, 'ATTRIBUTE4'
, HCA.ATTRIBUTE4
, 'ATTRIBUTE5'
, HCA.ATTRIBUTE5
, 'ATTRIBUTE6'
, HCA.ATTRIBUTE6
, 'ATTRIBUTE7'
, HCA.ATTRIBUTE7
, 'ATTRIBUTE8'
, HCA.ATTRIBUTE8
, 'ATTRIBUTE9'
, HCA.ATTRIBUTE9
, 'ATTRIBUTE10'
, HCA.ATTRIBUTE10
, 'ATTRIBUTE11'
, HCA.ATTRIBUTE11
, 'ATTRIBUTE12'
, HCA.ATTRIBUTE12
, 'ATTRIBUTE13'
, HCA.ATTRIBUTE13
, 'ATTRIBUTE14'
, HCA.ATTRIBUTE14
, 'ATTRIBUTE15'
, HCA.ATTRIBUTE15
, '2')
, '1'
, NVL(HCA.CUST_ACCOUNT_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(SC.LOOKUP_CODE
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(SREP.SALESREP_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, DECODE( DECODE( NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, HCA.ATTRIBUTE1
, 'ATTRIBUTE2'
, HCA.ATTRIBUTE2
, 'ATTRIBUTE3'
, HCA.ATTRIBUTE3
, 'ATTRIBUTE4'
, HCA.ATTRIBUTE4
, 'ATTRIBUTE5'
, HCA.ATTRIBUTE5
, 'ATTRIBUTE6'
, HCA.ATTRIBUTE6
, 'ATTRIBUTE7'
, HCA.ATTRIBUTE7
, 'ATTRIBUTE8'
, HCA.ATTRIBUTE8
, 'ATTRIBUTE9'
, HCA.ATTRIBUTE9
, 'ATTRIBUTE10'
, HCA.ATTRIBUTE10
, 'ATTRIBUTE11'
, HCA.ATTRIBUTE11
, 'ATTRIBUTE12'
, HCA.ATTRIBUTE12
, 'ATTRIBUTE13'
, HCA.ATTRIBUTE13
, 'ATTRIBUTE14'
, HCA.ATTRIBUTE14
, 'ATTRIBUTE15'
, HCA.ATTRIBUTE15
, '2')
, '1'
, NVL(HCS.SITE_USE_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(L.DEMAND_CLASS_CODE
, MSD_SR_UTIL.GET_NULL_PK)
, NVL(TRUNC(H.BOOKED_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.REQUEST_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.SCHEDULE_SHIP_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.SCHEDULE_ARRIVAL_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.ACTUAL_SHIPMENT_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(L.SHIPPING_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * NVL(L.SHIPPING_QUANTITY
, L.FULFILLED_QUANTITY) * NVL(L.UNIT_SELLING_PRICE
, NVL(L.UNIT_LIST_PRICE
, 0)) * MSD_SR_UTIL.CONVERT_GLOBAL_AMT (H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE)
, MSD_SR_UTIL.UOM_CONV(L.SHIPPING_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * NVL(L.SHIPPING_QUANTITY
, L.FULFILLED_QUANTITY)
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, NVL( L.ORIGINAL_INVENTORY_ITEM_ID
, NVL( L.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK))
, '2'
FROM MSD_APP_INSTANCE_ORGS MORG
, MSD_SETUP_PARAMETERS MORG2
, SO_LOOKUPS SC
, RA_SALESREPS_ALL SREP
, HZ_CUST_ACCOUNTS HCA
, HZ_CUST_SITE_USES_ALL HCS
, OE_ORDER_LINES_ALL L
, MTL_SYSTEM_ITEMS ITM
, OE_ORDER_HEADERS_ALL H
, MSD_SETUP_PARAMETERS MSP
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE L.HEADER_ID = H.HEADER_ID
AND MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MORG2.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND EXISTS (SELECT 1
FROM OE_PRICE_ADJUSTMENTS P
WHERE L.HEADER_ID = P.HEADER_ID
AND L.LINE_ID = NVL(P.LINE_ID
, L.LINE_ID)
AND P.LIST_LINE_TYPE_CODE IN ('DIS'
, 'PRG'
, 'PBH')
AND P.APPLIED_FLAG = 'Y' )
AND DECODE( NVL( MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE( NVL( ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE( L.ITEM_TYPE_CODE
, 'MODEL'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE (MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 'STANDARD'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE (MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 'KIT'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE (MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 2)
, 1)
, DECODE( NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, 2
, 1) ) = 1
AND DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, DECODE( L.ATO_LINE_ID
, L.LINK_TO_LINE_ID
, 1
, 2)
, 'MODEL'
, DECODE(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (L.HEADER_ID
, L.ORG_ID
, L.ATO_LINE_ID)
, 1
, 2
, 1)
, 'STANDARD'
, DECODE(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (L.HEADER_ID
, L.ORG_ID
, L.ATO_LINE_ID)
, 1
, 2
, 1)
, 'KIT'
, 1
, 2 ) = 1
AND NVL(H.ORDER_SOURCE_ID
, 0) <> 10
AND NVL(L.SHIPPING_QUANTITY
, L.FULFILLED_QUANTITY) IS NOT NULL
AND ITM.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID
AND ITM.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND (ITM.MRP_PLANNING_CODE<> 6 OR (ITM.MRP_PLANNING_CODE = 6
AND ITM.PICK_COMPONENTS_FLAG = 'Y'))
AND H.SALES_CHANNEL_CODE = SC.LOOKUP_CODE (+)
AND SC.LOOKUP_TYPE (+) = 'SALES_CHANNEL'
AND H.SALESREP_ID = SREP.SALESREP_ID (+)
AND H.ORG_ID = SREP.ORG_ID (+)
AND HCA.CUST_ACCOUNT_ID = H.SOLD_TO_ORG_ID
AND HCS.SITE_USE_ID (+) = L.SHIP_TO_ORG_ID
AND HCS.SITE_USE_CODE (+) = 'SHIP_TO'
AND MORG.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID
AND L.LINE_CATEGORY_CODE <> 'RETURN' UNION ALL SELECT NVL(L.SHIP_FROM_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(L.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, DECODE( DECODE(NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, HCA.ATTRIBUTE1
, 'ATTRIBUTE2'
, HCA.ATTRIBUTE2
, 'ATTRIBUTE3'
, HCA.ATTRIBUTE3
, 'ATTRIBUTE4'
, HCA.ATTRIBUTE4
, 'ATTRIBUTE5'
, HCA.ATTRIBUTE5
, 'ATTRIBUTE6'
, HCA.ATTRIBUTE6
, 'ATTRIBUTE7'
, HCA.ATTRIBUTE7
, 'ATTRIBUTE8'
, HCA.ATTRIBUTE8
, 'ATTRIBUTE9'
, HCA.ATTRIBUTE9
, 'ATTRIBUTE10'
, HCA.ATTRIBUTE10
, 'ATTRIBUTE11'
, HCA.ATTRIBUTE11
, 'ATTRIBUTE12'
, HCA.ATTRIBUTE12
, 'ATTRIBUTE13'
, HCA.ATTRIBUTE13
, 'ATTRIBUTE14'
, HCA.ATTRIBUTE14
, 'ATTRIBUTE15'
, HCA.ATTRIBUTE15
, '2')
, '1'
, NVL(HCA.CUST_ACCOUNT_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(SC.LOOKUP_CODE
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(SREP.SALESREP_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, DECODE( DECODE( NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, HCA.ATTRIBUTE1
, 'ATTRIBUTE2'
, HCA.ATTRIBUTE2
, 'ATTRIBUTE3'
, HCA.ATTRIBUTE3
, 'ATTRIBUTE4'
, HCA.ATTRIBUTE4
, 'ATTRIBUTE5'
, HCA.ATTRIBUTE5
, 'ATTRIBUTE6'
, HCA.ATTRIBUTE6
, 'ATTRIBUTE7'
, HCA.ATTRIBUTE7
, 'ATTRIBUTE8'
, HCA.ATTRIBUTE8
, 'ATTRIBUTE9'
, HCA.ATTRIBUTE9
, 'ATTRIBUTE10'
, HCA.ATTRIBUTE10
, 'ATTRIBUTE11'
, HCA.ATTRIBUTE11
, 'ATTRIBUTE12'
, HCA.ATTRIBUTE12
, 'ATTRIBUTE13'
, HCA.ATTRIBUTE13
, 'ATTRIBUTE14'
, HCA.ATTRIBUTE14
, 'ATTRIBUTE15'
, HCA.ATTRIBUTE15
, '2')
, '1'
, NVL(HCS.SITE_USE_ID
, MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(L.DEMAND_CLASS_CODE
, MSD_SR_UTIL.GET_NULL_PK)
, NVL(TRUNC(H.BOOKED_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.REQUEST_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.SCHEDULE_SHIP_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.SCHEDULE_ARRIVAL_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.ACTUAL_SHIPMENT_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * L.ORDERED_QUANTITY * NVL(L.UNIT_SELLING_PRICE
, NVL(L.UNIT_LIST_PRICE
, 0)) * MSD_SR_UTIL.CONVERT_GLOBAL_AMT (H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE)
, MSD_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * L.ORDERED_QUANTITY
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, NVL(L.ORIGINAL_INVENTORY_ITEM_ID
, NVL(L.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK))
, '3'
FROM MSD_APP_INSTANCE_ORGS MORG
, MSD_SETUP_PARAMETERS MORG2
, SO_LOOKUPS SC
, RA_SALESREPS_ALL SREP
, HZ_CUST_ACCOUNTS HCA
, HZ_CUST_SITE_USES_ALL HCS
, OE_ORDER_LINES_ALL L
, MTL_SYSTEM_ITEMS ITM
, OE_ORDER_HEADERS_ALL H
, MSD_SETUP_PARAMETERS MSP
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE L.HEADER_ID = H.HEADER_ID
AND MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MORG2.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND EXISTS (SELECT 1
FROM OE_PRICE_ADJUSTMENTS P
WHERE L.HEADER_ID = P.HEADER_ID
AND L.LINE_ID = NVL(P.LINE_ID
, L.LINE_ID)
AND P.LIST_LINE_TYPE_CODE IN ('DIS'
, 'PRG'
, 'PBH')
AND P.APPLIED_FLAG = 'Y' )
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE( NVL( ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE( L.ITEM_TYPE_CODE
, 'MODEL'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE (MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 'STANDARD'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE (MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 'KIT'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE (MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 2)
, 1)
, DECODE( NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, 2
, 1) ) = 1
AND DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, DECODE(L.ATO_LINE_ID
, L.LINK_TO_LINE_ID
, 1
, 2)
, 'MODEL'
, DECODE(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (L.HEADER_ID
, L.ORG_ID
, L.ATO_LINE_ID)
, 1
, 2
, 1)
, 'STANDARD'
, DECODE(MSD_SR_UTIL.CONFIG_ITEM_EXISTS (L.HEADER_ID
, L.ORG_ID
, L.ATO_LINE_ID)
, 1
, 2
, 1)
, 'KIT'
, 1
, 2 ) = 1
AND NVL(H.ORDER_SOURCE_ID
, 0) <> 10
AND L.SCHEDULE_STATUS_CODE = 'SCHEDULED'
AND L.CANCELLED_FLAG = 'N'
AND ITM.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID
AND ITM.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND ( ITM.MRP_PLANNING_CODE <> 6 OR (ITM.MRP_PLANNING_CODE = 6
AND ITM.PICK_COMPONENTS_FLAG = 'Y'))
AND H.SALES_CHANNEL_CODE = SC.LOOKUP_CODE (+)
AND SC.LOOKUP_TYPE (+) = 'SALES_CHANNEL'
AND H.SALESREP_ID = SREP.SALESREP_ID (+)
AND H.ORG_ID = SREP.ORG_ID (+)
AND HCA.CUST_ACCOUNT_ID = H.SOLD_TO_ORG_ID
AND HCS.SITE_USE_ID (+) = L.SHIP_TO_ORG_ID
AND HCS.SITE_USE_CODE (+) = 'SHIP_TO'
AND MORG.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID
AND L.LINE_CATEGORY_CODE <> 'RETURN'