1 package body IGS_RU_CALL_PKG AS
2 /* $Header: IGSUI01B.pls 115.11 2002/11/29 04:24:40 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_RU_CALL%RowType;
5 new_references IGS_RU_CALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_s_rule_call_cd IN VARCHAR2 ,
11 x_s_rule_type_cd IN VARCHAR2 ,
12 x_rud_sequence_number IN NUMBER ,
13 x_true_message IN VARCHAR2 ,
14 x_false_message IN VARCHAR2 ,
15 x_default_rule IN NUMBER ,
16 x_rug_sequence_number IN NUMBER ,
17 x_select_group IN NUMBER ,
18 x_creation_date IN DATE ,
19 x_created_by IN NUMBER ,
20 x_last_update_date IN DATE ,
21 x_last_updated_by IN NUMBER ,
22 x_last_update_login IN NUMBER ) AS
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_RU_CALL
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_CALL : P_ACTION Insert, validate Insert : IGSUI01B.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.s_rule_call_cd := x_s_rule_call_cd;
49 new_references.s_rule_type_cd := x_s_rule_type_cd;
50 new_references.rud_sequence_number := x_rud_sequence_number;
51 new_references.true_message := x_true_message;
52 new_references.false_message := x_false_message;
53 new_references.default_rule := x_default_rule;
54 new_references.rug_sequence_number := x_rug_sequence_number;
55 new_references.select_group := x_select_group;
56 IF (p_action = 'UPDATE') THEN
57 new_references.creation_date := old_references.creation_date;
58 new_references.created_by := old_references.created_by;
59 ELSE
60 new_references.creation_date := x_creation_date;
61 new_references.created_by := x_created_by;
62 END IF;
63 new_references.last_update_date := x_last_update_date;
64 new_references.last_updated_by := x_last_updated_by;
65 new_references.last_update_login := x_last_update_login;
66
67 END Set_Column_Values;
68
69
70 PROCEDURE Check_Parent_Existance AS
71 BEGIN
72
73 IF (((old_references.default_rule = new_references.default_rule)) OR
74 ((new_references.default_rule IS NULL))) THEN
75 NULL;
76 ELSE
77 IF NOT IGS_RU_NAMED_RULE_PKG.Get_PK_For_Validation (
78 new_references.default_rule
79 )THEN
80 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
81 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_NAMED_RULE : P_ACTION Check_Parent_Existance new_references.default_rule : IGSUI01B.PLS');
82 IGS_GE_MSG_STACK.ADD;
83 App_Exception.Raise_Exception;
84
85 END IF;
86
87 END IF;
88
89 IF (((old_references.rud_sequence_number = new_references.rud_sequence_number)) OR
90 ((new_references.rud_sequence_number IS NULL))) THEN
91 NULL;
92 ELSE
93 IF NOT IGS_RU_DESCRIPTION_PKG.Get_PK_For_Validation (
94 new_references.rud_sequence_number
95 )THEN
96 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
97 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_DESCRIPTION : P_ACTION Check_Parent_Existance new_references.rud_sequence_number : IGSUI01B.PLS');
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100
101 END IF;
102
103 END IF;
104
105 IF (((old_references.rug_sequence_number = new_references.rug_sequence_number)) OR
106 ((new_references.rug_sequence_number IS NULL))) THEN
107 NULL;
108 ELSE
109 IF NOT IGS_RU_GROUP_PKG.Get_PK_For_Validation (
110 new_references.rug_sequence_number
111 )THEN
112 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
113 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_GROUP : P_ACTION Check_Parent_Existance new_references.rug_sequence_number : IGSUI01B.PLS');
114 IGS_GE_MSG_STACK.ADD;
115 App_Exception.Raise_Exception;
116
117 END IF;
118
119 END IF;
120
121 IF (((old_references.select_group = new_references.select_group)) OR
122 ((new_references.select_group IS NULL))) THEN
123 NULL;
124 ELSE
125 IF NOT IGS_RU_GROUP_PKG.Get_PK_For_Validation (
126 new_references.select_group
127 )THEN
128 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
129 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_GROUP : P_ACTION Check_Parent_Existance new_references.select_group : IGSUI01B.PLS');
130 IGS_GE_MSG_STACK.ADD;
131 App_Exception.Raise_Exception;
132
133 END IF;
134
135 END IF;
136
137 END Check_Parent_Existance;
138
139 PROCEDURE Check_UK_Child_Existance AS
140 BEGIN
141
142 IF (old_references.rud_sequence_number = new_references.rud_sequence_number) OR
143 (old_references.rud_sequence_number IS NULL)
144 THEN
145 NULL;
146 ELSE
147 IGS_RU_ITEM_PKG.GET_UFK_IGS_RU_CALL(
148 old_references.rud_sequence_number
149 );
150 END IF;
151
152 END Check_UK_Child_Existance;
153
154
155 FUNCTION Get_PK_For_Validation (
156 x_s_rule_call_cd IN VARCHAR2
157 )
158 RETURN BOOLEAN AS
159
160 CURSOR cur_rowid IS
161 SELECT rowid
162 FROM IGS_RU_CALL
163 WHERE s_rule_call_cd = x_s_rule_call_cd
164 FOR UPDATE NOWAIT;
165
166 lv_rowid cur_rowid%RowType;
167
168 BEGIN
169
170 Open cur_rowid;
171 Fetch cur_rowid INTO lv_rowid;
172 IF (cur_rowid%FOUND) THEN
173 Close cur_rowid;
174 Return TRUE;
175 ELSE
176 Close cur_rowid;
177 Return FALSE;
178 END IF;
179
180 END Get_PK_For_Validation;
181
182
183
184 FUNCTION Get_UK1_For_Validation (
185 x_rud_sequence_number IN NUMBER
186 )
187 RETURN BOOLEAN AS
188 CURSOR cur_rowid IS
189 SELECT rowid
190 FROM IGS_RU_CALL
191 WHERE rud_sequence_number = x_rud_sequence_number
192 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
193
194
195 lv_rowid cur_rowid%RowType;
196 BEGIN
197 Open cur_rowid;
198 Fetch cur_rowid INTO lv_rowid;
199 IF (cur_rowid%FOUND) THEN
200 Close cur_rowid;
201 Return TRUE;
202 ELSE
203 Close cur_rowid;
204 Return FALSE;
205 END IF;
206 END Get_UK1_For_Validation;
207
208
209 PROCEDURE GET_FK_IGS_RU_NAMED_RULE (
210 x_rul_sequence_number IN NUMBER
211 ) AS
212
213 CURSOR cur_rowid IS
214 SELECT rowid
215 FROM IGS_RU_CALL
216 WHERE default_rule = x_rul_sequence_number ;
217
218 lv_rowid cur_rowid%RowType;
219
220 BEGIN
221
222 Open cur_rowid;
223 Fetch cur_rowid INTO lv_rowid;
224 IF (cur_rowid%FOUND) THEN
225 Close cur_rowid;
226 Fnd_Message.Set_Name ('IGS', 'IGS_RU_SRC_NR_FK');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception;
229 Return;
230 END IF;
231 Close cur_rowid;
232
233 END GET_FK_IGS_RU_NAMED_RULE;
234
235 PROCEDURE GET_FK_IGS_RU_DESCRIPTION (
236 x_sequence_number IN NUMBER
237 ) AS
238
239 CURSOR cur_rowid IS
240 SELECT rowid
241 FROM IGS_RU_CALL
242 WHERE rud_sequence_number = x_sequence_number ;
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 Fnd_Message.Set_Name ('IGS', 'IGS_RU_SRC_RUD_FK');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 Return;
256 END IF;
257 Close cur_rowid;
258
259 END GET_FK_IGS_RU_DESCRIPTION;
260
261
262 PROCEDURE GET_FK_IGS_RU_GROUP_SG (
263 x_sequence_number IN NUMBER
264 ) AS
265
266 CURSOR cur_rowid IS
267 SELECT rowid
268 FROM IGS_RU_CALL
269 WHERE rug_sequence_number = x_sequence_number
270 OR select_group = x_sequence_number ;
271
272 lv_rowid cur_rowid%RowType;
273
274 BEGIN
275
276 Open cur_rowid;
277 Fetch cur_rowid INTO lv_rowid;
278 IF (cur_rowid%FOUND) THEN
279 Close cur_rowid;
280 Fnd_Message.Set_Name ('IGS', 'IGS_RU_SRC_RUG_SG_FK');
281 IGS_GE_MSG_STACK.ADD;
282 App_Exception.Raise_Exception;
283 Return;
284 END IF;
285 Close cur_rowid;
286
287 END GET_FK_IGS_RU_GROUP_SG;
288
289 PROCEDURE Get_FK_IGS_RU_GROUP_SEQ (
290 x_sequence_number IN NUMBER
291 ) AS
292
293 CURSOR cur_rowid IS
294 SELECT rowid
295 FROM igs_ru_call
296 WHERE rug_sequence_number = x_sequence_number ;
297
298 lv_rowid cur_rowid%RowType;
299
300 BEGIN
301
302 Open cur_rowid;
303 Fetch cur_rowid INTO lv_rowid;
304 IF (cur_rowid%FOUND) THEN
305 Close cur_rowid;
306 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
307 App_Exception.Raise_Exception;
308 Return;
309 END IF;
310 Close cur_rowid;
311 END Get_FK_IGS_RU_GROUP_SEQ ;
312
313 PROCEDURE CHECK_UNIQUENESS AS
314
315 BEGIN
316
317 IF GET_UK1_FOR_VALIDATION(
318 new_references.rud_sequence_number) THEN
319 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
320 IGS_GE_MSG_STACK.ADD;
321 APP_EXCEPTION.RAISE_EXCEPTION;
322 END IF;
323
324
325 END CHECK_UNIQUENESS;
326
327 PROCEDURE Before_DML (
328 p_action IN VARCHAR2,
329 x_rowid IN VARCHAR2 ,
330 x_s_rule_call_cd IN VARCHAR2 ,
331 x_s_rule_type_cd IN VARCHAR2 ,
332 x_rud_sequence_number IN NUMBER ,
333 x_true_message IN VARCHAR2 ,
334 x_false_message IN VARCHAR2 ,
335 x_default_rule IN NUMBER ,
336 x_rug_sequence_number IN NUMBER ,
337 x_select_group IN NUMBER ,
338 x_creation_date IN DATE ,
339 x_created_by IN NUMBER ,
340 x_last_update_date IN DATE ,
341 x_last_updated_by IN NUMBER ,
342 x_last_update_login IN NUMBER
343 ) AS
344 BEGIN
345
346 Set_Column_Values (
347 p_action,
348 x_rowid,
349 x_s_rule_call_cd,
350 x_s_rule_type_cd,
351 x_rud_sequence_number,
352 x_true_message,
353 x_false_message,
354 x_default_rule,
355 x_rug_sequence_number,
356 x_select_group,
357 x_creation_date,
358 x_created_by,
359 x_last_update_date,
360 x_last_updated_by,
361 x_last_update_login
362 );
363
364 IF (p_action = 'INSERT') THEN
365 -- Call all the procedures related to Before Insert.
366 Null;
367 IF GET_PK_FOR_VALIDATION(
368 new_references.s_rule_call_cd )THEN
369 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
370 IGS_GE_MSG_STACK.ADD;
371 APP_EXCEPTION.RAISE_EXCEPTION;
372 END IF;
373 --Check_Unique (x_rowid);
374 Check_uniqueness;
375 Check_constraints;
376 Check_Parent_Existance;
377 ELSIF (p_action = 'UPDATE') THEN
378 -- Call all the procedures related to Before Update.
379 Check_uniqueness;
380 Check_constraints;
381 Check_Parent_Existance;
382 Check_UK_Child_Existance;
383 ELSIF (p_action = 'DELETE') THEN
384 -- Call all the procedures related to Before Delete.
385 --
386 -- svenkata - This table handler is released as part of IGS specific forms in SEED . As a consequence ,
387 -- the procedure Check_Child_Existance originally a part of this package , has been moved to Igs_Ru_Gen_005 .
388 -- This was done 'cos Check_Child_Existance makes calls to other procedures which are not being shipped !
389 -- Check_Child_Existance is called only when the user is not DATAMERGE . Hence , the proc.
390 -- Check_Child_Existance_ru_rule is being called using execute immediate only if the user is not DATAMERGE .
391 -- Bug # 2233951
392 --
393 IF (fnd_global.user_id <> 1) THEN
394 -- do execute immediate
395 EXECUTE IMMEDIATE 'BEGIN Igs_Ru_Gen_005.Check_Child_Existance_Ru_Call(:1,:2); END;'
396 USING
397 old_references.rud_sequence_number ,
398 old_references.s_rule_call_cd ;
399 END IF;
400
401 ELSIF (p_action = 'VALIDATE_INSERT') THEN
402 -- Call all the procedures related to Before Delete.
403 IF GET_PK_FOR_VALIDATION(
404 new_references.s_rule_call_cd
405 )THEN
406 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
407 IGS_GE_MSG_STACK.ADD;
408 APP_EXCEPTION.RAISE_EXCEPTION;
409 END IF;
410 Check_uniqueness;
411 Check_Constraints;
412 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
413 -- Call all the procedures related to Before Delete.
414 Check_uniqueness;
415 check_constraints;
416 Check_uk_child_existance;
417 ELSIF (p_action = 'VALIDATE_DELETE') THEN
418 -- Call all the procedures related to Before Delete.
419 --
420 -- svenkata - This table handler is released as part of IGS specific forms in SEED . As a consequence ,
421 -- the procedure Check_Child_Existance originally a part of this package , has been moved to Igs_Ru_Gen_005 .
422 -- This was done 'cos Check_Child_Existance makes calls to other procedures which are not being shipped !
423 -- Check_Child_Existance is called only when the user is not DATAMERGE . Hence , the proc.
424 -- Check_Child_Existance_ru_rule is being called using execute immediate only if the user is not DATAMERGE .
425 -- Bug # 2233951
426 --
427 IF (fnd_global.user_id <> 1) THEN
428 -- do execute immediate
429 EXECUTE IMMEDIATE 'BEGIN Igs_Ru_Gen_005.Check_Child_Existance_Ru_Call(:1,:2); END;'
430 USING
431 old_references.rud_sequence_number ,
432 old_references.s_rule_call_cd ;
433 END IF;
434
435 END IF;
436
437 END Before_DML;
438
439 PROCEDURE After_DML (
440 p_action IN VARCHAR2,
441 x_rowid IN VARCHAR2
442 ) AS
443 BEGIN
444
445 l_rowid := x_rowid;
446
447 l_rowid := NULL;
448
449 END After_DML;
450
451 procedure INSERT_ROW (
452 X_ROWID in out NOCOPY VARCHAR2,
453 X_S_RULE_CALL_CD in VARCHAR2,
454 X_S_RULE_TYPE_CD in VARCHAR2,
455 X_RUD_SEQUENCE_NUMBER in NUMBER,
456 X_SELECT_GROUP in NUMBER,
457 X_TRUE_MESSAGE in VARCHAR2,
458 X_FALSE_MESSAGE in VARCHAR2,
459 X_DEFAULT_RULE in NUMBER,
460 X_RUG_SEQUENCE_NUMBER in NUMBER,
461 X_MODE in VARCHAR2
462 ) AS
463 cursor C is select ROWID from IGS_RU_CALL
464 where S_RULE_CALL_CD = X_S_RULE_CALL_CD;
465 X_LAST_UPDATE_DATE DATE;
466 X_LAST_UPDATED_BY NUMBER;
467 X_LAST_UPDATE_LOGIN NUMBER;
468 begin
469 X_LAST_UPDATE_DATE := SYSDATE;
470 if(X_MODE = 'I') then
471 X_LAST_UPDATED_BY := 1;
472 X_LAST_UPDATE_LOGIN := 0;
473 elsif (X_MODE = 'R') then
474 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
475 if X_LAST_UPDATED_BY is NULL then
476 X_LAST_UPDATED_BY := -1;
477 end if;
478 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
479 if X_LAST_UPDATE_LOGIN is NULL then
480 X_LAST_UPDATE_LOGIN := -1;
481 end if;
482 else
483 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
484 IGS_GE_MSG_STACK.ADD;
485 app_exception.raise_exception;
486 end if;
487
488 Before_DML (
489 p_action => 'INSERT',
490 x_rowid => X_ROWID,
491 x_s_rule_call_cd =>X_S_RULE_CALL_CD,
492 x_s_rule_type_cd =>X_S_RULE_TYPE_CD,
493 x_rud_sequence_number =>X_RUD_SEQUENCE_NUMBER,
494 x_true_message =>X_TRUE_MESSAGE,
495 x_false_message =>X_FALSE_MESSAGE,
496 x_default_rule =>X_DEFAULT_RULE,
497 x_rug_sequence_number =>X_RUG_SEQUENCE_NUMBER,
498 x_select_group =>X_SELECT_GROUP,
499 x_creation_date =>X_LAST_UPDATE_DATE,
500 x_created_by =>X_LAST_UPDATED_BY,
501 x_last_update_date =>X_LAST_UPDATE_DATE,
502 x_last_updated_by =>X_LAST_UPDATED_BY,
503 x_last_update_login=> X_LAST_UPDATE_LOGIN
504 );
505
506 insert into IGS_RU_CALL (
507 S_RULE_TYPE_CD,
508 S_RULE_CALL_CD,
509 RUD_SEQUENCE_NUMBER,
510 SELECT_GROUP,
511 TRUE_MESSAGE,
512 FALSE_MESSAGE,
513 DEFAULT_RULE,
514 RUG_SEQUENCE_NUMBER,
515 CREATION_DATE,
516 CREATED_BY,
517 LAST_UPDATE_DATE,
518 LAST_UPDATED_BY,
519 LAST_UPDATE_LOGIN
520 ) values (
521 NEW_REFERENCES.S_RULE_TYPE_CD,
522 NEW_REFERENCES.S_RULE_CALL_CD,
523 NEW_REFERENCES.RUD_SEQUENCE_NUMBER,
524 NEW_REFERENCES.SELECT_GROUP,
525 NEW_REFERENCES.TRUE_MESSAGE,
526 NEW_REFERENCES.FALSE_MESSAGE,
527 NEW_REFERENCES.DEFAULT_RULE,
528 NEW_REFERENCES.RUG_SEQUENCE_NUMBER,
529 X_LAST_UPDATE_DATE,
530 X_LAST_UPDATED_BY,
531 X_LAST_UPDATE_DATE,
532 X_LAST_UPDATED_BY,
533 X_LAST_UPDATE_LOGIN
534 );
535
536 open c;
537 fetch c into X_ROWID;
538 if (c%notfound) then
539 close c;
540 raise no_data_found;
541 end if;
542 close c;
543
544 After_DML (
545 p_action => 'INSERT',
546 x_rowid => X_ROWID
547 );
548
549
550 end INSERT_ROW;
551
552 procedure LOCK_ROW (
553 X_ROWID in VARCHAR2,
554 X_S_RULE_CALL_CD in VARCHAR2,
555 X_S_RULE_TYPE_CD in VARCHAR2,
556 X_RUD_SEQUENCE_NUMBER in NUMBER,
557 X_SELECT_GROUP in NUMBER,
558 X_TRUE_MESSAGE in VARCHAR2,
559 X_FALSE_MESSAGE in VARCHAR2,
560 X_DEFAULT_RULE in NUMBER,
561 X_RUG_SEQUENCE_NUMBER in NUMBER
562 ) AS
563 cursor c1 is select
564 S_RULE_TYPE_CD,
565 RUD_SEQUENCE_NUMBER,
566 SELECT_GROUP,
567 TRUE_MESSAGE,
568 FALSE_MESSAGE,
569 DEFAULT_RULE,
570 RUG_SEQUENCE_NUMBER
571 from IGS_RU_CALL
572 where ROWID = X_ROWID
573 for update nowait;
574 tlinfo c1%rowtype;
575
576 begin
577 open c1;
578 fetch c1 into tlinfo;
579 if (c1%notfound) then
580 close c1;
581 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
582 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_CALL : P_ACTION LOCK_ROW : IGSUI01B.PLS');
583 IGS_GE_MSG_STACK.ADD;
584 app_exception.raise_exception;
585 return;
586 end if;
587 close c1;
588
589 if ( (tlinfo.S_RULE_TYPE_CD = X_S_RULE_TYPE_CD)
590 AND (tlinfo.RUD_SEQUENCE_NUMBER = X_RUD_SEQUENCE_NUMBER)
591 AND (tlinfo.SELECT_GROUP = X_SELECT_GROUP)
592 AND ((tlinfo.TRUE_MESSAGE = X_TRUE_MESSAGE)
593 OR ((tlinfo.TRUE_MESSAGE is null)
594 AND (X_TRUE_MESSAGE is null)))
595 AND ((tlinfo.FALSE_MESSAGE = X_FALSE_MESSAGE)
596 OR ((tlinfo.FALSE_MESSAGE is null)
597 AND (X_FALSE_MESSAGE is null)))
598 AND ((tlinfo.DEFAULT_RULE = X_DEFAULT_RULE)
599 OR ((tlinfo.DEFAULT_RULE is null)
600 AND (X_DEFAULT_RULE is null)))
601 AND ((tlinfo.RUG_SEQUENCE_NUMBER = X_RUG_SEQUENCE_NUMBER)
602 OR ((tlinfo.RUG_SEQUENCE_NUMBER is null)
603 AND (X_RUG_SEQUENCE_NUMBER is null)))
604 ) then
605 null;
606 else
607 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
608 IGS_RU_GEN_006.SET_TOKEN('IGS_RU_CALL : P_ACTION LOCK_ROW FORM_RECORD_CHANGED : IGSUI01B.PLS');
609 IGS_GE_MSG_STACK.ADD;
610 app_exception.raise_exception;
611 end if;
612 return;
613 end LOCK_ROW;
614
615 procedure UPDATE_ROW (
616 X_ROWID in VARCHAR2,
617 X_S_RULE_CALL_CD in VARCHAR2,
618 X_S_RULE_TYPE_CD in VARCHAR2,
619 X_RUD_SEQUENCE_NUMBER in NUMBER,
620 X_SELECT_GROUP in NUMBER,
621 X_TRUE_MESSAGE in VARCHAR2,
622 X_FALSE_MESSAGE in VARCHAR2,
623 X_DEFAULT_RULE in NUMBER,
624 X_RUG_SEQUENCE_NUMBER in NUMBER,
625 X_MODE in VARCHAR2
626 ) AS
627 X_LAST_UPDATE_DATE DATE;
628 X_LAST_UPDATED_BY NUMBER;
629 X_LAST_UPDATE_LOGIN NUMBER;
630 begin
631 X_LAST_UPDATE_DATE := SYSDATE;
632 if(X_MODE = 'I') then
633 X_LAST_UPDATED_BY := 1;
634 X_LAST_UPDATE_LOGIN := 0;
635 elsif (X_MODE = 'R') then
636 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
637 if X_LAST_UPDATED_BY is NULL then
638 X_LAST_UPDATED_BY := -1;
639 end if;
640 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
641 if X_LAST_UPDATE_LOGIN is NULL then
642 X_LAST_UPDATE_LOGIN := -1;
643 end if;
644 else
645 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
646 IGS_GE_MSG_STACK.ADD;
647 app_exception.raise_exception;
648 end if;
649
650 Before_DML (
651 p_action => 'UPDATE',
652 x_rowid => X_ROWID,
653 x_s_rule_call_cd =>X_S_RULE_CALL_CD,
654 x_s_rule_type_cd =>X_S_RULE_TYPE_CD,
655 x_rud_sequence_number =>X_RUD_SEQUENCE_NUMBER,
656 x_true_message =>X_TRUE_MESSAGE,
657 x_false_message =>X_FALSE_MESSAGE,
658 x_default_rule =>X_DEFAULT_RULE,
659 x_rug_sequence_number =>X_RUG_SEQUENCE_NUMBER,
660 x_select_group =>X_SELECT_GROUP,
661 x_creation_date =>X_LAST_UPDATE_DATE,
662 x_created_by =>X_LAST_UPDATED_BY,
663 x_last_update_date =>X_LAST_UPDATE_DATE,
664 x_last_updated_by =>X_LAST_UPDATED_BY,
665 x_last_update_login=> X_LAST_UPDATE_LOGIN
666 );
667
668 update IGS_RU_CALL set
669 S_RULE_TYPE_CD = NEW_REFERENCES.S_RULE_TYPE_CD,
670 RUD_SEQUENCE_NUMBER = NEW_REFERENCES.RUD_SEQUENCE_NUMBER,
671 SELECT_GROUP = NEW_REFERENCES.SELECT_GROUP,
672 TRUE_MESSAGE = NEW_REFERENCES.TRUE_MESSAGE,
673 FALSE_MESSAGE = NEW_REFERENCES.FALSE_MESSAGE,
674 DEFAULT_RULE = NEW_REFERENCES.DEFAULT_RULE,
675 RUG_SEQUENCE_NUMBER = NEW_REFERENCES.RUG_SEQUENCE_NUMBER,
676 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
677 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
678 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
679 where ROWID = X_ROWID ;
680 if (sql%notfound) then
681 raise no_data_found;
682 end if;
683 After_DML (
684 p_action => 'UPDATE',
685 x_rowid => X_ROWID
686 );
687
688 end UPDATE_ROW;
689
690 procedure ADD_ROW (
691 X_ROWID in out NOCOPY VARCHAR2,
692 X_S_RULE_CALL_CD in VARCHAR2,
693 X_S_RULE_TYPE_CD in VARCHAR2,
694 X_RUD_SEQUENCE_NUMBER in NUMBER,
695 X_SELECT_GROUP in NUMBER,
696 X_TRUE_MESSAGE in VARCHAR2,
697 X_FALSE_MESSAGE in VARCHAR2,
698 X_DEFAULT_RULE in NUMBER,
699 X_RUG_SEQUENCE_NUMBER in NUMBER,
700 X_MODE in VARCHAR2
701 ) AS
702 cursor c1 is select rowid from IGS_RU_CALL
703 where S_RULE_CALL_CD = X_S_RULE_CALL_CD
704 ;
705 begin
706 open c1;
707 fetch c1 into X_ROWID;
708 if (c1%notfound) then
709 close c1;
710 INSERT_ROW (
711 X_ROWID,
712 X_S_RULE_CALL_CD,
713 X_S_RULE_TYPE_CD,
714 X_RUD_SEQUENCE_NUMBER,
715 X_SELECT_GROUP,
716 X_TRUE_MESSAGE,
717 X_FALSE_MESSAGE,
718 X_DEFAULT_RULE,
719 X_RUG_SEQUENCE_NUMBER,
720 X_MODE);
721 return;
722 end if;
723 close c1;
724 UPDATE_ROW (
725 X_ROWID,
726 X_S_RULE_CALL_CD,
727 X_S_RULE_TYPE_CD,
728 X_RUD_SEQUENCE_NUMBER,
729 X_SELECT_GROUP,
730 X_TRUE_MESSAGE,
731 X_FALSE_MESSAGE,
732 X_DEFAULT_RULE,
733 X_RUG_SEQUENCE_NUMBER,
734 X_MODE);
735 end ADD_ROW;
736
737 procedure DELETE_ROW (
738 X_ROWID in VARCHAR2
739 ) AS
740 begin
741
742 Before_DML (
743 p_action => 'DELETE',
744 x_rowid => X_ROWID
745 );
746
747 delete from IGS_RU_CALL
748 where ROWID = X_ROWID;
749 if (sql%notfound) then
750 raise no_data_found;
751 end if;
752 After_DML (
753 p_action => 'DELETE',
754 x_rowid => X_ROWID
755 );
756 end DELETE_ROW;
757
758
759 PROCEDURE Check_Constraints (
760 Column_Name IN VARCHAR2 ,
761 Column_Value IN VARCHAR2
762 ) AS
763 BEGIN
764 IF Column_Name is null THEN
765 NULL;
766 ELSIF upper(Column_name) = 'DEFAULT_RULE' THEN
767 new_references.DEFAULT_RULE:= igs_ge_number.to_num(COLUMN_VALUE) ;
768
769 ELSIF upper(Column_name) = 'RUD_SEQUENCE_NUMBER' THEN
770 new_references.RUD_SEQUENCE_NUMBER:= igs_ge_number.to_num(COLUMN_VALUE) ;
771
772 ELSIF upper(Column_name) = 'RUG_SEQUENCE_NUMBER' THEN
773 new_references.RUG_SEQUENCE_NUMBER:= igs_ge_number.to_num(COLUMN_VALUE) ;
774
775 ELSIF upper(Column_name) = 'SELECT_GROUP' THEN
776 new_references.SELECT_GROUP:= igs_ge_number.to_num(COLUMN_VALUE) ;
777
778 ELSIF upper(Column_name) = 'S_RULE_CALL_CD' THEN
779 new_references.S_RULE_CALL_CD:= COLUMN_VALUE ;
780
781 ELSIF upper(Column_name) = 'S_RULE_TYPE_CD' THEN
782 new_references.S_RULE_TYPE_CD:= COLUMN_VALUE ;
783
784 END IF ;
785
786 IF upper(Column_name) = 'DEFAULT_RULE' OR COLUMN_NAME IS NULL THEN
787 IF new_references.DEFAULT_RULE < 1 or new_references.DEFAULT_RULE > 999999 then
788 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
789 IGS_GE_MSG_STACK.ADD;
790 App_Exception.Raise_Exception ;
791 END IF;
792
793 END IF ;
794
795 IF upper(Column_name) = 'RUD_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
796 IF new_references.RUD_SEQUENCE_NUMBER < 1 or new_references.RUD_SEQUENCE_NUMBER > 999999 then
797 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
798 IGS_GE_MSG_STACK.ADD;
799 App_Exception.Raise_Exception ;
800 END IF;
801
802 END IF ;
803
804 IF upper(Column_name) = 'RUG_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
805 IF new_references.RUG_SEQUENCE_NUMBER < 1 or new_references.RUG_SEQUENCE_NUMBER > 999999 then
806 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
807 IGS_GE_MSG_STACK.ADD;
808 App_Exception.Raise_Exception ;
809 END IF;
810
811 END IF ;
812
813 IF upper(Column_name) = 'SELECT_GROUP' OR COLUMN_NAME IS NULL THEN
814 IF new_references.SELECT_GROUP < 1 or new_references.SELECT_GROUP > 999999 then
815 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
816 IGS_GE_MSG_STACK.ADD;
817 App_Exception.Raise_Exception ;
818 END IF;
819
820 END IF ;
821
822 IF upper(Column_name) = 'S_RULE_CALL_CD' OR COLUMN_NAME IS NULL THEN
823 IF new_references.S_RULE_CALL_CD<> upper(new_references.S_RULE_CALL_CD) then
824 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
825 IGS_GE_MSG_STACK.ADD;
826 App_Exception.Raise_Exception ;
827 END IF;
828
829 END IF ;
830
831 IF upper(Column_name) = 'S_RULE_TYPE_CD' OR COLUMN_NAME IS NULL THEN
832 IF new_references.S_RULE_TYPE_CD<> upper(new_references.S_RULE_TYPE_CD) then
833 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
834 IGS_GE_MSG_STACK.ADD;
835 App_Exception.Raise_Exception ;
836 END IF;
837
838 END IF ;
839 END Check_Constraints;
840
841 end IGS_RU_CALL_PKG;
[Home] [Help]