The following reviews what should be defined in IDIF when creating an interface format to be used to load an external file using LMTD.
Unless explicitly noted, IDIF fields are not used/referenced by the LMTD based functions (LBEN, LPTS, etc)
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.
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.
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.
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. This value avoids ambiguity between a string value of "NULL" and an actual NULL reference.
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:
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.
You can use an existing field to use as a record qualifier or you may create a foreign field to use a qualifier and reference another field using a derivation expression. The above example uses a foreign field as a qualifier record. In the example, if value in the time entered field in the file does not equal 00000000 then the value passes the case when scenario (true) and the record qualifies. If the value did equal 0000000 then the value would fail the case when clause (false) and the record would be bypassed.
To load derived values for columns, the 'Beg Posn' and 'End Posn' values for the interface field must be set to '-1'.
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.
Screen captures are meant to be indicative of the concept being presented and may not reflect the current screen design.
If you have any comments or questions please email the Wiki Editor
All content © High Line Corporation