AR Transactions Awaiting Consolidation

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Transactions Awaiting Consolidation (XML)
Short Name: ARXAPIPM_XML
DB package: AR_ARXAPIPM_XMLP_PKG
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
Parameter NameSQL textValidation
Ledger Id
 
LOV Oracle
Currency
 
LOV Oracle
Invoice Type
 
LOV Oracle
Invoice Number High
 
LOV Oracle
Invoice Number Low
 
LOV Oracle
Customer Number
 
LOV Oracle
Customer Name
 
LOV Oracle
Payment Method
 
LOV Oracle
Due Date High
 
Date
Due Date Low
 
Date
Invoice Date High
 
Date
Invoice Date Low
 
Date
Status
 
LOV Oracle
Summarize
 
LOV Oracle
Order By
 
LOV Oracle