[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_GEN_011
Source
1 PACKAGE BODY igs_ad_gen_011 AS
2 /* $Header: IGSAD11B.pls 120.7 2006/02/21 22:51:09 arvsrini ship $ */
3 /*change history
4 who when what
5 npalanis 23-OCT-2002 Bug : 2608630
6 references to igs_pe_code_classes are removed
7 npalanis 23-OCT-2002 Bug : 2547368
8 Defaulting arguments in funtion and procedure definitions removed
9 rbezawad 30-Oct-2004 Added logic to properly handle the security Policy errors IGS_SC_POLICY_EXCEPTION
10 and IGS_SC_POLICY_UPD_DEL_EXCEP w.r.t. bug fix 3919112.
11 sjlaport 17-Feb-2005 Removed function Admp_Ins_Eap_Eitpi for IGR migration (bug 4114493)
12 */
13 Procedure Admp_Ins_Acai_Hist(
14 p_person_id IN NUMBER ,
15 p_admission_appl_number IN NUMBER ,
16 p_nominated_course_cd IN VARCHAR2 ,
17 p_sequence_number IN NUMBER ,
18 p_new_adm_cal_type IN VARCHAR2 ,
19 p_old_adm_cal_type IN VARCHAR2 ,
20 p_new_adm_ci_sequence_number IN NUMBER ,
21 p_old_adm_ci_sequence_number IN NUMBER ,
22 p_new_course_cd IN VARCHAR2 ,
23 p_old_course_cd IN VARCHAR2 ,
24 p_new_crv_version_number IN NUMBER ,
25 p_old_crv_version_number IN NUMBER ,
26 p_new_location_cd IN VARCHAR2 ,
27 p_old_location_cd IN VARCHAR2 ,
28 p_new_attendance_mode IN VARCHAR2 ,
29 p_old_attendance_mode IN VARCHAR2 ,
30 p_new_attendance_type IN VARCHAR2 ,
31 p_old_attendance_type IN VARCHAR2 ,
32 p_new_unit_set_cd IN VARCHAR2 ,
33 p_old_unit_set_cd IN VARCHAR2 ,
34 p_new_us_version_number IN NUMBER ,
35 p_old_us_version_number IN NUMBER ,
36 p_new_preference_number IN NUMBER ,
37 p_old_preference_number IN NUMBER ,
38 p_new_adm_doc_status IN VARCHAR2 ,
39 p_old_adm_doc_status IN VARCHAR2 ,
40 p_new_adm_entry_qual_status IN VARCHAR2 ,
41 p_old_adm_entry_qual_status IN VARCHAR2 ,
42 p_new_late_adm_fee_status IN VARCHAR2 ,
43 p_old_late_adm_fee_status IN VARCHAR2 ,
44 p_new_adm_outcome_status IN VARCHAR2 ,
45 p_old_adm_outcome_status IN VARCHAR2 ,
46 p_new_otcm_sts_auth_prsn_id IN NUMBER ,
47 p_old_otcm_sts_auth_prsn_id IN NUMBER ,
48 p_new_adm_otcm_status_auth_dt IN DATE ,
49 p_old_adm_otcm_status_auth_dt IN DATE ,
50 p_new_adm_otcm_status_reason IN VARCHAR2 ,
51 p_old_adm_otcm_status_reason IN VARCHAR2 ,
52 p_new_offer_dt IN DATE ,
53 p_old_offer_dt IN DATE ,
54 p_new_offer_response_dt IN DATE ,
55 p_old_offer_response_dt IN DATE ,
56 p_new_prpsd_commencement_dt IN DATE ,
57 p_old_prpsd_commencement_dt IN DATE ,
58 p_new_adm_cndtnl_offer_status IN VARCHAR2 ,
59 p_old_adm_cndtnl_offer_status IN VARCHAR2 ,
60 p_new_cndtnl_offer_stsfd_dt IN DATE ,
61 p_old_cndtnl_offer_stsfd_dt IN DATE ,
62 p_new_cndtnl_off_must_be_stsfd IN VARCHAR2 ,
63 p_old_cndtnl_off_must_be_stsfd IN VARCHAR2 ,
64 p_new_adm_offer_resp_status IN VARCHAR2 ,
65 p_old_adm_offer_resp_status IN VARCHAR2 ,
66 p_new_actual_response_dt IN DATE ,
67 p_old_actual_response_dt IN DATE ,
68 p_new_adm_offer_dfrmnt_status IN VARCHAR2 ,
69 p_old_adm_offer_dfrmnt_status IN VARCHAR2 ,
70 p_new_deferred_adm_cal_type IN VARCHAR2 ,
71 p_old_deferred_adm_cal_type IN VARCHAR2 ,
72 p_new_deferred_adm_ci_seq_num IN NUMBER ,
73 p_old_deferred_adm_ci_seq_num IN NUMBER ,
74 p_new_deferred_tracking_id IN NUMBER ,
75 p_old_deferred_tracking_id IN NUMBER ,
76 p_new_ass_rank IN NUMBER ,
77 p_old_ass_rank IN NUMBER ,
78 p_new_secondary_ass_rank IN NUMBER ,
79 p_old_secondary_ass_rank IN NUMBER ,
80 p_new_intrntnl_acpt_advice_num IN NUMBER ,
81 p_old_intrntnl_acpt_advice_num IN NUMBER ,
82 p_new_ass_tracking_id IN NUMBER ,
83 p_old_ass_tracking_id IN NUMBER ,
84 p_new_fee_cat IN VARCHAR2 ,
85 p_old_fee_cat IN VARCHAR2 ,
86 p_new_hecs_payment_option IN VARCHAR2 ,
87 p_old_hecs_payment_option IN VARCHAR2 ,
88 p_new_expected_completion_yr IN NUMBER ,
89 p_old_expected_completion_yr IN NUMBER ,
90 p_new_expected_completion_perd IN VARCHAR2 ,
91 p_old_expected_completion_perd IN VARCHAR2 ,
92 p_new_correspondence_cat IN VARCHAR2 ,
93 p_old_correspondence_cat IN VARCHAR2 ,
94 p_new_enrolment_cat IN VARCHAR2 ,
95 p_old_enrolment_cat IN VARCHAR2 ,
96 p_new_funding_source IN VARCHAR2 ,
97 p_old_funding_source IN VARCHAR2 ,
98 p_new_update_who IN VARCHAR2 ,
99 p_old_update_who IN VARCHAR2 ,
100 p_new_update_on IN DATE ,
101 p_old_update_on IN DATE ,
102 p_new_applicant_acptnce_cndtn IN VARCHAR2 ,
103 p_old_applicant_acptnce_cndtn IN VARCHAR2 ,
104 p_new_cndtnl_offer_cndtn IN VARCHAR2 ,
105 p_old_cndtnl_offer_cndtn IN VARCHAR2 ,
106 p_new_appl_inst_status IN VARCHAR2 DEFAULT NULL, --arvsrini igsm
107 p_old_appl_inst_status IN VARCHAR2 DEFAULT NULL,
108 P_NEW_DECISION_DATE DATE DEFAULT NULL, -- begin APADEGAL adtd001 igs.m
109 P_OLD_DECISION_DATE DATE DEFAULT NULL,
110 P_NEW_DECISION_MAKE_ID NUMBER DEFAULT NULL,
111 P_OLD_DECISION_MAKE_ID NUMBER DEFAULT NULL,
112 P_NEW_DECISION_REASON_ID NUMBER DEFAULT NULL,
113 P_OLD_DECISION_REASON_ID NUMBER DEFAULT NULL,
114 P_NEW_PENDING_REASON_ID NUMBER DEFAULT NULL,
115 P_OLD_PENDING_REASON_ID NUMBER DEFAULT NULL,
116 P_NEW_WAITLIST_STATUS VARCHAR2 DEFAULT NULL,
117 P_OLD_WAITLIST_STATUS VARCHAR2 DEFAULT NULL,
118 P_NEW_WAITLIST_RANK VARCHAR2 DEFAULT NULL,
119 P_OLD_WAITLIST_RANK VARCHAR2 DEFAULT NULL,
120 P_NEW_FUTURE_ACAD_CAL_TYPE VARCHAR2 DEFAULT NULL,
121 P_OLD_FUTURE_ACAD_CAL_TYPE VARCHAR2 DEFAULT NULL,
122 P_NEW_FUTURE_ACAD_CI_SEQ_NUM NUMBER DEFAULT NULL,
123 P_OLD_FUTURE_ACAD_CI_SEQ_NUM NUMBER DEFAULT NULL,
124 P_NEW_FUTURE_ADM_CAL_TYPE VARCHAR2 DEFAULT NULL,
125 P_OLD_FUTURE_ADM_CAL_TYPE VARCHAR2 DEFAULT NULL,
126 P_NEW_FUTURE_ADM_CI_SEQ_NUM NUMBER DEFAULT NULL,
127 P_OLD_FUTURE_ADM_CI_SEQ_NUM NUMBER DEFAULT NULL,
128 P_NEW_DEF_ACAD_CAL_TYPE VARCHAR2 DEFAULT NULL,
129 P_OLD_DEF_ACAD_CAL_TYPE VARCHAR2 DEFAULT NULL,
130 P_NEW_DEF_ACAD_CI_SEQ_NUM NUMBER DEFAULT NULL,
131 P_OLD_DEF_ACAD_CI_SEQ_NUM NUMBER DEFAULT NULL,
132 P_NEW_DECLINE_OFR_REASON VARCHAR2 DEFAULT NULL,
133 P_OLD_DECLINE_OFR_REASON VARCHAR2 DEFAULT NULL -- end APADEGAL adtd001 igs.m
134
135 )
136 IS
137 gv_other_detail VARCHAR2(255);
138
139 --Local variables to check if the Security Policy exception already set or not. Ref: Bug 3919112
140 l_sc_encoded_text VARCHAR2(4000);
141 l_sc_msg_count NUMBER;
142 l_sc_msg_index NUMBER;
143 l_sc_app_short_name VARCHAR2(50);
144 l_sc_message_name VARCHAR2(50);
145 lv_old_update_on DATE := p_old_update_on;
146
147
148 BEGIN -- admp_ins_acai_hist
149 -- Routine to create a history for the IGS_AD_PS_APPL_INST table.
150 DECLARE
151 v_changed_flag BOOLEAN DEFAULT FALSE;
152 v_acaih_rec IGS_AD_PS_APLINSTHST%ROWTYPE;
153 lv_rowid VARCHAR2(25);
154 l_org_id NUMBER(15);
155 l_old_hist_start_dt VARCHAR2(2);
156 CURSOR c_old_hist_dt
157 IS
158 SELECT 'x'
159 FROM IGS_AD_PS_APLINSTHST
160 WHERE person_id = p_person_id
161 AND admission_appl_number = p_admission_appl_number
162 AND nominated_course_cd = p_nominated_course_cd
163 AND sequence_number = p_sequence_number
164 AND hist_start_dt = p_old_update_on;
165
166 -- begin apadegal adtd001 igs.m
167 CURSOR cur_ad_ps_appl ( cp_person_id igs_ad_ps_appl.person_id%type ,
168 cp_admission_appl_number igs_ad_ps_appl.admission_appl_number%type ,
169 cp_nominated_course_cd igs_ad_ps_appl.nominated_course_cd%type ) IS
170 SELECT req_for_reconsideration_ind
171 FROM igs_ad_ps_appl
172 WHERE person_id = cp_person_id and
173 admission_appl_number = cp_admission_appl_number and
174 nominated_course_cd = cp_nominated_course_cd;
175 -- end apadegal adtd001 igs.m
176
177 CURSOR c_latest_hist_dt IS
178 SELECT MAX(hist_start_dt) max_hist_start_dt, MAX(hist_end_dt) max_hist_end_dt
179 FROM IGS_AD_PS_APLINSTHST
180 WHERE person_id = p_person_id
181 AND admission_appl_number = p_admission_appl_number
182 AND nominated_course_cd = p_nominated_course_cd
183 AND sequence_number = p_sequence_number;
184
185 l_latest_hist_dt c_latest_hist_dt%ROWTYPE;
186
187 BEGIN
188 -- Check if any of the old IGS_AD_PS_APPL_INST values are different from
189 -- the associated new IGS_AD_PS_APPL_INST values.
190 IF NVL(p_new_adm_cal_type,'NULL') <> NVL(p_old_adm_cal_type,'NULL') THEN
191 v_acaih_rec.adm_cal_type := p_old_adm_cal_type;
192 v_changed_flag := TRUE;
193 END IF;
194 IF NVL(p_new_adm_ci_sequence_number,-1) <>
195 NVL(p_old_adm_ci_sequence_number, -1) THEN
196 v_acaih_rec.adm_ci_sequence_number := p_old_adm_ci_sequence_number;
197 v_changed_flag := TRUE;
198 END IF;
199 IF p_new_course_cd <> p_old_course_cd THEN
200 v_acaih_rec.course_cd := p_old_course_cd;
201 v_changed_flag := TRUE;
202 END IF;
203 IF p_new_crv_version_number <> p_old_crv_version_number THEN
204 v_acaih_rec.crv_version_number := p_old_crv_version_number;
205 v_changed_flag := TRUE;
206 END IF;
207 IF NVL(p_new_location_cd,'NULL') <> NVL(p_old_location_cd,'NULL') THEN
208 v_acaih_rec.location_cd := p_old_location_cd;
209 v_changed_flag := TRUE;
210 END IF;
211 IF NVL(p_new_attendance_mode,'NULL') <>
212 NVL(p_old_attendance_mode,'NULL') THEN
213 v_acaih_rec.attendance_mode := p_old_attendance_mode;
214 v_changed_flag := TRUE;
215 END IF;
216 IF NVL(p_new_attendance_type,'NULL') <>
217 NVL(p_old_attendance_type,'NULL') THEN
218 v_acaih_rec.attendance_type := p_old_attendance_type;
219 v_changed_flag := TRUE;
220 END IF;
221 IF NVL(p_new_unit_set_cd, 'NULL') <> NVL(p_old_unit_set_cd, 'NULL') THEN
222 v_acaih_rec.unit_set_cd := p_old_unit_set_cd;
223 v_changed_flag := TRUE;
224 END IF;
225 IF NVL(p_new_us_version_number, -1) <> NVL(p_old_us_version_number, -1) THEN
226 v_acaih_rec.us_version_number := p_old_us_version_number;
227 v_changed_flag := TRUE;
228 END IF;
229 IF NVL(p_new_preference_number, -1) <> NVL(p_old_preference_number, -1) THEN
230 v_acaih_rec.preference_number := p_old_preference_number;
231 v_changed_flag := TRUE;
232 END IF;
233 IF p_new_adm_doc_status <> p_old_adm_doc_status THEN
234 v_acaih_rec.adm_doc_status := p_old_adm_doc_status;
235 v_changed_flag := TRUE;
236 END IF;
237 IF p_new_adm_entry_qual_status <> p_old_adm_entry_qual_status THEN
238 v_acaih_rec.adm_entry_qual_status := p_old_adm_entry_qual_status;
239 v_changed_flag := TRUE;
240 END IF;
241 IF p_new_late_adm_fee_status <> p_old_late_adm_fee_status THEN
242 v_acaih_rec.late_adm_fee_status := p_old_late_adm_fee_status;
243 v_changed_flag := TRUE;
244 END IF;
245 IF p_new_adm_outcome_status <> p_old_adm_outcome_status THEN
246 v_acaih_rec.adm_outcome_status := p_old_adm_outcome_status;
247 v_changed_flag := TRUE;
248 END IF;
249 IF NVL(p_new_otcm_sts_auth_prsn_id,-1) <>
250 NVL(p_old_otcm_sts_auth_prsn_id,-1) THEN
251 v_acaih_rec.adm_otcm_status_auth_person_id := p_old_otcm_sts_auth_prsn_id;
252 v_changed_flag := TRUE;
253 END IF;
254 IF NVL(p_new_adm_otcm_status_auth_dt,SYSDATE) <>
255 NVL(p_old_adm_otcm_status_auth_dt, SYSDATE) THEN
256 v_acaih_rec.adm_outcome_status_auth_dt := p_old_adm_otcm_status_auth_dt;
257 v_changed_flag := TRUE;
258 END IF;
259 IF NVL(p_new_adm_otcm_status_reason,'NULL') <>
260 NVL(p_old_adm_otcm_status_reason,'NULL') THEN
261 v_acaih_rec.adm_outcome_status_reason := p_old_adm_otcm_status_reason;
262 v_changed_flag := TRUE;
263 END IF;
264 IF NVL(p_new_offer_dt, SYSDATE) <> NVL(p_old_offer_dt, SYSDATE) THEN
265 v_acaih_rec.offer_dt := p_old_offer_dt;
266 v_changed_flag := TRUE;
267 END IF;
268 IF NVL(p_new_offer_response_dt,SYSDATE) <>
269 NVL(p_old_offer_response_dt,SYSDATE) THEN
270 v_acaih_rec.offer_response_dt := p_old_offer_response_dt;
271 v_changed_flag := TRUE;
272 END IF;
273 IF NVL(p_new_prpsd_commencement_dt, SYSDATE) <>
274 NVL(p_old_prpsd_commencement_dt, SYSDATE) THEN
275 v_acaih_rec.prpsd_commencement_dt := p_old_prpsd_commencement_dt;
276 v_changed_flag := TRUE;
277 END IF;
278 IF p_new_adm_cndtnl_offer_status <> p_old_adm_cndtnl_offer_status THEN
279 v_acaih_rec.adm_cndtnl_offer_status := p_old_adm_cndtnl_offer_status;
280 v_changed_flag := TRUE;
281 END IF;
282 IF NVL(p_new_cndtnl_offer_stsfd_dt,SYSDATE) <>
283 NVL(p_old_cndtnl_offer_stsfd_dt,SYSDATE) THEN
284 v_acaih_rec.cndtnl_offer_satisfied_dt := p_old_cndtnl_offer_stsfd_dt;
285 v_changed_flag := TRUE;
286 END IF;
287 IF NVL(p_new_cndtnl_off_must_be_stsfd, 'NULL') <>
288 NVL(p_old_cndtnl_off_must_be_stsfd, 'NULL') THEN
289 v_acaih_rec.cndtnl_offer_must_be_stsfd_ind := p_old_cndtnl_off_must_be_stsfd;
290 v_changed_flag := TRUE;
291 END IF;
292 IF p_new_adm_offer_resp_status <> p_old_adm_offer_resp_status THEN
293 v_acaih_rec.adm_offer_resp_status := p_old_adm_offer_resp_status;
294 v_changed_flag := TRUE;
295 END IF;
296 IF NVL(p_new_actual_response_dt,SYSDATE) <>
297 NVL(p_old_actual_response_dt,SYSDATE) THEN
298 v_acaih_rec.actual_response_dt := p_old_actual_response_dt;
299 v_changed_flag := TRUE;
300 END IF;
301 IF p_new_adm_offer_dfrmnt_status <> p_old_adm_offer_dfrmnt_status THEN
302 v_acaih_rec.adm_offer_dfrmnt_status := p_old_adm_offer_dfrmnt_status;
303 v_changed_flag := TRUE;
304 END IF;
305 IF NVL(p_new_deferred_adm_cal_type,'NULL') <>
306 NVL(p_old_deferred_adm_cal_type,'NULL') THEN
307 v_acaih_rec.deferred_adm_cal_type := p_old_deferred_adm_cal_type;
308 v_changed_flag := TRUE;
309 END IF;
310 IF NVL(p_new_deferred_adm_ci_seq_num,-1) <>
311 NVL(p_old_deferred_adm_ci_seq_num,-1) THEN
312 v_acaih_rec.deferred_adm_ci_sequence_num := p_old_deferred_adm_ci_seq_num;
313 v_changed_flag := TRUE;
314 END IF;
315 IF NVL(p_new_deferred_tracking_id,-1) <>
316 NVL(p_old_deferred_tracking_id,-1) THEN
317 v_acaih_rec.deferred_tracking_id := p_old_deferred_tracking_id;
318 v_changed_flag := TRUE;
319 END IF;
320 IF NVL(p_new_ass_rank,-1) <> NVL(p_old_ass_rank,-1) THEN
321 v_acaih_rec.ass_rank := p_old_ass_rank;
322 v_changed_flag := TRUE;
323 END IF;
324 IF NVL(p_new_secondary_ass_rank,-1) <> NVL(p_old_secondary_ass_rank,-1) THEN
325 v_acaih_rec.secondary_ass_rank := p_old_secondary_ass_rank;
326 v_changed_flag := TRUE;
327 END IF;
328 IF NVL(p_new_intrntnl_acpt_advice_num,-1) <>
329 NVL(p_old_intrntnl_acpt_advice_num,-1) THEN
330 v_acaih_rec.intrntnl_acceptance_advice_num := p_old_intrntnl_acpt_advice_num;
331 v_changed_flag := TRUE;
332 END IF;
333 IF NVL(p_new_ass_tracking_id,-1) <> NVL(p_old_ass_tracking_id,-1) THEN
334 v_acaih_rec.ass_tracking_id := p_old_ass_tracking_id;
335 v_changed_flag := TRUE;
336 END IF;
337 IF NVL(p_new_fee_cat,'NULL') <> NVL(p_old_fee_cat,'NULL') THEN
338 v_acaih_rec.fee_cat := p_old_fee_cat;
339 v_changed_flag := TRUE;
340 END IF;
341 IF NVL(p_new_funding_source, 'NULL') <>
342 NVL(p_old_funding_source, 'NULL') THEN
346 IF NVL(p_new_hecs_payment_option,'NULL') <>
343 v_acaih_rec.funding_source := p_old_funding_source;
344 v_changed_flag := TRUE;
345 END IF;
347 NVL(p_old_hecs_payment_option,'NULL') THEN
348 v_acaih_rec.hecs_payment_option := p_old_hecs_payment_option;
349 v_changed_flag := TRUE;
350 END IF;
351 IF NVL(p_new_expected_completion_yr,-1) <>
352 NVL(p_old_expected_completion_yr,-1) THEN
353 v_acaih_rec.expected_completion_yr := p_old_expected_completion_yr;
354 v_changed_flag := TRUE;
355 END IF;
356 IF NVL(p_new_expected_completion_perd,'NULL') <>
357 NVL(p_old_expected_completion_perd,'NULL') THEN
358 v_acaih_rec.expected_completion_perd := p_old_expected_completion_perd;
359 v_changed_flag := TRUE;
360 END IF;
361 IF NVL(p_new_correspondence_cat,'NULL') <>
362 NVL(p_old_correspondence_cat,'NULL') THEN
363 v_acaih_rec.correspondence_cat := p_old_correspondence_cat;
364 v_changed_flag := TRUE;
365 END IF;
366 IF NVL(p_new_enrolment_cat,'NULL') <> NVL(p_old_enrolment_cat,'NULL') THEN
367 v_acaih_rec.enrolment_cat := p_old_enrolment_cat;
368 v_changed_flag := TRUE;
369 END IF;
370 IF NVL(p_new_applicant_acptnce_cndtn,'NULL') <>
371 NVL(p_old_applicant_acptnce_cndtn,'NULL') THEN
372 v_acaih_rec.applicant_acptnce_cndtn := p_old_applicant_acptnce_cndtn;
373 v_changed_flag := TRUE;
374 END IF;
375 IF NVL(p_new_cndtnl_offer_cndtn, 'NULL') <>
376 NVL(p_old_cndtnl_offer_cndtn, 'NULL') THEN
377 v_acaih_rec.cndtnl_offer_cndtn := p_old_cndtnl_offer_cndtn;
378 v_changed_flag := TRUE;
379 END IF;
380
381 IF NVL(p_new_appl_inst_status, 'NULL') <> -- IGS.M arvsrini
382 NVL(p_old_appl_inst_status, 'NULL') THEN
383 v_acaih_rec.appl_inst_status := p_old_appl_inst_status;
384 v_changed_flag := TRUE;
385 END IF;
386
387 -- begin apadegal td001 igsm
388 IF NVL(p_new_decision_Make_Id, -1) <>
389 NVL(p_old_decision_Make_Id, -1) THEN
390 v_acaih_rec.decision_Make_Id := p_old_decision_Make_Id;
391 v_changed_flag := TRUE;
392 END IF;
393
394 IF NVL(p_new_decision_Date, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
395 NVL(p_old_decision_Date, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
396 v_acaih_rec.decision_Date := p_old_decision_Date;
397 v_changed_flag := TRUE;
398 END IF;
399
400 IF NVL(p_new_decision_reason_id, -1) <>
401 NVL(p_old_decision_reason_id, -1) THEN
402 v_acaih_rec.decision_reason_id := p_old_decision_reason_id;
403 v_changed_flag := TRUE;
404 END IF;
405
406 IF NVL(p_new_pending_reason_id, -1) <>
407 NVL(p_old_pending_reason_id, -1) THEN
408 v_acaih_rec.pending_reason_id := p_old_pending_reason_id;
409 v_changed_flag := TRUE;
410 END IF;
411 IF NVL(p_new_waitlist_status, 'NULL') <>
412 NVL(p_old_waitlist_status, 'NULL') THEN
413 v_acaih_rec.waitlist_status := p_old_waitlist_status;
414 v_changed_flag := TRUE;
415 END IF;
416
417 IF NVL(p_new_waitlist_rank, 'NULL') <>
418 NVL(p_old_waitlist_rank, 'NULL') THEN
419 v_acaih_rec.waitlist_rank := p_old_waitlist_rank;
420 v_changed_flag := TRUE;
421 END IF;
422
423 IF NVL(p_new_Future_Acad_Cal_Type, -1) <>
424 NVL(p_old_Future_Acad_Cal_Type, -1) THEN
425 v_acaih_rec.Future_Acad_Cal_Type := p_old_Future_Acad_Cal_Type;
426 v_changed_flag := TRUE;
427 END IF;
428
429 IF NVL(p_new_FUTURE_ACAD_CI_SEQ_NUM, -1) <>
430 NVL(p_old_FUTURE_ACAD_CI_SEQ_NUM, -1) THEN
431 v_acaih_rec.Future_Acad_Ci_Sequence_Num := p_old_FUTURE_ACAD_CI_SEQ_NUM;
432 v_changed_flag := TRUE;
433 END IF;
434
435 IF NVL(p_new_FUTURE_ADM_CAL_TYPE, -1) <>
436 NVL(p_old_FUTURE_ADM_CAL_TYPE, -1) THEN
437 v_acaih_rec.Future_Adm_Cal_Type := p_old_FUTURE_ADM_CAL_TYPE;
438 v_changed_flag := TRUE;
439 END IF;
440
441 IF NVL(p_new_FUTURE_ADM_CI_SEQ_NUM, -1) <>
442 NVL(p_old_FUTURE_ADM_CI_SEQ_NUM, -1) THEN
443 v_acaih_rec.Future_Adm_Ci_Sequence_Num := p_old_FUTURE_ADM_CI_SEQ_NUM;
444 v_changed_flag := TRUE;
445 END IF;
446
447 IF NVL(p_new_def_acad_cal_type, -1) <>
448 NVL(p_old_Def_acad_cal_type, -1) THEN
449 v_acaih_rec.Def_acad_cal_type := p_old_Def_acad_cal_type;
450 v_changed_flag := TRUE;
451 END IF;
452
453 IF NVL(p_new_DEF_ACAD_CI_SEQ_NUM, -1) <>
454 NVL(p_old_DEF_ACAD_CI_SEQ_NUM, -1) THEN
455 v_acaih_rec.def_Acad_Ci_Sequence_Num := p_old_DEF_ACAD_CI_SEQ_NUM;
456 v_changed_flag := TRUE;
457 END IF;
458
459 IF NVL(p_new_DECLINE_OFR_REASON, 'NULL') <>
460 NVL(p_old_DECLINE_OFR_REASON, 'NULL') THEN
461 v_acaih_rec.DECLINE_OFR_REASON := p_old_DECLINE_OFR_REASON;
462 v_changed_flag := TRUE;
463 END IF;
464 -- end apadegal td001 igsm
465
466
467 OPEN cur_ad_ps_appl (p_person_id,p_admission_appl_number,p_nominated_course_cd);
468 FETCH cur_ad_ps_appl INTO v_acaih_rec.RECONSIDER_FLAG ;
469 CLOSE cur_ad_ps_appl;
470
471
475
472 IF v_changed_flag = TRUE THEN
473
474
476
477 -- Create and IGS_AD_PS_APLINSTHST history record.
478 v_acaih_rec.person_id := p_person_id;
479 v_acaih_rec.admission_appl_number :=p_admission_appl_number;
480 v_acaih_rec.nominated_course_cd := p_nominated_course_cd;
481 v_acaih_rec.sequence_number := p_sequence_number;
482 v_acaih_rec.hist_start_dt := p_old_update_on;
483 v_acaih_rec.hist_end_dt := p_new_update_on;
484 v_acaih_rec.hist_who := p_old_update_who;
485
486
487
488 OPEN c_latest_hist_dt;
489 FETCH c_latest_hist_dt INTO l_latest_hist_dt;
490 CLOSE c_latest_hist_dt;
491
492
493 IF l_latest_hist_dt.max_hist_start_dt IS NOT NULL THEN
494 IF (l_latest_hist_dt.max_hist_start_dt = p_old_update_on) THEN
495 -- add one second from the hist_start_dt value
496 -- to avoid a primary key constraint from occurring
497 -- when saving the record. Modified as part of Bug:2315674
498 v_acaih_rec.hist_start_dt := v_acaih_rec.hist_start_dt +1 / (60*24*60);
499 v_acaih_rec.hist_end_dt := v_acaih_rec.hist_end_dt +1 / (60*24*60);
500 ELSIF (l_latest_hist_dt.max_hist_start_dt > p_old_update_on) THEN
501 v_acaih_rec.hist_start_dt := l_latest_hist_dt.max_hist_start_dt +1 / (60*24*60);
502 IF (l_latest_hist_dt.max_hist_end_dt >= p_new_update_on) THEN
503 v_acaih_rec.hist_end_dt := l_latest_hist_dt.max_hist_end_dt + 1 / (60*24*60);
504 END IF;
505 END IF;
506 END IF;
507
508
509
510 l_org_id := igs_ge_gen_003.get_org_id;
511 IGS_AD_PS_APLINSTHST_Pkg.Insert_Row (
512 X_Mode => 'R',
513 X_RowId => lv_rowid,
514 X_Person_Id => v_acaih_rec.person_id,
515 X_Admission_Appl_Number => v_acaih_rec.admission_appl_number,
516 X_Nominated_Course_Cd => v_acaih_rec.nominated_course_cd,
517 X_Sequence_Number => v_acaih_rec.sequence_number,
518 X_Hist_Start_Dt => v_acaih_rec.hist_start_dt,
519 X_Hist_End_Dt => v_acaih_rec.hist_end_dt,
520 X_Hist_Who => v_acaih_rec.hist_who,
521 X_Hist_Offer_Round_Number => Null,
522 X_Adm_Cal_Type => v_acaih_rec.adm_cal_type,
523 X_Adm_Ci_Sequence_Number => v_acaih_rec.adm_ci_sequence_number,
524 X_Course_Cd => v_acaih_rec.course_cd,
525 X_Crv_Version_Number => v_acaih_rec.crv_version_number,
526 X_Location_Cd => v_acaih_rec.location_cd,
527 X_Attendance_Mode => v_acaih_rec.attendance_mode,
528 X_Attendance_Type => v_acaih_rec.attendance_type,
529 X_Unit_Set_Cd => v_acaih_rec.unit_set_cd,
530 X_Us_Version_Number => v_acaih_rec.us_version_number,
531 X_Preference_Number => v_acaih_rec.preference_number,
532 X_Adm_Doc_Status => v_acaih_rec.adm_doc_status,
533 X_Adm_Entry_Qual_Status => v_acaih_rec.adm_entry_qual_status,
534 X_Late_Adm_Fee_Status => v_acaih_rec.late_adm_fee_status,
535 X_Adm_Outcome_Status => v_acaih_rec.adm_outcome_status,
536 X_ADM_OTCM_STATUS_AUTH_PER_ID => v_acaih_rec.adm_otcm_status_auth_person_id,
537 X_Adm_Outcome_Status_Auth_Dt => v_acaih_rec.adm_outcome_status_auth_dt,
538 X_Adm_Outcome_Status_Reason => v_acaih_rec.adm_outcome_status_reason,
539 X_Offer_Dt => v_acaih_rec.offer_dt,
540 X_Offer_Response_Dt => v_acaih_rec.offer_response_dt,
541 X_Prpsd_Commencement_Dt => v_acaih_rec.prpsd_commencement_dt,
542 X_Adm_Cndtnl_Offer_Status => v_acaih_rec.adm_cndtnl_offer_status,
543 X_Cndtnl_Offer_Satisfied_Dt => v_acaih_rec.cndtnl_offer_satisfied_dt,
544 X_CNDTNL_OFR_MUST_BE_STSFD_IND => v_acaih_rec.cndtnl_offer_must_be_stsfd_ind,
545 X_Adm_Offer_Resp_Status => v_acaih_rec.adm_offer_resp_status,
546 X_Actual_Response_Dt => v_acaih_rec.actual_response_dt,
547 X_Adm_Offer_Dfrmnt_Status => v_acaih_rec.adm_offer_dfrmnt_status,
548 X_Deferred_Adm_Cal_Type => v_acaih_rec.deferred_adm_cal_type,
549 X_Deferred_Adm_Ci_Sequence_Num => v_acaih_rec.deferred_adm_ci_sequence_num,
550 X_Deferred_Tracking_Id => v_acaih_rec.deferred_tracking_id,
551 X_Ass_Rank => v_acaih_rec.ass_rank,
552 X_Secondary_Ass_Rank => v_acaih_rec.secondary_ass_rank,
553 X_INTRNTNL_ACCEPT_ADVICE_NUM => v_acaih_rec.intrntnl_acceptance_advice_num,
554 X_Ass_Tracking_Id => v_acaih_rec.ass_tracking_id,
555 X_Fee_Cat => v_acaih_rec.fee_cat,
556 X_Hecs_Payment_Option => v_acaih_rec.hecs_payment_option,
557 X_Expected_Completion_Yr => v_acaih_rec.expected_completion_yr,
558 X_Expected_Completion_Perd => v_acaih_rec.expected_completion_perd,
562 X_Applicant_Acptnce_Cndtn => v_acaih_rec.applicant_acptnce_cndtn,
559 X_Correspondence_Cat => v_acaih_rec.correspondence_cat,
560 X_Enrolment_Cat => v_acaih_rec.enrolment_cat,
561 X_Funding_Source => v_acaih_rec.funding_source,
563 X_Cndtnl_Offer_Cndtn => v_acaih_rec.cndtnl_offer_cndtn,
564 X_Org_Id => l_org_id,
565 X_Appl_inst_status => v_acaih_rec.appl_inst_status, --arvsrini igsm
566 X_DECISION_DATE => v_acaih_rec.DECISION_DATE, -- begin APADEGAL adtd001 igs.m
567 X_DECISION_MAKE_ID => v_acaih_rec.DECISION_MAKE_ID,
568 X_DECISION_REASON_ID => v_acaih_rec.DECISION_REASON_ID,
569 X_PENDING_REASON_ID => v_acaih_rec.PENDING_REASON_ID,
570 X_WAITLIST_STATUS => v_acaih_rec.WAITLIST_STATUS,
571 X_WAITLIST_RANK => v_acaih_rec.WAITLIST_RANK,
572 X_FUTURE_ACAD_CAL_TYPE => v_acaih_rec.FUTURE_ACAD_CAL_TYPE,
573 X_FUTURE_ACAD_CI_SEQUENCE_NUM => v_acaih_rec.FUTURE_ACAD_CI_SEQUENCE_NUM,
574 X_FUTURE_ADM_CAL_TYPE => v_acaih_rec.FUTURE_ADM_CAL_TYPE,
575 X_FUTURE_ADM_CI_SEQUENCE_NUM => v_acaih_rec.FUTURE_ADM_CI_SEQUENCE_NUM,
576 X_DEF_ACAD_CAL_TYPE => v_acaih_rec.DEF_ACAD_CAL_TYPE,
577 X_DEF_ACAD_CI_SEQUENCE_NUM => v_acaih_rec.DEF_ACAD_CI_SEQUENCE_NUM,
578 X_RECONSIDER_FLAG => v_acaih_rec.RECONSIDER_FLAG,
579 X_DECLINE_OFR_REASON => v_acaih_rec.DECLINE_OFR_REASON -- end APADEGAL adtd001 igs.m
580
581 );
582
583 END IF;
584 END;
585 EXCEPTION
586 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
587 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
588 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,'igs.plsql.igs_ad_gen_011.admp_ins_acai_hist.APP_EXP','Application Exception raised with code '||SQLCODE||' and error '||SQLERRM);
589 END IF;
590 WHEN OTHERS THEN
591
592 --Loop through all messages in stack to check if there is Security Policy exception already set or not. Ref: Bug 3919112
593 l_sc_msg_count := IGS_GE_MSG_STACK.COUNT_MSG;
594 WHILE l_sc_msg_count <> 0 loop
595 igs_ge_msg_stack.get(l_sc_msg_count, 'T', l_sc_encoded_text, l_sc_msg_index);
596 fnd_message.parse_encoded(l_sc_encoded_text, l_sc_app_short_name, l_sc_message_name);
597 IF l_sc_message_name = 'IGS_SC_POLICY_EXCEPTION' OR l_sc_message_name = 'IGS_SC_POLICY_UPD_DEL_EXCEP' THEN
598 --Raise the exception to Higher Level with out setting any Unhandled exception.
599 App_Exception.Raise_Exception;
600 END IF;
601 l_sc_msg_count := l_sc_msg_count - 1;
602 END LOOP;
603 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
604 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_011.admp_ins_acai_hist');
605 IGS_GE_MSG_STACK.ADD;
606 App_Exception.Raise_Exception;
607 END admp_ins_acai_hist;
608
609 Procedure Admp_Ins_Aca_Hist(
610 p_person_id IN NUMBER ,
611 p_admission_appl_number IN NUMBER ,
612 p_nominated_course_cd IN VARCHAR2 ,
613 p_new_transfer_course_cd IN VARCHAR2 ,
614 p_old_transfer_course_cd IN VARCHAR2 ,
615 p_new_basis_for_admission_type IN VARCHAR2 ,
616 p_old_basis_for_admission_type IN VARCHAR2 ,
617 p_new_admission_cd IN VARCHAR2 ,
618 p_old_admission_cd IN VARCHAR2 ,
619 p_new_course_rank_set IN VARCHAR2 ,
620 p_old_course_rank_set IN VARCHAR2 ,
621 p_new_course_rank_schedule IN VARCHAR2 ,
622 p_old_course_rank_schedule IN VARCHAR2 ,
623 p_new_req_for_reconsider_ind IN VARCHAR2 ,
624 p_old_req_for_reconsider_ind IN VARCHAR2 ,
625 p_new_req_for_adv_standing_ind IN VARCHAR2 ,
626 p_old_req_for_adv_standing_ind IN VARCHAR2 ,
627 p_new_update_who IN VARCHAR2 ,
628 p_old_update_who IN VARCHAR2 ,
629 p_new_update_on IN DATE ,
630 p_old_update_on IN DATE )
631 IS
632 gv_other_detail VARCHAR2(255);
633
634 --Local variables to check if the Security Policy exception already set or not. Ref: Bug 3919112
635 l_sc_encoded_text VARCHAR2(4000);
636 l_sc_msg_count NUMBER;
637 l_sc_msg_index NUMBER;
638 l_sc_app_short_name VARCHAR2(50);
639 l_sc_message_name VARCHAR2(50);
640
641 BEGIN -- admp_ins_aca_hist
642 -- Routine to create a history for the IGS_AD_PS_APPL table.
643 DECLARE
644 v_changed_flag BOOLEAN DEFAULT FALSE;
645 v_acah_rec IGS_AD_PS_APPL_HIST%ROWTYPE;
646 lv_rowid VARCHAR2(25);
647 l_org_id NUMBER(15);
648 BEGIN
649 -- Check if any of the old IGS_AD_PS_APPL values are different from the
650 -- associated new IGS_AD_PS_APPL values.
651 IF NVL(p_new_transfer_course_cd, 'NULL') <>
652 NVL(p_old_transfer_course_cd, 'NULL') THEN
653 v_acah_rec.transfer_course_cd := p_old_transfer_course_cd;
654 v_changed_flag := TRUE;
655 END IF;
656 IF NVL(p_new_basis_for_admission_type, 'NULL') <>
657 NVL(p_old_basis_for_admission_type, 'NULL') THEN
658 v_acah_rec.basis_for_admission_type := p_old_basis_for_admission_type;
659 v_changed_flag := TRUE;
660 END IF;
664 END IF;
661 IF NVL(p_new_admission_cd, 'NULL') <> NVL(p_old_admission_cd, 'NULL') THEN
662 v_acah_rec.admission_cd := p_old_admission_cd;
663 v_changed_flag := TRUE;
665 IF NVL(p_new_course_rank_set, 'NULL') <>
666 NVL(p_old_course_rank_set, 'NULL') THEN
667 v_acah_rec.course_rank_set := p_old_course_rank_set;
668 v_changed_flag := TRUE;
669 END IF;
670 IF NVL(p_new_course_rank_schedule, 'NULL') <>
671 NVL(p_old_course_rank_schedule, 'NULL') THEN
672 v_acah_rec.course_rank_schedule := p_old_course_rank_schedule;
673 v_changed_flag := TRUE;
674 END IF;
675 IF p_new_req_for_reconsider_ind <> p_old_req_for_reconsider_ind THEN
676 v_acah_rec.req_for_reconsideration_ind := p_old_req_for_reconsider_ind;
677 v_changed_flag := TRUE;
678 END IF;
679 IF p_new_req_for_adv_standing_ind <> p_old_req_for_adv_standing_ind THEN
680 v_acah_rec.req_for_adv_standing_ind := p_old_req_for_adv_standing_ind;
681 v_changed_flag := TRUE;
682 END IF;
683 IF v_changed_flag = TRUE THEN
684 v_acah_rec.person_id := p_person_id;
685 v_acah_rec.admission_appl_number := p_admission_appl_number;
686 v_acah_rec.nominated_course_cd := p_nominated_course_cd;
687 v_acah_rec.hist_start_dt := p_old_update_on;
688 v_acah_rec.hist_end_dt := p_new_update_on;
689 v_acah_rec.hist_who := p_old_update_who;
690 -- Ceate an IGS_AD_PS_APPL_HIST history record.
691 l_org_id := igs_ge_gen_003.get_org_id;
692 IGS_AD_PS_APPL_Hist_Pkg.Insert_Row (
693 X_Mode => 'R',
694 X_RowId => lv_rowid,
695 X_Hist_Who => v_acah_rec.hist_who,
696 X_Transfer_Course_Cd => v_acah_rec.transfer_course_cd,
697 X_Basis_For_Admission_Type => v_acah_rec.basis_for_admission_type,
698 X_Admission_Cd => v_acah_rec.admission_cd,
699 X_Course_Rank_Set => v_acah_rec.course_rank_set,
700 X_Course_Rank_Schedule => v_acah_rec.course_rank_schedule,
701 X_Req_For_Reconsideration_Ind => v_acah_rec.req_for_reconsideration_ind,
702 X_Req_For_Adv_Standing_Ind => v_acah_rec.req_for_adv_standing_ind,
703 X_Person_Id => v_acah_rec.person_id,
704 X_Admission_Appl_Number => v_acah_rec.admission_appl_number,
705 X_Nominated_Course_Cd => v_acah_rec.nominated_course_cd,
706 X_Hist_Start_Dt => v_acah_rec.hist_start_dt,
707 X_Hist_End_Dt => v_acah_rec.hist_end_dt,
708 X_Org_Id => l_org_id
709 );
710
711 END IF;
712 END;
713 EXCEPTION
714 WHEN OTHERS THEN
715 --Loop through all messages in stack to check if there is Security Policy exception already set or not. Ref: Bug 3919112
716 l_sc_msg_count := IGS_GE_MSG_STACK.COUNT_MSG;
717 WHILE l_sc_msg_count <> 0 loop
718 igs_ge_msg_stack.get(l_sc_msg_count, 'T', l_sc_encoded_text, l_sc_msg_index);
719 fnd_message.parse_encoded(l_sc_encoded_text, l_sc_app_short_name, l_sc_message_name);
720 IF l_sc_message_name = 'IGS_SC_POLICY_EXCEPTION' OR l_sc_message_name = 'IGS_SC_POLICY_UPD_DEL_EXCEP' THEN
721 --Raise the exception to Higher Level with out setting any Unhandled exception.
722 App_Exception.Raise_Exception;
723 END IF;
724 l_sc_msg_count := l_sc_msg_count - 1;
725 END LOOP;
726
727 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
728 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_011.admp_ins_aca_hist');
729 IGS_GE_MSG_STACK.ADD;
730 App_Exception.Raise_Exception;
731 END admp_ins_aca_hist;
732 --
733 --
734 --
735 Function Admp_Ins_Adm_Letter (
736 p_acad_cal_type IN VARCHAR2,
737 p_acad_ci_sequence_number IN NUMBER,
738 p_adm_cal_type IN VARCHAR2,
739 p_adm_ci_sequence_number IN NUMBER,
740 p_admission_cat IN VARCHAR2,
741 p_s_admission_process_type IN VARCHAR2,
742 p_correspondence_type IN VARCHAR2,
743 p_person_id IN NUMBER,
744 p_admission_appl_number IN NUMBER,
745 p_adm_outcome_status IN VARCHAR2,
746 p_message_name OUT NOCOPY VARCHAR2,
747 p_reference_number OUT NOCOPY NUMBER,
748 p_pgmofstudy IN VARCHAR2,
749 p_response_stat IN VARCHAR2,
750 p_resd_class IN VARCHAR2,
751 p_resd_stat IN VARCHAR2,
752 p_persid_grp IN NUMBER,
753 p_org_unit IN VARCHAR2,
754 p_sortby IN VARCHAR2
755 ) RETURN BOOLEAN IS
756 /*
757 || Created By : [email protected]
758 || Created On : 25-MAY-2001
759 || Purpose : Initialises the Old and New references for the columns of the table.
760 || Known limitations, enhancements or remarks :
761 || Change History :
762 || Who When What
766 --
763 || Kalyan.Dande 07-May-2001 Made modifications according to the DLD (Communicate Offer to Student).
764 || (reverse chronological order - newest change first)
765 */
767 -- Local Variables
768 --
769 gv_other_detail VARCHAR2(255);
770 gv_first_cori BOOLEAN DEFAULT TRUE;
771 gv_reference_number NUMBER DEFAULT NULL;
772 CURSOR c_igsco_itm_rf_num_s IS
773 SELECT igs_co_itm_rf_num_s.NEXTVAL
774 FROM dual;
775 CURSOR c_igs_co_ou_co_ref_seq_num_s IS
776 SELECT igs_co_ou_co_ref_seq_num_s.NEXTVAL
777 FROM dual;
778 FUNCTION Admpl_Process_Person (
779 p_person_id IN igs_ad_appl_ltr.person_id%TYPE,
780 p_admission_appl_number IN igs_ad_appl.admission_appl_number%TYPE,
781 p_correspondence_type IN igs_ad_appl_ltr.correspondence_type%TYPE,
782 p_aal_sequence_number IN igs_ad_appl_ltr.sequence_number%TYPE,
783 p_admission_cat IN igs_ad_appl.admission_cat%TYPE,
784 p_s_admission_process_type IN igs_ad_appl.s_admission_process_type%TYPE,
785 p_message_name OUT NOCOPY VARCHAR2
786 ) RETURN BOOLEAN IS
787 --
788 -- Local Variables for admpl_process_person.
789 --
790 gv_other_detail VARCHAR2(255);
791 e_resource_busy EXCEPTION;
792 l_org_id NUMBER(15);
793 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
794 BEGIN
795 DECLARE
796 v_letter_reference_number igs_co_s_ltr.letter_reference_number%TYPE;
797 v_spl_sequence_number igs_co_s_per_lt_parm.spl_sequence_number%TYPE;
798 v_create_dt DATE;
799 v_issue_dt DATE;
800 v_comments VARCHAR2(2000);
801 v_message_name VARCHAR2(30);
802 v_sl_record_found BOOLEAN DEFAULT FALSE;
803 lv_rowid VARCHAR2(25);
804 x_rowid VARCHAR2(25);
805 v_igs_co_itm_rf_num_s NUMBER;
806 v_igs_co_ou_co_ref_seq_num_s igs_co_ou_co_ref.sequence_number%TYPE;
807 CURSOR c_apcl_slet (
808 cp_admission_cat igs_ad_appl.admission_cat%TYPE,
809 cp_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE,
810 cp_correspondence_type igs_ad_appl_ltr.correspondence_type%TYPE
811 ) IS
812 SELECT apcl.letter_reference_number
813 FROM igs_ad_prcs_cat_ltr apcl,
814 igs_co_s_ltr slet
815 WHERE apcl.admission_cat = cp_admission_cat
816 AND apcl.s_admission_process_type = cp_s_admission_process_type
817 AND apcl.correspondence_type = cp_correspondence_type
818 AND apcl.correspondence_type = slet.correspondence_type
819 AND apcl.letter_reference_number = slet.letter_reference_number
820 AND slet.closed_ind = 'N';
821 CURSOR c_sl (
822 cp_correspondence_type igs_ad_appl_ltr.correspondence_type%TYPE
823 ) IS
824 SELECT sl.letter_reference_number
825 FROM igs_co_s_ltr sl
826 WHERE sl.correspondence_type = cp_correspondence_type
827 AND sl.closed_ind = 'N'
828 ORDER BY sl.letter_reference_number;
829 CURSOR c_cit (
830 cp_correspondence_type igs_co_itm.correspondence_type%TYPE,
831 cp_create_dt igs_co_itm.create_dt%TYPE
832 ) IS
833 SELECT cit.reference_number
834 FROM igs_co_itm cit
835 WHERE cit.correspondence_type = cp_correspondence_type
836 AND cit.create_dt = cp_create_dt;
837 CURSOR c_aal (
838 cp_person_id igs_ad_appl_ltr.person_id%TYPE,
839 cp_admission_appl_number igs_ad_appl.admission_appl_number%TYPE,
840 cp_correspondence_type igs_ad_appl_ltr.correspondence_type%TYPE,
841 cp_aal_sequence_number igs_ad_appl_ltr.sequence_number%TYPE
842 ) IS
843 SELECT 'x'
844 FROM igs_ad_appl_ltr aal
845 WHERE aal.person_id = cp_person_id
846 AND aal.admission_appl_number = cp_admission_appl_number
847 AND aal.correspondence_type = cp_correspondence_type
848 AND aal.sequence_number = cp_aal_sequence_number
849 FOR UPDATE OF aal.letter_reference_number, aal.spl_sequence_number NOWAIT;
850 BEGIN
851 --
852 -- Process the Person passed as parameter.
853 --
854 v_message_name := NULL;
855 l_org_id := igs_ge_gen_003.get_org_id;
856 --
857 -- Find the letter by looking at IGS_AD_PRCS_CAT_LTR.
858 --
859 OPEN c_apcl_slet (
860 p_admission_cat,
861 p_s_admission_process_type,
862 p_correspondence_type
863 );
864 FETCH c_apcl_slet INTO v_letter_reference_number;
865 IF (c_apcl_slet%NOTFOUND) THEN
866 CLOSE c_apcl_slet;
867 p_message_name := 'IGS_AD_LETTER_NOT_PRODUCED';
868 RETURN FALSE;
869 ELSE
870 CLOSE c_apcl_slet;
871 END IF;
872 --
873 -- Insert Person letter.
874 --
875 IF (igs_co_gen_002.corp_ins_spl_detail (
876 p_person_id,
880 v_spl_sequence_number,
877 p_correspondence_type,
878 v_letter_reference_number,
879 p_admission_appl_number || '|' || p_aal_sequence_number,
881 v_message_name
882 ) = FALSE) THEN
883 p_message_name := v_message_name;
884 RETURN FALSE;
885 END IF;
886 --
887 -- Get values we need to create correspondence item.
888 --
889 IF (gv_first_cori = TRUE) THEN
890 v_create_dt := SYSDATE; -- Note: Using the time component
891 --
892 -- Join all the parameters into one long string for the comments field
893 --
894 v_comments := 'Admission Calendar-'
895 || p_adm_cal_type || ' '
896 || IGS_GE_NUMBER.TO_CANN (p_adm_ci_sequence_number)
897 || ', Academic Calendar-'
898 || p_acad_cal_type || ' '
899 || IGS_GE_NUMBER.TO_CANN (p_acad_ci_sequence_number)
900 || ', Admission Category-'
901 || p_admission_cat
902 || ', System Admission Process Type-'
903 || p_s_admission_process_type
904 || ', Correspondence Type-'
905 || p_correspondence_type
906 || ', Person ID-'
907 || IGS_GE_NUMBER.TO_CANN (p_person_id)
908 || ', Admission Application Number-'
909 || IGS_GE_NUMBER.TO_CANN (p_admission_appl_number)
910 || ', Admission Outcome Status-'
911 || p_adm_outcome_status
912 || ', Program of Study-'
913 || p_pgmofstudy
914 || ', Response Status-'
915 || p_response_stat
916 || ', Residency Class-'
917 || p_resd_class
918 || ', Residency Status-'
919 || p_resd_stat
920 || ', Person ID Group-'
921 || IGS_GE_NUMBER.TO_CANN (p_persid_grp)
922 || ', Organization Unit-'
923 || p_org_unit
924 || ', Sort By-'
925 || p_sortby;
926 OPEN c_igsco_itm_rf_num_s;
927 FETCH c_igsco_itm_rf_num_s INTO v_igs_co_itm_rf_num_s;
928 IF c_igsco_itm_rf_num_s%NOTFOUND THEN
929 RAISE NO_DATA_FOUND;
930 END IF;
931 CLOSE c_igsco_itm_rf_num_s;
932 --
933 -- Insert a record in Correspondence Item table.
934 --
935 DECLARE
936 lv_rowid VARCHAR2(25);
937 BEGIN
938 igs_co_itm_pkg.insert_row (
939 x_rowid => lv_rowid,
940 x_org_id => fnd_profile.value ('ORG_ID'),
941 x_correspondence_type => p_correspondence_type,
942 x_reference_number => v_igs_co_itm_rf_num_s,
943 x_create_dt => v_create_dt,
944 x_originator_person_id => NULL,
945 x_request_num => NULL,
946 x_s_job_name => NULL,
947 x_request_job_id => NULL,
948 x_output_num => NULL,
949 x_request_job_run_id => NULL,
950 x_cal_type => NULL,
951 x_ci_sequence_number => NULL,
952 x_course_cd => NULL,
953 x_cv_version_number => NULL,
954 x_unit_cd => NULL,
955 x_uv_version_number => NULL,
956 x_comments => UPPER (v_comments),
957 x_job_request_id => NULL,
958 x_mode => 'R'
959 );
960
961 END;
962 gv_first_cori := FALSE;
963 --
964 -- Now get the reference number of the correspondence item we just created.
965 --
966 OPEN c_cit (
967 p_correspondence_type,
968 v_create_dt
969 );
970 FETCH c_cit INTO gv_reference_number;
971 IF (c_cit%NOTFOUND) THEN
972 CLOSE c_cit;
973 p_message_name := 'IGS_AD_CORITEM_NOTBE_FOUND';
974 RETURN FALSE;
975 END IF;
976 CLOSE c_cit;
977 --
978 -- Set outgoing parameter
979 --
980 p_reference_number := gv_reference_number;
981 END IF;
982 --
983 -- Insert outgoing correspondence
984 -- We want to wait until the time has changed to the next second
985 --
986 v_issue_dt := SYSDATE;
987 WHILE (v_issue_dt = SYSDATE) LOOP
988 NULL;
989 END LOOP;
990 v_issue_dt := SYSDATE; -- IGS_GE_NOTE. Using the time component
991 --
992 -- Insert a record in Outgoing Correspondence table.
993 --
994 DECLARE
995 lv_rowid VARCHAR2(25);
996 BEGIN
997 igs_co_ou_co_pkg.insert_row (
998 x_rowid => lv_rowid,
1002 x_issue_dt => v_issue_dt,
999 x_person_id => p_person_id,
1000 x_correspondence_type => p_correspondence_type,
1001 x_reference_number => gv_reference_number,
1003 x_dt_sent => NULL,
1004 x_unknown_return_dt => NULL,
1005 x_addr_type => NULL,
1006 x_tracking_id => NULL,
1007 x_comments => NULL,
1008 x_letter_reference_number => v_letter_reference_number,
1009 x_org_id => l_org_id,
1010 x_spl_sequence_number => v_spl_sequence_number,
1011 x_mode => 'R'
1012 );
1013 END;
1014 OPEN c_igs_co_ou_co_ref_seq_num_s;
1015 FETCH c_igs_co_ou_co_ref_seq_num_s INTO v_igs_co_ou_co_ref_seq_num_s;
1016 IF (c_igs_co_ou_co_ref_seq_num_s%NOTFOUND) THEN
1017 RAISE NO_DATA_FOUND;
1018 END IF;
1019 CLOSE c_igs_co_ou_co_ref_seq_num_s;
1020 --
1021 -- Insert a record in Outgoing Correspondence Reference table.
1022 --
1023 DECLARE
1024 lv_rowid VARCHAR2(25);
1025 BEGIN
1026 igs_co_ou_co_ref_pkg.insert_row (
1027 x_rowid => lv_rowid,
1028 x_person_id => p_person_id,
1029 x_org_id => l_org_id,
1030 x_correspondence_type => p_correspondence_type,
1031 x_reference_number => gv_reference_number,
1032 x_issue_dt => v_issue_dt,
1033 x_sequence_number => v_igs_co_ou_co_ref_seq_num_s,
1034 x_cal_type => NULL,
1035 x_ci_sequence_number => NULL,
1036 x_course_cd => NULL,
1037 x_cv_version_number => NULL,
1038 x_unit_cd => NULL,
1039 x_uv_version_number => NULL,
1040 x_s_other_reference_type => 'SPL_SEQNUM',
1041 x_other_reference => v_spl_sequence_number,
1042 x_mode => 'R'
1043 );
1044 END;
1045 --
1046 -- Update Admission Application Letter details.
1047 --
1048 FOR v_aal_rec IN c_aal (
1049 p_person_id,
1050 p_admission_appl_number,
1051 p_correspondence_type,
1052 p_aal_sequence_number
1053 )
1054 LOOP
1055 UPDATE igs_ad_appl_ltr
1056 SET letter_reference_number = v_letter_reference_number,
1057 spl_sequence_number = v_spl_sequence_number
1058 WHERE CURRENT OF c_aal;
1059 END LOOP;
1060 RETURN TRUE;
1061 END;
1062 EXCEPTION
1063 WHEN NO_DATA_FOUND THEN
1064 CLOSE c_igsco_itm_rf_num_s;
1065 CLOSE c_igs_co_ou_co_ref_seq_num_s;
1066 WHEN E_RESOURCE_BUSY THEN
1067 p_message_name := 'IGS_AD_LETTER_NOTUPD_LOCKING';
1068 RETURN FALSE;
1069 WHEN OTHERS THEN
1070 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1071 fnd_message.set_token ('NAME', 'IGS_AD_GEN_011.ADMPL_PROCESS_PERSON');
1072 igs_ge_msg_stack.add;
1073 app_exception.raise_exception;
1074 END Admpl_Process_Person;
1075 --
1076 --
1077 --
1078 BEGIN -- Begin of Admp_Ins_Adm_Letter Function.
1079 --
1080 -- This module creates letter and correspondence item details for either
1081 -- a single student or a batch of students.
1082 --
1083 DECLARE
1084 v_message_name VARCHAR2(30);
1085 v_false BOOLEAN DEFAULT FALSE;
1086 v_success_count NUMBER(5);
1087 CURSOR c_aa_aal (
1088 cp_person_id igs_ad_appl_ltr.person_id%TYPE,
1089 cp_admission_appl_number igs_ad_appl_ltr.admission_appl_number%TYPE,
1090 cp_correspondence_type igs_ad_appl_ltr.correspondence_type%TYPE
1091 ) IS
1092 SELECT aa.admission_cat,
1093 aa.s_admission_process_type,
1094 aal.sequence_number,
1095 aal.spl_sequence_number
1096 FROM igs_ad_appl aa,
1097 igs_ad_appl_ltr aal
1098 WHERE aa.person_id = cp_person_id
1099 AND aa.admission_appl_number = cp_admission_appl_number
1100 AND aal.person_id = aa.person_id
1101 AND aal.admission_appl_number = aa.admission_appl_number
1102 AND aal.correspondence_type = cp_correspondence_type
1103 AND aal.composed_ind = 'Y';
1104 /*
1105 ** Cursor was changed for enhancement# 1818444.
1106 */
1107 CURSOR c_aa_aal_acaiv (
1108 cp_acad_cal_type igs_ad_appl.acad_cal_type%TYPE,
1109 cp_acad_ci_sequence_number igs_ad_appl.acad_ci_sequence_number%TYPE,
1110 cp_adm_cal_type igs_ad_appl.adm_cal_type%TYPE,
1111 cp_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE,
1112 cp_admission_cat igs_ad_appl.admission_cat%TYPE,
1116 cp_pgm_of_study igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
1113 cp_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE,
1114 cp_correspondence_type igs_ad_appl_ltr.correspondence_type%TYPE,
1115 cp_adm_outcome_status igs_ad_ou_stat.adm_outcome_status%TYPE,
1117 cp_response_status igs_ad_ofr_resp_stat.adm_offer_resp_status%TYPE,
1118 cp_residency_class igs_pe_res_dtls_v.residency_class%TYPE,
1119 cp_residency_stat igs_pe_res_dtls_v.residency_status%TYPE,
1120 cp_org_unit_code igs_ps_ver.responsible_org_unit_cd%TYPE
1121 ) IS
1122 SELECT DISTINCT
1123 aa.person_id person_id,
1124 aa.admission_appl_number admission_appl_number,
1125 aal.correspondence_type correspondence_type,
1126 aal.sequence_number sequence_number,
1127 aa.admission_cat admission_cat,
1128 aa.s_admission_process_type s_admission_process_type,
1129 padv.city city,
1130 padv.county county,
1131 padv.country country,
1132 padv.postal_code postal_code
1133 FROM igs_ad_appl aa,
1134 igs_ad_appl_ltr aal,
1135 igs_ad_ps_appl_inst acaiv, --Bug 3150054 Replaced igs_ad_ps_appl_inst_aplinst_v with igs_ad_ps_appl_inst
1136 igs_ps_ver pv,
1137 igs_pe_addr_v padv,
1138 igs_pe_res_dtls_v prdv
1139 WHERE aa.acad_cal_type LIKE cp_acad_cal_type
1140 AND aa.acad_ci_sequence_number LIKE cp_acad_ci_sequence_number
1141 AND NVL(acaiv.adm_cal_type, aa.adm_cal_type) LIKE cp_adm_cal_type
1142 AND NVL(acaiv.adm_ci_sequence_number, aa.adm_ci_sequence_number) LIKE cp_adm_ci_sequence_number
1143 AND aa.admission_cat LIKE cp_admission_cat
1144 AND aa.s_admission_process_type LIKE cp_s_admission_process_type
1145 AND aa.person_id = aal.person_id
1146 AND aa.admission_appl_number = aal.admission_appl_number
1147 AND aal.correspondence_type = cp_correspondence_type
1148 AND aal.composed_ind = 'Y'
1149 AND acaiv.person_id = aa.person_id
1150 AND acaiv.admission_appl_number = aa.admission_appl_number
1151 AND pv.course_cd = acaiv.nominated_course_cd
1152 AND pv.version_number = acaiv.crv_version_number
1153 AND pv.responsible_org_unit_cd LIKE NVL (cp_org_unit_code, '%')
1154 AND acaiv.adm_outcome_status LIKE cp_adm_outcome_status
1155 AND acaiv.nominated_course_cd LIKE cp_pgm_of_study
1156 AND acaiv.adm_offer_resp_status LIKE cp_response_status
1157 AND aa.person_id = prdv.person_id(+)
1158 AND aa.person_id = padv.person_id (+)
1159 AND prdv.residency_class (+) LIKE cp_residency_class
1160 AND prdv.residency_status (+) LIKE cp_residency_stat
1161 AND NVL (acaiv.offer_dt, SYSDATE) <= SYSDATE;
1162 /*
1163 ** Reference Cursor was added for enhancement# 1818444.
1164 */
1165 TYPE c_aal_ref_cursor IS REF CURSOR;
1166 c_aal_refcur c_aal_ref_cursor;
1167 v_aa_aal_acaiv_rec c_aa_aal_acaiv%ROWTYPE;
1168 /*
1169 ** Cursor was added for enhancement# 1818444.
1170 */
1171 CURSOR c_aa_aal_persid (
1172 cp_correspondence_type igs_ad_appl_ltr.correspondence_type%TYPE,
1173 cp_person_id igs_ad_appl_ltr.person_id%TYPE
1174 ) IS
1175 SELECT DISTINCT
1176 aa.admission_appl_number,
1177 aa.admission_cat,
1178 aa.s_admission_process_type,
1179 aal.sequence_number,
1180 aal.spl_sequence_number
1181 FROM igs_ad_appl aa,
1182 igs_ad_appl_ltr aal,
1183 igs_pe_addr_v padv
1184 WHERE aa.person_id = aal.person_id
1185 AND aa.person_id = padv.person_id (+)
1186 AND aal.person_id = cp_person_id
1187 AND aal.correspondence_type = cp_correspondence_type;
1188 /*
1189 ** Reference Cursor was added for enhancement# 1818444.
1190 */
1191 TYPE ref_cursor_aa_aal_persid IS REF CURSOR;
1192 ref_cur_aa_aal_persid ref_cursor_aa_aal_persid;
1193 v_aa_aal_persid_rec c_aa_aal_persid%ROWTYPE;
1194 BEGIN
1195 --
1196 -- Explicitly use large rollback section as we will not commit until the very end.
1197 --
1198 COMMIT;
1199 SAVEPOINT sp_before;
1200 p_message_name := null;
1201 v_success_count := 0;
1202 gv_first_cori := TRUE;
1203 gv_reference_number := NULL;
1204 IF (p_person_id IS NOT NULL AND
1205 p_admission_appl_number IS NOT NULL AND
1206 p_correspondence_type IS NOT NULL) THEN
1207 FOR v_aa_aal_rec IN c_aa_aal (
1208 p_person_id,
1209 p_admission_appl_number,
1210 p_correspondence_type
1211 )
1212 LOOP
1213 IF (igs_ad_gen_002.admp_get_aal_sent_dt (
1214 p_person_id,
1215 p_admission_appl_number,
1216 p_correspondence_type,
1217 v_aa_aal_rec.sequence_number
1218 ) IS NULL) THEN
1222 p_correspondence_type,
1219 IF (admpl_process_person (
1220 p_person_id,
1221 p_admission_appl_number,
1223 v_aa_aal_rec.sequence_number,
1224 v_aa_aal_rec.admission_cat,
1225 v_aa_aal_rec.s_admission_process_type,
1226 v_message_name
1227 ) = FALSE) THEN
1228 v_false := TRUE;
1229 p_message_name := v_message_name;
1230 EXIT;
1231 END IF;
1232 END IF;
1233 END LOOP;
1234 IF (v_false = TRUE) THEN
1235 ROLLBACK TO sp_before;
1236 RETURN FALSE;
1237 END IF;
1238 /*
1239 ** Start of code for enhancement# 1818444.
1240 */
1241 ELSIF (p_persid_grp IS NOT NULL) THEN
1242 DECLARE
1243 --
1244 -- Cursor to select all the Person IDs that are part of the Person ID Group.
1245 --
1246 CURSOR cur_person_ids (
1247 cp_person_id_group IN igs_pe_persid_group_all.group_id%TYPE
1248 ) IS
1249 SELECT person_id
1250 FROM igs_pe_prsid_grp_mem_v
1251 WHERE group_id = cp_person_id_group AND
1252 NVL(TRUNC(start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
1253 NVL(TRUNC(end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
1254 BEGIN
1255 FOR lcur_person_ids IN cur_person_ids (
1256 p_persid_grp
1257 )
1258 LOOP
1259 OPEN ref_cur_aa_aal_persid FOR '
1260 SELECT DISTINCT
1261 aa.admission_appl_number admission_appl_number,
1262 aa.admission_cat admission_cat,
1263 aa.s_admission_process_type s_admission_process_type,
1264 aal.sequence_number sequence_number,
1265 aal.spl_sequence_number spl_sequence_number, padv.' || p_sortby ||
1266 ' FROM igs_ad_appl aa,
1267 igs_ad_appl_ltr aal,
1268 igs_pe_addr_v padv
1269 WHERE aa.person_id = aal.person_id
1270 AND aa.person_id = padv.person_id (+)
1271 AND aal.person_id = :1
1272 AND aal.correspondence_type = :2
1273 ORDER BY padv.' || p_sortby
1274 USING lcur_person_ids.person_id, p_correspondence_type ;
1275
1276 LOOP
1277 FETCH ref_cur_aa_aal_persid INTO v_aa_aal_persid_rec;
1278 EXIT WHEN ref_cur_aa_aal_persid%NOTFOUND;
1279 IF (igs_ad_gen_002.admp_get_aal_sent_dt (
1280 lcur_person_ids.person_id,
1281 v_aa_aal_persid_rec.admission_appl_number,
1282 p_correspondence_type,
1283 v_aa_aal_persid_rec.sequence_number
1284 ) IS NULL) THEN
1285 IF (admpl_process_person (
1286 lcur_person_ids.person_id,
1287 v_aa_aal_persid_rec.admission_appl_number,
1288 p_correspondence_type,
1289 v_aa_aal_persid_rec.sequence_number,
1290 v_aa_aal_persid_rec.admission_cat,
1291 v_aa_aal_persid_rec.s_admission_process_type,
1292 v_message_name
1293 ) = FALSE) THEN
1294 v_false := TRUE;
1295 p_message_name := v_message_name;
1296 EXIT;
1297 END IF;
1298 END IF;
1299 END LOOP;
1300 CLOSE ref_cur_aa_aal_persid;
1301 END LOOP;
1302 END;
1303 /*
1304 ** End of code for enhancement# 1818444.
1305 */
1306 ELSIF (p_person_id IS NULL AND p_persid_grp IS NULL) THEN -- Changed for enhancement# 1818444.
1307 /*
1308 ** Start of code change for enhancement# 1818444.
1309 */
1310 OPEN c_aal_refcur FOR '
1311 SELECT DISTINCT
1312 aa.person_id person_id,
1313 aa.admission_appl_number admission_appl_number,
1314 aal.correspondence_type correspondence_type,
1315 aal.sequence_number sequence_number,
1316 aa.admission_cat admission_cat,
1317 aa.s_admission_process_type s_admission_process_type,
1318 padv.city city,
1319 padv.county county,
1320 padv.country country,
1321 padv.postal_code postal_code
1322 FROM igs_ad_appl aa,
1323 igs_ad_appl_ltr aal,
1324 igs_ad_ps_appl_inst acaiv, --Bug 3150054 Replaced igs_ad_ps_appl_inst_aplinst_v with igs_ad_ps_appl_inst
1325 igs_ps_ver pv,
1326 igs_pe_addr_v padv,
1327 igs_pe_res_dtls_v prdv
1328 WHERE aa.acad_cal_type LIKE :1
1329 AND aa.acad_ci_sequence_number LIKE :2
1330 AND NVL(acaiv.adm_cal_type, aa.adm_cal_type) LIKE :3
1331 AND NVL(acaiv.adm_ci_sequence_number,aa.adm_ci_sequence_number) LIKE :4
1332 AND aa.admission_cat LIKE :5
1333 AND aa.s_admission_process_type LIKE :6
1334 AND aa.person_id = aal.person_id
1338 AND acaiv.person_id = aa.person_id
1335 AND aa.admission_appl_number = aal.admission_appl_number
1336 AND aal.correspondence_type = :7
1337 AND aal.composed_ind = ''Y''
1339 AND acaiv.admission_appl_number = aa.admission_appl_number
1340 AND pv.course_cd = acaiv.nominated_course_cd
1341 AND pv.version_number = acaiv.crv_version_number
1342 AND pv.responsible_org_unit_cd LIKE :8
1343 AND acaiv.adm_outcome_status LIKE :9
1344 AND acaiv.nominated_course_cd LIKE :10
1345 AND acaiv.adm_offer_resp_status LIKE :11
1346 AND aa.person_id = prdv.person_id(+)
1347 AND aa.person_id = padv.person_id (+)
1348 AND prdv.residency_class (+) LIKE :12
1349 AND prdv.residency_status (+) LIKE :13
1350 AND NVL (acaiv.offer_dt, SYSDATE) <= SYSDATE
1351 ORDER BY padv.' || p_sortby
1352 USING NVL (p_acad_cal_type, '%'), NVL (IGS_GE_NUMBER.TO_CANN (p_acad_ci_sequence_number), '%'), NVL (p_adm_cal_type, '%'),
1353 NVL (IGS_GE_NUMBER.TO_CANN (p_adm_ci_sequence_number), '%'), p_admission_cat, p_s_admission_process_type, p_correspondence_type,
1354 NVL (p_org_unit, '%'), p_adm_outcome_status, p_pgmofstudy, p_response_stat, p_resd_stat, p_resd_class
1355 ;
1356 LOOP
1357 FETCH c_aal_refcur INTO v_aa_aal_acaiv_rec;
1358 EXIT WHEN c_aal_refcur%NOTFOUND;
1359 IF (igs_ad_gen_002.admp_get_aal_sent_dt (
1360 v_aa_aal_acaiv_rec.person_id,
1361 v_aa_aal_acaiv_rec.admission_appl_number,
1362 p_correspondence_type,
1363 v_aa_aal_acaiv_rec.sequence_number
1364 ) IS NULL) THEN
1365 IF (admpl_process_person (
1366 v_aa_aal_acaiv_rec.person_id,
1367 v_aa_aal_acaiv_rec.admission_appl_number,
1368 v_aa_aal_acaiv_rec.correspondence_type,
1369 v_aa_aal_acaiv_rec.sequence_number,
1370 v_aa_aal_acaiv_rec.admission_cat,
1371 v_aa_aal_acaiv_rec.s_admission_process_type,
1372 v_message_name
1373 ) = FALSE) THEN
1374 fnd_message.set_name ('IGS', 'IGS_AD_LETTER_NOTUPD_LOCKING');
1375 igs_ge_msg_stack.add;
1376 app_exception.raise_exception;
1377 ELSE
1378 v_success_count := v_success_count + 1;
1379 END IF;
1380 END IF;
1381 END LOOP;
1382 CLOSE c_aal_refcur;
1383 /*
1384 ** End of code change for enhancement# 1818444.
1385 */
1386 END IF;
1387 COMMIT;
1388 RETURN TRUE;
1389 END;
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1393 ROLLBACK TO sp_before;
1394 fnd_message.set_token ('NAME', 'IGS_AD_GEN_011.ADMPL_INS_ADM_LETTER');
1395 igs_ge_msg_stack.add;
1396 app_exception.raise_exception;
1397 END Admp_Ins_Adm_Letter;
1398 --
1399 PROCEDURE Adms_Ins_Adm_Letter (
1400 errbuf OUT NOCOPY VARCHAR2,
1401 retcode OUT NOCOPY NUMBER,
1402 p_acad_perd IN VARCHAR2,
1403 p_adm_perd IN VARCHAR2,
1404 p_adm_cat IN VARCHAR2,
1405 p_s_adm_prcss_type IN VARCHAR2,
1406 p_correspondence_type IN VARCHAR2,
1407 p_adm_outcome_stat IN VARCHAR2,
1408 p_org_id IN NUMBER,
1409 p_pgmofstudy IN VARCHAR2,
1410 p_response_stat IN VARCHAR2,
1411 p_resd_class IN VARCHAR2,
1412 p_resd_stat IN VARCHAR2,
1413 p_persid_grp IN NUMBER,
1414 p_org_unit IN VARCHAR2,
1415 p_sortby IN VARCHAR2
1416 ) IS
1417 /*
1418 || Created By : [email protected]
1419 || Created On : 25-MAY-2001
1420 || Purpose : Initialises the Old and New references for the columns of the table.
1421 || Known limitations, enhancements or remarks :
1422 || Change History :
1423 || Who When What
1424 || Kalyan.Dande 07-May-2001 Made modifications according to the DLD (Communicate Offer to Student).
1425 || (reverse chronological order - newest change first)
1426 */
1427 p_admission_cat igs_ad_cat.admission_cat%TYPE;
1428 p_s_admission_process_type igs_lookups_view.lookup_code%TYPE;
1429 p_adm_outcome_status igs_ad_ou_stat.adm_outcome_status%TYPE;
1430 p_acad_cal_type igs_ca_inst.cal_type%TYPE;
1431 p_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1432 p_adm_cal_type igs_ca_inst.cal_type%TYPE;
1433 p_adm_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1434 /*
1435 ** Start of code for enhancement# 1818444.
1436 */
1437 p_program_of_study igs_ad_ps_appl_inst.nominated_course_cd%TYPE;
1438 p_response_status igs_ad_ofr_resp_stat.adm_offer_resp_status%TYPE;
1439 p_residency_class igs_pe_res_dtls_v.residency_class%TYPE;
1440 p_residency_status igs_pe_res_dtls_v.residency_status%TYPE;
1441 p_person_id_group igs_pe_persid_group_all.group_id%TYPE;
1442 p_organization_unit igs_ps_ver.responsible_org_unit_cd%TYPE;
1443 p_sort_by fnd_lookups.lookup_code%TYPE;
1444 /*
1445 ** End of code for enhancement# 1818444.
1446 */
1447 invalid_parameter EXCEPTION;
1448 p_message_name VARCHAR2(30);
1449 p_reference_number NUMBER;
1450 BEGIN
1451 p_person_id_group := p_persid_grp;
1452 --
1453 -- Set Organization context for populating Multi-Org tables.
1454 --
1455 igs_ge_gen_003.set_org_id (IGS_GE_NUMBER.TO_CANN (p_org_id));
1456 retcode := 0;
1457 --
1458 -- Handle NULL parameter values.
1459 --
1460 p_admission_cat := NVL (p_adm_cat, '%');
1461 p_s_admission_process_type := NVL (p_s_adm_prcss_type, '%');
1462 p_adm_outcome_status := NVL (p_adm_outcome_stat, '%');
1463 /*
1464 ** Start of code for enhancement# 1818444.
1465 */
1466 p_program_of_study := NVL (p_pgmofstudy, '%');
1467 p_response_status := NVL (p_response_stat, '%');
1468 p_residency_class := NVL (p_resd_class, '%');
1469 p_residency_status := NVL (p_resd_stat, '%');
1470 p_sort_by := NVL (p_sortby, 'POSTAL_CODE');
1471 --
1472 -- Check the combination of the parameters and raise error if the wrong combination is passed.
1473 -- The proper combination is as follows:
1474 -- a) Correspondence Type, Person ID Group, Sort By
1475 -- b) Correspondence Type, Admission Calendar, Academic Calendar, Admission Category,
1476 -- System Admission Process Type, Admission Application Number, Admission Outcome Status,
1477 -- Program of Study, Response Status, Residency Class, Residency Status, Organization Unit, Sort By
1478 --
1479 IF ((p_persid_grp IS NOT NULL) AND ((p_acad_perd IS NOT NULL) OR
1480 (p_adm_perd IS NOT NULL) OR
1481 (p_adm_cat IS NOT NULL) OR
1482 (p_s_adm_prcss_type IS NOT NULL) OR
1483 (p_adm_outcome_stat IS NOT NULL) OR
1484 (p_pgmofstudy IS NOT NULL) OR
1485 (p_response_stat IS NOT NULL) OR
1486 (p_resd_class IS NOT NULL) OR
1487 (p_resd_stat IS NOT NULL) OR
1488 (p_org_unit IS NOT NULL))) THEN
1489 errbuf := fnd_message.get_string ('IGS', 'IGS_GE_INVALID_COMBI_OF_PARAMS');
1490 RAISE invalid_parameter;
1491 END IF;
1492 --
1493 -- Raise an error if the no parameter other than Correspondence Type is passed.
1494 --
1495 IF ((p_persid_grp IS NULL) AND
1496 (p_acad_perd IS NULL) AND
1497 (p_adm_perd IS NULL) AND
1498 (p_adm_cat IS NULL) AND
1502 (p_response_stat IS NULL) AND
1499 (p_s_adm_prcss_type IS NULL) AND
1500 (p_adm_outcome_stat IS NULL) AND
1501 (p_pgmofstudy IS NULL) AND
1503 (p_resd_class IS NULL) AND
1504 (p_resd_stat IS NULL) AND
1505 (p_org_unit IS NULL)) THEN
1506 errbuf := fnd_message.get_string ('IGS', 'IGS_GE_INVALID_COMBI_OF_PARAMS');
1507 RAISE invalid_parameter;
1508 END IF;
1509 --
1510 -- Added the IF condition in enhancement# 1818444 to take care of parameters.
1511 --
1512 IF ((p_acad_perd IS NOT NULL) AND (p_adm_perd IS NOT NULL)) THEN
1513 --
1514 -- Extract Academic Calendar
1515 --
1516 p_acad_cal_type := RTRIM (SUBSTR (p_acad_perd, 101, 10));
1517 p_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM (RTRIM (SUBSTR (p_acad_perd, 112, 6)));
1518 --
1519 -- Extract Admission Calendar
1520 --
1521 p_adm_cal_type := RTRIM (SUBSTR (p_adm_perd, 101, 10));
1522 p_adm_ci_sequence_number := IGS_GE_NUMBER.TO_NUM (RTRIM (SUBSTR (p_adm_perd, 112, 6)));
1523 --
1524 -- Validate if the Academic and Admission Calendar are within Calendar Instance.
1525 --
1526 IF (igs_en_gen_014.enrs_get_within_ci (
1527 p_acad_cal_type,
1528 p_acad_ci_sequence_number,
1529 p_adm_cal_type,
1530 p_adm_ci_sequence_number,
1531 'N'
1532 ) = 'N') THEN
1533 errbuf := fnd_message.get_string ('IGS', 'IGS_GE_VAL_DOES_NOT_XS');
1534 RAISE invalid_parameter;
1535 END IF;
1536 END IF;
1537 /*
1538 ** End of code for enhancement# 1818444.
1539 */
1540 --
1541 -- Call Admp_Ins_Adm_Letter.
1542 -- Parameters were added during enhancement# 1818444.
1543 --
1544 IF (admp_ins_adm_letter (
1545 p_acad_cal_type,
1546 p_acad_ci_sequence_number,
1547 p_adm_cal_type,
1548 p_adm_ci_sequence_number,
1549 p_admission_cat,
1550 p_s_admission_process_type,
1551 p_correspondence_type,
1552 NULL,
1553 NULL,
1554 p_adm_outcome_status,
1555 p_message_name,
1556 p_reference_number,
1557 p_program_of_study,
1558 p_response_status,
1559 p_residency_class,
1560 p_residency_status,
1561 p_person_id_group,
1562 p_organization_unit,
1563 p_sort_by
1564 ) = TRUE) THEN
1565 retcode := 0;
1566 ELSE
1567 retcode := 2;
1568 errbuf := fnd_message.get_string ('IGS', p_message_name);
1569 END IF;
1570 EXCEPTION
1571 WHEN INVALID_PARAMETER THEN
1572 retcode := 2;
1573 WHEN OTHERS THEN
1574 errbuf := fnd_message.get_string ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1575 retcode := 2;
1576 igs_ge_msg_stack.conc_exception_hndl;
1577 END Adms_Ins_Adm_Letter;
1578
1579 --removed the function Admp_Ins_Eap_Cepi (bug 2664699) rghosh
1580
1581 --removed the function Admp_Ins_Eap_Eapc(bug 2664699) rghosh
1582
1583 --removed the Function Admp_Ins_Eap_Eltpi (bug 2664699) rghosh
1584
1585 --removed the function Admp_Ins_Eap_Eitpi for IGR migration (bug 4114493) sjlaport
1586
1587 Function Admp_Ins_Phrase_Splp(
1588 p_person_id IN NUMBER ,
1589 p_admission_appl_number IN NUMBER ,
1590 p_correspondence_type IN VARCHAR2 ,
1591 p_aal_sequence_number IN NUMBER ,
1592 p_letter_parameter_type IN VARCHAR2 ,
1593 p_letter_reference_number IN NUMBER ,
1594 p_spl_sequence_number IN NUMBER ,
1595 p_letter_repeating_group_cd IN VARCHAR2 ,
1596 p_splrg_sequence_number IN NUMBER,
1597 p_letter_order_number IN NUMBER )
1598 RETURN BOOLEAN IS
1599 gv_other_detail VARCHAR2(255);
1600 BEGIN -- admp_ins_phrase_splp
1601 -- This module calculates the value for an admissions phrase IGS_CO_LTR_PARAM
1602 -- and inserts a record into the IGS_CO_S_PER_LT_PARM table
1603 DECLARE
1604 v_stored BOOLEAN DEFAULT FALSE;
1605 v_value VARCHAR2(2000);
1606 v_sequence_number IGS_CO_S_PER_LT_PARM.sequence_number%TYPE;
1607
1608 CURSOR c_aalp (
1609 cp_person_id IGS_AD_APPL_LTR_PHR.person_id%TYPE,
1610 cp_adm_appl_number IGS_AD_APPL_LTR_PHR.admission_appl_number%TYPE,
1611 cp_correspondence_type IGS_AD_APPL_LTR_PHR.correspondence_type%TYPE,
1612 cp_aal_sequence_number IGS_AD_APPL_LTR_PHR.aal_sequence_number%TYPE,
1613 cp_letter_parameter_type IGS_AD_APPL_LTR_PHR.letter_parameter_type%TYPE) IS
1614 SELECT aalp.phrase_cd,
1615 aalp.phrase_text
1616 FROM IGS_AD_APPL_LTR_PHR aalp
1617 WHERE aalp.person_id = cp_person_id AND
1618 aalp.admission_appl_number = cp_adm_appl_number AND
1619 aalp.correspondence_type = cp_correspondence_type AND
1620 aalp.aal_sequence_number = cp_aal_sequence_number AND
1621 aalp.letter_parameter_type = cp_letter_parameter_type
1622 ORDER BY aalp.phrase_order_number,
1623 aalp.sequence_number;
1624 CURSOR c_ltp (
1625 cp_phrase_cd IGS_CO_LTR_PHR.phrase_cd%TYPE) IS
1626 SELECT ltp.phrase_text
1627 FROM IGS_CO_LTR_PHR ltp
1628 WHERE ltp.phrase_cd = cp_phrase_cd;
1629 CURSOR c_get_nxt_seq IS
1630 SELECT IGS_CO_S_PER_LT_PARM_SEQ_NUM_S.NEXTVAL
1631 FROM DUAL;
1632
1633 lv_rowid VARCHAR2(25);
1634
1635 BEGIN
1636 -- Initialise variables
1637 v_stored := FALSE;
1638 FOR v_aalp_rec IN c_aalp(
1639 p_person_id,
1640 p_admission_appl_number,
1641 p_correspondence_type,
1642 p_aal_sequence_number,
1643 p_letter_parameter_type) LOOP
1644 IF(v_aalp_rec.phrase_text IS NOT NULL) THEN
1645 v_value := v_aalp_rec.phrase_text;
1646 ELSE
1647 OPEN c_ltp(
1648 v_aalp_rec.phrase_cd);
1649 FETCH c_ltp INTO v_value;
1650 IF(c_ltp%NOTFOUND) THEN
1651 v_value := NULL;
1652 END IF;
1653 CLOSE c_ltp;
1654 END IF;
1655 IF(v_value IS NOT NULL) THEN
1656 -- Get IGS_CO_S_PER_LT_PARM_SEQ_NUM_S.NEXTVAL
1657 OPEN c_get_nxt_seq;
1658 FETCH c_get_nxt_seq INTO v_sequence_number;
1659 CLOSE c_get_nxt_seq;
1660 IGS_CO_S_PER_LT_Parm_Pkg.Insert_Row (
1661 X_Mode => 'R',
1662 X_RowId => lv_rowid,
1663 X_Person_Id => p_person_id,
1664 X_Correspondence_Type => p_correspondence_type,
1665 X_Letter_Reference_Number => p_letter_reference_number,
1666 X_Spl_Sequence_Number => p_spl_sequence_number,
1667 X_Letter_Parameter_Type => p_letter_parameter_type,
1668 X_Sequence_Number => v_sequence_number,
1669 X_Parameter_Value => v_value,
1670 X_Letter_Repeating_Group_Cd => p_letter_repeating_group_cd,
1671 X_Splrg_Sequence_Number => p_splrg_sequence_number,
1672 x_letter_order_number => p_letter_order_number,
1673 X_ORG_ID => FND_PROFILE.value('ORG_ID')
1674 );
1675
1676 v_stored := TRUE;
1677 END IF;
1678 END LOOP;
1679 RETURN v_stored;
1680 END;
1681 EXCEPTION
1682 WHEN OTHERS THEN
1683 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1684 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_011.admp_ins_phrase_splp');
1685 IGS_GE_MSG_STACK.ADD;
1686 App_Exception.Raise_Exception;
1687 END admp_ins_phrase_splp;
1688
1689 END igs_ad_gen_011;