Query for AP and AR module

SELECT head, segment4, vendor, invoice_number, invoice_date, accounted_dr,
       accounted_cr
  FROM (SELECT DISTINCT 'AP_INVOICES' AS head, gcc.segment4,
                        aps.vendor_name AS vendor,
                        TO_CHAR (aia.doc_sequence_value) AS invoice_number,
                        TO_CHAR (aia.invoice_date,
                                 'DD-MON-YYYY'
                                ) AS invoice_date,
                        (CASE
                            WHEN CAST (gg.reference_9 AS NUMBER) IS NOT NULL
                               THEN CAST (gg.reference_9 AS NUMBER)
                            ELSE 0
                         END
                        ) AS accounted_dr,
                        (CASE
                            WHEN CAST (gg.reference_10 AS NUMBER) IS NOT NULL
                               THEN CAST (gg.reference_10 AS NUMBER)
                            ELSE 0
                         END
                        ) AS accounted_cr,
                        lv.je_header_id AS je_header_id,
                        lv.je_line_num AS je_line_num, gg.gl_sl_link_id,
                        '' AS TYPE, xteg.entity_code AS status,
                        '' AS subtnumber
                   FROM apps.gl_import_references gg,
                        apps.ap_invoices_all aia,
                        apps.gl_je_lines_v lv,
                        apps.gl_je_headers gjh,
                        apps.gl_code_combinations gcc,
                        apps.xla_transaction_entities_upg xteg,
                        apps.ap_suppliers aps,
                        apps.xla_ae_headers xeh,
                        apps.xla_ae_lines xal,
                        apps.gl_je_batches_headers_v glv
                  WHERE gg.je_header_id = lv.je_header_id
                    AND xteg.entity_code = 'AP_INVOICES'
                    AND gg.je_line_num = lv.je_line_num
                    AND aps.vendor_id = aia.vendor_id
                    AND gg.je_header_id = gjh.je_header_id
                    AND lv.je_header_id = gjh.je_header_id
                    AND gcc.code_combination_id = lv.code_combination_id
                    AND gcc.code_combination_id = xal.code_combination_id
                    AND lv.code_combination_id = xal.code_combination_id
                    AND xal.code_combination_id = gcc.code_combination_id
                    AND gg.je_header_id = glv.je_header_id
                    AND aia.invoice_id = xteg.source_id_int_1
                    AND xteg.entity_id = xeh.entity_id
                    AND xeh.ae_header_id = xal.ae_header_id
                    AND xal.gl_sl_link_id = gg.gl_sl_link_id
                    AND gg.je_batch_id = glv.je_batch_id
                    AND gjh.je_source IN ('Payables')
                    AND gjh.status = 'P'
                    AND TRUNC (gjh.default_effective_date) BETWEEN :i_fromdate
                                                               AND :i_todate
                    AND gcc.segment4 BETWEEN :i_from_code AND :i_to_code
        UNION ALL
        SELECT DISTINCT 'AP_PAYMENTS' AS head, gcc.segment4,
                        aps.vendor_name AS vendor,
                        TO_CHAR (aia.check_number) AS invoice_number,
                        TO_CHAR (aia.check_date,
                                 'DD-MON-YYYY'
                                ) AS invoice_date,
                        (CASE
                            WHEN CAST (gg.reference_9 AS NUMBER) IS NOT NULL
                               THEN CAST (gg.reference_9 AS NUMBER)
                            ELSE 0
                         END
                        ) AS accounted_dr,
                        (CASE
                            WHEN CAST (gg.reference_10 AS NUMBER) IS NOT NULL
                               THEN CAST (gg.reference_10 AS NUMBER)
                            ELSE 0
                         END
                        ) AS accounted_cr,
                        lv.je_header_id AS je_header_id,
                        lv.je_line_num AS je_line_num, gg.gl_sl_link_id,
                        '' AS TYPE, xteg.entity_code AS status,
                        '' AS subtnumber
                   FROM apps.gl_import_references gg,
                        apps.ap_checks_all aia,
                        apps.gl_je_lines_v lv,
                        apps.gl_je_headers gjh,
                        apps.gl_code_combinations gcc,
                        apps.xla_transaction_entities_upg xteg,
                        apps.ap_suppliers aps,
                        apps.xla_ae_headers xeh,
                        apps.xla_ae_lines xal,
                        apps.gl_je_batches_headers_v glv
                  WHERE gg.je_header_id = lv.je_header_id
                    AND xteg.entity_code = 'AP_PAYMENTS'
                    AND gg.je_line_num = lv.je_line_num
                    AND aps.vendor_id = aia.vendor_id
                    AND gg.je_header_id = gjh.je_header_id
                    AND lv.je_header_id = gjh.je_header_id
                    AND gcc.code_combination_id = lv.code_combination_id
                    AND gcc.code_combination_id = xal.code_combination_id
                    AND lv.code_combination_id = xal.code_combination_id
                    AND xal.code_combination_id = gcc.code_combination_id
                    AND gg.je_header_id = glv.je_header_id
                    AND aia.check_id = xteg.source_id_int_1
                    AND xteg.entity_id = xeh.entity_id
                    AND xeh.ae_header_id = xal.ae_header_id
                    AND xal.gl_sl_link_id = gg.gl_sl_link_id
                    AND gg.je_batch_id = glv.je_batch_id
                    AND gjh.je_source IN ('Payables')
                    AND gjh.status = 'P'
                    AND TRUNC (gjh.default_effective_date) BETWEEN :i_fromdate
                                                               AND :i_todate
                    AND gcc.segment4 BETWEEN :i_from_code AND :i_to_code
        UNION ALL
        SELECT DISTINCT 'AR_INVOICES' AS head, gcc.segment4,
                        aps.customer_name AS vendor,
                        TO_CHAR (aia.trx_number) AS invoice_number,
                        TO_CHAR (aia.trx_date, 'DD-MON-YYYY') AS invoice_date,
                        (CASE
                            WHEN CAST (gg.reference_9 AS NUMBER) IS NOT NULL
                               THEN CAST (gg.reference_9 AS NUMBER)
                            ELSE 0
                         END
                        ) AS accounted_dr,
                        (CASE
                            WHEN CAST (gg.reference_10 AS NUMBER) IS NOT NULL
                               THEN CAST (gg.reference_10 AS NUMBER)
                            ELSE 0
                         END
                        ) AS accounted_cr,
                        lv.je_header_id AS je_header_id,
                        lv.je_line_num AS je_line_num, gg.gl_sl_link_id,
                        '' AS TYPE, xteg.entity_code AS status,
                        '' AS subtnumber
                   FROM apps.gl_import_references gg,
                        apps.ra_customer_trx_all aia,
                        apps.gl_je_lines_v lv,
                        apps.gl_je_headers gjh,
                        apps.gl_code_combinations gcc,
                        apps.xla_transaction_entities_upg xteg,
                        apps.ar_customers aps,
                        apps.xla_ae_headers xeh,
                        apps.xla_ae_lines xal,
                        apps.gl_je_batches_headers_v glv
                  WHERE gg.je_header_id = lv.je_header_id
                    AND xteg.entity_code = 'TRANSACTIONS'
                    AND gg.je_line_num = lv.je_line_num
                    AND aps.customer_id = aia.bill_to_customer_id
                    AND gg.je_header_id = gjh.je_header_id
                    AND lv.je_header_id = gjh.je_header_id
                    AND gcc.code_combination_id = lv.code_combination_id
                    AND gcc.code_combination_id = xal.code_combination_id
                    AND lv.code_combination_id = xal.code_combination_id
                    AND xal.code_combination_id = gcc.code_combination_id
                    AND gg.je_header_id = glv.je_header_id
                    AND aia.customer_trx_id = xteg.source_id_int_1
                    AND xteg.entity_id = xeh.entity_id
                    AND xeh.ae_header_id = xal.ae_header_id
                    AND xal.gl_sl_link_id = gg.gl_sl_link_id
                    AND gg.je_batch_id = glv.je_batch_id
                    AND gjh.je_source IN ('Receivables')
                    AND gjh.status = 'P'
                    AND TRUNC (gjh.default_effective_date) BETWEEN :i_fromdate
                                                               AND :i_todate
                    AND gcc.segment4 BETWEEN :i_from_code AND :i_to_code
        UNION ALL
        SELECT DISTINCT 'AR_RECEIPTS' AS head, gcc.segment4,
                        aps.customer_name AS vendor,
                        TO_CHAR (aia.receipt_number) AS invoice_number,
                        TO_CHAR (aia.receipt_date,
                                 'DD-MON-YYYY'
                                ) AS invoice_date,
                        (CASE
                            WHEN CAST (gg.reference_9 AS NUMBER) IS NOT NULL
                               THEN CAST (gg.reference_9 AS NUMBER)
                            ELSE 0
                         END
                        ) AS accounted_dr,
                        (CASE
                            WHEN CAST (gg.reference_10 AS NUMBER) IS NOT NULL
                               THEN CAST (gg.reference_10 AS NUMBER)
                            ELSE 0
                         END
                        ) AS accounted_cr,
                        lv.je_header_id AS je_header_id,
                        lv.je_line_num AS je_line_num, gg.gl_sl_link_id,
                        '' AS TYPE, xteg.entity_code AS status,
                        '' AS subtnumber
                   FROM apps.gl_import_references gg,
                        apps.ar_cash_receipts_all aia,
                        apps.gl_je_lines_v lv,
                        apps.gl_je_headers gjh,
                        apps.gl_code_combinations gcc,
                        apps.xla_transaction_entities_upg xteg,
                        apps.ar_customers aps,
                        apps.xla_ae_headers xeh,
                        apps.xla_ae_lines xal,
                        apps.gl_je_batches_headers_v glv
                  WHERE gg.je_header_id = lv.je_header_id
                    AND xteg.entity_code = 'RECEIPTS'
                    AND gg.je_line_num = lv.je_line_num
                    AND aps.customer_id = aia.pay_from_customer
                    AND gg.je_header_id = gjh.je_header_id
                    AND lv.je_header_id = gjh.je_header_id
                    AND gcc.code_combination_id = lv.code_combination_id
                    AND gcc.code_combination_id = xal.code_combination_id
                    AND lv.code_combination_id = xal.code_combination_id
                    AND xal.code_combination_id = gcc.code_combination_id
                    AND gg.je_header_id = glv.je_header_id
                    AND aia.cash_receipt_id = xteg.source_id_int_1
                    AND xteg.entity_id = xeh.entity_id
                    AND xeh.ae_header_id = xal.ae_header_id
                    AND xal.gl_sl_link_id = gg.gl_sl_link_id
                    AND gg.je_batch_id = glv.je_batch_id
                    AND gjh.je_source IN ('Receivables')
                    AND gjh.status = 'P'
                    AND TRUNC (gjh.default_effective_date) BETWEEN :i_fromdate
                                                               AND :i_todate
                    AND gcc.segment4 BETWEEN :i_from_code AND :i_to_code
        UNION ALL
        /* Manual Sources*/
        SELECT 'MANUAL' AS head, gcc.segment4, NULL AS vendor,
               TO_CHAR (gjh.doc_sequence_value) AS invoice_number,
               TO_CHAR (gjh.default_effective_date,
                        'DD-MON-YYYY'
                       ) AS invoice_date,
               (CASE
                   WHEN lv.accounted_dr IS NOT NULL
                      THEN lv.accounted_dr
                   ELSE 0
                END
               ) AS accounted_dr,
               (CASE
                   WHEN lv.accounted_cr IS NOT NULL
                      THEN lv.accounted_cr
                   ELSE 0
                END
               ) AS accounted_dr,
               lv.je_header_id AS je_header_id, lv.je_line_num AS je_line_num,
               NULL gl_sl_link_id, '' AS TYPE, 'MANUAL' AS status,
               '' AS subtnumber
          FROM apps.gl_je_lines l,
               apps.gl_je_lines_v lv,
               apps.gl_je_headers gjh,
               apps.gl_code_combinations gcc,
               apps.gl_je_batches_headers_v glv
         WHERE l.je_header_id = lv.je_header_id
           AND l.je_line_num = lv.je_line_num
           AND l.je_header_id = gjh.je_header_id
           AND lv.je_header_id = gjh.je_header_id
           AND gcc.code_combination_id = lv.code_combination_id
           AND l.je_header_id = glv.je_header_id
           AND gjh.je_batch_id = glv.je_batch_id
           AND gjh.status = 'P'
           AND gjh.je_source IN ('Manual', 'AutoCopy')
           AND TRUNC (gjh.default_effective_date) BETWEEN :i_fromdate
                                                      AND :i_todate
           AND gcc.segment4 BETWEEN :i_from_code AND :i_to_code)