index

Package sb_fieldofstudy

This package provides the Common Business interface for the Field of Study API (sb_fieldofstudy).
The field of study is part of the learner curriculum and is the major, minor or concentration.  The field of study is the child table of the curriculum table SORLCUR.

Program units
f_api_version   Returns the API version number.
f_exists   Checks to see if a field of study record exists.
f_query_all   Selects all fields of study for a curriculum.
f_isequal   Compares two records for equality.
f_query_one   Selects one field of study record using the primary key columns.
f_query_current   Selects current fields of study for the PIDM, curriculum and field of study type.
f_find_current_all_ind   Selects current (Y) or all (N) status indicator.
f_query_max_seq   Selects the maximum sequence number for the curriculum, field of study type and priority.
f_query_one_lock   Locks a field of study record.
f_query_by_rowid   Selects one field of study record using the ROWID.
f_lfos_order   Returns an integer representing the order of the field of study based on its priority.
p_create   Creates a field of study record.  No updates are allowed on the field of study and curriculum tables, except to update the rolled outcome sequence number.  To update a value on a record, the user must insert and duplicate the row and then change the value on the new row.
p_delete   Deletes a field of study record.
p_lock   Locks a field of study record.

Types
fieldofstudy_rec   Field of study record type.
fieldofstudy_ref   Field of study cursor variable type.
fieldofstudy_tab   Field of study table type.

Constants
M_ENTITY_NAME   Business Entity name for Field of Study.

M_ENTITY_NAME 

M_ENTITY_NAME    CONSTANT VARCHAR2(12) := 'FIELDOFSTUDY';

Business Entity name for Field of Study.

fieldofstudy_rec 

TYPE fieldofstudy_rec IS RECORD (
   r_pidm                    sorlfos.sorlfos_pidm%TYPE,
   r_lcur_seqno              sorlfos.sorlfos_lcur_seqno%TYPE,
   r_seqno                   sorlfos.sorlfos_seqno%TYPE,
   r_lfst_code               sorlfos.sorlfos_lfst_code%TYPE,
   r_term_code               sorlfos.sorlfos_term_code%TYPE,
   r_priority_no             sorlfos.sorlfos_priority_no%TYPE,
   r_csts_code               sorlfos.sorlfos_csts_code%TYPE,
   r_cact_code               sorlfos.sorlfos_cact_code%TYPE,
   r_data_origin             sorlfos.sorlfos_data_origin%TYPE,
   r_user_id                 sorlfos.sorlfos_user_id%TYPE,
   r_majr_code               sorlfos.sorlfos_majr_code%TYPE,
   r_term_code_ctlg          sorlfos.sorlfos_term_code_ctlg%TYPE,
   r_term_code_end           sorlfos.sorlfos_term_code_end%TYPE,
   r_dept_code               sorlfos.sorlfos_dept_code%TYPE,
   r_lfos_rule               sorlfos.sorlfos_lfos_rule%TYPE,
   r_conc_attach_rule        sorlfos.sorlfos_conc_attach_rule%TYPE,
   r_start_date              sorlfos.sorlfos_start_date%TYPE,
   r_end_date                sorlfos.sorlfos_end_date%TYPE,
   r_tmst_code               sorlfos.sorlfos_tmst_code%TYPE,
   r_majr_code_attach        sorlfos.sorlfos_majr_code_attach%TYPE,
   r_rolled_seqno            sorlfos.sorlfos_rolled_seqno%TYPE,
   r_internal_record_id      gb_common.internal_record_id_type );

Field of study record type.

fieldofstudy_ref 

TYPE fieldofstudy_ref IS REF CURSOR RETURN fieldofstudy_rec;

Field of study cursor variable type.

fieldofstudy_tab 

TYPE fieldofstudy_tab IS TABLE OF fieldofstudy_rec INDEX BY BINARY_INTEGER;

