Wiki: CALLABLE FUNCTION

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

Wiki 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

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	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 for TO_CHAR and TO_DATE	Format mask options
NUMBER FORMAT MODELS 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
FLOOR	Rounding a number down to the previous integer
MOD	Modulus division (determining 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
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
NVL	testing for and resolving NULL values
NVL2	testing for a resolving NULL and NOT NULL values