AR Automatic Clearing for Receipts

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Automatic Clearing for Receipts (XML)
Short Name: ARZACS_XML
DB package: AR_ARZACS_XMLP_PKG
select
        ba.bank_acct_use_id     bank_account_id,
        bb.bank_name            bank_name,
        bb.branch_number                bank_number,
        cba.bank_account_num    bank_account_number,
        cba.bank_account_name   bank_account_name,
        cba.currency_code       bank_account_currency,
        l1.meaning||
                decode( crh.status,
                        'CLEARED', decode( crh.factor_flag,
                                        'Y', ' ' || l2.meaning,
                                        ''),
                        '') status,
         cr.receipt_number                                           receipt_number,
         to_char(ps.due_date,'dd-mm-yyyy')              maturity_date,
         rm.name                                                        payment_method,
         cr.currency_code                                                         currency_code,
        crh.amount                                                  amount,
        crh.acctd_amount                                            func_amount,
	AR_ARZACS_XMLP_PKG.C_REC1_p C_REC1
from
        ar_lookups l1,
        ar_lookups l2,
        ar_payment_schedules ps,
        ar_cash_receipts cr,
        ar_cash_receipt_history crh,
        ar_receipt_methods rm,
        ce_bank_accounts cba,
        ce_bank_acct_uses ba,
        ce_bank_branches_v bb
 where
   &p_auto_clear_receipts
    crh.cash_receipt_id = cr.cash_receipt_id
   and  cr.cash_receipt_id = ps.cash_receipt_id(+)
   and  cr.receipt_method_id = rm.receipt_method_id
   and  cr.remit_bank_acct_use_id = ba.bank_acct_use_id (+)
   and  ba.bank_account_id = cba.bank_account_id (+)
   and  bb.bank_party_id = cba.bank_branch_id
   and  crh.status = l1.lookup_code
   and  l1.lookup_type = 'RECEIPT_CREATION_STATUS'
   and  l2.lookup_type = 'REMITTANCE_METHOD'
   and  l2.lookup_code = 'FACTORING'
   and  crh.cash_receipt_history_id  > :MAX_CRH_ID
  order by
        bb.bank_name,
        cba.bank_account_num,
        crh.status,
        decode(crh.factor_flag, 'Y', 1, 'N', 2),
        cr.currency_code,
        cr.receipt_number
Parameter Name SQL text Validation
Run Bills Receivable Report
 
LOV Oracle
Customer Number To
 
LOV Oracle
Customer Number From
 
LOV Oracle
Customer Name To
 
LOV Oracle
Customer Name From
 
LOV Oracle
Receipt Number To
 
LOV Oracle
Receipt Number From
 
LOV Oracle
Exchange Rate Type
 
LOV Oracle
Payment Method
 
LOV Oracle
Remittance Bank Account
 
LOV Oracle
GL Date
 
Date
Clear Date
 
Date
Eliminate Bank Risk
 
LOV Oracle
Clear Discounted Receipts
 
LOV Oracle
Clear Remitted Receipts
 
LOV Oracle