Table of Contents

CREATE PAY HISTORY INTERFACE#

The system allows you to generate an interface file from pay history information for the pay headers and the associated information such as Pay Lines, Pay Line Details, Pay Amounts or the Journal Entries depending on the interface level. The employee's basic information such as Personals, Employments, prime Assignments, Entities, Locations, Jobs, Positions, Departments, Units, Groups can also be used for the interface file. You define the interface file layout on IDIF screen.

Required Set Up#

Step 1 - Define Lexicons in IMLN#

The following lexicons are provided by P2K and used for UPPHF Pay History Interface.

Step 2 - Define Interface Format in IDIF#

The system provides starter_IDIF_HL$_UPPHF_ADMIN.sql script to load in HL$UPPHF-ADMIN as an example of a fixed format interface file. You should use Copy Definition to copy the script into a different Interface code and then enter their own company data.

IDIF - Update Record Set Up#

During the UPPHF run, you have the ability to update certain User Defined Fields (UDF) to indicate that the Interface file has been generated and mark the records as being processed.

You may reference these UDFs in IDIF to prevent the same information from being processed again.

On IDIF screen, Record Type '92-Update Record' should be defined at the end of the IDIF definition

  • the Variable Name should contain the UDF (xxx) that is to be updated or inserted,e.g. '320629 - UDF (PRN) (32)'

The Constant Value field should contain the name of the UDF, e.g UPPHF_CALPERS_PRN. If this UDF name does not exist on the IMUF screen, UPPHF will create the UDF with this name on IMUF screen for the Data Base Table to be updated.

The Derivation Expression field should contain the expression that is evaluated to the value to be updated for the UDF

The following derivation expression features can be used to derive the value to be updated:

  • multiple fields processing, DECODE, BYPASS, string manipulation, concatenation, function calls, etc.

When UPPHF is run in non-Trial mode, Record Type '92-Update Record' will be processed at the appropriate levels to check if the UDF should be updated for the associated Data Base tables.

From above example:

  • Rec#500 Field#10 indicates to update 'UDF (PRN)', this will be processed at the end of each qualified pay run
  • Rec#500 Field#20 indicates to update 'UDF (PPH)', this will be processed at the end of each qualified pay header
  • Rec#500 Field#30 indicates to update 'UDF (PPL)', this will be processed at the end of each qualified pay line
  • Rec#500 Field#40 indicates to update 'UDF (EEM)', this will be processed at the end of each qualified pay header
  • Rec#500 Field#50 indicates to update 'UDF (EASD)', this will be processed at the end of each qualified pay line

If the UDF is for a variable name that can be varied at the Pay Line level, then the UDF will be updated at each Pay Line level, e.g. 'UDF (DDD)', each pay line may point to a different department, therefore the UDF may be updated at the Pay Line level for that department

Using the Returned Value of the derivation expression, UPPHF finds if the UDF already exists; if the UDF exists and the new value is different than the existed UDF value, it will be updated; if the UDF does not exist, it will be inserted

List of UDF supported in UPPHF from X_INF_VARIABLE_NAME are:

  • 320606 - UDF (EID) (32)
  • 320607 - UDF (EEM) (32)
  • 320608 - UDF (EPS) (32)
  • 320610 - UDF (EASD) (32)
  • 320621 - UDF (DED) (32)
  • 320623 - UDF (DDD) (32)
  • 320624 - UDF (DLN) (32)
  • 320626 - UDF (DUD) (32)
  • 320628 - UDF (DGD) (32)
  • 320629 - UDF (PRN) (32)
  • 320630 - UDF (PPH) (32)
  • 320631 - UDF (PPL) (32)

After UDFs are updated or inserted in UPPHF, the number of records updated for each Data Base table will be displayed on the UPPHF report

The number of UDFs updated represents the number of times the tables are being touched. These may not necessarily be the exact number of UDF for that table. e.g. UDF (EEM) may be touched two times if an employee has two pay headers to update the same EEM, but with a different value.

Processing Information#

Qualify Process#

From the selection criteria, UPPHF selects pay runs according to user specified From/To Pay Period, Entity, and Payroll.

A pay run's Pay Period End date or Pay Issue date must be on the day of or prior to the user specified ‘As of’ date.

Pay runs with a Pay Run Stage of '70 - Closed' or '80 - Journalized' will be processed.

All pay headers of the qualified pay runs will be processed, pay headers may further be selected by Department, Org Level, Unit, Group, Person code etc.

As each pay header is processed, the associated database tables for this pay header will be loaded.

