AR Transactions Awaiting Consolidation

select
           decode (:P_SORT_BY,
                          'DUE DATE', null,
                          'INVOICE NUMBER', trx.trx_number,
                          null) dcol_A,
           decode(:P_SORT_BY,
                         'DUE DATE', pays.due_date,
                        'INVOICE NUMBER', pays.due_date,
                         null ) ddatecol_A,
           decode(:P_SORT_BY,
                         'DUE DATE', decode(:RP_SUMMARIZE,
                                                             'YES', null,
                                                             trx.trx_number),
                         null ) dcol_B,
           trx.invoice_currency_code  currency_code,
           lc.meaning                             status,
           lc.lookup_code                     status_code,
           pmt.name                                method,
           decode(:RP_SUMMARIZE,
                         'YES', null,
                         SUBSTRB(PARTY.PARTY_NAME,1,50))           cust_name,
           decode(:RP_SUMMARIZE,
                         'YES', null,
                         cust.ACCOUNT_NUMBER)         cust_num,
           decode(:RP_SUMMARIZE,
                         'YES', null,
                         su.location)                              location,
           decode(:RP_SUMMARIZE,
                         'YES', null,
                         trx.trx_number)                        inv_num,
           decode(:RP_SUMMARIZE,
                         'YES', null,
                         trxtype.name)                           inv_type,
           decode(:RP_SUMMARIZE,
                         'YES', null,
                         trx.trx_date)                              inv_date,
           pays.due_date                        due_date,
           decode(:RP_SUMMARIZE,
                         'YES', null,
                         batch.name)                              batch,
         &LP_SUM_COLUMN                             amount_due,
         AR_ARXAPIPM_XMLP_PKG.D_SUM_AMOUNT_DUE_CURRFormula
from   ar_batches                           batch,
           ar_receipt_methods            pmt,
           ar_receipt_classes              class,
           ar_payment_schedules       pays,
           ar_cash_receipt_history     crh,
           ar_receivable_applications ra,
           ra_customer_trx                   trx,
           ra_cust_trx_types                trxtype,
           HZ_CUST_ACCOUNTS        cust,
          HZ_PARTIES 		PARTY,
           ar_lookups                            lc,
           HZ_CUST_site_uses                         su
where trx.set_of_books_id = :P_SET_OF_BOOKS_ID
and    pays.selected_for_receipt_batch_id = batch.batch_id (+)
and    trx.customer_trx_id                  = pays.customer_trx_id
and    trx.receipt_method_id                = pmt.receipt_method_id
and    class.receipt_class_id               = pmt.receipt_class_id
and    class.creation_method_code  in ('AUTOMATIC','BR')
and    trx.paying_customer_id             = cust.CUST_ACCOUNT_ID(+)
AND   CUST.PARTY_ID                   =  PARTY.PARTY_ID(+)
and    trx.paying_site_use_id              = su.site_use_id (+)
and    trxtype.cust_trx_type_id             = trx.cust_trx_type_id
and    pays.status = 'OP'
and    pays.payment_schedule_id        = ra.applied_payment_schedule_id(+)
and    ra.confirmed_flag(+)                    = 'N'
and    ra.cash_receipt_id                       = crh.cash_receipt_id(+)
and    nvl(crh.batch_id, -2) = decode(crh.batch_id , null, -2,
                                                                 batch.batch_id)
and    nvl(batch.batch_applied_status,
                'AVAILABLE_FOR_RECEIPT')   =  lc.lookup_code(+)
and    lc.lookup_type(+) = 'ARXAPIPM_BATCH_APPLIED_STATUS'
&LP_STATUS
&LP_INV_DATE
&LP_DUE_DATE
&LP_PMT_METHOD
&LP_CUST_NAME
&LP_CUST_NUM
&LP_INV_NUM
&LP_INV_TYPE
&LP_CURRENCY
&LP_GROUP_BY
--order by 8, 9, 10, 11, 12, 14, 13, 16, 15
order by 4,5,6,7,1,2,3,8,9,10,11,12,14,13,16,15