Field of study 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_pidm       sorlfos.sorlfos_pidm%TYPE,
                  p_lcur_seqno sorlfos.sorlfos_lcur_seqno%TYPE,
                  p_seqno      sorlfos.sorlfos_seqno%TYPE,
                  p_rowid      VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;

Checks to see if a field of study record exists.

Parameters
p_pidm   PIDM: Unique internal ID for an individual who has records in the system.  NUMBER(8)  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record. NUMBER(4)  Required Key.
p_seqno   FIELD OF STUDY SEQNO: Sequence number of the learner field of study. NUMBER(4)  Required Key.
p_rowid   INTERNAL ROW IDENTIFICATION: Database ROWID.   VARCHAR2(18)  Optional Key.

Returns
Y if found, otherwise N.

f_query_all 

FUNCTION f_query_all(p_pidm       sorlfos.sorlfos_pidm%TYPE,
                     p_lcur_seqno sorlfos.sorlfos_lcur_seqno%TYPE,
                     p_seqno      sorlfos.sorlfos_seqno%TYPE DEFAULT NULL)
  RETURN fieldofstudy_ref;

Selects all fields of study for a curriculum.

Parameters
p_pidm   PIDM: Internal Identification Number of the person or non-person account, valid in SPRIDEN. NUMBER(8) Required Key
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record. NUMBER(4) Required Key.
p_seqno   FIELD OF STUDY SEQNO: Sequence number of the learner field of study.  NUMBER(4) Default NULL

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

f_isequal 

FUNCTION f_isequal(rec_one fieldofstudy_rec, rec_two fieldofstudy_rec)
  RETURN VARCHAR2;

Compares two records for equality.

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

f_query_one 

FUNCTION f_query_one(p_pidm       sorlfos.sorlfos_pidm%TYPE,
                     p_lcur_seqno sorlfos.sorlfos_lcur_seqno%TYPE,
                     p_seqno      sorlfos.sorlfos_seqno%TYPE)
  RETURN fieldofstudy_ref;

Selects one field of study record using the primary key columns.

Parameters
p_pidm   PIDM: Unique internal ID for an individual who has records in the system.  NUMBER(8)  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record. NUMBER(4)  Required Key.
p_seqno   FIELD OF STUDY SEQNO: Sequence number of the learner field of study. NUMBER(4)  Required Key.

Returns
A cursor variable that will fetch exactly one record.

f_query_current 

FUNCTION f_query_current(p_pidm       sorlfos.sorlfos_pidm%type,
                         p_lcur_seqno sorlfos.sorlfos_lcur_seqno%type,
                         p_lfst_code  sorlfos.sorlfos_lfst_code%type DEFAULT NULL,
                         p_lfos_seqno sorlfos.sorlfos_seqno%TYPE DEFAULT NULL,
                         p_active_ind sobcact.sobcact_active_ind%type DEFAULT NULL)
  RETURN fieldofstudy_ref;

Selects current fields of study for the PIDM, curriculum and field of study type.  The current is the field of study that has the highest sequence number within the curriculum, field of study type and priority.  If the active indicator parameter is Y then only the current active fields of study are returned.

Parameters
p_pidm   PIDM: Unique internal ID for an individual who has records in the system.  NUMBER(8)  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record. NUMBER(4)  Required Key.
p_lfst_code   FIELD OF STUDY CODE: Field of study type code, for example MAJOR, MINOR, CONC.  VARCHAR2(15)  Optional Key.
p_seqno   FIELD OF STUDY SEQNO: Sequence number of the learner field of study.  NUMBER(8)  Optional Key.
p_active_ind   ACTIVE INDICATOR: Y/N indicator that specifies whether the return is to be active or any field of study. VARCHAR2(1)  Optional Key.

Returns
A cursor variable that will fetch exactly one record.

f_find_current_all_ind 

