1 PACKAGE BODY IGS_GR_GRD_LGCY_PUB AS
2 /* $Header: IGSPGR1B.pls 115.8 2003/10/10 09:20:45 anilk noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_PR_GRD_LGCY_PUB';
5
6 /*===========================================================================+
7 | PROCEDURE |
8 | IGS_PR_GRD_LGCY_PUB |
9 | |
10 | DESCRIPTION |
11 | Creates advanced standing unit level |
12 | |
13 | SCOPE - PUBLIC |
14 | |
15 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
16 | |
17 | ARGUMENTS : IN: |
18 | p_api_version |
19 | p_init_msg_list |
20 | p_commit |
21 | p_validation_level |
22 | p_lgcy_grd_rec |
23 | |
24 | |
25 | OUT: |
26 | x_return_status |
27 | x_msg_count |
28 | x_msg_data |
29 | |
30 | IN/ OUT: |
31 | p_lgcy_grd_rec |
32 | RETURNS : NONE |
33 | |
34 | NOTES |
35 | |
36 | MODIFICATION HISTORY |
37 | Manu Srinivasan 11-Nov-02 Created |
38 | Kalyan Dande 03-Jan-03 Changed create_graduand message name to |
39 | IGS_AV_UNHANDLED_ERROR |
40 +===========================================================================*/
41
42
43 FUNCTION VALIDATE_PARAMETERS(p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type) RETURN BOOLEAN;
44
45 FUNCTION DERIVE_GRADUAND_DATA( p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type,
46 p_person_id OUT NOCOPY IGS_PE_PERSON.PERSON_ID%TYPE,
47 p_cal_type OUT NOCOPY IGS_CA_INST.CAL_TYPE%TYPE,
48 p_sequence_number OUT NOCOPY IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
49 p_proxy_award_person_id OUT NOCOPY IGS_PE_PERSON.PERSON_ID%TYPE,
50 p_proxy_award_ind OUT NOCOPY IGS_GR_GRADUAND_ALL.PROXY_AWARD_IND%TYPE
51 )
52 RETURN BOOLEAN;
53
54 FUNCTION VALIDATE_GRAD_DB_CONS ( p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
55 p_cal_type IN IGS_CA_INST.CAL_TYPE%TYPE,
56 p_sequence_number IN IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
57 p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type
58 )
59 RETURN VARCHAR2;
60
61
62
63 FUNCTION VALIDATE_GRADUANDS( p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type,
64 P_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
65 P_cal_type IN IGS_CA_INST.CAL_TYPE%TYPE,
66 P_sequence_number IN IGS_CA_INST. SEQUENCE_NUMBER%TYPE,
67 P_proxy_award_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
68 P_proxy_award_ind IN IGS_GR_GRADUAND_ALL. PROXY_AWARD_IND%TYPE
69
70 ) RETURN BOOLEAN;
71 FUNCTION VALIDATE_POST_GRADUAND(
72 p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type,
73 P_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
74 P_cal_type IN IGS_CA_INST.CAL_TYPE%TYPE,
75 P_sequence_number IN IGS_CA_INST. SEQUENCE_NUMBER%TYPE
76 )RETURN BOOLEAN;
77
78
79 PROCEDURE create_graduand(
80 p_api_version IN NUMBER,
81 p_init_msg_list IN VARCHAR2 ,
82 p_commit IN VARCHAR2 ,
83 p_validation_level IN NUMBER ,
84 p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_msg_data OUT NOCOPY VARCHAR2)
88 IS
89
90 /****************************************************************************************************************
91 || Created By : msrinivi
92 || Created On : 11-Nov-2002
93 || Purpose : For legacy graduand API
94 ||
95 || This process is called when graduation legacy data needs
96 || to be put into OSS tables
97 || Known limitations, enhancements or remarks :
98 || Change History :
99 || Who When What
100 || (reverse chronological order - newest change first)
101 ****************************************************************************************************************/
102
103 l_api_name CONSTANT VARCHAR2(30) := 'create_graduand';
104 l_api_version CONSTANT NUMBER := 1.0;
105
106 --Local params
107 l_person_id IGS_PE_PERSON.PERSON_ID%TYPE;
108 l_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
109 l_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
110 l_proxy_award_person_id IGS_PE_PERSON.PERSON_ID%TYPE;
111 l_proxy_award_ind IGS_GR_GRADUAND_ALL.PROXY_AWARD_IND%TYPE;
112
113 l_return_value VARCHAR2(1);
114
115 WARN_TYPE_ERR EXCEPTION;
116
117 BEGIN
118 --Standard start of API savepoint
119 SAVEPOINT create_graduand;
120
121 --Standard call to check for call compatibility.
122 IF NOT FND_API.Compatible_API_Call(
123 l_api_version,
124 p_api_version,
125 l_api_name,
126 G_PKG_NAME)
127 THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 END IF;
130
131 --Initialize message list if p_init_msg_list is set to TRUE.
132 IF FND_API.to_Boolean(p_init_msg_list) THEN
133 FND_MSG_PUB.initialize;
134 END IF;
135
136 --Initialize API return status to success.
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138
139 --THE CODE LOGIC STARTS HERE
140
141 --Validate the params passed to this API
142 IF NOT validate_parameters(p_lgcy_grd_rec) THEN
143 x_return_status := FND_API.G_RET_STS_ERROR;
144 RAISE FND_API.G_EXC_ERROR;
145 END IF;
146 --End of Validate the params passed to this API
147
148 --Derive Graduand data
149 IF NOT derive_graduand_data( p_lgcy_grd_rec => p_lgcy_grd_rec,
150 p_person_id => l_person_id,
151 p_cal_type => l_cal_type,
152 p_sequence_number => l_sequence_number,
153 p_proxy_award_person_id => l_proxy_award_person_id,
154 p_proxy_award_ind => l_proxy_award_ind
155 ) THEN
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159 --End of Derive Graduand data
160
161 --CALL VALIDATE_GRAD_DB_CONS
162 l_return_value := VALIDATE_GRAD_DB_CONS ( p_person_id => l_person_id,
163 p_lgcy_grd_rec => p_lgcy_grd_rec,
164 p_cal_type => l_cal_type,
165 p_sequence_number=>l_sequence_number
166 );
167 IF l_return_value = 'E' THEN
168 x_return_status := FND_API.G_RET_STS_ERROR;
169 RAISE FND_API.G_EXC_ERROR;
170 ELSIF l_return_value = 'W' THEN
171 RAISE WARN_TYPE_ERR; --Error handling Goes here
172 END IF;
173 --END OF VALIDATE_GRAD_DB_CONS
174
175 --Call to validate_graduand
176 IF NOT VALIDATE_GRADUANDS( p_lgcy_grd_rec => p_lgcy_grd_rec,
177 P_person_id => l_person_id,
178 P_cal_type => l_cal_type,
179 P_sequence_number => l_sequence_number,
180 P_proxy_award_person_id => l_proxy_award_person_id,
181 P_proxy_award_ind => l_proxy_award_ind ) THEN
182 RAISE FND_API.G_EXC_ERROR; --Error handling Goes here
183 END IF;
184 --End of Call to validate_graduand
185 --Call RAW insert into the table
186 INSERT INTO igs_gr_graduand
187 (
188 PERSON_ID,
189 CREATE_DT,
190 GRD_CAL_TYPE,
191 GRD_CI_SEQUENCE_NUMBER,
192 COURSE_CD,
193 AWARD_COURSE_CD,
194 AWARD_CRS_VERSION_NUMBER,
195 AWARD_CD,
196 GRADUAND_STATUS,
197 GRADUAND_APPR_STATUS,
198 S_GRADUAND_TYPE,
199 GRADUATION_NAME,
200 PROXY_AWARD_IND,
201 PROXY_AWARD_PERSON_ID,
202 PREVIOUS_QUALIFICATIONS,
203 CONVOCATION_MEMBERSHIP_IND,
204 SUR_FOR_COURSE_CD,
205 SUR_FOR_CRS_VERSION_NUMBER,
206 SUR_FOR_AWARD_CD,
207 COMMENTS,
208 CREATION_DATE,
209 CREATED_BY,
210 LAST_UPDATE_DATE,
211 LAST_UPDATED_BY,
212 LAST_UPDATE_LOGIN,
213 REQUEST_ID,
214 PROGRAM_ID,
215 PROGRAM_APPLICATION_ID,
216 PROGRAM_UPDATE_DATE,
217 ORG_ID,
218 ATTRIBUTE_CATEGORY,
219 ATTRIBUTE1,
220 ATTRIBUTE2,
221 ATTRIBUTE3,
222 ATTRIBUTE4,
223 ATTRIBUTE5,
224 ATTRIBUTE6,
225 ATTRIBUTE7,
226 ATTRIBUTE8,
227 ATTRIBUTE9,
228 ATTRIBUTE10,
229 ATTRIBUTE11,
230 ATTRIBUTE12,
231 ATTRIBUTE13,
232 ATTRIBUTE14,
233 ATTRIBUTE15,
234 ATTRIBUTE16,
235 ATTRIBUTE17,
236 ATTRIBUTE18,
237 ATTRIBUTE19,
238 ATTRIBUTE20
239 )
240 VALUES
241 (
242
243 L_PERSON_ID,
244 p_lgcy_grd_rec.CREATE_DT,
245 l_cal_type ,
246 l_sequence_number ,
247 UPPER(p_lgcy_grd_rec.program_cd),
248 UPPER(p_lgcy_grd_rec.award_program_cd),
249 p_lgcy_grd_rec.AWARD_prog_VERSION_NUMBER,
250 UPPER(p_lgcy_grd_rec.award_cd),
251 p_lgcy_grd_rec.GRADUAND_STATUS,
252 p_lgcy_grd_rec.GRADUAND_APPR_STATUS,
253 UPPER(p_lgcy_grd_rec.s_graduand_type),
254 p_lgcy_grd_rec.GRADUATION_NAME,
255 l_proxy_award_ind,
256 l_proxy_award_person_id,
257 p_lgcy_grd_rec.PREVIOUS_QUALIFICATIONS,
258 p_lgcy_grd_rec.CONVOCATION_MEMBERSHIP_IND,
259 UPPER(p_lgcy_grd_rec.sur_for_program_cd),
260 p_lgcy_grd_rec.SUR_FOR_prog_VERSION_NUMBER,
261 UPPER(p_lgcy_grd_rec.sur_for_award_cd),
262 p_lgcy_grd_rec.COMMENTS,
263 SYSDATE ,--p_lgcy_grd_rec.CREATION_DATE,
264 NVL(FND_GLOBAL.USER_ID,-1),--p_lgcy_grd_rec.CREATED_BY,
265 SYSDATE,--p_lgcy_grd_rec.LAST_UPDATE_DATE,
266 NVL(FND_GLOBAL.USER_ID,-1),--p_lgcy_grd_rec.LAST_UPDATED_BY,
267 NVL(FND_GLOBAL.LOGIN_ID,-1),--p_lgcy_grd_rec.LAST_UPDATE_LOGIN,
268 NULL,--p_lgcy_grd_rec.REQUEST_ID,
269 NULL,--p_lgcy_grd_rec.PROGRAM_ID,
270 NULL,--p_lgcy_grd_rec.PROGRAM_APPLICATION_ID,
271 NULL,--p_lgcy_grd_rec.PROGRAM_UPDATE_DATE,
272 NULL,--p_lgcy_grd_rec.ORG_ID,
273 p_lgcy_grd_rec.ATTRIBUTE_CATEGORY,
274 p_lgcy_grd_rec.ATTRIBUTE1,
275 p_lgcy_grd_rec.ATTRIBUTE2,
276 p_lgcy_grd_rec.ATTRIBUTE3,
277 p_lgcy_grd_rec.ATTRIBUTE4,
278 p_lgcy_grd_rec.ATTRIBUTE5,
279 p_lgcy_grd_rec.ATTRIBUTE6,
280 p_lgcy_grd_rec.ATTRIBUTE7,
281 p_lgcy_grd_rec.ATTRIBUTE8,
282 p_lgcy_grd_rec.ATTRIBUTE9,
283 p_lgcy_grd_rec.ATTRIBUTE10,
284 p_lgcy_grd_rec.ATTRIBUTE11,
285 p_lgcy_grd_rec.ATTRIBUTE12,
286 p_lgcy_grd_rec.ATTRIBUTE13,
287 p_lgcy_grd_rec.ATTRIBUTE14,
288 p_lgcy_grd_rec.ATTRIBUTE15,
289 p_lgcy_grd_rec.ATTRIBUTE16,
290 p_lgcy_grd_rec.ATTRIBUTE17,
291 p_lgcy_grd_rec.ATTRIBUTE18,
292 p_lgcy_grd_rec.ATTRIBUTE19,
293 p_lgcy_grd_rec.ATTRIBUTE20
294 );
295
296 --Insert done into table, so call VALIDATE_POST_GRADUAND
297 IF NOT VALIDATE_POST_GRADUAND(
298 p_lgcy_grd_rec => p_lgcy_grd_rec,
299 P_person_id => l_person_id,
300 P_cal_type => l_cal_type,
301 P_sequence_number => l_sequence_number
302 ) THEN
303 RAISE WARN_TYPE_ERR; --Error handling Goes here
304 END IF;
305 --End of call to VALIDATE_POST_GRADUAND
306
307 --THE CODE LOGIC ENDS HERE
308
309 --Standard check of p_commit.
310 IF FND_API.to_Boolean(p_commit) THEN
311 commit;
312 END IF;
313
314 FND_MSG_PUB.Count_And_Get(
315 p_count => x_MSG_COUNT,
316 p_data => X_MSG_DATA);
317
318
319 EXCEPTION
320 WHEN WARN_TYPE_ERR THEN
321 ROLLBACK TO create_graduand;
322 x_return_status := 'W';
323 FND_MSG_PUB.Count_And_Get(
324 p_count => x_msg_count,
325 p_data => x_msg_data);
326 WHEN FND_API.G_EXC_ERROR THEN
327 ROLLBACK TO create_graduand;
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 FND_MSG_PUB.Count_And_Get(
330 p_count => x_msg_count,
331 p_data => x_msg_data);
332
333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
334 ROLLBACK TO create_graduand;
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
336 FND_MSG_PUB.Count_And_Get(
337 p_count => x_msg_count,
338 p_data => x_msg_data);
339 WHEN OTHERS THEN
340 ROLLBACK TO create_graduand;
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
343 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
344 FND_MSG_PUB.ADD;
345
346 FND_MSG_PUB.Count_And_Get(
347 p_count => x_msg_count,
348 p_data => x_msg_data);
349
350 END create_graduand ;
351
352
353
354 FUNCTION validate_parameters
355 (
356 p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type
357 ) RETURN BOOLEAN
358
359 /****************************************************************************************************************
360 || Created By : msrinivi
361 || Created On : 11-Nov-2002
362 || Purpose : Valdiates if all the mandatory for this API has been passed
363 || If not, add the msgs to the stack and return false
364 || This process is called when graduation legacy data needs
365 || to be put into OSS tables
366 || Known limitations, enhancements or remarks :
367 || Change History :
368 || Who When What
369 || (reverse chronological order - newest change first)
370 ****************************************************************************************************************/
371 IS
372
373 l_return_value BOOLEAN := FND_API.TO_BOOLEAN(FND_API.G_TRUE);
374
375 BEGIN
376
377 --Convert all the values that must be uppercase into uppercase forcibly
378 p_lgcy_grd_rec.program_cd := UPPER(p_lgcy_grd_rec.program_cd);
379 p_lgcy_grd_rec.award_program_cd := UPPER(p_lgcy_grd_rec.award_program_cd);
380 p_lgcy_grd_rec.award_cd := UPPER(p_lgcy_grd_rec.award_cd);
381 p_lgcy_grd_rec.s_graduand_type := UPPER(p_lgcy_grd_rec.s_graduand_type);
382 p_lgcy_grd_rec.sur_for_program_cd := UPPER(p_lgcy_grd_rec.sur_for_program_cd);
383 p_lgcy_grd_rec.sur_for_award_cd := UPPER(p_lgcy_grd_rec.sur_for_award_cd);
384 p_lgcy_grd_rec.grd_cal_alt_code := UPPER(p_lgcy_grd_rec.grd_cal_alt_code);
385
386 IF p_lgcy_grd_rec.person_number IS NULL THEN
387 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
388 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PER_NUM_NULL');
389 FND_MSG_PUB.ADD;
390 END IF;
391
392 IF p_lgcy_grd_rec.create_dt IS NULL THEN
393 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
394 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_CREATE_DT_NOT_NULL');
395 FND_MSG_PUB.ADD;
396 END IF;
397
398 IF p_lgcy_grd_rec.GRD_CAL_ALT_CODE IS NULL THEN
399 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
400 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_CALALTCD_NOT_NULL');
401 FND_MSG_PUB.ADD;
402 END IF;
403
404
405 IF p_lgcy_grd_rec.AWARD_CD IS NULL THEN
406 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
407 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_AWARD_CD_NOT_NULL');
408 FND_MSG_PUB.ADD;
409 END IF;
410
411 IF p_lgcy_grd_rec.GRADUAND_STATUS IS NULL THEN
412 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
413 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_GRADSTAT_NOT_NULL');
414 FND_MSG_PUB.ADD;
415 END IF;
416
417 IF p_lgcy_grd_rec.GRADUAND_APPR_STATUS IS NULL THEN
418 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
419 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_GRD_APPRSTAT_NOT_NULL');
420 FND_MSG_PUB.ADD;
421 END IF;
422
423 --If the Award_program_code record type parameter is not NULL
424 --then Award_program_version_number should be >=1 and <= 999
425 IF p_lgcy_grd_rec.AWARD_PROGRAM_CD IS NOT NULL
426 AND
427 (
428 p_lgcy_grd_rec.AWARD_PROG_VERSION_NUMBER IS NULL OR
429 NVL(p_lgcy_grd_rec.AWARD_PROG_VERSION_NUMBER,-1) < 1 OR
430 NVL(p_lgcy_grd_rec.AWARD_PROG_VERSION_NUMBER,-1)> 999
431 )
432 THEN
433 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
434 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_AWD_PRGVER_BET_1_999');
435 FND_MSG_PUB.ADD;
436 END IF;
437
438 --If the record parameter SUR_FOR_PROGRAM_CD is Not Null
439 --then SUR_FOR_PROG_VERSION_NUMBER should have a value >= 1 and <= 999
440 IF p_lgcy_grd_rec.sur_for_program_cd IS NOT NULL
441 AND
442 (
443 p_lgcy_grd_rec.SUR_FOR_PROG_VERSION_NUMBER IS NULL OR
444 NVL(p_lgcy_grd_rec.SUR_FOR_PROG_VERSION_NUMBER ,-1) < 1 OR
445 NVL(p_lgcy_grd_rec.SUR_FOR_PROG_VERSION_NUMBER ,-1)> 999
446 )
447 THEN
448 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
449 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_SUR_PRGVER_BET_1_999');
450 FND_MSG_PUB.ADD;
451 END IF;
452
453 --If the record parameter SUR_FOR_PROGRAM_CD is Not Null
454 --then the record parameter SUR_FOR_AWARD_CD should have a not null value
455 --for SUR_FOR_AWARD CODE specified
456 IF p_lgcy_grd_rec.sur_for_program_cd IS NOT NULL
457 AND
458 (
459 p_lgcy_grd_rec.SUR_FOR_AWARD_CD IS NULL
460 )
461 THEN
462 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
463 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_SUR_AWDCD_NOT_NULL');
464 FND_MSG_PUB.ADD;
465 END IF;
466
467 --Validate the flex definition
468 IF NOT IGS_AD_IMP_018.validate_desc_flex(
469 p_ATTRIBUTE_CATEGORY => p_lgcy_grd_rec.ATTRIBUTE_CATEGORY,
470 p_attribute1 => p_lgcy_grd_rec.ATTRIBUTE1 ,
471 p_attribute2 => p_lgcy_grd_rec.ATTRIBUTE2 ,
472 p_attribute3 => p_lgcy_grd_rec.ATTRIBUTE3 ,
473 p_attribute4 => p_lgcy_grd_rec.ATTRIBUTE4 ,
474 p_attribute5 => p_lgcy_grd_rec.ATTRIBUTE5 ,
475 p_attribute6 => p_lgcy_grd_rec.ATTRIBUTE6 ,
476 p_attribute7 => p_lgcy_grd_rec.ATTRIBUTE7 ,
477 p_attribute8 => p_lgcy_grd_rec.ATTRIBUTE8 ,
478 p_attribute9 => p_lgcy_grd_rec.ATTRIBUTE9 ,
479 p_ATTRIBUTE10 => p_lgcy_grd_rec.ATTRIBUTE10 ,
480 p_ATTRIBUTE11 => p_lgcy_grd_rec.ATTRIBUTE11 ,
481 p_ATTRIBUTE12 => p_lgcy_grd_rec.ATTRIBUTE12 ,
482 p_ATTRIBUTE13 => p_lgcy_grd_rec.ATTRIBUTE13 ,
483 p_ATTRIBUTE14 => p_lgcy_grd_rec.ATTRIBUTE14 ,
484 p_ATTRIBUTE15 => p_lgcy_grd_rec.ATTRIBUTE15 ,
485 p_ATTRIBUTE16 => p_lgcy_grd_rec.ATTRIBUTE16 ,
486 p_ATTRIBUTE17 => p_lgcy_grd_rec.ATTRIBUTE17 ,
487 p_ATTRIBUTE18 => p_lgcy_grd_rec.ATTRIBUTE18 ,
488 p_ATTRIBUTE19 => p_lgcy_grd_rec.ATTRIBUTE19 ,
489 p_ATTRIBUTE20 => p_lgcy_grd_rec.ATTRIBUTE20 ,
490 p_desc_flex_name => 'IGS_GR_GRADUAND_FLEX' ) THEN
491 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
492 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_INVALID_DESC_FLEX');
493 FND_MSG_PUB.ADD;
494 END IF;
495
496 RETURN l_return_value ;
497
498 END validate_parameters;
499
500 FUNCTION derive_graduand_data( p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type,
501 p_person_id OUT NOCOPY IGS_PE_PERSON.PERSON_ID%TYPE,
502 p_cal_type OUT NOCOPY IGS_CA_INST.CAL_TYPE%TYPE,
503 p_sequence_number OUT NOCOPY IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
504 p_proxy_award_person_id OUT NOCOPY IGS_PE_PERSON.PERSON_ID%TYPE,
505 p_proxy_award_ind OUT NOCOPY IGS_GR_GRADUAND_ALL.PROXY_AWARD_IND%TYPE
506 )RETURN BOOLEAN
507
508 /****************************************************************************************************************
509 || Created By : msrinivi
510 || Created On : 11-Nov-2002
511 || Purpose : Derives graduand data
512 || If error occurs, add the msgs to the stack and return false
513 || Called by create_graduand
514 ||
515 || Known limitations, enhancements or remarks :
516 || Change History :
517 || Who When What
518 || (reverse chronological order - newest change first)
519 ****************************************************************************************************************/
520 IS
521
522 l_return_value BOOLEAN := FND_API.TO_BOOLEAN(FND_API.G_TRUE);
523 l_message VARCHAR2(2000);
524 l_start_dt DATE ;
525 l_end_dt DATE ;
526 BEGIN
527
528 p_person_id := igs_ge_gen_003.get_person_id(p_lgcy_grd_rec.person_number);
529
530 --Get person id
531 IF p_person_id IS NULL THEN
532 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
533 FND_MSG_PUB.ADD;
534 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
535 RETURN l_return_value;
536 END IF;
537 --End of Get person id
538
539 --Get calendar info
540 igs_ge_gen_003.get_calendar_instance(p_lgcy_grd_rec.GRD_CAL_ALT_CODE,NULL,p_cal_type, p_sequence_number, l_start_dt, l_end_dt, l_message) ;
541
542 IF p_cal_type IS NULL OR p_sequence_number IS NULL THEN
543 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
544 FND_MSG_PUB.ADD;
545 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
546 RETURN l_return_value;
547 END IF;
548 --End of Get calendar info
549
550 --Derive proxy award person number if corrs. person number is passed
551 IF p_lgcy_grd_rec.proxy_award_person_number IS NOT NULL THEN -- Proxy person number is passed, get corrs. person id
552 p_proxy_award_person_id := Igs_Ge_Gen_003.Get_Person_id (p_lgcy_grd_rec.proxy_award_person_number) ;
553
554 IF p_proxy_award_person_id IS NULL THEN -- Wrong proxy person number is passed
555 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_INVALID_PRXYPERS_NUM');
556 FND_MSG_PUB.ADD;
557 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
558 RETURN l_return_value;
559 ELSE
560 -- Correct proxy person number is passed, so set the ind to Y
561 p_proxy_award_ind := 'Y';
562 END IF;
563
564 ELSE
565 -- No proxy person number is passed, so set the ind to N
566 p_proxy_award_ind := 'N';
567 END IF;
568 --End of Derive proxy award person number if corrs. person number is passed
569
570 --Default the value of the record parameter S_GRADUAND_TYPE to 'UNKNOWN' in case it has a value of NULL.
571 IF p_lgcy_grd_rec.S_GRADUAND_TYPE IS NULL THEN
572 p_lgcy_grd_rec.S_GRADUAND_TYPE := 'UNKNOWN';
573 END IF;
574 --End of Default the value of the record parameter S_GRADUAND_TYPE to 'UNKNOWN' in case it has a value of NULL.
575
576 --If the record type parameter has GRADUAND_NAME as null then default the GRADUAND_NAME
577 IF p_lgcy_grd_rec.GRADUATION_NAME IS NULL THEN
578 p_lgcy_grd_rec.GRADUATION_NAME := IGS_GR_GEN_001.GRDP_GET_GRAD_NAME(p_person_id);
579 END IF;
580 --End of If the record type parameter has GRADUAND_NAME as null then default the GRADUAND_NAME
581
582 --Default the value of the record parameter CONVOCATION_MEMBERSHIP_IND to 'N' if it has a null value passed.
583 IF p_lgcy_grd_rec.CONVOCATION_MEMBERSHIP_IND IS NULL THEN
584 p_lgcy_grd_rec.CONVOCATION_MEMBERSHIP_IND := 'N';
585 END IF;
586 --End of Default the value of the record parameter CONVOCATION_MEMBERSHIP_IND to 'N' if it has a null value passed.
587
588 RETURN l_return_value;
589
590 END derive_graduand_data;
591
592
593 FUNCTION VALIDATE_GRAD_DB_CONS ( p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
594 p_cal_type IN IGS_CA_INST.CAL_TYPE%TYPE,
595 p_sequence_number IN IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
596 p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type
597 )
598 RETURN VARCHAR2
599 /****************************************************************************************************************
600 || Created By : msrinivi
601 || Created On : 11-Nov-2002
602 || Purpose : Validate db constraints
603 || If error occurs, add the msgs to the stack and return false
604 || Called by create_graduand
605 ||
606 || Known limitations, enhancements or remarks :
607 || Change History :
608 || Who When What
609 || (reverse chronological order - newest change first)
610 ****************************************************************************************************************/
611 IS
612
613 --Initialize API return status to success.
614 l_return_value VARCHAR2(10) := FND_API.G_RET_STS_SUCCESS;
615
616 BEGIN
617 -- Check for record existance
618 IF Igs_Gr_Graduand_Pkg.Get_Pk_For_Validation(X_PERSON_ID => p_person_id,
619 X_CREATE_DT => p_lgcy_grd_rec.create_dt) THEN
620 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_GRAD_PK_ALREADY_EXISTS');
621 FND_MSG_PUB.ADD;
622 l_return_value := 'W'; -- Error out
623 RETURN l_return_value;
624 END IF;
625 -- End of check for record existance
626
627 -- Check for Unique key
628 IF Igs_Gr_Graduand_Pkg.get_uk_for_validation(
629 X_PERSON_ID => p_person_id,
630 X_CREATE_DT => p_lgcy_grd_rec.create_dt,
631 X_AWARD_COURSE_CD => p_lgcy_grd_rec.award_program_cd,
632 X_AWARD_CRS_VERSION_NUMBER => p_lgcy_grd_rec.award_prog_version_number,
633 X_AWARD_CD => p_lgcy_grd_rec.award_cd
634 )
635 THEN
636 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_GRAD_UK_ALREADY_EXISTS');
637 FND_MSG_PUB.ADD;
638 l_return_value := 'W'; -- Error out
639 RETURN l_return_value;
640 END IF;
641 -- End of Check for Unique key check
642
643 -- Foreign key validation with the table IGS_PS_AWD_PKG Column Award_Cd
644 IF NOT IGS_PS_AWD_PKG.Get_PK_For_Validation(X_AWARD_CD => p_lgcy_grd_rec.award_cd)
645 THEN
646 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_AWDCD_FK_NOT_EXISTS');
647 FND_MSG_PUB.ADD;
648 l_return_value := 'E'; --Continue
649 END IF;
650 -- End of Foreign key validation with the table IGS_PS_AWD_PKG Column Award_Cd
651
652 -- Foreign key validation with the table IGS_PS_AWARD_PKG Columns award_course_cd, award_crs_version_number and Award_Cd
653 IF p_lgcy_grd_rec.AWARD_PROGRAM_CD IS NOT NULL AND
654 p_lgcy_grd_rec.AWARD_PROG_VERSION_NUMBER IS NOT NULL AND
655 p_lgcy_grd_rec.AWARD_CD IS NOT NULL AND
656 NOT IGS_PS_AWARD_PKG.Get_PK_For_Validation(X_COURSE_CD => p_lgcy_grd_rec.AWARD_PROGRAM_CD ,
657 X_VERSION_NUMBER => p_lgcy_grd_rec.AWARD_PROG_VERSION_NUMBER ,
658 X_AWARD_CD => p_lgcy_grd_rec.AWARD_CD )
659 THEN
660 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_AWDDET_FK_NOT_EXISTS');
661 FND_MSG_PUB.ADD;
662 l_return_value := 'E';--Continue
663 END IF;
664 -- End of Foreign key validation with the table IGS_PS_AWARD_PKG Columns award_course_cd, award_crs_version_number and Award_Cd
665
666 --Foreign key validation with the table IGS_GR_CRMN_ROUND_PKG column grd_cal_type and grd_ci_sequence_number
667 IF NOT IGS_GR_CRMN_ROUND_PKG.Get_PK_For_Validation(
668 X_GRD_CAL_TYPE => p_cal_type ,
669 X_GRD_CI_SEQUENCE_NUMBER => p_sequence_number
670 )
671 THEN
672 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_CRMRND_FK_NOT_EXISTS');
673 FND_MSG_PUB.ADD;
674 l_return_value := 'E';--Continue
675 END IF;
676 --End of Foreign key validation with the table IGS_GR_CRMN_ROUND_PKG column grd_cal_type and grd_ci_sequence_number
677
678 --7Foreign key validation with the table IGS_GR_APRV_STAT_PKG column graduand_appr_status
679 IF NOT IGS_GR_APRV_STAT_PKG.Get_PK_For_Validation(X_GRADUAND_APPR_STATUS => p_lgcy_grd_rec.GRADUAND_APPR_STATUS)
680 THEN
681 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_APRSTAT_FK_NOT_EXISTS');
682 FND_MSG_PUB.ADD;
683 l_return_value := 'E';--Continue
684 END IF;
685 --7End of Foreign key validation with the table IGS_GR_APRV_STAT_PKG column graduand_appr_status
686
687 --Foreign key validation with the table IGS_GR_STAT_PKG column graduand_status
688 IF NOT IGS_GR_STAT_PKG.Get_PK_For_Validation(X_GRADUAND_STATUS => p_lgcy_grd_rec.GRADUAND_STATUS)
689 THEN
690 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_GRDSTAT_FK_NOT_EXISTS');
691 FND_MSG_PUB.ADD;
692 l_return_value := 'E';--Continue
693 END IF;
694 --End of Foreign key validation with the table IGS_GR_STAT_PKG column graduand_status
695
696 --Foreign key validation with the table IGS_EN_STDNT_PS_ATT_PKG column person_id and course_cd
697 IF p_person_id IS NOT NULL AND
698 p_lgcy_grd_rec.program_cd IS NOT NULL AND
699 NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation(X_PERSON_ID => p_person_id,
700 X_COURSE_CD => p_lgcy_grd_rec.program_cd)
701 THEN
702 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_STPRATPT_FK_NOT_EXISTS');
703 FND_MSG_PUB.ADD;
704 l_return_value := 'E';--Continue
705 END IF;
706 --End of Foreign key validation with the table IGS_EN_STDNT_PS_ATT_PKG column person_id and course_cd
707
708
709 --Foreign key validation with the table IGS_PS_AWARD_PKG column sur_for_course_cd, sur_for_crs_version_number and sur_for_award_cd
710 IF p_lgcy_grd_rec.sur_for_program_cd IS NOT NULL AND
711 p_lgcy_grd_rec.sur_for_prog_version_number IS NOT NULL AND
712 p_lgcy_grd_rec.sur_for_award_cd IS NOT NULL AND
713 NOT IGS_PS_AWARD_PKG.Get_PK_For_Validation (X_COURSE_CD => p_lgcy_grd_rec.sur_for_program_cd ,
714 X_VERSION_NUMBER => p_lgcy_grd_rec.sur_for_prog_version_number ,
715 X_AWARD_CD => p_lgcy_grd_rec.sur_for_award_cd)
716 THEN
717 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_SURCRSCD_FK_NOT_EXISTS');
718 FND_MSG_PUB.ADD;
719 l_return_value := 'E';--Continue
720 END IF;
721 --End of Foreign key validation with the table IGS_PS_AWARD_PKG column sur_for_course_cd, sur_for_crs_version_number and sur_for_award_cd
722
723 IF p_lgcy_grd_rec.CONVOCATION_MEMBERSHIP_IND NOT IN('Y','N') THEN
724 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_CONV_MEM_IND_Y_N');
725 FND_MSG_PUB.ADD;
726 l_return_value := 'E';--Continue
727 END IF;
728
729 --S_GRADUAND_TYPE should not have any other value except for Attending , Inabsentia , Articulate , Deferred , Unknown , Declined:
730 IF p_lgcy_grd_rec.S_GRADUAND_TYPE NOT IN ( 'ATTENDING' , 'INABSENTIA' , 'ARTICULATE' , 'DEFERRED' , 'UNKNOWN' , 'DECLINED' ) THEN
731 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_SGRADTYP_INVALID_VAL');
732 FND_MSG_PUB.ADD;
733 l_return_value := 'E';--Continue
734 END IF;
735 --End of S_GRADUAND_TYPE should not have any other value except for Attending , Inabsentia , Articulate , Deferred , Unknown , Declined:
736
737 RETURN l_return_value;
738
739 END VALIDATE_GRAD_DB_CONS;
740
741 FUNCTION VALIDATE_GRADUANDS( p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type,
742 P_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
743 P_cal_type IN IGS_CA_INST.CAL_TYPE%TYPE,
744 P_sequence_number IN IGS_CA_INST. SEQUENCE_NUMBER%TYPE,
745 P_proxy_award_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
746 P_proxy_award_ind IN IGS_GR_GRADUAND_ALL. PROXY_AWARD_IND%TYPE
747
748 ) RETURN BOOLEAN
749
750 /****************************************************************************************************************
751 || Created By : msrinivi
752 || Created On : 11-Nov-2002
753 || Purpose : This function validates all the business rules before
754 || inserting a record in the table IGS_GR_GRADUAND_ALL
755 ||
756 ||
757 || Known limitations, enhancements or remarks :
758 || Change History :
759 || Who When What
760 || (reverse chronological order - newest change first)
761 ****************************************************************************************************************/
762 IS
763
764 --Initialize API return status to true
765 l_return_value BOOLEAN := FND_API.TO_BOOLEAN(FND_API.G_TRUE);
766
767 --Cursor to fetch the award type
768 CURSOR c_awd_type(p_award_cd igs_ps_awd.award_cd%TYPE) IS
769 SELECT S_AWARD_TYPE
770 FROM igs_ps_awd
771 WHERE award_cd = p_award_cd;
772
773 CURSOR c_gst IS
774 SELECT gst.s_graduand_status
775 FROM IGS_GR_STAT gst
776 WHERE gst.graduand_status = p_lgcy_grd_rec.graduand_status;
777
778 CURSOR c_gas IS
779 SELECT gas.s_graduand_appr_status
780 FROM IGS_GR_APRV_STAT gas
781 WHERE gas.graduand_appr_status = p_lgcy_grd_rec.graduand_appr_status;
782
783 CURSOR c_sca IS
784 SELECT sca.version_number,
785 sca.course_rqrmnt_complete_ind
786 FROM IGS_EN_STDNT_PS_ATT sca
787 WHERE sca.person_id = P_person_id AND
788 sca.course_cd = p_lgcy_grd_rec.program_cd;
789
790 CURSOR c_scaae
791 (cp_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
792 SELECT 'x'
793 FROM IGS_PS_STDNT_APV_ALT scaae
794 WHERE scaae.person_id = p_person_id AND
795 scaae.course_cd = p_lgcy_grd_rec.program_cd AND
796 scaae.version_number = cp_sca_version_number AND
797 scaae.exit_course_cd = p_lgcy_grd_rec.award_program_cd AND
798 scaae.exit_version_number = p_lgcy_grd_rec.award_prog_version_number AND
799 scaae.rqrmnts_complete_ind = 'Y';
800
801 CURSOR c_sur_sca IS
802 SELECT 'x'
803 FROM IGS_EN_STDNT_PS_ATT sca
804 WHERE sca.person_id = p_person_id AND
805 sca.course_cd = p_lgcy_grd_rec.sur_for_program_cd AND
806 sca.version_number = p_lgcy_grd_rec.sur_for_prog_version_number;
807
808 CURSOR c_stu_grad_status IS
809 SELECT S_GRADUAND_STATUS
810 FROM IGS_GR_STAT
811 WHERE GRADUAND_STATUS = p_lgcy_grd_rec.graduand_status;
812
813
814 l_s_award_type igs_ps_awd.S_AWARD_TYPE%TYPE;
815 v_gst_s_graduand_status IGS_GR_STAT.s_graduand_status%TYPE;
816 v_gas_s_graduand_appr_status IGS_GR_APRV_STAT.s_graduand_appr_status%TYPE;
817 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
818 v_sca_crs_rqrmnt_ind IGS_EN_STDNT_PS_ATT.course_rqrmnt_complete_ind%TYPE;
819 v_scaae_exists VARCHAR2(1);
820 v_sca_exists CHAR(1);
821
822 cst_attending CONSTANT IGS_GR_GRADUAND.s_graduand_type%TYPE := 'ATTENDING';
823 cst_graduated CONSTANT VARCHAR2(9) := 'GRADUATED';
824 cst_surrender CONSTANT VARCHAR2(9) := 'SURRENDER';
825 cst_approved CONSTANT VARCHAR2(8) := 'APPROVED';
826
827 l_start_dt DATE;
828 l_end_dt DATE;
829 l_message fnd_new_messages.message_name%TYPE;
830 l_stu_grad_status igs_gr_stat.s_graduand_status%TYPE;
831
832
833 BEGIN
834 --1 to 7 Validates GRADUAND required details.
835 l_message := NULL;
836 IF NOT IGS_GR_VAL_GR.grdp_val_gr_rqrd(
837 p_course_cd => p_lgcy_grd_rec.program_cd,
838 p_graduand_status => p_lgcy_grd_rec.graduand_status,
839 p_s_graduand_type => p_lgcy_grd_rec.s_graduand_type,
840 p_award_course_cd => p_lgcy_grd_rec.award_program_cd,
841 p_award_crs_version_number => p_lgcy_grd_rec.award_prog_version_number ,
842 p_award_cd => p_lgcy_grd_rec.award_cd ,
843 p_sur_for_course_cd => p_lgcy_grd_rec.sur_for_program_cd ,
844 p_sur_for_crs_version_number=> p_lgcy_grd_rec.sur_for_program_cd ,
845 p_sur_for_award_cd => p_lgcy_grd_rec.sur_for_award_cd,
846 p_message_name => l_message )THEN
847
848 FND_MESSAGE.SET_NAME ('IGS', l_message);
849 FND_MSG_PUB.ADD;
850 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE); --Continue
851
852 END IF;
853 --End of 1-7 Validate that the surrendering for program award can only be specified when surrendering or articulating an award
854
855 -- Validate that if the Proxy award person number is passed then
856 -- the proxy award person number must be a different person from that of the graduand.
857 IF p_lgcy_grd_rec.proxy_award_person_number IS NOT NULL AND
858 p_lgcy_grd_rec.proxy_award_person_number = p_lgcy_grd_rec.person_number THEN
859 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_PRXY_AWD_MUST_BE_DIFF');
860 FND_MSG_PUB.ADD;
861 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE); --Continue
862 END IF;
863 -- End of Validate that if the Proxy award person number is passed
864 -- then the proxy award person number must be a different person from that of the graduand.
865
866 --Validate that the Graduand cannot be graduated unless the graduand approval status has a system value of "APPROVED
867 OPEN c_gst;
868 FETCH c_gst INTO v_gst_s_graduand_status;
869 CLOSE c_gst;
870
871 OPEN c_gas;
872 FETCH c_gas INTO v_gas_s_graduand_appr_status;
873 CLOSE c_gas;
874
875 IF v_gst_s_graduand_status IN (cst_graduated,cst_surrender) THEN
876 IF v_gas_s_graduand_appr_status <> cst_approved THEN
877 FND_MESSAGE.SET_NAME ('IGS','IGS_GR_MUST_HAVE_VALUE_APPROV');
878 FND_MSG_PUB.ADD;
879 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE); --Continue
880 END IF;
881 END IF;
882 --End of Validate that the Graduand cannot be graduated unless the graduand approval status has a system value of "APPROVED
883
884 --11 Validate that if the program award code and program award version number is the same as the program code and version number then validate
885 IF p_lgcy_grd_rec.program_cd IS NOT NULL AND NOT IGS_GR_VAL_GR.grdp_val_aw_eligible(
886 p_person_id => p_person_id,
887 p_course_cd => p_lgcy_grd_rec.program_cd,
888 p_award_course_cd => p_lgcy_grd_rec.award_program_cd ,
889 p_award_crs_version_number => p_lgcy_grd_rec.award_prog_version_number ,
890 p_award_cd => p_lgcy_grd_rec.award_cd,
891 p_message_name => l_message) THEN
892
893 FND_MESSAGE.SET_NAME ('IGS',l_message);
894 FND_MSG_PUB.ADD;
895 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE); --Continue
896 END IF;
897 --11 End of Validate that if the program award code and program award version number is the same as the program code and version number then validate
898
899 --12 Validate that the students are allowed to graduate from the specified program
900 IF NOT IGS_GR_VAL_GR.grdp_val_gr_sca(p_person_id => p_person_id,
901 p_course_cd => p_lgcy_grd_rec.program_cd,
902 p_message_name => l_message) THEN
903 FND_MESSAGE.SET_NAME ('IGS',l_message);
904 FND_MSG_PUB.ADD;
905 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE); --Continue
906 END IF;
907 --End of Validate that the students are allowed to graduate from the specified program
908
909 --13, 14 ,15 Validate that the award must be of system award type PROGRAM, HON,
910 --No check for special award type
911 IF p_lgcy_grd_rec.program_cd IS NULL THEN
912 l_s_award_type := 'HONORARY';
913 ELSE
914 l_s_award_type := 'COURSE';
915 END IF;
916
917 IF NOT IGS_GR_VAL_AWC.grdp_val_award_type(p_award_cd => p_lgcy_grd_rec.award_cd,
918 p_s_award_type => l_s_award_type,
919 p_message_name => l_message) THEN
920 FND_MESSAGE.SET_NAME ('IGS',l_message);
921 FND_MSG_PUB.ADD;
922 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE); --Continue
923 END IF;
924 --End of 13, 14 ,15 Validate that the award must be of system award type PROGRAM, HON
925
926 --16 Validate that the graduand record program award is an award for the student program attempt or an alternative exit
927 IF NOT IGS_GR_VAL_GR.grdp_val_gr_caw
928 (
929 p_person_id => p_person_id,
930 p_course_cd => p_lgcy_grd_rec.program_cd,
931 p_award_course_cd => p_lgcy_grd_rec.award_program_cd,
932 p_award_crs_version_number => p_lgcy_grd_rec.award_prog_version_number,
933 p_award_cd => p_lgcy_grd_rec.award_cd,
934 p_message_name => l_message
935 )THEN
936 FND_MESSAGE.SET_NAME ('IGS',l_message);
937 FND_MSG_PUB.ADD;
938 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE); --Continue
939 END IF;
940 --End of 16 Validate that the graduand record program award is an award for the student program attempt or an alternative exit
941
942 --17,18 Validate that a student cannot articulate or surrender a graduand record for the same program.
943 IF NOT IGS_GR_VAL_GR.GRDP_VAL_GR_SUR_CAW(
944 p_person_id => p_person_id ,
945 p_course_cd => p_lgcy_grd_rec.program_cd ,
946 p_graduand_status => p_lgcy_grd_rec.graduand_status ,
947 p_sur_for_course_cd => p_lgcy_grd_rec.sur_for_program_cd ,
948 p_sur_for_crs_version_num => p_lgcy_grd_rec.sur_for_prog_version_number,
949 p_sur_for_award_cd => p_lgcy_grd_rec.sur_for_award_cd ,
950 p_message_name => l_message) THEN
951
952 FND_MESSAGE.SET_NAME ('IGS',l_message);
953 FND_MSG_PUB.ADD;
954 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE); --Continue
955 END IF;
956 --end of 17, 18
957
958 --20
959 IF p_lgcy_grd_rec.s_graduand_type = 'ATTENDING' AND p_lgcy_grd_rec.proxy_award_person_number IS NOT NULL THEN
960 FND_MESSAGE.SET_NAME ('IGS','IGS_GR_PROXY_NOT_ALLOW');
961 FND_MSG_PUB.ADD;
962 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE); --Continue
963 END IF;
964 --End of 20
965
966 RETURN l_return_value ;
967
968 END VALIDATE_GRADUANDS;
969
970
971 FUNCTION VALIDATE_POST_GRADUAND(
972 p_lgcy_grd_rec IN OUT NOCOPY lgcy_grd_rec_type,
973 P_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
974 P_cal_type IN IGS_CA_INST.CAL_TYPE%TYPE,
975 P_sequence_number IN IGS_CA_INST. SEQUENCE_NUMBER%TYPE
976 )RETURN BOOLEAN
977
978 /****************************************************************************************************************
979 || Created By : msrinivi
980 || Created On : 11-Nov-2002
981 || Purpose : This function validates all the business rules after
982 || inserting a record in the table IGS_GR_GRADUAND
983 ||
984 ||
985 || Known limitations, enhancements or remarks :
986 || Change History :
987 || Who When What
988 || (reverse chronological order - newest change first)
989 ****************************************************************************************************************/
990 IS
991
992 --Initialize API return status to true
993 l_return_value BOOLEAN := FND_API.TO_BOOLEAN(FND_API.G_TRUE);
994 l_message fnd_new_messages.message_name%TYPE;
995
996
997 BEGIN
998
999 IF NOT igs_gr_val_gr.grdp_val_gr_unique(
1000 p_person_id => p_person_id,
1001 p_create_dt => p_lgcy_grd_rec.create_dt,
1002 p_grd_cal_type => p_cal_type,
1003 p_grd_ci_sequence_num => p_sequence_number,
1004 p_award_course_cd => p_lgcy_grd_rec.award_program_cd,
1005 p_award_crs_version_number=> p_lgcy_grd_rec.award_prog_version_number,
1006 p_award_cd => p_lgcy_grd_rec.award_cd,
1007 p_message_name => l_message ) THEN
1008 FND_MESSAGE.SET_NAME ('IGS','IGS_GR_GRAD_CR_DT_FAIL');
1009 FND_MSG_PUB.ADD;
1010 l_return_value := FND_API.TO_BOOLEAN(FND_API.G_FALSE);
1011 END IF;
1012
1013 RETURN l_return_value;
1014
1015 END VALIDATE_POST_GRADUAND;
1016
1017 END IGS_GR_GRD_LGCY_PUB;
[Home] [Help]