VLD_CMN_INCOME

WITH INTEREST AS 
( SELECT CASE WHEN p.product_category = 'JLD' THEN 'AED' ELSE p.product_category END PRODUCT_CATEGORY , 
CASE WHEN P.PRODUCT_TYPE = 'TRANSFER_REQUEST' AND P.PRODUCT_CATEGORY IN ('CH','JYT') THEN CP.AMOUNT - P.PRICE - CR.PREPAID_PENAL_VALUE - CR.OVERDUE_PENAL_VALUE 
WHEN P.PRODUCT_CATEGORY = 'ZJSX' THEN CP.AMOUNT - CP.PRINCIPAL 
ELSE cr.interest END INTEREST 
FROM ODL.COLLECTION_PLANS CP, 
ODL.CMN_COLLECTION_RECORDS CR, 
ODL.INVESTMENTS INV, 
ODL.CMN_PRODUCTS P 
WHERE CP.ID = CR.COLLECTION_PLAN_ID 
AND CR.INVESTMENT_ID = INV.ID 
AND INV.PRODUCT_ID = P.PRODUCT_ID 
AND CR.COLLECTION_TIME < TRUNC(SYSDATE) 
UNION ALL 
SELECT 'LJB' PRODUCT_CATEGORY, 
NVL(YESTERDAY_YIELD,0) INTEREST 
FROM ODL.YEB_INCOME_RECORD 
WHERE UPDATED_AT < TRUNC(SYSDATE) 
UNION ALL SELECT 'LHB' PRODUCT_CATEGORY, NVL(YESTERDAY_INCOME,0) INTEREST 
FROM ODL.BUS_WSQB_INCOME 
WHERE STATUS = 'DISPATCHED' 
AND UPDATED_AT < TRUNC(SYSDATE) ) 
SELECT COUNT(1) FROM 
( SELECT PRODUCT_CATEGORY, ROUND(SUM(AMOUNT),0) 
FROM ODL.CMN_INCOME 
WHERE TYPE = 'INTEREST' 
AND AMOUNT > 0 
GROUP BY PRODUCT_CATEGORY 
MINUS 
SELECT PRODUCT_CATEGORY, ROUND(SUM(INTEREST),0) 
FROM INTEREST 
WHERE INTEREST > 0 
GROUP BY PRODUCT_CATEGORY ) ;