[Home] [Help]
PACKAGE BODY: APPS.FND_OBJECT_INSTANCE_SETS_PKG
Source
1 package body FND_OBJECT_INSTANCE_SETS_PKG as
2 /* $Header: AFSCOISB.pls 120.2 2005/10/27 18:19:53 tmorrow ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_INSTANCE_SET_ID in NUMBER,
7 X_INSTANCE_SET_NAME in VARCHAR2,
8 X_OBJECT_ID in NUMBER,
9 X_PREDICATE in VARCHAR2,
10 X_DISPLAY_NAME in VARCHAR2,
11 X_DESCRIPTION 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 FND_OBJECT_INSTANCE_SETS
19 where INSTANCE_SET_ID = X_INSTANCE_SET_ID
20 ;
21 begin
22 insert into FND_OBJECT_INSTANCE_SETS (
23 INSTANCE_SET_ID,
24 INSTANCE_SET_NAME,
25 OBJECT_ID,
26 PREDICATE,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN
32 ) values (
33 X_INSTANCE_SET_ID,
34 X_INSTANCE_SET_NAME,
35 X_OBJECT_ID,
36 X_PREDICATE,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN
42 );
43
44 insert into FND_OBJECT_INSTANCE_SETS_TL (
45 INSTANCE_SET_ID,
46 DISPLAY_NAME,
47 DESCRIPTION,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATE_LOGIN,
53 LANGUAGE,
54 SOURCE_LANG
55 ) select
56 X_INSTANCE_SET_ID,
57 X_DISPLAY_NAME,
58 X_DESCRIPTION,
59 X_CREATED_BY,
60 X_CREATION_DATE,
61 X_LAST_UPDATED_BY,
62 X_LAST_UPDATE_DATE,
63 X_LAST_UPDATE_LOGIN,
64 L.LANGUAGE_CODE,
65 userenv('LANG')
66 from FND_LANGUAGES L
67 where L.INSTALLED_FLAG in ('I', 'B')
68 and not exists
69 (select NULL
70 from FND_OBJECT_INSTANCE_SETS_TL T
71 where T.INSTANCE_SET_ID = X_INSTANCE_SET_ID
72 and T.LANGUAGE = L.LANGUAGE_CODE);
73
74 open c;
75 fetch c into X_ROWID;
76 if (c%notfound) then
77 close c;
78 raise no_data_found;
79 end if;
80 close c;
81
82 end INSERT_ROW;
83
84 procedure LOCK_ROW (
85 X_INSTANCE_SET_ID in NUMBER,
86 X_INSTANCE_SET_NAME in VARCHAR2,
87 X_OBJECT_ID in NUMBER,
88 X_PREDICATE in VARCHAR2,
89 X_DISPLAY_NAME in VARCHAR2,
90 X_DESCRIPTION in VARCHAR2
91 ) is
92 cursor c is select
93 INSTANCE_SET_NAME,
94 OBJECT_ID,
95 PREDICATE
96 from FND_OBJECT_INSTANCE_SETS
97 where INSTANCE_SET_ID = X_INSTANCE_SET_ID
98 for update of INSTANCE_SET_ID nowait;
99 recinfo c%rowtype;
100
101 cursor c1 is select
102 DISPLAY_NAME,
103 DESCRIPTION,
104 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
105 from FND_OBJECT_INSTANCE_SETS_TL
106 where INSTANCE_SET_ID = X_INSTANCE_SET_ID
107 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
108 for update of INSTANCE_SET_ID nowait;
109 begin
110 open c;
111 fetch c into recinfo;
112 if (c%notfound) then
113 close c;
114 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
115 app_exception.raise_exception;
116 end if;
117 close c;
118 if ( (recinfo.INSTANCE_SET_NAME = X_INSTANCE_SET_NAME)
119 AND (recinfo.OBJECT_ID = X_OBJECT_ID)
120 AND ((recinfo.PREDICATE = X_PREDICATE)
121 OR ((recinfo.PREDICATE is null) AND (X_PREDICATE is null)))
122 ) then
123 null;
124 else
125 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
126 app_exception.raise_exception;
127 end if;
128
129 for tlinfo in c1 loop
130 if (tlinfo.BASELANG = 'Y') then
131 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
132 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
133 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
134 ) then
135 null;
136 else
137 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
138 app_exception.raise_exception;
139 end if;
140 end if;
141 end loop;
142 return;
143 end LOCK_ROW;
144
145 procedure UPDATE_ROW (
146 X_INSTANCE_SET_ID in NUMBER,
147 X_INSTANCE_SET_NAME in VARCHAR2,
148 X_OBJECT_ID in NUMBER,
149 X_PREDICATE in VARCHAR2,
150 X_DISPLAY_NAME in VARCHAR2,
151 X_DESCRIPTION in VARCHAR2,
152 X_LAST_UPDATE_DATE in DATE,
153 X_LAST_UPDATED_BY in NUMBER,
154 X_LAST_UPDATE_LOGIN in NUMBER
155 ) is
156 begin
157 update FND_OBJECT_INSTANCE_SETS set
158 OBJECT_ID = X_OBJECT_ID,
159 PREDICATE = X_PREDICATE,
160 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
161 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
163 where INSTANCE_SET_ID = X_INSTANCE_SET_ID;
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168
169 update FND_OBJECT_INSTANCE_SETS_TL set
170 DISPLAY_NAME = X_DISPLAY_NAME,
171 DESCRIPTION = X_DESCRIPTION,
172 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
173 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
174 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
175 SOURCE_LANG = userenv('LANG')
176 where INSTANCE_SET_ID = X_INSTANCE_SET_ID
177 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182 end UPDATE_ROW;
183
184 /* Overloaded version below */
185 procedure LOAD_ROW (
186 X_INSTANCE_SET_NAME in VARCHAR2,
187 X_OWNER in VARCHAR2,
188 X_OBJECT_NAME in VARCHAR2,
189 X_PREDICATE in VARCHAR2,
190 X_DISPLAY_NAME in VARCHAR2,
191 X_DESCRIPTION in VARCHAR2,
192 X_CUSTOM_MODE in VARCHAR2
193 ) is
194 begin
195 fnd_object_instance_sets_pkg.LOAD_ROW (
196 X_INSTANCE_SET_NAME => X_INSTANCE_SET_NAME,
197 X_OWNER => X_OWNER,
198 X_OBJECT_NAME => X_OBJECT_NAME,
199 X_PREDICATE => X_PREDICATE,
200 X_DISPLAY_NAME => X_DISPLAY_NAME,
201 X_DESCRIPTION => X_DESCRIPTION,
202 X_CUSTOM_MODE => X_CUSTOM_MODE,
203 X_LAST_UPDATE_DATE => null
204 );
205 end LOAD_ROW;
206
207 /* Overloaded version above */
208 procedure LOAD_ROW (
209 X_INSTANCE_SET_NAME in VARCHAR2,
210 X_OWNER in VARCHAR2,
211 X_OBJECT_NAME in VARCHAR2,
212 X_PREDICATE in VARCHAR2,
213 X_DISPLAY_NAME in VARCHAR2,
214 X_DESCRIPTION in VARCHAR2,
215 X_CUSTOM_MODE in VARCHAR2,
216 X_LAST_UPDATE_DATE in VARCHAR2
217 ) is
218 ins_set_id number;
219 obj_id number;
220 row_id varchar2(64);
221 f_luby number; -- entity owner in file
222 f_ludate date; -- entity update date in file
223 db_luby number; -- entity owner in db
224 db_ludate date; -- entity update date in db
225 begin
226 -- Translate owner to file_last_updated_by
227 f_luby := fnd_load_util.owner_id(x_owner);
228
229 -- Translate char last_update_date to date
230 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
231
232 select OBJECT_ID into obj_id
233 from FND_OBJECTS where OBJ_NAME = X_OBJECT_NAME;
234
235 -- Be careful, invalid object might cause no_data_found.
236 -- Fix it later after discussion
237
238 select INSTANCE_SET_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
239 into ins_set_id, db_luby, db_ludate
240 from fnd_object_instance_sets
241 where INSTANCE_SET_NAME = X_INSTANCE_SET_NAME;
242
243 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
244 db_ludate, X_CUSTOM_MODE)) then
245 fnd_object_instance_sets_pkg.UPDATE_ROW (
246 X_INSTANCE_SET_ID => ins_set_id,
247 X_INSTANCE_SET_NAME => X_INSTANCE_SET_NAME,
248 X_OBJECT_ID => obj_id,
249 X_PREDICATE => X_PREDICATE,
250 X_DISPLAY_NAME => X_DISPLAY_NAME,
251 X_DESCRIPTION => X_DESCRIPTION,
252 X_LAST_UPDATE_DATE => f_ludate,
253 X_LAST_UPDATED_BY => f_luby,
254 X_LAST_UPDATE_LOGIN => 0 );
255 end if;
256
257 exception
258 when NO_DATA_FOUND then
259
260 select fnd_object_instance_sets_s.nextval into ins_set_id from dual;
261
262 fnd_object_instance_sets_pkg.INSERT_ROW(
263 X_ROWID => row_id,
264 X_INSTANCE_SET_ID => ins_set_id,
265 X_INSTANCE_SET_NAME => X_INSTANCE_SET_NAME,
266 X_OBJECT_ID => obj_id,
267 X_PREDICATE => X_PREDICATE,
268 X_DISPLAY_NAME => X_DISPLAY_NAME,
269 X_DESCRIPTION => X_DESCRIPTION,
270 X_CREATION_DATE => f_ludate,
271 X_CREATED_BY => f_luby,
272 X_LAST_UPDATE_DATE => f_ludate,
273 X_LAST_UPDATED_BY => f_luby,
274 X_LAST_UPDATE_LOGIN => 0 );
275
276 end LOAD_ROW;
277
278 /* Overloaded version below */
279 procedure TRANSLATE_ROW (
280 X_INSTANCE_SET_NAME in VARCHAR2,
281 X_OWNER in VARCHAR2,
282 X_DISPLAY_NAME in VARCHAR2,
283 X_DESCRIPTION in VARCHAR2,
284 X_CUSTOM_MODE in VARCHAR2
285 ) is
286 begin
287 fnd_object_instance_sets_pkg.TRANSLATE_ROW (
288 X_INSTANCE_SET_NAME => X_INSTANCE_SET_NAME,
289 X_OWNER => X_OWNER ,
290 X_DISPLAY_NAME => X_DISPLAY_NAME,
291 X_DESCRIPTION => X_DESCRIPTION,
292 X_CUSTOM_MODE => X_CUSTOM_MODE,
293 X_LAST_UPDATE_DATE => null
294 );
295 end TRANSLATE_ROW;
296
297 /* Overloaded version above */
298 procedure TRANSLATE_ROW (
299 X_INSTANCE_SET_NAME in VARCHAR2,
300 X_OWNER in VARCHAR2,
301 X_DISPLAY_NAME in VARCHAR2,
302 X_DESCRIPTION in VARCHAR2,
303 X_CUSTOM_MODE in VARCHAR2,
304 X_LAST_UPDATE_DATE in VARCHAR2
305 ) is
306 f_luby number; -- entity owner in file
307 f_ludate date; -- entity update date in file
308 db_luby number; -- entity owner in db
309 db_ludate date; -- entity update date in db
310 begin
311 -- Translate owner to file_last_updated_by
312 f_luby := fnd_load_util.owner_id(x_owner);
313
314 -- Translate char last_update_date to date
315 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
316
317 select LAST_UPDATED_BY, LAST_UPDATE_DATE
318 into db_luby, db_ludate
319 from fnd_object_instance_sets_tl
320 where instance_set_id = (select i.instance_set_id
321 from fnd_object_instance_sets i
322 where i.instance_set_name = X_INSTANCE_SET_NAME)
323 and userenv('LANG') = LANGUAGE;
324
325 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
326 db_ludate, X_CUSTOM_MODE)) then
327 update fnd_object_instance_sets_tl set
328 display_name = nvl(X_DISPLAY_NAME, display_name),
329 description = nvl(X_DESCRIPTION, description),
330 source_lang = userenv('LANG'),
331 last_update_date = f_ludate,
332 last_updated_by = f_luby,
333 last_update_login = 0
334 where instance_set_id = (select i.instance_set_id
335 from fnd_object_instance_sets i
336 where i.instance_set_name = X_INSTANCE_SET_NAME)
337 and userenv('LANG') in (language, source_lang);
338 end if;
339 end TRANSLATE_ROW;
340
341 procedure DELETE_ROW (
342 X_INSTANCE_SET_ID in NUMBER
343 ) is
344 begin
345 delete from FND_OBJECT_INSTANCE_SETS_TL
346 where INSTANCE_SET_ID = X_INSTANCE_SET_ID;
347
348 if (sql%notfound) then
349 raise no_data_found;
350 end if;
351
352 delete from FND_OBJECT_INSTANCE_SETS
353 where INSTANCE_SET_ID = X_INSTANCE_SET_ID;
354
355 if (sql%notfound) then
356 raise no_data_found;
357 end if;
358 end DELETE_ROW;
359
360 procedure ADD_LANGUAGE
361 is
362 begin
363 /* Mar/19/03 requested by Ric Ginsberg */
364 /* The following delete and update statements are commented out */
365 /* as a quick workaround to fix the time-consuming table handler issue */
366 /* Eventually we'll need to turn them into a separate fix_language procedure */
367 /*
368
369 delete from FND_OBJECT_INSTANCE_SETS_TL T
370 where not exists
371 (select NULL
372 from FND_OBJECT_INSTANCE_SETS B
373 where B.INSTANCE_SET_ID = T.INSTANCE_SET_ID
374 );
375
376 update FND_OBJECT_INSTANCE_SETS_TL T set (
377 DISPLAY_NAME,
378 DESCRIPTION
379 ) = (select
380 B.DISPLAY_NAME,
381 B.DESCRIPTION
382 from FND_OBJECT_INSTANCE_SETS_TL B
383 where B.INSTANCE_SET_ID = T.INSTANCE_SET_ID
384 and B.LANGUAGE = T.SOURCE_LANG)
385 where (
386 T.INSTANCE_SET_ID,
387 T.LANGUAGE
388 ) in (select
389 SUBT.INSTANCE_SET_ID,
390 SUBT.LANGUAGE
391 from FND_OBJECT_INSTANCE_SETS_TL SUBB, FND_OBJECT_INSTANCE_SETS_TL SUBT
392 where SUBB.INSTANCE_SET_ID = SUBT.INSTANCE_SET_ID
393 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
394 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
395 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
396 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
397 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
398 ));
399 */
400
401 insert into FND_OBJECT_INSTANCE_SETS_TL (
402 INSTANCE_SET_ID,
403 DISPLAY_NAME,
404 DESCRIPTION,
405 CREATED_BY,
406 CREATION_DATE,
407 LAST_UPDATED_BY,
408 LAST_UPDATE_DATE,
409 LAST_UPDATE_LOGIN,
410 LANGUAGE,
411 SOURCE_LANG
412 ) select
413 B.INSTANCE_SET_ID,
414 B.DISPLAY_NAME,
415 B.DESCRIPTION,
416 B.CREATED_BY,
417 B.CREATION_DATE,
418 B.LAST_UPDATED_BY,
419 B.LAST_UPDATE_DATE,
420 B.LAST_UPDATE_LOGIN,
421 L.LANGUAGE_CODE,
422 B.SOURCE_LANG
423 from FND_OBJECT_INSTANCE_SETS_TL B, FND_LANGUAGES L
424 where L.INSTALLED_FLAG in ('I', 'B')
425 and B.LANGUAGE = userenv('LANG')
426 and not exists
427 (select NULL
428 from FND_OBJECT_INSTANCE_SETS_TL T
429 where T.INSTANCE_SET_ID = B.INSTANCE_SET_ID
430 and T.LANGUAGE = L.LANGUAGE_CODE);
431 end ADD_LANGUAGE;
432
433 end FND_OBJECT_INSTANCE_SETS_PKG;