AR Automatic Receipts Awaiting Confirmation

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Automatic Receipts Awaiting Confirmation (XML)
Short Name: ARXAPRCF_XML
DB package: AR_ARXAPRCF_XMLP_PKG
SELECT
                   decode(:p_sort_by,
                                 'REMITT ACCOUNT', cba.bank_account_name,
                                  null)  dcol_A,
                   decode(:p_sort_by,
                                 'MATURITY DATE', ps.due_date,
                                 'REMITT ACCOUNT', ps.due_date,
                                  null)                               dcoldate_A,
                   decode(:p_sort_by,
                             'CUSTOMER', SUBSTRB(PARTY.PARTY_NAME,1,50),
                             'PHONETIC',DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION',
                        PARTY.ORGANIZATION_NAME_PHONETIC,NULL),  &P_SORT)
                    dcolcust_A,
                   cr.currency_code              curr_code,
                     SUBSTRB(PARTY.PARTY_NAME,1,50)       cust_name,
                   cust.ACCOUNT_NUMBER     cust_num,
                   site.location                        location,
                   cba.bank_account_name acct_name,
                   ps.due_date                        due_date,
                   cr.receipt_number              rcpt_num,
                   cr.receipt_date                   rcpt_date,
                   cr.amount                            amount
            FROM   ar_cash_receipts       cr,
                   ar_cash_receipt_history  crh,
                   ar_payment_schedules    ps,
                   HZ_CUST_ACCOUNTS        cust,
                   HZ_PARTIES                    PARTY,
                   HZ_CUST_site_uses                      site,
                   ce_bank_acct_uses          acct,
                   ce_bank_accounts            cba
            WHERE crh.cash_receipt_id         = cr.cash_receipt_id    AND
                          crh.status                          = 'APPROVED'             AND
                          crh.current_record_flag  = 'Y'
            AND    ps.cash_receipt_id          = cr.cash_receipt_id
            AND    cust.CUST_ACCOUNT_ID             = cr.pay_from_customer
            AND    CUST.PARTY_ID  = PARTY.PARTY_ID
            AND    site.site_use_id (+)          = cr.customer_site_use_id
            AND   acct.bank_acct_use_id(+) = cr.remit_bank_acct_use_id
            AND   acct.bank_account_id  = cba.bank_account_id (+)
            &LP_CUST_NAME
            &LP_CUST_NUM
            &LP_REMIT_ACCOUNT
            &LP_MATURITY_DATE
            &LP_CURRENCY
            ORDER BY curr_code, dcol_A, dcoldate_A, dcolcust_A, cust_num, location, acct_name,
			due_date, rcpt_num, rcpt_date, amount, 10, 12
Parameter Name SQL text Validation
Ledger Id
 
LOV Oracle
Currency
 
LOV Oracle
Maturity Date High
 
Date
Maturity Date Low
 
Date
Remittance Account
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
CustomerName Low
 
LOV Oracle
Order By
 
LOV Oracle