IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dd_PayrollDetail'
AND type = 'P')
DROP PROCEDURE dd_PayrollDetail
GO
Create proc dd_PayrollDetail
-- dd_PayrollDetail '8/26/2016','asdf'
@Check_Date as date,
@Comment varchar(max) --pass in comments to be added to the output. More details below
AS
declare @Year_Start as date
select @Year_Start = DATEFROMPARTS(year(@Check_Date),1,1)
--====================================================================================================
--Parse the comment
--The comment is coming from Excel, so line breaks will be a single CHAR(10)
--This code breaks the comment after each CHAR(10) and places it in a table. This table will
-- be output at the end of the result set
--====================================================================================================
declare @pos as int
--declare a table for the result set
declare @cmt table (col1 varchar(100),RowID int identity)
--this aids in readability
declare @cr varchar(1)
set @cr = char(10)
--loop through the comment, break at each line break. Add the lines to the @cmt table variable
while CHARINDEX(@cr,@comment) > 0 begin
--find the next line break
set @pos = CHARINDEX(@cr,@comment)
--insert the line into the @cmt table variable
insert into @cmt(col1) values (substring(@comment,1,@pos - 1))
--remove the line from the input string
set @comment = substring(@comment,@pos + 1,99999)
end
--finally, add the remainder to @cmt
insert into @cmt(col1) values (@comment)
--the client requires that the header-level tax withholding be placed into the output.
--this query grabs the federal withholding and places it into a temp table, it will be added at the bottom of the output
--one line per employee/check for the federal withholding
select pth.EMPLOYID, pth.CHEKNMBR, pth.CHEKDATE, pth.FDWDGPRN as Period_Amount, pt.Period_Amount_YTD, 'Fed Withholding' as descr , 'FDWDGPRN' as Detail_Code
into #fed
from upr30100 pth
left join (
select pth.EMPLOYID,
sum(pth.FDWDGPRN) as Period_Amount_YTD
from upr30100 pth
where pth.CHEKDATE between @Year_Start and @Check_Date
group by pth.EMPLOYID
) pt on pt.EMPLOYID = pth.employid
where pth.CHEKDATE = @Check_Date
union all
--one line per employee/check for the medicare tax withholding
select pth.EMPLOYID, pth.CHEKNMBR, pth.CHEKDATE, pth.FICAMWPR as Period_Amount , pt.Period_Amount_YTD, 'Fed Medicare Tax' as descr , 'FICAMWPR' as Detail_Code
from upr30100 pth
left join (
select pth.EMPLOYID,
sum(pth.FICAMWPR) as Period_Amount_YTD
from upr30100 pth
where pth.CHEKDATE between @Year_Start and @Check_Date
group by pth.EMPLOYID
) pt on pt.EMPLOYID = pth.employid
where pth.CHEKDATE = @Check_Date
union all
--one line per employee/check for the SS withholding
select pth.EMPLOYID, pth.CHEKNMBR, pth.CHEKDATE, pth.FCASWPR as Period_Amount , pt.Period_Amount_YTD, 'Fed Soc Sec Tax' as descr , 'FCASWPR' as Detail_Code
from upr30100 pth
left join (
select pth.EMPLOYID,
sum(pth.FCASWPR) as Period_Amount_YTD
from upr30100 pth
where pth.CHEKDATE between @Year_Start and @Check_Date
group by pth.EMPLOYID
) pt on pt.EMPLOYID = pth.employid
where pth.CHEKDATE = @Check_Date
--==================================================================================
-- Main output
-- there are 4 queries UNIONed together here. The first is from UPR30300 and reports
-- the PAYROLCDs for each employee
--==================================================================================
select
DB_NAME() as 'Company_Code',
e.SOCSCNUM as 'SSN',
isnull(pt.CHEKNMBR,'') as 'Advice_Check_Number',
case when pt.CHEKDATE is null then '' else convert(varchar(10),pt.chekdate,101) end as 'Check_Pay_Date',
isnull(pt.Period_Amount,0) as 'Period_Amount',
pth.YTD_Amount,
isnull(pt.Period_Hours,0) as Period_Hours,
YTD_Hours as 'YTD_Hours',
isnull(PAYRATE,0) as 'Rate',
isnull(Period_Hours,0) as 'Units',
case pt.PYRLRTYP
when 1 then dsc.DSCRIPTN
when 2 then rtrim(pth.PAYROLCD) + ' - ' + dsc.DSCRIPTN
when 3 then rtrim(pth.PAYROLCD) + ' - ' + dsc.DSCRIPTN
when 4 then dsc.DSCRIPTN
when 5 then dsc.DSCRIPTN
end as 'Description',
pth.PAYROLCD as 'Detail_Code',
dsc.Detail_Type as 'Detail_Type',
pt.PYRLRTYP as sort
INTO #tmp
from upr00100 e
join (
--HEADER YTD
select pt.EMPLOYID,pt.PAYROLCD,pt.PYRLRTYP,
sum(pt.UNTSTOPY) as YTD_Hours,
sum(pt.UPRTRXAM) as YTD_Amount
from upr30300 pt
where pt.CHEKDATE between @Year_Start and @Check_Date
group by pt.EMPLOYID,pt.PAYROLCD, pt.PYRLRTYP
) pth on pth.EMPLOYID = e.employid
join (
--DETAIL THIS PERIOD
select pt.EMPLOYID,pt.CHEKNMBR, pt.CHEKDATE,pt.PAYROLCD,pt.PYRLRTYP,pt.PAYRATE,
sum(pt.UNTSTOPY) as Period_Hours,
sum(pt.UPRTRXAM) as Period_Amount
from upr30300 pt
where pt.CHEKDATE = @Check_Date
group by pt.EMPLOYID,pt.CHEKNMBR, pt.CHEKDATE,pt.PAYROLCD, pt.PYRLRTYP, pt.PAYRATE
) pt on pt.EMPLOYID = e.employid and pt.PAYROLCD = pth.PAYROLCD and pt.PYRLRTYP = pth.PYRLRTYP
left join (
--PAY CODE DESCRIPTIONS
select PAYRCORD, 1 as PYRLRTYP, DSCRIPTN, 'EARNINGS' as Detail_Type
from upr40600
union all
select DEDUCTON, 2 as PYRLRTYP, DSCRIPTN,
CASE WHEN SFRFEDTX = 0 THEN 'POSTTAX_DEDUCTION' ELSE 'PRETAX_DEDUCTION' END as Detail_Type
from upr40900
union all
select BENEFIT, 3 as PYRLRTYP, DSCRIPTN, 'EMPLOYER_CONTRIBUTION' as Detail_Type
from upr40800
union all
select statecd, 4 as PYRLRTYP, STATENAM, 'TAX' as Detail_Type
from upr41100
union all
select localtax, 5 as PYRLRTYP, DSCRIPTN, 'TAX' as Detail_Type
from upr41400
) dsc on dsc.PAYRCORD = pth.PAYROLCD and dsc.PYRLRTYP = pth.PYRLRTYP
union all
--==================================================================================
-- add in all the header level deductions that are not in the detail file
--==================================================================================
select
DB_NAME() as 'Company_Code',
e.SOCSCNUM as 'SSN',
isnull(pth.CHEKNMBR,'') as 'Advice_Check_Number',
case when pth.CHEKDATE is null then '' else convert(varchar(10),pth.chekdate,101) end as 'Check_Pay_Date',
isnull(pth.Period_Amount,0) as 'Period_Amount',
pth.Period_Amount_YTD,
0 as Period_Hours,
0 as 'YTD_Hours',
0 as 'Rate',
0 as 'Units',
pth.descr as 'Description', --dental ins
pth.Detail_Code as 'Detail_Code',
'TAX' as 'Detail_Type',
'10' as sort
from upr00100 e
join #fed pth on pth.EMPLOYID = e.EMPLOYID
union all
--==================================================================================
-- add in DIRECT DEPOSIT info
--==================================================================================
select
DB_NAME() as 'Company_Code',
e.SOCSCNUM as 'SSN',
isnull(pth.CHEKNMBR,'') as 'Advice_Check_Number',
case when pth.CHEKDATE is null then '' else convert(varchar(10),pth.chekdate,101) end as 'Check_Pay_Date',
isnull(dd.DDAMTDLR,0) as 'Period_Amount',
0 as Period_Amount_YTD,
0 as Period_Hours,
0 as 'YTD_Hours',
0 as 'Rate',
0 as 'Units',
CASE DDTRANS WHEN 22 THEN 'CHECKING'
WHEN 32 THEN 'SAVINGS'
end
+ ':' + RTRIM(DD.DDTRANUM) + ':' + rtrim(dd.DDACTNUM) as 'Description',
'DD' as 'Detail_Code',
'DIRECT_DEPOSIT' as 'Detail_Type',
15 as sort
from upr00100 e
join DD30101 dd on dd.EMPLOYID = e.EMPLOYID
join upr30100 pth on pth.CHEKNMBR = dd.CHEKNMBR
WHERE DD.DDTRANS IN (22,32)
and pth.CHEKDATE = @Check_Date
union all
--==================================================================================
-- add in COMMENT info
--==================================================================================
select
DB_NAME() as 'Company_Code',
e.SOCSCNUM as 'SSN',
'' as 'Advice_Check_Number',
'' as 'Check_Pay_Date',
0 as 'Period_Amount',
0 as Period_Amount_YTD,
0 as Period_Hours,
0 as 'YTD_Hours',
0 as 'Rate',
0 as 'Units',
c.col1 as 'Description',
'COMMENT' as 'Detail_Code',
'CUSTOM2' as 'Detail_Type',
20 + c.RowID as sort
--from upr00100 e
-- join @cmt c on 1=1
from upr00100 e
join upr30100 pth on pth.employid = e.employid
join @cmt c on 1=1
-- where pth.chekdate between …
where pth.chekdate = @Check_Date
--The client requires that the check number and check date be fill in completely. Since we're only outputting one check per
--employee, this query will fill in those fields
update #tmp set
Advice_Check_Number = case when t.Advice_Check_Number = '' then a.Advice_Check_Number else t.Advice_Check_Number end,
check_pay_date = case when t.check_pay_date = '' then a.check_pay_date else t.check_pay_date end
from #tmp t
join (
select ssn, max(Advice_Check_Number) as Advice_Check_Number, max(check_pay_date) as check_pay_date
from #tmp t
group by ssn
) a on a.ssn = t.ssn
--this is the final output
select
Company_Code,
SSN,
Advice_Check_Number,
Check_Pay_Date,
Period_Amount,
YTD_Amount,
Period_Hours,
YTD_Hours,
Rate,
Units,
Description,
Detail_Code,
Detail_Type
from #tmp
order by ssn, sort
go
grant exec on dd_PayrollDetail to public