e.g. Entity, Payroll, Unit, Group, Employment, Assignment of Pay Header, Department of Assignment, Position/Job of Assignment, Authorization Area of the applicable assignments, Organization Level of the department, Pay Period, Tax Jurisdiction, etc.

The pay header is then further qualified by using the IDIF record type, 'Qualify Record'. If the 'Qualify Record' returns the value of 'BYPASS', then this pay header will be bypassed.

After the pay header is qualified, then the UPPHF Interface Level parameter is checked.

Interface Level 01 - Pay Headers#

For the interface level of ' 01 - Pay Headers', the pay header and its associated pay amounts will be processed.

Each pay header's information will be loaded.

e.g.the pay header assignment, assignment's department, cost centers, position, job, authorization, organization level of department, etc.

If the IDIF numeric field specifies that an element (pph value) is to be used, then the Element code should be specified in the IDIF Constant Value field. UPPHF calls the Element function for this element to obtain the element amount.

If the IDIF numeric field specifies to 'Accumulate' and/or to 'Calculate Total', then the amount will be accumulated or totaled.

If the IDIF field specifies a sort sequence, then the Variable Name value is also used for sorting purpose. The IDIF Record Type 'Detail Record' (or Rec#30 - 69) information for this pay header will then be saved in an internal PL/SQL table.

With all the pay header information, UPPHF searches from the internal PL/SQL table to group the same information together.

If the Interface File layout specifies any field that is to be reported at the employee level, e.g. Employee Name, Pay Number etc, then UPPHF will write the internal PL/SQL table to Execution Run Log table with Trace Level = '7' with the Sort Data in the context 2 field.

If the Interface File layout does not specify any field that is to be reported at the employee level, then the internal PL/SQL internal table will be used for all employees. At the end of UPPHF run, it will write the internal PL/SQL table to Execution Run Log table with Trace Level = '7' with the Sort Data in the context 2 field. Then the Execution Run Log with Trace Level '7' will be re-read and sorted by context 2 field to write to the interface file.

If the sort sequence is not used in IDIF record type 'Detail Record' (or Rec# 30-69), then UPPHF will not write to Execution Run Log, it will directly read from the PL/SQL table and write to interface file.

After UPPHF is run, you should verify from the interface directory the Interface File layout

Interface Level 02 - Pay Headers + Pay Lines#

For interface level '02 - Pay Headers + Pay Lines', all pay lines of this pay header will be processed.

Each pay line's information will be loaded.

e.g. Pay line assignment, department, cost centers, position, job, authorization, organization level of department, etc. This may be different than the pay header's assignment, department, etc.

As each pay line is processed, all of its associated pay line details will be processed.

As each pay line detail is processed, the pay component and amount of this pay line detail is retrieved.

With one pay component and amount from one pay line detail, UPPHF will process IDIF record type 'Detail Record' (or Record # 30 - 69 if the record type does not exist).

The IDIF record type 'Detail Record' (or Record# 30 - 69) Variable Names can reference to the loaded pay line detail or pay header information. e.g. Pay Period, Department, Cost Centers,etc.

If the IDIF numeric field specifies to retrieve a pay component value, then the pay line detail amount will be used.

If the IDIF numeric field specifies to use an element, then the Element code is specified in the constant value and UPPHF checks if the Pay Line Detail pay component is in this element to include the pay line detail amount or not.

If the IDIF numeric field specifies to 'Accumulate' and/or to 'Calculate Total', then this Pay Line Detail amount will be accumulated or totaled.

If the IDIF field specifies a sort sequence, then the variable name result is also used for sorting purpose.

Then the IDIF record type 'Detail Record' (or Rec#30 - 69) information for this pay line detail will be saved in an internal PL/SQL table.

As the next pay line detail record is processed, UPPHF will search from the internal PL/SQL table to group the same information together.

After all pay line details and pay lines are processed for one pay header, then the pay amounts of this pay header will be processed.

As each pay amount is processed, it will subtract the amount that has been processed by the pay line details and then process the remaining amount with IDIF the record type 'Detail Record' (or Record # 30-69).

With the pay amount information, UPPHF searches from the internal PL/SQL table to group the same information together.

After all pay amounts are processed for one pay header, UPPHF will process the internal PL/SQL table and write to the interface file.

If the Interface File layout specifies any field that is to be reported at the employee level (e.g. Employee Name, Pay Number, etc.) then UPPHF will write the internal PL/SQL table to Execution Run Log table with Trace Level = '7' with the Sort Data in the context 2 field.

If the Interface File layout does not specify the field that is to be reported at the employee level, then the internal PL/SQL internal table will be used for all employees. At the end of UPPHF run, it will write the internal PL/SQL table to Execution Run Log table with Trace Level = '7' with the Sort Data in the context 2 field.

Then the execution run log with Trace Level '7' will be re-read and sorted by the context 2 field to write to the interface file.

If the sort sequence is not used in the IDIF record type 'Detail Record' (or Rec# 30-69), then UPPHF will not write to execution run log, it will directly read from PL/SQL table and write to interface file.

After UPPHF is run, you should verify the Interface File layout from the interface directory.

Interface Level 10 - Journal Entries#

For the Interface Level '10 - Journal Entries', UPPHF will first process the IDIF record type 'Detail Record' for the pay header and then process IDIF record type 'Detail Level 1' for all the pay header's related journal entries.

Record Type: Interface File Record

Detail Record: This is an optional field and may generate a Header record for the pay header.

Detail Level 1: This is a mandatory field and will generate one entry per Journal Detail record. In this way you may generate interface records in following sequence:

Pay header employee related info
Journal Detail
Journal Detail
Journal Details
Pay header employee related info
Journal Detail
Journal Detail
Journal Detail
Journal Detail
. etc

As each journal detail is processed, UPPHF saves the IDIF information using the internal PL/ SQL table in the same manner as the '01 - Pay Headers' processing. After all journal entries are processed for one pay header, UPPHF will then process the internal PL/SQL table and write to the interface file.

Pay Header Summary#

After the interface level is processed for these levels and before writing to the Interface File, UPPHF will process IDIF Record Type '17-Pay Header Summary' to generate pay header related summary information for this pay header.

Update Record#

When UPPHF is run in non-trial mode, Record Type '92-Update Record' will be processed at the appropriate levels to check if the User Defined Fields (UDF) are to be updated for the associated Data Base tables.

Report Parameters & Filters#

Report Parameters
From Pay Period This is an optional field. You may select a From Pay period for the Payroll code specified.
To Pay Period This is an optional field. You may select a To Pay period for the Payroll code specified.
From Pay Issue Date This is an optional field. You may select a From Pay Issue date for the pay headers.
To Pay Issue Date This is an optional field. You 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 the Pay Period end date or Pay Issue Date that is on or prior to this As Of date.
Interface Format Code Select Interface Format Code that is with the Interface Type of '32-Pay History Interface'
Interface Level This is a mandatory field. Specify the level of Interface file to be produced.
Directory Name Enter a valid directory name for interface file.
File Name Enter the file name of the interface file. If the word 'mex' is specified in the file name, then it will be replaced with the current execution id of UPPHF run. For a fixed format file, you may specify a file with extension .txt. For a comma delimited format file, you may specify a file with extension .csv For the XML file, you may specify a file with extension .XML
Exception Level If the exception level is '0', only exception messages will be printed. If the exception level is '1', one line will be printed per pay header with performance time. If the exception level is '2', one line will be printed per pay header with performance time and one line per Detail Level 1 record. If the exception level is '8' or '9', full utility trace is available only if the user selects by Person code. Otherwise UPPHF will internally reset to exception level '1' due to the volume of trace messages.

Report Filters
Entity This is a mandatory field. You must enter one 'Entity Code' to be processed.
Person Code You may enter a list of 'Person Code' filters.
Department This is an optional field. Multiple 'Department Code' filters are allowed to be entered.
Authorization This is an optional field. You may enter either one or multiple 'Authorization area' filters. The authorization area is retrieved from either the pay header or pay line's assignment if authorization exists at assignment level. Otherwise it is retrieved from the position of the assignment if it exists, otherwise retrieve from the department of the assignment.
Org Level Type This is an optional field. You may enter either one or multiple 'Org Level Type' filters. You may enter 'Org Type 02', 'Org Type 03', etc to process applicable level types or you may enter 'Org Type 02' to process only this level.
Org Level This is an optional field. You may enter either one or multiple 'Org Level' filters. If multiple Org Level Types are entered, then you may process across multiple levels.
Unit This is an optional field. Multiple 'Unit Code' filters are allowed to be entered.
Group This is an optional field. Multiple 'Group Code' filters are allowed to be entered.
Payroll This is a mandatory field. You must enter one 'Payroll Code' to be processed. The pay headers must belong to this payroll code in order to be processed.
Cost Center This is an optional field. Multiple 'Cost Center Code' filters are allowed to be entered. The cost centers are retrieved from the pay lines and therefore are not applicable at the Pay Header level.
Pay Run Number This is an optional field. You may select a list of Pay Run numbers for the Payroll code specified.
This particular version was published on 26-Nov-2021 10:22 by JEscott.Top