[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_ST_SPA_ALL_PKG
Source
1 PACKAGE BODY igs_he_st_spa_all_pkg AS
2 /* $Header: IGSWI22B.pls 120.4 2006/02/06 19:53:27 jbaber ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_st_spa_all%ROWTYPE;
6 new_references igs_he_st_spa_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_hesa_st_spa_id IN NUMBER ,
12 x_org_id IN NUMBER ,
13 x_person_id IN NUMBER ,
14 x_course_cd IN VARCHAR2 ,
15 x_version_number IN NUMBER ,
16 x_fe_student_marker IN VARCHAR2 ,
17 x_domicile_cd IN VARCHAR2 ,
18 x_inst_last_attended IN VARCHAR2 ,
19 x_year_left_last_inst IN VARCHAR2 ,
20 x_highest_qual_on_entry IN VARCHAR2 ,
21 x_date_qual_on_entry_calc IN DATE ,
22 x_a_level_point_score IN NUMBER ,
23 x_highers_points_scores IN NUMBER ,
24 x_occupation_code IN VARCHAR2 ,
25 x_commencement_dt IN DATE ,
26 x_special_student IN VARCHAR2 ,
27 x_student_qual_aim IN VARCHAR2 ,
28 x_student_fe_qual_aim IN VARCHAR2 ,
29 x_teacher_train_prog_id IN VARCHAR2 ,
30 x_itt_phase IN VARCHAR2 ,
31 x_bilingual_itt_marker IN VARCHAR2 ,
32 x_teaching_qual_gain_sector IN VARCHAR2 ,
33 x_teaching_qual_gain_subj1 IN VARCHAR2 ,
34 x_teaching_qual_gain_subj2 IN VARCHAR2 ,
35 x_teaching_qual_gain_subj3 IN VARCHAR2 ,
36 x_student_inst_number IN VARCHAR2 ,
37 x_destination IN VARCHAR2 ,
38 x_itt_prog_outcome IN VARCHAR2 ,
39 x_hesa_return_name IN VARCHAR2 ,
40 x_hesa_return_id IN NUMBER ,
41 x_hesa_submission_name IN VARCHAR2 ,
42 x_associate_ucas_number IN VARCHAR2 ,
43 x_associate_scott_cand IN VARCHAR2 ,
44 x_associate_teach_ref_num IN VARCHAR2 ,
45 x_associate_nhs_reg_num IN VARCHAR2 ,
46 x_nhs_funding_source IN VARCHAR2 ,
47 x_ufi_place IN VARCHAR2 ,
48 x_postcode IN VARCHAR2 ,
49 x_social_class_ind IN VARCHAR2 ,
50 x_occcode IN VARCHAR2 ,
51 x_total_ucas_tariff IN NUMBER ,
52 x_nhs_employer IN VARCHAR2 ,
53 x_return_type IN VARCHAR2 ,
54 x_qual_aim_subj1 IN VARCHAR2 ,
55 x_qual_aim_subj2 IN VARCHAR2 ,
56 x_qual_aim_subj3 IN VARCHAR2 ,
57 x_qual_aim_proportion IN VARCHAR2 ,
58 x_creation_date IN DATE ,
59 x_created_by IN NUMBER ,
60 x_last_update_date IN DATE ,
61 x_last_updated_by IN NUMBER ,
62 x_last_update_login IN NUMBER,
63 x_dependants_cd IN VARCHAR2,
64 x_implied_fund_rate IN NUMBER,
65 x_gov_initiatives_cd IN VARCHAR2,
66 x_units_for_qual IN NUMBER,
67 x_disadv_uplift_elig_cd IN VARCHAR2,
68 x_franch_partner_cd IN VARCHAR2,
69 x_units_completed IN NUMBER,
70 x_franch_out_arr_cd IN VARCHAR2,
71 x_employer_role_cd IN VARCHAR2,
72 x_disadv_uplift_factor IN NUMBER,
73 x_enh_fund_elig_cd IN VARCHAR2,
74 x_exclude_flag IN VARCHAR2
75 ) AS
76 /*
77 || Created By : [email protected]
78 || Created On : 21-FEB-2002
79 || Purpose : Initialises the Old and New references for the columns of the table.
80 || Known limitations, enhancements or remarks :
81 || Change History :
82 || Who When What
83 || (reverse chronological order - newest change first)
84 */
85
86 CURSOR cur_old_ref_values IS
87 SELECT *
88 FROM IGS_HE_ST_SPA_ALL
89 WHERE rowid = x_rowid;
90
91 BEGIN
92
93 l_rowid := x_rowid;
94
95 -- Code for setting the Old and New Reference Values.
96 -- Populate Old Values.
97 OPEN cur_old_ref_values;
98 FETCH cur_old_ref_values INTO old_references;
99 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
100 CLOSE cur_old_ref_values;
101 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
102 igs_ge_msg_stack.add;
103 app_exception.raise_exception;
104 RETURN;
105 END IF;
106 CLOSE cur_old_ref_values;
107
108 -- Populate New Values.
109 new_references.hesa_st_spa_id := x_hesa_st_spa_id;
110 new_references.org_id := x_org_id;
111 new_references.person_id := x_person_id;
112 new_references.course_cd := x_course_cd;
113 new_references.version_number := x_version_number;
114 new_references.fe_student_marker := x_fe_student_marker;
115 new_references.domicile_cd := x_domicile_cd;
116 new_references.inst_last_attended := x_inst_last_attended;
117 new_references.year_left_last_inst := x_year_left_last_inst;
118 new_references.highest_qual_on_entry := x_highest_qual_on_entry;
119 new_references.date_qual_on_entry_calc := x_date_qual_on_entry_calc;
120 new_references.a_level_point_score := x_a_level_point_score;
121 new_references.highers_points_scores := x_highers_points_scores;
122 new_references.occupation_code := x_occupation_code;
123 new_references.commencement_dt := x_commencement_dt;
124 new_references.special_student := x_special_student;
125 new_references.student_qual_aim := x_student_qual_aim;
126 new_references.student_fe_qual_aim := x_student_fe_qual_aim;
127 new_references.teacher_train_prog_id := x_teacher_train_prog_id;
128 new_references.itt_phase := x_itt_phase;
129 new_references.bilingual_itt_marker := x_bilingual_itt_marker;
130 new_references.teaching_qual_gain_sector := x_teaching_qual_gain_sector;
131 new_references.teaching_qual_gain_subj1 := x_teaching_qual_gain_subj1;
132 new_references.teaching_qual_gain_subj2 := x_teaching_qual_gain_subj2;
133 new_references.teaching_qual_gain_subj3 := x_teaching_qual_gain_subj3;
134 new_references.student_inst_number := x_student_inst_number;
135 new_references.destination := x_destination;
136 new_references.itt_prog_outcome := x_itt_prog_outcome;
137 new_references.hesa_return_name := x_hesa_return_name;
138 new_references.hesa_return_id := x_hesa_return_id;
139 new_references.hesa_submission_name := x_hesa_submission_name;
140 new_references.associate_ucas_number := x_associate_ucas_number;
141 new_references.associate_scott_cand := x_associate_scott_cand;
142 new_references.associate_teach_ref_num := x_associate_teach_ref_num;
143 new_references.associate_nhs_reg_num := x_associate_nhs_reg_num;
144 new_references.nhs_funding_source := x_nhs_funding_source;
145 new_references.ufi_place := x_ufi_place;
146 new_references.postcode := x_postcode;
147 new_references.social_class_ind := x_social_class_ind;
148 new_references.occcode := x_occcode;
149 new_references.total_ucas_tariff := x_total_ucas_tariff;
150 new_references.nhs_employer := x_nhs_employer;
151 new_references.return_type := x_return_type;
152 new_references.qual_aim_subj1 := x_qual_aim_subj1 ;
153 new_references.qual_aim_subj2 := x_qual_aim_subj2;
154 new_references.qual_aim_subj3 := x_qual_aim_subj3;
155 new_references.qual_aim_proportion := x_qual_aim_proportion ;
156 new_references.dependants_cd := x_dependants_cd;
157 new_references.implied_fund_rate := x_implied_fund_rate;
158 new_references.gov_initiatives_cd := x_gov_initiatives_cd;
159 new_references.units_for_qual := x_units_for_qual;
160 new_references.disadv_uplift_elig_cd := x_disadv_uplift_elig_cd;
161 new_references.franch_partner_cd := x_franch_partner_cd;
162 new_references.units_completed := x_units_completed;
163 new_references.franch_out_arr_cd := x_franch_out_arr_cd;
164 new_references.employer_role_cd := x_employer_role_cd;
165 new_references.disadv_uplift_factor := x_disadv_uplift_factor;
166 new_references.enh_fund_elig_cd := x_enh_fund_elig_cd;
167
168 IF (p_action = 'UPDATE' AND x_exclude_flag IS NULL) THEN
169 new_references.exclude_flag := old_references.exclude_flag;
170 ELSE
171 new_references.exclude_flag := x_exclude_flag;
172 END IF;
173
174 IF (p_action = 'INSERT') THEN
175 new_references.associate_ucas_number := NVL(x_associate_ucas_number,'Y');
176 new_references.associate_scott_cand := NVL(x_associate_scott_cand,'Y');
177 new_references.associate_teach_ref_num := NVL(x_associate_teach_ref_num,'Y');
178 new_references.associate_nhs_reg_num := NVL(x_associate_nhs_reg_num,'Y');
179 END IF;
180
181 IF (p_action = 'UPDATE') THEN
182 new_references.creation_date := old_references.creation_date;
183 new_references.created_by := old_references.created_by;
184 ELSE
185 new_references.creation_date := x_creation_date;
186 new_references.created_by := x_created_by;
187 END IF;
188
189 new_references.last_update_date := x_last_update_date;
190 new_references.last_updated_by := x_last_updated_by;
191 new_references.last_update_login := x_last_update_login;
192
193 END set_column_values;
194
195 PROCEDURE check_constraints(
196 column_name IN VARCHAR2,
197 column_value IN VARCHAR2
198 ) AS
199 /*----------------------------------------------------------------------------
200 || Created By : knaraset
201 || Created On : 14-Nov-2002
202 || Purpose : Validating the values of the given column
203 ||
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 ----------------------------------------------------------------------------*/
209 BEGIN
210 IF column_name IS NULL THEN
211 NULL;
212 ELSIF UPPER(column_name) = 'ASSOCIATE_UCAS_NUMBER' THEN
213 new_references.associate_ucas_number := column_value;
214 ELSIF UPPER(column_name) = 'ASSOCIATE_SCOTT_CAND' THEN
215 new_references.associate_scott_cand := column_value;
216 ELSIF UPPER(column_name) = 'ASSOCIATE_TEACH_REF_NUM' THEN
217 new_references.associate_teach_ref_num := column_value;
218 ELSIF UPPER(column_name) = 'ASSOCIATE_NHS_REG_NUM' THEN
219 new_references.associate_nhs_reg_num := column_value;
220 END IF;
221
222 IF UPPER(column_name) = 'ASSOCIATE_UCAS_NUMBER' OR
223 column_name IS NULL THEN
224 IF new_references.associate_ucas_number NOT IN ( 'Y' , 'N') THEN
225 FND_MESSAGE.SET_NAME ('IGS','IGS_GE_INVALID_VALUE');
226 IGS_GE_MSG_STACK.ADD;
227 APP_EXCEPTION.RAISE_EXCEPTION;
228 END IF;
229 END IF;
230 IF UPPER(column_name) = 'ASSOCIATE_SCOTT_CAND' OR
231 column_name IS NULL THEN
232 IF new_references.associate_scott_cand NOT IN ( 'Y' , 'N') THEN
233 FND_MESSAGE.SET_NAME ('IGS','IGS_GE_INVALID_VALUE');
234 IGS_GE_MSG_STACK.ADD;
235 APP_EXCEPTION.RAISE_EXCEPTION;
236 END IF;
237 END IF;
238 IF UPPER(column_name) = 'ASSOCIATE_TEACH_REF_NUM' OR
239 column_name IS NULL THEN
240 IF new_references.associate_teach_ref_num NOT IN ( 'Y' , 'N') THEN
241 FND_MESSAGE.SET_NAME ('IGS','IGS_GE_INVALID_VALUE');
242 IGS_GE_MSG_STACK.ADD;
243 APP_EXCEPTION.RAISE_EXCEPTION;
244 END IF;
245 END IF;
246 IF UPPER(column_name) = 'ASSOCIATE_NHS_REG_NUM' OR
247 column_name IS NULL THEN
248 IF new_references.associate_nhs_reg_num NOT IN ( 'Y' , 'N') THEN
249 FND_MESSAGE.SET_NAME ('IGS','IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 APP_EXCEPTION.RAISE_EXCEPTION;
252 END IF;
253 END IF;
254 END check_constraints;
255
256 PROCEDURE check_uniqueness AS
257 /*
258 || Created By : [email protected]
259 || Created On : 21-FEB-2002
260 || Purpose : Handles the Unique Constraint logic defined for the columns.
261 || Known limitations, enhancements or remarks :
262 || Change History :
263 || Who When What
264 || (reverse chronological order - newest change first)
265 */
266 BEGIN
267
268 IF ( get_uk_for_validation (
269 new_references.person_id,
270 new_references.course_cd
271 )
272 ) THEN
273 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
274 igs_ge_msg_stack.add;
275 app_exception.raise_exception;
276 END IF;
277
278 END check_uniqueness;
279
280
281 PROCEDURE check_parent_existance AS
282 /*
283 || Created By : [email protected]
284 || Created On : 21-FEB-2002
285 || Purpose : Checks for the existance of Parent records.
286 || Known limitations, enhancements or remarks :
287 || Change History :
288 || Who When What
289 || (reverse chronological order - newest change first)
290 */
291 BEGIN
292
293 IF (((old_references.person_id = new_references.person_id) AND
294 (old_references.course_cd = new_references.course_cd)) OR
295 ((new_references.person_id IS NULL) OR
296 (new_references.course_cd IS NULL))) THEN
297 NULL;
298 ELSIF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
299 new_references.person_id,
300 new_references.course_cd
301 ) THEN
302 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
303 igs_ge_msg_stack.add;
304 app_exception.raise_exception;
305 END IF;
306
307 END check_parent_existance;
308
309
310 PROCEDURE check_child_existance IS
311 /*
312 || Created By : [email protected]
313 || Created On : 21-FEB-2002
314 || Purpose : Checks for the existance of Child records.
315 || Known limitations, enhancements or remarks :
316 || Change History :
317 || Who When What
318 || (reverse chronological order - newest change first)
319 */
320 BEGIN
321
322 igs_he_st_spa_ut_all_pkg.get_ufk_igs_he_st_spa_all (
323 old_references.person_id,
324 old_references.course_cd
325 );
326
327 END check_child_existance;
328
329
330 PROCEDURE check_uk_child_existance IS
331 /*
332 || Created By : [email protected]
333 || Created On : 21-FEB-2002
334 || Purpose : Checks for the existance of Child records based on Unique Keys of this table.
335 || Known limitations, enhancements or remarks :
336 || Change History :
337 || Who When What
338 || (reverse chronological order - newest change first)
339 */
340 BEGIN
341
342 IF (((old_references.person_id = new_references.person_id) AND
343 (old_references.course_cd = new_references.course_cd)) OR
344 ((old_references.person_id IS NULL) OR
345 (old_references.course_cd IS NULL))) THEN
346 NULL;
347 ELSE igs_he_st_spa_ut_all_pkg.get_ufk_igs_he_st_spa_all (
348 old_references.person_id,
349 old_references.course_cd
350 );
351 END IF;
352
353 END check_uk_child_existance;
354
355
356 FUNCTION get_pk_for_validation (
357 x_hesa_st_spa_id IN NUMBER
358 ) RETURN BOOLEAN AS
359 /*
360 || Created By : [email protected]
361 || Created On : 21-FEB-2002
362 || Purpose : Validates the Primary Key of the table.
363 || Known limitations, enhancements or remarks :
364 || Change History :
365 || Who When What
366 || (reverse chronological order - newest change first)
367 */
368 CURSOR cur_rowid IS
369 SELECT rowid
370 FROM igs_he_st_spa_all
371 WHERE hesa_st_spa_id = x_hesa_st_spa_id
372 FOR UPDATE NOWAIT;
373
374 lv_rowid cur_rowid%RowType;
375
376 BEGIN
377
378 OPEN cur_rowid;
379 FETCH cur_rowid INTO lv_rowid;
380 IF (cur_rowid%FOUND) THEN
381 CLOSE cur_rowid;
382 RETURN(TRUE);
383 ELSE
384 CLOSE cur_rowid;
385 RETURN(FALSE);
386 END IF;
387
388 END get_pk_for_validation;
389
390
391 FUNCTION get_uk_for_validation (
392 x_person_id IN NUMBER,
393 x_course_cd IN VARCHAR2
394 ) RETURN BOOLEAN AS
395 /*
396 || Created By : [email protected]
397 || Created On : 21-FEB-2002
398 || Purpose : Validates the Unique Keys of the table.
399 || Known limitations, enhancements or remarks :
400 || Change History :
401 || Who When What
402 || (reverse chronological order - newest change first)
403 */
404 CURSOR cur_rowid IS
405 SELECT rowid
406 FROM igs_he_st_spa_all
407 WHERE person_id = x_person_id
408 AND course_cd = x_course_cd
409 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
410 FOR UPDATE NOWAIT;
411
412 lv_rowid cur_rowid%RowType;
413
414 BEGIN
415
416 OPEN cur_rowid;
417 FETCH cur_rowid INTO lv_rowid;
418 IF (cur_rowid%FOUND) THEN
419 CLOSE cur_rowid;
420 RETURN (true);
421 ELSE
422 CLOSE cur_rowid;
423 RETURN(FALSE);
424 END IF;
425
426 END get_uk_for_validation ;
427
428
429 PROCEDURE get_fk_igs_en_stdnt_ps_att_all (
430 x_person_id IN NUMBER,
431 x_course_cd IN VARCHAR2
432 ) AS
433 /*
434 || Created By : [email protected]
435 || Created On : 21-FEB-2002
436 || Purpose : Validates the Foreign Keys for the table.
437 || Known limitations, enhancements or remarks :
438 || Change History :
439 || Who When What
440 || (reverse chronological order - newest change first)
441 */
442 CURSOR cur_rowid IS
443 SELECT rowid
444 FROM igs_he_st_spa_all
445 WHERE ((course_cd = x_course_cd) AND
446 (person_id = x_person_id));
447
448 lv_rowid cur_rowid%RowType;
449
450 BEGIN
451
452 OPEN cur_rowid;
453 FETCH cur_rowid INTO lv_rowid;
454 IF (cur_rowid%FOUND) THEN
455 CLOSE cur_rowid;
456 fnd_message.set_name ('IGS', 'IGS_HE_HSPA_SCA_FK');
457 igs_ge_msg_stack.add;
458 app_exception.raise_exception;
459 RETURN;
460 END IF;
461 CLOSE cur_rowid;
462
463 END get_fk_igs_en_stdnt_ps_att_all;
464
465
466 PROCEDURE before_dml (
467 p_action IN VARCHAR2,
468 x_rowid IN VARCHAR2 ,
469 x_hesa_st_spa_id IN NUMBER ,
470 x_org_id IN NUMBER ,
471 x_person_id IN NUMBER ,
472 x_course_cd IN VARCHAR2 ,
473 x_version_number IN NUMBER ,
474 x_fe_student_marker IN VARCHAR2 ,
475 x_domicile_cd IN VARCHAR2 ,
476 x_inst_last_attended IN VARCHAR2 ,
477 x_year_left_last_inst IN VARCHAR2 ,
478 x_highest_qual_on_entry IN VARCHAR2 ,
479 x_date_qual_on_entry_calc IN DATE ,
480 x_a_level_point_score IN NUMBER ,
481 x_highers_points_scores IN NUMBER ,
482 x_occupation_code IN VARCHAR2 ,
483 x_commencement_dt IN DATE ,
484 x_special_student IN VARCHAR2 ,
485 x_student_qual_aim IN VARCHAR2 ,
486 x_student_fe_qual_aim IN VARCHAR2 ,
487 x_teacher_train_prog_id IN VARCHAR2 ,
488 x_itt_phase IN VARCHAR2 ,
489 x_bilingual_itt_marker IN VARCHAR2 ,
490 x_teaching_qual_gain_sector IN VARCHAR2 ,
491 x_teaching_qual_gain_subj1 IN VARCHAR2 ,
492 x_teaching_qual_gain_subj2 IN VARCHAR2 ,
493 x_teaching_qual_gain_subj3 IN VARCHAR2 ,
494 x_student_inst_number IN VARCHAR2 ,
495 x_destination IN VARCHAR2 ,
496 x_itt_prog_outcome IN VARCHAR2 ,
497 x_hesa_return_name IN VARCHAR2 ,
498 x_hesa_return_id IN NUMBER ,
499 x_hesa_submission_name IN VARCHAR2 ,
500 x_associate_ucas_number IN VARCHAR2 ,
501 x_associate_scott_cand IN VARCHAR2 ,
502 x_associate_teach_ref_num IN VARCHAR2 ,
503 x_associate_nhs_reg_num IN VARCHAR2 ,
504 x_nhs_funding_source IN VARCHAR2 ,
505 x_ufi_place IN VARCHAR2 ,
506 x_postcode IN VARCHAR2 ,
507 x_social_class_ind IN VARCHAR2 ,
508 x_occcode IN VARCHAR2 ,
509 x_total_ucas_tariff IN NUMBER ,
510 x_nhs_employer IN VARCHAR2 ,
511 x_return_type IN VARCHAR2 ,
512 x_qual_aim_subj1 IN VARCHAR2 ,
513 x_qual_aim_subj2 IN VARCHAR2 ,
514 x_qual_aim_subj3 IN VARCHAR2 ,
515 x_qual_aim_proportion IN VARCHAR2 ,
516 x_creation_date IN DATE ,
517 x_created_by IN NUMBER ,
518 x_last_update_date IN DATE ,
519 x_last_updated_by IN NUMBER ,
520 x_last_update_login IN NUMBER ,
521 x_dependants_cd IN VARCHAR2,
522 x_implied_fund_rate IN NUMBER ,
523 x_gov_initiatives_cd IN VARCHAR2,
524 x_units_for_qual IN NUMBER ,
525 x_disadv_uplift_elig_cd IN VARCHAR2,
526 x_franch_partner_cd IN VARCHAR2,
527 x_units_completed IN NUMBER ,
528 x_franch_out_arr_cd IN VARCHAR2,
529 x_employer_role_cd IN VARCHAR2,
530 x_disadv_uplift_factor IN NUMBER ,
531 x_enh_fund_elig_cd IN VARCHAR2,
532 x_exclude_flag IN VARCHAR2
533 ) AS
534 /*
535 || Created By : [email protected]
536 || Created On : 21-FEB-2002
537 || Purpose : Initialises the columns, Checks Constraints, Calls the
538 || Trigger Handlers for the table, before any DML operation.
539 || Known limitations, enhancements or remarks :
540 || Change History :
541 || Who When What
542 || (reverse chronological order - newest change first)
543 */
544 BEGIN
545
546 set_column_values (
547 p_action,
548 x_rowid,
549 x_hesa_st_spa_id,
550 x_org_id,
551 x_person_id,
552 x_course_cd,
553 x_version_number,
554 x_fe_student_marker,
555 x_domicile_cd,
556 x_inst_last_attended,
557 x_year_left_last_inst,
558 x_highest_qual_on_entry,
559 x_date_qual_on_entry_calc,
560 x_a_level_point_score,
561 x_highers_points_scores,
562 x_occupation_code,
563 x_commencement_dt,
564 x_special_student,
565 x_student_qual_aim,
566 x_student_fe_qual_aim,
567 x_teacher_train_prog_id,
568 x_itt_phase,
569 x_bilingual_itt_marker,
570 x_teaching_qual_gain_sector,
571 x_teaching_qual_gain_subj1,
572 x_teaching_qual_gain_subj2,
573 x_teaching_qual_gain_subj3,
574 x_student_inst_number,
575 x_destination,
576 x_itt_prog_outcome,
577 x_hesa_return_name,
578 x_hesa_return_id,
579 x_hesa_submission_name,
580 x_associate_ucas_number,
581 x_associate_scott_cand,
582 x_associate_teach_ref_num,
583 x_associate_nhs_reg_num,
584 x_nhs_funding_source,
585 x_ufi_place,
586 x_postcode,
587 x_social_class_ind,
588 x_occcode,
589 x_total_ucas_tariff,
590 x_nhs_employer,
591 x_return_type,
592 x_qual_aim_subj1 ,
593 x_qual_aim_subj2,
594 x_qual_aim_subj3,
595 x_qual_aim_proportion,
596 x_creation_date,
597 x_created_by,
598 x_last_update_date,
599 x_last_updated_by,
600 x_last_update_login,
601 x_dependants_cd,
602 x_implied_fund_rate,
603 x_gov_initiatives_cd,
604 x_units_for_qual,
605 x_disadv_uplift_elig_cd,
606 x_franch_partner_cd,
607 x_units_completed,
608 x_franch_out_arr_cd,
609 x_employer_role_cd,
610 x_disadv_uplift_factor,
611 x_enh_fund_elig_cd,
612 x_exclude_flag
613 );
614
615 IF (p_action = 'INSERT') THEN
616 -- Call all the procedures related to Before Insert.
617 IF ( get_pk_for_validation(
618 new_references.hesa_st_spa_id
619 )
620 ) THEN
621 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
622 igs_ge_msg_stack.add;
623 app_exception.raise_exception;
624 END IF;
625 check_uniqueness;
626 check_parent_existance;
627 ELSIF (p_action = 'UPDATE') THEN
628 -- Call all the procedures related to Before Update.
629 check_uniqueness;
630 check_parent_existance;
631 check_uk_child_existance;
632 ELSIF (p_action = 'DELETE') THEN
633 -- Call all the procedures related to Before Delete.
634 check_child_existance;
635 ELSIF (p_action = 'VALIDATE_INSERT') THEN
636 -- Call all the procedures related to Before Insert.
637 IF ( get_pk_for_validation (
638 new_references.hesa_st_spa_id
639 )
640 ) THEN
641 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
642 igs_ge_msg_stack.add;
643 app_exception.raise_exception;
644 END IF;
645 check_uniqueness;
646 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
647 check_uniqueness;
648 check_uk_child_existance;
649 ELSIF (p_action = 'VALIDATE_DELETE') THEN
650 check_child_existance;
651 END IF;
652
653 END before_dml;
654
655
656 PROCEDURE insert_row (
657 x_rowid IN OUT NOCOPY VARCHAR2,
658 x_hesa_st_spa_id IN OUT NOCOPY NUMBER,
659 x_org_id IN NUMBER,
660 x_person_id IN NUMBER,
661 x_course_cd IN VARCHAR2,
662 x_version_number IN NUMBER,
663 x_fe_student_marker IN VARCHAR2,
664 x_domicile_cd IN VARCHAR2,
665 x_inst_last_attended IN VARCHAR2,
666 x_year_left_last_inst IN VARCHAR2,
667 x_highest_qual_on_entry IN VARCHAR2,
668 x_date_qual_on_entry_calc IN DATE,
669 x_a_level_point_score IN NUMBER,
670 x_highers_points_scores IN NUMBER,
671 x_occupation_code IN VARCHAR2,
672 x_commencement_dt IN DATE,
673 x_special_student IN VARCHAR2,
674 x_student_qual_aim IN VARCHAR2,
675 x_student_fe_qual_aim IN VARCHAR2,
676 x_teacher_train_prog_id IN VARCHAR2,
677 x_itt_phase IN VARCHAR2,
678 x_bilingual_itt_marker IN VARCHAR2,
679 x_teaching_qual_gain_sector IN VARCHAR2,
680 x_teaching_qual_gain_subj1 IN VARCHAR2,
681 x_teaching_qual_gain_subj2 IN VARCHAR2,
682 x_teaching_qual_gain_subj3 IN VARCHAR2,
683 x_student_inst_number IN VARCHAR2,
684 x_destination IN VARCHAR2,
685 x_itt_prog_outcome IN VARCHAR2,
686 x_hesa_return_name IN VARCHAR2,
687 x_hesa_return_id IN NUMBER,
688 x_hesa_submission_name IN VARCHAR2,
689 x_associate_ucas_number IN VARCHAR2,
690 x_associate_scott_cand IN VARCHAR2,
691 x_associate_teach_ref_num IN VARCHAR2,
692 x_associate_nhs_reg_num IN VARCHAR2,
693 x_nhs_funding_source IN VARCHAR2,
694 x_ufi_place IN VARCHAR2,
695 x_postcode IN VARCHAR2,
696 x_social_class_ind IN VARCHAR2,
697 x_occcode IN VARCHAR2,
698 x_total_ucas_tariff IN NUMBER,
699 x_nhs_employer IN VARCHAR2,
700 x_return_type IN VARCHAR2,
701 x_qual_aim_subj1 IN VARCHAR2,
702 x_qual_aim_subj2 IN VARCHAR2,
703 x_qual_aim_subj3 IN VARCHAR2,
704 x_qual_aim_proportion IN VARCHAR2,
705 x_mode IN VARCHAR2,
706 x_dependants_cd IN VARCHAR2 ,
707 x_implied_fund_rate IN NUMBER ,
708 x_gov_initiatives_cd IN VARCHAR2 ,
709 x_units_for_qual IN NUMBER ,
710 x_disadv_uplift_elig_cd IN VARCHAR2 ,
711 x_franch_partner_cd IN VARCHAR2 ,
712 x_units_completed IN NUMBER ,
713 x_franch_out_arr_cd IN VARCHAR2 ,
714 x_employer_role_cd IN VARCHAR2 ,
715 x_disadv_uplift_factor IN NUMBER ,
716 x_enh_fund_elig_cd IN VARCHAR2 ,
717 x_exclude_flag IN VARCHAR2
718 ) AS
719 /*
720 || Created By : [email protected]
721 || Created On : 21-FEB-2002
722 || Purpose : Handles the INSERT DML logic for the table.
723 || Known limitations, enhancements or remarks :
724 || Change History :
725 || Who When What
726 || smvk 13-Feb-2002 Call to igs_ge_gen_003.get_org_id
727 || w.r.t. SWCR006
728 || (reverse chronological order - newest change first)
729 */
730 CURSOR c IS
731 SELECT rowid
732 FROM igs_he_st_spa_all
733 WHERE hesa_st_spa_id = x_hesa_st_spa_id;
734
735 x_last_update_date DATE;
736 x_last_updated_by NUMBER;
737 x_last_update_login NUMBER;
738
739 l_mode VARCHAR2(1);
740
741 BEGIN
742
743 l_mode := NVL(x_mode,'R');
744
745 x_last_update_date := SYSDATE;
746 IF (l_mode = 'I') THEN
747 x_last_updated_by := 1;
748 x_last_update_login := 0;
749 ELSIF (l_mode IN ('R','S')) THEN
750 x_last_updated_by := fnd_global.user_id;
751 IF (x_last_updated_by IS NULL) THEN
752 x_last_updated_by := -1;
753 END IF;
754 x_last_update_login := fnd_global.login_id;
755 IF (x_last_update_login IS NULL) THEN
756 x_last_update_login := -1;
757 END IF;
758 ELSE
759 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
760 igs_ge_msg_stack.add;
761 app_exception.raise_exception;
762 END IF;
763
764 SELECT igs_he_st_spa_all_s.NEXTVAL
765 INTO x_hesa_st_spa_id
766 FROM dual;
767
768 before_dml(
769 p_action => 'INSERT',
770 x_rowid => x_rowid,
771 x_hesa_st_spa_id => x_hesa_st_spa_id,
772 x_org_id => igs_ge_gen_003.get_org_id,
773 x_person_id => x_person_id,
774 x_course_cd => x_course_cd,
775 x_version_number => x_version_number,
776 x_fe_student_marker => x_fe_student_marker,
777 x_domicile_cd => x_domicile_cd,
778 x_inst_last_attended => x_inst_last_attended,
779 x_year_left_last_inst => x_year_left_last_inst,
780 x_highest_qual_on_entry => x_highest_qual_on_entry,
781 x_date_qual_on_entry_calc => x_date_qual_on_entry_calc,
782 x_a_level_point_score => x_a_level_point_score,
783 x_highers_points_scores => x_highers_points_scores,
784 x_occupation_code => x_occupation_code,
785 x_commencement_dt => x_commencement_dt,
786 x_special_student => x_special_student,
787 x_student_qual_aim => x_student_qual_aim,
788 x_student_fe_qual_aim => x_student_fe_qual_aim,
789 x_teacher_train_prog_id => x_teacher_train_prog_id,
790 x_itt_phase => x_itt_phase,
791 x_bilingual_itt_marker => x_bilingual_itt_marker,
792 x_teaching_qual_gain_sector => x_teaching_qual_gain_sector,
793 x_teaching_qual_gain_subj1 => x_teaching_qual_gain_subj1,
794 x_teaching_qual_gain_subj2 => x_teaching_qual_gain_subj2,
795 x_teaching_qual_gain_subj3 => x_teaching_qual_gain_subj3,
796 x_student_inst_number => x_student_inst_number,
797 x_destination => x_destination,
798 x_itt_prog_outcome => x_itt_prog_outcome,
799 x_hesa_return_name => x_hesa_return_name,
800 x_hesa_return_id => x_hesa_return_id,
801 x_hesa_submission_name => x_hesa_submission_name,
802 x_associate_ucas_number => x_associate_ucas_number,
803 x_associate_scott_cand => x_associate_scott_cand,
804 x_associate_teach_ref_num => x_associate_teach_ref_num,
805 x_associate_nhs_reg_num => x_associate_nhs_reg_num,
806 x_nhs_funding_source => x_nhs_funding_source,
807 x_ufi_place => x_ufi_place,
808 x_postcode => x_postcode,
809 x_social_class_ind => x_social_class_ind,
810 x_occcode => x_occcode,
811 x_total_ucas_tariff => x_total_ucas_tariff,
812 x_nhs_employer => x_nhs_employer,
813 x_return_type => x_return_type,
814 x_qual_aim_subj1 => x_qual_aim_subj1,
815 x_qual_aim_subj2 => x_qual_aim_subj2,
816 x_qual_aim_subj3 => x_qual_aim_subj3,
817 x_qual_aim_proportion => x_qual_aim_proportion ,
818 x_creation_date => x_last_update_date,
819 x_created_by => x_last_updated_by,
820 x_last_update_date => x_last_update_date,
821 x_last_updated_by => x_last_updated_by,
822 x_last_update_login => x_last_update_login,
823 x_dependants_cd => x_dependants_cd,
824 x_implied_fund_rate => x_implied_fund_rate,
825 x_gov_initiatives_cd => x_gov_initiatives_cd,
826 x_units_for_qual => x_units_for_qual,
827 x_disadv_uplift_elig_cd => x_disadv_uplift_elig_cd,
828 x_franch_partner_cd => x_franch_partner_cd,
829 x_units_completed => x_units_completed,
830 x_franch_out_arr_cd => x_franch_out_arr_cd,
831 x_employer_role_cd => x_employer_role_cd,
832 x_disadv_uplift_factor => x_disadv_uplift_factor,
833 x_enh_fund_elig_cd => x_enh_fund_elig_cd,
834 x_exclude_flag => x_exclude_flag
835 );
836
837 IF (x_mode = 'S') THEN
838 igs_sc_gen_001.set_ctx('R');
839 END IF;
840 INSERT INTO igs_he_st_spa_all (
841 hesa_st_spa_id,
842 org_id,
843 person_id,
844 course_cd,
845 version_number,
846 fe_student_marker,
847 domicile_cd,
848 inst_last_attended,
849 year_left_last_inst,
850 highest_qual_on_entry,
851 date_qual_on_entry_calc,
852 a_level_point_score,
853 highers_points_scores,
854 occupation_code,
855 commencement_dt,
856 special_student,
857 student_qual_aim,
858 student_fe_qual_aim,
859 teacher_train_prog_id,
860 itt_phase,
861 bilingual_itt_marker,
862 teaching_qual_gain_sector,
863 teaching_qual_gain_subj1,
864 teaching_qual_gain_subj2,
865 teaching_qual_gain_subj3,
866 student_inst_number,
867 destination,
868 itt_prog_outcome,
869 hesa_return_name,
870 hesa_return_id,
871 hesa_submission_name,
872 associate_ucas_number,
873 associate_scott_cand,
874 associate_teach_ref_num,
875 associate_nhs_reg_num,
876 nhs_funding_source,
877 ufi_place,
878 postcode,
879 social_class_ind,
880 occcode,
881 total_ucas_tariff,
882 nhs_employer,
883 return_type,
884 qual_aim_subj1,
885 qual_aim_subj2,
886 qual_aim_subj3,
887 qual_aim_proportion,
888 creation_date,
889 created_by,
890 last_update_date,
891 last_updated_by,
892 last_update_login,
893 dependants_cd,
894 implied_fund_rate,
895 gov_initiatives_cd,
896 units_for_qual,
897 disadv_uplift_elig_cd,
898 franch_partner_cd,
899 units_completed,
900 franch_out_arr_cd,
901 employer_role_cd,
902 disadv_uplift_factor,
903 enh_fund_elig_cd,
904 exclude_flag
905 ) VALUES (
906 new_references.hesa_st_spa_id,
907 new_references.org_id,
908 new_references.person_id,
909 new_references.course_cd,
910 new_references.version_number,
911 new_references.fe_student_marker,
912 new_references.domicile_cd,
913 new_references.inst_last_attended,
914 new_references.year_left_last_inst,
915 new_references.highest_qual_on_entry,
916 new_references.date_qual_on_entry_calc,
917 new_references.a_level_point_score,
918 new_references.highers_points_scores,
919 new_references.occupation_code,
920 new_references.commencement_dt,
921 new_references.special_student,
922 new_references.student_qual_aim,
923 new_references.student_fe_qual_aim,
924 new_references.teacher_train_prog_id,
925 new_references.itt_phase,
926 new_references.bilingual_itt_marker,
927 new_references.teaching_qual_gain_sector,
928 new_references.teaching_qual_gain_subj1,
929 new_references.teaching_qual_gain_subj2,
930 new_references.teaching_qual_gain_subj3,
931 new_references.student_inst_number,
932 new_references.destination,
933 new_references.itt_prog_outcome,
934 new_references.hesa_return_name,
935 new_references.hesa_return_id,
936 new_references.hesa_submission_name,
937 new_references.associate_ucas_number,
938 new_references.associate_scott_cand,
939 new_references.associate_teach_ref_num,
940 new_references.associate_nhs_reg_num,
941 new_references.nhs_funding_source,
942 new_references.ufi_place,
943 new_references.postcode,
944 new_references.social_class_ind,
945 new_references.occcode,
946 new_references.total_ucas_tariff,
947 new_references.nhs_employer,
948 new_references.return_type,
949 new_references.qual_aim_subj1,
950 new_references.qual_aim_subj2,
951 new_references.qual_aim_subj3,
952 new_references.qual_aim_proportion,
953 x_last_update_date,
954 x_last_updated_by,
955 x_last_update_date,
956 x_last_updated_by,
957 x_last_update_login,
958 new_references.dependants_cd,
959 new_references.implied_fund_rate,
960 new_references.gov_initiatives_cd,
961 new_references.units_for_qual,
962 new_references.disadv_uplift_elig_cd,
963 new_references.franch_partner_cd,
964 new_references.units_completed,
965 new_references.franch_out_arr_cd,
966 new_references.employer_role_cd,
967 new_references.disadv_uplift_factor,
968 new_references.enh_fund_elig_cd,
969 new_references.exclude_flag
970 );
971 IF (x_mode = 'S') THEN
972 igs_sc_gen_001.unset_ctx('R');
973 END IF;
974
975
976 OPEN c;
977 FETCH c INTO x_rowid;
978 IF (c%NOTFOUND) THEN
979 CLOSE c;
980 RAISE NO_DATA_FOUND;
981 END IF;
982 CLOSE c;
983
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
988 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
989 fnd_message.set_token ('ERR_CD', SQLCODE);
990 igs_ge_msg_stack.add;
991 igs_sc_gen_001.unset_ctx('R');
992 app_exception.raise_exception;
993 ELSE
994 igs_sc_gen_001.unset_ctx('R');
995 RAISE;
996 END IF;
997
998 END insert_row;
999
1000
1001 PROCEDURE lock_row (
1002 x_rowid IN VARCHAR2,
1003 x_hesa_st_spa_id IN NUMBER,
1004 x_org_id IN NUMBER,
1005 x_person_id IN NUMBER,
1006 x_course_cd IN VARCHAR2,
1007 x_version_number IN NUMBER,
1008 x_fe_student_marker IN VARCHAR2,
1009 x_domicile_cd IN VARCHAR2,
1010 x_inst_last_attended IN VARCHAR2,
1011 x_year_left_last_inst IN VARCHAR2,
1012 x_highest_qual_on_entry IN VARCHAR2,
1013 x_date_qual_on_entry_calc IN DATE,
1014 x_a_level_point_score IN NUMBER,
1015 x_highers_points_scores IN NUMBER,
1016 x_occupation_code IN VARCHAR2,
1017 x_commencement_dt IN DATE,
1018 x_special_student IN VARCHAR2,
1019 x_student_qual_aim IN VARCHAR2,
1020 x_student_fe_qual_aim IN VARCHAR2,
1021 x_teacher_train_prog_id IN VARCHAR2,
1022 x_itt_phase IN VARCHAR2,
1023 x_bilingual_itt_marker IN VARCHAR2,
1024 x_teaching_qual_gain_sector IN VARCHAR2,
1025 x_teaching_qual_gain_subj1 IN VARCHAR2,
1026 x_teaching_qual_gain_subj2 IN VARCHAR2,
1027 x_teaching_qual_gain_subj3 IN VARCHAR2,
1028 x_student_inst_number IN VARCHAR2,
1029 x_destination IN VARCHAR2,
1030 x_itt_prog_outcome IN VARCHAR2,
1031 x_hesa_return_name IN VARCHAR2,
1032 x_hesa_return_id IN NUMBER,
1033 x_hesa_submission_name IN VARCHAR2,
1034 x_associate_ucas_number IN VARCHAR2,
1035 x_associate_scott_cand IN VARCHAR2,
1036 x_associate_teach_ref_num IN VARCHAR2,
1037 x_associate_nhs_reg_num IN VARCHAR2,
1038 x_nhs_funding_source IN VARCHAR2,
1039 x_ufi_place IN VARCHAR2,
1040 x_postcode IN VARCHAR2,
1041 x_social_class_ind IN VARCHAR2,
1042 x_occcode IN VARCHAR2,
1043 x_total_ucas_tariff IN NUMBER,
1044 x_nhs_employer IN VARCHAR2,
1045 x_return_type IN VARCHAR2,
1046 x_qual_aim_subj1 IN VARCHAR2,
1047 x_qual_aim_subj2 IN VARCHAR2,
1048 x_qual_aim_subj3 IN VARCHAR2,
1049 x_qual_aim_proportion IN VARCHAR2,
1050 x_dependants_cd IN VARCHAR2 ,
1051 x_implied_fund_rate IN NUMBER ,
1052 x_gov_initiatives_cd IN VARCHAR2 ,
1053 x_units_for_qual IN NUMBER ,
1054 x_disadv_uplift_elig_cd IN VARCHAR2 ,
1055 x_franch_partner_cd IN VARCHAR2 ,
1056 x_units_completed IN NUMBER ,
1057 x_franch_out_arr_cd IN VARCHAR2 ,
1058 x_employer_role_cd IN VARCHAR2 ,
1059 x_disadv_uplift_factor IN NUMBER ,
1060 x_enh_fund_elig_cd IN VARCHAR2 ,
1061 x_exclude_flag IN VARCHAR2
1062 ) AS
1063 /*
1064 || Created By : [email protected]
1065 || Created On : 21-FEB-2002
1066 || Purpose : Handles the LOCK mechanism for the table.
1067 || Known limitations, enhancements or remarks :
1068 || Change History :
1069 || Who When What
1070 || smvk 13-feb-2002 Removed org_id from cursor declaration
1071 || and conditional checking w.r.t. SWCR006
1072 ||
1073 || (reverse chronological order - newest change first)
1074 */
1075 CURSOR c1 IS
1076 SELECT
1077 person_id,
1078 course_cd,
1079 version_number,
1080 fe_student_marker,
1081 domicile_cd,
1082 inst_last_attended,
1083 year_left_last_inst,
1084 highest_qual_on_entry,
1085 date_qual_on_entry_calc,
1086 a_level_point_score,
1087 highers_points_scores,
1088 occupation_code,
1089 commencement_dt,
1090 special_student,
1091 student_qual_aim,
1092 student_fe_qual_aim,
1093 teacher_train_prog_id,
1094 itt_phase,
1095 bilingual_itt_marker,
1096 teaching_qual_gain_sector,
1097 teaching_qual_gain_subj1,
1098 teaching_qual_gain_subj2,
1099 teaching_qual_gain_subj3,
1100 student_inst_number,
1101 destination,
1102 itt_prog_outcome,
1103 hesa_return_name,
1104 hesa_return_id,
1105 hesa_submission_name,
1106 associate_ucas_number,
1107 associate_scott_cand,
1108 associate_teach_ref_num,
1109 associate_nhs_reg_num,
1110 nhs_funding_source,
1111 ufi_place,
1112 postcode,
1113 social_class_ind,
1114 occcode,
1115 total_ucas_tariff,
1116 nhs_employer,
1117 return_type,
1118 qual_aim_subj1,
1119 qual_aim_subj2 ,
1120 qual_aim_subj3 ,
1121 qual_aim_proportion,
1122 dependants_cd,
1123 implied_fund_rate,
1124 gov_initiatives_cd,
1125 units_for_qual,
1126 disadv_uplift_elig_cd,
1127 franch_partner_cd,
1128 units_completed,
1129 franch_out_arr_cd,
1130 employer_role_cd,
1131 disadv_uplift_factor,
1132 enh_fund_elig_cd,
1133 exclude_flag
1134 FROM igs_he_st_spa_all
1135 WHERE rowid = x_rowid
1136 FOR UPDATE NOWAIT;
1137
1138 tlinfo c1%ROWTYPE;
1139
1140 BEGIN
1141
1142 OPEN c1;
1143 FETCH c1 INTO tlinfo;
1144 IF (c1%notfound) THEN
1145 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1146 igs_ge_msg_stack.add;
1147 CLOSE c1;
1148 app_exception.raise_exception;
1149 RETURN;
1150 END IF;
1151 CLOSE c1;
1152
1153 IF (
1154 (tlinfo.person_id = x_person_id)
1155 AND (tlinfo.course_cd = x_course_cd)
1156 AND (tlinfo.version_number = x_version_number)
1157 AND ((tlinfo.fe_student_marker = x_fe_student_marker) OR ((tlinfo.fe_student_marker IS NULL) AND (X_fe_student_marker IS NULL)))
1158 AND ((tlinfo.domicile_cd = x_domicile_cd) OR ((tlinfo.domicile_cd IS NULL) AND (X_domicile_cd IS NULL)))
1159 AND ((tlinfo.inst_last_attended = x_inst_last_attended) OR ((tlinfo.inst_last_attended IS NULL) AND (X_inst_last_attended IS NULL)))
1160 AND ((tlinfo.year_left_last_inst = x_year_left_last_inst) OR ((tlinfo.year_left_last_inst IS NULL) AND (X_year_left_last_inst IS NULL)))
1161 AND ((tlinfo.highest_qual_on_entry = x_highest_qual_on_entry) OR ((tlinfo.highest_qual_on_entry IS NULL) AND (X_highest_qual_on_entry IS NULL)))
1162 AND ((tlinfo.date_qual_on_entry_calc = x_date_qual_on_entry_calc) OR ((tlinfo.date_qual_on_entry_calc IS NULL) AND (X_date_qual_on_entry_calc IS NULL)))
1163 AND ((tlinfo.a_level_point_score = x_a_level_point_score) OR ((tlinfo.a_level_point_score IS NULL) AND (X_a_level_point_score IS NULL)))
1164 AND ((tlinfo.highers_points_scores = x_highers_points_scores) OR ((tlinfo.highers_points_scores IS NULL) AND (X_highers_points_scores IS NULL)))
1165 AND ((tlinfo.occupation_code = x_occupation_code) OR ((tlinfo.occupation_code IS NULL) AND (X_occupation_code IS NULL)))
1166 AND ((tlinfo.commencement_dt = x_commencement_dt) OR ((tlinfo.commencement_dt IS NULL) AND (X_commencement_dt IS NULL)))
1167 AND ((tlinfo.special_student = x_special_student) OR ((tlinfo.special_student IS NULL) AND (X_special_student IS NULL)))
1168 AND ((tlinfo.student_qual_aim = x_student_qual_aim) OR ((tlinfo.student_qual_aim IS NULL) AND (X_student_qual_aim IS NULL)))
1169 AND ((tlinfo.student_fe_qual_aim = x_student_fe_qual_aim) OR ((tlinfo.student_fe_qual_aim IS NULL) AND (X_student_fe_qual_aim IS NULL)))
1170 AND ((tlinfo.teacher_train_prog_id = x_teacher_train_prog_id) OR ((tlinfo.teacher_train_prog_id IS NULL) AND (X_teacher_train_prog_id IS NULL)))
1171 AND ((tlinfo.itt_phase = x_itt_phase) OR ((tlinfo.itt_phase IS NULL) AND (X_itt_phase IS NULL)))
1172 AND ((tlinfo.bilingual_itt_marker = x_bilingual_itt_marker) OR ((tlinfo.bilingual_itt_marker IS NULL) AND (X_bilingual_itt_marker IS NULL)))
1173 AND ((tlinfo.teaching_qual_gain_sector = x_teaching_qual_gain_sector) OR ((tlinfo.teaching_qual_gain_sector IS NULL) AND (X_teaching_qual_gain_sector IS NULL)))
1174 AND ((tlinfo.teaching_qual_gain_subj1 = x_teaching_qual_gain_subj1) OR ((tlinfo.teaching_qual_gain_subj1 IS NULL) AND (X_teaching_qual_gain_subj1 IS NULL)))
1175 AND ((tlinfo.teaching_qual_gain_subj2 = x_teaching_qual_gain_subj2) OR ((tlinfo.teaching_qual_gain_subj2 IS NULL) AND (X_teaching_qual_gain_subj2 IS NULL)))
1176 AND ((tlinfo.teaching_qual_gain_subj3 = x_teaching_qual_gain_subj3) OR ((tlinfo.teaching_qual_gain_subj3 IS NULL) AND (X_teaching_qual_gain_subj3 IS NULL)))
1177 AND ((tlinfo.student_inst_number = x_student_inst_number) OR ((tlinfo.student_inst_number IS NULL) AND (X_student_inst_number IS NULL)))
1178 AND ((tlinfo.destination = x_destination) OR ((tlinfo.destination IS NULL) AND (X_destination IS NULL)))
1179 AND ((tlinfo.itt_prog_outcome = x_itt_prog_outcome) OR ((tlinfo.itt_prog_outcome IS NULL) AND (X_itt_prog_outcome IS NULL)))
1180 AND ((tlinfo.hesa_return_name = x_hesa_return_name) OR ((tlinfo.hesa_return_name IS NULL) AND (X_hesa_return_name IS NULL)))
1181 AND ((tlinfo.hesa_return_id = x_hesa_return_id) OR ((tlinfo.hesa_return_id IS NULL) AND (X_hesa_return_id IS NULL)))
1182 AND ((tlinfo.hesa_submission_name = x_hesa_submission_name) OR ((tlinfo.hesa_submission_name IS NULL) AND (X_hesa_submission_name IS NULL)))
1183 AND ((tlinfo.associate_ucas_number = x_associate_ucas_number) OR ((tlinfo.associate_ucas_number IS NULL) AND (X_associate_ucas_number IS NULL)))
1184 AND ((tlinfo.associate_scott_cand = x_associate_scott_cand) OR ((tlinfo.associate_scott_cand IS NULL) AND (X_associate_scott_cand IS NULL)))
1185 AND ((tlinfo.associate_teach_ref_num = x_associate_teach_ref_num) OR ((tlinfo.associate_teach_ref_num IS NULL) AND (X_associate_teach_ref_num IS NULL)))
1186 AND ((tlinfo.associate_nhs_reg_num = x_associate_nhs_reg_num) OR ((tlinfo.associate_nhs_reg_num IS NULL) AND (X_associate_nhs_reg_num IS NULL)))
1187 AND ((tlinfo.nhs_funding_source = x_nhs_funding_source) OR ((tlinfo.nhs_funding_source IS NULL) AND (X_nhs_funding_source IS NULL)))
1188 AND ((tlinfo.ufi_place = x_ufi_place) OR ((tlinfo.ufi_place IS NULL) AND (X_ufi_place IS NULL)))
1189 AND ((tlinfo.postcode = x_postcode) OR ((tlinfo.postcode IS NULL) AND (X_postcode IS NULL)))
1190 AND ((tlinfo.social_class_ind = x_social_class_ind) OR ((tlinfo.social_class_ind IS NULL) AND (X_social_class_ind IS NULL)))
1191 AND ((tlinfo.occcode = x_occcode) OR ((tlinfo.occcode IS NULL) AND (X_occcode IS NULL)))
1192 AND ((tlinfo.total_ucas_tariff = x_total_ucas_tariff) OR ((tlinfo.total_ucas_tariff IS NULL) AND (X_total_ucas_tariff IS NULL)))
1193 AND ((tlinfo.nhs_employer = x_nhs_employer) OR ((tlinfo.nhs_employer IS NULL) AND (X_nhs_employer IS NULL)))
1194 AND ((tlinfo.return_type = x_return_type) OR ((tlinfo.return_type IS NULL) AND (X_return_type IS NULL)))
1195 AND ((tlinfo.qual_aim_subj1 = x_qual_aim_subj1) OR ((tlinfo.qual_aim_subj1 IS NULL) AND (X_qual_aim_subj1 IS NULL)))
1196 AND ((tlinfo.qual_aim_subj2 = x_qual_aim_subj2) OR ((tlinfo.qual_aim_subj2 IS NULL) AND (X_qual_aim_subj2 IS NULL)))
1197 AND ((tlinfo.qual_aim_subj3 = x_qual_aim_subj3) OR ((tlinfo.qual_aim_subj3 IS NULL) AND (X_qual_aim_subj3 IS NULL)))
1198 AND ((tlinfo.qual_aim_proportion = x_qual_aim_proportion) OR ((tlinfo.qual_aim_proportion IS NULL) AND (X_qual_aim_proportion IS NULL)))
1199 AND ((tlinfo.dependants_cd = x_dependants_cd) OR ((tlinfo.dependants_cd IS NULL) AND (x_dependants_cd IS NULL)))
1200 AND ((tlinfo.implied_fund_rate = x_implied_fund_rate) OR ((tlinfo.implied_fund_rate IS NULL) AND (x_implied_fund_rate IS NULL)))
1201 AND ((tlinfo.gov_initiatives_cd = x_gov_initiatives_cd) OR ((tlinfo.gov_initiatives_cd IS NULL) AND (x_gov_initiatives_cd IS NULL)))
1202 AND ((tlinfo.units_for_qual = x_units_for_qual) OR ((tlinfo.units_for_qual IS NULL) AND (x_units_for_qual IS NULL)))
1203 AND ((tlinfo.disadv_uplift_elig_cd = x_disadv_uplift_elig_cd) OR ((tlinfo.disadv_uplift_elig_cd IS NULL) AND (x_disadv_uplift_elig_cd IS NULL)))
1204 AND ((tlinfo.franch_partner_cd = x_franch_partner_cd) OR ((tlinfo.franch_partner_cd IS NULL) AND (x_franch_partner_cd IS NULL)))
1205 AND ((tlinfo.units_completed = x_units_completed) OR ((tlinfo.units_completed IS NULL) AND (x_units_completed IS NULL)))
1206 AND ((tlinfo.franch_out_arr_cd = x_franch_out_arr_cd) OR ((tlinfo.franch_out_arr_cd IS NULL) AND (x_franch_out_arr_cd IS NULL)))
1207 AND ((tlinfo.employer_role_cd = x_employer_role_cd) OR ((tlinfo.employer_role_cd IS NULL) AND (x_employer_role_cd IS NULL)))
1208 AND ((tlinfo.disadv_uplift_factor = x_disadv_uplift_factor) OR ((tlinfo.disadv_uplift_factor IS NULL) AND (x_disadv_uplift_factor IS NULL)))
1209 AND ((tlinfo.enh_fund_elig_cd = x_enh_fund_elig_cd) OR ((tlinfo.enh_fund_elig_cd IS NULL) AND (x_enh_fund_elig_cd IS NULL)))
1210 AND ((tlinfo.exclude_flag = x_exclude_flag) OR ((tlinfo.exclude_flag IS NULL) AND (x_exclude_flag IS NULL)))
1211 ) THEN
1212 NULL;
1213 ELSE
1214 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1215 igs_ge_msg_stack.add;
1216 app_exception.raise_exception;
1217 END IF;
1218
1219 RETURN;
1220
1221 END lock_row;
1222
1223
1224 PROCEDURE update_row (
1225 x_rowid IN VARCHAR2,
1226 x_hesa_st_spa_id IN NUMBER,
1227 x_org_id IN NUMBER,
1228 x_person_id IN NUMBER,
1229 x_course_cd IN VARCHAR2,
1230 x_version_number IN NUMBER,
1231 x_fe_student_marker IN VARCHAR2,
1232 x_domicile_cd IN VARCHAR2,
1233 x_inst_last_attended IN VARCHAR2,
1234 x_year_left_last_inst IN VARCHAR2,
1235 x_highest_qual_on_entry IN VARCHAR2,
1236 x_date_qual_on_entry_calc IN DATE,
1237 x_a_level_point_score IN NUMBER,
1238 x_highers_points_scores IN NUMBER,
1239 x_occupation_code IN VARCHAR2,
1240 x_commencement_dt IN DATE,
1241 x_special_student IN VARCHAR2,
1242 x_student_qual_aim IN VARCHAR2,
1243 x_student_fe_qual_aim IN VARCHAR2,
1244 x_teacher_train_prog_id IN VARCHAR2,
1245 x_itt_phase IN VARCHAR2,
1246 x_bilingual_itt_marker IN VARCHAR2,
1247 x_teaching_qual_gain_sector IN VARCHAR2,
1248 x_teaching_qual_gain_subj1 IN VARCHAR2,
1249 x_teaching_qual_gain_subj2 IN VARCHAR2,
1250 x_teaching_qual_gain_subj3 IN VARCHAR2,
1251 x_student_inst_number IN VARCHAR2,
1252 x_destination IN VARCHAR2,
1253 x_itt_prog_outcome IN VARCHAR2,
1254 x_hesa_return_name IN VARCHAR2,
1255 x_hesa_return_id IN NUMBER,
1256 x_hesa_submission_name IN VARCHAR2,
1257 x_associate_ucas_number IN VARCHAR2,
1258 x_associate_scott_cand IN VARCHAR2,
1259 x_associate_teach_ref_num IN VARCHAR2,
1260 x_associate_nhs_reg_num IN VARCHAR2,
1261 x_nhs_funding_source IN VARCHAR2,
1262 x_ufi_place IN VARCHAR2,
1263 x_postcode IN VARCHAR2,
1264 x_social_class_ind IN VARCHAR2,
1265 x_occcode IN VARCHAR2,
1266 x_total_ucas_tariff IN NUMBER,
1267 x_nhs_employer IN VARCHAR2,
1268 x_return_type IN VARCHAR2,
1269 x_qual_aim_subj1 IN VARCHAR2,
1270 x_qual_aim_subj2 IN VARCHAR2,
1271 x_qual_aim_subj3 IN VARCHAR2,
1272 x_qual_aim_proportion IN VARCHAR2,
1273 x_mode IN VARCHAR2,
1274 x_dependants_cd IN VARCHAR2 ,
1275 x_implied_fund_rate IN NUMBER ,
1276 x_gov_initiatives_cd IN VARCHAR2 ,
1277 x_units_for_qual IN NUMBER ,
1278 x_disadv_uplift_elig_cd IN VARCHAR2 ,
1279 x_franch_partner_cd IN VARCHAR2 ,
1280 x_units_completed IN NUMBER ,
1281 x_franch_out_arr_cd IN VARCHAR2 ,
1282 x_employer_role_cd IN VARCHAR2 ,
1283 x_disadv_uplift_factor IN NUMBER ,
1284 x_enh_fund_elig_cd IN VARCHAR2 ,
1285 x_exclude_flag IN VARCHAR2
1286 ) AS
1287 /*
1288 || Created By : [email protected]
1289 || Created On : 21-FEB-2002
1290 || Purpose : Handles the UPDATE DML logic for the table.
1291 || Known limitations, enhancements or remarks :
1292 || Change History :
1293 || Who When What
1294 ||smvk 13-feb-2002 Call to igs_ge_gen_003.get_org_id
1295 || w.r.t. SWCR006
1296 ||
1297 || (reverse chronological order - newest change first)
1298 */
1299 x_last_update_date DATE ;
1300 x_last_updated_by NUMBER;
1301 x_last_update_login NUMBER;
1302 l_mode VARCHAR2(1);
1303
1304 BEGIN
1305
1306 l_mode := NVL(x_mode,'R');
1307
1308 x_last_update_date := SYSDATE;
1309 IF (l_mode = 'I') THEN
1310 x_last_updated_by := 1;
1311 x_last_update_login := 0;
1312 ELSIF (l_mode IN ('R', 'S')) THEN
1313 x_last_updated_by := fnd_global.user_id;
1314 IF x_last_updated_by IS NULL THEN
1315 x_last_updated_by := -1;
1316 END IF;
1317 x_last_update_login := fnd_global.login_id;
1318 IF (x_last_update_login IS NULL) THEN
1319 x_last_update_login := -1;
1320 END IF;
1321 ELSE
1322 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1323 igs_ge_msg_stack.add;
1324 app_exception.raise_exception;
1325 END IF;
1326
1327 before_dml(
1328 p_action => 'UPDATE',
1329 x_rowid => x_rowid,
1330 x_hesa_st_spa_id => x_hesa_st_spa_id,
1331 x_org_id => igs_ge_gen_003.get_org_id,
1332 x_person_id => x_person_id,
1333 x_course_cd => x_course_cd,
1334 x_version_number => x_version_number,
1335 x_fe_student_marker => x_fe_student_marker,
1336 x_domicile_cd => x_domicile_cd,
1337 x_inst_last_attended => x_inst_last_attended,
1338 x_year_left_last_inst => x_year_left_last_inst,
1339 x_highest_qual_on_entry => x_highest_qual_on_entry,
1340 x_date_qual_on_entry_calc => x_date_qual_on_entry_calc,
1341 x_a_level_point_score => x_a_level_point_score,
1342 x_highers_points_scores => x_highers_points_scores,
1343 x_occupation_code => x_occupation_code,
1344 x_commencement_dt => x_commencement_dt,
1345 x_special_student => x_special_student,
1346 x_student_qual_aim => x_student_qual_aim,
1347 x_student_fe_qual_aim => x_student_fe_qual_aim,
1348 x_teacher_train_prog_id => x_teacher_train_prog_id,
1349 x_itt_phase => x_itt_phase,
1350 x_bilingual_itt_marker => x_bilingual_itt_marker,
1351 x_teaching_qual_gain_sector => x_teaching_qual_gain_sector,
1352 x_teaching_qual_gain_subj1 => x_teaching_qual_gain_subj1,
1353 x_teaching_qual_gain_subj2 => x_teaching_qual_gain_subj2,
1354 x_teaching_qual_gain_subj3 => x_teaching_qual_gain_subj3,
1355 x_student_inst_number => x_student_inst_number,
1356 x_destination => x_destination,
1357 x_itt_prog_outcome => x_itt_prog_outcome,
1358 x_hesa_return_name => x_hesa_return_name,
1359 x_hesa_return_id => x_hesa_return_id,
1360 x_hesa_submission_name => x_hesa_submission_name,
1361 x_associate_ucas_number => x_associate_ucas_number,
1362 x_associate_scott_cand => x_associate_scott_cand,
1363 x_associate_teach_ref_num => x_associate_teach_ref_num,
1364 x_associate_nhs_reg_num => x_associate_nhs_reg_num,
1365 x_nhs_funding_source => x_nhs_funding_source,
1366 x_ufi_place => x_ufi_place,
1367 x_postcode => x_postcode,
1368 x_social_class_ind => x_social_class_ind,
1369 x_occcode => x_occcode,
1370 x_total_ucas_tariff => x_total_ucas_tariff,
1371 x_nhs_employer => x_nhs_employer,
1372 x_return_type => x_return_type,
1373 x_qual_aim_subj1 => x_qual_aim_subj1,
1374 x_qual_aim_subj2 => x_qual_aim_subj2,
1375 x_qual_aim_subj3 => x_qual_aim_subj3,
1376 x_qual_aim_proportion => x_qual_aim_proportion ,
1377 x_creation_date => x_last_update_date,
1378 x_created_by => x_last_updated_by,
1379 x_last_update_date => x_last_update_date,
1380 x_last_updated_by => x_last_updated_by,
1381 x_last_update_login => x_last_update_login,
1382 x_dependants_cd => x_dependants_cd,
1383 x_implied_fund_rate => x_implied_fund_rate,
1384 x_gov_initiatives_cd => x_gov_initiatives_cd,
1385 x_units_for_qual => x_units_for_qual,
1386 x_disadv_uplift_elig_cd => x_disadv_uplift_elig_cd,
1387 x_franch_partner_cd => x_franch_partner_cd,
1388 x_units_completed => x_units_completed,
1389 x_franch_out_arr_cd => x_franch_out_arr_cd,
1390 x_employer_role_cd => x_employer_role_cd,
1391 x_disadv_uplift_factor => x_disadv_uplift_factor,
1392 x_enh_fund_elig_cd => x_enh_fund_elig_cd,
1393 x_exclude_flag => x_exclude_flag
1394 );
1395
1396 IF (x_mode = 'S') THEN
1397 igs_sc_gen_001.set_ctx('R');
1398 END IF;
1399 UPDATE igs_he_st_spa_all
1400 SET
1401 org_id = new_references.org_id,
1402 person_id = new_references.person_id,
1403 course_cd = new_references.course_cd,
1404 version_number = new_references.version_number,
1405 fe_student_marker = new_references.fe_student_marker,
1406 domicile_cd = new_references.domicile_cd,
1407 inst_last_attended = new_references.inst_last_attended,
1408 year_left_last_inst = new_references.year_left_last_inst,
1409 highest_qual_on_entry = new_references.highest_qual_on_entry,
1410 date_qual_on_entry_calc = new_references.date_qual_on_entry_calc,
1411 a_level_point_score = new_references.a_level_point_score,
1412 highers_points_scores = new_references.highers_points_scores,
1413 occupation_code = new_references.occupation_code,
1414 commencement_dt = new_references.commencement_dt,
1415 special_student = new_references.special_student,
1416 student_qual_aim = new_references.student_qual_aim,
1417 student_fe_qual_aim = new_references.student_fe_qual_aim,
1418 teacher_train_prog_id = new_references.teacher_train_prog_id,
1419 itt_phase = new_references.itt_phase,
1420 bilingual_itt_marker = new_references.bilingual_itt_marker,
1421 teaching_qual_gain_sector = new_references.teaching_qual_gain_sector,
1422 teaching_qual_gain_subj1 = new_references.teaching_qual_gain_subj1,
1423 teaching_qual_gain_subj2 = new_references.teaching_qual_gain_subj2,
1424 teaching_qual_gain_subj3 = new_references.teaching_qual_gain_subj3,
1425 student_inst_number = new_references.student_inst_number,
1426 destination = new_references.destination,
1427 itt_prog_outcome = new_references.itt_prog_outcome,
1428 hesa_return_name = new_references.hesa_return_name,
1429 hesa_return_id = new_references.hesa_return_id,
1430 hesa_submission_name = new_references.hesa_submission_name,
1431 associate_ucas_number = new_references.associate_ucas_number,
1432 associate_scott_cand = new_references.associate_scott_cand,
1433 associate_teach_ref_num = new_references.associate_teach_ref_num,
1434 associate_nhs_reg_num = new_references.associate_nhs_reg_num,
1435 nhs_funding_source = new_references.nhs_funding_source,
1436 ufi_place = new_references.ufi_place,
1437 postcode = new_references.postcode,
1438 social_class_ind = new_references.social_class_ind,
1439 occcode = new_references.occcode,
1440 total_ucas_tariff = new_references.total_ucas_tariff,
1441 nhs_employer = new_references.nhs_employer,
1442 return_type = new_references.return_type,
1443 qual_aim_subj1 = new_references.qual_aim_subj1,
1444 qual_aim_subj2 = new_references.qual_aim_subj2,
1445 qual_aim_subj3 = new_references.qual_aim_subj3,
1446 qual_aim_proportion = new_references.qual_aim_proportion ,
1447 last_update_date = x_last_update_date,
1448 last_updated_by = x_last_updated_by,
1449 last_update_login = x_last_update_login,
1450 dependants_cd = new_references.dependants_cd,
1451 implied_fund_rate = new_references.implied_fund_rate,
1452 gov_initiatives_cd = new_references.gov_initiatives_cd,
1453 units_for_qual = new_references.units_for_qual,
1454 disadv_uplift_elig_cd = new_references.disadv_uplift_elig_cd,
1455 franch_partner_cd = new_references.franch_partner_cd,
1456 units_completed = new_references.units_completed,
1457 franch_out_arr_cd = new_references.franch_out_arr_cd,
1458 employer_role_cd = new_references.employer_role_cd,
1459 disadv_uplift_factor = new_references.disadv_uplift_factor,
1460 enh_fund_elig_cd = new_references.enh_fund_elig_cd,
1461 exclude_flag = new_references.exclude_flag
1462 WHERE rowid = x_rowid;
1463
1464 IF (SQL%NOTFOUND) THEN
1465 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1466 igs_ge_msg_stack.add;
1467 igs_sc_gen_001.unset_ctx('R');
1468 app_exception.raise_exception;
1469 END IF;
1470 IF (x_mode = 'S') THEN
1471 igs_sc_gen_001.unset_ctx('R');
1472 END IF;
1473
1474 EXCEPTION
1475 WHEN OTHERS THEN
1476 IF (SQLCODE = (-28115)) THEN
1477 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1478 fnd_message.set_token ('ERR_CD', SQLCODE);
1479 igs_ge_msg_stack.add;
1480 igs_sc_gen_001.unset_ctx('R');
1481 app_exception.raise_exception;
1482 ELSE
1483 igs_sc_gen_001.unset_ctx('R');
1484 RAISE;
1485 END IF;
1486
1487 END update_row;
1488
1489
1490 PROCEDURE add_row (
1491 x_rowid IN OUT NOCOPY VARCHAR2,
1492 x_hesa_st_spa_id IN OUT NOCOPY NUMBER,
1493 x_org_id IN NUMBER,
1494 x_person_id IN NUMBER,
1495 x_course_cd IN VARCHAR2,
1496 x_version_number IN NUMBER,
1497 x_fe_student_marker IN VARCHAR2,
1498 x_domicile_cd IN VARCHAR2,
1499 x_inst_last_attended IN VARCHAR2,
1500 x_year_left_last_inst IN VARCHAR2,
1501 x_highest_qual_on_entry IN VARCHAR2,
1502 x_date_qual_on_entry_calc IN DATE,
1503 x_a_level_point_score IN NUMBER,
1504 x_highers_points_scores IN NUMBER,
1505 x_occupation_code IN VARCHAR2,
1506 x_commencement_dt IN DATE,
1507 x_special_student IN VARCHAR2,
1508 x_student_qual_aim IN VARCHAR2,
1509 x_student_fe_qual_aim IN VARCHAR2,
1510 x_teacher_train_prog_id IN VARCHAR2,
1511 x_itt_phase IN VARCHAR2,
1512 x_bilingual_itt_marker IN VARCHAR2,
1513 x_teaching_qual_gain_sector IN VARCHAR2,
1514 x_teaching_qual_gain_subj1 IN VARCHAR2,
1515 x_teaching_qual_gain_subj2 IN VARCHAR2,
1516 x_teaching_qual_gain_subj3 IN VARCHAR2,
1517 x_student_inst_number IN VARCHAR2,
1518 x_destination IN VARCHAR2,
1519 x_itt_prog_outcome IN VARCHAR2,
1520 x_hesa_return_name IN VARCHAR2,
1521 x_hesa_return_id IN NUMBER,
1522 x_hesa_submission_name IN VARCHAR2,
1523 x_associate_ucas_number IN VARCHAR2,
1524 x_associate_scott_cand IN VARCHAR2,
1525 x_associate_teach_ref_num IN VARCHAR2,
1526 x_associate_nhs_reg_num IN VARCHAR2,
1527 x_nhs_funding_source IN VARCHAR2,
1528 x_ufi_place IN VARCHAR2,
1529 x_postcode IN VARCHAR2,
1530 x_social_class_ind IN VARCHAR2,
1531 x_occcode IN VARCHAR2,
1532 x_total_ucas_tariff IN NUMBER,
1533 x_nhs_employer IN VARCHAR2,
1534 x_return_type IN VARCHAR2,
1535 x_qual_aim_subj1 IN VARCHAR2,
1536 x_qual_aim_subj2 IN VARCHAR2,
1537 x_qual_aim_subj3 IN VARCHAR2,
1538 x_qual_aim_proportion IN VARCHAR2,
1539 x_mode IN VARCHAR2,
1540 x_dependants_cd IN VARCHAR2,
1541 x_implied_fund_rate IN NUMBER ,
1542 x_gov_initiatives_cd IN VARCHAR2,
1543 x_units_for_qual IN NUMBER ,
1544 x_disadv_uplift_elig_cd IN VARCHAR2,
1545 x_franch_partner_cd IN VARCHAR2,
1546 x_units_completed IN NUMBER ,
1547 x_franch_out_arr_cd IN VARCHAR2,
1548 x_employer_role_cd IN VARCHAR2,
1549 x_disadv_uplift_factor IN NUMBER ,
1550 x_enh_fund_elig_cd IN VARCHAR2,
1551 x_exclude_flag IN VARCHAR2
1552 ) AS
1553 /*
1554 || Created By : [email protected]
1555 || Created On : 21-FEB-2002
1556 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1557 || Known limitations, enhancements or remarks :
1558 || Change History :
1559 || Who When What
1560 || (reverse chronological order - newest change first)
1561 */
1562 CURSOR c1 IS
1563 SELECT rowid
1564 FROM igs_he_st_spa_all
1565 WHERE hesa_st_spa_id = x_hesa_st_spa_id;
1566
1567 l_mode VARCHAR2(1);
1568
1569 BEGIN
1570
1571 l_mode := NVL(x_mode,'R');
1572
1573 OPEN c1;
1574 FETCH c1 INTO x_rowid;
1575 IF (c1%NOTFOUND) THEN
1576 CLOSE c1;
1577
1578 insert_row (
1579 x_rowid,
1580 x_hesa_st_spa_id,
1581 x_org_id,
1582 x_person_id,
1583 x_course_cd,
1584 x_version_number,
1585 x_fe_student_marker,
1586 x_domicile_cd,
1587 x_inst_last_attended,
1588 x_year_left_last_inst,
1589 x_highest_qual_on_entry,
1590 x_date_qual_on_entry_calc,
1591 x_a_level_point_score,
1592 x_highers_points_scores,
1593 x_occupation_code,
1594 x_commencement_dt,
1595 x_special_student,
1596 x_student_qual_aim,
1597 x_student_fe_qual_aim,
1598 x_teacher_train_prog_id,
1599 x_itt_phase,
1600 x_bilingual_itt_marker,
1601 x_teaching_qual_gain_sector,
1602 x_teaching_qual_gain_subj1,
1603 x_teaching_qual_gain_subj2,
1604 x_teaching_qual_gain_subj3,
1605 x_student_inst_number,
1606 x_destination,
1607 x_itt_prog_outcome,
1608 x_hesa_return_name,
1609 x_hesa_return_id,
1610 x_hesa_submission_name,
1611 x_associate_ucas_number,
1612 x_associate_scott_cand,
1613 x_associate_teach_ref_num,
1614 x_associate_nhs_reg_num,
1615 x_nhs_funding_source,
1616 x_ufi_place,
1617 x_postcode,
1618 x_social_class_ind,
1619 x_occcode,
1620 x_total_ucas_tariff,
1621 x_nhs_employer,
1622 x_return_type,
1623 x_qual_aim_subj1,
1624 x_qual_aim_subj2 ,
1625 x_qual_aim_subj3,
1626 x_qual_aim_proportion,
1627 l_mode,
1628 x_dependants_cd,
1629 x_implied_fund_rate,
1630 x_gov_initiatives_cd,
1631 x_units_for_qual,
1632 x_disadv_uplift_elig_cd,
1633 x_franch_partner_cd,
1634 x_units_completed,
1635 x_franch_out_arr_cd,
1636 x_employer_role_cd,
1637 x_disadv_uplift_factor,
1638 x_enh_fund_elig_cd,
1639 x_exclude_flag
1640 );
1641 RETURN;
1642 END IF;
1643 CLOSE c1;
1644
1645 update_row (
1646 x_rowid,
1647 x_hesa_st_spa_id,
1648 x_org_id,
1649 x_person_id,
1650 x_course_cd,
1651 x_version_number,
1652 x_fe_student_marker,
1653 x_domicile_cd,
1654 x_inst_last_attended,
1655 x_year_left_last_inst,
1656 x_highest_qual_on_entry,
1657 x_date_qual_on_entry_calc,
1658 x_a_level_point_score,
1659 x_highers_points_scores,
1660 x_occupation_code,
1661 x_commencement_dt,
1662 x_special_student,
1663 x_student_qual_aim,
1664 x_student_fe_qual_aim,
1665 x_teacher_train_prog_id,
1666 x_itt_phase,
1667 x_bilingual_itt_marker,
1668 x_teaching_qual_gain_sector,
1669 x_teaching_qual_gain_subj1,
1670 x_teaching_qual_gain_subj2,
1671 x_teaching_qual_gain_subj3,
1672 x_student_inst_number,
1673 x_destination,
1674 x_itt_prog_outcome,
1675 x_hesa_return_name,
1676 x_hesa_return_id,
1677 x_hesa_submission_name,
1678 x_associate_ucas_number,
1679 x_associate_scott_cand,
1680 x_associate_teach_ref_num,
1681 x_associate_nhs_reg_num,
1682 x_nhs_funding_source,
1683 x_ufi_place,
1684 x_postcode,
1685 x_social_class_ind,
1686 x_occcode,
1687 x_total_ucas_tariff,
1688 x_nhs_employer,
1689 x_return_type,
1690 x_qual_aim_subj1 ,
1691 x_qual_aim_subj2 ,
1692 x_qual_aim_subj3 ,
1693 x_qual_aim_proportion ,
1694 l_mode ,
1695 x_dependants_cd,
1696 x_implied_fund_rate,
1697 x_gov_initiatives_cd,
1698 x_units_for_qual,
1699 x_disadv_uplift_elig_cd,
1700 x_franch_partner_cd,
1701 x_units_completed,
1702 x_franch_out_arr_cd,
1703 x_employer_role_cd,
1704 x_disadv_uplift_factor,
1705 x_enh_fund_elig_cd,
1706 x_exclude_flag
1707 );
1708
1709 END add_row;
1710
1711
1712 PROCEDURE delete_row (
1713 x_rowid IN VARCHAR2,
1714 x_mode IN VARCHAR2
1715 ) AS
1716 /*
1717 || Created By : [email protected]
1718 || Created On : 21-FEB-2002
1719 || Purpose : Handles the DELETE DML logic for the table.
1720 || Known limitations, enhancements or remarks :
1721 || Change History :
1722 || Who When What
1723 || (reverse chronological order - newest change first)
1724 */
1725 BEGIN
1726
1727 before_dml (
1728 p_action => 'DELETE',
1729 x_rowid => x_rowid
1730 );
1731
1732 IF (x_mode = 'S') THEN
1733 igs_sc_gen_001.set_ctx('R');
1734 END IF;
1735 DELETE FROM igs_he_st_spa_all
1736 WHERE rowid = x_rowid;
1737
1738 IF (SQL%NOTFOUND) THEN
1739 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1740 igs_ge_msg_stack.add;
1741 igs_sc_gen_001.unset_ctx('R');
1742 app_exception.raise_exception;
1743 END IF;
1744 IF (x_mode = 'S') THEN
1745 igs_sc_gen_001.unset_ctx('R');
1746 END IF;
1747
1748
1749 END delete_row;
1750
1751
1752 END igs_he_st_spa_all_pkg;