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.

ValueVariable NameDescription
322000PPX Fed Govt Regist (32)- System derived Federal Government Registration Number from IDGV of the employee’s Group for Payroll Tax Interface
322001PPX State Govt Regist (32)- System derived State Government Registration Number from IDGV of the employee’s Group for Payroll Tax Interface
322002PPX Local Govt Regist (32)- System derived Local Government Registration Number from IDGV of the employee’s Group for Payroll Tax Interface
322003PPX Element Value (32)- the Element Value is retrieved from the current Pay Header or Internal Table that is being processed in UPPXF
322004PPX 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
322005PPX 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
322007IDIF Format Code (32) - this field contains the user selected IDIF Format Code
- this can be used for IDIF Header or Trailer record
322008IDIF Format Type (32) - this field contains the user selected IDIF Format Type
- this can be used for IDIF Header or Trailer record
322009IDIF Directory Name (32) - this field contains the user selected Directory Name
- this can be used for IDIF Header or Trailer record
322010PPX State Code (32) - System derived State Code that is currently being processed by UPPXF
322011PPX State Name (32) - System derived State Name that is currently being processed by UPPXF
322012PPX GEO Code (32) - System derived GEO Code that is currently being processed by UPPXF
322013PPX GEO Name (32) - System derived GEO Name that is currently being processed by UPPXF
322014PPX School Code (32) - System derived School Code that is currently being processed by UPPXF
322015PPX School Name (32) - System derived School Name that is currently being processed by UPPXF
322016PPX County Code (32) - System derived County Code that is currently being processed by UPPXF
322017PPX County Name (32) - System derived County Name that is currently being processed by UPPXF
322018PPX PA PSD Code (32) - System derived PA PSD Code that is currently being processed by UPPXF
322019PPX 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
IDIF field descriptions:
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
- the Federal Registration Number will be retrieved for each Pay Header and will be sorted so that Pay Headers are reported within each Federal Registration 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 UPPXF

UPPXF – 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 file


UPPXF 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

UPPXF – Sample Interface File – Comma Delimited File Format#

- The Interface file will be generated if the IDIF Format Code is entered on the prompt


Notes#

Click to create a new notes page