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_UC_EXP_APPLICANT_DTLS dependencies on IGS_UC_APPLICANTS

Line 73: CURSOR cur_chk_app_systems(cp_app_no igs_uc_applicants.app_no%TYPE ) IS

69: cur_ninumber_alt_type_rec cur_ninumber_alt_type%ROWTYPE;
70:
71: -- Cursors to get the Distinct Systems for the records to be selected for processing
72: -- Added for bug# bug# 3553352
73: CURSOR cur_chk_app_systems(cp_app_no igs_uc_applicants.app_no%TYPE ) IS
74: SELECT DISTINCT system_code
75: FROM igs_uc_applicants ucap
76: WHERE ucap.app_no = NVL(cp_app_no, ucap.app_no)
77: AND NVL(ucap.sent_to_oss,'N') = 'N';

Line 75: FROM igs_uc_applicants ucap

71: -- Cursors to get the Distinct Systems for the records to be selected for processing
72: -- Added for bug# bug# 3553352
73: CURSOR cur_chk_app_systems(cp_app_no igs_uc_applicants.app_no%TYPE ) IS
74: SELECT DISTINCT system_code
75: FROM igs_uc_applicants ucap
76: WHERE ucap.app_no = NVL(cp_app_no, ucap.app_no)
77: AND NVL(ucap.sent_to_oss,'N') = 'N';
78:
79: -- Cursor to check whether decision make id is set for the sytem

Line 99: CURSOR cur_uc_app_dtls (cp_app_no IGS_UC_APPLICANTS.app_no%TYPE) IS

95: WHERE person_id = cp_person_id;
96: cur_uc_person_dtls_rec cur_uc_person_dtls%ROWTYPE;
97:
98: -- Fetch the OSS Person ID of an UCAS Applicant
99: CURSOR cur_uc_app_dtls (cp_app_no IGS_UC_APPLICANTS.app_no%TYPE) IS
100: SELECT ucap.app_no, ucap.oss_person_id, domicile_apr, ucap.country_birth
101: FROM IGS_UC_APPLICANTS ucap
102: WHERE ucap.app_no = cp_app_no;
103: cur_uc_app_dtls_rec cur_uc_app_dtls%ROWTYPE;

Line 101: FROM IGS_UC_APPLICANTS ucap

97:
98: -- Fetch the OSS Person ID of an UCAS Applicant
99: CURSOR cur_uc_app_dtls (cp_app_no IGS_UC_APPLICANTS.app_no%TYPE) IS
100: SELECT ucap.app_no, ucap.oss_person_id, domicile_apr, ucap.country_birth
101: FROM IGS_UC_APPLICANTS ucap
102: WHERE ucap.app_no = cp_app_no;
103: cur_uc_app_dtls_rec cur_uc_app_dtls%ROWTYPE;
104:
105: -- Fetch the Interface ID for the passed Batch ID and Person ID

Line 116: CURSOR cur_upd_ucas_app ( cp_app_no IGS_UC_APPLICANTS.app_no%TYPE) IS

112:
113: /* Cursors used in the Export Applicant Details Logic */
114:
115: -- Cursor to fetch the Applicant Details for Update
116: CURSOR cur_upd_ucas_app ( cp_app_no IGS_UC_APPLICANTS.app_no%TYPE) IS
117: SELECT ucap.rowid,ucap.*
118: FROM IGS_UC_APPLICANTS ucap
119: WHERE ucap.app_no = cp_app_no;
120: cur_ucas_app_rec cur_upd_ucas_app%ROWTYPE;

Line 118: FROM IGS_UC_APPLICANTS ucap

114:
115: -- Cursor to fetch the Applicant Details for Update
116: CURSOR cur_upd_ucas_app ( cp_app_no IGS_UC_APPLICANTS.app_no%TYPE) IS
117: SELECT ucap.rowid,ucap.*
118: FROM IGS_UC_APPLICANTS ucap
119: WHERE ucap.app_no = cp_app_no;
120: cur_ucas_app_rec cur_upd_ucas_app%ROWTYPE;
121:
122:

Line 155: FROM IGS_UC_APPLICANTS ucap

