[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_PROG_OU_CC_PKG
Source
1 PACKAGE BODY igs_he_prog_ou_cc_pkg AS
2 /* $Header: IGSWI47B.pls 120.0 2005/06/01 14:27:11 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_prog_ou_cc%ROWTYPE;
6 new_references igs_he_prog_ou_cc%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_hesa_prog_cc_id IN NUMBER,
12 x_course_cd IN VARCHAR2,
13 x_version_number IN NUMBER,
14 x_org_unit_cd IN VARCHAR2,
15 x_cost_centre IN VARCHAR2,
16 x_subject IN VARCHAR2,
17 x_proportion IN NUMBER,
18 x_creation_date IN DATE,
19 x_created_by IN NUMBER,
20 x_last_update_date IN DATE,
21 x_last_updated_by IN NUMBER,
22 x_last_update_login IN NUMBER
23 ) AS
24 /*
25 || Created By : [email protected]
26 || Created On : 25-JAN-2005
27 || Purpose : Initialises the Old and New references for the columns of the table.
28 || Known limitations, enhancements or remarks :
29 || Change History :
30 || Who When What
31 || (reverse chronological order - newest change first)
32 */
33
34 CURSOR cur_old_ref_values IS
35 SELECT *
36 FROM igs_he_prog_ou_cc
37 WHERE rowid = x_rowid;
38
39 BEGIN
40
41 l_rowid := x_rowid;
42
43 -- Code for setting the Old and New Reference Values.
44 -- Populate Old Values.
45 OPEN cur_old_ref_values;
46 FETCH cur_old_ref_values INTO old_references;
47 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
48 CLOSE cur_old_ref_values;
49 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
50 igs_ge_msg_stack.add;
51 app_exception.raise_exception;
52 RETURN;
53 END IF;
54 CLOSE cur_old_ref_values;
55
56 -- Populate New Values.
57 new_references.hesa_prog_cc_id := x_hesa_prog_cc_id;
58 new_references.course_cd := x_course_cd;
59 new_references.version_number := x_version_number;
60 new_references.org_unit_cd := x_org_unit_cd;
61 new_references.cost_centre := x_cost_centre;
62 new_references.subject := x_subject;
63 new_references.proportion := x_proportion;
64
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72
73 new_references.last_update_date := x_last_update_date;
74 new_references.last_updated_by := x_last_updated_by;
75 new_references.last_update_login := x_last_update_login;
76
77 END set_column_values;
78
79
80 PROCEDURE check_uniqueness AS
81 /*
82 || Created By : [email protected]
83 || Created On : 25-JAN-2005
84 || Purpose : Handles the Unique Constraint logic defined for the columns.
85 || Known limitations, enhancements or remarks :
86 || Change History :
87 || Who When What
88 || (reverse chronological order - newest change first)
89 */
90 BEGIN
91
92 IF ( get_uk_for_validation (
93 new_references.course_cd,
94 new_references.version_number,
95 new_references.org_unit_cd,
96 new_references.cost_centre,
97 new_references.subject
98 )
99 ) THEN
100 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
101 igs_ge_msg_stack.add;
102 app_exception.raise_exception;
103 END IF;
104
105 END check_uniqueness;
106
107
108 PROCEDURE check_parent_existance AS
109 /*
110 || Created By : [email protected]
111 || Created On : 25-JAN-2005
112 || Purpose : Checks for the existance of Parent records.
113 || Known limitations, enhancements or remarks :
114 || Change History :
115 || Who When What
116 || (reverse chronological order - newest change first)
117 */
118 BEGIN
119
120 IF (((old_references.course_cd = new_references.course_cd) AND
121 (old_references.version_number = new_references.version_number) AND
122 (old_references.org_unit_cd = new_references.org_unit_cd)) OR
123 ((new_references.course_cd IS NULL) OR
124 (new_references.version_number IS NULL) OR
125 (new_references.org_unit_cd IS NULL))) THEN
126 NULL;
127 ELSIF NOT igs_ps_own_pkg.get_pk_for_validation (
128 new_references.course_cd,
129 new_references.version_number,
130 new_references.org_unit_cd
131 ) THEN
132 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
133 igs_ge_msg_stack.add;
134 app_exception.raise_exception;
135 END IF;
136
137 END check_parent_existance;
138
139
140 FUNCTION get_pk_for_validation (
141 x_hesa_prog_cc_id IN NUMBER
142 ) RETURN BOOLEAN AS
143 /*
144 || Created By : [email protected]
145 || Created On : 25-JAN-2005
146 || Purpose : Validates the Primary Key of the table.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 CURSOR cur_rowid IS
153 SELECT rowid
154 FROM igs_he_prog_ou_cc
155 WHERE hesa_prog_cc_id = x_hesa_prog_cc_id
156 FOR UPDATE NOWAIT;
157
158 lv_rowid cur_rowid%RowType;
159
160 BEGIN
161
162 OPEN cur_rowid;
163 FETCH cur_rowid INTO lv_rowid;
164 IF (cur_rowid%FOUND) THEN
165 CLOSE cur_rowid;
166 RETURN(TRUE);
167 ELSE
168 CLOSE cur_rowid;
169 RETURN(FALSE);
170 END IF;
171
172 END get_pk_for_validation;
173
174
175 FUNCTION get_uk_for_validation (
176 x_course_cd IN VARCHAR2,
177 x_version_number IN NUMBER,
178 x_org_unit_cd IN VARCHAR2,
179 x_cost_centre IN VARCHAR2,
180 x_subject IN VARCHAR2
181 ) RETURN BOOLEAN AS
182 /*
183 || Created By : [email protected]
184 || Created On : 25-JAN-2005
185 || Purpose : Validates the Unique Keys of the table.
186 || Known limitations, enhancements or remarks :
187 || Change History :
188 || Who When What
189 || (reverse chronological order - newest change first)
190 */
191 CURSOR cur_rowid IS
192 SELECT rowid
193 FROM igs_he_prog_ou_cc
194 WHERE course_cd = x_course_cd
195 AND version_number = x_version_number
196 AND org_unit_cd = x_org_unit_cd
197 AND cost_centre = x_cost_centre
198 AND subject = x_subject
199 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
200
201 lv_rowid cur_rowid%RowType;
202
203 BEGIN
204
205 OPEN cur_rowid;
206 FETCH cur_rowid INTO lv_rowid;
207 IF (cur_rowid%FOUND) THEN
208 CLOSE cur_rowid;
209 RETURN (true);
210 ELSE
211 CLOSE cur_rowid;
212 RETURN(FALSE);
213 END IF;
214
215 END get_uk_for_validation ;
216
217
218 PROCEDURE get_fk_igs_ps_own (
219 x_course_cd IN VARCHAR2,
220 x_version_number IN NUMBER,
221 x_org_unit_cd IN VARCHAR2
222 ) AS
223 /*
224 || Created By : [email protected]
225 || Created On : 25-JAN-2005
226 || Purpose : Validates the Foreign Keys for the table.
227 || Known limitations, enhancements or remarks :
228 || Change History :
229 || Who When What
230 || (reverse chronological order - newest change first)
231 */
232 CURSOR cur_rowid IS
233 SELECT rowid
234 FROM igs_he_prog_ou_cc
235 WHERE ((course_cd = x_course_cd) AND
236 (org_unit_cd = x_org_unit_cd) AND
237 (version_number = x_version_number));
238
239 lv_rowid cur_rowid%RowType;
240
241 BEGIN
242
243 OPEN cur_rowid;
244 FETCH cur_rowid INTO lv_rowid;
245 IF (cur_rowid%FOUND) THEN
246 CLOSE cur_rowid;
247 fnd_message.set_name ('IGS', 'IGS_HE_CC_OU_FK');
248 igs_ge_msg_stack.add;
249 app_exception.raise_exception;
250 RETURN;
251 END IF;
252 CLOSE cur_rowid;
253
254 END get_fk_igs_ps_own;
255
256
257 PROCEDURE before_dml (
258 p_action IN VARCHAR2,
259 x_rowid IN VARCHAR2,
260 x_hesa_prog_cc_id IN NUMBER,
261 x_course_cd IN VARCHAR2,
262 x_version_number IN NUMBER,
263 x_org_unit_cd IN VARCHAR2,
264 x_cost_centre IN VARCHAR2,
265 x_subject IN VARCHAR2,
266 x_proportion IN NUMBER,
267 x_creation_date IN DATE,
268 x_created_by IN NUMBER,
269 x_last_update_date IN DATE,
270 x_last_updated_by IN NUMBER,
271 x_last_update_login IN NUMBER
272 ) AS
273 /*
274 || Created By : [email protected]
275 || Created On : 25-JAN-2005
276 || Purpose : Initialises the columns, Checks Constraints, Calls the
277 || Trigger Handlers for the table, before any DML operation.
278 || Known limitations, enhancements or remarks :
279 || Change History :
280 || Who When What
281 || (reverse chronological order - newest change first)
282 */
283 BEGIN
284
285 set_column_values (
286 p_action,
287 x_rowid,
288 x_hesa_prog_cc_id,
289 x_course_cd,
290 x_version_number,
291 x_org_unit_cd,
292 x_cost_centre,
293 x_subject,
294 x_proportion,
295 x_creation_date,
296 x_created_by,
297 x_last_update_date,
298 x_last_updated_by,
299 x_last_update_login
300 );
301
302 IF (p_action = 'INSERT') THEN
303 -- Call all the procedures related to Before Insert.
304 IF ( get_pk_for_validation(
305 new_references.hesa_prog_cc_id
306 )
307 ) THEN
308 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
309 igs_ge_msg_stack.add;
310 app_exception.raise_exception;
311 END IF;
312 check_uniqueness;
313 check_parent_existance;
314 ELSIF (p_action = 'UPDATE') THEN
315 -- Call all the procedures related to Before Update.
316 check_uniqueness;
317 check_parent_existance;
318 ELSIF (p_action = 'VALIDATE_INSERT') THEN
319 -- Call all the procedures related to Before Insert.
320 IF ( get_pk_for_validation (
321 new_references.hesa_prog_cc_id
322 )
323 ) THEN
324 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
325 igs_ge_msg_stack.add;
326 app_exception.raise_exception;
327 END IF;
328 check_uniqueness;
329 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
330 check_uniqueness;
331 END IF;
332
333 END before_dml;
334
335
336 PROCEDURE insert_row (
337 x_rowid IN OUT NOCOPY VARCHAR2,
338 x_hesa_prog_cc_id IN OUT NOCOPY NUMBER,
339 x_course_cd IN VARCHAR2,
340 x_version_number IN NUMBER,
341 x_org_unit_cd IN VARCHAR2,
342 x_cost_centre IN VARCHAR2,
343 x_subject IN VARCHAR2,
344 x_proportion IN NUMBER,
345 x_mode IN VARCHAR2
346 ) AS
347 /*
348 || Created By : [email protected]
349 || Created On : 25-JAN-2005
350 || Purpose : Handles the INSERT DML logic for the table.
351 || Known limitations, enhancements or remarks :
352 || Change History :
353 || Who When What
354 || (reverse chronological order - newest change first)
355 */
356
357 x_last_update_date DATE;
358 x_last_updated_by NUMBER;
359 x_last_update_login NUMBER;
360
361 BEGIN
362
363 x_last_update_date := SYSDATE;
367 ELSIF (x_mode = 'R') THEN
364 IF (x_mode = 'I') THEN
365 x_last_updated_by := 1;
366 x_last_update_login := 0;
368 x_last_updated_by := fnd_global.user_id;
369 IF (x_last_updated_by IS NULL) THEN
370 x_last_updated_by := -1;
371 END IF;
372 x_last_update_login := fnd_global.login_id;
373 IF (x_last_update_login IS NULL) THEN
374 x_last_update_login := -1;
375 END IF;
376 ELSE
377 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
378 fnd_message.set_token ('ROUTINE', 'IGS_HE_PROG_OU_CC_PKG.INSERT_ROW');
379 igs_ge_msg_stack.add;
380 app_exception.raise_exception;
381 END IF;
382
383 x_hesa_prog_cc_id := NULL;
384
385 before_dml(
386 p_action => 'INSERT',
387 x_rowid => x_rowid,
388 x_hesa_prog_cc_id => x_hesa_prog_cc_id,
389 x_course_cd => x_course_cd,
390 x_version_number => x_version_number,
391 x_org_unit_cd => x_org_unit_cd,
392 x_cost_centre => x_cost_centre,
393 x_subject => x_subject,
394 x_proportion => x_proportion,
395 x_creation_date => x_last_update_date,
396 x_created_by => x_last_updated_by,
397 x_last_update_date => x_last_update_date,
398 x_last_updated_by => x_last_updated_by,
399 x_last_update_login => x_last_update_login
400 );
401
402 INSERT INTO igs_he_prog_ou_cc (
403 hesa_prog_cc_id,
404 course_cd,
405 version_number,
406 org_unit_cd,
407 cost_centre,
408 subject,
409 proportion,
410 creation_date,
411 created_by,
412 last_update_date,
413 last_updated_by,
414 last_update_login
415 ) VALUES (
416 igs_he_prog_ou_cc_s.NEXTVAL,
417 new_references.course_cd,
418 new_references.version_number,
419 new_references.org_unit_cd,
420 new_references.cost_centre,
421 new_references.subject,
422 new_references.proportion,
423 x_last_update_date,
424 x_last_updated_by,
425 x_last_update_date,
426 x_last_updated_by,
427 x_last_update_login
428 ) RETURNING ROWID, hesa_prog_cc_id INTO x_rowid, x_hesa_prog_cc_id;
429
430 END insert_row;
431
432
433 PROCEDURE lock_row (
434 x_rowid IN VARCHAR2,
435 x_hesa_prog_cc_id IN NUMBER,
436 x_course_cd IN VARCHAR2,
437 x_version_number IN NUMBER,
438 x_org_unit_cd IN VARCHAR2,
439 x_cost_centre IN VARCHAR2,
440 x_subject IN VARCHAR2,
441 x_proportion IN NUMBER
442 ) AS
443 /*
444 || Created By : [email protected]
445 || Created On : 25-JAN-2005
446 || Purpose : Handles the LOCK mechanism for the table.
447 || Known limitations, enhancements or remarks :
448 || Change History :
449 || Who When What
450 || (reverse chronological order - newest change first)
451 */
452 CURSOR c1 IS
453 SELECT
454 course_cd,
455 version_number,
456 org_unit_cd,
457 cost_centre,
458 subject,
459 proportion
460 FROM igs_he_prog_ou_cc
461 WHERE rowid = x_rowid
462 FOR UPDATE NOWAIT;
463
464 tlinfo c1%ROWTYPE;
465
466 BEGIN
467
468 OPEN c1;
469 FETCH c1 INTO tlinfo;
470 IF (c1%notfound) THEN
471 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
472 igs_ge_msg_stack.add;
473 CLOSE c1;
474 app_exception.raise_exception;
475 RETURN;
476 END IF;
477 CLOSE c1;
478
479 IF (
480 (tlinfo.course_cd = x_course_cd)
481 AND (tlinfo.version_number = x_version_number)
482 AND (tlinfo.org_unit_cd = x_org_unit_cd)
483 AND (tlinfo.cost_centre = x_cost_centre)
484 AND (tlinfo.subject = x_subject)
485 AND (tlinfo.proportion = x_proportion)
486 ) THEN
487 NULL;
488 ELSE
489 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
490 igs_ge_msg_stack.add;
491 app_exception.raise_exception;
492 END IF;
493
494 RETURN;
495
496 END lock_row;
497
498
499 PROCEDURE update_row (
500 x_rowid IN VARCHAR2,
501 x_hesa_prog_cc_id IN NUMBER,
502 x_course_cd IN VARCHAR2,
503 x_version_number IN NUMBER,
504 x_org_unit_cd IN VARCHAR2,
505 x_cost_centre IN VARCHAR2,
506 x_subject IN VARCHAR2,
507 x_proportion IN NUMBER,
508 x_mode IN VARCHAR2
509 ) AS
510 /*
511 || Created By : [email protected]
512 || Created On : 25-JAN-2005
513 || Purpose : Handles the UPDATE DML logic for the table.
514 || Known limitations, enhancements or remarks :
515 || Change History :
516 || Who When What
517 || (reverse chronological order - newest change first)
518 */
519 x_last_update_date DATE ;
520 x_last_updated_by NUMBER;
521 x_last_update_login NUMBER;
522
523 BEGIN
524
525 x_last_update_date := SYSDATE;
526 IF (X_MODE = 'I') THEN
527 x_last_updated_by := 1;
528 x_last_update_login := 0;
529 ELSIF (x_mode = 'R') THEN
530 x_last_updated_by := fnd_global.user_id;
531 IF x_last_updated_by IS NULL THEN
532 x_last_updated_by := -1;
533 END IF;
534 x_last_update_login := fnd_global.login_id;
535 IF (x_last_update_login IS NULL) THEN
536 x_last_update_login := -1;
537 END IF;
538 ELSE
539 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
540 fnd_message.set_token ('ROUTINE', 'IGS_HE_PROG_OU_CC_PKG.UPDATE_ROW');
541 igs_ge_msg_stack.add;
542 app_exception.raise_exception;
543 END IF;
544
545 before_dml(
546 p_action => 'UPDATE',
547 x_rowid => x_rowid,
548 x_hesa_prog_cc_id => x_hesa_prog_cc_id,
549 x_course_cd => x_course_cd,
550 x_version_number => x_version_number,
551 x_org_unit_cd => x_org_unit_cd,
552 x_cost_centre => x_cost_centre,
553 x_subject => x_subject,
554 x_proportion => x_proportion,
555 x_creation_date => x_last_update_date,
556 x_created_by => x_last_updated_by,
557 x_last_update_date => x_last_update_date,
558 x_last_updated_by => x_last_updated_by,
559 x_last_update_login => x_last_update_login
560 );
561
562 UPDATE igs_he_prog_ou_cc
563 SET
564 course_cd = new_references.course_cd,
565 version_number = new_references.version_number,
566 org_unit_cd = new_references.org_unit_cd,
567 cost_centre = new_references.cost_centre,
568 subject = new_references.subject,
569 proportion = new_references.proportion,
570 last_update_date = x_last_update_date,
571 last_updated_by = x_last_updated_by,
572 last_update_login = x_last_update_login
573 WHERE rowid = x_rowid;
574
575 IF (SQL%NOTFOUND) THEN
576 RAISE NO_DATA_FOUND;
577 END IF;
578
579 END update_row;
580
581
582 PROCEDURE add_row (
583 x_rowid IN OUT NOCOPY VARCHAR2,
584 x_hesa_prog_cc_id IN OUT NOCOPY NUMBER,
585 x_course_cd IN VARCHAR2,
586 x_version_number IN NUMBER,
587 x_org_unit_cd IN VARCHAR2,
588 x_cost_centre IN VARCHAR2,
589 x_subject IN VARCHAR2,
590 x_proportion IN NUMBER,
591 x_mode IN VARCHAR2
592 ) AS
593 /*
594 || Created By : [email protected]
595 || Created On : 25-JAN-2005
596 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
597 || Known limitations, enhancements or remarks :
598 || Change History :
599 || Who When What
600 || (reverse chronological order - newest change first)
601 */
602 CURSOR c1 IS
603 SELECT rowid
604 FROM igs_he_prog_ou_cc
605 WHERE hesa_prog_cc_id = x_hesa_prog_cc_id;
606
607 BEGIN
608
609 OPEN c1;
610 FETCH c1 INTO x_rowid;
611 IF (c1%NOTFOUND) THEN
612 CLOSE c1;
613
614 insert_row (
615 x_rowid,
616 x_hesa_prog_cc_id,
617 x_course_cd,
618 x_version_number,
619 x_org_unit_cd,
620 x_cost_centre,
621 x_subject,
622 x_proportion,
623 x_mode
624 );
625 RETURN;
626 END IF;
627 CLOSE c1;
628
629 update_row (
630 x_rowid,
631 x_hesa_prog_cc_id,
632 x_course_cd,
633 x_version_number,
634 x_org_unit_cd,
635 x_cost_centre,
636 x_subject,
637 x_proportion,
638 x_mode
639 );
640
641 END add_row;
642
643
644 PROCEDURE delete_row (
645 x_rowid IN VARCHAR2
646 ) AS
647 /*
648 || Created By : [email protected]
649 || Created On : 25-JAN-2005
650 || Purpose : Handles the DELETE DML logic for the table.
651 || Known limitations, enhancements or remarks :
652 || Change History :
653 || Who When What
654 || (reverse chronological order - newest change first)
655 */
656 BEGIN
657
658 before_dml (
659 p_action => 'DELETE',
660 x_rowid => x_rowid
661 );
662
663 DELETE FROM igs_he_prog_ou_cc
664 WHERE rowid = x_rowid;
665
666 IF (SQL%NOTFOUND) THEN
667 RAISE NO_DATA_FOUND;
668 END IF;
669
670 END delete_row;
671
672
673 END igs_he_prog_ou_cc_pkg;