1 PACKAGE BODY igs_ru_named_rule_gr_pkg AS
2 /* $Header: IGSUI17B.pls 115.10 2002/11/29 04:29:29 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ru_named_rule_gr%RowType;
5 new_references igs_ru_named_rule_gr%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_group_cd IN VARCHAR2 ,
11 x_name_cd IN VARCHAR2 ,
12 x_description IN VARCHAR2 ,
13 x_s_return_type IN VARCHAR2 ,
14 x_rug_sequence_number IN NUMBER ,
15 x_select_group IN NUMBER ,
16 x_message_group IN NUMBER ,
17 x_creation_date IN DATE ,
18 x_created_by IN NUMBER ,
19 x_last_update_date IN DATE ,
20 x_last_updated_by IN NUMBER ,
21 x_last_update_login IN NUMBER
22 ) AS
23
24 /*************************************************************
25 Created By : tray
26 Date Created By : 10.05.2000
27 Purpose :
28 Know limitations, enhancements or remarks
29 Change History
30 Who When What
31
32 (reverse chronological order - newest change first)
33 ***************************************************************/
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_RU_NAMED_RULE_GR
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 Open cur_old_ref_values;
47 Fetch cur_old_ref_values INTO old_references;
48 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
49 Close cur_old_ref_values;
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_NAMED_RULE_GR : P_ACTION INSERT, VALIDATE_INSERT : IGSUI17B.PLS');
52 IGS_GE_MSG_STACK.ADD;
53 App_Exception.Raise_Exception;
54 Return;
55 END IF;
56 Close cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.group_cd := x_group_cd;
60 new_references.name_cd := x_name_cd;
61 new_references.description := x_description;
62 new_references.s_return_type := x_s_return_type;
63 new_references.rug_sequence_number := x_rug_sequence_number;
64 new_references.select_group := x_select_group;
65 new_references.message_group := x_message_group;
66 IF (p_action = 'UPDATE') THEN
67 new_references.creation_date := old_references.creation_date;
68 new_references.created_by := old_references.created_by;
69 ELSE
70 new_references.creation_date := x_creation_date;
71 new_references.created_by := x_created_by;
72 END IF;
73 new_references.last_update_date := x_last_update_date;
74 new_references.last_updated_by := x_last_updated_by;
75 new_references.last_update_login := x_last_update_login;
76
77 END Set_Column_Values;
78
79 PROCEDURE Check_Constraints (
80 Column_Name IN VARCHAR2 ,
81 Column_Value IN VARCHAR2 ) AS
82 /*************************************************************
83 Created By : tray
84 Date Created By : 10.05.2000
85 Purpose :
86 Know limitations, enhancements or remarks
87 Change History
88 Who When What
89
90 (reverse chronological order - newest change first)
91 ***************************************************************/
92
93 BEGIN
94
95 IF column_name IS NULL THEN
96 NULL;
97 ELSIF UPPER(column_name) = 'SELECT_GROUP' THEN
98 new_references.select_group := IGS_GE_NUMBER.TO_NUM(column_value);
99 ELSIF UPPER(column_name) = 'MESSAGE_GROUP' THEN
100 new_references.message_group := IGS_GE_NUMBER.TO_NUM(column_value);
101 ELSIF UPPER(column_name) = 'RUG_SEQUENCE_NUMBER' THEN
102 new_references.rug_sequence_number := IGS_GE_NUMBER.TO_NUM(column_value);
103 NULL;
104 END IF;
105
106
107
108 -- The following code checks for check constraints on the Columns.
109 IF Upper(Column_Name) = 'SELECT_GROUP' OR
110 Column_Name IS NULL THEN
111 IF NOT (new_references.select_group BETWEEN 1
112 AND 999999) THEN
113 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
114 IGS_GE_MSG_STACK.ADD;
115 App_Exception.Raise_Exception;
116 END IF;
117 END IF;
118
119 -- The following code checks for check constraints on the Columns.
120 IF Upper(Column_Name) = 'MESSAGE_GROUP' OR
121 Column_Name IS NULL THEN
122 IF NOT (new_references.message_group BETWEEN 1
123 AND 999999) THEN
124 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception;
127 END IF;
128 END IF;
129
130 -- The following code checks for check constraints on the Columns.
131 IF Upper(Column_Name) = 'RUG_SEQUENCE_NUMBER' OR
132 Column_Name IS NULL THEN
133 IF NOT (new_references.rug_sequence_number BETWEEN 1
134 AND 999999) 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_Parent_Existance AS
145
146 /*************************************************************
147 Created By : tray
148 Date Created By : 10.05.2000
149 Purpose :
150 Know limitations, enhancements or remarks
151 Change History
152 Who When What
153
154 (reverse chronological order - newest change first)
155 ***************************************************************/
156
157 BEGIN
158
159 IF (((old_references.s_return_type = new_references.s_return_type)) OR
160 ((new_references.s_return_type IS NULL))) THEN
161 NULL;
162 ELSIF NOT Igs_Ru_Ret_Type_Pkg.Get_PK_For_Validation (
163 new_references.s_return_type
164 ) THEN
165 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
166 IGS_RU_GEN_006.SET_TOKEN('Igs_Ru_Ret_Type : P_ACTION Check_Parent_Existance s_return_type : IGSUI17B.PLS');
167 IGS_GE_MSG_STACK.ADD;
168 App_Exception.Raise_Exception;
169 END IF;
170
171 IF (((old_references.group_cd = new_references.group_cd)) OR
172 ((new_references.group_cd IS NULL))) THEN
173 NULL;
174 ELSIF NOT Igs_Ru_Nrg_Group_Cd_Pkg.Get_PK_For_Validation (
175 new_references.group_cd
176 ) THEN
177 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
178 IGS_RU_GEN_006.SET_TOKEN('Igs_Ru_Nrg_Group : P_ACTION Check_Parent_Existance group_cd : IGSUI17B.PLS');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182
183 IF (((old_references.message_group = new_references.message_group)) OR
184 ((new_references.message_group IS NULL))) THEN
185 NULL;
186 ELSIF NOT Igs_Ru_Group_Pkg.Get_PK_For_Validation (
187 new_references.message_group
188 ) THEN
189 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
190 IGS_RU_GEN_006.SET_TOKEN('Igs_Ru_Group : P_ACTION Check_Parent_Existance message_group : IGSUI17B.PLS');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END IF;
194
195 IF (((old_references.rug_sequence_number = new_references.rug_sequence_number)) OR
196 ((new_references.rug_sequence_number IS NULL))) THEN
197 NULL;
198 ELSIF NOT Igs_Ru_Group_Pkg.Get_PK_For_Validation (
199 new_references.rug_sequence_number
200 ) THEN
201 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
202 IGS_RU_GEN_006.SET_TOKEN('Igs_Ru_Group : P_ACTION Check_Parent_Existance rug_sequence_number : IGSUI17B.PLS');
203 IGS_GE_MSG_STACK.ADD;
204 App_Exception.Raise_Exception;
205 END IF;
206
207 IF (((old_references.select_group = new_references.select_group)) OR
208 ((new_references.select_group IS NULL))) THEN
209 NULL;
210 ELSIF NOT Igs_Ru_Group_Pkg.Get_PK_For_Validation (
211 new_references.select_group
212 ) THEN
213 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
214 IGS_RU_GEN_006.SET_TOKEN('Igs_Ru_Group : P_ACTION Check_Parent_Existance select_group : IGSUI17B.PLS');
215 IGS_GE_MSG_STACK.ADD;
216 App_Exception.Raise_Exception;
217 END IF;
218
219 END Check_Parent_Existance;
220
221 FUNCTION Get_PK_For_Validation (
222 x_group_cd IN VARCHAR2,
223 x_name_cd IN VARCHAR2
224 ) RETURN BOOLEAN AS
225
226 /*************************************************************
227 Created By : tray
228 Date Created By : 10.05.2000
229 Purpose :
230 Know limitations, enhancements or remarks
231 Change History
232 Who When What
233
234 (reverse chronological order - newest change first)
235 ***************************************************************/
236
237 CURSOR cur_rowid IS
238 SELECT rowid
239 FROM igs_ru_named_rule_gr
240 WHERE group_cd = x_group_cd
241 AND name_cd = x_name_cd
242 FOR UPDATE NOWAIT;
243
244 lv_rowid cur_rowid%RowType;
245
246 BEGIN
247
248 Open cur_rowid;
249 Fetch cur_rowid INTO lv_rowid;
250 IF (cur_rowid%FOUND) THEN
251 Close cur_rowid;
252 Return(TRUE);
253 ELSE
254 Close cur_rowid;
255 Return(FALSE);
256 END IF;
257 END Get_PK_For_Validation;
258
259 PROCEDURE Get_FK_Igs_Ru_Ret_Type (
260 x_s_return_type IN VARCHAR2
261 ) AS
262
263 /*************************************************************
264 Created By : tray
265 Date Created By : 10.05.2000
266 Purpose :
267 Know limitations, enhancements or remarks
268 Change History
269 Who When What
270
271 (reverse chronological order - newest change first)
272 ***************************************************************/
273
274 CURSOR cur_rowid IS
275 SELECT rowid
276 FROM igs_ru_named_rule_gr
277 WHERE s_return_type = x_s_return_type ;
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 Fnd_Message.Set_Name ('IGS', 'IGS_RU_NRG_SRRT_FK');
288 IGS_GE_MSG_STACK.ADD;
289 App_Exception.Raise_Exception;
290 Return;
291 END IF;
292 Close cur_rowid;
293
294 END Get_FK_Igs_Ru_Ret_Type;
295
296 PROCEDURE Get_FK_Igs_Ru_Nrg_Group_Cd (
297 x_group_cd IN VARCHAR2
298 ) AS
299
300 /*************************************************************
301 Created By : tray
302 Date Created By : 10.05.2000
303 Purpose :
304 Know limitations, enhancements or remarks
305 Change History
306 Who When What
307
308 (reverse chronological order - newest change first)
309 ***************************************************************/
310
311 CURSOR cur_rowid IS
312 SELECT rowid
313 FROM igs_ru_named_rule_gr
314 WHERE group_cd = x_group_cd ;
315
316 lv_rowid cur_rowid%RowType;
317
318 BEGIN
319
320 Open cur_rowid;
321 Fetch cur_rowid INTO lv_rowid;
322 IF (cur_rowid%FOUND) THEN
323 Close cur_rowid;
324 Fnd_Message.Set_Name ('IGS', 'IGS_RU_NRG_NGC_FK');
325 IGS_GE_MSG_STACK.ADD;
326 App_Exception.Raise_Exception;
327 Return;
328 END IF;
329 Close cur_rowid;
330
331 END Get_FK_Igs_Ru_Nrg_Group_Cd;
332
333 PROCEDURE Get_FK_Igs_Ru_Group_msg (
334 x_sequence_number IN NUMBER
335 ) AS
336
337 /*************************************************************
338 Created By : tray
339 Date Created By : 10.05.2000
340 Purpose :
341 Know limitations, enhancements or remarks
342 Change History
343 Who When What
344
345 (reverse chronological order - newest change first)
346 ***************************************************************/
347
348 CURSOR cur_rowid IS
349 SELECT rowid
350 FROM igs_ru_named_rule_gr
351 WHERE message_group = x_sequence_number ;
352
353 lv_rowid cur_rowid%RowType;
354
355 BEGIN
356
357 Open cur_rowid;
358 Fetch cur_rowid INTO lv_rowid;
359 IF (cur_rowid%FOUND) THEN
360 Close cur_rowid;
361 Fnd_Message.Set_Name ('IGS', 'IGS_RU_NRG_MG_RUG_FK');
362 IGS_GE_MSG_STACK.ADD;
363 App_Exception.Raise_Exception;
364 Return;
365 END IF;
366 Close cur_rowid;
367
368 END Get_FK_Igs_Ru_Group_msg;
369
370 PROCEDURE Get_FK_Igs_Ru_Group_seq (
371 x_sequence_number IN NUMBER
372 ) AS
373
374 /*************************************************************
375 Created By : tray
376 Date Created By : 10.05.2000
377 Purpose :
378 Know limitations, enhancements or remarks
379 Change History
380 Who When What
381
382 (reverse chronological order - newest change first)
383 ***************************************************************/
384
385 CURSOR cur_rowid IS
386 SELECT rowid
387 FROM igs_ru_named_rule_gr
388 WHERE rug_sequence_number = x_sequence_number ;
389
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_RU_NRG_MG_RUG_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_Ru_Group_seq;
406
407 PROCEDURE Get_FK_Igs_Ru_Group_sg (
408 x_sequence_number IN NUMBER
409 ) AS
410
411 /*************************************************************
412 Created By : tray
413 Date Created By : 10.05.2000
414 Purpose :
415 Know limitations, enhancements or remarks
416 Change History
417 Who When What
418
419 (reverse chronological order - newest change first)
420 ***************************************************************/
421
422 CURSOR cur_rowid IS
423 SELECT rowid
424 FROM igs_ru_named_rule_gr
425 WHERE select_group = x_sequence_number ;
426
427 lv_rowid cur_rowid%RowType;
428
429 BEGIN
430
431 Open cur_rowid;
432 Fetch cur_rowid INTO lv_rowid;
433 IF (cur_rowid%FOUND) THEN
434 Close cur_rowid;
435 Fnd_Message.Set_Name ('IGS', 'IGS_RU_NRG_MG_RUG_FK');
436 IGS_GE_MSG_STACK.ADD;
437 App_Exception.Raise_Exception;
438 Return;
439 END IF;
440 Close cur_rowid;
441
442 END Get_FK_Igs_Ru_Group_sg;
443
444 PROCEDURE Before_DML (
445 p_action IN VARCHAR2,
446 x_rowid IN VARCHAR2 ,
447 x_group_cd IN VARCHAR2 ,
448 x_name_cd IN VARCHAR2 ,
449 x_description IN VARCHAR2 ,
450 x_s_return_type IN VARCHAR2 ,
451 x_rug_sequence_number IN NUMBER ,
452 x_select_group IN NUMBER ,
453 x_message_group IN NUMBER ,
454 x_creation_date IN DATE ,
455 x_created_by IN NUMBER ,
456 x_last_update_date IN DATE ,
457 x_last_updated_by IN NUMBER ,
458 x_last_update_login IN NUMBER
459 ) AS
460
461 /*************************************************************
462 Created By : tray
463 Date Created By : 10.05.2000
464 Purpose :
465 Know limitations, enhancements or remarks
466 Change History
467 Who When What
468
469 (reverse chronological order - newest change first)
470 ***************************************************************/
471
472 BEGIN
473
474 Set_Column_Values (
475 p_action,
476 x_rowid,
477 x_group_cd,
478 x_name_cd,
479 x_description,
480 x_s_return_type,
481 x_rug_sequence_number,
482 x_select_group,
483 x_message_group,
484 x_creation_date,
485 x_created_by,
486 x_last_update_date,
487 x_last_updated_by,
488 x_last_update_login
489 );
490
491 IF (p_action = 'INSERT') THEN
492 -- Call all the procedures related to Before Insert.
493 Null;
494 IF Get_Pk_For_Validation(
495 new_references.group_cd,
496 new_references.name_cd) THEN
497 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
498 IGS_GE_MSG_STACK.ADD;
499 App_Exception.Raise_Exception;
500 END IF;
501 Check_Constraints;
502 Check_Parent_Existance;
503 ELSIF (p_action = 'UPDATE') THEN
504 -- Call all the procedures related to Before Update.
505 Null;
506 Check_Constraints;
507 Check_Parent_Existance;
508 ELSIF (p_action = 'DELETE') THEN
509 -- Call all the procedures related to Before Delete.
510 Null;
511 ELSIF (p_action = 'VALIDATE_INSERT') THEN
512 -- Call all the procedures related to Before Insert.
513 IF Get_PK_For_Validation (
514 new_references.group_cd,
515 new_references.name_cd) THEN
516 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
517 IGS_GE_MSG_STACK.ADD;
518 App_Exception.Raise_Exception;
519 END IF;
520 Check_Constraints;
521 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
522 Check_Constraints;
523 ELSIF (p_action = 'VALIDATE_DELETE') THEN
524 Null;
525 END IF;
526
527 END Before_DML;
528
529 PROCEDURE After_DML (
530 p_action IN VARCHAR2,
531 x_rowid IN VARCHAR2
532 ) IS
533
534 /*************************************************************
535 Created By : tray
536 Date Created By : 10.05.2000
537 Purpose :
538 Know limitations, enhancements or remarks
539 Change History
540 Who When What
541
542 (reverse chronological order - newest change first)
543 ***************************************************************/
544
545 BEGIN
546
547 l_rowid := x_rowid;
548
549 IF (p_action = 'INSERT') THEN
550 -- Call all the procedures related to After Insert.
551 Null;
552 ELSIF (p_action = 'UPDATE') THEN
553 -- Call all the procedures related to After Update.
554 Null;
555 ELSIF (p_action = 'DELETE') THEN
556 -- Call all the procedures related to After Delete.
557 Null;
558 END IF;
559
560 END After_DML;
561
562 procedure INSERT_ROW (
563 X_ROWID in out NOCOPY VARCHAR2,
564 x_GROUP_CD IN VARCHAR2,
565 x_NAME_CD IN VARCHAR2,
566 x_DESCRIPTION IN VARCHAR2,
567 x_S_RETURN_TYPE IN VARCHAR2,
568 x_RUG_SEQUENCE_NUMBER IN NUMBER,
569 x_SELECT_GROUP IN NUMBER,
570 x_MESSAGE_GROUP IN NUMBER,
571 X_MODE in VARCHAR2
572 ) AS
573
574 /*************************************************************
575 Created By : tray
576 Date Created By : 10.05.2000
577 Purpose :
578 Know limitations, enhancements or remarks
579 Change History
580 Who When What
581
582 (reverse chronological order - newest change first)
583 ***************************************************************/
584
585 cursor C is select ROWID from IGS_RU_NAMED_RULE_GR
586 where GROUP_CD= X_GROUP_CD
587 and NAME_CD = X_NAME_CD
588 ;
589 X_LAST_UPDATE_DATE DATE ;
590 X_LAST_UPDATED_BY NUMBER ;
591 X_LAST_UPDATE_LOGIN NUMBER ;
592 begin
593 X_LAST_UPDATE_DATE := SYSDATE;
594 if(X_MODE = 'I') then
595 X_LAST_UPDATED_BY := 1;
596 X_LAST_UPDATE_LOGIN := 0;
597 elsif (X_MODE = 'R') then
598 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
599 if X_LAST_UPDATED_BY is NULL then
600 X_LAST_UPDATED_BY := -1;
601 end if;
602 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
603 if X_LAST_UPDATE_LOGIN is NULL then
604 X_LAST_UPDATE_LOGIN := -1;
605 end if;
606 else
607 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
608 IGS_GE_MSG_STACK.ADD;
609 app_exception.raise_exception;
610 end if;
611 Before_DML(
612 p_action=>'INSERT',
613 x_rowid=>X_ROWID,
614 x_group_cd=>X_GROUP_CD,
615 x_name_cd=>X_NAME_CD,
616 x_description=>X_DESCRIPTION,
617 x_s_return_type=>X_S_RETURN_TYPE,
618 x_rug_sequence_number=>X_RUG_SEQUENCE_NUMBER,
619 x_select_group=>X_SELECT_GROUP,
620 x_message_group=>X_MESSAGE_GROUP,
621 x_creation_date=>X_LAST_UPDATE_DATE,
622 x_created_by=>X_LAST_UPDATED_BY,
623 x_last_update_date=>X_LAST_UPDATE_DATE,
624 x_last_updated_by=>X_LAST_UPDATED_BY,
625 x_last_update_login=>X_LAST_UPDATE_LOGIN);
626 insert into IGS_RU_NAMED_RULE_GR (
627 GROUP_CD
628 ,NAME_CD
629 ,DESCRIPTION
630 ,S_RETURN_TYPE
631 ,RUG_SEQUENCE_NUMBER
632 ,SELECT_GROUP
633 ,MESSAGE_GROUP
634 ,CREATION_DATE
635 ,CREATED_BY
636 ,LAST_UPDATE_DATE
637 ,LAST_UPDATED_BY
638 ,LAST_UPDATE_LOGIN
639 ) values (
640 NEW_REFERENCES.GROUP_CD
641 ,NEW_REFERENCES.NAME_CD
642 ,NEW_REFERENCES.DESCRIPTION
643 ,NEW_REFERENCES.S_RETURN_TYPE
644 ,NEW_REFERENCES.RUG_SEQUENCE_NUMBER
645 ,NEW_REFERENCES.SELECT_GROUP
646 ,NEW_REFERENCES.MESSAGE_GROUP
647 ,X_LAST_UPDATE_DATE
648 ,X_LAST_UPDATED_BY
649 ,X_LAST_UPDATE_DATE
650 ,X_LAST_UPDATED_BY
651 ,X_LAST_UPDATE_LOGIN
652 );
653 open c;
654 fetch c into X_ROWID;
655 if (c%notfound) then
656 close c;
657 raise no_data_found;
658 end if;
659 close c;
660 After_DML (
661 p_action => 'INSERT' ,
662 x_rowid => X_ROWID );
663 end INSERT_ROW;
664 procedure LOCK_ROW (
665 X_ROWID in VARCHAR2,
666 x_GROUP_CD IN VARCHAR2,
667 x_NAME_CD IN VARCHAR2,
668 x_DESCRIPTION IN VARCHAR2,
669 x_S_RETURN_TYPE IN VARCHAR2,
670 x_RUG_SEQUENCE_NUMBER IN NUMBER,
671 x_SELECT_GROUP IN NUMBER,
672 x_MESSAGE_GROUP IN NUMBER ) AS
673
674 /*************************************************************
675 Created By : tray
676 Date Created By : 10.05.2000
677 Purpose :
678 Know limitations, enhancements or remarks
679 Change History
680 Who When What
681
682 (reverse chronological order - newest change first)
683 ***************************************************************/
684
685 cursor c1 is select
686 DESCRIPTION
687 , S_RETURN_TYPE
688 , RUG_SEQUENCE_NUMBER
689 , SELECT_GROUP
690 , MESSAGE_GROUP
691 from IGS_RU_NAMED_RULE_GR
692 where ROWID = X_ROWID
693 for update nowait;
694 tlinfo c1%rowtype;
695 begin
696 open c1;
697 fetch c1 into tlinfo;
698 if (c1%notfound) then
699 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
700 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_NAMED_RULE_GR : P_ACTION LOCK_ROW : IGSUI17B.PLS');
701 IGS_GE_MSG_STACK.ADD;
702 close c1;
703 app_exception.raise_exception;
704 return;
705 end if;
706 close c1;
707 if ( ( tlinfo.DESCRIPTION = X_DESCRIPTION)
708 AND (tlinfo.S_RETURN_TYPE = X_S_RETURN_TYPE)
709 AND (tlinfo.RUG_SEQUENCE_NUMBER = X_RUG_SEQUENCE_NUMBER)
710 AND (tlinfo.SELECT_GROUP = X_SELECT_GROUP)
711 AND ((tlinfo.MESSAGE_GROUP = X_MESSAGE_GROUP)
712 OR ((tlinfo.MESSAGE_GROUP is null)
713 AND (X_MESSAGE_GROUP is null)))
714 ) then
715 null;
716 else
717 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
718 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_NAMED_RULE_GR : P_ACTION LOCK_ROW FORM_RECORD_CHANGED : IGSUI17B.PLS');
719 IGS_GE_MSG_STACK.ADD;
720 app_exception.raise_exception;
721 end if;
722 return;
723 end LOCK_ROW;
724 Procedure UPDATE_ROW (
725 X_ROWID in VARCHAR2,
726 x_GROUP_CD IN VARCHAR2,
727 x_NAME_CD IN VARCHAR2,
728 x_DESCRIPTION IN VARCHAR2,
729 x_S_RETURN_TYPE IN VARCHAR2,
730 x_RUG_SEQUENCE_NUMBER IN NUMBER,
731 x_SELECT_GROUP IN NUMBER,
732 x_MESSAGE_GROUP IN NUMBER,
733 X_MODE in VARCHAR2
734 ) AS
735
736 /*************************************************************
737 Created By : tray
738 Date Created By : 10.05.2000
739 Purpose :
740 Know limitations, enhancements or remarks
741 Change History
742 Who When What
743
744 (reverse chronological order - newest change first)
745 ***************************************************************/
746
747 X_LAST_UPDATE_DATE DATE ;
748 X_LAST_UPDATED_BY NUMBER ;
749 X_LAST_UPDATE_LOGIN NUMBER ;
750 begin
751 X_LAST_UPDATE_DATE := SYSDATE;
752 if(X_MODE = 'I') then
753 X_LAST_UPDATED_BY := 1;
754 X_LAST_UPDATE_LOGIN := 0;
755 elsif (X_MODE = 'R') then
756 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
757 if X_LAST_UPDATED_BY is NULL then
758 X_LAST_UPDATED_BY := -1;
759 end if;
760 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
761 if X_LAST_UPDATE_LOGIN is NULL then
762 X_LAST_UPDATE_LOGIN := -1;
763 end if;
764 else
765 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
766 IGS_GE_MSG_STACK.ADD;
767 app_exception.raise_exception;
768 end if;
769 Before_DML(
770 p_action=>'UPDATE',
771 x_rowid=>X_ROWID,
772 x_group_cd=>X_GROUP_CD,
773 x_name_cd=>X_NAME_CD,
774 x_description=>X_DESCRIPTION,
775 x_s_return_type=>X_S_RETURN_TYPE,
776 x_rug_sequence_number=>X_RUG_SEQUENCE_NUMBER,
777 x_select_group=>X_SELECT_GROUP,
778 x_message_group=>X_MESSAGE_GROUP,
779 x_creation_date=>X_LAST_UPDATE_DATE,
780 x_created_by=>X_LAST_UPDATED_BY,
781 x_last_update_date=>X_LAST_UPDATE_DATE,
782 x_last_updated_by=>X_LAST_UPDATED_BY,
783 x_last_update_login=>X_LAST_UPDATE_LOGIN);
784 update IGS_RU_NAMED_RULE_GR set
785 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
786 S_RETURN_TYPE = NEW_REFERENCES.S_RETURN_TYPE,
787 RUG_SEQUENCE_NUMBER = NEW_REFERENCES.RUG_SEQUENCE_NUMBER,
788 SELECT_GROUP = NEW_REFERENCES.SELECT_GROUP,
789 MESSAGE_GROUP = NEW_REFERENCES.MESSAGE_GROUP,
790 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
791 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
792 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
793 where ROWID = X_ROWID;
794 if (sql%notfound) then
795 raise no_data_found;
796 end if;
797
798 After_DML (
799 p_action => 'UPDATE' ,
800 x_rowid => X_ROWID
801 );
802 end UPDATE_ROW;
803 procedure ADD_ROW (
804 X_ROWID in out NOCOPY VARCHAR2,
805 x_GROUP_CD IN VARCHAR2,
806 x_NAME_CD IN VARCHAR2,
807 x_DESCRIPTION IN VARCHAR2,
808 x_S_RETURN_TYPE IN VARCHAR2,
809 x_RUG_SEQUENCE_NUMBER IN NUMBER,
810 x_SELECT_GROUP IN NUMBER,
811 x_MESSAGE_GROUP IN NUMBER,
812 X_MODE in VARCHAR2
813 ) AS
814
815 /*************************************************************
816 Created By : tray
817 Date Created By : 10.05.2000
818 Purpose :
819 Know limitations, enhancements or remarks
820 Change History
821 Who When What
822
823 (reverse chronological order - newest change first)
824 ***************************************************************/
825
826 cursor c1 is select ROWID from IGS_RU_NAMED_RULE_GR
827 where GROUP_CD= X_GROUP_CD
828 and NAME_CD = X_NAME_CD
829 ;
830 begin
831 open c1;
832 fetch c1 into X_ROWID;
833 if (c1%notfound) then
834 close c1;
835 INSERT_ROW (
836 X_ROWID,
837 X_GROUP_CD,
838 X_NAME_CD,
839 X_DESCRIPTION,
840 X_S_RETURN_TYPE,
841 X_RUG_SEQUENCE_NUMBER,
842 X_SELECT_GROUP,
843 X_MESSAGE_GROUP,
844 X_MODE );
845 return;
846 end if;
847 close c1;
848 UPDATE_ROW (
849 X_ROWID,
850 X_GROUP_CD,
851 X_NAME_CD,
852 X_DESCRIPTION,
853 X_S_RETURN_TYPE,
854 X_RUG_SEQUENCE_NUMBER,
855 X_SELECT_GROUP,
856 X_MESSAGE_GROUP,
857 X_MODE );
858 end ADD_ROW;
859 procedure DELETE_ROW (
860 X_ROWID in VARCHAR2
861 ) AS
862
863 /*************************************************************
864 Created By : tray
865 Date Created By : 10.05.2000
866 Purpose :
867 Know limitations, enhancements or remarks
868 Change History
869 Who When What
870
871 (reverse chronological order - newest change first)
872 ***************************************************************/
873
874 begin
875 Before_DML (
876 p_action => 'DELETE',
877 x_rowid => X_ROWID
878 );
879 delete from IGS_RU_NAMED_RULE_GR
880 where ROWID = X_ROWID;
881 if (sql%notfound) then
882 raise no_data_found;
883 end if;
884 After_DML (
885 p_action => 'DELETE',
886 x_rowid => X_ROWID
887 );
888 end DELETE_ROW;
889 END igs_ru_named_rule_gr_pkg;
[Home] [Help]