UPGLF#
Overview#
ePersonality provides capability for users to generate G/L and COST interface file from Pay History information of the Pay Headers and the associated information such as Pay Lines, Pay Line Details, Pay Amounts of the Journal Entries. The G/L Interface File and COST module Interface File are generated by UPGLF after the UPGL run. The user defines the interface file layout on IDIF screen.Processing Information#
If an interface file to a general ledger system needs to be created, then this function must be run. Running UPGLF in non-trial mode will commit the journal entries. For Labor G/L, this will mark individual pay lines as ‘Journalized’, for Payroll G/L this will mark the pay header as ‘Journalized’. When UPGLF is run in ‘Non-Trial’ mode, it will mark all of the pay headers and pay lines that it is picking up as being ‘journalized’.Setup#
IMLN – Lexicons#
- IMLN – X_INF_VARIABLE_NAME
- X_INF_VARIABLE_NAME contains a list of system defined Variables that are eligible by Interface type. The following list describes each variable and the usage for Interface Type 01 – Pay G/L Interface
IDIF Interface Format Definition#
IDIF – Define Interface Format #
- The system provides SEED data script to load in an example of a fixed format interface file
- The user can use Copy Definition to copy into a different Interface Code and then enter their own company data.
DEFINE INTERFACE FORMATS#
The Define Interface Formats (IDIF) screen allows you to define the interface formats. The header section of the form identifies the interface being defined. The detail section outlines each record and field, and transformations to be performed.The definition data for the Define Interface Formats screen is stored in the P2K_CM_INTERFACE_FORMATS and P2K_CM_INTERFACE_FIELDS tables.
Define Interface Formats’ Usage and Examples#
- Code
- This field identifies the name of the interface.
- Interface Type
- This field identifies the program to be executed and restricts the variables that are available.
- File Format
- This field identifies how the data is going to be created. e.g.fixed position, comma or tab delimited, XML.
- Description
- This field provides a brief description of the purpose of the interface.
- File Name
- This field specifies the default file name; otherwise the file name is entered on a parameter in the function.
- File Creation Number
- Some programs (mostly banking ones) use this number to uniquely identify the execution.
- Taxation Level
- This field is used for year end and quarterly reporting by functions such as RPYEC and RPYEU.
The details section of the form includes a grid where the fields are mapped to positions within the file. Not all of the detail fields are used by all of the programs using interfaces.
- Record Number
- This is a user assigned record number. Each record number generates a new line in the interface file. The following are suggested record numbers. You can set up your own numbers.
Record Number | Interface File Records Generated |
0 | Qualify Records |
1 - 29 | Header Records |
30 - 69 | Detail Records |
70 - 99 | Trailer Records |
- Field Number
- This is a user assigned number.
- Name
- This is a user assigned field name.
- Beginning Position
- This field will be the starting value for the fixed position in the file. If the File Format = 'Fixed Format', you must specify the Beginning and Ending positions. These positions must be continuous without gap, otherwise an error situation will occur. Beginning/Ending positions are not needed for XML and other file formats - the value -1 should be used.
- Ending Position
- This field will be the ending position for the for the fixed position in the file. If the File Format = 'Fixed Format', you must specify the Beginning and Ending positions. These positions must be continuous without gap, otherwise an error situation will occur. Beginning/Ending positions are not needed for XML and other file formats - the value -1 should be used.
- Field Type
- Field Type can be defined as 'Char', 'Number' or 'Date'.
- Variable Name
- Variable Name is used for outbound interfaces only.
- Sort Sequence
- This field specifies the sort sequence for this Record #, Field # and is used 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
- 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.
- Format Mask
- The format mask is applicable to Numeric and Date field.
- Record Type
- Record Type is typically only used for outbound interfaces.
- Record Identifier
- This field is not currently used.
- XML Tag
- This optional field is used for XML File Format only. If XML Tag is not specified for XML File, then the Name field is used as XML Tag.
- Reference Id
- This field is used in the UEINH program. This will contain the ID of the column to be loaded.
- Constant Value
- If the Variable Name = 'Constant Value', this field specifies the Constant Value to be used.
- If the Variable Name = 'User Calc', this field specifies the User Calc Name
- If the Variable Name = 'Statistics Amt', this field specifies the Statistics Code
- If the Variable Name = 'Element', this field specifies the Element Code
- If the 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
- Add to UK Set
- Used to manually add columns to the destination tables' UK column set by toggling the 'Add to UK set' check box. This will only add the column as a UK column to the existing UK set for the destination table. It will not act as a UK set override.
- Print Zero Rule
- This field is applicable to the Numeric field only and is used for Record Type = 'Detail Record'.
- If the amount is zero, this field indicates to include this amount in the interface file or not.
- e.g. if the field is from 'Statistics Amount' and the amount is zero, the user may not want to report this amount
- If all the 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.
- XML Tag Option
- This is applicable to the XML file, if the Variable Name = 'XML begin Tag' or 'XML end Tag'. This field option indicates if the XML Tag should be written.
- Accum Option
- This field is applicable to the Numeric field only and is used for Record Type = 'Detail Record'. This field indicates 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 the 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
- This field is applicable to the Numeric field only and is used for Record Type = Detail Record, Detail Level n. This field indicates 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 the field is 'Statistics Amount' or 'User Calc Amount', then you may want to calculate the total for Trailer record.
- Domain
- This is used by certain pre-loaded programs to further define characteristics and behaviours of a field. This field is not used in UPPHF, UEEF, UPGLF, LMTD, LBEN or LPTS.
- Derivation Expression
- This field specifies the derivation expression for the Variable name. Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in IDIF. Data from within the Personality database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the Personality specifications.
- For additional information on what may be used within the Derivation Expression field see the page titled CALLABLE FUNCTION.
- Comments
- This field can be used to add notes to the actions taking place in the interface. This is an ideal location to add notes on derivation expression set up or vendor notes.
Qualification Records#
A qualification record can be added to assist in determining if the record should be loaded or can be bypassed.
For the field that will be used as the qualifier, its Record Type needs to be set to "Qualifier Record". Next, the a Constant value of 'TRUE' is added and a Derivation Expression is also added to the record.
Sample Derivation Expression: CASE when (:PLPL.TIME_OR_AMOUNT) <>0 then 'TRUE' else 'FALSE' end
NULLVALUE in a GL File#
For GL file that is not with Fixed File Format, if a field value is NULL, user may want to use NULL for this field or use a space for this field on the file
On IDIF screen with the specified Interface Code, user can click the Header section that is with the Code and Interface Type, then click the User Defined Field icon to pop into the User Field Values screen
GL NULL VALUE OPTION#
This option specifies when an IDIF field value is NULL, how UPGLF returns the field value for the Entire GL File. The value options are listed in the lexicon as shown below.Lexicon | Lexicon Value |
X_GL_NULLVALUE | 00 - Use IDIF Derivation Expression |
X_GL_NULLVALUE | 01 - Use NULL for all NULL values |
X_GL_NULLVALUE | 02 - Use 1 Space for all NULL values |
Usages#
Each option has specific usages associated with them. The chart below outlines each lexicon value and their specific usage within IDIF.Lexicon Value | Usage |
00 - Use IDIF Derivation Expression | A: When no Derivation Expression is configured the system uses a space B: When Derivation Expression is configured using decode(~,,'NULLVALUE',~) no space is populated. |
01 - Use NULL for all NULL values | All spaces (e.g ' ') are changed to Null values (e.g ') for entire GL unless specified otherwise by using a Derivation Expression, decode(~,,'NULLSPACE',~). |
02 - Use 1 space for all null value is | All fields that have a null value are populated with 1 space. (e.g. ' ' are changed to ') unless specified otherwise by using a Derivation Expression, decode(~,,'NULLVALUE',~). |
Tips#
If this User Defined Field ‘GL NULLVALUE Option’ does not exist, please exit this screen and then run UPGLF for any File Format in Trial mode, UPGLF will automatically generate this UDF when this UDF does not exist, then user can pop into this IDIF UDF screen to specify the GL NULLVALUE OptionNotes #
Click to create a new notes pageInternal G/L Buffer Layout#
G/L Buffer#
There is no page called '’GL'. Would you like to create it?IDIF – Derivation Expression#
IDIF – Derivation Expression – BYPASS capability#
- On IDIF Derivation expression, you may decode the Variable Name using the character and return the word 'BYPASS' to bypass the Detail record entry that is with certain criteria.
- The 'BYPASS' criteria can be set up for Record Type = 'Qualify Record', 'Detail Record', and should not be used for Header and Trailer records.
- If the return value of any IDIF Record Number/Field Number = 'BYPASS', then this Detail record will be bypassed and will not be written to the Interface file.
- Example:
- to include only Departments start with 'P' on interface file: decode(rtrim(substr(~,1,1)),'P',~,'BYPASS')
- to include only Departments ends with 'C' on interface file: decode(substr(~,length(rtrim(~)),1),'C','BYPASS',~)
- to bypass Cost Centers that has '????' in second segments: decode(SUBSTR(~,5,4),'????','BYPASS',SUBSTR(~,5,4))
- to include Journal Entries with Account Numbers over 40000 in segment 5: decode(greatest('40000',substr(~,16,5)),'40000','BYPASS',substr(~,16,5))
IDIF – Derivation Expression – Multiple Fields processing#
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [ ], e.g. [010001]. Lexicon values available from IMLN for X_INF_VARIABLE_NAME are: 01nnnn, 03nnnnIMCT – Custom Table Processing#
IDIF – IMCT Custom Table Definition#
The following Variable Names can be used from lexicon X_INF_VARIABLE_NAME: 017000 - Set IMCT Custom Table- Constant Value - specifies IMCT Custom Code, X16, e.g. ‘GL-FILE’
- Derivation Expression - specifies Derivation Expression for the Constant Value
- e.g. ||’-‘||[018000], where 018000 –Fiscal Period
therefore, IMCT Custom Code will be
GL-FILE-201201 for Fiscal Period 201201 - Comments - specifies IMCT Description
- Variable 017000 can be used for IDIF Record Type of Header, Detail or Trailer
- If user wants to INSERT or READ entries from IMCT Table, Variable 017010 must be set up
- If user wants to ADD entries to IMCT Table, Variable. ‘017004 - Set IMCT INSERT Option’ must be set up
- When Variable ‘017004 - Set IMCT INSERT Option’ is set up, then
If IMCT Custom Code does not exist with this Custom Table Name, then IMCT Custom Table will be created
If IMCT Custom Code already exists, entries will be added to this Custom Table Code
- e.g. ||’-‘||[018000], where 018000 –Fiscal Period
- Use IDIF Constant Value or Derivation Expression to derive the Custom Key
017002 - Set IMCT Custom Subkey
- If this is not set, the Custom Subkey will be Null
- User may use IDIF Constant Value or Derivation Expression to derive the Custom Subkey
017003 - Set IMCT Custom Data
- usually user does not need to set up an IDIF entry with this Variable Name
- If this is not set, the entire Rec# of all Field Data are used as the Custom Data to be INSERT or UPDATE to IMCT Table
- User may also set up an IDIF entry with this Variable 017003 and use IDIF Constant Value or Derivation Expression to derive Custom Data
- if the Value = ‘#ALL#’, then the entire Rec# of all Field Data is used
- e.g. on last entry of a Rec#, user may set up a Fld# with Variable 017003 and use Derivation Expression as follows
017004 - Set IMCT INSERT Option
- if user wants to INSERT or UPDATE to IMCT, this Variable must be set
- user can use IDIF Constant Value or Derivation Expression to specify one of the following values:
- ‘INSERT’ – INSERT’ IMCT
- If IMCT already has an entry for the same Custom Key and Subkey, a new entry will be INSERT with the Custom Subkey concatenated with ‘Seq: n’ where n is the next sequence number starting from 1
- This option retains the original IMCT entry without overwriting to it, user can manually remove the duplicated entries after verification
- ‘UPDATE’ – UPDATE IMCT
- If IMCT already has an entry for the same Custom Key and Subkey, this entry will be UPDATED, otherwise the entry will be INSERTED
- ‘IGNORE’ - IGNORE IMCT
- If IMCT already has an entry for the same Custom Key and Subkey, this entry will be IGNORED, the original entry will be kept
- ‘INSERT’ – INSERT’ IMCT
017005 - Set IMCT BYPASS Criteria
- By default, if IDIF Record contains entries with any Variables that specify the IMCT Information, then IMCT entries will be Inserted or Updated
- However user may define Variable 017005 to BYPASS or SKIP this Rec # from being updated into IMCT base on certain criteria
- the IDIF Constant Value and Derivation Expression can be used to return one of the following values:
- ‘WRITE’ or NULL – WRITE to IMCT
- ‘SKIP’ – SKIP writing to IMCT
- this option allows user to still Write to the output file depending on 017020 – File BYPASS Criteria
- ‘BYPASS’ - BYPASS writing to IMCT and output FILE
- User can specify a Derivation Expression that will BYPASS this record from INSERT or UPDATE to IMCT Table and also bypass writing to output file
- e.g when Record Count = 0 or Total DR = 0, do not INSERT IMCT, do not write to output file
- Multiple IDIF Records can be used to define the BYPASS or SKIP criteria with derivation expression
- as long as there is one IDIF entry that contain ‘BYPASS’ or `SKIP`for this ‘017005’ Variable, then the entire IDIF Record Number will be bypassed
- any Fld# can specify SKIP or BYPASS
- the BYPASS option will take precedence over the SKIP option
017006 - Set IMCT Data Format
- by default, the IMCT Data Format is the same as IDIF File Format
** e.g. XML format for the output file
- if the IMCT Custom Data is required to be in a different format than the IDIF File Format, then this Variable 017006 must be set up
- e.g. Comma Delimited format for IMCT Custom Data while the output file is in XML format
- When setting up IDIF entry with this Variable Name, user can use IDIF Constant Value or Derivation Expression to specify one of the following values:
- X_FIELD_DELIMITER
- 00 Not Specified
- 01 Fixed Format
- 02 Comma Delimited
- 03 Tab Delimited
- 04 Pipe Delimited
- 05 XML File Format
- 06 Comma, No quotes
- 07 Tab, NO quotes
- 08 Pipe, No quotes
- X_FIELD_DELIMITER
017010 - Read IMCT Custom Key
- The IMCT Custom Key will be read with the specified IMCT Custom Table Code from 017000, 017001, 017002.
017011 - Read IMCT Custom Subkey
- Same as Variable 017010, used for Custom Subkey.
017012 - Read IMCT Custom Data
- Same as Variable 017010, used for Custom Data.
017020 - File BYPASS Criteria
- By default, the entire line of the same IDIF Record # information will be written to the Output File in addition to the IMCT processing. User may use IDIF Constant Value or Derivation Expression to specify one the following values to SKIP or BYPASS the writing to the output file
- ‘WRITE – WRITE to File
- ‘SKIP’, ‘BYPASS’ – Do not write this IMCT Record to Output File ** e.g when Record Count = 0, do not Write this record
- Each unique IDIF Record Number with all its Field Number entries should only perform either ‘INSERT IMCT’ or ‘READ IMCT’, it cannot define multiple Field # that include multiple Variables to SET and READ IMCT at the same time
- UPGLF has a Trial Run parameter, however regardless of this Trial Run option, the IMCT entries will always be INSERTED or UPDATED into IMCT for user to do manual verification
- after user verification, user may manually remove IMCT entries that are from the Trial UPGLF run
- for Trial run verification purpose, user can generate IMCT entries to a different IMCT Table Name that contains the Variable 010070 - Trial Run Indicator
- Variable 010070 – Trial Run Indicator can be used in the Derivation Expression to build the IMCT Custom Code such that if the UPGLF is run in Trial mode, it generates IMCT entries into a Custom Code that is concatenated with word ‘Trial’
- in Non-Trial Mode, the Derivation Expression for IMCT Custom Code will be different than the Trial Run, therefore user can keep the Non-Trial IMCT entries together
IMCT – Custom Table Example#
IMCT Comma Delimited format while GL File is in XML format#
- A company requires a Summary Report to be produced in order to confirm the transmission of all GL files are completed
- A single .csv file is needed to be produced that provides Summary Information based on all the GL interface XML files that have been processed within a Fiscal Period
- There are many GL Interface Code Formats that are defined on IDIF with XML format for each UPGLF run
- 1 GL$XML-GL111
- 2 GL$XML-GL112
- 3 GL$XML-GL113
- 4 GL$XML-GL114
- 5 GL$XML-GL115 etc
- each IDIF GL Interface Code format will produce a Trailer Record that contains DATE TIMESTAMP, NUMBER OF RECORDS, FISCAL PERIOD, RECORD TOTALS etc to feed into the .csv Summary Report
- To generate the .csv file with many UPGLF run information is a two steps process:
(2) Run UPPHF with Interface Level = IMCT Custom Code to read the IMCT entries of the UPGLF run to produce the single .csv file
UPGL IDIF – IMCT Custom Code Setup#
- IMCT Custom Table Code can be set up at the `Header Record`, `Detail Record` or the `Trailor Record`
UPGL IDIF – IMCT Detail Setup#
- Many IMCT Custom Table entries are set up for the `Trailer Record` in order to capture Record Count, Totals, etc.
- IDIF entries can be set up with these Variable Names:
- 017000 Set IMCT Custom Table (01)
- 017001 Set IMCT Custom Key (01)
- 017002 Set IMCT Custom Subkey (01)
- 017003 Set IMCT Custom Data (01)
- 017004 Set IMCT INSERT Option (01)
- 017005 Set IMCT BYPASS Criteria (01)
- 017006 Set IMCT Data Format (01)
- 017020 File BYPASS Criteria (01)
- Variable 017004 must be set up in order to INSERT or UPDATE into IMCT Table
- Variable 017006 must be set up in order to generate IMCT Data in Comma Delimited Format instead of IDIF XML File Format
- Variable 017005 should be set up because if there is NO RECORDS generated for the Interface File, user may not want to include this line in the Summary .csv file, in this case, set up an IDIF entry with Variable 017005 – Set IMCT BYPASS Criteria with Derivation Expression to SKIP or BYPASS the zero entry
UPGLF – Sample Run#
Selecting Parameter ‘G/L Format’ – example HL$GL-IMCT-XML The output report will look as follows:UPPHF IDIF – IMCT Custom Code Set up#
- IDIF Interface Type must be `Pay History Interface`
- IDIF File Format can be `Tab, No quotes` because the prior UPGLF run already generates the IMCT entries in Comma Delimited Format or other desired Format such as XML, fixed format
- UPPHF has a parameter to specify a list of IMCT Custom Table codes, there is no need to define IMCT Custom Table Codes on IDIF screen
- on IDIF Rec#, set up Variable Name 327012-Read IMCT Custom Data with `Detail Record`
UPPHF – Sample Run#
When running the UPPHF, select the following parameters as example: Interface Format Code: HL$GL-IMCT01-CSV Interface Level: IMCT Custom Table Directory Name: OUTPUT File Name: UPPHF_IMCT_mex.csv- A single .csv file is produced that provides Summary Information based on all the GL interface XML files that have been processed within a Fiscal Period
Report Parameters & Filters#
Report Parameters | |
---|---|
GL Company Code | Mandatory, LOV Available GL Company that the pays must be journalized for, as set up in the IDGC form. |
From Fiscal Period | Optional, LOV Available This field allows you to specify a beginning Fiscal Period used to qualify the Journal Entries (according to the IDCL set up). |
Up to Fiscal Period | Optional, LOV Available This field allows you to specify an ending Fiscal Period used to qualify the Journal Entries (according to the IDCL set up). |
Summarize Option | Optional, LOV Available |
Re-Interface Only | Optional, LOV Available This toggle indicates if UPGLF should pick up pay runs and pay headers that are marked with Pay Run and Pay Header stage as '86 - To-be re-interface' only. All other stages will not be processed. This toggle should be used you only want to reverse out (i.e. negate) the previously sent journal entries. |
GL Format Code | Optional, LOV Available Only those reports of the format “G/L Interface" will display. Choose from the report formats created. The format “HL$GL" has been provided as a sample, but should be copied, and the copy edited, by the client before running this report. The report definition selected will determine the format of the output file (if any), as well as the columnar content of the report. |
Interface Directory | Optional, Text Must be a directory on the data base server for which the current user (as signed onto the client’s network) has write capability. For example the entry ‘C:\TEMP’ accesses the ‘TEMP’ directory on the server, not the local directory for the computer terminal being used. |
GL File Name | Optional, Text Enter the name of the file that will be created in the interface directory specified. e.g. HLGL_mex.txt, if ‘mex’ is specified, MEX_ID will insert text into the file name. |
Write or Append to GL file | Mandatory, LOV Available, Defaults as <Write to File> If the file already exists should the current information overwrite it or append to it? |
Cost Format Code | Optional, LOV Available Only those reports of the format “Cost Interface" will display. Choose from the report formats created. The format “HL$COST" has been provided as a sample, but should be copied, and the copy edited, by the client before running this report. The report definition selected will determine the format of the output file (if any), as well as the columnar content of the report. This field is used for the Financial Costing module only. |
Cost File Name | Optional, Text Enter the name of the file that will be created in the interface directory specified. This field is used for the Financial Costing module. |
Write or Append to Cost | Mandatory, LOV Available, Defaults as <Write to File> If the file already exists should the current information overwrite it or append to it? |
External GL Info | |
Trial | Optional, Toggle If ‘ON’, the Journal Entries will be created with Journal Source = ‘Trial Payroll G/L’. This allows RPGL to print for verification and UPGL may be run again. If ‘OFF’, the Journal Entries will be created with Journal Source = ‘Payroll G/L’ and it cannot be removed by the system. |
Exception Level | Mandatory, LOV Available You may increase the level of exception to utility levels for testing purposes. |
User Comment | Optional, Text This field holds the user-supplied comment which will appear in the header of all pages of the report |
Report Filters | |
---|---|
Person Code | Optional, LOV Available This report will be limited to the person codes identified in this field. |
Entity | Optional, LOV Available This report will be limited to the entities identified in this field. |
Payroll | Optional, LOV Available This report will be limited to the payrolls identified in this field. |
Pay Run Number | Optional, LOV Available This report will be limited to the pay run numbers identified in this field. |
From Execution ID | Optional, LOV Available This is the Execution ID from the Labor or Payroll General Ledger Update (UPLG or UPGL). |
Journal Source | Optional, LOV Available Journal Source indicates where the entries are originating from Labor G/L, Payroll G/L, Accrual G/L, Trial Labor G/L, Trial Payroll G/L, or Trial Accrual G/L. |
Journal Type | Optional, LOV Available The Journal Type may be Financial or Statistical. |