1 PACKAGE BODY igs_ad_ac_comp AS
2 /* $Header: IGSADA8B.pls 120.3 2006/08/07 14:23:33 apadegal ship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL spec for package: IGS_AD_AC_COMP |
11 | |
12 | NOTES |
13 | To Update Admission Completion Status as Satisfied |
14 | |
15 | HISTORY |
16 | Who When What |
17 | brajendr 9-Aug-2001 Incorporaetd the review comments |
18 | brajendr 3-Aug-2001 Creation of the Initial Code |
19 | cdcruz 18-feb-2002 bug 2217104 Admit to future term Enhancement,updated tbh call for
20 | new columns being added to IGS_AD_PS_APPL_INST
21 | hreddych 4-apr-2002 bug 2273789 The function get_cmp_apltritm was returning TRUE
22 | if one of the tracking status was complete which was modified to
23 | to return TRUE if all are complete
24 | nshee 29-Aug-2002 Bug 2395510 added 6 columns as part of deferments build
25 |hreddych 8-jan-2002 #2740404 Added the logmessages for giving details
26 | of the application Instance.
27 |hreddych 25-jun-2003 # 2989257 Altered the cursors cur_tr,cur_tr_itm
28 | of the Function get_cmp_apltritm
29 | rghosh 21-Oct-2003 Added the REF CURSOR c_dyn_pig_check and hence the
30 | logic for supporting dynamic Person ID Group
31 | (Enh# 3194295 , ADCR043: Person ID Group)
32 |rbezawad 1-Nov-04 Modified get_cpti_apcmp procedure to display the security error
33 | message in the log file w.r.t. Bug 3919112.
34 |apadegal 7-Aug-06 5450345 - Removed the commit statement, as it un-necessarily commits the transaction.
35 *=======================================================================*/
36
37 -- Declare all Global variables and global constants
38
39
40 FUNCTION get_cmp_apltritm(
41 p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
42 p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
43 p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
44 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
45 ) RETURN BOOLEAN AS
46 /*
47 || Created By : brajendr
48 || Created On :
49 || Purpose : This Procedure will return TRUE if all the Tracking Items in the given Application Instance are Complete.
50 || Known limitations, enhancements or remarks :
51 || Change History :
52 || Who When What
53 || (reverse chronological order - newest change first)
54 */
55
56 -- Get all the tracking step details from for each application of the PERSON.
57 CURSOR cur_tr(
58 p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
59 p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
60 p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
61 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
62 ) IS
63 SELECT ti.tracking_id
64 FROM igs_ad_aplins_admreq aa,
65 igs_tr_item ti,
66 igs_tr_type tt
67 WHERE aa.person_id = p_person_id
68 AND aa.admission_appl_number = p_admission_appl_number
69 AND aa.sequence_number = p_sequence_number
70 AND aa.course_cd = p_course_cd
71 AND aa.tracking_id = ti.tracking_id
72 AND ti.tracking_type = tt.tracking_type
73 AND tt.s_tracking_type = 'ADM_PROCESSING';
74
75 -- Get the list of tracking steps for which the tracking status is 'COMPLETE'
76 CURSOR cur_tr_itm(
77 p_tracking_id igs_ad_aplins_admreq.tracking_id%TYPE
78 ) IS
79 SELECT 'x'
80 FROM igs_tr_item iti,
81 igs_tr_status its,
82 igs_tr_type itt
83 WHERE iti.tracking_id = p_tracking_id
84 AND itt.tracking_type = iti.tracking_type
85 AND itt.s_tracking_type = 'ADM_PROCESSING'
86 AND its.tracking_status = iti.tracking_status
87 AND its.s_tracking_status = 'COMPLETE';
88
89 l_tr_itms_not_found BOOLEAN := TRUE;
90 cur_tr_itm_rec cur_tr_itm%ROWTYPE;
91
92 BEGIN
93 -- Get all the tracking step details from for each application of the PERSON.
94 FOR cur_tr_rec IN cur_tr( p_person_id, p_admission_appl_number, p_course_cd, p_sequence_number) LOOP
95 OPEN cur_tr_itm( cur_tr_rec.tracking_id);
96 FETCH cur_tr_itm INTO cur_tr_itm_rec;
97 IF cur_tr_itm%NOTFOUND THEN
98 CLOSE cur_tr_itm;
99 RETURN FALSE;
100 END IF;
101 CLOSE cur_tr_itm;
102 END LOOP;
103 RETURN TRUE;
104
105 END get_cmp_apltritm;
106
107
108 PROCEDURE get_cpti_apcmp(
109 p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
110 p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
111 p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
112 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
113 ) AS
114 /*
115 || Created By : brajendr
116 || Created On :
117 || Purpose : This procedure will update given Application Instance to the COMPLETE all the Tracking Items under this are COMPLTE
118 || Known limitations, enhancements or remarks :
119 || Change History :
120 || Who When What
121 || samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
122 || (reverse chronological order - newest change first)
123 */
124
125 -- Get the details of
126 CURSOR cur_adm_doc_status IS
127 SELECT adm_doc_status
128 FROM igs_ad_doc_stat
129 WHERE s_adm_doc_status = 'SATISFIED'
130 AND closed_ind = 'N'
131 AND system_default_ind= 'Y';
132
133 -- Get the details of
134 CURSOR cur_apcmp(
135 p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
136 p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
137 p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
138 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
139 ) IS
140 SELECT *
141 FROM igs_ad_ps_appl_inst ain
142 WHERE ain.person_id = p_person_id
143 AND ain.admission_appl_number = p_admission_appl_number
144 AND ain.course_cd = p_course_cd
145 AND ain.sequence_number = p_sequence_number;
146
147 --Local variables to check if the Security Policy exception already set or not. Ref: Bug 3919112
148 l_sc_encoded_text VARCHAR2(4000);
149 l_sc_msg_count NUMBER;
150 l_sc_msg_index NUMBER;
151 l_sc_app_short_name VARCHAR2(50);
152 l_sc_message_name VARCHAR2(50);
153
154 BEGIN
155
156 -- Create a SavePoint in order to process each Application.
157 SAVEPOINT IGSADA8_SP1;
158
159 IF get_cmp_apltritm(
160 p_person_id,
161 p_admission_appl_number,
162 p_course_cd,
163 p_sequence_number
164 ) = FALSE
165 THEN
166
167 -- p_message_name = 'Application completion status cannot be updated to satisfied since the tracking item/s are not yet complete'
168 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Person Id :'||p_person_id||' Admission Application Number :'||p_admission_appl_number);
169 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Course Code :' ||p_course_cd ||' Sequence Number :'||p_sequence_number);
170 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_TR_ITM_NT_CMPLT');
171 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
172
173 RETURN;
174
175 ELSE
176 -- Since all the tracking items for the application instance have been completed and
177 -- hence the application completion status can be updated to 'SATISFIED'
178 FOR cur_adm_doc_status_rec IN cur_adm_doc_status LOOP
179
180 -- Update the tracking item status.
181 FOR cur_apcmp_rec IN cur_apcmp( p_person_id, p_admission_appl_number, p_course_cd, p_sequence_number) LOOP
182 igs_ad_ps_appl_inst_pkg.update_row(
183 -- cur_adm_doc_status_rec.adm_doc_status
184 x_rowid => cur_apcmp_rec.row_id,
185 x_person_id => cur_apcmp_rec.person_id,
186 x_admission_appl_number => cur_apcmp_rec.admission_appl_number,
187 x_nominated_course_cd => cur_apcmp_rec.nominated_course_cd,
188 x_sequence_number => cur_apcmp_rec.sequence_number,
189 x_predicted_gpa => cur_apcmp_rec.predicted_gpa,
190 x_academic_index => cur_apcmp_rec.academic_index,
191 x_adm_cal_type => cur_apcmp_rec.adm_cal_type,
192 x_app_file_location => cur_apcmp_rec.app_file_location,
193 x_adm_ci_sequence_number => cur_apcmp_rec.adm_ci_sequence_number,
194 x_course_cd => cur_apcmp_rec.course_cd,
195 x_app_source_id => cur_apcmp_rec.app_source_id,
196 x_crv_version_number => cur_apcmp_rec.crv_version_number,
197 x_waitlist_rank => cur_apcmp_rec.waitlist_rank,
198 x_waitlist_status => cur_apcmp_rec.waitlist_status,
199 x_location_cd => cur_apcmp_rec.location_cd,
200 x_attent_other_inst_cd => cur_apcmp_rec.attent_other_inst_cd,
201 x_attendance_mode => cur_apcmp_rec.attendance_mode,
202 x_edu_goal_prior_enroll_id => cur_apcmp_rec.edu_goal_prior_enroll_id,
203 x_attendance_type => cur_apcmp_rec.attendance_type,
204 x_decision_make_id => cur_apcmp_rec.decision_make_id,
205 x_unit_set_cd => cur_apcmp_rec.unit_set_cd,
206 x_decision_date => cur_apcmp_rec.decision_date,
207 x_attribute_category => cur_apcmp_rec.attribute_category,
208 x_attribute1 => cur_apcmp_rec.attribute1,
209 x_attribute2 => cur_apcmp_rec.attribute2,
210 x_attribute3 => cur_apcmp_rec.attribute3,
211 x_attribute4 => cur_apcmp_rec.attribute4,
212 x_attribute5 => cur_apcmp_rec.attribute5,
213 x_attribute6 => cur_apcmp_rec.attribute6,
214 x_attribute7 => cur_apcmp_rec.attribute7,
215 x_attribute8 => cur_apcmp_rec.attribute8,
216 x_attribute9 => cur_apcmp_rec.attribute9,
217 x_attribute10 => cur_apcmp_rec.attribute10,
218 x_attribute11 => cur_apcmp_rec.attribute11,
219 x_attribute12 => cur_apcmp_rec.attribute12,
220 x_attribute13 => cur_apcmp_rec.attribute13,
221 x_attribute14 => cur_apcmp_rec.attribute14,
222 x_attribute15 => cur_apcmp_rec.attribute15,
223 x_attribute16 => cur_apcmp_rec.attribute16,
224 x_attribute17 => cur_apcmp_rec.attribute17,
225 x_attribute18 => cur_apcmp_rec.attribute18,
226 x_attribute19 => cur_apcmp_rec.attribute19,
227 x_attribute20 => cur_apcmp_rec.attribute20,
228 x_decision_reason_id => cur_apcmp_rec.decision_reason_id,
229 x_us_version_number => cur_apcmp_rec.us_version_number,
230 x_decision_notes => cur_apcmp_rec.decision_notes,
231 x_pending_reason_id => cur_apcmp_rec.pending_reason_id,
232 x_preference_number => cur_apcmp_rec.preference_number,
233 x_adm_doc_status => cur_adm_doc_status_rec.adm_doc_status,
234 x_adm_entry_qual_status => cur_apcmp_rec.adm_entry_qual_status,
235 x_deficiency_in_prep => cur_apcmp_rec.deficiency_in_prep,
236 x_late_adm_fee_status => cur_apcmp_rec.late_adm_fee_status,
237 x_spl_consider_comments => cur_apcmp_rec.spl_consider_comments,
238 x_apply_for_finaid => cur_apcmp_rec.apply_for_finaid,
239 x_finaid_apply_date => cur_apcmp_rec.finaid_apply_date,
240 x_adm_outcome_status => cur_apcmp_rec.adm_outcome_status,
241 x_adm_otcm_stat_auth_per_id => cur_apcmp_rec.adm_otcm_status_auth_person_id,
242 x_adm_outcome_status_auth_dt => cur_apcmp_rec.adm_outcome_status_auth_dt,
243 x_adm_outcome_status_reason => cur_apcmp_rec.adm_outcome_status_reason,
244 x_offer_dt => cur_apcmp_rec.offer_dt,
245 x_offer_response_dt => cur_apcmp_rec.offer_response_dt,
246 x_prpsd_commencement_dt => cur_apcmp_rec.prpsd_commencement_dt,
247 x_adm_cndtnl_offer_status => cur_apcmp_rec.adm_cndtnl_offer_status,
248 x_cndtnl_offer_satisfied_dt => cur_apcmp_rec.cndtnl_offer_satisfied_dt,
249 x_cndnl_ofr_must_be_stsfd_ind => cur_apcmp_rec.cndtnl_offer_must_be_stsfd_ind,
250 x_adm_offer_resp_status => cur_apcmp_rec.adm_offer_resp_status,
254 x_deferred_adm_ci_sequence_num => cur_apcmp_rec.deferred_adm_ci_sequence_num,
251 x_actual_response_dt => cur_apcmp_rec.actual_response_dt,
252 x_adm_offer_dfrmnt_status => cur_apcmp_rec.adm_offer_dfrmnt_status,
253 x_deferred_adm_cal_type => cur_apcmp_rec.deferred_adm_cal_type,
255 x_deferred_tracking_id => cur_apcmp_rec.deferred_tracking_id,
256 x_ass_rank => cur_apcmp_rec.ass_rank,
257 x_secondary_ass_rank => cur_apcmp_rec.secondary_ass_rank,
258 x_intr_accept_advice_num => cur_apcmp_rec.intrntnl_acceptance_advice_num,
259 x_ass_tracking_id => cur_apcmp_rec.ass_tracking_id,
260 x_fee_cat => cur_apcmp_rec.fee_cat,
261 x_hecs_payment_option => cur_apcmp_rec.hecs_payment_option,
262 x_expected_completion_yr => cur_apcmp_rec.expected_completion_yr,
263 x_expected_completion_perd => cur_apcmp_rec.expected_completion_perd,
264 x_correspondence_cat => cur_apcmp_rec.correspondence_cat,
265 x_enrolment_cat => cur_apcmp_rec.enrolment_cat,
266 x_funding_source => cur_apcmp_rec.funding_source,
267 x_applicant_acptnce_cndtn => cur_apcmp_rec.applicant_acptnce_cndtn,
268 x_cndtnl_offer_cndtn => cur_apcmp_rec.cndtnl_offer_cndtn,
269 x_ss_application_id => cur_apcmp_rec.ss_application_id,
270 x_ss_pwd => cur_apcmp_rec.ss_pwd,
271 x_authorized_dt => cur_apcmp_rec.authorized_dt,
272 x_authorizing_pers_id => cur_apcmp_rec.authorizing_pers_id,
273 x_entry_status => cur_apcmp_rec.entry_status,
274 x_entry_level => cur_apcmp_rec.entry_level,
275 x_sch_apl_to_id => cur_apcmp_rec.sch_apl_to_id ,
276 x_idx_calc_date => cur_apcmp_rec.idx_calc_date ,
277 X_FUT_ACAD_CAL_TYPE => cur_apcmp_rec.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
278 X_FUT_ACAD_CI_SEQUENCE_NUMBER => cur_apcmp_rec.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
279 X_FUT_ADM_CAL_TYPE => cur_apcmp_rec.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
280 X_FUT_ADM_CI_SEQUENCE_NUMBER => cur_apcmp_rec.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
281 X_PREV_TERM_ADM_APPL_NUMBER => cur_apcmp_rec.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
282 X_PREV_TERM_SEQUENCE_NUMBER => cur_apcmp_rec.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
283 X_FUT_TERM_ADM_APPL_NUMBER => cur_apcmp_rec.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
284 X_FUT_TERM_SEQUENCE_NUMBER => cur_apcmp_rec.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
285 X_DEF_ACAD_CAL_TYPE => cur_apcmp_rec.DEF_ACAD_CAL_TYPE, --Bug 2395510
286 X_DEF_ACAD_CI_SEQUENCE_NUM => cur_apcmp_rec.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
287 X_DEF_PREV_TERM_ADM_APPL_NUM => cur_apcmp_rec.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
288 X_DEF_PREV_APPL_SEQUENCE_NUM => cur_apcmp_rec.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
289 X_DEF_TERM_ADM_APPL_NUM => cur_apcmp_rec.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
290 X_DEF_APPL_SEQUENCE_NUM => cur_apcmp_rec.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
291 x_attribute21=> cur_apcmp_rec.attribute21,
292 x_attribute22=> cur_apcmp_rec.attribute22,
293 x_attribute23=> cur_apcmp_rec.attribute23,
294 x_attribute24=> cur_apcmp_rec.attribute24,
295 x_attribute25=> cur_apcmp_rec.attribute25,
296 x_attribute26=> cur_apcmp_rec.attribute26,
297 x_attribute27=> cur_apcmp_rec.attribute27,
298 x_attribute28=> cur_apcmp_rec.attribute28,
299 x_attribute29=> cur_apcmp_rec.attribute29,
300 x_attribute30=> cur_apcmp_rec.attribute30,
301 x_attribute31=> cur_apcmp_rec.attribute31,
302 x_attribute32=> cur_apcmp_rec.attribute32,
303 x_attribute33=> cur_apcmp_rec.attribute33,
304 x_attribute34=> cur_apcmp_rec.attribute34,
305 x_attribute35=> cur_apcmp_rec.attribute35,
306 x_attribute36=> cur_apcmp_rec.attribute36,
307 x_attribute37=> cur_apcmp_rec.attribute37,
308 x_attribute38=> cur_apcmp_rec.attribute38,
309 x_attribute39=> cur_apcmp_rec.attribute39,
310 x_attribute40=> cur_apcmp_rec.attribute40,
311 x_appl_inst_status=> cur_apcmp_rec.appl_inst_status,
312 x_ais_reason=> cur_apcmp_rec.ais_reason,
313 x_decline_ofr_reason=> cur_apcmp_rec.decline_ofr_reason
314 );
318 -- p_message_name = 'Application completion status for the application updated to SATISFIED';
315 END LOOP;
316 END LOOP;
317
319 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Person Id :'||p_person_id||' Admission Application Number :'||p_admission_appl_number);
320 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Course Code :' ||p_course_cd ||' Sequence Number :'||p_sequence_number);
321 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_CMPLT_STAT_UPD');
322 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
323
324 END IF;
325
326 EXCEPTION
327
328 WHEN others THEN
329 ROLLBACK TO IGSADA8_SP1;
330 --Loop through the messages in stack to check if there is Security Policy exception already set or not. Ref: Bug 3919112
331 l_sc_msg_count := IGS_GE_MSG_STACK.COUNT_MSG;
332 WHILE l_sc_msg_count <> 0 LOOP
333 igs_ge_msg_stack.get(l_sc_msg_count, 'T', l_sc_encoded_text, l_sc_msg_index);
334 fnd_message.parse_encoded(l_sc_encoded_text, l_sc_app_short_name, l_sc_message_name);
335 IF l_sc_message_name = 'IGS_SC_POLICY_EXCEPTION' OR l_sc_message_name = 'IGS_SC_POLICY_UPD_DEL_EXCEP' THEN
336 --print the the security exception in log file and return.
337 fnd_file.put_line( fnd_file.log, 'Error occured while processing the Application instance with Person Id: '||p_person_id||', Admission Application Number: '||p_admission_appl_number||
338 ', Course Code: ' ||p_course_cd ||' and Sequence Number: '||p_sequence_number);
339 fnd_message.set_encoded(l_sc_encoded_text);
340 fnd_file.put_line( fnd_file.log, fnd_message.get());
341 fnd_file.put_line( fnd_file.log, ' ');
342 RETURN;
343 END IF;
344 l_sc_msg_count := l_sc_msg_count - 1;
345 END LOOP;
346 END get_cpti_apcmp;
347
348
349 PROCEDURE upd_apl_cmp_st(
350 ERRBUF OUT NOCOPY VARCHAR2,
351 RETCODE OUT NOCOPY NUMBER,
352 p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
353 p_person_id_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
354 p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
355 p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
356 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,
357 p_calendar_details IN VARCHAR2,
358 p_admission_process_category IN VARCHAR2,
359 p_org_id IN igs_fi_posting_int_all.org_id%TYPE
360 ) AS
361 /*
362 || Created By :
363 || Created On :
364 || Purpose : This is a main Procedure which will Update the given Application to COMPLETE, if all the Tracking items are COMPLETE.
365 || This is getting called as a concurrent Job.
366 || Known limitations, enhancements or remarks :
367 || Change History :
368 || Who When What
369 || (reverse chronological order - newest change first)
370 || rghosh 21-Oct-2003 Added the REF CURSOR c_dyn_pig_check and hence the
371 || logic for supporting dynamic Person ID Group
372 || (Enh# 3194295 , ADCR043: Person ID Group)
373 */
374
375
376 TYPE c_dyn_pig_checkCurTyp IS REF CURSOR;
377 c_dyn_pig_check c_dyn_pig_checkCurTyp;
378 TYPE c_dyn_pig_checkrecTyp IS RECORD ( person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
379 admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
380 course_cd igs_ad_ps_appl_inst_all.course_cd%TYPE,
381 sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE);
382 c_dyn_pig_check_rec c_dyn_pig_checkrecTyp ;
383
384
385 lv_status VARCHAR2(1);
386 lv_sql_stmt VARCHAR(32767);
387 lv_group_type IGS_PE_PERSID_GROUP_V.group_type%TYPE;
388
389 -- Get the details of
390 CURSOR cur_appl_case1(
391 p_person_id igs_ad_ps_appl_inst.person_id%TYPE,
392 p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
393 p_course_cd igs_ad_ps_appl_inst.course_cd%TYPE,
394 p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE
395 ) IS
396 SELECT person_id, admission_appl_number, course_cd, sequence_number
397 FROM igs_ad_ps_appl_inst apai,
398 igs_ad_ou_stat aos,
399 igs_ad_doc_stat ads
400 WHERE apai.person_id = p_person_id
401 AND apai.admission_appl_number = p_admission_appl_number
402 AND apai.course_cd = p_course_cd
403 AND apai.sequence_number = p_sequence_number
404 AND aos.s_adm_outcome_status IN ('PENDING','COND-OFFER')
405 AND aos.closed_ind = 'N'
406 AND apai.adm_outcome_status = aos.adm_outcome_status
407 AND ads.s_adm_doc_status = 'PENDING'
408 AND ads.closed_ind = 'N'
409 AND apai.adm_doc_status = ads.adm_doc_status;
410
411
412 -- Get the details of
413 CURSOR cur_appl_case2(
414 p_person_id igs_ad_ps_appl_inst.person_id%TYPE
418 igs_ad_ou_stat aos,
415 ) IS
416 SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
417 FROM igs_ad_ps_appl_inst apai,
419 igs_ad_doc_stat ads
420 WHERE apai.person_id = p_person_id
421 AND aos.s_adm_outcome_status IN ('PENDING','COND-OFFER')
422 AND aos.closed_ind = 'N'
423 AND apai.adm_outcome_status = aos.adm_outcome_status
424 AND ads.s_adm_doc_status = 'PENDING'
425 AND ads.closed_ind = 'N'
426 AND apai.adm_doc_status = ads.adm_doc_status;
427
428
429 -- Get the details of
430 CURSOR cur_appl_case4(
431 p_admission_cat igs_ad_appl_all.admission_cat%TYPE,
432 p_s_admission_process_type igs_ad_appl_all.s_admission_process_type%TYPE,
433 p_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE,
434 p_acad_ci_sequence_number igs_ad_appl_all.acad_ci_sequence_number%TYPE,
435 p_adm_cal_type igs_ad_appl_all.adm_cal_type%TYPE,
436 p_adm_ci_sequence_number igs_ad_appl_all.adm_ci_sequence_number%TYPE
437 ) IS
438 SELECT apai.person_id, apai.admission_appl_number, apai. course_cd, apai.sequence_number
439 FROM igs_ad_ps_appl_inst apai,
440 igs_ad_appl aa,
441 igs_ad_ou_stat aos,
442 igs_ad_doc_stat ads
443 WHERE apai.person_id = aa.person_id
444 AND apai.admission_appl_number = aa.admission_appl_number
445 AND aa.acad_cal_type = p_acad_cal_type
446 AND aa.acad_ci_sequence_number = p_acad_ci_sequence_number
447 AND aa.adm_cal_type = p_adm_cal_type
448 AND aa.adm_ci_sequence_number = p_adm_ci_sequence_number
449 AND aa.admission_cat = p_admission_cat
450 AND aa.s_admission_process_type = p_s_admission_process_type
451 AND aos.s_adm_outcome_status IN ('PENDING','COND-OFFER')
452 AND aos.closed_ind = 'N'
453 AND apai.adm_outcome_status = aos.adm_outcome_status
454 AND ads.s_adm_doc_status = 'PENDING'
455 AND ads.closed_ind = 'N'
456 AND apai.adm_doc_status = ads.adm_doc_status;
457
458
459 l_acad_cal_type igs_ca_inst_all.cal_type%TYPE;
460 l_acad_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
461 l_adm_cal_type igs_ca_inst_all.cal_type%TYPE;
462 l_adm_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
463 l_admission_cat igs_ad_appl_all.admission_cat%TYPE;
464 l_s_admission_process_type igs_ad_appl_all.s_admission_process_type%TYPE;
465 l_records_not_found BOOLEAN := TRUE;
466
467 BEGIN
468
469 -- Set the Org_id for the corresponding responsibility.
470 igs_ge_gen_003.set_org_id( p_org_id);
471 RETCODE := 0;
472 ERRBUF := NULL;
473 lv_status := 'S'; /*Defaulted to 'S' and the function will return 'F' in case of failure */
474 lv_sql_stmt := igs_pe_dynamic_persid_group.get_dynamic_sql(p_person_id_group,lv_status,lv_group_type);
475 -- Log the Initial parameters into the LOG file.
476 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_COM_PRMS');
477 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
478
479 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_PID');
480 FND_MESSAGE.SET_TOKEN('PID', p_person_id);
481 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
482
483 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_PID_GRP');
484 FND_MESSAGE.SET_TOKEN('PGPID', p_person_id_group);
485 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
486
487 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_ADM_APLNO');
488 FND_MESSAGE.SET_TOKEN('APLNO', p_admission_appl_number);
489 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
490
491 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_CRCD');
492 FND_MESSAGE.SET_TOKEN('CRCD', p_course_cd);
493 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
494
495 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_APP_SEQNO');
496 FND_MESSAGE.SET_TOKEN('SEQNO', p_sequence_number);
497 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
498
499 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_CL_DTLS');
500 FND_MESSAGE.SET_TOKEN('CLDTLS', p_calendar_details);
501 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
502
503 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_APC');
504 FND_MESSAGE.SET_TOKEN('APC', p_admission_process_category);
505 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
506
507 -- CASE 1 :
508 -- If the parameters p_person_id, p_admission_appl_number, p_course_cd and p_sequence_number are entered
509 IF p_person_id IS NOT NULL AND
510 p_admission_appl_number IS NOT NULL AND
511 p_course_cd IS NOT NULL AND
512 p_sequence_number IS NOT NULL AND
513 p_person_id_group IS NULL AND
514 p_admission_process_category IS NULL AND
515 p_calendar_details IS NULL
516 THEN
517
518 -- Based on the parameters entered fetch the application instance from admission
519 -- application instance table which have application completion status = 'PENDING'
520 -- and application outcome status = 'PENDING, COND-OFFER'.
521 l_records_not_found := TRUE;
525 -- Make a call to the procedure : get_incp_trstp
522 FOR cur_appl_case1_rec IN cur_appl_case1( p_person_id, p_admission_appl_number, p_course_cd, p_sequence_number) LOOP
523 l_records_not_found := FALSE;
524
526 get_cpti_apcmp(
527 cur_appl_case1_rec.person_id,
528 cur_appl_case1_rec.admission_appl_number,
529 cur_appl_case1_rec.course_cd,
530 cur_appl_case1_rec.sequence_number
531 );
532 END LOOP;
533
534 -- If the Applicaiton records are not found then log a message
535 IF l_records_not_found THEN
536 -- Tracking steps cannot be completed for applications not having application completion status of pending
537 -- and application outcome status of pending or conditional offer
538 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_PEND_STAT');
539 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
540
541 -- Abort the process and raise error
542 RETURN;
543
544 END IF;
545
546 -- CASE 2 :
547 -- In case only Person ID has been entered and all the other parameters are null
548 ELSIF p_person_id IS NOT NULL AND
549 p_admission_appl_number IS NULL AND
550 p_course_cd IS NULL AND
551 p_sequence_number IS NULL AND
552 p_admission_process_category IS NULL AND
553 p_calendar_details IS NULL AND
554 p_person_id_group IS NULL
555 THEN
556 -- Based on the parameters entered fetch the application instance from admission
557 -- application instance table which have application completion status = 'PENDING' and
558 -- application outcome status = 'PENDING or 'COND-OFFER'
559 l_records_not_found := TRUE;
560 FOR cur_appl_case2_rec IN cur_appl_case2( p_person_id) LOOP
561 l_records_not_found := FALSE;
562
563 -- Make a call to the procedure : get_incp_trstp
564 get_cpti_apcmp(
565 cur_appl_case2_rec.person_id,
566 cur_appl_case2_rec.admission_appl_number,
567 cur_appl_case2_rec.course_cd,
568 cur_appl_case2_rec.sequence_number
569 );
570 END LOOP;
571
572 -- If the Applicaiton records are not found then log a message
573 IF l_records_not_found THEN
574 -- Tracking steps cannot be completed for applications not having application completion status of pending
575 -- and application outcome status of pending or conditional offer
576 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_PEND_STAT');
577 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
578
579 -- Abort the process and raise error
580 RETURN;
581
582 END IF;
583
584 -- CASE 3 :
585 -- In case the person group id has been entered and all other parameters are null
586 ELSIF p_person_id_group IS NOT NULL AND
587 p_person_id IS NULL AND
588 p_admission_appl_number IS NULL AND
589 p_course_cd IS NULL AND
590 p_sequence_number IS NULL AND
591 p_admission_process_category IS NULL AND
592 p_calendar_details IS NULL
593 THEN
594
595 -- Based on the parameters entered fetch the application instance from admission
596 -- application instance table which have application completion status = 'PENDING' and
597 -- application outcome status = 'PENDING or COND-OFFER'
598
599 l_records_not_found := TRUE;
600
601 IF lv_status = 'S' THEN
602 BEGIN
603
604 IF lv_group_type = 'STATIC' THEN
605
606 OPEN c_dyn_pig_check FOR
607 'SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
608 FROM igs_ad_ps_appl_inst apai,
609 igs_ad_ou_stat aos,
610 igs_ad_doc_stat ads
611 WHERE apai.person_id IN ( '||lv_sql_stmt||')
612 AND aos.s_adm_outcome_status IN (''PENDING'',''COND-OFFER'')
613 AND aos.closed_ind = ''N''
614 AND apai.adm_outcome_status = aos.adm_outcome_status
615 AND ads.s_adm_doc_status = ''PENDING''
616 AND ads.closed_ind = ''N''
617 AND apai.adm_doc_status = ads.adm_doc_status '
618 USING p_person_id_group; LOOP
619
620 FETCH c_dyn_pig_check INTO c_dyn_pig_check_rec;
621
622 IF c_dyn_pig_check%NOTFOUND THEN
623 EXIT;
624 END IF;
625
626 l_records_not_found := FALSE;
627
628 -- Make a call to the procedure : get_incp_trstp
629 get_cpti_apcmp(
630 c_dyn_pig_check_rec.person_id,
631 c_dyn_pig_check_rec.admission_appl_number,
632 c_dyn_pig_check_rec.course_cd,
633 c_dyn_pig_check_rec.sequence_number
634 );
635 END LOOP;
636
637 ELSIF lv_group_type = 'DYNAMIC' THEN
641 FROM igs_ad_ps_appl_inst apai,
638
639 OPEN c_dyn_pig_check FOR
640 'SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
642 igs_ad_ou_stat aos,
643 igs_ad_doc_stat ads
644 WHERE apai.person_id IN ( '||lv_sql_stmt||')
645 AND aos.s_adm_outcome_status IN (''PENDING'',''COND-OFFER'')
646 AND aos.closed_ind = ''N''
647 AND apai.adm_outcome_status = aos.adm_outcome_status
648 AND ads.s_adm_doc_status = ''PENDING''
649 AND ads.closed_ind = ''N''
650 AND apai.adm_doc_status = ads.adm_doc_status'; LOOP
651
652 FETCH c_dyn_pig_check INTO c_dyn_pig_check_rec;
653
654 IF c_dyn_pig_check%NOTFOUND THEN
655 EXIT;
656 END IF;
657
658 l_records_not_found := FALSE;
659
660 -- Make a call to the procedure : get_incp_trstp
661 get_cpti_apcmp(
662 c_dyn_pig_check_rec.person_id,
663 c_dyn_pig_check_rec.admission_appl_number,
664 c_dyn_pig_check_rec.course_cd,
665 c_dyn_pig_check_rec.sequence_number
666 );
667 END LOOP;
668
669 CLOSE c_dyn_pig_check;
670
671 -- If the Applicaiton records are not found then log a message
672 IF l_records_not_found THEN
673 -- Tracking steps cannot be completed for applications not having application completion status of pending
674 -- and application outcome status of pending or conditional offer
675 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_PEND_STAT');
676 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
677
678 -- Abort the process and raise error
679 RETURN;
680 END IF;
681
682 END IF;
683 EXCEPTION
684 WHEN OTHERS THEN
685 FND_MESSAGE.SET_NAME ('IGF','IGF_AP_INVALID_QUERY');
686 FND_FILE.PUT_LINE (FND_FILE.LOG,FND_MESSAGE.GET);
687 FND_FILE.PUT_LINE (FND_FILE.LOG,lv_sql_stmt);
688 END;
689
690 ELSE
691 FND_MESSAGE.SET_NAME ('IGS',' IGS_AZ_DYN_PERS_ID_GRP_ERR');
692 FND_FILE.PUT_LINE (FND_FILE.LOG,FND_MESSAGE.GET);
693 END IF;
694
695 -- CASE 4 :
696 -- In case the academic calendar, admission process category and admission calendar are
697 -- entered and other parameters are null
698 ELSIF p_admission_process_category IS NOT NULL AND
699 p_calendar_details IS NOT NULL AND
700 p_person_id IS NULL AND
701 p_person_id_group IS NULL AND
702 p_admission_appl_number IS NULL AND
703 p_course_cd IS NULL AND
704 p_sequence_number IS NULL
705 THEN
706
707 -- Get the Academic Calander details form the Academic Calender Parameter
708 l_acad_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 1, 10));
709 l_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 14, 6));
710
711 -- Get the Admission Calander details form the Admission Calender Parameter
712 l_adm_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 23, 10));
713 l_adm_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 37, 6));
714
715 -- Get the Admission Process Category details form the APC
716 l_admission_cat := RTRIM ( SUBSTR ( p_admission_process_category, 1, 10));
717 l_s_admission_process_type := TRIM ( SUBSTR ( p_admission_process_category, 11));
718
719 -- Based on the parameters entered fetch the application instance from admission
720 -- application instance table which have application completion status = 'PENDING' and
721 -- application outcome status = 'PENDING or COND-OFFER'
722 l_records_not_found := TRUE;
723 FOR cur_appl_case4_rec IN cur_appl_case4( l_admission_cat, l_s_admission_process_type, l_acad_cal_type, l_acad_ci_sequence_number, l_adm_cal_type, l_adm_ci_sequence_number ) LOOP
724 l_records_not_found := FALSE;
725
726 -- Make a call to the procedure : get_incp_trstp
727 get_cpti_apcmp(
728 cur_appl_case4_rec.person_id,
729 cur_appl_case4_rec.admission_appl_number,
730 cur_appl_case4_rec.course_cd,
731 cur_appl_case4_rec.sequence_number
732 );
733 END LOOP;
734
735 -- If the Applicaiton records are not found then log a message
736 IF l_records_not_found THEN
737 -- Tracking steps cannot be completed for applications not having application completion status of pending
738 -- and application outcome status of pending or conditional offer
739 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_PEND_STAT');
740 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
741
742 -- Abort the process and raise error
743 RETURN;
744
745 END IF;
746
747 -- CASE 5 :
748 -- All the parameters are NULL, raise an error and abort the process
749 ELSIF p_admission_process_category IS NULL AND
750 p_calendar_details IS NULL AND
751 p_person_id IS NULL AND
752 p_person_id_group IS NULL AND
753 p_admission_appl_number IS NULL AND
754 p_course_cd IS NULL AND
755 p_sequence_number IS NULL
756 THEN
757
758 -- Message ( 'All the parameters are null. The process cannot be run')
759 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_ALL_PRM_NULL');
760 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
761 RETURN;
762
763 -- CASE 6 :
764 -- In case if the parameters are not in proper combination.
765 -- OR some improper combination
766 ELSE
767 -- 'Invalid parameters entered. Valid combinations for parameters to be entered is Person ID OR Person Group ID OR Person ID,
768 -- Admission Application Number, Program Code, Sequence Number OR Academic Calendar, Admission Calendar, Admission Process Category'
769 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_INV_PRM_COMB');
770 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
771 RETURN;
772
773 END IF;
774
775 EXCEPTION
776 WHEN OTHERS THEN
777 ROLLBACK;
778 RETCODE := 2;
779 ERRBUF := fnd_message.get_string( 'IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
780 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
781
782 END upd_apl_cmp_st;
783
784 END igs_ad_ac_comp;
[Home] [Help]