select * from (
with
pld as
(select cy_id, campaign_type, employer as giver_id,
SUM(NVL(leadership,0)+NVL(corppledges,0)+NVL(dbpledges,0)+NVL(indpledges,0)+NVL(fundpledges,0)
+NVL(pdpledges,0)+NVL(cashpledges,0)+NVL(creditpledges,0)+NVL(eftpledges,0)) totalpledges
from campaign_totals
group by cy_id, campaign_type, employer),
des as
(select d.cy_id, d.campaign_type, g.employer as giver_id,
sum(nvl(pd_amount,0)+ nvl(cash_amount,0)+ nvl(credit_amount,0)+ nvl(ind_amount,0)+
nvl(corp_amount,0)+ nvl(eft_amount,0)+ nvl(db_amount,0)) totaldesignations
from designations d, givers g
where d.giver_id = g.giver_id
group by d.cy_id, d.campaign_type, g.employer)
select pld.cy_id, pld.campaign_type, pld.giver_id, pld.totalpledges, pld.totalpledges - des.totaldesignations as undesignated
from givers, pld, des
where
givers.giver_id = pld.giver_id
and pld.cy_id = des.cy_id (+)
and pld.campaign_type = des.campaign_type (+)
and pld.giver_id = des.giver_id (+)
and pld.cy_id = 2009
and pld.totalpledges - des.totaldesignations > 0
order by pld.giver_id, pld.cy_id, pld.campaign_type)
Note the line "and pld.cy_id = 2009" toward the bottom of the query limits this to 2009. To see all years, remove the entire line. To see a different year, change the 2009 value to the desired year.
To use this query, simply paste it into the SQL Generator window or run it from the Oracle command line.