BANINST1@S10B80

index

Package sb_wl_notification

* This package provides the Common Business interface for the Waitlist Notification API(sb_wl_notification).
 
This has the students that have been notified that a new seats became available for the section.
The key to the waitlist notification table is the term code, the Course Reference number and the student?s personal identification number.

Program units
f_api_version   Returns the API version number.
f_exists   Checks 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 key.
f_query_by_rowid   Selects one record using 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
wl_notification_rec   Business Entity record type
wl_notification_ref   Entity cursor variable type
wl_notification_tab   Entity table type

Constants
M_ENTITY_NAME   Business Entity Name
M_BASE_TABLE_NAME   Base table name

M_ENTITY_NAME 

M_ENTITY_NAME        CONSTANT VARCHAR2(15) := 'WL_NOTIFICATION';

Business Entity Name

M_BASE_TABLE_NAME 

M_BASE_TABLE_NAME    CONSTANT VARCHAR2(7) := 'SFRWLNT';

Base table name

wl_notification_rec 

TYPE wl_notification_rec IS RECORD (
  r_term_code               sfrwlnt.sfrwlnt_term_code%TYPE,
  r_crn                     sfrwlnt.sfrwlnt_crn%TYPE,
  r_pidm                    sfrwlnt.sfrwlnt_pidm%TYPE,
  r_user_id                 sfrwlnt.sfrwlnt_user_id%TYPE,
  r_start_date              sfrwlnt.sfrwlnt_start_date%TYPE,
  r_end_date                sfrwlnt.sfrwlnt_end_date%TYPE,
  r_wl_priority             sfrwlnt.sfrwlnt_wl_priority%TYPE,
  r_reg_conf_stat           sfrwlnt.sfrwlnt_reg_conf_stat%TYPE,
  r_data_origin             sfrwlnt.sfrwlnt_data_origin%TYPE,
  r_error_status            sfrwlnt.sfrwlnt_error_status%TYPE,
  r_error_count             sfrwlnt.sfrwlnt_error_count%TYPE,
  r_last_error_message      sfrwlnt.sfrwlnt_last_error_message%TYPE,
  r_last_error_date         sfrwlnt.sfrwlnt_last_error_date%TYPE,
  r_internal_record_id      gb_common.internal_record_id_type);

Business Entity record type

wl_notification_ref 

TYPE wl_notification_ref IS REF CURSOR RETURN wl_notification_rec;

Entity cursor variable type

wl_notification_tab 

TYPE wl_notification_tab IS TABLE OF wl_notification_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 sfrwlnt.sfrwlnt_term_code%TYPE,
                  p_crn       sfrwlnt.sfrwlnt_crn%TYPE,
                  p_pidm      sfrwlnt.sfrwlnt_pidm%TYPE,
                  p_rowid     gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2

Checks if a record exists.

Parameters
p_term_code   Term code of the section that has an available seat. VARCHAR2(6) Required Key
p_crn   Course reference number of the section that has an available seat. VARCHAR2(5) Required Key
p_pidm   Unique identifier(PIDM) of the student that has been notified of available seats. NUMBER(8) 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 wl_notification_rec,
                   rec_two wl_notification_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   The first record to compare. Type wl_notification_rec Required
rec_two   The second record to compare. Type wl_notification_rec Required

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

f_query_all 

Function f_query_all(p_term_code sfrwlnt.sfrwlnt_term_code%TYPE,
                     p_crn       sfrwlnt.sfrwlnt_crn%TYPE,
                     p_pidm      sfrwlnt.sfrwlnt_pidm%TYPE)
  RETURN wl_notification_ref

Selects all records for the entity.

Parameters
p_term_code   Term code of the section that has an available seat. VARCHAR2(6) Required Key
p_crn   Course reference number of the section that has an available seat. VARCHAR2(5) Required Key
p_pidm   Unique identifier(PIDM) of the student that has been notified of available seats. NUMBER(8) Required Key

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

