AR Invoice Exception

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Invoice Exception Report (XML)
Short Name: RAXINX_XML
DB package: AR_RAXINX_XMLP_PKG
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_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_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
Chart of Accounts
 
Transaction Type High
 
LOV Oracle
Transaction Type Low
 
LOV Oracle
Transaction Date To
 
Date
Transaction Date From
 
Date
Entered Currency High
 
LOV Oracle
Entered Currency Low
 
LOV Oracle
Order By
 
LOV Oracle
GL Date To
 
Date
GL Date From
 
Date
Company Segment High
 
Company Segment Low
 
Ledger Currency
 
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle