First of two general queries
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'sp_4P_DirectDeposit'
)
DROP PROCEDURE dbo.sp_4P_DirectDeposit
GO
CREATE OR ALTER PROCEDURE sp_4P_DirectDeposit
@CHEKNMBR VARCHAR(21) = '00066437'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--earnings
SELECT
ch.CHEKDATE,
ch.CHEKNMBR,
ch.EMPLNAME,
dbo.f_4P_address7 (RTRIM(e.FRSTNAME) + ' ' + RTRIM(e.LASTNAME), '','' ,'','','' ,ISNULL(ea.ADDRESS1,'NO "PRIMARY" EMPLOYEE ADDRESS') ,ea.ADDRESS2,'' ,ea.CITY, ea.STATE,ea.ZIPCODE) AS EmpAddress,
dbo.f_4P_address7 (addr.LOCATNNM, '','' ,'','','' ,addr.ADDRESS1 ,'','' ,addr.CITY, addr.STATE,addr.ZIPCODE) AS CompanyAddress,
ch.PayPeriodBeginDate,
ch.PayPeriodEndDate,
ch.employid,
'***-**-' + RIGHT(RTRIM(ch.SOCSCNUM),4) AS SSN,
CONVERT(Numeric(19,5),ch.VACAVLBL) / 100 AS VACAVLBL,
CONVERT(Numeric(19,5),ch.SIKTIMAV) / 100 AS SIKTIMAV,
REPLICATE('*',LEN(dd.DDACTNUM)-4) + RIGHT( RTRIM(dd.ddactnum),4) AS ddactnum,
REPLICATE('*',LEN(dd.ddtranum)-4) + RIGHT( RTRIM(dd.ddtranum),4) AS ddtranum
FROM upr30100 ch
JOIN sy00600 Addr ON addr.LOCATNID = 'primary'
LEFT JOIN UPR00102 ea ON ea.EMPLOYID = ch.EMPLOYID AND ea.ADRSCODE = 'PRIMARY'
JOIN UPR00100 e ON e.EMPLOYID = ch.EMPLOYID
JOIN dd00200 dd ON dd.EMPLOYID = e.EMPLOYID and dd.INACTIVE = 0
WHERE ch.CHEKNMBR = @CHEKNMBR
AND ch.VOIDED = 0
GO
Grant EXEC on sp_4P_DirectDeposit to public
Second general query
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'sp_4P_DirectDeposit_DDInfo'
)
DROP PROCEDURE dbo.sp_4P_DirectDeposit_DDInfo
GO
CREATE OR ALTER PROCEDURE sp_4P_DirectDeposit_DDInfo
@CHEKNMBR VARCHAR(21) = '00060082'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--earnings
SELECT
dd.CHEKNMBR,
REPLICATE('*',LEN(dd.DDTRANUM)-4) + RIGHT( RTRIM(dd.DDTRANUM),4) AS Bank,
REPLICATE('*',LEN(dd.DDACTNUM)-4) + RIGHT( RTRIM(dd.DDACTNUM),4) AS Account,
dd.DDAMTDLR AS Amount
FROM dd30101 dd
WHERE dd.CHEKNMBR = @CHEKNMBR
GO
Grant EXEC on sp_4P_DirectDeposit_DDInfo to public
Taxes
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'sp_4P_DirectDeposit_Taxes'
)
DROP PROCEDURE dbo.sp_4P_DirectDeposit_Taxes
GO
CREATE OR ALTER PROCEDURE sp_4P_DirectDeposit_Taxes
@CHEKNMBR VARCHAR(21) = '00062322'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Med Numeric(19,5)
DECLARE @MedYTD Numeric(19,5)
DECLARE @SS Numeric(19,5)
DECLARE @SSYTD Numeric(19,5)
DECLARE @FED Numeric(19,5)
DECLARE @FEDYTD Numeric(19,5)
DECLARE @GA Numeric(19,5)
DECLARE @GAYTD Numeric(19,5)
DECLARE @StateAmt Numeric(19,5)
DECLARE @StateAmtYTD Numeric(19,5)
DECLARE @state VARCHAR(11)
DECLARE @EmployID VARCHAR(15)
DECLARE @year int
DECLARE @chekdate date
DECLARE @yearstart date
SELECT @EmployID = ch.EMPLOYID,
@year = ch.YEAR1,
@chekdate = ch.CHEKDATE
FROM upr30100 ch
WHERE ch.CHEKNMBR = @CHEKNMBR
SELECT
@FEDYTD = SUM(ch.FDWDGPRN),
@MEDYTD = SUM(ch.FICAMWPR),
@SSYTD = SUM(ch.FCASWPR),
@yearstart = DATEFROMPARTS(YEAR(@chekdate),1,1)
FROM upr30100 ch
WHERE
year1 = @year
AND ch.CHEKDATE <= @chekdate
AND ch.EMPLOYID = @EmployID
SELECT
@state = cd.PAYROLCD,
@StateAmt = cd.UPRTRXAM,
@FED = ch.FDWDGPRN,
@MED = ch.FICAMWPR,
@SS = ch.FCASWPR
FROM upr30100 ch
JOIN upr30300 cd ON ch.CHEKNMBR = cd.CHEKNMBR
WHERE ch.CHEKNMBR = @CHEKNMBR
--4 = Taxes
AND cd.PYRLRTYP = 4
ORDER BY ch.chekdate DESC
SELECT @StateAmtYTD = SUM(UPRTRXAM)
FROM upr30300
WHERE EMPLOYID = @EmployID
AND PYRLRTYP = 4
AND CHEKDATE BETWEEN @yearstart AND @chekdate
SELECT @CHEKNMBR AS CHEKNMBR,'FICA' as Code1 , null AS AMOUNT , '' AS Year_To_Date , 0 AS sort UNION all
SELECT @CHEKNMBR AS CHEKNMBR,' Med' as Code1 , @Med AS AMOUNT , format(@MedYTD,'#,##0.00') , 1 AS sort UNION all
SELECT @CHEKNMBR AS CHEKNMBR,' SS' as Code1 , @SS AS AMOUNT , format(@SSYTD,'#,##0.00') , 2 AS sort UNION all
SELECT @CHEKNMBR AS CHEKNMBR,'Federal' as Code1 , @FED AS AMOUNT , format(@FEDYTD,'#,##0.00') , 3 AS sort UNION all
SELECT @CHEKNMBR AS CHEKNMBR,'State' as Code1 , null AS AMOUNT , '' AS Year_To_Date , 4 AS sort UNION all
SELECT @CHEKNMBR AS CHEKNMBR,' ' + @state as Code1 , @StateAmt AS AMOUNT, format(@StateAmtYTD,'#,##0.00') , 5 AS sort
ORDER BY sort
GO
Grant EXEC on sp_4P_DirectDeposit_Taxes to public
Earnings
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'sp_4P_DirectDeposit_Earnings'
)
DROP PROCEDURE dbo.sp_4P_DirectDeposit_Earnings
GO
CREATE OR ALTER PROCEDURE sp_4P_DirectDeposit_Earnings
@CHEKNMBR VARCHAR(21) = '00060082'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--earnings
SELECT
ch.CHEKNMBR,
cd.PAYROLCD,
cd.PAYRATE,
cd.UNTSTOPY,
cd.UPRTRXAM
FROM upr30100 ch
JOIN upr30300 cd ON ch.CHEKNMBR = cd.CHEKNMBR
WHERE ch.CHEKNMBR = @CHEKNMBR
--1 = Regular Pay Codes
AND cd.PYRLRTYP = 1
ORDER BY ch.chekdate DESC
GO
Grant EXEC on sp_4P_DirectDeposit_Earnings to public
Wage Amounts
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'sp_4P_DirectDeposit_WageAmounts'
)
DROP PROCEDURE dbo.sp_4P_DirectDeposit_WageAmounts
GO
CREATE OR ALTER PROCEDURE sp_4P_DirectDeposit_WageAmounts
@CHEKNMBR VARCHAR(21) = '00060082'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @net Numeric(19,5)
DECLARE @netytd Numeric(19,5)
DECLARE @grossytd Numeric(19,5)
DECLARE @chekdate date
DECLARE @employid VARCHAR(15)
DECLARE @year1 INT
--wages
SELECT
@employid = ch.EMPLOYID,
@chekdate = ch.CHEKDATE,
@year1 = year1,
@net = ch.NTWPYRN
FROM upr30100 ch
WHERE ch.CHEKNMBR = @CHEKNMBR
SELECT @netytd = SUM(ch.NTWPYRN),
@grossytd = SUM(ch.GRWGPRN)
FROM upr30100 ch
WHERE YEAR1 = @year1
AND ch.CHEKDATE <= @chekdate
AND ch.EMPLOYID = @employid
SELECT @CHEKNMBR AS cheknmbr,'Net wages/period' AS col1, @net AS Amt , 0 as sort UNION ALL
SELECT @CHEKNMBR AS cheknmbr, 'Net wages YTD' as col1 , @netytd AS Amt , 1 as sort UNION ALL
SELECT @CHEKNMBR AS cheknmbr, 'Gross wages YTD' as col1 , @grossytd AS amt, 2 as sort
ORDER BY sort
GO
Grant EXEC on sp_4P_DirectDeposit_WageAmounts to public
Deductions
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'sp_4P_DirectDeposit_Deductions'
)
DROP PROCEDURE dbo.sp_4P_DirectDeposit_Deductions
GO
CREATE OR ALTER PROCEDURE sp_4P_DirectDeposit_Deductions
@CHEKNMBR VARCHAR(21) = '00060082'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @employid VARCHAR(15)
DECLARE @year1 INT
DECLARE @chekdate date
SELECT
@employid = ch.EMPLOYID,
@year1 = year1,
@chekdate = ch.CHEKDATE
FROM upr30100 ch
WHERE ch.CHEKNMBR = @CHEKNMBR
--deductions
SELECT
ch.CHEKNMBR,
cd.PAYROLCD,
cd.UPRTRXAM,
ytd.ytd
FROM upr30100 ch
JOIN upr30300 cd ON ch.CHEKNMBR = cd.CHEKNMBR
LEFT JOIN (
SELECT
ch.EMPLOYID,
cd.PAYROLCD,
SUM(cd.UPRTRXAM) AS YTD
FROM upr30100 ch
JOIN upr30300 cd ON ch.CHEKNMBR = cd.CHEKNMBR
WHERE cd.PYRLRTYP = 2
AND ch.EMPLOYID = @employid
AND ch.YEAR1 = @year1
AND ch.CHEKDATE <= @chekdate
GROUP BY cd.PAYROLCD, ch.EMPLOYID, cd.PAYROLCD
) ytd ON ytd.PAYROLCD = cd.PAYROLCD
WHERE ch.CHEKNMBR = @CHEKNMBR
--2 = Deductions
AND cd.PYRLRTYP = 2
ORDER BY 2
GO
Grant EXEC on sp_4P_DirectDeposit_Deductions to public
Benefits
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'sp_4P_DirectDeposit_Benefits'
)
DROP PROCEDURE dbo.sp_4P_DirectDeposit_Benefits
GO
CREATE OR ALTER PROCEDURE sp_4P_DirectDeposit_Benefits
@CHEKNMBR VARCHAR(21) = '00060082'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @employid VARCHAR(15)
DECLARE @year1 INT
SELECT
@employid = ch.EMPLOYID,
@year1 = year1
FROM upr30100 ch
WHERE ch.CHEKNMBR = @CHEKNMBR
--benefits
SELECT
ch.CHEKNMBR,
cd.PAYROLCD,
cd.UPRTRXAM,
ytd.ytd
FROM upr30100 ch
JOIN upr30300 cd ON ch.CHEKNMBR = cd.CHEKNMBR
LEFT JOIN (
SELECT
ch.EMPLOYID,
cd.PAYROLCD,
SUM(cd.UPRTRXAM) AS YTD
FROM upr30100 ch
JOIN upr30300 cd ON ch.CHEKNMBR = cd.CHEKNMBR
WHERE cd.PYRLRTYP = 3 --benefits
AND ch.EMPLOYID = @employid
AND ch.YEAR1 = @year1
GROUP BY cd.PAYROLCD, ch.EMPLOYID, cd.PAYROLCD
) ytd ON ytd.PAYROLCD = cd.PAYROLCD
WHERE ch.CHEKNMBR = @CHEKNMBR
--3 = benefits
AND cd.PYRLRTYP = 3
ORDER BY 2
GO
Grant EXEC on sp_4P_DirectDeposit_Benefits to public