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.EGO_USER_RULES_PKG

Source


1 package body EGO_USER_RULES_PKG as
2 /* $Header: EGOVUSRB.pls 120.1 2007/07/31 10:55:23 rgadiyar noship $ */
3 
4 procedure INSERT_ROW (
5   X_ROWID in out nocopy VARCHAR2,
6   X_RULE_ID in NUMBER,
7   X_RULESET_ID in NUMBER,
8   X_SEQUENCE in NUMBER,
9   X_RULE_NAME in VARCHAR2,
10   X_ATTR_GROUP_TYPE in VARCHAR2,
11   X_ATTR_GROUP_NAME in VARCHAR2,
12   X_ATTR_NAME in VARCHAR2,
13   X_SEVERITY in VARCHAR2,
14   X_RULE_XML in CLOB,
15   X_DESCRIPTION in VARCHAR2,
16   X_USER_EXPLANATION_MESSAGE in VARCHAR2,
17   X_CREATION_DATE in DATE,
18   X_CREATED_BY in NUMBER,
19   X_LAST_UPDATE_DATE in DATE,
20   X_LAST_UPDATED_BY in NUMBER,
21   X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23   cursor C is select ROWID from EGO_USER_RULES_B
24     where RULE_ID = X_RULE_ID
25     ;
26 begin
27   insert into EGO_USER_RULES_B (
28     RULE_ID,
29     RULESET_ID,
30     SEQUENCE,
31     RULE_NAME,
32     ATTR_GROUP_TYPE,
33     ATTR_GROUP_NAME,
34     ATTR_NAME,
35     SEVERITY,
36     RULE_XML,
37     CREATION_DATE,
38     CREATED_BY,
39     LAST_UPDATE_DATE,
40     LAST_UPDATED_BY,
41     LAST_UPDATE_LOGIN
42   ) values (
43     X_RULE_ID,
44     X_RULESET_ID,
45     X_SEQUENCE,
46     X_RULE_NAME,
47     X_ATTR_GROUP_TYPE,
48     X_ATTR_GROUP_NAME,
49     X_ATTR_NAME,
50     X_SEVERITY,
51     X_RULE_XML,
52     X_CREATION_DATE,
53     X_CREATED_BY,
54     X_LAST_UPDATE_DATE,
55     X_LAST_UPDATED_BY,
56     X_LAST_UPDATE_LOGIN
57   );
58 
59   insert into EGO_USER_RULES_TL (
60     RULE_ID,
61     DESCRIPTION,
62     USER_EXPLANATION_MESSAGE,
63     LAST_UPDATE_DATE,
64     LAST_UPDATED_BY,
65     CREATION_DATE,
66     CREATED_BY,
67     LAST_UPDATE_LOGIN,
68     LANGUAGE,
69     SOURCE_LANG
70   ) select
71     X_RULE_ID,
72     X_DESCRIPTION,
73     X_USER_EXPLANATION_MESSAGE,
74     X_LAST_UPDATE_DATE,
75     X_LAST_UPDATED_BY,
76     X_CREATION_DATE,
77     X_CREATED_BY,
78     X_LAST_UPDATE_LOGIN,
79     L.LANGUAGE_CODE,
80     userenv('LANG')
81   from FND_LANGUAGES L
82   where L.INSTALLED_FLAG in ('I', 'B')
83   and not exists
84     (select NULL
85     from EGO_USER_RULES_TL T
86     where T.RULE_ID = X_RULE_ID
87     and T.LANGUAGE = L.LANGUAGE_CODE);
88 
89   open c;
90   fetch c into X_ROWID;
91   if (c%notfound) then
92     close c;
93     raise no_data_found;
94   end if;
95   close c;
96 
97 end INSERT_ROW;
98 
99 procedure LOCK_ROW (
100   X_RULE_ID in NUMBER,
101   X_RULESET_ID in NUMBER,
102   X_SEQUENCE in NUMBER,
103   X_RULE_NAME in VARCHAR2,
104   X_ATTR_GROUP_TYPE in VARCHAR2,
105   X_ATTR_GROUP_NAME in VARCHAR2,
106   X_ATTR_NAME in VARCHAR2,
107   X_SEVERITY in VARCHAR2,
108   X_RULE_XML in CLOB,
109   X_DESCRIPTION in VARCHAR2,
110   X_USER_EXPLANATION_MESSAGE in VARCHAR2
111 ) is
112   cursor c is select
113       RULESET_ID,
114       SEQUENCE,
115       RULE_NAME,
116       ATTR_GROUP_TYPE,
117       ATTR_GROUP_NAME,
118       ATTR_NAME,
119       SEVERITY,
120       RULE_XML
121     from EGO_USER_RULES_B
122     where RULE_ID = X_RULE_ID
123     for update of RULE_ID nowait;
124   recinfo c%rowtype;
125 
126   cursor c1 is select
127       DESCRIPTION,
128       USER_EXPLANATION_MESSAGE,
129       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
130     from EGO_USER_RULES_TL
131     where RULE_ID = X_RULE_ID
132     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
133     for update of RULE_ID nowait;
134 begin
135   open c;
136   fetch c into recinfo;
137   if (c%notfound) then
138     close c;
139     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
140     app_exception.raise_exception;
141   end if;
142   close c;
143   if (    (recinfo.RULESET_ID = X_RULESET_ID)
144       AND (recinfo.SEQUENCE = X_SEQUENCE)
145       AND (recinfo.RULE_NAME = X_RULE_NAME)
146       AND ((recinfo.ATTR_GROUP_TYPE = X_ATTR_GROUP_TYPE)
147            OR ((recinfo.ATTR_GROUP_TYPE is null) AND (X_ATTR_GROUP_TYPE is null)))
148       AND ((recinfo.ATTR_GROUP_NAME = X_ATTR_GROUP_NAME)
149            OR ((recinfo.ATTR_GROUP_NAME is null) AND (X_ATTR_GROUP_NAME is null)))
150       AND ((recinfo.ATTR_NAME = X_ATTR_NAME)
151            OR ((recinfo.ATTR_NAME is null) AND (X_ATTR_NAME is null)))
152       AND ((recinfo.SEVERITY = X_SEVERITY)
153            OR ((recinfo.SEVERITY is null) AND (X_SEVERITY is null)))
154       AND (recinfo.RULE_XML = X_RULE_XML)
155   ) then
156     null;
157   else
158     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
159     app_exception.raise_exception;
160   end if;
161 
162   for tlinfo in c1 loop
163     if (tlinfo.BASELANG = 'Y') then
164       if (    ((tlinfo.DESCRIPTION = X_DESCRIPTION)
165                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
166           AND ((tlinfo.USER_EXPLANATION_MESSAGE = X_USER_EXPLANATION_MESSAGE)
167                OR ((tlinfo.USER_EXPLANATION_MESSAGE is null) AND (X_USER_EXPLANATION_MESSAGE is null)))
168       ) then
169         null;
170       else
171         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
172         app_exception.raise_exception;
173       end if;
174     end if;
175   end loop;
176   return;
177 end LOCK_ROW;
178 
179 procedure UPDATE_ROW (
180   X_RULE_ID in NUMBER,
181   X_RULESET_ID in NUMBER,
182   X_SEQUENCE in NUMBER,
183   X_RULE_NAME in VARCHAR2,
184   X_ATTR_GROUP_TYPE in VARCHAR2,
185   X_ATTR_GROUP_NAME in VARCHAR2,
186   X_ATTR_NAME in VARCHAR2,
187   X_SEVERITY in VARCHAR2,
188   X_RULE_XML in CLOB,
189   X_DESCRIPTION in VARCHAR2,
190   X_USER_EXPLANATION_MESSAGE in VARCHAR2,
191   X_LAST_UPDATE_DATE in DATE,
192   X_LAST_UPDATED_BY in NUMBER,
193   X_LAST_UPDATE_LOGIN in NUMBER
194 ) is
195 begin
196   update EGO_USER_RULES_B set
197     RULESET_ID = X_RULESET_ID,
198     SEQUENCE = X_SEQUENCE,
199     RULE_NAME = X_RULE_NAME,
200     ATTR_GROUP_TYPE = X_ATTR_GROUP_TYPE,
201     ATTR_GROUP_NAME = X_ATTR_GROUP_NAME,
202     ATTR_NAME = X_ATTR_NAME,
203     SEVERITY = X_SEVERITY,
204     RULE_XML = X_RULE_XML,
205     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
206     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
207     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
208   where RULE_ID = X_RULE_ID;
209 
210   if (sql%notfound) then
211     raise no_data_found;
212   end if;
213 
214   update EGO_USER_RULES_TL set
215     DESCRIPTION = X_DESCRIPTION,
216     USER_EXPLANATION_MESSAGE = X_USER_EXPLANATION_MESSAGE,
217     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
218     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
219     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
220     SOURCE_LANG = userenv('LANG')
221   where RULE_ID = X_RULE_ID
222   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
223 
224   if (sql%notfound) then
225     raise no_data_found;
226   end if;
227 end UPDATE_ROW;
228 
229 procedure DELETE_ROW (
230   X_RULE_ID in NUMBER
231 ) is
232 begin
233   delete from EGO_USER_RULES_TL
234   where RULE_ID = X_RULE_ID;
235 
236   if (sql%notfound) then
237     raise no_data_found;
238   end if;
239 
240   delete from EGO_USER_RULES_B
241   where RULE_ID = X_RULE_ID;
242 
243   if (sql%notfound) then
244     raise no_data_found;
245   end if;
246 end DELETE_ROW;
247 
248 procedure ADD_LANGUAGE
249 is
250 begin
251   delete from EGO_USER_RULES_TL T
252   where not exists
253     (select NULL
254     from EGO_USER_RULES_B B
255     where B.RULE_ID = T.RULE_ID
256     );
257 
258   update EGO_USER_RULES_TL T set (
259       DESCRIPTION,
260       USER_EXPLANATION_MESSAGE
261     ) = (select
262       B.DESCRIPTION,
263       B.USER_EXPLANATION_MESSAGE
264     from EGO_USER_RULES_TL B
265     where B.RULE_ID = T.RULE_ID
266     and B.LANGUAGE = T.SOURCE_LANG)
267   where (
268       T.RULE_ID,
269       T.LANGUAGE
270   ) in (select
271       SUBT.RULE_ID,
272       SUBT.LANGUAGE
273     from EGO_USER_RULES_TL SUBB, EGO_USER_RULES_TL SUBT
274     where SUBB.RULE_ID = SUBT.RULE_ID
275     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
276     and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
277       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
278       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
279       or SUBB.USER_EXPLANATION_MESSAGE <> SUBT.USER_EXPLANATION_MESSAGE
280       or (SUBB.USER_EXPLANATION_MESSAGE is null and SUBT.USER_EXPLANATION_MESSAGE is not null)
281       or (SUBB.USER_EXPLANATION_MESSAGE is not null and SUBT.USER_EXPLANATION_MESSAGE is null)
282   ));
283 
284   insert into EGO_USER_RULES_TL (
285     RULE_ID,
286     DESCRIPTION,
287     USER_EXPLANATION_MESSAGE,
288     LAST_UPDATE_DATE,
289     LAST_UPDATED_BY,
290     CREATION_DATE,
291     CREATED_BY,
292     LAST_UPDATE_LOGIN,
293     LANGUAGE,
294     SOURCE_LANG
295   ) select /*+ ORDERED */
296     B.RULE_ID,
297     B.DESCRIPTION,
298     B.USER_EXPLANATION_MESSAGE,
299     B.LAST_UPDATE_DATE,
300     B.LAST_UPDATED_BY,
301     B.CREATION_DATE,
302     B.CREATED_BY,
303     B.LAST_UPDATE_LOGIN,
304     L.LANGUAGE_CODE,
305     B.SOURCE_LANG
306   from EGO_USER_RULES_TL B, FND_LANGUAGES L
307   where L.INSTALLED_FLAG in ('I', 'B')
308   and B.LANGUAGE = userenv('LANG')
309   and not exists
310     (select NULL
311     from EGO_USER_RULES_TL T
312     where T.RULE_ID = B.RULE_ID
313     and T.LANGUAGE = L.LANGUAGE_CODE);
314 end ADD_LANGUAGE;
315 
316 procedure TRANSLATE_ROW (
317   X_RULE_ID in NUMBER,
318   X_USER_EXPLANATION_MESSAGE in VARCHAR2,
319   X_DESCRIPTION in VARCHAR2,
320   X_OWNER in VARCHAR2
321  ) is
322 begin
323  update EGO_USER_RULES_TL set
324    USER_EXPLANATION_MESSAGE = X_USER_EXPLANATION_MESSAGE,
325    DESCRIPTION = X_DESCRIPTION,
326    LAST_UPDATE_DATE = sysdate,
327    LAST_UPDATED_BY = decode(x_owner, 'ORACLE', 1, 0),
328    LAST_UPDATE_LOGIN = 0,
329    SOURCE_LANG = userenv('LANG')
330  where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
331  and RULE_ID = X_RULE_ID;
332 end TRANSLATE_ROW;
333 
334 procedure LOAD_ROW (
335   X_ROWID in out nocopy VARCHAR2,
336   X_RULE_ID in NUMBER,
337   X_RULESET_ID in NUMBER,
338   X_SEQUENCE in NUMBER,
339   X_RULE_NAME in VARCHAR2,
340   X_ATTR_GROUP_TYPE in VARCHAR2,
341   X_ATTR_GROUP_NAME in VARCHAR2,
342   X_ATTR_NAME in VARCHAR2,
343   X_SEVERITY in VARCHAR2,
344   X_RULE_XML in CLOB,
345   X_DESCRIPTION in VARCHAR2,
346   X_USER_EXPLANATION_MESSAGE in VARCHAR2,
347   X_CREATED_BY in NUMBER,
348   X_LAST_UPDATE_DATE in DATE,
349   X_LAST_UPDATED_BY in NUMBER,
350   X_LAST_UPDATE_LOGIN in NUMBER
351 ) is
352 begin
353    declare
354      l_rule_id number := 0;
355    begin
356 
357      EGO_USER_RULES_PKG.UPDATE_ROW(
358        X_RULE_ID		=>	X_RULE_ID,
359        X_RULESET_ID		=>	X_RULESET_ID,
360        X_SEQUENCE		=>	X_SEQUENCE,
361        X_RULE_NAME		=>	X_RULE_NAME,
362        X_ATTR_GROUP_TYPE	=>	X_ATTR_GROUP_TYPE,
363        X_ATTR_GROUP_NAME	=>	X_ATTR_GROUP_NAME,
364        X_ATTR_NAME		=>	X_ATTR_NAME,
365        X_SEVERITY		=>	X_SEVERITY,
366        X_RULE_XML		=>	X_RULE_XML,
367        X_DESCRIPTION		=>	X_DESCRIPTION,
368        X_USER_EXPLANATION_MESSAGE	=>	X_USER_EXPLANATION_MESSAGE,
369        X_LAST_UPDATE_DATE	=>	SYSDATE,
370        X_LAST_UPDATED_BY	=>	X_LAST_UPDATED_BY,
371        X_LAST_UPDATE_LOGIN	=>	X_LAST_UPDATE_LOGIN
372      );
373 
374    exception
375      when NO_DATA_FOUND then
376        select EGO_USER_RULES_S.nextval into l_rule_id from dual;
377 
378        EGO_USER_RULES_PKG.INSERT_ROW(
379          X_ROWID		=>	X_ROWID,
380 	 X_RULE_ID		=>	l_rule_id,
381 	 X_RULESET_ID		=>	X_RULESET_ID,
382 	 X_SEQUENCE		=>	X_SEQUENCE,
383 	 X_RULE_NAME		=>	X_RULE_NAME,
384 	 X_ATTR_GROUP_TYPE	=>	X_ATTR_GROUP_TYPE,
385 	 X_ATTR_GROUP_NAME	=>	X_ATTR_GROUP_NAME,
386 	 X_ATTR_NAME		=>	X_ATTR_NAME,
387 	 X_SEVERITY		=>	X_SEVERITY,
388 	 X_RULE_XML		=>	X_RULE_XML,
389 	 X_DESCRIPTION		=>	X_DESCRIPTION,
390 	 X_USER_EXPLANATION_MESSAGE	=>	X_USER_EXPLANATION_MESSAGE,
391 	 X_CREATION_DATE	=>	sysdate,
392 	 X_CREATED_BY		=>	X_CREATED_BY,
393 	 X_LAST_UPDATE_DATE	=>	sysdate,
394 	 X_LAST_UPDATED_BY	=>	X_LAST_UPDATED_BY,
395 	 X_LAST_UPDATE_LOGIN	=>	X_LAST_UPDATE_LOGIN
396        );
397    end;
398 
399 end LOAD_ROW;
400 
401 end EGO_USER_RULES_PKG;