Package sb_learner

Package sb_learner_rules

This package provides the support subprograms for the Learner API (sb_learner).
The primary procedure of this package is p_validate. This executes the business rules for the learner (SGBSTDN table).

Program units

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	Validates all the data in the learner record.

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 primary key only for the delete operations.

Parameters

	p_pidm	PIDM: Unique internal ID for an individual who has records in the system. NUMBER(8) Required Key.
	p_term_code_eff	EFF TERM: Effective term of the learner record. VARCHAR2(6) Required Key.

p_register_entity

Procedure p_register_entity(p_operation_type       NUMBER,
                            p_pidm                 sgbstdn.sgbstdn_pidm%TYPE,
                            p_term_code_eff        sgbstdn.sgbstdn_term_code_eff%TYPE,
                            p_stst_code            sgbstdn.sgbstdn_stst_code%TYPE,
                            p_styp_code            sgbstdn.sgbstdn_styp_code%TYPE,
                            p_exp_grad_date        sgbstdn.sgbstdn_exp_grad_date%TYPE,
                            p_full_part_ind        sgbstdn.sgbstdn_full_part_ind%TYPE,
                            p_sess_code            sgbstdn.sgbstdn_sess_code%TYPE,
                            p_resd_code            sgbstdn.sgbstdn_resd_code%TYPE,
                            p_orsn_code            sgbstdn.sgbstdn_orsn_code%TYPE,
                            p_prac_code            sgbstdn.sgbstdn_prac_code%TYPE,
                            p_advr_pidm            sgbstdn.sgbstdn_advr_pidm%TYPE,
                            p_grad_credit_appr_ind sgbstdn.sgbstdn_grad_credit_appr_ind%TYPE,
                            p_capl_code            sgbstdn.sgbstdn_capl_code%TYPE,
                            p_leav_code            sgbstdn.sgbstdn_leav_code%TYPE,
                            p_leav_from_date       sgbstdn.sgbstdn_leav_from_date%TYPE,
                            p_leav_to_date         sgbstdn.sgbstdn_leav_to_date%TYPE,
                            p_astd_code            sgbstdn.sgbstdn_astd_code%TYPE,
                            p_term_code_astd       sgbstdn.sgbstdn_term_code_astd%TYPE,
                            p_rate_code            sgbstdn.sgbstdn_rate_code%TYPE,
                            p_edlv_code            sgbstdn.sgbstdn_edlv_code%TYPE,
                            p_incm_code            sgbstdn.sgbstdn_incm_code%TYPE,
                            p_emex_code            sgbstdn.sgbstdn_emex_code%TYPE,
                            p_aprn_code            sgbstdn.sgbstdn_aprn_code%TYPE,
                            p_trcn_code            sgbstdn.sgbstdn_trcn_code%TYPE,
                            p_gain_code            sgbstdn.sgbstdn_gain_code%TYPE,
                            p_voed_code            sgbstdn.sgbstdn_voed_code%TYPE,
                            p_blck_code            sgbstdn.sgbstdn_blck_code%TYPE,
                            p_term_code_grad       sgbstdn.sgbstdn_term_code_grad%TYPE,
                            p_acyr_code            sgbstdn.sgbstdn_acyr_code%TYPE,
                            p_site_code            sgbstdn.sgbstdn_site_code%TYPE,
                            p_egol_code            sgbstdn.sgbstdn_egol_code%TYPE,
                            p_degc_code_dual       sgbstdn.sgbstdn_degc_code_dual%TYPE,
                            p_levl_code_dual       sgbstdn.sgbstdn_levl_code_dual%TYPE,
                            p_dept_code_dual       sgbstdn.sgbstdn_dept_code_dual%TYPE,
                            p_coll_code_dual       sgbstdn.sgbstdn_coll_code_dual%TYPE,
                            p_majr_code_dual       sgbstdn.sgbstdn_majr_code_dual%TYPE,
                            p_bskl_code            sgbstdn.sgbstdn_bskl_code%TYPE,
                            p_prev_code            sgbstdn.sgbstdn_prev_code%TYPE,
                            p_term_code_prev       sgbstdn.sgbstdn_term_code_prev%TYPE,
                            p_cast_code            sgbstdn.sgbstdn_cast_code%TYPE,
                            p_term_code_cast       sgbstdn.sgbstdn_term_code_cast%TYPE,
                            p_user_id              sgbstdn.sgbstdn_user_id%TYPE,
                            p_data_origin          sgbstdn.sgbstdn_data_origin%TYPE,
                            p_scpc_code            sgbstdn.sgbstdn_scpc_code%TYPE,
                            p_internal_record_id   VARCHAR2)

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

Parameters

	p_pidm	PIDM: Unique internal ID for an individual who has records in the system. NUMBER(8) Required Key.
	p_term_code_eff	EFF TERM: Effective term of the learner record. VARCHAR2(6) Required Key.
	p_stst_code	STATUS: Student's status for the effective term. VARCHAR2(2) 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 Required Key.
	p_full_part_ind	FULL PART: Specifies whether the student is a full-time or part-time student. VARCHAR2(1) Required Key.
	p_sess_code	SESSION: Session that the student is attending for the effective term. VARCHAR2(6) Required Key.
	p_resd_code	RESIDENCE: Residency status of the student for the effective term. VARCHAR2(1) Required Key.
	p_orsn_code	ORIENTATION: Orientation session assigned to the student for the effective term. VARCHAR2(1) Required Key.
	p_prac_code	PRAC TRAINING: Practical training experience of the student for the effective term. VARCHAR2(2) Required Key.
	p_advr_pidm	ADVISOR: Internal identification number for the advisor assigned to the student for the effective term. NUMBER(8) Required Key.
	p_grad_credit_appr_ind	GRAD CREDIT: Eligibility of the student to take graduate courses for credit for the effective term. Valid values are Y or blank only. VARCHAR2(1) Optional Key.
	p_capl_code	CAREER:Career plan of the student for the effective term. VARCHAR2(2) Required Key.
	p_leav_code	LEAVE: Reason for the student's leave of absence for the effective term. VARCHAR2(1) Required Key.
	p_leav_from_date	LEAVE FROM DATE: Begin date of the student's leave of absence for the effective term. DATE Required Key.
	p_leav_to_date	LEAVE TO DATE: End date of the student's leave of absence for the effective term. DATE Required Key.
	p_astd_code	ACAD STAND: Academic standing override for the student for the effective term. VARCHAR2(2) Required Key.
	p_term_code_astd	ACAD STAND TERM: Term associated with the academic standing override. VARCHAR2(6) Required Key.
	p_rate_code	RATE: Specific assessment rate of the student for the effective term. VARCHAR2(5) Required Key.
	p_edlv_code	EDUCATION LEVEL: Two-position alphanumeric field used to indicate the highest level of education that the student completed. VARCHAR2(3)Required Key.
	p_incm_code	INCOME: Two-position alphanumeric field used to indicate the income range of the student. VARCHAR2(2) Required Key.
	p_admt_code	ADMISSIONS: Admissions type from the admissions application. VARCHAR2(2) Required Key.
	p_emex_code	EMPLOY EXPEPT: General Student Employment Expectation Code. VARCHAR2(2) Required Key.
	p_aprn_code	APPRENTICESHIP: General Student Apprenticeship Code. VARCHAR2(2) Required Key.
	p_trcn_code	TRANSFER: General Student Transfer Center Code. VARCHAR2(2) Required Key.
	p_gain_code	EMPLOY TRAINING: Employment and training code of the student for the effective term. VARCHAR2(2) Required Key.
	p_voed_code	VOCATIONAL: General Student Vocation Education Status Code VARCHAR2(2) Required Key.
	p_blck_code	BLOCK: Block Schedule Code. VARCHAR2(10) Required Key.
	p_term_code_grad	GRAD TERM: Term that the student intends to graduate. VARCHAR2(6) Required Key.
	p_acyr_code	GRAD YEAR: Year that the student intends to graduate. VARCHAR2(4) Required Key.
	p_site_code	SITE: Site Code. VARCHAR2(3) Required Key.
	p_egol_code	EDUC LEVEL: Educational Goal code. VARCHAR2(2) Required Key.
	p_degc_code_dual	DUAL DEGREE: Degree code for dual degree. VARCHAR2(6) Required Key.
	p_levl_code_dual	DUAL LEVEL: Level code for a dual degree. VARCHAR2(2) Required Key.
	p_dept_code_dual	DUAL DEPARTMENT: Department code for a dual degree. VARCHAR2(4) Required Key.
	p_coll_code_dual	DUAL COLLEGE: College code for a dual degree. VARCHAR2(2) Required Key.
	p_majr_code_dual	DUAL MAJOR: Major code for a dual degree. VARCHAR2(4) Required Key.
	p_bskl_code	BASIC SKILLS: Student Basic Skills Code. VARCHAR2(2) Required Key.
	p_prev_code	PROG EVAL: General Student record Progress Evaluation code. This code overrides the code in table SHRTTRM. VARCHAR2(2) Required Key.
	p_term_code_prev	PROG EVAL TERM: General Student record Progress Evaluation term. This is the term for which the progress evaluation code override becomes effective. VARCHAR2(6) Required Key.
	p_cast_code	COMBINED ACAD STAND: General Student record Combined Academic Standing code. This code overrides the code in table SHRTTRM. VARCHAR2(2) Required Key.
	p_term_code_cast	COMBINED ACAD STAND TERM: General Student record Combined Academic Standing term. This is the term for which the combined academic standing code override becomes effective. VARCHAR2(6) Required Key.
	p_user_id	USER: Oracle ID of the person who updated the record. VARCHAR2(30) Required Key.
	p_data_origin	DATA ORIGIN: Origination of the data. VARCHAR2(30) Required Key.
	p_rowid	Database ROWID of the record to be updated. VARCHAR2(18) Required Key.
	p_scpc_code	STUDENT CENTRIC PERIOD CYCLE: Cycle Code for the student centric semester. Varchar2(6) Optional key.

p_validate

Procedure p_validate(p_pidm                 sgbstdn.sgbstdn_pidm%TYPE,
                     p_term_code_eff        sgbstdn.sgbstdn_term_code_eff%TYPE,
                     p_stst_code            sgbstdn.sgbstdn_stst_code%TYPE,
                     p_styp_code            sgbstdn.sgbstdn_styp_code%TYPE,
                     p_exp_grad_date        sgbstdn.sgbstdn_exp_grad_date%TYPE DEFAULT NULL,
                     p_full_part_ind        sgbstdn.sgbstdn_full_part_ind%TYPE DEFAULT NULL,
                     p_sess_code            sgbstdn.sgbstdn_sess_code%TYPE DEFAULT NULL,
                     p_resd_code            sgbstdn.sgbstdn_resd_code%TYPE DEFAULT NULL,
                     p_orsn_code            sgbstdn.sgbstdn_orsn_code%TYPE DEFAULT NULL,
                     p_prac_code            sgbstdn.sgbstdn_prac_code%TYPE DEFAULT NULL,
                     p_advr_pidm            sgbstdn.sgbstdn_advr_pidm%TYPE DEFAULT NULL,
                     p_grad_credit_appr_ind sgbstdn.sgbstdn_grad_credit_appr_ind%TYPE DEFAULT NULL,
                     p_capl_code            sgbstdn.sgbstdn_capl_code%TYPE DEFAULT NULL,
                     p_leav_code            sgbstdn.sgbstdn_leav_code%TYPE DEFAULT NULL,
                     p_leav_from_date       sgbstdn.sgbstdn_leav_from_date%TYPE DEFAULT NULL,
                     p_leav_to_date         sgbstdn.sgbstdn_leav_to_date%TYPE DEFAULT NULL,
                     p_astd_code            sgbstdn.sgbstdn_astd_code%TYPE DEFAULT NULL,
                     p_term_code_astd       sgbstdn.sgbstdn_term_code_astd%TYPE DEFAULT NULL,
                     p_rate_code            sgbstdn.sgbstdn_rate_code%TYPE DEFAULT NULL,
                     p_edlv_code            sgbstdn.sgbstdn_edlv_code%TYPE DEFAULT NULL,
                     p_incm_code            sgbstdn.sgbstdn_incm_code%TYPE DEFAULT NULL,
                     p_emex_code            sgbstdn.sgbstdn_emex_code%TYPE DEFAULT NULL,
                     p_aprn_code            sgbstdn.sgbstdn_aprn_code%TYPE DEFAULT NULL,
                     p_trcn_code            sgbstdn.sgbstdn_trcn_code%TYPE DEFAULT NULL,
                     p_gain_code            sgbstdn.sgbstdn_gain_code%TYPE DEFAULT NULL,
                     p_voed_code            sgbstdn.sgbstdn_voed_code%TYPE DEFAULT NULL,
                     p_blck_code            sgbstdn.sgbstdn_blck_code%TYPE DEFAULT NULL,
                     p_term_code_grad       sgbstdn.sgbstdn_term_code_grad%TYPE DEFAULT NULL,
                     p_acyr_code            sgbstdn.sgbstdn_acyr_code%TYPE DEFAULT NULL,
                     p_site_code            sgbstdn.sgbstdn_site_code%TYPE DEFAULT NULL,
                     p_egol_code            sgbstdn.sgbstdn_egol_code%TYPE DEFAULT NULL,
                     p_degc_code_dual       sgbstdn.sgbstdn_degc_code_dual%TYPE DEFAULT NULL,
                     p_levl_code_dual       sgbstdn.sgbstdn_levl_code_dual%TYPE DEFAULT NULL,
                     p_dept_code_dual       sgbstdn.sgbstdn_dept_code_dual%TYPE DEFAULT NULL,
                     p_coll_code_dual       sgbstdn.sgbstdn_coll_code_dual%TYPE DEFAULT NULL,
                     p_majr_code_dual       sgbstdn.sgbstdn_majr_code_dual%TYPE DEFAULT NULL,
                     p_bskl_code            sgbstdn.sgbstdn_bskl_code%TYPE DEFAULT NULL,
                     p_prev_code            sgbstdn.sgbstdn_prev_code%TYPE DEFAULT NULL,
                     p_term_code_prev       sgbstdn.sgbstdn_term_code_prev%TYPE DEFAULT NULL,
                     p_cast_code            sgbstdn.sgbstdn_cast_code%TYPE DEFAULT NULL,
                     p_term_code_cast       sgbstdn.sgbstdn_term_code_cast%TYPE DEFAULT NULL,
                     p_user_id              sgbstdn.sgbstdn_user_id%TYPE DEFAULT NULL,
                     p_data_origin          sgbstdn.sgbstdn_data_origin%TYPE DEFAULT NULL,
                     p_scpc_code            sgbstdn.sgbstdn_scpc_code%TYPE DEFAULT NULL)

Validates all the data in the learner record.
Validates that the required data is present:

PIDM
Effective Term
Student Status
Student Type
Residence

Validates that the PIDM exists and is a person using common identification function f_get_entity. The returned entity must have a value of P.

Validates foreign key constraints for all code type columns.

Validates that if the leave code is not blank, the leave start and end date are present.
The leave start date must be less than or equal to the leave end date. If either leave start or end date are present, the leave code must also be present.

Validates that both an academic standing override code and term are present if one is not blank.
The academic standing override term must equal the learner effective term.
Validates that both the combined academic standing override code and term are present if one is not blank. Validates that both the progress evaluation code and term are present if one is not blank.

The full - part time indicator must have a value of F, P or be blank.

The dual degree code must be present if the dual level, major, department or college are entered.
The graduate approval credit indicator must be Y or blank.

Parameters

	p_pidm	PIDM: Unique internal ID for an individual who has records in the system. NUMBER(8) Required Key.
	p_term_code_eff	EFF TERM: Effective term of the learner record. VARCHAR2(6) Required Key.
	p_stst_code	STATUS: Students status for the effective term. VARCHAR2(2) 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 Required Key.
	p_full_part_ind	FULL PART: Specifies whether the student is a full- or part-time student. VARCHAR2(1) Required Key.
	p_sess_code	SESSION: Session that the student is attending for the effective term. VARCHAR2(6) Required Key.
	p_resd_code	RESIDENCE: Residency status of the student for the effective term. VARCHAR2(1) Required Key.
	p_orsn_code	ORIENTATION: Orientation session assigned to the student for the effective term. VARCHAR2(1) Required Key.
	p_prac_code	PRAC TRAINING: Practical training experience of the student for the effective term. VARCHAR2(2) Required Key.
	p_advr_pidm	ADVISOR: Internal identification number for the advisor assigned to the student for the effective term. NUMBER(8) Required Key.
	p_grad_credit_appr_ind	GRAD CREDIT: Eligibility of the student to take graduate courses for credit for the effective term. Valid values are Y or blank only. VARCHAR2(1) Optional Key.
	p_capl_code	CAREER: Career plan of the student for the effective term. VARCHAR2(2) Required Key.
	p_leav_code	LEAVE: Reason for the student's leave of absence for the effective term. VARCHAR2(1) Required Key.
	p_leav_from_date	LEAVE FROM DATE: Begin date of the student's leave of absence for the effective term. DATE Required Key.
	p_leav_to_date	LEAVE TO DATE: End date of the student's leave of absence for the effective term. DATE Required Key.
	p_astd_code	ACAD STAND: Academic standing override for the student for the effective term. VARCHAR2(2) Required Key.
	p_term_code_astd	ACAD STAND TERM: Term associated with the academic standing override. VARCHAR2(6) Required Key.
	p_rate_code	RATE: Specific assessment rate of the student for the effective term. VARCHAR2(5) Required Key.
	p_edlv_code	EDUCATION LEVEL: Two-position alphanumeric field used to indicate the highest level of education that the student completed. VARCHAR2(3)Required Key.
	p_incm_code	INCOME: Two-position alphanumeric field used to indicate the income range of the student. VARCHAR2(2) Required Key.
	p_admt_code	ADMISSIONS: Admissions type from the admissions application. VARCHAR2(2) Required Key.
	p_emex_code	EMPLOY EXPEPT: General Student Employment Expectation Code. VARCHAR2(2) Required Key.
	p_aprn_code	APPRENTICESHIP: General Student Apprenticeship Code. VARCHAR2(2) Required Key.
	p_trcn_code	TRANSFER: General Student Transfer Center Code. VARCHAR2(2) Required Key.
	p_gain_code	EMPLOY TRAINING: Employment and training code of the student for the effective term. VARCHAR2(2) Required Key.
	p_voed_code	VOCATIONAL: General Student Vocation Education Status Code VARCHAR2(2) Required Key.
	p_blck_code	BLOCK: Block Schedule Code. VARCHAR2(10) Required Key.
	p_term_code_grad	GRAD TERM: Term that the student intends to graduate. VARCHAR2(6) Required Key.
	p_acyr_code	GRAD YEAR: Year that the student intends to graduate. VARCHAR2(4) Required Key.
	p_site_code	SITE: Site Code. VARCHAR2(3) Required Key.
	p_egol_code	EDUC LEVEL: Educational Goal code. VARCHAR2(2) Required Key.
	p_degc_code_dual	DUAL DEGREE: Degree code for a dual degree. VARCHAR2(6) Required Key.
	p_levl_code_dual	DUAL LEVEL: Level code for a dual degree. VARCHAR2(2) Required Key.
	p_dept_code_dual	DUAL DEPARTMENT: Department code for a dual degree. VARCHAR2(4) Required Key.
	p_coll_code_dual	DUAL COLLEGE: College code for a dual degree. VARCHAR2(2) Required Key.
	p_majr_code_dual	DUAL MAJOR: Major code for a dual degree. VARCHAR2(4) Required Key.
	p_bskl_code	BASIC SKILLS: Student Basic Skills Code. VARCHAR2(2) Required Key.
	p_prev_code	PROG EVAL: General Student record Progress Evaluation code. This code overrides the code in table SHRTTRM. VARCHAR2(2) Required Key.
	p_term_code_prev	PROG EVAL TERM: General Student record Progress Evaluation term. This is the term for which the progress evaluation code override becomes effective. VARCHAR2(6) Required Key.
	p_cast_code	COMBINED ACAD STAND: General Student record Combined Academic Standing code. This code overrides the code in table SHRTTRM. VARCHAR2(2) Required Key.
	p_term_code_cast	COMBINED ACAD STAND TERM: General Student record Combined Academic Standing term. This is the term for which the combined academic standing code override becomes effective. VARCHAR2(6) Required Key.
	p_user_id	USER: Oracle ID of the person who updated the record. VARCHAR2(30) Required Key.
	p_data_origin	DATA ORIGIN: Origination of the data. VARCHAR2(30) Required Key.
	p_scpc_code	STUDENT CENTRIC PERIOD CYCLE: Cycle Code for the student centric semester. Varchar2(6) Optional key.