Share
ReportIs there any out of the box report that includes PayCodes and Rates?
Is there a report that shows Employee ID, Last Name, First Name, Paycodes, Rates?
*This post is locked for comments
You’re offline. This is a read only version of the page.
Is there any out of the box report that includes PayCodes and Rates?
Is there a report that shows Employee ID, Last Name, First Name, Paycodes, Rates?
*This post is locked for comments
Hi
I don't know of an existing report, but I have written a SQL statement that I think gives you at least the information you need. Here it is:
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)**************************/
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
