[Home] [Help]
PACKAGE BODY: APPS.RG_XBRL_PKG
Source
1 PACKAGE BODY RG_XBRL_PKG AS
2 /* $Header: rgxbrlpb.pls 120.1 2004/08/19 00:16:58 vtreiger noship $ */
3 --
4 -- Wrappers
5 --
6 PROCEDURE Upload_taxonomy(errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY VARCHAR2,
8 p_full_tax_name IN VARCHAR2,
9 p_tax_file_name IN VARCHAR2,
10 p_tax_descr IN VARCHAR2) IS
11 BEGIN
12 --
13 RG_XBRL_PKG.load_taxonomy(p_full_tax_name => p_full_tax_name,
14 p_tax_file_name => p_tax_file_name,
15 p_tax_descr => p_tax_descr);
16 --
17 EXCEPTION
18 WHEN OTHERS THEN
19 errbuf := SQLERRM;
20 retcode := '2';
21 --app_exception.raise_exception;
22 END Upload_taxonomy;
23 --
24 --
25 --
26 PROCEDURE Remove_taxonomy(errbuf OUT NOCOPY VARCHAR2,
27 retcode OUT NOCOPY VARCHAR2,
28 p_full_tax_name IN VARCHAR2) IS
29 BEGIN
30 --
31 RG_XBRL_PKG.delete_taxonomy(p_full_tax_name => p_full_tax_name);
32 --
33 EXCEPTION
34 WHEN OTHERS THEN
35 errbuf := SQLERRM;
36 retcode := '2';
37 --app_exception.raise_exception;
38 END Remove_taxonomy;
39 --
40 -- Regular procedures
41 --
42 PROCEDURE delete_taxonomy(p_full_tax_name IN VARCHAR2)
43 IS
44 p_taxonomy_id NUMBER(15) := 0;
45 l_axis_set_id NUMBER(15) := 0;
46 l_parent_flag NUMBER(15) := 0;
47 Remove_tax_err EXCEPTION;
48 BEGIN
49 BEGIN
50 SELECT taxonomy_id
51 INTO p_taxonomy_id
52 FROM RG_XBRL_TAXONOMIES
53 WHERE taxonomy_name = p_full_tax_name AND
54 ROWNUM = 1;
55 EXCEPTION
56 WHEN NO_DATA_FOUND
57 THEN
58 p_taxonomy_id := 0;
59 END;
60 --
61 IF p_taxonomy_id = 0
62 THEN
63 -- wrong taxonomy name
64 -- deliver a message to a log file
65 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
66 token_num => 2,
67 t1 =>'ROUTINE',
68 v1 =>
69 'RG_XBRL_PKG.delete_taxonomy',
70 t2 =>'ACTION',
71 v2 =>'Taxonomy name '
72 || p_full_tax_name
73 ||' does not exist');
74 RAISE Remove_tax_err;
75 RETURN;
76 END IF;
77 --
78 --provide additional checks before delete
79 --
80 l_axis_set_id := 0;
81 BEGIN
82 SELECT axis_set_id
83 INTO l_axis_set_id
84 FROM RG_REPORT_AXIS_SETS
85 WHERE taxonomy_id = p_taxonomy_id AND
86 axis_set_type = 'R' AND
87 ROWNUM = 1;
88 EXCEPTION
89 WHEN NO_DATA_FOUND
90 THEN
91 l_axis_set_id := 0;
92 END;
93 --
94 IF l_axis_set_id > 0
95 THEN
96 -- taxonomy is used in at least one row set
97 -- deliver a message to a log file
98 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
99 token_num => 2,
100 t1 =>'ROUTINE',
101 v1 =>
102 'RG_XBRL_PKG.delete_taxonomy',
103 t2 =>'ACTION',
104 v2 =>'Taxonomy '
105 || p_full_tax_name
106 ||' is still used in a row set');
107 RAISE Remove_tax_err;
108 RETURN;
109 END IF;
110 --
111 l_parent_flag := 0;
112 BEGIN
113 SELECT 0
114 INTO l_parent_flag
115 FROM DUAL
116 WHERE EXISTS
117 (SELECT v1.taxonomy_id
118 FROM rg_xbrl_taxonomies v1
119 WHERE v1.taxonomy_id <> p_taxonomy_id AND
120 p_taxonomy_id IN
121 (SELECT DISTINCT v2.source_taxonomy_id
122 FROM rg_xbrl_map_v v2
123 WHERE v2.map_taxonomy_id = v1.taxonomy_id)
124 );
125 EXCEPTION
126 WHEN NO_DATA_FOUND
127 THEN
128 l_parent_flag := 1;
129 END;
130 --
131 -- l_parent_flag value 1 means that p_taxonomy_id is
132 -- top level parent node that has no parents above
133 --
134 -- l_parent_flag value 0 means that p_taxonomy_id is
135 -- a child node and has some parents above
136 --
137 IF l_parent_flag = 0
138 THEN
139 -- taxonomy is not a top level parent
140 -- deliver a message to a log file
141 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
142 token_num => 2,
143 t1 =>'ROUTINE',
144 v1 =>
145 'RG_XBRL_PKG.delete_taxonomy',
146 t2 =>'ACTION',
147 v2 =>'Taxonomy '
148 || p_full_tax_name
149 ||' is not a parent');
150 RAISE Remove_tax_err;
151 RETURN;
152 END IF;
153 --
154 DELETE FROM RG_XBRL_MAP_ELEMENTS
155 WHERE taxonomy_id = p_taxonomy_id;
156 COMMIT;
157 --
158 DELETE FROM RG_XBRL_ELEMENTS
159 WHERE taxonomy_id = p_taxonomy_id;
160 COMMIT;
161 --
162 DELETE FROM RG_XBRL_TAXONOMIES
163 WHERE taxonomy_id = p_taxonomy_id;
164 COMMIT;
165 --
166 END delete_taxonomy;
167 --
168 --
169 --
170 PROCEDURE load_taxonomy(p_full_tax_name IN VARCHAR2,
171 p_tax_file_name IN VARCHAR2,
172 p_tax_descr IN VARCHAR2)
173 IS
174 l_file_name_c VARCHAR2(240);
175 l_file_name_d VARCHAR2(240);
176 l_file_name_l VARCHAR2(240);
177 l_file_name_p VARCHAR2(240);
178 l_file_name_r VARCHAR2(240);
179 l_file_name VARCHAR2(240);
180 l_element_cnt NUMBER(15);
181 l_user_id NUMBER(15);
182 l_login_id NUMBER(15);
183 l_date DATE;
184 p_taxonomy_id NUMBER(15);
185 p_tax_dir VARCHAR2(4000);
186 l_url VARCHAR2(300);
187 l_valid_import_flag NUMBER(15);
188 l_valid_import_str VARCHAR2(4000);
189 l_taxonomy_id NUMBER(15);
190 l_xsd_pos NUMBER(15);
191 l_tax_file_name VARCHAR2(240);
192 --
193 Load_tax_err EXCEPTION;
194 --
195 BEGIN
196 l_url := ' ';
197 l_file_name_c := ' ';
198 l_file_name_d := ' ';
199 l_file_name_l := ' ';
200 l_file_name_p := ' ';
201 l_file_name_r := ' ';
202 -- Obtain user ID, login ID
203 l_user_id := 1;
204 l_login_id := 1;
205 l_user_id := FND_GLOBAL.User_Id;
206 l_login_id := FND_GLOBAL.Login_Id;
207 l_date := SYSDATE;
208 --
209 SELECT directory_path
210 INTO p_tax_dir
211 FROM all_directories
212 WHERE directory_name = 'XMLDIR'
213 AND owner = 'SYS';
214 --
215 l_xsd_pos := 0;
216 l_xsd_pos := INSTR(p_tax_file_name,'.xsd',1,1);
217 IF l_xsd_pos > 0
218 THEN
219 l_file_name := p_tax_file_name;
220 l_tax_file_name := SUBSTR(p_tax_file_name,1,l_xsd_pos-1);
221 ELSE
222 l_file_name := p_tax_file_name || '.xsd';
223 l_tax_file_name := p_tax_file_name;
224 END IF;
225 --
226 l_valid_import_flag := 1;
227 l_valid_import_str := '';
228 --
229 read_url(l_file_name,'targetNamespace="','"',l_url,'xlink:href="',
230 l_file_name_c,l_file_name_d,l_file_name_l,l_file_name_p,l_file_name_r);
231 --
232 l_taxonomy_id := 0;
233 --
234 BEGIN
235 SELECT taxonomy_id
236 INTO l_taxonomy_id
237 FROM RG_XBRL_TAXONOMIES
238 WHERE taxonomy_url = l_url;
239 EXCEPTION
240 WHEN NO_DATA_FOUND
241 THEN
242 l_taxonomy_id := 0;
243 END;
244 --
245 IF l_taxonomy_id > 0
246 THEN
247 -- taxonomy has been loaded already
248 -- deliver a message to a log file
249 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
250 token_num => 2,
251 t1 =>'ROUTINE',
252 v1 =>
253 'RG_XBRL_PKG.load_taxonomy',
254 t2 =>'ACTION',
255 v2 =>'Taxonomy '
256 || l_url || ' - ' || l_file_name
257 ||' has been loaded already');
258 RAISE Load_tax_err;
259 RETURN;
260 END IF;
261 --
262 verify_import(l_file_name, l_valid_import_flag, l_valid_import_str,
263 '<import','namespace="','schemaLocation="','"');
264 --
265 IF l_valid_import_flag = 0
266 THEN
267 RAISE Load_tax_err;
268 RETURN;
269 END IF;
270 --
271 SELECT RG_XBRL_TAXONOMY_S.NEXTVAL
272 INTO p_taxonomy_id
273 FROM dual;
274 --
275 -- add one new row in Taxonomy Storage RG_XBRL_TAXONOMIES
276 --
277 INSERT INTO RG_XBRL_TAXONOMIES
278 (TAXONOMY_ID,TAXONOMY_ALIAS,TAXONOMY_NAME,TAXONOMY_URL,TAXONOMY_DESCR,
279 TAXONOMY_IMPORT_FLAG,
280 CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CREATION_DATE,LAST_UPDATE_DATE)
281 VALUES (p_taxonomy_id, l_tax_file_name, p_full_tax_name,l_url,p_tax_descr,'N',
282 l_user_id,l_user_id,l_login_id,l_date,l_date);
283 -- COMMIT;
284 --
285 -- add multiple rows in Taxonomy Elements Storage RG_XBRL_ELEMENTS
286 --
287 insert_tax_clob(p_taxonomy_id, l_file_name, l_valid_import_str);
288 COMMIT;
289 --
290 -- update multiple rows in Taxonomy Elements Storage RG_XBRL_ELEMENTS
291 --
292 update_lbl_clob(l_tax_file_name, p_taxonomy_id,l_file_name_l);
293 --
294 -- update multiple rows in Taxonomy Elements Storage RG_XBRL_ELEMENTS
295 --
296 update_dfn_clob(l_tax_file_name, p_taxonomy_id, l_file_name_d);
297 --
298 -- update multiple rows in Taxonomy Elements Storage RG_XBRL_ELEMENTS
299 --
300 update_flags(p_taxonomy_id);
301 --
302 --
303 END load_taxonomy;
304 --
305 --
306 --
307 PROCEDURE update_flags(p_taxonomy_id IN NUMBER)
308 IS
309 l_taxonomy_id NUMBER := 0;
310 BEGIN
311 --
312 UPDATE RG_XBRL_ELEMENTS t2
313 SET t2.parent_id =
314 (SELECT t1.element_id
315 FROM RG_XBRL_ELEMENTS t1
316 WHERE t1.element_identifier = t2.parent_identifier AND
317 t1.taxonomy_id = p_taxonomy_id)
318 WHERE t2.taxonomy_id = p_taxonomy_id;
319 COMMIT;
320 --
321 UPDATE RG_XBRL_ELEMENTS
322 SET has_parent_flag = 'Y'
323 WHERE taxonomy_id = p_taxonomy_id AND
324 parent_identifier IS NOT NULL;
325 COMMIT;
326 --
327 UPDATE RG_XBRL_ELEMENTS t2
328 SET t2.has_child_flag = 'Y'
329 WHERE t2.taxonomy_id = p_taxonomy_id AND
330 t2.element_identifier IN
331 (SELECT t1.parent_identifier
332 FROM RG_XBRL_ELEMENTS t1
333 WHERE t1.taxonomy_id = p_taxonomy_id);
334 COMMIT;
335 --
336 UPDATE RG_XBRL_ELEMENTS
337 SET hierarchy_level = 3
338 WHERE taxonomy_id = p_taxonomy_id AND
339 (has_child_flag = 'Y' AND has_parent_flag = 'N');
340 COMMIT;
341 --
342 UPDATE RG_XBRL_ELEMENTS
343 SET hierarchy_level = 1
344 WHERE taxonomy_id = p_taxonomy_id AND
345 (has_child_flag = 'N' AND has_parent_flag = 'Y');
346 COMMIT;
347 --
348 UPDATE RG_XBRL_ELEMENTS
349 SET hierarchy_level = 2
350 WHERE taxonomy_id = p_taxonomy_id AND
351 (has_child_flag = 'Y' AND has_parent_flag = 'Y');
352 COMMIT;
353 --
354 END update_flags;
355 --
356 --
357 --
358 PROCEDURE verify_import(p_filename IN VARCHAR2,
359 p_valid_flag IN OUT NOCOPY NUMBER,
360 p_valid_str IN OUT NOCOPY VARCHAR2,
361 p_srch_str1 IN VARCHAR2,
362 p_srch_str2 IN VARCHAR2,
363 p_srch_str3 IN VARCHAR2,
364 p_srch_str4 IN VARCHAR2)
365 IS
366 import_srch_pos NUMBER(15) := 0;
367 l_import_pos NUMBER(15) := 0;
368 l_namesp_pos NUMBER(15) := 0;
369 l_schema_pos NUMBER(15) := 0;
370 l_end_pos NUMBER(15) := 0;
371 l_import_len NUMBER(15) := 0;
372 l_namesp_len NUMBER(15) := 0;
373 l_schema_len NUMBER(15) := 0;
374 l_tax_id NUMBER(15) := 0;
375 l_p_filename NUMBER(15) := 0;
376 --
377 xbfile bfile;
378 iclob clob;
379 --
380 l_namesp_str VARCHAR2(300);
381 l_schema_str VARCHAR2(300);
382 l_url_ret VARCHAR2(300);
383 l_alias VARCHAR2(240);
384 --
385 l_valid_str VARCHAR2(4000);
386 --
387 BEGIN
388 l_p_filename := LENGTH(p_filename);
389 l_alias := SUBSTR(p_filename,1,l_p_filename-4);
390 l_url_ret := ' ';
391 xbfile := bfilename('XMLDIR',p_filename);
392 dbms_lob.open(xbfile);
393 dbms_lob.createtemporary(iclob,TRUE,dbms_lob.session);
394 dbms_lob.loadfromfile(iclob,xbfile,dbms_lob.getlength(xbfile));
395 dbms_lob.close(xbfile);
396 --
397 l_valid_str := '';
398 import_srch_pos := 1;
399 l_import_len := LENGTH(p_srch_str1);
400 l_namesp_len := LENGTH(p_srch_str2);
401 l_schema_len := LENGTH(p_srch_str3);
402
403 LOOP
404 l_import_pos := dbms_lob.INSTR(iclob,p_srch_str1,import_srch_pos,1);
405 --
406 IF l_import_pos = 0
407 THEN
408 EXIT;
409 END IF;
410 --
411 l_namesp_str := '';
412 l_namesp_pos := dbms_lob.INSTR(iclob,p_srch_str2,l_import_pos+l_import_len+1,1);
413 l_end_pos := dbms_lob.INSTR(iclob,p_srch_str4,l_namesp_pos+l_namesp_len+2,1);
414 l_namesp_str :=
415 dbms_lob.SUBSTR(iclob,l_end_pos-l_namesp_pos-l_namesp_len,l_namesp_pos+l_namesp_len);
416 --
417 l_schema_str := '';
418 l_schema_pos := dbms_lob.INSTR(iclob,p_srch_str3,l_import_pos+l_import_len+1,1);
419 l_end_pos := dbms_lob.INSTR(iclob,p_srch_str4,l_schema_pos+l_schema_len+2,1);
420 l_schema_str :=
421 dbms_lob.SUBSTR(iclob,l_end_pos-l_schema_pos-l_schema_len,l_schema_pos+l_schema_len);
422 --
423 IF LENGTH(l_namesp_str) > 0
424 THEN
425 IF INSTR(l_namesp_str,'instance',1,1) > 0
426 THEN
427 -- skip this standard import taxonomy
428 l_tax_id := 0;
429 ELSE
430 --
431 l_valid_str := l_valid_str || l_namesp_str || '*****';
432 l_tax_id := 0;
433 --
434 BEGIN
435 SELECT taxonomy_id
436 INTO l_tax_id
437 FROM RG_XBRL_TAXONOMIES
438 WHERE taxonomy_url = l_namesp_str;
439 EXCEPTION
440 WHEN NO_DATA_FOUND
441 THEN
442 l_tax_id := 0;
443 END;
444 --
445 IF l_tax_id = 0
446 THEN
447 -- taxonomy was not loaded, but is referenced
448 -- deliver a message to a log file
449 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
450 token_num => 2,
451 t1 =>'ROUTINE',
452 v1 =>
453 'RG_XBRL_PKG.verify_import',
454 t2 =>'ACTION',
455 v2 =>'Taxonomy '
456 || l_namesp_str || ' - ' || l_schema_str
457 ||' was never loaded');
458 --
459 p_valid_flag := 0;
460 END IF;
461 END IF;
462 END IF;
463 --
464 import_srch_pos := l_end_pos + 1;
465 END LOOP;
469 --
466 p_valid_str := substr(l_valid_str,1);
467 --
468 dbms_lob.freetemporary(iclob);
470 END verify_import;
471 --
472 --
473 --
474 PROCEDURE read_url(filename IN VARCHAR2,
475 p_first_srch IN VARCHAR2,
476 p_last_replace IN VARCHAR2,
477 p_url_ret IN OUT NOCOPY VARCHAR2,
478 p_link_srch IN VARCHAR2,
479 p_link_c IN OUT NOCOPY VARCHAR2,
480 p_link_d IN OUT NOCOPY VARCHAR2,
481 p_link_l IN OUT NOCOPY VARCHAR2,
482 p_link_p IN OUT NOCOPY VARCHAR2,
483 p_link_r IN OUT NOCOPY VARCHAR2)
484 IS
485 l_first_pos NUMBER(15) := 0;
486 l_second_pos NUMBER(15) := 0;
487 l_first_len NUMBER(15) := 0;
488 link_srch_pos NUMBER(15) := 0;
489 l_alias_pos NUMBER(15) := 0;
490 xbfile bfile;
491 xclob clob;
492 l_url_ret VARCHAR2(300);
493 l_link_ret VARCHAR2(300);
494 l_alias VARCHAR2(240);
495 l_file_name VARCHAR2(240);
496 BEGIN
497 l_alias := SUBSTR(filename,1,LENGTH(filename)-4);
498 l_url_ret := ' ';
499 xbfile := bfilename('XMLDIR',filename);
500 dbms_lob.open(xbfile);
501 dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
502 dbms_lob.loadfromfile(xclob,xbfile,dbms_lob.getlength(xbfile));
503 dbms_lob.close(xbfile);
504 l_first_len := LENGTH(p_first_srch);
505 l_first_pos := dbms_lob.INSTR(xclob,p_first_srch,1,1);
506 l_second_pos := dbms_lob.INSTR(xclob,p_last_replace,l_first_pos+l_first_len+1,1);
507 p_url_ret :=
508 dbms_lob.SUBSTR(xclob,l_second_pos-l_first_pos-l_first_len,l_first_pos+l_first_len);
509 link_srch_pos := 1;
510 l_first_pos := 0;
511 l_second_pos := 0;
512 l_first_len := LENGTH(p_link_srch);
513 LOOP
514 l_first_pos := dbms_lob.INSTR(xclob,p_link_srch,link_srch_pos,1);
515 IF l_first_pos = 0
516 THEN
517 EXIT;
518 END IF;
519 l_second_pos := dbms_lob.INSTR(xclob,p_last_replace,l_first_pos+l_first_len+1,1);
520 l_link_ret :=
521 dbms_lob.SUBSTR(xclob,l_second_pos-l_first_pos-l_first_len,l_first_pos+l_first_len);
522 l_alias_pos := INSTR(l_link_ret,l_alias,1);
523 l_file_name := SUBSTR(l_link_ret,l_alias_pos);
524 --
525 IF INSTR(l_file_name,'calculation',1) > 0
526 THEN
527 p_link_c := l_file_name;
528 END IF;
529 --
530 IF INSTR(l_file_name,'definition',1) > 0
531 THEN
532 p_link_d := l_file_name;
533 END IF;
534 --
535 IF INSTR(l_file_name,'label',1) > 0
536 THEN
537 p_link_l := l_file_name;
538 END IF;
539 --
540 IF INSTR(l_file_name,'presentation',1) > 0
541 THEN
542 p_link_p := l_file_name;
543 END IF;
544 --
545 IF INSTR(l_file_name,'reference',1) > 0
546 THEN
547 p_link_r := l_file_name;
548 END IF;
549 --
550 link_srch_pos := l_second_pos + 1;
551 END LOOP;
552 dbms_lob.freetemporary(xclob);
553 EXCEPTION
554 WHEN others
555 THEN
556 l_url_ret := ' ';
557 END read_url;
558 --
559 --
560 --
561 PROCEDURE insert_tax_clob(p_taxonomy_id IN NUMBER,
562 filename IN VARCHAR2,
563 p_valid_str IN VARCHAR2)
564 IS
565 xbfile bfile;
566 p_clob clob;
567 --
568 l_user_id NUMBER(15);
569 l_login_id NUMBER(15);
570 l_date DATE;
571 --
572 l_elem_id NUMBER(15) := 0;
573 l_second_pos NUMBER(15) := 0;
574 --
575 l_elem_found_pos NUMBER(15) := 0;
576 --
577 l_elem_srch_pos NUMBER(15) := 0;
578 l_id_srch_pos NUMBER(15) := 0;
579 l_name_srch_pos NUMBER(15) := 0;
580 l_type_srch_pos NUMBER(15) := 0;
581 l_group_srch_pos NUMBER(15) := 0;
582 l_docm_srch_pos NUMBER(15) := 0;
583 l_abstr_srch_pos NUMBER(15) := 0;
584 l_next_elem_pos NUMBER(15) := 0;
585 l_exec NUMBER(15) := 0;
586 --
587 l_elem_xbrl_id VARCHAR2(240);
588 l_elem_name VARCHAR2(240);
589 l_elem_type VARCHAR2(240);
590 l_elem_group VARCHAR2(240);
591 l_elem_descr VARCHAR2(3000);
592 --
593 l_delim_pos NUMBER(15) := 0;
594 l_source_tax_id NUMBER(15) := 0;
595 l_cur_url VARCHAR2(300);
596 l_valid_str VARCHAR2(4000);
597 l_new_valid_str VARCHAR2(4000);
598 l_valid_len NUMBER(15) := 0;
599 --
600 Insert_tax_err EXCEPTION;
601 --
602 BEGIN
603 -- Obtain user ID, login ID
604 l_user_id := 1;
605 l_login_id := 1;
606 l_user_id := FND_GLOBAL.User_Id;
607 l_login_id := FND_GLOBAL.Login_Id;
608 l_date := SYSDATE;
609 --
610 xbfile := bfilename('XMLDIR',filename);
611 dbms_lob.open(xbfile);
612 dbms_lob.createtemporary(p_clob,TRUE,dbms_lob.session);
613 dbms_lob.loadfromfile(p_clob,xbfile,dbms_lob.getlength(xbfile));
614 dbms_lob.close(xbfile);
615 --
616 l_elem_found_pos := 1;
617 --
618 LOOP
619 l_elem_name := '';
620 l_elem_xbrl_id := '';
624 l_exec := 1;
621 l_elem_type := '';
622 l_elem_group := '';
623 l_elem_descr := '';
625 --
626 l_elem_srch_pos := 0;
627 l_elem_srch_pos := dbms_lob.INSTR(p_clob,'<element',l_elem_found_pos,1);
628 IF l_elem_srch_pos = 0
629 THEN
630 EXIT;
631 END IF;
632 --
633 l_next_elem_pos := dbms_lob.INSTR(p_clob,'<element',l_elem_srch_pos+1,1);
634 --
635 -- get name attribute for the element
636 --
637 l_name_srch_pos := 0;
638 l_name_srch_pos := dbms_lob.INSTR(p_clob,'name="',l_elem_srch_pos,1);
639 IF l_name_srch_pos = 0
640 THEN
641 EXIT;
642 END IF;
643 l_second_pos := dbms_lob.INSTR(p_clob,'"',l_name_srch_pos+6,1);
644 IF (l_next_elem_pos = 0)
645 THEN
646 l_elem_name :=
647 dbms_lob.SUBSTR(p_clob,l_second_pos-l_name_srch_pos-6,l_name_srch_pos+6);
648 ELSE
649 IF (l_name_srch_pos < l_next_elem_pos)
650 THEN
651 l_elem_name :=
652 dbms_lob.SUBSTR(p_clob,l_second_pos-l_name_srch_pos-6,l_name_srch_pos+6);
653 ELSE
654 l_exec := 0;
655 END IF;
656 END IF;
657 --
658 --
659 -- get id attribute for the element
660 --
661 l_id_srch_pos := 0;
662 l_id_srch_pos := dbms_lob.INSTR(p_clob,'id="',l_elem_srch_pos,1);
663 IF l_id_srch_pos = 0
664 THEN
665 l_elem_xbrl_id := l_elem_name;
666 ELSE
667 l_second_pos := dbms_lob.INSTR(p_clob,'"',l_id_srch_pos+4,1);
668 IF (l_next_elem_pos = 0)
669 THEN
670 l_elem_xbrl_id :=
671 dbms_lob.SUBSTR(p_clob,l_second_pos-l_id_srch_pos-4,l_id_srch_pos+4);
672 ELSE
673 IF (l_id_srch_pos < l_next_elem_pos)
674 THEN
675 l_elem_xbrl_id :=
676 dbms_lob.SUBSTR(p_clob,l_second_pos-l_id_srch_pos-4,l_id_srch_pos+4);
677 ELSE
678 l_elem_xbrl_id := l_elem_name;
679 END IF;
680 END IF;
681 END IF;
682 --
683 --
684 -- get type attribute for the element
685 --
686 l_type_srch_pos := 0;
687 l_type_srch_pos := dbms_lob.INSTR(p_clob,'type="',l_elem_srch_pos,1);
688 IF l_type_srch_pos = 0
689 THEN
690 l_elem_type := '';
691 ELSE
692 l_second_pos := dbms_lob.INSTR(p_clob,'"',l_type_srch_pos+6,1);
693 IF (l_next_elem_pos = 0)
694 THEN
695 l_elem_type :=
696 dbms_lob.SUBSTR(p_clob,l_second_pos-l_type_srch_pos-6,l_type_srch_pos+6);
697 ELSE
698 IF (l_type_srch_pos < l_next_elem_pos)
699 THEN
700 l_elem_type :=
701 dbms_lob.SUBSTR(p_clob,l_second_pos-l_type_srch_pos-6,l_type_srch_pos+6);
702 ELSE
703 l_elem_type := '';
704 END IF;
705 END IF;
706 END IF;
707 --
708 --
709 -- get group attribute for the element
710 --
711 l_group_srch_pos := 0;
712 l_group_srch_pos := dbms_lob.INSTR(p_clob,'substitutionGroup="',l_elem_srch_pos,1);
713 IF l_group_srch_pos = 0
714 THEN
715 l_elem_group := '';
716 ELSE
717 l_second_pos := dbms_lob.INSTR(p_clob,'"',l_group_srch_pos+19,1);
718 IF (l_next_elem_pos = 0)
719 THEN
720 l_elem_group :=
721 dbms_lob.SUBSTR(p_clob,l_second_pos-l_group_srch_pos-19,l_group_srch_pos+19);
722 ELSE
723 IF (l_group_srch_pos < l_next_elem_pos)
724 THEN
725 l_elem_group :=
726 dbms_lob.SUBSTR(p_clob,l_second_pos-l_group_srch_pos-19,l_group_srch_pos+19);
727 ELSE
728 l_elem_group := '';
729 END IF;
730 END IF;
731 END IF;
732 --
733 --
734 -- get documentation attribute for the element
735 --
736 l_docm_srch_pos := 0;
737 l_docm_srch_pos := dbms_lob.INSTR(p_clob,'<documentation>',l_elem_srch_pos,1);
738 IF l_docm_srch_pos = 0
739 THEN
740 l_elem_descr := '';
741 ELSE
742 l_second_pos := dbms_lob.INSTR(p_clob,'</documentation>',l_docm_srch_pos+15,1);
743 IF (l_next_elem_pos = 0)
744 THEN
745 IF (l_second_pos-l_docm_srch_pos-15) > 1000
746 THEN
747 l_elem_descr :=
748 dbms_lob.SUBSTR(p_clob,1000,l_docm_srch_pos+15);
749 ELSE
750 l_elem_descr :=
751 dbms_lob.SUBSTR(p_clob,l_second_pos-l_docm_srch_pos-15,l_docm_srch_pos+15);
752 END IF;
753 ELSE
754 IF (l_group_srch_pos < l_next_elem_pos)
755 THEN
756 IF (l_second_pos-l_docm_srch_pos-15) > 1000
757 THEN
758 l_elem_descr :=
759 dbms_lob.SUBSTR(p_clob,1000,l_docm_srch_pos+15);
760 ELSE
761 l_elem_descr :=
765 l_elem_group := '';
762 dbms_lob.SUBSTR(p_clob,l_second_pos-l_docm_srch_pos-15,l_docm_srch_pos+15);
763 END IF;
764 ELSE
766 END IF;
767 END IF;
768 END IF;
769 --
770 --
771 -- get abstract attribute for the element
772 --
773 l_abstr_srch_pos := 0;
774 l_abstr_srch_pos := dbms_lob.INSTR(p_clob,'abstract="true"',l_elem_srch_pos,1);
775 IF l_abstr_srch_pos > 0
776 THEN
777 IF (l_next_elem_pos = 0)
778 THEN
779 EXIT;
780 ELSE
781 IF (l_abstr_srch_pos < l_next_elem_pos)
782 THEN
783 l_exec := 0;
784 END IF;
785 END IF;
786 END IF;
787 --
788 IF l_exec = 1
789 THEN
790 IF INSTR(l_elem_group,'item',1,1) > 0
791 THEN
792 SELECT RG_XBRL_ELEMENTS_S.NEXTVAL
793 INTO l_elem_id
794 FROM dual;
795 --
796 INSERT INTO RG_XBRL_ELEMENTS
797 (taxonomy_id,element_id,
798 element_identifier,element_descr,
799 element_name,element_type,
800 element_group,
801 has_child_flag,has_parent_flag,hierarchy_level,
802 CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
803 CREATION_DATE,LAST_UPDATE_DATE)
804 VALUES
805 (p_taxonomy_id, l_elem_id,
806 l_elem_xbrl_id, l_elem_descr,
807 l_elem_name, l_elem_type,
808 l_elem_group,
809 'N', 'N', 0,
810 l_user_id,l_user_id,l_login_id,
811 l_date,l_date);
812 --
813 INSERT INTO RG_XBRL_MAP_ELEMENTS
814 (taxonomy_id,element_id,
815 enabled_flag,
816 CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
817 CREATION_DATE,LAST_UPDATE_DATE)
818 VALUES
819 (p_taxonomy_id, l_elem_id,
820 'Y',
821 l_user_id,l_user_id,l_login_id,
822 l_date,l_date);
823 END IF;
824 END IF;
825 --
826 l_elem_found_pos := l_elem_srch_pos + 5;
827 END LOOP;
828 dbms_lob.freetemporary(p_clob);
829 -- COMMIT;
830 --
831 -- add elements from imported taxonomies for the taxonomy_id
832 -- into RG_XBRL_MAP_ELEMENTS using p_valid_str
833 --
834 l_valid_str := SUBSTR(p_valid_str,1,LENGTH(p_valid_str));
835 IF LENGTH(l_valid_str) > 0
836 THEN
837 LOOP
838 IF LENGTH(l_valid_str) = 0
839 THEN
840 EXIT;
841 END IF;
842 l_delim_pos := INSTR(l_valid_str,'*****',1,1);
843 IF l_delim_pos = 0
844 THEN
845 EXIT;
846 END IF;
847 l_cur_url := SUBSTR(l_valid_str,1,l_delim_pos-1);
848 l_source_tax_id := 0;
849 SELECT taxonomy_id
850 INTO l_source_tax_id
851 FROM RG_XBRL_TAXONOMIES
852 WHERE taxonomy_url = l_cur_url;
853 --
854 IF l_source_tax_id = 0
855 THEN
856 -- imported taxonomy was not loaded
857 GL_MESSAGE.Write_Log(msg_name =>'SHRD0180',
858 token_num => 2,
859 t1 =>'ROUTINE',
860 v1 =>
861 'RG_XBRL_PKG.insert_tax_clob',
862 t2 =>'ACTION',
863 v2 =>'Import Taxonomy '
864 || l_cur_url ||
865 ' was not loaded');
866 RAISE Insert_tax_err;
867 EXIT;
868 ELSE
869 INSERT INTO RG_XBRL_MAP_ELEMENTS
870 (taxonomy_id,element_id,
871 enabled_flag,
872 CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
873 CREATION_DATE,LAST_UPDATE_DATE)
874 ( SELECT p_taxonomy_id,
875 mel.element_id,mel.enabled_flag,
876 mel.CREATED_BY,mel.LAST_UPDATED_BY,mel.LAST_UPDATE_LOGIN,
877 mel.CREATION_DATE,mel.LAST_UPDATE_DATE
878 FROM RG_XBRL_MAP_ELEMENTS mel
879 WHERE mel.taxonomy_id = l_source_tax_id AND
880 mel.element_id NOT IN
881 (SELECT map.element_id
882 FROM RG_XBRL_MAP_ELEMENTS map
883 WHERE map.taxonomy_id = p_taxonomy_id)
884 );
885 --COMMIT;
886 UPDATE RG_XBRL_TAXONOMIES
887 SET TAXONOMY_IMPORT_FLAG = 'Y'
888 WHERE taxonomy_id = p_taxonomy_id;
889 --COMMIT;
890 END IF;
891 --
892 --
893 l_valid_len := LENGTH(l_valid_str);
894 IF l_valid_len = (l_delim_pos + 4)
895 THEN
896 EXIT;
897 END IF;
898 l_new_valid_str := substr(l_valid_str,l_delim_pos+5,l_valid_len-l_delim_pos-4);
899 l_valid_len := LENGTH(l_new_valid_str);
900 l_valid_str := SUBSTR(l_new_valid_str,1,l_valid_len);
901 END LOOP;
902 --
903 END IF;
904 --
905 END insert_tax_clob;
906 --
910 p_taxonomy_id IN NUMBER,
907 --
908 --
909 PROCEDURE update_lbl_clob(p_tax_name IN VARCHAR2,
911 filename IN VARCHAR2)
912 IS
913 xbfile bfile;
914 l_clob clob;
915 --
916 l_start_pos NUMBER(15) := 0;
917 l_title_pos NUMBER(15) := 0;
918 l_second_pos NUMBER(15) := 0;
919 l_label_pos NUMBER(15) := 0;
920 l_descr_pos NUMBER(15) := 0;
921 l_left_br_pos NUMBER(15) := 0;
922 l_right_br_pos NUMBER(15) := 0;
923 l_link_to_pos NUMBER(15) := 0;
924 l_cur_lblarc_pos NUMBER(15) := 0;
925 l_fnd_lblarc_pos NUMBER(15) := 0;
926 l_near_lblarc_pos NUMBER(15) := 0;
927 l_prev_lblarc_pos NUMBER(15) := 0;
928 l_link_from_pos NUMBER(15) := 0;
929 --
930 l_element_id NUMBER(15) := 0;
931 l_element_xbrl_id VARCHAR2(240);
932 l_element_label VARCHAR2(240);
933 --
934 l_label VARCHAR2(240);
935 l_label_descr VARCHAR2(240);
936 l_label_from VARCHAR2(240);
937 --
938 CURSOR tax_el_storage (tax_id number) IS
939 SELECT element_id,
940 element_identifier,
941 element_label
942 FROM RG_XBRL_ELEMENTS t1
943 WHERE t1.taxonomy_id = tax_id
944 FOR UPDATE;
945 --
946 BEGIN
947 xbfile := bfilename('XMLDIR',filename);
948 dbms_lob.open(xbfile);
949 dbms_lob.createtemporary(l_clob,TRUE,dbms_lob.session);
950 dbms_lob.loadfromfile(l_clob,xbfile,dbms_lob.getlength(xbfile));
951 dbms_lob.close(xbfile);
952 --
953 OPEN tax_el_storage(p_taxonomy_id);
954 LOOP
955 l_element_id := 0;
956 l_element_xbrl_id := '';
957 l_element_label := '';
958 l_label := '';
959 --
960 FETCH tax_el_storage INTO l_element_id,
961 l_element_xbrl_id,
962 l_element_label;
963 IF tax_el_storage%NOTFOUND THEN
964 EXIT;
965 END IF;
966 --
967 l_start_pos := dbms_lob.INSTR(l_clob,p_tax_name || '.xsd#' || l_element_xbrl_id,1,1);
968 l_title_pos := dbms_lob.INSTR(l_clob, 'label="', l_start_pos+6, 1);
969 l_second_pos := dbms_lob.INSTR(l_clob, '"', l_title_pos+7, 1);
970 l_label_descr :=
971 dbms_lob.SUBSTR(l_clob,l_second_pos-l_title_pos-7,l_title_pos+7);
972 --
973 -- label description in loc is ready in l_label_descr
974 --
975 -- now find xlink:to="l_label_descr"
976 --
977 l_link_to_pos := dbms_lob.INSTR(l_clob,'xlink:to="' || l_label_descr || '"',1,1);
978 --
979 -- now find the nearest <labelArc for this l_link_to_pos
980 --
981 l_cur_lblarc_pos := 1;
982 l_near_lblarc_pos := 1;
983 l_prev_lblarc_pos := 1;
984 l_prev_lblarc_pos := dbms_lob.INSTR(l_clob, '<labelArc', 1, 1);
985 LOOP
986 l_fnd_lblarc_pos := dbms_lob.INSTR(l_clob, '<labelArc', l_cur_lblarc_pos, 1);
987 IF (l_fnd_lblarc_pos = 0) OR (l_fnd_lblarc_pos > l_link_to_pos)
988 THEN
989 l_near_lblarc_pos := l_cur_lblarc_pos;
990 EXIT;
991 END IF;
992 l_prev_lblarc_pos := l_fnd_lblarc_pos;
993 l_cur_lblarc_pos := l_fnd_lblarc_pos + 5;
994 END LOOP;
995 --
996 -- l_near_lblarc_pos is ready
997 --
998 l_link_from_pos := dbms_lob.INSTR(l_clob,'xlink:from="',l_near_lblarc_pos,1);
999 l_second_pos := dbms_lob.INSTR(l_clob, '"', l_link_from_pos+12, 1);
1000 l_label_from :=
1001 dbms_lob.SUBSTR(l_clob,l_second_pos-l_link_from_pos-12,l_link_from_pos+12);
1002 --
1003 -- l_label_from is ready for xlink:label=" " search
1004 --
1005 l_label_pos := dbms_lob.INSTR(l_clob,
1006 'xlink:label="' || l_label_from, 1, 1);
1007 l_right_br_pos := dbms_lob.INSTR(l_clob, '>', l_label_pos, 1);
1008 l_left_br_pos := dbms_lob.INSTR(l_clob, '<', l_right_br_pos+1, 1);
1009 --
1010 l_label :=
1011 dbms_lob.SUBSTR(l_clob,l_left_br_pos-l_right_br_pos-1, l_right_br_pos+1);
1012 --
1013 UPDATE RG_XBRL_ELEMENTS
1014 SET element_label = l_label
1015 WHERE CURRENT OF tax_el_storage;
1016 END LOOP;
1017 CLOSE tax_el_storage;
1018 dbms_lob.freetemporary(l_clob);
1019 COMMIT;
1020 END update_lbl_clob;
1021 --
1022 --
1023 --
1024 PROCEDURE update_dfn_clob(p_tax_name IN VARCHAR2,
1025 p_taxonomy_id IN NUMBER,
1026 filename IN VARCHAR2)
1027 IS
1028 xbfile bfile;
1029 d_clob clob;
1030 --
1031 l_start_pos NUMBER(15) := 0;
1032 l_title_pos NUMBER(15) := 0;
1033 l_second_pos NUMBER(15) := 0;
1034 l_label_pos NUMBER(15) := 0;
1035 l_descr_pos NUMBER(15) := 0;
1036 l_def_arc_pos NUMBER(15) := 0;
1037 l_from_pos NUMBER(15) := 0;
1038 l_to_pos NUMBER(15) := 0;
1039 l_ref_pos NUMBER(15) := 0;
1040 --
1041 l_element_id NUMBER(15) := 0;
1042 l_element_xbrl_id VARCHAR2(240);
1043 l_element_defn_parent VARCHAR2(240);
1044 --
1045 l_label VARCHAR2(240);
1046 l_title_descr VARCHAR2(240);
1047 l_parent_descr VARCHAR2(240);
1048 --
1049 CURSOR tax_el_storage (tax_id number) IS
1050 SELECT element_id,
1054 WHERE t1.taxonomy_id = tax_id
1051 element_identifier,
1052 parent_identifier
1053 FROM RG_XBRL_ELEMENTS t1
1055 FOR UPDATE;
1056 --
1057 BEGIN
1058 xbfile := bfilename('XMLDIR',filename);
1059 dbms_lob.open(xbfile);
1060 dbms_lob.createtemporary(d_clob,TRUE,dbms_lob.session);
1061 dbms_lob.loadfromfile(d_clob,xbfile,dbms_lob.getlength(xbfile));
1062 dbms_lob.close(xbfile);
1063 --
1064 OPEN tax_el_storage(p_taxonomy_id);
1065 LOOP
1066 l_element_id := 0;
1067 l_element_xbrl_id := '';
1068 l_element_defn_parent := '';
1069 --
1070 FETCH tax_el_storage INTO l_element_id,
1071 l_element_xbrl_id,
1072 l_element_defn_parent;
1073 IF tax_el_storage%NOTFOUND THEN
1074 EXIT;
1075 END IF;
1076 --
1077 l_start_pos := dbms_lob.INSTR(d_clob,p_tax_name || '.xsd#' || l_element_xbrl_id,1,1);
1078 l_title_pos := dbms_lob.INSTR(d_clob, 'label="', l_start_pos+6, 1);
1079 l_second_pos := dbms_lob.INSTR(d_clob, '"', l_title_pos+7, 1);
1080 l_title_descr :=
1081 dbms_lob.SUBSTR(d_clob,l_second_pos-l_title_pos-7,l_title_pos+7);
1082 --
1083 l_def_arc_pos := dbms_lob.INSTR(d_clob, '<definitionArc', 1, 1);
1084 l_from_pos := dbms_lob.INSTR(d_clob, 'from="' || l_title_descr, l_def_arc_pos, 1);
1085 l_to_pos := dbms_lob.INSTR(d_clob, 'to="' || l_title_descr, l_from_pos+6, 1);
1086 l_second_pos := dbms_lob.INSTR(d_clob, '"', l_to_pos+5, 1);
1087 l_parent_descr :=
1088 dbms_lob.SUBSTR(d_clob,l_second_pos-l_to_pos-4,l_to_pos+4);
1089 --
1090 l_ref_pos := dbms_lob.INSTR(d_clob, 'href="' || p_tax_name || '.xsd#' || l_parent_descr,
1091 1, 1);
1092 --
1093 l_element_defn_parent := '';
1094 IF (l_ref_pos > 0) AND (l_parent_descr <> l_element_xbrl_id)
1095 THEN
1096 l_element_defn_parent := SUBSTR(l_parent_descr,1,LENGTH(l_parent_descr));
1097 END IF;
1098 --
1099 UPDATE RG_XBRL_ELEMENTS
1100 SET parent_identifier = l_element_defn_parent
1101 WHERE CURRENT OF tax_el_storage;
1102 END LOOP;
1103 CLOSE tax_el_storage;
1104 dbms_lob.freetemporary(d_clob);
1105 COMMIT;
1106 END update_dfn_clob;
1107 --
1108 END RG_XBRL_PKG;