[Home] [Help]
PACKAGE BODY: APPS.IGS_RU_ITEM_PKG
Source
1 package body IGS_RU_ITEM_PKG as
2 /* $Header: IGSUI07B.pls 120.2 2006/02/20 04:34:17 sarakshi noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_RU_ITEM%RowType;
6 new_references IGS_RU_ITEM%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_rul_sequence_number IN NUMBER ,
12 x_item IN NUMBER ,
13 x_turin_function IN VARCHAR2 ,
14 x_named_rule IN NUMBER ,
15 x_rule_number IN NUMBER ,
16 x_set_number IN NUMBER ,
17 x_value IN VARCHAR2 ,
18 x_derived_rule IN NUMBER ,
19 x_creation_date IN DATE ,
20 x_created_by IN NUMBER ,
21 x_last_update_date IN DATE ,
22 x_last_updated_by IN NUMBER ,
23 x_last_update_login IN NUMBER
24 ) as
25
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_RU_ITEM
29 WHERE rowid = x_rowid;
30
31 BEGIN
32
33 l_rowid := x_rowid;
34
35 -- Code for setting the Old and New Reference Values.
36 -- Populate Old Values.
37 Open cur_old_ref_values;
38 Fetch cur_old_ref_values INTO old_references;
39 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
40 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
41 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_ITEM : P_ACTION INSERT VALIDATE_INSERT : IGSUI07B.PLS');
42 IGS_GE_MSG_STACK.ADD;
43 App_Exception.Raise_Exception;
44 Close cur_old_ref_values;
45 Return;
46 END IF;
47 Close cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.rul_sequence_number := x_rul_sequence_number;
51 new_references.item := x_item;
52 new_references.turin_function := x_turin_function;
53 new_references.named_rule := x_named_rule;
54 new_references.rule_number := x_rule_number;
55 new_references.set_number := x_set_number;
56 new_references.value := x_value;
57 new_references.derived_rule := x_derived_rule;
58 IF (p_action = 'UPDATE') THEN
59 new_references.creation_date := old_references.creation_date;
60 new_references.created_by := old_references.created_by;
61 ELSE
62 new_references.creation_date := x_creation_date;
63 new_references.created_by := x_created_by;
64 END IF;
65 new_references.last_update_date := x_last_update_date;
66 new_references.last_updated_by := x_last_updated_by;
67 new_references.last_update_login := x_last_update_login;
68
69 END Set_Column_Values;
70 PROCEDURE Check_Constraints (
71 Column_Name IN VARCHAR2 ,
72 Column_Value IN VARCHAR2
73 )
74 as
75 BEGIN
76 IF Column_Name is null THEN
77 NULL;
78 ELSIF upper(Column_name) = 'RUL_SEQUENCE_NUMBER' THEN
79 new_references.RUL_SEQUENCE_NUMBER:= igs_ge_number.to_num(COLUMN_VALUE) ;
80
81 ELSIF upper(Column_name) = 'SET_NUMBER' THEN
82 new_references.SET_NUMBER:= igs_ge_number.to_num(COLUMN_VALUE) ;
83
84 ELSIF upper(Column_name) = 'ITEM' THEN
85 new_references.ITEM:= igs_ge_number.to_num(COLUMN_VALUE) ;
86
87 END IF ;
88
89 IF upper(Column_name) = 'RUL_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
90 IF new_references.RUL_SEQUENCE_NUMBER < 0 or new_references.RUL_SEQUENCE_NUMBER > 999999 then
91 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
92 IGS_GE_MSG_STACK.ADD;
93 App_Exception.Raise_Exception ;
94 END IF;
95
96 END IF ;
97
98 IF upper(Column_name) = 'SET_NUMBER' OR COLUMN_NAME IS NULL THEN
99 IF new_references.SET_NUMBER < 0 or new_references.SET_NUMBER > 999999 then
100 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception ;
103 END IF;
104
105 END IF ;
106
107 IF upper(Column_name) = 'ITEM' OR COLUMN_NAME IS NULL THEN
108 IF new_references.ITEM < 0 or new_references.ITEM > 999999 then
109 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
110 IGS_GE_MSG_STACK.ADD;
111 App_Exception.Raise_Exception ;
112 END IF;
113
114 END IF ;
115 END Check_Constraints;
116
117 PROCEDURE Check_Parent_Existance as
118 BEGIN
119
120 IF (((old_references.named_rule = new_references.named_rule)) OR
121 ((new_references.named_rule IS NULL))) THEN
122 NULL;
123 ELSE
124 IF NOT IGS_RU_NAMED_RULE_PKG.Get_PK_For_Validation (
125 new_references.named_rule
126 ) THEN
127 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
128 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_NAMED_RULE : P_ACTION Check_Parent_Existance named_rule : IGSUI07B.PLS');
129 IGS_GE_MSG_STACK.ADD;
130 App_Exception.Raise_Exception;
131 END IF;
132 END IF;
133
134 IF (((old_references.rul_sequence_number = new_references.rul_sequence_number)) OR
135 ((new_references.rul_sequence_number IS NULL))) THEN
136 NULL;
137 ELSE
138 IF NOT IGS_RU_RULE_PKG.Get_PK_For_Validation (
139 new_references.rul_sequence_number
140 ) THEN
141 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
142 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_RULE : P_ACTION Check_Parent_Existance rul_sequence_number : IGSUI07B.PLS');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END IF;
146 END IF;
147
148 IF (((old_references.rule_number = new_references.rule_number)) OR
149 ((new_references.rule_number IS NULL))) THEN
150 NULL;
151 ELSE
152 IF NOT IGS_RU_RULE_PKG.Get_PK_For_Validation (
153 new_references.rule_number
154 ) THEN
155 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
156 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_RULE : P_ACTION Check_Parent_Existance rule_number : IGSUI07B.PLS');
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END IF;
160 END IF;
161
162 IF (((old_references.set_number = new_references.set_number)) OR
163 ((new_references.set_number IS NULL))) THEN
164 NULL;
165 ELSE
166 IF NOT IGS_RU_SET_PKG.Get_PK_For_Validation (
167 new_references.set_number
168 ) THEN
169 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
170 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_SET : P_ACTION Check_Parent_Existance set_number : IGSUI07B.PLS');
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173 END IF;
174 END IF;
175
176 IF (((old_references.derived_rule = new_references.derived_rule)) OR
177 ((new_references.derived_rule IS NULL))) THEN
178 NULL;
179 ELSE
180 IF NOT IGS_RU_CALL_PKG.Get_UK1_For_Validation (
181 new_references.derived_rule
182 ) THEN
183 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
184 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_CALL : P_ACTION Check_Parent_Existance derived_rule : IGSUI07B.PLS');
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 END IF;
188 END IF;
189
190 IF (((old_references.turin_function = new_references.turin_function)) OR
191 ((new_references.turin_function IS NULL))) THEN
192 NULL;
193 ELSE
194 IF NOT IGS_RU_TURIN_FNC_PKG.Get_PK_For_Validation (
195 new_references.turin_function
196 ) THEN
197 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
198 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_TURIN_FNC : P_ACTION Check_Parent_Existance turin_function : IGSUI07B.PLS');
199 IGS_GE_MSG_STACK.ADD;
200 App_Exception.Raise_Exception;
201 END IF;
202 END IF;
203
204 END Check_Parent_Existance;
205
206 FUNCTION Get_PK_For_Validation (
207 x_rul_sequence_number IN NUMBER,
208 x_item IN NUMBER
209 ) RETURN BOOLEAN
210 as
211 CURSOR cur_rowid IS
212 SELECT rowid
213 FROM IGS_RU_ITEM
214 WHERE rul_sequence_number = x_rul_sequence_number
215 AND item = x_item
216 FOR UPDATE NOWAIT;
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 Return (TRUE);
227 ELSE
228 Close cur_rowid;
229 Return (FALSE);
230 END IF;
231 END Get_PK_For_Validation;
232
233 PROCEDURE GET_FK_IGS_RU_NAMED_RULE (
234 x_rul_sequence_number IN VARCHAR2
235 )as
236
237 CURSOR cur_rowid IS
238 SELECT rowid
239 FROM IGS_RU_ITEM
240 WHERE named_rule = x_rul_sequence_number ;
241
242 lv_rowid cur_rowid%RowType;
243
244 BEGIN
245
246 Open cur_rowid;
247 Fetch cur_rowid INTO lv_rowid;
248 IF (cur_rowid%FOUND) THEN
249 Close cur_rowid;
250 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RUI_NR_FK');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 Return;
254 END IF;
255 Close cur_rowid;
256
257 END GET_FK_IGS_RU_NAMED_RULE;
258
259 PROCEDURE GET_FK_IGS_RU_RULE (
260 x_sequence_number IN NUMBER
261 )as
262
263 CURSOR cur_rowid IS
264 SELECT rowid
265 FROM IGS_RU_ITEM
266 WHERE rul_sequence_number = x_sequence_number
267 OR rule_number = x_sequence_number ;
268
269 lv_rowid cur_rowid%RowType;
270
271 BEGIN
272
273 Open cur_rowid;
274 Fetch cur_rowid INTO lv_rowid;
275 IF (cur_rowid%FOUND) THEN
276 Close cur_rowid;
277 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RUI_RUL_FK');
278 IGS_GE_MSG_STACK.ADD;
279 App_Exception.Raise_Exception;
280 Return;
281 END IF;
282 Close cur_rowid;
283
284 END GET_FK_IGS_RU_RULE;
285
286 PROCEDURE GET_FK_IGS_RU_SET (
287 x_sequence_number IN NUMBER
288 )as
289
290 CURSOR cur_rowid IS
291 SELECT rowid
292 FROM IGS_RU_ITEM
293 WHERE set_number = x_sequence_number ;
294
295 lv_rowid cur_rowid%RowType;
296
297 BEGIN
298
299 Open cur_rowid;
300 Fetch cur_rowid INTO lv_rowid;
301 IF (cur_rowid%FOUND) THEN
302 Close cur_rowid;
303 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RUI_RUL_FK');
304 IGS_GE_MSG_STACK.ADD;
305 App_Exception.Raise_Exception;
306 Return;
307 END IF;
308 Close cur_rowid;
309
310 END GET_FK_IGS_RU_SET;
311
312 PROCEDURE GET_UFK_IGS_RU_CALL (
313 x_rud_sequence_number IN NUMBER
314 )as
315
316 CURSOR cur_rowid IS
317 SELECT rowid
318 FROM IGS_RU_ITEM
319 WHERE derived_rule = x_rud_sequence_number ;
320
321 lv_rowid cur_rowid%RowType;
322
323 BEGIN
324
325 Open cur_rowid;
326 Fetch cur_rowid INTO lv_rowid;
327 IF (cur_rowid%FOUND) THEN
328 Close cur_rowid;
329 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RUI_SRC_FK');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 Return;
333 END IF;
334 Close cur_rowid;
335
336 END GET_UFK_IGS_RU_CALL;
337
338 PROCEDURE GET_FK_IGS_RU_TURIN_FNC (
339 x_s_turin_function IN VARCHAR2
340 )as
341
342 CURSOR cur_rowid IS
343 SELECT rowid
344 FROM IGS_RU_ITEM
345 WHERE turin_function = x_s_turin_function ;
346
347 lv_rowid cur_rowid%RowType;
348
349 BEGIN
350
351 Open cur_rowid;
352 Fetch cur_rowid INTO lv_rowid;
353 IF (cur_rowid%FOUND) THEN
354 Close cur_rowid;
355 Fnd_Message.Set_Name ('IGS', 'IGS_RU_RUI_STF_FK');
356 IGS_GE_MSG_STACK.ADD;
357 App_Exception.Raise_Exception;
358 Return;
359 END IF;
360 Close cur_rowid;
361
362 END GET_FK_IGS_RU_TURIN_FNC;
363
364 PROCEDURE Before_DML (
365 p_action IN VARCHAR2,
366 x_rowid IN VARCHAR2 ,
367 x_rul_sequence_number IN NUMBER ,
368 x_item IN NUMBER ,
369 x_turin_function IN VARCHAR2 ,
370 x_named_rule IN NUMBER ,
371 x_rule_number IN NUMBER ,
372 x_set_number IN NUMBER ,
373 x_value IN VARCHAR2 ,
374 x_derived_rule IN NUMBER ,
375 x_creation_date IN DATE ,
376 x_created_by IN NUMBER ,
377 x_last_update_date IN DATE ,
378 x_last_updated_by IN NUMBER ,
379 x_last_update_login IN NUMBER
380 )as
381 BEGIN
382
383 Set_Column_Values (
384 p_action,
385 x_rowid,
386 x_rul_sequence_number,
387 x_item,
388 x_turin_function,
389 x_named_rule,
390 x_rule_number,
391 x_set_number,
392 x_value,
393 x_derived_rule,
394 x_creation_date,
395 x_created_by,
396 x_last_update_date,
397 x_last_updated_by,
398 x_last_update_login
399 );
400 IF (p_action = 'INSERT') THEN
401 -- Call all the procedures related to Before Insert.
402 Null;
403 IF Get_PK_For_Validation (
404 new_references.rul_sequence_number,
405 new_references.item
406 ) THEN
407 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
408 IGS_GE_MSG_STACK.ADD;
409 App_Exception.Raise_Exception;
410 END IF;
411 Check_Constraints;
412 Check_Parent_Existance;
413 ELSIF (p_action = 'UPDATE') THEN
414 -- Call all the procedures related to Before Update.
415 Check_Constraints;
416 Check_Parent_Existance;
417 ELSIF (p_action = 'VALIDATE_INSERT') THEN
418 IF Get_PK_For_Validation (
419 new_references.rul_sequence_number,
420 new_references.item
421 ) THEN
422 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
423 IGS_GE_MSG_STACK.ADD;
424 App_Exception.Raise_Exception;
425 END IF;
426 Check_Constraints;
427 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
428 Check_Constraints;
429 END IF;
430 END Before_DML;
431
432 PROCEDURE After_DML (
433 p_action IN VARCHAR2,
434 x_rowid IN VARCHAR2
435 )as
436 BEGIN
437
438 l_rowid := x_rowid;
439
440 END After_DML;
441
442 procedure INSERT_ROW (
443 X_ROWID in out NOCOPY VARCHAR2,
444 X_RUL_SEQUENCE_NUMBER in NUMBER,
445 X_ITEM in NUMBER,
446 X_TURIN_FUNCTION in VARCHAR2,
447 X_NAMED_RULE in NUMBER,
448 X_RULE_NUMBER in NUMBER,
449 X_SET_NUMBER in NUMBER,
450 X_VALUE in VARCHAR2,
451 X_DERIVED_RULE in NUMBER,
452 X_MODE in VARCHAR2
453 )as
454 cursor C is select ROWID from IGS_RU_ITEM
455 where RUL_SEQUENCE_NUMBER = X_RUL_SEQUENCE_NUMBER
456 and ITEM = X_ITEM;
457 X_LAST_UPDATE_DATE DATE;
458 X_LAST_UPDATED_BY NUMBER;
459 X_LAST_UPDATE_LOGIN NUMBER;
460 begin
461 X_LAST_UPDATE_DATE := SYSDATE;
462 if(X_MODE = 'I') then
463 X_LAST_UPDATED_BY := 1;
464 X_LAST_UPDATE_LOGIN := 0;
465 elsif (X_MODE = 'R') then
466 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
467 if X_LAST_UPDATED_BY is NULL then
468 X_LAST_UPDATED_BY := -1;
469 end if;
470 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
471 if X_LAST_UPDATE_LOGIN is NULL then
472 X_LAST_UPDATE_LOGIN := -1;
473 end if;
474 else
475 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
476 IGS_GE_MSG_STACK.ADD;
477 app_exception.raise_exception;
478 end if;
479
480 Before_DML(
481 p_action=>'INSERT',
482 x_rowid=>X_ROWID,
483 x_derived_rule=>X_DERIVED_RULE,
484 x_item=>X_ITEM,
485 x_named_rule=>X_NAMED_RULE,
486 x_rul_sequence_number=>X_RUL_SEQUENCE_NUMBER,
487 x_rule_number=>X_RULE_NUMBER,
488 x_set_number=>X_SET_NUMBER,
489 x_turin_function=>X_TURIN_FUNCTION,
490 x_value=>X_VALUE,
491 x_creation_date=>X_LAST_UPDATE_DATE,
492 x_created_by=>X_LAST_UPDATED_BY,
493 x_last_update_date=>X_LAST_UPDATE_DATE,
494 x_last_updated_by=>X_LAST_UPDATED_BY,
495 x_last_update_login=>X_LAST_UPDATE_LOGIN
496 );
497
498 insert into IGS_RU_ITEM (
499 RUL_SEQUENCE_NUMBER,
500 ITEM,
501 TURIN_FUNCTION,
502 NAMED_RULE,
503 RULE_NUMBER,
504 SET_NUMBER,
505 VALUE,
506 DERIVED_RULE,
507 CREATION_DATE,
508 CREATED_BY,
509 LAST_UPDATE_DATE,
510 LAST_UPDATED_BY,
511 LAST_UPDATE_LOGIN
512 ) values (
513 NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
514 NEW_REFERENCES.ITEM,
515 NEW_REFERENCES.TURIN_FUNCTION,
516 NEW_REFERENCES.NAMED_RULE,
517 NEW_REFERENCES.RULE_NUMBER,
518 NEW_REFERENCES.SET_NUMBER,
519 NEW_REFERENCES.VALUE,
520 NEW_REFERENCES.DERIVED_RULE,
521 X_LAST_UPDATE_DATE,
522 X_LAST_UPDATED_BY,
523 X_LAST_UPDATE_DATE,
524 X_LAST_UPDATED_BY,
525 X_LAST_UPDATE_LOGIN
526 );
527
528 open c;
529 fetch c into X_ROWID;
530 if (c%notfound) then
531 close c;
532 raise no_data_found;
533 end if;
534 close c;
535
536 After_DML (
537 p_action => 'INSERT',
538 x_rowid => X_ROWID);
539
540 end INSERT_ROW;
541
542 procedure LOCK_ROW (
543 X_ROWID in VARCHAR2,
544 X_RUL_SEQUENCE_NUMBER in NUMBER,
545 X_ITEM in NUMBER,
546 X_TURIN_FUNCTION in VARCHAR2,
547 X_NAMED_RULE in NUMBER,
548 X_RULE_NUMBER in NUMBER,
549 X_SET_NUMBER in NUMBER,
550 X_VALUE in VARCHAR2,
551 X_DERIVED_RULE in NUMBER
552 )as
553 cursor c1 is select
554 TURIN_FUNCTION,
555 NAMED_RULE,
556 RULE_NUMBER,
557 SET_NUMBER,
558 VALUE,
559 DERIVED_RULE
560 from IGS_RU_ITEM
561 where ROWID = X_ROWID for update nowait;
562 tlinfo c1%rowtype;
563
564 begin
565 open c1;
566 fetch c1 into tlinfo;
567 if (c1%notfound) then
568 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
569 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_ITEM : P_ACTION LOCK_ROW : IGSUI07B.PLS');
570 IGS_GE_MSG_STACK.ADD;
571 app_exception.raise_exception;
572 close c1;
573 return;
574 end if;
575 close c1;
576
577 if ( ((tlinfo.TURIN_FUNCTION = X_TURIN_FUNCTION)
578 OR ((tlinfo.TURIN_FUNCTION is null)
579 AND (X_TURIN_FUNCTION is null)))
580 AND ((tlinfo.NAMED_RULE = X_NAMED_RULE)
581 OR ((tlinfo.NAMED_RULE is null)
582 AND (X_NAMED_RULE is null)))
583 AND ((tlinfo.RULE_NUMBER = X_RULE_NUMBER)
584 OR ((tlinfo.RULE_NUMBER is null)
585 AND (X_RULE_NUMBER is null)))
586 AND ((tlinfo.SET_NUMBER = X_SET_NUMBER)
587 OR ((tlinfo.SET_NUMBER is null)
588 AND (X_SET_NUMBER is null)))
589 AND ((tlinfo.VALUE = X_VALUE)
590 OR ((tlinfo.VALUE is null)
591 AND (X_VALUE is null)))
592 AND ((tlinfo.DERIVED_RULE = X_DERIVED_RULE)
593 OR ((tlinfo.DERIVED_RULE is null)
594 AND (X_DERIVED_RULE is null)))
595 ) then
596 null;
597 else
598 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
599 IGS_RU_GEN_006.SET_TOKEN(' IGS_RU_ITEM : P_ACTION LOCK_ROW FORM_RECORD_CHANGED : IGSUI07B.PLS');
600 IGS_GE_MSG_STACK.ADD;
601 app_exception.raise_exception;
602 end if;
603 return;
604 end LOCK_ROW;
605
606 procedure UPDATE_ROW (
607 X_ROWID in VARCHAR2,
608 X_RUL_SEQUENCE_NUMBER in NUMBER,
609 X_ITEM in NUMBER,
610 X_TURIN_FUNCTION in VARCHAR2,
611 X_NAMED_RULE in NUMBER,
612 X_RULE_NUMBER in NUMBER,
613 X_SET_NUMBER in NUMBER,
614 X_VALUE in VARCHAR2,
615 X_DERIVED_RULE in NUMBER,
616 X_MODE in VARCHAR2
617 )as
618 X_LAST_UPDATE_DATE DATE;
619 X_LAST_UPDATED_BY NUMBER;
620 X_LAST_UPDATE_LOGIN NUMBER;
621 begin
622 X_LAST_UPDATE_DATE := SYSDATE;
623 if(X_MODE = 'I') then
624 X_LAST_UPDATED_BY := 1;
625 X_LAST_UPDATE_LOGIN := 0;
626 elsif (X_MODE = 'R') then
627 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
628 if X_LAST_UPDATED_BY is NULL then
629 X_LAST_UPDATED_BY := -1;
630 end if;
631 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
632 if X_LAST_UPDATE_LOGIN is NULL then
633 X_LAST_UPDATE_LOGIN := -1;
634 end if;
635 else
636 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
637 IGS_GE_MSG_STACK.ADD;
638 app_exception.raise_exception;
639 end if;
640
641 Before_DML(
642 p_action=>'UPDATE',
643 x_rowid=>X_ROWID,
644 x_derived_rule=>X_DERIVED_RULE,
645 x_item=>X_ITEM,
646 x_named_rule=>X_NAMED_RULE,
647 x_rul_sequence_number=>X_RUL_SEQUENCE_NUMBER,
648 x_rule_number=>X_RULE_NUMBER,
649 x_set_number=>X_SET_NUMBER,
650 x_turin_function=>X_TURIN_FUNCTION,
651 x_value=>X_VALUE,
652 x_creation_date=>X_LAST_UPDATE_DATE,
653 x_created_by=>X_LAST_UPDATED_BY,
654 x_last_update_date=>X_LAST_UPDATE_DATE,
655 x_last_updated_by=>X_LAST_UPDATED_BY,
656 x_last_update_login=>X_LAST_UPDATE_LOGIN
657 );
658
659 update IGS_RU_ITEM set
660 TURIN_FUNCTION = NEW_REFERENCES.TURIN_FUNCTION,
661 NAMED_RULE = NEW_REFERENCES.NAMED_RULE,
662 RULE_NUMBER = NEW_REFERENCES.RULE_NUMBER,
663 SET_NUMBER = NEW_REFERENCES.SET_NUMBER,
664 VALUE = NEW_REFERENCES.VALUE,
665 DERIVED_RULE = NEW_REFERENCES.DERIVED_RULE,
666 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
667 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
668 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
669 where ROWID = X_ROWID
670 ;
671 if (sql%notfound) then
672 raise no_data_found;
673 end if;
674
675 After_DML (
676 p_action => 'UPDATE',
677 x_rowid => X_ROWID);
678
679 end UPDATE_ROW;
680
681 procedure ADD_ROW (
682 X_ROWID in out NOCOPY VARCHAR2,
683 X_RUL_SEQUENCE_NUMBER in NUMBER,
684 X_ITEM in NUMBER,
685 X_TURIN_FUNCTION in VARCHAR2,
686 X_NAMED_RULE in NUMBER,
687 X_RULE_NUMBER in NUMBER,
688 X_SET_NUMBER in NUMBER,
689 X_VALUE in VARCHAR2,
690 X_DERIVED_RULE in NUMBER,
691 X_MODE in VARCHAR2
692 )as
693 cursor c1 is select rowid from IGS_RU_ITEM
694 where RUL_SEQUENCE_NUMBER = X_RUL_SEQUENCE_NUMBER
695 and ITEM = X_ITEM
696 ;
697 begin
698 open c1;
699 fetch c1 into X_ROWID;
700 if (c1%notfound) then
701 close c1;
702 INSERT_ROW (
703 X_ROWID,
704 X_RUL_SEQUENCE_NUMBER,
705 X_ITEM,
706 X_TURIN_FUNCTION,
707 X_NAMED_RULE,
708 X_RULE_NUMBER,
709 X_SET_NUMBER,
710 X_VALUE,
711 X_DERIVED_RULE,
712 X_MODE);
713 return;
714 end if;
715 close c1;
716 UPDATE_ROW (
717 X_ROWID,
718 X_RUL_SEQUENCE_NUMBER,
719 X_ITEM,
720 X_TURIN_FUNCTION,
721 X_NAMED_RULE,
722 X_RULE_NUMBER,
723 X_SET_NUMBER,
724 X_VALUE,
725 X_DERIVED_RULE,
726 X_MODE);
727 end ADD_ROW;
728
729 procedure DELETE_ROW (
730 X_ROWID in VARCHAR2
731 )as
732 begin
733
734 Before_DML (
735 p_action => 'DELETE',
736 x_rowid => X_ROWID);
737
738 delete from IGS_RU_ITEM
739 where ROWID = X_ROWID;
740 if (sql%notfound) then
741 raise no_data_found;
742 end if;
743
744 After_DML (
745 p_action => 'DELETE',
746 x_rowid => X_ROWID);
747
748 end DELETE_ROW;
749
750 PROCEDURE LOAD_ROW (
751 x_rul_sequence_number IN NUMBER,
752 x_item IN NUMBER,
753 x_turin_function IN VARCHAR2,
754 x_named_rule IN NUMBER,
755 x_rule_number IN NUMBER,
756 x_set_number IN NUMBER,
757 x_value IN VARCHAR2,
758 x_derived_rule IN NUMBER,
759 x_owner IN VARCHAR2,
760 x_last_update_date IN VARCHAR2,
761 x_custom_mode IN VARCHAR2 ) IS
762
763 f_luby number; -- entity owner in file
764 f_ludate date; -- entity update date in file
765 db_luby number; -- entity owner in db
766 db_ludate date; -- entity update date in db
767
768 CURSOR c_ru_item(cp_rul_sequence_number igs_ru_item.rul_sequence_number%TYPE,
769 cp_item igs_ru_item.item%TYPE) IS
770 SELECT last_updated_by, last_update_date
771 FROM igs_ru_item
772 WHERE rul_sequence_number = cp_rul_sequence_number
773 AND item = cp_item
774 AND rul_sequence_number <= 500000; -- this is the addtional check put to filter out the customer defined rules, bug 2421803
775
776
777 BEGIN
778
779 -- Translate owner to file_last_updated_by
780 f_luby := fnd_load_util.owner_id(x_owner);
781
782 -- Translate char last_update_date to date
783 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
784
785
786 OPEN c_ru_item(x_rul_sequence_number,x_item);
787 FETCH c_ru_item INTO db_luby, db_ludate;
788 IF c_ru_item%FOUND THEN
789 IF (TRUNC(f_ludate) > TRUNC(db_ludate)) THEN
790
791 -- Delete all the old entries for the currenly processed RUL_SEQUENCE_NUMBER
792 -- Added as part of bug fix 2421803, nshee
793 DELETE FROM IGS_RU_ITEM
794 WHERE RUL_SEQUENCE_NUMBER = x_rul_sequence_number;
795
796 INSERT INTO igs_ru_item
797 (
798 RUL_SEQUENCE_NUMBER,
799 ITEM,
800 TURIN_FUNCTION,
801 NAMED_RULE,
802 RULE_NUMBER,
803 SET_NUMBER,
804 VALUE,
805 DERIVED_RULE,
806 CREATED_BY,
807 CREATION_DATE,
808 LAST_UPDATED_BY,
809 LAST_UPDATE_DATE,
810 LAST_UPDATE_LOGIN
811 )
812 VALUES
813 (
814 x_rul_sequence_number,
815 x_item,
816 x_turin_function,
817 x_named_rule,
818 x_rule_number,
819 x_set_number,
820 x_value,
821 x_derived_rule,
822 f_luby,
823 f_ludate,
824 f_luby,
825 f_ludate,
826 0
827 );
828
829 END IF;
830 ELSE
831 INSERT INTO igs_ru_item
832 (
833 RUL_SEQUENCE_NUMBER,
834 ITEM,
835 TURIN_FUNCTION,
836 NAMED_RULE,
837 RULE_NUMBER,
838 SET_NUMBER,
839 VALUE,
840 DERIVED_RULE,
841 CREATED_BY,
842 CREATION_DATE,
843 LAST_UPDATED_BY,
844 LAST_UPDATE_DATE,
845 LAST_UPDATE_LOGIN
846 )
847 VALUES
848 (
849 x_rul_sequence_number,
850 x_item,
851 x_turin_function,
852 x_named_rule,
853 x_rule_number,
854 x_set_number,
855 x_value,
856 x_derived_rule,
857 f_luby,
858 f_ludate,
859 f_luby,
860 f_ludate,
861 0
862 );
863 END IF;
864 CLOSE c_ru_item;
865
866 END LOAD_ROW;
867
868
869 PROCEDURE LOAD_SEED_ROW (
870 x_upload_mode IN VARCHAR2,
871 x_rul_sequence_number IN NUMBER,
872 x_item IN NUMBER,
873 x_turin_function IN VARCHAR2,
874 x_named_rule IN NUMBER,
875 x_rule_number IN NUMBER,
876 x_set_number IN NUMBER,
877 x_value IN VARCHAR2,
878 x_derived_rule IN NUMBER,
879 x_owner IN VARCHAR2,
880 x_last_update_date IN VARCHAR2,
881 x_custom_mode IN VARCHAR2 ) IS
882
883 BEGIN
884
885 IF (x_upload_mode = 'NLS') THEN
886 NULL; --For translated record call Table_pkg.TRANSLATE_ROW
887 ELSE
888 igs_ru_item_pkg.load_row(
889 x_rul_sequence_number => x_rul_sequence_number ,
890 x_item => x_item ,
891 x_turin_function => x_turin_function ,
892 x_named_rule => x_named_rule,
893 x_rule_number => x_rule_number ,
894 x_set_number => x_set_number ,
895 x_value => x_value ,
896 x_derived_rule => x_derived_rule ,
897 x_owner => x_owner ,
898 x_last_update_date => x_last_update_date ,
899 x_custom_mode => x_custom_mode );
900 END IF;
901
902 END LOAD_SEED_ROW;
903
904 end IGS_RU_ITEM_PKG;