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_OFF_RESP_DATA dependencies on IGS_AD_PS_APPL_INST_ALL

Line 18: CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,

14: ---------------------------------------------------------------------------------------------------------------------------------------
15:
16: -- Cursor to get the Application Instance record from OSS System table, corresponding to given Interface Offer Response record
17: -- Cursor is put at the package level as it's being accessed by different procedures of this package.
18: CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
19: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
20: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
21: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
22: ) IS

Line 19: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,

15:
16: -- Cursor to get the Application Instance record from OSS System table, corresponding to given Interface Offer Response record
17: -- Cursor is put at the package level as it's being accessed by different procedures of this package.
18: CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
19: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
20: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
21: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
22: ) IS
23: SELECT aplinst.rowid, aplinst.*

Line 20: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,

16: -- Cursor to get the Application Instance record from OSS System table, corresponding to given Interface Offer Response record
17: -- Cursor is put at the package level as it's being accessed by different procedures of this package.
18: CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
19: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
20: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
21: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
22: ) IS
23: SELECT aplinst.rowid, aplinst.*
24: FROM igs_ad_ps_appl_inst_all aplinst

Line 21: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE

17: -- Cursor is put at the package level as it's being accessed by different procedures of this package.
18: CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
19: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
20: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
21: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
22: ) IS
23: SELECT aplinst.rowid, aplinst.*
24: FROM igs_ad_ps_appl_inst_all aplinst
25: WHERE person_id = cp_person_id AND

Line 24: FROM igs_ad_ps_appl_inst_all aplinst

20: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
21: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
22: ) IS
23: SELECT aplinst.rowid, aplinst.*
24: FROM igs_ad_ps_appl_inst_all aplinst
25: WHERE person_id = cp_person_id AND
26: admission_appl_number = cp_admission_appl_number AND
27: nominated_course_cd = cp_nominated_course_cd AND
28: sequence_number = cp_sequence_number;

Line 201: p_adm_offer_defr_status OUT NOCOPY igs_ad_ps_appl_inst_all.adm_offer_dfrmnt_status%TYPE,

197: p_def_adm_cal_type IN igs_ad_offresp_int.def_adm_cal_type%TYPE ,
198: p_def_adm_ci_sequence_number IN igs_ad_offresp_int.def_adm_ci_sequence_number%TYPE ,
199: p_status IN igs_ad_offresp_int.status%TYPE ,
200: p_prpsd_commencement_date IN igs_ad_offresp_int.prpsd_commencement_date%TYPE,
201: p_adm_offer_defr_status OUT NOCOPY igs_ad_ps_appl_inst_all.adm_offer_dfrmnt_status%TYPE,
202: p_calc_actual_ofr_resp_dt OUT NOCOPY igs_ad_offresp_int.actual_offer_response_dt%TYPE,
203: appl_rec IN igs_ad_appl_all%ROWTYPE,
204: acaiv_rec IN cur_ad_ps_appl_inst%ROWTYPE,
205: p_yes_no IN VARCHAR2,

Line 235: cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,

