[Home] [Help]
PACKAGE BODY: APPS.OKC_XML_PVT
Source
1 Package body OKC_XML_PVT AS
2 /* $Header: OKCRXMLB.pls 120.0 2005/05/26 09:50:40 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ---------------------------------------------------------------------------
6 -- GLOBAL DATASTRUCTURES
7 ---------------------------------------------------------------------------
8 -- TYPES
9 ---------------------------------------------------------------------------
10 -- CONSTANTS
11 ---------------------------------------------------------------------------
12 -- PUBLIC VARIABLES
13 ---------------------------------------------------------------------------
14 -- EXCEPTIONS
15 ---------------------------------------------------------------------------
16 -- Procedures and Functions
17 ---------------------------------------------------------------------------
18
19 PROCEDURE build_xml_clob (
20 p_corrid_rec IN okc_aq_pvt.corrid_rec_typ,
21 p_element_tbl IN okc_aq_pvt.msg_tab_typ,
22 x_xml_clob OUT NOCOPY system.okc_aq_msg_typ
23 )
24 IS
25 l_element_name varchar2(240);
26 l_element_value varchar2(240);
27 l_index number := 1;
28 l_xml_clob system.okc_aq_msg_typ;
29 l_constant varchar2(32767) := '<?xml version="1.0" ?>';
30 l_char_msg varchar2(32767);
31 l_dest_clob system.okc_aq_msg_typ;
32 l_amount integer := 32767;
33 BEGIN
34 -- create a temporary lob
35 l_xml_clob := system.okc_aq_msg_typ(empty_clob());
36 x_xml_clob := system.okc_aq_msg_typ(empty_clob());
37 dbms_lob.createtemporary(l_xml_clob.body,TRUE,dbms_lob.session);
38 dbms_lob.writeappend(l_xml_clob.body,length(l_constant),l_constant);
39
40 -- load message body with message
41 l_constant := '<!DOCTYPE ' ||
42 p_corrid_rec.corrid ||
43 ' [<!ELEMENT ' ||
44 p_corrid_rec.corrid ||
45 '(';
46 dbms_lob.writeappend ( l_xml_clob.body
47 , length(l_constant)
48 , l_constant);
49
50 -- loop thro the records and build xml clob
51 FOR counter IN 1..p_element_tbl.count LOOP
52 l_element_name := p_element_tbl(l_index).element_name;
53 l_constant := l_element_name||',';
54 dbms_lob.writeappend(l_xml_clob.body,length(l_constant),l_constant);
55 l_index := l_index + 1;
56 END LOOP;
57 l_index := 1;
58
59 -- trim the trailing ',' from the clob
60 dbms_lob.trim ( l_xml_clob.body
61 , dbms_lob.getlength(l_xml_clob.body) - 1);
62 l_constant := ')>';
63 dbms_lob.writeappend(l_xml_clob.body,length(l_constant),l_constant);
64
65 -- loop thro the records to add DTD
66 FOR counter IN 1..p_element_tbl.count LOOP
67 l_element_name := p_element_tbl(l_index).element_name;
68 l_index := l_index + 1;
69 l_constant := '<!ELEMENT '||l_element_name||'(#PCDATA)>';
70 dbms_lob.writeappend(l_xml_clob.body,length(l_constant),l_constant);
71 END LOOP;
72 l_index := 1;
73 l_constant := ']><'||p_corrid_rec.corrid||'>';
74 dbms_lob.writeappend(l_xml_clob.body,length(l_constant),l_constant);
75
76 -- loop thro the records to add the xml string
77 FOR counter IN 1..p_element_tbl.count LOOP
78 l_element_name := p_element_tbl(l_index).element_name;
79 l_element_value := p_element_tbl(l_index).element_value;
80 l_index := l_index + 1;
81 l_constant := '<'||l_element_name||'>'||
82 l_element_value||'</'||l_element_name||'>';
83 dbms_lob.writeappend(l_xml_clob.body,length(l_constant),l_constant);
84 END LOOP;
85 l_index := 1;
86 l_constant := '</'||p_corrid_rec.corrid||'>';
87 dbms_lob.writeappend ( l_xml_clob.body
88 , length(l_constant)
89 , l_constant);
90
91 -- l_xml_clob.body := dbms_lob.substr(l_xml_clob.body,32767,1);
92
93 x_xml_clob.body := l_xml_clob.body;
94 dbms_lob.freetemporary(l_xml_clob.body);
95
96 END;
97
98
99 PROCEDURE get_element_vals (
100 p_msg IN system.okc_aq_msg_typ,
101 x_msg_tab OUT NOCOPY okc_aq_pvt.msg_tab_typ,
102 x_corrid OUT NOCOPY okc_aq_pvt.corrid_rec_typ)
103 IS
104 search_start number(10) := 1;
105 start_bracket varchar2(1) := '<';
106 end_bracket varchar2(1) := '>';
107 end_tag varchar2(2) := '</';
108 l_tag varchar2(3) := '>]>';
109 l_msg system.okc_aq_msg_typ;
110 l_temp_clob system.okc_aq_msg_typ;
111 l_temp1_clob system.okc_aq_msg_typ;
112 l_char_element_name varchar2(32767);
113 l_char_element_value varchar2(32767);
114 l_char_corrid varchar2(32767);
115 l_start_bracket_pos number(5);
116 l_start_pos integer ;
117 l_end_pos integer;
118 l_length integer;
119 l_msg_tab okc_aq_pvt.msg_tab_typ:=okc_aq_pvt.msg_tab_typ();
120 l_index integer := 1;
121 l_amount integer;
122
123 BEGIN
124
125 l_msg := system.okc_aq_msg_typ(empty_clob());
126 dbms_lob.createtemporary(l_msg.body,TRUE,dbms_lob.session);
127 l_temp_clob := system.okc_aq_msg_typ(empty_clob());
128 dbms_lob.createtemporary(l_temp_clob.body,TRUE,dbms_lob.session);
129 l_temp1_clob := system.okc_aq_msg_typ(empty_clob());
130 dbms_lob.createtemporary(l_temp1_clob.body,TRUE,dbms_lob.session);
131 l_msg := p_msg;
132
133 -- fix the position to remove DTD of xml string
134 l_end_pos := dbms_lob.instr(l_msg.body,l_tag,1,1) + 3 ;
135 -- cut the DTD from xml string
136 l_amount := dbms_lob.getlength(l_msg.body)- l_end_pos;
137 dbms_lob.copy(l_temp_clob.body,l_msg.body,l_amount,1,
138 (l_end_pos+1));
139
140 l_length := dbms_lob.getlength(l_temp_clob.body);
141
142 -- fix position to get corrid
143 l_tag := '>';
144 l_amount := (dbms_lob.instr(l_temp_clob.body,l_tag,1,1)-1);
145
146 l_char_corrid := dbms_lob.substr(l_temp_clob.body,l_amount,1);
147
148 -- assign corrid to corrid_rec_typ
149 x_corrid.corrid := l_char_corrid;
150 l_length := length(l_char_corrid)+1;
151 l_amount := (dbms_lob.getlength(l_temp_clob.body) -
152 l_length);
153 dbms_lob.copy(l_temp1_clob.body,l_temp_clob.body,
154 l_amount,1,(l_length+1));
155 -- fix position to remove end corrid tag REMINDER!
156 l_length := dbms_lob.getlength(l_temp1_clob.body)-(length(l_char_corrid)+3);
157 dbms_lob.trim(l_temp1_clob.body,l_length);
158
159 -- Remove
160 l_length := dbms_lob.getlength(l_temp1_clob.body);
161 l_char_element_name := dbms_lob.substr(l_temp1_clob.body,l_length,1);
162 -- Remove
163
164 LOOP
165
166 -- capture element name
167 l_start_bracket_pos
168 := dbms_lob.INSTR(l_temp1_clob.body,start_bracket,search_start,1);
169
170 -- see if it is time to get out
171 IF NVL(l_start_bracket_pos,0) = 0
172 THEN
173 EXIT;
174 ELSE
175 l_start_pos := l_start_bracket_pos + 1;
176 END IF;
177 l_end_pos := (dbms_lob.INSTR(l_temp1_clob.body,
178 end_bracket,
179 l_start_pos,
180 1) - 1);
181 l_length := (l_end_pos-l_start_pos) + 1;
182 l_char_element_name := dbms_lob.SUBSTR(l_temp1_clob.body,
183 l_length,
184 l_start_pos);
185
186
187 -- assign element_name to msg_tab_typ rows
188 l_msg_tab.extend;
189 l_msg_tab(l_index).element_name := l_char_element_name;
190
191 -- capture element value
192 l_start_pos := l_end_pos + 2;
193 l_end_pos := (dbms_lob.INSTR(l_temp1_clob.body,
194 end_tag,
195 l_start_pos,1) -1);
196 l_length := (l_end_pos-l_start_pos)+1;
197 l_char_element_value := dbms_lob.SUBSTR(l_temp1_clob.body,
198 l_length,l_start_pos);
199
200
201 -- assign element_value to msg_tab_typ rows
202 l_msg_tab(l_index).element_value := l_char_element_value;
203 search_start := l_end_pos + 2 ;
204 l_index := l_index + 1 ;
205 END LOOP;
206 x_msg_tab := l_msg_tab;
207
208 dbms_lob.freetemporary(l_temp_clob.body);
209 dbms_lob.freetemporary(l_temp1_clob.body);
210 -- dbms_lob.freetemporary(l_msg.body);
211 END;
212
213 END;