VIEW: APPS.IEX_INVOICE_DETAILS_V

Source

View Text - Preformatted

SELECT trx.CUSTOMER_TRX_ID ,trx.trx_number ,trx.INVOICE_CURRENCY_CODE ,trx.trx_date ,trx.status_trx ,lv.meaning ,trx.cust_trx_type_id ,trxtp.name ,trxtp.type ,sum(pay.amount_due_original) ,sum(pay.amount_due_remaining) ,LOC.ADDRESS1 || ' ' || LOC. ADDRESS2 || ' ' || LOC.CITY || ' ' || LOC.STATE || ' ' || LOC.POSTAL_CODE || ' ' || LOC.COUNTRY ,LOC1.ADDRESS1 || ' ' || LOC1. ADDRESS2 || ' ' || LOC1.CITY || ' ' || LOC1.STATE || ' ' || LOC1.POSTAL_CODE || ' ' || LOC1.COUNTRY ,sum(pay.AMOUNT_LINE_ITEMS_ORIGINAL) ,sum(pay.AMOUNT_LINE_ITEMS_REMAINING) ,sum(pay.TAX_ORIGINAL) ,sum(pay.TAX_REMAINING) ,sum(pay.FREIGHT_ORIGINAL) ,sum(pay.FREIGHT_REMAINING) ,sum(pay.AMOUNT_ADJUSTED) ,sum(pay.AMOUNT_ADJUSTED_PENDING) ,sum(pay.RECEIVABLES_CHARGES_REMAINING) ,trx.attribute_category ,trx.attribute1 ,trx.attribute2 ,trx.attribute3 ,trx.attribute4 ,trx.attribute5 ,trx.attribute6 ,trx.attribute7 ,trx.attribute8 ,trx.attribute9 ,trx.attribute10 ,trx.attribute11 ,trx.attribute12 ,trx.attribute13 ,trx.attribute14 ,trx.attribute15 ,trx.INTERFACE_HEADER_CONTEXT ,trx.INTERFACE_HEADER_ATTRIBUTE1 ,trx.INTERFACE_HEADER_ATTRIBUTE2 ,trx.INTERFACE_HEADER_ATTRIBUTE3 ,trx.INTERFACE_HEADER_ATTRIBUTE4 ,trx.INTERFACE_HEADER_ATTRIBUTE5 ,trx.INTERFACE_HEADER_ATTRIBUTE6 ,trx.INTERFACE_HEADER_ATTRIBUTE7 ,trx.INTERFACE_HEADER_ATTRIBUTE8 ,trx.INTERFACE_HEADER_ATTRIBUTE9 ,trx.INTERFACE_HEADER_ATTRIBUTE10 ,trx.INTERFACE_HEADER_ATTRIBUTE11 ,trx.INTERFACE_HEADER_ATTRIBUTE12 ,trx.INTERFACE_HEADER_ATTRIBUTE13 ,trx.INTERFACE_HEADER_ATTRIBUTE14 ,trx.INTERFACE_HEADER_ATTRIBUTE15 ,trx.GLOBAL_ATTRIBUTE_CATEGORY ,trx.GLOBAL_ATTRIBUTE1 ,trx.GLOBAL_ATTRIBUTE2 ,trx.GLOBAL_ATTRIBUTE3 ,trx.GLOBAL_ATTRIBUTE4 ,trx.GLOBAL_ATTRIBUTE5 ,trx.GLOBAL_ATTRIBUTE6 ,trx.GLOBAL_ATTRIBUTE7 ,trx.GLOBAL_ATTRIBUTE8 ,trx.GLOBAL_ATTRIBUTE9 ,trx.GLOBAL_ATTRIBUTE10 ,trx.GLOBAL_ATTRIBUTE11 ,trx.GLOBAL_ATTRIBUTE12 ,trx.GLOBAL_ATTRIBUTE13 ,trx.GLOBAL_ATTRIBUTE14 ,trx.GLOBAL_ATTRIBUTE15 ,trx.GLOBAL_ATTRIBUTE16 ,trx.GLOBAL_ATTRIBUTE17 ,trx.GLOBAL_ATTRIBUTE18 ,trx.GLOBAL_ATTRIBUTE19 ,trx.GLOBAL_ATTRIBUTE20 ,trx.GLOBAL_ATTRIBUTE21 ,trx.GLOBAL_ATTRIBUTE22 ,trx.GLOBAL_ATTRIBUTE23 ,trx.GLOBAL_ATTRIBUTE24 ,trx.GLOBAL_ATTRIBUTE25 ,trx.GLOBAL_ATTRIBUTE26 ,trx.GLOBAL_ATTRIBUTE27 ,trx.GLOBAL_ATTRIBUTE28 ,trx.GLOBAL_ATTRIBUTE29 ,trx.GLOBAL_ATTRIBUTE30 FROM ar_payment_schedules pay, ra_Customer_trx trx, RA_CUST_TRX_TYPES trxtp, ar_lookups lv, hz_cust_site_uses site, hz_cust_acct_sites acct_site, hz_party_sites party_site, hz_locations loc, hz_cust_site_uses site1, hz_cust_acct_sites acct_site1, hz_party_sites party_site1, hz_locations loc1 WHERE pay.customer_trx_id = trx.customer_trx_id AND trx.cust_trx_type_id = trxtp.cust_trx_type_id AND trx.org_id = trxtp.org_id AND trx.bill_to_site_use_id = site.site_use_id(+) AND site.CUST_ACCT_SITE_ID = acct_site.CUST_ACCT_SITE_ID(+) AND acct_site.PARTY_SITE_ID = party_site.PARTY_SITE_ID(+) AND LOC.LOCATION_ID(+) = party_site.LOCATION_ID AND trx.Ship_to_site_use_id = site1.site_use_id(+) AND site1.CUST_ACCT_SITE_ID = acct_site1.CUST_ACCT_SITE_ID(+) AND acct_site1.PARTY_SITE_ID = party_site1.PARTY_SITE_ID(+) AND LOC1.LOCATION_ID(+) = party_site1.LOCATION_ID AND lv.lookup_code = trx.status_trx AND lv.lookup_type = 'INVOICE_TRX_STATUS' group by trx.CUSTOMER_TRX_ID ,trx.trx_number ,trx.INVOICE_CURRENCY_CODE ,trx.trx_date ,trx.status_trx ,lv.meaning ,trx.cust_trx_type_id ,trxtp.name ,trxtp.type ,LOC.ADDRESS1 || ' ' || LOC. ADDRESS2 || ' ' || LOC.CITY || ' ' || LOC.STATE || ' ' || LOC.POSTAL_CODE || ' ' || LOC.COUNTRY ,LOC1.ADDRESS1 || ' ' || LOC1. ADDRESS2 || ' ' || LOC1.CITY || ' ' || LOC1.STATE || ' ' || LOC1.POSTAL_CODE || ' ' || LOC1.COUNTRY ,trx.attribute_category ,trx.attribute1 ,trx.attribute2 ,trx.attribute3 ,trx.attribute4 ,trx.attribute5 ,trx.attribute6 ,trx.attribute7 ,trx.attribute8 ,trx.attribute9 ,trx.attribute10 ,trx.attribute11 ,trx.attribute12 ,trx.attribute13 ,trx.attribute14 ,trx.attribute15 ,trx.INTERFACE_HEADER_CONTEXT ,trx.INTERFACE_HEADER_ATTRIBUTE1 ,trx.INTERFACE_HEADER_ATTRIBUTE2 ,trx.INTERFACE_HEADER_ATTRIBUTE3 ,trx.INTERFACE_HEADER_ATTRIBUTE4 ,trx.INTERFACE_HEADER_ATTRIBUTE5 ,trx.INTERFACE_HEADER_ATTRIBUTE6 ,trx.INTERFACE_HEADER_ATTRIBUTE7 ,trx.INTERFACE_HEADER_ATTRIBUTE8 ,trx.INTERFACE_HEADER_ATTRIBUTE9 ,trx.INTERFACE_HEADER_ATTRIBUTE10 ,trx.INTERFACE_HEADER_ATTRIBUTE11 ,trx.INTERFACE_HEADER_ATTRIBUTE12 ,trx.INTERFACE_HEADER_ATTRIBUTE13 ,trx.INTERFACE_HEADER_ATTRIBUTE14 ,trx.INTERFACE_HEADER_ATTRIBUTE15 ,trx.GLOBAL_ATTRIBUTE_CATEGORY ,trx.GLOBAL_ATTRIBUTE1 ,trx.GLOBAL_ATTRIBUTE2 ,trx.GLOBAL_ATTRIBUTE3 ,trx.GLOBAL_ATTRIBUTE4 ,trx.GLOBAL_ATTRIBUTE5 ,trx.GLOBAL_ATTRIBUTE6 ,trx.GLOBAL_ATTRIBUTE7 ,trx.GLOBAL_ATTRIBUTE8 ,trx.GLOBAL_ATTRIBUTE9 ,trx.GLOBAL_ATTRIBUTE10 ,trx.GLOBAL_ATTRIBUTE11 ,trx.GLOBAL_ATTRIBUTE12 ,trx.GLOBAL_ATTRIBUTE13 ,trx.GLOBAL_ATTRIBUTE14 ,trx.GLOBAL_ATTRIBUTE15 ,trx.GLOBAL_ATTRIBUTE16 ,trx.GLOBAL_ATTRIBUTE17 ,trx.GLOBAL_ATTRIBUTE18 ,trx.GLOBAL_ATTRIBUTE19 ,trx.GLOBAL_ATTRIBUTE20 ,trx.GLOBAL_ATTRIBUTE21 ,trx.GLOBAL_ATTRIBUTE22 ,trx.GLOBAL_ATTRIBUTE23 ,trx.GLOBAL_ATTRIBUTE24 ,trx.GLOBAL_ATTRIBUTE25 ,trx.GLOBAL_ATTRIBUTE26 ,trx.GLOBAL_ATTRIBUTE27 ,trx.GLOBAL_ATTRIBUTE28 ,trx.GLOBAL_ATTRIBUTE29 ,trx.GLOBAL_ATTRIBUTE30

