Package sb_fieldofstudy

Package sb_fieldofstudy_rules

This package provides support subprograms for the Field of Study API (sb_fieldofstudy).
This includes the processes to support the validation of the field of study at creation. Updates to the field of study are not allowed so as to preserve an audit trail of the data.
The only update allowed is to the rolled sequence number from the grade roll process.

Program units

f_seqno	Returns a sequence number for the new record during the create process.
f_rolled_lmod_exists	Checks to see if a record exists for a specific module, used to validate the rolled sequence number.
f_get_conc_attach_majr	Returns the major code for the concentration attached to the rule.
p_register_entity	Registers the attribute/value pairs to the message cache.
p_register_entity	Registers the attribute/value pairs to the message cache.
p_validate_insert	* Validates all the data in the field of study record during a create record activity.

f_seqno

FUNCTION f_seqno(p_pidm       sorlfos.sorlfos_pidm%TYPE,
                 p_lcur_seqno sorlfos.sorlfos_lcur_seqno%TYPE)
  RETURN NUMBER;

Returns a sequence number for the new record during the create process. This function generates the new sequence number which is a one-up number for a PIDM and curriculum.

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.

Returns

Sequence number for the new field of study record. Called during the build process.

f_rolled_lmod_exists

FUNCTION f_rolled_lmod_exists(p_pidm         sorlfos.sorlfos_pidm%TYPE,
                              p_lcur_seqno   sorlfos.sorlfos_lcur_seqno%TYPE,
                              p_rolled_seqno sorlfos.sorlfos_seqno%TYPE)
  RETURN VARCHAR2;

Checks to see if a record exists for a specific module, used to validate the rolled sequence number.

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_rolled_seqno	FIELD OF STUDY SEQNO: Sequence number of the learner field of study. NUMBER(4) Required Key.

Returns

Y if found, otherwise N.

f_get_conc_attach_majr

FUNCTION f_get_conc_attach_majr(p_conc_attach_rule sorlfos.sorlfos_conc_attach_rule%TYPE)
  RETURN VARCHAR2;

Returns the major code for the concentration attached to the rule.

Parameters

p_conc_attach_rule

CONC ATTACH RULE: Unique rule number used as key to the SORCMJR table. NUMBER(8) Required Key.

Returns

Major code

p_register_entity

PROCEDURE p_register_entity(p_operation_type     NUMBER,
                            p_internal_record_id VARCHAR2);

Registers the attribute/value pairs to the message cache. This signature uses the ROWID only for the delete operations.

Parameters

p_internal_record_id

Database ROWID. VARCHAR2(18) Required Key.

p_register_entity

PROCEDURE p_register_entity(p_operation_type     NUMBER,
                            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,
                            p_term_code_end      sorlfos.sorlfos_term_code_end%TYPE,
                            p_dept_code          sorlfos.sorlfos_dept_code%TYPE,
                            p_lfos_rule          sorlfos.sorlfos_lfos_rule%TYPE,
                            p_conc_attach_rule   sorlfos.sorlfos_conc_attach_rule%TYPE,
                            p_start_date         sorlfos.sorlfos_start_date%TYPE,
                            p_end_date           sorlfos.sorlfos_end_date%TYPE,
                            p_tmst_code          sorlfos.sorlfos_tmst_code%TYPE,
                            p_majr_code_attach   sorlfos.sorlfos_majr_code_attach%TYPE,
                            p_rolled_seqno       sorlfos.sorlfos_rolled_seqno%TYPE,
                            p_internal_record_id VARCHAR2);

