Disclaimer

 If you require any more information or have any questions about our site's disclaimer, please feel free to contact us by email at pradhyumns16dec@gmail.com.

Disclaimers for yourerptutor.blogspot.com

All the information on this website - https://yourerptutor.blogspot.com - is published in good faith and for general information purpose only. yourerptutor.blogspot.com does not make any warranties about the completeness, reliability and accuracy of this information. Any action you take upon the information you find on this website (yourerptutor.blogspot.com), is strictly at your own risk. yourerptutor.blogspot.com will not be liable for any losses and/or damages in connection with the use of our website.

From our website, you can visit other websites by following hyperlinks to such external sites. While we strive to provide only quality links to useful and ethical websites, we have no control over the content and nature of these sites. These links to other websites do not imply a recommendation for all the content found on these sites. Site owners and content may change without notice and may occur before we have the opportunity to remove a link which may have gone 'bad'.

Please be also aware that when you leave our website, other sites may have different privacy policies and terms which are beyond our control. Please be sure to check the Privacy Policies of these sites as well as their "Terms of Service" before engaging in any business or uploading any information.

Consent

By using our website, you hereby consent to our disclaimer and agree to its terms.

Update

Should we update, amend or make any changes to this document, those changes will be prominently posted here.

Customer Outstanding Amount Query

In this post, I am sharing customer outstanding amount which is calculated on accounting date. The logic includes override functionality also. Sometimes user selected wrong account and further he changed account by using override functionality available in account receivables.

You can use this query for AR Aging report. fun_outstanding_amt field shows values in ledger currency. 

Detail Query of Customer Outstanding/ opening balance

---

 SELECT hp.party_name customer, 
             rcta.trx_number,
                 hca.customer_class_code, 
hca.party_id,
                 rcta.bill_to_customer_id, 
rcta.org_id, 
hca.account_name,
                 hca.account_number, 
rcta.customer_trx_id,
                 (SELECT name
                    FROM hr_all_organization_units hr
                   WHERE hr.organization_id = rcta.org_id) bu_name,

                 apsa.amount_due_original invoice_amount,
                 (SELECT    segment1
                         || '.'
                         || segment2
                         || '.'
                         || segment3
                         || '.'
                         || segment4
                         || '.'
                         || segment5
                         || '.'
                         || segment6
                    FROM ra_cust_trx_line_gl_dist_all ardist,
                         gl_code_combinations gcc
                   WHERE ardist.account_class = 'REC'
                     AND gcc.code_combination_id = ardist.code_combination_id
                     AND ardist.customer_trx_id = rcta.customer_trx_id
                     AND ROWNUM = 1) account,
           a.segment6 nat_acct,
                 (  amount_due_original
                  - ( NVL ((SELECT SUM (ara.amount_applied)
                               FROM ar_cash_receipts_all acr,
                                    ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.applied_customer_trx_id
                                AND acr.cash_receipt_id = ara.cash_receipt_id
                               -- AND ara.applied_payment_schedule_id =
                                                      --apsa.payment_schedule_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
                                AND ARA.DISPLAY = 'Y'
--AND ara.reversal_gl_date IS NULL
                                AND ara.gl_date <=
                                       TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
                                                                'DD-MM-YYYY'
                                                               ),
                                                       'DD-MM-YYYY'
                                                      )
                                             )),
                            0
                           )    
+ NVL ((SELECT SUM (ara.amount_applied)
                               FROM ar_cash_receipts_all acr,
                                    ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.applied_customer_trx_id
                                AND acr.cash_receipt_id = ara.cash_receipt_id
                               -- AND ara.applied_payment_schedule_id =
                                                      --apsa.payment_schedule_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
                              AND ARA.DISPLAY = 'N'
AND ara.reversal_gl_date IS NOT NULL
                                AND ara.gl_date <=
                                       TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
                                                                'DD-MM-YYYY'
                                                               ),
                                                       'DD-MM-YYYY'
                                                      )
                                             )),
                            0
                           )
