1 PACKAGE BODY igs_uc_trx_gen_hook AS
2 /* $Header: IGSUC21B.pls 120.2 2006/08/21 03:51:45 jbaber noship $ */
3 l_alt_appl_id igs_ad_appl.alt_appl_id%TYPE;
4 lv_choice_no igs_uc_transactions.choice_no%TYPE;
5 lv_person_number igs_pe_person.person_number%TYPE;
6 lv_admission_appl_number igs_uc_transactions.app_no%TYPE;
7 lv_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE;
8 lv_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE;
9 lv_out_come_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE;
10 lv_cond_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE;
11 lv_system_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
12 lv_return1 igs_uc_transactions.return1%TYPE;
13 lv_return2 igs_uc_transactions.return2%TYPE;
14
15 PROCEDURE create_ucas_transactions(
16 p_ucas_id IN igs_pe_person.api_person_id%TYPE,
17 p_choice_number IN igs_uc_transactions.choice_no%TYPE,
18 p_person_number IN igs_pe_person.person_number%TYPE,
19 p_admission_appl_number IN igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
20 p_nominated_course_cd IN igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
21 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,
22 p_outcome_status IN igs_ad_ps_appl_inst.adm_outcome_status%TYPE,
23 p_cond_offer_status IN igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE,
24 p_alt_appl_id IN igs_ad_appl.alt_appl_id%TYPE,
25 p_condition_category IN igs_uc_offer_conds.condition_category%TYPE DEFAULT NULL,
26 p_condition_name IN igs_uc_offer_conds.condition_name%TYPE DEFAULT NULL,
27 p_uc_tran_id OUT NOCOPY NUMBER
28 ) IS
29
30 /*************************************************************
31 Created By : vbandaru
32 Date Created By : 23-JAN-2002
33 Purpose :
34
35 Know limitations, enhancements or remarks
36 Change History
37 Who When What
38 anwest 29-May-2006 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
39 rbezawad 31-Jul-2002 While creating the LD Transaction with Withdrawn Status(W), the comments_in_offer field was being populated
40 with the text 'WITHDRAWN'. The code removed and the parameter is passed with NULL value w.r.t. Bug 2483587.
41 rbezawad 17-Sep-2002 Added logic to create RD or RX transactions in clearing period. And also added logic to add the message to stack
42 which was returned with return1, return2 OUT NOCOPY parameters when some error is occurred in igs_uc_tran_processor_pkg.trans_build().
43 Modifications are done w.r.t. UCFD06 Build 2570389.
44 smaddali 02-oct-2002 Modified for bug 2603384 ,
45 1) added 3 new parameters to trans_build call .passing auto_generated_flag='Y' since
46 these transactions are automatically generated
47 pmarada 14-Nov-2002 Modified as per the small systems TD, added the p_alt_appl_id parameter
48 (reverse chronological order - newest change first)
49 ***************************************************************/
50
51 -- Get the ucas_cycle from app_choices;
52 CURSOR cur_cycle(cp_app_no igs_uc_app_choices.app_no%TYPE, cp_choice_no igs_uc_app_choices.choice_no%TYPE) IS
53 SELECT MAX(ucas_cycle) FROM igs_uc_app_choices
54 WHERE app_no = cp_app_no
55 AND choice_no = cp_choice_no;
56
57 l_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE;
58
59 -- Get the decision and reply, program details for the applicant choice.
60 CURSOR c_app_choices(cp_app_no igs_uc_app_choices.app_no%TYPE,
61 cp_choice_no igs_uc_app_choices.choice_no%TYPE,
62 cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
63 SELECT decision, reply , ucas_program_code, campus
64 FROM igs_uc_app_choices
65 WHERE app_no= cp_app_no
66 AND choice_no = cp_choice_no
67 AND ucas_cycle = cp_ucas_cycle;
68
69 -- Get the system code using the alternate application id (appno)
70 CURSOR cur_system_code(cp_appno igs_uc_applicants.app_no%TYPE) IS
71 SELECT system_code FROM igs_uc_applicants WHERE app_no = cp_appno;
72
73 -- Check whether clearing flag is set for the system
74 CURSOR cur_clearing (cp_system_code igs_uc_cyc_defaults.system_code%TYPE,
75 cp_ucas_cycle igs_uc_cyc_defaults.ucas_cycle%TYPE ) IS
76 SELECT clearing_flag FROM igs_uc_cyc_defaults
77 WHERE system_code = cp_system_code
78 AND ucas_cycle = cp_ucas_cycle;
79 l_clearing igs_uc_cyc_defaults.clearing_flag%TYPE;
80
81 -- Check whether exists any clearing record for the applicant.
82 CURSOR cur_app_clearing(cp_app_no igs_uc_app_clearing.app_no%TYPE) IS
83 SELECT 'X'
84 FROM igs_uc_app_clearing
85 WHERE app_no = cp_app_no;
86
87 app_clearing_rec cur_app_clearing%ROWTYPE;
88
89 -- Check whether exists any clearing round details for the applicant.
90 CURSOR cur_app_clr_rnd(cp_app_no igs_uc_app_clr_rnd.app_no%TYPE) IS
91 SELECT 'X'
92 FROM igs_uc_app_clr_rnd
93 WHERE app_no = cp_app_no;
94
95 -- To Fetch the Decision Mapping to Admission Outcome Status for a UCAS System
96 CURSOR ucas_decision_cd_cur( p_system_code igs_uc_map_out_stat.system_code%TYPE,
97 p_adm_outcome_status igs_uc_map_out_stat.adm_outcome_status%TYPE) IS
98 SELECT decision_code
99 FROM igs_uc_map_out_stat
100 WHERE system_code = p_system_code AND
101 adm_outcome_status = p_adm_outcome_status AND
102 closed_ind = 'N';
103
104 -- To Fetch the Name of the UCAS System
105 CURSOR system_name_cur ( p_system_code igs_uc_defaults.system_code%TYPE) IS
106 SELECT name
107 FROM igs_uc_defaults
108 WHERE system_code = p_system_code;
109
110 app_clr_rnd_rec cur_app_clr_rnd%ROWTYPE;
111
112 l_decision_value igs_uc_app_choices.decision%TYPE;
113 l_transaction_type igs_uc_transactions.transaction_type%TYPE;
114 l_decision igs_uc_app_choices.decision%TYPE;
115 l_reply igs_uc_app_choices.reply%TYPE;
116 l_ucas_program_code igs_uc_app_choices.ucas_program_code%TYPE;
117 l_campus igs_uc_app_choices.campus%TYPE;
118 l_system_code igs_uc_cyc_defaults.system_code%TYPE;
119 l_system_name igs_uc_defaults.name%TYPE;
120 l_validate_error_cd igs_lookup_values.lookup_code%TYPE;
121 l_transaction_toy igs_uc_ucas_control.transaction_toy_code%TYPE;
122
123 BEGIN
124
125 SAVEPOINT ucas_transactions;
126
127 l_system_name := NULL;
128 l_decision_value := NULL;
129 l_alt_appl_id := TO_NUMBER(p_alt_appl_id); --alternate application id
130 lv_choice_no := TO_NUMBER(p_choice_number);
131 lv_person_number := p_person_number;
132 lv_admission_appl_number := p_admission_appl_number;
133 lv_nominated_course_cd := p_nominated_course_cd;
134 lv_sequence_number := p_sequence_number;
135 lv_out_come_status := p_outcome_status;
136 lv_cond_offer_status := p_cond_offer_status;
137 lv_system_outcome_status := igs_ad_gen_008.admp_get_saos(lv_out_come_status); -- It returns the system outcome status
138 p_uc_tran_id := NULL;
139
140 OPEN cur_cycle(l_alt_appl_id,lv_choice_no);
141 FETCH cur_cycle INTO l_ucas_cycle;
142 CLOSE cur_cycle;
143
144 -- Get the Ucas decision, reply and Ucas program ,campus details for further usage
145 OPEN c_app_choices(l_alt_appl_id,lv_choice_no,l_ucas_cycle);
146 FETCH c_app_choices INTO l_decision, l_reply, l_ucas_program_code, l_campus;
147
148 -- check whether any records exists for the applicant choices
149 IF (c_app_choices%NOTFOUND) THEN
150 CLOSE c_app_choices;
151 fnd_message.set_name('IGS','IGS_UC_DEC_REP_NOT_FOUND');
152 fnd_message.set_token('APPNO', l_alt_appl_id);
153 fnd_message.set_token('CHOICENO', TO_CHAR(lv_choice_no));
154 igs_ge_msg_stack.add;
155 RETURN;
156 ELSE
157 -- get the system code for the applicant.
158 OPEN cur_system_code(l_alt_appl_id);
159 FETCH cur_system_code INTO l_system_code;
160 CLOSE cur_system_code;
161
162 -- get the Decision Code maaped to the System Code and Admission Outcome Status
163 OPEN ucas_decision_cd_cur (l_system_code, lv_out_come_status);
164 FETCH ucas_decision_cd_cur INTO l_decision_value;
165
166 -- check whether any records exists in the mapping table
167 IF (ucas_decision_cd_cur%NOTFOUND) THEN
168 CLOSE ucas_decision_cd_cur;
169
170 OPEN system_name_cur(l_system_code);
171 FETCH system_name_cur INTO l_system_name;
172 CLOSE system_name_cur;
173
174 fnd_message.set_name('IGS','IGS_UC_DECISION_MAP_NOT_FOUND');
175 fnd_message.set_token('SYS_NAME',l_system_name );
176 fnd_message.set_token('ADM_OUTSTAT', lv_out_come_status);
177 igs_ge_msg_stack.add;
178 RETURN;
179 END IF;
180 CLOSE ucas_decision_cd_cur;
181
182 -- Get the clearing flag value for the the system, for checking the system allows clearing or not
183 OPEN cur_clearing(l_system_code,l_ucas_cycle);
184 FETCH cur_clearing INTO l_clearing;
185 CLOSE cur_clearing;
186
187 -- If processing period is clearing then generate RX, RD transactions
188 -- Get the transaction time of year, call to the get_transaction_toy
189 l_transaction_toy := NULL;
190 igs_uc_gen_001.get_transaction_toy(p_system_code => l_system_code,
191 p_ucas_cycle => l_ucas_cycle ,
192 p_transaction_toy => l_transaction_toy);
193
194 IF (l_transaction_toy = 'C' AND l_clearing = 'Y') THEN
195 -- Check whether there exists any clearing details for the applicant
196 OPEN cur_app_clearing(l_alt_appl_id);
197 FETCH cur_app_clearing INTO app_clearing_rec;
198 -- Check whether there exists any clearing round details for the applicant
199 OPEN cur_app_clr_rnd(l_alt_appl_id);
200 FETCH cur_app_clr_rnd INTO app_clr_rnd_rec;
201
202 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
203 IF cur_app_clearing%FOUND AND
204 lv_system_outcome_status IN ('OFFER', 'COND-OFFER') AND
205 (l_ucas_cycle >= 2006 OR cur_app_clr_rnd%FOUND) THEN
206
207 -- Create a RX transaction with the decision A
208 igs_uc_tran_processor_pkg.trans_build ( p_tran_type => 'RX',
209 p_app_no => l_alt_appl_id,
210 p_choice_no => lv_choice_no,
211 p_decision => l_decision_value,
212 p_course => l_ucas_program_code,
213 p_campus => l_campus,
214 p_entry_month => NULL,
215 p_entry_year => NULL,
216 p_entry_point => NULL,
217 p_soc => NULL,
218 p_free_format => NULL,
219 p_hold => 'Y',
220 p_return1 => lv_return1,
221 p_return2 => lv_return2,
222 p_inst_reference => NULL ,
223 p_cond_cat => p_condition_category,
224 p_cond_name => p_condition_name,
225 p_auto_generated => 'Y',
226 p_system_code => l_system_code,
227 p_ucas_cycle => l_ucas_cycle,
228 p_modular => NULL,
229 p_part_time => NULL,
230 p_uc_tran_id => p_uc_tran_id,
231 p_validate_error_cd => l_validate_error_cd
232 );
233
234 -- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
235 ELSIF (lv_system_outcome_status IN ('OFFER', 'REJECTED')) THEN
236 -- Create a RD transaction with decision A
237 igs_uc_tran_processor_pkg.trans_build ( p_tran_type => 'RD',
238 p_app_no => l_alt_appl_id,
239 p_choice_no => lv_choice_no,
240 p_decision => l_decision_value,
241 p_course => NULL,
242 p_campus => NULL,
243 p_entry_month => NULL,
244 p_entry_year => NULL,
245 p_entry_point => NULL,
246 p_soc => NULL,
247 p_free_format => NULL,
248 p_hold => 'N',
249 p_return1 => lv_return1,
250 p_return2 => lv_return2,
251 p_inst_reference => NULL,
252 p_cond_cat => p_condition_category,
253 p_cond_name => p_condition_name,
254 p_auto_generated => 'Y',
255 p_system_code => l_system_code,
256 p_ucas_cycle => l_ucas_cycle,
257 p_modular => NULL,
258 p_part_time => NULL,
259 p_uc_tran_id => p_uc_tran_id,
260 p_validate_error_cd => l_validate_error_cd
261 );
262 ELSE
263 fnd_message.set_name('IGS','IGS_UC_CREATE_TRANSACTION');
264 igs_ge_msg_stack.add;
265 RETURN;
266 END IF;
267
268 CLOSE cur_app_clearing;
269 CLOSE cur_app_clr_rnd;
270
271 ELSE -- Processing period is not in clearing
272
273 IF (lv_system_outcome_status IN ('OFFER','REJECTED')) THEN
274
275 -- Both decision and reply are null then call the transaction builder with transaction LD
276 IF l_decision IS NULL AND l_reply IS NULL THEN
277 l_transaction_type := 'LD';
278 -- smaddali added decision A to the list for bug#3096471
279 ELSIF l_decision IN ('U','A','C','W','R','B','F','S','E','I','M','X','G') AND l_reply IS NULL THEN
280 -- Call the transaction builder with transaction LA
281 l_transaction_type := 'LA';
282
283 ELSIF l_decision IN ('C','I') AND l_reply IN ('F','I') THEN
284 -- Call the transaction builder with RD transaction.
285 l_transaction_type := 'RD';
286 ELSE
287 fnd_message.set_name('IGS','IGS_UC_CREATE_TRANSACTION');
288 igs_ge_msg_stack.add;
289 RETURN;
290 END IF;
291
292 -- Smaddali added the check for outcome status = SUSPEND along with WITHDRAWN for bug# 2593568
293 -- Even when the outcome status is changed to SUSPEND ,the same transaction should be
294 -- generated as for WITHDRAWN
295 ELSIF (lv_system_outcome_status IN ('WITHDRAWN','SUSPEND') ) THEN
296 IF l_decision IS NULL AND l_reply IS NULL THEN
297 -- Call the transaction builder with LD
298 l_transaction_type := 'LD';
299 -- smaddali added decision A to the list for bug#3096471
300 ELSIF l_decision IN ('U','A','C','R','B','F','S','E','I','M','X','G') AND l_reply IS NULL THEN
301 -- call the transaction builder with LA
302 l_transaction_type := 'LA';
303
304 ELSIF l_decision IN ('C','I') AND l_reply IN ('F','I') THEN
305 -- Call the transaction builder with LA
306 l_transaction_type := 'LA';
307 -- smaddali added decision A to the list for bug#3096471
308 ELSIF l_decision IN ('U','A') AND l_reply = 'F' THEN
309 -- Call the transaction builder with RW
310 l_transaction_type := 'RW';
311
312 ELSE
313 fnd_message.set_name('IGS','IGS_UC_CREATE_TRANSACTION');
314 igs_ge_msg_stack.add;
315 RETURN;
316 END IF;
317
318 ELSIF (lv_system_outcome_status = 'COND-OFFER') THEN
319 IF l_decision IS NULL AND l_reply IS NULL THEN
320 -- Call the transaction builder with LD
321 l_transaction_type := 'LD';
322 -- smaddali Added decisions I and A to the list for bug#3096471
323 ELSIF l_decision IN ('U','A','W','R','B','F','S','E','I','M','X','G') AND l_reply IS NULL THEN
324 -- Call the transaction builder with LA
325 l_transaction_type := 'LA';
326
327 ELSE
328 fnd_message.set_name('IGS','IGS_UC_CREATE_TRANSACTION');
329 igs_ge_msg_stack.add;
330 RETURN;
331 END IF;
332
333 ELSE
334 fnd_message.set_name('IGS','IGS_UC_CREATE_TRANSACTION');
335 igs_ge_msg_stack.add;
336 RETURN;
337 END IF; -- End if for system status
338
339 -- Call the transaction builder for the above transactions
340 IF l_transaction_type IS NOT NULL AND l_decision_value IS NOT NULL THEN
341 igs_uc_tran_processor_pkg.trans_build ( p_tran_type => l_transaction_type,
342 p_app_no => l_alt_appl_id,
343 p_choice_no => lv_choice_no,
344 p_decision => l_decision_value,
345 p_course => NULL,
346 p_campus => NULL,
347 p_entry_month => NULL,
348 p_entry_year => NULL,
349 p_entry_point => NULL,
350 p_soc => NULL,
351 p_free_format => NULL,
352 p_hold => 'N',
353 p_return1 => lv_return1,
354 p_return2 => lv_return2,
355 p_inst_reference => NULL,
356 p_cond_cat => p_condition_category,
357 p_cond_name => p_condition_name,
358 p_auto_generated => 'Y',
359 p_system_code => l_system_code,
360 p_modular => NULL,
361 p_part_time => NULL,
362 p_ucas_cycle => l_ucas_cycle,
363 p_uc_tran_id => p_uc_tran_id,
364 p_validate_error_cd => l_validate_error_cd
365 );
366 END IF;
367
368 END IF; -- Clearing Period Check
369
370 END IF;
371 CLOSE c_app_choices;
372
373 IF (lv_return1 = 1) THEN
374 fnd_message.set_name('IGS',lv_return2);
375 igs_ge_msg_stack.add;
376 END IF;
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 -- Incase if there is any exception, the rollback is limited to only ucas transaction.
381 -- changes as per bug# 2459877.
382 ROLLBACK TO ucas_transactions;
383 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
384 fnd_message.set_token('NAME','IGS_UC_TRX_GEN_HOOK.CREATE_UCAS_TRANSACTIONS');
385 igs_ge_msg_stack.add;
386 END create_ucas_transactions;
387
388 END igs_uc_trx_gen_hook;
[Home] [Help]