[Home] [Help]
PACKAGE BODY: APPS.FND_DOC_CATEGORIES_PKG
Source
1 package body FND_DOC_CATEGORIES_PKG as
2 /* $Header: AFAKCATB.pls 115.15 2004/02/06 20:49:39 blash ship $ */
3
4
5 procedure INSERT_ROW (
6 X_ROWID in out NOCOPY VARCHAR2,
7 X_CATEGORY_ID in NUMBER,
8 X_APPLICATION_ID in NUMBER,
9 X_NAME in VARCHAR2,
10 X_START_DATE_ACTIVE in DATE,
11 X_END_DATE_ACTIVE in DATE,
12 X_ATTRIBUTE_CATEGORY in VARCHAR2,
13 X_ATTRIBUTE1 in VARCHAR2,
14 X_ATTRIBUTE2 in VARCHAR2,
15 X_ATTRIBUTE3 in VARCHAR2,
16 X_ATTRIBUTE4 in VARCHAR2,
17 X_ATTRIBUTE5 in VARCHAR2,
18 X_ATTRIBUTE6 in VARCHAR2,
19 X_ATTRIBUTE7 in VARCHAR2,
20 X_ATTRIBUTE8 in VARCHAR2,
21 X_ATTRIBUTE9 in VARCHAR2,
22 X_ATTRIBUTE10 in VARCHAR2,
23 X_ATTRIBUTE11 in VARCHAR2,
24 X_ATTRIBUTE12 in VARCHAR2,
25 X_ATTRIBUTE13 in VARCHAR2,
26 X_ATTRIBUTE14 in VARCHAR2,
27 X_ATTRIBUTE15 in VARCHAR2,
28 X_DEFAULT_DATATYPE_ID in NUMBER,
29 X_USER_NAME in VARCHAR2,
30 X_CREATION_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_LAST_UPDATE_DATE in DATE,
33 X_LAST_UPDATED_BY in NUMBER,
34 X_LAST_UPDATE_LOGIN in NUMBER) is
35 cursor C is select ROWID from FND_DOCUMENT_CATEGORIES
36 where CATEGORY_ID = X_CATEGORY_ID;
37 begin
38
39 insert into FND_DOCUMENT_CATEGORIES (
40 CATEGORY_ID,
41 APPLICATION_ID,
42 NAME,
43 START_DATE_ACTIVE,
44 END_DATE_ACTIVE,
45 ATTRIBUTE_CATEGORY,
46 ATTRIBUTE1,
47 ATTRIBUTE2,
48 ATTRIBUTE3,
49 ATTRIBUTE4,
50 ATTRIBUTE5,
51 ATTRIBUTE6,
52 ATTRIBUTE7,
53 ATTRIBUTE8,
54 ATTRIBUTE9,
55 ATTRIBUTE10,
56 ATTRIBUTE11,
57 ATTRIBUTE12,
58 ATTRIBUTE13,
59 ATTRIBUTE14,
60 ATTRIBUTE15,
61 DEFAULT_DATATYPE_ID,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN
67 ) values (
68 X_CATEGORY_ID,
69 X_APPLICATION_ID,
70 X_NAME,
71 X_START_DATE_ACTIVE,
72 X_END_DATE_ACTIVE,
73 X_ATTRIBUTE_CATEGORY,
74 X_ATTRIBUTE1,
75 X_ATTRIBUTE2,
76 X_ATTRIBUTE3,
77 X_ATTRIBUTE4,
78 X_ATTRIBUTE5,
79 X_ATTRIBUTE6,
80 X_ATTRIBUTE7,
81 X_ATTRIBUTE8,
82 X_ATTRIBUTE9,
83 X_ATTRIBUTE10,
84 X_ATTRIBUTE11,
85 X_ATTRIBUTE12,
86 X_ATTRIBUTE13,
87 X_ATTRIBUTE14,
88 X_ATTRIBUTE15,
89 X_DEFAULT_DATATYPE_ID,
90 X_CREATION_DATE,
91 X_CREATED_BY,
92 X_LAST_UPDATE_DATE,
93 X_LAST_UPDATED_BY,
94 X_LAST_UPDATE_LOGIN );
95
96 open c;
97 fetch c into X_ROWID;
98 if (c%notfound) then
99 close c;
100 raise no_data_found;
101 end if;
102 close c;
103
104 insert into FND_DOCUMENT_CATEGORIES_TL (
105 CATEGORY_ID,
106 LANGUAGE,
107 NAME,
108 USER_NAME,
109 CREATION_DATE,
110 CREATED_BY,
111 LAST_UPDATE_DATE,
112 LAST_UPDATED_BY,
113 LAST_UPDATE_LOGIN,
114 SOURCE_LANG,
115 app_source_version
116 ) select
117 X_CATEGORY_ID,
118 L.LANGUAGE_CODE,
119 X_NAME,
120 X_USER_NAME,
121 X_CREATION_DATE,
122 X_CREATED_BY,
123 X_LAST_UPDATE_DATE,
124 X_LAST_UPDATED_BY,
125 X_LAST_UPDATE_LOGIN,
126 userenv('LANG'),
127 '<schema><<' || USER || '>>'
128 from FND_LANGUAGES L
129 where L.INSTALLED_FLAG in ('I', 'B')
130 and not exists
131 (select NULL
132 from FND_DOCUMENT_CATEGORIES_TL T
133 where T.CATEGORY_ID = X_CATEGORY_ID
134 and T.LANGUAGE = L.LANGUAGE_CODE);
135 end INSERT_ROW;
136
137 procedure LOCK_ROW (
138 X_CATEGORY_ID in NUMBER,
139 X_APPLICATION_ID in NUMBER,
140 X_NAME in VARCHAR2,
141 X_START_DATE_ACTIVE in DATE,
142 X_END_DATE_ACTIVE in DATE,
143 X_ATTRIBUTE_CATEGORY in VARCHAR2,
144 X_ATTRIBUTE1 in VARCHAR2,
145 X_ATTRIBUTE2 in VARCHAR2,
146 X_ATTRIBUTE3 in VARCHAR2,
147 X_ATTRIBUTE4 in VARCHAR2,
148 X_ATTRIBUTE5 in VARCHAR2,
149 X_ATTRIBUTE6 in VARCHAR2,
150 X_ATTRIBUTE7 in VARCHAR2,
151 X_ATTRIBUTE8 in VARCHAR2,
152 X_ATTRIBUTE9 in VARCHAR2,
153 X_ATTRIBUTE10 in VARCHAR2,
154 X_ATTRIBUTE11 in VARCHAR2,
155 X_ATTRIBUTE12 in VARCHAR2,
156 X_ATTRIBUTE13 in VARCHAR2,
157 X_ATTRIBUTE14 in VARCHAR2,
158 X_ATTRIBUTE15 in VARCHAR2,
159 X_DEFAULT_DATATYPE_ID in NUMBER,
160 X_USER_NAME in VARCHAR2) is
161 cursor c is select
162 APPLICATION_ID,
163 NAME,
164 START_DATE_ACTIVE,
165 END_DATE_ACTIVE,
166 ATTRIBUTE_CATEGORY,
167 ATTRIBUTE1,
168 ATTRIBUTE2,
169 ATTRIBUTE3,
170 ATTRIBUTE4,
171 ATTRIBUTE5,
172 ATTRIBUTE6,
173 ATTRIBUTE7,
174 ATTRIBUTE8,
175 ATTRIBUTE9,
176 ATTRIBUTE10,
177 ATTRIBUTE11,
178 ATTRIBUTE12,
179 ATTRIBUTE13,
180 ATTRIBUTE14,
181 ATTRIBUTE15,
182 DEFAULT_DATATYPE_ID
183 from FND_DOCUMENT_CATEGORIES
184 where CATEGORY_ID = X_CATEGORY_ID
185 for update of CATEGORY_ID nowait;
186 recinfo c%rowtype;
187
188 cursor c1 is select
189 USER_NAME
190 from FND_DOCUMENT_CATEGORIES_TL
191 where CATEGORY_ID = X_CATEGORY_ID
192 and LANGUAGE = userenv('LANG')
193 for update of CATEGORY_ID nowait;
194 tlinfo c1%rowtype;
195
196 begin
197 open c;
198 fetch c into recinfo;
199 if (c%notfound) then
200 close c;
201 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
202 app_exception.raise_exception;
203 end if;
204 close c;
205 if ( (recinfo.NAME = X_NAME)
206 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
207 OR ((recinfo.application_id is null)
208 AND (X_application_id is null)))
209 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
210 OR ((recinfo.START_DATE_ACTIVE is null)
211 AND (X_START_DATE_ACTIVE is null)))
212 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
213 OR ((recinfo.END_DATE_ACTIVE is null)
214 AND (X_END_DATE_ACTIVE is null)))
215 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
216 OR ((recinfo.ATTRIBUTE_CATEGORY is null)
217 AND (X_ATTRIBUTE_CATEGORY is null)))
218 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
219 OR ((recinfo.ATTRIBUTE1 is null)
220 AND (X_ATTRIBUTE1 is null)))
221 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
222 OR ((recinfo.ATTRIBUTE2 is null)
223 AND (X_ATTRIBUTE2 is null)))
224 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
225 OR ((recinfo.ATTRIBUTE3 is null)
226 AND (X_ATTRIBUTE3 is null)))
227 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
228 OR ((recinfo.ATTRIBUTE4 is null)
229 AND (X_ATTRIBUTE4 is null)))
230 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
231 OR ((recinfo.ATTRIBUTE5 is null)
232 AND (X_ATTRIBUTE5 is null)))
233 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
234 OR ((recinfo.ATTRIBUTE6 is null)
235 AND (X_ATTRIBUTE6 is null)))
236 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
237 OR ((recinfo.ATTRIBUTE7 is null)
238 AND (X_ATTRIBUTE7 is null)))
239 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
240 OR ((recinfo.ATTRIBUTE8 is null)
241 AND (X_ATTRIBUTE8 is null)))
242 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
243 OR ((recinfo.ATTRIBUTE9 is null)
244 AND (X_ATTRIBUTE9 is null)))
245 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
246 OR ((recinfo.ATTRIBUTE10 is null)
247 AND (X_ATTRIBUTE10 is null)))
248 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
249 OR ((recinfo.ATTRIBUTE11 is null)
250 AND (X_ATTRIBUTE11 is null)))
251 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
252 OR ((recinfo.ATTRIBUTE12 is null)
253 AND (X_ATTRIBUTE12 is null)))
254 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
255 OR ((recinfo.ATTRIBUTE13 is null)
256 AND (X_ATTRIBUTE13 is null)))
257 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
258 OR ((recinfo.ATTRIBUTE14 is null)
259 AND (X_ATTRIBUTE14 is null)))
260 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
261 OR ((recinfo.ATTRIBUTE15 is null)
262 AND (X_ATTRIBUTE15 is null)))
263 AND ((recinfo.DEFAULT_DATATYPE_ID = X_DEFAULT_DATATYPE_ID)
264 OR ((recinfo.DEFAULT_DATATYPE_ID is null)
265 AND (X_DEFAULT_DATATYPE_ID is null)))
266 ) then
267 null;
268 else
269 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
270 app_exception.raise_exception;
271 end if;
272
273 open c1;
274 fetch c1 into tlinfo;
275 if (c1%notfound) then
276 close c1;
277 return;
278 end if;
279 close c1;
280
281 if (NOT( (tlinfo.USER_NAME = X_USER_NAME)
282 )) then
283 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
284 app_exception.raise_exception;
285 end if;
286 return;
287 end LOCK_ROW;
288
289 procedure UPDATE_ROW (
290 X_CATEGORY_ID in NUMBER,
291 X_APPLICATION_ID in NUMBER,
292 X_NAME in VARCHAR2,
293 X_START_DATE_ACTIVE in DATE,
294 X_END_DATE_ACTIVE in DATE,
295 X_ATTRIBUTE_CATEGORY in VARCHAR2,
296 X_ATTRIBUTE1 in VARCHAR2,
297 X_ATTRIBUTE2 in VARCHAR2,
298 X_ATTRIBUTE3 in VARCHAR2,
299 X_ATTRIBUTE4 in VARCHAR2,
300 X_ATTRIBUTE5 in VARCHAR2,
301 X_ATTRIBUTE6 in VARCHAR2,
302 X_ATTRIBUTE7 in VARCHAR2,
303 X_ATTRIBUTE8 in VARCHAR2,
304 X_ATTRIBUTE9 in VARCHAR2,
305 X_ATTRIBUTE10 in VARCHAR2,
306 X_ATTRIBUTE11 in VARCHAR2,
307 X_ATTRIBUTE12 in VARCHAR2,
308 X_ATTRIBUTE13 in VARCHAR2,
309 X_ATTRIBUTE14 in VARCHAR2,
310 X_ATTRIBUTE15 in VARCHAR2,
311 X_DEFAULT_DATATYPE_ID in NUMBER,
312 X_USER_NAME in VARCHAR2,
313 X_LAST_UPDATE_DATE in DATE,
314 X_LAST_UPDATED_BY in NUMBER,
315 X_LAST_UPDATE_LOGIN in NUMBER) is
316 begin
317 update FND_DOCUMENT_CATEGORIES set
318 CATEGORY_ID = X_CATEGORY_ID,
319 APPLICATION_ID = X_APPLICATION_ID,
320 NAME = X_NAME,
321 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
322 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
323 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
324 ATTRIBUTE1 = X_ATTRIBUTE1,
325 ATTRIBUTE2 = X_ATTRIBUTE2,
326 ATTRIBUTE3 = X_ATTRIBUTE3,
327 ATTRIBUTE4 = X_ATTRIBUTE4,
328 ATTRIBUTE5 = X_ATTRIBUTE5,
329 ATTRIBUTE6 = X_ATTRIBUTE6,
330 ATTRIBUTE7 = X_ATTRIBUTE7,
331 ATTRIBUTE8 = X_ATTRIBUTE8,
332 ATTRIBUTE9 = X_ATTRIBUTE9,
333 ATTRIBUTE10 = X_ATTRIBUTE10,
334 ATTRIBUTE11 = X_ATTRIBUTE11,
335 ATTRIBUTE12 = X_ATTRIBUTE12,
336 ATTRIBUTE13 = X_ATTRIBUTE13,
337 ATTRIBUTE14 = X_ATTRIBUTE14,
338 ATTRIBUTE15 = X_ATTRIBUTE15,
339 DEFAULT_DATATYPE_ID = X_DEFAULT_DATATYPE_ID,
340 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
341 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
342 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
343 where CATEGORY_ID = X_CATEGORY_ID;
344 if (sql%notfound) then
345 raise no_data_found;
346 end if;
347
348 update FND_DOCUMENT_CATEGORIES_TL set
349 NAME = X_NAME,
350 USER_NAME = X_USER_NAME,
351 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
352 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
353 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
354 APP_SOURCE_VERSION = '<schema><<' || USER || '>>',
355 SOURCE_LANG = userenv('LANG')
356 where CATEGORY_ID = X_CATEGORY_ID
357 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
358 if (sql%notfound) then
359 raise no_data_found;
360 end if;
361 end UPDATE_ROW;
362
363 procedure DELETE_ROW (X_CATEGORY_ID in NUMBER) is
364 begin
365 -- need to mark fnd_document_categories.app_source_version with the
366 -- USER the delete is being sourced from so that trigger logic can call
367 -- the delete stored procedure in the appropriate schema
368 -- (R10/10SC compatibility logic) WIP logic operates off tl table
369 --UPDATE fnd_document_categories_tl
370 -- SET app_source_Version = 'DEL_10SC<schema><<' || USER || '>>'
371 --WHERE category_id = X_category_id;
372
373 -- now do the delete
374 delete from FND_DOCUMENT_CATEGORIES
375 where CATEGORY_ID = X_CATEGORY_ID;
376
377 if (sql%notfound) then
378 raise no_data_found;
379 end if;
380
381 delete from FND_DOCUMENT_CATEGORIES_TL
382 where CATEGORY_ID = X_CATEGORY_ID;
383 if (sql%notfound) then
384 raise no_data_found;
385 end if;
386
387 DELETE FROM fnd_doc_category_usages
388 WHERE category_id = x_category_id;
389
390 end DELETE_ROW;
391
392 procedure ADD_LANGUAGE
393 is
394 begin
395
396 /* Mar/19/03 requested by Ric Ginsberg */
397 /* The following delete and update statements are commented out */
398 /* as a quick workaround to fix the time-consuming table handler issue */
399 /* Eventually we'll need to turn them into a separate fix_language procedure */
400 /*
401
402 delete from FND_DOCUMENT_CATEGORIES_TL T
403 where not exists
404 (select NULL
405 from FND_DOCUMENT_CATEGORIES B
406 where B.CATEGORY_ID = T.CATEGORY_ID
407 );
408
409 update FND_DOCUMENT_CATEGORIES_TL T set (
410 USER_NAME
411 ) = (select
412 B.USER_NAME
413 from FND_DOCUMENT_CATEGORIES_TL B
414 where B.CATEGORY_ID = T.CATEGORY_ID
415 and B.LANGUAGE = T.SOURCE_LANG)
416 where (
417 T.CATEGORY_ID,
418 T.LANGUAGE
419 ) in (select
420 SUBT.CATEGORY_ID,
421 SUBT.LANGUAGE
422 from FND_DOCUMENT_CATEGORIES_TL SUBB, FND_DOCUMENT_CATEGORIES_TL SUBT
423 where SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
424 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
425 and (SUBB.USER_NAME <> SUBT.USER_NAME
426 ));
427 */
428
429 insert into FND_DOCUMENT_CATEGORIES_TL (
430 CATEGORY_ID,
431 LANGUAGE,
432 NAME,
433 USER_NAME,
434 CREATION_DATE,
435 CREATED_BY,
436 LAST_UPDATE_DATE,
437 LAST_UPDATED_BY,
438 LAST_UPDATE_LOGIN,
439 SOURCE_LANG
440 ) select
441 M.CATEGORY_ID,
442 L.LANGUAGE_CODE,
443 M.NAME,
444 M.USER_NAME,
445 M.CREATION_DATE,
446 M.CREATED_BY,
447 M.LAST_UPDATE_DATE,
448 M.LAST_UPDATED_BY,
449 M.LAST_UPDATE_LOGIN,
450 M.SOURCE_LANG
451 from FND_DOCUMENT_CATEGORIES_TL M, FND_LANGUAGES B, FND_LANGUAGES L
452 where B.INSTALLED_FLAG = 'B'
453 and L.INSTALLED_FLAG in ('I', 'B')
454 and M.LANGUAGE = userenv('LANG')
455 and not exists
456 (select NULL
457 from FND_DOCUMENT_CATEGORIES_TL T
458 where T.CATEGORY_ID = M.CATEGORY_ID
459 and T.LANGUAGE = L.LANGUAGE_CODE);
460 end ADD_LANGUAGE;
461
462 procedure TRANSLATE_ROW (
463 X_CATEGORY_NAME in VARCHAR2,
464 X_USER_NAME in VARCHAR2,
468 update fnd_document_categories_tl set
465 X_OWNER in VARCHAR2) IS
466 begin
467
469 user_name = nvl(X_USER_NAME,user_name),
470 last_update_date = sysdate,
471 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
472 last_update_login = 0,
473 source_lang = userenv('LANG')
474 where name = X_CATEGORY_NAME
475 and userenv('LANG') in (language, source_lang);
476
477 end TRANSLATE_ROW;
478
479 -- Overloaded for BUG 3087292.
480
481 procedure TRANSLATE_ROW (
482 X_CATEGORY_NAME in VARCHAR2,
483 X_USER_NAME in VARCHAR2,
484 X_OWNER in VARCHAR2,
485 X_LAST_UPDATE_DATE in VARCHAR2,
486 X_CUSTOM_MODE in VARCHAR2) IS
487
488 f_luby number; -- entity owner in file
489 f_ludate date; -- entity update date in file
490 db_luby number; -- entity owner in db
491 db_ludate date; -- entity update date in db
492
493 begin
494 -- Translate owner to file_last_updated_by
495 f_luby := fnd_load_util.owner_id(x_owner);
496
497 -- Translate char last_update_date to date
498 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
499
500 select LAST_UPDATED_BY, LAST_UPDATE_DATE
501 into db_luby, db_ludate
502 from fnd_document_categories_tl
503 where name = X_CATEGORY_NAME
504 and LANGUAGE = userenv('LANG');
505
506 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
507 db_ludate, X_CUSTOM_MODE)) then
508 update fnd_document_categories_tl set
509 user_name = nvl(X_USER_NAME,user_name),
510 last_update_date = f_ludate,
511 last_updated_by = f_luby,
512 last_update_login = 0,
513 source_lang = userenv('LANG')
514 where name = X_CATEGORY_NAME
515 and userenv('LANG') in (language, source_lang);
516 end if;
517
518 end TRANSLATE_ROW;
519
520 procedure LOAD_ROW (
521 X_CATEGORY_NAME in VARCHAR2,
522 X_APP_SHORT_NAME in VARCHAR2,
523 X_OWNER in VARCHAR2,
524 X_START_DATE_ACTIVE in VARCHAR2,
525 X_END_DATE_ACTIVE in VARCHAR2,
526 X_ATTRIBUTE_CATEGORY in VARCHAR2,
527 X_ATTRIBUTE1 in VARCHAR2,
528 X_ATTRIBUTE2 in VARCHAR2,
529 X_ATTRIBUTE3 in VARCHAR2,
530 X_ATTRIBUTE4 in VARCHAR2,
531 X_ATTRIBUTE5 in VARCHAR2,
532 X_ATTRIBUTE6 in VARCHAR2,
533 X_ATTRIBUTE7 in VARCHAR2,
534 X_ATTRIBUTE8 in VARCHAR2,
535 X_ATTRIBUTE9 in VARCHAR2,
536 X_ATTRIBUTE10 in VARCHAR2,
537 X_ATTRIBUTE11 in VARCHAR2,
538 X_ATTRIBUTE12 in VARCHAR2,
539 X_ATTRIBUTE13 in VARCHAR2,
540 X_ATTRIBUTE14 in VARCHAR2,
541 X_ATTRIBUTE15 in VARCHAR2,
542 X_DEFAULT_DATATYPE_ID in VARCHAR2,
543 X_APP_SOURCE_VERSION in VARCHAR2,
544 X_USER_NAME in VARCHAR2 ) IS
545
546 l_user_id number := 0 ;
547 l_category_id number := 0 ;
548 l_application_id number := 0 ;
549 l_row_id varchar2(64);
550
551 begin
552 if (X_OWNER = 'SEED') then
553 l_user_id := 1;
554 end if;
555
556 -- Get application id from fnd_application
557 if (X_APP_SHORT_NAME IS NOT NULL) then
558 select application_id
559 into l_application_id
560 from fnd_application
561 where application_short_name = X_APP_SHORT_NAME;
562 else
563 l_application_id := NULL ;
564 end if;
565
566 begin
567 -- Get category Id from fnd_document_categories.
568 select category_id, application_id
569 into l_category_id, l_application_id
570 from fnd_document_categories
571 where name = X_CATEGORY_NAME ;
572
573 UPDATE_ROW (
574 X_CATEGORY_ID => l_category_id,
575 X_APPLICATION_ID => l_application_id,
576 X_NAME => X_CATEGORY_NAME,
577 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE, 'YYYY/MM/DD'),
578 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE, 'YYYY/MM/DD'),
579 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
580 X_ATTRIBUTE1 => X_ATTRIBUTE1,
581 X_ATTRIBUTE2 => X_ATTRIBUTE2,
582 X_ATTRIBUTE3 => X_ATTRIBUTE3,
583 X_ATTRIBUTE4 => X_ATTRIBUTE4,
584 X_ATTRIBUTE5 => X_ATTRIBUTE5,
585 X_ATTRIBUTE6 => X_ATTRIBUTE6,
586 X_ATTRIBUTE7 => X_ATTRIBUTE7,
587 X_ATTRIBUTE8 => X_ATTRIBUTE8,
588 X_ATTRIBUTE9 => X_ATTRIBUTE9,
589 X_ATTRIBUTE10 => X_ATTRIBUTE10,
590 X_ATTRIBUTE11 => X_ATTRIBUTE11,
591 X_ATTRIBUTE12 => X_ATTRIBUTE12,
592 X_ATTRIBUTE13 => X_ATTRIBUTE13,
593 X_ATTRIBUTE14 => X_ATTRIBUTE14,
594 X_ATTRIBUTE15 => X_ATTRIBUTE15,
595 X_DEFAULT_DATATYPE_ID => to_number(X_DEFAULT_DATATYPE_ID),
596 X_USER_NAME => X_USER_NAME,
597 X_LAST_UPDATE_DATE => sysdate,
598 X_LAST_UPDATED_BY => l_user_id,
599 X_LAST_UPDATE_LOGIN => 0 );
600
601 exception
602 when no_data_found then
603
604 -- Get category id from a sequence.
605 select fnd_document_categories_s.nextval
606 into l_category_id
607 from dual;
608
609 INSERT_ROW (
610 X_ROWID => l_row_id,
611 X_CATEGORY_ID => l_category_id,
612 X_APPLICATION_ID => l_application_id,
616 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
613 X_NAME => X_CATEGORY_NAME,
614 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE, 'YYYY/MM/DD'),
615 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE, 'YYYY/MM/DD'),
617 X_ATTRIBUTE1 => X_ATTRIBUTE1,
618 X_ATTRIBUTE2 => X_ATTRIBUTE2,
619 X_ATTRIBUTE3 => X_ATTRIBUTE3,
620 X_ATTRIBUTE4 => X_ATTRIBUTE4,
621 X_ATTRIBUTE5 => X_ATTRIBUTE5,
622 X_ATTRIBUTE6 => X_ATTRIBUTE6,
623 X_ATTRIBUTE7 => X_ATTRIBUTE7,
624 X_ATTRIBUTE8 => X_ATTRIBUTE8,
625 X_ATTRIBUTE9 => X_ATTRIBUTE9,
626 X_ATTRIBUTE10 => X_ATTRIBUTE10,
627 X_ATTRIBUTE11 => X_ATTRIBUTE11,
628 X_ATTRIBUTE12 => X_ATTRIBUTE12,
629 X_ATTRIBUTE13 => X_ATTRIBUTE13,
630 X_ATTRIBUTE14 => X_ATTRIBUTE14,
631 X_ATTRIBUTE15 => X_ATTRIBUTE15,
632 X_DEFAULT_DATATYPE_ID => to_number(X_DEFAULT_DATATYPE_ID),
633 X_USER_NAME => X_USER_NAME,
634 X_CREATION_DATE => sysdate,
635 X_CREATED_BY => l_user_id,
636 X_LAST_UPDATE_DATE => sysdate,
637 X_LAST_UPDATED_BY => l_user_id,
638 X_LAST_UPDATE_LOGIN => 0 );
639
640 end;
641 end LOAD_ROW;
642
643 -- Overloaded for BUG 3087292.
644
645 procedure LOAD_ROW (
646 X_CATEGORY_NAME in VARCHAR2,
647 X_APP_SHORT_NAME in VARCHAR2,
648 X_OWNER in VARCHAR2,
649 X_START_DATE_ACTIVE in VARCHAR2,
650 X_END_DATE_ACTIVE in VARCHAR2,
651 X_ATTRIBUTE_CATEGORY in VARCHAR2,
652 X_ATTRIBUTE1 in VARCHAR2,
653 X_ATTRIBUTE2 in VARCHAR2,
654 X_ATTRIBUTE3 in VARCHAR2,
655 X_ATTRIBUTE4 in VARCHAR2,
656 X_ATTRIBUTE5 in VARCHAR2,
657 X_ATTRIBUTE6 in VARCHAR2,
658 X_ATTRIBUTE7 in VARCHAR2,
659 X_ATTRIBUTE8 in VARCHAR2,
660 X_ATTRIBUTE9 in VARCHAR2,
661 X_ATTRIBUTE10 in VARCHAR2,
662 X_ATTRIBUTE11 in VARCHAR2,
663 X_ATTRIBUTE12 in VARCHAR2,
664 X_ATTRIBUTE13 in VARCHAR2,
665 X_ATTRIBUTE14 in VARCHAR2,
666 X_ATTRIBUTE15 in VARCHAR2,
667 X_DEFAULT_DATATYPE_ID in VARCHAR2,
668 X_APP_SOURCE_VERSION in VARCHAR2,
669 X_USER_NAME in VARCHAR2,
670 X_LAST_UPDATE_DATE in VARCHAR2,
671 X_CUSTOM_MODE in VARCHAR2 ) IS
672
673 l_user_id number := 0 ;
674 l_category_id number := 0 ;
675 l_application_id number := 0 ;
676 l_row_id varchar2(64);
677 f_luby number; -- entity owner in file
678 f_ludate date; -- entity update date in file
679 db_luby number; -- entity owner in db
680 db_ludate date; -- entity update date in db
681
682 begin
683 -- Translate owner to file_last_updated_by
684 f_luby := fnd_load_util.owner_id(x_owner);
685
686 -- Translate char last_update_date to date
687 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
688
689 -- Get application id from fnd_application
690 if (X_APP_SHORT_NAME IS NOT NULL) then
691 select application_id
692 into l_application_id
693 from fnd_application
694 where application_short_name = X_APP_SHORT_NAME;
695 else
696 l_application_id := NULL ;
697 end if;
698
699 begin
700 -- Get category Id from fnd_document_categories.
701 select category_id, application_id, LAST_UPDATED_BY, LAST_UPDATE_DATE
702 into l_category_id, l_application_id, db_luby, db_ludate
703 from fnd_document_categories
704 where name = X_CATEGORY_NAME ;
705
706 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
707 db_ludate, X_CUSTOM_MODE)) then
708 UPDATE_ROW (
709 X_CATEGORY_ID => l_category_id,
710 X_APPLICATION_ID => l_application_id,
711 X_NAME => X_CATEGORY_NAME,
712 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE, 'YYYY/MM/DD'),
713 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE, 'YYYY/MM/DD'),
714 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
715 X_ATTRIBUTE1 => X_ATTRIBUTE1,
716 X_ATTRIBUTE2 => X_ATTRIBUTE2,
717 X_ATTRIBUTE3 => X_ATTRIBUTE3,
718 X_ATTRIBUTE4 => X_ATTRIBUTE4,
719 X_ATTRIBUTE5 => X_ATTRIBUTE5,
720 X_ATTRIBUTE6 => X_ATTRIBUTE6,
721 X_ATTRIBUTE7 => X_ATTRIBUTE7,
722 X_ATTRIBUTE8 => X_ATTRIBUTE8,
723 X_ATTRIBUTE9 => X_ATTRIBUTE9,
724 X_ATTRIBUTE10 => X_ATTRIBUTE10,
725 X_ATTRIBUTE11 => X_ATTRIBUTE11,
726 X_ATTRIBUTE12 => X_ATTRIBUTE12,
727 X_ATTRIBUTE13 => X_ATTRIBUTE13,
728 X_ATTRIBUTE14 => X_ATTRIBUTE14,
729 X_ATTRIBUTE15 => X_ATTRIBUTE15,
730 X_DEFAULT_DATATYPE_ID => to_number(X_DEFAULT_DATATYPE_ID),
731 X_USER_NAME => X_USER_NAME,
732 X_LAST_UPDATE_DATE => f_ludate,
733 X_LAST_UPDATED_BY => f_luby,
734 X_LAST_UPDATE_LOGIN => 0 );
735 end if;
736
737 exception
738 when no_data_found then
739
740 -- Get category id from a sequence.
741 select fnd_document_categories_s.nextval
742 into l_category_id
743 from dual;
744
745 INSERT_ROW (
746 X_ROWID => l_row_id,
747 X_CATEGORY_ID => l_category_id,
748 X_APPLICATION_ID => l_application_id,
749 X_NAME => X_CATEGORY_NAME,
753 X_ATTRIBUTE1 => X_ATTRIBUTE1,
750 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE, 'YYYY/MM/DD'),
751 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE, 'YYYY/MM/DD'),
752 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
754 X_ATTRIBUTE2 => X_ATTRIBUTE2,
755 X_ATTRIBUTE3 => X_ATTRIBUTE3,
756 X_ATTRIBUTE4 => X_ATTRIBUTE4,
757 X_ATTRIBUTE5 => X_ATTRIBUTE5,
758 X_ATTRIBUTE6 => X_ATTRIBUTE6,
759 X_ATTRIBUTE7 => X_ATTRIBUTE7,
760 X_ATTRIBUTE8 => X_ATTRIBUTE8,
761 X_ATTRIBUTE9 => X_ATTRIBUTE9,
762 X_ATTRIBUTE10 => X_ATTRIBUTE10,
763 X_ATTRIBUTE11 => X_ATTRIBUTE11,
764 X_ATTRIBUTE12 => X_ATTRIBUTE12,
765 X_ATTRIBUTE13 => X_ATTRIBUTE13,
766 X_ATTRIBUTE14 => X_ATTRIBUTE14,
767 X_ATTRIBUTE15 => X_ATTRIBUTE15,
768 X_DEFAULT_DATATYPE_ID => to_number(X_DEFAULT_DATATYPE_ID),
769 X_USER_NAME => X_USER_NAME,
770 X_CREATION_DATE => f_ludate,
771 X_CREATED_BY => f_luby,
772 X_LAST_UPDATE_DATE => f_ludate,
773 X_LAST_UPDATED_BY => f_luby,
774 X_LAST_UPDATE_LOGIN => 0 );
775
776 end;
777 end LOAD_ROW;
778
779 end FND_DOC_CATEGORIES_PKG;