AR Billing and Receipt History

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Billing and Receipt History (XML)
Short Name: ARXBPH_XML
DB package: AR_ARXBPH_XMLP_PKG
            select
                     ps.customer_id customer_id,
                     substrb(party.party_name,1,50)  customer_name,
                     cust_acct.account_number customer_number,
                      arpt_sql_func_util.get_trx_type_details(ct.cust_trx_type_id,'NAME')   name,
                     ps.customer_trx_id customer_trx_id,
                     ps.trx_number  Invoice_No,
                     ps.trx_number  Full_Invoice_No,
                     ct.purchase_order  purchase_order,
                     ps.due_date due_date,
                     ps.amount_due_original Invoice_amt,
                     ps.amount_due_remaining Balance_amt,
                     ps.trx_date trx_date_main,
                     ps.payment_schedule_id payment_schedule_id,
                     ps.invoice_currency_code Currency_Code,
                     ct.doc_sequence_value C_DOCUMENT_NUMBER,
                     &lp_query_show_bill cons_bill_number,
                     --added as fix
                     AR_ARXBPH_XMLP_PKG.D_INVOICE_AMOUNTFormula(substrb(party.party_name,1,50))
            from
                     ar_payment_schedules ps,
                     ra_customer_trx ct,
                     hz_cust_accounts cust_acct,
                     hz_parties party,
                     &lp_table_show_bill
                     hz_customer_profiles cp_cust,
                     hz_customer_profiles cp_site
            where   ps.customer_trx_id      = ct.customer_trx_id
            and     ct.previous_customer_trx_id is null
            and  cust_acct.party_id = party.party_id
            &P_WHERE_1
            &lp_customer_num_low
            &lp_customer_num_high
            &lp_invoice_amount_low
            &lp_invoice_amount_high
            &lp_invoice_number_low
            &lp_invoice_number_high
            &P_WHERE_11
            &P_WHERE_12
            &lp_balance_due_low
            &lp_balance_due_high
            &lp_trx_date_low
            &lp_trx_date_high
            &P_WHERE_2
            &lp_where_show_bill
            and      cp_cust.site_use_id    is null
            and      ct.bill_to_site_use_id = cp_site.site_use_id(+)
            and      nvl( nvl(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_site.account_status),
                                    ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_cust.account_status)),
                              decode(:P_IN_ACCOUNT_STATUS_LOW_1,
                                          'All', :P_IN_ACCOUNT_STATUS_LOW_1,
                                          null))
                        between decode(:P_IN_ACCOUNT_STATUS_LOW_1,
                                     'All',
                                      nvl (nvl(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_site.account_status),
                                                   ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_cust.account_status)),
                                             decode(:P_IN_ACCOUNT_STATUS_LOW_1,
                                                          'All', :P_IN_ACCOUNT_STATUS_LOW_1,null)),
                                                         :P_IN_ACCOUNT_STATUS_LOW_1)
                       and         decode(:P_IN_ACCOUNT_STATUS_high_1,
                                       'All',
                                      nvl (nvl(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_site.account_status),
                                                   ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_cust.account_status)),
                                             decode(:P_IN_ACCOUNT_STATUS_high_1,
                                                          'All', :P_IN_ACCOUNT_STATUS_high_1,null)),
                                                         :P_IN_ACCOUNT_STATUS_high_1)
            UNION
            select
                     cr.pay_from_customer customer_id,
                     substrb(party.party_name,1,50)  customer_name,
                     cust_acct.account_number customer_number,
                     l_pay.meaning,          TO_NUMBER('') customer_trx_id,          cr.receipt_number  Invoice_No,
                     cr.receipt_number || '-' || cr.cash_receipt_id  Full_Invoice_No,
                     '' purchase_order,
                     TO_DATE(NULL) due_date,          cr.amount Invoice_amt,          TO_NUMBER('') Balance_amt,            cr.receipt_date trx_date_main,          TO_NUMBER('') payment_schedule_id,           cr.currency_code Currency_Code,
                     cr.doc_sequence_value  C_DOCUMENT_NUMBER,
                     to_char(NULL) cons_bill_number,
                     --ADDED AS FIX
                     AR_ARXBPH_XMLP_PKG.D_INVOICE_AMOUNTFormula(substrb(party.party_name,1,50))
            from
                ar_cash_receipts cr,
                    hz_cust_accounts cust_acct,
                    hz_parties party,
                    hz_customer_profiles cp_cust,
                    hz_customer_profiles cp_site,
                    ar_lookups l_pay
            WHERE
            1 = DECODE(:p_in_invoice_number_low || :p_in_invoice_number_high ||
                   :p_in_invoice_amount_low || :p_in_invoice_amount_high ||
                   :p_in_balance_due_low    || :p_in_balance_due_high,
                   NULL, 1,
                     2)
            AND     cr.status IN ('APP','UNAPP')
            AND     0 <
                (SELECT NVL(sum(amount_applied),0)
                 FROM   ar_receivable_applications app
                 WHERE  cr.cash_receipt_id = app.cash_receipt_id
                 AND    app.status = 'ACC')
            and     cr.pay_from_customer      = cust_acct.cust_account_id
            and     cust_acct.party_id =party.party_id
            and             cr.cash_receipt_id <= nvl(:p_max_id, 999999999999999)
            &lp_customer_num_low
            &lp_customer_num_high
            &P_WHERE_11
            &P_WHERE_12
            &lp_r_trx_date_low
            &lp_r_trx_date_high
            &P_WHERE_2
            and      cp_cust.site_use_id    is null
            and      cr.customer_site_use_id = cp_site.site_use_id(+)
            and     nvl( nvl(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_site.account_status),
                             ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_cust.account_status)),
                         decode(:P_IN_ACCOUNT_STATUS_LOW_1,
                                  'All', :P_IN_ACCOUNT_STATUS_LOW_1,
                                  null))
                     between decode(:P_IN_ACCOUNT_STATUS_LOW_1,
                                    'All',
                                      nvl (nvl(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_site.account_status),
                                               ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_cust.account_status)),
                                       decode(:P_IN_ACCOUNT_STATUS_LOW_1,
                                                'All', :P_IN_ACCOUNT_STATUS_LOW_1,null)),
                                                :P_IN_ACCOUNT_STATUS_LOW_1)
                       and   decode(:P_IN_ACCOUNT_STATUS_high_1,
                                       'All',
                                      nvl (nvl(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_site.account_status),
                                               ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_cust.account_status)),
                                       decode(:P_IN_ACCOUNT_STATUS_high_1,
                                                'All', :P_IN_ACCOUNT_STATUS_high_1,null)),
                                                :P_IN_ACCOUNT_STATUS_high_1)
                        and  l_pay.lookup_type = 'PAYMENT_CATEGORY_TYPE'
                        and  l_pay.lookup_code = cr.type
            order by 2,         3,         1,         14,         6,         5,         9
Parameter NameSQL textValidation
Ledger
 
Number
Account Status High
 
LOV Oracle
Account Status Low
 
LOV Oracle
Transaction Date High
 
Date
Transaction Date Low
 
Date
Balance Due High
 
Number
Balance Due Low
 
Number
Invoice Amount High
 
Number
Invoice Amount Low
 
Number
Invoice Number High
 
LOV Oracle
Invoice Number Low
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle