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