[Home] [Help]
PACKAGE BODY: APPS.IES_NEW_END_OF_SCRIPT_PKG
Source
1 PACKAGE BODY ies_new_end_of_script_pkg AS
2 /* $Header: iesneosb.pls 120.0 2005/06/03 07:32:22 appldev noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ies_end_of_script_pkg';
4
5
6 PROCEDURE getTemporaryCLOB (clob OUT NOCOPY CLOB) IS
7
8 BEGIN
9 DBMS_LOB.CreateTemporary(clob, TRUE, DBMS_LOB.CALL);
10 END;
11
12 FUNCTION getProperty(element IN xmldom.DOMElement, key IN VARCHAR2) return VARCHAR2 IS
13 nl xmldom.DOMNodeList;
14 len number;
15 n xmldom.DOMNode;
16 dummyElem xmldom.DOMElement;
17 child xmldom.DOMNode;
18 retValue varchar2(4000);
19 BEGIN
20 nl := xmldom.getChildNodes(xmldom.makeNode(element));
21 len := xmldom.getLength(nl);
22
23 if (xmldom.getTagName(element) = 'IESQuestionData' OR xmldom.getTagName(element) = 'IESPanelData') then
24 if (key = 'InteractionId') then
25 return xmldom.getAttribute(element,'InteractionId');
26 end if;
27 if (key = 'AgentId') then
28 return xmldom.getAttribute(element,'AgentId');
29 end if;
30 end if;
31
32 for i in 0..len-1 loop
33 n := xmldom.item(nl, i);
34 dummyElem := xmldom.makeElement(n);
35
36 if (xmldom.getAttribute(dummyElem,'NAME') = key) then
37 child := xmldom.getFirstChild(n);
38 if NOT (xmldom.isNull(child)) then
39 retValue := xmldom.getNodeValue(child);
40 return retValue;
41 else
42 return NULL;
43 end if;
44 end if;
45 end loop;
46 return NULL;
47 END;
48
49 FUNCTION getDummyFootprintDataTag return xmldom.DOMElement IS
50 parser xmlparser.parser;
51 doc xmldom.DOMDocument;
52 element xmlDom.DOMElement;
53 p_element VARCHAR2(256) := '<IESFootprintData></IESFootprintData>';
54 BEGIN
55 -- API body
56 parser := xmlparser.newParser;
57
58 xmlparser.setValidationMode(parser, FALSE);
59 xmlparser.showWarnings(parser, TRUE);
60 xmlparser.parseBuffer(parser, p_element);
61
62 doc := xmlparser.getDocument(parser);
63 element := xmldom.getDocumentElement(doc);
64
65 return element;
66 end;
67
68 FUNCTION getDummyQuestionDataTag return xmldom.DOMElement IS
69 parser xmlparser.parser;
70 doc xmldom.DOMDocument;
71 element xmlDom.DOMElement;
72 p_element VARCHAR2(256) := '<IESQuestionData></IESQuestionData>';
73 BEGIN
74 -- API body
75 parser := xmlparser.newParser;
76
77 xmlparser.setValidationMode(parser, FALSE);
78 xmlparser.showWarnings(parser, TRUE);
79 xmlparser.parseBuffer(parser, p_element);
80
81 doc := xmlparser.getDocument(parser);
82 element := xmldom.getDocumentElement(doc);
83
84 return element;
85 end;
86
87
88 FUNCTION getFootprintDataElement(element IN xmldom.DOMElement) return xmldom.DOMELement IS
89 nl xmldom.DOMNodeList;
90 len number;
91 n xmldom.DOMNode;
92 dummyElem xmldom.DOMElement;
93 BEGIN
94 nl := xmldom.getChildNodes(xmldom.makeNode(element));
95 len := xmldom.getLength(nl);
96
97
98 for i in 0..len-1 loop
99 n := xmldom.item(nl, i);
100 dummyElem := xmldom.makeElement(n);
101
102 if (xmldom.getTagName(dummyElem) = 'IESFootprintData') then
103 return dummyElem;
104 end if;
105 end loop;
106
107 return getDummyFootprintDataTag; -- need this because null returned is not recognized when xmldom.isNull check is done
108 END;
109
110 FUNCTION getQuestionDataElement(element IN xmldom.DOMElement) return xmldom.DOMELement IS
111 nl xmldom.DOMNodeList;
112 len number;
113 n xmldom.DOMNode;
114 dummyElem xmldom.DOMElement;
115 BEGIN
116 nl := xmldom.getChildNodes(xmldom.makeNode(element));
117 len := xmldom.getLength(nl);
118
119
120 for i in 0..len-1 loop
121 n := xmldom.item(nl, i);
122 dummyElem := xmldom.makeElement(n);
123
124 if (xmldom.getTagName(dummyElem) = 'IESQuestionData') then
125 return dummyElem;
126 end if;
127 end loop;
128
129 return getDummyQuestionDataTag; -- need this because null returned is not recognized when xmldom.isNull check is done
130 END;
131
132 PROCEDURE saveFootprintData(element IN xmldom.DOMElement, panelDataId IN NUMBER, intId IN NUMBER, agentId IN NUMBER, seq IN NUMBER) IS
133 elapsedtime NUMBER ;
134
135 TYPE answerId IS REF CURSOR;
136 obj answerId;
137 insertStmt varchar2(4000);
138 seqval number;
139 BEGIN
140
141 elapsedtime := getProperty(element, 'ElapsedTime');
142
143 EXECUTE IMMEDIATE 'SELECT IES_FOOTPRINTING_DATA_S.nextval FROM dual' INTO seqval;
144
145 insertStmt := 'INSERT INTO IES_FOOTPRINTING_DATA( FOOTPRINTING_DATA_ID ,
146 created_by ,
147 creation_date ,
148 elapsed_time ,
149 sequence_number ,
150 panel_data_id)
151 VALUES ( :seq,
152 :1,
153 :2,
154 :3,
155 :4,
156 :5)';
157 EXECUTE immediate insertStmt using seqval, agentId, sysdate,
158 elapsedTime, seq+1, panelDataId;
159 END;
160
161 PROCEDURE insertFootprintData
162 (
163 p_element IN xmldom.DOMElement,
164 panel_data_id IN number,
165 interactionId IN number,
166 agentId IN number
167 ) IS
168
169 --element xmlDom.DOMElement;
170 dummyelem xmlDom.DOMElement;
171 nl xmlDom.DOMNodeList;
172 n xmldom.DOMNode;
173 len NUMBER;
174 BEGIN
175
176 -- API body
177 --element := p_element;
178
179 if NOT (xmldom.isnull(p_element)) then
180 nl := xmldom.getChildNodes(xmldom.makeNode(p_element));
181 len := xmldom.getLength(nl);
182
183 for i in 0..len-1 loop
184 n := xmldom.item(nl, i);
185
186 dummyElem := xmldom.makeElement(n);
187 saveFootprintData(dummyElem, panel_data_id, interactionId, agentId, i);
188
189 end loop;
190
191 end if;
192 END;
193
194
195
196 PROCEDURE saveQuestionData(element IN xmldom.DOMElement, panelDataId IN NUMBER, intId IN NUMBER, agentId IN NUMBER) IS
197 question_id NUMBER ;
198 lookup_id NUMBER ;
199 answer_id NUMBER ;
200 string_val VARCHAR2(4000);
201
202 insertStmt varchar2(4000);
203 seqval number;
204 BEGIN
205
206 question_id := getProperty(element, 'QuestionId');
207 lookup_id := getProperty(element, 'LookupId');
208 string_val := getProperty(element, 'Value');
209 answer_id := getProperty(element, 'AnswerId');
210
211
212 EXECUTE IMMEDIATE 'SELECT ies_question_data_s.nextval FROM dual' INTO seqval;
213
214 insertStmt := 'INSERT INTO ies_question_data( question_data_id ,
215 created_by ,
216 creation_date ,
217 transaction_id ,
218 question_id ,
219 lookup_id ,
220 answer_id ,
221 freeform_string ,
222 panel_data_id)
223 VALUES ( :seq ,
224 :1,
225 :2,
226 :3,
227 :4,
228 :5,
229 :6,
230 :7,
231 :8)';
232 EXECUTE immediate insertStmt using seqval ,
233 agentId ,
234 sysdate ,
235 intId ,
236 question_id ,
237 lookup_id ,
238 answer_id ,
239 string_val,
240 panelDataId;
241 END;
242
243 PROCEDURE insertIESQuestionData
244 (
245 p_element IN xmldom.DOMElement,
246 panel_data_id IN number,
247 interactionId IN number,
248 agentId IN number
249 ) IS
250
251 --element xmlDom.DOMElement;
252 dummyelem xmlDom.DOMElement;
253 nl xmlDom.DOMNodeList;
254 n xmldom.DOMNode;
255 len NUMBER;
256 BEGIN
257
258 -- API body
259 --element := p_element;
260
261 if NOT (xmldom.isnull(p_element)) then
262 nl := xmldom.getChildNodes(xmldom.makeNode(p_element));
263 len := xmldom.getLength(nl);
264
265 for i in 0..len-1 loop
266 n := xmldom.item(nl, i);
267
268 dummyElem := xmldom.makeElement(n);
269 saveQuestionData(dummyElem, panel_data_id, interactionId, agentId);
270
271 end loop;
272
273 end if;
274 END;
275
276
277
278
279 PROCEDURE savePanelData(element IN xmldom.DOMElement, intId IN NUMBER, agentId IN NUMBER) IS
280 panel_id NUMBER;
281 id NUMBER;
282 transaction_id NUMBER;
283 elapsed_time NUMBER;
284 sequence_num NUMBER;
285 deleted_status NUMBER;
286 qd_element XMLDOM.DOMElement;
287 fp_element XMLDOM.DOMElement;
288
289 insertStmt varchar2(4000);
290 seqval number;
291 nullval number;
292 BEGIN
293
294 panel_id := getProperty(element, 'PanelId');
295 sequence_num := getProperty(element, 'SequenceNumber');
296 deleted_Status := getProperty(element, 'DeletedStatus');
297
298 EXECUTE IMMEDIATE 'SELECT ies_panel_data_s.nextval FROM dual' INTO seqval;
299
300 insertStmt := 'INSERT INTO ies_panel_data( panel_data_id ,
301 created_by ,
302 creation_date ,
303 panel_id ,
304 transaction_id ,
305 elapsed_time ,
306 sequence_number ,
307 deleted_status )
308 VALUES ( :seq ,
309 :1,
310 :2,
311 :3,
312 :4,
313 :5,
314 :6,
315 :7) returning panel_data_id INTO :8';
316 execute immediate insertStmt using seqval,
317 agentId,
318 sysdate,
319 panel_id,
320 intid,
321 nullval,
322 sequence_num,
323 deleted_status returning INTO id;
324
325 fp_element := getFootprintDataElement(element);
326 if NOT (xmldom.isNULL(fp_element)) then
327 insertFootprintData(fp_element, id, intId, agentId);
328 end if;
329
330
331 qd_element := getQuestionDataElement(element);
332
333 if NOT (xmldom.isNULL(qd_element)) then
334 insertIESQuestionData(qd_element, id, intId, agentId);
335 end if;
336
337 END;
338
339
340
341 PROCEDURE insertIESPanelData
342 (
343 p_element IN varchar2
344 ) IS
345
346 parser xmlparser.parser;
347 doc xmldom.DOMDocument;
348 element xmlDom.DOMElement;
349 dummyelem xmlDom.DOMElement;
350 nl xmlDom.DOMNodeList;
351 n xmldom.DOMNode;
352 len NUMBER;
353 interactionId NUMBER;
354 agentId NUMBER;
355 BEGIN
356
357 -- API body
358 parser := xmlparser.newParser;
359
360 xmlparser.setValidationMode(parser, FALSE);
361 xmlparser.showWarnings(parser, TRUE);
362 xmlparser.parseBuffer(parser, p_element);
363
364 doc := xmlparser.getDocument(parser);
365 element := xmldom.getDocumentElement(doc);
366
367 if NOT (xmldom.isnull(element)) then
368 interactionId := to_number(getProperty(element, 'InteractionId'));
369 agentId := to_number(getProperty(element, 'AgentId'));
370 nl := xmldom.getChildNodes(xmldom.makeNode(element));
371 len := xmldom.getLength(nl);
372
373 for i in 0..len-1 loop
374 n := xmldom.item(nl, i);
375
376 dummyElem := xmldom.makeElement(n);
377 savePanelData(dummyElem, interactionId, agentId);
378
379 end loop;
380
381 end if;
382 end;
383
384 PROCEDURE saveEndOfScriptData (p_element IN CLOB ) IS
385 parser xmlparser.parser;
386 doc xmldom.DOMDocument;
390 n xmldom.DOMNode;
387 element xmlDom.DOMElement;
388 dummyelem xmlDom.DOMElement;
389 nl xmlDom.DOMNodeList;
391 len NUMBER;
392 interactionId NUMBER;
393 agentId NUMBER;
394 BEGIN
395 -- Standard Start of API savepoint
396 -- API body
397 parser := xmlparser.newParser;
398
399 xmlparser.setValidationMode(parser, FALSE);
400 xmlparser.showWarnings(parser, TRUE);
401 xmlparser.parseClob(parser, p_element);
402
403 doc := xmlparser.getDocument(parser);
404 element := xmldom.getDocumentElement(doc);
405
406 if NOT (xmldom.isnull(element)) then
407 interactionId := to_number(getProperty(element, 'InteractionId'));
408 agentId := to_number(getProperty(element, 'AgentId'));
409 nl := xmldom.getChildNodes(xmldom.makeNode(element));
410 len := xmldom.getLength(nl);
411
412 for i in 0..len-1 loop
413 n := xmldom.item(nl, i);
414
415 dummyElem := xmldom.makeElement(n);
416 savePanelData(dummyElem, interactionId, agentId);
417
418 end loop;
419
420 end if;
421 END;
422
423 END ies_new_end_of_script_pkg;