[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_CATLG_NOTES_PKG
Source
1 PACKAGE BODY igs_ps_catlg_notes_pkg AS
2 /* $Header: IGSPI0QB.pls 115.11 2002/11/29 01:59:33 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_catlg_notes_ALL%RowType;
6 new_references igs_ps_catlg_notes_ALL%RowType;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_catalog_note_id IN NUMBER DEFAULT NULL,
12 x_catalog_version_id IN NUMBER DEFAULT NULL,
13 x_note_type_id IN NUMBER DEFAULT NULL,
14 x_create_date IN DATE DEFAULT NULL,
15 x_end_date IN DATE DEFAULT NULL,
16 x_sequence IN NUMBER DEFAULT NULL,
17 x_note_text IN VARCHAR2 DEFAULT NULL,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL,
23 X_ORG_ID IN NUMBER DEFAULT NULL
24 ) AS
25
26 /*=======================================================================+
27 |
28 | Created By : ssuri
29 |
30 | Date Created By : 10-MAY-2000
31 |
32 | Purpose : NEW TABLE
33 |
34 | Know limitations, enhancements or remarks
35 |
36 | Change History
37 |
38 | Who When What
39 |
40 |
41 |(reverse chronological order - newest change first)
42 *=======================================================================*/
43
44 CURSOR cur_old_ref_values IS
45 SELECT *
46 FROM IGS_PS_CATLG_NOTES_ALL
47 WHERE rowid = x_rowid;
48
49 BEGIN
50
51 l_rowid := x_rowid;
52
53 -- Code for setting the Old and New Reference Values.
54 -- Populate Old Values.
55 OPEN cur_old_ref_values;
56 FETCH cur_old_ref_values INTO old_references;
57 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
58 CLOSE cur_old_ref_values;
59 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
60 IGS_GE_MSG_STACK.ADD;
61 APP_EXCEPTION.RAISE_EXCEPTION;
62 RETURN;
63 END IF;
64 CLOSE cur_old_ref_values;
65
66 -- Populate New Values.
67 new_references.catalog_note_id := x_catalog_note_id;
68 new_references.catalog_version_id := x_catalog_version_id;
69 new_references.note_type_id := x_note_type_id;
70 new_references.create_date := x_create_date;
71 new_references.end_date := x_end_date;
72 new_references.sequence := x_sequence;
73 new_references.note_text := x_note_text;
74 new_references.org_id:=x_org_id;
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 new_references.last_update_date := x_last_update_date;
83 new_references.last_updated_by := x_last_updated_by;
84 new_references.last_update_login:= x_last_update_login;
85
86 END set_column_values;
87
88 PROCEDURE check_constraints (
89 column_name IN VARCHAR2 DEFAULT NULL,
90 column_value IN VARCHAR2 DEFAULT NULL ) AS
91
92 /*=======================================================================+
93 |
94 | Created By : ssuri
95 |
96 | Date Created By : 10-MAY-2000
97 |
98 | Purpose : NEW TABLE
99 |
100 | Know limitations, enhancements or remarks
101 |
102 | Change History
103 |
104 | Who When What
105 | vvutukur 12-MAR-2002 Modified 1st two IF conditions in FOR loop to
106 | fix bug:2070575 to check create and end dates
107 | properly according to business rules without
108 | overlapping.
109 |(reverse chronological order - newest change first)
110 *=======================================================================*/
111
112 CURSOR c_date is
113 SELECT create_date,end_date
114 FROM igs_ps_catlg_notes_all
115 WHERE catalog_version_id = new_references.catalog_version_id and
116 note_type_id = new_references.note_type_id and
117 (
118 (l_rowid is not null AND
119 rowid <> l_rowid)
120 OR
121 (l_rowid is null)
122 )
123 ORDER BY create_date;
124
125 BEGIN
126
127 IF new_references.create_date IS NOT NULL AND new_references.end_date IS NOT NULL THEN
128 IF new_references.create_date> new_references.end_date THEN
129 FND_MESSAGE.SET_NAME('IGS','IGS_PS_CD_GT_ED');
130 IGS_GE_MSG_STACK.ADD;
131 APP_EXCEPTION.RAISE_EXCEPTION;
132 END IF;
133 END IF;
134
135 FOR lv_date_rec in c_date LOOP
136 IF new_references.end_date IS NULL THEN --main
137 IF lv_date_rec.end_date IS NULL THEN
138 FND_MESSAGE.SET_NAME('IGS','IGS_PS_ED_VAL');
139 IGS_GE_MSG_STACK.ADD;
140 APP_EXCEPTION.RAISE_EXCEPTION;
141 ELSIF new_references.create_date < lv_date_rec.end_date THEN
142 FND_MESSAGE.SET_NAME('IGS','IGS_PS_CREATE_DT');
143 IGS_GE_MSG_STACK.ADD;
144 APP_EXCEPTION.RAISE_EXCEPTION;
145 END IF;
146 ELSE
147 IF lv_date_rec.end_date IS NULL THEN --inner
148 IF new_references.end_date >= lv_date_rec.create_date THEN
149 FND_MESSAGE.SET_NAME('IGS','IGS_PS_ED_VAL');
150 IGS_GE_MSG_STACK.ADD;
151 APP_EXCEPTION.RAISE_EXCEPTION;
152 END IF;
153 ELSE
154 IF (new_references.create_date < lv_date_rec.create_date AND
155 new_references.end_date > lv_date_rec.end_date) THEN
156 FND_MESSAGE.SET_NAME('IGS','IGS_PS_DT_RANGE');
157 IGS_GE_MSG_STACK.ADD;
158 APP_EXCEPTION.RAISE_EXCEPTION;
159 END IF;
160 IF((new_references.create_date BETWEEN lv_date_rec.create_date AND lv_date_rec.end_date) OR
161 (new_references.end_date BETWEEN lv_date_rec.create_date AND lv_date_rec.end_date)) THEN
162 FND_MESSAGE.SET_NAME('IGS','IGS_PS_DT_RANGE');
163 IGS_GE_MSG_STACK.ADD;
164 APP_EXCEPTION.RAISE_EXCEPTION;
165 END IF;
166 END IF; --inner
167 END IF; --main
168 END LOOP;
169
170 END check_constraints;
171
172 PROCEDURE check_uniqueness AS
173
174 /*=======================================================================+
175 |
176 | Created By : ssuri
177 |
178 | Date Created By : 10-MAY-2000
179 |
180 | Purpose : NEW TABLE
181 |
182 | Know limitations, enhancements or remarks
183 |
184 | Change History
185 |
186 | Who When What
187 |
188 |
189 |(reverse chronological order - newest change first)
190 *=======================================================================*/
191
192 begin
193 IF get_uk_for_validation (
194 new_references.catalog_version_id
195 ,new_references.note_type_id
196 ,new_references.sequence
197 ) THEN
198 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
199 IGS_GE_MSG_STACK.ADD;
200 APP_EXCEPTION.RAISE_EXCEPTION;
201 END IF;
202 END check_uniqueness ;
203
204 PROCEDURE check_parent_existance AS
205
206 /*=======================================================================+
207 |
208 | Created By : ssuri
209 |
210 | Date Created By : 10-MAY-2000
211 |
212 | Purpose : NEW TABLE
213 |
214 | Know limitations, enhancements or remarks
215 |
216 | Change History
217 |
218 | Who When What
219 |
220 |
221 |(reverse chronological order - newest change first)
222 *=======================================================================*/
223
224
225 BEGIN
226
227 IF (((old_references.catalog_version_id = new_references.catalog_version_id)) OR
228 ((new_references.catalog_version_id IS NULL))) THEN
229 NULL;
230 ELSIF NOT Igs_Ps_Catlg_Vers_Pkg.Get_PK_For_Validation (
231 new_references.catalog_version_id
232 ) THEN
233 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
234 IGS_GE_MSG_STACK.ADD;
235 APP_EXCEPTION.RAISE_EXCEPTION;
236 END IF;
237
238 IF (((old_references.note_type_id = new_references.note_type_id)) OR
239 ((new_references.note_type_id IS NULL))) THEN
240 NULL;
241 ELSIF NOT igs_ps_note_types_pkg.get_pk_for_validation (
242 new_references.note_type_id
243 ) THEN
244 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
245 IGS_GE_MSG_STACK.ADD;
246 APP_EXCEPTION.RAISE_EXCEPTION;
247 END IF;
248
249 END check_parent_existance;
250
251 FUNCTION Get_PK_For_Validation (
252 x_catalog_note_id IN NUMBER
253 ) RETURN BOOLEAN AS
254 /*=======================================================================+
255 |
256 | Created By : ssuri
257 |
258 | Date Created By : 10-MAY-2000
259 |
260 | Purpose : NEW TABLE
261 |
262 | Know limitations, enhancements or remarks
263 |
264 | Change History
265 |
266 | Who When What
267 |
268 |
269 |(reverse chronological order - newest change first)
270 *=======================================================================*/
271
272
273 CURSOR cur_rowid IS
274 SELECT rowid
275 FROM igs_ps_catlg_notes_ALL
276 WHERE catalog_note_id = x_catalog_note_id
277 FOR UPDATE NOWAIT;
278
279 lv_rowid cur_rowid%RowType;
280
281 BEGIN
282
283 Open cur_rowid;
284 Fetch cur_rowid INTO lv_rowid;
285 IF (cur_rowid%FOUND) THEN
286 Close cur_rowid;
287 Return(TRUE);
288 ELSE
289 Close cur_rowid;
290 Return(FALSE);
291 END IF;
292 END Get_PK_For_Validation;
293
294 FUNCTION Get_UK_For_Validation (
295 x_catalog_version_id IN NUMBER,
296 x_note_type_id IN NUMBER,
297 x_sequence IN NUMBER
298 ) RETURN BOOLEAN AS
299
300 /*=======================================================================+
301 |
302 | Created By : ssuri
303 |
304 | Date Created By : 10-MAY-2000
305 |
306 | Purpose : NEW TABLE
307 |
308 | Know limitations, enhancements or remarks
309 |
310 | Change History
311 |
312 | Who When What
313 |
314 |
315 |(reverse chronological order - newest change first)
316 *=======================================================================*/
317
318
319 CURSOR cur_rowid IS
320 SELECT rowid
321 FROM igs_ps_catlg_notes_ALL
322 WHERE catalog_version_id = x_catalog_version_id
323 AND note_type_id = x_note_type_id
324 AND sequence = x_sequence and ((l_rowid is null) or (rowid <> l_rowid))
325
326 ;
327 lv_rowid cur_rowid%RowType;
328
329 BEGIN
330
331 Open cur_rowid;
332 Fetch cur_rowid INTO lv_rowid;
333 IF (cur_rowid%FOUND) THEN
334 Close cur_rowid;
335 return (true);
336 ELSE
337 close cur_rowid;
338 return(false);
339 END IF;
340 END Get_UK_For_Validation ;
341
342 PROCEDURE Get_FK_Igs_Ps_Catlg_Vers (
343 x_catalog_version_id IN NUMBER
344 ) AS
345 /*=======================================================================+
346 |
347 | Created By : ssuri
348 |
349 | Date Created By : 10-MAY-2000
350 |
351 | Purpose : NEW TABLE
352 |
353 | Know limitations, enhancements or remarks
354 |
355 | Change History
356 |
357 | Who When What
358 |
359 |
360 |(reverse chronological order - newest change first)
361 *=======================================================================*/
362
363 CURSOR cur_rowid IS
364 SELECT rowid
365 FROM igs_ps_catlg_notes_ALL
366 WHERE catalog_version_id = x_catalog_version_id ;
367
368 lv_rowid cur_rowid%RowType;
369
370 BEGIN
371
372 Open cur_rowid;
373 Fetch cur_rowid INTO lv_rowid;
374 IF (cur_rowid%FOUND) THEN
375 Close cur_rowid;
376 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CNDV_CATV_FK');
377 IGS_GE_MSG_STACK.ADD;
378 App_Exception.Raise_Exception;
379 Return;
380 END IF;
381 Close cur_rowid;
382
383 END Get_FK_Igs_Ps_Catlg_Vers;
384
385 PROCEDURE Get_FK_Igs_Ps_Note_Types (
386 x_note_type_id IN NUMBER
387 ) AS
388
389 /*=======================================================================+
390 |
391 | Created By : ssuri
392 |
393 | Date Created By : 10-MAY-2000
394 |
395 | Purpose : NEW TABLE
396 |
397 | Know limitations, enhancements or remarks
398 |
399 | Change History
400 |
401 | Who When What
402 |
403 |
404 |(reverse chronological order - newest change first)
405 *=======================================================================*/
406
407 CURSOR cur_rowid IS
408 SELECT rowid
409 FROM igs_ps_catlg_notes_ALL
410 WHERE note_type_id = x_note_type_id ;
411
412 lv_rowid cur_rowid%RowType;
413
414 BEGIN
415
416 Open cur_rowid;
417 Fetch cur_rowid INTO lv_rowid;
418 IF (cur_rowid%FOUND) THEN
419 Close cur_rowid;
420 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CNDV_NTPV_FK');
421 IGS_GE_MSG_STACK.ADD;
422 App_Exception.Raise_Exception;
423 Return;
424 END IF;
425 Close cur_rowid;
426
427 END Get_FK_Igs_Ps_Note_Types;
428
429 PROCEDURE Before_DML (
430 p_action IN VARCHAR2,
431 x_rowid IN VARCHAR2 DEFAULT NULL,
432 x_catalog_note_id IN NUMBER DEFAULT NULL,
433 x_catalog_version_id IN NUMBER DEFAULT NULL,
434 x_note_type_id IN NUMBER DEFAULT NULL,
435 x_create_date IN DATE DEFAULT NULL,
436 x_end_date IN DATE DEFAULT NULL,
437 x_sequence IN NUMBER DEFAULT NULL,
438 x_note_text IN VARCHAR2 DEFAULT NULL,
439 x_creation_date IN DATE DEFAULT NULL,
440 x_created_by IN NUMBER DEFAULT NULL,
441 x_last_update_date IN DATE DEFAULT NULL,
442 x_last_updated_by IN NUMBER DEFAULT NULL,
443 x_last_update_login IN NUMBER DEFAULT NULL,
444 X_ORG_ID IN NUMBER DEFAULT NULL
445 ) AS
446
447 /*=======================================================================+
448 |
449 | Created By : ssuri
450 |
451 | Date Created By : 10-MAY-2000
452 |
453 | Purpose : NEW TABLE
454 |
455 | Know limitations, enhancements or remarks
456 |
457 | Change History
458 |
459 | Who When What
460 |
461 |
462 |(reverse chronological order - newest change first)
463 *=======================================================================*/
464
465 BEGIN
466
467 Set_Column_Values (
468 p_action,
469 x_rowid,
470 x_catalog_note_id,
471 x_catalog_version_id,
472 x_note_type_id,
473 x_create_date,
474 x_end_date,
475 x_sequence,
476 x_note_text,
477 x_creation_date,
478 x_created_by,
479 x_last_update_date,
480 x_last_updated_by,
481 x_last_update_login,
482 x_org_id
483 );
484
485 IF (p_action = 'INSERT') THEN
486 -- Call all the procedures related to Before Insert.
487 Null;
488 IF Get_Pk_For_Validation(
489 new_references.catalog_note_id) THEN
490 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
491 IGS_GE_MSG_STACK.ADD;
492 App_Exception.Raise_Exception;
493 END IF;
494 Check_Uniqueness;
495 Check_Constraints;
496 Check_Parent_Existance;
497 ELSIF (p_action = 'UPDATE') THEN
498 -- Call all the procedures related to Before Update.
499 Null;
500 Check_Uniqueness;
501 Check_Constraints;
502 Check_Parent_Existance;
503 ELSIF (p_action = 'DELETE') THEN
504 -- Call all the procedures related to Before Delete.
505 Null;
506 ELSIF (p_action = 'VALIDATE_INSERT') THEN
507 -- Call all the procedures related to Before Insert.
508 IF Get_PK_For_Validation (
509 new_references.catalog_note_id) THEN
510 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
511 IGS_GE_MSG_STACK.ADD;
512 App_Exception.Raise_Exception;
513 END IF;
514 Check_Uniqueness;
515 Check_Constraints;
516 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
517 Check_Uniqueness;
518 Check_Constraints;
519 ELSIF (p_action = 'VALIDATE_DELETE') THEN
520 Null;
521 END IF;
522
523 END Before_DML;
524
525 PROCEDURE After_DML (
526 p_action IN VARCHAR2,
527 x_rowid IN VARCHAR2
528 ) IS
529
530 /*=======================================================================+
531 |
532 | Created By : ssuri
533 |
534 | Date Created By : 10-MAY-2000
535 |
536 | Purpose : NEW TABLE
537 |
538 | Know limitations, enhancements or remarks
539 |
540 | Change History
541 |
542 | Who When What
543 |
544 |
545 |(reverse chronological order - newest change first)
546 *=======================================================================*/
547
548 BEGIN
549
550 l_rowid := x_rowid;
551
552 IF (p_action = 'INSERT') THEN
553 -- Call all the procedures related to After Insert.
554 Null;
555 ELSIF (p_action = 'UPDATE') THEN
556 -- Call all the procedures related to After Update.
557 Null;
558 ELSIF (p_action = 'DELETE') THEN
559 -- Call all the procedures related to After Delete.
560 Null;
561 END IF;
562 l_rowid := null;
563 END After_DML;
564
565 procedure INSERT_ROW (
566 X_ROWID in out NOCOPY VARCHAR2,
567 x_CATALOG_NOTE_ID IN OUT NOCOPY NUMBER,
568 x_CATALOG_VERSION_ID IN NUMBER,
569 x_NOTE_TYPE_ID IN NUMBER,
570 x_CREATE_DATE IN DATE,
571 x_END_DATE IN DATE,
572 x_SEQUENCE IN NUMBER,
573 x_NOTE_TEXT IN VARCHAR2,
574 X_MODE in VARCHAR2 default 'R',
575 X_ORG_ID IN NUMBER
576 ) AS
577
578 /*=======================================================================+
579 |
580 | Created By : ssuri
581 |
582 | Date Created By : 10-MAY-2000
583 |
584 | Purpose : NEW TABLE
585 |
586 | Know limitations, enhancements or remarks
587 |
588 | Change History
589 |
590 | Who When What
591 |
592 |
593 |(reverse chronological order - newest change first)
594 *=======================================================================*/
595
596 cursor C is select ROWID from IGS_PS_CATLG_NOTES_ALL
597 where CATALOG_NOTE_ID= X_CATALOG_NOTE_ID
598 ;
599 X_LAST_UPDATE_DATE DATE ;
600 X_LAST_UPDATED_BY NUMBER ;
601 X_LAST_UPDATE_LOGIN NUMBER ;
602 begin
603 X_LAST_UPDATE_DATE := SYSDATE;
604 if(X_MODE = 'I') then
605 X_LAST_UPDATED_BY := 1;
606 X_LAST_UPDATE_LOGIN := 0;
607 elsif (X_MODE = 'R') then
608 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
609 if X_LAST_UPDATED_BY is NULL then
610 X_LAST_UPDATED_BY := -1;
611 end if;
612 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
613 if X_LAST_UPDATE_LOGIN is NULL then
614 X_LAST_UPDATE_LOGIN := -1;
615 end if;
616 else
617 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
618 IGS_GE_MSG_STACK.ADD;
619 app_exception.raise_exception;
620 end if;
621 SELECT IGS_PS_CATLG_NOTES_S.nextval INTO x_CATALOG_NOTE_ID FROM DUAL;
622 Before_DML(
623 p_action=>'INSERT',
624 x_rowid=>X_ROWID,
625 x_catalog_note_id=>X_CATALOG_NOTE_ID,
626 x_catalog_version_id=>X_CATALOG_VERSION_ID,
627 x_note_type_id=>X_NOTE_TYPE_ID,
628 x_create_date=>X_CREATE_DATE,
629 x_end_date=>X_END_DATE,
630 x_sequence=>X_SEQUENCE,
631 x_note_text=>X_NOTE_TEXT,
632 x_creation_date=>X_LAST_UPDATE_DATE,
633 x_created_by=>X_LAST_UPDATED_BY,
634 x_last_update_date=>X_LAST_UPDATE_DATE,
635 x_last_updated_by=>X_LAST_UPDATED_BY,
636 x_last_update_login=>X_LAST_UPDATE_LOGIN,
637 x_org_id=>igs_ge_gen_003.get_org_id);
638 insert into IGS_PS_CATLG_NOTES_ALL (
639 CATALOG_NOTE_ID
640 ,CATALOG_VERSION_ID
641 ,NOTE_TYPE_ID
642 ,CREATE_DATE
643 ,END_DATE
644 ,SEQUENCE
645 ,NOTE_TEXT
646 ,CREATION_DATE
647 ,CREATED_BY
648 ,LAST_UPDATE_DATE
649 ,LAST_UPDATED_BY
650 ,LAST_UPDATE_LOGIN
651 ,ORG_ID
652 ) values (
653 NEW_REFERENCES.CATALOG_NOTE_ID
654 ,NEW_REFERENCES.CATALOG_VERSION_ID
655 ,NEW_REFERENCES.NOTE_TYPE_ID
656 ,NEW_REFERENCES.CREATE_DATE
657 ,NEW_REFERENCES.END_DATE
658 ,NEW_REFERENCES.SEQUENCE
659 ,NEW_REFERENCES.NOTE_TEXT
660 ,X_LAST_UPDATE_DATE
661 ,X_LAST_UPDATED_BY
662 ,X_LAST_UPDATE_DATE
663 ,X_LAST_UPDATED_BY
664 ,X_LAST_UPDATE_LOGIN
665 ,NEW_REFERENCES.ORG_ID
666 );
667 open c;
668 fetch c into X_ROWID;
669 if (c%notfound) then
670 close c;
671 raise no_data_found;
672 end if;
673 close c;
674 After_DML (
675 p_action => 'INSERT' ,
676 x_rowid => X_ROWID );
677 end INSERT_ROW;
678
679 procedure LOCK_ROW (
680 X_ROWID in VARCHAR2,
681 x_CATALOG_NOTE_ID IN NUMBER,
682 x_CATALOG_VERSION_ID IN NUMBER,
683 x_NOTE_TYPE_ID IN NUMBER,
684 x_CREATE_DATE IN DATE,
685 x_END_DATE IN DATE,
686 x_SEQUENCE IN NUMBER,
687 x_NOTE_TEXT IN VARCHAR2
688 ) AS
689
690 /*=======================================================================+
691 |
692 | Created By : ssuri
693 |
694 | Date Created By : 10-MAY-2000
695 |
696 | Purpose : NEW TABLE
697 |
698 | Know limitations, enhancements or remarks
699 |
700 | Change History
701 |
702 | Who When What
703 |
704 |
705 |(reverse chronological order - newest change first)
706 *=======================================================================*/
707
708 cursor c1 is select
709 CATALOG_VERSION_ID
710 , NOTE_TYPE_ID
711 , CREATE_DATE
712 , END_DATE
713 , SEQUENCE
714 , NOTE_TEXT
715
716 from IGS_PS_CATLG_NOTES_ALL
717 where ROWID = X_ROWID
718 for update nowait;
719 tlinfo c1%rowtype;
720 begin
721 open c1;
722 fetch c1 into tlinfo;
723 if (c1%notfound) then
724 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
725 IGS_GE_MSG_STACK.ADD;
726 close c1;
727 app_exception.raise_exception;
728 return;
729 end if;
730 close c1;
731 if ( ( tlinfo.CATALOG_VERSION_ID = X_CATALOG_VERSION_ID)
732 AND (tlinfo.NOTE_TYPE_ID = X_NOTE_TYPE_ID)
733 AND (tlinfo.CREATE_DATE = X_CREATE_DATE)
734 AND ((tlinfo.END_DATE = X_END_DATE)
735 OR ((tlinfo.END_DATE is null)
736 AND (X_END_DATE is null)))
737 AND ((tlinfo.SEQUENCE = X_SEQUENCE)
738 OR ((tlinfo.SEQUENCE is null)
739 AND (X_SEQUENCE is null)))
740 AND ((tlinfo.NOTE_TEXT = X_NOTE_TEXT)
741 OR ((tlinfo.NOTE_TEXT is null)
742 AND (X_NOTE_TEXT is null)))
743 ) then
744 null;
745 else
746 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
747 IGS_GE_MSG_STACK.ADD;
748 app_exception.raise_exception;
749 end if;
750 return;
751 end LOCK_ROW;
752
753 Procedure UPDATE_ROW (
754 X_ROWID in VARCHAR2,
755 x_CATALOG_NOTE_ID IN NUMBER,
756 x_CATALOG_VERSION_ID IN NUMBER,
757 x_NOTE_TYPE_ID IN NUMBER,
758 x_CREATE_DATE IN DATE,
759 x_END_DATE IN DATE,
760 x_SEQUENCE IN NUMBER,
761 x_NOTE_TEXT IN VARCHAR2,
762 X_MODE in VARCHAR2 default 'R'
763 ) AS
764
765 /*=======================================================================+
766 |
767 | Created By : ssuri
768 |
769 | Date Created By : 10-MAY-2000
770 |
771 | Purpose : NEW TABLE
772 |
773 | Know limitations, enhancements or remarks
774 |
775 | Change History
776 |
777 | Who When What
778 |
779 |
780 |(reverse chronological order - newest change first)
781 *=======================================================================*/
782
783 X_LAST_UPDATE_DATE DATE ;
784 X_LAST_UPDATED_BY NUMBER ;
785 X_LAST_UPDATE_LOGIN NUMBER ;
786 begin
787 X_LAST_UPDATE_DATE := SYSDATE;
788 if(X_MODE = 'I') then
789 X_LAST_UPDATED_BY := 1;
790 X_LAST_UPDATE_LOGIN := 0;
791 elsif (X_MODE = 'R') then
792 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
793 if X_LAST_UPDATED_BY is NULL then
794 X_LAST_UPDATED_BY := -1;
795 end if;
796 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
797 if X_LAST_UPDATE_LOGIN is NULL then
798 X_LAST_UPDATE_LOGIN := -1;
799 end if;
800 else
801 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
802 IGS_GE_MSG_STACK.ADD;
803 app_exception.raise_exception;
804 end if;
805 Before_DML(
806 p_action=>'UPDATE',
807 x_rowid=>X_ROWID,
808 x_catalog_note_id=>X_CATALOG_NOTE_ID,
809 x_catalog_version_id=>X_CATALOG_VERSION_ID,
810 x_note_type_id=>X_NOTE_TYPE_ID,
811 x_create_date=>X_CREATE_DATE,
812 x_end_date=>X_END_DATE,
813 x_sequence=>X_SEQUENCE,
814 x_note_text=>X_NOTE_TEXT,
815 x_creation_date=>X_LAST_UPDATE_DATE,
816 x_created_by=>X_LAST_UPDATED_BY,
817 x_last_update_date=>X_LAST_UPDATE_DATE,
818 x_last_updated_by=>X_LAST_UPDATED_BY,
819 x_last_update_login=>X_LAST_UPDATE_LOGIN
820 );
821 update IGS_PS_CATLG_NOTES_ALL set
822 CATALOG_VERSION_ID = NEW_REFERENCES.CATALOG_VERSION_ID,
823 NOTE_TYPE_ID = NEW_REFERENCES.NOTE_TYPE_ID,
824 CREATE_DATE = NEW_REFERENCES.CREATE_DATE,
825 END_DATE = NEW_REFERENCES.END_DATE,
826 SEQUENCE = NEW_REFERENCES.SEQUENCE,
827 NOTE_TEXT = NEW_REFERENCES.NOTE_TEXT,
828 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
829 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
830 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
831
832 where ROWID = X_ROWID;
833 if (sql%notfound) then
834 raise no_data_found;
835 end if;
836
837 After_DML (
838 p_action => 'UPDATE' ,
839 x_rowid => X_ROWID
840 );
841 end UPDATE_ROW;
842
843 procedure ADD_ROW (
844 X_ROWID in out NOCOPY VARCHAR2,
845 x_CATALOG_NOTE_ID IN OUT NOCOPY NUMBER,
846 x_CATALOG_VERSION_ID IN NUMBER,
847 x_NOTE_TYPE_ID IN NUMBER,
848 x_CREATE_DATE IN DATE,
849 x_END_DATE IN DATE,
850 x_SEQUENCE IN NUMBER,
851 x_NOTE_TEXT IN VARCHAR2,
852 X_MODE in VARCHAR2 default 'R',
853 X_ORG_ID IN NUMBER
854
855 ) AS
856
857 /*=======================================================================+
858 |
859 | Created By : ssuri
860 |
861 | Date Created By : 10-MAY-2000
862 |
863 | Purpose : NEW TABLE
864 |
865 | Know limitations, enhancements or remarks
866 |
867 | Change History
868 |
869 | Who When What
870 |
871 |
872 |(reverse chronological order - newest change first)
873 *=======================================================================*/
874
875 cursor c1 is select ROWID from IGS_PS_CATLG_NOTES_ALL
876 where CATALOG_NOTE_ID= X_CATALOG_NOTE_ID
877 ;
878 begin
879 open c1;
880 fetch c1 into X_ROWID;
881 if (c1%notfound) then
882 close c1;
883 INSERT_ROW (
884 X_ROWID,
885 X_CATALOG_NOTE_ID,
886 X_CATALOG_VERSION_ID,
887 X_NOTE_TYPE_ID,
888 X_CREATE_DATE,
889 X_END_DATE,
890 X_SEQUENCE,
891 X_NOTE_TEXT,
892 X_MODE,
893 X_ORG_ID
894 );
895 return;
896 end if;
897 close c1;
898 UPDATE_ROW (
899 X_ROWID,
900 X_CATALOG_NOTE_ID,
901 X_CATALOG_VERSION_ID,
902 X_NOTE_TYPE_ID,
903 X_CREATE_DATE,
904 X_END_DATE,
905 X_SEQUENCE,
906 X_NOTE_TEXT,
907 X_MODE
908 );
909 end ADD_ROW;
910
911 procedure DELETE_ROW (
912 X_ROWID in VARCHAR2
913 ) AS
914
915 /*=======================================================================+
916 |
917 | Created By : ssuri
918 |
919 | Date Created By : 10-MAY-2000
920 |
921 | Purpose : NEW TABLE
922 |
923 | Know limitations, enhancements or remarks
924 |
925 | Change History
926 |
927 | Who When What
928 |
929 |
930 |(reverse chronological order - newest change first)
931 *=======================================================================*/
932
933 begin
934 Before_DML (
935 p_action => 'DELETE',
936 x_rowid => X_ROWID
937 );
938 delete from IGS_PS_CATLG_NOTES_ALL
939 where ROWID = X_ROWID;
940 if (sql%notfound) then
941 raise no_data_found;
942 end if;
943 After_DML (
944 p_action => 'DELETE',
945 x_rowid => X_ROWID
946 );
947 end DELETE_ROW;
948 END igs_ps_catlg_notes_pkg;