AR Notes Receivable

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Notes Receivable Report (XML)
Short Name: ARXNROPN_XML
DB package: AR_ARXNROPN_XMLP_PKG
            select  cr.currency_code        receipt_currency1,
                bb.bank_name        remit_bank_name1,
                decode(:RP_SORT_BY_PHONETICS, 'Y', bb.bank_name_alt, bb.bank_name) remit_bank_name1_sort,
                cba.bank_account_num        remit_bank_account1,
                SUBSTRB(PARTY.PARTY_NAME,1,50)  customer_name1,
                decode(:RP_SORT_BY_PHONETICS, 'Y', PARTY.ORGANIZATION_NAME_PHONETIC,         SUBSTRB(PARTY.PARTY_NAME,1,50)) customer_name1_sort,
                site.location       customer_site1,
                cr.issue_date       issue_date1,
                ps.due_date     maturity_date1,
                cr.receipt_number       note_number1,
                cr.amount           amount1,
                ecr.receipt_number      exchanged_note_number1,
                lk.meaning          status1
            from    ar_cash_receipts        cr,
                ar_cash_receipts        ecr,
                ar_receipt_methods      rm,
                ar_receipt_classes      rc,
                ar_cash_receipt_history crh,
                ar_receivable_applications  ra,
                ar_payment_schedules    ps,
                ar_payment_schedules    eps,
                            ce_bank_accounts                    cba,
                ce_bank_acct_uses       ba,
                ce_bank_branches_v      bb,
                HZ_CUST_ACCOUNTS    cust,
                HZ_PARTIES      PARTY,
                HZ_CUST_site_uses       site,
                ar_lookups      lk
            where   :P_ORDER_BY='REMITTANCE_BANK'
            and cr.receipt_method_id = rm.receipt_method_id
            and rm.receipt_class_id = rc.receipt_class_id
            and rc.notes_receivable = 'Y'
            and cr.reversal_date is NULL
            &LP_CURRENCY_CODE
            and cr.cash_receipt_id = ps.cash_receipt_id (+)
            &LP_START_DATE
            &LP_END_DATE
            and cr.cash_receipt_id = ra.cash_receipt_id
            and ra.applied_payment_schedule_id = eps.payment_schedule_id (+)
            and (ra.receivable_application_id,cr.cash_receipt_id) in
                (select max(ra2.receivable_application_id), cr2.cash_receipt_id
                from    ar_cash_receipts        cr2,
                    ar_receivable_applications  ra2,
                    ar_payment_schedules    ps2
                where   cr2.cash_receipt_id = ra2.cash_receipt_id
                and ra2.applied_payment_schedule_id = ps2.payment_schedule_id (+)
                and (nvl(ps2.reversed_cash_receipt_id,0),cr2.cash_receipt_id) in
                    (select max(nvl(ps3.reversed_cash_receipt_id,0)), cr3.cash_receipt_id
                    from    ar_cash_receipts        cr3,
                        ar_receivable_applications  ra3,
                        ar_payment_schedules    ps3
                    where   cr3.cash_receipt_id = ra3.cash_receipt_id
                    and ra3.applied_payment_schedule_id = ps3.payment_schedule_id (+)
                    group by cr3.cash_receipt_id)
                group by cr2.cash_receipt_id)
            and eps.reversed_cash_receipt_id  = ecr.cash_receipt_id (+)
            and cr.cash_receipt_id = crh.cash_receipt_id
            and crh.current_record_flag = 'Y'
            and crh.status in ('CONFIRMED', 'REMITTED', 'CLEARED', 'RISK_ELIMINATED')
            and lk.lookup_type = decode(crh.status,
                         'CONFIRMED', 'AR_NOTE_STATUS',
                         'REMITTED', 'RECEIPT_CREATION_STATUS',
                         'CLEARED', decode(crh.factor_flag, 'N', 'AR_NOTE_STATUS', 'RECEIPT_CREATION_STATUS'),
                         'RISK_ELIMINATED', 'AR_NOTE_STATUS')
            and lk.lookup_code  = decode(crh.status,
                         'CONFIRMED', decode(eps.reversed_cash_receipt_id, NULL, 'OPEN', 'EXCHANGE'),
                         'REMITTED', 'REMITTED',
                         'CLEARED', decode(crh.factor_flag, 'N', 'MATURED', 'REMITTED'),
                         'RISK_ELIMINATED', 'MATURED')
            &LP_NOTE_STATUS
            &LP_BANK_ACCOUNT_ID
            and cr.remit_bank_acct_use_id = ba.bank_acct_use_id (+)
            and           ba.bank_account_id = cba.bank_account_id (+)
            and     cba.bank_branch_id = bb.branch_party_id (+)
            &LP_REMITTANCE_BANK
            and cr.pay_from_customer = cust.CUST_ACCOUNT_ID (+)
            AND     CUST.PARTY_ID  = PARTY.PARTY_ID(+)
            &LP_CUSTOMER_NAME_LOW
            &LP_CUSTOMER_NAME_HIGH
            &LP_CUSTOMER_NUMBER_LOW
            &LP_CUSTOMER_NUMBER_HIGH
            and  cr.customer_site_use_id = site.site_use_id (+)
            order by    receipt_currency1,remit_bank_name1_sort,remit_bank_account1,maturity_date1,decode(:RP_SORT_BY_PHONETICS, 'Y', PARTY.ORGANIZATION_NAME_PHONETIC, PARTY.PARTY_NAME),
                site.location,
                cr.receipt_number
Parameter Name SQL text Validation
Ledger
 
LOV Oracle
Notes Receivable Status
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Remittance Bank Accoun
 
LOV Oracle
Remittance Bank
 
LOV Oracle
End Maturity Date
 
Date
Start Maturity Date
 
Date
Sort Option
 
LOV Oracle
Currency Code
 
LOV Oracle