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 |
