I just uploaded a file to GPUG ‘Share my Code’ to create a view that combines the Pay Code, Benefit and Deduction code tables into a single view. This can be used to create a great SmartList or Excel report so that you can view all of the current amounts for each employee from a single list.
Tables included: UPR00400 (Pay Code) UPR00500 (Deduction Code) UPR00600 (Benefit Code) UPR00100 (Employee Master). The following fields are included:
Employee_ID
Last_Name
First_Name
Middle_Name
Code
Code_Type
Pay_Type
Inactive
Begin_Date
End_Date
Base_Pay_Record
Amount
Report_As_Wages
Subj_to_FedTax
Subj_to_FICA
Subj_to_Medicare
Subj_to_SateTax
Subj_to_LocalTax
Subj_to_FUTA
Subj_to_SUTA
SUTA_State
Flat_FedTax_Rate
Flat_StateTax_Rate
Variable
Frequency
Taxable
Based_on_Records
Based_on_Rate
Method
Formula
Percent
Tier_Max
Tier_Max_Units
Max_per_Pay_Period
Max_per_Year
Lifetime_Max
W2_Box_Number
W2_Box_Label
Note_Index
Note_Index2
Life_to_Date_Amt
Data_Entry_Default
W2_Box_Number2
W2_Box_Label2
W2_Box_Number3
W2_Box_Label3
W2_Box_Number4
W2_Box_Label4
Fiscal_Yr_Max
Employer_Max_Match
Based_on_Record_Type
Pay_Unit
Pay_Unit_Period
Pay_Period
Pay_Per_Period
Earnings_Code
Court_Date
Court_Name
Document_Description
Original_Amount
Amount_Taken
Vendor_ID
Fed_Max_Deduction
State_Max_Deduction
Tip_Type
Pay_Advance
Accrue_Vacation
Accrue_Sick_Time
Workers_Comp_Code
Pay_Advance_Taken
Shift_Code
Pay_Factor
Pay_Step_Table_ID
Base_Step_Increases_On
Step
Step_Effective_Date
Step_FTE
Work_Flow_Approval_Status
Work_Flow_Priority
You can download it from the link below, or e-mail me for a copy.
http://community.gpug.com/Go.aspx?c=ViewDocument&DocumentKey=50d9b79e-5582-407c-81bd-92912bcfd211
The text of the code is below:
/* By: Leslie Vail
Date: 02/01/2014
Description: This creates a view that combines the Pay Code, Benefit and Deduction code tables into a
single view. This can be used to create a great SmartList or Excel report so that you can view
the current amounts from a single list.
Tables included: UPR00400 (Pay Code)
UPR00500 (Deduction Code)
UPR00600 (Benefit Code)
UPR00100 (Employee Master)
*/
CREATE VIEW view_payroll_codes AS
/***********************************START WITH PAY CODE (UPR00400)**************************/
SELECT upr00400.employid AS Employee_ID ,
lastname AS 'Last_Name' ,
frstname AS 'First_Name' ,
midlname AS 'Middle_Name' ,
payrcord AS Code ,
'Paycode' AS Code_Type ,
CASE paytype
WHEN 1 THEN 'Hourly'
WHEN 2 THEN 'Salary'
WHEN 3 THEN 'Piecework'
WHEN 4 THEN 'Commission'
WHEN 5 THEN 'Business_Expense'
WHEN 6 THEN 'Overtime'
WHEN 7 THEN 'Double_Time'
WHEN 8 THEN 'Vacation'
WHEN 9 THEN 'Sick'
WHEN 10 THEN 'Holiday'
WHEN 11 THEN 'Pension'
WHEN 12 THEN 'Other'
WHEN 13 THEN 'Earned_Income_Credit'
WHEN 14 THEN 'Charged_Tips'
WHEN 15 THEN 'Reported_Tips'
WHEN 16 THEN 'Minimum_Wage_Base'
ELSE 'error'
END AS Pay_Type ,
CASE upr00400.inactive
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Inactive ,
( CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ) AS Begin_Date ,
( CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ) AS End_Date ,
bspayrcd AS Base_Pay_Record ,
payrtamt AS Amount ,
CASE rptaswgs
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Report_As_Wages ,
CASE sbjtfdtx
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_FedTax ,
CASE sbjtssec
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_FICA ,
CASE sbjtmcar
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_Medicare ,
CASE sbjtsttx
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_SateTax ,
CASE sbjtltax
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_LocalTax ,
CASE sbjtfuta
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_FUTA ,
CASE sbjtsuta
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_SUTA ,
upr00400.sutastat AS SUTA_State ,
ffedtxrt AS Flat_FedTax_Rate ,
flsttxrt AS Flat_StateTax_Rate ,
'-' AS VARIABLE ,
'-' AS Frequency ,
CASE taxable
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Taxable ,
0 AS Based_on_Records ,
bsdonrte AS Based_on_Rate ,
'-' AS Method ,
'-' AS Formula ,
0 AS 'Percent' ,
0 AS Tier_Max ,
0 AS Tier_Max_Units ,
mxpypper AS Max_per_Pay_Period ,
0 AS Max_per_Year ,
0 AS Lifetime_Max ,
w2bxnmbr AS W2_Box_Number ,
w2bxlabl AS W2_Box_Label ,
upr00400.noteindx AS Note_Index ,
0 AS Note_Index2 ,
0 AS Life_to_Date_Amt ,
CASE dataentdflt
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Data_Entry_Default ,
w2bxnmbr2 AS W2_Box_Number2 ,
w2bxlabl2 AS W2_Box_Label2 ,
w2bxnmbr3 AS W2_Box_Number3 ,
w2bxlabl3 AS W2_Box_Label3 ,
w2bxnmbr4 AS W2_Box_Number4 ,
w2bxlabl4 AS W2_Box_Label4 ,
0 AS Fiscal_Yr_Max ,
0 AS Employer_Max_Match ,
0 AS Based_on_Record_Type ,
payunit AS Pay_Unit ,
CASE payunper
WHEN 1 THEN 'WeeEkly'
WHEN 2 THEN 'Biweekly'
WHEN 3 THEN 'Semimonthly'
WHEN 4 THEN 'Monthly'
WHEN 5 THEN 'Quarterly'
WHEN 6 THEN 'Semiannually'
WHEN 7 THEN 'Annually'
WHEN 8 THEN 'Daily_Miscellaneous'
ELSE 'Error'
END AS Pay_Unit_Period ,
CASE payperod
WHEN 1 THEN 'Weekly'
WHEN 2 THEN 'Biweekly'
WHEN 3 THEN 'Semimonthly'
WHEN 4 THEN 'Monthly'
WHEN 5 THEN 'Quarterly'
WHEN 6 THEN 'Semiannually'
WHEN 7 THEN 'Annually'
WHEN 8 THEN 'Daily_Miscellaneous'
ELSE 'Error'
END AS Pay_Period ,
payprprd AS Pay_Per_Period ,
'-' AS Earnings_Code ,
( CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ) AS Court_Date ,
'-' AS Court_Name ,
'-' AS Document_Description ,
0 AS Original_Amount ,
0 AS Amount_Taken ,
'-' AS Vendor_ID ,
'N/A' AS Fed_Max_Deduction ,
'N/A' AS State_Max_Deduction ,
CASE tiptype
WHEN 1 THEN 'Directly'
WHEN 2 THEN 'Indirectly'
ELSE 'Error'
END AS Tip_Type ,
payadvnc AS Pay_Advance ,
CASE acruvacn
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Accrue_Vacation ,
CASE acrustim
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Accrue_Sick_Time ,
upr00400.wrkrcomp AS Workers_Comp_Code ,
pyadvtkn AS Pay_Advance_Taken ,
shftcode AS Shift_Code ,
payfactr AS Pay_Factor ,
pystptblid AS Pay_Step_Table_ID ,
base_step_increased_on AS Base_Step_Increases_On ,
step AS Step ,
step_effective_date AS Step_Effective_Date ,
step_fte AS Step_FTE ,
CASE upr00400.workflow_approval_status
WHEN 1 THEN 'Not_Submitted'
WHEN 2 THEN 'Submitted'
WHEN 3 THEN 'Not_Needed'
WHEN 4 THEN 'Pending_Approval'
WHEN 5 THEN 'Pending_Changes'
WHEN 6 THEN 'Approved'
WHEN 7 THEN 'Rejected'
WHEN 8 THEN 'Ended'
WHEN 9 THEN 'Not_Activated'
WHEN 10 THEN 'Deactivated'
ELSE 'ERROR'
END AS Work_Flow_Approval_Status ,
CASE upr00400.workflow_priority
WHEN 1 THEN 'Low'
WHEN 2 THEN 'Normal'
WHEN 3 THEN 'High'
ELSE 'ERROR'
END AS Work_Flow_Priority
FROM upr00400
INNER JOIN upr00100
ON upr00400.employid = upr00100.employid
/*********************************** START OF DEDUCTION (UPR00500)**************************/
UNION
SELECT upr00500.employid AS Employee_ID ,
lastname AS 'Last_Name' ,
frstname AS 'First_Name' ,
midlname AS 'Middle_Name' ,
deducton AS Code ,
'Deduction' AS Code_Type ,
'-' AS Pay_Type ,
CASE upr00500.inactive
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Inactive ,
dedbegdt AS Begin_Date ,
dedenddt AS End_Date ,
'-' AS Base_Pay_Record ,
dedcamnt_1 AS Amount ,
'-' AS Report_As_Wages ,
CASE sfrfedtx
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'ERROR'
END AS Subj_to_FedTax ,
CASE shfrfica
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'ERROR'
END AS Subj_to_FICA ,
CASE shfrficamed
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'ERROR'
END AS Subj_to_Medicare ,
CASE shfrsttx
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'ERROR'
END AS Subj_to_SateTax ,
CASE sfrlcltx
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'ERROR'
END AS Subj_to_LocalTax ,
'-' AS Subj_to_FUTA ,
'-' AS Subj_to_SUTA ,
'-' AS SUTA_State ,
0 AS Flat_FedTax_Rate ,
0 AS Flat_StateTax_Rate ,
CASE vardedtn
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'ERROR'
END AS VARIABLE ,
CASE dednfreq
WHEN 1 THEN 'Weekly'
WHEN 2 THEN 'Biweekly'
WHEN 3 THEN 'Semimonthly'
WHEN 4 THEN 'Monthly'
WHEN 5 THEN 'Quarterly'
WHEN 6 THEN 'Semiannually'
WHEN 7 THEN 'Annually'
WHEN 8 THEN 'Daily_Miscellaneous'
ELSE 'Error'
END AS Frequency ,
CASE txshanty
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'ERROR'
END AS Taxable ,
bsdorcds AS Based_on_Records ,
0 AS Based_on_Rate ,
CASE dednmthd
WHEN 1 THEN 'Percent_of_Gross'
WHEN 2 THEN 'Percent_of_Net'
WHEN 3 THEN 'Fixed_Amount'
WHEN 4 THEN 'Amount_Per_Unit'
ELSE 'ERROR'
END AS Method ,
CASE dedfrmla
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'ERROR'
END AS Formula ,
dednprct_1 AS [Percent] ,
dedtrmax_1 AS Tier_Max ,
detrmxun_1 AS Tier_Max_Units ,
depyprmx AS Max_per_Pay_Period ,
dedyrmax AS Max_per_Year ,
dedltmax AS Lifetime_Max ,
w2bxnmbr AS W2_Box_Number ,
w2bxlabl AS W2_Box_Label ,
upr00500.noteindx AS Note_Index ,
upr00500.noteindx2 AS Note_Index2 ,
ltddedtn AS Life_to_Date_Amt ,
CASE dataentdflt
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Data_Entry_Default ,
w2bxnmbr2 AS W2_Box_Number2 ,
w2bxlabl2 AS W2_Box_Label2 ,
w2bxnmbr3 AS W2_Box_Number3 ,
w2bxlabl3 AS W2_Box_Label3 ,
w2bxnmbr4 AS W2_Box_Number4 ,
w2bxlabl4 AS W2_Box_Label4 ,
deduction_fiscal_yr_max AS Fiscal_Yr_Max ,
0 AS Employer_Max_Match ,
0 AS Based_on_Record_Type ,
'-' AS Pay_Unit ,
'-' AS Pay_Unit_Period ,
'-' AS Pay_Period ,
0 AS Pay_Per_Period ,
earningscode AS Earnings_Code ,
courtdate AS Court_Date ,
courtname AS Court_Name ,
docdescr AS Document_Description ,
originalamount AS Original_Amount ,
amnttaken AS Amount_Taken ,
vendorid AS Vendor_ID ,
fedmaxded AS Fed_Max_Deduction ,
statemaxded AS State_Max_Deduction ,
'-' AS Tip_Type ,
0 AS Pay_Advance ,
'-' AS Accrue_Vacation ,
'-' AS Accrue_Sick_Time ,
'-' AS Workers_Comp_Code ,
0 AS Pay_Advance_Taken ,
'-' AS Shift_Code ,
0 AS Pay_Factor ,
'-' AS Pay_Step_Table_ID ,
0 AS Base_Step_Increases_On ,
0 AS Step ,
( CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ) AS Step_Effective_Date ,
0 AS Step_FTE ,
'-' AS Work_Flow_Approval_Status ,
'-' AS Work_Flow_Priority
FROM upr00500
INNER JOIN upr00100
ON upr00500.employid = upr00100.employid
/*********************************** START OF BENEFIT (UPR00600)**************************/
UNION
SELECT upr00600.employid AS Employee_ID ,
lastname AS 'Last_Name' ,
frstname AS 'First_Name' ,
midlname AS 'Middle_Name' ,
benefit AS Code ,
'Benefit' AS Code_Type ,
'-' AS Pay_Type ,
CASE upr00600.inactive
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Inactive ,
bnfbegdt AS Begin_Date ,
bnfenddt AS End_Date ,
'-' AS Base_Pay_Record ,
bnfitamt_1 AS Amount ,
'-' AS Report_As_Wages ,
CASE sbjtfdtx
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_FedTax ,
CASE sbjtssec
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_FICA ,
CASE sbjtmcar
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_Medicare ,
CASE sbjtsttx
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_SateTax ,
CASE sbjtltax
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_LocalTax ,
CASE sbjtfuta
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_FUTA ,
CASE sbjtsuta
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Subj_to_SUTA ,
'-' AS SUTA_State ,
ffedtxrt AS Flat_FedTax_Rate ,
flsttxrt AS Flat_StateTax_Rate ,
CASE varbenft
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS VARIABLE ,
CASE bnftfreq
WHEN 1 THEN 'Weekly'
WHEN 2 THEN 'Biweekly'
WHEN 3 THEN 'Semimonthly'
WHEN 4 THEN 'Monthly'
WHEN 5 THEN 'Quarterly'
WHEN 6 THEN 'Semiannually'
WHEN 7 THEN 'Annually'
WHEN 8 THEN 'Daily_Miscellaneous'
ELSE 'Error'
END AS Frequency ,
CASE taxable
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Taxable ,
bsdorcds AS Based_on_Records ,
0 AS Based_on_Rate ,
CASE bnftmthd
WHEN 1 THEN 'Percent_of_Gross'
WHEN 2 THEN 'Percent_of_Net'
WHEN 3 THEN 'Percent_of_Deduction'
WHEN 4 THEN 'Fixed_Amount'
WHEN 5 THEN 'Amount_Per_Unit'
ELSE 'ERROR'
END AS Method ,
CASE bnffrmla
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'ERROR'
END AS Formula ,
bnfprcnt_1 AS 'Percent' ,
bnftrmax_1 AS Tier_Max ,
bntrmxun_1 AS Tier_Max_Units ,
bnpaypmx AS Max_per_Pay_Period ,
bnfyrmax AS Max_per_Year ,
bnflfmax AS Lifetime_Max ,
w2bxnmbr AS W2_Box_Number ,
w2bxlabl AS W2_Box_Label ,
upr00600.noteindx AS Note_Index ,
0 AS Note_Index2 ,
ltdbnfit AS Life_to_Date_Amt ,
CASE dataentdflt
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Error'
END AS Data_Entry_Default ,
w2bxnmbr2 AS W2_Box_Number2 ,
w2bxlabl2 AS W2_Box_Label2 ,
w2bxnmbr3 AS W2_Box_Number3 ,
w2bxlabl3 AS W2_Box_Label3 ,
w2bxnmbr4 AS W2_Box_Number4 ,
w2bxlabl4 AS W2_Box_Label4 ,
benefit_fiscal_max AS Fiscal_Yr_Max ,
emplrmaxmat AS Employer_Max_Match ,
borcdtyp AS Based_on_Record_Type ,
'-' AS Pay_Unit ,
'-' AS Pay_Unit_Period ,
'-' AS Pay_Period ,
0 AS Pay_Per_Period ,
'-' AS Earnings_Code ,
( CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ) AS Court_Date ,
'-' AS Court_Name ,
'-' AS Document_Description ,
0 AS Original_Amount ,
0 AS Amount_Taken ,
'-' AS Vendor_ID ,
'N/A' AS Fed_Max_Deduction ,
'N/A' AS State_Max_Deduction ,
'-' AS Tip_Type ,
0 AS Pay_Advance ,
'-' AS Accrue_Vacation ,
'-' AS Accrue_Sick_Time ,
'-' AS Workers_Comp_Code ,
0 AS Pay_Advance_Taken ,
'-' AS Shift_Code ,
0 AS Pay_Factor ,
'-' AS Pay_Step_Table_ID ,
0 AS Base_Step_Increases_On ,
0 AS Step ,
( CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ) AS Step_Effective_Date ,
0 AS Step_FTE ,
'-' AS Work_Flow_Approval_Status ,
'-' AS Work_Flow_Priority
FROM upr00600
INNER JOIN upr00100
ON upr00600.employid = upr00100.employid
/***********************************END OF BENEFIT (UPR00600)**************************/go
go
GRANT SELECT ON view_Payroll_Codes TO DYNGRP
Enjoy!
Leslie
2 comments:
Such a nice post on SQL Training. I found it very useful for me. Thanks for sharing such an informative post.
Well, these payroll codes are really useful for a developer and I hope many payroll service provider use these codes to develop their software and applications.
Post a Comment