AR Audit Report by Document Number

            select a.*,
            AR_ARXINVAD_XMLP_PKG.c_new_recordsformula(a.doc_sequence_value) C_new_records,
            AR_ARXINVAD_XMLP_PKG.C_last_value_p C_last_value
            from
            (SELECT    aud.doc_sequence_value              doc_sequence_value,
                              aud.doc_sequence_id                    doc_sequence_id,
                              aud.creation_date                           creation_date,
                              decode( cr.doc_sequence_value,
                                             null,
                                            :C_deleted,
                                            :C_entered)                       status,
                             dsc.name                                          category,
                             c.account_number                          customer_num,
                             substrb(party.party_name,1,50)    customer_name,
                             cr.receipt_number                           invoice_num,
                             cr.currency_code                            invoice_currency_code,
                             cr.amount                                         invoice_amount
            FROM     ar_doc_sequence_audit aud,
                            fnd_doc_sequence_assignments dsa,
                            fnd_doc_sequence_categories dsc,
                            hz_cust_accounts c,
                            hz_parties party,
                            ar_cash_receipts_all  cr
            WHERE  aud.doc_sequence_id = :p_sequence_id
            AND       aud.doc_sequence_id = cr.doc_sequence_id(+)
            AND       aud.doc_sequence_value =
                           cr.doc_sequence_value(+)
            AND       aud.doc_sequence_value BETWEEN
                           :P_sequence_from  AND :P_sequence_to
            AND       aud.doc_sequence_assignment_id =
                           dsa.doc_sequence_assignment_id
            AND       dsa.category_code = dsc.code
            AND       dsa.application_id = dsc.application_id
            AND       cr.pay_from_customer = c.cust_account_id(+)
            AND       c.party_id = party.party_id(+)
            AND      :p_type = 'RECEIPT'
            UNION
            SELECT    aud.doc_sequence_value             doc_sequence_value,
                              aud.doc_sequence_id                   doc_sequence_id,
                              aud.creation_date                          creation_date,
                              decode( ct.doc_sequence_value,
                                             null,
                                            :C_deleted,
                                            :C_entered )                      status,
                              dsc.name                                         category,
                              c.account_number                       customer_num,
                              substrb(party.party_name,1,50)  customer_name,
                              ct.trx_number                                  invoice_num,
                              ct.invoice_currency_code             invoice_currency_code,
                              ctl.amount                                       invoice_amount
            FROM       ar_doc_sequence_audit  aud,
                              fnd_doc_sequence_assignments dsa,
                              fnd_doc_sequence_categories dsc,
                              hz_cust_accounts c,
                               hz_parties party,
                              ra_customer_trx_all ct,
                              ra_cust_trx_line_gl_dist_all ctl
            WHERE    aud.doc_sequence_id = :p_sequence_id
            AND         aud.doc_sequence_id = ct.doc_sequence_id(+)
            AND         aud.doc_sequence_value =
                             ct.doc_sequence_value(+)
            AND         aud.doc_sequence_value BETWEEN
                            :P_sequence_from  AND :P_sequence_to
            AND        aud.doc_sequence_assignment_id =
                            dsa.doc_sequence_assignment_id
            AND        dsa.category_code = dsc.code
            AND        dsa.application_id = dsc.application_id
            AND        ct.bill_to_customer_id = c.cust_account_id(+)
            AND        c.party_id = party.party_id(+)
            AND        ct.customer_trx_id  = ctl.customer_trx_id(+)
            AND        nvl(ctl.account_class,'REC') = 'REC'
            AND        nvl(ctl.latest_rec_flag,'Y') = 'Y'
            AND        :p_type = 'INVOICE'
            UNION
            SELECT    aud.doc_sequence_value              doc_sequence_value,
                              aud.doc_sequence_id                    doc_sequence_id,
                              aud.creation_date                           creation_date,
                              decode( adj.doc_sequence_value,
                                            null,
                                           :C_deleted,
                                           :C_entered )                       status,
                             dsc.name                                          category,
                             c.account_number                        customer_num,
                             substrb(party.party_name,1,50)  customer_name,
                             adj.adjustment_number                  invoice_num,
                             ps.invoice_currency_code             invoice_currency_code,
                             adj.amount                                        invoice_amount
            FROM      ar_doc_sequence_audit aud,
                             fnd_doc_sequence_assignments dsa,
                             fnd_doc_sequence_categories dsc,
                             ar_payment_schedules_all ps,
                             hz_cust_accounts c,
                hz_parties party,
                             ra_customer_trx_all ct,
                             ar_adjustments_all adj
            WHERE   aud.doc_sequence_id = :p_sequence_id
            AND        aud.doc_sequence_id = adj.doc_sequence_id(+)
            AND        aud.doc_sequence_value =
                            adj.doc_sequence_value(+)
            AND        aud.doc_sequence_value BETWEEN
                           :P_sequence_from AND :P_sequence_to
            AND        aud.doc_sequence_assignment_id =
                            dsa.doc_sequence_assignment_id
            AND        dsa.category_code = dsc.code
            AND        dsa.application_id = dsc.application_id
            AND        adj.customer_trx_id = ct.customer_trx_id(+)
            AND        ct.bill_to_customer_id = c.cust_account_id(+)
            AND c.party_id = party.party_id(+)
            AND        adj.payment_schedule_id =
                            ps.payment_schedule_id(+)
            AND       :p_type = 'ADJUSTMENT'
            ORDER BY 1) a