1 PACKAGE BODY IEM_ROUTE_PUB AS
2 /* $Header: iemproub.pls 120.2 2006/05/22 20:10:32 pkesani noship $ */
3 --
4 --
5 -- Purpose: Mantain route classification related operations
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- Liang Xia 01/10/2002 fix bug that if the number of rules for a classification is less than
10 -- that of classification with higher priority, this classification
11 -- is never satisfied. ( No bug logged, shipped with FP-M )
12 -- Liang Xia 06/05/2002 Added Dynamic Route function
13 -- Liang Xia 08/06/2002 Changed jtf_rs_roles_vl to jtf_rs_roles_b, jtf_rs_group_members_vl to jtf_rs_group_members for
14 -- performance issue.
15 -- Liang Xia 11/12/2002 Added All_email tag, which overwrite the rules
16 -- Fixed GSCC warning: NOCOPY, FND_API.G_MISS...
17 -- Liang Xia 11/26/2002 Not to route groups that are expired.
18 -- Liang Xia 09/24/2003 Not to route groups that don't have 'Call Center' usage
19 -- Liang Xia 08/24/2004 Tar 4021452.999/bug 3855036.
20 -- Do not Auto-Route to agent if the agent has been auto-routed, but requeued the message
21 -- Do not route to group which doesn't have at least one agent who never requeue this message
22 -- Liang Xia 04/06/2005 Fixed GSCC sql.46 ( bug 4256769 )
23 -- Liang Xia 06/23/2005 Ported fixed: 12/06/2004 Changed for 115.11 schema complaince
24 -- Fixed more GSCC.sql46 warning ( bug 4452895 ).
25 -- PKESANI 05/20/2006 For Bug 5195496, change the SQL to look for responsibility_key
26 -- instead of responsibility_id.
27 -- --------- ------ ------------------------------------------
28
29
30 /*GLOBAL VARIABLES FOR PRIVATE USE
31 ==================================*/
32 G_PKG_NAME VARCHAR2(100):= 'IEM_ROUTE_PUB';
33
34 --The record type for passing rules
35 TYPE Rules_rec_type is RECORD (
36 key iem_route_rules.key_type_code%type,
37 operator iem_route_rules.operator_type_code%type,
38 value iem_route_rules.value%type);
39
40 --The table type for passing rules
41 TYPE Rules_tbl_type IS table of Rules_rec_type INDEX BY BINARY_INTEGER;
42
43
44
45 /* PRIVATE PROCEDURES NOT AVAILABLE TO THE PUBLIC
46 ================================================*/
47
48 /* Evaluate And conditions */
49 PROCEDURE evaluateAnd(keyVals IN keyVals_tbl_type, rules IN Rules_tbl_type, fireRoute OUT NOCOPY Boolean)
50 IS
51
52 x number := 1;
53 y number := 1;
54
55 keyToFind iem_route_rules.key_type_code%type;
56 operator iem_route_rules.operator_type_code%type;
57 valueToFind iem_route_rules.value%type;
58
59 foundKey Boolean;
60 foundKeyValue Boolean;
61
62 numberOfKeys Number;
63 numberOfRules Number;
64
65 errorMessage varchar2(2000);
66
67 Begin
68
69 numberOfKeys := keyVals.count;
70 numberofRules := rules.count;
71
72 --Evaluate each rule one at a time
73 while x <= numberOfRules loop
74
75 --Copy the rule into temp variables to save repetitive calls to the UDT
76 keyToFind := rules(x).key;
77 valueToFind := rules(x).value;
78 operator := rules(x).operator;
79
80 y := 1;
81 foundKey := false;
82 foundKeyValue := false;
83
84 --Search through all the keys that got passed in
85 while y <= numberOfKeys loop
86
87 --Make the key comparison
88 if keyToFind = keyVals(y).key then
89 foundKey := true;
90 --If the key is found then see if the value matches up based on the operator
91 if iem_operators_pvt.satisfied(keyVals(y).value, operator, valueToFind, keyVals(y).datatype) then
92 foundKeyValue := true;
93 end if;
94 --Exit since we found what we wanted
95 EXIT;
96 end if;
97
98 y := y + 1;
99 end loop;
100
101 --If we were unable to find the key or the value then exit since this is AND chaining
102 if (foundKey = false or foundKeyValue = false) then
103 fireRoute := false;
104 EXIT;
105 else
106 fireRoute := true;
107 end if;
108
109 x := x + 1;
110 end loop;
111
112 EXCEPTION
113 When others then
114
115 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
116 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
117 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_PUB.EVALUATEAND.OTHERS', errorMessage);
118 end if;
119
120
121 End evaluateAnd;
122
123 /* Evaluate OR conditions */
124 PROCEDURE evaluateOr(keyVals IN keyVals_tbl_type, rules IN Rules_tbl_type, fireRoute OUT NOCOPY Boolean)
125 IS
126
127 x number := 1;
128 y number := 1;
129
130 keyToFind iem_route_rules.key_type_code%type;
131 operator iem_route_rules.operator_type_code%type;
132 valueToFind iem_route_rules.value%type;
133
134 foundKeyValue Boolean;
135
136 numberOfKeys Number;
137 numberOfRules Number;
138
139 errorMessage varchar2(2000);
140
141 Begin
142
143 numberOfKeys := keyVals.count;
144 numberofRules := rules.count;
145
146 --Evaluate each rule one at a time
147 while x <= numberOfRules loop
148
149 --Copy the rule into temp variables to save repetitive calls to the UDT
150 keyToFind := rules(x).key;
151 valueToFind := rules(x).value;
152 operator := rules(x).operator;
153
154 y := 1;
155 foundKeyValue := false;
156
157 --Search through all the keys that got passed in
158 while y <= numberOfKeys loop
159
160 --Make the key comparison case insensitive
161 if upper(keyToFind) = upper(keyVals(y).key) then
162 --If the key is found then see if the value matches up based on the operator
163 if iem_operators_pvt.satisfied(keyVals(y).value, operator, valueToFind, keyVals(y).datatype) then
164 foundKeyValue := true;
165 end if;
166 --Exit since we found what we wanted
167 EXIT;
168 end if;
169
170 y := y + 1;
171 end loop;
172
173 --If we found a key value pair then exit since this is OR chaining
174 if foundKeyValue then
175 fireRoute := true;
176 EXIT;
177 else
178 fireRoute := false;
179 end if;
180
181 x := x + 1;
182 end loop;
183
184 EXCEPTION
185 When others then
186
187 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
188 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
189 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_PUB.EVALUATEOR.OTHERS', errorMessage);
190 end if;
191
192 End evaluateOr;
193
194 /*Check if a group is valid
195 1. Has at least one resource in it
196 2. At least one resource is assigned to the email account
197 3. At least one resource has the one of the 3 eMC Roles*
198 4. At least one resource(agent) that has not requeued the message ( added for 11.59
199 and later version. For Tar 4021452.999./bug 3855036 )*/
200 FUNCTION validGroup(
201 groupIdToCheck IN jtf_rs_group_members.group_id%TYPE,
202 emailAccountId IN iem_mstemail_accounts.email_account_id%TYPE,
203 p_message_id IN number)
204 return Boolean is
205
206 groupOK Boolean := TRUE;
207
208 resourceId JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE;
209 roleTypeCode JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE := 'ICENTER';
210 deleteFlag JTF_RS_ROLE_RELATIONS.DELETE_FLAG%TYPE := 'N';
211 groupUsage JTF_RS_GROUP_USAGES.usage%TYPE :='CALL';
212
213 errorMessage varchar2(2000);
214
215 begin
216 select
217 c.resource_id
218 into
219 resourceId
220 from
221 jtf_rs_roles_b a,
222 jtf_rs_role_relations b,
223 jtf_rs_group_members c,
224 --jtf_rs_resource_values d,
225 iem_agents d,
226 JTF_RS_GROUPS_B e,
227 JTF_RS_RESOURCE_EXTNS f,
228 FND_USER_RESP_GROUPS g,
229 FND_USER h,
230 JTF_RS_GROUP_USAGES i,
231 FND_RESPONSIBILITY resp
232 where
233 a.role_type_code = roleTypeCode
234 and
235 a.role_id = b.role_id
236 and
237 b.delete_flag = deleteFlag
238 and
239 b.START_DATE_ACTIVE< sysdate
240 and
241 ( b.END_DATE_ACTIVE>sysdate or b.END_DATE_ACTIVE is null)
242 and
243 b.role_resource_id = c.resource_id
244 and
245 c.group_id = groupIdToCheck
246 and
247 c.delete_flag = deleteFlag
248 and
249 c.resource_id = d.resource_id
250 and
251 --d.value_type = emailAccountId
252 d.email_account_id = emailAccountId
253 and
254 c.group_id = e.group_id
255 and
256 e.START_DATE_ACTIVE< sysdate
257 and
258 ( e.END_DATE_ACTIVE>sysdate or e.END_DATE_ACTIVE is null)
259 and
260 c.resource_id = f.resource_id
261 and
262 f.START_DATE_ACTIVE< sysdate
263 and
264 ( f.END_DATE_ACTIVE>sysdate or f.END_DATE_ACTIVE is null)
265 and
266 f.user_id = g.user_id
267 and
268 g.START_DATE< sysdate
269 and
270 ( g.END_DATE>sysdate or g.END_DATE is null)
271 and
272 -- ( g.responsibility_id = 23720 or g.responsibility_id = 23107 )
273 ( g.responsibility_id = resp.responsibility_id and resp.application_id=680)
274 and
275 ( resp.responsibility_key = 'EMAIL_CENTER_SUPERVISOR' or resp.responsibility_key='IEM_SA_AGENT')
276 and
277 g.user_id = h.user_id
278 and
279 h.START_DATE< sysdate
280 and
281 ( h.END_DATE>sysdate or h.END_DATE is null)
282 and
283 c.group_id = i.group_id
284 and
285 i.usage = groupUsage
286 and
287 d.resource_id not in (select agent_id from iem_reroute_hists where message_id=p_message_id)
288 and
289 rownum = 1;
290
291 return groupOK;
292
293 EXCEPTION
294 When NO_DATA_FOUND then
295 groupOK := false;
296 return groupOK;
297
298 When OTHERS then
299
300 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
301 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
302 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_PUB.VALIDGROUP.OTHERS', errorMessage);
303 end if;
304
305 groupOK := false;
306 return groupOK;
307
308
309 end validGroup;
310
311 /* PUBLIC PROCEDURES THAT CAN BE CALLED BY ALL USERS
312 ===================================================*/
313 PROCEDURE route(
314 p_api_version_number IN Number,
315 p_init_msg_list IN VARCHAR2 := null,
316 p_commit IN VARCHAR2 := null,
317 p_keyVals_tbl IN keyVals_tbl_type,
318 p_accountId IN Number,
319 x_groupId OUT NOCOPY Number,
320 x_return_status OUT NOCOPY VARCHAR2,
321 x_msg_count OUT NOCOPY NUMBER,
322 x_msg_data OUT NOCOPY VARCHAR2)
323 IS
324
325 l_api_version_number CONSTANT NUMBER:= 1.0;
326 l_api_name CONSTANT VARCHAR2(30):= 'Route';
327
328
329 --The active routes in the system
330 cursor c_routes (p_enabled_flag iem_account_routes.enabled_flag%type,
331 p_account_id iem_mstemail_accounts.email_account_id%type)
332 is
333 select
334 a.route_id,
335 a.boolean_type_code,
336 a.procedure_name,
337 a.all_email,
338 b.destination_group_id,
339 b.default_group_id
340 from
341 iem_routes a,
342 iem_account_routes b
343 where
344 a.route_id = b.route_id
345 and
346 b.enabled_flag = p_enabled_flag
347 and
348 b.email_account_id = p_account_id
349 order by b.priority;
350
351 --All the rules for a route
352 cursor c_rules (p_route_id iem_routes.route_id%type)
353 is
354 select
355 key_type_code,
356 operator_type_code,
357 value
358 from
359 iem_route_rules
360 where
361 route_id = p_route_id;
362
363
364 x number:= 1;
365 ruleHold Rules_tbl_type;
366 runTimekeyVals_tbl keyVals_tbl_type;
367 routeSatisfied Boolean := false;
368 enabledFlag varchar(1):= 'Y';
369 booleanTypeCode iem_routes.boolean_type_code%type;
370 procedureName iem_routes.procedure_name%type;
371 returnParamType iem_route_rules.key_type_code%type;
372 runTimeSuccess Boolean := true;
373 l_agent_id VARCHAR2(256);
374 all_email VARCHAR2(1);
375 l_msg_id VARCHAR2(15);
376 l_count NUMBER := 0;
377
378 l_result VARCHAR2(256);
379 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
380 l_msg_count NUMBER := 0;
381 l_msg_data VARCHAR2(2000);
382
383 logMessage varchar2(2000);
384 errorMessage varchar2(2000);
385
386 l_log_enabled BOOLEAN := false;
387 l_exception_log BOOLEAN := false;
388 BEGIN
389
390 --Standard begin of API savepoint
391 SAVEPOINT Route_PUB;
392 -- Standard call to check for call compatibility.
393 IF NOT FND_API.Compatible_API_Call(
394 l_api_version_number,
395 p_api_version_number,
396 l_api_name,
397 G_PKG_NAME)
398 THEN
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END IF;
401 -- Initialize message list if p_init_msg_list is set to TRUE.
402 IF FND_API.to_Boolean( p_init_msg_list ) THEN
403 FND_MSG_PUB.initialize;
404 END IF;
405
406 -- Initialize API return status to SUCCESS
407 x_return_status := FND_API.G_RET_STS_SUCCESS;
408
409 --=================--
410 -- Begining of API --
411 --=================--
412
413 l_log_enabled := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
414
415
416 l_exception_log := FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
417
418
419 if l_log_enabled then
420 logMessage := '[p_account_id=' || to_char(p_accountid) || ']';
421 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.START', logMessage);
422 end if;
423
424 --Initialize group to 0
425 x_groupId := 0;
426
427 --Iniitalize the Rule data structure
428 --ruleHold.delete;
429
430 --Check to see if the passed in PL/SQL table has valid key-vals
431 If p_keyVals_tbl.count > 0 then
432
433 if l_log_enabled then
434 logMessage := '[p_keyValsCount=' || to_char(p_keyVals_tbl.count) || ']';
435 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.KEY_VALS_MORE_THAN_0', logMessage);
436 end if;
437 --Get all the active routes in the system
438 For v_routes in c_routes (enabledFlag, p_accountId) Loop
439
440 --The boolean operator for the rule chaining
441 booleanTypeCode := v_routes.boolean_type_code;
442 procedureName := v_routes.procedure_name;
443
444 all_email := v_routes.all_email;
445
446 if all_email = 'Y' then
447 routeSatisfied := true;
448 else
449 --Iniitalize the Rule data structure
450 ruleHold.delete;
451 x := 1;
452 -- Identify route type
453 IF ( booleanTypeCode = 'DYNAMIC' ) THEN
454 if l_log_enabled then
455 logMessage := '[DYNAMIC procedure_name='||procedureName|| ']';
456 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.DYNAMIC_ROUTE', logMessage);
457 end if;
458
459 runTimekeyVals_tbl.delete;
460 runTimeSuccess := true;
461
462 --Get rules for the dynamic route and load it into the data structure
463 For v_rules in c_rules(v_routes.route_id) Loop
464 returnParamType:= v_rules.key_type_code;
465
466 -- begin PROCEDURE processing
467 BEGIN
468 IEM_ROUTE_RUN_PROC_PVT.run_Procedure(
469 p_api_version_number =>P_Api_Version_Number,
470 p_init_msg_list => FND_API.G_FALSE,
471 p_commit => P_Commit,
472 p_procedure_name => procedureName,
473 p_key_value => p_keyVals_tbl,
474 p_param_type => returnParamType,
475 x_result => l_result,
476 x_return_status => l_return_status,
477 x_msg_count => l_msg_count,
478 x_msg_data => l_msg_data);
479
480 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
481 if l_log_enabled then
482 logMessage := '[ERROR when execute procedure for RouteID: '||v_routes.route_id ||']';
483 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.DYNAMIC_ROUTE', logMessage);
484 end if;
485 runTimeSuccess := false;
486 else
487 -- Insert data in key-value pair table
488 if returnParamType = 'IEMNNUMBER' then
489 runTimekeyVals_tbl(x).key := 'IEMNDYNROUTERETURNVAL';
490 runTimekeyVals_tbl(x).value := l_result;
491 runTimekeyVals_tbl(x).datatype := 'N';
492 elsif returnParamType = 'IEMSVARCHAR2' then
493 runTimekeyVals_tbl(x).key := 'IEMSDYNROUTERETURNVAL';
494 runTimekeyVals_tbl(x).value := l_result;
495 runTimekeyVals_tbl(x).datatype := 'S';
496 end if;
497
498 if l_log_enabled then
499 logMessage := '[DYNAMIC ROUTE RETURNED VALUE =' || l_result || ']';
500 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.DYNAMIC_ROUTE', logMessage);
501 end if;
502 end if;
503 EXCEPTION
504 WHEN OTHERS THEN
505 runTimeSuccess := false;
506 if l_log_enabled then
507 logMessage := '[ERROR (Others) when execute procedure for keyId: '||v_routes.route_id ||'. error:'||sqlerrm||']';
508 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.DYNAMIC_ROUTE', logMessage);
509 end if;
510 END; -- end of PROCEDURE processing
511
512 -- Exit when run procedure failed
513 if runTimeSuccess then
514 if returnParamType = 'IEMNNUMBER' then
515 ruleHold(x).key := 'IEMNDYNROUTERETURNVAL';
516 elsif returnParamType = 'IEMSVARCHAR2' then
517 ruleHold(x).key := 'IEMSDYNROUTERETURNVAL';
518 end if;
519
520 ruleHold(x).operator := v_rules.operator_type_code;
521 ruleHold(x).value := v_rules.value;
522
523 if l_log_enabled then
524 logMessage := '[DYNAMIC ROUTE' || ruleHold(x).key || ruleHold(x).operator || ruleHold(x).value || ']';
525 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.DYNAMIC_ROUTE', logMessage);
526 end if;
527 else
528 exit;
529 end if;
530
531 x := x + 1;
532 End Loop;
533
534 --Depending on the operator call different evaluation functions
535 if runTimeSuccess then
536 evaluateAnd(runTimekeyVals_tbl, ruleHold, routeSatisfied);
537 else
538 routeSatisfied := false;
539 end if;
540
541
542 ELSIF ( booleanTypeCode = 'AND' ) or ( booleanTypeCode = 'OR') THEN
543
544 --Get all the rules for the route and load it into the data structure
545 For v_rules in c_rules(v_routes.route_id) Loop
546 ruleHold(x).key := v_rules.key_type_code;
547 ruleHold(x).operator := v_rules.operator_type_code;
548 ruleHold(x).value := v_rules.value;
549
550 if l_log_enabled then
551 logMessage := '[' || ruleHold(x).key || ruleHold(x).operator || ruleHold(x).value || ']';
552 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.RULES', logMessage);
553 end if;
554
555 x := x + 1;
556 End Loop;
557
558 --Depending on the operator call different evaluation functions
559 if booleanTypeCode = 'AND' then
560 evaluateAnd(p_keyVals_tbl, ruleHold, routeSatisfied);
561 elsif booleanTypeCode = 'OR' then
562 evaluateOr(p_keyVals_tbl, ruleHold, routeSatisfied);
563 end if;
564 END IF;
565 end if; --end of if all_email = 'Y'
566
567 --If the rules got satisfied then check
568 -- 1. if route to Agent originating the email, check if agent ID is valid
569 -- 2. otherwise route to valid group or destination group
570 if routeSatisfied then
571
572 l_msg_id := get_key_value(p_keyVals_tbl=>p_keyVals_tbl,
573 p_key_name=>'IEMNMESSAGEID');
574
575 --Valid if the key-value contains valid agent ID for auto-route to Agent
576 if v_routes.destination_group_id ='-1' then
577 l_agent_id := get_key_value(p_keyVals_tbl=>p_keyVals_tbl,
578 p_key_name=>'IEMNAGENTID');
579
580 if l_log_enabled then
581 logMessage := '[ROUTE to oringinating agent. IEMNAGENTID=' || l_agent_id|| ']';
582 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.RULES', logMessage);
583 end if;
584
585
586 if ( l_agent_id is null or l_agent_id='') then
587 if (v_routes.default_group_id = '0') then
588 x_groupId := 0;
589 elsif validGroup(v_routes.default_group_id, p_accountId, TO_NUMBER(l_msg_id)) then
590 x_groupId := v_routes.default_group_id;
591 else
592 x_groupId := 0;
593 end if;
594 else
595 -- Added on 8/21/2004. For Tar 4021452.999
596 -- Validate agent_id to see if the message has been route to the same agent before. For scenario
597 -- that auto-routed Agent pick up the message and requeues the message and expects it routed to different group
598 select count(*) into l_count from iem_reroute_hists
599 where message_id=l_msg_id and agent_id=l_agent_id;
600
601
602 if( l_count = 0 and IEM_TAGPROCESS_PUB.isValidAgent(l_agent_id,p_accountId)) then
603 x_groupId := -1;
604 elsif (v_routes.default_group_id = '0') then
605 x_groupId := 0;
606 elsif validGroup(v_routes.default_group_id, p_accountId, TO_NUMBER(l_msg_id)) then
607 x_groupId := v_routes.default_group_id;
608 else
609 x_groupId := 0;
610 end if;
611 end if;
612 else
613 if validGroup(v_routes.destination_group_id, p_accountId, TO_NUMBER(l_msg_id)) then
614 x_groupId := v_routes.destination_group_id;
615 elsif (v_routes.default_group_id = '0') then
616 x_groupId := 0;
617 elsif validGroup(v_routes.default_group_id, p_accountId, TO_NUMBER(l_msg_id)) then
618 x_groupId := v_routes.default_group_id;
619 else
620 x_groupId := 0;
621 end if;
622 end if;
623
624 if l_log_enabled then
625 logMessage := '[Route destination = ' || x_groupId|| ']';
626 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.RULES', logMessage);
627 end if;
628 EXIT;
629 end if;
630
631 End Loop;
632
633 End if;
634
635
636 --==========--
637 --End of API--
638 --==========--
639
640 --Standard check of p_commit
641 If FND_API.To_Boolean(p_commit) then
642 COMMIT WORK;
643 End if;
644
645 --Standard call to get message count and if count is 1 then get message info
646 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
647
648 EXCEPTION
649 WHEN FND_API.G_EXC_ERROR THEN
650 Rollback to Route_PUB;
651 x_return_status := FND_API.G_RET_STS_ERROR;
652
653 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
654
655 if l_exception_log then
656 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Execution Error';
657 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.EXEC_ERROR', errorMessage);
658 end if;
659
660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
661 Rollback to Route_PUB;
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
663 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
664 if l_exception_log then
665 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Unexpected Execution Error';
666 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.UNEXP_EXEC_ERROR', errorMessage);
667 end if;
668
669 WHEN OTHERS THEN
670 Rollback to Route_PUB;
671 x_return_status := FND_API.G_RET_STS_ERROR;
672 IF fnd_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
673 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
674 END IF;
675 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
676
677 if l_exception_log then
678 errorMessage := '[' || sqlcode || sqlerrm || ']' || ' Others';
679 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_ROUTE_PUB.ROUTE.OTHERS', errorMessage);
680 end if;
681
682 END route;
683
684 function get_key_value ( p_keyVals_tbl IN keyVals_tbl_type,
685 p_key_name IN VARCHAR2 )
686 return VARCHAR2
687 is
688 x_value VARCHAR(256):=''; --FND_API.G_MISS_CHAR;
689 begin
690 if p_keyVals_tbl.count <> 0 then
691 for i in 1..p_keyVals_tbl.count loop
692 if p_keyVals_tbl(i).key = p_key_name then
693 x_value := p_keyVals_tbl(i).value;
694 exit;
695 end if;
696 end loop;
697 end if;
698
699 return x_value;
700 end;
701
702 END IEM_ROUTE_PUB;
[Home] [Help]