BANINST1@S10B80

index

Package sb_faculty

This package provides the Common Business interface for the Faculty API.
 
Faculty information defines the persons who serve as instructors and advisors.
A person record must exist before a faculty / advisor record can be created.
Faculty and advisor information is effective term-based.  Faculty information can be valid over multiple terms.  New effective terms are created to reflect changes in faculty status and status date, availability as an instructor (faculty) and/or advisor, category, staff type and workload rule.  Faculty and advisor processing logic will return the maximum effective term record that is less than or equal to the processing term if there is no exact match on term.
 
A faculty member can be both an instructor and an advisor.  The faculty indicator (scheduled indicator) is set to 'Y' to allow instructional and non-instructional assignments for the term.  If no instructional assignments are permitted, the faculty (scheduled) indicator is blank (null).  The advisor indicator is set to 'Y' (Yes) to allow assignment as an advisor for the term.
If no advising assignments are permitted, the advisor indicator is blank (null).
 
Instructors must have an active status to be given instructional and non-instructional assignments for the term.  Instructor status cannot be changed to inactive for the term if instructional and/or non-instructional assignments exist.  Active status is determined based on the Active Indicator for the Faculty Status Code Validation Table (STVFCST).


Program units
f_api_version   Returns the API version number.
f_exists   Checks to see 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 the key.
f_query_by_rowid   Selects one record using the ROWID.
f_query_one_lock   Selects one record and locks it.
f_query_one_scheduled   Selects one record using the key and term code effective where schd_ind = Y.
p_create   Creates a record.
p_delete   Deletes a record.
p_lock   Locks a record.
p_update   Updates a record.

Types
faculty_rec   Entity record type
faculty_ref   Entity cursor variable type
faculty_tab   Entity table type

Constants
M_ENTITY_NAME   Business Entity name


M_ENTITY_NAME

M_ENTITY_NAME   CONSTANT VARCHAR2 (7) := 'FACULTY';

Business Entity name


faculty_rec

TYPE faculty_rec IS RECORD(

   r_pidm                        sibinst.sibinst_pidm%TYPE,
   r_term_code_eff               sibinst.sibinst_term_code_eff%TYPE,
   r_fcst_code                   sibinst.sibinst_fcst_code%TYPE,
   r_fctg_code                   sibinst.sibinst_fctg_code%TYPE,
   r_fstp_code                   sibinst.sibinst_fstp_code%TYPE,
   r_fcnt_code                   sibinst.sibinst_fcnt_code%TYPE,
   r_schd_ind                    sibinst.sibinst_schd_ind%TYPE,
   r_advr_ind                    sibinst.sibinst_advr_ind%TYPE,
   r_fcst_date                   sibinst.sibinst_fcst_date%TYPE,
   r_wkld_code                   sibinst.sibinst_wkld_code%TYPE,
   r_cntr_code                   sibinst.sibinst_cntr_code%TYPE,
   r_appoint_date                sibinst.sibinst_appoint_date%TYPE,
   r_data_origin                 sibinst.sibinst_data_origin%TYPE,
   r_user_id                     sibinst.sibinst_user_id%TYPE,
   r_override_process_ind        sibinst.sibinst_override_process_ind%TYPE,
   r_override_proc_userid        sibinst.sibinst_override_proc_userid%TYPE,
   r_override_proc_date          sibinst.sibinst_override_proc_date%TYPE,
   r_internal_record_id          gb_common.internal_record_id_type);

Entity record type


faculty_ref

TYPE faculty_ref IS REF CURSOR
   RETURN faculty_rec;

Entity cursor variable type


faculty_tab

TYPE faculty_tab IS TABLE OF faculty_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          sibinst.sibinst_pidm%TYPE,
                  p_term_code_eff sibinst.sibinst_term_code_eff%TYPE,
                  p_rowid         VARCHAR2 DEFAULT NULL) RETURN VARCHAR2

Checks to see if a record exists.

Parameters
p_pidm   Internal personal identification number of the faculty member.  NUMBER(8) Required key
p_term_code_eff   Effective term of the faculty member. VARCHAR2(6) 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 faculty_rec, rec_two faculty_rec)
  RETURN VARCHAR2

Compares two records for equality.  Nulls match nulls.

Parameters
rec_one   First record to compare. Defined as type faculty_rec.
rec_two   Second record to compare. Defined as type faculty_rec.

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


