[Home] [Help]
PACKAGE BODY: APPS.FEM_TABLES_PKG
Source
1 package body FEM_TABLES_PKG as
2 /* $Header: fem_tables_pkb.plb 120.5 2007/05/15 22:58:17 rflippo ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_TABLE_NAME in VARCHAR2,
6 X_PROC_KEY_INDEX_OWNER in VARCHAR2,
7 X_DI_VIEW_NAME in VARCHAR2,
8 X_REGISTRATION_STATUS_CODE in VARCHAR2,
9 X_INTERFACE_TABLE_NAME in VARCHAR2,
10 X_PROC_KEY_INDEX_NAME in VARCHAR2,
11 X_TABLE_OWNER_APPLICATION_ID in NUMBER,
12 X_TABLE_ID in NUMBER,
13 X_ENABLED_FLAG in VARCHAR2,
14 X_OBJECT_VERSION_NUMBER in NUMBER,
15 X_DISPLAY_NAME in VARCHAR2,
16 X_DESCRIPTION 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 FEM_TABLES_B
24 where TABLE_NAME = X_TABLE_NAME
25 ;
26 begin
27 insert into FEM_TABLES_B (
28 PROC_KEY_INDEX_OWNER,
29 DI_VIEW_NAME,
30 REGISTRATION_STATUS_CODE,
31 INTERFACE_TABLE_NAME,
32 PROC_KEY_INDEX_NAME,
33 TABLE_NAME,
34 TABLE_OWNER_APPLICATION_ID,
35 TABLE_ID,
36 ENABLED_FLAG,
37 OBJECT_VERSION_NUMBER,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_LOGIN
43 ) values (
44 X_PROC_KEY_INDEX_OWNER,
45 X_DI_VIEW_NAME,
46 X_REGISTRATION_STATUS_CODE,
47 X_INTERFACE_TABLE_NAME,
48 X_PROC_KEY_INDEX_NAME,
49 X_TABLE_NAME,
50 X_TABLE_OWNER_APPLICATION_ID,
51 X_TABLE_ID,
52 X_ENABLED_FLAG,
53 X_OBJECT_VERSION_NUMBER,
54 X_CREATION_DATE,
55 X_CREATED_BY,
56 X_LAST_UPDATE_DATE,
57 X_LAST_UPDATED_BY,
58 X_LAST_UPDATE_LOGIN
59 );
60
61 insert into FEM_TABLES_TL (
62 TABLE_NAME,
63 DISPLAY_NAME,
64 DESCRIPTION,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATED_BY,
68 LAST_UPDATE_DATE,
69 LAST_UPDATE_LOGIN,
70 LANGUAGE,
71 SOURCE_LANG
72 ) select
73 X_TABLE_NAME,
74 X_DISPLAY_NAME,
75 X_DESCRIPTION,
76 X_CREATION_DATE,
77 X_CREATED_BY,
78 X_LAST_UPDATED_BY,
79 X_LAST_UPDATE_DATE,
80 X_LAST_UPDATE_LOGIN,
81 L.LANGUAGE_CODE,
82 userenv('LANG')
83 from FND_LANGUAGES L
84 where L.INSTALLED_FLAG in ('I', 'B')
85 and not exists
86 (select NULL
87 from FEM_TABLES_TL T
88 where T.TABLE_NAME = X_TABLE_NAME
89 and T.LANGUAGE = L.LANGUAGE_CODE);
90
91 open c;
92 fetch c into X_ROWID;
93 if (c%notfound) then
94 close c;
95 raise no_data_found;
96 end if;
97 close c;
98
99 end INSERT_ROW;
100
101 procedure LOCK_ROW (
102 X_TABLE_NAME in VARCHAR2,
103 X_PROC_KEY_INDEX_OWNER in VARCHAR2,
104 X_DI_VIEW_NAME in VARCHAR2,
105 X_REGISTRATION_STATUS_CODE in VARCHAR2,
106 X_INTERFACE_TABLE_NAME in VARCHAR2,
107 X_PROC_KEY_INDEX_NAME in VARCHAR2,
108 X_TABLE_OWNER_APPLICATION_ID in NUMBER,
109 X_TABLE_ID in NUMBER,
110 X_ENABLED_FLAG in VARCHAR2,
111 X_OBJECT_VERSION_NUMBER in NUMBER,
112 X_DISPLAY_NAME in VARCHAR2,
113 X_DESCRIPTION in VARCHAR2
114 ) is
115 cursor c is select
116 PROC_KEY_INDEX_OWNER,
117 DI_VIEW_NAME,
118 REGISTRATION_STATUS_CODE,
119 INTERFACE_TABLE_NAME,
120 PROC_KEY_INDEX_NAME,
121 TABLE_OWNER_APPLICATION_ID,
122 TABLE_ID,
123 ENABLED_FLAG,
124 OBJECT_VERSION_NUMBER
125 from FEM_TABLES_B
126 where TABLE_NAME = X_TABLE_NAME
127 for update of TABLE_NAME nowait;
128 recinfo c%rowtype;
129
130 cursor c1 is select
131 DISPLAY_NAME,
132 DESCRIPTION,
133 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
134 from FEM_TABLES_TL
135 where TABLE_NAME = X_TABLE_NAME
136 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
137 for update of TABLE_NAME nowait;
138 begin
139 open c;
140 fetch c into recinfo;
141 if (c%notfound) then
142 close c;
143 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
144 app_exception.raise_exception;
145 end if;
146 close c;
147 if ( ((recinfo.PROC_KEY_INDEX_OWNER = X_PROC_KEY_INDEX_OWNER)
148 OR ((recinfo.PROC_KEY_INDEX_OWNER is null) AND (X_PROC_KEY_INDEX_OWNER is null)))
149 AND ((recinfo.DI_VIEW_NAME = X_DI_VIEW_NAME)
150 OR ((recinfo.DI_VIEW_NAME is null) AND (X_DI_VIEW_NAME is null)))
151 AND ((recinfo.REGISTRATION_STATUS_CODE = X_REGISTRATION_STATUS_CODE)
152 OR ((recinfo.REGISTRATION_STATUS_CODE is null) AND (X_REGISTRATION_STATUS_CODE is null)))
153 AND ((recinfo.INTERFACE_TABLE_NAME = X_INTERFACE_TABLE_NAME)
154 OR ((recinfo.INTERFACE_TABLE_NAME is null) AND (X_INTERFACE_TABLE_NAME is null)))
155 AND ((recinfo.PROC_KEY_INDEX_NAME = X_PROC_KEY_INDEX_NAME)
156 OR ((recinfo.PROC_KEY_INDEX_NAME is null) AND (X_PROC_KEY_INDEX_NAME is null)))
157 AND ((recinfo.TABLE_OWNER_APPLICATION_ID = X_TABLE_OWNER_APPLICATION_ID)
158 OR ((recinfo.TABLE_OWNER_APPLICATION_ID is null) AND (X_TABLE_OWNER_APPLICATION_ID is null)))
159 AND (recinfo.TABLE_ID = X_TABLE_ID)
160 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
161 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
162 ) then
163 null;
164 else
165 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
166 app_exception.raise_exception;
167 end if;
168
169 for tlinfo in c1 loop
170 if (tlinfo.BASELANG = 'Y') then
171 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
172 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
173 ) then
174 null;
175 else
176 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
177 app_exception.raise_exception;
178 end if;
179 end if;
180 end loop;
181 return;
182 end LOCK_ROW;
183
184 procedure UPDATE_ROW (
185 X_TABLE_NAME in VARCHAR2,
186 X_PROC_KEY_INDEX_OWNER in VARCHAR2,
187 X_DI_VIEW_NAME in VARCHAR2,
188 X_REGISTRATION_STATUS_CODE in VARCHAR2,
189 X_INTERFACE_TABLE_NAME in VARCHAR2,
190 X_PROC_KEY_INDEX_NAME in VARCHAR2,
191 X_TABLE_OWNER_APPLICATION_ID in NUMBER,
192 X_TABLE_ID in NUMBER,
193 X_ENABLED_FLAG in VARCHAR2,
194 X_OBJECT_VERSION_NUMBER in NUMBER,
195 X_DISPLAY_NAME in VARCHAR2,
196 X_DESCRIPTION in VARCHAR2,
197 X_LAST_UPDATE_DATE in DATE,
198 X_LAST_UPDATED_BY in NUMBER,
199 X_LAST_UPDATE_LOGIN in NUMBER
200 ) is
201 begin
202 update FEM_TABLES_B set
203 PROC_KEY_INDEX_OWNER = X_PROC_KEY_INDEX_OWNER,
204 DI_VIEW_NAME = X_DI_VIEW_NAME,
205 REGISTRATION_STATUS_CODE = X_REGISTRATION_STATUS_CODE,
206 INTERFACE_TABLE_NAME = X_INTERFACE_TABLE_NAME,
207 PROC_KEY_INDEX_NAME = X_PROC_KEY_INDEX_NAME,
208 TABLE_OWNER_APPLICATION_ID = X_TABLE_OWNER_APPLICATION_ID,
209 TABLE_ID = X_TABLE_ID,
210 ENABLED_FLAG = X_ENABLED_FLAG,
211 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
212 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
213 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
214 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
215 where TABLE_NAME = X_TABLE_NAME;
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220
221 update FEM_TABLES_TL set
222 DISPLAY_NAME = X_DISPLAY_NAME,
223 DESCRIPTION = X_DESCRIPTION,
224 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
225 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
226 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
227 SOURCE_LANG = userenv('LANG')
228 where TABLE_NAME = X_TABLE_NAME
229 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
230
231 if (sql%notfound) then
232 raise no_data_found;
233 end if;
234 end UPDATE_ROW;
235
236 procedure DELETE_ROW (
237 X_TABLE_NAME in VARCHAR2
238 ) is
239 begin
240 delete from FEM_TABLES_TL
241 where TABLE_NAME = X_TABLE_NAME;
242
243 if (sql%notfound) then
244 raise no_data_found;
245 end if;
246
247 delete from FEM_TABLES_B
248 where TABLE_NAME = X_TABLE_NAME;
249
250 if (sql%notfound) then
251 raise no_data_found;
252 end if;
253 end DELETE_ROW;
254
255 procedure ADD_LANGUAGE
256 is
257 begin
258 delete from FEM_TABLES_TL T
259 where not exists
260 (select NULL
261 from FEM_TABLES_B B
262 where B.TABLE_NAME = T.TABLE_NAME
263 );
264
265 update FEM_TABLES_TL T set (
266 DISPLAY_NAME,
267 DESCRIPTION
268 ) = (select
269 B.DISPLAY_NAME,
270 B.DESCRIPTION
271 from FEM_TABLES_TL B
272 where B.TABLE_NAME = T.TABLE_NAME
273 and B.LANGUAGE = T.SOURCE_LANG)
274 where (
275 T.TABLE_NAME,
276 T.LANGUAGE
277 ) in (select
278 SUBT.TABLE_NAME,
279 SUBT.LANGUAGE
280 from FEM_TABLES_TL SUBB, FEM_TABLES_TL SUBT
281 where SUBB.TABLE_NAME = SUBT.TABLE_NAME
282 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
283 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
284 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
285 ));
286
287 insert into FEM_TABLES_TL (
288 TABLE_NAME,
289 DISPLAY_NAME,
290 DESCRIPTION,
291 CREATION_DATE,
292 CREATED_BY,
293 LAST_UPDATED_BY,
294 LAST_UPDATE_DATE,
295 LAST_UPDATE_LOGIN,
296 LANGUAGE,
297 SOURCE_LANG
298 ) select /*+ ORDERED */
299 B.TABLE_NAME,
300 B.DISPLAY_NAME,
301 B.DESCRIPTION,
302 B.CREATION_DATE,
303 B.CREATED_BY,
304 B.LAST_UPDATED_BY,
305 B.LAST_UPDATE_DATE,
306 B.LAST_UPDATE_LOGIN,
307 L.LANGUAGE_CODE,
308 B.SOURCE_LANG
309 from FEM_TABLES_TL B, FND_LANGUAGES L
310 where L.INSTALLED_FLAG in ('I', 'B')
311 and B.LANGUAGE = userenv('LANG')
312 and not exists
313 (select NULL
314 from FEM_TABLES_TL T
315 where T.TABLE_NAME = B.TABLE_NAME
316 and T.LANGUAGE = L.LANGUAGE_CODE);
317 end ADD_LANGUAGE;
318 PROCEDURE TRANSLATE_ROW(
319 x_TABLE_NAME in varchar2,
320 x_owner in varchar2,
321 x_last_update_date in varchar2,
322 x_DISPLAY_NAME in varchar2,
323 x_description in varchar2,
324 x_custom_mode in varchar2) is
325
326 owner_id number;
327 ludate date;
328 row_id varchar2(64);
329 f_luby number; -- entity owner in file
330 f_ludate date; -- entity update date in file
331 db_luby number; -- entity owner in db
332 db_ludate date; -- entity update date in db
333 begin
334
335
336 -- Translate owner to file_last_updated_by
337 f_luby := fnd_load_util.owner_id(x_owner);
338
339 -- Translate char last_update_date to date
340 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
341 begin
342 select LAST_UPDATED_BY, LAST_UPDATE_DATE
343 into db_luby, db_ludate
344 from FEM_TABLES_TL
345 where TABLE_NAME = x_TABLE_NAME
346 and LANGUAGE = userenv('LANG');
347
348 -- Test for customization and version
349 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
350 db_ludate, x_custom_mode)) then
351 -- Update translations for this language
352 update FEM_TABLES_TL set
353 DISPLAY_NAME = decode(x_DISPLAY_NAME,
354 fnd_load_util.null_value, null, -- Real null
355 null, x_DISPLAY_NAME, -- No change
356 x_DISPLAY_NAME),
357 DESCRIPTION = nvl(x_description, DESCRIPTION),
358 LAST_UPDATE_DATE = f_ludate,
359 LAST_UPDATED_BY = f_luby,
360 LAST_UPDATE_LOGIN = 0,
361 SOURCE_LANG = userenv('LANG')
362 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
363 and TABLE_NAME = x_TABLE_NAME;
364 end if;
365 exception
366 when no_data_found then
367 -- Do not insert missing translations, skip this row
368 null;
369 end;
370 end TRANSLATE_ROW;
371
372
373 end FEM_TABLES_PKG;