f_query_one 

Function f_query_one(p_term_code sfrwlnt.sfrwlnt_term_code%TYPE,
                     p_crn       sfrwlnt.sfrwlnt_crn%TYPE,
                     p_pidm      sfrwlnt.sfrwlnt_pidm%TYPE)
  RETURN wl_notification_ref

Selects one record using key.

Parameters
p_term_code   Term code of the section that has an available seat. VARCHAR2(6) Required Key
p_crn   Course reference number of the section that has an available seat. VARCHAR2(5) Required Key
p_pidm   Unique identifier(PIDM) of the student that has been notified of available seats. 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 gb_common.internal_record_id_type)
  RETURN wl_notification_ref

Selects one record using 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_term_code sfrwlnt.sfrwlnt_term_code%TYPE,
                          p_crn       sfrwlnt.sfrwlnt_crn%TYPE,
                          p_pidm      sfrwlnt.sfrwlnt_pidm%TYPE,
                          p_rowid     gb_common.internal_record_id_type DEFAULT NULL)
  RETURN wl_notification_ref

Selects one record and locks it.

Parameters
p_term_code   Term code of the section that has an available seat. VARCHAR2(6) Required Key
p_crn   Course reference number of the section that has an available seat. VARCHAR2(5) Required Key
p_pidm   Unique identifier(PIDM) of the student that has been notified of available seats. NUMBER(8) 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          sfrwlnt.sfrwlnt_term_code%TYPE,
                   p_crn                sfrwlnt.sfrwlnt_crn%TYPE,
                   p_pidm               sfrwlnt.sfrwlnt_pidm%TYPE,
                   p_user_id            sfrwlnt.sfrwlnt_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_start_date         sfrwlnt.sfrwlnt_start_date%TYPE DEFAULT NULL,
                   p_end_date           sfrwlnt.sfrwlnt_end_date%TYPE DEFAULT NULL,
                   p_wl_priority        sfrwlnt.sfrwlnt_wl_priority%TYPE DEFAULT NULL,
                   p_reg_conf_stat      sfrwlnt.sfrwlnt_reg_conf_stat%TYPE DEFAULT NULL,
                   p_data_origin        sfrwlnt.sfrwlnt_data_origin%TYPE DEFAULT NULL,
                   p_error_status       sfrwlnt.sfrwlnt_error_status%TYPE DEFAULT NULL,
                   p_error_count        sfrwlnt.sfrwlnt_error_count%TYPE DEFAULT NULL,
                   p_last_error_message sfrwlnt.sfrwlnt_last_error_message%TYPE DEFAULT NULL,
                   p_last_error_date    sfrwlnt.sfrwlnt_last_error_date%TYPE DEFAULT NULL,
                   p_rowid_out          OUT gb_common.internal_record_id_type)

Creates a record.

Parameters
p_term_code   Term code of the section that has an available seat. VARCHAR2(6) Required Key
p_crn   Course reference number of the section that has an available seat. VARCHAR2(5) Required Key
p_pidm   Unique identifier(PIDM) of the student that has been notified of available seats. NUMBER(8) Required Key
p_user_id   ID of the user who inserted or last updated the data. VARCHAR2(30) Required
p_start_date   Date and time stamp of the notification. DATE
p_end_date   Date and time stamp of deadline to register for the course. DATE
p_wl_priority   Waitlist priority at notification time. NUMBER(11,6)
p_reg_conf_stat   Registration confirmation. Valid values: P-pending, R-student already registered for the Course, X-student was removed from the Waitlist. VARCHAR2(1)
p_data_origin   Source system that created or updated the row. VARCHAR2(30)
p_error_status   Email error status Success(S), Temporary error(T), Permanent error(P), Other(O). VARCHAR2(1)
p_error_count   Number of email failures. NUMBER(5)
p_last_error_message   Last updated email error. VARCHAR2(300)
p_last_error_date   Date and Time stamp of last email error. DATE
p_rowid_out   Database ROWID of the record to be created. VARCHAR2(18) Required

