[Home] [Help]
PACKAGE BODY: APPS.CS_ERES_INT_PKG
Source
1 PACKAGE BODY CS_ERES_INT_PKG AS
2 /* $Header: cserespb.pls 120.31.12020000.3 2012/07/17 04:36:51 gasankar ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'CS_ERES_INT_PKG' ;
5 dbg_msg VARCHAR2(4000) ;
6
7 PROCEDURE Start_Approval_Process
8 ( P_Incident_id IN NUMBER,
9 P_Incident_type_id IN NUMBER,
10 P_Incident_Status_Id IN NUMBER,
11 P_QA_Collection_Id IN NUMBER,
12 X_Approval_status OUT NOCOPY VARCHAR2,
13 X_Return_status OUT NOCOPY VARCHAR2,
14 X_Msg_count OUT NOCOPY NUMBER,
15 X_Msg_data OUT NOCOPY VARCHAR2 ) IS
16
17 -- Cursors
18
19 CURSOR get_det_erec_flag IS
20 SELECT NVL(detailed_erecord_req_flag ,'N')
21 FROM cs_incident_types_b
22 WHERE incident_type_id = p_incident_type_id ;
23
24 -- Local variables
25 l_det_erec_req VARCHAR2(3);
26 l_xml_doc CLOB;
27 l_return_status VARCHAR2(3) := FND_API.G_RET_STS_SUCCESS;
28 l_child_erecords EDR_ERES_EVENT_PUB.ERECORD_ID_TBL_TYPE;
29 l_event EDR_ERES_EVENT_PUB.ERES_EVENT_REC_TYPE;
30 l_qa_erecord_tbl QA_RESULT_GRP.QA_ERECORD_TBL_TYPE;
31 l_send_ackn BOOLEAN := FALSE;
32 l_txn_status VARCHAR2(30);
33 l_str VARCHAR2(240);
34 l_api_name VARCHAR2(40) := 'Start_Approval_Process';
35 -- Exceptions
36
37 BEGIN
38 -- Log the input parameters
39
40 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
41 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
42
43 dbg_msg := ('In CS_ERES_INT_PKG.START_APPROVAL_PROCESS Procedure');
44 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
45 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
46 END IF;
47
48 dbg_msg := ('P_Incident_id :'||P_Incident_id);
49 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
50 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
51 END IF;
52
53 dbg_msg := ('P_Incident_type_id :'||P_Incident_type_id);
54 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
55 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
56 END IF;
57
58 dbg_msg := ('P_Incident_status_id :'||P_Incident_status_id);
59 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
60 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
61 END IF;
62
63 dbg_msg := ('P_QA_collection_id :'||P_QA_collection_id);
64 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
65 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
66 END IF;
67 END IF;
68 END IF;
69
70 -- Populate the fixed event parameters required by the ERES event.
71
72 l_event.EVENT_NAME := 'oracle.apps.cs.sr.ServiceRequestApproval';
73 l_event.EVENT_KEY := p_incident_id;
74 l_event.ERECORD_ID := null;
75 l_event.EVENT_STATUS := null;
76 l_event.PARAM_NAME_1 := 'DEFERRED';
77 l_event.PARAM_VALUE_1 := 'Y';
78 l_event.PARAM_NAME_2 := 'POST_OPERATION_API';
79 l_event.PARAM_VALUE_2 := 'CS_ERES_INT_PKG.Post_Approval_Process('||
80 'p_incident_id =>'||p_incident_id||
81 ',P_Intermediate_Status_Id =>'||p_incident_status_id||')';
82 l_event.PARAM_NAME_3 := 'PSIG_USER_KEY_LABEL';
83 l_event.PARAM_VALUE_3 := 'CSERES';
84 l_event.PARAM_NAME_4 := 'PSIG_USER_KEY_VALUE';
85 l_event.PARAM_VALUE_4 := 'CS_ERES';
86 l_event.PARAM_NAME_5 := 'PSIG_TRANSACTION_AUDIT_ID';
87 l_event.PARAM_VALUE_5 := -1;
88 l_event.PARAM_NAME_6 := '#WF_SOURCE_APPLICATION_TYPE';
89 l_event.PARAM_VALUE_6 := 'DB';
90 l_event.PARAM_NAME_7 := '#WF_SIGN_REQUESTER';
91 l_event.PARAM_VALUE_7 := fnd_global.user_name;
92 l_event.PARAM_NAME_8 := 'TRANSFORM_XML';
93 l_event.PARAM_VALUE_8 := 'N';
94
95 -- Construct a call to the function that returns an XML document. This call will be executed by the ERES API
96 -- if approval rules are found.
97
98 -- Check what type of XML document is to be generated (Details or Light)
99
100 OPEN get_det_erec_flag;
101 FETCH get_det_erec_flag INTO l_det_erec_req;
102 CLOSE get_det_erec_flag;
103
104 IF NVL(l_det_erec_req,'N') = 'N' THEN
105 l_event.PARAM_NAME_9 := 'XML_GENERATION_API';
106 l_str := 'CS_ERES_INT_PKG.Generate_XML_Document('||p_incident_id||',''N'')';
107 l_event.PARAM_VALUE_9 := l_str;
108 ELSE
109 l_event.PARAM_NAME_9 := 'XML_GENERATION_API';
110 l_str := 'CS_ERES_INT_PKG.Generate_XML_Document('||p_incident_id||',''Y'')';
111 l_event.PARAM_VALUE_9 := l_str;
112 END IF ;
113
114 -- Construct a string to pass the FND attachment details
115 l_event.PARAM_NAME_10 := 'EDR_PSIG_ATTACHMENT';
116 l_event.PARAM_VALUE_10 := 'CS:entity=CS_INCIDENTS&'||'pk1name=INCIDENT_ID&'||'pk1value='||p_incident_id;
117
118 -- Call the QA API to get eRecord for the QA records.
119
120 IF p_qa_collection_id IS NOT NULL THEN
121
122 l_return_status := FND_API.G_RET_STS_SUCCESS;
123
124 QA_RESULT_GRP.Get_QA_Results_Erecords
125 ( p_api_version => 1.0,
126 p_init_msg_list => fnd_api.g_false,
127 p_commit => fnd_api.g_false,
128 p_Validation_Level => fnd_api.g_valid_level_full,
129 p_Collection_Id => p_qa_collection_id,
130 X_qa_erecord_tbl => l_qa_erecord_tbl,
131 x_return_status => l_return_status,
132 x_msg_count => x_msg_count,
133 x_msg_data => x_msg_data );
134
135 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
136 RAISE FND_API.G_EXC_ERROR;
137 ELSE
138 IF l_qa_erecord_tbl.COUNT > 0 THEN
139 FOR i IN 1..l_qa_erecord_tbl.COUNT
140 LOOP
141 l_child_erecords(i) := l_qa_erecord_tbl(i).erec_id;
142 END LOOP;
143 END IF;
144 END IF;
145 END IF;
146
147 -- Log the parameter being passed to the ERES API.
148
149 dbg_msg := ('P_Incident_id :'||P_Incident_id);
150
151 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
152 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
153 dbg_msg := ('Calling EDR_ERES_EVENT_PUB.RAISE_ERES_EVENT API ');
154 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
155 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
156 END IF;
157 END IF;
158 END IF;
159
160 -- Call the ERES API to raise ERES event.
161
162 l_return_status := FND_API.G_RET_STS_SUCCESS;
163
164 --dbms_output.put_line('Start Approval Process - Calling EDR_ERES_EVENT_PUB.RAISE_ERES_EVENT');
165
166 EDR_ERES_EVENT_PUB.RAISE_ERES_EVENT
167 ( p_api_version => 1.0 ,
168 p_init_msg_list => fnd_api.g_false,
169 p_validation_level => fnd_api.g_valid_level_full,
170 x_return_status => l_return_status,
171 x_msg_count => x_msg_count ,
172 x_msg_data => x_msg_data ,
173 p_child_erecords => l_child_erecords ,
174 x_event => l_event );
175
176 --dbms_output.put_line('ERES API Return Status : '||l_return_status);
177 --dbms_output.put_line('ERecord ID : '||l_event.ERECORD_ID);
178 --dbms_output.put_line('Event Status : '||l_event.event_status);
179
180 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
181 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
182 dbg_msg := ('After Calling EDR_ERES_EVENT_PUB.RAISE_ERES_EVENT API ');
183 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
184 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
185 END IF;
186
187 dbg_msg := ('EDR_ERES_EVENT_PUB.RAISE_ERES_EVENT API Return Status : '||l_return_status);
188 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
189 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
190 END IF;
191
192 dbg_msg := ('EDR_ERES_EVENT_PUB.RAISE_ERES_EVENT API Event Status : '||l_event.event_status);
193 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
194 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
195 END IF;
196
197 dbg_msg := ('EDR_ERES_EVENT_PUB.RAISE_ERES_EVENT API ERecord ID : '||l_event.ERECORD_ID);
198 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
199 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
200 END IF;
201 END IF;
202 END IF;
203
204 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
205 RAISE FND_API.G_EXC_ERROR;
206 END IF;
207
208 -- send an acknowledge to ERES about the business transaction being completed successfully
209 -- This acknowledgement will not be sent from this API if the ERES API response is 'PENDING'.
210
211 IF (l_event.event_status = 'PENDING') THEN
212 l_send_ackn := FALSE;
213 l_txn_status := 'SUCCESS';
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215 x_approval_status := 'PENDING';
216 ELSIF (l_event.event_status ='ERROR') AND (l_event.ERECORD_ID IS NOT NULL) THEN
217 l_send_ackn := TRUE;
218 l_txn_status := 'ERROR';
219 x_return_status := FND_API.G_RET_STS_ERROR;
220 x_approval_status := 'ERROR';
221 ELSIF (l_event.event_status = 'NOACTION') THEN
222
223 IF l_event.ERECORD_ID IS NOT NULL THEN
224 l_send_ackn := TRUE;
225 l_txn_status := 'SUCCESS';
226 END IF ;
227
228 x_return_status := FND_API.G_RET_STS_SUCCESS;
229 x_approval_status := 'NO_ACTION';
230 END IF ;
231
232 IF l_send_ackn = TRUE THEN
233
234 -- Log that EDR_TRANS_ACKN_PUB.SEND_ACKN is being called.
235
236 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
237 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
238 dbg_msg := ('Calling EDR_TRANS_ACKN_PUB.SEND_ACKN API ');
239 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
240 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
241 END IF;
242 END IF;
243 END IF;
244
245 l_return_status := FND_API.G_RET_STS_SUCCESS;
246
247 EDR_TRANS_ACKN_PUB.SEND_ACKN
248 ( p_api_version => 1.0,
249 p_init_msg_list => FND_API.G_TRUE ,
250 x_return_status => l_return_status,
251 x_msg_count => x_msg_count,
252 x_msg_data => x_msg_data,
253 p_event_name => l_event.event_name,
254 p_event_key => l_event.event_key,
255 p_ERECord_id => l_event.ERECORD_ID,
256 p_trans_status => l_txn_status,
257 p_ackn_by => 'Service Request Approval Process',
258 p_ackn_note => 'Service Request Approval Initiation process completed',
259 p_autonomous_commit => FND_API.G_FALSE );
260
261 -- Log output of EDR_TRANS_ACKN_PUB.SEND_ACKN call
262
263 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
264 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
265 dbg_msg := ('After Calling EDR_TRANS_ACKN_PUB.SEND_ACKN API ');
266 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
267 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
268 END IF;
269
270 dbg_msg := ('EDR_TRANS_ACKN_PUB.SEND_ACKN API Return Status : '||l_return_status);
271 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
272 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
273 END IF;
274 END IF;
275 END IF;
276
277 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
278 RAISE FND_API.G_EXC_ERROR;
279 END IF ;
280 END IF ; -- l_send_ackn
281
282 EXCEPTION
283 WHEN FND_API.G_EXC_ERROR THEN
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 FND_MSG_PUB.Count_And_Get
286 ( p_count => x_msg_count,
287 p_data => x_msg_data
288 );
289
290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
292 FND_MSG_PUB.Count_And_Get
293 ( p_count => x_msg_count,
294 p_data => x_msg_data
295 );
296 WHEN OTHERS THEN
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 FND_MSG_PUB.Count_And_Get
299 ( p_count => x_msg_count,
300 p_data => x_msg_data
301 );
302 END Start_Approval_Process ;
303
304 --------------------------------------------------------------------------------
305 -- Function Name : make_node
306 -- Parameters :
307 -- IN :
308 -- RETURN VALUE :
309 --
310 -- Description :
311 --
312 -- Modification History:
313 -- Date Name Desc
314 -- -------- -------- -----------------------------------------------------------
315 -- 10/25/05 grwang Created
316 -- 10/31/05 smisra added log messages
317 --------------------------------------------------------------------------------
318 FUNCTION make_node
319 ( p_doc IN dbms_xmldom.domdocument
320 , p_element_name IN VARCHAR2
321 , p_value IN VARCHAR2) RETURN dbms_xmldom.DOMNode AS
322
323 elem dbms_xmldom.DOMElement;
324 nelem dbms_xmldom.DOMNode;
325 agnode dbms_xmldom.DOMNode;
326 text dbms_xmldom.DOMText;
327 tempnode dbms_xmldom.DOMNode := NULL;
328 BEGIN
329
330 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
331 IF FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS') THEN
332 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', 'Inside function make_node');
333 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', 'P_element_name :' || p_element_name);
334 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', 'P_value :' || p_value);
335 END IF;
336 END IF;
337 --
338 elem := dbms_xmldom.createelement(p_doc, p_element_name);
339 agnode := dbms_xmldom.makenode(elem);
340
341 -- create a text node
342 text := dbms_xmldom.createtextnode(p_doc, p_value);
343 -- make node
344 nelem := dbms_xmldom.makeNode(text);
345
346 tempnode := dbms_xmldom.appendchild(agnode,nelem);
347
348 IF((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
349 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
350 dbg_msg := 'Returning Successfully from make_Node function';
351 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
352 END IF;
353 END IF;
354 RETURN agnode;
355 EXCEPTION
356 WHEN OTHERS THEN
357 IF((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
358 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
359 dbg_msg := 'Exception raised in make_Node function';
360 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
361 END IF;
362 END IF;
363 RETURN tempnode;
364 END make_node;
365
366 --------------------------------------------------------------------------------
367 -- Function Name : Append_ea_data
368 -- Parameters :
369 -- IN :
370 -- RETURN VALUE :
371 --
372 -- Description :
373 --
374 -- Modification History:
375 -- Date Name Desc
376 -- -------- -------- -----------------------------------------------------------
377 -- 10/25/05 grwang Created
378 -- 10/31/05 smisra added log messages
379 -- 11/07/05 smisra If get_sr_ext_attr returns an error then this function
380 -- returns NULL
381 --------------------------------------------------------------------------------
382 FUNCTION append_ea_data
383 ( P_Incident_Id IN NUMBER
384 , p_xml_doc IN CLOB) RETURN CLOB AS
385
386 xmldoc CLOB := p_xml_doc;
387
388 indomdoc dbms_xmldom.domdocument;
389 innode dbms_xmldom.domnode;
390 childnode dbms_xmldom.DOMNode;
391 srnode dbms_xmldom.DOMNode;
392 myParser dbms_xmlparser.Parser;
393
394 elem dbms_xmldom.DOMElement;
395 attrm dbms_xmldom.DOMElement;
396 topm dbms_xmldom.DOMElement;
397 topattrm dbms_xmldom.DOMElement;
398 topn dbms_xmldom.DOMNode;
399 topattrn dbms_xmldom.DOMNode;
400 nelem dbms_xmldom.DOMNode;
401 attrelem dbms_xmldom.DOMNode;
402 agnode dbms_xmldom.DOMNode;
403 attrnode dbms_xmldom.DOMNode;
404
405 attr_disp_value_node_ele dbms_xmldom.DOMElement;
406 attr_disp_value_node dbms_xmldom.DOMNode;
407
408 attr_disp_value_text_ele dbms_xmldom.DOMText;
409 attr_disp_value_text_node dbms_xmldom.DOMNode;
410
411
412 text dbms_xmldom.DOMText;
413 attrtext dbms_xmldom.DOMText;
414 tempnode dbms_xmldom.DOMNode;
415 sreanode dbms_xmldom.DOMNode;
416
417 l_extensibility_table CS_ServiceRequest_PUB.EXT_ATTR_GRP_TBL_TYPE;
418 l_ext_attr_table CS_ServiceRequest_PUB.EXT_ATTR_TBL_TYPE;
419
420 l_failed_row_id_list VARCHAR2(4000);
421 l_return_status VARCHAR2(1);
422 l_errorcode NUMBER;
423 l_msg_count NUMBER;
424 l_msg_data VARCHAR2(4000);
425
426 l_errm VARCHAR2(100);
427 l_msg VARCHAR2(250);
428
429 l_server_timezone_id VARCHAR2(15) := fnd_profile.value('SERVER_TIMEZONE_ID');
430 l_client_timezone_id VARCHAR2(15) := fnd_profile.value('CLIENT_TIMEZONE_ID');
431 l_timezone_enabled VARCHAR2(2) := fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS');
432 l_date_format VARCHAR2(240) := fnd_profile.value('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS';
433
434 CURSOR timezone_code(l_time_zone_id Number) IS -- Bug 11830349
435 Select timezone_code
436 From fnd_timezones_b
437 Where upgrade_tz_id = l_time_zone_id;
438 l_source_tz_code varchar2(50);
439 l_dest_tz_code varchar2(50);
440
441 BEGIN
442 IF((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
443 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
444 dbg_msg := 'Inside function append_ea_data';
445 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
446 END IF;
447 END IF;
448 -- Begin Bug 11830349
449 Open timezone_code(l_server_timezone_id);
450 Fetch timezone_code Into l_source_tz_code;
451 Close timezone_code;
452 Open timezone_code(l_client_timezone_id);
453 Fetch timezone_code Into l_dest_tz_code;
454 Close timezone_code;
455 -- End Bug 11830349
456 --
457 -- get sr ea data
458 --dbms_output.put_line('Going to call get sr ext data<br>');
459
460 l_return_status := FND_API.G_RET_STS_SUCCESS;
461
462 CS_SR_EXTATTRIBUTES_PVT.Get_SR_Ext_Attrs
463 ( p_api_version => 1.0
464 , p_init_msg_list => FND_API.G_FALSE
465 , p_commit => FND_API.G_FALSE
466 , p_incident_id => p_incident_id
467 , p_object_name => 'CS_SERVICE_REQUEST'
468 , x_ext_attr_grp_tbl => l_extensibility_table
469 , x_ext_attr_tbl => l_ext_attr_table
470 , x_return_status => l_return_status
471 , x_msg_count => l_msg_count
472 , x_msg_data => l_msg_data
473 );
474 IF((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
475 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
476 dbg_msg := 'Status returned by Get_SR_Ext_Attr:' || l_return_status;
477 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
478 END IF;
479 END IF;
480 --dbms_output.put_line('after call get sr ext data:' || l_return_status || '<br>');
481 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
482 RETURN NULL;
483 END IF;
484
485 --dbms_output.put_line('return status is '||l_return_status);
486 --dbms_output.put_line('number of attr grp '||l_extensibility_table.count);
487 IF l_extensibility_table IS NOT NULL AND
488 l_extensibility_table.count > 0
489 THEN
490 myParser := dbms_xmlparser.newParser;
491 dbms_xmlparser.parseBuffer(myParser, xmldoc);
492 indomdoc := dbms_xmlparser.getDocument(myParser);
493 innode := dbms_xmldom.makeNode(indomdoc);
494
495 childnode := dbms_xmldom.getfirstchild(innode);
496 srnode := dbms_xmldom.getfirstchild(childnode);
497
498 -- make sr ea node
499 elem := dbms_xmldom.createelement(indomdoc, 'EXTATTR');
500 sreanode := dbms_xmldom.makenode(elem);
501
502
503 FOR i IN l_extensibility_table.First..l_extensibility_table.LAST
504 LOOP
505
506 topm := dbms_xmldom.createelement(indomdoc, 'ATTR_GROUP');
507 topn := dbms_xmldom.makenode(topm);
508
509 -- add attribute group display name
510 tempnode := dbms_xmldom.appendchild
511 ( topn
512 , Make_Node
513 ( indomdoc
514 , 'ATTR_GROUP_DISP_NAME'
515 , l_extensibility_table(i).ATTR_GROUP_DISP_NAME
516 )
517 );
518 -- end attribute group display name
519
520
521 -- add_context
522 tempnode := dbms_xmldom.appendchild
523 ( topn
524 , Make_Node
525 ( indomdoc
526 , 'CONTEXT'
527 , l_extensibility_table(i).CONTEXT
528 )
529 );
530 -- end add_context
531
532 FOR j IN l_ext_attr_table.FIRST..l_ext_attr_table.LAST
533 LOOP
534
535 IF l_extensibility_table(i).ROW_IDENTIFIER = l_ext_attr_table(j).ROW_IDENTIFIER
536 THEN
537 -- add attributes
538 topattrm := dbms_xmldom.createelement(indomdoc, 'ATTRS');
539 topattrn := dbms_xmldom.makenode(topattrm);
540
541 -- add attr_name
542 tempnode := dbms_xmldom.appendchild
543 ( topattrn
544 , Make_Node
545 ( indomdoc
546 , 'ATTR_DISP_NAME'
547 , l_ext_attr_table(j).ATTR_DISP_NAME
548 )
549 );
550 -- attr_name eof
551
552 -- attr_value_display
553 IF l_ext_attr_table(j).ATTR_VALUE_DATE IS NOT NULL THEN
554 IF l_timezone_enabled = 'Y' THEN
555
556 tempnode := dbms_xmldom.appendchild
557 ( topattrn
558 , Make_Node
559 ( indomdoc
560 , 'ATTR_VALUE_DISPLAY'
561 , to_char(fnd_timezone_pub.adjust_datetime(l_ext_attr_table(j).ATTR_VALUE_DATE, l_source_tz_code,l_dest_tz_code),l_date_format) -- Bug 11830349
562 -- , to_char(hz_timezone_pub.convert_datetime(l_server_timezone_id,l_client_timezone_id,l_ext_attr_table(j).ATTR_VALUE_DATE),l_date_format)
563 )
564 );
565
566 ELSE
567 tempnode := dbms_xmldom.appendchild
568 ( topattrn
569 , Make_Node
570 ( indomdoc
571 , 'ATTR_VALUE_DISPLAY'
572 , to_char(l_ext_attr_table(j).ATTR_VALUE_DATE,l_date_format)
573 )
574 );
575 END IF;
576 ELSE
577 tempnode := dbms_xmldom.appendchild
578 ( topattrn
579 , Make_Node
580 ( indomdoc
581 , 'ATTR_VALUE_DISPLAY'
582 , l_ext_attr_table(j).ATTR_VALUE_DISPLAY
583 )
584 );
585 END IF ;
586 -- attr_value_display_eof
587
588 tempnode := dbms_xmldom.appendchild
589 ( topattrn
590 , Make_Node
591 ( indomdoc
592 , 'ATTR_UNIT_OF_MEASURE'
593 , l_ext_attr_table(j).ATTR_UNIT_OF_MEASURE
594 )
595 );
596 tempnode := dbms_xmldom.appendchild(topn, topattrn);
597
598 END IF;
599
600 END LOOP;
601
602 tempnode := dbms_xmldom.appendchild(sreanode, topn);
603
604 END LOOP; -- l_extensibility_table_end_loop
605
606 tempnode := dbms_xmldom.appendchild(srnode,sreanode);
607 dbms_xmldom.writetoclob(innode, xmldoc);
608
609 END IF; --if_l_extensibility_table_null_eof
610 --
611 IF((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
612 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS')) THEN
613 dbg_msg := 'Returning Successfully from function append_ea_data';
614 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.START_APPROVAL_PROCESS', dbg_msg);
615 END IF;
616 END IF;
617 --
618 RETURN xmldoc;
619 EXCEPTION
620 WHEN OTHERS THEN
621 FND_MESSAGE.SET_NAME ('CS' , 'CS_ERES_XML_GEN_FAILED');
622 FND_MESSAGE.SET_TOKEN ('SQLCODE', SQLCODE);
623 FND_MESSAGE.SET_TOKEN ('SQLERRM', SQLERRM);
624 FND_MSG_PUB.ADD;
625 RETURN NULL;
626 END Append_EA_Data ;
627
628 -- Following functions are internally used by the Generate_XML_Document while generating the
629 -- XML Document.
630 ---------------------------------------------------------------------------------------------
631
632 FUNCTION Get_Related_Objs (x_incident_id number) return cs_sr_related_OBJ_list_t as
633
634 CURSOR Get_Rel_Obj IS
635 SELECT CAST(MULTISET
636 (SELECT obj.name RelatedOB_name
637 , SrLnkEO.object_number RelatedOB_Number
638 , csz_servicerequest_util_pvt.get_rel_obj_details(SrLnkEO.object_type,
639 SrLnkEO.object_id) RelatedOB_Description
640 FROM cs_incident_links SrLnkEO
641 , jtf_objects_tl obj
642 WHERE SrLnkEO.subject_id = x_incident_id
643 AND SrLnkEO.subject_type = 'SR'
644 AND SrLnkEO.object_type <> 'SR'
645 AND obj.object_code (+) = SrLnkEO.object_type
646 AND obj.language (+) = userenv('LANG')
647 AND sysdate between nvl(SrLnkEO.start_date_active,sysdate)
648 AND nvl(SrLnkEO.end_date_active,sysdate) ) AS cs_sr_related_OBJ_list_t) RELATED_OB_LIST
649 FROM DUAL;
650
651 l_rel_onj_list cs_sr_related_OBJ_list_t;
652
653 BEGIN
654 OPEN Get_Rel_Obj;
655 FETCH Get_Rel_Obj INTO l_rel_onj_list;
656 CLOSE Get_Rel_Obj;
657
658 return l_rel_onj_list;
659 END;
660
661 FUNCTION Get_Related_SRs (x_incident_id number) return cs_sr_related_SR_list_t as
662
663 CURSOR Get_Rel_Sr IS
664 SELECT CAST(MULTISET
665 (SELECT ltype.name RelatedSR_Name
666 , status.name RelatedSR_Status
667 , fnd1.user_name RelatedSR_Created_By
668 , tlSR.summary RelatedSR_Summary
669 , SrLnkEO.object_number RelatedSR_Number
670 , severity.name RelatedSR_Severity
671 , (SELECT rs.resource_name
672 FROM jtf_rs_resource_extns_tl rs
673 WHERE rs.resource_id = relSR.incident_owner_id
674 AND rs.language = userenv('LANG')) RelatedSR_Owner
675 FROM cs_incident_links SrLnkEO
676 , cs_sr_link_types_tl ltype
677 , fnd_user fnd1
678 , cs_incidents_all_b relSR
679 , cs_incidents_all_tl tlSR
680 , cs_incident_statuses_tl status
681 , cs_incident_severities_tl severity
682 WHERE SrLnkEO.subject_id = x_incident_id
683 AND SrLnkEO.subject_type = 'SR'
684 AND SrLnkEO.object_type = 'SR'
685 AND SrLnkEO.link_type_id = ltype.link_type_id
686 AND ltype.LANGUAGE = userenv('LANG')
687 AND SrLnkEO.created_by = fnd1.user_id
688 AND SrLnkEO.object_number = relSR.incident_number
689 AND relSR.incident_status_id = status.incident_status_id
690 AND status.LANGUAGE = userenv('LANG')
691 AND relSR.incident_severity_id = severity.incident_severity_id
692 AND severity.LANGUAGE = userenv('LANG')
693 AND tlSR.incident_id = relSR.incident_id
694 AND tlSR.language = userenv('lang')
695 AND sysdate between nvl(SrLnkEO.start_date_active,sysdate)
696 AND nvl(SrLnkEO.end_date_active,sysdate) ) AS cs_sr_related_SR_list_t) RELATED_SR_LIST
697 FROM DUAL;
698
699 l_rel_sr_list cs_sr_related_SR_list_t;
700
701 BEGIN
702 OPEN Get_Rel_Sr;
703 FETCH Get_Rel_Sr INTO l_rel_sr_list;
704 CLOSE Get_Rel_Sr;
705
706 return l_rel_sr_list;
707 END;
708
709 FUNCTION Get_SR_Notes (x_incident_id number,
710 l_source_timezone_id number,
711 l_desc_timezone_id number,
712 l_date_format varchar2) return cs_sr_note_list_t as
713
714 l_source_tz_code varchar2(50);
715 l_dest_tz_code varchar2(50);
716
717
718
719 CURSOR Get_Notes IS
720 SELECT CAST(MULTISET
721 (SELECT nvl2(ext_usert.resource_name, ext_usert.resource_name, fnd1.user_name) as Note_Created_By
722 , DECODE(JNB.entered_date,'','',to_char(fnd_timezone_pub.adjust_datetime(JNB.ENTERED_DATE, l_source_tz_code,l_dest_tz_code),l_date_format)) Note_Creation_Date -- Bug 11830349
723 -- , DECODE(JNB.entered_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,JNB.ENTERED_DATE),l_date_format)) Note_Creation_Date
724 , FLS.MEANING Note_Type
725 , FLP.MEANING Note_Visibility
726 , JNT.NOTES Note_Description
727 , JNT.NOTES_DETAIL Note_Detail
728 FROM JTF_NOTES_B JNB ,
729 JTF_NOTES_TL JNT ,
730 FND_LOOKUPS FLS ,
731 FND_LOOKUPS FLP,
732 jtf_rs_resource_extns ext_userb,
733 jtf_rs_resource_extns_tl ext_usert,
734 FND_USER fnd1
735 WHERE JNB.JTF_NOTE_ID = JNT.JTF_NOTE_ID
736 AND JNT.LANGUAGE = USERENV('LANG')
737 AND FLS.LOOKUP_TYPE(+) = 'JTF_NOTE_TYPE'
738 AND FLS.LOOKUP_CODE(+) = JNB.NOTE_TYPE
739 AND FLP.lookup_type = 'JTF_NOTE_STATUS'
740 AND FLP.lookup_code = JNB.note_status
741 AND JNB.source_object_code = 'SR'
742 AND JNB.source_object_id = x_incident_id
743 AND ((JNB.note_status <> 'P') OR
744 (JNB.note_status = 'P' and JNB.created_by = fnd_profile.value_wnps('USER_ID') ))
745 AND JNB.created_by = fnd1.user_id
746 AND ext_userb.user_id (+) = fnd1.user_id
747 AND ext_usert.language (+)= userenv('LANG')
748 AND ext_userb.resource_id = ext_usert.resource_id (+)
749 AND ext_userb.category = ext_usert.category (+)
750 order by JNB.entered_date ) AS cs_sr_note_list_t) NOTE_LIST
751 FROM DUAL;
752
753 l_note_list cs_sr_note_list_t;
754 CURSOR timezone_code(l_time_zone_id Number) IS -- Bug 11830349
755 Select timezone_code
756 From fnd_timezones_b
757 Where upgrade_tz_id = l_time_zone_id;
758 BEGIN
759 -- Begin Bug 11830349
760 Open timezone_code(l_source_timezone_id);
761 Fetch timezone_code Into l_source_tz_code;
762 Close timezone_code;
763 Open timezone_code(l_desc_timezone_id);
764 Fetch timezone_code Into l_dest_tz_code;
765 Close timezone_code;
766 -- End Bug 11830349
767
768 OPEN Get_Notes;
769 FETCH Get_Notes INTO l_note_list;
770 CLOSE Get_Notes;
771
772 return l_note_list;
773 END;
774
775
776 FUNCTION Get_SR_Tasks (x_incident_id number,
777 l_source_timezone_id number,
778 l_desc_timezone_id number,
779 l_date_format varchar2) return cs_sr_task_list_t as
780
781
782 l_source_tz_code varchar2(50);
783 l_dest_tz_code varchar2(50);
784
785 CURSOR Get_Tasks IS
786 SELECT CAST(MULTISET
787 (SELECT
788 -- DECODE(jtf1B.creation_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.creation_date),l_date_format)) Task_Creation_Date
789 DECODE(jtf1B.creation_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.creation_date, l_source_tz_code,l_dest_tz_code),l_date_format)) Task_Creation_Date -- Bug 11830349
790 , jtf1B.task_number Task_Number
791 , type.name Task_Type
792 , jtf1T.TASK_NAME Task_Name
793 , jtf1T.DESCRIPTION Task_Description
794 , priority.name Task_Priority
795 , statusT.name Task_Status
796 , jtf_task_utl.get_owner(jtf1B.owner_type_code, jtf1B.owner_id) Task_Owner
797 , obj_vl.name Task_ownertype
798 , jtf2.task_number Task_parenttasknumber
799 , jtf1B.planned_effort task_planned_effort
800 , (SELECT unit_of_measure
801 FROM mtl_units_of_measure_vl um
802 WHERE um.uom_code = jtf1B.planned_effort_uom) as task_planned_effort_uom
803 , jtf1B.actual_effort task_actual_effort
804 , (SELECT unit_of_measure
805 FROM mtl_units_of_measure_tl um
806 WHERE um.uom_code = jtf1B.actual_effort_uom
807 AND um.LANGUAGE = USERENV('LANG') ) as task_actual_effort_uom
808 , jtf1B.duration task_duration
809 , (SELECT unit_of_measure
810 FROM mtl_units_of_measure_tl um
811 WHERE um.uom_code = jtf1B.duration_uom
812 AND um.LANGUAGE = USERENV('LANG') ) AS task_duration_uom
813 /*, DECODE(jtf1B.planned_start_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.planned_start_date),l_date_format)) task_planned_start_date
814 , DECODE(jtf1B.planned_end_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.planned_end_date),l_date_format)) task_planned_end_date
815 , DECODE(jtf1B.scheduled_start_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.scheduled_start_date),l_date_format)) task_scheduled_start_date
816 , DECODE(jtf1B.scheduled_end_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.scheduled_end_date),l_date_format)) task_scheduled_end_date
817 , DECODE(jtf1B.actual_start_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.ACTUAL_START_DATE),l_date_format)) task_actual_start_date
818 , DECODE(jtf1B.actual_end_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.ACTUAL_END_DATE),l_date_format)) task_actual_end_date
819 */
820 -- Bug 11830349
821 , DECODE(jtf1B.planned_start_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.planned_start_date, l_source_tz_code,l_dest_tz_code),l_date_format)) task_planned_start_date
822 , DECODE(jtf1B.planned_end_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.planned_end_date, l_source_tz_code,l_dest_tz_code),l_date_format)) task_planned_end_date
823 , DECODE(jtf1B.scheduled_start_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.scheduled_start_date, l_source_tz_code,l_dest_tz_code),l_date_format)) task_scheduled_start_date
824 , DECODE(jtf1B.scheduled_end_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.scheduled_end_date, l_source_tz_code,l_dest_tz_code),l_date_format)) task_scheduled_end_date
825 , DECODE(jtf1B.actual_start_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.ACTUAL_START_DATE, l_source_tz_code,l_dest_tz_code),l_date_format)) task_actual_start_date
826 , DECODE(jtf1B.actual_end_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.ACTUAL_END_DATE, l_source_tz_code,l_dest_tz_code),l_date_format)) task_actual_end_date
827 FROM JTF_TASKS_TL jtf1T, JTF_TASKS_B jtf1B
828 , jtf_tasks_b jtf2
829 , jtf_task_types_tl type
830 , jtf_task_priorities_tl priority
831 , JTF_TASK_STATUSES_TL statusT, JTF_TASK_STATUSES_B statusB
832 , jtf_objects_tl obj_vl
833 WHERE jtf1B.source_object_type_code='SR'
834 AND jtf1B.source_object_id = x_incident_id
835 AND jtf1B.TASK_ID = jtf1T.TASK_ID
836 AND jtf1T.LANGUAGE = userenv('LANG')
837 AND type.task_type_id = jtf1B.task_type_id
838 AND type.LANGUAGE = userenv('LANG')
839 AND priority.task_priority_id = jtf1B.task_priority_id
840 AND priority.LANGUAGE = userenv('LANG')
841 AND statusB.TASK_STATUS_ID = statusT.TASK_STATUS_ID
842 AND statusT.LANGUAGE = userenv('LANG')
843 AND statusb.usage = 'TASK'
844 AND statusB.task_status_id = jtf1B.task_status_id
845 AND obj_vl.object_code = jtf1B.owner_type_code
846 AND obj_vl.LANGUAGE = userenv('LANG')
847 AND jtf1B.parent_task_id = jtf2.task_id (+)
848 ORDER BY jtf1B.creation_date) AS cs_sr_task_list_t) TASK_LIST
849 FROM DUAL;
850
851 l_task_list cs_sr_task_list_t;
852 CURSOR timezone_code(l_time_zone_id Number) IS -- Bug 11830349
853 Select timezone_code
854 From fnd_timezones_b
855 Where upgrade_tz_id = l_time_zone_id;
856 BEGIN
857 -- Begin Bug 11830349
858 Open timezone_code(l_source_timezone_id);
859 Fetch timezone_code Into l_source_tz_code;
860 Close timezone_code;
861 Open timezone_code(l_desc_timezone_id);
862 Fetch timezone_code Into l_dest_tz_code;
863 Close timezone_code;
864 -- End Bug 11830349
865 OPEN Get_Tasks;
866 FETCH Get_Tasks INTO l_task_list;
867 CLOSE Get_Tasks;
868
869 return l_task_list;
870 END;
871
872 --------------------------------------------------------------------------------
873 -- Modification History:
874 -- Date Name Desc
875 -- -------- -------- -----------------------------------------------------------
876 -- 11/07/05 smisra In case of exception, this function returns NULL
877 -- 11/07/05 smisra Called append_ea_data only if SR has some ext records
878 --------------------------------------------------------------------------------
879
880 FUNCTION Generate_XML_Document
881 ( P_Incident_Id IN NUMBER,
882 P_Detailed_xml_reqd IN VARCHAR2 ) RETURN CLOB AS
883
884 -- Local Variables
885
886 Ctx1 DBMS_XMLGEN.ctxHandle;
887 Ctx2 DBMS_XMLGEN.ctxHandle;
888 xmldoc CLOB;
889 pass BOOLEAN;
890 l_query VARCHAR2(32000) := null;
891 l_api_name VARCHAR2(40) := 'Generate_XML_Document';
892 l_server_timezone_id VARCHAR2(15) := fnd_profile.value('SERVER_TIMEZONE_ID');
893 l_client_timezone_id VARCHAR2(15) := fnd_profile.value('CLIENT_TIMEZONE_ID');
894 l_timezone_enabled VARCHAR2(2) := fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS');
895 --l_display_timezone_id VARCHAR2(15);
896 l_date_format VARCHAR2(240) := fnd_profile.value('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS';
897 l_ext_rec_count NUMBER;
898
899 --New Cursors and Vars
900
901 l_display_timezone_id NUMBER;
902 l_source_timezone_id NUMBER;
903 l_desc_timezone_id NUMBER;
904 l_source_tz_code varchar2(50);
905 l_dest_tz_code varchar2(50);
906
907 CURSOR GET_SR (v_display_timezone_id NUMBER,
908 v_source_timezone_id NUMBER,
909 v_desc_timezone_id NUMBER,
910 v_date_format VARCHAR2) IS
911 SELECT sr.incident_id Incident_Id
912 ,sr.incident_number Incident_Number
913 ,sr.incident_type_id
914 ,sr.incident_status_id Incident_Status_Id
915 ,sr.incident_severity_id Incident_Severity_Id
916 ,sr.incident_urgency_id Incident_Urgency_Id
917 ,sr.owner_group_id Sr_Group_Id
918 ,sr.incident_owner_id Sr_Owner_Id
919 ,sr.problem_code Problem_code_id
920 ,sr.resolution_code Resolution_code_id
921 ,tl.summary
922 ,tl.resolution_summary
923 ,sr.publish_flag Publish_Flag
924 ,tl.summary Problem_Summary
925 ,sr.time_zone_id TimeZone_Id
926 ,sr.customer_id Customer_Id
927 ,sr.account_id Account_Id
928 ,sr.inventory_item_id Inventory_Item_Id
929 -- Bug 11830349
930 ,nvl2(sr.incident_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.incident_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) reported_date
931 ,nvl2(sr.incident_last_modified_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.incident_last_modified_date, l_source_tz_code,l_dest_tz_code),v_date_format),null)Last_Update_Date
932 ,nvl2(sr.incident_occurred_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.incident_occurred_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) Incident_Date
933 ,nvl2(sr.close_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.close_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) Close_Date
934 ,nvl2(sr.incident_resolved_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.incident_resolved_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) incident_resolved_date
935 ,nvl2(sr.obligation_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.obligation_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) respond_by_date
936 ,nvl2(sr.expected_resolution_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.expected_resolution_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) resolve_by_date
937 ,nvl2(sr.inc_responded_by_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.inc_responded_by_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) inc_responded_by_date
938 ,nvl2(sr.actual_resolution_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.actual_resolution_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) Actual_Resolution_Date
939 /* ,nvl2(sr.incident_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id, sr.incident_date),v_date_format),null) reported_date
940 ,nvl2(sr.incident_last_modified_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.incident_last_modified_date),v_date_format),null)Last_Update_Date
941 ,nvl2(sr.incident_occurred_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.incident_occurred_date),v_date_format),null) Incident_Date
942 ,nvl2(sr.close_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.close_date),v_date_format),null) Close_Date
943 ,nvl2(sr.incident_resolved_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.incident_resolved_date),v_date_format),null) incident_resolved_date
944 ,nvl2(sr.obligation_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.obligation_date),v_date_format),null) respond_by_date
945 ,nvl2(sr.expected_resolution_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.expected_resolution_date),v_date_format),null) resolve_by_date
946 ,nvl2(sr.inc_responded_by_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.inc_responded_by_date),v_date_format),null) inc_responded_by_date
947 ,nvl2(sr.actual_resolution_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.actual_resolution_date),v_date_format),null) Actual_Resolution_Date
948 */
949 ,sr.status_flag Status_Flag_Code
950 ,sr.created_by Created_By_Id
951 ,sr.customer_product_id Customer_Product_Id
952 ,sr.org_id Organization_Id
953 ,sr.inv_organization_id Inventory_Org_Id
954 ,instance.instance_number Instance_Number
955 ,nvl(instance.serial_number,sr.current_serial_number) Serial_Number
956 ,nvl(instance.external_reference,sr.external_reference) Tag_Number
957 , nvl2(sr.customer_product_id, instance.inventory_revision, sr.inv_item_revision) Item_Revision
958 , nvl2(sr.customer_product_id, (select instance.inventory_revision
959 from mtl_system_items_b_kfv product_a,
960 csi_item_instances instance
961 where product_a.inventory_item_id = instance.inventory_item_id
962 and product_a.organization_id = sr.inv_organization_id
963 and sr.cp_component_id=instance.instance_id), sr.inv_component_version) Component_Revision
964 , nvl2(sr.customer_product_id, (select instance.inventory_revision
965 from mtl_system_items_b_kfv product_b,
966 csi_item_instances instance
967 where product_b.inventory_item_id = instance.inventory_item_id
968 and product_b.organization_id = sr.inv_organization_id
969 and sr.cp_subcomponent_id = instance.instance_id ), sr.inv_subcomponent_version) Sub_Component_Revision
970 ,sr.incident_attribute_1 Attribute1
971 ,sr.incident_attribute_2 Attribute2
972 ,sr.incident_attribute_3 Attribute3
973 ,sr.incident_attribute_4 Attribute4
974 ,sr.incident_attribute_5 Attribute5
975 ,sr.incident_attribute_6 Attribute6
976 ,sr.incident_attribute_7 Attribute7
977 ,sr.incident_attribute_8 Attribute8
978 ,sr.incident_attribute_9 Attribute9
979 ,sr.incident_attribute_10 Attribute10
980 ,sr.incident_attribute_11 Attribute11
981 ,sr.incident_attribute_12 Attribute12
982 ,sr.incident_attribute_13 Attribute13
983 ,sr.incident_attribute_14 Attribute14
984 ,sr.incident_attribute_15 Attribute15
985 ,sr.incident_context Incident_Context
986 ,sr.external_attribute_1 Ext_Attribute1
987 ,sr.external_attribute_2 Ext_Attribute2
988 ,sr.external_attribute_3 Ext_Attribute3
989 ,sr.external_attribute_4 Ext_Attribute4
990 ,sr.external_attribute_5 Ext_Attribute5
991 ,sr.external_attribute_6 Ext_Attribute6
992 ,sr.external_attribute_7 Ext_Attribute7
993 ,sr.external_attribute_8 Ext_Attribute8
994 ,sr.external_attribute_9 Ext_Attribute9
995 ,sr.external_attribute_10 Ext_Attribute10
996 ,sr.external_attribute_11 Ext_Attribute11
997 ,sr.external_attribute_12 Ext_Attribute12
998 ,sr.external_attribute_13 Ext_Attribute13
999 ,sr.external_attribute_14 Ext_Attribute14
1000 ,sr.external_attribute_15 Ext_Attribute15
1001 ,sr.external_context Ext_Context
1002 ,sr.sr_creation_channel
1003 ,sr.contract_service_id
1004 ,sr.category_id
1005 ,sr.system_id
1006 ,sr.inv_component_id
1007 ,sr.cp_subcomponent_id
1008 ,sr.resource_type
1009 ,sr.incident_LOCATION_ID
1010 ,sr.incident_address
1011 ,nvl2(sr.incident_city, ','||sr.incident_city, NULL)
1012 ,nvl2(sr.incident_state, ', ' ||sr.incident_state, NULL)
1013 ,nvl2(sr.incident_province, ', '||sr.incident_province, NULL)
1014 ,nvl2(sr.incident_postal_code, ' '||sr.incident_postal_code, NULL)
1015 ,nvl2(sr.incident_country, ' ' ||sr.incident_country, NULL)
1016 ,instance.inventory_item_id
1017 ,instance.instance_id
1018 ,sr.cp_component_id
1019 ,sr.inv_subcomponent_id
1020 ,sr.incident_location_type
1021 FROM cs_incidents_b_sec sr
1022 ,cs_incidents_all_tl tl
1023 ,csi_item_instances instance
1024 WHERE sr.incident_id = tl.incident_id
1025 AND tl.language = userenv('lang')
1026 AND sr.incident_id = P_Incident_Id
1027 AND sr.inventory_item_id = instance.inventory_item_id (+)
1028 AND sr.customer_product_id = instance.instance_id (+);
1029
1030 l_Incident_Id NUMBER;
1031 l_Incident_Number VARCHAR2(64);
1032 l_incident_type_id NUMBER;
1033 l_Incident_Status_Id NUMBER;
1034 l_Incident_Severity_Id NUMBER;
1035 l_Incident_Urgency_Id NUMBER;
1036 l_Sr_Group_Id NUMBER;
1037 l_Sr_Owner_Id NUMBER;
1038 l_Problem_code_id VARCHAR2(30);
1039 l_Resolution_code_id VARCHAR2(30);
1040 l_summary VARCHAR2(240);
1041 l_resolution_summary VARCHAR2(250);
1042 l_Publish_Flag VARCHAR2(1);
1043 l_Problem_Summary VARCHAR2(240);
1044 l_TimeZone_Id NUMBER;
1045 l_Customer_Id NUMBER;
1046 l_Account_Id NUMBER;
1047 l_Inventory_Item_Id NUMBER;
1048 l_reported_date VARCHAR2(30);
1049 l_Last_Update_Date VARCHAR2(30);
1050 l_Incident_Date VARCHAR2(30);
1051 l_Close_Date VARCHAR2(30);
1052 l_incident_resolved_date VARCHAR2(30);
1053 l_respond_by_date VARCHAR2(30);
1054 l_resolve_by_date VARCHAR2(30);
1055 l_inc_responded_by_date VARCHAR2(30);
1056 l_Actual_Resolution_Date VARCHAR2(30);
1057 l_Status_Flag_Code VARCHAR2(3);
1058 l_Created_By_Id NUMBER;
1059 l_Customer_Product_Id NUMBER;
1060 l_Organization_Id NUMBER;
1061 l_Inventory_Org_Id NUMBER;
1062 l_Instance_Number VARCHAR2(30);
1063 l_Serial_Number VARCHAR2(30);
1064 l_Tag_Number VARCHAR2(30);
1065 l_Item_Revision VARCHAR2(240);
1066 l_Component_Revision VARCHAR2(90);
1067 l_Sub_Component_Revision VARCHAR2(90);
1068 l_Attribute1 VARCHAR2(150);
1069 l_Attribute2 VARCHAR2(150);
1070 l_Attribute3 VARCHAR2(150);
1071 l_Attribute4 VARCHAR2(150);
1072 l_Attribute5 VARCHAR2(150);
1073 l_Attribute6 VARCHAR2(150);
1074 l_Attribute7 VARCHAR2(150);
1075 l_Attribute8 VARCHAR2(150);
1076 l_Attribute9 VARCHAR2(150);
1077 l_Attribute10 VARCHAR2(150);
1078 l_Attribute11 VARCHAR2(150);
1079 l_Attribute12 VARCHAR2(150);
1080 l_Attribute13 VARCHAR2(150);
1081 l_Attribute14 VARCHAR2(150);
1082 l_Attribute15 VARCHAR2(150);
1083 l_Incident_Context VARCHAR2(30);
1084 l_Ext_Attribute1 VARCHAR2(150);
1085 l_Ext_Attribute2 VARCHAR2(150);
1086 l_Ext_Attribute3 VARCHAR2(150);
1087 l_Ext_Attribute4 VARCHAR2(150);
1088 l_Ext_Attribute5 VARCHAR2(150);
1089 l_Ext_Attribute6 VARCHAR2(150);
1090 l_Ext_Attribute7 VARCHAR2(150);
1091 l_Ext_Attribute8 VARCHAR2(150);
1092 l_Ext_Attribute9 VARCHAR2(150);
1093 l_Ext_Attribute10 VARCHAR2(150);
1094 l_Ext_Attribute11 VARCHAR2(150);
1095 l_Ext_Attribute12 VARCHAR2(150);
1096 l_Ext_Attribute13 VARCHAR2(150);
1097 l_Ext_Attribute14 VARCHAR2(150);
1098 l_Ext_Attribute15 VARCHAR2(150);
1099 l_Ext_Context VARCHAR2(30);
1100 l_sr_creation_channel VARCHAR2(50);
1101 l_contract_service_id NUMBER;
1102 l_category_id NUMBER;
1103 l_system_id NUMBER;
1104 l_inv_component_id NUMBER;
1105 l_cp_subcomponent_id NUMBER;
1106 l_resource_type_code VARCHAR2(30);
1107 l_incident_LOCATION_ID NUMBER;
1108 l_incident_addr VARCHAR2(960);
1109 l_incident_city VARCHAR2(60);
1110 l_incident_state VARCHAR2(60);
1111 l_incident_province VARCHAR2(60);
1112 l_incident_postal_code VARCHAR2(60);
1113 l_incident_country VARCHAR2(60);
1114 l_instance_inventory_item_id NUMBER;
1115 l_instance_id NUMBER;
1116 l_cp_component_id NUMBER;
1117 l_inv_subcomponent_id NUMBER;
1118 l_incident_location_type VARCHAR2(30);
1119
1120 --:Incident_Type
1121 --:Detailed_Erecord
1122
1123 CURSOR Incident_Type (v_incident_type_id NUMBER) IS
1124 SELECT typest.name, nvl(typesb.detailed_erecord_req_flag,'N') Detailed_Erecord
1125 FROM cs_incident_types_tl typest,
1126 cs_incident_types_b typesb
1127 WHERE typesb.incident_type_id = v_incident_type_id
1128 AND typesb.incident_type_id = typest.incident_type_id
1129 AND typest.language = userenv('LANG');
1130
1131 l_Incident_Type VARCHAR2(90); -- Bug 8365703 ,changed length from 30 to 90, vpremach
1132 l_Detailed_Erecord VARCHAR2(1);
1133
1134 --:Incident_Status
1135 --:status_sort_order
1136 CURSOR Get_Inc_Status ( v_incident_status_id NUMBER) IS
1137 SELECT status.name ,status_b.sort_order
1138 FROM cs_incident_statuses_tl status, cs_incident_statuses_b status_b
1139 WHERE status.incident_status_id = v_incident_status_id
1140 AND status.incident_status_id = status_b.incident_status_id
1141 AND status.language = userenv('LANG');
1142
1143 l_Incident_Status VARCHAR2(30);
1144 l_status_sort_order NUMBER;
1145
1146 --:Incident_Severity
1147 --:Sev_Importance_Level
1148 CURSOR Get_Inc_Severity (v_incident_severity_id NUMBER) IS
1149 SELECT sevt.name, sevb.importance_level
1150 FROM cs_incident_severities_tl sevt, cs_incident_severities_b sevb
1151 WHERE sevb.incident_severity_id = v_incident_severity_id
1152 AND sevt.incident_severity_id = sevb.incident_severity_id
1153 AND sevt.language = userenv('LANG');
1154
1155 l_Incident_Severity VARCHAR2(80);
1156 l_Sev_Importance_Level NUMBER;
1157
1158 --:Incident_Urgency
1159 CURSOR Get_Inc_Urg (v_incident_urgency_id NUMBER) IS
1160 SELECT urgency.name
1161 FROM cs_incident_urgencies_tl urgency
1162 WHERE urgency.incident_urgency_id = v_incident_urgency_id
1163 AND urgency.language = userenv('LANG');
1164
1165 l_Incident_Urgency VARCHAR2(30);
1166
1167 -- :Sr_Group
1168 CURSOR Get_Group (v_owner_group_id NUMBER) IS
1169 SELECT gr.group_name
1170 FROM jtf_rs_groups_tl gr
1171 WHERE gr.group_id =v_owner_group_id
1172 AND gr.LANGUAGE = userenv('LANG');
1173
1174 l_Sr_Group VARCHAR2(60);
1175
1176 -- :Sr_Owner
1177 CURSOR Get_Resource (v_incident_owner_id NUMBER) IS
1178 SELECT rs.resource_name
1179 FROM jtf_rs_resource_extns_tl rs
1180 WHERE rs.resource_id = v_incident_owner_id
1181 AND language = userenv('LANG');
1182
1183 l_Sr_Owner VARCHAR2(360);
1184 --
1185
1186 -- :Problem_code
1187 -- :Resolution_code
1188 -- :Sr_Creation_Channel
1189 -- :Customer_Type
1190 CURSOR Get_Cs_Lookup (v_lookup_code VARCHAR2, v_lookup_type VARCHAR2) IS
1191 SELECT meaning
1192 FROM FND_LOOKUP_VALUES
1193 WHERE lookup_code = v_lookup_code
1194 AND lookup_type = v_lookup_type
1195 AND LANGUAGE = userenv('LANG')
1196 AND View_APPLICATION_ID = 170
1197 AND SECURITY_GROUP_ID = fnd_global.lookup_security_group(LOOKUP_TYPE,
1198 VIEW_APPLICATION_ID);
1199
1200 l_Problem_code VARCHAR2(80);
1201 l_Resolution_code VARCHAR2(80);
1202 l_Sr_Creation_Channel_Name VARCHAR2(80);
1203 l_Customer_Type VARCHAR2(80);
1204
1205 --:Created_By
1206 CURSOR Get_Fnd_User (v_user_id NUMBER) IS
1207 SELECT usr.user_name
1208 FROM fnd_user usr
1209 WHERE usr.user_id = v_user_id;
1210
1211 l_Created_By VARCHAR2(100);
1212
1213 -- :Contact_Type
1214 CURSOR Get_Contact_Type (v_incident_id NUMBER) IS
1215 SELECT cont_type_lkup.meaning
1216 FROM FND_LOOKUP_VALUES cont_type_lkup
1217 ,cs_hz_sr_contact_points sr_cont
1218 WHERE sr_cont.contact_type=cont_type_lkup.lookup_code
1219 AND cont_type_lkup.lookup_type='CS_SR_CONTACT_TYPE'
1220 AND sr_cont.incident_id = v_incident_id
1221 ANd sr_cont.primary_flag = 'Y'
1222 AND cont_type_lkup.LANGUAGE = userenv('LANG')
1223 AND cont_type_lkup.View_APPLICATION_ID = 170
1224 AND cont_type_lkup.SECURITY_GROUP_ID = fnd_global.lookup_security_group(cont_type_lkup.LOOKUP_TYPE,
1225 cont_type_lkup.VIEW_APPLICATION_ID);
1226 l_Contact_Type VARCHAR2(80);
1227
1228
1229 -- :Contact_Name
1230 CURSOR Get_Contact_Name (v_incident_id NUMBER, v_customer_id NUMBER) IS
1231 SELECT CSZ_SERVICEREQUEST_UTIL_PVT.get_contact_name(sr_cont.contact_type,
1232 sr_cont.party_id,
1233 v_customer_id)
1234 FROM cs_hz_sr_contact_points sr_cont
1235 WHERE sr_cont.incident_id = v_incident_id
1236 AND sr_cont.primary_flag = 'Y';
1237
1238 l_Contact_Name VARCHAR2(360);
1239
1240 --:TimeZone_Name
1241 --:display_timezone
1242 CURSOR Get_Timezone (v_time_zone_id NUMBER) IS
1243 SELECT tz_tl.name
1244 FROM fnd_timezones_b tz, fnd_timezones_tl tz_tl
1245 WHERE tz.upgrade_tz_id = v_time_zone_id
1246 AND tz.TIMEZONE_CODE = tz_tl.TIMEZONE_CODE
1247 AND tz_tl.language = USERENV('LANG');
1248
1249 l_TimeZone_Name VARCHAR2(80);
1250 l_display_timezone VARCHAR2(80);
1251
1252 -- :account_number
1253 CURSOR Get_Account (v_account_id NUMBER) IS
1254 SELECT account.account_number
1255 FROM hz_cust_accounts account
1256 WHERE account.cust_account_id = v_account_id;
1257
1258 l_account_number VARCHAR2(30);
1259
1260 -- :Product
1261 -- :Product_Description
1262 CURSOR Get_Product (v_inventory_item_id NUMBER, v_inv_organization_id NUMBER) IS
1263 SELECT concatenated_segments, description
1264 FROM MTL_SYSTEM_ITEMS_VL
1265 WHERE inventory_item_id = v_inventory_item_id
1266 AND organization_id = v_inv_organization_id;
1267
1268 l_Product VARCHAR2(40);
1269 l_Product_Description VARCHAR2(240);
1270
1271 -- :Contract_Number
1272 -- :Contract_Service
1273 -- :Contract_Coverage
1274 CURSOR Get_Contract (v_contract_service_id NUMBER) IS
1275 SELECT contract_number, service_description, coverage_description
1276 FROM oks_ent_line_details_v
1277 WHERE service_line_id = v_contract_service_id;
1278
1279 l_Contract_Number VARCHAR2(120);
1280 l_Contract_Service VARCHAR2(240);
1281 l_Contract_Coverage VARCHAR2(1995);
1282
1283 --:Customer_Number
1284 --:Customer_Name
1285 --:customer_phone
1286 --:Customer_Email
1287 --:party_type
1288 CURSOR Get_Party (v_customer_id NUMBER) IS
1289 SELECT party.party_number,
1290 party.party_name,
1291 nvl2(party.primary_phone_country_code, party.primary_phone_country_code||'-',null)||
1292 nvl2(party.primary_phone_area_code, party.primary_phone_area_code||'-',null)||
1293 party.primary_phone_number customer_phone,
1294 party.email_address,
1295 party.party_type
1296 FROM hz_parties party
1297 WHERE party.party_id = v_customer_id;
1298
1299 l_Customer_Number VARCHAR2(30);
1300 l_Customer_Name VARCHAR2(360);
1301 l_customer_phone VARCHAR2(70);
1302 l_Customer_Email VARCHAR2(2000);
1303 l_party_type VARCHAR2(30);
1304
1305 ---------------------------------
1306
1307 --:Contact_Phone_Number
1308 -- :Contact_Telephone_Type
1309 CURSOR Get_Contact_Phone (v_incident_id NUMBER) IS
1310 SELECT ar.meaning PHONE_TYPE,
1311 nvl2(party_cont.phone_country_code, party_cont.phone_country_code || '-',null ) ||
1312 nvl2(party_cont.phone_area_code, party_cont.phone_area_code || '-', null) ||
1313 party_cont.phone_number PHONE_NUMBER
1314 FROM hz_contact_points party_cont,
1315 cs_hz_sr_contact_points sr_cont,
1316 FND_LOOKUP_VALUES ar
1317 WHERE sr_cont.incident_id = v_incident_id
1318 AND sr_cont.contact_point_id = party_cont.contact_point_id
1319 AND sr_cont.contact_type <> 'EMPLOYEE'
1320 AND party_cont.contact_point_type = 'PHONE'
1321 AND party_cont.phone_line_type = ar.lookup_code
1322 AND ar.lookup_type = 'PHONE_LINE_TYPE'
1323 AND ar.LANGUAGE = userenv('LANG')
1324 AND ar.VIEW_APPLICATION_ID = 222
1325 AND ar.SECURITY_GROUP_ID = 0
1326 AND sr_cont.primary_flag='Y'
1327 UNION
1328 SELECT hrl.meaning PHONE_TYPE,
1329 pp.phone_number PHONE_NUMBER
1330 FROM cs_hz_sr_contact_points sr_cont,
1331 per_phones pp,
1332 hr_lookups hrl
1333 WHERE sr_cont.incident_id = v_incident_id
1334 AND sr_cont.contact_type = 'EMPLOYEE'
1335 AND pp.phone_id = sr_cont.contact_point_id
1336 AND pp.parent_table = 'PER_ALL_PEOPLE_F'
1337 AND pp.phone_type = hrl.lookup_code
1338 AND hrl.lookup_type = 'PHONE_TYPE'
1339 AND sr_cont.contact_point_type = 'PHONE'
1340 AND sr_cont.primary_flag='Y';
1341
1342 l_Contact_Phone_Number VARCHAR2(70);
1343 l_Contact_Telephone_Type VARCHAR2(80);
1344
1345 --:Contact_Email
1346 CURSOR Get_Contact_Email (v_incident_id NUMBER) IS
1347 SELECT party_cont.email_address EMAIL
1348 FROM hz_contact_points party_cont,
1349 cs_hz_sr_contact_points sr_cont
1350 WHERE sr_cont.incident_id = v_incident_id
1351 AND sr_cont.contact_point_id = party_cont.contact_point_id
1352 AND sr_cont.contact_type <> 'EMPLOYEE'
1353 AND party_cont.contact_point_type = 'EMAIL'
1354 AND sr_cont.primary_flag = 'Y'
1355 AND party_cont.email_address is not null
1356 UNION
1357 SELECT pap.email_address EMAIL
1358 FROM cs_hz_sr_contact_points sr_cont,
1359 per_all_people_f pap
1360 WHERE sr_cont.incident_id = v_incident_id
1361 AND sr_cont.contact_type = 'EMPLOYEE'
1362 AND pap.person_id = sr_cont.party_id
1363 AND sr_cont.primary_flag = 'Y'
1364 AND pap.email_address is not null
1365 AND sr_cont.contact_point_type = 'EMAIL';
1366
1367 l_Contact_Email VARCHAR2(2000);
1368
1369 --:Item_Category
1370 CURSOR Get_Item_Category (v_category_id NUMBER) IS
1371 SELECT cat.concatenated_segments
1372 FROM mtl_categories_b_kfv cat
1373 WHERE cat.category_id = v_category_id;
1374
1375 l_Item_Category VARCHAR2(40);
1376
1377 -- :System_Number
1378 CURSOR Get_System_Number (v_system_id NUMBER) IS
1379 SELECT sys.name
1380 FROM CSI_SYSTEMS_TL sys
1381 WHERE sys.system_id = v_system_id
1382 AND sys.LANGUAGE = USERENV('LANG');
1383 l_System_Number VARCHAR2(50);
1384
1385
1386 --:Component
1387 --:Sub_Component
1388 CURSOR Get_Component_Instance (v_cp_component_id NUMBER, v_inv_organization_id NUMBER) IS
1389 SELECT product_b.concatenated_segments
1390 FROM mtl_system_items_b_kfv product_b,
1391 csi_item_instances instance
1392 WHERE product_b.inventory_item_id = instance.inventory_item_id
1393 AND product_b.organization_id = v_inv_organization_id
1394 AND instance.instance_id = v_cp_component_id;
1395
1396
1397 CURSOR Get_Component (v_inv_component_id NUMBER, v_inv_organization_id NUMBER) IS
1398 SELECT product_b.concatenated_segments
1399 FROM mtl_system_items_b_kfv product_b
1400 WHERE product_b.inventory_item_id = v_inv_component_id
1401 AND product_b.organization_id = v_inv_organization_id;
1402
1403 l_Component VARCHAR2(40);
1404 l_Sub_Component VARCHAR2(40);
1405
1406 -- :Resource_Type
1407 CURSOR Get_Res_Type (v_resource_type VARCHAR2) IS
1408 SELECT name
1409 FROM jtf_objects_tl o,
1410 jtf_object_usages ou
1411 WHERE o.object_code = ou.object_code
1412 AND ou.object_user_code = 'RESOURCES'
1413 AND o.object_code = v_resource_type
1414 AND o.LANGUAGE = userenv ( 'LANG' );
1415 l_Resource_Type VARCHAR2(30);
1416
1417
1418
1419 --:Incident_Address
1420 CURSOR Get_SR_Location (v_incident_location_id NUMBER) IS
1421 SELECT loc.address1 || nvl2(loc.address2,', '||loc.address2,NULL) ||
1422 nvl2(loc.address3,', '||loc.address3,NULL) ||
1423 nvl2(loc.address4,', '||loc.address4,NULL) ||
1424 nvl2(loc.city, ', '||loc.city, NULL) ||
1425 nvl2(loc.state, ', ' ||loc.state, NULL) ||
1426 nvl2(loc.province,', '||loc.province, NULL)||
1427 nvl2(loc.postal_code, ' '||loc.postal_code,NULL) ||
1428 nvl2(loc.country, ' ' || loc.country, NULL )
1429 FROM HZ_LOCATIONS LOC
1430 WHERE loc.location_id = v_incident_location_id;
1431
1432 CURSOR Get_SR_PS_Location (v_incident_location_id NUMBER) IS
1433 SELECT loc.address1 || nvl2(loc.address2,','||loc.address2,NULL) ||
1434 nvl2(loc.address3,', '||loc.address3,NULL) ||
1435 nvl2(loc.address4,', '||loc.address4,NULL) ||
1436 nvl2(loc.city, ', '||loc.city, NULL)||
1437 nvl2(loc.state, ', ' ||loc.state, NULL) ||
1438 nvl2(loc.province,', '||loc.province, NULL) ||
1439 nvl2(loc.postal_code, ' '||loc.postal_code,NULL) ||
1440 nvl2(loc.country, ' ' || loc.country, NULL)
1441 FROM HZ_LOCATIONS LOC,
1442 hz_party_sites hzp
1443 WHERE hzp.party_site_id = v_incident_location_id
1444 AND hzp.location_id = loc.location_id;
1445
1446 l_Incident_Address VARCHAR2(2000);
1447
1448 --:escalation
1449 CURSOR Get_Escalation (v_incident_id NUMBER) IS
1450 SELECT fnd1.meaning escalation_level
1451 FROM fnd_lookups fnd1,
1452 jtf_task_references_b r,
1453 jtf_tasks_b t
1454 WHERE fnd1.lookup_type = 'JTF_EC_ESC_LEVEL'and
1455 fnd1.lookup_code = t.escalation_level and
1456 v_incident_id = r.OBJECT_ID and
1457 r.object_type_code = 'SR' and
1458 r.reference_code = 'ESC' and
1459 r.task_id = t.task_id and
1460 t.task_type_id = 22;
1461 l_escalation VARCHAR2(80);
1462
1463 CURSOR timezone_code(l_time_zone_id Number) IS -- Bug 11830349
1464 Select timezone_code
1465 From fnd_timezones_b
1466 Where upgrade_tz_id = l_time_zone_id;
1467
1468 BEGIN
1469 -- Log the input parameter
1470
1471 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1472 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Generate_XML_Document')) THEN
1473 dbg_msg := ('In CS_ERES_INT_PKG.Generate_XML_Document Procedure');
1474 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1475 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Generate_XML_Document', dbg_msg);
1476 END IF;
1477 dbg_msg := ('P_Incident_Id : '||P_Incident_Id);
1478 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1479 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Generate_XML_Document', dbg_msg);
1480 END IF;
1481 dbg_msg := ('P_Detailed_xml_reqd : '||P_Detailed_xml_reqd);
1482 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1483 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Generate_XML_Document', dbg_msg);
1484 END IF;
1485 END IF;
1486 END IF;
1487
1488 IF NVL(P_Detailed_xml_reqd,'N') ='N' THEN
1489
1490 l_query := 'SELECT
1491 sr.incident_number Incident_Number
1492 ,(SELECT type.name
1493 FROM cs_incident_types_tl type
1494 WHERE type.incident_type_id = sr.incident_type_id
1495 AND type.language = userenv(''LANG'')) Incident_Type
1496 ,(SELECT type.detailed_erecord_req_flag
1497 FROM cs_incident_types_b type
1498 WHERE type.incident_type_id = sr.incident_type_id) Detailed_Erecord
1499 ,(SELECT status.name
1500 FROM cs_incident_statuses_tl status
1501 WHERE status.incident_status_id = sr.incident_status_id
1502 AND status.language = userenv(''LANG'') )Incident_Status
1503 ,(SELECT sev.name
1504 FROM cs_incident_severities_tl sev
1505 WHERE sev.incident_severity_id = sr.incident_severity_id
1506 AND sev.language = userenv(''LANG''))Incident_Severity
1507 ,(SELECT sev.importance_level
1508 FROM cs_incident_severities_b sev
1509 WHERE sev.incident_severity_id = sr.incident_severity_id
1510 ) Sev_Importance_Level
1511 ,(SELECT urgency.name
1512 FROM cs_incident_urgencies_tl urgency
1513 WHERE urgency.incident_urgency_id = sr.incident_urgency_id
1514 AND urgency.language = userenv(''LANG'')) Incident_Urgency
1515 ,(SELECT gr.group_name
1516 FROM jtf_rs_groups_tl gr
1517 WHERE gr.group_id = sr.owner_group_id
1518 AND gr.LANGUAGE = userenv(''LANG''))Sr_Group
1519 ,(SELECT rs.resource_name
1520 FROM jtf_rs_resource_extns_tl rs
1521 WHERE rs.resource_id = sr.incident_owner_id
1522 AND rs.language = userenv(''LANG'') )Sr_Owner
1523 ,(SELECT problem.meaning
1524 FROM fnd_lookup_values problem
1525 WHERE problem.lookup_code = sr.problem_code
1526 AND problem.lookup_type = ''REQUEST_PROBLEM_CODE''
1527 AND problem.LANGUAGE = userenv(''LANG'')
1528 AND problem.View_APPLICATION_ID = 170
1529 AND problem.SECURITY_GROUP_ID = fnd_global.lookup_security_group(problem.LOOKUP_TYPE, problem.VIEW_APPLICATION_ID) ) Problem_code
1530 ,(SELECT resolution.meaning
1531 FROM fnd_lookup_values resolution
1532 WHERE resolution.lookup_code = sr.resolution_code
1533 AND resolution.lookup_type = ''REQUEST_RESOLUTION_CODE''
1534 AND resolution.LANGUAGE = userenv(''LANG'')
1535 AND resolution.View_APPLICATION_ID = 170
1536 AND resolution.SECURITY_GROUP_ID = fnd_global.lookup_security_group(resolution.LOOKUP_TYPE,resolution.VIEW_APPLICATION_ID)) Resolution_code
1537 ,tl.summary Problem_summary
1538 ,tl.resolution_summary Resolution_Summary
1539 FROM cs_incidents_b_sec sr, cs_incidents_all_tl tl
1540 WHERE sr.incident_id = tl.incident_id
1541 AND tl.language = userenv(''lang'')
1542 AND sr.incident_id = :INCIDENT_ID' ;
1543 ELSE
1544
1545 -- Get Content
1546 --dbms_output.put_line('time_zone_enabled '||l_timezone_enabled);
1547 --dbms_output.put_line('server_timezone '||l_server_timezone_id);
1548 --dbms_output.put_line('client_timezone '||l_client_timezone_id);
1549
1550 IF NVL(P_Detailed_xml_reqd,'N') ='Y' THEN
1551 IF l_timezone_enabled = 'Y' THEN
1552 l_display_timezone_id := to_number(nvl(l_client_timezone_id, l_server_timezone_id));
1553 l_source_timezone_id := to_number(l_server_timezone_id);
1554 l_desc_timezone_id := to_number(nvl(l_client_timezone_id, l_server_timezone_id));
1555 ELSE
1556 l_display_timezone_id := to_number(l_server_timezone_id);
1557 l_source_timezone_id := to_number(l_server_timezone_id);
1558 l_desc_timezone_id := to_number(l_server_timezone_id);
1559 END IF;
1560 END IF; --detailed_xml
1561 -- Begin Bug 11830349
1562 Open timezone_code(l_source_timezone_id);
1563 Fetch timezone_code Into l_source_tz_code;
1564 Close timezone_code;
1565 Open timezone_code(l_desc_timezone_id);
1566 Fetch timezone_code Into l_dest_tz_code;
1567 Close timezone_code;
1568 -- End Bug 11830349
1569
1570
1571 OPEN GET_SR (l_display_timezone_id, l_source_timezone_id, l_desc_timezone_id, l_date_format);
1572 FETCH GET_SR INTO l_Incident_Id
1573 ,l_Incident_Number
1574 ,l_incident_type_id
1575 ,l_Incident_Status_Id
1576 ,l_Incident_Severity_Id
1577 ,l_Incident_Urgency_Id
1578 ,l_Sr_Group_Id
1579 ,l_Sr_Owner_Id
1580 ,l_Problem_code_id
1581 ,l_Resolution_code_id
1582 ,l_summary
1583 ,l_resolution_summary
1584 ,l_Publish_Flag
1585 ,l_Problem_Summary
1586 ,l_TimeZone_Id
1587 ,l_Customer_Id
1588 ,l_Account_Id
1589 ,l_Inventory_Item_Id
1590 ,l_reported_date
1591 ,l_Last_Update_Date
1592 ,l_Incident_Date
1593 ,l_Close_Date
1594 ,l_incident_resolved_date
1595 ,l_respond_by_date
1596 ,l_resolve_by_date
1597 ,l_inc_responded_by_date
1598 ,l_Actual_Resolution_Date
1599 ,l_Status_Flag_Code
1600 ,l_Created_By_Id
1601 ,l_Customer_Product_Id
1602 ,l_Organization_Id
1603 ,l_Inventory_Org_Id
1604 ,l_Instance_Number
1605 ,l_Serial_Number
1606 ,l_Tag_Number
1607 ,l_Item_Revision
1608 ,l_Component_Revision
1609 ,l_Sub_Component_Revision
1610 ,l_Attribute1
1611 ,l_Attribute2
1612 ,l_Attribute3
1613 ,l_Attribute4
1614 ,l_Attribute5
1615 ,l_Attribute6
1616 ,l_Attribute7
1617 ,l_Attribute8
1618 ,l_Attribute9
1619 ,l_Attribute10
1620 ,l_Attribute11
1621 ,l_Attribute12
1622 ,l_Attribute13
1623 ,l_Attribute14
1624 ,l_Attribute15
1625 ,l_Incident_Context
1626 ,l_Ext_Attribute1
1627 ,l_Ext_Attribute2
1628 ,l_Ext_Attribute3
1629 ,l_Ext_Attribute4
1630 ,l_Ext_Attribute5
1631 ,l_Ext_Attribute6
1632 ,l_Ext_Attribute7
1633 ,l_Ext_Attribute8
1634 ,l_Ext_Attribute9
1635 ,l_Ext_Attribute10
1636 ,l_Ext_Attribute11
1637 ,l_Ext_Attribute12
1638 ,l_Ext_Attribute13
1639 ,l_Ext_Attribute14
1640 ,l_Ext_Attribute15
1641 ,l_Ext_Context
1642 ,l_sr_creation_channel
1643 ,l_contract_service_id
1644 ,l_category_id
1645 ,l_system_id
1646 ,l_inv_component_id
1647 ,l_cp_subcomponent_id
1648 ,l_resource_type_code
1649 ,l_incident_LOCATION_ID
1650 ,l_incident_addr
1651 ,l_incident_city
1652 ,l_incident_state
1653 ,l_incident_province
1654 ,l_incident_postal_code
1655 ,l_incident_country
1656 ,l_instance_inventory_item_id
1657 ,l_instance_id
1658 ,l_cp_component_id
1659 ,l_inv_subcomponent_id
1660 ,l_incident_location_type;
1661 CLOSE GET_SR;
1662
1663 IF l_Incident_Id IS NOT NULL THEN
1664
1665 OPEN Incident_Type (l_incident_type_id);
1666 FETCH Incident_Type INTO l_Incident_Type, l_Detailed_Erecord;
1667 CLOSE Incident_Type;
1668
1669 OPEN Get_Inc_Status (l_incident_status_id);
1670 FETCH Get_Inc_Status INTO l_Incident_Status,l_status_sort_order;
1671 CLOSE Get_Inc_Status;
1672
1673 OPEN Get_Inc_Severity (l_incident_severity_id);
1674 FETCH Get_Inc_Severity INTO l_Incident_Severity, l_Sev_Importance_Level;
1675 CLOSE Get_Inc_Severity;
1676
1677 OPEN Get_Inc_Urg (l_incident_urgency_id);
1678 FETCH Get_Inc_Urg INTO l_Incident_Urgency;
1679 CLOSE Get_Inc_Urg;
1680
1681 OPEN Get_Group (l_Sr_Group_Id);
1682 FETCH Get_Group INTO l_Sr_Group;
1683 CLOSE Get_Group;
1684
1685 OPEN Get_Resource (l_Sr_Owner_Id);
1686 FETCH Get_Resource INTO l_Sr_Owner;
1687 CLOSE Get_Resource;
1688
1689 OPEN Get_Cs_Lookup ( l_Problem_code_id, 'REQUEST_PROBLEM_CODE');
1690 FETCH Get_Cs_Lookup INTO l_Problem_code;
1691 CLOSE Get_Cs_Lookup;
1692
1693 OPEN Get_Cs_Lookup ( l_Resolution_code_id, 'REQUEST_RESOLUTION_CODE');
1694 FETCH Get_Cs_Lookup INTO l_Resolution_code;
1695 CLOSE Get_Cs_Lookup;
1696
1697 OPEN Get_Cs_Lookup ( l_Sr_Creation_Channel, 'CS_SR_CREATION_CHANNEL');
1698 FETCH Get_Cs_Lookup INTO l_Sr_Creation_Channel_Name;
1699 CLOSE Get_Cs_Lookup;
1700
1701
1702 OPEN Get_Fnd_User (l_Created_By_Id);
1703 FETCH Get_Fnd_User INTO l_Created_By;
1704 CLOSE Get_Fnd_User;
1705
1706 OPEN Get_Contact_Type(l_incident_id);
1707 FETCH Get_Contact_Type INTO l_Contact_Type;
1708 CLOSE Get_Contact_Type;
1709
1710 OPEN Get_Contact_Name(l_incident_id, l_customer_id);
1711 FETCH Get_Contact_Name INTO l_Contact_Name;
1712 CLOSE Get_Contact_Name;
1713
1714 OPEN Get_Timezone (l_timezone_id);
1715 FETCH Get_Timezone INTO l_TimeZone_Name;
1716 CLOSE Get_Timezone;
1717
1718 OPEN Get_Account (l_account_id);
1719 FETCH Get_Account INTO l_account_number;
1720 CLOSE Get_Account;
1721
1722 OPEN Get_Timezone (l_display_timezone_id);
1723 FETCH Get_Timezone INTO l_display_timezone;
1724 CLOSE Get_Timezone;
1725
1726 OPEN Get_Product (l_inventory_item_id, l_Inventory_Org_Id);
1727 FETCH Get_Product INTO l_Product, l_Product_Description;
1728 CLOSE Get_Product;
1729
1730 OPEN Get_Party (l_customer_id );
1731 FETCH Get_Party INTO l_Customer_Number, l_Customer_Name, l_customer_phone, l_Customer_Email, l_party_type;
1732 CLOSE Get_Party;
1733
1734 OPEN Get_Cs_Lookup (l_party_type, 'CS_SR_CALLER_TYPE');
1735 FETCH Get_Cs_Lookup INTO l_Customer_Type;
1736 CLOSE Get_Cs_Lookup;
1737
1738 OPEN Get_Contract (l_contract_service_id);
1739 FETCH Get_Contract INTO l_Contract_Number, l_Contract_Service, l_Contract_Coverage;
1740 CLOSE Get_Contract;
1741
1742 OPEN Get_Contact_Phone (l_incident_id);
1743 FETCH Get_Contact_Phone INTO l_Contact_Phone_Number, l_Contact_Telephone_Type;
1744 CLOSE Get_Contact_Phone;
1745
1746 OPEN Get_Contact_Email (l_incident_id);
1747 FETCH Get_Contact_Email INTO l_Contact_Email;
1748 CLOSE Get_Contact_Email;
1749
1750 OPEN Get_Item_Category (l_category_id);
1751 FETCH Get_Item_Category INTO l_Item_Category;
1752 CLOSE Get_Item_Category;
1753
1754 OPEN Get_System_Number (l_system_id);
1755 FETCH Get_System_Number INTO l_System_Number;
1756 CLOSE Get_System_Number;
1757
1758 OPEN Get_Res_Type (l_resource_type_code);
1759 FETCH Get_Res_Type INTO l_Resource_Type;
1760 CLOSE Get_Res_Type;
1761
1762 OPEN Get_Escalation (l_incident_id);
1763 FETCH Get_Escalation INTO l_escalation;
1764 CLOSE Get_Escalation;
1765
1766 IF l_Customer_Product_Id IS NOT NULL THEN
1767 OPEN Get_Component_Instance (l_cp_component_id, l_Inventory_Org_Id);
1768 FETCH Get_Component_Instance INTO l_Component;
1769 CLOSE Get_Component_Instance;
1770
1771 OPEN Get_Component_Instance (l_cp_subcomponent_id, l_Inventory_Org_Id);
1772 FETCH Get_Component_Instance INTO l_Sub_Component;
1773 CLOSE Get_Component_Instance;
1774
1775 ELSE
1776 OPEN Get_Component (l_inv_component_id, l_Inventory_Org_Id);
1777 FETCH Get_Component INTO l_Component;
1778 CLOSE Get_Component;
1779
1780 OPEN Get_Component (l_inv_subcomponent_id, l_Inventory_Org_Id);
1781 FETCH Get_Component INTO l_Sub_Component;
1782 CLOSE Get_Component;
1783
1784 END IF;
1785
1786 IF l_incident_location_id IS NOT NULL THEN
1787 IF l_incident_location_type ='HZ_LOCATIONS' THEN
1788 OPEN Get_SR_Location (l_incident_location_id);
1789 FETCH Get_SR_Location INTO l_Incident_Address;
1790 CLOSE Get_SR_Location;
1791 ELSIF l_incident_location_type = 'HZ_PARTY_SITE' THEN
1792 OPEN Get_SR_PS_Location (l_incident_location_id);
1793 FETCH Get_SR_PS_Location INTO l_Incident_Address;
1794 CLOSE Get_SR_PS_Location;
1795 END IF;
1796 ELSE
1797
1798 l_Incident_Address := l_incident_addr || l_incident_city || l_incident_state ||
1799 l_incident_province || l_incident_postal_code || l_incident_country;
1800 END IF;
1801 END IF;
1802
1803 l_query :=
1804 'SELECT
1805 :INCIDENT_ID AS INCIDENT_ID
1806 ,:INCIDENT_NUMBER AS INCIDENT_NUMBER
1807 ,:INCIDENT_TYPE_ID AS INCIDENT_TYPE_ID
1808 ,:INCIDENT_TYPE AS INCIDENT_TYPE
1809 ,:DETAILED_ERECORD AS DETAILED_ERECORD
1810 ,:INCIDENT_STATUS_ID AS INCIDENT_STATUS_ID
1811 ,:INCIDENT_STATUS AS INCIDENT_STATUS
1812 ,:INCIDENT_SEVERITY_ID AS INCIDENT_SEVERITY_ID
1813 ,:INCIDENT_SEVERITY AS INCIDENT_SEVERITY
1814 ,:SEV_IMPORTANCE_LEVEL AS SEV_IMPORTANCE_LEVEL
1815 ,:INCIDENT_URGENCY_ID AS INCIDENT_URGENCY_ID
1816 ,:INCIDENT_URGENCY AS INCIDENT_URGENCY
1817 ,:SR_GROUP_ID AS SR_GROUP_ID
1818 ,:SR_GROUP AS SR_GROUP
1819 ,:SR_OWNER_ID AS SR_OWNER_ID
1820 ,:SR_OWNER AS SR_OWNER
1821 ,:PROBLEM_CODE_ID AS PROBLEM_CODE_ID
1822 ,:PROBLEM_CODE AS PROBLEM_CODE
1823 ,:RESOLUTION_CODE_ID AS RESOLUTION_CODE_ID
1824 ,:RESOLUTION_CODE AS RESOLUTION_CODE
1825 ,:SUMMARY AS SUMMARY
1826 ,:RESOLUTION_SUMMARY AS resolution_summary
1827 ,:PUBLISH_FLAG AS PUBLISH_FLAG
1828 ,:SR_CREATION_CHANNEL AS SR_CREATION_CHANNEL
1829 ,:PROBLEM_SUMMARY AS PROBLEM_SUMMARY
1830 ,:RESOLUTION_SUMMARY AS RESOLUTION_SUMMARY
1831 ,:CREATED_BY AS CREATED_BY
1832 ,:CONTACT_TYPE AS CONTACT_TYPE
1833 ,:CONTACT_NAME AS CONTACT_NAME
1834 ,:TIMEZONE_ID AS TIMEZONE_ID
1835 ,:TIMEZONE_NAME AS TIMEZONE_NAME
1836 ,:CUSTOMER_ID AS CUSTOMER_ID
1837 ,:CUSTOMER_NUMBER AS CUSTOMER_NUMBER
1838 ,:CUSTOMER_NAME AS CUSTOMER_NAME
1839 ,:ACCOUNT_ID AS ACCOUNT_ID
1840 ,:ACCOUNT_NUMBER AS ACCOUNT_NUMBER
1841 ,:INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID
1842 ,:PRODUCT AS PRODUCT
1843 ,:PRODUCT_DESCRIPTION AS PRODUCT_DESCRIPTION
1844 ,:DISPLAY_TIMEZONE AS DISPLAY_TIMEZONE
1845 ,:REPORTED_DATE AS REPORTED_DATE
1846 ,:LAST_UPDATE_DATE AS LAST_UPDATE_DATE
1847 ,:INCIDENT_DATE AS INCIDENT_DATE
1848 ,:CLOSE_DATE AS CLOSE_DATE
1849 ,:INCIDENT_RESOLVED_DATE AS INCIDENT_RESOLVED_DATE
1850 ,:RESPOND_BY_DATE AS RESPOND_BY_DATE
1851 ,:RESOLVE_BY_DATE AS RESOLVE_BY_DATE
1852 ,:INC_RESPONDED_BY_DATE AS INC_RESPONDED_BY_DATE
1853 ,:ACTUAL_RESOLUTION_DATE AS ACTUAL_RESOLUTION_DATE
1854 ,:STATUS_SORT_ORDER AS STATUS_SORT_ORDER
1855 ,:STATUS_FLAG_CODE AS STATUS_FLAG_CODE
1856 ,:CREATED_BY_ID AS CREATED_BY_ID
1857 ,:CUSTOMER_PRODUCT_ID AS CUSTOMER_PRODUCT_ID
1858 ,:ORGANIZATION_ID AS ORGANIZATION_ID
1859 ,:INVENTORY_ORG_ID AS INVENTORY_ORG_ID
1860 ,:CUSTOMER_PHONE AS CUSTOMER_PHONE
1861 ,:CUSTOMER_EMAIL AS CUSTOMER_EMAIL
1862 ,:CUSTOMER_TYPE AS CUSTOMER_TYPE
1863 ,:CONTRACT_NUMBER AS CONTRACT_NUMBER
1864 ,:CONTRACT_SERVICE AS CONTRACT_SERVICE
1865 ,:CONTRACT_COVERAGE AS CONTRACT_COVERAGE
1866 ,:CONTACT_PHONE_NUMBER AS CONTACT_PHONE_NUMBER
1867 ,:CONTACT_EMAIL AS CONTACT_EMAIL
1868 ,:CONTACT_TELEPHONE_TYPE AS CONTACT_TELEPHONE_TYPE
1869 ,:ITEM_CATEGORY AS ITEM_CATEGORY
1870 ,:INSTANCE_NUMBER AS INSTANCE_NUMBER
1871 ,:SERIAL_NUMBER AS SERIAL_NUMBER
1872 ,:TAG_NUMBER AS TAG_NUMBER
1873 ,:SYSTEM_NUMBER AS SYSTEM_NUMBER
1874 ,:COMPONENT AS COMPONENT
1875 ,:SUB_COMPONENT AS SUB_COMPONENT
1876 ,:ITEM_REVISION AS ITEM_REVISION
1877 ,:COMPONENT_REVISION AS COMPONENT_REVISION
1878 ,:SUB_COMPONENT_REVISION AS SUB_COMPONENT_REVISION
1879 ,:INCIDENT_ADDRESS AS INCIDENT_ADDRESS
1880 ,:RESOURCE_TYPE AS RESOURCE_TYPE
1881 ,:ATTRIBUTE1 AS ATTRIBUTE1
1882 ,:ATTRIBUTE2 AS ATTRIBUTE2
1883 ,:ATTRIBUTE3 AS ATTRIBUTE3
1884 ,:ATTRIBUTE4 AS ATTRIBUTE4
1885 ,:ATTRIBUTE5 AS ATTRIBUTE5
1886 ,:ATTRIBUTE6 AS ATTRIBUTE6
1887 ,:ATTRIBUTE7 AS ATTRIBUTE7
1888 ,:ATTRIBUTE8 AS ATTRIBUTE8
1889 ,:ATTRIBUTE9 AS ATTRIBUTE9
1890 ,:ATTRIBUTE10 AS ATTRIBUTE10
1891 ,:ATTRIBUTE11 AS ATTRIBUTE11
1892 ,:ATTRIBUTE12 AS ATTRIBUTE12
1893 ,:ATTRIBUTE13 AS ATTRIBUTE13
1894 ,:ATTRIBUTE14 AS ATTRIBUTE14
1895 ,:ATTRIBUTE15 AS ATTRIBUTE15
1896 ,:INCIDENT_CONTEXT AS INCIDENT_CONTEXT
1897 ,:EXT_ATTRIBUTE1 AS EXT_ATTRIBUTE1
1898 ,:EXT_ATTRIBUTE2 AS EXT_ATTRIBUTE2
1899 ,:EXT_ATTRIBUTE3 AS EXT_ATTRIBUTE3
1900 ,:EXT_ATTRIBUTE4 AS EXT_ATTRIBUTE4
1901 ,:EXT_ATTRIBUTE5 AS EXT_ATTRIBUTE5
1902 ,:EXT_ATTRIBUTE6 AS EXT_ATTRIBUTE6
1903 ,:EXT_ATTRIBUTE7 AS EXT_ATTRIBUTE7
1904 ,:EXT_ATTRIBUTE8 AS EXT_ATTRIBUTE8
1905 ,:EXT_ATTRIBUTE9 AS EXT_ATTRIBUTE9
1906 ,:EXT_ATTRIBUTE10 AS EXT_ATTRIBUTE10
1907 ,:EXT_ATTRIBUTE11 AS EXT_ATTRIBUTE11
1908 ,:EXT_ATTRIBUTE12 AS EXT_ATTRIBUTE12
1909 ,:EXT_ATTRIBUTE13 AS EXT_ATTRIBUTE13
1910 ,:EXT_ATTRIBUTE14 AS EXT_ATTRIBUTE14
1911 ,:EXT_ATTRIBUTE15 AS EXT_ATTRIBUTE15
1912 ,:EXT_CONTEXT AS EXT_CONTEXT
1913 ,:ESCALATION AS ESCALATION
1914 , CS_ERES_INT_PKG.Get_SR_Tasks (:INCIDENT_ID, :L_SOURCE_TIMEZONE_ID, :L_DESC_TIMEZONE_ID, :L_DATE_FORMAT) AS TASK_LIST
1915 , CS_ERES_INT_PKG.Get_SR_Notes (:incident_id, :l_source_timezone_id, :l_desc_timezone_id, :l_date_format) NOTE_LIST
1916 , CS_ERES_INT_PKG.Get_Related_SRs(:incident_id) RELATED_SR_LIST
1917 , CS_ERES_INT_PKG.Get_Related_Objs(:incident_id) RELATED_OB_LIST
1918 FROM DUAL';
1919
1920
1921 END IF ;
1922
1923 -- Log that XMLGen is being called
1924
1925 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1926 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Generate_XML_Document')) THEN
1927 dbg_msg := ('Calling DBMS_XMLGEN.getXML Procedure');
1928 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1929 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Generate_XML_Document', dbg_msg);
1930 END IF;
1931 END IF;
1932 END IF;
1933
1934 dbms_lob.createtemporary(xmldoc,true,DBMS_LOB.CALL);
1935 Ctx1 := DBMS_XMLGEN.newContext(l_query);
1936
1937 dbms_xmlgen.setRowTag(Ctx1,'oracle.apps.cs.sr.ServiceRequestApproval');
1938 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_ID', p_incident_id);
1939
1940 --dbms_output.put_line('time_zone_enabled '||l_timezone_enabled);
1941 --dbms_output.put_line('server_timezone '||l_server_timezone_id);
1942 --dbms_output.put_line('client_timezone '||l_client_timezone_id);
1943 --dbms_output.put_line('l_date_format : '||l_date_format);
1944
1945 IF NVL(P_Detailed_xml_reqd,'N') ='Y' THEN
1946
1947 IF l_timezone_enabled = 'Y' THEN
1948 l_client_timezone_id := nvl(l_client_timezone_id, l_server_timezone_id);
1949 -- DBMS_XMLGEN.setBindValue(Ctx1,'l_display_timezone_id',l_client_timezone_id);
1950 DBMS_XMLGEN.setBindValue(Ctx1,'l_source_timezone_id',l_server_timezone_id);
1951 DBMS_XMLGEN.setBindValue(Ctx1,'l_desc_timezone_id',l_client_timezone_id);
1952 DBMS_XMLGEN.setBindValue(Ctx1,'l_date_format',l_date_format);
1953 ELSE
1954 -- DBMS_XMLGEN.setBindValue(Ctx1,'l_display_timezone_id',l_server_timezone_id);
1955 DBMS_XMLGEN.setBindValue(Ctx1,'l_source_timezone_id',l_server_timezone_id);
1956 DBMS_XMLGEN.setBindValue(Ctx1,'l_desc_timezone_id',l_server_timezone_id);
1957 DBMS_XMLGEN.setBindValue(Ctx1,'l_date_format',l_date_format);
1958 END IF;
1959
1960 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_NUMBER', l_Incident_Number);
1961 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_TYPE_ID', l_incident_type_id);
1962 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_TYPE', l_Incident_Type);
1963 DBMS_XMLGEN.setBindValue(Ctx1,'DETAILED_ERECORD', l_Detailed_Erecord);
1964 --
1965 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_STATUS_ID', l_Incident_Status_Id);
1966 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_STATUS', l_Incident_Status);
1967 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_SEVERITY_ID', l_Incident_Severity_Id);
1968 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_SEVERITY', l_Incident_Severity);
1969 DBMS_XMLGEN.setBindValue(Ctx1,'SEV_IMPORTANCE_LEVEL', nvl(to_char(l_Sev_Importance_Level),' '));
1970 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_URGENCY_ID', nvl(to_char(l_Incident_Urgency_Id),' '));
1971 DBMS_XMLGEN.setBindValue(Ctx1,'INCIDENT_URGENCY', nvl(l_Incident_Urgency,' '));
1972 DBMS_XMLGEN.setBindValue(Ctx1,'SR_GROUP_ID', nvl(to_char(l_Sr_Group_Id),' '));
1973 DBMS_XMLGEN.setBindValue(Ctx1,'SR_GROUP', nvl(l_Sr_Group,' '));
1974 DBMS_XMLGEN.setBindValue(Ctx1,'SR_OWNER_ID', nvl(to_char(l_Sr_Owner_Id),' '));
1975 DBMS_XMLGEN.setBindValue(Ctx1,'SR_OWNER', nvl(l_Sr_Owner,' '));
1976 DBMS_XMLGEN.setBindValue(Ctx1,'Problem_code_id', nvl(to_char(l_Problem_code_id),' '));
1977 DBMS_XMLGEN.setBindValue(Ctx1,'Problem_code', nvl(l_Problem_code,' '));
1978 DBMS_XMLGEN.setBindValue(Ctx1,'Resolution_code_id', nvl(to_char(l_Resolution_code_id),' '));
1979 DBMS_XMLGEN.setBindValue(Ctx1,'Resolution_code', nvl(l_Resolution_code,' '));
1980 DBMS_XMLGEN.setBindValue(Ctx1,'summary', nvl(l_summary,' '));
1981 DBMS_XMLGEN.setBindValue(Ctx1,'resolution_summary', nvl(l_resolution_summary,' '));
1982 DBMS_XMLGEN.setBindValue(Ctx1,'Publish_Flag', nvl(l_Publish_Flag,' '));
1983 DBMS_XMLGEN.setBindValue(Ctx1,'Sr_Creation_Channel', nvl(l_Sr_Creation_Channel,' '));
1984 DBMS_XMLGEN.setBindValue(Ctx1,'Problem_Summary', nvl(l_Problem_Summary,' '));
1985 DBMS_XMLGEN.setBindValue(Ctx1,'Created_By', nvl(l_Created_By,' '));
1986 DBMS_XMLGEN.setBindValue(Ctx1,'Contact_Type', nvl(l_Contact_Type,' '));
1987 DBMS_XMLGEN.setBindValue(Ctx1,'Contact_Name', nvl(l_Contact_Name,' '));
1988 DBMS_XMLGEN.setBindValue(Ctx1,'TimeZone_Id', nvl(to_char(l_TimeZone_Id),' '));
1989 DBMS_XMLGEN.setBindValue(Ctx1,'TimeZone_Name', nvl(l_TimeZone_Name,' '));
1990 DBMS_XMLGEN.setBindValue(Ctx1,'Customer_Id', nvl(to_char(l_Customer_Id),' '));
1991 DBMS_XMLGEN.setBindValue(Ctx1,'Customer_Number', nvl(l_Customer_Number,' '));
1992 DBMS_XMLGEN.setBindValue(Ctx1,'Customer_Name', nvl(l_Customer_Name,' '));
1993 DBMS_XMLGEN.setBindValue(Ctx1,'Account_Id', nvl(to_char(l_Account_Id),' '));
1994 DBMS_XMLGEN.setBindValue(Ctx1,'account_number', nvl(l_account_number,' '));
1995 DBMS_XMLGEN.setBindValue(Ctx1,'Inventory_Item_Id', nvl(to_char(l_Inventory_Item_Id),' '));
1996 DBMS_XMLGEN.setBindValue(Ctx1,'Product', nvl(l_Product,' '));
1997 DBMS_XMLGEN.setBindValue(Ctx1,'Product_Description', nvl(l_Product_Description,' '));
1998 DBMS_XMLGEN.setBindValue(Ctx1,'display_timezone', nvl(l_display_timezone,' '));
1999 DBMS_XMLGEN.setBindValue(Ctx1,'reported_date', nvl(l_reported_date,' '));
2000 DBMS_XMLGEN.setBindValue(Ctx1,'Last_Update_Date', nvl(l_Last_Update_Date,' '));
2001 DBMS_XMLGEN.setBindValue(Ctx1,'Incident_Date', nvl(l_Incident_Date,' '));
2002 DBMS_XMLGEN.setBindValue(Ctx1,'Close_Date', nvl(l_Close_Date,' '));
2003 DBMS_XMLGEN.setBindValue(Ctx1,'incident_resolved_date', nvl(l_incident_resolved_date,' '));
2004 DBMS_XMLGEN.setBindValue(Ctx1,'respond_by_date', nvl(l_respond_by_date,' '));
2005 DBMS_XMLGEN.setBindValue(Ctx1,'resolve_by_date', nvl(l_resolve_by_date,' '));
2006 DBMS_XMLGEN.setBindValue(Ctx1,'inc_responded_by_date', nvl(l_inc_responded_by_date,' '));
2007 DBMS_XMLGEN.setBindValue(Ctx1,'Actual_Resolution_Date', nvl(l_Actual_Resolution_Date,' '));
2008 DBMS_XMLGEN.setBindValue(Ctx1,'status_sort_order', nvl(to_char(l_status_sort_order),' '));
2009 DBMS_XMLGEN.setBindValue(Ctx1,'Status_Flag_Code', nvl(l_Status_Flag_Code,' '));
2010 DBMS_XMLGEN.setBindValue(Ctx1,'Created_By_Id', nvl(to_char(l_Created_By_Id),' '));
2011 DBMS_XMLGEN.setBindValue(Ctx1,'Customer_Product_Id', nvl(to_char(l_Customer_Product_Id),' '));
2012 DBMS_XMLGEN.setBindValue(Ctx1,'Organization_Id', nvl(to_char(l_Organization_Id),' '));
2013 DBMS_XMLGEN.setBindValue(Ctx1,'Inventory_Org_Id', nvl(to_char(l_Inventory_Org_Id),' '));
2014 DBMS_XMLGEN.setBindValue(Ctx1,'customer_phone', nvl(l_customer_phone,' '));
2015 DBMS_XMLGEN.setBindValue(Ctx1,'Customer_Email', nvl(l_Customer_Email,' '));
2016 DBMS_XMLGEN.setBindValue(Ctx1,'Customer_Type', nvl(l_Customer_Type,' '));
2017 DBMS_XMLGEN.setBindValue(Ctx1,'Contract_Number', nvl(l_Contract_Number,' '));
2018 DBMS_XMLGEN.setBindValue(Ctx1,'Contract_Service', nvl(l_Contract_Service,' '));
2019 DBMS_XMLGEN.setBindValue(Ctx1,'Contract_Coverage', nvl(l_Contract_Coverage,' '));
2020 DBMS_XMLGEN.setBindValue(Ctx1,'Contact_Phone_Number', nvl(l_Contact_Phone_Number,' '));
2021 DBMS_XMLGEN.setBindValue(Ctx1,'Contact_Email', nvl(l_Contact_Email,' '));
2022 DBMS_XMLGEN.setBindValue(Ctx1,'Contact_Telephone_Type', nvl(l_Contact_Telephone_Type,' '));
2023 DBMS_XMLGEN.setBindValue(Ctx1,'Item_Category', nvl(l_Item_Category,' '));
2024 DBMS_XMLGEN.setBindValue(Ctx1,'Instance_Number', nvl(l_Instance_Number,' '));
2025 DBMS_XMLGEN.setBindValue(Ctx1,'Serial_Number', nvl(l_Serial_Number,' '));
2026 DBMS_XMLGEN.setBindValue(Ctx1,'Tag_Number', nvl(l_Tag_Number,' '));
2027 DBMS_XMLGEN.setBindValue(Ctx1,'System_Number', nvl(l_System_Number,' '));
2028 DBMS_XMLGEN.setBindValue(Ctx1,'Component', nvl(l_Component,' '));
2029 DBMS_XMLGEN.setBindValue(Ctx1,'Sub_Component', nvl(l_Sub_Component,' '));
2030 DBMS_XMLGEN.setBindValue(Ctx1,'Item_Revision', nvl(l_Item_Revision,' '));
2031 DBMS_XMLGEN.setBindValue(Ctx1,'Component_Revision', nvl(l_Component_Revision,' '));
2032 DBMS_XMLGEN.setBindValue(Ctx1,'Sub_Component_Revision', nvl(l_Sub_Component_Revision,' '));
2033 DBMS_XMLGEN.setBindValue(Ctx1,'Incident_Address', nvl(l_Incident_Address,' '));
2034 DBMS_XMLGEN.setBindValue(Ctx1,'Resource_Type', nvl(l_Resource_Type,' '));
2035 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute1', nvl(l_Attribute1,' '));
2036 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute2', nvl(l_Attribute2,' '));
2037 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute3', nvl(l_Attribute3,' '));
2038 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute4', nvl(l_Attribute4,' '));
2039 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute5', nvl(l_Attribute5,' '));
2040 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute6', nvl(l_Attribute6,' '));
2041 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute7', nvl(l_Attribute7,' '));
2042 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute8', nvl(l_Attribute8,' '));
2043 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute9', nvl(l_Attribute9,' '));
2044 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute10', nvl(l_Attribute10,' '));
2045 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute11', nvl(l_Attribute11,' '));
2046 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute12', nvl(l_Attribute12,' '));
2047 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute13', nvl(l_Attribute13,' '));
2048 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute14', nvl(l_Attribute14,' '));
2049 DBMS_XMLGEN.setBindValue(Ctx1,'Attribute15', nvl(l_Attribute15,' '));
2050 DBMS_XMLGEN.setBindValue(Ctx1,'Incident_Context', nvl(l_Incident_Context,' '));
2051 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute1', nvl(l_Ext_Attribute1,' '));
2052 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute2', nvl(l_Ext_Attribute2,' '));
2053 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute3', nvl(l_Ext_Attribute3,' '));
2054 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute4', nvl(l_Ext_Attribute4,' '));
2055 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute5', nvl(l_Ext_Attribute5,' '));
2056 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute6', nvl(l_Ext_Attribute6,' '));
2057 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute7', nvl(l_Ext_Attribute7,' '));
2058 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute8', nvl(l_Ext_Attribute8,' '));
2059 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute9', nvl(l_Ext_Attribute9,' '));
2060 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute10', nvl(l_Ext_Attribute10,' '));
2061 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute11', nvl(l_Ext_Attribute11,' '));
2062 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute12', nvl(l_Ext_Attribute12,' '));
2063 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute13', nvl(l_Ext_Attribute13,' '));
2064 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute14', nvl(l_Ext_Attribute14,' '));
2065 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Attribute15', nvl(l_Ext_Attribute15,' '));
2066 DBMS_XMLGEN.setBindValue(Ctx1,'Ext_Context', nvl(l_Ext_Context,' '));
2067 DBMS_XMLGEN.setBindValue(Ctx1,'escalation', nvl(l_escalation,' '));
2068
2069 END IF; --detailed_xml
2070
2071 DBMS_XMLGEN.setNullHandling(Ctx1, 2);
2072 DBMS_XMLGEN.getXML(Ctx1,xmldoc,dbms_xmlgen.SCHEMA);
2073 dbms_xmlgen.closeContext(Ctx1);
2074
2075 -- Log that XMLGen call is complete
2076
2077 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2078 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Generate_XML_Document')) THEN
2079 dbg_msg := ('After Calling DBMS_XMLGEN.getXML Procedure');
2080 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2081 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Generate_XML_Document', dbg_msg);
2082 END IF;
2083 END IF;
2084 END IF;
2085
2086 -- get number of service request extensible attribute records
2087
2088 SELECT COUNT(1)
2089 INTO l_ext_rec_count
2090 FROM CS_INCIDENTS_EXT
2091 WHERE incident_id = p_incident_id;
2092
2093 -- If a service request has any extensible atribute records only then call
2094 -- append_ea_data
2095
2096 IF l_ext_rec_count > 0 THEN
2097 xmldoc := append_ea_data(p_incident_id, xmldoc);
2098 END IF;
2099 --
2100
2101 RETURN xmldoc;
2102
2103 EXCEPTION
2104 WHEN OTHERS THEN
2105 --dbms_output.put_line('Other Error: '||sqlcode||sqlerrm);
2106 FND_MESSAGE.SET_NAME('CS','CS_ERES_XML_GEN_FAILED');
2107 FND_MESSAGE.SET_TOKEN ('SQLCODE',SQLCODE);
2108 FND_MESSAGE.SET_TOKEN ('SQLERRM',SQLERRM);
2109 FND_MSG_PUB.ADD;
2110 RETURN NULL;
2111 END Generate_XML_Document ;
2112
2113 --------------------------------------------------------------------------------
2114 -- Modification History:
2115 -- Date Name Desc
2116 -- -------- -------- -----------------------------------------------------------
2117 -- 12/19/05 smisra Bug 4896857
2118 -- for note tpye value used the profile
2119 -- 'CS_SR_ERES_COMMENT_NOTE_TYPE' instead of
2120 -- 'Service: Note Type For ERES Comment'
2121 --------------------------------------------------------------------------------
2122 PROCEDURE Post_Approval_Process
2123 ( P_Incident_id IN NUMBER,
2124 P_Intermediate_Status_Id IN NUMBER ) IS
2125
2126 -- Cursor to get service request version number, current status
2127 -- and lock the service request
2128
2129 CURSOR c_Get_SR_Version IS
2130 SELECT object_version_number , incident_status_id
2131 FROM cs_incidents_all_b
2132 WHERE incident_id = p_incident_id
2133 FOR UPDATE;
2134
2135
2136 -- Variables to pass to the EDR APIs
2137
2138 l_eRecord_Id NUMBER;
2139 l_document_rec EDR_PSIG_DOCUMENTS%ROWTYPE;
2140 l_doc_param_table EDR_EvidenceStore_PUB.Params_tbl_type;
2141 l_Signatures_tbl EDR_EvidenceStore_PUB.Signature_tbl_type;
2142 l_SignatureDetails EDR_PSIG_DETAILS%ROWTYPE;
2143 l_Signatureparams EDR_EvidenceStore_PUB.params_tbl_type;
2144
2145 -- Variables to be passed to the Update SR API
2146 l_note_status VARCHAR2(240);
2147 l_note_type VARCHAR2(240);
2148 l_notes_table CS_ServiceRequest_PVT.Notes_Table;
2149 l_notes_table_dummy CS_ServiceRequest_PVT.Notes_Table;
2150 l_ServiceRequest_Rec CS_ServiceRequest_PVT.Service_Request_Rec_Type;
2151 l_sr_version NUMBER;
2152 l_note_text VARCHAR2(4000);
2153 l_contacts_table CS_ServiceRequest_PVT.Contacts_Table;
2154 l_sr_update_out_rec CS_ServiceRequest_PVT.sr_update_out_rec_type;
2155
2156 -- Local Variables
2157 l_sig_status VARCHAR2(40);
2158 l_target_status_id NUMBER;
2159 l_return_status VARCHAR2(3);
2160 l_msg_count NUMBER;
2161 l_msg_data VARCHAR2(1000);
2162 l_sr_status_id NUMBER;
2163 q NUMBER := 1 ;
2164 l_approver VARCHAR2(240);
2165 l_note_detail VARCHAR2(32000);
2166 l_comment VARCHAR2(30000);
2167 l_action VARCHAR2(240);
2168 l_note_title VARCHAR2(280);
2169 lx_msg_count NUMBER;
2170 lx_msg_data VARCHAR2(4000);
2171 lx_return_status VARCHAR2(1);
2172 lx_msg_index_out NUMBER;
2173 l_chr_newline VARCHAR2(8) := fnd_global.newline;
2174 l_api_name VARCHAR2(240) := 'Post_Approval_Process';
2175 l_spl_excp VARCHAR2(3) := 'N';
2176 l_note_id NUMBER;
2177 l_note_err_msg VARCHAR2(240);
2178 l_validate_sr_close VARCHAR2(30) := FND_PROFILE.VALUE('CS_SR_AUTO_CLOSE_CHILDREN');
2179 -- l_close_sr_child VARCHAR2(3) := FND_PROFILE.VALUE('CS_SR_AUTO_CLOSE_CHILDREN');
2180 --Bug 9879799
2181 l_approver_code VARCHAR2(240);
2182 l_action_code VARCHAR2(240);
2183 l_comment_code VARCHAR2(240);
2184
2185
2186 BEGIN
2187 -- Log the input parameters
2188
2189 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2190 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2191 dbg_msg := ('In CS_ERES_INT_PKG.Post_Approval_Process Procedure');
2192 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2193 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2194 END IF;
2195
2196 dbg_msg := ('P_Incident_id : '||P_Incident_id);
2197 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2198 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2199 END IF;
2200
2201 dbg_msg := ('P_Intermediate_Status_Id : '||P_Intermediate_Status_Id);
2202 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2203 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2204 END IF;
2205
2206 END IF;
2207 END IF;
2208
2209 -- Get the approval status
2210
2211 l_sig_status := EDR_STANDARD_PUB.G_SIGNATURE_STATUS;
2212
2213 -- Log the E Signature Status
2214
2215 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2216 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2217 dbg_msg := ('E Signature Status : '||l_sig_status);
2218 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2219 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2220 END IF;
2221 END IF;
2222 END IF;
2223
2224 -- Log Get_Target_SR_Status is being called
2225
2226 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2227 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2228 dbg_msg := ('Calling Get_Target_SR_Status');
2229 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2230 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2231 END IF;
2232 END IF;
2233 END IF;
2234
2235 -- Get the target Service request status
2236
2237 l_return_status := FND_API.G_RET_STS_SUCCESS;
2238
2239 Get_Target_SR_Status
2240 ( P_Incident_Id => P_Incident_id,
2241 P_Intermediate_Status_Id => P_Intermediate_Status_Id,
2242 P_Action => l_sig_status ,
2243 X_Target_Status_Id => l_target_status_id,
2244 X_Return_Status => l_return_status,
2245 X_Msg_count => l_msg_count ,
2246 X_Msg_data => l_msg_data );
2247
2248 -- Log the output of Get_Target_SR_Status
2249
2250 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2251 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2252
2253 dbg_msg := ('After Calling Get_Target_SR_Status');
2254 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2255 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2256 END IF;
2257
2258 dbg_msg := ('Get_Target_SR_Status Return Status : '||l_return_status||' Msg Data : '||l_msg_data);
2259 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2260 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2261 END IF;
2262
2263 dbg_msg := ('Target Status Id : '||l_target_status_id);
2264 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2265 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2266 END IF;
2267
2268 END IF;
2269 END IF;
2270
2271
2272 -- Log EDR_Standard_PUB.Get_ERecord_ID is being called
2273
2274 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2275 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2276 dbg_msg := ('Calling EDR_Standard_PUB.Get_ERecord_ID');
2277 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2278 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2279 END IF;
2280 END IF;
2281 END IF;
2282
2283 -- Get the document ID (Erecord Id) using the event key and event name
2284
2285 l_return_status := FND_API.G_RET_STS_SUCCESS;
2286
2287 EDR_Standard_PUB.Get_ERecord_ID
2288 (p_api_version => 1.0,
2289 p_init_msg_list => fnd_api.g_false,
2290 p_event_name => 'oracle.apps.cs.sr.ServiceRequestApproval',
2291 p_event_key => P_Incident_id,
2292 x_return_status => l_return_status,
2293 x_msg_count => l_msg_count ,
2294 x_msg_data => l_msg_data,
2295 x_erecord_id => l_eRecord_Id );
2296
2297
2298 -- Log EDR_Standard_PUB.Get_ERecord_ID return status
2299
2300 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2301 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2302 dbg_msg := ('After Calling EDR_Standard_PUB.Get_ERecord_ID');
2303 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2304 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2305 END IF;
2306 dbg_msg := ('EDR_Standard_PUB.Get_ERecord_ID Return Status : '||l_return_status);
2307 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2308 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2309 END IF;
2310 dbg_msg := ('EDR_Standard_PUB.Get_ERecord_ID ERecord ID : '||l_eRecord_Id);
2311 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2312 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2313 END IF;
2314 END IF;
2315 END IF;
2316
2317 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2318 l_spl_excp := 'Y';
2319 END IF ;
2320
2321 IF l_eRecord_Id IS NOT NULL THEN
2322
2323 -- Log EDR_EvidenceStore_PUB.Get_DocumentDetails is being called
2324
2325 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2326 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2327 dbg_msg := ('Calling EDR_EvidenceStore_PUB.Get_DocumentDetails');
2328 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2329 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2330 END IF;
2331 END IF;
2332 END IF;
2333 -- Get Document Details using the eRecord/Document Id.
2334
2335 l_return_status := FND_API.G_RET_STS_SUCCESS;
2336
2337 EDR_EvidenceStore_PUB.Get_DocumentDetails
2338 ( P_api_version => 1.0,
2339 P_init_msg_list => FND_API.G_TRUE,
2340 p_document_id => l_eRecord_id,
2341 X_return_status => l_return_status,
2342 X_msg_count => l_msg_count,
2343 X_msg_data => l_msg_data,
2344 x_document_rec => l_document_rec,
2345 x_doc_parameters_tbl => l_doc_param_table,
2346 x_signatures_tbl => l_signatures_tbl) ;
2347
2348
2349 -- Log EDR_Standard_PUB.Get_ERecord_ID return status
2350
2351 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2352 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2353 dbg_msg := ('After Calling EDR_EvidenceStore_PUB.Get_DocumentDetails');
2354 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2355 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2356 END IF;
2357 dbg_msg := ('EDR_EvidenceStore_PUB.Get_DocumentDetails Return Status : '||l_return_status);
2358 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2359 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2360 END IF;
2361 dbg_msg := ('EDR_EvidenceStore_PUB.Get_DocumentDetails Sig Table Count : '||l_signatures_tbl.COUNT);
2362 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2363 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2364 END IF;
2365 END IF;
2366 END IF;
2367
2368 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2369 l_spl_excp := 'Y';
2370 END IF;
2371
2372 IF l_signatures_tbl.COUNT > 0 THEN
2373
2374 -- Get signature details for each signature in the document details.
2375 -- Retrive the signer's comment from the signature details.
2376 -- Populate the each signer's comment to a note
2377
2378 -- Get the approval variables populated.
2379
2380 FND_MESSAGE.SET_NAME('CS','CS_ERES_SR_APPROVER_NAME');
2381 l_approver := FND_MESSAGE.GET;
2382 FND_MESSAGE.SET_NAME('CS','CS_ERES_SR_APPROVER_ACTION');
2383 l_action := FND_MESSAGE.GET;
2384 FND_MESSAGE.SET_NAME('CS','CS_ERES_SR_APPROVER_COMMENT');
2385 l_comment := FND_MESSAGE.GET;
2386 FND_MESSAGE.SET_NAME('CS','CS_ERES_SR_APPROVAL_RESULT');
2387 l_note_title := FND_MESSAGE.GET;
2388
2389 -- Get the note type and status from the profile option
2390
2391 FND_PROFILE.Get('JTF_NTS_NOTE_STATUS',l_note_status);
2392 FND_PROFILE.Get('CS_SR_ERES_COMMENT_NOTE_TYPE',l_note_type);
2393
2394 FOR i IN 1..l_signatures_tbl.COUNT
2395 LOOP
2396 -- Retrive the signer's comment from the signature details.
2397
2398 l_approver := l_approver_code||' '||l_signatures_tbl(i).user_display_name|| --Bug 9879799 , changed l_approver to l_approver_Code
2399 ' ('||l_signatures_tbl(i).user_name||')';
2400 l_action := l_action_code||' '||l_signatures_tbl(i).user_response; --Bug 9879799 , changed l_action to l_action_Code
2401
2402
2403
2404 -- Log EDR_EvidenceStore_PUB.GET_SignatureDetails is being called
2405
2406 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2407 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2408 dbg_msg := ('Calling EDR_EvidenceStore_PUB.GET_SignatureDetails');
2409 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2410 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2411 END IF;
2412 END IF;
2413 END IF;
2414
2415 l_return_status := FND_API.G_RET_STS_SUCCESS;
2416
2417 EDR_EvidenceStore_PUB.GET_SignatureDetails
2418 ( P_api_version => 1.0,
2419 P_init_msg_list => FND_API.G_TRUE ,
2420 P_signature_id => l_signatures_tbl(i).signature_id,
2421 X_return_status => l_return_status,
2422 X_msg_count => l_msg_count,
2423 X_msg_data => l_msg_data,
2424 X_SIGNATUREDETAILS => l_signatureDetails,
2425 X_SIGNATUREPARAMS => l_Signatureparams ) ;
2426
2427
2428 -- Log EDR_EvidenceStore_PUB.GET_SignatureDetails return status
2429
2430 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2431 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2432 dbg_msg := ('After Calling EDR_EvidenceStore_PUB.GET_SignatureDetails');
2433 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2434 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2435 END IF;
2436 dbg_msg := ('EDR_EvidenceStore_PUB.GET_SignatureDetails Return Status : '||l_return_status);
2437 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2438 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2439 END IF;
2440 dbg_msg := ('EDR_EvidenceStore_PUB.GET_SignatureDetails Sig Param Table Count : '||l_Signatureparams.COUNT);
2441 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2442 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2443 END IF;
2444 END IF;
2445 END IF;
2446
2447 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2448 null;
2449 END IF ;
2450
2451 FOR j IN 1..l_Signatureparams.COUNT
2452 LOOP
2453 IF l_Signatureparams(j).param_name = 'SIGNERS_COMMENT' THEN
2454 --l_notes_table.extend;
2455 l_comment := l_comment_code||' '||l_Signatureparams(j).param_value; --Bug 9879799 , changed l_coomment to l_comment_Code
2456 l_note_detail := l_note_title||l_chr_newline||l_approver||l_chr_newline||
2457 l_action||l_chr_newline||l_comment||l_chr_newline;
2458
2459 l_notes_table(q).NOTE := substr(l_note_detail,1,2000) ;
2460 l_notes_table(q).NOTE_DETAIL := l_note_detail ;
2461 l_notes_table(q).NOTE_TYPE := l_note_type;
2462 l_notes_table(q).NOTE_STATUS := NVL(l_note_status,'I');
2463 l_notes_table(q).SOURCE_OBJECT_CODE := 'SR';
2464 l_notes_table(q).SOURCE_OBJECT_ID := p_incident_id;
2465 q := q + 1 ;
2466 END IF ;
2467 END LOOP;
2468 l_approver := null; --Bug 9879799
2469 l_action := null;--Bug 9879799
2470 l_comment := null;--Bug 9879799
2471
2472 END LOOP;
2473
2474 END IF ; -- signature_table.count
2475
2476 END IF ; -- l_ERecord_ID
2477
2478 IF l_spl_excp = 'Y' THEN
2479
2480 FND_MESSAGE.SET_NAME('CS','CS_ERES_SR_APPROVAL_RESULT');
2481 l_note_title := FND_MESSAGE.GET;
2482
2483 -- Get the note type and status from the profile option
2484
2485 FND_PROFILE.Get('JTF_NTS_NOTE_STATUS',l_note_status);
2486 FND_PROFILE.Get('CS_SR_ERES_COMMENT_NOTE_TYPE',l_note_type);
2487
2488 l_notes_table(q).NOTE := l_note_title||l_chr_newline ;
2489 l_notes_table(q).NOTE_DETAIL := l_note_title||l_chr_newline||l_sig_status ;
2490 l_notes_table(q).NOTE_TYPE := l_note_type;
2491 l_notes_table(q).NOTE_STATUS := NVL(l_note_status,'I');
2492 l_notes_table(q).SOURCE_OBJECT_CODE := 'SR';
2493 l_notes_table(q).SOURCE_OBJECT_ID := p_incident_id;
2494 q := q + 1 ;
2495
2496 END IF;
2497
2498 -- Get SR Version and lock the SR for update
2499 OPEN c_Get_SR_Version ;
2500 FETCH c_Get_SR_Version INTO l_sr_version,l_sr_status_id;
2501
2502 -- Call Update Service Request API to update the SR Status and Create Notes
2503
2504 -- Initialize the service request record.
2505 CS_ServiceRequest_PVT.Initialize_Rec(l_servicerequest_Rec);
2506
2507 IF l_sr_status_id = p_intermediate_status_id THEN
2508 l_servicerequest_Rec.status_id := l_target_status_id;
2509 END IF ;
2510
2511 -- populate the program code = 'ERES'
2512 l_servicerequest_Rec.last_update_program_code := 'ERES';
2513
2514 -- If UpdateService Request API call fails then add additional note and
2515 -- update the service request status to the initial status
2516
2517
2518 -- Log CS_ServiceRequest_PVT.Update_ServiceRequest is being called
2519
2520 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2521 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2522 dbg_msg := ('Calling CS_ServiceRequest_PVT.Update_ServiceRequest (1)');
2523 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2524 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2525 END IF;
2526 END IF;
2527 END IF;
2528
2529 l_return_status := FND_API.G_RET_STS_SUCCESS ;
2530
2531 CS_ServiceRequest_PVT.Update_ServiceRequest
2532 ( p_api_version => 4.0,
2533 x_return_status => l_return_status,
2534 x_msg_count => l_msg_count,
2535 x_msg_data => l_msg_data,
2536 p_request_id => p_incident_id,
2537 p_audit_id => null,
2538 p_object_version_number => l_sr_version,
2539 p_last_updated_by => fnd_global.user_id,
2540 p_last_update_date => sysdate,
2541 p_service_request_rec => l_servicerequest_Rec,
2542 p_notes => l_notes_table_dummy,
2543 p_contacts => l_contacts_table,
2544 p_validate_sr_closure => NVL(l_validate_sr_close,'N'),
2545 p_auto_close_child_entities => NVL(l_validate_sr_close,'N'),
2546 x_sr_update_out_rec => l_sr_update_out_rec );
2547
2548
2549
2550 -- Log CS_ServiceRequest_PVT.Update_ServiceRequest return status
2551
2552 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2553 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2554 dbg_msg := ('After Calling CS_ServiceRequest_PVT.Update_ServiceRequest');
2555 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2556 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2557 END IF;
2558 dbg_msg := ('CS_ServiceRequest_PVT.Update_ServiceRequest Return Status : '||l_return_status);
2559 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2560 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2561 END IF;
2562 END IF;
2563 END IF;
2564
2565 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2566
2567 IF l_note_type IS NOT NULL THEN
2568
2569 -- Log JTF_NOTES_PUB API is being called to create notes for signer's comments.
2570
2571 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2572 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2573 dbg_msg := ('Calling JTF_NOTES_PUB.Create_Note (1)');
2574 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2575 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2576 END IF;
2577 END IF;
2578 END IF;
2579
2580 FOR k IN 1..l_notes_table.COUNT
2581 LOOP
2582 l_note_id := NULL;
2583 l_return_status := FND_API.G_RET_STS_SUCCESS ;
2584
2585 JTF_Notes_PUB.Create_Note
2586 ( p_api_version => 1.0
2587 , p_init_msg_list => fnd_api.g_false
2588 , p_commit => fnd_api.g_false
2589 , p_validation_level => fnd_api.g_valid_level_full
2590 , p_source_object_id => p_incident_id
2591 , p_source_object_code => 'SR'
2592 , p_notes => l_notes_table(k).note
2593 , p_notes_detail => l_notes_table(k).note_detail
2594 , p_note_status => NVL(l_note_status,'I')
2595 , p_entered_by => fnd_global.user_id
2596 , p_entered_date => sysdate
2597 , p_last_update_date => sysdate
2598 , p_last_updated_by => fnd_global.user_id
2599 , p_creation_date => sysdate
2600 , p_created_by => fnd_global.user_id
2601 , p_last_update_login => fnd_global.login_id
2602 , p_note_type => l_note_type
2603 , x_return_status => l_return_status
2604 , x_msg_count => l_msg_count
2605 , x_msg_data => l_msg_data
2606 , x_jtf_note_id => l_note_id );
2607
2608
2609 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2610 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2611 dbg_msg := ('After Calling JTF_Notes_PUB.Create API (1)');
2612 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2613 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2614 END IF;
2615 dbg_msg := ('JTF_Notes_PUB.Create API Return Status (1) : '||l_return_status ||' Note Id : '||l_note_id);
2616 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2617 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2618 END IF;
2619 END IF;
2620 END IF;
2621
2622 -- if JTF API errors out then do not raise any errors
2623
2624 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2625
2626 --Get all the error messages and add error messages to the note
2627
2628 IF (FND_MSG_PUB.Count_Msg > 1) THEN
2629 FOR j in 1..FND_MSG_PUB.Count_Msg
2630 LOOP
2631 FND_MSG_PUB.Get(
2632 p_msg_index => j,
2633 p_encoded => 'F',
2634 p_data => lx_msg_data,
2635 p_msg_index_out => lx_msg_index_out);
2636
2637 l_note_text := l_note_text||' - '||lx_msg_data;
2638 END LOOP;
2639 -- Log the error message returned by the SR Update API
2640
2641 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2642 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2643 dbg_msg := ('Create Notes (1) JTF API Error ');
2644 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2645 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2646 END IF;
2647 dbg_msg := ('Error : '||l_note_text);
2648 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2649 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2650 END IF;
2651 END IF;
2652 END IF;
2653 ELSE
2654 --Only one error
2655 FND_MSG_PUB.Get(
2656 p_msg_index => 1,
2657 p_encoded => 'F',
2658 p_data => lx_msg_data,
2659 p_msg_index_out => lx_msg_index_out);
2660
2661 l_note_text := l_note_text||l_chr_newline||lx_msg_data;
2662
2663 -- Log the error message returned by the SR Update API
2664
2665 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2666 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2667 dbg_msg := ('Create Notes(1) JTF API Error ');
2668 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2669 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2670 END IF;
2671 dbg_msg := ('Error : '||l_note_text);
2672 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2673 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2674 END IF;
2675 END IF;
2676 END IF;
2677 END IF ; -- fnd_msg_pub
2678
2679 END IF ;
2680
2681 END LOOP;
2682
2683 END IF ;
2684
2685 -- Log EDR_TRANS_ACKN_PUB.SEND_ACKN is being called
2686
2687 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2688 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2689 dbg_msg := ('Calling EDR_TRANS_ACKN_PUB.SEND_ACKN');
2690 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2691 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2692 END IF;
2693 END IF;
2694 END IF;
2695
2696 -- Send an EDR acknowledgement.
2697 l_return_status := FND_API.G_RET_STS_SUCCESS ;
2698
2699 EDR_TRANS_ACKN_PUB.SEND_ACKN
2700 ( p_api_version => 1.0,
2701 p_init_msg_list => FND_API.G_TRUE ,
2702 x_return_status => l_return_status,
2703 x_msg_count => l_msg_count,
2704 x_msg_data => l_msg_data,
2705 p_event_name => 'oracle.apps.cs.sr.ServiceRequesstApproval',
2706 p_event_key => p_incident_id,
2707 p_ERECord_id => l_ERECORD_ID,
2708 p_trans_status => 'SUCCESS',
2709 p_ackn_by => 'Service Request Approval Process',
2710 p_ackn_note => 'Service Request Approval Completed',
2711 p_autonomous_commit => FND_API.G_FALSE );
2712
2713
2714 -- Log EDR_TRANS_ACKN_PUB.SEND_ACKN return status
2715
2716 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2717 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2718 dbg_msg := ('After Calling EDR_TRANS_ACKN_PUB.SEND_ACKN');
2719 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2720 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2721 END IF;
2722 dbg_msg := ('EDR_TRANS_ACKN_PUB.SEND_ACKN Return Status : '||l_return_status);
2723 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2724 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2725 END IF;
2726 END IF;
2727 END IF;
2728
2729 CLOSE c_Get_SR_Version ;
2730 ELSE
2731
2732 -- Updating SR to the target status has failed.
2733 -- Additional note about this failure should be created and SR should be updated
2734 -- to the previous status.
2735
2736 -- Populate note details
2737 IF l_sig_status = 'APPROVED' THEN
2738 fnd_message.set_name ('CS', 'CS_SR_ERES_APPROVED');
2739 l_note_text := fnd_message.get;
2740 ELSIF l_sig_status = 'REJECTED' THEN
2741 fnd_message.set_name ('CS', 'CS_SR_ERES_REJECTED');
2742 l_note_text := fnd_message.get;
2743 END IF ;
2744
2745 -- Add a note that an error encountered while ERES processing.
2746
2747 fnd_message.set_name ('CS','CS_ERES_ERROR_COMMENT_MSG');
2748 l_note_err_msg := fnd_message.get;
2749 l_note_text := l_note_text||l_chr_newline||l_note_err_msg||l_chr_newline;
2750
2751 --Get all the error messages and add error messages to the note
2752
2753 IF (FND_MSG_PUB.Count_Msg > 1) THEN
2754 FOR j in 1..FND_MSG_PUB.Count_Msg
2755 LOOP
2756 FND_MSG_PUB.Get(
2757 p_msg_index => j,
2758 p_encoded => 'F',
2759 p_data => lx_msg_data,
2760 p_msg_index_out => lx_msg_index_out);
2761
2762 l_note_text := l_note_text||lx_msg_data;
2763 END LOOP;
2764 -- Log the error message returned by the SR Update API
2765
2766 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2767 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2768 dbg_msg := ('Update Service Request(1) API Error ');
2769 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2770 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2771 END IF;
2772 dbg_msg := ('Error : '||l_note_text);
2773 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2774 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2775 END IF;
2776 END IF;
2777 END IF;
2778 ELSE
2779 --Only one error
2780 FND_MSG_PUB.Get(
2781 p_msg_index => 1,
2782 p_encoded => 'F',
2783 p_data => lx_msg_data,
2784 p_msg_index_out => lx_msg_index_out);
2785
2786 l_note_text := l_note_text||l_chr_newline||lx_msg_data;
2787
2788 -- Log the error message returned by the SR Update API
2789
2790 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2791 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2792 dbg_msg := ('Update Service Request(1) API Error ');
2793 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2794 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2795 END IF;
2796 dbg_msg := ('Error : '||l_note_text);
2797 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2798 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2799 END IF;
2800 END IF;
2801 END IF;
2802 END IF;
2803
2804 --l_notes_table.extend;
2805 l_notes_table(q).NOTE := l_note_text;
2806 l_notes_table(q).NOTE_DETAIL := l_note_text;
2807 l_notes_table(q).NOTE_TYPE := l_note_type;
2808 l_notes_table(q).NOTE_STATUS := NVL(l_note_status,'I');
2809 l_notes_table(q).SOURCE_OBJECT_CODE := 'SR';
2810 l_notes_table(q).SOURCE_OBJECT_ID := p_incident_id;
2811 q := q + 1 ;
2812
2813 -- Log Get_Target_SR_Status is being called
2814
2815 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2816 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2817 dbg_msg := ('Calling Get_Target_SR_Status (2)');
2818 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2819 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2820 END IF;
2821 END IF;
2822 END IF;
2823
2824 -- Get the initial status of the service request.
2825
2826 l_return_status := FND_API.G_RET_STS_SUCCESS;
2827
2828 Get_Target_SR_Status
2829 ( P_Incident_Id => P_Incident_id,
2830 P_Intermediate_Status_Id => P_Intermediate_Status_Id,
2831 P_Action => 'ERROR' ,
2832 X_Target_Status_Id => l_target_status_id,
2833 X_Return_Status => l_return_status,
2834 X_Msg_count => l_msg_count ,
2835 X_Msg_data => l_msg_data );
2836
2837
2838 -- Log Get_Target_SR_Status return status
2839
2840 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2841 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2842 dbg_msg := ('After Calling Get_Target_SR_Status (2)');
2843 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2844 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2845 END IF;
2846 dbg_msg := ('Get_Target_SR_Status Return Status : '||l_return_status);
2847 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2848 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2849 END IF;
2850 dbg_msg := ('Get_Target_SR_Status Target Status ID : '||l_target_status_id);
2851 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2852 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2853 END IF;
2854 END IF;
2855 END IF;
2856
2857 -- Log CS_ServiceRequest_PVT.Update_ServiceRequest is being called
2858
2859 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2860 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2861 dbg_msg := ('Calling CS_ServiceRequest_PVT.Update_ServiceRequest (2)');
2862 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2863 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2864 END IF;
2865 END IF;
2866 END IF;
2867
2868 -- call update service request api to update the service request to the initial status.
2869
2870 l_servicerequest_Rec.status_id := l_target_status_id;
2871 l_return_status := FND_API.G_RET_STS_SUCCESS ;
2872
2873
2874 CS_ServiceRequest_PVT.Update_ServiceRequest
2875 ( p_api_version => 4.0,
2876 x_return_status => l_return_status,
2877 x_msg_count => l_msg_count,
2878 x_msg_data => l_msg_data,
2879 p_request_id => p_incident_id,
2880 p_audit_id => null,
2881 p_object_version_number => l_sr_version,
2882 p_last_updated_by => fnd_global.user_id,
2883 p_last_update_date => sysdate,
2884 p_service_request_rec => l_servicerequest_Rec,
2885 p_notes => l_notes_table_dummy,
2886 p_contacts => l_contacts_table,
2887 p_validate_sr_closure => NVL(l_validate_sr_close,'N'),
2888 p_auto_close_child_entities => NVL(l_validate_sr_close,'N'),
2889 x_sr_update_out_rec => l_sr_update_out_rec );
2890
2891 -- Log CS_ServiceRequest_PVT.Update_ServiceRequest return status
2892
2893 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2894 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2895 dbg_msg := ('After Calling CS_ServiceRequest_PVT.Update_ServiceRequest (2)');
2896 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2897 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2898 END IF;
2899 dbg_msg := ('CS_ServiceRequest_PVT.Update_ServiceRequest Return Status : '||l_return_status);
2900 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2901 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2902 END IF;
2903 END IF;
2904 END IF;
2905
2906 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2907
2908 IF l_note_type IS NOT NULL THEN
2909
2910 -- Log JTF_NOTES_PUB API is being called to create notes for signer's comments.
2911
2912 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2913 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2914 dbg_msg := ('Calling JTF_NOTES_PUB.Create_Note (2)');
2915 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2916 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2917 END IF;
2918 END IF;
2919 END IF;
2920
2921 FOR k IN 1..l_notes_table.COUNT
2922 LOOP
2923 l_note_id := NULL;
2924 l_return_status := FND_API.G_RET_STS_SUCCESS;
2925
2926 JTF_Notes_PUB.Create_Note
2927 ( p_api_version => 1.0
2928 , p_init_msg_list => fnd_api.g_false
2929 , p_commit => fnd_api.g_false
2930 , p_validation_level => fnd_api.g_valid_level_full
2931 , p_source_object_id => p_incident_id
2932 , p_source_object_code => 'SR'
2933 , p_notes => l_notes_table(k).note
2934 , p_notes_detail => l_notes_table(k).note_detail
2935 , p_note_status => NVL(l_note_status,'I')
2936 , p_entered_by => fnd_global.user_id
2937 , p_entered_date => sysdate
2938 , p_last_update_date => sysdate
2939 , p_last_updated_by => fnd_global.user_id
2940 , p_creation_date => sysdate
2941 , p_created_by => fnd_global.user_id
2942 , p_last_update_login => fnd_global.login_id
2943 , p_note_type => l_note_type
2944 , x_return_status => l_return_status
2945 , x_msg_count => l_msg_count
2946 , x_msg_data => l_msg_data
2947 , x_jtf_note_id => l_note_id );
2948
2949
2950 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2951 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2952 dbg_msg := ('After Calling JTF_Notes_PUB.Create API (2)');
2953 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2954 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2955 END IF;
2956 dbg_msg := ('JTF_Notes_PUB.Create API Return Status (2) : '||l_return_status ||' Note Id : '||l_note_id);
2957 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2958 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2959 END IF;
2960 END IF;
2961 END IF;
2962
2963 -- if JTF API errors out then do not raise any errors
2964
2965 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2966
2967 --Get all the error messages and add error messages to the note
2968
2969 IF (FND_MSG_PUB.Count_Msg > 1) THEN
2970 FOR j in 1..FND_MSG_PUB.Count_Msg
2971 LOOP
2972 FND_MSG_PUB.Get(
2973 p_msg_index => j,
2974 p_encoded => 'F',
2975 p_data => lx_msg_data,
2976 p_msg_index_out => lx_msg_index_out);
2977
2978 l_note_text := l_note_text||' - '||lx_msg_data;
2979 END LOOP;
2980 -- Log the error message returned by the SR Update API
2981
2982 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
2983 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
2984 dbg_msg := ('Create Notes (2) JTF API Error ');
2985 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2986 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2987 END IF;
2988 dbg_msg := ('Error : '||l_note_text);
2989 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2990 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
2991 END IF;
2992 END IF;
2993 END IF;
2994 ELSE
2995 --Only one error
2996 FND_MSG_PUB.Get(
2997 p_msg_index => 1,
2998 p_encoded => 'F',
2999 p_data => lx_msg_data,
3000 p_msg_index_out => lx_msg_index_out);
3001
3002 l_note_text := l_note_text||l_chr_newline||lx_msg_data;
3003
3004 -- Log the error message returned by the SR Update API
3005
3006 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3007 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
3008 dbg_msg := ('Create Notes(2) JTF API Error ');
3009 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3010 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
3011 END IF;
3012 dbg_msg := ('Error : '||l_note_text);
3013 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3014 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
3015 END IF;
3016 END IF;
3017 END IF;
3018 END IF ; -- fnd_msg_pub
3019 END IF ;
3020 END LOOP;
3021
3022 END IF ; -- end if for the note_type check
3023
3024 END IF ; -- End if for the return status of the Update SR call (2)
3025
3026
3027 -- Log EDR_TRANS_ACKN_PUB.SEND_ACKN is being called
3028
3029 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3030 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
3031 dbg_msg := ('Calling EDR_TRANS_ACKN_PUB.SEND_ACKN (3)');
3032 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3033 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
3034 END IF;
3035 END IF;
3036 END IF;
3037
3038 -- Send an EDR acknowledgement.
3039
3040 l_return_status := FND_API.G_RET_STS_SUCCESS;
3041
3042 EDR_TRANS_ACKN_PUB.SEND_ACKN
3043 ( p_api_version => 1.0,
3044 p_init_msg_list => FND_API.G_TRUE ,
3045 x_return_status => l_return_status,
3046 x_msg_count => l_msg_count,
3047 x_msg_data => l_msg_data,
3048 p_event_name => 'oracle.apps.cs.sr.ServiceRequesstApproval',
3049 p_event_key => p_incident_id,
3050 p_ERECord_id => l_ERECORD_ID,
3051 p_trans_status => 'SUCCESS',
3052 p_ackn_by => 'Service Request Approval Process',
3053 p_ackn_note => 'Service Request Approval Completed',
3054 p_autonomous_commit => FND_API.G_FALSE );
3055
3056 -- Log EDR_TRANS_ACKN_PUB.SEND_ACKN return status
3057
3058 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
3059 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process')) THEN
3060 dbg_msg := ('After Calling EDR_TRANS_ACKN_PUB.SEND_ACKN');
3061 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3062 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
3063 END IF;
3064 dbg_msg := ('EDR_TRANS_ACKN_PUB.SEND_ACKN Return Status : '||l_return_status);
3065 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3066 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_ERES_INT_PKG.Post_Approval_Process', dbg_msg);
3067 END IF;
3068 END IF;
3069 END IF;
3070 CLOSE c_Get_SR_Version ;
3071
3072 END IF ;
3073
3074 EXCEPTION
3075 WHEN OTHERS THEN
3076 FND_MESSAGE.SET_NAME('CS','CS_ERES_CALLBACK_API_FAILED');
3077 FND_MESSAGE.SET_TOKEN ('P_SQLCODE',SQLCODE);
3078 FND_MESSAGE.SET_TOKEN ('P_SQLERRM',SQLERRM);
3079 FND_MSG_PUB.ADD;
3080 RAISE;
3081
3082 END Post_Approval_Process ;
3083
3084 PROCEDURE Get_Target_SR_Status
3085 ( P_Incident_Id IN NUMBER,
3086 P_Intermediate_Status_Id IN NUMBER,
3087 P_Action IN VARCHAR2,
3088 X_Target_Status_Id OUT NOCOPY NUMBER,
3089 X_Return_Status OUT NOCOPY VARCHAR2,
3090 X_Msg_count OUT NOCOPY NUMBER,
3091 X_Msg_data OUT NOCOPY NUMBER) IS
3092
3093 -- Cursor to get the target status using status definition setup.
3094
3095 CURSOR c_get_target_status IS
3096 SELECT incident_status_id ,
3097 approval_action_status_id ,
3098 rejection_action_status_id
3099 FROM cs_incident_statuses a
3100 WHERE a.intermediate_status_id = P_Intermediate_Status_Id;
3101
3102 -- Cursor to get the target status using SR audit.
3103
3104 CURSOR c_get_initial_status IS
3105 SELECT old_incident_status_id
3106 FROM cs_incidents_audit_b
3107 WHERE rowid = ( SELECT max(rowid)
3108 FROM cs_incidents_audit_b
3109 WHERE incident_id = p_incident_id
3110 AND incident_status_id = p_intermediate_status_id
3111 AND old_incident_status_id <> p_intermediate_status_id
3112 GROUP BY incident_id , incident_status_id);
3113
3114 -- Local Variables
3115
3116 l_status_id NUMBER;
3117 l_approved_status_id NUMBER;
3118 l_rejected_status_id NUMBER;
3119 l_initial_status_id NUMBER;
3120
3121 BEGIN
3122 IF p_action = 'ERROR' THEN
3123
3124 OPEN c_get_initial_status ;
3125 FETCH c_get_initial_status INTO l_initial_status_id;
3126 CLOSE c_get_initial_status;
3127
3128 X_Target_Status_Id := l_initial_status_id;
3129 ELSE
3130 OPEN c_get_target_status ;
3131 FETCH c_get_target_status INTO l_status_id,l_approved_status_id,l_rejected_status_id;
3132 CLOSE c_get_target_status ;
3133
3134 IF p_action = 'SUCCESS' THEN
3135 IF l_approved_status_id IS NOT NULL THEN
3136 X_Target_Status_Id := l_approved_status_id;
3137 ELSE
3138 X_Target_Status_Id := l_status_id;
3139 END IF ;
3140 ELSIF p_action IN ('REJECTED','TIMEOUT') THEN
3141 IF l_rejected_status_id IS NOT NULL THEN
3142 X_Target_Status_Id := l_rejected_status_id;
3143 ELSE
3144 OPEN c_get_initial_status ;
3145 FETCH c_get_initial_status INTO l_initial_status_id;
3146 CLOSE c_get_initial_status;
3147
3148 X_Target_Status_Id := l_initial_status_id;
3149
3150 END IF ;
3151 END IF ;
3152 END IF ;
3153
3154 EXCEPTION
3155 WHEN OTHERS THEN
3156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3157 FND_MESSAGE.SET_NAME('CS','CS_ERES_INVLD_INTMED_STS');
3158 FND_MSG_PUB.ADD;
3159 RAISE;
3160 END Get_Target_SR_Status ;
3161
3162 END CS_ERES_INT_PKG;