APPS.IGS_EN_VAL_SCHO SQL Statements

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

Line: 25

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

Line: 47

  	IF NOT IGS_EN_VAL_SCHO.enrp_val_scho_delete(
  					p_start_dt,
  					v_message_name) THEN
  		p_message_name := v_message_name;

Line: 78

  p_diff_hecs_ind_update_who IN VARCHAR2 ,
  p_diff_hecs_ind_update_on IN DATE ,
  p_diff_hecs_ind_update_comment IN VARCHAR2 ,
  p_outside_aus_res_ind IN VARCHAR2 ,
  p_nz_citizen_ind IN VARCHAR2 ,
  p_nz_citizen_less2yr_ind IN VARCHAR2 ,
  p_nz_citizen_not_res_ind IN VARCHAR2 ,
  p_safety_net_ind IN VARCHAR2 ,
  p_tax_file_number IN NUMBER ,
  p_tax_file_number_collected_dt IN DATE ,
  p_tax_file_invalid_dt IN DATE ,
  p_tax_file_certificate_number IN NUMBER ,
  p_message_name OUT NOCOPY VARCHAR2)
  RETURN boolean  AS
   BEGIN	-- enrp_val_scho_all
  	-- Perform all validations associated with the addition of a
  	-- IGS_EN_STDNTPSHECSOP record. This routine is typically called by
  	-- processes which are defaulting or HECS records and need all validations
  	-- to be performed.
  	-- The routine will return a single error, being the first one encountered.
  DECLARE
  	v_message_name		varchar2(30);

Line: 438

  		SELECT	IGS_EN_STDNTPSHECSOP.end_dt
  		FROM	IGS_EN_STDNTPSHECSOP
  		WHERE	IGS_EN_STDNTPSHECSOP.person_id = cp_person_id AND
  			IGS_EN_STDNTPSHECSOP.course_cd = cp_course_cd AND
  			IGS_EN_STDNTPSHECSOP.start_dt <> cp_start_dt;

Line: 478

    		SELECT	scho.start_dt,
    			scho.end_dt
    		FROM	IGS_EN_STDNTPSHECSOP	scho
    		WHERE	scho.person_id 	= p_person_id AND
    			scho.course_cd 	= p_course_cd AND
    			scho.start_dt 	<> p_start_dt;

Line: 486

    	-- being created or updated does not overlap with an existing record
    	-- for the nominated person
    	p_message_name := null;

Line: 657

                       SELECT  *
                       FROM    IGS_EN_STDNTPSHECSOP
                       WHERE   person_id <> p_person_id AND
                               tax_file_number = p_tax_file_number;

Line: 662

                       SELECT  *
                       FROM    IGS_EN_STDNTPSHECSOP
                       WHERE   person_id = p_person_id AND
  		tax_file_number is NOT NULL AND
                               tax_file_number <> p_tax_file_number;

Line: 740

  	-- Eliminate the record being updated/inserted.
  	  IF	(v_scho_same_rec.course_cd = p_course_cd AND
  		 v_scho_same_rec.start_dt <> p_start_dt) OR
  		(v_scho_same_rec.course_cd <> p_course_cd) THEN
  	                     v_count1 := v_count1 + 1;

Line: 783

  		SELECT  ghpo.s_hecs_payment_type
  		FROM	IGS_FI_HECS_PAY_OPTN		hpo,
  			IGS_FI_GOV_HEC_PA_OP	ghpo
  		WHERE	hpo.hecs_payment_option		= p_hecs_payment_option AND
  			ghpo.govt_hecs_payment_option	= hpo.govt_hecs_payment_option;

Line: 926

  FUNCTION enrp_val_scho_insert(
  p_person_id IN NUMBER ,
  p_course_cd IN VARCHAR2 ,
  p_message_name OUT NOCOPY VARCHAR2)
  RETURN BOOLEAN  AS
  BEGIN
  DECLARE
  	v_other_detail			VARCHAR2(255);

Line: 936

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

Line: 941

  	-- This module validates the insertion of a
  	-- IGS_EN_STDNT_PS_HECS_OPTION_V record, in which
  	-- it can't be inserted if it has a status of
  	-- completed or deleted.
  	p_message_name := null;

