Sybernet / Supplied Packages Reference
Release 3.00
May 27, 2008

HTTP.PEOPLETOOLS

The PEOPLETOOLS package contains subprograms for interrogating employees. These are mostly functions that retrieve commonly referenced columns; for example, the MAILTO function returns an employee's e-mail address, and the MAIL_DROP function returns their location. All functions in this package accept (and require) an employee number (EMPLID). All values that depend on an effective date are retrieved relative to the current date and time.

Clearly you are asking yourself why you would even bother to use this package. Retrieving someone's name is pretty simple, and although retrieving the name of their department is only a little more complicated, there are times when all you are interested is just one of these values, and you're in no mood to trap exceptions such as no data found. All of these functions return NULL in this case. This package also takes care of the char vs varchar semantics so you don't have to worry about trimming or padding your requests.

Summary of Subprograms

Subprogram	Description
BUSINESS_TITLE	Returns the job or business title for this employee.
COMPANYNAME	Return the long description of COMPANY.
DEPARTMENTNAME	Returns the department name of this employee.
DEPTID	Returns the department number for this employee.
DIVISIONNAME	Returns the division name of this employee.
EFFDT	Returns the last effective date for this employee.
EMPL_STATUS	Returns an employee's employment status.
EMPL_TYPE	Returns an employee's employment type.
FIRST_NAME	Returns an employee's first name.
FULLNAME	Returns an employee's full name.
GROUPNAME	Returns the group name of this employee
LAST_NAME	Returns an employee's last name.
LOCATION	Returns an employee's location.
MAILTO	Returns an employee's e-mail address
MAIL_DROP	Returns an employee's office location.
MIDDLE_NAME	Returns an employee's middle name.
NAME	Returns an employee's name.
ORIG_HIRE_DT	Returns an employee's hire date.
PHONE	Returns an employee's phone number.
REG_TEMP	Returns an employee's status.
STD_HOURS	Returns an employee's standard hours.
SUPERVISOR_ID	Returnes the employee number of this employee's supervisor.
TERMINATION_DATE	Returns an employee's termination date.
USER_NAME	Returns an employee's user name in the database.

Subprogram BUSINESS_TITLE

This function returns an employee's job title.

Syntax

Function BUSINESS_TITLE
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call BUSINESS_TITLE:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.BUSINESS_TITLE(EMPLID));
END;

Subprogram COMPANYNAME

This function returns the company for this employee. Company names at SRI include SRI International and its subsidiaries. Contractors and consultants belong to one of the above even though they really work for another company.

Syntax

Function COMPANYNAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call COMPANYNAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.COMPANYNAME(EMPLID));
END;

Subprogram DEPARTMENTNAME

This function returns the name of the department that this employee works for. If you are really interested in the department number, you should call the function DEPTID.

Syntax

Function DEPARTMENTNAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call DEPARTMENTNAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.DEPARTMENTNAME(EMPLID));
END;

Subprogram DEPTID

This function returns the department number for this employee. If you are interested in the long description, you should call the function DEPARTMENTNAME.

Syntax

Function DEPTID
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call DEPTID:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.DEPTID(EMPLID));
END;

Subprogram DIVISIONNAME

This function returns the division name that this employee works for.

Syntax

Function DIVISIONNAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call DIVISIONNAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.DIVISIONNAME(EMPLID));
END;

Subprogram EFFDT

This function returns the current effective date for this employee. Effective date represents the last date a change was made to this employee.

Syntax

Function EFFDT
(
    EMPLID                        VARCHAR2
)
RETURN DATE;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call EFFDT:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.EFFDT(EMPLID));
END;

Subprogram EMPL_STATUS

This function returns an employee's employment status. Employment status can be one of

A (Active)
D (Deceased)
E (Employee Benefit)
L (Leave of Absence)
P (Leave With Pay)
Q (Retired With Pay)
R (Retired)
S (Suspended)
T (Terminated)
U (Terminated With Pay)
V (Terminated Pension Pay Out)
W (Short Work Break)
X (Retired-Pension Administration)

Syntax

Function EMPL_STATUS
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call EMPL_STATUS:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    IF HTTP.PEOPLETOOLS.EMPL_STATUS(EMPLID) IN ('A','L','P') THEN
        HTTP.WRITELN(EMPLID || ' IS ACTIVE');
    ELSE
        HTTP.WRITELN(EMPLID || ' IS NOT ACTIVE');
    END IF;
END;

Subprogram EMPL_TYPE

This function returns an employee's employment type. Employment type can be one of

H (Hourly)
S (Salary)

Syntax

Function EMPL_TYPE
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call EMPL_TYPE:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    IF HTTP.PEOPLETOOLS.REG_TEMP(EMPLID) || HTTP.PEOPLETOOLS.EMPL_TYPE(EMPLID) = 'TH' THEN
        HTTP.WRITELN(EMPLID || ' IS TEMPORARY HOURLY');
    END IF;
END;

