VLD_UNCOLLECTED_INCOME_DAILY

WITH P2PUNCOLLECT AS ( SELECT INV.LOANER_ID USER_ID, SUM( CASE WHEN TRUNC(END_AT)< = TRUNC(SYSDATE -1) THEN INTEREST ELSE （TRUNC(SYSDATE-1)-ADD_MONTHS(TRUNC(END_AT),-1)）/(TRUNC(END_AT)-ADD_MONTHS(TRUNC(END_AT),-1))*INTEREST END ) UNCOLLECTED FROM BDL.COLLECTION_PLANS_H CP, BDL.INVESTMENTS_H INV, BDL.CMN_PRODUCTS_H P WHERE CP.INVESTMENT_ID = INV.ID AND INV.PRODUCT_ID = P.PRODUCT_ID AND CP.DW_BEGIN_DATE < = TRUNC(SYSDATE-1) AND CP.DW_END_DATE > TRUNC(SYSDATE-1) AND INV.DW_BEGIN_DATE < = TRUNC(SYSDATE-1) AND INV.DW_END_DATE > TRUNC(SYSDATE-1) AND P.DW_BEGIN_DATE < = TRUNC(SYSDATE-1) AND P.DW_END_DATE > TRUNC(SYSDATE-1) AND CP.STATUS NOT IN ( 'PAID', 'COMP_DONE','PREPAID','TRANSFER' ) AND P.ITEM = 'P2P' AND P.PRODUCT_CATEGORY = 'AED' AND ADD_MONTHS(END_AT,-1)< =TRUNC(SYSDATE-1) GROUP BY INV.LOANER_ID ) SELECT COUNT(1) FROM ( SELECT USER_ID,TRUNC(AED_UNCOLLECTED_INCOME) FROM IDL.UNCOLLECTED_INCOME_DAILY WHERE STAT_DATE = TRUNC(SYSDATE -1) AND AED_UNCOLLECTED_INCOME > 0 MINUS SELECT USER_ID, TRUNC(ROUND(UNCOLLECTED,2)) FROM P2PUNCOLLECT );