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