Package sb_facassignment

This package provides the Common Business interface for the Faculty Assignment API (sb_facassignment).
Faculty assignments track instructional responsibilities for valid course sections. Only active faculty for the term specified may be assigned to sections.

The CRN must be a valid section for the term specified.
Percent response and percent session must be between 0 and 100. Other control values for a faculty assignment exist. A session override indicator can be null or O. The primary faculty indicator can be null or Y.
A valid CRN can have multiple faculty assignments, but only one assignment can be designated as the primary instructor.
Faculty workload must be between 0 and 999999.999.

When a new faculty member is assigned to a CRN, some values are defaulted.
The faculty contract code, category code, adjusted workload and primary indicator will be defaulted. If the position number and position number suffix fields are entered and the Banner HR system is installed, these values must for a valid employee job.

An update to a faculty assignment is restricted to certain columns.
Faculty workload, percent responsibility, percent session, contract type, position and suffix can be updated.

A deletion of a primary faculty assignment record is only valid if no other records exist.

@headcom

Program units

f_api_version

f_exists

f_isequal

f_query_all

f_query_one

f_query_by_rowid

f_query_one_lock

p_create

p_delete

p_lock

p_update

f_valid_fcnt

f_default_fcnt

f_default_category

f_calc_workload

f_check_prim_sirasgn

Types

facassignment_rec

facassignment_ref

facassignment_tab

Constants

M_ENTITY_NAME

M_ENTITY_NAME   CONSTANT VARCHAR2 (13) := 'FACASSIGNMENT';

Business Entity name.

facassignment_rec

TYPE facassignment_rec IS RECORD(

   r_term_code                   sirasgn.sirasgn_term_code%TYPE,
   r_crn                         sirasgn.sirasgn_crn%TYPE,
   r_pidm                        sirasgn.sirasgn_pidm%TYPE,
   r_category                    sirasgn.sirasgn_category%TYPE,
   r_percent_response            sirasgn.sirasgn_percent_response%TYPE,
   r_workload_adjust             sirasgn.sirasgn_workload_adjust%TYPE,
   r_percent_sess                sirasgn.sirasgn_percent_sess%TYPE,
   r_primary_ind                 sirasgn.sirasgn_primary_ind%TYPE,
   r_over_ride                   sirasgn.sirasgn_over_ride%TYPE,
   r_position                    sirasgn.sirasgn_position%TYPE,
   r_fcnt_code                   sirasgn.sirasgn_fcnt_code%TYPE,
   r_posn                        sirasgn.sirasgn_posn%TYPE,
   r_suff                        sirasgn.sirasgn_suff%TYPE,
   r_asty_code                   sirasgn.sirasgn_asty_code%TYPE,
   r_data_origin                 sirasgn.sirasgn_data_origin%TYPE,
   r_user_id                     sirasgn.sirasgn_user_id%TYPE,
   r_internal_record_id          gb_common.internal_record_id_type);

Entity record type.

facassignment_ref

TYPE facassignment_ref IS REF CURSOR
   RETURN facassignment_rec;

Entity cursor variable type.

facassignment_tab

TYPE facassignment_tab IS TABLE OF facassignment_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_term_code   sirasgn.sirasgn_term_code%TYPE,
      p_crn         sirasgn.sirasgn_crn%TYPE,
      p_pidm        sirasgn.sirasgn_pidm%TYPE,
      p_category    sirasgn.sirasgn_category%TYPE,
      p_rowid       VARCHAR2 DEFAULT NULL
   ) RETURN VARCHAR2

Checks to see if a record exists.

Parameters

	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required key.
	p_crn	Course reference of the course assigned to the faculty member. VARCHAR2(5) Required key.
	p_pidm	Internal personal identification number of the faculty member. NUMBER(8) Required key.
	p_category	Session indicator associated with the assignment. 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 facassignment_rec, rec_two facassignment_rec) RETURN VARCHAR2

Compares two records for equality.

Parameters

	rec_one	First record to compare. Defined as type facassignment_rec.
	rec_two	Second record to compare. Defined as type facassignment_rec.

Returns

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

f_query_all

Function f_query_all(
      p_term_code   sirasgn.sirasgn_term_code%TYPE DEFAULT NULL,
      p_crn         sirasgn.sirasgn_crn%TYPE DEFAULT NULL,
      p_pidm        sirasgn.sirasgn_pidm%TYPE,
      p_category    sirasgn.sirasgn_category%TYPE DEFAULT NULL
   ) RETURN facassignment_ref

