Package sb_curriculum

Package sb_curriculum_rules

This package provides the support subprograms for the Learner Base Curriculum API.
The primary procedure of this package is p_validate_insert. This executes the business rules for the base curriculum (SORLCUR table).
There are no updates allowed to a curriculum; instead, the methodolgy is to employ non-destructive updates.
To change the curriculum, the user must duplicate the record and make the changes to the newly inserted record.

Program units

f_seqno	Returns the sequence number for creation.
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 record during an insert.

f_seqno

FUNCTION f_seqno(p_pidm sorlcur.sorlcur_pidm%TYPE) RETURN NUMBER;

Returns the sequence number for creation. The key to the curriculum record is the PIDM and a sequence number. The sequence is a one-up by PIDM.

Parameters

p_pidm

PIDM: Unique internal ID for an individual who has records in the system. Required Key.

Returns

Sequence number for creation of a new curriculum.

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.

p_register_entity

PROCEDURE p_register_entity(p_operation_type     NUMBER,
                            p_pidm               sorlcur.sorlcur_pidm%TYPE,
                            p_seqno              sorlcur.sorlcur_seqno%TYPE,
                            p_lmod_code          sorlcur.sorlcur_lmod_code%TYPE,
                            p_term_code          sorlcur.sorlcur_term_code%TYPE,
                            p_key_seqno          sorlcur.sorlcur_key_seqno%TYPE,
                            p_priority_no        sorlcur.sorlcur_priority_no%TYPE,
                            p_roll_ind           sorlcur.sorlcur_roll_ind%TYPE,
                            p_cact_code          sorlcur.sorlcur_cact_code%TYPE,
                            p_user_id            sorlcur.sorlcur_user_id%TYPE,
                            p_data_origin        sorlcur.sorlcur_data_origin%TYPE,
                            p_levl_code          sorlcur.sorlcur_levl_code%TYPE,
                            p_coll_code          sorlcur.sorlcur_coll_code%TYPE,
                            p_degc_code          sorlcur.sorlcur_degc_code%TYPE,
                            p_term_code_ctlg     sorlcur.sorlcur_term_code_ctlg%TYPE,
                            p_term_code_end      sorlcur.sorlcur_term_code_end%TYPE,
                            p_term_code_matric   sorlcur.sorlcur_term_code_matric%TYPE,
                            p_term_code_admit    sorlcur.sorlcur_term_code_admit%TYPE,
                            p_admt_code          sorlcur.sorlcur_admt_code%TYPE,
                            p_camp_code          sorlcur.sorlcur_camp_code%TYPE,
                            p_program            sorlcur.sorlcur_program%TYPE,
                            p_start_date         sorlcur.sorlcur_start_date%TYPE,
                            p_end_date           sorlcur.sorlcur_end_date%TYPE,
                            p_curr_rule          sorlcur.sorlcur_curr_rule%TYPE,
                            p_rolled_seqno       sorlcur.sorlcur_rolled_seqno%TYPE,
                            p_styp_code          sorlcur.sorlcur_styp_code%TYPE DEFAULT NULL,
                            p_exp_grad_date      sorlcur.sorlcur_exp_grad_date%TYPE DEFAULT NULL,
                            p_leav_code          sorlcur.sorlcur_leav_code%TYPE DEFAULT NULL,
                            p_leav_from_date     sorlcur.sorlcur_leav_from_date%TYPE DEFAULT NULL,
                            p_leav_to_date       sorlcur.sorlcur_leav_to_date%TYPE DEFAULT NULL,
                            p_rate_code          sorlcur.sorlcur_rate_code%TYPE DEFAULT NULL,
                            p_term_code_grad     sorlcur.sorlcur_term_code_grad%TYPE DEFAULT NULL,
                            p_acyr_code          sorlcur.sorlcur_acyr_code%TYPE DEFAULT NULL,
                            p_site_code          sorlcur.sorlcur_site_code%TYPE DEFAULT NULL,
                            p_appl_key_seqno     sorlcur.sorlcur_appl_key_seqno%TYPE DEFAULT NULL,
                            p_appl_seqno         sorlcur.sorlcur_appl_seqno%TYPE DEFAULT NULL,
                            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. Required Key.
	p_seqno	LCUR SEQNO: One-up number to define the row. Required Key.
	p_lmod_code	LEARNER MODULE CODE: Learner Module Code. Required Key.
	p_term_code	TERM CODE: Term code. Required Key.
	p_key_seqno	KEY SEQNO: Sequence number of the key record, saradap_appl_no, srbrecr_seqno, shrdgmr_seqno. Required Key.
	p_priority_no	PRIORITY NO: Priority of the curriculum within the module. Required Key.
	p_roll_ind	ROLL IND: Y/N indicator that specifies whether the learner curriculum should roll to academic history when courses are rolled. Required Key.
	p_cact_code	CURRICULUM ACTIVITY STATUS: Activity status code for the curriculum. Required Key.
	p_user_id	USER ID: Oracle ID of the most recent user to create or update a record. Required Key.
	p_data_origin	DATA SOURCE: Source system that generated the data. Required Key.
	p_levl_code	LEVEL CODE: Level code of the curriculum. Required Key.
	p_coll_code	COLLEGE CODE: College code of the curriculum. Required Key.
	p_degc_code	DEGREE CODE: Degree code of the curriculum. Required Key.
	p_term_code_ctlg	TERM CATALOG: Catalog term code of the learner curriculum. Required Key.
	p_term_code_end	TERM END: End term code of the curriculum. Required Key.
	p_term_code_matric	TERM MATRICULATED: Term code in which the learner matriculated into the curriculum. Required Key.
	p_term_code_admit	TERM ADMIT: Term code learner was admitted to curriculum. Required Key.
	p_admt_code	ADMIT CODE: Admissions code for the learners admission to the curriculum. Required Key.
	p_camp_code	CAMPUS CODE: Campus code of the curriculum. Required Key.
	p_program	PROGRAM: Program of the curriculum. Required Key.
	p_start_date	START DATE: Date on which the curriculum starts. Required Key.
	p_end_date	END DATE: Date on which the curriculum ends. Required Key.
	p_curr_rule	CURRICULUM RULE NUMBER: Rule number of the curriculum. Required Key.
	p_rolled_seqno	Sequence number from the SORLCUR table indicating the outcome curriculum. Required Key.
	p_internal_record_id	Database ROWID. Required Key.
	p_styp_code	STUDENT TYPE: Student type for the effective term. VARCHAR2(1) Required Key.
	p_exp_grad_date	EXPECTED GRAD: Expected graduation date. DATE Optional Key.
	p_leav_code	LEAVE: Reason for the student's leave of absence for the effective term. VARCHAR2(1) Optional Key.
	p_leav_from_date	LEAVE FROM DATE: Begin date for the student's leave of absence for the effective term. DATE Optional Key.
	p_leav_to_date	LEAVE TO DATE: End date for the student's leave of absence for the effective term. DATE Optional Key.
	p_rate_code	RATE: Specific assessment rate of the student for the effective term. VARCHAR2(5) Optional Key.
	p_term_code_grad	GRAD TERM: Term that the student intends to graduate. VARCHAR2(6) Optional Key.
	p_acyr_code	GRAD YEAR: Year that the student intends to graduate. VARCHAR2(4) Optional Key.
	p_site_code	SITE: Site Code. VARCHAR2(3) Optional Key.
	p_appl_seqno	APPL SEQNO: Sequence number from the application curriculum the learner curriculum was created from. NUMBER(4) Optional Key.
	p_appl_key_seqno	APPL KEY SEQNO: Application number from the application the learner curriculum was created from. NUMBER(4) Optional Key.

p_validate_insert

PROCEDURE p_validate_insert(p_pidm                 sorlcur.sorlcur_pidm%TYPE,
                            p_seqno                sorlcur.sorlcur_seqno%TYPE,
                            p_lmod_code            sorlcur.sorlcur_lmod_code%TYPE,
                            p_term_code            sorlcur.sorlcur_term_code%TYPE,
                            p_key_seqno            sorlcur.sorlcur_key_seqno%TYPE,
                            p_priority_no          sorlcur.sorlcur_priority_no%TYPE,
                            p_roll_ind             sorlcur.sorlcur_roll_ind%TYPE,
                            p_cact_code            sorlcur.sorlcur_cact_code%TYPE,
                            p_user_id              sorlcur.sorlcur_user_id%TYPE,
                            p_data_origin          sorlcur.sorlcur_data_origin%TYPE,
                            p_levl_code            sorlcur.sorlcur_levl_code%TYPE,
                            p_coll_code            sorlcur.sorlcur_coll_code%TYPE,
                            p_degc_code            sorlcur.sorlcur_degc_code%TYPE,
                            p_term_code_ctlg       sorlcur.sorlcur_term_code_ctlg%TYPE DEFAULT NULL,
                            p_term_code_end        sorlcur.sorlcur_term_code_end%TYPE DEFAULT NULL,
                            p_term_code_matric     sorlcur.sorlcur_term_code_matric%TYPE DEFAULT NULL,
                            p_term_code_admit      sorlcur.sorlcur_term_code_admit%TYPE DEFAULT NULL,
                            p_admt_code            sorlcur.sorlcur_admt_code%TYPE DEFAULT NULL,
                            p_camp_code            sorlcur.sorlcur_camp_code%TYPE DEFAULT NULL,
                            p_program              sorlcur.sorlcur_program%TYPE DEFAULT NULL,
                            p_start_date           sorlcur.sorlcur_start_date%TYPE DEFAULT NULL,
                            p_end_date             sorlcur.sorlcur_end_date%TYPE DEFAULT NULL,
                            p_rolled_seqno         sorlcur.sorlcur_rolled_seqno%TYPE DEFAULT NULL,
                            p_keyseqno_out         OUT sorlcur.sorlcur_key_seqno%TYPE,
                            p_term_code_ctlg_out   OUT sorlcur.sorlcur_term_code_ctlg%TYPE,
                            p_roll_ind_out         OUT sorlcur.sorlcur_roll_ind%TYPE,
                            p_curr_rule_out        OUT sorlcur.sorlcur_curr_rule%TYPE,
                            p_curr_err_out         OUT NUMBER,
                            p_cact_code_out        OUT sorlcur.sorlcur_cact_code%TYPE,
                            p_term_code_out        OUT sorlcur.sorlcur_term_code%TYPE,
                            p_term_code_admit_out  OUT sorlcur.sorlcur_term_code_admit%TYPE,
                            p_term_code_matric_out OUT sorlcur.sorlcur_term_code_matric%TYPE,
                            p_override_severity    VARCHAR2 DEFAULT NULL,
                            p_severity_out         OUT VARCHAR2,
                            p_styp_code            sorlcur.sorlcur_styp_code%TYPE DEFAULT NULL,
                            p_exp_grad_date        sorlcur.sorlcur_exp_grad_date%TYPE DEFAULT NULL,
                            p_leav_code            sorlcur.sorlcur_leav_code%TYPE DEFAULT NULL,
                            p_leav_from_date       sorlcur.sorlcur_leav_from_date%TYPE DEFAULT NULL,
                            p_leav_to_date         sorlcur.sorlcur_leav_to_date%TYPE DEFAULT NULL,
                            p_rate_code            sorlcur.sorlcur_rate_code%TYPE DEFAULT NULL,
                            p_term_code_grad       sorlcur.sorlcur_term_code_grad%TYPE DEFAULT NULL,
                            p_acyr_code            sorlcur.sorlcur_acyr_code%TYPE DEFAULT NULL,
                            p_site_code            sorlcur.sorlcur_site_code%TYPE DEFAULT NULL,
                            p_appl_seqno           sorlcur.sorlcur_appl_seqno%TYPE DEFAULT NULL,
                            p_appl_key_seqno       sorlcur.sorlcur_appl_key_seqno%TYPE DEFAULT NULL);

Validates all the data in the record during an insert. Updates are not allowed except to the rolled sequence number which comes directly from the grade roll and learner outcome creation process.
Validation Steps Validate that the PIDM exists and is a person using common identification function f_get_entity. The returned entity must have a value of P.

Fill in defaults:

If the module is LEARNER, key sequence number = 99
If the module is OUTCOME and the term is null, make the term 999999. This should be filled in with the outcome's student record term code.
If the roll indicator is null, change it to Y.
If the module is LEARNER, set the admissions term, matriculation term equal to the term code if they are null.

Derive the curriculum checking error level from the curriculum control table SOBCTRL:

If curriculum checking is checked on the curriculum control page, call the sokcurr.p_checkbase to validate the curriculum.
If the return curriculum error is not null, the override severity is F, the module error level on the curriculum control table is F, return an error.
If this is the primary curriculum, set the roll indicator to the primary roll indicator on the curriculum. If it is a secondary curriculum, set it to the secondary roll indicator.

Validate the existence of the module record:

Admissions, must exist in table SARADAP for the PIDM, TERM and KEYSEQNO
Recruit: must exist in table SRBRECR for PIDM, TERM and KEYSEQNO.
Outcome: must exist in table SHRDGMR for PIDM and KEYSEQNO.
Learner: must exist in table SGBSTDN for PIDM and EFFECTIVE TERM.

Default the Curriculum Status (CACT) code
If the input value is null, call the procedure soklcur.p_default_status to derive the value.

Validate mandatory items:
PIDM
Learner Module Code
Term Code
Key Sequence Number
Priority Number
Roll Indicator
Curriculum Status
Oracle ID
Data Origin
Level Code
College Code
Degree Code

Validate Foreign Keys if value is not null:
Curriculum Status must exist in table STVCACT
Campus Code must exist in table STVCAMP
College Code must exist in table STVCOLL
Level Code must exist in table STVLEVL
Learner Module must exist in table STVLMOD
Term Code must exists in table STVTERM
Admissions Term must exists in table STVTERM
Matriculation Term must exist in table STVTERM
End Term must exist in table STVTERM
Catalog Term must exist in table STVTERM
Leave Code must exist in table STVLEAV
Rate Code must exist in table STVRATE
Academic Year must exist in table STVACYR
Student type must exist in table STVSTYP

Validate that the new curriculum does not exceed the allowed count of the curriculum defined for the module on the curriculum control.

Validate that the start and end date range is logical.

Validate that the end term is greater than the start term for learner curriculum.

Validate that the rolled sequence number exists only on the learner curriculum and the combination of the PIDM and rolled sequence number exists as a curriculum.

Validate that the application key sequence number, and application sequence number exist only on the learner type record, and exists as an application (SARADAP) and a curriculum for the application.

Validate that the leave start and end dates are in a logic range and exist only if a leave code is entered.

Parameters

	p_pidm	PIDM: Unique internal ID for an individual who has records in the system. Required Key.
	p_seqno	LCUR SEQNO: One-up number to define the row. Required Key.
	p_lmod_code	LEARNER MODULE CODE: Learner Module Code. Required Key.
	p_term_code	TERM CODE: Term code. Required Key.
	p_key_seqno	KEY SEQNO: Sequence number of the key record, saradap_appl_no, srbrecr_seqno, shrdgmr_seqno. Required Key.
	p_priority_no	PRIORITY NO: Priority of the curriculum within the module. Required Key.
	p_roll_ind	ROLL IND: Y/N indicator that specified whether the learner curriculum should roll to academic history when courses are rolled. Required Key.
	p_cact_code	CURRICULUM ACTIVITY STATUS: Activity status code for the curriculum. Required Key.
	p_user_id	USER ID: Oracle ID of the most recent user to create or update a record. Required Key.
	p_data_origin	DATA SOURCE: Source system that generated the data. Required Key.
	p_levl_code	LEVEL CODE: Level code of the curriculum. Required Key.
	p_coll_code	COLLEGE CODE: College code of the curriculum. Required Key.
	p_degc_code	DEGREE CODE: Degree code of the curriculum. Required Key.
	p_term_code_ctlg	TERM CATALOG: Catalog term code of the learner curriculum. Optional Key.
	p_term_code_end	TERM END: End term code of the curriculum. Optional Key.
	p_term_code_matric	TERM MATRICULATED: Term code in which the learner matriculated into the curriculum. Optional Key.
	p_term_code_admit	TERM ADMIT: Term code learner was admitted to the curriculum. Optional Key.
	p_admt_code	ADMIT CODE: Admissions code for the learners admission to the curriculum. Optional Key.
	p_camp_code	CAMPUS CODE: Campus code of the curriculum. Optional Key.
	p_program	PROGRAM: Program of the curriculum. Optional Key.
	p_start_date	START DATE: Date on which the curriculum starts. Optional Key.
	p_end_date	END DATE: Date on which the curriculum ends. Optional Key.
	p_curr_rule	CURRICULUM RULE NUMBER: Rule number of the curriculum. Optional Key.
	p_rolled_seqno	SEQUENCE NUMBER: Sequence number on the outcome curriculum created during the grade roll. Optional Key.
	p_term_code_ctlg_out	RETURN CATALOG TERM: If curriculum checking is turned on, and if the upon insertion of a record the catalog code is blank, it is filled in with the term code.
	p_roll_ind_out	RETURN ROLL IND: Default Y. If curriculum is turned on, use values from SORCURR defined for the curriculum.
	p_curr_rule_out	RETURN CURRICULUM RULE: Curriculum rule.
	p_curr_err_out	RETURN CURRICULUM ERROR: Error message, which is filled in only if the severity level is a warning.
	p_cact_code_out	RETURN CURRICULUM ACTIVITY STATUS: Activity status code for the curriculum. If the input value is null, the soklcur.p_default_status is called to determine the status.
	p_term_code_out	RETURN TERM CODE: Term code, set to 999999 if the Learner module is Outcome since the degree record does not have a term code in its key.
	p_term_code_admit_out	RETURN ADMIT TERM CODE: Defaults the admissions term to the term code if the module is Learner and the input value is null.
	p_term_code_matric_out	RETURN MATRIC TERM CODE: Defaults the matriculation term to the term code if the module is Learner and the input value is null.
	p_override_severity	OVERRIDE SEVERITY LEVEL: Value of the curriculum checking severity level coded on SOACTRL. Certain processes must suppress and not process fatal errors. The out value is so the user interface can take appropriate action. Optional key.
	p_severity_out	SEVERITY LEVEL: Value of the curriculum checking severity level coded on SOACTRL. Certain processes must suppress and not process fatal errors. The out value is so the user interface can take appropriate action. Required Out Variable.
	p_styp_code	STUDENT TYPE: Student type for the effective term. VARCHAR2(1) Required Key.
	p_exp_grad_date	EXPECTED GRAD: Expected graduation date. DATE Optional Key.
	p_leav_code	LEAVE: Reason for the student's leave of absence for the effective term. VARCHAR2(1) Optional Key.
	p_leav_from_date	LEAVE FROM DATE: Begin date for the student's leave of absence for the effective term. DATE Optional Key.
	p_leav_to_date	LEAVE TO DATE: End date for the student's leave of absence for the effective term. DATE Optional Key.
	p_rate_code	RATE: Specific assessment rate of the student for the effective term. VARCHAR2(5) Optional Key.
	p_term_code_grad	GRAD TERM: Term that the student intends to graduate. VARCHAR2(6) Optional Key.
	p_acyr_code	GRAD YEAR: Year that the student intends to graduate. VARCHAR2(4) Optional Key.
	p_site_code	SITE: Site Code. VARCHAR2(3) Optional Key.
	p_appl_seqno	APPL SEQNO: Sequence number from the application curriculum the learner curriculum was created from. NUMBER(4) Optional Key.
	p_appl_key_seqno	APPL KEY SEQNO: Application number from the application the learner curriculum was created from. NUMBER(4) Optional Key.