Package sb_estimated

BANINST1@S10B80

Package sb_estimated_grades

Common Business interface for the Faculty Feedback Estimated Grades API.
This API provides the interface to create and maintain the Faculty Feedback Estimated Grades table sfrffst.

This table holds comment text and estimated grades for students in a course. You must create an estimated grades record in order to add Issues or Recommendations via the sb_faculty_feedback API.

In addition to the basic CRUD operations, it also provides a procedure that can be used to insert records for 'Monitored' students using a Banner population selection.

Edits provided by this API enforce the following constraints

Since these are estimated grades, there is no relation to actual grades, and the grade value is not verified.
If entered, the faculty pidm must exist in Spriden.
If entered, the Student pidm must exist in Spriden.
The CRN is mandatory, and must exist in the course section table SSBSECT for the term associated with the Feedback Session.
The Feedback Session ID (p_sfbffsc_id) is required, and must exist in the Feedback Session table sfbffsc.
You cannot delete an Estimated Grade grade record if feedback exists in the sfrffbk table.

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 a session.
f_query_one	Selects one record using surrogate key.
f_query_one	Selects one record using natural key.
p_create	Creates a record.
p_delete	Deletes a record.
p_update	Updates a record.
p_monitor_students	Stages feedback records for Monitored students.

Types

estimated_grades_rec	Business Entity record type
estimated_grades_ref	Entity cursor variable type
estimated_grades_tab	Entity table type

Constants

m_entity_name	Business Entity Name
m_base_table_name	Base table name
m_monitored_status	Status codes
m_optional_status
m_complete_status

m_entity_name

m_entity_name       CONSTANT VARCHAR2 (16) := 'ESTIMATED_GRADES';

Business Entity Name

m_base_table_name

m_base_table_name   CONSTANT VARCHAR2 (7) := 'SFRFFST';

Base table name

m_monitored_status

m_monitored_status   CONSTANT VARCHAR2 (1) := 'M';

Status codes

m_optional_status

m_optional_status   CONSTANT VARCHAR2 (1) := 'O';

m_complete_status

m_complete_status   CONSTANT VARCHAR2 (1) := 'C';

estimated_grades_rec

TYPE estimated_grades_rec IS RECORD
(
  r_surrogate_id    sfrffst.sfrffst_surrogate_id%TYPE,
  r_sfbffsc_id      sfrffst.sfrffst_sfbffsc_id%TYPE,
  r_student_pidm    sfrffst.sfrffst_student_pidm%TYPE,
  r_crn             sfrffst.sfrffst_crn%TYPE,
  r_faculty_pidm    sfrffst.sfrffst_faculty_pidm%TYPE,
  r_status_cde      sfrffst.sfrffst_status_cde%TYPE,
  r_grde_code       sfrffst.sfrffst_grde_code%TYPE,
  r_comments        sfrffst.sfrffst_comments%TYPE,
  r_version         sfrffst.sfrffst_version%TYPE,
  r_data_origin     sfrffst.sfrffst_data_origin%TYPE,
  r_user_id         sfrffst.sfrffst_user_id%TYPE,
  r_activity_date   sfrffst.sfrffst_activity_date%TYPE
);

Business Entity record type

estimated_grades_ref

TYPE estimated_grades_ref IS REF CURSOR
  RETURN estimated_grades_rec;

Entity cursor variable type

estimated_grades_tab

TYPE estimated_grades_tab IS TABLE OF estimated_grades_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 sfrffst.sfrffst_surrogate_id%TYPE)
  RETURN VARCHAR2

Checks if a record exists.

Parameters

p_surrogate_id

Surrogate ID for sfrffst. NUMBER(19) Required Key

Returns

Y if found, otherwise N.

f_exists

Function f_exists(p_session_id sfrffst.sfrffst_sfbffsc_id%TYPE,
                  p_pidm       sfrffst.sfrffst_student_pidm%TYPE,
                  p_crn        sfrffst.sfrffst_crn%TYPE) RETURN VARCHAR2

Checks if a record exists.
@parm p_pidm The pidm of the student recieving feedback Required Key
@parm p_crn The section crn that the feedback is about. Required Key

Parameters

p_session_id

Surrogate ID for the feedback session SIRFFSC. NUMBER(19) Required Key

Returns

Y if found, otherwise N.

f_isequal

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

Returns

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

f_query_all

Function f_query_all(p_session_id sfrffst.sfrffst_sfbffsc_id%TYPE,
                     p_pidm       sfrffst.sfrffst_student_pidm%TYPE DEFAULT NULL,
                     p_crn        sfrffst.sfrffst_crn%TYPE DEFAULT NULL)
  RETURN estimated_grades_tab

Selects all records for a session.
@parm p_pidm The pidm of the student recieving feedback Optional Key
@parm p_crn The section crn that the feedback is about. Optional Key

Parameters

p_surrogate_id

Surrogate ID for the feedback session SIRFFSC. NUMBER(19) Required Key

Returns

A table of records.

f_query_one

Function f_query_one(p_surrogate_id sfrffst.sfrffst_surrogate_id%TYPE)
  RETURN estimated_grades_rec

Selects one record using surrogate key.

Parameters

p_surrogate_id

