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_HE_UCAS_TARIFF_PKG dependencies on IGS_UC_QUAL_DETS

Line 84: CURSOR Cur_qual_dets (cp_person_id Igs_uc_qual_dets.person_id%TYPE,

80: -- smaddali modified this cursor for bug 2473397 ,
81: -- inorder to calculate ucas_tariff on the fly added the join with igs_as_grd_sch_grade
82: -- Modified the cursor to exclude excluded subjects and subjects that are excluded specifically for this award.
83: -- Modified the cursor to add subject_code IS NULL for 3224610
84: CURSOR Cur_qual_dets (cp_person_id Igs_uc_qual_dets.person_id%TYPE,
85: cp_commencement_date Igs_en_stdnt_ps_att.Commencement_dt%TYPE,
86: cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,
87: cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
88: SELECT uqd.Exam_level,

Line 86: cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,

82: -- Modified the cursor to exclude excluded subjects and subjects that are excluded specifically for this award.
83: -- Modified the cursor to add subject_code IS NULL for 3224610
84: CURSOR Cur_qual_dets (cp_person_id Igs_uc_qual_dets.person_id%TYPE,
85: cp_commencement_date Igs_en_stdnt_ps_att.Commencement_dt%TYPE,
86: cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,
87: cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
88: SELECT uqd.Exam_level,
89: uqd.Subject_code,
90: MAX(grd.rank) ucas_tariff

Line 91: FROM igs_uc_qual_dets uqd,

87: cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
88: SELECT uqd.Exam_level,
89: uqd.Subject_code,
90: MAX(grd.rank) ucas_tariff
91: FROM igs_uc_qual_dets uqd,
92: igs_as_grd_sch_grade grd
93: WHERE uqd.Person_id = cp_person_id
94: AND uqd.Exam_level = cp_exam_level
95: AND grd.grade = uqd.approved_result

Line 114: CURSOR Cur_exam_level (l_person_id igs_uc_qual_dets.person_id%TYPE,

110: -- Subquery3 is to filter out subject records which are setup for exclusion (Only subject without award)
111: -- Subquery4 is to filter out Award + subject combination records which are setup for exclusion i.e.
112: -- (both Award is not null and subject is not null in exclude qualifications table).
113: -- Modified the cursor to add subject_code IS NULL for 3224610
114: CURSOR Cur_exam_level (l_person_id igs_uc_qual_dets.person_id%TYPE,
115: l_commencement_date Igs_en_stdnt_ps_att. Commencement_dt%TYPE,
116: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
117: SELECT DISTINCT Exam_level
118: FROM Igs_uc_qual_dets

Line 118: FROM Igs_uc_qual_dets

114: CURSOR Cur_exam_level (l_person_id igs_uc_qual_dets.person_id%TYPE,
115: l_commencement_date Igs_en_stdnt_ps_att. Commencement_dt%TYPE,
116: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
117: SELECT DISTINCT Exam_level
118: FROM Igs_uc_qual_dets
119: WHERE Person_id = l_person_id
120: AND exam_level IN (SELECT hula.award_cd
121: FROM igs_he_ut_lvl_award hula
122: WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type

Line 227: FROM Igs_uc_qual_dets Iuqd,

223: Iagsta.to_version_number,
224: Iagsta.to_grade,
225: Iagsgv.full_grade_name, -- jchin 3484372 Added full grade name to output to log file
226: Iagsgv.rank
227: FROM Igs_uc_qual_dets Iuqd,
228: Igs_as_grd_sch_trn_all Iagsta,
229: Igs_as_grd_sch_grade_v Iagsgv,
230: Igs_as_grd_sch_grade grd
231: WHERE Iuqd.person_id = l_person_id

Line 253: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,

249: ORDER BY Iagsgv.rank ASC ;
250:
251: -- Returns the other awards with the same subject for the current person, exam level, subject being processed.
252: -- modified the cursor to include and exclude exam levels that are setup for the calculation type.
253: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
254: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
255: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
256: cp_commencement_date igs_en_stdnt_ps_att.commencement_dt%TYPE,
257: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS

Line 254: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,

250:
251: -- Returns the other awards with the same subject for the current person, exam level, subject being processed.
252: -- modified the cursor to include and exclude exam levels that are setup for the calculation type.
253: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
254: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
255: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
256: cp_commencement_date igs_en_stdnt_ps_att.commencement_dt%TYPE,
257: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
258: SELECT DISTINCT exam_level

Line 255: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,

251: -- Returns the other awards with the same subject for the current person, exam level, subject being processed.
252: -- modified the cursor to include and exclude exam levels that are setup for the calculation type.
253: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
254: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
255: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
256: cp_commencement_date igs_en_stdnt_ps_att.commencement_dt%TYPE,
257: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
258: SELECT DISTINCT exam_level
259: FROM igs_uc_qual_dets

Line 259: FROM igs_uc_qual_dets

255: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
256: cp_commencement_date igs_en_stdnt_ps_att.commencement_dt%TYPE,
257: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
258: SELECT DISTINCT exam_level
259: FROM igs_uc_qual_dets
260: WHERE person_id = cp_person_id
261: AND exam_level <> cp_exam_level
262: AND subject_code = cp_subject_cd
263: AND exam_level IN (SELECT hula.award_cd

Line 285: -- l_tariff_score igs_uc_qual_dets.ucas_tariff%TYPE := 0;

281: CONNECT BY PRIOR parent_award_cd = award_cd
282: AND tariff_calc_type_cd = cp_tariff_calc_type;
283:
284: -- anwest Bug#4035243 The original declaration:
285: -- l_tariff_score igs_uc_qual_dets.ucas_tariff%TYPE := 0;
286: -- was causing an Unhandled Exception when value > 999
287: -- because igs_uc_qual_dets.ucas_tariff%TYPE is defined as
288: -- NUMBER(3)
289: l_tariff_score igs_he_st_spa_ut_all.tariff_score%TYPE := 0;

Line 287: -- because igs_uc_qual_dets.ucas_tariff%TYPE is defined as

283:
284: -- anwest Bug#4035243 The original declaration:
285: -- l_tariff_score igs_uc_qual_dets.ucas_tariff%TYPE := 0;
286: -- was causing an Unhandled Exception when value > 999
287: -- because igs_uc_qual_dets.ucas_tariff%TYPE is defined as
288: -- NUMBER(3)
289: l_tariff_score igs_he_st_spa_ut_all.tariff_score%TYPE := 0;
290: l_qual_count Igs_he_st_spa_ut_all.Number_of_qual%TYPE := 0;
291: l_total_tariff_score Igs_he_st_spa_all.total_ucas_tariff%TYPE := 0;

Line 292: l_last_update_date Igs_uc_qual_dets.Last_update_date%TYPE ;

288: -- NUMBER(3)
289: l_tariff_score igs_he_st_spa_ut_all.tariff_score%TYPE := 0;
290: l_qual_count Igs_he_st_spa_ut_all.Number_of_qual%TYPE := 0;
291: l_total_tariff_score Igs_he_st_spa_all.total_ucas_tariff%TYPE := 0;
292: l_last_update_date Igs_uc_qual_dets.Last_update_date%TYPE ;
293: l_record_inserted NUMBER := 0;
294: l_record_updated NUMBER := 0;
295: l_hesa_st_spau_id Igs_he_st_spa_ut_all.hesa_st_spau_id%TYPE := 0;
296: l_Qual_aim igs_he_code_map_val.map1%TYPE ;

Line 350: fnd_dsql.add_text('AND EXISTS (SELECT person_id from igs_uc_qual_dets where person_id = ihss.person_id) ');

346: fnd_dsql.add_text('ihss.person_id, ihss.course_cd, ihss.version_number ');
347: fnd_dsql.add_text('FROM igs_he_st_spa ihss, igs_en_stdnt_ps_att iespa ');
348: fnd_dsql.add_text('WHERE ihss.person_id = iespa.person_id AND ihss.course_cd = iespa.course_cd ');
349: fnd_dsql.add_text('AND hesa_return_id IS NULL AND hesa_submission_name IS NULL AND hesa_return_name IS NULL ');
350: fnd_dsql.add_text('AND EXISTS (SELECT person_id from igs_uc_qual_dets where person_id = ihss.person_id) ');
351:
352: -- if person id is not null append the following filtering criteria
353: IF p_person_identifier IS NOT NULL THEN
354: fnd_dsql.add_text(' AND ihss.person_id = ');

Line 798: CURSOR prsn_exam_level_cur (cp_person_id igs_uc_qual_dets.person_id%TYPE,

794: -- Subquery3 is to filter out subject records which are setup for exclusion (Only subject without award)
795: -- Subquery4 is to filter out Award + subject combination records which are setup for exclusion i.e.
796: -- (both Award is not null and subject is not null in exclude qualifications table).
797: -- Modified the cursor to add subject_code IS NULL for 3224610
798: CURSOR prsn_exam_level_cur (cp_person_id igs_uc_qual_dets.person_id%TYPE,
799: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE)
800: IS
801: SELECT DISTINCT exam_level
802: FROM igs_uc_qual_dets

Line 802: FROM igs_uc_qual_dets

798: CURSOR prsn_exam_level_cur (cp_person_id igs_uc_qual_dets.person_id%TYPE,
799: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE)
800: IS
801: SELECT DISTINCT exam_level
802: FROM igs_uc_qual_dets
803: WHERE person_id = cp_person_id
804: AND exam_level IN (SELECT hula.award_cd
805: FROM igs_he_ut_lvl_award hula
806: WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type

Line 827: CURSOR get_tariff_cur (cp_person_id Igs_uc_qual_dets.person_id%TYPE,

823: -- get all the valid subject records as per setup for passed exam level of the student and person.
824: -- igs_as_grd_sch_grade is joined as UCAS Tariff is obtained/derived on the fly.
825: -- The subquery is used to exclude excluded subjects and subjects that are excluded specifically for this award.
826: -- Modified the cursor to add subject_code IS NULL for 3224610
827: CURSOR get_tariff_cur (cp_person_id Igs_uc_qual_dets.person_id%TYPE,
828: cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,
829: cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
830: SELECT uqd.Exam_level,
831: uqd.Subject_code,

Line 828: cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,

824: -- igs_as_grd_sch_grade is joined as UCAS Tariff is obtained/derived on the fly.
825: -- The subquery is used to exclude excluded subjects and subjects that are excluded specifically for this award.
826: -- Modified the cursor to add subject_code IS NULL for 3224610
827: CURSOR get_tariff_cur (cp_person_id Igs_uc_qual_dets.person_id%TYPE,
828: cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,
829: cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
830: SELECT uqd.Exam_level,
831: uqd.Subject_code,
832: MAX(grd.rank) ucas_tariff

Line 833: FROM igs_uc_qual_dets uqd,

829: cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
830: SELECT uqd.Exam_level,
831: uqd.Subject_code,
832: MAX(grd.rank) ucas_tariff
833: FROM igs_uc_qual_dets uqd,
834: igs_as_grd_sch_grade grd
835: WHERE uqd.Person_id = cp_person_id
836: AND uqd.Exam_level = cp_exam_level
837: AND grd.grade = uqd.approved_result

Line 848: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,

844: GROUP BY uqd.Exam_level, uqd.Subject_code ;
845:
846:
847: -- cursor to get records for the passed subject with other exam levels
848: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
849: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
850: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
851: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
852: SELECT DISTINCT exam_level

Line 849: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,

845:
846:
847: -- cursor to get records for the passed subject with other exam levels
848: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
849: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
850: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
851: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
852: SELECT DISTINCT exam_level
853: FROM igs_uc_qual_dets

Line 850: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,

846:
847: -- cursor to get records for the passed subject with other exam levels
848: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
849: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
850: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
851: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
852: SELECT DISTINCT exam_level
853: FROM igs_uc_qual_dets
854: WHERE person_id = cp_person_id

Line 853: FROM igs_uc_qual_dets

849: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
850: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
851: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
852: SELECT DISTINCT exam_level
853: FROM igs_uc_qual_dets
854: WHERE person_id = cp_person_id
855: AND exam_level <> cp_exam_level
856: AND subject_code = cp_subject_cd
857: AND exam_level IN (SELECT hula.award_cd

Line 911: l_person_id igs_uc_qual_dets.person_id%TYPE;

907: l_tariff_score NUMBER ;
908: l_total_tariff_score NUMBER; -- jchin bug 3678414 Hold the total tariff
909: l_qual_count NUMBER ;
910: l_record_inserted NUMBER := 0;
911: l_person_id igs_uc_qual_dets.person_id%TYPE;
912: l_calc_prsn_tariff VARCHAR2(1);
913: l_rowid VARCHAR2(26);
914: l_prnt_awd_closed VARCHAR2(1);
915:

Line 927: -- l_Int_calc_sql := 'SELECT DISTINCT person_id FROM igs_uc_qual_dets WHERE person_id = NVL(' || p_person_identifier || ', person_id) ';

923: BEGIN
924: fnd_dsql.init;
925:
926: -- basic sql stmt to get list of persons to be processed.
927: -- l_Int_calc_sql := 'SELECT DISTINCT person_id FROM igs_uc_qual_dets WHERE person_id = NVL(' || p_person_identifier || ', person_id) ';
928: fnd_dsql.add_text('SELECT DISTINCT person_id FROM igs_uc_qual_dets ');
929:
930: IF p_person_identifier IS NOT NULL OR p_person_id_grp IS NOT NULL THEN
931:

Line 928: fnd_dsql.add_text('SELECT DISTINCT person_id FROM igs_uc_qual_dets ');

924: fnd_dsql.init;
925:
926: -- basic sql stmt to get list of persons to be processed.
927: -- l_Int_calc_sql := 'SELECT DISTINCT person_id FROM igs_uc_qual_dets WHERE person_id = NVL(' || p_person_identifier || ', person_id) ';
928: fnd_dsql.add_text('SELECT DISTINCT person_id FROM igs_uc_qual_dets ');
929:
930: IF p_person_identifier IS NOT NULL OR p_person_id_grp IS NOT NULL THEN
931:
932: fnd_dsql.add_text(' WHERE ');

Line 1214: CURSOR get_tariff_cur (cp_person_id Igs_uc_qual_dets.person_id%TYPE,

1210: FROM igs_he_ut_prs_dtls upd
1211: WHERE person_id = cp_person_id
1212: AND tariff_calc_type_cd = cp_calc_type;
1213:
1214: CURSOR get_tariff_cur (cp_person_id Igs_uc_qual_dets.person_id%TYPE,
1215: cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,
1216: cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
1217: SELECT uqd.Exam_level,
1218: uqd.Subject_code,

Line 1215: cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,

1211: WHERE person_id = cp_person_id
1212: AND tariff_calc_type_cd = cp_calc_type;
1213:
1214: CURSOR get_tariff_cur (cp_person_id Igs_uc_qual_dets.person_id%TYPE,
1215: cp_exam_level Igs_uc_qual_dets.Exam_level%TYPE,
1216: cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
1217: SELECT uqd.Exam_level,
1218: uqd.Subject_code,
1219: MAX(grd.rank) ucas_tariff

Line 1220: FROM igs_uc_qual_dets uqd,

1216: cp_tariff_calc_type Igs_he_ut_excl_qals.tariff_calc_type_cd%TYPE) IS
1217: SELECT uqd.Exam_level,
1218: uqd.Subject_code,
1219: MAX(grd.rank) ucas_tariff
1220: FROM igs_uc_qual_dets uqd,
1221: igs_as_grd_sch_grade grd
1222: WHERE uqd.Person_id = cp_person_id
1223: AND uqd.Exam_level = cp_exam_level
1224: AND grd.grade = uqd.approved_result

Line 1234: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,

1230: AND (award_cd IS NULL OR award_cd = cp_exam_level)))
1231: GROUP BY uqd.Exam_level, uqd.Subject_code ;
1232:
1233: -- cursor to get records for the passed subject with other exam levels
1234: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
1235: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
1236: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
1237: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
1238: SELECT DISTINCT exam_level

Line 1235: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,

1231: GROUP BY uqd.Exam_level, uqd.Subject_code ;
1232:
1233: -- cursor to get records for the passed subject with other exam levels
1234: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
1235: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
1236: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
1237: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
1238: SELECT DISTINCT exam_level
1239: FROM igs_uc_qual_dets

Line 1236: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,

1232:
1233: -- cursor to get records for the passed subject with other exam levels
1234: CURSOR cur_check_dup_awards (cp_person_id igs_uc_qual_dets.person_id%TYPE,
1235: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
1236: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
1237: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
1238: SELECT DISTINCT exam_level
1239: FROM igs_uc_qual_dets
1240: WHERE person_id = cp_person_id

Line 1239: FROM igs_uc_qual_dets

1235: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
1236: cp_subject_cd igs_uc_qual_dets.subject_code%TYPE,
1237: cp_tariff_calc_type igs_he_ut_lvl_award.tariff_calc_type_cd%TYPE) IS
1238: SELECT DISTINCT exam_level
1239: FROM igs_uc_qual_dets
1240: WHERE person_id = cp_person_id
1241: AND exam_level <> cp_exam_level
1242: AND subject_code = cp_subject_cd
1243: AND exam_level IN (SELECT hula.award_cd