AR Aging — Overdue Receivables

WITH ar_open AS (
  SELECT it.CompanyCode,
         it.Customer,
         it.GLAccount                                AS recon_account,
         je.AccountingDocument,
         je.FiscalYear,
         it.AmountInCompanyCodeCurrency              AS open_amount,
         it.PaymentTerms,
         it.NetPaymentDays,
         ADD_DAYS(it.DocumentItemDate,
                  TO_INTEGER(it.NetPaymentDays))      AS net_due_date,
         DAYS_BETWEEN(ADD_DAYS(it.DocumentItemDate,
                  TO_INTEGER(it.NetPaymentDays)),
                  :P_KEY_DATE)                        AS days_overdue
  FROM   I_JournalEntry            AS je
  JOIN   I_OperationalAcctgDocItem AS it
         ON  je.CompanyCode        = it.CompanyCode
         AND je.AccountingDocument = it.AccountingDocument
         AND je.FiscalYear         = it.FiscalYear
  WHERE  it.FinancialAccountType   = 'D'             -- D = customer (Debitor)
    AND  it.CompanyCode            = :P_COMPANY_CODE
    AND  it.ClearingDate           IS NULL           -- open as of the key date
)
SELECT Customer,
       SUM(CASE WHEN days_overdue <= 0                  THEN open_amount ELSE 0 END) AS current_not_due,
       SUM(CASE WHEN days_overdue BETWEEN  1 AND 30      THEN open_amount ELSE 0 END) AS d_1_30,
       SUM(CASE WHEN days_overdue BETWEEN 31 AND 60      THEN open_amount ELSE 0 END) AS d_31_60,
       SUM(CASE WHEN days_overdue > 60                   THEN open_amount ELSE 0 END) AS d_60_plus,
       SUM(open_amount)                                                              AS total_open
FROM   ar_open
GROUP  BY Customer
ORDER  BY d_60_plus DESC;