--    
                     + NVL
                          ((SELECT SUM ((aav.amount * -1))
                              FROM ar_adjustments_all aav
                             WHERE rcta.customer_trx_id = aav.customer_trx_id
                               AND aav.payment_schedule_id =
                                                      apsa.payment_schedule_id
                               AND TRUNC (aav.gl_date) <=
                                      TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
                                                               'DD-MM-YYYY'
                                                              ),
                                                      'DD-MM-YYYY'
                                                     )
                                            )
                               AND aav.status = 'A'),
                           0
                          )
                     + (-1) * NVL ((SELECT SUM (ara.amount_applied)
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id = ara.customer_trx_id
                                AND ara.status IN ('ACTIVITY') --, 'APP')
                                AND ara.display = 'Y'
                                AND ara.gl_date <=
                                       TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
                                                                'DD-MM-YYYY'
                                                               ),
                                                       'DD-MM-YYYY'
                                                      )
                                             )),
                            0
                           )
+ (-1) * NVL ((SELECT SUM (  ara.amount_applied
                    -- * (-1)
                                        )
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.customer_trx_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
                                AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
                                AND ara.display = 'Y'
                                AND ara.gl_date <=
                                       TRUNC (:p_as_of_date)),
                            0
                           )
+  NVL ((SELECT SUM (  ara.amount_applied)
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.applied_customer_trx_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
                                AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
                                AND ara.display = 'Y'
                                AND ara.gl_date <=
                                       TRUNC (:p_as_of_date)),
                            0
                           )    
+ (-1) * NVL ((SELECT SUM (  ara.amount_applied
                     --* (-1)
                                        )
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.customer_trx_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
                                AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
                                AND ara.display = 'N'
                                AND ara.gl_date <=
                                       TRUNC (:p_as_of_date)),
                            0
                           )
+  NVL ((SELECT SUM (  ara.amount_applied)
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.applied_customer_trx_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
                                AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
                                AND ara.display = 'N'
                                AND ara.gl_date <=
                                       TRUNC (:p_as_of_date)),
                            0
                           )    
                    )
                 ) ent_outstanding_amt,
                 (  (amount_due_original * NVL(rcta.exchange_rate,1))
                  - ( NVL ((SELECT SUM (ara.amount_applied  * NVL(rcta.exchange_rate,1))
                               FROM ar_cash_receipts_all acr,
                                    ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.applied_customer_trx_id
                                AND acr.cash_receipt_id = ara.cash_receipt_id
                               -- AND ara.applied_payment_schedule_id =
                                                      --apsa.payment_schedule_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
                              AND ara.display = 'Y'
--AND ara.reversal_gl_date IS NULL
                                AND ara.gl_date <=
                                       TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
                                                                'DD-MM-YYYY'
                                                               ),
                                                       'DD-MM-YYYY'
                                                      )
                                             )),
                            0
                           )    
+ NVL ((SELECT SUM (ara.amount_applied  * NVL(rcta.exchange_rate,1))
                               FROM ar_cash_receipts_all acr,
                                    ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.applied_customer_trx_id
                                AND acr.cash_receipt_id = ara.cash_receipt_id
                               -- AND ara.applied_payment_schedule_id =
                                                      --apsa.payment_schedule_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
                              AND ara.display = 'N'
AND ara.reversal_gl_date IS NOT NULL
                                AND ara.gl_date <=
                                       TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
                                                                'DD-MM-YYYY'
                                                               ),
                                                       'DD-MM-YYYY'
                                                      )
                                             )),
                            0
                           )
--    
                     + NVL
                          ((SELECT SUM ((aav.amount * -1)  * NVL(rcta.exchange_rate,1))
                              FROM ar_adjustments_all aav
                             WHERE rcta.customer_trx_id = aav.customer_trx_id
                               AND aav.payment_schedule_id = apsa.payment_schedule_id
                               AND TRUNC (aav.gl_date) <=
                                      TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
                                                               'DD-MM-YYYY'
                                                              ),
                                                      'DD-MM-YYYY'
                                                     )
                                            )
                               AND aav.status = 'A'),
                           0
                          )
                     + (-1) * NVL ((SELECT SUM ((ara.amount_applied 
                 )  * NVL(rcta.exchange_rate,1))
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id = ara.customer_trx_id
                                AND ara.status IN ('ACTIVITY')
                                AND ara.display = 'Y'
                                AND ara.gl_date <=
                                       TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
                                                                'DD-MM-YYYY'
                                                               ),
                                                       'DD-MM-YYYY'
                                                      )
                                             )),
                            0
                           )
