Sybernet / Supplied Packages Reference
Release 3.00
Mar 14, 2004

PEOPLETOOLS

The PEOPLETOOLS package contains subprograms for interogating a PeopleSoft database. These are mostly functions that retrieve comonly referenced columns; for example, the MAILTO function returns an employee's e-mail address, and the MAIL_DROP function returns their location. When applicable, many of these functions are effective dated. This means you can pass an effective date (EFFDT), and the value relative to that date is retrieved.

Constants

PEOPLETOOLS defines the following constants:

  FY          CONSTANT CHAR(2) := 'FY';
  CY          CONSTANT CHAR(2) := 'CY';

  PTO         CONSTANT CHAR(2) := '51';
  PTI         CONSTANT CHAR(2) := '50';

Globals

PEOPLETOOLS defines the following global variables:

  EFFECTIVE_DATE DATE := NULL;

Summary of Subprograms

Subprogram	Description
ANNL_BENEF_BASE_RT	Returns the Annual Benefit Base Rate for this employee.
BALANCE_PERIOD	Relative BALANCE_PERIOD.
BALANCE_QTR	Relative BALANCE_QTR.
BALANCE_YEAR	Relative BALANCE_YEAR.
BIRTHDATE	Returns the BIRTHDATE of this employee.
BUSINESS_TITLE	Returns the BUSINESS_TITLE of this employee.
CITIZENSHIP_STATUS	Return the long description of CITIZENSHIP_STATUS.
COMPANY	Return the long description of COMPANY.
COMPRATE	Returns the COMPRATE of this employee.
COUNTRY_CODE	Return the country code corresponding to a country name.
COUNTRY_NAME	Return the country name corresponding to a country code.
DEPTID	Return the DEPTID of an employee.
DEVELOPMENT	Returns TRUE if this function was invoked on our development server.
EFFDT	Returns the global variable EFFECTIVE_DATE.
EMPL_STATUS	Returns an employee's EMPL_STATUS.
EMPL_TYPE	Returns the EMPL_TYPE for this employee.
FACTOR_MULT	Returns the factor multiplier for this earning code.
FIRST_NAME	Returns the first name of an employee.
FULLNAME	Returns an employee's full name.
GET_NEXT_PS_JOB	Retrieves the next row in PS_JOB for this employee.
GET_PREV_PS_JOB	Retrieves the previous row in PS_JOB for this employee.
HOURLY_RT	Returns the hourly rate for this employee.
JOB_FAMILY	Returns the JOB_FAMILY corresponding to JOBCODE.
LAST_NAME	Returns the last name of this employee.
LOCATION	Returns the location of this employee.
MAILTO	Returns an employee's e-mail address.
MAIL_DROP	Returns an employee's location.
MIDDLE_NAME	Returns the middle name or middle initial of this employee.
NAME	Returns an employee's name.
NATIONAL_ID	Returns the NATIONAL_ID (or social security number) of this employee.
PAGEBREAK	Inserts a page-break.
PAYGROUP	Returns the pay group of this employee.
PAY_END_DT	Returns the pay end date corresponding to this Run ID and Paygroup (SAL).
PERIOD_END_DT	Relative PERIOD_END_DT.
PHONENUMBER	Returns an employee's phone.
PRODUCTION	Returns TRUE if this function is invoked from our production server.
PS_LEAVE_ACCRUAL	Retrieves an employee's PTO or PTI balance.
REG_TEMP	Returns the REG_TEMP of this employee.
RUN_ID	Relative RUN_ID.
SEX	Returns the gender of this employee.
SSN	Returns the national ID of this employee formatted as a social security number.
STATE_CODE	Return the state code corresponding to a country name and state name.
STATE_NAME	Return the state name corresponding to a country code and state code.
SUB_PERIOD_END_DT	Relative SUB_PERIOD_END_DT.
VISA_PERMIT_TYPE	Return the long description of VISA_PERMIT_TYPE.
XLATLONGNAME	Returns the long description corresponding to FIELDNAME and FIELDVALUE.
XLATSHORTNAME	Return the short description corresponding to FIELDNAME and FIELDVALUE.

Subprogram BALANCE_PERIOD

This function returns the current BALANCE_PERIOD. You can pass an effective date which returns the BALANCE_PERIOD corresponding to that date. You can pass the BALANCE_ID as FY or CY which returns the BALANCE_PERIOD corresponding to BALANCE_ID.

Syntax

Function BALANCE_PERIOD
(
    EFFDT                         DATE     := SYSDATE
,   BALANCE_ID                    VARCHAR2 := FY
)
RETURN NUMBER;

Parameters

Parameter	Description
effdt	The effective date
balance_id	The balance identifier (FY or CY)

Example

The following example illustrates how to call BALANCE_PERIOD:

HTTP.WRITELN(PEOPLETOOLS.BALANCE_ID);

Subprogram BALANCE_QTR

This function returns the current BALANCE_QTR. You can pass an effective date which returns the BALANCE_QTR corresponding to that date. You can pass the BALANCE_QTR as FY or CY which returns the BALANCE_QTR corresponding to BALANCE_ID.

Syntax

Function BALANCE_QTR
(
    EFFDT                         DATE     := SYSDATE
,   BALANCE_ID                    VARCHAR2 := FY
)
RETURN NUMBER;

Parameters

Parameter	Description
effdt	The effective date
balance_id	The balance identifier (FY or CY)

Example

The following example illustrates how to call BALANCE_QTR:

HTTP.WRITELN(PEOPLETOOLS.BALANCE_QTR);

Subprogram BALANCE_YEAR

This function returns the current BALANCE_YEAR. You can pass an effective date which returns the BALANCE_YEAR corresponding to that date. You can pass the BALANCE_YEAR as FY or CY which returns the BALANCE_YEAR corresponding to BALANCE_ID.

Syntax

Function BALANCE_YEAR
(
    EFFDT                         DATE     := SYSDATE
,   BALANCE_ID                    VARCHAR2 := FY
)
RETURN NUMBER;

Parameters

Parameter	Description
effdt	The effective date
balance_id	The balance identifier (FY or CY)

Example

The following example illustrates how to call BALANCE_YEAR:

HTTP.WRITELN(PEOPLETOOLS.BALANCE_YEAR);

Subprogram CITIZENSHIP_STATUS

This function returns the long description of CITIZENSHIP_STATUS. NULL is returned if no data found.

Syntax

