VIEW: APPS.QPR_SR_OFFADJ_CD_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(rec.apply_date, 'dd'), to_date('1000/01/01', 'yyyy/mm/dd')), (case when rec.applied_customer_trx_line_id is not null then decode(hist.status, 'REVERSED', 0,(-1 *nvl(rec.earned_discount_taken, nvl(rec.unearned_discount_taken, 0)) ) /(ral.quantity_ordered )) else decode(hist.status, 'REVERSED', 0, -1 *((nvl(rec.earned_discount_taken, nvl(rec.unearned_discount_taken, 0)) * ral.unit_selling_price *ral.quantity_ordered) / (SELECT SUM(ral1.unit_selling_price *ral1.quantity_ordered) FROM ra_customer_trx_lines_all ral1 WHERE ral1.line_type = 'LINE' AND ral1.customer_trx_id = rec.applied_customer_trx_id GROUP BY ral1.customer_trx_id)) /(ral.quantity_ordered)) end) * qpr_sr_util.convert_global_amt(rah.invoice_currency_code, rec.apply_date)/ qpr_sr_util.uom_conv (ral.uom_code, ral.inventory_item_id, null) , (case when rec.applied_customer_trx_line_id is not null then decode(hist.status, 'REVERSED', 0, -1 * nvl(rec.earned_discount_taken, nvl(rec.unearned_discount_taken, 0))) else decode(hist.status, 'REVERSED', 0, -1 *((nvl(rec.earned_discount_taken, nvl(rec.unearned_discount_taken, 0)) * ral.unit_selling_price *ral.quantity_ordered) / (SELECT SUM(ral1.unit_selling_price *ral1.quantity_ordered) FROM ra_customer_trx_lines_all ral1 WHERE ral1.line_type = 'LINE' AND ral1.customer_trx_id = rec.applied_customer_trx_id GROUP BY ral1.customer_trx_id)) ) end) * qpr_sr_util.convert_global_amt(rah.invoice_currency_code, rec.apply_date) , 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, 'CD-' || ral.customer_trx_line_id, NULL FROM oe_order_lines_all l, oe_order_headers_all h, ar_cash_receipt_history_all hist, ar_receivable_applications_all rec, ra_customer_trx_lines_all ral, ra_customer_trx_all rah WHERE hist.status IN('CONFIRMED', 'CLEARED', 'REMITTED', 'REVERSED') AND hist.current_record_flag = 'Y' AND hist.cash_receipt_id = rec.cash_receipt_id AND rec.status = 'APP' AND rec.display = decode(hist.status, 'REVERSED', 'N', 'Y') AND rec.cash_receipt_history_id = decode(hist.status, 'REVERSED', hist.cash_receipt_history_id, rec.cash_receipt_history_id) AND rec.application_type = 'CASH' AND(nvl(rec.earned_discount_taken, 0) <> 0 OR nvl(rec.unearned_discount_taken, 0) <> 0) AND rec.applied_customer_trx_id = rah.customer_trx_id AND nvl(rec.applied_customer_trx_line_id, ral.customer_trx_line_id) = ral.customer_trx_line_id AND ral.customer_trx_id = rah.customer_trx_id AND ral.line_type = 'LINE' AND l.line_id = ral.interface_line_attribute6 AND l.org_id = ral.org_id AND l.header_id = h.header_id AND qpr_sr_util.uom_conv (ral.uom_code, ral.inventory_item_id, null) > 0 and qpr_sr_util.convert_global_amt(rah.invoice_currency_code, rec.apply_date) > 0 AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and rah.sold_to_customer_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(REC.APPLY_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, (CASE WHEN REC.APPLIED_CUSTOMER_TRX_LINE_ID IS NOT NULL THEN DECODE(HIST.STATUS
, 'REVERSED'
, 0
, (-1 *NVL(REC.EARNED_DISCOUNT_TAKEN
, NVL(REC.UNEARNED_DISCOUNT_TAKEN
, 0)) ) /(RAL.QUANTITY_ORDERED )) ELSE DECODE(HIST.STATUS
, 'REVERSED'
, 0
, -1 *((NVL(REC.EARNED_DISCOUNT_TAKEN
, NVL(REC.UNEARNED_DISCOUNT_TAKEN
, 0)) * RAL.UNIT_SELLING_PRICE *RAL.QUANTITY_ORDERED) / (SELECT SUM(RAL1.UNIT_SELLING_PRICE *RAL1.QUANTITY_ORDERED)
FROM RA_CUSTOMER_TRX_LINES_ALL RAL1
WHERE RAL1.LINE_TYPE = 'LINE'
AND RAL1.CUSTOMER_TRX_ID = REC.APPLIED_CUSTOMER_TRX_ID GROUP BY RAL1.CUSTOMER_TRX_ID)) /(RAL.QUANTITY_ORDERED)) END) * QPR_SR_UTIL.CONVERT_GLOBAL_AMT(RAH.INVOICE_CURRENCY_CODE
, REC.APPLY_DATE)/ QPR_SR_UTIL.UOM_CONV (RAL.UOM_CODE
, RAL.INVENTORY_ITEM_ID
, NULL)
, (CASE WHEN REC.APPLIED_CUSTOMER_TRX_LINE_ID IS NOT NULL THEN DECODE(HIST.STATUS
, 'REVERSED'
, 0
, -1 * NVL(REC.EARNED_DISCOUNT_TAKEN
, NVL(REC.UNEARNED_DISCOUNT_TAKEN
, 0))) ELSE DECODE(HIST.STATUS
, 'REVERSED'
, 0
, -1 *((NVL(REC.EARNED_DISCOUNT_TAKEN
, NVL(REC.UNEARNED_DISCOUNT_TAKEN
, 0)) * RAL.UNIT_SELLING_PRICE *RAL.QUANTITY_ORDERED) / (SELECT SUM(RAL1.UNIT_SELLING_PRICE *RAL1.QUANTITY_ORDERED)
FROM RA_CUSTOMER_TRX_LINES_ALL RAL1
WHERE RAL1.LINE_TYPE = 'LINE'
AND RAL1.CUSTOMER_TRX_ID = REC.APPLIED_CUSTOMER_TRX_ID GROUP BY RAL1.CUSTOMER_TRX_ID)) ) END) * QPR_SR_UTIL.CONVERT_GLOBAL_AMT(RAH.INVOICE_CURRENCY_CODE
, REC.APPLY_DATE)
, 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
, 'CD-' || RAL.CUSTOMER_TRX_LINE_ID
, NULL
FROM OE_ORDER_LINES_ALL L
, OE_ORDER_HEADERS_ALL H
, AR_CASH_RECEIPT_HISTORY_ALL HIST
, AR_RECEIVABLE_APPLICATIONS_ALL REC
, RA_CUSTOMER_TRX_LINES_ALL RAL
, RA_CUSTOMER_TRX_ALL RAH
WHERE HIST.STATUS IN('CONFIRMED'
, 'CLEARED'
, 'REMITTED'
, 'REVERSED')
AND HIST.CURRENT_RECORD_FLAG = 'Y'
AND HIST.CASH_RECEIPT_ID = REC.CASH_RECEIPT_ID
AND REC.STATUS = 'APP'
AND REC.DISPLAY = DECODE(HIST.STATUS
, 'REVERSED'
, 'N'
, 'Y')
AND REC.CASH_RECEIPT_HISTORY_ID = DECODE(HIST.STATUS
, 'REVERSED'
, HIST.CASH_RECEIPT_HISTORY_ID
, REC.CASH_RECEIPT_HISTORY_ID)
AND REC.APPLICATION_TYPE = 'CASH' AND(NVL(REC.EARNED_DISCOUNT_TAKEN
, 0) <> 0 OR NVL(REC.UNEARNED_DISCOUNT_TAKEN
, 0) <> 0)
AND REC.APPLIED_CUSTOMER_TRX_ID = RAH.CUSTOMER_TRX_ID
AND NVL(REC.APPLIED_CUSTOMER_TRX_LINE_ID
, RAL.CUSTOMER_TRX_LINE_ID) = RAL.CUSTOMER_TRX_LINE_ID
AND RAL.CUSTOMER_TRX_ID = RAH.CUSTOMER_TRX_ID
AND RAL.LINE_TYPE = 'LINE'
AND L.LINE_ID = RAL.INTERFACE_LINE_ATTRIBUTE6
AND L.ORG_ID = RAL.ORG_ID
AND L.HEADER_ID = H.HEADER_ID
AND QPR_SR_UTIL.UOM_CONV (RAL.UOM_CODE
, RAL.INVENTORY_ITEM_ID
, NULL) > 0
AND QPR_SR_UTIL.CONVERT_GLOBAL_AMT(RAH.INVOICE_CURRENCY_CODE
, REC.APPLY_DATE) > 0
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND RAH.SOLD_TO_CUSTOMER_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')