View Text - HTML Formatted

SELECT TRX.CUSTOMER_TRX_ID
, TRX.TRX_NUMBER
, TRX.INVOICE_CURRENCY_CODE
, TRX.TRX_DATE
, TRX.STATUS_TRX
, LV.MEANING
, TRX.CUST_TRX_TYPE_ID
, TRXTP.NAME
, TRXTP.TYPE
, SUM(PAY.AMOUNT_DUE_ORIGINAL)
, SUM(PAY.AMOUNT_DUE_REMAINING)
, LOC.ADDRESS1 || ' ' || LOC. ADDRESS2 || ' ' || LOC.CITY || ' ' || LOC.STATE || ' ' || LOC.POSTAL_CODE || ' ' || LOC.COUNTRY
, LOC1.ADDRESS1 || ' ' || LOC1. ADDRESS2 || ' ' || LOC1.CITY || ' ' || LOC1.STATE || ' ' || LOC1.POSTAL_CODE || ' ' || LOC1.COUNTRY
, SUM(PAY.AMOUNT_LINE_ITEMS_ORIGINAL)
, SUM(PAY.AMOUNT_LINE_ITEMS_REMAINING)
, SUM(PAY.TAX_ORIGINAL)
, SUM(PAY.TAX_REMAINING)
, SUM(PAY.FREIGHT_ORIGINAL)
, SUM(PAY.FREIGHT_REMAINING)
, SUM(PAY.AMOUNT_ADJUSTED)
, SUM(PAY.AMOUNT_ADJUSTED_PENDING)
, SUM(PAY.RECEIVABLES_CHARGES_REMAINING)
, TRX.ATTRIBUTE_CATEGORY
, TRX.ATTRIBUTE1
, TRX.ATTRIBUTE2
, TRX.ATTRIBUTE3
, TRX.ATTRIBUTE4
, TRX.ATTRIBUTE5
, TRX.ATTRIBUTE6
, TRX.ATTRIBUTE7
, TRX.ATTRIBUTE8
, TRX.ATTRIBUTE9
, TRX.ATTRIBUTE10
, TRX.ATTRIBUTE11
, TRX.ATTRIBUTE12
, TRX.ATTRIBUTE13
, TRX.ATTRIBUTE14
, TRX.ATTRIBUTE15
, TRX.INTERFACE_HEADER_CONTEXT
, TRX.INTERFACE_HEADER_ATTRIBUTE1
, TRX.INTERFACE_HEADER_ATTRIBUTE2
, TRX.INTERFACE_HEADER_ATTRIBUTE3
, TRX.INTERFACE_HEADER_ATTRIBUTE4
, TRX.INTERFACE_HEADER_ATTRIBUTE5
, TRX.INTERFACE_HEADER_ATTRIBUTE6
, TRX.INTERFACE_HEADER_ATTRIBUTE7
, TRX.INTERFACE_HEADER_ATTRIBUTE8
, TRX.INTERFACE_HEADER_ATTRIBUTE9
, TRX.INTERFACE_HEADER_ATTRIBUTE10
, TRX.INTERFACE_HEADER_ATTRIBUTE11
, TRX.INTERFACE_HEADER_ATTRIBUTE12
, TRX.INTERFACE_HEADER_ATTRIBUTE13
, TRX.INTERFACE_HEADER_ATTRIBUTE14
, TRX.INTERFACE_HEADER_ATTRIBUTE15
, TRX.GLOBAL_ATTRIBUTE_CATEGORY
, TRX.GLOBAL_ATTRIBUTE1
, TRX.GLOBAL_ATTRIBUTE2
, TRX.GLOBAL_ATTRIBUTE3
, TRX.GLOBAL_ATTRIBUTE4
, TRX.GLOBAL_ATTRIBUTE5
, TRX.GLOBAL_ATTRIBUTE6
, TRX.GLOBAL_ATTRIBUTE7
, TRX.GLOBAL_ATTRIBUTE8
, TRX.GLOBAL_ATTRIBUTE9
, TRX.GLOBAL_ATTRIBUTE10
, TRX.GLOBAL_ATTRIBUTE11
, TRX.GLOBAL_ATTRIBUTE12
, TRX.GLOBAL_ATTRIBUTE13
, TRX.GLOBAL_ATTRIBUTE14
, TRX.GLOBAL_ATTRIBUTE15
, TRX.GLOBAL_ATTRIBUTE16
, TRX.GLOBAL_ATTRIBUTE17
, TRX.GLOBAL_ATTRIBUTE18
, TRX.GLOBAL_ATTRIBUTE19
, TRX.GLOBAL_ATTRIBUTE20
, TRX.GLOBAL_ATTRIBUTE21
, TRX.GLOBAL_ATTRIBUTE22
, TRX.GLOBAL_ATTRIBUTE23
, TRX.GLOBAL_ATTRIBUTE24
, TRX.GLOBAL_ATTRIBUTE25
, TRX.GLOBAL_ATTRIBUTE26
, TRX.GLOBAL_ATTRIBUTE27
, TRX.GLOBAL_ATTRIBUTE28
, TRX.GLOBAL_ATTRIBUTE29
, TRX.GLOBAL_ATTRIBUTE30
FROM AR_PAYMENT_SCHEDULES PAY
, RA_CUSTOMER_TRX TRX
, RA_CUST_TRX_TYPES TRXTP
, AR_LOOKUPS LV
, HZ_CUST_SITE_USES SITE
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES SITE1
, HZ_CUST_ACCT_SITES ACCT_SITE1
, HZ_PARTY_SITES PARTY_SITE1
, HZ_LOCATIONS LOC1
WHERE PAY.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND TRX.CUST_TRX_TYPE_ID = TRXTP.CUST_TRX_TYPE_ID
AND TRX.ORG_ID = TRXTP.ORG_ID
AND TRX.BILL_TO_SITE_USE_ID = SITE.SITE_USE_ID(+)
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND LOC.LOCATION_ID(+) = PARTY_SITE.LOCATION_ID
AND TRX.SHIP_TO_SITE_USE_ID = SITE1.SITE_USE_ID(+)
AND SITE1.CUST_ACCT_SITE_ID = ACCT_SITE1.CUST_ACCT_SITE_ID(+)
AND ACCT_SITE1.PARTY_SITE_ID = PARTY_SITE1.PARTY_SITE_ID(+)
AND LOC1.LOCATION_ID(+) = PARTY_SITE1.LOCATION_ID
AND LV.LOOKUP_CODE = TRX.STATUS_TRX
AND LV.LOOKUP_TYPE = 'INVOICE_TRX_STATUS' GROUP BY TRX.CUSTOMER_TRX_ID
, TRX.TRX_NUMBER
, TRX.INVOICE_CURRENCY_CODE
, TRX.TRX_DATE
, TRX.STATUS_TRX
, LV.MEANING
, TRX.CUST_TRX_TYPE_ID
, TRXTP.NAME
, TRXTP.TYPE
, LOC.ADDRESS1 || ' ' || LOC. ADDRESS2 || ' ' || LOC.CITY || ' ' || LOC.STATE || ' ' || LOC.POSTAL_CODE || ' ' || LOC.COUNTRY
, LOC1.ADDRESS1 || ' ' || LOC1. ADDRESS2 || ' ' || LOC1.CITY || ' ' || LOC1.STATE || ' ' || LOC1.POSTAL_CODE || ' ' || LOC1.COUNTRY
, TRX.ATTRIBUTE_CATEGORY
, TRX.ATTRIBUTE1
, TRX.ATTRIBUTE2
, TRX.ATTRIBUTE3
, TRX.ATTRIBUTE4
, TRX.ATTRIBUTE5
, TRX.ATTRIBUTE6
, TRX.ATTRIBUTE7
, TRX.ATTRIBUTE8
, TRX.ATTRIBUTE9
, TRX.ATTRIBUTE10
, TRX.ATTRIBUTE11
, TRX.ATTRIBUTE12
, TRX.ATTRIBUTE13
, TRX.ATTRIBUTE14
, TRX.ATTRIBUTE15
, TRX.INTERFACE_HEADER_CONTEXT
, TRX.INTERFACE_HEADER_ATTRIBUTE1
, TRX.INTERFACE_HEADER_ATTRIBUTE2
, TRX.INTERFACE_HEADER_ATTRIBUTE3
, TRX.INTERFACE_HEADER_ATTRIBUTE4
, TRX.INTERFACE_HEADER_ATTRIBUTE5
, TRX.INTERFACE_HEADER_ATTRIBUTE6
, TRX.INTERFACE_HEADER_ATTRIBUTE7
, TRX.INTERFACE_HEADER_ATTRIBUTE8
, TRX.INTERFACE_HEADER_ATTRIBUTE9
, TRX.INTERFACE_HEADER_ATTRIBUTE10
, TRX.INTERFACE_HEADER_ATTRIBUTE11
, TRX.INTERFACE_HEADER_ATTRIBUTE12
, TRX.INTERFACE_HEADER_ATTRIBUTE13
, TRX.INTERFACE_HEADER_ATTRIBUTE14
, TRX.INTERFACE_HEADER_ATTRIBUTE15
, TRX.GLOBAL_ATTRIBUTE_CATEGORY
, TRX.GLOBAL_ATTRIBUTE1
, TRX.GLOBAL_ATTRIBUTE2
, TRX.GLOBAL_ATTRIBUTE3
, TRX.GLOBAL_ATTRIBUTE4
, TRX.GLOBAL_ATTRIBUTE5
, TRX.GLOBAL_ATTRIBUTE6
, TRX.GLOBAL_ATTRIBUTE7
, TRX.GLOBAL_ATTRIBUTE8
, TRX.GLOBAL_ATTRIBUTE9
, TRX.GLOBAL_ATTRIBUTE10
, TRX.GLOBAL_ATTRIBUTE11
, TRX.GLOBAL_ATTRIBUTE12
, TRX.GLOBAL_ATTRIBUTE13
, TRX.GLOBAL_ATTRIBUTE14
, TRX.GLOBAL_ATTRIBUTE15
, TRX.GLOBAL_ATTRIBUTE16
, TRX.GLOBAL_ATTRIBUTE17
, TRX.GLOBAL_ATTRIBUTE18
, TRX.GLOBAL_ATTRIBUTE19
, TRX.GLOBAL_ATTRIBUTE20
, TRX.GLOBAL_ATTRIBUTE21
, TRX.GLOBAL_ATTRIBUTE22
, TRX.GLOBAL_ATTRIBUTE23
, TRX.GLOBAL_ATTRIBUTE24
, TRX.GLOBAL_ATTRIBUTE25
, TRX.GLOBAL_ATTRIBUTE26
, TRX.GLOBAL_ATTRIBUTE27
, TRX.GLOBAL_ATTRIBUTE28
, TRX.GLOBAL_ATTRIBUTE29
, TRX.GLOBAL_ATTRIBUTE30