[Home] [Help]
PACKAGE BODY: APPS.IGS_AV_UNT_LGCY_PUB
Source
1 PACKAGE BODY igs_av_unt_lgcy_pub AS
2 /* $Header: IGSPAV2B.pls 120.7 2006/05/31 06:43:04 sepalani ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_AV_UNT_LGCY_PUB';
6
7 PROCEDURE mydebug (p_msg IN VARCHAR2)
8 -- this procedure will be used to debug
9 IS
10 BEGIN
11 null;
12 END mydebug;
13
14
15 FUNCTION validate_ref_code (
16 p_av_stnd_unit_id IN igs_av_stnd_unit_all.av_stnd_unit_id%TYPE,
17 p_reference_code_id IN igs_ge_ref_cd.reference_code_id%TYPE
18 )
19 RETURN BOOLEAN
20 IS
21 CURSOR c_untref_cd
22 IS
23 SELECT 1
24 FROM igs_av_unt_ref_cds
25 WHERE av_stnd_unit_id = p_av_stnd_unit_id
26 AND reference_code_id = p_reference_code_id;
27
28 x_return_status BOOLEAN := TRUE;
29 BEGIN
30
31 -- Primary key validation
32 OPEN c_untref_cd;
33
34 IF c_untref_cd%FOUND
35 THEN
36 fnd_message.set_name ('IGS', 'IGS_AV_UNT_REF_PK_EXISTS ');
37 fnd_msg_pub.ADD;
38 x_return_status := FALSE;
39 mydebug ('validate_unt_bss_db_cons IGS_AV_UNT_REF_PK_EXISTS ');
40 END IF;
41 CLOSE c_untref_cd;
42 RETURN x_return_status;
43 END validate_ref_code;
44
45
46 PROCEDURE initialise ( p_lgcy_adstunt_rec IN OUT NOCOPY lgcy_adstunt_rec_type )
47 IS
48 /*===========================================================================+
49 | PROCEDURE |
50 | initialise |
51 | |
52 | DESCRIPTION |
53 | Initialise the advanced standing lgcy_adstunt_rec_type record|
54 | |
55 | SCOPE - PUBLIC |
56 | |
57 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
58 | |
59 | ARGUMENTS : |
60 | IN/ OUT: p_lgcy_adstunt_rec |
61 | |
62 | RETURNS : NONE |
63 | |
64 | NOTES |
65 | |
66 | MODIFICATION HISTORY |
67 | jhanda 11-Nov-2002 Created |
68 | kdande 03-Jan-2002 Changed "IGS_AV_STUNT_INST_UID_NOT_NULL" to |
69 | IGS_AV_STUT_INST_UID_NOT_NULL |
70 | Changed "IGS_AV_LYENR_NOTGT_CURYR " to |
71 | "IGS_AV_LYENR_NOTGT_CURYR" |
72 | |
73 | nalkumar 10-Dec-2003 Bug# 3270446 RECR50 Build; Obsoleted the |
74 | IGS_AV_STND_UNIT.CREDIT_PERCENTAGE column. |
75 | sepalani 31-May-2006 Bug #5254238 IGSQUSRM:LEGACY LOAD AV STDNG IMPORTS |
76 | INVALID REFERENCE CODE/TYPE WITHOUT ERROR |
77 | |
78 +===========================================================================*/
79 BEGIN
80 p_lgcy_adstunt_rec.person_number := null;
81 p_lgcy_adstunt_rec.program_cd := null;
82 p_lgcy_adstunt_rec.total_exmptn_approved := null;
83 p_lgcy_adstunt_rec.total_exmptn_granted := null;
84 p_lgcy_adstunt_rec.total_exmptn_perc_grntd := null;
85 p_lgcy_adstunt_rec.exemption_institution_cd := null;
86 p_lgcy_adstunt_rec.unit_cd := null;
87 p_lgcy_adstunt_rec.version_number := null;
88 p_lgcy_adstunt_rec.approved_dt := null;
89 p_lgcy_adstunt_rec.authorising_person_number := null;
90 p_lgcy_adstunt_rec.prog_group_ind := null;
91 p_lgcy_adstunt_rec.granted_dt := null;
92 p_lgcy_adstunt_rec.expiry_dt := null;
93 p_lgcy_adstunt_rec.cancelled_dt := null;
94 p_lgcy_adstunt_rec.revoked_dt := null;
95 p_lgcy_adstunt_rec.comments := null;
96 p_lgcy_adstunt_rec.credit_percentage := null;
97 p_lgcy_adstunt_rec.s_adv_stnd_granting_status := null;
98 p_lgcy_adstunt_rec.s_adv_stnd_recognition_type := null;
99 p_lgcy_adstunt_rec.load_cal_alt_code := null;
100 p_lgcy_adstunt_rec.grading_schema_cd := null;
101 p_lgcy_adstunt_rec.grd_sch_version_number := null;
102 p_lgcy_adstunt_rec.grade := null;
103 p_lgcy_adstunt_rec.achievable_credit_points := null;
104 p_lgcy_adstunt_rec.prev_unit_cd := null;
105 p_lgcy_adstunt_rec.prev_term := null;
106 p_lgcy_adstunt_rec.tst_admission_test_type := null;
107 p_lgcy_adstunt_rec.tst_test_date := null;
108 p_lgcy_adstunt_rec.test_segment_name := null;
109 p_lgcy_adstunt_rec.alt_unit_cd := null;
110 p_lgcy_adstunt_rec.alt_version_number := null;
111 p_lgcy_adstunt_rec.optional_ind := null;
112 p_lgcy_adstunt_rec.basis_program_type := null;
113 p_lgcy_adstunt_rec.basis_year := null;
114 p_lgcy_adstunt_rec.basis_completion_ind := null;
115 p_lgcy_adstunt_rec.reference_cd_type := null;
116 p_lgcy_adstunt_rec.reference_cd := null;
117 p_lgcy_adstunt_rec.applied_program_cd := null;
118 END initialise;
119
120
121 FUNCTION validate_parameters(
122 p_lgcy_adstunt_rec IN LGCY_ADSTUNT_REC_TYPE
123 )RETURN BOOLEAN IS
124 /*===========================================================================+
125 | FUNCTION |
126 | validate_parameters |
127 | |
128 | DESCRIPTION |
129 | This function checks all the mandatory parameters for the |
130 | passed record type are not null ,and adds error messages to|
131 | the stack for all the parameters. |
132 | |
133 | SCOPE - PRIVATE |
134 | |
135 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
136 | |
137 | ARGUMENTS : IN: |
138 | p_person_id |
139 | p_lgcy_adstunt_rec |
140 | RETURNS : x_return_value |
141 | |
142 | NOTES |
143 | |
144 | MODIFICATION HISTORY |
145 | jhanda 11-11-2002 Created |
146 +===========================================================================*/
147 l_b_return_val BOOLEAN DEFAULT TRUE;
148 l_s_message_name VARCHAR2(30);
149 BEGIN
150
151 mydebug('Inside validate_parameters');
152
153 IF p_lgcy_adstunt_rec.person_number IS NULL THEN
154 l_s_message_name := 'IGS_EN_PER_NUM_NULL';
155 l_b_return_val :=FALSE;
156 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
157 FND_MSG_PUB.ADD;
158 END IF;
159 IF p_lgcy_adstunt_rec.program_cd IS NULL THEN
160 l_s_message_name := 'IGS_EN_PRGM_CD_NULL';
161 l_b_return_val :=FALSE;
162 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
163 FND_MSG_PUB.ADD;
164 END IF;
165 IF p_lgcy_adstunt_rec.total_exmptn_approved IS NULL THEN
166 l_s_message_name := 'IGS_AV_TOT_EXMPT_APPR_NULL';
167 l_b_return_val :=FALSE;
168 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
169 FND_MSG_PUB.ADD;
170 END IF;
171 IF p_lgcy_adstunt_rec.total_exmptn_granted IS NULL THEN
172 l_s_message_name := 'IGS_AV_TOT_EXMPT_GRNT_NULL';
173 l_b_return_val :=FALSE;
174 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
175 FND_MSG_PUB.ADD;
176 END IF;
177 IF p_lgcy_adstunt_rec.total_exmptn_perc_grntd IS NULL THEN
178 l_s_message_name := 'IGS_AV_TOT_EXT_PER_GRNT_NULL';
179 l_b_return_val :=FALSE;
180 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
181 FND_MSG_PUB.ADD;
182 END IF;
183 IF p_lgcy_adstunt_rec.exemption_institution_cd IS NULL THEN
184 l_s_message_name := 'IGS_AV_ADLVL_EX_INS_CD_NULL';
185 l_b_return_val :=FALSE;
186 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
187 FND_MSG_PUB.ADD;
188 END IF;
189 IF p_lgcy_adstunt_rec.unit_cd IS NULL THEN
190 l_s_message_name := 'IGS_AV_UNIT_CD_NULL';
191 l_b_return_val :=FALSE;
192 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
193 FND_MSG_PUB.ADD;
194 END IF;
195 IF p_lgcy_adstunt_rec.version_number IS NULL THEN
196 l_s_message_name := 'IGS_AV_UNIT_VER_NULL';
197 l_b_return_val :=FALSE;
198 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
199 FND_MSG_PUB.ADD;
200 END IF;
201 IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status IS NULL THEN
202 l_s_message_name := 'IGS_AV_ADLVL_GRNT_STAT_NULL';
203 l_b_return_val :=FALSE;
204 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
205 FND_MSG_PUB.ADD;
206 END IF;
207 IF p_lgcy_adstunt_rec.approved_dt IS NULL THEN
208 l_s_message_name := 'IGS_AV_ADLVL_APPR_DT_NULL';
209 l_b_return_val :=FALSE;
210 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
211 FND_MSG_PUB.ADD;
212 END IF;
213 IF p_lgcy_adstunt_rec.authorising_person_number IS NULL THEN
214 l_s_message_name := 'IGS_AV_ADLV_AUTH_PERNUM_NULL';
215 l_b_return_val :=FALSE;
216 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
217 FND_MSG_PUB.ADD;
218 END IF;
219 IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type IS NULL THEN
220 l_s_message_name := 'IGS_AV_STUNT_RG_TYP_NOT_NULL';
221 l_b_return_val :=FALSE;
222 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
223 FND_MSG_PUB.ADD;
224 END IF;
225
226 IF p_lgcy_adstunt_rec.prev_unit_cd IS NOT NULL AND
227 (p_lgcy_adstunt_rec.prev_term IS NULL OR
228 p_lgcy_adstunt_rec.start_date IS NULL OR
229 p_lgcy_adstunt_rec.end_date IS NULL OR
230 p_lgcy_adstunt_rec.institution_cd IS NULL
231 )THEN
232 l_s_message_name := 'IGS_AV_PREV_UNT_DET_NOT_NULL';
233 l_b_return_val :=FALSE;
234 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
235 FND_MSG_PUB.ADD;
236 END IF;
237
238 IF p_lgcy_adstunt_rec.tst_admission_test_type IS NOT NULL AND
239 ( p_lgcy_adstunt_rec.tst_test_date IS NULL OR p_lgcy_adstunt_rec.test_segment_name IS NULL) THEN
240 l_s_message_name := 'IGS_AV_TST_ADM_DET_NOT_NULL';
241 l_b_return_val :=FALSE;
242 FND_MESSAGE.SET_NAME('IGS', l_s_message_name);
243 FND_MSG_PUB.ADD;
244 END IF;
245
246 IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type <> 'PRECLUSION' AND
247 (
248 p_lgcy_adstunt_rec.alt_unit_cd IS NOT NULL OR
249 p_lgcy_adstunt_rec.alt_version_number IS NOT NULL
250 )
251 THEN
252 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_CRD_ALT_BOTH_EXISTS');
253 FND_MSG_PUB.ADD;
254 l_b_return_val := FALSE;
255 END IF;
256
257 /*
258 validate that when advanced standing granting status if granted -> revoked and cancelled dates are null
259 when advanced standing granting status if revoked then granted and cancelled dates are null
260 when advanced standing granting status if cancelled then revoked and granted dates are null
261 */
262 IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'GRANTED' AND
263 (p_lgcy_adstunt_rec.revoked_dt IS NOT NULL OR
264 p_lgcy_adstunt_rec.cancelled_dt IS NOT NULL) THEN
265 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CORR_DT_STATUS');
266 FND_MSG_PUB.ADD;
267 l_b_return_val := FALSE;
268 END IF;
269
270 IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'CANCELLED' AND
271 (p_lgcy_adstunt_rec.revoked_dt IS NOT NULL OR
272 p_lgcy_adstunt_rec.granted_dt IS NOT NULL) THEN
273 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CORR_DT_STATUS');
274 FND_MSG_PUB.ADD;
275 l_b_return_val := FALSE;
276 END IF;
277
278 IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'REVOKED' AND
279 (p_lgcy_adstunt_rec.granted_dt IS NOT NULL OR
280 p_lgcy_adstunt_rec.cancelled_dt IS NOT NULL) THEN
281 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CORR_DT_STATUS');
282 FND_MSG_PUB.ADD;
283 l_b_return_val := FALSE;
284 END IF;
285 mydebug('Comming Out Of validate_parameters' || l_s_message_name );
286 RETURN l_b_return_val;
287 END validate_parameters;
288
289
290 FUNCTION derive_unit_data(
291 p_lgcy_adstunt_rec IN OUT NOCOPY lgcy_adstunt_rec_type,
292 p_person_id OUT NOCOPY igs_pe_person.person_id%TYPE,
293 p_s_adv_stnd_type OUT NOCOPY igs_av_stnd_unit_all. s_adv_stnd_type%TYPE,
294 p_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
295 p_seq_number OUT NOCOPY igs_ca_inst. sequence_number%TYPE,
296 p_auth_pers_id OUT NOCOPY igs_pe_person.person_id%TYPE,
297 p_unit_details_id OUT NOCOPY igs_ad_term_unitdtls.unit_details_id%TYPE,
298 p_tst_rslt_dtls_id OUT NOCOPY igs_ad_tst_rslt_dtls .tst_rslt_dtls_id%TYPE,
299 p_as_version_number OUT NOCOPY igs_en_stdnt_ps_att.version_number%TYPE,
300 p_reference_code_id OUT NOCOPY igs_ge_ref_cd.reference_code_id%TYPE
301 )RETURN BOOLEAN IS
302 /*===========================================================================+
303 | FUNCTION |
304 | derive_unit_data |
305 | |
306 | DESCRIPTION |
307 | This function derives advanced standing unit level data |
308 | |
309 | SCOPE - PRIVATE |
310 | |
311 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
312 | |
313 | ARGUMENTS : IN: |
314 | p_lgcy_adstunt_rec |
315 | OUT: |
316 | p_lgcy_adstunt_rec |
317 | p_person_id |
318 | p_s_adv_stnd_type |
319 | p_cal_type |
320 | p_seq_number |
321 | p_auth_pers_id |
322 | p_unit_details_id |
323 | p_tst_rslt_dtls_id |
324 | p_as_version_number |
325 | |
326 | |
327 | RETURNS : x_return_value |
328 | |
329 | NOTES |
330 | |
331 | MODIFICATION HISTORY |
332 | jhanda 11-11-2002 Created |
333 +===========================================================================*/
334 l_n_rec_count NUMBER :=0 ;
335
336 CURSOR c_unit_details_id ( cp_unit igs_ad_term_unitdtls.unit%TYPE,
337 cp_prev_term igs_av_lgcy_lvl_int.prev_term%TYPE,
338 cp_start_date igs_av_lgcy_lvl_int.start_date%TYPE,
339 cp_end_date igs_av_lgcy_lvl_int.end_date%TYPE,
340 cp_person_id igs_pe_person.person_id%TYPE,
341 cp_institution_code igs_av_acad_history_v.institution_code%TYPE
342 ) IS
343 SELECT ahv.unit_details_id
344 FROM igs_av_acad_history_v ahv,
345 igs_ad_term_details td
346 WHERE ahv.term_details_id = td.term_details_id
347 AND ahv.term=td.term
348 AND td.term = cp_prev_term
349 AND trunc(td.start_date) = cp_start_date
350 AND trunc(td.end_date) = cp_end_date
351 AND ahv.unit = cp_unit
352 AND ahv.person_id = cp_person_id
353 AND ahv.institution_code = cp_institution_code ;
354
355
356 CURSOR c_tst_rslt_dtls_id (cp_admission_test_type
357 igs_ad_test_results.admission_test_type%TYPE,
358 cp_test_date igs_ad_test_results.test_date%TYPE,
359 cp_test_segment_name
360 igs_ad_test_segments.test_segment_name%TYPE,
361 cp_person_id igs_ad_test_results.person_id%TYPE) IS
362 SELECT b.tst_rslt_dtls_id
363 FROM igs_ad_test_results a,
364 igs_ad_tst_rslt_dtls b,
365 igs_ad_test_segments c
366 WHERE a.test_results_id = b.test_results_id
367 AND c.admission_test_type = cp_admission_test_type
368 AND b.test_segment_id = c.test_segment_id
369 AND a.admission_test_type = cp_admission_test_type
370 AND a.test_date = cp_test_date
371 AND c.test_segment_name = cp_test_segment_name
372 AND a.person_id = cp_person_id;
373 CURSOR c_credit_points ( cp_unit_cd p_lgcy_adstunt_rec.UNIT_CD%TYPE , cp_version_number igs_en_stdnt_ps_att.version_number%TYPE) IS
374 SELECT nvl(achievable_credit_points ,enrolled_credit_points) credit_points
375 FROM igs_ps_unit_ver
376 WHERE unit_cd=cp_unit_cd and version_number = cp_version_number ;
377
378 CURSOR c_ref_id (p_reference_cd_type igs_ge_ref_cd.reference_cd_type%TYPE , p_reference_cd igs_ge_ref_cd.reference_cd%TYPE) IS
379 SELECT reference_code_id
380 FROM igs_ge_ref_cd
381 WHERE reference_cd_type = p_reference_cd_type
382 AND reference_cd = p_reference_cd;
383
384 l_reference_code_id igs_ge_ref_cd.reference_code_id%TYPE :=null;
385
386 l_count NUMBER := 0;
387 l_start_dt igs_ad_term_details.start_date%TYPE;
388 l_end_dt igs_ad_term_details.end_date%TYPE;
389 l_return_status VARCHAR2(1000);
390
391 BEGIN
392 p_s_adv_stnd_type := 'UNIT'; -- initialise
393 p_person_id := IGS_GE_GEN_003.get_person_id(p_lgcy_adstunt_rec.person_number );
394 mydebug('Got person ID as ' || p_person_id);
395 IF p_person_id IS NULL THEN
396 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
397 FND_MSG_PUB.ADD;
398 RETURN FALSE;
399 END IF;
400
401 IF p_lgcy_adstunt_rec.load_cal_alt_code IS NULL THEN
402 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
403 FND_MSG_PUB.ADD;
404 RETURN FALSE;
405 END IF;
406
407 mydebug('Calling IGS_GE_GEN_003.get_calendar_instance ' || p_cal_type || p_lgcy_adstunt_rec.load_cal_alt_code);
408 igs_ge_gen_003.get_calendar_instance(p_alternate_cd => p_lgcy_adstunt_rec.load_cal_alt_code ,
409 p_s_cal_category=>'''LOAD''',
410 p_cal_type => p_cal_type,
411 p_ci_sequence_number => p_seq_number ,
412 p_start_dt => l_start_dt ,
413 p_end_dt => l_end_dt ,
414 p_return_status => l_return_status );
415 mydebug('Got p_cal_type as ' || p_cal_type || ' and p_seq_number as' || p_seq_number);
416 -- IF 0 or more load calendars are found
417 IF p_seq_number IS NULL OR p_cal_type IS NULL THEN
418 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
419 FND_MSG_PUB.ADD;
420 RETURN FALSE;
421 END IF;
422
423 p_auth_pers_id := igs_ge_gen_003.get_person_id(p_lgcy_adstunt_rec.authorising_person_number );
424
425 mydebug('Got p_auth_pers_id as ' || p_auth_pers_id);
426 OPEN c_unit_details_id(p_lgcy_adstunt_rec.PREV_UNIT_CD ,p_lgcy_adstunt_rec.prev_term ,
427 p_lgcy_adstunt_rec.start_date , p_lgcy_adstunt_rec.end_date ,
428 p_person_id , p_lgcy_adstunt_rec.institution_cd );
429 LOOP
430 FETCH c_unit_details_id INTO p_unit_details_id;
431 EXIT WHEN c_unit_details_id%NOTFOUND;
432 l_n_rec_count := c_unit_details_id%ROWCOUNT;
433 END LOOP;
434 CLOSE c_unit_details_id;
435 mydebug('******Got p_unit_details_id as ' || p_unit_details_id);
436
437 IF l_n_rec_count = 0 OR l_count >=2 THEN
438 p_unit_details_id := NULL;
439 END IF;
440 mydebug('Got p_unit_details_id as ' || p_unit_details_id);
441
442 OPEN c_tst_rslt_dtls_id(p_lgcy_adstunt_rec.tst_admission_test_type,
443 p_lgcy_adstunt_rec.tst_test_date,
444 p_lgcy_adstunt_rec.test_segment_name,
445 p_person_id);
446 LOOP
447 FETCH c_tst_rslt_dtls_id INTO p_tst_rslt_dtls_id;
448 EXIT WHEN c_tst_rslt_dtls_id%NOTFOUND;
449 l_n_rec_count := c_tst_rslt_dtls_id%ROWCOUNT;
450 END LOOP;
451 CLOSE c_tst_rslt_dtls_id;
452 mydebug('Got p_tst_rslt_dtls_id as ' || p_tst_rslt_dtls_id);
453
454 -- set p_unit_details_id in case no data or too many rows
455 IF l_n_rec_count = 0 OR l_count >=2 THEN
456 p_tst_rslt_dtls_id := NULL;
457 END IF;
458 -- Get the program version number
459 p_as_version_number := igs_ge_gen_003.get_program_version( p_person_id => p_person_id , p_program_cd => p_lgcy_adstunt_rec.program_cd );
460 mydebug('Got p_as_version_number as ' || p_as_version_number);
461
462 -- Default p_lgcy_adstunt_rec.achievable_credit_points
463 IF p_lgcy_adstunt_rec.achievable_credit_points IS NULL THEN
464 OPEN c_credit_points(p_lgcy_adstunt_rec.unit_cd , p_lgcy_adstunt_rec.version_number);
465 FETCH c_credit_points INTO p_lgcy_adstunt_rec.achievable_credit_points;
466 CLOSE c_credit_points;
467 END IF;
468 mydebug('Got p_achievable_credit_points as ' || p_lgcy_adstunt_rec.achievable_credit_points);
469
470 -- calculate the value for p_ reference_code_id as
471
472 OPEN c_ref_id(p_lgcy_adstunt_rec.reference_cd_type,
473 p_lgcy_adstunt_rec.reference_cd);
474 LOOP
475 FETCH c_ref_id INTO l_reference_code_id ;
476 EXIT WHEN c_ref_id%NOTFOUND;
477 l_n_rec_count := c_ref_id%ROWCOUNT;
478 END LOOP;
479 CLOSE c_ref_id;
480
481 IF p_lgcy_adstunt_rec.reference_cd_type IS NOT NULL AND l_reference_code_id IS NULL THEN
482 FND_MESSAGE.SET_NAME('IGS', 'IGS_PS_NO_OPEN_REFCDTYPE_EXIS');
483 FND_MSG_PUB.ADD;
484 RETURN FALSE;
485 END IF;
486 p_reference_code_id :=l_reference_code_id;
487 mydebug('Got p_reference_code_id as ' || l_reference_code_id);
488
489 RETURN TRUE;
490 END derive_unit_data;
491
492
493 FUNCTION validate_unit_basis(
494 p_person_id IN igs_pe_person.person_id%TYPE,
495 p_version_number IN igs_ps_ver_all.version_number%TYPE,
496 p_lgcy_adstunt_rec IN lgcy_adstunt_rec_type
497 )RETURN BOOLEAN IS
498 /*===========================================================================+
499 | FUNCTION |
500 | validate_unit_basis |
501 | |
502 | DESCRIPTION |
503 | |
504 | SCOPE - PRIVATE |
505 | |
506 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
507 | |
508 | ARGUMENTS : IN: |
509 | p_person_id |
510 | p_version_number |
511 | p_lgcy_adstunt_rec |
512 | RETURNS : x_return_value |
513 | |
514 | NOTES |
515 | |
516 | MODIFICATION HISTORY |
517 | jhanda 11-11-2002 Created |
518 +===========================================================================*/
519 l_b_return_val BOOLEAN:=TRUE;
520 l_message_name VARCHAR2(30);
521 l_return_type VARCHAR2(100);
522 BEGIN
523 -- Validate that the value in the BASIS_YEAR is not more than the current year
524 IF NOT igs_av_val_asuleb.advp_val_basis_year(
525 p_basis_year => p_lgcy_adstunt_rec.basis_year,
526 p_course_cd => p_lgcy_adstunt_rec.program_cd,
527 p_version_number => p_version_number,
528 p_message_name => l_message_name,
529 p_return_type => l_return_type
530 ) THEN
531 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_LYENR_NOTGT_CURYR');
532 FND_MSG_PUB.ADD;
533 l_b_return_val:=FALSE;
534 END IF;
535
536 RETURN l_b_return_val;
537 END validate_unit_basis;
538
539 FUNCTION validate_adv_std_db_cons(
540 p_version_number IN igs_ps_ver_all.version_number%TYPE,
541 p_lgcy_adstunt_rec IN lgcy_adstunt_rec_type
542 )RETURN BOOLEAN IS
543 /*===========================================================================+
544 | FUNCTION |
545 | validate_adv_std_db_cons |
546 | |
547 | DESCRIPTION |
548 | |
549 | SCOPE - PRIVATE |
550 | |
551 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
552 | |
553 | ARGUMENTS : IN: |
554 | p_person_id |
555 | p_version_number |
556 | p_lgcy_adstunt_rec |
557 | RETURNS : x_return_value |
558 | |
559 | NOTES |
560 | |
561 | MODIFICATION HISTORY |
562 | jhanda 11-11-2002 Created |
563 +===========================================================================*/
564 x_return_status BOOLEAN :=TRUE;
565 BEGIN
566 x_return_status := TRUE;
567 mydebug('Before igs_ps_ver_pkg.get_pk_for_validation ' );
568 IF NOT igs_ps_ver_pkg.get_pk_for_validation
569 (
570 x_course_cd => p_lgcy_adstunt_rec.program_cd,
571 x_version_number => p_version_number
572 ) THEN
573 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_PRG_CD_NOT_EXISTS');
574 FND_MSG_PUB.ADD;
575 x_return_status := FALSE;
576 END IF;
577 mydebug('Inside validate_adv_std_db_cons Got x_return_status as ' );
578 return x_return_status;
579 END validate_adv_std_db_cons;
580
581 FUNCTION validate_adv_stnd(
582 p_person_id IN igs_pe_person.person_id%TYPE,
583 p_version_number IN igs_ps_ver_all.version_number%TYPE,
584 p_lgcy_adstunt_rec IN lgcy_adstunt_rec_type
585 )RETURN BOOLEAN IS
586 /*===========================================================================+
587 | FUNCTION |
588 | validate_adv_stnd |
589 | |
590 | DESCRIPTION |
591 | |
592 | SCOPE - PRIVATE |
593 | |
594 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
595 | |
596 | ARGUMENTS : IN: |
597 | p_person_id |
598 | p_version_number |
599 | p_lgcy_adstunt_rec |
600 | RETURNS : x_return_value |
601 | |
602 | NOTES |
603 | |
604 | MODIFICATION HISTORY |
605 | jhanda 11-11-2002 Created |
606 +===========================================================================*/
607 x_return_status BOOLEAN;
608 BEGIN
609 x_return_status := TRUE;
610 /*
611 check whether person is deceased or not
612 */
613 DECLARE
614 CURSOR c_ind (cp_party_id igs_pe_hz_parties.party_id%TYPE) IS
615 SELECT deceased_ind
616 FROM igs_pe_hz_parties
617 WHERE party_id = cp_party_id;
618 l_ind igs_pe_hz_parties.deceased_ind%TYPE;
619 BEGIN
620 OPEN c_ind (p_person_id);
621 FETCH c_ind INTO l_ind;
622 CLOSE c_ind;
623 IF upper(l_ind) = 'Y' THEN
624 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_PERSON_DECEASED');
625 FND_MSG_PUB.ADD;
626 x_return_status := FALSE;
627 END IF;
628 mydebug ('l_ind :'||l_ind);
629 END;
630 /*
631 check whether exemtion_inst_cd is valid or not
632 */
633 DECLARE
634 CURSOR c_validate_inst(cp_exemption_institution_cd p_lgcy_adstunt_rec.exemption_institution_cd%TYPE) IS
635 SELECT 'x'
636 FROM hz_parties hp, igs_pe_hz_parties ihp
637 WHERE hp.party_id = ihp.party_id
638 AND ihp.inst_org_ind = 'I'
639 AND ihp.oi_govt_institution_cd IS NOT NULL
640 AND ihp.oi_institution_status = 'ACTIVE'
641 AND ihp.oss_org_unit_cd = cp_exemption_institution_cd;
642 BEGIN
643 OPEN c_validate_inst(p_lgcy_adstunt_rec.exemption_institution_cd);
644 IF c_validate_inst%NOTFOUND THEN
645 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_STND_EXMPT_INVALID');
646 FND_MSG_PUB.ADD;
647 x_return_status := FALSE;
648 END IF;
649 CLOSE c_validate_inst;
650 mydebug (' exemption_inst_cd');
651 END;
652 /*
653 check whether program_cd is valid or not
654 */
655 DECLARE
656 l_message_name VARCHAR2(2000);
657 BEGIN
658 IF NOT igs_av_val_as.advp_val_as_crs
659 (
660 p_person_id => p_person_id,
661 p_course_cd => p_lgcy_adstunt_rec.program_cd,
662 p_version_number => p_version_number,
663 p_message_name => l_message_name
664 ) THEN
665 FND_MESSAGE.SET_NAME('IGS', 'IGS_HE_EXT_SPA_DTL_NOT_FOUND');
666 FND_MSG_PUB.ADD;
667 x_return_status := FALSE;
668 END IF;
669 END;
670 /*
671 validation for exemption credit points
672 */
673 DECLARE
674 CURSOR c_local_inst_ind (cp_ins_cd igs_or_institution.institution_cd%type) IS
675 SELECT ins.local_institution_ind
676 FROM igs_or_institution ins
677 WHERE ins.institution_cd = cp_ins_cd;
678 CURSOR cur_program_exempt_totals (
679 cp_course_cd igs_ps_ver.course_cd%type,
680 cp_version_number igs_ps_ver.version_number%type,
681 cp_local_ind VARCHAR2) IS
682 SELECT DECODE (cp_local_ind, 'N', NVL (cv.external_adv_stnd_limit, -1),
683 NVL (cv.internal_adv_stnd_limit, -1)) adv_stnd_limit
684 FROM igs_ps_ver cv
685 WHERE cv.course_cd = cp_course_cd
686 AND cv.version_number = cp_version_number;
687 rec_cur_program_exempt_totals cur_program_exempt_totals%ROWTYPE;
688 rec_local_inst_ind c_local_inst_ind%ROWTYPE;
689 l_message_name fnd_new_messages.message_name%TYPE;
690 BEGIN
691 OPEN c_local_inst_ind (p_lgcy_adstunt_rec.exemption_institution_cd);
692 FETCH c_local_inst_ind INTO rec_local_inst_ind;
693 IF (c_local_inst_ind%NOTFOUND) THEN
694 rec_local_inst_ind.local_institution_ind := 'N';
695 END IF;
696 CLOSE c_local_inst_ind;
697 IF (rec_local_inst_ind.local_institution_ind = 'N') THEN
698 l_message_name := 'IGS_AV_EXCEEDS_PRGVER_EXT_LMT';
699 ELSE
700 l_message_name := 'IGS_AV_EXCEEDS_PRGVER_INT_LMT';
701 END IF;
702 OPEN cur_program_exempt_totals (
703 p_lgcy_adstunt_rec.program_cd,
704 p_version_number,
705 rec_local_inst_ind.local_institution_ind);
706 FETCH cur_program_exempt_totals INTO rec_cur_program_exempt_totals;
707 CLOSE cur_program_exempt_totals;
708 IF (rec_cur_program_exempt_totals.adv_stnd_limit <> -1) THEN
709 IF p_lgcy_adstunt_rec.total_exmptn_approved < 0 OR
710 p_lgcy_adstunt_rec.total_exmptn_approved > rec_cur_program_exempt_totals.adv_stnd_limit THEN
711 FND_MESSAGE.SET_NAME('IGS',l_message_name);
712 FND_MSG_PUB.ADD;
713 x_return_status := FALSE;
714 END IF;
715 END IF;
716 IF (rec_cur_program_exempt_totals.adv_stnd_limit <> -1) THEN
717 IF p_lgcy_adstunt_rec.total_exmptn_granted < 0 OR
718 p_lgcy_adstunt_rec.total_exmptn_granted > rec_cur_program_exempt_totals.adv_stnd_limit THEN
719 FND_MESSAGE.SET_NAME('IGS',l_message_name);
720 FND_MSG_PUB.ADD;
721 x_return_status := FALSE;
722 END IF;
723 END IF;
724 END;
725 /*
726 check the course_attempt_status
727 */
728 DECLARE
729 CURSOR c_exists (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
730 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE ) IS
731 SELECT 'x'
732 FROM igs_en_stdnt_ps_att
733 WHERE person_id = cp_person_id
734 AND course_cd = cp_course_cd
735 AND course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT','UNCONFIRM','DISCONTIN','COMPLETED');
736 l_exists VARCHAR2(1);
737 BEGIN
738 OPEN c_exists (p_person_id,
739 p_lgcy_adstunt_rec.program_cd);
740 FETCH c_exists INTO l_exists;
741 IF c_exists%NOTFOUND THEN
742 FND_MESSAGE.SET_NAME('IGS','IGS_AV_PRG_ATTMPT_INVALID');
743 FND_MSG_PUB.ADD;
744 x_return_status := FALSE;
745 END IF;
746 CLOSE c_exists;
747 END;
748 return x_return_status;
749
750 END validate_adv_stnd;
751
752 FUNCTION validate_std_unt_db_cons(
753 p_lgcy_adstunt_rec IN lgcy_adstunt_rec_type,
754 p_person_id IN igs_pe_person.person_id%TYPE,
755 p_s_adv_stnd_type IN igs_av_stnd_unit_all. s_adv_stnd_type %TYPE,
756 p_cal_type IN igs_ca_inst.cal_type%TYPE,
757 p_seq_number IN igs_ca_inst. sequence_number%TYPE,
758 p_auth_pers_id IN igs_pe_person.person_id%TYPE,
759 p_unit_details_id IN igs_ad_term_unitdtls. unit_details_id%TYPE,
760 p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls .tst_rslt_dtls_id%TYPE,
761 p_as_version_number IN igs_en_stdnt_ps_att.version_number%TYPE,
762 p_av_stnd_unit_lvl_id OUT NOCOPY igs_av_stnd_unit_all.av_stnd_unit_id%TYPE
763 )RETURN BOOLEAN IS
764 /*===========================================================================+
765 | FUNCTION |
766 | validate_std_unt_db_cons |
767 | |
768 | DESCRIPTION |
769 | This function performs all the data integrity validation |
770 | before entering into the table IGS_AV_STND_UNIT_ ALL and |
771 | keeps adding error message to stack as an when it encounters.| |
772 | SCOPE - PRIVATE |
773 | |
774 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
775 | |
776 | ARGUMENTS : IN: |
777 | p_lgcy_adstunt_rec |
778 | OUT: p_person_id |
779 | p_s_adv_stnd_type |
780 | p_cal_type |
781 | p_seq_number |
782 | p_auth_pers_number |
783 | p_unit_details_id |
784 | p_tst_rslt_dtls_id |
785 | p_as_version_number |
786 | p_av_stnd_unit_lvl_id |
787 | |
788 | |
789 | RETURNS : x_return_value |
790 | |
791 | NOTES |
792 | |
793 | MODIFICATION HISTORY |
794 | jhanda 11-11-2002 Created |
795 +===========================================================================*/
796 x_return_status BOOLEAN := TRUE;
797 l_c_tmp_msg VARCHAR2(30);
798 l_av_stnd_unit_lvl_id igs_av_stnd_unit_all.av_stnd_unit_id%TYPE;
799 CURSOR c_igs_av_stnd_unit_seq IS
800 select igs_av_stnd_unit_s.nextval from dual ;
801
802 BEGIN
803
804 OPEN c_igs_av_stnd_unit_seq ;
805 FETCH c_igs_av_stnd_unit_seq INTO l_av_stnd_unit_lvl_id;
806 CLOSE c_igs_av_stnd_unit_seq;
807
808 mydebug('***** Got l_av_stnd_unit_lvl_id=' ||l_av_stnd_unit_lvl_id);
809 -- Primary key validation
810 IF igs_av_stnd_unit_pkg.get_pk_for_validation(x_av_stnd_unit_id => l_av_stnd_unit_lvl_id) THEN
811 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STDUNT_ALREADY_EXISTS');
812 FND_MSG_PUB.ADD;
813 x_return_status := FALSE;
814 mydebug('validate_std_unt_db_cons IGS_AV_STND_UNIT_PKG.GET_PK_FOR_VALIDATION ');
815 RETURN x_return_status;
816 ELSE
817 p_av_stnd_unit_lvl_id :=l_av_stnd_unit_lvl_id;
818 END IF;
819 mydebug('**p_av_stnd_unit_lvl_id=' || p_av_stnd_unit_lvl_id || 'l_av_stnd_unit_lvl_id=' ||l_av_stnd_unit_lvl_id);
820 -- Foreign Key with Table IGS_AD_TERM_UNITDTLS
821
822 IF p_unit_details_id IS NULL AND
823 p_lgcy_adstunt_rec.prev_unit_cd IS NOT NULL AND
824 p_lgcy_adstunt_rec.prev_term IS NOT NULL THEN
825 FND_MESSAGE.SET_NAME('IGS','IGS_AV_TERM_UNTDTLS_NOT_EXISTS');
826 FND_MSG_PUB.ADD;
827 x_return_status := FALSE;
828 mydebug('validate_std_unt_db_cons p_unit_details_id ');
829 END IF;
830 -- Foreign Key with Table IGS_AD_TST_RSLT_DTLS
831 IF p_tst_rslt_dtls_id IS NULL AND p_lgcy_adstunt_rec.tst_admission_test_type IS NOT NULL THEN
832 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ADM_TST_RSLT_NOT_EXISTS');
833 FND_MSG_PUB.ADD;
834 x_return_status := FALSE;
835 mydebug('validate_std_unt_db_cons p_tst_rslt_dtls_id ');
836 END IF;
837 -- Foreign Key with Table IGS_AV_ADV_STANDING_PKG
838 IF NOT igs_av_adv_standing_pkg.get_pk_for_validation(
839 x_person_id => p_person_id ,
840 x_course_cd => p_lgcy_adstunt_rec.program_cd,
841 x_version_number => p_as_version_number ,
842 x_exemption_institution_cd => p_lgcy_adstunt_rec.exemption_institution_cd
843 ) THEN
844 FND_MESSAGE.SET_NAME('IGS','IGS_AV_NO_ADV_STND_DET_EXIST');
845 FND_MSG_PUB.ADD;
846 x_return_status := FALSE;
847 mydebug('validate_std_unt_db_cons IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION ');
848 END IF;
849 -- Foreign Key with AUTHORIZING_PERSON_ID exists in table IGS_PE_PERSON
850 IF p_auth_pers_id IS NULL THEN
851 FND_MESSAGE.SET_NAME('IGS','IGS_AV_INVALID_PERS_AUTH_NUM');
852 FND_MSG_PUB.ADD;
853 x_return_status := FALSE;
854 mydebug('validate_std_unt_db_cons p_auth_pers_id ');
855 END IF;
856 -- Valid s_adv_granting_status exists
857 IF NOT igs_lookups_view_pkg.get_pk_for_validation(
858 x_lookup_type => 'ADV_STND_GRANTING_STATUS',
859 x_lookup_code => p_lgcy_adstunt_rec.s_adv_stnd_granting_status) THEN
860 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CANNOT_DTR_GRNT_STAT');
861 FND_MSG_PUB.ADD;
862 x_return_status := FALSE;
863 mydebug('validate_std_unt_db_cons IGS_LOOKUPS_VIEW_PKG.GET_PK_FOR_VALIDATION ');
864 END IF;
865
866 -- Foreign Key with Table IGS_PS_UNIT_VER
867 IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (
868 x_unit_cd => p_lgcy_adstunt_rec.unit_cd,
869 x_version_number => p_lgcy_adstunt_rec.version_number
870 ) THEN
871 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ADV_STUNT_UNIT_EXISTS');
872 FND_MSG_PUB.ADD;
873 x_return_status := FALSE;
874 mydebug('validate_std_unt_db_cons IGS_PS_UNIT_VER_PKG.GET_PK_FOR_VALIDATION ');
875 END IF;
876
877 -- Foreign Key with Table IGS_AS_GRD_SCH_GRADE
878
879 IF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation ( x_grading_schema_cd => p_lgcy_adstunt_rec.grading_schema_cd,
880 x_version_number => p_lgcy_adstunt_rec.grd_sch_version_number,
881 x_grade => p_lgcy_adstunt_rec.grade
882 ) THEN
883 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ADV_STUNT_GRD_EXISTS');
884 FND_MSG_PUB.ADD;
885 x_return_status := FALSE;
886 mydebug('validate_std_unt_db_cons IGS_AS_GRD_SCH_GRADE_PKG.GET_PK_FOR_VALIDATION ');
887 END IF;
888
889 -- Validate that the record parameter S_Adv_Stnd_Recognition_Type cannot have any other values other than 'CREDIT','EXEMPTION' or 'PRECLUSION'
890
891 IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type NOT IN ('CREDIT' , 'EXEMPTION' , 'PRECLUSION') THEN
892 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_RECOG_VALUE');
893 FND_MSG_PUB.ADD;
894 x_return_status := FALSE;
895 mydebug('validate_std_unt_db_cons P_LGCY_ADSTUNT_REC.S_ADV_STND_RECOGNITION_TYPE ');
896 END IF;
897 -- Check constraint on PROG_GROUP_IND
898 IF p_lgcy_adstunt_rec.prog_group_ind <> upper(p_lgcy_adstunt_rec.prog_group_ind ) AND
899 p_lgcy_adstunt_rec.prog_group_ind NOT IN ('Y' , 'N') THEN
900 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CRS_GRP_IN_Y_N');
901 FND_MSG_PUB.ADD;
902 x_return_status := FALSE;
903 mydebug('validate_std_unt_db_cons P_LGCY_ADSTUNT_REC.PROG_GROUP_IND ');
904 END IF;
905 -- Check that if institution_cd is NOT NULL and unit_details_id is NULL
906 IF p_lgcy_adstunt_rec.institution_cd IS NOT NULL AND
907 p_unit_details_id IS NULL THEN
908 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUT_INST_UID_NOT_NULL');
909 FND_MSG_PUB.ADD;
910 x_return_status := FALSE;
911 mydebug('validate_std_unt_db_cons P_LGCY_ADSTUNT_REC.EXEMPTION_INSTITUTION_CD ');
912 END IF;
913
914 -- Validate that both institution_cd and tst_rslt_dtls_id are not nulls
915 IF p_lgcy_adstunt_rec.institution_cd IS NOT NULL AND
916 p_tst_rslt_dtls_id IS NOT NULL THEN
917 FND_MESSAGE.SET_NAME('IGS','IGS_AV_INST_RLID_BOTH_NOT_NULL');
918 FND_MSG_PUB.ADD;
919 x_return_status := FALSE;
920 mydebug('validate_std_unt_db_cons P_LGCY_ADSTUNT_REC.EXEMPTION_INSTITUTION_CD ');
921 END IF;
922 -- One and only one of unit details or test result details must be entered (both cannot be Not Nulls simultaneously
923 IF p_unit_details_id IS NULL AND
924 p_tst_rslt_dtls_id IS NULL THEN
925 FND_MESSAGE.SET_NAME('IGS','IGS_AV_UID_RSID_ATLEAST_NULL');
926 FND_MSG_PUB.ADD;
927 x_return_status := FALSE;
928 mydebug('validate_std_unt_db_cons IGS_AV_UID_RSID_ATLEAST_NULL ');
929 END IF;
930
931 RETURN x_return_status;
932 END validate_std_unt_db_cons;
933
934 FUNCTION validate_unit(
935 p_lgcy_adstunt_rec IN lgcy_adstunt_rec_type,
936 p_person_id IN igs_pe_person.person_id%TYPE,
937 p_s_adv_stnd_type IN igs_av_stnd_unit_all. s_adv_stnd_type %TYPE,
938 p_cal_type IN igs_ca_inst.cal_type%TYPE,
939 p_seq_number IN igs_ca_inst. sequence_number%TYPE,
940 p_auth_pers_id IN igs_pe_person.person_id%TYPE,
941 p_unit_details_id IN igs_ad_term_unitdtls. unit_details_id%TYPE,
942 p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls .tst_rslt_dtls_id%TYPE,
943 p_as_version_number IN igs_en_stdnt_ps_att.version_number%TYPE
944 )RETURN BOOLEAN IS
945 /*===========================================================================+
946 | FUNCTION |
947 | validate_unit |
948 | |
949 | DESCRIPTION |
950 | This function performs all the business validations before |
951 | inserting a record into the table IGS_AV_STND_UNIT_ALL and|
952 | keeps adding error message to stack as an when it encounters.|
953 | |
954 | SCOPE - PRIVATE |
955 | |
956 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
957 | |
958 | ARGUMENTS : IN: |
959 | p_lgcy_adstunt_rec |
960 | p_person_id |
961 | p_s_adv_stnd_type |
962 | p_cal_type |
963 | p_seq_number |
964 | p_auth_pers_number |
965 | p_unit_details_id |
966 | p_tst_rslt_dtls_id |
967 | p_as_version_number |
968 | |
969 | RETURNS : x_return_value |
970 | |
971 | NOTES |
972 | |
973 | MODIFICATION HISTORY |
974 | jhanda 11-11-2002 Created |
975 +===========================================================================*/
976 x_return_status BOOLEAN := TRUE;
977 l_total_exmptn_approved p_lgcy_adstunt_rec.total_exmptn_approved%TYPE ;
978 l_total_exmptn_granted p_lgcy_adstunt_rec.total_exmptn_granted%TYPE ;
979 l_total_exmptn_perc_grntd p_lgcy_adstunt_rec.total_exmptn_perc_grntd%TYPE ;
980 l_message_name VARCHAR2(30);
981 BEGIN
982 /*
983 Validate that the approved date is greater than current date
984 */
985 IF p_lgcy_adstunt_rec.approved_dt >= SYSDATE THEN
986 FND_MESSAGE.SET_NAME('IGS','IGS_AV_APRVDT_LE_CURDT');
987 FND_MSG_PUB.ADD;
988 x_return_status := FALSE;
989 END IF;
990
991 IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type = 'PRECLUSION' AND
992 p_lgcy_adstunt_rec.achievable_credit_points <> 0.00 THEN
993 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CREDIT_PRECL_IS_ZERO');
994 FND_MSG_PUB.ADD;
995 x_return_status := FALSE;
996 mydebug('validate_unit IGS_AV_CREDIT_PRECL_IS_ZERO ');
997 END IF;
998 IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type = 'PRECLUSION' AND
999 p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'GRANTED' THEN
1000 FND_MESSAGE.SET_NAME('IGS','IGS_AV_NOT_GRT_PRE');
1001 FND_MSG_PUB.ADD;
1002 x_return_status := FALSE;
1003 mydebug('validate_unit IGS_AV_NOT_GRT_PRE ');
1004 END IF;
1005
1006 IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'EXPIRED' AND
1007 p_lgcy_adstunt_rec.expiry_dt IS NULL THEN
1008 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_EXPDT_TOBE_SET');
1009 FND_MSG_PUB.ADD;
1010 x_return_status := FALSE;
1011 mydebug('validate_unit IGS_AV_STUNT_EXPDT_TOBE_SET');
1012 END IF;
1013
1014
1015 IF NOT igs_av_val_asu.advp_val_as_totals(
1016 p_person_id => p_person_id,
1017 p_course_cd => p_lgcy_adstunt_rec.program_cd ,
1018 p_version_number => p_as_version_number ,
1019 p_include_approved => TRUE ,
1020 p_asu_unit_cd => p_lgcy_adstunt_rec.unit_cd ,
1021 p_asu_version_number => p_lgcy_adstunt_rec.version_number ,
1022 p_asu_advstnd_granting_status => p_lgcy_adstunt_rec.s_adv_stnd_granting_status ,
1023 p_asul_unit_level => NULL ,
1024 p_asul_exmptn_institution_cd => p_lgcy_adstunt_rec.exemption_institution_cd ,
1025 p_asul_advstnd_granting_status=> p_lgcy_adstunt_rec.s_adv_stnd_granting_status ,
1026 p_total_exmptn_approved => l_total_exmptn_approved ,
1027 p_total_exmptn_granted => l_total_exmptn_granted ,
1028 p_total_exmptn_perc_grntd => l_total_exmptn_perc_grntd ,
1029 p_message_name => l_message_name,
1030 p_unit_details_id => p_unit_details_id ,
1031 p_tst_rslt_dtls_id => p_tst_rslt_dtls_id
1032 ) THEN
1033 FND_MESSAGE.SET_NAME('IGS',l_message_name);
1034 FND_MSG_PUB.ADD;
1035 x_return_status := FALSE;
1036 mydebug('validate_unit IGS_AV_VAL_ASU.ADVP_VAL_AS_TOTALS ');
1037 END IF;
1038
1039 -- Check for person hold
1040 IF NOT igs_en_val_encmb.enrp_val_excld_prsn(
1041 p_person_id => p_person_id ,
1042 p_course_cd => p_lgcy_adstunt_rec.program_cd,
1043 p_effective_dt => p_lgcy_adstunt_rec.granted_dt,
1044 p_message_name => l_message_name
1045 ) THEN
1046 FND_MESSAGE.SET_NAME('IGS',l_message_name);
1047 FND_MSG_PUB.ADD;
1048 x_return_status := FALSE;
1049 mydebug('validate_unit IGS_EN_VAL_ENCMB.ENRP_VAL_EXCLD_PRSN ');
1050 END IF;
1051
1052
1053 IF p_as_version_number IS NULL AND
1054 p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'GRANTED' THEN
1055 FND_MESSAGE.SET_NAME('IGS','IGS_AV_GRANTED_STUDPRG_EXISTS');
1056 FND_MSG_PUB.ADD;
1057 x_return_status := FALSE;
1058 mydebug('validate_unit IGS_AV_GRANTED_STUDPRG_EXISTS ');
1059 END IF;
1060
1061 IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'GRANTED' AND
1062 p_lgcy_adstunt_rec.granted_dt IS NULL THEN
1063 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_GRANTDT_NOT_NULL');
1064 FND_MSG_PUB.ADD;
1065 x_return_status := FALSE;
1066 mydebug('validate_unit IGS_AV_STUNT_GRANTDT_NOT_NULL');
1067 END IF;
1068
1069 IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'CANCELLED' AND
1070 p_lgcy_adstunt_rec.cancelled_dt IS NULL THEN
1071 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_CANCDT_NOT_NULL');
1072 FND_MSG_PUB.ADD;
1073 x_return_status := FALSE;
1074 mydebug('validate_unit IGS_AV_STUNT_CANCDT_NOT_NULL');
1075 END IF;
1076
1077 IF p_lgcy_adstunt_rec.s_adv_stnd_granting_status = 'REVOKED' AND
1078 p_lgcy_adstunt_rec.revoked_dt IS NULL THEN
1079 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STUNT_REVDT_NOT_NULL');
1080 FND_MSG_PUB.ADD;
1081 x_return_status := FALSE;
1082 mydebug('validate_unit IGS_AV_STUNT_REVDT_NOT_NULL');
1083 END IF;
1084
1085 IF NOT igs_av_val_asu.advp_val_approved_dt(
1086 p_approved_dt => p_lgcy_adstunt_rec.approved_dt ,
1087 p_expiry_dt => p_lgcy_adstunt_rec.expiry_dt ,
1088 p_message_name => l_message_name
1089 ) THEN
1090 FND_MESSAGE.SET_NAME('IGS',l_message_name);
1091 FND_MSG_PUB.ADD;
1092 x_return_status := FALSE;
1093 mydebug('validate_unit IGS_AV_VAL_ASU.ADVP_VAL_APPROVED_DT ');
1094 END IF;
1095 -- Validate whether the Granted Date, Cancelled Date Or Revoked Dates are greater than or equal to the Approved date
1096 IF NOT (
1097 igs_av_val_asu.advp_val_as_aprvd_dt(
1098 p_approved_dt => p_lgcy_adstunt_rec.approved_dt ,
1099 p_related_dt => p_lgcy_adstunt_rec.granted_dt ,
1100 p_message_name => l_message_name
1101 ) AND
1102 igs_av_val_asu.advp_val_as_aprvd_dt(
1103 p_approved_dt => p_lgcy_adstunt_rec.approved_dt ,
1104 p_related_dt => p_lgcy_adstunt_rec.cancelled_dt ,
1105 p_message_name => l_message_name
1106 ) AND
1107 igs_av_val_asu.advp_val_as_aprvd_dt(
1108 p_approved_dt => p_lgcy_adstunt_rec.approved_dt ,
1109 p_related_dt => p_lgcy_adstunt_rec.revoked_dt ,
1110 p_message_name => l_message_name
1111 )
1112 )THEN
1113
1114 FND_MESSAGE.SET_NAME('IGS','IGS_AV_DTASSO_LE_APPRVDT' );
1115 FND_MSG_PUB.ADD;
1116 x_return_status := FALSE;
1117 mydebug('validate_unit IGS_AV_VAL_ASU.ADVP_VAL_APRVD_DT ');
1118 END IF;
1119
1120 IF NOT igs_ad_val_acai.genp_val_staff_prsn(
1121 p_person_id => p_auth_pers_id ,
1122 p_message_name => l_message_name
1123 ) THEN
1124 FND_MESSAGE.SET_NAME('IGS','IGS_GE_NOT_STAFF_MEMBER');
1125 FND_MSG_PUB.ADD;
1126 x_return_status := FALSE;
1127 mydebug('validate_unit IGS_GE_NOT_STAFF_MEMBER ');
1128 END IF;
1129
1130 IF p_lgcy_adstunt_rec.achievable_credit_points IS NULL THEN
1131 FND_MESSAGE.SET_NAME('IGS','IGS_AV_CRD_PER_CANNOT_BE_NULL');
1132 FND_MSG_PUB.ADD;
1133 x_return_status := FALSE;
1134 mydebug('validate_unit IGS_AV_CRD_PER_CANNOT_BE_NULL ');
1135 END IF;
1136
1137 /*
1138 check the course_attempt_status
1139 */
1140 DECLARE
1141 CURSOR c_exists (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
1142 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE ) IS
1143 SELECT 'x'
1144 FROM igs_en_stdnt_ps_att
1145 WHERE person_id = cp_person_id
1146 AND course_cd = cp_course_cd
1147 AND course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT','UNCONFIRM','DISCONTIN','COMPLETED');
1148 l_exists VARCHAR2(1);
1149 BEGIN
1150 OPEN c_exists (p_person_id,
1151 p_lgcy_adstunt_rec.program_cd);
1152 FETCH c_exists INTO l_exists;
1153 IF c_exists%NOTFOUND THEN
1154 FND_MESSAGE.SET_NAME('IGS','IGS_AV_PRG_ATTMPT_INVALID');
1155 FND_MSG_PUB.ADD;
1156 mydebug('validate_unit IGS_AV_PRG_ATTMPT_INVALID ');
1157 x_return_status := FALSE;
1158 END IF;
1159 CLOSE c_exists;
1160 END;
1161 RETURN x_return_status;
1162 END validate_unit;
1163
1164
1165 FUNCTION create_post_unit(
1166 p_person_id IN igs_pe_person.person_id%type,
1167 p_course_version IN igs_ps_ver.version_number%type,
1168 p_unit_details_id IN igs_ad_term_unitdtls. unit_details_id%type,
1169 p_tst_rslt_dtls_id IN igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%type,
1170 p_lgcy_adstunt_rec IN lgcy_adstunt_rec_type
1171 )RETURN BOOLEAN IS
1172 /*===========================================================================+
1173 | FUNCTION |
1174 | create_post_unit |
1175 | |
1176 | DESCRIPTION |
1177 | |
1178 | SCOPE - PRIVATE |
1179 | |
1180 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1181 | |
1182 | ARGUMENTS : IN: |
1183 | p_person_id |
1184 | p_lgcy_adstunt_rec |
1185 | RETURNS : x_return_value |
1186 | |
1187 | NOTES |
1188 | |
1189 | MODIFICATION HISTORY |
1190 | jhanda 11-11-2002 Created |
1191 +===========================================================================*/
1192 x_return_status BOOLEAN :=TRUE;
1193 l_message VARCHAR2(2000);
1194 l_total_exmptn_approved igs_av_adv_standing_all.total_exmptn_approved%TYPE ;
1195 l_total_exmptn_granted igs_av_adv_standing_all.total_exmptn_granted%TYPE ;
1196 l_total_exmptn_perc_grntd igs_av_adv_standing_all.total_exmptn_perc_grntd%TYPE ;
1197 BEGIN
1198 x_return_status := TRUE;
1199 mydebug ('in create_post_unit');
1200 /*
1201 Validate whether the advanced standing approved / granted has not
1202 exceeded the advanced standing internal or external limits of
1203 the Program version
1204 */
1205 IF NOT igs_av_val_asu.advp_val_as_totals(
1206 p_person_id => p_person_id,
1207 p_course_cd => p_lgcy_adstunt_rec.program_cd,
1208 p_version_number => p_course_version,
1209 p_include_approved => TRUE,
1210 p_asu_unit_cd => p_lgcy_adstunt_rec.unit_cd,
1211 p_asu_version_number => p_lgcy_adstunt_rec.version_number,
1212 p_asu_advstnd_granting_status => p_lgcy_adstunt_rec.s_adv_stnd_granting_status,
1213 p_asul_unit_level => NULL ,
1214 p_asul_exmptn_institution_cd => p_lgcy_adstunt_rec.exemption_institution_cd,
1215 p_asul_advstnd_granting_status => p_lgcy_adstunt_rec.s_adv_stnd_granting_status,
1216 p_total_exmptn_approved => l_total_exmptn_approved,
1217 p_total_exmptn_granted => l_total_exmptn_granted,
1218 p_total_exmptn_perc_grntd => l_total_exmptn_perc_grntd,
1219 p_message_name => l_message,
1220 p_unit_details_id => p_unit_details_id,
1221 p_tst_rslt_dtls_id => p_tst_rslt_dtls_id,
1222 p_asu_exmptn_institution_cd => p_lgcy_adstunt_rec.exemption_institution_cd
1223 ) THEN
1224 FND_MESSAGE.SET_NAME('IGS',l_message);
1225 FND_MSG_PUB.ADD;
1226 x_return_status := FALSE;
1227 ELSE -- function returns TRUE
1228 /*
1229 update IGS_AV_ADV_STANDING_ALL with above obtained values for
1230 total_exmptn_approved, total_exmptn_granted and total_exmptn_perc_grntd
1231 */
1232 UPDATE igs_av_adv_standing_all
1233 SET total_exmptn_approved = l_total_exmptn_approved,
1234 total_exmptn_granted = l_total_exmptn_granted,
1235 total_exmptn_perc_grntd = l_total_exmptn_perc_grntd
1236 WHERE person_id = p_person_id
1237 AND course_cd = p_lgcy_adstunt_rec.program_cd
1238 AND version_number = p_course_version
1239 AND exemption_institution_cd = p_lgcy_adstunt_rec.exemption_institution_cd;
1240 END IF;
1241 mydebug ('out create_post_lvl');
1242 return x_return_status;
1243 END create_post_unit;
1244
1245
1246 FUNCTION validate_alt_unt_db_cons(
1247 p_lgcy_adstunt_rec IN LGCY_ADSTUNT_REC_TYPE,
1248 p_av_stnd_unit_id IN IGS_AV_STND_UNIT_ALL.AV_STND_UNIT_ID%TYPE ,
1249 p_s_adv_stnd_type IN IGS_AV_STND_UNIT_ALL.S_ADV_STND_TYPE%TYPE ,
1250 p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE ,
1251 p_unit_details_id IN IGS_AD_TERM_UNITDTLS.UNIT_DETAILS_ID%TYPE ,
1252 p_tst_rslt_dtls_id IN IGS_AD_TST_RSLT_DTLS.TST_RSLT_DTLS_ID%TYPE ,
1253 p_as_version_number IN IGS_AV_STND_UNIT_ALL.AS_VERSION_NUMBER%TYPE ,
1254 p_av_stnd_unit_lvl_id OUT NOCOPY IGS_AV_STND_ALT_UNIT.AV_STND_UNIT_ID%TYPE ,
1255 x_return_status OUT NOCOPY VARCHAR2
1256 )
1257 RETURN BOOLEAN
1258 /*===========================================================================+
1259 | FUNCTION |
1260 | validate_alt_unt_db_cons |
1261 | |
1262 | DESCRIPTION |
1263 | This function performs all the data integrity validation |
1264 | before entering into the table IGS_AV_STND_UNIT_ ALL and |
1265 | keeps adding error message to stack as an when it encounters.|
1266 | |
1267 | SCOPE - PRIVATE |
1268 | |
1269 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1270 | |
1271 | ARGUMENTS : IN: |
1272 | p_lgcy_adstunt_rec |
1273 | p_av_stnd_unit_id |
1274 | p_s_adv_stnd_type |
1275 | RETURNS : x_return_value |
1276 | |
1277 | NOTES |
1278 | |
1279 | MODIFICATION HISTORY |
1280 | jhanda 11-11-2002 Created |
1281 +===========================================================================*/
1282
1283 IS
1284
1285 CURSOR cur_get_adv_stnd_id ( cp_person_id NUMBER,
1286 cp_institution_cd VARCHAR2,
1287 cp_unit_details_id NUMBER,
1288 cp_tst_rslt_dtls_id NUMBER,
1289 cp_unit_cd VARCHAR2,
1290 cp_as_course_cd VARCHAR2,
1291 cp_as_version_number NUMBER
1292 )IS
1293 SELECT
1294 av_stnd_unit_id
1295 FROM
1296 igs_av_stnd_unit_all
1297 WHERE
1298 person_id = cp_person_id AND
1299 NVL(institution_cd,0) = NVL(cp_institution_cd,0) AND
1300 NVL(tst_rslt_dtls_id,0) = NVL(cp_tst_rslt_dtls_id,0) AND
1301 NVL(unit_details_id,0) = NVL(cp_unit_details_id,0) AND
1302 unit_cd = cp_unit_cd AND
1303 as_course_cd = cp_as_course_cd AND
1304 as_version_number = cp_as_version_number ;
1305
1306 l_av_stnd_unit_lvl_id IGS_AV_STND_UNIT_ALL.AV_STND_UNIT_ID%TYPE;
1307 l_return_status BOOLEAN DEFAULT TRUE;
1308 BEGIN
1309 -- Initialise x_return_status to 'S'
1310 x_return_status := 'S';
1311
1312 -- Foreign key and Primary key validation
1313 OPEN cur_get_adv_stnd_id (
1314 cp_person_id => p_person_id ,
1315 cp_institution_cd => p_lgcy_adstunt_rec.institution_cd ,
1316 cp_unit_details_id => p_unit_details_id ,
1317 cp_tst_rslt_dtls_id => p_tst_rslt_dtls_id ,
1318 cp_unit_cd => p_lgcy_adstunt_rec.unit_cd ,
1319 cp_as_course_cd => p_lgcy_adstunt_rec.program_cd ,
1320 cp_as_version_number => p_as_version_number
1321 );
1322 FETCH cur_get_adv_stnd_id INTO l_av_stnd_unit_lvl_id;
1323 IF cur_get_adv_stnd_id%NOTFOUND THEN
1324 -- foreign key with table igs_av_stnd_unit_all does not exist.
1325 FND_MESSAGE.SET_NAME('IGS','IGS_AV_UNT_ALT_ID_FK_EXISTS');
1326 FND_MSG_PUB.ADD;
1327 x_return_status := 'E';
1328 l_return_status := FALSE;
1329 ELSE
1330 -- av_stnd_unit_lvl_id found in table igs_av_stnd_unit_all
1331 -- check primary in table igs_av_std_alt_unit
1332 IF igs_av_stnd_alt_unit_pkg.get_pk_for_validation(
1333 x_av_stnd_unit_id => l_av_stnd_unit_lvl_id,
1334 x_alt_unit_cd => p_lgcy_adstunt_rec.alt_unit_cd,
1335 x_alt_version_number => p_lgcy_adstunt_rec.alt_version_number
1336 )THEN
1337 CLOSE cur_get_adv_stnd_id;
1338 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STDUNT_ALREADY_EXISTS');
1339 FND_MSG_PUB.ADD;
1340 x_return_status := 'W';
1341 l_return_status := FALSE;
1342 RETURN (l_return_status) ;
1343 END IF;
1344 p_av_stnd_unit_lvl_id := l_av_stnd_unit_lvl_id;
1345 END IF;
1346 CLOSE cur_get_adv_stnd_id;
1347
1348 IF NOT IGS_PS_UNIT_VER_PKG.GET_PK_FOR_VALIDATION(
1349 x_unit_cd =>p_lgcy_adstunt_rec.unit_cd ,
1350 x_version_number => p_lgcy_adstunt_rec.version_number
1351 )THEN
1352 FND_MESSAGE.SET_NAME('IGS','IGS_AV_UNT_ALT_UID_FK_EXISTS');
1353 FND_MSG_PUB.ADD;
1354 x_return_status := 'E';
1355 l_return_status := FALSE;
1356 mydebug('validate_unit IGS_AV_UNT_ALT_UID_FK_EXISTS ');
1357 END IF;
1358
1359 IF p_lgcy_adstunt_rec.OPTIONAL_IND NOT IN ('Y' , 'N') THEN
1360 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ALT_OPT_IND_IN_Y_N');
1361 FND_MSG_PUB.ADD;
1362 x_return_status := 'E';
1363 l_return_status := FALSE;
1364 mydebug('validate_unit IGS_AV_ALT_OPT_IND_IN_Y_N ');
1365 END IF;
1366
1367 RETURN ( l_return_status );
1368
1369 END validate_alt_unt_db_cons;
1370
1371
1372 FUNCTION validate_alt_unit(
1373 p_lgcy_adstunt_rec IN lgcy_adstunt_rec_type,
1374 p_av_stnd_unit_id IN igs_av_stnd_unit_all.av_stnd_unit_id%TYPE,
1375 p_s_adv_stnd_type IN igs_av_stnd_unit_all.s_adv_stnd_type %TYPE
1376 )RETURN BOOLEAN IS
1377 /*===========================================================================+
1378 | FUNCTION |
1379 | validate_alt_unit |
1380 | |
1381 | DESCRIPTION |
1382 | Validate that the advanced standing unit code is not |
1383 | same as the Alternate Unit Code |
1384 | |
1385 | SCOPE - PRIVATE |
1386 | |
1387 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1388 | |
1389 | ARGUMENTS : IN: |
1390 | p_lgcy_adstunt_rec |
1391 | p_av_stnd_unit_id |
1392 | p_s_adv_stnd_type |
1393 | RETURNS : x_return_value |
1394 | |
1395 | NOTES |
1396 | |
1397 | MODIFICATION HISTORY |
1398 | jhanda 11-11-2002 Created |
1399 +===========================================================================*/
1400 x_return_status BOOLEAN :=TRUE;
1401 l_message_name VARCHAR2(30);
1402 BEGIN
1403 -- Validate that the advanced standing unit code is not same as the Alternate Unit Code
1404 IF NOT igs_av_val_asau.advp_val_prclde_unit(
1405 p_precluded_unit_cd => p_lgcy_adstunt_rec.unit_cd ,
1406 p_alternate_unit_cd => p_lgcy_adstunt_rec.alt_unit_cd ,
1407 p_message_name => l_message_name
1408 ) THEN
1409 FND_MESSAGE.SET_NAME('IGS','IGS_AV_ALTUNIT_DIFF_UNITASSOC');
1410 FND_MSG_PUB.ADD;
1411 x_return_status := FALSE;
1412 mydebug('validate_unit IGS_AV_ALTUNIT_DIFF_UNITASSOC ');
1413 END IF;
1414 RETURN x_return_status;
1415 END validate_alt_unit;
1416
1417
1418 FUNCTION validate_unt_bss_db_cons(
1419 p_lgcy_adstunt_rec IN lgcy_adstunt_rec_type,
1420 p_av_stnd_unit_id IN igs_av_stnd_unit_all.av_stnd_unit_id%TYPE,
1421 p_s_adv_stnd_type IN igs_av_stnd_unit_all.s_adv_stnd_type %TYPE
1422 )RETURN BOOLEAN IS
1423 /*===========================================================================+
1424 | FUNCTION |
1425 | validate_unt_bss_db_cons |
1426 | |
1427 | DESCRIPTION |
1428 | This function performs all the data integrity validation |
1429 | before entering into the table IGS_AV_STD_UNT_BASIS_ALL and |
1430 | keeps adding error message to stack as an when it encounters.| |
1431 | SCOPE - PRIVATE |
1432 | |
1433 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1434 | |
1435 | ARGUMENTS : IN: |
1436 | p_lgcy_adstunt_rec |
1437 | p_av_stnd_unit_id |
1438 | p_s_adv_stnd_type |
1439 | RETURNS : x_return_value |
1440 | |
1441 | NOTES |
1442 | |
1443 | MODIFICATION HISTORY |
1444 | jhanda 11-11-2002 Created |
1445 | jhanda 01-07-2003 Changed for Bug 2743009 |
1446 | swaghmar 19-10-2005 Changed for Bug 4676359
1447 +===========================================================================*/
1448 x_return_status BOOLEAN := TRUE;
1449 BEGIN
1450 -- Primary key validation
1451 IF igs_av_std_unt_basis_pkg.get_pk_for_validation(
1452 x_av_stnd_unit_id =>p_av_stnd_unit_id
1453 ) THEN
1454 FND_MESSAGE.SET_NAME('IGS','IGS_AV_UNT_BAS_PK_EXISTS');
1455 FND_MSG_PUB.ADD;
1456 x_return_status := FALSE;
1457 mydebug('validate_unt_bss_db_cons IGS_AV_UNT_BAS_PK_EXISTS ');
1458 END IF;
1459
1460 -- Check Foreign key Validation with the table IGS_AV_STND_UNIT_ALL
1461 IF NOT IGS_AV_STND_UNIT_PKG.GET_PK_FOR_VALIDATION( x_av_stnd_unit_id => p_av_stnd_unit_id) THEN
1462 FND_MESSAGE.SET_NAME('IGS','IGS_AV_UNT_ALT_ID_FK_EXISTS');
1463 FND_MSG_PUB.ADD;
1464 x_return_status := FALSE;
1465 mydebug('validate_unt_bss_db_cons IGS_AV_UNT_ALT_ID_FK_EXISTS ');
1466 END IF;
1467
1468 -- Check passed BASIS_PROGRAM_TYPE for x_course_type
1469 IF ((p_lgcy_adstunt_rec.basis_program_type IS NOT NULL) AND NOT igs_ps_type_pkg.get_pk_for_validation(
1470 x_course_type =>p_lgcy_adstunt_rec.basis_program_type
1471 )) THEN
1472 FND_MESSAGE.SET_NAME('IGS','IGS_AV_BAS_CRS_TYP_FK_EXISTS');
1473 FND_MSG_PUB.ADD;
1474 x_return_status := FALSE;
1475 mydebug('validate_unt_bss_db_cons IGS_AV_BAS_CRS_TYP_FK_EXISTS ');
1476 END IF;
1477 -- Validate that the record parameter basis_year has a value greater than 1900 and less than 2100
1478 IF ((p_lgcy_adstunt_rec.basis_year IS NOT NULL) AND (p_lgcy_adstunt_rec.basis_year < 1900 OR
1479 p_lgcy_adstunt_rec.basis_year > 2100)) THEN
1480 FND_MESSAGE.SET_NAME('IGS','IGS_AV_BAS_YEAR_1900_2100');
1481 FND_MSG_PUB.ADD;
1482 x_return_status := FALSE;
1483 mydebug('validate_unt_bss_db_cons IGS_AV_BAS_YEAR_1900_2100 ');
1484 END IF;
1485 -- Validate that the value for the record parameter Basis_completion_Ind field cannot be anything other than 'Y' or 'N'
1486 IF ((p_lgcy_adstunt_rec.basis_completion_ind IS NOT NULL) AND (NOT p_lgcy_adstunt_rec.basis_completion_ind IN ('Y','N'))) THEN
1487 FND_MESSAGE.SET_NAME('IGS','IGS_AV_BAS_COMP_IND_IN_Y_N');
1488 FND_MSG_PUB.ADD;
1489 x_return_status := FALSE;
1490 mydebug('validate_unt_bss_db_cons IGS_AV_BAS_COMP_IND_IN_Y_N ');
1491 END IF;
1492 RETURN x_return_status;
1493 END validate_unt_bss_db_cons;
1494
1495
1496 FUNCTION validate_un1t_basis(
1497 p_lgcy_adstunt_rec IN lgcy_adstunt_rec_type,
1498 p_av_stnd_unit_id IN igs_av_stnd_unit_all.av_stnd_unit_id%TYPE,
1499 p_s_adv_stnd_type IN igs_av_stnd_unit_all.s_adv_stnd_type %TYPE
1500 )RETURN BOOLEAN IS
1501 /*===========================================================================+
1502 | FUNCTION |
1503 | validate_un1t_basis |
1504 | |
1505 | DESCRIPTION |
1506 | This function performs all the data integrity validation |
1507 | before entering into the table IGS_AV_STD_UNT_BASIS_ALL and |
1508 | keeps adding error message to stack as an when it encounters.| |
1509 | |
1510 | SCOPE - PRIVATE |
1511 | |
1512 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1513 | |
1514 | ARGUMENTS : IN: |
1515 | p_lgcy_adstunt_rec |
1516 | p_av_stnd_unit_id |
1517 | p_s_adv_stnd_type |
1518 | RETURNS : x_return_value |
1519 | |
1520 | NOTES |
1521 | |
1522 | MODIFICATION HISTORY |
1523 | jhanda 11-11-2002 Created |
1524 +===========================================================================*/
1525 x_return_status BOOLEAN :=TRUE;
1526 l_message_name VARCHAR2(30);
1527 l_return_type VARCHAR2(300);
1528 BEGIN
1529 -- Validate that the value in the BASIS_YEAR is not more than the current year
1530 IF igs_av_val_asuleb.advp_val_basis_year(
1531 p_basis_year => p_lgcy_adstunt_rec.basis_year ,
1532 p_course_cd => p_lgcy_adstunt_rec.program_cd ,
1533 p_version_number => p_lgcy_adstunt_rec.version_number ,
1534 p_message_name => l_message_name,
1535 p_return_type => l_return_type ) THEN
1536 FND_MESSAGE.SET_NAME('IGS','IGS_AV_LYENR_NOTGT_CURYR');
1537 FND_MSG_PUB.ADD;
1538 x_return_status := FALSE;
1539 mydebug('validate_unt_bss_db_cons IGS_AV_LYENR_NOTGT_CURYR');
1540 END IF;
1541 RETURN x_return_status;
1542 END validate_un1t_basis;
1543
1544
1545 PROCEDURE create_adv_stnd_unit
1546 (p_api_version IN NUMBER,
1547 p_init_msg_list IN VARCHAR2 ,
1548 p_commit IN VARCHAR2 ,
1549 p_validation_level IN VARCHAR2 ,
1550 p_lgcy_adstunt_rec IN OUT NOCOPY lgcy_adstunt_rec_type,
1551 x_return_status OUT NOCOPY VARCHAR2,
1552 x_msg_count OUT NOCOPY NUMBER,
1553 x_msg_data OUT NOCOPY VARCHAR2
1554 )
1555 IS
1556 /*===========================================================================+
1557 | PROCEDURE |
1558 | create_adv_stnd_unit |
1559 | |
1560 | DESCRIPTION |
1561 | Creates advanced standing unit |
1562 | |
1563 | SCOPE - PUBLIC |
1564 | |
1565 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1566 | |
1567 | ARGUMENTS : IN: |
1568 | p_api_version |
1569 | p_init_msg_list |
1570 | p_commit |
1571 | p_lgcy_adstunt_rec |
1572 | OUT: |
1573 | x_return_status |
1574 | x_msg_count |
1575 | x_msg_data |
1576 | IN/ OUT: |
1577 | |
1578 | RETURNS : NONE |
1579 | |
1580 | NOTES |
1581 | |
1582 | MODIFICATION HISTORY |
1583 | jhanda 11-11-2002 Created |
1584 | shimitta 9-11-2005 Modified BUG#472377: optional_ind set to N if null|
1585 | swaghmar 14-11-2005 Bug# 4723760 -Added check IF l_reference_code_id |
1586 | IS NOT NULL |
1587 +===========================================================================*/
1588 l_api_name CONSTANT VARCHAR2(30) := 'create_adv_stnd_unit';
1589 l_api_version CONSTANT NUMBER := 1.0;
1590 l_ret_status BOOLEAN;
1591 l_b_av_stnd_alt_unit_pk_exist BOOLEAN := TRUE;
1592 l_person_id igs_pe_person.person_id%TYPE;
1593 l_s_adv_stnd_type igs_av_stnd_unit_all. s_adv_stnd_type%TYPE;
1594 l_cal_type igs_ca_inst.cal_type%TYPE;
1595 l_seq_number igs_ca_inst. sequence_number%TYPE;
1596 l_auth_pers_id igs_pe_person.person_id%TYPE;
1597 l_unit_details_id igs_ad_term_unitdtls.unit_details_id%TYPE;
1598 l_tst_rslt_dtls_id igs_ad_tst_rslt_dtls .tst_rslt_dtls_id%TYPE;
1599 l_as_version_number igs_en_stdnt_ps_att.version_number%TYPE;
1600 l_av_stnd_unit_lvl_id igs_av_stnd_unit_all.av_stnd_unit_id%TYPE;
1601 L_REQUEST_ID igs_av_stnd_unit_all.request_id%TYPE ;
1602 L_PROGRAM_ID igs_av_stnd_unit_all.program_id%TYPE ;
1603 L_PROGRAM_APPLICATION_ID igs_av_stnd_unit_all.program_application_id%TYPE;
1604 L_PROGRAM_UPDATE_DATE igs_av_stnd_unit_all.program_update_date%TYPE;
1605 duplicate_record_exists EXCEPTION;
1606 l_reference_code_id igs_ge_ref_cd.reference_code_id%TYPE;
1607 l_AVU_REFERENCE_CD_ID IGS_AV_UNT_REF_CDS.AVU_REFERENCE_CD_ID%TYPE;
1608
1609 CURSOR c_unit_ref_id is
1610 select IGS_AV_UNT_REF_CDS_S.nextval from dual;
1611
1612 BEGIN
1613 mydebug('ENTERED create_adv_stnd_unit ');
1614 --Standard start of API savepoint
1615 SAVEPOINT create_adv_stnd_unit;
1616
1617 --Standard call to check for call compatibility.
1618 IF NOT FND_API.Compatible_API_Call(
1619 l_api_version,
1620 p_api_version,
1621 l_api_name,
1622 G_PKG_NAME)
1623 THEN
1624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1625 END IF;
1626
1627 --Initialize message list if p_init_msg_list is set to TRUE.
1628 IF FND_API.to_Boolean(p_init_msg_list) THEN
1629 FND_MSG_PUB.initialize;
1630 END IF;
1631
1632 --Initialize API return status to success.
1633 x_return_status := FND_API.G_RET_STS_SUCCESS;
1634
1635 /*==================== Start Your coding here==========*/
1636
1637 /* Initialise */
1638 p_lgcy_adstunt_rec.prog_group_ind := upper(p_lgcy_adstunt_rec.prog_group_ind);
1639 p_lgcy_adstunt_rec.program_cd := upper(p_lgcy_adstunt_rec.program_cd);
1640 p_lgcy_adstunt_rec.unit_cd := upper(p_lgcy_adstunt_rec.unit_cd) ;
1641 p_lgcy_adstunt_rec.s_adv_stnd_granting_status := upper(p_lgcy_adstunt_rec.s_adv_stnd_granting_status);
1642 p_lgcy_adstunt_rec.exemption_institution_cd := upper(p_lgcy_adstunt_rec.exemption_institution_cd);
1643 p_lgcy_adstunt_rec.s_adv_stnd_recognition_type := upper(p_lgcy_adstunt_rec.s_adv_stnd_recognition_type);
1644 p_lgcy_adstunt_rec.optional_ind := upper(nvl(p_lgcy_adstunt_rec.optional_ind,'N')); --shimitta
1645 p_lgcy_adstunt_rec.alt_unit_cd := upper(p_lgcy_adstunt_rec.alt_unit_cd);
1646
1647
1648 IF validate_parameters(
1649 p_lgcy_adstunt_rec =>p_lgcy_adstunt_rec
1650 )THEN
1651 mydebug('Before derive_unit_data');
1652 IF derive_unit_data(
1653 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec ,
1654 p_person_id => l_person_id ,
1655 p_s_adv_stnd_type => l_s_adv_stnd_type ,
1656 p_cal_type => l_cal_type,
1657 p_seq_number => l_seq_number,
1658 p_auth_pers_id => l_auth_pers_id,
1659 p_unit_details_id => l_unit_details_id ,
1660 p_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
1661 p_as_version_number => l_as_version_number,
1662 p_reference_code_id => l_reference_code_id
1663 ) THEN
1664 mydebug('*****l_unit_details_id='||l_unit_details_id);
1665 mydebug('Before validate_adv_std_db_cons');
1666 IF validate_adv_std_db_cons(
1667 p_version_number => l_as_version_number,
1668 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec
1669 ) THEN
1670 mydebug('Before validate_adv_stnd');
1671 IF validate_adv_stnd(
1672 p_person_id => l_person_id ,
1673 p_version_number => l_as_version_number,
1674 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec
1675 ) THEN
1676 mydebug('Before IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION');
1677 -- Validate that the current record is already present in the tables IGS_AV_ADV_STANDING_ALL and IGS_AV_STND_UNIT_ALL
1678 IF NOT igs_av_adv_standing_pkg.get_pk_for_validation(
1679 x_person_id => l_person_id,
1680 x_course_cd => p_lgcy_adstunt_rec.program_cd ,
1681 x_version_number => l_as_version_number,
1682 x_exemption_institution_cd => p_lgcy_adstunt_rec.exemption_institution_cd
1683 ) THEN
1684 mydebug('***** INSERT INTO IGS_AV_ADV_STANDING_ALL *****');
1685 INSERT INTO igs_av_adv_standing_all(person_id,
1686 created_by,
1687 creation_date,
1688 last_updated_by,
1689 last_update_date,
1690 last_update_login,
1691 course_cd,
1692 version_number,
1693 total_exmptn_approved,
1694 total_exmptn_granted,
1695 total_exmptn_perc_grntd,
1696 exemption_institution_cd ,
1697 org_id
1698 ) VALUES (
1699 l_person_id,
1700 NVL(FND_GLOBAL.USER_ID,-1),
1701 SYSDATE ,
1702 NVL(FND_GLOBAL.USER_ID,-1),
1703 SYSDATE ,
1704 NVL(FND_GLOBAL.LOGIN_ID,-1),
1705 upper(p_lgcy_adstunt_rec.program_cd) ,
1706 l_as_version_number,
1707 p_lgcy_adstunt_rec.total_exmptn_approved ,
1708 p_lgcy_adstunt_rec.total_exmptn_granted,
1709 p_lgcy_adstunt_rec.total_exmptn_perc_grntd,
1710 p_lgcy_adstunt_rec.exemption_institution_cd ,
1711 igs_ge_gen_003.get_org_id()
1712 );
1713 END IF; --IGS_AV_ADV_STANDING_PKG.GET_PK_FOR_VALIDATION
1714 mydebug('Before IGS_AV_STND_UNIT_PKG.GET_UK_FOR_VALIDATION');
1715 IF NOT IGS_AV_STND_UNIT_PKG.GET_UK_FOR_VALIDATION(
1716 x_person_id => l_person_id,
1717 x_exemption_institution_cd => p_lgcy_adstunt_rec.exemption_institution_cd,
1718 x_unit_details_id => l_unit_details_id,
1719 x_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
1720 x_unit_cd => p_lgcy_adstunt_rec.unit_cd,
1721 x_as_course_cd => p_lgcy_adstunt_rec.program_cd,
1722 x_as_version_number => l_as_version_number,
1723 x_version_number => p_lgcy_adstunt_rec.version_number,
1724 x_s_adv_stnd_type => l_s_adv_stnd_type
1725 ) THEN
1726 mydebug('Before validate_std_unt_db_cons');
1727 mydebug('**** l_unit_details_id='||l_unit_details_id);
1728 IF validate_std_unt_db_cons(
1729 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec,
1730 p_person_id => l_person_id,
1731 p_s_adv_stnd_type => l_s_adv_stnd_type,
1732 p_cal_type => l_cal_type,
1733 p_seq_number => l_seq_number,
1734 p_auth_pers_id => l_auth_pers_id,
1735 p_unit_details_id => l_unit_details_id,
1736 p_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
1737 p_as_version_number => l_as_version_number,
1738 p_av_stnd_unit_lvl_id => l_av_stnd_unit_lvl_id
1739 ) THEN
1740 mydebug('Before validate_unit');
1741 IF validate_unit(
1742 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec,
1743 p_person_id => l_person_id,
1744 p_s_adv_stnd_type => l_s_adv_stnd_type,
1745 p_cal_type => l_cal_type,
1746 p_seq_number => l_seq_number,
1747 p_auth_pers_id => l_auth_pers_id,
1748 p_unit_details_id => l_unit_details_id,
1749 p_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
1750 p_as_version_number => l_as_version_number
1751 ) THEN
1752 IF p_lgcy_adstunt_rec.prog_group_ind is null THEN
1753 mydebug(' INSERT INTO IGS_AV_STND_UNIT_ALL N');
1754 p_lgcy_adstunt_rec.prog_group_ind :='N';
1755 END IF;
1756
1757 mydebug(' INSERT INTO IGS_AV_STND_UNIT_ALL lgcy_adstunt_rec.prog_group_ind ');
1758
1759 L_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID ;
1760 L_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID ;
1761 L_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID ;
1762
1763 if (L_REQUEST_ID = -1) then
1764 L_REQUEST_ID := NULL ;
1765 L_PROGRAM_ID := NULL ;
1766 L_PROGRAM_APPLICATION_ID := NULL ;
1767 L_PROGRAM_UPDATE_DATE := NULL ;
1768 else
1769 L_PROGRAM_UPDATE_DATE := SYSDATE ;
1770 end if ;
1771 mydebug('***** l_av_stnd_unit_lvl_id=' || l_av_stnd_unit_lvl_id);
1772 INSERT INTO igs_av_stnd_unit_all(
1773 person_id,
1774 as_course_cd,
1775 as_version_number,
1776 s_adv_stnd_type,
1777 unit_cd,
1778 version_number,
1779 s_adv_stnd_granting_status,
1780 approved_dt,
1781 authorising_person_id,
1782 crs_group_ind,
1783 exemption_institution_cd,
1784 granted_dt,
1785 expiry_dt,
1786 cancelled_dt,
1787 revoked_dt,
1788 comments,
1789 /* credit_percentage, */
1790 s_adv_stnd_recognition_type,
1791 org_id,
1792 request_id,
1793 program_application_id,
1794 program_id,
1795 program_update_date,
1796 created_by,
1797 creation_date,
1798 last_updated_by,
1799 last_update_date,
1800 last_update_login,
1801 av_stnd_unit_id,
1802 cal_type,
1803 ci_sequence_number,
1804 institution_cd,
1805 grading_schema_cd,
1806 grd_sch_version_number,
1807 grade,
1808 achievable_credit_points,
1809 deg_aud_detail_id,
1810 unit_details_id,
1811 tst_rslt_dtls_id
1812 ) VALUES (
1813 l_person_id,
1814 upper(p_lgcy_adstunt_rec.program_cd),
1815 l_as_version_number,
1816 upper(l_s_adv_stnd_type),
1817 upper(p_lgcy_adstunt_rec.unit_cd),
1818 p_lgcy_adstunt_rec.version_number,
1819 upper(p_lgcy_adstunt_rec.s_adv_stnd_granting_status),
1820 p_lgcy_adstunt_rec.approved_dt,
1821 l_auth_pers_id,
1822 upper(p_lgcy_adstunt_rec.prog_group_ind),
1823 upper(p_lgcy_adstunt_rec.exemption_institution_cd),
1824 p_lgcy_adstunt_rec.granted_dt,
1825 p_lgcy_adstunt_rec.expiry_dt,
1826 p_lgcy_adstunt_rec.cancelled_dt,
1827 p_lgcy_adstunt_rec.revoked_dt,
1828 p_lgcy_adstunt_rec.comments,
1829 /* p_lgcy_adstunt_rec.credit_percentage, */
1830 upper(p_lgcy_adstunt_rec.s_adv_stnd_recognition_type),
1831 igs_ge_gen_003.get_org_id(),
1832 l_request_id,
1833 l_program_application_id,
1834 l_program_id,
1835 l_program_update_date,
1836 NVL(FND_GLOBAL.USER_ID,-1),
1837 SYSDATE,
1838 NVL(FND_GLOBAL.USER_ID,-1),
1839 SYSDATE,
1840 NVL(FND_GLOBAL.LOGIN_ID,-1),
1841 l_av_stnd_unit_lvl_id,
1842 l_cal_type,
1843 l_seq_number,
1844 upper(p_lgcy_adstunt_rec.institution_cd),
1845 p_lgcy_adstunt_rec.grading_schema_cd,
1846 p_lgcy_adstunt_rec.grd_sch_version_number,
1847 p_lgcy_adstunt_rec.grade,
1848 p_lgcy_adstunt_rec.achievable_credit_points,
1849 NULL ,
1850 l_unit_details_id,
1851 l_tst_rslt_dtls_id
1852 );
1853 mydebug(' Inserted into IGS_AV_STND_UNIT_ALL val AV_STND_UNIT_ID =' ||l_av_stnd_unit_lvl_id);
1854 IF NOT create_post_unit(
1855 p_person_id => l_person_id,
1856 p_course_version => l_as_version_number,
1857 p_unit_details_id => l_unit_details_id,
1858 p_tst_rslt_dtls_id => l_tst_rslt_dtls_id,
1859 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec
1860 ) THEN
1861 mydebug('Error 2');
1862 x_return_status := FND_API.G_RET_STS_ERROR;
1863 ELSE -- create_post_unit
1864 IF l_reference_code_id IS NOT NULL THEN
1865 IF validate_ref_code(
1866 p_av_stnd_unit_id => l_av_stnd_unit_lvl_id,
1867 p_reference_code_id => l_reference_code_id
1868 ) THEN
1869 mydebug('INSERT INTO IGS_AV_STD_UNT_BASIS_ALL AV_STND_UNIT_ID= '|| l_av_stnd_unit_lvl_id);
1870 OPEN c_unit_ref_id;
1871 FETCH c_unit_ref_id INTO l_AVU_REFERENCE_CD_ID;
1872 CLOSE c_unit_ref_id;
1873 INSERT INTO IGS_AV_UNT_REF_CDS(
1874 last_update_login,
1875 created_by,
1876 creation_date,
1877 last_updated_by,
1878 last_update_date,
1879 AVU_REFERENCE_CD_ID,
1880 PERSON_ID,
1881 AV_STND_UNIT_ID,
1882 REFERENCE_CODE_ID,
1883 APPLIED_COURSE_CD,
1884 DELETED_DATE
1885 )
1886 VALUES (
1887 NVL(FND_GLOBAL.LOGIN_ID,-1),
1888 NVL(FND_GLOBAL.USER_ID,-1),
1889 SYSDATE,
1890 NVL(FND_GLOBAL.USER_ID,-1),
1891 SYSDATE,
1892 l_AVU_REFERENCE_CD_ID ,
1893 l_person_id,
1894 l_av_stnd_unit_lvl_id,
1895 l_REFERENCE_CODE_ID,
1896 p_lgcy_adstunt_rec.APPLIED_PROGRAM_CD,
1897 null
1898 );
1899 ELSE -- validate_reference codes
1900 x_return_status := FND_API.G_RET_STS_ERROR;
1901 mydebug('Error 6');
1902 END IF; -- validate_ref_code
1903 END IF;
1904 mydebug('Before validate_alt_unt_db_cons');
1905 IF validate_unt_bss_db_cons(
1906 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec,
1907 p_av_stnd_unit_id => l_av_stnd_unit_lvl_id,
1908 p_s_adv_stnd_type => l_s_adv_stnd_type
1909 ) THEN
1910 mydebug('Before validate_unit_basis');
1911 IF validate_unit_basis(
1912 p_person_id => l_person_id,
1913 p_version_number => l_as_version_number,
1914 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec
1915 ) THEN
1916 mydebug('INSERT INTO IGS_AV_STD_UNT_BASIS_ALL AV_STND_UNIT_ID= '|| l_av_stnd_unit_lvl_id);
1917 INSERT INTO igs_av_std_unt_basis_all(
1918 last_update_login,
1919 created_by,
1920 creation_date,
1921 last_updated_by,
1922 last_update_date,
1923 basis_course_type,
1924 basis_year,
1925 basis_completion_ind,
1926 org_id,
1927 av_stnd_unit_id
1928 ) VALUES (
1929 NVL(FND_GLOBAL.LOGIN_ID,-1),
1930 NVL(FND_GLOBAL.USER_ID,-1),
1931 SYSDATE,
1932 NVL(FND_GLOBAL.USER_ID,-1),
1933 SYSDATE,
1934 p_lgcy_adstunt_rec.basis_program_type ,
1935 p_lgcy_adstunt_rec.basis_year,
1936 p_lgcy_adstunt_rec.basis_completion_ind,
1937 igs_ge_gen_003.get_org_id(),
1938 l_av_stnd_unit_lvl_id
1939 );
1940 ELSE -- validate_unit_basis
1941 x_return_status := FND_API.G_RET_STS_ERROR;
1942 mydebug('Error 6');
1943 END IF; -- validate_unit_basis
1944 ELSE -- validate_unt_bss_db_cons
1945 x_return_status := FND_API.G_RET_STS_ERROR;
1946 mydebug('Error 7');
1947 END IF; --validate_unt_bss_db_cons
1948 END IF; --create_post_unit
1949 ELSE -- validate_unit
1950 mydebug('Error 3');
1951 x_return_status := FND_API.G_RET_STS_ERROR;
1952 END IF; --validate_unit
1953 ELSE -- validate_std_unt_db_cons
1954 x_return_status := FND_API.G_RET_STS_ERROR;
1955 mydebug('Error 4');
1956 END IF; --validate_std_unt_db_cons
1957 ELSE
1958 IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type <> 'PRECLUSION' THEN
1959 mydebug('****IGS_AV_STND_UNIT_PKG.GET_UK_FOR_VALIDATION and s_adv_stnd_recognition_type <> PRECLUSION ');
1960 FND_MESSAGE.SET_NAME('IGS','IGS_AV_STDUNT_ALREADY_EXISTS');
1961 FND_MSG_PUB.ADD;
1962 RAISE duplicate_record_exists;
1963 END IF;
1964 END IF; --IGS_AV_STND_UNIT_PKG.GET_UK_FOR_VALIDATION
1965 mydebug('***** Preclusion *****');
1966 IF p_lgcy_adstunt_rec.s_adv_stnd_recognition_type = 'PRECLUSION' AND
1967 p_lgcy_adstunt_rec.alt_unit_cd IS NOT NULL AND
1968 p_lgcy_adstunt_rec.alt_version_number IS NOT NULL
1969 THEN
1970 IF validate_alt_unt_db_cons(
1971 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec ,
1972 p_av_stnd_unit_id => l_av_stnd_unit_lvl_id ,
1973 p_s_adv_stnd_type => l_s_adv_stnd_type ,
1974 p_person_id => l_person_id ,
1975 p_unit_details_id => l_unit_details_id ,
1976 p_tst_rslt_dtls_id => l_tst_rslt_dtls_id ,
1977 p_as_version_number => l_as_version_number ,
1978 p_av_stnd_unit_lvl_id=> l_av_stnd_unit_lvl_id ,
1979 x_return_status => x_return_status
1980 ) THEN
1981
1982 mydebug('Before validate_alt_unit');
1983 IF validate_alt_unit(
1984 p_lgcy_adstunt_rec => p_lgcy_adstunt_rec,
1985 p_av_stnd_unit_id => l_av_stnd_unit_lvl_id,
1986 p_s_adv_stnd_type => l_s_adv_stnd_type
1987 ) THEN
1988 mydebug('**** INSERT INTO IGS_AV_STND_ALT_UNIT ');
1989 INSERT INTO igs_av_stnd_alt_unit(
1990 last_update_login,
1991 created_by,
1992 creation_date,
1993 last_updated_by,
1994 last_update_date,
1995 alt_unit_cd,
1996 alt_version_number,
1997 optional_ind,
1998 av_stnd_unit_id
1999 )
2000 VALUES
2001 (
2002 NVL(FND_GLOBAL.LOGIN_ID,-1),
2003 NVL(FND_GLOBAL.USER_ID,-1),
2004 SYSDATE,
2005 NVL(FND_GLOBAL.USER_ID,-1),
2006 SYSDATE,
2007 p_lgcy_adstunt_rec.alt_unit_cd,
2008 p_lgcy_adstunt_rec.alt_version_number,
2009 upper(p_lgcy_adstunt_rec.optional_ind),
2010 l_av_stnd_unit_lvl_id
2011 );
2012 END IF; --validate_alt_unit
2013 ELSE -- validate_alt_unt_db_cons
2014 mydebug('Error 5');
2015 IF x_return_status = 'W' THEN
2016 RAISE duplicate_record_exists;
2017 ElSE
2018 RAISE FND_API.G_EXC_ERROR;
2019 END IF;
2020 END IF; -- validate_alt_unt_db_cons
2021 END IF; --l_b_av_stnd_alt_unit_pk_exist
2022 ELSE -- validate_adv_stnd
2023 x_return_status := FND_API.G_RET_STS_ERROR;
2024 mydebug('Error 8');
2025 END IF;--validate_adv_stnd
2026 ELSE -- validate_adv_std_db_cons
2027 x_return_status := FND_API.G_RET_STS_ERROR;
2028 mydebug('Error 9');
2029 END IF; --validate_adv_std_db_cons
2030 ELSE -- derive_unit_data
2031 x_return_status := FND_API.G_RET_STS_ERROR;
2032 mydebug('Error 10');
2033 END IF;-- derive_unit_data
2034 ELSE -- validate_parameters
2035 x_return_status := FND_API.G_RET_STS_ERROR;
2036 mydebug('Error 11');
2037 END IF;--validate_parameters
2038
2039 IF x_return_status IN (FND_API.G_RET_STS_ERROR,'E','W') THEN
2040 mydebug('************************ Roll Back ********************');
2041 ROLLBACK TO create_adv_stnd_unit;
2042 END IF;
2043 /*==================== End Your coding here==========*/
2044
2045
2046 --Standard check of p_commit.
2047
2048 mydebug('************************ Before Doing a COMMIT ********************');
2049 IF FND_API.to_Boolean(p_commit) AND x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2050 mydebug('************************ Doing a COMMIT ********************');
2051 commit;
2052 END IF;
2053 --Standard call to get message count and if count is 1, get message info.
2054 FND_MSG_PUB.Count_And_Get(
2055 p_count => x_msg_count,
2056 p_data => x_msg_data);
2057 EXCEPTION
2058 WHEN DUPLICATE_RECORD_EXISTS THEN
2059 ROLLBACK TO create_adv_stnd_unit;
2060 x_return_status := 'W';
2061 FND_MSG_PUB.Count_And_Get(
2062 p_count => x_msg_count,
2063 p_data => x_msg_data
2064 );
2065 WHEN FND_API.G_EXC_ERROR THEN
2066 ROLLBACK TO create_adv_stnd_unit;
2067 x_return_status := FND_API.G_RET_STS_ERROR;
2068 FND_MSG_PUB.Count_And_Get(
2069 p_count => x_msg_count,
2070 p_data => x_msg_data);
2071
2072 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2073 ROLLBACK TO create_adv_stnd_unit;
2074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2075 FND_MSG_PUB.Count_And_Get(
2076 p_count => x_msg_count,
2077 p_data => x_msg_data);
2078 WHEN OTHERS THEN
2079 ROLLBACK TO create_adv_stnd_unit;
2080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2081 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
2082 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2083 FND_MSG_PUB.ADD;
2084
2085 FND_MSG_PUB.Count_And_Get(
2086 p_count => x_msg_count,
2087 p_data => x_msg_data);
2088
2089 END create_adv_stnd_unit;
2090
2091
2092 END igs_av_unt_lgcy_pub;
2093
2094