Payrun Tax Interface Processing#
Overview#
This process UPPXF provides information on gross wages, taxable wages, tax withheld etc. at the Federal, State, County, City, School District level on an output Report file in txt file format and also provides the Government Totals on the Windward Report. This process can be run per Pay Run basis or by Pay Period or by a time frame of Pay Issue Date.This document describes the processing to retrieve US Taxation Amounts at Federal / State / Local levels after the Pay Cycle is run.
This process also provides capability to generate an Interface file based on a user defined IDIF format.
Set Up#
IMLN – Lexicons#
The following lexicons are provided by the system and used for the UPPXF Payrun Tax Interface.IMLN - X_INTERFACE_TYPE
The following value from X_INTERFACE_TYPE must be used for IDIF screen Interface Type field:
32 – Payrun Tax Interface
IMLN - X_INTERFACE_UPPXF
X_INTERFACE_UPPXF is used on UPPXF selection criteria screen for Interface Level parameter:
01 – Pay Headers - Taxation information are retrieved from Pay Headers, Pay Amounts and employee basic information
- this is used to retrieve Federal amounts by Federal Registration Number
IMLN - X_INF_VARIABLE_NAME X_INF_VARIABLE_NAME contains list of system defined Variables that are eligible by Interface type. The following list describes the variable and the usage for UPPXF Interface.
| Value | Variable Name | Description |
|---|---|---|
| 322000 | PPX Fed Govt Regist (32) | - System derived Federal Government Registration Number from IDGV of the employee’s Group for Payroll Tax Interface |
| 322001 | PPX State Govt Regist (32) | - System derived State Government Registration Number from IDGV of the employee’s Group for Payroll Tax Interface |
| 322002 | PPX Local Govt Regist (32) | - System derived Local Government Registration Number from IDGV of the employee’s Group for Payroll Tax Interface |
| 322003 | PPX Element Value (32) | - the Element Value is retrieved from the current Pay Header or Internal Table that is being processed in UPPXF |
| 322004 | PPX Element Value-Work (32) | - this is used to retrieve the Element Value for the Work Jurisdiction of the current Pay Header that is being processed in UPPXF - when the Pay Header’s Work Jurisdiction matches the current Jurisdiction that is being processed in UPPXF, then each Pay Component in the Element will be accumulated - the Element should specify only the Pay Component with IPCU PC Usage = ‘Work State’, ‘Work County’, ‘Work City’, ‘Work School’ etc |
| 322005 | PPX Element Value-Res (32) | - this is used to retrieve the Element Value for the Home Jurisdiction of the current Pay Header that is being processed in UPPXF - when the Pay Header’s Home Jurisdiction matches the current Jurisdiction that is being processed in UPPXF, then each Pay Component in the Element will be accumulated - the Element should specify only the Pay Component with IPCU PC Usage = ‘Res State’, ‘Res County’, ‘Res City’, ‘Res School’ etc |
| 322007 | IDIF Format Code (32) | - this field contains the user selected IDIF Format Code - this can be used for IDIF Header or Trailer record |
| 322008 | IDIF Format Type (32) | - this field contains the user selected IDIF Format Type - this can be used for IDIF Header or Trailer record |
| 322009 | IDIF Directory Name (32) | - this field contains the user selected Directory Name - this can be used for IDIF Header or Trailer record |
| 322010 | PPX State Code (32) | - System derived State Code that is currently being processed by UPPXF |
| 322011 | PPX State Name (32) | - System derived State Name that is currently being processed by UPPXF |
| 322012 | PPX GEO Code (32) | - System derived GEO Code that is currently being processed by UPPXF |
| 322013 | PPX GEO Name (32) | - System derived GEO Name that is currently being processed by UPPXF |
| 322014 | PPX School Code (32) | - System derived School Code that is currently being processed by UPPXF |
| 322015 | PPX School Name (32) | - System derived School Name that is currently being processed by UPPXF |
| 322016 | PPX County Code (32) | - System derived County Code that is currently being processed by UPPXF |
| 322017 | PPX County Name (32) | - System derived County Name that is currently being processed by UPPXF |
| 322018 | PPX PA PSD Code (32) | - System derived PA PSD Code that is currently being processed by UPPXF |
| 322019 | PPX PA PSD Name (32) | - System derived PA PSD Name that is currently being processed by UPPXF |
IDIF – Define Interface Format – Comma Delimited format#
- The system provides SEED_IDIF_HL-PPX-US-FED.sql script to load in example of a Comma Delimited format interface file
- The user should use Copy Definition to copy into a different Interface Code and then enter their own company data
| Code | Description |
|---|---|
| Code | - user defined Interface Code - if IDIF layout is supplied by the system, the Interface Code begins with HL$ |
| Interface Type | - must be 32 – Payrun Tax Interface |
| File Format | - specifies one of the File Formats from look up list Note: XML file format is not supported in UPPXF |
| Form Type | - please select ‘Not Specified’ from look up list, Form Type is not used for UPPXF |
| Description | - user defined description |
| File Name | - specifies the default file name, otherwise file name is entered on UPPXF parameter |
| File Creation Number | - Not used for UPPXF |
| Record Number | - user assigned Record Number - each Record Number generates a new line in the interface file |
| Field Number | - user assigned number - it is recommended to use at least a 3 digits number so that Rec# and Field# will not be duplicated because the program is concatenating Rec# and Field# as a unique index to read IDIF |
| Name | - user assigned field name |
| Record Type | - mandatory, must be used for this Interface Type - lexicon X_RECORD_TYPE values are: 00 – Not Specified - not applicable for this Interface type 01 – Header Record - generate Header records at begin of file 10 – Employer Header - generate Employer Header records for each Government Registration Number 11 – Employer Summary - generate Employer Summary records for each Government Registration Number 12 – Employer Header 2 - not used for UPPXF 13 – Employer Summary 2 - generate Grand Totals for UPPXF 14 – Employee Header - not used for UPPXF 15 – Employee Summary - generate Employee Summary records for each employee at the Federal Level for each Government Registration Number, this is used after all ‘Detail Level’ records 16 – Pay Header Begin - not used for UPPXF 17 – Pay Header Summary - not used for UPPXF 20 – Detail Record - not used for UPPXF 21 – Detail Level 1 - generate Employee Detail records for each Pay Header at the Federal Level for each Government Registration Number 22 – Detail Level 2 - not used for UPPXF 23 – Detail Level 3 - not used for UPPXF 24 – Detail Level 4 - not used for UPPXF 25 – Detail Level 5 - not used for UPPXF 30 – Detail Summary - not used for UPPXF 80 – Total Record - not used for UPPXF 90 – Trailer Record - generate Trailer records at end of file 91 – Qualify Record - not used for UPPXF 92 – Update Record - not used for UPPXF |
| Beginning Position | - if File Format = ‘Fixed Format’, the user must specify the Beginning position |
| Ending Position | - if File Format = ‘Fixed Format’, the user must specify the Ending position - these positions must be continuous without gap, otherwise an error situation will occur - Beginning/Ending positions are not needed for other file format |
| Field Type | - indicate the result of the Field Variable is a type of ‘Char’, ‘Number’ or ‘Date’ |
| Variable Name | - select from lexicon X_INF_VARIABLE_NAME 32nnnn, refer to lexicon list above |
| XML Tag | - used for XML File Format only, not used for UPPXF - if XML Tag is not specified for XML File, then the Name field is used as XML Tag |
| Sort Sequence | - specifies the Sort Sequence for this Record #, Field #, not used for UPPXF - use for Record # with Record Type = ‘Detail Record’ only - for Record # with Record Type = ‘Detail Level 1’ to ‘Detail Level 5’, it uses the same Sort from Record Type = ‘Detail Record’ so that the Detail Level n records are followed after the ‘Detail Record’ e.g. Interface File need to sort by: Department, Employee First Name, Last Name - then for the Fld# entry that specify ‘Department’, enter sort sequence: 100 - for the Fld# that specify ‘Employee First Name’, enter sort sequence: 150 - for the Fld# that specify ‘Employee Last Name’, enter sort sequence: 200 - all Sort sequences must be entered on entries with the same Record Number - Sort Sequence is not supported for XML file format because xml file may specify many different Record # to generate xml begin and end tags and the xml tags are not able to sort along with the data - Sort Sequence is not supported if the IDIF format contains ‘Pay Header Begin’ or ‘Pay Header Summary’ because the Sorting of the Detail Record cannot carry it’s associated Header/Summary Record |
| Format Mask | - applicable to Numeric and Date field |
| Record Identifier | - not used for this Interface Type |
| Constant Value | - if Variable Name = ‘Constant Value’, this field specifies the Constant Value to be used - if Variable Name = ‘User Calc’, this field specifies the User Calc Name - if Variable Name = ‘UDF (xxx)’, this field specifies the User Defined Field Name - if Variable Name = ‘Statistics Amt’, this field specifies the Statistics Code - if Variable Name = ‘Element’, this field specifies the Element Code - if Variable Name = ‘Total Value’, this field specifies the Rec# and Field # of the Detail Records to be totaled e.g. Record # 50 Field 70 is to be totaled and Total option = ‘Calculate Total’, then the Trailer record can report the ‘Total Value’, Constant value should be 5070 |
| Print Zero Rule | - applicable to Numeric field only, use for Record Type = ‘Detail Record’ - if amount is zero, this field indicates to include this amount in the interface file or not - e.g. if the field is from ‘Element Amount’ and the amount is zero, the user may not want to report this amount - if all numeric amounts are zero within one Record# and the Print Zero Rule are set to ‘Do not Print zeros’ for each field, then this entry will not be reported on interface file - lexicon X_PRINT_ZERO_RULE 00 – Not Specified 01 – Print Zero Amounts 02 – Don’t Print Zeros |
| XML Tag option | - applicable to XML file, if Variable Name = ‘XML begin Tag’or ‘XML end Tag’, this field option indicates if the XML Tag should be written - lexicon X_XML_TAG_OPTION 00 – Not Specified 01 – Optional, omit tag if null 02 – Mandatory, always write |
| Accum Option | - applicable to Numeric field only, use for Record Type = ‘Detail Record’ - indicate if this Record #, Field # amount should be accumulated or not - e.g. if the field is a ‘Pay Number’ or ‘Check Number’, then do not accumulate - if field is ‘Element Value’ or ‘PC Amount’, then you may want to accumulate to avoid writing too much detail per element or per pay component |
| Total Option | - applicable to Numeric field only, use for Record Type = Detail Record, Detail Leveln - indicate if this Record #, Field # amount should be totaled for Header or Trailer record - e.g. if the field is a ‘FTE’ or ‘Rate of Pay’, then do not totaled - if field is ‘Statistics Amount’ or ‘User Calc Amount’, then you may want to calculate the total for Trailer record - lexicon X_TOTAL_OPTION 00 – Not Specified 01 – Calculate Total |
| Derivation Expression | - specifies the derivation expression for the Variable name - this derivation expression will apply to the result of the Variable name before format mask - the user may use RTRIM(~) to remove trailing blanks before comparing the entire string because the data may contain blanks that are not visible at the end - please refer to following Derivation Expression processing |
UPPXF Selection Criteria#
UPPXF – Payrun Tax Interface Selection Criteria#
| From Pay Period | - optional, user may select a From Pay Period for the Payroll code specified If the Pay Period is displayed multiple times in the lookup, please select any entry, e.g. 201701, UPPXF will match with the Period specified |
| To Pay Period | - optional, user may select a To Pay Period for the Payroll code specified - e.g. From Pay Period: 201701 To Pay Period: 201704 |
| From Pay Issue Date | - optional, the user may select a From Pay Issue Date for the Pay Headers |
| To Pay Issue Date | - optional, the user may select a To Pay Issue Date for the Pay Headers - if the From-To Pay Issue Dates are entered, each Pay Header’s Pay Issue Date must be within this date range in order to be processed |
| As Of | - this date is used to retrieve Pay Runs that are with Pay Period end date or Pay Issue date that are on or prior to this As of date |
| Interface Level | - mandatory, specify the level for the Interface file to be produced - lexicon X_INTERFACE_UPPXF values are: 01 – Pay Headers |
| Sort Level | - optional, specify the Sort level for the Employee - lexicon X_UPPXF_SORT values are: 00 – Not Specified 01 – Person Code 02 – Last Name/First Name 03 – First Name/Last Name |
| Combine Fed Regist | - optional, specify if the Federal Registration Number should be combined or not - this is used when processing at State or Local level, or for Grand Totals only |
| Interface Format Code | - select Interface Format Code that is with Interface Type = ’32-Payrun Tax Interface’ Sample Format Code supplied by HL: HL-PPX-US-FED, HL-PPX-US-STATE |
| Directory Name | - enter valid directory name for interface file |
| File name | - enter file name for interface file - if the word ‘mex’ is specified in the file name, then it will be replaced with the current execution id of UPPXF run - for fixed format file, you may specify a file with extension .txt - for comma delimited format file, you may specify a file with extension .csv - for xml file, you may specify a file with extension .xml |
| Employee level Print | - optional, specify how the Employee Detail information should be printed - lexicon X_UPPXF_EE_PRINTvalues are: 00 – Do not print by Empl 01 – Detail by Pay Header 02 – Summary by Pay Header |
| Pay Component Print | - optional, specify how the Pay Component information should be printed - lexicon X_UPPXF_PC_PRINTvalues are: 00 – Default (Taxation PC only) 01 – All Pay Components 02 – Taxation Pay Components - if ‘01 – All Pay Components’ is specified, then all pay components will be printed including the non-taxation pay components, this can be used to balance all amounts to RPREGT - if ‘02 – Taxation Pay Components’ is specified, then IPCU pc usages are used to retrieve those pay components that are for US Taxations, this will only print the -taxation pay components and can be used to balance to RPREGT and RPYEU |
| Print Report On | - optional, specify if the Govt Totals are to be printed on Report or File - lexicon X_UPPXF_RPT_PRINTvalues are: 01 – Print on File only (fast) 02 – Report and File - UPPXF always generates the OUTPUT file in txt format with all information, the Govt Totals can optionally be printed on the Windward report - user can select ’01 – Print on File only’ to speed up the Windward report process |
| User Comment | - enter the user comment to be displayed on first page of the report |
| Exception Level | - if Exception Level = ‘0’, only exception messages will be printed - if Exception Level = ‘9’ full utility trace, this is available only if user selects by Person code, otherwise UPPXF will internally reset to Exception level ‘1’ due to volume of trace messages |
| Entity | - optional , enter one or more ‘Entity Code’ to be processed |
| Payroll | - optional , enter one or more ‘Payroll Code’ to be processed |
| Pay Run Number | - optional , enter one or more ‘Payroll Run Number’ to be processed |
| People List | - optional , enter People List to be processed |
| Person Code | - optional , user may enter list of ‘Person Code’ |
| Federal Regist Set | - optional, enter one or more ‘Federal Regist Set’ to be processed |
| Federal Regist Type | - optional, enter one or more ‘Federal Regist Type’ to be processed |
| Federal Regist Number | - optional, enter one or more ‘Federal Regist Number’ to be processed |
| Select State | - optional, enter one or more States to be processed |
| State Regist Set | - optional, enter one or more ‘State Regist Set’ to be processed |
| State Regist Type | - optional, enter one or more ‘State Regist Type’ to be processed |
| State Regist Number | - optional, enter one or more ‘State Regist Number’ to be processed |
| Local Regist Set | - optional, enter one or more ‘Local Regist Set’ to be processed |
| Local Regist Type | - optional, enter one or more ‘Local Regist Type’ to be processed |
| Local Regist Number | - optional, enter one or more ‘Local Regist Number’ to be processed |
| Select County | - optional, enter one or more Counties to be processed |
| Select City | - optional, enter one or more Cities to be processed |
| Select School | - optional, enter one or more Schools to be processed |
Payrun Tax Interface Processing#
UPPXF – Interface Level 01 – Pay Headers#
- From UPPXF Selection Criteria, user must enter one of the following parameters in order to qualify the Pay Runs to be processed, otherwise UPPXF will abort to avoid processing of all Pay Runs in the data base- From Pay Period / To Pay Period
- From Pay Issue Date / To Pay Issue Date
- Pay Run Number
- user can combine the Federal Registration Numbers to report at State level or to obtain Grand totals of the entire run
- UPPXF always generates a Report OUTPUT file in txt format, user can optionally print the Government Totals on the Windward report for reference, however this may take some performance time to print on Windward report
- UPPXF reads IPCU PC Usages and internally categorize all US PC Usages by Work / Res levels. Then UPPXF categorizes each Pay Header’s taxation pay component by these US PC Usages in order to report at the Federal / State / County / City / School District level
- For Interface Level ‘01 – Pay Headers’, the Pay Header and its associated Pay Amounts will be processed - each Pay Header’s information will be loaded, e.g. Pay Header assignment, assignment’s department, cost centers, position, job, authorization, organization level of department etc
- if IDIF Variable field specifies to use an Element (pph value), then the Element code should be specified in the IDIF Constant Value field and UPPXF calls the Element function for this Element to obtain the element amount
- then IDIF Record Type ‘Detail Record 1’ information for this Pay Header will be processed
- at the end of an Employee, the IDIF Record Type ‘Employee Summary’ will be processed
- after UPPXF is run, user should verify from the Interface Directory both the Report txt file and the Interface File layout
- if UPPXF is run in Trace mode, then a trace file will be generated in Interface Directory and then remove the Execution Run logs so that the Windward report will not print all the trace to save performance time
- user can verify UPPXF txt report and the Interface File with RPREGT or RPYPEU for balancing purpose
- IDIF Format Code ‘HL-PPX-US-FED’ is supplied as an example to retrieve Federal level W2 information, user should copy this Form Code and then enter your own W2 Elements on IDIF prior to running UPPXF; user can also customize the IDIF Format Code to report different Elements to suit their needs
- however user can run UPPXF to generate the Output Report txt file without using the IDIF Format Code ‘HL-PPX-US-FED’ to generate the Interface file
UPPXF Sample Report#
UPPXF – Sample Report – Print Report On File only #
- The OUTPUT Report txt file and Interface File information are displayed at the end of UPPXFUPPXF – Sample Report – Print Report On Report and File #
- The OUTPUT Report txt file and Interface File information are displayed at the end of UPPXF - The Government Registration Totals for Federal / State / County / City / School District information are printed on Windward report as well as the Output Report txt fileUPPXF Sample Interface File#
UPPXF – Sample Report File – .txt File Format #
-The OUTPUT Report txt file are printed according to the Selection criteria of these prompts:| Employee level Print | - optional, specify how the Employee Detail information should be printed - lexicon X_UPPXF_EE_PRINT values are: 00 – Do not print by Empl 01 – Detail by Pay Header 02 – Summary by Pay Header |
| Pay Component Print | - optional, specify how the Pay Component information should be printed - lexicon X_UPPXF_PC_PRINT values are: 00 – Default (Taxation PC only) 01 – All Pay Components 02 – Taxation Pay Components - if ‘01 – All Pay Components’ is specified, then all pay components will be printed including the non-taxation pay components. UPPXF report can be used to balance all amounts to RPREGT - if ‘02 – Taxation Pay Components’ is specified, then IPCU pc usages are used to retrieve those pay components that are for US Taxations. UPPXF will only print these US taxation pay components. UPPXF report can be used to balance to RPREGT and RPYEU |
- The Government Registration Totals for Federal / State / County / City / School District information are always printed on the Output Report txt file
