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_SMGCDGiven an ID and Table_Alias return the CODE
P2K_SMGETUDFGiven a Table_Name and User Defined Field name return the value
P2K_SMGLXGiven a Lexicon_Name and Stored_Value return the Displayed_Value
P2K_SMGLXMNGiven a Lexicon_Name and Stored_Value return the Meaning value
P2K_SMAGEDetermine the age between two dates
P2K_STEEMObtain the ID for the employee's P2K_HR_EMPLOYMENTS record
P2K_PPAMTS.SPGETPELGiven an Element_Name return the ID
P2K_PPAMTS.SPGETPPCGiven a PC_CODE return the ID
P2K_PPAMTS.SPELPAYGet the value of an element from a Pay Header
P2K_PPAMTS.SPPCPAYGet the value of a pay component from a Pay Header
P2K_SPGETYTDGet the year to date value for a specific pay component for an employee
P2K_PPAMTS.SPPCTOTGet to To-Date value for a pay component for an employee
P2K_PPAMTS.SPPCTOT_FILTERGet to To-Date value for a pay component for an employee with additional filtering
P2K_PPAMTS.SPELTOTGet to To-Date value for an element for an employee
P2K_PPAMTS.SPELTOT_FILTERGet to To-Date value for an element for an employee with additional filtering
P2K_SPPERIODReturn a date in correct period format
P2K_PPAMTS.SPGETESTget an employees statistic value from IEST
P2K_PPGEN.SPINELEMDetermine if a pay component is in an element
P2K_SMZONEconvert a number into a zoned overpunch number
P2K_SPPGWAGEdetermine the wage rate for an employee
P2K_PMFORM.GET_ORG_LEVEL_CODEDetermine the ORG_LEVEL_CODE for a department

Oracle Built-in Functions#

ORACLE CASE WHEN FunctionalityComplex If-Then-Else logic

Conversion Functions#

TO_CHARConverting a numeric or date field to a character field (often with formatting)
TO_DATEConverting a character field to a date (often with formatting)
TO_NUMBERConverting a character field to a number(often with formatting)
DATE FORMAT MODELS (Oracle) for TO_CHAR and TO_DATEFormat mask options
NUMBER FORMAT MODELS (Oracle) for TO_CHARFormat mask options

Text Functions#

CONCATConcatenation of two strings - usually easier to use two pipes (eg. ||)
SUBSTRExtracting a substring out of a larger text string
INITCAP , UPPER and LOWERReformatting a text string's capital letters
INSTRDetermining if one string is inside another
LENGTHDetermining the length of the string
LPAD and RPADLeft- and Right-padding character fields with another value
TRIMTrimming off characters (usually blanks)
LTRIM and RTRIMTrimming off characters (usually blanks) from teh left or right side of the string
REPLACEReplace one value in a string with another

Numeric Functions#

ABSAbsolute Value (removing the - on negative values)
CEILRounding a number up to the next integer
FLOORRounding a number down to the previous integer
MODModulus division (determining the remainder)
ROUNDRounding a number up/down and to a specific number of decimal places
TRUNCTruncating the decimal part of a number
SIGNDetermining the +/- sign of a number

Date Functions#

SYSDATEGetting the current date/time
LAST_DAYDetermining the last day of a month
ADD_MONTHSAdding a number of months to a date
MONTHS_BETWEENDetermining the number of months between dates
TRUNC (Date usage)Round a date to a nearest unit (usually to midnight)

Miscellaneous Functions#

DECODETranslating a finite set of values from one set to another
GREATEST and LEASTDetermining the greatest (maximum) and least (minimum) value between sets of values
NVLtesting for and resolving NULL values
NVL2testing for a resolving NULL and NOT NULL values