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