Package sb_feedback

BANINST1@S10B80

Package sb_feedback_session

Common Business interface for the Faculty Feedback Session Control API (sb_feedback_session).

This API provides the interface to create and maintain Faculty Feedback Sessions table sfbffsc.

All Faculty Feedback data depends on the Feedback Session record created by this API.

Once a Feedback Session is defined, then Estimated Grades can be created for the students in each class for the term defined in the Feedback Session.

Optionally, Faculty Feedback Issues and Recommendations can then be created for each Estimated Grade record.

In addition to the basic CRUD operations, it also provides a procedure that can be used to purge Feedback data when that information is no longer needed.

Edits provided by this enforce the following constraints

Date ranges cannot overlap for a given term code.
Term and Description combination must be unique.
Once feedback has been entered (there is at least one row in the Estimated Grades table sfrffst), then the term and description cannot be updated.
You cannot delete a feedback session if feedback exists. In that event, you must use the purge process to remove feedback data.

All Faculty Feedback tables have a surrogate id column, which is a unique numeric identifier for the record. All foreign key references between tables are based on this column. The API automatically generates this value.

The API implements optimistic locking for concurrency control. The Version number column is automatically generated on create, and subsequent update operations increment it. Both update and delete operations require that you query the record and pass back the version number. The update or delete will fail if the version number of the record in the db does not match the version number you pass in.
This prevents overwriting other sessions updates, while minimizing time spent waiting for explicit locks.

Program units

f_api_version	Returns the API version number.
f_exists	Checks if a record exists.
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 natural key.
f_feedback_exists	Returns Y if feedback exists for the session in the Estimated Grades table (sfrffst), otherwise N.
f_query_one	Selects one record using key.
p_create	Creates a Faculty Feedback Session Control record.
p_delete	Deletes a Faculty Feedback Session Control record.
p_update	Updates a Faculty Feedback Session Control record.
p_purge	Purges Faculty Feedback data.

Types

feedback_session_rec	Business Entity record type
feedback_session_ref	Entity cursor variable type
feedback_session_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(16) := 'FEEDBACK_SESSION';

Business Entity Name

M_BASE_TABLE_NAME

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

Base table name

feedback_session_rec

TYPE feedback_session_rec IS RECORD (
  r_surrogate_id            sfbffsc.sfbffsc_surrogate_id%TYPE,
  r_term_code               sfbffsc.sfbffsc_term_code%TYPE,
  r_description             sfbffsc.sfbffsc_description%TYPE,
  r_suspend_feedback_ind    sfbffsc.sfbffsc_suspend_feedback_ind%TYPE,
  r_start_date              sfbffsc.sfbffsc_start_date%TYPE,
  r_end_date                sfbffsc.sfbffsc_end_date%TYPE,
  r_add_comments_flag       sfbffsc.sfbffsc_add_comments_flag%TYPE,
  r_add_recommends_flag     sfbffsc.sfbffsc_add_recommends_flag%TYPE,
  r_version                 sfbffsc.sfbffsc_version%TYPE,
  r_data_origin             sfbffsc.sfbffsc_data_origin%TYPE,
  r_user_id                 sfbffsc.sfbffsc_user_id%TYPE,
  r_activity_date           sfbffsc.sfbffsc_activity_date%TYPE);

Business Entity record type

feedback_session_ref

TYPE feedback_session_ref IS REF CURSOR RETURN feedback_session_rec;

Entity cursor variable type

feedback_session_tab

TYPE feedback_session_tab IS TABLE OF feedback_session_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_surrogate_id sfbffsc.sfbffsc_surrogate_id%TYPE)
  RETURN VARCHAR2

Checks if a record exists.

Parameters

p_surrogate_id

Surrogate ID for the Faculty Feedback Session Control table (sfbffsc). NUMBER(19) Required Key

Returns

Y if found, otherwise N.

f_exists

Function f_exists(p_term_code   sfbffsc.sfbffsc_term_code%TYPE,
                  p_description sfbffsc.sfbffsc_description%TYPE)
  RETURN VARCHAR2

Checks if a record exists.

Parameters

	p_term_code	Term code for which faculty feedback is being requested. VARCHAR2(6) Required Key
	p_description	Feedback session description. VARCHAR2(30) Required

Returns

Y if found, otherwise N.

f_isequal

