[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_POOUS_OU_ALL_PKG
Source
1 PACKAGE BODY igs_he_poous_ou_all_pkg AS
2 /* $Header: IGSWI19B.pls 120.1 2006/05/22 09:26:16 jchakrab noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_poous_ou_all%ROWTYPE;
6 new_references igs_he_poous_ou_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_hesa_poous_ou_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_organization_unit IN VARCHAR2 ,
22 x_proportion IN NUMBER ,
23 x_creation_date IN DATE ,
24 x_created_by IN NUMBER ,
25 x_last_update_date IN DATE ,
26 x_last_updated_by IN NUMBER ,
27 x_last_update_login IN NUMBER
28 ) AS
29 /*
30 || Created By : [email protected]
31 || Created On : 26-JAN-2002
32 || Purpose : Initialises the Old and New references for the columns of the table.
33 || Known limitations, enhancements or remarks :
34 || Change History :
35 || Who When What
36 || (reverse chronological order - newest change first)
37 */
38
39 CURSOR cur_old_ref_values IS
40 SELECT *
41 FROM IGS_HE_POOUS_OU_ALL
42 WHERE rowid = x_rowid;
43
44 BEGIN
45
46 l_rowid := x_rowid;
47
48 -- Code for setting the Old and New Reference Values.
49 -- Populate Old Values.
50 OPEN cur_old_ref_values;
51 FETCH cur_old_ref_values INTO old_references;
52 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
53 CLOSE cur_old_ref_values;
54 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
55 igs_ge_msg_stack.add;
56 app_exception.raise_exception;
57 RETURN;
58 END IF;
59 CLOSE cur_old_ref_values;
60
61 -- Populate New Values.
62 new_references.hesa_poous_ou_id := x_hesa_poous_ou_id;
63 new_references.org_id := x_org_id;
64 new_references.course_cd := x_course_cd;
65 new_references.crv_version_number := x_crv_version_number;
66 new_references.cal_type := x_cal_type;
67 new_references.location_cd := x_location_cd;
68 new_references.attendance_mode := x_attendance_mode;
69 new_references.attendance_type := x_attendance_type;
70 new_references.unit_set_cd := x_unit_set_cd;
71 new_references.us_version_number := x_us_version_number;
72 new_references.organization_unit := x_organization_unit;
73 new_references.proportion := x_proportion;
74
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82
83 new_references.last_update_date := x_last_update_date;
84 new_references.last_updated_by := x_last_updated_by;
85 new_references.last_update_login := x_last_update_login;
86
87 END set_column_values;
88
89
90 PROCEDURE check_uniqueness AS
91 /*
92 || Created By : [email protected]
93 || Created On : 26-JAN-2002
94 || Purpose : Handles the Unique Constraint logic defined for the columns.
95 || Known limitations, enhancements or remarks :
96 || Change History :
97 || Who When What
98 || (reverse chronological order - newest change first)
99 */
100 BEGIN
101
102 IF ( get_uk_for_validation (
103 new_references.course_cd,
104 new_references.crv_version_number,
105 new_references.cal_type,
106 new_references.location_cd,
107 new_references.attendance_mode,
108 new_references.attendance_type,
109 new_references.unit_set_cd,
110 new_references.us_version_number,
111 new_references.organization_unit
112 )
113 ) THEN
114 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
115 igs_ge_msg_stack.add;
116 app_exception.raise_exception;
117 END IF;
118
119 END check_uniqueness;
120
121
122 PROCEDURE check_parent_existance AS
123 /*
124 || Created By : [email protected]
125 || Created On : 26-JAN-2002
126 || Purpose : Checks for the existance of Parent records.
127 || Known limitations, enhancements or remarks :
128 || Change History :
129 || Who When What
130 || jchakrab 03-May-2006 Added check for parent unitsets in IGS_PS_OFR_OPT_UNIT_SET_V
131 || sbaliga 9-May-2002 The parent table has been changed from igs_he_poous_all
132 || to igs_ps_ofr_opt_all and Igs_en_unit_set_all as aprt of #2330002
133 || (reverse chronological order - newest change first)
134 */
135
136 CURSOR cur_unitset_check(cp_course_cd igs_ps_ofr_opt_unit_set_v.course_cd%TYPE,
137 cp_crv_version_number igs_ps_ofr_opt_unit_set_v.crv_version_number%TYPE,
138 cp_cal_type igs_ps_ofr_opt_unit_set_v.cal_type%TYPE,
139 cp_location_cd igs_ps_ofr_opt_unit_set_v.location_cd%TYPE,
140 cp_attendance_mode igs_ps_ofr_opt_unit_set_v.attendance_mode%TYPE,
141 cp_attendance_type igs_ps_ofr_opt_unit_set_v.attendance_type%TYPE,
142 cp_unit_set_cd igs_ps_ofr_opt_unit_set_v.unit_set_cd%TYPE,
143 cp_us_version_number igs_ps_ofr_opt_unit_set_v.us_version_number%TYPE) IS
144 SELECT 'X'
145 FROM IGS_PS_OFR_OPT_UNIT_SET_V
146 WHERE COURSE_CD = cp_course_cd
147 AND CRV_VERSION_NUMBER = cp_crv_version_number
148 AND CAL_TYPE = cp_cal_type
149 AND LOCATION_CD = cp_location_cd
150 AND ATTENDANCE_MODE = cp_attendance_mode
151 AND ATTENDANCE_TYPE = cp_attendance_type
152 AND UNIT_SET_CD = cp_unit_set_cd
153 AND US_VERSION_NUMBER = cp_us_version_number;
154
155 l_c_var VARCHAR2(1);
156
157
158 BEGIN
159
160 IF (((old_references.course_cd = new_references.course_cd) AND
161 (old_references.crv_version_number = new_references.crv_version_number) AND
162 (old_references.cal_type = new_references.cal_type) AND
163 (old_references.location_cd = new_references.location_cd) AND
164 (old_references.attendance_type = new_references.attendance_type) AND
165 (old_references.attendance_mode = new_references.attendance_mode))
166 OR
167 ((new_references.course_cd IS NULL) OR
168 (new_references.crv_version_number IS NULL) OR
169 (new_references.cal_type IS NULL) OR
170 (new_references.location_cd IS NULL) OR
171 (new_references.attendance_type IS NULL) OR
172 (new_references.attendance_mode IS NULL))) THEN
173 NULL;
174 ELSIF NOT igs_ps_ofr_opt_pkg.get_pk_For_validation (
175 new_references.course_cd,
176 new_references.crv_version_number,
177 new_references.cal_type,
178 new_references.location_cd,
179 new_references.attendance_mode,
180 new_references.attendance_type
181 ) THEN
182 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
183 igs_ge_msg_stack.add;
184 app_exception.raise_exception;
185 END IF;
186
187 IF((( old_references.unit_set_cd = new_references.unit_set_cd) AND
188 (old_references.us_version_number = new_references.us_version_number))
189 OR
190 ((new_references.unit_set_cd IS NULL) OR
191 (new_references.us_version_number IS NULL)))THEN
192 NULL;
193 ELSIF NOT igs_en_unit_set_pkg.get_pk_for_validation(
194 new_references.unit_set_cd,
195 new_references.us_version_number
196 )THEN
197 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
198 igs_ge_msg_stack.add;
199 app_exception.raise_exception;
200 END IF;
201
202 --Added this check for unit sets in IGS_PS_OFR_OPT_UNIT_SET_V view
203 OPEN cur_unitset_check( new_references.course_cd,
204 new_references.crv_version_number,
205 new_references.cal_type,
206 new_references.location_cd,
207 new_references.attendance_mode,
208 new_references.attendance_type,
209 new_references.unit_set_cd,
210 new_references.us_version_number);
211 FETCH cur_unitset_check INTO l_c_var;
212 IF cur_unitset_check%NOTFOUND THEN
213 CLOSE cur_unitset_check;
214 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
215 IGS_GE_MSG_STACK.ADD;
216 App_Exception.Raise_Exception;
217 END IF;
218 CLOSE cur_unitset_check;
219
220 END check_parent_existance;
221
222
223 PROCEDURE check_child_existance AS
224 /*
225 || Created By : [email protected]
226 || Created On : 12-JAN-2005
227 || Purpose : Checks for the existance of Child records.
228 || Known limitations, enhancements or remarks :
229 || Change History :
230 || Who When What
231 || (reverse chronological order - newest change first)
232 */
233 BEGIN
234
235 igs_he_poous_ou_cc_pkg.get_fk_igs_he_poous_ou (
236 old_references.hesa_poous_ou_id
237 );
238
239 END check_child_existance;
240
241
242 FUNCTION get_pk_for_validation (
243 x_hesa_poous_ou_id IN NUMBER
244 ) RETURN BOOLEAN AS
245 /*
246 || Created By : [email protected]
247 || Created On : 26-JAN-2002
248 || Purpose : Validates the Primary Key of the table.
249 || Known limitations, enhancements or remarks :
250 || Change History :
251 || Who When What
252 || (reverse chronological order - newest change first)
253 */
254 CURSOR cur_rowid IS
255 SELECT rowid
256 FROM igs_he_poous_ou_all
257 WHERE hesa_poous_ou_id = x_hesa_poous_ou_id
258 FOR UPDATE NOWAIT;
259
260 lv_rowid cur_rowid%RowType;
261
262 BEGIN
263
264 OPEN cur_rowid;
265 FETCH cur_rowid INTO lv_rowid;
266 IF (cur_rowid%FOUND) THEN
267 CLOSE cur_rowid;
268 RETURN(TRUE);
269 ELSE
270 CLOSE cur_rowid;
271 RETURN(FALSE);
272 END IF;
273
274 END get_pk_for_validation;
275
276
277 FUNCTION get_uk_for_validation (
278 x_course_cd IN VARCHAR2,
279 x_crv_version_number IN NUMBER,
280 x_cal_type IN VARCHAR2,
281 x_location_cd IN VARCHAR2,
282 x_attendance_mode IN VARCHAR2,
283 x_attendance_type IN VARCHAR2,
284 x_unit_set_cd IN VARCHAR2,
285 x_us_version_number IN NUMBER,
286 x_organization_unit IN VARCHAR2
287 ) RETURN BOOLEAN AS
288 /*
289 || Created By : [email protected]
290 || Created On : 26-JAN-2002
291 || Purpose : Validates the Unique Keys of the table.
292 || Known limitations, enhancements or remarks :
293 || Change History :
294 || Who When What
295 || (reverse chronological order - newest change first)
296 */
297 CURSOR cur_rowid IS
298 SELECT rowid
299 FROM igs_he_poous_ou_all
300 WHERE course_cd = x_course_cd
301 AND crv_version_number = x_crv_version_number
302 AND cal_type = x_cal_type
303 AND location_cd = x_location_cd
304 AND attendance_mode = x_attendance_mode
305 AND attendance_type = x_attendance_type
306 AND unit_set_cd = x_unit_set_cd
307 AND us_version_number = x_us_version_number
308 AND organization_unit = x_organization_unit
309 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
310
311 lv_rowid cur_rowid%RowType;
312
313 BEGIN
314
315 OPEN cur_rowid;
316 FETCH cur_rowid INTO lv_rowid;
317 IF (cur_rowid%FOUND) THEN
318 CLOSE cur_rowid;
319 RETURN (true);
320 ELSE
321 CLOSE cur_rowid;
322 RETURN(FALSE);
323 END IF;
324
325 END get_uk_for_validation ;
326
327
328 PROCEDURE get_fk_igs_ps_ofr_opt_all (
329 x_course_cd IN VARCHAR2,
330 x_crv_version_number IN NUMBER,
331 x_cal_type IN VARCHAR2,
332 x_location_cd IN VARCHAR2,
333 x_attendance_mode IN VARCHAR2,
334 x_attendance_type IN VARCHAR2
335 ) AS
336 /*
337 || Created By : [email protected]
338 || Created On : 9-May-2002
339 || Purpose : Validates the Foreign Keys for the table.
340 || Known limitations, enhancements or remarks :
341 || Change History :
342 || Who When What
343 || (reverse chronological order - newest change first)
344 */
345 CURSOR cur_rowid IS
346 SELECT rowid
347 FROM igs_he_poous_ou_all
348 WHERE (attendance_mode = x_attendance_type) AND
349 (attendance_type = x_attendance_mode) AND
350 (cal_type = x_cal_type) AND
351 (course_cd = x_course_cd) AND
352 (crv_version_number = x_crv_version_number) AND
353 (location_cd = x_location_cd);
354 lv_rowid cur_rowid%RowType;
355
356 BEGIN
357
358 OPEN cur_rowid;
359 FETCH cur_rowid INTO lv_rowid;
360 IF (cur_rowid%FOUND) THEN
361 CLOSE cur_rowid;
362 fnd_message.set_name ('IGS', 'IGS_HE_HPOU_COO_FK');
363 igs_ge_msg_stack.add;
364 app_exception.raise_exception;
365 RETURN;
366 END IF;
367 CLOSE cur_rowid;
368
369 END get_fk_igs_ps_ofr_opt_all;
370
371 PROCEDURE get_fk_igs_en_unit_set_all (
372 x_unit_set_cd IN VARCHAR2,
373 x_us_version_number IN NUMBER
374 ) AS
375
376 /*
377 || Created By : [email protected]
378 || Created On : 9-May-2002
379 || Purpose : Validates the Foreign Keys for the table.
380 || Known limitations, enhancements or remarks :
381 || Change History :
382 || Who When What
383 || (reverse chronological order - newest change first)
384 */
385 CURSOR cur_rowid IS
386 SELECT rowid
387 FROM igs_he_poous_ou_all
388 WHERE (unit_set_cd = x_unit_set_cd) AND
389 (us_version_number = x_us_version_number);
390 lv_rowid cur_rowid%RowType;
391
392 BEGIN
393
394 OPEN cur_rowid;
395 FETCH cur_rowid INTO lv_rowid;
396 IF (cur_rowid%FOUND) THEN
397 CLOSE cur_rowid;
398 fnd_message.set_name ('IGS', 'IGS_HE_HPOU_US_FK');
399 igs_ge_msg_stack.add;
400 app_exception.raise_exception;
401 RETURN;
402 END IF;
403 CLOSE cur_rowid;
404
405 END get_fk_igs_en_unit_set_all;
406
407 PROCEDURE get_fk_igs_ps_ofr_unit_set (
408 x_course_cd IN VARCHAR2,
409 x_version_number IN NUMBER,
410 x_cal_type IN VARCHAR2,
411 x_unit_set_cd IN VARCHAR2,
412 x_us_version_number IN NUMBER
413 ) AS
414 /*************************************************************
415 Created By :jchakrab
416 Date Created By :03-MAY-2006
417 Purpose : To be called by parent TBH to check child existence
418 Know limitations, enhancements or remarks
419 Change History
420 Who When What
421
422 (reverse chronological order - newest change first)
423 ***************************************************************/
424 CURSOR cur_rowid IS
425 SELECT rowid
426 FROM IGS_HE_POOUS_OU_ALL
427 WHERE COURSE_CD = x_course_cd
428 AND CRV_VERSION_NUMBER = x_version_number
429 AND CAL_TYPE = x_cal_type
430 AND UNIT_SET_CD = x_unit_set_cd
431 AND US_VERSION_NUMBER = x_us_version_number ;
432
433 lv_rowid cur_rowid%ROWTYPE;
434
435 BEGIN
436
437 Open cur_rowid;
438 Fetch cur_rowid INTO lv_rowid;
439 IF (cur_rowid%FOUND) THEN
440 Close cur_rowid;
441 Fnd_Message.Set_Name ('IGS', 'IGS_HE_HPUD_US_FK');
442 IGS_GE_MSG_STACK.ADD;
443 App_Exception.Raise_Exception;
444 Return;
445 END IF;
446 Close cur_rowid;
447
448 END get_fk_igs_ps_ofr_unit_set;
449
450
451 PROCEDURE before_dml (
452 p_action IN VARCHAR2,
453 x_rowid IN VARCHAR2 ,
454 x_hesa_poous_ou_id IN NUMBER ,
455 x_org_id IN NUMBER ,
456 x_course_cd IN VARCHAR2 ,
457 x_crv_version_number IN NUMBER ,
458 x_cal_type IN VARCHAR2 ,
459 x_location_cd IN VARCHAR2 ,
460 x_attendance_mode IN VARCHAR2 ,
461 x_attendance_type IN VARCHAR2 ,
462 x_unit_set_cd IN VARCHAR2 ,
463 x_us_version_number IN NUMBER ,
464 x_organization_unit IN VARCHAR2 ,
465 x_proportion IN NUMBER ,
466 x_creation_date IN DATE ,
467 x_created_by IN NUMBER ,
468 x_last_update_date IN DATE ,
469 x_last_updated_by IN NUMBER ,
470 x_last_update_login IN NUMBER
471 ) AS
472 /*
473 || Created By : [email protected]
474 || Created On : 26-JAN-2002
475 || Purpose : Initialises the columns, Checks Constraints, Calls the
476 || Trigger Handlers for the table, before any DML operation.
477 || Known limitations, enhancements or remarks :
478 || Change History :
479 || Who When What
480 || (reverse chronological order - newest change first)
481 || jbaber 17-Jan-2005 Added check_child_existence for
482 || HE355 - Org Unit Cost Center Link
483 */
484 BEGIN
485
486 set_column_values (
487 p_action,
488 x_rowid,
489 x_hesa_poous_ou_id,
490 x_org_id,
491 x_course_cd,
492 x_crv_version_number,
493 x_cal_type,
494 x_location_cd,
495 x_attendance_mode,
496 x_attendance_type,
497 x_unit_set_cd,
498 x_us_version_number,
499 x_organization_unit,
500 x_proportion,
501 x_creation_date,
502 x_created_by,
503 x_last_update_date,
504 x_last_updated_by,
505 x_last_update_login
506 );
507
508 IF (p_action = 'INSERT') THEN
509 -- Call all the procedures related to Before Insert.
510 IF ( get_pk_for_validation(
511 new_references.hesa_poous_ou_id
512 )
513 ) THEN
514 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
515 igs_ge_msg_stack.add;
516 app_exception.raise_exception;
517 END IF;
518 check_uniqueness;
519 check_parent_existance;
520 ELSIF (p_action = 'UPDATE') THEN
521 -- Call all the procedures related to Before Update.
522 check_uniqueness;
523 check_parent_existance;
524 ELSIF (p_action = 'DELETE') THEN
525 -- Call all the procedures related to Before Delete.
526 check_child_existance;
527 ELSIF (p_action = 'VALIDATE_INSERT') THEN
528 -- Call all the procedures related to Before Insert.
529 IF ( get_pk_for_validation (
530 new_references.hesa_poous_ou_id
531 )
532 ) THEN
533 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
534 igs_ge_msg_stack.add;
535 app_exception.raise_exception;
536 END IF;
537 check_uniqueness;
538 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
539 check_uniqueness;
540 ELSIF (p_action = 'VALIDATE_DELETE') THEN
541 check_child_existance;
542 END IF;
543
544 END before_dml;
545
546
547 PROCEDURE insert_row (
548 x_rowid IN OUT NOCOPY VARCHAR2,
549 x_hesa_poous_ou_id IN OUT NOCOPY NUMBER,
550 x_org_id IN NUMBER,
551 x_course_cd IN VARCHAR2,
552 x_crv_version_number IN NUMBER,
553 x_cal_type IN VARCHAR2,
554 x_location_cd IN VARCHAR2,
555 x_attendance_mode IN VARCHAR2,
556 x_attendance_type IN VARCHAR2,
557 x_unit_set_cd IN VARCHAR2,
558 x_us_version_number IN NUMBER,
559 x_organization_unit IN VARCHAR2,
560 x_proportion IN NUMBER,
561 x_mode IN VARCHAR2
562 ) AS
563 /*
564 || Created By : [email protected]
565 || Created On : 26-JAN-2002
566 || Purpose : Handles the INSERT DML logic for the table.
567 || Known limitations, enhancements or remarks :
568 || Change History :
569 || Who When What
570 || smvk 13-Feb-2002 Call to igs_ge_gen_003.get_org_id
571 || w.r.t. SWCR006
572 || (reverse chronological order - newest change first)
573 */
574 CURSOR c IS
575 SELECT rowid
576 FROM igs_he_poous_ou_all
577 WHERE hesa_poous_ou_id = x_hesa_poous_ou_id;
578
579 x_last_update_date DATE;
580 x_last_updated_by NUMBER;
581 x_last_update_login NUMBER;
582
583 BEGIN
584
585 x_last_update_date := SYSDATE;
586 IF (x_mode = 'I') THEN
587 x_last_updated_by := 1;
588 x_last_update_login := 0;
589 ELSIF (x_mode = 'R') THEN
590 x_last_updated_by := fnd_global.user_id;
591 IF (x_last_updated_by IS NULL) THEN
592 x_last_updated_by := -1;
593 END IF;
594 x_last_update_login := fnd_global.login_id;
595 IF (x_last_update_login IS NULL) THEN
596 x_last_update_login := -1;
597 END IF;
598 ELSE
599 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
600 igs_ge_msg_stack.add;
601 app_exception.raise_exception;
602 END IF;
603
604 SELECT igs_he_poous_ou_all_s.NEXTVAL
605 INTO x_hesa_poous_ou_id
606 FROM dual;
607
608 before_dml(
609 p_action => 'INSERT',
610 x_rowid => x_rowid,
611 x_hesa_poous_ou_id => x_hesa_poous_ou_id,
612 x_org_id => igs_ge_gen_003.get_org_id,
613 x_course_cd => x_course_cd,
614 x_crv_version_number => x_crv_version_number,
615 x_cal_type => x_cal_type,
616 x_location_cd => x_location_cd,
617 x_attendance_mode => x_attendance_mode,
618 x_attendance_type => x_attendance_type,
619 x_unit_set_cd => x_unit_set_cd,
620 x_us_version_number => x_us_version_number,
621 x_organization_unit => x_organization_unit,
622 x_proportion => x_proportion,
623 x_creation_date => x_last_update_date,
624 x_created_by => x_last_updated_by,
625 x_last_update_date => x_last_update_date,
626 x_last_updated_by => x_last_updated_by,
627 x_last_update_login => x_last_update_login
628 );
629
630 INSERT INTO igs_he_poous_ou_all (
631 hesa_poous_ou_id,
632 org_id,
633 course_cd,
634 crv_version_number,
635 cal_type,
636 location_cd,
637 attendance_mode,
638 attendance_type,
639 unit_set_cd,
640 us_version_number,
641 organization_unit,
642 proportion,
643 creation_date,
644 created_by,
645 last_update_date,
646 last_updated_by,
647 last_update_login
648 ) VALUES (
649 new_references.hesa_poous_ou_id,
650 new_references.org_id,
651 new_references.course_cd,
652 new_references.crv_version_number,
653 new_references.cal_type,
654 new_references.location_cd,
655 new_references.attendance_mode,
656 new_references.attendance_type,
657 new_references.unit_set_cd,
658 new_references.us_version_number,
659 new_references.organization_unit,
660 new_references.proportion,
661 x_last_update_date,
662 x_last_updated_by,
663 x_last_update_date,
664 x_last_updated_by,
665 x_last_update_login
666 );
667
668 OPEN c;
669 FETCH c INTO x_rowid;
670 IF (c%NOTFOUND) THEN
671 CLOSE c;
672 RAISE NO_DATA_FOUND;
673 END IF;
674 CLOSE c;
675
676 END insert_row;
677
678
679 PROCEDURE lock_row (
680 x_rowid IN VARCHAR2,
681 x_hesa_poous_ou_id IN NUMBER,
682 x_org_id IN NUMBER,
683 x_course_cd IN VARCHAR2,
684 x_crv_version_number IN NUMBER,
685 x_cal_type IN VARCHAR2,
686 x_location_cd IN VARCHAR2,
687 x_attendance_mode IN VARCHAR2,
688 x_attendance_type IN VARCHAR2,
689 x_unit_set_cd IN VARCHAR2,
690 x_us_version_number IN NUMBER,
691 x_organization_unit IN VARCHAR2,
692 x_proportion IN NUMBER
693 ) AS
694 /*
695 || Created By : [email protected]
696 || Created On : 26-JAN-2002
697 || Purpose : Handles the LOCK mechanism for the table.
698 || Known limitations, enhancements or remarks :
699 || Change History :
700 || Who When What
701 || smvk 13-Feb-2002 Removed org_id from cursor declaration
702 || and conditional checking w.r.t. SWCR006
703 || (reverse chronological order - newest change first)
704 */
705 CURSOR c1 IS
706 SELECT
707 course_cd,
708 crv_version_number,
709 cal_type,
710 location_cd,
711 attendance_mode,
712 attendance_type,
713 unit_set_cd,
714 us_version_number,
715 organization_unit,
716 proportion
717 FROM igs_he_poous_ou_all
718 WHERE rowid = x_rowid
719 FOR UPDATE NOWAIT;
720
721 tlinfo c1%ROWTYPE;
722
723 BEGIN
724
725 OPEN c1;
726 FETCH c1 INTO tlinfo;
727 IF (c1%notfound) THEN
728 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
729 igs_ge_msg_stack.add;
730 CLOSE c1;
731 app_exception.raise_exception;
732 RETURN;
733 END IF;
734 CLOSE c1;
735
736 IF (
737 (tlinfo.course_cd = x_course_cd)
738 AND (tlinfo.crv_version_number = x_crv_version_number)
739 AND (tlinfo.cal_type = x_cal_type)
740 AND (tlinfo.location_cd = x_location_cd)
741 AND (tlinfo.attendance_mode = x_attendance_mode)
742 AND (tlinfo.attendance_type = x_attendance_type)
743 AND (tlinfo.unit_set_cd = x_unit_set_cd)
744 AND (tlinfo.us_version_number = x_us_version_number)
745 AND (tlinfo.organization_unit = x_organization_unit)
746 AND ((tlinfo.proportion = x_proportion) OR ((tlinfo.proportion IS NULL) AND (X_proportion IS NULL)))
747 ) THEN
748 NULL;
749 ELSE
750 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
751 igs_ge_msg_stack.add;
752 app_exception.raise_exception;
753 END IF;
754
755 RETURN;
756
757 END lock_row;
758
759
760 PROCEDURE update_row (
761 x_rowid IN VARCHAR2,
762 x_hesa_poous_ou_id IN NUMBER,
763 x_org_id IN NUMBER,
764 x_course_cd IN VARCHAR2,
765 x_crv_version_number IN NUMBER,
766 x_cal_type IN VARCHAR2,
767 x_location_cd IN VARCHAR2,
768 x_attendance_mode IN VARCHAR2,
769 x_attendance_type IN VARCHAR2,
770 x_unit_set_cd IN VARCHAR2,
771 x_us_version_number IN NUMBER,
772 x_organization_unit IN VARCHAR2,
773 x_proportion IN NUMBER,
774 x_mode IN VARCHAR2
775 ) AS
776 /*
777 || Created By : [email protected]
778 || Created On : 26-JAN-2002
779 || Purpose : Handles the UPDATE DML logic for the table.
780 || Known limitations, enhancements or remarks :
781 || Change History :
782 || Who When What
783 || smvk 13-Feb-2002 Call to igs_ge_gen_003.get_org_id
784 || w.r.t. SWCR 006
785 || (reverse chronological order - newest change first)
786 */
787 x_last_update_date DATE ;
788 x_last_updated_by NUMBER;
789 x_last_update_login NUMBER;
790
791 BEGIN
792
793 x_last_update_date := SYSDATE;
794 IF (X_MODE = 'I') THEN
795 x_last_updated_by := 1;
796 x_last_update_login := 0;
797 ELSIF (x_mode = 'R') THEN
798 x_last_updated_by := fnd_global.user_id;
799 IF x_last_updated_by IS NULL THEN
800 x_last_updated_by := -1;
801 END IF;
802 x_last_update_login := fnd_global.login_id;
803 IF (x_last_update_login IS NULL) THEN
804 x_last_update_login := -1;
805 END IF;
806 ELSE
807 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
808 igs_ge_msg_stack.add;
809 app_exception.raise_exception;
810 END IF;
811
812 before_dml(
813 p_action => 'UPDATE',
814 x_rowid => x_rowid,
815 x_hesa_poous_ou_id => x_hesa_poous_ou_id,
816 x_org_id => igs_ge_gen_003.get_org_id,
817 x_course_cd => x_course_cd,
818 x_crv_version_number => x_crv_version_number,
819 x_cal_type => x_cal_type,
820 x_location_cd => x_location_cd,
821 x_attendance_mode => x_attendance_mode,
822 x_attendance_type => x_attendance_type,
823 x_unit_set_cd => x_unit_set_cd,
824 x_us_version_number => x_us_version_number,
825 x_organization_unit => x_organization_unit,
826 x_proportion => x_proportion,
827 x_creation_date => x_last_update_date,
828 x_created_by => x_last_updated_by,
829 x_last_update_date => x_last_update_date,
830 x_last_updated_by => x_last_updated_by,
831 x_last_update_login => x_last_update_login
832 );
833
834 UPDATE igs_he_poous_ou_all
835 SET
836 course_cd = new_references.course_cd,
837 crv_version_number = new_references.crv_version_number,
838 cal_type = new_references.cal_type,
839 location_cd = new_references.location_cd,
840 attendance_mode = new_references.attendance_mode,
841 attendance_type = new_references.attendance_type,
842 unit_set_cd = new_references.unit_set_cd,
843 us_version_number = new_references.us_version_number,
844 organization_unit = new_references.organization_unit,
845 proportion = new_references.proportion,
846 last_update_date = x_last_update_date,
847 last_updated_by = x_last_updated_by,
848 last_update_login = x_last_update_login
849 WHERE rowid = x_rowid;
850
851 IF (SQL%NOTFOUND) THEN
852 RAISE NO_DATA_FOUND;
853 END IF;
854
855 END update_row;
856
857
858 PROCEDURE add_row (
859 x_rowid IN OUT NOCOPY VARCHAR2,
860 x_hesa_poous_ou_id IN OUT NOCOPY NUMBER,
861 x_org_id IN NUMBER,
862 x_course_cd IN VARCHAR2,
863 x_crv_version_number IN NUMBER,
864 x_cal_type IN VARCHAR2,
865 x_location_cd IN VARCHAR2,
866 x_attendance_mode IN VARCHAR2,
867 x_attendance_type IN VARCHAR2,
868 x_unit_set_cd IN VARCHAR2,
869 x_us_version_number IN NUMBER,
870 x_organization_unit IN VARCHAR2,
871 x_proportion IN NUMBER,
872 x_mode IN VARCHAR2
873 ) AS
874 /*
875 || Created By : [email protected]
876 || Created On : 26-JAN-2002
877 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
878 || Known limitations, enhancements or remarks :
879 || Change History :
880 || Who When What
881 || (reverse chronological order - newest change first)
882 */
883 CURSOR c1 IS
884 SELECT rowid
885 FROM igs_he_poous_ou_all
886 WHERE hesa_poous_ou_id = x_hesa_poous_ou_id;
887
888 BEGIN
889
890 OPEN c1;
891 FETCH c1 INTO x_rowid;
892 IF (c1%NOTFOUND) THEN
893 CLOSE c1;
894
895 insert_row (
896 x_rowid,
897 x_hesa_poous_ou_id,
898 x_org_id,
899 x_course_cd,
900 x_crv_version_number,
901 x_cal_type,
902 x_location_cd,
903 x_attendance_mode,
904 x_attendance_type,
905 x_unit_set_cd,
906 x_us_version_number,
907 x_organization_unit,
908 x_proportion,
909 x_mode
910 );
911 RETURN;
912 END IF;
913 CLOSE c1;
914
915 update_row (
916 x_rowid,
917 x_hesa_poous_ou_id,
918 x_org_id,
919 x_course_cd,
920 x_crv_version_number,
921 x_cal_type,
922 x_location_cd,
923 x_attendance_mode,
924 x_attendance_type,
925 x_unit_set_cd,
926 x_us_version_number,
927 x_organization_unit,
928 x_proportion,
929 x_mode
930 );
931
932 END add_row;
933
934
935 PROCEDURE delete_row (
936 x_rowid IN VARCHAR2
937 ) AS
938 /*
939 || Created By : [email protected]
940 || Created On : 26-JAN-2002
941 || Purpose : Handles the DELETE DML logic for the table.
942 || Known limitations, enhancements or remarks :
943 || Change History :
944 || Who When What
945 || (reverse chronological order - newest change first)
946 */
947 BEGIN
948
949 before_dml (
950 p_action => 'DELETE',
951 x_rowid => x_rowid
952 );
953
954 DELETE FROM igs_he_poous_ou_all
955 WHERE rowid = x_rowid;
956
957 IF (SQL%NOTFOUND) THEN
958 RAISE NO_DATA_FOUND;
959 END IF;
960
961 END delete_row;
962
963
964 END igs_he_poous_ou_all_pkg;