Selects all records for the entity.
Note that parameters other than PIDM default to NULL and are ignored if not populated.
AND is used when multiple criteria are given.

Parameters

	p_term_code	Term associated with the transaction. For faculty transactions valid in STVTERM, or ARTERM for non-student transactions. VARCHAR2(6) Key.
	p_crn	Course reference number (CRN) of the course that the instructor is assigned to. VARCHAR2(5) Key.
	p_pidm	Internal personal identification number of the faculty member. NUMBER(8) Required key.
	p_category	Session indicator associated with the assignment. VARCHAR2(2) Key.

Returns

A cursor variable that will fetch the set of records.

f_query_one

Function f_query_one(
      p_term_code   sirasgn.sirasgn_term_code%TYPE,
      p_crn         sirasgn.sirasgn_crn%TYPE,
      p_pidm        sirasgn.sirasgn_pidm%TYPE,
      p_category    sirasgn.sirasgn_category%TYPE
   ) RETURN facassignment_ref

Selects one record using the key.

Parameters

	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required key.
	p_crn	Course reference number (CRN) of the course that the instructor was assigned to. VARCHAR2(5) Required key.
	p_pidm	Internal personal identification number of the faculty member. NUMBER(8) Required key.
	p_category	Session indicator associated with the assignment. 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 VARCHAR2) RETURN facassignment_ref

Selects one record using the ROWID.

Parameters

p_rowid

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

Returns

A cursor variable that will fetch exactly one record.

f_query_one_lock

Function f_query_one_lock(
      p_term_code   sirasgn.sirasgn_term_code%TYPE,
      p_crn         sirasgn.sirasgn_crn%TYPE,
      p_pidm        sirasgn.sirasgn_pidm%TYPE,
      p_category    sirasgn.sirasgn_category%TYPE,
      p_rowid       VARCHAR2 DEFAULT NULL
   ) RETURN facassignment_ref

Selects one record and locks it.

Parameters

	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required key.
	p_crn	Course reference number (CRN) of the course that the instructor was assigned to. VARCHAR2(5) Required key.
	p_pidm	Internal personal identification number of the faculty member. NUMBER(8) Required key.
	p_category	Session indicator associated with the assignment. 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_term_code                sirasgn.sirasgn_term_code%TYPE,
      p_crn                      sirasgn.sirasgn_crn%TYPE,
      p_pidm                     sirasgn.sirasgn_pidm%TYPE,
      p_category                 sirasgn.sirasgn_category%TYPE,
      p_percent_response         sirasgn.sirasgn_percent_response%TYPE,
      p_workload_adjust          sirasgn.sirasgn_workload_adjust%TYPE
            DEFAULT NULL,
      p_percent_sess             sirasgn.sirasgn_percent_sess%TYPE,
      p_primary_ind              sirasgn.sirasgn_primary_ind%TYPE DEFAULT NULL,
      p_over_ride                sirasgn.sirasgn_over_ride%TYPE DEFAULT NULL,
      p_position                 sirasgn.sirasgn_position%TYPE DEFAULT NULL,
      p_fcnt_code                sirasgn.sirasgn_fcnt_code%TYPE DEFAULT NULL,
      p_posn                     sirasgn.sirasgn_posn%TYPE DEFAULT NULL,
      p_suff                     sirasgn.sirasgn_suff%TYPE DEFAULT NULL,
      p_asty_code                sirasgn.sirasgn_asty_code%TYPE DEFAULT NULL,
      p_data_origin              sirasgn.sirasgn_data_origin%TYPE DEFAULT NULL,
      p_user_id                  sirasgn.sirasgn_user_id%TYPE DEFAULT gb_common.f_sct_user,
      p_rowid_out          OUT   VARCHAR2
   )

Creates a record.

Parameters

	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required key.
	p_crn	Course reference number (CRN) of the course that the instructor was assigned to. VARCHAR2(5) Required key.
	p_pidm	Internal personal identification number of the faculty member. NUMBER(8) Required key.
	p_category	Session indicator associated with the assignment. VARCHAR2(2) Required key.
	p_percent_response	Faculty members percentage of responsibility to the assignment. NUMBER(3)
	p_workload_adjust	Faculty Adjusted Workload for the instructional assignment. NUMBER(9,3)
	p_percent_sess	Faculty session percentage of responsibility of the instructional assignment. NUMBER(3)
	p_primary_ind	Primary instructor of the course. VARCHAR2(1)
	p_over_ride	Override Indicator. VARCHAR2(1).
	p_position	Faculty Position. NUMBER(8).
	p_fcnt_code	Contract type that the instructional assignment is associated with. VARCHAR2(2)
	p_posn	Position number. Used to link the faculty member's assignment to a position defined in the Banner Human Resources System. VARCHAR2(6)
	p_suff	Position number suffix. Used to link the faculty member's assignment to a position defined in the Banner Human Resources System. VARCHAR2(2)
	p_asty_code	Faculty Assignment Type Code. VARCHAR2(4)
	p_data_origin	Source system that generated the data. VARCHAR2(30)
	p_user_id	Oracle ID of the user who inserted or last updated the data.
	p_rowid_out	Database ROWID of the record to be created. VARCHAR2(18)

