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#
For purposes of interfacing, writing scripts or end user reporting, there are a number of data base functions that can be used - both from Oracle and those created as part of the application. As well, customers could create their own custom data base function for this purpose.
The functions listed below are those found to be useful in the creation of interfaces, conversion, script writing or reporting. It is not an exhaustive list, and can certainly be extended by a customer adding their own functions.
| Calling Functions in SQL Loader |
| Calling Functions in IDIF (Derivation Expression) – export interfaces |
Personality supplied Functions#
| P2K_SMGCD | Given an ID and Table_Alias return the CODE |
| P2K_SMGETUDF | Given a Table_Name and User Defined Field name return the value |
| P2K_SMGLX | Given a Lexicon_Name and Stored_Value return the Displayed_Value |
| P2K_SMGLXMN | Given a Lexicon_Name and Stored_Value return the Meaning value |
| P2K_SMAGE | Determine the age between two dates |
| P2K_STEEM | Obtain the ID for the employee's P2K_HR_EMPLOYMENTS record |
| P2K_PPAMTS.SPGETPEL | Given an Element_Name return the ID |
| P2K_PPAMTS.SPGETPPC | Given a PC_CODE return the ID |
| P2K_PPAMTS.SPELPAY | Get the value of an element from a Pay Header |
| P2K_PPAMTS.SPPCPAY | Get the value of a pay component from a Pay Header |
| P2K_SPGETYTD | Get the year to date value for a specific pay component for an employee |
| P2K_PPAMTS.SPPCTOT | Get to To-Date value for a pay component for an employee |
| P2K_PPAMTS.SPPCTOT_FILTER | Get to To-Date value for a pay component for an employee with additional filtering |
| P2K_PPAMTS.SPELTOT | Get to To-Date value for an element for an employee |
| P2K_PPAMTS.SPELTOT_FILTER | Get to To-Date value for an element for an employee with additional filtering |
| P2K_SPPERIOD | Return a date in correct period format |
| P2K_PPAMTS.SPGETEST | get an employees statistic value from IEST |
| P2K_PPGEN.SPINELEM | Determine if a pay component is in an element |
| P2K_SMZONE | convert a number into a zoned overpunch number |
| P2K_SPPGWAGE | determine the wage rate for an employee |
| P2K_PMFORM.GET_ORG_LEVEL_CODE | Determine the ORG_LEVEL_CODE for a department |
| P2K_PU | A complete utility package of numerous functions used for the creation of interfaces and reports |
| P2K_PMSEC.CHECKANDDECRYPT | Decryption of encrypted Varchar2 or Number data |
| P2K_PMSEC.CHECKANDDECRYPTDATE | Decryption of encrypted date columns |
Oracle Built-in Functions#
| ORACLE CASE WHEN Functionality | Complex If-Then-Else logic |
Conversion Functions#
| TO_CHAR | Converting a numeric or date field to a character field (often with formatting) |
| TO_DATE(Oracle_Function) | Converting a character field to a date (often with formatting) |
| TO_NUMBER | Converting a character field to a number(often with formatting) |
| DATE FORMAT MODELS (Oracle) for TO_CHAR and TO_DATE | Format mask options |
| NUMBER FORMAT MODELS (Oracle) for TO_CHAR | Format mask options |
Text Functions#
| CONCAT | Concatenation of two strings - usually easier to use two pipes (eg. ||) |
| SUBSTR | Extracting a substring out of a larger text string |
| INITCAP , UPPER and LOWER | Reformatting a text string's capital letters |
| INSTR | Determining if one string is inside another |
| LENGTH | Determining the length of the string |
| LPAD and RPAD | Left- and Right-padding character fields with another value |
| TRIM | Trimming off characters (usually blanks) |
| LTRIM and RTRIM | Trimming off characters (usually blanks) from teh left or right side of the string |
| REPLACE | Replace one value in a string with another |
Numeric Functions#
| ABS | Absolute Value (removing the - on negative values) |
| CEIL | Rounding a number up to the next integer |
| EXTRACT | Extracts a year, month or day value from a date |
| FLOOR | Rounding a number down to the previous integer |
| MOD and REMAINDER | Modulus division (determining the remainder) or just returning the remainder |
| ROUND | Rounding a number up/down and to a specific number of decimal places |
| TRUNC | Truncating the decimal part of a number |
| SIGN | Determining the +/- sign of a number |
Date Functions#
| SYSDATE | Getting the current date/time |
| LAST_DAY | Determining the last day of a month |
| ADD_MONTHS | Adding a number of months to a date |
| NEXT_DAY | Returns the first weekday that is greater than a specified date |
| MONTHS_BETWEEN | Determining the number of months between dates |
| TRUNC (Date usage) | Round a date to a nearest unit (usually to midnight) |
Miscellaneous Functions#
| DECODE | Translating a finite set of values from one set to another |
| GREATEST and LEAST | Determining the greatest (maximum) and least (minimum) value between sets of values |
| MAX and MIN | Returns the maximum and minimum values from an expression |
| NVL | testing for and resolving NULL values |
| NVL2 | testing for a resolving NULL and NOT NULL values |
| SUM | Returns the summed value of an expression |
| COALESCE | testing for and resolving multiple NULL values |
| SQLCODE | returns Oracle error message number from the most recently raised exception |
| SQLERRM | returns Oracle error message text from the most recently raised exception |
| USER | returns the Oracle database username of your session |
| UID | returns the Oracle Session UserID number for your session |
| SYS_CONTEXT-USERENV | Return various values related to your current database session |
