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 ) ;
VLD_CMN_INCOME
最新推荐文章于 2021-03-11 23:22:55 发布