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