p_delete

Procedure p_delete(
      p_term_code   sirasgn.sirasgn_term_code%TYPE,
      p_crn         sirasgn.sirasgn_crn%TYPE,
      p_pidm        sirasgn.sirasgn_pidm%TYPE,
      p_category    sirasgn.sirasgn_category%TYPE,
      p_rowid       VARCHAR2 DEFAULT NULL
   )

Deletes a record.

Parameters

	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required key.
	p_crn	Course reference number (CRN) of the course that the instructor was assigned to. VARCHAR2(5) Required key.
	p_pidm	Internal personal identification number of the faculty member. NUMBER(8). Required key.
	p_category	Session indicator associated with the assignment. VARCHAR2(2) Required key.
	p_rowid	Database ROWID of the record to be deleted. VARCHAR2(18)

p_lock

Procedure p_lock(
      p_term_code              sirasgn.sirasgn_term_code%TYPE,
      p_crn                    sirasgn.sirasgn_crn%TYPE,
      p_pidm                   sirasgn.sirasgn_pidm%TYPE,
      p_category               sirasgn.sirasgn_category%TYPE,
      p_rowid_inout   IN OUT   VARCHAR2
   )

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

Parameters

	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required key.
	p_crn	Course reference number (CRN) of the course that the instructor was assigned to. VARCHAR2(5) Required key.
	p_pidm	Internal personal identification number of the faculty member. NUMBER(8) Required key.
	p_category	Session indicator associated with the assignment. VARCHAR2(2) Required key.
	p_rowid_inout	Database ROWID of the record to be locked. VARCHAR2(18) Required.

p_update

Procedure p_update(
      p_term_code          sirasgn.sirasgn_term_code%TYPE,
      p_crn                sirasgn.sirasgn_crn%TYPE,
      p_pidm               sirasgn.sirasgn_pidm%TYPE,
      p_category           sirasgn.sirasgn_category%TYPE,
      p_percent_response   sirasgn.sirasgn_percent_response%TYPE
            DEFAULT dml_common.f_unspecified_number,
      p_workload_adjust    sirasgn.sirasgn_workload_adjust%TYPE
            DEFAULT dml_common.f_unspecified_number,
      p_percent_sess       sirasgn.sirasgn_percent_sess%TYPE
            DEFAULT dml_common.f_unspecified_number,
      p_primary_ind        sirasgn.sirasgn_primary_ind%TYPE
            DEFAULT dml_common.f_unspecified_string,
      p_over_ride          sirasgn.sirasgn_over_ride%TYPE
            DEFAULT dml_common.f_unspecified_string,
      p_position           sirasgn.sirasgn_position%TYPE
            DEFAULT dml_common.f_unspecified_number,
      p_fcnt_code          sirasgn.sirasgn_fcnt_code%TYPE
            DEFAULT dml_common.f_unspecified_string,
      p_posn               sirasgn.sirasgn_posn%TYPE
            DEFAULT dml_common.f_unspecified_string,
      p_suff               sirasgn.sirasgn_suff%TYPE
            DEFAULT dml_common.f_unspecified_string,
      p_asty_code          sirasgn.sirasgn_asty_code%TYPE
            DEFAULT dml_common.f_unspecified_string,
      p_data_origin        sirasgn.sirasgn_data_origin%TYPE
            DEFAULT dml_common.f_unspecified_string,
      p_user_id            sirasgn.sirasgn_user_id%TYPE DEFAULT gb_common.f_sct_user,
      p_rowid              VARCHAR2 DEFAULT NULL
   )

Updates a record.

