1 PACKAGE BODY igs_en_susa_lgcy_pub AS
2 /* $Header: IGSENA3B.pls 120.2 2005/10/28 04:18:18 appldev ship $ */
3
4
5
6 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_EN_SUSA_LGCY_PUB';
7
8
9 FUNCTION validate_parameters(p_susa_rec IN susa_rec_type)
10 RETURN BOOLEAN AS
11 /*----------------------------------------------------------------------------
12 || Created By : prraj
13 || Created On : 11-Nov-2002
14 || Purpose : To validate the input parameters
15 || Known limitations, enhancements or remarks :
16 || Change History :
17 || Who When What
18 ------------------------------------------------------------------------------*/
19
20 l_desc_flex_name CONSTANT VARCHAR2(30) := 'IGS_AS_SU_SETATMPT_FLEX';
21 l_valid_params BOOLEAN := TRUE;
22 l_msg_count NUMBER;
23 BEGIN
24
25 IF p_susa_rec.person_number IS NULL THEN
26 -- Add excep to stack
27 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PER_NUM_NULL');
28 FND_MSG_PUB.ADD;
29 l_valid_params := FALSE;
30 END IF;
31
32 -- Program code
33 IF p_susa_rec.program_cd IS NULL THEN
34 -- Add excep to stack
35 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_NULL');
36 FND_MSG_PUB.ADD;
37 l_valid_params := FALSE;
38 ELSE
39 BEGIN
40 igs_as_su_setatmpt_pkg.check_constraints (column_name => 'COURSE_CD',
41 column_value => p_susa_rec.program_cd);
42 EXCEPTION
43 WHEN OTHERS THEN
44 -- Pop and set excep
45 l_msg_count := FND_MSG_PUB.COUNT_MSG;
46 FND_MSG_PUB.DELETE_MSG (l_msg_count);
47 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_UCASE');
48 FND_MSG_PUB.ADD;
49 l_valid_params := FALSE;
50 END;
51 END IF;
52
53 -- Unit Set code
54 IF p_susa_rec.unit_set_cd IS NULL THEN
55 -- Add excep to stack
56 FND_MESSAGE.SET_NAME('IGS','IGS_EN_UNIT_SET_CD_NULL');
57 FND_MSG_PUB.ADD;
58 l_valid_params := FALSE;
59 ELSE
60 BEGIN
61 igs_as_su_setatmpt_pkg.check_constraints (column_name => 'UNIT_SET_CD',
62 column_value => p_susa_rec.unit_set_cd);
63 EXCEPTION
64 WHEN OTHERS THEN
65 -- Pop and set excep
66 l_msg_count := FND_MSG_PUB.COUNT_MSG;
67 FND_MSG_PUB.DELETE_MSG (l_msg_count);
68 FND_MESSAGE.SET_NAME('IGS','IGS_EN_UNIT_SET_CD_UCASE');
69 FND_MSG_PUB.ADD;
70 l_valid_params := FALSE;
71 END;
72 END IF;
73
74 -- Unit Set version number
75 IF p_susa_rec.us_version_number IS NULL THEN
76 -- Add excep to stack
77 FND_MESSAGE.SET_NAME('IGS','IGS_EN_US_VER_NUM_NULL');
78 FND_MSG_PUB.ADD;
79 l_valid_params := FALSE;
80 END IF;
81
82 -- Student confirmed ind
83 IF p_susa_rec.student_confirmed_ind IS NOT NULL THEN
84 BEGIN
85 igs_as_su_setatmpt_pkg.check_constraints (column_name => 'STUDENT_CONFIRMED_IND',
86 column_value => p_susa_rec.student_confirmed_ind);
87 EXCEPTION
88 WHEN OTHERS THEN
89 -- Pop and set excep
90 l_msg_count := FND_MSG_PUB.COUNT_MSG;
91 FND_MSG_PUB.DELETE_MSG (l_msg_count);
92 FND_MESSAGE.SET_NAME('IGS','IGS_EN_STU_CF_IND_INVALID');
93 FND_MSG_PUB.ADD;
94 l_valid_params := FALSE;
95 END;
96 END IF;
97
98
99 -- Parent Unit set code
100 IF p_susa_rec.parent_unit_set_cd IS NOT NULL THEN
101 BEGIN
102 igs_as_su_setatmpt_pkg.check_constraints (column_name => 'PARENT_UNIT_SET_CD',
103 column_value => p_susa_rec.parent_unit_set_cd);
104 EXCEPTION
105 WHEN OTHERS THEN
106 -- Pop and set excep
107 l_msg_count := FND_MSG_PUB.COUNT_MSG;
108 FND_MSG_PUB.DELETE_MSG (l_msg_count);
109 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PARNT_US_CD_UCASE');
110 FND_MSG_PUB.ADD;
111 l_valid_params := FALSE;
112 END;
113 END IF;
114
115
116 -- Primary set ind
117 IF p_susa_rec.primary_set_ind IS NOT NULL THEN
118 BEGIN
119 igs_as_su_setatmpt_pkg.check_constraints (column_name => 'PRIMARY_SET_IND',
120 column_value => p_susa_rec.primary_set_ind);
121 EXCEPTION
122 WHEN OTHERS THEN
123 -- Pop and set excep
124 l_msg_count := FND_MSG_PUB.COUNT_MSG;
125 FND_MSG_PUB.DELETE_MSG (l_msg_count);
126 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRIM_IND_INVALID');
127 FND_MSG_PUB.ADD;
128 l_valid_params := FALSE;
129 END;
130 END IF;
131
132
133 -- Voluntary end ind
134 IF p_susa_rec.voluntary_end_ind IS NOT NULL THEN
135 BEGIN
136 igs_as_su_setatmpt_pkg.check_constraints (column_name => 'VOLUNTARY_END_IND',
137 column_value => p_susa_rec.voluntary_end_ind);
138 EXCEPTION
139 WHEN OTHERS THEN
140 -- Pop and set excep
141 l_msg_count := FND_MSG_PUB.COUNT_MSG;
142 FND_MSG_PUB.DELETE_MSG (l_msg_count);
143 FND_MESSAGE.SET_NAME('IGS','IGS_EN_VL_END_IND_INVALID');
144 FND_MSG_PUB.ADD;
145 l_valid_params := FALSE;
146 END;
147 END IF;
148
149
150 -- Override title
151 IF p_susa_rec.override_title IS NOT NULL THEN
152 BEGIN
153 igs_as_su_setatmpt_pkg.check_constraints (column_name => 'OVERRIDE_TITLE',
154 column_value => p_susa_rec.override_title);
155 EXCEPTION
156 WHEN OTHERS THEN
157 -- Pop and set excep
158 l_msg_count := FND_MSG_PUB.COUNT_MSG;
159 FND_MSG_PUB.DELETE_MSG (l_msg_count);
160 FND_MESSAGE.SET_NAME('IGS','IGS_EN_OVRIDE_TITLE_UCASE');
161 FND_MSG_PUB.ADD;
162 l_valid_params := FALSE;
163 END;
164 END IF;
165
166
167 -- Requirements complete ind
168 IF p_susa_rec.rqrmnts_complete_ind IS NOT NULL THEN
169 BEGIN
170 igs_as_su_setatmpt_pkg.check_constraints (column_name => 'RQRMNTS_COMPLETE_IND',
171 column_value => p_susa_rec.rqrmnts_complete_ind);
172 EXCEPTION
173 WHEN OTHERS THEN
174 -- Pop and set excep
175 l_msg_count := FND_MSG_PUB.COUNT_MSG;
176 FND_MSG_PUB.DELETE_MSG (l_msg_count);
177 FND_MESSAGE.SET_NAME('IGS','IGS_EN_RQRMT_COMP_INVALID');
178 FND_MSG_PUB.ADD;
179 l_valid_params := FALSE;
180 END;
181 END IF;
182
183
184 -- S Completed source type
185 IF p_susa_rec.s_completed_source_type IS NOT NULL THEN
186 BEGIN
187 igs_as_su_setatmpt_pkg.check_constraints (column_name => 'S_COMPLETED_SOURCE_TYPE',
188 column_value => p_susa_rec.s_completed_source_type);
189 EXCEPTION
190 WHEN OTHERS THEN
191 -- Pop and set excep
192 l_msg_count := FND_MSG_PUB.COUNT_MSG;
193 FND_MSG_PUB.DELETE_MSG (l_msg_count);
194 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SCP_SRCTYP_INVALID');
195 FND_MSG_PUB.ADD;
196 l_valid_params := FALSE;
197 END;
198 END IF;
199
200
201 -- Validate DFF columns
202 --
203 -- If any of the Descriptive Flex field columns have value , validate them .
204 IF (p_susa_rec.attribute_category IS NOT NULL OR p_susa_rec.attribute1 IS NOT NULL OR p_susa_rec.attribute2 IS NOT NULL OR
205 p_susa_rec.attribute3 IS NOT NULL OR p_susa_rec.attribute4 IS NOT NULL OR p_susa_rec.attribute5 IS NOT NULL OR
206 p_susa_rec.attribute6 IS NOT NULL OR p_susa_rec.attribute7 IS NOT NULL OR p_susa_rec.attribute8 IS NOT NULL OR
207 p_susa_rec.attribute9 IS NOT NULL OR p_susa_rec.attribute10 IS NOT NULL OR p_susa_rec.attribute11 IS NOT NULL OR
208 p_susa_rec.attribute12 IS NOT NULL OR p_susa_rec.attribute13 IS NOT NULL OR p_susa_rec.attribute14 IS NOT NULL OR
209 p_susa_rec.attribute15 IS NOT NULL OR p_susa_rec.attribute16 IS NOT NULL OR p_susa_rec.attribute17 IS NOT NULL OR
210 p_susa_rec.attribute18 IS NOT NULL OR p_susa_rec.attribute19 IS NOT NULL OR p_susa_rec.attribute20 IS NOT NULL ) THEN
211
212 IF NOT igs_ad_imp_018.validate_desc_flex (p_attribute_category => p_susa_rec.attribute_category,
213 p_attribute1 => p_susa_rec.attribute1,
214 p_attribute2 => p_susa_rec.attribute2,
215 p_attribute3 => p_susa_rec.attribute3,
216 p_attribute4 => p_susa_rec.attribute4,
217 p_attribute5 => p_susa_rec.attribute5,
218 p_attribute6 => p_susa_rec.attribute6,
219 p_attribute7 => p_susa_rec.attribute7,
220 p_attribute8 => p_susa_rec.attribute8,
221 p_attribute9 => p_susa_rec.attribute9,
222 p_attribute10 => p_susa_rec.attribute10,
223 p_attribute11 => p_susa_rec.attribute11,
224 p_attribute12 => p_susa_rec.attribute12,
225 p_attribute13 => p_susa_rec.attribute13,
226 p_attribute14 => p_susa_rec.attribute14,
227 p_attribute15 => p_susa_rec.attribute15,
228 p_attribute16 => p_susa_rec.attribute16,
229 p_attribute17 => p_susa_rec.attribute17,
230 p_attribute18 => p_susa_rec.attribute18,
231 p_attribute19 => p_susa_rec.attribute19,
232 p_attribute20 => p_susa_rec.attribute20,
233 p_desc_flex_name => l_desc_flex_name ) THEN
234 -- Add excep to stack
235 FND_MESSAGE.SET_NAME('IGS','IGS_AD_INVALID_DESC_FLEX');
236 FND_MSG_PUB.ADD;
237 l_valid_params := FALSE;
238 END IF;
239 END IF;
240
241
242 RETURN l_valid_params;
243
244 END validate_parameters;
245
246
247
248 FUNCTION validate_db_cons(p_person_id IN igs_as_su_setatmpt.person_id%TYPE,
249 p_parent_seq_number IN igs_as_su_setatmpt.parent_sequence_number%TYPE,
250 p_susa_rec IN susa_rec_type
251 ) RETURN VARCHAR2 AS
252 /*----------------------------------------------------------------------------
253 || Created By : prraj
254 || Created On : 11-Nov-2002
255 || Purpose : Validates the database constaints ie PK, UK and FK checks
256 || Known limitations, enhancements or remarks :
257 || Change History :
258 || Who When What
259 ------------------------------------------------------------------------------*/
260
261 l_ret_value VARCHAR2(1) := 'S';
262 BEGIN
263
264 -- Check for duplicate student unit set attempt
265 IF igs_en_gen_legacy.check_dup_susa (p_person_id => p_person_id,
266 p_program_cd => p_susa_rec.program_cd,
267 p_unit_set_cd => p_susa_rec.unit_set_cd,
268 p_us_version_number => p_susa_rec.us_version_number,
269 p_selection_dt => p_susa_rec.selection_dt) THEN
270 -- Add excep to stack
271 FND_MESSAGE.SET_NAME('IGS','IGS_EN_STU_USA_EXIST');
272 FND_MSG_PUB.ADD;
273 RETURN 'W';
274 END IF;
275
276
277 -- Program Attempt existence
278 IF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation(x_person_id => p_person_id,
279 x_course_cd => p_susa_rec.program_cd
280 ) THEN
281 -- Add excep to stack
282 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_ATT_NOT_EXIST');
283 FND_MSG_PUB.ADD;
284 l_ret_value := 'E';
285 END IF;
286
287
288 -- Parent Unit Set Attempt existence
289 IF p_susa_rec.parent_unit_set_cd IS NOT NULL THEN
290 IF NOT igs_as_su_setatmpt_pkg.get_pk_for_validation (x_person_id => p_person_id,
291 x_course_cd => p_susa_rec.program_cd,
292 x_unit_set_cd => p_susa_rec.parent_unit_set_cd,
293 x_sequence_number => p_parent_seq_number
294 ) THEN
295 -- Add excep to stack
296 FND_MESSAGE.SET_NAME('IGS','IGS_EN_NO_PAR_UNIT_SET_CD');
297 FND_MSG_PUB.ADD;
298 l_ret_value := 'E';
299 END IF;
300 END IF;
301
302
303 -- Unit Set existence
304 IF NOT igs_en_unit_set_pkg.get_pk_for_validation (x_unit_set_cd => p_susa_rec.unit_set_cd,
305 x_version_number => p_susa_rec.us_version_number
306 ) THEN
307 -- Add excep to stack
308 FND_MESSAGE.SET_NAME('IGS','IGS_EN_UNIT_SET_NOT_EXIST');
309 FND_MSG_PUB.ADD;
310 l_ret_value := 'E';
311 END IF;
312
313 RETURN l_ret_value;
314
315 END validate_db_cons;
316
317
318
319 FUNCTION validate_unit_set_atmpt (p_person_id IN igs_as_su_setatmpt.person_id%TYPE,
320 p_sequence_number IN igs_as_su_setatmpt.sequence_number%TYPE,
321 p_parent_seq_number IN igs_as_su_setatmpt.parent_sequence_number%TYPE,
322 p_auth_person_id IN igs_as_su_setatmpt.authorised_person_id%TYPE,
323 p_susa_rec IN susa_rec_type
324 ) RETURN BOOLEAN AS
325 /*----------------------------------------------------------------------------
326 || Created By : prraj
327 || Created On : 05-11-2002
328 || Purpose : Perform business validations for the EN Student Unit Set Attempt
329 || Known limitations, enhancements or remarks :
330 || Change History :
331 || Who When What
332 || bdeviset 29-JUL-2004 Added parameters p_end_dt,p_sequence_number
333 || for call to Function igs_en_gen_legacy.check_usa_overlap
334 || as it is modified for bug 3149133
335 ------------------------------------------------------------------------------*/
336
337 l_validation_success BOOLEAN := TRUE;
338 l_ret_val BOOLEAN;
339 l_message_name VARCHAR2(2000) := NULL;
340 l_legacy CONSTANT VARCHAR2(1) := 'Y';
341 BEGIN
342
343 -- 1. Check whether Unit set is offered within the students program offering option
344 -- 2. Check whether a unit set attempt is being created against a unit set, which has
345 -- already been completed by the student in the same program.
346 l_ret_val := igs_en_val_susa.enrp_val_susa_ins (p_person_id => p_person_id,
347 p_course_cd => p_susa_rec.program_cd,
348 p_unit_set_cd => p_susa_rec.unit_set_cd,
349 p_sequence_number => p_sequence_number,
350 p_us_version_number => p_susa_rec.us_version_number,
351 p_message_name => l_message_name,
352 p_legacy => l_legacy);
353 IF l_message_name IS NOT NULL THEN
354 l_validation_success := FALSE;
355 l_message_name := NULL;
356 END IF;
357
358
359 -- 1. Check if the authorized date is set, then the authorized person must also be set (and visa versa).
360 -- 2. The authorized date/person can only be set if the unit set is being ended, or if the
361 -- unit set version is flagged as requiring authorization to enroll.
362 l_ret_val := igs_en_val_susa.enrp_val_susa_auth (p_unit_set_cd => p_susa_rec.unit_set_cd,
363 p_us_version_number => p_susa_rec.us_version_number,
364 p_end_dt => p_susa_rec.end_dt,
365 p_authorised_person_id => p_auth_person_id,
366 p_authorised_on => p_susa_rec.authorised_on,
367 p_message_name => l_message_name,
368 p_legacy => l_legacy);
369
370 IF l_message_name IS NOT NULL THEN
371 l_validation_success := FALSE;
372 l_message_name := NULL;
373 END IF;
374
375
376 -- Check that when unit set requires authorisation then the authorised fields must be set
377 IF NOT igs_en_val_susa.enrp_val_susa_us_ath (p_unit_set_cd => p_susa_rec.unit_set_cd,
378 p_version_number => p_susa_rec.us_version_number,
379 p_authorised_person_id => p_auth_person_id,
380 p_authorised_on => p_susa_rec.authorised_on,
381 p_message_name => l_message_name) THEN
382
383 FND_MESSAGE.SET_NAME('IGS',l_message_name);
384 FND_MSG_PUB.ADD;
385 l_validation_success := FALSE;
386 l_message_name := NULL;
387 END IF;
388
389
390 -- 1. If requirements complete date is set then complete flag must also be set and vice-versa.
391 -- 2. The completion flag/date can only be set if the unit set attempt has been confirmed
392 l_ret_val := igs_en_val_susa.enrp_val_susa_cmplt (p_rqrmnts_complete_dt => p_susa_rec.rqrmnts_complete_dt,
393 p_rqrmnts_complete_ind => NVL(p_susa_rec.rqrmnts_complete_ind,'N'),
394 p_student_confirmed_ind => p_susa_rec.student_confirmed_ind,
395 p_message_name => l_message_name,
396 p_legacy => l_legacy);
397
398 IF l_message_name IS NOT NULL THEN
399 l_validation_success := FALSE;
400 l_message_name := NULL;
401 END IF;
402
403
404 -- Check that completed source type can only be set if completion date and indicator are set
405 IF NOT igs_en_val_susa.enrp_val_susa_scst (p_rqrmnts_complete_dt => p_susa_rec.rqrmnts_complete_dt,
406 p_rqrmnts_complete_ind => NVL(p_susa_rec.rqrmnts_complete_ind,'N'),
407 p_s_completed_source_type => p_susa_rec.s_completed_source_type,
408 p_message_name => l_message_name) THEN
409
410 FND_MESSAGE.SET_NAME('IGS',l_message_name);
411 FND_MSG_PUB.ADD;
412 l_validation_success := FALSE;
413 l_message_name := NULL;
414 END IF;
415
416
417 -- Check whether the selection date is set if the student confirmed indicator is set and visa versa
418 l_ret_val := igs_en_val_susa.enrp_val_susa_sci_sd (p_student_confirmed_ind => NVL(p_susa_rec.student_confirmed_ind,'N'),
419 p_selection_dt => p_susa_rec.selection_dt,
420 p_message_name => l_message_name,
421 p_legacy => l_legacy);
422
423 IF l_message_name IS NOT NULL THEN
424 l_validation_success := FALSE;
425 l_message_name := NULL;
426 END IF;
427
428
429 -- Check that voluntary end indicator can only be set when end date is set
430 IF NOT igs_en_val_susa.enrp_val_susa_end_vi (p_voluntary_end_ind => p_susa_rec.voluntary_end_ind,
431 p_end_dt => p_susa_rec.end_dt,
432 p_message_name => l_message_name) THEN
433
434 FND_MESSAGE.SET_NAME('IGS',l_message_name);
435 FND_MSG_PUB.ADD;
436 l_validation_success := FALSE;
437 l_message_name := NULL;
438 END IF;
439
440
441 IF p_susa_rec.parent_unit_set_cd IS NOT NULL THEN
442
443 -- 1. If the unit set is specified as a subordinate within the unit set relationships, then it must be a child attempt.
444 -- 2. If the attempt is a child attempt, then the parent set must be valid within the unit set relationships setup
445 l_ret_val := igs_en_val_susa.enrp_val_susa_cousr (p_person_id => p_person_id,
446 p_course_cd => p_susa_rec.program_cd,
447 p_unit_set_cd => p_susa_rec.unit_set_cd,
448 p_us_version_number => p_susa_rec.us_version_number,
449 p_parent_unit_set_cd => p_susa_rec.parent_unit_set_cd,
450 p_parent_sequence_number => p_parent_seq_number,
451 p_message_type => 'E',
452 p_message_name => l_message_name,
453 p_legacy => l_legacy);
454 IF l_message_name IS NOT NULL THEN
455 l_validation_success := FALSE;
456 l_message_name := NULL;
457 END IF;
458 END IF;
459
460
461 IF p_susa_rec.parent_unit_set_cd IS NOT NULL THEN
462
463 -- 1. If the unit set attempt is a direct parent of itself.
464 -- 2. Whether the unit set attempt is an indirect parent of itself.
465 -- 3. The Parent must be within the same program attempt, and must not be ended.
466 -- 4. Cannot have a confirmed parent if the attempt is not also confirmed
467 l_ret_val := igs_en_val_susa.enrp_val_susa_parent (p_person_id => p_person_id,
468 p_course_cd => p_susa_rec.program_cd,
469 p_unit_set_cd => p_susa_rec.unit_set_cd,
470 p_sequence_number => p_sequence_number,
471 p_parent_unit_set_cd => p_susa_rec.parent_unit_set_cd,
472 p_parent_sequence_number => p_parent_seq_number,
473 p_student_confirmed_ind => p_susa_rec.student_confirmed_ind,
474 p_message_name => l_message_name,
475 p_legacy => l_legacy);
476 IF l_message_name IS NOT NULL THEN
477 l_validation_success := FALSE;
478 l_message_name := NULL;
479 END IF;
480 END IF;
481
482
483 -- 1. If end date is being set and the unit set was part of the admissions offer, then authorisation fields must be set.
484 -- 2. Cannot have two active attempts of the same unit set within a single program attempt.
485 -- 3. The end date must be set if the parent unit set is ended
486 l_ret_val := igs_en_val_susa.enrp_val_susa_end_dt (p_person_id => p_person_id,
487 p_course_cd => p_susa_rec.program_cd,
488 p_unit_set_cd => p_susa_rec.unit_set_cd,
489 p_sequence_number => p_sequence_number,
490 p_us_version_number => p_susa_rec.us_version_number,
491 p_end_dt => p_susa_rec.end_dt,
492 p_authorised_person_id => p_auth_person_id,
493 p_authorised_on => p_susa_rec.authorised_on,
494 p_parent_unit_set_cd => p_susa_rec.parent_unit_set_cd,
495 p_parent_sequence_number => p_parent_seq_number,
496 p_message_type => 'E',
497 p_message_name => l_message_name,
498 p_legacy => l_legacy);
499
500 IF l_message_name IS NOT NULL THEN
501 l_validation_success := FALSE;
502 l_message_name := NULL;
503 END IF;
504
505
506 -- 1. The confirmed indicator cannot be unset if the end date is set
507 -- 2. The confirmed indicator cannot be unset if the completed date is set
508 -- 3. The confirmed indicator cannot be set if the program attempt status is unconfirmed
509 -- 4. The confirmed indicator cannot be set when parent is unconfirmed
510 l_ret_val := igs_en_val_susa.enrp_val_susa_sci (p_person_id => p_person_id,
511 p_course_cd => p_susa_rec.program_cd,
512 p_unit_set_cd => p_susa_rec.unit_set_cd,
513 p_sequence_number => p_sequence_number,
514 p_us_version_number => p_susa_rec.us_version_number,
515 p_parent_unit_set_cd => p_susa_rec.parent_unit_set_cd,
516 p_parent_sequence_number => p_parent_seq_number,
517 p_student_confirmed_ind => p_susa_rec.student_confirmed_ind,
518 p_selection_dt => p_susa_rec.selection_dt,
519 p_end_dt => p_susa_rec.end_dt,
520 p_rqrmnts_complete_ind => p_susa_rec.rqrmnts_complete_ind,
521 p_message_name => l_message_name,
522 p_legacy => l_legacy);
523
524
525 IF l_message_name IS NOT NULL THEN
526 l_validation_success := FALSE;
527 l_message_name := NULL;
528 END IF;
529
530
531 -- Check that an administrative unit cannot be set to be a primary one
532 l_ret_val := igs_en_val_susa.enrp_val_susa_prmry (p_person_id => p_person_id,
533 p_course_cd => p_susa_rec.program_cd,
534 p_unit_set_cd => p_susa_rec.unit_set_cd,
535 p_us_version_number => p_susa_rec.us_version_number,
536 p_primary_set_ind => p_susa_rec.primary_set_ind,
537 p_message_name => l_message_name,
538 p_legacy => l_legacy);
539
540 IF l_message_name IS NOT NULL THEN
541 l_validation_success := FALSE;
542 l_message_name := NULL;
543 END IF;
544
545 -- Check the condition that unit sets with category of 'pre-enrollment year' cannot be
546 -- inserted unless profile option is set
547 IF NOT igs_en_gen_legacy.check_pre_enroll_prof (p_unit_set_cd => p_susa_rec.unit_set_cd,
548 p_us_version_number => p_susa_rec.us_version_number) THEN
549
550 -- Add excep to stack
551 FND_MESSAGE.SET_NAME('IGS','IGS_EN_CANT_ADD_PRENRL_US');
552 FND_MSG_PUB.ADD;
553 l_validation_success := FALSE;
554 END IF;
555
556
557 IF p_susa_rec.selection_dt IS NOT NULL THEN
558
559 -- Check the condition that unit sets with category of 'pre-enrollment year' cannot
560 -- overlap selection/completion dates
561 l_message_name := NULL;
562 IF NOT igs_en_gen_legacy.check_usa_overlap (p_person_id => p_person_id,
563 p_program_cd => p_susa_rec.program_cd,
564 p_selection_dt => p_susa_rec.selection_dt,
565 p_rqrmnts_complete_dt => p_susa_rec.rqrmnts_complete_dt,
566 p_end_dt => p_susa_rec.end_dt,
567 p_sequence_number => p_sequence_number,
568 p_unit_set_cd => p_susa_rec.unit_set_cd,
569 p_us_version_number => p_susa_rec.us_version_number,
570 p_message_name => l_message_name) THEN
571 -- Add excep to stack
572 FND_MESSAGE.SET_NAME('IGS',l_message_name);
573 FND_MSG_PUB.ADD;
574 l_validation_success := FALSE;
575 END IF;
576 END IF;
577
578 RETURN l_validation_success;
579
580 END validate_unit_set_atmpt;
581
582
583
584 PROCEDURE create_unit_set_atmpt (p_api_version IN NUMBER,
585 p_init_msg_list IN VARCHAR2,
586 p_commit IN VARCHAR2,
587 p_validation_level IN NUMBER,
588 p_susa_rec IN susa_rec_type,
589 x_return_status OUT NOCOPY VARCHAR2,
590 x_msg_count OUT NOCOPY NUMBER,
591 x_msg_data OUT NOCOPY VARCHAR2) AS
592
593 /*----------------------------------------------------------------------------
594 || Created By : prraj
595 || Created On : 05-11-2002
596 || Purpose : To create a EN Student Unit Set Attempt
597 || Known limitations, enhancements or remarks :
598 || Change History :
599 || Who When What
600 ctyagi 16-March-2005 truncate the time component for the date field
601 for bug 4207943
602 ------------------------------------------------------------------------------*/
603
604 -- Cursor to fetch the sequence number
605 CURSOR c_susa_seq IS
606 SELECT
607 igs_as_su_setatmpt_seq_num_s.NEXTVAL
608 FROM dual;
609
610 l_api_name CONSTANT VARCHAR2(30) := 'create_unit_set_atmpt';
611 l_api_version CONSTANT NUMBER := 1.0;
612
613 l_insert_flag BOOLEAN := TRUE;
614 l_ret_val VARCHAR2(1) := NULL;
615
616 l_person_id igs_as_su_setatmpt.person_id%TYPE;
617 l_auth_person_id igs_as_su_setatmpt.authorised_person_id%TYPE;
618 l_parent_seq_number igs_as_su_setatmpt.parent_sequence_number%TYPE;
619 l_us_version_number igs_as_su_setatmpt.us_version_number%TYPE;
620 l_seqval igs_as_su_setatmpt.sequence_number%TYPE;
621 l_cal_type igs_as_su_setatmpt.catalog_cal_type%TYPE;
622 l_ci_sequence_number igs_as_su_setatmpt.catalog_seq_num%TYPE;
623 l_cal_start_dt igs_ca_inst.start_dt%TYPE;
624 l_cal_end_dt igs_ca_inst.end_dt%TYPE;
625 l_cal_return_status VARCHAR2(20);
626
627 l_creation_date igs_as_su_setatmpt.creation_date%TYPE;
628 l_last_update_date igs_as_su_setatmpt.last_update_date%TYPE;
629 l_created_by igs_as_su_setatmpt.created_by%TYPE;
630 l_last_updated_by igs_as_su_setatmpt.last_updated_by%TYPE;
631 l_last_update_login igs_as_su_setatmpt.last_update_login%TYPE;
632
633 l_request_id igs_as_su_setatmpt.request_id%TYPE;
634 l_program_appl_id igs_as_su_setatmpt.program_application_id%TYPE;
635 l_program_id igs_as_su_setatmpt.program_id%TYPE;
636 l_program_update_date igs_as_su_setatmpt.program_update_date%TYPE;
637
638 BEGIN
639
640 -- Create a savepoint
641 SAVEPOINT create_susa_pub;
642
643 -- Check for the Compatible API call
644 IF NOT FND_API.COMPATIBLE_API_CALL( l_api_version,
645 p_api_version,
646 l_api_name,
647 g_pkg_name) THEN
648
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END IF;
651
652 -- If the calling program has passed the parameter for initializing the message list
653 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
654 FND_MSG_PUB.INITIALIZE;
655 END IF;
656
657 -- Set the return status to success
658 x_return_status := FND_API.G_RET_STS_SUCCESS;
659
660
661
662 -- Validate input paramaters ---------
663
664 IF NOT validate_parameters(p_susa_rec => p_susa_rec) THEN
665 x_return_status := FND_API.G_RET_STS_ERROR;
666 l_insert_flag := FALSE;
667 END IF;
668
669
670 -- Derivations ----------------------------------
671
672 -- Person ID
673 IF l_insert_flag THEN
674 l_person_id := igs_ge_gen_003.get_person_id (p_person_number => p_susa_rec.person_number);
675
676 IF l_person_id IS NULL THEN
677 -- Add excep to stack
678 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_PERSON_NUMBER');
679 FND_MSG_PUB.ADD;
680 x_return_status := FND_API.G_RET_STS_ERROR;
681 l_insert_flag := FALSE;
682 END IF;
683 END IF;
684
685
686 -- Parent sequence number
687 IF l_insert_flag THEN
688 IF p_susa_rec.parent_unit_set_cd IS NOT NULL THEN
689 igs_ge_gen_003.get_susa_sequence_num (p_person_id => l_person_id,
690 p_program_cd => p_susa_rec.program_cd,
691 p_unit_set_cd => p_susa_rec.parent_unit_set_cd,
692 p_us_version_number => l_us_version_number,
693 p_sequence_number => l_parent_seq_number);
694
695 IF l_us_version_number IS NULL OR l_parent_seq_number IS NULL THEN
696 -- Add excep to stack
697 FND_MESSAGE.SET_NAME('IGS','IGS_EN_NO_PAR_UNIT_SET_CD');
698 FND_MSG_PUB.ADD;
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 l_insert_flag := FALSE;
701 END IF;
702 END IF;
703 END IF;
704
705
706 -- Catalog Cal type and sequence number
707 IF p_susa_rec.catalog_cal_alternate_code IS NOT NULL THEN
708 igs_ge_gen_003.get_calendar_instance (p_alternate_cd => p_susa_rec.catalog_cal_alternate_code,
709 p_s_cal_category => '''LOAD'',''ACADEMIC''',
710 p_cal_type => l_cal_type,
711 p_ci_sequence_number => l_ci_sequence_number,
712 p_start_dt => l_cal_start_dt,
713 p_end_dt => l_cal_end_dt,
714 p_return_status => l_cal_return_status);
715
716 IF l_cal_return_status = 'INVALID' THEN
717 -- Add excep to stack
718 FND_MESSAGE.SET_NAME('IGS','IGS_EN_NO_ACAD_TERM_CAL');
719 FND_MSG_PUB.ADD;
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 l_insert_flag := FALSE;
722 ELSIF l_cal_return_status = 'MULTIPLE' THEN
723 -- Add excep to stack
724 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MORE_CAL_FOUND');
725 FND_MSG_PUB.ADD;
726 x_return_status := FND_API.G_RET_STS_ERROR;
727 l_insert_flag := FALSE;
728 END IF;
729 END IF;
730
731
732 -- Authorisor person id
733 IF p_susa_rec.authorised_person_number IS NOT NULL THEN
734 l_auth_person_id := igs_ge_gen_003.get_person_id (p_person_number => p_susa_rec.authorised_person_number);
735
736 IF l_auth_person_id IS NULL THEN
737 -- Add excep to stack
738 FND_MESSAGE.SET_NAME('IGS','IGS_EN_AUTH_PERS_NOTEXIST');
739 FND_MSG_PUB.ADD;
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 l_insert_flag := FALSE;
742 END IF;
743 END IF;
744
745
746 -- Validate database constraints
747 IF l_insert_flag THEN
748 l_ret_val := validate_db_cons (p_person_id => l_person_id,
749 p_parent_seq_number => l_parent_seq_number,
750 p_susa_rec => p_susa_rec);
751
752 IF l_ret_val = 'E' THEN
753 x_return_status := FND_API.G_RET_STS_ERROR;
754 l_insert_flag := FALSE;
755 ELSIF l_ret_val = 'W' THEN
756 x_return_status := 'W';
757 l_insert_flag := FALSE;
758 END IF;
759 END IF;
760
761
762 -- Business validation
763 IF l_insert_flag THEN
764 -- Unit Set attempt is not yet created, hence passing zero
765 -- for sequence number
766 IF NOT validate_unit_set_atmpt (p_person_id => l_person_id,
767 p_sequence_number => 0,
768 p_parent_seq_number => l_parent_seq_number,
769 p_auth_person_id => l_auth_person_id,
770 p_susa_rec => p_susa_rec) THEN
771 x_return_status := FND_API.G_RET_STS_ERROR;
772 l_insert_flag := FALSE;
773 END IF;
774 END IF;
775
776
777 -- Perform direct insert on IGS_AS_SU_SETATMPT
778 IF l_insert_flag THEN
779
780 l_creation_date := SYSDATE;
781 l_created_by := FND_GLOBAL.USER_ID;
782
783 l_last_update_date := SYSDATE;
784 l_last_updated_by := FND_GLOBAL.USER_ID;
785 l_last_update_login :=FND_GLOBAL.LOGIN_ID;
786
787 IF l_created_by IS NULL THEN
788 l_created_by := -1;
789 END IF;
790
791 IF l_last_updated_by IS NULL THEN
792 l_last_updated_by := -1;
793 END IF;
794
795 IF l_last_update_login IS NULL THEN
796 l_last_update_login := -1;
797 END IF;
798
799 -- Concurrent manager columns
800 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
801 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
802 l_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
803
804 IF (l_request_id = -1) THEN
805 l_request_id := NULL;
806 l_program_id := NULL;
807 l_program_appl_id := NULL;
808 l_program_update_date := NULL;
809 ELSE
810 l_program_update_date := SYSDATE;
811 END IF;
812
813 -- Sequence number
814 OPEN c_susa_seq;
815 FETCH c_susa_seq INTO l_seqval;
816 CLOSE c_susa_seq;
817
818
819 INSERT INTO igs_as_su_setatmpt (
820 person_id,
821 course_cd,
822 unit_set_cd,
823 us_version_number,
824 sequence_number,
825 selection_dt,
826 student_confirmed_ind,
827 end_dt,
828 parent_unit_set_cd,
829 parent_sequence_number,
830 primary_set_ind,
831 voluntary_end_ind,
832 authorised_person_id,
833 authorised_on,
834 override_title,
835 rqrmnts_complete_ind,
836 rqrmnts_complete_dt,
837 s_completed_source_type,
838 created_by,
839 creation_date,
840 last_updated_by,
841 last_update_date,
842 last_update_login,
843 request_id,
844 program_application_id,
845 program_id,
846 program_update_date,
847 catalog_cal_type,
848 catalog_seq_num,
849 attribute_category,
850 attribute1,
851 attribute2,
852 attribute3,
853 attribute4,
854 attribute5,
855 attribute6,
856 attribute7,
857 attribute8,
858 attribute9,
859 attribute10,
860 attribute11,
861 attribute12,
862 attribute13,
863 attribute14,
864 attribute15,
865 attribute16,
866 attribute17,
867 attribute18,
868 attribute19,
869 attribute20)
870 VALUES (
871 l_person_id,
872 p_susa_rec.program_cd,
873 p_susa_rec.unit_set_cd,
874 p_susa_rec.us_version_number,
875 l_seqval,
876 trunc(p_susa_rec.selection_dt),
877 NVL(p_susa_rec.student_confirmed_ind,'N'),
878 trunc(p_susa_rec.end_dt),
879 p_susa_rec.parent_unit_set_cd,
880 l_parent_seq_number,
881 NVL(p_susa_rec.primary_set_ind,'N'),
882 NVL(p_susa_rec.voluntary_end_ind,'N'),
883 l_auth_person_id,
884 p_susa_rec.authorised_on,
885 p_susa_rec.override_title,
886 NVL(p_susa_rec.rqrmnts_complete_ind,'N'),
887 trunc(p_susa_rec.rqrmnts_complete_dt),
888 p_susa_rec.s_completed_source_type,
889 l_created_by,
890 l_creation_date,
891 l_last_updated_by,
892 l_last_update_date,
893 l_last_update_login,
894 l_request_id,
895 l_program_appl_id,
896 l_program_id,
897 l_program_update_date,
898 l_cal_type,
899 l_ci_sequence_number,
900 p_susa_rec.attribute_category,
901 p_susa_rec.attribute1,
902 p_susa_rec.attribute2,
903 p_susa_rec.attribute3,
904 p_susa_rec.attribute4,
905 p_susa_rec.attribute5,
906 p_susa_rec.attribute6,
907 p_susa_rec.attribute7,
908 p_susa_rec.attribute8,
909 p_susa_rec.attribute9,
910 p_susa_rec.attribute10,
911 p_susa_rec.attribute11,
912 p_susa_rec.attribute12,
913 p_susa_rec.attribute13,
914 p_susa_rec.attribute14,
915 p_susa_rec.attribute15,
916 p_susa_rec.attribute16,
917 p_susa_rec.attribute17,
918 p_susa_rec.attribute18,
919 p_susa_rec.attribute19,
920 p_susa_rec.attribute20);
921
922 ELSE
923 ROLLBACK TO create_susa_pub;
924 END IF;
925
926
927
928 -- If the calling program has passed the parameter for committing the data and there
929 -- have been no validation failures, then commit the work
930 IF ( (FND_API.TO_BOOLEAN(p_commit)) AND (l_insert_flag) ) THEN
931 COMMIT WORK;
932 END IF;
933
934
935 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
936 p_data => x_msg_data);
937
938
939 EXCEPTION
940 WHEN FND_API.G_EXC_ERROR THEN
941 ROLLBACK TO create_susa_pub;
942 x_return_status := FND_API.G_RET_STS_ERROR;
943 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
944 p_data => x_msg_data);
945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
946 ROLLBACK TO create_susa_pub;
947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
948 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
949 p_data => x_msg_data);
950 WHEN OTHERS THEN
951 ROLLBACK TO create_susa_pub;
952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
953 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
954 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,
955 l_api_name);
956 END IF;
957 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
958 p_data => x_msg_data);
959
960
961 END create_unit_set_atmpt;
962
963
964
965 END igs_en_susa_lgcy_pub;
[Home] [Help]