This is version 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 . It is not the current version, and thus it cannot be edited.

Back to current versionRestore this version

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 Wiki 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 Wiki specifications.

Derivation Expression Logic for Inbound Interfaces#

Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running LMTD. The LMTD allows you to apply derivation expressions to variables within the record that is being loaded.

The LMTD respects most of the same derivation expressions that are used with SQL Loader. One exception that the LMTD does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.

LMTD can handle multiple fields being referenced in a derivation expression, however, for LMTD purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).

---

Calling Functions in IDIF for Outbound Interfaces#

Define Interface Formats (IDIF) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:

• UEEF - Employee / Assignment Interface
• UPPHF - Pay History Interface
• UBEF - Employee / Benefits Interface
• UPGLF - General Ledger File Interface
• UPDIF - Disbursement File Interface
• UENH - New Hire Interface
• UPVEND - AP (Vendors) Interface
• UPDTB - Deposits to Bank Interface
• UPROEF - Canadian Record of Employment file interface

---

Derivation Expressions Calling Database Functions#

---

Example Derivation Expressions#

The following are examples of what derivation expressions can be used for:

To translate Gender to M/F

DECODE(~, '01', 'M', '02', 'F')

To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID

REPLACE(~, '-',)

To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available

P2K_SMGCD(~,'DDP')

• To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"

DECODE(~,'ABC','1234','BYPASS')

• To populate an effective date

NVL(RTRIM(~),'01-JAN-0001')

• To change the case of supplied data

upper(~) or initcap(~)

• To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only

interested in loading some of it to our field): substr(~,1,5)

• To remove whitespace

trim(~)

• To remove leading zeros off of a Person Code

LTRIM(~,'0')

• To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the

data base to accept it TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')

• To shorten a fiscal period of 200307 for just the period:

Substr(~,5,2) would give you "07", begin in position 5 for 2 characters

• To shorten a fiscal period of 200307 for just the year:

Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters

• Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:

Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.