This page (revision-12) was last changed on 31-Oct-2022 13:53 by Lilia Urtan

This page was created on 26-Nov-2021 10:22 by JEscott

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
12 31-Oct-2022 13:53 17 KB Lilia Urtan to previous
11 26-Nov-2021 10:22 17 KB kparrott to previous | to last
10 26-Nov-2021 10:22 17 KB kparrott to previous | to last
9 26-Nov-2021 10:22 17 KB kparrott to previous | to last
8 26-Nov-2021 10:22 17 KB kparrott to previous | to last
7 26-Nov-2021 10:22 4 KB kparrott to previous | to last
6 26-Nov-2021 10:22 5 KB kparrott to previous | to last
5 26-Nov-2021 10:22 4 KB kparrott to previous | to last
4 26-Nov-2021 10:22 4 KB RForbes to previous | to last
3 26-Nov-2021 10:22 4 KB JEscott to previous | to last
2 26-Nov-2021 10:22 4 KB JEscott to previous | to last
1 26-Nov-2021 10:22 4 KB JEscott to last

Page References

Incoming links Outgoing links
UPGLF
UPGLF

Version management

Difference between version and

At line 1 changed one line
!!!CREATE G/L INTERFACE FILE
!!!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.
At line 5 removed one line
At line 7 removed one line
At line 10 added 6 lines
!!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
[{InsertPage page=’X_INV_VARIABLE_NAME (01)’}]
At line 11 removed one line
!!Report Parameters & Filters
At line 18 added 188 lines
!!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.
[{InsertPage page=’IDIF’}]
----
!!Internal G/L Buffer Layout
!G/L Buffer
[{InsertPage page=’GL BUFFER’}]
----
!!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, 03nnnn
* Example: decode(~,’ABC’,decode([010031],'02',[ 010063],[ 010064]),[ 010063])
----
!!IMCT – 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
017001 - Set IMCT Custom Key
* 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
*** decode([010070],'Y','#ALL#',substr(~,11,30))
***or user may re-derive the entire Custom Data with Derivation Expression\\ e.g. 'GL COMPANY: '||[010001]||' Total DR: '||[019000]||' Rec: '||[019002]
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
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
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:
(1) Run UPGLF for each IDIF GL Interface with Trailer information to be INSERTED into IMCT Table
(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
At line 40 removed one line
At line 236 added one line
This page (revision-12) was last changed on 31-Oct-2022 13:53 by Lilia UrtanTop