SELECT  TO_CHAR(trunc(cc.birthdate,'YEAR'),'DD-MON-YYYY') "# of Members by Year of Birth"
        ,count(m.contactid)
        ,'N/A' "Total Employer Units"
        ,'N/A' "Total Employer $ Volume"
        ,'N/A' "Total Employer $ Life Premiums"
        ,NVL(SUM(p.units),0)  "Total Employee Units"
        ,SUM(NVL((CASE WHEN  agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 50 AND 59 THEN agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE)  * p.units * 7000
               WHEN  agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 60 AND 64 THEN agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE)  * p.units * 6000
               WHEN  agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 65 AND 69 THEN agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE)  * p.units * 5000
               WHEN  agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 70 AND 74 THEN agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE)  * p.units * 4000
               WHEN  agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 75 AND 79 THEN agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE)  * p.units * 3000
               WHEN  agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 80 AND 84 THEN agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE)  * p.units * 2000
               WHEN  agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 85 AND 89 THEN agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE)  * p.units * 1000
               WHEN  agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE) > = 90 THEN agepkg_mis.ageyears(TRUNC(cc.birthdate,'YEAR'),SYSDATE)             * p.units * 0
          END),0)) "Total Employee $ Volume"
          
        ,NVL((SUM(p.units) * SUM(p.unit_cost)),0)  "Total Employee Life Premiums"
        ,'N/A' "Total Family(Dependant) units"
        ,'N/A' "Total Family(Dependant) volume"
        ,'N/A' "Tot Family(Dependant) premiums"
FROM    pri_mis p, member_mis m, contactv_con cc
WHERE   p.memberid=M.MEMBERID
AND     m.contactid=cc.contactid
AND     cc.deathdate is null
AND     nvl(p.state,'Accepted')='Accepted'
GROUP BY  ROLLUP(trunc(cc.birthdate,'YEAR'))
UNION 
SELECT   
        TO_CHAR(trunc(cc.birthdate,'YEAR'),'DD-MON-YYYY') "# of Members by Year of Birth" 
        ,count(cc.contactid)
        ,'N/A' "Total Employer Units"
        ,'N/A' "Total Employer $ Volume"
        ,'N/A' "Total Employer $ Life Premiums"
        ,NVl(SUM(pri.insuranceunits),0)  "Total Employee Units"
        ,SUM(NVL((CASE WHEN  agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 50 AND 59 THEN agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE)  * pri.insuranceunits * 7000
               WHEN  agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 60 AND 64 THEN agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE)  * pri.insuranceunits * 6000
               WHEN  agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 65 AND 69 THEN agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE)  * pri.insuranceunits * 5000
               WHEN  agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 70 AND 74 THEN agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE)  * pri.insuranceunits * 4000
               WHEN  agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 75 AND 79 THEN agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE)  * pri.insuranceunits * 3000
               WHEN  agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 80 AND 84 THEN agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE)  * pri.insuranceunits * 2000
               WHEN  agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE) BETWEEN 85 AND 89 THEN agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE)  * pri.insuranceunits * 1000
               WHEN  agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE) > = 90 THEN agepkg_mis.ageyears(trunc(cc.birthdate,'YEAR'),SYSDATE)             * pri.insuranceunits * 0
          END),0)) "Total Employee $ Volume" 
        ,NVL(SUM(pri.insuranceunits),0)*2 "Total Employee Life Premiums"
        ,'N/A' "Total Family(Dependant) units"
        ,'N/A' "Total Family(Dependant) volume"
        ,'N/A' "Tot Family(Dependant) premiums"
FROM    rmdeduction pri, rmperson rp, contactv_con cc, 
(SELECT personid, deathdate FROM rmpersonaldata) rpd
WHERE   deductioncode='PRI'
AND     pri.personid=rp.id
AND     pri.personid=rpd.personid(+)
AND     rpd.deathdate is null
AND     rp.contactid=cc.contactid
AND     cc.contactid IN (SELECT  m.contactid
                        FROM member_mis m, pri_mis p
                            WHERE   p.memberid = m.memberid
                                AND     NVL(p.state,'Accepted')  = 'Accepted'
                                AND     cc.deathdate IS NULL)
GROUP BY  ROLLUP(trunc(cc.birthdate,'YEAR'))                                
--Order By trunc(cc.birthdate,'YEAR')
;