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_OM_QUOTE_ADJS_V

Source

View Text - Preformatted


SELECT l.header_id, l.line_id, nvl( (SELECT application_id FROM fnd_application_vl WHERE application_short_name = 'ONT' AND rownum < 2), qpr_sr_util.get_null_pk), 'ONINVOICE', pa.list_line_id, (SELECT name FROM qp_list_headers_vl WHERE list_header_id = pa.list_header_id AND rownum < 2) || '-' || pa.list_line_no modifier_desc, CASE WHEN pa.line_id IS NOT NULL THEN (decode(pa.arithmetic_operator, 'LUMPSUM', decode(l.ordered_quantity, 0, 0, pa.operand / l.ordered_quantity) * decode(list_line_type_code, 'SUR', -1, 1), -1 * pa.adjusted_amount)) ELSE decode(pa.list_line_type_code, 'SUR', -1, 1) * decode(pa.arithmetic_operator, NULL, 0, '%', pa.operand * l.unit_list_price / 100, 'AMT', pa.operand, 'NEWPRICE', (l.unit_list_price -pa.operand)) END adj_amt, CASE WHEN pa.line_id IS NOT NULL THEN (decode(pa.arithmetic_operator, 'LUMPSUM', pa.operand * decode(list_line_type_code, 'SUR', -1, 1), -1 * pa.adjusted_amount * l.ordered_quantity)) ELSE decode(list_line_type_code, 'SUR', -1, 1) * decode(pa.arithmetic_operator, NULL, 0, '%', pa.operand * l.unit_list_price / 100, 'AMT', pa.operand, 'NEWPRICE', (l.unit_list_price -pa.operand)) * l.ordered_quantity END adj_rev, CASE WHEN pa.line_id IS NOT NULL THEN (decode(nvl(l.unit_list_price, 0), 0, 0, decode(pa.arithmetic_operator, 'LUMPSUM', decode(l.ordered_quantity, 0, 0, pa.operand / l.ordered_quantity) * decode(list_line_type_code, 'SUR', -1, 1), -1 * pa.adjusted_amount) * 100 / l.unit_list_price)) ELSE decode(list_line_type_code, 'SUR', -1, 1) * decode(nvl(l.unit_list_price, 0), 0, 0, decode(pa.arithmetic_operator, NULL, 0, '%', pa.operand * l.unit_list_price / 100, 'AMT', pa.operand, 'NEWPRICE', (l.unit_list_price -pa.operand)) * 100 / l.unit_list_price) END adj_perc FROM oe_price_adjustments pa, oe_order_headers_all h, oe_order_lines_all l WHERE pa.applied_flag = 'Y' and pa.accrual_flag = 'N' AND pa.list_line_id IS NOT NULL AND pa.header_id = h.header_id AND nvl(pa.line_id, l.line_id) = l.line_id AND l.header_id = h.header_id AND h.cancelled_flag = 'N' AND l.cancelled_flag = 'N' 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 ((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') AND(pa.list_line_type_code IN('PBH') OR(pa.list_line_type_code IN('DIS', 'SUR') AND pa.list_line_id NOT IN (SELECT to_rltd_modifier_id FROM qp_rltd_modifiers WHERE rltd_modifier_grp_type = 'PRICE BREAK')))

View Text - HTML Formatted

SELECT L.HEADER_ID
, L.LINE_ID
, NVL( (SELECT APPLICATION_ID
FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME = 'ONT'
AND ROWNUM < 2)
, QPR_SR_UTIL.GET_NULL_PK)
, 'ONINVOICE'
, PA.LIST_LINE_ID
, (SELECT NAME
FROM QP_LIST_HEADERS_VL
WHERE LIST_HEADER_ID = PA.LIST_HEADER_ID
AND ROWNUM < 2) || '-' || PA.LIST_LINE_NO MODIFIER_DESC
, CASE WHEN PA.LINE_ID IS NOT NULL THEN (DECODE(PA.ARITHMETIC_OPERATOR
, 'LUMPSUM'
, DECODE(L.ORDERED_QUANTITY
, 0
, 0
, PA.OPERAND / L.ORDERED_QUANTITY) * DECODE(LIST_LINE_TYPE_CODE
, 'SUR'
, -1
, 1)
, -1 * PA.ADJUSTED_AMOUNT)) ELSE DECODE(PA.LIST_LINE_TYPE_CODE
, 'SUR'
, -1
, 1) * DECODE(PA.ARITHMETIC_OPERATOR
, NULL
, 0
, '%'
, PA.OPERAND * L.UNIT_LIST_PRICE / 100
, 'AMT'
, PA.OPERAND
, 'NEWPRICE'
, (L.UNIT_LIST_PRICE -PA.OPERAND)) END ADJ_AMT
, CASE WHEN PA.LINE_ID IS NOT NULL THEN (DECODE(PA.ARITHMETIC_OPERATOR
, 'LUMPSUM'
, PA.OPERAND * DECODE(LIST_LINE_TYPE_CODE
, 'SUR'
, -1
, 1)
, -1 * PA.ADJUSTED_AMOUNT * L.ORDERED_QUANTITY)) ELSE DECODE(LIST_LINE_TYPE_CODE
, 'SUR'
, -1
, 1) * DECODE(PA.ARITHMETIC_OPERATOR
, NULL
, 0
, '%'
, PA.OPERAND * L.UNIT_LIST_PRICE / 100
, 'AMT'
, PA.OPERAND
, 'NEWPRICE'
, (L.UNIT_LIST_PRICE -PA.OPERAND)) * L.ORDERED_QUANTITY END ADJ_REV
, CASE WHEN PA.LINE_ID IS NOT NULL THEN (DECODE(NVL(L.UNIT_LIST_PRICE
, 0)
, 0
, 0
, DECODE(PA.ARITHMETIC_OPERATOR
, 'LUMPSUM'
, DECODE(L.ORDERED_QUANTITY
, 0
, 0
, PA.OPERAND / L.ORDERED_QUANTITY) * DECODE(LIST_LINE_TYPE_CODE
, 'SUR'
, -1
, 1)
, -1 * PA.ADJUSTED_AMOUNT) * 100 / L.UNIT_LIST_PRICE)) ELSE DECODE(LIST_LINE_TYPE_CODE
, 'SUR'
, -1
, 1) * DECODE(NVL(L.UNIT_LIST_PRICE
, 0)
, 0
, 0
, DECODE(PA.ARITHMETIC_OPERATOR
, NULL
, 0
, '%'
, PA.OPERAND * L.UNIT_LIST_PRICE / 100
, 'AMT'
, PA.OPERAND
, 'NEWPRICE'
, (L.UNIT_LIST_PRICE -PA.OPERAND)) * 100 / L.UNIT_LIST_PRICE) END ADJ_PERC
FROM OE_PRICE_ADJUSTMENTS PA
, OE_ORDER_HEADERS_ALL H
, OE_ORDER_LINES_ALL L
WHERE PA.APPLIED_FLAG = 'Y'
AND PA.ACCRUAL_FLAG = 'N'
AND PA.LIST_LINE_ID IS NOT NULL
AND PA.HEADER_ID = H.HEADER_ID
AND NVL(PA.LINE_ID
, L.LINE_ID) = L.LINE_ID
AND L.HEADER_ID = H.HEADER_ID
AND H.CANCELLED_FLAG = 'N'
AND L.CANCELLED_FLAG = 'N'
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 ((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') AND(PA.LIST_LINE_TYPE_CODE IN('PBH') OR(PA.LIST_LINE_TYPE_CODE IN('DIS'
, 'SUR')
AND PA.LIST_LINE_ID NOT IN (SELECT TO_RLTD_MODIFIER_ID
FROM QP_RLTD_MODIFIERS
WHERE RLTD_MODIFIER_GRP_TYPE = 'PRICE BREAK')))