APPS.IGS_PS_GEN_005 SQL Statements

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

Line: 18

	-- This module will delete the history records associated with a
	-- IGS_PS_TCH_RESP_OVRD record.
DECLARE
	CURSOR	c_troh IS
		SELECT	Rowid,unit_cd
		FROM	IGS_PS_TCH_RSOV_HIST troh
		WHERE	troh.unit_cd		= p_unit_cd		AND
			troh.version_number	= p_version_number	AND
			troh.cal_type		= p_cal_type		AND
			troh.ci_sequence_number = p_ci_sequence_number	AND
			troh.location_cd	= p_location_cd		AND
			troh.unit_class		= p_unit_class		AND
			troh.org_unit_cd	= p_org_unit_cd		AND
			troh.ou_start_dt	= p_ou_start_dt
		FOR UPDATE OF unit_cd NOWAIT;

Line: 37

		-- Delete the current record.

		IGS_PS_TCH_RSOV_HIST_PKG.Delete_Row(X_ROWID => v_troh_rec.Rowid);

Line: 47

	-- select set, then want to handle the exception by returning false and
	-- an error message from this routine.
	WHEN e_resource_busy_exception THEN
		IF (c_troh%ISOPEN) THEN
			CLOSE c_troh;

Line: 53

		p_message_name := 'IGS_PS_UNABLE_TO_DELETE';

Line: 86

	v_rec_inserted_cnt	NUMBER(5)	DEFAULT 0;

Line: 88

		SELECT	max(effective_start_dt)
		FROM	IGS_PS_ANL_LOAD
		WHERE	course_cd	= p_course_cd		AND
			version_number	= p_version_number	AND
			yr_num		= p_yr_num 	AND
			effective_start_dt < p_effective_start_dt;

Line: 95

		SELECT	calul.unit_cd,
			calul.uv_version_number,
			us.s_unit_status
		FROM	IGS_PS_ANL_LOAD_U_LN	calul,
			IGS_PS_UNIT_VER			uv,
			IGS_PS_UNIT_STAT			us
		WHERE	calul.course_cd		 = p_course_cd	AND
			calul.crv_version_number = p_version_number	 AND
			calul.yr_num		 = p_yr_num		 AND
			calul.effective_start_dt = cp_effective_start_dt AND
			calul.unit_cd 		 = uv.unit_cd		 AND
			calul.uv_version_number	 = uv.version_number	 AND
			uv.unit_status		 = us.unit_status;

Line: 118

	v_rec_inserted_cnt := 0;

Line: 119

	-- loop through all selected IGS_PS_UNIT links, insert IGS_PS_COURSE annual load passed in.
	OPEN c_calul(v_effective_start_dt);

Line: 126

		-- Do not insert records with system status of 'INACTIVE'
		IF (v_s_unit_status <> 'INACTIVE') THEN
			v_rec_inserted_cnt := v_rec_inserted_cnt + 1;

Line: 130

			IGS_PS_ANL_LOAD_U_LN_PKG.Insert_Row(
							 X_ROWID    		=>	x_rowid,
							 X_COURSE_CD            =>	p_course_cd,
							 X_CRV_VERSION_NUMBER   =>	p_version_number,
							 X_EFFECTIVE_START_DT   =>	p_effective_start_dt,
							 X_YR_NUM               =>	p_yr_num,
							 X_UV_VERSION_NUMBER    =>	v_uv_version_number,
							 X_UNIT_CD              =>	v_unit_cd,
							 X_MODE                 =>	'R');

Line: 142

	-- no IGS_PS_ANL_LOAD_U_LN record inserted
	IF (v_rec_inserted_cnt = 0) THEN
		p_message_name := 'IGS_PS_NOPRG_ANNUAL_LOADLINKS';

Line: 145

	-- all IGS_PS_ANL_LOAD_U_LN records inserted
	ELSIF (v_rec_inserted_cnt = c_calul%ROWCOUNT) THEN
		p_message_name := 'IGS_PS_SUCCESS_COPY_PRGANNUAL';

Line: 148

	-- some IGS_PS_ANL_LOAD_U_LN record inserted
	ELSE
		p_message_name := 'IGS_PS_SOME_PRGANNUAL_LOADLIN';

Line: 175

  p_new_update_who IN VARCHAR2 ,
  p_old_update_who IN VARCHAR2 ,
  p_new_update_on IN DATE ,
  p_old_update_on IN DATE )
AS
BEGIN	-- crsp_ins_tro_hist
	-- Insert a teaching_responsibility_ovrd_hist record.
DECLARE
	v_hist_start_dt		IGS_PS_TCH_RSOV_HIST.hist_start_dt%TYPE;

Line: 190

		v_hist_start_dt	:= p_old_update_on;

Line: 191

		v_hist_end_dt 	:= p_new_update_on;

Line: 192

		v_hist_who 	:= p_old_update_who;