+ (-1) * NVL ((SELECT SUM (  ara.amount_applied
                    -- * (-1)
* NVL(rcta.exchange_rate,1)
                                        )
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.customer_trx_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
                                AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
                                AND ara.display = 'Y'
                                AND ara.gl_date <=
                                       TRUNC (:p_as_of_date)),
                            0
                           )
+  NVL ((SELECT SUM (  ara.amount_applied
                    -- * (-1)
* NVL(rcta.exchange_rate,1)
                                        )
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.applied_customer_trx_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
                                AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
                                AND ara.display = 'Y'
                                --AND ARA.APPLY_DATE <=
                                AND ara.gl_date <=
                                       TRUNC (:p_as_of_date)),
                            0
                           )
+ NVL ((SELECT SUM (  ara.amount_applied
                     --* (-1)
* NVL(rcta.exchange_rate,1)
                                        )
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.applied_customer_trx_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
                                AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
                                AND ara.display = 'N'
                                --AND ARA.APPLY_DATE <=
                                AND ara.gl_date <=
                                        (:p_as_of_date)),
                            0
                           )    
+ (-1) * NVL ((SELECT SUM (  ara.amount_applied
                    -- * (-1)
* NVL(rcta.exchange_rate,1) 
                                        )
                               FROM ar_receivable_applications_all ara
                              WHERE rcta.customer_trx_id =
                                                   ara.customer_trx_id
                                AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
                                AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
                                AND ara.display = 'N'
                                --AND ARA.APPLY_DATE <=
                                AND ara.gl_date <=
                                       TRUNC (:p_as_of_date)),
                            0
                           )    
                    )
                 ) fun_outstanding_amt,
                   ((  apsa.amount_due_original
                     + NVL (apsa.amount_credited, 0)
                     + NVL (apsa.amount_adjusted, 0)
                     - apsa.tax_remaining
                    )
                   )
                 * .1 retainage_amt,
 
        ((  apsa.amount_due_original
                     + NVL (apsa.amount_credited, 0)
                     + NVL (apsa.amount_adjusted, 0)
                     - apsa.tax_remaining
                    )
                   )
                 * .1 * NVL (rcta.exchange_rate, 1) retainage_amt1,
                 DECODE ((SELECT COUNT (1)
                            FROM ra_batch_sources_all
                           WHERE batch_source_seq_id =
                                                      rcta.batch_source_seq_id
                             AND NAME = 'MIGRATION'),
                         1, 'NO',
                         DECODE (UPPER (rcta.attribute1),
                                 'YES', 'YES',
                                 'NO', 'NO',
                                 NULL, DECODE (hca.customer_class_code,
                                               'RETENTION', 'YES',
                                               'NO'
                                              ),
                                 'NO'
                                )
                        ) eligible,
                 apsa.amount_due_original, apsa.amount_credited,
                 apsa.amount_adjusted, apsa.tax_remaining, apsa.due_date,
                 apsa.amount_due_remaining, apsa.gl_date, rctt.TYPE,
                 rctt.NAME trx_type, rcta.trx_date invoice_date,
                 hp.party_number,
                 DECODE (rcta.invoice_currency_code,
                         'SAR', 1,
                         rcta.exchange_rate
                        ) cur_cd,
                 rcta.doc_sequence_value, rcta.invoice_currency_code,
                 (SELECT rt.NAME
                    FROM ra_terms rt
                   WHERE rcta.term_id = rt.term_id) payment_term,
                 NVL ((  TO_DATE (TO_CHAR (due_date, 'MM-DD-YYYY'),
                                  'MM-DD-YYYY'
                                 )
                       - TO_DATE (TO_CHAR (:p_as_of_date, 'MM-DD-YYYY'),
                                  'MM-DD-YYYY'
                                 )
                      ),
                      0
                     ) past_due_days,
rcta.exchange_rate_type
            FROM hz_cust_accounts hca,
                 ra_customer_trx_all rcta,
                 ar_payment_schedules_all apsa,
                 hz_parties hp,
                 ra_cust_trx_types_all rctt,
