APPS.IGS_RE_VAL_TPM SQL Statements

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

Line: 26

  		SELECT	tpt.recommended_panel_size
  		FROM	IGS_RE_THS_PNL_TYPE tpt
  		WHERE	thesis_panel_type = p_thesis_panel_type;

Line: 30

  		SELECT	count('x')
  		FROM	IGS_RE_THS_PNL_MBR tpm
  		WHERE	ca_person_id	= p_person_id AND
  			ca_sequence_number = p_ca_sequence_number AND
  			the_sequence_number = p_the_sequence_number AND
  			creation_dt = p_creation_dt AND
  			confirmed_dt IS NOT NULL;

Line: 40

  	--1. Select panel size from thesis panel type
  	OPEN c_tpt;

Line: 99

  		SELECT	'X'
  		FROM	IGS_RE_THS_PNL_MBR		tpm,
  			IGS_RE_THS_PNL_MR_TP	tpmpt
  		WHERE	tpm.ca_person_id		= p_ca_person_id AND
  			tpm.ca_sequence_number		= p_ca_sequence_number AND
  			tpm.the_sequence_number		= p_the_sequence_number AND
  			tpm.creation_dt			= p_creation_dt AND
  			tpm.confirmed_dt IS NOT NULL AND
  			tpmpt.panel_member_type 	= tpm.panel_member_type AND
  			tpmpt.panel_chair_ind 		= 'Y';

Line: 277

  		SELECT 	'x'
  		FROM	IGS_EN_STDNT_PS_ATT sca
  		WHERE	sca.person_id 			= p_person_id AND
  			sca.course_attempt_status 	IN (
  							cst_enrolled,
  							cst_inactive,
  							cst_lapsed,
  							cst_intermit);

Line: 286

  		SELECT 	'x'
  		FROM	IGS_RE_SPRVSR rsup
  		WHERE	rsup.ca_person_id 	= p_ca_person_id 	AND
  			rsup.ca_sequence_number = p_ca_sequence_number 	AND
  			rsup.person_id 		= p_person_id 		AND
  			rsup.start_dt 		<= SYSDATE 		AND
  			(rsup.end_dt 		IS NULL 		OR
   			rsup.end_dt 		> SYSDATE);

Line: 373

  		SELECT	tex.submission_dt
  		FROM	IGS_RE_THESIS_EXAM		tex
  		WHERE	tex.person_id		= p_ca_person_id AND
  			tex.ca_sequence_number	= p_ca_sequence_number AND
  			tex.the_sequence_number	= p_the_sequence_number AND
  			tex.creation_dt 	= p_creation_dt;

Line: 380

  		SELECT	thr.closed_ind
  		FROM	IGS_RE_THESIS_RESULT 		thr
  		WHERE	thr.thesis_result_cd 	= p_thesis_result_cd;

Line: 445

  		SELECT	tpmt.closed_ind
  		FROM 	IGS_RE_THS_PNL_MR_TP 	tpmt
  		WHERE	tpmt.panel_member_type 		= p_panel_member_type;

Line: 492

  	-- Validate whether IGS_RE_THS_PNL_MBR update transactions are possible
  	-- (insert,update and delete), checking for :
  	-- Can only insert or delete records when parent
  	-- thesis_examination.thesis_result_cd is not set
  	-- Cannot update thesis_result_cd, panel_member_type, confirmed_dt if parent
  	-- examination result has been entered.
  DECLARE
  	cst_insert	CONSTANT	VARCHAR2(10) := 'INSERT';

Line: 500

  	cst_delete	CONSTANT	VARCHAR2(10) := 'DELETE';

Line: 501

  	cst_update	CONSTANT	VARCHAR2(10) := 'UPDATE';

Line: 504

  		SELECT	tex.thesis_result_cd
  		FROM	IGS_RE_THESIS_EXAM tex
  		WHERE	tex.person_id 		= p_ca_person_id 	AND
  			tex.ca_sequence_number 	= p_ca_sequence_number	AND
  			tex.the_sequence_number = p_the_sequence_number AND
  			tex.creation_dt 	= p_creation_dt;

Line: 521

  	IF p_transaction_type IN (cst_insert, cst_delete) THEN
  		-- 2. If the parent result code is set then insert and delete
  		-- are not possible.
  		IF v_thesis_result_cd IS NOT NULL THEN
  			p_message_name := 'IGS_RE_CANT_ID_PANEL_MEMBERS';

Line: 529

  	IF p_transaction_type = cst_update THEN
  		-- 3. If specified fields have changed and the parent result has
  		-- been entered then fail.
  		IF v_thesis_result_cd IS NOT NULL THEN
  			IF NVL(p_old_thesis_result_cd, 'NULL') <>
  					NVL(p_new_thesis_result_cd, 'NULL') OR
  		    			NVL(p_old_panel_member_type, 'NULL') <>
  					NVL(p_new_panel_member_type, 'NULL') OR
  		    			NVL(p_old_confirmed_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
  					NVL(p_new_confirmed_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
  				p_message_name := 'IGS_RE_CANT_UPD_PAN_MEM_TYPE';