Line: 194

		IGS_PS_TCH_RSOV_HIST_PKG.Insert_Row(
					X_ROWID              =>	      x_rowid,
					X_UNIT_CD            =>  	p_unit_cd,
					X_CAL_TYPE           =>  	p_cal_type,
					X_CI_SEQUENCE_NUMBER =>  	p_ci_sequence_number,
					X_VERSION_NUMBER     =>  	p_version_number,
					X_LOCATION_CD        =>  	p_location_cd,
					X_ORG_UNIT_CD        =>  	p_org_unit_cd,
					X_HIST_START_DT      =>  	v_hist_start_dt,
					X_OU_START_DT        =>  	p_ou_start_dt,
					X_UNIT_CLASS         =>  	p_unit_class,
					X_HIST_END_DT        =>  	v_hist_end_dt,
					X_HIST_WHO           =>  	v_hist_who,
					X_PERCENTAGE         =>  	p_old_percentage,
					X_MODE               =>		'R',
					X_ORG_ID	     =>		l_org_id);

Line: 225

  p_last_update_on IN DATE ,
  p_update_on IN DATE ,
  p_last_update_who IN VARCHAR2 ,
  p_percentage IN NUMBER )
AS
	CURSOR	c_unit_status(
			cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
			cp_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
		SELECT	IGS_PS_UNIT_STAT.s_unit_status
		FROM	IGS_PS_UNIT_STAT,IGS_PS_UNIT_VER
		WHERE	IGS_PS_UNIT_VER.unit_cd = cp_unit_cd AND
			IGS_PS_UNIT_VER.version_number = cp_version_number AND
			IGS_PS_UNIT_STAT.unit_status = IGS_PS_UNIT_VER.unit_status;

Line: 251

		IGS_PS_TCH_RESP_HIST_PKG.Insert_Row(
						X_ROWID            =>   	x_rowid,
						X_UNIT_CD          =>  		p_unit_cd,
						X_VERSION_NUMBER	 =>   	p_version_number,
						X_OU_START_DT      =>    	p_ou_start_dt,
						X_HIST_START_DT    =>    	p_last_update_on,
						X_ORG_UNIT_CD      =>    	p_org_unit_cd,
						X_HIST_END_DT      =>    	p_update_on,
						X_HIST_WHO         =>    	p_last_update_who,
						X_PERCENTAGE       =>    	p_percentage,
						X_MODE             =>		'R',
						X_ORG_ID	   =>		l_org_id);

Line: 276

  p_last_update_on IN DATE ,
  p_update_on IN DATE ,
  p_last_update_who IN VARCHAR2 ,
  p_percentage IN NUMBER )
AS
	CURSOR	c_unit_status(
			cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
			cp_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
		SELECT	IGS_PS_UNIT_STAT.s_unit_status
		FROM	IGS_PS_UNIT_STAT,IGS_PS_UNIT_VER
		WHERE	IGS_PS_UNIT_VER.unit_cd = cp_unit_cd AND
			IGS_PS_UNIT_VER.version_number = cp_version_number AND
			IGS_PS_UNIT_STAT.unit_status = IGS_PS_UNIT_VER.unit_status;

Line: 303

			IGS_PS_UNT_DSCP_HIST_PKG.Insert_Row(
						X_ROWID                =>	x_rowid,
						X_UNIT_CD              =>	p_unit_cd,
						X_HIST_START_DT        =>	p_last_update_on,
						X_DISCIPLINE_GROUP_CD  =>	p_discipline_group_cd,
						X_VERSION_NUMBER       =>	p_version_number,
						X_HIST_END_DT          =>	p_update_on,
						X_HIST_WHO             =>	p_last_update_who,
						X_PERCENTAGE           =>	p_percentage,
						X_MODE                 =>	'R',
						X_ORG_ID	       =>	l_org_id);

Line: 328

  p_last_update_on IN DATE ,
  p_update_on IN DATE ,
  p_last_update_who IN VARCHAR2 ,
  p_description IN VARCHAR2 )
AS
	CURSOR	c_unit_status(
			cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
			cp_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
		SELECT	IGS_PS_UNIT_STAT.s_unit_status
		FROM	IGS_PS_UNIT_STAT,IGS_PS_UNIT_VER
		WHERE	IGS_PS_UNIT_VER.unit_cd = cp_unit_cd AND
			IGS_PS_UNIT_VER.version_number = cp_version_number AND
			IGS_PS_UNIT_STAT.unit_status = IGS_PS_UNIT_VER.unit_status;

Line: 353

		IGS_PS_UNIT_REF_HIST_PKG.Insert_Row(
					X_ROWID              =>	      x_rowid,
					X_UNIT_CD            =>  	p_unit_cd,
					X_HIST_START_DT      =>  	p_last_update_on,
					X_REFERENCE_CD       =>  	p_reference_cd,
					X_VERSION_NUMBER     =>  	p_version_number,
					X_REFERENCE_CD_TYPE  =>  	p_reference_cd_type,
					X_HIST_END_DT        =>  	p_update_on,
					X_HIST_WHO           =>  	p_last_update_who,
					X_DESCRIPTION        =>  	p_description,
					X_MODE               =>		'R',
					X_ORG_ID	     =>		l_org_id);