1 PACKAGE BODY FUN_RULE_OBJECTS_PKG AS
2 /*$Header: FUNXTMRULROBTBB.pls 120.11.12010000.2 2008/08/06 07:44:51 makansal ship $ */
3
4 PROCEDURE INSERT_ROW (
5 X_ROWID IN OUT NOCOPY VARCHAR2,
6 X_RULE_OBJECT_ID IN NUMBER,
7 X_APPLICATION_ID IN NUMBER,
8 X_RULE_OBJECT_NAME IN VARCHAR2,
9 X_RESULT_TYPE IN VARCHAR2,
10 X_REQUIRED_FLAG IN VARCHAR2,
11 X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
12 X_DEFAULT_APPLICATION_ID IN NUMBER,
13 X_DEFAULT_VALUE IN VARCHAR2,
14 X_FLEX_VALUE_SET_ID IN NUMBER,
15 X_FLEXFIELD_NAME IN VARCHAR2,
16 X_FLEXFIELD_APP_SHORT_NAME IN VARCHAR2,
17 X_MULTI_RULE_RESULT_FLAG IN VARCHAR2,
18 X_CREATED_BY_MODULE IN VARCHAR2,
19 X_USER_RULE_OBJECT_NAME IN VARCHAR2,
20 X_DESCRIPTION IN VARCHAR2,
21 X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
22 X_INSTANCE_LABEL IN VARCHAR2 DEFAULT NULL,
23 X_PARENT_RULE_OBJECT_ID IN NUMBER DEFAULT NULL,
24 X_ORG_ID IN NUMBER DEFAULT NULL,
25 X_CREATION_DATE IN DATE DEFAULT NULL,
26 X_CREATED_BY IN NUMBER DEFAULT NULL,
27 X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
28 X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
29 X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
30 ) IS
31 l_roa_rowid varchar2(64);
32 l_seq_val FUN_RULE_OBJECTS_B.RULE_OBJECT_ID%TYPE;
33 begin
34
35 --
36 --Always derive the RULE_OBJECT_ID here else in INSERT_ROW proc for
37 --FUN_RULE_OBJ_ATTRIBUTES table, it will be hard to derive the RULE_OBJECT_ID
38 --For the current Rule Object from RULE_OBJECT_NAME. Because, Rule Object Instances
39 --will share the same Rule Object Name as of the Parent Rule Object Id.
40
41 IF X_RULE_OBJECT_ID IS NULL THEN
42 select FUN_RULE_OBJECTS_S.NEXTVAL into l_seq_val from dual;
43 END IF;
44
45
46 INSERT_ROW(X_ROWID,
47 NVL(X_RULE_OBJECT_ID,l_seq_val),
48 X_APPLICATION_ID,
49 X_RULE_OBJECT_NAME,
50 X_RESULT_TYPE,
51 X_REQUIRED_FLAG,
52 X_USE_DEFAULT_VALUE_FLAG,
53 X_FLEX_VALUE_SET_ID,
54 X_FLEXFIELD_NAME,
55 X_FLEXFIELD_APP_SHORT_NAME,
56 X_MULTI_RULE_RESULT_FLAG,
57 X_CREATED_BY_MODULE,
58 X_USER_RULE_OBJECT_NAME,
59 X_DESCRIPTION,
60 NVL(X_USE_INSTANCE_FLAG,'N'), --override internally to N
61 X_INSTANCE_LABEL,
62 X_PARENT_RULE_OBJECT_ID,
63 X_ORG_ID,
64 X_CREATION_DATE,
65 X_CREATED_BY,
66 X_LAST_UPDATE_DATE,
67 X_LAST_UPDATED_BY,
68 X_LAST_UPDATE_LOGIN
69 );
70
71 INSERT_ROW(l_roa_rowid,
72 NVL(X_RULE_OBJECT_ID,l_seq_val),
73 X_APPLICATION_ID,
74 X_RULE_OBJECT_NAME,
75 X_DEFAULT_APPLICATION_ID,
76 X_DEFAULT_VALUE,
77 X_CREATION_DATE,
78 X_CREATED_BY,
79 X_LAST_UPDATE_DATE,
80 X_LAST_UPDATED_BY,
81 X_LAST_UPDATE_LOGIN
82 );
83 end INSERT_ROW;
84
85 PROCEDURE INSERT_ROW (
86 X_ROWID IN OUT NOCOPY VARCHAR2,
87 X_RULE_OBJECT_ID IN NUMBER,
88 X_APPLICATION_ID IN NUMBER,
89 X_RULE_OBJECT_NAME IN VARCHAR2,
90 X_RESULT_TYPE IN VARCHAR2,
91 X_REQUIRED_FLAG IN VARCHAR2,
92 X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
93 X_FLEX_VALUE_SET_ID IN NUMBER,
94 X_FLEXFIELD_NAME IN VARCHAR2,
95 X_FLEXFIELD_APP_SHORT_NAME IN VARCHAR2,
96 X_MULTI_RULE_RESULT_FLAG IN VARCHAR2,
97 X_CREATED_BY_MODULE IN VARCHAR2,
98 X_USER_RULE_OBJECT_NAME IN VARCHAR2,
99 X_DESCRIPTION IN VARCHAR2,
100 X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
101 X_INSTANCE_LABEL IN VARCHAR2 DEFAULT NULL,
102 X_PARENT_RULE_OBJECT_ID IN NUMBER DEFAULT NULL,
103 X_ORG_ID IN NUMBER DEFAULT NULL,
104 X_CREATION_DATE IN DATE DEFAULT NULL,
105 X_CREATED_BY IN NUMBER DEFAULT NULL,
106 X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
107 X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
108 X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
109 ) IS
110
111 cursor C(id number) is select ROWID from FUN_RULE_OBJECTS_B
112 where APPLICATION_ID = X_APPLICATION_ID
113 and RULE_OBJECT_ID = id
114 ;
115
116 l_orig_rule_object_name FUN_RULE_OBJECTS_B.RULE_OBJECT_NAME%TYPE;
117 l_parent_rule_object_id FUN_RULE_OBJECTS_B.PARENT_RULE_OBJECT_ID%TYPE := NULL;
118
119 BEGIN
120
121 --Derive the parent_rule_object_id if the X_INSTANCE_LABEL is NOT NULL
122 --or ORG_ID is NOT NULL.
123
124 BEGIN
125 IF (X_INSTANCE_LABEL IS NOT NULL OR X_ORG_ID IS NOT NULL) THEN
126 SELECT RULE_OBJECT_ID INTO l_parent_rule_object_id
127 FROM FUN_RULE_OBJECTS_B
128 WHERE RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
129 AND UPPER(USE_INSTANCE_FLAG) = 'Y'
130 AND INSTANCE_LABEL IS NULL
131 AND ORG_ID IS NULL
132 AND PARENT_RULE_OBJECT_ID IS NULL;
133
134 END IF;
135
136 EXCEPTION
137 WHEN NO_DATA_FOUND THEN
138 -- Since this should never happen if the code flow has reached till here.
139 -- Throwing an exception with hard coded message text
140 app_exception.raise_exception(exception_text=>'Invalid rule object name - '||X_RULE_OBJECT_NAME);
141 END ;
142
143 insert into FUN_RULE_OBJECTS_B (
144 RULE_OBJECT_ID,
145 APPLICATION_ID,
146 RULE_OBJECT_NAME,
147 RESULT_TYPE,
148 REQUIRED_FLAG,
149 USE_DEFAULT_VALUE_FLAG,
150 FLEX_VALUE_SET_ID,
151 FLEXFIELD_NAME,
152 FLEXFIELD_APP_SHORT_NAME,
153 MULTI_RULE_RESULT_FLAG,
154 OBJECT_VERSION_NUMBER,
155 USE_INSTANCE_FLAG,
156 INSTANCE_LABEL,
157 PARENT_RULE_OBJECT_ID,
158 ORG_ID,
159 CREATED_BY_MODULE,
160 CREATED_BY,
161 CREATION_DATE,
162 LAST_UPDATE_LOGIN,
163 LAST_UPDATE_DATE,
164 LAST_UPDATED_BY
165 ) values (
166 X_RULE_OBJECT_ID,
167 X_APPLICATION_ID,
168 X_RULE_OBJECT_NAME,
169 X_RESULT_TYPE,
170 X_REQUIRED_FLAG,
171 X_USE_DEFAULT_VALUE_FLAG,
172 X_FLEX_VALUE_SET_ID,
173 X_FLEXFIELD_NAME,
174 X_FLEXFIELD_APP_SHORT_NAME,
175 X_MULTI_RULE_RESULT_FLAG,
176 1,
177 NVL(X_USE_INSTANCE_FLAG,'N'), --override internally to N
178 X_INSTANCE_LABEL,
179 NVL(X_PARENT_RULE_OBJECT_ID,l_parent_rule_object_id),
180 X_ORG_ID,
181 X_CREATED_BY_MODULE,
182 NVL(X_CREATED_BY,FUN_RULE_UTILITY_PKG.CREATED_BY),
183 NVL(X_CREATION_DATE,FUN_RULE_UTILITY_PKG.CREATION_DATE),
184 NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
185 NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
186 NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY)
187 )RETURNING ROWID INTO X_ROWID;
188
189
190 insert into FUN_RULE_OBJECTS_TL (
191 RULE_OBJECT_ID,
192 USER_RULE_OBJECT_NAME,
193 DESCRIPTION,
194 CREATED_BY,
195 CREATION_DATE,
196 LAST_UPDATE_LOGIN,
197 LAST_UPDATE_DATE,
198 LAST_UPDATED_BY,
199 LANGUAGE,
200 SOURCE_LANG
201 ) select
202 X_RULE_OBJECT_ID,
203 X_USER_RULE_OBJECT_NAME,
204 X_DESCRIPTION,
205 NVL(X_CREATED_BY,FUN_RULE_UTILITY_PKG.CREATED_BY),
206 NVL(X_CREATION_DATE,FUN_RULE_UTILITY_PKG.CREATION_DATE),
207 NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
208 NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
209 NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY),
210 L.LANGUAGE_CODE,
211 userenv('LANG')
212 from FND_LANGUAGES L
213 where L.INSTALLED_FLAG in ('I', 'B')
214 and not exists
215 (select NULL
216 from FUN_RULE_OBJECTS_TL T
217 where T.RULE_OBJECT_ID = X_RULE_OBJECT_ID
218 and T.LANGUAGE = L.LANGUAGE_CODE);
219
220 open c(X_RULE_OBJECT_ID);
221 fetch c into X_ROWID;
222 if (c%notfound) then
223 close c;
224 raise no_data_found;
225 end if;
226 close c;
227 END INSERT_ROW;
228
229 PROCEDURE INSERT_ROW (
230 X_ROWID IN OUT NOCOPY VARCHAR2,
231 X_RULE_OBJECT_ID IN NUMBER,
232 X_APPLICATION_ID IN NUMBER,
233 X_RULE_OBJECT_NAME IN VARCHAR2,
234 X_DEFAULT_APPLICATION_ID IN NUMBER,
235 X_DEFAULT_VALUE IN VARCHAR2,
236 X_CREATION_DATE IN DATE DEFAULT NULL,
237 X_CREATED_BY IN NUMBER DEFAULT NULL,
238 X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
239 X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
240 X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
241 ) IS
242
243 cursor C(id number) is select ROWID from FUN_RULE_OBJ_ATTRIBUTES
244 where RULE_OBJECT_ID = id
245 ;
246
247
248 BEGIN
249
250 insert into FUN_RULE_OBJ_ATTRIBUTES (
251 RULE_OBJECT_ID,
252 DEFAULT_APPLICATION_ID,
253 DEFAULT_VALUE,
254 CREATED_BY,
255 CREATION_DATE,
256 LAST_UPDATE_LOGIN,
257 LAST_UPDATE_DATE,
258 LAST_UPDATED_BY
259 )
260 values (
261 X_RULE_OBJECT_ID,
262 X_DEFAULT_APPLICATION_ID,
263 X_DEFAULT_VALUE,
264 NVL(X_CREATED_BY,FUN_RULE_UTILITY_PKG.CREATED_BY),
265 NVL(X_CREATION_DATE,FUN_RULE_UTILITY_PKG.CREATION_DATE),
266 NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
267 NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
268 NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY)
269 );
270
271 open c(X_RULE_OBJECT_ID);
272 fetch c into X_ROWID;
273 if (c%notfound) then
274 close c;
275 raise no_data_found;
276 end if;
277 close c;
278 END INSERT_ROW;
279
280
281 PROCEDURE LOCK_ROW (
282 X_RULE_OBJECT_ID IN NUMBER,
283 X_OBJECT_VERSION_NUMBER IN NUMBER
284 ) IS
285 cursor c is select
286 OBJECT_VERSION_NUMBER
287 from FUN_RULE_OBJECTS_B
288 where RULE_OBJECT_ID = X_RULE_OBJECT_ID
289 for update of RULE_OBJECT_ID nowait;
290 recinfo c%rowtype;
291
292 begin
293 open c;
294 fetch c into recinfo;
295 if (c%notfound) then
296 close c;
297 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
298 app_exception.raise_exception;
299 end if;
300 close c;
301 if (((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
302 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
303 ) then
304 null;
305 else
306 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
307 app_exception.raise_exception;
308 end if;
309 return;
310 end LOCK_ROW;
311
312 PROCEDURE UPDATE_ROW (
313 X_RULE_OBJECT_ID IN NUMBER,
314 X_APPLICATION_ID IN NUMBER,
315 X_RULE_OBJECT_NAME IN VARCHAR2,
316 X_RESULT_TYPE IN VARCHAR2,
317 X_REQUIRED_FLAG IN VARCHAR2,
318 X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
319 X_DEFAULT_APPLICATION_ID IN NUMBER,
320 X_DEFAULT_VALUE IN VARCHAR2,
321 X_FLEX_VALUE_SET_ID IN NUMBER,
322 X_FLEXFIELD_NAME IN VARCHAR2,
323 X_FLEXFIELD_APP_SHORT_NAME IN VARCHAR2,
324 X_MULTI_RULE_RESULT_FLAG IN VARCHAR2,
325 X_OBJECT_VERSION_NUMBER IN NUMBER,
326 X_CREATED_BY_MODULE IN VARCHAR2,
327 X_USER_RULE_OBJECT_NAME IN VARCHAR2,
328 X_DESCRIPTION IN VARCHAR2,
329 X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
330 X_INSTANCE_LABEL IN VARCHAR2 DEFAULT NULL,
331 X_PARENT_RULE_OBJECT_ID IN NUMBER DEFAULT NULL,
332 X_ORG_ID IN NUMBER DEFAULT NULL,
333 X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
334 X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
335 X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
336 ) IS
337 begin
338
339 update_row(X_APPLICATION_ID,
340 X_RULE_OBJECT_ID,
341 X_RULE_OBJECT_NAME,
342 X_RESULT_TYPE,
343 X_REQUIRED_FLAG,
344 X_USE_DEFAULT_VALUE_FLAG,
345 X_FLEX_VALUE_SET_ID,
346 X_FLEXFIELD_NAME,
347 X_FLEXFIELD_APP_SHORT_NAME,
348 X_MULTI_RULE_RESULT_FLAG,
349 X_CREATED_BY_MODULE,
350 X_USER_RULE_OBJECT_NAME,
351 X_DESCRIPTION,
352 X_USE_INSTANCE_FLAG,
353 X_INSTANCE_LABEL,
354 X_PARENT_RULE_OBJECT_ID,
355 X_ORG_ID,
356 X_LAST_UPDATE_DATE,
357 X_LAST_UPDATED_BY,
358 X_LAST_UPDATE_LOGIN
359 );
360
361 update_row(X_APPLICATION_ID,
362 X_RULE_OBJECT_ID,
363 X_DEFAULT_APPLICATION_ID,
364 X_DEFAULT_VALUE,
365 X_LAST_UPDATE_DATE,
366 X_LAST_UPDATED_BY,
367 X_LAST_UPDATE_LOGIN
368 );
369
370 end UPDATE_ROW;
371
372 procedure UPDATE_ROW (
373 X_APPLICATION_ID in NUMBER,
374 X_RULE_OBJECT_ID in NUMBER,
375 X_RULE_OBJECT_NAME in VARCHAR2,
376 X_RESULT_TYPE in VARCHAR2,
377 X_REQUIRED_FLAG in VARCHAR2,
378 X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
379 X_FLEX_VALUE_SET_ID in NUMBER,
380 X_FLEXFIELD_NAME in VARCHAR2,
381 X_FLEXFIELD_APP_SHORT_NAME in VARCHAR2,
382 X_MULTI_RULE_RESULT_FLAG in VARCHAR2,
383 X_CREATED_BY_MODULE in VARCHAR2,
384 X_USER_RULE_OBJECT_NAME in VARCHAR2,
385 X_DESCRIPTION in VARCHAR2,
386 X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
387 X_INSTANCE_LABEL IN VARCHAR2 DEFAULT NULL,
388 X_PARENT_RULE_OBJECT_ID IN NUMBER DEFAULT NULL,
389 X_ORG_ID IN NUMBER DEFAULT NULL,
390 X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
391 X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
392 X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
393 ) is
394 begin
395
396 update FUN_RULE_OBJECTS_B set
397 RULE_OBJECT_NAME = X_RULE_OBJECT_NAME,
398 RESULT_TYPE = X_RESULT_TYPE,
399 REQUIRED_FLAG = X_REQUIRED_FLAG,
400 USE_DEFAULT_VALUE_FLAG = X_USE_DEFAULT_VALUE_FLAG,
401 FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID,
402 FLEXFIELD_NAME = X_FLEXFIELD_NAME,
403 FLEXFIELD_APP_SHORT_NAME = X_FLEXFIELD_APP_SHORT_NAME,
404 MULTI_RULE_RESULT_FLAG = X_MULTI_RULE_RESULT_FLAG,
405 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
406 CREATED_BY_MODULE = X_CREATED_BY_MODULE,
407 USE_INSTANCE_FLAG = NVL(X_USE_INSTANCE_FLAG,'N'),
408 INSTANCE_LABEL = X_INSTANCE_LABEL,
409 PARENT_RULE_OBJECT_ID = X_PARENT_RULE_OBJECT_ID,
410 ORG_ID = X_ORG_ID,
411 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
412 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY),
413 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN)
414 where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
415
416 if (sql%notfound) then
417 raise no_data_found;
418 end if;
419
420 update FUN_RULE_OBJECTS_TL set
421 USER_RULE_OBJECT_NAME = X_USER_RULE_OBJECT_NAME,
422 DESCRIPTION = X_DESCRIPTION,
423 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
424 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY),
425 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
426 SOURCE_LANG = userenv('LANG')
427 where RULE_OBJECT_ID = X_RULE_OBJECT_ID
428 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
429
430 if (sql%notfound) then
431 raise no_data_found;
432 end if;
433 end UPDATE_ROW;
434
435 procedure UPDATE_ROW (
436 X_APPLICATION_ID in NUMBER,
437 X_RULE_OBJECT_ID in NUMBER,
438 X_DEFAULT_APPLICATION_ID in NUMBER,
439 X_DEFAULT_VALUE in VARCHAR2,
440 X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
441 X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
442 X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
443 ) is
444 begin
445 update FUN_RULE_OBJ_ATTRIBUTES set
446 DEFAULT_APPLICATION_ID = X_DEFAULT_APPLICATION_ID,
447 DEFAULT_VALUE = X_DEFAULT_VALUE,
448 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
449 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY),
450 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN)
451 where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
452
453 if (sql%notfound) then
454 raise no_data_found;
455 end if;
456 end UPDATE_ROW;
457
458 PROCEDURE DELETE_ROW (
459 X_RULE_OBJECT_NAME IN VARCHAR2,
460 X_APPLICATION_ID IN NUMBER
461
462 ) IS
463 begin
464
465 delete from FUN_RULE_OBJ_ATTRIBUTES
466 where RULE_OBJECT_ID in (select RULE_OBJECT_ID
467 from FUN_RULE_OBJECTS_B
468 where RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
469 AND APPLICATION_ID = X_APPLICATION_ID
470 );
471
472 if (sql%notfound) then
473 raise no_data_found;
474 end if;
475
476 delete from FUN_RULE_OBJECTS_TL
477 where RULE_OBJECT_ID in (select RULE_OBJECT_ID
478 from FUN_RULE_OBJECTS_B
479 where RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
480 AND APPLICATION_ID = X_APPLICATION_ID
481 );
482
483 if (sql%notfound) then
484 raise no_data_found;
485 end if;
486
487 delete from FUN_RULE_OBJECTS_B
488 where RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
489 AND APPLICATION_ID = X_APPLICATION_ID;
490
491
492 if (sql%notfound) then
493 raise no_data_found;
494 end if;
495
496 end DELETE_ROW;
497
498
499 PROCEDURE DELETE_ROW (
500 X_RULE_OBJECT_NAME IN VARCHAR2,
501 X_APPLICATION_ID IN NUMBER,
502 X_INSTANCE_LABEL IN VARCHAR2,
503 X_ORG_ID IN NUMBER
504 ) IS
505 begin
506
507 delete from FUN_RULE_OBJECTS_B
508 where RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
509 AND APPLICATION_ID = X_APPLICATION_ID
510 AND
511 ( (INSTANCE_LABEL IS NULL AND X_INSTANCE_LABEL IS NULL) OR
512 (INSTANCE_LABEL IS NOT NULL AND X_INSTANCE_LABEL IS NOT NULL AND INSTANCE_LABEL = X_INSTANCE_LABEL))
513 AND
514 ( (ORG_ID IS NULL AND X_ORG_ID IS NULL) OR
515 (ORG_ID IS NOT NULL AND X_ORG_ID IS NOT NULL AND ORG_ID = X_ORG_ID))
516 AND PARENT_RULE_OBJECT_ID IS NOT NULL;
517
518
519 if (sql%notfound) then
520 raise no_data_found;
521 end if;
522
523 end DELETE_ROW;
524
525 PROCEDURE DELETE_ROW (
526 X_RULE_OBJECT_ID IN NUMBER
527 ) IS
528 begin
529 delete from FUN_RULE_OBJECTS_TL
530 where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
531
532 if (sql%notfound) then
533 raise no_data_found;
534 end if;
535
536 delete from FUN_RULE_OBJECTS_B
537 where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
538
539 if (sql%notfound) then
540 raise no_data_found;
541 end if;
542
543 delete from FUN_RULE_OBJ_ATTRIBUTES
544 where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
545
546 if (sql%notfound) then
547 raise no_data_found;
548 end if;
549
550 end DELETE_ROW;
551
552
553 PROCEDURE Select_Row (
554 X_RULE_OBJECT_NAME IN OUT NOCOPY VARCHAR2,
555 X_RULE_OBJECT_ID OUT NOCOPY NUMBER,
556 X_APPLICATION_ID IN OUT NOCOPY NUMBER,
557 X_USER_RULE_OBJECT_NAME OUT NOCOPY VARCHAR2,
558 X_DESCRIPTION OUT NOCOPY VARCHAR2,
559 X_RESULT_TYPE OUT NOCOPY VARCHAR2,
560 X_REQUIRED_FLAG OUT NOCOPY VARCHAR2,
561 X_USE_DEFAULT_VALUE_FLAG OUT NOCOPY VARCHAR2,
562 X_DEFAULT_APPLICATION_ID OUT NOCOPY NUMBER,
563 X_DEFAULT_VALUE OUT NOCOPY VARCHAR2,
564 X_FLEX_VALUE_SET_ID OUT NOCOPY NUMBER,
565 X_FLEXFIELD_NAME OUT NOCOPY VARCHAR2,
566 X_FLEXFIELD_APP_SHORT_NAME OUT NOCOPY VARCHAR2,
567 X_MULTI_RULE_RESULT_FLAG OUT NOCOPY VARCHAR2,
568 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
569 X_USE_INSTANCE_FLAG OUT NOCOPY VARCHAR2,
570 X_INSTANCE_LABEL OUT NOCOPY VARCHAR2,
571 X_PARENT_RULE_OBJECT_ID OUT NOCOPY NUMBER,
572 X_ORG_ID OUT NOCOPY NUMBER
573 ) IS
574
575 l_count NUMBER;
576 BEGIN
577
578 -- If INSTANCE_LABEL IS NULL and ORG_ID is NULL , then we want the parent rule object
579 -- to be returned to public api. otherwiase we will return the record
580 -- with the instance label passed from the public api.
581
582 IF(X_INSTANCE_LABEL IS NULL AND X_ORG_ID IS NULL) THEN
583 SELECT
584 RULE_OBJECT_NAME,
585 RULE_OBJECT_ID,
586 APPLICATION_ID,
587 USER_RULE_OBJECT_NAME,
588 DESCRIPTION,
589 RESULT_TYPE,
590 REQUIRED_FLAG,
591 USE_DEFAULT_VALUE_FLAG,
592 DEFAULT_APPLICATION_ID,
593 DEFAULT_VALUE,
594 FLEX_VALUE_SET_ID,
595 FLEXFIELD_NAME,
596 FLEXFIELD_APP_SHORT_NAME,
597 MULTI_RULE_RESULT_FLAG,
598 CREATED_BY_MODULE,
599 USE_INSTANCE_FLAG ,
600 INSTANCE_LABEL ,
601 PARENT_RULE_OBJECT_ID ,
602 ORG_ID
603 INTO
604 X_RULE_OBJECT_NAME,
605 X_RULE_OBJECT_ID,
606 X_APPLICATION_ID,
607 X_USER_RULE_OBJECT_NAME,
608 X_DESCRIPTION,
609 X_RESULT_TYPE,
610 X_REQUIRED_FLAG,
611 X_USE_DEFAULT_VALUE_FLAG,
612 X_DEFAULT_APPLICATION_ID,
613 X_DEFAULT_VALUE,
614 X_FLEX_VALUE_SET_ID,
615 X_FLEXFIELD_NAME,
616 X_FLEXFIELD_APP_SHORT_NAME,
617 X_MULTI_RULE_RESULT_FLAG,
618 X_CREATED_BY_MODULE,
619 X_USE_INSTANCE_FLAG,
620 X_INSTANCE_LABEL,
621 X_PARENT_RULE_OBJECT_ID,
622 X_ORG_ID
623 FROM FUN_RULE_OBJECTS_VL
624 WHERE RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
625 AND APPLICATION_ID = X_APPLICATION_ID
626 AND INSTANCE_LABEL IS NULL
627 AND ORG_ID IS NULL;
628 ELSE
629 SELECT
630 RULE_OBJECT_NAME,
631 RULE_OBJECT_ID,
632 APPLICATION_ID,
633 USER_RULE_OBJECT_NAME,
634 DESCRIPTION,
635 RESULT_TYPE,
636 REQUIRED_FLAG,
637 USE_DEFAULT_VALUE_FLAG,
638 DEFAULT_APPLICATION_ID,
639 DEFAULT_VALUE,
640 FLEX_VALUE_SET_ID,
641 FLEXFIELD_NAME,
642 FLEXFIELD_APP_SHORT_NAME,
643 MULTI_RULE_RESULT_FLAG,
644 CREATED_BY_MODULE,
645 USE_INSTANCE_FLAG ,
646 INSTANCE_LABEL ,
647 PARENT_RULE_OBJECT_ID ,
648 ORG_ID
649 INTO
650 X_RULE_OBJECT_NAME,
651 X_RULE_OBJECT_ID,
652 X_APPLICATION_ID,
653 X_USER_RULE_OBJECT_NAME,
654 X_DESCRIPTION,
655 X_RESULT_TYPE,
656 X_REQUIRED_FLAG,
657 X_USE_DEFAULT_VALUE_FLAG,
658 X_DEFAULT_APPLICATION_ID,
659 X_DEFAULT_VALUE,
660 X_FLEX_VALUE_SET_ID,
661 X_FLEXFIELD_NAME,
662 X_FLEXFIELD_APP_SHORT_NAME,
663 X_MULTI_RULE_RESULT_FLAG,
664 X_CREATED_BY_MODULE,
665 X_USE_INSTANCE_FLAG,
666 X_INSTANCE_LABEL,
667 X_PARENT_RULE_OBJECT_ID,
668 X_ORG_ID
669 FROM FUN_RULE_OBJECTS_VL
670 WHERE RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
671 AND APPLICATION_ID = X_APPLICATION_ID
672 AND
673 ( (INSTANCE_LABEL IS NULL AND X_INSTANCE_LABEL IS NULL) OR
674 (INSTANCE_LABEL IS NOT NULL AND X_INSTANCE_LABEL IS NOT NULL AND INSTANCE_LABEL = X_INSTANCE_LABEL))
675 AND
676 ( (ORG_ID IS NULL AND X_ORG_ID IS NULL) OR
677 (ORG_ID IS NOT NULL AND X_ORG_ID IS NOT NULL AND ORG_ID = X_ORG_ID))
678 AND PARENT_RULE_OBJECT_ID IS NOT NULL;
679 END IF;
680
681 EXCEPTION
682 WHEN NO_DATA_FOUND THEN
683 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_NO_RECORD' );
684 FND_MESSAGE.SET_TOKEN( 'RECORD', 'p_rule_objects_rec');
685 FND_MESSAGE.SET_TOKEN( 'VALUE', X_RULE_OBJECT_NAME );
686 FND_MSG_PUB.ADD;
687 RAISE FND_API.G_EXC_ERROR;
688 END Select_Row;
689
690 /*Overloaded procedure to select Rule Objects record based on the RULE_OBJECT_ID passed*/
691
692 PROCEDURE Select_Row_Rob_Id (
693 X_RULE_OBJECT_NAME OUT NOCOPY VARCHAR2,
694 X_RULE_OBJECT_ID IN OUT NOCOPY NUMBER,
695 X_APPLICATION_ID OUT NOCOPY NUMBER,
696 X_USER_RULE_OBJECT_NAME OUT NOCOPY VARCHAR2,
697 X_DESCRIPTION OUT NOCOPY VARCHAR2,
698 X_RESULT_TYPE OUT NOCOPY VARCHAR2,
699 X_REQUIRED_FLAG OUT NOCOPY VARCHAR2,
700 X_USE_DEFAULT_VALUE_FLAG OUT NOCOPY VARCHAR2,
701 X_DEFAULT_APPLICATION_ID OUT NOCOPY NUMBER,
702 X_DEFAULT_VALUE OUT NOCOPY VARCHAR2,
703 X_FLEX_VALUE_SET_ID OUT NOCOPY NUMBER,
704 X_FLEXFIELD_NAME OUT NOCOPY VARCHAR2,
705 X_FLEXFIELD_APP_SHORT_NAME OUT NOCOPY VARCHAR2,
706 X_MULTI_RULE_RESULT_FLAG OUT NOCOPY VARCHAR2,
707 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
708 X_USE_INSTANCE_FLAG OUT NOCOPY VARCHAR2,
709 X_INSTANCE_LABEL OUT NOCOPY VARCHAR2,
710 X_PARENT_RULE_OBJECT_ID OUT NOCOPY NUMBER,
711 X_ORG_ID OUT NOCOPY NUMBER
712 ) IS
713
714 l_count NUMBER;
715 BEGIN
716
717 SELECT
718 RULE_OBJECT_NAME,
719 RULE_OBJECT_ID,
720 APPLICATION_ID,
721 USER_RULE_OBJECT_NAME,
722 DESCRIPTION,
723 RESULT_TYPE,
724 REQUIRED_FLAG,
725 USE_DEFAULT_VALUE_FLAG,
726 DEFAULT_APPLICATION_ID,
727 DEFAULT_VALUE,
728 FLEX_VALUE_SET_ID,
729 FLEXFIELD_NAME,
730 FLEXFIELD_APP_SHORT_NAME,
731 MULTI_RULE_RESULT_FLAG,
732 CREATED_BY_MODULE,
733 USE_INSTANCE_FLAG ,
734 INSTANCE_LABEL ,
735 PARENT_RULE_OBJECT_ID ,
736 ORG_ID
737 INTO
738 X_RULE_OBJECT_NAME,
739 X_RULE_OBJECT_ID,
740 X_APPLICATION_ID,
741 X_USER_RULE_OBJECT_NAME,
742 X_DESCRIPTION,
743 X_RESULT_TYPE,
744 X_REQUIRED_FLAG,
745 X_USE_DEFAULT_VALUE_FLAG,
746 X_DEFAULT_APPLICATION_ID,
747 X_DEFAULT_VALUE,
748 X_FLEX_VALUE_SET_ID,
749 X_FLEXFIELD_NAME,
750 X_FLEXFIELD_APP_SHORT_NAME,
751 X_MULTI_RULE_RESULT_FLAG,
752 X_CREATED_BY_MODULE,
753 X_USE_INSTANCE_FLAG,
754 X_INSTANCE_LABEL,
755 X_PARENT_RULE_OBJECT_ID,
756 X_ORG_ID
757 FROM FUN_RULE_OBJECTS_VL
758 WHERE RULE_OBJECT_ID = X_RULE_OBJECT_ID;
759
760 EXCEPTION
761 WHEN NO_DATA_FOUND THEN
762 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_NO_RECORD' );
763 FND_MESSAGE.SET_TOKEN( 'RECORD', 'p_rule_objects_rec');
764 FND_MESSAGE.SET_TOKEN( 'VALUE', X_RULE_OBJECT_NAME );
765 FND_MSG_PUB.ADD;
766 RAISE FND_API.G_EXC_ERROR;
767 END Select_Row_Rob_Id;
768
769 procedure ADD_LANGUAGE
770 is
771 begin
772 delete from FUN_RULE_OBJECTS_TL T
773 where not exists
774 (select NULL
775 from FUN_RULE_OBJECTS_B B
776 where B.RULE_OBJECT_ID = T.RULE_OBJECT_ID
777 );
778
779 update FUN_RULE_OBJECTS_TL T set (
780 USER_RULE_OBJECT_NAME,
781 DESCRIPTION
782 ) = (select
783 B.USER_RULE_OBJECT_NAME,
784 B.DESCRIPTION
785 from FUN_RULE_OBJECTS_TL B
786 where B.RULE_OBJECT_ID = T.RULE_OBJECT_ID
787 and B.LANGUAGE = T.SOURCE_LANG)
788 where (
789 T.RULE_OBJECT_ID,
790 T.LANGUAGE
791 ) in (select
792 SUBT.RULE_OBJECT_ID,
793 SUBT.LANGUAGE
794 from FUN_RULE_OBJECTS_TL SUBB, FUN_RULE_OBJECTS_TL SUBT
795 where SUBB.RULE_OBJECT_ID = SUBT.RULE_OBJECT_ID
796 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
797 and (SUBB.USER_RULE_OBJECT_NAME <> SUBT.USER_RULE_OBJECT_NAME
798 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
799 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
800 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
801 ));
802
803
804 insert into FUN_RULE_OBJECTS_TL (
805 RULE_OBJECT_ID,
806 USER_RULE_OBJECT_NAME,
807 DESCRIPTION,
808 CREATION_DATE,
809 CREATED_BY,
810 LAST_UPDATE_DATE,
811 LAST_UPDATED_BY,
812 LAST_UPDATE_LOGIN,
813 LANGUAGE,
814 SOURCE_LANG
815 ) select /*+ ORDERED */
816 B.RULE_OBJECT_ID,
817 B.USER_RULE_OBJECT_NAME,
818 B.DESCRIPTION,
819 B.CREATION_DATE,
820 B.CREATED_BY,
821 B.LAST_UPDATE_DATE,
822 B.LAST_UPDATED_BY,
823 B.LAST_UPDATE_LOGIN,
824 L.LANGUAGE_CODE,
825 B.SOURCE_LANG
826 from FUN_RULE_OBJECTS_TL B, FND_LANGUAGES L
827 where L.INSTALLED_FLAG in ('I', 'B')
828 and B.LANGUAGE = userenv('LANG')
829 and not exists
830 (select NULL
831 from FUN_RULE_OBJECTS_TL T
832 where T.RULE_OBJECT_ID = B.RULE_OBJECT_ID
833 and T.LANGUAGE = L.LANGUAGE_CODE);
834 end ADD_LANGUAGE;
835
836
837
838 PROCEDURE TRANSLATE_ROW(
839 X_APP_SHORT_NAME in VARCHAR2,
840 X_RULE_OBJECT_NAME in VARCHAR2,
841 X_OWNER in VARCHAR2,
842 X_USER_RULE_OBJECT_NAME in VARCHAR2,
843 X_DESCRIPTION in VARCHAR2,
844 X_CUSTOM_MODE in VARCHAR2,
845 X_LAST_UPDATE_DATE in VARCHAR2
846 )
847 IS
848 appid number;
849 roid number;
850
851 f_luby number; -- entity owner in file
852 f_ludate date; -- entity update date in file
853 db_luby number; -- entity owner in db
854 db_ludate date; -- entity update date in db
855 BEGIN
856
857 -- Translate owner to file_last_updated_by
858 f_luby := fnd_load_util.owner_id(x_owner);
859
860 -- Translate char last_update_date to date
861 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
862
863 BEGIN
864 SELECT application_id INTO appid
865 FROM fnd_application
866 WHERE application_short_name = X_APP_SHORT_NAME;
867 EXCEPTION
868 WHEN NO_DATA_FOUND THEN
869 -- Since this should never happen, throwing an exception with hard coded message text
870 app_exception.raise_exception(exception_text=>'Invalid application short name - '||X_APP_SHORT_NAME);
871 END;
872
873 BEGIN
874 select RULE_OBJECT_ID
875 into roid
876 from FUN_RULE_OBJECTS_B
877 where APPLICATION_ID = appid
878 and RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
879 AND PARENT_RULE_OBJECT_ID IS NULL;
880
881 EXCEPTION
882 WHEN NO_DATA_FOUND THEN
883 -- Since this should never happen, throwing an exception with hard coded message text
884 app_exception.raise_exception(exception_text=>'Invalid rule object name - '||x_rule_object_name);
885 END;
886
887 BEGIN
888 select last_updated_by, last_update_date
889 into db_luby, db_ludate
890 from FUN_RULE_OBJECTS_TL
891 where rule_object_id = roid
892 and language = userenv('LANG');
893 EXCEPTION
894 WHEN NO_DATA_FOUND THEN
895 -- Since this should never happen, throwing an exception with hard coded message text
896 app_exception.raise_exception(exception_text=>'Unable to find translation row for rule object - '||x_rule_object_name||','||userenv('LANG'));
897 END;
898
899 -- c. owners are the same, and file_date > db_date
900 if (fnd_load_util.UPLOAD_TEST(
901 p_file_id => f_luby,
902 p_file_lud => f_ludate,
903 p_db_id => db_luby,
904 p_db_lud => db_ludate,
905 p_custom_mode => x_custom_mode))
906 then
907 update FUN_RULE_OBJECTS_TL
908 set user_rule_object_name = nvl(x_user_rule_object_name, user_rule_object_name),
909 description = nvl(x_description, description),
910 source_lang = userenv('LANG')
911 where rule_object_id = roid
912 and userenv('LANG') in (language, source_lang);
913 end if;
914 END TRANSLATE_ROW;
915
916
917 /* Currently we are not supporting the seeding of Rule Object Instances.
918 Only if the USE_INSTANCE_FLAG is Y and update mode, then we will propagate
919 the changes to all the instances. */
920
921 procedure LOAD_ROW (
922 X_APP_SHORT_NAME in VARCHAR2,
923 X_RULE_OBJECT_NAME in VARCHAR2,
924 X_RESULT_TYPE in VARCHAR2,
925 X_REQUIRED_FLAG in VARCHAR2,
926 X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
927 X_DEFAULT_APP_SHORT_NAME in VARCHAR2,
928 X_DEFAULT_VALUE in VARCHAR2,
929 X_FLEX_VALUE_SET_NAME in VARCHAR2,
930 X_FLEXFIELD_NAME in VARCHAR2,
931 X_FLEXFIELD_APP_SHORT_NAME in VARCHAR2,
932 X_MULTI_RULE_RESULT_FLAG in VARCHAR2,
933 X_USER_RULE_OBJECT_NAME in VARCHAR2,
934 X_DESCRIPTION in VARCHAR2,
935 X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
936 X_OWNER IN VARCHAR2,
937 X_LAST_UPDATE_DATE IN VARCHAR2,
938 X_ATT_OWNER IN VARCHAR2,
939 X_ATT_LAST_UPDATE_DATE IN VARCHAR2,
940 X_CUSTOM_MODE IN VARCHAR2)
941 is
942 begin
943 LOAD_ROW(X_APP_SHORT_NAME,
944 X_RULE_OBJECT_NAME,
945 X_RESULT_TYPE,
946 X_REQUIRED_FLAG,
947 X_USE_DEFAULT_VALUE_FLAG,
948 X_FLEX_VALUE_SET_NAME,
949 X_FLEXFIELD_NAME,
950 X_FLEXFIELD_APP_SHORT_NAME,
951 X_MULTI_RULE_RESULT_FLAG,
952 X_USER_RULE_OBJECT_NAME,
953 X_DESCRIPTION,
954 NVL(X_USE_INSTANCE_FLAG, 'N'),
955 X_OWNER,
956 X_LAST_UPDATE_DATE,
957 X_CUSTOM_MODE);
958
959 LOAD_ROW(X_APP_SHORT_NAME,
960 X_RULE_OBJECT_NAME,
961 X_DEFAULT_APP_SHORT_NAME,
962 X_DEFAULT_VALUE,
963 X_OWNER,
964 X_LAST_UPDATE_DATE,
965 X_CUSTOM_MODE);
966 end LOAD_ROW;
967
968 procedure LOAD_ROW (
969 X_APP_SHORT_NAME in VARCHAR2,
970 X_RULE_OBJECT_NAME in VARCHAR2,
971 X_RESULT_TYPE in VARCHAR2,
972 X_REQUIRED_FLAG in VARCHAR2,
973 X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
974 X_FLEX_VALUE_SET_NAME in VARCHAR2,
975 X_FLEXFIELD_NAME in VARCHAR2,
976 X_FLEXFIELD_APP_SHORT_NAME in VARCHAR2,
977 X_MULTI_RULE_RESULT_FLAG in VARCHAR2,
978 X_USER_RULE_OBJECT_NAME in VARCHAR2,
979 X_DESCRIPTION in VARCHAR2,
980 X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
981 X_OWNER IN VARCHAR2,
982 X_LAST_UPDATE_DATE IN VARCHAR2,
983 X_CUSTOM_MODE IN VARCHAR2)
984 is
985 appid number := null;
986 vsid number := null;
987
988 row_id varchar2(64);
989 f_luby number; -- entity owner in file
990 f_ludate date; -- entity update date in file
991 db_luby number; -- entity owner in db
992 db_ludate date; -- entity update date in db
993 l_use_instance_flag VARCHAR2(1);
994 l_parent_rule_object_id fun_rule_objects_b.parent_rule_object_id%type;
995
996 roid number;
997
998 --For restoring the original values related to INSTANCE information.
999
1000 CURSOR FUN_RULE_OBJECTS_CUR(p_rule_object_id NUMBER) IS
1001 SELECT
1002 B.RULE_OBJECT_ID,
1003 B.USE_INSTANCE_FLAG,
1004 B.INSTANCE_LABEL,
1005 B.PARENT_RULE_OBJECT_ID,
1006 B.ORG_ID
1007 FROM FUN_RULE_OBJECTS_B B
1008 WHERE B.PARENT_RULE_OBJECT_ID = p_rule_object_id;
1009
1010 begin
1011 --
1012 -- Get the APPLICATION_ID. Required
1013 begin
1014 SELECT application_id INTO appid
1015 FROM fnd_application
1016 WHERE application_short_name = X_APP_SHORT_NAME;
1017 exception
1018 WHEN NO_DATA_FOUND THEN
1019 -- Since this should never happen, throwing an exception with hard coded message text
1020 app_exception.raise_exception(exception_text=>'Invalid application short name - '||X_APP_SHORT_NAME);
1021 end;
1022
1023 --
1024 -- Get the FLEX_VALUE_SET_ID. Required only if name is not null
1025 IF x_flex_value_set_name IS NOT NULL THEN
1026 begin
1027 select flex_value_set_id into vsid
1028 from fnd_flex_value_sets
1029 where flex_value_set_name = X_FLEX_VALUE_SET_NAME;
1030 exception
1031 WHEN NO_DATA_FOUND THEN
1032 -- Since this should never happen, throwing an exception with hard coded message text
1033 app_exception.raise_exception(exception_text=>'Invalid value set name - '||x_flex_value_set_name);
1034 end;
1035 ELSE
1036 vsid := NULL;
1037 END IF;
1038
1039 -- Translate owner to file_last_updated_by
1040 f_luby := fnd_load_util.owner_id(x_owner);
1041
1042 -- Translate char last_update_date to date
1043 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1044
1045 --
1046 -- Get the RULE_OBJECT_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
1047 --
1048 -- Allow the SELECT to raise NO_DATA_FOUND so that it is caught and we
1049 -- go through the INSERT routine.
1050
1051 select RULE_OBJECT_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE , USE_INSTANCE_FLAG
1052 into roid, db_luby, db_ludate, l_use_instance_flag
1053 from FUN_RULE_OBJECTS_B
1054 where APPLICATION_ID = appid
1055 and RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
1056 AND PARENT_RULE_OBJECT_ID IS NULL;
1057
1058 --Raise an internal error, if developer wants to update the USE_INSTANCE_FLAG from Y to N.
1059 --Else, if the flag is NULL and NULL is passed, then make it as N
1060 if ( l_use_instance_flag = 'Y' AND X_USE_INSTANCE_FLAG = 'N') then
1061 app_exception.raise_exception(exception_text=>'Cannot update USE_INSTANCE_FLAG from Y to N. Please use the upgrdae script for this');
1062 elsif ( l_use_instance_flag IS NULL AND X_USE_INSTANCE_FLAG IS NULL) then
1063 l_use_instance_flag := 'N';
1064 else
1065 l_use_instance_flag := X_USE_INSTANCE_FLAG;
1066 end if;
1067
1068
1069 if (fnd_load_util.UPLOAD_TEST(
1070 p_file_id => f_luby,
1071 p_file_lud => f_ludate,
1072 p_db_id => db_luby,
1073 p_db_lud => db_ludate,
1074 p_custom_mode => x_custom_mode))
1075
1076 then
1077 /*For Parent Rule Object, dont allow the users to Update the USE_INSTANCE_FLAG.
1078 Also, for these rule objects, INSTANCE_LABEL, PARENT_RULE_OBJECT_ID, ORG_ID
1079 should always be NULL. */
1080
1081 UPDATE_ROW (
1082 appid,
1083 roid,
1084 X_RULE_OBJECT_NAME,
1085 X_RESULT_TYPE,
1086 X_REQUIRED_FLAG,
1087 X_USE_DEFAULT_VALUE_FLAG,
1088 vsid,
1089 X_FLEXFIELD_NAME,
1090 X_FLEXFIELD_APP_SHORT_NAME,
1091 X_MULTI_RULE_RESULT_FLAG,
1092 'ORACLE',
1093 X_USER_RULE_OBJECT_NAME,
1094 X_DESCRIPTION,
1095 l_use_instance_flag,
1096 null, --INSTANCE_LABEL,
1097 null, --PARENT_RULE_OBJECT_ID,
1098 null, --ORG_ID,
1099 f_ludate,
1100 f_luby,
1101 0);
1102
1103 --After successful Update to Parent Rule Object,
1104 --we should check if any Rule Object Instances exists or not . If exists,
1105 --then propagate the changes from Parent Rule Object's non Instance information
1106 --to Rule Object Instances.
1107
1108 BEGIN
1109 IF(upper(l_use_instance_flag) = 'Y') THEN
1110 FOR C_REC IN FUN_RULE_OBJECTS_CUR(roid) LOOP
1111
1112 UPDATE_ROW (
1113 appid,
1114 C_REC.RULE_OBJECT_ID,
1115 X_RULE_OBJECT_NAME,
1116 X_RESULT_TYPE,
1117 X_REQUIRED_FLAG,
1118 X_USE_DEFAULT_VALUE_FLAG,
1119 vsid,
1120 X_FLEXFIELD_NAME,
1121 X_FLEXFIELD_APP_SHORT_NAME,
1122 X_MULTI_RULE_RESULT_FLAG,
1123 'ORACLE',
1124 X_USER_RULE_OBJECT_NAME,
1125 X_DESCRIPTION,
1126 C_REC.USE_INSTANCE_FLAG,
1127 C_REC.INSTANCE_LABEL,
1128 C_REC.PARENT_RULE_OBJECT_ID,
1129 C_REC.ORG_ID,
1130 f_ludate,
1131 f_luby,
1132 0);
1133
1134 END LOOP;
1135 END IF;
1136 EXCEPTION
1137 WHEN NO_DATA_FOUND THEN
1138 NULL;
1139 END;
1140 --End of Rule Object Instance changes propagation for UPDATE Mode.
1141 end if;
1142
1143 EXCEPTION
1144
1145 WHEN NO_DATA_FOUND THEN
1146 SELECT fun_rule_objects_s.nextval into roid from dual;
1147
1148 --Pass NULL values for INSTANCE_LABEL, PARENT_RULE_OBJECT_ID, ORG_ID
1149 --for Rule Object Instances because we dont support seeding of the
1150 --Rule Object Instance. may be we need it later.
1151
1152 INSERT_ROW (
1153 row_id,
1154 roid,
1155 appid,
1156 X_RULE_OBJECT_NAME,
1157 X_RESULT_TYPE,
1158 X_REQUIRED_FLAG,
1159 X_USE_DEFAULT_VALUE_FLAG,
1160 vsid,
1161 X_FLEXFIELD_NAME,
1162 X_FLEXFIELD_APP_SHORT_NAME,
1163 X_MULTI_RULE_RESULT_FLAG,
1164 'ORACLE',
1165 X_USER_RULE_OBJECT_NAME,
1166 X_DESCRIPTION,
1167 X_USE_INSTANCE_FLAG,
1168 null, --INSTANCE_LABEL
1169 null, --PARENT_RULE_OBJECT_ID
1170 null, --ORG_ID
1171 f_ludate,
1172 f_luby,
1173 f_ludate,
1174 f_luby,
1175 0);
1176
1177 end LOAD_ROW;
1178
1179
1180 procedure LOAD_ROW (
1181 X_APP_SHORT_NAME in VARCHAR2,
1182 X_RULE_OBJECT_NAME in VARCHAR2,
1183 X_DEFAULT_APP_SHORT_NAME in VARCHAR2,
1184 X_DEFAULT_VALUE in VARCHAR2,
1185 X_OWNER IN VARCHAR2,
1186 X_LAST_UPDATE_DATE IN VARCHAR2,
1187 X_CUSTOM_MODE IN VARCHAR2)
1188 is
1189 appid number;
1190 default_appid number;
1191
1192 row_id varchar2(64);
1193 f_luby number; -- entity owner in file
1194 f_ludate date; -- entity update date in file
1195 db_luby number; -- entity owner in db
1196 db_ludate date; -- entity update date in db
1197
1198 roid number;
1199 begin
1200
1201 BEGIN
1202 SELECT application_id INTO appid
1203 FROM fnd_application
1204 WHERE application_short_name = X_APP_SHORT_NAME;
1205 EXCEPTION
1206 WHEN NO_DATA_FOUND THEN
1207 -- Since this should never happen, throwing an exception with hard coded message text
1208 app_exception.raise_exception(exception_text=>'Invalid application short name - '||X_APP_SHORT_NAME);
1209 END;
1210
1211 if (X_DEFAULT_APP_SHORT_NAME IS NOT NULL) then
1212 SELECT application_id INTO default_appid
1213 FROM fnd_application
1214 WHERE application_short_name = X_DEFAULT_APP_SHORT_NAME;
1215 else
1216 default_appid := null;
1217 end if;
1218
1219 -- Translate owner to file_last_updated_by
1220 f_luby := fnd_load_util.owner_id(x_owner);
1221
1222 -- Translate char last_update_date to date
1223 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1224
1225 BEGIN
1226 SELECT RULE_OBJECT_ID
1227 into roid
1228 FROM FUN_RULE_OBJECTS_B
1229 WHERE application_id = appid
1230 AND rule_object_name = X_RULE_OBJECT_NAME
1231 AND parent_rule_object_id IS NULL;
1232 EXCEPTION
1233 WHEN NO_DATA_FOUND THEN
1234 -- Since this should never happen, throwing an exception with hard coded message text
1235 app_exception.raise_exception(exception_text=>'Invalid rule object name - '||x_rule_object_name);
1236 END;
1237
1238
1239 BEGIN
1240 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1241 into db_luby, db_ludate
1242 from FUN_RULE_OBJ_ATTRIBUTES
1243 where RULE_OBJECT_ID = roid;
1244
1245 if (fnd_load_util.UPLOAD_TEST(
1246 p_file_id => f_luby,
1247 p_file_lud => f_ludate,
1248 p_db_id => db_luby,
1249 p_db_lud => db_ludate,
1250 p_custom_mode => x_custom_mode))
1251 then
1252 UPDATE_ROW (
1253 appid,
1254 roid,
1255 default_appid,
1256 X_DEFAULT_VALUE,
1257 f_ludate,
1258 f_luby,
1259 0);
1260 end if;
1261
1262 EXCEPTION
1263 WHEN NO_DATA_FOUND THEN
1264 INSERT_ROW (row_id,
1265 roid,
1266 appid,
1267 X_RULE_OBJECT_NAME,
1268 default_appid,
1269 X_DEFAULT_VALUE,
1270 f_ludate,
1271 f_luby,
1272 f_ludate,
1273 f_luby,
1274 0);
1275
1276 END;
1277 end LOAD_ROW;
1278
1279 END FUN_RULE_OBJECTS_PKG;
[Home] [Help]