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