(SELECT gcc.segment6,
         ardist.customer_trx_id
                    FROM gl_code_combinations gcc,
xla_distribution_links xdl,
ra_cust_trx_line_gl_dist_all ardist,
xla_ae_lines xal,
xla_ae_headers xah
                   WHERE ardist.account_class IN ('REC')
                     AND  ardist.cust_trx_line_gl_dist_id = xdl.source_distribution_id_num_1 
                     AND xdl.application_id = 222
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xal.ae_header_id = xah.ae_header_id
AND xah.je_category_name IN ('Sales Invoices','Credit Memos','Debit Memos')
AND xal.accounting_class_code = 'RECEIVABLE'
AND xah.ae_header_id = (SELECT MAX(xal1.ae_header_id) 
                           FROM xla_ae_lines xal1,
        xla_ae_headers xah1 
  WHERE xah1.entity_id = xah.entity_id 
                            AND xal1.ae_header_id = xah1.ae_header_id  
AND xal1.accounting_class_code = 'RECEIVABLE' 
                            AND xah1.event_id = xdl.event_id
                            AND xah1.je_category_name IN ('Sales Invoices','Credit Memos','Debit Memos')
                            AND xal1.accounting_date <= :p_as_of_date
                       GROUP BY xah1.entity_id
     )  
                     -- Overridden logic
AND gcc.code_combination_id IN DECODE(xal.override_reason,NULL,
                                       ardist.code_combination_id,
                                      (SELECT xal1.code_combination_id 
     FROM  xla_ae_lines xal1 
WHERE xal1.accounting_class_code = 'RECEIVABLE'
                                              AND xal1.ae_header_id=xal.ae_header_id
                                          AND xal1.overridden_code_combination_id IS NOT NULL
                                          AND xal1.ae_header_id = (SELECT MAX(xal1.ae_header_id) 
                             FROM xla_ae_lines xal1,
      xla_ae_headers xah1 
WHERE xah1.entity_id=xah.entity_id 
                                                                      AND xal1.ae_header_id = xah1.ae_header_id  
  AND xal1.accounting_class_code = 'RECEIVABLE' 
                                                                      AND xah1.je_category_name IN ('Sales Invoices','Credit Memos','Debit Memos')
                                                                      AND xah1.event_id = xdl.event_id
                                                                      AND xal1.accounting_date <= :p_as_of_date
                                                                 GROUP BY xah1.entity_id
    )
                                       ))
) a
           WHERE hca.cust_account_id = rcta.bill_to_customer_id
             AND rcta.customer_trx_id = apsa.customer_trx_id
             AND hca.party_id = hp.party_id
             AND rcta.cust_trx_type_seq_id = rctt.cust_trx_type_seq_id
AND rcta.customer_trx_id = a.customer_trx_id
             AND TRUNC (apsa.gl_date) <= NVL (:p_as_of_date, SYSDATE) 
             AND rcta.bill_to_customer_id IN (
                    SELECT DISTINCT hzc.cust_account_id
                               FROM hz_cust_accounts hzc, 
        hz_parties hzp
                              WHERE hzc.party_id = hzp.party_id
                                AND (   hzp.party_name IN (:p_cust_name)
                                     OR LEAST (:p_cust_name) IS NULL
                                    )
                                AND (   hzc.account_name IN (:p_cust_acc)
                                     OR LEAST (:p_cust_acc) IS NULL
                                    ))
             AND rcta.org_id IN (SELECT organization_id
                                   FROM hr_all_organization_units
                                  WHERE name = :p_bu_name)

GL Dump in Oracle Fusion

In this post, I will share GL query which provide details of journal entries in General Ledger. 

Tables

---

gl_je_headers
gl_je_lines
gl_je_batches
gl_code_combinations
xle_entity_profiles
gl_periods

GL Detailed Query

