Employee Charges By Account w/ Leave Usage

gistfile1.sql

select 
	e.last_name, 
	e.first_name, 
	e.display_employee, 
	e.other_string3, 
	ld3.short_description proj_desc, 
	ld1.ld1 account_id, 
	ld1.short_description account_name, 
	ld3.ld1 project, 
	sum(tso.pay) as salary, 
	sum(tso.calc_value2) as fringe, 
	sum(tso.calc_value4) as fringe_icr,
	NULL as hours,
	NULL as pay_code,
	NULL as work_dt
from employee e
    join time_sheet_output tso on e.employee = tso.employee
	join ld3 on tso.ld3 = ld3.ld1
    join ld1 on tso.ld1 = ld1.ld1
    join employee_periods ep on e.employee = ep.employee
where tso.pay_code not in ('SICK', 'ANNUAL', 'FURLOUGH')
	and tso.work_dt between ld1.eff_dt and ld1.end_eff_dt
	and tso.work_dt between ld3.eff_dt and ld3.end_eff_dt
	and tso.transaction_type in (30, 40)
	and tso.employee_period_version = ep.calc_emp_period_version
	and tso.work_dt between e.eff_dt and e.end_eff_dt
	and tso.work_dt between to_date('2011-12-01', 'yyyy-mm-dd') and to_date('2011-12-31', 'yyyy-mm-dd') 
	and (to_date('2011-12-01', 'yyyy-mm-dd') between ep.pp_begin and ep.pp_end
		or to_date('2011-12-31', 'yyyy-mm-dd')  between ep.pp_begin and ep.pp_end
		or ep.pp_begin between to_date('2011-12-01', 'yyyy-mm-dd') and to_date('2011-12-31', 'yyyy-mm-dd'))
group by e.last_name, e.first_name, e.display_employee, ld1.ld1, ld1.short_description, ld3.short_description, ld3.ld1, e.other_string3
UNION
select 
	e.last_name, 
	e.first_name, 
	e.display_employee, 
	e.other_string3, 
	NULL as proj_desc,
	NULL as account_id,
	NULL as account_name,
	NULL as project,
	NULL as salary,
	NULL as fringe,
	NULL as fringe_icr,
	tord.hours,
	tord.pay_code,
	tord.work_dt
from employee e
	join time_off_request tor on e.employee = tor.employee
	join time_off_request_detail tord on tor.time_off_request = tord.time_off_request
where tord.work_dt between to_date('2011-12-01', 'yyyy-mm-dd') and to_date('2011-12-31', 'yyyy-mm-dd')
order by last_name, first_name, display_employee, proj_desc;