SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2013) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2013 E, LEDGER2013 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2013 E, LEDGER2013 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2013 E, LEDGER2013 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2012) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2012 E, LEDGER2012 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2012 E, LEDGER2012 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2012 E, LEDGER2012 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2014) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2014 E, LEDGER2014 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2014 E, LEDGER2014 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2014 E, LEDGER2014 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2014) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2014 E, LEDGER2014 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2014 E, LEDGER2014 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2014 E, LEDGER2014 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2015) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2015 E, LEDGER2015 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2015 E, LEDGER2015 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2015 E, LEDGER2015 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2016) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2016 E, LEDGER2016 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2016 E, LEDGER2016 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2016 E, LEDGER2016 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2017) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2017 E, LEDGER2017 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2017 E, LEDGER2017 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2017 E, LEDGER2017 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2018) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2018 E, LEDGER2018 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2018 E, LEDGER2018 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2018 E, LEDGER2018 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2019) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2019 E, LEDGER2019 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2019 E, LEDGER2019 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2019 E, LEDGER2019 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER2020) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2020 E, LEDGER2020 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2020 E, LEDGER2020 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER2020 E, LEDGER2020 L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583' UNION SELECT DMD.RRNO,TO_CHAR(DMD.RDNGDATE,'MON-YYYY') SCHEDULEDDAY, DMD.BILLDATE,DMD.BILLNO, DMD.PRESENTREADING FR, DMD.PREVIOUSREADING IR, NVL(DMD.UNITS,0)UNITS,DMD.BM,DMD.METERSTATUS STATUS, NVL(DMD.OBR,0)+NVL(D1.DISPUTE,0) OBREVENUE, NVL(DMD.OBI,0) OBINTEREST, NVL(DMD.OBT,0) OBTAX, NVL(DMD.OBF,0) AS OBFAC, (NVL(DMD.OBR,0) +NVL(D1.DISPUTE,0)+NVL(DMD.OBI,0) + NVL(DMD.OBT,0) + NVL(DMD.OBF,0) )AS OB_TOTAL, (NVL(DMD.FCAMT,0)) FC_REV, (NVL(DMD.ECAMT,0)) EC_REV, (NVL(DMD.OTHERS,0)) OTHER_REV, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) REVENUE, NVL(DMD.INTEREST,0)INTEREST, NVL(DMD.TAX,0)TAX, NVL(DMD.FPPCA,0)FPPCA, NVL(DMD.FCAMT,0) + NVL(DMD.ECAMT,0) + NVL(DMD.OTHERS,0) + NVL(DMD.INTEREST,0)+ NVL(DMD.TAX,0)+ NVL(DMD.FPPCA,0) AS DEMAND_TOTAL, NVL(CASH.REV,0)+(NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0))++NVL(ADJ.FPPCA,0) COLLECTION_REV, NVL(CASH.INTEREST,0)+NVL(ADJ.INTEREST,0) COLLECTION_INT, NVL(CASH.TAX,0)+NVL(ADJ.TAX,0) COLLECTION_TAX, NVL(CASH.FPPCA,0) COLLECTION_FPPCA, (NVL(CASH.REV,0)+ NVL(CASH.INTEREST,0) + NVL(CASH.TAX,0) + NVL(CASH.FPPCA,0) ) + (NVL(ADJ.REV,0)+NVL(ADJ1.TOTAL,0) + NVL(ADJ.INTEREST,0) + NVL(ADJ.TAX,0)+ NVL(ADJ.FPPCA,0)) COLLECTION_TOTAL, NVL(DMD.CBR,0)+NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) CBREVENUE, NVL(DMD.CBI,0) CBINTEREST, NVL(DMD.CBT,0) CBTAX, NVL(DMD.CBF,0) CBFAC, (NVL(DMD.CBR,0) + NVL(DMD.CBI,0) + NVL(DMD.CBT,0) + NVL(DMD.CBF,0))++NVL(D1.DISPUTE,0)+NVL(DMD.SUSCB,0) AS CB_TOTAL, NVL(D1.DISPUTE,0) DISPUTE,NVL(DMD.SUSCB,0)SUSPANCECB FROM (SELECT TARIFFDCB,RRNO,BILLDATE,RDNGDATE, BILLNO,PRESENTREADING,PREVIOUSREADING,BM,METERSTATUS, (CASE WHEN INSTSTATUS='3' THEN 'LIVE' WHEN INSTSTATUS='0' THEN 'PD' WHEN INSTSTATUS='1' THEN 'LD' WHEN INSTSTATUS='2' THEN 'TD' WHEN INSTSTATUS='-4' THEN 'BILLSTOP' ELSE 'OTHERS' END ) AS STATUS,SOCODE, FEEDERCODE, MRCODE, TCCODE,(UNITS) AS UNITS,(OBA+SUSOB) AS OBR, (OBI) AS OBI,(OBT) AS OBT, (OBF) AS OBF,(FCAMT) AS FCAMT,(ECAMT) AS ECAMT, (PFPEN+OTHERS+AUDITARR+BACKBILLARR+PENAMT+DRAMT-CREDITREBATE-DL_ADJ_GIVEN) AS OTHERS,(INTEREST-OLDINTEREST) AS INTEREST,(TAX) AS TAX,(FPPCA) AS FPPCA,(CBA) AS CBR,(CBI) AS CBI,(CBT) AS CBT, (CBF) AS CBF, (SUSCB) AS SUSCB FROM LEDGER) DMD, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER E, LEDGER L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR(E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (0,1) GROUP BY L.TARIFFDCB, L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) CASH, (SELECT L.RRNO, L.TARIFFDCB,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.REVENUE+E.ARREARS) AS REV,SUM(E.INTEREST) AS INTEREST,SUM(E.TAX) AS TAX,SUM(E.FPPCA) AS FPPCA,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER E, LEDGER L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (2,3,4,5,6,7,9,10, 11, 12,13) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') ) ADJ, (SELECT L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY') MONTH,SUM(E.AMOUNT) AS TOTAL FROM CASHMASTER E, LEDGER L WHERE E.RRNO=L.RRNO AND TO_CHAR(L.RDNGDATE, 'MMYYYY')=TO_CHAR (E.RECDATE, 'MMYYYY') AND E.CASHTYPE IN (8) GROUP BY L.TARIFFDCB,L.RRNO,TO_CHAR(E.RECDATE, 'MON-YYYY')) ADJ1, (SELECT ZA.RRNO, ZA.TARIFFDCB, SUM(ZA.AMOUNT) AS DISPUTE FROM DISPUTECASES ZA, MASTER ZB WHERE ZA.RRNO=ZB.RRNO GROUP BY ZA.TARIFFDCB, ZA.RRNO ) D1 WHERE DMD.TARIFFDCB=CASH.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ.TARIFFDCB(+) AND DMD.TARIFFDCB=ADJ1.TARIFFDCB(+) AND DMD.TARIFFDCB=D1.TARIFFDCB(+) AND DMD.RRNO=CASH.RRNO(+) AND DMD.RRNO=ADJ.RRNO(+) AND DMD.RRNO=ADJ1.RRNO(+) AND DMD.RRNO=D1.RRNO(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=CASH.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ.MONTH(+) AND TO_CHAR(DMD.RDNGDATE,'MON-YYYY')=ADJ1.MONTH(+) AND DMD.RRNO LIKE 'HDHL55583'