---

 SELECT xep.name legal_entity_name,
       gjh.legal_entity_id,
   gjh.je_header_id,
       TRUNC(gjh.creation_date) journal_date,
   gjh.default_effective_date accounting_date,
   gjh.posted_date,
   gjh.je_source,
   gjh.je_category,
   gjh.name journal_name,
           gjh.period_name,
   gjb.name batch_name,
   gjh.POSTING_ACCT_SEQ_VALUE doc_sequence_value,
   gjl.je_line_num,
   gcc.segment1 company,
   gcc.segment2 cost_center,
   gcc.segment3 natural_account,
   gcc.segment4 product,
   gcc.segment5 intercompany,
   gcc.segment6 future,
   gjl.description,
   gjl.accounted_cr,
   gjl.accounted_dr
  FROM gl_je_headers gjh,
       gl_je_lines gjl,
   gl_je_batches gjb,
   gl_code_combinations gcc,
   xle_entity_profiles xep,
   gl_periods gp
 WHERE gjh.je_header_id = gjl.je_header_id 
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gjh.legal_entity_id = xep.legal_entity_id
   AND gjh.status = 'P' -- only posted journals
   AND gjh.period_name = gp.period_name
   AND gp.period_set_name IN (SELECT period_set_name 
                                FROM gl_ledgers 
       WHERE ledger_id IN (SELECT ledger_id 
                             FROM xle_le_ou_ledger_v
    WHERE legal_entity_id = :p_entity_id
      )
      )
   AND xep.legal_entity_id = :p_entity_id
   AND TO_NUMBER(gp.period_year||lpad(gp.period_num,2,'0')) >= :p_from_period
   AND TO_NUMBER(gp.period_year||lpad(gp.period_num,2,'0')) <= :p_to_period
ORDER BY gjh.je_header_id, gjl.je_line_num

Customer Statement of Account (SOA)

The customer statement will provide the information of customer transactions, happened till as of date. These transactions can be invoice, debit memo, credit memo, receipts, applications, adjustments. This report also shows the closing balance of customer/s till as of date. 

This is one of the main report which management demands almost in all the organizations/ companies. We provided the detailed query of customers happened in between the dates. You can refer Customer Opening Query to get customer opening balance.

Detailed Query

---

SELECT '1' typ,hou.name "Business Unit",
       hca.cust_account_id,
       rcta.org_id org_id,
       hca.ACCOUNT_NUMBER CustomerNumber,
       hp.party_name customer_name,
          hp.ADDRESS1
       || ','
       || CHR (10)
       || hp.ADDRESS2
       || ','
       || CHR (10)
       || hp.ADDRESS3
          address,
       hp.city city,
       hp.COUNTRY,
       UPPER (rcta.INVOICE_CURRENCY_CODE) currency,
       rcta.trx_date transactiondate,
       rctta.name trantype,
       rcta.trx_number transactionnumber,
       rcta.doc_sequence_value vouchernumber,
       rt.name termname,
       rcta.purchase_order ponumber,
       rcta.comments description,
       rcta.invoice_currency_code innvoicecurrency,
       (CASE
           WHEN rctta.TYPE IN ('INV', 'DM')
           THEN
              (SELECT SUM (ABS (NVL (extended_amount, 0)))
                 FROM ra_customer_trx_lines_all
                WHERE customer_trx_id = rcta.customer_trx_id)
           ELSE
              0
        END)
       * (NVL (rcta.EXCHANGE_RATE, 1))
          debit,
       (CASE
           WHEN rctta.TYPE IN ('CM')
           THEN
              (SELECT SUM (ABS (NVL (extended_amount, 0)))
                 FROM ra_customer_trx_lines_all
                WHERE customer_trx_id = rcta.customer_trx_id)
           ELSE
              0
        END)
       * (NVL (rcta.EXCHANGE_RATE, 1))
          credit,
       CASE
          WHEN (SELECT COUNT (*)
                  FROM ra_cust_trx_line_gl_dist_all
                 WHERE customer_trx_id = rcta.customer_trx_id
                       AND gl_posted_date IS NOT NULL) > 0
          THEN
             'Accounted'
          WHEN (SELECT COUNT (*)
                  FROM ra_cust_trx_line_gl_dist_all
                 WHERE customer_trx_id = rcta.customer_trx_id
                       AND gl_posted_date IS NOT NULL) < 0
          THEN
             'Not Accounted'
       END
          transactionstatus,
       rcta.CUSTOMER_TRX_ID,
       rcta.EXCHANGE_RATE_TYPE,
       rcta.EXCHANGE_DATE,
       rcta.EXCHANGE_RATE,
       (SELECT SUM (NVL (extended_amount, 0))
          FROM ra_customer_trx_lines_all
         WHERE customer_trx_id = rcta.customer_trx_id)
          entered_amount
  FROM ra_customer_trx_all rcta,
       hz_cust_accounts hca,
       hz_parties hp,
       ra_cust_trx_types_all rctta,
       ra_terms rt,
       hr_operating_units hou
 WHERE     1 = 1
       AND rcta.bill_to_customer_id = hca.cust_account_id
       AND hca.party_id = hp.party_id
       AND rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id
       AND rt.term_id(+) = rcta.term_id
       AND hou.organization_id = rcta.org_id
