[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_SPAT_LGCY_PUB
Source
1 PACKAGE BODY igs_en_spat_lgcy_pub AS
2 /* $Header: IGSENA9B.pls 120.3 2005/09/23 08:23:23 appldev ship $ */
3
4 /*****************************************************************************
5 Who When What
6 sgurusam 20-Jun-05 Added PLAN_SHT_STATUS value as 'NONE' in insert the statement
7 of table igs_en_spa_terms
8 ******************************************************************************/
9
10 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_EN_SPAT_LGCY_PUB';
11
12 FUNCTION validate_parameters(
13 p_spat_rec IN spat_rec_type)
14 RETURN BOOLEAN AS
15 /*----------------------------------------------------------------------------
16 || Created By : vkarthik
17 || Created On : 11-Dec-2003
18 || Purpose : validates the spat_int record attributes
19 || Known limitations, enhancements or remarks :
20 || Change History :
21 || Who When What
22 ------------------------------------------------------------------------------*/
23
24 l_message_count NUMBER(5);
25 l_ret_status BOOLEAN := TRUE;
26 l_get_calendar_instance_return VARCHAR2(10);
27
28 BEGIN
29 IF p_spat_rec.person_number IS NULL THEN
30 l_ret_status := FALSE;
31 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PER_NUM_NULL');
32 FND_MSG_PUB.ADD;
33 END IF;
34
35 IF p_spat_rec.program_cd IS NULL THEN
36 l_ret_status := FALSE;
37 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_NULL');
38 FND_MSG_PUB.ADD;
39 END IF;
40
41 IF p_spat_rec.program_version IS NULL THEN
42 l_ret_status := FALSE;
43 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_VER_NULL');
44 FND_MSG_PUB.ADD;
45 END IF;
46
47 IF p_spat_rec.location_cd IS NULL THEN
48 l_ret_status := FALSE;
49 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_LOC_NULL');
50 FND_MSG_PUB.ADD;
51 END IF;
52
53 IF p_spat_rec.attendance_mode IS NULL THEN
54 l_ret_status := FALSE;
55 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_ATT_MOD_NULL');
56 FND_MSG_PUB.ADD;
57 END IF;
58
59 IF p_spat_rec.attendance_type IS NULL THEN
60 l_ret_status := FALSE;
61 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_ATT_TYPE_NULL');
62 FND_MSG_PUB.ADD;
63 END IF;
64
65 IF p_spat_rec.key_program_flag IS NULL THEN
66 l_ret_status := FALSE;
67 FND_MESSAGE.SET_NAME('IGS','IGS_EN_KEY_PROG_NULL');
68 FND_MSG_PUB.ADD;
69 END IF;
70
71 IF p_spat_rec.acad_cal_type IS NULL THEN
72 l_ret_status := FALSE;
73 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ACAD_CAL_NULL');
74 FND_MSG_PUB.ADD;
75 END IF;
76
77 IF p_spat_rec.term_cal_alternate_cd IS NULL THEN
78 l_ret_status := FALSE;
79 FND_MESSAGE.SET_NAME('IGS','IGS_EN_TERM_CAL_NULL');
80 FND_MSG_PUB.ADD;
81 END IF;
82
83 IF (p_spat_rec.attribute_category IS NOT NULL OR
84 p_spat_rec.attribute1 IS NOT NULL OR
85 p_spat_rec.attribute2 IS NOT NULL OR
86 p_spat_rec.attribute3 IS NOT NULL OR
87 p_spat_rec.attribute4 IS NOT NULL OR
88 p_spat_rec.attribute5 IS NOT NULL OR
89 p_spat_rec.attribute6 IS NOT NULL OR
90 p_spat_rec.attribute7 IS NOT NULL OR
91 p_spat_rec.attribute8 IS NOT NULL OR
92 p_spat_rec.attribute9 IS NOT NULL OR
93 p_spat_rec.attribute10 IS NOT NULL OR
94 p_spat_rec.attribute11 IS NOT NULL OR
95 p_spat_rec.attribute12 IS NOT NULL OR
96 p_spat_rec.attribute12 IS NOT NULL OR
97 p_spat_rec.attribute13 IS NOT NULL OR
98 p_spat_rec.attribute14 IS NOT NULL OR
99 p_spat_rec.attribute15 IS NOT NULL OR
100 p_spat_rec.attribute16 IS NOT NULL OR
101 p_spat_rec.attribute17 IS NOT NULL OR
102 p_spat_rec.attribute18 IS NOT NULL OR
103 p_spat_rec.attribute19 IS NOT NULL OR
104 p_spat_rec.attribute20 IS NOT NULL) THEN
105 IF NOT igs_ad_imp_018.validate_desc_flex (
106 p_attribute_category => p_spat_rec.attribute_category ,
107 p_attribute1 => p_spat_rec.attribute1,
108 p_attribute2 => p_spat_rec.attribute2,
109 p_attribute3 => p_spat_rec.attribute3,
110 p_attribute4 => p_spat_rec.attribute4,
111 p_attribute5 => p_spat_rec.attribute5,
112 p_attribute6 => p_spat_rec.attribute6,
113 p_attribute7 => p_spat_rec.attribute7,
114 p_attribute8 => p_spat_rec.attribute8,
115 p_attribute9 => p_spat_rec.attribute9,
116 p_attribute10 => p_spat_rec.attribute10,
117 p_attribute11 => p_spat_rec.attribute11,
118 p_attribute12 => p_spat_rec.attribute12,
119 p_attribute13 => p_spat_rec.attribute13,
120 p_attribute14 => p_spat_rec.attribute14,
121 p_attribute15 => p_spat_rec.attribute15,
122 p_attribute16 => p_spat_rec.attribute16,
123 p_attribute17 => p_spat_rec.attribute17,
124 p_attribute18 => p_spat_rec.attribute18,
125 p_attribute19 => p_spat_rec.attribute19,
126 p_attribute20 => p_spat_rec.attribute20,
127 p_desc_flex_name => 'IGS_EN_SPA_TERMS_FLEX') THEN
128 l_ret_status := FALSE;
129 FND_MESSAGE.SET_NAME('IGS','IGS_AD_INVALID_DESC_FLEX');
130 FND_MSG_PUB.ADD;
131 END IF;
132 END IF;
133
134 IF p_spat_rec.program_cd IS NOT NULL THEN
135 BEGIN
136 igs_en_stdnt_ps_att_pkg.check_constraints(
137 column_name => 'COURSE_CD',
138 column_value => p_spat_rec.program_cd);
139 EXCEPTION
140 WHEN OTHERS THEN
141 l_ret_status := FALSE;
142 l_message_count := FND_MSG_PUB.COUNT_MSG;
143 FND_MSG_PUB.DELETE_MSG(l_message_count);
144 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_CD_UPPCASE');
145 FND_MSG_PUB.ADD;
146 END;
147 END IF;
148
149 IF p_spat_rec.acad_cal_type IS NOT NULL THEN
150 BEGIN
151 igs_en_stdnt_ps_att_pkg.check_constraints(
152 column_name => 'CAL_TYPE',
153 column_value => p_spat_rec.acad_cal_type);
154 EXCEPTION
155 WHEN OTHERS THEN
156 l_ret_status := FALSE;
157 l_message_count := FND_MSG_PUB.COUNT_MSG;
158 FND_MSG_PUB.DELETE_MSG(l_message_count);
159 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ACAD_CAL_UPPCASE');
160 FND_MSG_PUB.ADD;
161 END;
162 END IF;
163
164 IF p_spat_rec.location_cd IS NOT NULL THEN
165 BEGIN
166 igs_en_stdnt_ps_att_pkg.check_constraints(
167 column_name => 'LOCATION_CD',
168 column_value => p_spat_rec.location_cd);
169 EXCEPTION
170 WHEN OTHERS THEN
171 l_ret_status := FALSE;
172 l_message_count := FND_MSG_PUB.COUNT_MSG;
173 FND_MSG_PUB.DELETE_MSG(l_message_count);
174 FND_MESSAGE.SET_NAME('IGS','IGS_EN_LOC_CD_UCASE');
175 FND_MSG_PUB.ADD;
176 END;
177 END IF;
178
179 IF p_spat_rec.attendance_mode IS NOT NULL THEN
180 BEGIN
181 igs_en_stdnt_ps_att_pkg.check_constraints (
182 column_name => 'ATTENDANCE_MODE',
183 column_value => p_spat_rec.attendance_mode);
184 EXCEPTION
185 WHEN OTHERS THEN
186 l_ret_status := FALSE;
187 l_message_count := FND_MSG_PUB.COUNT_MSG;
188 FND_MSG_PUB.DELETE_MSG(l_message_count);
189 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_ATT_MODE_UCASE');
190 FND_MSG_PUB.ADD;
191 END;
192 END IF;
193
194 IF p_spat_rec.attendance_type IS NOT NULL THEN
195 BEGIN
196 igs_en_stdnt_ps_att_pkg.check_constraints(
197 column_name => 'ATTENDANCE_TYPE',
198 column_value => p_spat_rec.attendance_type);
199 EXCEPTION
200 WHEN OTHERS THEN
201 l_ret_status := FALSE;
202 l_message_count := FND_MSG_PUB.COUNT_MSG;
203 FND_MSG_PUB.DELETE_MSG(l_message_count);
204 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_ATT_TYPE_UCASE');
205 FND_MSG_PUB.ADD;
206 END;
207 END IF;
208
209 IF p_spat_rec.key_program_flag IS NOT NULL THEN
210 BEGIN
211 igs_en_stdnt_ps_att_pkg.check_constraints(
212 column_name => 'KEY_PROGRAM',
213 column_value => p_spat_rec.key_program_flag);
214 EXCEPTION
215 WHEN OTHERS THEN
216 l_ret_status := FALSE;
217 l_message_count := FND_MSG_PUB.COUNT_MSG;
218 FND_MSG_PUB.DELETE_MSG(l_message_count);
219 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_KEY_INVALID');
220 FND_MSG_PUB.ADD;
221 END;
222 END IF;
223 RETURN l_ret_status;
224 END validate_parameters;
225
226
227 FUNCTION validate_spat_db_cons (
228 p_person_id IN NUMBER,
229 p_term_cal_type IN VARCHAR2,
230 p_term_sequence_number IN NUMBER,
231 p_spat_rec IN spat_rec_type)
232 RETURN VARCHAR2 AS
233 /*----------------------------------------------------------------------------
234 || Created By : vkarthik
235 || Created On : 11-Dec-2003
236 || Purpose : validates database constraints
237 || Known limitations, enhancements or remarks :
238 || Change History :
239 || Who When What
240 ------------------------------------------------------------------------------*/
241
242 l_ret_status VARCHAR2(1) := 'S';
243 l_get_pk_for_validation_return BOOLEAN;
244 l_get_uk_for_validation_return BOOLEAN;
245
246 BEGIN
247 l_get_uk_for_validation_return := igs_en_spa_terms_pkg.get_uk_for_validation(
248 p_person_id,
249 p_spat_rec.program_cd,
250 p_term_cal_type,
251 p_term_sequence_number);
252 IF l_get_uk_for_validation_return = TRUE THEN
253 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SPAT_EXISTS');
254 FND_MSG_PUB.ADD;
255 RETURN 'W';
256 END IF;
257
258 l_get_pk_for_validation_return := igs_en_stdnt_ps_att_pkg.get_pk_for_validation(
259 p_person_id,
260 p_spat_rec.program_cd);
261 IF l_get_pk_for_validation_return = FALSE THEN
262 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PRSNID_PRGCD_NOT_MATCH');
263 FND_MSG_PUB.ADD;
264 l_ret_status := 'E';
265 END IF;
266
267 IF p_spat_rec.fee_cat IS NOT NULL THEN
268 l_get_pk_for_validation_return := igs_fi_fee_cat_pkg.get_pk_for_validation(
269 p_spat_rec.fee_cat);
270 IF l_get_pk_for_validation_return = FALSE THEN
271 FND_MESSAGE.SET_NAME('IGS','IGS_EN_FEE_CAT_NOT_FOUND');
272 FND_MSG_PUB.ADD;
273 l_ret_status := 'E';
274 END IF;
275 END IF;
276 RETURN l_ret_status;
277 END validate_spat_db_cons;
278
279
280 FUNCTION validate_pre_spat(
281 p_person_id IN NUMBER,
282 p_term_cal_type IN VARCHAR2,
283 p_term_sequence_number IN NUMBER,
284 p_spat_rec IN spat_rec_type)
285 RETURN BOOLEAN AS
286 /*----------------------------------------------------------------------------
287 || Created By : vkarthik
288 || Created On : 11-Dec-2003
289 || Purpose : validates business rules
290 || Known limitations, enhancements or remarks :
291 || Change History :
292 || Who When What
293 ------------------------------------------------------------------------------*/
294
295 CURSOR c_igs_en_spa_terms_career(
296 pc_course_type igs_ps_ver.course_type%TYPE,
297 pc_person_id NUMBER,
298 pc_term_cal_type VARCHAR2,
299 pc_term_sequence_number NUMBER,
300 pc_program_cd igs_en_spa_terms.program_cd%TYPE) IS
301 SELECT 'X'
302 FROM igs_en_spa_terms spa_terms, igs_ps_ver ps_ver
303 WHERE
304 spa_terms.person_id = pc_person_id AND
305 spa_terms.term_cal_type = pc_term_cal_type AND
306 spa_terms.term_sequence_number = pc_term_sequence_number AND
307 spa_terms.program_cd <> pc_program_cd AND
308 ps_ver.course_type = pc_course_type AND
309 spa_terms.program_cd = ps_ver.course_cd AND
310 spa_terms.program_version = ps_ver.version_number;
311
312 CURSOR c_igs_en_spa_terms_normal(
313 pc_person_id NUMBER,
314 pc_term_cal_type VARCHAR2,
315 pc_term_sequence_number NUMBER,
316 pc_program_cd igs_en_spa_terms.program_cd%TYPE) IS
317 SELECT 'X'
318 FROM igs_en_spa_terms
319 WHERE
320 person_id = pc_person_id AND
321 term_cal_type = pc_term_cal_type AND
322 term_sequence_number = pc_term_sequence_number AND
323 program_cd <> pc_program_cd AND
324 key_program_flag = 'Y';
325
326 CURSOR c_course_type_p_spat_rec(
327 cp_program_cd igs_ps_ver.course_cd%TYPE,
328 cp_program_version igs_ps_ver.version_number%TYPE) IS
329 SELECT course_type
330 FROM igs_ps_ver
331 WHERE
332 course_cd = cp_program_cd AND
333 version_number = cp_program_version;
334
335 l_return_value BOOLEAN := TRUE;
336 l_igs_en_spa_terms_career c_igs_en_spa_terms_career%ROWTYPE;
337 l_igs_en_spa_terms_normal c_igs_en_spa_terms_normal%ROWTYPE;
338 l_course_type_p_spat_rec igs_ps_ver.course_type%TYPE;
339 l_career_flag VARCHAR2(1);
340
341 BEGIN
342 OPEN c_course_type_p_spat_rec(p_spat_rec.program_cd, p_spat_rec.program_version);
343 FETCH c_course_type_p_spat_rec INTO l_course_type_p_spat_rec;
344 CLOSE c_course_type_p_spat_rec;
345
346 l_career_flag := NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'), 'N');
347
348 IF l_career_flag = 'Y' THEN
349 OPEN c_igs_en_spa_terms_career(
350 l_course_type_p_spat_rec,
351 p_person_id,
352 p_term_cal_type,
353 p_term_sequence_number,
354 p_spat_rec.program_cd);
355 FETCH c_igs_en_spa_terms_career INTO l_igs_en_spa_terms_career;
356 IF c_igs_en_spa_terms_career%FOUND THEN
357 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SPAT_EXISTS_CAREER');
358 FND_MSG_PUB.ADD;
359 l_return_value := FALSE;
360 END IF;
361 CLOSE c_igs_en_spa_terms_career;
362 END IF;
363
364 -- if the new record is a key program
365 IF p_spat_rec.key_program_flag = 'Y' THEN
366 -- check if any other key program already exists for the term and person
367 OPEN c_igs_en_spa_terms_normal(
368 p_person_id,
369 p_term_cal_type,
370 p_term_sequence_number,
371 p_spat_rec.program_cd);
372 FETCH c_igs_en_spa_terms_normal INTO l_igs_en_spa_terms_normal;
373 IF c_igs_en_spa_terms_normal%FOUND THEN
374 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MORE_KEY_IN_TERM');
375 FND_MSG_PUB.ADD;
376 l_return_value := FALSE;
377 END IF;
378 CLOSE c_igs_en_spa_terms_normal;
379 END IF;
380 RETURN l_return_value;
381 END validate_pre_spat;
382
383 PROCEDURE validate_post_spat(
384 p_person_id IN igs_en_spa_terms.person_id%TYPE,
385 p_term_cal_type IN igs_en_spa_terms.term_cal_type%TYPE,
386 p_term_sequence_number IN igs_en_spa_terms.term_sequence_number%TYPE,
387 p_class_standing_id IN igs_en_spa_terms.class_standing_id%TYPE,
388 p_coo_id IN igs_en_spa_terms.coo_id%TYPE,
389 p_spat_rec IN spat_rec_type) AS
390 /*----------------------------------------------------------------------------
391 || Created By : vkarthik
392 || Created On : 11-Dec-2003
393 || Purpose : procedure takes care of backward gap and forward gap filling
394 || Known limitations, enhancements or remarks :
395 || Change History :
396 || Who When What
397 ------------------------------------------------------------------------------*/
398
399 v_term_rec igs_en_spa_terms_api.EN_SPAT_REC_TYPE%TYPE;
400 BEGIN
401 -- fill gaps
402 v_term_rec.person_id := p_person_id;
403 v_term_rec.program_cd := p_spat_rec.program_cd;
404 v_term_rec.term_cal_type :=p_term_cal_type;
405 v_term_rec.term_sequence_number := p_term_sequence_number;
406 v_term_rec.program_version := p_spat_rec.program_version;
407 v_term_rec.coo_id :=p_coo_id;
408 v_term_rec.acad_cal_type := p_spat_rec.acad_cal_type;
409 v_term_rec.key_program_flag :=p_spat_rec.key_program_flag;
410 v_term_rec.location_cd :=p_spat_rec.location_cd;
411 v_term_rec.attendance_mode :=p_spat_rec.attendance_mode;
412 v_term_rec.attendance_type :=p_spat_rec.attendance_type;
413 v_term_rec.fee_cat := p_spat_rec.fee_cat;
414 v_term_rec.class_standing_id := p_class_standing_id;
415
416 IF p_term_cal_type IS NOT NULL AND p_term_sequence_number IS NOT NULL THEN
417 igs_en_spa_terms_api.backward_gap_fill ( v_term_rec);
418 igs_en_spa_terms_api.forward_gap_fill ( v_term_rec);
419 END IF;
420
421 END validate_post_spat;
422
423 PROCEDURE create_spa_t (
424 p_api_version IN NUMBER,
425 p_init_msg_list IN VARCHAR2,
426 p_commit IN VARCHAR2,
427 p_validation_level IN NUMBER,
428 p_spat_rec IN spat_rec_type,
429 x_return_status OUT NOCOPY VARCHAR2,
430 x_msg_count OUT NOCOPY NUMBER,
431 x_msg_data OUT NOCOPY VARCHAR2 ) AS
432 /*----------------------------------------------------------------------------
433 || Created By : vkarthik
434 || Created On : 11-Dec-2003
435 || Purpose : public procedure that inserts the records into spat
436 || Known limitations, enhancements or remarks :
437 || Change History :
438 || Who When What
439 ------------------------------------------------------------------------------*/
440
441 CURSOR c_igs_en_spa_terms_s IS
442 SELECT igs_en_spa_terms_s.NEXTVAL
443 FROM dual;
444
445 l_api_name CONSTANT VARCHAR2(30) := 'create_spa_t';
446 l_api_version CONSTANT NUMBER := 1.0;
447 l_validation_failed BOOLEAN :=FALSE;
448 l_get_calendar_instance_return VARCHAR2(10);
449 l_validate_db_ret_status VARCHAR2(1);
450 l_spa_terms_term_record_id igs_en_spa_terms.term_record_id%TYPE;
451 l_request_id igs_en_spa_terms.request_id%TYPE;
452 l_program_application_id igs_en_spa_terms.program_application_id%TYPE;
453 l_program_id igs_en_spa_terms.program_id%TYPE;
454 l_program_update_date igs_en_spa_terms.program_update_date%TYPE;
455 l_person_id igs_en_spa_terms.person_id%TYPE;
456 l_term_cal_type igs_en_spa_terms.term_cal_type%TYPE;
457 l_term_sequence_number igs_en_spa_terms.term_sequence_number%TYPE;
458 l_ci_start_dt DATE;
459 l_ci_end_dt DATE;
460 l_class_standing_id igs_en_spa_terms.class_standing_id%TYPE;
461 l_coo_id igs_en_spa_terms.coo_id%TYPE;
462
463 BEGIN
464 -- create a save point
465 SAVEPOINT create_spa_t_svpt;
466
467 --check for compatible API call
468 IF NOT FND_API.COMPATIBLE_API_CALL (
469 l_api_version,
470 p_api_version,
471 l_api_name,
472 g_pkg_name) THEN
473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
474 END IF;
475
476 -- If the calling program has passed the parameter for initializing the message list
477 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
478 FND_MSG_PUB.INITIALIZE;
479 END IF;
480
481 -- Set the return status to success
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483
484 -- validate parameters
485 IF NOT validate_parameters(
486 p_spat_rec => p_spat_rec) THEN
487 x_return_status := FND_API.G_RET_STS_ERROR;
488 l_validation_failed := TRUE;
489 END IF;
490
491 -- if no validation has failed then derive person_id, term_cal_type, term_sequence_number
492 -- class_standing_id, coo_id
493 IF NOT l_validation_failed THEN
494 l_person_id := igs_ge_gen_003.get_person_id(p_spat_rec.person_number);
495 IF l_person_id IS NULL THEN
496 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_PERSON_NUMBER');
497 FND_MSG_PUB.ADD;
498 x_return_status := FND_API.G_RET_STS_ERROR;
499 l_validation_failed := TRUE;
500 END IF;
501
502 igs_ge_gen_003.get_calendar_instance(
503 p_spat_rec.term_cal_alternate_cd,
504 '''LOAD''',
505 l_term_cal_type,
506 l_term_sequence_number,
507 l_ci_start_dt,
508 l_ci_end_dt,
509 l_get_calendar_instance_return);
510 IF l_get_calendar_instance_return = 'INVALID' THEN
511 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ALT_CD_NO_TRM_FND');
512 FND_MSG_PUB.ADD;
513 x_return_status := FND_API.G_RET_STS_ERROR;
514 l_validation_failed := TRUE;
515 ELSE
516 IF l_get_calendar_instance_return = 'MULTIPLE' THEN
517 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MULTI_TRM_CAL_FND');
518 FND_MSG_PUB.ADD;
519 x_return_status := FND_API.G_RET_STS_ERROR;
520 l_validation_failed := TRUE;
521 END IF;
522 END IF;
523
524 IF p_spat_rec.class_standing IS NOT NULL THEN
525 l_class_standing_id := igs_en_gen_legacy.get_class_std_id(p_spat_rec.class_standing);
526 IF l_class_standing_id IS NULL THEN
527 FND_MESSAGE.SET_NAME('IGS','IGS_EN_CLASS_STD_ID_NOT_FOUND');
528 FND_MSG_PUB.ADD;
529 x_return_status := FND_API.G_RET_STS_ERROR;
530 l_validation_failed := TRUE;
531 END IF;
532 END IF;
533
534 l_coo_id := igs_en_gen_legacy.get_coo_id(
535 p_spat_rec.program_cd,
536 p_spat_rec.program_version,
537 p_spat_rec.acad_cal_type,
538 p_spat_rec.location_cd,
539 p_spat_rec.attendance_mode,
540 p_spat_rec.attendance_type);
541 IF l_coo_id IS NULL THEN
542 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PS_OFR_OPT_NOT_FOUND');
543 FND_MSG_PUB.ADD;
544 x_return_status := FND_API.G_RET_STS_ERROR;
545 l_validation_failed := TRUE;
546 END IF;
547 END IF;
548
549 -- if no validation failed then check for database constraints
550 IF NOT l_validation_failed THEN
551 l_validate_db_ret_status := validate_spat_db_cons (
552 l_person_id,
553 l_term_cal_type,
554 l_term_sequence_number,
555 p_spat_rec);
556 IF l_validate_db_ret_status = 'W' THEN
557 x_return_status := 'W';
558 l_validation_failed := TRUE;
559 END IF;
560 IF l_validate_db_ret_status = 'E' THEN
561 x_return_status := FND_API.G_RET_STS_ERROR;
562 l_validation_failed := TRUE;
563 END IF;
564 END IF; --end of database constraints validation
565
566 -- if no validation failed then check the business rules and forward ripple
567 IF NOT l_validation_failed THEN
568 IF NOT validate_pre_spat (
569 l_person_id,
570 l_term_cal_type,
571 l_term_sequence_number,
572 p_spat_rec) THEN
573 x_return_status := FND_API.G_RET_STS_ERROR;
574 l_validation_failed := TRUE;
575 END IF;
576 END IF;
577
578 -- if no validation failed then insert the record into spa terms table
579 IF NOT l_validation_failed THEN
580
581 --get the nextval from sequence igs_en_spa_terms_s for term_record_id
582 OPEN c_igs_en_spa_terms_s;
583 FETCH c_igs_en_spa_terms_s INTO l_spa_terms_term_record_id;
584 CLOSE c_igs_en_spa_terms_s;
585
586 -- derive the who column values
587 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
588 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
589 l_program_application_id := FND_GLOBAL.PROG_APPL_ID;
590 IF (l_request_id = -1) THEN
591 l_request_id := NULL;
592 l_program_id := NULL;
593 l_program_application_id:= NULL;
594 l_program_update_date := NULL;
595 ELSE
596 l_program_update_date := SYSDATE;
597 END IF;
598
599 -- insert legacy record into spa_terms
600 INSERT INTO igs_en_spa_terms (
601 term_record_id,
602 person_id,
603 program_cd,
604 program_version,
605 acad_cal_type,
606 term_cal_type,
607 term_sequence_number,
608 key_program_flag,
609 location_cd,
610 attendance_mode,
611 attendance_type,
612 fee_cat,
613 coo_id,
614 class_standing_id,
615 created_by,
616 creation_date,
617 last_updated_by,
618 last_update_date,
619 last_update_login,
620 request_id,
621 program_application_id,
622 program_id,
623 program_update_date,
624 attribute_category,
625 attribute1,
626 attribute2,
627 attribute3,
628 attribute4,
629 attribute5,
630 attribute6,
631 attribute7,
632 attribute8,
633 attribute9,
634 attribute10,
635 attribute11,
636 attribute12,
637 attribute13,
638 attribute14,
639 attribute15,
640 attribute16,
641 attribute17,
642 attribute18,
643 attribute19,
644 attribute20,
645 plan_sht_status)
646 VALUES (
647 l_spa_terms_term_record_id,
648 l_person_id,
649 p_spat_rec.program_cd,
650 p_spat_rec.program_version,
651 p_spat_rec.acad_cal_type,
652 l_term_cal_type,
653 l_term_sequence_number,
654 p_spat_rec.key_program_flag,
655 p_spat_rec.location_cd,
656 p_spat_rec.attendance_mode,
657 p_spat_rec.attendance_type,
658 p_spat_rec.fee_cat,
659 l_coo_id,
660 l_class_standing_id,
661 NVL(FND_GLOBAL.USER_ID, -1), -- created_by
662 SYSDATE, -- creation_date
663 NVL(FND_GLOBAL.USER_ID,-1), -- last_updated_by
664 SYSDATE, -- last_update_date
665 NVL(FND_GLOBAL.LOGIN_ID, -1), -- last_update_login
666 l_request_id, -- request_id
667 l_program_application_id, -- program_application_id
668 l_program_id, -- program_id
669 l_program_update_date, -- program_update_date
670 p_spat_rec.attribute_category,
671 p_spat_rec.attribute1,
672 p_spat_rec.attribute2,
673 p_spat_rec.attribute3,
674 p_spat_rec.attribute4,
675 p_spat_rec.attribute5,
676 p_spat_rec.attribute6,
677 p_spat_rec.attribute7,
678 p_spat_rec.attribute8,
679 p_spat_rec.attribute9,
680 p_spat_rec.attribute10,
681 p_spat_rec.attribute11,
682 p_spat_rec.attribute12,
683 p_spat_rec.attribute13,
684 p_spat_rec.attribute14,
685 p_spat_rec.attribute15,
686 p_spat_rec.attribute16,
687 p_spat_rec.attribute17,
688 p_spat_rec.attribute18,
689 p_spat_rec.attribute19,
690 p_spat_rec.attribute20,
691 'NONE');
692
693 -- forward and backward gap filling
694 validate_post_spat(
695 p_person_id => l_person_id,
696 p_term_cal_type => l_term_cal_type,
697 p_term_sequence_number => l_term_sequence_number,
698 p_class_standing_id => l_class_standing_id,
699 p_coo_id => l_coo_id,
700 p_spat_rec => p_spat_rec);
701
702 -- setting the return value to success
703 x_return_status := FND_API.G_RET_STS_SUCCESS;
704 ELSE
705 ROLLBACK TO create_spa_t_svpt;
706 END IF;
707
708 -- if no validation failed and asked to commit through p_commit
709 IF NOT l_validation_failed AND FND_API.TO_BOOLEAN(p_commit) THEN
710 COMMIT;
711 END IF;
712
713 FND_MSG_PUB.COUNT_AND_GET (
714 p_count => x_msg_count,
715 p_data => x_msg_data);
716
717 RETURN;
718
719 EXCEPTION
720
721 WHEN FND_API.G_EXC_ERROR THEN
722 ROLLBACK TO create_spa_t_svpt;
723 x_return_status := FND_API.G_RET_STS_ERROR;
724 FND_MSG_PUB.COUNT_AND_GET (
725 p_count => x_msg_count,
726 p_data => x_msg_data);
727
728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
729 ROLLBACK TO create_spa_t_svpt;
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731 FND_MSG_PUB.COUNT_AND_GET (
732 p_count => x_msg_count,
733 p_data => x_msg_data);
734
735 WHEN OTHERS THEN
736 ROLLBACK TO create_spa_t_svpt;
737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
738 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
739 FND_MSG_PUB.ADD_EXC_MSG(
740 g_pkg_name,
741 l_api_name);
742 END IF;
743 FND_MSG_PUB.COUNT_AND_GET (
744 p_count => x_msg_count,
745 p_data => x_msg_data);
746
747 END create_spa_t;
748
749 END igs_en_spat_lgcy_pub;