Package sb_feedback

BANINST1@S10B80

Package sb_feedback_codes

Common Business interface for the Faculty Feedback Codes API (sb_feedback_codes).

This API provides the interface to create and maintain Faculty Feedback Codes table stvffva.

This validation table contains the Issues and Recommendations codes used in collection of Faculty Feedback by the Faculty Feedback API.
There are two types of codes, Issues and Recommendations.

Edits provided by this enforce the following constraints

Codes are unique, regardless of type.
Once feedback has been entered (there is at least one row in the Faculty Feedback table sfrffbk), then the code record cannot be deleted. You must either manually delete the feedback records, or use the Purge process to remove the faculty feedback records, after which you can delete the Feedback Code record.

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_all	Selects all records for the entity by type ISSUE or RECOMMENDATION.
f_query_one	Selects one record using key.
f_query_one	Selects one record using key.
p_create	Creates a record.
p_delete	Deletes a record.
p_update	Updates a record.

Types

feedback_codes_rec	Business Entity record type
feedback_codes_ref	Entity cursor variable type
feedback_codes_tab	Entity table type

Constants

M_ENTITY_NAME	Business Entity Name
M_BASE_TABLE_NAME	Base table name
ISSUE_TYPE_NAME
RECOMMEND_TYPE_NAME

M_ENTITY_NAME

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

Business Entity Name

M_BASE_TABLE_NAME

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

Base table name

ISSUE_TYPE_NAME

ISSUE_TYPE_NAME CONSTANT VARCHAR2(50) := 'ISSUE';

RECOMMEND_TYPE_NAME

RECOMMEND_TYPE_NAME CONSTANT VARCHAR2(50) := 'RECOMMENDATION';

feedback_codes_rec

TYPE feedback_codes_rec IS RECORD (
  r_surrogate_id            stvffva.stvffva_surrogate_id%TYPE,
  r_code                    stvffva.stvffva_code%TYPE,
  r_desc                    stvffva.stvffva_desc%TYPE,
  r_type                    stvffva.stvffva_type%TYPE,
  r_active_ind              stvffva.stvffva_active_ind%TYPE,
  r_version                 stvffva.stvffva_version%TYPE,
  r_data_origin             stvffva.stvffva_data_origin%TYPE,
  r_user_id                 stvffva.stvffva_user_id%TYPE,
  r_activity_date           stvffva.stvffva_activity_date%TYPE);

Business Entity record type

feedback_codes_ref

TYPE feedback_codes_ref IS REF CURSOR RETURN feedback_codes_rec;

Entity cursor variable type

feedback_codes_tab

TYPE feedback_codes_tab IS TABLE OF feedback_codes_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 stvffva.stvffva_surrogate_id%TYPE)
  RETURN VARCHAR2

Checks if a record exists.

Parameters

p_surrogate_id

Surrogate ID for STVFFVA. NUMBER(19) Required Key

Returns

Y if found, otherwise N.

f_exists

Function f_exists(p_code stvffva.stvffva_code%TYPE) RETURN VARCHAR2

Checks if a record exists.

Parameters

p_code

VARCHAR2(30) Required

Returns

Y if found, otherwise N.

f_isequal

Function f_isequal(rec_one feedback_codes_rec, rec_two feedback_codes_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_codes_rec Required
	rec_two	The second record to compare. Type feedback_codes_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_codes_tab

Selects all records for the entity.

Parameters

p_surrogate_id

Surrogate ID for STVFFVA. NUMBER(19) Required Key

Returns

A table of records.

f_query_all

Function f_query_all(p_type stvffva.stvffva_type%type)
  RETURN feedback_codes_tab

Selects all records for the entity by type ISSUE or RECOMMENDATION.

Parameters

p_type

Type of code Required Key

Returns

A table of records.

f_query_one

Function f_query_one(p_code stvffva.stvffva_code%TYPE)
  RETURN feedback_codes_rec

Selects one record using key.

Parameters

p_code

VARCHAR2(30) Required

Returns

A record.

f_query_one

Function f_query_one(p_surrogate_id stvffva.stvffva_surrogate_id%TYPE)
  RETURN feedback_codes_rec

Selects one record using key.

Parameters

p_surrogate_id

Surrogate ID for STVFFVA. NUMBER(19) Required Key

Returns

Y if found, otherwise N.

p_create

Procedure p_create(p_surrogate_id_out OUT stvffva.stvffva_surrogate_id%TYPE,
                   p_code             stvffva.stvffva_code%TYPE,
                   p_desc             stvffva.stvffva_desc%TYPE,
                   p_type             stvffva.stvffva_type%TYPE,
                   p_active_ind       stvffva.stvffva_active_ind%TYPE,
                   p_data_origin      stvffva.stvffva_data_origin%TYPE,
                   p_user_id          stvffva.stvffva_user_id%TYPE DEFAULT gb_common.f_sct_user)

Creates a record.

Parameters

	p_surrogate_id	Surrogate ID for STVFFVA. NUMBER(19) Required Key
	p_code	VARCHAR2(30) Required
	p_desc	Faculty Issue or Recommendation Name. VARCHAR2(255) Required
	p_type	ISSUE or RECOMMENDATION. VARCHAR2(20) Required
	p_active_ind	Y indicates the value is active, N indicates not active and will not be available for selection. 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 stvffva.stvffva_surrogate_id%TYPE,
                   p_version      stvffva.stvffva_version%TYPE)

Deletes a record.

Parameters

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

p_update

Procedure p_update(p_surrogate_id stvffva.stvffva_surrogate_id%TYPE,
                   p_code         stvffva.stvffva_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_desc         stvffva.stvffva_desc%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_type         stvffva.stvffva_type%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_active_ind   stvffva.stvffva_active_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_version      stvffva.stvffva_version%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_data_origin  stvffva.stvffva_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id      stvffva.stvffva_user_id%TYPE DEFAULT gb_common.f_sct_user)

Updates a record.

Parameters

	p_surrogate_id	Surrogate ID for STVFFVA. NUMBER(19) Required Key
	p_code	VARCHAR2(30) Required
	p_desc	Faculty Issue or Recommendation Name. VARCHAR2(255) Required
	p_type	ISSUE or RECOMMENDATION. VARCHAR2(20) Required
	p_active_ind	Y indicates the value is active, N indicates not active and will not be available for selection. VARCHAR2(1) Required
	p_version	Optimistic lock token for STVFFVA. 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