1 PACKAGE BODY igs_ps_note_types_pkg AS
2 /* $Header: IGSPI1KB.pls 115.7 2002/11/29 02:07:30 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_note_types%RowType;
5 new_references igs_ps_note_types%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_note_type_id IN NUMBER DEFAULT NULL,
11 x_note_type IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_closed_ind IN VARCHAR2 DEFAULT NULL,
14 x_catalog_schedule IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21
22 /*************************************************************
23 Created By :
24 Date Created By :
25 Purpose :
26 Know limitations, enhancements or remarks
27 Change History
28 Who When What
29
30 (reverse chronological order - newest change first)
31 ***************************************************************/
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_PS_NOTE_TYPES
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 Open cur_old_ref_values;
45 Fetch cur_old_ref_values INTO old_references;
46 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
47 Close cur_old_ref_values;
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 IGS_GE_MSG_STACK.ADD;
50 App_Exception.Raise_Exception;
51 Return;
52 END IF;
53 Close cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.note_type_id := x_note_type_id;
57 new_references.note_type := x_note_type;
58 new_references.description := x_description;
59 new_references.closed_ind := x_closed_ind;
60 new_references.catalog_schedule := x_catalog_schedule;
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71
72 END Set_Column_Values;
73
74 PROCEDURE Check_Constraints (
75 Column_Name IN VARCHAR2 DEFAULT NULL,
76 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
77 /*************************************************************
78 Created By :
79 Date Created By :
80 Purpose :
81 Know limitations, enhancements or remarks
82 Change History
83 Who When What
84
85 (reverse chronological order - newest change first)
86 ***************************************************************/
87
88 BEGIN
89
90 IF column_name IS NULL THEN
91 NULL;
92 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
93 new_references.closed_ind := column_value;
94 ELSIF UPPER(column_name) = 'CATALOG_SCHEDULE' THEN
95 new_references.catalog_schedule := column_value;
96 NULL;
97 END IF;
98
99
100
101 -- The following code checks for check constraints on the Columns.
102 IF Upper(Column_Name) = 'CLOSED_IND' OR
103 Column_Name IS NULL THEN
104 IF NOT (new_references.closed_ind IN ('Y', 'N')) THEN
105 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
106 IGS_GE_MSG_STACK.ADD;
107 App_Exception.Raise_Exception;
108 END IF;
109 END IF;
110
111 -- The following code checks for check constraints on the Columns.
112 IF Upper(Column_Name) = 'CATALOG_SCHEDULE' OR
113 Column_Name IS NULL THEN
114 IF NOT (new_references.catalog_schedule IN ('CATALOG', 'SCHEDULE')) THEN
115 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
116 IGS_GE_MSG_STACK.ADD;
117 App_Exception.Raise_Exception;
118 END IF;
119 END IF;
120
121 -- The following code checks for check constraints on the Columns.
122 IF Upper(Column_Name) = 'CLOSED_IND' OR
123 Column_Name IS NULL THEN
124 IF NOT (new_references.closed_ind IN ('Y', 'N')) THEN
125 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
126 IGS_GE_MSG_STACK.ADD;
127 App_Exception.Raise_Exception;
128 END IF;
129 END IF;
130
131 -- The following code checks for check constraints on the Columns.
132 IF Upper(Column_Name) = 'CATALOG_SCHEDULE' OR
133 Column_Name IS NULL THEN
134 IF NOT (new_references.catalog_schedule IN ('CATALOG', 'SCHEDULE')) THEN
135 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END IF;
139 END IF;
140
141
142 END Check_Constraints;
143
144 PROCEDURE Check_Uniqueness AS
145 /*************************************************************
146 Created By :
147 Date Created By :
148 Purpose :
149 Know limitations, enhancements or remarks
150 Change History
151 Who When What
152
153 (reverse chronological order - newest change first)
154 ***************************************************************/
155
156 begin
157 IF Get_Uk_For_Validation (
158 new_references.catalog_schedule
159 ,new_references.note_type
160 ) THEN
161 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
162 IGS_GE_MSG_STACK.ADD;
163 app_exception.raise_exception;
164 END IF;
165 END Check_Uniqueness ;
166 PROCEDURE Check_Child_Existance IS
167 /*************************************************************
168 Created By :
169 Date Created By :
170 Purpose :
171 Know limitations, enhancements or remarks
172 Change History
173 Who When What
174
175 (reverse chronological order - newest change first)
176 ***************************************************************/
177
178 BEGIN
179
180 Igs_Ps_Catlg_Notes_Pkg.Get_FK_Igs_Ps_Note_Types (
181 old_references.note_type_id
182 );
183
184 END Check_Child_Existance;
185
186 FUNCTION Get_PK_For_Validation (
187 x_note_type_id IN NUMBER
188 ) RETURN BOOLEAN AS
189
190 /*************************************************************
191 Created By :
192 Date Created By :
193 Purpose :
194 Know limitations, enhancements or remarks
195 Change History
196 Who When What
197
198 (reverse chronological order - newest change first)
199 ***************************************************************/
200
201 CURSOR cur_rowid IS
202 SELECT rowid
203 FROM igs_ps_note_types
204 WHERE note_type_id = x_note_type_id
205 FOR UPDATE NOWAIT;
206
207 lv_rowid cur_rowid%RowType;
208
209 BEGIN
210
211 Open cur_rowid;
212 Fetch cur_rowid INTO lv_rowid;
213 IF (cur_rowid%FOUND) THEN
214 Close cur_rowid;
215 Return(TRUE);
216 ELSE
217 Close cur_rowid;
218 Return(FALSE);
219 END IF;
220 END Get_PK_For_Validation;
221
222 FUNCTION Get_UK_For_Validation (
223 x_catalog_schedule IN VARCHAR2,
224 x_note_type IN VARCHAR2
225 ) RETURN BOOLEAN AS
226
227 /*************************************************************
228 Created By :
229 Date Created By :
230 Purpose :
231 Know limitations, enhancements or remarks
232 Change History
233 Who When What
234
235 (reverse chronological order - newest change first)
236 ***************************************************************/
237
238 CURSOR cur_rowid IS
239 SELECT rowid
240 FROM igs_ps_note_types
241 WHERE catalog_schedule = x_catalog_schedule
242 AND note_type = x_note_type and ((l_rowid is null) or (rowid <> l_rowid))
243
244 ;
245 lv_rowid cur_rowid%RowType;
246
247 BEGIN
248
249 Open cur_rowid;
250 Fetch cur_rowid INTO lv_rowid;
251 IF (cur_rowid%FOUND) THEN
252 Close cur_rowid;
253 return (true);
254 ELSE
255 close cur_rowid;
256 return(false);
257 END IF;
258 END Get_UK_For_Validation ;
259 PROCEDURE Before_DML (
260 p_action IN VARCHAR2,
261 x_rowid IN VARCHAR2 DEFAULT NULL,
262 x_note_type_id IN NUMBER DEFAULT NULL,
263 x_note_type IN VARCHAR2 DEFAULT NULL,
264 x_description IN VARCHAR2 DEFAULT NULL,
265 x_closed_ind IN VARCHAR2 DEFAULT NULL,
266 x_catalog_schedule IN VARCHAR2 DEFAULT NULL,
267 x_creation_date IN DATE DEFAULT NULL,
268 x_created_by IN NUMBER DEFAULT NULL,
269 x_last_update_date IN DATE DEFAULT NULL,
270 x_last_updated_by IN NUMBER DEFAULT NULL,
271 x_last_update_login IN NUMBER DEFAULT NULL
272 ) AS
273 /*************************************************************
274 Created By :
275 Date Created By :
276 Purpose :
277 Know limitations, enhancements or remarks
278 Change History
279 Who When What
280
281 (reverse chronological order - newest change first)
282 ***************************************************************/
283
284 BEGIN
285
286 Set_Column_Values (
287 p_action,
288 x_rowid,
289 x_note_type_id,
290 x_note_type,
291 x_description,
292 x_closed_ind,
293 x_catalog_schedule,
294 x_creation_date,
295 x_created_by,
296 x_last_update_date,
297 x_last_updated_by,
298 x_last_update_login
299 );
300
301 IF (p_action = 'INSERT') THEN
302 -- Call all the procedures related to Before Insert.
303 Null;
304 IF Get_Pk_For_Validation(
305 new_references.note_type_id) THEN
306 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
307 IGS_GE_MSG_STACK.ADD;
308 App_Exception.Raise_Exception;
309 END IF;
310 Check_Uniqueness;
311 Check_Constraints;
312 ELSIF (p_action = 'UPDATE') THEN
313 -- Call all the procedures related to Before Update.
314 Null;
315 Check_Uniqueness;
316 Check_Constraints;
317 ELSIF (p_action = 'DELETE') THEN
318 -- Call all the procedures related to Before Delete.
319 Null;
320 Check_Child_Existance;
321 ELSIF (p_action = 'VALIDATE_INSERT') THEN
322 -- Call all the procedures related to Before Insert.
323 IF Get_PK_For_Validation (
324 new_references.note_type_id) THEN
325 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
326 IGS_GE_MSG_STACK.ADD;
327 App_Exception.Raise_Exception;
328 END IF;
329 Check_Uniqueness;
330 Check_Constraints;
331 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
332 Check_Uniqueness;
333 Check_Constraints;
334 ELSIF (p_action = 'VALIDATE_DELETE') THEN
335 Check_Child_Existance;
336 END IF;
337
338 END Before_DML;
339
340 PROCEDURE After_DML (
341 p_action IN VARCHAR2,
342 x_rowid IN VARCHAR2
343 ) IS
344 /*************************************************************
345 Created By :
346 Date Created By :
347 Purpose :
348 Know limitations, enhancements or remarks
349 Change History
350 Who When What
351
352 (reverse chronological order - newest change first)
353 ***************************************************************/
354
355 BEGIN
356
357 l_rowid := x_rowid;
358
359 IF (p_action = 'INSERT') THEN
360 -- Call all the procedures related to After Insert.
361 Null;
362 ELSIF (p_action = 'UPDATE') THEN
363 -- Call all the procedures related to After Update.
364 Null;
365 ELSIF (p_action = 'DELETE') THEN
366 -- Call all the procedures related to After Delete.
367 Null;
368 END IF;
369 l_rowid:=NULL;
370 END After_DML;
371
372 procedure INSERT_ROW (
373 X_ROWID in out NOCOPY VARCHAR2,
374 x_NOTE_TYPE_ID IN OUT NOCOPY NUMBER,
375 x_NOTE_TYPE IN VARCHAR2,
376 x_DESCRIPTION IN VARCHAR2,
377 x_CLOSED_IND IN VARCHAR2,
378 x_CATALOG_SCHEDULE IN VARCHAR2,
379 X_MODE in VARCHAR2 default 'R'
380 ) AS
381 /*************************************************************
382 Created By :
383 Date Created By :
384 Purpose :
385 Know limitations, enhancements or remarks
386 Change History
387 Who When What
388
389 (reverse chronological order - newest change first)
390 ***************************************************************/
391
392 cursor C is select ROWID from IGS_PS_NOTE_TYPES
393 where NOTE_TYPE_ID= X_NOTE_TYPE_ID
394 ;
395 X_LAST_UPDATE_DATE DATE ;
396 X_LAST_UPDATED_BY NUMBER ;
397 X_LAST_UPDATE_LOGIN NUMBER ;
398 begin
399 X_LAST_UPDATE_DATE := SYSDATE;
400 if(X_MODE = 'I') then
401 X_LAST_UPDATED_BY := 1;
402 X_LAST_UPDATE_LOGIN := 0;
403 elsif (X_MODE = 'R') then
404 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
405 if X_LAST_UPDATED_BY is NULL then
406 X_LAST_UPDATED_BY := -1;
407 end if;
408 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
409 if X_LAST_UPDATE_LOGIN is NULL then
410 X_LAST_UPDATE_LOGIN := -1;
411 end if;
412 else
413 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
414 IGS_GE_MSG_STACK.ADD;
415 app_exception.raise_exception;
416 end if;
417 ----
418 SELECT igs_ps_note_types_s.nextval
419 INTO x_NOTE_TYPE_ID
420 FROM dual;
421 ---
422
423 Before_DML(
424 p_action=>'INSERT',
425 x_rowid=>X_ROWID,
426 x_note_type_id=>X_NOTE_TYPE_ID,
427 x_note_type=>X_NOTE_TYPE,
428 x_description=>X_DESCRIPTION,
429 x_closed_ind=>NVL(X_CLOSED_IND,'N' ),
430 x_catalog_schedule=>X_CATALOG_SCHEDULE,
431 x_creation_date=>X_LAST_UPDATE_DATE,
432 x_created_by=>X_LAST_UPDATED_BY,
433 x_last_update_date=>X_LAST_UPDATE_DATE,
434 x_last_updated_by=>X_LAST_UPDATED_BY,
435 x_last_update_login=>X_LAST_UPDATE_LOGIN);
436 insert into IGS_PS_NOTE_TYPES (
437 NOTE_TYPE_ID
438 ,NOTE_TYPE
439 ,DESCRIPTION
440 ,CLOSED_IND
441 ,CATALOG_SCHEDULE
442 ,CREATION_DATE
443 ,CREATED_BY
444 ,LAST_UPDATE_DATE
445 ,LAST_UPDATED_BY
446 ,LAST_UPDATE_LOGIN
447 ) values (
448 NEW_REFERENCES.NOTE_TYPE_ID
449 ,NEW_REFERENCES.NOTE_TYPE
450 ,NEW_REFERENCES.DESCRIPTION
451 ,NEW_REFERENCES.CLOSED_IND
452 ,NEW_REFERENCES.CATALOG_SCHEDULE
453 ,X_LAST_UPDATE_DATE
454 ,X_LAST_UPDATED_BY
455 ,X_LAST_UPDATE_DATE
456 ,X_LAST_UPDATED_BY
457 ,X_LAST_UPDATE_LOGIN
458 );
459 open c;
460 fetch c into X_ROWID;
461 if (c%notfound) then
462 close c;
463 raise no_data_found;
464 end if;
465 close c;
466 After_DML (
467 p_action => 'INSERT' ,
468 x_rowid => X_ROWID );
469 end INSERT_ROW;
470 procedure LOCK_ROW (
471 X_ROWID in VARCHAR2,
472 x_NOTE_TYPE_ID IN NUMBER,
473 x_NOTE_TYPE IN VARCHAR2,
474 x_DESCRIPTION IN VARCHAR2,
475 x_CLOSED_IND IN VARCHAR2,
476 x_CATALOG_SCHEDULE IN VARCHAR2 ) AS
477 /*************************************************************
478 Created By :
479 Date Created By :
480 Purpose :
481 Know limitations, enhancements or remarks
482 Change History
483 Who When What
484
485 (reverse chronological order - newest change first)
486 ***************************************************************/
487
488 cursor c1 is select
489 NOTE_TYPE
490 , DESCRIPTION
491 , CLOSED_IND
492 , CATALOG_SCHEDULE
493 from IGS_PS_NOTE_TYPES
494 where ROWID = X_ROWID
495 for update nowait;
496 tlinfo c1%rowtype;
497 begin
498 open c1;
499 fetch c1 into tlinfo;
500 if (c1%notfound) then
501 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
502 IGS_GE_MSG_STACK.ADD;
503 close c1;
504 app_exception.raise_exception;
505 return;
506 end if;
507 close c1;
508 if ( ( tlinfo.NOTE_TYPE = X_NOTE_TYPE)
509 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
510 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
511 AND (tlinfo.CATALOG_SCHEDULE = X_CATALOG_SCHEDULE)
512 ) then
513 null;
514 else
515 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
516 IGS_GE_MSG_STACK.ADD;
517 app_exception.raise_exception;
518 end if;
519 return;
520 end LOCK_ROW;
521 Procedure UPDATE_ROW (
522 X_ROWID in VARCHAR2,
523 x_NOTE_TYPE_ID IN NUMBER,
524 x_NOTE_TYPE IN VARCHAR2,
525 x_DESCRIPTION IN VARCHAR2,
526 x_CLOSED_IND IN VARCHAR2,
527 x_CATALOG_SCHEDULE IN VARCHAR2,
528 X_MODE in VARCHAR2 default 'R'
529 ) AS
530 /*************************************************************
531 Created By :
532 Date Created By :
533 Purpose :
534 Know limitations, enhancements or remarks
535 Change History
536 Who When What
537
538 (reverse chronological order - newest change first)
539 ***************************************************************/
540
541 X_LAST_UPDATE_DATE DATE ;
542 X_LAST_UPDATED_BY NUMBER ;
543 X_LAST_UPDATE_LOGIN NUMBER ;
544 begin
545 X_LAST_UPDATE_DATE := SYSDATE;
546 if(X_MODE = 'I') then
547 X_LAST_UPDATED_BY := 1;
548 X_LAST_UPDATE_LOGIN := 0;
549 elsif (X_MODE = 'R') then
550 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
551 if X_LAST_UPDATED_BY is NULL then
552 X_LAST_UPDATED_BY := -1;
553 end if;
554 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
555 if X_LAST_UPDATE_LOGIN is NULL then
556 X_LAST_UPDATE_LOGIN := -1;
557 end if;
558 else
559 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
560 IGS_GE_MSG_STACK.ADD;
561 app_exception.raise_exception;
562 end if;
563 Before_DML(
564 p_action=>'UPDATE',
565 x_rowid=>X_ROWID,
566 x_note_type_id=>X_NOTE_TYPE_ID,
567 x_note_type=>X_NOTE_TYPE,
568 x_description=>X_DESCRIPTION,
569 x_closed_ind=>NVL(X_CLOSED_IND,'N' ),
570 x_catalog_schedule=>X_CATALOG_SCHEDULE,
571 x_creation_date=>X_LAST_UPDATE_DATE,
572 x_created_by=>X_LAST_UPDATED_BY,
573 x_last_update_date=>X_LAST_UPDATE_DATE,
574 x_last_updated_by=>X_LAST_UPDATED_BY,
575 x_last_update_login=>X_LAST_UPDATE_LOGIN);
576 update IGS_PS_NOTE_TYPES set
577 NOTE_TYPE = NEW_REFERENCES.NOTE_TYPE,
578 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
579 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
580 CATALOG_SCHEDULE = NEW_REFERENCES.CATALOG_SCHEDULE,
581 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
582 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
583 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
584 where ROWID = X_ROWID;
585 if (sql%notfound) then
586 raise no_data_found;
587 end if;
588
589 After_DML (
590 p_action => 'UPDATE' ,
591 x_rowid => X_ROWID
592 );
593 end UPDATE_ROW;
594 procedure ADD_ROW (
595 X_ROWID in out NOCOPY VARCHAR2,
596 x_NOTE_TYPE_ID IN OUT NOCOPY NUMBER,
597 x_NOTE_TYPE IN VARCHAR2,
598 x_DESCRIPTION IN VARCHAR2,
599 x_CLOSED_IND IN VARCHAR2,
600 x_CATALOG_SCHEDULE IN VARCHAR2,
601 X_MODE in VARCHAR2 default 'R'
602 ) AS
603 /*************************************************************
604 Created By :
605 Date Created By :
606 Purpose :
607 Know limitations, enhancements or remarks
608 Change History
609 Who When What
610
611 (reverse chronological order - newest change first)
612 ***************************************************************/
613
614 cursor c1 is select ROWID from IGS_PS_NOTE_TYPES
615 where NOTE_TYPE_ID= X_NOTE_TYPE_ID
616 ;
617 begin
618 open c1;
619 fetch c1 into X_ROWID;
620 if (c1%notfound) then
621 close c1;
622 INSERT_ROW (
623 X_ROWID,
624 X_NOTE_TYPE_ID,
625 X_NOTE_TYPE,
626 X_DESCRIPTION,
627 X_CLOSED_IND,
628 X_CATALOG_SCHEDULE,
629 X_MODE );
630 return;
631 end if;
632 close c1;
633 UPDATE_ROW (
634 X_ROWID,
635 X_NOTE_TYPE_ID,
636 X_NOTE_TYPE,
637 X_DESCRIPTION,
638 X_CLOSED_IND,
639 X_CATALOG_SCHEDULE,
640 X_MODE );
641 end ADD_ROW;
642 procedure DELETE_ROW (
643 X_ROWID in VARCHAR2
644 ) AS
645 /*************************************************************
646 Created By :
647 Date Created By :
648 Purpose :
649 Know limitations, enhancements or remarks
650 Change History
651 Who When What
652
653 (reverse chronological order - newest change first)
654 ***************************************************************/
655
656 begin
657 Before_DML (
658 p_action => 'DELETE',
659 x_rowid => X_ROWID
660 );
661 delete from IGS_PS_NOTE_TYPES
662 where ROWID = X_ROWID;
663 if (sql%notfound) then
664 raise no_data_found;
665 end if;
666 After_DML (
667 p_action => 'DELETE',
668 x_rowid => X_ROWID
669 );
670 end DELETE_ROW;
671 END igs_ps_note_types_pkg;
[Home] [Help]