Line: 957

  	-- to deleted or completed
  	IF (v_course_attempt_status = cst_deleted OR
              v_course_attempt_status = cst_completed) THEN
  		p_message_name := 'IGS_EN_CANT_CREATE_HECS';

Line: 967

		FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_insert');

Line: 971

  END enrp_val_scho_insert;

Line: 974

  FUNCTION enrp_val_scho_update(
  p_start_dt IN DATE ,
  p_message_name OUT NOCOPY VARCHAR2)
  RETURN BOOLEAN  AS
  BEGIN
  DECLARE

  	v_diff			NUMBER;

Line: 984

  	-- record may be updated.  It may only be updated if
  	-- the IGS_EN_STDNTPSHECSOP.start_dt is equal or greater
  	-- than the current date.
  	v_diff := MONTHS_BETWEEN(SYSDATE, p_start_dt);

Line: 1000

		FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_update');

Line: 1004

  END enrp_val_scho_update;

Line: 1007

  FUNCTION enrp_val_scho_delete(
  p_start_dt IN DATE ,
  p_message_name OUT NOCOPY VARCHAR2)
  RETURN BOOLEAN  AS
  BEGIN
  DECLARE

  	v_diff			NUMBER;

Line: 1017

  	-- record may be deleted.  It may only be deleted if
  	-- the IGS_EN_STDNTPSHECSOP.start_dt is equal or greater
  	-- than the current date.
  	v_diff := MONTHS_BETWEEN(SYSDATE, p_start_dt);

Line: 1033

		FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCHO.enrp_val_scho_delete');

Line: 1037

  END enrp_val_scho_delete;

Line: 1063

  		SELECT	govt_hecs_payment_option
  		FROM	IGS_FI_HECS_PAY_OPTN
  		WHERE	hecs_payment_option = p_hecs_payment_option AND
  			govt_hecs_payment_option IS NOT NULL;

Line: 1068

  		SELECT	PS.start_dt,
  			PS.end_dt,
  			CIT.govt_citizenship_cd,
  			PRCD.govt_perm_resident_cd
  		FROM	IGS_PE_STATISTICS PS,
  			IGS_ST_CITIZENSHP_CD CIT,
  			IGS_PE_PERM_RES_CD PRCD
  		WHERE	PS.person_id = p_person_id AND
  			PS.citizenship_cd = CIT.citizenship_cd AND
  			PS.perm_resident_cd = PRCD.perm_resident_cd (+)
  		ORDER BY PS.start_dt,
  			 PS.end_dt;

Line: 1081

  		SELECT	govt_citizenship_cd
  		FROM	IGS_ST_CITIZENSHP_CD
  		WHERE	citizenship_cd = p_citizenship_cd;

Line: 1085

  		SELECT	govt_perm_resident_cd
  		FROM	IGS_PE_PERM_RES_CD
  		WHERE	perm_resident_cd = p_perm_resident_cd;

Line: 1089

  		SELECT	SCHOV.start_dt,
  			SCHOV.end_dt,
  			SCHOV.outside_aus_res_ind,
  			HPO.govt_hecs_payment_option
  		FROM	IGS_EN_STDNT_PS_HECS_OPTION_V SCHOV,
  			IGS_FI_HECS_PAY_OPTN HPO
  		WHERE	SCHOV.person_id = p_person_id AND
  			SCHOV.hecs_payment_option =
  				HPO.hecs_payment_option AND
  			HPO.govt_hecs_payment_option IS NOT NULL
  		ORDER BY SCHOV.start_dt,
  			 SCHOV.end_dt;

Line: 1310

  		SELECT  prc.govt_perm_resident_cd
  		FROM	IGS_PE_PERM_RES_CD	prc
  		WHERE	prc.perm_resident_cd = p_perm_resident_cd;

Line: 1314

  		SELECT   ps.start_dt,
  			 ps.end_dt,
  			 cic.govt_citizenship_cd,
  			 prcd.govt_perm_resident_cd
  		FROM	 IGS_PE_STATISTICS 	ps,
  			 IGS_ST_CITIZENSHP_CD 	cic,
  			 IGS_PE_PERM_RES_CD 	prcd
  		WHERE	 ps.person_id 		= p_person_id AND
  			 ps.citizenship_cd 	= cic.citizenship_cd AND
  			 ps.perm_resident_cd 	= prcd.perm_resident_cd (+)
  		ORDER BY ps.start_dt,
  			 ps.end_dt;