AND rcta.complete_flag = 'Y'
       AND EXISTS (SELECT '1' 
               FROM ra_cust_trx_line_gl_dist_all d
              WHERE d.customer_trx_id = rcta.customer_trx_id
    AND d.accounting_date BETWEEN NVL (:p_from_date,
                                              TRUNC (d.accounting_date))
                                     AND NVL (:p_to_date,
                                              TRUNC (d.accounting_date))
 )
   AND rcta.org_id = NVL (:p_org_id, rcta.org_id)
       AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION                                     -- Below Query For Adjusted Invoices
SELECT '3' typ,hou.name "Business Unit",
       hca.cust_account_id,
       rcta.org_id org_id,
       hca.ACCOUNT_NUMBER CustomerNumber,
       hp.party_name customer_name,
          hp.ADDRESS1
       || ','
       || CHR (10)
       || hp.ADDRESS2
       || ','
       || CHR (10)
       || hp.ADDRESS3
          address,
       hp.city city,
       hp.COUNTRY,
       UPPER (rcta.INVOICE_CURRENCY_CODE) currency,
       adj.trx_date transactiondate,
       'Adjustment' trantype,
       adj.trx_number transactionnumber,
       adj.doc_sequence_value vouchernumber,
       rt.name termname,
       rcta.purchase_order ponumber,
       rcta.comments description,
       rcta.invoice_currency_code innvoicecurrency,
       CASE
          WHEN NVL (adj.amount, 0) >= 0
          THEN
             ( (ABS (adj.amount)) * (NVL (rcta.EXCHANGE_RATE, 1)))
          ELSE
             0
       END
          debit,
       CASE
          WHEN NVL (adj.amount, 0) < 0
          THEN
             ( (ABS (adj.amount)) * (NVL (rcta.EXCHANGE_RATE, 1)))
          ELSE
             0
       END
          credit,                                                           --
       NULL transactionstatus,
       rcta.CUSTOMER_TRX_ID,
       rcta.EXCHANGE_RATE_TYPE,
       rcta.EXCHANGE_DATE,
       rcta.EXCHANGE_RATE,
       ABS (adj.amount) entered_amount
  FROM ra_customer_trx_all rcta,
       hz_cust_accounts hca,
       hz_parties hp,
       ra_cust_trx_types_all rctta,
       ra_terms rt,
       hr_operating_units hou,
       (  SELECT ada.CUSTOMER_TRX_ID,
                 ada.APPLY_DATE trx_date,
                 ada.ADJUSTMENT_NUMBER trx_number,
                 ada.DOC_SEQUENCE_VALUE,
                 SUM (NVL (ada.AMOUNT, 0)) amount,
 ada.gl_date
            FROM AR_ADJUSTMENTS_ALL ada
           WHERE ada.STATUS = 'A'
        GROUP BY ada.CUSTOMER_TRX_ID,
                 ada.ADJUSTMENT_NUMBER,
                 ada.APPLY_DATE,
                 ada.DOC_SEQUENCE_VALUE, ada.gl_date) adj
 WHERE     1 = 1
       AND rcta.bill_to_customer_id = hca.cust_account_id
       AND hca.party_id = hp.party_id
       AND rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id
       AND rt.term_id(+) = rcta.term_id
       AND hou.organization_id = rcta.org_id
AND rcta.complete_flag = 'Y'
       AND rcta.CUSTOMER_TRX_ID = adj.CUSTOMER_TRX_ID
       AND TRUNC (adj.gl_date) BETWEEN NVL (:p_from_date,
                                             TRUNC (adj.gl_date))
                                    AND NVL (:p_to_date,
                                             TRUNC (adj.gl_date))
       AND rcta.org_id = NVL (:p_org_id, rcta.org_id)
       AND hca.account_number = NVL (:p_account_number, hca.account_number) 
