[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_OFF_RESP_DATA
Source
1 PACKAGE BODY igs_ad_imp_off_resp_data AS
2 /* $Header: IGSADC2B.pls 120.5 2006/01/16 20:24:33 rghosh ship $ */
3 ---------------------------------------------------------------------------------------------------------------------------------------
4 -- Created By : rboddu
5 -- Date Created On : 17-SEP-2002
6 -- Purpose : Bug: 2395510. This package is used to import the Offer Response data from interface tables to the Admission System tables,
7 -- by performing All the validations that are currently done in Offer Response form (IGSAD093). Bulk Collect is implemented to import
8 -- the interface records. Currently this is job is called from UCAS process. This job can also be invoked from SRS.
9 -- Know limitations, enhancements or remarks
10 -- Change History
11 -- Who When What
12 -- rghosh 02-Jan-03 removed the planned status as per bug#2722785
13 -- rbezawad 1-Nov-04 Modified imp_off_resp procedure to display the security error message in the log file w.r.t. Bug 3919112.
14 ---------------------------------------------------------------------------------------------------------------------------------------
15
16 -- Cursor to get the Application Instance record from OSS System table, corresponding to given Interface Offer Response record
17 -- Cursor is put at the package level as it's being accessed by different procedures of this package.
18 CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
19 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
20 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
21 cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
22 ) IS
23 SELECT aplinst.rowid, aplinst.*
24 FROM igs_ad_ps_appl_inst_all aplinst
25 WHERE person_id = cp_person_id AND
26 admission_appl_number = cp_admission_appl_number AND
27 nominated_course_cd = cp_nominated_course_cd AND
28 sequence_number = cp_sequence_number;
29
30 PROCEDURE logdetail(l_id IN igs_ad_offresp_int.offresp_int_id%TYPE,
31 l_err_code IN igs_ad_offresp_err.error_code%TYPE,
32 l_err_msg IN igs_ad_offresp_err.error_text%TYPE,
33 l_debug_msg IN VARCHAR2,
34 l_first_flag IN VARCHAR2) IS
35 ---------------------------------------------------------------------------------------------------------------------------------------
36 -- Created By : rboddu
37 -- Date Created On : 09-SEP-2002
38 -- Purpose : Bug 2395510. Procedure to display the LOG data in required format.
39 -- Know limitations, enhancements or remarks
40 -- Change History
41 -- Who When What
42 ---------------------------------------------------------------------------------------------------------------------------------------
43
44 l_full_string VARCHAR2(400);
45 l_meaning VARCHAR2(400);
46 l_bat_string VARCHAR2(100);
47 l_text VARCHAR2(400);
48 CURSOR c_lkup_meaning(l_code igs_ad_offresp_err.error_code%TYPE) IS
49 SELECT meaning
50 FROM igs_lookups_view
51 WHERE lookup_type = 'IMPORT_ERROR_CODE' AND
52 lookup_code = l_code;
53
54 CURSOR batch_details(l_batch_id igs_ad_offresp_batch.batch_id%TYPE) IS
55 SELECT batch_id, batch_desc
56 FROM igs_ad_offresp_batch
57 WHERE batch_id = l_batch_id;
58 l_batch_rec batch_details%ROWTYPE;
59
60 BEGIN
61 IF l_err_code IS NOT NULL THEN
62 OPEN c_lkup_meaning(l_err_code);
63 FETCH c_lkup_meaning INTO l_meaning;
64 CLOSE c_lkup_meaning;
65 ELSE
66 l_meaning := FND_MESSAGE.GET_STRING('IGS',l_err_msg);
67 END IF;
68
69 IF l_first_flag = 'Y' THEN
70 OPEN batch_details(l_id);
71 FETCH batch_details INTO l_batch_rec;
72 CLOSE batch_Details;
73
74 l_full_string :=' ';
75 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_string);
76
77 FND_MESSAGE.SET_NAME('IGS','IGS_AD_BATCH_ID');
78 l_full_string := RPAD(FND_MESSAGE.GET,10,' ')||RPAD(IGS_GE_NUMBER.TO_CANN(l_batch_rec.batch_id),15,' ')||l_batch_rec.batch_desc;
79 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_string);
80
81 l_full_string :='----------------------------------------------------------------------------------------------------------------------------------';
82 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_string);
83
84 FND_MESSAGE.SET_NAME('IGS','IGS_AD_IMP_OFR_LOG_HDR');
85 l_full_string := FND_MESSAGE.GET;
86
87 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_string);
88
89 l_full_string :='----------------------------------------------------------------------------------------------------------------------------------';
90 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_string);
91
92 ELSIF l_err_code IS NOT NULL OR l_err_msg IS NOT NULL THEN
93 l_text := RPAD(IGS_GE_NUMBER.TO_CANN(l_id),15,' ')||RPAD(NVL(l_err_code,' '),10,' ')||l_meaning;
94 FND_FILE.PUT_LINE(FND_FILE.LOG, l_text);
95
96 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DEBUG_INFO');
97 l_full_string := FND_MESSAGE.GET;
98 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_string||' '||l_debug_msg);
99 l_full_string :=' ';
100 FND_FILE.PUT_LINE(FND_FILE.LOG, l_full_string);
101
102 END IF;
103
104 IF l_id IS NOT NULL AND l_err_code IS NULL AND l_err_msg IS NULL AND l_first_flag IS NULL THEN
105
106 FND_MESSAGE.SET_NAME('IGS','IGS_AD_SUCC_IMP_OFR_RESP_REC');
107 l_full_string := FND_MESSAGE.GET;
108
109 FND_FILE.PUT_LINE(FND_FILE.LOG, RPAD(IGS_GE_NUMBER.TO_CANN(l_id),15,' ')||' '||l_full_string);
110 END IF;
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 IF c_lkup_meaning%ISOPEN THEN
115 CLOSE c_lkup_meaning;
116 END IF;
117 IF batch_details%ISOPEN THEN
118 CLOSE batch_details;
119 END IF;
120 FND_FILE.PUT_LINE(FND_FILE.LOG,'logdetail: '||FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' '||SQLERRM);
121 END logdetail;
122
123 --Procedure to Update igs_ad_offresp_int with the Error status and insert the corresponding Error record into igs_ad_offresp_err.
124 PROCEDURE insert_int_error( p_offresp_int_id IN igs_ad_offresp_int.offresp_int_id%TYPE,
125 p_error_code IN igs_ad_offresp_err.error_code%TYPE,
126 p_message_name IN VARCHAR2
127 ) IS
128 ---------------------------------------------------------------------------------------------------------------------------------------
129 -- Created By : rboddu
130 -- Date Created On : 09-SEP-2002
131 -- Purpose : Bug 2395510. Procedure to insert Error Code / Error Message text into igs_ad_offresp_err, for the failed validations.
132 -- Know limitations, enhancements or remarks
133 -- Change History
134 -- Who When What
135 ---------------------------------------------------------------------------------------------------------------------------------------
136
137 l_message_text VARCHAR2(2000);
138 BEGIN
139 l_message_text := NULL;
140 IF p_message_name IS NOT NULL THEN
141 IF length(p_message_name) > 30 THEN
142 l_message_text := p_message_name;
143 ELSE
144 FND_MESSAGE.SET_NAME('IGS',p_message_name);
145 l_message_text := fnd_message.get();
146 END IF;
147 END IF;
148
149 INSERT INTO igs_ad_offresp_err(
150 offresp_err_id,
151 offresp_int_id,
152 error_code,
153 error_text,
154 created_by,
155 creation_date,
156 last_updated_by,
157 last_update_date,
158 last_update_login,
159 request_id,
160 program_application_id,
161 program_update_date,
162 program_id)
163 VALUES(
164 igs_ad_offresp_err_s.nextval,
165 p_offresp_int_id,
166 p_error_code,
167 l_message_text,
168 fnd_global.user_id,
169 SYSDATE,
170 fnd_global.user_id,
171 SYSDATE,
172 fnd_global.user_id,
173 fnd_global.conc_request_id,
174 fnd_global.prog_appl_id,
175 SYSDATE,
176 fnd_global.conc_program_id);
177 EXCEPTION
178 WHEN OTHERS THEN
179 FND_FILE.PUT_LINE(FND_FILE.LOG,'insert_int_error: '||FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' '||SQLERRM);
180 END insert_int_error;
181
182
183 PROCEDURE validate_off_resp_dtls(
184 p_offresp_int_id IN igs_ad_offresp_int.OFFRESP_INT_ID%TYPE,
185 p_batch_id IN igs_ad_offresp_int.BATCH_ID%TYPE,
186 p_person_id IN igs_ad_offresp_int.person_id%TYPE,
187 p_admission_appl_number IN igs_ad_offresp_int.admission_appl_number%TYPE,
188 p_nominated_course_cd IN igs_ad_offresp_int.nominated_course_cd%TYPE ,
189 p_sequence_number IN igs_ad_offresp_int.sequence_number%TYPE,
190 p_adm_offer_resp_status IN igs_ad_offresp_int.adm_offer_resp_status%TYPE ,
191 p_decline_ofr_reason IN igs_ad_offresp_int.decline_ofr_reason%TYPE, --arvsrini igsm
192 p_actual_offer_response_dt IN igs_ad_offresp_int.actual_offer_response_dt%TYPE,
193 p_attent_other_inst_cd IN igs_ad_offresp_int.attent_other_inst_cd%TYPE,
194 p_applicant_acptnce_cndtn IN igs_ad_offresp_int.applicant_acptnce_cndtn%TYPE,
195 p_def_acad_cal_type IN igs_ad_offresp_int.def_acad_cal_type%TYPE ,
196 p_def_acad_ci_sequence_number IN igs_ad_offresp_int.def_acad_ci_sequence_number%TYPE,
197 p_def_adm_cal_type IN igs_ad_offresp_int.def_adm_cal_type%TYPE ,
198 p_def_adm_ci_sequence_number IN igs_ad_offresp_int.def_adm_ci_sequence_number%TYPE ,
199 p_status IN igs_ad_offresp_int.status%TYPE ,
200 p_prpsd_commencement_date IN igs_ad_offresp_int.prpsd_commencement_date%TYPE,
201 p_adm_offer_defr_status OUT NOCOPY igs_ad_ps_appl_inst_all.adm_offer_dfrmnt_status%TYPE,
202 p_calc_actual_ofr_resp_dt OUT NOCOPY igs_ad_offresp_int.actual_offer_response_dt%TYPE,
203 appl_rec IN igs_ad_appl_all%ROWTYPE,
204 acaiv_rec IN cur_ad_ps_appl_inst%ROWTYPE,
205 p_yes_no IN VARCHAR2,
206 p_validation_success OUT NOCOPY VARCHAR2) IS
207 ---------------------------------------------------------------------------------------------------------------------------------------
208 -- Created By : rboddu
209 -- Date Created On : 09-SEP-2002
210 -- Purpose : Bug 2395510. This procedure performs all the validations that are being done in Offer Response form (IGSAD093). Apart
211 -- from these validations some additional validations are performed to check for the validity of different Offer Response details.
212 -- Validations are stopped whenever basic validation a fails, like the application is not in Open processing state or Outcome Status
213 -- is not valid etc.
214 -- Know limitations, enhancements or remarks
215 -- Change History
216 -- Who When What
217 -- rboddu 11/17/2003 Added p_prpsd_commencement_date and related validations. Bug:3181590
218 ---------------------------------------------------------------------------------------------------------------------------------------
219
220 CURSOR c_apcs (cp_admission_cat igs_ad_prcs_cat_step.admission_cat%TYPE,
221 cp_s_admission_process_type igs_ad_prcs_cat_step.s_admission_process_type%TYPE) IS
222 SELECT s_admission_step_type,
223 step_type_restriction_num
224 FROM igs_ad_prcs_cat_step
225 WHERE admission_cat = cp_admission_cat AND
226 s_admission_process_type = cp_s_admission_process_type AND
227 step_group_type <> 'TRACK' ;
228
229 CURSOR valid_inst_cur(l_cd igs_or_institution.institution_cd%TYPE) IS
230 SELECT institution_cd
231 FROM igs_or_institution
232 WHERE institution_cd = l_cd;
233
234 CURSOR c_appl_inst_ctxt(
235 cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
236 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
237 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
238 cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
239 SELECT ci1.start_dt acad_ci_start_dt,
240 ci1.end_dt acad_ci_end_dt,
241 ci2.start_dt adm_ci_start_dt,
242 ci2.end_dt adm_ci_end_dt
243 FROM igs_ad_appl aav, /* Replaced igs_ad_ps_apl_inst_cntx_v with underlying tables. Bug 3150054 */
244 igs_ad_ps_appl_inst acai,
245 igs_ca_inst ci1,
246 igs_ca_inst ci2
247 WHERE acai.person_id = cp_person_id AND
248 acai.admission_appl_number = cp_admission_appl_number AND
249 acai.nominated_course_cd = cp_nominated_course_cd AND
250 acai.sequence_number = cp_sequence_number AND
251 aav.person_id = acai.person_id AND
252 aav.admission_appl_number = acai.admission_appl_number AND
253 ci1.cal_type = aav.acad_cal_type AND
254 ci1.sequence_number = aav.acad_ci_sequence_number AND
255 ci2.cal_type = nvl(acai.adm_cal_type,aav.adm_cal_type) AND
256 ci2.sequence_number = nvl(acai.adm_ci_sequence_number,aav.adm_ci_sequence_number);
257
258
259 --Cursor to select the valid Deferred Academic Calendars
260 CURSOR c_valid_acad_cals(cp_acad_ci_start_dt IN igs_ca_inst.start_dt%TYPE,
261 cp_acad_ci_end_dt IN igs_ca_inst.end_dt%TYPE ) IS
262 SELECT UNIQUE
263 acad_ciav.alternate_code def_acad_alternate_code
264 ,acad_ciav.cal_type def_acad_cal_type
265 ,acad_ciav.sequence_number def_acad_ci_seq_number
266 ,acad_ciav.start_dt start_dt
267 ,acad_ciav.end_dt end_dt
268 ,acad_ciav.description abbreviation
269 ,acad_ciav.cal_status cal_status
270 ,acad_ciav.s_cal_status s_cal_status
271 FROM
272 igs_ca_inst_alt_v acad_ciav,
273 igs_ca_inst_alt_v adm_ciav,
274 igs_ca_inst_rel cir,
275 igs_ad_prd_ad_prc_ca apapc,
276 igs_ca_type ct
277 WHERE
278 (acad_ciav.s_cal_cat = 'ACADEMIC' AND
279 acad_ciav.s_cal_status = 'ACTIVE' AND
280 adm_ciav.s_cal_cat = 'ADMISSION' AND
281 adm_ciav.s_cal_status = 'ACTIVE' AND
282 cir.sup_cal_type = acad_ciav.cal_type AND
283 cir.sup_ci_sequence_number = acad_ciav.sequence_number AND
284 cir.sub_cal_type = adm_ciav.cal_type AND
285 cir.sub_ci_sequence_number = adm_ciav.sequence_number AND
286 apapc.adm_cal_type = cir.sub_cal_type AND
287 apapc.adm_ci_sequence_number = cir.sub_ci_sequence_number AND
288 ct.cal_type = acad_ciav.cal_type AND
289 acad_ciav.start_dt >= cp_acad_ci_start_dt AND
290 acad_ciav.end_dt >= cp_acad_ci_end_dt AND
291 apapc.closed_ind = 'N') --added the closed indicator for bug# 2380108 (rghosh)
292 ORDER BY
293 acad_ciav.cal_type asc
294 ,acad_ciav.start_dt desc;
295
296 --Cursor to select the valid Deferred Admission Calendars for the given Academic Calendar
297 CURSOR c_valid_adm_cals( cp_admission_cat igs_ad_appl_all.admission_cat%TYPE,
298 cp_s_adm_process_type igs_ad_appl_all.s_admission_process_type%TYPE,
299 cp_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE,
300 cp_def_acad_ci_seq_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE,
301 cp_adm_ci_end_dt igs_ca_inst.end_dt%TYPE
302 ) IS
303 SELECT
304 ciav1.alternate_code def_adm_alternate_code,
305 ciav1.cal_type def_adm_cal_type ,
306 ciav1.sequence_number def_adm_ci_seq_number ,
307 ciav1.start_dt start_dt ,
308 ciav1.end_dt end_dt ,
309 ciav1.description abbreviation ,
310 ciav1.cal_status cal_status
311 FROM
312 igs_ca_inst_alt_v ciav1
313 WHERE
314 (ciav1.s_cal_cat = 'ADMISSION' AND ciav1.s_cal_status = 'ACTIVE'
315 AND ciav1.start_dt > cp_adm_ci_end_dt
316 AND (ciav1.cal_type, ciav1.sequence_number) IN
317 (SELECT apapc.adm_cal_type,
318 apapc.adm_ci_sequence_number
319 FROM igs_ad_prd_ad_prc_ca apapc,
320 igs_ca_inst_rel cir
321 WHERE apapc.admission_cat = cp_admission_cat AND
322 apapc.s_admission_process_type =cp_s_adm_process_type AND
323 cir.sub_cal_type = apapc.adm_cal_type AND
324 cir.sub_ci_sequence_number = apapc.adm_ci_sequence_number AND
325 cir.sup_cal_type = cp_def_acad_cal_type AND
326 cir.sup_ci_sequence_number = cp_def_acad_ci_seq_num AND
327 apapc.closed_ind= 'N') ) --added the closed indicator for bug# 2380108 (rghosh)
328 ORDER BY ciav1.cal_type ASC ,
329 ciav1.start_dt ASC;
330
331 CURSOR get_aplinst_adm_period IS
332 SELECT adm_cal_type,adm_ci_sequence_number
333 FROM igs_ad_ps_appl_inst
334 WHERE person_id = p_person_id
335 AND admission_appl_number = p_admission_appl_number
336 AND nominated_course_cd = p_nominated_course_cd
337 AND sequence_number = p_sequence_number;
338
339 -- Following cursors added as part of Single Response build Bug:3132406
340 CURSOR get_single_response (p_admission_cat igs_ad_appl_all.admission_cat%TYPE,
341 p_s_admission_process_type igs_ad_appl_all.s_admission_process_type%TYPE) IS
342 SELECT admprd.single_response_flag
343 FROM igs_ad_prd_ad_prc_ca admprd,
344 igs_ad_appl_all appl,
345 igs_ad_ps_appl_inst_all aplinst
346 WHERE appl.person_id = p_person_id
347 AND appl.admission_appl_number = p_admission_appl_number
348 AND appl.person_id = aplinst.person_id
349 AND appl.admission_appl_number = aplinst.admission_appl_number
350 AND admprd.adm_cal_type = NVL(aplinst.adm_cal_type,appl.adm_cal_type)
351 AND admprd.adm_ci_sequence_number = NVL(aplinst.adm_ci_sequence_number,appl.adm_ci_sequence_number)
352 AND admprd.admission_cat = p_admission_cat
353 AND admprd.s_admission_process_type = p_s_admission_process_type;
354
355
356 CURSOR get_aplinst_response_accepted IS
357 SELECT distinct appl.application_id, aplinst.nominated_course_cd
358 FROM igs_ad_appl_all appl,
359 igs_ad_ps_appl_inst aplinst,
360 igs_ad_prd_ad_prc_ca admprd
361 WHERE appl.person_id = aplinst.person_id
362 AND appl.admission_appl_number = aplinst.admission_appl_number
363 AND appl.person_id = p_person_id
364 AND igs_ad_gen_009.admp_get_sys_aors(aplinst.adm_offer_resp_status) = 'ACCEPTED'
365 AND admprd.adm_cal_type = NVL(aplinst.adm_cal_type,appl.adm_cal_type)
366 AND admprd.adm_ci_sequence_number = NVL(aplinst.adm_ci_sequence_number,appl.adm_ci_sequence_number)
367 AND admprd.admission_cat = appl.admission_cat
368 AND admprd.s_admission_process_type = appl.s_admission_process_type
369 AND admprd.single_response_flag = 'Y';
370
371
372 CURSOR get_alternate_code ( p_cal_type igs_ca_inst.cal_type%TYPE,
373 p_sequence_number igs_ca_inst.sequence_number%TYPE) IS
374 SELECT alternate_code
375 FROM igs_ca_inst
376 WHERE cal_type = p_cal_type
377 AND sequence_number = p_sequence_number;
378
379 c_appl_inst_ctxt_rec c_appl_inst_ctxt%ROWTYPE;
380 v_step_type VARCHAR2(100);
381 l_deferral_allowed VARCHAR2(1);
382 l_pre_enrol VARCHAR2(1);
383 l_multi_offer_allowed VARCHAR2(1);
384 l_multi_offer_limit NUMBER(10);
385 v_message_name VARCHAR2(100);
386 l_valid_def_adm_cal VARCHAR2(1);
387 l_valid_def_acad_cal VARCHAR2(1);
388 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
389 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
390 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
391 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
392 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
393 cst_accepted CONSTANT VARCHAR2(10) := 'ACCEPTED';
394 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
395 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
396 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
397 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
398
399 l_applicant_acptnce_cndtn igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE;
400 l_inst_cd igs_or_institution.institution_cd%TYPE;
401
402 v_admission_cat igs_ad_appl.admission_cat%TYPE;
403 v_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;
404 v_acad_cal_type igs_ad_appl.acad_cal_type%TYPE;
405 v_acad_ci_sequence_number igs_ad_appl.acad_ci_sequence_number%TYPE;
406 v_aa_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
407 v_aa_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
408 v_acaiv_adm_cal_type igs_ad_ps_appl_inst_all.adm_cal_type%TYPE;
409 v_acaiv_adm_ci_sequence_number igs_ad_ps_appl_inst_all.adm_ci_sequence_number%TYPE;
410 v_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
411 v_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
412 v_appl_dt igs_ad_appl.appl_dt%TYPE;
413 v_adm_appl_status igs_ad_appl.adm_appl_status%TYPE;
414 v_adm_fee_status igs_ad_appl.adm_fee_status%TYPE;
415 l_single_response_flag igs_ad_prd_ad_prc_ca.single_response_flag%TYPE;
416 l_application_id igs_ad_appl_all.application_id%TYPE;
417 l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
418 l_acad_alt_code igs_ca_inst.alternate_code%TYPE;
419 l_adm_alt_code igs_ca_inst.alternate_code%TYPE;
420
421 BEGIN
422
423 --Initialize the flag indicating the SUCCESS ('Y'). Gets updated to 'N' even if one validation fails
424 p_validation_success := 'Y';
425
426 --Validations on the Application Instance Outcome Status. Check if the Applicant's Outcome Status is mapped to one of the System Outcome Status of
427 --'Make Offer of Admission' (OFFER) or 'Make Offer of Admission Subject to Condition' (COND-OFFER).
428 IF NVL(igs_ad_gen_008.admp_get_saos(acaiv_rec.adm_outcome_status), 'NULL') NOT IN (cst_offer, cst_cond_offer) THEN
429 insert_int_error(p_offresp_int_id, 'E618',NULL);
430 p_validation_success := 'N';
431 logdetail(p_offresp_int_id, 'E618', NULL, 'validate_off_resp_dtls: IF NVL(igs_ad_gen_008.admp_get_saos(acaiv_rec.adm_outcome_status)' ,NULL);
432 RETURN;
433 END IF;
434
435 --Check if the Interface Offer Response Status is a valid Offer Response Status mapped to one of the System Offer Response Statuses.
436 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) IS NULL THEN
437 insert_int_error(p_offresp_int_id, 'E600',NULL);
438 p_validation_success := 'N';
439 logdetail(p_offresp_int_id, 'E600', NULL, 'validate_off_resp_dtls: IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status)' ,NULL);
440 RETURN;
441 END IF;
442
443
444 /********** VALIDATIONS WHICH CHECK FOR THE PROPER COMBINATION OF INTERFACE Vs SYSTEM TABLE Offer Response Status. Stop processing in case of failure *******/
445
446 -- Check if Interface Offer Response status = Production table (IGS_AD_PS_APPL_INST_ALL) Offer Response Status.
447 /* Hashed this code as part of bug fix for 2631947. The corresponding MNT fix is through 2624637 */
448 /*IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = igs_ad_gen_008.admp_get_saors(acaiv_rec.adm_offer_resp_status) THEN */
449
450 IF p_adm_offer_resp_status = acaiv_rec.adm_offer_resp_status THEN
451 insert_int_error(p_offresp_int_id, 'E601',NULL);
452 p_validation_success := 'N';
453 logdetail(p_offresp_int_id, 'E601', NULL, 'validate_off_resp_dtls: IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status)' ,NULL);
454 RETURN;
455 END IF;
456 -- Check if the Interface Offer Response Status is allowed to update the existing offer response in production table.
457
458 -- If Offer Response is changed to PENDING from DEFERRAL, then the Deffered Calendars should be NULL otherswise insert error record into corresponding table
459 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) <> cst_pending AND igs_ad_gen_008.admp_get_saors(acaiv_rec.adm_offer_resp_status) = cst_deferral THEN
460 IF p_def_acad_cal_type IS NOT NULL OR p_def_acad_ci_sequence_number IS NOT NULL THEN
461 insert_int_error(p_offresp_int_id, 'E612',NULL);
462 p_validation_success := 'N';
463 logdetail(p_offresp_int_id, 'E612', NULL, 'validate_off_resp_dtls: Check if the Interface Offer Response Status is allowed to update' ,NULL);
464 RETURN;
465 END IF;
466 IF p_def_adm_cal_type IS NOT NULL OR p_def_adm_ci_sequence_number IS NOT NULL THEN
467 insert_int_error(p_offresp_int_id, 'E611',NULL);
468 p_validation_success := 'N';
469 logdetail(p_offresp_int_id, 'E611', NULL, 'validate_off_resp_dtls: Check if the Interface Offer Response Status is allowed to update' ,NULL);
470 RETURN;
471 END IF;
472 IF p_validation_success = 'N' THEN
473 RETURN;
474 END IF;
475 END IF;
476
477 /***** END OF VALIDATIONS CHECKING FOR THE PROPER COMBINATION OF OFFER RESPONSE STATUS (Interface Table Vs System Table) ************/
478
479 --Copy the interface Actual Response Date to the OUT NOCOPY Variable p_calc_actual_ofr_resp_dt and populate this variable accordingly after necessary validations
480 p_calc_actual_ofr_resp_dt := p_actual_offer_response_dt;
481
482 -- Check if Interface Offer Response Status is Other than 'PENDING', 'LAPSED', 'NOT-APPLIC'.
483 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) NOT IN (cst_pending, cst_lapsed, cst_not_applic) THEN
484 IF p_calc_actual_ofr_resp_dt IS NULL THEN
485 p_calc_actual_ofr_resp_dt := SYSDATE;
486 ELSE
487 IF TRUNC(p_calc_actual_ofr_resp_dt) > TRUNC(SYSDATE) THEN
488 insert_int_error(p_offresp_int_id, 'E607',NULL);
489 p_validation_success := 'N';
490 logdetail(p_offresp_int_id, 'E607', NULL, 'validate_off_resp_dtls: IF TRUNC(p_calc_actual_ofr_resp_dt) > TRUNC(SYSDATE) THEN' ,NULL);
491 RETURN;
492 END IF;
493 END IF;
494 END IF;
495
496 --arvsrini igsm
497 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) IN ('REJECTED','NOT-COMING') AND p_decline_ofr_reason IS NULL THEN
498 insert_int_error(p_offresp_int_id, 'E592',NULL);
499 p_validation_success := 'N';
500 logdetail(p_offresp_int_id, 'E592', NULL, 'validate_off_resp_dtls: Check if the Interface Offer Response Status is allowed to update' ,NULL);
501
502 RETURN;
503 END IF;
504
505 --arvsrini igsm
506 IF p_decline_ofr_reason = 'OTHER-INST' AND p_attent_other_inst_cd IS NULL THEN
507 insert_int_error(p_offresp_int_id, 'E593',NULL);
508 p_validation_success := 'N';
509 logdetail(p_offresp_int_id, 'E593', NULL, 'validate_off_resp_dtls: Check if the Interface Offer Response Status is allowed to update' ,NULL);
510 RETURN;
511 END IF;
512
513
514
515
516 -- Validate admission offer response status
517 FOR c_apcs_rec IN c_apcs(appl_rec.admission_cat,appl_rec.s_admission_process_type) LOOP
518 IF c_apcs_rec.s_admission_step_type = 'DEFER' THEN
519 l_deferral_allowed := 'Y';
520 END IF;
521 IF c_apcs_rec.s_admission_step_type = 'PRE-ENROL' THEN
522 v_step_type := 'IGSAD' || SUBSTR (ltrim(rtrim(c_apcs_rec.s_admission_step_type)),1,3);
523 IF fnd_function.test(v_step_type) THEN
524 l_pre_enrol := 'Y';
525 END IF;
526 END IF;
527 IF c_apcs_rec.s_admission_step_type = 'MULTI-OFF' THEN
528 l_multi_offer_allowed := 'Y';
529 l_multi_offer_limit := c_apcs_rec.step_type_restriction_num;
530 END IF;
531
532 END LOOP;
533 IF igs_ad_val_acai_status.admp_val_aors_item(
534 p_person_id,
535 p_admission_appl_number,
536 p_nominated_course_cd,
537 p_sequence_number,
538 acaiv_rec.course_cd,
539 p_adm_offer_resp_status,
540 p_calc_actual_ofr_resp_dt,
541 appl_rec.s_admission_process_type,
542 NVL(l_deferral_allowed,'N'),
543 NVL(l_pre_enrol, 'N'),
544 v_message_name,
545 p_decline_ofr_reason, --arvsrini igsm
546 p_attent_other_inst_cd
547 ) = FALSE THEN
548 insert_int_error(p_offresp_int_id, NULL,v_message_name);
549 p_validation_success := 'N';
550 logdetail(p_offresp_int_id, NULL, v_message_name, 'validate_off_resp_dtls: IF igs_ad_val_acai_status.admp_val_aors_item' ,NULL);
551 RETURN;
552 END IF;
553
554
555 -- Validations on the Offer Deferment Status
556 -- Though Offer Deferment Status is not directly imported from Offer Response Interface table, it should be populated
557 -- with either of the values 'PENDING' or 'NOT-APPLIC' depending on the value of Offer Response Status.
558 -- Default the Offer Deferment Status, depending on the value of Offer Response Status, and validate the same.
559 -- IF Offer Response Status is 'DEFERRAL', then default the Offer Deferment Status to 'PENDING'.
560 -- ELSE Offer Response Status is not equal to 'DEFERRAL', then Default the Offer Deferment Status to 'NOT-APPLIC'.
561
562 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = cst_deferral AND
563 NVL(igs_ad_gen_008.admp_get_saods(acaiv_rec.adm_offer_dfrmnt_status), cst_not_applic) = cst_not_applic THEN
564 IF igs_ad_gen_009.admp_get_sys_aods(cst_pending) IS NULL THEN
565 insert_int_error(p_offresp_int_id, 'E625',NULL);
566 p_validation_success := 'N';
567 logdetail(p_offresp_int_id, 'E625', NULL, 'IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status)' ,NULL);
568 RETURN;
569 ELSE
570 p_adm_offer_defr_status := igs_ad_gen_009.admp_get_sys_aods(cst_pending);
571 -- ADMISSION COURSE APPLICATION INSTANCE: Admission Offer Deferment Status.
572 IF p_adm_offer_defr_status <> acaiv_rec.adm_offer_dfrmnt_status THEN
573 -- Validate.
574 IF igs_ad_val_acai_status.admp_val_acai_aods (
575 p_person_id,
576 p_admission_appl_number,
577 p_nominated_course_cd,
578 p_sequence_number,
579 acaiv_rec.course_cd,
580 p_adm_offer_defr_status,
581 acaiv_rec.adm_offer_dfrmnt_status,
582 p_adm_offer_resp_status,
583 NVL(l_deferral_allowed,'N'),
584 appl_rec.s_admission_process_type,
585 v_message_name) = FALSE THEN
586 insert_int_error(p_offresp_int_id, NULL,v_message_name);
587 p_validation_success := 'N';
588 logdetail(p_offresp_int_id, NULL, v_message_name, 'validate_off_resp_dtls: IF igs_ad_val_acai_status.admp_val_acai_aods' ,NULL);
589 RETURN;
590 ELSE
591 --Beginning of the deferred calendar validations here
592 OPEN c_appl_inst_ctxt(
593 p_person_id ,
594 p_admission_appl_number,
595 p_nominated_course_cd,
596 p_sequence_number
597 );
598 FETCH c_appl_inst_ctxt INTO c_appl_inst_ctxt_rec;
599 CLOSE c_appl_inst_ctxt;
600
601 --Begin of checking the Def Acad cals for NULL values
602 IF p_def_acad_cal_type IS NULL OR p_def_acad_ci_sequence_number IS NULL THEN
603 insert_int_error(p_offresp_int_id,'E614', NULL);
604 p_validation_success := 'N';
605 logdetail(p_offresp_int_id,'E614', NULL, 'validate_off_resp_dtls: IF p_def_acad_cal_type IS NULL OR p_def_acad_ci_sequence_number' ,NULL);
606 RETURN;
607 ELSE
608 l_valid_def_acad_cal := 'N';
609 FOR valid_acad_cal_rec IN c_valid_acad_cals(c_appl_inst_ctxt_rec.acad_ci_start_dt,
610 c_appl_inst_ctxt_rec.acad_ci_end_dt) LOOP
611 IF p_def_acad_cal_type = valid_acad_cal_rec.def_acad_cal_type AND
612 p_def_acad_ci_sequence_number = valid_acad_cal_rec.def_acad_ci_seq_number THEN
613
614 l_valid_def_acad_cal := 'Y';
615 EXIT;
616 END IF;
617 END LOOP;
618
619 IF l_valid_def_acad_cal = 'N' THEN
620 insert_int_error(p_offresp_int_id, 'E612', NULL);
621 p_validation_success := 'N';
622 logdetail(p_offresp_int_id,'E612', NULL, 'validate_off_resp_dtls: IF l_valid_def_acad_cal = ' ,NULL);
623 RETURN;
624 --validate the Deferred Academic Calendar.
625 ELSE
626 --Beginning of IF for adm cals
627 IF p_def_adm_cal_type IS NULL OR p_def_adm_ci_sequence_number IS NULL THEN
628 insert_int_error(p_offresp_int_id,'E615', NULL);
629 logdetail(p_offresp_int_id,'E615', NULL, 'IF p_def_adm_cal_type IS NULL OR' ,NULL);
630 p_validation_success := 'N';
631 RETURN;
632 ELSE
633 --validate the Deferred Admission Calendar.
634 l_valid_def_adm_cal := 'N';
635 FOR valid_adm_cal_rec IN c_valid_adm_cals(
636 appl_rec.admission_cat,
637 appl_rec.s_admission_process_type,
638 p_def_acad_cal_type,
639 p_def_acad_ci_sequence_number,
640 c_appl_inst_ctxt_rec.adm_ci_end_dt) LOOP
641 IF p_def_adm_cal_type = valid_adm_cal_rec.def_adm_cal_type AND
642 p_def_adm_ci_sequence_number = valid_adm_cal_rec.def_adm_ci_seq_number THEN
643 l_valid_def_adm_cal := 'Y';
644 EXIT;
645 END IF;
646 END LOOP;
647
648 IF l_valid_def_adm_cal = 'N' THEN
649 insert_int_error(p_offresp_int_id, 'E611', NULL);
650 p_validation_success := 'N';
651 RETURN;
652 END IF;
653
654 END IF; --End of IF for adm cals
655 END IF; --l_valid_def_acad_cal <> 'N'
656 END IF; --END of checking the Def Acad cals for NULL values
657 END IF; --End of igs_ad_val_acai_status.admp_val_acai_aods
658 END IF; ---End of the deferred calendar validations here
659 END IF;
660 ELSE -- Of DEFERRAL check
661 IF igs_ad_gen_009.admp_get_sys_aods(cst_not_applic) IS NULL THEN
662 insert_int_error(p_offresp_int_id, 'E602',NULL);
663 p_validation_success := 'N';
664 logdetail(p_offresp_int_id,'E602', NULL, 'validate_off_resp_dtls: IF igs_ad_gen_009.admp_get_sys_aods' ,NULL);
665 RETURN;
666 ELSE
667 p_adm_offer_defr_status := igs_ad_gen_009.admp_get_sys_aods(cst_not_applic);
668 END IF;
669 END IF; -- Of DEFERRAL check
670
671 -- Validate all the Offer Response details here
672 IF igs_ad_val_acai_status.admp_val_acai_aors(
673 p_person_id,
674 p_admission_appl_number,
675 p_nominated_course_cd,
676 p_sequence_number,
677 acaiv_rec.course_cd,
678 p_adm_offer_resp_status,
679 acaiv_rec.adm_offer_resp_status,
680 acaiv_rec.adm_outcome_status,
681 p_adm_offer_defr_status,
682 acaiv_rec.adm_offer_dfrmnt_status,
683 acaiv_rec.adm_outcome_status_auth_dt,
684 p_calc_actual_ofr_resp_dt,
685 appl_rec.adm_cal_type,
686 appl_rec.adm_ci_sequence_number,
687 appl_rec.admission_cat,
688 appl_rec.s_admission_process_type,
689 NVL(l_deferral_allowed,'N'),
690 NVL(l_multi_offer_allowed,'N'),
691 l_multi_offer_limit,
692 NVL(l_pre_enrol, 'N'),
693 acaiv_rec.cndtnl_offer_must_be_stsfd_ind,
694 acaiv_rec.cndtnl_offer_satisfied_dt,
695 'FORM',
696 v_message_name,
697 p_decline_ofr_reason, --arvsrini igsm
698 p_attent_other_inst_cd
699 ) = FALSE THEN
700 insert_int_error(p_offresp_int_id, NULL,v_message_name);
701 p_validation_success := 'N';
702 logdetail(p_offresp_int_id, NULL, v_message_name, 'validate_off_resp_dtls: IGS_AD_VAL_ACAI_STATUS.admp_val_acai_aors' ,NULL);
703 RETURN;
704 END IF;
705
706
707 --Validations on the Actual Response Date
708 IF p_calc_actual_ofr_resp_dt IS NULL and acaiv_rec.actual_response_dt IS NOT NULL OR
709 p_calc_actual_ofr_resp_dt IS NOT NULL AND acaiv_rec.actual_response_dt IS NULL OR
710 (TRUNC(p_calc_actual_ofr_resp_dt) IS NOT NULL AND TRUNC(acaiv_rec.actual_response_dt) IS NOT NULL
711 AND TRUNC(p_calc_actual_ofr_resp_dt) <> TRUNC(acaiv_rec.actual_response_dt)
712 ) OR
713 p_adm_offer_resp_status <> acaiv_rec.adm_offer_resp_status THEN
714 -- Validate.
715 IF igs_ad_val_acai.admp_val_act_resp_dt (
716 p_calc_actual_ofr_resp_dt,
717 p_adm_offer_resp_status,
718 acaiv_rec.offer_dt,
719 v_message_name) = FALSE THEN
720 insert_int_error(p_offresp_int_id, NULL,v_message_name);
721 p_validation_success := 'N';
722 logdetail(p_offresp_int_id, NULL, v_message_name, 'validate_off_resp_dtls: IF IGS_AD_VAL_ACAI.admp_val_act_resp_dt' ,NULL);
723 RETURN;
724 END IF;
725 END IF;
726
727
728 -- if the offer response date is elapsed then continue with other validations if p_yes_no is 'Y'. Otherwise insert error record into Interface tables.
729 IF p_calc_actual_ofr_resp_dt > acaiv_rec.offer_response_dt AND TRUNC(acaiv_rec.offer_response_dt) < TRUNC(SYSDATE) AND
730 igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = cst_accepted THEN
731 IF p_yes_no = '2' THEN
732 insert_int_error(p_offresp_int_id, NULL,'IGS_AD_RESP_DT_PASSED');
733 p_validation_success := 'N';
734 logdetail(p_offresp_int_id, NULL, 'IGS_AD_RESP_DT_PASSED', 'validate_off_resp_dtls: IF p_calc_actual_ofr_resp_dt > acaiv_rec.offer_response_dt' ,NULL);
735 RETURN;
736 END IF;
737 END IF;
738
739
740 --Single Response build Bug:3132406
741 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = cst_accepted THEN
742
743 -- Get the Application level details which are required for Single Response validation. Bug:3132406
744 igs_ad_gen_002.admp_get_aa_dtl(
745 p_person_id,
746 p_admission_appl_number,
747 v_admission_cat,
748 v_s_admission_process_type,
749 v_acad_cal_type,
750 v_acad_ci_sequence_number,
751 v_aa_adm_cal_type,
752 v_aa_adm_ci_sequence_number,
753 v_appl_dt,
754 v_adm_appl_status,
755 v_adm_fee_status);
756
757 OPEN get_single_response (v_admission_cat,v_s_admission_process_type);
758 FETCH get_single_response INTO l_single_response_flag;
759 CLOSE get_single_response;
760
761 OPEN get_aplinst_adm_period;
762 FETCH get_aplinst_adm_period
763 INTO v_acaiv_adm_cal_type,v_acaiv_adm_ci_sequence_number;
764 CLOSE get_aplinst_adm_period;
765
766 IF l_single_response_flag = 'Y' THEN
767 OPEN get_aplinst_response_accepted;
768 FETCH get_aplinst_response_accepted INTO l_application_id,l_nominated_course_cd;
769 IF get_aplinst_response_accepted%FOUND THEN
770 CLOSE get_aplinst_response_accepted;
771
772 OPEN get_alternate_code(v_acad_cal_type,v_acad_ci_sequence_number);
773 FETCH get_alternate_code INTO l_acad_alt_code;
774 CLOSE get_alternate_code;
775
776 OPEN get_alternate_code(NVL(v_acaiv_adm_cal_type,v_aa_adm_cal_type),
777 NVL(v_acaiv_adm_ci_sequence_number,v_aa_adm_ci_sequence_number));
778 FETCH get_alternate_code INTO l_adm_alt_code;
779 CLOSE get_alternate_code;
780
781 FND_MESSAGE.SET_NAME('IGS','IGS_AD_SINGLE_OFFRESP_EXISTS');
782 FND_MESSAGE.SET_TOKEN ('PROG_CODE',l_nominated_course_cd);
783 FND_MESSAGE.SET_TOKEN ('APPL_ID', TO_CHAR(l_application_id));
784 FND_MESSAGE.SET_TOKEN ('ACAD_ADM_PRD', l_acad_alt_code||'/'||l_adm_alt_code);
785
786 insert_int_error(p_offresp_int_id, 'E693',fnd_message.get());
787 p_validation_success := 'N';
788 logdetail(p_offresp_int_id, 'E693',NULL, 'validate Single Response' ,NULL);
789 RETURN;
790 ELSE
791 CLOSE get_aplinst_response_accepted;
792 END IF;
793 END IF;
794 END IF;
795
796 --Bug 3181590
797 IF TRUNC(p_prpsd_commencement_date) > TRUNC(SYSDATE) OR TRUNC(p_prpsd_commencement_date) < TRUNC(v_appl_dt)
798 OR TRUNC(p_prpsd_commencement_date) < TRUNC(acaiv_rec.offer_dt) THEN
799 insert_int_error(p_offresp_int_id, NULL,'IGS_AD_PRPSD_CMCMNT_DT_INVALID');
800 p_validation_success := 'N';
801 logdetail(p_offresp_int_id, NULL, 'IGS_AD_PRPSD_CMCMNT_DT_INVALID', 'validate_off_resp_dtls: IF TRUNC(p_prpsd_commencement_date) > TRUNC(SYSDATE) ' ,NULL);
802 RETURN;
803 END IF;
804
805 -- Validations on the Application Acceptance Condition
806 IF p_applicant_acptnce_cndtn IS NOT NULL THEN
807 IF p_applicant_acptnce_cndtn <> acaiv_rec.applicant_acptnce_cndtn OR
808 p_adm_offer_resp_status <> acaiv_rec.adm_offer_resp_status THEN
809 -- Validate the acceptance condition
810 IF igs_ad_val_acai.admp_val_acpt_cndtn (
811 p_applicant_acptnce_cndtn,
812 p_adm_offer_resp_status,
813 v_message_name) = FALSE THEN
814 insert_int_error(p_offresp_int_id, NULL,v_message_name);
815 p_validation_success := 'N';
816 logdetail(p_offresp_int_id, NULL, v_message_name, 'validate_off_resp_dtls: IF IGS_AD_VAL_ACAI.admp_val_acpt_cndtn' ,NULL);
817 RETURN;
818 END IF; -- End of igs_ad_val_acai.admp_val_acpt_cndtn
819 END IF;
820 END IF;
821
822
823 -- Validations on Other Institution Code. It should be a valid institution_cd From IGS_OR_INSTITUTION table.
824
825 IF p_attent_other_inst_cd IS NOT NULL THEN
826 OPEN valid_inst_cur(p_attent_other_inst_cd);
827 FETCH valid_inst_cur INTO l_inst_cd;
828 IF valid_inst_cur%NOTFOUND THEN
829 insert_int_error(p_offresp_int_id,'E608',NULL);
830 p_validation_success := 'N';
831 logdetail(p_offresp_int_id,'E608', NULL, 'validate_off_resp_dtls: IF valid_inst_cur%NOTFOUND THEN ' ,NULL);
832 RETURN;
833 END IF;
834 CLOSE valid_inst_cur;
835 END IF;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 IF c_apcs%ISOPEN THEN
840 CLOSE c_apcs;
841 END IF;
842 IF valid_inst_cur%ISOPEN THEN
843 CLOSE valid_inst_cur;
844 END IF;
845 IF c_appl_inst_ctxt%ISOPEN THEN
846 CLOSE c_appl_inst_ctxt;
847 END IF;
848 IF c_valid_acad_cals%ISOPEN THEN
849 CLOSE c_valid_acad_cals;
850 END IF;
851 IF c_valid_adm_cals%ISOPEN THEN
852 CLOSE c_valid_adm_cals;
853 END IF;
854 p_validation_success := 'N';
855 logdetail(p_offresp_int_id,'E621', NULL, 'validate_off_resp_dtls: EXCEPTION WHEN OTHERS ' ,NULL);
856 FND_FILE.PUT_LINE(FND_FILE.LOG,'validate_off_resp_dtls: '||FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' '||SQLERRM);
857 END validate_off_resp_dtls;
858
859 PROCEDURE imp_off_resp( errbuf OUT NOCOPY VARCHAR2,
860 retcode OUT NOCOPY NUMBER,
861 p_batch_id IN igs_ad_offresp_batch.batch_id%TYPE,
862 p_yes_no IN VARCHAR2)
863 AS
864 ---------------------------------------------------------------------------------------------------------------------------------------
865 -- Created By : rboddu
866 -- Date Created On : 09-SEP-2002
867 -- Purpose : Bug 2395510. Main procedure of this package.
868 -- Process flow
869 -- 1. Import all the Pending Interface Offer Response records into PL/SQL tables using BULK COLLECT.
870 -- 2. Perform all the validations on each Interface record by calling local procedure validate_off_resp_dtls.
871 -- 3. If any of the validations fail then insert Error record into the interface error table IGS_AD_OFFRESP_ERR.
872 -- 3. If validations Successful then Update the OSS table IGS_AD_PS_APPL_INST_ALL with Interface Offer Response details.
873 -- 4. If the Offer Response status of Application Instance is updated to 'ACCEPTED' then perform the Pre enrollment by calling
874 -- the corresponding job.
875 -- 5. If the record is succesfully imported then Delete the interface record from IGS_AD_OFFRESP_INT.
876 -- 6. If All the interface records for the given batch_id are succesfully imported then delete the Batch record from IGS_AD_OFFRESP_BATCH.
877 -- 7. Once all the pending records are processed then invoke the Import Offer Response Error Rerport (IGSADS21).
878 -- Know limitations, enhancements or remarks
879 -- Change History
880 -- Who When What
881 -- knag 28-Oct-2002 Called func igs_ad_gen_003.get_core_or_optional_unit for bug 2647482
882 -- rboddu 11/17/2003 Added p_prpsd_commencement_date declartions and import of it.
883 ---------------------------------------------------------------------------------------------------------------------------------------
884
885 /* Variable Declaration for BULK COLLECT feature (PL/SQL tables which hold the data for each column of interface table)*/
886 TYPE offresp_int_idType IS TABLE OF igs_ad_offresp_int.offresp_int_id%TYPE;
887 TYPE batch_idType IS TABLE OF igs_ad_offresp_int.batch_id%TYPE;
888 TYPE person_idType IS TABLE OF igs_ad_offresp_int.person_id%TYPE;
889 TYPE admission_appl_numberType IS TABLE OF igs_ad_offresp_int.admission_appl_number%TYPE;
890 TYPE nominated_course_cdType IS TABLE OF igs_ad_offresp_int.nominated_course_cd%TYPE;
891 TYPE sequence_numberType IS TABLE OF igs_ad_offresp_int.sequence_number%TYPE;
892 TYPE adm_offer_resp_statusType IS TABLE OF igs_ad_offresp_int.adm_offer_resp_status%TYPE;
893 TYPE decline_ofr_reasonType IS TABLE OF igs_ad_offresp_int.decline_ofr_reason%TYPE;
894 TYPE actual_offer_response_dtType IS TABLE OF igs_ad_offresp_int.actual_offer_response_dt%TYPE;
895 TYPE attent_other_inst_cdType IS TABLE OF igs_ad_offresp_int.attent_other_inst_cd%TYPE;
896 TYPE applicant_acptnce_cndtnType IS TABLE OF igs_ad_offresp_int.applicant_acptnce_cndtn%TYPE;
897 TYPE def_acad_cal_typeType IS TABLE OF igs_ad_offresp_int.def_acad_cal_type%TYPE;
898 TYPE def_acad_ci_seq_numType IS TABLE OF igs_ad_offresp_int.def_acad_ci_sequence_number%TYPE;
899 TYPE def_adm_cal_typeType IS TABLE OF igs_ad_offresp_int.def_adm_cal_type%TYPE;
900 TYPE def_adm_ci_sequence_numberType IS TABLE OF igs_ad_offresp_int.def_adm_ci_sequence_number%TYPE;
901 TYPE statusType IS TABLE OF igs_ad_offresp_int.status%TYPE;
902 TYPE prpsd_commencement_dateType IS TABLE OF igs_ad_offresp_int.prpsd_commencement_date%TYPE;
903
904 t_offresp_int_id offresp_int_idType;
905 t_batch_id batch_idType;
906 t_person_id person_idType;
907 t_admission_appl_number admission_appl_numberType;
908 t_nominated_course_cd nominated_course_cdType;
909 t_sequence_number sequence_numberType;
910 t_adm_offer_resp_status adm_offer_resp_statusType;
911 t_decline_ofr_reason decline_ofr_reasonType; --arvsrini igsm
912 t_actual_offer_response_dt actual_offer_response_dtType;
913 t_attent_other_inst_cd attent_other_inst_cdType ;
914 t_applicant_acptnce_cndtn applicant_acptnce_cndtnType;
915 t_def_acad_cal_type def_acad_cal_typeType;
916 t_def_acad_ci_sequence_number def_acad_ci_seq_numType;
917 t_def_adm_cal_type def_adm_cal_typeType;
918 t_def_adm_ci_sequence_number def_adm_ci_sequence_numberType;
919 t_status statusType;
920 t_prpsd_commencement_date prpsd_commencement_dateType;
921
922
923 CURSOR int_off_resp_cur IS
924 SELECT
925 ofresp.offresp_int_id,
926 ofresp.batch_id,
927 ofresp.person_id,
928 ofresp.admission_appl_number,
929 ofresp.nominated_course_cd,
930 ofresp.sequence_number,
931 ofresp.adm_offer_resp_status,
932 ofresp.decline_ofr_reason, --arvsrini igsm
933 ofresp.actual_offer_response_dt,
934 ofresp.attent_other_inst_cd,
935 ofresp.applicant_acptnce_cndtn,
936 ofresp.def_acad_cal_type,
937 ofresp.def_acad_ci_sequence_number,
938 ofresp.def_adm_cal_type,
939 ofresp.def_adm_ci_sequence_number,
940 ofresp.status,
941 ofresp.prpsd_commencement_date
942 FROM
943 igs_ad_offresp_int ofresp
944 WHERE
945 ofresp.batch_id = p_batch_id AND
946 ofresp.status = '2';
947
948 -- Cursor to get the Record status, which indicates whether the Record is succesfully Imported or not.
949 CURSOR int_record_status(p_offresp_id igs_ad_offresp_int.offresp_int_id%TYPE) IS
950 SELECT ofresp.status
951 FROM
952 igs_ad_offresp_int ofresp
953 WHERE
954 ofresp.offresp_int_id = p_offresp_id;
955
956 CURSOR c_adm_appl_dtl (cp_person_id igs_ad_appl_all.person_id%TYPE ,
957 cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS
958 SELECT appl.*
959 FROM igs_ad_appl_all appl
960 WHERE person_id = cp_person_id AND
961 admission_appl_number = cp_admission_appl_number;
962
963 --Cursor to check if there is any Pending or Error interface Offer Response record is present in IGS_AD_OFFRESP_INT;
964 CURSOR c_processed_recs(p_batch_id igs_ad_offresp_batch.batch_id%TYPE) IS
965 SELECT offresp_int_id
966 FROM igs_ad_offresp_int
967 WHERE batch_id = p_batch_id AND
968 status IN ('2','3');
969
970 CURSOR get_conc_desctiption(l_name fnd_concurrent_programs_vl.concurrent_program_name%TYPE) IS
971 SELECT description
972 FROM fnd_concurrent_programs_vl
973 WHERE concurrent_program_name = l_name;
974
975 l_completed_flag VARCHAR2(1) ;
976 v_message_name VARCHAR2(30) ;
977 v_warn_level VARCHAR2(10);
978 l_request_id NUMBER;
979 l_tot_rec_processed PLS_INTEGER;
980
981 l_processed_Rec_Stat igs_ad_offresp_int.status%TYPE;
982 l_adm_offer_defr_status igs_ad_ps_appl_inst_all.adm_offer_dfrmnt_status%TYPE;
983 l_validation_success VARCHAR2(1);
984 l_acaiv_rec cur_ad_ps_appl_inst%ROWTYPE;
985 l_appl_rec igs_ad_appl_all%ROWTYPE;
986 l_calc_actual_ofr_resp_dt igs_ad_offresp_int.actual_offer_response_dt%TYPE;
987 l_offresp_id igs_ad_offresp_int.offresp_int_id%TYPE;
988 l_conc_description fnd_concurrent_programs_vl.description%TYPE;
989 l_space_string VARCHAR2(300);
990
991
992 l_gather_status VARCHAR2(5);
993 l_industry VARCHAR2(5);
994 l_schema VARCHAR2(30);
995 l_gather_return BOOLEAN;
996 l_owner VARCHAR2(30);
997
998 l_pre_enrol_success VARCHAR2(1);
999
1000 --Local variables to check if the Security Policy exception already set or not. Ref: Bug 3919112
1001 l_sc_encoded_text VARCHAR2(4000);
1002 l_sc_msg_count NUMBER;
1003 l_sc_msg_index NUMBER;
1004 l_sc_app_short_name VARCHAR2(50);
1005 l_sc_message_name VARCHAR2(50);
1006
1007 x_dummy VARCHAR2(2000);
1008 lv_un_conf_prg_atmpt BOOLEAN DEFAULT TRUE;
1009
1010
1011 BEGIN --of procedure imp_off_resp
1012
1013 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
1014 igs_ge_gen_003.set_org_id(null);
1015
1016 retcode:=0;
1017 l_completed_flag :='N';
1018
1019 -- Gather statistics for interface table
1020 -- by rrengara on 20-jan-2003 bug 2711176
1021
1022 BEGIN
1023 l_gather_return := fnd_installation.get_app_info('IGS', l_gather_status, l_industry, l_schema);
1024 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_OFFRESP_BATCH', cascade => TRUE);
1025 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_OFFRESP_INT', cascade => TRUE);
1026 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_OFFRESP_ERR', cascade => TRUE);
1027 EXCEPTION WHEN OTHERS THEN
1028 NULL;
1029 END;
1030
1031
1032 -- Delete all the records from the error table for the currently processed Batch ID
1033 DELETE igs_ad_offresp_err WHERE offresp_int_id IN (SELECT offresp_int_id FROM igs_ad_offresp_int WHERE batch_id = p_batch_id);
1034
1035 --Fetch All the Pending (Status = '2) Offer Response Interface records from IGS_AD_OFFRESP_INT (for the given batch id.)
1036 OPEN int_off_resp_cur;
1037 FETCH int_off_resp_cur BULK COLLECT INTO
1038 t_offresp_int_id,
1039 t_batch_id,
1040 t_person_id,
1041 t_admission_appl_number,
1042 t_nominated_course_cd,
1043 t_sequence_number,
1044 t_adm_offer_resp_status,
1045 t_decline_ofr_reason,
1046 t_actual_offer_response_dt,
1047 t_attent_other_inst_cd,
1048 t_applicant_acptnce_cndtn,
1049 t_def_acad_cal_type,
1050 t_def_acad_ci_sequence_number,
1051 t_def_adm_cal_type,
1052 t_def_adm_ci_sequence_number,
1053 t_status,
1054 t_prpsd_commencement_date;
1055
1056 --This piece of code ensures that Processing is not done when there are no Pending Interface records found for given batch_id.
1057 IF NOT (t_offresp_int_id.COUNT >0) THEN
1058 logdetail(p_batch_id, NULL, NULL, NULL,'Y');
1059 FND_MESSAGE.SET_NAME('IGS','IGS_AD_NO_PEND_INT_REC');
1060 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1061 CLOSE int_off_resp_cur;
1062 RETURN;
1063 END IF;
1064
1065 CLOSE int_off_resp_cur;
1066
1067 l_tot_rec_processed := 0; --Variable which stores the number of successfully imported records in the current run
1068
1069 --Put the log header here by calling the logdetail procedure with relavant parameter values.
1070 logdetail(p_batch_id, NULL, NULL, NULL,'Y');
1071
1072 FOR t_idx IN t_offresp_int_id.first..t_offresp_int_id.last LOOP
1073
1074 OPEN c_adm_appl_dtl( t_person_id(t_idx),
1075 t_admission_appl_number(t_idx));
1076 FETCH c_adm_appl_dtl INTO l_appl_rec;
1077 CLOSE c_adm_appl_dtl;
1078
1079 OPEN cur_ad_ps_appl_inst(
1080 t_person_id(t_idx),
1081 t_admission_appl_number(t_idx),
1082 t_nominated_course_cd(t_idx),
1083 t_sequence_number(t_idx));
1084 FETCH cur_ad_ps_appl_inst INTO l_acaiv_rec;
1085 IF cur_ad_ps_appl_inst%FOUND THEN
1086 -- Validate each interface record here by calling the procedure Validate_offer_response_Details.
1087 validate_off_resp_dtls(
1088 t_offresp_int_id (t_idx),
1089 t_batch_id (t_idx),
1090 t_person_id (t_idx),
1091 t_admission_appl_number (t_idx),
1092 t_nominated_course_cd (t_idx),
1093 t_sequence_number (t_idx),
1094 t_adm_offer_resp_status (t_idx),
1095 t_decline_ofr_reason (t_idx),
1096 t_actual_offer_response_dt (t_idx),
1097 t_attent_other_inst_cd (t_idx),
1098 t_applicant_acptnce_cndtn (t_idx),
1099 t_def_acad_cal_type (t_idx),
1100 t_def_acad_ci_sequence_number (t_idx),
1101 t_def_adm_cal_type (t_idx),
1102 t_def_adm_ci_sequence_number (t_idx),
1103 t_status (t_idx),
1104 t_prpsd_commencement_date (t_idx),
1105 l_adm_offer_defr_status, --OUT var
1106 l_calc_actual_ofr_resp_dt,
1107 l_appl_rec,
1108 l_acaiv_rec,
1109 p_yes_no,
1110 l_validation_success); --OUT var
1111
1112 SAVEPOINT current_rec_savepoint;
1113
1114 -- If the record has passed all the validations then import the record into Production table by calling The necessary TBH Update_row call.
1115 l_completed_flag := 'N'; --Flag to ensure proper Updation and Pre Enrolment (If Offer Resp Stat is 'ACCEPTED)
1116
1117
1118
1119
1120
1121 lv_un_conf_prg_atmpt := TRUE;
1122 IF l_validation_success = 'N' THEN
1123 UPDATE igs_ad_offresp_int SET status = '3' WHERE offresp_int_id = t_offresp_int_id(t_idx);
1124 ELSE
1125
1126
1127
1128 -- begin apadegal adtd001 igs.m
1129 BEGIN
1130
1131 IF NVL(IGS_AD_GEN_008.ADMP_GET_SAORS(l_acaiv_rec.adm_offer_resp_status), 'NULL') = 'ACCEPTED'
1132 AND NVL(IGS_AD_GEN_008.ADMP_GET_SAORS(t_adm_offer_resp_status(t_idx)), 'NULL') <> 'ACCEPTED'
1133 THEN
1134 -- UNCONFIRM the Student PROGRAM ATTEMPTS. (api would be provided by enrolments team)
1135 IF NOT IGS_EN_VAL_SCA.handle_rederive_prog_att (p_person_id => l_acaiv_rec.PERSON_ID ,
1136 p_admission_appl_number => l_acaiv_rec.ADMISSION_APPL_NUMBER ,
1137 p_nominated_course_cd => l_acaiv_rec.NOMINATED_COURSE_CD ,
1138 p_sequence_number => l_acaiv_rec.SEQUENCE_NUMBER ,
1139 p_message => x_dummy
1140 )
1141 THEN
1142
1143 lv_un_conf_prg_atmpt := FALSE;
1144 App_Exception.Raise_Exception;
1145
1146 END IF;
1147 END IF;
1148
1149 EXCEPTION --Update operation failed
1150 WHEN OTHERS THEN
1151 ROLLBACK TO current_rec_savepoint;
1152 l_completed_flag := 'N';
1153 UPDATE igs_ad_offresp_int SET status = '3' WHERE offresp_int_id = t_offresp_int_id(t_idx);
1154
1155 x_dummy := FND_MESSAGE.GET;
1156 insert_int_error(t_offresp_int_id (t_idx),NULL,x_dummy);
1157
1158 logdetail(t_offresp_int_id(t_idx), NULL, x_dummy,'imp_off_resp: IF IGS_EN_VAL_SCA.handle_rederive_prog_att: '||x_dummy,NULL);
1159 END ;
1160 -- end apadegal adtd001 igs.m
1161
1162 BEGIN
1163
1164 IF lv_un_conf_prg_atmpt --- if unconfirming SPA is successful
1165 THEN
1166
1167 igs_ad_ps_appl_inst_pkg.update_row (
1168 x_mode => 'R',
1169 x_rowid => l_acaiv_rec.rowid,
1170 x_person_id => l_acaiv_rec.person_id,
1171 x_admission_appl_number => l_acaiv_rec.admission_appl_number,
1172 x_nominated_course_cd => l_acaiv_rec.nominated_course_cd,
1173 x_sequence_number => l_acaiv_rec.sequence_number,
1174 x_predicted_gpa => l_acaiv_rec.predicted_gpa,
1175 x_academic_index => l_acaiv_rec.academic_index,
1176 x_adm_cal_type => l_acaiv_rec.adm_cal_type,
1177 x_app_file_location => l_acaiv_rec.app_file_location,
1178 x_adm_ci_sequence_number => l_acaiv_rec.adm_ci_sequence_number,
1179 x_course_cd => l_acaiv_rec.course_cd,
1180 x_app_source_id => l_acaiv_rec.app_source_id,
1181 x_crv_version_number => l_acaiv_rec.crv_version_number,
1182 x_waitlist_rank => l_acaiv_rec.waitlist_rank,
1183 x_location_cd => l_acaiv_rec.location_cd,
1184 x_attent_other_inst_cd => t_attent_other_inst_cd (t_idx), --From Interface
1185 x_attendance_mode => l_acaiv_rec.attendance_mode,
1186 x_edu_goal_prior_enroll_id => l_acaiv_rec.edu_goal_prior_enroll_id,
1187 x_attendance_type => l_acaiv_rec.attendance_type,
1188 x_decision_make_id => l_acaiv_rec.decision_make_id,
1189 x_unit_set_cd => l_acaiv_rec.unit_set_cd,
1190 x_decision_date => l_acaiv_rec.decision_date,
1191 x_attribute_category => l_acaiv_rec.attribute_category,
1192 x_attribute1 => l_acaiv_rec.attribute1,
1193 x_attribute2 => l_acaiv_rec.attribute2,
1194 x_attribute3 => l_acaiv_rec.attribute3,
1195 x_attribute4 => l_acaiv_rec.attribute4,
1196 x_attribute5 => l_acaiv_rec.attribute5,
1197 x_attribute6 => l_acaiv_rec.attribute6,
1198 x_attribute7 => l_acaiv_rec.attribute7,
1199 x_attribute8 => l_acaiv_rec.attribute8,
1200 x_attribute9 => l_acaiv_rec.attribute9,
1201 x_attribute10 => l_acaiv_rec.attribute10,
1202 x_attribute11 => l_acaiv_rec.attribute11,
1203 x_attribute12 => l_acaiv_rec.attribute12,
1204 x_attribute13 => l_acaiv_rec.attribute13,
1205 x_attribute14 => l_acaiv_rec.attribute14,
1206 x_attribute15 => l_acaiv_rec.attribute15,
1207 x_attribute16 => l_acaiv_rec.attribute16,
1208 x_attribute17 => l_acaiv_rec.attribute17,
1209 x_attribute18 => l_acaiv_rec.attribute18,
1210 x_attribute19 => l_acaiv_rec.attribute19,
1211 x_attribute20 => l_acaiv_rec.attribute20,
1212 x_decision_reason_id => l_acaiv_rec.decision_reason_id,
1213 x_us_version_number => l_acaiv_rec.us_version_number,
1214 x_decision_notes => l_acaiv_rec.decision_notes,
1215 x_pending_reason_id => l_acaiv_rec.pending_reason_id,
1216 x_preference_number => l_acaiv_rec.preference_number,
1217 x_adm_doc_status => l_acaiv_rec.adm_doc_status,
1218 x_adm_entry_qual_status => l_acaiv_rec.adm_entry_qual_status,
1219 x_deficiency_in_prep => l_acaiv_rec.deficiency_in_prep,
1220 x_late_adm_fee_status => l_acaiv_rec.late_adm_fee_status,
1221 x_spl_consider_comments => l_acaiv_rec.spl_consider_comments,
1222 x_apply_for_finaid => l_acaiv_rec.apply_for_finaid,
1223 x_finaid_apply_date => l_acaiv_rec.finaid_apply_date,
1224 x_adm_outcome_status => l_acaiv_rec.adm_outcome_status,
1225 x_adm_otcm_stat_auth_per_id => l_acaiv_rec.adm_otcm_status_auth_person_id,
1226 x_adm_outcome_status_auth_dt => l_acaiv_rec.adm_outcome_status_auth_dt,
1227 x_adm_outcome_status_reason => l_acaiv_rec.adm_outcome_status_reason,
1228 x_offer_dt => l_acaiv_rec.offer_dt,
1229 x_offer_response_dt => l_acaiv_rec.offer_response_dt,
1230 x_prpsd_commencement_dt => NVL(t_prpsd_commencement_date (t_idx),l_acaiv_rec.prpsd_commencement_dt),
1231 x_adm_cndtnl_offer_status => l_acaiv_rec.adm_cndtnl_offer_status,
1232 x_cndtnl_offer_satisfied_dt => l_acaiv_rec.cndtnl_offer_satisfied_dt,
1233 x_cndnl_ofr_must_be_stsfd_ind => l_acaiv_rec.cndtnl_offer_must_be_stsfd_ind,
1234 x_adm_offer_resp_status => t_adm_offer_resp_status(t_idx), --From Interface
1235 x_actual_response_dt => TRUNC(l_calc_actual_ofr_resp_dt), --From Interface (Populated with SYSDATE if NULL in the Validate_off_resp_Dtls procedure)
1236 x_adm_offer_dfrmnt_status => l_adm_offer_defr_status, --Derived From Interface record
1237 x_deferred_adm_cal_type => t_def_adm_cal_type(t_idx), --From Interface
1238 x_deferred_adm_ci_sequence_num => t_def_adm_ci_sequence_number(t_idx), --From Interface
1239 x_deferred_tracking_id => NULL,
1240 x_ass_rank => l_acaiv_rec.ass_rank,
1241 x_secondary_ass_rank => l_acaiv_rec.secondary_ass_rank,
1242 x_intr_accept_advice_num => l_acaiv_rec.intrntnl_acceptance_advice_num,
1243 x_ass_tracking_id => l_acaiv_rec.ass_tracking_id,
1244 x_fee_cat => l_acaiv_rec.fee_cat,
1245 x_hecs_payment_option => l_acaiv_rec.hecs_payment_option,
1246 x_expected_completion_yr => l_acaiv_rec.expected_completion_yr,
1247 x_expected_completion_perd => l_acaiv_rec.expected_completion_perd,
1248 x_correspondence_cat => l_acaiv_rec.correspondence_cat,
1249 x_enrolment_cat => l_acaiv_rec.enrolment_cat,
1250 x_funding_source => l_acaiv_rec.funding_source,
1251 x_applicant_acptnce_cndtn => t_applicant_acptnce_cndtn(t_idx), --From Interface
1252 x_cndtnl_offer_cndtn => l_acaiv_rec.cndtnl_offer_cndtn,
1253 x_ss_application_id => NULL,
1254 x_ss_pwd => NULL ,
1255 x_authorized_dt => l_acaiv_rec.authorized_dt, --From Interface
1256 x_authorizing_pers_id => l_acaiv_rec.authorizing_pers_id,
1257 x_entry_status => l_acaiv_rec.entry_status,
1258 x_entry_level => l_acaiv_rec.entry_level,
1259 x_sch_apl_to_id => l_acaiv_rec.sch_apl_to_id,
1260 x_idx_calc_date => l_acaiv_rec.idx_calc_date,
1261 x_waitlist_status => l_acaiv_rec.waitlist_status,
1262 x_attribute21 => l_acaiv_rec.attribute21,
1263 x_attribute22 => l_acaiv_rec.attribute22,
1264 x_attribute23 => l_acaiv_rec.attribute23,
1265 x_attribute24 => l_acaiv_rec.attribute24,
1266 x_attribute25 => l_acaiv_rec.attribute25,
1267 x_attribute26 => l_acaiv_rec.attribute26,
1268 x_attribute27 => l_acaiv_rec.attribute27,
1269 x_attribute28 => l_acaiv_rec.attribute28,
1270 x_attribute29 => l_acaiv_rec.attribute29,
1271 x_attribute30 => l_acaiv_rec.attribute30,
1272 x_attribute31 => l_acaiv_rec.attribute31,
1273 x_attribute32 => l_acaiv_rec.attribute32,
1274 x_attribute33 => l_acaiv_rec.attribute33,
1275 x_attribute34 => l_acaiv_rec.attribute34,
1276 x_attribute35 => l_acaiv_rec.attribute35,
1277 x_attribute36 => l_acaiv_rec.attribute36,
1278 x_attribute37 => l_acaiv_rec.attribute37,
1279 x_attribute38 => l_acaiv_rec.attribute38,
1280 x_attribute39 => l_acaiv_rec.attribute39,
1281 x_attribute40 => l_acaiv_rec.attribute40,
1282 x_fut_acad_cal_type => l_acaiv_rec.future_acad_cal_type,
1283 x_fut_acad_ci_sequence_number => l_acaiv_rec.future_acad_ci_sequence_number,
1284 x_fut_adm_cal_type => l_acaiv_rec.future_adm_cal_type,
1285 x_fut_adm_ci_sequence_number => l_acaiv_rec.future_adm_ci_sequence_number,
1286 x_prev_term_adm_appl_number => l_acaiv_rec.previous_term_adm_appl_number,
1287 x_prev_term_sequence_number => l_acaiv_rec.previous_term_sequence_number,
1288 x_fut_term_adm_appl_number => l_acaiv_rec.future_term_adm_appl_number,
1289 x_fut_term_sequence_number => l_acaiv_rec.future_term_sequence_number,
1290 x_def_acad_cal_type => t_def_acad_cal_type(t_idx), --From Interface
1291 x_def_acad_ci_sequence_num => t_def_acad_ci_sequence_number(t_idx), --From Interface
1292 x_def_prev_term_adm_appl_num => NULL,
1293 x_def_prev_appl_sequence_num => NULL,
1294 x_def_term_adm_appl_num => NULL,
1295 x_def_appl_sequence_num => NULL,
1296 x_decline_ofr_reason => t_decline_ofr_reason(t_idx) --arvsrini igsm
1297 );
1298
1299 l_completed_flag := 'Y';
1300 END IF;
1301 EXCEPTION --Update operation failed
1302 WHEN OTHERS THEN
1303 ROLLBACK TO current_rec_savepoint;
1304 l_completed_flag := 'N';
1305 UPDATE igs_ad_offresp_int SET status = '3' WHERE offresp_int_id = t_offresp_int_id(t_idx);
1306 insert_int_error(t_offresp_int_id (t_idx),'E621',NULL);
1307
1308 logdetail(t_offresp_int_id(t_idx), 'E621', NULL,'imp_off_resp: igs_ad_ps_appl_inst_pkg.update_row',NULL);
1309
1310 --Loop through all messages in stack to check if there is Security Policy exception already set or not. Ref: Bug 3919112
1311 l_sc_msg_count := IGS_GE_MSG_STACK.COUNT_MSG;
1312 WHILE l_sc_msg_count <> 0 loop
1313 igs_ge_msg_stack.get(l_sc_msg_count, 'T', l_sc_encoded_text, l_sc_msg_index);
1314 fnd_message.parse_encoded(l_sc_encoded_text, l_sc_app_short_name, l_sc_message_name);
1315 IF l_sc_message_name = 'IGS_SC_POLICY_EXCEPTION' OR l_sc_message_name = 'IGS_SC_POLICY_UPD_DEL_EXCEP' THEN
1316 --print the the security exception in log file.
1317 fnd_message.set_encoded(l_sc_encoded_text);
1318 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
1319 EXIT;
1320 END IF;
1321 l_sc_msg_count := l_sc_msg_count - 1;
1322 END LOOP;
1323
1324 END; --of igs_ad_ps_appl_inst_pkg.update_row
1325 END IF;
1326
1327 -- Else Insert proper Error message into the Error Interface table.
1328 -- Update the Current Interface Offer Response record with Status '3'.
1329
1330 IF l_completed_flag = 'Y' THEN
1331 IF igs_ad_gen_008.admp_get_saors(t_adm_offer_resp_status(t_idx)) = 'ACCEPTED' THEN
1332 -- Now the Record is updated successfully then call the Pre Enrollment Job with Confirm_ind and Eligibility_Ind parameters as 'Y', 'Y' respectively.
1333 IF igs_ad_upd_initialise.perform_pre_enrol(
1334 l_acaiv_rec.person_id,
1335 l_acaiv_rec.admission_appl_number,
1336 l_acaiv_rec.nominated_course_cd,
1337 l_acaiv_rec.sequence_number,
1338 'Y', -- Confirm course indicator.
1339 'Y', -- Perform eligibility check indicator.
1340 v_message_name) = FALSE THEN
1341 ROLLBACK TO current_rec_savepoint;
1342 -- Update the Status of interface record and insert the error Code / Message text into Error table igs_ad_offresp_int.
1343 l_completed_flag := 'N';
1344 l_pre_enrol_success := 'N';
1345 insert_int_error(t_offresp_int_id (t_idx),NULL,v_message_name);
1346 logdetail(t_offresp_int_id(t_idx), NULL, v_message_name,'imp_off_resp: IF igs_ad_upd_initialise.perform_pre_enrol',NULL);
1347 UPDATE igs_ad_offresp_int SET status = '3' WHERE offresp_int_id = t_offresp_int_id(t_idx);
1348 END IF; -- Of call to Pre Enrollment job
1349 END IF;
1350
1351
1352 -- Pre enrollment job is successful so put the Successful record information in the Concurrent LOG.
1353 IF NVL(l_pre_enrol_success,'Y') = 'Y' THEN
1354 logdetail(t_offresp_int_id(t_idx), NULL, NULL,NULL,NULL);
1355 -- Delete the succesfully imported Interface record from the corresponding interface table.
1356 DELETE igs_ad_offresp_int WHERE offresp_int_id = t_offresp_int_id (t_idx);
1357 END IF;
1358
1359 END IF; --End of l_completed_flag = 'Y'
1360
1361
1362 ELSE --Application instance is not found in the OSS System table.
1363 insert_int_error(t_offresp_int_id (t_idx),'E605',NULL);
1364 UPDATE igs_ad_offresp_int SET status = '3' WHERE offresp_int_id = t_offresp_int_id(t_idx);
1365 logdetail(t_offresp_int_id (t_idx), 'E605', NULL, 'imp_off_resp: IF acaiv_rec.person_id' ,NULL);
1366 END IF;
1367 CLOSE cur_ad_ps_appl_inst;
1368
1369 l_tot_rec_processed := l_tot_rec_processed+1;
1370 -- For every 200 records processed, issue a COMMIT here.
1371 IF l_tot_rec_processed > 200 THEN
1372 COMMIT;
1373 l_tot_rec_processed := 0;
1374 END IF;
1375
1376 END LOOP; -- All the Pending records are processed.
1377
1378 -- Check if all the interface records have successfully been imported. In this case delete the batch record from interface batch table.
1379 OPEN c_processed_recs(p_batch_id);
1380 FETCH c_processed_recs INTO l_offresp_id;
1381 IF c_processed_recs%NOTFOUND THEN
1382 DELETE igs_ad_offresp_batch where batch_id = p_batch_id;
1383 END IF;
1384 CLOSE c_processed_recs;
1385
1386 IF l_tot_rec_processed > 0 THEN -- Commit the records not yet committed above
1387 COMMIT;
1388 END IF;
1389
1390 --Invoke the Import Offer Response Error Report by submit the Concurrent Request.
1391 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1392 APPLICATION => 'IGS',
1393 PROGRAM => 'IGSADS21',
1394 DESCRIPTION => 'Import Offer Response Error Report',
1395 START_TIME => NULL,
1396 SUB_REQUEST => FALSE,
1397 ARGUMENT1 => p_batch_id ,
1398 ARGUMENT2 => CHR(0),
1399 ARGUMENT3 => NULL,
1400 ARGUMENT4 => NULL,
1401 ARGUMENT5 => NULL,
1402 ARGUMENT6 => NULL,
1403 ARGUMENT7 => NULL,
1404 ARGUMENT8 => NULL,
1405 ARGUMENT9 => NULL,
1406 ARGUMENT10 => NULL,
1407 ARGUMENT11 => NULL,
1408 ARGUMENT12 => NULL,
1409 ARGUMENT13 => NULL,
1410 ARGUMENT14 => NULL,
1411 ARGUMENT15 => NULL,
1412 ARGUMENT16 => NULL,
1413 ARGUMENT17 => NULL,
1414 ARGUMENT18 => NULL,
1415 ARGUMENT19 => NULL,
1416 ARGUMENT20 => NULL,
1417 ARGUMENT21 => NULL,
1418 ARGUMENT22 => NULL,
1419 ARGUMENT23 => NULL,
1420 ARGUMENT24 => NULL,
1421 ARGUMENT25 => NULL,
1422 ARGUMENT26 => NULL,
1423 ARGUMENT27 => NULL,
1424 ARGUMENT28 => NULL,
1425 ARGUMENT29 => NULL,
1426 ARGUMENT30 => NULL,
1427 ARGUMENT31 => NULL,
1428 ARGUMENT32 => NULL,
1429 ARGUMENT33 => NULL,
1430 ARGUMENT34 => NULL,
1431 ARGUMENT35 => NULL,
1432 ARGUMENT36 => NULL,
1433 ARGUMENT37 => NULL,
1434 ARGUMENT38 => NULL,
1435 ARGUMENT39 => NULL,
1436 ARGUMENT40 => NULL,
1437 ARGUMENT41 => NULL,
1438 ARGUMENT42 => NULL,
1439 ARGUMENT43 => NULL,
1440 ARGUMENT44 => NULL,
1441 ARGUMENT45 => NULL,
1442 ARGUMENT46 => NULL,
1443 ARGUMENT47 => NULL,
1444 ARGUMENT48 => NULL,
1445 ARGUMENT49 => NULL,
1446 ARGUMENT50 => NULL,
1447 ARGUMENT51 => NULL,
1448 ARGUMENT52 => NULL,
1449 ARGUMENT53 => NULL,
1450 ARGUMENT54 => NULL,
1451 ARGUMENT55 => NULL,
1452 ARGUMENT56 => NULL,
1453 ARGUMENT57 => NULL,
1454 ARGUMENT58 => NULL,
1455 ARGUMENT59 => NULL,
1456 ARGUMENT60 => NULL,
1457 ARGUMENT61 => NULL,
1458 ARGUMENT62 => NULL,
1459 ARGUMENT63 => NULL,
1460 ARGUMENT64 => NULL,
1461 ARGUMENT65 => NULL,
1462 ARGUMENT66 => NULL,
1463 ARGUMENT67 => NULL,
1464 ARGUMENT68 => NULL,
1465 ARGUMENT69 => NULL,
1466 ARGUMENT70 => NULL,
1467 ARGUMENT71 => NULL,
1468 ARGUMENT72 => NULL,
1469 ARGUMENT73 => NULL,
1470 ARGUMENT74 => NULL,
1471 ARGUMENT75 => NULL,
1472 ARGUMENT76 => NULL,
1473 ARGUMENT77 => NULL,
1474 ARGUMENT78 => NULL,
1475 ARGUMENT79 => NULL,
1476 ARGUMENT80 => NULL,
1477 ARGUMENT81 => NULL,
1478 ARGUMENT82 => NULL,
1479 ARGUMENT83 => NULL,
1480 ARGUMENT84 => NULL,
1481 ARGUMENT85 => NULL,
1482 ARGUMENT86 => NULL,
1483 ARGUMENT87 => NULL,
1484 ARGUMENT88 => NULL,
1485 ARGUMENT89 => NULL,
1486 ARGUMENT90 => NULL,
1487 ARGUMENT91 => NULL,
1488 ARGUMENT92 => NULL,
1489 ARGUMENT93 => NULL,
1490 ARGUMENT94 => NULL,
1491 ARGUMENT95 => NULL,
1492 ARGUMENT96 => NULL,
1493 ARGUMENT97 => NULL,
1494 ARGUMENT98 => NULL,
1495 ARGUMENT99 => NULL,
1496 ARGUMENT100 => NULL
1497 );
1498
1499 -- Job is invoked, so Display the Cocnurrent Job request Request ID details in the LOG file of the Import of Offer Response job.
1500 OPEN get_conc_desctiption('IGSADS21');
1501 FETCH get_conc_desctiption INTO l_conc_Description;
1502 CLOSE get_conc_desctiption;
1503
1504 l_space_string :=' ';
1505 FND_FILE.PUT_LINE(FND_FILE.LOG, l_space_string);
1506
1507 FND_MESSAGE.SET_NAME('FND','CONC-SUBMITTED REQUEST');
1508 FND_MESSAGE.SET_TOKEN('REQUEST_ID',IGS_GE_NUMBER.TO_CANN(l_request_id), FALSE);
1509 FND_FILE.PUT_LINE(FND_FILE.LOG,l_conc_Description||' '||FND_MESSAGE.GET);
1510
1511 EXCEPTION --raised in the Concurrent job - Import Offer Response error report
1512 WHEN OTHERS THEN
1513 IF int_off_resp_cur%ISOPEN THEN
1514 CLOSE int_off_resp_cur;
1515 END IF;
1516 IF int_record_status%ISOPEN THEN
1517 CLOSE int_record_status;
1518 END IF;
1519 IF c_adm_appl_dtl%ISOPEN THEN
1520 CLOSE c_adm_appl_dtl;
1521 END IF;
1522 IF c_processed_recs%ISOPEN THEN
1523 CLOSE c_processed_recs;
1524 END IF;
1525 IF get_conc_desctiption%ISOPEN THEN
1526 CLOSE get_conc_desctiption;
1527 END IF;
1528 retcode:=2;
1529 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' '||SQLERRM;
1530 FND_FILE.PUT_LINE(FND_FILE.LOG,'igs_ad_imp_off_resp_data.imp_off_resp: '||errbuf);
1531 Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL;
1532
1533 END imp_off_resp; -- End of the main procedure
1534
1535 END igs_ad_imp_off_resp_data; --End of Package