For purposes of [interfacing|INTERFACING], writing [scripts|SCRIPT] or [end user reporting|END USER REPORTING], there are a number of [data base functions|DATA BASE FUNCTION] 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|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


!![{$applicationname}] supplied Functions
%%zebra-table
|[P2K_SMGCD]|Given an [ID] and [Table_Alias|TABLE_ALIAS] return the [CODE]
|[P2K_SMGETUDF]|Given a [Table_Name|TABLE_NAME] and [User Defined Field|USER DEFINED FIELD] name return the value
|[P2K_SMGLX]|Given a [Lexicon_Name|LEXICON_NAME] and [Stored_Value|LOW_VALUE] return the [Displayed_Value|LEX_ABBREVIATION]
|[P2K_SMGLXMN]|Given a [Lexicon_Name|LEXICON_NAME] and [Stored_Value|LOW_VALUE] return the [Meaning|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|ELEMENT_CODE] 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|ENCRYPTION] data
%%

!!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
|[LAST_DAY]|returns the last day of the month
|[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


----
![Notes|Edit:Internal.CALLABLE+FUNCTION] 	
[{InsertPage page='Internal.CALLABLE+FUNCTION' default='Click to create a new notes page'}]