231: FROM igs_or_institution
232: WHERE institution_cd = l_cd;
233:
234: CURSOR c_appl_inst_ctxt(
235: cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
236: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
237: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
238: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
239: SELECT ci1.start_dt acad_ci_start_dt,

Line 236: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,

232: WHERE institution_cd = l_cd;
233:
234: CURSOR c_appl_inst_ctxt(
235: cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
236: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
237: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
238: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
239: SELECT ci1.start_dt acad_ci_start_dt,
240: ci1.end_dt acad_ci_end_dt,

Line 237: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,

233:
234: CURSOR c_appl_inst_ctxt(
235: cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
236: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
237: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
238: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
239: SELECT ci1.start_dt acad_ci_start_dt,
240: ci1.end_dt acad_ci_end_dt,
241: ci2.start_dt adm_ci_start_dt,

Line 238: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS

234: CURSOR c_appl_inst_ctxt(
235: cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
236: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
237: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
238: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
239: SELECT ci1.start_dt acad_ci_start_dt,
240: ci1.end_dt acad_ci_end_dt,
241: ci2.start_dt adm_ci_start_dt,
242: ci2.end_dt adm_ci_end_dt

Line 299: cp_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE,

295:
296: --Cursor to select the valid Deferred Admission Calendars for the given Academic Calendar
297: CURSOR c_valid_adm_cals( cp_admission_cat igs_ad_appl_all.admission_cat%TYPE,
298: cp_s_adm_process_type igs_ad_appl_all.s_admission_process_type%TYPE,
299: cp_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE,
300: cp_def_acad_ci_seq_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE,
301: cp_adm_ci_end_dt igs_ca_inst.end_dt%TYPE
302: ) IS
303: SELECT

Line 300: cp_def_acad_ci_seq_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE,

296: --Cursor to select the valid Deferred Admission Calendars for the given Academic Calendar
297: CURSOR c_valid_adm_cals( cp_admission_cat igs_ad_appl_all.admission_cat%TYPE,
298: cp_s_adm_process_type igs_ad_appl_all.s_admission_process_type%TYPE,
299: cp_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE,
300: cp_def_acad_ci_seq_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE,
301: cp_adm_ci_end_dt igs_ca_inst.end_dt%TYPE
302: ) IS
303: SELECT
304: ciav1.alternate_code def_adm_alternate_code,

Line 345: igs_ad_ps_appl_inst_all aplinst

341: p_s_admission_process_type igs_ad_appl_all.s_admission_process_type%TYPE) IS
342: SELECT admprd.single_response_flag
343: FROM igs_ad_prd_ad_prc_ca admprd,
344: igs_ad_appl_all appl,
345: igs_ad_ps_appl_inst_all aplinst
346: WHERE appl.person_id = p_person_id
347: AND appl.admission_appl_number = p_admission_appl_number
348: AND appl.person_id = aplinst.person_id
349: AND appl.admission_appl_number = aplinst.admission_appl_number

Line 399: l_applicant_acptnce_cndtn igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE;

395: cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
396: cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
397: cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
398:
399: l_applicant_acptnce_cndtn igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE;
400: l_inst_cd igs_or_institution.institution_cd%TYPE;
401:
402: v_admission_cat igs_ad_appl.admission_cat%TYPE;
403: v_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;

Line 408: v_acaiv_adm_cal_type igs_ad_ps_appl_inst_all.adm_cal_type%TYPE;

404: v_acad_cal_type igs_ad_appl.acad_cal_type%TYPE;
405: v_acad_ci_sequence_number igs_ad_appl.acad_ci_sequence_number%TYPE;
406: v_aa_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
407: v_aa_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
408: v_acaiv_adm_cal_type igs_ad_ps_appl_inst_all.adm_cal_type%TYPE;
409: v_acaiv_adm_ci_sequence_number igs_ad_ps_appl_inst_all.adm_ci_sequence_number%TYPE;
410: v_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
411: v_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
412: v_appl_dt igs_ad_appl.appl_dt%TYPE;

Line 409: v_acaiv_adm_ci_sequence_number igs_ad_ps_appl_inst_all.adm_ci_sequence_number%TYPE;

405: v_acad_ci_sequence_number igs_ad_appl.acad_ci_sequence_number%TYPE;
406: v_aa_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
407: v_aa_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
408: v_acaiv_adm_cal_type igs_ad_ps_appl_inst_all.adm_cal_type%TYPE;
409: v_acaiv_adm_ci_sequence_number igs_ad_ps_appl_inst_all.adm_ci_sequence_number%TYPE;
410: v_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
411: v_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
412: v_appl_dt igs_ad_appl.appl_dt%TYPE;
413: v_adm_appl_status igs_ad_appl.adm_appl_status%TYPE;

Line 417: l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;

413: v_adm_appl_status igs_ad_appl.adm_appl_status%TYPE;
414: v_adm_fee_status igs_ad_appl.adm_fee_status%TYPE;
415: l_single_response_flag igs_ad_prd_ad_prc_ca.single_response_flag%TYPE;
416: l_application_id igs_ad_appl_all.application_id%TYPE;
417: l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
418: l_acad_alt_code igs_ca_inst.alternate_code%TYPE;
419: l_adm_alt_code igs_ca_inst.alternate_code%TYPE;
420:
421: BEGIN

Line 446: -- Check if Interface Offer Response status = Production table (IGS_AD_PS_APPL_INST_ALL) Offer Response Status.

442:
443:
444: /********** VALIDATIONS WHICH CHECK FOR THE PROPER COMBINATION OF INTERFACE Vs SYSTEM TABLE Offer Response Status. Stop processing in case of failure *******/
445:
446: -- Check if Interface Offer Response status = Production table (IGS_AD_PS_APPL_INST_ALL) Offer Response Status.
447: /* Hashed this code as part of bug fix for 2631947. The corresponding MNT fix is through 2624637 */
448: /*IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = igs_ad_gen_008.admp_get_saors(acaiv_rec.adm_offer_resp_status) THEN */
449:
450: IF p_adm_offer_resp_status = acaiv_rec.adm_offer_resp_status THEN

Line 872: -- 3. If validations Successful then Update the OSS table IGS_AD_PS_APPL_INST_ALL with Interface Offer Response details.

868: -- Process flow
869: -- 1. Import all the Pending Interface Offer Response records into PL/SQL tables using BULK COLLECT.
870: -- 2. Perform all the validations on each Interface record by calling local procedure validate_off_resp_dtls.
871: -- 3. If any of the validations fail then insert Error record into the interface error table IGS_AD_OFFRESP_ERR.
872: -- 3. If validations Successful then Update the OSS table IGS_AD_PS_APPL_INST_ALL with Interface Offer Response details.
873: -- 4. If the Offer Response status of Application Instance is updated to 'ACCEPTED' then perform the Pre enrollment by calling
874: -- the corresponding job.
875: -- 5. If the record is succesfully imported then Delete the interface record from IGS_AD_OFFRESP_INT.
876: -- 6. If All the interface records for the given batch_id are succesfully imported then delete the Batch record from IGS_AD_OFFRESP_BATCH.

Line 982: l_adm_offer_defr_status igs_ad_ps_appl_inst_all.adm_offer_dfrmnt_status%TYPE;

978: l_request_id NUMBER;
979: l_tot_rec_processed PLS_INTEGER;
980:
981: l_processed_Rec_Stat igs_ad_offresp_int.status%TYPE;
982: l_adm_offer_defr_status igs_ad_ps_appl_inst_all.adm_offer_dfrmnt_status%TYPE;
983: l_validation_success VARCHAR2(1);
984: l_acaiv_rec cur_ad_ps_appl_inst%ROWTYPE;
985: l_appl_rec igs_ad_appl_all%ROWTYPE;
986: l_calc_actual_ofr_resp_dt igs_ad_offresp_int.actual_offer_response_dt%TYPE;