151: /* Cursors used to Process the Admission Interface Tables data */
152:
153: CURSOR cur_proc_applicants(cp_batch_id IGS_AD_INTERFACE_ALL.batch_id%TYPE) IS
154: SELECT ucap.rowid,ucap.*
155: FROM IGS_UC_APPLICANTS ucap
156: WHERE ucap.ad_batch_id = cp_batch_id;
157:
158: CURSOR cur_proc_app_address(cp_batch_id IGS_AD_INTERFACE_ALL.batch_id%TYPE) IS
159: SELECT ucad.rowid,ucad.*

Line 173: FROM IGS_UC_APPLICANTS ucapi

169:
170: -- anwest Bug# 3642740 New cursor to store UCAS applicants in error
171: CURSOR cur_proc_applicants_i IS
172: SELECT ucapi.rowid, ucapi.*
173: FROM IGS_UC_APPLICANTS ucapi
174: WHERE ucapi.sent_to_oss = 'I';
175:
176: -- anwest Bug# 3642740 New cursor to store UCAS applicant addresses in error
177: CURSOR cur_proc_app_address_i IS

Line 228: app_no IGS_UC_APPLICANTS.APP_NO%TYPE,

224: TYPE res_term_table_type IS TABLE OF res_term_type INDEX BY BINARY_INTEGER;
225:
226: --jchakrab added for 3691176
227: TYPE applicant_record IS RECORD (
228: app_no IGS_UC_APPLICANTS.APP_NO%TYPE,
229: system_code IGS_UC_APPLICANTS.SYSTEM_CODE%TYPE,
230: oss_person_id IGS_UC_APPLICANTS.OSS_PERSON_ID%TYPE,
231: scn IGS_UC_APPLICANTS.SCN%TYPE,
232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,

Line 229: system_code IGS_UC_APPLICANTS.SYSTEM_CODE%TYPE,

225:
226: --jchakrab added for 3691176
227: TYPE applicant_record IS RECORD (
228: app_no IGS_UC_APPLICANTS.APP_NO%TYPE,
229: system_code IGS_UC_APPLICANTS.SYSTEM_CODE%TYPE,
230: oss_person_id IGS_UC_APPLICANTS.OSS_PERSON_ID%TYPE,
231: scn IGS_UC_APPLICANTS.SCN%TYPE,
232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,
233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,

Line 230: oss_person_id IGS_UC_APPLICANTS.OSS_PERSON_ID%TYPE,

226: --jchakrab added for 3691176
227: TYPE applicant_record IS RECORD (
228: app_no IGS_UC_APPLICANTS.APP_NO%TYPE,
229: system_code IGS_UC_APPLICANTS.SYSTEM_CODE%TYPE,
230: oss_person_id IGS_UC_APPLICANTS.OSS_PERSON_ID%TYPE,
231: scn IGS_UC_APPLICANTS.SCN%TYPE,
232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,
233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,
234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,

Line 231: scn IGS_UC_APPLICANTS.SCN%TYPE,

227: TYPE applicant_record IS RECORD (
228: app_no IGS_UC_APPLICANTS.APP_NO%TYPE,
229: system_code IGS_UC_APPLICANTS.SYSTEM_CODE%TYPE,
230: oss_person_id IGS_UC_APPLICANTS.OSS_PERSON_ID%TYPE,
231: scn IGS_UC_APPLICANTS.SCN%TYPE,
232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,
233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,
234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,
235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,

Line 232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,

228: app_no IGS_UC_APPLICANTS.APP_NO%TYPE,
229: system_code IGS_UC_APPLICANTS.SYSTEM_CODE%TYPE,
230: oss_person_id IGS_UC_APPLICANTS.OSS_PERSON_ID%TYPE,
231: scn IGS_UC_APPLICANTS.SCN%TYPE,
232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,
233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,
234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,
235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,
236: special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,

Line 233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,

229: system_code IGS_UC_APPLICANTS.SYSTEM_CODE%TYPE,
230: oss_person_id IGS_UC_APPLICANTS.OSS_PERSON_ID%TYPE,
231: scn IGS_UC_APPLICANTS.SCN%TYPE,
232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,
233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,
234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,
235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,
236: special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,
237: school IGS_UC_APPLICANTS.SCHOOL%TYPE,

Line 234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,

230: oss_person_id IGS_UC_APPLICANTS.OSS_PERSON_ID%TYPE,
231: scn IGS_UC_APPLICANTS.SCN%TYPE,
232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,
233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,
234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,
235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,
236: special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,
237: school IGS_UC_APPLICANTS.SCHOOL%TYPE,
238: application_date IGS_UC_APPLICANTS.APPLICATION_DATE%TYPE,

Line 235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,

231: scn IGS_UC_APPLICANTS.SCN%TYPE,
232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,
233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,
234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,
235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,
236: special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,
237: school IGS_UC_APPLICANTS.SCHOOL%TYPE,
238: application_date IGS_UC_APPLICANTS.APPLICATION_DATE%TYPE,
239: country_birth IGS_UC_APPLICANTS.COUNTRY_BIRTH%TYPE );

Line 236: special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,

232: ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,
233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,
234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,
235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,
236: special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,
237: school IGS_UC_APPLICANTS.SCHOOL%TYPE,
238: application_date IGS_UC_APPLICANTS.APPLICATION_DATE%TYPE,
239: country_birth IGS_UC_APPLICANTS.COUNTRY_BIRTH%TYPE );
240:

Line 237: school IGS_UC_APPLICANTS.SCHOOL%TYPE,

233: residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,
234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,
235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,
236: special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,
237: school IGS_UC_APPLICANTS.SCHOOL%TYPE,
238: application_date IGS_UC_APPLICANTS.APPLICATION_DATE%TYPE,
239: country_birth IGS_UC_APPLICANTS.COUNTRY_BIRTH%TYPE );
240:
241: TYPE t_cur_exp_applicant_dtls IS REF CURSOR RETURN applicant_record;

Line 238: application_date IGS_UC_APPLICANTS.APPLICATION_DATE%TYPE,

234: nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,
235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,
236: special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,
237: school IGS_UC_APPLICANTS.SCHOOL%TYPE,
238: application_date IGS_UC_APPLICANTS.APPLICATION_DATE%TYPE,
239: country_birth IGS_UC_APPLICANTS.COUNTRY_BIRTH%TYPE );
240:
241: TYPE t_cur_exp_applicant_dtls IS REF CURSOR RETURN applicant_record;
242: cur_exp_applicant_dtls t_cur_exp_applicant_dtls;