FUNCTION f_find_current_all_ind(p_pidm        sorlfos.sorlfos_pidm%TYPE,
                                p_lcur_seqno  sorlfos.sorlfos_lcur_seqno%TYPE,
                                p_seqno       sorlfos.sorlfos_seqno%TYPE,
                                p_lfst_code   sorlfos.sorlfos_lfst_code%TYPE,
                                p_priority_no sorlfos.sorlfos_priority_no%TYPE)
  RETURN VARCHAR2;

Selects current (Y) or all (N) status indicator.

Parameters
p_pidm   PIDM: Unique internal ID for an individual who has records in the system.  NUMBER(8)  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record. NUMBER(2)  Required Key.
p_seqno   FIELD OF STUDY SEQNO: Sequence number of the learner field of study. NUMBER(2)  Required Key.
p_lfst_code   FIELD OF STUDY CODE: Field of study type code, for example MAJOR, MINOR, CONC. VARCHAR2(15)  Required Key.
p_priority_no   PRIORITY NUMBER: Priority number of the field of study.  NUMBER(4)   Required Key.

Returns
A value of Y or N.  The value Y means the field of study is current, a value of N means the field of study is All others or not current.  If the curriculum record is not the current record for the priority, all fields of study will have a value of N.

f_query_max_seq 

FUNCTION f_query_max_seq(p_pidm        sorlfos.sorlfos_pidm%type,
                         p_lcur_seqno  sorlfos.sorlfos_lcur_seqno%type,
                         p_lfst_code   sorlfos.sorlfos_lfst_code%type,
                         p_priority_no sorlfos.sorlfos_priority_no%TYPE)
  RETURN NUMBER;

Selects the maximum sequence number for the curriculum, field of study type and priority.

Parameters
p_pidm   PIDM: Unique internal ID for an individual who has records in the system. NUMBER(8)  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record.  NUMBER(4)  Required Key.
p_seqno   FIELD OF STUDY TYPE: Type of field of study.  NUMBER(4)  Required Key.
p_priority_no   PRIORITY NUMBER: Priority number of the field of study. NUMBER(4)  Required Key.

Returns
The maximum field of study sequence number.

f_query_one_lock 

FUNCTION f_query_one_lock(p_pidm       sorlfos.sorlfos_pidm%TYPE,
                          p_lcur_seqno sorlfos.sorlfos_lcur_seqno%TYPE,
                          p_seqno      sorlfos.sorlfos_seqno%TYPE,
                          p_rowid      VARCHAR2 DEFAULT NULL)
  RETURN fieldofstudy_ref;

Locks a field of study record.

Parameters
p_pidm   PIDM: Unique internal ID for an individual who has records in the system.  NUMBER(8)  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record.  NUMBER(4)  Required Key.
p_seqno   FIELD OF STUDY SEQNO: Sequence number of the learner field of study.  NUMBER(4)  Required Key.

Returns
A cursor variable for one record, locking the record.

f_query_by_rowid 

FUNCTION f_query_by_rowid(p_rowid VARCHAR2) RETURN fieldofstudy_ref;

Selects one field of study record using the ROWID.

Parameters
p_rowid   Database ROWID of the record to be selected.  VARCHAR2(18)  Required Key.

Returns
A cursor variable that will fetch exactly one record.

f_lfos_order 

FUNCTION f_lfos_order(p_pidm       IN sorlcur.sorlcur_pidm%TYPE,
                      p_lcur_seqno sorlcur.sorlcur_seqno%TYPE,
                      p_lfst_code  sorlfos.sorlfos_lfst_code%TYPE,
                      p_rowid      VARCHAR2) RETURN NUMBER;

Returns an integer representing the order of the field of study based on its priority.

Parameters
p_pidm   PIDM: Unique internal ID for an individual who has records in the system.  NUMBER(8)  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record.  NUMBER(4)  Required Key.
p_lfst_code   FIELD OF STUDY CODE: Field of study type code, for example MAJOR, MINOR, CONC. VARCHAR2(15)  Required Key.
p_rowid   Database ROWID of the record to be selected.  VARCHAR2(18)  Required Key.