Function f_isequal(rec_one feedback_session_rec,
                   rec_two feedback_session_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 feedback_session_rec Required
	rec_two	The second record to compare. Type feedback_session_rec Required

Returns

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

f_query_all

Function f_query_all RETURN feedback_session_tab

Selects all records for the entity.

Parameters

p_surrogate_id

Surrogate ID for Faculty Feedback Session Control table (sfbffsc). NUMBER(19) Required Key

Returns

A table of records.

f_query_one

Function f_query_one(p_term_code   sfbffsc.sfbffsc_term_code%TYPE,
                     p_description sfbffsc.sfbffsc_description%TYPE)
  RETURN feedback_session_rec

Selects one record using natural key.

Parameters

	p_term_code	Term code for which faculty feedback is being requested. VARCHAR2(6) Required Key
	p_description	Feedback session description. VARCHAR2(30) Required

Returns

A record.

f_feedback_exists

Function f_feedback_exists(p_session_id sfbffsc.sfbffsc_surrogate_id%TYPE)
  RETURN VARCHAR2

Returns Y if feedback exists for the session in the Estimated Grades table (sfrffst), otherwise N.

Parameters

p_surrogate_id

Surrogate ID for Faculty Feedback Session Control table (sfbffsc). NUMBER(19) Required Key

Returns

Y or N.

f_query_one

Function f_query_one(p_surrogate_id sfbffsc.sfbffsc_surrogate_id%TYPE)
  RETURN feedback_session_rec

Selects one record using key.

Parameters

p_surrogate_id

Surrogate ID for Faculty Feedback Session Control table (sfbffsc). NUMBER(19) Required Key

Returns

A record.

p_create

Procedure p_create(p_surrogate_id_out     OUT sfbffsc.sfbffsc_surrogate_id%TYPE,
                   p_term_code            sfbffsc.sfbffsc_term_code%TYPE,
                   p_description          sfbffsc.sfbffsc_description%TYPE,
                   p_suspend_feedback_ind sfbffsc.sfbffsc_suspend_feedback_ind%TYPE,
                   p_start_date           sfbffsc.sfbffsc_start_date%TYPE,
                   p_end_date             sfbffsc.sfbffsc_end_date%TYPE,
                   p_add_comments_flag    sfbffsc.sfbffsc_add_comments_flag%TYPE,
                   p_add_recommends_flag  sfbffsc.sfbffsc_add_recommends_flag%TYPE,
                   p_data_origin          sfbffsc.sfbffsc_data_origin%TYPE,
                   p_user_id              sfbffsc.sfbffsc_user_id%TYPE DEFAULT gb_common.f_sct_user)

Creates a Faculty Feedback Session Control record.
Note the Term and Description combination must be unique.
Start and End dates cannot overlap.

Parameters

	p_surrogate_id	Generated Surrogate ID for Faculty Feedback Session Control table (sfbffsc). NUMBER(19)
	p_term_code	Term code for which faculty feedback is being requested. VARCHAR2(6) Required
	p_description	Feedback session description. VARCHAR2(30) Required
	p_suspend_feedback_ind	Y indicates this session has been suspended for feedback. VARCHAR2(1) Required
	p_start_date	The date within a term from which faculty can start providing feedback. DATE Required
	p_end_date	The date after which no more feedback can be provided. DATE Required
	p_add_comments_flag	Y indicates faculty can enter free-form comments while choosing concerns. VARCHAR2(1) Required
	p_add_recommends_flag	Y indicates faculty can provide recommendations to mitigate the concerns. VARCHAR2(1) Required
	p_data_origin	This system maintained field specifies the origin of the data. VARCHAR2(30) Required
	p_user_id	Oracle User who created or updated the record. VARCHAR2(30) Required

p_delete

Procedure p_delete(p_surrogate_id sfbffsc.sfbffsc_surrogate_id%TYPE,
                   p_version      sfbffsc.sfbffsc_version%TYPE)

Deletes a Faculty Feedback Session Control record.
All Faculty Feedback and Estimated Grades must be deleted first or an exception will be thrown.
The Version number passed in must match the version number of the record in the Database. If it is different, then another user has updated the record, and an exception stating such will be thrown.

Parameters

	p_surrogate_id	Surrogate ID for Faculty Feedback Session Control table (sfbffsc). NUMBER(19) Required Key
	p_version	Optimistic lock token for Faculty Feedback Session Control table (sfbffsc). NUMBER(19) Required

p_update

Procedure p_update(p_surrogate_id         sfbffsc.sfbffsc_surrogate_id%TYPE,
                   p_term_code            sfbffsc.sfbffsc_term_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_description          sfbffsc.sfbffsc_description%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_suspend_feedback_ind sfbffsc.sfbffsc_suspend_feedback_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_start_date           sfbffsc.sfbffsc_start_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_end_date             sfbffsc.sfbffsc_end_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_add_comments_flag    sfbffsc.sfbffsc_add_comments_flag%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_add_recommends_flag  sfbffsc.sfbffsc_add_recommends_flag%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_version              sfbffsc.sfbffsc_version%TYPE,
                   p_data_origin          sfbffsc.sfbffsc_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id              sfbffsc.sfbffsc_user_id%TYPE DEFAULT gb_common.f_sct_user)

Updates a Faculty Feedback Session Control record.
The description cannot be updated to be the same as the description for another Session in the same term.
Also, the description cannot be updated if feedback exists for the session.

Parameters

	p_surrogate_id	Surrogate ID for Faculty Feedback Session Control table (sfbffsc). NUMBER(19) Required Key
	p_term_code	Term code for which faculty feedback is being requested. VARCHAR2(6) Required
	p_description	Feedback session description. VARCHAR2(30) Required
	p_suspend_feedback_ind	Y indicates this session has been suspended for feedback. VARCHAR2(1) Required
	p_start_date	The date within a term from which faculty can start providing feedback. DATE Required
	p_end_date	The date after which no more feedback can be provided. DATE Required
	p_add_comments_flag	Y indicates faculty can enter free-form comments while choosing concerns. VARCHAR2(1) Required
	p_add_recommends_flag	Y indicates faculty can provide recommendations to mitigate the concerns. VARCHAR2(1) Required
	p_version	Optimistic lock token for Faculty Feedback Session Control table (sfbffsc). NUMBER(19) Required
	p_data_origin	This system maintained field specifies the origin of the data. VARCHAR2(30) Required
	p_user_id	Oracle User who created or updated the record. VARCHAR2(30) Required

p_purge

Procedure p_purge(p_term_code   sfbffsc.sfbffsc_term_code%TYPE,
                  p_description sfbffsc.sfbffsc_description%TYPE)

Purges Faculty Feedback data.
Note. This will delete ALL faculty feedback data for the period, as long as the feedback end date is less that sysdate.

Parameters

	p_term_code	Term code for faculty feedback session to purge. VARCHAR2(6) Required
	p_description	Feedback session description. VARCHAR2(30) Required