DBA Data[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;