eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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,
475   X_PACKAGE_NAME in VARCHAR2,
472   X_RULE_USED_BY_TYPE in VARCHAR2,
473   X_RULE_TYPE in VARCHAR2,
474   X_API_TYPE 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
637     from AMS_OBJECT_RULES_B B
634   delete from AMS_OBJECT_RULES_TL T
635   where not exists
636     (select NULL
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;