1 PACKAGE BODY igs_av_lvl_lgcy_pub AS
2 /* $Header: IGSPAV1B.pls 120.8 2006/08/07 08:33:03 amanohar ship $ */
3 /**************************************************************************
4 Created By -
5 Purpose
6 History
7 Who When Why
8 Aiyer 03-jan-2003 The function validate_lvl_db_cons,
9 create_adv_stnd_level have been modified
10 as per the bug #2732975.
11 ****************************************************************************/
12
13
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_AV_LVL_LGCY_PUB';
15
16 PROCEDURE initialise ( p_lgcy_adstlvl_rec IN OUT NOCOPY lgcy_adstlvl_rec_type )
17 IS
18 BEGIN
19 p_lgcy_adstlvl_rec.person_number := NULL;
20 p_lgcy_adstlvl_rec.program_cd := NULL;
21 p_lgcy_adstlvl_rec.total_exmptn_approved := NULL;
22 p_lgcy_adstlvl_rec.total_exmptn_granted := NULL;
23 p_lgcy_adstlvl_rec.total_exmptn_perc_grntd := NULL;
24 p_lgcy_adstlvl_rec.exemption_institution_cd := NULL;
25 p_lgcy_adstlvl_rec.unit_level := NULL;
26 p_lgcy_adstlvl_rec.prog_group_ind := NULL;
27 p_lgcy_adstlvl_rec.load_cal_alt_code := NULL;
28 p_lgcy_adstlvl_rec.institution_cd := NULL;
29 p_lgcy_adstlvl_rec.s_adv_stnd_granting_status := NULL;
30 p_lgcy_adstlvl_rec.credit_points := NULL;
31 p_lgcy_adstlvl_rec.approved_dt := NULL;
32 p_lgcy_adstlvl_rec.authorising_person_number := NULL;
33 p_lgcy_adstlvl_rec.granted_dt := NULL;
34 p_lgcy_adstlvl_rec.expiry_dt := NULL;
35 p_lgcy_adstlvl_rec.cancelled_dt := NULL;
36 p_lgcy_adstlvl_rec.revoked_dt := NULL;
37 p_lgcy_adstlvl_rec.comments := NULL;
38 p_lgcy_adstlvl_rec.qual_exam_level := NULL;
39 p_lgcy_adstlvl_rec.qual_subject_code := NULL;
40 p_lgcy_adstlvl_rec.qual_year := NULL;
41 p_lgcy_adstlvl_rec.qual_sitting := NULL;
42 p_lgcy_adstlvl_rec.qual_awarding_body := NULL;
43 p_lgcy_adstlvl_rec.approved_result := NULL;
44 p_lgcy_adstlvl_rec.prev_unit_cd := NULL;
45 p_lgcy_adstlvl_rec.prev_term := NULL;
46 p_lgcy_adstlvl_rec.start_date := NULL;
47 p_lgcy_adstlvl_rec.end_date := NULL;
48 p_lgcy_adstlvl_rec.tst_admission_test_type := NULL;
49 p_lgcy_adstlvl_rec.tst_test_date := NULL;
50 p_lgcy_adstlvl_rec.test_segment_name := NULL;
51 p_lgcy_adstlvl_rec.basis_program_type := NULL;
52 p_lgcy_adstlvl_rec.basis_year := NULL;
53 p_lgcy_adstlvl_rec.basis_completion_ind := NULL;
54 p_lgcy_adstlvl_rec.unit_level_mark := NULL;
55
56 END initialise;
57
58 -- forward declaration of procedure/function used in this package
59
60 /*
61 validate_parameters function checks all the mandatory parameters
62 for the passed record type are not null
63 */
64 FUNCTION validate_parameters
65 (
66 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
67 )
68 RETURN BOOLEAN;
69
70 /*
71 derive_level_data procedure derives advanced standing unit level data like: -
72 1. Derive Person_id from person_number .
73 2. Derive cal_type and sequence_number from load_cal_alt_code
74 3. Set Unit_level parameter
75 4. Derive the authorizing_person_id from authorizing_person_number
76 5. Derive Unit_details_id , tst_rslt_dtls_id and qual_dets_id
77 */
78
79 PROCEDURE derive_level_data
80 (
81 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type,
82 p_person_id OUT NOCOPY igs_pe_person.person_id%type,
83 p_s_adv_stnd_unit_level OUT NOCOPY igs_av_stnd_unit_lvl.s_adv_stnd_type%type,
84 p_cal_type OUT NOCOPY igs_ca_inst.cal_type%type,
85 p_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%type,
86 p_auth_pers_id OUT NOCOPY igs_pe_person.person_id%type,
87 p_unit_details_id OUT NOCOPY igs_ad_term_unitdtls.unit_details_id%type,
88 p_tst_rslt_dtls_id OUT NOCOPY igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
89 p_qual_dets_id OUT NOCOPY igs_uc_qual_dets.qual_dets_id%type,
90 p_as_version_number OUT NOCOPY igs_en_stdnt_ps_att.version_number%type
91 );
92
93 /*
94 validate_adv_std_db_cons function performs all the data integrity validation
95 */
96 FUNCTION validate_adv_std_db_cons
97 (
98 p_person_id IN igs_pe_person.person_id%type,
99 p_version_number IN igs_ps_ver_all.version_number%type,
100 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
101 )
102 RETURN BOOLEAN;
103
104 /*
105 validate_adv_stnd function validates all the business rules before
106 inserting a record in the table IGS_AV_ADV_STANDING_ALL
107 */
108 FUNCTION validate_adv_stnd
109 (
110 p_person_id IN igs_pe_person.person_id%type,
111 p_version_number IN igs_ps_ver.version_number%type,
112 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
113 )
114 RETURN BOOLEAN;
115
116 /*
117 validate_lvl_db_cons function performs all the data integrity validation
118 before entering into the table IGS_AV_STND_UNIT_LVL_ALL
119 */
120 FUNCTION validate_lvl_db_cons
121 (
122 p_person_id IN igs_pe_person.person_id%type,
123 p_s_adv_stnd_unit_level IN igs_ps_unit_level.unit_level%type,
124 p_cal_type IN igs_ca_inst.cal_type%type,
125 p_seq_number IN igs_ca_inst.sequence_number%type,
126 p_auth_pers_id IN igs_pe_person.person_id%type,
127 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%type,
128 p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
129 p_qual_dets_id IN igs_uc_qual_dets.qual_dets_id%type,
130 p_course_version IN igs_ps_ver.version_number%type,
131 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type,
132 p_av_stnd_unit_lvl_id OUT NOCOPY igs_av_std_ulvlbasis_all.av_stnd_unit_lvl_id%type
133 )
134 RETURN BOOLEAN;
135
136 /*
137 validate_level function performs all the business validations before
138 inserting a record into the table IGS_AV_STND_UNIT_LVL_ALL
139 */
140 FUNCTION validate_level
141 (
142 p_person_id IN igs_pe_person.person_id%type,
143 p_unit_level IN igs_ps_unit_level.unit_level%type,
144 p_cal_type IN igs_ca_inst.cal_type%type,
145 p_seq_number IN igs_ca_inst.sequence_number%type,
146 p_auth_pers_id IN igs_pe_person.person_id%type,
147 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%type,
148 p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
149 p_qual_dets_id IN igs_uc_qual_dets.qual_dets_id%type,
150 p_course_version IN igs_ps_ver.version_number%type,
151 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
152 )
153 RETURN BOOLEAN;
154
155 /*
156 create_post_lvl function performs all the Post Insert business
157 validations on the table IGS_AV_STND_UNIT_LVL_ALL
158 */
159 FUNCTION create_post_lvl
160 (
161 p_person_id IN igs_pe_person.person_id%type,
162 p_course_version IN igs_ps_ver.version_number%type,
163 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%type,
164 p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
165 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
166 )
167 RETURN BOOLEAN;
168
169 /*
170 validate_lvl_bas_db_cons function performs all the data integrity validation
171 before inserting into the table IGS_AV_STD_ULVLBASIS_ALL
172 */
173 FUNCTION validate_lvl_bas_db_cons
174 (
175 p_person_id IN igs_pe_person.person_id%type,
176 p_av_stnd_unit_lvl_id IN igs_av_std_ulvlbasis_all.av_stnd_unit_lvl_id%type,
177 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
178 )
179 RETURN VARCHAR2;
180
181 /*
182 validate_lvl_bas function performs all the business validation before
183 inserting into the table IGS_AV_STD_ULVLBASIS_ALL
184 */
185 FUNCTION validate_lvl_bas
186 (
187 p_course_version IN igs_ps_ver.version_number%type,
188 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
189 )
190 RETURN BOOLEAN;
191
192
193
194 /*===================================================================+
195 | PROCEDURE |
196 | create_adv_stnd_level |
197 | |
198 | DESCRIPTION |
199 | Creates advanced standing unit level |
200 | |
201 | SCOPE - PUBLIC |
202 | |
203 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
204 | |
205 | ARGUMENTS : IN: |
206 | p_api_version |
207 | p_init_msg_list |
208 | p_commit |
209 | p_lgcy_adstlvl_rec |
210 | OUT: |
211 | x_return_status |
212 | x_msg_count |
213 | x_msg_data |
214 | IN/ OUT: |
215 | |
216 | RETURNS : NONE |
217 | |
218 | NOTES |
219 | |
220 | MODIFICATION HISTORY |
221 | smanglm 11-11-2002 Created |
222 | kdande 03-Jan-2003 Added check for s_adv_stnd_granting_status =|
223 | 'EXPIRED' and expiry_dt IS NULL |
224 |Aiyer 07-Jan-2002 Code modified as a part of the fix for |
225 | the bug #2732975. |
226 | If l_av_stnd_unit_lvl_id is null then set a|
227 | warning that Advanced Standing unit level |
228 | records already exists. |
229 | |
230 +===================================================================*/
231
232 PROCEDURE create_adv_stnd_level
233 (p_api_version IN NUMBER,
234 p_init_msg_list IN VARCHAR2,
235 p_commit IN VARCHAR2,
236 p_validation_level IN VARCHAR2,
237 p_lgcy_adstlvl_rec IN OUT NOCOPY lgcy_adstlvl_rec_type,
238 x_return_status OUT NOCOPY VARCHAR2,
239 x_msg_count OUT NOCOPY NUMBER,
240 x_msg_data OUT NOCOPY VARCHAR2
241 )
242 IS
243 l_api_name CONSTANT VARCHAR2(30) := 'create_adv_stnd_level';
244 l_api_version CONSTANT NUMBER := 1.0;
245
246 -- variables declared to fetch data from derive_level_data
247 l_person_id igs_pe_person.person_id%type;
248 l_s_adv_stnd_unit_level igs_av_stnd_unit_lvl.s_adv_stnd_type%type;
249 l_cal_type igs_ca_inst.cal_type%type;
250 l_sequence_number igs_ca_inst.sequence_number%type;
251 l_auth_pers_id igs_pe_person.person_id%type;
252 l_unit_details_id igs_ad_term_unitdtls.unit_details_id%type;
253 l_tst_rslt_dtls_id igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type;
254 l_qual_dets_id igs_uc_qual_dets.qual_dets_id%type;
255 l_as_version_number igs_en_stdnt_ps_att.version_number%type;
256
257 -- variable declared to fetch data from validate_lvl_db_cons
258 l_av_stnd_unit_lvl_id igs_av_std_ulvlbasis_all.av_stnd_unit_lvl_id%type;
259
260 l_check VARCHAR2(1) := 'N'; -- check whether to insert into parent
261 l_skip VARCHAR2(1) := 'N'; -- check whether to to skip the steps
262 l_return VARCHAR2(1) ;
263
264 BEGIN -- main begin
265 --Standard start of API savepoint
266 SAVEPOINT create_adv_stnd_level;
267
268 --Standard call to check for call compatibility.
269 IF NOT FND_API.Compatible_API_Call(
270 l_api_version,
271 p_api_version,
272 l_api_name,
273 G_PKG_NAME)
274 THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277
278 --Initialize message list if p_init_msg_list is set to TRUE.
279 IF FND_API.to_Boolean(p_init_msg_list) THEN
280 FND_MSG_PUB.initialize;
281 END IF;
282
283 --Initialize API return status to success.
284 x_return_status := FND_API.G_RET_STS_SUCCESS;
285
286
287 -- main code logic begins
288 -- ensure UPPER case
289 p_lgcy_adstlvl_rec.basis_program_type := UPPER(p_lgcy_adstlvl_rec.basis_program_type) ;
290 p_lgcy_adstlvl_rec.program_cd := UPPER(p_lgcy_adstlvl_rec.program_cd) ;
291 p_lgcy_adstlvl_rec.exemption_institution_cd := UPPER(p_lgcy_adstlvl_rec.exemption_institution_cd) ;
292 p_lgcy_adstlvl_rec.unit_level := UPPER(p_lgcy_adstlvl_rec.unit_level) ;
293 p_lgcy_adstlvl_rec.prog_group_ind := UPPER(p_lgcy_adstlvl_rec.prog_group_ind) ;
294 p_lgcy_adstlvl_rec.s_adv_stnd_granting_status := UPPER(p_lgcy_adstlvl_rec.s_adv_stnd_granting_status);
295 -- call validate_parameters
296 IF validate_parameters(p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec) THEN
297 -- call derive_level_data
298 derive_level_data (
299 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec ,
300 p_person_id => l_person_id ,
301 p_s_adv_stnd_unit_level => l_s_adv_stnd_unit_level,
302 p_cal_type => l_cal_type ,
303 p_sequence_number => l_sequence_number ,
304 p_auth_pers_id => l_auth_pers_id ,
305 p_unit_details_id => l_unit_details_id ,
306 p_tst_rslt_dtls_id => l_tst_rslt_dtls_id ,
307 p_qual_dets_id => l_qual_dets_id ,
308 p_as_version_number => l_as_version_number
309 );
310 -- call validate_adv_std_db_cons
311 IF validate_adv_std_db_cons
312 (
313 p_person_id => l_person_id,
314 p_version_number => l_as_version_number,
315 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
316 ) THEN
317 -- call validate_adv_stnd
318 IF validate_adv_stnd
319 (
320 p_person_id => l_person_id,
321 p_version_number => l_as_version_number,
322 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
323 ) THEN
324 /*
325 Validate that the current record is already present in the
326 tables IGS_AV_ADV_STANDING_ALL and IGS_AV_STND_UNIT_LVL_ALL
327 */
328 /*
329 Check that the Primary Key for the table
330 IGS_AV_ADV_STANDING_ALL does not exists
331 */
332 IF IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION
333 (
334 x_person_id => l_person_id,
335 x_course_cd => p_lgcy_adstlvl_rec.program_cd,
336 x_version_number => l_as_version_number,
337 x_exemption_institution_cd => p_lgcy_adstlvl_rec.exemption_institution_cd
338 ) THEN
339 /*
340 Check that the unique key combination for the table
341 IGS_AV_STND_UNIT_LVL_ALL does not already exist
342 */
343 IF IGS_AV_STND_UNIT_LVL_PKG.GET_UK_FOR_VALIDATION
344 (
345 x_person_id => l_person_id,
346 x_exemption_institution_cd => p_lgcy_adstlvl_rec.exemption_institution_cd,
347 x_unit_details_id => l_unit_details_id,
348 x_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
349 x_unit_level => p_lgcy_adstlvl_rec.unit_level,
350 x_as_course_cd => p_lgcy_adstlvl_rec.program_cd,
351 x_as_version_number => l_as_version_number,
352 x_qual_dets_id => l_qual_dets_id,
353 X_S_ADV_STND_TYPE => l_s_adv_stnd_unit_level,
354 X_CRS_GROUP_IND => NVL(UPPER(p_lgcy_adstlvl_rec.prog_group_ind),'N')
355 )
356 THEN
357
358 /*
359 This code has been added as a part of the fix for the bug #2732975.
360 If l_av_stnd_unit_lvl_id is null then set a warning that Advanced Standing
361 unit level records already exists.
362 */
363
364 IF l_av_stnd_unit_lvl_id IS NULL THEN
365 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ADV_STND_ALREADY_EXISTS');
366 FND_MSG_PUB.ADD;
367 x_return_status := 'W';
368 -- skip to end
369 l_skip := 'Y';
370 ELSE
371 l_return := validate_lvl_bas_db_cons
372 (
373 p_person_id => l_person_id,
374 p_av_stnd_unit_lvl_id => l_av_stnd_unit_lvl_id,
375 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
376 );
377 IF l_return = 'S' THEN
378 IF validate_lvl_bas
379 (
380 p_course_version => l_as_version_number,
381 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
382 )
383 THEN
384 /*
385 insert into IGS_AV_STD_ULVLBASIS_ALL
386 */
387 INSERT INTO IGS_AV_STD_ULVLBASIS_ALL (
388 AV_STND_UNIT_LVL_ID,
389 BASIS_COURSE_TYPE,
390 BASIS_YEAR,
391 BASIS_COMPLETION_IND,
392 CREATION_DATE,
393 CREATED_BY,
394 LAST_UPDATE_DATE,
395 LAST_UPDATED_BY,
396 LAST_UPDATE_LOGIN,
397 ORG_ID
398 )
399 VALUES (
400 l_av_stnd_unit_lvl_id,
401 UPPER(p_lgcy_adstlvl_rec.basis_program_type),
402 p_lgcy_adstlvl_rec.basis_year,
403 p_lgcy_adstlvl_rec.basis_completion_ind,
404 SYSDATE,
405 NVL(FND_GLOBAL.USER_ID,-1),
406 SYSDATE,
407 NVL(FND_GLOBAL.USER_ID,-1),
408 NVL(FND_GLOBAL.LOGIN_ID,-1),
409 igs_ge_gen_003.get_org_id
410 );
411
412 ELSE -- validate_lvl_bas
413 x_return_status := l_return; --it can be either E or W
414 END IF; -- validate_lvl_bas
415 ELSE -- l_return
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 END IF;
418 -- skip to end
419 l_skip := 'Y';
420 END IF; -- End of l_av_stnd_unit_lvl_id is not null
421 ELSE
422 l_check := 'Y';
423 END IF; -- IGS_AV_STND_UNIT_LVL_PKG.GET_UK_FOR_VALIDATION
424 ELSE -- IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION
425 l_check := 'N';
426 END IF; -- IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION
427
428 IF l_skip = 'N' THEN
429 IF l_check = 'N' THEN
430 -- insert into IGS_AV_ADV_STANDING_ALL
431 INSERT INTO IGS_AV_ADV_STANDING_ALL (
432 PERSON_ID,
433 COURSE_CD,
434 VERSION_NUMBER,
435 TOTAL_EXMPTN_APPROVED,
436 TOTAL_EXMPTN_GRANTED,
437 TOTAL_EXMPTN_PERC_GRNTD,
438 EXEMPTION_INSTITUTION_CD,
439 CREATION_DATE,
440 CREATED_BY,
441 LAST_UPDATE_DATE,
442 LAST_UPDATED_BY,
443 LAST_UPDATE_LOGIN,
444 ORG_ID
445 ) VALUES (
446 l_person_id,
447 UPPER(p_lgcy_adstlvl_rec.program_cd),
448 l_as_version_number,
449 p_lgcy_adstlvl_rec.total_exmptn_approved,
450 p_lgcy_adstlvl_rec.total_exmptn_granted,
451 p_lgcy_adstlvl_rec.total_exmptn_perc_grntd,
452 UPPER(p_lgcy_adstlvl_rec.exemption_institution_cd),
453 SYSDATE,
454 NVL(FND_GLOBAL.USER_ID,-1),
455 SYSDATE,
456 NVL(FND_GLOBAL.USER_ID,-1),
457 NVL(FND_GLOBAL.LOGIN_ID,-1),
458 igs_ge_gen_003.get_org_id
459 );
460 END IF; --l_check = 'Y'
461 -- step 8 of TD
462 /*
463 call to VALIDATE_LVL_DB_CONS
464 */
465 IF validate_lvl_db_cons
466 (
467 p_person_id => l_person_id,
468 p_s_adv_stnd_unit_level => p_lgcy_adstlvl_rec.unit_level,
469 p_cal_type => l_cal_type,
470 p_seq_number => l_sequence_number,
471 p_auth_pers_id => l_auth_pers_id,
472 p_unit_details_id => l_unit_details_id,
473 p_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
474 p_qual_dets_id => l_qual_dets_id,
475 p_course_version => l_as_version_number,
476 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec,
477 p_av_stnd_unit_lvl_id => l_av_stnd_unit_lvl_id
478 ) THEN
479 IF validate_level
480 (
481 p_person_id => l_person_id,
482 p_unit_level => p_lgcy_adstlvl_rec.unit_level,
483 p_cal_type => l_cal_type,
484 p_seq_number => l_sequence_number,
485 p_auth_pers_id => l_auth_pers_id,
486 p_unit_details_id => l_unit_details_id,
487 p_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
488 p_qual_dets_id => l_qual_dets_id,
489 p_course_version => l_as_version_number,
490 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
491 ) THEN
492 /*
493 insert into IGS_AV_STND_UNIT_LVL_ALL
494 */
495 INSERT INTO IGS_AV_STND_UNIT_LVL_ALL (
496 PERSON_ID,
497 AS_COURSE_CD,
498 AS_VERSION_NUMBER,
499 S_ADV_STND_TYPE,
500 UNIT_LEVEL,
501 CRS_GROUP_IND,
502 EXEMPTION_INSTITUTION_CD,
503 S_ADV_STND_GRANTING_STATUS,
504 CREDIT_POINTS,
505 APPROVED_DT,
506 AUTHORISING_PERSON_ID,
507 GRANTED_DT,
508 EXPIRY_DT,
509 CANCELLED_DT,
510 REVOKED_DT,
511 COMMENTS,
512 AV_STND_UNIT_LVL_ID,
513 CAL_TYPE,
514 CI_SEQUENCE_NUMBER,
515 INSTITUTION_CD,
516 UNIT_DETAILS_ID,
517 TST_RSLT_DTLS_ID,
518 CREATION_DATE,
519 CREATED_BY,
520 LAST_UPDATE_DATE,
521 LAST_UPDATED_BY,
522 LAST_UPDATE_LOGIN,
523 REQUEST_ID,
524 PROGRAM_ID,
525 PROGRAM_APPLICATION_ID,
526 PROGRAM_UPDATE_DATE,
527 ORG_ID,
528 DEG_AUD_DETAIL_ID,
529 QUAL_DETS_ID,
530 UNIT_LEVEL_MARK
531 ) VALUES (
532 l_person_id,
533 UPPER(p_lgcy_adstlvl_rec.program_cd),
534 l_as_version_number,
535 l_s_adv_stnd_unit_level,
536 UPPER(p_lgcy_adstlvl_rec.unit_level),
537 NVL(UPPER(p_lgcy_adstlvl_rec.prog_group_ind),'N'),
538 UPPER(p_lgcy_adstlvl_rec.exemption_institution_cd),
539 UPPER(p_lgcy_adstlvl_rec.s_adv_stnd_granting_status),
540 p_lgcy_adstlvl_rec.credit_points,
541 p_lgcy_adstlvl_rec.approved_dt,
542 l_auth_pers_id,
543 p_lgcy_adstlvl_rec.granted_dt,
544 p_lgcy_adstlvl_rec.expiry_dt,
545 p_lgcy_adstlvl_rec.cancelled_dt,
546 p_lgcy_adstlvl_rec.revoked_dt,
547 p_lgcy_adstlvl_rec.comments,
548 l_av_stnd_unit_lvl_id,
549 l_cal_type,
550 l_sequence_number,
551 p_lgcy_adstlvl_rec.institution_cd,
552 l_unit_details_id,
553 l_tst_rslt_dtls_id,
554 SYSDATE,
555 NVL(FND_GLOBAL.USER_ID,-1),
556 SYSDATE,
557 NVL(FND_GLOBAL.USER_ID,-1),
558 NVL(FND_GLOBAL.LOGIN_ID,-1),
559 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
560 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
561 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
562 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
563 igs_ge_gen_003.get_org_id,
564 NULL, -- aiyer NEW_REFERENCES.DEG_AUD_DETAIL_ID,
565 l_qual_dets_id,
566 p_lgcy_adstlvl_rec.UNIT_LEVEL_MARK
567 );
568
569 /*
570 post insert validation create_post_lvl
571 */
572 IF create_post_lvl
573 (
574 p_person_id => l_person_id,
575 p_course_version => l_as_version_number,
576 p_unit_details_id => l_unit_details_id,
577 p_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
578 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
579 ) THEN
580 l_return := validate_lvl_bas_db_cons
581 (
582 p_person_id => l_person_id,
583 p_av_stnd_unit_lvl_id => l_av_stnd_unit_lvl_id,
584 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
585 );
586 IF l_return = 'S' THEN
587 IF validate_lvl_bas
588 (
589 p_course_version => l_as_version_number,
590 p_lgcy_adstlvl_rec => p_lgcy_adstlvl_rec
591 ) THEN
592 /*
593 insert into IGS_AV_STD_ULVLBASIS_ALL
594 */
595 INSERT INTO IGS_AV_STD_ULVLBASIS_ALL (
596 AV_STND_UNIT_LVL_ID,
597 BASIS_COURSE_TYPE,
598 BASIS_YEAR,
599 BASIS_COMPLETION_IND,
600 CREATION_DATE,
601 CREATED_BY,
602 LAST_UPDATE_DATE,
603 LAST_UPDATED_BY,
604 LAST_UPDATE_LOGIN,
605 ORG_ID
606 ) VALUES (
607 l_av_stnd_unit_lvl_id,
608 UPPER(p_lgcy_adstlvl_rec.basis_program_type),
609 p_lgcy_adstlvl_rec.basis_year,
610 p_lgcy_adstlvl_rec.basis_completion_ind,
611 SYSDATE,
612 NVL(FND_GLOBAL.USER_ID,-1),
613 SYSDATE,
614 NVL(FND_GLOBAL.USER_ID,-1),
615 NVL(FND_GLOBAL.LOGIN_ID,-1),
616 igs_ge_gen_003.get_org_id
617 );
618 ELSE -- validate_lvl_bas
619 x_return_status := l_return; --it can be either E or W
620 END IF; -- validate_lvl_bas
621 ELSE -- l_return
622 x_return_status := FND_API.G_RET_STS_ERROR;
623 END IF;
624 ELSE --create_post_lvl
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 END IF; --create_post_lvl
627 ELSE -- validate level
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 END IF; -- validate level
630 ELSE -- VALIDATE_LVL_DB_CONS
631 x_return_status := FND_API.G_RET_STS_ERROR;
632 END IF; -- VALIDATE_LVL_DB_CONS
633 END IF; -- l_skip
634 ELSE -- validate_adv_stnd
635 x_return_status := FND_API.G_RET_STS_ERROR;
636 END IF; --validate_adv_stnd
637 ELSE -- validate_adv_std_db_cons
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 END IF; -- validate_adv_std_db_cons
640 ELSE -- validate_parameters(p_lgcy_adstlvl_rec)
641 x_return_status := FND_API.G_RET_STS_ERROR;
642 END IF; -- validate_parameters(p_lgcy_adstlvl_rec)
643
644 --rollback if the x_return_status is set to E (FND_API.G_RET_STS_ERROR) or W
645 IF x_return_status IN (FND_API.G_RET_STS_ERROR,'W','E') THEN
646 ROLLBACK TO create_adv_stnd_level;
647 END IF;
648
649 --Standard check of p_commit.
650 IF FND_API.to_Boolean(p_commit) AND x_return_status = FND_API.G_RET_STS_SUCCESS THEN
651 commit;
652 END IF;
653
654 --Standard call to get message count and if count is 1, get message info.
655 FND_MSG_PUB.Count_And_Get(
656 p_count => x_msg_count,
657 p_data => x_msg_data);
658
659 EXCEPTION
660 WHEN FND_API.G_EXC_ERROR THEN
661 ROLLBACK TO create_adv_stnd_level;
662 x_return_status := FND_API.G_RET_STS_ERROR;
663 FND_MSG_PUB.Count_And_Get(
664 p_count => x_msg_count,
665 p_data => x_msg_data);
666
667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
668 ROLLBACK TO create_adv_stnd_level;
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 FND_MSG_PUB.Count_And_Get(
671 p_count => x_msg_count,
672 p_data => x_msg_data);
673 WHEN OTHERS THEN
674 ROLLBACK TO create_adv_stnd_level;
675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
677 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
678 FND_MSG_PUB.ADD;
679 FND_MSG_PUB.Count_And_Get(
680 p_count => x_msg_count,
681 p_data => x_msg_data);
682
683 END create_adv_stnd_level;
684
685
686 FUNCTION validate_parameters
687 (
688 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
689 )
690 RETURN BOOLEAN
691 /*************************************************************
692 Created By : smanglm
693 Date Created on : 2002/11/13
694 Purpose : validate_parameters function checks all the mandatory
695 parameters for the passed record type are not null
696 Know limitations, enhancements or remarks
697 Change History
698 Who When What
699 shimitta 2005/11/9 BUG#4723892 :added a check for unit_level_mark
700 (reverse chronological order - newest change first)
701 ***************************************************************/
702 IS
703 x_return_status BOOLEAN;
704 BEGIN
705 x_return_status := TRUE;
706
707 IF p_lgcy_adstlvl_rec.unit_level_mark < 0
708 OR p_lgcy_adstlvl_rec.unit_level_mark > 100 THEN
709 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_INV_UNT_LVL_MARK');
710 FND_MSG_PUB.ADD;
711 x_return_status := FALSE;
712 END IF; -- shimitta
713 IF p_lgcy_adstlvl_rec.person_number IS NULL THEN
714 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PER_NUM_NULL');
715 FND_MSG_PUB.ADD;
716 x_return_status := FALSE;
717 END IF;
718 IF p_lgcy_adstlvl_rec.program_cd IS NULL THEN
719 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PRGM_CD_NULL');
720 FND_MSG_PUB.ADD;
721 x_return_status := FALSE;
722 END IF;
723 IF p_lgcy_adstlvl_rec.total_exmptn_approved IS NULL THEN
724 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_TOT_EXMPT_APPR_NULL');
725 FND_MSG_PUB.ADD;
726 x_return_status := FALSE;
727 END IF;
728 IF p_lgcy_adstlvl_rec.total_exmptn_granted IS NULL THEN
729 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_TOT_EXMPT_GRNT_NULL');
730 FND_MSG_PUB.ADD;
731 x_return_status := FALSE;
732 END IF;
733 IF p_lgcy_adstlvl_rec.total_exmptn_perc_grntd IS NULL OR
734 p_lgcy_adstlvl_rec.total_exmptn_perc_grntd < 0 OR
735 p_lgcy_adstlvl_rec.total_exmptn_perc_grntd > 100 THEN
736 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_TOT_EXT_PER_GRNT_NULL');
737 FND_MSG_PUB.ADD;
738 x_return_status := FALSE;
739 END IF;
740 IF p_lgcy_adstlvl_rec.exemption_institution_cd IS NULL THEN
741 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_ADLVL_EX_INS_CD_NULL');
742 FND_MSG_PUB.ADD;
743 x_return_status := FALSE;
744 END IF;
745 IF p_lgcy_adstlvl_rec.unit_level IS NULL THEN
746 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_ADLVL_UNIT_LVL_NULL');
747 FND_MSG_PUB.ADD;
748 x_return_status := FALSE;
749 END IF;
750 IF p_lgcy_adstlvl_rec.s_adv_stnd_granting_status IS NULL THEN
751 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_ADLVL_GRNT_STAT_NULL');
752 FND_MSG_PUB.ADD;
753 x_return_status := FALSE;
754 END IF;
755 IF p_lgcy_adstlvl_rec.credit_points IS NULL THEN
756 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_ADLVL_CRD_PNTS_NULL');
757 FND_MSG_PUB.ADD;
758 x_return_status := FALSE;
759 END IF;
760 IF p_lgcy_adstlvl_rec.approved_dt IS NULL THEN
761 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_ADLVL_APPR_DT_NULL');
762 FND_MSG_PUB.ADD;
763 x_return_status := FALSE;
764 END IF;
765 IF p_lgcy_adstlvl_rec.authorising_person_number IS NULL THEN
766 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_ADLV_AUTH_PERNUM_NULL');
767 FND_MSG_PUB.ADD;
768 x_return_status := FALSE;
769 END IF;
770 /*
771 Validate that the record parameter qual_exam_level if NOT NULL then all
772 the releavnt should also be not null
773 */
774 IF p_lgcy_adstlvl_rec.qual_exam_level IS NOT NULL THEN
775 IF p_lgcy_adstlvl_rec.qual_subject_code IS NULL OR
776 p_lgcy_adstlvl_rec.qual_year IS NULL OR
777 p_lgcy_adstlvl_rec.qual_sitting IS NULL OR
778 p_lgcy_adstlvl_rec.qual_awarding_body IS NULL OR
779 p_lgcy_adstlvl_rec.approved_result IS NULL THEN
780 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_ADLV_QUL_DET_NOT_NULL');
781 FND_MSG_PUB.ADD;
782 x_return_status := FALSE;
783 END IF;
784 END IF;
785 /*
786 Validate that the record parameter prev_unit_cd if NOT NULL then the
787 field prev_term should also be not null
788 */
789 IF p_lgcy_adstlvl_rec.prev_unit_cd IS NOT NULL THEN
790 IF p_lgcy_adstlvl_rec.prev_term IS NULL OR
791 p_lgcy_adstlvl_rec.start_date IS NULL OR
792 p_lgcy_adstlvl_rec.end_date IS NULL OR
793 p_lgcy_adstlvl_rec.institution_cd IS NULL THEN
794 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_PREV_UNT_DET_NOT_NULL');
795 FND_MSG_PUB.ADD;
796 x_return_status := FALSE;
797 END IF;
798 END IF;
799 /*
800 Validate that the record parameter TST_ADMISSION_TEST_TYPE if NOT NULL
801 then the fields TST_TEST_DATE and TEST_SEGMENT_NAME should also be not null
802 */
803 IF p_lgcy_adstlvl_rec.tst_admission_test_type IS NOT NULL THEN
804 IF p_lgcy_adstlvl_rec.tst_test_date IS NULL OR
805 p_lgcy_adstlvl_rec.test_segment_name IS NULL THEN
806 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_TST_ADM_DET_NOT_NULL');
807 FND_MSG_PUB.ADD;
808 x_return_status := FALSE;
809 END IF;
810 END IF;
811 /*
812 Validate that if s_adv_stnd_granting_status is granted then granting date cannot be not null.
813 if s_adv_stnd_granting_status is cancelled then cancelled date cannot be not null.
814 if s_adv_stnd_granting_status is revoked then revoked date cannot be not null.
815 */
816 IF p_lgcy_adstlvl_rec.s_adv_stnd_granting_status = 'GRANTED' AND
817 p_lgcy_adstlvl_rec.granted_dt IS NULL THEN
818 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_GRANTDT_NOT_NULL');
819 FND_MSG_PUB.ADD;
820 x_return_status := FALSE;
821 END IF;
822
823 IF p_lgcy_adstlvl_rec.s_adv_stnd_granting_status = 'CANCELLED' AND
824 p_lgcy_adstlvl_rec.cancelled_dt IS NULL THEN
825 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_CANCDT_NOT_NULL');
826 FND_MSG_PUB.ADD;
827 x_return_status := FALSE;
828 END IF;
829
830 IF p_lgcy_adstlvl_rec.s_adv_stnd_granting_status = 'REVOKED' AND
831 p_lgcy_adstlvl_rec.revoked_dt IS NULL THEN
832 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_REVDT_NOT_NULL');
833 FND_MSG_PUB.ADD;
834 x_return_status := FALSE;
835 END IF;
836
837 IF p_lgcy_adstlvl_rec.s_adv_stnd_granting_status = 'EXPIRED' AND
838 p_lgcy_adstlvl_rec.expiry_dt IS NULL THEN
839 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_EXPDT_TOBE_SET');
840 FND_MSG_PUB.ADD;
841 x_return_status := FALSE;
842 END IF;
843
844 /*
845 validate that when advanced standing granting status if granted -> revoked and cancelled dates are null
846 when advanced standing granting status if revoked then granted and cancelled dates are null
847 when advanced standing granting status if cancelled then revoked and granted dates are null
848 */
849 IF p_lgcy_adstlvl_rec.s_adv_stnd_granting_status = 'GRANTED' AND
850 (p_lgcy_adstlvl_rec.revoked_dt IS NOT NULL OR
851 p_lgcy_adstlvl_rec.cancelled_dt IS NOT NULL) THEN
852 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CORR_DT_STATUS');
853 FND_MSG_PUB.ADD;
854 x_return_status := FALSE;
855 END IF;
856
857 IF p_lgcy_adstlvl_rec.s_adv_stnd_granting_status = 'CANCELLED' AND
858 (p_lgcy_adstlvl_rec.revoked_dt IS NOT NULL OR
859 p_lgcy_adstlvl_rec.granted_dt IS NOT NULL) THEN
860 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CORR_DT_STATUS');
861 FND_MSG_PUB.ADD;
862 x_return_status := FALSE;
863 END IF;
864
865 IF p_lgcy_adstlvl_rec.s_adv_stnd_granting_status = 'REVOKED' AND
866 (p_lgcy_adstlvl_rec.granted_dt IS NOT NULL OR
867 p_lgcy_adstlvl_rec.cancelled_dt IS NOT NULL) THEN
868 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CORR_DT_STATUS');
869 FND_MSG_PUB.ADD;
870 x_return_status := FALSE;
871 END IF;
872
873 /*
874 return the value of x_return_status
875 */
876 return x_return_status;
877 END validate_parameters;
878
879
880 PROCEDURE derive_level_data
881 (
882 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type,
883 p_person_id OUT NOCOPY igs_pe_person.person_id%type,
884 p_s_adv_stnd_unit_level OUT NOCOPY igs_av_stnd_unit_lvl.s_adv_stnd_type%type,
885 p_cal_type OUT NOCOPY igs_ca_inst.cal_type%type,
886 p_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%type,
887 p_auth_pers_id OUT NOCOPY igs_pe_person.person_id%type,
888 p_unit_details_id OUT NOCOPY igs_ad_term_unitdtls.unit_details_id%type,
889 p_tst_rslt_dtls_id OUT NOCOPY igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
890 p_qual_dets_id OUT NOCOPY igs_uc_qual_dets.qual_dets_id%type,
891 p_as_version_number OUT NOCOPY igs_en_stdnt_ps_att.version_number%type
892 )
893 IS
894 /*****************************************************************************************************************
895 Created By : smanglm
896 Date Created on : 2002/11/13
897 Purpose :
898 derive_level_data procedure derives advanced standing unit level data like: -
899 1. Derive Person_id from person_number .
900 2. Derive cal_type and sequence_number from load_cal_alt_code
901 3. Set Unit_level parameter
902 4. Derive the authorizing_person_id from authorizing_person_number
903 5. Derive Unit_details_id , tst_rslt_dtls_id and qual_dets_id
904 Know limitations, enhancements or remarks
905 Change History
906 Who When What
907 (reverse chronological order - newest change first)
908 Aiyer 03-jan-2003 This procedure has been modified as a part of the fix for
909 the bug #2732975
910 Made the derivation of Qualification detail ID , Unit details ID ,
911 Test result Details ID conditional based on whether its corresponding fields have a
912 value specified for it.
913 ********************************************************************************************************************/
914 x_return_status BOOLEAN;
915 BEGIN
916 x_return_status := TRUE;
917 p_s_adv_stnd_unit_level := 'LEVEL';
918 /*
919 get person_id
920 */
921 p_person_id := IGS_GE_GEN_003.GET_PERSON_ID(p_lgcy_adstlvl_rec.person_number);
922 IF p_person_id IS NULL THEN
923 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
924 FND_MSG_PUB.ADD;
925 x_return_status := FALSE;
926 END IF;
927 /*
928 get cal_type and sequence_number
929 */
930 DECLARE
931 l_start_dt igs_ca_inst.start_dt%TYPE;
932 l_end_dt igs_ca_inst.end_dt%TYPE;
933 l_return_status VARCHAR2(2000);
934 BEGIN
935 IGS_GE_GEN_003.GET_CALENDAR_INSTANCE
936 (
937 P_ALTERNATE_CD => p_lgcy_adstlvl_rec.load_cal_alt_code,
938 P_S_CAL_CATEGORY => NULL,
939 P_CAL_TYPE => p_cal_type,
940 P_CI_SEQUENCE_NUMBER => p_sequence_number,
941 P_START_DT => l_start_dt,
942 P_END_DT => l_end_dt,
943 P_RETURN_STATUS => l_return_status
944 );
945 IF p_cal_type IS NULL THEN
946 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
947 FND_MSG_PUB.ADD;
948 x_return_status := FALSE;
949 END IF;
950 END;
951 /*
952 derive p_authorizing_person_id
953 */
954 p_auth_pers_id := IGS_GE_GEN_003.GET_PERSON_ID(p_lgcy_adstlvl_rec.authorising_person_number);
955
956 /*
957 derive p_qual_dets_id
958 Code modified for the bug #2732975.
959 Added the following If clauses before deriving the value for qual_dets_id
960 */
961
962 IF p_lgcy_adstlvl_rec.qual_exam_level IS NOT NULL THEN
963
964 DECLARE
965 -- cursor to get qual_dets_id
966 CURSOR c_qual_dets_id (cp_person_id igs_uc_qual_dets.person_id%TYPE,
967 cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
968 cp_subject_code igs_uc_qual_dets.subject_code%TYPE,
969 cp_year igs_uc_qual_dets.year%TYPE,
970 cp_sitting igs_uc_qual_dets.sitting%TYPE,
971 cp_awarding_body igs_uc_qual_dets.awarding_body%TYPE,
972 cp_approved_result igs_uc_qual_dets.approved_result%TYPE ) IS
973 SELECT qual_dets_id
974 FROM igs_uc_qual_dets
975 WHERE person_id = cp_person_id
976 AND exam_level = cp_exam_level
977 AND subject_code = cp_subject_code
978 AND year = cp_year
979 AND sitting = cp_sitting
980 AND awarding_body = cp_awarding_body
981 AND approved_result = cp_approved_result;
982 BEGIN
983 OPEN c_qual_dets_id (p_person_id,
984 p_lgcy_adstlvl_rec.qual_exam_level,
985 p_lgcy_adstlvl_rec.qual_subject_code,
986 p_lgcy_adstlvl_rec.qual_year,
987 p_lgcy_adstlvl_rec.qual_sitting,
988 p_lgcy_adstlvl_rec.qual_awarding_body,
989 p_lgcy_adstlvl_rec.approved_result);
990 FETCH c_qual_dets_id INTO p_qual_dets_id;
991
992 IF c_qual_dets_id%NOTFOUND THEN
993 FND_MESSAGE.SET_NAME('IGS','IGS_AV_NOPREV_EDU_QUAL_EXISTS');
994 FND_MSG_PUB.ADD;
995 x_return_status := FALSE;
996 END IF;
997 CLOSE c_qual_dets_id;
998 END;
999 END IF;
1000
1001 /*
1002 derive p_unit_details_id
1003 */
1004 /*
1005 Code modified for the bug #2732975.
1006 Added the following IF clauses before deriving the value for unit details ID
1007 */
1008 IF p_lgcy_adstlvl_rec.prev_unit_cd IS NOT NULL THEN
1009 DECLARE
1010 CURSOR c_unit_details_id (cp_unit igs_ad_term_unitdtls.unit%TYPE,
1011 cp_prev_term igs_av_lgcy_lvl_int.prev_term%TYPE,
1012 cp_start_date igs_av_lgcy_lvl_int.start_date%TYPE,
1013 cp_end_date igs_av_lgcy_lvl_int.end_date%TYPE,
1014 cp_person_id igs_pe_person.person_id%TYPE,
1015 cp_inst_cd igs_av_acad_history_v.institution_code%TYPE) IS
1016 SELECT ahv.unit_details_id
1017 FROM igs_av_acad_history_v ahv,
1018 igs_ad_term_details td
1019 WHERE ahv.term_details_id = td.term_details_id
1020 AND ahv.term=td.term
1021 AND td.term = cp_prev_term
1022 AND td.start_date = cp_start_date
1023 AND td.end_date = cp_end_date
1024 AND ahv.unit = cp_unit
1025 AND ahv.person_id = cp_person_id
1026 AND ahv.institution_code = cp_inst_cd;
1027
1028 l_count NUMBER := 0;
1029 BEGIN
1030 OPEN c_unit_details_id (p_lgcy_adstlvl_rec.prev_unit_cd,
1031 p_lgcy_adstlvl_rec.prev_term,
1032 p_lgcy_adstlvl_rec.start_date,
1033 p_lgcy_adstlvl_rec.end_date,
1034 p_person_id,
1035 p_lgcy_adstlvl_rec.institution_cd);
1036 LOOP
1037 FETCH c_unit_details_id INTO p_unit_details_id;
1038 EXIT WHEN c_unit_details_id%NOTFOUND;
1039 l_count := c_unit_details_id%ROWCOUNT;
1040 END LOOP;
1041 CLOSE c_unit_details_id;
1042 -- set p_unit_details_id in case no data or too many rows
1043 IF l_count = 0 OR l_count >=2 THEN
1044 FND_MESSAGE.SET_NAME('IGS','IGS_AV_TERM_UNTDTLS_NOT_EXISTS');
1045 FND_MSG_PUB.ADD;
1046 x_return_status := FALSE;
1047 END IF;
1048 END;
1049 END IF;
1050 /*
1051 Derive p_tst_rslt_dtls_id
1052 Code modified for the bug #2732975.
1053 Added the following If clauses before deriving the value for tst_rslt_dtls_id
1054 */
1055
1056 IF p_lgcy_adstlvl_rec.tst_admission_test_type IS NOT NULL THEN
1057 DECLARE
1058 CURSOR c_tst_rslt_dtls_id (cp_admission_test_type igs_ad_test_results.admission_test_type%TYPE,
1059 cp_test_date igs_ad_test_results.test_date%TYPE,
1060 cp_test_segment_name igs_ad_test_segments.test_segment_name%TYPE,
1061 cp_person_id igs_ad_test_results.person_id%TYPE
1062 ) IS
1063 SELECT b.tst_rslt_dtls_id
1064 FROM igs_ad_test_results a,
1065 igs_ad_tst_rslt_dtls b,
1066 igs_ad_test_segments c
1067 WHERE a.test_results_id = b.test_results_id
1068 AND b.test_segment_id = c.test_segment_id
1069 AND c.admission_test_type = cp_admission_test_type
1070 AND a.admission_test_type = cp_admission_test_type
1071 AND a.test_date = cp_test_date
1072 AND c.test_segment_name = cp_test_segment_name
1073 AND a.person_id = cp_person_id;
1074 l_count NUMBER := 0;
1075 BEGIN
1076 OPEN c_tst_rslt_dtls_id ( p_lgcy_adstlvl_rec.tst_admission_test_type,
1077 p_lgcy_adstlvl_rec.tst_test_date,
1078 p_lgcy_adstlvl_rec.test_segment_name,
1079 p_person_id
1080 );
1081 LOOP
1082 FETCH c_tst_rslt_dtls_id INTO p_tst_rslt_dtls_id;
1083 EXIT WHEN c_tst_rslt_dtls_id%NOTFOUND;
1084 l_count := c_tst_rslt_dtls_id%ROWCOUNT;
1085 END LOOP;
1086 CLOSE c_tst_rslt_dtls_id;
1087 -- set p_unit_details_id in case no data or too many rows
1088 IF l_count = 0 OR l_count >=2 THEN
1089 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ADM_TST_RSLT_NOT_EXISTS');
1090 FND_MSG_PUB.ADD;
1091 x_return_status := FALSE;
1092 END IF;
1093 END;
1094 END IF;
1095
1096 /*
1097 derive p_as_version_number
1098 */
1099 p_as_version_number := IGS_GE_GEN_003.GET_PROGRAM_VERSION
1100 (
1101 p_person_id => p_person_id,
1102 p_program_cd => p_lgcy_adstlvl_rec.program_cd
1103 );
1104
1105
1106 /*
1107 check for x_return_status, in this way you can capture all
1108 failures at one go
1109 */
1110
1111 IF NOT x_return_status THEN
1112 RAISE FND_API.G_EXC_ERROR;
1113 END IF;
1114
1115 END derive_level_data;
1116
1117 FUNCTION validate_adv_std_db_cons
1118 (
1119 p_person_id IN igs_pe_person.person_id%type,
1120 p_version_number IN igs_ps_ver_all.version_number%type,
1121 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
1122 )
1123 RETURN BOOLEAN
1124 /*************************************************************
1125 Created By : smanglm
1126 Date Created on : 2002/11/13
1127 Purpose : validate_adv_std_db_cons function performs
1128 all the data integrity validation
1129 Know limitations, enhancements or remarks
1130 Change History
1131 Who When What
1132 (reverse chronological order - newest change first)
1133 ***************************************************************/
1134 IS
1135 x_return_status BOOLEAN;
1136 BEGIN
1137 x_return_status := TRUE;
1138 IF NOT IGS_PS_VER_PKG.GET_PK_FOR_VALIDATION
1139 (
1140 x_course_cd => p_lgcy_adstlvl_rec.program_cd,
1141 x_version_number => p_version_number
1142 ) THEN
1143 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_PRG_CD_NOT_EXISTS');
1144 FND_MSG_PUB.ADD;
1145 x_return_status := FALSE;
1146 END IF;
1147 return x_return_status;
1148 END validate_adv_std_db_cons;
1149
1150 FUNCTION validate_adv_stnd
1151 (
1152 p_person_id IN igs_pe_person.person_id%type,
1153 p_version_number IN igs_ps_ver.version_number%type,
1154 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
1155 )
1156 RETURN BOOLEAN
1157 /*************************************************************
1158 Created By : smanglm
1159 Date Created on : 2002/11/13
1160 Purpose : validate_adv_stnd function validates all the business
1161 rules before inserting a record in the table
1162 IGS_AV_ADV_STANDING_ALL
1163 Know limitations, enhancements or remarks
1164 Change History
1165 Who When What
1166 (reverse chronological order - newest change first)
1167 ***************************************************************/
1168 IS
1169 x_return_status BOOLEAN;
1170 BEGIN
1171 x_return_status := TRUE;
1172 /*
1173 check whether person is deceased or not
1174 */
1175 DECLARE
1176 CURSOR c_ind (cp_party_id igs_pe_hz_parties.party_id%TYPE) IS
1177 SELECT deceased_ind
1178 FROM igs_pe_hz_parties
1179 WHERE party_id = cp_party_id;
1180 l_ind igs_pe_hz_parties.deceased_ind%TYPE;
1181 BEGIN
1182 OPEN c_ind (p_person_id);
1183 FETCH c_ind INTO l_ind;
1184 CLOSE c_ind;
1185 IF upper(l_ind) = 'Y' THEN
1186 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_PERSON_DECEASED');
1187 FND_MSG_PUB.ADD;
1188 x_return_status := FALSE;
1189 END IF;
1190 END;
1191 /*
1192 check whether exemtion_inst_cd is valid or not
1193 */
1194 DECLARE
1195 CURSOR c_exists (cp_exemption_institution_cd igs_or_inst_exempt_v.exemption_institution_cd%TYPE) IS
1196 SELECT 'x'
1197 FROM hz_parties hp, igs_pe_hz_parties ihp
1198 WHERE hp.party_id = ihp.party_id
1199 AND ihp.inst_org_ind = 'I'
1200 AND ihp.oi_govt_institution_cd IS NOT NULL
1201 AND ihp.oi_institution_status = 'ACTIVE'
1202 AND ihp.oss_org_unit_cd = cp_exemption_institution_cd;
1203 l_exists VARCHAR2(1);
1204 BEGIN
1205 OPEN c_exists(p_lgcy_adstlvl_rec.exemption_institution_cd);
1206 FETCH c_exists INTO l_exists;
1207 IF c_exists%NOTFOUND THEN
1208 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_STND_EXMPT_INVALID');
1209 FND_MSG_PUB.ADD;
1210 x_return_status := FALSE;
1211 END IF;
1212 CLOSE c_exists;
1213 END;
1214 /*
1215 check whether program_cd is valid or not
1216 */
1217 DECLARE
1218 l_message_name VARCHAR2(2000);
1219 BEGIN
1220 IF NOT IGS_AV_VAL_AS.ADVP_VAL_AS_CRS
1221 (
1222 p_person_id => p_person_id,
1223 p_course_cd => p_lgcy_adstlvl_rec.program_cd,
1224 p_version_number => p_version_number,
1225 p_message_name => l_message_name
1226 ) THEN
1227 FND_MESSAGE.SET_NAME('IGS', 'IGS_HE_EXT_SPA_DTL_NOT_FOUND');
1228 FND_MSG_PUB.ADD;
1229 x_return_status := FALSE;
1230 END IF;
1231 END;
1232 /*
1233 validation for exemption credit points
1234 */
1235
1236 DECLARE
1237 CURSOR c_local_inst_ind (cp_ins_cd igs_or_institution.institution_cd%type) IS
1238 SELECT ins.local_institution_ind
1239 FROM igs_or_institution ins
1240 WHERE ins.institution_cd = cp_ins_cd;
1241
1242 CURSOR cur_program_exempt_totals (
1243 cp_course_cd igs_ps_ver.course_cd%type,
1244 cp_version_number igs_ps_ver.version_number%type,
1245 cp_local_ind VARCHAR2) IS
1246 SELECT DECODE (cp_local_ind, 'N', NVL (cv.external_adv_stnd_limit, -1),
1247 NVL (cv.internal_adv_stnd_limit, -1)) adv_stnd_limit
1248 FROM igs_ps_ver cv
1249 WHERE cv.course_cd = cp_course_cd
1250 AND cv.version_number = cp_version_number;
1251
1252 rec_cur_program_exempt_totals cur_program_exempt_totals%ROWTYPE;
1253 rec_local_inst_ind c_local_inst_ind%ROWTYPE;
1254 l_message_name fnd_new_messages.message_name%TYPE;
1255
1256 BEGIN
1257 OPEN c_local_inst_ind (p_lgcy_adstlvl_rec.exemption_institution_cd);
1258 FETCH c_local_inst_ind INTO rec_local_inst_ind;
1259 IF (c_local_inst_ind%NOTFOUND) THEN
1260 rec_local_inst_ind.local_institution_ind := 'N';
1261 END IF;
1262 CLOSE c_local_inst_ind;
1263 IF (rec_local_inst_ind.local_institution_ind = 'N') THEN
1264 l_message_name := 'IGS_AV_EXCEEDS_PRGVER_EXT_LMT';
1265 ELSE
1266 l_message_name := 'IGS_AV_EXCEEDS_PRGVER_INT_LMT';
1267 END IF;
1268
1269 OPEN cur_program_exempt_totals (
1270 p_lgcy_adstlvl_rec.program_cd,
1271 p_version_number,
1272 rec_local_inst_ind.local_institution_ind);
1273 FETCH cur_program_exempt_totals INTO rec_cur_program_exempt_totals;
1274 CLOSE cur_program_exempt_totals;
1275
1276 IF (rec_cur_program_exempt_totals.adv_stnd_limit <> -1) THEN
1277 IF p_lgcy_adstlvl_rec.total_exmptn_approved < 0 OR
1278 p_lgcy_adstlvl_rec.total_exmptn_approved > rec_cur_program_exempt_totals.adv_stnd_limit THEN
1279 FND_MESSAGE.SET_NAME('IGS',l_message_name);
1280 FND_MSG_PUB.ADD;
1281 x_return_status := FALSE;
1282 END IF;
1283 END IF;
1284
1285 IF (rec_cur_program_exempt_totals.adv_stnd_limit <> -1) THEN
1286 IF p_lgcy_adstlvl_rec.total_exmptn_granted < 0 OR
1287 p_lgcy_adstlvl_rec.total_exmptn_granted > rec_cur_program_exempt_totals.adv_stnd_limit THEN
1288 FND_MESSAGE.SET_NAME('IGS',l_message_name);
1289 FND_MSG_PUB.ADD;
1290 x_return_status := FALSE;
1291 END IF;
1292 END IF;
1293 END;
1294 /*
1295 check the course_attempt_status
1296 */
1297 DECLARE
1298 CURSOR c_exists (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
1299 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE ) IS
1300 SELECT 'x'
1301 FROM igs_en_stdnt_ps_att
1302 WHERE person_id = cp_person_id
1303 AND course_cd = cp_course_cd
1304 AND course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT','UNCONFIRM','DISCONTIN','COMPLETED');
1305 l_exists VARCHAR2(1);
1306 BEGIN
1307 OPEN c_exists (p_person_id,
1308 p_lgcy_adstlvl_rec.program_cd);
1309 FETCH c_exists INTO l_exists;
1310 IF c_exists%NOTFOUND THEN
1311 FND_MESSAGE.SET_NAME('IGS','IGS_AV_PRG_ATTMPT_INVALID');
1312 FND_MSG_PUB.ADD;
1313 x_return_status := FALSE;
1314 END IF;
1315 CLOSE c_exists;
1316 END;
1317
1318
1319 return x_return_status;
1320
1321 END validate_adv_stnd;
1322
1323
1324 FUNCTION validate_lvl_db_cons
1325 (
1326 p_person_id IN igs_pe_person.person_id%type,
1327 p_s_adv_stnd_unit_level IN igs_ps_unit_level.unit_level%type,
1328 p_cal_type IN igs_ca_inst.cal_type%type,
1329 p_seq_number IN igs_ca_inst.sequence_number%type,
1330 p_auth_pers_id IN igs_pe_person.person_id%type,
1331 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%type,
1332 p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
1333 p_qual_dets_id IN igs_uc_qual_dets.qual_dets_id%type,
1334 p_course_version IN igs_ps_ver.version_number%type,
1335 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type,
1336 p_av_stnd_unit_lvl_id OUT NOCOPY igs_av_std_ulvlbasis_all.av_stnd_unit_lvl_id%type
1337 )
1338 RETURN BOOLEAN
1339 /******************************************************************************************
1340 Created By : smanglm
1341 Date Created on : 2002/11/13
1342 Purpose : validate_lvl_db_cons function performs all the data
1343 integrity validation before entering into the table
1344 IGS_AV_STND_UNIT_LVL_ALL
1345 Know limitations, enhancements or remarks
1346 Change History
1347 Who When What
1348 (reverse chronological order - newest change first)
1349 Aiyer 03-jan-2003 This Function has been modified as per the bug #2732975
1350 Changed the if conditition i.e if both institution code and
1351 tst_rslt_dtls_id are not nulls then show the error message
1352 IGS_AV_INST_RLID_BOTH_NOT_NULL.
1353 Also removed the foreign key condition check for qualification details ID,
1354 Unit Details ID ,Test Result details ID. Instead these have been added in the procedure
1355 derive_level_data
1356 Lkaki 22-Mar-2005 The range while checking the credit points entered is changed from 100 to 999
1357 as part of the bug : 4253919
1358 ***********************************************************************************************/
1359 IS
1360 x_return_status BOOLEAN;
1361 BEGIN
1362 x_return_status := TRUE;
1363 /*
1364 primary key validation
1365 */
1366 SELECT IGS_AV_STND_UNIT_LVL_S.NEXTVAL INTO p_av_stnd_unit_lvl_id FROM dual;
1367 IF IGS_AV_STND_UNIT_LVL_PKG.GET_PK_FOR_VALIDATION (p_av_stnd_unit_lvl_id) THEN
1368 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ADV_STND_ALREADY_EXISTS');
1369 FND_MSG_PUB.ADD;
1370 p_av_stnd_unit_lvl_id := NULL;
1371 x_return_status := FALSE;
1372 END IF;
1373
1374 /*
1375 Foreign Key with Table IGS_AV_ADV_STANDING_PKG
1376 */
1377 IF NOT IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION
1378 (
1379 x_person_id => p_person_id,
1380 x_course_cd => p_lgcy_adstlvl_rec.program_cd,
1381 x_version_number => p_course_version,
1382 x_exemption_institution_cd => p_lgcy_adstlvl_rec.exemption_institution_cd
1383 ) THEN
1384 FND_MESSAGE.SET_NAME('IGS','IGS_AV_NO_ADV_STND_DET_EXIST');
1385 FND_MSG_PUB.ADD;
1386 x_return_status := FALSE;
1387 END IF;
1388 /*
1389 Foreign Key with AUTHORIZING_PERSON_ID exists in table IGS_PE_PERSON
1390 */
1391 IF p_auth_pers_id IS NULL THEN
1392 FND_MESSAGE.SET_NAME('IGS','IGS_AV_INVALID_PERS_AUTH_NUM');
1393 FND_MSG_PUB.ADD;
1394 x_return_status := FALSE;
1395 END IF;
1396 /*
1397 Valid s_adv_granting_status exists
1398 */
1399 IF NOT IGS_LOOKUPS_VIEW_PKG.GET_PK_FOR_VALIDATION
1400 (
1401 x_lookup_type => 'ADV_STND_GRANTING_STATUS',
1402 x_lookup_code => p_lgcy_adstlvl_rec.s_adv_stnd_granting_status
1403 ) THEN
1404 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CANNOT_DTR_GRNT_STAT');
1405 FND_MSG_PUB.ADD;
1406 x_return_status := FALSE;
1407 END IF;
1408 /*
1409 Foreign Key with Table IGS_PS_UNIT_LEVEL
1410 */
1411 IF NOT IGS_PS_UNIT_LEVEL_PKG.GET_PK_FOR_VALIDATION
1412 (
1413 x_unit_level => p_s_adv_stnd_unit_level
1414 ) THEN
1415 FND_MESSAGE.SET_NAME('IGS','IGS_AV_TYPE_MUSTBE_LEVEL');
1416 FND_MSG_PUB.ADD;
1417 x_return_status := FALSE;
1418 END IF;
1419 /*
1420 Credit points between 0 and 999
1421 */
1422 BEGIN
1423 IF to_number(p_lgcy_adstlvl_rec.credit_points) < 0 OR
1424 to_number(p_lgcy_adstlvl_rec.credit_points) > 999 THEN
1425 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CRD_POINTS_BET_0_99');
1426 FND_MSG_PUB.ADD;
1427 x_return_status := FALSE;
1428 END IF;
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CRD_POINTS_BET_0_99');
1432 FND_MSG_PUB.ADD;
1433 x_return_status := FALSE;
1434 END;
1435 /*
1436 program_group_ind should be Y or N
1437 */
1438 IF p_lgcy_adstlvl_rec.prog_group_ind NOT IN ('Y','N') THEN
1439 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CRS_GRP_IN_Y_N');
1440 FND_MSG_PUB.ADD;
1441 x_return_status := FALSE;
1442 END IF;
1443 /*
1444 Validate that if institution_cd is not null then one of
1445 unit_details_id or QUAL_DETS_ID needs to have a not null value
1446 */
1447 IF p_lgcy_adstlvl_rec.institution_cd IS NOT NULL THEN
1448 IF p_unit_details_id IS NULL AND
1449 p_qual_dets_id IS NULL THEN
1450 FND_MESSAGE.SET_NAME('IGS','IGS_AV_UDID_QDID_CAN_NOT_NULL');
1451 FND_MSG_PUB.ADD;
1452 x_return_status := FALSE;
1453 END IF;
1454 END IF;
1455 /*
1456 Validate that if both institution_cd and tst_rslt_dtls_id
1457 are not nulls then raise an error message
1458 This code has been modfied as per the bug #2732975
1459 Changed condition from null to not null for both institution code and tst_rslt_dtls_id
1460 */
1461 IF p_lgcy_adstlvl_rec.institution_cd IS NOT NULL AND
1462 p_tst_rslt_dtls_id IS NOT NULL THEN
1463 FND_MESSAGE.SET_NAME('IGS','IGS_AV_INST_RLID_BOTH_NOT_NULL');
1464 FND_MSG_PUB.ADD;
1465 x_return_status := FALSE;
1466 END IF;
1467 /*
1468 Validate that One and only one of qualification details,
1469 unit details or test result details can be
1470 entered (all the three cannot be Not Nulls simultaneously)
1471 */
1472 IF NOT
1473 (
1474 ( p_unit_details_id IS NOT NULL AND
1475 p_tst_rslt_dtls_id IS NULL AND
1476 p_qual_dets_id IS NULL
1477 )
1478 OR
1479 ( p_unit_details_id IS NULL AND
1480 p_tst_rslt_dtls_id IS NOT NULL AND
1481 p_qual_dets_id IS NULL
1482 )
1483 OR
1484 ( p_unit_details_id IS NULL AND
1485 p_tst_rslt_dtls_id IS NULL AND
1486 p_qual_dets_id IS NOT NULL
1487 )
1488 ) THEN
1489 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ATLEAST_ONE_NOT_NULL');
1490 FND_MSG_PUB.ADD;
1491 x_return_status := FALSE;
1492 END IF;
1493 /*
1494 Validate that valid values for the record parameter
1495 s_adv_stnd_granting_status are in
1496 'CANCELLED','GRANTED','APPROVED','EXPIRED','REVOKED'
1497 */
1498 IF p_lgcy_adstlvl_rec.s_adv_stnd_granting_status NOT IN
1499 ('CANCELLED','GRANTED','APPROVED','EXPIRED','REVOKED') THEN
1500 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CANNOT_DTR_GRNT_STAT');
1501 FND_MSG_PUB.ADD;
1502 x_return_status := FALSE;
1503 END IF;
1504
1505 /*
1506 Unit level marks between 0 and 100
1507 */
1508 BEGIN
1509 IF to_number(p_lgcy_adstlvl_rec.unit_level_mark) < 0 OR
1510 to_number(p_lgcy_adstlvl_rec.unit_level_mark) > 100 THEN
1511 FND_MESSAGE.SET_NAME('IGS','IGS_GR_MARK_INV_0_100');
1512 FND_MSG_PUB.ADD;
1513 x_return_status := FALSE;
1514 END IF;
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517 FND_MESSAGE.SET_NAME('IGS','IGS_GR_MARK_INV_0_100');
1518 FND_MSG_PUB.ADD;
1519 x_return_status := FALSE;
1520 END;
1521
1522 return x_return_status;
1523
1524 END validate_lvl_db_cons;
1525
1526
1527 FUNCTION validate_level
1528 (
1529 p_person_id IN igs_pe_person.person_id%type,
1530 p_unit_level IN igs_ps_unit_level.unit_level%type,
1531 p_cal_type IN igs_ca_inst.cal_type%type,
1532 p_seq_number IN igs_ca_inst.sequence_number%type,
1533 p_auth_pers_id IN igs_pe_person.person_id%type,
1534 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%type,
1535 p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
1536 p_qual_dets_id IN igs_uc_qual_dets.qual_dets_id%type,
1537 p_course_version IN igs_ps_ver.version_number%type,
1538 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
1539 )
1540 RETURN BOOLEAN
1541 /*************************************************************
1542 Created By : smanglm
1543 Date Created on : 2002/11/13
1544 Purpose : validate_level function performs all the business
1545 validations before inserting a record into the table
1546 IGS_AV_STND_UNIT_LVL_ALL
1547 Know limitations, enhancements or remarks
1548 Change History
1549 Who When What
1550 (reverse chronological order - newest change first)
1551 ***************************************************************/
1552 IS
1553 x_return_status BOOLEAN;
1554 BEGIN
1555 x_return_status := TRUE;
1556 /*
1557 Validate that the approved date is greater than current date
1558 */
1559 IF p_lgcy_adstlvl_rec.approved_dt >= SYSDATE THEN
1560 FND_MESSAGE.SET_NAME('IGS','IGS_AV_APRVDT_LE_CURDT');
1561 FND_MSG_PUB.ADD;
1562 x_return_status := FALSE;
1563 END IF;
1564 /*
1565 Validate whether the granted date, cancelled date or
1566 revoked date are greater than or equal to the approved
1567 date for the same record
1568 */
1569 IF p_lgcy_adstlvl_rec.granted_dt <= p_lgcy_adstlvl_rec.approved_dt OR
1570 p_lgcy_adstlvl_rec.cancelled_dt <= p_lgcy_adstlvl_rec.approved_dt OR
1571 p_lgcy_adstlvl_rec.revoked_dt <= p_lgcy_adstlvl_rec.approved_dt THEN
1572 FND_MESSAGE.SET_NAME('IGS','IGS_AV_DTASSO_LE_APPRVDT');
1573 FND_MSG_PUB.ADD;
1574 x_return_status := FALSE;
1575 END IF;
1576 /*
1577 Validate whether auth person is staff or not
1578 */
1579 DECLARE
1580 l_message VARCHAR2(2000);
1581 BEGIN
1582 IF NOT IGS_AD_VAL_ACAI.GENP_VAL_STAFF_PRSN
1583 (
1584 p_person_id => p_auth_pers_id,
1585 p_message_name => l_message
1586 ) THEN
1587 FND_MESSAGE.SET_NAME('IGS','IGS_GE_NOT_STAFF_MEMBER');
1588 FND_MSG_PUB.ADD;
1589 x_return_status := FALSE;
1590 END IF;
1591 END;
1592 /*
1593 Validate inst cd
1594 */
1595 DECLARE
1596 l_message VARCHAR2(2000);
1597 BEGIN
1598 IF NOT IGS_AV_VAL_ASU.ADVP_VAL_ASU_INST
1599 (
1600 p_exempt_inst => p_lgcy_adstlvl_rec.exemption_institution_cd,
1601 p_message_name => l_message
1602 ) THEN
1603 FND_MESSAGE.SET_NAME('IGS','IGS_AV_INST_CODE_INVALID');
1604 FND_MSG_PUB.ADD;
1605 x_return_status := FALSE;
1606 END IF;
1607 END;
1608 /*
1609 Validate whether the advanced standing approved / granted has not
1610 exceeded the advanced standing internal or external limits of
1611 the Program version
1612 */
1613 DECLARE
1614 l_message VARCHAR2(2000);
1615 l_total_exmptn_approved NUMBER;
1616 l_total_exmptn_granted NUMBER;
1617 l_total_exmptn_perc_grntd NUMBER;
1618
1619 BEGIN
1620 IF NOT IGS_AV_VAL_ASU.ADVP_VAL_AS_TOTALS
1621 (
1622 p_person_id => p_person_id,
1623 p_course_cd => p_lgcy_adstlvl_rec.program_cd,
1624 p_version_number => p_course_version,
1625 p_include_approved => TRUE,
1626 p_asu_unit_cd => NULL,
1627 p_asu_version_number => NULL,
1628 p_asu_advstnd_granting_status => NULL,
1629 p_asul_unit_level => p_lgcy_adstlvl_rec.unit_level,
1630 p_asul_exmptn_institution_cd => p_lgcy_adstlvl_rec.exemption_institution_cd,
1631 p_asul_advstnd_granting_status => p_lgcy_adstlvl_rec.s_adv_stnd_granting_status,
1632 p_total_exmptn_approved => l_total_exmptn_approved,
1633 p_total_exmptn_granted => l_total_exmptn_granted,
1634 p_total_exmptn_perc_grntd => l_total_exmptn_perc_grntd,
1635 p_message_name => l_message,
1636 p_unit_details_id => p_unit_details_id,
1637 p_tst_rslt_dtls_id => p_tst_rslt_dtls_id
1638 ) THEN
1639 FND_MESSAGE.SET_NAME('IGS',l_message);
1640 FND_MSG_PUB.ADD;
1641 x_return_status := FALSE;
1642 END IF;
1643 END;
1644
1645 /*
1646 check for person hold
1647 */
1648 DECLARE
1649 l_message VARCHAR2(2000);
1650 BEGIN
1651 IF NOT IGS_EN_VAL_ENCMB.ENRP_VAL_EXCLD_PRSN
1652 (
1653 p_person_id => p_person_id,
1654 p_course_cd => p_lgcy_adstlvl_rec.program_cd,
1655 p_effective_dt => p_lgcy_adstlvl_rec.approved_dt,
1656 p_message_name => l_message
1657 ) THEN
1658 FND_MESSAGE.SET_NAME('IGS',l_message);
1659 FND_MSG_PUB.ADD;
1660 x_return_status := FALSE;
1661 END IF;
1662 END;
1663 /*
1664 Validate that the approved date is less than the expiry date
1665 */
1666 IF p_lgcy_adstlvl_rec.approved_dt >= p_lgcy_adstlvl_rec.expiry_dt THEN
1667 FND_MESSAGE.SET_NAME('IGS','IGS_AV_APRVDT_NOT_GT_EXPDT');
1668 FND_MSG_PUB.ADD;
1669 x_return_status := FALSE;
1670 END IF;
1671 /*
1672 check the course_attempt_status
1673 */
1674 DECLARE
1675 CURSOR c_exists (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
1676 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE ) IS
1677 SELECT 'x'
1678 FROM igs_en_stdnt_ps_att
1679 WHERE person_id = cp_person_id
1680 AND course_cd = cp_course_cd
1681 AND course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT','UNCONFIRM','DISCONTIN','COMPLETED');
1682 l_exists VARCHAR2(1);
1683 BEGIN
1684 OPEN c_exists (p_person_id,
1685 p_lgcy_adstlvl_rec.program_cd);
1686 FETCH c_exists INTO l_exists;
1687 IF c_exists%NOTFOUND THEN
1688 FND_MESSAGE.SET_NAME('IGS','IGS_AV_PRG_ATTMPT_INVALID');
1689 FND_MSG_PUB.ADD;
1690 x_return_status := FALSE;
1691 END IF;
1692 CLOSE c_exists;
1693 END;
1694 return x_return_status;
1695
1696 END validate_level;
1697
1698
1699 FUNCTION create_post_lvl
1700 (
1701 p_person_id IN igs_pe_person.person_id%type,
1702 p_course_version IN igs_ps_ver.version_number%type,
1703 p_unit_details_id IN igs_ad_term_unitdtls.unit_details_id%type,
1704 p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
1705 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
1706 )
1707 RETURN BOOLEAN
1708 /*************************************************************
1709 Created By : smanglm
1710 Date Created on : 2002/11/13
1711 Purpose : create_post_lvl function performs all the Post
1712 Insert business validations on the table
1713 IGS_AV_STND_UNIT_LVL_ALL
1714 Know limitations, enhancements or remarks
1715 Change History
1716 Who When What
1717 (reverse chronological order - newest change first)
1718 ***************************************************************/
1719 IS
1720 x_return_status BOOLEAN;
1721 l_message VARCHAR2(2000);
1722 l_total_exmptn_approved NUMBER;
1723 l_total_exmptn_granted NUMBER;
1724 l_total_exmptn_perc_grntd NUMBER;
1725
1726 BEGIN
1727 x_return_status := TRUE;
1728 /*
1729 Validate whether the advanced standing approved / granted has not
1730 exceeded the advanced standing internal or external limits of
1731 the Program version
1732 */
1733 IF NOT IGS_AV_VAL_ASU.ADVP_VAL_AS_TOTALS
1734 (
1735 p_person_id => p_person_id,
1736 p_course_cd => p_lgcy_adstlvl_rec.program_cd,
1737 p_version_number => p_course_version,
1738 p_include_approved => TRUE,
1739 p_asu_unit_cd => NULL,
1740 p_asu_version_number => NULL,
1741 p_asu_advstnd_granting_status => NULL,
1742 p_asul_unit_level => p_lgcy_adstlvl_rec.unit_level,
1743 p_asul_exmptn_institution_cd => p_lgcy_adstlvl_rec.exemption_institution_cd,
1744 p_asul_advstnd_granting_status => p_lgcy_adstlvl_rec.s_adv_stnd_granting_status,
1745 p_total_exmptn_approved => l_total_exmptn_approved,
1746 p_total_exmptn_granted => l_total_exmptn_granted,
1747 p_total_exmptn_perc_grntd => l_total_exmptn_perc_grntd,
1748 p_message_name => l_message,
1749 p_unit_details_id => p_unit_details_id,
1750 p_tst_rslt_dtls_id => p_tst_rslt_dtls_id,
1751 p_asu_exmptn_institution_cd => p_lgcy_adstlvl_rec.exemption_institution_cd
1752 ) THEN
1753 FND_MESSAGE.SET_NAME('IGS',l_message);
1754 FND_MSG_PUB.ADD;
1755 x_return_status := FALSE;
1756 END IF; -- function returns TRUE
1757 /*
1758 update IGS_AV_ADV_STANDING_ALL with above obtained values for
1759 total_exmptn_approved, total_exmptn_granted and total_exmptn_perc_grntd
1760 */
1761 IF x_return_status THEN
1762 UPDATE IGS_AV_ADV_STANDING_ALL
1763 SET TOTAL_EXMPTN_APPROVED = l_total_exmptn_approved,
1764 TOTAL_EXMPTN_GRANTED = l_total_exmptn_granted,
1765 TOTAL_EXMPTN_PERC_GRNTD = l_total_exmptn_perc_grntd
1766 WHERE PERSON_ID = p_person_id
1767 AND COURSE_CD = p_lgcy_adstlvl_rec.program_cd
1768 AND VERSION_NUMBER = p_course_version
1769 AND EXEMPTION_INSTITUTION_CD = p_lgcy_adstlvl_rec.exemption_institution_cd;
1770 END IF;
1771 return x_return_status;
1772 END create_post_lvl;
1773
1774
1775 FUNCTION validate_lvl_bas_db_cons
1776 (
1777 p_person_id IN igs_pe_person.person_id%type,
1778 p_av_stnd_unit_lvl_id IN igs_av_std_ulvlbasis_all.av_stnd_unit_lvl_id%type,
1779 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
1780 )
1781 RETURN VARCHAR2
1782 /*************************************************************
1783 Created By : smanglm
1784 Date Created on : 2002/11/13
1785 Purpose :
1786 validate_lvl_bas_db_cons function performs all the
1787 data integrity validation before inserting
1788 into the table IGS_AV_STD_ULVLBASIS_ALL
1789 Know limitations, enhancements or remarks
1790 Change History
1791 Who When What
1792 swaghmar 19-Oct-2005 Changed for bug# 4676359
1793 (reverse chronological order - newest change first)
1794 ***************************************************************/
1795 IS
1796 x_return_status VARCHAR2(1);
1797 BEGIN
1798 x_return_status := 'S';
1799 /*
1800 Primary key validation
1801 */
1802
1803 IF IGS_AV_STD_ULVLBASIS_PKG.GET_PK_FOR_VALIDATION
1804 (
1805 x_av_stnd_unit_lvl_id => p_av_stnd_unit_lvl_id
1806 ) THEN
1807 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ULVLBS_ALREADY_EXISTS');
1808 FND_MSG_PUB.ADD;
1809 x_return_status := 'W';
1810 return x_return_status;
1811 END IF;
1812 /*
1813 Foreign Key with IGS_AV_STND_UNIT_LVL_ALL
1814 */
1815 IF NOT IGS_AV_STND_UNIT_LVL_PKG.GET_PK_FOR_VALIDATION
1816 (
1817 x_av_stnd_unit_lvl_id => p_av_stnd_unit_lvl_id
1818 ) THEN
1819 FND_MESSAGE.SET_NAME('IGS','IGS_AV_NO_ADV_STND_DET_EXIST');
1820 FND_MSG_PUB.ADD;
1821 x_return_status := 'E';
1822 END IF;
1823 /*
1824 Foreign Key with IGS_PS_TYPE_ALL
1825 */
1826 IF ((p_lgcy_adstlvl_rec.basis_program_type IS NOT NULL) AND NOT IGS_PS_TYPE_PKG.GET_PK_FOR_VALIDATION(
1827 x_course_type => p_lgcy_adstlvl_rec.basis_program_type
1828 )) THEN
1829 FND_MESSAGE.SET_NAME('IGS','IGS_AV_BAS_CRS_TYP_FK_EXISTS');
1830 FND_MSG_PUB.ADD;
1831 x_return_status := 'E';
1832 END IF;
1833 /*
1834 Validate that the record parameter BASIS_YEAR
1835 is greater than 1900 and less than 2100
1836 */
1837 IF ((p_lgcy_adstlvl_rec.basis_year IS NOT NULL) AND ( p_lgcy_adstlvl_rec.basis_year <= 1900 OR
1838 p_lgcy_adstlvl_rec.basis_year >= 2100)) THEN
1839 FND_MESSAGE.SET_NAME('IGS','IGS_AV_BS_YR_BET_1900_2100');
1840 FND_MSG_PUB.ADD;
1841 x_return_status := 'E';
1842 END IF;
1843 /*
1844 Validate that the record parameter BASIS_COMPLETION_IND
1845 must be either 'Y' or 'N'
1846 */
1847 IF ((p_lgcy_adstlvl_rec.basis_completion_ind IS NOT NULL) AND (p_lgcy_adstlvl_rec.basis_completion_ind NOT IN ('Y','N'))) THEN
1848 FND_MESSAGE.SET_NAME('IGS','IGS_AV_BS_CMPL_IND_IN_Y_N');
1849 FND_MSG_PUB.ADD;
1850 x_return_status := 'E';
1851 END IF;
1852
1853 return x_return_status;
1854
1855 END validate_lvl_bas_db_cons;
1856
1857 FUNCTION validate_lvl_bas
1858 (
1859 p_course_version IN igs_ps_ver.version_number%type,
1860 p_lgcy_adstlvl_rec IN lgcy_adstlvl_rec_type
1861 )
1862 RETURN BOOLEAN
1863 /*************************************************************
1864 Created By : smanglm
1865 Date Created on : 2002/11/13
1866 Purpose :
1867 validate_lvl_bas function performs all the business
1868 validation before inserting into the table
1869 IGS_AV_STD_ULVLBASIS_ALL
1870 Know limitations, enhancements or remarks
1871 Change History
1872 Who When What
1873 (reverse chronological order - newest change first)
1874 ***************************************************************/
1875 IS
1876 x_return_status BOOLEAN;
1877 l_message_name VARCHAR2(2000);
1878 l_return_type VARCHAR2(2000);
1879
1880 BEGIN
1881 x_return_status := TRUE;
1882
1883 x_return_status := IGS_AV_VAL_ASULEB.ADVP_VAL_BASIS_YEAR
1884 (
1885 p_basis_year => p_lgcy_adstlvl_rec.basis_year,
1886 p_course_cd => p_lgcy_adstlvl_rec.program_cd,
1887 p_version_number => p_course_version,
1888 p_message_name => l_message_name,
1889 p_return_type => l_return_type
1890 );
1891 IF NOT x_return_status THEN
1892 FND_MESSAGE.SET_NAME('IGS', l_message_name);
1893 FND_MSG_PUB.ADD;
1894 END IF;
1895 return x_return_status;
1896 END validate_lvl_bas;
1897
1898 END igs_av_lvl_lgcy_pub;
[Home] [Help]