SELECT ORG_NAME,
             NAME,
             VENDOR_NAME,
             GLDATE,
             SUM(balance) balance,
             SUM(thirtyDay) thirtyDay,
             SUM(sixtyDay) sixtyDay,
             SUM(ninetyDay) ninetyDay,
             SUM(ohaeDay) ohaeDay
        FROM (SELECT ORG_NAME,
                     NAME,
                     VENDOR_NAME,
                     TO_CHAR(GL_DATE, 'yyyymm') GLDATE,
                     amount - AMOUNT_PAID balance,
                     (CASE
                       WHEN ROUND(SYSDATE - GL_DATE, 0) > 0 AND
                            ROUND(SYSDATE - GL_DATE, 0) < 31 THEN
                        AMOUNT - NVL(AMOUNT_PAID, 0)
                       ELSE
                        0
                     END) AS thirtyDay,
                     (CASE
                       WHEN ROUND(SYSDATE - GL_DATE, 0) >= 31 AND
                            ROUND(SYSDATE - GL_DATE, 0) < 61 THEN
                        AMOUNT - NVL(AMOUNT_PAID, 0)
                       ELSE
                        0
                     END) AS sixtyDay,
                     (CASE
                       WHEN ROUND(SYSDATE - GL_DATE, 0) >= 61 AND
                            ROUND(SYSDATE - GL_DATE, 0) < 91 THEN
                        AMOUNT - NVL(AMOUNT_PAID, 0)
                       ELSE
                        0
                     END) AS ninetyDay,
                     (CASE
                       WHEN ROUND(SYSDATE - GL_DATE, 0) >= 91 AND
                            ROUND(SYSDATE - GL_DATE, 0) < 181 THEN
                        AMOUNT - NVL(AMOUNT_PAID, 0)
                       ELSE
                        0
                     END) AS ohaeDay
                FROM (SELECT AIA.INVOICE_NUM,
                             GL.NAME ORG_NAME,
                             substr(GLCD.name,
                                    instr(GLCD.name, '_', 1, 2) + 1,
                                    instr(GLCD.name, '_', 1, 2) + 15) NAME,
                             NVL(PV.VENDOR_NAME, HP.PARTY_NAME) VENDOR_NAME,
                             AIA.GL_DATE GL_DATE,
                             SUM(AIA.INVOICE_AMOUNT *
                                 nvl(AIA.EXCHANGE_RATE, 1)) AMOUNT,
                             nvl(SUM(AIA.AMOUNT_PAID), 0) AMOUNT_PAID
                        FROM AP_INVOICES_ALL          AIA,
                             po_vendors               PV,
                             hr_operating_units       GLCD,
                             gl_ledgers               gl,
                             AP_PAYMENT_SCHEDULES_ALL APSA,
                             HZ_PARTIES               HP
                       WHERE 1 = 1
                         AND AIA.VENDOR_ID = PV.VENDOR_ID(+)
                         AND AIA.Org_Id = GLCD.organization_id
                         AND GL.LEDGER_ID = GLCD.set_of_books_id
                         AND AIA.INVOICE_ID = APSA.INVOICE_ID
                         AND AIA.PARTY_ID = HP.PARTY_ID
                       GROUP BY AIA.INVOICE_NUM,
                                SEGMENT1,
                                AIA.GL_DATE,
                                GL.NAME,
                                GLCD.NAME,
                                PV.VENDOR_NAME,
                                HP.PARTY_NAME))
       WHERE 1 = 1
       GROUP BY ORG_NAME, NAME, VENDOR_NAME, GLDATE;