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_SHIPMENT_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',    cust.attribute1, 'attribute2', cust.attribute2, 'attribute3', cust.attribute3,
    'attribute4', cust.attribute4, 'attribute5', cust.attribute5, 'attribute6',
    cust.attribute6, 'attribute7', cust.attribute7, 'attribute8', cust.attribute8,
    'attribute9', cust.attribute9, 'attribute10', cust.attribute10, 'attribute11',
    cust.attribute11, 'attribute12', cust.attribute12, 'attribute13',
    cust.attribute13, 'attribute14', cust.attribute14, 'attribute15',
    cust.attribute15, '2') , '1', nvl(cust.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,
    /* Bug# 5073531
    nvl(srep.salesrep_id, msd_sr_util.get_null_pk), */
    nvl(srep2.salesrep_id,nvl(srep1.salesrep_id,msd_sr_util.get_null_pk)),
    null,
    decode(decode(nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1',
    cust.attribute1, 'attribute2', cust.attribute2, 'attribute3', cust.attribute3,
    'attribute4', cust.attribute4, 'attribute5', cust.attribute5, 'attribute6',
    cust.attribute6, 'attribute7', cust.attribute7, 'attribute8', cust.attribute8,
    'attribute9', cust.attribute9, 'attribute10', cust.attribute10, 'attribute11',
    cust.attribute11, 'attribute12', cust.attribute12, 'attribute13',
    cust.attribute13, 'attribute14', cust.attribute14, 'attribute15',
    cust.attribute15, '2') , '1', nvl(prs.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')),
    nvl(trunc(l.request_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')),
    nvl(trunc(l.promise_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.shipped_quantity, 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.shipping_quantity_uom, l.inventory_item_id) * nvl(l.shipped_quantity,l.ordered_quantity),
    msd_sr_util.get_null_pk,
    null,
    msd_sr_util.get_null_pk,
    null,
    nvl(decode(l.item_relationship_type, 2, l.original_inventory_item_id, null, nvl(l.original_inventory_item_id, l.inventory_item_id), l.inventory_item_id), msd_sr_util.get_null_pk),
    decode(l.item_type_code,
           'CONFIG',null,
            decode(nvl(para.parameter_value,'1'),
                       '1', null,
                       '3', MSD_SR_UTIL.FIND_PARENT_ITEM(l.link_to_line_id,'N'),
                       MSD_SR_UTIL.FIND_PARENT_ITEM(l.link_to_line_id,'Y')
                       )
           ),
    nvl(l.demand_class_code, msd_sr_util.get_null_pk),
	h.order_source_id ORDER_SOURCE_ID,                    /* Bug 4615390: ISO */
	h.order_type_id ORDER_TYPE_ID                         /* Bug# 4747555     */
FROM
    msd_app_instance_orgs morg,
    msd_setup_parameters morg2,
    msd_setup_parameters para,
    msd_setup_parameters para2,
    so_lookups sc,
    /* Bug# 5073531
    ra_salesreps_all srep, */
    ra_salesreps_all srep1,
    ra_salesreps_all srep2,
    hz_cust_accounts cust,
    hz_cust_site_uses_all prs,
    oe_order_lines_all l,
    mtl_system_items itm,
    oe_order_headers_all h,
    (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust,
    /* Bug# 5073531 */
    fnd_product_groups fpg
WHERE
para.parameter_name = 'MSD_PLANNING_PERCENTAGE'
and para2.parameter_name = 'MSD_TWO_LEVEL_PLANNING'
and morg2.parameter_name = 'MSD_MASTER_ORG'
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, decode(itm.bom_item_type,
                                                 2, 1,
                                                 2),
                                       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
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'
/* Bug# 5073531
and h.SALESREP_ID = srep.salesrep_id (+) and
h.org_id = srep.org_id (+) */
AND h.salesrep_id = srep1.salesrep_id(+)
AND h.org_id = srep1.org_id(+)
AND l.salesrep_id = srep2.salesrep_id(+)
AND l.org_id = srep2.org_id(+)
and cust.cust_account_id = h.sold_to_org_id
and prs.site_use_id (+) = l.ship_to_org_id
and prs.site_use_code (+) = 'SHIP_TO'
and morg.organization_id = l.ship_from_org_id
and l.line_category_code <> 'RETURN'
/* Bug# 5073531 */
AND nvl(fpg.multi_org_flag,   'Y') = 'Y'
/* Bug# 5073531  - For Non Multi Org Instance */
UNION ALL
/*Bug 4585376 RA_CUSTOMERS and RA_SITE_USES_ALL replaced by HZ_CUST_SITE_USES_ALL and HZ_CUST_ACCOUNTS*/
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',    cust.attribute1, 'attribute2', cust.attribute2, 'attribute3', cust.attribute3,
    'attribute4', cust.attribute4, 'attribute5', cust.attribute5, 'attribute6',
    cust.attribute6, 'attribute7', cust.attribute7, 'attribute8', cust.attribute8,
    'attribute9', cust.attribute9, 'attribute10', cust.attribute10, 'attribute11',
    cust.attribute11, 'attribute12', cust.attribute12, 'attribute13',
    cust.attribute13, 'attribute14', cust.attribute14, 'attribute15',
    cust.attribute15, '2') , '1', nvl(cust.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,
    /* Bug# 5073531
    nvl(srep.salesrep_id, msd_sr_util.get_null_pk), */
    nvl(srep2.salesrep_id,   nvl(srep1.salesrep_id,   msd_sr_util.get_null_pk)),
    null,
    decode(decode(nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1',
    cust.attribute1, 'attribute2', cust.attribute2, 'attribute3', cust.attribute3,
    'attribute4', cust.attribute4, 'attribute5', cust.attribute5, 'attribute6',
    cust.attribute6, 'attribute7', cust.attribute7, 'attribute8', cust.attribute8,
    'attribute9', cust.attribute9, 'attribute10', cust.attribute10, 'attribute11',
    cust.attribute11, 'attribute12', cust.attribute12, 'attribute13',
    cust.attribute13, 'attribute14', cust.attribute14, 'attribute15',
    cust.attribute15, '2') , '1', nvl(prs.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')),
    nvl(trunc(l.request_date,'DD'), to_date('1000/01/01', 'yyyy/mm/dd')),
    nvl(trunc(l.promise_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.shipped_quantity, 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.shipping_quantity_uom, l.inventory_item_id) * nvl(l.shipped_quantity,l.ordered_quantity),
    msd_sr_util.get_null_pk,
    null,
    msd_sr_util.get_null_pk,
    null,
    nvl(decode(l.item_relationship_type, 2, l.original_inventory_item_id, null, nvl(l.original_inventory_item_id, l.inventory_item_id), l.inventory_item_id), msd_sr_util.get_null_pk),
    decode(l.item_type_code,
           'CONFIG',null,
            decode(nvl(para.parameter_value,'1'),
                       '1', null,
                       '3', MSD_SR_UTIL.FIND_PARENT_ITEM(l.link_to_line_id,'N'),
                       MSD_SR_UTIL.FIND_PARENT_ITEM(l.link_to_line_id,'Y')
                       )
           ),
    nvl(l.demand_class_code, msd_sr_util.get_null_pk),
	h.order_source_id ORDER_SOURCE_ID,                    /* Bug 4615390: ISO */
	h.order_type_id ORDER_TYPE_ID                         /* Bug# 4747555     */
FROM
    msd_app_instance_orgs morg,
    msd_setup_parameters morg2,
    msd_setup_parameters para,
    msd_setup_parameters para2,
    so_lookups sc,
    /* Bug# 5073531
    ra_salesreps_all srep, */
    ra_salesreps_all srep1,
    ra_salesreps_all srep2,
    hz_cust_accounts cust,
    hz_cust_site_uses_all prs,
    oe_order_lines_all l,
    mtl_system_items itm,
    oe_order_headers_all h,
    (select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust,
    /* Bug# 5073531 */
    fnd_product_groups fpg
WHERE
para.parameter_name = 'MSD_PLANNING_PERCENTAGE'
and para2.parameter_name = 'MSD_TWO_LEVEL_PLANNING'
and morg2.parameter_name = 'MSD_MASTER_ORG'
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, decode(itm.bom_item_type,
                                                 2, 1,
                                                 2),
                                       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
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'
/* Bug# 5073531
and h.SALESREP_ID = srep.salesrep_id (+) and
h.org_id = srep.org_id (+) */
AND h.salesrep_id = srep1.salesrep_id(+)
AND l.salesrep_id = srep2.salesrep_id(+)
and cust.cust_account_id = h.sold_to_org_id
and prs.site_use_id (+) = l.ship_to_org_id
and prs.site_use_code (+) = 'SHIP_TO'
and morg.organization_id = l.ship_from_org_id
and l.line_category_code <> 'RETURN'
/* Bug# 5073531 */
AND nvl(fpg.multi_org_flag,   'Y') = 'N'

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'
, CUST.ATTRIBUTE1
, 'ATTRIBUTE2'
, CUST.ATTRIBUTE2
, 'ATTRIBUTE3'
, CUST.ATTRIBUTE3
, 'ATTRIBUTE4'
, CUST.ATTRIBUTE4
, 'ATTRIBUTE5'
, CUST.ATTRIBUTE5
, 'ATTRIBUTE6'
, CUST.ATTRIBUTE6
, 'ATTRIBUTE7'
, CUST.ATTRIBUTE7
, 'ATTRIBUTE8'
, CUST.ATTRIBUTE8
, 'ATTRIBUTE9'
, CUST.ATTRIBUTE9
, 'ATTRIBUTE10'
, CUST.ATTRIBUTE10
, 'ATTRIBUTE11'
, CUST.ATTRIBUTE11
, 'ATTRIBUTE12'
, CUST.ATTRIBUTE12
, 'ATTRIBUTE13'
, CUST.ATTRIBUTE13
, 'ATTRIBUTE14'
, CUST.ATTRIBUTE14
, 'ATTRIBUTE15'
, CUST.ATTRIBUTE15
, '2')
, '1'
, NVL(CUST.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
, /* BUG# 5073531 NVL(SREP.SALESREP_ID
, MSD_SR_UTIL.GET_NULL_PK)
, */ NVL(SREP2.SALESREP_ID
, NVL(SREP1.SALESREP_ID
, MSD_SR_UTIL.GET_NULL_PK))
, NULL
, DECODE(DECODE(NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, CUST.ATTRIBUTE1
, 'ATTRIBUTE2'
, CUST.ATTRIBUTE2
, 'ATTRIBUTE3'
, CUST.ATTRIBUTE3
, 'ATTRIBUTE4'
, CUST.ATTRIBUTE4
, 'ATTRIBUTE5'
, CUST.ATTRIBUTE5
, 'ATTRIBUTE6'
, CUST.ATTRIBUTE6
, 'ATTRIBUTE7'
, CUST.ATTRIBUTE7
, 'ATTRIBUTE8'
, CUST.ATTRIBUTE8
, 'ATTRIBUTE9'
, CUST.ATTRIBUTE9
, 'ATTRIBUTE10'
, CUST.ATTRIBUTE10
, 'ATTRIBUTE11'
, CUST.ATTRIBUTE11
, 'ATTRIBUTE12'
, CUST.ATTRIBUTE12
, 'ATTRIBUTE13'
, CUST.ATTRIBUTE13
, 'ATTRIBUTE14'
, CUST.ATTRIBUTE14
, 'ATTRIBUTE15'
, CUST.ATTRIBUTE15
, '2')
, '1'
, NVL(PRS.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'))
, NVL(TRUNC(L.REQUEST_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.PROMISE_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.SHIPPED_QUANTITY
, 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.SHIPPING_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * NVL(L.SHIPPED_QUANTITY
, L.ORDERED_QUANTITY)
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, NVL(DECODE(L.ITEM_RELATIONSHIP_TYPE
, 2
, L.ORIGINAL_INVENTORY_ITEM_ID
, NULL
, NVL(L.ORIGINAL_INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID)
, L.INVENTORY_ITEM_ID)
, MSD_SR_UTIL.GET_NULL_PK)
, DECODE(L.ITEM_TYPE_CODE
, 'CONFIG'
, NULL
, DECODE(NVL(PARA.PARAMETER_VALUE
, '1')
, '1'
, NULL
, '3'
, MSD_SR_UTIL.FIND_PARENT_ITEM(L.LINK_TO_LINE_ID
, 'N')
, MSD_SR_UTIL.FIND_PARENT_ITEM(L.LINK_TO_LINE_ID
, 'Y') ) )
, NVL(L.DEMAND_CLASS_CODE
, MSD_SR_UTIL.GET_NULL_PK)
, H.ORDER_SOURCE_ID ORDER_SOURCE_ID
, /* BUG 4615390: ISO */ H.ORDER_TYPE_ID ORDER_TYPE_ID /* BUG# 4747555 */ FROM MSD_APP_INSTANCE_ORGS MORG
, MSD_SETUP_PARAMETERS MORG2
, MSD_SETUP_PARAMETERS PARA
, MSD_SETUP_PARAMETERS PARA2
, SO_LOOKUPS SC
, /* BUG# 5073531 RA_SALESREPS_ALL SREP
, */ RA_SALESREPS_ALL SREP1
, RA_SALESREPS_ALL SREP2
, HZ_CUST_ACCOUNTS CUST
, HZ_CUST_SITE_USES_ALL PRS
, OE_ORDER_LINES_ALL L
, MTL_SYSTEM_ITEMS ITM
, OE_ORDER_HEADERS_ALL H
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
, /* BUG# 5073531 */ FND_PRODUCT_GROUPS FPG WHERE PARA.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE' AND PARA2.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING' AND MORG2.PARAMETER_NAME = 'MSD_MASTER_ORG' 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
, DECODE(ITM.BOM_ITEM_TYPE
, 2
, 1
, 2)
, 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 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' /* BUG# 5073531 AND H.SALESREP_ID = SREP.SALESREP_ID (+) AND H.ORG_ID = SREP.ORG_ID (+) */ AND H.SALESREP_ID = SREP1.SALESREP_ID(+) AND H.ORG_ID = SREP1.ORG_ID(+) AND L.SALESREP_ID = SREP2.SALESREP_ID(+) AND L.ORG_ID = SREP2.ORG_ID(+) AND CUST.CUST_ACCOUNT_ID = H.SOLD_TO_ORG_ID AND PRS.SITE_USE_ID (+) = L.SHIP_TO_ORG_ID AND PRS.SITE_USE_CODE (+) = 'SHIP_TO' AND MORG.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID AND L.LINE_CATEGORY_CODE <> 'RETURN' /* BUG# 5073531 */ AND NVL(FPG.MULTI_ORG_FLAG
, 'Y') = 'Y' /* BUG# 5073531 - FOR NON MULTI ORG INSTANCE */ UNION ALL /*BUG 4585376 RA_CUSTOMERS
AND RA_SITE_USES_ALL REPLACED BY HZ_CUST_SITE_USES_ALL
AND HZ_CUST_ACCOUNTS*/ 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'
, CUST.ATTRIBUTE1
, 'ATTRIBUTE2'
, CUST.ATTRIBUTE2
, 'ATTRIBUTE3'
, CUST.ATTRIBUTE3
, 'ATTRIBUTE4'
, CUST.ATTRIBUTE4
, 'ATTRIBUTE5'
, CUST.ATTRIBUTE5
, 'ATTRIBUTE6'
, CUST.ATTRIBUTE6
, 'ATTRIBUTE7'
, CUST.ATTRIBUTE7
, 'ATTRIBUTE8'
, CUST.ATTRIBUTE8
, 'ATTRIBUTE9'
, CUST.ATTRIBUTE9
, 'ATTRIBUTE10'
, CUST.ATTRIBUTE10
, 'ATTRIBUTE11'
, CUST.ATTRIBUTE11
, 'ATTRIBUTE12'
, CUST.ATTRIBUTE12
, 'ATTRIBUTE13'
, CUST.ATTRIBUTE13
, 'ATTRIBUTE14'
, CUST.ATTRIBUTE14
, 'ATTRIBUTE15'
, CUST.ATTRIBUTE15
, '2')
, '1'
, NVL(CUST.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
, /* BUG# 5073531 NVL(SREP.SALESREP_ID
, MSD_SR_UTIL.GET_NULL_PK)
, */ NVL(SREP2.SALESREP_ID
, NVL(SREP1.SALESREP_ID
, MSD_SR_UTIL.GET_NULL_PK))
, NULL
, DECODE(DECODE(NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, CUST.ATTRIBUTE1
, 'ATTRIBUTE2'
, CUST.ATTRIBUTE2
, 'ATTRIBUTE3'
, CUST.ATTRIBUTE3
, 'ATTRIBUTE4'
, CUST.ATTRIBUTE4
, 'ATTRIBUTE5'
, CUST.ATTRIBUTE5
, 'ATTRIBUTE6'
, CUST.ATTRIBUTE6
, 'ATTRIBUTE7'
, CUST.ATTRIBUTE7
, 'ATTRIBUTE8'
, CUST.ATTRIBUTE8
, 'ATTRIBUTE9'
, CUST.ATTRIBUTE9
, 'ATTRIBUTE10'
, CUST.ATTRIBUTE10
, 'ATTRIBUTE11'
, CUST.ATTRIBUTE11
, 'ATTRIBUTE12'
, CUST.ATTRIBUTE12
, 'ATTRIBUTE13'
, CUST.ATTRIBUTE13
, 'ATTRIBUTE14'
, CUST.ATTRIBUTE14
, 'ATTRIBUTE15'
, CUST.ATTRIBUTE15
, '2')
, '1'
, NVL(PRS.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'))
, NVL(TRUNC(L.REQUEST_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.PROMISE_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.SHIPPED_QUANTITY
, 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.SHIPPING_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * NVL(L.SHIPPED_QUANTITY
, L.ORDERED_QUANTITY)
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, NVL(DECODE(L.ITEM_RELATIONSHIP_TYPE
, 2
, L.ORIGINAL_INVENTORY_ITEM_ID
, NULL
, NVL(L.ORIGINAL_INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID)
, L.INVENTORY_ITEM_ID)
, MSD_SR_UTIL.GET_NULL_PK)
, DECODE(L.ITEM_TYPE_CODE
, 'CONFIG'
, NULL
, DECODE(NVL(PARA.PARAMETER_VALUE
, '1')
, '1'
, NULL
, '3'
, MSD_SR_UTIL.FIND_PARENT_ITEM(L.LINK_TO_LINE_ID
, 'N')
, MSD_SR_UTIL.FIND_PARENT_ITEM(L.LINK_TO_LINE_ID
, 'Y') ) )
, NVL(L.DEMAND_CLASS_CODE
, MSD_SR_UTIL.GET_NULL_PK)
, H.ORDER_SOURCE_ID ORDER_SOURCE_ID
, /* BUG 4615390: ISO */ H.ORDER_TYPE_ID ORDER_TYPE_ID /* BUG# 4747555 */ FROM MSD_APP_INSTANCE_ORGS MORG
, MSD_SETUP_PARAMETERS MORG2
, MSD_SETUP_PARAMETERS PARA
, MSD_SETUP_PARAMETERS PARA2
, SO_LOOKUPS SC
, /* BUG# 5073531 RA_SALESREPS_ALL SREP
, */ RA_SALESREPS_ALL SREP1
, RA_SALESREPS_ALL SREP2
, HZ_CUST_ACCOUNTS CUST
, HZ_CUST_SITE_USES_ALL PRS
, OE_ORDER_LINES_ALL L
, MTL_SYSTEM_ITEMS ITM
, OE_ORDER_HEADERS_ALL H
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
, /* BUG# 5073531 */ FND_PRODUCT_GROUPS FPG WHERE PARA.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE' AND PARA2.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING' AND MORG2.PARAMETER_NAME = 'MSD_MASTER_ORG' 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
, DECODE(ITM.BOM_ITEM_TYPE
, 2
, 1
, 2)
, 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 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' /* BUG# 5073531 AND H.SALESREP_ID = SREP.SALESREP_ID (+) AND H.ORG_ID = SREP.ORG_ID (+) */ AND H.SALESREP_ID = SREP1.SALESREP_ID(+) AND L.SALESREP_ID = SREP2.SALESREP_ID(+) AND CUST.CUST_ACCOUNT_ID = H.SOLD_TO_ORG_ID AND PRS.SITE_USE_ID (+) = L.SHIP_TO_ORG_ID AND PRS.SITE_USE_CODE (+) = 'SHIP_TO' AND MORG.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID AND L.LINE_CATEGORY_CODE <> 'RETURN' /* BUG# 5073531 */ AND NVL(FPG.MULTI_ORG_FLAG
, 'Y') = 'N'