AR Receipts Awaiting Bank Clearance

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Receipts Awaiting Bank Clearance Report (XML)
Short Name: ARXAPRCL_XML
DB package: AR_ARXAPRCL_XMLP_PKG
SELECT   cr.currency_code                  currency_code,
                decode(:P_SORT_BY,
                  'MATURITY DATE', ps.due_date,
                  null)                                       ddatecol_A,
                decode(:P_SORT_BY,
                 'RECEIPT NUMBER', cr.receipt_number,
                 null)                                       dcol_A,
                CBA.bank_account_name   remit_acct,
                crh.trx_date                            remit_date,
                ps.due_date                           maturity_date,
                rmethod_lc.meaning             remit_method,
                rmethod.name                        pmt_method,
                cr.receipt_number                 receipt_number,
                cr.amount                               amount,
                cr.factor_discount_amount  bank_charges,
  				AR_ARXAPRCL_XMLP_PKG.COUNTER_p COUNTER,
  				AR_ARXAPRCL_XMLP_PKG.D_SUM_AMOUNT_CURRFormula
FROM    AR_CASH_RECEIPT_HISTORY   crh,
               AR_CASH_RECEIPTS                   cr,
               AR_PAYMENT_SCHEDULES        ps,
               AR_RECEIPT_METHODS              rmethod,
               AR_RECEIPT_CLASSES               rclass,
               AR_BATCHES                                 batch,
               CE_BANK_ACCT_USES                 racct,
               CE_BANK_ACCOUNTS                  CBA,
               AR_LOOKUPS                               rmethod_lc
WHERE crh.status                               = 'REMITTED'
AND     crh.current_record_flag       = 'Y'
AND     crh.postable_flag                  = 'Y'
AND     cr.cash_receipt_id                = crh.cash_receipt_id
AND     ps.cash_receipt_id (+)          = cr.cash_receipt_id
AND     racct.bank_acct_use_id(+) =cr.remit_bank_acct_use_id
AND     racct.bank_account_id = cba.bank_account_id (+)
AND       rmethod.receipt_method_id     = cr.receipt_method_id
AND       rclass.receipt_class_id             = rmethod.receipt_class_id
AND       rclass.clear_flag                        IN ('Y' , 'S')
AND       batch.batch_id (+)            = crh.batch_id  AND       rmethod_lc.lookup_code(+) = batch.remit_method_code
AND       rmethod_lc.lookup_type(+)  = 'REMITTANCE_METHOD'
AND       cr.reversal_date	IS NULL
&LP_REMIT_ACCOUNT
&LP_REMIT_METHOD
&LP_PMT_METHOD
&LP_MATURITY_DATE
&LP_REMIT_AMOUNT
&LP_CURRENCY
--ORDER BY cr.receipt_number,cr.cash_receipt_id
ORDER BY remit_date,cr.receipt_number,cr.cash_receipt_id
Parameter Name SQL text Validation
Ledger Id
 
LOV Oracle
Currency
 
LOV Oracle
Remittance Amount High
 
Number
Remittance Amount Low
 
Number
Maturity Date High
 
Date
Maturity Date Low
 
Date
Payment Method
 
LOV Oracle
Remittance Method
 
LOV Oracle
Bank Account Id
 
LOV Oracle
Order By
 
LOV Oracle
Ask a question