VIEW: APPS.QPR_SR_OFFINV_AR_V

Source

View Text - Preformatted

SELECT a.customer_trx_id, a.hdr_reason_code, a.sold_to_customer_id, a.org_id, a.trx_number, a.trx_date, a.invoice_currency_code, a.trx_name, a.trx_type, a.cust_trx_type_id, a.cust_trx_line_id, a.line_reason_code, a.inventory_item_id, a.description, a.quantity_ordered * uom_conv, a.unit_selling_price *curr_conv/uom_conv, a.extended_amount * curr_conv, a.uom_code, a.line_number from (select distinct 'AR-' || l.customer_trx_line_id as cust_trx_line_id, l.reason_code line_reason_code, l.inventory_item_id, l.description, l.quantity_ordered, l.unit_selling_price, l.extended_amount, l.uom_code, l.line_number, decode(l.inventory_item_id, null, 1, qpr_sr_util.uom_conv(l.uom_code, l.inventory_item_id, NULL)) uom_conv, qpr_sr_util.convert_global_amt(h.invoice_currency_code, h.trx_date) curr_conv, h.customer_trx_id, h.reason_code hdr_reason_code, h.sold_to_customer_id, h.org_id, h.trx_number, h.trx_date, h.invoice_currency_code, (SELECT tt.name FROM ra_cust_trx_types_all tt WHERE tt.cust_trx_type_id = h.cust_trx_type_id AND tt.org_id = h.org_id AND rownum < 2) trx_name, (SELECT tt.type FROM ra_cust_trx_types_all tt WHERE tt.cust_trx_type_id = h.cust_trx_type_id AND tt.org_id = h.org_id AND rownum < 2) trx_type, h.cust_trx_type_id from ra_customer_trx_lines_all l, ra_customer_trx_all h where l.line_type <> 'TAX' and l.customer_trx_id = h.customer_trx_id AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and h.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') ) a WHERE a.uom_conv > 0 and a.curr_conv > 0

View Text - HTML Formatted

SELECT A.CUSTOMER_TRX_ID
, A.HDR_REASON_CODE
, A.SOLD_TO_CUSTOMER_ID
, A.ORG_ID
, A.TRX_NUMBER
, A.TRX_DATE
, A.INVOICE_CURRENCY_CODE
, A.TRX_NAME
, A.TRX_TYPE
, A.CUST_TRX_TYPE_ID
, A.CUST_TRX_LINE_ID
, A.LINE_REASON_CODE
, A.INVENTORY_ITEM_ID
, A.DESCRIPTION
, A.QUANTITY_ORDERED * UOM_CONV
, A.UNIT_SELLING_PRICE *CURR_CONV/UOM_CONV
, A.EXTENDED_AMOUNT * CURR_CONV
, A.UOM_CODE
, A.LINE_NUMBER
FROM (SELECT DISTINCT 'AR-' || L.CUSTOMER_TRX_LINE_ID AS CUST_TRX_LINE_ID
, L.REASON_CODE LINE_REASON_CODE
, L.INVENTORY_ITEM_ID
, L.DESCRIPTION
, L.QUANTITY_ORDERED
, L.UNIT_SELLING_PRICE
, L.EXTENDED_AMOUNT
, L.UOM_CODE
, L.LINE_NUMBER
, DECODE(L.INVENTORY_ITEM_ID
, NULL
, 1
, QPR_SR_UTIL.UOM_CONV(L.UOM_CODE
, L.INVENTORY_ITEM_ID
, NULL)) UOM_CONV
, QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.INVOICE_CURRENCY_CODE
, H.TRX_DATE) CURR_CONV
, H.CUSTOMER_TRX_ID
, H.REASON_CODE HDR_REASON_CODE
, H.SOLD_TO_CUSTOMER_ID
, H.ORG_ID
, H.TRX_NUMBER
, H.TRX_DATE
, H.INVOICE_CURRENCY_CODE
, (SELECT TT.NAME
FROM RA_CUST_TRX_TYPES_ALL TT
WHERE TT.CUST_TRX_TYPE_ID = H.CUST_TRX_TYPE_ID
AND TT.ORG_ID = H.ORG_ID
AND ROWNUM < 2) TRX_NAME
, (SELECT TT.TYPE
FROM RA_CUST_TRX_TYPES_ALL TT
WHERE TT.CUST_TRX_TYPE_ID = H.CUST_TRX_TYPE_ID
AND TT.ORG_ID = H.ORG_ID
AND ROWNUM < 2) TRX_TYPE
, H.CUST_TRX_TYPE_ID
FROM RA_CUSTOMER_TRX_LINES_ALL L
, RA_CUSTOMER_TRX_ALL H
WHERE L.LINE_TYPE <> 'TAX'
AND L.CUSTOMER_TRX_ID = H.CUSTOMER_TRX_ID
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND H.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') ) A
WHERE A.UOM_CONV > 0
AND A.CURR_CONV > 0