1 package body IGS_RU_DESCRIPTION_PKG as
2 /* $Header: IGSUI03B.pls 115.14 2003/01/29 12:01:40 nshee ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_RU_DESCRIPTION%RowType;
6 new_references IGS_RU_DESCRIPTION%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_sequence_number IN NUMBER ,
12 x_s_return_type IN VARCHAR2 ,
13 x_rule_description IN VARCHAR2 ,
14 x_s_turin_function IN VARCHAR2 ,
15 x_parenthesis_ind IN VARCHAR2 ,
16 x_description IN VARCHAR2 ,
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 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_RU_DESCRIPTION
27 WHERE rowid = x_rowid;
28
29 BEGIN
30
31 l_rowid := x_rowid;
32
33 -- Code for setting the Old and New Reference Values.
34 -- Populate Old Values.
35 Open cur_old_ref_values;
36 Fetch cur_old_ref_values INTO old_references;
37 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
38 Close cur_old_ref_values;
39 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
40 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_DESCRIPTION : P_ACTION INSERT, VALIDATE_INSERT : IGSUI03B.PLS');
41 IGS_GE_MSG_STACK.ADD;
42 App_Exception.Raise_Exception;
43 Return;
44 END IF;
45 Close cur_old_ref_values;
46
47 -- Populate New Values.
48 new_references.sequence_number := x_sequence_number;
49 new_references.s_return_type := x_s_return_type;
50 new_references.rule_description := x_rule_description;
51 new_references.s_turin_function := x_s_turin_function;
52 new_references.parenthesis_ind := x_parenthesis_ind;
53 new_references.description := x_description;
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61 new_references.last_update_date := x_last_update_date;
62 new_references.last_updated_by := x_last_updated_by;
63 new_references.last_update_login := x_last_update_login;
64
65 END Set_Column_Values;
66
67 -- Trigger description :-
68 -- "OSS_TST".trg_rud_br_iu
69 -- BEFORE INSERT OR UPDATE
70 -- ON rule_description
71 -- FOR EACH ROW
72
73 PROCEDURE BeforeRowInsertUpdate1(
74 p_inserting IN BOOLEAN ,
75 p_updating IN BOOLEAN ,
76 p_deleting IN BOOLEAN
77 ) as
78 v_message_name Varchar2(30);
79 BEGIN
80 IF p_inserting OR p_updating
81 THEN
82 -- validate return type and IGS_RU_RULE description
83 IF IGS_RU_VAL_RUD.rulp_val_rud_desc(
84 old_references.sequence_number,
85 old_references.s_return_type,
86 old_references.rule_description,
87 old_references.s_turin_function,
88 new_references.s_return_type,
89 new_references.rule_description,
90 v_message_name) = FALSE
91 THEN
92 Fnd_Message.Set_Name('IGS',v_message_name);
93 IGS_GE_MSG_STACK.ADD;
94 App_Exception.Raise_Exception;
95 END IF;
96 END IF;
97
98
99 END BeforeRowInsertUpdate1;
100
101 -- Trigger description :-
102 -- "OSS_TST".trg_rud_ar_u
103 -- AFTER UPDATE
104 -- ON rule_description
105 -- FOR EACH ROW
106
107 PROCEDURE AfterRowUpdate2(
108 p_inserting IN BOOLEAN ,
109 p_updating IN BOOLEAN ,
110 p_deleting IN BOOLEAN
111 ) as
112 --
113 -- if named IGS_RU_RULE then update IGS_RU_RULE text
114 -- else retry with parent IGS_RU_RULE
115 --
116 PROCEDURE do_rule_text (
117 p_rule_number NUMBER )
118 as
119 v_rule_text IGS_RU_NAMED_RULE.rule_text%TYPE;
120 BEGIN
121 FOR nr IN (
122 SELECT rule_text
123 FROM IGS_RU_NAMED_RULE
124 WHERE rul_sequence_number = p_rule_number )
125 LOOP
126 -- if named IGS_RU_RULE then update IGS_RU_RULE text
127 v_rule_text := IGS_RU_GEN_006.RULP_GET_RULE(p_rule_number);-- Changed IGS_RU_GEN_003 to IGS_RU_GEN_006 As part of Seed Migration Build Bug :2233951. This approach is taken to resolve the release issues .
128 UPDATE IGS_RU_NAMED_RULE
129 SET rule_text = v_rule_text
130 WHERE rul_sequence_number = p_rule_number;
131 RETURN;
132 END LOOP;
133 -- else find the calling IGS_RU_RULE and try again
134 FOR rui IN (
135 SELECT rul_sequence_number
136 FROM IGS_RU_ITEM
137 WHERE rule_number = p_rule_number )
138 LOOP
139 do_rule_text(rui.rul_sequence_number);
140 END LOOP;
141 END do_rule_text;
142
143 BEGIN
144 IF p_updating AND
145 old_references.rule_description <> new_references.rule_description
146 THEN
147 IF New_References.S_TURIN_FUNCTION IS NOT NULL THEN
148 -- find all rules which use this turing function
149 FOR rui IN (
150 SELECT UNIQUE
151 rul_sequence_number
152 FROM IGS_RU_ITEM
153 WHERE turin_function = New_References.S_TURIN_FUNCTION )
154 LOOP
155 -- update the IGS_RU_RULE text of this named IGS_RU_RULE
156 do_rule_text(rui.rul_sequence_number);
157 END LOOP;
158 ELSE
159 -- find all rules which call this named IGS_RU_RULE
160 FOR nr IN (
161 SELECT UNIQUE
162 rui.rul_sequence_number
163 FROM IGS_RU_NAMED_RULE nr,
164 IGS_RU_ITEM rui
165 WHERE nr.rud_sequence_number = New_References.sequence_number
166 AND rui.named_rule = nr.rul_sequence_number )
167 LOOP
168 -- update the IGS_RU_RULE text of this named IGS_RU_RULE
169 do_rule_text(nr.rul_sequence_number);
170 END LOOP;
171 END IF;
172 END IF;
173
174 END AfterRowUpdate2;
175
176
177
178 PROCEDURE Check_Constraints (
179 Column_Name IN VARCHAR2 ,
180 Column_Value IN VARCHAR2
181 ) as
182 Begin
183
184 IF Column_Name is null THEN
185 NULL;
186 ELSIF upper(Column_name) = 'S_RETURN_TYPE' THEN
187 new_references.S_RETURN_TYPE:= COLUMN_VALUE ;
188
189 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' THEN
190 new_references.SEQUENCE_NUMBER:= igs_ge_number.to_num(COLUMN_VALUE) ;
191
192 ELSIF upper(Column_name) = 'PARENTHESIS_IND' THEN
193 new_references.PARENTHESIS_IND:= COLUMN_VALUE ;
194
195 END IF ;
196
197 IF upper(Column_name) = 'S_RETURN_TYPE' OR COLUMN_NAME IS NULL THEN
198 IF new_references.S_RETURN_TYPE<> upper(new_references.S_RETURN_TYPE) then
199 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception ;
202 END IF;
203
204 END IF ;
205
206 IF upper(Column_name) = 'SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
207 IF new_references.SEQUENCE_NUMBER < 0 or new_references.SEQUENCE_NUMBER > 999999 then
208 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception ;
211 END IF;
212
213 END IF ;
214
215 IF upper(Column_name) = 'PARENTHESIS_IND' OR COLUMN_NAME IS NULL THEN
216 IF new_references.PARENTHESIS_IND not in ('Y','N') then
217 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
218 IGS_GE_MSG_STACK.ADD;
219 App_Exception.Raise_Exception ;
220 END IF;
221
222 END IF ;
223
224
225 END Check_Constraints;
226
227 PROCEDURE Check_Parent_Existance as
228 BEGIN
229
230 IF (((old_references.s_return_type = new_references.s_return_type)) OR
231 ((new_references.s_return_type IS NULL))) THEN
232 NULL;
233 ELSE
234 IF not IGS_RU_RET_TYPE_PKG.Get_PK_For_Validation (
235 new_references.s_return_type
236 ) THEN
237 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
238 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_RET_TYPE : P_ACTION Check_Parent_Existance new_references.s_return_type : IGSUI03B.PLS');
239 IGS_GE_MSG_STACK.ADD;
240 App_Exception.Raise_Exception;
241 END IF;
242 END IF;
243 IF (((old_references.s_turin_function = new_references.s_turin_function)) OR
244 ((new_references.s_turin_function IS NULL))) THEN
245 NULL;
246 ELSE
247 IF NOT IGS_RU_TURIN_FNC_PKG.Get_PK_For_Validation (
248 new_references.s_turin_function
249 ) THEN
250 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
251 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_TURIN_FNC : P_ACTION Check_Parent_Existance new_references.s_turin_function : IGSUI03B.PLS');
252 IGS_GE_MSG_STACK.ADD;
253 App_Exception.Raise_Exception;
254 END IF;
255 END IF;
256 END Check_Parent_Existance;
257
258 PROCEDURE Check_Child_Existance as
259 BEGIN
260
261 IGS_RU_NAMED_RULE_PKG.GET_FK_IGS_RU_DESCRIPTION (
262 old_references.sequence_number
263 );
264
265 IGS_RU_GROUP_SET_PKG.GET_FK_IGS_RU_DESCRIPTION (
266 old_references.sequence_number
267 );
268
269 IGS_RU_CALL_PKG.GET_FK_IGS_RU_DESCRIPTION (
270 old_references.sequence_number
271 );
272
273 IGS_RU_TURIN_FNC_PKG.GET_FK_IGS_RU_DESCRIPTION (
274 old_references.sequence_number
275 );
276
277 END Check_Child_Existance;
278
279 PROCEDURE CHECK_UNIQUENESS as
280 BEGIN
281 IF GET_UK1_FOR_VALIDATION ( new_references.s_return_type ,
282 new_references.rule_description
283 ) THEN
284 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
285 IGS_GE_MSG_STACK.ADD;
286 App_Exception.Raise_Exception;
287 END IF;
288
289 END CHECK_UNIQUENESS ;
290
291 FUNCTION Get_PK_For_Validation (
292 x_sequence_number IN NUMBER
293 ) RETURN BOOLEAN
294 as
295
296 CURSOR cur_rowid IS
297 SELECT rowid
298 FROM IGS_RU_DESCRIPTION
299 WHERE sequence_number = x_sequence_number
300 FOR UPDATE NOWAIT;
301
302 lv_rowid cur_rowid%RowType;
303
304 BEGIN
305
306 Open cur_rowid;
307 Fetch cur_rowid INTO lv_rowid;
308
309 IF (cur_rowid%FOUND) THEN
310 Close cur_rowid;
311 Return (TRUE);
312 ELSE
313 Close cur_rowid;
314 Return (FALSE);
315 END IF;
316
317 END Get_PK_For_Validation;
318
319
320
321 FUNCTION GET_UK1_FOR_VALIDATION ( x_s_return_type varchar2 ,
322 x_rule_description varchar2
323 ) RETURN BOOLEAN as
324
325 CURSOR cur_rowid IS
326 SELECT rowid
327 FROM IGS_RU_DESCRIPTION
328 WHERE s_return_type = x_s_return_type
329 AND rule_description = x_rule_description
330 AND (l_rowid is null or rowid <> l_rowid)
331 FOR UPDATE NOWAIT;
332
333 lv_rowid cur_rowid%RowType;
334
335 BEGIN
336
337 Open cur_rowid;
338 Fetch cur_rowid INTO lv_rowid;
339
340 IF (cur_rowid%FOUND) THEN
341 Close cur_rowid;
342 Return (TRUE);
343 ELSE
344 Close cur_rowid;
345 Return (FALSE);
346 END IF;
347
348 END ;
349
350 PROCEDURE GET_FK_IGS_RU_RET_TYPE (
351 x_s_return_type IN VARCHAR2
352 ) as
353
354 CURSOR cur_rowid IS
355 SELECT rowid
356 FROM IGS_RU_DESCRIPTION
357 WHERE s_return_type = x_s_return_type ;
358
359 lv_rowid cur_rowid%RowType;
360
361 BEGIN
362
363 Open cur_rowid;
364 Fetch cur_rowid INTO lv_rowid;
365 IF (cur_rowid%FOUND) THEN
366 Close cur_rowid;
367 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RUD_SRRT_FK');
368 IGS_GE_MSG_STACK.ADD;
369 App_Exception.Raise_Exception;
370 Return;
371 END IF;
372 Close cur_rowid;
373
374 END GET_FK_IGS_RU_RET_TYPE;
375
376 PROCEDURE GET_FK_IGS_RU_TURIN_FNC (
377 x_s_turin_function IN VARCHAR2
378 ) as
379
380 CURSOR cur_rowid IS
381 SELECT rowid
382 FROM IGS_RU_DESCRIPTION
383 WHERE s_turin_function = x_s_turin_function ;
384
385 lv_rowid cur_rowid%RowType;
386
387 BEGIN
388
389 Open cur_rowid;
390 Fetch cur_rowid INTO lv_rowid;
391 IF (cur_rowid%FOUND) THEN
392 Close cur_rowid;
393 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RUD_STF_FK');
394 IGS_GE_MSG_STACK.ADD;
395 App_Exception.Raise_Exception;
396 Return;
397 END IF;
398 Close cur_rowid;
399
400 END GET_FK_IGS_RU_TURIN_FNC;
401
402 PROCEDURE Before_DML (
403 p_action IN VARCHAR2,
404 x_rowid IN VARCHAR2 ,
405 x_sequence_number IN NUMBER ,
406 x_s_return_type IN VARCHAR2 ,
407 x_rule_description IN VARCHAR2 ,
408 x_s_turin_function IN VARCHAR2 ,
409 x_parenthesis_ind IN VARCHAR2 ,
410 x_description IN VARCHAR2 ,
411 x_creation_date IN DATE ,
412 x_created_by IN NUMBER ,
413 x_last_update_date IN DATE ,
414 x_last_updated_by IN NUMBER ,
415 x_last_update_login IN NUMBER
416 ) as
417 BEGIN
418 Set_Column_Values (
419 p_action,
420 x_rowid,
421 x_sequence_number,
422 x_s_return_type,
423 x_rule_description,
424 x_s_turin_function,
425 x_parenthesis_ind,
426 x_description,
427 x_creation_date,
428 x_created_by,
429 x_last_update_date,
430 x_last_updated_by,
431 x_last_update_login
432 );
433
434 IF (p_action = 'INSERT') THEN
435 -- Call all the procedures related to Before Insert.
436 BeforeRowInsertUpdate1 ( p_inserting => TRUE, p_updating => FALSE, p_deleting => FALSE );
437 IF Get_PK_For_Validation (
438 new_references.sequence_number
439 ) THEN
440 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
441 IGS_GE_MSG_STACK.ADD;
442 App_Exception.Raise_Exception;
443 END IF;
444 Check_Uniqueness;
445 Check_Constraints;
446 Check_Parent_Existance;
447 ELSIF (p_action = 'UPDATE') THEN
448 -- Call all the procedures related to Before Update.
449 BeforeRowInsertUpdate1 ( p_inserting => TRUE, p_updating => TRUE, p_deleting => FALSE );
450 check_uniqueness;
451 Check_Constraints;
452 Check_Parent_Existance;
453 ELSIF (p_action = 'DELETE') THEN
454 -- Call all the procedures related to Before Delete.
455 Check_Child_Existance;
456 ELSIF (p_action = 'VALIDATE_INSERT') THEN
457 IF Get_PK_For_Validation (
458 new_references.sequence_number
459 ) THEN
460 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
461 IGS_GE_MSG_STACK.ADD;
462 App_Exception.Raise_Exception;
463 END IF;
464 check_uniqueness;
465 Check_Constraints;
466 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
467 check_uniqueness;
468 Check_Constraints;
469 ELSIF (p_action = 'VALIDATE_DELETE') THEN
470 Check_Child_Existance;
471 END IF;
472
473 END Before_DML;
474
475 PROCEDURE After_DML (
476 p_action IN VARCHAR2,
477 x_rowid IN VARCHAR2
478 ) as
479 BEGIN
480
481 l_rowid := x_rowid;
482
483 IF (p_action = 'UPDATE') THEN
484 -- Call all the procedures related to After Update.
485 AfterRowUpdate2 ( p_inserting => FALSE, p_updating => TRUE ,p_deleting => FALSE);
486 END IF;
487
488 END After_DML;
489
490 procedure INSERT_ROW (
491 X_ROWID in out NOCOPY VARCHAR2,
492 X_SEQUENCE_NUMBER in NUMBER,
493 X_S_RETURN_TYPE in VARCHAR2,
494 X_RULE_DESCRIPTION in VARCHAR2,
495 X_DESCRIPTION in VARCHAR2,
496 X_S_TURIN_FUNCTION in VARCHAR2,
497 X_PARENTHESIS_IND in VARCHAR2,
498 X_MODE in VARCHAR2
499 ) as
500 ------------------------------------------------------------------
501 --Created by : nsinha, Oracle India
502 --Date created: 12-Mar-2001
503 --
504 --Purpose: INSERT_ROW
505 --
506 --Known limitations/enhancements and/or remarks:
507 --
508 --Change History:
509 --Who When What
510 --kdande 15-Mar-2002 Bug # 2233951: The cursor C is being modified and cursor
511 -- cur_max_plus_one is being created. This is to ensure that
512 -- when a user defined rule is created,
513 -- it picks up a sequence number more than 500000.
514 -- rnirwani - 15-Mar-02 - 2233951 the cursor has been changed to do a select for update
515 -- so that parallel processing can be prevented.
516 -------------------------------------------------------------------
517 l_sequence_number NUMBER;
518 cursor C is select ROWID from IGS_RU_DESCRIPTION
519 where SEQUENCE_NUMBER = L_SEQUENCE_NUMBER;
520 CURSOR cur_max_plus_one IS
521 SELECT (a.sequence_number + 1) sequence_number
522 FROM igs_ru_description a
523 WHERE a.sequence_number = (SELECT MAX(b.sequence_number) FROM igs_ru_description b
524 WHERE b.sequence_number < 499999) FOR UPDATE OF a.sequence_number NOWAIT;
525 X_LAST_UPDATE_DATE DATE;
526 X_LAST_UPDATED_BY NUMBER;
527 X_LAST_UPDATE_LOGIN NUMBER;
528 begin
529 X_LAST_UPDATE_DATE := SYSDATE;
530 if(X_MODE = 'I') then
531 X_LAST_UPDATED_BY := 1;
532 X_LAST_UPDATE_LOGIN := 0;
533 elsif (X_MODE = 'R') then
534 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
535 if X_LAST_UPDATED_BY is NULL then
536 X_LAST_UPDATED_BY := -1;
537 end if;
538 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
539 if X_LAST_UPDATE_LOGIN is NULL then
540 X_LAST_UPDATE_LOGIN := -1;
541 end if;
542 else
543 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
544 IGS_GE_MSG_STACK.ADD;
545 app_exception.raise_exception;
546 end if;
547
548 Before_DML(
549 p_action=>'INSERT',
550 x_rowid=>X_ROWID,
551 x_description=>X_DESCRIPTION,
552 x_parenthesis_ind=>X_PARENTHESIS_IND,
553 x_rule_description=>X_RULE_DESCRIPTION,
554 x_s_return_type=>X_S_RETURN_TYPE,
555 x_s_turin_function=>X_S_TURIN_FUNCTION,
556 x_sequence_number=>X_SEQUENCE_NUMBER,
557 x_creation_date=>X_LAST_UPDATE_DATE,
558 x_created_by=>X_LAST_UPDATED_BY,
559 x_last_update_date=>X_LAST_UPDATE_DATE,
560 x_last_updated_by=>X_LAST_UPDATED_BY,
561 x_last_update_login=>X_LAST_UPDATE_LOGIN
562 );
563 --
564 -- If the sequence number is passed as a NULL value then generate it.
565 --
566 IF (fnd_global.user_id = 1) THEN
567 --
568 -- If the sequence number is passed as a NULL value then generate it.
569 -- If the User creating this record is DATAMERGE (id = 1) then
570 -- Get the sequence as the existing maximum value + 1
571 --
572 IF (x_sequence_number IS NULL) THEN
573 OPEN cur_max_plus_one;
574 FETCH cur_max_plus_one INTO l_sequence_number;
575 CLOSE cur_max_plus_one;
576 ELSE
577 l_sequence_number := x_sequence_number;
578 END IF;
579 --
580 -- Seeded Sequences can go upto 499999 only else raise an error
581 --
582 IF (l_sequence_number > 499999) THEN
583 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
584 IGS_GE_MSG_STACK.ADD;
585 App_Exception.Raise_Exception ;
586 END IF;
587 ELSE
588 l_sequence_number := NEW_REFERENCES.SEQUENCE_NUMBER;
589 END IF;
590 insert into IGS_RU_DESCRIPTION (
591 SEQUENCE_NUMBER,
592 S_RETURN_TYPE,
593 RULE_DESCRIPTION,
594 DESCRIPTION,
595 S_TURIN_FUNCTION,
596 PARENTHESIS_IND,
597 CREATION_DATE,
598 CREATED_BY,
599 LAST_UPDATE_DATE,
600 LAST_UPDATED_BY,
601 LAST_UPDATE_LOGIN
602 ) values (
603 l_sequence_number,
604 NEW_REFERENCES.S_RETURN_TYPE,
605 NEW_REFERENCES.RULE_DESCRIPTION,
606 NEW_REFERENCES.DESCRIPTION,
607 NEW_REFERENCES.S_TURIN_FUNCTION,
608 NEW_REFERENCES.PARENTHESIS_IND,
609 X_LAST_UPDATE_DATE,
610 X_LAST_UPDATED_BY,
611 X_LAST_UPDATE_DATE,
612 X_LAST_UPDATED_BY,
613 X_LAST_UPDATE_LOGIN
614 );
615
616 open c;
617 fetch c into X_ROWID;
618 if (c%notfound) then
619 close c;
620 raise no_data_found;
621 end if;
622 close c;
623
624 After_DML (
625 p_action => 'INSERT',
626 x_rowid => X_ROWID);
627
628 end INSERT_ROW;
629
630 procedure LOCK_ROW (
631 X_ROWID in VARCHAR2,
632 X_SEQUENCE_NUMBER in NUMBER,
633 X_S_RETURN_TYPE in VARCHAR2,
634 X_RULE_DESCRIPTION in VARCHAR2,
635 X_DESCRIPTION in VARCHAR2,
636 X_S_TURIN_FUNCTION in VARCHAR2,
637 X_PARENTHESIS_IND in VARCHAR2
638 ) as
639 cursor c1 is select
640 S_RETURN_TYPE,
641 RULE_DESCRIPTION,
642 DESCRIPTION,
643 S_TURIN_FUNCTION,
644 PARENTHESIS_IND
645 from IGS_RU_DESCRIPTION
646 where ROWID = X_ROWID for update nowait;
647 tlinfo c1%rowtype;
648
649 begin
650 open c1;
651 fetch c1 into tlinfo;
652 if (c1%notfound) then
653 close c1;
654 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
655 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_DESCRIPTION : P_ACTION LOCK_ROW : IGSUI03B.PLS');
656 IGS_GE_MSG_STACK.ADD;
657 app_exception.raise_exception;
658 return;
659 end if;
660 close c1;
661
662 if ( (tlinfo.S_RETURN_TYPE = X_S_RETURN_TYPE)
663 AND (RTRIM(tlinfo.RULE_DESCRIPTION) = X_RULE_DESCRIPTION) --nshee, bug 2774952
664 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
665 OR ((tlinfo.DESCRIPTION is null)
666 AND (X_DESCRIPTION is null)))
667 AND ((tlinfo.S_TURIN_FUNCTION = X_S_TURIN_FUNCTION)
668 OR ((tlinfo.S_TURIN_FUNCTION is null)
669 AND (X_S_TURIN_FUNCTION is null)))
670 AND ((tlinfo.PARENTHESIS_IND = X_PARENTHESIS_IND)
671 OR ((tlinfo.PARENTHESIS_IND is null)
672 AND (X_PARENTHESIS_IND is null)))
673 ) then
674 null;
675 else
676 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
677 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_DESCRIPTION : P_ACTION LOCK_ROW FORM_RECORD_CHANGED : IGSUI03B.PLS');
678 IGS_GE_MSG_STACK.ADD;
679 app_exception.raise_exception;
680 end if;
681 return;
682 end LOCK_ROW;
683
684 procedure UPDATE_ROW (
685 X_ROWID in VARCHAR2,
686 X_SEQUENCE_NUMBER in NUMBER,
687 X_S_RETURN_TYPE in VARCHAR2,
688 X_RULE_DESCRIPTION in VARCHAR2,
689 X_DESCRIPTION in VARCHAR2,
690 X_S_TURIN_FUNCTION in VARCHAR2,
691 X_PARENTHESIS_IND in VARCHAR2,
692 X_MODE in VARCHAR2
693 )as
694 X_LAST_UPDATE_DATE DATE;
695 X_LAST_UPDATED_BY NUMBER;
696 X_LAST_UPDATE_LOGIN NUMBER;
697 begin
698 X_LAST_UPDATE_DATE := SYSDATE;
699 if(X_MODE = 'I') then
700 X_LAST_UPDATED_BY := 1;
701 X_LAST_UPDATE_LOGIN := 0;
702 elsif (X_MODE = 'R') then
703 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
704 if X_LAST_UPDATED_BY is NULL then
705 X_LAST_UPDATED_BY := -1;
706 end if;
707 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
708 if X_LAST_UPDATE_LOGIN is NULL then
709 X_LAST_UPDATE_LOGIN := -1;
710 end if;
711 else
712 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
713 IGS_GE_MSG_STACK.ADD;
714 app_exception.raise_exception;
715 end if;
716
717 Before_DML(
718 p_action=>'UPDATE',
719 x_rowid=>X_ROWID,
720 x_description=>X_DESCRIPTION,
721 x_parenthesis_ind=>X_PARENTHESIS_IND,
722 x_rule_description=>X_RULE_DESCRIPTION,
723 x_s_return_type=>X_S_RETURN_TYPE,
724 x_s_turin_function=>X_S_TURIN_FUNCTION,
725 x_sequence_number=>X_SEQUENCE_NUMBER,
726 x_creation_date=>X_LAST_UPDATE_DATE,
727 x_created_by=>X_LAST_UPDATED_BY,
728 x_last_update_date=>X_LAST_UPDATE_DATE,
729 x_last_updated_by=>X_LAST_UPDATED_BY,
730 x_last_update_login=>X_LAST_UPDATE_LOGIN
731 );
732
733 update IGS_RU_DESCRIPTION set
734 S_RETURN_TYPE = NEW_REFERENCES.S_RETURN_TYPE,
735 RULE_DESCRIPTION = NEW_REFERENCES.RULE_DESCRIPTION,
736 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
737 S_TURIN_FUNCTION = NEW_REFERENCES.S_TURIN_FUNCTION,
738 PARENTHESIS_IND = NEW_REFERENCES.PARENTHESIS_IND,
739 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
740 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
741 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
742 where ROWID = X_ROWID
743 ;
744 if (sql%notfound) then
745 raise no_data_found;
746 end if;
747
748 After_DML (
749 p_action => 'UPDATE',
750 x_rowid => X_ROWID);
751
752 end UPDATE_ROW;
753
754 procedure ADD_ROW (
755 X_ROWID in out NOCOPY VARCHAR2,
756 X_SEQUENCE_NUMBER in NUMBER,
757 X_S_RETURN_TYPE in VARCHAR2,
758 X_RULE_DESCRIPTION in VARCHAR2,
759 X_DESCRIPTION in VARCHAR2,
760 X_S_TURIN_FUNCTION in VARCHAR2,
761 X_PARENTHESIS_IND in VARCHAR2,
762 X_MODE in VARCHAR2
763 )as
764 cursor c1 is select rowid from IGS_RU_DESCRIPTION
765 where SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
766 ;
767 begin
768 open c1;
769 fetch c1 into X_ROWID;
770 if (c1%notfound) then
771 close c1;
772 INSERT_ROW (
773 X_ROWID,
774 X_SEQUENCE_NUMBER,
775 X_S_RETURN_TYPE,
776 X_RULE_DESCRIPTION,
777 X_DESCRIPTION,
778 X_S_TURIN_FUNCTION,
779 X_PARENTHESIS_IND,
780 X_MODE);
781 return;
782 end if;
783 close c1;
784 UPDATE_ROW (
785 X_ROWID,
786 X_SEQUENCE_NUMBER,
787 X_S_RETURN_TYPE,
788 X_RULE_DESCRIPTION,
789 X_DESCRIPTION,
790 X_S_TURIN_FUNCTION,
791 X_PARENTHESIS_IND,
792 X_MODE);
793 end ADD_ROW;
794
795 procedure DELETE_ROW (
796 X_ROWID in VARCHAR2
797 ) as
798 begin
799
800 Before_DML (
801 p_action => 'DELETE',
802 x_rowid => X_ROWID);
803
804 delete from IGS_RU_DESCRIPTION
805 where ROWID = X_ROWID;
806 if (sql%notfound) then
807 raise no_data_found;
808 end if;
809
810 After_DML (
811 p_action => 'DELETE',
812 x_rowid => X_ROWID);
813
814 end DELETE_ROW;
815
816 end IGS_RU_DESCRIPTION_PKG;
[Home] [Help]