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.QPR_SR_ORDER_DATA_V

Source

View Text - Preformatted


SELECT nvl(l.ship_from_org_id, qpr_sr_util.get_null_pk), NULL, nvl(l.inventory_item_id, qpr_sr_util.get_null_pk), NULL, (select decode(decode(nvl(qpr_sr_util.get_customer_attribute, '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, qpr_sr_util.get_null_pk), qpr_sr_util.get_null_pk) from hz_cust_accounts cust where cust.cust_account_id = h.sold_to_org_id and rownum < 2 ) , NULL, nvl((select sc.lookup_code from so_lookups sc where sc.lookup_code = h.sales_channel_code AND sc.lookup_type = 'SALES_CHANNEL' and rownum < 2), qpr_sr_util.get_null_pk), NULL, nvl((select srep.salesrep_id from ra_salesreps_all srep where srep.salesrep_id = h.salesrep_id AND srep.org_id = h.org_id and rownum < 2) , qpr_sr_util.get_null_pk), NULL, decode((select decode(nvl(qpr_sr_util.get_customer_attribute, '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') from hz_cust_accounts cust where cust.cust_account_id = h.sold_to_org_id and rownum < 2 ), '1', nvl((select prs.site_use_id from hz_cust_site_uses_all prs where prs.site_use_id = l.ship_to_org_id AND prs.site_use_code = 'SHIP_TO' and rownum < 2), qpr_sr_util.get_null_pk), qpr_sr_util.get_null_pk ) , NULL, h.org_id, nvl(TRUNC(h.booked_date, 'dd'), to_date('1000/01/01', 'yyyy/mm/dd')), l.ordered_quantity * nvl(l.unit_selling_price, nvl(l.unit_list_price, 0)) * qpr_sr_util.convert_global_amt(h.transactional_curr_code, h.booked_date) , l.ordered_quantity * qpr_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id, NULL), nvl(l.original_list_price, l.unit_list_price) *qpr_sr_util.convert_global_amt(h.transactional_curr_code, h.booked_date) / qpr_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id, NULL), l.unit_selling_price* qpr_sr_util.convert_global_amt(h.transactional_curr_code, h.booked_date)/qpr_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id, NULL), l.unit_list_price * qpr_sr_util.convert_global_amt(h.transactional_curr_code, h.booked_date)/qpr_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id, NULL), 1, decode(nvl(l.unit_list_price, 0), 0, 0,((l.unit_list_price -l.unit_selling_price) / l.unit_list_price) *100), qpr_sr_util.get_null_pk, NULL, qpr_sr_util.get_null_pk, NULL, qpr_sr_util.get_null_pk, NULL, qpr_sr_util.get_null_pk, NULL, qpr_sr_util.get_null_pk, NULL, l.line_id, NULL, qpr_sr_util.get_null_pk, NULL, qpr_sr_util.get_base_uom(l.inventory_item_id), l.item_type_code FROM oe_order_lines_all l, oe_order_headers_all h WHERE l.header_id = h.header_id AND h.booked_flag = 'Y' AND l.booked_flag = 'Y' AND h.order_category_code <> 'RETURN' AND l.line_category_code <> 'RETURN' AND nvl(h.source_document_type_id, 0) <> 10 AND l.retrobill_request_id IS NULL AND nvl(l.order_source_id, 0) <> 27 and qpr_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id, NULL) > 0 and qpr_sr_util.convert_global_amt(h.transactional_curr_code, h.booked_date) > 0 AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and h.sold_to_org_id not in ( select cust_account_id from hz_cust_accounts where nvl(customer_type, 'C') = 'I')) or nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'Y')

View Text - HTML Formatted

SELECT NVL(L.SHIP_FROM_ORG_ID
, QPR_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(L.INVENTORY_ITEM_ID
, QPR_SR_UTIL.GET_NULL_PK)
, NULL
, (SELECT DECODE(DECODE(NVL(QPR_SR_UTIL.GET_CUSTOMER_ATTRIBUTE
, '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
, QPR_SR_UTIL.GET_NULL_PK)
, QPR_SR_UTIL.GET_NULL_PK)
FROM HZ_CUST_ACCOUNTS CUST
WHERE CUST.CUST_ACCOUNT_ID = H.SOLD_TO_ORG_ID
AND ROWNUM < 2 )
, NULL
, NVL((SELECT SC.LOOKUP_CODE
FROM SO_LOOKUPS SC
WHERE SC.LOOKUP_CODE = H.SALES_CHANNEL_CODE
AND SC.LOOKUP_TYPE = 'SALES_CHANNEL'
AND ROWNUM < 2)
, QPR_SR_UTIL.GET_NULL_PK)
, NULL
, NVL((SELECT SREP.SALESREP_ID
FROM RA_SALESREPS_ALL SREP
WHERE SREP.SALESREP_ID = H.SALESREP_ID
AND SREP.ORG_ID = H.ORG_ID
AND ROWNUM < 2)
, QPR_SR_UTIL.GET_NULL_PK)
, NULL
, DECODE((SELECT DECODE(NVL(QPR_SR_UTIL.GET_CUSTOMER_ATTRIBUTE
, '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')
FROM HZ_CUST_ACCOUNTS CUST
WHERE CUST.CUST_ACCOUNT_ID = H.SOLD_TO_ORG_ID
AND ROWNUM < 2 )
, '1'
, NVL((SELECT PRS.SITE_USE_ID
FROM HZ_CUST_SITE_USES_ALL PRS
WHERE PRS.SITE_USE_ID = L.SHIP_TO_ORG_ID
AND PRS.SITE_USE_CODE = 'SHIP_TO'
AND ROWNUM < 2)
, QPR_SR_UTIL.GET_NULL_PK)
, QPR_SR_UTIL.GET_NULL_PK )
, NULL
, H.ORG_ID
, NVL(TRUNC(H.BOOKED_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, L.ORDERED_QUANTITY * NVL(L.UNIT_SELLING_PRICE
, NVL(L.UNIT_LIST_PRICE
, 0)) * QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE)
, L.ORDERED_QUANTITY * QPR_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID
, NULL)
, NVL(L.ORIGINAL_LIST_PRICE
, L.UNIT_LIST_PRICE) *QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE) / QPR_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID
, NULL)
, L.UNIT_SELLING_PRICE* QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE)/QPR_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID
, NULL)
, L.UNIT_LIST_PRICE * QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE)/QPR_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID
, NULL)
, 1
, DECODE(NVL(L.UNIT_LIST_PRICE
, 0)
, 0
, 0
, ((L.UNIT_LIST_PRICE -L.UNIT_SELLING_PRICE) / L.UNIT_LIST_PRICE) *100)
, QPR_SR_UTIL.GET_NULL_PK
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, NULL
, L.LINE_ID
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, NULL
, QPR_SR_UTIL.GET_BASE_UOM(L.INVENTORY_ITEM_ID)
, L.ITEM_TYPE_CODE
FROM OE_ORDER_LINES_ALL L
, OE_ORDER_HEADERS_ALL H
WHERE L.HEADER_ID = H.HEADER_ID
AND H.BOOKED_FLAG = 'Y'
AND L.BOOKED_FLAG = 'Y'
AND H.ORDER_CATEGORY_CODE <> 'RETURN'
AND L.LINE_CATEGORY_CODE <> 'RETURN'
AND NVL(H.SOURCE_DOCUMENT_TYPE_ID
, 0) <> 10
AND L.RETROBILL_REQUEST_ID IS NULL
AND NVL(L.ORDER_SOURCE_ID
, 0) <> 27
AND QPR_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID
, NULL) > 0
AND QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE) > 0
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND H.SOLD_TO_ORG_ID NOT IN ( SELECT CUST_ACCOUNT_ID
FROM HZ_CUST_ACCOUNTS
WHERE NVL(CUSTOMER_TYPE
, 'C') = 'I')) OR NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'Y')