[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_EN_SUSA_PKG
Source
1 PACKAGE BODY igs_he_en_susa_pkg AS
2 /* $Header: IGSWI21B.pls 120.2 2005/07/03 18:34:02 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_en_susa%ROWTYPE;
6 new_references igs_he_en_susa%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_hesa_en_susa_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_course_cd IN VARCHAR2 ,
14 x_unit_set_cd IN VARCHAR2 ,
15 x_us_version_number IN NUMBER ,
16 x_sequence_number IN NUMBER ,
17 x_new_he_entrant_cd IN VARCHAR2 ,
18 x_term_time_accom IN VARCHAR2 ,
19 x_disability_allow IN VARCHAR2 ,
20 x_additional_sup_band IN VARCHAR2 ,
21 x_sldd_discrete_prov IN VARCHAR2 ,
22 x_study_mode IN VARCHAR2 ,
23 x_study_location IN VARCHAR2 ,
24 x_fte_perc_override IN NUMBER ,
25 x_franchising_activity IN VARCHAR2 ,
26 x_completion_status IN VARCHAR2 ,
27 x_good_stand_marker IN VARCHAR2 ,
28 x_complete_pyr_study_cd IN VARCHAR2 ,
29 x_credit_value_yop1 IN NUMBER ,
30 x_credit_value_yop2 IN NUMBER ,
31 x_credit_value_yop3 IN NUMBER ,
32 x_credit_value_yop4 IN NUMBER ,
33 x_credit_level_achieved1 IN VARCHAR2 ,
34 x_credit_level_achieved2 IN VARCHAR2 ,
35 x_credit_level_achieved3 IN VARCHAR2 ,
36 x_credit_level_achieved4 IN VARCHAR2 ,
37 x_credit_pt_achieved1 IN NUMBER ,
38 x_credit_pt_achieved2 IN NUMBER ,
39 x_credit_pt_achieved3 IN NUMBER ,
40 x_credit_pt_achieved4 IN NUMBER ,
41 x_credit_level1 IN VARCHAR2 ,
42 x_credit_level2 IN VARCHAR2 ,
43 x_credit_level3 IN VARCHAR2 ,
44 x_credit_level4 IN VARCHAR2 ,
45 x_additional_sup_cost IN NUMBER ,
46 x_enh_fund_elig_cd IN VARCHAR2 ,
47 x_disadv_uplift_factor IN NUMBER ,
48 x_year_stu IN NUMBER ,
49 x_grad_sch_grade IN VARCHAR2 ,
50 x_mark IN NUMBER ,
51 x_teaching_inst1 IN VARCHAR2 ,
52 x_teaching_inst2 IN VARCHAR2 ,
53 x_pro_not_taught IN NUMBER ,
54 x_fundability_code IN VARCHAR2 ,
55 x_fee_eligibility IN VARCHAR2 ,
56 x_fee_band IN VARCHAR2 ,
57 x_non_payment_reason IN VARCHAR2 ,
58 x_student_fee IN VARCHAR2 ,
59 x_fte_intensity IN NUMBER ,
60 x_calculated_fte IN NUMBER ,
61 x_fte_calc_type IN VARCHAR2 ,
62 x_type_of_year IN VARCHAR2 ,
63 x_creation_date IN DATE ,
64 x_created_by IN NUMBER ,
65 x_last_update_date IN DATE ,
66 x_last_updated_by IN NUMBER ,
67 x_last_update_login IN NUMBER
68 ) AS
69 /*
70 || Created By : [email protected]
71 || Created On : 20-FEB-2002
72 || Purpose : Initialises the Old and New references for the columns of the table.
73 || Known limitations, enhancements or remarks :
74 || Change History :
75 || Who When What
76 || sbaliga 8-Apr-2002 Added 3 new parameters x_fte_intensity,x_calculated_fte
77 || and x_fte_calc_type as part of #2278825
78 || (reverse chronological order - newest change first)
79 */
80
81 CURSOR cur_old_ref_values IS
82 SELECT *
83 FROM IGS_HE_EN_SUSA
84 WHERE rowid = x_rowid;
85
86 BEGIN
87
88 l_rowid := x_rowid;
89
90 -- Code for setting the Old and New Reference Values.
91 -- Populate Old Values.
92 OPEN cur_old_ref_values;
93 FETCH cur_old_ref_values INTO old_references;
94 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
95 CLOSE cur_old_ref_values;
96 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
97 igs_ge_msg_stack.add;
98 app_exception.raise_exception;
99 RETURN;
100 END IF;
101 CLOSE cur_old_ref_values;
102
103 -- Populate New Values.
104 new_references.hesa_en_susa_id := x_hesa_en_susa_id;
105 new_references.person_id := x_person_id;
106 new_references.course_cd := x_course_cd;
107 new_references.unit_set_cd := x_unit_set_cd;
108 new_references.us_version_number := x_us_version_number;
109 new_references.sequence_number := x_sequence_number;
110 new_references.new_he_entrant_cd := x_new_he_entrant_cd;
111 new_references.term_time_accom := x_term_time_accom;
112 new_references.disability_allow := x_disability_allow;
113 new_references.additional_sup_band := x_additional_sup_band;
114 new_references.sldd_discrete_prov := x_sldd_discrete_prov;
115 new_references.study_mode := x_study_mode;
116 new_references.study_location := x_study_location;
117 new_references.fte_perc_override := x_fte_perc_override;
118 new_references.franchising_activity := x_franchising_activity;
119 new_references.completion_status := x_completion_status;
120 new_references.good_stand_marker := x_good_stand_marker;
121 new_references.complete_pyr_study_cd := x_complete_pyr_study_cd;
122 new_references.credit_value_yop1 := x_credit_value_yop1;
123 new_references.credit_value_yop2 := x_credit_value_yop2;
124 new_references.credit_value_yop3 := x_credit_value_yop3;
125 new_references.credit_value_yop4 := x_credit_value_yop4;
126 new_references.credit_level_achieved1 := x_credit_level_achieved1;
127 new_references.credit_level_achieved2 := x_credit_level_achieved2;
128 new_references.credit_level_achieved3 := x_credit_level_achieved3;
129 new_references.credit_level_achieved4 := x_credit_level_achieved4;
130 new_references.credit_pt_achieved1 := x_credit_pt_achieved1;
131 new_references.credit_pt_achieved2 := x_credit_pt_achieved2;
132 new_references.credit_pt_achieved3 := x_credit_pt_achieved3;
133 new_references.credit_pt_achieved4 := x_credit_pt_achieved4;
134 new_references.credit_level1 := x_credit_level1;
135 new_references.credit_level2 := x_credit_level2;
136 new_references.credit_level3 := x_credit_level3;
137 new_references.credit_level4 := x_credit_level4;
138 new_references.additional_sup_cost := x_additional_sup_cost;
139 new_references.enh_fund_elig_cd := x_enh_fund_elig_cd;
140 new_references.disadv_uplift_factor := x_disadv_uplift_factor;
141 new_references.year_stu := x_year_stu;
142 new_references.grad_sch_grade := x_grad_sch_grade;
143 new_references.mark := x_mark;
144 new_references.teaching_inst1 := x_teaching_inst1;
145 new_references.teaching_inst2 := x_teaching_inst2;
146 new_references.pro_not_taught := x_pro_not_taught;
147 new_references.fundability_code := x_fundability_code;
148 new_references.fee_eligibility := x_fee_eligibility;
149 new_references.fee_band := x_fee_band;
150 new_references.non_payment_reason := x_non_payment_reason;
151 new_references.student_fee := x_student_fee;
152 new_references.fte_intensity := x_fte_intensity;
153 new_references.calculated_fte := x_calculated_fte;
154 new_references.fte_calc_type := x_fte_calc_type;
155 new_references.type_of_year := x_type_of_year;
156
157
158 IF (p_action = 'UPDATE') THEN
159 new_references.creation_date := old_references.creation_date;
160 new_references.created_by := old_references.created_by;
161 ELSE
162 new_references.creation_date := x_creation_date;
163 new_references.created_by := x_created_by;
164 END IF;
165
166 new_references.last_update_date := x_last_update_date;
167 new_references.last_updated_by := x_last_updated_by;
168 new_references.last_update_login := x_last_update_login;
169
170 END set_column_values;
171
172
173 PROCEDURE check_uniqueness AS
174 /*
175 || Created By : [email protected]
176 || Created On : 20-FEB-2002
177 || Purpose : Handles the Unique Constraint logic defined for the columns.
178 || Known limitations, enhancements or remarks :
179 || Change History :
180 || Who When What
181 || (reverse chronological order - newest change first)
182 */
183 BEGIN
184
185 IF ( get_uk_for_validation (
186 new_references.person_id,
187 new_references.course_cd,
188 new_references.unit_set_cd,
189 new_references.sequence_number
190 )
191 ) THEN
192 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
193 igs_ge_msg_stack.add;
194 app_exception.raise_exception;
195 END IF;
196
197 END check_uniqueness;
198
199
200 PROCEDURE check_parent_existance AS
201 /*
202 || Created By : [email protected]
203 || Created On : 20-FEB-2002
204 || Purpose : Checks for the existance of Parent records.
205 || Known limitations, enhancements or remarks :
206 || Change History :
207 || Who When What
208 || (reverse chronological order - newest change first)
209 */
210 BEGIN
211
212 IF (((old_references.person_id = new_references.person_id) AND
213 (old_references.course_cd = new_references.course_cd) AND
214 (old_references.unit_set_cd = new_references.unit_set_cd) AND
215 (old_references.sequence_number = new_references.sequence_number)) OR
216 ((new_references.person_id IS NULL) OR
217 (new_references.course_cd IS NULL) OR
218 (new_references.unit_set_cd IS NULL) OR
219 (new_references.sequence_number IS NULL))) THEN
220 NULL;
221 ELSIF NOT igs_as_su_setatmpt_pkg.get_pk_for_validation (
222 new_references.person_id,
223 new_references.course_cd,
224 new_references.unit_set_cd,
225 new_references.sequence_number
226 ) THEN
227 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
228 igs_ge_msg_stack.add;
229 app_exception.raise_exception;
230 END IF;
231
232 END check_parent_existance;
233
234
235 FUNCTION get_pk_for_validation (
236 x_hesa_en_susa_id IN NUMBER
237 ) RETURN BOOLEAN AS
238 /*
239 || Created By : [email protected]
240 || Created On : 20-FEB-2002
241 || Purpose : Validates the Primary Key of the table.
242 || Known limitations, enhancements or remarks :
243 || Change History :
244 || Who When What
245 || (reverse chronological order - newest change first)
246 */
247 CURSOR cur_rowid IS
248 SELECT rowid
249 FROM igs_he_en_susa
250 WHERE hesa_en_susa_id = x_hesa_en_susa_id
251 FOR UPDATE NOWAIT;
252
253 lv_rowid cur_rowid%RowType;
254
255 BEGIN
256
257 OPEN cur_rowid;
258 FETCH cur_rowid INTO lv_rowid;
259 IF (cur_rowid%FOUND) THEN
260 CLOSE cur_rowid;
261 RETURN(TRUE);
262 ELSE
263 CLOSE cur_rowid;
264 RETURN(FALSE);
265 END IF;
266
267 END get_pk_for_validation;
268
269
270 FUNCTION get_uk_for_validation (
271 x_person_id IN NUMBER,
272 x_course_cd IN VARCHAR2,
273 x_unit_set_cd IN VARCHAR2,
274 x_sequence_number IN NUMBER
275 ) RETURN BOOLEAN AS
276 /*
277 || Created By : [email protected]
278 || Created On : 20-FEB-2002
279 || Purpose : Validates the Unique Keys of the table.
280 || Known limitations, enhancements or remarks :
281 || Change History :
282 || Who When What
283 || (reverse chronological order - newest change first)
284 */
285 CURSOR cur_rowid IS
286 SELECT rowid
287 FROM igs_he_en_susa
288 WHERE person_id = x_person_id
289 AND course_cd = x_course_cd
290 AND unit_set_cd = x_unit_set_cd
291 AND sequence_number = x_sequence_number
292 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
293
294 lv_rowid cur_rowid%RowType;
295
296 BEGIN
297
298 OPEN cur_rowid;
299 FETCH cur_rowid INTO lv_rowid;
300 IF (cur_rowid%FOUND) THEN
301 CLOSE cur_rowid;
302 RETURN (true);
303 ELSE
304 CLOSE cur_rowid;
305 RETURN(FALSE);
306 END IF;
307
308 END get_uk_for_validation ;
309
310
311 PROCEDURE get_fk_igs_as_su_setatmpt (
312 x_person_id IN NUMBER,
313 x_course_cd IN VARCHAR2,
314 x_unit_set_cd IN VARCHAR2,
315 x_sequence_number IN NUMBER
316 ) AS
317 /*
318 || Created By : [email protected]
319 || Created On : 20-FEB-2002
320 || Purpose : Validates the Foreign Keys for the table.
321 || Known limitations, enhancements or remarks :
322 || Change History :
323 || Who When What
324 || (reverse chronological order - newest change first)
325 */
326 CURSOR cur_rowid IS
327 SELECT rowid
328 FROM igs_he_en_susa
329 WHERE ((course_cd = x_course_cd) AND
330 (person_id = x_person_id) AND
331 (sequence_number = x_sequence_number) AND
332 (unit_set_cd = x_unit_set_cd));
333
334 lv_rowid cur_rowid%RowType;
335
336 BEGIN
337
338 OPEN cur_rowid;
339 FETCH cur_rowid INTO lv_rowid;
340 IF (cur_rowid%FOUND) THEN
341 CLOSE cur_rowid;
342 fnd_message.set_name ('IGS', 'HES_ASS_FKIGS_AS_SU_SETATMPT');
343 igs_ge_msg_stack.add;
344 app_exception.raise_exception;
345 RETURN;
346 END IF;
347 CLOSE cur_rowid;
348
349 END get_fk_igs_as_su_setatmpt;
350
351
352 PROCEDURE before_dml (
353 p_action IN VARCHAR2,
354 x_rowid IN VARCHAR2 ,
355 x_hesa_en_susa_id IN NUMBER ,
356 x_person_id IN NUMBER ,
357 x_course_cd IN VARCHAR2 ,
358 x_unit_set_cd IN VARCHAR2 ,
359 x_us_version_number IN NUMBER ,
360 x_sequence_number IN NUMBER ,
361 x_new_he_entrant_cd IN VARCHAR2 ,
362 x_term_time_accom IN VARCHAR2 ,
363 x_disability_allow IN VARCHAR2 ,
364 x_additional_sup_band IN VARCHAR2 ,
365 x_sldd_discrete_prov IN VARCHAR2 ,
366 x_study_mode IN VARCHAR2 ,
367 x_study_location IN VARCHAR2 ,
368 x_fte_perc_override IN NUMBER ,
369 x_franchising_activity IN VARCHAR2 ,
370 x_completion_status IN VARCHAR2 ,
371 x_good_stand_marker IN VARCHAR2 ,
372 x_complete_pyr_study_cd IN VARCHAR2 ,
373 x_credit_value_yop1 IN NUMBER ,
374 x_credit_value_yop2 IN NUMBER ,
375 x_credit_value_yop3 IN NUMBER ,
376 x_credit_value_yop4 IN NUMBER ,
377 x_credit_level_achieved1 IN VARCHAR2 ,
378 x_credit_level_achieved2 IN VARCHAR2 ,
379 x_credit_level_achieved3 IN VARCHAR2 ,
380 x_credit_level_achieved4 IN VARCHAR2 ,
381 x_credit_pt_achieved1 IN NUMBER ,
382 x_credit_pt_achieved2 IN NUMBER ,
383 x_credit_pt_achieved3 IN NUMBER ,
384 x_credit_pt_achieved4 IN NUMBER ,
385 x_credit_level1 IN VARCHAR2 ,
386 x_credit_level2 IN VARCHAR2 ,
387 x_credit_level3 IN VARCHAR2 ,
388 x_credit_level4 IN VARCHAR2 ,
389 x_additional_sup_cost IN NUMBER ,
390 x_enh_fund_elig_cd IN VARCHAR2 ,
391 x_disadv_uplift_factor IN NUMBER ,
392 x_year_stu IN NUMBER ,
393 x_grad_sch_grade IN VARCHAR2 ,
394 x_mark IN NUMBER ,
395 x_teaching_inst1 IN VARCHAR2 ,
396 x_teaching_inst2 IN VARCHAR2 ,
397 x_pro_not_taught IN NUMBER ,
398 x_fundability_code IN VARCHAR2 ,
399 x_fee_eligibility IN VARCHAR2 ,
400 x_fee_band IN VARCHAR2 ,
401 x_non_payment_reason IN VARCHAR2 ,
402 x_student_fee IN VARCHAR2 ,
403 x_fte_intensity IN NUMBER ,
404 x_calculated_fte IN NUMBER ,
405 x_fte_calc_type IN VARCHAR2 ,
406 x_type_of_year IN VARCHAR2 ,
407 x_creation_date IN DATE ,
408 x_created_by IN NUMBER ,
409 x_last_update_date IN DATE ,
410 x_last_updated_by IN NUMBER ,
411 x_last_update_login IN NUMBER
412 ) AS
413 /*
414 || Created By : [email protected]
415 || Created On : 20-FEB-2002
416 || Purpose : Initialises the columns, Checks Constraints, Calls the
417 || Trigger Handlers for the table, before any DML operation.
418 || Known limitations, enhancements or remarks :
419 || Change History :
420 || Who When What
421 || sbaliga 8-Apr-2002 Added 3 new parameters x_fte_intensity,x_calculated_fte
422 || and x_fte_calc_type as part of #2278825
423 || (reverse chronological order - newest change first)
424 */
425 BEGIN
426
427 set_column_values (
428 p_action,
429 x_rowid,
430 x_hesa_en_susa_id,
431 x_person_id,
432 x_course_cd,
433 x_unit_set_cd,
434 x_us_version_number,
435 x_sequence_number,
436 x_new_he_entrant_cd,
437 x_term_time_accom,
438 x_disability_allow,
439 x_additional_sup_band,
440 x_sldd_discrete_prov,
441 x_study_mode,
442 x_study_location,
443 x_fte_perc_override,
444 x_franchising_activity,
445 x_completion_status,
446 x_good_stand_marker,
447 x_complete_pyr_study_cd,
448 x_credit_value_yop1,
449 x_credit_value_yop2,
450 x_credit_value_yop3,
451 x_credit_value_yop4,
452 x_credit_level_achieved1,
453 x_credit_level_achieved2,
454 x_credit_level_achieved3,
455 x_credit_level_achieved4,
456 x_credit_pt_achieved1,
457 x_credit_pt_achieved2,
458 x_credit_pt_achieved3,
459 x_credit_pt_achieved4,
460 x_credit_level1,
461 x_credit_level2,
462 x_credit_level3,
463 x_credit_level4,
464 x_additional_sup_cost,
465 x_enh_fund_elig_cd,
466 x_disadv_uplift_factor,
467 x_year_stu,
468 x_grad_sch_grade,
469 x_mark,
470 x_teaching_inst1,
471 x_teaching_inst2,
472 x_pro_not_taught,
473 x_fundability_code,
474 x_fee_eligibility,
475 x_fee_band,
476 x_non_payment_reason,
477 x_student_fee,
478 x_fte_intensity,
479 x_calculated_fte,
480 x_fte_calc_type,
481 x_type_of_year,
482 x_creation_date,
483 x_created_by,
484 x_last_update_date,
485 x_last_updated_by,
486 x_last_update_login
487 );
488
489 IF (p_action = 'INSERT') THEN
490 -- Call all the procedures related to Before Insert.
491 IF ( get_pk_for_validation(
495 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
492 new_references.hesa_en_susa_id
493 )
494 ) THEN
496 igs_ge_msg_stack.add;
497 app_exception.raise_exception;
498 END IF;
499 check_uniqueness;
500 check_parent_existance;
501 ELSIF (p_action = 'UPDATE') THEN
502 -- Call all the procedures related to Before Update.
503 check_uniqueness;
504 check_parent_existance;
505 ELSIF (p_action = 'VALIDATE_INSERT') THEN
506 -- Call all the procedures related to Before Insert.
507 IF ( get_pk_for_validation (
508 new_references.hesa_en_susa_id
509 )
510 ) THEN
511 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
512 igs_ge_msg_stack.add;
513 app_exception.raise_exception;
514 END IF;
515 check_uniqueness;
516 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
517 check_uniqueness;
518 END IF;
519
520 END before_dml;
521
522
523 PROCEDURE insert_row (
524 x_rowid IN OUT NOCOPY VARCHAR2,
525 x_hesa_en_susa_id IN OUT NOCOPY NUMBER,
526 x_person_id IN NUMBER,
527 x_course_cd IN VARCHAR2,
528 x_unit_set_cd IN VARCHAR2,
529 x_us_version_number IN NUMBER,
530 x_sequence_number IN NUMBER,
531 x_new_he_entrant_cd IN VARCHAR2,
532 x_term_time_accom IN VARCHAR2,
533 x_disability_allow IN VARCHAR2,
534 x_additional_sup_band IN VARCHAR2,
535 x_sldd_discrete_prov IN VARCHAR2,
536 x_study_mode IN VARCHAR2,
537 x_study_location IN VARCHAR2,
538 x_fte_perc_override IN NUMBER,
539 x_franchising_activity IN VARCHAR2,
540 x_completion_status IN VARCHAR2,
541 x_good_stand_marker IN VARCHAR2,
542 x_complete_pyr_study_cd IN VARCHAR2,
543 x_credit_value_yop1 IN NUMBER,
544 x_credit_value_yop2 IN NUMBER,
545 x_credit_value_yop3 IN NUMBER,
546 x_credit_value_yop4 IN NUMBER,
547 x_credit_level_achieved1 IN VARCHAR2,
548 x_credit_level_achieved2 IN VARCHAR2,
549 x_credit_level_achieved3 IN VARCHAR2,
550 x_credit_level_achieved4 IN VARCHAR2,
551 x_credit_pt_achieved1 IN NUMBER,
552 x_credit_pt_achieved2 IN NUMBER,
553 x_credit_pt_achieved3 IN NUMBER,
554 x_credit_pt_achieved4 IN NUMBER,
555 x_credit_level1 IN VARCHAR2,
556 x_credit_level2 IN VARCHAR2,
557 x_credit_level3 IN VARCHAR2,
558 x_credit_level4 IN VARCHAR2,
559 x_additional_sup_cost IN NUMBER,
560 x_enh_fund_elig_cd IN VARCHAR2,
561 x_disadv_uplift_factor IN NUMBER,
562 x_year_stu IN NUMBER,
563 x_grad_sch_grade IN VARCHAR2,
564 x_mark IN NUMBER,
565 x_teaching_inst1 IN VARCHAR2,
566 x_teaching_inst2 IN VARCHAR2,
567 x_pro_not_taught IN NUMBER,
568 x_fundability_code IN VARCHAR2,
569 x_fee_eligibility IN VARCHAR2,
570 x_fee_band IN VARCHAR2,
571 x_non_payment_reason IN VARCHAR2,
572 x_student_fee IN VARCHAR2,
573 x_fte_intensity IN NUMBER ,
574 x_calculated_fte IN NUMBER ,
575 x_fte_calc_type IN VARCHAR2 ,
576 x_type_of_year IN VARCHAR2 ,
577 x_mode IN VARCHAR2
578 ) AS
579 /*
580 || Created By : [email protected]
581 || Created On : 20-FEB-2002
582 || Purpose : Handles the INSERT DML logic for the table.
583 || Known limitations, enhancements or remarks :
584 || Change History :
585 || Who When What
586 || sbaliga 8-Apr-2002 Added 3 new parameters x_fte_intensity,x_calculated_fte
587 || and x_fte_calc_type as part of #2278825
588 || (reverse chronological order - newest change first)
589 */
590 CURSOR c IS
591 SELECT rowid
592 FROM igs_he_en_susa
593 WHERE hesa_en_susa_id = x_hesa_en_susa_id;
594
595 x_last_update_date DATE;
596 x_last_updated_by NUMBER;
597 x_last_update_login NUMBER;
598
599 BEGIN
600
601 x_last_update_date := SYSDATE;
602 IF (x_mode = 'I') THEN
603 x_last_updated_by := 1;
604 x_last_update_login := 0;
605 ELSIF (X_MODE IN ('R', 'S')) THEN
606 x_last_updated_by := fnd_global.user_id;
607 IF (x_last_updated_by IS NULL) THEN
608 x_last_updated_by := -1;
609 END IF;
610 x_last_update_login := fnd_global.login_id;
611 IF (x_last_update_login IS NULL) THEN
612 x_last_update_login := -1;
613 END IF;
617 app_exception.raise_exception;
614 ELSE
615 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
616 igs_ge_msg_stack.add;
618 END IF;
619
620 SELECT igs_he_en_susa_s.NEXTVAL
621 INTO x_hesa_en_susa_id
622 FROM dual;
623
624 before_dml(
625 p_action => 'INSERT',
626 x_rowid => x_rowid,
627 x_hesa_en_susa_id => x_hesa_en_susa_id,
628 x_person_id => x_person_id,
629 x_course_cd => x_course_cd,
630 x_unit_set_cd => x_unit_set_cd,
631 x_us_version_number => x_us_version_number,
632 x_sequence_number => x_sequence_number,
633 x_new_he_entrant_cd => x_new_he_entrant_cd,
634 x_term_time_accom => x_term_time_accom,
635 x_disability_allow => x_disability_allow,
636 x_additional_sup_band => x_additional_sup_band,
637 x_sldd_discrete_prov => x_sldd_discrete_prov,
638 x_study_mode => x_study_mode,
639 x_study_location => x_study_location,
640 x_fte_perc_override => x_fte_perc_override,
641 x_franchising_activity => x_franchising_activity,
642 x_completion_status => x_completion_status,
643 x_good_stand_marker => x_good_stand_marker,
644 x_complete_pyr_study_cd => x_complete_pyr_study_cd,
645 x_credit_value_yop1 => x_credit_value_yop1,
646 x_credit_value_yop2 => x_credit_value_yop2,
647 x_credit_value_yop3 => x_credit_value_yop3,
648 x_credit_value_yop4 => x_credit_value_yop4,
649 x_credit_level_achieved1 => x_credit_level_achieved1,
650 x_credit_level_achieved2 => x_credit_level_achieved2,
651 x_credit_level_achieved3 => x_credit_level_achieved3,
652 x_credit_level_achieved4 => x_credit_level_achieved4,
653 x_credit_pt_achieved1 => x_credit_pt_achieved1,
654 x_credit_pt_achieved2 => x_credit_pt_achieved2,
655 x_credit_pt_achieved3 => x_credit_pt_achieved3,
656 x_credit_pt_achieved4 => x_credit_pt_achieved4,
657 x_credit_level1 => x_credit_level1,
658 x_credit_level2 => x_credit_level2,
659 x_credit_level3 => x_credit_level3,
660 x_credit_level4 => x_credit_level4,
661 x_additional_sup_cost => x_additional_sup_cost,
662 x_enh_fund_elig_cd => x_enh_fund_elig_cd,
663 x_disadv_uplift_factor => x_disadv_uplift_factor,
664 x_year_stu => x_year_stu,
665 x_grad_sch_grade => x_grad_sch_grade,
666 x_mark => x_mark,
667 x_teaching_inst1 => x_teaching_inst1,
668 x_teaching_inst2 => x_teaching_inst2,
669 x_pro_not_taught => x_pro_not_taught,
670 x_fundability_code => x_fundability_code,
671 x_fee_eligibility => x_fee_eligibility,
672 x_fee_band => x_fee_band,
673 x_non_payment_reason => x_non_payment_reason,
674 x_student_fee => x_student_fee,
675 x_fte_intensity => x_fte_intensity,
676 x_calculated_fte => x_calculated_fte,
677 x_fte_calc_type => x_fte_calc_type,
678 x_type_of_year => x_type_of_year,
679 x_creation_date => x_last_update_date,
680 x_created_by => x_last_updated_by,
681 x_last_update_date => x_last_update_date,
682 x_last_updated_by => x_last_updated_by,
683 x_last_update_login => x_last_update_login
684 );
685
686 IF (x_mode = 'S') THEN
687 igs_sc_gen_001.set_ctx('R');
688 END IF;
689 INSERT INTO igs_he_en_susa (
690 hesa_en_susa_id,
691 person_id,
692 course_cd,
693 unit_set_cd,
694 us_version_number,
695 sequence_number,
696 new_he_entrant_cd,
697 term_time_accom,
698 disability_allow,
699 additional_sup_band,
700 sldd_discrete_prov,
701 study_mode,
702 study_location,
703 fte_perc_override,
704 franchising_activity,
705 completion_status,
706 good_stand_marker,
707 complete_pyr_study_cd,
708 credit_value_yop1,
709 credit_value_yop2,
710 credit_value_yop3,
711 credit_value_yop4,
712 credit_level_achieved1,
713 credit_level_achieved2,
714 credit_level_achieved3,
715 credit_level_achieved4,
716 credit_pt_achieved1,
717 credit_pt_achieved2,
718 credit_pt_achieved3,
719 credit_pt_achieved4,
720 credit_level1,
721 credit_level2,
722 credit_level3,
723 credit_level4,
724 additional_sup_cost,
725 enh_fund_elig_cd,
726 disadv_uplift_factor,
727 year_stu,
728 grad_sch_grade,
729 mark,
730 teaching_inst1,
734 fee_eligibility,
731 teaching_inst2,
732 pro_not_taught,
733 fundability_code,
735 fee_band,
736 non_payment_reason,
737 student_fee,
738 fte_intensity,
739 calculated_fte,
740 fte_calc_type,
741 type_of_year,
742 creation_date,
743 created_by,
744 last_update_date,
745 last_updated_by,
746 last_update_login
747 ) VALUES (
748 new_references.hesa_en_susa_id,
749 new_references.person_id,
750 new_references.course_cd,
751 new_references.unit_set_cd,
752 new_references.us_version_number,
753 new_references.sequence_number,
754 new_references.new_he_entrant_cd,
755 new_references.term_time_accom,
756 new_references.disability_allow,
757 new_references.additional_sup_band,
758 new_references.sldd_discrete_prov,
759 new_references.study_mode,
760 new_references.study_location,
761 new_references.fte_perc_override,
762 new_references.franchising_activity,
763 new_references.completion_status,
764 new_references.good_stand_marker,
765 new_references.complete_pyr_study_cd,
766 new_references.credit_value_yop1,
767 new_references.credit_value_yop2,
768 new_references.credit_value_yop3,
769 new_references.credit_value_yop4,
770 new_references.credit_level_achieved1,
771 new_references.credit_level_achieved2,
772 new_references.credit_level_achieved3,
773 new_references.credit_level_achieved4,
774 new_references.credit_pt_achieved1,
775 new_references.credit_pt_achieved2,
776 new_references.credit_pt_achieved3,
777 new_references.credit_pt_achieved4,
778 new_references.credit_level1,
779 new_references.credit_level2,
780 new_references.credit_level3,
781 new_references.credit_level4,
782 new_references.additional_sup_cost,
783 new_references.enh_fund_elig_cd,
784 new_references.disadv_uplift_factor,
785 new_references.year_stu,
786 new_references.grad_sch_grade,
787 new_references.mark,
788 new_references.teaching_inst1,
789 new_references.teaching_inst2,
790 new_references.pro_not_taught,
791 new_references.fundability_code,
792 new_references.fee_eligibility,
793 new_references.fee_band,
794 new_references.non_payment_reason,
795 new_references.student_fee,
796 new_references.fte_intensity,
797 new_references.calculated_fte,
798 new_references.fte_calc_type,
799 new_references.type_of_year,
800 x_last_update_date,
801 x_last_updated_by,
802 x_last_update_date,
803 x_last_updated_by,
804 x_last_update_login
805 );
806 IF (x_mode = 'S') THEN
807 igs_sc_gen_001.unset_ctx('R');
808 END IF;
809
810
811 OPEN c;
812 FETCH c INTO x_rowid;
813 IF (c%NOTFOUND) THEN
814 CLOSE c;
815 RAISE NO_DATA_FOUND;
816 END IF;
817 CLOSE c;
818
819
820 EXCEPTION
821 WHEN OTHERS THEN
822 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
823 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
824 fnd_message.set_token ('ERR_CD', SQLCODE);
825 igs_ge_msg_stack.add;
826 igs_sc_gen_001.unset_ctx('R');
827 app_exception.raise_exception;
828 ELSE
829 igs_sc_gen_001.unset_ctx('R');
830 RAISE;
831 END IF;
832 END insert_row;
833
834
835 PROCEDURE lock_row (
836 x_rowid IN VARCHAR2,
837 x_hesa_en_susa_id IN NUMBER,
838 x_person_id IN NUMBER,
839 x_course_cd IN VARCHAR2,
840 x_unit_set_cd IN VARCHAR2,
841 x_us_version_number IN NUMBER,
842 x_sequence_number IN NUMBER,
843 x_new_he_entrant_cd IN VARCHAR2,
844 x_term_time_accom IN VARCHAR2,
845 x_disability_allow IN VARCHAR2,
846 x_additional_sup_band IN VARCHAR2,
847 x_sldd_discrete_prov IN VARCHAR2,
848 x_study_mode IN VARCHAR2,
849 x_study_location IN VARCHAR2,
850 x_fte_perc_override IN NUMBER ,
851 x_franchising_activity IN VARCHAR2,
852 x_completion_status IN VARCHAR2,
853 x_good_stand_marker IN VARCHAR2,
854 x_complete_pyr_study_cd IN VARCHAR2,
855 x_credit_value_yop1 IN NUMBER,
856 x_credit_value_yop2 IN NUMBER,
857 x_credit_value_yop3 IN NUMBER,
858 x_credit_value_yop4 IN NUMBER,
859 x_credit_level_achieved1 IN VARCHAR2,
860 x_credit_level_achieved2 IN VARCHAR2,
861 x_credit_level_achieved3 IN VARCHAR2,
862 x_credit_level_achieved4 IN VARCHAR2,
863 x_credit_pt_achieved1 IN NUMBER,
864 x_credit_pt_achieved2 IN NUMBER,
865 x_credit_pt_achieved3 IN NUMBER,
866 x_credit_pt_achieved4 IN NUMBER,
867 x_credit_level1 IN VARCHAR2,
871 x_additional_sup_cost IN NUMBER,
868 x_credit_level2 IN VARCHAR2,
869 x_credit_level3 IN VARCHAR2,
870 x_credit_level4 IN VARCHAR2,
872 x_enh_fund_elig_cd IN VARCHAR2,
873 x_disadv_uplift_factor IN NUMBER,
874 x_year_stu IN NUMBER,
875 x_grad_sch_grade IN VARCHAR2,
876 x_mark IN NUMBER,
877 x_teaching_inst1 IN VARCHAR2,
878 x_teaching_inst2 IN VARCHAR2,
879 x_pro_not_taught IN NUMBER,
880 x_fundability_code IN VARCHAR2,
881 x_fee_eligibility IN VARCHAR2,
882 x_fee_band IN VARCHAR2,
883 x_non_payment_reason IN VARCHAR2,
884 x_student_fee IN VARCHAR2,
885 x_fte_intensity IN NUMBER ,
886 x_calculated_fte IN NUMBER ,
887 x_fte_calc_type IN VARCHAR2 ,
888 x_type_of_year IN VARCHAR2
889
890 ) AS
891 /*
892 || Created By : [email protected]
893 || Created On : 20-FEB-2002
894 || Purpose : Handles the LOCK mechanism for the table.
895 || Known limitations, enhancements or remarks :
896 || Change History :
897 || Who When What
898 || sbaliga 8-Apr-2002 Added 3 new parameters x_fte_intensity,x_calculated_fte
899 || and x_fte_calc_type as part of #2278825
900 || (reverse chronological order - newest change first)
901 */
902 CURSOR c1 IS
903 SELECT
904 person_id,
905 course_cd,
906 unit_set_cd,
907 us_version_number,
908 sequence_number,
909 new_he_entrant_cd,
910 term_time_accom,
911 disability_allow,
912 additional_sup_band,
913 sldd_discrete_prov,
914 study_mode,
915 study_location,
916 fte_perc_override,
917 franchising_activity,
918 completion_status,
919 good_stand_marker,
920 complete_pyr_study_cd,
921 credit_value_yop1,
922 credit_value_yop2,
923 credit_value_yop3,
924 credit_value_yop4,
925 credit_level_achieved1,
926 credit_level_achieved2,
927 credit_level_achieved3,
928 credit_level_achieved4,
929 credit_pt_achieved1,
930 credit_pt_achieved2,
931 credit_pt_achieved3,
932 credit_pt_achieved4,
933 credit_level1,
934 credit_level2,
935 credit_level3,
936 credit_level4,
937 additional_sup_cost,
938 enh_fund_elig_cd,
939 disadv_uplift_factor,
940 year_stu,
941 grad_sch_grade,
942 mark,
943 teaching_inst1,
944 teaching_inst2,
945 pro_not_taught,
946 fundability_code,
947 fee_eligibility,
948 fee_band,
949 non_payment_reason,
950 student_fee,
951 fte_intensity,
952 calculated_fte,
953 fte_calc_type,
954 type_of_year
955 FROM igs_he_en_susa
956 WHERE rowid = x_rowid
957 FOR UPDATE NOWAIT;
958
959 tlinfo c1%ROWTYPE;
960
961 BEGIN
962
963 OPEN c1;
964 FETCH c1 INTO tlinfo;
965 IF (c1%notfound) THEN
966 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
967 igs_ge_msg_stack.add;
968 CLOSE c1;
969 app_exception.raise_exception;
970 RETURN;
971 END IF;
972 CLOSE c1;
973
974 IF (
975 (tlinfo.person_id = x_person_id)
976 AND (tlinfo.course_cd = x_course_cd)
977 AND (tlinfo.unit_set_cd = x_unit_set_cd)
978 AND (tlinfo.us_version_number = x_us_version_number)
979 AND (tlinfo.sequence_number = x_sequence_number)
980 AND ((tlinfo.new_he_entrant_cd = x_new_he_entrant_cd) OR ((tlinfo.new_he_entrant_cd IS NULL) AND (X_new_he_entrant_cd IS NULL)))
981 AND ((tlinfo.term_time_accom = x_term_time_accom) OR ((tlinfo.term_time_accom IS NULL) AND (X_term_time_accom IS NULL)))
982 AND ((tlinfo.disability_allow = x_disability_allow) OR ((tlinfo.disability_allow IS NULL) AND (X_disability_allow IS NULL)))
983 AND ((tlinfo.additional_sup_band = x_additional_sup_band) OR ((tlinfo.additional_sup_band IS NULL) AND (X_additional_sup_band IS NULL)))
984 AND ((tlinfo.sldd_discrete_prov = x_sldd_discrete_prov) OR ((tlinfo.sldd_discrete_prov IS NULL) AND (X_sldd_discrete_prov IS NULL)))
985 AND ((tlinfo.study_mode = x_study_mode) OR ((tlinfo.study_mode IS NULL) AND (X_study_mode IS NULL)))
986 AND ((tlinfo.study_location = x_study_location) OR ((tlinfo.study_location IS NULL) AND (X_study_location IS NULL)))
987 AND ((tlinfo.fte_perc_override = x_fte_perc_override) OR ((tlinfo.fte_perc_override IS NULL) AND (X_fte_perc_override IS NULL)))
988 AND ((tlinfo.franchising_activity = x_franchising_activity) OR ((tlinfo.franchising_activity IS NULL) AND (X_franchising_activity IS NULL)))
989 AND ((tlinfo.completion_status = x_completion_status) OR ((tlinfo.completion_status IS NULL) AND (X_completion_status IS NULL)))
990 AND ((tlinfo.good_stand_marker = x_good_stand_marker) OR ((tlinfo.good_stand_marker IS NULL) AND (X_good_stand_marker IS NULL)))
994 AND ((tlinfo.credit_value_yop3 = x_credit_value_yop3) OR ((tlinfo.credit_value_yop3 IS NULL) AND (X_credit_value_yop3 IS NULL)))
991 AND ((tlinfo.complete_pyr_study_cd = x_complete_pyr_study_cd) OR ((tlinfo.complete_pyr_study_cd IS NULL) AND (X_complete_pyr_study_cd IS NULL)))
992 AND ((tlinfo.credit_value_yop1 = x_credit_value_yop1) OR ((tlinfo.credit_value_yop1 IS NULL) AND (X_credit_value_yop1 IS NULL)))
993 AND ((tlinfo.credit_value_yop2 = x_credit_value_yop2) OR ((tlinfo.credit_value_yop2 IS NULL) AND (X_credit_value_yop2 IS NULL)))
995 AND ((tlinfo.credit_value_yop4 = x_credit_value_yop4) OR ((tlinfo.credit_value_yop4 IS NULL) AND (X_credit_value_yop4 IS NULL)))
996 AND ((tlinfo.credit_level_achieved1 = x_credit_level_achieved1) OR ((tlinfo.credit_level_achieved1 IS NULL) AND (X_credit_level_achieved1 IS NULL)))
997 AND ((tlinfo.credit_level_achieved2 = x_credit_level_achieved2) OR ((tlinfo.credit_level_achieved2 IS NULL) AND (X_credit_level_achieved2 IS NULL)))
998 AND ((tlinfo.credit_level_achieved3 = x_credit_level_achieved3) OR ((tlinfo.credit_level_achieved3 IS NULL) AND (X_credit_level_achieved3 IS NULL)))
999 AND ((tlinfo.credit_level_achieved4 = x_credit_level_achieved4) OR ((tlinfo.credit_level_achieved4 IS NULL) AND (X_credit_level_achieved4 IS NULL)))
1000 AND ((tlinfo.credit_pt_achieved1 = x_credit_pt_achieved1) OR ((tlinfo.credit_pt_achieved1 IS NULL) AND (X_credit_pt_achieved1 IS NULL)))
1001 AND ((tlinfo.credit_pt_achieved2 = x_credit_pt_achieved2) OR ((tlinfo.credit_pt_achieved2 IS NULL) AND (X_credit_pt_achieved2 IS NULL)))
1002 AND ((tlinfo.credit_pt_achieved3 = x_credit_pt_achieved3) OR ((tlinfo.credit_pt_achieved3 IS NULL) AND (X_credit_pt_achieved3 IS NULL)))
1003 AND ((tlinfo.credit_pt_achieved4 = x_credit_pt_achieved4) OR ((tlinfo.credit_pt_achieved4 IS NULL) AND (X_credit_pt_achieved4 IS NULL)))
1004 AND ((tlinfo.credit_level1 = x_credit_level1) OR ((tlinfo.credit_level1 IS NULL) AND (X_credit_level1 IS NULL)))
1005 AND ((tlinfo.credit_level2 = x_credit_level2) OR ((tlinfo.credit_level2 IS NULL) AND (X_credit_level2 IS NULL)))
1006 AND ((tlinfo.credit_level3 = x_credit_level3) OR ((tlinfo.credit_level3 IS NULL) AND (X_credit_level3 IS NULL)))
1007 AND ((tlinfo.credit_level4 = x_credit_level4) OR ((tlinfo.credit_level4 IS NULL) AND (X_credit_level4 IS NULL)))
1008 AND ((tlinfo.additional_sup_cost = x_additional_sup_cost) OR ((tlinfo.additional_sup_cost IS NULL) AND (X_additional_sup_cost IS NULL)))
1009 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)))
1010 AND ((tlinfo.disadv_uplift_factor = x_disadv_uplift_factor) OR ((tlinfo.disadv_uplift_factor IS NULL) AND (X_disadv_uplift_factor IS NULL)))
1011 AND ((tlinfo.year_stu = x_year_stu) OR ((tlinfo.year_stu IS NULL) AND (X_year_stu IS NULL)))
1012 AND ((tlinfo.grad_sch_grade = x_grad_sch_grade) OR ((tlinfo.grad_sch_grade IS NULL) AND (X_grad_sch_grade IS NULL)))
1013 AND ((tlinfo.mark = x_mark) OR ((tlinfo.mark IS NULL) AND (X_mark IS NULL)))
1014 AND ((tlinfo.teaching_inst1 = x_teaching_inst1) OR ((tlinfo.teaching_inst1 IS NULL) AND (X_teaching_inst1 IS NULL)))
1015 AND ((tlinfo.teaching_inst2 = x_teaching_inst2) OR ((tlinfo.teaching_inst2 IS NULL) AND (X_teaching_inst2 IS NULL)))
1016 AND ((tlinfo.pro_not_taught = x_pro_not_taught) OR ((tlinfo.pro_not_taught IS NULL) AND (X_pro_not_taught IS NULL)))
1017 AND ((tlinfo.fundability_code = x_fundability_code) OR ((tlinfo.fundability_code IS NULL) AND (X_fundability_code IS NULL)))
1018 AND ((tlinfo.fee_eligibility = x_fee_eligibility) OR ((tlinfo.fee_eligibility IS NULL) AND (X_fee_eligibility IS NULL)))
1019 AND ((tlinfo.fee_band = x_fee_band) OR ((tlinfo.fee_band IS NULL) AND (X_fee_band IS NULL)))
1020 AND ((tlinfo.non_payment_reason = x_non_payment_reason) OR ((tlinfo.non_payment_reason IS NULL) AND (X_non_payment_reason IS NULL)))
1021 AND ((tlinfo.student_fee = x_student_fee) OR ((tlinfo.student_fee IS NULL) AND (X_student_fee IS NULL)))
1022 AND ((tlinfo.fte_intensity = x_fte_intensity) OR ((tlinfo.fte_intensity IS NULL) AND (X_fte_intensity IS NULL)))
1023 AND ((tlinfo.calculated_fte = x_calculated_fte) OR ((tlinfo.calculated_fte IS NULL) AND (X_calculated_fte IS NULL)))
1024 AND ((tlinfo.fte_calc_type = x_fte_calc_type) OR ((tlinfo.fte_calc_type IS NULL) AND (X_fte_calc_type IS NULL)))
1025 AND ((tlinfo.type_of_year = x_type_of_year) OR ((tlinfo.type_of_year IS NULL) AND (X_type_of_year IS NULL)))
1026 ) THEN
1027 NULL;
1028 ELSE
1029 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1030 igs_ge_msg_stack.add;
1031 app_exception.raise_exception;
1032 END IF;
1033
1034 RETURN;
1035
1036 END lock_row;
1037
1038
1039 PROCEDURE update_row (
1040 x_rowid IN VARCHAR2,
1041 x_hesa_en_susa_id IN NUMBER,
1042 x_person_id IN NUMBER,
1043 x_course_cd IN VARCHAR2,
1044 x_unit_set_cd IN VARCHAR2,
1045 x_us_version_number IN NUMBER,
1046 x_sequence_number IN NUMBER,
1047 x_new_he_entrant_cd IN VARCHAR2,
1048 x_term_time_accom IN VARCHAR2,
1049 x_disability_allow IN VARCHAR2,
1050 x_additional_sup_band IN VARCHAR2,
1051 x_sldd_discrete_prov IN VARCHAR2,
1052 x_study_mode IN VARCHAR2,
1053 x_study_location IN VARCHAR2,
1054 x_fte_perc_override IN NUMBER ,
1055 x_franchising_activity IN VARCHAR2,
1059 x_credit_value_yop1 IN NUMBER,
1056 x_completion_status IN VARCHAR2,
1057 x_good_stand_marker IN VARCHAR2,
1058 x_complete_pyr_study_cd IN VARCHAR2,
1060 x_credit_value_yop2 IN NUMBER,
1061 x_credit_value_yop3 IN NUMBER,
1062 x_credit_value_yop4 IN NUMBER,
1063 x_credit_level_achieved1 IN VARCHAR2,
1064 x_credit_level_achieved2 IN VARCHAR2,
1065 x_credit_level_achieved3 IN VARCHAR2,
1066 x_credit_level_achieved4 IN VARCHAR2,
1067 x_credit_pt_achieved1 IN NUMBER,
1068 x_credit_pt_achieved2 IN NUMBER,
1069 x_credit_pt_achieved3 IN NUMBER,
1070 x_credit_pt_achieved4 IN NUMBER,
1071 x_credit_level1 IN VARCHAR2,
1072 x_credit_level2 IN VARCHAR2,
1073 x_credit_level3 IN VARCHAR2,
1074 x_credit_level4 IN VARCHAR2,
1075 x_additional_sup_cost IN NUMBER,
1076 x_enh_fund_elig_cd IN VARCHAR2,
1077 x_disadv_uplift_factor IN NUMBER,
1078 x_year_stu IN NUMBER,
1079 x_grad_sch_grade IN VARCHAR2,
1080 x_mark IN NUMBER,
1081 x_teaching_inst1 IN VARCHAR2,
1082 x_teaching_inst2 IN VARCHAR2,
1083 x_pro_not_taught IN NUMBER,
1084 x_fundability_code IN VARCHAR2,
1085 x_fee_eligibility IN VARCHAR2,
1086 x_fee_band IN VARCHAR2,
1087 x_non_payment_reason IN VARCHAR2,
1088 x_student_fee IN VARCHAR2,
1089 x_fte_intensity IN NUMBER ,
1090 x_calculated_fte IN NUMBER ,
1091 x_fte_calc_type IN VARCHAR2 ,
1092 x_type_of_year IN VARCHAR2 ,
1093 x_mode IN VARCHAR2
1094 ) AS
1095 /*
1096 || Created By : [email protected]
1097 || Created On : 20-FEB-2002
1098 || Purpose : Handles the UPDATE DML logic for the table.
1099 || Known limitations, enhancements or remarks :
1100 || Change History :
1101 || Who When What
1102 ||sbaliga 8-Apr-2002 Added 3 new parameters x_fte_intensity,x_calculated_fte and
1103 || x_fte_calc_type as part of #2278825
1104 || (reverse chronological order - newest change first)
1105 */
1106 x_last_update_date DATE ;
1107 x_last_updated_by NUMBER;
1108 x_last_update_login NUMBER;
1109
1110 BEGIN
1111
1112 x_last_update_date := SYSDATE;
1113 IF (X_MODE = 'I') THEN
1114 x_last_updated_by := 1;
1115 x_last_update_login := 0;
1116 ELSIF (X_MODE IN ('R', 'S')) THEN
1117 x_last_updated_by := fnd_global.user_id;
1118 IF x_last_updated_by IS NULL THEN
1119 x_last_updated_by := -1;
1120 END IF;
1121 x_last_update_login := fnd_global.login_id;
1122 IF (x_last_update_login IS NULL) THEN
1123 x_last_update_login := -1;
1124 END IF;
1125 ELSE
1126 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1127 igs_ge_msg_stack.add;
1128 app_exception.raise_exception;
1129 END IF;
1130
1131 before_dml(
1132 p_action => 'UPDATE',
1133 x_rowid => x_rowid,
1134 x_hesa_en_susa_id => x_hesa_en_susa_id,
1135 x_person_id => x_person_id,
1136 x_course_cd => x_course_cd,
1137 x_unit_set_cd => x_unit_set_cd,
1138 x_us_version_number => x_us_version_number,
1139 x_sequence_number => x_sequence_number,
1140 x_new_he_entrant_cd => x_new_he_entrant_cd,
1141 x_term_time_accom => x_term_time_accom,
1142 x_disability_allow => x_disability_allow,
1143 x_additional_sup_band => x_additional_sup_band,
1144 x_sldd_discrete_prov => x_sldd_discrete_prov,
1145 x_study_mode => x_study_mode,
1146 x_study_location => x_study_location,
1147 x_fte_perc_override => x_fte_perc_override,
1148 x_franchising_activity => x_franchising_activity,
1149 x_completion_status => x_completion_status,
1150 x_good_stand_marker => x_good_stand_marker,
1151 x_complete_pyr_study_cd => x_complete_pyr_study_cd,
1152 x_credit_value_yop1 => x_credit_value_yop1,
1153 x_credit_value_yop2 => x_credit_value_yop2,
1154 x_credit_value_yop3 => x_credit_value_yop3,
1155 x_credit_value_yop4 => x_credit_value_yop4,
1156 x_credit_level_achieved1 => x_credit_level_achieved1,
1157 x_credit_level_achieved2 => x_credit_level_achieved2,
1158 x_credit_level_achieved3 => x_credit_level_achieved3,
1159 x_credit_level_achieved4 => x_credit_level_achieved4,
1160 x_credit_pt_achieved1 => x_credit_pt_achieved1,
1161 x_credit_pt_achieved2 => x_credit_pt_achieved2,
1162 x_credit_pt_achieved3 => x_credit_pt_achieved3,
1166 x_credit_level3 => x_credit_level3,
1163 x_credit_pt_achieved4 => x_credit_pt_achieved4,
1164 x_credit_level1 => x_credit_level1,
1165 x_credit_level2 => x_credit_level2,
1167 x_credit_level4 => x_credit_level4,
1168 x_additional_sup_cost => x_additional_sup_cost,
1169 x_enh_fund_elig_cd => x_enh_fund_elig_cd,
1170 x_disadv_uplift_factor => x_disadv_uplift_factor,
1171 x_year_stu => x_year_stu,
1172 x_grad_sch_grade => x_grad_sch_grade,
1173 x_mark => x_mark,
1174 x_teaching_inst1 => x_teaching_inst1,
1175 x_teaching_inst2 => x_teaching_inst2,
1176 x_pro_not_taught => x_pro_not_taught,
1177 x_fundability_code => x_fundability_code,
1178 x_fee_eligibility => x_fee_eligibility,
1179 x_fee_band => x_fee_band,
1180 x_non_payment_reason => x_non_payment_reason,
1181 x_student_fee => x_student_fee,
1182 x_fte_intensity => x_fte_intensity,
1183 x_calculated_fte =>x_calculated_fte,
1184 x_fte_calc_type => x_fte_calc_type,
1185 x_type_of_year => x_type_of_year,
1186 x_creation_date => x_last_update_date,
1187 x_created_by => x_last_updated_by,
1188 x_last_update_date => x_last_update_date,
1189 x_last_updated_by => x_last_updated_by,
1190 x_last_update_login => x_last_update_login
1191 );
1192
1193 IF (x_mode = 'S') THEN
1194 igs_sc_gen_001.set_ctx('R');
1195 END IF;
1196 UPDATE igs_he_en_susa
1197 SET
1198 person_id = new_references.person_id,
1199 course_cd = new_references.course_cd,
1200 unit_set_cd = new_references.unit_set_cd,
1201 us_version_number = new_references.us_version_number,
1202 sequence_number = new_references.sequence_number,
1203 new_he_entrant_cd = new_references.new_he_entrant_cd,
1204 term_time_accom = new_references.term_time_accom,
1205 disability_allow = new_references.disability_allow,
1206 additional_sup_band = new_references.additional_sup_band,
1207 sldd_discrete_prov = new_references.sldd_discrete_prov,
1208 study_mode = new_references.study_mode,
1209 study_location = new_references.study_location,
1210 fte_perc_override = new_references.fte_perc_override,
1211 franchising_activity = new_references.franchising_activity,
1212 completion_status = new_references.completion_status,
1213 good_stand_marker = new_references.good_stand_marker,
1214 complete_pyr_study_cd = new_references.complete_pyr_study_cd,
1215 credit_value_yop1 = new_references.credit_value_yop1,
1216 credit_value_yop2 = new_references.credit_value_yop2,
1217 credit_value_yop3 = new_references.credit_value_yop3,
1218 credit_value_yop4 = new_references.credit_value_yop4,
1219 credit_level_achieved1 = new_references.credit_level_achieved1,
1220 credit_level_achieved2 = new_references.credit_level_achieved2,
1221 credit_level_achieved3 = new_references.credit_level_achieved3,
1222 credit_level_achieved4 = new_references.credit_level_achieved4,
1223 credit_pt_achieved1 = new_references.credit_pt_achieved1,
1224 credit_pt_achieved2 = new_references.credit_pt_achieved2,
1225 credit_pt_achieved3 = new_references.credit_pt_achieved3,
1226 credit_pt_achieved4 = new_references.credit_pt_achieved4,
1227 credit_level1 = new_references.credit_level1,
1228 credit_level2 = new_references.credit_level2,
1229 credit_level3 = new_references.credit_level3,
1230 credit_level4 = new_references.credit_level4,
1231 additional_sup_cost = new_references.additional_sup_cost,
1232 enh_fund_elig_cd = new_references.enh_fund_elig_cd,
1233 disadv_uplift_factor = new_references.disadv_uplift_factor,
1234 year_stu = new_references.year_stu,
1235 grad_sch_grade = new_references.grad_sch_grade,
1236 mark = new_references.mark,
1237 teaching_inst1 = new_references.teaching_inst1,
1238 teaching_inst2 = new_references.teaching_inst2,
1239 pro_not_taught = new_references.pro_not_taught,
1240 fundability_code = new_references.fundability_code,
1241 fee_eligibility = new_references.fee_eligibility,
1242 fee_band = new_references.fee_band,
1243 non_payment_reason = new_references.non_payment_reason,
1244 student_fee = new_references.student_fee,
1245 fte_intensity = new_references.fte_intensity,
1246 calculated_fte = new_references.calculated_fte,
1247 fte_calc_type = new_references.fte_calc_type,
1248 type_of_year = new_references.type_of_year,
1249 last_update_date = x_last_update_date,
1250 last_updated_by = x_last_updated_by,
1251 last_update_login = x_last_update_login
1252 WHERE rowid = x_rowid;
1253
1254 IF (SQL%NOTFOUND) THEN
1255 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1256 igs_ge_msg_stack.add;
1257 igs_sc_gen_001.unset_ctx('R');
1258 app_exception.raise_exception;
1259 END IF;
1260 IF (x_mode = 'S') THEN
1261 igs_sc_gen_001.unset_ctx('R');
1262 END IF;
1263
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 IF (SQLCODE = (-28115)) THEN
1267 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1268 fnd_message.set_token ('ERR_CD', SQLCODE);
1269 igs_ge_msg_stack.add;
1270 igs_sc_gen_001.unset_ctx('R');
1271 app_exception.raise_exception;
1272 ELSE
1273 igs_sc_gen_001.unset_ctx('R');
1274 RAISE;
1275 END IF;
1276
1277 END update_row;
1278
1279
1280 PROCEDURE add_row (
1281 x_rowid IN OUT NOCOPY VARCHAR2,
1282 x_hesa_en_susa_id IN OUT NOCOPY NUMBER,
1283 x_person_id IN NUMBER,
1284 x_course_cd IN VARCHAR2,
1285 x_unit_set_cd IN VARCHAR2,
1286 x_us_version_number IN NUMBER,
1287 x_sequence_number IN NUMBER,
1288 x_new_he_entrant_cd IN VARCHAR2,
1289 x_term_time_accom IN VARCHAR2,
1290 x_disability_allow IN VARCHAR2,
1291 x_additional_sup_band IN VARCHAR2,
1292 x_sldd_discrete_prov IN VARCHAR2,
1293 x_study_mode IN VARCHAR2,
1294 x_study_location IN VARCHAR2,
1295 x_fte_perc_override IN NUMBER,
1296 x_franchising_activity IN VARCHAR2,
1297 x_completion_status IN VARCHAR2,
1298 x_good_stand_marker IN VARCHAR2,
1299 x_complete_pyr_study_cd IN VARCHAR2,
1300 x_credit_value_yop1 IN NUMBER,
1301 x_credit_value_yop2 IN NUMBER,
1302 x_credit_value_yop3 IN NUMBER,
1303 x_credit_value_yop4 IN NUMBER,
1304 x_credit_level_achieved1 IN VARCHAR2,
1305 x_credit_level_achieved2 IN VARCHAR2,
1306 x_credit_level_achieved3 IN VARCHAR2,
1307 x_credit_level_achieved4 IN VARCHAR2,
1308 x_credit_pt_achieved1 IN NUMBER,
1309 x_credit_pt_achieved2 IN NUMBER,
1310 x_credit_pt_achieved3 IN NUMBER,
1311 x_credit_pt_achieved4 IN NUMBER,
1312 x_credit_level1 IN VARCHAR2,
1313 x_credit_level2 IN VARCHAR2,
1314 x_credit_level3 IN VARCHAR2,
1315 x_credit_level4 IN VARCHAR2,
1316 x_additional_sup_cost IN NUMBER,
1317 x_enh_fund_elig_cd IN VARCHAR2,
1318 x_disadv_uplift_factor IN NUMBER,
1319 x_year_stu IN NUMBER,
1320 x_grad_sch_grade IN VARCHAR2,
1321 x_mark IN NUMBER,
1322 x_teaching_inst1 IN VARCHAR2,
1323 x_teaching_inst2 IN VARCHAR2,
1324 x_pro_not_taught IN NUMBER,
1325 x_fundability_code IN VARCHAR2,
1326 x_fee_eligibility IN VARCHAR2,
1327 x_fee_band IN VARCHAR2,
1328 x_non_payment_reason IN VARCHAR2,
1329 x_student_fee IN VARCHAR2,
1330 x_fte_intensity IN NUMBER ,
1331 x_calculated_fte IN NUMBER ,
1332 x_fte_calc_type IN VARCHAR2 ,
1333 x_type_of_year IN VARCHAR2 ,
1334 x_mode IN VARCHAR2
1335 ) AS
1336 /*
1337 || Created By : [email protected]
1338 || Created On : 20-FEB-2002
1339 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1340 || Known limitations, enhancements or remarks :
1341 || Change History :
1342 || Who When What
1343 || sbaliga 8-Apr-2002 Added 3 parameters fte_intensity,calculated_fte and
1344 || fte_calc_type as part of #2278825
1345 || (reverse chronological order - newest change first)
1346 */
1347 CURSOR c1 IS
1348 SELECT rowid
1349 FROM igs_he_en_susa
1350 WHERE hesa_en_susa_id = x_hesa_en_susa_id;
1351
1352 BEGIN
1353
1354 OPEN c1;
1355 FETCH c1 INTO x_rowid;
1356 IF (c1%NOTFOUND) THEN
1357 CLOSE c1;
1358
1359 insert_row (
1360 x_rowid,
1361 x_hesa_en_susa_id,
1362 x_person_id,
1363 x_course_cd,
1364 x_unit_set_cd,
1365 x_us_version_number,
1366 x_sequence_number,
1367 x_new_he_entrant_cd,
1368 x_term_time_accom,
1369 x_disability_allow,
1370 x_additional_sup_band,
1371 x_sldd_discrete_prov,
1372 x_study_mode,
1373 x_study_location,
1374 x_fte_perc_override,
1375 x_franchising_activity,
1376 x_completion_status,
1377 x_good_stand_marker,
1378 x_complete_pyr_study_cd,
1379 x_credit_value_yop1,
1380 x_credit_value_yop2,
1381 x_credit_value_yop3,
1382 x_credit_value_yop4,
1383 x_credit_level_achieved1,
1384 x_credit_level_achieved2,
1385 x_credit_level_achieved3,
1386 x_credit_level_achieved4,
1387 x_credit_pt_achieved1,
1388 x_credit_pt_achieved2,
1389 x_credit_pt_achieved3,
1390 x_credit_pt_achieved4,
1391 x_credit_level1,
1392 x_credit_level2,
1393 x_credit_level3,
1394 x_credit_level4,
1395 x_additional_sup_cost,
1396 x_enh_fund_elig_cd,
1397 x_disadv_uplift_factor,
1398 x_year_stu,
1399 x_grad_sch_grade,
1400 x_mark,
1401 x_teaching_inst1,
1402 x_teaching_inst2,
1403 x_pro_not_taught,
1404 x_fundability_code,
1405 x_fee_eligibility,
1406 x_fee_band,
1407 x_non_payment_reason,
1408 x_student_fee,
1409 x_fte_intensity,
1410 x_calculated_fte,
1411 x_fte_calc_type,
1412 x_type_of_year ,
1413 x_mode
1414 );
1415 RETURN;
1416 END IF;
1417 CLOSE c1;
1418
1419 update_row (
1420 x_rowid,
1421 x_hesa_en_susa_id,
1422 x_person_id,
1423 x_course_cd,
1424 x_unit_set_cd,
1425 x_us_version_number,
1426 x_sequence_number,
1427 x_new_he_entrant_cd,
1428 x_term_time_accom,
1429 x_disability_allow,
1430 x_additional_sup_band,
1431 x_sldd_discrete_prov,
1432 x_study_mode,
1433 x_study_location,
1434 x_fte_perc_override,
1435 x_franchising_activity,
1436 x_completion_status,
1437 x_good_stand_marker,
1438 x_complete_pyr_study_cd,
1439 x_credit_value_yop1,
1440 x_credit_value_yop2,
1441 x_credit_value_yop3,
1442 x_credit_value_yop4,
1443 x_credit_level_achieved1,
1444 x_credit_level_achieved2,
1445 x_credit_level_achieved3,
1446 x_credit_level_achieved4,
1447 x_credit_pt_achieved1,
1448 x_credit_pt_achieved2,
1449 x_credit_pt_achieved3,
1450 x_credit_pt_achieved4,
1451 x_credit_level1,
1452 x_credit_level2,
1453 x_credit_level3,
1454 x_credit_level4,
1455 x_grad_sch_grade,
1456 x_additional_sup_cost,
1457 x_enh_fund_elig_cd,
1458 x_disadv_uplift_factor,
1459 x_year_stu,
1460 x_mark,
1461 x_teaching_inst1,
1462 x_teaching_inst2,
1463 x_pro_not_taught,
1464 x_fundability_code,
1465 x_fee_eligibility,
1466 x_fee_band,
1467 x_non_payment_reason,
1468 x_student_fee,
1469 x_fte_intensity,
1470 x_calculated_fte,
1471 x_fte_calc_type,
1472 x_type_of_year ,
1473 x_mode
1474 );
1475
1476 END add_row;
1477
1478
1479 PROCEDURE delete_row (
1480 x_rowid IN VARCHAR2,
1481 x_mode IN VARCHAR2
1482 ) AS
1483 /*
1484 || Created By : [email protected]
1485 || Created On : 20-FEB-2002
1486 || Purpose : Handles the DELETE DML logic for the table.
1487 || Known limitations, enhancements or remarks :
1488 || Change History :
1489 || Who When What
1490 || (reverse chronological order - newest change first)
1491 */
1492 BEGIN
1493
1494 before_dml (
1495 p_action => 'DELETE',
1496 x_rowid => x_rowid
1497 );
1498
1499 IF (x_mode = 'S') THEN
1500 igs_sc_gen_001.set_ctx('R');
1501 END IF;
1502 DELETE FROM igs_he_en_susa
1503 WHERE rowid = x_rowid;
1504
1505 IF (SQL%NOTFOUND) THEN
1506 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1507 igs_ge_msg_stack.add;
1508 igs_sc_gen_001.unset_ctx('R');
1509 app_exception.raise_exception;
1510 END IF;
1511 IF (x_mode = 'S') THEN
1512 igs_sc_gen_001.unset_ctx('R');
1513 END IF;
1514
1515
1516 END delete_row;
1517
1518
1519 END igs_he_en_susa_pkg;