Line 239: country_birth IGS_UC_APPLICANTS.COUNTRY_BIRTH%TYPE );

235: dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,
236: special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,
237: school IGS_UC_APPLICANTS.SCHOOL%TYPE,
238: application_date IGS_UC_APPLICANTS.APPLICATION_DATE%TYPE,
239: country_birth IGS_UC_APPLICANTS.COUNTRY_BIRTH%TYPE );
240:
241: TYPE t_cur_exp_applicant_dtls IS REF CURSOR RETURN applicant_record;
242: cur_exp_applicant_dtls t_cur_exp_applicant_dtls;
243:

Line 316: CURSOR cur_get_syscode_entryyr(cp_app_no igs_uc_applicants.app_no%TYPE ) IS

312: ******************************************************************************/
313:
314: -- retrieves application system codes and entry years from their
315: -- application choices
316: CURSOR cur_get_syscode_entryyr(cp_app_no igs_uc_applicants.app_no%TYPE ) IS
317: SELECT system_code, entry_year
318: FROM IGS_UC_APP_CHOICES
319: WHERE app_no = cp_app_no
320: ORDER BY entry_year;

Line 564: FROM IGS_UC_APPLICANTS UCAP

560: OPEN cur_exp_applicant_dtls FOR
561: SELECT APP_NO, SYSTEM_CODE, OSS_PERSON_ID, SCN, NI_NUMBER, RESIDENTIAL_CATEGORY,
562: NATIONALITY, DUAL_NATIONALITY, SPECIAL_NEEDS, SCHOOL, APPLICATION_DATE,
563: COUNTRY_BIRTH
564: FROM IGS_UC_APPLICANTS UCAP
565: WHERE UCAP.SENT_TO_OSS = 'N'
566: ORDER BY UCAP.APP_NO;
567:
568: ELSE

Line 573: FROM IGS_UC_APPLICANTS UCAP

569: OPEN cur_exp_applicant_dtls FOR
570: SELECT APP_NO, SYSTEM_CODE, OSS_PERSON_ID, SCN, NI_NUMBER, RESIDENTIAL_CATEGORY,
571: NATIONALITY, DUAL_NATIONALITY, SPECIAL_NEEDS, SCHOOL, APPLICATION_DATE,
572: COUNTRY_BIRTH
573: FROM IGS_UC_APPLICANTS UCAP
574: WHERE UCAP.APP_NO = p_app_no AND
575: UCAP.SENT_TO_OSS = 'N'
576: ORDER BY UCAP.APP_NO;
577: END IF;

