AR Customer Open Balance Letter

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