1 PACKAGE BODY IGS_HE_SPA_LGCY_PUB AS
2 /* $Header: IGSHE22B.pls 120.1 2006/02/07 14:53:56 jbaber noship $ */
3
4 -- capture the package name in global variable
5 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_HE_SPA_LGCY_PUB';
6
7 /*------------------------------------------------------------------
8 --Created by : knaraset, Oracle IDC
9 --Date created: 14-Nov-2002
10 --
11 --Purpose: Function to validate the mandatory parameters
12 --
13 --Known limitations/enhancements and/or remarks:
14 --
15 --Change History:
16 --Who When What
17 --
18 ------------------------------------------------------------------ */
19 FUNCTION validate_parameters(
20 p_hesa_spa_stats_rec IN hesa_spa_rec_type)
21 RETURN BOOLEAN AS
22
23 l_ret_status BOOLEAN := TRUE;
24 BEGIN
25 IF p_hesa_spa_stats_rec.person_number IS NULL THEN
26 l_ret_status := FALSE;
27 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PER_NUM_NULL');
28 FND_MSG_PUB.ADD;
29 END IF;
30 IF p_hesa_spa_stats_rec.program_cd IS NULL THEN
31 l_ret_status := FALSE;
32 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_NULL');
33 FND_MSG_PUB.ADD;
34 END IF;
35 IF p_hesa_spa_stats_rec.student_inst_number IS NULL THEN
36 l_ret_status := FALSE;
37 FND_MESSAGE.SET_NAME('IGS','IGS_HE_STD_INST_NUM_MAND');
38 FND_MSG_PUB.ADD;
39 END IF;
40
41 RETURN l_ret_status;
42 END validate_parameters;
43
44 /*------------------------------------------------------------------
45 --Created by : knaraset, Oracle IDC
46 --Date created: 14-Nov-2002
47 --
48 --Purpose: Function to validate the data base constraints like
49 -- primary key, unique key, foreign key and check constraints.
50 --
51 --Known limitations/enhancements and/or remarks:
52 --
53 --Change History:
54 --Who When What
55 --jtmathew 21-Sep-2004 Added validation for the new fields
56 -- described in HEFD350.
57 ------------------------------------------------------------------ */
58 FUNCTION validate_db_cons(
59 p_person_id IN hz_parties.party_id%TYPE,
60 p_version_number IN igs_en_stdnt_ps_att.version_number%TYPE,
61 p_hesa_spa_stats_rec IN hesa_spa_rec_type)
62 RETURN VARCHAR2 AS
63
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(4000);
66 l_db_val_failed BOOLEAN := FALSE;
67 BEGIN
68
69 -- Check whether Hesa program statistics details already exists for the given program attempt
70 IF igs_he_st_spa_all_pkg.get_uk_for_validation(x_person_id => p_person_id,
71 x_course_cd => p_hesa_spa_stats_rec.program_cd) THEN
72 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SPA_STATS_EXIST');
73 FND_MSG_PUB.ADD;
74 RETURN 'W';
75 END IF;
76
77 -- start of Foreign Key validations
78 --
79 -- Check whether the program attempt exists
80 IF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation(x_person_id => p_person_id,
81 x_course_cd => p_hesa_spa_stats_rec.program_cd) THEN
82 FND_MESSAGE.SET_NAME('IGS','IGS_HE_EXT_SPA_DTL_NOT_FOUND');
83 FND_MSG_PUB.ADD;
84 l_db_val_failed := TRUE;
85 END IF;
86 -- Check whether the Domicile code exists
87 IF p_hesa_spa_stats_rec.domicile_cd IS NOT NULL THEN
88 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_DOM',
89 x_value => p_hesa_spa_stats_rec.domicile_cd) THEN
90 FND_MESSAGE.SET_NAME('IGS','IGS_HE_DOM_CD_NOT_EXIST');
91 FND_MSG_PUB.ADD;
92 l_db_val_failed := TRUE;
93 END IF;
94 END IF;
95
96 -- Check whether the Occupation Code exists
97 IF p_hesa_spa_stats_rec.occupation_code IS NOT NULL THEN
98 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_OCC',
99 x_value => p_hesa_spa_stats_rec.occupation_code) THEN
100 FND_MESSAGE.SET_NAME('IGS','IGS_HE_OCCUP_CD_NOT_EXIST');
101 FND_MSG_PUB.ADD;
102 l_db_val_failed := TRUE;
103 END IF;
104 END IF;
105 -- Check whether the Teaching training program ID exists
106 IF p_hesa_spa_stats_rec.teacher_train_prog_id IS NOT NULL THEN
107 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_TTCID',
108 x_value => p_hesa_spa_stats_rec.teacher_train_prog_id) THEN
109 FND_MESSAGE.SET_NAME('IGS','IGS_HE_TEACH_TRNPRG_NEX');
110 FND_MSG_PUB.ADD;
111 l_db_val_failed := TRUE;
112 END IF;
113 END IF;
114 -- Check whether the ITT Phase exists
115 IF p_hesa_spa_stats_rec.itt_phase IS NOT NULL THEN
116 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_ITTPHSC',
117 x_value => p_hesa_spa_stats_rec.itt_phase) THEN
118 FND_MESSAGE.SET_NAME('IGS','IGS_HE_ITT_PHASE_NEX');
119 FND_MSG_PUB.ADD;
120 l_db_val_failed := TRUE;
121 END IF;
122 END IF;
123 -- Check whether the Bilingual ITT marker exists
124 IF p_hesa_spa_stats_rec.bilingual_itt_marker IS NOT NULL THEN
125 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_BITTM',
126 x_value => p_hesa_spa_stats_rec.bilingual_itt_marker) THEN
127 FND_MESSAGE.SET_NAME('IGS','IGS_HE_BILINGUAL_ITT_NEX');
128 FND_MSG_PUB.ADD;
129 l_db_val_failed := TRUE;
130 END IF;
131 END IF;
132 -- Check whether the Teaching Qualification Gain Sector exists
133 IF p_hesa_spa_stats_rec.teaching_qual_gain_sector IS NOT NULL THEN
134 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_TQSEC',
135 x_value => p_hesa_spa_stats_rec.teaching_qual_gain_sector) THEN
136 FND_MESSAGE.SET_NAME('IGS','IGS_HE_TCH_QUAL_GSEC_NEX');
137 FND_MSG_PUB.ADD;
138 l_db_val_failed := TRUE;
139 END IF;
140 END IF;
141 -- Check whether the Teaching Qualification Gain subject 1 exists
142 IF p_hesa_spa_stats_rec.teaching_qual_gain_subj1 IS NOT NULL THEN
143 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_TQSUB123',
144 x_value => p_hesa_spa_stats_rec.teaching_qual_gain_subj1) THEN
145 FND_MESSAGE.SET_NAME('IGS','IGS_HE_TCH_QUAL_SUB1_NEX');
146 FND_MSG_PUB.ADD;
147 l_db_val_failed := TRUE;
148 END IF;
149 END IF;
150 -- Check whether the Teaching Qualification Gain subject 2 exists
151 IF p_hesa_spa_stats_rec.teaching_qual_gain_subj2 IS NOT NULL THEN
152 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_TQSUB123',
153 x_value => p_hesa_spa_stats_rec.teaching_qual_gain_subj2) THEN
154 FND_MESSAGE.SET_NAME('IGS','IGS_HE_TCH_QUAL_SUB2_NEX');
155 FND_MSG_PUB.ADD;
156 l_db_val_failed := TRUE;
157 END IF;
158 END IF;
159 -- Check whether the Teaching Qualification Gain subject 3 exists
160 IF p_hesa_spa_stats_rec.teaching_qual_gain_subj3 IS NOT NULL THEN
161 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_TQSUB123',
162 x_value => p_hesa_spa_stats_rec.teaching_qual_gain_subj3) THEN
163 FND_MESSAGE.SET_NAME('IGS','IGS_HE_TCH_QUAL_SUB3_NEX');
164 FND_MSG_PUB.ADD;
165 l_db_val_failed := TRUE;
166 END IF;
167 END IF;
168 -- Check whether the Destination exists
169 IF p_hesa_spa_stats_rec.destination IS NOT NULL THEN
170 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_DEST',
171 x_value => p_hesa_spa_stats_rec.destination) THEN
172 FND_MESSAGE.SET_NAME('IGS','IGS_HE_DESTINATION_NEX');
173 FND_MSG_PUB.ADD;
174 l_db_val_failed := TRUE;
175 END IF;
176 END IF;
177 -- Check whether the ITT Program Outcome exists
178 IF p_hesa_spa_stats_rec.itt_prog_outcome IS NOT NULL THEN
179 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_OUTCOME',
180 x_value => p_hesa_spa_stats_rec.itt_prog_outcome) THEN
181 FND_MESSAGE.SET_NAME('IGS','IGS_HE_ITT_PRG_OC_NEX');
182 FND_MSG_PUB.ADD;
183 l_db_val_failed := TRUE;
184 END IF;
185 END IF;
186 -- Check whether the NHS Funding Source exists
187 IF p_hesa_spa_stats_rec.nhs_funding_source IS NOT NULL THEN
188 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_NHS_FUSRC',
189 x_value => p_hesa_spa_stats_rec.nhs_funding_source) THEN
190 FND_MESSAGE.SET_NAME('IGS','IGS_HE_NHS_FUND_SRC_NEX');
191 FND_MSG_PUB.ADD;
192 l_db_val_failed := TRUE;
193 END IF;
194 END IF;
195 -- Check whether the UFI Place exists
196 IF p_hesa_spa_stats_rec.ufi_place IS NOT NULL THEN
197 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_UFI_PLACE',
198 x_value => p_hesa_spa_stats_rec.ufi_place) THEN
199 FND_MESSAGE.SET_NAME('IGS','IGS_HE_UFI_PLACE_NEX');
200 FND_MSG_PUB.ADD;
201 l_db_val_failed := TRUE;
202 END IF;
203 END IF;
204 -- Check whether the Social Class Indicator exists
205 IF p_hesa_spa_stats_rec.social_class_ind IS NOT NULL THEN
206 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_SOC',
207 x_value => p_hesa_spa_stats_rec.social_class_ind) THEN
208 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SOCIAL_CLS_IND_NEX');
209 FND_MSG_PUB.ADD;
210 l_db_val_failed := TRUE;
211 END IF;
212 END IF;
213 -- Check whether the NHS Employer exists
214 IF p_hesa_spa_stats_rec.nhs_employer IS NOT NULL THEN
215 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_NHS_EMPLOYER',
216 x_value => p_hesa_spa_stats_rec.nhs_employer) THEN
217 FND_MESSAGE.SET_NAME('IGS','IGS_HE_NHS_EMP_NEX');
218 FND_MSG_PUB.ADD;
219 l_db_val_failed := TRUE;
220 END IF;
221 END IF;
222 -- Check whether the Return Type exists
223 IF p_hesa_spa_stats_rec.return_type IS NOT NULL THEN
224 IF NOT igs_lookups_view_pkg.get_pk_for_validation (x_lookup_type => 'IGS_HE_RED_RTN',
225 x_lookup_code => p_hesa_spa_stats_rec.return_type) THEN
226 FND_MESSAGE.SET_NAME('IGS','IGS_HE_RET_TYPE_NEX');
227 FND_MSG_PUB.ADD;
228 l_db_val_failed := TRUE;
229 END IF;
230 END IF;
231 -- Check whether the Student Qualification Aim exists
232 IF p_hesa_spa_stats_rec.student_qual_aim IS NOT NULL THEN
233 IF NOT igs_en_hesa_pkg.validate_program_aim (p_award_cd => p_hesa_spa_stats_rec.student_qual_aim) THEN
234 FND_MESSAGE.SET_NAME('IGS','IGS_HE_STD_PRG_AIM_NEX');
235 FND_MSG_PUB.ADD;
236 l_db_val_failed := TRUE;
237 END IF;
238 END IF;
239 -- Check whether the Student FE Qualification Aim exists
240 IF p_hesa_spa_stats_rec.student_fe_qual_aim IS NOT NULL THEN
241 IF NOT igs_en_hesa_pkg.validate_program_aim (p_award_cd => p_hesa_spa_stats_rec.student_fe_qual_aim) THEN
242 FND_MESSAGE.SET_NAME('IGS','IGS_HE_STD_FE_PRG_AIM_NEX');
243 FND_MSG_PUB.ADD;
244 l_db_val_failed := TRUE;
245 END IF;
246 END IF;
247 -- Check whether the Subject Qualification Aim 1 exists
248 IF p_hesa_spa_stats_rec.subj_qualaim1 IS NOT NULL THEN
249 IF NOT igs_ps_fld_of_study_pkg.Get_Pk_For_Validation ( x_field_of_study => p_hesa_spa_stats_rec.subj_qualaim1) THEN
250 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUBQ1_FS_INVALID');
251 FND_MSG_PUB.ADD;
252 l_db_val_failed := TRUE;
253 END IF;
254 END IF;
255
256 -- Check whether the Subject Qualification Aim 2 exists
257 IF p_hesa_spa_stats_rec.subj_qualaim2 IS NOT NULL THEN
258 IF NOT igs_ps_fld_of_study_pkg.Get_Pk_For_Validation ( x_field_of_study => p_hesa_spa_stats_rec.subj_qualaim2) THEN
259 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUBQ2_FS_INVALID');
260 FND_MSG_PUB.ADD;
261 l_db_val_failed := TRUE;
262 END IF;
263 END IF;
264 -- Check whether the Subject Qualification Aim 3 exists
265 IF p_hesa_spa_stats_rec.subj_qualaim3 IS NOT NULL THEN
266 IF NOT igs_ps_fld_of_study_pkg.Get_Pk_For_Validation ( x_field_of_study => p_hesa_spa_stats_rec.subj_qualaim3) THEN
267 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SUBQ3_FS_INVALID');
268 FND_MSG_PUB.ADD;
269 l_db_val_failed := TRUE;
270 END IF;
271 END IF;
272
273 -- Check whether the Qualification Aim Proportion exists
274 IF p_hesa_spa_stats_rec.qualaim_proportion IS NOT NULL THEN
275 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_PROPORTION',
276 x_value => p_hesa_spa_stats_rec.qualaim_proportion) THEN
277 FND_MESSAGE.SET_NAME('IGS','IGS_HE_QUAL_PROP_NEX');
278 FND_MSG_PUB.ADD;
279 l_db_val_failed := TRUE;
280 END IF;
281 END IF;
282 -- Check whether the Special Student exists
283 IF p_hesa_spa_stats_rec.special_student IS NOT NULL THEN
284 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_SPEC_STUD',
285 x_value => p_hesa_spa_stats_rec.special_student) THEN
286 FND_MESSAGE.SET_NAME('IGS','IGS_HE_SPEC_STUD_NEX');
287 FND_MSG_PUB.ADD;
288 l_db_val_failed := TRUE;
289 END IF;
290 END IF;
291 -- Check whether the FE Student Marker exists
292 IF p_hesa_spa_stats_rec.fe_student_marker IS NOT NULL THEN
293 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_FESTUMK',
294 x_value => p_hesa_spa_stats_rec.fe_student_marker) THEN
295 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FE_STUD_MARKER_NEX');
296 FND_MSG_PUB.ADD;
297 l_db_val_failed := TRUE;
298 END IF;
299 END IF;
300
301 -- Check whether the Dependants Code exists
302 IF p_hesa_spa_stats_rec.dependants_cd IS NOT NULL THEN
303 IF NOT igs_he_code_values_pkg.get_pk_for_validation(x_code_type => 'OSS_DEPEND',
304 x_value => p_hesa_spa_stats_rec.dependants_cd) THEN
305 FND_MESSAGE.SET_NAME('IGS','IGS_HE_DEP_CD_NEX');
306 FND_MSG_PUB.ADD;
307 l_db_val_failed := TRUE;
308 END IF;
309 END IF;
310
311 -- Check whether the Government Initiatives Code exists
312 IF p_hesa_spa_stats_rec.gov_initiatives_cd IS NOT NULL THEN
313 IF NOT igs_he_code_values_pkg.get_pk_for_validation(x_code_type => 'OSS_GOVINIT',
314 x_value => p_hesa_spa_stats_rec.gov_initiatives_cd) THEN
315 FND_MESSAGE.SET_NAME('IGS','IGS_HE_GOV_INIT_NEX');
316 FND_MSG_PUB.ADD;
317 l_db_val_failed := TRUE;
318 END IF;
319 END IF;
320
321 -- Check whether Eligibility for Disadvantage Uplift Indicator exists
322 IF p_hesa_spa_stats_rec.disadv_uplift_elig_cd IS NOT NULL THEN
323 IF NOT igs_he_code_values_pkg.get_pk_for_validation(x_code_type => 'OSS_ELIDISUP',
324 x_value => p_hesa_spa_stats_rec.disadv_uplift_elig_cd) THEN
325 FND_MESSAGE.SET_NAME('IGS','IGS_HE_ELIG_DIS_NEX');
326 FND_MSG_PUB.ADD;
327 l_db_val_failed := TRUE;
328 END IF;
329 END IF;
330
331 -- Check whether Franchise Partner Indicator exists
332 IF p_hesa_spa_stats_rec.franch_partner_cd IS NOT NULL THEN
333 IF NOT igs_he_code_values_pkg.get_pk_for_validation(x_code_type => 'OSS_FRANPART',
334 x_value => p_hesa_spa_stats_rec.franch_partner_cd) THEN
335 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FRAN_PART_NEX');
336 FND_MSG_PUB.ADD;
337 l_db_val_failed := TRUE;
338 END IF;
339 END IF;
340
341 -- Check whether Franchised Out Arrangement Indicator exists
342 IF p_hesa_spa_stats_rec.franch_out_arr_cd IS NOT NULL THEN
343 IF NOT igs_he_code_values_pkg.get_pk_for_validation(x_code_type => 'OSS_FROUTARR',
344 x_value => p_hesa_spa_stats_rec.franch_out_arr_cd) THEN
345 FND_MESSAGE.SET_NAME('IGS','IGS_HE_FRAN_OUT_ARR_NEX');
346 FND_MSG_PUB.ADD;
347 l_db_val_failed := TRUE;
348 END IF;
349 END IF;
350
351 -- Check whether Employer Role Code exists
352 IF p_hesa_spa_stats_rec.employer_role_cd IS NOT NULL THEN
353 IF NOT igs_he_code_values_pkg.get_pk_for_validation(x_code_type => 'OSS_EMPROLE',
354 x_value => p_hesa_spa_stats_rec.employer_role_cd) THEN
355 FND_MESSAGE.SET_NAME('IGS','IGS_HE_EMP_ROLE_CD_NEX');
356 FND_MSG_PUB.ADD;
357 l_db_val_failed := TRUE;
358 END IF;
359 END IF;
360
361 -- Check whether the Implied Rate of Council Partial Funding field is
362 -- within the range 0 to 100 (inclusive)
363 IF p_hesa_spa_stats_rec.implied_fund_rate IS NOT NULL THEN
364 IF NOT p_hesa_spa_stats_rec.implied_fund_rate between 0 and 100 THEN
365 FND_MESSAGE.SET_NAME('IGS','IGS_HE_IMP_RATE_INVALID');
366 FND_MSG_PUB.ADD;
367 l_db_val_failed := TRUE;
368 END IF;
369 END IF;
370
371 -- Check whether the Number of Units To Achieve Full Qualification field is
372 -- within the range 0 to 99 (inclusive)
373 IF p_hesa_spa_stats_rec.units_for_qual IS NOT NULL THEN
374 IF NOT p_hesa_spa_stats_rec.units_for_qual between 0 and 99 THEN
375 FND_MESSAGE.SET_NAME('IGS','IGS_HE_UNITS_QUAL_INVALID');
376 FND_MSG_PUB.ADD;
377 l_db_val_failed := TRUE;
378 END IF;
379 END IF;
380
381 -- Check whether the Number of Units Completed field is within the range 0 to 99 (inclusive)
382 IF p_hesa_spa_stats_rec.units_completed IS NOT NULL THEN
383 IF NOT p_hesa_spa_stats_rec.units_completed between 0 and 99 THEN
384 FND_MESSAGE.SET_NAME('IGS','IGS_HE_UNITS_COMP_INVALID');
385 FND_MSG_PUB.ADD;
386 l_db_val_failed := TRUE;
387 END IF;
388 END IF;
389
390 -- Check if the Eligibility for Enhanced Funding Indicator exists
391 IF p_hesa_spa_stats_rec.enh_fund_elig_cd IS NOT NULL THEN
392 IF NOT igs_he_code_values_pkg.get_pk_for_validation (x_code_type => 'OSS_ELIGENFD',
393 x_value => p_hesa_spa_stats_rec.enh_fund_elig_cd) THEN
394 -- ADD excep to stack
395 FND_MESSAGE.SET_NAME('IGS','IGS_HE_ELIG_ENH_FUND_NEX');
396 FND_MSG_PUB.ADD;
397 l_db_val_failed := TRUE;
398 END IF;
399 END IF;
400
401 -- Check whether the Disadvantage Uplift Factor field is within
402 -- range 0.0000 to 9.9999 (inclusive)
403 IF p_hesa_spa_stats_rec.disadv_uplift_factor IS NOT NULL THEN
404 IF NOT p_hesa_spa_stats_rec.disadv_uplift_factor between 0.0000 and 9.9999 THEN
405 FND_MESSAGE.SET_NAME('IGS','IGS_HE_DIS_UPLIFT_FTR_INVALID');
406 FND_MSG_PUB.ADD;
407 l_db_val_failed := TRUE;
408 END IF;
409 END IF;
410
411 -- Validating the check constraints
412 --
413 -- Check whether the Associate UCAS Number has valid value
414 IF p_hesa_spa_stats_rec.associate_ucas_number IS NOT NULL THEN
415 BEGIN
416 igs_he_st_spa_all_pkg.check_constraints (column_name => 'ASSOCIATE_UCAS_NUMBER',
417 column_value => p_hesa_spa_stats_rec.associate_ucas_number);
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
422 p_data => l_msg_data);
423 FND_MSG_PUB.DELETE_MSG(l_msg_count);
424 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_HE_ASSOC_UCAS_NUM_Y_N');
425 FND_MSG_PUB.ADD;
426 l_db_val_failed := TRUE;
427 END;
428 END IF;
429 -- Check whether the Associate Scottish Candidate has valid value
430 IF p_hesa_spa_stats_rec.associate_scott_cand IS NOT NULL THEN
431 BEGIN
432 igs_he_st_spa_all_pkg.check_constraints (column_name => 'ASSOCIATE_SCOTT_CAND',
433 column_value => p_hesa_spa_stats_rec.associate_scott_cand);
434 EXCEPTION
435 WHEN OTHERS THEN
436 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
437 p_data => l_msg_data);
438 FND_MSG_PUB.DELETE_MSG(l_msg_count);
439 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_HE_ASSOC_SCT_CAND_Y_N');
440 FND_MSG_PUB.ADD;
441 l_db_val_failed := TRUE;
442 END;
443 END IF;
444 -- Check whether the Associate Teaching Reference Number has valid value
445 IF p_hesa_spa_stats_rec.associate_teach_ref_num IS NOT NULL THEN
446 BEGIN
447 igs_he_st_spa_all_pkg.check_constraints (column_name => 'ASSOCIATE_TEACH_REF_NUM',
448 column_value => p_hesa_spa_stats_rec.associate_teach_ref_num);
449 EXCEPTION
450 WHEN OTHERS THEN
451 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
452 p_data => l_msg_data);
453 FND_MSG_PUB.DELETE_MSG(l_msg_count);
454 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_HE_ASSOC_TCH_REF_Y_N');
455 FND_MSG_PUB.ADD;
456 l_db_val_failed := TRUE;
457 END;
458 END IF;
459 -- Check whether the Associate NHS Registration Number has valid value
460 IF p_hesa_spa_stats_rec.associate_nhs_reg_num IS NOT NULL THEN
461 BEGIN
462 igs_he_st_spa_all_pkg.check_constraints (column_name => 'ASSOCIATE_NHS_REG_NUM',
463 column_value => p_hesa_spa_stats_rec.associate_nhs_reg_num);
464 EXCEPTION
465 WHEN OTHERS THEN
466 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
467 p_data => l_msg_data);
468 FND_MSG_PUB.DELETE_MSG(l_msg_count);
469 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_HE_ASSOC_NHS_REG_Y_N');
470 FND_MSG_PUB.ADD;
471 l_db_val_failed := TRUE;
472 END;
473 END IF;
474
475 -- Check whether any validation failed, if yes then return Error status otherwise return success status
476 IF l_db_val_failed THEN
477 RETURN 'E';
478 ELSE
479 RETURN 'S';
480 END IF;
481
482 END validate_db_cons;
483
484 /*------------------------------------------------------------------
485 --Created by : knaraset, Oracle IDC
486 --Date created: 14-Nov-2002
487 --
488 --Purpose: function to validate the business rules for the HESA program attempt statistics
489 --
490 --Known limitations/enhancements and/or remarks:
491 --
492 --Change History:
493 --Who When What
494 --
495 ------------------------------------------------------------------ */
496 FUNCTION validate_hesa_spa(
497 p_hesa_spa_stats_rec IN hesa_spa_rec_type)
498 RETURN BOOLEAN AS
499
500 l_br_val_failed BOOLEAN := FALSE;
501 BEGIN
502 -- validate whether the specified combination of subj_qualaim's and qualaim_proportion is valid
503 IF NOT igs_en_hesa_pkg.val_sub_qual_proportion (
504 p_subj_qualaim1 => p_hesa_spa_stats_rec.subj_qualaim1,
505 p_subj_qualaim2 => p_hesa_spa_stats_rec.subj_qualaim2,
506 p_subj_qualaim3 => p_hesa_spa_stats_rec.subj_qualaim3,
507 p_qualaim_proportion => p_hesa_spa_stats_rec.qualaim_proportion) THEN
508
509 l_br_val_failed := TRUE;
510 END IF;
511
512 -- Validate whether the given highest qual on entry is exists against the
513 -- grading schema defined for HESA code HESA_HIGH_QUAL_ON_ENT.
514 IF NOT igs_en_hesa_pkg.val_highest_qual_entry (p_highest_qual_on_entry => p_hesa_spa_stats_rec.highest_qual_on_entry) THEN
515 FND_MESSAGE.SET_NAME('IGS','IGS_HE_QUAL_ENTRY_NEX');
516 FND_MSG_PUB.ADD;
517 l_br_val_failed := TRUE;
518 END IF;
519
520 -- Check whether any validation failed, if yes then return FALSE otherwise return TRUE
521 IF l_br_val_failed THEN
522 RETURN FALSE;
523 ELSE
524 RETURN TRUE;
525 END IF;
526
527 END validate_hesa_spa;
528
529 /*------------------------------------------------------------------
530 --Created by : knaraset, Oracle IDC
531 --Date created: 14-Nov-2002
532 --
533 --Purpose: This is Public API to import the Legacy HESA program attempt statistics details
534 -- into OSS system.
535 --
536 --Known limitations/enhancements and/or remarks:
537 --
538 -- This API takes the record type variable of program attempt statistics along with
539 -- other standard API parameters. following is the flow of the procedure
540 --
541 -- 1. Validate the Country profile value
542 -- 2. Validate the mandatory parameters
543 -- 3. Derive the required values based on input values
544 -- 4. Validate the database constraints
545 -- 5. Validate the business rules.
546 -- 6. Insert the record into OSS table(igs_he_st_spa_all)
547 --
548 -- If any of the above step validation/logic failed then the procudure returns
549 -- with appropriate message(s) and status.
550 --
551 --Change History:
552 --Who When What
553 --ayedubat 14-Jan-2004 Added the NVL condition for the fields, associate_ucas_number, associate_scott_cand,
554 -- associate_teach_ref_num,associate_nhs_reg_num to 'Y' for Bug, 3374555
555 --jtmathew 21-Sep-2004 Modified INSERT statement to accommodate the new fields described in HEFD350.
556 ------------------------------------------------------------------ */
557 PROCEDURE create_hesa_spa (p_api_version IN NUMBER,
558 p_init_msg_list IN VARCHAR2,
559 p_commit IN VARCHAR2,
560 p_validation_level IN NUMBER,
561 p_hesa_spa_stats_rec IN hesa_spa_rec_type,
562 x_return_status OUT NOCOPY VARCHAR2,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_msg_data OUT NOCOPY VARCHAR2) AS
565
566 -- Derive HESA_ST_SPA_ID from sequence
567 CURSOR cur_hesa_st_spa_id IS
568 SELECT igs_he_st_spa_all_s.NEXTVAL
569 FROM dual;
570
571 l_api_name CONSTANT VARCHAR2(30) := 'create_hesa_spa';
572 l_api_version CONSTANT NUMBER := 1.0;
573
574 l_validation_failed BOOLEAN := FALSE;
575 l_db_val_status VARCHAR2(1);
576 l_person_id hz_parties.party_id%TYPE;
577 l_version_number igs_en_stdnt_ps_att.version_number%TYPE;
578 l_hesa_st_spa_id igs_he_st_spa_all.hesa_st_spa_id%TYPE;
579
580 BEGIN
581 -- Create save point
582 SAVEPOINT create_hesa_spa_pub;
583
584 -- Check for the Compatible API call
585 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
586 p_api_version,
587 l_api_name,
588 g_pkg_name) THEN
589
590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
591 END IF;
592
593 -- If the calling program has passed the parameter for initializing the message list
594 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
595 FND_MSG_PUB.INITIALIZE;
596 END IF;
597
598 -- Set the return status to success
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600
601
602 -- Check whether the counry profile value is GB
603 IF NVL(FND_PROFILE.VALUE('OSS_COUNTRY_CODE'),'NONE') <> 'GB' THEN
604 FND_MESSAGE.SET_NAME ('IGS','IGS_UC_HE_NOT_ENABLED');
605 FND_MSG_PUB.ADD;
606 x_return_status := FND_API.G_RET_STS_ERROR;
607 l_validation_failed := TRUE;
608 END IF;
609
610 -- If no validation failed then validate parameters
611 IF NOT l_validation_failed THEN
612 IF NOT validate_parameters(p_hesa_spa_stats_rec => p_hesa_spa_stats_rec) THEN
613 l_validation_failed := TRUE;
614 END IF;
615 END IF;
616
617 -- If no validation failed then derive the required values
618 IF NOT l_validation_failed THEN
619
620 -- derive the person Id
621 l_person_id := igs_ge_gen_003.get_person_id(p_person_number => p_hesa_spa_stats_rec.person_number);
622 IF l_person_id IS NULL THEN
623 FND_MESSAGE.SET_NAME ('IGS','IGS_GE_INVALID_PERSON_NUMBER');
624 FND_MSG_PUB.ADD;
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 l_validation_failed := TRUE;
627 END IF;
628
629 -- Check whether person ID found then only version number can be derived
630 IF NOT l_validation_failed THEN
631 -- derive the version number of the program attempt
632 l_version_number := igs_ge_gen_003.get_program_version(p_person_id => l_person_id,
633 p_program_cd => p_hesa_spa_stats_rec.program_cd);
634 IF l_version_number IS NULL THEN
635 FND_MESSAGE.SET_NAME ('IGS','IGS_HE_EXT_SPA_DTL_NOT_FOUND');
636 FND_MSG_PUB.ADD;
637 x_return_status := FND_API.G_RET_STS_ERROR;
638 l_validation_failed := TRUE;
639 END IF;
640 END IF;
641 END IF; -- end, derivations
642
643 -- If no validation failed then validate the database constraints
644 IF NOT l_validation_failed THEN
645 l_db_val_status := validate_db_cons(p_person_id => l_person_id,
646 p_version_number => l_version_number,
647 p_hesa_spa_stats_rec => p_hesa_spa_stats_rec);
648 IF l_db_val_status = 'W' THEN
649 x_return_status := 'W';
650 l_validation_failed := TRUE;
651 ELSIF l_db_val_status = 'E' THEN
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 l_validation_failed := TRUE;
654 END IF;
655 END IF; -- end, validate database constraints
656
657 -- If no validation failed then validate the business rules for Hesa program statistics
658 IF NOT l_validation_failed THEN
659 IF NOT validate_hesa_spa(p_hesa_spa_stats_rec => p_hesa_spa_stats_rec) THEN
660 x_return_status := FND_API.G_RET_STS_ERROR;
661 l_validation_failed := TRUE;
662 END IF;
663 END IF;
664
665 -- If no validation failed then insert the record into OSS table(Igs_He_St_Spa_all)
666 IF NOT l_validation_failed THEN
667
668 -- get the hesa_st_spa_id from the sequence
669 OPEN cur_hesa_st_spa_id;
670 FETCH cur_hesa_st_spa_id INTO l_hesa_st_spa_id;
671 CLOSE cur_hesa_st_spa_id;
672
673 --
674 -- Insert the HESA program statistics record into OSS table igs_he_st_spa_all
675 --
676 INSERT INTO igs_he_st_spa_all (
677 hesa_st_spa_id,
678 org_id,
679 person_id,
680 course_cd,
681 version_number,
682 fe_student_marker,
683 domicile_cd,
684 inst_last_attended,
685 year_left_last_inst,
686 highest_qual_on_entry,
687 date_qual_on_entry_calc,
688 a_level_point_score,
689 highers_points_scores,
690 occupation_code,
691 commencement_dt,
692 special_student,
693 student_qual_aim,
694 student_fe_qual_aim,
695 teacher_train_prog_id,
696 itt_phase,
697 bilingual_itt_marker,
698 teaching_qual_gain_sector,
699 teaching_qual_gain_subj1,
700 teaching_qual_gain_subj2,
701 teaching_qual_gain_subj3,
702 student_inst_number,
703 destination,
704 itt_prog_outcome,
705 hesa_return_name,
706 hesa_return_id,
707 hesa_submission_name,
708 associate_ucas_number,
709 associate_scott_cand,
710 associate_teach_ref_num,
711 associate_nhs_reg_num,
712 nhs_funding_source,
713 ufi_place,
714 postcode,
715 social_class_ind,
716 occcode,
717 total_ucas_tariff,
718 nhs_employer,
719 creation_date,
720 created_by,
721 last_update_date,
722 last_updated_by,
723 last_update_login,
724 return_type,
725 calculated_fte,
726 qual_aim_subj1,
727 qual_aim_subj2,
728 qual_aim_subj3,
729 qual_aim_proportion,
730 dependants_cd,
731 implied_fund_rate,
732 gov_initiatives_cd,
733 units_for_qual,
734 disadv_uplift_elig_cd,
735 franch_partner_cd,
736 units_completed,
737 franch_out_arr_cd,
738 employer_role_cd,
739 disadv_uplift_factor,
740 enh_fund_elig_cd,
741 exclude_flag)
742 VALUES (
743 l_hesa_st_spa_id,
744 igs_ge_gen_003.get_org_id(),
745 l_person_id,
746 p_hesa_spa_stats_rec.program_cd,
747 l_version_number,
748 p_hesa_spa_stats_rec.fe_student_marker,
749 p_hesa_spa_stats_rec.domicile_cd,
750 NULL, --inst_last_attended,
751 NULL, --year_left_last_inst,
752 p_hesa_spa_stats_rec.highest_qual_on_entry,
753 NULL, --date_qual_on_entry_calc,
754 NULL, --a_level_point_score,
755 NULL, --highers_points_scores,
756 p_hesa_spa_stats_rec.occupation_code,
757 p_hesa_spa_stats_rec.commencement_dt,
758 p_hesa_spa_stats_rec.special_student,
759 p_hesa_spa_stats_rec.student_qual_aim,
760 p_hesa_spa_stats_rec.student_fe_qual_aim,
761 p_hesa_spa_stats_rec.teacher_train_prog_id,
762 p_hesa_spa_stats_rec.itt_phase,
763 p_hesa_spa_stats_rec.bilingual_itt_marker,
764 p_hesa_spa_stats_rec.teaching_qual_gain_sector,
765 p_hesa_spa_stats_rec.teaching_qual_gain_subj1,
766 p_hesa_spa_stats_rec.teaching_qual_gain_subj2,
767 p_hesa_spa_stats_rec.teaching_qual_gain_subj3,
768 p_hesa_spa_stats_rec.student_inst_number,
769 p_hesa_spa_stats_rec.destination,
770 p_hesa_spa_stats_rec.itt_prog_outcome,
771 NULL, --hesa_return_name,
772 NULL, --hesa_return_id,
773 NULL, --hesa_submission_name,
774 NVL(p_hesa_spa_stats_rec.associate_ucas_number,'Y'),
775 NVL(p_hesa_spa_stats_rec.associate_scott_cand,'Y'),
776 NVL(p_hesa_spa_stats_rec.associate_teach_ref_num,'Y'),
777 NVL(p_hesa_spa_stats_rec.associate_nhs_reg_num,'Y'),
778 p_hesa_spa_stats_rec.nhs_funding_source,
779 p_hesa_spa_stats_rec.ufi_place,
780 p_hesa_spa_stats_rec.postcode,
781 p_hesa_spa_stats_rec.social_class_ind,
782 p_hesa_spa_stats_rec.occcode,
783 NULL, --total_ucas_tariff,
784 p_hesa_spa_stats_rec.nhs_employer,
785 SYSDATE, --creation_date,
786 NVL(FND_GLOBAL.USER_ID,-1), --created_by,
787 SYSDATE, --last_update_date,
788 NVL(FND_GLOBAL.USER_ID,-1), --last_updated_by,
789 NVL(FND_GLOBAL.LOGIN_ID,-1), --last_update_login,
790 p_hesa_spa_stats_rec.return_type,
791 NULL, --calculated_fte,
792 p_hesa_spa_stats_rec.subj_qualaim1,
793 p_hesa_spa_stats_rec.subj_qualaim2,
794 p_hesa_spa_stats_rec.subj_qualaim3,
795 p_hesa_spa_stats_rec.qualaim_proportion,
796 p_hesa_spa_stats_rec.dependants_cd,
797 p_hesa_spa_stats_rec.implied_fund_rate,
798 p_hesa_spa_stats_rec.gov_initiatives_cd,
799 p_hesa_spa_stats_rec.units_for_qual,
800 p_hesa_spa_stats_rec.disadv_uplift_elig_cd,
801 p_hesa_spa_stats_rec.franch_partner_cd,
802 p_hesa_spa_stats_rec.units_completed,
803 p_hesa_spa_stats_rec.franch_out_arr_cd,
804 p_hesa_spa_stats_rec.employer_role_cd,
805 p_hesa_spa_stats_rec.disadv_uplift_factor,
806 p_hesa_spa_stats_rec.enh_fund_elig_cd,
807 'N');
808 -- Set the return status to success.
809 x_return_status := FND_API.G_RET_STS_SUCCESS;
810
811 ELSE
812 ROLLBACK TO create_hesa_spa_pub;
813 END IF;
814
815 -- If no validation failed and p_commit is passed as 'Y' then commit the changes.
816 IF NOT l_validation_failed AND FND_API.TO_BOOLEAN(p_commit) THEN
817 COMMIT;
818 END IF;
819
820 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
821 p_data => x_msg_data);
822
823 RETURN;
824
825 EXCEPTION
826 WHEN FND_API.G_EXC_ERROR THEN
827 ROLLBACK TO create_hesa_spa_pub;
828 x_return_status := FND_API.G_RET_STS_ERROR;
829 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
830 p_data => x_msg_data);
831 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
832 ROLLBACK TO create_hesa_spa_pub;
833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
835 p_data => x_msg_data);
836 WHEN OTHERS THEN
837 ROLLBACK TO create_hesa_spa_pub;
838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
839
840 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
841 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,
842 l_api_name);
843 END IF;
844 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
845 p_data => x_msg_data);
846 END create_hesa_spa;
847
848
849 END IGS_HE_SPA_LGCY_PUB;
[Home] [Help]