DERIVATION_EXPRESSION_USAGE

Table of Contents


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.

Derivation Expression Logic for Inbound Interfaces#

Inbound interfaces provide the ability to load a source file into Personality; 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:

Derivation Expressions Calling Database Functions#

The Callable Functions page contains the list of available functions that may be called within a Derivation Expression that may be used in an interface. The list contains functions from Oracle and those created as part of the application.

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.

This is an example of populating a distribution segment using the value from another field defined in IDIF. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translationlexicon.
'?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'

Format a GL amount to display the leading zeros and display a decimal point for $2263.13
Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.

To decode specific areas of the string
Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')

To round information coming in from four to two decimal places
ROUND(~,2)

This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')

To removing a colon from time field in an inbound interface (ie TTX.START_TIME)
concat(substr(~,1,2),substr(~,4,5))

Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in IMLN, the saved value is the value defined in the source file and the displayed value is the translation, in this example the time code.
P2K_SMGLX('GWRS_PC_TRANSLT',~)

Derivation Expression Examples for UPPHF and UEEF#

Multiple Field 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. [320001]. Lexicon values available from IMLN for X_INF_VARIABLE_NAME are: 32nnnn, 35nnnn.

A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.

Please see example below:

Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:

decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where:
[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal
[320009] Identity eid_id
[350400] Unit dun_id (DUN)

Example: Record # 50, Field # 10 Requirement: Work Date or Pay Period

decode(~,'Y',[320102],to_char(to_date([320756]),'DD/MM/YYYY'))
where:
[320102] Pay Period
[320756] GL Eff Date (PJD)

Internal Functions#

You have the ability to call some Personality internal functions in the derivation expression under the guidance of a consultant.

One example of doing this is for the UPPHF Payroll History interface. In this example, the Hours Comp Time Amount field is defined with the Variable Name 'Pay Header pph_id' and with a derivation Expression of:

P2K_PPAMTS.SPELPAY(~,P2K.PPAMTS.SPGETPEL('HOURS COMP TIME'))

The derivation expression will return the Element value of 'HOURS COMP TIME'.

The above example can also be achieved by setting up a variable name of 'Element pph Value' with the element code specified in the Constant Value field.

Internal Functions with Parameters#

You have the ability to call some Personality internal functions in the derivation expression under the guidance of a consultant and pass the following internal parameters in order to perform some internal calculation:

#PPH_ID#
This value must be in capital letters; this #PPH_ID# will be replaced by the current pay header's pph.id.
#EEM_ID#
This value must be in capital letters; this #EEM_ID# will be replaced by the current employment's eem.id.
#EID_ID#
This value must be in capital letters; this #EID_ID# will be replaced by the current identity's eid.id e.g. the Element PC value of 'HOURS O/T' will be retrieved first and is represented as in derivation expression. The derivation expression will return the Element value of 'HOURS COMP TIME' for #PPH_ID# and add it to the value of ~.
Example:
The OT Hours field name is defined with the Variable Element PC Value with a Constant Value of HOURS O/T and has a derivation expression of:
P2K_PPAMTS.SPELPAY(#PPH_ID#,P2K_PPAMTS.SPGETPEL('HOURS COMP TIME')),~

You may perform some internal arithmetic calculation in the derivation expression to return some prorated amount.

Example:
The 'Prorate Element' Field Name is defined with the Varibale Name of elemnt PC Value with a Derivation Expression of:
~/P2K_PPAMTS.SPELPAY(#PPH_ID#,P2K-PPAMTS.SPGETPEL('HOURS O/T'))*P2K_PPAMTS.SPELPAY(#PPH_ID#,P2K_PPAMTS.SPGETPEL('HOURS COMPE TIME'))

Call UserCalc Function#

You can call the UserCalc function at each Record Number and each Field Number. You should set up the variable name to be 'User Calc', and specify the UserCalc name in the Constant Value field.

The field type must be defined with a Char, Number or Date type. The Return Value UserCalc function on IMUC screen must match the values (Char, Number or Date) with the IMUC RET command to return the corresponding Char, Number or Date.

The data base tables available for UserCalc are:

at company level: DEN / DLN / DDP / DDD / DUN / DGR / DGD / DGV
at employee level: EID / EPS / EEM / EAS / EASD / PPRU / PPRC

BYPASS Capability#

Example:

GOTO#nn#nnn#

GOTO#nn#nnn where the 1st nn is the Record #, the 2nd nnn is the Field #

Notes #

Click to create a new notes page

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