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_RULE_SETS_PKG

Source


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