APPS.IGS_PR_VAL_SPO SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 29

  p_inserting IN BOOLEAN ,
  p_updating IN BOOLEAN ,
  p_deleting IN BOOLEAN ,
  p_message_name OUT NOCOPY VARCHAR2 )
  RETURN BOOLEAN IS
  	v_index			BINARY_INTEGER;

Line: 50

  		-- Validate insert
  		IF p_inserting THEN
  			IF igs_pr_val_spo.prgp_val_spo_ins (
  					gt_rowid_table(v_index).person_id,
  					gt_rowid_table(v_index).course_cd,
  					gt_rowid_table(v_index).prg_cal_type,
  					gt_rowid_table(v_index).prg_ci_sequence_number,
  					gt_rowid_table(v_index).rule_check_dt,
  					gt_rowid_table(v_index).progression_rule_cat,
  					gt_rowid_table(v_index).pra_sequence_number,
  					v_message_name) = FALSE THEN
  				p_message_name := v_message_name;

Line: 66

  		IF p_inserting OR (p_updating AND
  		   gt_rowid_table(v_index).new_decision_status <>
  		   gt_rowid_table(v_index).old_decision_status) THEN
  			IF igs_pr_val_spo.prgp_val_spo_dcsn (
  						gt_rowid_table(v_index).person_id,
  						gt_rowid_table(v_index).course_cd,
  						gt_rowid_table(v_index).prg_cal_type,
  						gt_rowid_table(v_index).prg_ci_sequence_number,
  						gt_rowid_table(v_index).rule_check_dt,
  						gt_rowid_table(v_index).progression_rule_cat,
  						gt_rowid_table(v_index).pra_sequence_number,
  						gt_rowid_table(v_index).progression_outcome_type,
  						gt_rowid_table(v_index).old_decision_status,
  						gt_rowid_table(v_index).new_decision_status,
  						gt_rowid_table(v_index).decision_dt,
  						gt_rowid_table(v_index).decision_org_unit_cd,
  						gt_rowid_table(v_index).decision_ou_start_dt,
  						gt_rowid_table(v_index).applied_dt,
  						gt_rowid_table(v_index).expiry_dt,
  						v_message_name) = FALSE THEN
  				p_message_name := v_message_name;

Line: 91

  		IF p_inserting OR (p_updating AND
   		   gt_rowid_table(v_index).new_decision_status <>
  		   gt_rowid_table(v_index).old_decision_status) THEN
  			IF igs_pr_val_spo.prgp_val_spo_approve (
  					gt_rowid_table(v_index).person_id,
  					gt_rowid_table(v_index).course_cd,
  					gt_rowid_table(v_index).sequence_number,
  					gt_rowid_table(v_index).progression_outcome_type,
  					gt_rowid_table(v_index).old_decision_status,
  					gt_rowid_table(v_index).new_decision_status,
  					gt_rowid_table(v_index).encmb_course_group_cd,
  					gt_rowid_table(v_index).restricted_enrolment_cp,
  					gt_rowid_table(v_index).restricted_attendance_type,
  					v_message_name) = FALSE THEN
  				p_message_name := v_message_name;

Line: 110

  		IF p_inserting OR (p_updating AND
  		   NVL(gt_rowid_table(v_index).new_duration, -1) <>
  		   NVL(gt_rowid_table(v_index).old_duration, -1) OR
  		   NVL(gt_rowid_table(v_index).new_duration_type, ' ') <>
  		   NVL(gt_rowid_table(v_index).old_duration_type, ' ')) THEN
  			IF igs_pr_val_spo.prgp_val_spo_drtn (
  						gt_rowid_table(v_index).person_id,
  						gt_rowid_table(v_index).course_cd,
  						gt_rowid_table(v_index).sequence_number,
  						gt_rowid_table(v_index).new_decision_status,
  						gt_rowid_table(v_index).old_duration,
  						gt_rowid_table(v_index).new_duration,
  						gt_rowid_table(v_index).old_duration_type,
  						gt_rowid_table(v_index).new_duration_type,
  						gt_rowid_table(v_index).expiry_dt,
  						v_message_name) = FALSE THEN
  				p_message_name := v_message_name;

Line: 196

  		SELECT	'X'
  		FROM	IGS_PR_STDNT_PR_OU              spo
  		WHERE	spo.person_id			= p_person_id AND
  			spo.course_cd			= p_course_cd AND
  			spo.prg_cal_type		= p_prg_cal_type AND
  			spo.prg_ci_sequence_number 	= p_prg_ci_sequence_number AND
  			spo.rule_check_dt 		IS NOT NULL AND
  			spo.rule_check_dt 		> p_rule_check_dt AND
  			spo.progression_rule_cat 	= p_progression_rule_cat AND
  			spo.pra_sequence_number 	= p_pra_sequence_number;

Line: 378

  		SELECT	sca.version_number
  		FROM	IGS_EN_STDNT_PS_ATT	sca
  		WHERE	sca.person_id	= p_person_id AND
  			sca.course_cd	= p_course_cd;

Line: 507

  		SELECT	sca.version_number
  		FROM	IGS_EN_STDNT_PS_ATT	sca
  		WHERE	sca.person_id	= p_person_id AND
  			sca.course_cd	= p_course_cd;

Line: 513

  		SELECT	MAX(daiv.alias_val)
  		FROM	IGS_CA_DA_INST_V	daiv
  		WHERE	daiv.dt_alias 		= cp_shw_cse_ctoff_dt_alias AND
  			daiv.cal_type 		= p_prg_cal_type AND
  			daiv.ci_sequence_number = p_prg_ci_sequence_number;

Line: 705

  		SELECT	sca.version_number
  		FROM	IGS_EN_STDNT_PS_ATT	sca
  		WHERE	sca.person_id	= p_person_id AND
  			sca.course_cd	= p_course_cd;

Line: 828

  		SELECT	sca.version_number
  		FROM	IGS_EN_STDNT_PS_ATT	sca
  		WHERE	sca.person_id	= p_person_id AND
  			sca.course_cd	= p_course_cd;

Line: 833

  		SELECT	MAX(daiv.alias_val)
  		FROM	IGS_CA_DA_INST_V	daiv
  		WHERE	daiv.dt_alias 		= cp_apl_ctoff_dt_alias AND
  			daiv.cal_type 		= p_prg_cal_type AND
  			daiv.ci_sequence_number = p_prg_ci_sequence_number;

Line: 1121

  	SELECT	cat.s_cal_cat,
  		cs.s_cal_status
  	FROM	IGS_CA_INST 		ci,
  		IGS_CA_TYPE		cat,
  		IGS_CA_STAT		cs
  	WHERE	cat.cal_type		= p_cal_type AND
  		ci.cal_type		= cat.cal_type AND
  		ci.sequence_number	= p_ci_sequence_number AND
  		cs.cal_status		= ci.cal_status;

Line: 1175

  	-- Validate insert of IGS_PR_STDNT_PR_OU record, validating for
  	-- related course attempt must have status of ENROLLED/INACTIVE/INTERMIT/
  	-- LAPSED / DISCONTIN
  	-- If related to IGS_PR_SDT_PR_RU_CK cannot be record against passed rule.
  	-- If related to student rule check, must be recorded against the latest
  	--check of a given rule within the IGS_PR_SDT_PR_RU_CK table.
  DECLARE
  	cst_enrolled	 CONSTANT	VARCHAR2(10) := 'ENROLLED';

Line: 1193

  		SELECT	course_attempt_status
  		FROM	IGS_EN_STDNT_PS_ATT 	sca
  		WHERE	person_id 		= p_person_id  AND
  			course_cd 		= p_course_cd;

Line: 1198

  		SELECT	passed_ind
  		FROM	IGS_PR_SDT_PR_RU_CK 	sprc
  		WHERE	person_id		= p_person_id AND
  			course_cd 		= p_course_cd AND
  			prg_cal_type		= p_prg_cal_type AND
  			prg_ci_sequence_number 	= p_prg_ci_sequence_number AND
  			rule_check_dt 		= p_rule_check_dt  AND
  			progression_rule_cat	= p_progression_rule_cat AND
  			pra_sequence_number 	= p_pra_sequence_number;

Line: 1208

  		SELECT 'X'
  		FROM	IGS_PR_SDT_PR_RU_CK 	sprc
  		WHERE	person_id 		= p_person_id AND
  			course_cd 		= p_course_cd AND
  			prg_cal_type 		= p_prg_cal_type AND
  			prg_ci_sequence_number 	= p_prg_ci_sequence_number AND
  			pra_sequence_number 	= p_pra_sequence_number AND
  			rule_check_dt 		> p_rule_check_dt;

Line: 1317

  		SELECT  s_progression_outcome_type
  		FROM    IGS_PR_OU_TYPE	pot
  		WHERE   pot.progression_outcome_type	= p_progression_outcome_type;

Line: 1393

  		SELECT 	'X'
  		FROM	IGS_PR_OU_TYPE 	pot,
  			IGS_FI_ENC_DFLT_EFT etde
  		WHERE	pot.progression_outcome_type 	= p_progression_outcome_type AND
  			pot.encumbrance_type		= etde.encumbrance_type  AND
  			etde.s_encmb_effect_type	= cst_rstr_at_ty;

Line: 1453

  		SELECT 	'X'
  		FROM	IGS_PR_OU_TYPE 	pot,
  			IGS_FI_ENC_DFLT_EFT		etde
  		WHERE	pot.progression_outcome_type 	= p_progression_outcome_type AND
  			pot.encumbrance_type		= etde.encumbrance_type  AND
  			etde.s_encmb_effect_type	= cst_exc_crs_gp;

Line: 1514

  		SELECT 	'X'
  		FROM	IGS_PR_OU_TYPE 	pot,
  			IGS_FI_ENC_DFLT_EFT		etde
  		WHERE	pot.progression_outcome_type 	= p_progression_outcome_type AND
  			pot.encumbrance_type		= etde.encumbrance_type  AND
  			etde.s_encmb_effect_type	IN (
  								cst_rstr_ge_cp,
  								cst_rstr_le_cp);

Line: 1587

  		SELECT	pro.apply_automatically_ind
  		FROM	IGS_PR_STDNT_PR_OU 	spo,
  			IGS_PR_RU_OU 	pro
  		WHERE	spo.person_id 			= p_person_id AND
  			spo.course_cd 			= p_course_cd AND
  			spo.sequence_number 		= p_sequence_number AND
  			pro.progression_rule_cat (+)		= spo.progression_rule_cat AND
  			pro.pra_sequence_number (+)	= spo.pro_pra_sequence_number AND
  			pro.sequence_number (+)		= spo.pro_sequence_number;

Line: 1661

  		SELECT	closed_ind
  		FROM	IGS_PS_GRP
  		WHERE	course_group_cd = cp_course_group_cd;

Line: 1744

  		SELECT	course_attempt_status
  		FROM	IGS_EN_STDNT_PS_ATT
  		WHERE	person_id	= p_person_id AND
  			course_cd	= p_course_cd;

Line: 1749

  		SELECT 	etde.s_encmb_effect_type
  		FROM	IGS_PR_OU_TYPE 	pot,
  			IGS_FI_ENC_DFLT_EFT		etde
  		WHERE	pot.progression_outcome_type 	= p_progression_outcome_type AND
  			pot. encumbrance_type		= etde.encumbrance_type;

Line: 1755

  		SELECT	'X'
  		FROM	IGS_PR_STDNT_PR_PS
  		WHERE	person_id 	 	= p_person_id AND
  			spo_course_cd	 	= p_course_cd AND
  			spo_sequence_number 	= p_sequence_number;

Line: 1761

  		SELECT	'X'
  		FROM	IGS_PR_SDT_PR_UNT_ST
  		WHERE	person_id 	 	= p_person_id AND
  			course_cd	 	= p_course_cd AND
  			spo_sequence_number 	= p_sequence_number;

Line: 1767

  		SELECT	'X'
  		FROM	IGS_PR_STDNT_PR_FND
  		WHERE	person_id 	 	= p_person_id AND
  			course_cd	 	= p_course_cd AND
  			spo_sequence_number 	= p_sequence_number;

Line: 1773

  		SELECT	'X'
  		FROM	IGS_PR_STDNT_PR_UNIT
  		WHERE	person_id 	 	= p_person_id AND
  			course_cd	 	= p_course_cd AND
  			spo_sequence_number 	= p_sequence_number AND
  			s_unit_type		= cst_excluded;

Line: 1780

  		SELECT	'X'
  		FROM	IGS_PR_STDNT_PR_UNIT
  		WHERE	person_id 	 	= p_person_id AND
  			course_cd	 	= p_course_cd AND
  			spo_sequence_number 	= p_sequence_number AND
  			s_unit_type		= cst_required;

Line: 1787

  		SELECT	pro.apply_automatically_ind
  		FROM	IGS_PR_STDNT_PR_OU 	spo,
  			IGS_PR_RU_OU 	pro
  		WHERE	spo.person_id 			= p_person_id AND
  			spo.course_cd 			= p_course_cd AND
  			spo.sequence_number 		= p_sequence_number AND
  			pro.progression_rule_cat (+)	= spo.progression_rule_cat AND
  			pro.pra_sequence_number (+)	= spo.pro_pra_sequence_number AND
  			pro.sequence_number (+)		= spo.pro_sequence_number;

Line: 2025

  		SELECT	sca.version_number
  		FROM	IGS_EN_STDNT_PS_ATT 	sca
  		WHERE	sca.person_id 		= p_person_id  AND
  			sca.course_cd 		= p_course_cd;

Line: 2146

  		SELECT	sca.version_number
  		FROM	IGS_EN_STDNT_PS_ATT	sca
  		WHERE	sca.person_id	= p_person_id AND
  			sca.course_cd	= p_course_cd;

Line: 2211

  		SELECT	sca.version_number
  		FROM	IGS_EN_STDNT_PS_ATT	sca
  		WHERE	sca.person_id	= p_person_id AND
  			sca.course_cd	= p_course_cd;