Payroll Exemption Queries

Exemptions vs. Federal Gross

Select emp_id,em.display_name,run_no,session_no,trans_no,trx_date,def_value GROSS,
 (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id='FEDGRS')  FEDGRS,
 (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id in
    (select (select def_id from prtcompdef where LINK_ID=(select link_id from prtcompdef cd where cd.def_id=cdx.def_id1) 
     and def_type='D') from prtcompdefxref cdx where def_id2='FEDGRS') )  FEDdedExm,
def_value- (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id='FEDGRS') FEDEEXM_Actual
 from prtempdefhst eh
inner join prtemployee em on (em.emp_id = eh.emp_id)
where def_id='GROSS' and trx_date>='1/1/6'
and
 (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id in
    (select (select def_id from prtcompdef where LINK_ID=(select link_id from prtcompdef cd where cd.def_id=cdx.def_id1) 
     and def_type='D') from prtcompdefxref cdx where def_id2='FEDGRS') )  is not null
and
 (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id in
    (select (select def_id from prtcompdef where LINK_ID=(select link_id from prtcompdef cd where cd.def_id=cdx.def_id1) 
     and def_type='D') from prtcompdefxref cdx where def_id2='FEDGRS') ) <>
 def_value- (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id='FEDGRS')
order by emp_id,em.display_name

Exemptions vs SS Gross

Select emp_id,em.display_name,run_no,session_no,trans_no,trx_date,def_value GROSS,
 (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id='SSGROSS')  SSGROSS,
 (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id in
    (select (select def_id from prtcompdef where LINK_ID=(select link_id from prtcompdef cd where cd.def_id=cdx.def_id1) 
     and def_type='D') from prtcompdefxref cdx where def_id2='SSGROSS') )  SSdedExm,
def_value- (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id='SSGROSS') SSEEXM_Actual
 from prtempdefhst eh
inner join prtemployee em on (em.emp_id = eh.emp_id)
where def_id='GROSS' and trx_date>='1/1/6'
and
 (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id in
    (select (select def_id from prtcompdef where LINK_ID=(select link_id from prtcompdef cd where cd.def_id=cdx.def_id1) 
     and def_type='D') from prtcompdefxref cdx where def_id2='SSGROSS') )  is not null
and
 (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id in
    (select (select def_id from prtcompdef where LINK_ID=(select link_id from prtcompdef cd where cd.def_id=cdx.def_id1) 
     and def_type='D') from prtcompdefxref cdx where def_id2='SSGROSS') ) <>
 def_value- (select sum(def_value) from prtempdefhst e1 where e1.session_no=eh.session_no and e1.trans_no=eh.trans_no
   and e1.def_id='SSGROSS')
order by emp_id,em.display_name

See Exemption Deductions List