Package sb_enrollment

NOTE: Please be advised that this API is currently intended to only support internal operations.
To ensure data integrity, this API is not supported when called by external applications or interfaces to manipulate data. The recommendation for external applications is to use message level integration to integrate with this entitity in Banner.

This package provides the Common Business interface for the Enrollment API (sb_enrollment).
One enrollment record for each student per term (on the SFBETRM table) is required to support the course registration process.

A number of conditions will prevent a student from enrolling and registering for classes. A student will not be permitted to enroll if one or more of the following conditions is true:

the person is not a student.
a hold that prevents registration is currently active.
the student's status does not allow registration.
the enrollment date is not between the start and end dates for the enrollment status rule.
the enrollment status prevents registration.
the combined academic standing prevents registration.
the combined academic standing override prevents registration.
End of Term Academic Standing prevents registration.
Academic Standing Override prevents registration.

Depending on rules specific to each institution, creating, updating or deleting enrollment records may impact tuition and fee assessment. Batch fee assessment will process and update any outstanding adjustments to tuition and fees that may be appropriate. Also, multiple rules may exist that would default the enrollment maximum hours credit hours for registration.
Manual updates to default values override a previous default value. If manually updated, any future changes needed also require manual adjustment with one exception. Calculation of academic standing includes an option to update enrollment maximum hours for a future term.

Enrollment records may not be deleted if course registration exists for the term.

NOTE: For the current release the Create, Update, and Delete API calls are not supported.

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_eligible_to_register	Checks if the given student is eligible to register for a given term.
f_eligible_to_register	Checks if a given enrollment status is open for registration.
f_enrollment_date_valid	Checks if a given enrollment status code is valid for enrollment date.
p_create	Creates a record.
p_delete	Deletes a record.
p_lock	Locks a record.
p_update	Updates a record.

Types

enrollment_rec	Entity record type
enrollment_ref	Entity reference cursor type
enrollment_tab	Entity table type

Constants

M_ENTITY_NAME

Business Entity Name

M_ENTITY_NAME

M_ENTITY_NAME    CONSTANT VARCHAR2(10) := 'ENROLLMENT';

Business Entity Name

enrollment_rec

TYPE enrollment_rec IS RECORD (
   r_term_code               sfbetrm.sfbetrm_term_code%TYPE,
   r_pidm                    sfbetrm.sfbetrm_pidm%TYPE,
   r_ests_code               sfbetrm.sfbetrm_ests_code%TYPE,
   r_ests_date               sfbetrm.sfbetrm_ests_date%TYPE,
   r_min_hrs                 sfbetrm.sfbetrm_min_hrs%TYPE,
   r_mhrs_over               sfbetrm.sfbetrm_mhrs_over%TYPE,
   r_minh_srce_cde           sfbetrm.sfbetrm_minh_srce_cde%TYPE,
   r_maxh_srce_cde           sfbetrm.sfbetrm_maxh_srce_cde%TYPE,
   r_ar_ind                  sfbetrm.sfbetrm_ar_ind%TYPE,
   r_assessment_date         sfbetrm.sfbetrm_assessment_date%TYPE,
   r_add_date                sfbetrm.sfbetrm_add_date%TYPE,
   r_rgre_code               sfbetrm.sfbetrm_rgre_code%TYPE,
   r_tmst_code               sfbetrm.sfbetrm_tmst_code%TYPE,
   r_tmst_date               sfbetrm.sfbetrm_tmst_date%TYPE,
   r_tmst_maint_ind          sfbetrm.sfbetrm_tmst_maint_ind%TYPE,
   r_user                    sfbetrm.sfbetrm_user%TYPE,
   r_refund_date             sfbetrm.sfbetrm_refund_date%TYPE,
   r_data_origin             sfbetrm.sfbetrm_data_origin%TYPE,
   r_internal_record_id      VARCHAR2(18));

Entity record type

enrollment_ref

TYPE enrollment_ref IS REF CURSOR RETURN enrollment_rec;

Entity reference cursor type

enrollment_tab

TYPE enrollment_tab IS TABLE OF enrollment_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      sfbetrm.sfbetrm_pidm%TYPE,
                  p_term_code sfbetrm.sfbetrm_term_code%TYPE,
                  p_rowid     VARCHAR2 DEFAULT NULL) RETURN VARCHAR2

Checks to see if a record exists.

