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_OFFADJ_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')), nvl(TRUNC(h.booked_date, 'dd'), to_date('1000/01/01', 'yyyy/mm/dd')), NULL, -1 * qpr_sr_util.convert_global_amt(h.transactional_curr_code, h.booked_date) * nvl( (SELECT CASE WHEN((nvl(SUM(decode(pa2.arithmetic_operator, 'LUMPSUM', pa2.operand, pa2.adjusted_amount *l.ordered_quantity)), 0) + nvl(SUM(l.ordered_quantity * decode(nvl(pa2.line_id, -1), -1, decode(pa2.arithmetic_operator, NULL, 0, '%', pa2.operand * l.unit_list_price / 100, 'AMT', pa2.operand, 'NEWPRICE',(l.unit_list_price -pa2.operand)), 0)), 0)) < nvl(SUM(pa1.adjusted_amount) / decode(COUNT(pa2.line_id), 0, 1, COUNT(pa2.line_id)), 0)) THEN (nvl(SUM(pa1.adjusted_amount) / decode(COUNT(pa2.line_id), 0, 1, COUNT(pa2.line_id)), 0) - nvl(SUM(decode(pa2.arithmetic_operator, 'LUMPSUM', pa2.operand, pa2.adjusted_amount * l.ordered_quantity)), 0) + nvl(SUM(l.ordered_quantity *decode(nvl(pa2.line_id, -1), -1, decode(pa2.arithmetic_operator, NULL, 0, '%', pa2.operand *l.unit_list_price / 100, 'AMT', pa2.operand, 'NEWPRICE',(l.unit_list_price -pa2.operand)), 0)), 0)) / nvl(SUM(pa1.adjusted_amount) / decode(COUNT(pa2.line_id), 0, 1, COUNT(pa2.line_id)), 0) ELSE 0 END FROM oe_price_adjustments pa2, oe_price_adjustments pa1 WHERE pa2.list_line_type_code = 'FREIGHT_CHARGE' AND pa2.applied_flag = 'Y' AND pa1.list_line_type_code = 'COST' AND pa1.line_id = l.line_id AND pa1.header_id = l.header_id AND pa1.header_id = pa2.header_id(+) AND pa1.line_id = nvl(pa2.line_id(+), pa1.line_id)) * pa.adjusted_amount, 0) , 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, qpr_sr_util.get_null_pk, NULL, l.line_id, NULL, 'OM-' || pa.price_adjustment_id, NULL FROM oe_order_lines_all l, oe_price_adjustments pa, oe_order_headers_all h WHERE pa.line_id = l.line_id AND 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 pa.list_line_type_code = 'COST' 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'))
, NVL(TRUNC(H.BOOKED_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NULL
, -1 * QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE) * NVL( (SELECT CASE WHEN((NVL(SUM(DECODE(PA2.ARITHMETIC_OPERATOR
, 'LUMPSUM'
, PA2.OPERAND
, PA2.ADJUSTED_AMOUNT *L.ORDERED_QUANTITY))
, 0) + NVL(SUM(L.ORDERED_QUANTITY * DECODE(NVL(PA2.LINE_ID
, -1)
, -1
, DECODE(PA2.ARITHMETIC_OPERATOR
, NULL
, 0
, '%'
, PA2.OPERAND * L.UNIT_LIST_PRICE / 100
, 'AMT'
, PA2.OPERAND
, 'NEWPRICE'
, (L.UNIT_LIST_PRICE -PA2.OPERAND))
, 0))
, 0)) < NVL(SUM(PA1.ADJUSTED_AMOUNT) / DECODE(COUNT(PA2.LINE_ID)
, 0
, 1
, COUNT(PA2.LINE_ID))
, 0)) THEN (NVL(SUM(PA1.ADJUSTED_AMOUNT) / DECODE(COUNT(PA2.LINE_ID)
, 0
, 1
, COUNT(PA2.LINE_ID))
, 0) - NVL(SUM(DECODE(PA2.ARITHMETIC_OPERATOR
, 'LUMPSUM'
, PA2.OPERAND
, PA2.ADJUSTED_AMOUNT * L.ORDERED_QUANTITY))
, 0) + NVL(SUM(L.ORDERED_QUANTITY *DECODE(NVL(PA2.LINE_ID
, -1)
, -1
, DECODE(PA2.ARITHMETIC_OPERATOR
, NULL
, 0
, '%'
, PA2.OPERAND *L.UNIT_LIST_PRICE / 100
, 'AMT'
, PA2.OPERAND
, 'NEWPRICE'
, (L.UNIT_LIST_PRICE -PA2.OPERAND))
, 0))
, 0)) / NVL(SUM(PA1.ADJUSTED_AMOUNT) / DECODE(COUNT(PA2.LINE_ID)
, 0
, 1
, COUNT(PA2.LINE_ID))
, 0) ELSE 0 END
FROM OE_PRICE_ADJUSTMENTS PA2
, OE_PRICE_ADJUSTMENTS PA1
WHERE PA2.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND PA2.APPLIED_FLAG = 'Y'
AND PA1.LIST_LINE_TYPE_CODE = 'COST'
AND PA1.LINE_ID = L.LINE_ID
AND PA1.HEADER_ID = L.HEADER_ID
AND PA1.HEADER_ID = PA2.HEADER_ID(+)
AND PA1.LINE_ID = NVL(PA2.LINE_ID(+)
, PA1.LINE_ID)) * PA.ADJUSTED_AMOUNT
, 0)
, 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
, QPR_SR_UTIL.GET_NULL_PK
, NULL
, L.LINE_ID
, NULL
, 'OM-' || PA.PRICE_ADJUSTMENT_ID
, NULL
FROM OE_ORDER_LINES_ALL L
, OE_PRICE_ADJUSTMENTS PA
, OE_ORDER_HEADERS_ALL H
WHERE PA.LINE_ID = L.LINE_ID
AND 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 PA.LIST_LINE_TYPE_CODE = 'COST'
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')