  CREATE OR REPLACE FORCE VIEW "CFP_AGED_DEBTORS_SUMMARY" ("CUSTOMER CODE", "CUSTOMER NAME", "SUB LEDGER", "0-30 DAYS", "31-60 DAYS", "61-90 DAYS", "OVER 90 DAYS", "OUTSTANDING") AS 
  SELECT
  SLT_SUPPLIER AS "SUPPLIER CODE",
  upper(NA_NAME) AS "SUPPLIER NAME",
  SLT_SUB_LEDGER AS "SUB LEDGER",
  sum(case when trunc(sysdate) - slt_date <=30 then slt_money_os ELSE 0 END) as "0-30 DAYS",
  sum(case when trunc(sysdate) - slt_date >30 and trunc(sysdate) - slt_date <=60 then slt_money_os ELSE 0 END) as "31-60 DAYS",
  sum(case when trunc(sysdate) - slt_date >60 and trunc(sysdate) - slt_date <=90 then slt_money_os ELSE 0 END) as "61-90 DAYS",
  sum(case when trunc(sysdate) - slt_date >90 and trunc(sysdate) - slt_date >90 then  slt_money_os ELSE 0 END) as "OVER 90 DAYS",
  sum(slt_money_os) as "OUTSTANDING"
FROM SUB_LEDGER_TRANS,NAMES,TRANSACTION_TYPES,TRANSACTION_SUB_TYPES
WHERE SLT_SUPPLIER = NA_CODE
AND SLT_TRANS_TYPE = TT_TRANS_TYPE
AND (slt_trans_type = TST_TRANS_TYPE(+)
AND SLT_SUB_TYPE_ID = TST_SUB_TYPE_ID(+))
AND slt_sub_ledger  in (select sl_sub_ledger from sub_ledgers where sl_ledger_type = v('P201_LEDGER_TYPE'))
AND SLT_SUB_LEDGER = v('P202_SUB_LEDGER')
--AND ( slt_supplier          = 'WEL0001'
--OR slt_secondary_name       = 'WEL0001')
AND (slt_status            IN ('A', 'P')
OR (slt_status              = 'F'
AND slt_instalment_plan_id IS NOT NULL
AND EXISTS
  (SELECT ip1_status
  FROM instalment_plans
  WHERE ip1_plan_id = slt_instalment_plan_id
  AND ip1_status   IN ('C','A')
  )))
AND NVL(slt_status,'%') LIKE NVL('','%')
AND NVL(TO_CHAR(slt_money_os),'%') LIKE NVL(TO_CHAR(''),'%')
AND slt_supplier IN
  (SELECT na_code FROM names WHERE NVL(na_type,'N') != 'X'
  )
AND slt_instalment_plan_cross_ref IS NULL
GROUP BY SLT_SUPPLIER, NA_NAME,slt_sub_ledger;
 
CREATE OR REPLACE FORCE VIEW "CFP_AGED_DEBTORS_DETAILS" AS 
SELECT SLT_SUPPLIER             AS "CUSTOMER CODE",
  upper(NA_NAME)                AS "CUSTOMER NAME",
  SLT_SUB_LEDGER                AS "SUB LEDGER",
  TRUNC(sysdate)-slt_date       AS "AGEING DAYS INV DATE",
  TRUNC(sysdate)-slt_due_date   AS "AGEING DAYS DUE DATE",
  SLT_OUR_REF_2                 AS "OUR REFERENCE",
  slt_date                      AS "INVOICE DATE",
  slt_their_ref                 AS "THEIR REFERENCE",
  slt_description               AS "DESCRIPTION",
  slt_money_os                  AS "BALANCE",
  slt_trans_type                AS "TRANSACTION TYPE",
  slt_due_date                  AS "DUE DATE",
  TO_CHAR(sysdate,'DD/MM/YYYY') AS "TODAY",
  CASE
    WHEN slt_trans_type IN ( 'SI' , 'SC')
    THEN
      CASE
        WHEN (TRUNC(sysdate) - slt_due_date) <= 30 THEN '0-30 DAYS'
        WHEN (TRUNC(sysdate) - slt_due_date) > 30 AND TRUNC(sysdate)  - slt_due_date <= 60 THEN '31-60 DAYS'
        WHEN (TRUNC(sysdate) - slt_due_date) > 60 AND TRUNC(sysdate)  - slt_due_date <= 90 THEN '61-90 DAYS'
        WHEN (TRUNC(sysdate) - slt_due_date) > 90 THEN 'OVER 90 DAYS'
      END
    ELSE 'CASH'
  END AS "AGEING BY DUE DATE",
   CASE
    WHEN slt_trans_type IN ( 'SI' , 'SC')
    THEN
      CASE
        WHEN (TRUNC(sysdate) - slt_date) <= 30 THEN '0-30 DAYS'
        WHEN (TRUNC(sysdate) - slt_date) > 30 AND TRUNC(sysdate)  - slt_due_date <= 60 THEN '31-60 DAYS'
        WHEN (TRUNC(sysdate) - slt_date) > 60 AND TRUNC(sysdate)  - slt_due_date <= 90 THEN '61-90 DAYS'
        WHEN (TRUNC(sysdate) - slt_date) > 90 THEN 'OVER 90 DAYS'
      END
    ELSE 'CASH'
  END AS "AGEING BY INV DATE"
FROM SUB_LEDGER_TRANS,
  NAMES,
  TRANSACTION_TYPES,
  TRANSACTION_SUB_TYPES
WHERE SLT_SUPPLIER  = NA_CODE
AND SLT_TRANS_TYPE  = TT_TRANS_TYPE
AND (slt_trans_type = TST_TRANS_TYPE(+)
AND SLT_SUB_TYPE_ID = TST_SUB_TYPE_ID(+))
AND slt_sub_ledger IN
  (SELECT sl_sub_ledger
  FROM sub_ledgers
  WHERE sl_ledger_type = 'SALES'
  )
AND (slt_status            IN ('A', 'P')
OR (slt_status              = 'F'
AND slt_instalment_plan_id IS NOT NULL
AND EXISTS
  (SELECT ip1_status
  FROM instalment_plans
  WHERE ip1_plan_id = slt_instalment_plan_id
  AND ip1_status   IN ('C','A')
  )))
AND NVL(slt_status,'%') LIKE NVL('','%')
AND NVL(TO_CHAR(slt_money_os),'%') LIKE NVL(TO_CHAR(''),'%')
AND slt_supplier IN
  (SELECT na_code FROM names WHERE NVL(na_type,'N') != 'X'
  )
AND slt_instalment_plan_cross_ref IS NULL;