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

Source


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