Surrogate ID for sfrffst. NUMBER(19) Required Key

Returns

A record.

f_query_one

Function f_query_one(p_session_id sfrffst.sfrffst_sfbffsc_id%TYPE,
                     p_pidm       sfrffst.sfrffst_student_pidm%TYPE,
                     p_crn        sfrffst.sfrffst_crn%TYPE)
  RETURN estimated_grades_rec

Selects one record using natural key.
@parm p_pidm The pidm of the student recieving feedback Rerquired Key
@parm p_crn The section crn that the feedback is about. Rerquired Key

Parameters

p_surrogate_id

Surrogate ID for sfrffst. NUMBER(19) Required Key

Returns

A record.

p_create

Procedure p_create(p_surrogate_id_out OUT sfrffst.sfrffst_surrogate_id%TYPE,
                   p_sfbffsc_id       sfrffst.sfrffst_sfbffsc_id%TYPE,
                   p_student_pidm     sfrffst.sfrffst_student_pidm%TYPE,
                   p_crn              sfrffst.sfrffst_crn%TYPE,
                   p_faculty_pidm     sfrffst.sfrffst_faculty_pidm%TYPE DEFAULT NULL,
                   p_status_cde       sfrffst.sfrffst_status_cde%TYPE,
                   p_grde_code        sfrffst.sfrffst_grde_code%TYPE DEFAULT NULL,
                   p_comments         sfrffst.sfrffst_comments%TYPE DEFAULT NULL,
                   p_data_origin      sfrffst.sfrffst_data_origin%TYPE,
                   p_user_id          sfrffst.sfrffst_user_id%TYPE DEFAULT gb_common.f_sct_user)

Creates a record.

Parameters

	p_surrogate_id_out	Surrogate ID for sfrffst. NUMBER(19) Required Key
	p_sfbffsc_id	Surrogate ID from the session control table for which this feedback is being entered. NUMBER(19) Required
	p_student_pidm	The pidm of the student about whom the feedback is entered. NUMBER(8) Required
	p_crn	The course number of the course the student has been registered. VARCHAR2(5) Required
	p_faculty_pidm	The pidm of the faculty person entering the feedback. NUMBER(8)
	p_status_cde	(M)onitored, (O)ptional, (C)ompleted. Completed is per CRN/Faculty. VARCHAR2(1) Required
	p_grde_code	VARCHAR2(6)
	p_comments	Free-form overall comments entered by the faculty. VARCHAR2(4000)
	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 sfrffst.sfrffst_surrogate_id%TYPE,
                   p_version      sfrffst.sfrffst_version%TYPE)

Deletes a record.

Parameters

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

p_update

Procedure p_update(p_surrogate_id sfrffst.sfrffst_surrogate_id%TYPE,
                   p_sfbffsc_id   sfrffst.sfrffst_sfbffsc_id%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_student_pidm sfrffst.sfrffst_student_pidm%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_crn          sfrffst.sfrffst_crn%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_faculty_pidm sfrffst.sfrffst_faculty_pidm%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_status_cde   sfrffst.sfrffst_status_cde%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_grde_code    sfrffst.sfrffst_grde_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_comments     sfrffst.sfrffst_comments%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_version      sfrffst.sfrffst_version%TYPE,
                   p_data_origin  sfrffst.sfrffst_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id      sfrffst.sfrffst_user_id%TYPE DEFAULT gb_common.f_sct_user)

Updates a record.

Parameters

	p_surrogate_id	Surrogate ID for sfrffst. NUMBER(19) Required Key
	p_sfbffsc_id	Surrogate ID from the session control table for which this feedback is being entered. NUMBER(19) Required
	p_student_pidm	The pidm of the student about whom the feedback is entered. NUMBER(8) Required
	p_crn	The course number of the course the student has been registered. VARCHAR2(5) Required
	p_faculty_pidm	The pidm of the faculty person entering the feedback. NUMBER(8)
	p_status_cde	(M)onitored, (O)ptional, (C)ompleted. Completed is per CRN/Faculty. VARCHAR2(1) Required
	p_grde_code	VARCHAR2(6)
	p_comments	Free-form overall comments entered by the faculty. VARCHAR2(4000)
	p_version	Optimistic lock token for sfrffst. 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_monitor_students

Procedure p_monitor_students(p_term_code   sfbffsc.sfbffsc_term_code%TYPE,
                             p_description sfbffsc.sfbffsc_description%TYPE,
                             p_application VARCHAR2,
                             p_selection   VARCHAR2,
                             p_creator_id  VARCHAR2,
                             p_user_id     VARCHAR2)

Stages feedback records for Monitored students.
Takes a population selection and a Faculty Feedback period and for all the students in the population, creates estimated grade records for this feedback session, with the type 'M'.

Parameters

	p_term_code	Feedback Session term code. VARCHAR2 Required Key
	p_description	Feedback Session description. VARCHAR2 Required Key
	p_application	Job Submission application name. VARCHAR2 Required Key
	p_selection	Job Submission selection name. VARCHAR2 Required Key
	p_creator_id	Job Submission creator. VARCHAR2 Required Key
	p_user_id	Job Submission user id. VARCHAR2 Required Key