AR Billing and Receipt History

            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