[Home] [Help]
PACKAGE BODY: APPS.IGS_ADMAPPLICATION_PUB
Source
1 PACKAGE BODY igs_admapplication_pub AS
2 /* $Header: IGSPAPPB.pls 120.16 2006/09/22 13:21:43 pbondugu noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2 (30):='IGS_ADMAPPLICATION_PUB';
4
5 PROCEDURE check_length(p_param_name IN VARCHAR2, p_table_name IN VARCHAR2, p_param_length IN NUMBER) AS
6 CURSOR c_col_length IS
7 SELECT WIDTH , precision , column_type ,scale
8 FROM FND_COLUMNS
9 WHERE table_id IN
10 (SELECT TABLE_ID
11 FROM FND_TABLES
12 WHERE table_name = p_table_name AND APPLICATION_ID = 8405)
13 AND column_name = p_param_name
14 AND APPLICATION_ID = 8405;
15
16 l_col_length c_col_length%ROWTYPE;
17 begin
18 OPEN c_col_length;
19 FETCH c_col_length INTO l_col_length;
20 CLOSE c_col_length;
21 IF l_col_length.column_type = 'V' AND p_param_length > l_col_length.width THEN
22 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EXCEED_MAX_LENGTH');
23 FND_MESSAGE.SET_TOKEN('PARAMETER',p_param_name);
24 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.width);
25 IGS_GE_MSG_STACK.ADD;
26 RAISE FND_API.G_EXC_ERROR;
27
28
29 ELSIF l_col_length.column_type ='N' AND p_param_length > (l_col_length.precision - l_col_length.scale) THEN
30 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EXCEED_MAX_LENGTH');
31 FND_MESSAGE.SET_TOKEN('PARAMETER',p_param_name);
32 IF l_col_length.scale > 0 THEN
33 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.precision || ',' || l_col_length.scale);
34 ELSE
35 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.precision );
36 END IF;
37 IGS_GE_MSG_STACK.ADD;
38 RAISE FND_API.G_EXC_ERROR;
39 END IF;
40
41
42 END check_length;
43
44 --API
45 PROCEDURE RECORD_ACADEMIC_INDEX(
46 --Standard Parameters Start
47 p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
49 p_commit IN VARCHAR2 default FND_API.G_FALSE,
50 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54 --Standard parameter ends
55 p_person_id IN NUMBER,
56 p_admission_appl_number IN NUMBER,
57 p_nominated_program_cd IN VARCHAR2,
58 p_sequence_number IN NUMBER,
59 p_predicted_gpa IN NUMBER,
60 p_academic_index IN VARCHAR2,
61 p_calculation_date IN DATE
62 )
63 AS
64 l_api_version CONSTANT NUMBER := '1.0';
65 l_api_name CONSTANT VARCHAR2(30) := 'RECORD_ACADEMIC_INDEX';
66 l_msg_index NUMBER;
67 l_return_status VARCHAR2(1);
68 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
69
70
71
72
73 -- All records selected from application Instance Tables
74
75 CURSOR c_acai ( p_person_id NUMBER,
76 p_admission_appl_number NUMBER,
77 p_nominated_course_cd VARCHAR,
78 p_sequence_number NUMBER) IS
79 SELECT
80 a.*
81 FROM
82 igs_ad_ps_appl_inst a,
83 igs_ad_ou_stat c
84 WHERE a.person_id = p_person_id
85 AND a.admission_appl_number = p_admission_appl_number
86 AND a.nominated_course_cd = p_nominated_course_cd
87 AND a.sequence_number = p_sequence_number
88 AND a.adm_outcome_status = c.adm_outcome_status
89 AND c.s_adm_outcome_status = 'PENDING';
90
91 l_appl_inst_rec c_acai%ROWTYPE;
92 -- DOC Status
93
94 /* CURSOR c_doc_status(p_doc_status VARCHAR2) IS
95 SELECT 'x'
96 FROM igs_ad_doc_stat
97 WHERE s_adm_doc_status = 'SATISFIED'
98 AND adm_doc_status = p_doc_status;
99 l_doc_status c_doc_status%ROWTYPE;
100 */
101 l_academic_index VARCHAR2(10);
102 l_predicted_gpa NUMBER;
103 l_calculation_date DATE;
104 BEGIN
105 l_msg_index := 0;
106 SAVEPOINT RECORD_ACADEMIC_INDEX_pub;
107 -- Standard call to check for call compatibility.
108 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
109 RAISE FND_API.G_EXC_ERROR;
110 END IF;
111
112 -- Initialize message list if p_init_msg_list is set to TRUE.
113 IF FND_API.to_Boolean( p_init_msg_list ) THEN
114 FND_MSG_PUB.initialize;
115 END IF;
116 l_msg_index := igs_ge_msg_stack.count_msg;
117
118 -- Validate all the parameters for their length
119 -- PERSON_ID
120 check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
121 -- P_ADMISSION_APPL_NUMBER
122 check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
123 -- p_nominated_program_cd
124 check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
125 -- P_SEQUENCE_NUMBER
126 check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
127 -- P_ACADEMIC_INDEX
128 check_length('ACADEMIC_INDEX', 'IGS_AD_PS_APPL_INST_ALL', length(p_academic_index));
129 -- P_PREDICTED_GPA
130 check_length('PREDICTED_GPA', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_predicted_gpa)));
131 -- END OF PARAMETER VALIDATIONS.
132
133
134 -- Initialize API return status to success
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136
137
138 OPEN C_ACAI(p_person_id,
139 p_admission_appl_number,
140 p_nominated_program_cd,
141 p_sequence_number);
142
143 FETCH c_acai INTO l_appl_inst_rec;
144
145 IF c_acai%NOTFOUND THEN --If no application instance exists for this application
146 --with s_adm_outcome_status = 'PENDING'
147 ROLLBACK TO RECORD_ACADEMIC_INDEX_pub;
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ACDX_NO_APPL');
150 IGS_GE_MSG_STACK.ADD;
151 RAISE FND_API.G_EXC_ERROR;
152 ELSIF c_acai%FOUND THEN
153 ------------------------------
154 --Intialization of varable to handle G_MISS_CHAR/NUM/DATE
155 -------------------------------
156 IF p_academic_index = FND_API.G_MISS_CHAR THEN
157 l_academic_index := NULL;
158 ELSE
159 l_academic_index := NVL(p_academic_index, l_appl_inst_rec.academic_index);
160 END IF;
161
162 IF p_predicted_gpa = FND_API.G_MISS_NUM THEN
163 l_predicted_gpa := NULL;
164 ELSE
165 l_predicted_gpa := NVL(p_predicted_gpa, l_appl_inst_rec.predicted_gpa);
166 END IF;
167
168 IF p_calculation_date = FND_API.G_MISS_DATE THEN
169 l_calculation_date := NULL;
170 ELSE
171 l_calculation_date := NVL(p_calculation_date, l_appl_inst_rec.idx_calc_date);
172 END IF;
173
174
175 -- Fetch the documentation status
176 -- OPEN c_doc_status(l_appl_inst_rec.adm_doc_status);
177 -- FETCH c_doc_status INTO l_doc_status;
178 -- IF c_doc_status%FOUND THEN
179 -- BEGIN
180 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'E: Record found with SATISFIED STATUS');
181 -- Call to update row
182 igs_ad_ps_appl_inst_pkg.update_row(
183 x_rowid => l_appl_inst_rec.row_id,
184 x_person_id => l_appl_inst_rec.person_id,
185 x_admission_appl_number => l_appl_inst_rec.admission_appl_number,
186 x_nominated_course_cd => l_appl_inst_rec.nominated_course_cd,
187 x_sequence_number => l_appl_inst_rec.sequence_number,
188 x_predicted_gpa => l_predicted_gpa,
189 x_academic_index => l_academic_index,
190 x_adm_cal_type => l_appl_inst_rec.adm_cal_type,
191 x_app_file_location => l_appl_inst_rec.app_file_location,
192 x_adm_ci_sequence_number => l_appl_inst_rec.adm_ci_sequence_number,
193 x_course_cd => l_appl_inst_rec.course_cd,
194 x_app_source_id => l_appl_inst_rec.app_source_id,
195 x_crv_version_number => l_appl_inst_rec.crv_version_number,
196 x_waitlist_rank => l_appl_inst_rec.waitlist_rank,
197 x_waitlist_status => l_appl_inst_rec.waitlist_status,
198 x_location_cd => l_appl_inst_rec.location_cd,
199 x_attent_other_inst_cd => l_appl_inst_rec.attent_other_inst_cd,
200 x_attendance_mode => l_appl_inst_rec.attendance_mode,
201 x_edu_goal_prior_enroll_id => l_appl_inst_rec.edu_goal_prior_enroll_id,
202 x_attendance_type => l_appl_inst_rec.attendance_type,
203 x_decision_make_id => l_appl_inst_rec.decision_make_id,
204 x_unit_set_cd => l_appl_inst_rec.unit_set_cd,
205 x_decision_date => l_appl_inst_rec.decision_date,
206 x_attribute_category => l_appl_inst_rec.attribute_category,
207 x_attribute1 => l_appl_inst_rec.attribute1,
208 x_attribute2 => l_appl_inst_rec.attribute2,
209 x_attribute3 => l_appl_inst_rec.attribute3,
210 x_attribute4 => l_appl_inst_rec.attribute4,
211 x_attribute5 => l_appl_inst_rec.attribute5,
212 x_attribute6 => l_appl_inst_rec.attribute6,
213 x_attribute7 => l_appl_inst_rec.attribute7,
214 x_attribute8 => l_appl_inst_rec.attribute8,
215 x_attribute9 => l_appl_inst_rec.attribute9,
216 x_attribute10 => l_appl_inst_rec.attribute10,
217 x_attribute11 => l_appl_inst_rec.attribute11,
218 x_attribute12 => l_appl_inst_rec.attribute12,
219 x_attribute13 => l_appl_inst_rec.attribute13,
220 x_attribute14 => l_appl_inst_rec.attribute14,
221 x_attribute15 => l_appl_inst_rec.attribute15,
222 x_attribute16 => l_appl_inst_rec.attribute16,
223 x_attribute17 => l_appl_inst_rec.attribute17,
224 x_attribute18 => l_appl_inst_rec.attribute18,
225 x_attribute19 => l_appl_inst_rec.attribute19,
226 x_attribute20 => l_appl_inst_rec.attribute20,
227 x_decision_reason_id => l_appl_inst_rec.decision_reason_id,
228 x_us_version_number => l_appl_inst_rec.us_version_number,
229 x_decision_notes => l_appl_inst_rec.decision_notes,
230 x_pending_reason_id => l_appl_inst_rec.pending_reason_id,
231 x_preference_number => l_appl_inst_rec.preference_number,
232 x_adm_doc_status => l_appl_inst_rec.adm_doc_status,
233 x_adm_entry_qual_status => l_appl_inst_rec.adm_entry_qual_status,
234 x_deficiency_in_prep => l_appl_inst_rec.deficiency_in_prep,
235 x_late_adm_fee_status => l_appl_inst_rec.late_adm_fee_status,
236 x_spl_consider_comments => l_appl_inst_rec.spl_consider_comments,
237 x_apply_for_finaid => l_appl_inst_rec.apply_for_finaid,
238 x_finaid_apply_date => l_appl_inst_rec.finaid_apply_date,
239 x_adm_outcome_status => l_appl_inst_rec.adm_outcome_status,
240 x_adm_otcm_stat_auth_per_id => l_appl_inst_rec.adm_otcm_status_auth_person_id,
241 x_adm_outcome_status_auth_dt => l_appl_inst_rec.adm_outcome_status_auth_dt,
242 x_adm_outcome_status_reason => l_appl_inst_rec.adm_outcome_status_reason,
243 x_offer_dt => l_appl_inst_rec.offer_dt,
244 x_offer_response_dt => l_appl_inst_rec.offer_response_dt,
248 x_cndnl_ofr_must_be_stsfd_ind => l_appl_inst_rec.cndtnl_offer_must_be_stsfd_ind,
245 x_prpsd_commencement_dt => l_appl_inst_rec.prpsd_commencement_dt,
246 x_adm_cndtnl_offer_status => l_appl_inst_rec.adm_cndtnl_offer_status,
247 x_cndtnl_offer_satisfied_dt => l_appl_inst_rec.cndtnl_offer_satisfied_dt,
249 x_adm_offer_resp_status => l_appl_inst_rec.adm_offer_resp_status,
250 x_actual_response_dt => l_appl_inst_rec.actual_response_dt,
251 x_adm_offer_dfrmnt_status => l_appl_inst_rec.adm_offer_dfrmnt_status,
252 x_deferred_adm_cal_type => l_appl_inst_rec.deferred_adm_cal_type,
253 x_deferred_adm_ci_sequence_num => l_appl_inst_rec.deferred_adm_ci_sequence_num,
254 x_deferred_tracking_id => l_appl_inst_rec.deferred_tracking_id,
255 x_ass_rank => l_appl_inst_rec.ass_rank,
256 x_secondary_ass_rank => l_appl_inst_rec.secondary_ass_rank,
257 x_intr_accept_advice_num => l_appl_inst_rec.intrntnl_acceptance_advice_num,
258 x_ass_tracking_id => l_appl_inst_rec.ass_tracking_id,
259 x_fee_cat => l_appl_inst_rec.fee_cat,
260 x_hecs_payment_option => l_appl_inst_rec.hecs_payment_option,
261 x_expected_completion_yr => l_appl_inst_rec.expected_completion_yr,
262 x_expected_completion_perd => l_appl_inst_rec.expected_completion_perd,
263 x_correspondence_cat => l_appl_inst_rec.correspondence_cat,
264 x_enrolment_cat => l_appl_inst_rec.enrolment_cat,
265 x_funding_source => l_appl_inst_rec.funding_source,
266 x_applicant_acptnce_cndtn => l_appl_inst_rec.applicant_acptnce_cndtn,
267 x_cndtnl_offer_cndtn => l_appl_inst_rec.cndtnl_offer_cndtn,
268 x_ss_application_id => l_appl_inst_rec.ss_application_id,
269 x_ss_pwd => l_appl_inst_rec.ss_pwd,
270 x_authorized_dt => l_appl_inst_rec.authorized_dt,
271 x_authorizing_pers_id => l_appl_inst_rec.authorizing_pers_id,
272 x_entry_status => l_appl_inst_rec.entry_status,
273 x_entry_level => l_appl_inst_rec.entry_level,
274 x_sch_apl_to_id => l_appl_inst_rec.sch_apl_to_id,
275 x_idx_calc_date => l_calculation_date,
276 X_FUT_ACAD_CAL_TYPE => l_appl_inst_rec.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
277 X_FUT_ACAD_CI_SEQUENCE_NUMBER => l_appl_inst_rec.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
278 X_FUT_ADM_CAL_TYPE => l_appl_inst_rec.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
279 X_FUT_ADM_CI_SEQUENCE_NUMBER => l_appl_inst_rec.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
280 X_PREV_TERM_ADM_APPL_NUMBER => l_appl_inst_rec.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
281 X_PREV_TERM_SEQUENCE_NUMBER => l_appl_inst_rec.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
282 X_FUT_TERM_ADM_APPL_NUMBER => l_appl_inst_rec.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
283 X_FUT_TERM_SEQUENCE_NUMBER => l_appl_inst_rec.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
284 X_DEF_ACAD_CAL_TYPE => l_appl_inst_rec.DEF_ACAD_CAL_TYPE, --Bug 2395510
285 X_DEF_ACAD_CI_SEQUENCE_NUM => l_appl_inst_rec.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
286 X_DEF_PREV_TERM_ADM_APPL_NUM => l_appl_inst_rec.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
287 X_DEF_PREV_APPL_SEQUENCE_NUM => l_appl_inst_rec.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
288 X_DEF_TERM_ADM_APPL_NUM => l_appl_inst_rec.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
289 X_DEF_APPL_SEQUENCE_NUM => l_appl_inst_rec.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
290 x_mode => 'R',
291 x_attribute21 => l_appl_inst_rec.attribute21,
292 x_attribute22 => l_appl_inst_rec.attribute22,
293 x_attribute23 => l_appl_inst_rec.attribute23,
294 x_attribute24 => l_appl_inst_rec.attribute24,
295 x_attribute25 => l_appl_inst_rec.attribute25,
296 x_attribute26 => l_appl_inst_rec.attribute26,
297 x_attribute27 => l_appl_inst_rec.attribute27,
298 x_attribute28 => l_appl_inst_rec.attribute28,
299 x_attribute29 => l_appl_inst_rec.attribute29,
300 x_attribute30 => l_appl_inst_rec.attribute30,
301 x_attribute31 => l_appl_inst_rec.attribute31,
302 x_attribute32 => l_appl_inst_rec.attribute32,
303 x_attribute33 => l_appl_inst_rec.attribute33,
304 x_attribute34 => l_appl_inst_rec.attribute34,
305 x_attribute35 => l_appl_inst_rec.attribute35,
306 x_attribute36 => l_appl_inst_rec.attribute36,
307 x_attribute37 => l_appl_inst_rec.attribute37,
308 x_attribute38 => l_appl_inst_rec.attribute38,
309 x_attribute39 => l_appl_inst_rec.attribute39,
310 x_attribute40 => l_appl_inst_rec.attribute40,
311 X_APPL_INST_STATUS => l_appl_inst_rec.appl_inst_status,
312 x_ais_reason => l_appl_inst_rec.ais_reason,
313 x_decline_ofr_reason => l_appl_inst_rec.decline_ofr_reason
314
315 );
316 -- END;
317 -- ELSIF c_doc_status%NOTFOUND THEN -- DOC STATUS
318 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'C: No record found with SATISFIED STATUS');
319 -- ROLLBACK TO RECORD_ACADEMIC_INDEX_pub;
320 -- x_return_status := FND_API.G_RET_STS_ERROR;
321 -- FND_MESSAGE.SET_NAME('IGS','IGS_AD_DOC_STATUS_INCORRECT');
322 -- IGS_GE_MSG_STACK.ADD;
323 -- RAISE FND_API.G_EXC_ERROR;
324 -- END IF;--IF c_doc_status%NOTFOUND THEN
325 -- IF c_doc_status%ISOPEN THEN
326 -- CLOSE c_doc_status;
327 -- END IF ;
328 END IF;-- of C_ACAI%NOTFOUND
329 CLOSE c_acai;
330 -- IF c_doc_status%ISOPEN THEN
331 -- CLOSE c_doc_status;
332 -- END IF ;
333 -- Standard check of p_commit.
334 IF FND_API.To_Boolean( p_commit ) THEN
335 COMMIT WORK;
336 END IF;
337 -- Standard call to get message count and if count is 1, get message info.
338 EXCEPTION
339 WHEN FND_API.G_EXC_ERROR THEN
340 ROLLBACK TO RECORD_ACADEMIC_INDEX_pub;
344 p_return_status => l_return_status,
341 x_return_status := FND_API.G_RET_STS_ERROR ;
342 igs_ad_gen_016.extract_msg_from_stack (
343 p_msg_at_index => l_msg_index,
345 p_msg_count => x_msg_count,
346 p_msg_data => x_msg_data,
347 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
348 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
349 x_msg_count := x_msg_count-1;
350 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'aFTER STACK Exception in API: FND_API.G_EXC_ERROR : '|| l_hash_msg_name_text_type_tab(x_msg_count-2).text);
351 IF c_acai%ISOPEN THEN
352 CLOSE c_acai;
353 END IF;
354 -- IF c_doc_status%ISOPEN THEN
355 -- CLOSE c_doc_status;
356 -- END IF ;
357
358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359 ROLLBACK TO RECORD_ACADEMIC_INDEX_PUB;
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
362 p_data => x_msg_data);
363
364 WHEN OTHERS THEN
365 ROLLBACK TO RECORD_ACADEMIC_INDEX_pub;
366 igs_ad_gen_016.extract_msg_from_stack (
367 p_msg_at_index => l_msg_index,
368 p_return_status => l_return_status,
369 p_msg_count => x_msg_count,
370 p_msg_data => x_msg_data,
371 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
372 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
373 x_return_status := FND_API.G_RET_STS_ERROR ;
374 ELSE
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376 END IF;
377 IF c_acai%ISOPEN THEN
378 CLOSE c_acai;
379 END IF;
380 -- IF c_doc_status%ISOPEN THEN
381 -- CLOSE c_doc_status;
382 -- END IF ;
383
384 END RECORD_ACADEMIC_INDEX;
385
386
387
388 PROCEDURE Record_Outcome_AdmApplication(p_api_version IN NUMBER,
389 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
390 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
391 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
392
393 p_person_id NUMBER,
394 p_admission_appl_number NUMBER,
395 p_nominated_program_cd VARCHAR2,
396 p_sequence_number NUMBER,
397
398 p_adm_outcome_status VARCHAR2,
399 p_decision_maker_id NUMBER,
400 p_decision_date DATE,
401 p_decision_reason_id NUMBER DEFAULT NULL,
402 p_pending_reason_id NUMBER DEFAULT NULL,
403 p_offer_dt DATE DEFAULT NULL,
404 -- Columns for Override Outcome
405 p_adm_outcome_status_auth_dt DATE DEFAULT NULL,
406 p_adm_otcm_status_auth_per_id NUMBER DEFAULT NULL,
407 p_adm_outcome_status_reason VARCHAR2 DEFAULT NULL,
408 -- Columns for Conditional Offer Status
409 p_adm_cndtnl_offer_status VARCHAR2 DEFAULT NULL,
410 p_cndtnl_offer_cndtn VARCHAR2 DEFAULT NULL,
411 p_cndtl_offer_must_stsfd_ind VARCHAR2 DEFAULT NULL,
412 p_cndtnl_offer_satisfied_dt DATE DEFAULT NULL,
413
414 p_offer_response_dt DATE DEFAULT NULL,
415 p_reconsider_flag VARCHAR2 DEFAULT 'N',
416 p_prpsd_commencement_date DATE DEFAULT NULL,
417 p_ucas_transaction VARCHAR2 DEFAULT 'N',
418
419 x_return_status OUT NOCOPY VARCHAR2,
420 x_msg_count OUT NOCOPY NUMBER,
421 x_msg_data OUT NOCOPY VARCHAR2
422
423 ) AS
424
425 CURSOR c_appl_cur IS
426 SELECT a.ROWID, a.*
427 FROM IGS_AD_APPL a
428 WHERE person_id = p_person_id
429 AND admission_appl_number = p_admission_appl_number;
430 l_c_appl_cur c_appl_cur%ROWTYPE;
431
432 CURSOR c_aplinst_cur IS
433 SELECT a.ROWID, a.*, b.req_for_reconsideration_ind
434 FROM igs_ad_ps_appl_inst a, IGS_AD_PS_APPL b
435 WHERE a.person_id = p_person_id
436 AND a.admission_appl_number = p_admission_appl_number
437 AND a.nominated_course_cd = p_nominated_program_cd
438 AND a.sequence_number = p_sequence_number
439 AND a.person_id = b.person_id
440 AND a.admission_appl_number = b.admission_appl_number
441 AND a.nominated_course_cd = b.nominated_course_cd;
442
443 CURSOR c_ps_appl_cur IS
444 SELECT a.*
445 FROM igs_ad_ps_appl a
446 WHERE person_id = p_person_id
447 AND admission_appl_number = p_admission_appl_number
448 AND nominated_course_cd = p_nominated_program_cd;
449
450 CURSOR c_check_reconsider(cp_admission_cat IGS_AD_PRCS_CAT_STEP.ADMISSION_CAT%TYPE, cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
451 SELECT 'X'
452 FROM IGS_AD_PRCS_CAT_STEP
453 WHERE admission_cat = cp_admission_cat
457 CURSOR c_apcs(cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE, cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
454 AND s_admission_process_type = cp_s_admission_process_type
455 AND s_admission_step_type = 'RECONSIDER';
456
458 SELECT 'Y'
459 FROM IGS_AD_PRCS_CAT_STEP
460 WHERE admission_cat = cp_admission_cat
461 AND s_admission_process_type = cp_s_admission_process_type
462 AND s_admission_step_type = 'PRE-ENROL'
463 AND step_group_type <> 'TRACK';
464
465 CURSOR c_adm_ofr_resp_stat_cur IS
466 SELECT a.adm_offer_resp_status
467 FROM igs_ad_ps_appl_inst a
468 WHERE person_id = p_person_id
469 AND admission_appl_number = p_admission_appl_number
470 AND nominated_course_cd = p_nominated_program_cd
471 AND sequence_number = p_sequence_number;
472
473 CURSOR c_person_id(cp_party_number hz_parties.party_number%TYPE) IS
474 SELECT party_id FROM hz_parties WHERE party_number = cp_party_number;
475 l_adm_otcm_status_auth_per_id hz_parties.party_id%TYPE;
476 l_check_reconsider VARCHAR2(1);
477 l_pre_enroll VARCHAR2(2);
478 l_c_aplinst_cur c_aplinst_cur%ROWTYPE;
479 l_c_ps_appl_cur c_ps_appl_cur%ROWTYPE;
480
481 l_api_name CONSTANT VARCHAR2(30) := 'Record_Outcome_AdmApplication';
482 l_api_version CONSTANT NUMBER := 1.1;
483 l_message_name VARCHAR2(80);
484
485 l_decision_make_id NUMBER(15) ;
486 l_decision_date DATE ;
487 l_decision_reason_id NUMBER(15) ;
488 l_pending_reason_id NUMBER(15) ;
489 l_offer_dt DATE ;
490 l_offer_response_dt DATE ;
491 l_reconsider_flag VARCHAR2(1) ;
492 l_prpsd_commencement_date DATE ;
493 l_cndtnl_offer_cndtn VARCHAR2(2000) ;
494 l_cndtl_offer_must_stsfd_ind VARCHAR2(1) ;
495 l_actual_response_dt igs_ad_ps_appl_inst.actual_response_dt%TYPE;
496 l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE ;
497 l_adm_offer_resp_status igs_ad_ps_appl_inst.adm_offer_resp_status%TYPE ;
498 l_cndtnl_offer_satisfied_dt igs_ad_ps_appl_inst.cndtnl_offer_satisfied_dt%TYPE ;
499 l_adm_cndtl_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE ;
500 l_adm_outcome_status_auth_dt igs_ad_ps_appl_inst.adm_outcome_status_auth_dt%TYPE;
501 l_adm_otcm_status_auth_per_num hz_parties.party_number%TYPE;
502 l_adm_outcome_status_reason igs_ad_ps_appl_inst.adm_outcome_status_reason%TYPE;
503
504 l_uc_tran_id igs_uc_transactions.uc_tran_id%TYPE;
505 l_s_adm_outcome_status igs_ad_ou_Stat.s_adm_outcome_status%TYPE;
506
507 l_req_for_reconsideration_ind VARCHAR2(1);
508 v_message_name VARCHAR2(2000);
509
510 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
511 l_msg_index NUMBER := 0;
512
513 --begin apadegal ADTD001 IGS.M
514 lv_return_status VARCHAR2(10);
515 lv_msg_count NUMBER;
516 lv_msg_data VARCHAR2(1000);
517 lv_msg_nme varchar2(2000);
518 l_recon_unchecked BOOLEAN DEFAULT FALSE;
519
520 --end apadegal ADTD001 IGS.M
521
522
523 BEGIN
524
525 -- Standard Start of API savepoint
526 SAVEPOINT Record_Outcome_AdmAppl_pub;
527 l_msg_index := igs_ge_msg_stack.count_msg;
528
529 -- Initialize message list if p_init_msg_list is set to TRUE.
530 IF FND_API.to_Boolean(p_init_msg_list) THEN
531 FND_MSG_PUB.initialize;
532 END IF;
533
534 -- Standard call to check for call compatibility.
535 IF NOT FND_API.Compatible_API_Call(l_api_version,
536 p_api_version,
537 l_api_name,
538 G_PKG_NAME) THEN
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541
542
543 -- Validate all the parameters for their length
544 -- PERSON_ID
545 check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
546 -- P_ADMISSION_APPL_NUMBER
547 check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
548 -- p_nominated_program_cd
549 check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
550 -- P_SEQUENCE_NUMBER
551 check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
552 -- P_ADM_OUTCOME_STATUS
553 check_length('ADM_OUTCOME_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status));
554 -- P_DECISION_MAKER_ID
555 check_length('DECISION_MAKE_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_maker_id)));
556 -- P_DECISION_REASON_ID
557 check_length('DECISION_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_reason_id)));
558 -- P_PENDING_REASON_ID
559 check_length('PENDING_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_pending_reason_id)));
560 -- P_ADM_OTCM_STATUS_AUTH_PER_ID
561 check_length('ADM_OTCM_STATUS_AUTH_PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_adm_otcm_status_auth_per_id)));
562 -- P_ADM_OUTCOME_STATUS_REASON
563 check_length('ADM_OUTCOME_STATUS_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status_reason));
564 -- P_ADM_CNDTNL_OFFER_STATUS
565 check_length('ADM_CNDTNL_OFFER_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_cndtnl_offer_status));
566 -- P_CNDTNL_OFFER_CNDTN
567 check_length('CNDTNL_OFFER_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_cndtnl_offer_cndtn));
568 -- P_CNDTL_OFFER_MUST_STSFD_IND
569 check_length('CNDTNL_OFFER_MUST_BE_STSFD_IND', 'IGS_AD_PS_APPL_INST_ALL', length(p_cndtl_offer_must_stsfd_ind));
570 -- P_RECONSIDER_FLAG
574 -- Initialize API return status to success
571 check_length('REQ_FOR_RECONSIDERATION_IND', 'IGS_AD_PS_APPL', length(p_reconsider_flag));
572
573
575 x_return_status := FND_API.G_RET_STS_SUCCESS;
576 -- API body
577
578 -- Check whether any application is available in OSS to update outcome status
579 -- if the corresponding application is not there , then update the interface record with appropriate error code
580 OPEN c_appl_cur;
581 FETCH c_appl_cur INTO l_c_appl_cur;
582 CLOSE c_appl_cur;
583
584
585 OPEN c_aplinst_cur;
586 FETCH c_aplinst_cur
587 INTO l_c_aplinst_cur;
588 CLOSE c_aplinst_cur;
589
590 IF l_c_aplinst_cur.person_id IS NULL THEN
591 fnd_message.set_name('IGS', 'IGS_AD_DECISION_DTLS_INVALID');
592 IGS_GE_MSG_STACK.ADD;
593 RAISE FND_API.G_EXC_ERROR;
594 END IF;
595 ---------------------------------------------------------------
596 -- Start Code to Check and assign the Missing Fields/ Nullified Filelds
597 ---------------------------------------------------------------
598 IF p_adm_outcome_status = FND_API.G_MISS_CHAR OR p_adm_outcome_status IS NULL THEN
599 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
600 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_OUTCOME_STATUS'));
601 IGS_GE_MSG_STACK.ADD;
602 RAISE FND_API.G_EXC_ERROR;
603 ELSE
604 l_adm_outcome_status := p_adm_outcome_status;
605 END IF;
606 l_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS(l_adm_outcome_status);
607
608 IF p_decision_maker_id = FND_API.G_MISS_NUM THEN
609 l_decision_make_id := null;
610 ELSE
611 l_decision_make_id := NVL( p_decision_maker_id, l_c_aplinst_cur.decision_make_id);
612 END IF;
613 IF p_decision_date = FND_API.G_MISS_DATE THEN
614 l_decision_date := null;
615 ELSE
616 l_decision_date := NVL( p_decision_date, l_c_aplinst_cur.decision_date);
617 END IF;
618 IF p_decision_reason_id = FND_API.G_MISS_NUM THEN
619 l_decision_reason_id := null;
620 ELSE
621 l_decision_reason_id := NVL( p_decision_reason_id, l_c_aplinst_cur.decision_reason_id);
622 END IF;
623 IF p_pending_reason_id = FND_API.G_MISS_NUM THEN
624 l_pending_reason_id := null;
625 ELSE
626 l_pending_reason_id := NVL( p_pending_reason_id, l_c_aplinst_cur.pending_reason_id);
627 END IF;
628
629 IF p_offer_dt = FND_API.G_MISS_DATE THEN
630 l_offer_dt := null;
631 ELSIF l_s_adm_outcome_status IN ('OFFER', 'COND-OFFER') THEN
632 l_offer_dt := NVL (NVL( p_offer_dt, l_c_aplinst_cur.offer_dt), SYSDATE);
633 END IF;
634 IF p_offer_response_dt = FND_API.G_MISS_DATE THEN
635 l_offer_response_dt := null;
636 ELSE
637 l_offer_response_dt := NVL( p_offer_response_dt, l_c_aplinst_cur.offer_response_dt);
638 END IF;
639
640
641 IF p_reconsider_flag = FND_API.G_MISS_CHAR OR p_reconsider_flag NOT IN ('Y', 'N') THEN
642 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE_WITH_CTXT');
643 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_REQ_RECONS_IND'));
644 IGS_GE_MSG_STACK.ADD;
645 RAISE FND_API.G_EXC_ERROR;
646 ELSE
647 l_reconsider_flag := NVL(p_reconsider_flag, l_c_aplinst_cur.req_for_reconsideration_ind);
648 END IF;
649
650 IF p_prpsd_commencement_date = FND_API.G_MISS_DATE OR l_s_adm_outcome_status NOT IN ('OFFER', 'COND-OFFER')THEN
651 l_prpsd_commencement_date := NULL;
652 ELSE
653 l_prpsd_commencement_date := NVL(p_prpsd_commencement_date, l_c_aplinst_cur.prpsd_commencement_dt);
654 END IF;
655 IF p_adm_cndtnl_offer_status = FND_API.G_MISS_CHAR THEN
656 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE_WITH_CTXT');
657 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_COND_OFR_STATUS'));
658 IGS_GE_MSG_STACK.ADD;
659 RAISE FND_API.G_EXC_ERROR;
660 ELSE
661 l_adm_cndtl_offer_status := p_adm_cndtnl_offer_status;
662 END IF;
663
664 IF p_cndtnl_offer_cndtn = FND_API.G_MISS_CHAR THEN
665 l_cndtnl_offer_cndtn := NULL;
666 ELSE
667 l_cndtnl_offer_cndtn := NVL(p_cndtnl_offer_cndtn, l_c_aplinst_cur.cndtnl_offer_cndtn);
668 END IF;
669
670 IF p_cndtl_offer_must_stsfd_ind = FND_API.G_MISS_CHAR
671 OR p_cndtl_offer_must_stsfd_ind NOT IN ('Y', 'N') THEN
672
673 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE_WITH_CTXT');
674 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CND_OFR_STSFD_IND'));
675 IGS_GE_MSG_STACK.ADD;
676 RAISE FND_API.G_EXC_ERROR;
677 ELSE
678 l_cndtl_offer_must_stsfd_ind :=
679 NVL(p_cndtl_offer_must_stsfd_ind, l_c_aplinst_cur.cndtnl_offer_must_be_stsfd_ind);
680 END IF;
681
682
683 IF p_cndtnl_offer_satisfied_dt = FND_API.G_MISS_DATE THEN
684 l_cndtnl_offer_satisfied_dt := NULL;
685 ELSE
686 l_cndtnl_offer_satisfied_dt :=
687 NVL(p_cndtnl_offer_satisfied_dt, l_c_aplinst_cur.cndtnl_offer_satisfied_dt);
688 END IF;
689
690 IF p_adm_outcome_status_auth_dt = FND_API.G_MISS_DATE THEN
691 l_adm_outcome_status_auth_dt := NULL;
692 ELSE
693 l_adm_outcome_status_auth_dt :=
694 NVL(p_adm_outcome_status_auth_dt, l_c_aplinst_cur.adm_outcome_status_auth_dt);
695 END IF;
696
697
698 IF p_adm_otcm_status_auth_per_id = FND_API.G_MISS_NUM THEN
699 l_adm_otcm_status_auth_per_id := NULL;
700 ELSE
701 l_adm_otcm_status_auth_per_id :=
705 IF p_adm_outcome_status_reason = FND_API.G_MISS_CHAR THEN
702 NVL(p_adm_otcm_status_auth_per_id, l_c_aplinst_cur.adm_otcm_status_auth_person_id);
703 END IF;
704
706 l_adm_outcome_status_reason := NULL;
707 ELSE
708 l_adm_outcome_status_reason := NVL(p_adm_outcome_status_reason, l_c_aplinst_cur.adm_outcome_status_reason);
709 END IF;
710
711 ---------------------------------------------------------------
712 -- End Code to Check and assign the Missing Fields/ Nullified Fields
713 ---------------------------------------------------------------
714
715
716 /* Not needed now - ADTD001 - IGS.M - apadegal
717
718 IF IGS_AD_VAL_AA.admp_val_aa_update (
719 l_c_appl_cur.adm_appl_status,
720 v_message_name) = FALSE AND l_reconsider_flag = 'N' THEN
721 FND_MESSAGE.SET_NAME('IGS',v_message_name);
722 IGS_GE_MSG_STACK.ADD;
723 RAISE FND_API.G_EXC_ERROR;
724 END IF;
725
726 */
727
728 IF l_s_adm_outcome_status IN ('OFFER', 'COND-OFFER') THEN
729 IF l_offer_response_dt IS NULL AND
730 NVL(p_offer_response_dt, TRUNC(SYSDATE) )
731 <> FND_API.G_MISS_DATE THEN
732 l_offer_response_dt := IGS_AD_GEN_007.ADMP_GET_RESP_DT(l_c_aplinst_cur.nominated_course_cd,
733 l_c_aplinst_cur.crv_version_number,
734 l_c_appl_cur.acad_cal_type,
735 l_c_aplinst_cur.location_cd,
736 l_c_aplinst_cur.attendance_mode,
737 l_c_aplinst_cur.attendance_type,
738 l_c_appl_cur.admission_cat,
739 l_c_appl_cur.s_admission_process_type,
740 NVL(l_c_aplinst_cur.adm_cal_type,
741 l_c_appl_cur.adm_cal_type),
742 NVL(l_c_aplinst_cur.adm_ci_sequence_number,
743 l_c_appl_cur.adm_ci_sequence_number),
744 l_offer_dt);
745
746 IF l_offer_response_dt IS NULL THEN
747 fnd_message.set_name('IGS', 'IGS_AD_OFR_RESPDT_SET_ODR_ADM');
748 IGS_GE_MSG_STACK.ADD;
749 RAISE FND_API.G_EXC_ERROR;
750 END IF;
751 END IF;
752 END IF;
753
754 -- Moved back from method BeforeInsertUpdateDelete1 of TBH-IGSAI18B.pls on account of bug-4234911
755 -- ==========================================
756 IF l_decision_make_id IS NULL THEN
757 IF l_s_adm_outcome_status NOT IN ('PENDING') THEN
758 fnd_message.set_name('IGS', 'IGS_AD_INVALID_DECISION_ID');
759 IGS_GE_MSG_STACK.ADD;
760 APP_EXCEPTION.RAISE_EXCEPTION;
761 END IF;
762 ELSIF l_decision_make_id = p_person_id THEN
763 fnd_message.set_name('IGS', 'IGS_AD_INVALID_DECISION_ID');
764 IGS_GE_MSG_STACK.ADD;
765 APP_EXCEPTION.RAISE_EXCEPTION;
766 ELSIF NOT igs_ad_val_acai.genp_val_staff_fculty_prsn
767 (l_decision_make_id, v_message_name) THEN
768 fnd_message.set_name('IGS', v_message_name);
769 IGS_GE_MSG_STACK.ADD;
770 APP_EXCEPTION.RAISE_EXCEPTION;
771 END IF;
772 -- Validate decision Date
773 IF l_decision_date IS NULL AND l_s_adm_outcome_status NOT IN ('PENDING') THEN
774 fnd_message.set_name('IGS', 'IGS_AD_MAND_DECISION_INFO');
775 IGS_GE_MSG_STACK.ADD;
776 APP_EXCEPTION.RAISE_EXCEPTION;
777 ELSE
778 -- Decision date should be between the application date and the system date
779 IF NOT l_decision_date BETWEEN l_c_appl_cur.appl_dt AND SYSDATE THEN
780 fnd_message.set_name('IGS', 'IGS_AD_DECISION_DATE');
781 IGS_GE_MSG_STACK.ADD;
782 APP_EXCEPTION.RAISE_EXCEPTION;
783 END IF;
784 END IF;
785
786 --Validate Decision Reason
787 IF l_decision_reason_id IS NULL AND
788 l_s_adm_outcome_status NOT IN ('PENDING')
789 THEN
790 fnd_message.set_name('IGS', 'IGS_AD_DECISION_REASON_INVALID');
791 IGS_GE_MSG_STACK.ADD;
792 APP_EXCEPTION.RAISE_EXCEPTION;
793 END IF;
794
795 -- ==============================================================
796
797
798 --Validate Pending Reason
799 IF l_pending_reason_id IS NULL AND
800 l_s_adm_outcome_status IN ('PENDING') AND
801 l_c_aplinst_cur.adm_outcome_status <> l_adm_outcome_status_reason THEN
802 fnd_message.set_name('IGS', 'IGS_AD_PENDING_REASON_INVALID');
803 IGS_GE_MSG_STACK.ADD;
804 APP_EXCEPTION.RAISE_EXCEPTION;
805 END IF;
806
807 IF l_s_adm_outcome_status IN ('OFFER', 'COND-OFFER') THEN
808 IF (p_pending_reason_id IS NOT NULL AND NVL(p_pending_reason_id, -1) <> FND_API.G_MISS_NUM ) THEN
809 fnd_message.set_name('IGS', 'IGS_AD_INVALID_PARAM_COMB');
810 IGS_GE_MSG_STACK.ADD;
811 RAISE FND_API.G_EXC_ERROR;
812 END IF ;
813 l_pending_reason_id := NULL;
814 ELSIF l_s_adm_outcome_status IN ('PENDING') THEN
815 -- p_offer_dt, p_offer_response_dt, p_decision_maker_id or p_decision_date
816 -- cannot be specified when l_s_adm_outcome_status = PENDING
817 IF (p_offer_dt IS NOT NULL AND NVL(p_offer_dt, TRUNC(SYSDATE) ) <> FND_API.G_MISS_DATE)
821 (p_decision_maker_id IS NOT NULL AND NVL(p_decision_maker_id, -1 ) <> FND_API.G_MISS_NUM )
818 OR
819 (p_offer_response_dt IS NOT NULL AND NVL(p_offer_response_dt, TRUNC(SYSDATE) ) <> FND_API.G_MISS_DATE )
820 OR
822 OR
823 (p_decision_date IS NOT NULL AND NVL( p_decision_date, TRUNC(SYSDATE) ) <> FND_API.G_MISS_DATE) THEN
824 fnd_message.set_name('IGS', 'IGS_AD_INVALID_PARAM_COMB');
825 IGS_GE_MSG_STACK.ADD;
826 RAISE FND_API.G_EXC_ERROR;
827 END IF;
828 l_offer_dt := NULL;
829 l_offer_response_dt := NULL;
830 l_decision_reason_id := NULL;
831 l_decision_make_id := NULL;
832 l_decision_date := NULL;
833 ELSE
834 IF
835 -- p_offer_dt, p_offer_response_dt or p_pending_reason_id cannot be specified when
836 -- l_s_adm_outcome_status NOT IN PENDING , OFFER , COND-OFFER
837 (p_offer_dt IS NOT NULL AND NVL(p_offer_dt, TRUNC(SYSDATE) ) <> FND_API.G_MISS_DATE)
838 OR
839 (p_offer_response_dt IS NOT NULL AND NVL(p_offer_response_dt, TRUNC(SYSDATE) ) <> FND_API.G_MISS_DATE )
840 OR
841 (p_pending_reason_id IS NOT NULL AND NVL(p_pending_reason_id, -1) <> FND_API.G_MISS_NUM ) THEN
842 fnd_message.set_name('IGS', 'IGS_AD_INVALID_PARAM_COMB');
843 IGS_GE_MSG_STACK.ADD;
844 RAISE FND_API.G_EXC_ERROR;
845 END IF;
846 l_offer_dt := NULL;
847 l_offer_response_dt := NULL;
848 l_pending_reason_id := NULL;
849 END IF;
850
851 l_actual_response_dt := l_c_aplinst_cur.actual_response_dt;
852
853 IF NVL(igs_ad_gen_008.admp_get_saos(l_adm_outcome_status), 'NONE') IN
854 ('OFFER', 'COND-OFFER') THEN
855 IF l_prpsd_commencement_date IS NULL THEN
856 -- Default the proposed commencement date.
857 l_prpsd_commencement_date := igs_en_gen_002.enrp_get_acad_comm(l_c_appl_cur.acad_cal_type,
858 l_c_appl_cur.acad_ci_sequence_number,
859 p_person_id,
860 p_nominated_program_cd,
861 p_admission_appl_number,
862 p_nominated_program_cd,
863 p_sequence_number,
864 'Y'); -- Check for proposed commencement date.
865 END IF;
866
867
868
869 -- Default the Offer Response Status.
870 IF l_c_appl_cur.s_admission_process_type <> 'RE-ADMIT' THEN
871 l_adm_offer_resp_status := igs_ad_gen_009.admp_get_sys_aors('PENDING');
872 ELSE
873 l_adm_offer_resp_status := igs_ad_gen_009.admp_get_sys_aors('ACCEPTED');
874 l_actual_response_dt := TRUNC(SYSDATE);
875 END IF;
876 ELSE
877 l_adm_offer_resp_status := igs_ad_gen_009.admp_get_sys_aors('NOT-APPLIC');
878 END IF;
879
880 -- If outcome status is not in withdrawn, voided and cond offer then
881 -- set the condition offer status as not-applic
882 -- otherwise retain the old status
883
884 IF NVL(l_s_adm_outcome_status, 'NONE') NOT IN ('WITHDRAWN', 'VOIDED', 'COND-OFFER') THEN
885 IF p_adm_cndtnl_offer_status IS NOT NULL AND
886 igs_ad_gen_007.Admp_Get_Sacos (p_adm_cndtnl_offer_status) <> 'NOT-APPLIC' THEN
887 fnd_message.set_name('IGS', 'IGS_AD_INVALID_PARAM_COMB');
888 IGS_GE_MSG_STACK.ADD;
889 RAISE FND_API.G_EXC_ERROR;
890 END IF;
891 l_adm_cndtl_offer_status := igs_ad_gen_009.admp_get_sys_acos('NOT-APPLIC');
892 ELSIF NVL(l_s_adm_outcome_status, 'NONE') = 'COND-OFFER' AND
893 NVL(IGS_AD_GEN_007.ADMP_GET_SACOS(l_adm_cndtl_offer_status),
894 'NOT-APPLIC') = 'NOT-APPLIC' THEN
895
896 -- if outcome status is cond offer then set the cond offer status to pending
897 l_adm_cndtl_offer_status := igs_ad_gen_009.admp_get_sys_acos('PENDING');
898 ELSE
899 l_adm_cndtl_offer_status := l_adm_cndtl_offer_status;
900 END IF;
901
902
903
904 BEGIN
905 l_msg_index := igs_ge_msg_stack.count_msg;
906
907 -- begin apadegal adtd001 igs.m
908 OPEN c_ps_appl_cur;
909 FETCH c_ps_appl_cur INTO l_c_ps_appl_cur;
910 CLOSE c_ps_appl_cur;
911
912 -- If not reconsidered previously
913 IF p_reconsider_flag = 'Y' AND NVL(l_c_ps_appl_cur.req_for_reconsideration_ind,'N') = 'N'
914 THEN
915
916 igs_ad_gen_002.Is_inst_recon_allowed( p_person_id => l_c_aplinst_cur.person_id,
917 p_admission_appl_number => l_c_aplinst_cur.admission_appl_number,
918 p_nominated_course_cd => l_c_aplinst_cur.nominated_course_cd,
919 p_sequence_number => l_c_aplinst_cur.sequence_number,
920 p_success => lv_return_status,
921 p_message_name => lv_msg_nme
922 );
923 IF lv_return_status = 'N'
924 THEN
925 FND_MESSAGE.SET_NAME('IGS', lv_msg_nme);
926 IGS_GE_MSG_STACK.ADD;
927 APP_EXCEPTION.RAISE_EXCEPTION;
928 END IF;
929
930 igs_ad_ps_appl_pkg.update_row(x_rowid => l_c_ps_appl_cur.row_id,
931 x_person_id => l_c_ps_appl_cur.person_id,
932 x_admission_appl_number => l_c_ps_appl_cur.admission_appl_number,
933 x_nominated_course_cd => l_c_ps_appl_cur.nominated_course_cd,
934 x_transfer_course_cd => l_c_ps_appl_cur.transfer_course_cd,
938 x_course_rank_schedule => l_c_ps_appl_cur.course_rank_schedule,
935 x_basis_for_admission_type => l_c_ps_appl_cur.basis_for_admission_type,
936 x_admission_cd => l_c_ps_appl_cur.admission_cd,
937 x_course_rank_set => l_c_ps_appl_cur.course_rank_set,
939 x_req_for_reconsideration_ind => 'Y',
940 x_req_for_adv_standing_ind => l_c_ps_appl_cur.req_for_adv_standing_ind,
941 x_mode => 'R');
942
943 IGS_AD_GEN_002.Reconsider_Appl_Inst(p_person_id => l_c_aplinst_cur.person_id,
944 p_admission_appl_number => l_c_aplinst_cur.admission_appl_number,
945 p_nominated_course_cd => l_c_aplinst_cur.nominated_course_cd,
946 p_acai_sequence_number => l_c_aplinst_cur.sequence_number,
947 p_interface => 'IMPORT'
948 );
949
950
951 END IF;
952 -- end apadegal adtd001 igs.m
953
954 -- check if the program was previously reconsidered and outcome is changed now
955 OPEN c_ps_appl_cur;
956 FETCH c_ps_appl_cur INTO l_c_ps_appl_cur;
957 CLOSE c_ps_appl_cur;
958
959 IF NVL(l_c_ps_appl_cur.req_for_reconsideration_ind,'N') = 'Y' AND
960 NVL(IGS_AD_GEN_008.ADMP_GET_SAOS(l_adm_outcome_status),'NULL') <> NVL(IGS_AD_GEN_008.ADMP_GET_SAOS (l_c_aplinst_cur.Adm_outcome_status ),'NULL')
961 THEN
962
963 igs_ad_ps_appl_pkg.update_row(x_rowid => l_c_ps_appl_cur.row_id,
964 x_person_id => l_c_ps_appl_cur.person_id,
965 x_admission_appl_number => l_c_ps_appl_cur.admission_appl_number,
966 x_nominated_course_cd => l_c_ps_appl_cur.nominated_course_cd,
967 x_transfer_course_cd => l_c_ps_appl_cur.transfer_course_cd,
968 x_basis_for_admission_type => l_c_ps_appl_cur.basis_for_admission_type,
969 x_admission_cd => l_c_ps_appl_cur.admission_cd,
970 x_course_rank_set => l_c_ps_appl_cur.course_rank_set,
971 x_course_rank_schedule => l_c_ps_appl_cur.course_rank_schedule,
972 x_req_for_reconsideration_ind => 'N',
973 x_req_for_adv_standing_ind => l_c_ps_appl_cur.req_for_adv_standing_ind,
974 x_mode => 'R');
975 l_recon_unchecked := TRUE;
976 END IF;
977
978 -- end apadegal adtd001 igs.m
979
980 igs_ad_ps_appl_inst_pkg.update_row(x_rowid => l_c_aplinst_cur.ROWID,
981 x_person_id => l_c_aplinst_cur.person_id,
982 x_admission_appl_number => l_c_aplinst_cur.admission_appl_number,
983 x_nominated_course_cd => l_c_aplinst_cur.nominated_course_cd,
984 x_sequence_number => l_c_aplinst_cur.sequence_number,
985 x_predicted_gpa => l_c_aplinst_cur.predicted_gpa,
986 x_academic_index => l_c_aplinst_cur.academic_index,
987 x_adm_cal_type => l_c_aplinst_cur.adm_cal_type,
988 x_app_file_location => l_c_aplinst_cur.app_file_location,
989 x_adm_ci_sequence_number => l_c_aplinst_cur.adm_ci_sequence_number,
990 x_course_cd => l_c_aplinst_cur.course_cd,
991 x_app_source_id => l_c_aplinst_cur.app_source_id,
992 x_crv_version_number => l_c_aplinst_cur.crv_version_number,
993 x_waitlist_rank => l_c_aplinst_cur.waitlist_rank,
994 x_waitlist_status => l_c_aplinst_cur.waitlist_status,
995 x_location_cd => l_c_aplinst_cur.location_cd,
996 x_attent_other_inst_cd => l_c_aplinst_cur.attent_other_inst_cd,
997 x_attendance_mode => l_c_aplinst_cur.attendance_mode,
998 x_edu_goal_prior_enroll_id => l_c_aplinst_cur.edu_goal_prior_enroll_id,
999 x_attendance_type => l_c_aplinst_cur.attendance_type,
1000 x_decision_make_id => l_decision_make_id,
1001 x_unit_set_cd => l_c_aplinst_cur.unit_set_cd,
1002 x_decision_date => l_decision_date,
1003 x_attribute_category => l_c_aplinst_cur.attribute_category,
1004 x_attribute1 => l_c_aplinst_cur.attribute1,
1005 x_attribute2 => l_c_aplinst_cur.attribute2,
1006 x_attribute3 => l_c_aplinst_cur.attribute3,
1007 x_attribute4 => l_c_aplinst_cur.attribute4,
1008 x_attribute5 => l_c_aplinst_cur.attribute5,
1009 x_attribute6 => l_c_aplinst_cur.attribute6,
1010 x_attribute7 => l_c_aplinst_cur.attribute7,
1011 x_attribute8 => l_c_aplinst_cur.attribute8,
1015 x_attribute12 => l_c_aplinst_cur.attribute12,
1012 x_attribute9 => l_c_aplinst_cur.attribute9,
1013 x_attribute10 => l_c_aplinst_cur.attribute10,
1014 x_attribute11 => l_c_aplinst_cur.attribute11,
1016 x_attribute13 => l_c_aplinst_cur.attribute13,
1017 x_attribute14 => l_c_aplinst_cur.attribute14,
1018 x_attribute15 => l_c_aplinst_cur.attribute15,
1019 x_attribute16 => l_c_aplinst_cur.attribute16,
1020 x_attribute17 => l_c_aplinst_cur.attribute17,
1021 x_attribute18 => l_c_aplinst_cur.attribute18,
1022 x_attribute19 => l_c_aplinst_cur.attribute19,
1023 x_attribute20 => l_c_aplinst_cur.attribute20,
1024 x_decision_reason_id => l_decision_reason_id,
1025 x_us_version_number => l_c_aplinst_cur.us_version_number,
1026 x_decision_notes => l_c_aplinst_cur.decision_notes,
1027 x_pending_reason_id => l_pending_reason_id,
1028 x_preference_number => l_c_aplinst_cur.preference_number,
1029 x_adm_doc_status => l_c_aplinst_cur.adm_doc_status,
1030 x_adm_entry_qual_status => l_c_aplinst_cur.adm_entry_qual_status,
1031 x_deficiency_in_prep => l_c_aplinst_cur.deficiency_in_prep,
1032 x_late_adm_fee_status => l_c_aplinst_cur.late_adm_fee_status,
1033 x_spl_consider_comments => l_c_aplinst_cur.spl_consider_comments,
1034 x_apply_for_finaid => l_c_aplinst_cur.apply_for_finaid,
1035 x_finaid_apply_date => l_c_aplinst_cur.finaid_apply_date,
1036 x_adm_outcome_status => l_adm_outcome_status,
1037 x_adm_otcm_stat_auth_per_id => l_adm_otcm_status_auth_per_id,
1038 x_adm_outcome_status_auth_dt => l_adm_outcome_status_auth_dt,
1039 x_adm_outcome_status_reason => l_adm_outcome_status_reason,
1040 x_offer_dt => l_offer_dt,
1041 x_offer_response_dt => l_offer_response_dt,
1042 x_prpsd_commencement_dt => l_prpsd_commencement_date,
1043 x_adm_cndtnl_offer_status => l_adm_cndtl_offer_status,
1044 x_cndtnl_offer_satisfied_dt => l_cndtnl_offer_satisfied_dt,
1045 x_cndnl_ofr_must_be_stsfd_ind => l_cndtl_offer_must_stsfd_ind,
1046 x_adm_offer_resp_status => l_adm_offer_resp_status,
1047 x_actual_response_dt => l_actual_response_dt,
1048 x_adm_offer_dfrmnt_status => l_c_aplinst_cur.adm_offer_dfrmnt_status,
1049 x_deferred_adm_cal_type => l_c_aplinst_cur.deferred_adm_cal_type,
1050 x_deferred_adm_ci_sequence_num => l_c_aplinst_cur.deferred_adm_ci_sequence_num,
1051 x_deferred_tracking_id => l_c_aplinst_cur.deferred_tracking_id,
1052 x_ass_rank => l_c_aplinst_cur.ass_rank,
1053 x_secondary_ass_rank => l_c_aplinst_cur.secondary_ass_rank,
1054 x_intr_accept_advice_num => l_c_aplinst_cur.intrntnl_acceptance_advice_num,
1055 x_ass_tracking_id => l_c_aplinst_cur.ass_tracking_id,
1056 x_fee_cat => l_c_aplinst_cur.fee_cat,
1057 x_hecs_payment_option => l_c_aplinst_cur.hecs_payment_option,
1058 x_expected_completion_yr => l_c_aplinst_cur.expected_completion_yr,
1059 x_expected_completion_perd => l_c_aplinst_cur.expected_completion_perd,
1060 x_correspondence_cat => l_c_aplinst_cur.correspondence_cat,
1061 x_enrolment_cat => l_c_aplinst_cur.enrolment_cat,
1062 x_funding_source => l_c_aplinst_cur.funding_source,
1063 x_applicant_acptnce_cndtn => l_c_aplinst_cur.applicant_acptnce_cndtn,
1064 x_cndtnl_offer_cndtn => l_cndtnl_offer_cndtn,
1065 x_ss_application_id => l_c_aplinst_cur.ss_application_id,
1066 x_ss_pwd => l_c_aplinst_cur.ss_pwd,
1067 x_authorized_dt => l_c_aplinst_cur.authorized_dt,
1068 x_authorizing_pers_id => l_c_aplinst_cur.authorizing_pers_id,
1069 x_entry_status => l_c_aplinst_cur.entry_status,
1070 x_entry_level => l_c_aplinst_cur.entry_level,
1071 x_sch_apl_to_id => l_c_aplinst_cur.sch_apl_to_id,
1072 x_idx_calc_date => l_c_aplinst_cur.idx_calc_date,
1076 x_fut_adm_ci_sequence_number => l_c_aplinst_cur.future_adm_ci_sequence_number, -- bug # 2217104
1073 x_fut_acad_cal_type => l_c_aplinst_cur.future_acad_cal_type, -- bug # 2217104
1074 x_fut_acad_ci_sequence_number => l_c_aplinst_cur.future_acad_ci_sequence_number, -- bug # 2217104
1075 x_fut_adm_cal_type => l_c_aplinst_cur.future_adm_cal_type, -- bug # 2217104
1077 x_prev_term_adm_appl_number => l_c_aplinst_cur.previous_term_adm_appl_number, -- bug # 2217104
1078 x_prev_term_sequence_number => l_c_aplinst_cur.previous_term_sequence_number, -- bug # 2217104
1079 x_fut_term_adm_appl_number => l_c_aplinst_cur.future_term_adm_appl_number, -- bug # 2217104
1080 x_fut_term_sequence_number => l_c_aplinst_cur.future_term_sequence_number, -- bug # 2217104
1081 X_DEF_ACAD_CAL_TYPE => l_c_aplinst_cur.DEF_ACAD_CAL_TYPE, --Bug 2395510
1082 X_DEF_ACAD_CI_SEQUENCE_NUM => l_c_aplinst_cur.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
1083 X_DEF_PREV_TERM_ADM_APPL_NUM => l_c_aplinst_cur.DEF_PREV_TERM_ADM_APPL_NUM, --Bug 2395510
1084 X_DEF_PREV_APPL_SEQUENCE_NUM => l_c_aplinst_cur.DEF_PREV_APPL_SEQUENCE_NUM, --Bug 2395510
1085 X_DEF_TERM_ADM_APPL_NUM => l_c_aplinst_cur.DEF_TERM_ADM_APPL_NUM, --Bug 2395510
1086 X_DEF_APPL_SEQUENCE_NUM => l_c_aplinst_cur.DEF_APPL_SEQUENCE_NUM, --Bug 2395510
1087 x_mode => 'R',
1088 x_attribute21 => l_c_aplinst_cur.attribute21,
1089 x_attribute22 => l_c_aplinst_cur.attribute22,
1090 x_attribute23 => l_c_aplinst_cur.attribute23,
1091 x_attribute24 => l_c_aplinst_cur.attribute24,
1092 x_attribute25 => l_c_aplinst_cur.attribute25,
1093 x_attribute26 => l_c_aplinst_cur.attribute26,
1094 x_attribute27 => l_c_aplinst_cur.attribute27,
1095 x_attribute28 => l_c_aplinst_cur.attribute28,
1096 x_attribute29 => l_c_aplinst_cur.attribute29,
1097 x_attribute30 => l_c_aplinst_cur.attribute30,
1098 x_attribute31 => l_c_aplinst_cur.attribute31,
1099 x_attribute32 => l_c_aplinst_cur.attribute32,
1100 x_attribute33 => l_c_aplinst_cur.attribute33,
1101 x_attribute34 => l_c_aplinst_cur.attribute34,
1102 x_attribute35 => l_c_aplinst_cur.attribute35,
1103 x_attribute36 => l_c_aplinst_cur.attribute36,
1104 x_attribute37 => l_c_aplinst_cur.attribute37,
1105 x_attribute38 => l_c_aplinst_cur.attribute38,
1106 x_attribute39 => l_c_aplinst_cur.attribute39,
1107 x_attribute40 => l_c_aplinst_cur.attribute40,
1108 X_APPL_INST_STATUS => l_c_aplinst_cur.appl_inst_status,
1109 x_ais_reason => l_c_aplinst_cur.ais_reason,
1110 x_decline_ofr_reason => l_c_aplinst_cur.decline_ofr_reason
1111 );
1112
1113
1114 --- NEED TO CREATE dummy history records for pending applications, if recon is unchecked
1115 -- begin apadegal adtd001 igs.m
1116 IF l_recon_unchecked
1117 THEN
1118 IGS_AD_GEN_002.ins_dummy_pend_hist_rec( l_c_aplinst_cur.person_id,
1119 l_c_aplinst_cur.admission_appl_number,
1120 l_c_aplinst_cur.nominated_course_cd );
1121 END IF;
1122 -- end apadegal adtd001 igs.m
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 ROLLBACK TO Record_Outcome_AdmAppl_pub;
1126 igs_ad_gen_016.extract_msg_from_stack(p_msg_at_index => l_msg_index,
1127 p_return_status => x_return_status,
1128 p_msg_count => x_msg_count,
1129 p_msg_data => x_msg_data,
1130 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1131
1132 IF l_hash_msg_name_text_type_tab(x_msg_count - 1).name <> 'ORA' THEN
1133
1134 x_return_status := FND_API.G_RET_STS_ERROR;
1135 ELSE
1136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1137 END IF;
1138 RETURN;
1139 END;
1140
1141
1142 IF p_ucas_transaction = 'Y' THEN
1143 -- Check the message count in stack before calling TBH.
1144 IF FND_PROFILE.VALUE('OSS_COUNTRY_CODE') = 'GB' THEN
1145 igs_ad_ps_appl_inst_pkg.ucas_user_hook(p_admission_appl_number => l_c_aplinst_cur.admission_appl_number,
1146 p_nominated_course_cd => l_c_aplinst_cur.nominated_course_cd,
1147 p_sequence_number => l_c_aplinst_cur.sequence_number,
1148 p_adm_outcome_status => l_adm_outcome_status,
1149 p_cond_offer_status => l_c_aplinst_cur.adm_cndtnl_offer_status,
1153 p_condition_category => NULL,
1150 p_adm_outcome_status_old => l_c_aplinst_cur.adm_outcome_status,
1151 p_cond_offer_status_old => l_c_aplinst_cur.adm_cndtnl_offer_status,
1152 p_person_id => l_c_aplinst_cur.person_id,
1154 p_condition_name => NULL,
1155 p_uc_tran_id => l_uc_tran_id);
1156 END IF;
1157 -- tray end CCR2550009
1158 -- kamohan Bug # 2550009
1159 END IF;
1160
1161 -- Call the Pre-enrollment Process if the Outcome status is in OFFER or COND-OFFER
1162 -- Check whether the APC Step has been included for the application's APC
1163 -- then call the pre-enrollment process
1164 l_pre_enroll := 'N';
1165
1166 OPEN c_apcs(l_c_appl_cur.admission_cat,
1167 l_c_appl_cur.s_admission_process_type);
1168 FETCH c_apcs
1169 INTO l_pre_enroll;
1170 CLOSE c_apcs;
1171
1172
1173 OPEN c_adm_ofr_resp_stat_cur;
1174 FETCH c_adm_ofr_resp_stat_cur
1175 INTO l_adm_offer_resp_status;
1176 CLOSE c_adm_ofr_resp_stat_cur;
1177 IF NVL(igs_ad_gen_008.admp_get_saors(l_adm_offer_resp_status), 'NULL') =
1178 'ACCEPTED' THEN
1179 IF igs_ad_upd_initialise.perform_pre_enrol(p_person_id,
1180 p_admission_appl_number,
1181 p_nominated_program_cd,
1182 p_sequence_number,
1183 'Y', -- Confirm course indicator.
1184 'Y', -- Perform eligibility check indicator.
1185 v_message_name) = FALSE THEN
1186 FND_MESSAGE.SET_NAME('IGS', v_message_name);
1187 IGS_GE_MSG_STACK.ADD;
1188 RAISE FND_API.G_EXC_ERROR;
1189 END IF;
1190
1191 -- if we need to run the pre enrollment process for this application,
1192 -- we need to check the apc step and run the pre enrollment process
1193 -- for a course that has been offered on this update.
1194 ELSIF l_pre_enroll = 'Y' AND
1195 NVL(igs_ad_gen_008.admp_get_saos(l_adm_outcome_status), 'NULL') IN
1196 ('OFFER', 'COND-OFFER') AND
1197 NVL(igs_ad_gen_008.admp_get_saos(l_adm_outcome_status), 'NULL') <>
1198 NVL(igs_ad_gen_008.admp_get_saos(l_c_aplinst_cur.adm_outcome_status),
1199 'NULL') THEN
1200
1201 IF igs_ad_upd_initialise.perform_pre_enrol(p_person_id,
1202 p_admission_appl_number,
1203 p_nominated_program_cd,
1204 p_sequence_number,
1205 'N', -- Confirm course indicator.
1206 'N', -- Perform eligibility check indicator.
1207 v_message_name) = FALSE THEN
1208 FND_MESSAGE.SET_NAME('IGS', v_message_name);
1209 IGS_GE_MSG_STACK.ADD;
1210
1211 RAISE FND_API.G_EXC_ERROR;
1212 END IF;
1213 END IF;
1214
1215 -- Application Decision got imported successfully
1216 -- Raise the business event
1217 -- Changes to the logic of raising the business event is done as part of Financial Aid Integration buid - 3202866
1218 IF l_c_aplinst_cur.adm_outcome_status <> l_adm_outcome_status THEN
1219
1220 igs_ad_wf_001.wf_raise_event(p_person_id => p_person_id,
1221 p_raised_for => 'IOD',
1222 p_admission_appl_number => p_admission_appl_number,
1223 p_nominated_course_cd => p_nominated_program_cd,
1224 p_sequence_number => p_sequence_number,
1225 p_old_outcome_status => l_c_aplinst_cur.adm_outcome_status,
1226 p_new_outcome_status => l_adm_outcome_status);
1227 END IF;
1228 -- End of API body.
1229 -- Standard check of p_commit.
1230 IF FND_API.To_Boolean(p_commit) THEN
1231 COMMIT WORK;
1232 END IF;
1233 -- Standard call to get message count and if count is 1, get message info.
1234 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1235 EXCEPTION
1236 WHEN FND_API.G_EXC_ERROR THEN
1237 ROLLBACK TO Record_Outcome_AdmAppl_pub;
1238 x_return_status := FND_API.G_RET_STS_ERROR;
1239 igs_ad_gen_016.extract_msg_from_stack(p_msg_at_index => l_msg_index,
1240 p_return_status => x_return_status,
1241 p_msg_count => x_msg_count,
1242 p_msg_data => x_msg_data,
1243 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1244
1245 IF l_hash_msg_name_text_type_tab(x_msg_count - 2).name <> 'ORA' THEN
1246 x_return_status := FND_API.G_RET_STS_ERROR;
1247 ELSE
1248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1249 END IF;
1250
1251 x_msg_data := l_hash_msg_name_text_type_tab( x_msg_count - 2).text;
1252 x_msg_count := x_msg_count - 1;
1253
1254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1255 ROLLBACK TO Record_Outcome_AdmAppl_pub;
1256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1257 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1258 p_data => x_msg_data);
1259 WHEN OTHERS THEN
1260 ROLLBACK TO Record_Outcome_AdmAppl_pub;
1264 p_msg_data => x_msg_data,
1261 igs_ad_gen_016.extract_msg_from_stack(p_msg_at_index => l_msg_index,
1262 p_return_status => x_return_status,
1263 p_msg_count => x_msg_count,
1265 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1266
1267 IF l_hash_msg_name_text_type_tab(x_msg_count - 1).name <> 'ORA' THEN
1268 x_return_status := FND_API.G_RET_STS_ERROR;
1269 ELSE
1270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1271 END IF;
1272
1273 END Record_Outcome_AdmApplication;
1274
1275 PROCEDURE validate_off_resp_dtls(
1276 p_person_id IN igs_ad_offresp_int.person_id%TYPE,
1277 p_admission_appl_number IN igs_ad_offresp_int.admission_appl_number%TYPE,
1278 p_nominated_course_cd IN igs_ad_offresp_int.nominated_course_cd%TYPE ,
1279 p_sequence_number IN igs_ad_offresp_int.sequence_number%TYPE,
1280 p_adm_offer_resp_status IN igs_ad_offresp_int.adm_offer_resp_status%TYPE ,
1281 p_actual_offer_response_dt IN igs_ad_offresp_int.actual_offer_response_dt%TYPE,
1282 p_applicant_acptnce_cndtn IN igs_ad_offresp_int.applicant_acptnce_cndtn%TYPE,
1283 -- p_attent_other_inst_cd IN igs_ad_offresp_int.attent_other_inst_cd%TYPE,
1284 p_adm_offer_defr_status IN OUT NOCOPY igs_ad_ps_appl_inst_all.adm_offer_dfrmnt_status%TYPE,
1285 -- p_authorized_dt IN DATE, -- if null then default it to sys date. and validation exists in Form(pld)
1286 -- p_authorizing_pers_id IN NUMBER, --NUMBER(15) LOV (No validation) need to write validations
1287 p_def_acad_cal_type IN igs_ad_offresp_int.def_acad_cal_type%TYPE ,
1288 p_def_acad_ci_sequence_number IN igs_ad_offresp_int.def_acad_ci_sequence_number%TYPE,
1289 p_def_adm_cal_type IN igs_ad_offresp_int.def_adm_cal_type%TYPE ,
1290 p_def_adm_ci_sequence_number IN igs_ad_offresp_int.def_adm_ci_sequence_number%TYPE ,
1291 p_decline_ofr_reason IN igs_ad_offresp_int.decline_ofr_reason%TYPE,
1292 p_attent_other_inst_cd IN igs_ad_offresp_int.attent_other_inst_cd%TYPE,
1293 p_calc_actual_ofr_resp_dt OUT NOCOPY igs_ad_offresp_int.actual_offer_response_dt%TYPE,
1294 -- p_yes_no IN VARCHAR2,
1295 p_validation_success OUT NOCOPY VARCHAR2) IS
1296 ---------------------------------------------------------------------------------------------------------------------------------------
1297 -- Created By : rsharma
1298 -- Date Created On : 09-OCT-2004
1299 -- Purpose : This Procedure performs all the validations that are being done in Offer Response form (IGSAD093). Apart
1300 -- from these validations some additional validations are performed to check for the validity of different Offer Response details.
1301 -- Validations are stopped whenever basic validation a fails, like the application is not in Open processing state or Outcome Status
1302 -- is not valid etc.
1303 -- Know limitations, enhancements or remarks
1304 -- Change History
1305 -- Who When What
1306 ---------------------------------------------------------------------------------------------------------------------------------------
1307
1308
1309 -- to get the one application row
1310 CURSOR c_adm_appl_dtl (cp_person_id igs_ad_appl_all.person_id%TYPE ,
1311 cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS
1312 SELECT appl.*
1313 FROM igs_ad_appl_all appl
1314 WHERE person_id = cp_person_id AND
1315 admission_appl_number = cp_admission_appl_number;
1316
1317 -- to get teh application instance
1318 CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
1319 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
1320 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
1321 cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
1322 ) IS
1323 SELECT aplinst.rowid, aplinst.*
1324 FROM igs_ad_ps_appl_inst_all aplinst
1325 WHERE person_id = cp_person_id AND
1326 admission_appl_number = cp_admission_appl_number AND
1327 nominated_course_cd = cp_nominated_course_cd AND
1328 sequence_number = cp_sequence_number;
1329
1330 CURSOR c_apcs (cp_admission_cat igs_ad_prcs_cat_step.admission_cat%TYPE,
1331 cp_s_admission_process_type igs_ad_prcs_cat_step.s_admission_process_type%TYPE) IS
1332 SELECT s_admission_step_type,
1333 step_type_restriction_num
1334 FROM igs_ad_prcs_cat_step
1335 WHERE admission_cat = cp_admission_cat AND
1336 s_admission_process_type = cp_s_admission_process_type AND
1337 step_group_type <> 'TRACK' ;
1338
1339
1340 v_step_type VARCHAR2(100);
1341 l_deferral_allowed VARCHAR2(1);
1342 l_pre_enrol VARCHAR2(1);
1343 l_multi_offer_allowed VARCHAR2(1);
1344 l_multi_offer_limit NUMBER(10);
1345 v_message_name VARCHAR2(100);
1346 l_valid_def_adm_cal VARCHAR2(1);
1347 l_valid_def_acad_cal VARCHAR2(1);
1348 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
1349 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
1350 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
1351 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
1352 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
1353 cst_accepted CONSTANT VARCHAR2(10) := 'ACCEPTED';
1354 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
1355 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
1356 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
1360 v_admission_cat igs_ad_appl.admission_cat%TYPE;
1357 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
1358
1359
1361 v_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;
1362 v_acad_cal_type igs_ad_appl.acad_cal_type%TYPE;
1363 v_acad_ci_sequence_number igs_ad_appl.acad_ci_sequence_number%TYPE;
1364 v_aa_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
1365 v_aa_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
1366 v_acaiv_adm_cal_type igs_ad_ps_appl_inst_all.adm_cal_type%TYPE;
1367 v_acaiv_adm_ci_sequence_number igs_ad_ps_appl_inst_all.adm_ci_sequence_number%TYPE;
1368 v_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
1369 v_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
1370 v_appl_dt igs_ad_appl.appl_dt%TYPE;
1371 v_adm_appl_status igs_ad_appl.adm_appl_status%TYPE;
1372 v_adm_fee_status igs_ad_appl.adm_fee_status%TYPE;
1373 l_single_response_flag igs_ad_prd_ad_prc_ca.single_response_flag%TYPE;
1374 l_application_id igs_ad_appl_all.application_id%TYPE;
1375 l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
1376 l_acad_alt_code igs_ca_inst.alternate_code%TYPE;
1377 l_adm_alt_code igs_ca_inst.alternate_code%TYPE;
1378 l_acaiv_rec cur_ad_ps_appl_inst%ROWTYPE;
1379 l_appl_rec c_adm_appl_dtl%ROWTYPE;
1380
1381 BEGIN
1382
1383 --Initialize the flag indicating the SUCCESS ('Y'). Gets updated to 'N' even if one validation fails
1384 p_validation_success := 'Y';
1385
1386
1387 OPEN c_adm_appl_dtl( p_person_id,
1388 p_admission_appl_number);
1389 FETCH c_adm_appl_dtl INTO l_appl_rec;
1390 CLOSE c_adm_appl_dtl;
1391
1392 OPEN cur_ad_ps_appl_inst(
1393 p_person_id,
1394 p_admission_appl_number,
1395 p_nominated_course_cd,
1396 p_sequence_number);
1397 FETCH cur_ad_ps_appl_inst INTO l_acaiv_rec;
1398 CLOSE cur_ad_ps_appl_inst;
1399
1400 IF l_acaiv_rec.person_id IS NULL THEN
1401 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
1402 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PROGRAM_APPL'));
1403 IGS_GE_MSG_STACK.ADD;
1404 APP_EXCEPTION.RAISE_EXCEPTION;
1405 END IF;
1406
1407
1408 -- begin apadegal adtd001 igs.m
1409
1410 /* This validation is not needed now as offer reponse is always updateable as per Re-Open ADFD001
1411
1412 --Validations to check if Application Processing Status is OPEN or not
1413 IF igs_ad_gen_007.admp_get_saas(l_appl_rec.adm_appl_status) IN (cst_completed, cst_withdrawn) THEN
1414 p_validation_success := 'N';
1415 fnd_message.set_name('IGS', 'IGS_AD_CANNOTUPD_STATUS_COMPL');
1416 IGS_GE_MSG_STACK.ADD;
1417 RETURN;
1418 END IF;
1419 */
1420 -- end apadegal adtd001 igs.m
1421
1422 --Check if the New Offer Response Status is a valid Offer Response Status mapped to one of the System Offer Response Statuses.
1423 -- IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) IS NULL THEN
1424 IF NOT IGS_AD_OFR_RESP_STAT_PKG.Get_PK_For_Validation(p_adm_offer_resp_status,'N') THEN
1425 p_validation_success := 'N';
1426 fnd_message.set_name('IGS', 'IGS_GE_PK_UK_NOT_FOUND_CLOSED');
1427 fnd_message.set_token('ATTRIBUTE', FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_OFFER_RESP_STATUS'));
1428 IGS_GE_MSG_STACK.ADD;
1429 RETURN;
1430 END IF;
1431 --Validations on the Application Instance Outcome Status. Check if the Applicant's Outcome Status is mapped to one of the System Outcome Status of
1432 --'Make Offer of Admission' (OFFER) or 'Make Offer of Admission Subject to Condition' (COND-OFFER).
1433 IF NVL(igs_ad_gen_008.admp_get_saos(l_acaiv_rec.adm_outcome_status), 'NULL') NOT IN (cst_offer, cst_cond_offer) THEN
1434 p_validation_success := 'N';
1435 fnd_message.set_name('IGS', 'IGS_AD_OFRST_NOTACCEPTED');
1436 IGS_GE_MSG_STACK.ADD;
1437 RETURN;
1438 END IF;
1439
1440
1441 -- ********** VALIDATIONS WHICH CHECK FOR THE PROPER COMBINATION OF New Offer response status Vs SYSTEM TABLE Offer Response Status. Stop processing in case of failure
1442 -- Check if New Offer Response status = Production table (IGS_AD_PS_APPL_INST_ALL) Offer Response Status.
1443 IF p_adm_offer_resp_status = l_acaiv_rec.adm_offer_resp_status THEN
1444 p_validation_success := 'N';
1445 fnd_message.set_name('IGS', 'IGS_AD_RESP_SATUS_NOT_CHANGE');
1446 IGS_GE_MSG_STACK.ADD;
1447 RETURN;
1448 END IF;
1449
1450 -- begin apadegal adtd001 igs.m
1451
1452 /* Following validations are not needed now as Offer response is
1453 -- Check if the Interface Offer Response Status is allowed to update the existing offer response in production table.
1454 -- IF Interface Offer Response Status is 'PENDING' and the corresponding status in production table is 'ACCEPTED' Then
1455 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = cst_pending AND igs_ad_gen_008.admp_get_saors(l_acaiv_rec.adm_offer_resp_status) = cst_accepted THEN
1456 p_validation_success := 'N';
1457 fnd_message.set_name('IGS', 'IGS_AD_ADMOFR_ALREADY_RESPOND');
1458 IGS_GE_MSG_STACK.ADD;
1459 RETURN;
1460 END IF;
1461 -- IF Interface Offer Response Status is PENDING and the corresponding status in production table is REJECTED Then
1462 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = cst_pending AND igs_ad_gen_008.admp_get_saors(l_acaiv_rec.adm_offer_resp_status) = cst_rejected THEN
1463 p_validation_success := 'N';
1464 fnd_message.set_name('IGS', 'IGS_AD_ADMOFR_ALREADY_REJECT');
1465 IGS_GE_MSG_STACK.ADD;
1466 RETURN;
1467 END IF;
1471 fnd_message.set_name('IGS', 'IGS_AD_ADMOFR_ALREADY_REJECT');
1468 -- IF Interface Offer Response Status is 'ACCEPTED' and the corresponding status in production table is 'REJECTED' Then
1469 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = cst_accepted AND igs_ad_gen_008.admp_get_saors(l_acaiv_rec.adm_offer_resp_status) = cst_rejected THEN
1470 p_validation_success := 'N';
1472 IGS_GE_MSG_STACK.ADD;
1473 RETURN;
1474 END IF;
1475 -- IF Interface Offer Response Status is 'DEFERRAL' and the corresponding status in production table is 'REJECTED' Then
1476 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = cst_deferral AND igs_ad_gen_008.admp_get_saors(l_acaiv_rec.adm_offer_resp_status) = cst_rejected THEN
1477 p_validation_success := 'N';
1478 fnd_message.set_name('IGS', 'IGS_AD_ADMOFR_ALREADY_REJECT');
1479 IGS_GE_MSG_STACK.ADD;
1480 RETURN;
1481 END IF;
1482 */
1483
1484 -- added this new validation to check if an application is already created in deferred term. Throw the error accordingly.
1485
1486 IF l_acaiv_rec.Def_term_adm_appl_num IS NOT NULL or l_acaiv_rec.def_appl_sequence_num IS NOT NULL
1487 THEN
1488 p_validation_success := 'N';
1489 fnd_message.set_name('IGS', 'IGS_AD_CANNOT_CHG_CONF_APPL');
1490 IGS_GE_MSG_STACK.ADD;
1491 RETURN ;
1492
1493 END IF;
1494
1495 -- end apadegal adtd001 igs.m
1496
1497
1498 -- If Offer Response is changed to PENDING from DEFERRAL, then the Deffered Calendars should be NULL otherswise insert error record into corresponding table
1499 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) <> cst_pending AND igs_ad_gen_008.admp_get_saors(l_acaiv_rec.adm_offer_resp_status) = cst_deferral THEN
1500 IF p_def_acad_cal_type IS NOT NULL OR p_def_acad_ci_sequence_number IS NOT NULL THEN
1501 p_validation_success := 'N';
1502 fnd_message.set_name('IGS', 'IDS_AD_DEFER_TO_PENDING');
1503 IGS_GE_MSG_STACK.ADD;
1504 RETURN;
1505 END IF;
1506 IF p_def_adm_cal_type IS NOT NULL OR p_def_adm_ci_sequence_number IS NOT NULL THEN
1507 p_validation_success := 'N';
1508 fnd_message.set_name('IGS', 'IDS_AD_DEFER_TO_PENDING');
1509 IGS_GE_MSG_STACK.ADD;
1510 RETURN;
1511 END IF;
1512 END IF;
1513
1514 /***** END OF VALIDATIONS CHECKING FOR THE PROPER COMBINATION OF OFFER RESPONSE STATUS (Interface Table Vs System Table) ************/
1515
1516 --Copy the interface Actual Response Date to the OUT NOCOPY Variable p_calc_actual_ofr_resp_dt
1517 -- and populate this variable accordingly after necessary validations
1518 p_calc_actual_ofr_resp_dt := p_actual_offer_response_dt;
1519
1520 -- Check if parameter Offer Response Status is Other than 'PENDING', 'LAPSED', 'NOT-APPLIC'.
1521 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) NOT IN (cst_pending, cst_lapsed, cst_not_applic) THEN
1522 IF p_calc_actual_ofr_resp_dt IS NULL THEN
1523 p_calc_actual_ofr_resp_dt := SYSDATE;
1524 END IF;
1525 END IF;
1526
1527 -- Validate admission offer response status
1528 FOR c_apcs_rec IN c_apcs(l_appl_rec.admission_cat,l_appl_rec.s_admission_process_type) LOOP
1529 IF c_apcs_rec.s_admission_step_type = 'DEFER' THEN
1530 l_deferral_allowed := 'Y';
1531 END IF;
1532 IF c_apcs_rec.s_admission_step_type = 'PRE-ENROL' THEN
1533 v_step_type := 'IGSAD' || SUBSTR (ltrim(rtrim(c_apcs_rec.s_admission_step_type)),1,3);
1534 IF fnd_function.test(v_step_type) THEN
1535 l_pre_enrol := 'Y';
1536 END IF;
1537 END IF;
1538 IF c_apcs_rec.s_admission_step_type = 'MULTI-OFF' THEN
1539 l_multi_offer_allowed := 'Y';
1540 l_multi_offer_limit := c_apcs_rec.step_type_restriction_num;
1541 END IF;
1542
1543 END LOOP;
1544 IF igs_ad_val_acai_status.admp_val_aors_item(
1545 p_person_id,
1546 p_admission_appl_number,
1547 p_nominated_course_cd,
1548 p_sequence_number,
1549 l_acaiv_rec.course_cd,
1550 p_adm_offer_resp_status,
1551 p_calc_actual_ofr_resp_dt,
1552 l_appl_rec.s_admission_process_type,
1553 NVL(l_deferral_allowed,'N'),
1554 NVL(l_pre_enrol, 'N'),
1555 v_message_name,
1556 p_decline_ofr_reason , --arvsrini igsm
1557 p_attent_other_inst_cd --arvsrini igsm
1558 ) = FALSE THEN
1559 p_validation_success := 'N';
1560 fnd_message.set_name('IGS', v_message_name);
1561 IGS_GE_MSG_STACK.ADD;
1562 RETURN;
1563 END IF;
1564
1565
1566 -- Validations on the Offer Deferment Status
1567 -- Though Offer Deferment Status is not directly imported from Offer Response Interface table, it should be populated
1568 -- with either of the values 'PENDING' or 'NOT-APPLIC' depending on the value of Offer Response Status.
1569 -- Default the Offer Deferment Status, depending on the value of Offer Response Status, and validate the same.
1570 -- IF Offer Response Status is 'DEFERRAL', then default the Offer Deferment Status to 'PENDING'.
1571 -- ELSE Offer Response Status is not equal to 'DEFERRAL', then Default the Offer Deferment Status to 'NOT-APPLIC'.
1572 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = cst_deferral AND
1573 NVL(igs_ad_gen_008.admp_get_saods(l_acaiv_rec.adm_offer_dfrmnt_status), cst_not_applic) = cst_not_applic THEN
1574 IF igs_ad_gen_009.admp_get_sys_aods(cst_pending) IS NULL AND p_adm_offer_defr_status IS NULL THEN
1575 p_validation_success := 'N';
1576 fnd_message.set_name('IGS', 'IGS_AD_DEFR_STATUS_PEND_NOMAP');
1577 IGS_GE_MSG_STACK.ADD;
1578 RETURN;
1579 ELSE
1580 p_adm_offer_defr_status := NVL(p_adm_offer_defr_status , igs_ad_gen_009.admp_get_sys_aods(cst_pending));
1581 -- ADMISSION COURSE APPLICATION INSTANCE: Admission Offer Deferment Status.
1585 p_admission_appl_number,
1582 IF p_adm_offer_defr_status <> l_acaiv_rec.adm_offer_dfrmnt_status THEN
1583 --the offer deferment status can be moved to CONFIRM only from 'APPROVED'.
1584 IF igs_ad_val_acai_status.admp_val_aods_update(p_person_id,
1586 p_nominated_course_cd,
1587 p_sequence_number,
1588 p_adm_offer_defr_status,
1589 v_message_name) = FALSE THEN
1590 p_validation_success := 'N';
1591 FND_MESSAGE.SET_NAME('IGS',v_message_name);
1592 IGS_GE_MSG_STACK.ADD;
1593 RETURN;
1594 END IF;
1595 -- Validate.
1596 IF igs_ad_val_acai_status.admp_val_acai_aods (
1597 p_person_id,
1598 p_admission_appl_number,
1599 p_nominated_course_cd,
1600 p_sequence_number,
1601 l_acaiv_rec.course_cd,
1602 p_adm_offer_defr_status,
1603 l_acaiv_rec.adm_offer_dfrmnt_status,
1604 p_adm_offer_resp_status,
1605 NVL(l_deferral_allowed,'N'),
1606 l_appl_rec.s_admission_process_type,
1607 v_message_name) = FALSE THEN
1608 p_validation_success := 'N';
1609 fnd_message.set_name('IGS', v_message_name);
1610 IGS_GE_MSG_STACK.ADD;
1611 RETURN;
1612 END IF; --End of igs_ad_val_acai_status.admp_val_acai_aods
1613 END IF; ---End of the deferred calendar validations here
1614 END IF;
1615 ELSE -- Of DEFERRAL check
1616 IF igs_ad_gen_009.admp_get_sys_aods(cst_not_applic) IS NULL THEN
1617 p_validation_success := 'N';
1618 fnd_message.set_name('IGS', 'IGS_AD_DEFER_NOT_DEFSTATUS');
1619 IGS_GE_MSG_STACK.ADD;
1620 RETURN;
1621 ELSE
1622 p_adm_offer_defr_status := igs_ad_gen_009.admp_get_sys_aods(cst_not_applic);
1623 END IF;
1624 END IF; -- Of DEFERRAL check
1625
1626 /* Commeting this code to enable the Offer Response after offer response Deadline
1627 -- if the offer response date is elapsed then continue with other validations Otherwise raise the error.
1628 IF p_calc_actual_ofr_resp_dt > l_acaiv_rec.offer_response_dt AND TRUNC(l_acaiv_rec.offer_response_dt) < TRUNC(SYSDATE) AND
1629 igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) = cst_accepted THEN
1630 p_validation_success := 'N';
1631 fnd_message.set_name('IGS', 'IGS_AD_RESP_DT_PASSED');
1632 IGS_GE_MSG_STACK.ADD;
1633 RETURN;
1634 END IF;*/
1635
1636 -------------------------------------
1637 --Start of code taken out by praveen
1638 -------------------------------------
1639
1640 /* -- Validations on the Application Acceptance Condition
1641 IF p_applicant_acptnce_cndtn IS NOT NULL THEN
1642 IF p_applicant_acptnce_cndtn <> l_acaiv_rec.applicant_acptnce_cndtn OR
1643 p_adm_offer_resp_status <> l_acaiv_rec.adm_offer_resp_status THEN
1644 -- Validate the acceptance condition
1645 IF igs_ad_val_acai.admp_val_acpt_cndtn (
1646 p_applicant_acptnce_cndtn,
1647 p_adm_offer_resp_status,
1648 v_message_name) = FALSE THEN
1649 p_validation_success := 'N';
1650 fnd_message.set_name('IGS', v_message_name);
1651 IGS_GE_MSG_STACK.ADD;
1652 RETURN;
1653 END IF; -- End of igs_ad_val_acai.admp_val_acpt_cndtn
1654 END IF;
1655 END IF;*/
1656
1657 --------------------------------------------------------------------------------
1658 -- Start of Validations intoduced as part of ADSS Build For Respond To Offer Page
1659 --------------------------------------------------------------------------------
1660 IF igs_ad_gen_008.admp_get_saors(p_adm_offer_resp_status) IN ('REJECTED','NOT-COMING') AND p_decline_ofr_reason IS NULL THEN
1661 p_validation_success := 'N';
1662 fnd_message.set_name('IGS','IGS_AD_ADMOFR_WITH_REAS');
1663 IGS_GE_MSG_STACK.ADD;
1664 RETURN;
1665 END IF;
1666
1667 IF (p_decline_ofr_reason = 'OTHER-INST' AND p_attent_other_inst_cd IS NULL) THEN
1668 p_validation_success := 'N';
1669 fnd_message.set_name('IGS','IGS_AD_NO_OTH_INST');
1670 IGS_GE_MSG_STACK.ADD;
1671 RETURN;
1672 END IF;
1673 -- Validations Ends
1674
1675 EXCEPTION
1676
1677
1678 WHEN OTHERS THEN
1679 p_validation_success := 'N';
1680 IF c_apcs%ISOPEN THEN
1681 CLOSE c_apcs;
1682 END IF;
1683 IF c_adm_appl_dtl%ISOPEN THEN
1684 CLOSE c_adm_appl_dtl;
1685 END IF;
1686 IF cur_ad_ps_appl_inst%ISOPEN THEN
1687 CLOSE cur_ad_ps_appl_inst;
1688 END IF;
1689 END validate_off_resp_dtls;
1690
1691 PROCEDURE RECORD_OFFER_RESPONSE(
1692 --Standard Parameters Start
1693 p_api_version IN NUMBER,
1694 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
1695 p_commit IN VARCHAR2 default FND_API.G_FALSE,
1696 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
1697 x_return_status OUT NOCOPY VARCHAR2,
1698 x_msg_count OUT NOCOPY NUMBER,
1699 x_msg_data OUT NOCOPY VARCHAR2,
1700 --Standard parameter ends
1701 p_person_id IN NUMBER,
1702 p_admission_appl_number IN NUMBER,
1703 p_nominated_program_cd IN VARCHAR2,
1704 p_sequence_number IN NUMBER,
1705 p_adm_offer_resp_status IN VARCHAR2, --Varchar2(10) LOV(validation exists in Form(PLD))
1709 p_def_acad_ci_sequence_num IN NUMBER, --NUMBER(5)
1706 p_actual_response_dt IN DATE, --validation exists in form(pld)
1707 p_response_comments IN VARCHAR2, --VARCHAR2(2000)
1708 p_def_acad_cal_type IN VARCHAR2, --Varchar2(10)
1710 p_def_adm_cal_type IN VARCHAR2, --Varchar2(10)
1711 p_def_adm_ci_sequence_num IN NUMBER, --NUMBER(5)
1712 p_decline_ofr_reason IN VARCHAR2,
1713 p_attent_other_inst_cd IN VARCHAR2
1714 )
1715 AS
1716 l_api_version CONSTANT NUMBER := '2.0';
1717 l_api_name CONSTANT VARCHAR2(30) := 'RECORD_OFFER_RESPONSE';
1718 l_msg_index NUMBER;
1719 l_return_status VARCHAR2(1);
1720 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1721 l_validation_success VARCHAR2(1);
1722 l_calc_actual_response_dt DATE;
1723 l_adm_offer_dfrmnt_status VARCHAR2(10);
1724
1725 l_response_comments igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE;
1726 l_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE;
1727 l_def_acad_ci_sequence_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE;
1728 l_def_adm_cal_type igs_ad_ps_appl_inst_all.deferred_adm_cal_type%TYPE;
1729 l_def_adm_ci_sequence_num igs_ad_ps_appl_inst_all.deferred_adm_ci_sequence_num%TYPE;
1730 l_decline_ofr_reason igs_ad_ps_appl_inst_all.decline_ofr_reason%TYPE;
1731 l_attent_other_inst_cd igs_ad_ps_appl_inst_all.attent_other_inst_cd%TYPE;
1732 l_enrl_message_name VARCHAR2(30);
1733
1734 CURSOR cur_ad_ps_appl_inst( cp_person_id igs_ad_ps_appl_inst.person_id%type ,
1735 cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%type ,
1736 cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%type,
1737 cp_sequence_no igs_ad_ps_appl_inst.sequence_number%type) IS
1738 SELECT rowid , igs_ad_ps_appl_inst.* from igs_ad_ps_appl_inst
1739 WHERE person_id = cp_person_id and
1740 admission_appl_number = cp_admission_appl_number and
1741 nominated_course_cd = cp_nominated_course_cd and
1742 sequence_number = cp_sequence_no;
1743
1744 cur_ad_ps_appl_inst_rec cur_ad_ps_appl_inst%ROWTYPE;
1745
1746 x_dummy VARCHAR2(2000);
1747 BEGIN
1748 SAVEPOINT S_RECORD_OFFER_RESPONSE_PUB;
1749 -- Initialize API return status to success
1750 x_return_status := FND_API.G_RET_STS_SUCCESS;
1751
1752 l_msg_index := 0;
1753 --All the standard functionality
1754 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
1755 RAISE FND_API.G_EXC_ERROR;
1756 END IF;
1757
1758 -- Initialize message list if p_init_msg_list is set to TRUE.
1759 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1760 FND_MSG_PUB.initialize;
1761 END IF;
1762 l_msg_index := igs_ge_msg_stack.count_msg;
1763 --Assign all defaul values
1764
1765 -- Validate all the parameters for their length
1766 check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
1767 check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
1768 check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
1769 check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
1770 check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1771 check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1772 -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1773 check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1774 check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1775 check_length('DEFERRED_ADM_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_adm_cal_type));
1776 check_length('DEFERRED_ADM_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_adm_ci_sequence_num)));
1777 check_length('DECLINE_OFR_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_decline_ofr_reason));
1778 check_length('ATTENT_OTHER_INST_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_attent_other_inst_cd));
1779
1780 l_adm_offer_dfrmnt_status := NULL;
1781
1782 OPEN cur_ad_ps_appl_inst(p_person_id,
1783 p_admission_appl_number,
1784 p_nominated_program_cd,
1785 p_sequence_number);
1786 FETCH cur_ad_ps_appl_inst INTO cur_ad_ps_appl_inst_rec;
1787 CLOSE cur_ad_ps_appl_inst;
1788
1789 IF p_adm_offer_resp_status = FND_API.G_MISS_CHAR OR p_adm_offer_resp_status IS NULL THEN
1790 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1791 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_OFFER_RESP_STATUS'));
1792 IGS_GE_MSG_STACK.ADD;
1793 RAISE FND_API.G_EXC_ERROR;
1794 END IF;
1795
1796 IF p_actual_response_dt = FND_API.G_MISS_DATE THEN
1797 l_calc_actual_response_dt := null;
1798 END IF;
1799
1800 IF p_response_comments = FND_API.G_MISS_CHAR THEN
1801 l_response_comments := NULL;
1802 ELSE
1803 l_response_comments := NVL(p_response_comments, cur_ad_ps_appl_inst_rec.applicant_acptnce_cndtn );
1804 END IF;
1805
1806 IF p_def_acad_cal_type = FND_API.G_MISS_CHAR THEN
1807 l_def_acad_cal_type := NULL;
1808 ELSE
1809 l_def_acad_cal_type := NVL(p_def_acad_cal_type, cur_ad_ps_appl_inst_rec.def_acad_cal_type );
1810 END IF;
1811
1812 IF p_def_acad_ci_sequence_num = FND_API.G_MISS_NUM THEN
1813 l_def_acad_ci_sequence_num := NULL;
1814 ELSE
1818 IF p_def_adm_cal_type = FND_API.G_MISS_CHAR THEN
1815 l_def_acad_ci_sequence_num := NVL(p_def_acad_ci_sequence_num, cur_ad_ps_appl_inst_rec.def_acad_ci_sequence_num );
1816 END IF;
1817
1819 l_def_adm_cal_type := NULL;
1820 ELSE
1821 l_def_adm_cal_type := NVL(p_def_adm_cal_type, cur_ad_ps_appl_inst_rec.deferred_adm_cal_type );
1822 END IF;
1823
1824 IF p_def_adm_ci_sequence_num = FND_API.G_MISS_NUM THEN
1825 l_def_adm_ci_sequence_num := NULL;
1826 ELSE
1827 l_def_adm_ci_sequence_num := NVL(p_def_adm_ci_sequence_num, cur_ad_ps_appl_inst_rec.deferred_adm_ci_sequence_num );
1828 END IF;
1829
1830 IF p_decline_ofr_reason = FND_API.G_MISS_CHAR THEN
1831 l_decline_ofr_reason := NULL;
1832 ELSE
1833 l_decline_ofr_reason := NVL(p_decline_ofr_reason, cur_ad_ps_appl_inst_rec.decline_ofr_reason );
1834 END IF;
1835
1836 IF p_attent_other_inst_cd = FND_API.G_MISS_CHAR THEN
1837 l_attent_other_inst_cd := NULL;
1838 ELSE
1839 l_attent_other_inst_cd := NVL(p_attent_other_inst_cd, cur_ad_ps_appl_inst_rec.attent_other_inst_cd );
1840 END IF;
1841
1842 --Validate all the parameter through function validate_off_resp_dtls, if validation fails then retun value will
1843 -- be stored in variable l_validation_success as 'N' else it will be 'Y'.
1844 validate_off_resp_dtls
1845 (
1846 p_person_id => p_person_id,
1847 p_admission_appl_number => p_admission_appl_number,
1848 p_nominated_course_cd => p_nominated_program_cd,
1849 p_sequence_number => p_sequence_number,
1850 p_adm_offer_resp_status => p_adm_offer_resp_status,
1851 p_actual_offer_response_dt => p_actual_response_dt,
1852 p_applicant_acptnce_cndtn => p_response_comments,
1853 -- p_attent_other_inst_cd => p_attent_other_inst_cd,
1854 p_adm_offer_defr_status => l_adm_offer_dfrmnt_status,
1855 -- p_authorized_dt => p_authorized_dt,
1856 -- p_authorizing_pers_id => p_authorizing_pers_id,
1857 p_def_acad_cal_type => p_def_acad_cal_type,
1858 p_def_acad_ci_sequence_number => p_def_acad_ci_sequence_num,
1859 p_def_adm_cal_type => p_def_adm_cal_type,
1860 p_def_adm_ci_sequence_number => p_def_adm_ci_sequence_num,
1861 p_decline_ofr_reason => l_decline_ofr_reason,
1862 p_attent_other_inst_cd => l_attent_other_inst_cd,
1863 p_calc_actual_ofr_resp_dt => l_calc_actual_response_dt,
1864 -- p_yes_no => p_yes_no,
1865 p_validation_success => l_validation_success
1866
1867 );
1868
1869
1870 IF l_validation_success = 'Y' THEN
1871
1872
1873 IF NVL(IGS_AD_GEN_008.ADMP_GET_SAORS(cur_ad_ps_appl_inst_rec.adm_offer_resp_status), 'NULL') = 'ACCEPTED'
1874 AND NVL(IGS_AD_GEN_008.ADMP_GET_SAORS(p_adm_offer_resp_status), 'NULL') <> 'ACCEPTED'
1875 THEN
1876 -- UNCONFIRM the Student PROGRAM ATTEMPTS. (api would be provided by enrolments team)
1877 IF NOT IGS_EN_VAL_SCA.handle_rederive_prog_att (p_person_id => cur_ad_ps_appl_inst_rec.PERSON_ID ,
1878 p_admission_appl_number => cur_ad_ps_appl_inst_rec.ADMISSION_APPL_NUMBER ,
1879 p_nominated_course_cd => cur_ad_ps_appl_inst_rec.NOMINATED_COURSE_CD ,
1880 p_sequence_number => cur_ad_ps_appl_inst_rec.SEQUENCE_NUMBER ,
1881 p_message => x_dummy
1882 )
1883 THEN
1884
1885
1886 App_Exception.Raise_Exception;
1887
1888 END IF;
1889 END IF;
1890
1891
1892
1893
1894 IGS_AD_PS_APPL_INST_PKG.UPDATE_ROW (
1895 X_Mode => 'R',
1896 X_RowId => cur_ad_ps_appl_inst_rec.row_id,
1897 X_Person_Id => cur_ad_ps_appl_inst_rec.Person_Id,
1898 X_Admission_Appl_Number => cur_ad_ps_appl_inst_rec.Admission_Appl_Number,
1899 X_Nominated_Course_Cd => cur_ad_ps_appl_inst_rec.Nominated_Course_Cd,
1900 X_Sequence_Number => cur_ad_ps_appl_inst_rec.Sequence_Number,
1901 X_Predicted_Gpa => cur_ad_ps_appl_inst_rec.Predicted_Gpa,
1902 X_Academic_Index => cur_ad_ps_appl_inst_rec.Academic_Index,
1903 X_Adm_Cal_Type => cur_ad_ps_appl_inst_rec.Adm_Cal_Type,
1904 X_App_File_Location => cur_ad_ps_appl_inst_rec.App_File_Location,
1905 X_Adm_Ci_Sequence_Number => cur_ad_ps_appl_inst_rec.Adm_Ci_Sequence_Number,
1906 X_Course_Cd => cur_ad_ps_appl_inst_rec.Course_Cd,
1907 X_App_Source_Id => cur_ad_ps_appl_inst_rec.App_Source_Id,
1908 X_Crv_Version_Number => cur_ad_ps_appl_inst_rec.Crv_Version_Number,
1909 X_Waitlist_Rank => cur_ad_ps_appl_inst_rec.Waitlist_Rank,
1910 X_Location_Cd => cur_ad_ps_appl_inst_rec.Location_Cd,
1911 X_Attent_Other_Inst_Cd => l_attent_other_inst_cd, --
1912 X_Attendance_Mode => cur_ad_ps_appl_inst_rec.Attendance_Mode,
1913 X_Edu_Goal_Prior_Enroll_Id => cur_ad_ps_appl_inst_rec.Edu_Goal_Prior_Enroll_Id,
1914 X_Attendance_Type => cur_ad_ps_appl_inst_rec.Attendance_Type,
1915 X_Decision_Make_Id => cur_ad_ps_appl_inst_rec.Decision_Make_Id,
1916 X_Unit_Set_Cd => cur_ad_ps_appl_inst_rec.Unit_Set_Cd,
1917 X_Decision_Date => cur_ad_ps_appl_inst_rec.Decision_Date,
1918 X_Attribute_Category => cur_ad_ps_appl_inst_rec.Attribute_Category,
1919 X_Attribute1 => cur_ad_ps_appl_inst_rec.Attribute1,
1920 X_Attribute2 => cur_ad_ps_appl_inst_rec.Attribute2,
1921 X_Attribute3 => cur_ad_ps_appl_inst_rec.Attribute3,
1922 X_Attribute4 => cur_ad_ps_appl_inst_rec.Attribute4,
1923 X_Attribute5 => cur_ad_ps_appl_inst_rec.Attribute5,
1924 X_Attribute6 => cur_ad_ps_appl_inst_rec.Attribute6,
1925 X_Attribute7 => cur_ad_ps_appl_inst_rec.Attribute7,
1926 X_Attribute8 => cur_ad_ps_appl_inst_rec.Attribute8,
1927 X_Attribute9 => cur_ad_ps_appl_inst_rec.Attribute9,
1928 X_Attribute10 => cur_ad_ps_appl_inst_rec.Attribute10,
1929 X_Attribute11 => cur_ad_ps_appl_inst_rec.Attribute11,
1930 X_Attribute12 => cur_ad_ps_appl_inst_rec.Attribute12,
1931 X_Attribute13 => cur_ad_ps_appl_inst_rec.Attribute13,
1932 X_Attribute14 => cur_ad_ps_appl_inst_rec.Attribute14,
1933 X_Attribute15 => cur_ad_ps_appl_inst_rec.Attribute15,
1934 X_Attribute16 => cur_ad_ps_appl_inst_rec.Attribute16,
1935 X_Attribute17 => cur_ad_ps_appl_inst_rec.Attribute17,
1936 X_Attribute18 => cur_ad_ps_appl_inst_rec.Attribute18,
1937 X_Attribute19 => cur_ad_ps_appl_inst_rec.Attribute19,
1938 X_Attribute20 => cur_ad_ps_appl_inst_rec.Attribute20,
1939 X_Decision_Reason_Id => cur_ad_ps_appl_inst_rec.decision_reason_id,
1940 X_Us_Version_Number => cur_ad_ps_appl_inst_rec.Us_Version_Number,
1941 X_Decision_Notes => cur_ad_ps_appl_inst_rec.Decision_Notes,
1942 X_Pending_Reason_Id => cur_ad_ps_appl_inst_rec.Pending_Reason_Id,
1943 X_Preference_Number => cur_ad_ps_appl_inst_rec.Preference_Number,
1944 X_Adm_Doc_Status => cur_ad_ps_appl_inst_rec.Adm_Doc_Status,
1945 X_Adm_Entry_Qual_Status => cur_ad_ps_appl_inst_rec.Adm_Entry_Qual_Status,
1946 X_Deficiency_In_Prep => cur_ad_ps_appl_inst_rec.Deficiency_In_Prep,
1947 X_Late_Adm_Fee_Status => cur_ad_ps_appl_inst_rec.Late_Adm_Fee_Status,
1948 X_Spl_Consider_Comments => cur_ad_ps_appl_inst_rec.Spl_Consider_Comments,
1949 X_Apply_For_Finaid => cur_ad_ps_appl_inst_rec.Apply_For_Finaid,
1950 X_Finaid_Apply_Date => cur_ad_ps_appl_inst_rec.Finaid_Apply_Date,
1951 X_Adm_Outcome_Status => cur_ad_ps_appl_inst_rec.Adm_Outcome_Status,
1952 X_Adm_Otcm_Stat_Auth_Per_Id => cur_ad_ps_appl_inst_rec.Adm_Otcm_Status_Auth_Person_Id,
1953 X_Adm_Outcome_Status_Auth_Dt => cur_ad_ps_appl_inst_rec.Adm_Outcome_Status_Auth_Dt,
1954 X_Adm_Outcome_Status_Reason => cur_ad_ps_appl_inst_rec.Adm_Outcome_Status_Reason,
1955 X_Offer_Dt => cur_ad_ps_appl_inst_rec.Offer_Dt,
1956 X_Offer_Response_Dt => cur_ad_ps_appl_inst_rec.Offer_Response_Dt,
1957 X_Prpsd_Commencement_Dt => cur_ad_ps_appl_inst_rec.Prpsd_Commencement_Dt,
1958 X_Adm_Cndtnl_Offer_Status => cur_ad_ps_appl_inst_rec.Adm_Cndtnl_Offer_Status,
1959 X_Cndtnl_Offer_Satisfied_Dt => cur_ad_ps_appl_inst_rec.Cndtnl_Offer_Satisfied_Dt,
1960 X_Cndnl_Ofr_Must_Be_Stsfd_Ind => cur_ad_ps_appl_inst_rec.Cndtnl_Offer_Must_Be_Stsfd_Ind,
1961 X_Adm_Offer_Resp_Status => p_adm_offer_resp_status, -- Updated field
1962 X_Actual_Response_Dt => l_calc_actual_response_dt, --Updated field
1963 X_Adm_Offer_Dfrmnt_Status => l_adm_offer_dfrmnt_status, --Updated Field
1964 X_Deferred_Adm_Cal_Type => l_def_adm_cal_type, --Updated Field
1965 X_Deferred_Adm_Ci_Sequence_Num => l_def_adm_ci_sequence_num, --Updated field
1966 X_Deferred_Tracking_Id => NULL,
1967 X_Ass_Rank => cur_ad_ps_appl_inst_rec.Ass_Rank,
1968 X_Secondary_Ass_Rank => cur_ad_ps_appl_inst_rec.Secondary_Ass_Rank,
1969 X_Intr_Accept_Advice_Num => cur_ad_ps_appl_inst_rec.Intrntnl_Acceptance_Advice_Num,
1970 X_Ass_Tracking_Id => cur_ad_ps_appl_inst_rec.Ass_Tracking_Id,
1971 X_Fee_Cat => cur_ad_ps_appl_inst_rec.Fee_Cat,
1972 X_Hecs_Payment_Option => cur_ad_ps_appl_inst_rec.Hecs_Payment_Option,
1973 X_Expected_Completion_Yr => cur_ad_ps_appl_inst_rec.Expected_Completion_Yr,
1974 X_Expected_Completion_Perd => cur_ad_ps_appl_inst_rec.Expected_Completion_Perd,
1975 X_Correspondence_Cat => cur_ad_ps_appl_inst_rec.Correspondence_Cat,
1976 X_Enrolment_Cat => cur_ad_ps_appl_inst_rec.Enrolment_Cat,
1977 X_Funding_Source => cur_ad_ps_appl_inst_rec.Funding_Source,
1978 X_Applicant_Acptnce_Cndtn => l_response_comments, --Updated Field
1979 X_Cndtnl_Offer_Cndtn => cur_ad_ps_appl_inst_rec.Cndtnl_Offer_Cndtn,
1980 X_SS_APPLICATION_ID => NULL,
1981 X_SS_PWD => NULL ,
1982 X_AUTHORIZED_DT => cur_ad_ps_appl_inst_rec.authorized_dt,--
1983 X_AUTHORIZING_PERS_ID => cur_ad_ps_appl_inst_rec.authorizing_pers_id,--
1984 X_ENTRY_STATUS => cur_ad_ps_appl_inst_rec.entry_status,
1985 X_ENTRY_LEVEL => cur_ad_ps_appl_inst_rec.entry_level,
1986 X_SCH_APL_TO_ID => cur_ad_ps_appl_inst_rec.sch_apl_to_id,
1987 X_IDX_CALC_DATE => cur_ad_ps_appl_inst_rec.IDX_CALC_DATE,
1988 X_WAITLIST_STATUS => cur_ad_ps_appl_inst_rec.Waitlist_Status,
1989 X_Attribute21 => cur_ad_ps_appl_inst_rec.Attribute21,
1990 X_Attribute22 => cur_ad_ps_appl_inst_rec.Attribute22,
1991 X_Attribute23 => cur_ad_ps_appl_inst_rec.Attribute23,
1992 X_Attribute24 => cur_ad_ps_appl_inst_rec.Attribute24,
1993 X_Attribute25 => cur_ad_ps_appl_inst_rec.Attribute25,
1994 X_Attribute26 => cur_ad_ps_appl_inst_rec.Attribute26,
1998 X_Attribute30 => cur_ad_ps_appl_inst_rec.Attribute30,
1995 X_Attribute27 => cur_ad_ps_appl_inst_rec.Attribute27,
1996 X_Attribute28 => cur_ad_ps_appl_inst_rec.Attribute28,
1997 X_Attribute29 => cur_ad_ps_appl_inst_rec.Attribute29,
1999 X_Attribute31 => cur_ad_ps_appl_inst_rec.Attribute31,
2000 X_Attribute32 => cur_ad_ps_appl_inst_rec.Attribute32,
2001 X_Attribute33 => cur_ad_ps_appl_inst_rec.Attribute33,
2002 X_Attribute34 => cur_ad_ps_appl_inst_rec.Attribute34,
2003 X_Attribute35 => cur_ad_ps_appl_inst_rec.Attribute35,
2004 X_Attribute36 => cur_ad_ps_appl_inst_rec.Attribute36,
2005 X_Attribute37 => cur_ad_ps_appl_inst_rec.Attribute37,
2006 X_Attribute38 => cur_ad_ps_appl_inst_rec.Attribute38,
2007 X_Attribute39 => cur_ad_ps_appl_inst_rec.Attribute39,
2008 X_Attribute40 => cur_ad_ps_appl_inst_rec.Attribute40,
2009 x_fut_acad_cal_type => cur_ad_ps_appl_inst_rec.future_acad_cal_type,
2010 x_fut_acad_ci_sequence_number => cur_ad_ps_appl_inst_rec.future_acad_ci_sequence_number,
2011 x_fut_adm_cal_type => cur_ad_ps_appl_inst_rec.future_adm_cal_type,
2012 x_fut_adm_ci_sequence_number => cur_ad_ps_appl_inst_rec.future_adm_ci_sequence_number,
2013 x_prev_term_adm_appl_number => cur_ad_ps_appl_inst_rec.previous_term_adm_appl_number,
2014 x_prev_term_sequence_number => cur_ad_ps_appl_inst_rec.previous_term_sequence_number,
2015 x_fut_term_adm_appl_number => cur_ad_ps_appl_inst_rec.future_term_adm_appl_number,
2016 x_fut_term_sequence_number => cur_ad_ps_appl_inst_rec.future_term_sequence_number,
2017 x_def_acad_cal_type => l_def_acad_cal_type,--Updated field
2018 x_def_acad_ci_sequence_num => l_def_acad_ci_sequence_num,--Updated field
2019 x_def_prev_term_adm_appl_num => cur_ad_ps_appl_inst_rec.def_prev_term_adm_appl_num,
2020 x_def_prev_appl_sequence_num => cur_ad_ps_appl_inst_rec.def_prev_appl_sequence_num,
2021 x_def_term_adm_appl_num => cur_ad_ps_appl_inst_rec.def_term_adm_appl_num,
2022 x_def_appl_sequence_num => cur_ad_ps_appl_inst_rec.def_appl_sequence_num,
2023 x_decline_ofr_reason => l_decline_ofr_reason,
2024 X_APPL_INST_STATUS => cur_ad_ps_appl_inst_rec.appl_inst_status,
2025 x_ais_reason => cur_ad_ps_appl_inst_rec.ais_reason
2026 );
2027
2028 IF NVL(IGS_AD_GEN_008.ADMP_GET_SAORS(p_adm_offer_resp_status), 'NULL') = 'ACCEPTED' THEN
2029 IF igs_ad_upd_initialise.perform_pre_enrol(cur_ad_ps_appl_inst_rec.Person_Id,
2030 cur_ad_ps_appl_inst_rec.Admission_Appl_Number,
2031 cur_ad_ps_appl_inst_rec.Nominated_Course_Cd,
2032 cur_ad_ps_appl_inst_rec.Sequence_Number,
2033 'Y', -- Confirm course indicator.
2034 'Y', -- Perform eligibility check indicator.
2035 l_enrl_message_name) = FALSE THEN
2036 FND_MESSAGE.SET_NAME('IGS',l_enrl_message_name);
2037 IGS_GE_MSG_STACK.ADD;
2038 RAISE FND_API.G_EXC_ERROR;
2039 END IF;
2040 END IF;
2041
2042 IF FND_API.To_Boolean( p_commit ) THEN
2043 COMMIT;
2044 END IF;
2045 ELSE
2046 RAISE FND_API.G_EXC_ERROR;
2047 END IF;
2048 --check the outcome status
2049 EXCEPTION
2050 WHEN FND_API.G_EXC_ERROR THEN
2051
2052 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in API: FND_API.G_EXC_ERROR : '||SQLERRM);
2053 ROLLBACK TO s_RECORD_OFFER_RESPONSE_pub;
2054 x_return_status := FND_API.G_RET_STS_ERROR ;
2055 igs_ad_gen_016.extract_msg_from_stack (
2056 p_msg_at_index => l_msg_index,
2057 p_return_status => l_return_status,
2058 p_msg_count => x_msg_count,
2059 p_msg_data => x_msg_data,
2060 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2061 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
2062 x_msg_count := x_msg_count-1;
2063 IF cur_ad_ps_appl_inst%ISOPEN THEN
2064 CLOSE cur_ad_ps_appl_inst;
2065 END IF;
2066 WHEN OTHERS THEN
2067 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in API: FND_API.G_EXC_ERROR : '||SQLERRM);
2068 ROLLBACK TO s_RECORD_OFFER_RESPONSE_pub;
2069 x_return_status := FND_API.G_RET_STS_ERROR ;
2070 igs_ad_gen_016.extract_msg_from_stack (
2071 p_msg_at_index => l_msg_index,
2072 p_return_status => l_return_status,
2073 p_msg_count => x_msg_count,
2074 p_msg_data => x_msg_data,
2075 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2076 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-1).text;
2077 IF cur_ad_ps_appl_inst%ISOPEN THEN
2078 CLOSE cur_ad_ps_appl_inst;
2079 END IF;
2080 END RECORD_OFFER_RESPONSE;
2081
2082 PROCEDURE RECORD_QUALIFICATION_CODE(
2083 p_api_version IN NUMBER ,
2084 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2085 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2086 p_validation_level IN NUMBER :=
2087 FND_API.G_VALID_LEVEL_FULL ,
2088 p_person_id IN NUMBER,
2089 p_admission_appl_number IN NUMBER,
2090 p_nominated_course_cd IN VARCHAR2,
2091 p_sequence_number IN NUMBER,
2092 p_qualifying_type_code IN VARCHAR2,
2093 p_qualifying_code IN VARCHAR2,
2094 p_qualifying_value IN VARCHAR2,
2098 )
2095 x_return_status OUT NOCOPY VARCHAR2,
2096 x_msg_count OUT NOCOPY NUMBER,
2097 x_msg_data OUT NOCOPY VARCHAR2
2099 AS
2100 l_api_version CONSTANT NUMBER := '1.0';
2101 l_api_name CONSTANT VARCHAR2(30) := 'RECORD_QUALIFICATION_CODE';
2102 l_msg_index NUMBER;
2103 l_return_status VARCHAR2(1);
2104 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
2105
2106 l_qualifying_type_code VARCHAR2(30);
2107 l_qualifying_code VARCHAR2(30);
2108 l_qualifying_value VARCHAR2(80);
2109
2110
2111
2112 -- All records selected from application Instance Tables
2113
2114 CURSOR c_acai ( p_person_id NUMBER,
2115 p_admission_appl_number NUMBER,
2116 p_nominated_course_cd VARCHAR,
2117 p_sequence_number NUMBER) IS
2118 SELECT
2119 a.*
2120 FROM
2121 igs_ad_ps_appl_inst a,
2122 igs_ad_ou_stat c
2123 WHERE a.person_id = p_person_id
2124 AND a.admission_appl_number = p_admission_appl_number
2125 AND a.nominated_course_cd = p_nominated_course_cd
2126 AND a.sequence_number = p_sequence_number
2127 AND a.adm_outcome_status = c.adm_outcome_status
2128 AND c.s_adm_outcome_status = 'PENDING';
2129
2130 l_appl_inst_rec c_acai%ROWTYPE;
2131
2132 CURSOR c_qualtype (p_person_id IN NUMBER,
2133 p_admission_appl_number IN NUMBER,
2134 p_nominated_course_cd IN VARCHAR,
2135 p_sequence_number IN NUMBER,
2136 p_qualfying_type IN VARCHAR2 ) IS
2137 SELECT ac.rowid,ac.* FROM IGS_AD_APPQUAL_CODE ac WHERE
2138 PERSON_ID = p_person_id
2139 AND ADMISSION_APPL_NUMBER = p_admission_appl_number
2140 AND NOMINATED_COURSE_CD = p_nominated_course_cd
2141 AND SEQUENCE_NUMBER = p_sequence_number
2142 AND qualifying_type_code = p_qualfying_type;
2143 l_qualtype c_qualtype%ROWTYPE;
2144
2145 CURSOR c_qualcode(cp_qual_code IN VARCHAR2,
2146 cp_qual_type_code IN VARCHAR2) IS
2147 SELECT code_id FROM IGS_AD_CODE_CLASSES ac WHERE
2148 UPPER(ac.NAME)=UPPER(cp_qual_code) AND ac.CLASS_TYPE_CODE='IGS_AD_QUAL_TYPE'
2149 AND UPPER(ac.CLASS) = UPPER(cp_qual_type_code);
2150
2151 l_qualcode c_qualcode%ROWTYPE;
2152 l_code_id IGS_AD_CODE_CLASSES.CODE_ID%TYPE;
2153
2154 BEGIN
2155 l_msg_index := 0;
2156 SAVEPOINT RECORD_QUALIFICATION_CODE_pub;
2157 -- Standard call to check for call compatibility.
2158 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
2159 RAISE FND_API.G_EXC_ERROR;
2160 END IF;
2161
2162 -- Initialize message list if p_init_msg_list is set to TRUE.
2163 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2164 FND_MSG_PUB.initialize;
2165 END IF;
2166 l_msg_index := igs_ge_msg_stack.count_msg;
2167
2168 -- Validate all the parameters for their length
2169 -- PERSON_ID
2170 check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
2171 -- P_ADMISSION_APPL_NUMBER
2172 check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
2173 -- p_nominated_course_cd
2174 check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_course_cd));
2175 -- P_SEQUENCE_NUMBER
2176 check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
2177 -- P_qualifying_type_code
2178 check_length('QUALIFYING_TYPE_CODE', 'IGS_AD_APPQUAL_CODE', length(p_qualifying_type_code));
2179 -- P_QUALIFYING_CODE
2180 check_length('NAME', 'IGS_AD_CODE_CLASSES', length(p_qualifying_code));
2181 -- P_QUALIFYING_VALUE
2182 check_length('QUALIFYING_VALUE', 'IGS_AD_APPQUAL_CODE', length(p_qualifying_value));
2183 -- END OF PARAMETER VALIDATIONS.
2184
2185 IF p_person_id = FND_API.G_MISS_NUM OR p_person_id IS NULL THEN
2186 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2187 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_PERSON_ID');
2188 IGS_GE_MSG_STACK.ADD;
2189 RAISE FND_API.G_EXC_ERROR;
2190 END IF;
2191
2192 IF p_admission_appl_number = FND_API.G_MISS_NUM OR p_admission_appl_number IS NULL THEN
2193 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2194 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_ADMISSION_APPL_NUMBER');
2195 IGS_GE_MSG_STACK.ADD;
2196 RAISE FND_API.G_EXC_ERROR;
2197 END IF;
2198
2199 IF p_nominated_course_cd = FND_API.G_MISS_CHAR OR p_nominated_course_cd IS NULL THEN
2200 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2201 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_NOMINATED_COURSE_CD');
2202 IGS_GE_MSG_STACK.ADD;
2203 RAISE FND_API.G_EXC_ERROR;
2204 END IF;
2205
2206 IF p_sequence_number = FND_API.G_MISS_NUM OR p_sequence_number IS NULL THEN
2207 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2208 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_SEQUENCE_NUMBER');
2209 IGS_GE_MSG_STACK.ADD;
2210 RAISE FND_API.G_EXC_ERROR;
2211 END IF;
2212
2213 IF p_qualifying_type_code = FND_API.G_MISS_CHAR OR p_qualifying_type_code IS NULL THEN
2214 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2215 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_QUALIFYING_TYPE_CODE');
2216 IGS_GE_MSG_STACK.ADD;
2217 RAISE FND_API.G_EXC_ERROR;
2218 END IF;
2219 -- Initialize API return status to success
2220 x_return_status := FND_API.G_RET_STS_SUCCESS;
2221
2222 /* OPEN C_ACAI(p_person_id,
2223 p_admission_appl_number,
2224 p_nominated_course_cd,
2225 p_sequence_number);
2226 FETCH c_acai INTO l_appl_inst_rec;
2230 x_return_status := FND_API.G_RET_STS_ERROR;
2227 IF c_acai%NOTFOUND THEN --If no application instance exists for this application
2228 --with s_adm_outcome_status = 'PENDING'
2229 ROLLBACK TO RECORD_QUALIFICATION_CODE_pub;
2231 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ACDX_NO_APPL');
2232 IGS_GE_MSG_STACK.ADD;
2233 RAISE FND_API.G_EXC_ERROR;
2234 ELSIF c_acai%FOUND THEN */
2235 ------------------------------
2236 --Intialization of varable to handle G_MISS_CHAR/NUM/DATE
2237 -------------------------------
2238 IF p_qualifying_type_code = FND_API.G_MISS_CHAR THEN
2239 l_qualifying_type_code := NULL;
2240 ELSE
2241 l_qualifying_type_code := p_qualifying_type_code;
2242 END IF;
2243
2244 IF p_qualifying_code = FND_API.G_MISS_CHAR THEN
2245 l_qualifying_code := NULL;
2246 ELSE
2247 l_qualifying_code := p_qualifying_code;
2248 END IF;
2249
2250 IF p_qualifying_value = FND_API.G_MISS_CHAR THEN
2251 l_qualifying_value := NULL;
2252 ELSE
2253 l_qualifying_value := p_qualifying_value;
2254 END IF;
2255
2256 --Check whthere provided qualifying Type exists for this application or not.
2257 --You can move this code to TBH of this table.
2258 l_qualtype := NULL;
2259 OPEN c_qualtype(p_person_id,
2260 p_admission_appl_number,
2261 p_nominated_course_cd,
2262 p_sequence_number,
2263 l_qualifying_type_code);
2264 FETCH c_qualtype INTO l_qualtype;
2265 IF c_qualtype%NOTFOUND THEN
2266 CLOSE c_qualtype;
2267 FND_MESSAGE.SET_NAME('IGS','IGS_AD_QUAL_TYPE_ERROR');
2268 FND_MESSAGE.SET_TOKEN('QUALIFYING_TYPE',l_qualifying_type_code);
2269 IGS_GE_MSG_STACK.ADD;
2270 RAISE FND_API.G_EXC_ERROR;
2271 END IF;
2272 CLOSE c_qualtype;
2273
2274 --Check if any qual code is there for current qual type and user is trying to pass qual value.
2275 IF l_qualifying_value IS NOT NULL AND l_qualifying_code IS NOT NULL THEN
2276 FND_MESSAGE.SET_NAME('IGS','IGS_AD_QUAL_CODE_VAL');
2277 IGS_GE_MSG_STACK.ADD;
2278 RAISE FND_API.G_EXC_ERROR;
2279 END IF;
2280
2281
2282 --Check if any qual code is there for current qual type and user is trying to pass qual value.
2283 IF l_qualifying_value IS NOT NULL AND l_qualtype.QUALIFYING_CODE_ID IS NOT NULL THEN
2284 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DUP_QUAL_CODE');
2285 FND_MESSAGE.SET_TOKEN('ATTRIBUTE1','QUALIFYING CODE');
2286 FND_MESSAGE.SET_TOKEN('ATTRIBUTE2','QUALIFYING VALUE');
2287 IGS_GE_MSG_STACK.ADD;
2288 RAISE FND_API.G_EXC_ERROR;
2289 END IF;
2290
2291 --Check if any qual value is there for current qual type and user is trying to pass qual code.
2292 IF l_qualifying_code IS NOT NULL AND l_qualtype.QUALIFYING_VALUE IS NOT NULL THEN
2293 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DUP_QUAL_CODE');
2294 FND_MESSAGE.SET_TOKEN('ATTRIBUTE1','QUALIFYING VALUE');
2295 FND_MESSAGE.SET_TOKEN('ATTRIBUTE2','QUALIFYING CODE');
2296 IGS_GE_MSG_STACK.ADD;
2297 RAISE FND_API.G_EXC_ERROR;
2298 END IF;
2299 --will be done in TBH
2300 IF l_qualifying_code IS NOT NULL THEN
2301 OPEN c_qualcode(l_qualifying_code,l_qualifying_type_code);
2302 FETCH c_qualcode INTO l_qualcode;
2303 CLOSE c_qualcode;
2304 IF l_qualcode.code_id IS NULL THEN
2305 FND_MESSAGE.SET_NAME('IGS','IGS_AD_QUAL_CODE_ERROR');
2306 FND_MESSAGE.SET_TOKEN('QUALIFYING_CODE',l_qualifying_code);
2307 IGS_GE_MSG_STACK.ADD;
2308 RAISE FND_API.G_EXC_ERROR;
2309 ELSE
2310 l_code_id := l_qualcode.code_id;
2311 END IF;
2312 END IF;
2313
2314 --Check if any qual code is already exists.
2315 IF l_code_id = l_qualtype.qualifying_code_id THEN
2316 FND_MESSAGE.SET_NAME('IGS','IGS_AD_QUAL_EXISTS');
2317 FND_MESSAGE.SET_TOKEN('QUALIFYING_CODE',l_qualifying_code);
2318 IGS_GE_MSG_STACK.ADD;
2319 RAISE FND_API.G_EXC_ERROR;
2320 END IF;
2321
2322 --Check if any qual code is already exists.
2323 IF l_qualifying_value = l_qualtype.qualifying_value THEN
2324 FND_MESSAGE.SET_NAME('IGS','IGS_AD_QUAL_VAL_EXISTS');
2325 FND_MESSAGE.SET_TOKEN('QUALIFYING_VALUE',l_qualifying_value);
2326 IGS_GE_MSG_STACK.ADD;
2327 RAISE FND_API.G_EXC_ERROR;
2328 END IF;
2329 --Call TBH
2330 igs_ad_appqual_code_pkg.UPDATE_ROW(
2331 x_rowid => l_qualtype.rowid,
2332 x_person_id => p_person_id,
2333 x_admission_appl_number => p_admission_appl_number,
2334 x_nominated_course_cd => p_nominated_course_cd,
2335 x_sequence_number => p_sequence_number,
2336 x_qualifying_type_code => l_qualifying_type_code,
2337 x_qualifying_code_id => l_code_id,
2338 x_qualifying_value => l_qualifying_value,
2339 x_mode => 'R'
2340 );
2341
2342 IF FND_API.To_Boolean( p_commit ) THEN
2343 COMMIT WORK;
2344 END IF;
2345 -- Standard call to get message count and if count is 1, get message info.
2346 EXCEPTION
2347 WHEN FND_API.G_EXC_ERROR THEN
2348 ROLLBACK TO RECORD_QUALIFICATION_CODE_pub;
2349 x_return_status := FND_API.G_RET_STS_ERROR ;
2350 igs_ad_gen_016.extract_msg_from_stack (
2351 p_msg_at_index => l_msg_index,
2352 p_return_status => l_return_status,
2353 p_msg_count => x_msg_count,
2354 p_msg_data => x_msg_data,
2355 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2356 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
2357 x_msg_count := x_msg_count-1;
2358 IF (x_msg_count = 0) THEN
2359 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2360 FND_MSG_PUB.ADD;
2364 FND_MESSAGE.SET_NAME(l_hash_msg_name_text_type_tab(x_msg_count - 1).appl,
2361 x_msg_data := 'IGS_GE_UNHANDLED_EXCEPTION';
2362 ELSE
2363 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count - 1).name;
2365 x_msg_data);
2366 FND_MSG_PUB.ADD;
2367 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count - 1).text;
2368 END IF;
2369
2370 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'aFTER STACK Exception in API: FND_API.G_EXC_ERROR : '|| l_hash_msg_name_text_type_tab(x_msg_count-2).text);
2371 IF c_acai%ISOPEN THEN
2372 CLOSE c_acai;
2373 END IF;
2374
2375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2376 ROLLBACK TO RECORD_QUALIFICATION_CODE_PUB;
2377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2378 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2379 p_data => x_msg_data);
2380
2381 WHEN OTHERS THEN
2382 ROLLBACK TO RECORD_QUALIFICATION_CODE_pub;
2383 igs_ad_gen_016.extract_msg_from_stack (
2384 p_msg_at_index => l_msg_index,
2385 p_return_status => l_return_status,
2386 p_msg_count => x_msg_count,
2387 p_msg_data => x_msg_data,
2388 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2389 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
2390 x_return_status := FND_API.G_RET_STS_ERROR ;
2391 ELSE
2392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2393 END IF;
2394 IF c_acai%ISOPEN THEN
2395 CLOSE c_acai;
2396 END IF;
2397 END RECORD_QUALIFICATION_CODE;
2398
2399
2400 PROCEDURE UPDATE_ENTRY_QUAL_STATUS(
2401 --Standard Parameters Start
2402 p_api_version IN NUMBER,
2403 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2404 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2405 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2406 x_return_status OUT NOCOPY VARCHAR2,
2407 x_msg_count OUT NOCOPY NUMBER,
2408 x_msg_data OUT NOCOPY VARCHAR2,
2409 --Standard parameter ends
2410 p_person_id IN NUMBER,
2411 p_admission_appl_number IN NUMBER,
2412 p_nominated_program_cd IN VARCHAR2,
2413 p_sequence_number IN NUMBER,
2414 p_entry_qual_status IN VARCHAR2
2415 )
2416 AS
2417 -- Declaration of the location variables
2418 l_api_version CONSTANT NUMBER := '1.0';
2419 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ENTRY_QUAL_STATUS';
2420 l_msg_index NUMBER;
2421 l_return_status VARCHAR2(1);
2422 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
2423 l_entry_qual_status VARCHAR2(10);
2424 v_message_name VARCHAR2(30);
2425
2426
2427
2428 -- Cursor to fetch the application instance record details for a given set of person id, application number
2429 -- nominated course code and sequence number
2430
2431 CURSOR c_acai ( cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
2432 cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
2433 cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
2434 cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE)
2435 IS
2436 SELECT aplinst.*, aplinst.rowid
2437 FROM igs_ad_ps_appl_inst aplinst
2438 WHERE aplinst.person_id = cp_person_id
2439 AND aplinst.admission_appl_number = cp_admission_appl_number
2440 AND aplinst.nominated_course_cd = cp_nominated_course_cd
2441 AND aplinst.sequence_number = cp_sequence_number;
2442
2443 l_appl_inst_rec c_acai%ROWTYPE;
2444
2445 -- Cursor to fetch the system admission process type of the admission application
2446 CURSOR c_appl (cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
2447 cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE)
2448 IS
2449 SELECT appl.s_admission_process_type
2450 FROM igs_ad_appl_all appl
2451 WHERE appl.person_id = cp_person_id
2452 AND appl.admission_appl_number = cp_admission_appl_number;
2453
2454 l_s_admission_process_type igs_ad_appl_all.s_admission_process_type%TYPE;
2455
2456 BEGIN
2457 l_msg_index := 0;
2458 SAVEPOINT UPDATE_ENTRY_QUAL_STATUS_SAVE;
2459
2460 -- Standard call to check for call compatibility.
2461 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
2462 RAISE FND_API.G_EXC_ERROR;
2463 END IF;
2464 -- Initialize message list if p_init_msg_list is set to TRUE.
2465 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2466 FND_MSG_PUB.initialize;
2467 END IF;
2468 l_msg_index := igs_ge_msg_stack.count_msg;
2469
2470
2471 -- Validate all the parameters for their length
2472 -- person_id
2473 check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
2474
2475 -- p_admission_appl_number
2476 check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
2477
2478 -- p_nominated_course_cd
2479 check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
2480
2481 -- p_sequence_number
2482 check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
2483
2484 -- p_entry_qual_status
2488
2485 check_length('ADM_ENTRY_QUAL_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_entry_qual_status));
2486
2487 -- end of parameter validations.
2489 -- Show appropriate message when the parameter values are missing
2490
2491 IF p_person_id = FND_API.G_MISS_NUM OR p_person_id IS NULL THEN
2492 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2493 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_PERSON_ID');
2494 IGS_GE_MSG_STACK.ADD;
2495 RAISE FND_API.G_EXC_ERROR;
2496 END IF;
2497
2498 IF p_admission_appl_number = FND_API.G_MISS_NUM OR p_admission_appl_number IS NULL THEN
2499 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2500 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_ADMISSION_APPL_NUMBER');
2501 IGS_GE_MSG_STACK.ADD;
2502 RAISE FND_API.G_EXC_ERROR;
2503 END IF;
2504
2505 IF p_nominated_program_cd = FND_API.G_MISS_CHAR OR p_nominated_program_cd IS NULL THEN
2506 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2507 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_NOMINATED_PROGRAM_CD');
2508 IGS_GE_MSG_STACK.ADD;
2509 RAISE FND_API.G_EXC_ERROR;
2510 END IF;
2511
2512 IF p_sequence_number = FND_API.G_MISS_NUM OR p_sequence_number IS NULL THEN
2513 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2514 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_SEQUENCE_NUMBER');
2515 IGS_GE_MSG_STACK.ADD;
2516 RAISE FND_API.G_EXC_ERROR;
2517 END IF;
2518
2519 IF p_entry_qual_status = FND_API.G_MISS_CHAR OR p_entry_qual_status IS NULL THEN
2520 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
2521 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','P_ENTRY_QUAL_STATUS');
2522 IGS_GE_MSG_STACK.ADD;
2523 RAISE FND_API.G_EXC_ERROR;
2524 END IF;
2525
2526
2527 -- Initialize API return status to success
2528 x_return_status := FND_API.G_RET_STS_SUCCESS;
2529
2530
2531 --Intialization of varable to handle G_MISS_CHAR/NUM/DATE
2532 l_entry_qual_status := p_entry_qual_status;
2533
2534
2535 -- Open the cursor c_acai to fetch the application instance record for the combination given
2536 OPEN c_acai(p_person_id, p_admission_appl_number, p_nominated_program_cd, p_sequence_number);
2537 FETCH c_acai INTO l_appl_inst_rec;
2538 CLOSE c_acai;
2539
2540 IF l_appl_inst_rec.person_id IS NULL THEN
2541 fnd_message.set_name('IGS', 'IGS_AD_INVALID_PARAM_COMB');
2542 IGS_GE_MSG_STACK.ADD;
2543 RAISE FND_API.G_EXC_ERROR;
2544 END IF ;
2545
2546 -- Open the cursor c_appl to fetch the system admission process type for the application
2547 OPEN c_appl(p_person_id, p_admission_appl_number);
2548 FETCH c_appl INTO l_s_admission_process_type;
2549 CLOSE c_appl;
2550
2551
2552 -- Validate the admission entry qualification status
2553 IF IGS_AD_VAL_ACAI_STATUS.admp_val_acai_aeqs (
2554 p_entry_qual_status,
2555 l_appl_inst_rec.adm_outcome_status,
2556 l_s_admission_process_type,
2557 v_message_name) = FALSE THEN
2558 fnd_message.set_name('IGS', v_message_name);
2559 IGS_GE_MSG_STACK.ADD;
2560 RAISE FND_API.G_EXC_ERROR;
2561 END IF;
2562
2563 -- Update the application instance record with the new value of entry qualification status
2564 igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2565 X_Mode => 'S',
2566 X_RowId => l_appl_inst_rec.rowId,
2567 X_Person_Id => p_person_id,
2568 X_Admission_Appl_Number => p_admission_appl_number,
2569 X_Nominated_Course_Cd => p_nominated_program_cd,
2570 X_Sequence_Number => p_sequence_number,
2571 X_Predicted_Gpa => l_appl_inst_rec.Predicted_Gpa,
2572 X_Academic_Index => l_appl_inst_rec.Academic_Index,
2573 X_Adm_Cal_Type => l_appl_inst_rec.Adm_Cal_Type,
2574 X_App_File_Location => l_appl_inst_rec.App_File_Location,
2575 X_Adm_Ci_Sequence_Number => l_appl_inst_rec.Adm_Ci_Sequence_Number,
2576 X_Course_Cd => l_appl_inst_rec.Course_Cd,
2577 X_App_Source_Id => l_appl_inst_rec.App_Source_Id,
2578 X_Crv_Version_Number => l_appl_inst_rec.Crv_Version_Number,
2579 X_Waitlist_Rank => l_appl_inst_rec.Waitlist_Rank,
2580 X_Location_Cd => l_appl_inst_rec.Location_Cd,
2581 X_Attent_Other_Inst_Cd => l_appl_inst_rec.Attent_Other_Inst_Cd,
2582 X_Attendance_Mode => l_appl_inst_rec.Attendance_Mode,
2583 X_Edu_Goal_Prior_Enroll_Id => l_appl_inst_rec.Edu_Goal_Prior_Enroll_Id,
2584 X_Attendance_Type => l_appl_inst_rec.Attendance_Type,
2585 X_Decision_Make_Id => l_appl_inst_rec.Decision_Make_Id,
2586 X_Unit_Set_Cd => l_appl_inst_rec.Unit_Set_Cd,
2587 X_Decision_Date => l_appl_inst_rec.Decision_Date,
2588 X_Attribute_Category => l_appl_inst_rec.Attribute_Category,
2589 X_Attribute1 => l_appl_inst_rec.Attribute1,
2590 X_Attribute2 => l_appl_inst_rec.Attribute2,
2591 X_Attribute3 => l_appl_inst_rec.Attribute3,
2592 X_Attribute4 => l_appl_inst_rec.Attribute4,
2593 X_Attribute5 => l_appl_inst_rec.Attribute5,
2594 X_Attribute6 => l_appl_inst_rec.Attribute6,
2595 X_Attribute7 => l_appl_inst_rec.Attribute7,
2596 X_Attribute8 => l_appl_inst_rec.Attribute8,
2600 X_Attribute12 => l_appl_inst_rec.Attribute12,
2597 X_Attribute9 => l_appl_inst_rec.Attribute9,
2598 X_Attribute10 => l_appl_inst_rec.Attribute10,
2599 X_Attribute11 => l_appl_inst_rec.Attribute11,
2601 X_Attribute13 => l_appl_inst_rec.Attribute13,
2602 X_Attribute14 => l_appl_inst_rec.Attribute14,
2603 X_Attribute15 => l_appl_inst_rec.Attribute15,
2604 X_Attribute16 => l_appl_inst_rec.Attribute16,
2605 X_Attribute17 => l_appl_inst_rec.Attribute17,
2606 X_Attribute18 => l_appl_inst_rec.Attribute18,
2607 X_Attribute19 => l_appl_inst_rec.Attribute19,
2608 X_Attribute20 => l_appl_inst_rec.Attribute20,
2609 X_Attribute21 => l_appl_inst_rec.Attribute21,
2610 X_Attribute22 => l_appl_inst_rec.Attribute22,
2611 X_Attribute23 => l_appl_inst_rec.Attribute23,
2612 X_Attribute24 => l_appl_inst_rec.Attribute24,
2613 X_Attribute25 => l_appl_inst_rec.Attribute25,
2614 X_Attribute26 => l_appl_inst_rec.Attribute26,
2615 X_Attribute27 => l_appl_inst_rec.Attribute27,
2616 X_Attribute28 => l_appl_inst_rec.Attribute28,
2617 X_Attribute29 => l_appl_inst_rec.Attribute29,
2618 X_Attribute30 => l_appl_inst_rec.Attribute30,
2619 X_Attribute31 => l_appl_inst_rec.Attribute31,
2620 X_Attribute32 => l_appl_inst_rec.Attribute32,
2621 X_Attribute33 => l_appl_inst_rec.Attribute33,
2622 X_Attribute34 => l_appl_inst_rec.Attribute34,
2623 X_Attribute35 => l_appl_inst_rec.Attribute35,
2624 X_Attribute36 => l_appl_inst_rec.Attribute36,
2625 X_Attribute37 => l_appl_inst_rec.Attribute37,
2626 X_Attribute38 => l_appl_inst_rec.Attribute38,
2627 X_Attribute39 => l_appl_inst_rec.Attribute39,
2628 X_Attribute40 => l_appl_inst_rec.Attribute40,
2629 X_Decision_Reason_Id => l_appl_inst_rec.Decision_Reason_Id,
2630 X_Us_Version_Number => l_appl_inst_rec.Us_Version_Number,
2631 X_Decision_Notes => l_appl_inst_rec.Decision_Notes,
2632 X_Pending_Reason_Id => l_appl_inst_rec.Pending_Reason_Id,
2633 X_Preference_Number => l_appl_inst_rec.Preference_Number,
2634 X_Adm_Doc_Status => l_appl_inst_rec.Adm_Doc_Status,
2635 X_Adm_Entry_Qual_Status => l_entry_qual_status,
2636 X_Deficiency_In_Prep => l_appl_inst_rec.Deficiency_In_Prep,
2637 X_Late_Adm_Fee_Status => l_appl_inst_rec.Late_Adm_Fee_Status,
2638 X_Spl_Consider_Comments => l_appl_inst_rec.Spl_Consider_Comments,
2639 X_Apply_For_Finaid => l_appl_inst_rec.Apply_For_Finaid,
2640 X_Finaid_Apply_Date => l_appl_inst_rec.Finaid_Apply_Date,
2641 X_Adm_Outcome_Status => l_appl_inst_rec.Adm_Outcome_Status,
2642 X_Adm_Otcm_Stat_Auth_Per_Id => l_appl_inst_rec.adm_otcm_status_auth_person_id,
2643 X_Adm_Outcome_Status_Auth_Dt => l_appl_inst_rec.Adm_Outcome_Status_Auth_Dt,
2644 X_Adm_Outcome_Status_Reason => l_appl_inst_rec.Adm_Outcome_Status_Reason,
2645 X_Offer_Dt => l_appl_inst_rec.Offer_Dt,
2646 X_Offer_Response_Dt => l_appl_inst_rec.Offer_Response_Dt,
2647 X_Prpsd_Commencement_Dt => l_appl_inst_rec.Prpsd_Commencement_Dt,
2648 X_Adm_Cndtnl_Offer_Status => l_appl_inst_rec.Adm_Cndtnl_Offer_Status,
2649 X_Cndtnl_Offer_Satisfied_Dt => l_appl_inst_rec.Cndtnl_Offer_Satisfied_Dt,
2650 X_Cndnl_Ofr_Must_Be_Stsfd_Ind => l_appl_inst_rec.cndtnl_offer_must_be_stsfd_ind,
2651 X_Adm_Offer_Resp_Status => l_appl_inst_rec.Adm_Offer_Resp_Status,
2652 X_Actual_Response_Dt => l_appl_inst_rec.Actual_Response_Dt,
2653 X_Adm_Offer_Dfrmnt_Status => l_appl_inst_rec.Adm_Offer_Dfrmnt_Status,
2654 X_Deferred_Adm_Cal_Type => l_appl_inst_rec.Deferred_Adm_Cal_Type,
2655 X_Deferred_Adm_Ci_Sequence_Num => l_appl_inst_rec.Deferred_Adm_Ci_Sequence_Num,
2656 X_Deferred_Tracking_Id => l_appl_inst_rec.Deferred_Tracking_Id,
2657 X_Ass_Rank => l_appl_inst_rec.Ass_Rank,
2658 X_Secondary_Ass_Rank => l_appl_inst_rec.Secondary_Ass_Rank,
2659 X_Intr_Accept_Advice_Num => l_appl_inst_rec.intrntnl_acceptance_advice_num,
2660 X_Ass_Tracking_Id => l_appl_inst_rec.Ass_Tracking_Id,
2661 X_Fee_Cat => l_appl_inst_rec.Fee_Cat,
2662 X_Hecs_Payment_Option => l_appl_inst_rec.Hecs_Payment_Option,
2663 X_Expected_Completion_Yr => l_appl_inst_rec.Expected_Completion_Yr,
2664 X_Expected_Completion_Perd => l_appl_inst_rec.Expected_Completion_Perd,
2665 X_Correspondence_Cat => l_appl_inst_rec.Correspondence_Cat,
2666 X_Enrolment_Cat => l_appl_inst_rec.Enrolment_Cat,
2667 X_Funding_Source => l_appl_inst_rec.Funding_Source,
2668 X_Applicant_Acptnce_Cndtn => l_appl_inst_rec.Applicant_Acptnce_Cndtn,
2669 X_Cndtnl_Offer_Cndtn => l_appl_inst_rec.Cndtnl_Offer_Cndtn,
2670 X_SS_APPLICATION_ID => l_appl_inst_rec.SS_APPLICATION_ID,
2671 X_SS_PWD => l_appl_inst_rec.SS_PWD,
2672 X_AUTHORIZED_DT => l_appl_inst_rec.AUTHORIZED_DT,
2673 X_AUTHORIZING_PERS_ID => l_appl_inst_rec.AUTHORIZING_PERS_ID,
2674 X_ENTRY_STATUS => l_appl_inst_rec.ENTRY_STATUS,
2675 X_ENTRY_LEVEL => l_appl_inst_rec.ENTRY_LEVEL,
2676 X_SCH_APL_TO_ID => l_appl_inst_rec.SCH_APL_TO_ID,
2677 X_IDX_CALC_DATE => l_appl_inst_rec.IDX_CALC_DATE,
2678 X_WAITLIST_STATUS => l_appl_inst_rec.WAITLIST_STATUS,
2679 x_fut_acad_cal_type => l_appl_inst_rec.future_acad_cal_type,
2680 x_fut_acad_ci_sequence_number => l_appl_inst_rec.future_acad_ci_sequence_number,
2681 x_fut_adm_cal_type => l_appl_inst_rec.future_adm_cal_type,
2682 x_fut_adm_ci_sequence_number => l_appl_inst_rec.future_adm_ci_sequence_number,
2683 x_prev_term_adm_appl_number => l_appl_inst_rec.previous_term_adm_appl_number,
2684 x_prev_term_sequence_number => l_appl_inst_rec.previous_term_sequence_number,
2685 x_fut_term_adm_appl_number => l_appl_inst_rec.future_term_adm_appl_number,
2686 x_fut_term_sequence_number => l_appl_inst_rec.future_term_sequence_number,
2687 x_def_acad_cal_type => l_appl_inst_rec.def_acad_cal_type,
2688 x_def_acad_ci_sequence_num => l_appl_inst_rec.def_acad_ci_sequence_num,
2689 x_def_prev_term_adm_appl_num => l_appl_inst_rec.def_prev_term_adm_appl_num,
2690 x_def_prev_appl_sequence_num => l_appl_inst_rec.def_prev_appl_sequence_num,
2691 x_def_term_adm_appl_num => l_appl_inst_rec.def_term_adm_appl_num,
2692 x_def_appl_sequence_num => l_appl_inst_rec.def_appl_sequence_num,
2693 x_appl_inst_status => l_appl_inst_rec.appl_inst_status,
2694 x_ais_reason => l_appl_inst_rec.ais_reason,
2695 x_decline_ofr_reason => l_appl_inst_rec.decline_ofr_reason);
2696
2697 -- Standard check of p_commit.
2698 IF FND_API.To_Boolean( p_commit ) THEN
2699 COMMIT WORK;
2700 END IF;
2701
2702
2703 EXCEPTION
2704 WHEN FND_API.G_EXC_ERROR THEN
2705
2706 ROLLBACK TO UPDATE_ENTRY_QUAL_STATUS_SAVE;
2707 x_return_status := FND_API.G_RET_STS_ERROR;
2708
2709 igs_ad_gen_016.extract_msg_from_stack (
2710 p_msg_at_index => l_msg_index,
2711 p_return_status => l_return_status,
2712 p_msg_count => x_msg_count,
2713 p_msg_data => x_msg_data,
2714 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2715
2716 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
2717 x_msg_count := x_msg_count-1;
2718
2719 IF (x_msg_count = 0) THEN
2720 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2721 FND_MSG_PUB.ADD;
2722 IGS_GE_MSG_STACK.ADD;
2723 x_msg_data := FND_MESSAGE.GET;
2724 ELSE
2725 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count - 1).name;
2726 FND_MESSAGE.SET_NAME(l_hash_msg_name_text_type_tab(x_msg_count - 1).appl,x_msg_data);
2727 FND_MSG_PUB.ADD;
2728 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count - 1).text;
2729 END IF;
2730
2731 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'aFTER STACK Exception in API: FND_API.G_EXC_ERROR : '|| l_hash_msg_name_text_type_tab(x_msg_count-2).text);
2732 IF c_acai%ISOPEN THEN
2733 CLOSE c_acai;
2734 END IF;
2735
2736 IF c_appl%ISOPEN THEN
2737 CLOSE c_acai;
2738 END IF;
2739
2740
2741 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2742
2743 ROLLBACK TO UPDATE_ENTRY_QUAL_STATUS_SAVE;
2744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2745 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2746
2747
2748 WHEN OTHERS THEN
2749
2750 ROLLBACK TO UPDATE_ENTRY_QUAL_STATUS_SAVE;
2751 igs_ad_gen_016.extract_msg_from_stack (
2752 p_msg_at_index => l_msg_index,
2753 p_return_status => l_return_status,
2754 p_msg_count => x_msg_count,
2755 p_msg_data => x_msg_data,
2756 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2757
2758 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
2759 x_return_status := FND_API.G_RET_STS_ERROR ;
2760 ELSE
2761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2762 END IF;
2763
2764 IF c_acai%ISOPEN THEN
2765 CLOSE c_acai;
2766 END IF;
2767
2768 IF c_appl%ISOPEN THEN
2769 CLOSE c_acai;
2770 END IF;
2771
2772 END UPDATE_ENTRY_QUAL_STATUS;
2773
2774
2775 END igs_admapplication_pub;