Line 580: -- Loop through all the records in IGS_UC_APPLICANTS table satisfying the criteria,

576: ORDER BY UCAP.APP_NO;
577: END IF;
578:
579:
580: -- Loop through all the records in IGS_UC_APPLICANTS table satisfying the criteria,
581: -- SENT_TO_OSS is N and passed parameter,P_APP_NO
582: LOOP
583: FETCH cur_exp_applicant_dtls INTO cur_exp_applicant_dtls_rec;
584: EXIT WHEN cur_exp_applicant_dtls%NOTFOUND;

Line 875: igs_uc_applicants_pkg.update_row (

871: FETCH cur_upd_ucas_app INTO cur_ucas_app_rec;
872: CLOSE cur_upd_ucas_app;
873:
874: -- Update the UCAS Applicants Table with Batch ID and Interface ID
875: igs_uc_applicants_pkg.update_row (
876: x_rowid => cur_ucas_app_rec.rowid
877: ,x_app_id => cur_ucas_app_rec.app_id
878: ,x_app_no => cur_ucas_app_rec.app_no
879: ,x_check_digit => cur_ucas_app_rec.check_digit

Line 1404: -- Processing the records in the IGS_UC_APPLICANTS Table

1400: /******* Process the Interface Records and update the UCAS Interface Tables ******/
1401:
1402: IF l_adm_imp_status = TRUE THEN
1403:
1404: -- Processing the records in the IGS_UC_APPLICANTS Table
1405: FOR cur_proc_applicant_rec IN cur_proc_applicants(l_ad_batch_id) LOOP
1406:
1407: -- Check whether the record exist in the IGS_AD_INTERFACE Table for the
1408: -- Admission Batch ID and Interface ID

Line 1420: -- update the IGS_UC_APPLICANTS Table

1416: l_sent_to_oss_flag := 'Y';
1417: END IF;
1418: CLOSE cur_ad_interface_exist;
1419:
1420: -- update the IGS_UC_APPLICANTS Table
1421: cur_ucas_app_rec := NULL;
1422: OPEN cur_upd_ucas_app (cur_proc_applicant_rec.app_no);
1423: FETCH cur_upd_ucas_app INTO cur_ucas_app_rec;
1424: CLOSE cur_upd_ucas_app;

Line 1427: igs_uc_applicants_pkg.update_row (

1423: FETCH cur_upd_ucas_app INTO cur_ucas_app_rec;
1424: CLOSE cur_upd_ucas_app;
1425:
1426: -- Update the UCAS Applicants Table with Batch ID and Interface ID
1427: igs_uc_applicants_pkg.update_row (
1428: x_rowid => cur_ucas_app_rec.rowid
1429: ,x_app_id => cur_ucas_app_rec.app_id
1430: ,x_app_no => cur_ucas_app_rec.app_no
1431: ,x_check_digit => cur_ucas_app_rec.check_digit

Line 1594: -- Processing the records in the IGS_UC_APPLICANTS Table with SENT_TO_OSS set to 'I'

1590:
1591: END IF;
1592:
1593: -- anwest Bug# 3642740
1594: -- Processing the records in the IGS_UC_APPLICANTS Table with SENT_TO_OSS set to 'I'
1595: FOR cur_proc_applicant_i_rec IN cur_proc_applicants_i LOOP
1596:
1597: -- Check whether the record exist in the IGS_AD_INTERFACE Table for the
1598: -- Admission Batch ID and Interface ID

Line 1609: igs_uc_applicants_pkg.update_row (

1605: cur_ucas_app_rec := NULL;
1606: OPEN cur_upd_ucas_app (cur_proc_applicant_i_rec.app_no);
1607: FETCH cur_upd_ucas_app INTO cur_ucas_app_rec;
1608: CLOSE cur_upd_ucas_app;
1609: igs_uc_applicants_pkg.update_row (
1610: x_rowid => cur_ucas_app_rec.rowid
1611: ,x_app_id => cur_ucas_app_rec.app_id
1612: ,x_app_no => cur_ucas_app_rec.app_no
1613: ,x_check_digit => cur_ucas_app_rec.check_digit