This page (revision-10) was last changed on 26-Nov-2021 10:22 by RForbes

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
10 26-Nov-2021 10:22 10 KB RForbes to previous LMTD INTERFACE FORMAT ==> DATA LOADER IDIF FORMAT
9 26-Nov-2021 10:22 10 KB RForbes to previous | to last Updates based on experiences
8 26-Nov-2021 10:22 9 KB RForbes to previous | to last
7 26-Nov-2021 10:22 9 KB RForbes to previous | to last
6 26-Nov-2021 10:22 9 KB JAiken to previous | to last
5 26-Nov-2021 10:22 9 KB JEscott to previous | to last
4 26-Nov-2021 10:22 9 KB JEscott to previous | to last
3 26-Nov-2021 10:22 8 KB JEscott to previous | to last
2 26-Nov-2021 10:22 8 KB JEscott to previous | to last
1 26-Nov-2021 10:22 2 KB JEscott to last

Page References

Incoming links Outgoing links
DATA LOADER IDIF FORMAT
DATA LOADER IDIF FORMAT

Version management

Difference between version and

At line 1 changed one line
The following reviews what should be defined in [IDIF] when creating an interface format to be used to load an external file using LMTD.
The following reviews what should be defined in [IDIF] when creating an interface format to be used to load an external file using the deta loader mechanism. data Loader functions start with the letter L as in [LMTD],[LEVS],[LBEN],]LPTS] etc.
At line 3 added 2 lines
Unless explicitly noted, IDIF fields are not used/referenced by the data loader functions.
At line 5 changed 26 lines
;[Code|??]:This field displays the user-defined Interface code.
;[Interface Type|??]:Please select Not Specified; this is not used for LMTD.
;[File Format|??]:Please select Not Specified; this is not used for LMTD.
;[Form Type|??]:Please select Not Specified; this is not used for LMTD.
;[Description|??]:This is a user-defined description.
;[File Name|??]:This field specifies the default file name.
;[File Creation Number|??]:This field is not used for LMTD.
;[Taxation Level|??]:Please select Not Specified; this is not used for LMTD.
;[Record Number|??]:The 'Rec#' column is always set to '1', unless defining header or trailer records.
;[Field Number|??]:This is a user-assigned number. For delimited files, the sequence of the field number is important, as it identifies each successive field.
;[Name|??]:This field is composed of two parts: a table alias and a column name. For example, DCO.COUNTRY_CODE is a valid field name for a DIF Data Loader interface format. The table alias must be a valid table alias for a Business Class in the HL system. The column name does not need to be valid.
;[XML Tag|??]:This not currently supported for LMTD.
;[Beginning Position|??]:If the source file data is 'Character Delimited', you must specify the Beginning position as '-1'.
;[Ending Position|??]:If the source file data is 'Character Delimited', you must specify the Ending position as '-1'.
;[Field Type|??]:This field is not used for LMTD; defaults to Char.
;[Variable Name|??]:This field is not used for LMTD.
;[Sort Sequence|??]:This field is not used for LMTD.
;[Format Mask|??]:This field is not used for LMTD.
;[Record Type|??]:Qualify Record, Header Record and Trailer Record are the only options available for LMTD purposes.
;[Record Identifier|??]:This field is not used for LMTD.
;[Constant Value|??]:This field allows you to load a constant value for a specified field.
;[Print Zero Rule|??]:This field is not used for LMTD.
;[XML Tag option|??]:This field is not used for LMTD.
;[Accum Option|??]:This field is not used for LMTD.
;[Total Option|??]:This field is not used for LMTD.
;[Derivation Expression|??]:Supported in release 4.07.14+
;[Code|INTERFACE_CODE]:This field displays the user-defined Interface code.
;[Interface Type|INTERFACE_TYPE]:Please select Not Specified; this is not used for LMTD. Selecting any other value will give a false sense of what variables are available.
;[Description|DESCRIPTION]:This is a user-defined description.
;[Record Number|RECORD_NUMBER]: This field identifies the different record layouts. Numbers must be sequential without gap. Header records must be defined first, with a record type of "Header". Trailer records must be defined after the data record, with a record type of "Trailer"
;[Field Number|FIELD_NUMBER]: This field identifies the order of the fields and must be sequential, without a gap. For delimited fields, every column must be defined as there is no mechanism to skip fields.
;[Name|FIELD_NAME]:This field is composed of two parts: a [table alias|TABLE_ALIAS] and a column name. For example, DCO.COUNTRY_CODE is a valid field name for a DIF Data Loader interface format. The table alias must be a valid table alias for a Business Class in the HL system. The column name does not need to be valid. For fields that are linked via ID, you must construct the sequence of tables to get to the value on the file. For example, BEL.EEM_ID.EID_ID.PERSON_CODE is used when loading the P2K_BE_ELECTIONS table (BEL) to identify the employee by PERSON_CODE which comes from the Employment record link (EEM_ID) to the Identity record link (EID_ID)
;[Beginning Position|FIELD_START_BYTE]:If the source file data is 'Character Delimited', you must specify the Beginning position as '-1'. If the source file data is fixed position, this would indicate the starting character from the left for this field.
;[Ending Position|FIELD_END_BYTE]:If the source file data is 'Character Delimited', you must specify the Ending position as '-1'. If the source file data is fixed position, this would indicate the ending character from the left for this field.
;[Field Type|FIELD_TYPE]:This field is not used for LMTD; defaults to Char.
;[Record Type|RECORD_TYPE]:Qualify Record, Header Record and Trailer Record are the only options available for LMTD purposes.
;[Constant Value|CONSTANT_VALUE]:This field allows you to load a constant value for a specified field.
;[Derivation Expression|DERIVATION_EXPRESSION]: Derivation expressions may be used to reference the current field or other fields on the same record. Any [PL/SQL built-in function or data base function|CALLABLE FUNCTION] may be used.
;[Comments|COMMENTS]:While this field is not used in the processing of the interface, it is highly recommended that this be used to describe the field and any derivation expressions you may have in it.
!Record Skipping
If a source file has header or trailer records, these must all be defined in the interface in [IDIF]. They must be defined with a record type of 'Header Record' or 'Trailer Record'. Each of the header and or trailer records must be defined so the LMTD knows how many records it must bypass before it tries to read and interpret the detail records. It is suggested that these Header and Trailer records be given a different record number so the interface is easier for users to read.
!Qualification
If you have defined a field as a Qualification record type, this field is used to determine if the record should be processed or not. The __Constant Value__ field will contain the value that the field must equal for it to be able to proceed with loading this record. If the field resolves to a different value, the record will be bypassed. You may use derivation expressions to evaluate field values as long as the end result matches the value in the constant value, the record will be passed. Normally the fields that are marked for qualification are noted as Foreign Columns (i.e. BEL.F_QUAL1) as described below.
By default (if Record Type is not set) all records are qualified.
!Virtual Columns
Virtual columns could be columns that are required fields for the table but that are not included in the source file (may be derived or set to constant values as a result) or they could be used to help fill a unique key identifier. They may also be used for qualification of the records, as needed.
In a fixed-position [IDIF], we can add virtual columns, by setting the Beg Posn and End Posn values to '-1'.
In a delimited [IDIF], we can add virtual columns by setting the Beg Posn and End Posn values to '0'. Virtual columns can also be added to a comma delimited format by specifying -1 in the Beg Posn and End Posn. However, when using this type of virtual column in a comma delimited format, there must be a stub value provided in the file.
If there are virtual columns defined using either of the above set up and there is no value defined in the file there must be some sort of stub value defined in the Constant field or Derivation Expression.
!Constant Values
The 'Constant Value' attribute allows you to load a constant value for a specified field. This value will override any value mapped to the field in the source file. You could enter a space in the Constant field to represent a blank value. or the word NULL in the derivation expression field to represent a NULL value.
For example, we have the following fields in the [IDIF]:\\
EID.PERSON_CODE, EID.GOVT_CODE_VERIFIED and we want to set a constant value for the EID.GOVT_CODE_VERIFIED field to 'Y'.
In the source file, we must have a "stub" value representing the field value for EID.GOVT_CODE_VERIFIED. This could look something like: 12345, or 12346,N or 12347,Y or , ,
It does not matter what the stub value is as it will be overridden with the constant value in the [IDIF]. It is only required that the stub value be there.
Each line of the source file is separated into field names and field values. Field names, as described in the previous section identify the base table and the column to load the field value into.
!Foreign Columns
Column names do not need to exist in the table and can be used to represent field names/values from a non-Personality database. The values for these non-Personality fields can be used to derive the values for Personality columns before inserting/updating. The naming convention for non-Personality columns is to prefix the column names with "F_". An example is PSL.F_TRANSACTION_CODE, where PSL determines the base table to load into (Sundries), F_ indicates it is a foreign column, and Transaction_Code is the foreign column name.
Fields that start with F_ are not loaded into te data base, but may be used for fillers, qualifiers or for other derivation expression usage.
!User Defined Fields
The LMTD function does support populating user-defined fields in a table. They are to be named the same as if they were a true database column.
!NULL Values
To load a constant value of NULL, the user will need to set a string value of ' ' (2 consecutive single quotes) in the constant value field or a derivation expression value of NULL. This value avoids ambiguity between a string value of "NULL" and an actual NULL reference.
!Derivation Expressions
Value derivation or manipulation is respected by the LMTD using the [IDIF] screen. You populate the 'Derivation Expression' field on the [IDIF] screen for the field needed to manipulate or derive.
The derivation expression must result in a character value returned from any or all function calls. If you wish to use the current field as a parameter in any function call, then you would indicate that field with a tilde (~). You may reference other field values on the same record by naming the field exactly as it is typed in the FIELD NAME field, __preceded by a colon (:)__. You may reference multiple variables in the same derivation expression.
For example, we can invoke a built-in function like [lpad|LPAD and RPAD] to add zeroes in front of an employee number on the field that would load it:
{{{
LPAD(~,'0',6)
}}}
or we could reference the same input field in a different field as:
{{{
LPAD(:BEL.EEM_ID.EID_ID.PERSON_CODE,'0',6)
}}}
Note that a reference to another field will give you the field value as it is on the file, not the final value
!Business Logic
You can now add fields to the [IDIF] to directly execute business logic before/during/after load process. These action type fields are similar in appearance to the regular fields in that they begin with the table alias of the EntityModel class in which the business logic resides followed by the name of the action column.
For example, to call the business logic in the action column of the TimeExceptions business class ACT_DIF_POST_INSERT_BUILD_SCHED, we specify a field name of TTX.ACT_DIF_POST_INSERT_BUILD_SCHED with beginning and ending positions of 0 since this is not an actual field in the load file. Using this in [IDIF] for a time exception load would cause the schedule to rebuild after time exceptions have been loaded in.
The name of the action columns also specifies the point at which the business logic is invoked during the load process. The action columns must begin with the following:
*ACT_DIF_PRE_LOAD
*ACT_DIF_POST_LOAD
*ACT_DIF_PRE_UPDATE
*ACT_DIF_POST_UPDATE
*ACT_DIF_PRE_INSERT
*ACT_DIF_POST_INSERT
!Unique Keys
The data loader can determine the linkages required for the records being loaded based upon unique key values. When defining the interface it is very important that the correct data is provided for the column names. It is also important to define the correct Unique Keys (UKs) for a particular table so the system can correctly focus on the right record to update/insert. You can override with more UKs columns but if not all of the default ones are provided, the system will assume the values for the ones not provided are null.
You can also 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.
An example of where this would be used is for loading into the Sundries table as there is no defined UK column set for that table. Fields which could be used to define a UK set for this table could be, PSL.START_DATE, PSL.PPC_ID.PC_CODE, PSLD.PPC_ID.PC_CODE, PSL.EEM_ID.EID_ID.PERSON_CODE.
----
![Notes|Edit:Internal.LMTD+INTERFACE+FORMAT]
[{InsertPage page='Internal.LMTD+INTERFACE+FORMAT' default='Click to create a new notes page'}]
This page (revision-10) was last changed on 26-Nov-2021 10:22 by RForbesTop