[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_TRAN_PROCESSOR_PKG
Source
1 PACKAGE BODY igs_uc_tran_processor_pkg AS
2 /* $Header: IGSUC23B.pls 120.9 2006/08/21 03:52:08 jbaber noship $ */
3
4 lv_rowid VARCHAR2(26);
5 l_uc_tran_id igs_uc_transactions.UC_Tran_Id%TYPE;
6 l_tranid NUMBER := 0;
7
8 PROCEDURE trans_build ( P_Tran_type IN igs_uc_transactions.transaction_type%TYPE,
9 P_App_no IN igs_uc_transactions.app_no%TYPE,
10 P_Choice_no IN igs_uc_transactions.choice_no%TYPE,
11 P_Decision IN igs_uc_transactions.decision%TYPE,
12 P_Course IN igs_uc_transactions.program_code%TYPE,
13 P_Campus IN igs_uc_transactions.campus%TYPE,
14 P_Entry_month IN igs_uc_transactions.entry_month%TYPE,
15 P_Entry_year IN igs_uc_transactions.entry_year%TYPE,
16 P_Entry_point IN igs_uc_transactions.entry_point%TYPE,
17 P_SOC IN igs_uc_transactions.SOC%TYPE,
18 P_Free_Format IN igs_uc_transactions.comments_in_offer%TYPE,
19 P_Hold IN igs_uc_transactions.hold_flag%TYPE,
20 P_return1 OUT NOCOPY igs_uc_transactions.return1%TYPE,
21 P_return2 OUT NOCOPY igs_uc_transactions.return2%TYPE,
22 P_Inst_reference IN igs_uc_transactions.inst_reference%TYPE ,
23 P_cond_cat IN igs_uc_transactions.test_cond_cat%TYPE ,
24 P_cond_name IN igs_uc_transactions.test_cond_name%TYPE ,
25 P_auto_generated IN igs_uc_transactions.auto_generated_flag%TYPE,
26 p_system_code IN igs_uc_transactions.system_code%TYPE,
27 p_ucas_cycle IN igs_uc_transactions.ucas_cycle%TYPE,
28 p_modular IN igs_uc_transactions.modular%TYPE DEFAULT NULL,
29 p_part_time IN igs_uc_transactions.part_time%TYPE DEFAULT NULL,
30 p_uc_tran_id OUT NOCOPY igs_uc_transactions.uc_tran_id%TYPE,
31 p_validate_error_cd OUT NOCOPY igs_lookup_values.lookup_code%TYPE,
32 p_mode IN VARCHAR2
33 ) IS
34
35 /*************************************************************
36 Created By : solakshm
37 Date Created By : 23-JAN-2002
38 Purpose : To build transaction records from parameter values and store in
39 IGS_UC_TRANSACTIONS.
40 Know limitations, enhancements or remarks
41 Change History
42 Who When What
43 rbezawad 19-Oct-2002 Modified for Bug 2628587
44 While generating the LD transaction, removed the validation which is
45 checking the P_free_format value to be not null when decision = W.
46 smaddali 02-oct-2002 modified for bug 2603384
47 1) added 3 new parameters p_cond_cat, p_cond_nae ,p_auto_generated
48 2) modifying tbh calls igs_uc_transactions_pkg to add x_auto_generated_flag
49 3) passing calues of p_cond_cat and p_cond_name to x_test_cond_cat and x_test_cond_name
50 instead of NULL in tbh calls of igs_uc_transactions_pkg
51 rbezawad 02-Apr-2002 Modified w.r.t. UCCR002 DLD. Bug No:2278817.
52 1) Passed Entry year, Entry month fields for LC type transactions.
53 2) RX Transactions are validated to always accept decision value 'A'.
54 3) Inst_reference field is added as data model is effected.
55 pmarada 12-nov-2002 Added the system_code parameter.
56 pmarada 09-Jun-2003 Added ucas cycle parameter, and transactions generating for only current cycle.
57 pmarada 01-nov-03 Added and p_valide_error_cd parameters as per UC208 build.
58 jchakrab 08-Jun-2005 Added parameter p_mode for IGS.M Security Solution - 4380412
59 jbaber 18-Jul-03 Added cur_find_appclear_2006 for UC315 - UCAS 2006 Support
60 anwest 29-May-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
61 (reverse chronological order - newest change first)
62 ***************************************************************/
63
64 CURSOR cur_find_deceased IS
65 SELECT b.deceased_ind FROM igs_uc_applicants a,igs_pe_hz_parties b
66 WHERE a.app_no = P_App_no AND a.oss_person_id = b.party_id;
67
68 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
69 CURSOR cur_find_trans(cp_transaction_type igs_uc_transactions.transaction_type%TYPE) IS
70 SELECT COUNT(*)
71 FROM igs_uc_transactions
72 WHERE app_no = P_App_no
73 AND choice_no = P_Choice_no
74 AND ucas_cycle = p_ucas_cycle
75 AND transaction_type = cp_transaction_type;
76
77 CURSOR cur_app_choice_decision IS
78 SELECT decision,reply FROM igs_uc_app_choices
79 WHERE app_no = P_App_no
80 AND choice_no = P_Choice_no
81 AND ucas_cycle = p_ucas_cycle;
82
83 CURSOR cur_find_RD IS
84 SELECT COUNT(*) FROM igs_uc_transactions
85 WHERE app_no = P_App_no
86 AND ucas_cycle = p_ucas_cycle
87 AND transaction_type = 'RD';
88
89 CURSOR cur_find_appclear IS
90 SELECT COUNT(*) FROM igs_uc_u_cvname_2003
91 WHERE appno = P_App_no;
92
93 CURSOR cur_find_appclear_2006 IS
94 SELECT COUNT(*) FROM igs_uc_u_cvname_2003
95 WHERE appno =
96 (SELECT LPAD((app_no||check_digit),9,0) from IGS_UC_APPLICANTS where app_no = P_App_no);
97
98 -- Check whether there exists any applicant record for generating PE
99 CURSOR cur_applicants IS
100 SELECT COUNT(*) FROM igs_uc_applicants WHERE app_no = p_app_no;
101
102 -- Get the count of records exists in the choices tabel between 1 to 6 choices, for PE
103 CURSOR cur_choices IS
104 SELECT COUNT(*) FROM igs_uc_app_choices
105 WHERE app_no = p_app_no AND (choice_no BETWEEN 1 AND 6)
106 AND ucas_cycle = p_ucas_cycle
107 AND institute_code = (SELECT current_inst_code FROM igs_uc_defaults WHERE system_code = p_system_code);
108
109 -- Check whether record exists in app_chocies for the cycle
110 CURSOR cur_rec_found(cp_appno igs_uc_app_choices.app_no%TYPE,
111 cp_choice_no igs_uc_app_choices.choice_no%TYPE,
112 cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,
113 cp_system_code igs_uc_app_choices.system_code%TYPE) IS
114 SELECT 'X' FROM igs_uc_app_choices
115 WHERE app_no = cp_appno
116 AND choice_no = cp_choice_no
117 AND ucas_cycle = cp_ucas_cycle
118 AND system_code = cp_system_code;
119 l_rec_found VARCHAR2(1);
120
121 CURSOR c_transactions(cp_uc_tran_id igs_uc_transactions.uc_tran_id%TYPE) IS
122 SELECT t.ROWID, t.* FROM igs_uc_transactions t
123 WHERE uc_tran_id = cp_uc_tran_id;
124
125 l_transactions c_transactions%ROWTYPE;
126
127 l_decision igs_uc_app_choices.decision%TYPE;
128 l_reply igs_uc_app_choices.reply%TYPE;
129 l_cnt NUMBER;
130 l_cnt1 NUMBER;
131 l_deceased_ind igs_pe_hz_parties.deceased_ind%TYPE;
132 l_return1 igs_uc_transactions.return1%TYPE;
133 l_return2 igs_uc_transactions.return2%TYPE;
134 l_generate VARCHAR2(1);
135 l_generate_tran VARCHAR2(1);
136 l_soc igs_uc_transactions.soc%TYPE;
137 l_comments_in_offer igs_uc_transactions.comments_in_offer%TYPE;
138 l_error_code igs_lookup_values.lookup_code%TYPE;
139 l_hold igs_uc_transactions.hold_flag%TYPE;
140 l_transaction_toy igs_uc_ucas_control.transaction_toy_code%TYPE;
141
142 BEGIN
143
144 OPEN cur_find_deceased;
145 FETCH cur_find_deceased INTO l_deceased_ind;
146 CLOSE cur_find_deceased;
147
148 l_return1:=0;
149 l_generate_tran := 'N';
150 p_uc_tran_id := NULL;
151
152 --Check if the person is deceased or not. If deseased, raise the error message.
153 IF l_deceased_ind = 'Y' THEN
154 P_return1 := 1;
155 P_return2 := 'IGS_UC_APP_DEAD';
156 ELSE
157 -- Check whether record exists in app_choices table or not with appno, chocie, cycle, system
158 OPEN cur_rec_found(P_App_no, P_Choice_no, p_ucas_cycle, p_system_code );
159 FETCH cur_rec_found INTO l_rec_found;
160 IF cur_rec_found%NOTFOUND THEN
161 CLOSE cur_rec_found;
162 p_return1 := 1;
163 p_return2 := 'IGS_UC_INVALID_APP_CH_CYCLE';
164 ELSE
165 CLOSE cur_rec_found;
166
167 -- Get the transaction time of year value, and used to derive hold flag value for some transactions
168 -- call to the get_transaction_toy
169 l_transaction_toy := NULL;
170 igs_uc_gen_001.get_transaction_toy(p_system_code => p_system_code,
171 p_ucas_cycle => p_ucas_cycle ,
172 p_transaction_toy => l_transaction_toy);
173
174 -- If Hold Profile value set then hold flag value is Y
175 IF fnd_profile.value('IGS_UC_HOLD_UCAS_TRANSACTION') = 'Y' THEN
176 l_Hold := 'Y' ;
177 ELSE
178 l_hold := NVL(p_hold,'N');
179 END IF;
180
181 -- call the transaction population process to get the SOC and comments in offer values
182 IF P_cond_cat IS NOT NULL AND P_cond_name IS NOT NULL THEN
183 igs_uc_tran_processor_pkg.transaction_population(
184 p_condition_category1 => P_cond_cat,
185 p_condition_name1 => P_cond_name,
186 p_soc1 => l_soc,
187 p_comments_in_offer => l_comments_in_offer);
188
189 IF l_soc IS NULL THEN
190 l_soc := p_soc;
191 END IF;
192 IF l_comments_in_offer IS NULL THEN
193 l_comments_in_offer := P_Free_Format;
194 END IF;
195 ELSE
196 l_soc := p_soc;
197 l_comments_in_offer := P_Free_Format;
198 END IF;
199
200 IF (P_Tran_type = 'LA') THEN
201 --Check for Mandatory Parameters.
202 IF P_App_no IS NOT NULL AND P_Choice_no IS NOT NULL AND P_Decision IS NOT NULL AND P_Hold IS NOT NULL
203 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
204 -- For generating a LA transaction, there should exists at least one LD transaction
205 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
206 OPEN cur_find_trans('LD');
207 FETCH cur_find_trans INTO l_cnt;
208 CLOSE cur_find_trans;
209
210 IF l_cnt > 0 THEN
211 P_return1 := 0;
212 igs_uc_transactions_pkg.insert_row (
213 x_mode => p_mode, --jchakrab modified for 4380412
214 x_rowid => lv_rowid,
215 x_uc_tran_id => l_uc_tran_id,
216 x_transaction_id => NULL,
217 x_datetimestamp => NULL,
218 x_updater => NULL,
219 x_error_code => NULL,
220 x_transaction_type => P_Tran_type,
221 x_app_no => P_App_no,
222 x_choice_no => P_Choice_no,
223 x_decision => P_Decision,
224 x_program_code => P_Course,
225 x_campus => P_Campus,
226 x_entry_month => P_Entry_month,
227 x_entry_year => P_Entry_year,
228 x_entry_point => P_Entry_point,
229 x_soc => l_soc,
230 x_comments_in_offer => l_comments_in_offer,
231 x_return1 => l_return1,
232 x_return2 => l_return2,
233 x_hold_flag => l_hold,
234 x_sent_to_ucas => 'N',
235 x_test_cond_cat => P_cond_cat,
236 x_test_cond_name => P_cond_name,
237 x_inst_reference => P_Inst_reference ,
238 x_auto_generated_flag => P_auto_generated,
239 x_system_code => p_system_code,
240 x_ucas_cycle => p_ucas_cycle,
241 x_modular => p_modular,
242 x_part_time => p_part_time);
243
244 -- Call the transaction validation process
245 l_error_code := NULL;
246 igs_uc_tran_processor_pkg.transaction_validation(p_transaction_type => P_Tran_type,
247 p_decision => P_Decision,
248 p_comments_in_offer => l_comments_in_offer,
249 p_error_code => l_error_code);
250
251 -- If transaction has an error then set the hold flag Y
252 IF l_error_code IS NOT NULL AND NVL(fnd_profile.value('IGS_UC_HOLD_UCAS_TRANSACTION'),'N') = 'N' THEN
253 -- get the hold profile value and update the transactions with hold flag Y
254 OPEN c_transactions(l_uc_tran_id);
255 FETCH c_transactions INTO l_transactions;
256 CLOSE c_transactions;
257
258 igs_uc_transactions_pkg.update_row(
259 x_rowid => l_transactions.rowid,
260 x_uc_tran_id => l_transactions.uc_tran_id,
261 x_transaction_id => l_transactions.transaction_id,
262 x_datetimestamp => l_transactions.datetimestamp,
263 x_updater => l_transactions.updater,
264 x_error_code => l_transactions.error_code,
265 x_transaction_type => l_transactions.transaction_type,
266 x_app_no => l_transactions.app_no,
267 x_choice_no => l_transactions.choice_no,
268 x_decision => l_transactions.decision,
269 x_program_code => l_transactions.program_code,
270 x_campus => l_transactions.campus,
271 x_entry_month => l_transactions.entry_month,
272 x_entry_year => l_transactions.entry_year,
273 x_entry_point => l_transactions.entry_point,
274 x_soc => l_transactions.soc ,
275 x_comments_in_offer => l_transactions.comments_in_offer,
276 x_return1 => l_transactions.return1,
277 x_return2 => l_transactions.return2,
278 x_hold_flag => 'Y',
279 x_sent_to_ucas => l_transactions.sent_to_ucas,
280 x_test_cond_cat => l_transactions.test_cond_cat,
281 x_test_cond_name => l_transactions.test_cond_name ,
282 x_mode => p_mode, --jchakrab modified for 4380412
283 x_inst_reference => l_transactions.inst_reference,
284 x_auto_generated_flag => l_transactions.auto_generated_flag,
285 x_system_code => l_transactions.system_code,
286 x_ucas_cycle => l_transactions.ucas_cycle,
287 x_modular => l_transactions.modular,
288 x_part_time => l_transactions.part_time);
289
290 END IF; -- end if for error code not null
291 p_uc_tran_id := l_uc_tran_id;
292 p_validate_error_cd := l_error_code;
293 ELSE
294 P_return1 := 1;
295 P_return2:= 'IGS_UC_NO_LA_NO_LD';
296 END IF; -- End if for l_cnt >0
297 ELSE
298 P_return1 := 1;
299 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
300 END IF; -- End if for mandatory parameters null
301
302 ELSIF (P_Tran_type = 'LC') THEN
303 --Check for Mandatory Parameters.
304 IF P_App_no IS NOT NULL AND P_Choice_no IS NOT NULL AND P_Hold IS NOT NULL
305 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
306 IF P_Course IS NOT NULL OR P_Entry_point IS NOT NULL THEN
307 P_return1 := 0;
308 igs_uc_transactions_pkg.insert_row (
309 x_mode => p_mode, --jchakrab modified for 4380412
310 x_rowid => lv_rowid,
311 x_uc_tran_id => l_uc_tran_id,
312 x_transaction_id => NULL,
313 x_datetimestamp => NULL,
314 x_updater => NULL,
315 x_error_code => NULL,
316 x_transaction_type => P_Tran_type,
317 x_app_no => P_App_no,
318 x_choice_no => P_Choice_no,
319 x_decision => p_decision,
320 x_program_code => P_Course,
321 x_campus => P_Campus,
322 x_entry_month => P_Entry_month,
323 x_entry_year => P_Entry_year,
324 x_entry_point => P_Entry_point,
325 x_soc => p_soc,
326 x_comments_in_offer => P_Free_Format,
327 x_return1 => NULL,
328 x_return2 => NULL,
329 x_hold_flag => l_hold,
330 x_sent_to_ucas => 'N',
331 x_test_cond_cat => NULL,
332 x_test_cond_name => NULL,
333 x_inst_reference => P_Inst_reference ,
334 x_auto_generated_flag => P_auto_generated,
335 x_system_code => p_system_code,
336 x_ucas_cycle => p_ucas_cycle,
337 x_modular => p_modular,
338 x_part_time => p_part_time);
339
340 p_uc_tran_id := l_uc_tran_id;
341
342 ELSE
343 P_return1 := 1;
344 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
345 END IF;
346 ELSE
347 P_return1 := 1;
348 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
349 END IF; -- End if for mandatory parameters null
350
351 ELSIF(P_Tran_type = 'LD') THEN
352 --Check for Mandatory Parameters.
353 IF P_App_no IS NOT NULL AND P_Choice_no IS NOT NULL AND P_Decision IS NOT NULL AND P_Hold IS NOT NULL
354 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
355 P_return1 := 0;
356
357 igs_uc_transactions_pkg.insert_row (
358 x_mode => p_mode, --jchakrab modified for 4380412
359 x_rowid => lv_rowid,
360 x_uc_tran_id => l_uc_tran_id,
361 x_transaction_id => NULL,
362 x_datetimestamp => NULL,
363 x_updater => NULL,
364 x_error_code => NULL,
365 x_transaction_type => P_Tran_type,
366 x_app_no => P_App_no,
367 x_choice_no => P_Choice_no,
368 x_decision => P_Decision,
369 x_program_code => P_Course,
370 x_campus => P_Campus,
371 x_entry_month => P_Entry_month,
372 x_entry_year => P_Entry_year,
373 x_entry_point => P_Entry_point,
374 x_soc => l_soc,
375 x_comments_in_offer => l_comments_in_offer,
376 x_return1 => l_return1,
377 x_return2 => l_return2,
378 x_hold_flag => l_hold,
379 x_sent_to_ucas => 'N',
380 x_test_cond_cat => P_cond_cat,
381 x_test_cond_name => P_cond_name,
382 x_inst_reference => P_Inst_reference,
383 x_auto_generated_flag => P_auto_generated,
384 x_system_code => p_system_code,
385 x_ucas_cycle => p_ucas_cycle,
386 x_modular => p_modular,
387 x_part_time => p_part_time);
388
389 -- Call the transaction validation process
390 l_error_code := NULL;
391 igs_uc_tran_processor_pkg.transaction_validation(p_transaction_type => P_Tran_type,
392 p_decision => P_Decision,
393 p_comments_in_offer => l_comments_in_offer,
394 p_error_code => l_error_code);
395
396 -- If transaction has an error then set the hold flag Y
397 IF l_error_code IS NOT NULL AND NVL(fnd_profile.value('IGS_UC_HOLD_UCAS_TRANSACTION'),'N') = 'N' THEN
398 -- get the hold profile value and update the transactions with hold flag Y
399 OPEN c_transactions(l_uc_tran_id);
400 FETCH c_transactions INTO l_transactions;
401 CLOSE c_transactions;
402
403 igs_uc_transactions_pkg.update_row(
404 x_rowid => l_transactions.rowid,
405 x_uc_tran_id => l_transactions.uc_tran_id,
406 x_transaction_id => l_transactions.transaction_id,
407 x_datetimestamp => l_transactions.datetimestamp,
408 x_updater => l_transactions.updater,
409 x_error_code => l_transactions.error_code,
410 x_transaction_type => l_transactions.transaction_type,
411 x_app_no => l_transactions.app_no,
412 x_choice_no => l_transactions.choice_no,
413 x_decision => l_transactions.decision,
414 x_program_code => l_transactions.program_code,
415 x_campus => l_transactions.campus,
416 x_entry_month => l_transactions.entry_month,
417 x_entry_year => l_transactions.entry_year,
418 x_entry_point => l_transactions.entry_point,
419 x_soc => l_transactions.soc ,
420 x_comments_in_offer => l_transactions.comments_in_offer,
421 x_return1 => l_transactions.return1,
422 x_return2 => l_transactions.return2,
423 x_hold_flag => 'Y',
424 x_sent_to_ucas => l_transactions.sent_to_ucas,
425 x_test_cond_cat => l_transactions.test_cond_cat,
426 x_test_cond_name => l_transactions.test_cond_name ,
427 x_mode => p_mode, --jchakrab modified for 4380412
428 x_inst_reference => l_transactions.inst_reference,
429 x_auto_generated_flag => l_transactions.auto_generated_flag,
430 x_system_code => l_transactions.system_code,
431 x_ucas_cycle => l_transactions.ucas_cycle,
432 x_modular => l_transactions.modular,
433 x_part_time => l_transactions.part_time);
434
435 END IF; -- end if for error code not null
436 p_uc_tran_id := l_uc_tran_id;
437 p_validate_error_cd := l_error_code;
438 ELSE
439 P_return1 := 1;
440 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
441 END IF;
442
443 ELSIF(P_Tran_type = 'LK') THEN
444 --Check for Mandatory Parameters.
445 IF P_App_no IS NOT NULL AND P_Choice_no IS NOT NULL AND P_Hold IS NOT NULL
446 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
447 -- For generating a LK transaction, there should not exists any LA transaction
448 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
449 OPEN cur_find_trans('LA');
450 FETCH cur_find_trans INTO l_cnt;
451 CLOSE cur_find_trans;
452
453 IF l_cnt > 0 THEN
454 P_return1 := 1;
455 P_return2:= 'IGS_UC_NO_LK_FOR_LA';
456 ELSE
457 P_return1 := 0;
458 igs_uc_transactions_pkg.insert_row (
459 x_mode => p_mode, --jchakrab modified for 4380412
460 x_rowid => lv_rowid,
461 x_uc_tran_id => l_uc_tran_id,
462 x_transaction_id => NULL,
463 x_datetimestamp => NULL,
464 x_updater => NULL,
465 x_error_code => NULL,
466 x_transaction_type => P_Tran_type,
467 x_app_no => P_App_no,
468 x_choice_no => P_Choice_no,
469 x_decision => NULL,
470 x_program_code => NULL,
471 x_campus => NULL,
472 x_entry_month => NULL,
473 x_entry_year => NULL,
474 x_entry_point => NULL,
475 x_soc => p_soc,
476 x_comments_in_offer => P_Free_Format,
477 x_return1 => NULL,
478 x_return2 => NULL,
479 x_hold_flag => l_hold,
480 x_sent_to_ucas => 'N',
481 x_test_cond_cat => NULL,
482 x_test_cond_name => NULL,
483 x_inst_reference => P_Inst_reference,
484 x_auto_generated_flag => P_auto_generated,
485 x_system_code => p_system_code,
486 x_ucas_cycle => p_ucas_cycle,
487 x_modular => p_modular,
488 x_part_time => p_part_time);
489 p_uc_tran_id := l_uc_tran_id;
490
491 END IF;
492 ELSE
493 P_return1 := 1;
494 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
495 END IF;
496
497 ELSIF(P_Tran_type = 'PE') THEN
498
499 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
500 OPEN cur_find_trans(P_Tran_type);
501 FETCH cur_find_trans INTO l_cnt;
502 CLOSE cur_find_trans;
503
504 IF l_cnt < 1 THEN
505
506 --Check for Mandatory Parameters.
507 IF P_App_no IS NOT NULL AND P_Hold IS NOT NULL AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
508 -- IF the person not exists in applicants then generate PE, if person exists in Applicants and does not exists the
509 -- Choices records between 1 to 6 then generate PE. bug 2632048
510 OPEN cur_applicants;
511 FETCH cur_applicants INTO l_cnt;
512 CLOSE cur_applicants;
513 l_generate := 'N';
514 IF l_cnt > 0 THEN
515 -- If the choices are exist between the 1 to 6 then not generating the transaction.
516 OPEN cur_choices;
517 FETCH cur_choices INTO l_cnt1;
518 CLOSE cur_choices;
519 IF l_cnt1 > 0 THEN
520 l_generate := 'N';
521 ELSE
522 l_generate := 'Y';
523 END IF;
524 ELSE -- Applicantion not exists in the applicants table generate PE
525 l_generate := 'Y';
526 END IF;
527
528 IF l_generate = 'Y' THEN
529 P_return1 := 0;
530 igs_uc_transactions_pkg.insert_row (
531 x_mode => p_mode, --jchakrab modified for 4380412
532 x_rowid => lv_rowid,
533 x_uc_tran_id => l_uc_tran_id,
534 x_transaction_id => NULL,
535 x_datetimestamp => NULL,
536 x_updater => NULL,
537 x_error_code => NULL,
538 x_transaction_type => P_Tran_type,
539 x_app_no => P_App_no,
540 x_choice_no => P_Choice_no,
541 x_decision => p_decision,
542 x_program_code => NULL,
543 x_campus => NULL,
544 x_entry_month => NULL,
545 x_entry_year => NULL,
546 x_entry_point => NULL,
547 x_soc => NULL,
548 x_comments_in_offer => NULL,
549 x_return1 => NULL,
550 x_return2 => NULL,
551 x_hold_flag => l_hold,
552 x_sent_to_ucas => 'N',
553 x_test_cond_cat => NULL ,
554 x_test_cond_name => NULL,
555 x_inst_reference => P_Inst_reference,
556 x_auto_generated_flag => P_auto_generated,
557 x_system_code => p_system_code,
558 x_ucas_cycle => p_ucas_cycle,
559 x_modular => p_modular,
560 x_part_time => p_part_time);
561
562 p_uc_tran_id := l_uc_tran_id;
563 ELSE
564 P_return1 := 1;
565 P_return2 := 'IGS_UC_PE_NOT_GENERATED';
566 END IF;
567 ELSE
568 P_return1 := 1;
569 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
570 END IF;
571
572 END IF; -- l_cnt < 1
573
574 ELSIF (P_Tran_type = 'RA') THEN
575 --Check for Mandatory Parameters.
576 IF P_App_no IS NOT NULL AND P_Hold IS NOT NULL AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
577 OPEN cur_app_choice_decision;
578 FETCH cur_app_choice_decision INTO l_decision,l_reply;
579 CLOSE cur_app_choice_decision;
580
581 IF l_decision = 'U' AND l_reply = 'F' THEN
582 P_return1 := 0;
583 igs_uc_transactions_pkg.insert_row (
584 x_mode => p_mode, --jchakrab modified for 4380412
585 x_rowid => lv_rowid,
586 x_uc_tran_id => l_uc_tran_id,
587 x_transaction_id => NULL,
588 x_datetimestamp => NULL,
589 x_updater => NULL,
590 x_error_code => NULL,
591 x_transaction_type => P_Tran_type,
592 x_app_no => P_App_no,
593 x_choice_no => P_Choice_no,
594 x_decision => NULL,
595 x_program_code => P_Course,
596 x_campus => P_Campus,
597 x_entry_month => P_Entry_month,
598 x_entry_year => P_Entry_year,
599 x_entry_point => P_Entry_point,
600 x_soc => NULL,
601 x_comments_in_offer => NULL,
602 x_return1 => NULL,
603 x_return2 => NULL,
604 x_hold_flag => l_hold,
605 x_sent_to_ucas => 'N',
606 x_test_cond_cat => NULL,
607 x_test_cond_name => NULL,
608 x_inst_reference => P_Inst_reference,
609 x_auto_generated_flag => P_auto_generated,
610 x_system_code => p_system_code,
611 x_ucas_cycle => p_ucas_cycle,
612 x_modular => p_modular,
613 x_part_time => p_part_time);
614
615 p_uc_tran_id := l_uc_tran_id;
616 ELSE
617 P_return1 := 1;
618 P_return2 := 'IGS_UC_APP_NOT_UF';
619 END IF;
620 ELSE
621 P_return1 := 1;
622 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
623 END IF;
624
625 ELSIF (P_Tran_type = 'RD') THEN
626 --Check for Mandatory Parameters.
627 IF P_App_no IS NOT NULL AND P_Choice_no IS NOT NULL AND P_Decision IS NOT NULL AND P_Hold IS NOT NULL
628 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
629 IF (P_Decision IN ('C','I') AND ((P_Course IS NOT NULL AND P_Campus IS NOT NULL) OR
630 (P_Entry_month IS NOT NULL AND P_Entry_Year IS NOT NULL) OR (P_Entry_point IS NOT NULL)))
631 OR (P_Decision NOT IN ('C','I') AND (P_Course IS NULL AND P_Campus IS NULL AND P_Entry_month IS NULL AND P_Entry_year IS NULL AND P_Entry_point IS NULL)) THEN
632 P_return1 := 0;
633 igs_uc_transactions_pkg.insert_row (
634 x_mode => p_mode, --jchakrab modified for 4380412
635 x_rowid => lv_rowid,
636 x_uc_tran_id => l_uc_tran_id,
637 x_transaction_id => NULL,
638 x_datetimestamp => NULL,
639 x_updater => NULL,
640 x_error_code => NULL,
641 x_transaction_type => P_Tran_type,
642 x_app_no => P_App_no,
643 x_choice_no => P_Choice_no,
644 x_decision => P_Decision,
645 x_program_code => P_Course,
646 x_campus => P_Campus,
647 x_entry_month => P_Entry_month,
648 x_entry_year => P_Entry_year,
649 x_entry_point => P_Entry_point,
650 x_soc => NULL,
651 x_comments_in_offer => NULL,
652 x_return1 => NULL,
653 x_return2 => NULL,
654 x_hold_flag => l_hold,
655 x_sent_to_ucas => 'N',
656 x_test_cond_cat => NULL,
657 x_test_cond_name => NULL,
658 x_inst_reference => P_Inst_reference,
659 x_auto_generated_flag => P_auto_generated,
660 x_system_code => p_system_code,
661 x_ucas_cycle => p_ucas_cycle,
662 x_modular => p_modular,
663 x_part_time => p_part_time);
664
665 p_uc_tran_id := l_uc_tran_id;
666 ELSE
667 P_return1 := 1;
668 --Check the decision value and assign the proper error message.
669 IF (P_Decision IN ('C','I') ) THEN
670 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
671 ELSE
672 P_return2 := 'IGS_UC_EXTRA_PARAMS';
673 END IF;
674 END IF;
675 ELSE
676 P_return1 := 1;
677 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
678 END IF;
679
680 ELSIF (P_Tran_type = 'RE') THEN
681
682 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
683 OPEN cur_find_trans(P_Tran_type);
684 FETCH cur_find_trans INTO l_cnt;
685 CLOSE cur_find_trans;
686
687 IF l_cnt < 1 THEN
688
689 --Check for Mandatory Parameters.
690 IF P_App_no IS NOT NULL AND P_Hold IS NOT NULL AND P_Course IS NOT NULL AND P_Entry_year IS NOT NULL
691 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
692
693 --For FTUG system applicant should exist in the cvname then only generate the transaction for other system applicant does not exists in cvname.
694 IF p_system_code = 'U' THEN
695
696 -- Modified for UC315 - UCAS 2006 Support
697 IF p_ucas_cycle < 2006 THEN
698 OPEN cur_find_appclear;
699 FETCH cur_find_appclear INTO l_cnt;
700 CLOSE cur_find_appclear;
701 ELSE
702 OPEN cur_find_appclear_2006;
703 FETCH cur_find_appclear_2006 INTO l_cnt;
704 CLOSE cur_find_appclear_2006;
705 END IF;
706
707 IF l_cnt > 0 THEN
708 l_generate_tran := 'Y';
709 END IF;
710 ELSE
711 l_generate_tran := 'Y';
712 END IF;
713
714 IF l_generate_tran = 'Y' THEN
715 P_return1 := 0;
716 igs_uc_transactions_pkg.insert_row (
717 x_mode => p_mode, --jchakrab modified for 4380412
718 x_rowid => lv_rowid,
719 x_uc_tran_id => l_uc_tran_id,
720 x_transaction_id => NULL,
721 x_datetimestamp => NULL,
722 x_updater => NULL,
723 x_error_code => NULL,
724 x_transaction_type => P_Tran_type,
725 x_app_no => P_App_no,
726 x_choice_no => P_Choice_no,
727 x_decision => NULL,
728 x_program_code => P_Course,
729 x_campus => P_Campus,
730 x_entry_month => NULL,
731 x_entry_year => P_Entry_year,
732 x_entry_point => NULL,
733 x_soc => NULL,
734 x_comments_in_offer => NULL,
735 x_return1 => NULL,
736 x_return2 => NULL,
737 x_hold_flag => l_hold,
738 x_sent_to_ucas => 'N',
739 x_test_cond_cat => NULL,
740 x_test_cond_name => NULL,
741 x_inst_reference => P_Inst_reference,
742 x_auto_generated_flag => P_auto_generated,
743 x_system_code => p_system_code,
744 x_ucas_cycle => p_ucas_cycle,
745 x_modular => p_modular,
746 x_part_time => p_part_time);
747
748 p_uc_tran_id := l_uc_tran_id;
749 ELSE
750 P_return1 := 1;
751 P_return2 := 'IGS_UC_APP_NOT_CLEAR';
752 END IF;
753 ELSE
754 P_return1 := 1;
755 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
756 END IF;
757
758 END IF; -- l_cnt < 1
759
760 ELSIF (P_Tran_type = 'RK') THEN
761 --Check for Mandatory Parameters.
762 IF P_App_no IS NOT NULL AND P_Choice_no IS NOT NULL AND P_HOLD IS NOT NULL AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
763 OPEN cur_find_RD;
764 FETCH cur_find_RD INTO l_cnt;
765 CLOSE cur_find_RD;
766 IF l_cnt > 0 THEN
767 P_return1 := 0;
768 igs_uc_transactions_pkg.insert_row (
769 x_mode => p_mode, --jchakrab modified for 4380412
770 x_rowid => lv_rowid,
771 x_uc_tran_id => l_uc_tran_id,
772 x_transaction_id => NULL,
773 x_datetimestamp => NULL,
774 x_updater => NULL,
775 x_error_code => NULL,
776 x_transaction_type => P_Tran_type,
777 x_app_no => P_App_no,
778 x_choice_no => P_Choice_no,
779 x_decision => NULL,
780 x_program_code => NULL,
781 x_campus => NULL,
782 x_entry_month => NULL,
783 x_entry_year => NULL,
784 x_entry_point => NULL,
785 x_soc => NULL,
786 x_comments_in_offer => NULL,
787 x_return1 => NULL,
788 x_return2 => NULL,
789 x_hold_flag => l_hold,
790 x_sent_to_ucas => 'N',
791 x_test_cond_cat => NULL,
792 x_test_cond_name => NULL,
793 x_inst_reference => P_Inst_reference,
794 x_auto_generated_flag => P_auto_generated,
795 x_system_code => p_system_code,
796 x_ucas_cycle => p_ucas_cycle,
797 x_modular => p_modular,
798 x_part_time => p_part_time);
799
800 p_uc_tran_id := l_uc_tran_id;
801 ELSE
802 P_return1 := 1;
803 P_return2 := 'IGS_UC_NO_RD_NO_RK';
804 END IF;
805 ELSE
806 P_return1 := 1;
807 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
808 END IF;
809
810 ELSIF (P_Tran_type = 'RQ') THEN
811
812 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
813 OPEN cur_find_trans(P_Tran_type);
814 FETCH cur_find_trans INTO l_cnt;
815 CLOSE cur_find_trans;
816
817 IF l_cnt < 1 THEN
818
819 --Check for Mandatory Parameters.
820 IF P_App_no IS NOT NULL AND P_Choice_no IS NOT NULL AND P_HOLD IS NOT NULL AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
821
822 --For FTUG system applicant should exist in the cvname then only generate the transaction for other system applicant does not exists in cvname.
823 IF p_system_code = 'U' THEN
824
825 -- Modified for UC315 - UCAS 2006 Support
826 IF p_ucas_cycle < 2006 THEN
827 OPEN cur_find_appclear;
828 FETCH cur_find_appclear INTO l_cnt;
829 CLOSE cur_find_appclear;
830 ELSE
831 OPEN cur_find_appclear_2006;
832 FETCH cur_find_appclear_2006 INTO l_cnt;
833 CLOSE cur_find_appclear_2006;
834 END IF;
835
836 IF l_cnt > 0 THEN
837 l_generate_tran := 'Y';
838 END IF;
839 ELSE
840 l_generate_tran := 'Y';
841 END IF;
842
843 -- If processing period is NOT claering then hold falg is Y
844 IF l_transaction_toy <> 'C' THEN
845 l_hold := 'Y';
846 ELSE
847 l_hold := NVL(fnd_profile.value('IGS_UC_HOLD_UCAS_TRANSACTION'),'N');
848 END IF;
849
850 IF l_generate_tran = 'Y' THEN
851 P_return1 := 0;
852 igs_uc_transactions_pkg.insert_row (
853 x_mode => p_mode, --jchakrab modified for 4380412
854 x_rowid => lv_rowid,
855 x_uc_tran_id => l_uc_tran_id,
856 x_transaction_id => NULL,
857 x_datetimestamp => NULL,
858 x_updater => NULL,
859 x_error_code => NULL,
860 x_transaction_type => P_Tran_type,
861 x_app_no => P_App_no,
862 x_choice_no => P_Choice_no,
863 x_decision => NULL,
864 x_program_code => P_Course,
865 x_campus => P_Campus,
866 x_entry_month => NULL,
867 x_entry_year => P_Entry_year,
868 x_entry_point => NULL,
869 x_soc => NULL,
870 x_comments_in_offer => NULL,
871 x_return1 => NULL,
872 x_return2 => NULL,
873 x_hold_flag => l_hold,
874 x_sent_to_ucas => 'N',
875 x_test_cond_cat => NULL,
876 x_test_cond_name => NULL,
877 x_inst_reference => P_Inst_reference,
878 x_auto_generated_flag => P_auto_generated,
879 x_system_code => p_system_code,
880 x_ucas_cycle => p_ucas_cycle,
881 x_modular => p_modular,
882 x_part_time => p_part_time);
883
884 p_uc_tran_id := l_uc_tran_id;
885
886 ELSE
887 P_return1 := 1;
888 P_return2 := 'IGS_UC_APP_NOT_CLEAR';
889 END IF;
890 ELSE
891 P_return1 := 1;
892 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
893 END IF;
894
895 END IF; -- l_cnt < 1
896
897 ELSIF (P_Tran_type = 'RR') THEN
898 --Check for Mandatory Parameters.
899 IF P_App_no IS NOT NULL AND P_Free_Format IS NOT NULL AND P_HOLD IS NOT NULL
900 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
901 IF P_Free_Format ='.R1' OR P_Free_Format = '.R2' OR P_Free_Format = '.R3'
902 OR P_Free_Format = '.R4' OR P_Free_Format = '.R5' OR P_Free_Format = '.R6' THEN
903 P_return1 := 0;
904 igs_uc_transactions_pkg.insert_row (
905 x_mode => p_mode, --jchakrab modified for 4380412
906 x_rowid => lv_rowid,
907 x_uc_tran_id => l_uc_tran_id,
908 x_transaction_id => NULL,
909 x_datetimestamp => NULL,
910 x_updater => NULL,
911 x_error_code => NULL,
912 x_transaction_type => P_Tran_type,
913 x_app_no => P_App_no,
914 x_choice_no => P_Choice_no,
915 x_decision => NULL,
916 x_program_code => NULL,
917 x_campus => NULL,
918 x_entry_month => NULL,
919 x_entry_year => NULL,
920 x_entry_point => NULL,
921 x_soc => NULL,
922 x_comments_in_offer => P_Free_Format,
923 x_return1 => NULL,
924 x_return2 => NULL,
925 x_hold_flag => l_hold,
926 x_sent_to_ucas => 'N',
927 x_test_cond_cat => NULL,
928 x_test_cond_name => NULL,
929 x_inst_reference => P_Inst_reference,
930 x_auto_generated_flag => P_auto_generated,
931 x_system_code => p_system_code,
932 x_ucas_cycle => p_ucas_cycle,
933 x_modular => p_modular,
934 x_part_time => p_part_time);
935
936 p_uc_tran_id := l_uc_tran_id;
937 ELSE
938 P_return1 := 1;
939 P_return2 := 'IGS_UC_RR_FREEFORMAT';
940 END IF;
941 ELSE
942 P_return1 := 1;
943 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
944 END IF;
945
946 ELSIF (P_Tran_type = 'RW') THEN
947 --Check for Mandatory Parameters.
948 IF P_App_no IS NOT NULL AND P_HOLD IS NOT NULL AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
949 OPEN cur_app_choice_decision;
950 FETCH cur_app_choice_decision INTO l_decision,l_reply;
951 CLOSE cur_app_choice_decision;
952
953 IF l_decision = 'U' AND l_reply = 'F' THEN
954 P_return1 := 0;
955 igs_uc_transactions_pkg.insert_row (
956 x_mode => p_mode, --jchakrab modified for 4380412
957 x_rowid => lv_rowid,
958 x_uc_tran_id => l_uc_tran_id,
959 x_transaction_id => NULL,
960 x_datetimestamp => NULL,
961 x_updater => NULL,
962 x_error_code => NULL,
963 x_transaction_type => P_Tran_type,
964 x_app_no => P_App_no,
965 x_choice_no => P_Choice_no,
966 x_decision => NULL,
967 x_program_code => NULL,
968 x_campus => NULL,
969 x_entry_month => NULL,
970 x_entry_year => NULL,
971 x_entry_point => NULL,
972 x_soc => NULL,
973 x_comments_in_offer => NULL,
974 x_return1 => NULL,
975 x_return2 => NULL,
976 x_hold_flag => l_hold,
977 x_sent_to_ucas => 'N',
978 x_test_cond_cat => NULL,
979 x_test_cond_name => NULL,
980 x_inst_reference => P_Inst_reference ,
981 x_auto_generated_flag => P_auto_generated,
982 x_system_code => p_system_code,
983 x_ucas_cycle => p_ucas_cycle,
984 x_modular => p_modular,
985 x_part_time => p_part_time);
986
987 p_uc_tran_id := l_uc_tran_id;
988 ELSE
989 P_return1 := 1;
990 P_return2 := 'IGS_UC_APP_NOT_UF';
991 END IF;
992 ELSE
993 P_return1 := 1;
994 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
995 END IF;
996
997 ELSIF (P_Tran_type = 'RX') THEN
998 --Check for Mandatory Parameters.
999 IF P_App_no IS NOT NULL AND P_Decision IS NOT NULL AND P_Hold IS NOT NULL AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
1000 IF P_Course IS NOT NULL
1001 AND ((p_system_code IN ('U','N') AND P_Decision = 'A') OR (p_system_code = 'G' AND P_Decision IN ('C','U'))) THEN
1002 P_return1 := 0;
1003
1004 --If processing period is not claering then hold flag is Y
1005 IF l_transaction_toy <> 'C' THEN
1006 l_hold := 'Y';
1007 ELSE
1008 l_hold := NVL(fnd_profile.value('IGS_UC_HOLD_UCAS_TRANSACTION'),'N');
1009 END IF;
1010
1011 igs_uc_transactions_pkg.insert_row (
1012 x_mode => p_mode, --jchakrab modified for 4380412
1013 x_rowid => lv_rowid,
1014 x_uc_tran_id => l_uc_tran_id,
1015 x_transaction_id => NULL,
1016 x_datetimestamp => NULL,
1017 x_updater => NULL,
1018 x_error_code => NULL,
1019 x_transaction_type => P_Tran_type,
1020 x_app_no => P_App_no,
1021 x_choice_no => P_Choice_no,
1022 x_decision => P_Decision,
1023 x_program_code => NVL(P_Course,NULL),
1024 x_campus => P_Campus,
1025 x_entry_month => P_Entry_month,
1026 x_entry_year => P_Entry_year,
1027 x_entry_point => P_Entry_point,
1028 x_soc => NULL,
1029 x_comments_in_offer => NULL,
1030 x_return1 => NULL,
1031 x_return2 => NULL,
1032 x_hold_flag => l_hold,
1033 x_sent_to_ucas => 'N',
1034 x_test_cond_cat => NULL,
1035 x_test_cond_name => NULL,
1036 x_inst_reference => P_Inst_reference,
1037 x_auto_generated_flag => P_auto_generated,
1038 x_system_code => p_system_code,
1039 x_ucas_cycle => p_ucas_cycle,
1040 x_modular => p_modular,
1041 x_part_time => p_part_time);
1042
1043 p_uc_tran_id := l_uc_tran_id;
1044 ELSE
1045 P_return1 := 1;
1046 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
1047 END IF;
1048 ELSE
1049 P_return1 := 1;
1050 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
1051 END IF;
1052
1053 ELSIF (P_Tran_type = 'XA') THEN
1054 --Check for Mandatory Parameters.
1055 IF P_App_no IS NOT NULL AND P_Choice_no IS NOT NULL AND P_Decision IS NOT NULL AND P_Hold IS NOT NULL
1056 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
1057
1058 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
1059 OPEN cur_find_trans('LD');
1060 FETCH cur_find_trans INTO l_cnt;
1061 CLOSE cur_find_trans;
1062
1063 IF l_cnt > 0 THEN
1064 P_return1 := 0;
1065 igs_uc_transactions_pkg.insert_row (
1066 x_mode => p_mode, --jchakrab modified for 4380412
1067 x_rowid => lv_rowid,
1068 x_uc_tran_id => l_uc_tran_id,
1069 x_transaction_id => NULL,
1070 x_datetimestamp => NULL,
1071 x_updater => NULL,
1072 x_error_code => NULL,
1073 x_transaction_type => P_Tran_type,
1074 x_app_no => P_App_no,
1075 x_choice_no => P_Choice_no,
1076 x_decision => P_Decision,
1077 x_program_code => P_Course,
1078 x_campus => P_Campus,
1079 x_entry_month => P_Entry_month,
1080 x_entry_year => P_Entry_year,
1081 x_entry_point => P_Entry_point,
1082 x_soc => l_soc,
1083 x_comments_in_offer => l_comments_in_offer,
1084 x_return1 => l_return1,
1085 x_return2 => l_return2,
1086 x_hold_flag => l_hold,
1087 x_sent_to_ucas => 'N',
1088 x_test_cond_cat => P_cond_cat,
1089 x_test_cond_name => P_cond_name,
1090 x_inst_reference => P_Inst_reference,
1091 x_auto_generated_flag => P_auto_generated,
1092 x_system_code => p_system_code,
1093 x_ucas_cycle => p_ucas_cycle,
1094 x_modular => p_modular,
1095 x_part_time => p_part_time);
1096
1097 -- Call the transaction validation process
1098 l_error_code := NULL;
1099 igs_uc_tran_processor_pkg.transaction_validation(p_transaction_type => P_Tran_type,
1100 p_decision => P_Decision,
1101 p_comments_in_offer => l_comments_in_offer,
1102 p_error_code => l_error_code);
1103
1104 -- If transaction has an error then set the hold flag Y
1105 IF l_error_code IS NOT NULL AND NVL(fnd_profile.value('IGS_UC_HOLD_UCAS_TRANSACTION'),'N') = 'N' THEN
1106 -- get the hold profile value and update the transactions with hold flag Y
1107 OPEN c_transactions(l_uc_tran_id);
1108 FETCH c_transactions INTO l_transactions;
1109 CLOSE c_transactions;
1110
1111 igs_uc_transactions_pkg.update_row(
1112 x_rowid => l_transactions.rowid,
1113 x_uc_tran_id => l_transactions.uc_tran_id,
1114 x_transaction_id => l_transactions.transaction_id,
1115 x_datetimestamp => l_transactions.datetimestamp,
1116 x_updater => l_transactions.updater,
1117 x_error_code => l_transactions.error_code,
1118 x_transaction_type => l_transactions.transaction_type,
1119 x_app_no => l_transactions.app_no,
1120 x_choice_no => l_transactions.choice_no,
1121 x_decision => l_transactions.decision,
1122 x_program_code => l_transactions.program_code,
1123 x_campus => l_transactions.campus,
1124 x_entry_month => l_transactions.entry_month,
1125 x_entry_year => l_transactions.entry_year,
1126 x_entry_point => l_transactions.entry_point,
1127 x_soc => l_transactions.soc ,
1128 x_comments_in_offer => l_transactions.comments_in_offer,
1129 x_return1 => l_transactions.return1,
1130 x_return2 => l_transactions.return2,
1131 x_hold_flag => 'Y',
1132 x_sent_to_ucas => l_transactions.sent_to_ucas,
1133 x_test_cond_cat => l_transactions.test_cond_cat,
1134 x_test_cond_name => l_transactions.test_cond_name ,
1135 x_mode => p_mode, --jchakrab modified for 4380412
1136 x_inst_reference => l_transactions.inst_reference,
1137 x_auto_generated_flag => l_transactions.auto_generated_flag,
1138 x_system_code => l_transactions.system_code,
1139 x_ucas_cycle => l_transactions.ucas_cycle,
1140 x_modular => l_transactions.modular,
1141 x_part_time => l_transactions.part_time);
1142
1143 END IF; -- end if for error code not null
1144 p_uc_tran_id := l_uc_tran_id;
1145 p_validate_error_cd := l_error_code;
1146 ELSE
1147 P_return1 := 1;
1148 P_return2:= 'IGS_UC_NO_XA_NO_LD';
1149 END IF;
1150 ELSE
1151 P_return1 := 1;
1152 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
1153 END IF;
1154
1155 ELSIF (P_Tran_type = 'XD') THEN
1156 --Check for Mandatory Parameters.
1157 IF P_App_no IS NOT NULL AND P_Choice_no IS NOT NULL AND P_Decision IS NOT NULL AND P_Hold IS NOT NULL
1158 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
1159 IF P_Decision IN ('W','X') THEN
1160 IF P_Free_Format IS NOT NULL THEN
1161 P_return1 := 0;
1162
1163 igs_uc_transactions_pkg.insert_row (
1164 x_mode => p_mode, --jchakrab modified for 4380412
1165 x_rowid => lv_rowid,
1166 x_uc_tran_id => l_uc_tran_id,
1167 x_transaction_id => NULL,
1168 x_datetimestamp => NULL,
1169 x_updater => NULL,
1170 x_error_code => NULL,
1171 x_transaction_type => P_Tran_type,
1172 x_app_no => P_App_no,
1173 x_choice_no => P_Choice_no,
1174 x_decision => P_Decision,
1175 x_program_code => P_Course,
1176 x_campus => P_Campus,
1177 x_entry_month => P_Entry_month,
1178 x_entry_year => P_Entry_year,
1179 x_entry_point => P_Entry_point,
1180 x_soc => l_soc,
1181 x_comments_in_offer => l_comments_in_offer,
1182 x_return1 => l_return1,
1183 x_return2 => l_return2,
1184 x_hold_flag => l_hold,
1185 x_sent_to_ucas => 'N',
1186 x_test_cond_cat => P_cond_cat,
1187 x_test_cond_name => P_cond_name,
1188 x_inst_reference => P_Inst_reference ,
1189 x_auto_generated_flag => P_auto_generated,
1190 x_system_code => p_system_code,
1191 x_ucas_cycle => p_ucas_cycle,
1192 x_modular => p_modular,
1193 x_part_time => p_part_time);
1194
1195 -- Call the transaction validation process
1196 l_error_code := NULL;
1197 igs_uc_tran_processor_pkg.transaction_validation(p_transaction_type => P_Tran_type,
1198 p_decision => P_Decision,
1199 p_comments_in_offer => l_comments_in_offer,
1200 p_error_code => l_error_code);
1201
1202 -- If transaction has an error then set the hold flag Y
1203 IF l_error_code IS NOT NULL AND NVL(fnd_profile.value('IGS_UC_HOLD_UCAS_TRANSACTION'),'N') = 'N' THEN
1204 -- get the hold profile value and update the transactions with hold flag Y
1205 OPEN c_transactions(l_uc_tran_id);
1206 FETCH c_transactions INTO l_transactions;
1207 CLOSE c_transactions;
1208
1209 igs_uc_transactions_pkg.update_row(
1210 x_rowid => l_transactions.rowid,
1211 x_uc_tran_id => l_transactions.uc_tran_id,
1212 x_transaction_id => l_transactions.transaction_id,
1213 x_datetimestamp => l_transactions.datetimestamp,
1214 x_updater => l_transactions.updater,
1215 x_error_code => l_transactions.error_code,
1216 x_transaction_type => l_transactions.transaction_type,
1217 x_app_no => l_transactions.app_no,
1218 x_choice_no => l_transactions.choice_no,
1219 x_decision => l_transactions.decision,
1220 x_program_code => l_transactions.program_code,
1221 x_campus => l_transactions.campus,
1222 x_entry_month => l_transactions.entry_month,
1223 x_entry_year => l_transactions.entry_year,
1224 x_entry_point => l_transactions.entry_point,
1225 x_soc => l_transactions.soc ,
1226 x_comments_in_offer => l_transactions.comments_in_offer,
1227 x_return1 => l_transactions.return1,
1228 x_return2 => l_transactions.return2,
1229 x_hold_flag => 'Y',
1230 x_sent_to_ucas => l_transactions.sent_to_ucas,
1231 x_test_cond_cat => l_transactions.test_cond_cat,
1232 x_test_cond_name => l_transactions.test_cond_name ,
1233 x_mode => p_mode, --jchakrab modified for 4380412
1234 x_inst_reference => l_transactions.inst_reference,
1235 x_auto_generated_flag => l_transactions.auto_generated_flag,
1236 x_system_code => l_transactions.system_code,
1237 x_ucas_cycle => l_transactions.ucas_cycle,
1238 x_modular => l_transactions.modular,
1239 x_part_time => l_transactions.part_time);
1240
1241 END IF; -- end if for error code not null
1242 p_uc_tran_id := l_uc_tran_id;
1243 p_validate_error_cd := l_error_code;
1244 ELSE
1245 P_return1 := 1;
1246 P_return2 := 'IGS_UC_NO_WITHD_REASON';
1247 END IF;
1248 END IF;
1249 ELSE
1250 P_return1 := 1;
1251 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
1252 END IF;
1253
1254 ELSIF (P_Tran_type = 'LE') THEN
1255
1256 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
1257 OPEN cur_find_trans(P_Tran_type);
1258 FETCH cur_find_trans INTO l_cnt;
1259 CLOSE cur_find_trans;
1260
1261 IF l_cnt < 1 THEN
1262
1263 --Check for Mandatory Parameters.
1264 IF P_App_no IS NOT NULL AND P_Hold IS NOT NULL AND P_Course IS NOT NULL AND P_Free_Format IS NOT NULL
1265 AND p_system_code IS NOT NULL AND p_ucas_cycle IS NOT NULL THEN
1266
1267 IF LENGTH(P_Free_Format) = 7 AND SUBSTR(P_Free_Format,1,1) = 'X' AND SUBSTR(P_Free_Format,7,1) IN ('Y','N') THEN
1268 P_return1 := 0;
1269 -- If processing period is not extra then hold flag is Y
1270 IF l_transaction_toy <> 'E' THEN
1271 l_hold := 'Y';
1272 ELSE
1273 l_hold := NVL(fnd_profile.value('IGS_UC_HOLD_UCAS_TRANSACTION'),'N');
1274 END IF;
1275
1276 igs_uc_transactions_pkg.insert_row (
1277 x_mode => p_mode, --jchakrab modified for 4380412
1278 x_rowid => lv_rowid,
1279 x_uc_tran_id => l_uc_tran_id,
1280 x_transaction_id => NULL,
1281 x_datetimestamp => NULL,
1282 x_updater => NULL,
1283 x_error_code => NULL,
1284 x_transaction_type => P_Tran_type,
1285 x_app_no => P_App_no,
1286 x_choice_no => P_Choice_no,
1287 x_decision => NULL,
1288 x_program_code => P_Course,
1289 x_campus => P_Campus,
1290 x_entry_month => NULL,
1291 x_entry_year => NULL,
1292 x_entry_point => NULL,
1293 x_soc => NULL,
1294 x_comments_in_offer => P_Free_Format,
1295 x_return1 => l_return1,
1296 x_return2 => l_return2,
1297 x_hold_flag => l_hold,
1298 x_sent_to_ucas => 'N',
1299 x_test_cond_cat => P_cond_cat,
1300 x_test_cond_name => P_cond_name,
1301 x_inst_reference => P_Inst_reference,
1302 x_auto_generated_flag => P_auto_generated,
1303 x_system_code => p_system_code,
1304 x_ucas_cycle => p_ucas_cycle,
1305 x_modular => p_modular,
1306 x_part_time => p_part_time);
1307
1308 p_uc_tran_id := l_uc_tran_id;
1309 ELSE
1310 P_return1 := 1;
1311 P_return2 := 'IGS_UC_INVALID_EXTRA_ID';
1312 END IF;
1313 ELSE
1314 P_return1 := 1;
1315 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
1316 END IF; -- End if for mandatory parameters
1317
1318 END IF; -- l_cnt < 1
1319
1320 ELSE
1321 P_return1 := 1;
1322 P_return2 := 'IGS_UC_NO_MANDATORY_PARAMS';
1323 END IF; --Tran TYPE check
1324
1325 END IF; -- End if for cur_rec_found
1326
1327 END IF; --deceased check
1328
1329 EXCEPTION
1330 WHEN OTHERS THEN
1331 RAISE;
1332
1333 END trans_build;
1334
1335 PROCEDURE transaction_population(p_condition_category1 IN igs_uc_transactions.test_cond_cat%TYPE ,
1336 p_condition_name1 IN igs_uc_transactions.test_cond_name%TYPE ,
1337 p_soc1 OUT NOCOPY igs_uc_transactions.SOC%TYPE,
1338 p_comments_in_offer OUT NOCOPY igs_uc_offer_conds.marvin_code%TYPE ) IS
1339
1340 /*********************************************************************
1341 Created By : pmarada
1342 Date Created By : 01-Nov-2003
1343 Purpose: Returns the derived field value based on the condition categroy and name
1344 this procedure could be enhanced in future
1345
1346 Know limitations, enhancements or remarks
1347 Change History (reverse chronological order - newest change first)
1348 Who When What
1349
1350 **********************************************************************/
1351
1352 CURSOR c_offer_conds (cp_condition_category igs_uc_offer_conds.condition_category%TYPE,
1353 cp_condition_name igs_uc_offer_conds.condition_name%TYPE) IS
1354 SELECT marvin_code, summ_of_cond FROM igs_uc_offer_conds
1355 WHERE condition_category = cp_condition_category
1356 AND condition_name = cp_condition_name;
1357
1358 l_offer_conds c_offer_conds%ROWTYPE;
1359
1360 BEGIN
1361 -- get the Summary of Conds and Marvin code valeus and return to the main procedure
1362 OPEN c_offer_conds(p_condition_category1, p_condition_name1);
1363 FETCH c_offer_conds INTO l_offer_conds;
1364 CLOSE c_offer_conds;
1365
1366 p_soc1 := l_offer_conds.summ_of_cond;
1367 p_comments_in_offer := l_offer_conds.marvin_code;
1368
1369 END transaction_population;
1370
1371 PROCEDURE transaction_validation(p_transaction_type IN igs_uc_transactions.transaction_type%TYPE,
1372 p_decision IN igs_uc_transactions.decision%TYPE,
1373 p_comments_in_offer IN igs_uc_transactions.comments_in_offer%TYPE,
1374 p_error_code OUT NOCOPY igs_lookup_values.lookup_code%TYPE) IS
1375
1376 /*********************************************************************
1377 Created By : pmarada
1378 Date Created By : 01-Nov-2003
1379 Purpose: procedure validates the comments in offer filed value for
1380 decision and transaction if any error returns the error code
1381
1382 Know limitations, enhancements or remarks
1383 Change History (reverse chronological order - newest change first)
1384 Who When What
1385
1386 **********************************************************************/
1387
1388 l_error DATE;
1389
1390 BEGIN
1391
1392 p_error_code := NULL;
1393 -- For Interview decision comments in offer field should have Interview date with DDMMYY format.
1394 IF p_decision = 'I' THEN
1395 IF p_transaction_type IN ('LA','LD') AND p_comments_in_offer IS NULL THEN
1396 p_error_code := '1001';
1397 ELSIF p_transaction_type IN ('LA','LD') AND p_comments_in_offer IS NOT NULL THEN
1398 BEGIN
1399 IF length(p_comments_in_offer) <> 6 THEN
1400 p_error_code := '1002';
1401 END IF;
1402 l_error := TO_DATE(p_comments_in_offer,'DDMMYY');
1403 EXCEPTION
1404 WHEN OTHERS THEN
1405 p_error_code := '1002';
1406 END;
1407 END IF;
1408 -- For Conditional offer decision Comments in offer is required.
1409 ELSIF p_decision = 'C' AND p_transaction_type IN ('LA','LD','XA','XD')
1410 AND p_comments_in_offer IS NULL THEN
1411 p_error_code := '1003';
1412 ELSE
1413 p_error_code := NULL;
1414 END IF;
1415
1416 END transaction_validation;
1417
1418
1419 PROCEDURE proc_tranin_2003(p_conf_cycle IN igs_uc_defaults.configured_cycle%TYPE ) IS
1420
1421 /*************************************************************
1422 Created By : solakshm
1423 Date Created By : 23-JAN-2002
1424 Purpose: To write into TRANIN when the 'sent_to_ucas' and 'hold_flag' flag is no.
1425 and cycle is 2003 then write the transaction details in Tranin and
1426 Also updates igs_uc_transactions with info. from TRANIN and update the
1427 igs_uc_offer_conds table with status
1428 Know limitations, enhancements or remarks
1429 Change History
1430 Who When What
1431 rbezawad 4-Apr-2002 1) Inistitution Reference Column is added w.r.t. UCCR002 Data Model Change.
1432 2) Exception Handling part is modifiled
1433 rbezawad 2-May-2002 Modified w.r.t. UCCR003 Build.Bug No: 2311662
1434 In trans_write() procedure added validation for Checking Interface Type and perform validations accordingly.
1435 rbezawad 11-Jul-2002 Modified code in the exception handling part to raise the error when the exception occurs.
1436 Also while inserting records into traning, the columns TRANSACTIONID,TIMESTAMP,UPDATER,ERRORCODE values are passed as NULL.
1437 These modifications are done as part of 2450438.
1438 rbezawad 16-Jul-2002 While inserting Transactions into Tranin, for the program_code and Campus fields, logic is added to replace the '*' character with ' '.
1439 Added logic for better reporting while exporting transactions. Modifications are done w.r.t. Bug 2462096
1440 rbezawad 16-Jul-2002 Added logic to populate the columns SUMM_OF_COND, LETTER_TEXT columns of table IGS_UC_OFFER_CONDS w.r.t. Bug 2461913.
1441 smaddali 02-oct-2002 Modified for bug 2603384
1442 1) added new column auto_generated_flag to tbh calls to igs_uc_transactions_pkg
1443 2) modified cursor cur_records_to_write to fetch column auto_generated_flag
1444 3) modified igs_uc_transactions.update_row call to update test_cond_cat and name
1445 fields with proper values rather than with NULL
1446 ayedubat 05-DEC-2002 Changed the Log file generations of transactions being processed in the current run for bug: 2462078
1447 Replaced the message, IGS_UC_TRANS_PROC_LOG with the new message 'IGS_UC_TRANS_LOG_HEADER' for dsiplaying
1448 the header line for the transaction records.
1449 ayedubat 21-Mar-03 Removed local variable l_log_text w.r.t. Bug 2841582.
1450 pmarada 09-Jun-03 created this procedure Proc_Tranin_2003 and added cycle in required places. as per UCFD203 Multiple cycles
1451 arvsrini 26-APR-04 Added code to update IGS_UC_TRANSACTIONS.SOC based on errorcode value Bug#3576288
1452
1453 (reverse chronological order - newest change first)
1454 ***************************************************************/
1455
1456 CURSOR cur_records_to_write(cp_conf_cycle igs_uc_defaults.configured_cycle%TYPE) IS
1457 SELECT a.ROWID,a.uc_tran_id,a.transaction_id, a.datetimestamp,
1458 a.updater, a.error_code, a.transaction_type, a.app_no,
1459 a.choice_no, a.decision, a.program_code,
1460 a.campus, a.entry_month, a.entry_year, a.entry_point,
1461 a.soc, a.comments_in_offer, a.return1, a.return2,
1462 a.hold_flag, a.created_by, a.creation_date,
1463 a.last_updated_by, a.last_update_date, a.last_update_login,
1464 a.sent_to_ucas, a.test_cond_cat, a.test_cond_name, a.inst_reference ,
1465 a.auto_generated_flag, a.system_code , a.ucas_cycle, a.modular, a.part_time
1466 FROM igs_uc_transactions a, igs_uc_cyc_defaults b
1467 WHERE a.sent_to_ucas = 'N' AND a.hold_flag = 'N'
1468 AND a.ucas_cycle = cp_conf_cycle
1469 AND a.system_code = b.system_code
1470 AND a.ucas_cycle = b.ucas_cycle
1471 AND b.ucas_interface = 'H'
1472 ORDER BY a.creation_date;
1473
1474 CURSOR cur_update_info(cp_appno igs_uc_transactions.app_no%TYPE) IS
1475 SELECT appno, choiceno, transactionID, errorcode,
1476 timestamp, updater, return1, return2, soc
1477 FROM igs_uc_u_tranin_2003
1478 WHERE appno = cp_appno
1479 AND transactionID = (SELECT MAX(TransactionID)
1480 FROM igs_uc_u_tranin_2003
1481 WHERE appno = cp_appno);
1482
1483 CURSOR cur_offer_conds(cp_test_cond_cat igs_uc_transactions.test_cond_cat%TYPE,
1484 cp_test_cond_name igs_uc_transactions.test_cond_name%TYPE) IS
1485 SELECT a.ROWID,a.condition_category, a.condition_name,
1486 a.effective_from, a.effective_to, a.status, a.marvin_code,
1487 a.summ_of_cond, a.letter_text, a.created_by,
1488 a.creation_date, a.last_updated_by,
1489 a.last_update_date, a.last_update_login, a.decision
1490 FROM igs_uc_offer_conds a
1491 WHERE a.condition_category = cp_test_cond_cat
1492 AND a.condition_name = cp_test_cond_name;
1493
1494 l_app_no igs_uc_transactions.app_no%TYPE;
1495 l_user_id Varchar2(20) := fnd_global.user_id;
1496 l_soc igs_uc_transactions.soc%TYPE;
1497
1498 BEGIN
1499
1500 -- get the Transaction records from IGS_UC_TRANSACTIONS table and insert into Hercules igs_uc_u_tranin_2003 View.
1501 FOR uc_transaction_rec IN cur_records_to_write(p_conf_cycle)
1502 LOOP
1503 l_app_no := uc_transaction_rec.app_no;
1504
1505 INSERT INTO igs_uc_u_tranin_2003(transactionid,
1506 timestamp,
1507 updater,
1508 errorcode,
1509 transactiontype,
1510 appno,
1511 choiceno,
1512 decision,
1513 course,
1514 campus,
1515 entrymonth,
1516 entryyear,
1517 entrypoint,
1518 SOC,
1519 freeformat,
1520 return1,
1521 return2,
1522 instreference
1523 )
1524 VALUES(NULL,
1525 NULL,
1526 NULL,
1527 NULL,
1528 uc_transaction_rec.transaction_type,
1529 uc_transaction_rec.app_no,
1530 uc_transaction_rec.choice_no,
1531 uc_transaction_rec.decision,
1532 REPLACE(uc_transaction_rec.program_code, '*', ' '),
1533 REPLACE(uc_transaction_rec.campus, '*', ' '),
1534 uc_transaction_rec.entry_month,
1535 uc_transaction_rec.entry_year,
1536 uc_transaction_rec.entry_point,
1537 uc_transaction_rec.SOC,
1538 uc_transaction_rec.comments_in_offer,
1539 uc_transaction_rec.return1,
1540 uc_transaction_rec.return2,
1541 uc_transaction_rec.inst_reference
1542 );
1543
1544 -- Get back the values for transactionID,errorcode,timestamp,updater,return1,return2 columns for
1545 -- the newly inserted record from TranIn and Update the IGS_UC_TRANSACTIONS Table.
1546
1547 FOR tranin_rec IN cur_update_info(l_app_no)
1548 LOOP
1549
1550 IF tranin_rec.errorcode = 0 THEN --to update the SOC based on the errorcode value
1551 l_soc:=tranin_rec.soc;
1552 ELSE
1553 l_soc:=uc_transaction_rec.SOC;
1554 END IF;
1555
1556 igs_uc_transactions_pkg.update_row (
1557 x_mode => 'R',
1558 x_rowid => uc_transaction_rec.ROWID,
1559 x_uc_tran_id => uc_transaction_rec.UC_Tran_Id,
1560 x_transaction_id => tranin_rec.transactionid,
1561 x_datetimestamp => tranin_rec.timestamp,
1562 x_updater => tranin_rec.updater,
1563 x_error_code => tranin_rec.errorcode,
1564 x_transaction_type => uc_transaction_rec.transaction_type,
1565 x_app_no => uc_transaction_rec.app_no,
1566 x_choice_no => uc_transaction_rec.choice_no,
1567 x_decision => uc_transaction_rec.decision,
1568 x_program_code => uc_transaction_rec.program_code,
1569 x_campus => uc_transaction_rec.campus,
1570 x_entry_month => uc_transaction_rec.entry_month,
1571 x_entry_year => uc_transaction_rec.entry_year,
1572 x_entry_point => uc_transaction_rec.entry_point,
1573 x_soc => l_soc,
1574 x_comments_in_offer => uc_transaction_rec.comments_in_offer,
1575 x_return1 => tranin_rec.return1,
1576 x_return2 => tranin_rec.return2,
1577 x_hold_flag => uc_transaction_rec.hold_flag,
1578 x_sent_to_ucas => 'Y',
1579 x_test_cond_cat => uc_transaction_rec.test_cond_cat,
1580 x_test_cond_name => uc_transaction_rec.test_cond_name,
1581 x_inst_reference => uc_transaction_rec.inst_reference ,
1582 x_auto_generated_flag => uc_transaction_rec.auto_generated_flag,
1583 x_system_code => uc_transaction_rec.system_code,
1584 x_ucas_cycle => uc_transaction_rec.ucas_cycle,
1585 x_modular => uc_transaction_rec.modular,
1586 x_part_time => uc_transaction_rec.part_time);
1587 -- Check for 'XA' and 'XD' type transactions.
1588
1589 IF uc_transaction_rec.transaction_type = 'XA' OR uc_transaction_rec.transaction_type = 'XD' THEN
1590 --If errorcode returned from 'TranIn' view success then the status field in IGS_UC_OFFER_CONDS should be updated accordingly.
1591 --Also if errorcode returned is not success then the status field in IGS_UC_OFFER_CONDS should be updated with corresponding to failed.
1592
1593 IF tranin_rec.errorcode = 0 THEN
1594 FOR offer_conds_rec1 IN cur_offer_conds(uc_transaction_rec.test_cond_cat,uc_transaction_rec.test_cond_name)
1595 LOOP
1596 igs_uc_offer_conds_pkg.update_row (
1597 x_mode => 'R',
1598 x_rowid => offer_conds_rec1.ROWID,
1599 x_condition_category => offer_conds_rec1.condition_category,
1600 x_condition_name => offer_conds_rec1.condition_name,
1601 x_effective_from => offer_conds_rec1.effective_from,
1602 x_effective_to => offer_conds_rec1.effective_to,
1603 x_status => 'A',
1604 x_marvin_code => offer_conds_rec1.marvin_code,
1605 x_summ_of_cond => NVL(offer_conds_rec1.summ_of_cond, tranin_rec.soc),
1606 x_letter_text => tranin_rec.return2,
1607 x_decision => offer_conds_rec1.decision
1608 );
1609 END LOOP;
1610 ELSE
1611 FOR offer_conds_rec1 IN cur_offer_conds(uc_transaction_rec.test_cond_cat,uc_transaction_rec.test_cond_name)
1612 LOOP
1613 igs_uc_offer_conds_pkg.update_row (
1614 x_mode => 'R',
1615 x_rowid => offer_conds_rec1.ROWID,
1616 x_condition_category => offer_conds_rec1.condition_category,
1617 x_condition_name => offer_conds_rec1.condition_name,
1618 x_effective_from => offer_conds_rec1.effective_from,
1619 x_effective_to => offer_conds_rec1.effective_to,
1620 x_status => 'F',
1621 x_marvin_code => offer_conds_rec1.marvin_code,
1622 x_summ_of_cond => NVL(offer_conds_rec1.summ_of_cond, tranin_rec.soc),
1623 x_letter_text => tranin_rec.return2,
1624 x_decision => offer_conds_rec1.decision
1625 );
1626 END LOOP;
1627 END IF; --end error code
1628 END IF; --end 'XA', 'XD'
1629
1630 --To generate the Transaction record in the Log file
1631 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(tranin_rec.transactionid,16)|| RPAD(tranin_rec.appno,20) || RPAD(tranin_rec.choiceno,15) ||
1632 RPAD(NVL(TO_CHAR(tranin_rec.timestamp,'DD-MON-YYYY HH24:MI:SS'),' '),23) ||
1633 RPAD(NVL(TO_CHAR(tranin_rec.errorcode),' '),12) || RPAD(NVL(TO_CHAR(tranin_rec.return1),' '),9) ||
1634 tranin_rec.return2);
1635
1636 END LOOP;
1637 END LOOP;
1638
1639 EXCEPTION
1640 WHEN OTHERS THEN
1641 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1642 Fnd_Message.Set_Token('NAME','igs_uc_tran_processor_pkg.proc_tranin_2003');
1643 fnd_file.put_line(fnd_file.log, fnd_message.get);
1644 App_Exception.Raise_Exception;
1645
1646 END proc_tranin_2003;
1647
1648 PROCEDURE proc_tranin_2004(p_conf_cycle IN igs_uc_defaults.configured_cycle%TYPE,
1649 p_system_code IN igs_uc_ucas_control.system_code%TYPE) IS
1650
1651 /*********************************************************************
1652 Created By : pmarada
1653 Date Created By : 10-June-2003
1654 Purpose: To write into TRANIN when the 'sent_to_ucas' and 'hold_flag' flag is no.
1655 and cycle is 2004 then write the transaction details into Tranin and
1656 Also updates igs_uc_transactions with info. from TRANIN, and update the
1657 igs_uc_offer_conds table with status.
1658 Know limitations, enhancements or remarks
1659 Change History (reverse chronological order - newest change first)
1660
1661 Who When What
1662 jchakrab 23-May-2006 Modified for 5155053 - use roundno column (for choice no) for GTTR transactions
1663 jbaber 19-Aug-2005 Modified for UC307 - HERCULES Small Systems Support
1664 Added p_system_code parameter
1665 jbaber 12-Jul-2004 Modified for UC315 - UCAS 2006 Support
1666 Appended check_digit to appno when configured cycle > 2005
1667 arvsrini 26-Apr-2004 Added code to update IGS_UC_TRANSACTIONS.SOC based on
1668 the errorcode value Bug#3576288
1669 **********************************************************************/
1670
1671 CURSOR cur_records_to_write(cp_conf_cycle igs_uc_defaults.configured_cycle%TYPE) IS
1672 SELECT a.ROWID,a.uc_tran_id,a.transaction_id, a.datetimestamp,
1673 a.updater, a.error_code, a.transaction_type, a.app_no,
1674 a.choice_no, a.decision, a.program_code,
1675 a.campus, a.entry_month, a.entry_year, a.entry_point,
1676 a.soc, a.comments_in_offer, a.return1, a.return2,
1677 a.hold_flag, a.created_by, a.creation_date,
1678 a.last_updated_by, a.last_update_date, a.last_update_login,
1679 a.sent_to_ucas, a.test_cond_cat, a.test_cond_name, a.inst_reference ,
1680 a.auto_generated_flag, a.system_code , a.ucas_cycle, a.modular, a.part_time
1681 FROM igs_uc_transactions a, igs_uc_cyc_defaults b
1682 WHERE a.sent_to_ucas = 'N' AND a.hold_flag = 'N'
1683 AND a.ucas_cycle = cp_conf_cycle
1684 AND a.system_code = p_system_code
1685 AND a.system_code = b.system_code
1686 AND a.ucas_cycle = b.ucas_cycle
1687 AND b.ucas_interface = 'H'
1688 ORDER BY a.creation_date;
1689
1690 CURSOR cur_update_info(cp_appno igs_uc_transactions.app_no%TYPE) IS
1691 SELECT appno, choiceno, transactionID, errorcode,
1692 timestamp, updater, return1, return2, soc, roundno
1693 FROM igs_uc_u_tranin_2004
1694 WHERE appno = cp_appno
1695 AND transactionID = (SELECT MAX(TransactionID)
1696 FROM igs_uc_u_tranin_2004
1697 WHERE appno = cp_appno);
1698
1699 CURSOR cur_offer_conds(cp_test_cond_cat igs_uc_transactions.test_cond_cat%TYPE,
1700 cp_test_cond_name igs_uc_transactions.test_cond_name%TYPE) IS
1701 SELECT a.ROWID,a.condition_category, a.condition_name,
1702 a.effective_from, a.effective_to, a.status, a.marvin_code,
1703 a.summ_of_cond, a.letter_text, a.created_by,
1704 a.creation_date, a.last_updated_by,
1705 a.last_update_date, a.last_update_login, a.decision
1706 FROM igs_uc_offer_conds a
1707 WHERE a.condition_category = cp_test_cond_cat
1708 AND a.condition_name = cp_test_cond_name;
1709
1710 -- Cursor to convert 8-digit appno to 9 digit NUMBER with check digit for UC315 - UCAS 2006 Support
1711 CURSOR cur_appno(cp_appno igs_uc_applicants.app_no%TYPE) IS
1712 SELECT TO_NUMBER(app_no || check_digit)
1713 FROM igs_uc_applicants
1714 WHERE app_no = cp_appno;
1715
1716 l_user_id Varchar2(20) := fnd_global.user_id;
1717 l_soc igs_uc_transactions.soc%TYPE;
1718 l_appno igs_uc_u_tranin_2004.appno%TYPE;
1719
1720 BEGIN
1721
1722 --When the Interface is Hercules then get the Transaction records from IGS_UC_TRANSACTIONS table and insert into Hercules igs_uc_u_tranin_2004 .
1723 FOR uc_transaction_rec IN cur_records_to_write(p_conf_cycle)
1724 LOOP
1725
1726 -- For cycle 2004 syscode, roundno, modular, parttime columns are added in tranin table
1727 -- Determine appno based on configured year.
1728 IF p_conf_cycle < 2006 THEN
1729 l_appno := uc_transaction_rec.app_no;
1730 ELSE
1731 -- Convert 8-digit appno to 9 digit NUMBER with check digit for UC315 - UCAS 2006 Support
1732 OPEN cur_appno(uc_transaction_rec.app_no);
1733 FETCH cur_appno INTO l_appno;
1734 CLOSE cur_appno;
1735 END IF;
1736
1737 IF uc_transaction_rec.system_code = 'G' THEN
1738
1739 INSERT INTO igs_uc_u_tranin_2004(transactionid,
1740 timestamp,
1741 updater,
1742 errorcode,
1743 transactiontype,
1744 appno,
1745 choiceno,
1746 decision,
1747 course,
1748 campus,
1749 entrymonth,
1750 entryyear,
1751 entrypoint,
1752 SOC,
1753 freeformat,
1754 return1,
1755 return2,
1756 instreference,
1757 syscode,
1758 roundno,
1759 modular,
1760 parttime)
1761 VALUES(NULL,
1762 NULL,
1763 NULL,
1764 NULL,
1765 uc_transaction_rec.transaction_type,
1766 l_appno,
1767 NULL,
1768 uc_transaction_rec.decision,
1769 REPLACE(uc_transaction_rec.program_code, '*', ' '),
1770 REPLACE(uc_transaction_rec.campus, '*', ' '),
1771 uc_transaction_rec.entry_month,
1772 uc_transaction_rec.entry_year,
1773 uc_transaction_rec.entry_point,
1774 uc_transaction_rec.SOC,
1775 uc_transaction_rec.comments_in_offer,
1776 uc_transaction_rec.return1,
1777 uc_transaction_rec.return2,
1778 uc_transaction_rec.inst_reference,
1779 uc_transaction_rec.system_code,
1780 uc_transaction_rec.choice_no,
1781 uc_transaction_rec.modular,
1782 uc_transaction_rec.part_time);
1783
1784 ELSE
1785
1786 INSERT INTO igs_uc_u_tranin_2004(transactionid,
1787 timestamp,
1788 updater,
1789 errorcode,
1790 transactiontype,
1791 appno,
1792 choiceno,
1793 decision,
1794 course,
1795 campus,
1796 entrymonth,
1797 entryyear,
1798 entrypoint,
1799 SOC,
1800 freeformat,
1801 return1,
1802 return2,
1803 instreference,
1804 syscode,
1805 roundno,
1806 modular,
1807 parttime)
1808 VALUES(NULL,
1809 NULL,
1810 NULL,
1811 NULL,
1812 uc_transaction_rec.transaction_type,
1813 l_appno,
1814 uc_transaction_rec.choice_no,
1815 uc_transaction_rec.decision,
1816 REPLACE(uc_transaction_rec.program_code, '*', ' '),
1817 REPLACE(uc_transaction_rec.campus, '*', ' '),
1818 uc_transaction_rec.entry_month,
1819 uc_transaction_rec.entry_year,
1820 uc_transaction_rec.entry_point,
1821 uc_transaction_rec.SOC,
1822 uc_transaction_rec.comments_in_offer,
1823 uc_transaction_rec.return1,
1824 uc_transaction_rec.return2,
1825 uc_transaction_rec.inst_reference,
1826 uc_transaction_rec.system_code,
1827 NULL,
1828 NULL,
1829 NULL);
1830
1831 END IF;
1832
1833 -- Get back the values for transactionID,errorcode,timestamp,updater,return1,return2 columns for
1834 -- the newly inserted record from igs_uc_u_tranin_2004 and Update the IGS_UC_TRANSACTIONS Table.
1835
1836 FOR tranin_rec IN cur_update_info(l_appno)
1837 LOOP
1838
1839 IF tranin_rec.errorcode = 0 THEN --to update the SOC based on the errorcode value
1840 l_soc:=tranin_rec.soc;
1841 ELSE
1842 l_soc:=uc_transaction_rec.SOC;
1843 END IF;
1844
1845 igs_uc_transactions_pkg.update_row (
1846 x_mode => 'R',
1847 x_rowid => uc_transaction_rec.ROWID,
1848 x_uc_tran_id => uc_transaction_rec.UC_Tran_Id,
1849 x_transaction_id => tranin_rec.transactionid,
1850 x_datetimestamp => tranin_rec.timestamp,
1851 x_updater => tranin_rec.updater,
1852 x_error_code => tranin_rec.errorcode,
1853 x_transaction_type => uc_transaction_rec.transaction_type,
1854 x_app_no => uc_transaction_rec.app_no,
1855 x_choice_no => uc_transaction_rec.choice_no,
1856 x_decision => uc_transaction_rec.decision,
1857 x_program_code => uc_transaction_rec.program_code,
1858 x_campus => uc_transaction_rec.campus,
1859 x_entry_month => uc_transaction_rec.entry_month,
1860 x_entry_year => uc_transaction_rec.entry_year,
1861 x_entry_point => uc_transaction_rec.entry_point,
1862 x_soc => l_soc,
1863 x_comments_in_offer => uc_transaction_rec.comments_in_offer,
1864 x_return1 => tranin_rec.return1,
1865 x_return2 => tranin_rec.return2,
1866 x_hold_flag => uc_transaction_rec.hold_flag,
1867 x_sent_to_ucas => 'Y',
1868 x_test_cond_cat => uc_transaction_rec.test_cond_cat,
1869 x_test_cond_name => uc_transaction_rec.test_cond_name,
1870 x_inst_reference => uc_transaction_rec.inst_reference ,
1871 x_auto_generated_flag => uc_transaction_rec.auto_generated_flag,
1872 x_system_code => uc_transaction_rec.system_code,
1873 x_ucas_cycle => uc_transaction_rec.ucas_cycle,
1874 x_modular => uc_transaction_rec.modular,
1875 x_part_time => uc_transaction_rec.part_time);
1876 -- Check for 'XA' and 'XD' type transactions.
1877
1878 IF uc_transaction_rec.transaction_type = 'XA' OR uc_transaction_rec.transaction_type = 'XD' THEN
1879 --If errorcode returned from 'TranIn' view success then the status field in IGS_UC_OFFER_CONDS should be updated accordingly.
1880 --Also if errorcode returned is not success then the status field in IGS_UC_OFFER_CONDS should be updated with corresponding to failed.
1881
1882 IF tranin_rec.errorcode = 0 THEN
1883 FOR offer_conds_rec1 IN cur_offer_conds(uc_transaction_rec.test_cond_cat,uc_transaction_rec.test_cond_name)
1884 LOOP
1885 igs_uc_offer_conds_pkg.update_row (
1886 x_mode => 'R',
1887 x_rowid => offer_conds_rec1.ROWID,
1888 x_condition_category => offer_conds_rec1.condition_category,
1889 x_condition_name => offer_conds_rec1.condition_name,
1890 x_effective_from => offer_conds_rec1.effective_from,
1891 x_effective_to => offer_conds_rec1.effective_to,
1892 x_status => 'A',
1893 x_marvin_code => offer_conds_rec1.marvin_code,
1894 x_summ_of_cond => NVL(offer_conds_rec1.summ_of_cond, tranin_rec.soc),
1895 x_letter_text => tranin_rec.return2,
1896 x_decision => offer_conds_rec1.decision
1897 );
1898
1899 END LOOP;
1900 ELSE
1901 FOR offer_conds_rec1 IN cur_offer_conds(uc_transaction_rec.test_cond_cat,uc_transaction_rec.test_cond_name)
1902 LOOP
1903 igs_uc_offer_conds_pkg.update_row (
1904 x_mode => 'R',
1905 x_rowid => offer_conds_rec1.ROWID,
1906 x_condition_category => offer_conds_rec1.condition_category,
1907 x_condition_name => offer_conds_rec1.condition_name,
1908 x_effective_from => offer_conds_rec1.effective_from,
1909 x_effective_to => offer_conds_rec1.effective_to,
1910 x_status => 'F',
1911 x_marvin_code => offer_conds_rec1.marvin_code,
1912 x_summ_of_cond => NVL(offer_conds_rec1.summ_of_cond, tranin_rec.soc),
1913 x_letter_text => tranin_rec.return2,
1914 x_decision => offer_conds_rec1.decision
1915 );
1916 END LOOP;
1917 END IF; --end error code
1918 END IF; --end 'XA', 'XD'
1919
1920 IF uc_transaction_rec.system_code = 'G' THEN
1921 --To generate the Transaction record in the Log file
1922 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(tranin_rec.transactionid,16)|| RPAD(tranin_rec.appno,20) || RPAD(tranin_rec.roundno,15) ||
1923 RPAD(NVL(TO_CHAR(tranin_rec.timestamp,'DD-MON-YYYY HH24:MI:SS'),' '),23) ||
1924 RPAD(NVL(TO_CHAR(tranin_rec.errorcode),' '),12) || RPAD(NVL(TO_CHAR(tranin_rec.return1),' '),9) ||
1925 tranin_rec.return2);
1926 ELSE
1927 --To generate the Transaction record in the Log file
1928 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(tranin_rec.transactionid,16)|| RPAD(tranin_rec.appno,20) || RPAD(tranin_rec.choiceno,15) ||
1929 RPAD(NVL(TO_CHAR(tranin_rec.timestamp,'DD-MON-YYYY HH24:MI:SS'),' '),23) ||
1930 RPAD(NVL(TO_CHAR(tranin_rec.errorcode),' '),12) || RPAD(NVL(TO_CHAR(tranin_rec.return1),' '),9) ||
1931 tranin_rec.return2);
1932 END IF;
1933
1934 END LOOP;
1935 END LOOP;
1936
1937 EXCEPTION
1938 WHEN OTHERS THEN
1939 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1940 Fnd_Message.Set_Token('NAME','igs_uc_tran_processor_pkg.proc_tranin_2004'||' - '||SQLERRM);
1941 fnd_file.put_line(fnd_file.log, fnd_message.get);
1942 App_Exception.Raise_Exception;
1943
1944 END proc_tranin_2004;
1945
1946 PROCEDURE trans_write(p_system_code IN igs_uc_ucas_control.system_code%TYPE,
1947 errbuf OUT NOCOPY VARCHAR2,
1948 retcode OUT NOCOPY NUMBER) IS
1949 /*************************************************************
1950 Created By : Pmarada
1951 Date Created By : 10-Jun-2003
1952 Purpose : This is main procedure, the procedure will calls the subprocedures
1953 dependends on the cycle.as per the UCFD203 Multiple cycles build.
1954 Know limitations, enhancements or remarks
1955 Change History (reverse chronological order - newest change first)
1956 who when what
1957 jbaber 19-Aug-05 Modified for UC307 - HERCULES Small Systems Support
1958 Added p_system_code parameter
1959 jbaber 12-Jul-05 Modified for UC315 - UCAS Support 2006
1960 Replaced reference to igs_uc_cvcontrol_2003_v with igs_uc_ucas_control
1961 jchakrab 27-Jul-04 Modified for UCFD308 - UCAS - 2005 Regulatory Changes
1962 rgangara 23-Jan-04 Changed the variable declaration of L_entry_year to type
1963 of configured cycle i.e 4 Digit Number from cvcontrol.entryyear
1964 which is a 2 Digit Number as fix for bug# 3392506
1965
1966 *************************************************************/
1967
1968 CURSOR cur_cycle IS
1969 SELECT max(current_cycle) current_cycle, max(configured_cycle) configured_cycle
1970 FROM igs_uc_defaults ;
1971
1972 cur_cycle_rec cur_cycle%ROWTYPE;
1973
1974 l_entry_year igs_uc_defaults.configured_cycle%TYPE;
1975
1976 BEGIN
1977 --To generate the Header Line in the Log File
1978 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('-',105,'-'));
1979 FND_MESSAGE.SET_NAME('IGS','IGS_UC_TRANS_LOG_HEADER');
1980 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1981 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('-',105,'-'));
1982
1983 -- get the ucas configured cycle
1984 OPEN cur_cycle;
1985 FETCH cur_cycle INTO cur_cycle_rec;
1986 CLOSE cur_cycle;
1987
1988 -- Depends on the cycle call the respective cycle procedure
1989 -- If cycle is 2003 call the Proc_Tranin_2003
1990 IF cur_cycle_rec.configured_cycle = 2003 THEN
1991 Proc_Tranin_2003(p_conf_cycle => cur_cycle_rec.configured_cycle);
1992 -- If cycle is 2004 or 2005 call the Proc_Tranin_2004
1993 ELSIF cur_cycle_rec.configured_cycle = 2004 OR cur_cycle_rec.configured_cycle = 2005 OR cur_cycle_rec.configured_cycle = 2006 OR cur_cycle_rec.configured_cycle = 2007 THEN
1994 Proc_Tranin_2004(p_conf_cycle => cur_cycle_rec.configured_cycle,
1995 p_system_code => p_system_code);
1996 END IF;
1997
1998 EXCEPTION
1999 WHEN OTHERS THEN
2000 ROLLBACK;
2001 retcode :=2;
2002 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2003 FND_MESSAGE.SET_TOKEN('NAME','IGS_UC_TRAN_PROCESSOR_PKG.TRANS_WRITE'||' - '||SQLERRM);
2004 Errbuf := FND_MESSAGE.GET;
2005 FND_FILE.PUT_LINE(FND_FILE.LOG,Errbuf);
2006 app_exception.raise_exception;
2007
2008 END trans_write;
2009
2010 FUNCTION get_adm_offer_resp_stat (
2011 p_alt_appl_id IN igs_ad_appl_all.alt_appl_id%TYPE,
2012 p_choice_number IN igs_ad_appl_all.choice_number%TYPE,
2013 p_old_outcome_status IN igs_ad_ps_appl_inst_all.adm_outcome_status%TYPE,
2014 p_new_outcome_status IN igs_ad_ps_appl_inst_all.adm_outcome_status%TYPE,
2015 p_old_adm_offer_resp_status IN igs_ad_ps_appl_inst_all.adm_offer_resp_status%TYPE,
2016 p_message_name OUT NOCOPY VARCHAR2
2017 ) RETURN VARCHAR2 IS
2018
2019 /*************************************************************
2020 Created By : rghosh
2021 Date Created By : 01-Apr-2003
2022 Purpose : this function will return the user defined offer response status mapped with
2023 which the Admission Application instance has to be updated with, after validating
2024 the Old and New Outcome statuses and Offer Response Status against UCAS setup.
2025 Know limitations, enhancements or remarks
2026 Change History
2027
2028 *************************************************************/
2029
2030 -- Cursor to find the system code associated with the UCAS application
2031
2032 CURSOR c_sys_cd (cp_alt_appl_id igs_ad_appl_all.alt_appl_id%TYPE ) IS
2033 SELECT system_code
2034 FROM igs_uc_applicants
2035 WHERE TO_CHAR(app_no) = cp_alt_appl_id;
2036
2037 l_sys_cd igs_uc_applicants.system_code%TYPE;
2038
2039 --Cursor to get the decision code associated with the current outcome status and the new outcome status
2040
2041 CURSOR c_decision_cd(cp_system_code igs_uc_app_choices.system_code%TYPE,
2042 cp_outcome_status igs_ad_ps_appl_inst_all.adm_outcome_status%TYPE) IS
2043 SELECT decision_code
2044 FROM igs_uc_map_out_stat
2045 WHERE system_code = cp_system_code
2046 AND adm_outcome_status = cp_outcome_status
2047 AND closed_ind = 'N';
2048
2049 l_decision_code igs_uc_map_out_stat.decision_code%TYPE;
2050
2051 -- Cursor to find out if the reply code of Firm Acceptance is mapped to the decision code returned from the cursor (c_decision_cd) with the old outcome status
2052
2053 CURSOR c_curr_off_resp_status (cp_system_code igs_uc_app_choices.system_code%TYPE,
2054 cp_decision_code igs_uc_map_out_stat.decision_code%TYPE,
2055 cp_adm_offer_resp_status igs_ad_ps_appl_inst_all.adm_offer_resp_status%TYPE) IS
2056 SELECT 'X'
2057 FROM igs_uc_map_off_resp
2058 WHERE decision_code= cp_decision_code
2059 AND system_code = cp_system_code
2060 AND closed_ind = 'N'
2061 AND reply_code = 'F'
2062 AND adm_offer_resp_status = cp_adm_offer_resp_status;
2063
2064 l_curr_off_resp_status VARCHAR2(1);
2065
2066 --Cursor to select the decision code associated with the new outcome status
2067
2068 l_off_resp_dec_cd igs_uc_map_out_stat.decision_code%TYPE;
2069
2070 -- Cursor to get the admission offer response mapped out to the reply code of Firm Acceptance and decision code returned from the cursor (c_decision_cd) with the new
2071 -- outcome status
2072
2073 CURSOR c_new_off_resp_status(cp_system_code igs_uc_app_choices.system_code%TYPE,
2074 cp_decision_code igs_uc_map_out_stat.decision_code%TYPE) IS
2075 SELECT adm_offer_resp_status
2076 FROM igs_uc_map_off_resp
2077 WHERE system_code = cp_system_code
2078 AND decision_code= cp_decision_code
2079 AND closed_ind = 'N'
2080 AND reply_code = 'F' ;
2081
2082 l_new_off_resp_status igs_uc_map_off_resp.adm_offer_resp_status%TYPE;
2083
2084 BEGIN
2085
2086 OPEN c_sys_cd(p_alt_appl_id);
2087 FETCH c_sys_cd INTO l_sys_cd;
2088 IF c_sys_cd%NOTFOUND THEN
2089 CLOSE c_sys_cd;
2090 RETURN NULL;
2091 END IF;
2092 CLOSE c_sys_cd;
2093
2094 OPEN c_decision_cd(l_sys_cd,p_old_outcome_status);
2095 FETCH c_decision_cd INTO l_decision_code;
2096 IF c_decision_cd%NOTFOUND THEN
2097 CLOSE c_decision_cd;
2098 p_message_name := 'IGS_UC_NO_ACT_DEC_CODE_CONDOFR';
2099 RETURN NULL;
2100 END IF;
2101 CLOSE c_decision_cd;
2102
2103 OPEN c_curr_off_resp_status(l_sys_cd,l_decision_code,p_old_adm_offer_resp_status);
2104 FETCH c_curr_off_resp_status INTO l_curr_off_resp_status;
2105 IF c_curr_off_resp_status%NOTFOUND THEN
2106 CLOSE c_curr_off_resp_status;
2107 p_message_name := 'IGS_UC_NO_ACTIVE_REPLY_CODE';
2108 RETURN NULL;
2109 END IF;
2110 CLOSE c_curr_off_resp_status;
2111
2112 OPEN c_decision_cd(l_sys_cd,p_new_outcome_status);
2113 FETCH c_decision_cd INTO l_off_resp_dec_cd;
2114 IF c_decision_cd%NOTFOUND THEN
2115 CLOSE c_decision_cd;
2116 p_message_name := 'IGS_UC_NO_ACT_DEC_CODE_OFFER';
2117 RETURN NULL;
2118 END IF;
2119 CLOSE c_decision_cd;
2120
2121 OPEN c_new_off_resp_status(l_sys_cd,l_off_resp_dec_cd);
2122 FETCH c_new_off_resp_status INTO l_new_off_resp_status;
2123 IF c_new_off_resp_status%NOTFOUND THEN
2124 CLOSE c_new_off_resp_status;
2125 p_message_name := 'IGS_UC_NO_MAP_REPLY_CODE';
2126 RETURN NULL;
2127 END IF;
2128 CLOSE c_new_off_resp_status;
2129
2130 RETURN l_new_off_resp_status;
2131
2132
2133 EXCEPTION
2134
2135 WHEN OTHERS THEN
2136
2137 IF c_sys_cd%ISOPEN THEN
2138 CLOSE c_sys_cd;
2139 END IF;
2140
2141 IF c_decision_cd%ISOPEN THEN
2142 CLOSE c_decision_cd;
2143 END IF;
2144
2145 IF c_curr_off_resp_status%ISOPEN THEN
2146 CLOSE c_curr_off_resp_status;
2147 END IF;
2148
2149 IF c_new_off_resp_status%ISOPEN THEN
2150 CLOSE c_new_off_resp_status;
2151 END IF;
2152
2153 RETURN NULL;
2154
2155 END get_adm_offer_resp_stat;
2156
2157 END igs_uc_tran_processor_pkg;