Subprogram FIRST_NAME

This function returns an employee's first name.

Syntax

Function FIRST_NAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call FIRST_NAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.FIRST_NAME(EMPLID));
END;

Subprogram FULLNAME

This function returns an employee's full name as first name followed by middle name followed by last name.

Syntax

Function FULLNAME(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call FULLNAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.FULLNAME(EMPLID));
END;

Subprogram GROUPNAME

This function returns the group name for this employee.

Syntax

Function GROUPNAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call GROUPNAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.GROUPNAME(EMPLID));
END;

Subprogram LAST_NAME

This function returns an employee's last name.

Syntax

Function LAST_NAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call LAST_NAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.LAST_NAME(EMPLID));
END;

Subprogram LOCATION

This function returns an employee's location. Location is not their office location. Location represents the site this employee works at; for example, all Menlo Park employees have a location of CA01. For office location use the function MAIL_DROP.

Syntax

Function LOCATION
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call LOCATION:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.LOCATION(EMPLID));
END;

Subprogram MAILTO

This function returns an employee's e-mail address.

Syntax

Function MAILTO
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call MAILTO:

DECLARE
    EMPLID VARCHAR(11) := '99999';
    ROW_ID NUMBER;
BEGIN
    ROW_ID:=HTTP.SENDMAIL
    (
        MAILTO=>HTTP.PEOPLETOOLS.MAILTO(EMPLID)
    ,   SUBJECT=>'TEST'
    ,   MESSAGE=>'HELLO'
    )   ;
END;

Subprogram MAIL_DROP

This function returns an employee's office location.

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:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.MAIL_DROP(EMPLID));
END;

Subprogram MIDDLE_NAME

This function returns an employee's middle name.

Syntax

Function MIDDLE_NAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call MIDDLE_NAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.MIDDLE_NAME(EMPLID));
END;

Subprogram NAME

This function returns an employee's name. Name is last name followed by a comma followed by first name followed by middle name.

Syntax

Function NAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call NAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.NAME(EMPLID));
END;

Subprogram ORIG_HIRE_DT

This function returns an employee's hire date.

Syntax

Function ORIG_HIRE_DT
(
    EMPLID                        VARCHAR2
)
RETURN DATE;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call ORIG_HIRE_DT:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.ORIG_HIRE_DT(EMPLID));
END;

Subprogram PHONE

This function returns an employee's telephone number.

Syntax

Function PHONE
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call PHONE:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.PHONE(EMPLID));
END;

Subprogram REG_TEMP

This function determines if this employee is a regular employee or a temporary employee. REG_TEMP returns on of the following:

R (Regular)
T (Temporary)

Syntax

Function REG_TEMP
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call REG_TEMP:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    IF HTTP.PEOPLETOOLS.REG_TEMP(EMPLID) || HTTP.PEOPLETOOLS.EMPL_TYPE(EMPLID) = 'TH' THEN
        HTTP.WRITELN(EMPLID || ' IS TEMPORARY HOURLY');
    END IF;
END;

Subprogram STD_HOURS

This function returns an employee's standard hours.

Syntax

Function STD_HOURS
(
    EMPLID                        VARCHAR2
)
RETURN NUMBER;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call STD_HOURS:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(EMPLID || ' (FTE=' || TO_CHAR(HTTP.PEOPLETOOLS.STD_HOURS(EMPLID)/40,'0.0') || ')');
END;

Subprogram SUPERVISOR_ID

This function returns the employee number of this employee's supervisor.

Syntax

Function SUPERVISOR_ID
(
    EMPLID                        VARCHAR2
)
RETURN NUMBER;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call SUPERVISOR_ID:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(EMPLID || ' (SUPERVISOR_ID=' || HTTP.PEOPLETOOLS.SUPERVISOR_ID(EMPLID) || ')');
END;

Example

The following example illustrates how to retrieve the supervisor ID (or manager ID) of this supervisor ID:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(EMPLID || ' (MANAGER_ID=' || HTTP.PEOPLETOOLS.SUPERVISOR_ID(HTTP.PEOPLETOOLS.SUPERVISOR_ID(EMPLID)) || ')');
END;

Subprogram TERMINATION_DATE

This function returns an employee's termination date. NULL is returned if this employee is not terminated.

Syntax

Function TERMINATION_DATE
(
    EMPLID                        VARCHAR2
)
RETURN DATE;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call TERMINATION_DATE:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.TERMINATION_DATE(EMPLID));
END;

Subprogram USER_NAME

This function returns an employee's username in the database.

Syntax

Function USER_NAME
(
    EMPLID                        VARCHAR2
)
RETURN VARCHAR2;

Parameters

Parameter	Description
emplid	An employee number.

Example

The following example illustrates how to call USER_NAME:

DECLARE
    EMPLID VARCHAR(11) := '99999';
BEGIN
    HTTP.WRITELN(HTTP.PEOPLETOOLS.USER_NAME(EMPLID));
END;