[Home] [Help]
PACKAGE BODY: APPS.IBC_ATTRIBUTE_TYPES_PKG
Source
1 PACKAGE BODY Ibc_Attribute_Types_Pkg AS
2 /* $Header: ibctattb.pls 120.4 2006/06/22 09:19:19 sharma ship $*/
3
4 -- Purpose: Table Handler for Ibc_Attribute_Types_b table.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Sri Rangarajan 01/06/2002 Created Package
10 -- shitij.vatsa 11/04/2002 Updated for FND_API.G_MISS_XXX
11 -- vicho 11/13/2002 Added Overloaded procedures for OA UI
12 -- Subir Anshumali 06/03/2005 Declared OUT and IN OUT arguments as references using the NOCOPY hint
13 -- Sharma 07/04/2005 Modified LOAD_ROW, TRANSLATE_ROW and created
14 -- LOAD_SEED_ROW for R12 LCT standards bug 4411674
15
16 PROCEDURE insert_row (
17 x_rowid OUT NOCOPY VARCHAR2
18 ,p_attribute_type_code IN VARCHAR2
19 ,p_content_type_code IN VARCHAR2
20 ,p_data_type_code IN VARCHAR2
21 ,p_data_length IN NUMBER
22 ,p_min_instances IN NUMBER
23 ,p_max_instances IN NUMBER
24 ,p_reference_code IN VARCHAR2
25 ,p_default_value IN VARCHAR2
26 ,p_updateable_flag IN VARCHAR2
27 ,p_object_version_number IN NUMBER
28 ,p_attribute_type_name IN VARCHAR2
29 ,p_description IN VARCHAR2
30 ,p_creation_date IN DATE --DEFAULT NULL
31 ,p_created_by IN NUMBER --DEFAULT NULL
32 ,p_last_update_date IN DATE --DEFAULT NULL
33 ,p_last_updated_by IN NUMBER --DEFAULT NULL
34 ,p_last_update_login IN NUMBER --DEFAULT NULL
35 ,p_display_order IN NUMBER
36 ,p_flex_value_set_id IN NUMBER
37 ) IS
38 CURSOR c IS SELECT ROWID FROM ibc_attribute_types_b
39 WHERE attribute_type_code = p_attribute_type_code
40 AND content_type_code = p_content_type_code
41 ;
42 BEGIN
43 INSERT INTO ibc_attribute_types_b (
44 attribute_type_code
45 ,content_type_code
46 ,data_type_code
47 ,data_length
48 ,min_instances
49 ,max_instances
50 ,reference_code
51 ,default_value
52 ,updateable_flag
53 ,object_version_number
54 ,creation_date
55 ,created_by
56 ,last_update_date
57 ,last_updated_by
58 ,last_update_login
59 ,display_order
60 ,flex_value_set_id
61 ) VALUES (
62 p_attribute_type_code
63 ,p_content_type_code
64 ,p_data_type_code
65 ,DECODE(p_data_length,FND_API.G_MISS_NUM,NULL,p_data_length)
66 ,DECODE(p_min_instances,FND_API.G_MISS_NUM,NULL,p_min_instances)
67 ,DECODE(p_max_instances,FND_API.G_MISS_NUM,NULL,p_max_instances)
68 ,DECODE(p_reference_code,FND_API.G_MISS_CHAR,NULL,p_reference_code)
69 ,DECODE(p_default_value,FND_API.G_MISS_CHAR,NULL,p_default_value)
70 ,DECODE(p_updateable_flag,FND_API.G_MISS_CHAR,NULL,p_updateable_flag)
71 ,DECODE(p_object_version_number,FND_API.G_MISS_NUM,1,NULL,1,p_object_version_number)
72 ,DECODE(p_creation_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_creation_date)
73 ,DECODE(p_created_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_created_by)
74 ,DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
75 ,DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
76 ,DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
77 ,DECODE(p_display_order,FND_API.G_MISS_NUM,NULL,p_display_order)
78 ,DECODE(p_flex_value_set_id,FND_API.G_MISS_NUM,NULL,p_flex_value_set_id)
79 );
80
81 INSERT INTO ibc_attribute_types_tl (
82 attribute_type_code
83 ,content_type_code
84 ,attribute_type_name
85 ,description
86 ,creation_date
87 ,created_by
88 ,last_update_date
89 ,last_updated_by
90 ,last_update_login
91 ,language
92 ,source_lang
93 ) SELECT
94 p_attribute_type_code
95 ,p_content_type_code
96 ,p_attribute_type_name
97 ,DECODE(p_description,FND_API.G_MISS_CHAR,NULL,p_description)
98 ,DECODE(p_creation_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_creation_date)
99 ,DECODE(p_created_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_created_by)
100 ,DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
101 ,DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
102 ,DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
103 ,l.language_code
104 ,USERENV('lang')
105 FROM fnd_languages l
106 WHERE l.installed_flag IN ('I', 'B')
107 AND NOT EXISTS
108 (SELECT NULL
109 FROM ibc_attribute_types_tl T
110 WHERE T.attribute_type_code = p_attribute_type_code
111 AND T.content_type_code = p_content_type_code
112 AND T.LANGUAGE = l.language_code);
113
114 OPEN c;
115 FETCH c INTO x_rowid;
116 IF (c%NOTFOUND) THEN
117 CLOSE c;
118 RAISE NO_DATA_FOUND;
119 END IF;
120 CLOSE c;
121
122 END insert_row;
123
124 PROCEDURE lock_row (
125 p_attribute_type_code IN VARCHAR2,
126 p_content_type_code IN VARCHAR2,
127 p_data_type_code IN VARCHAR2,
128 p_data_length IN NUMBER,
129 p_min_instances IN NUMBER,
130 p_max_instances IN NUMBER,
131 p_reference_code IN VARCHAR2,
132 p_default_value IN VARCHAR2,
133 p_updateable_flag IN VARCHAR2,
134 p_object_version_number IN NUMBER,
135 p_attribute_type_name IN VARCHAR2,
136 p_description IN VARCHAR2
137 ) IS
138 CURSOR c IS SELECT
139 data_type_code,
140 data_length,
141 min_instances,
142 max_instances,
143 reference_code,
144 default_value,
145 updateable_flag,
146 object_version_number
147 FROM ibc_attribute_types_b
148 WHERE attribute_type_code = p_attribute_type_code
149 AND content_type_code = p_content_type_code
150 FOR UPDATE OF attribute_type_code NOWAIT;
151 recinfo c%ROWTYPE;
152
153 CURSOR c1 IS SELECT
154 attribute_type_name,
155 description,
156 DECODE(LANGUAGE, USERENV('lang'), 'y', 'n') baselang
157 FROM ibc_attribute_types_tl
158 WHERE attribute_type_code = p_attribute_type_code
159 AND content_type_code = p_content_type_code
160 AND USERENV('lang') IN (LANGUAGE, source_lang)
161 FOR UPDATE OF attribute_type_code NOWAIT;
162 BEGIN
163 OPEN c;
164 FETCH c INTO recinfo;
165 IF (c%NOTFOUND) THEN
166 CLOSE c;
167 fnd_message.set_name('fnd', 'form_record_deleted');
168 app_exception.raise_exception;
169 END IF;
170 CLOSE c;
171 IF ( (recinfo.data_type_code = p_data_type_code)
172 AND ((recinfo.data_length = p_data_length)
173 OR ((recinfo.data_length IS NULL) AND (p_data_length IS NULL)))
174 AND (recinfo.min_instances = p_min_instances)
175 AND (recinfo.max_instances = p_max_instances)
176 AND ((recinfo.reference_code = p_reference_code)
177 OR ((recinfo.reference_code IS NULL) AND (p_reference_code IS NULL)))
178 AND ((recinfo.default_value = p_default_value)
179 OR ((recinfo.default_value IS NULL) AND (p_default_value IS NULL)))
180 AND ((recinfo.updateable_flag = p_updateable_flag)
181 OR ((recinfo.updateable_flag IS NULL) AND (p_updateable_flag IS NULL)))
182 AND (recinfo.object_version_number = p_object_version_number)
183
184 ) THEN
185 NULL;
186 ELSE
187 fnd_message.set_name('fnd', 'form_record_changed');
188 app_exception.raise_exception;
189 END IF;
190
191 FOR tlinfo IN c1 LOOP
192 IF (tlinfo.baselang = 'y') THEN
193 IF ( (tlinfo.attribute_type_name = p_attribute_type_name)
194 AND ((tlinfo.description = p_description)
195 OR ((tlinfo.description IS NULL) AND (p_description IS NULL)))
196 ) THEN
197 NULL;
198 ELSE
199 fnd_message.set_name('fnd', 'form_record_changed');
200 app_exception.raise_exception;
201 END IF;
202 END IF;
203 END LOOP;
204 RETURN;
205 END lock_row;
206
207 PROCEDURE update_row (
208 p_attribute_type_code IN VARCHAR2
209 ,p_attribute_type_name IN VARCHAR2 --DEFAULT NULL
210 ,p_content_type_code IN VARCHAR2
211 ,p_data_length IN NUMBER --DEFAULT NULL
212 ,p_data_type_code IN VARCHAR2 --DEFAULT NULL
213 ,p_default_value IN VARCHAR2 --DEFAULT NULL
214 ,p_description IN VARCHAR2 --DEFAULT NULL
215 ,p_last_updated_by IN NUMBER --DEFAULT NULL
216 ,p_last_update_date IN DATE --DEFAULT NULL
217 ,p_last_update_login IN NUMBER --DEFAULT NULL
218 ,p_max_instances IN NUMBER --DEFAULT NULL
219 ,p_min_instances IN NUMBER --DEFAULT NULL
220 ,p_object_version_number IN NUMBER --DEFAULT NULL
221 ,p_reference_code IN VARCHAR2 --DEFAULT NULL
222 ,p_updateable_flag IN VARCHAR2 --DEFAULT NULL
223 ,p_display_order IN NUMBER
224 ,p_flex_value_set_id IN NUMBER
225 )
226 IS
227 BEGIN
228 UPDATE ibc_attribute_types_b SET
229 content_type_code = DECODE(p_content_type_code,FND_API.G_MISS_CHAR,NULL,NULL,content_type_code,p_content_type_code)
230 ,data_type_code = DECODE(p_data_type_code,FND_API.G_MISS_CHAR,NULL,NULL,data_type_code,p_data_type_code)
231 ,data_length = DECODE(p_data_length,FND_API.G_MISS_NUM,NULL,NULL,data_length,p_data_length)
232 ,min_instances = DECODE(p_min_instances,FND_API.G_MISS_NUM,NULL,NULL,min_instances,p_min_instances)
233 ,max_instances = DECODE(p_max_instances,FND_API.G_MISS_NUM,NULL,NULL,max_instances,p_max_instances)
234 ,reference_code = DECODE(p_reference_code,FND_API.G_MISS_CHAR,NULL,NULL,reference_code,p_reference_code)
235 ,default_value = DECODE(p_default_value,FND_API.G_MISS_CHAR,NULL,NULL,default_value,p_default_value)
236 ,updateable_flag = DECODE(p_updateable_flag,FND_API.G_MISS_CHAR,NULL,NULL,updateable_flag,p_updateable_flag)
237 ,object_version_number = NVL(object_version_number,0) + 1
238 ,last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
239 ,last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
240 ,last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
241 ,display_order = DECODE(p_display_order,FND_API.G_MISS_NUM,NULL,NULL,display_order,p_display_order)
242 ,flex_value_set_id = DECODE(p_flex_value_set_id,FND_API.G_MISS_NUM,NULL,NULL,flex_value_set_id,p_flex_value_set_id)
243 WHERE attribute_type_code = p_attribute_type_code
244 AND content_type_code = p_content_type_code
245 AND object_version_number = DECODE(p_object_version_number,
246 fnd_api.g_miss_num,
247 object_version_number,
248 NULL,
249 object_version_number,
250 p_object_version_number);
251
252 IF (SQL%NOTFOUND) THEN
253 RAISE NO_DATA_FOUND;
254 END IF;
255
256 UPDATE ibc_attribute_types_tl SET
257 attribute_type_name = DECODE(p_attribute_type_name,FND_API.G_MISS_CHAR,NULL,NULL,attribute_type_name,p_attribute_type_name)
258 ,description = DECODE(p_description,FND_API.G_MISS_CHAR,NULL,NULL,description,p_description)
259 ,last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
260 ,last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
261 ,last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
262 ,source_lang = USERENV('lang')
263 WHERE attribute_type_code = p_attribute_type_code
264 AND content_type_code = p_content_type_code
265 AND USERENV('lang') IN (LANGUAGE, source_lang);
266
267 IF (SQL%NOTFOUND) THEN
268 RAISE NO_DATA_FOUND;
269 END IF;
270 END update_row;
271
272 PROCEDURE delete_row (
273 p_attribute_type_code IN VARCHAR2,
274 p_content_type_code IN VARCHAR2
275 ) IS
276 BEGIN
277 DELETE FROM ibc_attribute_types_tl
278 WHERE attribute_type_code = p_attribute_type_code
279 AND content_type_code = p_content_type_code;
280
281 IF (SQL%NOTFOUND) THEN
282 RAISE NO_DATA_FOUND;
283 END IF;
284
285 DELETE FROM ibc_attribute_types_b
286 WHERE attribute_type_code = p_attribute_type_code
287 AND content_type_code = p_content_type_code;
288
289 IF (SQL%NOTFOUND) THEN
290 RAISE NO_DATA_FOUND;
291 END IF;
292 END delete_row;
293
294 --
295 -- Overloaded Delete to delete all the content Types
296 --
297
298 PROCEDURE delete_rows (
299 p_content_type_code IN VARCHAR2
300 ) IS
301 BEGIN
302 DELETE FROM ibc_attribute_types_tl
303 WHERE attribute_type_code = attribute_type_code
304 AND content_type_code = p_content_type_code;
305
306 IF (SQL%NOTFOUND) THEN
307 RAISE NO_DATA_FOUND;
308 END IF;
309
310 DELETE FROM ibc_attribute_types_b
311 WHERE attribute_type_code = attribute_type_code
312 AND content_type_code = p_content_type_code;
313
314 IF (SQL%NOTFOUND) THEN
315 RAISE NO_DATA_FOUND;
316 END IF;
317 END delete_rows;
318
319 PROCEDURE add_language
320 IS
321 BEGIN
322 DELETE FROM ibc_attribute_types_tl T
323 WHERE NOT EXISTS
324 (SELECT NULL
325 FROM ibc_attribute_types_b b
326 WHERE b.attribute_type_code = T.attribute_type_code
327 AND b.content_type_code = T.content_type_code
328 );
329
330 UPDATE ibc_attribute_types_tl T SET (
331 attribute_type_name,
332 description
333 ) = (SELECT
334 b.attribute_type_name,
335 b.description
336 FROM ibc_attribute_types_tl b
337 WHERE b.attribute_type_code = T.attribute_type_code
338 AND b.content_type_code = T.content_type_code
339 AND b.LANGUAGE = T.source_lang)
340 WHERE (
341 T.attribute_type_code,
342 T.content_type_code,
343 T.LANGUAGE
344 ) IN (SELECT
345 subt.attribute_type_code,
346 subt.content_type_code,
347 subt.LANGUAGE
348 FROM ibc_attribute_types_tl subb, ibc_attribute_types_tl subt
349 WHERE subb.attribute_type_code = subt.attribute_type_code
350 AND subb.content_type_code = subt.content_type_code
351 AND subb.LANGUAGE = subt.source_lang
352 AND (subb.attribute_type_name <> subt.attribute_type_name
353 OR subb.description <> subt.description
354 OR (subb.description IS NULL AND subt.description IS NOT NULL)
355 OR (subb.description IS NOT NULL AND subt.description IS NULL)
356 ));
357
358 INSERT INTO ibc_attribute_types_tl (
359 attribute_type_code,
360 content_type_code,
361 attribute_type_name,
362 description,
363 created_by,
364 creation_date,
365 last_updated_by,
366 last_update_date,
367 last_update_login,
368 LANGUAGE,
369 source_lang
370 ) SELECT
371 b.attribute_type_code,
372 b.content_type_code,
373 b.attribute_type_name,
374 b.description,
375 b.created_by,
376 b.creation_date,
377 b.last_updated_by,
378 b.last_update_date,
379 b.last_update_login,
380 l.language_code,
381 b.source_lang
382 FROM ibc_attribute_types_tl b, fnd_languages l
383 WHERE l.installed_flag IN ('I', 'B')
384 AND b.LANGUAGE = USERENV('lang')
385 AND NOT EXISTS
386 (SELECT NULL
387 FROM ibc_attribute_types_tl T
388 WHERE T.attribute_type_code = b.attribute_type_code
389 AND T.content_type_code = b.content_type_code
390 AND T.LANGUAGE = l.language_code);
391 END add_language;
392
393 PROCEDURE LOAD_SEED_ROW (
394 p_upload_mode IN VARCHAR2,
395 p_attribute_type_code IN VARCHAR2
396 ,p_content_type_code IN VARCHAR2
397 ,p_data_type_code IN VARCHAR2
398 ,p_data_length IN NUMBER
399 ,p_min_instances IN NUMBER
400 ,p_max_instances IN NUMBER
401 ,p_reference_code IN VARCHAR2
402 ,p_default_value IN VARCHAR2
403 ,p_updateable_flag IN VARCHAR2
404 ,p_attribute_type_name IN VARCHAR2
405 ,p_description IN VARCHAR2
406 ,p_owner IN VARCHAR2
407 ,p_display_order IN NUMBER DEFAULT NULL
408 ,p_flex_value_set_id IN NUMBER DEFAULT NULL,
409 p_last_update_date IN VARCHAR2 ) IS
410
411 BEGIN
412 IF (p_upload_mode = 'NLS') THEN
413 IBC_ATTRIBUTE_TYPES_PKG.TRANSLATE_ROW (
414 p_upload_mode => p_upload_mode,
415 p_content_type_code => p_content_type_code,
416 p_attribute_type_code => p_attribute_type_code,
417 p_attribute_type_name => p_attribute_type_name,
418 p_description => p_description,
419 p_owner => p_owner,
420 p_last_update_date => p_last_update_date);
421 ELSE
422 IBC_ATTRIBUTE_TYPES_PKG.LOAD_ROW (
423 p_upload_mode => p_upload_mode,
424 p_attribute_type_code => p_attribute_type_code,
425 p_content_type_code => p_content_type_code,
426 p_data_type_code =>p_data_type_code,
427 p_data_length =>TO_NUMBER(p_data_length),
428 p_min_instances =>TO_NUMBER(p_min_instances),
429 p_max_instances =>TO_NUMBER(p_max_instances),
430 p_reference_code =>p_reference_code,
431 p_default_value =>p_default_value,
432 p_updateable_flag =>p_updateable_flag,
433 p_attribute_type_name =>p_attribute_type_name,
434 p_description =>p_description,
435 p_owner =>p_owner,
436 p_display_order =>p_display_order,
437 p_flex_value_set_id =>p_flex_value_set_id,
438 p_last_update_date => p_last_update_date);
439 END IF;
440
441 END;
442
443
444 PROCEDURE LOAD_ROW (
445 p_upload_mode IN VARCHAR2,
446 p_attribute_type_code IN VARCHAR2,
447 p_content_type_code IN VARCHAR2,
448 p_data_type_code IN VARCHAR2,
449 p_data_length IN NUMBER,
450 p_min_instances IN NUMBER,
451 p_max_instances IN NUMBER,
452 p_reference_code IN VARCHAR2,
453 p_default_value IN VARCHAR2,
454 p_updateable_flag IN VARCHAR2,
455 p_attribute_type_name IN VARCHAR2,
456 p_description IN VARCHAR2,
457 p_OWNER IN VARCHAR2,
458 p_display_order IN NUMBER,
459 p_flex_value_set_id IN NUMBER,
460 p_last_update_date IN VARCHAR2 ) IS
461 BEGIN
462 DECLARE
463 l_user_id NUMBER := 0;
464 l_row_id VARCHAR2(64);
465 l_last_update_date DATE;
466
467 db_user_id NUMBER := 0;
468 db_last_update_date DATE;
469 BEGIN
470 --get last updated by user id
471 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
472
473 --translate data type VARCHAR2 to DATE for last_update_date
474 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
475
476 -- get updatedby and update_date values if existing in db
477 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
478 FROM ibc_attribute_types_b
479 WHERE attribute_type_code = p_attribute_type_code
480 AND content_type_code = p_content_type_code
481 AND object_version_number = DECODE(object_version_number,
482 fnd_api.g_miss_num,
483 object_version_number,
484 NULL,
485 object_version_number,
486 object_version_number);
487
488 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
489 db_user_id, db_last_update_date, p_upload_mode )) THEN
490
491 Ibc_Attribute_Types_Pkg.Update_row (
492 p_attribute_type_code => NVL(p_attribute_type_code,FND_API.G_MISS_CHAR)
493 ,p_content_type_code => NVL(p_content_type_code,FND_API.G_MISS_CHAR)
494 ,p_data_type_code => NVL(p_data_type_code,FND_API.G_MISS_CHAR)
495 ,p_data_length => NVL(p_data_length,FND_API.G_MISS_NUM)
496 ,p_min_instances => NVL(p_min_instances,FND_API.G_MISS_NUM)
497 ,p_max_instances => NVL(p_max_instances,FND_API.G_MISS_NUM)
498 ,p_reference_code => NVL(p_reference_code,FND_API.G_MISS_CHAR)
499 ,p_default_value => NVL(p_default_value,FND_API.G_MISS_CHAR)
500 ,p_updateable_flag => NVL(p_updateable_flag,FND_API.G_MISS_CHAR)
501 ,p_object_version_number => NULL
502 ,p_attribute_type_name => NVL(p_attribute_type_name,FND_API.G_MISS_CHAR)
503 ,p_description => NVL(p_description,FND_API.G_MISS_CHAR)
504 ,p_last_update_date => l_last_update_date
505 ,p_last_updated_by => l_user_id
506 ,p_last_update_login => 0
507 ,p_display_order => NVL(p_display_order,FND_API.G_MISS_NUM)
508 ,p_flex_value_set_id => NVL(p_flex_value_set_id,FND_API.G_MISS_NUM)
509 );
510 END IF;
511
512 EXCEPTION
513 WHEN NO_DATA_FOUND THEN
514
515 Ibc_Attribute_Types_Pkg.insert_row (
516 x_rowid =>l_row_id,
517 p_attribute_type_code =>p_attribute_type_code,
518 p_content_type_code =>p_content_type_code,
519 p_data_type_code =>p_data_type_code,
520 p_data_length =>p_data_length,
521 p_min_instances =>p_min_instances,
522 p_max_instances =>p_max_instances,
523 p_reference_code =>p_reference_code,
524 p_default_value =>p_default_value,
525 p_updateable_flag =>p_updateable_flag,
526 p_object_version_number =>FND_API.G_MISS_NUM,
527 p_attribute_type_name =>p_attribute_type_name,
528 p_description =>p_description,
529 p_creation_date =>l_last_update_date,
530 p_created_by =>l_user_id,
531 p_last_update_date =>l_last_update_date,
532 p_last_updated_by =>l_user_id,
533 p_last_update_login =>0,
534 p_display_order => p_display_order,
535 p_flex_value_set_id => p_flex_value_set_id
536 );
537 END;
538 END LOAD_ROW;
539
540 PROCEDURE TRANSLATE_ROW (
541 p_upload_mode IN VARCHAR2,
542 p_CONTENT_TYPE_CODE IN VARCHAR2,
543 p_ATTRIBUTE_TYPE_CODE IN VARCHAR2,
544 p_ATTRIBUTE_TYPE_NAME IN VARCHAR2,
545 p_DESCRIPTION IN VARCHAR2,
546 p_OWNER IN VARCHAR2,
547 p_last_update_date IN VARCHAR2 ) IS
548
549 l_user_id NUMBER := 0;
550 l_row_id VARCHAR2(64);
551 l_last_update_date DATE;
552
553 db_user_id NUMBER := 0;
554 db_last_update_date DATE;
555 BEGIN
556 --get last updated by user id
557 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
558
559 --translate data type VARCHAR2 to DATE for last_update_date
560 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
561
562 -- get updatedby and update_date values if existing in db
563 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
564 FROM IBC_ATTRIBUTE_TYPES_TL
565 WHERE CONTENT_TYPE_CODE = p_CONTENT_TYPE_CODE
566 AND ATTRIBUTE_TYPE_CODE = p_ATTRIBUTE_TYPE_CODE
567 AND USERENV('LANG') IN (LANGUAGE, source_lang);
568
569 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
570 db_user_id, db_last_update_date, p_upload_mode )) THEN
571 -- Only update rows which have not been altered by user
572 UPDATE IBC_ATTRIBUTE_TYPES_TL
573 SET description = p_DESCRIPTION,
574 ATTRIBUTE_TYPE_NAME = p_ATTRIBUTE_TYPE_NAME,
575 source_lang = USERENV('LANG'),
576 last_update_date = l_last_update_date,
577 last_updated_by = l_user_id,
578 last_update_login = 0
579 WHERE CONTENT_TYPE_CODE = p_CONTENT_TYPE_CODE
580 AND ATTRIBUTE_TYPE_CODE = p_ATTRIBUTE_TYPE_CODE
581 AND USERENV('LANG') IN (LANGUAGE, source_lang);
582 END IF;
583
584 END TRANSLATE_ROW;
585
586
587 --
588 -- Overloaded Procedures for OA Content Type UI
589 --
590 PROCEDURE INSERT_ROW (
591 X_ROWID IN OUT NOCOPY VARCHAR2,
592 X_CONTENT_TYPE_CODE IN VARCHAR2,
593 X_ATTRIBUTE_TYPE_CODE IN VARCHAR2,
594 X_MIN_INSTANCES IN NUMBER,
595 X_MAX_INSTANCES IN NUMBER,
596 X_DEFAULT_VALUE IN VARCHAR2,
597 X_UPDATEABLE_FLAG IN VARCHAR2,
598 X_REFERENCE_CODE IN VARCHAR2,
599 X_OBJECT_VERSION_NUMBER IN NUMBER,
600 X_SECURITY_GROUP_ID IN NUMBER,
601 X_DISPLAY_ORDER IN NUMBER,
602 X_FLEX_VALUE_SET_ID IN NUMBER,
603 X_DATA_TYPE_CODE IN VARCHAR2,
604 X_DATA_LENGTH IN NUMBER,
605 X_ATTRIBUTE_TYPE_NAME IN VARCHAR2,
606 X_DESCRIPTION IN VARCHAR2,
607 X_CREATION_DATE IN DATE,
608 X_CREATED_BY IN NUMBER,
609 X_LAST_UPDATE_DATE IN DATE,
610 X_LAST_UPDATED_BY IN NUMBER,
611 X_LAST_UPDATE_LOGIN IN NUMBER
612 ) IS
613 CURSOR C IS SELECT ROWID FROM IBC_ATTRIBUTE_TYPES_B
614 WHERE CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE
615 AND ATTRIBUTE_TYPE_CODE = X_ATTRIBUTE_TYPE_CODE
616 ;
617 BEGIN
618 INSERT INTO IBC_ATTRIBUTE_TYPES_B (
619 MIN_INSTANCES,
620 MAX_INSTANCES,
621 DEFAULT_VALUE,
622 UPDATEABLE_FLAG,
623 REFERENCE_CODE,
624 OBJECT_VERSION_NUMBER,
625 SECURITY_GROUP_ID,
626 DISPLAY_ORDER,
627 ATTRIBUTE_TYPE_CODE,
628 CONTENT_TYPE_CODE,
629 FLEX_VALUE_SET_ID,
630 DATA_TYPE_CODE,
631 DATA_LENGTH,
632 CREATION_DATE,
633 CREATED_BY,
634 LAST_UPDATE_DATE,
635 LAST_UPDATED_BY,
636 LAST_UPDATE_LOGIN
637 ) VALUES (
638 X_MIN_INSTANCES,
639 X_MAX_INSTANCES,
640 X_DEFAULT_VALUE,
641 X_UPDATEABLE_FLAG,
642 X_REFERENCE_CODE,
643 X_OBJECT_VERSION_NUMBER,
644 X_SECURITY_GROUP_ID,
645 X_DISPLAY_ORDER,
646 X_ATTRIBUTE_TYPE_CODE,
647 X_CONTENT_TYPE_CODE,
648 X_FLEX_VALUE_SET_ID,
649 X_DATA_TYPE_CODE,
650 X_DATA_LENGTH,
651 X_CREATION_DATE,
652 X_CREATED_BY,
653 X_LAST_UPDATE_DATE,
654 X_LAST_UPDATED_BY,
655 X_LAST_UPDATE_LOGIN
656 );
657
658 INSERT INTO IBC_ATTRIBUTE_TYPES_TL (
659 LAST_UPDATE_DATE,
660 LAST_UPDATE_LOGIN,
661 SECURITY_GROUP_ID,
662 CREATED_BY,
663 CREATION_DATE,
664 LAST_UPDATED_BY,
665 ATTRIBUTE_TYPE_CODE,
666 CONTENT_TYPE_CODE,
667 ATTRIBUTE_TYPE_NAME,
668 DESCRIPTION,
669 LANGUAGE,
670 SOURCE_LANG
671 ) SELECT
672 X_LAST_UPDATE_DATE,
673 X_LAST_UPDATE_LOGIN,
674 X_SECURITY_GROUP_ID,
675 X_CREATED_BY,
676 X_CREATION_DATE,
677 X_LAST_UPDATED_BY,
678 X_ATTRIBUTE_TYPE_CODE,
679 X_CONTENT_TYPE_CODE,
680 X_ATTRIBUTE_TYPE_NAME,
681 X_DESCRIPTION,
682 L.LANGUAGE_CODE,
683 USERENV('LANG')
684 FROM FND_LANGUAGES L
685 WHERE L.INSTALLED_FLAG IN ('I', 'B')
686 AND NOT EXISTS
687 (SELECT NULL
688 FROM IBC_ATTRIBUTE_TYPES_TL T
689 WHERE T.CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE
690 AND T.ATTRIBUTE_TYPE_CODE = X_ATTRIBUTE_TYPE_CODE
691 AND T.LANGUAGE = L.LANGUAGE_CODE);
692
693 OPEN c;
694 FETCH c INTO X_ROWID;
695 IF (c%NOTFOUND) THEN
696 CLOSE c;
697 RAISE NO_DATA_FOUND;
698 END IF;
699 CLOSE c;
700
701 END INSERT_ROW;
702
703 PROCEDURE LOCK_ROW (
704 X_CONTENT_TYPE_CODE IN VARCHAR2,
705 X_ATTRIBUTE_TYPE_CODE IN VARCHAR2,
706 X_MIN_INSTANCES IN NUMBER,
707 X_MAX_INSTANCES IN NUMBER,
708 X_DEFAULT_VALUE IN VARCHAR2,
709 X_UPDATEABLE_FLAG IN VARCHAR2,
710 X_REFERENCE_CODE IN VARCHAR2,
711 X_OBJECT_VERSION_NUMBER IN NUMBER,
712 X_SECURITY_GROUP_ID IN NUMBER,
713 X_DISPLAY_ORDER IN NUMBER,
714 X_FLEX_VALUE_SET_ID IN NUMBER,
715 X_DATA_TYPE_CODE IN VARCHAR2,
716 X_DATA_LENGTH IN NUMBER,
717 X_ATTRIBUTE_TYPE_NAME IN VARCHAR2,
718 X_DESCRIPTION IN VARCHAR2
719 ) IS
720 CURSOR c IS SELECT
721 MIN_INSTANCES,
722 MAX_INSTANCES,
723 DEFAULT_VALUE,
724 UPDATEABLE_FLAG,
725 REFERENCE_CODE,
726 OBJECT_VERSION_NUMBER,
727 SECURITY_GROUP_ID,
728 DISPLAY_ORDER,
729 FLEX_VALUE_SET_ID,
730 DATA_TYPE_CODE,
731 DATA_LENGTH
732 FROM IBC_ATTRIBUTE_TYPES_B
733 WHERE CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE
734 AND ATTRIBUTE_TYPE_CODE = X_ATTRIBUTE_TYPE_CODE
735 FOR UPDATE OF CONTENT_TYPE_CODE NOWAIT;
736 recinfo c%ROWTYPE;
737
738 CURSOR c1 IS SELECT
739 ATTRIBUTE_TYPE_NAME,
740 DESCRIPTION,
741 DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
742 FROM IBC_ATTRIBUTE_TYPES_TL
743 WHERE CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE
744 AND ATTRIBUTE_TYPE_CODE = X_ATTRIBUTE_TYPE_CODE
745 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
746 FOR UPDATE OF CONTENT_TYPE_CODE NOWAIT;
747 BEGIN
748 OPEN c;
749 FETCH c INTO recinfo;
750 IF (c%NOTFOUND) THEN
751 CLOSE c;
752 Fnd_Message.set_name('FND', 'FORM_RECORD_DELETED');
753 App_Exception.raise_exception;
754 END IF;
755 CLOSE c;
756 IF ( (recinfo.MIN_INSTANCES = X_MIN_INSTANCES)
757 AND ((recinfo.MAX_INSTANCES = X_MAX_INSTANCES)
758 OR ((recinfo.MAX_INSTANCES IS NULL) AND (X_MAX_INSTANCES IS NULL)))
759 AND ((recinfo.DEFAULT_VALUE = X_DEFAULT_VALUE)
760 OR ((recinfo.DEFAULT_VALUE IS NULL) AND (X_DEFAULT_VALUE IS NULL)))
761 AND (recinfo.UPDATEABLE_FLAG = X_UPDATEABLE_FLAG)
762 AND ((recinfo.REFERENCE_CODE = X_REFERENCE_CODE)
763 OR ((recinfo.REFERENCE_CODE IS NULL) AND (X_REFERENCE_CODE IS NULL)))
764 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
765 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
766 OR ((recinfo.SECURITY_GROUP_ID IS NULL) AND (X_SECURITY_GROUP_ID IS NULL)))
767 AND ((recinfo.DISPLAY_ORDER = X_DISPLAY_ORDER)
768 OR ((recinfo.DISPLAY_ORDER IS NULL) AND (X_DISPLAY_ORDER IS NULL)))
769 AND ((recinfo.FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID)
770 OR ((recinfo.FLEX_VALUE_SET_ID IS NULL) AND (X_FLEX_VALUE_SET_ID IS NULL)))
771 AND (recinfo.DATA_TYPE_CODE = X_DATA_TYPE_CODE)
772 AND ((recinfo.DATA_LENGTH = X_DATA_LENGTH)
773 OR ((recinfo.DATA_LENGTH IS NULL) AND (X_DATA_LENGTH IS NULL)))
774 ) THEN
775 NULL;
776 ELSE
777 Fnd_Message.set_name('FND', 'FORM_RECORD_CHANGED');
778 App_Exception.raise_exception;
779 END IF;
780
781 FOR tlinfo IN c1 LOOP
782 IF (tlinfo.BASELANG = 'Y') THEN
783 IF ( (tlinfo.ATTRIBUTE_TYPE_NAME = X_ATTRIBUTE_TYPE_NAME)
784 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
785 OR ((tlinfo.DESCRIPTION IS NULL) AND (X_DESCRIPTION IS NULL)))
786 ) THEN
787 NULL;
788 ELSE
789 Fnd_Message.set_name('FND', 'FORM_RECORD_CHANGED');
790 App_Exception.raise_exception;
791 END IF;
792 END IF;
793 END LOOP;
794 RETURN;
795 END LOCK_ROW;
796
797 PROCEDURE UPDATE_ROW (
798 X_CONTENT_TYPE_CODE IN VARCHAR2,
799 X_ATTRIBUTE_TYPE_CODE IN VARCHAR2,
800 X_MIN_INSTANCES IN NUMBER,
801 X_MAX_INSTANCES IN NUMBER,
802 X_DEFAULT_VALUE IN VARCHAR2,
803 X_UPDATEABLE_FLAG IN VARCHAR2,
804 X_REFERENCE_CODE IN VARCHAR2,
805 X_OBJECT_VERSION_NUMBER IN NUMBER,
806 X_SECURITY_GROUP_ID IN NUMBER,
807 X_DISPLAY_ORDER IN NUMBER,
808 X_FLEX_VALUE_SET_ID IN NUMBER,
809 X_DATA_TYPE_CODE IN VARCHAR2,
810 X_DATA_LENGTH IN NUMBER,
811 X_ATTRIBUTE_TYPE_NAME IN VARCHAR2,
812 X_DESCRIPTION IN VARCHAR2,
813 X_LAST_UPDATE_DATE IN DATE,
814 X_LAST_UPDATED_BY IN NUMBER,
815 X_LAST_UPDATE_LOGIN IN NUMBER
816 ) IS
817 BEGIN
818 UPDATE IBC_ATTRIBUTE_TYPES_B SET
819 MIN_INSTANCES = X_MIN_INSTANCES,
820 MAX_INSTANCES = X_MAX_INSTANCES,
821 DEFAULT_VALUE = X_DEFAULT_VALUE,
822 UPDATEABLE_FLAG = X_UPDATEABLE_FLAG,
823 REFERENCE_CODE = X_REFERENCE_CODE,
824 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
825 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
826 DISPLAY_ORDER = X_DISPLAY_ORDER,
827 FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID,
828 DATA_TYPE_CODE = X_DATA_TYPE_CODE,
829 DATA_LENGTH = X_DATA_LENGTH,
830 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
831 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
832 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
833 WHERE CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE
834 AND ATTRIBUTE_TYPE_CODE = X_ATTRIBUTE_TYPE_CODE;
835
836 IF (SQL%NOTFOUND) THEN
837 RAISE NO_DATA_FOUND;
838 END IF;
839
840 UPDATE IBC_ATTRIBUTE_TYPES_TL SET
841 ATTRIBUTE_TYPE_NAME = X_ATTRIBUTE_TYPE_NAME,
842 DESCRIPTION = X_DESCRIPTION,
843 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
844 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
845 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
846 SOURCE_LANG = USERENV('LANG')
847 WHERE CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE
848 AND ATTRIBUTE_TYPE_CODE = X_ATTRIBUTE_TYPE_CODE
849 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
850
851 IF (SQL%NOTFOUND) THEN
852 RAISE NO_DATA_FOUND;
853 END IF;
854 END UPDATE_ROW;
855
856 PROCEDURE DELETE_ROW (
857 X_CONTENT_TYPE_CODE IN VARCHAR2,
858 X_ATTRIBUTE_TYPE_CODE IN VARCHAR2
859 ) IS
860 BEGIN
861 DELETE FROM IBC_ATTRIBUTE_TYPES_TL
862 WHERE CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE
863 AND ATTRIBUTE_TYPE_CODE = X_ATTRIBUTE_TYPE_CODE;
864
865 IF (SQL%NOTFOUND) THEN
866 RAISE NO_DATA_FOUND;
867 END IF;
868
869 DELETE FROM IBC_ATTRIBUTE_TYPES_B
870 WHERE CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE
871 AND ATTRIBUTE_TYPE_CODE = X_ATTRIBUTE_TYPE_CODE;
872
873 IF (SQL%NOTFOUND) THEN
874 RAISE NO_DATA_FOUND;
875 END IF;
876 END DELETE_ROW;
877
878
879 END Ibc_Attribute_Types_Pkg;