SELECT   arps.org_id, hou.NAME company, rctl.customer_trx_id,
               TO_CHAR (arps.trx_date, 'MON-RRRR') fiscal_year_month,
               arps.trx_number invoice_number, arps.trx_date invoice_date,
               arps.customer_id,
               NVL (arps.amount_due_original, 0) total_invoice_amount,
               NVL (arps.tax_original, 0) total_tax_amount,
                 NVL (quantity_invoiced, 0)
               * NVL (unit_selling_price, 0) line_amount,
               arps.due_date,
               CASE
                  WHEN arps.CLASS = 'INV'
                     THEN 'Invoice'
                  WHEN arps.CLASS = 'CM'
                     THEN 'Credit Memo'
                  WHEN arps.CLASS = 'DM'
                     THEN 'Debit Memo'
                  ELSE 'Others'
               END inv_type,
               arps.gl_date, REPLACE (rt.NAME, ',', ' ') payment_terms,
               arps.term_id, arps.invoice_currency_code,
               TO_CHAR (arps.gl_date, 'RRRR') fiscal_year,
               rctl.inventory_item_id,
               TO_CHAR (arps.gl_date, 'Mon-RRRR') fiscal_period,
               REPLACE (rctl.description, ',', ' ') item_desc,
               msi.segment1 item_code, rctl.quantity_invoiced,
               rctl.extended_amount, gll.currency_code ledger_currency,
               (SELECT ct_reference
                  FROM ra_customer_trx_all rct
                 WHERE rct.customer_trx_id =
                                       rctl.customer_trx_id)
                                                           invoice_reference,
               (SELECT DISTINCT gck.concatenated_segments
                           FROM ra_cust_trx_line_gl_dist_all rctd,
                                gl_code_combinations_kfv gck
                          WHERE 1 = 1
                            AND rctl.customer_trx_line_id =
                                                     rctd.customer_trx_line_id
                            AND rctd.account_class IN ('REV')
                            --and rctl.line_type='LINE'
                            AND rctd.code_combination_id =
                                                       gck.code_combination_id)
                                                              rev_cc_account,
               (SELECT DISTINCT gck.concatenated_segments
                           FROM ra_cust_trx_line_gl_dist_all rctd,
                                gl_code_combinations_kfv gck
                          WHERE 1 = 1
                            AND rctd.customer_trx_id = arps.customer_trx_id
                            AND rctd.account_class IN ('REC')
                            --and rctl.line_type='LINE'
                            AND rctd.code_combination_id =
                                                       gck.code_combination_id)
                                                              rec_cc_account,
               rctl.unit_selling_price, hca.account_number customer_number,
               SUM (xal.entered_dr) entered_dr,
               SUM (xal.entered_cr) entered_cr,
               SUM (xal.accounted_cr) accounted_cr,
               SUM (xal.accounted_dr) accounted_dr, xal.currency_code,
               xal.accounting_date
          FROM ar_payment_schedules_all arps,
               xla.xla_transaction_entities xte,
               xla.xla_ae_headers xah,
               xla.xla_ae_lines xal,
               gl_ledgers gll,
               hr_operating_units hou,
               ra_terms_vl rt,
               ra_customer_trx_lines_all rctl,
               mtl_system_items_b msi,
               hz_cust_accounts hca
         WHERE 1 = 1
           AND xte.source_id_int_1 = arps.customer_trx_id
           AND xte.entity_id = xah.entity_id(+)
           AND xte.ledger_id = gll.ledger_id(+)
           AND xah.ae_header_id = xal.ae_header_id(+)
           AND xte.entity_code = 'TRANSACTIONS'
--AND arps.trx_number = '10021269'
--     AND arps.trx_number = '10046064'
--     AND xah.event_type_code = 'INV_CREATE'
--           AND arps.customer_trx_id IN (985645, 1202200, 985646, 1037036)
           AND arps.amount_due_remaining <> 0
           AND arps.org_id = p_org_id                                    --204
           AND arps.org_id = hou.organization_id
           AND arps.customer_trx_id = rctl.customer_trx_id
           AND rctl.line_type = 'LINE'
           AND arps.customer_id = hca.cust_account_id
           AND rctl.inventory_item_id = msi.inventory_item_id(+)
           AND rctl.warehouse_id = msi.organization_id(+)
           AND arps.term_id = rt.term_id
      GROUP BY xal.currency_code,
               xal.accounting_date,
               arps.trx_number,
               arps.trx_date,
               arps.due_date,
               arps.amount_due_original,
               arps.tax_original,
               arps.CLASS,
               arps.gl_date,
               arps.invoice_currency_code,
               rctl.description,
               msi.segment1,
               rctl.quantity_invoiced,
               arps.customer_trx_id,
               rctl.unit_selling_price,
               hca.account_number,
               rt.NAME,
               arps.org_id,
               hou.NAME,
               arps.customer_id,
               rctl.customer_trx_id,
               arps.term_id,
               rctl.extended_amount,
               rctl.customer_trx_line_id,
               rctl.inventory_item_id,
               gll.currency_code
      ORDER BY arps.trx_number;