Package sb_recruit

Common business interface for the Recruit API (sb_recruit).

A person may have many recruit records. However, each recruit record is uniquely defined by internal identification number, term code, and administrative sequence number.

There is a Selected indicator that defines which is the preferred recruit record for the term. Only one recruit record per term may exist with the Selected indicator set to 'Y'.

Starting with Banner 7.0, all curriculum and field of study data are maintained on the tables SORLCUR and SORLFOS (sb_curriculum API and sb_fieldofstudy API).
When a recruit record is created, a primary curriculum and primary major field of study must also be created. At the conclusion of the curriculum and field of study insert, the primary and secondary curriculum data are backfilled to the curriculum on the recruit record.

Program units

f_api_version

f_exists

f_isequal

f_query_all

f_query_one

f_query_by_rowid

f_query_one_lock

f_GetSrbrecrSeqno

p_create

p_delete

p_lock

p_update

Types

recruit_rec

recruit_ref

recruit_tab

Constants

M_ENTITY_NAME

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

Business Entity name

recruit_rec

TYPE recruit_rec IS RECORD (
   r_pidm                    srbrecr.srbrecr_pidm%TYPE,
   r_term_code               srbrecr.srbrecr_term_code%TYPE,
   r_admin_seqno             srbrecr.srbrecr_admin_seqno%TYPE,
   r_dept_code               srbrecr.srbrecr_dept_code%TYPE,
   r_levl_code               srbrecr.srbrecr_levl_code%TYPE,
   r_degc_code               srbrecr.srbrecr_degc_code%TYPE,
   r_majr_code               srbrecr.srbrecr_majr_code%TYPE,
   r_recr_code               srbrecr.srbrecr_recr_code%TYPE,
   r_rsta_code               srbrecr.srbrecr_rsta_code%TYPE,
   r_select_ind              srbrecr.srbrecr_select_ind%TYPE,
   r_add_date                srbrecr.srbrecr_add_date%TYPE,
   r_admt_code               srbrecr.srbrecr_admt_code%TYPE,
   r_camp_code               srbrecr.srbrecr_camp_code%TYPE,
   r_coll_code               srbrecr.srbrecr_coll_code%TYPE,
   r_majr_code2              srbrecr.srbrecr_majr_code2%TYPE,
   r_edlv_code               srbrecr.srbrecr_edlv_code%TYPE,
   r_egol_code               srbrecr.srbrecr_egol_code%TYPE,
   r_full_part_ind           srbrecr.srbrecr_full_part_ind%TYPE,
   r_sbgi_code               srbrecr.srbrecr_sbgi_code%TYPE,
   r_wrsn_code               srbrecr.srbrecr_wrsn_code%TYPE,
   r_rtyp_code               srbrecr.srbrecr_rtyp_code%TYPE,
   r_resd_code               srbrecr.srbrecr_resd_code%TYPE,
   r_sess_code               srbrecr.srbrecr_sess_code%TYPE,
   r_site_code               srbrecr.srbrecr_site_code%TYPE,
   r_styp_code               srbrecr.srbrecr_styp_code%TYPE,
   r_program_1               srbrecr.srbrecr_program_1%TYPE,
   r_term_code_ctlg_1        srbrecr.srbrecr_term_code_ctlg_1%TYPE,
   r_dept_code_2             srbrecr.srbrecr_dept_code_2%TYPE,
   r_majr_code_conc_111      srbrecr.srbrecr_majr_code_conc_111%TYPE,
   r_majr_code_conc_112      srbrecr.srbrecr_majr_code_conc_112%TYPE,
   r_majr_code_conc_113      srbrecr.srbrecr_majr_code_conc_113%TYPE,
   r_majr_code_conc_121      srbrecr.srbrecr_majr_code_conc_121%TYPE,
   r_majr_code_conc_122      srbrecr.srbrecr_majr_code_conc_122%TYPE,
   r_majr_code_conc_123      srbrecr.srbrecr_majr_code_conc_123%TYPE,
   r_majr_code_minr_1        srbrecr.srbrecr_majr_code_minr_1%TYPE,
   r_majr_code_minr_1_2      srbrecr.srbrecr_majr_code_minr_1_2%TYPE,
   r_curr_rule_1             srbrecr.srbrecr_curr_rule_1%TYPE,
   r_cmjr_rule_1_1           srbrecr.srbrecr_cmjr_rule_1_1%TYPE,
   r_ccon_rule_11_1          srbrecr.srbrecr_ccon_rule_11_1%TYPE,
   r_ccon_rule_11_2          srbrecr.srbrecr_ccon_rule_11_2%TYPE,
   r_ccon_rule_11_3          srbrecr.srbrecr_ccon_rule_11_3%TYPE,
   r_cmjr_rule_1_2           srbrecr.srbrecr_cmjr_rule_1_2%TYPE,
   r_ccon_rule_12_1          srbrecr.srbrecr_ccon_rule_12_1%TYPE,
   r_ccon_rule_12_2          srbrecr.srbrecr_ccon_rule_12_2%TYPE,
   r_ccon_rule_12_3          srbrecr.srbrecr_ccon_rule_12_3%TYPE,
   r_cmnr_rule_1_1           srbrecr.srbrecr_cmnr_rule_1_1%TYPE,
   r_cmnr_rule_1_2           srbrecr.srbrecr_cmnr_rule_1_2%TYPE,
   r_data_origin             srbrecr.srbrecr_data_origin%TYPE,
   r_user_id                 srbrecr.srbrecr_user_id%TYPE,
   r_internal_record_id      gb_common.internal_record_id_type);