Parameters

	p_term_code	Term associated with this registration. VARCHAR2(6) Required Key
	p_pidm	Internal Identification Number of the person registered . NUMBER(8) Required Key
	p_rowid	Database ROWID of the record to be selected. VARCHAR(18)

Returns

Y if found, otherwise N.

f_isequal

Function f_isequal(rec_one enrollment_rec, rec_two enrollment_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	First record to compare. Type enrollment_rec Required
	rec_two	Second record to compare. Type enrollment_rec Required

Returns

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

f_query_all

Function f_query_all(p_pidm      sfbetrm.sfbetrm_pidm%TYPE,
                     p_term_code sfbetrm.sfbetrm_term_code%TYPE DEFAULT NULL)
  RETURN enrollment_ref

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

Parameters

	p_pidm	Internal Identification Number of the person registered. NUMBER(8) Required Key
	p_term_code	Term associated with the registration. VARCHAR2(6) Default NULL

Returns

A cursor variable that will fetch the set of records.

f_query_one

Function f_query_one(p_pidm      sfbetrm.sfbetrm_pidm%TYPE,
                     p_term_code sfbetrm.sfbetrm_term_code%TYPE)
  RETURN enrollment_ref

Selects one record using the key.

Parameters

	p_term_code	Term associated with the registration. VARCHAR2(6) Required Key
	p_pidm	Internal Identification Number of the person registered . NUMBER(8) 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 enrollment_ref

Selects one record using the ROWID.

Parameters

p_rowid

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

Returns

A cursor variable that will fetch exactly one record.

f_query_one_lock

Function f_query_one_lock(p_pidm      sfbetrm.sfbetrm_pidm%TYPE,
                          p_term_code sfbetrm.sfbetrm_term_code%TYPE,
                          p_rowid     VARCHAR2 DEFAULT NULL)
  RETURN enrollment_ref

Selects one record and locks it.

Parameters

	p_term_code	Term associated with this registration. VARCHAR2(6) Required Key
	p_pidm	Internal Identification Number of the person registered . NUMBER(8) Required Key
	p_rowid	Database ROWID of the record to be selected. VARCHAR(18)

Returns

A cursor variable for one record, locking the record.

f_eligible_to_register

Function f_eligible_to_register(p_pidm      sfbetrm.sfbetrm_pidm%TYPE,
                                p_term_code sfbetrm.sfbetrm_term_code%TYPE)
  RETURN VARCHAR2

Checks if the given student is eligible to register for a given term.

Parameters

	p_term_code	Term associated with this registration. VARCHAR2(6) Required Key
	p_pidm	Internal Identification Number of the person registered . NUMBER(8) Required Key

Returns

	'Y' if eligible to register
	'N' if not eligible to register

f_eligible_to_register

Function f_eligible_to_register(p_ests_code sfbetrm.sfbetrm_ests_code%TYPE)
  RETURN VARCHAR2

Checks if a given enrollment status is open for registration.
A 'Y' is returned if enrollment status is open else an 'N' if not open.

Parameters

p_ests_code

Student enrollment status code. VARCHAR2(2) Required

Returns

	'Y' if enrollment status does not prevent registration.
	'N' if enrollment status prevents registration.

f_enrollment_date_valid

Function f_enrollment_date_valid(p_term_code sfbetrm.sfbetrm_term_code%TYPE,
                                 p_ests_code sfbetrm.sfbetrm_ests_code%TYPE,
                                 p_ests_date sfbetrm.sfbetrm_ests_date%TYPE)
  RETURN VARCHAR2

Checks if a given enrollment status code is valid for enrollment date.
A 'Y' is returned if enrollment status is valid for date, otherwise 'N' is returned.

Parameters

	p_term_code	Term associated with the enrollment. VARCHAR2(6) Required Key
	p_ests_code	Student enrollment status code. VARCHAR2(2) Required
	p_ests_date	Date associated with the enrollment. VARCHAR2(2) Required

Returns

	'Y' if enrollment status is valid for enrollment date.
	'N' if enrollment status is not valid for enrollment date.

p_create

Procedure p_create(p_term_code       sfbetrm.sfbetrm_term_code%TYPE,
                   p_pidm            sfbetrm.sfbetrm_pidm%TYPE,
                   p_ests_code       sfbetrm.sfbetrm_ests_code%TYPE,
                   p_ests_date       sfbetrm.sfbetrm_ests_date%TYPE DEFAULT NULL,
                   p_min_hrs         sfbetrm.sfbetrm_min_hrs%TYPE,
                   p_mhrs_over       sfbetrm.sfbetrm_mhrs_over%TYPE,
                   p_minh_srce_cde   sfbetrm.sfbetrm_minh_srce_cde%TYPE DEFAULT NULL,
                   p_maxh_srce_cde   sfbetrm.sfbetrm_maxh_srce_cde%TYPE DEFAULT NULL,
                   p_ar_ind          sfbetrm.sfbetrm_ar_ind%TYPE,
                   p_assessment_date sfbetrm.sfbetrm_assessment_date%TYPE DEFAULT NULL,
                   p_add_date        sfbetrm.sfbetrm_add_date%TYPE,
                   p_rgre_code       sfbetrm.sfbetrm_rgre_code%TYPE DEFAULT NULL,
                   p_tmst_code       sfbetrm.sfbetrm_tmst_code%TYPE DEFAULT NULL,
                   p_tmst_date       sfbetrm.sfbetrm_tmst_date%TYPE DEFAULT NULL,
                   p_tmst_maint_ind  sfbetrm.sfbetrm_tmst_maint_ind%TYPE DEFAULT NULL,
                   p_user            sfbetrm.sfbetrm_user%TYPE DEFAULT NULL,
                   p_refund_date     sfbetrm.sfbetrm_refund_date%TYPE DEFAULT NULL,
                   p_data_origin     sfbetrm.sfbetrm_data_origin%TYPE DEFAULT NULL,
                   p_rowid_out       OUT VARCHAR2)

Creates a record.

Parameters

	p_term_code	Term associated with this registration. VARCHAR2(6) Required Key
	p_pidm	Internal Identification Number of the person registered . NUMBER(8) Required Key
	p_ests_code	Student enrollment status code. VARCHAR2(2) Required
	p_ests_date	Date associated with the student enrollment status code. DATE
	p_min_hrs	Minimum hours for this term. NUMBER(9,3) Required
	p_mhrs_over	Maximum hours override for this term. NUMBER(9,3) Required
	p_minh_srce_cde	Minimum hours source code. VARCHAR2(1) Required
	p_maxh_srce_cde	Maximum hours source code. VARCHAR2(1) Required
	p_ar_ind	Term charges acceptance indicator. VARCHAR2(1) Required
	p_assessment_date	Date of the assessment for this term. DATE
	p_add_date	Date of the initial registration for this term. DATE Required
	p_rgre_code	Student Registration Reason Code. VARCHAR2(2)
	p_tmst_code	Most recently calculated time status code. VARCHAR2(2)
	p_tmst_date	Date that the change in time status occurred. DATE
	p_tmst_maint_ind	Flag indicating whether the change in time status was calculated by the system or overridden and updated by a user. VARCHAR2(1)
	p_user	Oracle ID of the user who updated or created the record. VARCHAR2(30)
	p_refund_date	Refund-by-total refund date used in performing fee assessment. This date is needed for refund-by-total processing to determine the refund period used in the last assessment. DATE
	p_data_origin	Source system that generated the data. VARCHAR2(30)
	p_rowid_out	Database ROWID of the record to be created. VARCHAR(18) Required

p_delete

Procedure p_delete(p_pidm      sfbetrm.sfbetrm_pidm%TYPE,
                   p_term_code sfbetrm.sfbetrm_term_code%TYPE,
                   p_rowid     VARCHAR2 DEFAULT NULL)

Deletes a record.

Parameters

	p_term_code	Term associated with this registration. VARCHAR2(6) Required Key
	p_pidm	Internal Identification Number of the person registered . NUMBER(8) Required Key
	p_rowid	Database ROWID of the record to be deleted. VARCHAR(18)

p_lock

Procedure p_lock(p_pidm        sfbetrm.sfbetrm_pidm%TYPE,
                 p_term_code   sfbetrm.sfbetrm_term_code%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_term_code	Term associated with this registration. VARCHAR2(6) Required Key
	p_pidm	Internal Identification Number of the person registered . NUMBER(8) Required Key
	p_rowid_inout	Database ROWID of the record to be locked. VARCHAR(18) Required

p_update

Procedure p_update(p_term_code       sfbetrm.sfbetrm_term_code%TYPE,
                   p_pidm            sfbetrm.sfbetrm_pidm%TYPE,
                   p_ests_code       sfbetrm.sfbetrm_ests_code%TYPE DEFAULT dml_common.F_UNSPECIFIED_STRING,
                   p_ests_date       sfbetrm.sfbetrm_ests_date%TYPE DEFAULT dml_common.F_UNSPECIFIED_DATE,
                   p_min_hrs         sfbetrm.sfbetrm_min_hrs%TYPE DEFAULT dml_common.F_UNSPECIFIED_NUMBER,
                   p_mhrs_over       sfbetrm.sfbetrm_mhrs_over%TYPE DEFAULT dml_common.F_UNSPECIFIED_NUMBER,
                   p_minh_srce_cde   sfbetrm.sfbetrm_minh_srce_cde%TYPE DEFAULT dml_common.F_UNSPECIFIED_STRING,
                   p_maxh_srce_cde   sfbetrm.sfbetrm_maxh_srce_cde%TYPE DEFAULT dml_common.F_UNSPECIFIED_STRING,
                   p_ar_ind          sfbetrm.sfbetrm_ar_ind%TYPE DEFAULT dml_common.F_UNSPECIFIED_STRING,
                   p_assessment_date sfbetrm.sfbetrm_assessment_date%TYPE DEFAULT dml_common.F_UNSPECIFIED_DATE,
                   p_add_date        sfbetrm.sfbetrm_add_date%TYPE DEFAULT dml_common.F_UNSPECIFIED_DATE,
                   p_rgre_code       sfbetrm.sfbetrm_rgre_code%TYPE DEFAULT dml_common.F_UNSPECIFIED_STRING,
                   p_tmst_code       sfbetrm.sfbetrm_tmst_code%TYPE DEFAULT dml_common.F_UNSPECIFIED_STRING,
                   p_tmst_date       sfbetrm.sfbetrm_tmst_date%TYPE DEFAULT dml_common.F_UNSPECIFIED_DATE,
                   p_tmst_maint_ind  sfbetrm.sfbetrm_tmst_maint_ind%TYPE DEFAULT dml_common.F_UNSPECIFIED_STRING,
                   p_user            sfbetrm.sfbetrm_user%TYPE DEFAULT dml_common.F_UNSPECIFIED_STRING,
                   p_refund_date     sfbetrm.sfbetrm_refund_date%TYPE DEFAULT dml_common.F_UNSPECIFIED_DATE,
                   p_data_origin     sfbetrm.sfbetrm_data_origin%TYPE DEFAULT dml_common.F_UNSPECIFIED_STRING,
                   p_rowid           VARCHAR2 DEFAULT NULL)

Updates a record.

Parameters

	p_term_code	Term associated with this registration. VARCHAR2(6) Required Key
	p_pidm	Internal Identification Number of the person registered . NUMBER(8) Required Key
	p_ests_code	Student enrollment status code. VARCHAR2(2) Required
	p_ests_date	Date associated with the student enrollment status code. DATE
	p_min_hrs	Minimum hours for this term. NUMBER(9,3) Required
	p_mhrs_over	Maximum hours override for this term. NUMBER(9,3) Required
	p_minh_srce_cde	Minimum hours source code. VARCHAR2(1) Required
	p_maxh_srce_cde	Maximum hours source code. VARCHAR2(1) Required
	p_ar_ind	Term charges acceptance indicator. VARCHAR2(1) Required
	p_assessment_date	Date of the assessment for this term. DATE
	p_add_date	Date of the initial registration for this term. DATE Required
	p_rgre_code	Student Registration Reason Code. VARCHAR2(2)
	p_tmst_code	Most recently calculated time status code. VARCHAR2(2)
	p_tmst_date	Date that the change in time status occurred. DATE
	p_tmst_maint_ind	Flag indicating whether the change in time status was calculated by the system or overridden and updated by a user. VARCHAR2(1)
	p_user	Oracle ID of the user who updated or created the record. VARCHAR2(30)
	p_refund_date	Refund-by-total refund date used in performing fee assessment. This date is needed for refund-by-total processing to determine the refund period used in the last assessment. DATE
	p_data_origin	Source system that generated the data. VARCHAR2(30)
	p_rowid	Database ROWID of the record to be updated. VARCHAR(18)