Returns
An integer representing the order of the current and active field of study.

p_create 

PROCEDURE p_create(p_pidm              sorlfos.sorlfos_pidm%TYPE,
                   p_lcur_seqno        sorlfos.sorlfos_lcur_seqno%TYPE,
                   p_seqno             sorlfos.sorlfos_seqno%TYPE,
                   p_lfst_code         sorlfos.sorlfos_lfst_code%TYPE,
                   p_term_code         sorlfos.sorlfos_term_code%TYPE,
                   p_priority_no       sorlfos.sorlfos_priority_no%TYPE,
                   p_csts_code         sorlfos.sorlfos_csts_code%TYPE,
                   p_cact_code         sorlfos.sorlfos_cact_code%TYPE,
                   p_data_origin       sorlfos.sorlfos_data_origin%TYPE,
                   p_user_id           sorlfos.sorlfos_user_id%TYPE,
                   p_majr_code         sorlfos.sorlfos_majr_code%TYPE,
                   p_term_code_ctlg    sorlfos.sorlfos_term_code_ctlg%TYPE DEFAULT NULL,
                   p_term_code_end     sorlfos.sorlfos_term_code_end%TYPE DEFAULT NULL,
                   p_dept_code         sorlfos.sorlfos_dept_code%TYPE DEFAULT NULL,
                   p_lfos_rule         sorlfos.sorlfos_lfos_rule%TYPE DEFAULT NULL,
                   p_conc_attach_rule  sorlfos.sorlfos_conc_attach_rule%TYPE DEFAULT NULL,
                   p_start_date        sorlfos.sorlfos_start_date%TYPE DEFAULT NULL,
                   p_end_date          sorlfos.sorlfos_end_date%TYPE DEFAULT NULL,
                   p_tmst_code         sorlfos.sorlfos_tmst_code%TYPE DEFAULT NULL,
                   p_majr_code_attach  sorlfos.sorlfos_majr_code_attach%TYPE DEFAULT NULL,
                   p_rolled_seqno      sorlfos.sorlfos_rolled_seqno%TYPE DEFAULT NULL,
                   p_override_severity VARCHAR2 DEFAULT NULL,
                   p_rowid_out         OUT VARCHAR2,
                   p_curr_error_out    OUT NUMBER,
                   p_severity_out      OUT VARCHAR2,
                   p_lfos_seqno_out    OUT sorlfos.sorlfos_seqno%TYPE);

Creates a field of study record.  No updates are allowed on the field of study and curriculum tables, except to update the rolled outcome sequence number.  To update a value on a record, the user must insert and duplicate the row and then change the value on the new row. Each curriculum must have at lease one primary major.

Parameters
p_pidm   PIDM: Unique internal ID for an individual who has records in the system.  NUMBER(8)  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record.  NUMBER(4)  Required Key.
p_seqno   FIELD OF STUDY SEQNO: Sequence number of the learner field of study.  NUMBER(4)  Required Key.
p_lfst_code   FIELD OF STUDY CODE: Field of study type code, for example MAJOR, MINOR, CONC.  VARCHAR2(15)  Required Key.
p_term_code   TERM CODE: Term code for which the field of study is active.  VARCHAR2(6)  Required Key.
p_priority_no   PRIORITY NUMBER: Priority number, or ranking of the field of study.  NUMBER(4)  Required Key.
p_csts_code   CURRICULUM STATUS: Status of the curriculum.  VARCHAR2(15)  Required Key.
p_cact_code   CURRICULUM ACTIVITY STATUS: Curriculum activity status.  Table SOBCACT indicates whether the activity status is active or inactive.  VARCHAR2(15)  Required Key.
p_data_origin   DATA SOURCE: Source system that generated the data.  VARCHAR(30)  Required Key.
p_user_id   USER ID: Oracle ID of the most recent user to create or update a record.  VARCHAR2(30)  Required Key.
p_majr_code   MAJOR CODE: Major code for the field of study.  VARCHAR@(4)  Required Key.
p_term_code_ctlg   CATALOG TERM CODE: Term code identifying the catalog for the curriculum. VARCHAR2(6)  Optional Key.
p_term_code_end   END TERM CODE: End term code for which the curriculum is active.  VARCHAR2(6)  Optional Key.
p_dept_code   DEPARTMENT CODE: Department code associated with the major field of study. VARCHAR2(4)  Optional Key.
p_lfos_rule   FIELD OF STUDY CURRICULUM RULE: Curriculum rule from the curriculum table. The major curriculum rule is from table SORCMJR, minor from table SORCMNR, and concentration from table SORCCMN.  NUMBER(8)  Optional Key.
p_conc_attach_rule   ATTACHED MAJOR CURRICULUM RULE: Major curriculum rule from table SORCCMN for concentrations attached to a major.  NUMBER(8)  Optional Key.
p_start_date   START DATE: Start date of the field of study.  DATE  Optional Key.
p_end_date   END DATE: End date of the field of study.  DATE Optional Key.
p_tmst_code   TIME STATUS CODE: Time status code to indicate the intent of the student's pursuit of the field of study.  VARCHAR2(2)  Optional Key.
p_majr_code_attach   ATTACHED MAJOR CODE: Major code the concentration applies too.  This is filled in only for concentration type field of studys.  VARCHAR2(4)  .
p_rolled_seqno   ROLLED SEQNO:  Outcome field of study sequence number that was created from the history roll process. NUMBER(4).
p_rowid_out   Database ROWID of record created. VARCHAR2(18).
p_curr_error_out   CURRICULUM ERROR: Error assigned by curriculum processing for particular error.  NUMBER.
p_override_severity   OVERRIDE SEVERITY:  Override curriculum error severity level. The default is F if no value is sent.  VARCHAR2  Optional Key.
p_severity_out   SEVERITY:  Original curriculum error severity level coded on the curriculum control page.  VARCHAR2.
p_lfos_seqno_out   FIELD OF STUDY SEQNO OUT: Sequence number for the new field of study record.  NUMBER.

p_delete 

PROCEDURE p_delete(p_pidm       sorlfos.sorlfos_pidm%TYPE,
                   p_lcur_seqno sorlfos.sorlfos_lcur_seqno%TYPE,
                   p_seqno      sorlfos.sorlfos_seqno%TYPE,
                   p_rowid      VARCHAR2 DEFAULT NULL);

Deletes a field of study record.

Parameters
p_pidm   PIDM: Unique internal ID for an individual who has records in the system.  NUMBER(8)  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record.  NUMBER(4)  Required Key.
p_seqno   FIELD OF STUDY SEQNO: Sequence number of the learner field of study.  NUMBER(4)  Required Key.
p_rowid   Database ROWID of the record to  be deleted. VARCHAR2(18)  Optional Key.

p_lock 

PROCEDURE p_lock(p_pidm        sorlfos.sorlfos_pidm%TYPE,
                 p_lcur_seqno  sorlfos.sorlfos_lcur_seqno%TYPE,
                 p_seqno       sorlfos.sorlfos_seqno%TYPE,
                 p_rowid_inout IN OUT VARCHAR2);

Locks a field of study record.

Parameters
p_pidm   PIDM: Unique internal an ID for individual who has records in the system.  Required Key.
p_lcur_seqno   CURRICULUM SEQNO:  Sequence number of the parent learner curriculum base record.  Required Key.
p_seqno   FIELD OF STUDY SEQNO: Sequence number of the learner field of study.  Required Key.
p_rowid_inout   Database ROWID of the record to be locked. In / out variable. The ROWID of the field of study locked is returned to the calling program.