[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_REF_CD_PKG
Source
1 PACKAGE BODY igs_ps_ref_cd_pkg AS
2 /* $Header: IGSPI29B.pls 115.9 2003/05/20 12:48:52 sarakshi ship $ */
3 /* CAHNGE HISTORY
4 WHO WHEN WAHT
5 ayedubat 11-JUN-2001 modified the BeforeRowInsertUpdateDelete1 to add a
6 new validation according to the DLD,PSP001-US */
7
8 l_rowid VARCHAR2(25);
9 old_references igs_ps_ref_cd%ROWTYPE;
10 new_references igs_ps_ref_cd%ROWTYPE;
11
12 PROCEDURE set_column_values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2 DEFAULT NULL,
15 x_course_cd IN VARCHAR2 DEFAULT NULL,
16 x_version_number IN NUMBER DEFAULT NULL,
17 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
18 x_reference_cd IN VARCHAR2 DEFAULT NULL,
19 x_description IN VARCHAR2 DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM igs_ps_ref_cd
30 WHERE ROWID = x_rowid;
31
32 BEGIN
33
34 l_rowid := x_rowid;
35
36 -- Code for setting the Old and New Reference Values.
37 -- Populate Old Values.
38 OPEN cur_old_ref_values;
39 FETCH cur_old_ref_values INTO old_references;
40 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
41 CLOSE cur_old_ref_values;
42 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
43 igs_ge_msg_stack.add;
44 app_exception.raise_exception;
45 RETURN;
46 END IF;
47 CLOSE cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.course_cd := x_course_cd;
51 new_references.version_number := x_version_number;
52 new_references.reference_cd_type := x_reference_cd_type;
53 new_references.reference_cd := x_reference_cd;
54 new_references.description := x_description;
55 IF (p_action = 'UPDATE') THEN
56 new_references.creation_date := old_references.creation_date;
57 new_references.created_by := old_references.created_by;
58 ELSE
59 new_references.creation_date := x_creation_date;
60 new_references.created_by := x_created_by;
61 END IF;
62 new_references.last_update_date := x_last_update_date;
63 new_references.last_updated_by := x_last_updated_by;
64 new_references.last_update_login := x_last_update_login;
65
66 END set_column_values;
67
68 -- Trigger description :-
69 -- "OSS_TST".trg_crfc_br_iud
70 -- BEFORE INSERT OR DELETE OR UPDATE
71 -- ON IGS_PS_REF_CD
72 -- FOR EACH ROW
73
74 PROCEDURE beforerowinsertupdatedelete1(
75 p_inserting IN BOOLEAN DEFAULT FALSE,
76 p_updating IN BOOLEAN DEFAULT FALSE,
77 p_deleting IN BOOLEAN DEFAULT FALSE
78 ) AS
79 v_description igs_ps_ref_cd.description%TYPE;
80 v_message_name VARCHAR2(30);
81 v_course_cd igs_ps_ref_cd.course_cd%TYPE;
82 v_version_number igs_ps_ref_cd.version_number%TYPE;
83 BEGIN
84 -- Set variables.
85 IF p_deleting THEN
86 v_course_cd := old_references.course_cd;
87 v_version_number := old_references.version_number;
88 ELSE -- p_inserting or p_updating
89 v_course_cd := new_references.course_cd;
90 v_version_number := new_references.version_number;
91 END IF;
92 -- Validate the insert/update/delete.
93 IF igs_ps_val_crs.crsp_val_iud_crv_dtl (
94 v_course_cd,
95 v_version_number,
96 v_message_name) = FALSE THEN
97 fnd_message.set_name('IGS',v_message_name);
98 igs_ge_msg_stack.add;
99 app_exception.raise_exception;
100 END IF;
101 -- Validate reference code type.
102 IF p_inserting OR
103 (p_updating AND
104 (old_references.reference_cd_type <> new_references.reference_cd_type)) THEN
105 IF igs_ps_val_crfc.crsp_val_ref_cd_type(
106 new_references.reference_cd_type,
107 v_message_name) = FALSE THEN
108 fnd_message.set_name('IGS',v_message_name);
109 igs_ge_msg_stack.add;
110 app_exception.raise_exception;
111 END IF;
112 END IF;
113 IF p_updating THEN
114 IF NVL(old_references.description,'NULL') <>
115 NVL(new_references.description,'NULL') THEN
116 SELECT decode(NVL(old_references.description,'NULL'),
117 NVL(new_references.description,'NULL'),
118 NULL,old_references.description)
119 INTO v_description
120 FROM dual;
121 -- Create history record for update
122 igs_ps_gen_007.crsp_ins_crc_hist(
123 old_references.course_cd,
124 old_references.version_number,
125 old_references.reference_cd_type,
126 old_references.reference_cd,
127 old_references.last_update_date,
128 new_references.last_update_date,
129 old_references.last_updated_by,
130 v_description);
131 END IF;
132 END IF;
133 IF p_deleting THEN
134 IF igs_ps_val_atl.chk_mandatory_ref_cd(old_references.reference_cd_type) THEN
135 fnd_message.set_name ('IGS', 'IGS_PS_REF_CD_MANDATORY');
136 igs_ge_msg_stack.add;
137 app_exception.raise_exception;
138 END IF;
139 -- Create history record for deletion
140 igs_ps_gen_007.crsp_ins_crc_hist(
141 old_references.course_cd,
142 old_references.version_number,
143 old_references.reference_cd_type,
144 old_references.reference_cd,
145 old_references.last_update_date,
146 SYSDATE,
147 old_references.last_updated_by,
148 old_references.description);
149 END IF;
150
151 END beforerowinsertupdatedelete1;
152
153 -- Trigger description :-
154 -- "OSS_TST".trg_crfc_ar_iu
155 -- AFTER INSERT OR UPDATE
156 -- ON IGS_PS_REF_CD
157 -- FOR EACH ROW
158
159 PROCEDURE afterrowinsertupdate2(
160 p_inserting IN BOOLEAN DEFAULT FALSE,
161 p_updating IN BOOLEAN DEFAULT FALSE,
162 p_deleting IN BOOLEAN DEFAULT FALSE
163 ) AS
164 v_message_name VARCHAR2(30);
165 BEGIN
166 IF p_inserting THEN
167 NULL;
168 END IF;
169
170 END afterrowinsertupdate2;
171
172 -- Trigger description :-
173 -- "OSS_TST".trg_crfc_as_iu
174 -- AFTER INSERT OR UPDATE
175 -- ON IGS_PS_REF_CD
176
177
178 PROCEDURE check_uniqueness AS
179 BEGIN
180 IF get_uk_for_validation(
181 new_references.course_cd,
182 new_references.version_number,
183 new_references.reference_cd_type
184 )THEN
185 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
186 igs_ge_msg_stack.add;
187 app_exception.raise_exception;
188 END IF;
189 END check_uniqueness;
190
191
192 PROCEDURE check_constraints (
193 column_name IN VARCHAR2 DEFAULT NULL,
194 column_value IN VARCHAR2 DEFAULT NULL
195 ) AS
196 BEGIN
197 IF column_name IS NULL THEN
198 NULL;
199 ELSIF UPPER(column_name) = 'COURSE_CD' THEN
200 new_references.course_cd := column_value;
201 ELSIF UPPER(column_name) = 'REFERENCE_CD' THEN
202 new_references.reference_cd := column_value;
203 ELSIF UPPER(column_name) = 'REFERENCE_CD_TYPE' THEN
204 new_references.reference_cd_type := column_value;
205 END IF;
206 IF UPPER(column_name)= 'COURSE_CD' OR
207 column_name IS NULL THEN
208 IF new_references.course_cd <> UPPER(new_references.course_cd) THEN
209 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
210 igs_ge_msg_stack.add;
211 app_exception.raise_exception;
212 END IF;
213 END IF;
214
215 IF UPPER(column_name)= 'REFERENCE_CD' OR
216 column_name IS NULL THEN
217 IF new_references.reference_cd <> UPPER(new_references.reference_cd) THEN
218 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
219 igs_ge_msg_stack.add;
220 app_exception.raise_exception;
221 END IF;
222 END IF;
223
224 IF UPPER(column_name)= 'REFERENCE_CD_TYPE' OR
225 column_name IS NULL THEN
226 IF new_references.reference_cd_type <> UPPER(new_references.reference_cd_type) THEN
227 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
228 igs_ge_msg_stack.add;
229 app_exception.raise_exception;
230 END IF;
231 END IF;
232
233 END check_constraints;
234
235 PROCEDURE check_parent_existance AS
236 BEGIN
237
238 IF (((old_references.course_cd = new_references.course_cd) AND
239 (old_references.version_number = new_references.version_number)) OR
240 ((new_references.course_cd IS NULL) OR
241 (new_references.version_number IS NULL))) THEN
242 NULL;
243 ELSE
244 IF NOT igs_ps_ver_pkg.get_pk_for_validation (
245 new_references.course_cd,
246 new_references.version_number
247 )THEN
248 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
249 igs_ge_msg_stack.add;
250 app_exception.raise_exception;
251 END IF;
252 END IF;
253 IF (((old_references.reference_cd_type = new_references.reference_cd_type)) OR
254
255 ((new_references.reference_cd_type IS NULL))) THEN
256 NULL;
257 ELSE
258 IF NOT igs_ge_ref_cd_type_pkg.get_pk_for_validation (
259 new_references.reference_cd_type )THEN
260 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 END IF;
264 END IF;
265 END check_parent_existance;
266
267 FUNCTION get_pk_for_validation (
268 x_course_cd IN VARCHAR2,
269 x_version_number IN NUMBER,
270 x_reference_cd_type IN VARCHAR2,
271 x_reference_cd IN VARCHAR2
272 )RETURN BOOLEAN
273 AS
274
275 CURSOR cur_rowid IS
276 SELECT ROWID
277 FROM igs_ps_ref_cd
278 WHERE course_cd = x_course_cd
279 AND version_number = x_version_number
280 AND reference_cd_type = x_reference_cd_type
281 AND reference_cd = x_reference_cd
282 FOR UPDATE NOWAIT;
283
284 lv_rowid cur_rowid%ROWTYPE;
285
286 BEGIN
287
288 OPEN cur_rowid;
289 FETCH cur_rowid INTO lv_rowid;
290 IF (cur_rowid%FOUND) THEN
291 CLOSE cur_rowid;
292 RETURN(TRUE);
293 ELSE
294 CLOSE cur_rowid;
295 RETURN(FALSE);
296 END IF;
297 END get_pk_for_validation;
298
299 FUNCTION get_uk_for_validation (
300 x_course_cd IN VARCHAR2,
301 x_version_number IN NUMBER,
302 x_reference_cd_type IN VARCHAR2
303 )RETURN BOOLEAN
304 AS
305
306 CURSOR cur_rowid IS
307 SELECT ROWID
308 FROM igs_ps_ref_cd
309 WHERE course_cd = x_course_cd
310 AND version_number = x_version_number
311 AND reference_cd_type = x_reference_cd_type
312 AND (l_rowid IS NULL OR ROWID <> l_rowid)
313 FOR UPDATE NOWAIT;
314
315 lv_rowid cur_rowid%ROWTYPE;
316
317 BEGIN
318
319 OPEN cur_rowid;
320 FETCH cur_rowid INTO lv_rowid;
321 IF (cur_rowid%FOUND) THEN
322 CLOSE cur_rowid;
323 RETURN(TRUE);
324 ELSE
325 CLOSE cur_rowid;
326 RETURN(FALSE);
327 END IF;
328 END get_uk_for_validation;
329
330 PROCEDURE get_fk_igs_ps_ver (
331 x_course_cd IN VARCHAR2,
332 x_version_number IN NUMBER
333 ) AS
334
335 CURSOR cur_rowid IS
336 SELECT ROWID
337 FROM igs_ps_ref_cd
338 WHERE course_cd = x_course_cd
339 AND version_number = x_version_number ;
340
341 lv_rowid cur_rowid%ROWTYPE;
342
343 BEGIN
344
345 OPEN cur_rowid;
346 FETCH cur_rowid INTO lv_rowid;
347 IF (cur_rowid%FOUND) THEN
348 CLOSE cur_rowid;
349 fnd_message.set_name ('IGS', 'IGS_PS_CRFC_CRV_FK');
350 igs_ge_msg_stack.add;
351 app_exception.raise_exception;
352 RETURN;
353 END IF;
354 CLOSE cur_rowid;
355
356 END get_fk_igs_ps_ver;
357
358 PROCEDURE get_fk_igs_ge_ref_cd_type (
359 x_reference_cd_type IN VARCHAR2
360 ) AS
361
362 CURSOR cur_rowid IS
363 SELECT ROWID
364 FROM igs_ps_ref_cd
365 WHERE reference_cd_type = x_reference_cd_type ;
366
367 lv_rowid cur_rowid%ROWTYPE;
368
369 BEGIN
370
371 OPEN cur_rowid;
372 FETCH cur_rowid INTO lv_rowid;
373 IF (cur_rowid%FOUND) THEN
374 CLOSE cur_rowid;
375 fnd_message.set_name ('IGS', 'IGS_PS_CRFC_RCT_FK');
376 igs_ge_msg_stack.add;
377 app_exception.raise_exception;
378 RETURN;
379 END IF;
380 CLOSE cur_rowid;
381
382 END get_fk_igs_ge_ref_cd_type;
383
384 PROCEDURE get_ufk_igs_ge_ref_cd (
385 x_reference_cd_type IN VARCHAR2,
386 x_reference_cd IN VARCHAR2
387 ) AS
388
389 /*************************************************************
390 Created By :sarakshi
391 Date Created By :7-May-2003
392 Purpose :
393 Know limitations, enhancements or remarks
394 Change History
395 Who When What
396
397 (reverse chronological order - newest change first)
398 ***************************************************************/
399
400 CURSOR cur_rowid IS
401 SELECT ROWID
402 FROM igs_ps_ref_cd
403 WHERE reference_cd_type = x_reference_cd_type
404 AND reference_cd = x_reference_cd ;
405
406 lv_rowid cur_rowid%ROWTYPE;
407
408 BEGIN
409
410 OPEN cur_rowid;
411 FETCH cur_rowid INTO lv_rowid;
412 IF (cur_rowid%FOUND) THEN
413 CLOSE cur_rowid;
414 fnd_message.set_name ('IGS', 'IGS_PS_CRFC_RC_FK');
415 igs_ge_msg_stack.add;
416 app_exception.raise_exception;
417 RETURN;
418 END IF;
419 CLOSE cur_rowid;
420
421 END get_ufk_igs_ge_ref_cd;
422
423
424 PROCEDURE before_dml (
425 p_action IN VARCHAR2,
426 x_rowid IN VARCHAR2 DEFAULT NULL,
427 x_course_cd IN VARCHAR2 DEFAULT NULL,
431 x_description IN VARCHAR2 DEFAULT NULL,
428 x_version_number IN NUMBER DEFAULT NULL,
429 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
430 x_reference_cd IN VARCHAR2 DEFAULT NULL,
432 x_creation_date IN DATE DEFAULT NULL,
433 x_created_by IN NUMBER DEFAULT NULL,
434 x_last_update_date IN DATE DEFAULT NULL,
435 x_last_updated_by IN NUMBER DEFAULT NULL,
436 x_last_update_login IN NUMBER DEFAULT NULL
437 ) AS
438 BEGIN
439
440 set_column_values (
441 p_action,
442 x_rowid,
443 x_course_cd,
444 x_version_number,
445 x_reference_cd_type,
446 x_reference_cd,
447 x_description,
448 x_creation_date,
449 x_created_by,
450 x_last_update_date,
451 x_last_updated_by,
452 x_last_update_login
453 );
454
455 IF (p_action = 'INSERT') THEN
456 -- Call all the procedures related to Before Insert.
457 beforerowinsertupdatedelete1 ( p_inserting => TRUE );
458 IF get_pk_for_validation(
459 new_references.course_cd ,
460 new_references.version_number ,
461 new_references.reference_cd_type ,
462 new_references.reference_cd
463 ) THEN
464 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
465 igs_ge_msg_stack.add;
466 app_exception.raise_exception;
467 END IF;
468 check_uniqueness;
469 check_constraints;
470 check_parent_existance;
471 ELSIF (p_action = 'UPDATE') THEN
472 -- Call all the procedures related to Before Update.
473 beforerowinsertupdatedelete1 ( p_updating => TRUE );
474 check_uniqueness;
475 check_constraints;
476 check_parent_existance;
477 ELSIF (p_action = 'DELETE') THEN
478 -- Call all the procedures related to Before Delete.
479 beforerowinsertupdatedelete1 ( p_deleting => TRUE );
480 ELSIF (p_action = 'VALIDATE_INSERT') THEN
481 IF get_pk_for_validation(
482 new_references.course_cd ,
483 new_references.version_number ,
484 new_references.reference_cd_type ,
485 new_references.reference_cd ) THEN
486 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
487 igs_ge_msg_stack.add;
488 app_exception.raise_exception;
489 END IF;
490 check_uniqueness;
491 check_constraints;
492 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
493 check_uniqueness;
494 check_constraints;
495 END IF;
496
497 l_rowid:=NULL;
498
499 END before_dml;
500
501 PROCEDURE after_dml (
502 p_action IN VARCHAR2,
503 x_rowid IN VARCHAR2
504 ) AS
505 BEGIN
506
507 l_rowid := x_rowid;
508
509 IF (p_action = 'INSERT') THEN
510 -- Call all the procedures related to After Insert.
511 afterrowinsertupdate2 ( p_inserting => TRUE );
512 ELSIF (p_action = 'UPDATE') THEN
513 -- Call all the procedures related to After Update.
514 afterrowinsertupdate2 ( p_updating => TRUE );
515 END IF;
516
517 l_rowid:=NULL;
518
519 END after_dml;
520
521 PROCEDURE insert_row (
522 x_rowid IN OUT NOCOPY VARCHAR2,
523 x_course_cd IN VARCHAR2,
524 x_version_number IN NUMBER,
525 x_reference_cd IN VARCHAR2,
526 x_reference_cd_type IN VARCHAR2,
527 x_description IN VARCHAR2,
528 x_mode IN VARCHAR2 DEFAULT 'R'
529 ) AS
530 CURSOR c IS SELECT ROWID FROM igs_ps_ref_cd
531 WHERE course_cd = x_course_cd
532 AND version_number = x_version_number
533 AND reference_cd = x_reference_cd
534 AND reference_cd_type = x_reference_cd_type;
535 x_last_update_date DATE;
536 x_last_updated_by NUMBER;
537 x_last_update_login NUMBER;
538 BEGIN
539 x_last_update_date := SYSDATE;
540 IF(x_mode = 'I') THEN
541 x_last_updated_by := 1;
542 x_last_update_login := 0;
543 ELSIF (x_mode = 'R') THEN
544 x_last_updated_by := fnd_global.user_id;
545 IF x_last_updated_by IS NULL THEN
546 x_last_updated_by := -1;
547 END IF;
548 x_last_update_login :=fnd_global.login_id;
549 IF x_last_update_login IS NULL THEN
550 x_last_update_login := -1;
551 END IF;
552 ELSE
553 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
554 igs_ge_msg_stack.add;
555 app_exception.raise_exception;
556 END IF;
557 before_dml (
558 p_action => 'INSERT',
559 x_rowid => x_rowid,
560 x_course_cd => x_course_cd,
561 x_version_number => x_version_number,
562 x_reference_cd_type => x_reference_cd_type,
563 x_reference_cd => x_reference_cd,
564 x_description => x_description,
565 x_creation_date => x_last_update_date ,
566 x_created_by => x_last_updated_by ,
567 x_last_update_date => x_last_update_date ,
568 x_last_updated_by => x_last_updated_by ,
569 x_last_update_login => x_last_update_login
570 );
571
572 INSERT INTO igs_ps_ref_cd (
573 course_cd,
577 description,
574 version_number,
575 reference_cd_type,
576 reference_cd,
578 creation_date,
579 created_by,
580 last_update_date,
581 last_updated_by,
582 last_update_login)
583 VALUES (
584 new_references.course_cd,
585 new_references.version_number,
586 new_references.reference_cd_type,
587 new_references.reference_cd,
588 new_references.description,
589 x_last_update_date,
590 x_last_updated_by,
591 x_last_update_date,
592 x_last_updated_by,
593 x_last_update_login
594 );
595 OPEN c;
596 FETCH c INTO x_rowid;
597 IF (c%NOTFOUND) THEN
598 CLOSE c;
599 RAISE NO_DATA_FOUND;
600 END IF;
601 CLOSE c;
602 after_dml (
603 p_action => 'INSERT',
604 x_rowid => x_rowid
605 );
606 END insert_row;
607
608 PROCEDURE lock_row (
609 x_rowid IN VARCHAR2,
610 x_course_cd IN VARCHAR2,
611 x_version_number IN NUMBER,
612 x_reference_cd IN VARCHAR2,
613 x_reference_cd_type IN VARCHAR2,
614 x_description IN VARCHAR2
615 ) AS
616 CURSOR c1 IS SELECT
617 description
618 FROM igs_ps_ref_cd
619 WHERE ROWID = x_rowid
620 FOR UPDATE NOWAIT;
621 tlinfo c1%ROWTYPE;
622
623 BEGIN
624 OPEN c1;
625 FETCH c1 INTO tlinfo;
626 IF (c1%NOTFOUND) THEN
627 CLOSE c1;
628 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
629 igs_ge_msg_stack.add;
630 app_exception.raise_exception;
631 RETURN;
632 END IF;
633 CLOSE c1;
634
635 IF ( ((tlinfo.description = x_description)
636 OR ((tlinfo.description IS NULL)
637 AND (x_description IS NULL)))
638 ) THEN
639 NULL;
640 ELSE
641 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
642 igs_ge_msg_stack.add;
643 app_exception.raise_exception;
644 END IF;
645 RETURN;
646 END lock_row;
647
648 PROCEDURE update_row (
649 x_rowid IN VARCHAR2,
650 x_course_cd IN VARCHAR2,
651 x_version_number IN NUMBER,
652 x_reference_cd IN VARCHAR2,
653 x_reference_cd_type IN VARCHAR2,
654 x_description IN VARCHAR2,
655 x_mode IN VARCHAR2 DEFAULT 'R'
656 ) AS
657 x_last_update_date DATE;
658 x_last_updated_by NUMBER;
659 x_last_update_login NUMBER;
660 BEGIN
661 x_last_update_date := SYSDATE;
662 IF(x_mode = 'I') THEN
663 x_last_updated_by := 1;
664 x_last_update_login := 0;
665 ELSIF (x_mode = 'R') THEN
666 x_last_updated_by := fnd_global.user_id;
667 IF x_last_updated_by IS NULL THEN
668 x_last_updated_by := -1;
669 END IF;
670 x_last_update_login :=fnd_global.login_id;
671 IF x_last_update_login IS NULL THEN
672 x_last_update_login := -1;
673 END IF;
674 ELSE
675 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
676 igs_ge_msg_stack.add;
677 app_exception.raise_exception;
678 END IF;
679 before_dml (
680 p_action => 'UPDATE',
681 x_rowid => x_rowid,
682 x_course_cd => x_course_cd,
683 x_version_number => x_version_number,
684 x_reference_cd_type => x_reference_cd_type,
685 x_reference_cd => x_reference_cd,
686 x_description => x_description,
687 x_creation_date => x_last_update_date ,
688 x_created_by => x_last_updated_by ,
689 x_last_update_date => x_last_update_date ,
690 x_last_updated_by => x_last_updated_by ,
691 x_last_update_login => x_last_update_login
692 );
693
694 UPDATE igs_ps_ref_cd SET
695 course_cd = new_references.course_cd,
696 version_number = new_references.version_number,
697 reference_cd_type = new_references.reference_cd_type,
698 reference_cd = new_references.reference_cd,
699 description = new_references.description,
700 last_update_date = x_last_update_date,
701 last_updated_by = x_last_updated_by,
702 last_update_login = x_last_update_login
703 WHERE ROWID = x_rowid ;
704 IF (SQL%NOTFOUND) THEN
705 RAISE NO_DATA_FOUND;
706 END IF;
707 after_dml (
708 p_action => 'UPDATE',
709 x_rowid => x_rowid);
710
711 END update_row;
712
713 PROCEDURE add_row (
714 x_rowid IN OUT NOCOPY VARCHAR2,
715 x_course_cd IN VARCHAR2,
716 x_version_number IN NUMBER,
717 x_reference_cd IN VARCHAR2,
718 x_reference_cd_type IN VARCHAR2,
719 x_description IN VARCHAR2,
720 x_mode IN VARCHAR2 DEFAULT 'R'
721 ) AS
722 CURSOR c1 IS SELECT ROWID FROM igs_ps_ref_cd
723 WHERE course_cd = x_course_cd
724 AND version_number = x_version_number
725 AND reference_cd = x_reference_cd
726 AND reference_cd_type = x_reference_cd_type ;
727 BEGIN
728 OPEN c1;
729 FETCH c1 INTO x_rowid;
730 IF (c1%NOTFOUND) THEN
731 CLOSE c1;
732 insert_row (
736 x_reference_cd,
733 x_rowid,
734 x_course_cd,
735 x_version_number,
737 x_reference_cd_type,
738 x_description,
739 x_mode);
740 RETURN;
741 END IF;
742 CLOSE c1;
743 update_row (
744 x_rowid,
745 x_course_cd,
746 x_version_number,
747 x_reference_cd,
748 x_reference_cd_type,
749 x_description,
750 x_mode);
751 END add_row;
752
753 PROCEDURE delete_row (
754 x_rowid IN VARCHAR2
755 ) AS
756 BEGIN
757 before_dml (
758 p_action => 'DELETE',
759 x_rowid => x_rowid
760 );
761 DELETE FROM igs_ps_ref_cd
762 WHERE ROWID = x_rowid;
763 IF (SQL%NOTFOUND) THEN
764 RAISE NO_DATA_FOUND;
765 END IF;
766 after_dml (
767 p_action => 'DELETE',
768 x_rowid => x_rowid);
769 END delete_row;
770
771 END igs_ps_ref_cd_pkg;