[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_TRNCMT_LGCY_PUB
Source
1 PACKAGE BODY igs_as_trncmt_lgcy_pub AS
2 /* $Header: IGSAS56B.pls 120.0 2005/07/05 12:56:21 appldev noship $ */
3 /******************************************************************************
4 || Created By : anilk
5 || Created On : 22-Sep-2002
6 || Purpose : This is an API to move legacy teranscript comments to OSS
7 ||
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || (reverse chronological order - newest change first)
12 ******************************************************************************/
13 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_AS_TRNCMT_LGCY_PUB';
14
15 -- This procedure puts NULL to all the non-required fields for a particular comment type
16 PROCEDURE nullify_inappropriate_fields(
17 p_lgcy_trncmt_rec IN OUT NOCOPY lgcy_trncmt_rec_type) IS
18 BEGIN
19 IF p_lgcy_trncmt_rec.comment_type_code = 'UNIT_ATTEMPT' THEN
20 p_lgcy_trncmt_rec.program_cd := NULL;
21 p_lgcy_trncmt_rec.program_type := NULL;
22 p_lgcy_trncmt_rec.award_cd := NULL;
23 p_lgcy_trncmt_rec.load_cal_alternate_cd := NULL;
24 p_lgcy_trncmt_rec.unit_set_cd := NULL;
25 p_lgcy_trncmt_rec.us_version_number := NULL;
26 return;
27 END IF;
28 p_lgcy_trncmt_rec.unit_cd := NULL;
29 p_lgcy_trncmt_rec.version_number := NULL;
30 p_lgcy_trncmt_rec.teach_cal_alternate_cd := NULL;
31 p_lgcy_trncmt_rec.location_cd := NULL;
32 p_lgcy_trncmt_rec.unit_class := NULL;
33
34 IF p_lgcy_trncmt_rec.comment_type_code = 'CAREER_HEADER' OR
35 p_lgcy_trncmt_rec.comment_type_code = 'CAREER_FOOTER' OR
36 p_lgcy_trncmt_rec.comment_type_code = 'CAREER_BASIS' THEN
37 p_lgcy_trncmt_rec.program_cd := NULL;
38 p_lgcy_trncmt_rec.award_cd := NULL;
39 p_lgcy_trncmt_rec.load_cal_alternate_cd := NULL;
40 p_lgcy_trncmt_rec.unit_set_cd := NULL;
41 p_lgcy_trncmt_rec.us_version_number := NULL;
42 ELSIF p_lgcy_trncmt_rec.comment_type_code = 'CAREER_TERM' THEN
43 p_lgcy_trncmt_rec.program_cd := NULL;
44 p_lgcy_trncmt_rec.award_cd := NULL;
45 p_lgcy_trncmt_rec.unit_set_cd := NULL;
46 p_lgcy_trncmt_rec.us_version_number := NULL;
47 ELSIF p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_HEADER' OR
48 p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_FOOTER' OR
49 p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_BASIS' THEN
50 p_lgcy_trncmt_rec.program_type := NULL;
51 p_lgcy_trncmt_rec.award_cd := NULL;
52 p_lgcy_trncmt_rec.load_cal_alternate_cd := NULL;
53 p_lgcy_trncmt_rec.unit_set_cd := NULL;
54 p_lgcy_trncmt_rec.us_version_number := NULL;
55 ELSIF p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_YEAR' THEN
56 p_lgcy_trncmt_rec.program_type := NULL;
57 p_lgcy_trncmt_rec.award_cd := NULL;
58 p_lgcy_trncmt_rec.load_cal_alternate_cd := NULL;
59 ELSIF p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_AWARD' THEN
60 -- p_lgcy_trncmt_rec.program_type := NULL;
61 p_lgcy_trncmt_rec.load_cal_alternate_cd := NULL;
62 p_lgcy_trncmt_rec.unit_set_cd := NULL;
63 p_lgcy_trncmt_rec.us_version_number := NULL;
64 END IF;
65 END nullify_inappropriate_fields;
66
67 FUNCTION validate_parameters (
68 p_lgcy_trncmt_rec IN OUT NOCOPY lgcy_trncmt_rec_type
69 ) RETURN BOOLEAN
70 /******************************************************************************
71 || Created By : anilk
72 || Created On : 22-Sep-2002
73 || Purpose : Valdiates if all the mandatory for this API has been passed
74 || If not, add the msgs to the stack and return false
75 ||
76 || Known limitations, enhancements or remarks :
77 || Change History :
78 || Who When What
79 || (reverse chronological order - newest change first)
80 ******************************************************************************/
81 IS
82
83 l_return_value BOOLEAN := FND_API.TO_BOOLEAN(FND_API.G_TRUE);
84 l_dummy VARCHAR2(30);
85
86 CURSOR cur_lookup_code(p_lookup_code igs_lookup_values.lookup_code%TYPE) IS
87 SELECT 'x'
88 FROM igs_lookup_values
89 WHERE lookup_type = 'IGS_AS_STDNT_TRNS_CMNT_TYPE'
90 AND lookup_code = p_lookup_code
91 AND NVL(enabled_flag, 'N') = 'Y';
92
93 BEGIN
94 --Convert all the values that must be uppercase into uppercase forcibly
95 p_lgcy_trncmt_rec.comment_type_code := UPPER(p_lgcy_trncmt_rec.comment_type_code);
96 p_lgcy_trncmt_rec.program_cd := UPPER(p_lgcy_trncmt_rec.program_cd);
97 p_lgcy_trncmt_rec.program_type := UPPER(p_lgcy_trncmt_rec.program_type);
98 p_lgcy_trncmt_rec.award_cd := UPPER(p_lgcy_trncmt_rec.award_cd);
99 p_lgcy_trncmt_rec.unit_set_cd := UPPER(p_lgcy_trncmt_rec.unit_set_cd);
100
101 -- nullify the values inappropriate for the current comment_type_code
102 nullify_inappropriate_fields(p_lgcy_trncmt_rec);
103
104 IF p_lgcy_trncmt_rec.comment_type_code IS NULL THEN
105 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
106 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_CMNT_TYP_NULL');
107 FND_MSG_PUB.ADD;
108 END IF;
109
110 IF p_lgcy_trncmt_rec.comment_txt IS NULL THEN
111 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
112 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_CMNT_NULL');
113 FND_MSG_PUB.ADD;
114 END IF;
115
116 IF p_lgcy_trncmt_rec.person_number IS NULL THEN
117 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
118 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PER_NUM_NULL');
119 FND_MSG_PUB.ADD;
120 END IF;
121
122 IF p_lgcy_trncmt_rec.comment_type_code IS NOT NULL THEN
123 OPEN cur_lookup_code(p_lgcy_trncmt_rec.comment_type_code);
124 FETCH cur_lookup_code INTO l_dummy;
125 IF cur_lookup_code%NOTFOUND THEN
126 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
127 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_CMNT_TYP_INVALID');
128 FND_MSG_PUB.ADD;
129 RETURN l_return_value ;
130 END IF;
131 CLOSE cur_lookup_code;
132
133 IF (p_lgcy_trncmt_rec.comment_type_code = 'CAREER_HEADER' OR
134 p_lgcy_trncmt_rec.comment_type_code = 'CAREER_FOOTER' OR
135 p_lgcy_trncmt_rec.comment_type_code = 'CAREER_BASIS' OR
136 p_lgcy_trncmt_rec.comment_type_code = 'CAREER_TERM' ) AND
137 p_lgcy_trncmt_rec.program_type IS NULL
138 THEN
139 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
140 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
141 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'program_type');
142 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
143 FND_MSG_PUB.ADD;
144 END IF;
145
146 IF (p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_HEADER' OR
147 p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_FOOTER' OR
148 p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_BASIS' OR
149 p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_YEAR' OR
150 p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_AWARD' ) AND
151 p_lgcy_trncmt_rec.program_cd IS NULL
152 THEN
153 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
154 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
155 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'program_cd');
156 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
157 FND_MSG_PUB.ADD;
158 END IF;
159
160 IF p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_AWARD' AND
161 p_lgcy_trncmt_rec.award_cd IS NULL
162 THEN
163 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
164 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
165 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'award_cd');
166 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
167 FND_MSG_PUB.ADD;
168 END IF;
169
170 IF p_lgcy_trncmt_rec.comment_type_code = 'CAREER_TERM' AND
171 p_lgcy_trncmt_rec.load_cal_alternate_cd IS NULL
172 THEN
173 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
174 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
175 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'load_cal_alternate_cd');
176 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
177 FND_MSG_PUB.ADD;
178 END IF;
179
180 IF p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_YEAR' THEN
181 IF p_lgcy_trncmt_rec.unit_set_cd IS NULL THEN
182 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
183 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
184 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'unit_set_cd');
185 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
186 FND_MSG_PUB.ADD;
187 END IF;
188 IF p_lgcy_trncmt_rec.us_version_number IS NULL THEN
189 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
190 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
191 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'us_version_number');
192 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
193 FND_MSG_PUB.ADD;
194 END IF;
195 END IF;
196
197 IF p_lgcy_trncmt_rec.comment_type_code = 'UNIT_ATTEMPT' THEN
198 IF p_lgcy_trncmt_rec.unit_cd IS NULL THEN
199 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
200 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
201 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'unit_cd');
202 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
203 FND_MSG_PUB.ADD;
204 END IF;
205 IF p_lgcy_trncmt_rec.version_number IS NULL THEN
206 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
207 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
208 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'version_number');
209 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
210 FND_MSG_PUB.ADD;
211 END IF;
212 IF p_lgcy_trncmt_rec.teach_cal_alternate_cd IS NULL THEN
213 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
214 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
215 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'teach_cal_alternate_cd');
216 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
217 FND_MSG_PUB.ADD;
218 END IF;
219 IF p_lgcy_trncmt_rec.location_cd IS NULL THEN
220 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
221 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
222 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'location_cd');
223 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
224 FND_MSG_PUB.ADD;
225 END IF;
226 IF p_lgcy_trncmt_rec.unit_class IS NULL THEN
227 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
228 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
229 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'unit_class');
230 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
231 FND_MSG_PUB.ADD;
232 END IF;
233 END IF;
234
235 END IF;
236
237 RETURN l_return_value ;
238
239 END validate_parameters;
240
241 -------------------------------------------------------------------------------
242 FUNCTION derive_trncmt_data(
243 p_lgcy_trncmt_rec IN OUT NOCOPY lgcy_trncmt_rec_type,
244 p_person_id OUT NOCOPY igs_pe_person.person_id%TYPE,
245 p_load_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
246 p_load_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
247 p_uoo_id OUT NOCOPY igs_ps_unit_ofr_opt_all.uoo_id%TYPE
248 )RETURN BOOLEAN
249 /****************************************************************************
250 || Created By : anilk
251 || Created On : 22-Sep-2003
252 || Purpose : Derives transcript comment data
253 || If error occurs, add the msgs to the stack and return false
254 || Called by create_trncmt
255 ||
256 || Known limitations, enhancements or remarks :
257 || Change History :
258 || Who When What
259 || (reverse chronological order - newest change first)
260 ****************************************************************************/
261 IS
262
263 l_return_value BOOLEAN := FND_API.TO_BOOLEAN(FND_API.G_TRUE);
264 l_message VARCHAR2(2000);
265 l_start_dt DATE ;
266 l_end_dt DATE ;
267 l_teach_cal_type igs_ca_inst.cal_type%TYPE;
268 l_teach_sequence_number igs_ca_inst.sequence_number%TYPE;
269
270 CURSOR cur_uoo_id(
271 p_unit_cd igs_ps_unit_ofr_opt_all.unit_cd%TYPE,
272 p_version_number igs_ps_unit_ofr_opt_all.version_number%TYPE,
273 p_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,
274 p_ci_sequence_number igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE,
275 p_location_cd igs_ps_unit_ofr_opt_all.location_cd%TYPE,
276 p_unit_class igs_ps_unit_ofr_opt_all.unit_class%TYPE
277 ) IS
278 SELECT uoo_id
279 FROM igs_ps_unit_ofr_opt_all
280 WHERE unit_cd = p_unit_cd
281 AND version_number = p_version_number
282 AND cal_type = p_cal_type
283 AND ci_sequence_number = p_ci_sequence_number
284 AND location_cd = p_location_cd
285 AND unit_class = p_unit_class;
286
287 BEGIN
288
289 --Get person id
290 p_person_id := igs_ge_gen_003.get_person_id(p_lgcy_trncmt_rec.person_number);
291 IF p_person_id IS NULL THEN
292 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
293 FND_MSG_PUB.ADD;
294 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
295 RETURN l_return_value;
296 END IF;
297
298 --Get load calendar info
299 IF p_lgcy_trncmt_rec.comment_type_code = 'CAREER_TERM' THEN
300 igs_ge_gen_003.get_calendar_instance(p_lgcy_trncmt_rec.load_cal_alternate_cd,
301 '''LOAD''',
302 p_load_cal_type,
303 p_load_sequence_number,
304 l_start_dt,
305 l_end_dt,
306 l_message) ;
307 IF p_load_cal_type IS NULL OR p_load_sequence_number IS NULL THEN
308 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
309 FND_MSG_PUB.ADD;
310 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
311 RETURN l_return_value;
312 END IF;
313 END IF;
314
315 --Get teaching calendar info
316 IF p_lgcy_trncmt_rec.comment_type_code = 'UNIT_ATTEMPT' THEN
317 igs_ge_gen_003.get_calendar_instance(p_lgcy_trncmt_rec.teach_cal_alternate_cd,
318 '''TEACHING''',
319 l_teach_cal_type,
320 l_teach_sequence_number,
321 l_start_dt,
322 l_end_dt,
323 l_message) ;
324 IF l_teach_cal_type IS NULL OR l_teach_sequence_number IS NULL THEN
325 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
326 FND_MSG_PUB.ADD;
327 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
328 RETURN l_return_value;
329 END IF;
330 END IF;
331
332 --Derive uoo_id if it is UNIT_ATTEMPT
333 IF p_lgcy_trncmt_rec.comment_type_code = 'UNIT_ATTEMPT' THEN
334 OPEN cur_uoo_id(
335 p_lgcy_trncmt_rec.unit_cd,
336 p_lgcy_trncmt_rec.version_number,
337 l_teach_cal_type,
338 l_teach_sequence_number,
339 p_lgcy_trncmt_rec.location_cd,
340 p_lgcy_trncmt_rec.unit_class);
341 FETCH cur_uoo_id INTO p_uoo_id;
342 IF cur_uoo_id%NOTFOUND THEN
343 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
344 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_NULL_CHK');
345 FND_MESSAGE.SET_TOKEN('INT_FIELD', 'uoo_id');
346 FND_MESSAGE.SET_TOKEN('CMNT_TYPE', p_lgcy_trncmt_rec.comment_type_code);
347 FND_MSG_PUB.ADD;
348 END IF;
349 CLOSE cur_uoo_id;
350 END IF;
351
352 RETURN l_return_value;
353
354 END derive_trncmt_data;
355
356 -------------------------------------------------------------------------------
357 FUNCTION validate_trncmt_db_cons(
358 p_person_id IN igs_pe_person.person_id%TYPE,
359 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
360 p_load_sequence_number IN igs_ca_inst.sequence_number%TYPE,
361 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
362 p_lgcy_trncmt_rec IN lgcy_trncmt_rec_type
363 ) RETURN VARCHAR2
364 /****************************************************************************
365 || Created By : anilk
366 || Created On : 22-Sep-2003
367 || Purpose : Validate db constraints
368 || If error occurs, add the msgs to the stack and return false
369 || Called by create_graduand
370 ||
371 || Known limitations, enhancements or remarks :
372 || Change History :
373 || Who When What
374 || (reverse chronological order - newest change first)
375 ****************************************************************************/
376 IS
377 CURSOR cur_check_course_type(
378 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
379 cp_course_type IGS_PS_VER_ALL.course_type%TYPE
380 )IS
381 SELECT 'X'
382 FROM IGS_EN_STDNT_PS_ATT spa, IGS_PS_VER_ALL pva
383 WHERE spa.course_cd = pva.course_cd
384 AND spa.version_number = pva.version_number
385 AND spa.person_id = cp_person_id
386 AND pva.course_type = cp_course_type;
387
388 CURSOR cur_check_career_term(
389 cp_person_id igs_pe_person.person_id%TYPE,
390 cp_course_type igs_ps_ver_all.course_type%TYPE,
391 cp_load_cal_type igs_ca_inst.cal_type%TYPE,
392 cp_load_ci_sequence_number igs_ca_inst.sequence_number%TYPE
393 ) IS
394 SELECT 'X'
395 FROM igs_pr_acad_load_v
396 WHERE person_id = cp_person_id
397 AND course_type = cp_course_type
398 AND load_cal_type = cp_load_cal_type
399 AND load_ci_sequence_number = cp_load_ci_sequence_number;
400
401 CURSOR cur_igs_as_su_setatmpt(
402 cp_person_id igs_pe_person.person_id%TYPE,
403 cp_course_cd igs_as_su_setatmpt.course_cd%TYPE,
404 cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE
405 ) IS
406 SELECT 'X'
407 FROM IGS_AS_SU_SETATMPT
408 WHERE person_id = cp_person_id
409 AND course_cd = cp_course_cd
410 AND unit_set_cd = cp_unit_set_cd;
411
412 CURSOR cur_igs_he_en_susa(
413 cp_person_id igs_pe_person.person_id%TYPE,
414 cp_course_cd igs_as_su_setatmpt.course_cd%TYPE,
415 cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE
416 ) IS
417 SELECT 'X'
418 FROM igs_he_en_susa
419 WHERE person_id = cp_person_id
420 AND course_cd = cp_course_cd
421 AND unit_set_cd = cp_unit_set_cd;
422
423 l_dummy VARCHAR2(30);
424 l_return_value VARCHAR2(10) := FND_API.G_RET_STS_SUCCESS;
425
426 BEGIN
427 -- Unique key validation
428 IF igs_as_stu_trn_cmts_pkg.get_uk_for_validation(
429 x_person_id => p_person_id,
430 x_comment_type_code => p_lgcy_trncmt_rec.comment_type_code,
431 x_course_cd => p_lgcy_trncmt_rec.program_cd,
432 x_course_type => p_lgcy_trncmt_rec.program_type,
433 x_award_cd => p_lgcy_trncmt_rec.award_cd,
434 x_load_cal_type => p_load_cal_type,
435 x_load_ci_sequence_number => p_load_sequence_number,
436 x_unit_set_cd => p_lgcy_trncmt_rec.unit_set_cd,
437 x_us_version_number => p_lgcy_trncmt_rec.us_version_number,
438 x_uoo_id => p_uoo_id
439 )
440 THEN
441 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_EXISTS');
442 FND_MSG_PUB.ADD;
443 l_return_value := 'E'; -- Error out
444 RETURN l_return_value;
445 END IF;
446
447 -- Foreign key validation with IGS_EN_STDNT_PS_ATT
448 IF p_lgcy_trncmt_rec.program_cd IS NOT NULL AND
449 NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation(
450 x_person_id => p_person_id ,
451 x_course_cd => p_lgcy_trncmt_rec.program_cd)
452 THEN
453 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_STPRATPT_FK_NOT_EXISTS');
454 FND_MSG_PUB.ADD;
455 l_return_value := 'E';
456 END IF;
457
458 -- Foreign key validation with IGS_HE_EN_SUSA
459 IF p_lgcy_trncmt_rec.unit_set_cd IS NOT NULL THEN
460 OPEN cur_igs_he_en_susa(p_person_id,
461 p_lgcy_trncmt_rec.program_cd,
462 p_lgcy_trncmt_rec.unit_set_cd);
463 FETCH cur_igs_he_en_susa into l_dummy;
464 IF cur_igs_he_en_susa%NOTFOUND THEN
465 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_STD_ENRL_NOT_EXISTS');
466 FND_MSG_PUB.ADD;
467 l_return_value := 'E';
468 END IF;
469 END IF;
470
471 --Foreign key validation with IGS_EN_SPA_AWD_AIM
472 IF p_lgcy_trncmt_rec.award_cd IS NOT NULL AND
473 NOT igs_en_spa_awd_aim_pkg.get_pk_for_validation(
474 x_person_id => p_person_id,
475 x_course_cd => p_lgcy_trncmt_rec.program_cd,
476 x_award_cd => p_lgcy_trncmt_rec.award_cd)
477 THEN
478 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_AWD_AWARD_FK');
479 FND_MSG_PUB.ADD;
480 l_return_value := 'E';
481 END IF;
482
483 -- unit_set_cd should be in range (0, 999)
484 IF p_lgcy_trncmt_rec.unit_set_cd IS NOT NULL THEN
485 BEGIN
486 igs_en_unit_set_pkg.check_constraints(
487 column_name => 'UNIT_SET_CD',
488 column_value => p_lgcy_trncmt_rec.unit_set_cd);
489 EXCEPTION
490 WHEN OTHERS THEN
491 FND_MSG_PUB.Delete_Msg (FND_MSG_PUB.COUNT_MSG);
492 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_UNTST_VERSION_BET_0_999');
493 FND_MSG_PUB.ADD;
494 l_return_value := 'E';
495 END;
496 END IF;
497
498 --Foreign key validation with IGS_PS_TYPE
499 IF p_lgcy_trncmt_rec.program_type IS NOT NULL AND
500 NOT igs_ps_type_pkg.get_pk_for_validation(x_course_type => p_lgcy_trncmt_rec.program_type)
501 THEN
502 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AV_BAS_CRS_TYP_FK_EXISTS');
503 FND_MSG_PUB.ADD;
504 l_return_value := 'E';
505 END IF;
506
507 --Foreign key validation with IGS_AS_SU_SETATMPT
508 IF p_lgcy_trncmt_rec.comment_type_code = 'PROGRAM_YEAR' THEN
509 OPEN cur_igs_as_su_setatmpt(p_person_id,
510 p_lgcy_trncmt_rec.program_cd,
511 p_lgcy_trncmt_rec.unit_set_cd);
512 FETCH cur_igs_as_su_setatmpt into l_dummy;
513 IF cur_igs_as_su_setatmpt%NOTFOUND THEN
514 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_TRNS_CMTS_US_CHK');
515 FND_MSG_PUB.ADD;
516 l_return_value := 'E';
517 END IF;
518 END IF;
519
520 --Foreign key validation with IGS_EN_SU_ATTEMPT
521 IF p_lgcy_trncmt_rec.comment_type_code = 'CAREER_TERM' THEN
522 OPEN cur_check_career_term(
523 p_person_id,
524 p_lgcy_trncmt_rec.program_type,
525 p_load_cal_type,
526 p_load_sequence_number);
527 FETCH cur_check_career_term into l_dummy;
528 IF cur_check_career_term%NOTFOUND THEN
529 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AS_STD_ENRL_NOT_EXISTS');
530 FND_MSG_PUB.ADD;
531 l_return_value := 'E';
532 END IF;
533 CLOSE cur_check_career_term;
534 END IF;
535
536 -- Validation for COURSE_TYPE
537 IF substr(p_lgcy_trncmt_rec.comment_type_code, 0, 7) = 'CAREER_' THEN
538 OPEN cur_check_course_type(p_person_id, p_lgcy_trncmt_rec.program_type);
539 FETCH cur_check_course_type into l_dummy;
540 IF cur_check_course_type%NOTFOUND THEN
541 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AV_BAS_CRS_TYP_FK_EXISTS');
542 FND_MSG_PUB.ADD;
543 l_return_value := 'E';
544 END IF;
545 CLOSE cur_check_course_type;
546 END IF;
547
548 RETURN l_return_value;
549
550 END validate_trncmt_db_cons;
551
552 -------------------------------------------------------------------------------
553 PROCEDURE create_trncmt(
554 p_api_version IN NUMBER,
555 p_init_msg_list IN VARCHAR2 ,
556 p_commit IN VARCHAR2 ,
557 p_validation_level IN NUMBER ,
558 p_lgcy_trncmt_rec IN OUT NOCOPY LGCY_TRNCMT_REC_TYPE,
559 x_return_status OUT NOCOPY VARCHAR2,
560 x_msg_count OUT NOCOPY NUMBER,
561 x_msg_data OUT NOCOPY VARCHAR2
562 ) IS
563 /****************************************************************************
564 || Created By : anilk
565 || Created On : 22-Sep-2003
566 || Purpose : For legacy transcript comments API
567 ||
568 || This is called for importing transcript comments data into OSS tables
569 || Known limitations, enhancements or remarks :
570 || Change History :
571 || Who When What
572 || (reverse chronological order - newest change first)
573 ****************************************************************************/
574 l_api_name CONSTANT VARCHAR2(30) := 'create_trncmt';
575 l_api_version CONSTANT NUMBER := 1.0;
576
577 --Local params
578 l_comment_id igs_as_stu_trn_cmts.comment_id%TYPE;
579 l_load_cal_type igs_ca_inst.cal_type%TYPE;
580 l_load_sequence_number igs_ca_inst.sequence_number%TYPE;
581 l_person_id igs_pe_person.person_id%TYPE;
582 l_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
583
584 l_return_value VARCHAR2(1);
585 WARN_TYPE_ERR EXCEPTION;
586
587 BEGIN
588 --Standard start of API savepoint
589 SAVEPOINT create_trncmt;
590
591 --Standard call to check for call compatibility.
592 IF NOT FND_API.COMPATIBLE_API_CALL(
593 l_api_version,
594 p_api_version,
595 l_api_name,
596 G_PKG_NAME)
597 THEN
598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
599 END IF;
600
601 --Initialize message list if p_init_msg_list is set to TRUE.
602 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
603 FND_MSG_PUB.INITIALIZE;
604 END IF;
605
606 --Initialize API return status to success.
607 x_return_status := FND_API.G_RET_STS_SUCCESS;
608
609
610 --Validate the params passed to this API
611 IF NOT validate_parameters(p_lgcy_trncmt_rec) THEN
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 RAISE FND_API.G_EXC_ERROR;
614 END IF;
615
616 --Derive trancript comment data
617 IF NOT derive_trncmt_data(
618 p_lgcy_trncmt_rec => p_lgcy_trncmt_rec,
619 p_person_id => l_person_id,
620 p_load_cal_type => l_load_cal_type,
621 p_load_sequence_number => l_load_sequence_number,
622 p_uoo_id => l_uoo_id
623 ) THEN
624 x_return_status := FND_API.G_RET_STS_ERROR;
625 RAISE FND_API.G_EXC_ERROR;
626 END IF;
627
628 --Validate trancript comments for db constraints
629 l_return_value := validate_trncmt_db_cons (
630 p_person_id => l_person_id,
631 p_load_cal_type => l_load_cal_type,
632 p_load_sequence_number => l_load_sequence_number,
633 p_uoo_id => l_uoo_id,
634 p_lgcy_trncmt_rec => p_lgcy_trncmt_rec
635 );
636 IF l_return_value = 'E' THEN
637 x_return_status := FND_API.G_RET_STS_ERROR;
638 RAISE FND_API.G_EXC_ERROR;
639 ELSIF l_return_value = 'W' THEN
640 RAISE WARN_TYPE_ERR; --Error handling Goes here
641 END IF;
642
643 --Generate the sequence number for comment_id and do RAW insert into the table
644 SELECT igs_as_stu_trns_cmts_s.NEXTVAL
645 INTO l_comment_id
646 FROM dual
647 WHERE ROWNUM = 1;
648
649 INSERT INTO IGS_AS_STU_TRN_CMTS
650 (
651 COMMENT_ID ,
652 COMMENT_TYPE_CODE ,
653 COMMENT_TXT ,
654 PERSON_ID ,
655 COURSE_CD ,
656 COURSE_TYPE ,
657 AWARD_CD ,
658 LOAD_CAL_TYPE ,
659 LOAD_CI_SEQUENCE_NUMBER ,
660 UNIT_SET_CD ,
661 US_VERSION_NUMBER ,
662 UOO_ID ,
663 CREATED_BY ,
664 CREATION_DATE ,
665 LAST_UPDATED_BY ,
666 LAST_UPDATE_DATE ,
667 LAST_UPDATE_LOGIN
668 )
669 VALUES (
670 l_comment_id ,
671 UPPER(p_lgcy_trncmt_rec.comment_type_code) ,
672 p_lgcy_trncmt_rec.comment_txt ,
673 l_person_id ,
674 UPPER(p_lgcy_trncmt_rec.program_cd) ,
675 UPPER(p_lgcy_trncmt_rec.program_type) ,
676 UPPER(p_lgcy_trncmt_rec.award_cd) ,
677 UPPER(l_load_cal_type) ,
678 l_load_sequence_number ,
679 UPPER(p_lgcy_trncmt_rec.unit_set_cd) ,
680 p_lgcy_trncmt_rec.us_version_number ,
681 l_uoo_id ,
682 NVL(FND_GLOBAL.USER_ID, -1) ,
683 SYSDATE ,
684 NVL(FND_GLOBAL.USER_ID,-1) ,
685 SYSDATE ,
686 NVL(FND_GLOBAL.LOGIN_ID,-1)
687 );
688
689
690 --Standard check of p_commit.
691 IF FND_API.to_Boolean(p_commit) THEN
692 commit;
693 END IF;
694
695 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
696
697
698 EXCEPTION
699 WHEN WARN_TYPE_ERR THEN
700 ROLLBACK TO create_trncmt;
701 x_return_status := 'W';
702 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
703 WHEN FND_API.G_EXC_ERROR THEN
704 ROLLBACK TO create_trncmt;
705 x_return_status := FND_API.G_RET_STS_ERROR;
706 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
707
708 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
709 ROLLBACK TO create_trncmt;
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
712 WHEN OTHERS THEN
713 ROLLBACK TO create_trncmt;
714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
715 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
716 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
717 FND_MSG_PUB.ADD;
718 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
719
720 END create_trncmt ;
721
722
723 END igs_as_trncmt_lgcy_pub;