Line: 1327

  		SELECT  cic.govt_citizenship_cd
  		FROM	IGS_ST_CITIZENSHP_CD	cic
  		WHERE	cic.citizenship_cd = p_citizenship_cd;

Line: 1331

  		SELECT  schov.start_dt,
  			schov.end_dt,
  			schov.outside_aus_res_ind,
  			schov.nz_citizen_ind,
  			schov.nz_citizen_less2yr_ind,
  			schov.nz_citizen_not_res_ind
  		FROM	IGS_EN_STDNT_PS_HECS_OPTION_V 	schov
  		WHERE	schov.person_id = p_person_id
  		ORDER BY
  			schov.start_dt,
  			schov.end_dt;

Line: 1553

  		SELECT	hpo.govt_hecs_payment_option
  		FROM	IGS_FI_HECS_PAY_OPTN	hpo
  		WHERE	hpo.hecs_payment_option = p_hecs_payment_option;

Line: 1557

  		SELECT	cty.govt_course_type
  		FROM	IGS_EN_STDNT_PS_ATT	sca,
  			IGS_PS_VER		crv,
  			IGS_PS_TYPE		cty
  		WHERE	sca.person_id = p_person_id AND
  			sca.course_cd = p_course_cd AND
  			crv.course_cd = sca.course_cd AND
  			crv.version_number = sca.version_number AND
  			crv.course_type = cty.course_type;

Line: 1712

  		SELECT  hpo.govt_hecs_payment_option
  		FROM	IGS_FI_HECS_PAY_OPTN	hpo
  		WHERE	hpo.hecs_payment_option = p_hecs_payment_option;

Line: 1716

  		SELECT  crv.govt_special_course_type
  		FROM	IGS_EN_STDNT_PS_ATT	sca,
  			IGS_PS_VER		crv
  		WHERE	sca.person_id 		= p_person_id AND
  			sca.course_cd 		= p_course_cd AND
  			crv.course_cd 		= sca.course_cd AND
  			crv.version_number 	= sca.version_number;

Line: 1724

  		SELECT 	'x'
  		FROM 	IGS_OR_INSTITUTION		ins,
  			IGS_OR_INST_STAT	ist
  		WHERE  	ins.local_institution_ind 	= 'Y' AND
  			ins.govt_institution_cd 	= 2239 AND
  			ist.institution_status 		= ins.institution_status AND
  			ist.s_institution_status 	= 'ACTIVE';

Line: 1827

  		SELECT  hpo.govt_hecs_payment_option
  		FROM	IGS_FI_HECS_PAY_OPTN		hpo
  		WHERE	hpo.hecs_payment_option 	= p_hecs_payment_option AND
  			hpo.govt_hecs_payment_option	IS NOT NULL;

Line: 1832

  		SELECT   ps.start_dt,
  			 ps.end_dt,
  			 cic.govt_citizenship_cd,
  			 ps.yr_arrival
  		FROM	 IGS_PE_STATISTICS 	ps,
  			 IGS_ST_CITIZENSHP_CD 	cic
  		WHERE	 ps.person_id 		= p_person_id AND
  			 ps.citizenship_cd 	= cic.citizenship_cd(+)
  		ORDER BY ps.start_dt,
  			 ps.end_dt;

Line: 1843

  		SELECT  gcc.govt_citizenship_cd
  		FROM	IGS_ST_CITIZENSHP_CD	gcc
  		WHERE	gcc.citizenship_cd 	= p_citizenship_cd;

Line: 1847

  		SELECT  scho.start_dt,
  			scho.end_dt,
  			scho.outside_aus_res_ind,
  			scho.nz_citizen_ind,
  			scho.nz_citizen_less2yr_ind,
  			scho.nz_citizen_not_res_ind,
  		 	hpo.govt_hecs_payment_option
  		FROM	IGS_EN_STDNT_PS_HECS_OPTION_V 	scho,
  			IGS_FI_HECS_PAY_OPTN 		hpo
  		WHERE	scho.person_id 			= p_person_id AND
  			scho.hecs_payment_option 	= hpo.hecs_payment_option  AND
  			hpo.govt_hecs_payment_option 	IS NOT NULL
  		ORDER BY scho.start_dt,
  			 scho.end_dt;

