Package nb_job

Package nb_job_base

This package provides the common business interface for a Job Base (NB_JOB_BASE).

The Job Base API establishes basic information about an employee's job assignment. The data maintained by this API is not date sensitive.
The API also maintains other information related to a Job Base such as the job history.

Before creating Job Base information can be created, position and employee information must be created in Banner.

Program units

f_api_version	Returns the API version number.
f_exists	Checks if a record exists.
f_isequal	Compares two records for equality.
f_query_all	Selects all records for the entity.
f_query_one	Selects one record using key.
f_query_by_rowid	Selects one record using ROWID.
f_query_one_lock	Selects one record and locks it.
p_create	Creates a record.
p_delete	Deletes a record.
p_lock	Locks a record.
p_update	Updates a record.

Types

job_base_rec	Business Entity record type
job_base_ref	Entity cursor variable type
job_base_tab	Entity table type

Constants

M_ENTITY_NAME	Business Entity Name
M_BASE_TABLE_NAME	Base table name

M_ENTITY_NAME

M_ENTITY_NAME    CONSTANT VARCHAR2(8) := 'JOB_BASE';

Business Entity Name

M_BASE_TABLE_NAME

M_BASE_TABLE_NAME    CONSTANT VARCHAR2(7) := 'NBRBJOB';

Base table name

job_base_rec

TYPE job_base_rec IS RECORD (
  r_pidm                    nbrbjob.nbrbjob_pidm%TYPE,
  r_posn                    nbrbjob.nbrbjob_posn%TYPE,
  r_suff                    nbrbjob.nbrbjob_suff%TYPE,
  r_begin_date              nbrbjob.nbrbjob_begin_date%TYPE,
  r_end_date                nbrbjob.nbrbjob_end_date%TYPE,
  r_defer_bal               nbrbjob.nbrbjob_defer_bal%TYPE,
  r_contract_type           nbrbjob.nbrbjob_contract_type%TYPE,
  r_salary_encumbrance      nbrbjob.nbrbjob_salary_encumbrance%TYPE,
  r_contract_begin_date     nbrbjob.nbrbjob_contract_begin_date%TYPE,
  r_contract_end_date       nbrbjob.nbrbjob_contract_end_date%TYPE,
  r_total_contract_hrs      nbrbjob.nbrbjob_total_contract_hrs%TYPE,
  r_total_encumbrance_hrs   nbrbjob.nbrbjob_total_encumbrance_hrs%TYPE,
  r_step_incr_mon           nbrbjob.nbrbjob_step_incr_mon%TYPE,
  r_step_incr_day           nbrbjob.nbrbjob_step_incr_day%TYPE,
  r_coas_code               nbrbjob.nbrbjob_coas_code%TYPE,
  r_accrue_leave_ind        nbrbjob.nbrbjob_accrue_leave_ind%TYPE,
  r_civil_service_ind       nbrbjob.nbrbjob_civil_service_ind%TYPE,
  r_encumbrance_change_ind  nbrbjob.nbrbjob_encumbrance_change_ind%TYPE,
  r_fringe_encumbrance      nbrbjob.nbrbjob_fringe_encumbrance%TYPE,
  r_ipeds_rept_ind          nbrbjob.nbrbjob_ipeds_rept_ind%TYPE,
  r_facl_statscan_rept_ind  nbrbjob.nbrbjob_facl_statscan_rept_ind%TYPE,
  r_probation_begin_date    nbrbjob.nbrbjob_probation_begin_date%TYPE,
  r_probation_end_date      nbrbjob.nbrbjob_probation_end_date%TYPE,
  r_probation_units         nbrbjob.nbrbjob_probation_units%TYPE,
  r_eligible_date           nbrbjob.nbrbjob_eligible_date%TYPE,
  r_user_id                 nbrbjob.nbrbjob_user_id%TYPE,
  r_data_origin             nbrbjob.nbrbjob_data_origin%TYPE,
  r_internal_record_id      gb_common.internal_record_id_type,
  r_future_salary_enc       nbrbjob.nbrbjob_future_salary_enc%TYPE,
  r_future_fringe_enc       nbrbjob.nbrbjob_future_fringe_enc%TYPE);

