VIEW: APPS.MSD_SR_PRODUCT_RETURN_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(trunc(h.booked_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, ottl.name FROM msd_app_instance_orgs morg, msd_setup_parameters para, msd_setup_parameters para2, 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, oe_transaction_types_all otall, oe_transaction_types_tl ottl, msd_setup_parameters morg2, (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust WHERE l.line_type_id = otall.transaction_type_id and otall.transaction_type_code = 'LINE' and otall.order_category_code = 'RETURN' and otall.transaction_type_id = ottl.transaction_type_id and ottl.language = userenv('LANG') and para.parameter_name = 'MSD_PLANNING_PERCENTAGE' and morg2.parameter_name = 'MSD_MASTER_ORG' and para2.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and l.header_id = h.header_id and decode( nvl(itm.ato_forecast_control,3), 3, decode(l.item_type_code, 'OPTION', decode(nvl(para.parameter_value, '1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_FACT (l.inventory_item_id , l.component_sequence_id, l.link_to_line_id), 2), 'CLASS', decode(nvl(para.parameter_value, '1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_FACT (l.inventory_item_id, l.component_sequence_id, l.link_to_line_id), 2), 'MODEL', decode(nvl(para2.parameter_value, '2'), '1', msd_sr_util.is_product_family_forecastable(morg2.parameter_value, l.inventory_item_id, 2), 2), 'STANDARD', decode(nvl(para2.parameter_value, '2'), '1', msd_sr_util.is_product_family_forecastable(morg2.parameter_value, l.inventory_item_id, 2), 2), 2), 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, 'INCLUDED',2, 'OPTION', decode(MSD_SR_UTIL.CONFIG_ITEM_EXISTS(l.header_id, l.org_id, l.ato_line_id), 1, 2, decode(nvl(para.parameter_value, 1), 1, 2, 1)), 'CLASS', decode(MSD_SR_UTIL.CONFIG_ITEM_EXISTS(l.header_id, l.org_id, l.ato_line_id), 1, 2, decode(nvl(para.parameter_value, 1), 1, 2, 3, decode( itm.bom_item_type, 1, 1, 2), 1)), 2) )= 1 and l.header_id = h.header_id 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'

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(TRUNC(H.BOOKED_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
, OTTL.NAME
FROM MSD_APP_INSTANCE_ORGS MORG
, MSD_SETUP_PARAMETERS PARA
, MSD_SETUP_PARAMETERS PARA2
, 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
, OE_TRANSACTION_TYPES_ALL OTALL
, OE_TRANSACTION_TYPES_TL OTTL
, MSD_SETUP_PARAMETERS MORG2
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE L.LINE_TYPE_ID = OTALL.TRANSACTION_TYPE_ID
AND OTALL.TRANSACTION_TYPE_CODE = 'LINE'
AND OTALL.ORDER_CATEGORY_CODE = 'RETURN'
AND OTALL.TRANSACTION_TYPE_ID = OTTL.TRANSACTION_TYPE_ID
AND OTTL.LANGUAGE = USERENV('LANG')
AND PARA.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND MORG2.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND PARA2.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND L.HEADER_ID = H.HEADER_ID
AND DECODE( NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(L.ITEM_TYPE_CODE
, 'OPTION'
, DECODE(NVL(PARA.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_FACT (L.INVENTORY_ITEM_ID
, L.COMPONENT_SEQUENCE_ID
, L.LINK_TO_LINE_ID)
, 2)
, 'CLASS'
, DECODE(NVL(PARA.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_FACT (L.INVENTORY_ITEM_ID
, L.COMPONENT_SEQUENCE_ID
, L.LINK_TO_LINE_ID)
, 2)
, 'MODEL'
, DECODE(NVL(PARA2.PARAMETER_VALUE
, '2')
, '1'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 2)
, 'STANDARD'
, DECODE(NVL(PARA2.PARAMETER_VALUE
, '2')
, '1'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG2.PARAMETER_VALUE
, L.INVENTORY_ITEM_ID
, 2)
, 2)
, 2)
, 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
, 'INCLUDED'
, 2
, 'OPTION'
, DECODE(MSD_SR_UTIL.CONFIG_ITEM_EXISTS(L.HEADER_ID
, L.ORG_ID
, L.ATO_LINE_ID)
, 1
, 2
, DECODE(NVL(PARA.PARAMETER_VALUE
, 1)
, 1
, 2
, 1))
, 'CLASS'
, DECODE(MSD_SR_UTIL.CONFIG_ITEM_EXISTS(L.HEADER_ID
, L.ORG_ID
, L.ATO_LINE_ID)
, 1
, 2
, DECODE(NVL(PARA.PARAMETER_VALUE
, 1)
, 1
, 2
, 3
, DECODE( ITM.BOM_ITEM_TYPE
, 1
, 1
, 2)
, 1))
, 2) )= 1
AND L.HEADER_ID = H.HEADER_ID
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'