Parameters

	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required key.
	p_crn	Course reference number (CRN) of the course that the instructor was assigned to. VARCHAR2(5) Required key.
	p_pidm	Internal personal identification number of the faculty member. NUMBER(8) Required key.
	p_category	Session indicator associated with the assignment. VARCHAR2(2) Required key.
	p_percent_response	Faculty members percentage of responsibility to the assignment. NUMBER(3)
	p_workload_adjust	Faculty Adjusted Workload for the instructional assignment. NUMBER(9,3)
	p_percent_sess	Faculty session percentage of responsibility of the instructional assignment. NUMBER(3)
	p_primary_ind	Primary instructor of the course. VARCHAR2(1)
	p_over_ride	Override Indicator. VARCHAR2(1).
	p_position	Faculty Position. NUMBER(8).
	p_fcnt_code	Contract type that the instructional assignment is associated with. VARCHAR2(2)
	p_posn	Position number. Used to link the faculty member's assignment to a position defined in the Banner Human Resources System. VARCHAR2(6)
	p_suff	Position number suffix. Used to link the faculty member's assignment to a position defined in the Banner Human Resources System. VARCHAR2(2)
	p_asty_code	Faculty Assignment Type Code. VARCHAR2(4)
	p_data_origin	Source system that generated the data. VARCHAR2(30)
	p_user_id	Oracle ID of the user who inserted or last updated the data. VARCHAR2(30)
	p_rowid	Database ROWID of the record to be updated. VARCHAR2(18)

f_valid_fcnt

Function f_valid_fcnt(
      p_pidm        siricnt.siricnt_pidm%TYPE,
      p_term_code   siricnt.siricnt_term_code_eff%TYPE,
      p_fcnt_code   sirasgn.sirasgn_fcnt_code%TYPE
   ) RETURN VARCHAR2

Validates STVFCNT code with the SIRICNT table.

Parameters

	p_pidm	Personal internal identification number of the faculty member. VARCHAR2(8) Required.
	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required.
	p_fcnt_code	Contract type that the instructional assignment is associated with. VARCHAR2(6) Required.

Returns

VARCHAR2

f_default_fcnt

Function f_default_fcnt(
      p_pidm        siricnt.siricnt_pidm%TYPE,
      p_term_code   siricnt.siricnt_term_code_eff%TYPE
   ) RETURN sirasgn.sirasgn_fcnt_code%TYPE

Defaults the contract type that the instructional assignment is associated with.

Parameters

	p_pidm	Personal internal identification number of the faculty member. VARCHAR2(8) Required.
	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required.

Returns

SIRASGN_FCNT_CODE VARCHAR(2)

f_default_category

Function f_default_category(
      p_crn         sirasgn.sirasgn_crn%TYPE,
      p_term_code   sirasgn.sirasgn_term_code%TYPE,
      p_category    sirasgn.sirasgn_category%TYPE
   ) RETURN sirasgn.sirasgn_category%TYPE

Defaults the session indicator associated with the assignment.

Parameters

	p_pidm	Personal internal identification number of the faculty member. VARCHAR2(8) Required.
	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required.
	p_category	Session indicator associated with the assignment. VARCHAR2(2) Required.

Returns

SIRASGN_CATEGORY VARCHAR(2)

f_calc_workload

Function f_calc_workload(
      p_term_code   sirasgn.sirasgn_term_code%TYPE,
      p_crn         sirasgn.sirasgn_term_code%TYPE,
      p_category    sirasgn.sirasgn_category%TYPE
   ) RETURN sirasgn.sirasgn_workload_adjust%TYPE

Defaults the session indicator associated with the assignment.

Parameters

	p_term_code	Term of the faculty member assignment. VARCHAR2(6) Required.
	p_crn	Course reference number of the course associated with the faculty member. VARCHAR2(5) Required.
	p_category	The session indicator associated with the assignment. VARCHAR2(2) Required.

Returns

SIRASGN_WORKLOAD_ADJUST NUMBER(9,3)

f_check_prim_sirasgn

Function f_check_prim_sirasgn(
      p_crn         sirasgn.sirasgn_crn%TYPE,
      p_term_code   sirasgn.sirasgn_term_code%TYPE,
      p_rowid       VARCHAR2 DEFAULT NULL
   ) RETURN sirasgn.sirasgn_primary_ind%TYPE

Defaults whether or not the faculty member is primary instructor of the course.

Parameters

	p_crn	Course reference number (CRN) of the course associated with the faculty member. VARCHAR2(5) Required.
	p_category	Session indicator associated with the assignment. VARCHAR2(2) Required.
	p_rowid	Database ROWID of the record to be selected. VARCHAR2(18)

Returns

SIRASGN_PRIMARY_IND VARCHAR2(1)