Line: 2125

  		SELECT govt_hecs_payment_option
  		FROM	IGS_FI_HECS_PAY_OPTN
  		WHERE	hecs_payment_option = p_hecs_payment_option AND
  			govt_hecs_payment_option IS NOT NULL;

Line: 2130

  		SELECT PS.start_dt,
  			 PS.end_dt,
  			 CIT.govt_citizenship_cd,
  			 PRCD.govt_perm_resident_cd
  		FROM	 IGS_PE_STATISTICS PS,
  			 IGS_ST_CITIZENSHP_CD CIT,
  			 IGS_PE_PERM_RES_CD PRCD
  		WHERE	 PS.person_id = p_person_id AND
  			 PS.citizenship_cd = CIT.citizenship_cd AND
  			 PS.perm_resident_cd = PRCD.perm_resident_cd (+)
  		ORDER BY PS.start_dt,
  			 PS.end_dt;

Line: 2143

  		SELECT	govt_citizenship_cd
  		FROM	IGS_ST_CITIZENSHP_CD
  		WHERE	citizenship_cd = p_citizenship_cd;

Line: 2147

  		SELECT	govt_perm_resident_cd
  		FROM	IGS_PE_PERM_RES_CD
  		WHERE	perm_resident_cd = p_perm_resident_cd;

Line: 2151

  		SELECT	SCHOV.start_dt,
  			 SCHOV.end_dt,
  			 HPO.govt_hecs_payment_option
  		FROM	 IGS_EN_STDNT_PS_HECS_OPTION_V SCHOV,
  			 IGS_FI_HECS_PAY_OPTN HPO
  		WHERE	 SCHOV.person_id = p_person_id AND
  			 SCHOV.hecs_payment_option = HPO.hecs_payment_option AND
  			HPO.govt_hecs_payment_option IS NOT NULL
  		ORDER BY SCHOV.start_dt,
  			 SCHOV.end_dt;

Line: 2408

  		SELECT 	hpo.govt_hecs_payment_option
  		FROM	IGS_FI_HECS_PAY_OPTN	hpo
  		WHERE	hpo.hecs_payment_option = p_hecs_payment_option;

Line: 2412

  		SELECT	ct.govt_course_type
  		FROM	IGS_EN_STDNT_PS_ATT	sca,
  			IGS_PS_VER		cv,
  			IGS_PS_TYPE		ct
  		WHERE	sca.person_id		= p_person_id		AND
  			sca.course_cd		= p_course_cd		AND
  			cv.course_cd		= sca.course_cd		AND
  			cv.version_number	= sca.version_number	AND
  			cv.course_type		= ct.course_type;

Line: 2422

  		SELECT	ps.start_dt,
  			ps.end_dt,
  			cc.govt_citizenship_cd
  		FROM	IGS_PE_STATISTICS	ps,
  			IGS_ST_CITIZENSHP_CD		cc
  		WHERE	ps.person_id		= p_person_id	AND
  			ps.citizenship_cd	= cc.citizenship_cd
  		ORDER BY
  			start_dt,
  			end_dt;

Line: 2433

  		SELECT	scho.start_dt,
  			scho.end_dt,
  			hpo.govt_hecs_payment_option,
  			ct.govt_course_type
  		FROM	IGS_EN_STDNT_PS_HECS_OPTION_V	scho,
  			IGS_FI_HECS_PAY_OPTN		hpo,
  			IGS_EN_STDNT_PS_ATT		sca,
  			IGS_PS_VER			cv,
  			IGS_PS_TYPE			ct
  		WHERE	scho.person_id			= p_person_id			AND
  			scho.hecs_payment_option	= hpo.hecs_payment_option	AND
  			sca.person_id			= scho.person_id		AND
  			sca.course_cd			= scho.course_cd		AND
  			cv.course_cd			= sca.course_cd			AND
  			cv.version_number		= sca.version_number		AND
  			cv.course_type			= ct.course_type;

Line: 2450

  		SELECT	cic.govt_citizenship_cd
  		FROM	IGS_ST_CITIZENSHP_CD	cic
  		WHERE	cic.citizenship_cd = p_citizenship_cd;