Function CITIZENSHIP_STATUS
(
    CITIZENSHIP_STATUS            VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
citizenship_status	An employee's citizenship status

Example

The following example illustrates how to call CITIZENSHIP_STATUS:

HTTP.WRITELN(PEOPLETOOLS.CITIZENSHIP_STATUS('1'));

Subprogram COMPANY

This function returns the long description of a COMPANY code.

Syntax

Function COMPANY
(
    COMPANY                       VARCHAR2
,   EFFDT                         DATE
)
RETURN VARCHAR2;

Parameters

Parameter	Description
company	The company code
effdt	The effective date

Example

The following example illustrates how to call COMPANY:

HTTP.WRITELN(PEOPLETOOLS.COMPANY('001'));

Subprogram COUNTRY_CODE

This function returns the country code corresponding to a country name. NULL is returned if no data found.

Syntax

Function COUNTRY_CODE
(
    COUNTRY_NAME                  VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
country_name	The name of a country

Example

The following example illustrates how to call COUNTRY_CODE:

HTTP.WRITELN(PEOPLETOOLS.COUNTRY_CODE('United States'));

Subprogram COUNTRY_NAME

This function returns the country name corresponding to a country code. NULL is returned if no data found.

Syntax

Function COUNTRY_NAME
(
    COUNTRY_CODE                  VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
country_code	A country code

Example

The following example illustrates how to call COUNTRY_NAME:

HTTP.WRITELN(PEOPLETOOLS.COUNTRY_NAME('USA'));

Subprogram EFFDT

This functions returns the global variable EFFECTIVE_DATE. This function is used in the views PS_JOB_VIEW_DYNAMIC and PS_JOB_DYNAMIC. These views return the most current row relative to some effective date. An exception is raised if you use these views without assigning a value to EFFECTIVE_DATE. PS_JOB_VIEW_DYNAMIC imposes row-level security based on your procedure name and the user invoking it. PS_JOB_DYNAMIC imposes no restrictions.

Syntax

Function EFFDT RETURN DATE;

Example

The following example illustrates how to use EFFDT:

DECLARE
    DEPTID PS_JOB.DEPTID%TYPE;
BEGIN
    PEOPLETOOLS.EFFECTIVE_DATE:='Jan 1, 2001';
    SELECT DEPTID
    INTO   DEPTID
    FROM   PS_JOB_DYNAMIC
    WHERE  EMPLID = '12345';
    HTTP.WRITELN(DEPTID);
END;

Subprogram EMPL_STATUS

This functions returns the EMPL_STATUS corresponding to an employee's EMPLID. NULL is returned if no data found.

Syntax

Function EMPL_STATUS
(
    EMPLID                        VARCHAR2
)
RETURN CHAR;

Parameters

Parameter	Description
emplid	An employee number

Example

The following example illustrates how to call EMPL_STATUS:

HTTP.WRITELN(PEOPLETOOLS.EMPL_STATUS('12345'));

Subprogram FULLNAME

This functions returns an employee's full name as first name, middle name, followed by last name. NULL is returned if no data found.

Syntax

Function FULLNAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number

Example

The following example illustrates how to call FULLNAME:

HTTP.WRITELN(PEOPLETOOLS.FULLNAME('12345'));

Subprogram GET_NEXT_PS_JOB

This function returns the next row in PS_JOB corresponding to an employee. It is up to you to catch NO_DATA_FOUND.

Syntax

Procedure GET_NEXT_PS_JOB
(
    P                             IN OUT PL/SQL RECORD
)   ;

Parameters

Parameter	Description
p	A PS_JOB rowtype variable.

Example

The following example illustrates how to call GET_NEXT_PS_JOB:

DECLARE
    JOB PS_JOB%ROWTYPE;
BEGIN
    SELECT *
    INTO   JOB
    FROM   PS_JOB_CURRENT
    WHERE  EMPLID = '12345';
    PEOPLETOOLS.GET_NEXT_PS_JOB(JOB);
END;

Subprogram GET_PREV_PS_JOB

This function returns the prior row in PS_JOB corresponding to an employee. It is up to you to catch NO_DATA_FOUND.

Syntax

Procedure GET_PREV_PS_JOB
(
    P                             PL/SQL RECORD
)   ;

Parameters

Parameter	Description
p	A PS_JOB rowtype variable.

Example

The following example illustrates how to call GET_PREV_PS_JOB:

DECLARE
    JOB PS_JOB%ROWTYPE;
BEGIN
    SELECT *
    INTO   JOB
    FROM   PS_JOB_CURRENT
    WHERE  EMPLID = '12345';
    PEOPLETOOLS.GET_PREV_PS_JOB(JOB);
END;

Subprogram JOB_FAMILY

This function returns JOB_FAMILY corresponding to JOBCODE. NULL is returned if no data found.

Syntax

Function JOB_FAMILY
(
    JOBCODE                       VARCHAR2
,   EFFDT                         DATE
)
RETURN VARCHAR2;

Parameters

Parameter	Description
jobcode	The jobcode to search.
effdt	An effective date.

Example

The following example illustrates how to call JOB_FAMILY:

HTTP.WRITELN(PEOPLETOOLS.JOB_FAMILY('7997'));

Subprogram MAILTO

This functions retrieves the electronic mail address for an employee. NULL is returned if no data found. While similar to the MAILTO
function in HTTP, this function only searches for employees in the PeopleSoft database.

Syntax

Function MAILTO
(
    EMPLID                        VARCHAR2
,   E_ADDR_TYPE                   VARCHAR2 := BUSN
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.
e_addr_type	The type of e-mail address to be retrieved.

Example

The following example illustrates how to call MAILTO:

HTTP.WRITELN(PEOPLETOOLS.MAILTO('12345'));

Subprogram MAIL_DROP

This function returns the office location of an employee. NULL is returned if no data found.

Syntax

Function MAIL_DROP
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call MAIL_DROP:

HTTP.WRITELN(PEOPLETOOLS.MAIL_DROP('12345'));

Subprogram NAME

This function returns an employee's name. NULL is returned if no data found.

Syntax

Function NAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call NAME:

HTTP.WRITELN(PEOPLETOOLS.NAME('12345'));

Subprogram PERIOD_END_DT

This functions returns a relative period end date.

Syntax

Function PERIOD_END_DT
(
    EFFDT                         DATE := SYSDATE
)
RETURN DATE;

Parameters

Parameter	Description
effdt	An effective date.

Example

The following example illustrates how to call PERIOD_END_DT:

HTTP.WRITELN(PEOPLETOOLS.PERIOD_END_DT);

Subprogram PHONENUMBER

This function returns an employee's phone number.

Syntax

Function PHONENUMBER
(
    EMPLID                        VARCHAR2
,   PHONE_TYPE                    VARCHAR2 := WORK
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.
phone_type	A phone type.

Example

The following example illustrates how to call PHONENUMBER:

HTTP.WRITELN(PEOPLETOOLS.PHONENUMBER('12345'));

Subprogram PS_LEAVE_ACCRUAL

This function calculates an employee's PTO and PTI balance.

Syntax

Function PS_LEAVE_ACCRUAL
(
    EMPLID                        VARCHAR2
,   PLAN_TYPE                     VARCHAR2 := PTO
,   EFFDT                         DATE     := EFFDT
)
RETURN NUMBER;

Parameters

Parameter	Description
emplid	An employee number.
plan_type	PTO or PTI.
effdt	An effective date.

Example

The following example illustrates how to call PS_LEAVE_ACCRUAL:

HTTP.WRITELN(PEOPLETOOLS.PS_LEAVE_ACCRUAL('12345',PEOPLETOOLS.PTI,SYSDATE));

Subprogram RUN_ID

This function returns a relative RUN_ID.

Syntax

Function RUN_ID
(
    EFFDT                         DATE := SYSDATE
)
RETURN VARCHAR2;

Parameters

Parameter	Description
effdt	An effective date.

Example

The following example illustrates how to call RUN_ID:

HTTP.WRITELN(PEOPLETOOLS.RUN_ID);

Subprogram STATE_CODE

This functions returns a state code coressponding to a state name and country name. NULL is returned if no data found.

Syntax

Function STATE_CODE
(
    COUNTRY_NAME                  VARCHAR2
,   STATE_NAME                    VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
country_name	The name of a country.
state_name	The name of a state in that country.

Example

The following example illustrates how to call STATE_CODE:

HTTP.WRITELN(PEOPLETOOLS.STATE_CODE('United States','California'));

Subprogram STATE_NAME

This functions returns a state name coressponding to a state code and country code. NULL is returned if no data found.

Syntax

Function STATE_NAME
(
    COUNTRY_CODE                  VARCHAR2
,   STATE_CODE                    VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
country_code	A country code.
state_code	A state code.

Example

The following example illustrates how to call STATE_NAME:

HTTP.WRITELN(PEOPLETOOLS.STATE_NAME('USA','CA'));

Subprogram SUB_PERIOD_END_DT

This function returns a relative sub period end date.

Syntax

Function SUB_PERIOD_END_DT
(
    EFFDT                         DATE
)
RETURN DATE;

Parameters

Parameter	Description
effdt	An effective date.

Example

The following example illustrates how to call SUB_PERIOD_END_DT:

HTTP.WRITELN(PEOPLETOOLS.SUB_PERIOD_END_DT);

Subprogram VISA_PERMIT_TYPE

This function returns the long description for VISA_PERMIT_TYPE. NULL is returned if no data found.

Syntax

Function VISA_PERMIT_TYPE
(
    VISA_PERMIT_TYPE              VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
visa_permit_type	A visa or permit type.

Example

The following example illustrates how to call VISA_PERMIT_TYPE:

HTTP.WRITELN(PEOPLETOOLS.VISA_PERMIT_TYPE('H1B'));

Subprogram XLATLONGNAME

This function returns the long description corresponding to FIELDNAME and FIELDVALUE. NULL is returned if no data found. As its name implies, this functions searches the PeopleSoft Translate Table, but will also search other tables when this seems appropriate; for example, are earning codes stored in the Translate table or are they stored somewhere else?

The following field names not in the PeopleSoft Translate Table are recognized by this function:

COMPANY
COUNTRY
DEPARTMENT
DIVISION
ERNCD
GROUP
JOBCODE
LOCATION
PAYGROUP
VISA_PERMIT_TYPE

Anything else causes this function to search the Translate Table.

Syntax

Function XLATLONGNAME
(
    FIELDNAME                     VARCHAR2
,   FIELDVALUE                    VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
fieldname	The name of a column.
fieldvalue	The value of a column.

Example

The following example illustrates how to call XLATLONGNAME:

HTTP.WRITELN(PEOPLETOOLS.XLATLONGNAME('EMPL_STATUS'),PEOPLETOOLS.EMPL_STATUS('12345'));

Subprogram XLATSHORTNAME

This function returns the short description corresponding to FIELDNAME and FIELDVALUE. NULL is returned if no data found. As its name implies, this functions searches the PeopleSoft Translate Table, but will also search other tables when this seems appropriate; for example, are earning codes stored in the Translate table or are they stored somewhere else?

The following field names not in the PeopleSoft Translate Table are recognized by this function:

COMPANY
COUNTRY
DEPARTMENT
DIVISION
ERNCD
GROUP
JOBCODE
LOCATION
PAYGROUP
VISA_PERMIT_TYPE

Anything else causes this function to search the Translate Table.

Syntax

Function XLATSHORTNAME
(
    FIELDNAME                     VARCHAR2
,   FIELDVALUE                    VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
fieldname	The name of a column.
fieldvalue	The value of a column.

Example

The following example illustrates how to call XLATSHORTNAME:

HTTP.WRITELN(PEOPLETOOLS.XLATSHORTNAME('EMPL_STATUS'),PEOPLETOOLS.EMPL_STATUS('12345'))