UNION                                          -- Below Query for Receipt Data
SELECT '2' typ,hou.name "Business Unit",
       hca.cust_account_id,
       acra.org_id org_id,
       hca.ACCOUNT_NUMBER CustomerNumber,
       hp.party_name customername,
          hp.ADDRESS1
       || ','
       || CHR (10)
       || hp.ADDRESS2
       || ','
       || CHR (10)
       || hp.ADDRESS3
          address,
       hp.city city,
       hp.COUNTRY,
       UPPER (acra.CURRENCY_CODE) currency,
       acra.receipt_date transactiondate,
       'Receipts' trantype,
       acra.receipt_number transactionnumber,
       acra.doc_sequence_value vouchernumber,
       NULL termname,
       NULL ponumber,
       acra.comments description,
       acra.currency_code innvoicecurrency,
       0 debit,
       (NVL (acra.amount, 0) * NVL (acra.EXCHANGE_RATE, 1)) credit,
       DECODE (apsa.status,  'OP', 'Unapplied/Partially Applied',  'CL', 'Applied') transactionstatus,
       acra.cash_receipt_id,
       acra.EXCHANGE_RATE_TYPE,
       acra.EXCHANGE_DATE,
       acra.EXCHANGE_RATE,
       acra.AMOUNT entered_amount
  FROM ar_cash_receipts_all acra,
       hz_cust_accounts hca,
       hz_parties hp,
       ar_payment_schedules_all apsa,
       hr_operating_units hou
 WHERE     1 = 1
       AND acra.pay_from_customer = hca.cust_account_id
       AND hp.party_id = hca.party_id
       AND apsa.cash_receipt_id = acra.cash_receipt_id
       AND hou.organization_id = acra.org_id
       AND TRUNC (apsa.gl_date) BETWEEN NVL (:p_from_date,
                                                  TRUNC (apsa.gl_date))
                                         AND NVL (:p_to_date,
                                                  TRUNC (apsa.gl_date))
       AND acra.org_id = NVL (:p_org_id, acra.org_id)
       AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
       AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION                                          -- Below Query for Receipt Data
SELECT '4' typ,hou.name "Business Unit",
       hca.cust_account_id,
       acra.org_id org_id,
       hca.ACCOUNT_NUMBER CustomerNumber,
       hp.party_name customername,
          hp.ADDRESS1
       || ','
       || CHR (10)
       || hp.ADDRESS2
       || ','
       || CHR (10)
       || hp.ADDRESS3
          address,
       hp.city city,
       hp.COUNTRY,
       UPPER (acra.CURRENCY_CODE) currency,
       --acra.receipt_date transactiondate,
   ara.gl_date transactiondate,
       'Refund' trantype,
       acra.receipt_number transactionnumber,
       acra.doc_sequence_value vouchernumber,
       NULL termname,
       NULL ponumber,
       acra.comments description,
       acra.currency_code innvoicecurrency,
       (NVL (ara.amount_applied, 0) * NVL (acra.EXCHANGE_RATE, 1)) debit,
   0 credit,
   'Refund' transactionstatus,
       acra.cash_receipt_id,
       acra.EXCHANGE_RATE_TYPE,
       acra.EXCHANGE_DATE,
       acra.EXCHANGE_RATE,
   ara.amount_applied entered_amount
  FROM ar_cash_receipts_all acra,
       hz_cust_accounts hca,
       hz_parties hp,
       ar_receivable_applications ara,
       hr_operating_units hou
 WHERE     1 = 1
       AND acra.pay_from_customer = hca.cust_account_id
       AND hp.party_id = hca.party_id
   AND ara.applied_payment_schedule_id IN (-8,-3)  -- -8 For refund  and -3 for Receipt Write-off
       AND ara.cash_receipt_id = acra.cash_receipt_id
       AND hou.organization_id = acra.org_id
   AND TRUNC (ara.gl_date) BETWEEN NVL (:p_from_date,
                                                  TRUNC (ara.gl_date))
                                         AND NVL (:p_to_date,
                                                  TRUNC (ara.gl_date))
       AND acra.org_id = NVL (:p_org_id, acra.org_id)
       AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION
