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