[Home] [Help]
PACKAGE BODY: APPS.AMS_OBJECT_RULES_PKG
Source
1 package body AMS_OBJECT_RULES_PKG as
2 /* $Header: amslobrb.pls 120.1 2005/06/27 05:38:34 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_OBJECT_RULE_ID in NUMBER,
6 X_PARAM15 in VARCHAR2,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_SECURITY_GROUP_ID in NUMBER,
9 X_APPROVAL_TYPE in VARCHAR2,
10 X_RULE_USED_BY in VARCHAR2,
11 X_RULE_USED_BY_TYPE in VARCHAR2,
12 X_RULE_TYPE in VARCHAR2,
13 X_API_TYPE in VARCHAR2,
14 X_PACKAGE_NAME in VARCHAR2,
15 X_PROCEDURE_NAME in VARCHAR2,
16 X_CLASS_NAME in VARCHAR2,
17 X_METHOD_NAME in VARCHAR2,
18 X_QUERY in VARCHAR2,
19 X_RELATED_OBJECT in VARCHAR2,
20 X_SEEDED_FLAG in VARCHAR2,
21 X_PARAM1 in VARCHAR2,
22 X_PARAM2 in VARCHAR2,
23 X_PARAM3 in VARCHAR2,
24 X_PARAM4 in VARCHAR2,
25 X_PARAM5 in VARCHAR2,
26 X_PARAM6 in VARCHAR2,
27 X_PARAM7 in VARCHAR2,
28 X_PARAM8 in VARCHAR2,
29 X_PARAM9 in VARCHAR2,
30 X_PARAM10 in VARCHAR2,
31 X_PARAM11 in VARCHAR2,
32 X_PARAM12 in VARCHAR2,
33 X_PARAM13 in VARCHAR2,
34 X_PARAM14 in VARCHAR2,
35 X_APPLICATION_ID in NUMBER,
36 X_OBJECT_RULE_NAME in VARCHAR2,
37 X_DESCRIPTION in VARCHAR2,
38 X_CREATION_DATE in DATE,
39 X_CREATED_BY in NUMBER,
40 X_LAST_UPDATE_DATE in DATE,
41 X_LAST_UPDATED_BY in NUMBER,
42 X_LAST_UPDATE_LOGIN in NUMBER
43 ) is
44
45 cursor C is select ROWID from AMS_OBJECT_RULES_B
46 where OBJECT_RULE_ID = X_OBJECT_RULE_ID
47 ;
48 begin
49 insert into AMS_OBJECT_RULES_B (
50 OBJECT_RULE_ID,
51 PARAM15,
52 OBJECT_VERSION_NUMBER,
53 --SECURITY_GROUP_ID,
54 APPROVAL_TYPE,
55 RULE_USED_BY,
56 RULE_USED_BY_TYPE,
57 RULE_TYPE,
58 API_TYPE,
59 PACKAGE_NAME,
60 PROCEDURE_NAME,
61 CLASS_NAME,
62 METHOD_NAME,
63 QUERY,
64 RELATED_OBJECT,
65 SEEDED_FLAG,
66 PARAM1,
67 PARAM2,
68 PARAM3,
69 PARAM4,
70 PARAM5,
71 PARAM6,
72 PARAM7,
73 PARAM8,
74 PARAM9,
75 PARAM10,
76 PARAM11,
77 PARAM12,
78 PARAM13,
79 PARAM14,
80 APPLICATION_ID,
81 CREATION_DATE,
82 CREATED_BY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATED_BY,
85 LAST_UPDATE_LOGIN
86 ) values (
87 X_OBJECT_RULE_ID,
88 X_PARAM15,
89 X_OBJECT_VERSION_NUMBER,
90 --X_SECURITY_GROUP_ID,
91 X_APPROVAL_TYPE,
92 X_RULE_USED_BY,
93 X_RULE_USED_BY_TYPE,
94 X_RULE_TYPE,
95 X_API_TYPE,
96 X_PACKAGE_NAME,
97 X_PROCEDURE_NAME,
98 X_CLASS_NAME,
99 X_METHOD_NAME,
100 X_QUERY,
101 X_RELATED_OBJECT,
102 X_SEEDED_FLAG,
103 X_PARAM1,
104 X_PARAM2,
105 X_PARAM3,
106 X_PARAM4,
107 X_PARAM5,
108 X_PARAM6,
109 X_PARAM7,
110 X_PARAM8,
111 X_PARAM9,
112 X_PARAM10,
113 X_PARAM11,
114 X_PARAM12,
115 X_PARAM13,
116 X_PARAM14,
117 X_APPLICATION_ID,
118 X_CREATION_DATE,
119 X_CREATED_BY,
120 X_LAST_UPDATE_DATE,
121 X_LAST_UPDATED_BY,
122 X_LAST_UPDATE_LOGIN
123 );
124
125 insert into AMS_OBJECT_RULES_TL (
126 OBJECT_RULE_ID,
127 LAST_UPDATE_DATE,
128 LAST_UPDATED_BY,
129 CREATION_DATE,
130 CREATED_BY,
131 LAST_UPDATE_LOGIN,
132 --SECURITY_GROUP_ID,
133 OBJECT_RULE_NAME,
134 DESCRIPTION,
135 LANGUAGE,
136 SOURCE_LANG
137 ) select
138 X_OBJECT_RULE_ID,
139 X_LAST_UPDATE_DATE,
140 X_LAST_UPDATED_BY,
141 X_CREATION_DATE,
142 X_CREATED_BY,
143 X_LAST_UPDATE_LOGIN,
144 --X_SECURITY_GROUP_ID,
145 X_OBJECT_RULE_NAME,
146 X_DESCRIPTION,
147 L.LANGUAGE_CODE,
148 userenv('LANG')
149 from FND_LANGUAGES L
150 where L.INSTALLED_FLAG in ('I', 'B')
151 and not exists
152 (select NULL
153 from AMS_OBJECT_RULES_TL T
154 where T.OBJECT_RULE_ID = X_OBJECT_RULE_ID
155 and T.LANGUAGE = L.LANGUAGE_CODE);
156
157 open c;
158 fetch c into X_ROWID;
159 if (c%notfound) then
160 close c;
161 raise no_data_found;
162 end if;
163 close c;
164
165 end INSERT_ROW;
166
167 procedure LOCK_ROW (
168 X_OBJECT_RULE_ID in NUMBER,
169 X_PARAM15 in VARCHAR2,
170 X_OBJECT_VERSION_NUMBER in NUMBER,
171 X_SECURITY_GROUP_ID in NUMBER,
172 X_APPROVAL_TYPE in VARCHAR2,
173 X_RULE_USED_BY in VARCHAR2,
174 X_RULE_USED_BY_TYPE in VARCHAR2,
175 X_RULE_TYPE in VARCHAR2,
176 X_API_TYPE in VARCHAR2,
177 X_PACKAGE_NAME in VARCHAR2,
178 X_PROCEDURE_NAME in VARCHAR2,
179 X_CLASS_NAME in VARCHAR2,
180 X_METHOD_NAME in VARCHAR2,
181 X_QUERY in VARCHAR2,
182 X_RELATED_OBJECT in VARCHAR2,
183 X_SEEDED_FLAG in VARCHAR2,
184 X_PARAM1 in VARCHAR2,
185 X_PARAM2 in VARCHAR2,
186 X_PARAM3 in VARCHAR2,
187 X_PARAM4 in VARCHAR2,
188 X_PARAM5 in VARCHAR2,
189 X_PARAM6 in VARCHAR2,
190 X_PARAM7 in VARCHAR2,
191 X_PARAM8 in VARCHAR2,
192 X_PARAM9 in VARCHAR2,
193 X_PARAM10 in VARCHAR2,
194 X_PARAM11 in VARCHAR2,
195 X_PARAM12 in VARCHAR2,
196 X_PARAM13 in VARCHAR2,
197 X_PARAM14 in VARCHAR2,
198 X_APPLICATION_ID in NUMBER
199 ) is
200 cursor c is select
201 PARAM15,
202 OBJECT_VERSION_NUMBER,
203 SECURITY_GROUP_ID,
204 APPROVAL_TYPE,
205 RULE_USED_BY,
206 RULE_USED_BY_TYPE,
207 RULE_TYPE,
208 API_TYPE,
209 PACKAGE_NAME,
210 PROCEDURE_NAME,
211 CLASS_NAME,
212 METHOD_NAME,
213 QUERY,
214 RELATED_OBJECT,
215 SEEDED_FLAG,
216 PARAM1,
217 PARAM2,
218 PARAM3,
219 PARAM4,
220 PARAM5,
221 PARAM6,
222 PARAM7,
223 PARAM8,
224 PARAM9,
225 PARAM10,
226 PARAM11,
227 PARAM12,
228 PARAM13,
229 PARAM14,
230 APPLICATION_ID
231 from AMS_OBJECT_RULES_B
232 where OBJECT_RULE_ID = X_OBJECT_RULE_ID
233 for update of OBJECT_RULE_ID nowait;
234 recinfo c%rowtype;
235
236 cursor c1 is select
237 OBJECT_RULE_ID,
238 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
239 from AMS_OBJECT_RULES_TL
240 where OBJECT_RULE_ID = X_OBJECT_RULE_ID
241 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
242 for update of OBJECT_RULE_ID nowait;
243 begin
244 open c;
245 fetch c into recinfo;
246 if (c%notfound) then
247 close c;
248 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
249 app_exception.raise_exception;
250 end if;
251 close c;
252 if ( ((recinfo.PARAM15 = X_PARAM15)
253 OR ((recinfo.PARAM15 is null) AND (X_PARAM15 is null)))
254 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
255 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
256 --AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
257 --OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
258 AND ((recinfo.APPROVAL_TYPE = X_APPROVAL_TYPE)
259 OR ((recinfo.APPROVAL_TYPE is null) AND (X_APPROVAL_TYPE is null)))
260 AND ((recinfo.RULE_USED_BY = X_RULE_USED_BY)
261 OR ((recinfo.RULE_USED_BY is null) AND (X_RULE_USED_BY is null)))
262 AND ((recinfo.RULE_USED_BY_TYPE = X_RULE_USED_BY_TYPE)
263 OR ((recinfo.RULE_USED_BY_TYPE is null) AND (X_RULE_USED_BY_TYPE is null)))
264 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
265 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
266 AND ((recinfo.RULE_TYPE = X_RULE_TYPE)
267 OR ((recinfo.RULE_TYPE is null) AND (X_RULE_TYPE is null)))
268 AND ((recinfo.API_TYPE = X_API_TYPE)
269 OR ((recinfo.API_TYPE is null) AND (X_API_TYPE is null)))
270 AND ((recinfo.PACKAGE_NAME = X_PACKAGE_NAME)
271 OR ((recinfo.PACKAGE_NAME is null) AND (X_PACKAGE_NAME is null)))
272 AND ((recinfo.PROCEDURE_NAME = X_PROCEDURE_NAME)
273 OR ((recinfo.PROCEDURE_NAME is null) AND (X_PROCEDURE_NAME is null)))
274 AND ((recinfo.CLASS_NAME = X_CLASS_NAME)
275 OR ((recinfo.CLASS_NAME is null) AND (X_CLASS_NAME is null)))
276 AND ((recinfo.METHOD_NAME = X_METHOD_NAME)
277 OR ((recinfo.METHOD_NAME is null) AND (X_METHOD_NAME is null)))
278 AND ((recinfo.QUERY = X_QUERY)
279 OR ((recinfo.QUERY is null) AND (X_QUERY is null)))
280 AND ((recinfo.RELATED_OBJECT = X_RELATED_OBJECT)
281 OR ((recinfo.RELATED_OBJECT is null) AND (X_RELATED_OBJECT is null)))
282 AND ((recinfo.PARAM1 = X_PARAM1)
283 OR ((recinfo.PARAM1 is null) AND (X_PARAM1 is null)))
284 AND ((recinfo.PARAM2 = X_PARAM2)
285 OR ((recinfo.PARAM2 is null) AND (X_PARAM2 is null)))
286 AND ((recinfo.PARAM3 = X_PARAM3)
287 OR ((recinfo.PARAM3 is null) AND (X_PARAM3 is null)))
288 AND ((recinfo.PARAM4 = X_PARAM4)
289 OR ((recinfo.PARAM4 is null) AND (X_PARAM4 is null)))
290 AND ((recinfo.PARAM5 = X_PARAM5)
291 OR ((recinfo.PARAM5 is null) AND (X_PARAM5 is null)))
292 AND ((recinfo.PARAM6 = X_PARAM6)
293 OR ((recinfo.PARAM6 is null) AND (X_PARAM6 is null)))
294 AND ((recinfo.PARAM7 = X_PARAM7)
295 OR ((recinfo.PARAM7 is null) AND (X_PARAM7 is null)))
296 AND ((recinfo.PARAM8 = X_PARAM8)
297 OR ((recinfo.PARAM8 is null) AND (X_PARAM8 is null)))
298 AND ((recinfo.PARAM9 = X_PARAM9)
299 OR ((recinfo.PARAM9 is null) AND (X_PARAM9 is null)))
300 AND ((recinfo.PARAM10 = X_PARAM10)
301 OR ((recinfo.PARAM10 is null) AND (X_PARAM10 is null)))
302 AND ((recinfo.PARAM11 = X_PARAM11)
303 OR ((recinfo.PARAM11 is null) AND (X_PARAM11 is null)))
304 AND ((recinfo.PARAM12 = X_PARAM12)
305 OR ((recinfo.PARAM12 is null) AND (X_PARAM12 is null)))
306 AND ((recinfo.PARAM13 = X_PARAM13)
307 OR ((recinfo.PARAM13 is null) AND (X_PARAM13 is null)))
308 AND ((recinfo.PARAM14 = X_PARAM14)
309 OR ((recinfo.PARAM14 is null) AND (X_PARAM14 is null)))
310 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
311 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
312 ) then
313 null;
314 else
315 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
316 app_exception.raise_exception;
317 end if;
318
319 for tlinfo in c1 loop
320 if (tlinfo.BASELANG = 'Y') then
321 if ( (tlinfo.OBJECT_RULE_ID = X_OBJECT_RULE_ID)
322 ) then
323 null;
324 else
325 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
326 app_exception.raise_exception;
327 end if;
328 end if;
329 end loop;
330 return;
331 end LOCK_ROW;
332
333 procedure UPDATE_ROW (
334 X_OBJECT_RULE_ID in NUMBER,
335 X_PARAM15 in VARCHAR2,
336 X_OBJECT_VERSION_NUMBER in NUMBER,
337 X_SECURITY_GROUP_ID in NUMBER,
338 X_APPROVAL_TYPE in VARCHAR2,
339 X_RULE_USED_BY in VARCHAR2,
340 X_RULE_USED_BY_TYPE in VARCHAR2,
341 X_RULE_TYPE in VARCHAR2,
342 X_API_TYPE in VARCHAR2,
343 X_PACKAGE_NAME in VARCHAR2,
344 X_PROCEDURE_NAME in VARCHAR2,
345 X_CLASS_NAME in VARCHAR2,
346 X_METHOD_NAME in VARCHAR2,
347 X_QUERY in VARCHAR2,
348 X_RELATED_OBJECT in VARCHAR2,
349 X_SEEDED_FLAG in VARCHAR2,
350 X_PARAM1 in VARCHAR2,
351 X_PARAM2 in VARCHAR2,
352 X_PARAM3 in VARCHAR2,
353 X_PARAM4 in VARCHAR2,
354 X_PARAM5 in VARCHAR2,
355 X_PARAM6 in VARCHAR2,
356 X_PARAM7 in VARCHAR2,
357 X_PARAM8 in VARCHAR2,
358 X_PARAM9 in VARCHAR2,
359 X_PARAM10 in VARCHAR2,
360 X_PARAM11 in VARCHAR2,
361 X_PARAM12 in VARCHAR2,
362 X_PARAM13 in VARCHAR2,
363 X_PARAM14 in VARCHAR2,
364 X_APPLICATION_ID in NUMBER,
365 X_OBJECT_RULE_NAME in VARCHAR2,
366 X_DESCRIPTION in VARCHAR2,
367 X_LAST_UPDATE_DATE in DATE,
368 X_LAST_UPDATED_BY in NUMBER,
369 X_LAST_UPDATE_LOGIN in NUMBER
370 ) is
371 begin
372 update AMS_OBJECT_RULES_B set
373 PARAM15 = X_PARAM15,
374 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
375 --SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
376 APPROVAL_TYPE = X_APPROVAL_TYPE,
377 RULE_USED_BY = X_RULE_USED_BY,
378 RULE_USED_BY_TYPE = X_RULE_USED_BY_TYPE,
379 RULE_TYPE = X_RULE_TYPE,
380 API_TYPE = X_API_TYPE,
381 PACKAGE_NAME = X_PACKAGE_NAME,
382 PROCEDURE_NAME = X_PROCEDURE_NAME,
383 CLASS_NAME = X_CLASS_NAME,
384 METHOD_NAME = X_METHOD_NAME,
385 QUERY = X_QUERY,
386 RELATED_OBJECT = X_RELATED_OBJECT,
387 SEEDED_FLAG = X_SEEDED_FLAG,
388 PARAM1 = X_PARAM1,
389 PARAM2 = X_PARAM2,
390 PARAM3 = X_PARAM3,
391 PARAM4 = X_PARAM4,
392 PARAM5 = X_PARAM5,
393 PARAM6 = X_PARAM6,
394 PARAM7 = X_PARAM7,
395 PARAM8 = X_PARAM8,
396 PARAM9 = X_PARAM9,
397 PARAM10 = X_PARAM10,
398 PARAM11 = X_PARAM11,
399 PARAM12 = X_PARAM12,
400 PARAM13 = X_PARAM13,
401 PARAM14 = X_PARAM14,
402 APPLICATION_ID = X_APPLICATION_ID,
403 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
404 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
405 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
406 where OBJECT_RULE_ID = X_OBJECT_RULE_ID;
407
408 if (sql%notfound) then
409 raise no_data_found;
410 end if;
411
412 update AMS_OBJECT_RULES_TL set
413 OBJECT_RULE_ID = X_OBJECT_RULE_ID,
414 OBJECT_RULE_NAME = X_OBJECT_RULE_NAME,
415 DESCRIPTION = X_DESCRIPTION,
416 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
417 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
418 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
419 SOURCE_LANG = userenv('LANG')
420 where OBJECT_RULE_ID = X_OBJECT_RULE_ID
421 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
422
423 if (sql%notfound) then
424 raise no_data_found;
425 end if;
426 end UPDATE_ROW;
427
428 procedure DELETE_ROW (
429 X_OBJECT_RULE_ID in NUMBER
430 ) is
431 begin
432 delete from AMS_OBJECT_RULES_TL
433 where OBJECT_RULE_ID = X_OBJECT_RULE_ID;
434
435 if (sql%notfound) then
436 raise no_data_found;
437 end if;
438
439 delete from AMS_OBJECT_RULES_B
440 where OBJECT_RULE_ID = X_OBJECT_RULE_ID;
441
442 if (sql%notfound) then
443 raise no_data_found;
444 end if;
445 end DELETE_ROW;
446
447 procedure TRANSLATE_ROW(
448 X_OBJECT_RULE_ID in NUMBER,
449 X_OBJECT_RULE_NAME in VARCHAR2,
450 X_DESCRIPTION in VARCHAR2,
451 X_OWNER in VARCHAR2
452 ) IS
453 begin
454 update AMS_OBJECT_RULES_TL set
455 object_rule_name = nvl(x_object_rule_name, object_rule_name),
456 description = nvl(x_description, description),
457 source_lang = userenv('LANG'),
458 last_update_date = sysdate,
459 last_updated_by = decode(x_owner, 'SEED', 1, 0),
460 last_update_login = 0
461 where OBJECT_RULE_ID = X_OBJECT_RULE_ID
462 and userenv('LANG') in (language, source_lang);
463 end TRANSLATE_ROW;
464
465 procedure LOAD_ROW (
466 X_OBJECT_RULE_ID in NUMBER,
467 X_PARAM15 in VARCHAR2,
468 X_OBJECT_VERSION_NUMBER in NUMBER,
469 X_SECURITY_GROUP_ID in NUMBER,
470 X_APPROVAL_TYPE in VARCHAR2,
471 X_RULE_USED_BY in VARCHAR2,
472 X_RULE_USED_BY_TYPE in VARCHAR2,
473 X_RULE_TYPE in VARCHAR2,
474 X_API_TYPE in VARCHAR2,
475 X_PACKAGE_NAME in VARCHAR2,
476 X_PROCEDURE_NAME in VARCHAR2,
477 X_CLASS_NAME in VARCHAR2,
478 X_METHOD_NAME in VARCHAR2,
479 X_QUERY in VARCHAR2,
480 X_RELATED_OBJECT in VARCHAR2,
481 X_SEEDED_FLAG in VARCHAR2,
482 X_PARAM1 in VARCHAR2,
483 X_PARAM2 in VARCHAR2,
484 X_PARAM3 in VARCHAR2,
485 X_PARAM4 in VARCHAR2,
486 X_PARAM5 in VARCHAR2,
487 X_PARAM6 in VARCHAR2,
488 X_PARAM7 in VARCHAR2,
489 X_PARAM8 in VARCHAR2,
490 X_PARAM9 in VARCHAR2,
491 X_PARAM10 in VARCHAR2,
492 X_PARAM11 in VARCHAR2,
493 X_PARAM12 in VARCHAR2,
494 X_PARAM13 in VARCHAR2,
495 X_PARAM14 in VARCHAR2,
496 X_APPLICATION_ID in NUMBER,
497 X_OBJECT_RULE_NAME in VARCHAR2,
498 X_DESCRIPTION in VARCHAR2,
499 X_OWNER IN VARCHAR2
500 )is
501 l_user_id number := 0;
502 l_objrule_id number;
503 l_obj_verno number;
504 l_dummy_char varchar2(1);
505 l_row_id varchar2(100);
506
507 cursor c_obj_verno is
508 select object_version_number
509 from AMS_OBJECT_RULES_B
510 where OBJECT_RULE_ID = X_OBJECT_RULE_ID;
511
512 cursor c_chk_objrule_exists is
513 select 'x'
514 from AMS_OBJECT_RULES_B
515 where OBJECT_RULE_ID = X_OBJECT_RULE_ID;
516
517 cursor c_get_objrule_id is
518 select AMS_OBJECT_RULES_B_S.nextval
519 from dual;
520
521 BEGIN
522 if X_OWNER = 'SEED' then
523 l_user_id := 1;
524 end if;
525
526 open c_chk_objrule_exists;
527 fetch c_chk_objrule_exists into l_dummy_char;
528 if c_chk_objrule_exists%notfound
529 then
530 close c_chk_objrule_exists;
531 if X_OBJECT_RULE_ID is null
532 then
533 open c_get_objrule_id;
534 fetch c_get_objrule_id into l_objrule_id;
535 close c_get_objrule_id;
536 else
537 l_objrule_id := X_OBJECT_RULE_ID;
538 end if;
539
540 AMS_OBJECT_RULES_PKG.INSERT_ROW (
541 X_ROWID => l_row_id,
542 X_OBJECT_RULE_ID => l_objrule_id,
543 X_PARAM15 => X_PARAM15,
544 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
545 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
546 X_APPROVAL_TYPE => X_APPROVAL_TYPE,
547 X_RULE_USED_BY => X_RULE_USED_BY,
548 X_RULE_USED_BY_TYPE => X_RULE_USED_BY_TYPE,
549 X_RULE_TYPE => X_RULE_TYPE,
550 X_API_TYPE => X_API_TYPE,
551 X_PACKAGE_NAME => X_PACKAGE_NAME,
552 X_PROCEDURE_NAME => X_PROCEDURE_NAME,
553 X_CLASS_NAME => X_CLASS_NAME,
554 X_METHOD_NAME => X_METHOD_NAME,
555 X_QUERY => X_QUERY,
556 X_RELATED_OBJECT => X_RELATED_OBJECT,
557 X_SEEDED_FLAG => X_SEEDED_FLAG,
558 X_PARAM1 => X_PARAM1,
559 X_PARAM2 => X_PARAM2,
560 X_PARAM3 => X_PARAM3,
561 X_PARAM4 => X_PARAM4,
562 X_PARAM5 => X_PARAM5,
563 X_PARAM6 => X_PARAM6,
564 X_PARAM7 => X_PARAM7,
565 X_PARAM8 => X_PARAM8,
566 X_PARAM9 => X_PARAM9,
567 X_PARAM10 => X_PARAM10,
568 X_PARAM11 => X_PARAM11,
569 X_PARAM12 => X_PARAM12,
570 X_PARAM13 => X_PARAM13,
571 X_PARAM14 => X_PARAM14,
572 X_APPLICATION_ID => X_APPLICATION_ID ,
573 X_OBJECT_RULE_NAME => X_OBJECT_RULE_NAME,
574 X_DESCRIPTION => X_DESCRIPTION,
575 X_CREATION_DATE => sysdate,
576 X_CREATED_BY => l_user_id,
577 X_LAST_UPDATE_DATE => sysdate,
578 X_LAST_UPDATED_BY => l_user_id,
579 X_LAST_UPDATE_LOGIN => 0
580 );
581 else
582 close c_chk_objrule_exists;
583 open c_obj_verno;
584 fetch c_obj_verno into l_obj_verno;
585 close c_obj_verno;
586
587 -- assigning value for l_user_status_id
588 l_objrule_id := X_OBJECT_RULE_ID;
589 AMS_OBJECT_RULES_PKG.UPDATE_ROW(
590 X_OBJECT_RULE_ID => X_OBJECT_RULE_ID,
591 X_PARAM15 => X_PARAM15,
592 X_OBJECT_VERSION_NUMBER => l_obj_verno,
593 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
594 X_APPROVAL_TYPE => X_APPROVAL_TYPE,
595 X_RULE_USED_BY => X_RULE_USED_BY,
596 X_RULE_USED_BY_TYPE => X_RULE_USED_BY_TYPE,
597 X_RULE_TYPE => X_RULE_TYPE,
598 X_API_TYPE => X_API_TYPE,
599 X_PACKAGE_NAME => X_PACKAGE_NAME,
600 X_PROCEDURE_NAME => X_PROCEDURE_NAME,
601 X_CLASS_NAME => X_CLASS_NAME,
602 X_METHOD_NAME => X_METHOD_NAME,
603 X_QUERY => X_QUERY,
604 X_RELATED_OBJECT => X_RELATED_OBJECT,
605 X_SEEDED_FLAG => X_SEEDED_FLAG,
606 X_PARAM1 => X_PARAM1,
607 X_PARAM2 => X_PARAM2,
608 X_PARAM3 => X_PARAM3,
609 X_PARAM4 => X_PARAM4,
610 X_PARAM5 => X_PARAM5,
611 X_PARAM6 => X_PARAM6,
612 X_PARAM7 => X_PARAM7,
613 X_PARAM8 => X_PARAM8,
614 X_PARAM9 => X_PARAM9,
615 X_PARAM10 => X_PARAM10,
616 X_PARAM11 => X_PARAM11,
617 X_PARAM12 => X_PARAM12,
618 X_PARAM13 => X_PARAM13,
619 X_PARAM14 => X_PARAM14,
620 X_APPLICATION_ID => X_APPLICATION_ID,
621 X_OBJECT_RULE_NAME => X_OBJECT_RULE_NAME,
622 X_DESCRIPTION => X_DESCRIPTION,
623 X_LAST_UPDATE_DATE => SYSDATE,
624 X_LAST_UPDATED_BY => l_user_id,
625 X_LAST_UPDATE_LOGIN => 0
626 );
627 END IF;
628
629 end LOAD_ROW;
630
631 procedure ADD_LANGUAGE
632 is
633 begin
634 delete from AMS_OBJECT_RULES_TL T
635 where not exists
636 (select NULL
637 from AMS_OBJECT_RULES_B B
638 where B.OBJECT_RULE_ID = T.OBJECT_RULE_ID
639 );
640
641 update AMS_OBJECT_RULES_TL T set (
642 OBJECT_RULE_ID
643 ) = (select
644 B.OBJECT_RULE_ID
645 from AMS_OBJECT_RULES_TL B
646 where B.OBJECT_RULE_ID = T.OBJECT_RULE_ID
647 and B.LANGUAGE = T.SOURCE_LANG)
648 where (
649 T.OBJECT_RULE_ID,
650 T.LANGUAGE
651 ) in (select
652 SUBT.OBJECT_RULE_ID,
653 SUBT.LANGUAGE
654 from AMS_OBJECT_RULES_TL SUBB, AMS_OBJECT_RULES_TL SUBT
655 where SUBB.OBJECT_RULE_ID = SUBT.OBJECT_RULE_ID
656 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
657 and (SUBB.OBJECT_RULE_ID <> SUBT.OBJECT_RULE_ID
658 ));
659
660 insert into AMS_OBJECT_RULES_TL (
661 OBJECT_RULE_ID,
662 LAST_UPDATE_DATE,
663 LAST_UPDATED_BY,
664 CREATION_DATE,
665 CREATED_BY,
666 LAST_UPDATE_LOGIN,
667 SECURITY_GROUP_ID,
668 OBJECT_RULE_NAME,
669 DESCRIPTION,
670 LANGUAGE,
671 SOURCE_LANG
672 ) select
673 B.OBJECT_RULE_ID,
674 B.LAST_UPDATE_DATE,
675 B.LAST_UPDATED_BY,
676 B.CREATION_DATE,
677 B.CREATED_BY,
678 B.LAST_UPDATE_LOGIN,
679 B.SECURITY_GROUP_ID,
680 B.OBJECT_RULE_NAME,
681 B.DESCRIPTION,
682 L.LANGUAGE_CODE,
683 B.SOURCE_LANG
684 from AMS_OBJECT_RULES_TL B, FND_LANGUAGES L
685 where L.INSTALLED_FLAG in ('I', 'B')
686 and B.LANGUAGE = userenv('LANG')
687 and not exists
688 (select NULL
689 from AMS_OBJECT_RULES_TL T
690 where T.OBJECT_RULE_ID = B.OBJECT_RULE_ID
691 and T.LANGUAGE = L.LANGUAGE_CODE);
692 end ADD_LANGUAGE;
693
694 end AMS_OBJECT_RULES_PKG;