[Home] [Help]
PACKAGE BODY: APPS.IBC_UTILITIES_PVT
Source
1 PACKAGE BODY Ibc_Utilities_Pvt AS
2 /* $Header: ibcvutlb.pls 120.5 2006/01/20 20:53:33 apulijal ship $ */
3
4 -- ---------------------------------------------------
5 -- ----------- PACKAGE VARIABLES ---------------------
6 -- ---------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30):='IBC_Utilities_Pvt';
8 G_FILE_NAME CONSTANT VARCHAR2(12):='ibcvutlb.pls';
9
10 G_APPL_ID NUMBER := Fnd_Global.Prog_Appl_Id;
11 G_LOGIN_ID NUMBER := Fnd_Global.Conc_Login_Id;
12 G_PROGRAM_ID NUMBER := Fnd_Global.Conc_Program_Id;
13 G_USER_ID NUMBER := Fnd_Global.User_Id;
14 G_REQUEST_ID NUMBER := Fnd_Global.Conc_Request_Id;
15
16
17 /****************************************************
18 -------------FUNCTIONS--------------------------------------------------------------------------
19 ****************************************************/
20
21 FUNCTION getEncoding
22 RETURN VARCHAR2
23 IS
24 CURSOR c_nls IS
25 select value
26 from nls_database_parameters
27 where parameter = 'NLS_CHARACTERSET';
28
29 l_tmp nls_database_parameters.value%TYPE;
30
31 l_encoding VARCHAR2(100);
32
33 BEGIN
34
35 fnd_profile.GET('ICX_CLIENT_IANA_ENCODING', l_encoding );
36
37 RETURN l_encoding;
38
39 END getEncoding;
40
41 FUNCTION IBC_DECODE(l_base_date DATE, comp1 DATE, date1 DATE, date2 DATE)
42 RETURN DATE
43 IS
44 BEGIN
45 IF l_base_date = comp1 THEN
46 RETURN date1;
47 ELSE
48 RETURN date2;
49 END IF;
50 END IBC_DECODE;
51
52 -- --------------------------------------------------------------
53 -- get_citem_name
54 --
55 -- Given content_item_id it returns content item name of
56 -- the last version for the current language
57 --
58 -- --------------------------------------------------------------
59 FUNCTION get_citem_name(p_content_item_id IN NUMBER)
60 RETURN VARCHAR2
61 IS
62 CURSOR c_name(p_content_item_id NUMBER) IS
63 SELECT content_item_name
64 FROM ibc_citem_versions_tl
65 WHERE citem_version_id = (SELECT citem_version_id
66 FROM ibc_citem_versions_b civb
67 WHERE content_item_id = p_content_item_id
68 AND version_number = (SELECT MAX(version_number)
69 FROM ibc_citem_versions_b civb2
70 WHERE civb2.content_item_id = civb.content_item_id)
71 )
72 AND language = USERENV('lang');
73 l_citem_name IBC_CITEM_VERSIONS_TL.content_item_name%TYPE;
74 BEGIN
75 OPEN c_name(p_content_item_id);
76 FETCH c_name INTO l_citem_name;
77 IF c_name%NOTFOUND THEN
78 l_citem_name := NULL;
79 END IF;
80 CLOSE c_name;
81 RETURN l_citem_name;
82 END get_citem_name;
83
84 -- --------------------------------------------------------------
85 -- get_directory_name
86 --
87 -- Given directory_node_id it returns directory name
88 -- for the current language
89 --
90 -- --------------------------------------------------------------
91 FUNCTION get_directory_name(p_directory_node_id IN NUMBER)
92 RETURN VARCHAR2
93 IS
94
95 CURSOR c_name(p_directory_node_id NUMBER) IS
96 SELECT directory_node_code
97 FROM ibc_directory_nodes_b
98 WHERE directory_node_id = p_directory_node_id;
99
100 l_directory_name IBC_DIRECTORY_NODES_B.directory_node_code%TYPE;
101
102 BEGIN
103
104 OPEN c_name(p_directory_node_id);
105 FETCH c_name INTO l_directory_name;
106 IF c_name%NOTFOUND THEN
107 l_directory_name := NULL;
108 END IF;
109 CLOSE c_name;
110
111 RETURN l_directory_name;
112
113 END get_directory_name;
114
115 -- --------------------------------------------------------------
116 -- GET RESOURCE NAME
117 --
118 -- Used to get resource name by id
119 --
120 -- --------------------------------------------------------------
121 FUNCTION getResourceName(
122 f_resource_id IN NUMBER
123 ,f_resource_type IN VARCHAR2
124 )
125 RETURN VARCHAR2
126 IS
127 -- For performance issues, assuming all resource_types
128 -- to be GROUPS, as they are the only one supported for
129 -- 11.5.10 version
130 CURSOR c_rn IS
131 SELECT
132 group_name resource_name
133 FROM
134 jtf_rs_groups_vl
135 WHERE
136 group_id = f_resource_id;
137
138 temp JTF_RS_RESOURCE_EXTNS_TL.resource_name%TYPE;
139 BEGIN
140 OPEN c_rn;
141 FETCH c_rn INTO temp;
142
143 IF (c_rn%NOTFOUND) THEN
144 CLOSE c_rn;
145 RETURN NULL;
146 ELSE
147 CLOSE c_rn;
148 RETURN temp;
149 END IF;
150 END;
151
152 -- --------------------------------------------------------------
153 -- GET DIRECTORY ID
154 --
155 -- Get Directory ID given a Directory Path and node type
156 --
157 -- --------------------------------------------------------------
158 FUNCTION get_directory_node_id(p_directory_path IN VARCHAR2,
159 p_node_type IN VARCHAR2)
160 RETURN VARCHAR2
161 IS
162 CURSOR c_dirid IS
163 SELECT directory_node_id
164 FROM ibc_directory_nodes_b
165 WHERE directory_path = p_directory_path
166 AND node_type = p_node_type;
167
168 l_dirid NUMBER;
169 BEGIN
170 OPEN c_dirid;
171 FETCH c_dirid INTO l_dirid;
172
173 IF (c_dirid%NOTFOUND) THEN
174 l_dirid := NULL;
175 END IF;
176
177 CLOSE c_dirid;
178
179 RETURN l_dirid;
180
181 END;
182
183 -- --------------------------------------------------------------
184 -- GET Content Item Keywords
185 --
186 -- Used to get content item keywords by content_item_id
187 --
188 -- --------------------------------------------------------------
189 FUNCTION getCItemKeywords
190 (
191 pcItemId IN NUMBER
192 )
193 RETURN VARCHAR2
194 IS
195 CURSOR c_kw IS
196 SELECT
197 keyword
198 FROM
199 ibc_citem_keywords
200 WHERE
201 content_item_id=pcItemId;
202
203 x_keywords VARCHAR2(4000);
204 l_keyword VARCHAR2(100);
205 l_index NUMBER :=0;
206 BEGIN
207 OPEN c_kw;
208 LOOP
209 FETCH c_kw INTO l_keyword;
210
211 EXIT WHEN c_kw%NOTFOUND;
212 IF (l_index=0) THEN
213 x_keywords := l_keyword;
214 ELSE
215 x_keywords := x_keywords||','||l_keyword;
216 END IF;
217 l_index :=l_index+1;
218 END LOOP;
219 CLOSE c_kw;
220 RETURN x_keywords;
221 END;
222
223
224
225 /****************************************************
226 -------------PROCEDURES--------------------------------------------------------------------------
227 ****************************************************/
228 --------------------------------------------------------------------------------
229 -- Start of comments
230 -- API name : get_Language_Description
231 -- Type : Private
232 -- Pre-reqs : None
233 -- Description : This procedure takes in the language code and returns the
234 -- corresponding language description
235 -- Parameters :
236 -- p_language_code IN VARCHAR2
237 -- p_language_description OUT NOCOPY VARCHAR2
238 --------------------------------------------------------------------------------
239 PROCEDURE Get_Language_Description (p_language_code IN VARCHAR2
240 ,p_language_description OUT NOCOPY VARCHAR2
241 ) IS
242 -- Cursor : c_role_detail
243 CURSOR c_language (cv_language_code IN VARCHAR2) IS
244 SELECT description
245 FROM fnd_languages_vl
246 WHERE language_code = cv_language_code;
247 BEGIN
248 -- Get the role detail
249 OPEN c_language(p_language_code);
250 FETCH c_language INTO p_language_description;
251 CLOSE c_language;
252
253 EXCEPTION
254
255 WHEN OTHERS THEN
256 RAISE;
257
258 END Get_Language_Description;
259
260
261
262 --------------------------------------------------------------------------------
263 -- Start of comments
264 -- API name : Build_Attribute_Bundle
265 -- Type : Private
266 -- Pre-reqs : None
267 -- Function : Concatenate the user-defined attributes of IBC output xml to the
268 -- incoming CLOB.
269 -- Parameters :
270 -- IN : p_file_id IN NUMBER
271 -- p_xml_clob_loc IN OUT NOCOPY CLOB
272 --------------------------------------------------------------------------------
273 PROCEDURE Build_Attribute_Bundle (
274 p_file_id IN NUMBER,
275 p_xml_clob_loc IN OUT NOCOPY CLOB
276 ) AS
277 xmlBlob_loc CLOB;
278 BEGIN
279 IF (p_file_id IS NOT NULL) THEN
280 SELECT attribute_bundle_data
281 INTO xmlBlob_loc
282 FROM IBC_ATTRIBUTE_BUNDLES
283 WHERE attribute_bundle_id = p_file_id;
284
285 DBMS_LOB.APPEND(dest_lob => p_xml_clob_loc, src_lob => xmlBlob_loc);
286 END IF;
287
288 -- If Content Item does not have user-defined primitive
289 -- attributes, do nothing.
290
291 END Build_Attribute_Bundle;
292
293
294
295
296 PROCEDURE Build_Citem_Open_Tag (
297 p_content_type_code IN VARCHAR2,
298 p_content_item_id IN NUMBER,
299 p_version_number IN NUMBER,
300 p_item_reference_code IN VARCHAR2,
301 p_item_label IN VARCHAR2,
302 p_xml_clob_loc IN OUT NOCOPY CLOB
303 ) AS
304 l_buffer VARCHAR2(2000);
305 l_item_reference_code VARCHAR2(100) := '';
306 l_item_label VARCHAR2(120) := '';
307 BEGIN
308 IF (p_item_reference_code IS NOT NULL) THEN
309 l_item_reference_code := p_item_reference_code;
310 END IF;
311 IF (p_item_label IS NOT NULL) THEN
312 l_item_label := Fnd_Global.local_chr(38) || 'amp;label=' || p_item_label;
313 END IF;
314
315 l_buffer := '<' || p_content_type_code || ' datatype="citem" ' ||
316 G_XML_ID || '="' || p_content_item_id || '" ' ||
317 G_XML_VERSION || '="' || p_version_number || '" ' ||
318 G_XML_IRCODE || '="' || l_item_reference_code || '" ' ||
319 G_XML_REF || '="f" ' ||
320 G_XML_URL || '="' || Ibc_Utilities_Pub.G_CITEM_SERVLET_URL || 'cItemId=' || p_content_item_id || l_item_label || '">';
321
322 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_buffer), l_buffer);
323
324 END Build_Citem_Open_Tag;
325
326
327
328
329 --------------------------------------------------------------------------------
330 -- Start of comments
331 -- API name : Build_Citem_Open_Tags
332 -- Type : Private
333 -- Pre-reqs : None
334 -- Function : Building content item xml open tags.
335 --------------------------------------------------------------------------------
336 PROCEDURE Build_Citem_Open_Tags (
337 p_content_type_code IN VARCHAR2,
338 p_content_item_id IN NUMBER,
339 p_citem_version_id IN NUMBER,
340 p_item_label IN VARCHAR2,
341 p_lang_code IN VARCHAR2,
342 p_version_number IN NUMBER,
343 p_start_date IN DATE,
344 p_end_date IN DATE,
345 p_item_reference_code IN VARCHAR2,
346 p_encrypt_flag IN VARCHAR2,
347 p_content_item_name IN VARCHAR2,
348 p_description IN VARCHAR2,
349 p_attachment_attribute_code IN VARCHAR2,
350 p_attachment_file_id IN NUMBER,
351 p_attachment_file_name IN VARCHAR2,
352 p_default_mime_type IN VARCHAR2,
353 p_is_preview IN VARCHAR2,
354 p_xml_clob_loc IN OUT NOCOPY CLOB
355 ) AS
356 l_buffer VARCHAR2(9000);
357 l_item_reference_code IBC_CONTENT_ITEMS.ITEM_REFERENCE_CODE%TYPE := '';
358 l_item_label VARCHAR2(120) := '';
359
360 l_rendition_counter NUMBER := 1;
361 l_mime_type IBC_RENDITIONS.MIME_TYPE%TYPE;
362 l_rendition_name FND_LOOKUP_VALUES.DESCRIPTION%TYPE;
363 --
364 --// p_lang_code has taken translation approval into account
365 CURSOR Get_Renditions IS
366 SELECT file_id, file_name, mime_type, language
367 FROM IBC_RENDITIONS
368 WHERE CITEM_VERSION_ID = p_citem_version_id
369 AND LANGUAGE = NVL(p_lang_code, USERENV('LANG'));
370
371 --// p_lang_code has taken translation approval into account
372 CURSOR Get_Rendition_Name IS
373 SELECT NVL(DESCRIPTION, MEANING)
374 FROM FND_LOOKUP_VALUES
375 WHERE LOOKUP_TYPE = Ibc_Utilities_Pvt.G_REND_LOOKUP_TYPE
376 AND LANGUAGE = NVL(p_lang_code, USERENV('LANG'))
377 AND LOOKUP_CODE = l_mime_type;
378 BEGIN
379 IF (p_item_reference_code IS NOT NULL) THEN
380 l_item_reference_code := p_item_reference_code;
381 END IF;
382 IF (p_item_label IS NOT NULL) THEN
383 l_item_label := Fnd_Global.local_chr(38) || 'amp;'|| G_XML_URL_LB ||'=' || p_item_label;
384 END IF;
385
386 -- // Append Open tag
387 l_buffer := '<' || p_content_type_code || ' datatype="citem" ' ||
388 G_XML_ID || '="' || p_content_item_id || '" ' ||
389 G_XML_VERSION || '="' || p_version_number || '" ' ||
390 G_XML_AVAIL || '="' || TO_CHAR(p_start_date, 'yyyy-mm-dd') || '" ' ||
391 G_XML_EXPIRE || '="' || TO_CHAR(p_end_date, 'yyyy-mm-dd') || '" ' ||
392 G_XML_IRCODE || '="' || l_item_reference_code || '" ' ||
393 G_XML_REF || '="f" ' ||
394 G_XML_ENC || '="' || LOWER(p_encrypt_flag) || '" ';
395 -- // Preview Url
396 IF (p_is_preview = FND_API.g_true) THEN
397 l_buffer := l_buffer ||
398 G_XML_URL || '="' || Ibc_Utilities_Pub.G_PCITEM_SERVLET_URL || 'cItemId=' || p_content_item_id
399 || Fnd_Global.local_chr(38) || 'amp;cItemVerId=' || p_citem_version_id ||
400 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_ENC ||'='|| LOWER(p_encrypt_flag) ||
401 '">';
402 -- // Runtime Url
403 ELSE
404 l_buffer := l_buffer ||
405 G_XML_URL || '="' || Ibc_Utilities_Pub.G_CITEM_SERVLET_URL || G_XML_URL_CID ||'='|| p_content_item_id ||
406 l_item_label ||
407 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_ENC ||'='|| LOWER(p_encrypt_flag) ||
408 '">';
409 END IF;
410
411 -- // Rendition tags
412 FOR rendition_rec IN Get_Renditions LOOP
413 l_mime_type := rendition_rec.mime_type;
414 OPEN Get_Rendition_Name;
415 FETCH Get_Rendition_Name INTO l_rendition_name;
416 IF Get_Rendition_Name%NOTFOUND THEN
417 CLOSE Get_Rendition_Name;
418 l_mime_type := Ibc_Utilities_Pvt.G_REND_UNKNOWN_MIME;
419 OPEN Get_Rendition_Name;
420 FETCH Get_Rendition_Name INTO l_rendition_name;
421 CLOSE Get_Rendition_Name;
422 ELSE
423 CLOSE Get_Rendition_Name;
424 END IF;
425
426 l_buffer := l_buffer ||
427 '<' || Ibc_Utilities_Pub.G_XML_REND_TAG || ' datatype="rendition" ' ||
428 G_XML_ID || '="' || rendition_rec.file_id || '" ' ||
429 G_XML_REF || '="t" ' ||
430 G_XML_FILE || '="' || Replace_Special_Chars(rendition_rec.file_name) || '" ' ||
431 G_XML_MIME || '="' || LOWER(rendition_rec.mime_type) || '" ' ||
432 G_XML_REND || '="' || l_rendition_name || '" ';
433 IF (p_default_mime_type = rendition_rec.mime_type) THEN
434 l_buffer := l_buffer || G_XML_DEFAULT_MIME || '="t" ';
435 END IF;
436 l_buffer := l_buffer ||
437 -- G_XML_URL || '="' || Ibc_Utilities_Pub.G_SERVLET_URL || G_XML_URL_CID ||'='|| p_content_item_id ||
438 -- Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_FID ||'='|| rendition_rec.file_id ||
439 -- Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_ENC ||'='|| LOWER(p_encrypt_flag) ||
440 -- '" />';
441
442
443 G_XML_URL || '="' || Ibc_Utilities_Pub.G_RENDITION_SERVLET_URL || G_XML_URL_CVERID ||'='|| p_citem_version_id ||
444 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_LANG ||'='|| rendition_rec.language ||
445 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_MIME ||'='|| rendition_rec.mime_type ||
446 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_ENC ||'='|| LOWER(p_encrypt_flag) ||
447 '" />';
448
449
450 -- check if the concatenated string is going over the buffer size
451 IF (l_rendition_counter = 5) THEN
452 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_buffer), l_buffer);
453 l_buffer := '';
454 l_rendition_counter := 1;
455 ELSE
456 l_rendition_counter := l_rendition_counter + 1;
457 END IF;
458 END LOOP;
459
460 -- // Attachment Tag
461 IF (p_attachment_file_id IS NOT NULL) THEN
462 l_buffer := l_buffer ||
463 '<' || p_attachment_attribute_code || ' datatype="attachment" ' ||
464 G_XML_ID || '="' || p_attachment_file_id || '" ' ||
465 G_XML_REF || '="t" ' ||
466 G_XML_FILE || '="' || p_attachment_file_name || '" ' ||
467 G_XML_URL || '="' || Ibc_Utilities_Pub.G_SERVLET_URL || G_XML_URL_CID ||'='|| p_content_item_id ||
468 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_FID ||'='|| p_attachment_file_id ||
469 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_ENC ||'='|| LOWER(p_encrypt_flag) ||
470 '" />';
471 END IF;
472
473 -- // Name and Description tag
474 l_buffer := l_buffer ||
475 '<NAME datatype="string"><![CDATA[' || p_content_item_name || ']]></NAME>' ||
476 '<DESCRIPTION datatype="string"><![CDATA[' || p_description || ']]></DESCRIPTION>';
477
478 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_buffer), l_buffer);
479
480 END Build_Citem_Open_Tags;
481
482
483
484
485 --------------------------------------------------------------------------------
486 -- Start of comments
487 -- API name : Build_Citem_Open_Tags
488 -- Function : This is for BACKWARD COMPATIBILITY for Admin Usage (Similar to above).
489 --------------------------------------------------------------------------------
490 PROCEDURE Build_Citem_Open_Tags (
491 p_content_type_code IN VARCHAR2,
492 p_content_item_id IN NUMBER,
493 p_version_number IN NUMBER,
494 p_item_reference_code IN VARCHAR2,
495 p_content_item_name IN VARCHAR2,
496 p_description IN VARCHAR2,
497 p_root_tag_only_flag IN VARCHAR2,
498 p_xml_clob_loc IN OUT NOCOPY CLOB
499 ) AS
500 l_buffer VARCHAR2(5000);
501 l_item_reference_code VARCHAR2(100) := '';
502 BEGIN
503 IF (p_item_reference_code IS NOT NULL) THEN
504 l_item_reference_code := p_item_reference_code;
505 END IF;
506
507 l_buffer := '<' || p_content_type_code || ' datatype="citem" ' ||
508 G_XML_ID || '="' || p_content_item_id || '" ' ||
509 G_XML_VERSION || '="' || p_version_number || '" ' ||
510 G_XML_IRCODE || '="' || l_item_reference_code || '" ' ||
511 G_XML_REF || '="f" ' ||
512 '>';
513
514 -- Include Name, Description and Attachment tags
515 IF (p_root_tag_only_flag = Fnd_Api.G_FALSE) THEN
516 l_buffer := l_buffer ||
517 '<NAME datatype="string"><![CDATA[' ||
518 p_content_item_name ||
519 ']]></NAME>' ||
520 '<DESCRIPTION datatype="string"><![CDATA[' ||
521 p_description ||
522 ']]></DESCRIPTION>';
523 END IF;
524
525 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_buffer), l_buffer);
526
527 END Build_Citem_Open_Tags;
528
529
530
531
532
533
534
535
536 PROCEDURE Build_Close_Tag (
537 p_close_tag IN VARCHAR2,
538 p_xml_clob_loc IN OUT NOCOPY CLOB
539 ) AS
540 l_buffer VARCHAR2(250);
541 BEGIN
542 l_buffer := '</' || p_close_tag || '>';
543
544 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_buffer), l_buffer);
545
546 END Build_Close_Tag;
547
548
549
550
551
552
553
554
555 PROCEDURE Build_Compound_Item_Open_Tag (
556 p_attribute_type_code IN VARCHAR2,
557 p_content_item_id IN NUMBER,
558 p_item_label IN VARCHAR2,
559 p_encrypt_flag IN VARCHAR2,
560 p_xml_clob_loc IN OUT NOCOPY CLOB
561 ) AS
562 l_buffer VARCHAR2(300);
563 l_item_label VARCHAR2(100) := '';
564 BEGIN
565 IF (p_item_label IS NOT NULL) THEN
566 l_item_label := Fnd_Global.local_chr(38) || 'amp;label=' || p_item_label;
567 END IF;
568
569 l_buffer := '<' || p_attribute_type_code || ' datatype="component" ' ||
570 G_XML_ID || '="' || p_content_item_id || '" ' ||
571 G_XML_REF || '="f" ' ||
572 G_XML_URL || '="' || Ibc_Utilities_Pub.G_CITEM_SERVLET_URL || G_XML_URL_CID ||'='|| p_content_item_id ||
573 l_item_label ||
574 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_ENC ||'='|| LOWER(p_encrypt_flag) ||
575 '">';
576
577 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_buffer), l_buffer);
578
579 END Build_Compound_Item_Open_Tag;
580
581
582 PROCEDURE Build_Preview_Cpnt_Open_Tag (
583 p_attribute_type_code IN VARCHAR2,
584 p_content_item_id IN NUMBER,
585 p_content_item_version_id IN NUMBER,
586 p_encrypt_flag IN VARCHAR2,
587 p_xml_clob_loc IN OUT NOCOPY CLOB
588 ) AS
589 l_buffer VARCHAR2(300);
590 BEGIN
591 l_buffer := '<' || p_attribute_type_code || ' datatype="component" ' ||
592 G_XML_ID || '="' || p_content_item_id || '" ' ||
593 G_XML_REF || '="f" ' ||
594 G_XML_URL || '="' || Ibc_Utilities_Pub.G_PCITEM_SERVLET_URL || G_XML_URL_CID ||'='|| p_content_item_id ||
595 Fnd_Global.local_chr(38) || 'amp;cItemVerId=' || p_content_item_version_id ||
596 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_ENC ||'='|| LOWER(p_encrypt_flag) ||
597 '">';
598
599 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_buffer), l_buffer);
600
601 END Build_Preview_Cpnt_Open_Tag;
602
603
604
605
606
607
608
609 PROCEDURE Build_Compound_Item_References (
610 p_citem_version_id IN NUMBER,
611 p_item_label IN VARCHAR2,
612 p_xml_clob_loc IN OUT NOCOPY CLOB
613 ) AS
614 l_total_buffer VARCHAR2(2500) := '';
615 l_buffer VARCHAR2(250);
616 l_compound_count NUMBER := 1;
617 l_item_label VARCHAR2(100) := '';
618 --
619 CURSOR Get_Compound_Item_Ref IS
620 SELECT r.ATTRIBUTE_TYPE_CODE, r.CONTENT_ITEM_ID, c.ENCRYPT_FLAG
621 FROM IBC_COMPOUND_RELATIONS r, IBC_CONTENT_ITEMS c
622 WHERE r.CITEM_VERSION_ID = p_citem_version_id
623 AND r.CONTENT_ITEM_ID = c.CONTENT_ITEM_ID
624 ORDER BY r.SORT_ORDER;
625 BEGIN
626 IF (p_item_label IS NOT NULL) THEN
627 l_item_label := Fnd_Global.local_chr(38) || 'amp;label=' || p_item_label;
628 END IF;
629
630 FOR compound_item_rec IN Get_Compound_Item_Ref LOOP
631 l_buffer := '<' || compound_item_rec.attribute_type_code || ' datatype="component" ' ||
632 G_XML_ID || '="' || compound_item_rec.content_item_id || '" ' ||
633 G_XML_REF || '="t" ' ||
634 G_XML_URL || '="' || Ibc_Utilities_Pub.G_CITEM_SERVLET_URL || G_XML_URL_CID ||'='|| compound_item_rec.content_item_id ||
635 l_item_label ||
636 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_ENC ||'='|| LOWER(compound_item_rec.encrypt_flag) ||
637 '" />';
638
639 l_total_buffer := l_total_buffer || l_buffer;
640
641 IF (l_compound_count = 10) THEN
642 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_total_buffer), l_total_buffer);
643 l_total_buffer := '';
644 l_compound_count := 1;
645 END IF;
646
647 l_compound_count := l_compound_count + 1;
648 END LOOP;
649 IF (l_compound_count > 1) THEN
650 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_total_buffer), l_total_buffer);
651 END IF;
652
653 END Build_Compound_Item_References;
654
655
656 PROCEDURE Build_Preview_Cpnt_References (
657 p_citem_version_id IN NUMBER,
658 p_xml_clob_loc IN OUT NOCOPY CLOB
659 ) AS
660 l_total_buffer VARCHAR2(2500) := '';
661 l_buffer VARCHAR2(250);
662 l_compound_count NUMBER := 1;
663 l_component_item_id NUMBER;
664 l_component_version_id NUMBER;
665 --
666 CURSOR Get_Compound_Item_Ref IS
667 SELECT r.ATTRIBUTE_TYPE_CODE, r.CONTENT_ITEM_ID, c.ENCRYPT_FLAG
668 FROM IBC_COMPOUND_RELATIONS r, IBC_CONTENT_ITEMS c
669 WHERE r.CITEM_VERSION_ID = p_citem_version_id
670 AND r.CONTENT_ITEM_ID = c.CONTENT_ITEM_ID
671 ORDER BY r.SORT_ORDER;
672
673 CURSOR Get_Cpnt_Latest_Version IS
674 SELECT CITEM_VERSION_ID
675 FROM IBC_CITEM_VERSIONS_B
676 WHERE CONTENT_ITEM_ID = l_component_item_id
677 AND VERSION_NUMBER = (SELECT MAX(VERSION_NUMBER)
678 FROM IBC_CITEM_VERSIONS_B
679 WHERE CONTENT_ITEM_ID = l_component_item_id);
680 BEGIN
681 -- Loop through each component item
682 FOR compound_item_rec IN Get_Compound_Item_Ref LOOP
683 l_component_item_id := compound_item_rec.content_item_id;
684 -- Get the version id of the latest component item version
685 OPEN Get_Cpnt_Latest_Version;
686 FETCH Get_Cpnt_Latest_Version INTO l_component_version_id;
687 CLOSE Get_Cpnt_Latest_Version;
688
689 l_buffer := '<' || compound_item_rec.attribute_type_code || ' datatype="component" ' ||
690 G_XML_ID || '="' || l_component_item_id || '" ' ||
691 G_XML_REF || '="t" ' ||
692 G_XML_URL || '="' || Ibc_Utilities_Pub.G_PCITEM_SERVLET_URL || G_XML_URL_CID ||'='|| l_component_item_id ||
693 Fnd_Global.local_chr(38) || 'amp;cItemVerId=' || l_component_version_id ||
694 Fnd_Global.local_chr(38) ||'amp;'|| G_XML_URL_ENC ||'='|| LOWER(compound_item_rec.encrypt_flag) ||
695 '" />';
696 l_total_buffer := l_total_buffer || l_buffer;
697
698 IF (l_compound_count = 10) THEN
699 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_total_buffer), l_total_buffer);
700 l_total_buffer := '';
701 l_compound_count := 1;
702 END IF;
703
704 l_compound_count := l_compound_count + 1;
705 END LOOP;
706 IF (l_compound_count > 1) THEN
707 DBMS_LOB.WRITEAPPEND(p_xml_clob_loc, LENGTH(l_total_buffer), l_total_buffer);
708 END IF;
709
710 END Build_Preview_Cpnt_References;
711
712
713
714
715
716
717
718
719
720
721 PROCEDURE Get_Messages (
722 p_message_count IN NUMBER,
723 x_msgs OUT NOCOPY VARCHAR2)
724 IS
725 l_msg_list VARCHAR2(2000) := '
726 ';
727 l_temp_msg VARCHAR2(2000);
728 l_appl_short_name VARCHAR2(20) ;
729 l_message_name VARCHAR2(30) ;
730
731 l_id NUMBER;
732 l_message_num NUMBER;
733
734 l_msg_count NUMBER;
735 l_msg_data VARCHAR2(2000);
736
737 CURSOR Get_Appl_Id (x_short_name VARCHAR2) IS
738 SELECT application_id
739 FROM fnd_application_vl
740 WHERE application_short_name = x_short_name;
741
742 CURSOR Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
743 SELECT msg.message_number
744 FROM fnd_new_messages msg, fnd_languages_vl lng
745 WHERE msg.message_name = x_msg
746 AND msg.application_id = x_id
747 AND lng.LANGUAGE_CODE = msg.language_code
748 AND lng.language_id = x_lang_id;
749 BEGIN
750 FOR l_count IN 1..NVL(p_message_count,0) LOOP
751 l_temp_msg := Fnd_Msg_Pub.get(Fnd_Msg_Pub.g_next, Fnd_Api.g_true);
752 Fnd_Message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
753 OPEN Get_Appl_Id (l_appl_short_name);
754 FETCH Get_Appl_Id INTO l_id;
755 CLOSE Get_Appl_Id;
756
757 l_message_num := NULL;
758 IF l_id IS NOT NULL
759 THEN
760 OPEN Get_Message_Num (l_message_name, l_id,
761 TO_NUMBER(NVL(Fnd_Profile.Value('LANGUAGE'), '0')));
762 FETCH Get_Message_Num INTO l_message_num;
763 CLOSE Get_Message_Num;
764 END IF;
765
766 l_temp_msg := Fnd_Msg_Pub.get(Fnd_Msg_Pub.g_previous, Fnd_Api.g_true);
767
768 IF NVL(l_message_num, 0) <> 0
769 THEN
770 l_temp_msg := 'APP-' || TO_CHAR(l_message_num) || ': ';
771 ELSE
772 l_temp_msg := NULL;
773 END IF;
774
775 IF l_count = 1
776 THEN
777 l_msg_list := l_msg_list || l_temp_msg ||
778 Fnd_Msg_Pub.get(Fnd_Msg_Pub.g_first, Fnd_Api.g_false);
779 ELSE
780 l_msg_list := l_msg_list || l_temp_msg ||
781 Fnd_Msg_Pub.get(Fnd_Msg_Pub.g_next, Fnd_Api.g_false);
782 END IF;
783
784 l_msg_list := l_msg_list || '
785 ';
786 EXIT WHEN LENGTH(l_msg_list) > 2000;
787 END LOOP;
788
789 x_msgs := SUBSTR(l_msg_list, 0, 2000);
790
791 END Get_Messages;
792
793
794
795
796
797
798
799
800
801
802
803
804 PROCEDURE Handle_Exceptions(
805 P_API_NAME IN VARCHAR2,
806 P_PKG_NAME IN VARCHAR2,
807 P_EXCEPTION_LEVEL IN NUMBER,
808 P_SQLCODE IN NUMBER,
809 P_SQLERRM IN VARCHAR2,
810 P_PACKAGE_TYPE IN VARCHAR2,
811 X_MSG_COUNT OUT NOCOPY NUMBER,
812 X_MSG_DATA OUT NOCOPY VARCHAR2,
813 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
814 IS
815 l_api_name VARCHAR2(30);
816 l_len_sqlerrm NUMBER ;
817 i NUMBER := 1;
818
819 BEGIN
820
821 --DBMS_OUTPUT.PUT_LINE('*******EXCEPTION*******');
822 --DBMS_OUTPUT.PUT_LINE('API_NAME = '|| P_API_NAME);
823 --DBMS_OUTPUT.PUT_LINE('SQL_CODE = '|| P_SQLCODE );
824 --DBMS_OUTPUT.PUT_LINE('ERROR_M = '|| P_SQLERRM);
825 l_api_name := UPPER(p_api_name);
826
827
828 -- DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || p_package_type);
829
830 IF p_exception_level = Fnd_Msg_Pub.G_MSG_LVL_ERROR
831 THEN
832 x_return_status := Fnd_Api.G_RET_STS_ERROR;
833 x_msg_count := Fnd_Msg_Pub.Count_msg();
834 x_msg_data := Fnd_Msg_Pub.get(Fnd_Msg_Pub.G_FIRST);
835 ELSIF p_exception_level = Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
836 THEN
837 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
838 x_msg_count := Fnd_Msg_Pub.Count_msg();
839 x_msg_data := Fnd_Msg_Pub.get(Fnd_Msg_Pub.G_FIRST);
840 ELSIF p_exception_level = G_EXC_OTHERS
841 THEN
842 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
843
844 Fnd_Message.Set_Name('IBC', 'IBC_ERROR_RETURNED');
845 Fnd_Message.Set_token('PKG_NAME' , p_pkg_name);
846 Fnd_Message.Set_token('API_NAME' , p_api_name);
847 Fnd_Msg_Pub.ADD;
848
849 l_len_sqlerrm := LENGTH(P_SQLERRM) ;
850 WHILE l_len_sqlerrm >= i LOOP
851 Fnd_Message.Set_Name('IBC', 'IBC_SQLERRM');
852 Fnd_Message.Set_token('ERR_TEXT' , SUBSTR(P_SQLERRM,i,240));
853 i := i + 240;
854 Fnd_Msg_Pub.ADD;
855 END LOOP;
856
857 x_msg_count := Fnd_Msg_Pub.Count_msg();
858 x_msg_data := Fnd_Msg_Pub.get(Fnd_Msg_Pub.G_FIRST);
859
860 END IF;
861
862 END Handle_Exceptions;
863
864
865
866
867
868
869
870
871
872
873 PROCEDURE Handle_Ret_Status(p_return_Status VARCHAR2)
874 IS
875 BEGIN
876 IF p_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
877 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
878 ELSIF p_return_status = Fnd_Api.G_RET_STS_ERROR THEN
879 RAISE Fnd_Api.G_EXC_ERROR;
880 END IF;
881
882 END Handle_Ret_Status;
883
884
885
886
887
888
889
890
891
892
893
894 /**************************** INSERT ATTACHMENT *******************/
895 -- This procedure is used to insert new attachments
896 --
897 -- This procedure does not commit the action.
898 --
899 -- VARIABLES *Required
900 -- *p_file_id = fnd_lob file_id that will be assigned to this object
901 -- *p_file_data = the attachment
902 -- *p_file_name = name of the file being added
903 -- *p_mime_type = this is equivalent to p_file_content_type of the
904 -- -- fnd_lobs table, but is not used with that name to avoid confusion.
905 -- *p_file_format = only two(2) valid formats: 'text','binary'
906 -- p_program_tag IN VARCHAR2 DEFAULT NULL
907 /*******************************************************************/
908 PROCEDURE insert_attachment(
909 x_file_id OUT NOCOPY NUMBER
910 ,p_file_data IN BLOB
911 ,p_file_name IN VARCHAR2
912 ,p_mime_type IN VARCHAR2
913 ,p_file_format IN VARCHAR2
914 ,p_program_tag IN VARCHAR2
915 ,x_return_status OUT NOCOPY VARCHAR2
916 )
917 IS
918 l_api_name CONSTANT VARCHAR2(30) := 'insert_attachment';
919 BEGIN
920 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
921
922 -- *** VALIDATION OF VALUES ******
923 -- file format
924 IF ((p_file_format <> 'text') AND (p_file_format <> 'binary')) THEN
925 x_return_status := Fnd_Api.G_RET_STS_ERROR;
926 Fnd_Message.Set_Name('IBC', 'BAD_INPUT_VALUE');
927 Fnd_Message.Set_Token('INPUT', 'p_file_format', FALSE);
928 Fnd_Msg_Pub.ADD;
929 END IF;
930 -- mime type
931 IF (p_mime_type IS NULL) THEN
932 x_return_status := Fnd_Api.G_RET_STS_ERROR;
933 Fnd_Message.Set_Name('IBC', 'BAD_INPUT_VALUE');
934 Fnd_Message.Set_Token('INPUT', 'p_mime_type', FALSE);
935 Fnd_Msg_Pub.ADD;
936 END IF;
937
938 -- Getting next fnd_lobs sequence number
939 SELECT
940 fnd_lobs_s.NEXTVAL
941 INTO
942 x_file_id
943 FROM
944 dual;
945
946 INSERT INTO fnd_lobs(
947 file_id
948 ,file_name
949 ,file_content_type
950 ,file_data
951 ,upload_date
952 ,expiration_date
953 ,program_name
954 ,program_tag
955 ,file_format
956 )VALUES(
957 x_file_id
958 ,p_file_name
959 ,p_mime_type
960 ,p_file_data
961 ,SYSDATE
962 ,NULL
963 ,NULL
964 ,p_program_tag
965 ,p_file_format
966 );
967
968 EXCEPTION
969 WHEN OTHERS THEN
970 x_return_status := Fnd_Api.G_RET_STS_ERROR;
971 Fnd_Message.Set_Name('IBC', 'LOB_INSERT_ERROR');
972 Fnd_Msg_Pub.ADD;
973 END;
974
975
976
977
978
979 /************ LOG ACTION *******************************/
980
981
982 PROCEDURE log_action(
983 p_activity IN VARCHAR2
984 ,p_parent_value IN VARCHAR2
985 ,p_object_type IN VARCHAR2
986 ,p_object_value1 IN VARCHAR2
987 ,p_object_value2 IN VARCHAR2
988 ,p_object_value3 IN VARCHAR2
989 ,p_object_value4 IN VARCHAR2
990 ,p_object_value5 IN VARCHAR2
991 ,p_description IN VARCHAR2
992 )
993 IS
994 temp_rowid VARCHAR2(100);
995 audit_log_id NUMBER;
996 BEGIN
997 Ibc_Audit_Logs_Pkg.insert_row(
998 px_audit_log_id => audit_log_id
999 ,p_activity => p_activity
1000 ,p_parent_value => p_parent_value
1001 ,p_user_id => Fnd_Global.user_id
1002 ,p_time_stamp => SYSDATE
1003 ,p_object_type => p_object_type
1004 ,p_object_value1 => p_object_value1
1005 ,p_object_value2 => p_object_value2
1006 ,p_object_value3 => p_object_value3
1007 ,p_object_value4 => p_object_value4
1008 ,p_object_value5 => p_object_value5
1009 ,p_description => p_description
1010 ,p_object_version_number => 1
1011 ,x_rowid => temp_rowid
1012 );
1013 END;
1014
1015
1016
1017
1018
1019
1020 /**************************** INSERT ATTRIBUTE BUNDLE ***************/
1021 -- This procedure is used to create new attribute bundles
1022 --
1023 -- This procedure does not commit the action.
1024 --
1025 -- VARIABLES
1026 -- file_id = file id in fnd_lobs given to the lob created.
1027 /*******************************************************************/
1028 PROCEDURE insert_attribute_bundle(
1029 x_lob_file_id OUT NOCOPY NUMBER
1030 ,p_new_bundle IN CLOB
1031 ,x_return_status OUT NOCOPY VARCHAR2
1032 )
1033 IS
1034 l_api_name CONSTANT VARCHAR2(30) := 'insert_attribute_bundle';
1035
1036 BEGIN
1037 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1038
1039 -- Getting next fnd_lobs sequence number
1040 SELECT ibc_attribute_bundles_s1.NEXTVAL
1041 INTO x_lob_file_id
1042 FROM dual;
1043
1044 INSERT INTO IBC_ATTRIBUTE_BUNDLES(
1045 attribute_bundle_id
1046 ,attribute_bundle_data
1047 ,created_by
1048 ,creation_date
1049 ,last_updated_by
1050 ,last_update_date
1051 ,last_update_login
1052 ,object_version_number
1053 )VALUES(
1054 x_lob_file_id
1055 ,p_new_bundle
1056 ,FND_GLOBAL.user_id
1057 ,SYSDATE
1058 ,FND_GLOBAL.user_id
1059 ,SYSDATE
1060 ,FND_GLOBAL.login_id
1061 ,1
1062 );
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1066 Fnd_Message.Set_Name('IBC', 'LOB_INSERT_ERROR');
1067 Fnd_Msg_Pub.ADD;
1068 END;
1069
1070
1071
1072
1073
1074
1075 /**************************** TOUCH ATTRIBUTE BUNDLE ***************/
1076 -- This procedure is used to create the attribute bundle before its
1077 -- usage.
1078 --
1079 -- This procedure does not commit the action.
1080 --
1081 -- VARIABLES
1082 -- new_blob = prepared data to be added to fnd_lobs, it also returns a reference
1083 -- to the blob for further additions.
1084 -- exp_date = expiration date to set with data (DEFAULT is NULL).
1085 -- program_tag = VARCHAR2(32) to store added info about blob.
1086 -- file_id = file id in fnd_lobs given to the lob created.
1087 /*******************************************************************/
1088 PROCEDURE touch_attribute_bundle(
1089 x_lob_file_id OUT NOCOPY NUMBER
1090 ,p_exp_date IN DATE
1091 ,p_program_tag IN VARCHAR2
1092 ,x_return_status OUT NOCOPY VARCHAR2
1093 )
1094 IS
1095 l_api_name CONSTANT VARCHAR2(30) := 'touch_attribute_bundle';
1096 BEGIN
1097 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1098
1099 -- Getting next fnd_lobs sequence number
1100 SELECT
1101 fnd_lobs_s.NEXTVAL
1102 INTO
1103 x_lob_file_id
1104 FROM
1105 dual;
1106
1107 -- Reserving empty blob with meta-data
1108 INSERT INTO fnd_lobs(
1109 file_id,
1110 file_name,
1111 file_content_type,
1112 file_data,
1113 upload_date,
1114 expiration_date,
1115 program_name,
1116 program_tag,
1117 file_format)
1118 VALUES(
1119 x_lob_file_id,
1120 'ibc_attributes',
1121 'text/plain',
1122 EMPTY_BLOB(),
1123 SYSDATE,
1124 p_exp_date,
1125 'CONTENT_ITEM',
1126 p_program_tag,
1127 'text');
1128
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1132 Fnd_Message.Set_Name('IBC', 'LOB_INSERT_ERROR');
1133 Fnd_Msg_Pub.ADD;
1134 END;
1135
1136
1137 -- ----------------------------------------------------
1138 -- FUNCTION: Check_Current_User
1139 -- DESCRIPTION:
1140 -- Given either user_id or (srch) resource id and type
1141 -- (mutually exclusive) returns 'TRUE' if it's current user
1142 -- (in case p_user_id was passed) or current resource exists
1143 -- in a resource id and type (usally a group).
1144 -- It's useful to know if a resource is
1145 -- included in a resource group.
1146 -- ----------------------------------------------------
1147 FUNCTION Check_Current_User(
1148 p_user_id IN NUMBER
1149 ,p_resource_id IN NUMBER
1150 ,p_resource_type IN VARCHAR2
1151 ,p_current_user_id IN NUMBER
1152 ) RETURN VARCHAR2 IS
1153 l_result VARCHAR2(30);
1154 l_dummy VARCHAR2(2);
1155 l_current_user_id NUMBER;
1156
1157 CURSOR Check_Group(p_user_id IN NUMBER, p_group_id IN NUMBER) IS
1158 SELECT 'X'
1159 FROM jtf_rs_groups_denorm rsgroup,
1160 jtf_rs_group_members rsmember,
1161 jtf_rs_resource_extns rsextn
1162 WHERE parent_group_id = p_group_id
1163 AND rsgroup.group_id = rsmember.group_id
1164 AND rsmember.delete_flag = 'N'
1165 AND rsextn.resource_id = rsmember.resource_id
1166 AND rsextn.user_id = p_user_id;
1167
1168 CURSOR Check_Individual(p_user_id IN NUMBER, p_resource_id IN NUMBER) IS
1169 SELECT 'X'
1170 FROM jtf_rs_resource_extns
1171 WHERE resource_id = p_resource_id
1172 AND user_id = p_user_id;
1173
1174 CURSOR Check_Responsibility(p_user_id IN NUMBER, p_resp_id IN NUMBER) IS
1175 SELECT 'X'
1176 FROM fnd_user_resp_groups
1177 WHERE user_id = p_user_id
1178 AND responsibility_id = p_resp_id;
1179
1180 BEGIN
1181 l_result := 'FALSE';
1182 l_current_user_id := NVL(p_current_user_id, Fnd_Global.user_id);
1183 IF p_user_id IS NOT NULL THEN
1184 IF p_user_id = l_current_user_id THEN
1185 l_result := 'TRUE';
1186 END IF;
1187 ELSE
1188 IF p_resource_type IN ('GROUP', 'RS_GROUP') THEN
1189 OPEN Check_Group(l_current_user_id, p_resource_id);
1190 FETCH Check_Group INTO l_dummy;
1191 IF Check_Group%FOUND THEN
1192 l_result := 'TRUE';
1193 END IF;
1194 CLOSE Check_Group;
1195 ELSIF p_resource_type = 'RESPONSIBILITY' THEN
1196 IF l_current_user_id = FND_GLOBAL.user_id THEN
1197 IF p_resource_id = FND_GLOBAL.resp_id THEN
1198 l_result := 'TRUE';
1199 END IF;
1200 ELSE
1201 OPEN Check_Responsibility(l_current_user_id, p_resource_id);
1202 FETCH Check_Responsibility INTO l_dummy;
1203 IF Check_Responsibility%FOUND THEN
1204 l_result := 'TRUE';
1205 END IF;
1206 CLOSE Check_Responsibility;
1207 END IF;
1208 ELSE
1209 OPEN Check_Individual(l_current_user_id, p_resource_id);
1210 FETCH Check_Individual INTO l_dummy;
1211 IF Check_Individual%FOUND THEN
1212 l_result := 'TRUE';
1213 END IF;
1214 CLOSE Check_Individual;
1215 END IF;
1216 END IF;
1217 RETURN l_result;
1218 END Check_Current_User;
1219
1220
1221 /**************************** POST INSERT ***************/
1222 -- This procedure is used to recreate the references of the file_id
1223 -- used in ibc_citem_version_tl table. Called from FNDGFU
1224 -- usage.
1225 --
1226 -- This procedure does not commit the action.
1227 --
1228 -- VARIABLES
1229 -- file_id = file id in returned after FNDGFU inserts the file into
1230 -- FND_LOB
1231 /*******************************************************************/
1232 PROCEDURE post_insert(p_file_id IN NUMBER,
1233 p_file_type IN VARCHAR2)
1234 IS
1235 CURSOR CUR_FND_LOBS
1236 IS
1237 SELECT
1238 file_id,
1239 file_name,
1240 file_content_type,
1241 file_data,
1242 upload_date,
1243 expiration_date,
1244 program_name,
1245 program_tag,
1246 file_format,
1247 LANGUAGE
1248 FROM
1249 FND_LOBS
1250 WHERE
1251 file_id = p_file_id;
1252
1253 CURSOR CUR_CITEM_REN(p_citem_version_id IN NUMBER)
1254 IS
1255 SELECT rendition_id, citem.citem_version_id, citem.LANGUAGE,
1256 default_rendition_mime_type,
1257 attachment_file_id
1258 FROM
1259 IBC_RENDITIONS ren,
1260 ibc_citem_versions_tl citem
1261 WHERE
1262 citem.citem_version_id = ren.citem_version_id(+) AND
1263 citem.LANGUAGE = ren.LANGUAGE(+) AND
1264 NVL(default_rendition_mime_type,' ') = NVL(mime_type,' ')
1265 AND citem.citem_version_id = p_citem_version_id;
1266
1267 fnd_lobs_rec CUR_FND_LOBS%ROWTYPE;
1268
1269 l_citem_version_id NUMBER;
1270 l_language VARCHAR2(4);
1271 l_file_name VARCHAR2(100);
1272 l_app_name VARCHAR2(10);
1273
1274
1275 l_old_file_id NUMBER := NULL;
1276
1277 BEGIN
1278
1279 OPEN CUR_FND_LOBS;
1280 FETCH CUR_FND_LOBS INTO fnd_lobs_rec;
1281
1282 l_citem_version_id := SUBSTR(FND_LOBS_REC.program_tag,INSTR(FND_LOBS_REC.program_tag,':',1)+1);
1283 l_app_name := UPPER(SUBSTR(FND_LOBS_REC.program_tag,1,INSTR(FND_LOBS_REC.program_tag,':',1)-1));
1284 l_language := FND_LOBS_REC.LANGUAGE;
1285 l_file_name := SUBSTR(FND_LOBS_REC.file_name,INSTR(FND_LOBS_REC.file_name,'/',-1)+1);
1286
1287
1288 CLOSE CUR_FND_LOBS;
1289
1290 --
1291 -- The below was added to remove any existing attribute files ids or
1292 -- attachment file_ids
1293 -- from FND_LOBS after the content item is loaded from seed. FNDGFU will
1294 -- always insert new files and this file_id will be replaced with the new ones.
1295 -- select the file_id to removed from the fnd lobs
1296 -- Later after the update is done make sure that the file is not referenced any where.
1297 --
1298
1299 BEGIN
1300 SELECT DECODE(p_file_type,'ATTRIB',ATTRIBUTE_FILE_ID,'ATTACH',ATTACHMENT_FILE_ID) file_id
1301 INTO l_old_file_id
1302 FROM ibc_citem_versions_tl A
1303 WHERE a.citem_version_id = l_citem_version_id
1304 AND A.LANGUAGE = USERENV('LANG');
1305 EXCEPTION WHEN OTHERS THEN
1306 NULL;
1307 END;
1308
1309
1310 UPDATE IBC_CITEM_VERSIONS_TL SET
1311 ATTRIBUTE_FILE_ID =DECODE(p_file_type,'ATTRIB',p_file_id,ATTRIBUTE_FILE_ID)
1312 ,ATTACHMENT_FILE_ID =DECODE(p_file_type,'ATTACH',p_file_id,ATTACHMENT_FILE_ID)
1313 ,last_update_date =SYSDATE
1314 WHERE CITEM_VERSION_ID IN (
1315 SELECT
1316 b.citem_version_id
1317 FROM
1318 ibc_citem_versions_tl a,
1319 ibc_citem_versions_tl b
1320 WHERE
1321 a.citem_version_id = l_citem_version_id AND
1322 NVL(a.attachment_file_id, 0) = NVL(DECODE(p_file_type,'ATTACH',b.attachment_file_id,a.attachment_file_id), 0) AND
1323 a.attribute_file_id = DECODE(p_file_type,'ATTRIB',b.attribute_file_id,a.attribute_file_id) AND
1324 a.LANGUAGE = b.LANGUAGE AND
1325 a.LANGUAGE = l_language)
1326 AND USERENV('LANG') IN (LANGUAGE, source_lang);
1327
1328
1329 IF p_file_type='ATTACH' THEN
1330
1331 UPDATE FND_LOBS
1332 SET file_name = (SELECT attachment_file_name FROM ibc_citem_versions_tl
1333 WHERE attachment_file_id=p_file_id AND ROWNUM=1)
1334 WHERE file_id = p_file_id;
1335
1336 BEGIN
1337
1338 FOR i_rec IN CUR_CITEM_REN(l_citem_version_id)
1339
1340 LOOP
1341
1342 Ibc_Renditions_Pkg.LOAD_ROW (
1343 P_RENDITION_ID => i_rec.RENDITION_ID
1344 ,P_LANGUAGE => i_rec.LANGUAGE
1345 ,P_FILE_ID => i_rec.attachment_file_id
1346 ,P_FILE_NAME => NULL
1347 ,P_CITEM_VERSION_ID => l_CITEM_VERSION_ID
1348 ,P_mime_type => i_rec.default_rendition_mime_type
1349 ,p_OWNER => 'SEED'
1350 );
1351
1352
1353 END LOOP;
1354
1355 END;
1356
1357 ELSIF p_file_type='ATTRIB' THEN
1358
1359 UPDATE FND_LOBS
1360 SET file_name = l_file_name
1361 WHERE file_id = p_file_id;
1362
1363 END IF;
1364
1365 DELETE FROM fnd_lobs
1366 WHERE file_id = l_old_file_id
1367 AND NOT EXISTS (SELECT NULL FROM ibc_citem_versions_tl
1368 WHERE DECODE(p_file_type,'ATTRIB',ATTRIBUTE_FILE_ID,'ATTACH',ATTACHMENT_FILE_ID) = l_old_file_id);
1369
1370
1371 COMMIT;
1372
1373
1374 END post_insert;
1375
1376
1377 PROCEDURE post_insert_attach(p_file_id IN NUMBER)
1378 IS
1379
1380 BEGIN
1381 post_insert(p_file_id => p_file_id
1382 ,p_file_type => 'ATTACH');
1383
1384 END post_insert_attach;
1385
1386
1387 PROCEDURE post_insert_attrib(p_file_id IN NUMBER)
1388 IS
1389 BEGIN
1390 post_insert(p_file_id => p_file_id
1391 ,p_file_type => 'ATTRIB');
1392 END post_insert_attrib;
1393
1394 ---------------------------------------------------------
1395 -- FUNCTION: g_true
1396 -- DESCRIPTION: Returns FND_API.g_true, it's useful
1397 -- to access the value from SQL stmts
1398 ---------------------------------------------------------
1399 FUNCTION g_true RETURN VARCHAR2 IS
1400 BEGIN
1401 RETURN Fnd_Api.g_true;
1402 END g_true;
1403
1404 ---------------------------------------------------------
1405 -- FUNCTION: g_false
1406 -- DESCRIPTION: Returns FND_API.g_false, it's useful
1407 -- to access the value from SQL stmts
1408 ---------------------------------------------------------
1409 FUNCTION g_false RETURN VARCHAR2 IS
1410 BEGIN
1411 RETURN Fnd_Api.g_false;
1412 END g_false;
1413
1414 ---------------------------------------------------------
1415 -- FUNCTION: Is_Name_Already_Used
1416 -- DESCRIPTION: Returns TRUE/FALSE, if the name
1417 -- is already used by a different item or
1418 -- directory.
1419 ---------------------------------------------------------
1420 FUNCTION Is_Name_Already_Used(p_dir_node_id IN NUMBER,
1421 p_name IN VARCHAR2,
1422 p_language IN VARCHAR2,
1423 p_chk_content_item_id IN NUMBER,
1424 p_chk_dir_node_id IN NUMBER,
1425 x_object_type OUT NOCOPY VARCHAR2,
1426 x_object_id OUT NOCOPY NUMBER
1427 )
1428 RETURN BOOLEAN
1429 IS
1430 l_result BOOLEAN;
1431 l_dir_node_id NUMBER;
1432 l_dummy VARCHAR2(1);
1433 l_hidden_flag VARCHAR2(1);
1434
1435 CURSOR c_dir_info(p_dir_node_id NUMBER) IS
1436 SELECT hidden_flag
1437 FROM ibc_directory_nodes_b
1438 WHERE directory_node_id = p_dir_node_id;
1439
1440 CURSOR c_chk_name IS
1441 SELECT 'CITEM', civb.content_item_id
1442 FROM ibc_citem_versions_b civb,
1443 ibc_citem_versions_tl civtl
1444 WHERE civb.citem_version_id = civtl.citem_version_id
1445 AND language = NVL(p_language, USERENV('lang'))
1446 AND civb.content_item_id <> NVL(p_chk_content_item_id, -1)
1447 AND EXISTS (SELECT 'X'
1448 FROM ibc_content_items
1449 WHERE directory_node_id = l_dir_node_id
1450 AND content_item_id = civb.content_item_id
1451 )
1452 AND UPPER(civtl.content_item_name) = UPPER(p_name)
1453 UNION
1454 SELECT 'DIRNODE', dirnodeb.directory_node_id
1455 FROM ibc_directory_nodes_b dirnodeb,
1456 ibc_directory_node_rels dirrel
1457 WHERE dirnodeb.directory_node_id = dirrel.child_dir_node_id
1458 AND dirrel.parent_dir_node_id = l_dir_node_id
1459 AND dirnodeb.directory_node_id <> NVL(p_chk_dir_node_id, -1)
1460 AND UPPER(dirnodeb.directory_node_code) = UPPER(p_name)
1461 ;
1462
1463 BEGIN
1464 l_result := FALSE;
1465
1466 l_dir_node_id := p_dir_node_id;
1467
1468 IF l_dir_node_id IS NULL THEN
1469 IF p_chk_dir_node_id IS NOT NULL THEN
1470 SELECT parent_dir_node_id
1471 INTO l_dir_node_id
1472 FROM ibc_directory_node_rels
1473 WHERE child_dir_node_id = p_chk_dir_node_id;
1474 ELSIF p_chk_content_item_id IS NOT NULL THEN
1475 SELECT directory_node_id
1476 INTO l_dir_node_id
1477 FROM ibc_content_items
1478 WHERE content_item_id = p_chk_content_item_id;
1479 END IF;
1480 END IF;
1481
1482 OPEN c_dir_info(l_dir_node_id);
1483 FETCH c_dir_info INTO l_hidden_flag;
1484 CLOSE c_dir_info;
1485
1486 -- Only checking uniqueness in non-hidden folders
1487 IF NVL(l_hidden_flag, 'N') = 'N' OR
1488 p_chk_dir_node_id IS NOT NULL
1489 THEN
1490 OPEN c_chk_name;
1491 FETCH c_chk_name INTO x_object_type, x_object_id;
1492 l_result := c_chk_name%FOUND;
1493 CLOSE c_chk_name;
1494 END IF;
1495
1496 RETURN l_result;
1497 EXCEPTION
1498 WHEN OTHERS THEN
1499 x_object_type := NULL;
1500 x_object_id := NULL;
1501 RETURN FALSE;
1502
1503 END Is_Name_Already_Used;
1504
1505 --------------------------------------------------------------------------------
1506 -- Start of comments
1507 -- API name : Create_Autonomous_renditions
1508 -- Type : Private
1509 -- Pre-reqs : None
1510 -- Function : Called from Content Item Screens/AM to create an autonomous
1511 -- Transaction for FND LOBS
1512 -------------------------------------------------------------------------------
1513 PROCEDURE Create_Autonomous_Upload( p_file_name IN VARCHAR2,
1514 p_mime_type IN VARCHAR2,
1515 p_file_format IN VARCHAR2,
1516 p_program_tag IN VARCHAR2,
1517 x_return_status OUT NOCOPY VARCHAR2,
1518 x_file_id OUT NOCOPY NUMBER
1519 )
1520 IS
1521
1522 PRAGMA AUTONOMOUS_TRANSACTION;
1523
1524 BEGIN
1525
1526 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1527 -- Getting next fnd_lobs sequence number
1528 SELECT
1529 fnd_lobs_s.NEXTVAL INTO x_file_id
1530 FROM
1531 dual;
1532
1533 INSERT INTO fnd_lobs(
1534 file_id
1535 ,file_name
1536 ,file_content_type
1537 ,file_data
1538 ,upload_date
1539 ,expiration_date
1540 ,program_name
1541 ,program_tag
1542 ,file_format
1543 )VALUES(
1544 x_file_id
1545 ,p_file_name
1546 ,p_mime_type
1547 ,EMPTY_BLOB()
1548 ,SYSDATE
1549 ,NULL
1550 ,NULL
1551 ,p_program_tag
1552 ,p_file_format
1553 );
1554
1555 COMMIT;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1560 Fnd_Message.Set_Name('IBC', 'LOB_INSERT_ERROR');
1561 Fnd_Msg_Pub.ADD;
1562
1563 END Create_Autonomous_Upload;
1564
1565 --------------------------------------------------------------------------------
1566 -- Start of comments
1567 -- API name : get_object_name
1568 -- Type : Private
1569 -- Pre-reqs : None
1570 -- Function : called from associations package to get name and code for
1571 -- product_associations
1572 -------------------------------------------------------------------------------
1573 PROCEDURE Get_Object_Name(p_assoc_type_code IN VARCHAR2,
1574 p_assoc_object_val1 IN VARCHAR2,
1575 p_assoc_object_val2 IN VARCHAR2,
1576 p_assoc_object_val3 IN VARCHAR2,
1577 p_assoc_object_val4 IN VARCHAR2,
1578 p_assoc_object_val5 IN VARCHAR2,
1579 x_assoc_name OUT NOCOPY VARCHAR2,
1580 x_assoc_code OUT NOCOPY VARCHAR2,
1581 x_return_status OUT NOCOPY VARCHAR2,
1582 x_msg_count OUT NOCOPY NUMBER,
1583 x_msg_data OUT NOCOPY VARCHAR2
1584 )
1585 IS
1586 CURSOR c_product_info IS
1587 SELECT description assoc_name,
1588 concatenated_segments assoc_code
1589 FROM mtl_system_items_vl
1590 WHERE organization_id = p_assoc_object_val1
1591 AND inventory_item_id = p_assoc_object_val2;
1592
1593 -- CURSOR c_product_category_info IS
1594 -- SELECT CATEGORY_DESC assoc_name,
1595 -- CONCAT_CAT_PARENTAGE assoc_code
1596 -- FROM ENI_PROD_DEN_HRCHY_PARENTS_V
1597 -- WHERE CATEGORY_ID = p_assoc_object_val2
1598 -- AND CATEGORY_SET_ID = p_assoc_object_val1;
1599
1600 l_assoc_name VARCHAR2(200);
1601 l_assoc_code VARCHAR2(80);
1602
1603 l_pcatquery VARCHAR2(1000) := 'SELECT CATEGORY_DESC assoc_name,CONCAT_CAT_PARENTAGE assoc_code FROM ENI_PROD_DEN_HRCHY_PARENTS_V WHERE CATEGORY_ID = :p_assoc_object_val2 AND CATEGORY_SET_ID = :p_assoc_object_val1';
1604
1605 BEGIN
1606 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1607
1608 IF (p_assoc_type_code = 'IBC_PRODUCT') THEN
1609 OPEN c_product_info;
1610 FETCH c_product_info INTO l_assoc_name, l_assoc_code;
1611 CLOSE c_product_info;
1612 x_assoc_name := l_assoc_name;
1613 x_assoc_code := l_assoc_code;
1614
1615 ELSIF (p_assoc_type_code = 'IBC_PRODUCT_CATEGORY') THEN
1616
1617 -- This is written using dynamic SQL to prevent dependency on ENI when OCM is
1618 -- is installed in an environment prior to 11.5.10.
1619 EXECUTE IMMEDIATE l_pcatquery INTO x_assoc_name, x_assoc_code USING p_assoc_object_val2,p_assoc_object_val1;
1620
1621 END IF;
1622
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1626 END Get_Object_Name;
1627
1628 --------------------------------------------------------------------------------
1629 -- Start of comments
1630 -- API name : getAttachclob
1631 -- Type : Private
1632 -- Pre-reqs : None
1633 -- Function : returns the CLOB from FND_LOBS for the attachment files
1634 --
1635 -------------------------------------------------------------------------------
1636 FUNCTION getAttachclob (p_file_id NUMBER) RETURN CLOB IS
1637 l_xmlblob BLOB;
1638 l_xmlclob CLOB;
1639 l_rawBuffer RAW(32767);
1640 l_amount BINARY_INTEGER := 32767;
1641 l_chunksize INTEGER;
1642 l_totalLen INTEGER;
1643 l_offset INTEGER := 1;
1644 l_attrib_id NUMBER;
1645 l_return_status VARCHAR2(30);
1646 BEGIN
1647
1648 l_amount := 32767;
1649 l_offset := 1;
1650
1651 DBMS_LOB.createtemporary(l_xmlclob, TRUE, 2);
1652
1653 SELECT file_data
1654 INTO l_xmlblob
1655 FROM FND_LOBS
1656 WHERE file_id = p_file_id;
1657
1658 l_totalLen := DBMS_LOB.GETLENGTH(l_xmlblob);
1659 l_chunksize := DBMS_LOB.GETCHUNKSIZE(l_xmlblob);
1660 IF (l_chunksize < 32767) THEN
1661 l_amount := (32767 / l_chunksize) * l_chunksize;
1662 END IF;
1663
1664 WHILE l_totalLen >= l_amount LOOP
1665 DBMS_LOB.READ(l_xmlblob, l_amount, l_offset, l_rawBuffer);
1666 DBMS_LOB.WRITEAPPEND(l_xmlclob, LENGTH(utl_raw.cast_to_varchar2(l_rawBuffer)), utl_raw.cast_to_varchar2(l_rawBuffer));
1667 l_totalLen := l_totalLen - l_amount;
1668 l_offset := l_offset + l_amount;
1669 END LOOP;
1670
1671 IF l_totalLen > 0 THEN
1672 DBMS_LOB.READ(l_xmlblob, l_totalLen, l_offset, l_rawBuffer);
1673 DBMS_LOB.WRITEAPPEND(l_xmlclob, LENGTH(utl_raw.cast_to_varchar2(l_rawBuffer)), utl_raw.cast_to_varchar2(l_rawBuffer));
1674 END IF;
1675
1676
1677 RETURN l_xmlclob;
1678
1679 END getAttachCLOB;
1680
1681 FUNCTION Replace_Special_Chars (p_string IN VARCHAR2) RETURN VARCHAR2 IS
1682
1683 l_pos NUMBER;
1684 l_string VARCHAR2(32767);
1685
1686 BEGIN
1687 l_string := p_string;
1688 l_pos := INSTR(l_string, '&');
1689
1690 IF l_pos <> 0 THEN
1691 l_string := REPLACE( p_string, '&', Fnd_Global.local_chr(38) ||'amp;');
1692 END IF;
1693
1694 RETURN l_string;
1695
1696 END Replace_Special_Chars;
1697
1698 END Ibc_Utilities_Pvt;