SELECT '5' typ,hou.name "Business Unit",
       hca.cust_account_id,
       acra.org_id org_id,
       hca.ACCOUNT_NUMBER CustomerNumber,
       hp.party_name customername,
          hp.ADDRESS1
       || ','
       || CHR (10)
       || hp.ADDRESS2
       || ','
       || CHR (10)
       || hp.ADDRESS3
          address,
       hp.city city,
       hp.COUNTRY,
       UPPER (acra.invoice_CURRENCY_CODE) currency,
       --acra.receipt_date transactiondate,
   ara.gl_date transactiondate,
       'Refund' trantype,
       acra.trx_number transactionnumber,
       acra.doc_sequence_value vouchernumber,
       NULL termname,
       NULL ponumber,
       acra.comments description,
       acra.invoice_currency_code innvoicecurrency,
       (NVL (ara.amount_applied, 0) * NVL (acra.EXCHANGE_RATE, 1)) debit,
   0 credit,
   'Refund' transactionstatus,
       acra.customer_trx_id,
       acra.EXCHANGE_RATE_TYPE,
       acra.EXCHANGE_DATE,
       acra.EXCHANGE_RATE,
   ara.amount_applied entered_amount
  FROM ra_customer_trx_all acra,
       hz_cust_accounts hca,
       hz_parties hp,
       ar_receivable_applications ara,
       hr_operating_units hou
 WHERE     1 = 1
       AND acra.bill_to_customer_id = hca.cust_account_id
       AND hp.party_id = hca.party_id
   AND ara.applied_payment_schedule_id IN (-8,-3)  -- -8 For refund and -3 for Receipt Write-off
       AND ara.customer_trx_id = acra.customer_trx_id
       AND hou.organization_id = acra.org_id
   AND ara.display = 'Y'
   AND TRUNC (ara.gl_date) BETWEEN NVL (:p_from_date,
                                                  TRUNC (ara.gl_date))
                                         AND NVL (:p_to_date,
                                                  TRUNC (ara.gl_date))
       AND acra.org_id = NVL (:p_org_id, acra.org_id)
       AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION ALL                                       -- Below Query for Receipt Data
SELECT '6' typ,hou.name "Business Unit",
       hca.cust_account_id,
       acra.org_id org_id,
       hca.ACCOUNT_NUMBER CustomerNumber,
       hp.party_name customername,
          hp.ADDRESS1
       || ','
       || CHR (10)
       || hp.ADDRESS2
       || ','
       || CHR (10)
       || hp.ADDRESS3
          address,
       hp.city city,
       hp.COUNTRY,
       UPPER (acra.CURRENCY_CODE) currency,
       acrh.gl_date transactiondate,
   --acra.receipt_date transactiondate,
       'Receipts' trantype,
       acra.receipt_number transactionnumber,
       acra.doc_sequence_value vouchernumber,
       NULL termname,
       NULL ponumber,
       acra.comments description,
       acra.currency_code innvoicecurrency,
       (NVL (acra.amount, 0) * NVL (acra.EXCHANGE_RATE, 1)) debit,
       0 credit,
       'Reversed' transactionstatus,
       acra.cash_receipt_id,
       acra.EXCHANGE_RATE_TYPE,
       acra.EXCHANGE_DATE,
       acra.EXCHANGE_RATE,
       acra.AMOUNT entered_amount
  FROM ar_cash_receipts_all acra,
       hz_cust_accounts hca,
       hz_parties hp,
       ar_payment_schedules_all apsa,
       hr_operating_units hou,
   ar_cash_receipt_history_all acrh
 WHERE     1 = 1
       AND acra.pay_from_customer = hca.cust_account_id
       AND hp.party_id = hca.party_id
       AND apsa.cash_receipt_id = acra.cash_receipt_id
       AND hou.organization_id = acra.org_id
       AND TRUNC (acrh.gl_date) BETWEEN NVL (:p_from_date,
                                                  TRUNC (acrh.gl_date))
                                         AND NVL (:p_to_date,
                                                  TRUNC (acrh.gl_date))
       AND acra.org_id = NVL (:p_org_id, acra.org_id)
       AND hca.account_number = NVL (:p_account_number, hca.account_number)
   AND acrh.status = 'REVERSED'
   AND acra.cash_receipt_id = acrh.cash_receipt_id
   AND     EXISTS (SELECT acrh1.cash_receipt_history_id
 FROM ar_cash_receipt_history_all acrh1
WHERE acra.cash_receipt_id = acrh1.cash_receipt_id
  AND acrh1.status = 'REVERSED'
  AND acrh1.gl_date <= NVL (:p_to_date,TRUNC (acrh1.gl_date))
   ) 
ORDER BY transactiondate, typ