f_query_all

Function f_query_all(p_pidm          sibinst.sibinst_pidm%TYPE,
                     p_term_code_eff sibinst.sibinst_term_code_eff%TYPE DEFAULT NULL)
  RETURN faculty_ref

Selects all records for the entity.

Parameters
p_pidm   Internal personal identification number of the faculty member.  NUMBER(8) Required key
p_term_code_eff   Effective term of the faculty member.  VARCHAR2(6) key

Returns
A cursor variable that will fetch the set of records.


f_query_one

Function f_query_one(p_pidm          sibinst.sibinst_pidm%TYPE,
                     p_term_code_eff sibinst.sibinst_term_code_eff%TYPE)
  RETURN faculty_ref

Selects one record using the key.

Parameters
p_pidm   Internal personal identification number of the faculty member.  NUMBER(8) Required key
p_term_code_eff   Effective term of the faculty member.  VARCHAR2(6) 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 faculty_ref

Selects one record using the 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          sibinst.sibinst_pidm%TYPE,
                          p_term_code_eff sibinst.sibinst_term_code_eff%TYPE,
                          p_rowid         VARCHAR2 DEFAULT NULL)
  RETURN faculty_ref

Selects one record and locks it.

Parameters
p_pidm   Internal personal identification number of the faculty member. NUMBER(8) Required key
p_term_code_eff   Effective term of the faculty member.  VARCHAR2(6) Required key
p_rowid   Database ROWID of the record to be selected.  VARCHAR2(18)

Returns
A cursor variable for one record, locking the record.


f_query_one_scheduled

Function f_query_one_scheduled(p_pidm          sibinst.sibinst_pidm%TYPE,
                               p_term_code_eff sibinst.sibinst_term_code_eff%TYPE)
  RETURN faculty_ref

Selects one record using the key and term code effective where schd_ind = Y.

Parameters
p_pidm   Internal personal identification number of the faculty member.  NUMBER(8) Required key
p_term_code_eff   Effective term of the faculty member.  VARCHAR2(6) Required key

Returns
A cursor variable that will fetch exactly one record.


p_create

Procedure p_create(p_pidm                 sibinst.sibinst_pidm%TYPE,
                   p_term_code_eff        sibinst.sibinst_term_code_eff%TYPE,
                   p_fcst_code            sibinst.sibinst_fcst_code%TYPE,
                   p_fctg_code            sibinst.sibinst_fctg_code%TYPE DEFAULT NULL,
                   p_fstp_code            sibinst.sibinst_fstp_code%TYPE DEFAULT NULL,
                   p_fcnt_code            sibinst.sibinst_fcnt_code%TYPE DEFAULT NULL,
                   p_schd_ind             sibinst.sibinst_schd_ind%TYPE DEFAULT NULL,
                   p_advr_ind             sibinst.sibinst_advr_ind%TYPE DEFAULT NULL,
                   p_fcst_date            sibinst.sibinst_fcst_date%TYPE,
                   p_wkld_code            sibinst.sibinst_wkld_code%TYPE DEFAULT NULL,
                   p_cntr_code            sibinst.sibinst_cntr_code%TYPE DEFAULT NULL,
                   p_appoint_date         sibinst.sibinst_appoint_date%TYPE DEFAULT NULL,
                   p_data_origin          sibinst.sibinst_data_origin%TYPE DEFAULT NULL,
                   p_user_id              sibinst.sibinst_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_override_process_ind sibinst.sibinst_override_process_ind%TYPE DEFAULT 'N',
                   p_override_proc_userid sibinst.sibinst_override_proc_userid%TYPE DEFAULT NULL,
                   p_override_proc_date   sibinst.sibinst_override_proc_date%TYPE DEFAULT NULL,
                   p_rowid_out            OUT VARCHAR2)

Creates a record.

