AR Receipts Awaiting Remittance

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Receipts Awaiting Remittance Report (XML)
Short Name: ARXAPRRM_XML
DB package: AR_ARXAPRRM_XMLP_PKG
            SELECT  cr.currency_code                   currency_code,
                    status_lc.meaning                           status,
                    decode(:P_SORT_BY,
                                  'MATURITY DATE', ps.due_date,
                                  null)                                    ddatecol_rec,
                    decode(:P_SORT_BY,
                                  'RECEIPT NUMBER', cr.receipt_number,
                                  null)                                    dcol_rec,
                    decode(:P_SORT_BY,
                                  'REMITTANCE BANK', decode(cbranch.instrument_type,'BANKACCOUNT',cbranch.bank_name,'CREDITCARD',cbranch.card_issuer_name,'DEBITCARD',cbranch.card_issuer_name,null),
                                  null)                                    bank_name1,
                    CBA.bank_account_name             remit_acct,
                    decode(:RP_SUMMARIZE,
                          'YES', null,
                         decode(cbranch.instrument_type,'BANKACCOUNT',cbranch.bank_name,'CREDITCARD',cbranch.card_issuer_name,'DEBITCARD',cbranch.card_issuer_name,null))                 bank_name,
                    decode(:RP_SUMMARIZE,
                          'YES', null,
                         decode(cbranch.instrument_type,'BANKACCOUNT',cbranch.branch_name,null))   branch_name,
                    ps.due_date                                     maturity_date,
                   decode(:RP_SUMMARIZE,
                          'YES', null,
                          rmethod_lc.meaning)      remit_method,
                    rmethod.name                                  pmt_method,
                   decode(:RP_SUMMARIZE,
                          'YES', null,
                          cr.receipt_number)                    receipt_number,
                    &RP_SUM_COL_AMOUNT            amount,
                    &RP_SUM_COL_CHARGES          bank_charges,
                AR_ARXAPRRM_XMLP_PKG.COUNTER_p COUNTER,
                AR_ARXAPRRM_XMLP_PKG.D_SUM_AMOUNT_CURRFormula
            FROM    AR_CASH_RECEIPT_HISTORY         crh,
                           AR_CASH_RECEIPTS                         cr,
                           AR_PAYMENT_SCHEDULES             ps,
                           AR_BATCHES                                      batch,
                           AR_RECEIPT_METHODS                    rmethod,
                           AR_RECEIPT_CLASSES                     rclass,
                           CE_BANK_ACCT_USES                      racct,
                           CE_BANK_ACCOUNTS                      cba,
                           IBY_TRXN_EXTENSIONS_V                 extn,
                           IBY_FNDCPT_PAYER_ASSGN_INSTR_V        cbranch,
                           AR_LOOKUPS                                    status_lc,
                           AR_LOOKUPS                                    rmethod_lc
            WHERE   crh.status                          = 'CONFIRMED'
            AND     crh.current_record_flag     = 'Y'
            AND      cr.reversal_date IS NULL
            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     cba.bank_account_id (+) = racct.bank_account_id
            AND     extn.trxn_extension_id (+) = cr.payment_trxn_extension_id
            AND     cbranch.instrument_id(+)   = extn.instrument_id
            AND     rmethod.receipt_method_id = cr.receipt_method_id
            AND     batch.batch_id(+)         = cr.selected_remittance_batch_id
            AND     status_lc.lookup_code(+) = nvl(batch.batch_applied_status,
                                                                                'AVAILABLE_FOR_REMITT')
            AND     status_lc.lookup_type(+)  = 'ARXAPRRM_BATCH_APPLIED_STATUS'
            AND     rclass.receipt_class_id     = rmethod.receipt_class_id
            AND     rclass.remit_flag                = 'Y'
            AND     rmethod_lc.lookup_code(+) = rclass.remit_method_code
            AND     rmethod_lc.lookup_type(+)  = 'REMITTANCE_METHOD'
            &LP_STATUS
            &LP_REMIT_ACCOUNT
            &LP_REMIT_METHOD
            &LP_PMT_METHOD
            &LP_MATURITY_DATE
            &LP_REMIT_AMOUNT
            &LP_CURRENCY
            &RP_GROUP_BY
            --order by 12,13
            --order by 1,5,2,3,4,6,9,10,11,12,13,14
            /*order by
cr.currency_code,status,REMIT_ACCT,bank_name,BRANCH_NAME,MATURITY_DATE,REMIT_METHOD,PMT_METHOD,RECEIPT_NUMBER,AMOUNT,BANK_cHARGES*/
ORDER BY 1 ASC,5 ASC,2 ASC,3 ASC,4 ASC,6 ASC,9 ASC,10 ASC,11 ASC , 12 , 13
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 Name
 
LOV Oracle
Status
 
LOV Oracle
Summarize
 
LOV Oracle
Order By
 
LOV Oracle