Package sb_faculty

BANINST1@S10B80

Package sb_faculty_feedback

Common Business interface for the Faculty Feedback API (sb_faculty_feedback).
This API provides the interface to create and maintain Faculty Feedback Issues and Recommendations table sfrffbk.

Data entered via this API depends on an Estimated Grade record existing.

Edits provided by this API enforce the following constraints:

There is no update provided by this API. Only create and delete operations are permitted.
The estimated grade id is mandatory must exist in the SFRFFST table.
The Feedback Code Id is mandatory must exist in the STVFFVA table.
You cannot create more than one record with the same Feedback Code Id and Estimated Grade Id.

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 feedback exists for a given estimated grade record.
f_isequal	Compares two records for equality.
f_query_all	Selects all isssues/recommendations for the pidm/crn of the estimated grade record.
f_query_one	Selects one record using surrogate key.
f_query_one	Selects one record using naturalkey.
p_create	Creates a record.
p_delete	Deletes a record.
p_delete	Deletes a record.

Types

faculty_feedback_rec	Business Entity record type
faculty_feedback_ref	Entity cursor variable type
faculty_feedback_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) := 'FACULTY_FEEDBACK';

Business Entity Name

M_BASE_TABLE_NAME

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

Base table name

faculty_feedback_rec

TYPE faculty_feedback_rec IS RECORD (
  r_surrogate_id            sfrffbk.sfrffbk_surrogate_id%TYPE,
  r_sfrffst_id              sfrffbk.sfrffbk_sfrffst_id%TYPE,
  r_stvffva_id              sfrffbk.sfrffbk_stvffva_id%TYPE,
  r_version                 sfrffbk.sfrffbk_version%TYPE,
  r_data_origin             sfrffbk.sfrffbk_data_origin%TYPE,
  r_user_id                 sfrffbk.sfrffbk_user_id%TYPE,
  r_activity_date           sfrffbk.sfrffbk_activity_date%TYPE);

Business Entity record type

faculty_feedback_ref

TYPE faculty_feedback_ref IS REF CURSOR RETURN faculty_feedback_rec;

Entity cursor variable type

faculty_feedback_tab

TYPE faculty_feedback_tab IS TABLE OF faculty_feedback_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 sfrffbk.sfrffbk_surrogate_id%TYPE)
  RETURN VARCHAR2

Checks if a record exists.

Parameters

p_surrogate_id

Surrogate ID for sfrffbk. NUMBER(19) Required Key

Returns

Y if found, otherwise N.

f_exists

Function f_exists(p_sfrffst_id sfrffbk.sfrffbk_sfrffst_id%TYPE,
                  p_stvffva_id sfrffbk.sfrffbk_stvffva_id%TYPE DEFAULT NULL)
  RETURN VARCHAR2

Checks if feedback exists for a given estimated grade record.
If no feedback type id is supplied, it returns yes if any records at all exist.

Parameters

	p_sfrffst_id	Surrogate ID from the required students sfrffst table for which this feedback is being entered. NUMBER(19) Required Key
	p_stvffva_id	The surrogate id of the actual issue or recommendation that the faculty has about the student. NUMBER(19) Required Key

Returns

Y if found, otherwise N.

f_isequal

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

Returns

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

f_query_all

Function f_query_all(p_sfrffst_id sfrffbk.sfrffbk_sfrffst_ID%TYPE)
  RETURN faculty_feedback_tab

Selects all isssues/recommendations for the pidm/crn of the estimated grade record.

Parameters

p_sfrffst_id

Surrogate ID from the required students sfrffst table for which this feedback is being entered. NUMBER(19) Required Key

Returns

A cursor variable that will fetch a set of records.

f_query_one

Function f_query_one(p_surrogate_id sfrffbk.sfrffbk_surrogate_id%TYPE)
  RETURN faculty_feedback_rec

Selects one record using surrogate key.

Parameters

p_surrogate_id

Surrogate ID for sfrffbk. NUMBER(19) Required Key

Returns

A cursor variable that will fetch exactly one record.

f_query_one

Function f_query_one(p_sfrffst_id sfrffbk.sfrffbk_sfrffst_ID%TYPE,
                     p_stvffva_id sfrffbk.sfrffbk_STVFFVA_ID%TYPE)
  RETURN faculty_feedback_rec

Selects one record using naturalkey.

Parameters

	p_sfrffst_id	Surrogate ID from the required students sfrffst table for which this feedback is being entered. NUMBER(19) Required Key
	p_stvffva_id	The surrogate id of the actual issue or recommendation that the faculty has about the student. NUMBER(19) Required Key

Returns

A record.

p_create

Procedure p_create(p_surrogate_id_out OUT sfrffbk.sfrffbk_surrogate_id%TYPE,
                   p_sfrffst_id       sfrffbk.sfrffbk_sfrffst_id%TYPE,
                   p_stvffva_id       sfrffbk.sfrffbk_stvffva_id%type,
                   p_data_origin      sfrffbk.sfrffbk_data_origin%TYPE,
                   p_user_id          sfrffbk.sfrffbk_user_id%TYPE DEFAULT gb_common.f_sct_user)

Creates a record.

Parameters

	p_surrogate_id_out	Surrogate ID for sfrffbk. NUMBER(19) Required Key
	p_sfrffst_id	Surrogate ID from the required students sfrffst table for which this feedback is being entered. NUMBER(19) Required
	p_stvffva_id	The surrogate id of the actual issue or recommendation that the faculty has about the student. NUMBER(19) Required Key
	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 sfrffbk.sfrffbk_surrogate_id%TYPE)

Deletes a record.

Parameters

	p_surrogate_id	Surrogate ID for sfrffbk. NUMBER(19) Required Key
	p_version	Optimistic lock token for sfrffbk. NUMBER(19) Required

p_delete

Procedure p_delete(p_sfrffst_id sfrffbk.sfrffbk_sfrffst_id%TYPE,
                   p_stvffva_id sfrffbk.sfrffbk_stvffva_id%type)

Deletes a record.
This supports deleting a feedback record based on the parent key and the key for the feedback type.

Parameters

	p_sfrffst_id	Surrogate ID from the required students sfrffst table for which this feedback is being entered. NUMBER(19) Required
	p_stvffva_id	The surrogate id of the actual issue or recommendation that the faculty has about the student. NUMBER(19) Required Key