1 PACKAGE BODY igs_uc_export_decision_reply AS
2 /* $Header: IGSUC65B.pls 120.6 2006/06/19 06:04:03 anwest ship $ */
3
4 PROCEDURE export_decision( p_app_no igs_uc_app_choices.app_no%TYPE,
5 p_choice_number igs_uc_app_choices.choice_no%TYPE ) AS
6 /****************************************************************
7 Created By : ayedubat
8 Date Created By : 17-SEP-2002
9 Purpose : This process populates the admissions decision import
10 process interface tables for exporting the UCAS Decision
11 to OSS Application Outcome Status.
12 Known limitations,enhancements,remarks:
13 Change History
14 Who When What
15 Nishikant 01-OCT-2002 A new column extra_round_nbr added in the TBH calls of
16 the package IGS_UC_APP_CHOICES_PKG.
17 ayedubat 20-OCT-2002 Added a Logic to check whether the decision is required to export or not
18 for the bug fix: 2628041
19 smaddali 19-oct-2002 added igs_uc_old_oustat_pkg.delete_row call whenever
20 export_to_oss_status is being set to "DC" and captured record is found ,for bug 2630219
21 ayedubat 13-DEC-2002 Changed the where clause cursor,cur_oss_appl_inst to replace the local
22 variables used to compare the academic and admissions calendars for bug:2708981
23 ayedubat 26-MAR-2003 Changed the procedure to create a new cursor,c_ch_system which is used to
24 create the Decision Import batch ids only for the Systems for which atleast
25 one Application Choice Record exist for the bug: 2669209
26 jchakrab 03-Oct-2005 Modified for 4506750 Impact - added extra filter for IGS_AD_CODE_CLASSES.class_type_code
27 jchin 20-jan-06 Modified for R12 Perf improvements - Bug 3691277 and 3691250
28 jchakrab 22-May-06 Modified for 5165624
29 ******************************************************************/
30
31 -- Cursor to find the details of default UCAS setup defined in the SYSTEM.
32 -- smaddali modified this cursor to add check for system_code , for bug 2643048 UCFD102 build
33 CURSOR cur_ucas_setup( cp_system_code igs_uc_defaults.system_code%TYPE) IS
34 SELECT *
35 FROM igs_uc_defaults
36 WHERE system_code = NVL(cp_system_code,system_code) ;
37 cur_ucas_setup_rec cur_ucas_setup%ROWTYPE;
38
39 -- Cursor to fetch the UCAS Application choices
40 -- If both application number and choice are not passed it fetches all the application choices
41 -- If only application number is passed, it fetches the App. choices of the passed app. number
42 -- If both App. number and Choice are passed, it fetches only one Application
43 -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
44 CURSOR cur_ucas_app_choice IS
45 SELECT uac.*,uac.ROWID
46 FROM igs_uc_app_choices uac,
47 igs_uc_defaults ud
48 WHERE uac.app_no = NVL(p_app_no,uac.app_no)
49 AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
50 AND uac.export_to_oss_status = 'AC'
51 AND ud.system_code = uac.system_code
52 AND ud.current_inst_code = uac.institute_code
53 ORDER BY uac.ucas_cycle, uac.app_no, uac.choice_no ;
54 cur_ucas_app_choice_rec cur_ucas_app_choice%ROWTYPE;
55
56 -- Cursor to find the OSS Application Instance for the current UACS Application Choice
57 -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
58 -- jchin - bug 3691277 and 3691250
59 CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,
60 cp_choice_no igs_uc_app_choices.choice_no%TYPE,
61 cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
62 SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
63 APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,
64 APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,
65 APLINST.CRV_VERSION_NUMBER, APLINST.LOCATION_CD,
66 APLINST.ATTENDANCE_MODE, APLINST.ATTENDANCE_TYPE, APLINST.UNIT_SET_CD,
67 APLINST.US_VERSION_NUMBER, APL.ACAD_CAL_TYPE, UAC.POINT_OF_ENTRY
68 FROM IGS_UC_APP_CHOICES UAC,
69 IGS_UC_APPLICANTS UA,
70 IGS_UC_DEFAULTS UD,
71 IGS_AD_SS_APPL_TYP AAT,
72 IGS_AD_APPL_ALL APL,
73 IGS_AD_PS_APPL_INST_ALL APLINST,
74 IGS_UC_SYS_CALNDRS USC
75 WHERE UAC.APP_NO = CP_APP_NO
76 AND UAC.CHOICE_NO = CP_CHOICE_NO
77 AND UAC.UCAS_CYCLE = CP_UCAS_CYCLE
78 AND UA.APP_NO = UAC.APP_NO
79 AND UA.OSS_PERSON_ID = APL.PERSON_ID
80 AND TO_CHAR (UA.APP_NO) = APL.ALT_APPL_ID
81 AND APL.CHOICE_NUMBER = UAC.CHOICE_NO
82 AND UAC.SYSTEM_CODE = UD.SYSTEM_CODE
83 AND UD.APPLICATION_TYPE = AAT.ADMISSION_APPLICATION_TYPE
84 AND UAC.SYSTEM_CODE = USC.SYSTEM_CODE
85 AND UAC.ENTRY_YEAR = USC.ENTRY_YEAR
86 AND (UAC.ENTRY_MONTH = USC.ENTRY_MONTH OR USC.ENTRY_MONTH = 0)
87 AND APL.ACAD_CAL_TYPE = USC.ACA_CAL_TYPE
88 AND APL.ACAD_CI_SEQUENCE_NUMBER = USC.ACA_CAL_SEQ_NO
89 AND APL.ADM_CAL_TYPE = USC.ADM_CAL_TYPE
90 AND APL.ADM_CI_SEQUENCE_NUMBER = USC.ADM_CAL_SEQ_NO
91 AND APL.ADMISSION_CAT = AAT.ADMISSION_CAT
92 AND APL.S_ADMISSION_PROCESS_TYPE = AAT.S_ADMISSION_PROCESS_TYPE
93 AND APL.PERSON_ID = APLINST.PERSON_ID
94 AND APL.ADMISSION_APPL_NUMBER = APLINST.ADMISSION_APPL_NUMBER
95 AND APLINST.NOMINATED_COURSE_CD = UAC.OSS_PROGRAM_CODE
96 AND APLINST.CRV_VERSION_NUMBER = UAC.OSS_PROGRAM_VERSION
97 AND APLINST.LOCATION_CD = UAC.OSS_LOCATION
98 AND APLINST.ATTENDANCE_MODE = UAC.OSS_ATTENDANCE_MODE
99 AND APLINST.ATTENDANCE_TYPE = UAC.OSS_ATTENDANCE_TYPE;
100 cur_oss_appl_inst_rec cur_oss_appl_inst%ROWTYPE;
101
102 -- Check the existence of unit set code corresponding to the application instance
103 -- jchin - bug 3691277 and 3691250
104 CURSOR cur_unit_set_cd(
105 p_unit_set_cd igs_ad_ps_appl_inst_all.unit_set_cd%TYPE,
106 p_us_version_number igs_ad_ps_appl_inst_all.us_version_number%TYPE,
107 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
108 p_crv_version_number igs_ad_ps_appl_inst_all.crv_version_number%TYPE,
109 p_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE,
110 p_location_cd igs_ad_ps_appl_inst_all.location_cd%TYPE,
111 p_attendance_mode igs_ad_ps_appl_inst_all.attendance_mode%TYPE,
112 p_attendance_type igs_ad_ps_appl_inst_all.attendance_type%TYPE,
113 p_point_of_entry igs_uc_app_choices.point_of_entry%TYPE
114 ) IS
115 SELECT US.UNIT_SET_CD,
116 US.VERSION_NUMBER US_VERSION_NUMBER
117 FROM IGS_PS_OFR_UNIT_SET COUS,
118 IGS_PS_OFR_OPT COO,
119 IGS_EN_UNIT_SET US,
120 IGS_EN_UNIT_SET_CAT USC,
121 IGS_PS_US_PRENR_CFG CFG
122 WHERE COUS.UNIT_SET_CD = P_UNIT_SET_CD
123 AND COUS.US_VERSION_NUMBER = P_US_VERSION_NUMBER
124 AND COUS.COURSE_CD = P_NOMINATED_COURSE_CD
125 AND COUS.CRV_VERSION_NUMBER = P_CRV_VERSION_NUMBER
126 AND COUS.CAL_TYPE = P_ACAD_CAL_TYPE
127 AND COO.LOCATION_CD = P_LOCATION_CD
128 AND COO.ATTENDANCE_MODE = P_ATTENDANCE_MODE
129 AND COO.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
130 AND COO.COURSE_CD = COUS.COURSE_CD
131 AND COO.VERSION_NUMBER = COUS.CRV_VERSION_NUMBER
132 AND COO.CAL_TYPE = COUS.CAL_TYPE
133 AND US.UNIT_SET_CD = COUS.UNIT_SET_CD
134 AND US.VERSION_NUMBER = COUS.US_VERSION_NUMBER
135 AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
136 AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
137 AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
138 AND CFG.SEQUENCE_NO = NVL(P_POINT_OF_ENTRY,1)
139 AND NOT EXISTS (SELECT COURSE_CD FROM IGS_PS_OF_OPT_UNT_ST COOUS WHERE COOUS.COO_ID = COO.COO_ID)
140 UNION ALL
141 SELECT US.UNIT_SET_CD,
142 US.VERSION_NUMBER US_VERSION_NUMBER
143 FROM IGS_PS_OF_OPT_UNT_ST COOUS,
144 IGS_EN_UNIT_SET US,
145 IGS_EN_UNIT_SET_CAT USC,
146 IGS_PS_US_PRENR_CFG CFG
147 WHERE COOUS.UNIT_SET_CD = P_UNIT_SET_CD
148 AND COOUS.US_VERSION_NUMBER = P_US_VERSION_NUMBER
149 AND COOUS.COURSE_CD = P_NOMINATED_COURSE_CD
150 AND COOUS.CRV_VERSION_NUMBER = P_CRV_VERSION_NUMBER
151 AND COOUS.CAL_TYPE = P_ACAD_CAL_TYPE
152 AND COOUS.LOCATION_CD = P_LOCATION_CD
153 AND COOUS.ATTENDANCE_MODE = P_ATTENDANCE_MODE
154 AND COOUS.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
155 AND US.UNIT_SET_CD = COOUS.UNIT_SET_CD
156 AND US.VERSION_NUMBER = COOUS.US_VERSION_NUMBER
157 AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
158 AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
159 AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
160 AND CFG.SEQUENCE_NO = NVL(P_POINT_OF_ENTRY,1);
161 cur_unit_set_cd_rec cur_unit_set_cd%ROWTYPE;
162
163
164 -- Cursor to fetch the previous Outcome Status details captured for the current Application Choice
165 CURSOR cur_prev_ou_captured( p_app_no IGS_UC_APP_CHOICES.APP_NO%TYPE,
166 p_choice_no IGS_UC_APP_CHOICES.CHOICE_NO%TYPE ) IS
167 SELECT uoc.*, uoc.ROWID
168 FROM igs_uc_old_oustat uoc
169 WHERE uoc.app_no = p_app_no
170 AND uoc.choice_no = p_choice_no;
171 cur_prev_ou_captured_rec cur_prev_ou_captured%ROWTYPE;
172
173 -- Cursor to fetch the latest decision setting Transaction
174 CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,
175 p_choice_no igs_uc_app_choices.choice_no%TYPE,
176 p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
177 SELECT *
178 FROM igs_uc_transactions tran
179 WHERE tran.app_no = p_app_no
180 AND tran.choice_no = p_choice_no
181 AND tran.ucas_cycle = p_ucas_cycle
182 AND tran.transaction_type IN ( 'LA','LD','RA','RD','RX' )
183 ORDER BY tran.uc_tran_id DESC;
184 cur_latest_trans_rec cur_latest_trans%ROWTYPE;
185
186 -- Cursor to Check whether there exists any un processed transaction
187 CURSOR cur_unprocess_trans_exist( p_app_no igs_uc_app_choices.app_no%TYPE,
188 p_choice_no igs_uc_app_choices.choice_no%TYPE,
189 p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
190 SELECT 'X'
191 FROM igs_uc_transactions tran
192 WHERE tran.app_no = p_app_no
193 AND tran.choice_no = p_choice_no
194 AND tran.ucas_cycle = p_ucas_cycle
195 AND tran.transaction_type IN ( 'LA','LD','RA','RD','RX' )
196 AND ( NVL(tran.sent_to_ucas,'N') = 'N' OR tran.error_code <> 0 ) ;
197 cur_unprocess_trans_exist_rec cur_unprocess_trans_exist%ROWTYPE;
198
199 -- Cursor to find the OSS User Outcome Status mapped to the defaulted Decision
200 -- of the current Application Instance
201 --smaddali modified cursor to add check for system_code ,for bug 2643048 UCFD102 build
202 CURSOR cur_ou_mapping ( p_decision IGS_UC_MAP_OUT_STAT.DECISION_CODE%TYPE,
203 p_system_code igs_uc_map_out_stat.system_code%TYPE) IS
204 SELECT mos.adm_outcome_status
205 FROM igs_uc_map_out_stat mos
206 WHERE mos.system_code = p_system_code
207 AND mos.decision_code = p_decision
208 AND mos.default_ind = 'Y'
209 AND mos.closed_ind <> 'Y' ;
210 cur_ou_mapping_rec cur_ou_mapping%ROWTYPE;
211
212 -- Cursor to fetch the System Admission Outcome status of the
213 -- current OSS Application Instance outcome status
214 CURSOR cur_s_adm_ou_stat(p_adm_out_status IGS_AD_OU_STAT.ADM_OUTCOME_STATUS%TYPE) IS
215 SELECT s_adm_outcome_status
216 FROM igs_ad_ou_stat
217 WHERE adm_outcome_status = p_adm_out_status;
218 cur_s_adm_ou_stat_rec cur_s_adm_ou_stat%ROWTYPE;
219
220 -- Cursor to find the the value of Reconsideration Flag
221 -- from the Admission Application,igs_ad_ps_appl_all
222 CURSOR cur_recons_flag ( p_person_id IGS_AD_PS_APPL_ALL.PERSON_ID%TYPE,
223 p_admission_appl_number IGS_AD_PS_APPL_ALL.ADMISSION_APPL_NUMBER%TYPE,
224 p_nominated_course_code IGS_AD_PS_APPL_ALL.NOMINATED_COURSE_CD%TYPE ) IS
225 SELECT req_for_reconsideration_ind
226 FROM igs_ad_ps_appl_all
227 WHERE person_id = p_person_id
228 AND admission_appl_number = p_admission_appl_number
229 AND nominated_course_cd = p_nominated_course_code;
230 cur_recons_flag_rec cur_recons_flag%ROWTYPE;
231
232 -- Cursor to fetch all the Application Choices of the current institution which are
233 -- errored out in the previous decision import process
234 -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
235 CURSOR cur_dp_app_choice IS
236 SELECT uac.*,uac.ROWID
237 FROM igs_uc_app_choices uac,
238 igs_uc_defaults ud
239 WHERE uac.app_no = NVL( p_app_no,uac.app_no)
240 AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
241 AND uac.batch_id IS NOT NULL
242 AND uac.export_to_oss_status = 'DP'
243 AND uac.institute_code = ud.current_inst_code
244 AND uac.system_code = ud.system_code ;
245 cur_dp_app_choice_rec cur_dp_app_choice%ROWTYPE ;
246
247 -- Cursor to check for the errorin the import decision process
248 CURSOR cur_dec_import_error( p_batch_id IGS_AD_BATC_DEF_DET_ALL.batch_id%TYPE ,
249 p_person_id IGS_AD_ADMDE_INT_ALL.person_id%TYPE,
250 p_admission_appl_number IGS_AD_ADMDE_INT_ALL.admission_appl_number%TYPE,
251 p_nominated_course_cd IGS_AD_ADMDE_INT_ALL.nominated_course_cd%TYPE,
252 p_sequence_number IGS_AD_ADMDE_INT_ALL.sequence_number%TYPE ) IS
253 SELECT error_code, status
254 FROM igs_ad_admde_int_all
255 WHERE batch_id = p_batch_id
256 AND person_id = p_person_id
257 AND admission_appl_number = p_admission_appl_number
258 AND nominated_course_cd = p_nominated_course_cd
259 AND sequence_number = p_sequence_number
260 AND ( status IN ('3','2') OR error_code IS NOT NULL ) ;
261 cur_dec_import_error_rec cur_dec_import_error%ROWTYPE ;
262
263 -- Cursor to fetch the record Admission Decision import Interface table
264 CURSOR cur_admde_interface ( p_interface_mkdes_id IGS_AD_ADMDE_INT_ALL.interface_mkdes_id%TYPE ) IS
265 SELECT *
266 FROM igs_ad_admde_int_all
267 WHERE interface_mkdes_id = p_interface_mkdes_id ;
268 cur_admde_interface_rec cur_admde_interface%ROWTYPE;
269
270 --Cursor to fetch the record from Admission Decision import Interface batch table
271 CURSOR cur_batc_def_det ( p_batch_id IGS_AD_BATC_DEF_DET_ALL.batch_id%TYPE ) IS
272 SELECT *
273 FROM igs_ad_batc_def_det_all
274 WHERE batch_id = p_batch_id ;
275 cur_batc_def_det_rec cur_batc_def_det%ROWTYPE ;
276
277 -- Cursor to fetch the default Pending Reason
278 -- used when the Bulk reject reset by UCAS to PENDING
279 CURSOR cur_pending_reason IS
280 SELECT code_id
281 FROM igs_ad_code_classes
282 WHERE class = 'PENDING_REASON'
283 AND system_default = 'Y'
284 AND class_type_code = 'ADM_CODE_CLASSES';
285 cur_pending_reason_rec cur_pending_reason%ROWTYPE ;
286
287 -- Cursor selecte a value from Sequence,IGS_AD_INTERFACE_CTL_S */
288 CURSOR cur_interface_ctl_s IS
289 SELECT igs_ad_interface_ctl_s.NEXTVAL
290 FROM DUAL ;
291
292 -- to get all the distinct system_codes belonging to the passed application choice parameter
293 CURSOR c_ch_system IS
294 SELECT DISTINCT a.system_code, a.entry_year, a.entry_month
295 FROM igs_uc_app_choices a,
296 igs_uc_defaults ud
297 WHERE a.app_no = NVL(p_app_no, a.app_no)
298 AND a.choice_no = NVL(p_choice_number,a.choice_no)
299 AND a.export_to_oss_status = 'AC'
300 AND ud.system_code = a.system_code
301 AND ud.current_inst_code = a.institute_code;
302
303 --Cursor to get the Calendar details for the given System, Entry Month and Entry Year.
304 CURSOR cur_sys_entry_cal_det ( cp_system_code igs_uc_sys_calndrs.system_code%TYPE,
305 cp_entry_year igs_uc_sys_calndrs.entry_year%TYPE,
306 cp_entry_month igs_uc_sys_calndrs.entry_month%TYPE ) IS
307 SELECT aca_cal_type,
308 aca_cal_seq_no,
309 adm_cal_type,
310 adm_cal_seq_no
311 FROM igs_uc_sys_calndrs sc
312 WHERE sc.system_code = cp_system_code
313 AND sc.entry_year = cp_entry_year
314 AND sc.entry_month = cp_entry_month;
315
316 l_sys_entry_cal_det_rec cur_sys_entry_cal_det%ROWTYPE;
317
318 --Cursor to get the Admission Process Category and Admission Process Type for the
319 --Admission Application Type defined for the System in UCAS Setup.
320 CURSOR cur_apc_det ( cp_application_type igs_uc_defaults.application_type%TYPE) IS
321 SELECT admission_cat, s_admission_process_type
322 FROM igs_ad_ss_appl_typ
323 WHERE admission_application_type = cp_application_type
324 AND closed_ind = 'N';
325
326 l_apc_det_rec cur_apc_det%ROWTYPE;
327
328 -- Define the Local Variables
329 l_conc_request_id NUMBER(15);
330 l_app_inst_sys_adm_ou_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
331 l_oss_ou_status_of_app_choice igs_ad_ou_stat.adm_outcome_status%TYPE;
332 l_latest_trans_decision igs_uc_transactions.decision%TYPE;
333
334 l_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
335 l_deffered_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
336 l_current_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
337
338 l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
339 l_app_choice_error_code igs_uc_app_choices.error_code%TYPE ;
340
341 l_rowid VARCHAR2(25);
342 l_interface_mkdes_id igs_ad_admde_int_all.interface_mkdes_id%TYPE ;
343 l_interface_run_id igs_ad_admde_int_all.interface_run_id%TYPE ;
344 l_return_status VARCHAR2(10) ;
345 l_error_message fnd_new_messages.message_text%TYPE ;
346 l_description VARCHAR2(2000);
347
348 l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
349 l_reconsideration_flag igs_ad_ps_appl.req_for_reconsideration_ind%TYPE;
350
351 --Record Type to hold the batch_id created for a system cycle calendars.
352 TYPE batch_det_type IS RECORD
353 ( system_code igs_uc_app_choices.system_code%TYPE,
354 entry_year igs_uc_app_choices.entry_year%TYPE,
355 entry_month igs_uc_app_choices.entry_month%TYPE,
356 batch_id igs_ad_batc_def_det_all.batch_id%TYPE
357 );
358
359 --Table Type to hold the batch_id created for diferrent system cycle calendars.
360 TYPE batch_det_table_type IS TABLE OF batch_det_type INDEX BY BINARY_INTEGER;
361
362 --Table/Collection variable to hold the records for batch ids created of diferrent system, cycle and calendars.
363 l_batch_id_det batch_det_table_type;
364 l_batch_id_loc NUMBER;
365
366
367 PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,
368 p_entry_year IN igs_uc_app_choices.entry_year%TYPE,
369 p_entry_month IN igs_uc_app_choices.entry_month%TYPE,
370 p_batch_id_loc OUT NOCOPY NUMBER) IS
371 /******************************************************************
372 Created By : rbezawad
373 Date Created By : 16-Jun-03
374 Purpose : Local Procedure to export_decision() procedure, which retuns the Batch ID location
375 in pl/sql table(l_batch_id_det) of Batch ID for passed parameter criteria.
376 Known limitations,enhancements,remarks:
377 Change History
378 Who When What
379 rbezawad 24-Jul-2003 Done modifications to retrieve the batch id location based on system code, entry year and entry month.
380 Modifications are done as part of UCCR007 and UCCR203 enhancement, Bug No: 3022067.
381 ***************************************************************** */
382 BEGIN
383
384 -- Search for the Batch ID location only when the PL/SQL table has some data.
385 IF l_batch_id_det.FIRST IS NOT NULL AND l_batch_id_det.LAST IS NOT NULL THEN
386
387 --Loop through the pl/sql table and check for the values.
388 FOR l_loc IN l_batch_id_det.FIRST..l_batch_id_det.LAST LOOP
389 IF l_batch_id_det(l_loc).system_code = p_system_code AND
390 l_batch_id_det(l_loc).entry_year = p_entry_year AND
391 l_batch_id_det(l_loc).entry_month = p_entry_month THEN
392 --If the Batch ID found for the matching parameters then return the location of batch id in to out parameter p_batch_id_loc.
393 p_batch_id_loc := l_loc;
394 EXIT;
395 END IF;
396 END LOOP;
397
398 END IF;
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
403 fnd_message.set_token('NAME','IGS_UC_EXPORT_DECISION_REPLY.GET_BATCHID_LOC'||' - '||SQLERRM);
404 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
405 App_Exception.Raise_Exception;
406
407 END get_batchid_loc;
408
409
410 BEGIN
411
412 -- Initialize all the local variables
413 l_app_inst_sys_adm_ou_status := NULL ;
414 l_oss_ou_status_of_app_choice := NULL ;
415 l_latest_trans_decision := NULL ;
416
417 l_batch_id := NULL ;
418 l_deffered_batch_id := NULL ;
419 l_current_batch_id := NULL ;
420
421 l_export_to_oss_status := NULL ;
422 l_app_choice_error_code := NULL ;
423
424 l_interface_mkdes_id := NULL ;
425 l_interface_run_id := NULL ;
426 l_return_status := NULL ;
427 l_error_message := NULL ;
428 l_batch_id_loc := 0;
429
430 -- Get the Concurrent Request ID of the current export of UCAS application to
431 -- OSS admission applications run
432 l_conc_request_id := fnd_global.conc_request_id();
433
434 FOR c_ch_system_rec IN c_ch_system LOOP
435
436 -- Insert a record into the Admission Decision Import Batch table,IGS_AD_BATC_DEF_DET_ALL
437 -- for the deffered and current academic/admission calendar session details for each of the ucas systems
438 -- This Batch ID will be used while populating the Admission Decision Import Process Interface Table
439 FOR cur_ucas_setup_rec IN cur_ucas_setup(c_ch_system_rec.system_code) LOOP
440
441 --Get the APC details corresponding to the Application Type defined in UCAS Setup
442 OPEN cur_apc_det(cur_ucas_setup_rec.application_type);
443 FETCH cur_apc_det INTO l_apc_det_rec;
444 CLOSE cur_apc_det;
445
446 -- We need to create a separate batch id for each of the UCAS System's calendars
447 -- Get the Batch ID Description value from the Message,IGS_UC_DEC_BATCH
448 fnd_message.set_name('IGS','IGS_UC_DEC_BATCH');
449 l_description := fnd_message.get;
450 l_rowid := NULL ;
451 l_batch_id := NULL;
452
453 --Get the Calendar details for the given System, Entry Month and Entry Year from System Calendards table.
454 l_sys_entry_cal_det_rec := NULL;
455 OPEN cur_sys_entry_cal_det (c_ch_system_rec.system_code, c_ch_system_rec.entry_year, c_ch_system_rec.entry_month);
456 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
457 --If no matching Entry Year and Entry Month record for the system is found in the System Calendars table then
458 -- get the calendar details from the IGS_UC_SYS_CALNDRS table based on the system, Entry Year and Entry Month as 0 (Zero).
459 IF cur_sys_entry_cal_det%NOTFOUND THEN
460 CLOSE cur_sys_entry_cal_det;
461 OPEN cur_sys_entry_cal_det(c_ch_system_rec.system_code, c_ch_system_rec.entry_year, 0);
462 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
463 END IF;
464 CLOSE cur_sys_entry_cal_det;
465
466 -- We need to create a separate batch id for each of the calendar setup available for UCAS System, Entry Year and Entry Month details.
467 igs_ad_batc_def_det_pkg.insert_row(
468 x_rowid => l_rowid,
469 x_batch_id => l_batch_id,
470 x_description => l_description,
471 x_acad_cal_type => l_sys_entry_cal_det_rec.aca_cal_type,
472 x_acad_ci_sequence_number => l_sys_entry_cal_det_rec.aca_cal_seq_no,
473 x_adm_cal_type => l_sys_entry_cal_det_rec.adm_cal_type,
474 x_adm_ci_sequence_number => l_sys_entry_cal_det_rec.adm_cal_seq_no,
475 x_admission_cat => l_apc_det_rec.admission_cat,
476 x_s_admission_process_type => l_apc_det_rec.s_admission_process_type,
477 x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
478 x_decision_date => SYSDATE,
479 x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
480 x_pending_reason_id => NULL,
481 x_offer_dt => NULL,
482 x_offer_response_dt => NULL,
483 x_mode => 'R' );
484
485 --Store the information of Batch ID created into a pl/sql table
486 l_batch_id_det(l_batch_id_loc).system_code := c_ch_system_rec.system_code;
487 l_batch_id_det(l_batch_id_loc).entry_year := c_ch_system_rec.entry_year;
488 l_batch_id_det(l_batch_id_loc).entry_month := c_ch_system_rec.entry_month;
489 l_batch_id_det(l_batch_id_loc).batch_id := l_batch_id;
490 l_batch_id_loc := l_batch_id_loc + 1;
491
492 END LOOP ;
493
494 END LOOP ;
495
496 /* Process all the Application Choice records of the passed p_app_no,p_choice_number
497 and of the current institution with Export_to_oss Status = "AC" */
498 FOR cur_ucas_app_choice_rec IN cur_ucas_app_choice LOOP
499
500 -- Initialize the variable at the start of each application choice
501 -- which will be used to update the application choice record at the end of processing
502
503 l_export_to_oss_status := NULL ;
504 l_app_choice_error_code := NULL ;
505 l_ch_batch_id := NULL ;
506 l_interface_mkdes_id := NULL ;
507 l_interface_run_id := NULL ;
508 l_rowid := NULL ;
509
510 -- Fetch the default values setup for the UCAS SYSTEM to which this application choice belongs to,
511 -- These values will be used in this procedure wherever default values requires
512 cur_ucas_setup_rec := NULL ;
513 OPEN cur_ucas_setup(cur_ucas_app_choice_rec.system_code) ;
514 FETCH cur_ucas_setup INTO cur_ucas_setup_rec;
515 CLOSE cur_ucas_setup;
516
517 -- 1. Get the Batch ID of the Admission Decision Import Batch table,IGS_AD_BATC_DEF_DET_ALL
518 -- smaddali modified the code to get the batch id corresponding to the UCAS system ,for UCFD102 build , bug 2643048
519 l_batch_id := NULL ;
520 l_batch_id_loc :=NULL;
521 get_batchid_loc(p_system_code => cur_ucas_app_choice_rec.system_code,
522 p_entry_year => cur_ucas_app_choice_rec.entry_year,
523 p_entry_month => cur_ucas_app_choice_rec.entry_month,
524 p_batch_id_loc => l_batch_id_loc);
525 IF l_batch_id_loc IS NOT NULL THEN
526 l_batch_id := l_batch_id_det(l_batch_id_loc).batch_id;
527 END IF;
528
529 -- Identify whether the Application Instance for the current Application Choice Exist or not
530 OPEN cur_oss_appl_inst( cur_ucas_app_choice_rec.app_no,
531 cur_ucas_app_choice_rec.choice_no,
532 cur_ucas_app_choice_rec.ucas_cycle);
533 FETCH cur_oss_appl_inst INTO cur_oss_appl_inst_rec ;
534
535 IF cur_oss_appl_inst%NOTFOUND THEN
536 -- update UCAS Application Chocie record to set error Code to 'D002'
537 l_app_choice_error_code := 'D002' ;
538 l_export_to_oss_status := cur_ucas_app_choice_rec.export_to_oss_status ;
539 l_ch_batch_id := NULL ;
540 -- Write the error message to the Log file indicating the error occurred
541 fnd_message.set_name('IGS','IGS_UC_APP_INST_NOTFOUND');
542 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
543 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
544 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
545
546 ELSE /* OSS Application Instance Found */
547
548 -- jchin - bug 3691277 and 3691250
549 -- added check whether the matching unit-set-code exists for the identofi application instance
550 OPEN cur_unit_set_cd(cur_oss_appl_inst_rec.unit_set_cd,
551 cur_oss_appl_inst_rec.us_version_number,
552 cur_oss_appl_inst_rec.nominated_course_cd,
553 cur_oss_appl_inst_rec.crv_version_number,
554 cur_oss_appl_inst_rec.acad_cal_type,
555 cur_oss_appl_inst_rec.location_cd,
556 cur_oss_appl_inst_rec.attendance_mode,
557 cur_oss_appl_inst_rec.attendance_type,
558 cur_oss_appl_inst_rec.point_of_entry);
559 FETCH cur_unit_set_cd INTO cur_unit_set_cd_rec;
560
561 IF cur_unit_set_cd%NOTFOUND THEN
562 -- update UCAS Application Chocie record to set error Code to 'D002'
563 l_app_choice_error_code := 'D002' ;
564 l_export_to_oss_status := cur_ucas_app_choice_rec.export_to_oss_status ;
565 l_ch_batch_id := NULL ;
566 -- Write the error message to the Log file indicating the error occurred
567 fnd_message.set_name('IGS','IGS_UC_APP_INST_NOTFOUND');
568 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
569 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
570 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
571 ELSE /* OSS Application Instance and unit-set-code found */
572
573 -- Get the System Admission Outcome status of the current admission Application Outcome status
574 -- This will be used in the following code
575 cur_s_adm_ou_stat_rec := NULL ;
576 OPEN cur_s_adm_ou_stat( cur_oss_appl_inst_rec.adm_outcome_status );
577 FETCH cur_s_adm_ou_stat INTO cur_s_adm_ou_stat_rec;
578 CLOSE cur_s_adm_ou_stat;
579 l_app_inst_sys_adm_ou_status := cur_s_adm_ou_stat_rec.s_adm_outcome_status;
580
581 -- Get the Default OSS admission Outcome status of the Application choice decision
582 -- This will be used in the following code
583 cur_ou_mapping_rec := NULL ;
584 OPEN cur_ou_mapping ( cur_ucas_app_choice_rec.decision,cur_ucas_app_choice_rec.system_code );
585 FETCH cur_ou_mapping INTO cur_ou_mapping_rec;
586 CLOSE cur_ou_mapping;
587 l_oss_ou_status_of_app_choice := cur_ou_mapping_rec.adm_outcome_status;
588
589 -- Find the latest Decision setting UCAS transaction of the Application Choice
590 -- This will be used in the following code
591 cur_latest_trans_rec := NULL ;
592 OPEN cur_latest_trans( cur_ucas_app_choice_rec.app_no,
593 cur_ucas_app_choice_rec.choice_no,
594 cur_ucas_app_choice_rec.ucas_cycle);
595 FETCH cur_latest_trans INTO cur_latest_trans_rec;
596 CLOSE cur_latest_trans;
597
598 --anwest 06-JUN-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
599 IF cur_latest_trans_rec.transaction_type = 'RA' THEN
600 l_latest_trans_decision := cur_ucas_app_choice_rec.decision;
601 ELSE
602 l_latest_trans_decision := cur_latest_trans_rec.decision;
603 END IF;
604
605 -- Check whether the previous Outcome details are captured for the current Application Instance or not
606 -- in IGS_UC_OLD_OUSTAT Table
607 OPEN cur_prev_ou_captured( cur_ucas_app_choice_rec.app_no,
608 cur_ucas_app_choice_rec.choice_no );
609 FETCH cur_prev_ou_captured INTO cur_prev_ou_captured_rec;
610
611
612 /*** Logic for: Previous Outcome details are captured
613 That is if the previous Outcome details are captured for the current Application Instance
614 in IGS_UC_OLD_OUSTAT Table, then import the application choice decision */
615 IF cur_prev_ou_captured%FOUND THEN
616
617 -- If Previous Outcome details are captured and there is no trasaction decision found,
618 -- the raise the error with error Code to 'D005'
619 IF l_latest_trans_decision IS NULL THEN --anwest 06-JUN-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
620
621 -- Update the Appchoice record with export_to_oss_status as 'DC'
622 l_export_to_oss_status := 'DC' ;
623 l_app_choice_error_code := NULL ;
624 l_ch_batch_id := NULL ;
625 -- smaddali added igs_uc_old_oustat_pkg.delete_row call for bug 2630219
626 -- 4. Delete the corresponding record from IGS_UC_OLD_OUSTAT table for current Application and Choice
627 igs_uc_old_oustat_pkg.delete_row(
628 X_ROWID => cur_prev_ou_captured_rec.ROWID ) ;
629
630 -- Write the error message to the Log file indicating the error occurred
631 fnd_message.set_name('IGS','IGS_UC_TRAN_NOT_FOUND');
632 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no) );
633 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no) );
634 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
635
636 ELSE /* Latest Transaction was found */
637
638 --anwest 06-JUN-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
639 IF cur_latest_trans_rec.transaction_type = 'RD' AND l_latest_trans_decision = 'C' THEN
640 -- Find the OSS User Outcome Status mapped to the tapplication choice decision
641 OPEN cur_ou_mapping (cur_ucas_app_choice_rec.decision, cur_ucas_app_choice_rec.system_code);
642 FETCH cur_ou_mapping INTO cur_ou_mapping_rec ;
643 ELSE
644 -- Find the OSS User Outcome Status mapped to the latest transaction Decision of the Application Choice
645 OPEN cur_ou_mapping ( l_latest_trans_decision , cur_ucas_app_choice_rec.system_code ) ;
646 FETCH cur_ou_mapping INTO cur_ou_mapping_rec ;
647 END IF;
648
649 -- If the mapping of Transaction Decision to Default OSS User Outcome Status is defined
650 IF cur_ou_mapping%FOUND THEN
651
652 CLOSE cur_ou_mapping ;
653 -- If previous Captured Outcome status is equal to transaction decision mapped User Outthe status
654 -- and the previous Captured Outcome status is 'PENDING',
655 -- then no need to import the Decision, so update the Application Choice status to 'DC'
656 OPEN cur_s_adm_ou_stat( cur_prev_ou_captured_rec.old_outcome_status ) ;
657 FETCH cur_s_adm_ou_stat INTO cur_s_adm_ou_stat_rec ;
658 CLOSE cur_s_adm_ou_stat ;
659
660 IF cur_ou_mapping_rec.adm_outcome_status = cur_prev_ou_captured_rec.old_outcome_status AND
661 cur_s_adm_ou_stat_rec.s_adm_outcome_status = 'PENDING' THEN
662
663 -- Update the Appchoice record with export_to_oss_status as 'DC'
664 l_export_to_oss_status := 'DC' ;
665 l_app_choice_error_code := NULL ;
666 l_ch_batch_id := NULL ;
667
668 -- smaddali added igs_uc_old_oustat_pkg.delete_row call for bug 2630219
669 -- 4. Delete the corresponding record from IGS_UC_OLD_OUSTAT table for current Application and Choice
670 igs_uc_old_oustat_pkg.delete_row(
671 x_rowid => cur_prev_ou_captured_rec.ROWID ) ;
672
673 ELSE /* Previous Captured Outcome status is not 'PENDING' */
674
675 /* Populate the Admission Decision Import Interface tables with the default OSS user outcome status
676 corresponding to UCAS latest decision setting Transaction Decision */
677
678 -- 2. Populate the Admission Decision Import Interface table,IGS_AD_ADMDE_INT_ALL as,
679
680 /*Populate the Admission Decision Import Interface tables with default OSS user outcome status
681 mapped to the UCAS Transaction Decision.
682
683 If the UCAS Transaction Decision maps to the captured OSS user outcome Status or
684 the latest transaction setting decision is an RD or an RA transaction and
685 the decision in the transaction maps to the captured OSS outcome status,
686 then Populate the Decision_maker, decision_reason and Decision Date details from
687 IGS_UC_OLD_OUSTAT table and Application Instance information from CUR_OSS_APPL_INST_REC
688 Else,Populate the Decision_maker, decision_reason,Decision_date details from UCAS Setup
689 and Application Instance information CUR_OSS_APPL_INST_REC.
690
691 The offer_dt should be set to the decision date from the igs_uc_app_choices for the choice being
692 imported unless the outcome status is being synchronized with an unprocessed UCAS transaction
693 in which case it should be set to the transaction date created. */
694
695 /* Get the Interface Run ID value from Sequence */
696 OPEN cur_interface_ctl_s ;
697 FETCH cur_interface_ctl_s INTO l_interface_run_id;
698 CLOSE cur_interface_ctl_s;
699
700 -- Derive the Reconsideration Flag value based on the Admission outcome status to be changed. This Reconsideration
701 -- Flag value will be stored in igs_ad_admde_int_all table and used for exporting UCAS decisions to OSS.
702 l_reconsideration_flag := NULL;
703 IF igs_ad_gen_008.admp_get_saos(cur_ou_mapping_rec.adm_outcome_status) IN ('REJECTED','NO-QUOTA') THEN
704 -- Set the Reconsideration Flat to 'Y' for allowing institutions to process UCAS applications seemlessly.
705 l_reconsideration_flag := 'Y';
706 ELSE
707 -- When Application Decision to be changed is not REJECTED or NO-QUOTA then there is no need to set the flag.
708 l_reconsideration_flag := 'N';
709 END IF;
710
711 --anwest 06-JUN-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
712 IF cur_ou_mapping_rec.adm_outcome_status = cur_prev_ou_captured_rec.old_outcome_status THEN
713
714 l_rowid := NULL ;
715 igs_ad_admde_int_pkg.insert_row (
716 x_rowid => l_rowid,
717 x_interface_mkdes_id => l_interface_mkdes_id,
718 x_interface_run_id => l_interface_run_id,
719 x_batch_id => l_batch_id,
720 x_person_id => cur_oss_appl_inst_rec.person_id,
721 x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
722 x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
723 x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
724 x_adm_outcome_status => cur_ou_mapping_rec.adm_outcome_status,
725 x_decision_make_id => cur_prev_ou_captured_rec.decision_make_id,
726 x_decision_date => cur_prev_ou_captured_rec.decision_date,
727 x_decision_reason_id => cur_prev_ou_captured_rec.decision_reason_id,
728 x_pending_reason_id => NULL,
729 x_offer_dt => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
730 x_offer_response_dt => NULL,
731 x_status => '2', --Pending Status
732 x_error_code => NULL,
733 x_mode => 'R',
734 x_reconsider_flag => l_reconsideration_flag );
735
736 ELSE
737
738 l_rowid := NULL ;
739 igs_ad_admde_int_pkg.insert_row (
740 x_rowid => l_rowid,
741 x_interface_mkdes_id => l_interface_mkdes_id,
742 x_interface_run_id => l_interface_run_id,
743 x_batch_id => l_batch_id,
744 x_person_id => cur_oss_appl_inst_rec.person_id,
745 x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
746 x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
747 x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
748 x_adm_outcome_status => cur_ou_mapping_rec.adm_outcome_status,
749 x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
750 x_decision_date => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
751 x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
752 x_pending_reason_id => NULL,
753 x_offer_dt => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
754 x_offer_response_dt => NULL,
755 x_status => '2', --Pending Status
756 x_error_code => NULL,
757 x_mode => 'R',
758 x_reconsider_flag => l_reconsideration_flag );
759
760 END IF;
761
762 -- 3. Update the Application Choice Record with export_to_oss_status = 'DP' and Concurrent Request ID
763 -- Assign DP' to local variable,l_export_to_oss_status and update the Application Choice Record at the end
764 l_export_to_oss_status := 'DP';
765 l_app_choice_error_code := NULL ;
766 l_ch_batch_id := NULL ;
767 -- 4. Delete the corresponding record from IGS_UC_OLD_OUSTAT table for current Application and Choice Number.
768 igs_uc_old_oustat_pkg.delete_row(
769 x_rowid => cur_prev_ou_captured_rec.ROWID ) ;
770
771 END IF; /* End of checking the outcome status as 'PENDING' */
772
773 ELSE /* raise the error with code 'D006' */
774
775 CLOSE cur_ou_mapping;
776 l_app_choice_error_code := 'D006' ;
777 l_export_to_oss_status := cur_ucas_app_choice_rec.export_to_oss_status;
778 l_ch_batch_id := NULL ;
779
780 -- Write the error message to the Log file indicating the error occurred
781 fnd_message.set_name('IGS','IGS_UC_TRANS_DEC_NOT_MAPPED');
782 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no ));
783 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
784 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
785
786 END IF; /* End of mapping found */
787
788 END IF; /* Latest Transaction Found */
789
790
791 /*** Logic for: Bulk Reject by UACAS
792 That is, The Application Choice decision is 'R'(Reject) and Action flag set to 'R' */
793 ELSIF cur_ucas_app_choice_rec.decision = 'R' AND cur_ucas_app_choice_rec.action = 'R' THEN
794
795 -- Check weather there exists any unprocessed transaction
796 OPEN cur_unprocess_trans_exist( cur_ucas_app_choice_rec.app_no,
797 cur_ucas_app_choice_rec.choice_no,
798 cur_ucas_app_choice_rec.ucas_cycle);
799 FETCH cur_unprocess_trans_exist INTO cur_unprocess_trans_exist_rec;
800
801 -- If the System Admission Outcome status of the Application instance is 'PENDING' or
802 -- no unprocessed decision setting transaction exists
803 IF l_app_inst_sys_adm_ou_status = 'PENDING' AND cur_unprocess_trans_exist%NOTFOUND THEN
804
805 /* Populate the Admission Decision Import Interface tables with the default OSS user outcome status
806 with system outcome status type of 'REJECTED' from the UCAS setup */
807
808 --1. Insert a record into Admission Decision Import Interface table,IGS_AD_ADMDE_INT_ALL
809
810 /* Get the Interface Run ID value from Sequence */
811 OPEN cur_interface_ctl_s ;
812 FETCH cur_interface_ctl_s INTO l_interface_run_id;
813 CLOSE cur_interface_ctl_s;
814
815 -- Derive the Reconsideration Flag value based on the Admission outcome status to be changed. This Reconsideration
816 -- Flag value will be stored in igs_ad_admde_int_all table and used for exporting UCAS decisions to OSS.
817 l_reconsideration_flag := NULL;
818 IF igs_ad_gen_008.admp_get_saos(cur_ucas_setup_rec.rejected_outcome_status) IN ('REJECTED','NO-QUOTA') THEN
819 -- Set the Reconsideration Flat to 'Y' for allowing institutions to process UCAS applications seemlessly.
820 l_reconsideration_flag := 'Y';
821 ELSE
822 -- When Application Decision to be changed is not REJECTED or NO-QUOTA then there is no need to set the flag.
823 l_reconsideration_flag := 'N';
824 END IF;
825
826 /* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
827 igs_ad_admde_int_pkg.insert_row (
828 x_rowid => l_rowid,
829 x_interface_mkdes_id => l_interface_mkdes_id,
830 x_interface_run_id => l_interface_run_id,
831 x_batch_id => l_batch_id,
832 x_person_id => cur_oss_appl_inst_rec.person_id,
833 x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
834 x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
835 x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
836 x_adm_outcome_status => cur_ucas_setup_rec.rejected_outcome_status,
837 x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
838 x_decision_date => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
839 x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
840 x_pending_reason_id => NULL,
841 x_offer_dt => NULL,
842 x_offer_response_dt => NULL,
843 x_status => '2', --Pending Status
844 x_error_code => NULL,
845 x_mode => 'R',
846 x_reconsider_flag => l_reconsideration_flag );
847
848 -- 3. Update the Application Choice Record with export_to_oss_status='DP' and Concurrent Request ID
849 l_export_to_oss_status := 'DP';
850 l_app_choice_error_code := NULL ;
851 l_ch_batch_id := NULL ;
852
853 ELSE /* Raise the error with error_code ='D007' */
854
855 l_app_choice_error_code := 'D007' ;
856 l_export_to_oss_status := cur_ucas_app_choice_rec.export_to_oss_status;
857 l_ch_batch_id := NULL ;
858
859 -- Write the error message to the Log file indicating the error occurred
860 fnd_message.set_name('IGS','IGS_UC_EXP_BULK_REJ_DEC_FAIL');
861 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
862 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
863 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
864
865 END IF ;
866 CLOSE cur_unprocess_trans_exist ;
867
868
869 /*** Logic for: Bulk Reject is reset by UCAS
870 That is, if the Application Choice decision is NULL and Action flag IS NULL and the Application
871 Instance system Outocme status is 'REJECTED' and If there is any transaction exists, then
872 it should be either the latest decision setting transaction
873 is processed by UCAS or transaction decision is 'R' then, import the Admission decision */
874 ELSIF cur_ucas_app_choice_rec.decision IS NULL AND
875 cur_ucas_app_choice_rec.action IS NULL AND
876 l_app_inst_sys_adm_ou_status = 'REJECTED' AND
877 ( l_latest_trans_decision IS NULL OR -- transaction deos not exist
878 ( l_latest_trans_decision = 'R' OR
879 ( cur_latest_trans_rec.sent_to_ucas = 'Y' AND cur_latest_trans_rec.error_code = 0 ))) THEN
880
881 /* Populate the Admission Decision Import Interface tables with the default OSS user outcome status
882 with system outcome status type of 'PENDING' */
883
884 -- 1. Insert a record into Admission Decision Import Interface table,IGS_AD_ADMDE_INT_ALL
885
886 /* Get the Pending Reason ID */
887 OPEN cur_pending_reason;
888 FETCH cur_pending_reason INTO cur_pending_reason_rec;
889 CLOSE cur_pending_reason;
890
891 /* Get the Interface Run ID value from Sequence */
892 OPEN cur_interface_ctl_s ;
893 FETCH cur_interface_ctl_s INTO l_interface_run_id ;
894 CLOSE cur_interface_ctl_s ;
895
896 -- Derive the Reconsideration Flag value based on the Admission outcome status to be changed. This Reconsideration
897 -- Flag value will be stored in igs_ad_admde_int_all table and used for exporting UCAS decisions to OSS.
898 l_reconsideration_flag := NULL;
899 IF igs_ad_gen_008.admp_get_saos(cur_ucas_setup_rec.pending_outcome_status) IN ('REJECTED','NO-QUOTA') THEN
900 -- Set the Reconsideration Flat to 'Y' for allowing institutions to process UCAS applications seemlessly.
901 l_reconsideration_flag := 'Y';
902 ELSE
903 -- When Application Decision to be changed is not REJECTED or NO-QUOTA then there is no need to set the flag.
904 l_reconsideration_flag := 'N';
905 END IF;
906
907 /* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
908 igs_ad_admde_int_pkg.insert_row (
909 x_rowid => l_rowid,
910 x_interface_mkdes_id => l_interface_mkdes_id,
911 x_interface_run_id => l_interface_run_id,
912 x_batch_id => l_batch_id,
913 x_person_id => cur_oss_appl_inst_rec.person_id,
914 x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
915 x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
916 x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
917 x_adm_outcome_status => cur_ucas_setup_rec.pending_outcome_status,
918 x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
919 x_decision_date => TRUNC(cur_ucas_app_choice_rec.decision_date),
920 x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
921 x_pending_reason_id => cur_pending_reason_rec.code_id,
922 x_offer_dt => NULL,
923 x_offer_response_dt => NULL,
924 x_status => '2', --Pending Status
925 x_error_code => NULL,
926 x_mode => 'R',
927 x_reconsider_flag => l_reconsideration_flag );
928
929 -- 3. Update the Application Choice Record with export_to_oss_status='DP' and Concurrent Request ID
930 l_export_to_oss_status := 'DP';
931 l_app_choice_error_code := NULL ;
932 l_ch_batch_id := NULL ;
933
934 /*** Logic For: Check whether the decision is NULL, required to export or not */
935 ELSIF cur_ucas_app_choice_rec.decision IS NULL AND
936 l_latest_trans_decision IS NULL AND
937 l_app_inst_sys_adm_ou_status = 'PENDING' THEN
938
939 -- Update the Appchoice record with export_to_oss_status as 'DC'
940 l_export_to_oss_status := 'DC' ;
941 l_app_choice_error_code := NULL ;
942 l_ch_batch_id := NULL ;
943
944 /*** Lgogic For: OSS admission Outcome status of the Application choice decision is not equal to
945 the Application Instances admission Outcome status */
946 /* NVL is added because even if the value of l_oss_ou_status_of_app_choice is NULL, condition should be processed */
947 ELSIF NVL(l_oss_ou_status_of_app_choice,' ') <> cur_oss_appl_inst_rec.adm_outcome_status THEN
948
949 -- Get the Reconsideration flag of the Admission Application
950 OPEN cur_recons_flag ( cur_oss_appl_inst_rec.person_id,
951 cur_oss_appl_inst_rec.admission_appl_number,
952 cur_oss_appl_inst_rec.nominated_course_cd ) ;
953 FETCH cur_recons_flag INTO cur_recons_flag_rec;
954 CLOSE cur_recons_flag;
955
956 -- If the System Admission outcome status is one of 'REJECTED','VOIDED', 'WITHDRAWN' and
957 -- request for reconsideration flag is not set to 'Y' then raise error
958 IF l_app_inst_sys_adm_ou_status IN ( 'REJECTED', 'VOIDED', 'WITHDRAWN' ) AND
959 cur_recons_flag_rec.req_for_reconsideration_ind <> 'Y' THEN
960 /* Raise the error with code 'D004' */
961 l_app_choice_error_code := 'D004' ;
962 l_export_to_oss_status := cur_ucas_app_choice_rec.export_to_oss_status;
963 l_ch_batch_id := NULL ;
964 -- Write the error message to the Log file indicating the error occurred
965 fnd_message.set_name('IGS','IGS_UC_APP_INST_COMPLETED');
966 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
967 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
968 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
969
970 ELSE
971
972 -- If the latest decision setting transaction is processed successfully,
973 -- then populate the OSS admission outcome ststaus corresponding to Application Choice
974 IF cur_latest_trans_rec.sent_to_ucas = 'Y' AND cur_latest_trans_rec.error_code = 0 THEN
975
976 IF l_oss_ou_status_of_app_choice IS NOT NULL THEN
977 /* Populate the Admission Decision Import Interface tables with the default OSS user outcome status
978 corresponding to Application Choice Decision */
979
980 -- 1. Insert a record into Admission Decision Import Interface table,IGS_AD_ADMDE_INT_ALL
981 /* Get the Interface Run ID value from Sequence */
982 OPEN cur_interface_ctl_s ;
983 FETCH cur_interface_ctl_s INTO l_interface_run_id;
984 CLOSE cur_interface_ctl_s;
985
986 -- Derive the Reconsideration Flag value based on the Admission outcome status to be changed. This Reconsideration
987 -- Flag value will be stored in igs_ad_admde_int_all table and used for exporting UCAS decisions to OSS.
988 l_reconsideration_flag := NULL;
989 IF igs_ad_gen_008.admp_get_saos(l_oss_ou_status_of_app_choice) IN ('REJECTED','NO-QUOTA') THEN
990 -- Set the Reconsideration Flat to 'Y' for allowing institutions to process UCAS applications seemlessly.
991 l_reconsideration_flag := 'Y';
992 ELSE
993 -- When Application Decision to be changed is not REJECTED or NO-QUOTA then there is no need to set the flag.
994 l_reconsideration_flag := 'N';
995 END IF;
996
997 /* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
998 igs_ad_admde_int_pkg.insert_row (
999 x_rowid => l_rowid,
1000 x_interface_mkdes_id => l_interface_mkdes_id,
1001 x_interface_run_id => l_interface_run_id,
1002 x_batch_id => l_batch_id,
1003 x_person_id => cur_oss_appl_inst_rec.person_id,
1004 x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
1005 x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
1006 x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
1007 x_adm_outcome_status => l_oss_ou_status_of_app_choice,
1008 x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
1009 x_decision_date => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
1010 x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
1011 x_pending_reason_id => NULL,
1012 x_offer_dt => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
1013 x_offer_response_dt => NULL,
1014 x_status => '2', --Pending Status
1015 x_error_code => NULL,
1016 x_mode => 'R',
1017 x_reconsider_flag => l_reconsideration_flag );
1018
1019 -- 3. Update the Application Choice Record with export_to_oss_status='DP' and Concurrent Request ID
1020 l_export_to_oss_status := 'DP';
1021 l_app_choice_error_code := NULL ;
1022 l_ch_batch_id := NULL ;
1023
1024 ELSE /* raise the error with code 'D003' */
1025 l_app_choice_error_code := 'D003' ;
1026 l_export_to_oss_status := cur_ucas_app_choice_rec.export_to_oss_status;
1027 l_ch_batch_id := NULL ;
1028
1029 -- Write the error message to the Log file indicating the error occurred
1030 fnd_message.set_name('IGS','IGS_UC_APPCH_DEC_NOT_MAPPED');
1031 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
1032 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
1033 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1034 END IF;
1035
1036 ELSE /* If the latest decision setting transaction is NOT processed successfully,
1037 then populate the OSS admission outcome ststaus corresponding to UCAS Transaction Decision*/
1038
1039 -- Find the OSS User Outcome Status mapped to the Latest Transaction Decision
1040 OPEN cur_ou_mapping ( l_latest_trans_decision ,cur_ucas_app_choice_rec.system_code);
1041 FETCH cur_ou_mapping INTO cur_ou_mapping_rec;
1042 -- If the mapping of Transaction Decision to Default OSS User Outcome Status is defined
1043 IF cur_ou_mapping%FOUND THEN
1044 /* Populate the Admission Decision Import Interface tables with the default OSS user outcome status
1045 corresponding to UCAS Transaction Decision */
1046 -- 1. Insert a record into Admission Decision Import Interface table,IGS_AD_ADMDE_INT_ALL
1047
1048 /* Get the Interface Run ID value from Sequence */
1049 OPEN cur_interface_ctl_s ;
1050 FETCH cur_interface_ctl_s INTO l_interface_run_id;
1051 CLOSE cur_interface_ctl_s;
1052
1053 -- Derive the Reconsideration Flag value based on the Admission outcome status to be changed. This Reconsideration
1054 -- Flag value will be stored in igs_ad_admde_int_all table and used for exporting UCAS decisions to OSS.
1055 l_reconsideration_flag := NULL;
1056 IF igs_ad_gen_008.admp_get_saos(cur_ou_mapping_rec.adm_outcome_status) IN ('REJECTED','NO-QUOTA') THEN
1057 -- Set the Reconsideration Flat to 'Y' for allowing institutions to process UCAS applications seemlessly.
1058 l_reconsideration_flag := 'Y';
1059 ELSE
1060 -- When Application Decision to be changed is not REJECTED or NO-QUOTA then there is no need to set the flag.
1061 l_reconsideration_flag := 'N';
1062 END IF;
1063
1064 /* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
1065 igs_ad_admde_int_pkg.insert_row (
1066 x_rowid => l_rowid,
1067 x_interface_mkdes_id => l_interface_mkdes_id,
1068 x_interface_run_id => l_interface_run_id,
1069 x_batch_id => l_batch_id,
1070 x_person_id => cur_oss_appl_inst_rec.person_id,
1071 x_admission_appl_number => cur_oss_appl_inst_rec.admission_appl_number,
1072 x_nominated_course_cd => cur_oss_appl_inst_rec.nominated_course_cd,
1073 x_sequence_number => cur_oss_appl_inst_rec.sequence_number,
1074 x_adm_outcome_status => cur_ou_mapping_rec.adm_outcome_status,
1075 x_decision_make_id => cur_ucas_setup_rec.decision_make_id,
1076 x_decision_date => TRUNC(NVL(cur_ucas_app_choice_rec.decision_date, SYSDATE)),
1077 x_decision_reason_id => cur_ucas_setup_rec.decision_reason_id,
1078 x_pending_reason_id => NULL,
1079 x_offer_dt => TRUNC(cur_latest_trans_rec.creation_date), --Populate the Transaction Date
1080 x_offer_response_dt => NULL,
1081 x_status => '2', -- pending status
1082 x_error_code => NULL,
1083 x_mode => 'R',
1084 x_reconsider_flag => l_reconsideration_flag );
1085
1086 -- 3. Update the Application Choice Record with export_to_oss_status='DP' and Concurrent Request ID
1087 l_export_to_oss_status := 'DP';
1088 l_app_choice_error_code := NULL ;
1089 l_ch_batch_id := NULL ;
1090
1091 ELSE /* raise the error with code 'D006' */
1092
1093 l_app_choice_error_code := 'D006' ;
1094 l_export_to_oss_status := cur_ucas_app_choice_rec.export_to_oss_status;
1095 l_ch_batch_id := NULL ;
1096
1097 -- Write the error message to the Log file indicating the error occurred
1098 fnd_message.set_name('IGS','IGS_UC_TRANS_DEC_NOT_MAPPED');
1099 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
1100 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
1101 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1102
1103 END IF;
1104 CLOSE cur_ou_mapping;
1105
1106 END IF; /* end of the latest decision setting transaction logic */
1107
1108 END IF; /* End of the System Admission outcome status is one of 'REJECTED','VOIDED', 'WITHDRAWN' */
1109
1110 /*** Logic For: If all the above four conditions are failed */
1111 ELSE /* then no need to impot the Admission decision. Change the export to OSS status to 'DC' */
1112
1113 -- Update the Appchoice record with export_to_oss_status as 'DC'
1114 l_export_to_oss_status := 'DC' ;
1115 l_app_choice_error_code := NULL ;
1116 l_ch_batch_id := NULL ;
1117
1118 END IF; /* end of Previous Outcome details process */
1119 CLOSE cur_prev_ou_captured;
1120
1121 -- jchin - 3691277 and 3691250
1122 END IF; /* End If for cursor cur_unit_set_cd%NOTFOUND */
1123 CLOSE cur_unit_set_cd;
1124
1125
1126 END IF; /* End of processing the current OSS Application Instance */
1127 CLOSE cur_oss_appl_inst;
1128
1129 /* ********* End of populating the Admission Decision Import Interface Table *********/
1130
1131 /* If the Application choice status is changed to 'DP', then Call the Admission Decision
1132 Import Process which update the outcome decision for an application */
1133 IF l_export_to_oss_status = 'DP' THEN
1134
1135 -- Fetch the populated record from the Import Interface Table
1136 OPEN cur_admde_interface ( l_interface_mkdes_id ) ;
1137 FETCH cur_admde_interface INTO cur_admde_interface_rec ;
1138 CLOSE cur_admde_interface ;
1139
1140 OPEN cur_batc_def_det ( cur_admde_interface_rec.batch_id ) ;
1141 FETCH cur_batc_def_det INTO cur_batc_def_det_rec ;
1142 CLOSE cur_batc_def_det ;
1143
1144 -- Find the System Admission Outcome status
1145 OPEN cur_s_adm_ou_stat( cur_admde_interface_rec.adm_outcome_status ) ;
1146 FETCH cur_s_adm_ou_stat INTO cur_s_adm_ou_stat_rec ;
1147 CLOSE cur_s_adm_ou_stat ;
1148
1149 l_reconsideration_flag := NULL;
1150 IF cur_s_adm_ou_stat_rec.s_adm_outcome_status IN ('REJECTED','NO-QUOTA') THEN
1151 l_reconsideration_flag := 'Y';
1152 ELSE
1153 l_reconsideration_flag := 'N';
1154 END IF;
1155
1156 /* Admission Decision Import Process Call */
1157
1158 igs_ad_imp_adm_des.prc_adm_outcome_status(
1159 p_person_id => cur_admde_interface_rec.person_id ,
1160 p_admission_appl_number => cur_admde_interface_rec.admission_appl_number ,
1161 p_nominated_course_cd => cur_admde_interface_rec.nominated_course_cd ,
1162 p_sequence_number => cur_admde_interface_rec.sequence_number ,
1163 p_adm_outcome_status => cur_admde_interface_rec.adm_outcome_status ,
1164 p_s_adm_outcome_status => cur_s_adm_ou_stat_rec.s_adm_outcome_status ,
1165 p_acad_cal_type => cur_batc_def_det_rec.acad_cal_type ,
1166 p_acad_ci_sequence_number => cur_batc_def_det_rec.acad_ci_sequence_number ,
1167 p_adm_cal_type => cur_batc_def_det_rec.adm_cal_type ,
1168 p_adm_ci_sequence_number => cur_batc_def_det_rec.adm_ci_sequence_number ,
1169 p_admission_cat => cur_batc_def_det_rec.admission_cat ,
1170 p_s_admission_process_type => cur_batc_def_det_rec.s_admission_process_type ,
1171 p_batch_id => cur_admde_interface_rec.batch_id ,
1172 p_interface_run_id => cur_admde_interface_rec.interface_run_id ,
1173 p_interface_mkdes_id => cur_admde_interface_rec.interface_mkdes_id ,
1174 p_error_message => l_error_message, -- Replaced error_code with error_message Bug 3297241
1175 p_return_status => l_return_status,
1176 p_ucas_transaction => 'Y',
1177 p_reconsideration => l_reconsideration_flag);
1178
1179 IF l_return_status = 'FALSE' AND l_error_message IS NOT NULL THEN
1180
1181 /* raise the error with code 'D001' */
1182 l_app_choice_error_code :='D001' ;
1183 l_export_to_oss_status :='DP';
1184 l_ch_batch_id := l_batch_id ;
1185
1186 -- Write the error message to the Log file indicating the error occurred
1187 fnd_message.set_name('IGS','IGS_UC_DEC_IMP_ERR');
1188 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
1189 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
1190 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1191
1192 ELSE /* If l_error_message IS NULL, that means Decision Process was successfull */
1193 l_app_choice_error_code := NULL ;
1194 l_export_to_oss_status :='DC';
1195 l_ch_batch_id := NULL ;
1196
1197 END IF ;
1198
1199 END IF ;
1200
1201 /* Update the Application choice record with the error code, batch_id and status */
1202
1203 igs_uc_app_choices_pkg.update_row
1204 ( x_rowid => cur_ucas_app_choice_rec.ROWID
1205 ,x_app_choice_id => cur_ucas_app_choice_rec.app_choice_id
1206 ,x_app_id => cur_ucas_app_choice_rec.app_id
1207 ,x_app_no => cur_ucas_app_choice_rec.app_no
1208 ,x_choice_no => cur_ucas_app_choice_rec.choice_no
1209 ,x_last_change => cur_ucas_app_choice_rec.last_change
1210 ,x_institute_code => cur_ucas_app_choice_rec.institute_code
1211 ,x_ucas_program_code => cur_ucas_app_choice_rec.ucas_program_code
1212 ,x_oss_program_code => cur_ucas_app_choice_rec.oss_program_code
1213 ,x_oss_program_version => cur_ucas_app_choice_rec.oss_program_version
1214 ,x_oss_attendance_type => cur_ucas_app_choice_rec.oss_attendance_type
1215 ,x_oss_attendance_mode => cur_ucas_app_choice_rec.oss_attendance_mode
1216 ,x_campus => cur_ucas_app_choice_rec.campus
1217 ,x_oss_location => cur_ucas_app_choice_rec.oss_location
1218 ,x_faculty => cur_ucas_app_choice_rec.faculty
1219 ,x_entry_year => cur_ucas_app_choice_rec.entry_year
1220 ,x_entry_month => cur_ucas_app_choice_rec.entry_month
1221 ,x_point_of_entry => cur_ucas_app_choice_rec.point_of_entry
1222 ,x_home => cur_ucas_app_choice_rec.home
1223 ,x_deferred => cur_ucas_app_choice_rec.deferred
1224 ,x_route_b_pref_round => cur_ucas_app_choice_rec.route_b_pref_round
1225 ,x_route_b_actual_round => cur_ucas_app_choice_rec.route_b_actual_round
1226 ,x_condition_category => cur_ucas_app_choice_rec.condition_category
1227 ,x_condition_code => cur_ucas_app_choice_rec.condition_code
1228 ,x_decision => cur_ucas_app_choice_rec.decision
1229 ,x_decision_date => cur_ucas_app_choice_rec.decision_date
1230 ,x_decision_number => cur_ucas_app_choice_rec.decision_number
1231 ,x_reply => cur_ucas_app_choice_rec.reply
1232 ,x_summary_of_cond => cur_ucas_app_choice_rec.summary_of_cond
1233 ,x_choice_cancelled => cur_ucas_app_choice_rec.choice_cancelled
1234 ,x_action => cur_ucas_app_choice_rec.action
1235 ,x_substitution => cur_ucas_app_choice_rec.substitution
1236 ,x_date_substituted => cur_ucas_app_choice_rec.date_substituted
1237 ,x_prev_institution => cur_ucas_app_choice_rec.prev_institution
1238 ,x_prev_course => cur_ucas_app_choice_rec.prev_course
1239 ,x_prev_campus => cur_ucas_app_choice_rec.prev_campus
1240 ,x_ucas_amendment => cur_ucas_app_choice_rec.ucas_amendment
1241 ,x_withdrawal_reason => cur_ucas_app_choice_rec.withdrawal_reason
1242 ,x_offer_course => cur_ucas_app_choice_rec.offer_course
1243 ,x_offer_campus => cur_ucas_app_choice_rec.offer_campus
1244 ,x_offer_crse_length => cur_ucas_app_choice_rec.offer_crse_length
1245 ,x_offer_entry_month => cur_ucas_app_choice_rec.offer_entry_month
1246 ,x_offer_entry_year => cur_ucas_app_choice_rec.offer_entry_year
1247 ,x_offer_entry_point => cur_ucas_app_choice_rec.offer_entry_point
1248 ,x_offer_text => cur_ucas_app_choice_rec.offer_text
1249 ,x_export_to_oss_status => l_export_to_oss_status
1250 ,x_error_code => l_app_choice_error_code
1251 ,x_request_id => l_conc_request_id
1252 ,x_batch_id => l_ch_batch_id
1253 ,x_mode => 'R'
1254 ,x_extra_round_nbr => cur_ucas_app_choice_rec.extra_round_nbr
1255 ,x_system_code => cur_ucas_app_choice_rec.system_code
1256 ,x_part_time => cur_ucas_app_choice_rec.part_time
1257 ,x_interview => cur_ucas_app_choice_rec.interview
1258 ,x_late_application => cur_ucas_app_choice_rec.late_application
1259 ,x_modular => cur_ucas_app_choice_rec.modular
1260 ,x_residential => cur_ucas_app_choice_rec.residential
1261 ,x_ucas_cycle => cur_ucas_app_choice_rec.ucas_cycle);
1262
1263 END LOOP ; /* end of processing the application choice records */
1264
1265
1266 /* Check for the Previous Application Choices which are errored out earlier,
1267 but corrected by running the Decision Import Process Independently.
1268 If there are no errors in the Admission Decision Importinterface table,
1269 then change the corresponding Application Choice export to OSS status to 'DC' */
1270 FOR cur_dp_app_choice_rec IN cur_dp_app_choice LOOP
1271
1272 -- Fetch the default values setup for the UCAS SYSTEM to which this application choice belongs to,
1273 -- These values will be used in this procedure wherever default values requires
1274 cur_ucas_setup_rec := NULL ;
1275 OPEN cur_ucas_setup(cur_dp_app_choice_rec.system_code) ;
1276 FETCH cur_ucas_setup INTO cur_ucas_setup_rec;
1277 CLOSE cur_ucas_setup;
1278
1279 -- 1. Get the Batch ID corresponding to the UCAS system available in Admission Decision Import Batch table,IGS_AD_BATC_DEF_DET_ALL
1280 l_batch_id := NULL ;
1281 l_batch_id_loc := NULL;
1282 get_batchid_loc(p_system_code => cur_dp_app_choice_rec.system_code,
1283 p_entry_year => cur_dp_app_choice_rec.entry_year,
1284 p_entry_month => cur_dp_app_choice_rec.entry_month,
1285 p_batch_id_loc => l_batch_id_loc);
1286 IF l_batch_id_loc IS NOT NULL THEN
1287 l_batch_id := l_batch_id_det(l_batch_id_loc).batch_id;
1288 ELSE
1289 --If batch ID is not availabe then assign it as zero. So that the application choice
1290 -- will be identified as errored in previous run and necessary checks will be performed
1291 -- to move the application choice status to DC.
1292 l_batch_id := 0;
1293 END IF;
1294
1295 IF cur_dp_app_choice_rec.batch_id <> l_batch_id THEN
1296
1297 OPEN cur_oss_appl_inst( cur_dp_app_choice_rec.app_no,
1298 cur_dp_app_choice_rec.choice_no,
1299 cur_dp_app_choice_rec.ucas_cycle);
1300 FETCH cur_oss_appl_inst INTO cur_oss_appl_inst_rec;
1301
1302 -- jchin - bug 3691277 and 3691250
1303 IF cur_oss_appl_inst%FOUND THEN
1304 /* OSS Application Instance Found */
1305 --check whether the matching unit-set-code exists for the identofi application instance
1306 OPEN cur_unit_set_cd(cur_oss_appl_inst_rec.unit_set_cd,
1307 cur_oss_appl_inst_rec.us_version_number,
1308 cur_oss_appl_inst_rec.nominated_course_cd,
1309 cur_oss_appl_inst_rec.crv_version_number,
1310 cur_oss_appl_inst_rec.acad_cal_type,
1311 cur_oss_appl_inst_rec.location_cd,
1312 cur_oss_appl_inst_rec.attendance_mode,
1313 cur_oss_appl_inst_rec.attendance_type,
1314 cur_oss_appl_inst_rec.point_of_entry);
1315 FETCH cur_unit_set_cd INTO cur_unit_set_cd_rec;
1316
1317 IF cur_unit_set_cd%FOUND THEN
1318 /* OSS Application Instance and unit-set-code found */
1319 --Continue processing
1320
1321 OPEN cur_dec_import_error ( cur_dp_app_choice_rec.batch_id,
1322 cur_oss_appl_inst_rec.person_id,
1323 cur_oss_appl_inst_rec.admission_appl_number,
1324 cur_oss_appl_inst_rec.nominated_course_cd,
1325 cur_oss_appl_inst_rec.sequence_number );
1326 FETCH cur_dec_import_error INTO cur_dec_import_error_rec;
1327
1328 IF cur_dec_import_error%NOTFOUND THEN
1329
1330 igs_uc_app_choices_pkg.update_row (
1331 x_rowid => cur_dp_app_choice_rec.ROWID
1332 ,x_app_choice_id => cur_dp_app_choice_rec.app_choice_id
1333 ,x_app_id => cur_dp_app_choice_rec.app_id
1334 ,x_app_no => cur_dp_app_choice_rec.app_no
1335 ,x_choice_no => cur_dp_app_choice_rec.choice_no
1336 ,x_last_change => cur_dp_app_choice_rec.last_change
1337 ,x_institute_code => cur_dp_app_choice_rec.institute_code
1338 ,x_ucas_program_code => cur_dp_app_choice_rec.ucas_program_code
1339 ,x_oss_program_code => cur_dp_app_choice_rec.oss_program_code
1340 ,x_oss_program_version => cur_dp_app_choice_rec.oss_program_version
1341 ,x_oss_attendance_type => cur_dp_app_choice_rec.oss_attendance_type
1342 ,x_oss_attendance_mode => cur_dp_app_choice_rec.oss_attendance_mode
1343 ,x_campus => cur_dp_app_choice_rec.campus
1344 ,x_oss_location => cur_dp_app_choice_rec.oss_location
1345 ,x_faculty => cur_dp_app_choice_rec.faculty
1346 ,x_entry_year => cur_dp_app_choice_rec.entry_year
1347 ,x_entry_month => cur_dp_app_choice_rec.entry_month
1348 ,x_point_of_entry => cur_dp_app_choice_rec.point_of_entry
1349 ,x_home => cur_dp_app_choice_rec.home
1350 ,x_deferred => cur_dp_app_choice_rec.deferred
1351 ,x_route_b_pref_round => cur_dp_app_choice_rec.route_b_pref_round
1352 ,x_route_b_actual_round => cur_dp_app_choice_rec.route_b_actual_round
1353 ,x_condition_category => cur_dp_app_choice_rec.condition_category
1354 ,x_condition_code => cur_dp_app_choice_rec.condition_code
1355 ,x_decision => cur_dp_app_choice_rec.decision
1356 ,x_decision_date => cur_dp_app_choice_rec.decision_date
1357 ,x_decision_number => cur_dp_app_choice_rec.decision_number
1358 ,x_reply => cur_dp_app_choice_rec.reply
1359 ,x_summary_of_cond => cur_dp_app_choice_rec.summary_of_cond
1360 ,x_choice_cancelled => cur_dp_app_choice_rec.choice_cancelled
1361 ,x_action => cur_dp_app_choice_rec.action
1362 ,x_substitution => cur_dp_app_choice_rec.substitution
1363 ,x_date_substituted => cur_dp_app_choice_rec.date_substituted
1364 ,x_prev_institution => cur_dp_app_choice_rec.prev_institution
1365 ,x_prev_course => cur_dp_app_choice_rec.prev_course
1366 ,x_prev_campus => cur_dp_app_choice_rec.prev_campus
1367 ,x_ucas_amendment => cur_dp_app_choice_rec.ucas_amendment
1368 ,x_withdrawal_reason => cur_dp_app_choice_rec.withdrawal_reason
1369 ,x_offer_course => cur_dp_app_choice_rec.offer_course
1370 ,x_offer_campus => cur_dp_app_choice_rec.offer_campus
1371 ,x_offer_crse_length => cur_dp_app_choice_rec.offer_crse_length
1372 ,x_offer_entry_month => cur_dp_app_choice_rec.offer_entry_month
1373 ,x_offer_entry_year => cur_dp_app_choice_rec.offer_entry_year
1374 ,x_offer_entry_point => cur_dp_app_choice_rec.offer_entry_point
1375 ,x_offer_text => cur_dp_app_choice_rec.offer_text
1376 ,x_export_to_oss_status => 'DC'
1377 ,x_error_code => NULL
1378 ,x_request_id => l_conc_request_id
1379 ,x_batch_id => NULL
1380 ,x_mode => 'R'
1381 ,x_extra_round_nbr => cur_dp_app_choice_rec.extra_round_nbr
1382 ,x_system_code => cur_dp_app_choice_rec.system_code
1383 ,x_part_time => cur_dp_app_choice_rec.part_time
1384 ,x_interview => cur_dp_app_choice_rec.interview
1385 ,x_late_application => cur_dp_app_choice_rec.late_application
1386 ,x_modular => cur_dp_app_choice_rec.modular
1387 ,x_residential => cur_dp_app_choice_rec.residential
1388 ,x_ucas_cycle => cur_dp_app_choice_rec.ucas_cycle);
1389
1390 ELSE
1391
1392 -- Write the error message to the Log file indicating the error occurred
1393 fnd_message.set_name('IGS','IGS_UC_DEC_IMP_ERR');
1394 fnd_message.set_token('APP_NO',TO_CHAR(cur_dp_app_choice_rec.app_no));
1395 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_dp_app_choice_rec.choice_no));
1396 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1397
1398 END IF;
1399 CLOSE cur_dec_import_error;
1400
1401 -- jchin - 3691277 and 3691250
1402 END IF; /* End If for cursor cur_unit_set_cd%FOUND */
1403 CLOSE cur_unit_set_cd;
1404 END IF ; /* End If for cur_oss_appl_inst%FOUND */
1405 CLOSE cur_oss_appl_inst;
1406
1407 END IF ;
1408
1409 END LOOP ; /* End of previous Application Choices which are errored out */
1410
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413
1414 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1415 fnd_message.set_token('NAME','IGS_UC_EXPORT_DECISION_REPLY.EXPORT_DECISION'||' - '||SQLERRM);
1416 IGS_GE_MSG_STACK.ADD;
1417 App_Exception.Raise_Exception;
1418
1419 END export_decision;
1420
1421
1422
1423 PROCEDURE export_reply( p_app_no IGS_UC_APP_CHOICES.APP_NO%TYPE,
1424 p_choice_number IGS_UC_APP_CHOICES.CHOICE_NO%TYPE ) AS
1425 /******************************************************************
1426 Created By : ayedubat
1427 Date Created By : 16-SEP-2002
1428 Purpose : This process exports the UCAS reply to OSS by populating the admissions
1429 offer response import process interface tables
1430 Known limitations,enhancements,remarks:
1431 Change History
1432 Who When What
1433 Nishikant 01-OCT-2002 A new column extra_round_nbr added in the TBH calls of
1434 the package IGS_UC_APP_CHOICES_PKG.
1435 Ayedubat 18-OCT-2002 Passed NULL to the column,ACTUAL_OFFER_RESPONSE_DT while populating into the interface table,IGS_AD_OFFRESP_INT for the bug fix:2632302
1436 jchin 20-jan-2006 Modified for R12 Perf improvements - bug 3691277 and 3691250
1437 jchakrab 22-May-2006 Modified for 5165624
1438 jbaber 07-Jun-2006 Added decline_ofr_reason for bug 528190/5222716
1439 ******************************************************************/
1440
1441 -- Local Variables definition
1442 l_batch_id igs_ad_offresp_batch.batch_id%TYPE ;
1443 l_conc_request_id NUMBER(15) ;
1444 l_errbuf VARCHAR2(2000) ;
1445 l_retcode NUMBER(15) ;
1446 l_export_to_oss igs_uc_app_choices.export_to_oss_status%TYPE ;
1447 l_app_choice_error_code igs_uc_app_choices.error_code%TYPE ;
1448 l_last_update_login NUMBER(15) ;
1449 l_last_updated_by NUMBER(15) ;
1450 l_description VARCHAR2(2000);
1451 l_aca_cal_type igs_uc_sys_calndrs.aca_cal_type%TYPE ;
1452 l_aca_seq_no igs_uc_sys_calndrs.aca_cal_seq_no%TYPE;
1453 l_adm_cal_type igs_uc_sys_calndrs.adm_cal_type%TYPE ;
1454 l_adm_seq_no igs_uc_sys_calndrs.adm_cal_seq_no%TYPE ;
1455 l_ch_error igs_uc_app_choices.error_code%TYPE ;
1456 l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
1457 l_exp_reply_flag BOOLEAN ;
1458
1459 -- Cursor to find the details of default UCAS setup defined in the SYSTEM.
1460 -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
1461 CURSOR cur_ucas_setup( cp_system_code igs_uc_defaults.system_code%TYPE) IS
1462 SELECT *
1463 FROM igs_uc_defaults
1464 WHERE system_code = cp_system_code ;
1465 cur_ucas_setup_rec cur_ucas_setup%ROWTYPE;
1466
1467 -- Cursor to fetch the UCAS Application choices
1468 -- If both application number and choice are not passed it fetches all the application choices
1469 -- If only application number is passed, it fetches the App. choices of the passed app. number
1470 -- If both App. number and Choice are passed, it fetches only one Application
1471 -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
1472 CURSOR cur_ucas_app_choice IS
1473 SELECT uac.*,uac.ROWID
1474 FROM IGS_UC_APP_CHOICES uac,
1475 igs_uc_defaults ud
1476 WHERE uac.app_no = NVL(p_app_no,uac.app_no)
1477 AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
1478 AND uac.export_to_oss_status = 'DC'
1479 AND uac.institute_code = ud.current_inst_code
1480 AND uac.system_code = ud.system_code
1481 ORDER BY uac.ucas_cycle, uac.app_no, uac.choice_no;
1482 cur_ucas_app_choice_rec cur_ucas_app_choice%ROWTYPE;
1483
1484 -- Cursor to find the OSS Application Instance for the current UACS Application Choice
1485 -- smaddali modified this cursor to add the where clause of System_code check ,
1486 -- and modifying the where clause comparing calendars , bug 2643048 UCFD102 build
1487 -- jchin - bug 3691277 and 3691250
1488 CURSOR cur_oss_appl_inst( cp_app_no igs_uc_app_choices.app_no%TYPE,
1489 cp_choice_no igs_uc_app_choices.choice_no%TYPE,
1490 cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
1491 SELECT APLINST.ADM_OUTCOME_STATUS, APLINST.ADM_OFFER_RESP_STATUS,
1492 APLINST.PERSON_ID, APLINST.ADMISSION_APPL_NUMBER,
1493 APLINST.NOMINATED_COURSE_CD, APLINST.SEQUENCE_NUMBER,
1494 APLINST.CRV_VERSION_NUMBER, APLINST.LOCATION_CD,
1495 APLINST.ATTENDANCE_MODE, APLINST.ATTENDANCE_TYPE, APLINST.UNIT_SET_CD,
1496 APLINST.US_VERSION_NUMBER, APL.ACAD_CAL_TYPE, UAC.POINT_OF_ENTRY
1497 FROM IGS_UC_APP_CHOICES UAC,
1498 IGS_UC_APPLICANTS UA,
1499 IGS_UC_DEFAULTS UD,
1500 IGS_AD_SS_APPL_TYP AAT,
1501 IGS_AD_APPL_ALL APL,
1502 IGS_AD_PS_APPL_INST_ALL APLINST,
1503 IGS_UC_SYS_CALNDRS USC
1504 WHERE UAC.APP_NO = CP_APP_NO
1505 AND UAC.CHOICE_NO = CP_CHOICE_NO
1506 AND UAC.UCAS_CYCLE = CP_UCAS_CYCLE
1507 AND UA.APP_NO = UAC.APP_NO
1508 AND UA.OSS_PERSON_ID = APL.PERSON_ID
1509 AND TO_CHAR (UA.APP_NO) = APL.ALT_APPL_ID
1510 AND APL.CHOICE_NUMBER = UAC.CHOICE_NO
1511 AND UAC.SYSTEM_CODE = UD.SYSTEM_CODE
1512 AND UD.APPLICATION_TYPE = AAT.ADMISSION_APPLICATION_TYPE
1513 AND UAC.SYSTEM_CODE = USC.SYSTEM_CODE
1514 AND UAC.ENTRY_YEAR = USC.ENTRY_YEAR
1515 AND (UAC.ENTRY_MONTH = USC.ENTRY_MONTH OR USC.ENTRY_MONTH = 0)
1516 AND APL.ACAD_CAL_TYPE = USC.ACA_CAL_TYPE
1517 AND APL.ACAD_CI_SEQUENCE_NUMBER = USC.ACA_CAL_SEQ_NO
1518 AND APL.ADM_CAL_TYPE = USC.ADM_CAL_TYPE
1519 AND APL.ADM_CI_SEQUENCE_NUMBER = USC.ADM_CAL_SEQ_NO
1520 AND APL.ADMISSION_CAT = AAT.ADMISSION_CAT
1521 AND APL.S_ADMISSION_PROCESS_TYPE = AAT.S_ADMISSION_PROCESS_TYPE
1522 AND APL.PERSON_ID = APLINST.PERSON_ID
1523 AND APL.ADMISSION_APPL_NUMBER = APLINST.ADMISSION_APPL_NUMBER
1524 AND APLINST.NOMINATED_COURSE_CD = UAC.OSS_PROGRAM_CODE
1525 AND APLINST.CRV_VERSION_NUMBER = UAC.OSS_PROGRAM_VERSION
1526 AND APLINST.LOCATION_CD = UAC.OSS_LOCATION
1527 AND APLINST.ATTENDANCE_MODE = UAC.OSS_ATTENDANCE_MODE
1528 AND APLINST.ATTENDANCE_TYPE = UAC.OSS_ATTENDANCE_TYPE;
1529 cur_oss_appl_inst_rec cur_oss_appl_inst%ROWTYPE;
1530
1531 -- jchin - bug 3691277 and 3691250
1532 -- new cursor Check the existence of unit set code corresponding to the application instance
1533 CURSOR cur_unit_set_cd(
1534 p_unit_set_cd igs_ad_ps_appl_inst_all.unit_set_cd%TYPE,
1535 p_us_version_number igs_ad_ps_appl_inst_all.us_version_number%TYPE,
1536 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1537 p_crv_version_number igs_ad_ps_appl_inst_all.crv_version_number%TYPE,
1538 p_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE,
1539 p_location_cd igs_ad_ps_appl_inst_all.location_cd%TYPE,
1540 p_attendance_mode igs_ad_ps_appl_inst_all.attendance_mode%TYPE,
1541 p_attendance_type igs_ad_ps_appl_inst_all.attendance_type%TYPE,
1542 p_point_of_entry igs_uc_app_choices.point_of_entry%TYPE
1543 ) IS
1544 SELECT US.UNIT_SET_CD,
1545 US.VERSION_NUMBER US_VERSION_NUMBER
1546 FROM IGS_PS_OFR_UNIT_SET COUS,
1547 IGS_PS_OFR_OPT COO,
1548 IGS_EN_UNIT_SET US,
1549 IGS_EN_UNIT_SET_CAT USC,
1550 IGS_PS_US_PRENR_CFG CFG
1551 WHERE COUS.UNIT_SET_CD = P_UNIT_SET_CD
1552 AND COUS.US_VERSION_NUMBER = P_US_VERSION_NUMBER
1553 AND COUS.COURSE_CD = P_NOMINATED_COURSE_CD
1554 AND COUS.CRV_VERSION_NUMBER = P_CRV_VERSION_NUMBER
1555 AND COUS.CAL_TYPE = P_ACAD_CAL_TYPE
1556 AND COO.LOCATION_CD = P_LOCATION_CD
1557 AND COO.ATTENDANCE_MODE = P_ATTENDANCE_MODE
1558 AND COO.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
1559 AND COO.COURSE_CD = COUS.COURSE_CD
1560 AND COO.VERSION_NUMBER = COUS.CRV_VERSION_NUMBER
1561 AND COO.CAL_TYPE = COUS.CAL_TYPE
1562 AND US.UNIT_SET_CD = COUS.UNIT_SET_CD
1563 AND US.VERSION_NUMBER = COUS.US_VERSION_NUMBER
1564 AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
1565 AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
1566 AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
1567 AND CFG.SEQUENCE_NO = NVL(P_POINT_OF_ENTRY,1)
1568 AND NOT EXISTS (SELECT COURSE_CD FROM IGS_PS_OF_OPT_UNT_ST COOUS WHERE COOUS.COO_ID = COO.COO_ID)
1569 UNION ALL
1570 SELECT US.UNIT_SET_CD,
1571 US.VERSION_NUMBER US_VERSION_NUMBER
1572 FROM IGS_PS_OF_OPT_UNT_ST COOUS,
1573 IGS_EN_UNIT_SET US,
1574 IGS_EN_UNIT_SET_CAT USC,
1575 IGS_PS_US_PRENR_CFG CFG
1576 WHERE COOUS.UNIT_SET_CD = P_UNIT_SET_CD
1577 AND COOUS.US_VERSION_NUMBER = P_US_VERSION_NUMBER
1578 AND COOUS.COURSE_CD = P_NOMINATED_COURSE_CD
1579 AND COOUS.CRV_VERSION_NUMBER = P_CRV_VERSION_NUMBER
1580 AND COOUS.CAL_TYPE = P_ACAD_CAL_TYPE
1581 AND COOUS.LOCATION_CD = P_LOCATION_CD
1582 AND COOUS.ATTENDANCE_MODE = P_ATTENDANCE_MODE
1583 AND COOUS.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
1584 AND US.UNIT_SET_CD = COOUS.UNIT_SET_CD
1585 AND US.VERSION_NUMBER = COOUS.US_VERSION_NUMBER
1586 AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
1587 AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
1588 AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
1589 AND CFG.SEQUENCE_NO = NVL(P_POINT_OF_ENTRY,1);
1590 cur_unit_set_cd_rec cur_unit_set_cd%ROWTYPE;
1591
1592
1593 -- Cursor to get the OSS user Offer Response mapped to the UCAS system/decision and reply
1594 -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
1595 CURSOR cur_map_offr_resp( p_decision igs_uc_app_choices.decision%TYPE,
1596 p_reply igs_uc_app_choices.reply%TYPE,
1597 p_system_code igs_uc_app_choices.system_code%TYPE) IS
1598 SELECT adm_offer_resp_status
1599 FROM igs_uc_map_off_resp
1600 WHERE system_code = p_system_code
1601 AND decision_code = p_decision
1602 AND reply_code = p_reply
1603 AND closed_ind <> 'Y';
1604 cur_map_offr_resp_rec cur_map_offr_resp%ROWTYPE;
1605
1606 -- Cursor to fetch the System Admission Outcome status of the
1607 -- current OSS Application Instance outcome status
1608 CURSOR cur_s_adm_ou_stat(p_adm_out_status igs_ad_ou_stat.adm_outcome_status%TYPE) IS
1609 SELECT s_adm_outcome_status
1610 FROM igs_ad_ou_stat
1611 WHERE adm_outcome_status = p_adm_out_status;
1612 cur_s_adm_ou_stat_rec cur_s_adm_ou_stat%ROWTYPE;
1613
1614 -- Cursor to Fetch the Application Choices of export to OSS status 'RP'
1615 -- smaddali modified this cursor to add the where clause of System_code check , bug 2643048 UCFD102 build
1616 CURSOR cur_rp_app_choice IS
1617 SELECT uac.*,uac.ROWID
1618 FROM igs_uc_app_choices uac,
1619 igs_uc_defaults ud
1620 WHERE uac.app_no = NVL(p_app_no,uac.app_no)
1621 AND uac.choice_no = NVL( p_choice_number,uac.choice_no )
1622 AND uac.export_to_oss_status = 'RP'
1623 AND uac.institute_code = ud.current_inst_code
1624 AND uac.system_code = ud.system_code
1625 ORDER BY uac.ucas_cycle, uac.app_no, uac.choice_no;
1626 cur_rp_app_choice_rec cur_rp_app_choice%ROWTYPE ;
1627
1628 -- Cursor to check for the error or pending records in the Offer Response Import Process
1629 CURSOR cur_reply_import_error ( p_person_id igs_ad_offresp_int.person_id%TYPE,
1630 p_admission_appl_number igs_ad_offresp_int.admission_appl_number%TYPE,
1631 p_nominated_course_cd igs_ad_offresp_int.nominated_course_cd%TYPE,
1632 p_sequence_number igs_ad_offresp_int.sequence_number%TYPE ) IS
1633 SELECT 'X'
1634 FROM igs_ad_offresp_int
1635 WHERE person_id = p_person_id
1636 AND admission_appl_number = p_admission_appl_number
1637 AND nominated_course_cd = p_nominated_course_cd
1638 AND sequence_number = p_sequence_number
1639 AND status IN ( 2,3 ) ;
1640 cur_reply_import_error_rec cur_reply_import_error%ROWTYPE ;
1641
1642 -- Cursor to fetch the value from the sequence for inserting a record into IGS_AD_OFFRESP_BATCH table
1643 CURSOR cur_offresp_batc_s IS
1644 SELECT igs_ad_offresp_batch_s.NEXTVAL
1645 FROM dual ;
1646
1647 --Cursor to get the Calendar details for the given System, Entry Month and Entry Year.
1648 CURSOR cur_sys_entry_cal_det ( cp_system_code igs_uc_sys_calndrs.system_code%TYPE,
1649 cp_entry_year igs_uc_sys_calndrs.entry_year%TYPE,
1650 cp_entry_month igs_uc_sys_calndrs.entry_month%TYPE ) IS
1651 SELECT aca_cal_type,
1652 aca_cal_seq_no,
1653 adm_cal_type,
1654 adm_cal_seq_no
1655 FROM igs_uc_sys_calndrs
1656 WHERE system_code = cp_system_code
1657 AND entry_year = cp_entry_year
1658 AND entry_month = cp_entry_month;
1659
1660 l_sys_entry_cal_det_rec cur_sys_entry_cal_det%ROWTYPE;
1661
1662 -- Foloowing Local variables are used as OUT parameters for the FND procedure which waits until
1663 -- the Concurrent Request Completes
1664 l_phase VARCHAR2(100) ;
1665 l_conc_status VARCHAR2(100) ;
1666 l_dev_phase VARCHAR2(100) ;
1667 l_dev_status VARCHAR2(100) ;
1668 l_conc_message VARCHAR2(100) ;
1669 l_conc_wait BOOLEAN ;
1670 l_decline_ofr_reason VARCHAR2(100);
1671
1672 BEGIN /* begin of export_reply */
1673
1674 -- Get the Concurrent Request ID of the current export of UCAS application to
1675 -- OSS admission applications run
1676 l_conc_request_id := fnd_global.conc_request_id() ;
1677 l_exp_reply_flag := FALSE ;
1678
1679 -- Populate the Offer Response Import Interface Batch table,IGS_AD_OFFRESP_BATCH
1680 -- Get the Batch ID using the sequence, IGS_AD_OFFRESP_BATCH_S
1681 l_batch_id := NULL ;
1682 OPEN cur_offresp_batc_s ;
1683 FETCH cur_offresp_batc_s INTO l_batch_id ;
1684 CLOSE cur_offresp_batc_s ;
1685
1686 -- Find the WHO columns to insert a record into IGS_AD_OFFRESP_BATCH table
1687 l_last_updated_by := NVL(FND_GLOBAL.USER_ID,-1) ;
1688 l_last_update_login := NVL(FND_GLOBAL.LOGIN_ID,-1) ;
1689
1690 -- Get the Description value from the Message,IGS_UC_OFF_BATCH
1691 fnd_message.set_name('IGS','IGS_UC_OFF_BATCH');
1692 l_description := fnd_message.get;
1693
1694 INSERT INTO igs_ad_offresp_batch
1695 ( batch_id,
1696 batch_desc,
1697 created_by,
1698 creation_date,
1699 last_updated_by,
1700 last_update_date,
1701 last_update_login,
1702 request_id,
1703 program_application_id,
1704 program_update_date,
1705 program_id )
1706 VALUES
1707 ( l_batch_id,
1708 l_description,
1709 l_last_updated_by,
1710 SYSDATE,
1711 l_last_updated_by,
1712 SYSDATE,
1713 l_last_update_login,
1714 fnd_global.conc_request_id,
1715 fnd_global.prog_appl_id,
1716 SYSDATE,
1717 fnd_global.conc_program_id );
1718
1719 /* Process all the Application Choice records of the passed p_app_no, p_choice_number
1720 and of the current institution with Export_to_oss Status = 'DC' */
1721
1722 FOR cur_ucas_app_choice_rec IN cur_ucas_app_choice LOOP
1723
1724 -- Initialize the variable for each Application Choice
1725 l_app_choice_error_code := NULL ;
1726 l_export_to_oss := NULL ;
1727 /* Commented the re-initialization of the batch id variable for bug 2738551
1728 as the value is taken from igs_ad_offresp_batch_s sequence */
1729 -- l_batch_id := NULL ;
1730
1731 -- Fetch the default values setup for the UCAS in the SYSTEM,
1732 -- These values will be used in this procedure whereever default values requires
1733 cur_ucas_setup_rec := NULL ;
1734 OPEN cur_ucas_setup(cur_ucas_app_choice_rec.system_code) ;
1735 FETCH cur_ucas_setup INTO cur_ucas_setup_rec;
1736 CLOSE cur_ucas_setup;
1737
1738 --Get the Calendar details for the given System, Entry Month and Entry Year from System Calendards table.
1739 l_sys_entry_cal_det_rec := NULL;
1740 OPEN cur_sys_entry_cal_det(cur_ucas_app_choice_rec.system_code, cur_ucas_app_choice_rec.entry_year, cur_ucas_app_choice_rec.entry_month);
1741 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
1742 --If no matching Entry Year and Entry Month record for the system is found in the System Calendars table then
1743 -- get the calendar details from the IGS_UC_SYS_CALNDRS table based on the system, Entry Year and Entry Month as 0 (Zero).
1744 IF cur_sys_entry_cal_det%NOTFOUND THEN
1745 CLOSE cur_sys_entry_cal_det;
1746 OPEN cur_sys_entry_cal_det(cur_ucas_app_choice_rec.system_code, cur_ucas_app_choice_rec.entry_year, 0);
1747 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
1748 END IF;
1749 CLOSE cur_sys_entry_cal_det;
1750
1751 l_aca_cal_type := l_sys_entry_cal_det_rec.aca_cal_type ;
1752 l_aca_seq_no := l_sys_entry_cal_det_rec.aca_cal_seq_no;
1753 l_adm_cal_type := l_sys_entry_cal_det_rec.adm_cal_type;
1754 l_adm_seq_no := l_sys_entry_cal_det_rec.adm_cal_seq_no ;
1755
1756 -- Identify whether the Application Instance for the current Application Choice Exist or not
1757 OPEN cur_oss_appl_inst( cur_ucas_app_choice_rec.app_no,
1758 cur_ucas_app_choice_rec.choice_no,
1759 cur_ucas_app_choice_rec.ucas_cycle);
1760 FETCH cur_oss_appl_inst INTO cur_oss_appl_inst_rec;
1761
1762 IF cur_oss_appl_inst%NOTFOUND THEN
1763
1764 -- Raise the error with Error Code as 'R002'
1765 l_app_choice_error_code := 'R002' ;
1766 l_export_to_oss := cur_ucas_app_choice_rec.export_to_oss_status ;
1767 l_ch_batch_id := NULL ;
1768 -- Write the error message to the Log file indicating the error occurred
1769 fnd_message.set_name('IGS','IGS_UC_APP_INST_NOTFOUND');
1770 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
1771 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
1772 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1773
1774 ELSE /* OSS Application Instance Found */
1775
1776 -- jchin - Bug 3691277 and 3691250
1777 --check whether the matching unit-set-code exists for the identofi application instance
1778 OPEN cur_unit_set_cd(cur_oss_appl_inst_rec.unit_set_cd,
1779 cur_oss_appl_inst_rec.us_version_number,
1780 cur_oss_appl_inst_rec.nominated_course_cd,
1781 cur_oss_appl_inst_rec.crv_version_number,
1782 cur_oss_appl_inst_rec.acad_cal_type,
1783 cur_oss_appl_inst_rec.location_cd,
1784 cur_oss_appl_inst_rec.attendance_mode,
1785 cur_oss_appl_inst_rec.attendance_type,
1786 cur_oss_appl_inst_rec.point_of_entry);
1787 FETCH cur_unit_set_cd INTO cur_unit_set_cd_rec;
1788
1789 IF cur_unit_set_cd%NOTFOUND THEN
1790 -- update UCAS Application Chocie record to set error Code to 'D002'
1791 l_app_choice_error_code := 'D002' ;
1792 l_export_to_oss := cur_ucas_app_choice_rec.export_to_oss_status ;
1793 l_ch_batch_id := NULL ;
1794 -- Write the error message to the Log file indicating the error occurred
1795 fnd_message.set_name('IGS','IGS_UC_APP_INST_NOTFOUND');
1796 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
1797 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
1798 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1799 ELSE /* OSS Application Instance and unit-set-code found */
1800
1801
1802 -- Check whether UCAS reply needs to exported to OSS or not
1803 IF cur_ucas_app_choice_rec.reply IS NOT NULL THEN
1804
1805 -- Find the OSS user Offer Response mapped to the UCAS system decision and reply
1806 OPEN cur_map_offr_resp( cur_ucas_app_choice_rec.decision,
1807 cur_ucas_app_choice_rec.reply ,cur_ucas_app_choice_rec.system_code);
1808 FETCH cur_map_offr_resp INTO cur_map_offr_resp_rec;
1809
1810 /* If OSS user Offer Response is not mapped to the UCAS system decision and reply */
1811 IF cur_map_offr_resp%NOTFOUND THEN
1812 CLOSE cur_map_offr_resp;
1813 -- Raise the error with Error Code as 'R003'
1814 l_app_choice_error_code := 'R003' ;
1815 l_export_to_oss := cur_ucas_app_choice_rec.export_to_oss_status ;
1816 l_ch_batch_id := NULL ;
1817 -- Write the error message to the Log file indicating the error occurred
1818 fnd_message.set_name('IGS','IGS_UC_DEC_REPLY_NOT_MAPPED');
1819 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
1820 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
1821 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1822
1823 ELSE /* OSS user Offer Response mapping to the UCAS system decision and reply found */
1824 CLOSE cur_map_offr_resp;
1825 -- Check whether the OSS Application Instance system outcome status is 'OFFER' or 'COND-OFFER'
1826 OPEN cur_s_adm_ou_stat( cur_oss_appl_inst_rec.adm_outcome_status );
1827 FETCH cur_s_adm_ou_stat INTO cur_s_adm_ou_stat_rec;
1828 CLOSE cur_s_adm_ou_stat ;
1829 IF cur_s_adm_ou_stat_rec.s_adm_outcome_status NOT IN ( 'OFFER', 'COND-OFFER' ) THEN
1830 -- Raise the error with Error Code as 'R004'
1831 l_app_choice_error_code := 'R004' ;
1832 l_export_to_oss := cur_ucas_app_choice_rec.export_to_oss_status ;
1833 l_ch_batch_id := NULL ;
1834 -- Write the error message to the Log file indicating the error occurred
1835 fnd_message.set_name('IGS','IGS_UC_APP_NOT_OFFERED');
1836 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
1837 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
1838 fnd_file.put_line( fnd_file.LOG ,fnd_message.get );
1839
1840 ELSE /* OSS Application Instnace system Offer Response status is 'OFFER' or 'COND-OFFER' */
1841
1842 -- If the OSS User Admission Offer Response Status mapped to the
1843 -- Application Choice Reply is not equal to OSS Application Instance Offer Response Status
1844 IF cur_map_offr_resp_rec.adm_offer_resp_status <> cur_oss_appl_inst_rec.adm_offer_resp_status THEN
1845
1846 IF igs_ad_gen_008.admp_get_saors(cur_map_offr_resp_rec.adm_offer_resp_status) = 'REJECTED' THEN
1847 l_decline_ofr_reason := 'NO-REAS-REQ';
1848 ELSE
1849 l_decline_ofr_reason := NULL;
1850 END IF;
1851
1852
1853 /* Populate Admissions Offer Response import interface table,IGS_AD_OFFRESP_INT */
1854 INSERT INTO igs_ad_offresp_int
1855 ( offresp_int_id,
1856 batch_id,
1857 person_id,
1858 admission_appl_number,
1859 nominated_course_cd,
1860 sequence_number,
1861 adm_offer_resp_status,
1862 actual_offer_response_dt,
1863 attent_other_inst_cd,
1864 applicant_acptnce_cndtn,
1865 def_acad_cal_type,
1866 def_acad_ci_sequence_number,
1867 def_adm_cal_type,
1868 def_adm_ci_sequence_number,
1869 status,
1870 created_by,
1871 creation_date,
1872 last_updated_by,
1873 last_update_date,
1874 last_update_login,
1875 request_id,
1876 program_application_id,
1877 program_update_date,
1878 program_id,
1879 decline_ofr_reason)
1880 VALUES
1881 ( igs_ad_offresp_int_s.NEXTVAL,
1882 l_batch_id,
1883 cur_oss_appl_inst_rec.person_id,
1884 cur_oss_appl_inst_rec.admission_appl_number,
1885 cur_oss_appl_inst_rec.nominated_course_cd,
1886 cur_oss_appl_inst_rec.sequence_number,
1887 cur_map_offr_resp_rec.adm_offer_resp_status,
1888 NULL, --ACTUAL_OFFER_RESPONSE_DT
1889 NULL, --ATTENT_OTHER_INST_CD
1890 NULL, --APPLICANT_ACPTNCE_CNDTN
1891 NULL, --DEF_ACAD_CAL_TYPE
1892 NULL, --DEF_ACAD_CI_SEQUENCE_NUMBER
1893 NULL, --DEF_ADM_CAL_TYPE
1894 NULL, --DEF_ADM_CI_SEQUENCE_NUMBER
1895 '2', --Pending Status ( Unprocessed Record)
1896 l_last_updated_by,
1897 SYSDATE,
1898 l_last_updated_by,
1899 SYSDATE,
1900 l_last_update_login,
1901 fnd_global.conc_request_id,
1902 fnd_global.prog_appl_id,
1903 SYSDATE,
1904 fnd_global.conc_program_id,
1905 l_decline_ofr_reason);
1906
1907 -- Update the Application Choice record with the export_to_OSS_Status="RP"
1908 l_export_to_oss := 'RP' ;
1909 l_app_choice_error_code := NULL ;
1910 l_ch_batch_id := NULL ;
1911 l_exp_reply_flag := TRUE ;
1912
1913 ELSE /* If there is no difference between the Application Instance Offer Response Status
1914 and the mapped Application Choice reply, then import of reply is not required */
1915 /* Update the Application Choice Record with export to OSS status 'COMP' */
1916 l_export_to_oss := 'COMP' ;
1917 l_app_choice_error_code := NULL ;
1918 l_ch_batch_id := NULL ;
1919 -- Write the error message to the Log file indicating the error occurred
1920 fnd_message.set_name('IGS','IGS_UC_EXP_APP_COMPLETED');
1921 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
1922 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
1923 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1924 END IF;
1925
1926 END IF; /* End of checking OSS Application Instnace system Offer Response status */
1927
1928 END IF; /* End of OSS user Offer Response mapped to the UCAS system decision and reply */
1929
1930 ELSE /* UCAS reply is NULL, so not required to export to OSS */
1931 /* Update the Application Choice Record with export to OSS status 'COMP' */
1932 l_export_to_oss := 'COMP' ;
1933 l_app_choice_error_code := NULL ;
1934 l_ch_batch_id := NULL ;
1935 -- Write the error message to the Log file indicating the error occurred
1936 fnd_message.set_name('IGS','IGS_UC_EXP_APP_COMPLETED');
1937 fnd_message.set_token('APP_NO',TO_CHAR(cur_ucas_app_choice_rec.app_no));
1938 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_ucas_app_choice_rec.choice_no));
1939 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1940
1941 END IF; /* End of UCAS reply exported to OSS */
1942
1943 --jchin - bug 3691277 and 3691250
1944 END IF; /* End If for cur_unit_set_cd%NOTFOUND */
1945 CLOSE cur_unit_set_cd;
1946
1947 END IF ; /* End of OSS Application Instance processinng */
1948 CLOSE cur_oss_appl_inst;
1949
1950 /* update the Application choice with the error code ,batch_id and export_to_oss_status*/
1951 igs_uc_app_choices_pkg.update_row
1952 ( x_rowid => cur_ucas_app_choice_rec.ROWID
1953 ,x_app_choice_id => cur_ucas_app_choice_rec.app_choice_id
1954 ,x_app_id => cur_ucas_app_choice_rec.app_id
1955 ,x_app_no => cur_ucas_app_choice_rec.app_no
1956 ,x_choice_no => cur_ucas_app_choice_rec.choice_no
1957 ,x_last_change => cur_ucas_app_choice_rec.last_change
1958 ,x_institute_code => cur_ucas_app_choice_rec.institute_code
1959 ,x_ucas_program_code => cur_ucas_app_choice_rec.ucas_program_code
1960 ,x_oss_program_code => cur_ucas_app_choice_rec.oss_program_code
1961 ,x_oss_program_version => cur_ucas_app_choice_rec.oss_program_version
1962 ,x_oss_attendance_type => cur_ucas_app_choice_rec.oss_attendance_type
1963 ,x_oss_attendance_mode => cur_ucas_app_choice_rec.oss_attendance_mode
1964 ,x_campus => cur_ucas_app_choice_rec.campus
1965 ,x_oss_location => cur_ucas_app_choice_rec.oss_location
1966 ,x_faculty => cur_ucas_app_choice_rec.faculty
1967 ,x_entry_year => cur_ucas_app_choice_rec.entry_year
1968 ,x_entry_month => cur_ucas_app_choice_rec.entry_month
1969 ,x_point_of_entry => cur_ucas_app_choice_rec.point_of_entry
1970 ,x_home => cur_ucas_app_choice_rec.home
1971 ,x_deferred => cur_ucas_app_choice_rec.deferred
1972 ,x_route_b_pref_round => cur_ucas_app_choice_rec.route_b_pref_round
1973 ,x_route_b_actual_round => cur_ucas_app_choice_rec.route_b_actual_round
1974 ,x_condition_category => cur_ucas_app_choice_rec.condition_category
1975 ,x_condition_code => cur_ucas_app_choice_rec.condition_code
1976 ,x_decision => cur_ucas_app_choice_rec.decision
1977 ,x_decision_date => cur_ucas_app_choice_rec.decision_date
1978 ,x_decision_number => cur_ucas_app_choice_rec.decision_number
1979 ,x_reply => cur_ucas_app_choice_rec.reply
1980 ,x_summary_of_cond => cur_ucas_app_choice_rec.summary_of_cond
1981 ,x_choice_cancelled => cur_ucas_app_choice_rec.choice_cancelled
1982 ,x_action => cur_ucas_app_choice_rec.action
1983 ,x_substitution => cur_ucas_app_choice_rec.substitution
1984 ,x_date_substituted => cur_ucas_app_choice_rec.date_substituted
1985 ,x_prev_institution => cur_ucas_app_choice_rec.prev_institution
1986 ,x_prev_course => cur_ucas_app_choice_rec.prev_course
1987 ,x_prev_campus => cur_ucas_app_choice_rec.prev_campus
1988 ,x_ucas_amendment => cur_ucas_app_choice_rec.ucas_amendment
1989 ,x_withdrawal_reason => cur_ucas_app_choice_rec.withdrawal_reason
1990 ,x_offer_course => cur_ucas_app_choice_rec.offer_course
1991 ,x_offer_campus => cur_ucas_app_choice_rec.offer_campus
1992 ,x_offer_crse_length => cur_ucas_app_choice_rec.offer_crse_length
1993 ,x_offer_entry_month => cur_ucas_app_choice_rec.offer_entry_month
1994 ,x_offer_entry_year => cur_ucas_app_choice_rec.offer_entry_year
1995 ,x_offer_entry_point => cur_ucas_app_choice_rec.offer_entry_point
1996 ,x_offer_text => cur_ucas_app_choice_rec.offer_text
1997 ,x_export_to_oss_status => l_export_to_oss
1998 ,x_error_code => l_app_choice_error_code
1999 ,x_request_id => l_conc_request_id
2000 ,x_batch_id => l_ch_batch_id
2001 ,x_mode => 'R'
2002 ,x_extra_round_nbr => cur_ucas_app_choice_rec.extra_round_nbr
2003 ,x_system_code => cur_ucas_app_choice_rec.system_code
2004 ,x_part_time => cur_ucas_app_choice_rec.part_time
2005 ,x_interview => cur_ucas_app_choice_rec.interview
2006 ,x_late_application => cur_ucas_app_choice_rec.late_application
2007 ,x_modular => cur_ucas_app_choice_rec.modular
2008 ,x_residential => cur_ucas_app_choice_rec.residential
2009 ,x_ucas_cycle => cur_ucas_app_choice_rec.ucas_cycle
2010 );
2011
2012 END LOOP; /* End of Processing all the Application Choices */
2013
2014 /********** End of Processing all the Application Choices ********/
2015
2016 /* If there are any Application choice records with export_to_oss_status is 'RP'
2017 Then submit the request for 'Admissions Offer Response Import Process' */
2018 IF l_exp_reply_flag THEN
2019
2020 -- Display the information message in the log file
2021 fnd_message.set_name('IGS','IGS_UC_OFFRESP_IMP_PROC_LAUNCH');
2022 fnd_message.set_token('REQ_ID',TO_CHAR(l_batch_id));
2023 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2024
2025 -- Invoke the Admission Import Offer Response Process
2026 BEGIN
2027
2028 igs_ad_imp_off_resp_data.imp_off_resp(
2029 errbuf => l_errbuf,
2030 retcode => l_retcode,
2031 p_batch_id => l_batch_id,
2032 p_yes_no => '1' ) ;
2033 EXCEPTION
2034 WHEN OTHERS THEN
2035 -- If any of the exception are reported in the offer response import process,
2036 -- app choice records will be updated below, so need to handle it here
2037 NULL ;
2038 END ;
2039
2040 END IF;
2041 /****** Admissions Offer Response Import Process Completed ************/
2042
2043 /* Loop through all the Application Choice records according to passesd parameters
2044 criteria with export to OSS status is 'RP' */
2045 FOR cur_rp_app_choice_rec IN cur_rp_app_choice LOOP
2046
2047 -- Get the OSS Admission application instance for this ucas application choice
2048 OPEN cur_oss_appl_inst( cur_rp_app_choice_rec.app_no,
2049 cur_rp_app_choice_rec.choice_no,
2050 cur_rp_app_choice_rec.ucas_cycle );
2051 FETCH cur_oss_appl_inst INTO cur_oss_appl_inst_rec;
2052
2053 --jchin - bug 3691277 and 3691250
2054 IF cur_oss_appl_inst%FOUND THEN
2055 /* OSS Application Instance Found */
2056 --check whether the matching unit-set-code exists for the identofi application instance
2057 OPEN cur_unit_set_cd(cur_oss_appl_inst_rec.unit_set_cd,
2058 cur_oss_appl_inst_rec.us_version_number,
2059 cur_oss_appl_inst_rec.nominated_course_cd,
2060 cur_oss_appl_inst_rec.crv_version_number,
2061 cur_oss_appl_inst_rec.acad_cal_type,
2062 cur_oss_appl_inst_rec.location_cd,
2063 cur_oss_appl_inst_rec.attendance_mode,
2064 cur_oss_appl_inst_rec.attendance_type,
2065 cur_oss_appl_inst_rec.point_of_entry);
2066 FETCH cur_unit_set_cd INTO cur_unit_set_cd_rec;
2067
2068 IF cur_unit_set_cd%FOUND THEN
2069 /* OSS Application Instance and unit-set-code found */
2070 --Continue processing
2071
2072 OPEN cur_reply_import_error ( cur_oss_appl_inst_rec.person_id,
2073 cur_oss_appl_inst_rec.admission_appl_number,
2074 cur_oss_appl_inst_rec.nominated_course_cd,
2075 cur_oss_appl_inst_rec.sequence_number );
2076 FETCH cur_reply_import_error INTO cur_reply_import_error_rec;
2077
2078 -- If there are any errors in Offer Response import,
2079 -- update the Application Choice Record with error code 'ROO1'
2080 IF cur_reply_import_error%FOUND THEN
2081 -- log message in log file
2082 fnd_message.set_name('IGS','IGS_UC_OFFRESP_IMP_ERR');
2083 fnd_message.set_token('APP_NO',TO_CHAR(cur_rp_app_choice_rec.app_no));
2084 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_rp_app_choice_rec.choice_no));
2085 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2086
2087 -- If the error was generated during the current run, then update the record with error code
2088 IF cur_rp_app_choice_rec.batch_id IS NULL THEN
2089 l_export_to_oss := cur_rp_app_choice_rec.export_to_oss_status ;
2090 l_app_choice_error_code := 'ROO1' ;
2091 l_ch_batch_id := l_batch_id ;
2092 ELSE
2093 l_export_to_oss := cur_rp_app_choice_rec.export_to_oss_status ;
2094 l_app_choice_error_code := cur_rp_app_choice_rec.error_code ;
2095 l_ch_batch_id := cur_rp_app_choice_rec.batch_id ;
2096 END IF ;
2097 ELSE
2098 -- Update the Application Choice with export to OSS status as 'COMP', Concurrent Request ID
2099 -- and clear the error code and batch ID
2100 l_export_to_oss := 'COMP' ;
2101 l_app_choice_error_code := NULL ;
2102 l_ch_batch_id := NULL ;
2103 -- Write the error message to the Log file indicating the error occurred
2104 fnd_message.set_name('IGS','IGS_UC_EXP_APP_COMPLETED');
2105 fnd_message.set_token('APP_NO',TO_CHAR(cur_rp_app_choice_rec.app_no));
2106 fnd_message.set_token('CHOICE_NO',TO_CHAR(cur_rp_app_choice_rec.choice_no));
2107 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2108 END IF;
2109 CLOSE cur_reply_import_error;
2110 -- jchin - bug 3691277 and 3691250
2111 END IF; /* End If for cur_unit_set_cd%FOUND */
2112 CLOSE cur_unit_set_cd;
2113
2114 END IF ; /* End If for cur_oss_appl_inst%FOUND */
2115 CLOSE cur_oss_appl_inst;
2116
2117 -- Update the Application Choice with export to OSS status , Concurrent Request ID
2118 igs_uc_app_choices_pkg.update_row
2119 ( x_rowid => cur_rp_app_choice_rec.ROWID
2120 ,x_app_choice_id => cur_rp_app_choice_rec.app_choice_id
2121 ,x_app_id => cur_rp_app_choice_rec.app_id
2122 ,x_app_no => cur_rp_app_choice_rec.app_no
2123 ,x_choice_no => cur_rp_app_choice_rec.choice_no
2124 ,x_last_change => cur_rp_app_choice_rec.last_change
2125 ,x_institute_code => cur_rp_app_choice_rec.institute_code
2126 ,x_ucas_program_code => cur_rp_app_choice_rec.ucas_program_code
2127 ,x_oss_program_code => cur_rp_app_choice_rec.oss_program_code
2128 ,x_oss_program_version => cur_rp_app_choice_rec.oss_program_version
2129 ,x_oss_attendance_type => cur_rp_app_choice_rec.oss_attendance_type
2130 ,x_oss_attendance_mode => cur_rp_app_choice_rec.oss_attendance_mode
2131 ,x_campus => cur_rp_app_choice_rec.campus
2132 ,x_oss_location => cur_rp_app_choice_rec.oss_location
2133 ,x_faculty => cur_rp_app_choice_rec.faculty
2134 ,x_entry_year => cur_rp_app_choice_rec.entry_year
2135 ,x_entry_month => cur_rp_app_choice_rec.entry_month
2136 ,x_point_of_entry => cur_rp_app_choice_rec.point_of_entry
2137 ,x_home => cur_rp_app_choice_rec.home
2138 ,x_deferred => cur_rp_app_choice_rec.deferred
2139 ,x_route_b_pref_round => cur_rp_app_choice_rec.route_b_pref_round
2140 ,x_route_b_actual_round => cur_rp_app_choice_rec.route_b_actual_round
2141 ,x_condition_category => cur_rp_app_choice_rec.condition_category
2142 ,x_condition_code => cur_rp_app_choice_rec.condition_code
2143 ,x_decision => cur_rp_app_choice_rec.decision
2144 ,x_decision_date => cur_rp_app_choice_rec.decision_date
2145 ,x_decision_number => cur_rp_app_choice_rec.decision_number
2146 ,x_reply => cur_rp_app_choice_rec.reply
2147 ,x_summary_of_cond => cur_rp_app_choice_rec.summary_of_cond
2148 ,x_choice_cancelled => cur_rp_app_choice_rec.choice_cancelled
2149 ,x_action => cur_rp_app_choice_rec.action
2150 ,x_substitution => cur_rp_app_choice_rec.substitution
2151 ,x_date_substituted => cur_rp_app_choice_rec.date_substituted
2152 ,x_prev_institution => cur_rp_app_choice_rec.prev_institution
2153 ,x_prev_course => cur_rp_app_choice_rec.prev_course
2154 ,x_prev_campus => cur_rp_app_choice_rec.prev_campus
2155 ,x_ucas_amendment => cur_rp_app_choice_rec.ucas_amendment
2156 ,x_withdrawal_reason => cur_rp_app_choice_rec.withdrawal_reason
2157 ,x_offer_course => cur_rp_app_choice_rec.offer_course
2158 ,x_offer_campus => cur_rp_app_choice_rec.offer_campus
2159 ,x_offer_crse_length => cur_rp_app_choice_rec.offer_crse_length
2160 ,x_offer_entry_month => cur_rp_app_choice_rec.offer_entry_month
2161 ,x_offer_entry_year => cur_rp_app_choice_rec.offer_entry_year
2162 ,x_offer_entry_point => cur_rp_app_choice_rec.offer_entry_point
2163 ,x_offer_text => cur_rp_app_choice_rec.offer_text
2164 ,x_export_to_oss_status => l_export_to_oss
2165 ,x_error_code => l_app_choice_error_code
2166 ,x_request_id => l_conc_request_id
2167 ,x_batch_id => l_ch_batch_id
2168 ,x_mode => 'R'
2169 ,x_extra_round_nbr => cur_rp_app_choice_rec.extra_round_nbr
2170 ,x_system_code => cur_rp_app_choice_rec.system_code
2171 ,x_part_time => cur_rp_app_choice_rec.part_time
2172 ,x_interview => cur_rp_app_choice_rec.interview
2173 ,x_late_application => cur_rp_app_choice_rec.late_application
2174 ,x_modular => cur_rp_app_choice_rec.modular
2175 ,x_residential => cur_rp_app_choice_rec.residential
2176 ,x_ucas_cycle => cur_rp_app_choice_rec.ucas_cycle
2177 );
2178
2179 END LOOP ;
2180 /******* End of processing the Offer Response exported Application Choices ********/
2181
2182 EXCEPTION
2183 WHEN OTHERS THEN
2184 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
2185 fnd_message.set_token('NAME','IGS_UC_EXPORT_DECISION_REPLY.EXPORT_REPLY'||' - '||SQLERRM);
2186 igs_ge_msg_stack.add;
2187 App_Exception.Raise_Exception;
2188
2189 END export_reply ;
2190
2191 END igs_uc_export_decision_reply ;
[Home] [Help]