SQL View Union of all Payroll Codes
L Vail
65,271
Like
Share
ReportI 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
,0 AS Fed_Max_Deduction
,0 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
,0 AS Fed_Max_Deduction
,0 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
GRANT SELECT ON view_Payroll_Codes TO DYNGRP
Enjoy!
Leslie
This was originally posted here.
*This post is locked for comments