[Home] [Help]
PACKAGE BODY: APPS.JTF_ESCWFACTIVITY_PVT
Source
1 PACKAGE BODY JTF_EscWFActivity_PVT AS
2 /* $Header: jtfvewab.pls 120.4.12010000.3 2008/11/05 11:26:04 rkamasam ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_EscWFActivity_PVT';
5 g_success CONSTANT VARCHAR2(15) := 'NOERROR';
6 g_critical CONSTANT VARCHAR2(15) := 'CRITICAL';
7 g_noncritical CONSTANT VARCHAR2(15) := 'NONCRITICAL';
8
9 ----------------------------------------------------------------------------
10 -- Attributes set by WF before calling this package
11 ----------------------------------------------------------------------------
12
13 -- unique object identifier
14 g_object_id JTF_TASKS_VL.TASK_ID%TYPE;
15
16 -- type of object.
17 -- possible values are: - 'CS_BRM_3D_SERVICE_REQUEST_V' - SR
18 -- - 'CSS_BRM_3D_DEFECT_V' - Defect
19 -- - 'JTF_BRM_3D_TASK_V' - Task
20 g_object_type AK_OBJECTS_VL.DATABASE_OBJECT_NAME%TYPE;
21
22 -- unique rule identifier
23 g_rule_id JTF_BRM_RULES_VL.RULE_ID%TYPE;
24
25 ----------------------------------------------------------------------------
26 -- Attributes set by this package before returning to WF
27 ----------------------------------------------------------------------------
28
29 -- WF user id of person to receive notification
30 g_notif_person_id JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE;
31
32 -- user-visible object name
33 g_object_name AK_OBJECTS_VL.NAME%TYPE;
34
35 -- short name for the rule
36 g_rule_name JTF_BRM_RULES_VL.RULE_NAME%TYPE;
37
38 -- long description of rule
39 g_rule_desc JTF_BRM_RULES_VL.RULE_DESCRIPTION%TYPE;
40
41 -- business rule owner
42 g_rule_owner JTF_BRM_RULES_VL.RULE_OWNER%TYPE;
43
44 -- date and time the object was detected
45 g_detected_date DATE;
46
47 -- object owner
48 g_owner_id JTF_TASKS_VL.OWNER_ID%TYPE;
49
50 -- SR object owner in case owner is a group
51 g_owner_group_id NUMBER;
52
53 -- SR group type either Group or Team
54 g_owner_group_type VARCHAR2(30);
55
56 -- object owner's territory
57 g_territory JTF_TASKS_VL.OWNER_TERRITORY_ID%TYPE;
58
59 -- object owner's resource type
60 g_res_type_code JTF_TASKS_VL.OWNER_TYPE_CODE%TYPE;
61
62 -- derive this from the object
63 g_object_type_code JTF_OBJECTS_VL.OBJECT_CODE%TYPE;
64
65 -- object name as held in jtf_objects, used in Task APIs
66 g_jtf_object_name JTF_OBJECTS_VL.SELECT_NAME%TYPE;
67
68 --Added by MPADHIAR for Bug#5068840
69
70 --objects's Customer ID
71 g_customer_id jtf_tasks_v.customer_id%type;
72
73 --objects's Customer Account ID
74 g_cust_account_id jtf_tasks_v.cust_account_id%type;
75
76 --objects's Customer Address ID
77 g_address_id jtf_tasks_v.address_id%type;
78
79
80 g_debug VARCHAR2(1):= NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
81
82 g_debug_level NUMBER := NVL(fnd_profile.value_specific('AFLOG_LEVEL'), fnd_log.level_event);
83
84 PROCEDURE debug(p_level NUMBER, p_module VARCHAR2, p_message VARCHAR2) IS
85 BEGIN
86 IF g_debug = 'Y' AND p_level >= g_debug_level THEN
87 fnd_log.string(p_level, 'jtf.plsql.JTF_ESCWFACTIVITY_PVT.' || p_module, p_message);
88 END IF;
89 END debug;
90
91
92 FUNCTION Get_Messages_On_Stack
93 RETURN VARCHAR2
94 IS
95 l_msg_count NUMBER := 0;
96 l_msg FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
97
98 BEGIN
99
100 l_msg_count := fnd_msg_pub.count_msg;
101 IF l_msg_count > 0
102 THEN
103 l_msg := ' ' || substr(fnd_msg_pub.get( fnd_msg_pub.G_FIRST
104 , fnd_api.G_FALSE
105 ),1, 512);
106 END IF;
107
108 FOR iIndex IN 1..(l_msg_count-1) LOOP
109 l_msg := l_msg || ' ' || substr(fnd_msg_pub.get(fnd_msg_pub.G_NEXT
110 ,fnd_api.G_FALSE
111 ), 1, 512);
112 END LOOP;
113
114 END Get_Messages_On_Stack;
115
116
117 --Added by MPADHIAR for Bug#5068840 Ends Here
118
119 ----------------------------------------------------------------------------
120 -- Start of comments
121 -- Function : Get_WorkflowAttribute
122 -- Description : Return the code or value for the specified Workflow
123 -- Attribute, depending on p_return_type.
124 -- This function is private to this package.
125 -- Parameters :
126 -- name direction type required?
127 -- ---- --------- ---- ---------
128 -- p_attr_name IN VARCHAR2 required
129 -- p_return_type IN VARCHAR2 required
130 --
131 -- returns
132 -- -------
133 -- x_param_value OUT VARCHAR2
134 --
135 -- Notes :
136 -- p_return_type = CODE - return the code
137 -- p_return_type = VALUE - return the value
138 --
139 -- End of comments
140 ----------------------------------------------------------------------------
141 FUNCTION Get_WorkflowAttribute
142 ( p_attr_name IN VARCHAR2
143 , p_return_type IN VARCHAR2
144 ) RETURN VARCHAR2
145 IS
146 -------------------------------------------------------------------------
147 -- Cursor for Rule query
148 -------------------------------------------------------------------------
149 CURSOR c_get_rule(b_rule_id JTF_BRM_PROCESSES.RULE_ID%TYPE)
150 IS SELECT workflow_item_type
151 , workflow_process_name
152 FROM jtf_brm_processes
153 WHERE rule_id = b_rule_id;
154
155 l_attr_value JTF_BRM_WF_ATTR_VALUES_V.WF_ATTRIBUTE_VALUE%TYPE;
156 l_item_type JTF_BRM_PROCESSES.WORKFLOW_ITEM_TYPE%TYPE;
157 l_process_name JTF_BRM_PROCESSES.WORKFLOW_PROCESS_NAME%TYPE;
158
159 BEGIN
160
161 debug( fnd_log.level_statement
162 , 'Get_WorkflowAttribute'
163 , 'input attr name'||p_attr_name|| 'return type'||p_return_type
164 );
165 OPEN c_get_rule(g_rule_id);
166 FETCH c_get_rule INTO l_item_type,
167 l_process_name;
168 IF (c_get_rule%NOTFOUND)
169 THEN
170 -----------------------------------------------------------------------
171 -- Query Rule didn't find a record
172 -----------------------------------------------------------------------
173 debug( fnd_log.level_error, 'Get_WorkflowAttribute', 'There is no rule for the query'||g_rule_id);
174 CLOSE c_get_rule;
175 RAISE FND_API.G_EXC_ERROR;
176 END IF;
177 CLOSE C_GET_RULE;
178 IF (p_return_type = 'CODE')
179 THEN
180 l_attr_value := JTF_BRM_UTILITY_PVT.Attribute_Code
181 ( p_rule_id => g_rule_id
182 , p_wf_item_type => l_item_type
183 , p_wf_process_name => l_process_name
184 , p_wf_attribute_name => p_attr_name
185 );
186 ELSE
187 l_attr_value := JTF_BRM_UTILITY_PVT.Attribute_Meaning
188 ( p_rule_id => g_rule_id
189 , p_wf_item_type => l_item_type
190 , p_wf_process_name => l_process_name
191 , p_wf_attribute_name => p_attr_name
192 );
193 END IF;
194 debug( fnd_log.level_statement, 'Get_WorkflowAttribute', 'the attribute value is '||l_attr_value);
195 RETURN(l_attr_value);
196
197 EXCEPTION
198 WHEN OTHERS
199 THEN
200 debug( fnd_log.level_unexpected, 'Get_WorkflowAttribute'
201 , 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM
202 );
203 RAISE;
204
205 END Get_WorkflowAttribute;
206
207 ----------------------------------------------------------------------------
208 -- Start of comments
209 -- Function : Get_EmployeeID
210 -- Description : Return an Employee ID when given a resource_id.
211 -- This function is private to this package.
212 -- Parameters :
213 -- name direction type required?
214 -- ---- --------- ---- ---------
215 -- p_resource_id IN NUMBER required
216 -- x_resultout OUT VARCHAR2
217 --
218 -- returns
219 -- -------
220 -- x_employee_id OUT NUMBER
221 --
222 -- Notes :
223 --
224 -- End of comments
225 ----------------------------------------------------------------------------
226 FUNCTION Get_EmployeeID
227 ( p_resource_id IN NUMBER
228 , x_resultout OUT NOCOPY VARCHAR2
229 ) RETURN NUMBER
230 IS
231 -------------------------------------------------------------------------
232 -- Cursor for Resources query
233 -------------------------------------------------------------------------
234 CURSOR c_query_emp (b_resource_id JTF_RS_EMP_DTLS_VL.RESOURCE_ID%TYPE)
235 IS SELECT source_id
236 FROM JTF_RS_RESOURCE_EXTNS
237 WHERE resource_id = b_resource_id;
238
239 l_person_id JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE;
240
241 BEGIN
242 x_resultout := FND_API.G_Ret_Sts_Success;
243
244 -- -----------------------------------------------------------------------
245 -- Query Resources view to get the employee_id
246 -- -----------------------------------------------------------------------
247 OPEN c_query_emp(p_resource_id);
248 FETCH c_query_emp INTO l_person_id;
249 IF (c_query_emp%NOTFOUND)
250 THEN
251 -----------------------------------------------------------------------
252 -- Query Resources didn't find a record
253 -----------------------------------------------------------------------
254 debug( fnd_log.level_error, 'Get_EmployeeID', 'Employee details not found for the resource'||p_resource_id);
255 CLOSE c_query_emp;
256 RAISE FND_API.G_EXC_ERROR;
257 END IF;
258 CLOSE c_query_emp;
259 debug( fnd_log.level_statement, 'Get_EmployeeID', 'Employee id for the resource'||p_resource_id||':'||l_person_id);
260 RETURN(l_person_id);
261
262 EXCEPTION
263 WHEN FND_API.G_EXC_ERROR
264 THEN
265 x_resultout := FND_API.G_Ret_Sts_Error;
266 debug( fnd_log.level_error, 'Get_EmployeeID'
267 , 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM
268 );
269 RETURN(NULL);
270 WHEN OTHERS
271 THEN
272 x_resultout := FND_API.G_Ret_Sts_Unexp_Error;
273 debug( fnd_log.level_unexpected, 'Get_EmployeeID'
274 , 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM
275 );
276 RETURN(NULL);
277
278 END Get_EmployeeID;
279
280 ----------------------------------------------------------------------------
281 -- Start of comments
282 -- Function : Get_EmployeeRole
283 -- Description : Return an Employee's WF role when given an employee_id.
284 -- This function is private to this package.
285 -- Parameters :
286 -- name direction type required?
287 -- ---- --------- ---- ---------
288 -- p_employee_id IN NUMBER required
289 -- x_resultout OUT VARCHAR2
290 --
291 -- returns
292 -- -------
293 -- x_employee_role OUT VARCHAR2
294 --
295 -- Notes :
296 --
297 -- End of comments
298 ----------------------------------------------------------------------------
299 FUNCTION Get_EmployeeRole
300 ( p_employee_id IN NUMBER
301 , x_resultout OUT NOCOPY VARCHAR2
302 ) RETURN VARCHAR2
303 IS
304 l_wf_role FND_USER.USER_NAME%TYPE;
305 l_role_display_name wf_local_roles.display_name%type;
306
307 BEGIN
308
309 x_resultout := FND_API.G_RET_STS_SUCCESS;
310
311 -------------------------------------------------------------------------
312 -- Call Workflow API to get the role
313 -- If there is more than one role for this employee, the API will
314 -- return the first one fetched. If no Workflow role exists for
315 -- the employee, out variable will be NULL
316 -------------------------------------------------------------------------
317 WF_DIRECTORY.GetRoleName( p_orig_system => 'PER'
318 , p_orig_system_id => p_employee_id
319 , p_name => l_wf_role
320 , p_display_name => l_role_display_name
321 );
322
323 IF (l_wf_role IS NULL)
324 THEN
325 x_resultout := FND_API.G_RET_STS_ERROR;
326 debug( fnd_log.level_error, 'Get_EmployeeRole', 'No role found for the employee:'||p_employee_id);
327 END IF;
328 RETURN(l_wf_role);
329
330 EXCEPTION
331 WHEN FND_API.G_EXC_ERROR
332 THEN
333 x_resultout := FND_API.G_Ret_Sts_Error;
334 debug( fnd_log.level_error, 'Get_EmployeeRole'
335 , 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM
336 );
337 RETURN(NULL);
338 WHEN OTHERS
339 THEN
340 x_resultout := FND_API.G_Ret_Sts_Unexp_Error;
341 debug( fnd_log.level_unexpected, 'Get_EmployeeRole'
342 , 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM
343 );
344 RETURN(NULL);
345
346 END Get_EmployeeRole;
347
348
349 ----------------------------------------------------------------------------
350 -- Start of comments
351 -- Function : Get_RuleOwner
352 -- Description : Return an Apps resource ID or person ID for the owner of
353 -- the business rule. This function is private to this
354 -- package.
355 -- Parameters :
356 -- name direction type required?
357 -- ---- --------- ---- ---------
358 -- p_id_type IN VARCHAR2 required
359 -- x_resultout OUT VARCHAR2
360 --
361 -- returns
362 -- -------
363 -- x_owner_id OUT NUMBER
364 --
365 -- Notes :
366 --
367 -- End of comments
368 ----------------------------------------------------------------------------
369 FUNCTION Get_RuleOwner
370 ( p_id_type IN VARCHAR2
371 , x_resultout OUT NOCOPY VARCHAR2
372 ) RETURN NUMBER
373 IS
374 -------------------------------------------------------------------------
375 -- Cursor for Resources query
376 -------------------------------------------------------------------------
377 CURSOR c_query_resource (b_source_id JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE)
378 IS SELECT resource_id
379 FROM JTF_RS_RESOURCE_EXTNS
380 WHERE source_id = b_source_id;
381
382 l_owner_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
383
384 BEGIN
385 IF (p_id_type = 'RESOURCE')
386 THEN
387 ---------------------------------------------------------------------
388 -- Query Resources view to get BR Owner resource_id
389 ---------------------------------------------------------------------
390 OPEN c_query_resource(g_rule_owner);
391 FETCH c_query_resource INTO l_owner_id;
392 IF (c_query_resource%NOTFOUND)
393 THEN
394 -------------------------------------------------------------------
395 -- Query Resources didn't find a record
396 -------------------------------------------------------------------
397 debug( fnd_log.level_error, 'Get_RuleOwner', 'No resource found for the rule owner:'||g_rule_owner);
398 CLOSE c_query_resource;
399 RAISE FND_API.G_EXC_ERROR;
400 END IF;
401 CLOSE c_query_resource;
402 ELSE
403 l_owner_id := g_rule_owner;
404 END IF;
405 debug( fnd_log.level_statement, 'Get_RuleOwner', 'The returned rule owner is :'||l_owner_id);
406 RETURN(l_owner_id);
407
408 EXCEPTION
409 WHEN FND_API.G_EXC_ERROR
410 THEN
411 x_resultout := FND_API.G_Ret_Sts_Error;
412 RETURN(NULL);
413 WHEN OTHERS
414 THEN
415 x_resultout := FND_API.G_Ret_Sts_Unexp_Error;
416 RETURN(NULL);
417
418 END Get_RuleOwner;
419
420 ----------------------------------------------------------------------------
421 -- Start of comments
422 -- Function : Get_EscTerrContact
423 -- Description : Return an Apps resource ID for the primary contact in the
424 -- escalation territory of the source document, or of the
425 -- catch-all territory if the source document doesn't have a
426 -- territory, or of the Business Rule Owner if the primary
427 -- contact is not an employee. This function is private to
428 -- this package.
429 -- Parameters :
430 -- name direction type required?
431 -- ---- --------- ---- ---------
432 -- p_id_type IN VARCHAR2 required
433 -- x_res_type OUT VARCHAR2
434 -- x_resultout OUT VARCHAR2
435 --
436 -- returns
437 -- -------
438 -- x_contact_id OUT NUMBER
439 --
440 -- Notes :
441 --
442 -- End of comments
443 ----------------------------------------------------------------------------
444 FUNCTION Get_EscTerrContact
445 ( p_id_type IN VARCHAR2
446 , x_res_type OUT NOCOPY VARCHAR2
447 , x_resultout OUT NOCOPY VARCHAR2
448 ) RETURN NUMBER
449 IS
450 -------------------------------------------------------------------------
451 -- Standard API out parameters
452 -------------------------------------------------------------------------
453 l_return_status VARCHAR2(1);
454 l_msg_count NUMBER;
455 l_msg_data VARCHAR2(2000);
456 l_api_name CONSTANT VARCHAR2(30) := 'Get_EscTerrContact';
457
458 -------------------------------------------------------------------------
459 -- Cursor for Resources query
460 -------------------------------------------------------------------------
461 CURSOR c_query_resource(b_source_id JTF_RS_EMP_DTLS_VL.SOURCE_ID%TYPE)
462 IS SELECT resource_id
463 FROM JTF_RS_EMP_DTLS_VL
464 WHERE source_id = b_source_id;
465
466 -------------------------------------------------------------------------
467 -- Territory record and table definitions (returned from
468 -- jtf_territories_get APIs)
469 -------------------------------------------------------------------------
470 l_terr_resource_table jtf_territory_get_pub.QualifyingRsc_out_tbl_type;
471 l_terr_record jtf_territory_get_pub.Terr_Rec_Type;
472 l_terr_type_record jtf_territory_get_pub.Terr_Type_Rec_Type;
473 l_terr_sub_terr_table jtf_territory_get_pub.Terr_Tbl_Type;
474 l_terr_usgs_table jtf_territory_get_pub.Terr_Usgs_Tbl_Type;
475 l_terr_qtype_usgs_table jtf_territory_get_pub.Terr_QType_Usgs_Tbl_Type;
476 l_terr_qual_table jtf_territory_get_pub.Terr_Qual_Tbl_Type;
477 l_terr_values_table jtf_territory_get_pub.Terr_Values_Tbl_Type;
478 l_terr_rsc_table jtf_territory_get_pub.Terr_Rsc_Tbl_Type;
479 l_index BINARY_INTEGER;
480 l_esc_territory NUMBER;
481 l_contact_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
482 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
483 l_resultout VARCHAR2(80);
484
485 Begin
486 x_resultout := FND_API.G_Ret_Sts_Success;
487
488 debug( fnd_log.level_statement, l_api_name, 'Input ID type'||p_id_type);
489
490 -------------------------------------------------------------------------
491 -- Get the primary contact from the escalation territory
492 -------------------------------------------------------------------------
493 IF (g_territory IS NULL)
494 THEN
495 -----------------------------------------------------------------
496 -- There is no territory attached to the Source Document.
497 -- Use Business Rule owner as the escalation contact.
498 -----------------------------------------------------------------
499 l_resource_id := Get_RuleOwner( p_id_type => p_id_type
500 , x_resultout => l_resultout
501 );
502 IF (l_resultout <> FND_API.G_Ret_Sts_Success)
503 THEN
504 RAISE FND_API.G_EXC_ERROR;
505 END IF;
506
507 debug( fnd_log.level_statement, l_api_name, 'business rule owner for'||l_resource_id);
508
509 l_contact_id := l_resource_id;
510
511 x_res_type := jtf_ec_pub.g_escalation_owner_type_code;
512 ELSE
513 -----------------------------------------------------------------------
514 -- Territory exists in the source document
515 -- Use primary contact in relevant escalation territory
516 -----------------------------------------------------------------------
517 debug( fnd_log.level_statement, l_api_name, 'escalation territory exists for source doc');
518
519 JTF_TERRITORY_GET_PUB.Get_Escalation_Territory
520 ( p_api_version => 1.0
521 , p_init_msg_list => FND_API.G_TRUE
522 , x_return_status => l_return_status
523 , x_msg_count => l_msg_count
524 , x_msg_data => l_msg_data
525 , p_terr_id => g_territory
526 , x_escalation_terr_id => l_esc_territory
527 );
528
529 IF (l_return_status = FND_API.G_Ret_Sts_Success)
530 THEN
531 debug( fnd_log.level_statement, l_api_name, 'Getting the members for territory'||l_esc_territory);
532 JTF_TERRITORY_GET_PUB.Get_Escalation_TerrMembers
533 ( 1.0
534 , p_terr_id => l_esc_territory
535 , p_init_msg_list => FND_API.G_TRUE
536 , x_return_status => l_return_status
537 , x_msg_count => l_msg_count
538 , x_msg_data => l_msg_data
539 , x_QualifyingRsc_out_tbl => l_terr_resource_table
540 );
541 debug( fnd_log.level_statement, l_api_name, 'retrieving members was:'||l_return_status);
542 IF (l_return_status = FND_API.G_Ret_Sts_Success)
543 THEN
544 l_index := l_terr_resource_table.First;
545 LOOP
546 EXIT WHEN ( (l_terr_resource_table(l_index).primary_contact_flag = 'Y')
547 OR (l_index = l_terr_resource_table.Last)
548 );
549 l_index := l_terr_resource_table.Next(l_index);
550 END LOOP;
551
552 IF (l_terr_resource_table(l_index).primary_contact_flag = 'Y') and
553 (l_terr_resource_table(l_index).resource_type = 'RS_EMPLOYEE') Then
554 l_resource_id := l_terr_resource_table(l_index).resource_id;
555 x_res_type := l_terr_resource_table(l_index).resource_type;
556 IF (p_id_type = 'EMPLOYEE') Then
557 l_contact_id := Get_EmployeeID( p_resource_id => l_resource_id
558 , x_resultout => l_resultout
559 );
560 IF (l_resultout <> FND_API.G_Ret_Sts_Success) Then
561 debug( fnd_log.level_error, l_api_name, 'employee id not found for'||l_resource_id);
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564 ELSE
565 l_contact_id := l_resource_id;
566 END IF;
567 ELSE
568 -----------------------------------------------------------------
569 -- Territory has no primary contact, or the primary contact is
570 -- not an employee resource - use the Business Rule owner
571 -----------------------------------------------------------------
572 l_resource_id := Get_RuleOwner( p_id_type => p_id_type
573 , x_resultout => l_resultout
574 );
575 IF (l_resultout <> FND_API.G_Ret_Sts_Success)
576 THEN
577 RAISE FND_API.G_EXC_ERROR;
578 END IF;
579
580 debug( fnd_log.level_statement, l_api_name, 'business rule owner for'||l_resource_id);
581
582 l_contact_id := l_resource_id;
583
584 x_res_type := jtf_ec_pub.g_escalation_owner_type_code;
585 END IF;
586 ELSE
587 -------------------------------------------------------------------
588 -- Error from Get_Escalation_TerrMembers API
589 -------------------------------------------------------------------
590 debug( fnd_log.level_error, l_api_name, 'Getting the members for territory failed');
591 RAISE FND_API.G_EXC_ERROR;
592 END IF;
593 ELSE
594 ---------------------------------------------------------------------
595 -- Error from Get_Escalation_Territory API
596 ---------------------------------------------------------------------
597 debug( fnd_log.level_error, l_api_name, 'Getting the escalation territory failed');
598 RAISE FND_API.G_EXC_ERROR;
599 END IF;
600 END IF;
601 RETURN(l_contact_id);
602
603 EXCEPTION
604 WHEN FND_API.G_EXC_ERROR
605 THEN
606 x_resultout := FND_API.G_Ret_Sts_Error;
607 debug(fnd_log.LEVEL_statement, l_api_name, 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM);
608 RETURN(NULL);
609 WHEN OTHERS
610 THEN
611 x_resultout := FND_API.G_Ret_Sts_Unexp_Error;
612 debug( fnd_log.level_unexpected, l_api_name , 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM);
613 RETURN(NULL);
614
615 END Get_EscTerrContact;
616
617 ----------------------------------------------------------------------------
618 -- Start of comments
619 -- Function : Get_PersonID
620 -- Description : Return a Resource ID or Employee ID when given the role
621 -- this person fulfils in relation to the source document.
622 -- This function is private to this package.
623 -- Parameters :
624 -- name direction type required?
625 -- ---- --------- ---- ---------
626 -- p_document_role IN VARCHAR2 required
627 -- p_id_type IN VARCHAR2 required
628 -- x_res_type OUT VARCHAR2
629 -- x_resultout OUT VARCHAR2
630 --
631 -- returns
632 -- -------
633 -- x_person_id OUT NUMBER
634 --
635 -- Notes : p_id_type is either 'RESOURCE' or 'EMPLOYEE', which determines
636 -- what is returned in x_person_id
637 --
638 -- End of comments
639 ----------------------------------------------------------------------------
640 FUNCTION Get_PersonID
641 ( p_document_role IN VARCHAR2
642 , p_id_type IN VARCHAR2
643 , x_res_type OUT NOCOPY VARCHAR2
644 , x_resultout OUT NOCOPY VARCHAR2
645 ) RETURN NUMBER IS
646
647 -------------------------------------------------------------------------
648 -- Standard API out parameters
649 -------------------------------------------------------------------------
650 l_return_status VARCHAR2(1);
651 l_msg_count NUMBER;
652 l_msg_data VARCHAR2(2000);
653 l_api_name CONSTANT VARCHAR2(30) := 'Get_PersonID';
654
655 -------------------------------------------------------------------------
656 -- Territory record and table definitions (returned from
657 -- jtf_territories_get APIs)
658 -------------------------------------------------------------------------
659 l_terr_resource_table jtf_territory_get_pub.QualifyingRsc_out_tbl_type;
660 l_terr_record jtf_territory_get_pub.Terr_Rec_Type;
661 l_terr_type_record jtf_territory_get_pub.Terr_Type_Rec_Type;
662 l_terr_sub_terr_table jtf_territory_get_pub.Terr_Tbl_Type;
663 l_terr_usgs_table jtf_territory_get_pub.Terr_Usgs_Tbl_Type;
664 l_terr_qtype_usgs_table jtf_territory_get_pub.Terr_QType_Usgs_Tbl_Type;
665 l_terr_qual_table jtf_territory_get_pub.Terr_Qual_Tbl_Type;
666 l_terr_values_table jtf_territory_get_pub.Terr_Values_Tbl_Type;
667 l_terr_rsc_table jtf_territory_get_pub.Terr_Rsc_Tbl_Type;
668
669 -------------------------------------------------------------------------
670 -- Cursor for HR Manager query
671 -------------------------------------------------------------------------
672 CURSOR c_query_hr_manager(b_owner_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
673 IS SELECT manager_person_id
674 FROM JTF_RS_EMP_DTLS_VL
675 WHERE resource_id = b_owner_id;
676
677 -------------------------------------------------------------------------
678 -- Cursor for Resources query
679 -------------------------------------------------------------------------
680 CURSOR c_query_resource(b_source_id JTF_RS_EMP_DTLS_VL.SOURCE_ID%TYPE)
681 IS SELECT resource_id
682 FROM JTF_RS_EMP_DTLS_VL
683 WHERE source_id = b_source_id;
684
685 -------------------------------------------------------------------------
686 -- Cursor for Resource Group query
687 -------------------------------------------------------------------------
688 CURSOR c_query_group(b_group_id JTF_RS_GROUP_MBR_ROLE_VL.GROUP_ID%TYPE,
689 b_now DATE)
690 IS SELECT role.resource_id
691 FROM JTF_RS_GROUP_MBR_ROLE_VL role,
692 JTF_RS_GROUP_MEMBERS_VL mem
693 WHERE role.group_id = b_group_id
694 AND mem.group_id = b_group_id
695 AND role.group_member_id = mem.group_member_id
696 AND role.manager_flag = 'Y'
697 AND mem.category = 'EMPLOYEE'
698 AND start_date_active <= b_now
699 AND nvl(end_date_active, b_now) >= b_now;
700
701 l_now DATE := SYSDATE;
702 l_index BINARY_INTEGER;
703 l_esc_territory JTF_TASKS_VL.OWNER_TERRITORY_ID%TYPE;
704 l_person_id JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE;
705 l_resultout VARCHAR2(80);
706 l_source_id JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE;
707 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
708
709 BEGIN
710 x_resultout := FND_API.G_Ret_Sts_Success;
711 x_res_type := jtf_ec_pub.g_escalation_owner_type_code;
712
713 debug(fnd_log.level_statement, l_api_name,'input doc role'||p_document_role||' input id type'||p_id_type);
714
715 IF (p_document_role = 'UNASSIGNED')
716 THEN
717 -----------------------------------------------------------------------
718 -- No assignee required
719 -----------------------------------------------------------------------
720 l_person_id := NULL;
721 ELSIF (p_document_role = 'BUSINESS_OWNER')
722 THEN
723 -----------------------------------------------------------------------
724 -- Get the Business Rule owner
725 -----------------------------------------------------------------------
726 l_person_id := Get_RuleOwner( p_id_type => p_id_type
727 , x_resultout => l_resultout
728 );
729 IF (l_resultout <> FND_API.G_Ret_Sts_Success)
730 THEN
731 debug(fnd_log.level_error, l_api_name, 'Retrieving Business Rule Owner Failed');
732 RAISE FND_API.G_EXC_ERROR;
733 END IF;
734 ELSIF ( (p_document_role = 'ESC_TERRITORY_PRIMARY')
735 OR ( (g_owner_id Is Null) AND g_owner_group_id IS NULL)
736 )
737 THEN
738 -----------------------------------------------------------------------
739 -- Get the escalation primary contact
740 -- Also if the document has no owner
741 -----------------------------------------------------------------------
742 l_person_id := Get_EscTerrContact( p_id_type => p_id_type
743 , x_res_type => x_res_type
744 , x_resultout => l_resultout
745 );
746 IF (l_resultout <> FND_API.G_Ret_Sts_Success)
747 THEN
748 debug(fnd_log.level_error, l_api_name, 'Retrieving Escalation Primary Contact Failed for esc_terr_primary');
749 RAISE FND_API.G_EXC_ERROR;
750 END IF;
751 ELSIF (p_document_role = 'DOCUMENT_OWNER')
752 THEN
753 -----------------------------------------------------------------------
754 -- If the resource type isn't Employee or Group then use the Escalation
755 -- Territory primary contact, as Escalation owner must be an employee
756 -----------------------------------------------------------------------
757 If g_res_type_code not in ('RS_EMPLOYEE', 'RS_GROUP') Then
758 l_person_id := Get_EscTerrContact( p_id_type => p_id_type
759 , x_res_type => x_res_type
760 , x_resultout => l_resultout
761 );
762 IF (l_resultout <> FND_API.G_Ret_Sts_Success)
763 THEN
764 debug(fnd_log.level_error, l_api_name, 'Retrieving Escalation Primary Contact Failed for document_owner');
765 RAISE FND_API.G_EXC_ERROR;
766 END IF;
767 Elsif g_res_type_code = 'RS_EMPLOYEE' Then
768 ---------------------------------------------------------------------
769 -- Get the document owner
770 ---------------------------------------------------------------------
771 l_person_id := g_owner_id;
772 IF (p_id_type = 'EMPLOYEE')
773 THEN
774 l_source_id := Get_EmployeeID( p_resource_id => l_person_id
775 , x_resultout => l_resultout
776 );
777 IF (l_resultout = FND_API.G_Ret_Sts_Success)
778 THEN
779 l_person_id := l_source_id;
780 ELSE
781 debug(fnd_log.level_error, l_api_name ,'Retrieving DocumentOwner for Employee Resource failed for:'||l_person_id);
782 RAISE FND_API.G_EXC_ERROR;
783 END IF;
784 END IF;
785 Else
786 ---------------------------------------------------------------------
787 -- It's a Group Resource, so work out if any of the members are
788 -- Managers and are of type Employee (use the first one returned)
789 ---------------------------------------------------------------------
790 OPEN c_query_group(g_owner_id,
791 l_now);
792 FETCH c_query_group INTO l_resource_id;
793
794 IF (c_query_group%FOUND)
795 THEN
796 CLOSE c_query_group;
797 debug(fnd_log.level_statement, l_api_name, 'group res for:'||g_owner_id||' is:'||l_resource_id);
798 If p_id_type = 'EMPLOYEE' Then
799 l_source_id := Get_EmployeeID( p_resource_id => l_resource_id
800 , x_resultout => l_resultout
801 );
802 IF (l_resultout = FND_API.G_Ret_Sts_Success) Then
803 debug(fnd_log.level_statement, l_api_name, 'employee id found:'||l_source_id);
804 l_person_id := l_source_id;
805 ELSE
806 debug(fnd_log.level_error, l_api_name, 'Retrieving Employee ID for Manager failed');
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809 Else
810 l_person_id := l_resource_id;
811 End If;
812
813
814 ELSIF (c_query_group%NOTFOUND)
815 THEN
816 -------------------------------------------------------------------
817 -- If there is no suitable manager then use the Escalation
818 -- Territory primary contact instead
819 -------------------------------------------------------------------
820 CLOSE c_query_group;
821 l_person_id := Get_EscTerrContact( p_id_type => p_id_type
822 , x_res_type => x_res_type
823 , x_resultout => l_resultout
824 );
825 IF (l_resultout <> FND_API.G_Ret_Sts_Success)
826 THEN
827 debug(fnd_log.level_error, l_api_name, 'Escaltion Primary Contact failed for group resource');
828 --
829 -- simply assign it to the group
830 --
831 x_res_type := g_owner_group_type;
832 l_person_id := g_owner_group_id;
833
834 END IF;
835 END IF;
836 END IF;
837 ELSIF (p_document_role = 'DOCUMENT_OWNER_MGR')
838 THEN
839 -----------------------------------------------------------------------
840 -- If the resource type isn't Employee or Group then use the Escalation
841 -- Territory primary contact, as Escalation owner must be an employee
842 -----------------------------------------------------------------------
843 If g_res_type_code not in ('RS_EMPLOYEE', 'RS_GROUP') Then
844 l_person_id := Get_EscTerrContact( p_id_type => p_id_type
845 , x_res_type => x_res_type
846 , x_resultout => l_resultout
847 );
848 IF (l_resultout <> FND_API.G_Ret_Sts_Success)
849 THEN
850 debug(fnd_log.level_error, l_api_name, 'Escaltion Primary Contact failed for DOCUMENT_OWNER_MGR');
851 RAISE FND_API.G_EXC_ERROR;
852 END IF;
853 Elsif g_res_type_code = 'RS_EMPLOYEE' Then
854 ---------------------------------------------------------------------
855 -- Query Resources view to get HR Manager
856 ---------------------------------------------------------------------
857 OPEN c_query_hr_manager(g_owner_id);
858 FETCH c_query_hr_manager INTO l_person_id;
859 IF (c_query_hr_manager%notfound)
860 THEN
861 -------------------------------------------------------------------
862 -- Query HR Manager didn't find a record
863 -------------------------------------------------------------------
864 CLOSE c_query_hr_manager;
865 debug(fnd_log.level_error, l_api_name, 'Query for HR manager failed for owner'||g_owner_id);
866 RAISE FND_API.G_EXC_ERROR;
867 END IF;
868 CLOSE c_query_hr_manager;
869 IF (p_id_type = 'RESOURCE')
870 THEN
871 -------------------------------------------------------------------
872 -- Query Resources view to get HR Manager resource_id
873 -------------------------------------------------------------------
874 OPEN c_query_resource(l_person_id);
875 FETCH c_query_resource INTO l_resource_id;
876 IF (c_query_resource%NOTFOUND)
877 THEN
878 CLOSE c_query_resource;
879 -----------------------------------------------------------------
880 -- If the manager isn't a resource then use the Escalation
881 -- Territory primary contact instead
882 -----------------------------------------------------------------
883 l_person_id := Get_EscTerrContact( p_id_type => p_id_type
884 , x_res_type => x_res_type
885 , x_resultout => l_resultout
886 );
887 IF (l_resultout <> FND_API.G_Ret_Sts_Success)
888 THEN
889 debug(fnd_log.level_error, l_api_name, 'Escaltion Primary Contact failed for id type'||p_id_type);
890 RAISE FND_API.G_EXC_ERROR;
891 END IF;
892 ELSE
893 l_person_id := l_resource_id;
894 CLOSE c_query_resource;
895 END IF;
896 END IF;
897 Else
898 ---------------------------------------------------------------------
899 -- It's a Group Resource, so work out if any of the members are
900 -- Managers and are of type Employee (use the first one returned)
901 ---------------------------------------------------------------------
902 OPEN c_query_group(g_owner_id,
903 l_now);
904 FETCH c_query_group INTO l_resource_id;
905 IF (c_query_group%NOTFOUND) Then
906 -------------------------------------------------------------------
907 -- If there is no suitable manager then use the Escalation
908 -- Territory primary contact instead
909 -------------------------------------------------------------------
910 CLOSE c_query_group;
911 l_person_id := Get_EscTerrContact( p_id_type => p_id_type
912 , x_res_type => x_res_type
913 , x_resultout => l_resultout
914 );
915 IF (l_resultout <> FND_API.G_Ret_Sts_Success)
916 THEN
917 debug(fnd_log.level_error, l_api_name, 'Escaltion Primary Contact failed for group resource');
918 RAISE FND_API.G_EXC_ERROR;
919 END IF;
920 Else
921 debug(fnd_log.level_statement, l_api_name, 'Employee ID found is:'||l_resource_id);
922 l_person_id := l_resource_id;
923 CLOSE c_query_group;
924 End If;
925 End If;
926 ELSE
927 debug(fnd_log.level_error, l_api_name, 'Unhandled Document Owner Role');
928 -----------------------------------------------------------------------
929 -- Unhandled value for p_document_role
930 -----------------------------------------------------------------------
931
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
934 debug(fnd_log.level_statement, l_api_name, 'Returning the person id: '||l_person_id);
935 RETURN(l_person_id);
936
937 EXCEPTION
938 WHEN FND_API.G_EXC_ERROR
939 THEN
940 x_resultout := FND_API.G_Ret_Sts_Error;
941 RETURN(NULL);
942 WHEN OTHERS
943 THEN
944 debug( fnd_log.level_unexpected, l_api_name, 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM);
945 x_resultout := FND_API.G_Ret_Sts_Unexp_Error;
946 RETURN(NULL);
947
948 END Get_PersonID;
949
950 ----------------------------------------------------------------------------
951 -- Start of comments
952 -- Procedure : Set_Globals
953 -- Description : Set values for global package variables. This procedure
954 -- is private to this package.
955 -- Parameters :
956 -- name direction type required?
957 -- ---- --------- ---- ---------
958 -- itemtype IN VARCHAR2 required
959 -- itemkey IN VARCHAR2 required
960 -- actid IN NUMBER required
961 -- funcmode IN VARCHAR2 required
962 -- resultout OUT VARCHAR2
963 --
964 -- Notes :
965 -- Expects WF item attributes 'OBJECT_ID' and 'OBJECT_TYPE' to be available
966 -- to this procedure.
967 -- Possible values for 'OBJECT_TYPE' are:
968 -- 'CS_BRM_3D_SERVICE_REQUEST_V' - Service Request
969 -- 'CSS_BRM_3D_DEFECT_V' - Defect
970 -- 'JTF_BRM_3D_TASK_V' - Task
971 --
972 -- End of comments
973 ----------------------------------------------------------------------------
974 PROCEDURE Set_Globals
975 ( itemtype IN VARCHAR2
976 , itemkey IN VARCHAR2
977 , actid IN NUMBER
978 , funcmode IN VARCHAR2
979 , resultout OUT NOCOPY VARCHAR2
980 )
981 IS
982 -------------------------------------------------------------------------
983 -- Standard API out parameters
984 -------------------------------------------------------------------------
985 l_return_status VARCHAR2(1);
986 l_msg_count NUMBER;
987 l_msg_data VARCHAR2(2000);
988
989 -------------------------------------------------------------------------
990 -- Cursor for AK_Objects query
991 -------------------------------------------------------------------------
992 CURSOR c_query_ak(b_object_type AK_OBJECTS_VL.DATABASE_OBJECT_NAME%TYPE)
993 IS SELECT description
994 FROM AK_OBJECTS_VL
995 WHERE DATABASE_OBJECT_NAME = b_object_type;
996
997 -------------------------------------------------------------------------
998 -- Cursor for BRM_Rules query
999 -------------------------------------------------------------------------
1000 CURSOR c_query_rule(b_rule_id JTF_BRM_RULES_VL.RULE_ID%TYPE)
1001 IS SELECT rule_name
1002 , rule_description
1003 , rule_owner
1004 FROM JTF_BRM_RULES_VL
1005 WHERE rule_id = b_rule_id;
1006
1007 -------------------------------------------------------------------------
1008 -- Cursor for JTF_Objects query
1009 -------------------------------------------------------------------------
1010 CURSOR c_obj_details(b_obj_code VARCHAR2)
1011 IS SELECT select_id
1012 , select_name
1013 , from_table
1014 FROM JTF_OBJECTS_VL
1015 WHERE object_code = b_obj_code;
1016
1017 -------------------------------------------------------------------------
1018 -- Task table definition (returned from query_task API)
1019 -------------------------------------------------------------------------
1020 l_task_table jtf_tasks_pub.task_table_type;
1021 l_sort_data jtf_tasks_pub.sort_data;
1022 l_start_pointer NUMBER;
1023 l_rec_wanted NUMBER;
1024 l_retrieved NUMBER;
1025 l_returned NUMBER;
1026 l_object_version_number JTF_TASKS_VL.OBJECT_VERSION_NUMBER%TYPE;
1027
1028 TYPE cur_type IS REF CURSOR;
1029 c_object cur_type;
1030
1031 l_query VARCHAR2(1000);
1032 l_select_id JTF_OBJECTS_VL.SELECT_ID%TYPE;
1033 l_select_name JTF_OBJECTS_VL.SELECT_NAME%TYPE;
1034 l_from_table JTF_OBJECTS_VL.FROM_TABLE%TYPE;
1035
1036 BEGIN
1037 resultout := FND_API.G_Ret_Sts_Success;
1038 -------------------------------------------------------------------------
1039 -- Get item attribute values
1040 -------------------------------------------------------------------------
1041 g_object_id := WF_ENGINE.GetItemAttrNumber( itemtype => itemtype
1042 , itemkey => itemkey
1043 , aname => 'OBJECT_ID'
1044 );
1045 g_object_type := WF_ENGINE.GetItemAttrText( itemtype => itemtype
1046 , itemkey => itemkey
1047 , aname => 'OBJECT_TYPE'
1048 );
1049 g_rule_id := WF_ENGINE.GetItemAttrNumber( itemtype => itemtype
1050 , itemkey => itemkey
1051 , aname => 'RULE_ID'
1052 );
1053
1054 debug( fnd_log.level_statement
1055 , 'Set_Globals'
1056 , 'Objectid is: '||g_object_id||' Object type: '||g_object_type||' Rule Id:'||g_rule_id
1057 );
1058 -------------------------------------------------------------------------
1059 -- Get object owner, territory, name and number
1060 -------------------------------------------------------------------------
1061
1062 /* This Code is commented as CSS product is scraped.
1063 -- Ref Bug 5025448
1064 --
1065 If (g_object_type = 'CSS_BRM_3D_DEFECT_V')
1066 THEN
1067 -----------------------------------------------------------------------
1068 -- It's a Defect object
1069 -- Select straight from the view - using dynamic SQL so there is no
1070 -- build dependency on Defect code
1071 -----------------------------------------------------------------------
1072 l_query := 'SELECT phase_owner_id,' ||
1073 ' territory_id,' ||
1074 ' phase_owner_resource_type' ||
1075 ' FROM CSS_DEF_DEFECTS_B' ||
1076 ' WHERE defect_id = :b_object_id';
1077 OPEN c_object FOR l_query using g_object_id;
1078 FETCH c_object INTO g_owner_id,
1079 g_territory,
1080 g_res_type_code;
1081
1082 IF (c_object%NOTFOUND)
1083 THEN
1084 ---------------------------------------------------------------------
1085 -- Query of Defect details didn't find a record
1086 ---------------------------------------------------------------------
1087 debug( fnd_log.level_error,'Set_Globals', 'Defect Details Not Found Using:'||g_object_id);
1088 CLOSE c_object;
1089 RAISE FND_API.G_EXC_ERROR;
1090 END IF;
1091 CLOSE c_object;
1092
1093 g_object_type_code := 'DF';
1094 ELS
1095 */
1096 IF (g_object_type = 'CS_BRM_3D_SERVICE_REQUEST_V')
1097 THEN
1098 -----------------------------------------------------------------------
1099 -- It's a Service Request object
1100 -- There's no Query API, so select straight from the view - using
1101 -- dynamic SQL so there is no build dependency on Service Request code
1102 -----------------------------------------------------------------------
1103 l_query := 'SELECT incident_owner_id,' ||
1104 ' territory_id,' ||
1105 ' resource_type,' ||
1106 ' owner_group_id,' ||
1107 ' group_type, ' ||
1108 --Added by MPADHIAR for Bug#5068840
1109 ' CUSTOMER_ID,' ||
1110 ' ACCOUNT_ID, ' ||
1111 ' install_site_id ' ||
1112 --Added by MPADHIAR for Bug#5068840 Ends here
1113 ' FROM CS_INCIDENTS_ALL_VL' ||
1114 ' WHERE incident_id = :b_object_id';
1115 OPEN c_object FOR l_query using g_object_id;
1116 FETCH c_object INTO g_owner_id,
1117 g_territory,
1118 g_res_type_code,
1119 g_owner_group_id,
1120 g_owner_group_type,
1121 --Added by MPADHIAR for Bug#5068840
1122 g_customer_id,
1123 g_cust_account_id,
1124 g_address_id;
1125 --Added by MPADHIAR for Bug#5068840 Ends here
1126 IF (c_object%NOTFOUND)
1127 THEN
1128 ---------------------------------------------------------------------
1129 -- Query of Service Request details didn't find a record
1130 ---------------------------------------------------------------------
1131 debug( fnd_log.level_error
1132 , 'Set_Globals'
1133 , 'Service Request Details Not Found Using:'||g_object_id);
1134
1135 CLOSE c_object;
1136 RAISE FND_API.G_EXC_ERROR;
1137 END IF;
1138 CLOSE c_object;
1139 g_object_type_code := 'SR';
1140
1141 ELSIF (g_object_type = 'JTF_BRM_3D_TASK_V')
1142 THEN
1143 -----------------------------------------------------------------------
1144 -- It's a Task object
1145 -----------------------------------------------------------------------
1146 JTF_TASKS_PUB.Query_Task( p_api_version => 1.0
1147 , p_init_msg_list => FND_API.G_TRUE
1148 , p_task_id => g_object_id
1149 , p_sort_data => l_sort_data
1150 , p_start_pointer => l_start_pointer
1151 , p_rec_wanted => l_rec_wanted
1152 , p_show_all => 'Y'
1153 , x_task_table => l_task_table
1154 , x_total_retrieved => l_retrieved
1155 , x_total_returned => l_returned
1156 , x_return_status => l_return_status
1157 , x_msg_count => l_msg_count
1158 , x_msg_data => l_msg_data
1159 , x_object_version_number => l_object_version_number
1160 );
1161 IF ( (l_return_status = FND_API.G_Ret_Sts_Success)
1162 AND (l_returned > 0)
1163 )
1164 THEN
1165 debug( fnd_log.level_statement,'Set_Globals','Task Details Found');
1166 g_owner_id := l_task_table(1).owner_id;
1167 g_territory := l_task_table(1).owner_territory_id;
1168 g_res_type_code := l_task_table(1).owner_type_code;
1169 --Added by MPADHIAR for Bug#5068840
1170 g_customer_id := l_task_table(1).customer_id;
1171 g_cust_account_id := l_task_table(1).cust_account_id;
1172 g_address_id := l_task_table(1).address_id;
1173 --Added by MPADHIAR for Bug#5068840 Ends here
1174 g_object_type_code := 'TASK';
1175 ELSE
1176 ---------------------------------------------------------------------
1177 -- Query API returned an error
1178 ---------------------------------------------------------------------
1179 debug(fnd_log.level_error,'Set_Globals','Task Details Not Found Using:'||g_object_id);
1180 RAISE FND_API.G_EXC_ERROR;
1181 END IF;
1182 ELSE
1183 -----------------------------------------------------------------------
1184 -- It's an invalid object type
1185 -----------------------------------------------------------------------
1186 debug(fnd_log.level_statement,'Set_Globals','Invalid Object Type'||g_object_type);
1187 RAISE FND_API.G_EXC_ERROR;
1188 END IF;
1189 -------------------------------------------------------------------------
1190 -- Get the object name from AK
1191 -------------------------------------------------------------------------
1192 OPEN c_query_ak(g_object_type);
1193 FETCH c_query_ak INTO g_object_name;
1194 IF (c_query_ak%NOTFOUND)
1195 THEN
1196 -----------------------------------------------------------------------
1197 -- Query AK_OBJECTS didn't find a record
1198 -----------------------------------------------------------------------
1199 debug(fnd_log.level_error,'Set_Globals','Object Name from AK not found for:'||g_object_type);
1200 CLOSE c_query_ak;
1201 RAISE FND_API.G_EXC_ERROR;
1202 END IF;
1203 CLOSE c_query_ak;
1204 -------------------------------------------------------------------------
1205 -- Get the rule name, description and owner
1206 -------------------------------------------------------------------------
1207 OPEN c_query_rule(g_rule_id);
1208 FETCH c_query_rule INTO g_rule_name
1209 , g_rule_desc
1210 , g_rule_owner;
1211 IF (c_query_rule%NOTFOUND)
1212 THEN
1213 -----------------------------------------------------------------------
1214 -- Query rule didn't find a record
1215 -----------------------------------------------------------------------
1216 debug(fnd_log.level_error,'Set_Globals','Rule details not found for rule ID:'||g_rule_id);
1217 CLOSE c_query_rule;
1218 RAISE FND_API.G_EXC_ERROR;
1219 END IF;
1220 CLOSE c_query_rule;
1221
1222 -------------------------------------------------------------------------
1223 -- Get the detected date
1224 -------------------------------------------------------------------------
1225 g_detected_date := SYSDATE;
1226
1227 -- -----------------------------------------------------------------------
1228 -- Get the object name according to jtf_objects, for input to Task APIs
1229 -- -----------------------------------------------------------------------
1230 OPEN c_obj_details(g_object_type_code);
1231 FETCH c_obj_details INTO l_select_id
1232 , l_select_name
1233 , l_from_table;
1234 IF (c_obj_details%NOTFOUND)
1235 THEN
1236 -----------------------------------------------------------------------
1237 -- Query jtf_objects didn't find a record
1238 -----------------------------------------------------------------------
1239 debug(fnd_log.level_error,'Set_Globals', 'Details from JTF_OBJECTS not found for:'||g_object_type_code);
1240 CLOSE c_obj_details;
1241 RAISE FND_API.G_EXC_ERROR;
1242 END IF;
1243 CLOSE c_obj_details;
1244
1245 l_query := 'SELECT ' || l_select_name ||
1246 ' FROM ' || l_from_table ||
1247 ' WHERE ' || l_select_id ||
1248 ' = :b_input_id';
1249
1250 OPEN c_object FOR l_query using g_object_id;
1251 FETCH c_object INTO g_jtf_object_name;
1252 IF (c_object%NOTFOUND)
1253 THEN
1254 -----------------------------------------------------------------------
1255 -- Query of object details didn't find a record
1256 -----------------------------------------------------------------------
1257 debug(fnd_log.level_error,'Set_Globals','Object Details not found for object name:'||g_jtf_object_name);
1258 CLOSE c_object;
1259 RAISE FND_API.G_EXC_ERROR;
1260 END IF;
1261 CLOSE c_object;
1262
1263 debug( fnd_log.level_statement
1264 , 'Set_Globals'
1265 , 'g_object_name: ' || g_object_name
1266 || ' g_jtf_object_name: ' || g_jtf_object_name
1267 || ' g_object_type_code:' || g_object_type_code
1268 || ' g_rule_name:' || g_rule_name
1269 || ' g_rule_desc:' || g_rule_desc
1270 || ' g_detected_date' || g_detected_date
1271 );
1272
1273 -------------------------------------------------------------------------
1274 -- Set item attribute values
1275 -------------------------------------------------------------------------
1276 WF_ENGINE.SetItemAttrText( itemtype => itemtype
1277 , itemkey => itemkey
1278 , aname => 'OBJECT_NAME'
1279 , avalue => g_object_name
1280 );
1281
1282 WF_ENGINE.SetItemAttrText( itemtype => itemtype
1283 , itemkey => itemkey
1284 , aname => 'OBJECT_NUMBER'
1285 , avalue => g_jtf_object_name
1286 );
1287
1288 WF_ENGINE.SetItemAttrText( itemtype => itemtype
1289 , itemkey => itemkey
1290 , aname => 'RULE_NAME'
1291 , avalue => g_rule_name
1292 );
1293
1294 WF_ENGINE.SetItemAttrText( itemtype => itemtype
1295 , itemkey => itemkey
1296 , aname => 'RULE_DESC'
1297 , avalue => g_rule_desc
1298 );
1299
1300 WF_ENGINE.SetItemAttrDate( itemtype => itemtype
1301 , itemkey => itemkey
1302 , aname => 'DETECTED_DATE'
1303 , avalue => g_detected_date
1304 );
1305
1306 EXCEPTION
1307 WHEN FND_API.G_EXC_ERROR
1308 THEN
1309 resultout := FND_API.G_Ret_Sts_Error;
1310
1311 WHEN OTHERS
1312 THEN
1313 debug( fnd_log.level_unexpected, 'Set_Globals'
1314 , 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM
1315 );
1316
1317 resultout := FND_API.G_Ret_Sts_Unexp_Error;
1318
1319 END Set_Globals;
1320
1321 ----------------------------------------------------------------------------
1322 -- Start of comments
1323 -- Procedure : Create_NotifTask
1324 -- Description : Call Task Manager API to create task for potential
1325 -- escalation notification purposes
1326 -- Parameters :
1327 -- name direction type required?
1328 -- ---- --------- ---- ---------
1329 -- itemtype IN VARCHAR2 required
1330 -- itemkey IN VARCHAR2 required
1331 -- actid IN NUMBER required
1332 -- funcmode IN VARCHAR2 required
1333 -- resultout OUT VARCHAR2
1334 --
1335 -- Notes :
1336 --
1337 -- End of comments
1338 ----------------------------------------------------------------------------
1339 PROCEDURE Create_NotifTask
1340 ( itemtype IN VARCHAR2
1341 , itemkey IN VARCHAR2
1342 , actid IN NUMBER
1343 , funcmode IN VARCHAR2
1344 , resultout OUT NOCOPY VARCHAR2
1345 )
1346 IS
1347 -------------------------------------------------------------------------
1348 -- Standard API out parameters
1349 -------------------------------------------------------------------------
1350 l_return_status VARCHAR2(1);
1351 l_msg_count NUMBER;
1352 l_msg_data VARCHAR2(2000);
1353 l_api_name CONSTANT VARCHAR2(30) := 'Create_NotifTask';
1354
1355 -------------------------------------------------------------------------
1356 -- Task table definition (returned from create_task_from_template API)
1357 -------------------------------------------------------------------------
1358 l_task_details_table jtf_tasks_pub.task_details_tbl;
1359
1360 -------------------------------------------------------------------------
1361 -- Task table definition (returned from query_task API)
1362 -------------------------------------------------------------------------
1363 l_task_table jtf_tasks_pub.task_table_type;
1364 l_sort_data jtf_tasks_pub.sort_data;
1365 l_start_pointer NUMBER;
1366 l_rec_wanted NUMBER;
1367 l_retrieved NUMBER;
1368 l_returned NUMBER;
1369 l_template_name JTF_TASK_TEMPLATES_VL.TASK_NAME%TYPE;
1370 l_notif_task_id JTF_TASKS_VL.TASK_ID%TYPE;
1371 l_task_assign_id NUMBER;
1372 l_obj_version JTF_TASKS_VL.OBJECT_VERSION_NUMBER%TYPE;
1373 l_object_name JTF_OBJECTS_VL.NAME%TYPE;
1374 l_task_reference_id JTF_TASK_REFERENCES_VL.TASK_REFERENCE_ID%TYPE;
1375 l_assign_id NUMBER;
1376 l_assign_role VARCHAR2(80);
1377 l_owner_id NUMBER;
1378 l_owner_role VARCHAR2(80);
1379 l_resource_type JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE;
1380 l_status_id VARCHAR2(80);
1381 l_resultout VARCHAR2(80);
1382
1383 -------------------------------------------------------------------------
1384 -- Cursor to pick Task_template_id from Templeate name
1385 -- Added for bug 3611893 by ABRAINA
1386 -------------------------------------------------------------------------
1387 cursor c_Get_Template_id (p_template_name in varchar2 ) is
1388 select TASK_TEMPLATE_GROUP_ID
1389 from JTF_TASK_TEMP_GROUPS_TL
1390 where TEMPLATE_GROUP_NAME = p_template_name ;
1391
1392 l_template_id JTF_TASK_TEMP_GROUPS_TL.TASK_TEMPLATE_GROUP_ID%TYPE;
1393
1394 BEGIN
1395
1396 Set_Globals( itemtype => itemtype
1397 , itemkey => itemkey
1398 , actid => actid
1399 , funcmode => funcmode
1400 , resultout => resultout
1401 );
1402 If (resultout = FND_API.G_Ret_Sts_Success)
1403 THEN
1404 IF (funcmode = 'RUN')
1405 THEN
1406 ---------------------------------------------------------------------
1407 -- 'RUN' function from WF
1408 ---------------------------------------------------------------------
1409
1410 -- -------------------------------------------------------------------
1411 -- Get appropriate task template name from the Workflow attribute
1412 -- -------------------------------------------------------------------
1413 IF (g_object_type_code = 'TASK')
1414 THEN
1415 l_template_name := Get_WorkflowAttribute
1416 ( p_attr_name => 'JTF_ESC_TASK_TEMPLATE_TASK'
1417 , p_return_type => 'VALUE'
1418 );
1419 ELSIF (g_object_type_code = 'DF')
1420 THEN
1421 l_template_name := Get_WorkflowAttribute
1422 ( p_attr_name => 'CSS_ESC_TASK_TEMPLATE_DF'
1423 , p_return_type => 'VALUE'
1424 );
1425 ELSIF (g_object_type_code = 'SR')
1426 THEN
1427 l_template_name := Get_WorkflowAttribute
1428 ( p_attr_name => 'CS_ESC_TASK_TEMPLATE_SR'
1429 , p_return_type => 'VALUE'
1430 );
1431 END IF;
1432 debug(fnd_log.level_statement,l_api_name, 'template name is: '||l_template_name);
1433 IF (l_template_name IS NOT NULL)
1434 THEN
1435 -------------------------------------------------------------------
1436 -- Get task owner's role from the Workflow attribute
1437 -------------------------------------------------------------------
1438 IF (g_object_type_code = 'TASK')
1439 THEN
1440 l_owner_role := Get_WorkflowAttribute
1441 ( p_attr_name => 'JTF_ESC_NOTIF_TASK_OWNER_ROLE'
1442 , p_return_type => 'CODE'
1443 );
1444 ELSIF (g_object_type_code = 'DF')
1445 THEN
1446 l_owner_role := Get_WorkflowAttribute
1447 ( p_attr_name => 'CSS_ESC_NOTIF_TASK_OWNER_ROLE'
1448 , p_return_type => 'CODE'
1449 );
1450 ELSIF (g_object_type_code = 'SR')
1451 THEN
1452 l_owner_role := Get_WorkflowAttribute
1453 ( p_attr_name => 'CS_ESC_NOTIF_TASK_OWNER_ROLE'
1454 , p_return_type => 'CODE'
1455 );
1456 END IF;
1457 debug(fnd_log.level_statement, l_api_name,'task owner role is: '||l_owner_role);
1458 -------------------------------------------------------------------
1459 -- Get ID of person who is to own the task
1460 -------------------------------------------------------------------
1461 l_owner_id := Get_PersonID( p_document_role => l_owner_role
1462 , p_id_type => 'RESOURCE'
1463 , x_res_type => l_resource_type
1464 , x_resultout => l_resultout
1465 );
1466 IF (l_resultout = FND_API.G_Ret_Sts_Success)
1467 THEN
1468
1469 if c_Get_Template_id%ISOPEN
1470 then
1471 close c_Get_Template_id;
1472 end if;
1473
1474 open c_Get_Template_id (l_template_name);
1475 fetch c_Get_Template_id into l_template_id;
1476 close c_Get_Template_id ;
1477
1478 if c_Get_Template_id%ISOPEN
1479 then
1480 close c_Get_Template_id;
1481 end if;
1482
1483 debug(fnd_log.level_statement, l_api_name,'template id: '||l_template_id);
1484
1485 -----------------------------------------------------------------
1486 -- Create the notification task
1487 -----------------------------------------------------------------
1488 JTF_TASKS_PUB.Create_Task_From_Template
1489 ( p_api_version => 1.0
1490 , p_init_msg_list => FND_API.G_True
1491 , p_task_template_group_id => l_template_id
1492 , p_task_template_group_name => l_template_name
1493 , p_owner_type_code => jtf_ec_pub.g_escalation_owner_type_code
1494 , p_owner_id => l_owner_id
1495 , p_source_object_id => g_object_id
1496 , p_source_object_name => g_jtf_object_name
1497 --Added by MPADHIAR for Bug#5068840
1498 , p_customer_id => g_customer_id
1499 , p_cust_account_id => g_cust_account_id
1500 , p_address_id => g_address_id
1501 --Added by MPADHIAR for Bug#5068840 Ends here
1502 , x_return_status => l_return_status
1503 , x_msg_count => l_msg_count
1504 , x_msg_data => l_msg_data
1505 , x_task_details_tbl => l_task_details_table
1506 );
1507 debug(fnd_log.level_statement, l_api_name,'Notification Task creation was : '||l_return_status);
1508 IF (l_return_status = FND_API.G_Ret_Sts_Success)
1509 THEN
1510 l_notif_task_id := l_task_details_table(1).task_id;
1511 ---------------------------------------------------------------
1512 -- Need to query back the task to get the object version number
1513 ---------------------------------------------------------------
1514 JTF_TASKS_PUB.Query_Task( p_api_version => 1.0
1515 , p_init_msg_list => FND_API.G_TRUE
1516 , p_task_id => l_notif_task_id
1517 , p_sort_data => l_sort_data
1518 , p_start_pointer => l_start_pointer
1519 , p_rec_wanted => l_rec_wanted
1520 , p_show_all => 'Y'
1521 , x_task_table => l_task_table
1522 , x_total_retrieved => l_retrieved
1523 , x_total_returned => l_returned
1524 , x_return_status => l_return_status
1525 , x_msg_count => l_msg_count
1526 , x_msg_data => l_msg_data
1527 , x_object_version_number => l_obj_version
1528 );
1529 l_obj_version := l_task_table(1).object_version_number;
1530
1531 IF (l_return_status = FND_API.G_Ret_Sts_Success)
1532 THEN
1533 -------------------------------------------------------------
1534 -- Update the notification task with name and description
1535 -------------------------------------------------------------
1536 debug(fnd_log.level_statement, l_api_name,'Notification Task created with task id:'|| l_notif_task_id);
1537 JTF_TASKS_PUB.Update_Task( p_api_version => 1.0
1538 , p_init_msg_list => FND_API.G_True
1539 , p_object_version_number => l_obj_version
1540 , p_task_id => l_notif_task_id
1541 , p_task_name => g_rule_name
1542 , p_description => g_rule_desc
1543 , x_return_status => l_return_status
1544 , x_msg_count => l_msg_count
1545 , x_msg_data => l_msg_data
1546 );
1547 debug(fnd_log.level_statement, l_api_name,'Updation of the notification task completes with : '||l_return_status);
1548
1549 IF (l_return_status = FND_API.G_Ret_Sts_Success)
1550 THEN
1551 -----------------------------------------------------------
1552 -- Task created successfully
1553 -- Create reference to source document
1554 -----------------------------------------------------------
1555 JTF_TASK_REFERENCES_PUB.Create_References
1556 ( p_api_version => 1.0
1557 , p_init_msg_list => FND_API.G_True
1558 , p_task_id => l_notif_task_id
1559 , p_object_type_code => g_object_type_code
1560 , p_object_name => g_jtf_object_name
1561 , p_object_id => g_object_id
1562 , p_reference_code => 'FYI'
1563 , x_return_status => l_return_status
1564 , x_msg_count => l_msg_count
1565 , x_msg_data => l_msg_data
1566 , x_task_reference_id => l_task_reference_id
1567 );
1568 debug(fnd_log.level_statement, l_api_name, 'Reference creation completes with : '||l_return_status);
1569 IF (l_return_status = FND_API.G_Ret_Sts_Success)
1570 THEN
1571 ---------------------------------------------------------
1572 -- Get task assignee's role from the Workflow attribute
1573 ---------------------------------------------------------
1574 IF (g_object_type_code = 'TASK')
1575 THEN
1576 l_assign_role := Get_WorkflowAttribute
1577 ( p_attr_name => 'JTF_ESC_NOTIF_TASK_ASSIGN_ROLE'
1578 , p_return_type => 'CODE'
1579 );
1580 ELSIF (g_object_type_code = 'DF')
1581 THEN
1582 l_assign_role := Get_WorkflowAttribute
1583 ( p_attr_name => 'CSS_ESC_NOTIF_TASK_ASSIGN_ROLE'
1584 , p_return_type => 'CODE'
1585 );
1586 ELSIF (g_object_type_code = 'SR')
1587 THEN
1588 l_assign_role := Get_WorkflowAttribute
1589 ( p_attr_name => 'CS_ESC_NOTIF_TASK_ASSIGN_ROLE'
1590 , p_return_type => 'CODE'
1591 );
1592 END IF;
1593 debug(fnd_log.level_statement, l_api_name, 'Assignee Role to whom task will be assigned : '||l_assign_role);
1594 ---------------------------------------------------------
1595 -- Get ID of person who is to be assigned the task
1596 ---------------------------------------------------------
1597 l_assign_id := Get_PersonID
1598 ( p_document_role => l_assign_role
1599 , p_id_type => 'RESOURCE'
1600 , x_res_type => l_resource_type
1601 , x_resultout => l_resultout
1602 );
1603 IF (l_resultout = FND_API.G_Ret_Sts_Success)
1604 THEN
1605 IF (l_assign_id IS NOT NULL)
1606 THEN
1607 -----------------------------------------------------
1608 -- Assign the notification task
1609 -----------------------------------------------------
1610 l_status_id := FND_PROFILE.Value
1611 ( name => 'JTF_TASK_DEFAULT_TASK_STATUS');
1612
1613 JTF_TASK_ASSIGNMENTS_PUB.Create_Task_Assignment
1614 ( p_api_version => 1.0
1615 , p_init_msg_list => FND_API.G_True
1616 , p_task_id => l_notif_task_id
1617 , p_resource_type_code => l_resource_type
1618 , p_resource_id => l_assign_id
1619 , p_assignment_status_id => l_status_id
1620 , x_return_status => l_return_status
1621 , x_msg_count => l_msg_count
1622 , x_msg_data => l_msg_data
1623 , x_task_assignment_id => l_task_assign_id
1624 );
1625 debug(fnd_log.level_statement, l_api_name, 'Assignment creation completes with : '||l_return_status);
1626 IF (l_return_status <> FND_API.G_Ret_Sts_Success)
1627 THEN
1628 ---------------------------------------------------
1629 -- Create_Task_Assignment API failed
1630 ---------------------------------------------------
1631 IF (l_return_status = FND_API.G_Ret_Sts_Error)
1632 THEN
1633 RAISE FND_API.G_EXC_ERROR;
1634 ELSE
1635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1636 END IF;
1637 END IF;
1638 END IF;
1639 ELSE
1640 debug(fnd_log.level_statement, l_api_name, 'Getting Person ID failed');
1641 -------------------------------------------------------
1642 -- Get_PersonID (assign_id) failed
1643 -------------------------------------------------------
1644 IF (l_resultout = FND_API.G_Ret_Sts_Error)
1645 THEN
1646 RAISE FND_API.G_EXC_ERROR;
1647 ELSE
1648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1649 END IF;
1650 END IF;
1651 ELSE
1652 ---------------------------------------------------------
1653 -- Create_References API failed
1654 ---------------------------------------------------------
1655 debug(fnd_log.level_statement, l_api_name,'Creating Reference failed');
1656 IF (l_return_status = FND_API.G_Ret_Sts_Error)
1657 THEN
1658 RAISE FND_API.G_EXC_ERROR;
1659 ELSE
1660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1661 END IF;
1662 END IF;
1663 ELSE
1664 -----------------------------------------------------------
1665 -- Update_Task API failed
1666 -----------------------------------------------------------
1667 debug(fnd_log.level_statement, l_api_name,'Update Task failed');
1668 IF (l_return_status = FND_API.G_Ret_Sts_Error)
1669 THEN
1670 RAISE FND_API.G_EXC_ERROR;
1671 ELSE
1672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1673 END IF;
1674 END IF;
1675 ELSE
1676 -------------------------------------------------------------
1677 -- Query_Task API failed
1678 -------------------------------------------------------------
1679 debug(fnd_log.level_statement, l_api_name,'Query Task failed');
1680 IF (l_return_status = FND_API.G_Ret_Sts_Error)
1681 THEN
1682 RAISE FND_API.G_EXC_ERROR;
1683 ELSE
1684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1685 END IF;
1686 END IF;
1687 ELSE
1688 debug(fnd_log.level_error, l_api_name,'Creating Task From Template failed');
1689 ---------------------------------------------------------------
1690 -- Create_Task_From_Template API failed
1691 ---------------------------------------------------------------
1692 IF (l_return_status = FND_API.G_Ret_Sts_Error)
1693 THEN
1694 RAISE FND_API.G_EXC_ERROR;
1695 ELSE
1696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697 END IF;
1698 END IF;
1699 ELSE
1700 -----------------------------------------------------------------
1701 -- Get_PersonID (owner_id) failed
1702 -----------------------------------------------------------------
1703 debug(fnd_log.level_error, l_api_name,'Getting Person ID failed');
1704 IF (l_resultout = FND_API.G_Ret_Sts_Error)
1705 THEN
1706 RAISE FND_API.G_EXC_ERROR;
1707 ELSE
1708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1709 END IF;
1710 END IF;
1711 ELSE
1712 -------------------------------------------------------------------
1713 -- Invalid profile value for JTF_ESC_TASK_TEMPLATE_NAME
1714 -------------------------------------------------------------------
1715 debug(fnd_log.level_error, l_api_name,'invalid profile value for JTF_ESC_TASK_TEMPLATE_NAME');
1716 RAISE FND_API.G_EXC_ERROR;
1717 END IF;
1718 ---------------------------------------------------------------------
1719 -- If we get here then it's all been successful - return 'COMPLETE'
1720 ---------------------------------------------------------------------
1721 resultout := 'COMPLETE:' || g_success;
1722 ELSIF (funcmode = 'CANCEL')
1723 THEN
1724 ---------------------------------------------------------------------
1725 -- 'CANCEL' function from WF
1726 ---------------------------------------------------------------------
1727 resultout := 'COMPLETE:' || g_success;
1728 ELSIF (funcmode = 'TIMEOUT')
1729 THEN
1730 ---------------------------------------------------------------------
1731 -- 'TIMEOUT' function from WF
1732 ---------------------------------------------------------------------
1733 resultout := '';
1734 ELSE
1735 ---------------------------------------------------------------------
1736 -- Unknown function from WF - raise error
1737 ---------------------------------------------------------------------
1738 debug(fnd_log.level_error, l_api_name,'unknown function from Workflow');
1739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1740 END IF;
1741 ELSE
1742 -----------------------------------------------------------------------
1743 -- Set_Globals failed
1744 -----------------------------------------------------------------------
1745 debug(fnd_log.level_error, l_api_name,'set_globals failed with error:'||resultout);
1746 IF (resultout = FND_API.G_Ret_Sts_Error)
1747 THEN
1748 RAISE FND_API.G_EXC_ERROR;
1749 ELSE
1750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1751 END IF;
1752 END IF;
1753 debug(fnd_log.level_statement, l_api_name, 'Result Out is '||resultout);
1754
1755 EXCEPTION
1756 WHEN FND_API.G_EXC_ERROR
1757 THEN
1758 resultout := 'COMPLETE:' || g_noncritical;
1759 debug(fnd_log.level_error, l_api_name, ' The error:' || Get_Messages_On_Stack());
1760 WF_CORE.Context( pkg_name => g_pkg_name
1761 , proc_name => 'Create_NotifTask'
1762 , arg1 => itemtype
1763 , arg2 => itemkey
1764 , arg3 => to_char(actid)
1765 , arg4 => funcmode
1766 );
1767 RAISE;
1768 WHEN OTHERS
1769 THEN
1770 resultout := 'COMPLETE:' || g_critical;
1771 debug(fnd_log.level_unexpected, l_api_name, ' The error:' || Get_Messages_On_Stack());
1772 WF_CORE.Context( pkg_name => g_pkg_name
1773 , proc_name => 'Create_NotifTask'
1774 , arg1 => itemtype
1775 , arg2 => itemkey
1776 , arg3 => to_char(actid)
1777 , arg4 => funcmode
1778 );
1779 RAISE;
1780
1781 END Create_NotifTask;
1782
1783 ----------------------------------------------------------------------------
1784 -- Start of comments
1785 -- Procedure : Create_EscTask
1786 -- Description : Call Task Manager API to create escalation task
1787 -- Parameters :
1788 -- name direction type required?
1789 -- ---- --------- ---- ---------
1790 -- itemtype IN VARCHAR2 required
1791 -- itemkey IN VARCHAR2 required
1792 -- actid IN NUMBER required
1793 -- funcmode IN VARCHAR2 required
1794 -- resultout OUT VARCHAR2
1795 --
1796 -- Notes :
1797 --
1798 -- End of comments
1799 ----------------------------------------------------------------------------
1800 PROCEDURE Create_EscTask
1801 ( itemtype IN VARCHAR2
1802 , itemkey IN VARCHAR2
1803 , actid IN NUMBER
1804 , funcmode IN VARCHAR2
1805 , resultout OUT NOCOPY VARCHAR2
1806 )
1807 IS
1808 -------------------------------------------------------------------------
1809 -- Standard API out parameters
1810 -------------------------------------------------------------------------
1811 l_return_status VARCHAR2(1);
1812 l_msg_count NUMBER;
1813 l_msg_data VARCHAR2(2000);
1814 l_api_name CONSTANT VARCHAR2(30) := 'Create_EscTask';
1815
1816 -------------------------------------------------------------------------
1817 -- Record and table type descriptions for Escalation API
1818 -------------------------------------------------------------------------
1819 l_esc_rec jtf_ec_pub.esc_rec_type;
1820 l_esc_ref_docs jtf_ec_pub.esc_ref_docs_tbl_type;
1821 l_esc_contacts jtf_ec_pub.esc_contacts_tbl_type;
1822 l_esc_cont_phones jtf_ec_pub.esc_cont_points_tbl_type;
1823 l_esc_task_id JTF_TASKS_VL.TASK_ID%TYPE;
1824 l_esc_task_number JTF_TASKS_VL.TASK_NUMBER%TYPE;
1825 l_wf_process_id NUMBER;
1826 l_owner_role VARCHAR2(80);
1827 l_owner_id JTF_TASKS_VL.OWNER_ID%TYPE;
1828 l_wf_role FND_USER.USER_NAME%TYPE;
1829 l_resource_type JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE;
1830 l_object_name JTF_OBJECTS_VL.NAME%TYPE;
1831 l_task_reference_id JTF_TASK_REFERENCES_VL.TASK_REFERENCE_ID%TYPE;
1832 l_requester_id NUMBER;
1833 l_esc_status VARCHAR2(80);
1834 l_esc_level VARCHAR2(80);
1835 l_resultout VARCHAR2(80);
1836 l_msg FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1837 l_msg_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1838 l_short_name VARCHAR2(80);
1839 l_user_id number := to_number(fnd_profile.value('USER_ID'));
1840
1841 BEGIN
1842
1843 Set_Globals( itemtype => itemtype
1844 , itemkey => itemkey
1845 , actid => actid
1846 , funcmode => funcmode
1847 , resultout => resultout
1848 );
1849
1850 IF (resultout = FND_API.G_Ret_Sts_Success)
1851 THEN
1852 IF (funcmode = 'RUN')
1853 THEN
1854 ---------------------------------------------------------------------
1855 -- 'RUN' function from WF
1856 ---------------------------------------------------------------------
1857 ---------------------------------------------------------------------
1858 -- Get esc owner's role from the Workflow attribute. Get esc level
1859 -- and default status from the profile options
1860 ---------------------------------------------------------------------
1861 debug( fnd_log.level_statement
1862 , l_api_name
1863 , 'funcmode = RUN'
1864 ||'itemtype '||itemtype
1865 ||'itemkey'||itemkey
1866 ||'actid'||actid
1867 );
1868 IF (g_object_type_code = 'TASK')
1869 THEN
1870 l_owner_role := Get_WorkflowAttribute
1871 ( p_attr_name => 'JTF_ESC_DOCUMENT_OWNER_ROLE'
1872 , p_return_type => 'CODE'
1873 );
1874 ELSIF (g_object_type_code = 'DF')
1875 THEN
1876 l_owner_role := Get_WorkflowAttribute
1877 ( p_attr_name => 'CSS_ESC_DOCUMENT_OWNER_ROLE'
1878 , p_return_type => 'CODE'
1879 );
1880 ELSIF (g_object_type_code = 'SR')
1881 THEN
1882 l_owner_role := Get_WorkflowAttribute
1883 ( p_attr_name => 'CS_ESC_DOCUMENT_OWNER_ROLE'
1884 , p_return_type => 'CODE'
1885 );
1886 END IF;
1887
1888 debug(fnd_log.level_statement, l_api_name,'Owner role is: '||l_owner_role);
1889
1890 l_esc_status := FND_PROFILE.Value
1891 ( name => 'JTF_EC_DEFAULT_STATUS'
1892 );
1893 l_esc_level := FND_PROFILE.Value
1894 ( name => 'JTF_EC_DEFAULT_ESCALATION_LEVEL'
1895 );
1896
1897 debug(fnd_log.level_statement, l_api_name, 'Escalation status is: '||l_esc_status||' Level is: '||l_esc_level);
1898
1899 ---------------------------------------------------------------------
1900 -- Get ID of person who is to own the task
1901 ---------------------------------------------------------------------
1902 l_owner_id := Get_PersonID( p_document_role => l_owner_role
1903 , p_id_type => 'RESOURCE'
1904 , x_res_type => l_resource_type
1905 , x_resultout => l_resultout
1906 );
1907
1908 debug( fnd_log.level_statement
1909 , l_api_name
1910 , 'Result Out is:'||l_resultout
1911 ||'Owner Id is: '||l_owner_id
1912 ||'Res type'||l_resource_type
1913 );
1914
1915 IF (l_resultout = FND_API.G_Ret_Sts_Success)
1916 THEN
1917 l_esc_rec.esc_owner_id := l_owner_id;
1918 l_esc_rec.esc_owner_type_code := NVL(l_resource_type,FND_API.G_MISS_CHAR);
1919 l_esc_rec.esc_name := g_rule_name;
1920 l_esc_rec.esc_description := g_rule_desc;
1921 l_esc_rec.status_id := l_esc_status;
1922 l_esc_rec.escalation_level := l_esc_level;
1923 --Added by MPADHIAR for Bug#5068840
1924 l_esc_rec.customer_id := g_customer_id ;
1925 l_esc_rec.cust_account_id := g_cust_account_id;
1926 l_esc_rec.cust_address_id := g_address_id;
1927 --Added by MPADHIAR for Bug#5068840 Ends here
1928 l_esc_rec.reason_code := 'AUTOMATED';
1929 l_esc_rec.esc_open_date := SYSDATE;
1930 l_esc_ref_docs(1).action_code := 'I';
1931 l_esc_ref_docs(1).object_type_code := g_object_type_code;
1932 l_esc_ref_docs(1).object_name := g_jtf_object_name;
1933 l_esc_ref_docs(1).object_id := g_object_id;
1934 l_esc_ref_docs(1).reference_code := 'ESC';
1935 l_esc_contacts(1).action_code := 'I';
1936 l_esc_contacts(1).contact_id := g_rule_owner;
1937 l_esc_contacts(1).contact_type_code := 'EMP';
1938 l_esc_contacts(1).escalation_requester_flag := 'Y';
1939
1940 JTF_EC_PUB.Create_Escalation
1941 ( p_api_version => 1.0
1942 , p_init_msg_list => FND_API.G_True
1943 , x_return_status => l_return_status
1944 , x_msg_count => l_msg_count
1945 , x_msg_data => l_msg_data
1946 , p_user_id => l_user_id
1947 , p_esc_record => l_esc_rec
1948 , p_reference_documents => l_esc_ref_docs
1949 , p_esc_contacts => l_esc_contacts
1950 , p_cont_points => l_esc_cont_phones
1951 , x_esc_id => l_esc_task_id
1952 , x_esc_number => l_esc_task_number
1953 , x_workflow_process_id => l_wf_process_id
1954 );
1955 IF (l_return_status <> FND_API.G_Ret_Sts_Success)
1956 THEN
1957 l_msg_count := fnd_msg_pub.count_msg;
1958 IF l_msg_count > 0
1959 THEN
1960 l_msg := ' ' || substr(fnd_msg_pub.get( fnd_msg_pub.G_FIRST
1961 , fnd_api.G_FALSE
1962 ),1, 512);
1963 END IF;
1964
1965 FOR iIndex IN 1..(l_msg_count-1) LOOP
1966 l_msg := l_msg || ' ' || substr(fnd_msg_pub.get(fnd_msg_pub.G_NEXT
1967 ,fnd_api.G_FALSE
1968 ), 1, 512);
1969
1970 END LOOP;
1971
1972 -----------------------------------------------------------------
1973 -- Create_Escalation API failed
1974 -----------------------------------------------------------------
1975 IF (l_return_status = FND_API.G_Ret_Sts_Error)
1976 THEN
1977 debug(fnd_log.level_statement, l_api_name, 'Escalation API failed because:'||l_msg||l_msg_count);
1978 ---------------------------------------------------------------
1979 -- No need to report the 'document already escalated' error
1980 ---------------------------------------------------------------
1981 IF (l_msg_name <> 'JTF_TK_ESC_DOC_EXIST')
1982 THEN
1983 RAISE FND_API.G_EXC_ERROR;
1984 END IF;
1985 ELSE
1986 debug(fnd_log.level_statement, l_api_name, 'Escalation API failed with unexpected error'||l_msg||l_msg_count);
1987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1988 END IF;
1989 END IF;
1990 ELSE
1991 -------------------------------------------------------------------
1992 -- Get_PersonID failed
1993 -------------------------------------------------------------------
1994 debug(fnd_log.level_statement, l_api_name, 'Get_PersonID failed');
1995 IF (l_resultout = FND_API.G_Ret_Sts_Error)
1996 THEN
1997 RAISE FND_API.G_EXC_ERROR;
1998 ELSE
1999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2000 END IF;
2001 END IF;
2002 ---------------------------------------------------------------------
2003 -- If we get here then it's all been successful - return 'COMPLETE'
2004 ---------------------------------------------------------------------
2005 resultout := 'COMPLETE:' || g_success;
2006
2007 ELSIF (funcmode = 'CANCEL')
2008 THEN
2009 ---------------------------------------------------------------------
2010 -- 'CANCEL' function from WF
2011 ---------------------------------------------------------------------
2012 resultout := 'COMPLETE:' || g_success;
2013
2014 ELSIF (funcmode = 'TIMEOUT')
2015 THEN
2016 ---------------------------------------------------------------------
2017 -- 'TIMEOUT' function from WF
2018 ---------------------------------------------------------------------
2019 resultout := '';
2020
2021 ELSE
2022 ---------------------------------------------------------------------
2023 -- Unknown function from WF - raise error
2024 ---------------------------------------------------------------------
2025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2026 END IF;
2027 ELSE
2028 -----------------------------------------------------------------------
2029 -- Set_Globals failed
2030 -----------------------------------------------------------------------
2031 debug(fnd_log.level_statement, l_api_name, 'Set_Globals is not successful: '||resultout);
2032 IF (resultout = FND_API.G_Ret_Sts_Error)
2033 THEN
2034 RAISE FND_API.G_EXC_ERROR;
2035 ELSE
2036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2037 END IF;
2038 END IF;
2039 debug(fnd_log.level_statement, l_api_name, 'Return status of the API:'||resultout);
2040 EXCEPTION
2041 WHEN FND_API.G_EXC_ERROR
2042 THEN
2043 resultout := 'COMPLETE:' || g_noncritical;
2044
2045 debug(fnd_log.level_error, l_api_name, 'API returns with Expected Error');
2046
2047 WF_CORE.Context( pkg_name => g_pkg_name
2048 , proc_name => 'Create_EscTask'
2049 , arg1 => itemtype
2050 , arg2 => itemkey
2051 , arg3 => to_char(actid)
2052 , arg4 => funcmode
2053 );
2054 RAISE;
2055 WHEN OTHERS
2056 THEN
2057 resultout := 'COMPLETE:' || g_critical;
2058
2059 debug(fnd_log.level_unexpected, l_api_name, 'API returns with unexpected error');
2060
2061 WF_CORE.Context( pkg_name => g_pkg_name
2062 , proc_name => 'Create_EscTask'
2063 , arg1 => itemtype
2064 , arg2 => itemkey
2065 , arg3 => to_char(actid)
2066 , arg4 => funcmode
2067 );
2068 RAISE;
2069
2070 END Create_EscTask;
2071
2072 ----------------------------------------------------------------------------
2073 -- Start of comments
2074 -- Procedure : Get_NotifPerson
2075 -- Description : Work out who is to be notified regarding the escalation
2076 -- Parameters :
2077 -- name direction type required?
2078 -- ---- --------- ---- ---------
2079 -- itemtype IN VARCHAR2 required
2080 -- itemkey IN VARCHAR2 required
2081 -- actid IN NUMBER required
2082 -- funcmode IN VARCHAR2 required
2083 -- resultout OUT VARCHAR2
2084 --
2085 -- Notes :
2086 -- Expects WF item attribute 'NTF_PERSON' to be available to this
2087 -- procedure.
2088 --
2089 -- End of comments
2090 ----------------------------------------------------------------------------
2091 PROCEDURE Get_NotifPerson
2092 ( itemtype IN VARCHAR2
2093 , itemkey IN VARCHAR2
2094 , actid IN NUMBER
2095 , funcmode IN VARCHAR2
2096 , resultout OUT NOCOPY VARCHAR2
2097 )
2098 IS
2099 l_notif_role VARCHAR2(80);
2100 l_wf_role FND_USER.USER_NAME%TYPE;
2101 l_resource_type JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE;
2102 l_resultout VARCHAR2(80);
2103 l_api_name CONSTANT VARCHAR2(30) := 'Get_NotifPerson';
2104
2105 BEGIN
2106 Set_Globals( itemtype => itemtype
2107 , itemkey => itemkey
2108 , actid => actid
2109 , funcmode => funcmode
2110 , resultout => resultout
2111 );
2112 IF (resultout = FND_API.G_Ret_Sts_Success)
2113 THEN
2114 IF (funcmode = 'RUN')
2115 THEN
2116 ---------------------------------------------------------------------
2117 -- 'RUN' function from WF
2118 ---------------------------------------------------------------------
2119
2120 ---------------------------------------------------------------------
2121 -- Get notification role from the Workflow attribute
2122 ---------------------------------------------------------------------
2123 IF (g_object_type_code = 'TASK')
2124 THEN
2125 l_notif_role := Get_WorkflowAttribute
2126 ( p_attr_name => 'JTF_ESC_NOTIF_ROLE'
2127 , p_return_type => 'CODE'
2128 );
2129 ELSIF (g_object_type_code = 'DF')
2130 THEN
2131 l_notif_role := Get_WorkflowAttribute
2132 ( p_attr_name => 'CSS_ESC_NOTIF_ROLE'
2133 , p_return_type => 'CODE'
2134 );
2135 ELSIF (g_object_type_code = 'SR')
2136 THEN
2137 l_notif_role := Get_WorkflowAttribute
2138 ( p_attr_name => 'CS_ESC_NOTIF_ROLE'
2139 , p_return_type => 'CODE'
2140 );
2141 END IF;
2142 debug( fnd_log.level_statement, l_api_name, 'Notification role is:'||l_notif_role);
2143 ---------------------------------------------------------------------
2144 -- Get ID of person who is to be notified
2145 ---------------------------------------------------------------------
2146 g_notif_person_id := Get_PersonID
2147 ( p_document_role => l_notif_role
2148 , p_id_type => 'EMPLOYEE'
2149 , x_res_type => l_resource_type
2150 , x_resultout => l_resultout
2151 );
2152 IF (l_resultout = FND_API.G_Ret_Sts_Success)
2153 THEN
2154 -------------------------------------------------------------------
2155 -- Work out WF internal name for the employee
2156 -------------------------------------------------------------------
2157 debug(fnd_log.level_statement, l_api_name, 'Finding workflow role of:'||g_notif_person_id);
2158 l_wf_role := Get_EmployeeRole
2159 ( p_employee_id => g_notif_person_id
2160 , x_resultout => l_resultout
2161 );
2162 debug(fnd_log.level_statement, l_api_name, 'Finding Workflow role completed with '||l_resultout);
2163 IF (l_resultout = FND_API.G_Ret_Sts_Success)
2164 THEN
2165 -----------------------------------------------------------------
2166 -- Set the WF attribute
2167 -----------------------------------------------------------------
2168 debug(fnd_log.level_statement, l_api_name, 'Setting the NTF_PERSON with:'||l_wf_role);
2169 WF_ENGINE.SetItemAttrText( itemtype => itemtype
2170 , itemkey => itemkey
2171 , aname => 'NTF_PERSON'
2172 , avalue => l_wf_role
2173 );
2174 -----------------------------------------------------------------
2175 -- If we get here then it's all been successful - return 'COMPLETE'
2176 -----------------------------------------------------------------
2177 resultout := 'COMPLETE:' || g_success;
2178 ELSE
2179 -----------------------------------------------------------------
2180 -- Get_EmployeeRole failed
2181 -----------------------------------------------------------------
2182 IF (l_resultout = FND_API.G_Ret_Sts_Error)
2183 THEN
2184 debug(fnd_log.level_error, l_api_name, 'Getting Employee Role Failed');
2185 RAISE FND_API.G_EXC_ERROR;
2186 ELSE
2187 debug(fnd_log.level_unexpected, l_api_name, 'Getting Employee Role Failed with unexpected error');
2188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2189 END IF;
2190 END IF;
2191 ELSE
2192 -------------------------------------------------------------------
2193 -- Get_PersonId failed
2194 -------------------------------------------------------------------
2195 debug(fnd_log.level_statement, l_api_name, 'Getting Person ID failed with:'||l_resultout);
2196 IF (l_resultout = FND_API.G_Ret_Sts_Error)
2197 THEN
2198 RAISE FND_API.G_EXC_ERROR;
2199 ELSE
2200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2201 END IF;
2202 END IF;
2203 ELSIF (funcmode = 'CANCEL')
2204 THEN
2205 ---------------------------------------------------------------------
2206 -- 'CANCEL' function from WF
2207 ---------------------------------------------------------------------
2208 resultout := 'COMPLETE:' || g_success;
2209 ELSIF (funcmode = 'TIMEOUT')
2210 THEN
2211 ---------------------------------------------------------------------
2212 -- 'TIMEOUT' function from WF
2213 ---------------------------------------------------------------------
2214 resultout := '';
2215 ELSE
2216 ---------------------------------------------------------------------
2217 -- Unknown function from WF - raise error
2218 ---------------------------------------------------------------------
2219 debug(fnd_log.level_unexpected, l_api_name, 'Unknown WF action requested');
2220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2221 END IF;
2222 ELSE
2223 -----------------------------------------------------------------------
2224 -- Set_Globals failed
2225 -----------------------------------------------------------------------
2226 debug(fnd_log.level_unexpected, l_api_name, 'Setting Globals failed with:'||l_resultout);
2227 IF (resultout = FND_API.G_Ret_Sts_Error)
2228 THEN
2229 RAISE FND_API.G_EXC_ERROR;
2230 ELSE
2231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2232 END IF;
2233 END IF;
2234 debug(fnd_log.level_statement, l_api_name, 'Return status of the API:'||resultout);
2235
2236 EXCEPTION
2237 WHEN FND_API.G_EXC_ERROR
2238 THEN
2239 resultout := 'COMPLETE:' || g_noncritical;
2240 debug( fnd_log.level_error
2241 , l_api_name
2242 , 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM
2243 );
2244 WF_CORE.Context( pkg_name => g_pkg_name
2245 , proc_name => 'Get_NotifPerson'
2246 , arg1 => itemtype
2247 , arg2 => itemkey
2248 , arg3 => to_char(actid)
2249 , arg4 => funcmode
2250 );
2251 RAISE;
2252 WHEN OTHERS
2253 THEN
2254 resultout := 'COMPLETE:' || g_critical;
2255 debug( fnd_log.level_unexpected
2256 , l_api_name
2257 , 'Error occured: Code:'|| SQLCODE || 'Error:'|| SQLERRM
2258 );
2259 WF_CORE.Context( pkg_name => g_pkg_name
2260 , proc_name => 'Get_NotifPerson'
2261 , arg1 => itemtype
2262 , arg2 => itemkey
2263 , arg3 => to_char(actid)
2264 , arg4 => funcmode
2265 );
2266 RAISE;
2267
2268 END Get_NotifPerson;
2269
2270 END JTF_EscWFActivity_PVT;