1 PACKAGE BODY igs_ad_ss_appl_fee_pkg AS
2 /* $Header: IGSADB7B.pls 120.6 2005/12/18 21:28:32 appldev ship $ */
3
4 /*************************************************************
5 Created By :rboddu
6 Date Created By : 31-DEC-2001
7 Purpose :2158524
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 (reverse chronological order - newest change first)
12 ***************************************************************/
13
14 PROCEDURE check_offer_resp_update(
15 p_person_id IN NUMBER,
16 p_admission_application_number IN NUMBER,
17 p_nominated_course_cd IN VARCHAR2,
18 p_sequence_number IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2 )
22 AS
23 /*************************************************************
24 Created By :samaresh
25 Date : 20-DEC-2001
26 Created By : Sandhya.Amaresh
27 Purpose : This api check if the offer response can be made
28 'Accepted' for the offer
29 Know limitations, enhancements or remarks
30 Change History
31 Who When What
32 ***************************************************************/
33 CURSOR c_appl(cp_person_id NUMBER,cp_admission_appl_number NUMBER) IS
34 SELECT *
35 FROM igs_ad_appl
36 WHERE person_id = cp_person_id
37 AND admission_appl_number = cp_admission_appl_number;
38
39 CURSOR c_appl_inst(cp_person_id NUMBER,cp_admission_appl_number NUMBER,cp_nominated_cd VARCHAR,cp_seq_number NUMBER) IS
40 SELECT *
41 FROM igs_ad_ps_appl_inst
42 WHERE person_id = cp_person_id
43 AND admission_appl_number = cp_admission_appl_number
44 AND nominated_course_cd = cp_nominated_cd
45 AND sequence_number = cp_seq_number;
46
47 c_appl_inst_rec c_appl_inst%ROWTYPE;
48 c_appl_rec c_appl%ROWTYPE;
49
50 invalidsysstatus EXCEPTION;
51 applnotfound EXCEPTION;
52 applinstnotfound EXCEPTION;
53
54 l_offer_resp_outcome_status IGS_AD_PS_APPL_INST_ALL.adm_outcome_status%TYPE;
55
56 l_apcs_pref_limit_ind VARCHAR2(1);
57 l_apcs_app_fee_ind VARCHAR2(1);
58 l_apcs_late_app_ind VARCHAR2(1);
59 l_apcs_late_fee_ind VARCHAR2(1);
60 l_apcs_chkpencumb_ind VARCHAR2(1);
61 l_apcs_fee_assess_ind VARCHAR2(1);
62 l_apcs_corcategry_ind VARCHAR2(1);
63 l_apcs_enrcategry_ind VARCHAR2(1);
64 l_apcs_chkcencumb_ind VARCHAR2(1);
65 l_apcs_unit_set_ind VARCHAR2(1);
66 l_apcs_un_crs_us_ind VARCHAR2(1);
67 l_apcs_chkuencumb_ind VARCHAR2(1);
68 l_apcs_unit_restr_ind VARCHAR2(1);
69 l_apcs_unit_restriction_num IGS_AD_PRCS_CAT_STEP.STEP_ORDER_NUM%TYPE;
70 l_apcs_un_dob_ind VARCHAR2(1);
71 l_apcs_un_title_ind VARCHAR2(1);
72 l_apcs_asses_cond_ind VARCHAR2(1);
73 l_apcs_fee_cond_ind VARCHAR2(1);
74 l_apcs_doc_cond_ind VARCHAR2(1);
75 l_apcs_multi_off_ind VARCHAR2(1);
76 l_apcs_multi_off_restn_num IGS_AD_PRCS_CAT_STEP.STEP_ORDER_NUM%TYPE;
77 l_apcs_set_otcome_ind VARCHAR2(1);
78 l_apcs_override_o_ind VARCHAR2(1);
79 l_apcs_defer_ind VARCHAR2(1);
80 l_apcs_ack_app_ind VARCHAR2(1);
81 l_apcs_outcome_lt_ind VARCHAR2(1);
82 l_apcs_pre_enrol_ind VARCHAR2(1);
83
84 BEGIN
85
86
87 l_offer_resp_outcome_status := IGS_AD_GEN_009.ADMP_GET_SYS_AORS('ACCEPTED');
88 IF l_offer_resp_outcome_status IS NULL THEN
89 RAISE invalidsysstatus;
90 END IF;
91
92 OPEN c_appl(p_person_id,p_admission_application_number);
93 FETCH c_appl INTO c_appl_rec;
94 IF c_appl%NOTFOUND THEN
95 RAISE applnotfound;
96 END IF;
97 CLOSE c_appl;
98
99 OPEN c_appl_inst(p_person_id,p_admission_application_number,p_nominated_course_cd,p_sequence_number);
100 FETCH c_appl_inst INTO c_appl_inst_rec;
101 IF c_appl_inst%NOTFOUND THEN
102 RAISE applinstnotfound;
103 END IF;
104 CLOSE c_appl_inst;
105
106 IGS_AD_GEN_004.ADMP_GET_APCS_VAL(
107 c_appl_rec.admission_cat,
108 c_appl_rec.s_admission_process_type,
109 l_apcs_pref_limit_ind,
110 l_apcs_app_fee_ind,
111 l_apcs_late_app_ind,
112 l_apcs_late_fee_ind,
113 l_apcs_chkpencumb_ind,
114 l_apcs_fee_assess_ind,
115 l_apcs_corcategry_ind,
116 l_apcs_enrcategry_ind,
117 l_apcs_chkcencumb_ind,
118 l_apcs_unit_set_ind,
119 l_apcs_un_crs_us_ind,
120 l_apcs_chkuencumb_ind,
121 l_apcs_unit_restr_ind,
122 l_apcs_unit_restriction_num,
123 l_apcs_un_dob_ind,
124 l_apcs_un_title_ind,
125 l_apcs_asses_cond_ind,
126 l_apcs_fee_cond_ind,
127 l_apcs_doc_cond_ind,
128 l_apcs_multi_off_ind,
129 l_apcs_multi_off_restn_num,
130 l_apcs_set_otcome_ind,
131 l_apcs_override_o_ind,
132 l_apcs_defer_ind,
133 l_apcs_ack_app_ind,
134 l_apcs_outcome_lt_ind,
135 l_apcs_pre_enrol_ind);
136
137 IF igs_ad_val_acai_status.admp_val_aors_item(
138 p_person_id,
139 p_admission_application_number,
140 p_nominated_course_cd,
141 p_sequence_number,
142 p_nominated_course_cd,
143 l_offer_resp_outcome_status,
144 SYSDATE,
145 c_appl_rec.s_admission_process_type,
146 l_apcs_defer_ind,
147 l_apcs_pre_enrol_ind,
148 x_msg_data,
149 c_appl_inst_rec.decline_ofr_reason , -- IGSM
150 c_appl_inst_rec.attent_other_inst_cd -- igsm
151 ) THEN
152
153 -- Call the following Api to validate the offer response status at record level
154 IF igs_ad_val_acai_status.admp_val_acai_aors(
155 p_person_id,
156 p_admission_application_number,
157 p_nominated_course_cd,
158 p_sequence_number,
159 p_nominated_course_cd,
160 l_offer_resp_outcome_status,
161 c_appl_inst_rec.adm_offer_resp_status,
162 c_appl_inst_rec.adm_outcome_status,
163 c_appl_inst_rec.adm_offer_dfrmnt_status,
164 c_appl_inst_rec.adm_offer_dfrmnt_status,
165 c_appl_inst_rec.adm_outcome_status_auth_dt,
166 SYSDATE,
167 c_appl_inst_rec.adm_cal_type,
168 c_appl_inst_rec.adm_ci_sequence_number,
169 c_appl_rec.admission_cat,
170 c_appl_rec.s_admission_process_type,
171 l_apcs_defer_ind,
172 l_apcs_multi_off_ind,
173 l_apcs_multi_off_restn_num,
174 l_apcs_pre_enrol_ind,
175 c_appl_inst_rec.cndtnl_offer_must_be_stsfd_ind,
176 c_appl_inst_rec.cndtnl_offer_satisfied_dt,
177 'FORM',
178 x_msg_data,
179 c_appl_inst_rec.decline_ofr_reason , -- IGSM
180 c_appl_inst_rec.attent_other_inst_cd -- igsm
181 ) THEN
182 x_return_status := 'S';
183 x_msg_count := 0;
184 x_msg_data := NULL;
185 RETURN;
186 ELSE
187 x_return_status := 'E';
188 x_msg_count := 0;
189 RETURN;
190 END IF;
191 ELSE
192 x_return_status := 'E';
193 x_msg_count := 0;
194 RETURN;
195 END IF;
196 EXCEPTION
197 WHEN invalidsysstatus THEN
198 x_return_status := 'E';
199 x_msg_data := 'IGS_AD_INVALID_SYSTEM_TYPE';
200 x_msg_count := 0;
201 RETURN;
202 WHEN applnotfound THEN
203 CLOSE c_appl;
204 x_return_status := 'E';
205 x_msg_data := 'IGS_AD_INVALID_APPL';
206 x_msg_count := 0;
207 RETURN;
208 WHEN applinstnotfound THEN
209 CLOSE c_appl_inst;
210 x_return_status := 'E';
211 x_msg_data := 'IGS_AD_INVALID_APPL';
212 x_msg_count := 0;
213 RETURN;
214 WHEN OTHERS THEN
215 x_return_status := 'E';
216 x_msg_data := 'IGS_GE_UNHANLED_EXP';
217 x_msg_count := 0;
218 RETURN;
219 END check_offer_resp_update;
220
221 PROCEDURE check_offer_update(
222 p_person_id IN NUMBER,
223 p_admission_application_number IN NUMBER,
224 p_nominated_course_cd IN VARCHAR2,
225 p_sequence_number IN NUMBER,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2 )
229 AS
230 /*************************************************************
231 Created By :samaresh
232 Date : 20-DEC-2001
233 Created By : Sandhya.Amaresh
234 Purpose : This api check if an offer can be made for the
235 application
236 Know limitations, enhancements or remarks
237 Change History
238 Who When What
239 ***************************************************************/
240
241 CURSOR c_appl(cp_person_id NUMBER,cp_admission_appl_number NUMBER) IS
242 SELECT *
243 FROM igs_ad_appl
244 WHERE person_id = cp_person_id
245 AND admission_appl_number = cp_admission_appl_number;
246
247 CURSOR c_appl_inst(cp_person_id NUMBER,cp_admission_appl_number NUMBER,cp_nominated_cd VARCHAR,cp_seq_number NUMBER) IS
248 SELECT *
249 FROM igs_ad_ps_appl_inst
250 WHERE person_id = cp_person_id
251 AND admission_appl_number = cp_admission_appl_number
252 AND nominated_course_cd = cp_nominated_cd
253 AND sequence_number = cp_seq_number;
254
255 c_appl_inst_rec c_appl_inst%ROWTYPE;
256 c_appl_rec c_appl%ROWTYPE;
257
258 invalidsysstatus EXCEPTION;
259 applnotfound EXCEPTION;
260 applinstnotfound EXCEPTION;
261
262 l_offer_adm_outcome_status IGS_AD_PS_APPL_INST_ALL.adm_outcome_status%TYPE;
263 l_old_adm_outcome_status IGS_AD_PS_APPL_INST_ALL.adm_outcome_status%TYPE;
264
265 l_offer_resp_status IGS_AD_PS_APPL_INST_ALL.adm_offer_resp_status%TYPE;
266 l_offer_old_resp_status IGS_AD_PS_APPL_INST_ALL.adm_offer_resp_status%TYPE;
267
268 l_apcs_pref_limit_ind VARCHAR2(1);
269 l_apcs_app_fee_ind VARCHAR2(1);
270 l_apcs_late_app_ind VARCHAR2(1);
271 l_apcs_late_fee_ind VARCHAR2(1);
272 l_apcs_chkpencumb_ind VARCHAR2(1);
273 l_apcs_fee_assess_ind VARCHAR2(1);
274 l_apcs_corcategry_ind VARCHAR2(1);
275 l_apcs_enrcategry_ind VARCHAR2(1);
276 l_apcs_chkcencumb_ind VARCHAR2(1);
277 l_apcs_unit_set_ind VARCHAR2(1);
278 l_apcs_un_crs_us_ind VARCHAR2(1);
279 l_apcs_chkuencumb_ind VARCHAR2(1);
280 l_apcs_unit_restr_ind VARCHAR2(1);
281 l_apcs_unit_restriction_num IGS_AD_PRCS_CAT_STEP.STEP_ORDER_NUM%TYPE;
282 l_apcs_un_dob_ind VARCHAR2(1);
283 l_apcs_un_title_ind VARCHAR2(1);
284 l_apcs_asses_cond_ind VARCHAR2(1);
285 l_apcs_fee_cond_ind VARCHAR2(1);
286 l_apcs_doc_cond_ind VARCHAR2(1);
287 l_apcs_multi_off_ind VARCHAR2(1);
288 l_apcs_multi_off_restn_num IGS_AD_PRCS_CAT_STEP.STEP_ORDER_NUM%TYPE;
289 l_apcs_set_otcome_ind VARCHAR2(1);
290 l_apcs_override_o_ind VARCHAR2(1);
291 l_apcs_defer_ind VARCHAR2(1);
292 l_apcs_ack_app_ind VARCHAR2(1);
293 l_apcs_outcome_lt_ind VARCHAR2(1);
294 l_apcs_pre_enrol_ind VARCHAR2(1);
295
296 BEGIN
297
298 l_offer_adm_outcome_status := IGS_AD_GEN_009.ADMP_GET_SYS_AOS('OFFER');
299 l_old_adm_outcome_status := IGS_AD_GEN_009.ADMP_GET_SYS_AOS('PENDING');
300
301 l_offer_resp_status := IGS_AD_GEN_009.ADMP_GET_SYS_AORS('PENDING');
302 l_offer_old_resp_status := IGS_AD_GEN_009.ADMP_GET_SYS_AORS('NOT-APPLIC');
303
304 IF l_offer_adm_outcome_status IS NULL OR l_old_adm_outcome_status IS NULL THEN
305 RAISE invalidsysstatus;
306 END IF;
307
308 IF l_offer_resp_status IS NULL OR l_offer_old_resp_status IS NULL THEN
309 RAISE invalidsysstatus;
310 END IF;
311
312 OPEN c_appl(p_person_id,p_admission_application_number);
313 FETCH c_appl INTO c_appl_rec;
314 IF c_appl%NOTFOUND THEN
315 RAISE applnotfound;
316 END IF;
317 CLOSE c_appl;
318
319 OPEN c_appl_inst(p_person_id,p_admission_application_number,p_nominated_course_cd,p_sequence_number);
320 FETCH c_appl_inst INTO c_appl_inst_rec;
321 IF c_appl_inst%NOTFOUND THEN
322 RAISE applinstnotfound;
323 END IF;
324 CLOSE c_appl_inst;
325
326
327 IGS_AD_GEN_004.ADMP_GET_APCS_VAL(
328 c_appl_rec.admission_cat,
329 c_appl_rec.s_admission_process_type,
330 l_apcs_pref_limit_ind,
331 l_apcs_app_fee_ind,
332 l_apcs_late_app_ind,
333 l_apcs_late_fee_ind,
334 l_apcs_chkpencumb_ind,
335 l_apcs_fee_assess_ind,
336 l_apcs_corcategry_ind,
337 l_apcs_enrcategry_ind,
338 l_apcs_chkcencumb_ind,
339 l_apcs_unit_set_ind,
340 l_apcs_un_crs_us_ind,
341 l_apcs_chkuencumb_ind,
342 l_apcs_unit_restr_ind,
343 l_apcs_unit_restriction_num,
344 l_apcs_un_dob_ind,
345 l_apcs_un_title_ind,
346 l_apcs_asses_cond_ind,
347 l_apcs_fee_cond_ind,
348 l_apcs_doc_cond_ind,
349 l_apcs_multi_off_ind,
350 l_apcs_multi_off_restn_num,
351 l_apcs_set_otcome_ind,
352 l_apcs_override_o_ind,
353 l_apcs_defer_ind,
354 l_apcs_ack_app_ind,
355 l_apcs_outcome_lt_ind,
356 l_apcs_pre_enrol_ind);
357
358 IF IGS_AD_VAL_ACAI_STATUS.admp_val_acai_aos(
359 p_person_id,
360 p_admission_application_number,
361 p_nominated_course_cd,
362 p_sequence_number,
363 p_nominated_course_cd,
364 c_appl_inst_rec.crv_version_number,
365 c_appl_inst_rec.location_cd,
366 c_appl_inst_rec.attendance_mode,
367 c_appl_inst_rec.attendance_type,
368 c_appl_inst_rec.unit_set_cd,
369 c_appl_inst_rec.us_version_number,
370 c_appl_rec.acad_cal_type,
371 c_appl_rec.acad_ci_sequence_number,
372 c_appl_inst_rec.adm_cal_type,
373 c_appl_inst_rec.adm_ci_sequence_number,
374 c_appl_rec.admission_cat,
375 c_appl_rec.s_admission_process_type,
376 c_appl_rec.appl_dt,
377 c_appl_inst_rec.fee_cat,
378 c_appl_inst_rec.correspondence_cat,
379 c_appl_inst_rec.enrolment_cat,
380 l_offer_adm_outcome_status,
381 l_old_adm_outcome_status,
382 c_appl_inst_rec.adm_doc_status,
383 c_appl_rec.adm_fee_status,
384 c_appl_inst_rec.late_adm_fee_status,
385 c_appl_inst_rec.adm_cndtnl_offer_status,
386 c_appl_inst_rec.adm_entry_qual_status,
387 l_offer_resp_status,
388 l_offer_old_resp_status,
389 c_appl_inst_rec.adm_outcome_status_auth_dt,
390 l_apcs_set_otcome_ind,
391 'N',
392 'N',
393 'N',
394 l_apcs_late_app_ind,
395 l_apcs_app_fee_ind,
396 l_apcs_multi_off_ind,
397 l_apcs_multi_off_restn_num,
398 l_apcs_pref_limit_ind,
399 l_apcs_unit_set_ind,
400 l_apcs_chkpencumb_ind,
401 l_apcs_chkcencumb_ind,
402 'FORM',
403 x_msg_data) THEN
404 x_return_status := 'S';
405 x_msg_count := 0;
406 ELSE
407 x_return_status := 'E';
408 END IF;
409 EXCEPTION
410 WHEN invalidsysstatus THEN
411 x_return_status := 'E';
412 x_msg_data := 'IGS_AD_INVALID_SYSTEM_TYPE';
413 x_msg_count := 0;
414 RETURN;
415 WHEN applnotfound THEN
416 CLOSE c_appl;
417 x_return_status := 'E';
418 x_msg_data := 'IGS_AD_INVALID_APPL';
419 x_msg_count := 0;
420 RETURN;
421 WHEN APPLINSTNOTFOUND THEN
422 CLOSE c_appl_inst;
423 x_return_status := 'E';
424 x_msg_data := 'IGS_AD_INVALID_APPL';
425 x_msg_count := 0;
426 RETURN;
427 WHEN OTHERS THEN
428 x_return_status := 'E';
429 x_msg_data := 'IGS_GE_UNHANLED_EXP';
430 x_msg_count := 0;
431 RETURN;
432 END check_offer_update;
433
434 PROCEDURE check_update_aeps_acs(
435 p_person_id IN NUMBER,
436 p_admission_application_number IN NUMBER,
437 p_nominated_course_cd IN VARCHAR2,
438 p_sequence_number IN NUMBER,
439 x_return_status OUT NOCOPY VARCHAR2,
440 x_msg_count OUT NOCOPY NUMBER,
441 x_msg_data OUT NOCOPY VARCHAR2)
442 AS
443 /*************************************************************
444 Created By :rboddu
445 Date Created By : 31-DEC-2001
446 Purpose : 2158524
447 Know limitations, enhancements or remarks
448 Change History
449 Who When What
450 (reverse chronological order - newest change first)
451 ***************************************************************/
452 --Cursor which retrieves the system admission process type associated with the application.
453 CURSOR c_appl_cur IS
454 SELECT s_admission_process_type, admission_cat
455 FROM igs_ad_appl
456 WHERE person_id = p_person_id AND
457 admission_appl_number = p_admission_application_number;
458
459 CURSOR c_trk_exists(p_adm_cat VARCHAR2, p_adm_prc_typ VARCHAR2) IS
460 SELECT 1 FROM IGS_AD_PRCS_CAT_STEP APCS , IGS_TR_TYPE_ALL TRT
461 WHERE
462 APCS.S_ADMISSION_STEP_TYPE = TRT.TRACKING_TYPE AND
463 APCS.STEP_GROUP_TYPE = 'TRACK' AND
464 TRT.S_TRACKING_TYPE IS NOT NULL
465 AND admission_cat = p_adm_cat
466 AND s_admission_process_type = p_adm_prc_typ;
467
468 c_appl_rec c_appl_cur%ROWTYPE;
469
470 l_trk_exists number;
471 l_adm_cat igs_ad_appl.ADMISSION_CAT%TYPE;
472 l_adm_proc_type igs_ad_appl.S_ADMISSION_PROCESS_TYPE%TYPE;
473 lvc_user_application_status VARCHAR2(30);
474 lvc_user_entry_qual_status VARCHAR2(30);
475 lvc_user_outcome_status VARCHAR2(30);
476 lvc_user_conditional_status VARCHAR2(30);
477
478 BEGIN
479 --Get the user defined Statuses for the corresponding system defined Statuses
480 lvc_user_application_status := igs_ad_gen_009.admp_get_sys_ads('SATISFIED');
481 lvc_user_entry_qual_status := igs_ad_gen_009.admp_get_sys_aeqs('QUALIFIED');
482 lvc_user_outcome_status := igs_ad_gen_009.admp_get_sys_aos('PENDING');
483 lvc_user_conditional_status := igs_ad_gen_009.admp_get_sys_acos('NOT-APPLIC');
484
485 --If any of User defined Status is NULL copy the error message into x_msg_data and set the corresponding x_return_status to 'E'
486 IF ((lvc_user_application_status IS NULL) OR (lvc_user_entry_qual_status IS NULL) OR
487 (lvc_user_outcome_status IS NULL) OR (lvc_user_conditional_status IS NULL)) THEN
488 x_msg_data := 'IGS_AD_INVALID_SYSTEM_TYPE';
489 x_return_status:= 'E';
490 x_msg_count:=0;
491 RETURN;
492 END IF;
493
494 OPEN c_appl_cur;
495 FETCH c_appl_cur INTO c_appl_rec;
496 l_adm_cat := c_appl_rec.admission_cat;
497 l_adm_proc_type := c_appl_rec.s_admission_process_type;
498 IF c_appl_cur%NOTFOUND THEN
499 x_return_status := 'E';
500 x_msg_data:='IGS_AD_INVALID_APPL';
501 x_msg_count :=0;
502 CLOSE c_appl_cur;
503 RETURN;
504 END IF;
505 CLOSE c_appl_cur;
506
507 -- hreddych 3419856 For a NON-AWARD Appl Type the Entry Qual Status and Appl Comp Status
508 -- should be NOT-APPLIC
509 IF l_adm_proc_type = 'NON-AWARD' THEN
510 lvc_user_application_status := igs_ad_gen_009.admp_get_sys_ads('NOT-APPLIC');
511 lvc_user_entry_qual_status := igs_ad_gen_009.admp_get_sys_aeqs('NOT-APPLIC');
512 END IF;
513
514 --Check whether the Admission Entry qualification status be updated
515 --to system admission entry qualification
516 --'QUALIFIED' by calling the following API. If API returns false
517 --then correspondingly update the x_return_status to 'E'. x_msg_data will contain the error message thrown by the API.
518 IF NOT (igs_ad_val_acai_status.admp_val_acai_aeqs(
519 lvc_user_entry_qual_status,
520 lvc_user_outcome_status,
521 c_appl_rec.s_admission_process_type,
522 x_msg_data)) THEN
523 x_return_status:='E';
524 x_msg_count :=0;
525 RETURN;
526 END IF;
527
528 --Check whether the Admission Application status can be updated to 'SATISFIED' by calling the following API. If API returns false
529 --then correspondingly update the x_return_status to 'E'. x_msg_data will contain the error message thrown by the API.
530
531 --Validate at Record level which internally validates the Item Level also.
532 --Capture the error message into x_msg_data if the validation returns FALSE.
533 IF NOT (igs_ad_val_acai_status.admp_val_acai_ads(
534 lvc_user_application_status,
535 lvc_user_outcome_status,
536 lvc_user_conditional_status,
537 c_appl_rec.s_admission_process_type,
538 'N',
539 x_msg_data)) THEN
540 x_return_status :='E';
541 x_msg_count :=0;
542 RETURN;
543 END IF;
544
545 -- Check whether the tracking type is assoicated for the APC
546 OPEN c_trk_exists(l_adm_cat, l_adm_proc_type);
547 FETCH c_trk_exists INTO l_trk_exists;
548
549 --IF l_trk_exists <> 0 THEN
550 IF c_trk_exists%FOUND THEN
551
552 --Check whether the tracking items are completed for the Application by calling the following API.
553 IF NOT (igs_ad_ac_comp.get_cmp_apltritm(
554 p_person_id,
555 p_admission_application_number,
556 p_nominated_course_cd,
557 p_sequence_number)) THEN
558 x_return_status:='E';
559 x_msg_count:=0;
560 x_msg_data:= 'IGS_AD_CNT_COM_APP';
561 RETURN;
562 END IF;
563
564 ELSE
565 x_return_status := 'S';
566 x_msg_data := NULL;
567 x_msg_count:=0;
568
569 END IF;
570 CLOSE c_trk_exists;
571
572 --Control reaches here if all the validations are successful. The x_return_Status is set to 'S' and x_msg_data tp 0
573 x_return_status := 'S';
574 x_msg_data := NULL;
575 x_msg_count:=0;
576
577 END check_update_aeps_acs;
578
579 PROCEDURE get_appl_type_fee_details(
580 p_person_id IN NUMBER,
581 p_admission_appl_number IN NUMBER,
582 appl_fee_amt OUT NOCOPY NUMBER,
583 revenue_acct_code OUT NOCOPY VARCHAR2,
584 cash_acct_code OUT NOCOPY VARCHAR2,
585 revenue_acct_ccid OUT NOCOPY NUMBER,
586 cash_acct_ccid OUT NOCOPY NUMBER,
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2)
590 AS
591 /**************************************************************
592 Created By :rboddu
593 modified to derive fee amount from igs_ad_appl_all - igsm - arvsrini
594 Date Created By : 31-DEC-2001
595 Purpose : 2158524
596 Know limitations, enhancements or remarks
597 Change History
598 Who When What
599 (reverse chronological order - newest change first)
600 ***************************************************************/
601
602
603 --Cursor which returns the admission_application_type associated with the passed admission_application_number
604 CURSOR get_appl_type_cur IS
605 SELECT application_type
606 FROM igs_ad_appl
607 WHERE person_id = p_person_id AND
608 admission_appl_number = p_admission_appl_number;
609
610 --Cursor to fetch the account details associated with the application_type into the OUT NOCOPY parameters
611 CURSOR get_account_details_cur(l_adm_application_type igs_ad_ss_appl_typ.admission_application_type%TYPE) IS
612 SELECT
613 gl_rev_acct_ccid,
614 gl_cash_acct_ccid,
615 rev_account_code,
616 cash_account_code
617 FROM igs_ad_ss_appl_typ
618 WHERE admission_application_type = l_adm_application_type;
619
620 -- Cursor to fetch the outstanding balance for the applicant --arvsrini
621 CURSOR c_appl_fee_amt IS
622 SELECT
623 (NVL(apl.appl_fee_amt,0)-sum(req.FEE_AMOUNT)) OutstandingBal
624 FROM
625 IGS_AD_APPL_ALL apl,
626 igs_ad_app_req req
627 WHERE
628 apl.person_id= p_person_id AND
629 apl.admission_appl_number = p_admission_appl_number AND
630 apl.person_id = req.person_id AND
631 apl.admission_appl_number= req.admission_appl_number AND
632 EXISTS (SELECT 'x'
633 FROM igs_ad_code_classes
634 WHERE class = 'SYS_FEE_TYPE'
635 AND system_status = 'APPL_FEE'
636 AND applicant_fee_type = code_id
637 AND CLASS_TYPE_CODE = 'ADM_CODE_CLASSES')
638 GROUP BY apl.appl_fee_amt;
639 --cursor to fetch the initial application fee amount
640 CURSOR c_appl_fee_appl IS
641 SELECT
642 NVL(apl.appl_fee_amt,0) appfee
643 FROM IGS_AD_APPL_ALL apl
644 WHERE
645 apl.person_id= p_person_id AND
646 apl.admission_appl_number = p_admission_appl_number;
647
648
649 l_application_type igs_ad_ss_appl_typ.admission_application_type%TYPE;
650
651 BEGIN
652 OPEN get_appl_type_cur;
653 FETCH get_appl_type_cur INTO l_application_type;
654
655 -- If application_type associated with the given person is not found then populate error message into out NOCOPY parameter x_msg_data.
656 IF l_application_type IS NULL THEN
657 x_return_status:= 'E';
658 x_msg_data := 'IGS_AD_NO_APPL_TYPE';
659 x_msg_count :=0;
660 appl_fee_amt := NULL;
661 revenue_acct_code := NULL;
662 cash_acct_code := NULL;
663 revenue_acct_ccid := NULL;
664 cash_acct_ccid := NULL;
665
666 CLOSE get_appl_type_cur;
667 RETURN;
668 ELSE
669 OPEN c_appl_fee_appl;
670 FETCH c_appl_fee_appl INTO appl_fee_amt;
671 IF (c_appl_fee_appl%NOTFOUND) THEN
672 appl_fee_amt:=0;
673 END IF;
674 CLOSE c_appl_fee_appl;
675
676 OPEN get_account_details_cur(l_application_type);
677 FETCH get_account_details_cur
678 INTO
679 revenue_acct_ccid,
680 cash_acct_ccid,
681 revenue_acct_code,
682 cash_acct_code;
683 CLOSE get_account_details_cur;
684
685 OPEN c_appl_fee_amt; -- IGS.M fee details derivation
686 FETCH c_appl_fee_amt INTO appl_fee_amt;
687
688 IF (c_appl_fee_amt%FOUND) THEN
689 IF appl_fee_amt < 0 THEN
690 appl_fee_amt:= 0;
691 END IF;
692 END IF;
693
694 CLOSE c_appl_fee_amt;
695
696 x_return_status := 'S';
697 x_msg_data := NULL;
698 x_msg_count := 0;
699 CLOSE get_appl_type_cur;
700 END IF;
701
702 IF(get_appl_type_cur%ISOPEN) THEN
703 CLOSE get_appl_type_cur;
704 END IF;
705
706 END get_appl_type_fee_details;
707
708 PROCEDURE upd_fee_details( p_person_id IN NUMBER,
709 p_admission_appl_number IN NUMBER,
710 p_app_fee_amt IN NUMBER,
711 p_authorization_number IN VARCHAR2,
712 p_sys_fee_status IN VARCHAR2,
713 p_sys_fee_type IN VARCHAR2,
714 p_sys_fee_method IN VARCHAR2,
715 x_return_status OUT NOCOPY VARCHAR2,
716 x_msg_count OUT NOCOPY NUMBER,
717 x_msg_data OUT NOCOPY VARCHAR2,
718 p_credit_card_code IN VARCHAR2,
719 p_credit_card_holder_name IN VARCHAR2,
720 p_credit_card_number IN VARCHAR2,
721 p_credit_card_expiration_date IN DATE,
722 p_gl_date IN DATE,
723 p_rev_gl_ccid IN NUMBER,
724 p_cash_gl_ccid IN NUMBER,
725 p_rev_account_cd IN VARCHAR2,
726 p_cash_account_cd IN VARCHAR2,
727 p_credit_card_tangible_cd IN VARCHAR2
728 ) AS
729 /*************************************************************
730 Created By :rboddu
731 Date Created By : 31-DEC-2001
732 Purpose : 2158524
733 Know limitations, enhancements or remarks
734 Change History
735 Who When What
736 (reverse chronological order - newest change first)
737 pathipat 14-Jun-2003 Enh 2831587 - Credit Card Fund Transfer build
738 Added new IN parameter, p_credit_card_tangible_cd
739 Modified call to igs_ad_app_req_pkg.insert_row() - added 3 new parameters
740 VVUTUKUR 26-NOV-2002 Enh#2584986.GL Interface Build. Added 9 new parameters to this procedure.
741 These additional attributes, i.e. credit card details, Accounting information and the GL_DATE are passed to the call to igs_ad_app_req_pkg.insert_row.
742 ***************************************************************/
743
744 --CURSOR which checks whether the application is a valid one
745 CURSOR is_valid_appl_cur(l_person_id IN NUMBER, l_admission_appl_number IN NUMBER) IS
746 SELECT person_id
747 FROM igs_ad_appl apai
748 WHERE apai.person_id = l_person_id AND
749 apai.admission_appl_number = l_admission_appl_number;
750
751 --CURSOR which retrieves the default System Status for the given System Fee Method
752 CURSOR def_pay_method_cur IS
753 SELECT code_id
754 FROM igs_ad_code_classes
755 WHERE system_status = p_sys_fee_method
756 AND system_default = 'Y'
757 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
758 -- removed the check whether there is already a transaction with the same Fee Type
759 --CURSOR which retrieves the default System Status for the given System Fee Status
760 CURSOR def_fee_status_cur IS
761 SELECT code_id
762 FROM igs_ad_code_classes
763 WHERE system_status = p_sys_fee_status
764 AND system_default = 'Y'
765 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
766
767 --CURSOR which retrieves the default System Status for the given System Fee Type
768 CURSOR def_fee_types_cur IS
769 SELECT code_id
770 FROM igs_ad_code_classes
771 WHERE system_status = p_sys_fee_type
772 AND system_default = 'Y'
773 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
774
775
776 l_default_pay_method igs_ad_code_classes.code_id%TYPE;
777 l_default_fee_status igs_ad_code_classes.code_id%TYPE;
778 l_default_fee_type igs_ad_code_classes.code_id%TYPE;
779 l_rowid VARCHAR2(100);
780 l_app_req_id NUMBER(15);
781 lv_person_id igs_ad_appl.person_id%TYPE;
782 lv_dup_person VARCHAR2(10);
783 l_message VARCHAR2(100);
784 invalidamount EXCEPTION;
785 invalidappl EXCEPTION;
786 sysinvalid EXCEPTION;
787
788 BEGIN
789 --If passed fee amount is not positive then abort the process by raising error
790 IF p_app_fee_amt <= 0 THEN
791 RAISE invalidamount;
792 END IF;
793
794 -- If application is invalid then abort the process by raising error
795 OPEN is_valid_appl_cur(p_person_id,p_admission_appl_number);
796 FETCH is_valid_appl_cur INTO lv_person_id;
797 IF is_valid_appl_cur%NOTFOUND THEN
798 RAISE invalidappl;
799 ELSE
800 --Get the default payment method associated with the given System Payment method
801 OPEN def_pay_method_cur;
802 FETCH def_pay_method_cur INTO l_default_pay_method;
803 IF def_pay_method_cur%NOTFOUND THEN
804 CLOSE def_pay_method_cur;
805 RAISE sysinvalid;
806 END IF;
807 CLOSE def_pay_method_cur;
808 --Get the default fee status associated with the given system Fee Status
809 OPEN def_fee_status_cur;
810 FETCH def_fee_status_cur INTO l_default_fee_status;
811 IF def_fee_status_cur%NOTFOUND THEN
812 CLOSE def_fee_status_cur;
813 RAISE sysinvalid;
814 END IF;
815 CLOSE def_fee_status_cur;
816 --Get the default Fee Status associated with the given System Fee Type
817 OPEN def_fee_types_cur;
818 FETCH def_fee_types_cur INTO l_default_fee_type;
819
820 IF def_fee_types_cur%NOTFOUND THEN
821 CLOSE def_fee_types_cur;
822 RAISE sysinvalid;
823 END IF;
824 CLOSE def_fee_types_cur;
825
826 -- Removed the existing check that disallowed multiple payments for the same fee type as a part of IGS.M
827
828 igs_ad_app_req_pkg.insert_row(
829 X_ROWID => l_rowid,
830 X_APP_REQ_ID => l_app_req_id,
831 X_PERSON_ID => p_person_id,
832 X_ADMISSION_APPL_NUMBER => p_admission_appl_number,
833 X_APPLICANT_FEE_TYPE => l_default_fee_type,
834 X_APPLICANT_FEE_STATUS => l_default_fee_status,
835 X_FEE_DATE => TRUNC(SYSDATE),
836 X_FEE_PAYMENT_METHOD => l_default_pay_method,
837 X_FEE_AMOUNT => p_app_fee_amt,
838 X_REFERENCE_NUM => p_authorization_number,
839 X_CREDIT_CARD_CODE => p_credit_card_code,
840 X_CREDIT_CARD_HOLDER_NAME => p_credit_card_holder_name,
841 X_CREDIT_CARD_NUMBER => p_credit_card_number,
842 X_CREDIT_CARD_EXPIRATION_DATE => p_credit_card_expiration_date,
843 X_REV_GL_CCID => p_rev_gl_ccid,
844 X_CASH_GL_CCID => p_cash_gl_ccid,
845 X_REV_ACCOUNT_CD => p_rev_account_cd,
846 X_CASH_ACCOUNT_CD => p_cash_account_cd,
847 X_GL_DATE => p_gl_date,
848 X_GL_POSTED_DATE => NULL,
849 X_POSTING_CONTROL_ID => NULL,
850 x_credit_card_tangible_cd => p_credit_card_tangible_cd,
851 x_credit_card_payee_cd => fnd_profile.value('IGS_FI_PAYEE_NAME'),
852 x_credit_card_status_code => 'PENDING'
853 );
854 END IF;
855
856 -- All the validations are successfull and insertion of record into igs_ad_app_req is successfull.
857 x_return_status := 'S';
858 x_msg_count:=0;
859 x_msg_data :=NULL;
860
861 EXCEPTION
862 WHEN invalidamount THEN
863 x_return_status := 'E';
864 x_msg_count:=0;
865 x_msg_data :='IGS_AD_FEE_AMT_NON_NEGATIVE';
866 RETURN;
867
868 WHEN invalidappl THEN
869 x_return_status := 'E';
870 x_msg_count:=0;
871 x_msg_data :='IGS_AD_INVALID_APPL';
872 RETURN;
873
874 WHEN sysinvalid THEN
875 x_return_status := 'E';
876 x_msg_count:=0;
877 x_msg_data :='IGS_AD_INVALID_SYSTEM_TYPE';
878 RETURN;
879
880
881 WHEN OTHERS THEN
882 x_return_status := 'E';
883 x_msg_count:=0;
884 x_msg_data :=sqlerrm;
885 RETURN;
886
887 END upd_fee_details;
888
889 END igs_ad_ss_appl_fee_pkg;
[Home] [Help]