Registers the attribute/value pairs to the message cache.
This signature registers all the parameters.

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, examples are 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 if the activity status is an active or inactive. VARCHAR2(15) Required Key.
	p_data_origin	DATA SOURCE: Source system that generated the data. VARCHAR2(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. VARCHAR2(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 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 SORCMJR, minor from table SORCMNR, and concentration from table SORCCMN. NUMBER(8).
	p_conc_attach_rule	ATTACHED MAJOR CURRICULUM RULE: Major curriculum rule from table SORCCMN for concentrations attached to a major. NUMBER(8) Required Key.
	p_start_date	START DATE: Start date of the field of study. DATE Required Key.
	p_end_date	END DATE: End date of the field of study. DATE Required Key.
	p_tmst_code	TIME STATUS CODE: Time status code to indicate the intent of the students pursuit of the field of study. VARCHAR2(2) Required Key.
	p_majr_code_attach	MAJOR CODE ATTACHED: Major code which the concentration is attached. VARCHAR2(4).
	p_rolled_seqno	ROLLED SEQNO: Outcome field of study sequence number that was created from the history roll process. This is only present on a learner field of study. Optional Key. NUMBER(4).
	p_internal_record_id	Database ROWID. VARCHAR2(18) Required Key.

p_validate_insert

PROCEDURE p_validate_insert(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_term_code_ctlg_out   OUT sorlfos.sorlfos_term_code_ctlg%TYPE,
                            p_lfos_rule_out        OUT sorlfos.sorlfos_lfos_rule%TYPE,
                            p_conc_attach_rule_out OUT sorlfos.sorlfos_conc_attach_rule%TYPE,
                            p_curr_err_out         OUT NUMBER,
                            p_csts_code_out        OUT sorlfos.sorlfos_csts_code%TYPE,
                            p_cact_code_out        OUT sorlfos.sorlfos_cact_code%TYPE,
                            p_override_severity    VARCHAR2 DEFAULT NULL,
                            p_severity_out         OUT VARCHAR2);

* Validates all the data in the field of study record during a create record activity.

* 1. Validate that the PIDM is for a person. Execute gb_indentification.f_get_entity to verify that the PIDM exists in SPRIDEN and has an entity of P.
* 2. Query the field of study's parent curriculum record. Raise an error if this is missing.

* 3. If the in curriculum status or curriculum activity status is null, execute the procedure soklcur.p_default_status to retrieve the default values.

* 4. Query the curriculum control table SOBCTRL.
*

If curriculum checking is turned on, and the catalog term is not filled in, set it equal to the term code value.
*
Set the out curriculum severity level to the one recorded for the learner module on SOBCTRL.
*
If curriculum checking is turned on, execute the procedure sokcurr.p_checklfos to validate
* the field of study against the curriculum rules. This procedure returns the
* curriculum rule, the attached to major rule code for concentrations and any error.
*
If the in severity level is F, and the severity level on the curriculum control
* table is F, then raise an error if a curriculum error number returned. The
* error message is derived from sokcmsg.f_sorlcurerrormsg.
*

* 5. Count the number of fields of study for the curriculum. The function soklcur.f_count_lfos
*   returns an N if the new record will exceed the number allowed.

* 6. If the learner module is LEARNER, and the rolled sequence number is not null, validate that the
* rolled sequence number exists on an OUTCOME field of study. The f_rolled_lmod_exists function returns
* an N if the OUTCOME curriculum and field of study does not exist. Raise an error if the rolled sequence number is not
* null and the module is not LEARNER.

* 7. Check for missing values on required fields
*       Learner Curriculum Sequence Number
*       Field of Study Sequence Number
*       Learner Field of Study Type
*       Term Code
*       Priority Number
*       Data Origin
*       Major Code
*       Oracle ID

* 8. Check the foreign key values.
*      Curriculum status code against table STVCSTS
*      Curriculum activity status code against table STVCACT
*      Department code against table STVDEPT
*      Learner field of study against table GTVLFST
*      Term code against table STVTERM
*      End term code against table STVTERM
*      Catalog term code against table STVTERM
*      Time status code against table STBTMST
*      Major code attached against table STVMAJR.
*      Major code against table STVMAJR.
*

If the Learner field of study is MAJOR, the STVMAJR_VALID_MAJR_IND = Y
*
If the Learner field of study is MINOR, the STVMAJR_VALID_MINR_IND = Y
*
If the Learner field of study is CONCENTRATION, the STVMAJR_VALID_CONCENTRATN_IND = Y
*

* 9. The term code must equal the curriculum term code.

* 10. The end term code must be greater than or equal to the term code, if not blank.

* 11. The start date must be less than or equal to the end date.

* @param p_pidm PIDM: Unique internal ID for an individual who has records in the system. NUMBER(8) Required Key.
* @param p_lcur_seqno CURRICULUM SEQNO: Sequence number of the parent learner curriculum base record. NUMBER(4) Required Key.
* @param p_seqno FIELD OF STUDY SEQNO: Sequence number of the learner field of study. NUMBER(4) Required Key.
* @param p_lfst_code FIELD OF STUDY CODE: Field of study type code for example MAJOR, MINOR, CONCENTRATION. VARCHAR2(15) Required Key.
* @param p_term_code TERM CODE: Term code for which the field of study is active. VARCHAR2(6) Required Key.
* @param p_priority_no PRIORITY NUMBER: Priority number, or ranking of the field of study. NUMBER(4) Required Key.
* @param p_csts_code CURRICULUM STATUS: Status of the curriculum. VARCHAR2(15) Required Key.
* @param p_cact_code CURRICULUM ACTIVITY STATUS: Curriculum activity status. Table SOBCACT indicates if the activity
* status is an active or inactive. VARCHAR2(15) Required Key.
* @param p_data_origin DATA SOURCE: Source system that generated the data. VARCHAR2(30) Required Key.
* @param p_user_id USER ID: Oracle ID of the most recent user to create or update a record. VARCHAR2(30) Required Key.
* @param p_majr_code MAJOR CODE: Major code for the field of study. VARCHAR2(4) Required Key.
* @param p_term_code_ctlg CATALOG TERM CODE: Term code identifying the catalog for the curriculum. VARCHAR2(6) Optional Key.
* @param p_term_code_end END TERM CODE: End term code the curriculum is active. VARCHAR2(6) Optional Key.
* @param p_dept_code DEPARTMENT CODE: Department code associated with the major field of study. VARCHAR2(4) Optional Key.
* @param 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.
* @param p_conc_attach_rule ATTACHED MAJOR CURRICULUM RULE: Major curriculum rule from SORCCMN for concentrations attached to a major. NUMBER(8) Optional Key.
* @param p_start_date START DATE: Start date of the field of study. DATE Optional Key.
* @param p_end_date END DATE: End date of the field of study. DATE Optional Key.
* @param p_tmst_code TIME STATUS CODE: Time status code to indicate the intent of the students pursuit of the field of study. VARCHAR2(2) Optional Key.
* @param p_majr_code_attach MAJOR CODE ATTACHED: Major to which the concentration is attached. VARCHAR2(4).
* @param p_rolled_seqno ROLLED SEQNO: Outcome field of study sequence number that was created from the history roll process. NUMBER(4) Optional Key.
* @param p_term_code_ctlg_out OUT CATALOG TERM CODE: Defaulted term code identifying the catalog for the curriculum. VARCHAR2(6).
* @param p_lfos_rule_out OUT FIELD OF STUDY CURRICULUM RULE: Defaulted curriculum rule if the in value is null. This is
* determined during the curriculum checking processing from tables SORCCON, SORCMNR and SORCMJR.
* @param p_conc_attach_rule_out OUT ATTACHED MAJOR CURRICULUM RULE: Defaulted curriculum rule for major if the field of
* study is a concentration and is attached to a major. NUMBER(8).
* @param p_curr_err_out OUT CURRICULUM ERROR NUMBER: Error number returned from SOKCURR if the field of study is not attached
* to the curriculum. NUMBER.
* @param p_csts_code_out OUT CURRICULUM STATUS: Defaulted curriculum status if in value is null. VARCHAR2(15).
* @param p_cact_code_out OUT CURRICULUM ACTIVITY STATUS: Defaulted curriculum activity status if the in value is null. VARCHAR2(15).
* @param p_override_severity OVERRIDE CURRICULUM SEVERITY LEVEL: Value to override the severity level. A null value
* will be interpreted as F for fatal. A value of N will cause the API to continue processing even if a
tal curriculum error occurs. This is normally done during batch processing. VARCHAR2.
* @param p_severity_out OUT SEVERITY LEVEL: Curriculum checking value found on the curriculum control page
* SOACTRL for each of the modules. The user interface needs to process all Warning type of errors because the
* object has to display a message and continue processing the transaction. VARCHAR2.