Entity record type

recruit_ref

TYPE recruit_ref IS REF CURSOR RETURN recruit_rec;

Entity cursor variable type

recruit_tab

TYPE recruit_tab IS TABLE OF recruit_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                      srbrecr.srbrecr_pidm%TYPE,
      p_term_code                 srbrecr.srbrecr_term_code%TYPE,
      p_admin_seqno               srbrecr.srbrecr_admin_seqno%TYPE,
      p_rowid                     VARCHAR2 DEFAULT NULL) RETURN VARCHAR2

Checks to see if a record exists.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_term_code	Term associated with the recruiting record. VARCHAR2(6) Required Key
	p_admin_seqno	Internal sequence number for each term record. NUMBER(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                     recruit_rec,
      rec_two                     recruit_rec) RETURN VARCHAR2

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

Parameters

	rec_one	First record to compare. Type recruit_rec. Required
	rec_two	Second record to compare. Type recruit_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                      srbrecr.srbrecr_pidm%TYPE,
      p_term_code                 srbrecr.srbrecr_term_code%TYPE DEFAULT NULL,
      p_admin_seqno               srbrecr.srbrecr_admin_seqno%TYPE DEFAULT NULL) RETURN recruit_ref

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

Parameters

	p_pidm	Internal Identification Number of the person or non-person account, valid in SPRIDEN. NUMBER(8) Required Key
	p_term_code	Term associated with the transaction. For student transactions valid in STVTERM. VARCHAR2(6) Default NULL
	p_admin_seqno	Internal sequence number for each term record. NUMBER(2) Default NULL

Returns

A cursor variable that will fetch the set of records.

f_query_one

Function f_query_one(
      p_pidm                      srbrecr.srbrecr_pidm%TYPE,
      p_term_code                 srbrecr.srbrecr_term_code%TYPE,
      p_admin_seqno               srbrecr.srbrecr_admin_seqno%TYPE) RETURN recruit_ref

Selects one record using the key.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_term_code	Term associated with the recruiting record. VARCHAR2(6) Required Key
	p_admin_seqno	Internal sequence number for each term record. NUMBER(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 recruit_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                      srbrecr.srbrecr_pidm%TYPE,
      p_term_code                 srbrecr.srbrecr_term_code%TYPE,
      p_admin_seqno               srbrecr.srbrecr_admin_seqno%TYPE,
      p_rowid                     VARCHAR2 DEFAULT NULL) RETURN recruit_ref

