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

Source


1 package body IEC_P_RES_GRP_PARAMS_PKG as
2 /* $Header: IECHRGPB.pls 115.11 2004/03/15 17:51:13 jezhu ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_RES_GROUP_PARAM_ID in NUMBER,
6   X_OBJECT_VERSION_NUMBER in NUMBER,
7   X_VDU_TYPE_ID in NUMBER,
8   X_PARAM_CODE in VARCHAR2,
9   X_LOOKUP_TYPE in VARCHAR2,
10   X_VALUE_TYPE in VARCHAR2,
11   X_VALUE_LENGTH in VARCHAR2,
12   X_REQUIRED in VARCHAR2,
13   X_DEFAULT_VALUE in VARCHAR2,
14   X_PARAM_NAME in VARCHAR2,
15   X_PARAM_DESCRIPTION in VARCHAR2,
16   X_CREATION_DATE in DATE,
17   X_CREATED_BY in NUMBER,
18   X_LAST_UPDATE_DATE in DATE,
19   X_LAST_UPDATED_BY in NUMBER,
20   X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22   cursor C is select ROWID from IEC_P_RES_GRP_PARAMS_B
23     where RES_GROUP_PARAM_ID = X_RES_GROUP_PARAM_ID
24     ;
25 begin
26   insert into IEC_P_RES_GRP_PARAMS_B (
27     RES_GROUP_PARAM_ID,
28     OBJECT_VERSION_NUMBER,
29     VDU_TYPE_ID,
30     PARAM_CODE,
31     LOOKUP_TYPE,
32     VALUE_TYPE,
33     VALUE_LENGTH,
34     REQUIRED,
35     DEFAULT_VALUE,
36     CREATION_DATE,
37     CREATED_BY,
38     LAST_UPDATE_DATE,
39     LAST_UPDATED_BY,
40     LAST_UPDATE_LOGIN
41   ) values (
42     X_RES_GROUP_PARAM_ID,
43     X_OBJECT_VERSION_NUMBER,
44     X_VDU_TYPE_ID,
45     X_PARAM_CODE,
46     X_LOOKUP_TYPE,
47     X_VALUE_TYPE,
48     X_VALUE_LENGTH,
49     X_REQUIRED,
50     X_DEFAULT_VALUE,
51     X_CREATION_DATE,
52     X_CREATED_BY,
53     X_LAST_UPDATE_DATE,
54     X_LAST_UPDATED_BY,
55     X_LAST_UPDATE_LOGIN
56   );
57 
58   insert into IEC_P_RES_GRP_PARAMS_TL (
59     RES_GROUP_PARAM_ID,
60     CREATED_BY,
61     CREATION_DATE,
62     LAST_UPDATED_BY,
63     LAST_UPDATE_DATE,
64     LAST_UPDATE_LOGIN,
65     PARAM_NAME,
66     PARAM_DESCRIPTION,
67     LANGUAGE,
68     SOURCE_LANG
69   ) select
70     X_RES_GROUP_PARAM_ID,
71     X_CREATED_BY,
72     X_CREATION_DATE,
73     X_LAST_UPDATED_BY,
74     X_LAST_UPDATE_DATE,
75     X_LAST_UPDATE_LOGIN,
76     X_PARAM_NAME,
77     X_PARAM_DESCRIPTION,
78     L.LANGUAGE_CODE,
79     userenv('LANG')
80   from FND_LANGUAGES L
81   where L.INSTALLED_FLAG in ('I', 'B')
82   and not exists
83     (select NULL
84     from IEC_P_RES_GRP_PARAMS_TL T
85     where T.RES_GROUP_PARAM_ID = X_RES_GROUP_PARAM_ID
86     and T.LANGUAGE = L.LANGUAGE_CODE);
87 
88   open c;
89   fetch c into X_ROWID;
90   if (c%notfound) then
91     close c;
92     raise no_data_found;
93   end if;
94   close c;
95 
96 end INSERT_ROW;
97 
98 procedure LOCK_ROW (
99   X_RES_GROUP_PARAM_ID in NUMBER,
100   X_OBJECT_VERSION_NUMBER in NUMBER,
101   X_VDU_TYPE_ID in NUMBER,
102   X_PARAM_CODE in VARCHAR2,
103   X_LOOKUP_TYPE in VARCHAR2,
104   X_VALUE_TYPE in VARCHAR2,
105   X_VALUE_LENGTH in VARCHAR2,
106   X_REQUIRED in VARCHAR2,
107   X_DEFAULT_VALUE in VARCHAR2,
108   X_PARAM_NAME in VARCHAR2,
109   X_PARAM_DESCRIPTION in VARCHAR2
110 ) is
111   cursor c is select
112       OBJECT_VERSION_NUMBER,
113       VDU_TYPE_ID,
114       PARAM_CODE,
115       LOOKUP_TYPE,
116       VALUE_TYPE,
117       VALUE_LENGTH,
118       REQUIRED,
119       DEFAULT_VALUE
120     from IEC_P_RES_GRP_PARAMS_B
121     where RES_GROUP_PARAM_ID = X_RES_GROUP_PARAM_ID
122     for update of RES_GROUP_PARAM_ID nowait;
123   recinfo c%rowtype;
124 
125   cursor c1 is select
126       PARAM_NAME,
127       PARAM_DESCRIPTION,
128       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
129     from IEC_P_RES_GRP_PARAMS_TL
130     where RES_GROUP_PARAM_ID = X_RES_GROUP_PARAM_ID
131     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
132     for update of RES_GROUP_PARAM_ID nowait;
133 begin
134   open c;
135   fetch c into recinfo;
136   if (c%notfound) then
137     close c;
138     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
139     app_exception.raise_exception;
140   end if;
141   close c;
142   if (    (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
143       AND (recinfo.VDU_TYPE_ID = X_VDU_TYPE_ID)
144       AND (recinfo.PARAM_CODE = X_PARAM_CODE)
145       AND ((recinfo.LOOKUP_TYPE = X_LOOKUP_TYPE)
146            OR ((recinfo.LOOKUP_TYPE is null) AND (X_LOOKUP_TYPE is null)))
147       AND ((recinfo.VALUE_TYPE = X_VALUE_TYPE)
148            OR ((recinfo.VALUE_TYPE is null) AND (X_VALUE_TYPE is null)))
149       AND ((recinfo.VALUE_LENGTH = X_VALUE_LENGTH)
150            OR ((recinfo.VALUE_LENGTH is null) AND (X_VALUE_LENGTH is null)))
151       AND ((recinfo.REQUIRED = X_REQUIRED)
152            OR ((recinfo.REQUIRED is null) AND (X_REQUIRED is null)))
153      AND ((recinfo.DEFAULT_VALUE = X_DEFAULT_VALUE)
154            OR ((recinfo.DEFAULT_VALUE is null) AND (X_DEFAULT_VALUE is null)))
155 
156   ) then
157     null;
158   else
159     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
160     app_exception.raise_exception;
161   end if;
162 
163   for tlinfo in c1 loop
164     if (tlinfo.BASELANG = 'Y') then
165       if (    (tlinfo.PARAM_NAME = X_PARAM_NAME)
166           AND ((tlinfo.PARAM_DESCRIPTION = X_PARAM_DESCRIPTION)
167                OR ((tlinfo.PARAM_DESCRIPTION is null) AND (X_PARAM_DESCRIPTION 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_RES_GROUP_PARAM_ID in NUMBER,
181   X_OBJECT_VERSION_NUMBER in NUMBER,
182   X_VDU_TYPE_ID in NUMBER,
183   X_PARAM_CODE in VARCHAR2,
184   X_LOOKUP_TYPE in VARCHAR2,
185   X_VALUE_TYPE in VARCHAR2,
186   X_VALUE_LENGTH in VARCHAR2,
187   X_REQUIRED in VARCHAR2,
188   X_DEFAULT_VALUE in VARCHAR2,
189   X_PARAM_NAME in VARCHAR2,
190   X_PARAM_DESCRIPTION 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 IEC_P_RES_GRP_PARAMS_B set
197     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
198     VDU_TYPE_ID = X_VDU_TYPE_ID,
199     PARAM_CODE = X_PARAM_CODE,
200     LOOKUP_TYPE = X_LOOKUP_TYPE,
201     VALUE_TYPE = X_VALUE_TYPE,
202     VALUE_LENGTH = X_VALUE_LENGTH,
203     REQUIRED = X_REQUIRED,
204     DEFAULT_VALUE = X_DEFAULT_VALUE,
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 RES_GROUP_PARAM_ID = X_RES_GROUP_PARAM_ID;
209 
210   if (sql%notfound) then
211     raise no_data_found;
212   end if;
213 
214   update IEC_P_RES_GRP_PARAMS_TL set
215     PARAM_NAME = X_PARAM_NAME,
216     PARAM_DESCRIPTION = X_PARAM_DESCRIPTION,
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 RES_GROUP_PARAM_ID = X_RES_GROUP_PARAM_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_RES_GROUP_PARAM_ID in NUMBER
231 ) is
232 begin
233   delete from IEC_P_RES_GRP_PARAMS_TL
234   where RES_GROUP_PARAM_ID = X_RES_GROUP_PARAM_ID;
235 
236   if (sql%notfound) then
237     raise no_data_found;
238   end if;
239 
240   delete from IEC_P_RES_GRP_PARAMS_B
241   where RES_GROUP_PARAM_ID = X_RES_GROUP_PARAM_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 IEC_P_RES_GRP_PARAMS_TL T
252   where not exists
253     (select NULL
254     from IEC_P_RES_GRP_PARAMS_B B
255     where B.RES_GROUP_PARAM_ID = T.RES_GROUP_PARAM_ID
256     );
257 
258   update IEC_P_RES_GRP_PARAMS_TL T set (
259       PARAM_NAME,
260       PARAM_DESCRIPTION
261     ) = (select
262       B.PARAM_NAME,
263       B.PARAM_DESCRIPTION
264     from IEC_P_RES_GRP_PARAMS_TL B
265     where B.RES_GROUP_PARAM_ID = T.RES_GROUP_PARAM_ID
266     and B.LANGUAGE = T.SOURCE_LANG)
267   where (
268       T.RES_GROUP_PARAM_ID,
269       T.LANGUAGE
270   ) in (select
271       SUBT.RES_GROUP_PARAM_ID,
272       SUBT.LANGUAGE
273     from IEC_P_RES_GRP_PARAMS_TL SUBB, IEC_P_RES_GRP_PARAMS_TL SUBT
274     where SUBB.RES_GROUP_PARAM_ID = SUBT.RES_GROUP_PARAM_ID
275     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
276     and (SUBB.PARAM_NAME <> SUBT.PARAM_NAME
277       or SUBB.PARAM_DESCRIPTION <> SUBT.PARAM_DESCRIPTION
278       or (SUBB.PARAM_DESCRIPTION is null and SUBT.PARAM_DESCRIPTION is not null)
279       or (SUBB.PARAM_DESCRIPTION is not null and SUBT.PARAM_DESCRIPTION is null)
280   ));
281 
282   insert into IEC_P_RES_GRP_PARAMS_TL (
283     RES_GROUP_PARAM_ID,
284     CREATED_BY,
285     CREATION_DATE,
286     LAST_UPDATED_BY,
287     LAST_UPDATE_DATE,
288     LAST_UPDATE_LOGIN,
289     PARAM_NAME,
290     PARAM_DESCRIPTION,
291     LANGUAGE,
292     SOURCE_LANG
293   ) select /*+ ORDERED */
294     B.RES_GROUP_PARAM_ID,
295     B.CREATED_BY,
296     B.CREATION_DATE,
297     B.LAST_UPDATED_BY,
298     B.LAST_UPDATE_DATE,
299     B.LAST_UPDATE_LOGIN,
300     B.PARAM_NAME,
301     B.PARAM_DESCRIPTION,
302     L.LANGUAGE_CODE,
303     B.SOURCE_LANG
304   from IEC_P_RES_GRP_PARAMS_TL B, FND_LANGUAGES L
305   where L.INSTALLED_FLAG in ('I', 'B')
306   and B.LANGUAGE = userenv('LANG')
307   and not exists
308     (select NULL
309     from IEC_P_RES_GRP_PARAMS_TL T
310     where T.RES_GROUP_PARAM_ID = B.RES_GROUP_PARAM_ID
311     and T.LANGUAGE = L.LANGUAGE_CODE);
312 end ADD_LANGUAGE;
313 
314 procedure LOAD_ROW (
315   X_RES_GROUP_PARAM_ID in NUMBER,
316   X_VDU_TYPE_ID in NUMBER,
317   X_PARAM_CODE in VARCHAR2,
318   X_LOOKUP_TYPE in VARCHAR2,
319   X_VALUE_TYPE in VARCHAR2,
320   X_VALUE_LENGTH in VARCHAR2,
321   X_REQUIRED in VARCHAR2,
322   X_DEFAULT_VALUE in VARCHAR2,
323   X_PARAM_NAME in VARCHAR2,
324   X_PARAM_DESCRIPTION in VARCHAR2,
325   X_OWNER in VARCHAR2
326 ) is
327 
328   USER_ID NUMBER := 0;
329   ROW_ID  VARCHAR2(500);
330 begin
331 
332   if (X_OWNER = 'SEED') then
333     USER_ID := 1;
334   end if;
335 
336   UPDATE_ROW ( X_RES_GROUP_PARAM_ID
337              , 0
338              , X_VDU_TYPE_ID
339              , X_PARAM_CODE
340              , X_LOOKUP_TYPE
341              , X_VALUE_TYPE
342              , X_VALUE_LENGTH
343              , X_REQUIRED
344              , X_DEFAULT_VALUE
345              , X_PARAM_NAME
346              , X_PARAM_DESCRIPTION
347              , SYSDATE
348              , USER_ID
349              , 0);
350 
351 exception
352   when no_data_found then
353     INSERT_ROW ( ROW_ID
354                , X_RES_GROUP_PARAM_ID
355                , 0
356                , X_VDU_TYPE_ID
357                , X_PARAM_CODE
358                , X_LOOKUP_TYPE
359                , X_VALUE_TYPE
360                , X_VALUE_LENGTH
361                , X_REQUIRED
362                , X_DEFAULT_VALUE
363                , X_PARAM_NAME
364                , X_PARAM_DESCRIPTION
365                , SYSDATE
366                , USER_ID
367                , SYSDATE
368                , USER_ID
369                , 0);
370 
371 end LOAD_ROW;
372 
373 
374 procedure TRANSLATE_ROW (
375   X_RES_GROUP_PARAM_ID in NUMBER,
376   X_PARAM_NAME in VARCHAR2,
377   X_PARAM_DESCRIPTION in VARCHAR2,
378   X_OWNER in VARCHAR2
379 ) is
380 begin
381 
382   -- only UPDATE rows that have not been altered by user
383 
384   update IEC_P_RES_GRP_PARAMS_TL set
385   PARAM_NAME = X_PARAM_NAME,
386   SOURCE_LANG = userenv('LANG'),
387   PARAM_DESCRIPTION = X_PARAM_DESCRIPTION,
388   LAST_UPDATE_DATE = SYSDATE,
389   LAST_UPDATED_BY = DECODE(X_OWNER, 'SEED', 1, 0),
390   LAST_UPDATE_LOGIN = 0
391   where RES_GROUP_PARAM_ID = X_RES_GROUP_PARAM_ID
392   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
393 
394 end TRANSLATE_ROW;
395 
396 end IEC_P_RES_GRP_PARAMS_PKG;