index

Package gb_hold

This package provides the Common Business interface for the Hold API (gb_hold).
Holds are assigned to a person record, and a person record may have one or more holds.  Holds may prevent processing depending on which process indicators have been checked ("Y") for the hold validation codes (on the STVHLDD table). Holds can be placed on:
  • Registration
  • Enrollment verification
  • Transcript
  • Graduation
  • Grades
  • Accounts receivable
  • Admissions applications
  • Degree audit (CAPP) compliance
 
A user may choose to restrict the hold placed on a person record so only he or she can change or remove the hold (release indicator is checked - "Y").  Any user may change or remove a hold if the release indicator is unchecked ("N").
 
A hold record must include the hold code, the hold start and end dates, the Oracle ID of the user who created/updated the hold and the Release Indicator ("Y" or "N").
A hold record can include an origination code, free-format comment about the hold, and a monetary amount associated with the hold.

Program units
f_api_version   Returns the API version number.
f_exists   Checks to see if a record exists.
f_hold_exists   Checks to see if any hold record exists.
f_any_hold_exists   Checks to see if any hold record exists for a pidm.
f_isequal   Compares two hold 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.
p_create   Creates a record.
p_delete   Deletes a record.
p_lock   Locks a record.
p_update   Updates a record.

Types
hold_rec   Entity record type
hold_ref   Entity reference cursor type
hold_tab   Entity table type

Constants
M_ENTITY_NAME   Business Entity name

M_ENTITY_NAME 

M_ENTITY_NAME    CONSTANT VARCHAR2(4) := 'HOLD';

Business Entity name

hold_rec 

TYPE hold_rec IS RECORD (
   r_pidm                    sprhold.sprhold_pidm%TYPE,
   r_hldd_code               sprhold.sprhold_hldd_code%TYPE,
   r_user                    sprhold.sprhold_user%TYPE,
   r_from_date               sprhold.sprhold_from_date%TYPE,
   r_to_date                 sprhold.sprhold_to_date%TYPE,
   r_release_ind             sprhold.sprhold_release_ind%TYPE,
   r_reason                  sprhold.sprhold_reason%TYPE,
   r_amount_owed             sprhold.sprhold_amount_owed%TYPE,
   r_orig_code               sprhold.sprhold_orig_code%TYPE,
   r_data_origin             sprhold.sprhold_data_origin%TYPE,
   r_internal_record_id      gb_common.internal_record_id_type);

Entity record type

hold_ref 

TYPE hold_ref IS REF CURSOR RETURN hold_rec;

Entity reference cursor type

hold_tab 

TYPE hold_tab IS TABLE OF hold_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_rowid VARCHAR2) RETURN VARCHAR2

Checks to see if a record exists.

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

Returns
Y if found, otherwise N.

f_hold_exists 

FUNCTION f_hold_exists(p_pidm       sprhold.sprhold_pidm%TYPE,
                       p_reg_hold   stvhldd.stvhldd_reg_hold_ind%TYPE DEFAULT NULL,
                       p_trans_hold stvhldd.stvhldd_trans_hold_ind%TYPE DEFAULT NULL,
                       p_grad_hold  stvhldd.stvhldd_grad_hold_ind%TYPE DEFAULT NULL,
                       p_grade_hold stvhldd.stvhldd_grade_hold_ind%TYPE DEFAULT NULL,
                       p_ar_hold    stvhldd.stvhldd_ar_hold_ind%TYPE DEFAULT NULL,
                       p_env_hold   stvhldd.stvhldd_env_hold_ind%TYPE DEFAULT NULL,
                       p_app_hold   stvhldd.stvhldd_application_hold_ind%TYPE DEFAULT NULL,
                       p_compl_hold stvhldd.stvhldd_compliance_hold_ind%TYPE DEFAULT NULL,
                       p_date       DATE DEFAULT SYSDATE) RETURN VARCHAR2

Checks to see if any hold record exists.

Parameters
p_pidm   Internal identification number of the person record. NUMBER(8) Required

Returns
Y if found, otherwise N.

f_any_hold_exists 

FUNCTION f_any_hold_exists(p_pidm sprhold.sprhold_pidm%TYPE,
                           p_date DATE DEFAULT SYSDATE) RETURN VARCHAR2