Selects one record and locks it.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_term_code	Term associated with the recruiting record. VARCHAR2(6) Required Key
	p_admin_seqno	Internal sequence number for each term record. NUMBER(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.

f_GetSrbrecrSeqno

Function f_GetSrbrecrSeqno(
     p_pidm         IN NUMBER,
     p_term_code    IN VARCHAR2,
     p_curr_or_next     IN VARCHAR2) RETURN number

Gets the Next or Current administrative sequence number for the SRBRECR row.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_term_code	Term associated with the recruiting record. VARCHAR2(6) Required Key
	p_curr_or_next	Passes a C for the current srbrecr_admin_seqno or N to retrieve the sequence number for the next (new) row. VARCHAR2(1) Required

Returns

A number of the current or next SRBRECR administrative sequence number for the term.

p_create

Procedure p_create(
      p_pidm                      srbrecr.srbrecr_pidm%TYPE,
      p_term_code                 srbrecr.srbrecr_term_code%TYPE,
      p_admin_seqno_inout IN OUT  srbrecr.srbrecr_admin_seqno%TYPE,
      p_recr_code                 srbrecr.srbrecr_recr_code%TYPE DEFAULT NULL,
      p_rsta_code                 srbrecr.srbrecr_rsta_code%TYPE DEFAULT NULL,
      p_select_ind                srbrecr.srbrecr_select_ind%TYPE DEFAULT NULL,
      p_add_date                  srbrecr.srbrecr_add_date%TYPE,
      p_admt_code                 srbrecr.srbrecr_admt_code%TYPE DEFAULT NULL,
      p_edlv_code                 srbrecr.srbrecr_edlv_code%TYPE DEFAULT NULL,
      p_egol_code                 srbrecr.srbrecr_egol_code%TYPE DEFAULT NULL,
      p_full_part_ind             srbrecr.srbrecr_full_part_ind%TYPE DEFAULT NULL,
      p_sbgi_code                 srbrecr.srbrecr_sbgi_code%TYPE DEFAULT NULL,
      p_wrsn_code                 srbrecr.srbrecr_wrsn_code%TYPE DEFAULT NULL,
      p_rtyp_code                 srbrecr.srbrecr_rtyp_code%TYPE DEFAULT NULL,
      p_resd_code                 srbrecr.srbrecr_resd_code%TYPE DEFAULT NULL,
      p_sess_code                 srbrecr.srbrecr_sess_code%TYPE DEFAULT NULL,
      p_site_code                 srbrecr.srbrecr_site_code%TYPE DEFAULT NULL,
      p_styp_code                 srbrecr.srbrecr_styp_code%TYPE DEFAULT NULL,
      p_data_origin               srbrecr.srbrecr_data_origin%TYPE DEFAULT NULL,
      p_user_id                   srbrecr.srbrecr_user_id%TYPE DEFAULT gb_common.f_sct_user,
      p_rowid_out             OUT VARCHAR2)

Creates a record.
Recruit processing creates unique recruit rows for a person by assigning an administrative sequence number to each row within term code for the recruit internal identification number.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_term_code	Term associated with the recruiting record. VARCHAR2(6) Required Key
	p_admin_seqno_inout	Internal sequence number to each term record. NUMBER(2) Required Key
	p_recr_code	Recruiter assigned to the prospect recruiting record. VARCHAR2(3)
	p_rsta_code	Status of the prospect associated with the recruiting record. VARCHAR2(2)
	p_select_ind	Selected indicator, which is used to specify which record is the current or most recent recruiting record. Informational only. Values are Y or null. Only one record per term may be selected. VARCHAR2(1)
	p_add_date	Date the record was added. DATE Required
	p_admt_code	Admission Type code. VARCHAR2(2)
	p_edlv_code	Education Level code. VARCHAR2(3)
	p_egol_code	Education Goal code. VARCHAR2(2)
	p_full_part_ind	Fulltime or Parttime Indicator. If not null, values are F or P. VARCHAR2(1)
	p_sbgi_code	Institution Attending code of the institution the student decided to attend instead of the home institution. VARCHAR2(6)
	p_wrsn_code	Withdrawal Reason code. Used when a student decides not to attend the home institution. VARCHAR2(2)
	p_rtyp_code	Recruit Type code. VARCHAR2(2)
	p_resd_code	Residency code. VARCHAR2(1)
	p_sess_code	Session code. VARCHAR2(1)
	p_site_code	Site code. VARCHAR2(3)
	p_styp_code	Student Type code. VARCHAR2(1)
	p_data_origin	Source system that generated the data. VARCHAR2(30)
	p_user_id	Most recent user to create or update a record. VARCHAR2(30)
	p_rowid_out	Database ROWID of the record to be created. VARCHAR2(18) Required

p_delete

Procedure p_delete(
      p_pidm                      srbrecr.srbrecr_pidm%TYPE,
      p_term_code                 srbrecr.srbrecr_term_code%TYPE,
      p_admin_seqno               srbrecr.srbrecr_admin_seqno%TYPE,
      p_rowid                     VARCHAR2 DEFAULT NULL)

Deletes a record.
Either the ROWID or all primary key fields are required.

Before deleting the recruit record, the process will check for child records of SRBRECR and other recruit-dependent rows. It will delete rows from the cohort (SRRCHRT), "How I Learned of the Institution" (SRRLEND), source (SRRRSRC), and attribute (SRRRATT) tables.
It will also delete curriculum (SORLCUR) and field of study (SORLFOS) rows associated with the recruit record.
If there are no more recruit rows, the API will also delete all comment rows (SRRRCMT).

The process acquires a lock first, registers the values for messaging if needed, and calls the DML layer to perform the delete.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_term_code	Term associated with the recruiting record. VARCHAR2(6) Required Key
	p_admin_seqno	Internal sequence number for each term record. NUMBER(2) Required Key
	p_rowid	Database ROWID of the record to be deleted. VARCHAR2(18)

p_lock

Procedure p_lock(
      p_pidm                    srbrecr.srbrecr_pidm%TYPE,
      p_term_code               srbrecr.srbrecr_term_code%TYPE,
      p_admin_seqno             srbrecr.srbrecr_admin_seqno%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 identification number. NUMBER(8) Required Key
	p_term_code	Term associated with the recruiting record. VARCHAR2(6) Required Key
	p_admin_seqno	Internal sequence number for each term record. NUMBER(2) Required Key
	p_rowid_inout	Database ROWID of the record to be locked. VARCHAR2(18) Required

p_update

Procedure p_update(
      p_pidm                      srbrecr.srbrecr_pidm%TYPE,
      p_term_code                 srbrecr.srbrecr_term_code%TYPE,
      p_admin_seqno               srbrecr.srbrecr_admin_seqno%TYPE,
      p_recr_code                 srbrecr.srbrecr_recr_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_rsta_code                 srbrecr.srbrecr_rsta_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_select_ind                srbrecr.srbrecr_select_ind%TYPE DEFAULT dml_common.f_unspecified_string,
      p_add_date                  srbrecr.srbrecr_add_date%TYPE DEFAULT dml_common.f_unspecified_date,
      p_admt_code                 srbrecr.srbrecr_admt_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_edlv_code                 srbrecr.srbrecr_edlv_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_egol_code                 srbrecr.srbrecr_egol_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_full_part_ind             srbrecr.srbrecr_full_part_ind%TYPE DEFAULT dml_common.f_unspecified_string,
      p_sbgi_code                 srbrecr.srbrecr_sbgi_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_wrsn_code                 srbrecr.srbrecr_wrsn_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_rtyp_code                 srbrecr.srbrecr_rtyp_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_resd_code                 srbrecr.srbrecr_resd_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_sess_code                 srbrecr.srbrecr_sess_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_site_code                 srbrecr.srbrecr_site_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_styp_code                 srbrecr.srbrecr_styp_code%TYPE DEFAULT dml_common.f_unspecified_string,
      p_data_origin               srbrecr.srbrecr_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
      p_user_id                   srbrecr.srbrecr_user_id%TYPE DEFAULT gb_common.f_sct_user,
      p_rowid                      VARCHAR2 DEFAULT NULL)

Updates a record.
To update a recruit record, the API must be able to locate the recruit record by internal identification number, term code and administrative sequence number. This uniquely defines the recruit record.

The update process acquires a lock on the row, performs validation, and calls the DML procedure to perform the dynamic update.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_term_code	Term associated with the recruiting record. VARCHAR2(6) Required Key
	p_admin_seqno	Internal sequence number to each term record. NUMBER(2) Required Key
	p_recr_code	Recruiter assigned to the prospect recruiting record. VARCHAR2(3)
	p_rsta_code	Status of the prospect associated with the recruiting record. VARCHAR2(2)
	p_select_ind	Selected indicator , which is used to specify which record is the current or most recent recruiting record. Informational only. Values are Y or null. Only one record per term may be selected. VARCHAR2(1)
	p_add_date	Date the record was added. DATE Required
	p_admt_code	Admission Type code. VARCHAR2(2)
	p_edlv_code	Education Level code. VARCHAR2(3)
	p_egol_code	Education Goal code. VARCHAR2(2)
	p_full_part_ind	Full-time or Part-time Indicator. If not null, values are F or P. VARCHAR2(1)
	p_sbgi_code	Institution Attending code of the institution the student decided to attend instead of the home institution. VARCHAR2(6)
	p_wrsn_code	Withdrawal Reason code. Used when a student decides not to attend the home institution. VARCHAR2(2)
	p_rtyp_code	Recruit Type code. VARCHAR2(2)
	p_resd_code	Residency code. VARCHAR2(1)
	p_sess_code	Session code. VARCHAR2(1)
	p_site_code	Site code. VARCHAR2(3)
	p_styp_code	Student Type code. VARCHAR2(1)
	p_data_origin	Source system that generated the data. VARCHAR2(30)
	p_user_id	Most recent user to create or update a record. VARCHAR2(30)
	p_rowid	Database ROWID of the record to be updated. VARCHAR2(18)