[Home] [Help]
PACKAGE BODY: APPS.IES_END_OF_SCRIPT_PKG
Source
1 PACKAGE BODY ies_end_of_script_pkg AS
2 /* $Header: ieseosb.pls 115.3 2002/12/09 21:13:15 appldev noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ies_end_of_script_pkg';
4
5 PROCEDURE getTemporaryCLOB (panelClob OUT NOCOPY CLOB, questionClob OUT NOCOPY CLOB) IS
6
7 BEGIN
8 DBMS_LOB.CreateTemporary(panelclob, TRUE, DBMS_LOB.CALL);
9 DBMS_LOB.CreateTemporary(questionclob, 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(2000);
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 PROCEDURE saveQuestionData(element IN xmldom.DOMElement, intId IN NUMBER, agentId IN NUMBER) IS
50 question_id NUMBER ;
51 lookup_id NUMBER ;
52 answer_id NUMBER ;
53 string_val VARCHAR2(512);
54 display_val VARCHAR2(512);
55
56 TYPE answerId IS REF CURSOR;
57 obj answerId;
58 BEGIN
59
60 question_id := getProperty(element, 'QuestionId');
61 lookup_id := getProperty(element, 'LookupId');
62 string_val := getProperty(element, 'Value');
63 display_val := getProperty(element, 'DisplayValue');
64
65 OPEN obj FOR
66 'SELECT answer_id
67 FROM ies_answers
68 WHERE lookup_id = :lkp_id
69 AND answer_value = :ans_val
70 AND answer_display_value = :ans_disp_val' using lookup_id, string_val, display_val;
71
72 FETCH obj INTO answer_id;
73 CLOSE obj;
74
75 INSERT INTO ies_question_data( question_data_id ,
76 created_by ,
77 creation_date ,
78 transaction_id ,
79 question_id ,
80 lookup_id ,
81 answer_id ,
82 freeform_string )
83 VALUES ( ies_question_data_s.nextval ,
84 agentId ,
85 sysdate ,
86 intId ,
87 question_id ,
88 lookup_id ,
89 answer_id ,
90 string_val );
91 END;
92
93 PROCEDURE insertIESQuestionData
94 (
95 p_element IN varchar2
96 ) IS
97
98 parser xmlparser.parser;
99 doc xmldom.DOMDocument;
100 element xmlDom.DOMElement;
101 dummyelem xmlDom.DOMElement;
102 nl xmlDom.DOMNodeList;
103 n xmldom.DOMNode;
104 len NUMBER;
105 interactionId NUMBER;
106 agentId NUMBER;
107 BEGIN
108
109 -- API body
110 parser := xmlparser.newParser;
111
112 xmlparser.setValidationMode(parser, FALSE);
113 xmlparser.showWarnings(parser, TRUE);
114 xmlparser.parseBuffer(parser, p_element);
115
116 doc := xmlparser.getDocument(parser);
117 element := xmldom.getDocumentElement(doc);
118
119 if NOT (xmldom.isnull(element)) then
120 interactionId := to_number(getProperty(element, 'InteractionId'));
121 agentId := to_number(getProperty(element, 'AgentId'));
122 nl := xmldom.getChildNodes(xmldom.makeNode(element));
123 len := xmldom.getLength(nl);
124
125 for i in 0..len-1 loop
126 n := xmldom.item(nl, i);
127
128 dummyElem := xmldom.makeElement(n);
129 saveQuestionData(dummyElem, interactionId, agentId);
130
131 end loop;
132
133 end if;
134 end;
135
136 PROCEDURE insertIESQuestionData (p_element IN CLOB ) IS
137 parser xmlparser.parser;
138 doc xmldom.DOMDocument;
139 element xmlDom.DOMElement;
140 dummyelem xmlDom.DOMElement;
141 nl xmlDom.DOMNodeList;
142 n xmldom.DOMNode;
143 len NUMBER;
144 interactionId NUMBER;
145 agentId NUMBER;
146 BEGIN
147 -- Standard Start of API savepoint
148 -- API body
149 parser := xmlparser.newParser;
150
151 xmlparser.setValidationMode(parser, FALSE);
152 xmlparser.showWarnings(parser, TRUE);
153 xmlparser.parseClob(parser, p_element);
154
155 doc := xmlparser.getDocument(parser);
156 element := xmldom.getDocumentElement(doc);
157
158 if NOT (xmldom.isnull(element)) then
159 interactionId := to_number(getProperty(element, 'InteractionId'));
160 agentId := to_number(getProperty(element, 'AgentId'));
161 nl := xmldom.getChildNodes(xmldom.makeNode(element));
162 len := xmldom.getLength(nl);
163
164 for i in 0..len-1 loop
165 n := xmldom.item(nl, i);
166
167 dummyElem := xmldom.makeElement(n);
168 saveQuestionData(dummyElem, interactionId, agentId);
169
170 end loop;
171
172 end if;
173
174 END insertIESQuestionData;
175
176
177 PROCEDURE savePanelData(element IN xmldom.DOMElement, intId IN NUMBER, agentId IN NUMBER) IS
178 panel_id NUMBER;
179 transaction_id NUMBER;
180 elapsed_time NUMBER;
181 sequence_num NUMBER;
182 deleted_status NUMBER;
183 BEGIN
184
185 panel_id := getProperty(element, 'PanelId');
186 elapsed_time := getProperty(element, 'ElapsedTime');
187 sequence_num := getProperty(element, 'SequenceNumber');
188 deleted_Status := getProperty(element, 'DeletedStatus');
189
190 INSERT INTO ies_panel_data( panel_data_id ,
191 created_by ,
192 creation_date ,
193 panel_id ,
194 transaction_id ,
195 elapsed_time ,
196 sequence_number ,
197 deleted_status )
198 VALUES ( ies_panel_data_s.nextval ,
199 agentId ,
200 sysdate ,
201 panel_id ,
202 intid ,
203 elapsed_time ,
204 sequence_num ,
205 deleted_status );
206 END;
207
208 PROCEDURE insertIESPanelData
209 (
210 p_element IN varchar2
211 ) IS
212
213 parser xmlparser.parser;
214 doc xmldom.DOMDocument;
215 element xmlDom.DOMElement;
216 dummyelem xmlDom.DOMElement;
217 nl xmlDom.DOMNodeList;
218 n xmldom.DOMNode;
219 len NUMBER;
220 interactionId NUMBER;
221 agentId NUMBER;
222 BEGIN
223
224 -- API body
225 parser := xmlparser.newParser;
226
227 xmlparser.setValidationMode(parser, FALSE);
228 xmlparser.showWarnings(parser, TRUE);
229 xmlparser.parseBuffer(parser, p_element);
230
231 doc := xmlparser.getDocument(parser);
232 element := xmldom.getDocumentElement(doc);
233
234 if NOT (xmldom.isnull(element)) then
235 interactionId := to_number(getProperty(element, 'InteractionId'));
236 agentId := to_number(getProperty(element, 'AgentId'));
237 nl := xmldom.getChildNodes(xmldom.makeNode(element));
238 len := xmldom.getLength(nl);
239
240 for i in 0..len-1 loop
241 n := xmldom.item(nl, i);
242
243 dummyElem := xmldom.makeElement(n);
244 savePanelData(dummyElem, interactionId, agentId);
245
246 end loop;
247
248 end if;
249 end;
250
251 PROCEDURE insertIESPanelData (p_element IN CLOB ) IS
252 parser xmlparser.parser;
253 doc xmldom.DOMDocument;
254 element xmlDom.DOMElement;
255 dummyelem xmlDom.DOMElement;
256 nl xmlDom.DOMNodeList;
257 n xmldom.DOMNode;
258 len NUMBER;
259 interactionId NUMBER;
260 agentId NUMBER;
261 BEGIN
262 -- Standard Start of API savepoint
263 -- API body
264 parser := xmlparser.newParser;
265
266 xmlparser.setValidationMode(parser, FALSE);
267 xmlparser.showWarnings(parser, TRUE);
268 xmlparser.parseClob(parser, p_element);
269
270 doc := xmlparser.getDocument(parser);
271 element := xmldom.getDocumentElement(doc);
272
273 if NOT (xmldom.isnull(element)) then
274 interactionId := to_number(getProperty(element, 'InteractionId'));
275 agentId := to_number(getProperty(element, 'AgentId'));
276 nl := xmldom.getChildNodes(xmldom.makeNode(element));
277 len := xmldom.getLength(nl);
278
279 for i in 0..len-1 loop
280 n := xmldom.item(nl, i);
281
282 dummyElem := xmldom.makeElement(n);
283 savePanelData(dummyElem, interactionId, agentId);
284
285 end loop;
286
287 end if;
288 END;
289
290 PROCEDURE updateIESTransactions(interactionId IN NUMBER) IS
291 begin
292
293 UPDATE ies_transactions SET end_time = sysdate WHERE transaction_id = interactionId;
294 END;
295
296
297 END ies_end_of_script_pkg;