AR Reversed Notes Receivable

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Reversed Notes Receivable Report (XML)
Short Name: ARXNRREV_XML
DB package: AR_ARXNRREV_XMLP_PKG
select :CP_order_by                         order_by_cu,
           CR.receipt_number                note_number_cu,
           CR.amount                              note_amount_cu,
           CR.currency_code                  currency_code_cu,
           CR.issuer_name                     issuer_name_cu,
           CR.issue_date                        issue_date_cu,
           CR.reversal_date                   reversal_date_cu,
           PS.due_date                           maturity_date_cu,
           SUBSTRB(PARTY.PARTY_NAME,1,50)   cust_name_cu,
           CU.ACCOUNT_NUMBER             cust_num_cu,
           SU.location                             site_name_cu,
           CBA.bank_account_name         bank_account_cu,
           BB.bank_name                        remittance_bank_cu,
           BBI.bank_name                       issuer_bank_name_cu,
           PSX.trx_number                     debit_memo_cu,
           DECODE(CRX.reversal_date,
                           NULL, CRX.receipt_number,
                           ' ')                             exchange_note_cu,
           LK.meaning                              status_cu,
          DECODE(:RP_SORT_BY_PHONETICS, 
                          'Y',  PARTY.ORGANIZATION_NAME_PHONETIC,
                                  SUBSTRB(PARTY.PARTY_NAME,1,50))     customer_name_sort_cu,
           DECODE(:RP_SORT_BY_PHONETICS,
                           'Y',  BB.bank_name_alt,
                                   BB.bank_name)            bank_name_sort_cu
from 
           ar_cash_receipts                 CR,
           ar_cash_receipts                 CRX,
           ar_cash_receipt_history     RH,
           ar_receipt_methods            RM,
            ar_receipt_classes             RC,
           ce_bank_acct_uses            BA,
           ce_bank_accounts               CBA,
           ce_bank_branches_v              BB,
           ce_bank_branches_v              BBI,
           ar_payment_schedules       PS,
           ar_payment_schedules       PSX,
           HZ_CUST_ACCOUNTS       CU,
           HZ_PARTIES 		    PARTY,
           HZ_CUST_site_uses                        SU,
           ar_receivable_applications  RAX,
           ar_lookups                            LK
WHERE
            :P_ORDER_BY = 'CUSTOMER'
AND    CR.reversal_date is not NULL
AND    CR.reversal_date   >=
                             nvl(:P_START_REVERSAL_DATE,  CR.reversal_date)
AND    CR.reversal_date  <= 
                             nvl(:P_END_REVERSAL_DATE,  CR.reversal_date)
AND    CR.currency_code             =
                             nvl(:P_CURRENCY_CODE,  CR.currency_code)
AND     RH.cash_receipt_id         =   CR.cash_receipt_id
AND    RH.current_record_flag    =   'Y'
AND     RM.receipt_method_id    =   CR.receipt_method_id
AND    RC.receipt_class_id         =   RM.receipt_class_id
AND    nvl(RC.notes_receivable,'N') = 'Y'
AND     BA.bank_acct_use_id              =  CR.remit_bank_acct_use_id
AND    BA.bank_account_id          = CBA.bank_account_id 
AND     BA.bank_acct_use_id              =  
                             nvl(:P_BANK_ACCOUNT,  BA.bank_acct_use_id)
AND    BB.branch_party_id                 =  CBA.bank_branch_id
AND    BB.bank_name                  =
                             nvl(:P_REMITTANCE_BANK,  BB.bank_name)
AND    BBI.branch_party_id(+)           =  CR.issuer_bank_branch_id
AND    PS.cash_receipt_id                =  CR.cash_receipt_id
AND    PS.due_date          >=
                             nvl(:P_START_MATURITY_DATE,  PS.due_date)
AND   PS.due_date           <=
                             nvl(:P_END_MATURITY_DATE,  PS.due_date)
AND    CU.CUST_ACCOUNT_ID (+)    =   CR.pay_from_customer
AND     CU.PARTY_ID      = PARTY.PARTY_ID(+)
AND    CU.ACCOUNT_NUMBER              =
                             nvl(:P_CUSTOMER_NUMBER,  CU.ACCOUNT_NUMBER)
AND    SU.site_use_id(+)                  =  CR.customer_site_use_id
AND    PSX.reversed_cash_receipt_id(+)  =  PS.cash_receipt_id
AND   nvl(PSX.payment_schedule_id, -1) in
            (select nvl(max(payment_schedule_id),  -1)
              from ar_payment_schedules PSS
              where   PSS.reversed_cash_receipt_id = CR.cash_receipt_id
               and      PSS.class   =  'DM')
AND   RAX.applied_payment_schedule_id (+)  =   PSX.payment_schedule_id
AND    nvl(RAX.receivable_application_id,  -1) in
                   (select  nvl(max(RAXX.receivable_application_id), -1)
                     from ar_receivable_applications  RAXX
                     where   RAXX.applied_payment_schedule_id   =  RAX.applied_payment_schedule_id
                      and      RAXX.status = 'APP')
AND    CRX.cash_receipt_id(+)                  =   RAX.cash_receipt_id
AND    DECODE(:P_NON_EXCHANGED,
                            'Y',   DECODE(PSX.trx_number,
                                                     NULL,  'N',
                                                     DECODE(CRX.receipt_number,
                                                                    NULL,  'Y',
                                                                     DECODE(CRX.reversal_date,
                                                                                      NULL,  'N',
                                                                                      'Y'))),
                            'Y')                =               'Y'
AND   LK.lookup_code             =
           DECODE(PSX.trx_number,
           NULL,  'RETURN',
                          DECODE(CR.reversal_date-PS.due_date,
                                          0,  'RETURN',
                           DECODE((CR.reversal_date-PS.due_date+1)/abs(CR.reversal_date-PS.due_date+1),
                                            -1,  'RETURN',
                                                                       DECODE(RH.factor_flag,
                                                                                       'N', 'DELINQUENT',
                                                                                       'REPURCHASE'))))
AND   LK.lookup_type             =    'AR_NOTE_STATUS'
and     LK.lookup_code            =   
                                      nvl(:P_NOTE_STATUS,  LK.lookup_code)
order by
CR.reversal_date,
  DECODE(:RP_SORT_BY_PHONETICS,
                'Y',  BB.bank_name_alt,
                BB.bank_name)
Parameter Name SQL text Validation
Ledger
 
LOV Oracle
Report Non-Exchanged Notes
 
LOV Oracle
Note Receivable Status
 
LOV Oracle
Customer Number
 
LOV Oracle
Customer Name
 
LOV Oracle
Remittance Bank Account
 
LOV Oracle
Remittance Bank
 
LOV Oracle
End Reversal Date
 
Date
Start Reversal Date
 
Date
End Maturity Date
 
Date
Start Maturity Date
 
Date
Sort Option
 
LOV Oracle
Currency Code
 
LOV Oracle
Ask a question