p_delete 

Procedure p_delete(p_term_code sfrwlnt.sfrwlnt_term_code%TYPE,
                   p_crn       sfrwlnt.sfrwlnt_crn%TYPE,
                   p_pidm      sfrwlnt.sfrwlnt_pidm%TYPE,
                   p_rowid     gb_common.internal_record_id_type DEFAULT NULL)

Deletes a record.

Parameters
p_term_code   Term code of the section that has an available seat. VARCHAR2(6) Required Key
p_crn   Course reference number of the section that has an available seat. VARCHAR2(5) Required Key
p_pidm   Unique identifier(PIDM) of the student that has been notified of available seats. NUMBER(8) Required Key
p_rowid   Database ROWID of the record to be deleted. VARCHAR2(18)

p_lock 

Procedure p_lock(p_term_code   sfrwlnt.sfrwlnt_term_code%TYPE,
                 p_crn         sfrwlnt.sfrwlnt_crn%TYPE,
                 p_pidm        sfrwlnt.sfrwlnt_pidm%TYPE,
                 p_rowid_inout IN OUT gb_common.internal_record_id_type)

Locks a record.
If 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 code of the section that has an available seat. VARCHAR2(6) Required Key
p_crn   Course reference number of the section that has an available seat. VARCHAR2(5) Required Key
p_pidm   Unique identifier(PIDM) of the student that has been notified of available seats. NUMBER(8) Required Key
p_rowid_inout   Database ROWID of the record to be locked. VARCHAR2(18) Required

p_update 

Procedure p_update(p_term_code          sfrwlnt.sfrwlnt_term_code%TYPE,
                   p_crn                sfrwlnt.sfrwlnt_crn%TYPE,
                   p_pidm               sfrwlnt.sfrwlnt_pidm%TYPE,
                   p_user_id            sfrwlnt.sfrwlnt_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_start_date         sfrwlnt.sfrwlnt_start_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_end_date           sfrwlnt.sfrwlnt_end_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_wl_priority        sfrwlnt.sfrwlnt_wl_priority%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_reg_conf_stat      sfrwlnt.sfrwlnt_reg_conf_stat%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin        sfrwlnt.sfrwlnt_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_error_status       sfrwlnt.sfrwlnt_error_status%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_error_count        sfrwlnt.sfrwlnt_error_count%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_last_error_message sfrwlnt.sfrwlnt_last_error_message%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_last_error_date    sfrwlnt.sfrwlnt_last_error_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_rowid              gb_common.internal_record_id_type DEFAULT NULL)

Updates a record.

Parameters
p_term_code   Term code of the section that has an available seat. VARCHAR2(6) Required Key
p_crn   Course reference number of the section that has an available seat. VARCHAR2(5) Required Key
p_pidm   Unique identifier(PIDM) of the student that has been notified of available seats. NUMBER(8) Required Key
p_user_id   ID of the user who inserted or last updated the data. VARCHAR2(30) Required
p_start_date   Date and time stamp of the notification. DATE
p_end_date   Date and time stamp of deadline to register for the course. DATE
p_wl_priority   Waitlist priority at notification time. NUMBER(11,6)
p_reg_conf_stat   Registration confirmation. Valid values: P-pending, R-student already registered for the Course, X-student was removed from the Waitlist. VARCHAR2(1)
p_data_origin   Source system that created or updated the row. VARCHAR2(30)
p_error_status   Email error status Success(S), Temporary error(T), Permanent error(P), Other(O). VARCHAR2(1)
p_error_count   Number of email failures. NUMBER(5)
p_last_error_message   Last updated email error. VARCHAR2(300)
p_last_error_date   Date and Time stamp of last email error. DATE
p_rowid   Database ROWID of the record to be updated. VARCHAR2(18)