Checks to see if any hold record exists for a pidm.

Parameters
p_pidm   Internal identification number of the person record. NUMBER(8) Required

Returns
Y if found, otherwise N.

f_isequal 

FUNCTION f_isequal(rec_one hold_rec,
                   rec_two hold_rec) RETURN VARCHAR2

Compares two hold 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 hold_rec. Required
rec_two   Second record to compare. Type hold_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        sprhold.sprhold_pidm%TYPE,
                     p_hldd_code   sprhold.sprhold_hldd_code%TYPE DEFAULT NULL,
                     p_user        sprhold.sprhold_user%TYPE DEFAULT NULL,
                     p_from_date   sprhold.sprhold_from_date%TYPE DEFAULT NULL,
                     p_to_date     sprhold.sprhold_to_date%TYPE DEFAULT NULL,
                     p_release_ind sprhold.sprhold_release_ind%TYPE DEFAULT NULL,
                     p_reason      sprhold.sprhold_reason%TYPE DEFAULT NULL,
                     p_amount_owed sprhold.sprhold_amount_owed%TYPE DEFAULT NULL,
                     p_orig_code   sprhold.sprhold_orig_code%TYPE DEFAULT NULL,
                     p_data_origin sprhold.sprhold_data_origin%TYPE DEFAULT NULL)
  RETURN hold_ref

Selects all records for the entity.
The hold entity (SPRHOLD) does not have a primary or unique key. It is safer to use this signature (f_query_all) compared to the f_query_one, since f_query_one could return multiple records for the given parameters.

Parameters
p_pidm   Internal identification number of the person record. NUMBER(8) Required
p_hldd_code   Type of hold on the person record. VARCHAR2(2) Required
p_user   Oracle ID of the user initiating the hold. VARCHAR2(30) Required
p_from_date   Effective begin date of the hold. DATE Required
p_to_date   Date the hold expires. DATE Required
p_release_ind   Y - allow only the system user to release the hold. VARCHAR2(1) Required
p_reason   Free-format field which identifies the reason the hold was placed. VARCHAR2(30)
p_amount_owed   Monetary amount associated with the hold. NUMBER(7,2)
p_orig_code   Person or office who authorized the hold status. VARCHAR2(4)
p_data_origin   Source system that generated the data. VARCHAR2(30)

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

f_query_one 

FUNCTION f_query_one(p_pidm        sprhold.sprhold_pidm%TYPE,
                     p_hldd_code   sprhold.sprhold_hldd_code%TYPE,
                     p_user        sprhold.sprhold_user%TYPE,
                     p_from_date   sprhold.sprhold_from_date%TYPE,
                     p_to_date     sprhold.sprhold_to_date%TYPE,
                     p_release_ind sprhold.sprhold_release_ind%TYPE,
                     p_reason      sprhold.sprhold_reason%TYPE,
                     p_amount_owed sprhold.sprhold_amount_owed%TYPE,
                     p_orig_code   sprhold.sprhold_orig_code%TYPE,
                     p_data_origin sprhold.sprhold_data_origin%TYPE)
  RETURN hold_ref

Selects one record using the key.
The hold entity (SPRHOLD) does not have a primary or unique key. Since the table could host multiple records with exact same signature, the f_query_one may not always return a unique record.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Required
p_hldd_code   Type of hold on the person record. VARCHAR2(2) Required
p_user   Oracle ID of the user initiating the hold. VARCHAR2(30) Required
p_from_date   Effective begin date of the hold. DATE Required
p_to_date   Date the hold expires. DATE Required
p_release_ind   Y - allow only the system user to release the hold. VARCHAR2(1) Required
p_reason   Free-format field which identifies the reason the hold was placed. VARCHAR2(30)
p_amount_owed   Monetary amount associated with the hold. NUMBER(7,2)
p_orig_code   Person or office who authorized the hold status. VARCHAR2(4)
p_data_origin   Source system that generated the data. VARCHAR2(30)

Returns
A cursor variable that will fetch exactly one record.

f_query_by_rowid 

FUNCTION f_query_by_rowid(p_rowid VARCHAR2) RETURN hold_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_rowid VARCHAR2) RETURN hold_ref

