[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_ADM_DES
Source
1 PACKAGE BODY igs_ad_imp_adm_des AS
2 /* $Header: IGSADB1B.pls 120.1 2006/02/01 04:21:43 pfotedar noship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL Body for package: IGS_AD_IMP_ADM_DES |
11 | |
12 | NOTES |
13 | This is the Package body for the Package IGS_AD_IMP_ADM_DES |
14 | which will update the outcome decision for an application |
15 | calculated by the user in interface table |
16 | HISTORY |
17 | Who When What |
18 | rrengara 2001/08/14 Creation of this code |
19 | cdcruz 18-feb-2002 Bug 2217104 Admit to future term Enhancement,updated tbh call for
20 | new columns being added to IGS_AD_PS_APPL_INST
21 |
22 | kamohan 09-SEP-2002 Bug 2536463 Modified the package to accomodate the detailed |
23 | error codes for the outcome status validation failure |
24 | nshee 29-Aug-2002 Bug 2395510 added 6 columns as part of deferments build |
25 | kamohan 16-SEP-2002 Bug # 2550009 // Modified the prc_adm_outcome_status procedure
26 | for the UCAS transaction builder call |
27 |ayedubat 04-DEC-03 Modified the call to the procedure,ucas_user_hook to add |
28 | two new IN parameters, p_condition_category and p_condition_name |
29 | and one OUT Patrameter,p_uc_tran_id for bug, 3009203 |
30 *=======================================================================*/
31
32
33 PROCEDURE update_int_table (
34 p_status IN igs_ad_admde_int.status%TYPE,
35 p_error_msg IN fnd_new_messages.message_text%TYPE, -- Replaced error_code with error_msg Bug 3297241
36 p_interface_mkdes_id IN igs_ad_admde_int.interface_mkdes_id%TYPE,
37 p_outcome_status IN igs_ad_admde_int.adm_outcome_status%TYPE
38 )
39 IS
40 ------------------------------------------------------------------
41 --Created by : rrengara, Oracle India (in)
42 --Date created: 14-AUG-2001
43 --
44 --Purpose: to update the interface table
45 --
46 --
47 --Known limitations/enhancements and/or remarks:
48 --
49 --Change History:
50 --Who When What
51 -------------------------------------------------------------------
52 BEGIN
53 UPDATE igs_ad_admde_int
54 SET status = p_status,
55 error_text = p_error_msg
56 WHERE interface_mkdes_id = p_interface_mkdes_id;
57 END update_int_table;
58
59 PROCEDURE validate_set_decision_details (
60 p_batch_id IN igs_ad_admde_int.batch_id%TYPE,
61 p_interface_mkdes_id IN igs_ad_admde_int.interface_mkdes_id%TYPE,
62 p_person_id IN igs_pe_person.person_id%TYPE,
63 p_acad_cal_type IN igs_ad_appl.acad_cal_type%TYPE,
64 p_acad_ci_sequence_number IN igs_ad_appl.acad_ci_sequence_number%TYPE,
65 p_adm_cal_type IN igs_ad_appl.adm_cal_type%TYPE,
66 p_adm_ci_sequence_number IN igs_ad_appl.adm_ci_sequence_number%TYPE,
67 p_admission_cat IN igs_ad_appl.admission_cat%TYPE,
68 p_s_admission_process_type IN igs_ad_appl.s_admission_process_type%TYPE,
69 p_s_adm_outcome_status IN igs_ad_ou_Stat.s_adm_outcome_status%TYPE,
70 p_decision_make_id OUT NOCOPY igs_ad_ps_appl_inst.decision_make_id%TYPE,
71 p_decision_date OUT NOCOPY igs_ad_ps_appl_inst.decision_date%TYPE,
72 p_decision_reason_id OUT NOCOPY igs_ad_ps_appl_inst.decision_reason_id%TYPE,
73 p_pending_reason_id OUT NOCOPY igs_ad_ps_appl_inst.pending_reason_id%TYPE,
74 p_offer_dt OUT NOCOPY igs_ad_ps_appl_inst.offer_dt%TYPE,
75 p_offer_response_dt OUT NOCOPY igs_ad_ps_appl_inst.offer_response_dt%TYPE,
76 p_error_msg OUT NOCOPY fnd_new_messages.message_name%TYPE, -- Replaced error_code with error_msg Bug 3297241
77 p_return_status OUT NOCOPY VARCHAR2,
78 p_prpsd_commencement_date IN igs_ad_admde_int_all.prpsd_commencement_date%TYPE DEFAULT NULL
79 )
80 IS
81 ------------------------------------------------------------------
82 --Created by : rrengara, Oracle India (in)
83 --Date created: 14-AUG-2001
84 --
85 --Purpose: to validate set decision details
86 --
87 --
88 --Known limitations/enhancements and/or remarks:
89 --
90 --Change History:
91 --Who When What
92 --rboddu 11/17/2003 Added p_prpsd_commencement_date and related validations. Bug 3181590
93 -------------------------------------------------------------------
94 CURSOR def_fields_cur
95 IS
96 SELECT decision_make_id, decision_date, decision_reason_id,
97 pending_reason_id, offer_dt, offer_response_dt
98 FROM igs_ad_batc_def_det
99 WHERE
100 batch_id = p_batch_id AND
101 acad_cal_type = p_acad_cal_type AND
102 acad_ci_sequence_number = p_acad_ci_sequence_number AND
103 adm_cal_type = p_adm_cal_type AND
104 adm_ci_sequence_number = p_adm_ci_sequence_number AND
105 admission_cat = p_admission_cat AND
106 s_admission_process_type = p_s_admission_process_type;
107
108
109 CURSOR appl_des_fields_cur
110 IS
111 SELECT person_id, decision_make_id, decision_date, decision_reason_id,
112 pending_reason_id, offer_dt, offer_response_dt, admission_appl_number
113 FROM igs_ad_admde_int
114 WHERE interface_mkdes_id = p_interface_mkdes_id;
115
116 l_c_def_fields_rec def_fields_cur%ROWTYPE;
117 l_c_appl_des_fields_rec appl_des_fields_cur%ROWTYPE;
118 l_decision_maker VARCHAR2(1);
119
120 CURSOR appl_dt_cur (
121 cp_person_id igs_pe_person.person_id%TYPE,
122 cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE
123 )
124 IS
125 SELECT appl_dt
126 FROM igs_ad_appl
127 WHERE person_id = cp_person_id
128 AND admission_appl_number = cp_admission_appl_number;
129
130 l_appl_dt igs_ad_appl.appl_dt%TYPE;
131
132 CURSOR c_code_classes (cp_class igs_ad_code_classes.class%TYPE,
133 cp_code_id igs_ad_code_classes.code_id%TYPE) IS
134 SELECT 'x'
135 FROM igs_ad_code_classes
136 WHERE code_id = cp_code_id
137 AND class = cp_class
138 AND closed_ind = 'N';
139
140 l_decision_reason_id_found VARCHAR2(1);
141 l_pending_reason_id_found VARCHAR2(1);
142 CURSOR c_decision_maker (cp_decision_maker_id igs_pe_person_base_v.person_id%TYPE) IS
143 SELECT 'x'
144 FROM igs_pe_typ_instances typeinst
145 WHERE typeinst.person_id = cp_decision_maker_id
146 AND typeinst.system_type IN ('STAFF','FACULTY')
147 AND (SYSDATE between typeinst.start_date AND NVL(typeinst.end_date,SYSDATE));
148
149 CURSOR c_dmi (cp_person_id NUMBER) IS
150 SELECT 'X'
151 FROM igs_pe_person_base_v base, igs_pe_hz_parties pd
152 WHERE base.person_id = cp_person_id
153 AND base.person_id = pd.party_id (+)
154 AND DECODE(base.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') = 'Y';
155
156 l_deceased igs_pe_person.person_id%TYPE := NULL;
157
158 BEGIN
159 /* Open the details */
160 OPEN def_fields_cur;
161 FETCH def_fields_cur INTO l_c_def_fields_rec;
162 CLOSE def_fields_cur;
163 /* Open the application specific decision reason fields */
164 OPEN appl_des_fields_cur;
165 FETCH appl_des_fields_cur INTO l_c_appl_des_fields_rec;
166 CLOSE appl_des_fields_cur;
167
168 OPEN appl_dt_cur (
169 l_c_appl_des_fields_rec.person_id,
170 l_c_appl_des_fields_rec.admission_appl_number
171 );
172 FETCH appl_dt_cur INTO l_appl_dt;
173 CLOSE appl_dt_cur;
174
175 /* If the application specific decision detail information is not available then assign the */
176 /* decision detail information. */
177 /*Validate and Set Decision Make ID information */
178 IF l_c_appl_des_fields_rec.decision_make_id IS NOT NULL
179 THEN
180 p_decision_make_id := l_c_appl_des_fields_rec.decision_make_id;
181 ELSE
182 p_decision_make_id := l_c_def_fields_rec.decision_make_id;
183 END IF;
184
185
186
187 --Validate and Set Decision Date field --
188 IF l_c_appl_des_fields_rec.decision_date IS NULL
189 THEN
190 p_decision_date := l_c_def_fields_rec.decision_date;
191
192
193 ELSE
194 p_decision_date := l_c_appl_des_fields_rec.decision_date;
195 END IF;
196
197 /* Validate And Set Decision Reason ID */
198 p_decision_reason_id := NVL (
199 l_c_appl_des_fields_rec.decision_reason_id,
200 l_c_def_fields_rec.decision_reason_id
201 );
202
203
204
205 /* Validate And Set Pending Reason ID */
206 p_pending_reason_id := NVL (
207 l_c_appl_des_fields_rec.pending_reason_id,
208 l_c_def_fields_rec.pending_reason_id
209 );
210
211
212
213
214 /* Validate And Set the offer date */
215 p_offer_dt :=
216 NVL (l_c_appl_des_fields_rec.offer_dt, l_c_def_fields_rec.offer_dt);
217
218
219
220 --Bug 3181590
221 IF p_s_adm_outcome_status NOT IN ('OFFER','COND-OFFER') AND
222 p_prpsd_commencement_date IS NOT NULL THEN
223 p_error_msg := 'IGS_AD_PRPSD_CMNCDT_NOIMPORT'; --Proposed Commencement Date cannot be imported without Offer Bug 3297241
224 p_return_status := 'FALSE';
225 RETURN;
226 END IF;
227 IF p_prpsd_commencement_date IS NOT NULL
228 AND TRUNC(p_prpsd_commencement_date) > TRUNC(SYSDATE)
229 OR TRUNC(p_prpsd_commencement_date) < TRUNC(l_appl_dt)
230 OR TRUNC(p_prpsd_commencement_date) < TRUNC(p_offer_dt) THEN
231 p_error_msg := 'IGS_AD_PRPSD_CMCMNT_DT_INVALID'; --Proposed Commencement Date can not be greater than current date, cannot be less than Application Date and cannot be less than Offer Date
232 p_return_status := 'FALSE';
233 RETURN;
234 END IF;
235 --End Bug 3181590
236
237 /* Set the offer response date. */
238 p_offer_response_dt := NVL (
239 l_c_appl_des_fields_rec.offer_response_dt,
240 l_c_def_fields_rec.offer_response_dt
241 );
242
243 EXCEPTION
244 WHEN OTHERS
245 THEN
246 p_error_msg := 'IGS_AD_DECISION_DTLS_INVALID'; -- Replaced error_code with error_msg Bug 3297241
247 p_return_status := 'FALSE';
248 END validate_set_decision_details;
249
250 PROCEDURE prc_adm_outcome_status (
251 p_person_id IN igs_pe_person.person_id%TYPE,
252 p_admission_appl_number IN igs_ad_appl.admission_appl_number%TYPE,
253 p_nominated_course_cd IN igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
254 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,
255 p_adm_outcome_status IN igs_ad_ou_stat.adm_outcome_status%TYPE,
256 p_s_adm_outcome_status IN igs_ad_ou_stat.s_adm_outcome_status%TYPE,
257 p_acad_cal_type IN igs_ad_appl.acad_cal_type%TYPE,
258 p_acad_ci_sequence_number IN igs_ad_appl.acad_ci_sequence_number%TYPE,
259 p_adm_cal_type IN igs_ad_appl.adm_cal_type%TYPE,
260 p_adm_ci_sequence_number IN igs_ad_appl.adm_ci_sequence_number%TYPE,
261 p_admission_cat IN igs_ad_appl.admission_cat%TYPE,
262 p_s_admission_process_type IN igs_ad_appl.s_admission_process_type%TYPE,
263 p_batch_id IN igs_ad_admde_int.batch_id%TYPE,
264 p_interface_run_id IN igs_ad_admde_int.interface_run_id%TYPE,
265 p_interface_mkdes_id IN igs_ad_admde_int.interface_mkdes_id%TYPE,
266 p_error_message OUT NOCOPY fnd_new_messages.message_text%TYPE,
267 p_return_status OUT NOCOPY VARCHAR2,
268 p_ucas_transaction IN VARCHAR2,
269 p_reconsideration IN VARCHAR2,
270 p_prpsd_commencement_date IN igs_ad_admde_int_all.prpsd_commencement_date%TYPE
271 )
272 IS
273 ------------------------------------------------------------------
274 -- Created by : rrengara, Oracle India (in)
275 -- Date created: 14-AUG-2001
276 --
277 -- Purpose: to import the outcome status of the application.
278 --
279 --
280 -- Known limitations/enhancements and/or remarks:
281 --
282 -- Change History:
283 -- Who When What
284 -- samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
285 -- kamohan 16-SEP-2002 Bug # 2550009 Changed the procedure defnition with
286 -- addition of p_ucas_transaction parameter
287 -- rghosh 18-Jun-2003 Bug#2860852(Request for reconsideration import enhancement) added the parameter p_reconsideration
288 -- rboddu 11/17/2003 Added p_prpsd_commencement_date. Bug:3181590
289 -------------------------------------------------------------------
290
291
292 l_decision_make_id igs_ad_ps_appl_inst.decision_make_id%TYPE;
293 l_decision_date igs_ad_ps_appl_inst.decision_date%TYPE;
294 l_decision_reason_id igs_ad_ps_appl_inst.decision_reason_id%TYPE;
295 l_pending_reason_id igs_ad_ps_appl_inst.pending_reason_id%TYPE;
296 l_offer_dt igs_ad_ps_appl_inst.offer_dt%TYPE;
297 l_offer_response_dt igs_ad_ps_appl_inst.offer_response_dt%TYPE;
298 l_adm_offer_resp_status igs_ad_ps_appl_inst.adm_offer_resp_status%TYPE;
299 l_adm_cndtl_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE;
300 l_sqlerrm VARCHAR2(2000);
301
302 CURSOR c_appl_cur IS
303 SELECT a.ROWID, a.*
304 FROM IGS_AD_APPL a
305 WHERE person_id = p_person_id
306 AND admission_appl_number = p_admission_appl_number;
307 l_c_appl_cur c_appl_cur%ROWTYPE;
308
309 CURSOR c_aplinst_cur IS
310 SELECT a.ROWID, a.*, b.req_for_reconsideration_ind
311 FROM igs_ad_ps_appl_inst a, IGS_AD_PS_APPL b
312 WHERE a.person_id = p_person_id
313 AND a.admission_appl_number = p_admission_appl_number
314 AND a.nominated_course_cd = p_nominated_course_cd
315 AND a.sequence_number = p_sequence_number
316 AND a.person_id = b.person_id
317 AND a.admission_appl_number = b.admission_appl_number
318 AND a.nominated_course_cd = b.nominated_course_cd;
319
320 l_c_aplinst_cur c_aplinst_cur%ROWTYPE;
321
322
323
324 CURSOR c_apcs (
325 cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
326 cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
327 SELECT
328 'Y'
329 FROM
330 IGS_AD_PRCS_CAT_STEP
331 WHERE
332 admission_cat = cp_s_admission_process_type
333 AND s_admission_process_type = cp_s_admission_process_type
334 AND s_admission_step_type = 'PRE-ENROL'
335 AND step_group_type <> 'TRACK' ;
336
337 -- cursor to fetch the values from the table igs_ad_ps_appl which are passed while calling
338 -- igs_ad_ps_appl_pkg.update_row (rghosh -- bug#2860860)
339
340 CURSOR c_ps_appl_cur IS
341 SELECT a.*
342 FROM igs_ad_ps_appl a
343 WHERE person_id = p_person_id
344 AND admission_appl_number = p_admission_appl_number
345 AND nominated_course_cd = p_nominated_course_cd;
346
347 -- cursor to get the offer response status
348 CURSOR c_adm_ofr_resp_stat_cur IS
349 SELECT a.adm_offer_resp_status
350 FROM igs_ad_ps_appl_inst a
351 WHERE person_id = p_person_id
352 AND admission_appl_number = p_admission_appl_number
353 AND nominated_course_cd = p_nominated_course_cd
354 AND sequence_number = p_sequence_number;
355
356 l_c_ps_appl_cur c_ps_appl_cur%ROWTYPE;
357 l_req_for_reconsideration_ind igs_ad_ps_appl.req_for_reconsideration_ind%TYPE;
358 l_prpsd_commencement_dt igs_ad_ps_appl_inst.prpsd_commencement_dt%TYPE;
359 l_actual_response_dt igs_ad_ps_appl_inst.actual_response_dt%TYPE;
360 l_cur_msg_count NUMBER;
361 l_max_msg_count NUMBER;
362 l_msg_index_out NUMBER;
363 l_app_name VARCHAR2(2000);
364
365 l_pre_enroll VARCHAR2(2);
366 v_message_name VARCHAR2(2000);
367 v_warn_level VARCHAR2(2000);
368
369 -- cursor to get whether APC step of RECONSIDER is attached to the given APC (rghosh bug#2860852 - Request for Reconsideration Import)
370 CURSOR c_check_reconsider (cp_admission_cat IGS_AD_PRCS_CAT_STEP.ADMISSION_CAT%TYPE,
371 cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
372 SELECT 'X'
373 FROM IGS_AD_PRCS_CAT_STEP
374 WHERE admission_cat = cp_admission_cat
375 AND s_admission_process_type = cp_s_admission_process_type
376 AND s_admission_step_type = 'RECONSIDER';
377
378 l_check_reconsider VARCHAR2(1);
379 l_uc_tran_id igs_uc_transactions.uc_tran_id%TYPE;
380 l_error_msg FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
381 l_err_msg FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
382 l_return_status VARCHAR2(1);
383 l_msg_data VARCHAR2(2000);
384 l_msg_count NUMBER;
385
386 BEGIN
387
388 -- Check whether any application is available in OSS to update outcome status
389 -- if the corresponding application is not there , then update the interface record with appropriate error code
390 OPEN c_appl_cur;
391 FETCH c_appl_cur INTO l_c_appl_cur;
392 CLOSE c_appl_cur;
393
394 OPEN c_aplinst_cur;
395 FETCH c_aplinst_cur
396 INTO l_c_aplinst_cur;
397 CLOSE c_aplinst_cur;
398
399 IF l_c_aplinst_cur.person_id IS NULL THEN
400 fnd_message.set_name('IGS','IGS_AD_DECISION_DTLS_INVALID'); -- Replaced error_code with error_msg Bug 3297241
401 l_err_msg := fnd_message.get;
402 update_int_table ('3', l_err_msg, p_interface_mkdes_id, p_adm_outcome_status);
403 p_error_message := l_err_msg;
404 p_return_status := 'FALSE';
405 RETURN;
406 END IF;
407
408 -- Resolve the Decision, Offer Values given by the user
409 validate_set_decision_details (
410 p_batch_id=> p_batch_id,
411 p_interface_mkdes_id=> p_interface_mkdes_id,
412 p_person_id => p_person_id,
413 p_acad_cal_type => NVL(p_acad_cal_type,l_c_appl_cur.acad_cal_type),
414 p_acad_ci_sequence_number => NVL(p_acad_ci_sequence_number,l_c_appl_cur.acad_ci_sequence_number),
415 p_adm_cal_type => NVL(p_adm_cal_type,l_c_appl_cur.adm_cal_type),
416 p_adm_ci_sequence_number => NVL(p_adm_ci_sequence_number,l_c_appl_cur.adm_ci_sequence_number),
417 p_admission_cat => NVL(p_admission_cat,l_c_appl_cur.admission_cat),
418 p_s_admission_process_type => NVL(p_s_admission_process_type,l_c_appl_cur.s_admission_process_type),
419 p_s_adm_outcome_status=> p_s_adm_outcome_status,
420 p_decision_make_id=> l_decision_make_id,
421 p_decision_date=> l_decision_date,
422 p_decision_reason_id=> l_decision_reason_id,
423 p_pending_reason_id=> l_pending_reason_id,
424 p_offer_dt=> l_offer_dt,
425 p_offer_response_dt=> l_offer_response_dt,
426 p_error_msg=> l_error_msg, -- Replaced error_code with error_msg Bug 3297241
427 p_return_status=> l_return_status,
428 p_prpsd_commencement_date => p_prpsd_commencement_date
429 );
430 IF UPPER (l_return_status) = 'FALSE'
431 THEN
432 fnd_message.set_name('IGS',l_error_msg); -- Replaced error_code with error_msg Bug 3297241
433 l_error_msg := fnd_message.get;
434 update_int_table ('3', l_error_msg, p_interface_mkdes_id, p_adm_outcome_status);
435 p_error_message := l_error_msg;
436 p_return_status := l_return_status;
437 RETURN;
438 END IF;
439
440
441
442 IF p_s_adm_outcome_status IN ('OFFER', 'COND-OFFER')
443 THEN
444 l_pending_reason_id := NULL;
445 ELSIF p_s_adm_outcome_status IN ('PENDING')
446 THEN
447 l_offer_dt := NULL;
448 l_offer_response_dt := NULL;
449 l_decision_reason_id := NULL;
450 l_decision_make_id := NULL;
451 l_decision_date := NULL;
452 ELSE
453 l_offer_dt := NULL;
454 l_offer_response_dt := NULL;
455 l_pending_reason_id := NULL;
456 END IF;
457
458 l_prpsd_commencement_dt := l_c_aplinst_cur.prpsd_commencement_dt;
459 l_actual_response_dt := l_c_aplinst_cur.actual_response_dt;
460
461
462 l_cur_msg_count := igs_ge_msg_stack.count_msg;
463
464 IGS_AdmApplication_PUB.Record_Outcome_AdmApplication(
465 p_api_version => 1.0,
466 p_init_msg_list => FND_API.G_TRUE,
467 p_commit => FND_API.G_FALSE,
468 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
469
470
471 p_person_id => l_c_aplinst_cur.person_id,
472 p_admission_appl_number => l_c_aplinst_cur.admission_appl_number,
473 p_nominated_program_cd => l_c_aplinst_cur.nominated_course_cd,
474 p_sequence_number => l_c_aplinst_cur.sequence_number,
475 p_adm_outcome_status => p_adm_outcome_status,
476 p_decision_maker_id => l_decision_make_id,
477 p_decision_date => l_decision_date,
478 p_decision_reason_id => l_decision_reason_id,
479 p_pending_reason_id => l_pending_reason_id,
480 p_offer_dt => l_offer_dt,
481 p_offer_response_dt => l_offer_response_dt,
482 p_reconsider_flag => p_reconsideration,
483 p_prpsd_commencement_date => l_prpsd_commencement_dt,
484 p_ucas_transaction => p_ucas_transaction,
485
486 x_return_status => l_return_status,
487 x_msg_count => l_msg_count,
488 x_msg_data => l_msg_data
489 );
490
491
492
493 IF l_return_status IN ('E','U') THEN
494 update_int_table ('3', l_msg_data, p_interface_mkdes_id, p_adm_outcome_status);
495 p_error_message := l_sqlerrm;
496 p_return_status := 'FALSE';
497 ELSE
498 UPDATE igs_ad_admde_int_all
499 SET status = '1'
500 WHERE interface_mkdes_id = p_interface_mkdes_id;
501 p_return_status := 'TRUE';
502 END IF;
503
504
505 EXCEPTION WHEN OTHERS THEN
506 l_sqlerrm := SQLERRM;
507
508 UPDATE igs_ad_admde_int SET status = '3', error_text = l_sqlerrm WHERE interface_mkdes_id = p_interface_mkdes_id;
509 p_error_message := l_sqlerrm;
510 p_return_status := 'FALSE';
511 RETURN;
512
513 END prc_adm_outcome_status;
514
515 PROCEDURE import_adm_decision (
516 p_batch_id IN igs_ad_batc_def_det_all.batch_id%TYPE,
517 p_ucas_transaction IN VARCHAR2,
518 p_message_name OUT NOCOPY VARCHAR2,
519 p_msg_token_rec_prc_cnt OUT NOCOPY NUMBER
520 ) IS
521
522 l_msg_token_rec_prc_cnt NUMBER := 0;
523 l_processed_records NUMBER := 0;
524 l_return_status VARCHAR2(30);
525
526 CURSOR c_batc_def_det IS
527 SELECT *
528 FROM igs_ad_batc_def_det_all abdd
529 WHERE batch_id = p_batch_id AND
530 ( (abdd.ACAD_CAL_TYPE IS NULL AND
531 abdd.ACAD_CI_SEQUENCE_NUMBER IS NULL AND
532 abdd.ADM_CAL_TYPE IS NULL AND
533 abdd.ADM_CI_SEQUENCE_NUMBER IS NULL AND
534 abdd.ADMISSION_CAT IS NULL AND
535 abdd.S_ADMISSION_PROCESS_TYPE IS NULL)
536 OR
537 (abdd.ACAD_CAL_TYPE IS NOT NULL AND
538 abdd.ACAD_CI_SEQUENCE_NUMBER IS NOT NULL AND
539 abdd.ADM_CAL_TYPE IS NOT NULL AND
540 abdd.ADM_CI_SEQUENCE_NUMBER IS NOT NULL AND
541 abdd.ADMISSION_CAT IS NOT NULL AND
542 abdd.S_ADMISSION_PROCESS_TYPE IS NOT NULL) ) ;
543
544 CURSOR c_admde_int IS
545 SELECT mdi.*
546 FROM igs_ad_admde_int_all mdi, igs_ad_batc_def_det_all abdd
547 WHERE mdi.batch_id = p_batch_id
548 AND mdi.batch_id = abdd.batch_id
549 AND EXISTS ( SELECT 1
550 FROM igs_ad_ps_appl_inst aplinst, igs_ad_appl appl
551 WHERE aplinst.person_id = appl.person_id
552 AND aplinst.admission_appl_number = appl.admission_appl_number
553 AND aplinst.person_id = mdi.person_id
554 AND aplinst.admission_appl_number = mdi.admission_appl_number
555 AND aplinst.nominated_course_cd = mdi.nominated_course_cd
556 AND aplinst.sequence_number = mdi.sequence_number
557 AND ( (abdd.ACAD_CAL_TYPE IS NULL
558 AND abdd.ACAD_CI_SEQUENCE_NUMBER IS NULL
559 AND abdd.ADM_CAL_TYPE IS NULL
560 AND abdd.ADM_CI_SEQUENCE_NUMBER IS NULL
561 AND abdd.ADMISSION_CAT IS NULL
562 AND abdd.S_ADMISSION_PROCESS_TYPE IS NULL)
563 OR appl.ACAD_CAL_TYPE = abdd.ACAD_CAL_TYPE
564 AND appl.ACAD_CI_SEQUENCE_NUMBER = abdd.ACAD_CI_SEQUENCE_NUMBER
565 AND NVL(aplinst.ADM_CAL_TYPE, appl.ADM_CAL_TYPE) = abdd.ADM_CAL_TYPE
566 AND NVL(aplinst.ADM_CI_SEQUENCE_NUMBER, appl.ADM_CI_SEQUENCE_NUMBER ) = abdd.ADM_CI_SEQUENCE_NUMBER
567 AND appl.ADMISSION_CAT = abdd.ADMISSION_CAT
568 AND appl.S_ADMISSION_PROCESS_TYPE = abdd.S_ADMISSION_PROCESS_TYPE /*2*/) /*1*/)
569 AND status = '2';
570
571 CURSOR c_old_adm_outcome_status (
572 cp_person_id IN igs_pe_person.person_id%TYPE,
573 cp_admission_appl_number IN igs_ad_appl.admission_appl_number%TYPE,
574 cp_nominated_course_cd IN igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
575 cp_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
576 ) IS
577 SELECT adm_outcome_status
578 FROM igs_ad_ps_appl_inst
579 WHERE person_id = cp_person_id
580 AND admission_appl_number = cp_admission_appl_number
581 AND nominated_course_cd = cp_nominated_course_cd
582 AND sequence_number = cp_sequence_number;
583
584 l_old_adm_outcome_status igs_ad_ps_appl_inst_all.adm_outcome_status%TYPE;
585 l_error_msg fnd_new_messages.message_text%TYPE;
586
587 BEGIN
588 DECLARE
589 l_gather_status VARCHAR2(5);
590 l_industry VARCHAR2(5);
591 l_schema VARCHAR2(30);
592 l_gather_return BOOLEAN;
593 BEGIN
594 l_gather_return := fnd_installation.get_app_info('IGS', l_gather_status, l_industry, l_schema);
595 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_BATC_DEF_DET', cascade => TRUE);
596 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_ADMDE_INT', cascade => TRUE);
597 EXCEPTION
598 WHEN OTHERS THEN
599 NULL;
600 END;
601
602 FOR c_batc_def_det_rec IN c_batc_def_det
603 LOOP
604 FOR c_admde_int_rec IN c_admde_int
605 LOOP
606 l_msg_token_rec_prc_cnt := l_msg_token_rec_prc_cnt + 1;
607 l_processed_records := l_processed_records + 1;
608 -- Ensured call to igs_ad_imp_adm_des.prc_adm_outcome_status
609 -- do not result in code flow to EXCEPTION section in calling procedure
610 -- variable l_return_status determines success or failure of the process
611
612 OPEN c_old_adm_outcome_status (
613 c_admde_int_rec.person_id,
614 c_admde_int_rec.admission_appl_number,
615 c_admde_int_rec.nominated_course_cd,
616 c_admde_int_rec.sequence_number);
617 FETCH c_old_adm_outcome_status INTO l_old_adm_outcome_status;
618 CLOSE c_old_adm_outcome_status;
619
620
621
622 igs_ad_imp_adm_des.prc_adm_outcome_status(
623 p_person_id => c_admde_int_rec.person_id,
624 p_admission_appl_number => c_admde_int_rec.admission_appl_number,
625 p_nominated_course_cd => c_admde_int_rec.nominated_course_cd,
626 p_sequence_number => c_admde_int_rec.sequence_number,
627 p_adm_outcome_status => c_admde_int_rec.adm_outcome_status,
628 p_s_adm_outcome_status => igs_ad_gen_008.admp_get_saos(c_admde_int_rec.adm_outcome_status),
629 p_acad_cal_type => c_batc_def_det_rec.acad_cal_type,
630 p_acad_ci_sequence_number => c_batc_def_det_rec.acad_ci_sequence_number,
631 p_adm_cal_type => c_batc_def_det_rec.adm_cal_type,
632 p_adm_ci_sequence_number => c_batc_def_det_rec.adm_ci_sequence_number,
633 p_admission_cat => c_batc_def_det_rec.admission_cat,
634 p_s_admission_process_type => c_batc_def_det_rec.s_admission_process_type,
635 p_batch_id => c_batc_def_det_rec.batch_id,
636 p_interface_run_id => c_admde_int_rec.interface_run_id,
637 p_interface_mkdes_id => c_admde_int_rec.interface_mkdes_id,
638 p_error_message => l_error_msg, -- Replaced error_code with error_msg Bug 3297241
639 p_return_status => l_return_status,
640 p_ucas_transaction => p_ucas_transaction,
641 p_reconsideration => c_admde_int_rec.reconsider_flag,
642 p_prpsd_commencement_date => c_admde_int_rec.prpsd_commencement_date
643 );
644
645 /* -- Application Decision got imported successfully
646 -- Raise the business event
647 -- Changes to the logic of raising the business event is done as part of Financial Aid Integration buid - 3202866
648 IF l_old_adm_outcome_status <> c_admde_int_rec.adm_outcome_status
649 AND NVL(l_return_status,'TRUE') <> 'FALSE' THEN
650
651 igs_ad_wf_001.wf_raise_event(
652 p_person_id => c_admde_int_rec.person_id,
653 p_raised_for => 'IOD',
654 p_admission_appl_number => c_admde_int_rec.admission_appl_number,
655 p_nominated_course_cd => c_admde_int_rec.nominated_course_cd,
656 p_sequence_number => c_admde_int_rec.sequence_number,
657 p_old_outcome_status => l_old_adm_outcome_status,
658 p_new_outcome_status => c_admde_int_rec.adm_outcome_status
659 );
660 END IF;
661 */
662 IF l_processed_records = 100 THEN
663 COMMIT;
664 l_processed_records := 0;
665 END IF;
666 END LOOP;
667
668 IF l_msg_token_rec_prc_cnt > 0 THEN
669 p_message_name := 'IGS_AD_PROCESS_N_RECORDS';
670 p_msg_token_rec_prc_cnt := l_msg_token_rec_prc_cnt;
671
672 DELETE FROM igs_ad_admde_int_all
673 WHERE batch_id = p_batch_id
674 AND status = '1';
675 COMMIT;
676
677 END IF;
678 END LOOP;
679 END import_adm_decision;
680
681
682 PROCEDURE discard_adm_decision (
683 p_batch_id IN igs_ad_batc_def_det_all.batch_id%TYPE,
684 p_message_name OUT NOCOPY VARCHAR2,
685 p_msg_token_rec_prc_cnt OUT NOCOPY NUMBER
686 ) IS
687
688 l_msg_token_rec_prc_cnt NUMBER := 0;
689 l_processed_records NUMBER := 0;
690
691 CURSOR c_admde_int IS
692 SELECT *
693 FROM igs_ad_admde_int_all
694 WHERE batch_id = p_batch_id
695 AND status = '2';
696
697 BEGIN
698 FOR c_admde_int_rec IN c_admde_int
699 LOOP
700 l_msg_token_rec_prc_cnt := l_msg_token_rec_prc_cnt + 1;
701 l_processed_records := l_processed_records + 1;
702
703 DELETE FROM igs_ad_admde_int_all
704 WHERE interface_mkdes_id = c_admde_int_rec.interface_mkdes_id;
705
706 IF l_processed_records = 100 THEN
707 COMMIT;
708 l_processed_records := 0;
709 END IF;
710 END LOOP;
711
712 IF l_msg_token_rec_prc_cnt > 0 THEN
713 p_message_name := 'IGS_AD_DELETE_N_RECORDS';
714 p_msg_token_rec_prc_cnt := l_msg_token_rec_prc_cnt;
715 COMMIT;
716 END IF;
717
718 END discard_adm_decision;
719
720 END igs_ad_imp_adm_des;