[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_ATTRIBUTES_PVT
Source
1 PACKAGE BODY ICX_CAT_ATTRIBUTES_PVT AS
2 /* $Header: ICXVATRB.pls 120.3 2005/10/22 05:20:44 srmani noship $ */
3
4 PROCEDURE INSERT_ROW(X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_ATTRIBUTE_ID IN NUMBER,
6 X_KEY IN VARCHAR2,
7 X_ATTRIBUTE_NAME IN VARCHAR2,
8 X_DESCRIPTION IN VARCHAR2,
9 X_RT_CATEGORY_ID IN NUMBER,
10 X_TYPE IN NUMBER,
11 X_SEARCH_RESULTS_VISIBLE IN VARCHAR2,
12 X_ITEM_DETAIL_VISIBLE IN VARCHAR2,
13 X_SEARCHABLE IN NUMBER,
14 X_SEQUENCE IN NUMBER,
15 X_CREATED_BY IN NUMBER,
16 X_CREATION_DATE IN DATE,
17 X_LAST_UPDATED_BY IN NUMBER,
18 X_LAST_UPDATE_DATE IN DATE,
19 X_LAST_UPDATE_LOGIN IN NUMBER,
20 X_REQUEST_ID IN NUMBER,
21 X_PROGRAM_APPLICATION_ID IN NUMBER,
22 X_PROGRAM_ID IN NUMBER,
23 X_STORED_IN_TABLE IN VARCHAR2,
24 X_STORED_IN_COLUMN IN VARCHAR2,
25 X_SECTION_TAG IN NUMBER) IS
26 CURSOR C IS
27 SELECT ROWID
28 FROM ICX_CAT_ATTRIBUTES_TL
29 WHERE ATTRIBUTE_ID = X_ATTRIBUTE_ID
30 AND LANGUAGE = userenv('LANG');
31 BEGIN
32 INSERT INTO ICX_CAT_ATTRIBUTES_TL
33 (ATTRIBUTE_ID,
34 KEY,
35 ATTRIBUTE_NAME,
36 DESCRIPTION,
37 RT_CATEGORY_ID,
38 TYPE,
39 SEARCH_RESULTS_VISIBLE,
40 ITEM_DETAIL_VISIBLE,
41 SEARCHABLE,
42 SEQUENCE,
43 CREATED_BY,
44 CREATION_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATE_LOGIN,
48 REQUEST_ID,
49 PROGRAM_APPLICATION_ID,
50 PROGRAM_ID,
51 LANGUAGE,
52 SOURCE_LANG,
53 STORED_IN_TABLE,
54 STORED_IN_COLUMN,
55 SECTION_TAG)
56 SELECT X_ATTRIBUTE_ID,
57 X_KEY,
58 X_ATTRIBUTE_NAME,
59 X_DESCRIPTION,
60 X_RT_CATEGORY_ID,
61 X_TYPE,
62 X_SEARCH_RESULTS_VISIBLE,
63 X_ITEM_DETAIL_VISIBLE,
64 X_SEARCHABLE,
65 X_SEQUENCE,
66 X_CREATED_BY,
67 X_CREATION_DATE,
68 X_LAST_UPDATED_BY,
69 X_LAST_UPDATE_DATE,
70 X_LAST_UPDATE_LOGIN,
71 X_REQUEST_ID,
72 X_PROGRAM_APPLICATION_ID,
73 X_PROGRAM_ID,
74 L.LANGUAGE_CODE,
75 userenv('LANG'),
76 X_STORED_IN_TABLE,
77 X_STORED_IN_COLUMN,
78 X_SECTION_TAG
79 FROM FND_LANGUAGES L
80 WHERE L.INSTALLED_FLAG IN ('I', 'B')
81 AND NOT EXISTS
82 (SELECT NULL
83 FROM ICX_CAT_ATTRIBUTES_TL T
84 WHERE T.ATTRIBUTE_ID = X_ATTRIBUTE_ID
85 AND T.LANGUAGE = L.LANGUAGE_CODE);
86
87 OPEN c;
88 FETCH c
89 INTO X_ROWID;
90 IF (c%NOTFOUND)
91 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(X_ATTRIBUTE_ID IN NUMBER,
100 X_KEY IN VARCHAR2,
101 X_ATTRIBUTE_NAME IN VARCHAR2,
102 X_DESCRIPTION IN VARCHAR2,
103 X_RT_CATEGORY_ID IN NUMBER,
104 X_TYPE IN NUMBER,
105 X_SEARCH_RESULTS_VISIBLE IN VARCHAR2,
106 X_ITEM_DETAIL_VISIBLE IN VARCHAR2,
107 X_SEARCHABLE IN NUMBER,
108 X_SEQUENCE IN NUMBER) IS
109 CURSOR c1 IS
110 SELECT ATTRIBUTE_ID,
111 KEY,
112 ATTRIBUTE_NAME,
113 DESCRIPTION,
114 RT_CATEGORY_ID,
115 TYPE,
116 SEARCH_RESULTS_VISIBLE,
117 ITEM_DETAIL_VISIBLE,
118 SEARCHABLE,
119 SEQUENCE,
120 decode(LANGUAGE,
121 userenv('LANG'),
122 'Y',
123 'N') BASELANG
124 FROM ICX_CAT_ATTRIBUTES_TL
125 WHERE ATTRIBUTE_ID = X_ATTRIBUTE_ID
126 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
127 FOR UPDATE OF ATTRIBUTE_ID NOWAIT;
128 BEGIN
129 FOR tlinfo IN c1
130 LOOP
131 IF (tlinfo.BASELANG = 'Y')
132 THEN
133 IF ((tlinfo.KEY = X_KEY) AND
134 (tlinfo.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME) AND
135 ((tlinfo.DESCRIPTION = X_DESCRIPTION) OR
136 ((tlinfo.DESCRIPTION IS NULL) AND (X_DESCRIPTION IS NULL))) AND
137 (tlinfo.RT_CATEGORY_ID = X_RT_CATEGORY_ID) AND
138 (tlinfo.TYPE = X_TYPE) AND
139 ((tlinfo.SEARCH_RESULTS_VISIBLE = X_SEARCH_RESULTS_VISIBLE) OR
140 ((tlinfo.SEARCH_RESULTS_VISIBLE IS NULL) AND
141 (X_SEARCH_RESULTS_VISIBLE IS NULL))) AND
142 ((tlinfo.ITEM_DETAIL_VISIBLE = X_ITEM_DETAIL_VISIBLE) OR
143 ((tlinfo.ITEM_DETAIL_VISIBLE IS NULL) AND
144 (X_ITEM_DETAIL_VISIBLE IS NULL))) AND
145 ((tlinfo.SEARCHABLE = X_SEARCHABLE) OR
146 ((tlinfo.SEARCHABLE IS NULL) AND (X_SEARCHABLE IS NULL))) AND
147 ((tlinfo.SEQUENCE = X_SEQUENCE) OR
148 ((tlinfo.SEQUENCE IS NULL) AND (X_SEQUENCE IS NULL))))
149 THEN
150 NULL;
151 ELSE
152 fnd_message.set_name('FND',
153 'FORM_RECORD_CHANGED');
154 app_exception.raise_exception;
155 END IF;
156 END IF;
157 END LOOP;
158 RETURN;
159 END LOCK_ROW;
160
161 PROCEDURE UPDATE_ROW(X_ATTRIBUTE_ID IN NUMBER,
162 X_KEY IN VARCHAR2,
163 X_ATTRIBUTE_NAME IN VARCHAR2,
164 X_DESCRIPTION IN VARCHAR2,
165 X_RT_CATEGORY_ID IN NUMBER,
166 X_TYPE IN NUMBER,
167 X_SEARCH_RESULTS_VISIBLE IN VARCHAR2,
168 X_ITEM_DETAIL_VISIBLE IN VARCHAR2,
169 X_SEARCHABLE IN NUMBER,
170 X_SEQUENCE IN NUMBER,
171 X_LAST_UPDATED_BY IN NUMBER,
172 X_LAST_UPDATE_DATE IN DATE,
173 X_LAST_UPDATE_LOGIN IN NUMBER,
174 X_REQUEST_ID IN NUMBER,
175 X_PROGRAM_APPLICATION_ID IN NUMBER,
176 X_PROGRAM_ID IN NUMBER,
177 X_STORED_IN_TABLE IN VARCHAR2,
178 X_STORED_IN_COLUMN IN VARCHAR2,
179 X_SECTION_TAG IN NUMBER) IS
180 BEGIN
181 --Attributes that are not translated i.e rt_category_id, key, type,
182 --search_resuls_visible, item_detail_visible, required, refinable,
183 --searchable, sequence, stored_in_table, stored_in_column,
184 --section_tag and class should be updated
185 --for all rows irrespective of the language and source_lang
186 --So changed the update statement into two update statements,
187 --first sql non-translated values only for those descriptors which are
188 --not customized i.e. for a descriptor there should
189 --be no row with the last_updated_by <> -1.
190 --and the secpnd sql updates the translated values, for the descriptors
191 --which were not already translated by the customers
192 --due the clause (userenv('LANG') in (LANGUAGE, SOURCE_LANG))
193 UPDATE ICX_CAT_ATTRIBUTES_TL o
194 SET KEY = X_KEY,
195 RT_CATEGORY_ID = X_RT_CATEGORY_ID,
196 TYPE = X_TYPE,
197 SEARCH_RESULTS_VISIBLE = X_SEARCH_RESULTS_VISIBLE,
198 ITEM_DETAIL_VISIBLE = X_ITEM_DETAIL_VISIBLE,
199 SEARCHABLE = X_SEARCHABLE,
200 SEQUENCE = X_SEQUENCE,
201 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
202 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
203 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
204 REQUEST_ID = X_REQUEST_ID,
205 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
206 PROGRAM_ID = X_PROGRAM_ID,
207 STORED_IN_TABLE = X_STORED_IN_TABLE,
208 STORED_IN_COLUMN = X_STORED_IN_COLUMN,
209 SECTION_TAG = X_SECTION_TAG
210 WHERE ATTRIBUTE_ID = X_ATTRIBUTE_ID
211 AND NOT EXISTS (SELECT NULL
212 FROM ICX_CAT_ATTRIBUTES_TL i
213 WHERE i.ATTRIBUTE_ID = o.ATTRIBUTE_ID
214 AND i.LAST_UPDATED_BY <> -1);
215
216 UPDATE ICX_CAT_ATTRIBUTES_TL
217 SET ATTRIBUTE_NAME = X_ATTRIBUTE_NAME,
218 DESCRIPTION = X_DESCRIPTION,
219 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
220 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
221 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
222 REQUEST_ID = X_REQUEST_ID,
223 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
224 PROGRAM_ID = X_PROGRAM_ID,
225 SOURCE_LANG = userenv('LANG')
226 WHERE ATTRIBUTE_ID = X_ATTRIBUTE_ID
227 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
228
229 IF (SQL%NOTFOUND)
230 THEN
231 RAISE no_data_found;
232 END IF;
233 END UPDATE_ROW;
234
235 PROCEDURE DELETE_ROW(X_ATTRIBUTE_ID IN NUMBER) IS
236 BEGIN
237 DELETE FROM ICX_CAT_ATTRIBUTES_TL
238 WHERE ATTRIBUTE_ID = X_ATTRIBUTE_ID;
239
240 IF (SQL%NOTFOUND)
241 THEN
242 RAISE no_data_found;
243 END IF;
244
245 END DELETE_ROW;
246
247 PROCEDURE TRANSLATE_ROW(X_ATTRIBUTE_ID IN VARCHAR2,
248 X_OWNER IN VARCHAR2,
249 X_ATTRIBUTE_NAME IN VARCHAR2,
250 X_DESCRIPTION IN VARCHAR2,
251 X_CUSTOM_MODE IN VARCHAR2,
252 X_LAST_UPDATE_DATE IN VARCHAR2) IS
253 BEGIN
254
255 DECLARE
256 f_luby NUMBER; -- entity owner in file
257 f_ludate DATE; -- entity update in file
258 db_luby NUMBER; -- entity owner in db
259 db_ludate DATE; -- entity update in db
260 BEGIN
261 -- Translate owner to file_last_updated_by
262 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
263 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE,
264 'YYYY/MM/DD'),
265 SYSDATE);
266
267 SELECT LAST_UPDATED_BY,
268 LAST_UPDATE_DATE
269 INTO db_luby,
270 db_ludate
271 FROM ICX_CAT_ATTRIBUTES_TL
272 WHERE LANGUAGE = userenv('LANG')
273 AND ATTRIBUTE_ID = to_number(X_ATTRIBUTE_ID);
274
275 -- Update record, honoring customization mode.
276 -- Record should be updated only if:
277 -- a. CUSTOM_MODE = FORCE, or
278 -- b. file owner is CUSTOM, db owner is SEED
279 -- c. owners are the same, and file_date > db_date
280 IF (fnd_load_util.UPLOAD_TEST(p_file_id => f_luby,
281 p_file_lud => f_ludate,
282 p_db_id => db_luby,
283 p_db_lud => db_ludate,
284 p_custom_mode => X_CUSTOM_MODE))
285 THEN
286 UPDATE ICX_CAT_ATTRIBUTES_TL
287 SET ATTRIBUTE_NAME = nvl(X_ATTRIBUTE_NAME,
288 ATTRIBUTE_NAME),
289 description = nvl(X_DESCRIPTION,
290 DESCRIPTION),
291 source_lang = userenv('LANG'),
292 last_update_date = SYSDATE,
293 last_updated_by = f_luby,
294 last_update_login = 0
295 WHERE ATTRIBUTE_ID = to_number(X_ATTRIBUTE_ID)
296 AND userenv('LANG') IN (LANGUAGE, source_lang);
297
298 END IF;
299 END;
300
301 END TRANSLATE_ROW;
302
303 PROCEDURE LOAD_ROW(X_ATTRIBUTE_ID IN VARCHAR2,
304 X_OWNER IN VARCHAR2,
305 X_KEY IN VARCHAR2,
306 X_ATTRIBUTE_NAME IN VARCHAR2,
307 X_DESCRIPTION IN VARCHAR2,
308 X_CATEGORY_ID IN VARCHAR2,
309 X_TYPE IN VARCHAR2,
310 X_SEARCH_RESULTS_VISIBLE IN VARCHAR2,
311 X_ITEM_DETAIL_VISIBLE IN VARCHAR2,
312 X_SEARCHABLE IN VARCHAR2,
313 X_SEQUENCE IN VARCHAR2,
314 X_STORED_IN_TABLE IN VARCHAR2,
315 X_STORED_IN_COLUMN IN VARCHAR2,
316 X_SECTION_TAG IN NUMBER,
317 X_CUSTOM_MODE IN VARCHAR2,
318 X_LAST_UPDATE_DATE IN VARCHAR2) IS
319 BEGIN
320
321 DECLARE
322 row_id VARCHAR2(64);
323 f_luby NUMBER; -- entity owner in file
324 f_ludate DATE; -- entity update in file
325 db_luby NUMBER; -- entity owner in db
326 db_ludate DATE; -- entity update in db
327
328 BEGIN
329 -- Translate owner to file_last_updated_by
330 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
331 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE,
332 'YYYY/MM/DD'),
333 SYSDATE);
334
335 SELECT LAST_UPDATED_BY,
336 LAST_UPDATE_DATE
337 INTO db_luby,
338 db_ludate
339 FROM ICX_CAT_ATTRIBUTES_TL
340 WHERE LANGUAGE = userenv('LANG')
341 AND ATTRIBUTE_ID = to_number(X_ATTRIBUTE_ID);
342
343
344 -- Update record, honoring customization mode.
345 -- Record should be updated only if:
346 -- a. CUSTOM_MODE = FORCE, or
347 -- b. file owner is CUSTOM, db owner is SEED
348 -- c. owners are the same, and file_date > db_date
349 IF (fnd_load_util.UPLOAD_TEST(p_file_id => f_luby,
350 p_file_lud => f_ludate,
351 p_db_id => db_luby,
352 p_db_lud => db_ludate,
353 p_custom_mode => X_CUSTOM_MODE))
354 THEN
355 ICX_CAT_ATTRIBUTES_PVT.UPDATE_ROW(X_ATTRIBUTE_ID => to_number(X_ATTRIBUTE_ID),
356 X_KEY => X_KEY,
357 X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME,
358 X_DESCRIPTION => X_DESCRIPTION,
359 X_RT_CATEGORY_ID => to_number(X_CATEGORY_ID),
360 X_TYPE => to_number(X_TYPE),
361 X_SEARCH_RESULTS_VISIBLE => X_SEARCH_RESULTS_VISIBLE,
362 X_ITEM_DETAIL_VISIBLE => X_ITEM_DETAIL_VISIBLE,
363 X_SEARCHABLE => to_number(X_SEARCHABLE),
364 X_SEQUENCE => to_number(X_SEQUENCE),
365 X_LAST_UPDATED_BY => f_luby,
366 X_LAST_UPDATE_DATE => SYSDATE,
367 X_LAST_UPDATE_LOGIN => 0,
368 X_REQUEST_ID => NULL,
369 X_PROGRAM_APPLICATION_ID => NULL,
370 X_PROGRAM_ID => NULL,
371 X_STORED_IN_TABLE => X_STORED_IN_TABLE,
372 X_STORED_IN_COLUMN => X_STORED_IN_COLUMN,
373 X_SECTION_TAG => X_SECTION_TAG);
374 END IF;
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 ICX_CAT_ATTRIBUTES_PVT.INSERT_ROW(X_ROWID => row_id,
378 X_ATTRIBUTE_ID => to_number(X_ATTRIBUTE_ID),
379 X_KEY => X_KEY,
380 X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME,
381 X_DESCRIPTION => X_DESCRIPTION,
382 X_RT_CATEGORY_ID => to_number(X_CATEGORY_ID),
383 X_TYPE => to_number(X_TYPE),
384 X_SEARCH_RESULTS_VISIBLE => X_SEARCH_RESULTS_VISIBLE,
385 X_ITEM_DETAIL_VISIBLE => X_ITEM_DETAIL_VISIBLE,
386 X_SEARCHABLE => to_number(X_SEARCHABLE),
387 X_SEQUENCE => to_number(X_SEQUENCE),
388 X_CREATED_BY => f_luby,
389 X_CREATION_DATE => SYSDATE,
390 X_LAST_UPDATED_BY => f_luby,
391 X_LAST_UPDATE_DATE => SYSDATE,
392 X_LAST_UPDATE_LOGIN => 0,
393 X_REQUEST_ID => NULL,
394 X_PROGRAM_APPLICATION_ID => NULL,
395 X_PROGRAM_ID => NULL,
396 X_STORED_IN_TABLE => X_STORED_IN_TABLE,
397 X_STORED_IN_COLUMN => X_STORED_IN_COLUMN,
398 X_SECTION_TAG => X_SECTION_TAG);
399 END;
400 END LOAD_ROW;
401
402
403 PROCEDURE ADD_LANGUAGE IS
404 BEGIN
405 INSERT INTO ICX_CAT_ATTRIBUTES_TL
406 (ATTRIBUTE_ID,
407 KEY,
408 ATTRIBUTE_NAME,
409 DESCRIPTION,
410 RT_CATEGORY_ID,
411 TYPE,
412 SEARCH_RESULTS_VISIBLE,
413 ITEM_DETAIL_VISIBLE,
414 SEARCHABLE,
415 SEQUENCE,
416 SECTION_TAG,
417 CREATED_BY,
418 CREATION_DATE,
419 LAST_UPDATED_BY,
420 LAST_UPDATE_DATE,
421 LAST_UPDATE_LOGIN,
422 REQUEST_ID,
423 PROGRAM_APPLICATION_ID,
424 PROGRAM_ID,
425 LANGUAGE,
426 SOURCE_LANG,
427 STORED_IN_TABLE,
428 STORED_IN_COLUMN)
429 SELECT B.ATTRIBUTE_ID,
430 B.KEY,
431 B.ATTRIBUTE_NAME,
432 B.DESCRIPTION,
433 B.RT_CATEGORY_ID,
434 B.TYPE,
435 B.SEARCH_RESULTS_VISIBLE,
436 B.ITEM_DETAIL_VISIBLE,
437 B.SEARCHABLE,
438 B.SEQUENCE,
439 B.SECTION_TAG,
440 B.CREATED_BY,
441 B.CREATION_DATE,
442 B.LAST_UPDATED_BY,
443 B.LAST_UPDATE_DATE,
444 B.LAST_UPDATE_LOGIN,
445 B.REQUEST_ID,
446 B.PROGRAM_APPLICATION_ID,
447 B.PROGRAM_ID,
448 L.LANGUAGE_CODE,
449 B.SOURCE_LANG,
450 B.STORED_IN_TABLE,
451 B.STORED_IN_COLUMN
452 FROM ICX_CAT_ATTRIBUTES_TL B,
453 FND_LANGUAGES L
454 WHERE L.INSTALLED_FLAG IN ('I', 'B')
455 AND B.LANGUAGE = userenv('LANG')
456 AND NOT EXISTS
457 (SELECT NULL
458 FROM ICX_CAT_ATTRIBUTES_TL T
459 WHERE T.ATTRIBUTE_ID = B.ATTRIBUTE_ID
460 AND T.LANGUAGE = L.LANGUAGE_CODE);
461
462 END ADD_LANGUAGE;
463
464 END ICX_CAT_ATTRIBUTES_PVT;