VIEW: APPS.QPR_SR_QUOTE_ADJS_V

Source

View Text - Preformatted

SELECT l.quote_header_id, l.quote_line_id, nvl( (SELECT application_id FROM fnd_application_vl WHERE application_short_name = 'ASO' AND rownum < 2), qpr_sr_util.get_null_pk), 'ONINVOICE', pa.modifier_line_id, (select name from qp_list_headers_vl where list_header_id = pa.modifier_header_id and rownum < 2) || '-' || (select list_line_no from qp_list_lines where list_line_id = pa.modifier_line_id and rownum < 2) modifier_desc, case when pa.quote_line_id is not null then (decode(pa.arithmetic_operator, 'LUMPSUM', decode(l.quantity, 0, 0, pa.operand / l.quantity) * decode(pa.modifier_line_type_code, 'SUR', -1, 1), -1 * pa.adjusted_amount) ) else decode(pa.modifier_line_type_code, 'SUR', -1, 1) * decode(pa.arithmetic_operator, NULL, 0, '%', pa.operand * l.line_list_price / 100, 'AMT', pa.operand, 'NEWPRICE',(l.line_list_price - pa.operand)) end adj_amt, case when pa.quote_line_id is not null then (decode(pa.arithmetic_operator, 'LUMPSUM', pa.operand * decode(modifier_line_type_code, 'SUR', -1, 1), -1 * pa.adjusted_amount * l.quantity)) else decode(modifier_line_type_code, 'SUR', -1, 1) * decode(pa.arithmetic_operator, NULL, 0, '%', pa.operand * l.line_list_price / 100, 'AMT', pa.operand, 'NEWPRICE', (l.line_list_price - pa.operand)) * l.quantity end adj_rev, case when pa.quote_line_id is not null then (decode(nvl(l.line_list_price, 0), 0, 0, decode(pa.arithmetic_operator, 'LUMPSUM', decode(l.quantity, 0, 0, pa.operand / l.quantity) * decode(modifier_line_type_code, 'SUR', -1, 1), -1 * pa.adjusted_amount) * 100 / l.line_list_price)) else decode(modifier_line_type_code, 'SUR', -1, 1) * decode(nvl(l.line_list_price, 0), 0, 0, decode(pa.arithmetic_operator, NULL, 0, '%', pa.operand *l.line_list_price / 100, 'AMT', pa.operand, 'NEWPRICE',(l.line_list_price - pa.operand)) *100 / l.line_list_price) end adj_perc from aso_price_adjustments pa, aso_quote_headers_all h, aso_quote_lines_all l WHERE pa.applied_flag = 'Y' and pa.accrual_flag = 'N' and pa.modifier_line_id is not null and pa.quote_header_id = h.quote_header_id and nvl(pa.quote_line_id, l.quote_line_id) = l.quote_line_id AND l.quote_header_id = h.quote_header_id AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and h.cust_account_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.modifier_line_type_code IN('PBH') OR(pa.modifier_line_type_code IN('DIS', 'SUR') AND pa.modifier_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.QUOTE_HEADER_ID
, L.QUOTE_LINE_ID
, NVL( (SELECT APPLICATION_ID
FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME = 'ASO'
AND ROWNUM < 2)
, QPR_SR_UTIL.GET_NULL_PK)
, 'ONINVOICE'
, PA.MODIFIER_LINE_ID
, (SELECT NAME
FROM QP_LIST_HEADERS_VL
WHERE LIST_HEADER_ID = PA.MODIFIER_HEADER_ID
AND ROWNUM < 2) || '-' || (SELECT LIST_LINE_NO
FROM QP_LIST_LINES
WHERE LIST_LINE_ID = PA.MODIFIER_LINE_ID
AND ROWNUM < 2) MODIFIER_DESC
, CASE WHEN PA.QUOTE_LINE_ID IS NOT NULL THEN (DECODE(PA.ARITHMETIC_OPERATOR
, 'LUMPSUM'
, DECODE(L.QUANTITY
, 0
, 0
, PA.OPERAND / L.QUANTITY) * DECODE(PA.MODIFIER_LINE_TYPE_CODE
, 'SUR'
, -1
, 1)
, -1 * PA.ADJUSTED_AMOUNT) ) ELSE DECODE(PA.MODIFIER_LINE_TYPE_CODE
, 'SUR'
, -1
, 1) * DECODE(PA.ARITHMETIC_OPERATOR
, NULL
, 0
, '%'
, PA.OPERAND * L.LINE_LIST_PRICE / 100
, 'AMT'
, PA.OPERAND
, 'NEWPRICE'
, (L.LINE_LIST_PRICE - PA.OPERAND)) END ADJ_AMT
, CASE WHEN PA.QUOTE_LINE_ID IS NOT NULL THEN (DECODE(PA.ARITHMETIC_OPERATOR
, 'LUMPSUM'
, PA.OPERAND * DECODE(MODIFIER_LINE_TYPE_CODE
, 'SUR'
, -1
, 1)
, -1 * PA.ADJUSTED_AMOUNT * L.QUANTITY)) ELSE DECODE(MODIFIER_LINE_TYPE_CODE
, 'SUR'
, -1
, 1) * DECODE(PA.ARITHMETIC_OPERATOR
, NULL
, 0
, '%'
, PA.OPERAND * L.LINE_LIST_PRICE / 100
, 'AMT'
, PA.OPERAND
, 'NEWPRICE'
, (L.LINE_LIST_PRICE - PA.OPERAND)) * L.QUANTITY END ADJ_REV
, CASE WHEN PA.QUOTE_LINE_ID IS NOT NULL THEN (DECODE(NVL(L.LINE_LIST_PRICE
, 0)
, 0
, 0
, DECODE(PA.ARITHMETIC_OPERATOR
, 'LUMPSUM'
, DECODE(L.QUANTITY
, 0
, 0
, PA.OPERAND / L.QUANTITY) * DECODE(MODIFIER_LINE_TYPE_CODE
, 'SUR'
, -1
, 1)
, -1 * PA.ADJUSTED_AMOUNT) * 100 / L.LINE_LIST_PRICE)) ELSE DECODE(MODIFIER_LINE_TYPE_CODE
, 'SUR'
, -1
, 1) * DECODE(NVL(L.LINE_LIST_PRICE
, 0)
, 0
, 0
, DECODE(PA.ARITHMETIC_OPERATOR
, NULL
, 0
, '%'
, PA.OPERAND *L.LINE_LIST_PRICE / 100
, 'AMT'
, PA.OPERAND
, 'NEWPRICE'
, (L.LINE_LIST_PRICE - PA.OPERAND)) *100 / L.LINE_LIST_PRICE) END ADJ_PERC
FROM ASO_PRICE_ADJUSTMENTS PA
, ASO_QUOTE_HEADERS_ALL H
, ASO_QUOTE_LINES_ALL L
WHERE PA.APPLIED_FLAG = 'Y'
AND PA.ACCRUAL_FLAG = 'N'
AND PA.MODIFIER_LINE_ID IS NOT NULL
AND PA.QUOTE_HEADER_ID = H.QUOTE_HEADER_ID
AND NVL(PA.QUOTE_LINE_ID
, L.QUOTE_LINE_ID) = L.QUOTE_LINE_ID
AND L.QUOTE_HEADER_ID = H.QUOTE_HEADER_ID
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND H.CUST_ACCOUNT_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.MODIFIER_LINE_TYPE_CODE IN('PBH') OR(PA.MODIFIER_LINE_TYPE_CODE IN('DIS'
, 'SUR')
AND PA.MODIFIER_LINE_ID NOT IN (SELECT TO_RLTD_MODIFIER_ID
FROM QP_RLTD_MODIFIERS
WHERE RLTD_MODIFIER_GRP_TYPE = 'PRICE BREAK')))