AR Invoice Exception
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
Description: Lists all invoices not open receivable
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_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 |