[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_ST_PROG_ALL_PKG
Source
1 PACKAGE BODY igs_he_st_prog_all_pkg AS
2 /* $Header: IGSWI26B.pls 120.1 2006/02/06 19:54:15 jbaber noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_st_prog_all%ROWTYPE;
6 new_references igs_he_st_prog_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_hesa_st_prog_id IN NUMBER ,
12 x_org_id IN NUMBER ,
13 x_course_cd IN VARCHAR2 ,
14 x_version_number IN NUMBER ,
15 x_teacher_train_prog_id IN VARCHAR2 ,
16 x_itt_phase IN VARCHAR2 ,
17 x_bilingual_itt_marker IN VARCHAR2 ,
18 x_teaching_qual_sought_sector IN VARCHAR2 ,
19 x_teaching_qual_sought_subj1 IN VARCHAR2 ,
20 x_teaching_qual_sought_subj2 IN VARCHAR2 ,
21 x_teaching_qual_sought_subj3 IN VARCHAR2 ,
22 x_location_of_study IN VARCHAR2 ,
23 x_other_inst_prov_teaching1 IN VARCHAR2 ,
24 x_other_inst_prov_teaching2 IN VARCHAR2 ,
25 x_prop_teaching_in_welsh IN NUMBER ,
26 x_prop_not_taught IN NUMBER ,
27 x_credit_transfer_scheme IN VARCHAR2 ,
28 x_return_type IN VARCHAR2 ,
29 x_default_award IN VARCHAR2 ,
30 x_program_calc IN VARCHAR2 ,
31 x_level_applicable_to_funding IN VARCHAR2 ,
32 x_franchising_activity IN VARCHAR2 ,
33 x_nhs_funding_source IN VARCHAR2 ,
34 x_fe_program_marker IN VARCHAR2 ,
35 x_fee_band IN VARCHAR2 ,
36 x_fundability IN VARCHAR2 ,
37 x_fte_intensity IN NUMBER ,
38 x_teach_period_start_dt IN DATE ,
39 x_teach_period_end_dt IN DATE ,
40 x_creation_date IN DATE ,
41 x_created_by IN NUMBER ,
42 x_last_update_date IN DATE ,
43 x_last_updated_by IN NUMBER ,
44 x_last_update_login IN NUMBER ,
45 x_implied_fund_rate IN NUMBER ,
46 x_gov_initiatives_cd IN VARCHAR2 ,
47 x_units_for_qual IN NUMBER ,
48 x_disadv_uplift_elig_cd IN VARCHAR2 ,
49 x_franch_partner_cd IN VARCHAR2 ,
50 x_franch_out_arr_cd IN VARCHAR2 ,
51 x_exclude_flag IN VARCHAR2
52 ) AS
53 /*
54 || Created By : [email protected]
55 || Created On : 29-JAN-2002
56 || Purpose : Initialises the Old and New references for the columns of the table.
57 || Known limitations, enhancements or remarks :
58 || Change History :
59 || Who When What
60 || sbaliga 4-Apr-2002 Added 3 new parameters to the function i.e. x_fte_intensity,
61 || x_teach_period_start_dt and x_teach_period_end_dt
62 || (reverse chronological order - newest change first)
63 */
64
65 CURSOR cur_old_ref_values IS
66 SELECT *
67 FROM IGS_HE_ST_PROG_ALL
68 WHERE rowid = x_rowid;
69
70 BEGIN
71
72 l_rowid := x_rowid;
73
74 -- Code for setting the Old and New Reference Values.
75 -- Populate Old Values.
76 OPEN cur_old_ref_values;
77 FETCH cur_old_ref_values INTO old_references;
78 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
79 CLOSE cur_old_ref_values;
80 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
81 igs_ge_msg_stack.add;
82 app_exception.raise_exception;
83 RETURN;
84 END IF;
85 CLOSE cur_old_ref_values;
86
87 -- Populate New Values.
88 new_references.hesa_st_prog_id := x_hesa_st_prog_id;
89 new_references.org_id := x_org_id;
90 new_references.course_cd := x_course_cd;
91 new_references.version_number := x_version_number;
92 new_references.teacher_train_prog_id := x_teacher_train_prog_id;
93 new_references.itt_phase := x_itt_phase;
94 new_references.bilingual_itt_marker := x_bilingual_itt_marker;
95 new_references.teaching_qual_sought_sector := x_teaching_qual_sought_sector;
96 new_references.teaching_qual_sought_subj1 := x_teaching_qual_sought_subj1;
97 new_references.teaching_qual_sought_subj2 := x_teaching_qual_sought_subj2;
98 new_references.teaching_qual_sought_subj3 := x_teaching_qual_sought_subj3;
99 new_references.location_of_study := x_location_of_study;
100 new_references.other_inst_prov_teaching1 := x_other_inst_prov_teaching1;
101 new_references.other_inst_prov_teaching2 := x_other_inst_prov_teaching2;
102 new_references.prop_teaching_in_welsh := x_prop_teaching_in_welsh;
103 new_references.prop_not_taught := x_prop_not_taught;
104 new_references.credit_transfer_scheme := x_credit_transfer_scheme;
105 new_references.return_type := x_return_type;
106 new_references.default_award := x_default_award;
107 new_references.program_calc := x_program_calc;
108 new_references.level_applicable_to_funding := x_level_applicable_to_funding;
109 new_references.franchising_activity := x_franchising_activity;
110 new_references.nhs_funding_source := x_nhs_funding_source;
111 new_references.fe_program_marker := x_fe_program_marker;
112 new_references.fee_band := x_fee_band;
113 new_references.fundability := x_fundability;
114 new_references.fte_intensity := x_fte_intensity;
115 new_references.teach_period_start_dt := x_teach_period_start_dt;
116 new_references.teach_period_end_dt := x_teach_period_end_dt;
117 new_references.implied_fund_rate := x_implied_fund_rate;
118 new_references.gov_initiatives_cd := x_gov_initiatives_cd;
119 new_references.units_for_qual := x_units_for_qual;
120 new_references.disadv_uplift_elig_cd := x_disadv_uplift_elig_cd;
121 new_references.franch_partner_cd := x_franch_partner_cd;
122 new_references.franch_out_arr_cd := x_franch_out_arr_cd;
123 new_references.exclude_flag := x_exclude_flag;
124
125 IF (p_action = 'UPDATE') THEN
126 new_references.creation_date := old_references.creation_date;
127 new_references.created_by := old_references.created_by;
128 ELSE
129 new_references.creation_date := x_creation_date;
130 new_references.created_by := x_created_by;
131 END IF;
132
133 new_references.last_update_date := x_last_update_date;
134 new_references.last_updated_by := x_last_updated_by;
135 new_references.last_update_login := x_last_update_login;
136
137 END set_column_values;
138
139
140 PROCEDURE check_uniqueness AS
141 /*
142 || Created By : [email protected]
143 || Created On : 29-JAN-2002
144 || Purpose : Handles the Unique Constraint logic defined for the columns.
145 || Known limitations, enhancements or remarks :
146 || Change History :
147 || Who When What
148 || (reverse chronological order - newest change first)
149 */
150 BEGIN
151
152 IF ( get_uk_for_validation (
153 new_references.course_cd,
154 new_references.version_number
155 )
156 ) THEN
157 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
158 igs_ge_msg_stack.add;
159 app_exception.raise_exception;
160 END IF;
161
162 END check_uniqueness;
163
164
165 PROCEDURE check_parent_existance AS
166 /*
167 || Created By : [email protected]
168 || Created On : 29-JAN-2002
169 || Purpose : Checks for the existance of Parent records.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 BEGIN
176
177 IF (((old_references.course_cd = new_references.course_cd) AND
178 (old_references.version_number = new_references.version_number)) OR
179 ((new_references.course_cd IS NULL) OR
180 (new_references.version_number IS NULL))) THEN
181 NULL;
182 ELSIF NOT igs_ps_ver_pkg.get_pk_for_validation (
183 new_references.course_cd,
184 new_references.version_number
185 ) THEN
186 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
187 igs_ge_msg_stack.add;
188 app_exception.raise_exception;
189 END IF;
190
191 END check_parent_existance;
192
193
194 FUNCTION get_pk_for_validation (
195 x_hesa_st_prog_id IN NUMBER
196 ) RETURN BOOLEAN AS
197 /*
198 || Created By : [email protected]
199 || Created On : 29-JAN-2002
200 || Purpose : Validates the Primary Key of the table.
201 || Known limitations, enhancements or remarks :
202 || Change History :
203 || Who When What
204 || (reverse chronological order - newest change first)
205 */
206 CURSOR cur_rowid IS
207 SELECT rowid
208 FROM igs_he_st_prog_all
209 WHERE hesa_st_prog_id = x_hesa_st_prog_id
210 FOR UPDATE NOWAIT;
211
212 lv_rowid cur_rowid%RowType;
213
214 BEGIN
215
216 OPEN cur_rowid;
217 FETCH cur_rowid INTO lv_rowid;
218 IF (cur_rowid%FOUND) THEN
219 CLOSE cur_rowid;
220 RETURN(TRUE);
221 ELSE
222 CLOSE cur_rowid;
223 RETURN(FALSE);
224 END IF;
225
226 END get_pk_for_validation;
227
228
229 FUNCTION get_uk_for_validation (
230 x_course_cd IN VARCHAR2,
231 x_version_number IN NUMBER
232 ) RETURN BOOLEAN AS
233 /*
234 || Created By : [email protected]
235 || Created On : 29-JAN-2002
236 || Purpose : Validates the Unique Keys of the table.
237 || Known limitations, enhancements or remarks :
238 || Change History :
239 || Who When What
240 || (reverse chronological order - newest change first)
241 */
242 CURSOR cur_rowid IS
243 SELECT rowid
244 FROM igs_he_st_prog_all
245 WHERE course_cd = x_course_cd
246 AND version_number = x_version_number
247 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
248
249 lv_rowid cur_rowid%RowType;
250
251 BEGIN
252
253 OPEN cur_rowid;
254 FETCH cur_rowid INTO lv_rowid;
255 IF (cur_rowid%FOUND) THEN
256 CLOSE cur_rowid;
257 RETURN (true);
258 ELSE
259 CLOSE cur_rowid;
260 RETURN(FALSE);
261 END IF;
262
263 END get_uk_for_validation ;
264
265
266 PROCEDURE get_fk_igs_ps_ver_all (
267 x_course_cd IN VARCHAR2,
268 x_version_number IN NUMBER
269 ) AS
270 /*
271 || Created By : [email protected]
272 || Created On : 29-JAN-2002
273 || Purpose : Validates the Foreign Keys for the table.
274 || Known limitations, enhancements or remarks :
275 || Change History :
276 || Who When What
277 || (reverse chronological order - newest change first)
278 */
279 CURSOR cur_rowid IS
280 SELECT rowid
281 FROM igs_he_st_prog_all
282 WHERE ((course_cd = x_course_cd) AND
283 (version_number = x_version_number));
284
285 lv_rowid cur_rowid%RowType;
286
287 BEGIN
288
289 OPEN cur_rowid;
290 FETCH cur_rowid INTO lv_rowid;
291 IF (cur_rowid%FOUND) THEN
292 CLOSE cur_rowid;
293 fnd_message.set_name ('IGS', 'IGS_HE_HSPR_CRV_FK');
294 igs_ge_msg_stack.add;
295 app_exception.raise_exception;
296 RETURN;
297 END IF;
298 CLOSE cur_rowid;
299
300 END get_fk_igs_ps_ver_all;
301
302 PROCEDURE check_prog_attempt_exists AS
303 CURSOR cur_prog_attempt(cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
304 cp_version igs_en_stdnt_ps_att.version_number%TYPE
305 ) IS
306 SELECT 'X' FROM igs_en_stdnt_ps_att
307 WHERE course_cd = cp_course_cd
308 AND version_number = cp_version;
309
310 l_prog_attempt VARCHAR2(1);
311
312 BEGIN
313 --Check whether any SPAs exists for this program
314 OPEN cur_prog_attempt(new_references.course_cd,
315 new_references.version_number);
316 FETCH cur_prog_attempt INTO l_prog_attempt;
317 IF cur_prog_attempt%FOUND THEN
318 CLOSE cur_prog_attempt;
319 fnd_message.set_name ('IGS', 'IGS_HE_CANT_DEL_SPA_EXIST');
320 igs_ge_msg_stack.add;
321 app_exception.raise_exception;
322 RETURN;
323 END IF;
324 CLOSE cur_prog_attempt;
325
326 END check_prog_attempt_exists;
327
328 PROCEDURE before_dml (
329 p_action IN VARCHAR2,
330 x_rowid IN VARCHAR2 ,
331 x_hesa_st_prog_id IN NUMBER ,
332 x_org_id IN NUMBER ,
333 x_course_cd IN VARCHAR2 ,
334 x_version_number IN NUMBER ,
335 x_teacher_train_prog_id IN VARCHAR2 ,
336 x_itt_phase IN VARCHAR2 ,
337 x_bilingual_itt_marker IN VARCHAR2 ,
338 x_teaching_qual_sought_sector IN VARCHAR2 ,
339 x_teaching_qual_sought_subj1 IN VARCHAR2 ,
340 x_teaching_qual_sought_subj2 IN VARCHAR2 ,
341 x_teaching_qual_sought_subj3 IN VARCHAR2 ,
342 x_location_of_study IN VARCHAR2 ,
343 x_other_inst_prov_teaching1 IN VARCHAR2 ,
344 x_other_inst_prov_teaching2 IN VARCHAR2 ,
345 x_prop_teaching_in_welsh IN NUMBER ,
346 x_prop_not_taught IN NUMBER ,
347 x_credit_transfer_scheme IN VARCHAR2 ,
348 x_return_type IN VARCHAR2 ,
349 x_default_award IN VARCHAR2 ,
350 x_program_calc IN VARCHAR2 ,
351 x_level_applicable_to_funding IN VARCHAR2 ,
352 x_franchising_activity IN VARCHAR2 ,
353 x_nhs_funding_source IN VARCHAR2 ,
354 x_fe_program_marker IN VARCHAR2 ,
355 x_fee_band IN VARCHAR2 ,
356 x_fundability IN VARCHAR2 ,
357 x_fte_intensity IN NUMBER ,
358 x_teach_period_start_dt IN DATE ,
359 x_teach_period_end_dt IN DATE ,
360 x_creation_date IN DATE ,
361 x_created_by IN NUMBER ,
362 x_last_update_date IN DATE ,
363 x_last_updated_by IN NUMBER ,
364 x_last_update_login IN NUMBER ,
365 x_implied_fund_rate IN NUMBER DEFAULT NULL,
366 x_gov_initiatives_cd IN VARCHAR2 DEFAULT NULL,
367 x_units_for_qual IN NUMBER DEFAULT NULL,
368 x_disadv_uplift_elig_cd IN VARCHAR2 DEFAULT NULL,
369 x_franch_partner_cd IN VARCHAR2 DEFAULT NULL,
370 x_franch_out_arr_cd IN VARCHAR2 DEFAULT NULL,
371 x_exclude_flag IN VARCHAR2
372 ) AS
373 /*
374 || Created By : [email protected]
375 || Created On : 29-JAN-2002
376 || Purpose : Initialises the columns, Checks Constraints, Calls the
377 || Trigger Handlers for the table, before any DML operation.
378 || Known limitations, enhancements or remarks :
379 || Change History :
380 || Who When What
381 || pmarada 20-may-2003 While deleting a record checking whether any students
382 || attempted this program as per the bug 2932025.
383 || sbaliga Apr-4-2002 Added 3 new parameters to the function
384 || i.e. x_fte_intensity,x_teach_period_start_dt
385 || and x_teach_period_end_dt
386 || (reverse chronological order - newest change first)
387 */
388 BEGIN
389
390 set_column_values (
391 p_action,
392 x_rowid,
393 x_hesa_st_prog_id,
394 x_org_id,
395 x_course_cd,
396 x_version_number,
397 x_teacher_train_prog_id,
398 x_itt_phase,
399 x_bilingual_itt_marker,
400 x_teaching_qual_sought_sector,
401 x_teaching_qual_sought_subj1,
402 x_teaching_qual_sought_subj2,
403 x_teaching_qual_sought_subj3,
404 x_location_of_study,
405 x_other_inst_prov_teaching1,
406 x_other_inst_prov_teaching2,
407 x_prop_teaching_in_welsh,
408 x_prop_not_taught,
409 x_credit_transfer_scheme,
410 x_return_type,
411 x_default_award,
412 x_program_calc,
413 x_level_applicable_to_funding,
414 x_franchising_activity,
415 x_nhs_funding_source,
416 x_fe_program_marker,
417 x_fee_band,
418 x_fundability,
419 x_fte_intensity,
420 x_teach_period_start_dt,
421 x_teach_period_end_dt,
422 x_creation_date,
423 x_created_by,
424 x_last_update_date,
425 x_last_updated_by,
426 x_last_update_login,
427 x_implied_fund_rate ,
428 x_gov_initiatives_cd ,
429 x_units_for_qual ,
430 x_disadv_uplift_elig_cd ,
431 x_franch_partner_cd ,
432 x_franch_out_arr_cd ,
433 x_exclude_flag
434 );
435
436 IF (p_action = 'INSERT') THEN
437 -- Call all the procedures related to Before Insert.
438 IF ( get_pk_for_validation(
439 new_references.hesa_st_prog_id
440 )
441 ) THEN
442 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
443 igs_ge_msg_stack.add;
444 app_exception.raise_exception;
445 END IF;
446 check_uniqueness;
447 check_parent_existance;
448 ELSIF (p_action = 'UPDATE') THEN
449 -- Call all the procedures related to Before Update.
450 check_uniqueness;
451 check_parent_existance;
452 ELSIF (p_action = 'DELETE') THEN
453 check_prog_attempt_exists;
454
455 ELSIF (p_action = 'VALIDATE_INSERT') THEN
456 -- Call all the procedures related to Before Insert.
457 IF ( get_pk_for_validation (
458 new_references.hesa_st_prog_id
459 )
460 ) THEN
461 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
462 igs_ge_msg_stack.add;
463 app_exception.raise_exception;
464 END IF;
465 check_uniqueness;
466 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
467 check_uniqueness;
468 ELSIF (p_action = 'VALIDATE_DELETE') THEN
469 check_prog_attempt_exists;
470 END IF;
471
472 END before_dml;
473
474
475 PROCEDURE insert_row (
476 x_rowid IN OUT NOCOPY VARCHAR2,
477 x_hesa_st_prog_id IN OUT NOCOPY NUMBER,
478 x_org_id IN NUMBER,
479 x_course_cd IN VARCHAR2,
480 x_version_number IN NUMBER,
481 x_teacher_train_prog_id IN VARCHAR2,
482 x_itt_phase IN VARCHAR2,
483 x_bilingual_itt_marker IN VARCHAR2,
484 x_teaching_qual_sought_sector IN VARCHAR2,
485 x_teaching_qual_sought_subj1 IN VARCHAR2,
486 x_teaching_qual_sought_subj2 IN VARCHAR2,
487 x_teaching_qual_sought_subj3 IN VARCHAR2,
488 x_location_of_study IN VARCHAR2,
489 x_other_inst_prov_teaching1 IN VARCHAR2,
490 x_other_inst_prov_teaching2 IN VARCHAR2,
491 x_prop_teaching_in_welsh IN NUMBER,
492 x_prop_not_taught IN NUMBER,
493 x_credit_transfer_scheme IN VARCHAR2,
494 x_return_type IN VARCHAR2,
495 x_default_award IN VARCHAR2,
496 x_program_calc IN VARCHAR2,
497 x_level_applicable_to_funding IN VARCHAR2,
498 x_franchising_activity IN VARCHAR2,
499 x_nhs_funding_source IN VARCHAR2,
500 x_fe_program_marker IN VARCHAR2,
501 x_fee_band IN VARCHAR2,
502 x_fundability IN VARCHAR2,
503 x_fte_intensity IN NUMBER ,
504 x_teach_period_start_dt IN DATE ,
505 x_teach_period_end_dt IN DATE ,
506 x_mode IN VARCHAR2 ,
507 x_implied_fund_rate IN NUMBER DEFAULT NULL,
508 x_gov_initiatives_cd IN VARCHAR2 DEFAULT NULL,
509 x_units_for_qual IN NUMBER DEFAULT NULL,
510 x_disadv_uplift_elig_cd IN VARCHAR2 DEFAULT NULL,
511 x_franch_partner_cd IN VARCHAR2 DEFAULT NULL,
512 x_franch_out_arr_cd IN VARCHAR2 DEFAULT NULL,
513 x_exclude_flag IN VARCHAR2
514 ) AS
515 /*
516 || Created By : [email protected]
517 || Created On : 29-JAN-2002
518 || Purpose : Handles the INSERT DML logic for the table.
519 || Known limitations, enhancements or remarks :
520 || Change History :
521 || Who When What
522 || sbaliga Apr-4-2002 Added 3 new parameters to the function
523 || i.e. x_fte_intensity,x_teach_period_start_dt
524 || and x_teach_period_end_dt
525 || smvk 13-Feb-2002 call to igs_ge_gen_003.get_org_id
526 || w.r.t. SWCR006
527 || (reverse chronological order - newest change first)
528 */
529 CURSOR c IS
530 SELECT rowid
531 FROM igs_he_st_prog_all
532 WHERE hesa_st_prog_id = x_hesa_st_prog_id;
533
534 x_last_update_date DATE;
535 x_last_updated_by NUMBER;
536 x_last_update_login NUMBER;
537 l_mode VARCHAR2(1);
538 BEGIN
539
540 l_mode := NVL(x_mode,'R');
541
542 x_last_update_date := SYSDATE;
543 IF (l_mode = 'I') THEN
544 x_last_updated_by := 1;
545 x_last_update_login := 0;
546 ELSIF (l_mode = 'R') THEN
547 x_last_updated_by := fnd_global.user_id;
548 IF (x_last_updated_by IS NULL) THEN
549 x_last_updated_by := -1;
550 END IF;
551 x_last_update_login := fnd_global.login_id;
552 IF (x_last_update_login IS NULL) THEN
553 x_last_update_login := -1;
554 END IF;
555 ELSE
556 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
557 igs_ge_msg_stack.add;
558 app_exception.raise_exception;
559 END IF;
560
561 SELECT igs_he_st_prog_all_s.NEXTVAL
562 INTO x_hesa_st_prog_id
563 FROM dual;
564
565 before_dml(
566 p_action => 'INSERT',
567 x_rowid => x_rowid,
568 x_hesa_st_prog_id => x_hesa_st_prog_id,
569 x_org_id => igs_ge_gen_003.get_org_id,
570 x_course_cd => x_course_cd,
571 x_version_number => x_version_number,
572 x_teacher_train_prog_id => x_teacher_train_prog_id,
573 x_itt_phase => x_itt_phase,
574 x_bilingual_itt_marker => x_bilingual_itt_marker,
575 x_teaching_qual_sought_sector => x_teaching_qual_sought_sector,
576 x_teaching_qual_sought_subj1 => x_teaching_qual_sought_subj1,
577 x_teaching_qual_sought_subj2 => x_teaching_qual_sought_subj2,
578 x_teaching_qual_sought_subj3 => x_teaching_qual_sought_subj3,
579 x_location_of_study => x_location_of_study,
580 x_other_inst_prov_teaching1 => x_other_inst_prov_teaching1,
581 x_other_inst_prov_teaching2 => x_other_inst_prov_teaching2,
582 x_prop_teaching_in_welsh => x_prop_teaching_in_welsh,
583 x_prop_not_taught => x_prop_not_taught,
584 x_credit_transfer_scheme => x_credit_transfer_scheme,
585 x_return_type => x_return_type,
586 x_default_award => x_default_award,
587 x_program_calc => x_program_calc,
588 x_level_applicable_to_funding => x_level_applicable_to_funding,
589 x_franchising_activity => x_franchising_activity,
590 x_nhs_funding_source => x_nhs_funding_source,
591 x_fe_program_marker => x_fe_program_marker,
592 x_fee_band => x_fee_band,
593 x_fundability => x_fundability,
594 x_fte_intensity => x_fte_intensity,
595 x_teach_period_start_dt => x_teach_period_start_dt,
596 x_teach_period_end_dt => x_teach_period_end_dt,
597 x_creation_date => x_last_update_date,
598 x_created_by => x_last_updated_by,
599 x_last_update_date => x_last_update_date,
600 x_last_updated_by => x_last_updated_by,
601 x_last_update_login => x_last_update_login,
602 x_implied_fund_rate => x_implied_fund_rate ,
603 x_gov_initiatives_cd => x_gov_initiatives_cd ,
604 x_units_for_qual => x_units_for_qual ,
605 x_disadv_uplift_elig_cd => x_disadv_uplift_elig_cd,
606 x_franch_partner_cd => x_franch_partner_cd ,
607 x_franch_out_arr_cd => x_franch_out_arr_cd ,
608 x_exclude_flag => x_exclude_flag
609 );
610
611 INSERT INTO igs_he_st_prog_all (
612 hesa_st_prog_id,
613 org_id,
614 course_cd,
615 version_number,
616 teacher_train_prog_id,
617 itt_phase,
618 bilingual_itt_marker,
619 teaching_qual_sought_sector,
620 teaching_qual_sought_subj1,
621 teaching_qual_sought_subj2,
622 teaching_qual_sought_subj3,
623 location_of_study,
624 other_inst_prov_teaching1,
625 other_inst_prov_teaching2,
626 prop_teaching_in_welsh,
627 prop_not_taught,
628 credit_transfer_scheme,
629 return_type,
630 default_award,
631 program_calc,
632 level_applicable_to_funding,
633 franchising_activity,
634 nhs_funding_source,
635 fe_program_marker,
636 fee_band,
637 fundability,
638 fte_intensity,
639 teach_period_start_dt,
640 teach_period_end_dt,
641 creation_date,
642 created_by,
643 last_update_date,
644 last_updated_by,
645 last_update_login,
646 implied_fund_rate ,
647 gov_initiatives_cd ,
648 units_for_qual ,
649 disadv_uplift_elig_cd ,
650 franch_partner_cd ,
651 franch_out_arr_cd ,
652 exclude_flag
653 ) VALUES (
654 new_references.hesa_st_prog_id,
655 new_references.org_id,
656 new_references.course_cd,
657 new_references.version_number,
658 new_references.teacher_train_prog_id,
659 new_references.itt_phase,
660 new_references.bilingual_itt_marker,
661 new_references.teaching_qual_sought_sector,
662 new_references.teaching_qual_sought_subj1,
663 new_references.teaching_qual_sought_subj2,
664 new_references.teaching_qual_sought_subj3,
665 new_references.location_of_study,
666 new_references.other_inst_prov_teaching1,
667 new_references.other_inst_prov_teaching2,
668 new_references.prop_teaching_in_welsh,
669 new_references.prop_not_taught,
670 new_references.credit_transfer_scheme,
671 new_references.return_type,
672 new_references.default_award,
673 new_references.program_calc,
674 new_references.level_applicable_to_funding,
675 new_references.franchising_activity,
676 new_references.nhs_funding_source,
677 new_references.fe_program_marker,
678 new_references.fee_band,
679 new_references.fundability,
680 new_references.fte_intensity,
681 new_references.teach_period_start_dt,
682 new_references.teach_period_end_dt,
683 x_last_update_date,
684 x_last_updated_by,
685 x_last_update_date,
686 x_last_updated_by,
687 x_last_update_login,
688 new_references.implied_fund_rate ,
689 new_references.gov_initiatives_cd ,
690 new_references.units_for_qual ,
691 new_references.disadv_uplift_elig_cd ,
692 new_references.franch_partner_cd ,
693 new_references.franch_out_arr_cd ,
694 new_references.exclude_flag
695 );
696
697 OPEN c;
698 FETCH c INTO x_rowid;
699 IF (c%NOTFOUND) THEN
700 CLOSE c;
701 RAISE NO_DATA_FOUND;
702 END IF;
703 CLOSE c;
704
705 END insert_row;
706
707
708 PROCEDURE lock_row (
709 x_rowid IN VARCHAR2,
710 x_hesa_st_prog_id IN NUMBER,
711 x_org_id IN NUMBER,
712 x_course_cd IN VARCHAR2,
713 x_version_number IN NUMBER,
714 x_teacher_train_prog_id IN VARCHAR2,
715 x_itt_phase IN VARCHAR2,
716 x_bilingual_itt_marker IN VARCHAR2,
717 x_teaching_qual_sought_sector IN VARCHAR2,
718 x_teaching_qual_sought_subj1 IN VARCHAR2,
719 x_teaching_qual_sought_subj2 IN VARCHAR2,
720 x_teaching_qual_sought_subj3 IN VARCHAR2,
721 x_location_of_study IN VARCHAR2,
722 x_other_inst_prov_teaching1 IN VARCHAR2,
723 x_other_inst_prov_teaching2 IN VARCHAR2,
724 x_prop_teaching_in_welsh IN NUMBER,
725 x_prop_not_taught IN NUMBER,
726 x_credit_transfer_scheme IN VARCHAR2,
727 x_return_type IN VARCHAR2,
728 x_default_award IN VARCHAR2,
729 x_program_calc IN VARCHAR2,
730 x_level_applicable_to_funding IN VARCHAR2,
731 x_franchising_activity IN VARCHAR2,
732 x_nhs_funding_source IN VARCHAR2,
733 x_fe_program_marker IN VARCHAR2,
734 x_fee_band IN VARCHAR2,
735 x_fundability IN VARCHAR2,
736 x_fte_intensity IN NUMBER ,
737 x_teach_period_start_dt IN DATE ,
738 x_teach_period_end_dt IN DATE ,
739 x_implied_fund_rate IN NUMBER DEFAULT NULL,
740 x_gov_initiatives_cd IN VARCHAR2 DEFAULT NULL,
741 x_units_for_qual IN NUMBER DEFAULT NULL,
742 x_disadv_uplift_elig_cd IN VARCHAR2 DEFAULT NULL,
743 x_franch_partner_cd IN VARCHAR2 DEFAULT NULL,
744 x_franch_out_arr_cd IN VARCHAR2 DEFAULT NULL,
745 x_exclude_flag IN VARCHAR2
746 ) AS
747 /*
748 || Created By : [email protected]
749 || Created On : 29-JAN-2002
750 || Purpose : Handles the LOCK mechanism for the table.
751 || Known limitations, enhancements or remarks :
752 || Change History :
753 || Who When What
754 || sbaliga Apr-4-2002 Added 3 new parameters to the function
755 || i.e. x_fte_intensity,x_teach_period_start_dt
756 || and x_teach_period_end_dt
757 || smvk 13-Feb-2002 Removed org_id from cursor declaration
758 || and conditional checking w.r.t. SWCR006
759 || (reverse chronological order - newest change first)
760 */
761 CURSOR c1 IS
762 SELECT
763 course_cd,
764 version_number,
765 teacher_train_prog_id,
766 itt_phase,
767 bilingual_itt_marker,
768 teaching_qual_sought_sector,
769 teaching_qual_sought_subj1,
770 teaching_qual_sought_subj2,
771 teaching_qual_sought_subj3,
772 location_of_study,
773 other_inst_prov_teaching1,
774 other_inst_prov_teaching2,
775 prop_teaching_in_welsh,
776 prop_not_taught,
777 credit_transfer_scheme,
778 return_type,
779 default_award,
780 program_calc,
781 level_applicable_to_funding,
782 franchising_activity,
783 nhs_funding_source,
784 fe_program_marker,
785 fee_band,
786 fundability,
787 fte_intensity,
788 teach_period_start_dt,
789 teach_period_end_dt,
790 implied_fund_rate ,
791 gov_initiatives_cd ,
792 units_for_qual ,
793 disadv_uplift_elig_cd,
794 franch_partner_cd ,
795 franch_out_arr_cd ,
796 exclude_flag
797 FROM igs_he_st_prog_all
798 WHERE rowid = x_rowid
799 FOR UPDATE NOWAIT;
800
801 tlinfo c1%ROWTYPE;
802
803 BEGIN
804
805 OPEN c1;
806 FETCH c1 INTO tlinfo;
807 IF (c1%notfound) THEN
808 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
809 igs_ge_msg_stack.add;
810 CLOSE c1;
811 app_exception.raise_exception;
812 RETURN;
813 END IF;
814 CLOSE c1;
815
816 IF (
817 (tlinfo.course_cd = x_course_cd)
818 AND (tlinfo.version_number = x_version_number)
819 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)))
820 AND ((tlinfo.itt_phase = x_itt_phase) OR ((tlinfo.itt_phase IS NULL) AND (X_itt_phase IS NULL)))
821 AND ((tlinfo.bilingual_itt_marker = x_bilingual_itt_marker) OR ((tlinfo.bilingual_itt_marker IS NULL) AND (X_bilingual_itt_marker IS NULL)))
822 AND ((tlinfo.teaching_qual_sought_sector = x_teaching_qual_sought_sector) OR ((tlinfo.teaching_qual_sought_sector IS NULL) AND (X_teaching_qual_sought_sector IS NULL)))
823 AND ((tlinfo.teaching_qual_sought_subj1 = x_teaching_qual_sought_subj1) OR ((tlinfo.teaching_qual_sought_subj1 IS NULL) AND (X_teaching_qual_sought_subj1 IS NULL)))
824 AND ((tlinfo.teaching_qual_sought_subj2 = x_teaching_qual_sought_subj2) OR ((tlinfo.teaching_qual_sought_subj2 IS NULL) AND (X_teaching_qual_sought_subj2 IS NULL)))
825 AND ((tlinfo.teaching_qual_sought_subj3 = x_teaching_qual_sought_subj3) OR ((tlinfo.teaching_qual_sought_subj3 IS NULL) AND (X_teaching_qual_sought_subj3 IS NULL)))
826 AND ((tlinfo.location_of_study = x_location_of_study) OR ((tlinfo.location_of_study IS NULL) AND (X_location_of_study IS NULL)))
827 AND ((tlinfo.other_inst_prov_teaching1 = x_other_inst_prov_teaching1) OR ((tlinfo.other_inst_prov_teaching1 IS NULL) AND (X_other_inst_prov_teaching1 IS NULL)))
828 AND ((tlinfo.other_inst_prov_teaching2 = x_other_inst_prov_teaching2) OR ((tlinfo.other_inst_prov_teaching2 IS NULL) AND (X_other_inst_prov_teaching2 IS NULL)))
829 AND ((tlinfo.prop_teaching_in_welsh = x_prop_teaching_in_welsh) OR ((tlinfo.prop_teaching_in_welsh IS NULL) AND (X_prop_teaching_in_welsh IS NULL)))
830 AND ((tlinfo.prop_not_taught = x_prop_not_taught) OR ((tlinfo.prop_not_taught IS NULL) AND (X_prop_not_taught IS NULL)))
831 AND ((tlinfo.credit_transfer_scheme = x_credit_transfer_scheme) OR ((tlinfo.credit_transfer_scheme IS NULL) AND (X_credit_transfer_scheme IS NULL)))
832 AND ((tlinfo.return_type = x_return_type) OR ((tlinfo.return_type IS NULL) AND (X_return_type IS NULL)))
833 AND ((tlinfo.default_award = x_default_award) OR ((tlinfo.default_award IS NULL) AND (X_default_award IS NULL)))
834 AND ((tlinfo.program_calc = x_program_calc) OR ((tlinfo.program_calc IS NULL) AND (X_program_calc IS NULL)))
835 AND ((tlinfo.level_applicable_to_funding = x_level_applicable_to_funding) OR ((tlinfo.level_applicable_to_funding IS NULL) AND (X_level_applicable_to_funding IS NULL)))
836 AND ((tlinfo.franchising_activity = x_franchising_activity) OR ((tlinfo.franchising_activity IS NULL) AND (X_franchising_activity IS NULL)))
837 AND ((tlinfo.nhs_funding_source = x_nhs_funding_source) OR ((tlinfo.nhs_funding_source IS NULL) AND (X_nhs_funding_source IS NULL)))
838 AND ((tlinfo.fe_program_marker = x_fe_program_marker) OR ((tlinfo.fe_program_marker IS NULL) AND (X_fe_program_marker IS NULL)))
839 AND ((tlinfo.fee_band = x_fee_band) OR ((tlinfo.fee_band IS NULL) AND (X_fee_band IS NULL)))
840 AND ((tlinfo.fundability = x_fundability) OR ((tlinfo.fundability IS NULL) AND (X_fundability IS NULL)))
841 AND ((tlinfo.fte_intensity = x_fte_intensity) OR ((tlinfo.fte_intensity IS NULL) AND (X_fte_intensity IS NULL)))
842 AND ((tlinfo.teach_period_start_dt = x_teach_period_start_dt) OR ((tlinfo.teach_period_start_dt IS NULL) AND (X_teach_period_start_dt IS NULL)))
843 AND ((tlinfo.teach_period_end_dt = x_teach_period_end_dt) OR ((tlinfo.teach_period_end_dt IS NULL) AND (X_teach_period_end_dt IS NULL)))
844 AND ((tlinfo.implied_fund_rate = x_implied_fund_rate ) OR ((tlinfo.implied_fund_rate IS NULL) AND (x_implied_fund_rate IS NULL)))
845 AND ((tlinfo.gov_initiatives_cd = x_gov_initiatives_cd ) OR ((tlinfo.gov_initiatives_cd IS NULL) AND (x_gov_initiatives_cd IS NULL)))
846 AND ((tlinfo.units_for_qual = x_units_for_qual ) OR ((tlinfo.units_for_qual IS NULL) AND (x_units_for_qual IS NULL)))
847 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)))
848 AND ((tlinfo.franch_partner_cd = x_franch_partner_cd ) OR ((tlinfo.franch_partner_cd IS NULL) AND (x_franch_partner_cd IS NULL)))
849 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)))
850 AND ((tlinfo.exclude_flag = x_exclude_flag ) OR ((tlinfo.exclude_flag IS NULL) AND (x_exclude_flag IS NULL)))
851 ) THEN
852 NULL;
853 ELSE
854 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
855 igs_ge_msg_stack.add;
856 app_exception.raise_exception;
857 END IF;
858
859 RETURN;
860
861 END lock_row;
862
863
864 PROCEDURE update_row (
865 x_rowid IN VARCHAR2,
866 x_hesa_st_prog_id IN NUMBER,
867 x_org_id IN NUMBER,
868 x_course_cd IN VARCHAR2,
869 x_version_number IN NUMBER,
870 x_teacher_train_prog_id IN VARCHAR2,
871 x_itt_phase IN VARCHAR2,
872 x_bilingual_itt_marker IN VARCHAR2,
873 x_teaching_qual_sought_sector IN VARCHAR2,
874 x_teaching_qual_sought_subj1 IN VARCHAR2,
875 x_teaching_qual_sought_subj2 IN VARCHAR2,
876 x_teaching_qual_sought_subj3 IN VARCHAR2,
877 x_location_of_study IN VARCHAR2,
878 x_other_inst_prov_teaching1 IN VARCHAR2,
879 x_other_inst_prov_teaching2 IN VARCHAR2,
880 x_prop_teaching_in_welsh IN NUMBER,
881 x_prop_not_taught IN NUMBER,
882 x_credit_transfer_scheme IN VARCHAR2,
883 x_return_type IN VARCHAR2,
884 x_default_award IN VARCHAR2,
885 x_program_calc IN VARCHAR2,
886 x_level_applicable_to_funding IN VARCHAR2,
887 x_franchising_activity IN VARCHAR2,
888 x_nhs_funding_source IN VARCHAR2,
889 x_fe_program_marker IN VARCHAR2,
890 x_fee_band IN VARCHAR2,
891 x_fundability IN VARCHAR2,
892 x_fte_intensity IN NUMBER ,
893 x_teach_period_start_dt IN DATE ,
894 x_teach_period_end_dt IN DATE ,
895 x_mode IN VARCHAR2 ,
896 x_implied_fund_rate IN NUMBER DEFAULT NULL,
897 x_gov_initiatives_cd IN VARCHAR2 DEFAULT NULL,
898 x_units_for_qual IN NUMBER DEFAULT NULL,
899 x_disadv_uplift_elig_cd IN VARCHAR2 DEFAULT NULL,
900 x_franch_partner_cd IN VARCHAR2 DEFAULT NULL,
901 x_franch_out_arr_cd IN VARCHAR2 DEFAULT NULL,
902 x_exclude_flag IN VARCHAR2
903 ) AS
904 /*
905 || Created By : [email protected]
906 || Created On : 29-JAN-2002
907 || Purpose : Handles the UPDATE DML logic for the table.
908 || Known limitations, enhancements or remarks :
909 || Change History :
910 || Who When What
911 || sbaliga Apr-4-2002 Added 3 new parameters to the function
912 || i.e. x_fte_intensity,x_teach_period_start_dt
913 || and x_teach_period_end_dt
914 || smvk 13-Feb-2002 Call to igs_ge_gen_003.get_org_id
915 || w.r.t. SWCR006
916 || (reverse chronological order - newest change first)
917 */
918 x_last_update_date DATE ;
919 x_last_updated_by NUMBER;
920 x_last_update_login NUMBER;
921 l_mode VARCHAR2(1);
922 BEGIN
923
924 l_mode := NVL(x_mode,'R');
925
926 x_last_update_date := SYSDATE;
927 IF (l_MODE = 'I') THEN
928 x_last_updated_by := 1;
929 x_last_update_login := 0;
930 ELSIF (l_mode = 'R') THEN
931 x_last_updated_by := fnd_global.user_id;
932 IF x_last_updated_by IS NULL THEN
933 x_last_updated_by := -1;
934 END IF;
935 x_last_update_login := fnd_global.login_id;
936 IF (x_last_update_login IS NULL) THEN
937 x_last_update_login := -1;
938 END IF;
939 ELSE
940 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
941 igs_ge_msg_stack.add;
942 app_exception.raise_exception;
943 END IF;
944
945 before_dml(
946 p_action => 'UPDATE',
947 x_rowid => x_rowid,
948 x_hesa_st_prog_id => x_hesa_st_prog_id,
949 x_org_id => igs_ge_gen_003.get_org_id,
950 x_course_cd => x_course_cd,
951 x_version_number => x_version_number,
952 x_teacher_train_prog_id => x_teacher_train_prog_id,
953 x_itt_phase => x_itt_phase,
954 x_bilingual_itt_marker => x_bilingual_itt_marker,
955 x_teaching_qual_sought_sector => x_teaching_qual_sought_sector,
956 x_teaching_qual_sought_subj1 => x_teaching_qual_sought_subj1,
957 x_teaching_qual_sought_subj2 => x_teaching_qual_sought_subj2,
958 x_teaching_qual_sought_subj3 => x_teaching_qual_sought_subj3,
959 x_location_of_study => x_location_of_study,
960 x_other_inst_prov_teaching1 => x_other_inst_prov_teaching1,
961 x_other_inst_prov_teaching2 => x_other_inst_prov_teaching2,
962 x_prop_teaching_in_welsh => x_prop_teaching_in_welsh,
963 x_prop_not_taught => x_prop_not_taught,
964 x_credit_transfer_scheme => x_credit_transfer_scheme,
965 x_return_type => x_return_type,
966 x_default_award => x_default_award,
967 x_program_calc => x_program_calc,
968 x_level_applicable_to_funding => x_level_applicable_to_funding,
969 x_franchising_activity => x_franchising_activity,
970 x_nhs_funding_source => x_nhs_funding_source,
971 x_fe_program_marker => x_fe_program_marker,
972 x_fee_band => x_fee_band,
973 x_fundability => x_fundability,
974 x_fte_intensity => x_fte_intensity,
975 x_teach_period_start_dt => x_teach_period_start_dt,
976 x_teach_period_end_dt => x_teach_period_end_dt,
977 x_creation_date => x_last_update_date,
978 x_created_by => x_last_updated_by,
979 x_last_update_date => x_last_update_date,
980 x_last_updated_by => x_last_updated_by,
981 x_last_update_login => x_last_update_login,
982 x_implied_fund_rate => x_implied_fund_rate ,
983 x_gov_initiatives_cd => x_gov_initiatives_cd ,
984 x_units_for_qual => x_units_for_qual ,
985 x_disadv_uplift_elig_cd => x_disadv_uplift_elig_cd,
986 x_franch_partner_cd => x_franch_partner_cd ,
987 x_franch_out_arr_cd => x_franch_out_arr_cd ,
988 x_exclude_flag => x_exclude_flag
989 );
990
991 UPDATE igs_he_st_prog_all
992 SET
993 course_cd = new_references.course_cd,
994 version_number = new_references.version_number,
995 teacher_train_prog_id = new_references.teacher_train_prog_id,
996 itt_phase = new_references.itt_phase,
997 bilingual_itt_marker = new_references.bilingual_itt_marker,
998 teaching_qual_sought_sector = new_references.teaching_qual_sought_sector,
999 teaching_qual_sought_subj1 = new_references.teaching_qual_sought_subj1,
1000 teaching_qual_sought_subj2 = new_references.teaching_qual_sought_subj2,
1001 teaching_qual_sought_subj3 = new_references.teaching_qual_sought_subj3,
1002 location_of_study = new_references.location_of_study,
1003 other_inst_prov_teaching1 = new_references.other_inst_prov_teaching1,
1004 other_inst_prov_teaching2 = new_references.other_inst_prov_teaching2,
1005 prop_teaching_in_welsh = new_references.prop_teaching_in_welsh,
1006 prop_not_taught = new_references.prop_not_taught,
1007 credit_transfer_scheme = new_references.credit_transfer_scheme,
1008 return_type = new_references.return_type,
1009 default_award = new_references.default_award,
1010 program_calc = new_references.program_calc,
1011 level_applicable_to_funding = new_references.level_applicable_to_funding,
1012 franchising_activity = new_references.franchising_activity,
1013 nhs_funding_source = new_references.nhs_funding_source,
1014 fe_program_marker = new_references.fe_program_marker,
1015 fee_band = new_references.fee_band,
1016 fundability = new_references.fundability,
1017 fte_intensity = new_references.fte_intensity,
1018 teach_period_start_dt = new_references.teach_period_start_dt,
1019 teach_period_end_dt = new_references.teach_period_end_dt,
1020 last_update_date = x_last_update_date,
1021 last_updated_by = x_last_updated_by,
1022 last_update_login = x_last_update_login ,
1023 implied_fund_rate = new_references.implied_fund_rate ,
1024 gov_initiatives_cd = new_references.gov_initiatives_cd ,
1025 units_for_qual = new_references.units_for_qual ,
1026 disadv_uplift_elig_cd = new_references.disadv_uplift_elig_cd,
1027 franch_partner_cd = new_references.franch_partner_cd ,
1028 franch_out_arr_cd = new_references.franch_out_arr_cd ,
1029 exclude_flag = new_references.exclude_flag
1030 WHERE rowid = x_rowid;
1031
1032 IF (SQL%NOTFOUND) THEN
1033 RAISE NO_DATA_FOUND;
1034 END IF;
1035
1036 END update_row;
1037
1038
1039 PROCEDURE add_row (
1040 x_rowid IN OUT NOCOPY VARCHAR2,
1041 x_hesa_st_prog_id IN OUT NOCOPY NUMBER,
1042 x_org_id IN NUMBER,
1043 x_course_cd IN VARCHAR2,
1044 x_version_number IN NUMBER,
1045 x_teacher_train_prog_id IN VARCHAR2,
1046 x_itt_phase IN VARCHAR2,
1047 x_bilingual_itt_marker IN VARCHAR2,
1048 x_teaching_qual_sought_sector IN VARCHAR2,
1049 x_teaching_qual_sought_subj1 IN VARCHAR2,
1050 x_teaching_qual_sought_subj2 IN VARCHAR2,
1051 x_teaching_qual_sought_subj3 IN VARCHAR2,
1052 x_location_of_study IN VARCHAR2,
1053 x_other_inst_prov_teaching1 IN VARCHAR2,
1054 x_other_inst_prov_teaching2 IN VARCHAR2,
1055 x_prop_teaching_in_welsh IN NUMBER,
1056 x_prop_not_taught IN NUMBER,
1057 x_credit_transfer_scheme IN VARCHAR2,
1058 x_return_type IN VARCHAR2,
1059 x_default_award IN VARCHAR2,
1060 x_program_calc IN VARCHAR2,
1061 x_level_applicable_to_funding IN VARCHAR2,
1062 x_franchising_activity IN VARCHAR2,
1063 x_nhs_funding_source IN VARCHAR2,
1064 x_fe_program_marker IN VARCHAR2,
1065 x_fee_band IN VARCHAR2,
1066 x_fundability IN VARCHAR2,
1067 x_fte_intensity IN NUMBER ,
1068 x_teach_period_start_dt IN DATE ,
1069 x_teach_period_end_dt IN DATE ,
1070 x_mode IN VARCHAR2 ,
1071 x_implied_fund_rate IN NUMBER DEFAULT NULL,
1072 x_gov_initiatives_cd IN VARCHAR2 DEFAULT NULL,
1073 x_units_for_qual IN NUMBER DEFAULT NULL,
1074 x_disadv_uplift_elig_cd IN VARCHAR2 DEFAULT NULL,
1075 x_franch_partner_cd IN VARCHAR2 DEFAULT NULL,
1076 x_franch_out_arr_cd IN VARCHAR2 DEFAULT NULL,
1077 x_exclude_flag IN VARCHAR2
1078 ) AS
1079 /*
1080 || Created By : [email protected]
1081 || Created On : 29-JAN-2002
1082 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1083 || Known limitations, enhancements or remarks :
1084 || Change History :
1085 || Who When What
1086 || sbaliga Apr-4-2002 Added 3 new parameters to the function
1087 || i.e. x_fte_intensity,x_teach_period_start_dt
1088 || and x_teach_period_end_dt
1089 || (reverse chronological order - newest change first)
1090 */
1091 CURSOR c1 IS
1092 SELECT rowid
1093 FROM igs_he_st_prog_all
1094 WHERE hesa_st_prog_id = x_hesa_st_prog_id;
1095
1096 l_mode VARCHAR2(1);
1097
1098 BEGIN
1099
1100 l_mode := NVL(x_mode,'R');
1101
1102 OPEN c1;
1103 FETCH c1 INTO x_rowid;
1104 IF (c1%NOTFOUND) THEN
1105 CLOSE c1;
1106
1107 insert_row (
1108 x_rowid,
1109 x_hesa_st_prog_id,
1110 x_org_id,
1111 x_course_cd,
1112 x_version_number,
1113 x_teacher_train_prog_id,
1114 x_itt_phase,
1115 x_bilingual_itt_marker,
1116 x_teaching_qual_sought_sector,
1117 x_teaching_qual_sought_subj1,
1118 x_teaching_qual_sought_subj2,
1119 x_teaching_qual_sought_subj3,
1120 x_location_of_study,
1121 x_other_inst_prov_teaching1,
1122 x_other_inst_prov_teaching2,
1123 x_prop_teaching_in_welsh,
1124 x_prop_not_taught,
1125 x_credit_transfer_scheme,
1126 x_return_type,
1127 x_default_award,
1128 x_program_calc,
1129 x_level_applicable_to_funding,
1130 x_franchising_activity,
1131 x_nhs_funding_source,
1132 x_fe_program_marker,
1133 x_fee_band,
1134 x_fundability,
1135 x_fte_intensity,
1136 x_teach_period_start_dt,
1137 x_teach_period_end_dt,
1138 l_mode,
1139 x_implied_fund_rate ,
1140 x_gov_initiatives_cd ,
1141 x_units_for_qual ,
1142 x_disadv_uplift_elig_cd,
1143 x_franch_partner_cd ,
1144 x_franch_out_arr_cd ,
1145 x_exclude_flag
1146 );
1147 RETURN;
1148 END IF;
1149 CLOSE c1;
1150
1151 update_row (
1152 x_rowid,
1153 x_hesa_st_prog_id,
1154 x_org_id,
1155 x_course_cd,
1156 x_version_number,
1157 x_teacher_train_prog_id,
1158 x_itt_phase,
1159 x_bilingual_itt_marker,
1160 x_teaching_qual_sought_sector,
1161 x_teaching_qual_sought_subj1,
1162 x_teaching_qual_sought_subj2,
1163 x_teaching_qual_sought_subj3,
1164 x_location_of_study,
1165 x_other_inst_prov_teaching1,
1166 x_other_inst_prov_teaching2,
1167 x_prop_teaching_in_welsh,
1168 x_prop_not_taught,
1169 x_credit_transfer_scheme,
1170 x_return_type,
1171 x_default_award,
1172 x_program_calc,
1173 x_level_applicable_to_funding,
1174 x_franchising_activity,
1175 x_nhs_funding_source,
1176 x_fe_program_marker,
1177 x_fee_band,
1178 x_fundability,
1179 x_fte_intensity,
1180 x_teach_period_start_dt,
1181 x_teach_period_end_dt,
1182 l_mode ,
1183 x_implied_fund_rate ,
1184 x_gov_initiatives_cd ,
1185 x_units_for_qual ,
1186 x_disadv_uplift_elig_cd,
1187 x_franch_partner_cd ,
1188 x_franch_out_arr_cd ,
1189 x_exclude_flag
1190 );
1191
1192 END add_row;
1193
1194
1195 PROCEDURE delete_row (
1196 x_rowid IN VARCHAR2
1197 ) AS
1198 /*
1199 || Created By : [email protected]
1200 || Created On : 29-JAN-2002
1201 || Purpose : Handles the DELETE DML logic for the table.
1202 || Known limitations, enhancements or remarks :
1203 || Change History :
1204 || Who When What
1205 || (reverse chronological order - newest change first)
1206 */
1207 BEGIN
1208
1209 before_dml (
1210 p_action => 'DELETE',
1211 x_rowid => x_rowid
1212 );
1213
1214 DELETE FROM igs_he_st_prog_all
1215 WHERE rowid = x_rowid;
1216
1217 IF (SQL%NOTFOUND) THEN
1218 RAISE NO_DATA_FOUND;
1219 END IF;
1220
1221 END delete_row;
1222
1223
1224 END igs_he_st_prog_all_pkg;