VIEW: APPS.QPR_SR_ADJ_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')), (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, 'PBH', -1, 'DIS', -1, 1), pa.adjusted_amount) ) else decode(pa.list_line_type_code, 'PBH', -1, 'DIS', -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) * 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) adj_amt, (case when pa.line_id is not null then (decode(pa.arithmetic_operator, 'LUMPSUM', pa.operand * decode(list_line_type_code, 'PBH', -1, 'DIS', -1, 1), pa.adjusted_amount *l.ordered_quantity)) else decode(list_line_type_code, 'PBH', -1, 'DIS', -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 ) * qpr_sr_util.convert_global_amt(h.transactional_curr_code, h.booked_date) 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, 'PBH', -1, 'DIS', -1, 1), pa.adjusted_amount) * 100 / l.unit_list_price)) else decode(list_line_type_code, 'PBH', -1, 'DIS', -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, 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, pa.list_line_id, NULL, pa.list_line_type_code 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.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 IN('PBH', 'FREIGHT_CHARGE') 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'))) and qpr_sr_util.convert_global_amt(h.transactional_curr_code, h.booked_date) > 0 and qpr_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id, NULL) > 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'))
, (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
, 'PBH'
, -1
, 'DIS'
, -1
, 1)
, PA.ADJUSTED_AMOUNT) ) ELSE DECODE(PA.LIST_LINE_TYPE_CODE
, 'PBH'
, -1
, 'DIS'
, -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) * 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) ADJ_AMT
, (CASE WHEN PA.LINE_ID IS NOT NULL THEN (DECODE(PA.ARITHMETIC_OPERATOR
, 'LUMPSUM'
, PA.OPERAND * DECODE(LIST_LINE_TYPE_CODE
, 'PBH'
, -1
, 'DIS'
, -1
, 1)
, PA.ADJUSTED_AMOUNT *L.ORDERED_QUANTITY)) ELSE DECODE(LIST_LINE_TYPE_CODE
, 'PBH'
, -1
, 'DIS'
, -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 ) * QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE) 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
, 'PBH'
, -1
, 'DIS'
, -1
, 1)
, PA.ADJUSTED_AMOUNT) * 100 / L.UNIT_LIST_PRICE)) ELSE DECODE(LIST_LINE_TYPE_CODE
, 'PBH'
, -1
, 'DIS'
, -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
, 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
, PA.LIST_LINE_ID
, NULL
, PA.LIST_LINE_TYPE_CODE
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.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 IN('PBH'
, 'FREIGHT_CHARGE') 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')))
AND QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE) > 0
AND QPR_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID
, NULL) > 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')