AR Customer Open Balance Letter

Description
Categories: BI Publisher
Application: Receivables
Source: Customer Open Balance Letter (XML)
Short Name: ARXCOBLX_XML
DB package: AR_ARXCOBLX_XMLP_PKG
Run AR Customer Open Balance Letter and other Oracle EBS reports with Blitz Report™ on our demo environment
select
                                         organization_name,
                                         as_of_date,
                                         customer_name,
                                         customer_number,
                                         tax_reference_num,
                                         address_line1,
                                         address_line2,
                                         address_line3,
                                         address_line4,
                                         city,
                                         state,
                                         zip,
                                         country,
                                     decode( trans_type,'INV','Standard','DM',
                                         'Debit Memo','CB','Chargeback','DEP','Deposit') trans_type,
                                           trx_number,
                                         transaction_date,
                                         trx_currency_code,
                                         nvl(trans_amount,0) trans_amount,
                                        nvl( trans_amount_remaining,0) trans_amount_remaining,
                                        nvl( receipt_amount,0) receipt_amount,
                                         nvl(adjustment_amount,0) adjustment_amount,
                                         nvl(earned_discount_amount,0) earned_discount_amount,
                                         nvl(unearned_discount_amount,0) unearned_discount_amount,
                                        nvl(invoice_credit_amount,0) invoice_credit_amount,
                                         nvl(bank_charge,0) bank_charge,
                                         nvl(on_account_credit_amount,0) on_account_credit_amount,
                                        nvl( on_account_receipts,0) on_account_receipts,
                                         nvl(unapplied_receipts,0) unapplied_receipts,
	AR_ARXCOBLX_XMLP_PKG.c_as_of_date_displayformula() C_as_of_date_display,
	AR_ARXCOBLX_XMLP_PKG.cf_totalformula(:C_inv_open_balance, :CF_credits_and_receipts) CF_Total,
	AR_ARXCOBLX_XMLP_PKG.cf_credits_and_receiptsformula(:C_unapplied_receipts, :C_on_account_receipts, :C_on_account_credits) CF_credits_and_receipts
from ar_customer_balance_itf
                                    Where
                                    request_id =:p_conc_request_id
                                    --order by transaction_date,  trx_number
order by 3,12,9,8,2,5,11,7,6,13,10,4,1,17,15,14,16,18,19,20,21,22,23,24,25,26,27,28
Parameter Name SQL text Validation
As of date
 
Date
Customer Name From
 
LOV Oracle
Customer Name To
 
LOV Oracle
Customer Number From
 
LOV Oracle
Customer Number To
 
LOV Oracle
Currency
 
LOV Oracle
Minimum Invoice Balance
 
Number
Minimum Open Balance
 
Number
Include On Account Credits
 
LOV Oracle
Include On Account Receipts
 
LOV Oracle
Include Unapplied Receipts
 
LOV Oracle
Include Uncleared Receipts
 
LOV Oracle
Reference Number
 
Char
Blitz Report™