Tuesday, June 3, 2014

SQL View Union of all Payroll Codes

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:

SQL Training said...

Such a nice post on SQL Training. I found it very useful for me. Thanks for sharing such an informative post.

Akira Dania said...

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.