Creditor Combine Report

/* Formatted on 7/4/2014 3:23:49 PM (QP5 v5.115.810.9015) */
SELECT vendor_type_lookup_code,
       org_id,
       vendor_num,
       vendor_name,
       vendor_site_code,
       code_combination,
       NVL (SUM(DECODE (invoice_type_lookup_code,
                        'CREDIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'DEBIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'STANDARD',
                        invoice_amount + (paid_amount + adusted_amount)
                )),
            0
       )
       + NVL (SUM(DECODE (invoice_type_lookup_code,
                          'PREPAYMENT',
                          (invoice_amount + (paid_amount + adjusted_amount))
                  )),
              0
         )
          "AMOUNT",
       NVL (SUM(DECODE (invoice_type_lookup_code,
                        'CREDIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'DEBIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'STANDARD',
                        invoice_amount + (paid_amount + adusted_amount)
                )),
            0
       )
          "LIABILITY",
       NVL (SUM(DECODE (invoice_type_lookup_code,
                        'PREPAYMENT',
                        (invoice_amount + (paid_amount + adjusted_amount))
                )),
            0
       )
          "PREPAYMENT"
FROM (SELECT DISTINCT
             ai.vendor_id,
             aps.vendor_name,
             aps.segment1 "VENDOR_NUM",
             aps.vendor_type_lookup_code,
             apss1.vendor_site_code,
             ai.org_id,
             ai.invoice_id,
             ai.invoice_num,
             ai.invoice_date,
             ai.invoice_type_lookup_code,
             ROUND (DECODE (ai.invoice_type_lookup_code,
                            'PREPAYMENT',
                            ai.invoice_amount * NVL (ai.exchange_rate, 1),
                            ai.invoice_amount * NVL (ai.exchange_rate, 1) * -1
                    ),
                    2
             )
                invoice_amount,
             ai.payment_status_flag,
             NVL ( (SELECT ROUND (SUM(NVL (aip.amount, 0)
                                      * NVL (ai.exchange_rate, 1)),
                                  2
                           )
                              amount_paid
                    FROM apps.ap_invoice_payments_all aip
                    WHERE     aip.invoice_id = ai.invoice_id
                          AND TRUNC (aip.accounting_date) <= :p89_date
                          AND ai.invoice_type_lookup_code <> 'PREPAYMENT'),
                  0
             )
                paid_amount,
             NVL (cc.code_combination,
                  (SELECT gk.concatenated_segments
                   FROM apps.gl_code_combinations_kfv gk
                   WHERE gk.code_combination_id =
                            ai.accts_pay_code_combination_id)
             )
                code_combination,
             ROUND ( (SELECT NVL (SUM (amount) * -1, 0)
                      FROM apps.ap_invoice_lines_all aia
                      WHERE     aia.invoice_id = ai.invoice_id
                            AND aia.org_id = ai.org_id
                            AND aia.line_type_lookup_code = 'PREPAY'
                            AND TRUNC (aia.accounting_date) <= :p89_date)
                    * NVL (ai.exchange_rate, 1),
                    2
             )
                adusted_amount,
             ROUND ( (SELECT NVL (SUM (amount), 0)
                      FROM apps.ap_invoice_lines_all aim
                      WHERE     aim.prepay_invoice_id = ai.invoice_id
                            AND aim.org_id = ai.org_id
                            AND TRUNC (aim.accounting_date) <= :p89_date)
                    * NVL (ai.exchange_rate, 1),
                    2
             )
                adjusted_amount
      FROM apps.ap_invoices_all ai,
           apps.ap_suppliers aps,
           apps.ap_supplier_sites_all apss1,
           apps.ap_invoice_distributions_all ad,
           apps.ap_invoice_lines_all ail,
           (SELECT DISTINCT
                   xd.applied_to_source_id_num_1,
                   DECODE (xal.accounting_class_code,
                      'PREPAID_EXPENSE', 'PREPAYMENT',
                      xal.accounting_class_code)
                      "CODE",
                      gcc.segment1
                   || '.'
                   || gcc.segment2
                   || '.'
                   || gcc.segment3
                   || '.'
                   || gcc.segment4
                   || '.'
                   || gcc.segment5
                   || '.'
                   || gcc.segment6
                   || '.'
                   || gcc.segment7
                      code_combination
            FROM apps.xla_ae_lines xal,
                 apps.xla_distribution_links xd,
                 apps.gl_code_combinations gcc
            WHERE xd.ae_header_id = xal.ae_header_id
                  AND xd.ae_line_num = xal.ae_line_num
                  AND xal.accounting_class_code IN
                           ('LIABILITY', 'PREPAID_EXPENSE')
                  AND xal.code_combination_id = gcc.code_combination_id) cc
      WHERE     ai.invoice_id = ad.invoice_id
            AND ad.match_status_flag = 'A'
            AND ai.vendor_id = aps.vendor_id
            AND ai.vendor_id = apss1.vendor_id
            AND ai.vendor_site_id = apss1.vendor_site_id
            AND ai.org_id = apss1.org_id
            AND ai.invoice_id = cc.applied_to_source_id_num_1(+)
            AND ai.invoice_id = ail.invoice_id
            AND ai.org_id = ail.org_id
            AND DECODE (ai.invoice_type_lookup_code,
                  'PREPAYMENT', ai.invoice_type_lookup_code,
                  'LIABILITY') = cc.code(+)
            AND ad.line_type_lookup_code <> 'AWT'
            AND ai.org_id = :p89_org_id
            AND NOT (ai.invoice_type_lookup_code = 'PREPAYMENT'
                     AND payment_status_flag = 'N')
            AND TRUNC (ad.accounting_date) <= :p89_date
      ORDER BY aps.segment1)
GROUP BY vendor_type_lookup_code,
         org_id,
         vendor_num,
         vendor_name,
         vendor_site_code,
         code_combination