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