IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_PayrollHeader') begin
DROP proc dd_PayrollHeader
end
go
create proc dd_PayrollHeader
-- dd_PayrollHeader '8/26/2016'
@Check_Date as date
AS
declare @Year_Start as date
select @Year_Start = DATEFROMPARTS(year(@Check_Date),1,1)
--get a list of all employees with transactions this year
select
db_name() as 'Company_Code',
pt.CHEKNMBR as 'Advice_Check_Number',
convert(varchar(10),pt.CHEKDATE,101) as 'Check_Pay_Date',
convert(varchar(10),pt.TRXBEGDT,101) as 'Pay_Period_Start_Date',
convert(varchar(10),pt.TRXENDDT,101) as 'Pay_Period_End_Date',
e.EMPLOYID as 'Employee_Code',
e.SOCSCNUM as 'SSN',
e.LASTNAME as 'Last_Name',
e.FRSTNAME as 'First_Name',
ea.ADDRESS1 as 'Address_Line_1',
ea.ADDRESS2 as 'Address_Line_2',
ea.CITY as 'City',
ea.STATE as 'State',
ea.ZIPCODE as 'Postal_Code',
h.GRWGPRN as 'Gross_Pay_Amount',
ph.Gross_Pay_Amount_YTD as 'Gross_Pay_Amount_YTD',
h.NTWPYRN as 'Net_Pay_Amount',
hYTD.Net_Pay_Amount_YTD as Net_Pay_Amount_YTD,
case tax.FDFLGSTS
when 'mar' then 'M'
when 'single' then 'S'
else tax.FDFLGSTS end as 'Federal_Tax_Status',
tax.FEDEXMPT as 'Federal_Allowances', --exemptions
tax.ADFDWHDG as 'Federal_Additional_Amount', --PayrollHistoricalTrx
case tax.FDFLGSTS
when 'mar' then 'M'
when 'single' then 'S'
else tax.FDFLGSTS end as 'State_Tax_Status',
tax.FEDEXMPT as 'State_Allowances',
0 as 'State_Additional_Amount',
Case when h.VOIDED =1 then 'Y' else 'N' end as 'Void_Check_Flag'
from upr00100 e
join upr00102 ea on e.EMPLOYID = ea.EMPLOYID and e.ADRSCODE = ea.ADRSCODE
--one line per employee
--gathers YTD info
join (
select pt.EMPLOYID,
sum(case when pt.PYRLRTYP = 1 then pt.UPRTRXAM else 0 end) as Gross_Pay_Amount_YTD
from upr30300 pt
where pt.CHEKDATE between @Year_Start and @Check_Date
group by pt.EMPLOYID
) ph on ph.EMPLOYID = e.employid
--one line per employee
--gathers pay amount for this check
join (
select pt.EMPLOYID,pt.CHEKNMBR, pt.CHEKDATE,pt.TRXBEGDT, pt.TRXENDDT,
sum(case when pt.PYRLRTYP = 1 then pt.UPRTRXAM else 0 end) as Gross_Pay_Amount
from upr30300 pt
where pt.CHEKDATE = @Check_Date
group by pt.EMPLOYID,pt.CHEKNMBR, pt.CHEKDATE,pt.TRXBEGDT, pt.TRXENDDT
) pt on pt.EMPLOYID = e.employid
--one line per employee
--gathers pay amounts from the header table
join (
select pt.EMPLOYID,
sum(NTWPYRN) as Net_Pay_Amount_YTD
from upr30100 pt
where pt.CHEKDATE between @Year_Start and @Check_Date
group by pt.EMPLOYID
) hYTD on hYTD.EMPLOYID = e.employid
join upr30100 h on h.CHEKNMBR = pt.CHEKNMBR
left join UPR00300 tax on tax.EMPLOYID = e.EMPLOYID
order by 3