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_EXPORT_TO_OSS dependencies on IGS_UC_APP_CHOICES

Line 7: CURSOR c_upd_ch ( cp_app_no igs_uc_app_choices.app_no%TYPE ,

3:
4:
5:
6: -- Get application choice details to update error code, batch id, export status and requesty id
7: CURSOR c_upd_ch ( cp_app_no igs_uc_app_choices.app_no%TYPE ,
8: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
9: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
10: SELECT ch.ROWID , ch.*
11: FROM igs_uc_app_choices ch

Line 8: cp_choice_no igs_uc_app_choices.choice_no%TYPE,

4:
5:
6: -- Get application choice details to update error code, batch id, export status and requesty id
7: CURSOR c_upd_ch ( cp_app_no igs_uc_app_choices.app_no%TYPE ,
8: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
9: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
10: SELECT ch.ROWID , ch.*
11: FROM igs_uc_app_choices ch
12: WHERE ch.app_no = cp_app_no

Line 9: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS

5:
6: -- Get application choice details to update error code, batch id, export status and requesty id
7: CURSOR c_upd_ch ( cp_app_no igs_uc_app_choices.app_no%TYPE ,
8: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
9: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
10: SELECT ch.ROWID , ch.*
11: FROM igs_uc_app_choices ch
12: WHERE ch.app_no = cp_app_no
13: AND ch.choice_no = cp_choice_no

Line 11: FROM igs_uc_app_choices ch

7: CURSOR c_upd_ch ( cp_app_no igs_uc_app_choices.app_no%TYPE ,
8: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
9: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
10: SELECT ch.ROWID , ch.*
11: FROM igs_uc_app_choices ch
12: WHERE ch.app_no = cp_app_no
13: AND ch.choice_no = cp_choice_no
14: AND ch.ucas_cycle = cp_ucas_cycle;
15: c_upd_ch_rec c_upd_ch%ROWTYPE ;

Line 23: p_choice_no IN igs_uc_app_choices.choice_no%TYPE ,

19:
20:
21: PROCEDURE populate_imp_int (
22: p_app_no IN igs_uc_applicants.app_no%TYPE,
23: p_choice_no IN igs_uc_app_choices.choice_no%TYPE ,
24: p_source_type_id igs_pe_src_types_all.source_type_id%TYPE,
25: p_batch_id NUMBER,
26: p_orgid NUMBER) AS
27: /******************************************************************

Line 52: l_ch_error igs_uc_app_choices.error_code%TYPE ;

48: l_last_updated_by CONSTANT NUMBER := FND_GLOBAL.LOGIN_ID;
49: l_request_id CONSTANT NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
50: l_program_application_id CONSTANT NUMBER := FND_GLOBAL.PROG_APPL_ID;
51: l_program_id CONSTANT NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
52: l_ch_error igs_uc_app_choices.error_code%TYPE ;
53: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
54: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
55: l_aca_cal_type igs_uc_sys_calndrs.aca_cal_type%TYPE ;
56: l_aca_seq_no igs_uc_sys_calndrs.aca_cal_seq_no%TYPE;

Line 53: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;

49: l_request_id CONSTANT NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
50: l_program_application_id CONSTANT NUMBER := FND_GLOBAL.PROG_APPL_ID;
51: l_program_id CONSTANT NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
52: l_ch_error igs_uc_app_choices.error_code%TYPE ;
53: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
54: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
55: l_aca_cal_type igs_uc_sys_calndrs.aca_cal_type%TYPE ;
56: l_aca_seq_no igs_uc_sys_calndrs.aca_cal_seq_no%TYPE;
57: l_adm_cal_type igs_uc_sys_calndrs.adm_cal_type%TYPE ;

Line 54: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;

50: l_program_application_id CONSTANT NUMBER := FND_GLOBAL.PROG_APPL_ID;
51: l_program_id CONSTANT NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
52: l_ch_error igs_uc_app_choices.error_code%TYPE ;
53: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
54: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
55: l_aca_cal_type igs_uc_sys_calndrs.aca_cal_type%TYPE ;
56: l_aca_seq_no igs_uc_sys_calndrs.aca_cal_seq_no%TYPE;
57: l_adm_cal_type igs_uc_sys_calndrs.adm_cal_type%TYPE ;
58: l_adm_seq_no igs_uc_sys_calndrs.adm_cal_seq_no%TYPE ;

Line 64: CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE ,

60: l_update_adm_seq_number igs_ad_ps_appl_inst_int.update_adm_seq_number%TYPE;
61:
62: -- Get the admission application corresponding to the passed choice record
63: -- Smaddali modified this cursor to add check for c.alt_appl_id , bug 2643048 UCFD012
64: CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE ,
65: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
66: SELECT c.admission_appl_number, c.person_id
67: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
68: WHERE a.app_no = b.app_no AND

Line 65: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS

61:
62: -- Get the admission application corresponding to the passed choice record
63: -- Smaddali modified this cursor to add check for c.alt_appl_id , bug 2643048 UCFD012
64: CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE ,
65: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
66: SELECT c.admission_appl_number, c.person_id
67: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
68: WHERE a.app_no = b.app_no AND
69: TO_CHAR(a.app_no) = c.alt_appl_id AND

Line 67: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c

63: -- Smaddali modified this cursor to add check for c.alt_appl_id , bug 2643048 UCFD012
64: CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE ,
65: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
66: SELECT c.admission_appl_number, c.person_id
67: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
68: WHERE a.app_no = b.app_no AND
69: TO_CHAR(a.app_no) = c.alt_appl_id AND
70: a.oss_person_id = c.person_id AND
71: b.choice_no = c.choice_number AND

Line 84: cp_oss_program_code igs_uc_app_choices.oss_program_code%TYPE,

80:
81: -- Get the admission application instance sequence number corresponding to the passed OSS details
82: CURSOR c_match_adm_appl_inst ( cp_person_id igs_ad_appl_all.person_id%TYPE,
83: cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE,
84: cp_oss_program_code igs_uc_app_choices.oss_program_code%TYPE,
85: cp_oss_program_version igs_uc_app_choices.oss_program_version%TYPE,
86: cp_oss_location igs_uc_app_choices.oss_location%TYPE,
87: cp_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE,
88: cp_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE,

Line 85: cp_oss_program_version igs_uc_app_choices.oss_program_version%TYPE,

81: -- Get the admission application instance sequence number corresponding to the passed OSS details
82: CURSOR c_match_adm_appl_inst ( cp_person_id igs_ad_appl_all.person_id%TYPE,
83: cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE,
84: cp_oss_program_code igs_uc_app_choices.oss_program_code%TYPE,
85: cp_oss_program_version igs_uc_app_choices.oss_program_version%TYPE,
86: cp_oss_location igs_uc_app_choices.oss_location%TYPE,
87: cp_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE,
88: cp_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE,
89: cp_unit_set_cd igs_ps_ofr_opt_unit_set_v.unit_set_cd%TYPE,

Line 86: cp_oss_location igs_uc_app_choices.oss_location%TYPE,

82: CURSOR c_match_adm_appl_inst ( cp_person_id igs_ad_appl_all.person_id%TYPE,
83: cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE,
84: cp_oss_program_code igs_uc_app_choices.oss_program_code%TYPE,
85: cp_oss_program_version igs_uc_app_choices.oss_program_version%TYPE,
86: cp_oss_location igs_uc_app_choices.oss_location%TYPE,
87: cp_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE,
88: cp_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE,
89: cp_unit_set_cd igs_ps_ofr_opt_unit_set_v.unit_set_cd%TYPE,
90: cp_us_version_number igs_ps_ofr_opt_unit_set_v.us_version_number%TYPE ) IS

Line 87: cp_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE,

83: cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE,
84: cp_oss_program_code igs_uc_app_choices.oss_program_code%TYPE,
85: cp_oss_program_version igs_uc_app_choices.oss_program_version%TYPE,
86: cp_oss_location igs_uc_app_choices.oss_location%TYPE,
87: cp_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE,
88: cp_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE,
89: cp_unit_set_cd igs_ps_ofr_opt_unit_set_v.unit_set_cd%TYPE,
90: cp_us_version_number igs_ps_ofr_opt_unit_set_v.us_version_number%TYPE ) IS
91: SELECT acai.sequence_number

Line 88: cp_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE,

84: cp_oss_program_code igs_uc_app_choices.oss_program_code%TYPE,
85: cp_oss_program_version igs_uc_app_choices.oss_program_version%TYPE,
86: cp_oss_location igs_uc_app_choices.oss_location%TYPE,
87: cp_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE,
88: cp_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE,
89: cp_unit_set_cd igs_ps_ofr_opt_unit_set_v.unit_set_cd%TYPE,
90: cp_us_version_number igs_ps_ofr_opt_unit_set_v.us_version_number%TYPE ) IS
91: SELECT acai.sequence_number
92: FROM igs_ad_ps_appl_inst acai

Line 153: FROM igs_uc_app_choices a, igs_uc_applicants b

149: CURSOR c_uc_app_ch IS
150: SELECT DISTINCT a.ucas_program_code, a.campus, a.choice_no, a.oss_program_code, a.oss_program_version,
151: a.oss_location, a.point_of_entry, a.deferred, a.oss_attendance_type, a.oss_attendance_mode,
152: a.route_b_pref_round, b.application_source , a.app_no , a.system_code, a.ucas_cycle, a.entry_year, a.entry_month
153: FROM igs_uc_app_choices a, igs_uc_applicants b
154: WHERE a.app_no=b.app_no AND
155: b.app_no=NVL(p_app_no ,b.app_no) AND
156: a.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df
157: WHERE df.system_code = a.system_code) AND

Line 233: CURSOR cur_app_choices(cp_appno igs_uc_app_choices.app_no%TYPE,

229: AND cr.oss_location IS NOT NULL;
230: oss_prog_mapped_rec cur_oss_prog_mapped%ROWTYPE;
231:
232: -- Get the application choice details for updating error code,request id and export status fields
233: CURSOR cur_app_choices(cp_appno igs_uc_app_choices.app_no%TYPE,
234: cp_choiceno igs_uc_app_choices.choice_no%TYPE,
235: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
236: SELECT a.ROWID, a.*
237: FROM igs_uc_app_choices a

Line 234: cp_choiceno igs_uc_app_choices.choice_no%TYPE,

230: oss_prog_mapped_rec cur_oss_prog_mapped%ROWTYPE;
231:
232: -- Get the application choice details for updating error code,request id and export status fields
233: CURSOR cur_app_choices(cp_appno igs_uc_app_choices.app_no%TYPE,
234: cp_choiceno igs_uc_app_choices.choice_no%TYPE,
235: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
236: SELECT a.ROWID, a.*
237: FROM igs_uc_app_choices a
238: WHERE a.app_no = cp_appno

Line 235: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS

231:
232: -- Get the application choice details for updating error code,request id and export status fields
233: CURSOR cur_app_choices(cp_appno igs_uc_app_choices.app_no%TYPE,
234: cp_choiceno igs_uc_app_choices.choice_no%TYPE,
235: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
236: SELECT a.ROWID, a.*
237: FROM igs_uc_app_choices a
238: WHERE a.app_no = cp_appno
239: AND a.choice_no = cp_choiceno

Line 237: FROM igs_uc_app_choices a

233: CURSOR cur_app_choices(cp_appno igs_uc_app_choices.app_no%TYPE,
234: cp_choiceno igs_uc_app_choices.choice_no%TYPE,
235: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
236: SELECT a.ROWID, a.*
237: FROM igs_uc_app_choices a
238: WHERE a.app_no = cp_appno
239: AND a.choice_no = cp_choiceno
240: AND a.ucas_cycle = cp_ucas_cycle;
241: app_choices_rec cur_app_choices%ROWTYPE;

Line 603: igs_uc_app_choices_pkg.update_row

599: OPEN cur_app_choices(j.app_no, j.choice_no, j.ucas_cycle);
600: FETCH cur_app_choices INTO app_choices_rec;
601: CLOSE cur_app_choices;
602:
603: igs_uc_app_choices_pkg.update_row
604: ( x_rowid => app_choices_rec.ROWID
605: ,x_app_choice_id => app_choices_rec.app_choice_id
606: ,x_app_id => app_choices_rec.app_id
607: ,x_app_no => app_choices_rec.app_no

Line 749: p_choice_no igs_uc_app_choices.choice_no%TYPE

745: END import_process;
746:
747: PROCEDURE obsolete_applications(
748: p_app_no igs_uc_applicants.app_no%TYPE ,
749: p_choice_no igs_uc_app_choices.choice_no%TYPE
750: ) IS
751: /******************************************************************
752: Created By : smaddali
753: Date Created By : 12-SEP-2002

Line 790: l_ch_error igs_uc_app_choices.error_code%TYPE ;

786: l_count NUMBER ;
787: l_new_appl BOOLEAN ;
788: l_new_appl_inst BOOLEAN ;
789: l_cancel_appl BOOLEAN; -- added for bug 4424068
790: l_ch_error igs_uc_app_choices.error_code%TYPE ;
791: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
792: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
793: l_error_message fnd_new_messages.message_text%TYPE; -- Bug 3297241
794: l_return_status VARCHAR2(100) ;

Line 791: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;

787: l_new_appl BOOLEAN ;
788: l_new_appl_inst BOOLEAN ;
789: l_cancel_appl BOOLEAN; -- added for bug 4424068
790: l_ch_error igs_uc_app_choices.error_code%TYPE ;
791: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
792: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
793: l_error_message fnd_new_messages.message_text%TYPE; -- Bug 3297241
794: l_return_status VARCHAR2(100) ;
795: l_pref_excep BOOLEAN ;

Line 792: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;

788: l_new_appl_inst BOOLEAN ;
789: l_cancel_appl BOOLEAN; -- added for bug 4424068
790: l_ch_error igs_uc_app_choices.error_code%TYPE ;
791: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
792: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
793: l_error_message fnd_new_messages.message_text%TYPE; -- Bug 3297241
794: l_return_status VARCHAR2(100) ;
795: l_pref_excep BOOLEAN ;
796:

Line 805: FROM igs_uc_app_choices ch

801: SELECT ch.app_no , ch.choice_no , ch.deferred , ch.batch_id , ch.export_to_oss_status ,
802: ch.point_of_entry , ch.oss_program_code, ch.oss_location ,ch.oss_program_version ,
803: ch.oss_attendance_type ,ch.oss_attendance_mode , ch.system_code, ch.ucas_cycle,
804: ch.entry_year, ch.entry_month, ch.decision, ch.reply
805: FROM igs_uc_app_choices ch
806: WHERE ch.app_no = NVL(p_app_no,ch.app_no) AND
807: ch.export_to_oss_status = 'NEW' AND
808: ch.choice_no = NVL(p_choice_no , ch.choice_no) AND
809: ch.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df

Line 817: FROM igs_uc_app_choices ch

813: -- Get all valid application choices with status OO belonging to current institution
814: -- smaddali modified this cursor to add check for system_code ,bug 2643048 UCFD102 build
815: CURSOR c_oo_ch IS
816: SELECT ch.batch_id, ch.deferred , ch.app_no , ch.choice_no , ch.system_code, ch.ucas_cycle, ch.entry_year, ch.entry_month
817: FROM igs_uc_app_choices ch
818: WHERE ch.app_no = NVL(p_app_no,ch.app_no) AND
819: ch.export_to_oss_status ='OO' AND
820: ch.choice_no = NVL(p_choice_no , ch.choice_no) AND
821: ch.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df

Line 828: CURSOR c_adm_appl( cp_app_no igs_uc_app_choices.app_no%TYPE,

824:
825: -- Get the admission application corresponding to the passed application choice
826: -- i.e if this choice has been previously exported to oss or not
827: -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
828: CURSOR c_adm_appl( cp_app_no igs_uc_app_choices.app_no%TYPE,
829: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
830: SELECT c.person_id , c.admission_appl_number
831: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
832: WHERE a.app_no = b.app_no AND

Line 829: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS

825: -- Get the admission application corresponding to the passed application choice
826: -- i.e if this choice has been previously exported to oss or not
827: -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
828: CURSOR c_adm_appl( cp_app_no igs_uc_app_choices.app_no%TYPE,
829: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
830: SELECT c.person_id , c.admission_appl_number
831: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
832: WHERE a.app_no = b.app_no AND
833: a.oss_person_id = c.person_id AND

Line 831: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c

827: -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
828: CURSOR c_adm_appl( cp_app_no igs_uc_app_choices.app_no%TYPE,
829: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
830: SELECT c.person_id , c.admission_appl_number
831: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
832: WHERE a.app_no = b.app_no AND
833: a.oss_person_id = c.person_id AND
834: TO_CHAR(a.app_no) = c.alt_appl_id AND
835: b.choice_no = c.choice_number AND

Line 842: CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE,

838: c_adm_appl_rec c_adm_appl%ROWTYPE ;
839:
840: -- Get the admission application number of the application corresponding to the passed application choice
841: -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
842: CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE,
843: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
844: SELECT c.person_id,c.admission_appl_number ,b.choice_no
845: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
846: WHERE a.app_no = b.app_no AND

Line 843: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS

839:
840: -- Get the admission application number of the application corresponding to the passed application choice
841: -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
842: CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE,
843: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
844: SELECT c.person_id,c.admission_appl_number ,b.choice_no
845: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
846: WHERE a.app_no = b.app_no AND
847: a.oss_person_id = c.person_id AND

Line 845: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c

841: -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
842: CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE,
843: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
844: SELECT c.person_id,c.admission_appl_number ,b.choice_no
845: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
846: WHERE a.app_no = b.app_no AND
847: a.oss_person_id = c.person_id AND
848: TO_CHAR(a.app_no) = c.alt_appl_id AND
849: b.choice_no = c.choice_number AND

Line 862: CURSOR c_adm_appl_inst (cp_app_no igs_uc_app_choices.app_no%TYPE,

858:
859: -- Get the admission application instance corresponding to the passed application choice
860: -- smaddali added unit set cd and version number parameters and in where clause using these parameters ,bug2630219
861: -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
862: CURSOR c_adm_appl_inst (cp_app_no igs_uc_app_choices.app_no%TYPE,
863: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
864: cp_adm_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
865: cp_unit_set_cd igs_ad_ps_appl_inst.unit_set_cd%TYPE ,
866: cp_us_version_number igs_ad_ps_appl_inst.us_version_number%TYPE ) IS

Line 863: cp_choice_no igs_uc_app_choices.choice_no%TYPE,

859: -- Get the admission application instance corresponding to the passed application choice
860: -- smaddali added unit set cd and version number parameters and in where clause using these parameters ,bug2630219
861: -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
862: CURSOR c_adm_appl_inst (cp_app_no igs_uc_app_choices.app_no%TYPE,
863: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
864: cp_adm_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
865: cp_unit_set_cd igs_ad_ps_appl_inst.unit_set_cd%TYPE ,
866: cp_us_version_number igs_ad_ps_appl_inst.us_version_number%TYPE ) IS
867: SELECT 'X'

Line 868: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c,

864: cp_adm_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
865: cp_unit_set_cd igs_ad_ps_appl_inst.unit_set_cd%TYPE ,
866: cp_us_version_number igs_ad_ps_appl_inst.us_version_number%TYPE ) IS
867: SELECT 'X'
868: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c,
869: igs_ad_ps_appl_all d , igs_ad_ps_appl_inst_all e
870: WHERE a.app_no = b.app_no AND
871: a.oss_person_id = c.person_id AND
872: TO_CHAR(a.app_no) = c.alt_appl_id AND

Line 897: CURSOR c_cancel_appl (cp_app_no igs_uc_app_choices.app_no%TYPE,

893: -- for bug 4424068 - need to check if the oss-program-version has changed for the application
894: -- if the only change in the new choice record is the program version number
895: -- and the current application status is 'RECEIVED', and current outcome status is 'PENDING'/'WITHDRAWN'
896:
897: CURSOR c_cancel_appl (cp_app_no igs_uc_app_choices.app_no%TYPE,
898: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
899: cp_adm_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
900: cp_unit_set_cd igs_ad_ps_appl_inst.unit_set_cd%TYPE ,
901: cp_us_version_number igs_ad_ps_appl_inst.us_version_number%TYPE ) IS

Line 898: cp_choice_no igs_uc_app_choices.choice_no%TYPE,

894: -- if the only change in the new choice record is the program version number
895: -- and the current application status is 'RECEIVED', and current outcome status is 'PENDING'/'WITHDRAWN'
896:
897: CURSOR c_cancel_appl (cp_app_no igs_uc_app_choices.app_no%TYPE,
898: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
899: cp_adm_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
900: cp_unit_set_cd igs_ad_ps_appl_inst.unit_set_cd%TYPE ,
901: cp_us_version_number igs_ad_ps_appl_inst.us_version_number%TYPE ) IS
902: SELECT

Line 907: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c,

903: c.person_id , c.admission_appl_number ,c.choice_number, e.sequence_number,
904: e.decision_date , e.decision_reason_id , e.decision_make_id ,e.adm_outcome_status ,
905: e.nominated_course_cd , c.adm_cal_type , c.adm_ci_sequence_number,
906: c.acad_cal_type , c.acad_ci_sequence_number ,c.admission_cat ,c.s_admission_process_type
907: FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c,
908: igs_ad_ps_appl_all d , igs_ad_ps_appl_inst_all e
909: WHERE a.app_no = b.app_no AND
910: a.oss_person_id = c.person_id AND
911: TO_CHAR(a.app_no) = c.alt_appl_id AND

Line 1085: CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,

1081: WHERE adm_outcome_status = cp_out_stat ;
1082: l_s_obsol_ou_stat igs_ad_ou_stat.s_adm_outcome_status%TYPE ;
1083:
1084: -- Cursor to fetch the latest Transaction
1085: CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,
1086: p_choice_no igs_uc_app_choices.choice_no%TYPE,
1087: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
1088: SELECT transaction_type,program_code,entry_month,entry_year,entry_point
1089: FROM IGS_UC_TRANSACTIONS tran

Line 1086: p_choice_no igs_uc_app_choices.choice_no%TYPE,

1082: l_s_obsol_ou_stat igs_ad_ou_stat.s_adm_outcome_status%TYPE ;
1083:
1084: -- Cursor to fetch the latest Transaction
1085: CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,
1086: p_choice_no igs_uc_app_choices.choice_no%TYPE,
1087: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
1088: SELECT transaction_type,program_code,entry_month,entry_year,entry_point
1089: FROM IGS_UC_TRANSACTIONS tran
1090: WHERE tran.app_no = p_app_no

Line 1087: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS

1083:
1084: -- Cursor to fetch the latest Transaction
1085: CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,
1086: p_choice_no igs_uc_app_choices.choice_no%TYPE,
1087: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
1088: SELECT transaction_type,program_code,entry_month,entry_year,entry_point
1089: FROM IGS_UC_TRANSACTIONS tran
1090: WHERE tran.app_no = p_app_no
1091: AND tran.choice_no = p_choice_no

Line 1098: CURSOR cur_comp_app_choice(p_app_no igs_uc_app_choices.app_no%TYPE ,

1094: cur_latest_trans_rec cur_latest_trans%ROWTYPE;
1095:
1096: -- Cursor to find whether a Completed OSS Admission Application already exist
1097: -- for the UCAS Application Choice
1098: CURSOR cur_comp_app_choice(p_app_no igs_uc_app_choices.app_no%TYPE ,
1099: p_choice_no igs_uc_app_choices.choice_no%TYPE,
1100: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
1101: SELECT apl.person_id,apl.admission_appl_number
1102: FROM IGS_UC_APP_CHOICES uac,

Line 1099: p_choice_no igs_uc_app_choices.choice_no%TYPE,

1095:
1096: -- Cursor to find whether a Completed OSS Admission Application already exist
1097: -- for the UCAS Application Choice
1098: CURSOR cur_comp_app_choice(p_app_no igs_uc_app_choices.app_no%TYPE ,
1099: p_choice_no igs_uc_app_choices.choice_no%TYPE,
1100: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
1101: SELECT apl.person_id,apl.admission_appl_number
1102: FROM IGS_UC_APP_CHOICES uac,
1103: IGS_UC_APPLICANTS ua,

Line 1100: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS

1096: -- Cursor to find whether a Completed OSS Admission Application already exist
1097: -- for the UCAS Application Choice
1098: CURSOR cur_comp_app_choice(p_app_no igs_uc_app_choices.app_no%TYPE ,
1099: p_choice_no igs_uc_app_choices.choice_no%TYPE,
1100: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
1101: SELECT apl.person_id,apl.admission_appl_number
1102: FROM IGS_UC_APP_CHOICES uac,
1103: IGS_UC_APPLICANTS ua,
1104: IGS_AD_APPL_ALL apl,

Line 1102: FROM IGS_UC_APP_CHOICES uac,

1098: CURSOR cur_comp_app_choice(p_app_no igs_uc_app_choices.app_no%TYPE ,
1099: p_choice_no igs_uc_app_choices.choice_no%TYPE,
1100: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
1101: SELECT apl.person_id,apl.admission_appl_number
1102: FROM IGS_UC_APP_CHOICES uac,
1103: IGS_UC_APPLICANTS ua,
1104: IGS_AD_APPL_ALL apl,
1105: IGS_AD_PS_APPL_ALL aplps,
1106: IGS_AD_PS_APPL_INST_ALL aplinst

Line 1126: cp_app_no igs_uc_app_choices.app_no%TYPE,

1122:
1123: -- Cursor to find any change in the UCAS Application Choice with the OSS Admission
1124: -- Application Instance
1125: CURSOR change_in_adm_appl_cur (
1126: cp_app_no igs_uc_app_choices.app_no%TYPE,
1127: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
1128: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,
1129: cp_person_id igs_pe_person.person_id%TYPE,
1130: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE) IS

Line 1127: cp_choice_no igs_uc_app_choices.choice_no%TYPE,

1123: -- Cursor to find any change in the UCAS Application Choice with the OSS Admission
1124: -- Application Instance
1125: CURSOR change_in_adm_appl_cur (
1126: cp_app_no igs_uc_app_choices.app_no%TYPE,
1127: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
1128: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,
1129: cp_person_id igs_pe_person.person_id%TYPE,
1130: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE) IS
1131: SELECT

Line 1128: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,

1124: -- Application Instance
1125: CURSOR change_in_adm_appl_cur (
1126: cp_app_no igs_uc_app_choices.app_no%TYPE,
1127: cp_choice_no igs_uc_app_choices.choice_no%TYPE,
1128: cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,
1129: cp_person_id igs_pe_person.person_id%TYPE,
1130: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE) IS
1131: SELECT
1132: 'X'

Line 1134: igs_uc_app_choices uac,

1130: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE) IS
1131: SELECT
1132: 'X'
1133: FROM
1134: igs_uc_app_choices uac,
1135: igs_uc_sys_calndrs ucal,
1136: igs_ad_appl_all apl,
1137: igs_ad_ps_appl_inst_all aplinst,
1138: igs_en_unit_set us,

Line 1189: FROM igs_uc_app_choices a

1185:
1186: -- to get all the distinct system_codes belonging to the passed application choice parameter
1187: CURSOR c_ch_system IS
1188: SELECT DISTINCT a.system_code, a.entry_year, a.entry_month
1189: FROM igs_uc_app_choices a
1190: WHERE a.app_no = NVL(p_app_no, a.app_no)
1191: AND a.choice_no = NVL(p_choice_no,a.choice_no)
1192: AND a.export_to_oss_status = 'NEW'
1193: AND a.institute_code IN (SELECT df.current_inst_code FROM igs_uc_defaults df);

Line 1222: ( system_code igs_uc_app_choices.system_code%TYPE,

1218: l_apc_det_rec cur_apc_det%ROWTYPE;
1219:
1220: --Record Type to hold the batch_id created for a system cycle calendars.
1221: TYPE batch_det_type IS RECORD
1222: ( system_code igs_uc_app_choices.system_code%TYPE,
1223: entry_year igs_uc_app_choices.entry_year%TYPE,
1224: entry_month igs_uc_app_choices.entry_month%TYPE,
1225: batch_id igs_ad_batc_def_det_all.batch_id%TYPE
1226: );

Line 1223: entry_year igs_uc_app_choices.entry_year%TYPE,

1219:
1220: --Record Type to hold the batch_id created for a system cycle calendars.
1221: TYPE batch_det_type IS RECORD
1222: ( system_code igs_uc_app_choices.system_code%TYPE,
1223: entry_year igs_uc_app_choices.entry_year%TYPE,
1224: entry_month igs_uc_app_choices.entry_month%TYPE,
1225: batch_id igs_ad_batc_def_det_all.batch_id%TYPE
1226: );
1227:

Line 1224: entry_month igs_uc_app_choices.entry_month%TYPE,

1220: --Record Type to hold the batch_id created for a system cycle calendars.
1221: TYPE batch_det_type IS RECORD
1222: ( system_code igs_uc_app_choices.system_code%TYPE,
1223: entry_year igs_uc_app_choices.entry_year%TYPE,
1224: entry_month igs_uc_app_choices.entry_month%TYPE,
1225: batch_id igs_ad_batc_def_det_all.batch_id%TYPE
1226: );
1227:
1228: --Table Type to hold the batch_id created for diferrent system cycle calendars.

Line 1236: PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,

1232: l_batch_id_det batch_det_table_type;
1233: l_batch_id_loc NUMBER;
1234:
1235:
1236: PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,
1237: p_entry_year IN igs_uc_app_choices.entry_year%TYPE,
1238: p_entry_month IN igs_uc_app_choices.entry_month%TYPE,
1239: p_batch_id_loc OUT NOCOPY NUMBER) IS
1240: /******************************************************************

Line 1237: p_entry_year IN igs_uc_app_choices.entry_year%TYPE,

1233: l_batch_id_loc NUMBER;
1234:
1235:
1236: PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,
1237: p_entry_year IN igs_uc_app_choices.entry_year%TYPE,
1238: p_entry_month IN igs_uc_app_choices.entry_month%TYPE,
1239: p_batch_id_loc OUT NOCOPY NUMBER) IS
1240: /******************************************************************
1241: Created By : rbezawad

Line 1238: p_entry_month IN igs_uc_app_choices.entry_month%TYPE,

1234:
1235:
1236: PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,
1237: p_entry_year IN igs_uc_app_choices.entry_year%TYPE,
1238: p_entry_month IN igs_uc_app_choices.entry_month%TYPE,
1239: p_batch_id_loc OUT NOCOPY NUMBER) IS
1240: /******************************************************************
1241: Created By : rbezawad
1242: Date Created By : 14-Jun-03

Line 1983: igs_uc_app_choices_pkg.update_row

1979: c_upd_ch_rec := NULL ;
1980: OPEN c_upd_ch( c_new_app_ch_rec.app_no , c_new_app_ch_rec.choice_no, c_new_app_ch_rec.ucas_cycle);
1981: FETCH c_upd_ch INTO c_upd_ch_rec ;
1982: CLOSE c_upd_ch ;
1983: igs_uc_app_choices_pkg.update_row
1984: ( x_rowid => c_upd_ch_rec.rowid
1985: ,x_app_choice_id => c_upd_ch_rec.app_choice_id
1986: ,x_app_id => c_upd_ch_rec.app_id
1987: ,x_app_no => c_upd_ch_rec.app_no

Line 2116: igs_uc_app_choices_pkg.update_row

2112: c_upd_ch_rec := NULL ;
2113: OPEN c_upd_ch( c_oo_ch_rec.app_no , c_oo_ch_rec.choice_no, c_oo_ch_rec.ucas_cycle) ;
2114: FETCH c_upd_ch INTO c_upd_ch_rec ;
2115: CLOSE c_upd_ch ;
2116: igs_uc_app_choices_pkg.update_row
2117: ( x_rowid => c_upd_ch_rec.ROWID
2118: ,x_app_choice_id => c_upd_ch_rec.app_choice_id
2119: ,x_app_id => c_upd_ch_rec.app_id
2120: ,x_app_no => c_upd_ch_rec.app_no

Line 2197: p_choice_no igs_uc_app_choices.choice_no%TYPE

2193:
2194:
2195: PROCEDURE export_applications(
2196: p_app_no igs_uc_applicants.app_no%TYPE ,
2197: p_choice_no igs_uc_app_choices.choice_no%TYPE
2198: ) IS
2199: /******************************************************************
2200: Created By : smaddali
2201: Date Created By : 12-SEP-2002

Line 2221: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;

2217:
2218: l_app_no IGS_UC_APPLICANTS.APP_NO%TYPE;
2219:
2220: l_imp_batch_id NUMBER;
2221: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
2222: l_ch_error igs_uc_app_choices.error_code%TYPE ;
2223: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
2224: l_conc_process BOOLEAN ;
2225: l_phase VARCHAR2(10) ;

Line 2222: l_ch_error igs_uc_app_choices.error_code%TYPE ;

2218: l_app_no IGS_UC_APPLICANTS.APP_NO%TYPE;
2219:
2220: l_imp_batch_id NUMBER;
2221: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
2222: l_ch_error igs_uc_app_choices.error_code%TYPE ;
2223: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
2224: l_conc_process BOOLEAN ;
2225: l_phase VARCHAR2(10) ;
2226: l_status VARCHAR2(10) ;

Line 2223: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;

2219:
2220: l_imp_batch_id NUMBER;
2221: l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
2222: l_ch_error igs_uc_app_choices.error_code%TYPE ;
2223: l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
2224: l_conc_process BOOLEAN ;
2225: l_phase VARCHAR2(10) ;
2226: l_status VARCHAR2(10) ;
2227: l_dev_phase VARCHAR2(10) ;

Line 2236: FROM igs_uc_app_choices ch

2232: -- Get all the application choices with status AP
2233: -- smaddali modified this cursor to add the validation for system_code , UCFD102 build bug 2643048
2234: CURSOR c_ap_ch IS
2235: SELECT ch.app_no , ch.choice_no , ch.batch_id, ch.ucas_cycle
2236: FROM igs_uc_app_choices ch
2237: WHERE ch.app_no = NVL(p_app_no,ch.app_no) AND
2238: ch.export_to_oss_status = 'AP' AND
2239: ch.choice_no = NVL(p_choice_no , ch.choice_no) AND
2240: ch.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df

Line 2260: CURSOR c_appl_int( cp_batch_id igs_uc_app_choices.batch_id%TYPE,

2256:
2257: -- Get the admission application instance interface record corresponding to the
2258: -- passed ucas application choice record whose import has failed
2259: -- smaddali modified this cursor to add b.alt_appl_id = a.app_no condition as part of ucfd102 build, bug 2643048
2260: CURSOR c_appl_int( cp_batch_id igs_uc_app_choices.batch_id%TYPE,
2261: cp_app_no igs_uc_app_choices.app_no%TYPE ,
2262: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
2263: SELECT a.interface_id -- if application details import fails
2264: FROM igs_ad_interface a, igs_ad_apl_int b , igs_ad_ps_appl_inst_int c ,

Line 2261: cp_app_no igs_uc_app_choices.app_no%TYPE ,

2257: -- Get the admission application instance interface record corresponding to the
2258: -- passed ucas application choice record whose import has failed
2259: -- smaddali modified this cursor to add b.alt_appl_id = a.app_no condition as part of ucfd102 build, bug 2643048
2260: CURSOR c_appl_int( cp_batch_id igs_uc_app_choices.batch_id%TYPE,
2261: cp_app_no igs_uc_app_choices.app_no%TYPE ,
2262: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
2263: SELECT a.interface_id -- if application details import fails
2264: FROM igs_ad_interface a, igs_ad_apl_int b , igs_ad_ps_appl_inst_int c ,
2265: igs_uc_app_choices ch , igs_uc_applicants ap

Line 2262: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS

2258: -- passed ucas application choice record whose import has failed
2259: -- smaddali modified this cursor to add b.alt_appl_id = a.app_no condition as part of ucfd102 build, bug 2643048
2260: CURSOR c_appl_int( cp_batch_id igs_uc_app_choices.batch_id%TYPE,
2261: cp_app_no igs_uc_app_choices.app_no%TYPE ,
2262: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
2263: SELECT a.interface_id -- if application details import fails
2264: FROM igs_ad_interface a, igs_ad_apl_int b , igs_ad_ps_appl_inst_int c ,
2265: igs_uc_app_choices ch , igs_uc_applicants ap
2266: WHERE a.batch_id = cp_batch_id AND

Line 2265: igs_uc_app_choices ch , igs_uc_applicants ap

2261: cp_app_no igs_uc_app_choices.app_no%TYPE ,
2262: cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
2263: SELECT a.interface_id -- if application details import fails
2264: FROM igs_ad_interface a, igs_ad_apl_int b , igs_ad_ps_appl_inst_int c ,
2265: igs_uc_app_choices ch , igs_uc_applicants ap
2266: WHERE a.batch_id = cp_batch_id AND
2267: a.interface_id = b.interface_id AND
2268: b.choice_number = ch.choice_no AND
2269: b.interface_appl_id = c.interface_appl_id AND

Line 2279: igs_uc_app_choices ch , igs_uc_applicants ap

2275: ch.choice_no = cp_choice_no
2276: UNION -- if person or person details import fail
2277: SELECT a.interface_id
2278: FROM igs_ad_interface a, igs_ad_apl_int b , igs_ad_ps_appl_inst_int c ,
2279: igs_uc_app_choices ch , igs_uc_applicants ap
2280: WHERE a.batch_id = cp_batch_id AND
2281: a.interface_id = b.interface_id AND
2282: b.choice_number = ch.choice_no AND
2283: b.interface_appl_id = c.interface_appl_id AND

Line 2340: FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF

2336: -- jchakrab changed c_oc_app_ch to a ref cursor to execute different queries based on parameter values (3691186)
2337: IF p_app_no IS NOT NULL AND p_choice_no IS NOT NULL THEN
2338: OPEN c_oc_app_ch FOR
2339: SELECT DISTINCT AP.APP_NO
2340: FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF
2341: WHERE AP.APP_NO = CH.APP_NO AND
2342: CH.APP_NO = P_APP_NO AND
2343: DF.SYSTEM_CODE = CH.SYSTEM_CODE AND
2344: CH.EXPORT_TO_OSS_STATUS = 'OC' AND

Line 2352: FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF

2348:
2349: ELSIF p_app_no IS NOT NULL AND p_choice_no IS NULL THEN
2350: OPEN c_oc_app_ch FOR
2351: SELECT DISTINCT AP.APP_NO
2352: FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF
2353: WHERE AP.APP_NO = CH.APP_NO AND
2354: CH.APP_NO = P_APP_NO AND
2355: DF.SYSTEM_CODE = CH.SYSTEM_CODE AND
2356: CH.EXPORT_TO_OSS_STATUS = 'OC' AND

Line 2363: FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF

2359:
2360: ELSE
2361: OPEN c_oc_app_ch FOR
2362: SELECT DISTINCT AP.APP_NO
2363: FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF
2364: WHERE AP.APP_NO = CH.APP_NO AND
2365: DF.SYSTEM_CODE = CH.SYSTEM_CODE AND
2366: CH.EXPORT_TO_OSS_STATUS = 'OC' AND
2367: CH.INSTITUTE_CODE = DF.CURRENT_INST_CODE

Line 2567: igs_uc_app_choices_pkg.update_row

2563: c_upd_ch_rec := NULL ;
2564: OPEN c_upd_ch( c_ap_ch_rec.app_no , c_ap_ch_rec.choice_no, c_ap_ch_rec.ucas_cycle) ;
2565: FETCH c_upd_ch INTO c_upd_ch_rec ;
2566: CLOSE c_upd_ch ;
2567: igs_uc_app_choices_pkg.update_row
2568: ( x_rowid => c_upd_ch_rec.ROWID
2569: ,x_app_choice_id => c_upd_ch_rec.app_choice_id
2570: ,x_app_id => c_upd_ch_rec.app_id
2571: ,x_app_no => c_upd_ch_rec.app_no

Line 2645: p_choice_no igs_uc_app_choices.choice_no%TYPE

2641: PROCEDURE main_process(
2642: errbuf OUT NOCOPY VARCHAR2,
2643: retcode OUT NOCOPY NUMBER,
2644: p_app_no igs_uc_applicants.app_no%TYPE ,
2645: p_choice_no igs_uc_app_choices.choice_no%TYPE
2646: ) IS
2647: /******************************************************************
2648: Created By : smaddali
2649: Date Created By : 12-SEP-2002

Line 2666: FROM igs_uc_app_choices a

2662: -- smaddali added this cursor for bug 2643048 ,
2663: -- to get all the distinct system_codes belonging to the passed application choice parameter
2664: CURSOR c_ch_system IS
2665: SELECT DISTINCT a.system_code
2666: FROM igs_uc_app_choices a
2667: WHERE a.app_no = NVL(p_app_no, a.app_no) AND
2668: a.choice_no = NVL(p_choice_no,a.choice_no) ;
2669:
2670: -- smaddali added cursor ,for bug 2643048

Line 2680: FROM igs_uc_app_choices

2676:
2677: --Curosor to get the Entry Year, Entry Month details that are need to check for calendar setup.
2678: CURSOR cur_app_choice_entry_det (cp_system_code igs_uc_defaults.system_code%TYPE) IS
2679: SELECT DISTINCT entry_year, entry_month
2680: FROM igs_uc_app_choices
2681: WHERE app_no = NVL(p_app_no, app_no)
2682: AND choice_no = NVL(p_choice_no, choice_no)
2683: AND system_code = cp_system_code;
2684: