1 PACKAGE BODY igs_he_susa_lgcy_pub AS
2 /* $Header: IGSHE23B.pls 120.0 2005/06/01 22:17:57 appldev noship $ */
3
4
5
6 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_HE_SUSA_LGCY_PUB';
7
8
9 FUNCTION validate_parameters(p_hesa_susa_rec IN hesa_susa_rec_type)
10 RETURN BOOLEAN AS
11 /*----------------------------------------------------------------------------
12 || Created By : prraj
13 || Created On : 05-11-2002
14 || Purpose : To validate the input parameters
15 || Known limitations, enhancements or remarks :
16 || Change History :
17 || Who When What
18 ------------------------------------------------------------------------------*/
19
20 l_valid_params BOOLEAN := TRUE;
21 BEGIN
22
23 -- Mandatory parameter check
24 IF p_hesa_susa_rec.person_number IS NULL THEN
25 -- Add excep to stack
26 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PER_NUM_NULL');
27 FND_MSG_PUB.ADD;
28 l_valid_params := FALSE;
29 END IF;
30
31 -- Mandatory parameter check
32 IF p_hesa_susa_rec.program_cd IS NULL THEN
33 -- Add excep to stack
34 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_NULL');
35 FND_MSG_PUB.ADD;
36 l_valid_params := FALSE;
37 END IF;
38
39 -- Mandatory parameter check
40 IF p_hesa_susa_rec.unit_set_cd IS NULL THEN
41 -- Add excep to stack
42 FND_MESSAGE.SET_NAME('IGS','IGS_HE_UNIT_SET_MAND');
43 FND_MSG_PUB.ADD;
44 l_valid_params := FALSE;
45 END IF;
46
47 RETURN l_valid_params;
48
49 END validate_parameters;
50
51
52
53 FUNCTION validate_db_cons(p_person_id IN NUMBER,
54 p_sequence_number IN NUMBER,
55 p_hesa_susa_rec IN hesa_susa_rec_type
56 ) RETURN VARCHAR2 AS
57 /*----------------------------------------------------------------------------
58 || Created By : prraj
59 || Created On : 05-11-2002
60 || Purpose : Validates the database constaints ie PK, UK and FK checks
61 || Known limitations, enhancements or remarks :
62 || Change History :
63 || Who When What
64 || jtmathew 21-Sep-2004 Added validation for new and existing fields
65 || as described in HEFD350.
66 ------------------------------------------------------------------------------*/
67
68 l_ret_value VARCHAR2(1) := 'S';
69
70
71 -- Check if the specified fte_calc_type is a valid value in igs_lookup_values
72 -- with type IGS_HE_FTE_CALC_TYPE
73 CURSOR c_fte_calc_type IS
74 SELECT 'X'
75 FROM igs_lookup_values
76 WHERE lookup_type = 'IGS_HE_FTE_CALC_TYPE'
77 AND lookup_code = p_hesa_susa_rec.fte_calc_type
78 AND enabled_flag='Y';
79 l_dummy VARCHAR2(1);
80
81
82 BEGIN
83
84 -- Primary Key validations
85 -- Check if the HESA Unit Set Attempt exist
86 IF igs_he_en_susa_pkg.get_uk_for_validation (x_person_id => p_person_id,
87 x_course_cd => p_hesa_susa_rec.program_cd,
88 x_unit_set_cd => p_hesa_susa_rec.unit_set_cd,
89 x_sequence_number => p_sequence_number
90 ) THEN
91 -- Add excep to stack
92 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUSA_DTLS_EXIST');
93 FND_MSG_PUB.ADD;
94 RETURN 'W';
95 END IF;
96
97 -- Foreign Key validations -----------------------------
98 -- Check if the Unit Set Attempt exists
99 IF NOT igs_as_su_setatmpt_pkg.get_pk_for_validation (x_person_id => p_person_id,
100 x_course_cd => p_hesa_susa_rec.program_cd,
101 x_unit_set_cd => p_hesa_susa_rec.unit_set_cd,
102 x_sequence_number => p_sequence_number
103 ) THEN
104 -- Add excep to stack
105 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUSA_REC_NEX');
106 FND_MSG_PUB.ADD;
107 l_ret_value := 'E';
108 END IF;
109
110 -- Check if the New HE Entrant exists
111 IF p_hesa_susa_rec.new_he_entrant_cd IS NOT NULL THEN
112 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_HEENT',
113 x_value => p_hesa_susa_rec.new_he_entrant_cd
114 ) THEN
115 -- Add excep to stack
116 FND_MESSAGE.SET_NAME('IGS','IGS_HE_NEW_HE_ENTRN_NEX');
117 FND_MSG_PUB.ADD;
118 l_ret_value := 'E';
119 END IF;
120 END IF;
121
122 -- Check if the Term Time Accommodation exists
123 IF p_hesa_susa_rec.term_time_accom IS NOT NULL THEN
124 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_TTA',
125 x_value => p_hesa_susa_rec.term_time_accom
126 ) THEN
127 -- Add excep to stack
128 FND_MESSAGE.SET_NAME('IGS','IGS_HE_TRM_ACCOMNEX');
129 FND_MSG_PUB.ADD;
130 l_ret_value := 'E';
131 END IF;
132 END IF;
133
134 -- Check if the Disability Allowance exists
135 IF p_hesa_susa_rec.disability_allow IS NOT NULL THEN
136 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_DIS_ALLOW',
137 x_value => p_hesa_susa_rec.disability_allow
138 ) THEN
139 -- Add excep to stack
140 FND_MESSAGE.SET_NAME('IGS','IGS_HE_DISB_ALLW_NEX');
141 FND_MSG_PUB.ADD;
142 l_ret_value := 'E';
143 END IF;
144 END IF;
145
146 -- Check if the Additional Support Band exists
147 IF p_hesa_susa_rec.additional_sup_band IS NOT NULL THEN
148 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_SUP_BAND',
149 x_value => p_hesa_susa_rec.additional_sup_band
150 ) THEN
151 -- ADD excep to stack
152 FND_MESSAGE.SET_NAME('IGS','IGS_HE_ADD_SUP_BAND_NEX');
153 FND_MSG_PUB.ADD;
154 l_ret_value := 'E';
155 END IF;
156 END IF;
157
158 -- Check if the SLDD discrete prov exists
159 IF p_hesa_susa_rec.sldd_discrete_prov IS NOT NULL THEN
160 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_ST13',
161 x_value => p_hesa_susa_rec.sldd_discrete_prov
162 ) THEN
163 -- Add excep to stack
164 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SLDD_DISCR_NEX');
165 FND_MSG_PUB.ADD;
166 l_ret_value := 'E';
167 END IF;
168 END IF;
169
170 -- Check if the Study Mode exists
171 IF p_hesa_susa_rec.study_mode IS NOT NULL THEN
172 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_MODE_TYPE',
173 x_value => p_hesa_susa_rec.study_mode
174 ) THEN
175 -- ADD excep to stack
176 FND_MESSAGE.SET_NAME('IGS','IGS_HE_STUDY_MODE_NEX');
177 FND_MSG_PUB.ADD;
178 l_ret_value := 'E';
179 END IF;
180 END IF;
181
182 -- Check if the Study Location exists
183 IF p_hesa_susa_rec.study_location IS NOT NULL THEN
184 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_LOCSDY',
185 x_value => p_hesa_susa_rec.study_location
186 ) THEN
187 -- ADD excep to stack
188 FND_MESSAGE.SET_NAME('IGS','IGS_HE_STUDY_LOC_NEX');
189 FND_MSG_PUB.ADD;
190 l_ret_value := 'E';
191 END IF;
192 END IF;
193
194 -- Check if the Franchising Activity exists
195 IF p_hesa_susa_rec.franchising_activity IS NOT NULL THEN
196 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_FRAN_ACT',
197 x_value => p_hesa_susa_rec.franchising_activity
198 ) THEN
199 -- ADD excep to stack
200 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FRANCH_ACT_NEX');
201 FND_MSG_PUB.ADD;
202 l_ret_value := 'E';
203 END IF;
204 END IF;
205
206 -- Check if the Completion Status exists
207 IF p_hesa_susa_rec.completion_status IS NOT NULL THEN
208 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_CSTAT',
209 x_value => p_hesa_susa_rec.completion_status
210 ) THEN
211 -- ADD excep to stack
212 FND_MESSAGE.SET_NAME('IGS','IGS_HE_COMP_STATUS_NEX');
213 FND_MSG_PUB.ADD;
214 l_ret_value := 'E';
215 END IF;
216 END IF;
217
218 -- Check if the Good Standing Marker exists
219 IF p_hesa_susa_rec.good_stand_marker IS NOT NULL THEN
220 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_PROGRESS',
221 x_value => p_hesa_susa_rec.good_stand_marker
222 ) THEN
223 -- ADD excep to stack
224 FND_MESSAGE.SET_NAME('IGS','IGS_HE_GOOD_STAND_NEX');
225 FND_MSG_PUB.ADD;
226 l_ret_value := 'E';
227 END IF;
228 END IF;
229
230 -- Check if the Complete PYR Study exists
231 IF p_hesa_susa_rec.complete_pyr_study_cd IS NOT NULL THEN
232 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_FUNDCOMP',
233 x_value => p_hesa_susa_rec.complete_pyr_study_cd
234 ) THEN
235 -- ADD excep to stack
236 FND_MESSAGE.SET_NAME('IGS','IGS_HE_COMP_PYR_STUDY_NEX');
237 FND_MSG_PUB.ADD;
238 l_ret_value := 'E';
239 END IF;
240 END IF;
241
242 -- Check if the Fundability Code exists
243 IF p_hesa_susa_rec.fundability_code IS NOT NULL THEN
244 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_FUND_CODE',
245 x_value => p_hesa_susa_rec.fundability_code
246 ) THEN
247 -- ADD excep to stack
248 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FUNDB_CODE_NEX');
249 FND_MSG_PUB.ADD;
250 l_ret_value := 'E';
251 END IF;
252 END IF;
253
254 -- Check if the Fee Eligibility exists
255 IF p_hesa_susa_rec.fee_eligibility IS NOT NULL THEN
256 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_FEEELIG',
257 x_value => p_hesa_susa_rec.fee_eligibility
258 ) THEN
259 -- ADD excep to stack
260 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FEE_ELGBL_NEX');
261 FND_MSG_PUB.ADD;
262 l_ret_value := 'E';
263 END IF;
264 END IF;
265
266 -- Check if the Fee Band exists
267 IF p_hesa_susa_rec.fee_band IS NOT NULL THEN
268 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_FEEBAND',
269 x_value => p_hesa_susa_rec.fee_band
270 ) THEN
271 -- ADD excep to stack
272 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FEE_BAND_NEX');
273 FND_MSG_PUB.ADD;
274 l_ret_value := 'E';
275 END IF;
276 END IF;
277
278 -- Check if the Non Payment Reason exists
279 IF p_hesa_susa_rec.non_payment_reason IS NOT NULL THEN
280 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_NONPAY',
281 x_value => p_hesa_susa_rec.non_payment_reason
282 ) THEN
283 -- ADD excep to stack
284 FND_MESSAGE.SET_NAME('IGS','IGS_HE_NOPAY_REASON_NEX');
285 FND_MSG_PUB.ADD;
286 l_ret_value := 'E';
287 END IF;
288 END IF;
289
290 -- Check if the Student Fee exists
291 IF p_hesa_susa_rec.student_fee IS NOT NULL THEN
292 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_MSTUFEE',
293 x_value => p_hesa_susa_rec.student_fee
294 ) THEN
295 -- ADD excep to stack
296 FND_MESSAGE.SET_NAME('IGS','IGS_HE_STUD_FEE_NEX');
297 FND_MSG_PUB.ADD;
298 l_ret_value := 'E';
299 END IF;
300 END IF;
301
302 -- Check if the Type of Program Year exists
303 IF p_hesa_susa_rec.type_of_year IS NOT NULL THEN
304 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_TYPEYR',
305 x_value => p_hesa_susa_rec.type_of_year
306 ) THEN
307 -- ADD excep to stack
308 FND_MESSAGE.SET_NAME('IGS','IGS_HE_TYPE_OF_YEAR_NEX');
309 FND_MSG_PUB.ADD;
310 l_ret_value := 'E';
311 END IF;
312 END IF;
313
314 -- Check if the Eligibility for Enhanced Funding Indicator exists
315 IF p_hesa_susa_rec.enh_fund_elig_cd IS NOT NULL THEN
316 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_ELIGENFD',
317 x_value => p_hesa_susa_rec.enh_fund_elig_cd
318 ) THEN
319 -- ADD excep to stack
320 FND_MESSAGE.SET_NAME('IGS','IGS_HE_ELIG_ENH_FUND_NEX');
321 FND_MSG_PUB.ADD;
322 l_ret_value := 'E';
323 END IF;
324 END IF;
325
326 -- Check if the Credit Level Achieved 1 exists
327 IF p_hesa_susa_rec.credit_level_achieved1 IS NOT NULL THEN
328 IF NOT igs_ps_unit_level_pkg.get_pk_for_validation (x_unit_level => p_hesa_susa_rec.credit_level_achieved1) THEN
329 -- ADD excep to stack
330 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CR_LEVEL_ACHD1_NEX');
331 FND_MSG_PUB.ADD;
332 l_ret_value := 'E';
333 END IF;
334 END IF;
335
336 -- Check if the Credit Level Achieved 2 exists
337 IF p_hesa_susa_rec.credit_level_achieved2 IS NOT NULL THEN
338 IF NOT igs_ps_unit_level_pkg.get_pk_for_validation (x_unit_level => p_hesa_susa_rec.credit_level_achieved2) THEN
339 -- ADD excep to stack
340 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CR_LEVEL_ACHD2_NEX');
341 FND_MSG_PUB.ADD;
342 l_ret_value := 'E';
343 END IF;
344 END IF;
345
346 -- Check if the Credit Level Achieved 3 exists
347 IF p_hesa_susa_rec.credit_level_achieved3 IS NOT NULL THEN
348 IF NOT igs_ps_unit_level_pkg.get_pk_for_validation (x_unit_level => p_hesa_susa_rec.credit_level_achieved3) THEN
349 -- ADD excep to stack
350 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CR_LEVEL_ACHD3_NEX');
351 FND_MSG_PUB.ADD;
352 l_ret_value := 'E';
353 END IF;
354 END IF;
355
356 -- Check if the Credit Level Achieved 4 exists
357 IF p_hesa_susa_rec.credit_level_achieved4 IS NOT NULL THEN
358 IF NOT igs_ps_unit_level_pkg.get_pk_for_validation (x_unit_level => p_hesa_susa_rec.credit_level_achieved4) THEN
359 -- ADD excep to stack
360 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CR_LEVEL_ACHD4_NEX');
361 FND_MSG_PUB.ADD;
362 l_ret_value := 'E';
363 END IF;
364 END IF;
365
366 -- Check if the Credit Level 1 exists
367 IF p_hesa_susa_rec.credit_level1 IS NOT NULL THEN
368 IF NOT igs_ps_unit_level_pkg.get_pk_for_validation (x_unit_level => p_hesa_susa_rec.credit_level1) THEN
369 -- ADD excep to stack
370 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CREDIT_LEVEL1_NEX');
371 FND_MSG_PUB.ADD;
372 l_ret_value := 'E';
373 END IF;
374 END IF;
375
376 -- Check if the Credit Level 2 exists
377 IF p_hesa_susa_rec.credit_level2 IS NOT NULL THEN
378 IF NOT igs_ps_unit_level_pkg.get_pk_for_validation (x_unit_level => p_hesa_susa_rec.credit_level2) THEN
379 -- ADD excep to stack
380 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CREDIT_LEVEL2_NEX');
381 FND_MSG_PUB.ADD;
382 l_ret_value := 'E';
383 END IF;
384 END IF;
385
386 -- Check if the Credit Level 3 exists
387 IF p_hesa_susa_rec.credit_level3 IS NOT NULL THEN
388 IF NOT igs_ps_unit_level_pkg.get_pk_for_validation (x_unit_level => p_hesa_susa_rec.credit_level3) THEN
389 -- ADD excep to stack
390 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CREDIT_LEVEL3_NEX');
391 FND_MSG_PUB.ADD;
392 l_ret_value := 'E';
393 END IF;
394 END IF;
395
396 -- Check if the Credit Level 4 exists
397 IF p_hesa_susa_rec.credit_level4 IS NOT NULL THEN
398 IF NOT igs_ps_unit_level_pkg.get_pk_for_validation (x_unit_level => p_hesa_susa_rec.credit_level4) THEN
399 -- ADD excep to stack
400 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CREDIT_LEVEL4_NEX');
401 FND_MSG_PUB.ADD;
402 l_ret_value := 'E';
403 END IF;
404 END IF;
405
406 -- bug #3547382
407 -- Check whether the Credit Value Year of Program 1 field
408 -- is within range 0 to 999 (inclusive)
409 IF p_hesa_susa_rec.credit_value_yop1 IS NOT NULL THEN
410 IF NOT p_hesa_susa_rec.credit_value_yop1 between 0 and 999 THEN
411 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CV_YOP1_RANGE_INVALID');
412 FND_MSG_PUB.ADD;
413 l_ret_value := 'E';
414 END IF;
415 END IF;
416
417 -- bug #3547382
418 -- Check whether the Credit Value Year of Program 2 field
419 -- is within range 0 to 999 (inclusive)
420 IF p_hesa_susa_rec.credit_value_yop2 IS NOT NULL THEN
421 IF NOT p_hesa_susa_rec.credit_value_yop2 between 0 and 999 THEN
422 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CV_YOP2_RANGE_INVALID');
423 FND_MSG_PUB.ADD;
424 l_ret_value := 'E';
425 END IF;
426 END IF;
427
428 -- Check whether the Credit Value Year of Program 3 field
429 -- is within range 0 to 999 (inclusive)
430 IF p_hesa_susa_rec.credit_value_yop3 IS NOT NULL THEN
431 IF NOT p_hesa_susa_rec.credit_value_yop3 between 0 and 999 THEN
432 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CV_YOP3_RANGE_INVALID');
433 FND_MSG_PUB.ADD;
434 l_ret_value := 'E';
435 END IF;
436 END IF;
437
438 -- Check whether the Credit Value Year of Program 4 field
439 -- is within range 0 to 999 (inclusive)
440 IF p_hesa_susa_rec.credit_value_yop4 IS NOT NULL THEN
441 IF NOT p_hesa_susa_rec.credit_value_yop4 between 0 and 999 THEN
442 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CV_YOP4_RANGE_INVALID');
443 FND_MSG_PUB.ADD;
444 l_ret_value := 'E';
445 END IF;
446 END IF;
447
448 -- Check whether the Year Of Student field is within range 0 to 39 (inclusive)
449 IF p_hesa_susa_rec.year_stu IS NOT NULL THEN
450 IF NOT p_hesa_susa_rec.year_stu between 0 and 39 THEN
451 FND_MESSAGE.SET_NAME('IGS','IGS_HE_YEAR_STU_INVALID');
452 FND_MSG_PUB.ADD;
453 l_ret_value := 'E';
454 END IF;
455 END IF;
456
457 -- bug #3547394
458 -- Check whether the Number of Credit Points obtained 1 field
459 -- is within range 0 to 999 (inclusive)
460 IF p_hesa_susa_rec.credit_pt_achieved1 IS NOT NULL THEN
461 IF NOT p_hesa_susa_rec.credit_pt_achieved1 between 0 and 999 THEN
462 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CP_ACH1_INVALID');
463 FND_MSG_PUB.ADD;
464 l_ret_value := 'E';
465 END IF;
466 END IF;
467
468 -- bug #3547394
469 -- Check whether the Number of Credit Points obtained 2 field
470 -- is within range 0 to 999 (inclusive)
471 IF p_hesa_susa_rec.credit_pt_achieved2 IS NOT NULL THEN
472 IF NOT p_hesa_susa_rec.credit_pt_achieved2 between 0 and 999 THEN
473 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CP_ACH2_INVALID');
474 FND_MSG_PUB.ADD;
475 l_ret_value := 'E';
476 END IF;
477 END IF;
478
479 -- Check whether the Number of Credit Points obtained 3 field
480 -- is within range 0 to 999 (inclusive)
481 IF p_hesa_susa_rec.credit_pt_achieved3 IS NOT NULL THEN
482 IF NOT p_hesa_susa_rec.credit_pt_achieved3 between 0 and 999 THEN
483 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CP_ACH3_INVALID');
484 FND_MSG_PUB.ADD;
485 l_ret_value := 'E';
486 END IF;
487 END IF;
488
489 -- Check whether the Number of Credit Points obtained 4 field
490 -- is within range 0 to 999 (inclusive)
491 IF p_hesa_susa_rec.credit_pt_achieved4 IS NOT NULL THEN
492 IF NOT p_hesa_susa_rec.credit_pt_achieved4 between 0 and 999 THEN
493 FND_MESSAGE.SET_NAME('IGS','IGS_HE_CP_ACH4_INVALID');
494 FND_MSG_PUB.ADD;
495 l_ret_value := 'E';
496 END IF;
497 END IF;
498
499 -- bug #3547402
500 -- Check whether the Proportion not taught by institution field
501 -- is within the range 0 to 100 (inclusive)
502 IF p_hesa_susa_rec.pro_not_taught IS NOT NULL THEN
503 IF NOT p_hesa_susa_rec.pro_not_taught between 0 and 100 THEN
504 FND_MESSAGE.SET_NAME('IGS','IGS_HE_PRO_NOT_TAUGHT_INVALID');
505 FND_MSG_PUB.ADD;
506 l_ret_value := 'E';
507 END IF;
508 END IF;
509
510 -- bug #3547402
511 -- Check whether the FTE Intensity field is within range 0 to 300 (inclusive)
512 IF p_hesa_susa_rec.fte_intensity IS NOT NULL THEN
513 IF NOT p_hesa_susa_rec.fte_intensity between 0 and 300 THEN
514 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FTE_INTENSITY_INVALID');
515 FND_MSG_PUB.ADD;
516 l_ret_value := 'E';
517 END IF;
518 END IF;
519
520 -- bug #3547416
521 -- Check whether the FTE Calculation type is a valid value from IGS Lookups with type
522 -- IGS_HE_FTE_CALC_TYPE
523 IF p_hesa_susa_rec.fte_calc_type IS NOT NULL THEN
524 OPEN c_fte_calc_type;
525 FETCH c_fte_calc_type INTO l_dummy;
526 IF c_fte_calc_type%NOTFOUND THEN
527 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FTE_CTYPE_RANGE_NEX');
528 FND_MSG_PUB.ADD;
529 l_ret_value := 'E';
530 END IF;
531 CLOSE c_fte_calc_type;
532 END IF;
533
534 -- bug #3547420
535 -- Check whether the FTE Intensity field is within range 0 to 300 (inclusive)
536 IF p_hesa_susa_rec.fte_perc_override IS NOT NULL THEN
537 IF NOT p_hesa_susa_rec.fte_perc_override between 0 and 300 THEN
538 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FTE_PERC_OVR_INVALID');
539 FND_MSG_PUB.ADD;
540 l_ret_value := 'E';
541 END IF;
542 END IF;
543
544 -- Check whether the Additional Support Cost field is within range 0 to 999999 (inclusive)
545 IF p_hesa_susa_rec.additional_sup_cost IS NOT NULL THEN
546 IF NOT p_hesa_susa_rec.additional_sup_cost between 0 and 999999 THEN
547 FND_MESSAGE.SET_NAME('IGS','IGS_HE_ADD_SUPP_COST_INVALID');
548 FND_MSG_PUB.ADD;
549 l_ret_value := 'E';
550 END IF;
551 END IF;
552
553 -- Check whether the Disadvantage Uplift Factor field is within
554 -- range 0.0000 to 9.9999 (inclusive)
555 IF p_hesa_susa_rec.disadv_uplift_factor IS NOT NULL THEN
556 IF NOT p_hesa_susa_rec.disadv_uplift_factor between 0.0000 and 9.9999 THEN
557 FND_MESSAGE.SET_NAME('IGS','IGS_HE_DIS_UPLIFT_FTR_INVALID');
558 FND_MSG_PUB.ADD;
559 l_ret_value := 'E';
560 END IF;
561 END IF;
562
563 -- Check if the Grading Schema Grade exists
564 IF p_hesa_susa_rec.grad_sch_grade IS NOT NULL THEN
565 IF NOT igs_en_hesa_pkg.check_grading_sch_grade (p_person_id => p_person_id,
566 p_program_cd => p_hesa_susa_rec.program_cd,
567 p_unit_set_cd => p_hesa_susa_rec.unit_set_cd,
568 p_grad_sch_grade => p_hesa_susa_rec.grad_sch_grade) THEN
569 -- ADD excep to stack
570 FND_MESSAGE.SET_NAME('IGS','IGS_HE_GRD_SCH_GRADE_NEX');
571 FND_MSG_PUB.ADD;
572 l_ret_value := 'E';
573 END IF;
574 END IF;
575
576 -- Check if the Teaching Institution 1 exists
577 IF p_hesa_susa_rec.teaching_inst1 IS NOT NULL THEN
578 IF NOT igs_en_hesa_pkg.check_teach_inst (p_teaching_inst => p_hesa_susa_rec.teaching_inst1) THEN
579 -- ADD excep to stack
580 FND_MESSAGE.SET_NAME('IGS','IGS_HE_TCH_INST1_POSTSEC_NEX');
581 FND_MSG_PUB.ADD;
582 l_ret_value := 'E';
583 END IF;
584 END IF;
585
586 -- Check if the Teaching Institution 2 exists
587 IF p_hesa_susa_rec.teaching_inst2 IS NOT NULL THEN
588 IF NOT igs_en_hesa_pkg.check_teach_inst (p_teaching_inst => p_hesa_susa_rec.teaching_inst2) THEN
589 -- ADD excep to stack
590 FND_MESSAGE.SET_NAME('IGS','IGS_HE_TCH_INST2_POSTSEC_NEX');
591 FND_MSG_PUB.ADD;
592 l_ret_value := 'E';
593 END IF;
594 END IF;
595
596 RETURN l_ret_value;
597
598 END validate_db_cons;
599
600
601
602 FUNCTION validate_hesa_susa (p_us_version_number IN NUMBER,
603 p_hesa_susa_rec IN hesa_susa_rec_type
604 ) RETURN BOOLEAN AS
605 l_br_val_failed BOOLEAN := FALSE;
606 /*----------------------------------------------------------------------------
607 || Created By : prraj
608 || Created On : 05-11-2002
609 || Purpose : Perform business validations for the HESA Student Unit Set Attempt
610 || Known limitations, enhancements or remarks :
611 || Change History :
612 || Who When What
613 ------------------------------------------------------------------------------*/
614 BEGIN
615
616 -- Validating whether the unit set category is of type Pre-enrollment
617 IF 'PRENRL_YR' <> igs_en_hesa_pkg.get_unit_set_cat (p_unit_set_cd => p_hesa_susa_rec.unit_set_cd,
618 p_us_version_number => p_us_version_number) THEN
619 -- ADD excep to stack
620 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUSA_PRE_YEAR_US');
621 FND_MSG_PUB.ADD;
622 l_br_val_failed := TRUE;
623 END IF;
624
625 -- Check whether any validation failed, if yes then return FALSE otherwise return TRUE
626 IF l_br_val_failed THEN
627 RETURN FALSE;
628 ELSE
629 RETURN TRUE;
630 END IF;
631
632 END validate_hesa_susa;
633
634
635 PROCEDURE create_hesa_susa (p_api_version IN NUMBER,
636 p_init_msg_list IN VARCHAR2,
637 p_commit IN VARCHAR2,
638 p_validation_level IN NUMBER,
639 p_hesa_susa_rec IN hesa_susa_rec_type,
640 x_return_status OUT NOCOPY VARCHAR2,
641 x_msg_count OUT NOCOPY NUMBER,
642 x_msg_data OUT NOCOPY VARCHAR2) AS
643
644 /*----------------------------------------------------------------------------
645 || Created By : prraj
646 || Created On : 05-11-2002
647 || Purpose : To create a HESA Student Unit Set Attempt
648 || Known limitations, enhancements or remarks :
649 || Change History :
650 || Who When What
651 || jtmathew 21-Sep-2004 Modified INSERT statement to accommodate the new
652 || fields described in HEFD350.
653 ------------------------------------------------------------------------------*/
654
655 l_api_name CONSTANT VARCHAR2(30) := 'create_hesa_susa';
656 l_api_version CONSTANT NUMBER := 1.0;
657
658 l_insert_flag BOOLEAN := TRUE;
659 l_ret_val VARCHAR2(1) := NULL;
660
661 l_person_id igs_he_en_susa.person_id%TYPE;
662 l_sequence_number igs_he_en_susa.sequence_number%TYPE;
663 l_us_version_number igs_he_en_susa.us_version_number%TYPE;
664 l_hesa_en_susa_id igs_he_en_susa.hesa_en_susa_id%TYPE;
665
666 l_creation_date igs_he_en_susa.creation_date%TYPE;
667 l_last_update_date igs_he_en_susa.last_update_date%TYPE;
668 l_created_by igs_he_en_susa.created_by%TYPE;
669 l_last_updated_by igs_he_en_susa.last_updated_by%TYPE;
670 l_last_update_login igs_he_en_susa.last_update_login%TYPE;
671
672 BEGIN
673
674 -- Create a savepoint
675 SAVEPOINT create_hesa_susa_pub;
676
677 -- Check for the Compatible API call
678 IF NOT FND_API.COMPATIBLE_API_CALL( l_api_version,
679 p_api_version,
680 l_api_name,
681 g_pkg_name) THEN
682
683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
684 END IF;
685
686 -- If the calling program has passed the parameter for initializing the message list
687 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
688 FND_MSG_PUB.INITIALIZE;
689 END IF;
690
691 -- Set the return status to success
692 x_return_status := FND_API.G_RET_STS_SUCCESS;
693
694
695 -- Check whether the country profile value is GB
696 IF NVL(FND_PROFILE.VALUE('OSS_COUNTRY_CODE'),'NONE') <> 'GB' THEN
697 FND_MESSAGE.SET_NAME ('IGS','IGS_UC_HE_NOT_ENABLED');
698 FND_MSG_PUB.ADD;
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 l_insert_flag := FALSE;
701 END IF;
702
703
704 -- // Validate input paramaters ---------
705 IF l_insert_flag THEN
706 IF NOT validate_parameters(p_hesa_susa_rec) THEN
707 x_return_status := FND_API.G_RET_STS_ERROR;
708 l_insert_flag := FALSE;
709 END IF;
710 END IF;
711
712
713 -- Derivations ----------------------------------
714
715 -- Person ID
716 IF l_insert_flag THEN
717 l_person_id := igs_ge_gen_003.get_person_id (p_person_number => p_hesa_susa_rec.person_number);
718
719 IF l_person_id IS NULL THEN
720 -- ADD excep to stack
721 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_PERSON_NUMBER');
722 FND_MSG_PUB.ADD;
723 x_return_status := FND_API.G_RET_STS_ERROR;
724 l_insert_flag := FALSE;
725 END IF;
726 END IF;
727
728 -- Sequence number and unit set version number
729 IF l_insert_flag THEN
730 igs_ge_gen_003.get_susa_sequence_num (p_person_id => l_person_id,
731 p_program_cd => p_hesa_susa_rec.program_cd,
732 p_unit_set_cd => p_hesa_susa_rec.unit_set_cd,
733 p_us_version_number => l_us_version_number,
734 p_sequence_number => l_sequence_number);
735
736 IF l_us_version_number IS NULL OR l_sequence_number IS NULL THEN
737 -- ADD excep to stack
738 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUSA_REC_NEX');
739 FND_MSG_PUB.ADD;
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 l_insert_flag := FALSE;
742 END IF;
743 END IF;
744
745
746 -- Validate database constraints
747 IF l_insert_flag THEN
748 l_ret_val := validate_db_cons(p_person_id => l_person_id,
749 p_sequence_number => l_sequence_number,
750 p_hesa_susa_rec => p_hesa_susa_rec);
751
752 IF l_ret_val = 'E' THEN
753 x_return_status := FND_API.G_RET_STS_ERROR;
754 l_insert_flag := FALSE;
755 ELSIF l_ret_val = 'W' THEN
756 x_return_status := 'W';
757 l_insert_flag := FALSE;
758 END IF;
759 END IF;
760
761
762 -- Business validation
763 IF l_insert_flag THEN
764 IF NOT validate_hesa_susa (p_us_version_number => l_us_version_number,
765 p_hesa_susa_rec => p_hesa_susa_rec) THEN
766 x_return_status := FND_API.G_RET_STS_ERROR;
767 l_insert_flag := FALSE;
768 END IF;
769 END IF;
770
771
772
773 -- Perform direct insert on IGS_HE_EN_SUSA
774 IF l_insert_flag THEN
775
776 l_creation_date := SYSDATE;
777 l_created_by := FND_GLOBAL.USER_ID;
778
779 l_last_update_date := SYSDATE;
780 l_last_updated_by := FND_GLOBAL.USER_ID;
781 l_last_update_login :=FND_GLOBAL.LOGIN_ID;
782
783 IF l_created_by IS NULL THEN
784 l_created_by := -1;
785 END IF;
786
787 IF l_last_updated_by IS NULL THEN
788 l_last_updated_by := -1;
789 END IF;
790
791 IF l_last_update_login IS NULL THEN
792 l_last_update_login := -1;
793 END IF;
794
795 -- Derive HESA_EN_SUSA_ID from sequence
796 SELECT igs_he_en_susa_s.NEXTVAL
797 INTO l_hesa_en_susa_id
798 FROM dual;
799
800
801 INSERT INTO igs_he_en_susa (
802 hesa_en_susa_id,
803 person_id,
804 course_cd,
805 unit_set_cd,
806 us_version_number,
807 sequence_number,
808 new_he_entrant_cd,
809 term_time_accom,
810 disability_allow,
811 additional_sup_band,
812 sldd_discrete_prov,
813 study_mode,
814 study_location,
815 fte_perc_override,
816 franchising_activity,
817 completion_status,
818 good_stand_marker,
819 complete_pyr_study_cd,
820 credit_value_yop1,
821 credit_value_yop2,
822 credit_value_yop3,
823 credit_value_yop4,
824 credit_level_achieved1,
825 credit_level_achieved2,
826 credit_level_achieved3,
827 credit_level_achieved4,
828 credit_pt_achieved1,
829 credit_pt_achieved2,
830 credit_pt_achieved3,
831 credit_pt_achieved4,
832 credit_level1,
833 credit_level2,
834 credit_level3,
835 credit_level4,
836 grad_sch_grade,
837 mark,
838 teaching_inst1,
839 teaching_inst2,
840 pro_not_taught,
841 fundability_code,
842 fee_eligibility,
843 fee_band,
844 non_payment_reason,
845 student_fee,
846 fte_intensity,
847 calculated_fte,
848 fte_calc_type,
849 type_of_year,
850 year_stu,
851 enh_fund_elig_cd,
852 additional_sup_cost,
853 disadv_uplift_factor,
854 creation_date,
855 created_by,
856 last_update_date,
857 last_updated_by,
858 last_update_login)
859 VALUES (
860 l_hesa_en_susa_id,
861 l_person_id,
862 p_hesa_susa_rec.program_cd,
863 p_hesa_susa_rec.unit_set_cd,
864 l_us_version_number,
865 l_sequence_number,
866 p_hesa_susa_rec.new_he_entrant_cd,
867 p_hesa_susa_rec.term_time_accom,
868 p_hesa_susa_rec.disability_allow,
869 p_hesa_susa_rec.additional_sup_band,
870 p_hesa_susa_rec.sldd_discrete_prov,
871 p_hesa_susa_rec.study_mode,
872 p_hesa_susa_rec.study_location,
873 p_hesa_susa_rec.fte_perc_override,
874 p_hesa_susa_rec.franchising_activity,
875 p_hesa_susa_rec.completion_status,
876 p_hesa_susa_rec.good_stand_marker,
877 p_hesa_susa_rec.complete_pyr_study_cd,
878 p_hesa_susa_rec.credit_value_yop1,
879 p_hesa_susa_rec.credit_value_yop2,
880 p_hesa_susa_rec.credit_value_yop3,
881 p_hesa_susa_rec.credit_value_yop4,
882 p_hesa_susa_rec.credit_level_achieved1,
883 p_hesa_susa_rec.credit_level_achieved2,
884 p_hesa_susa_rec.credit_level_achieved3,
885 p_hesa_susa_rec.credit_level_achieved4,
886 p_hesa_susa_rec.credit_pt_achieved1,
887 p_hesa_susa_rec.credit_pt_achieved2,
888 p_hesa_susa_rec.credit_pt_achieved3,
889 p_hesa_susa_rec.credit_pt_achieved4,
890 p_hesa_susa_rec.credit_level1,
891 p_hesa_susa_rec.credit_level2,
892 p_hesa_susa_rec.credit_level3,
893 p_hesa_susa_rec.credit_level4,
894 p_hesa_susa_rec.grad_sch_grade,
895 p_hesa_susa_rec.mark,
896 p_hesa_susa_rec.teaching_inst1,
897 p_hesa_susa_rec.teaching_inst2,
898 p_hesa_susa_rec.pro_not_taught,
899 p_hesa_susa_rec.fundability_code,
900 p_hesa_susa_rec.fee_eligibility,
901 p_hesa_susa_rec.fee_band,
902 p_hesa_susa_rec.non_payment_reason,
903 p_hesa_susa_rec.student_fee,
904 p_hesa_susa_rec.fte_intensity,
905 p_hesa_susa_rec.calculated_fte,
906 p_hesa_susa_rec.fte_calc_type,
907 p_hesa_susa_rec.type_of_year,
908 p_hesa_susa_rec.year_stu,
909 p_hesa_susa_rec.enh_fund_elig_cd,
910 p_hesa_susa_rec.additional_sup_cost,
911 p_hesa_susa_rec.disadv_uplift_factor,
912 l_creation_date,
913 l_created_by,
914 l_last_update_date,
915 l_last_updated_by,
916 l_last_update_login);
917
918 ELSE
919 ROLLBACK TO create_hesa_susa_pub;
920 END IF;
921
922
923
924 -- If the calling program has passed the parameter for committing the data and there
925 -- have been no errors in calling the balances process, then commit the work
926 IF ( (FND_API.TO_BOOLEAN(p_commit)) AND (l_insert_flag) ) THEN
927 COMMIT WORK;
928 END IF;
929
930
931 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
932 p_data => x_msg_data);
933
934
935 EXCEPTION
936 WHEN FND_API.G_EXC_ERROR THEN
937 ROLLBACK TO create_hesa_susa_pub;
938 x_return_status := FND_API.G_RET_STS_ERROR;
939 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
940 p_data => x_msg_data);
941 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
942 ROLLBACK TO create_hesa_susa_pub;
943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
944 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
945 p_data => x_msg_data);
946 WHEN OTHERS THEN
947 ROLLBACK TO create_hesa_susa_pub;
948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
949 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
950 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,
951 l_api_name);
952 END IF;
953 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
954 p_data => x_msg_data);
955
956
957 END create_hesa_susa;
958
959
960
961 END igs_he_susa_lgcy_pub;
[Home] [Help]