AR Customer Open Balance Letter

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Customer Open Balance Letter (XML)
Short Name: ARXCOBLX_XML
DB package: AR_ARXCOBLX_XMLP_PKG
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
Reference Number
 
Include Uncleared Receipts
 
LOV Oracle
Include Unapplied Receipts
 
LOV Oracle
Include On Account Receipts
 
LOV Oracle
Include On Account Credits
 
LOV Oracle
Minimum Open Balance
 
Number
Minimum Invoice Balance
 
Number
Currency
 
LOV Oracle
Customer Number To
 
LOV Oracle
Customer Number From
 
LOV Oracle
Customer Name To
 
LOV Oracle
Customer Name From
 
LOV Oracle
As of date
 
Date