Parameters
p_pidm   Internal personal identification number of the faculty member.  NUMBER(8) Required key
p_term_code_eff   Effective term of the faculty member.  VARCHAR2(6) Required
p_fcst_code   Faculty member status.  VARCHAR2(2) Required
p_fctg_code   Faculty member category.  VARCHAR2(6)
p_fstp_code   Faculty member staff type which is required for workload calculation.  VARCHAR2(4)
p_fcnt_code   Faculty member contract rule which is required for workload calculation.  VARCHAR2(2)
p_schd_ind   Faculty member available for schedule indicator. VARCHAR2(1)
p_advr_ind   Faculty member available for advising indicator.  VARCHAR2(1)
p_fcst_date   Faculty member status date. DATE Required
p_wkld_code   Faculty member workload rule code. VARCHAR2(6)
p_cntr_code   Faculty member contract rule code. VARCHAR2(6)
p_appoint_date   Date the appointment was awarded.  DATE
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_override_process_ind   Indicator to allow bypass of all process security on SOAFACS  VARCHAR2(1) Required
p_override_proc_userid   User that updates the bypass indicator of all process security on SOAFACS  VARCHAR2(30)
p_override_proc_date   Date of update to the bypass indicator of all process security on SOAFACS  DATE
p_rowid_out   Database ROWID of the record to be created.  VARCHAR2(18)


p_delete

Procedure p_delete(p_pidm          sibinst.sibinst_pidm%TYPE,
                   p_term_code_eff sibinst.sibinst_term_code_eff%TYPE,
                   p_rowid         VARCHAR2 DEFAULT NULL)

Deletes a record.

Parameters
p_pidm   Internal personal identification number of the faculty member.  NUMBER(8) Required key
p_term_code_eff   Effective term of the faculty member. VARCHAR2(6) Required key
p_rowid   Database ROWID of the record to be deleted.  VARCHAR2(18)


p_lock

Procedure p_lock(p_pidm          sibinst.sibinst_pidm%TYPE,
                 p_term_code_eff sibinst.sibinst_term_code_eff%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 p_rowid_inout.

Parameters
p_pidm   Internal personal identification number of the faculty member. NUMBER(8) Required key
p_term_code_eff   Effective term of the faculty member.  VARCHAR2(6) Required key
p_rowid_inout   Database ROWID of the record to locked. VARCHAR2(18)


p_update

Procedure p_update(p_pidm                 sibinst.sibinst_pidm%TYPE,
                   p_term_code_eff        sibinst.sibinst_term_code_eff%TYPE,
                   p_fcst_code            sibinst.sibinst_fcst_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_fctg_code            sibinst.sibinst_fctg_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_fstp_code            sibinst.sibinst_fstp_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_fcnt_code            sibinst.sibinst_fcnt_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_schd_ind             sibinst.sibinst_schd_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_advr_ind             sibinst.sibinst_advr_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_fcst_date            sibinst.sibinst_fcst_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_wkld_code            sibinst.sibinst_wkld_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_cntr_code            sibinst.sibinst_cntr_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_appoint_date         sibinst.sibinst_appoint_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_data_origin          sibinst.sibinst_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id              sibinst.sibinst_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_override_process_ind sibinst.sibinst_override_process_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_override_proc_userid sibinst.sibinst_override_proc_userid%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_override_proc_date   sibinst.sibinst_override_proc_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_rowid                VARCHAR2 DEFAULT NULL)

Updates a record.

Parameters
p_pidm   Internal personal identification number of the faculty member. NUMBER(8) Required key
p_term_code_eff   Effective term of the faculty member. VARCHAR2(6) Required key
p_fcst_code   Faculty member status. VARCHAR2(2)
p_fctg_code   Faculty member category.  VARCHAR2(6)
p_fstp_code   Faculty member staff type which is required for workload calculation.  VARCHAR2(4)
p_fcnt_code   Faculty member contract rule which is required for workload calculation.  VARCHAR2(2)
p_schd_ind   Faculty member available for schedule indicator.  VARCHAR2(1)
p_advr_ind   Faculty member available for advising indicator.  VARCHAR2(1)
p_fcst_date   Faculty member status date. DATE
p_wkld_code   Faculty member workload rule code. VARCHAR2(6)
p_cntr_code   Faculty member contract rule code.  VARCHAR2(6)
p_appoint_date   Date the appointment was awarded.  DATE
p_data_origin   Source system that generated the data. VARCHAR2(30)
p_user_id   Oracle ID of the user who inserted or last update the data.  VARCHAR2(30)
p_override_process_ind   Indicator to allow bypass of all process security on SOAFACS  VARCHAR2(1) Required
p_override_proc_userid   User that updates the bypass indicator of all process security on SOAFACS  VARCHAR2(30)
p_override_proc_date   Date of update to the bypass indicator of all process security on SOAFACS  DATE
p_rowid   Database ROWID of the record to be updated. VARCHAR2(18)