eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_AD_IMP_UH_TST_PKG SQL Statements

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

Line: 15


SELECT pgm.person_id
FROM   igs_pe_prsid_grp_mem pgm
WHERE  pgm.group_id = cp_group_id AND
NVL(TRUNC(pgm.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
NVL(TRUNC(pgm.end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);

Line: 103


	SELECT
		*
	FROM
		igs_ad_tstrst_uh_int
	WHERE
		PERSON_ID  = p_person_id AND
		SESSION_ID = p_session_id AND
		STATUS = '2';

Line: 113


        SELECT SUM(B.TEST_SCORE) FROM IGS_AD_TSTRST_UH_INT A,IGS_AD_TSTDTL_UH_INT B
        WHERE A.INTERFACE_TST_ID = B.INTERFACE_TST_ID
        AND A.PERSON_ID = p_person_id
        AND A.SESSION_ID = p_session_id
        AND B.TEST_SEGMENT_ID IN (SELECT TEST_SEGMENT_ID
                           FROM   IGS_AD_TEST_SEGMENTS
			   WHERE  INCLUDE_IN_COMP_SCORE ='Y'
			   AND ADMISSION_TEST_TYPE IN
				( SELECT distinct admission_test_type
                                  FROM IGS_AD_TEST_RESULTS
                                  WHERE person_id = p_person_id));

Line: 130


	SELECT
		score_type
	FROM
		igs_ad_test_type
	WHERE
		admission_test_type = cp_test_type;

Line: 147


	SELECT
		a.rowid, a.*
	FROM
		igs_ad_test_results a
	WHERE
		person_id = p_person_id  AND
		admission_test_type = cp_test_type AND
		active_ind = 'Y' AND
		test_results_id <> cp_test_results_id;

Line: 179


		-- 8.  Create  Cursor  C_TSTDTL_CUR with the following SELECT statement in order to import
		-- all the test segments for the imported test type
		--
		CURSOR c_tstdtl_cur ( cp_interface_tst_id NUMBER) IS
		SELECT
			*
		FROM
			igs_ad_tstdtl_uh_int
		WHERE
			interface_tst_id = cp_interface_tst_id;

Line: 191


			igs_ad_tst_rslt_dtls_pkg.insert_row
			(
				X_ROWID                        => l_tstdtl_rowid,
				X_TST_RSLT_DTLS_ID             => l_tst_rslt_dtls_id,
				X_TEST_RESULTS_ID              => l_test_results_id,
				X_TEST_SEGMENT_ID              => c_tstdtl_rec.test_segment_id,
				X_TEST_SCORE                   => c_tstdtl_rec.test_score,
				X_PERCENTILE                   => NULL,
				X_NATIONAL_PERCENTILE          => NULL,
				X_STATE_PERCENTILE             => NULL,
				X_PERCENTILE_YEAR_RANK         => NULL,
				X_SCORE_BAND_LOWER             => NULL,
				X_SCORE_BAND_UPPER             => NULL,
				X_IRREGULARITY_CODE_ID         => NULL,
				X_ATTRIBUTE_CATEGORY           => NULL,
				X_ATTRIBUTE1                   => NULL,
				X_ATTRIBUTE2                   => NULL,
				X_ATTRIBUTE3                   => NULL,
				X_ATTRIBUTE4                   => NULL,
				X_ATTRIBUTE5                   => NULL,
				X_ATTRIBUTE6                   => NULL,
				X_ATTRIBUTE7                   => NULL,
				X_ATTRIBUTE8                   => NULL,
				X_ATTRIBUTE9                   => NULL,
				X_ATTRIBUTE10                  => NULL,
				X_ATTRIBUTE11                  => NULL,
				X_ATTRIBUTE12                  => NULL,
				X_ATTRIBUTE13                  => NULL,
				X_ATTRIBUTE14                  => NULL,
				X_ATTRIBUTE15                  => NULL,
				X_ATTRIBUTE16                  => NULL,
				X_ATTRIBUTE17                  => NULL,
				X_ATTRIBUTE18                  => NULL,
				X_ATTRIBUTE19                  => NULL,
				X_ATTRIBUTE20                  => NULL,
				X_MODE                         => 'R'
			);

Line: 265


				-- Insert master begin
				BEGIN
					--
					-- DLD_adsr_Test_Scores
					-- 7.            Call IGS_AD_TEST_RESULTS.INSERT_ROW(
					--
					igs_ad_test_results_pkg.insert_row
					(
						X_ROWID                        => l_tst_rowid,
						X_TEST_RESULTS_ID              => l_test_results_id,
						X_PERSON_ID                    => c_tst_rec.person_id,
						X_ADMISSION_TEST_TYPE          => c_tst_rec.test_type,
						X_TEST_DATE                    => c_tst_rec.test_date,
						X_SCORE_REPORT_DATE            => NULL,
						X_EDU_LEVEL_ID                 => NULL,
						X_SCORE_TYPE                   => c_score_typ_rec.score_type,
						X_SCORE_SOURCE_ID              => NULL,
						X_NON_STANDARD_ADMIN           => NULL,
						X_COMP_TEST_SCORE              => l_test_scores,
						X_SPECIAL_CODE                 => NULL,
						X_REGISTRATION_NUMBER          => NULL,
						X_GRADE_ID                     => NULL,
						X_ATTRIBUTE_CATEGORY           => NULL,
						X_ATTRIBUTE1                   => NULL,
						X_ATTRIBUTE2                   => NULL,
						X_ATTRIBUTE3                   => NULL,
						X_ATTRIBUTE4                   => NULL,
						X_ATTRIBUTE5                   => NULL,
						X_ATTRIBUTE6                   => NULL,
						X_ATTRIBUTE7                   => NULL,
						X_ATTRIBUTE8                   => NULL,
						X_ATTRIBUTE9                   => NULL,
						X_ATTRIBUTE10                  => NULL,
						X_ATTRIBUTE11                  => NULL,
						X_ATTRIBUTE12                  => NULL,
						X_ATTRIBUTE13                  => NULL,
						X_ATTRIBUTE14                  => NULL,
						X_ATTRIBUTE15                  => NULL,
						X_ATTRIBUTE16                  => NULL,
						X_ATTRIBUTE17                  => NULL,
						X_ATTRIBUTE18                  => NULL,
						X_ATTRIBUTE19                  => NULL,
						X_ATTRIBUTE20                  => NULL,
						X_MODE                         => 'R',
						X_ACTIVE_IND                   => c_tst_rec.active_ind
					);

Line: 314


						FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Insertion of Test Result record failed '
									|| ' Person ID :: ' || IGS_GE_NUMBER.TO_CANN ( p_person_id)
									|| ' INTERFACE_TST_ID :: ' || IGS_GE_NUMBER.TO_CANN ( c_tst_rec.interface_tst_id) );

Line: 317


				-- Insert Master End
				END;

Line: 321


					-- After successful insertion of the master record Check if the active ind is 'Y'
					-- If 'y' then update the other test results records with same admission_test_type
					-- to 'N'
					--
					IF c_tst_rec.active_ind = 'Y' THEN
						FOR c_other_test_rec IN c_other_test_cur ( c_tst_rec.test_type, l_test_results_id) LOOP
						-- Update Active Ind Begin
						BEGIN
							igs_ad_test_results_pkg.update_row
							(
								X_ROWID                        => c_other_test_rec.rowid,
								X_TEST_RESULTS_ID              => c_other_test_rec.test_results_id,
								X_PERSON_ID                    => c_other_test_rec.person_id,
								X_ADMISSION_TEST_TYPE          => c_other_test_rec.admission_test_type,
								X_TEST_DATE                    => c_other_test_rec.test_date,
								X_SCORE_REPORT_DATE            => c_other_test_rec.score_report_date,
								X_EDU_LEVEL_ID                 => c_other_test_rec.edu_level_id,
								X_SCORE_TYPE                   => c_other_test_rec.score_type,
								X_SCORE_SOURCE_ID              => c_other_test_rec.score_source_id,
								X_NON_STANDARD_ADMIN           => c_other_test_rec.non_standard_admin,
								X_COMP_TEST_SCORE              => c_other_test_rec.comp_test_score,
								X_SPECIAL_CODE                 => c_other_test_rec.special_code,
								X_REGISTRATION_NUMBER          => c_other_test_rec.registration_number,
								X_GRADE_ID                     => c_other_test_rec.grade_id,
								X_ATTRIBUTE_CATEGORY           => c_other_test_rec.attribute_category,
								X_ATTRIBUTE1                   => c_other_test_rec.attribute1,
								X_ATTRIBUTE2                   => c_other_test_rec.attribute2,
								X_ATTRIBUTE3                   => c_other_test_rec.attribute3,
								X_ATTRIBUTE4                   => c_other_test_rec.attribute4,
								X_ATTRIBUTE5                   => c_other_test_rec.attribute5,
								X_ATTRIBUTE6                   => c_other_test_rec.attribute6,
								X_ATTRIBUTE7                   => c_other_test_rec.attribute7,
								X_ATTRIBUTE8                   => c_other_test_rec.attribute8,
								X_ATTRIBUTE9                   => c_other_test_rec.attribute9,
								X_ATTRIBUTE10                  => c_other_test_rec.attribute10,
								X_ATTRIBUTE11                  => c_other_test_rec.attribute11,
								X_ATTRIBUTE12                  => c_other_test_rec.attribute12,
								X_ATTRIBUTE13                  => c_other_test_rec.attribute13,
								X_ATTRIBUTE14                  => c_other_test_rec.attribute14,
								X_ATTRIBUTE15                  => c_other_test_rec.attribute15,
								X_ATTRIBUTE16                  => c_other_test_rec.attribute16,
								X_ATTRIBUTE17                  => c_other_test_rec.attribute17,
								X_ATTRIBUTE18                  => c_other_test_rec.attribute18,
								X_ATTRIBUTE19                  => c_other_test_rec.attribute19,
								X_ATTRIBUTE20                  => c_other_test_rec.attribute20,
								X_MODE                         => 'R',
								X_ACTIVE_IND                   => 'N' -- This field alone is updated
							);

Line: 371


								l_error_code := 'E004'; -- Active Indicator Update failed

Line: 372


								FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Active Indicator Update failed '
											|| ' Person ID :: ' || IGS_GE_NUMBER.TO_CANN ( p_person_id)
											|| ' INTERFACE_TST_ID :: ' || IGS_GE_NUMBER.TO_CANN ( c_tst_rec.interface_tst_id) );

Line: 376


						-- Update Active Ind End
						END;

Line: 382


				-- After successful insertion of the master record insert the
				-- child records into the result details table
				--
				IF l_error_code IS NULL THEN
					imp_chld_test_details ( P_RETURN_STATUS => l_return_status, P_INTERFACE_TST_ID => c_tst_rec.interface_tst_id);

Line: 389


					l_error_code := 'E003'; -- Insertion of child record failed

Line: 390


					FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Insertion of child record failed '
									|| ' Person ID :: ' || IGS_GE_NUMBER.TO_CANN ( p_person_id)
									|| ' INTERFACE_TST_ID :: ' || IGS_GE_NUMBER.TO_CANN ( c_tst_rec.interface_tst_id) );

Line: 405


		DELETE FROM
			igs_ad_tstdtl_uh_int
		WHERE
			interface_tst_id = c_tst_rec.interface_tst_id;

Line: 410


		DELETE FROM
			igs_ad_tstrst_uh_int
		WHERE
			interface_tst_id = c_tst_rec.interface_tst_id;

Line: 416


		UPDATE
			igs_ad_tstrst_uh_int
		SET
			status = '3',
			error_code = l_error_code
		WHERE
			interface_tst_id = c_tst_rec.interface_tst_id;