Selects one record and locks it.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Required
p_hldd_code   Type of hold on the person record. VARCHAR2(2) Required
p_user   Oracle ID of the person initiating the hold. VARCHAR2(30) Required
p_from_date   Effective begin date of the hold. DATE Required
p_to_date   Date the hold expires. DATE Required
p_release_ind   Y - allow only the system user to release the hold. VARCHAR2(1) Required
p_reason   Free-format field which identifies the reason the hold was placed. VARCHAR2(30)
p_amount_owed   Monetary amount associated with the hold. NUMBER(7,2)
p_orig_code   Person or office who authorized the hold status. VARCHAR2(4)
p_data_origin   Source system that generated the data. VARCHAR2(30)
p_rowid   Database ROWID of the record to be selected. VARCHAR(18)

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

p_create 

PROCEDURE p_create(p_pidm        sprhold.sprhold_pidm%TYPE,
                   p_hldd_code   sprhold.sprhold_hldd_code%TYPE,
                   p_user        sprhold.sprhold_user%TYPE,
                   p_from_date   sprhold.sprhold_from_date%TYPE,
                   p_to_date     sprhold.sprhold_to_date%TYPE,
                   p_release_ind sprhold.sprhold_release_ind%TYPE,
                   p_reason      sprhold.sprhold_reason%TYPE DEFAULT NULL,
                   p_amount_owed sprhold.sprhold_amount_owed%TYPE DEFAULT NULL,
                   p_orig_code   sprhold.sprhold_orig_code%TYPE DEFAULT NULL,
                   p_data_origin sprhold.sprhold_data_origin%TYPE DEFAULT NULL,
                   p_rowid_out   OUT VARCHAR2)

Creates a record.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Required
p_hldd_code   Type of hold on the person record. VARCHAR2(2) Required
p_user   Oracle ID of the user initiating the hold. VARCHAR2(30) Required
p_from_date   Effective begin date of the hold. DATE Required
p_to_date   Date the hold expires. DATE Required
p_release_ind   Y - allow only the system user to release the hold. VARCHAR2(1) Required
p_reason   Free-format field which identifies the reason the hold was placed. VARCHAR2(30)
p_amount_owed   Monetary amount associated with the hold. NUMBER(7,2)
p_orig_code   Person or office who authorized the hold status. VARCHAR2(4)
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_user  sprhold.sprhold_user%TYPE DEFAULT gb_common.f_sct_user,
                   p_rowid VARCHAR2)

Deletes a record.

Parameters
p_user   Oracle ID of the user initiating the hold. VARCHAR2(30) Required
p_rowid   Database ROWID of the record to be deleted. VARCHAR(18)

p_lock 

PROCEDURE p_lock(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_rowid_inout   Database ROWID of the record to be locked. VARCHAR(18) Required

p_update 

PROCEDURE p_update(p_pidm        sprhold.sprhold_pidm%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_hldd_code   sprhold.sprhold_hldd_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user        sprhold.sprhold_user%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_from_date   sprhold.sprhold_from_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_to_date     sprhold.sprhold_to_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_release_ind sprhold.sprhold_release_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_reason      sprhold.sprhold_reason%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_amount_owed sprhold.sprhold_amount_owed%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_orig_code   sprhold.sprhold_orig_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin sprhold.sprhold_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid       VARCHAR2)

Updates a record.
The hold entity (SPRHOLD) does not have a primary or unique key, and to update a record the p_rowid is required.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8)
p_hldd_code   Type of hold on the person record. VARCHAR2(2)
p_user   Oracle ID of the person initiating the hold. VARCHAR2(30)
p_from_date   Effective begin date of the hold. DATE
p_to_date   Date the hold expires. DATE
p_release_ind   Y - allow only the system user to release the hold. VARCHAR2(1)
p_reason   Free-format field which identifies the reason hold was placed. VARCHAR2(30)
p_amount_owed   Monetary amount associated with the hold. NUMBER(7,2)
p_orig_code   Person or office who authorized the hold status. VARCHAR2(4)
p_data_origin   Source system that generated the data. VARCHAR2(30)
p_rowid   Database ROWID of the record to be updated. VARCHAR(18) Required