Line: 2825

  		SELECT 	ins.govt_institution_cd
  		FROM 	IGS_OR_INSTITUTION		ins,
  			IGS_OR_INST_STAT	ist
  		WHERE  	ins.local_institution_ind 	= 'Y' AND
  			ist.institution_status		= ins.institution_status AND
  			ist.s_institution_status 	= 'ACTIVE';

Line: 2832

  		SELECT	hpo.govt_hecs_payment_option
  		FROM	IGS_FI_HECS_PAY_OPTN	hpo
  		WHERE	hpo.hecs_payment_option 	= p_hecs_payment_option AND
  			hpo.govt_hecs_payment_option 	IS NOT NULL;

Line: 2837

  		SELECT	ps.start_dt,
  			ps.end_dt,
  			cic.govt_citizenship_cd
  		FROM	IGS_PE_STATISTICS	ps,
  			IGS_ST_CITIZENSHP_CD		cic
  		WHERE	ps.person_id 		= p_person_id AND
  			cic.citizenship_cd 	= ps.citizenship_cd
  		ORDER BY
  			ps.start_dt,
  			ps.end_dt;

Line: 2848

  		SELECT	schov.start_dt,
  			schov.end_dt,
  			hpo.govt_hecs_payment_option
  		FROM	IGS_EN_STDNT_PS_HECS_OPTION_V	schov,
  			IGS_FI_HECS_PAY_OPTN		hpo
  		WHERE	schov.person_id 		= p_person_id AND
  			schov.hecs_payment_option	= hpo.hecs_payment_option AND
  			hpo.govt_hecs_payment_option 	IS NOT NULL
  		ORDER BY
  			schov.start_dt,
  			schov.end_dt;

Line: 2860

  		SELECT		govt_citizenship_cd
  		FROM		IGS_ST_CITIZENSHP_CD
  		WHERE		citizenship_cd = p_citizenship_cd;

Line: 3002

  		SELECT	IGS_FI_HECS_PAY_OPTN.closed_ind
  		FROM	IGS_FI_HECS_PAY_OPTN
  		WHERE	IGS_FI_HECS_PAY_OPTN.hecs_payment_option = cp_hecs_payment_option;

Line: 3059

  		SELECT	hpo.govt_hecs_payment_option
  		FROM	IGS_FI_HECS_PAY_OPTN	hpo
  		WHERE	hpo.hecs_payment_option = p_hecs_payment_option;

Line: 3063

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

Line: 3068

  		SELECT	ps.start_dt,
  			ps.end_dt,
  			ps.yr_arrival,
  			ps.term_location_country,
  			ps.term_location_postcode,
  			cc.govt_citizenship_cd
  		FROM	IGS_PE_STATISTICS	ps,
  			IGS_ST_CITIZENSHP_CD		cc
  		WHERE	ps.person_id	= p_person_id	AND
  			ps.citizenship_cd = cc.citizenship_cd
  		ORDER BY
  			ps.start_dt,
  			ps.end_dt;

Line: 3082

  		SELECT	cic.govt_citizenship_cd
  		FROM	IGS_ST_CITIZENSHP_CD	cic
  		WHERE	cic.citizenship_cd = p_citizenship_cd;

Line: 3086

  		SELECT	scho.start_dt,
  			scho.end_dt,
  			hpo.govt_hecs_payment_option,
  			sca.commencement_dt
  		FROM	IGS_EN_STDNT_PS_HECS_OPTION_V	scho,
  			IGS_FI_HECS_PAY_OPTN		hpo,
  			IGS_EN_STDNT_PS_ATT		sca
  		WHERE	scho.person_id		= p_person_id AND
  			scho.hecs_payment_option = hpo.hecs_payment_option AND
  			sca.person_id		= scho.person_id AND
  			sca.course_cd		= scho.course_cd
  		ORDER BY
  			scho.start_dt,
  			scho.end_dt;

Line: 3145

  			SELECT	'x'
  			FROM	IGS_PE_COUNTRY_CD	cnc
  			WHERE	cnc.country_cd = lp_term_location_country;

Line: 3437

  		SELECT	expire_aftr_acdmc_perd_ind
  		FROM	IGS_FI_HECS_PAY_OPTN
  		WHERE	hecs_payment_option= cp_hpo;

Line: 3443

  		SELECT 	end_dt
  		FROM	IGS_CA_INST
  		WHERE	cal_type = cp_cal_type AND
  			sequence_number = cp_cal_sequence_number ;