1 PACKAGE BODY FUN_RULE_CRIT_PARAMS_PKG AS
2 /*$Header: FUNXTMRULRCPTBB.pls 120.6.12010000.2 2008/08/06 07:43:49 makansal ship $ */
3
4 PROCEDURE INSERT_ROW (
5 X_ROWID IN OUT NOCOPY VARCHAR2,
6 X_CRITERIA_PARAM_ID IN NUMBER,
7 X_RULE_OBJECT_ID IN NUMBER,
8 X_PARAM_NAME IN VARCHAR2,
9 X_DATA_TYPE IN VARCHAR2,
10 X_FLEX_VALUE_SET_ID IN NUMBER,
11 X_CREATED_BY_MODULE IN VARCHAR2,
12 X_USER_PARAM_NAME IN VARCHAR2,
13 X_DESCRIPTION IN VARCHAR2,
14 X_TIP_TEXT IN VARCHAR2,
15 X_CREATION_DATE IN DATE DEFAULT NULL,
16 X_CREATED_BY IN NUMBER DEFAULT NULL,
17 X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
18 X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
19 X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
20 ) IS
21
22 l_seq_val FUN_RULE_CRIT_PARAMS_B.CRITERIA_PARAM_ID%TYPE;
23
24 begin
25
26
27 IF X_CRITERIA_PARAM_ID IS NULL THEN
28 select FUN_RULE_CRITERIA_PARAMS_S.NEXTVAL into l_seq_val from dual;
29 END IF;
30
31 insert into FUN_RULE_CRIT_PARAMS_B (
32 CRITERIA_PARAM_ID,
33 RULE_OBJECT_ID,
34 PARAM_NAME,
35 DATA_TYPE,
36 FLEX_VALUE_SET_ID,
37 OBJECT_VERSION_NUMBER,
38 CREATED_BY_MODULE,
39 CREATED_BY,
40 CREATION_DATE,
41 LAST_UPDATE_LOGIN,
42 LAST_UPDATE_DATE,
43 LAST_UPDATED_BY
44 ) values (
45 NVL(X_CRITERIA_PARAM_ID,l_seq_val),
46 X_RULE_OBJECT_ID,
47 X_PARAM_NAME,
48 X_DATA_TYPE,
49 X_FLEX_VALUE_SET_ID,
50 1,
51 X_CREATED_BY_MODULE,
52 FUN_RULE_UTILITY_PKG.CREATED_BY,
53 FUN_RULE_UTILITY_PKG.CREATION_DATE,
54 FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN,
55 FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE,
56 FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY
57 )RETURNING ROWID INTO X_ROWID;
58
59
60 insert into FUN_RULE_CRIT_PARAMS_TL (
61 CRITERIA_PARAM_ID,
62 USER_PARAM_NAME,
63 DESCRIPTION,
64 TIP_TEXT,
65 CREATED_BY,
66 CREATION_DATE,
67 LAST_UPDATE_LOGIN,
68 LAST_UPDATE_DATE,
69 LAST_UPDATED_BY,
70 LANGUAGE,
71 SOURCE_LANG
72 ) select
73 NVL(X_CRITERIA_PARAM_ID,l_seq_val),
74 X_USER_PARAM_NAME,
75 X_DESCRIPTION,
76 X_TIP_TEXT,
77 FUN_RULE_UTILITY_PKG.CREATED_BY,
78 FUN_RULE_UTILITY_PKG.CREATION_DATE,
79 FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN,
80 FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE,
81 FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY,
82 L.LANGUAGE_CODE,
83 userenv('LANG')
84 from FND_LANGUAGES L
85 where L.INSTALLED_FLAG in ('I', 'B')
86 and not exists
87 (select NULL
88 from FUN_RULE_CRIT_PARAMS_TL T
89 where T.CRITERIA_PARAM_ID = NVL(X_CRITERIA_PARAM_ID,l_seq_val)
90 and T.LANGUAGE = L.LANGUAGE_CODE);
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 FND_MESSAGE.SET_NAME('FUN', 'FUN_RULE_API_OTHERS_EXCEP');
95 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
96 FND_MSG_PUB.ADD;
97 RAISE FND_API.G_EXC_ERROR;
98
99 END INSERT_ROW;
100
101 PROCEDURE LOCK_ROW (
102 X_CRITERIA_PARAM_ID IN NUMBER,
103 X_OBJECT_VERSION_NUMBER IN NUMBER
104 ) IS
105
106 cursor c is select
107 OBJECT_VERSION_NUMBER
108 from FUN_RULE_CRIT_PARAMS_B
109 where CRITERIA_PARAM_ID = X_CRITERIA_PARAM_ID
110 for update of CRITERIA_PARAM_ID nowait;
111 recinfo c%rowtype;
112 begin
113 open c;
114 fetch c into recinfo;
115 if (c%notfound) then
116 close c;
117 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
118 app_exception.raise_exception;
119 end if;
120 close c;
121 if (
122 recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
123 ) then
124 null;
125 else
126 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
127 app_exception.raise_exception;
128 end if;
129
130 return;
131 end LOCK_ROW;
132
133 PROCEDURE UPDATE_ROW (
134 X_CRITERIA_PARAM_ID IN NUMBER,
135 X_RULE_OBJECT_ID IN NUMBER,
136 X_PARAM_NAME IN VARCHAR2,
137 X_DATA_TYPE IN VARCHAR2,
138 X_FLEX_VALUE_SET_ID IN NUMBER,
139 X_CREATED_BY_MODULE IN VARCHAR2,
140 X_USER_PARAM_NAME IN VARCHAR2,
141 X_DESCRIPTION IN VARCHAR2,
142 X_TIP_TEXT IN VARCHAR2,
143 X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
144 X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
145 X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
146 ) IS
147
148 l_rule_criteria_param_id FUN_RULE_CRIT_PARAMS_B.CRITERIA_PARAM_ID%TYPE;
149
150 begin
151
152 l_rule_criteria_param_id := X_CRITERIA_PARAM_ID;
153
154 IF X_CRITERIA_PARAM_ID IS NULL THEN
155
156 SELECT CRITERIA_PARAM_ID INTO l_rule_criteria_param_id
157 FROM FUN_RULE_CRIT_PARAMS_B
158 WHERE PARAM_NAME = X_PARAM_NAME
159 AND RULE_OBJECT_ID = X_RULE_OBJECT_ID;
160
161 END IF;
162
163 update FUN_RULE_CRIT_PARAMS_B set
164 RULE_OBJECT_ID = X_RULE_OBJECT_ID,
165 PARAM_NAME = X_PARAM_NAME,
166 DATA_TYPE = X_DATA_TYPE,
167 FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID,
168 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
169 CREATED_BY_MODULE = X_CREATED_BY_MODULE,
170 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
171 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
172 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY)
173 where CRITERIA_PARAM_ID = l_rule_criteria_param_id;
174
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178
179 update FUN_RULE_CRIT_PARAMS_TL set
180 USER_PARAM_NAME = X_USER_PARAM_NAME,
181 DESCRIPTION = X_DESCRIPTION,
182 TIP_TEXT = X_TIP_TEXT,
183 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
184 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
185 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY),
186 SOURCE_LANG = userenv('LANG')
187 where CRITERIA_PARAM_ID = l_rule_criteria_param_id
188 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193 end UPDATE_ROW;
194
195
196 PROCEDURE Select_Row (
197 X_CRITERIA_PARAM_ID IN OUT NOCOPY NUMBER,
198 X_PARAM_NAME OUT NOCOPY VARCHAR2,
199 X_RULE_OBJECT_ID OUT NOCOPY NUMBER,
200 X_USER_PARAM_NAME OUT NOCOPY VARCHAR2,
201 X_DESCRIPTION OUT NOCOPY VARCHAR2,
202 X_TIP_TEXT OUT NOCOPY VARCHAR2,
203 X_DATA_TYPE OUT NOCOPY VARCHAR2,
204 X_FLEX_VALUE_SET_ID OUT NOCOPY NUMBER,
205 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2
206 ) IS
207
208 BEGIN
209
210 SELECT
211 CRITERIA_PARAM_ID,
212 PARAM_NAME,
213 RULE_OBJECT_ID,
214 USER_PARAM_NAME,
215 DESCRIPTION,
216 TIP_TEXT,
217 DATA_TYPE,
218 FLEX_VALUE_SET_ID,
219 CREATED_BY_MODULE
220 INTO
221 X_CRITERIA_PARAM_ID,
222 X_PARAM_NAME,
223 X_RULE_OBJECT_ID,
224 X_USER_PARAM_NAME,
225 X_DESCRIPTION,
226 X_TIP_TEXT,
227 X_DATA_TYPE,
228 X_FLEX_VALUE_SET_ID,
229 X_CREATED_BY_MODULE
230 FROM FUN_RULE_CRIT_PARAMS_VL
231 WHERE CRITERIA_PARAM_ID = X_CRITERIA_PARAM_ID;
232
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_NO_RECORD' );
236 FND_MESSAGE.SET_TOKEN( 'RECORD', 'p_rule_crit_params_rec');
237 FND_MESSAGE.SET_TOKEN( 'VALUE', X_PARAM_NAME );
238 FND_MSG_PUB.ADD;
239 RAISE FND_API.G_EXC_ERROR;
240
241 END Select_Row;
242
243 PROCEDURE Delete_Row (X_CRITERIA_PARAM_ID IN NUMBER)
244 IS
245 BEGIN
246
247 DELETE FUN_RULE_CRIT_PARAMS_B
248 WHERE CRITERIA_PARAM_ID = X_CRITERIA_PARAM_ID;
249
250 IF ( SQL%NOTFOUND ) THEN
251 RAISE NO_DATA_FOUND;
252 END IF;
253
254 DELETE FROM FUN_RULE_CRIT_PARAMS_TL
255 WHERE CRITERIA_PARAM_ID = X_CRITERIA_PARAM_ID;
256
257 if (sql%notfound) then
258 raise no_data_found;
259 end if;
260
261
262 END Delete_Row;
263
264
265 procedure ADD_LANGUAGE
266 IS
267 BEGIN
268 delete from FUN_RULE_CRIT_PARAMS_TL T
269 where not exists
270 (select NULL
271 from FUN_RULE_CRIT_PARAMS_B B
272 where B.CRITERIA_PARAM_ID = T.CRITERIA_PARAM_ID
273 );
274
275 update FUN_RULE_CRIT_PARAMS_TL T set (
276 USER_PARAM_NAME,
277 DESCRIPTION,
278 TIP_TEXT
279 ) = (select
280 B.USER_PARAM_NAME,
281 B.DESCRIPTION,
282 B.TIP_TEXT
283 from FUN_RULE_CRIT_PARAMS_TL B
284 where B.CRITERIA_PARAM_ID = T.CRITERIA_PARAM_ID
285 and B.LANGUAGE = T.SOURCE_LANG)
286 where (
287 T.CRITERIA_PARAM_ID,
288 T.LANGUAGE
289 ) in (select
290 SUBT.CRITERIA_PARAM_ID,
291 SUBT.LANGUAGE
292 from FUN_RULE_CRIT_PARAMS_TL SUBB, FUN_RULE_CRIT_PARAMS_TL SUBT
293 where SUBB.CRITERIA_PARAM_ID = SUBT.CRITERIA_PARAM_ID
294 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
295 and (SUBB.USER_PARAM_NAME <> SUBT.USER_PARAM_NAME
296 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
297 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
298 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
299 or SUBB.TIP_TEXT <> SUBT.TIP_TEXT
300 or (SUBB.TIP_TEXT is null and SUBT.TIP_TEXT is not null)
301 or (SUBB.TIP_TEXT is not null and SUBT.TIP_TEXT is null)
302 ));
303
304 insert into FUN_RULE_CRIT_PARAMS_TL (
305 CRITERIA_PARAM_ID,
306 USER_PARAM_NAME,
307 DESCRIPTION,
308 TIP_TEXT,
309 CREATION_DATE,
310 CREATED_BY,
311 LAST_UPDATE_DATE,
312 LAST_UPDATED_BY,
313 LAST_UPDATE_LOGIN,
314 LANGUAGE,
315 SOURCE_LANG
316 ) select /*+ ORDERED */
317 B.CRITERIA_PARAM_ID,
318 B.USER_PARAM_NAME,
319 B.DESCRIPTION,
320 B.TIP_TEXT,
321 B.CREATION_DATE,
322 B.CREATED_BY,
323 B.LAST_UPDATE_DATE,
324 B.LAST_UPDATED_BY,
325 B.LAST_UPDATE_LOGIN,
326 L.LANGUAGE_CODE,
327 B.SOURCE_LANG
328 from FUN_RULE_CRIT_PARAMS_TL B, FND_LANGUAGES L
329 where L.INSTALLED_FLAG in ('I', 'B')
330 and B.LANGUAGE = userenv('LANG')
331 and not exists
332 (select NULL
333 from FUN_RULE_CRIT_PARAMS_TL T
334 where T.CRITERIA_PARAM_ID = B.CRITERIA_PARAM_ID
335 and T.LANGUAGE = L.LANGUAGE_CODE);
336 END ADD_LANGUAGE;
337
338 procedure LOAD_ROW (
339 X_APP_SHORT_NAME in VARCHAR2,
340 X_RULE_OBJECT_NAME in VARCHAR2,
341 X_PARAM_NAME in VARCHAR2,
342 X_DATA_TYPE IN VARCHAR2,
343 X_FLEX_VALUE_SET_NAME IN VARCHAR2,
344 X_USER_PARAM_NAME IN VARCHAR2,
345 X_DESCRIPTION IN VARCHAR2,
346 X_TIP_TEXT IN VARCHAR2,
347 X_OWNER IN VARCHAR2,
348 X_LAST_UPDATE_DATE IN VARCHAR2,
349 X_CUSTOM_MODE IN VARCHAR2)
350 IS
351 appid number;
352 roid number;
353 vsid number;
354
355 row_id varchar2(64);
356 f_luby number; -- entity owner in file
357 f_ludate date; -- entity update date in file
358 db_luby number; -- entity owner in db
359 db_ludate date; -- entity update date in db
360
361 cpid number;
362 begin
363
364 BEGIN
365 SELECT application_id INTO appid
366 FROM fnd_application
367 WHERE application_short_name = X_APP_SHORT_NAME;
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 -- Since this should never happen, throwing an exception with hard coded message text
371 app_exception.raise_exception(exception_text=>'Invalid application short name - '||X_APP_SHORT_NAME);
372 END;
373
374 BEGIN
375 select RULE_OBJECT_ID
376 into roid
377 from FUN_RULE_OBJECTS_B
378 where APPLICATION_ID = appid
379 and RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
380 and parent_rule_object_id is null;
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 -- Since this should never happen, throwing an exception with hard coded message text
384 app_exception.raise_exception(exception_text=>'Invalid rule object name - '||x_rule_object_name);
385 END;
386
387 BEGIN
388 select FLEX_VALUE_SET_ID
389 into vsid
390 from FND_FLEX_VALUE_SETS
391 where FLEX_VALUE_SET_NAME = X_FLEX_VALUE_SET_NAME;
392 EXCEPTION
393 WHEN NO_DATA_FOUND THEN
394 -- Since this should never happen, throwing an exception with hard coded message text
395 app_exception.raise_exception(exception_text=>'Invalid value set name - '||x_flex_value_set_name);
396 END;
397
398 -- Translate owner to file_last_updated_by
399 f_luby := fnd_load_util.owner_id(x_owner);
400
401 -- Translate char last_update_date to date
402 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
403
404 select CRITERIA_PARAM_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
405 into cpid, db_luby, db_ludate
406 from FUN_RULE_CRIT_PARAMS_B
407 where RULE_OBJECT_ID = roid
408 and PARAM_NAME = X_PARAM_NAME;
409
410 if (fnd_load_util.UPLOAD_TEST(
411 p_file_id => f_luby,
412 p_file_lud => f_ludate,
413 p_db_id => db_luby,
414 p_db_lud => db_ludate,
415 p_custom_mode => x_custom_mode))
416 then
417 UPDATE_ROW (
418 cpid,
419 roid,
420 X_PARAM_NAME,
421 X_DATA_TYPE,
422 vsid,
423 'ORACLE',
424 X_USER_PARAM_NAME,
425 X_DESCRIPTION,
426 X_TIP_TEXT,
427 f_ludate,
428 f_luby,
429 0);
430
431 end if;
432
433 EXCEPTION
434
435 WHEN NO_DATA_FOUND THEN
436 SELECT fun_rule_criteria_params_s.nextval into cpid from dual;
437
438 INSERT_ROW (
439 row_id,
440 cpid,
441 roid,
442 X_PARAM_NAME,
443 X_DATA_TYPE,
444 vsid,
445 'ORACLE',
446 X_USER_PARAM_NAME,
447 X_DESCRIPTION,
448 X_TIP_TEXT,
449 f_ludate,
450 f_luby,
451 f_ludate,
452 f_luby,
453 0);
454
455
456 end LOAD_ROW;
457
458 PROCEDURE TRANSLATE_ROW(
459 X_APP_SHORT_NAME in VARCHAR2,
460 X_RULE_OBJECT_NAME in VARCHAR2,
461 X_PARAM_NAME in VARCHAR2,
462 X_OWNER in VARCHAR2,
463 X_USER_PARAM_NAME IN VARCHAR2,
464 X_DESCRIPTION IN VARCHAR2,
465 X_TIP_TEXT IN VARCHAR2,
466 X_CUSTOM_MODE in VARCHAR2,
467 X_LAST_UPDATE_DATE in VARCHAR2
468 ) IS
469 appid number;
470 roid number;
471 cpid number;
472
473 f_luby number; -- entity owner in file
474 f_ludate date; -- entity update date in file
475 db_luby number; -- entity owner in db
476 db_ludate date; -- entity update date in db
477 BEGIN
478
479 -- Translate owner to file_last_updated_by
480 f_luby := fnd_load_util.owner_id(x_owner);
481
482 -- Translate char last_update_date to date
483 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
484
485 BEGIN
486 SELECT application_id INTO appid
487 FROM fnd_application
488 WHERE application_short_name = X_APP_SHORT_NAME;
489 EXCEPTION
490 WHEN NO_DATA_FOUND THEN
491 -- Since this should never happen, throwing an exception with hard coded message text
492 app_exception.raise_exception(exception_text=>'Invalid application short name - '||X_APP_SHORT_NAME);
493 END;
494
495 BEGIN
496 select RULE_OBJECT_ID
497 into roid
498 from FUN_RULE_OBJECTS_B
499 where APPLICATION_ID = appid
500 and RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
501 and parent_rule_object_id is null;
502 EXCEPTION
503 WHEN NO_DATA_FOUND THEN
504 -- Since this should never happen, throwing an exception with hard coded message text
505 app_exception.raise_exception(exception_text=>'Invalid rule object name - '||x_rule_object_name);
506 END;
507
508 BEGIN
509 select criteria_param_id
510 into cpid
511 from fun_rule_crit_params_b
512 where rule_object_id = roid
513 and param_name = X_PARAM_NAME;
514 EXCEPTION
515 WHEN NO_DATA_FOUND THEN
516 -- Since this should never happen, throwing an exception with hard coded message text
517 app_exception.raise_exception(exception_text=>'Invalid parameter name - '||x_param_name);
518 END;
519
520 BEGIN
521 select last_updated_by, last_update_date
522 into db_luby, db_ludate
523 from fun_rule_crit_params_tl
524 where criteria_param_id = cpid
525 and language = userenv('LANG');
526 EXCEPTION
527 WHEN NO_DATA_FOUND THEN
528 -- Since this should never happen, throwing an exception with hard coded message text
529 app_exception.raise_exception(exception_text=>'Unable to find translation row for parameter - '||x_param_name||','||userenv('LANG'));
530 END;
531
532 -- c. owners are the same, and file_date > db_date
533 if (fnd_load_util.UPLOAD_TEST(
534 p_file_id => f_luby,
535 p_file_lud => f_ludate,
536 p_db_id => db_luby,
537 p_db_lud => db_ludate,
538 p_custom_mode => x_custom_mode))
539 then
540 update fun_rule_crit_params_tl
541 set user_param_name = nvl(x_user_param_name, user_param_name),
542 description = nvl(x_description, description),
543 tip_text = nvl(x_tip_text, tip_text),
544 source_lang = userenv('LANG')
545 where criteria_param_id = cpid
546 and userenv('LANG') in (language, source_lang);
547 end if;
548 END TRANSLATE_ROW;
549
550
551 END FUN_RULE_CRIT_PARAMS_PKG;
[Home] [Help]