AR Invoice Exception

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Lists all invoices not open receivable
Application: Receivables
Source: Invoice Exception Report (XML)
Short Name: RAXINX_XML
DB package: AR_RAXINX_XMLP_PKG
Run AR Invoice Exception and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT   decode(type.post_to_gl, 'Y', :Yes, :No)           C_POSTABLE,
               money.currency_code                                    C_CURRENCY_CODE,
               trx.trx_number                                              C_INVOICE_NUMBER,
               SUBSTR(arpt_sql_func_util.get_lookup_meaning('INV/CM',type.type),1,8)	          C_TYPE,
              substrb(party.party_name,1,50)                     C_NAME,
              cust.account_number                                     C_CUSTOMER_NUMBER,
              trx.trx_date                                                   C_INVOICE_DATE,
              dist.amount                                                    C_AMOUNT_IC,
              dist.acctd_amount                                          C_AMOUNT_FC,
              decode(upper(:lp_sortname),'CUSTOMER',party.party_name,
              'INVOICE_NUMBER',trx.trx_number,trx.trx_number )     C_SORT
FROM   	hz_cust_accounts_all 		cust,
	hz_parties 			party,
     	ra_cust_trx_types_all                               type,
	fnd_currencies			money,
	/*ar_xla_ctlgd_lines_v               dist,*/
	ra_cust_trx_line_gl_dist_all         dist,
	ra_customer_trx_all			trx,
                gl_code_combinations                                cc
WHERE 	trx.complete_flag = 'Y'
AND 	cust.cust_account_id = trx.bill_to_customer_id
AND	cust.party_id = party.party_id
AND 	trx.set_of_books_id = :set_of_books_id
AND           not exists ( select 'x'
		 from	ar_payment_schedules_all pay
		 where	pay.customer_trx_id = trx.customer_trx_id )
AND	trx.cust_trx_type_id = type.cust_trx_type_id
AND 	type.type in ('INV', 'DEP', 'GUAR', 'CM','DM')
AND 	trx.invoice_currency_code = money.currency_code
AND 	trx.cust_trx_type_id = type.cust_trx_type_id
AND          dist.customer_trx_id = trx.customer_trx_id
AND          dist.account_class = 'REC'
AND          dist.latest_rec_flag = 'Y'
AND          dist.gl_date  IS NULL
AND          dist.code_combination_id = cc.code_combination_id
&lp_start_trx_date2
&lp_end_trx_date2
&lp_start_currency
&lp_end_currency
&lp_start_trx
&lp_end_trx
&lp_start_trx_date
&lp_end_trx_date
&lp_bal_seg_low
&lp_bal_seg_high
&P_ORG_WHERE_CUST
&P_ORG_WHERE_DIST
&P_ORG_WHERE_TRX
&P_ORG_WHERE_TYPE
union all
SELECT  decode(type.post_to_gl,'Y',:Yes,:No)                                             C_POSTABLE,
money.currency_code                                                                                       C_CURRENCY_CODE,
trx.trx_number                                                                                                 C_INVOICE_NUMBER,
substr(arpt_sql_func_util.get_lookup_meaning('INV/CM',type.type) ,1,8)  C_TYPE,
substrb(party.party_name,1,50)                                                                       C_NAME,
cust.account_number                                                                                        C_CUSTOMER_NUMBER,
trx.trx_date                                                                                                       C_INVOICE_DATE,
dist.amount                                                                                                        C_AMOUNT_IC,
dist.acctd_amount                                                                                             C_AMOUNT_FC,
decode(upper(:lp_sortname),'CUSTOMER',party.party_name,
 'INVOICE_NUMBER',trx.trx_number,trx.trx_number)                                   C_SORT
from
hz_cust_accounts_all              cust,
hz_parties                               party,
ra_cust_trx_types_all             type,
fnd_currencies                        money,
/*ar_xla_ctlgd_lines_v                    dist,*/
ra_cust_trx_line_gl_dist_all        dist,
ra_customer_trx_all                 trx,
gl_code_combinations               cc
where  trx.complete_flag = 'Y'
AND  cust.cust_account_id = trx.bill_to_customer_id
AND cust.party_id = party.party_id
AND  trx.set_of_books_id = :Set_of_books_id
AND  not exists  (select 'x'
                             from ar_payment_schedules_all pay
                             where pay.customer_trx_id =  trx.customer_trx_id)
AND  trx.cust_trx_type_id = type.cust_trx_type_id
AND  type.type in('INV','DEP','GUAR','CM','DM','CM')
AND trx.invoice_currency_code =  money.currency_code
AND  trx.cust_trx_type_id  = type.cust_trx_type_id
AND  dist.customer_trx_id =  trx.customer_trx_id
AND  dist.account_class = 'REC'
AND   dist.latest_rec_flag  = 'Y'
AND  dist.gl_date  IS NOT NULL
AND dist.code_combination_id = cc.code_combination_id
&lp_start_gl_date
&lp_end_gl_date
&lp_start_currency
&lp_end_currency
&lp_start_trx
&lp_end_trx
&lp_start_trx_date
&lp_end_trx_date
&lp_bal_seg_low
&lp_bal_seg_high
&P_ORG_WHERE_CUST
&P_ORG_WHERE_DIST
&P_ORG_WHERE_TRX
&P_ORG_WHERE_TYPE
/*ORDER BY
C_POSTABLE,
C_CURRENCY_CODE,
C_TYPE,
C_SORT,
C_INVOICE_NUMBER*/
ORDER BY C_POSTABLE,C_CURRENCY_CODE,C_SORT
Parameter Name SQL text Validation
Reporting Level 
 
 LOV Oracle
Reporting Context 
 
 LOV Oracle
Ledger Currency 
 
 Char
Company Segment Low 
 
 Char
Company Segment High 
 
 Char
GL Date From 
 
 Date
GL Date To 
 
 Date
Order By 
 
 LOV Oracle
Entered Currency Low 
 
 LOV Oracle
Entered Currency High 
 
 LOV Oracle
Transaction Date From 
 
 Date
Transaction Date To 
 
 Date
Transaction Type Low 
 
 LOV Oracle
Transaction Type High 
 
 LOV Oracle
Download
Related Blitz Reports
GL Account Analysis AR Transactions and Lines HACTL AR Transaction Listings ECC Receivables, AR Closing, SQL2 AR Balance of Advance Received in Selected Currency AR Deposit Balance Report - Japan AR Open Balances Revaluation GL Account Analysis (Distributions) XXSJ Report VAT Register
Blitz Report™

Blitz Report™ provides multiple benefits: