APPS.IGS_PS_VAL_UV SQL Statements

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

Line: 6

    sarakshi     12-Jul-2004     Bug#3729462, Added the DELETE_FLAG predicate in the cursor c_unit_offering_pattern of procedure crsp_val_uv_quality.
    ijeddy       03-nov-2003     Bug# 3181938; Modified this object as per Summary Measurement Of Attainment FD.

Line: 39

  	SELECT	closed_ind
  	FROM	IGS_PS_UNIT_LEVEL
  	WHERE	unit_level = p_unit_level AND
  		closed_ind = 'Y';

Line: 71

  	SELECT	closed_ind
  	FROM	IGS_LOOKUPS_VIEW
  	WHERE	lookup_code = p_credit_point_descriptor AND
  		closed_ind = 'Y'  AND
  		lookup_type = 'CREDIT_POINT_DSCR';

Line: 103

  	SELECT	closed_ind
  	FROM	IGS_PS_UNIT_INT_LVL
  	WHERE	unit_int_course_level_cd = p_unit_int_course_level_cd AND
  		closed_ind = 'Y';

Line: 136

  		SELECT s_unit_status
  		FROM	IGS_PS_UNIT_STAT
  		WHERE	unit_status = p_unit_status;

Line: 184

  		SELECT s_unit_status
  		FROM	IGS_PS_UNIT_STAT
  		WHERE	unit_status = p_unit_status;

Line: 188

  		SELECT 'x'
  		FROM	IGS_PS_UNIT_VER	uv,
  			IGS_PS_UNIT_STAT	us
  		WHERE	unit_cd		= p_unit_cd		AND
  			version_number 	<> p_version_number	AND
  			expiry_dt		IS NULL			AND
  			uv.unit_status	= us.unit_status		AND
  			us.s_unit_status	= 'ACTIVE';

Line: 390

  		SELECT	closed_ind
  		FROM	IGS_PS_UNIT_STAT
  		WHERE unit_status = p_new_unit_status;

Line: 394

  		SELECT	s_unit_status
  		FROM	IGS_PS_UNIT_STAT
  		WHERE	unit_status = p_new_unit_status;

Line: 398

  		SELECT	s_unit_status
  		FROM	IGS_PS_UNIT_STAT
  		WHERE unit_status = p_old_unit_status;

Line: 493

  	-- Perform a quality validation check on insert.
  	-- 	* Validate that all reference data is open and available for use
  	--	  for IGS_PS_UNIT_VER records (e.g IGS_PS_UNIT_LEVEL is not closed) and also
  	--	  for existing IGS_PS_UNIT_VER detail records such as:
  	-- 		IGS_PS_UNIT_DSCP,
  	-- 		IGS_PS_UNIT_CATEGORY,
  	-- 		IGS_PS_UNIT_LVL,
  	-- 		IGS_PS_UNIT_REF_CD.
  	-- 		If IGS_PS_UNIT version is altered from a system status of planned to
  	--		active then check:
  	-- 			IGS_PS_UNIT_OFR,
  	-- 			IGS_PS_UNIT_OFR_PAT,
  	-- 			IGS_PS_UNIT_OFR_OPT.
  	--	* Validate that where tables contains fields that hold percentages, that
  	--	  the records total 100% for the given IGS_PS_UNIT version. The relevant tables
  	--	  are:
  	-- 		IGS_PS_TCH_RESP,
  	-- 		IGS_PS_TCH_RESP_OVRD,
  	-- 		IGS_PS_UNIT_DSCP.
  	--	* Validate that all referenced organisational units are active.
  DECLARE
  	v_terminate		BOOLEAN := FALSE;

Line: 520

  		SELECT	*
  		FROM	IGS_PS_UNIT_VER
  		WHERE	unit_cd		= p_unit_cd	AND
  			version_number	= p_version_number;

Line: 525

  		SELECT	discipline_group_cd
  		FROM	IGS_PS_UNIT_DSCP
  		WHERE	unit_cd		= p_unit_cd	AND
  			version_number	= p_version_number;

Line: 530

  		SELECT	course_cd, course_version_number
  		FROM	igs_ps_unit_lvl
  		WHERE	unit_cd		= p_unit_cd	AND
  			version_number	= p_version_number;

Line: 535

  		SELECT	unit_cat
  		FROM	IGS_PS_UNIT_CATEGORY
  		WHERE	unit_cd		= p_unit_cd	AND
  			version_number	= p_version_number;

Line: 542

                SELECT  reference_cd_type
                FROM    IGS_PS_UNIT_REF_CD
                WHERE   unit_cd         = p_unit_cd     AND
                        version_number  = p_version_number;

Line: 547

  		SELECT	s_unit_status
  		FROM 	IGS_PS_UNIT_STAT
  		WHERE	unit_status	= p_old_unit_status;

Line: 551

  		SELECT	cal_type
  		FROM	IGS_PS_UNIT_OFR
  		WHERE	unit_cd		= p_unit_cd	AND
  			version_number	= p_version_number;

Line: 556

  		SELECT	cal_type,
  			ci_sequence_number
  		FROM	IGS_PS_UNIT_OFR_PAT
  		WHERE	unit_cd		= p_unit_cd	AND
  			version_number	= p_version_number
		AND     delete_flag = 'N';

Line: 563

  		SELECT	location_cd,
  			unit_class,
  			unit_contact
  		FROM	IGS_PS_UNIT_OFR_OPT
  		WHERE	unit_cd		= p_unit_cd	AND
  			version_number	= p_version_number;

Line: 570

  		SELECT	cal_type,
  			ci_sequence_number,
  			location_cd,
  			unit_class
  		FROM 	IGS_PS_UNIT_OFR_OPT
  		WHERE	unit_cd		= p_unit_cd AND
  			version_number	= p_version_number;

Line: 865

  		SELECT	DISTINCT sua.unit_attempt_status
  		FROM	IGS_EN_SU_ATTEMPT sua
  		WHERE	sua.unit_cd 		= p_unit_cd 		AND
  			sua.version_number 	= p_version_number 	AND
  			(sua.unit_attempt_status IN (cst_enrolled,cst_completed)	OR
  			(sua.unit_attempt_status = cst_discontin	AND
  			IGS_EN_GEN_007.ENRP_GET_SUA_INCUR (
  					sua.person_id,
  					sua.course_cd,
  					sua.unit_cd,
  					sua.version_number,
  					sua.cal_type,
  					sua.ci_sequence_number,
  					sua.unit_attempt_status,
  					sua.discontinued_dt,
  					sua.administrative_unit_status,
                    sua.uoo_id) = 'Y')) AND
  			(sua.override_enrolled_cp IS NOT NULL OR
  			sua.override_achievable_cp IS NOT NULL);

Line: 885

  		SELECT  sua.unit_attempt_status
  		FROM	IGS_EN_SU_ATTEMPT sua
  		WHERE	sua.unit_cd 		= p_unit_cd 			AND
  			sua.version_number 	= p_version_number 		AND
  			(sua.unit_attempt_status IN (cst_enrolled,cst_completed) OR
  			(sua.unit_attempt_status = cst_discontin		AND
  			IGS_EN_GEN_007.ENRP_GET_SUA_INCUR (
  					sua.person_id,
  					sua.course_cd,
  					sua.unit_cd,
  					sua.version_number,
  					sua.cal_type,
  					sua.ci_sequence_number,
  					sua.unit_attempt_status,
  					sua.discontinued_dt,
  					sua.administrative_unit_status,
                    sua.uoo_id) = 'Y')) AND
  			(p_points_min IS NULL OR
  			 NVL(sua.override_enrolled_cp,999999) < p_points_min OR
  			 NVL(sua.override_achievable_cp,999999) < p_points_min) AND
  			(p_points_max IS NULL OR
  			 NVL(sua.override_enrolled_cp,0) > p_points_max OR
  			 NVL(sua.override_achievable_cp,0) > p_points_max) AND
  			(p_points_increment IS NULL OR
  			MOD(NVL(sua.override_enrolled_cp,p_points_increment),
  			 		p_points_increment) <> 0.0 OR
  			MOD(NVL(sua.override_achievable_cp,p_points_increment),
  					p_points_increment) <> 0.0);

Line: 1043

  		SELECT 	'x'
  		FROM	IGS_EN_SU_ATTEMPT sua,
  			IGS_AS_SU_STMPTOUT suao,
  			IGS_AS_GRD_SCH_GRADE gsg
  		WHERE	sua.unit_cd 		= p_unit_cd AND
  			sua.version_number 	= p_version_number AND
  			sua.person_id 		= suao.person_id AND
  			sua.course_cd 		= suao.course_cd AND
  			sua.uoo_id 		= suao.uoo_id	AND
  			sua.unit_attempt_status = cst_discontin AND
  			suao.grading_schema_cd 	= gsg.grading_schema_cd AND
  			suao.grade 		= gsg.grade AND
  			suao.version_number 	= gsg.version_number AND
  			gsg.s_result_type 	= cst_pass AND
  			(p_points_min IS NULL 				OR
  		 	NVL(sua.override_enrolled_cp,999999) < p_points_min OR
  		 	NVL(sua.override_achievable_cp,999999) < p_points_min) AND
  			(p_points_max IS NULL 				OR
  		 	NVL(sua.override_enrolled_cp,0) > p_points_max 	OR
  		 	NVL(sua.override_achievable_cp,0) > p_points_max) AND
  			(p_points_increment IS NULL 			OR
  			MOD(NVL(sua.override_enrolled_cp,p_points_increment),
  		 	p_points_increment) <> 0.0 			OR
  			MOD(NVL(sua.override_achievable_cp,p_points_increment),
  			p_points_increment) <> 0.0) ;

Line: 1118

  		SELECT 	DISTINCT sua.unit_attempt_status
  		FROM 	IGS_EN_SU_ATTEMPT	sua
  		WHERE 	sua.unit_cd = p_unit_cd AND
  			sua.version_number = p_version_number AND
  			sua.unit_attempt_status in (cst_enrolled, cst_unconfirm, cst_invalid);

Line: 1175

  		SELECT	sua.unit_attempt_status
  		FROM 	IGS_EN_SU_ATTEMPT 	sua
  		WHERE	sua.unit_cd = p_unit_cd AND
  			sua.version_number = p_version_number AND
  			sua.unit_attempt_status IN (
  						cst_enrolled,
  						cst_completed,
  						cst_discontin) AND
  			sua.alternative_title IS NOT NULL;

Line: 1246

  SELECT
	uv.enrolled_credit_points,
	uv.billing_hrs,
	uv.billing_credit_points
  FROM
	IGS_PS_UNIT_VER uv,
	IGS_PS_UNIT_OFR_OPT uoo
  WHERE
	uoo.uoo_id = cp_uoo_id AND
	uoo.unit_cd = uv.unit_cd AND
	uoo.version_number = uv.version_number;

Line: 1260

  SELECT
	us.enrolled_credit_points,
	us.billing_hrs,
	us.billing_credit_points
  FROM
	IGS_PS_USEC_CPS  us
  WHERE
	us.uoo_id = cp_uoo_id;