Business Entity record type

job_base_ref

TYPE job_base_ref IS REF CURSOR RETURN job_base_rec;

Entity cursor variable type

job_base_tab

TYPE job_base_tab IS TABLE OF job_base_rec INDEX BY BINARY_INTEGER;

Entity table type

f_api_version

Function f_api_version RETURN PLS_INTEGER

Returns the API version number.

Returns

Version of the API signature. Changes only when the signature changes.

f_exists

Function f_exists(p_pidm  IN nbrbjob.nbrbjob_pidm%TYPE,
                  p_posn  IN nbrbjob.nbrbjob_posn%TYPE,
                  p_suff  IN nbrbjob.nbrbjob_suff%TYPE,
                  p_rowid IN gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2

Checks if a record exists.

Parameters

	p_pidm	Internal identification number of the employee. NUMBER(8) Required Key
	p_posn	Position to which a job is attached. VARCHAR2(6) Required Key
	p_suff	Unique identifier that helps create and maintain multiple jobs for the same position, for the same employee. VARCHAR2(2) Required Key
	p_rowid	Database ROWID of the record to be selected. VARCHAR2(18)

Returns

Y if found, otherwise N.

f_isequal

Function f_isequal(rec_one IN job_base_rec, rec_two IN job_base_rec)
  RETURN VARCHAR2

Compares two records for equality.
Tests each field of rec_one against the corresponding field of rec_two. Two null values are considered equal.

Parameters

	rec_one	The first record to compare. Type job_base_rec Required
	rec_two	The second record to compare. Type job_base_rec Required

Returns

Y if all values in records are equal, otherwise N. Nulls match Nulls.

f_query_all

Function f_query_all(p_pidm IN nbrbjob.nbrbjob_pidm%TYPE,
                     p_posn IN nbrbjob.nbrbjob_posn%TYPE,
                     p_suff IN nbrbjob.nbrbjob_suff%TYPE)
  RETURN job_base_ref

Selects all records for the entity.

Parameters

	p_pidm	Internal identification number of the employee. NUMBER(8) Required Key
	p_posn	Position to which a job is attached. VARCHAR2(6) Required Key
	p_suff	Unique identifier that helps create and maintain multiple jobs for the same position, for the same employee. VARCHAR2(2) Required Key

Returns

A cursor variable that will fetch the set of records.

f_query_one

Function f_query_one(p_pidm IN nbrbjob.nbrbjob_pidm%TYPE,
                     p_posn IN nbrbjob.nbrbjob_posn%TYPE,
                     p_suff IN nbrbjob.nbrbjob_suff%TYPE)
  RETURN job_base_ref

Selects one record using key.

Parameters

	p_pidm	Internal identification number of the employee. NUMBER(8) Required Key
	p_posn	Position to which a job is attached. VARCHAR2(6) Required Key
	p_suff	Unique identifier that helps create and maintain multiple jobs for the same position, for the same employee. VARCHAR2(2) Required Key

Returns

A cursor variable that will fetch exactly one record.

f_query_by_rowid

Function f_query_by_rowid(p_rowid IN gb_common.internal_record_id_type)
  RETURN job_base_ref

Selects one record using ROWID.

Parameters

p_rowid

Database ROWID of the record to be selected. VARCHAR2(18) Required

Returns

A cursor variable that will fetch exactly one record.

f_query_one_lock

Function f_query_one_lock(p_pidm  IN nbrbjob.nbrbjob_pidm%TYPE,
                          p_posn  IN nbrbjob.nbrbjob_posn%TYPE,
                          p_suff  IN nbrbjob.nbrbjob_suff%TYPE,
                          p_rowid IN gb_common.internal_record_id_type DEFAULT NULL)
  RETURN job_base_ref

Selects one record and locks it.

Parameters

	p_pidm	Internal identification number of the employee. NUMBER(8) Required Key
	p_posn	Position to which a job is attached. VARCHAR2(6) Required Key
	p_suff	Unique identifier that helps create and maintain multiple jobs for the same position, for the same employee. VARCHAR2(2) Required Key
	p_rowid	Database ROWID of the record to be selected. VARCHAR2(18)

Returns

A cursor variable for one record, locking the record.

p_create

Procedure p_create(p_pidm                   IN nbrbjob.nbrbjob_pidm%TYPE,
                   p_posn                   IN nbrbjob.nbrbjob_posn%TYPE,
                   p_suff                   IN nbrbjob.nbrbjob_suff%TYPE,
                   p_begin_date             IN nbrbjob.nbrbjob_begin_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_contract_type          IN nbrbjob.nbrbjob_contract_type%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_salary_encumbrance     IN nbrbjob.nbrbjob_salary_encumbrance%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_contract_begin_date    IN nbrbjob.nbrbjob_contract_begin_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_contract_end_date      IN nbrbjob.nbrbjob_contract_end_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_total_contract_hrs     IN nbrbjob.nbrbjob_total_contract_hrs%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_total_encumbrance_hrs  IN nbrbjob.nbrbjob_total_encumbrance_hrs%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_step_incr_mon          IN nbrbjob.nbrbjob_step_incr_mon%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_step_incr_day          IN nbrbjob.nbrbjob_step_incr_day%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_accrue_leave_ind       IN nbrbjob.nbrbjob_accrue_leave_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_civil_service_ind      IN nbrbjob.nbrbjob_civil_service_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_ipeds_rept_ind         IN nbrbjob.nbrbjob_ipeds_rept_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_facl_statscan_rept_ind IN nbrbjob.nbrbjob_facl_statscan_rept_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_probation_begin_date   IN nbrbjob.nbrbjob_probation_begin_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_probation_end_date     IN nbrbjob.nbrbjob_probation_end_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_probation_units        IN nbrbjob.nbrbjob_probation_units%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_eligible_date          IN nbrbjob.nbrbjob_eligible_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_user_id                IN nbrbjob.nbrbjob_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_data_origin            IN nbrbjob.nbrbjob_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid_out              OUT gb_common.internal_record_id_type,
                   p_warning_msg_out        OUT gb_common_strings.err_type)

Creates a record.

Parameters

p_pidm

Internal identification number of the employee. NUMBER(8) Required Key

p_posn

Position to which a job is attached. VARCHAR2(6) Required Key

p_suff

Unique identifier that helps create and maintain multiple jobs for the same position, for the same employee. VARCHAR2(2) Required Key

p_begin_date

Begin date of the job. DATE Required

p_contract_type

Indicates the type of job. Only one primary job per person is allowed. VARCHAR2(1) Required

	P	Primary (default)
	S	Secondary
	O	Overload

p_salary_encumbrance

The amount of money that a person will be paid in a job during the current fiscal year. NUMBER(11,2) Required

p_contract_begin_date

Date on which a contract begins. This date is significant for the encumbering process. DATE

p_contract_end_date

Date on which a contract ends. This date is significant for the encumbering process. DATE

p_total_contract_hrs

The total number of hours of the contract. This is especially helpful if a contract spans multiple fiscal years. NUMBER(6,2)

p_total_encumbrance_hrs

The sum of the Job Detail Encumbrance Hours for the currently active fiscal year. It represents the total hours contracted to be paid in the active fiscal year. NUMBER(6,2)

p_step_incr_mon

Month in which an automatic step increase is to take place. VARCHAR2(2)

p_step_incr_day

Day of the month in which an automatic step increase is to take place. VARCHAR2(2)

p_accrue_leave_ind

Determines whether leave is accrued for a job. VARCHAR2(1) Required

	Y	Yes
	N	No

p_civil_service_ind

Indicates if an assignment is a civil service job. VARCHAR2(1) Required

	Y	Yes
	N	No

p_ipeds_rept_ind

Indicates if a job should be reported on the IPEDS report. VARCHAR2(1) Required

	Y	Yes
	N	No

p_facl_statscan_rept_ind

This indicator will allow the Canadian user to gather data that is necessary for reporting of the Annual Faculty STATSCAN reports. VARCHAR2(1) Required

	Y	Yes
	N	No

p_probation_begin_date

Begin date of the probationary period. DATE

p_probation_end_date

End date of the probationary period. This data is calculated based on the Probationary Period and Probation Begin Date, but may be overridden. DATE

p_probation_units

Identifies the number of Probationary Units. It represents months or days depending on the Probationary Period Units institutional setting. NUMBER(3)

p_eligible_date

Eligible Date for the step increase. DATE

p_user_id

The Oracle ID of the user who changed the record. VARCHAR2(30)

p_data_origin

Source system that created or updated the row. VARCHAR2(30)

p_rowid_out

Database ROWID of the record to be created. VARCHAR2(18) Required

p_warning_msg_out

Returned warning messages. Warning messages will only be returned if the record is successfully created. gb_common_strings.err_type

p_delete

Procedure p_delete(p_pidm            IN nbrbjob.nbrbjob_pidm%TYPE,
                   p_posn            IN nbrbjob.nbrbjob_posn%TYPE,
                   p_suff            IN nbrbjob.nbrbjob_suff%TYPE,
                   p_rowid           IN gb_common.internal_record_id_type DEFAULT NULL,
                   p_warning_msg_out OUT gb_common_strings.err_type)

Deletes a record.

Parameters

	p_pidm	Internal identification number of the employee. NUMBER(8) Required Key
	p_posn	Position to which a job is attached. VARCHAR2(6) Required Key
	p_suff	Unique identifier that helps create and maintain multiple jobs for the same position, for the same employee. VARCHAR2(2) Required Key
	p_rowid	Database ROWID of the record to be deleted. VARCHAR2(18)
	p_warning_msg_out	Returned warning messages. Warning messages will only be returned if the record is successfully deleted. gb_common_strings.err_type

p_lock

Procedure p_lock(p_pidm        IN nbrbjob.nbrbjob_pidm%TYPE,
                 p_posn        IN nbrbjob.nbrbjob_posn%TYPE,
                 p_suff        IN nbrbjob.nbrbjob_suff%TYPE,
                 p_rowid_inout IN OUT gb_common.internal_record_id_type)

Locks a record.
If ROWID is not passed in, the record is located using the key values and the ROWID of the locked row is passed in p_rowid_inout

Parameters

	p_pidm	Internal identification number of the employee. NUMBER(8) Required Key
	p_posn	Position to which a job is attached. VARCHAR2(6) Required Key
	p_suff	Unique identifier that helps create and maintain multiple jobs for the same position, for the same employee. VARCHAR2(2) Required Key
	p_rowid_inout	Database ROWID of the record to be locked. VARCHAR2(18) Required

p_update

Procedure p_update(p_pidm                   IN nbrbjob.nbrbjob_pidm%TYPE,
                   p_posn                   IN nbrbjob.nbrbjob_posn%TYPE,
                   p_suff                   IN nbrbjob.nbrbjob_suff%TYPE,
                   p_end_date               IN nbrbjob.nbrbjob_end_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_defer_bal              IN nbrbjob.nbrbjob_defer_bal%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_contract_type          IN nbrbjob.nbrbjob_contract_type%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_salary_encumbrance     IN nbrbjob.nbrbjob_salary_encumbrance%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_contract_begin_date    IN nbrbjob.nbrbjob_contract_begin_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_contract_end_date      IN nbrbjob.nbrbjob_contract_end_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_total_contract_hrs     IN nbrbjob.nbrbjob_total_contract_hrs%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_total_encumbrance_hrs  IN nbrbjob.nbrbjob_total_encumbrance_hrs%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_step_incr_mon          IN nbrbjob.nbrbjob_step_incr_mon%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_step_incr_day          IN nbrbjob.nbrbjob_step_incr_day%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_accrue_leave_ind       IN nbrbjob.nbrbjob_accrue_leave_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_civil_service_ind      IN nbrbjob.nbrbjob_civil_service_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_encumbrance_change_ind IN nbrbjob.nbrbjob_encumbrance_change_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_fringe_encumbrance     IN nbrbjob.nbrbjob_fringe_encumbrance%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_ipeds_rept_ind         IN nbrbjob.nbrbjob_ipeds_rept_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_facl_statscan_rept_ind IN nbrbjob.nbrbjob_facl_statscan_rept_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_probation_begin_date   IN nbrbjob.nbrbjob_probation_begin_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_probation_end_date     IN nbrbjob.nbrbjob_probation_end_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_probation_units        IN nbrbjob.nbrbjob_probation_units%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_eligible_date          IN nbrbjob.nbrbjob_eligible_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_user_id                IN nbrbjob.nbrbjob_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_data_origin            IN nbrbjob.nbrbjob_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid                  IN gb_common.internal_record_id_type DEFAULT NULL,
                   p_warning_msg_out        OUT gb_common_strings.err_type,
                   p_future_salary_enc      IN nbrbjob.nbrbjob_future_salary_enc%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_future_fringe_enc      IN nbrbjob.nbrbjob_future_fringe_enc%TYPE DEFAULT dml_common.f_unspecified_number)

Updates a record.

Parameters

p_pidm

Internal identification number of the employee. NUMBER(8) Required Key

p_posn

Position to which a job is attached. VARCHAR2(6) Required Key

p_suff

Unique identifier that helps create and maintain multiple jobs for the same position, for the same employee. VARCHAR2(2) Required Key

p_end_date

The date the job will terminate. If date is null, then the job is defined as indefinite. The employee will be paid for this day. DATE

p_defer_bal

Amount of deferred pay accumulated for a job. NUMBER(11,2) Required

p_contract_type

Indicates the type of job. Only one primary job per person is allowed. VARCHAR2(1) Required

	P	Primary (default)
	S	Secondary
	O	Overload

p_salary_encumbrance

Amount of money that a person will be paid in a job during the current fiscal year. NUMBER(11,2) Required

p_contract_begin_date

Date on which a contract begins. This date is significant for the encumbering process. DATE

p_contract_end_date

Date which a contract ends. This date is significant for the encumbering process. DATE

p_total_contract_hrs

Total number of hours of the contract. This is especially helpful if a contract spans multiple fiscal years. NUMBER(6,2)

p_total_encumbrance_hrs

The sum of the Job Detail Encumbrance Hours for the currently active fiscal year. It represents the total hours contracted to be paid in the active fiscal year. NUMBER(6,2)

p_step_incr_mon

Month in which an automatic step increase is to take place. VARCHAR2(2)

p_step_incr_day

Day of the month in which an automatic step increase is to take place. VARCHAR2(2)

p_accrue_leave_ind

Determines whether leave is accrued for a job. VARCHAR2(1) Required

	Y	Yes
	N	No

p_civil_service_ind

Indicates if an assignment is a civil service job. VARCHAR2(1) Required

	Y	Yes
	N	No

p_encumbrance_change_ind

Indicates if the encumbrance amount has changed for a job. VARCHAR2(1) Required

	Y	Yes
	N	No

p_fringe_encumbrance

Cost of fringe benefits that a person will be paid in a job during the current fiscal year. NUMBER(11,2)

p_ipeds_rept_ind

Indicates if a job should be reported on the IPEDS report. VARCHAR2(1) Required

	Y	Yes
	N	No

p_facl_statscan_rept_ind

This indicator will allow the Canadian user to gather data that is necessary for reporting of the Annual Faculty STATSCAN reports. VARCHAR2(1) Required

	Y	Yes
	N	No

p_probation_begin_date

Begin date of the probationary period. DATE

p_probation_end_date

End date of the probationary period. This data is calculated based on the Probationary Period and Probation Begin Date, but may be overridden. DATE

p_probation_units

Identifies the number of Probationary Units. It represents months or days depending on the Probationary Period Units institutional setting. NUMBER(3)

p_eligible_date

Eligible date for the step increase. DATE

p_user_id

The Oracle ID of the user who changed the record. VARCHAR2(30)

p_data_origin

Source system that created or updated the row. VARCHAR2(30)

p_rowid

Database ROWID of the record to be updated. VARCHAR2(18)

p_warning_msg_out

Returned warning messages. Warning messages will only be returned if the record is successfully updated. gb_common_strings.err_type

p_future_salary_enc

The estimated amount a person will be paid in a job in future fiscal years. NUMBER(17,2)

p_future_fringe_enc

The estimated cost of fringe benefits a person will be paid in a job in future fiscal years. NUMBER(17,2)