1 PACKAGE BODY FTE_ACS_CACHE_PKG AS
2 /* $Header: FTEACSCB.pls 120.5 2005/09/19 05:27:22 alksharm noship $ */
3
4 -- -------------------------------------------------------------------------- --
5 -- --
6 -- NAME: FTE_ACS_CACHE_PKG --
7 -- TYPE: PACKAGE BODY --
8 -- DESCRIPTION: Contains core procedures for searching the rule in the cache --
9 -- In this package processing is done for a single entity. --
10 -- --
11 -- CHANGE CONTROL LOG --
12 -- --
13 -- -------------------------------------------------------------------------- --
14
15 --
16 -- Used to get rule data from the database for a particular group id
17 --
18 TYPE rules_record_type IS RECORD( rule_id FTE_SEL_RULE_RESTRICTIONS.rule_id%type,
19 attribute_name FTE_SEL_RULE_RESTRICTIONS.attribute_name%type,
20 attribute_value_from FTE_SEL_RULE_RESTRICTIONS.attribute_value_from%type,
21 attribute_value_to FTE_SEL_RULE_RESTRICTIONS.attribute_value_to%type,
22 attribute_value_from_number FTE_SEL_RULE_RESTRICTIONS.attribute_value_from_number%type,
23 attribute_value_to_number FTE_SEL_RULE_RESTRICTIONS.attribute_value_to_number%type,
24 overlap_flag FTE_SEL_RULE_RESTRICTIONS.range_overlap_flag%type);
25
26 TYPE rules_tab_type IS TABLE OF rules_record_type INDEX BY BINARY_INTEGER;
27
28 --
29 --Limit is a) 'L' - Lower limit.
30 -- b) 'U' - Upper limit
31 -- c) 'N' - Null (Added when range is not there)
32
33 TYPE range_match_rule_rec IS RECORD( rule_id FTE_SEL_RULE_RESTRICTIONS.rule_id%type,
34 overlap_flag FTE_SEL_RULE_RESTRICTIONS.range_overlap_flag%type,
35 limit VARCHAR2(1));
36
37 TYPE range_match_tab IS TABLE OF range_match_rule_rec INDEX BY BINARY_INTEGER;
38
39 TYPE range_match_cache IS TABLE OF range_match_tab INDEX BY VARCHAR2(32767);
40
41 --
42 -- To be used for FOB and Regions
43 --
44
45 TYPE exact_match_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
46
47 TYPE exact_match_cache IS TABLE OF exact_match_tab INDEX BY VARCHAR2(32767);
48
49 TYPE priority_tab_type IS TABLE OF NUMBER;
50
51 TYPE rule_match_tab IS TABLE OF NUMBER;
52
53 TYPE group_cache_rec IS RECORD( attribute_tab wsh_util_core.id_tab_type,
54 priority_tab priority_tab_type,
55 weight_uom_code VARCHAR2(30),
56 volume_uom_code VARCHAR2(30));
57
58 TYPE groups_cache_type IS TABLE OF group_cache_rec INDEX BY BINARY_INTEGER;
59
60 TYPE from_to_region_cache_type IS TABLE OF NUMBER INDEX BY VARCHAR2(32767);
61
62 --
63 -- The data structure is used for storing RULE-RESULT association.
64 -- 1 rule can be associated with multiple results.
65 -- The results are indexed by rule_id.
66 --
67 TYPE result_id_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
68
69 TYPE rule_result_tab IS TABLE OF result_id_tab INDEX BY BINARY_INTEGER;
70
71 --
72 -- Local data structure for querying the result attributes.
73 --
74 TYPE fte_attr_code_val_rec_type IS RECORD( attr_code VARCHAR2(30),
75 attr_val VARCHAR2(240));
76
77 TYPE fte_attr_code_val_tab_type IS TABLE OF fte_attr_code_val_rec_type INDEX BY BINARY_INTEGER;
78
79 -- -------------------------------------------------------------------------- --
80 -- Global Package Constants --
81 -- ------------------------ --
82 -- --
83 -- -------------------------------------------------------------------------- --
84
85 --
86 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_ACS_CACHE_PKG';
87 --
88 g_session_id NUMBER;
89 g_num_absent NUMBER := -9999 ;
90 g_val_absent VARCHAR2(25):= 'NULL';
91 g_max_cache_size NUMBER := 10;
92 g_null_tab WSH_UTIL_CORE.ID_TAB_TYPE;
93
94 --
95 -- 2^10 = 2 147 483 648 ,
96 -- Taking 11 characters in the index
97 -- g_int_mask is used for Integer Fields, Include Sign always otherwise negative length will be more
98 -- g__num_mask is used for decimals
99 --
100 -- The three masks are different
101 -- Valid Combinations are - 1) g_int_mask - g_num_mask
102 -- 2) g_int_mask - char_mask
103 --
104 g_int_mask VARCHAR2(12) := 'S00000000000';
105 g_num_mask VARCHAR2(20) := '00000000000D00000000';
106 g_lpad_char VARCHAR2(1) := '0';
107 g_lpad_length NUMBER := 25;
108
109 g_low_range_break VARCHAR2(1) := 'L';
110 g_high_range_break VARCHAR2(1) := 'H';
111 g_null_range VARCHAR2(1) := 'N';
112 g_value_present NUMBER := 1;
113
114 g_wt_idx NUMBER := 0;
115 g_vol_idx NUMBER := 1;
116 g_fr_reg_idx NUMBER := 2;
117 g_to_reg_idx NUMBER := 3;
118 g_fr_post_idx NUMBER := 4;
119 g_to_post_idx NUMBER := 5;
120 g_transit_time_idx NUMBER := 6;
121 g_fob_code_idx NUMBER := 7;
122
123
124 -- -------------------------------------------------------------------------- --
125 -- Global Priority Tables --
126 -- ------------------------ --
127 -- --
128 -- -------------------------------------------------------------------------- --
129 g_to_postal_priority priority_tab_type;
130 g_from_postal_priority priority_tab_type;
131 g_to_region_priority priority_tab_type;
132 g_from_region_priority priority_tab_type;
133 g_all_priority priority_tab_type;
134 g_from_pzone_priority priority_tab_type;
135 g_to_pzone_priority priority_tab_type;
136
137 g_table_initialized BOOLEAN := FALSE;
138
139
140 -- -------------------------------------------------------------------------- --
141 -- Global Caches --
142 -- ------------------------ --
143 -- --
144 -- -------------------------------------------------------------------------- --
145
146 --
147 -- Global Range Caches Used
148 --
149
150 g_weight_cache range_match_cache;
151 g_volume_cache range_match_cache;
152 g_transit_cache range_match_cache;
153 g_from_postal_cache range_match_cache;
154 g_to_postal_cache range_match_cache;
155
156 --
157 -- Global Exact Caches Used
158 --
159
160 g_fob_cache exact_match_cache;
161
162 --
163 -- Other caches
164 --
165 g_groups_cache groups_cache_type;
166 g_from_to_region_cache from_to_region_cache_type;
167
168 -- Global caches for storing results for a particular rule.
169 --
170 -- Results are indexed by rule_id
171 -- The order in which results are stored in cache is determined by rank or leg sequence
172 -- For eg :
173 -- Rank(Index) Result
174 -- 1 501
175 -- 2 509
176 -- 3 513
177 -- Leg Destination(Index) Result
178 -- 1 539
179 -- 2 532
180 g_rule_result_cache rule_result_tab;
181 g_result_attribute_cache fte_cs_result_attr_tab;
182
183 -- -------------------------------------------------------------------------- --
184 -- Procedures Definitions --
185 -- ------------------------ --
186 -- --
187 -- -------------------------------------------------------------------------- --
188
189
190 --***************************************************************************--
191 --========================================================================
192 -- FUNCTION : get_fixed_key PRIVATE
193 --
194 -- PARAMETERS: p_group_id Group id
195 -- p_from_region_id From Region Id
196 -- p_to_region_id To Region Id
197 --
198 -- COMMENT : Returns appended p_group_id, p_from_region_id, p_to_region_id after
199 -- formatting them
200 --
201 --***************************************************************************--
202 FUNCTION get_fixed_key ( p_group_id IN NUMBER,
203 p_from_region_id IN NUMBER,
204 p_to_region_id IN NUMBER) RETURN VARCHAR2
205 IS
206
207 l_key VARCHAR2(32767);
208 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
209 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_fixed_key';
210
211 BEGIN
212
213 IF l_debug_on THEN
214 wsh_debug_sv.push (l_module_name);
215 END IF;
216
217 l_key := TO_CHAR(p_group_id, g_int_mask) || '-' ||
218 TO_CHAR(p_from_region_id,g_int_mask) || '-' ||
219 TO_CHAR(p_to_region_id , g_int_mask);
220
221 IF l_debug_on THEN
222 WSH_DEBUG_SV.log(l_module_name,'Fixed Key ',l_key);
223 WSH_DEBUG_SV.POP (l_module_name);
224 END IF;
225
226 RETURN (l_key);
227 EXCEPTION
228
229 WHEN others THEN
230 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_fixed_key');
231
232 IF l_debug_on THEN
233 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
234 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
235 END IF;
236 --
237 END get_fixed_key;
238
239
240 --***************************************************************************--
241 --========================================================================
242 -- FUNCTION : get_key_for_num PRIVATE
243 --
244 -- PARAMETERS: p_fixed_key Fixed Key
245 -- p_number Number key
246 --
247 -- COMMENT : Returns p_fixed_key appended with formated number key
248 --
249 --***************************************************************************--
250 FUNCTION get_key_for_num( p_fixed_key IN VARCHAR2,
251 p_number IN NUMBER) RETURN VARCHAR2
252 IS
253 l_key VARCHAR2(32767);
254 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
255 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_key_for_num';
256
257 BEGIN
258
259 IF l_debug_on THEN
260 wsh_debug_sv.push (l_module_name);
261 END IF;
262
263
264 l_key := p_fixed_key|| '-' ||
265 TO_CHAR(p_number,g_num_mask);
266
267 IF l_debug_on THEN
268 WSH_DEBUG_SV.log(l_module_name,'Number Key ',l_key);
269 WSH_DEBUG_SV.POP (l_module_name);
270 END IF;
271
272 RETURN l_key;
273
274 EXCEPTION
275 WHEN others THEN
276 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_key_for_num');
277
278 IF l_debug_on THEN
279 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
280 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
281 END IF;
282 --
283 END get_key_for_num;
284
285 --***************************************************************************--
286 --========================================================================
287 -- FUNCTION : get_key_for_char PRIVATE
288 --
289 -- PARAMETERS: p_fixed_key Fixed Key
290 -- p_char Char key
291 --
292 -- COMMENT : Returns p_fixed_key appended with padded p_char key
293 --
294 --***************************************************************************--
295 FUNCTION get_key_for_char( p_fixed_key IN VARCHAR2,
296 p_char IN VARCHAR2) RETURN VARCHAR2
297 IS
298 l_key VARCHAR2(32767);
299 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
300 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_key_for_char';
301 BEGIN
302
303 IF l_debug_on THEN
304 wsh_debug_sv.push (l_module_name);
305 END IF;
306
307 l_key := p_fixed_key||'-' ||
308 LPAD(p_char,g_lpad_length,g_lpad_char);
309
310 IF l_debug_on THEN
311 WSH_DEBUG_SV.log(l_module_name,'Char Key ',l_key);
312 WSH_DEBUG_SV.POP (l_module_name);
313 END IF;
314
315 RETURN l_key;
316 EXCEPTION
317 WHEN others THEN
318 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_key_for_char');
319
320 IF l_debug_on THEN
321 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
322 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
323 END IF;
324 --
325 END get_key_for_char;
326
327
328 --***************************************************************************--
329 --========================================================================
330 -- FUNCTION : get_key_for_null PRIVATE
331 --
332 -- PARAMETERS: p_fixed_key Fixed Key
333 --
334 --
335 -- COMMENT : Returns p_fixed_key appended with padded g_val_absent
336 --
337 --***************************************************************************--
338 FUNCTION get_key_for_null( p_fixed_key IN VARCHAR2) RETURN VARCHAR2
339 IS
340 l_key VARCHAR2(32767);
341 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
342 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_key_for_null';
343 BEGIN
344 IF l_debug_on THEN
345 wsh_debug_sv.push (l_module_name);
346 END IF;
347
348 l_key := p_fixed_key||'-'||LPAD(g_val_absent,g_lpad_length,g_lpad_char);
349
350 IF l_debug_on THEN
351 WSH_DEBUG_SV.log(l_module_name,'NULL Key',l_key);
352 WSH_DEBUG_SV.POP (l_module_name);
353 END IF;
354
355 RETURN l_key;
356 EXCEPTION
357 WHEN others THEN
361 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
358 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_key_for_null');
359 IF l_debug_on THEN
360 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
362 END IF;
363 --
364
365 END get_key_for_null;
366
367 --***************************************************************************--
368 --========================================================================
369 -- PROCEDURE : initialize_tables PRIVATE
370 --
371 -- PARAMETERS: x_return_status Return Status
372 --
373 -- COMMENT : The API initializes priority tables .Following is priority list is followed.
374 --
375 --PRIORITY ATTRIBUTE TYPE ATTRIBUTE TYPE ATTRIBUTE TYPE ATTRIBUTE TYPE
376 ----------------------------------------------------------------------------------
377 -- 1. TO_POSTAL TO_REGION/ZONE_ID FROM_POSTAL FROM_REGION/ZONE_ID
378 -- 2. TO_POSTAL TO_REGION/ZONE_ID FROM_POSTAL -
379 -- 3. TO_POSTAL TO_REGION_ID FROM POSTAL ZONE -
380 -- 4. TO_POSTAL TO_REGION_ID FROM_REGION_ID -
381 -- 5. TO_POSTAL TO_REGION_ID - -
382 -- 6. TO_POSTAL - FROM_POSTAL FROM_REGION_ID
383 -- 7. TO POSTAL ZONE - FROM_POSTAL FROM_REGION_ID
384 -- 8. TO_POSTAL - FROM_POSTAL -
385 -- 9. TO_POSTAL - FROM_POSTAL_ZONE -
386 -- 10. TO POSTAL ZONE - FROM POSTAL -
387 -- 11. TO POSTAL ZONE - FROM POSTAL_ZONE -
388 -- 12. TO_POSTAL - - FROM_REGION_ID
389 -- 13. TO POSTAL ZONE - - FROM_REGION_ID
390 -- 14. TO_POSTAL - - -
391 -- 15 TO POSTAL ZONE - - -
392 -- 16. TO_REGION_ID - FROM_POSTAL FROM_REGION_ID
393 -- 17. TO_REGION_ID - FROM_POSTAL -
394 -- 18. TO_REGION_ID - FROM POSTAL ZONE -
395 -- 19. TO_REGION_ID - FROM_REGION_ID -
396 -- 20. TO_REGION_ID - - -
397 -- 21. - - FROM_POSTAL FROM_REGION_ID
398 -- 22. - - FROM_POSTAL -
399 -- 23. - - FROM POSTAL ZONE -
400 -- 24. - - FROM_REGION_ID -
401 -- 25. - - - -
402 --***************************************************************************--
403 PROCEDURE initialize_tables (x_return_status OUT NOCOPY VARCHAR2) IS
404
405 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
406 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'initialize_tables';
407
408 BEGIN
409 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
410
411 IF l_debug_on THEN
412 wsh_debug_sv.push (l_module_name);
413 END IF;
414
415 --All the priorites
416 g_all_priority := priority_tab_type(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25);
417 --Priorities where to postal code has to be evaluated.
418 g_to_postal_priority := priority_tab_type(1,2,3,4,5,6,8,9,12,14);
419 --Priorities where from postal code has to be evaluated.
420 g_from_postal_priority := priority_tab_type(1,2,6,7,8,10,16,17,21,22);
421 --Priorities where to region has to be evaluated.
422 g_to_region_priority := priority_tab_type(1,2,3,4,5,16,17,18,19,20);
423 --Priorities where from region has to be evaluated.
424 g_from_region_priority := priority_tab_type(1,4,6,7,12,13,16,19,21,24);
425 --Priorities where to postal zone has to be evaluated.
426 g_to_pzone_priority := priority_tab_type(7,10,11,13,15);
427 --Priorities where from postal zone has to be evaluated
428 g_from_pzone_priority := priority_tab_type(3,9,11,18,23);
429
430 --Used for NULL representation of region/zone.
431 g_null_tab(1) := g_num_absent;
432
433 g_table_initialized := TRUE;
434
435 IF l_debug_on THEN
436 WSH_DEBUG_SV.POP(l_module_name);
437 END IF;
438 EXCEPTION
439 WHEN others THEN
440 g_table_initialized := FALSE;
441 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.INITIALIZE_TABLES');
442 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
443
444 IF l_debug_on THEN
445 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
446 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
447 END IF;
448 --
449 END initialize_tables;
450
451
452 --***************************************************************************--
453 --========================================================================
454 -- PROCEDURE : Delete_Rule_Caches PRIVATE
455 --
456 -- PARAMETERS: x_return_status Return Status
457 --
458 -- COMMENT : Delete all the rule caches
459 --***************************************************************************--
460
461 PROCEDURE delete_rule_caches( x_return_status OUT NOCOPY VARCHAR2)
462 IS
463
464 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
465 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'delete_rule_caches';
466
467 BEGIN
468
469 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
470
471 IF l_debug_on THEN
472 wsh_debug_sv.push (l_module_name);
473 END IF;
474
475 g_groups_cache.DELETE;
476 g_weight_cache.DELETE;
477 g_volume_cache.DELETE;
478 g_transit_cache.DELETE;
479 g_from_postal_cache.DELETE;
480 g_to_postal_cache.DELETE;
481 g_fob_cache.DELETE;
482
483 IF l_debug_on THEN
484 WSH_DEBUG_SV.logmsg(l_module_name,'CACHES DELETED');
485 WSH_DEBUG_SV.POP (l_module_name);
486 END IF;
487
488 EXCEPTION
489 WHEN others THEN
490
491 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.DELETE_RULE_CACHES');
492 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
493
497 END IF;
494 IF l_debug_on THEN
495 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
496 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
498 --
499 END delete_rule_caches;
500
501
502 --========================================================================
503 -- PROCEDURE : refresh_cache PRIVATE
504 --
505 -- PARAMETERS: x_return_status Return Status
506 -- COMMENT :
507 -- Refreshes rules database caches
508 -- if middletier session (ICX session) is changed
509 --========================================================================
510
511 PROCEDURE refresh_cache (
512 x_return_status OUT NOCOPY VARCHAR2 )
513 IS
514
515 l_session_id NUMBER := 0;
516 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
517 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'refresh_cache';
518
519 BEGIN
520 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
521 --
522 IF l_debug_on THEN
523 wsh_debug_sv.push (l_module_name);
524 END IF;
525 --
526
527 l_session_id := icx_sec.g_session_id;
528
529 IF l_debug_on THEN
530 wsh_debug_sv.logmsg(l_module_name,'g_session_id : '||g_session_id||' Current session id : '||l_session_id);
531 END IF;
532
533 IF g_session_id IS NULL OR l_session_id <> g_session_id THEN
534 delete_rule_caches(x_return_status => x_return_status);
535
536 g_session_id := l_session_id;
537
538 IF l_debug_on THEN
539 wsh_debug_sv.logmsg(l_module_name,'Rules Cache cleared');
540 END IF;
541
542 END IF;
543
544 --
545 IF l_debug_on THEN
546 wsh_debug_sv.pop (l_module_name);
547 END IF;
548 --
549
550 EXCEPTION
551 WHEN others THEN
552 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.refresh_cache');
553 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
554 --
555 IF l_debug_on THEN
556 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
557 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
558 END IF;
559 --
560 END refresh_cache;
561
562
563 --***************************************************************************--
564 --========================================================================
565 -- PROCEDURE : sort_priority_tab PRIVATE
566 --
567 -- PARAMETERS: p_priority_tab Priority tab to be sorted,
568 -- x_return_status Return Status
569 --
570 -- COMMENT : Sorts the priority table.
571 --
572 --***************************************************************************--
573 PROCEDURE sort_priority_tab(p_priority_tab IN OUT NOCOPY PRIORITY_TAB_TYPE,
574 x_return_status OUT NOCOPY VARCHAR2)
575 IS
576
577 i NUMBER;
578 j NUMBER;
579 temp NUMBER;
580 more_sort BOOLEAN := TRUE;
581
582 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
583 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'sort_priority_tab';
584
585 BEGIN
586 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
587
588 IF l_debug_on THEN
589 wsh_debug_sv.push (l_module_name);
590 END IF;
591
592 IF (p_priority_tab.COUNT<>1) THEN
593
594 i:= p_priority_tab.FIRST;
595
596 IF i IS NOT NULL THEN
597 LOOP
598 more_sort := FALSE;
599 j := p_priority_tab.NEXT(i);
600 IF j is NOT NULL THEN
601 LOOP
602 IF (p_priority_tab(j) < p_priority_tab(i)) THEN
603 temp := p_priority_tab(j);
604 p_priority_tab(j) := p_priority_tab(i);
605 p_priority_tab(i) := temp;
606 more_sort := TRUE;
607 END IF;
608
609 EXIT when j = p_priority_tab.LAST;
610 j:= p_priority_tab.NEXT(j);
611 END LOOP;
612 END IF;
613 EXIT when NOT(more_sort) OR (i = p_priority_tab.LAST);
614 i:= p_priority_tab.NEXT(i);
615 END LOOP;
616 END IF;
617
618 END IF;
619
620 IF l_debug_on THEN
621 WSH_DEBUG_SV.POP (l_module_name);
622 END IF;
623 EXCEPTION
624 WHEN others THEN
625
626 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.SORT_PRIORITY_TAB');
627 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
628
629 IF l_debug_on THEN
630 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
631 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
632 END IF;
633 --
634 END sort_priority_tab;
635
636
637 --***************************************************************************--
638 --========================================================================
639 -- PROCEDURE : get_sorted_priority_tab PRIVATE
640 --
641 -- PARAMETERS: p_attribute_tab Attributes present in the group.
642 -- x_priority_tab Table of priorities that should be validated for group.
643 -- x_return_status Return Status
644 --
645 -- COMMENT : Returns sorted priority tab.
646 --
647 --
648 -- ALGORITHM :
649 -- Uses except SET operation to find the priorities that need to be
650 -- evaluated. The procees is not an additive process it is a subtractive process.
654 PROCEDURE get_sorted_priority_tab( p_attribute_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
651 -- Remove all that need not be evaluated
652 --***************************************************************************--
653
655 x_priority_tab OUT NOCOPY PRIORITY_TAB_TYPE,
656 x_return_status OUT NOCOPY VARCHAR2)
657 IS
658
659 l_sort BOOLEAN := FALSE;
660 l_priority_tab PRIORITY_TAB_TYPE;
661
662 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
663 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'get_sorted_priority_tab';
664
665 BEGIN
666 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
667
668 IF l_debug_on THEN
669 wsh_debug_sv.push (l_module_name);
670 END IF;
671
672 l_priority_tab := g_all_priority;
673
674 IF NOT(p_attribute_tab.EXISTS(g_fr_post_idx)) THEN
675 l_priority_tab := l_priority_tab MULTISET EXCEPT g_from_postal_priority;
676 l_sort := TRUE;
677 END IF;
678
679 IF NOT(p_attribute_tab.EXISTS(g_to_post_idx)) THEN
680 l_priority_tab := l_priority_tab MULTISET EXCEPT g_to_postal_priority;
681 l_sort := TRUE;
682 END IF;
683
684 -- We need to remove from pzone also.
685 IF NOT(p_attribute_tab.EXISTS(g_fr_reg_idx)) THEN
686 l_priority_tab := l_priority_tab MULTISET EXCEPT g_from_region_priority;
687 l_priority_tab := l_priority_tab MULTISET EXCEPT g_from_pzone_priority;
688 l_sort := TRUE;
689 END IF;
690
691 IF NOT(p_attribute_tab.EXISTS(g_to_reg_idx)) THEN
692 l_priority_tab := l_priority_tab MULTISET EXCEPT g_to_region_priority;
693 l_priority_tab := l_priority_tab MULTISET EXCEPT g_to_pzone_priority;
694 l_sort := TRUE;
695 END IF;
696
697 -- Results of operations may not be sorted fashion
698 -- Sort the priority tab
699 -- Writing boolean sort
700
701 IF (l_priority_tab.COUNT<>1 and l_sort ) THEN
702
703 sort_priority_tab( p_priority_tab => l_priority_tab,
704 x_return_status => x_return_status);
705
706 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
707 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
708 raise FND_API.G_EXC_UNEXPECTED_ERROR;
709 END IF;
710 END IF;
711 END IF;
712
713 x_priority_tab := l_priority_tab;
714
715 IF l_debug_on THEN
716 WSH_DEBUG_SV.POP (l_module_name);
717 END IF;
718 EXCEPTION
719 WHEN others THEN
720 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.GET_SORTED_PRIORITY_TAB');
721 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
722
723 IF l_debug_on THEN
724 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
725 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
726 END IF;
727 --
728 END get_sorted_priority_tab;
729
730
731 --***************************************************************************--
732 --========================================================================
733 -- PROCEDURE : add_group_to_group_cache PRIVATE
734 --
735 -- PARAMETERS: p_group_id Group id to be used
736 -- x_return_status Return Status
737 --
738 -- COMMENT : Adds a group and its attribute to the global cache.
739 --
740 --
741 -- ALGORITHM :
742 -- 1) Query for all group attributes.
743 -- 2) Add the group attributes and priority_tab to the cache
744 --***************************************************************************--
745 PROCEDURE add_group_to_group_cache( p_group_id IN NUMBER,
746 x_return_status OUT NOCOPY VARCHAR2)
747 IS
748
749 --
750 --Check this query
751 --
752 CURSOR c_get_group_attributes IS
753 SELECT attribute_name,attribute_uom_code
754 FROM fte_sel_group_attributes
755 WHERE group_id = p_group_id;
756
757 itr NUMBER;
758
759 l_attr_tab FTE_ATTR_CODE_VAL_TAB_TYPE;
760 l_group_attr WSH_UTIL_CORE.ID_TAB_TYPE;
761
762 l_group_cache_rec GROUP_CACHE_REC;
763 l_priority_tab PRIORITY_TAB_TYPE;
764 l_priority_str VARCHAR2(600);
765
766 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
767 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'add_group_to_group_cache';
768
769 BEGIN
770 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
771
772 IF l_debug_on THEN
773 wsh_debug_sv.push (l_module_name);
774 WSH_DEBUG_SV.log(l_module_name,'Adding group to Group Cache ',p_group_id);
775 WSH_DEBUG_SV.logmsg(l_module_name,'Attribtues to be checked ');
776 END IF;
777
778 OPEN c_get_group_attributes;
779 FETCH c_get_group_attributes BULK COLLECT INTO l_attr_tab;
780 CLOSE c_get_group_attributes;
781
782 itr := l_attr_tab.FIRST;
783
784 IF (itr IS NOT NULL) THEN
785 LOOP
786
787 IF ( l_attr_tab(itr).attr_code = 'WEIGHT') THEN
788 l_group_attr(g_wt_idx) := g_value_present;
789 l_group_cache_rec.weight_uom_code := l_attr_tab(itr).attr_val;
790
791 IF l_debug_on THEN
792 WSH_DEBUG_SV.logmsg(l_module_name,'Weight');
793 END IF;
794
795 ELSIF (l_attr_tab(itr).attr_code = 'VOLUME') THEN
796 l_group_attr(g_vol_idx) := g_value_present;
797 l_group_cache_rec.volume_uom_code := l_attr_tab(itr).attr_val;
798
799 IF l_debug_on THEN
800 WSH_DEBUG_SV.logmsg(l_module_name,'Volume');
801 END IF;
802
803 ELSIF (l_attr_tab(itr).attr_code = 'TRANSIT_TIME') THEN
804 l_group_attr(g_transit_time_idx) := g_value_present;
805
809
806 IF l_debug_on THEN
807 WSH_DEBUG_SV.logmsg(l_module_name,'Transit Time');
808 END IF;
810 --
811 -- FTE_SEL_RULE_RESTICTIONS has FROM_REGION_ID
812 -- FTE_SEL_GROUP_ATTRIBUTES has 'FROM_REGION_COUNTRY'
813 --
814 ELSIF (l_attr_tab(itr).attr_code IN ('FROM_REGION_COUNTRY','FROM_REGION_STATE','FROM_REGION_CITY','FROM_ZONE','FROM_REGION')) THEN
815
816 -- R12 UI enters 'FROM_REGION' for postal codes also.
817 l_group_attr(g_fr_reg_idx) := g_value_present;
818 IF (l_attr_tab(itr).attr_code = 'FROM_REGION') THEN
819 l_group_attr(g_fr_post_idx) := g_value_present;
820 IF l_debug_on THEN
821 WSH_DEBUG_SV.logmsg(l_module_name,'From Region Postal Code');
822 END IF;
823 END IF;
824
825 IF l_debug_on THEN
826 WSH_DEBUG_SV.logmsg(l_module_name,'From Region');
827 END IF;
828
829 --
830 -- FTE_SEL_RULE_RESTRICTIONS has TO_REGION_ID
831 -- FTE_SEL_GROUP_ATTRIBUTES has 'TO_REGION_COUNTRY'
832 --
833 ELSIF (l_attr_tab(itr).attr_code IN ('TO_REGION_COUNTRY','TO_REGION_STATE','TO_REGION_CITY','TO_ZONE','TO_REGION')) THEN
834 l_group_attr(g_to_reg_idx) := g_value_present;
835
836 -- R12 UI enters 'TO_REGION' for postal codes also.
837 IF (l_attr_tab(itr).attr_code = 'TO_REGION') THEN
838 l_group_attr(g_to_post_idx) := g_value_present;
839 IF l_debug_on THEN
840 WSH_DEBUG_SV.logmsg(l_module_name,'To Region Postal Code');
841 END IF;
842 END IF;
843
844 IF l_debug_on THEN
845 WSH_DEBUG_SV.logmsg(l_module_name,'To Region');
846 END IF;
847
848 -- Prior to R12 , UI stores 'FROM_REGION_POSTAL_CODE' for from postal code.
849 ELSIF (l_attr_tab(itr).attr_code IN('FROM_REGION_POSTAL_CODE')) THEN
850 l_group_attr(g_fr_post_idx) := g_value_present;
851
852 IF l_debug_on THEN
853 WSH_DEBUG_SV.logmsg(l_module_name,'From Region Postal Code');
854 END IF;
855
856 -- Prior to R12 , UI stores 'TO_REGION_POSTAL_CODE' for To postal codes.
857 ELSIF (l_attr_tab(itr).attr_code IN ('TO_REGION_POSTAL_CODE')) THEN
858 l_group_attr(g_to_post_idx) := g_value_present;
859
860 IF l_debug_on THEN
861 WSH_DEBUG_SV.logmsg(l_module_name,'To Region Postal Code');
862 END IF;
863
864 ELSIF (l_attr_tab(itr).attr_code = 'FOB_CODE') THEN
865 l_group_attr(g_fob_code_idx) := g_value_present;
866
867 IF l_debug_on THEN
868 WSH_DEBUG_SV.logmsg(l_module_name,'Fob Code');
869 END IF;
870
871 END IF;
872
873 EXIT WHEN itr = l_attr_tab.LAST;
874 itr := l_attr_tab.NEXT(itr);
875 END LOOP;
876 END IF;
877
878 get_sorted_priority_tab( p_attribute_tab => l_group_attr,
879 x_priority_tab => l_priority_tab,
880 x_return_status => x_return_status);
881
882 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
883 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
884 raise FND_API.G_EXC_UNEXPECTED_ERROR;
885 END IF;
886 END IF;
887
888 l_group_cache_rec.attribute_tab := l_group_attr;
889 l_group_cache_rec.priority_tab := l_priority_tab;
890
891 g_groups_cache(p_group_id):=l_group_cache_rec;
892
893 IF l_debug_on THEN
894
895 itr := l_priority_tab.FIRST;
896 l_priority_str := l_priority_tab(itr);
897
898 IF NOT (itr = l_priority_tab.LAST) THEN
899 LOOP
900 itr := l_priority_tab.NEXT(itr);
901 l_priority_str := l_priority_str ||'-'||l_priority_tab(itr);
902 EXIT WHEN itr = l_priority_tab.LAST;
903 END LOOP;
904 END IF;
905
906 WSH_DEBUG_SV.log(l_module_name,'Priorities To be checked',l_priority_str);
907 WSH_DEBUG_SV.POP (l_module_name);
908 END IF;
909
910 EXCEPTION
911 WHEN OTHERS THEN
912
913 IF c_get_group_attributes%ISOPEN THEN
914 CLOSE c_get_group_attributes;
915 END IF;
916
917 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.add_group_to_cache');
918 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
919
920 IF l_debug_on THEN
921 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
922 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
923 END IF;
924 --
925 END add_group_to_group_cache;
926
927
928 --***************************************************************************--
929 --========================================================================
930 -- PROCEDURE : add_to_non_range_cache PRIVATE
931 --
932 -- PARAMETERS: p_cache Global cache to be modified
933 -- p_fixed_key Key for the attribute value
934 -- p_null_key Key used if attribute value is null.
935 -- p_rule_id Rule id to be added
936 -- x_return_status Return Status
937 --
938 -- COMMENT : Adds a rule to Non Range Cache
939 --
940 --
941 -- ALGORITHM :
942 -- 1. IF NULL_KEY THEN index is NULL_KEY ELSE index is FIXED_KEY
943 -- 2. Insert the record at proper index
944 --***************************************************************************--
945 PROCEDURE add_to_non_range_cache ( p_cache IN OUT NOCOPY EXACT_MATCH_CACHE,
946 p_fixed_key IN VARCHAR2 DEFAULT NULL,
947 p_null_key IN VARCHAR2 DEFAULT NULL,
948 p_rule_id IN NUMBER,
949 x_return_status OUT NOCOPY VARCHAR2)
950 IS
951
952 l_rule_tab exact_match_tab;
953 l_key VARCHAR2(32767);
954
958 BEGIN
955 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
956 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'add_to_non_range_cache';
957
959
960 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
961
962 IF l_debug_on THEN
963 WSH_DEBUG_SV.push(l_module_name);
964 WSH_DEBUG_SV.log(l_module_name,'p_fixed_key',p_fixed_key);
965 WSH_DEBUG_SV.log(l_module_name,'p_null_key ', p_null_key);
966 WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
967 END IF;
968
969 IF (p_fixed_key IS NOT NULL) THEN
970 l_key := p_fixed_key;
971 ELSE
972 l_key := p_null_key;
973 END IF;
974
975 IF (p_cache.EXISTS(l_key)) THEN
976 -- Add new rule to table present
977 l_rule_tab := p_cache(l_key);
978 l_rule_tab(l_rule_tab.COUNT) := p_rule_id;
979 ELSE
980 -- Add new table
981 l_rule_tab(0) := p_rule_id;
982 END IF;
983
984 p_cache(l_key) := l_rule_tab;
985
986 IF l_debug_on THEN
987 WSH_DEBUG_SV.POP (l_module_name);
988 END IF;
989
990 EXCEPTION
991 WHEN others THEN
992 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.ADD_TO_NON_RANGE_CACHE');
993 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
994
995 IF l_debug_on THEN
996 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
997 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
998 END IF;
999 --
1000 END add_to_non_range_cache;
1001
1002 --***************************************************************************--
1003 --========================================================================
1004 -- PROCEDURE : add_to_range_cache PRIVATE
1005 --
1006 -- PARAMETERS: p_cache Global cache to be modified
1007 -- p_key_low Lower range break
1008 -- p_key_high Higher range break
1009 -- p_null_key Key used if attribute value is null.
1010 -- p_overlap_flag Indicates whether there is overlap in the range or not
1011 -- p_rule_id Rule id to be added
1012 -- x_return_status Return Status
1013 --
1014 -- COMMENT : Adds a rule to Range Cache
1015 --
1016 --
1017 -- ALGORITHM :
1018 -- 1. If NULL_KEY then create null_key Records else Low/High Records.
1019 -- 2. Insert the record at proper index
1020 --***************************************************************************--
1021 PROCEDURE add_to_range_cache( p_cache IN OUT NOCOPY RANGE_MATCH_CACHE,
1022 p_key_low IN VARCHAR2 DEFAULT NULL,
1023 p_key_high IN VARCHAR2 DEFAULT NULL,
1024 p_null_key IN VARCHAR2 DEFAULT NULL,
1025 p_overlap_flag IN VARCHAR2 DEFAULT 'Y',
1026 p_rule_id IN NUMBER,
1027 x_return_status OUT NOCOPY VARCHAR2)
1028 IS
1029
1030 l_rule_rec_low range_match_rule_rec;
1031 l_rule_rec_high range_match_rule_rec;
1032 l_rule_rec_null range_match_rule_rec;
1033
1034 l_rule_tab_low range_match_tab;
1035 l_rule_tab_high range_match_tab;
1036 l_rule_tab_null range_match_tab;
1037
1038 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1039 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||G_PKG_NAME ||'.'||'add_to_range_cache';
1040
1041 BEGIN
1042
1043 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1044
1045 IF l_debug_on THEN
1046
1047 WSH_DEBUG_SV.push(l_module_name);
1048 WSH_DEBUG_SV.log(l_module_name,'p_key_low ',p_key_low);
1049 WSH_DEBUG_SV.log(l_module_name,'p_key_high ',p_key_high);
1050 WSH_DEBUG_SV.log(l_module_name,'p_null_key ',p_null_key);
1051 WSH_DEBUG_SV.log(l_module_name,'p_overlap_flag',p_overlap_flag);
1052 WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
1053
1054 END IF;
1055
1056 IF (p_null_key IS NULL) THEN
1057
1058 --
1059 -- Create the records
1060 --
1061
1062 l_rule_rec_low.rule_id := p_rule_id;
1063 l_rule_rec_low.overlap_flag := p_overlap_flag;
1064 l_rule_rec_low.limit := g_low_range_break;
1065
1066 l_rule_rec_high.rule_id := p_rule_id;
1067 l_rule_rec_high.overlap_flag := p_overlap_flag;
1068 l_rule_rec_high.limit := g_high_range_break;
1069
1070 --
1071 -- Add the records to the cache
1072 --
1073
1074 IF (p_cache.EXISTS(p_key_low)) THEN
1075 l_rule_tab_low := p_cache(p_key_low);
1076 l_rule_tab_low(l_rule_tab_low.COUNT) := l_rule_rec_low;
1077 ELSE
1078 l_rule_tab_low(0) := l_rule_rec_low;
1079 END IF;
1080
1081 p_cache(p_key_low) := l_rule_tab_low;
1082
1083 IF (p_cache.EXISTS(p_key_high)) THEN
1084 l_rule_tab_high := p_cache(p_key_high);
1085 l_rule_tab_high(l_rule_tab_high.COUNT) := l_rule_rec_high;
1086 ELSE
1087 l_rule_tab_high(0) := l_rule_rec_high;
1088 END IF;
1089 p_cache(p_key_high) := l_rule_tab_high;
1090
1091 ELSE
1092
1093 l_rule_rec_null.rule_id := p_rule_id;
1094 l_rule_rec_null.overlap_flag := p_overlap_flag;
1095 l_rule_rec_null.limit := g_null_range;
1096
1097 IF (p_cache.EXISTS(p_null_key)) THEN
1098 l_rule_tab_null := p_cache(p_null_key);
1099 l_rule_tab_null(l_rule_tab_null.COUNT) := l_rule_rec_null;
1100 ELSE
1101 l_rule_tab_null(0) := l_rule_rec_null;
1102 END IF;
1103
1104 p_cache(p_null_key) := l_rule_tab_null;
1105
1106 END IF;
1110 END IF;
1107
1108 IF l_debug_on THEN
1109 WSH_DEBUG_SV.POP (l_module_name);
1111
1112 EXCEPTION
1113 WHEN others THEN
1114
1115 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.ADD_TO_RANGE_CACHE');
1116 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1117
1118 IF l_debug_on THEN
1119 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1120 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1121 END IF;
1122 END add_to_range_cache;
1123
1124
1125 --***************************************************************************--
1126 --========================================================================
1127 -- PROCEDURE : build_cache PRIVATE
1128 --
1129 -- PARAMETERS: p_group_id Group id to be used
1130 -- x_return_status Return Status
1131 --
1132 -- COMMENT : For a given group id creates the cache.
1133 --
1134 --
1135 -- ALGORITHM :
1136 -- 1, Drop the cache if number of groups is more than the limit.
1137 -- 2. Query for all the rules within a group.
1138 -- 3. Check if overlap flag is set for a sample rule.
1139 -- If flag is not set then call FTE_ACS_RULE_UTIL_PKG.SET_overlap_FLAG
1140 -- 4. For all Rules
1141 -- Get all the attributes for a rule.
1142 -- Put the respective attributes in respective caches.
1143 --
1144 --***************************************************************************--
1145 PROCEDURE build_cache(p_group_id IN NUMBER,
1146 x_return_status OUT NOCOPY VARCHAR)
1147
1148 IS
1149
1150 CURSOR C_GET_RULE_RESTRICTIONS IS
1151 SELECT rule_id,
1152 attribute_name,
1153 attribute_value_From,
1154 attribute_value_to,
1155 attribute_value_from_number,
1156 attribute_value_to_number,
1157 range_overlap_flag
1158 FROM fte_sel_rule_restrictions
1159 WHERE group_id = p_group_id
1160 ORDER BY rule_id;
1161
1162
1163 curr_rule_id NUMBER;
1164 prev_rule_id NUMBER;
1165
1166 from_weight NUMBER;
1167 to_weight NUMBER;
1168 weight_overlap VARCHAR2(1);
1169
1170 from_volume NUMBER;
1171 to_volume NUMBER;
1172 volume_overlap VARCHAR2(1);
1173
1174 from_transit_time NUMBER;
1175 to_transit_time NUMBER;
1176 transit_overlap VARCHAR2(1);
1177
1178 from_region NUMBER;
1179 to_region NUMBER;
1180
1181 from_postal_low VARCHAR2(240);
1182 from_postal_high VARCHAR2(240);
1183 from_postal_overlap VARCHAR2(1);
1184
1185 to_postal_low VARCHAR2(240);
1186 to_postal_high VARCHAR2(240);
1187 to_postal_overlap VARCHAR2(1);
1188
1189 fob_code VARCHAR2(240);
1190
1191 rule_tab RULES_TAB_TYPE;
1192
1193 fixed_key VARCHAR2(32767);
1194 null_key VARCHAR2(32767);
1195 key_low VARCHAR2(32767);
1196 key_high VARCHAR2(32767);
1197
1198 itr NUMBER;
1199 next_itr NUMBER;
1200 next_rule_id NUMBER;
1201
1202 l_group_attr WSH_UTIL_CORE.ID_TAB_TYPE;
1203
1204 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1205 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'build_cache';
1206
1207 --
1208 --Exceptions
1209 --
1210 FTE_CS_ERROR_INVALID_RANGE EXCEPTION;
1211
1212 BEGIN
1213
1214 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1215
1216 IF l_debug_on THEN
1217 WSH_DEBUG_SV.push(l_module_name);
1218 WSH_DEBUG_SV.log(l_module_name,'p_group_id ',p_group_id);
1219 END IF;
1220
1221 refresh_cache(x_return_status);
1222
1223 -- We will not have greater than, because it means we first add and then drop
1224 -- the cache.
1225 IF ( g_groups_cache.COUNT = g_max_cache_size ) THEN
1226 DELETE_RULE_CACHES(x_return_status => x_return_status);
1227 END IF;
1228
1229 --
1230 -- Add group to the group cache.
1231 --
1232 add_group_to_group_cache( p_group_id => p_group_id,
1233 x_return_status => x_return_status);
1234
1235 l_group_attr:= g_groups_cache(p_group_id).attribute_tab;
1236
1237
1238 OPEN c_get_rule_restrictions;
1239 FETCH c_get_rule_restrictions BULK COLLECT INTO rule_tab;
1240 CLOSE c_get_rule_restrictions;
1241
1242 itr := rule_tab.FIRST;
1243
1244 IF (itr IS NOT NULL) THEN
1245
1246 --
1247 -- overlap flag has to be either 'Y' or 'N'
1248 --
1249 IF (rule_tab(itr).overlap_flag IS NULL) THEN
1250
1251 --
1252 -- overlap flag needs to be set
1253 --
1254
1255 FTE_ACS_RULE_UTIL_PKG.SET_RANGE_OVERLAP_FLAG(p_group_id => p_group_id);
1256
1257 --
1258 -- Delete Rule Tab and query again.
1259 --
1260 rule_tab.DELETE;
1261
1262 OPEN c_get_rule_restrictions;
1263 FETCH c_get_rule_restrictions BULK COLLECT INTO rule_tab;
1264 CLOSE c_get_rule_restrictions;
1265 itr := rule_tab.FIRST;
1266 END IF;
1267 END IF;
1268
1269
1270 IF (itr IS NOT NULL) THEN
1271 LOOP
1272 --
1273 --Initialize the default Values;
1274 --
1275 curr_rule_id := rule_tab(itr).rule_id;
1276 from_region := g_num_absent;
1277 to_region := g_num_absent;
1278 from_postal_low := g_val_absent;
1279 from_postal_high := g_val_absent;
1280 to_postal_low := g_val_absent;
1281 to_postal_high := g_val_absent;
1282 from_weight := g_num_absent;
1286 from_transit_time := g_num_absent;
1283 to_weight := g_num_absent;
1284 from_volume := g_num_absent;
1285 to_volume := g_num_absent;
1287 to_transit_time := g_num_absent;
1288 fob_code := g_val_absent;
1289
1290 --
1291 -- Get the values for a rule.
1292 --
1293 LOOP
1294
1295 -- For Weight Volume Conversions we use display weight and volume.
1296 IF ( rule_tab(itr).attribute_name = 'DISPLAY_WEIGHT') THEN
1297
1298 from_weight := rule_tab(itr).attribute_value_from_number;
1299 to_weight := rule_tab(itr).attribute_value_to_number;
1300 weight_overlap := rule_tab(itr).overlap_flag;
1301
1302 ELSIF (rule_tab(itr).attribute_name = 'DISPLAY_VOLUME') THEN
1303
1304 from_volume := rule_tab(itr).attribute_value_from_number;
1305 to_volume := rule_tab(itr).attribute_value_to_number;
1306 volume_overlap := rule_tab(itr).overlap_flag;
1307
1308 ELSIF (rule_tab(itr).attribute_name = 'TRANSIT_TIME') THEN
1309
1310 from_transit_time := rule_tab(itr).attribute_value_from_number;
1311 to_transit_time := rule_tab(itr).attribute_value_to_number;
1312 transit_overlap := rule_tab(itr).overlap_flag;
1313
1314 ELSIF (rule_tab(itr).attribute_name = 'FROM_REGION_POSTAL_CODE') THEN
1315
1316 from_postal_low := rule_tab(itr).attribute_value_from;
1317 from_postal_high := rule_tab(itr).attribute_value_to;
1318
1319 ELSIF (rule_tab(itr).attribute_name = 'TO_REGION_POSTAL_CODE') THEN
1320
1321 to_postal_low := rule_tab(itr).attribute_value_from;
1322 to_postal_high := rule_tab(itr).attribute_value_to;
1323
1324 ELSIF (rule_tab(itr).attribute_name = 'FROM_REGION_ID') THEN
1325
1326 from_region := rule_tab(itr).attribute_value_from_number;
1327
1328 ELSIF (rule_tab(itr).attribute_name = 'TO_REGION_ID') THEN
1329
1330 to_region := rule_tab(itr).attribute_value_from_number;
1331
1332 ELSIF (rule_tab(itr).attribute_name = 'FOB_CODE') THEN
1333
1334 fob_code := rule_tab(itr).attribute_value_from;
1335
1336 END IF;
1337
1338 IF ( itr = rule_tab.LAST ) THEN
1339 EXIT;
1340 ELSE
1341 next_itr := rule_tab.NEXT(itr);
1342 next_rule_id := rule_tab(next_itr).rule_id;
1343 EXIT WHEN next_rule_id <> curr_rule_id;
1344 itr := next_itr;
1345 END IF;
1346
1347 END LOOP;
1348
1349 IF l_debug_on THEN
1350
1351 WSH_DEBUG_SV.log(l_module_name,' Rule ID ', curr_rule_id );
1352 WSH_DEBUG_SV.log(l_module_name,' From_region ', from_region );
1353 WSH_DEBUG_SV.log(l_module_name,' To_region ', to_region);
1354 WSH_DEBUG_SV.log(l_module_name,' From_postal_low ', from_postal_low);
1355 WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1356 WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1357 WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1358 WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1359 WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1360 WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1361 WSH_DEBUG_SV.log(l_module_name,' To_volume ', to_volume);
1362 WSH_DEBUG_SV.log(l_module_name,' From_transit_time ', from_transit_time);
1363 WSH_DEBUG_SV.log(l_module_name,' To_transit_time ', to_transit_time);
1364 WSH_DEBUG_SV.log(l_module_name,' Fob_code ', fob_code);
1365
1366 END IF;
1367
1368 fixed_key := get_fixed_key(p_group_id => p_group_id,
1369 p_from_region_id => from_region,
1370 p_to_region_id => to_region);
1371
1372
1373 null_key := get_key_for_null(p_fixed_key => fixed_key);
1374
1375 --
1376 --Register From-To-Region combination in the From-To-Region-Cache
1377 --Register using dummy value
1378 --
1379 g_from_to_region_cache(fixed_key) := g_value_present;
1380
1381 --
1382 -- In range attributes either both from and to will be present
1383 -- or none will be present
1384 -- Only add value to the cache if the attribute is present in the group.
1385
1386 IF (l_group_attr.EXISTS(g_wt_idx)) THEN
1387
1388 IF l_debug_on THEN
1389 WSH_DEBUG_SV.logmsg(l_module_name,'Adding to Weight Cache');
1390 END IF;
1391
1392 IF (from_weight <> g_num_absent) AND (to_weight <> g_num_absent) THEN
1393
1394 key_low := get_key_for_num( p_fixed_key => fixed_key,
1395 p_number => from_weight);
1396
1397 key_high := get_key_for_num( p_fixed_key => fixed_key,
1398 p_number => to_weight);
1399
1400
1401 add_to_range_cache( p_cache => g_weight_cache,
1402 p_key_low => key_low,
1403 p_key_high => key_high,
1404 p_overlap_flag => weight_overlap,
1405 p_rule_id => curr_rule_id,
1406 x_return_status => x_return_status);
1407
1408 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1409 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1410 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1411 END IF;
1412 END IF;
1413
1414 ELSIF (from_weight = g_num_absent) AND (to_weight = g_num_absent) THEN
1415
1416
1417 add_to_range_cache( p_cache => g_weight_cache,
1418 p_null_key => null_key,
1419 p_rule_id => curr_rule_id,
1420 x_return_status => x_return_status);
1421
1425 END IF;
1422 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1423 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1424 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1426 END IF;
1427 ELSE
1428 RAISE FTE_CS_ERROR_INVALID_RANGE;
1429 END IF;
1430 END IF;
1431
1432 IF (l_group_attr.EXISTS(g_vol_idx)) THEN
1433
1434
1435 IF l_debug_on THEN
1436 WSH_DEBUG_SV.logmsg(l_module_name,'Adding to Volume Cache');
1437 END IF;
1438
1439 IF (from_volume <> g_num_absent) AND (to_volume <> g_num_absent) THEN
1440
1441 key_low := get_key_for_num( p_fixed_key => fixed_key,
1442 p_number => from_volume);
1443
1444 key_high := get_key_for_num( p_fixed_key => fixed_key,
1445 p_number => to_volume);
1446
1447 add_to_range_cache( p_cache => g_volume_cache,
1448 p_key_low => key_low,
1449 p_key_high => key_high,
1450 p_overlap_flag => volume_overlap,
1451 p_rule_id => curr_rule_id,
1452 x_return_status => x_return_status);
1453
1454 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1455 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1456 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1457 END IF;
1458 END IF;
1459
1460 ELSIF (from_volume = g_num_absent) AND (to_volume = g_num_absent) THEN
1461
1462 add_to_range_cache( p_cache => g_volume_cache,
1463 p_null_key => null_key,
1464 p_rule_id => curr_rule_id,
1465 x_return_status => x_return_status);
1466
1467 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1468 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1469 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1470 END IF;
1471 END IF;
1472 ELSE
1473 RAISE FTE_CS_ERROR_INVALID_RANGE;
1474 END IF;
1475
1476 END IF;
1477
1478 IF (l_group_attr.EXISTS(g_transit_time_idx)) THEN
1479
1480 IF l_debug_on THEN
1481 WSH_DEBUG_SV.logmsg(l_module_name,'Adding to Transit Time Cache');
1482 END IF;
1483
1484
1485 IF (from_transit_time <> g_num_absent) AND (to_transit_time <> g_num_absent) THEN
1486
1487 key_low := get_key_for_num( p_fixed_key => fixed_key,
1488 p_number => from_transit_time);
1489
1490 key_high := get_key_for_num( p_fixed_key => fixed_key,
1491 p_number => to_transit_time);
1492
1493 add_to_range_cache( p_cache => g_transit_cache,
1494 p_key_low => key_low,
1495 p_key_high => key_high,
1496 p_overlap_flag => transit_overlap,
1497 p_rule_id => curr_rule_id,
1498 x_return_status => x_return_status );
1499
1500 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1501 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1502 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1503 END IF;
1504 END IF;
1505
1506 ELSIF (from_transit_time = g_num_absent) AND (to_transit_time = g_num_absent) THEN
1507
1508 add_to_range_cache( p_cache => g_transit_cache,
1509 p_null_key => null_key,
1510 p_rule_id => curr_rule_id,
1511 x_return_status => x_return_status);
1512
1513 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1514 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1515 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1516 END IF;
1517 END IF;
1518
1519 ELSE
1520 RAISE FTE_CS_ERROR_INVALID_RANGE;
1521 END IF;
1522
1523 END IF;
1524
1525 IF (l_group_attr.EXISTS(g_to_post_idx)) THEN
1526
1527 IF l_debug_on THEN
1528 WSH_DEBUG_SV.logmsg(l_module_name,'Adding to To Postal Code Cache');
1529 END IF;
1530
1531 IF (to_postal_low <> g_val_absent) AND (to_postal_high <> g_val_absent) THEN
1532
1533 key_low := get_key_for_char( p_fixed_key => fixed_key,
1534 p_char => to_postal_low);
1535
1536 key_high := get_key_for_char( p_fixed_key => fixed_key,
1537 p_char => to_postal_high);
1538
1539
1540 add_to_range_cache( p_cache => g_to_postal_cache,
1541 p_key_low => key_low,
1542 p_key_high => key_high,
1543 p_overlap_flag => to_postal_overlap,
1544 p_rule_id => curr_rule_id,
1545 x_return_status => x_return_status );
1546
1547 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1548 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1549 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1550 END IF;
1551 END IF;
1552
1553 ELSIF (to_postal_low = g_val_absent) AND (to_postal_high = g_val_absent) THEN
1554
1555 add_to_range_cache( p_cache => g_to_postal_cache,
1556 p_null_key => null_key,
1557 p_rule_id => curr_rule_id,
1558 x_return_status => x_return_status);
1559
1560 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1561 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1562 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1563 END IF;
1564 END IF;
1565
1566 --
1567 -- In case of postal code one limit can be null
1568 --
1569
1570 ELSIF (to_postal_low = g_val_absent) AND (to_postal_high <> g_val_absent) THEN
1571
1572 IF l_debug_on THEN
1573 WSH_DEBUG_SV.logmsg(l_module_name,'Lower postal code is NULL');
1574 END IF;
1575
1576 key_high := get_key_for_char( p_fixed_key => fixed_key,
1577 p_char => to_postal_high);
1578
1582 p_overlap_flag => to_postal_overlap,
1579 add_to_range_cache( p_cache => g_to_postal_cache,
1580 p_key_low => key_high,
1581 p_key_high => key_high,
1583 p_rule_id => curr_rule_id,
1584 x_return_status => x_return_status );
1585
1586 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1587 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1588 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1589 END IF;
1590 END IF;
1591
1592 ELSE
1593
1594 IF l_debug_on THEN
1595 WSH_DEBUG_SV.logmsg(l_module_name,'Upper postal code is NULL');
1596 END IF;
1597
1598 key_low := get_key_for_char( p_fixed_key => fixed_key,
1599 p_char => to_postal_low);
1600
1601 add_to_range_cache( p_cache => g_to_postal_cache,
1602 p_key_low => key_low,
1603 p_key_high => key_low,
1604 p_overlap_flag => to_postal_overlap,
1605 p_rule_id => curr_rule_id,
1606 x_return_status => x_return_status );
1607
1608 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1609 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1610 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1611 END IF;
1612 END IF;
1613 END IF;
1614 END IF;
1615
1616 IF (l_group_attr.EXISTS(g_fr_post_idx)) THEN
1617
1618 IF l_debug_on THEN
1619 WSH_DEBUG_SV.logmsg(l_module_name,'Adding to From Postal Code Cache');
1620 END IF;
1621
1622 IF (from_postal_low <> g_val_absent) AND (from_postal_high <> g_val_absent) THEN
1623
1624 key_low := get_key_for_char( p_fixed_key => fixed_key,
1625 p_char => from_postal_low);
1626
1627 key_high := get_key_for_char( p_fixed_key => fixed_key,
1628 p_char => from_postal_high);
1629
1630 add_to_range_cache( p_cache => g_from_postal_cache,
1631 p_key_low => key_low,
1632 p_key_high => key_high,
1633 p_overlap_flag => from_postal_overlap,
1634 p_rule_id => curr_rule_id,
1635 x_return_status => x_return_status );
1636
1637 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1638 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1639 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1640 END IF;
1641 END IF;
1642
1643 ELSIF (from_postal_low = g_val_absent) AND (from_postal_high = g_val_absent) THEN
1644
1645 IF l_debug_on THEN
1646 WSH_DEBUG_SV.logmsg(l_module_name,'Both from and to Postal Codes are NULL');
1647 END IF;
1648
1649 add_to_range_cache( p_cache => g_from_postal_cache,
1650 p_null_key => null_key,
1651 p_rule_id => curr_rule_id,
1652 x_return_status => x_return_status);
1653
1654 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1655 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1656 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1657 END IF;
1658 END IF;
1659
1660 ELSIF (from_postal_low = g_val_absent) AND (from_postal_high <> g_val_absent) THEN
1661
1662 IF l_debug_on THEN
1663 WSH_DEBUG_SV.logmsg(l_module_name,'Lower postal code is NULL');
1664 END IF;
1665
1666 key_high := get_key_for_char( p_fixed_key => fixed_key,
1667 p_char => from_postal_high);
1668
1669 add_to_range_cache( p_cache => g_from_postal_cache,
1670 p_key_low => key_high,
1671 p_key_high => key_high,
1672 p_overlap_flag => from_postal_overlap,
1673 p_rule_id => curr_rule_id,
1674 x_return_status => x_return_status );
1675
1676 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1677 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1678 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1679 END IF;
1680 END IF;
1681
1682 ELSE
1683
1684 IF l_debug_on THEN
1685 WSH_DEBUG_SV.logmsg(l_module_name,'Upper postal code is NULL');
1686 END IF;
1687
1688 key_low := get_key_for_char( p_fixed_key => fixed_key,
1689 p_char => from_postal_low);
1690
1691 add_to_range_cache( p_cache => g_from_postal_cache,
1692 p_key_low => key_low,
1693 p_key_high => key_low,
1694 p_overlap_flag => from_postal_overlap,
1695 p_rule_id => curr_rule_id,
1696 x_return_status => x_return_status );
1697
1698 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1699 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1700 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1701 END IF;
1702 END IF;
1703
1704 END IF;
1705
1706 END IF;
1707
1708 IF (l_group_attr.EXISTS(g_fob_code_idx)) THEN
1709
1710 IF l_debug_on THEN
1711 WSH_DEBUG_SV.logmsg(l_module_name,'Adding to FOB Cache');
1712 END IF;
1713
1714 IF ( fob_code <> g_val_absent) THEN
1715
1716 key_low := get_key_for_char( p_fixed_key => fixed_key,
1717 p_char => fob_code);
1718
1719 add_to_non_range_cache( p_cache => g_fob_cache,
1720 p_fixed_key => key_low,
1721 p_rule_id => curr_rule_id,
1722 x_return_status => x_return_status );
1723
1724 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1725 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1726 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1727 END IF;
1728 END IF;
1729
1730 ELSE
1731 add_to_non_range_cache( p_cache => g_fob_cache,
1732 p_null_key => null_key,
1736 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1733 p_rule_id => curr_rule_id,
1734 x_return_status => x_return_status);
1735
1737 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1738 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1739 END IF;
1740 END IF;
1741 END IF;
1742 END IF;
1743
1744 EXIT WHEN itr = rule_tab.LAST;
1745 itr := rule_tab.NEXT(itr);
1746 END LOOP;
1747
1748 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1749 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1750 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1751 END IF;
1752 END IF;
1753
1754 END IF;
1755
1756 IF l_debug_on THEN
1757 WSH_DEBUG_SV.POP (l_module_name);
1758 END IF;
1759
1760 EXCEPTION
1761 WHEN FTE_CS_ERROR_INVALID_RANGE THEN
1762
1763 IF c_get_rule_restrictions%ISOPEN THEN
1764 CLOSE c_get_rule_restrictions;
1765 END IF;
1766 --
1767 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1768 --
1769 IF l_debug_on THEN
1770 WSH_DEBUG_SV.pop(l_module_name,'Attribute Range has either FROM or TO Only (Range is Invalid');
1771 END IF;
1772
1773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1774
1775 IF c_get_rule_restrictions%ISOPEN THEN
1776 CLOSE c_get_rule_restrictions;
1777 END IF;
1778
1779 --
1780 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1781 --
1782 IF l_debug_on THEN
1783 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1784 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1785 END IF;
1786 --
1787
1788 WHEN others THEN
1789
1790 IF c_get_rule_restrictions%ISOPEN THEN
1791 CLOSE c_get_rule_restrictions;
1792 END IF;
1793
1794 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.ADD_TO_RANGE_CACHE');
1795 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1796 --
1797 IF l_debug_on THEN
1798 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1799 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1800 END IF;
1801
1802 END build_cache;
1803
1804
1805 --***************************************************************************--
1806 --========================================================================
1807 -- PROCEDURE : search_exact_cache_for_null PRIVATE
1808 --
1809 -- PARAMETERS: p_cache EXACT_MATCH_CACHE to be searched
1810 -- p_fixed_key Fixed key to be used.
1811 -- x_result_tab Matching Rules.
1812 -- x_return_status Return Status
1813 --
1814 -- COMMENT : This API returns all the rules which have null as matching attributes
1815 --
1816 --***************************************************************************--
1817 PROCEDURE search_exact_cache_for_null( p_cache IN EXACT_MATCH_CACHE,
1818 p_fixed_key IN VARCHAR2,
1819 x_result_tab OUT NOCOPY rule_match_tab,
1820 x_return_status OUT NOCOPY VARCHAR2)
1821 IS
1822 l_null_key VARCHAR2(32767);
1823 l_rule_tab exact_match_tab;
1824 itr NUMBER;
1825
1826 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1827 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_exact_cache_for_null';
1828
1829 BEGIN
1830
1831 IF l_debug_on THEN
1832 wsh_debug_sv.push (l_module_name);
1833 END IF;
1834
1835 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1836
1837 l_null_key := get_key_for_null ( p_fixed_key) ;
1838
1839 IF (p_cache.EXISTS(l_null_key)) THEN
1840 l_rule_tab := p_cache(l_null_key);
1841 itr := l_rule_tab.FIRST;
1842
1843 IF (itr IS NOT NULL) THEN
1844 LOOP
1845 IF (x_result_tab IS NULL) THEN
1846 x_result_tab:= rule_match_tab(l_rule_tab(itr));
1847 ELSE
1848 x_result_tab.EXTEND;
1849 x_result_tab(x_result_tab.COUNT):= l_rule_tab(itr);
1850 END IF;
1851 EXIT WHEN itr = l_rule_tab.LAST;
1852 itr := l_rule_tab.NEXT(itr);
1853 END LOOP;
1854 END IF;
1855 END IF;
1856
1857 IF l_debug_on THEN
1858 WSH_DEBUG_SV.POP (l_module_name);
1859 END IF;
1860
1861 EXCEPTION
1862 WHEN others THEN
1863
1864 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_exact_cache_for_null');
1865 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1866
1867 IF l_debug_on THEN
1868 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1869 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1870 END IF;
1871 --
1872 END search_exact_cache_for_null;
1873
1874
1875 --***************************************************************************--
1876 --========================================================================
1877 -- PROCEDURE : search_range_cache_for_null PRIVATE
1878 --
1879 -- PARAMETERS: p_cache RANGE_MATCH_CACHE to be searched
1880 -- p_fixed_key Fixed key to be used.
1881 -- x_result_tab Matching Rules (This will be a non index by variable)
1882 -- x_return_status Return Status
1883 --
1884 -- COMMENT : This API returns all the rules which have null as matching attributes
1885 --
1886 --***************************************************************************--
1890 x_return_status OUT NOCOPY VARCHAR2)
1887 PROCEDURE search_range_cache_for_null( p_cache IN RANGE_MATCH_CACHE,
1888 p_fixed_key IN VARCHAR2,
1889 x_result_tab OUT NOCOPY rule_match_tab,
1891 IS
1892
1893 l_null_key VARCHAR2(32767);
1894 l_rule_tab range_match_tab;
1895 itr NUMBER;
1896
1897 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1898 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_range_cache_for_null';
1899
1900 BEGIN
1901
1902 IF l_debug_on THEN
1903 wsh_debug_sv.push (l_module_name);
1904 END IF;
1905
1906 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1907
1908 l_null_key := get_key_for_null(p_fixed_key);
1909
1910 IF (p_cache.EXISTS(l_null_key)) THEN
1911
1912 l_rule_tab := p_cache(l_null_key);
1913
1914 itr := l_rule_tab.FIRST;
1915
1916 IF (itr IS NOT NULL) THEN
1917 LOOP
1918 -- Type casting needs to be done.
1919 IF (x_result_tab IS NULL) THEN
1920
1921 x_result_tab:= rule_match_tab(l_rule_tab(itr).rule_id);
1922
1923 ELSE
1924 x_result_tab.EXTEND;
1925 x_result_tab(x_result_tab.COUNT):= l_rule_tab(itr).rule_id;
1926
1927 END IF;
1928 EXIT WHEN itr = l_rule_tab.LAST;
1929 itr := l_rule_tab.NEXT(itr);
1930 END LOOP;
1931 END IF;
1932 END IF;
1933
1934 IF l_debug_on THEN
1935 WSH_DEBUG_SV.POP (l_module_name);
1936 END IF;
1937
1938 EXCEPTION
1939 WHEN others THEN
1940
1941 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_range_cache_for_null');
1942 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1943 IF l_debug_on THEN
1944 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1945 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1946 END IF;
1947 --
1948 END search_range_cache_for_null;
1949
1950
1951 --***************************************************************************--
1952 --========================================================================
1953 -- PROCEDURE : get_higher_breaks PRIVATE
1954 --
1955 -- PARAMETERS: p_cache Range Cache to be searched
1956 -- p_start_idx Starting index for search.
1957 -- p_fixed_key Fixed key to be used.
1958 -- p_null_key Null key.
1959 -- x_rule_tab Rules having higher breaks.
1960 -- x_return_status Return Status
1961 --
1962 -- COMMENT : This API returns all the rules with have upper range break more
1963 -- than the attribute value.
1964 --***************************************************************************--
1965 PROCEDURE get_higher_breaks( p_cache IN RANGE_MATCH_CACHE,
1966 p_start_idx IN VARCHAR2,
1967 p_fixed_key IN VARCHAR2,
1968 p_null_key IN VARCHAR2,
1969 x_rule_tab OUT NOCOPY RULE_MATCH_TAB,
1970 x_return_status OUT NOCOPY VARCHAR2)
1971 IS
1972 l_curr_idx VARCHAR2(32767);
1973 l_rule_tab range_match_tab;
1974 itr NUMBER;
1975 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1976 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'GET_HIGHER_BREAKS';
1977 BEGIN
1978 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1979
1980 IF l_debug_on THEN
1981 wsh_debug_sv.push(l_module_name);
1982 END IF;
1983
1984 l_curr_idx := p_cache.NEXT(p_start_idx);
1985
1986 --
1987 -- All the rules to be searched should have the same fixed key.
1988 -- Null key attributes should not be considered.
1989 --
1990
1991 WHILE (l_curr_idx IS NOT NULL AND l_curr_idx <> p_null_key AND INSTR(l_curr_idx,p_fixed_key,1,1) = 1)
1992 LOOP
1993
1994 l_rule_tab := p_cache(l_curr_idx);
1995 itr := l_rule_tab.FIRST;
1996
1997 IF (itr IS NOT NULL) THEN
1998 LOOP
1999 IF (l_rule_tab(itr).limit=g_high_range_break) THEN
2000
2001 IF (x_rule_tab IS NULL) THEN
2002 x_rule_tab := rule_match_tab(l_rule_tab(itr).rule_id);
2003 ELSE
2004 x_rule_tab.EXTEND;
2005 x_rule_tab(x_rule_tab.COUNT) := l_rule_tab(itr).rule_id;
2006 END IF;
2007 END IF;
2008
2009 EXIT WHEN itr = l_rule_tab.LAST;
2010 itr := l_rule_tab.NEXT(itr);
2011 END LOOP;
2012
2013 END IF;
2014
2015 l_curr_idx := p_cache.NEXT(l_curr_idx);
2016 END LOOP;
2017
2018 IF l_debug_on THEN
2019 WSH_DEBUG_SV.POP(l_module_name);
2020 END IF;
2021
2022 EXCEPTION
2023 WHEN others THEN
2024
2025 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_higher_breaks');
2026 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2027
2028 IF l_debug_on THEN
2029 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2030 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2031 END IF;
2032 --
2033 END get_higher_breaks;
2034
2035
2036 --***************************************************************************--
2037 --========================================================================
2038 -- PROCEDURE : get_lower_breaks PRIVATE
2039 --
2040 -- PARAMETERS: p_cache Range Cache to be searched
2041 -- p_start_idx Starting index for search.
2042 -- p_fixed_key Fixed key to be used.
2043 -- p_null_key Null key.
2044 -- x_rule_tab Rules with lower breaks.
2045 -- x_return_status Return Status
2046 --
2047 -- COMMENT : This API returns all the rules with have lower break less
2051 PROCEDURE get_lower_breaks( p_cache IN RANGE_MATCH_CACHE,
2048 -- than the attribute value.
2049 --
2050 --***************************************************************************--
2052 p_start_idx IN VARCHAR2,
2053 p_null_key IN VARCHAR2,
2054 p_fixed_key IN VARCHAR2,
2055 x_rule_tab OUT NOCOPY RULE_MATCH_TAB,
2056 x_return_status OUT NOCOPY VARCHAR2)
2057 IS
2058
2059 l_curr_idx VARCHAR2(32767);
2060 l_rule_tab range_match_tab;
2061 itr NUMBER;
2062
2063 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2064 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'GET_LOWER_BREAKS';
2065
2066 BEGIN
2067
2068 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2069
2070 IF l_debug_on THEN
2071 wsh_debug_sv.push (l_module_name);
2072 END IF;
2073
2074 l_curr_idx := p_cache.PRIOR(p_start_idx);
2075
2076 WHILE (l_curr_idx IS NOT NULL AND l_curr_idx <> p_null_key AND INSTR(l_curr_idx,p_fixed_key,1,1) = 1)
2077 LOOP
2078 l_rule_tab := p_cache(l_curr_idx);
2079 itr := l_rule_tab.FIRST;
2080
2081 IF (itr IS NOT NULL) THEN
2082 LOOP
2083
2084 IF (l_rule_tab(itr).limit=g_low_range_break) THEN
2085 IF (x_rule_tab IS NULL) THEN
2086 x_rule_tab := rule_match_tab(l_rule_tab(itr).rule_id);
2087 ELSE
2088 x_rule_tab.EXTEND;
2089 x_rule_tab(x_rule_tab.COUNT) := l_rule_tab(itr).rule_id;
2090 END IF;
2091 END IF;
2092
2093 EXIT WHEN itr = l_rule_tab.LAST;
2094 itr := l_rule_tab.NEXT(itr);
2095 END LOOP;
2096 END IF;
2097
2098 -- Check this condition.
2099 l_curr_idx := p_cache.PRIOR(l_curr_idx);
2100 END LOOP;
2101
2102 IF l_debug_on THEN
2103 WSH_DEBUG_SV.POP (l_module_name);
2104 END IF;
2105 EXCEPTION
2106 WHEN others THEN
2107 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_lower_breaks');
2108 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2109
2110 IF l_debug_on THEN
2111 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2112 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2113 END IF;
2114
2115 END get_lower_breaks;
2116
2117 --***************************************************************************--
2118 --========================================================================
2119 -- PROCEDURE : search_exact_cache PRIVATE
2120 --
2121 -- PARAMETERS: p_cache Exact Cache to be searched
2122 -- p_fixed_key Fixed key to be used.
2123 -- p_attribute Attribute value
2124 -- p_search_null This flag determines whether
2125 -- NULL values should be searched or not
2126 -- x_result_tab Matching Rules
2127 -- x_return_status Return Status
2128 --
2129 -- COMMENT : This API returns all the rules which exactly match the attribute
2130 --
2131 --***************************************************************************--
2132 PROCEDURE search_exact_cache( p_cache IN EXACT_MATCH_CACHE,
2133 p_fixed_key IN VARCHAR2,
2134 p_attribute_key IN VARCHAR2,
2135 p_search_null IN BOOLEAN,
2136 x_result_tab OUT NOCOPY rule_match_tab,
2137 x_return_status OUT NOCOPY VARCHAR2)
2138
2139 IS
2140 l_rule_tab EXACT_MATCH_TAB;
2141 itr NUMBER;
2142
2143 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2144 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_exact_cache';
2145 BEGIN
2146
2147 IF l_debug_on THEN
2148 wsh_debug_sv.push (l_module_name);
2149 END IF;
2150
2151 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2152
2153 IF (p_search_null) THEN
2154
2155 search_exact_cache_for_null( p_cache => p_cache,
2156 p_fixed_key => p_fixed_key,
2157 x_result_tab => x_result_tab,
2158 x_return_status=> x_return_status);
2159
2160 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2161 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2162 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2163 END IF;
2164 END IF;
2165 END IF;
2166
2167 IF (p_cache.EXISTS(p_attribute_key)) THEN
2168 l_rule_tab := p_cache(p_attribute_key);
2169 itr := l_rule_tab.FIRST;
2170 IF (itr is NOT NULL) THEN
2171 LOOP
2172 IF (x_result_tab IS NULL) THEN
2173 x_result_tab:= rule_match_tab(l_rule_tab(itr));
2174 ELSE
2175 x_result_tab.EXTEND;
2176 x_result_tab(x_result_tab.COUNT) :=l_rule_tab(itr);
2177 END IF;
2178 EXIT when itr = l_rule_tab.LAST;
2179 itr := l_rule_tab.NEXT(itr);
2180 END LOOP;
2181 END IF;
2182 END IF;
2183
2184 IF l_debug_on THEN
2185 WSH_DEBUG_SV.POP (l_module_name);
2186 END IF;
2187
2188 EXCEPTION
2189 WHEN others THEN
2190
2191 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_exact_cache');
2192 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2193
2194 IF l_debug_on THEN
2195 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2196 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2197 END IF;
2198 --
2199 END search_exact_cache;
2200
2201 --***************************************************************************--
2202 --========================================================================
2203 -- PROCEDURE : search_range_cache PRIVATE
2204 --
2208 -- p_search_null This flag determines whether
2205 -- PARAMETERS: p_cache Range Cache to be searched
2206 -- p_fixed_key Fixed key to be used.
2207 -- p_attribute Attribute value
2209 -- NULL values should be searched or not
2210 -- x_result_tab Matching Rules
2211 -- x_return_status Return Status
2212 --
2213 -- COMMENT : This API returns all the rules which match the attribute in the range
2214 -- cache.
2215 --***************************************************************************--
2216 PROCEDURE search_range_cache( p_cache IN RANGE_MATCH_CACHE,
2217 p_fixed_key IN VARCHAR2,
2218 p_attribute_key IN VARCHAR2,
2219 p_match_upper_limit IN BOOLEAN,
2220 p_search_null IN BOOLEAN,
2221 x_result_tab OUT NOCOPY rule_match_tab,
2222 x_return_status OUT NOCOPY VARCHAR2)
2223 IS
2224
2225 check_overlap BOOLEAN := FALSE;
2226
2227 l_prev_idx VARCHAR2(32767);
2228 l_next_idx VARCHAR2(32767);
2229 l_unique_rule BOOLEAN;
2230 l_rule_id FTE_SEL_RULE_RESTRICTIONS.rule_id%type;
2231 l_rule_tab range_match_tab;
2232 l_prev_tab range_match_tab;
2233 l_next_tab range_match_tab;
2234 l_prev_rec range_match_rule_rec;
2235 l_next_rec range_match_rule_rec;
2236
2237 itr NUMBER;
2238 results_itr NUMBER;
2239 l_null_key VARCHAR2(32767);
2240
2241 lower_breaks rule_match_tab;
2242 higher_breaks rule_match_tab;
2243 match_rules rule_match_tab;
2244
2245 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2246 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_range_cache';
2247
2248 BEGIN
2249
2250 IF l_debug_on THEN
2251 wsh_debug_sv.push (l_module_name);
2252 END IF;
2253
2254 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2255 l_unique_rule := true;
2256 IF (p_search_null) THEN
2257
2258 search_range_cache_for_null( p_cache => p_cache,
2259 p_fixed_key => p_fixed_key,
2260 x_result_tab => x_result_tab,
2261 x_return_status=> x_return_status);
2262
2263 --Done with search range cache for null
2264
2265 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2266 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2267 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2268 END IF;
2269 END IF;
2270 END IF;
2271
2272 --
2273 -- Checking the exact match. The exact match. In case of rules we have
2274 -- Range defined >= and <
2275 -- In case of exact match we should check for only >= . ie lower range.
2276 --
2277 -- For postal codes and transit time the range is defined as <= .
2278 -- In case of .EXISTS we should check for higher range break.
2279 --
2280
2281 IF (p_cache.EXISTS(p_attribute_key)) THEN
2282 l_rule_tab := p_cache(p_attribute_key);
2283 itr := l_rule_tab.FIRST;
2284
2285 IF (itr is NOT NULL) THEN
2286 LOOP
2287 IF (l_rule_tab(itr).limit = g_low_range_break) OR
2288 (l_rule_tab(itr).limit = g_high_range_break AND p_match_upper_limit)
2289 THEN
2290 l_rule_id := l_rule_tab(itr).rule_id;
2291 IF (x_result_tab IS NULL) THEN
2292 x_result_tab:= rule_match_tab(l_rule_id);
2293 ELSE
2294 results_itr := x_result_tab.FIRST;
2295 IF results_itr IS NOT NULL THEN
2296 LOOP
2297 IF x_result_tab(results_itr) = l_rule_id THEN
2298 l_unique_rule := false;
2299 EXIT;
2300 END IF;
2301 EXIT WHEN results_itr = x_result_tab.LAST;
2302 results_itr := x_result_tab.NEXT(results_itr);
2303 END LOOP;
2304 END IF;
2305 IF l_unique_rule THEN
2306 x_result_tab.EXTEND;
2307 x_result_tab(x_result_tab.COUNT) :=l_rule_tab(itr).rule_id;
2308 END IF;
2309 END IF;
2310 --
2311 --Even if for 1 record in table overlap flag is 'Y'.
2312 --We need to check for overlaps
2313 IF (l_rule_tab(itr).overlap_flag = 'Y') THEN
2314 check_overlap:= TRUE;
2315 END IF;
2316 END IF;
2317 EXIT when itr = l_rule_tab.LAST;
2318 itr := l_rule_tab.NEXT(itr);
2319 END LOOP;
2320
2321 --
2322 -- If we are here,then above code has been executed atleast once.
2323 -- If check overlap flag is FALSE,we can return.
2324 -- Attributes exists and none of them has overlap.
2325 --
2326
2327 IF (NOT check_overlap) THEN
2328
2329 IF l_debug_on THEN
2330 WSH_DEBUG_SV.POP (l_module_name);
2331 END IF;
2332 RETURN;
2333
2334 END IF;
2335 END IF;
2336 END IF;
2337
2338 l_prev_idx := p_cache.prior(p_attribute_key);
2339 l_next_idx := p_cache.next(p_attribute_key);
2340 l_null_key := get_key_for_null(p_fixed_key) ;
2341
2342 --
2343 -- Previous and Next Index will be null
2344 --
2345
2346 IF l_prev_idx IS NULL OR l_next_idx IS NULL
2347 OR (INSTR(l_prev_idx,p_fixed_key,1,1) <> 1) OR (l_prev_idx = l_null_key)
2348 OR (INSTR(l_next_idx,p_fixed_key,1,1) <> 1) OR (l_next_idx = l_null_key)
2349 THEN
2350 IF l_debug_on THEN
2351 WSH_DEBUG_SV.POP (l_module_name);
2352 END IF;
2353 RETURN;
2354 END IF;
2355
2356
2357 --
2358 -- Get the previous and next tabs, We are sure that both of them are valid.
2359 -- a) Null entries will not be made
2360 -- b) The indexes are valid.
2364 -- b) Next tab has 1 record.
2361 --
2362 -- Here handle the likely case when
2363 -- a) The prev tab has 1 record.
2365 -- c) Both are closed and overlap flag is 'N'
2366 -- If such a case is found return back.
2367
2368 l_prev_tab := p_cache(l_prev_idx);
2369 l_next_tab := p_cache(l_next_idx);
2370
2371 IF (l_prev_tab.COUNT = 1) AND (l_next_tab.COUNT = 1) THEN
2372
2373 l_prev_rec := l_prev_tab(l_prev_tab.FIRST);
2374 l_next_rec := l_next_tab(l_next_tab.FIRST);
2375
2376 IF (l_prev_rec.rule_id = l_next_rec.rule_id) AND
2377 (l_prev_rec.limit = g_low_range_break) AND
2378 (l_next_rec.limit = g_high_range_break) THEN
2379
2380 IF (x_result_tab IS NULL) THEN
2381 x_result_tab:= rule_match_tab(l_prev_rec.rule_id);
2382 ELSE
2383 x_result_tab.EXTEND;
2384 x_result_tab(x_result_tab.COUNT):=l_prev_rec.rule_id;
2385 END IF;
2386
2387 --
2388 -- If overlap flag at the next rec and the previous record is
2389 -- 'N' we can exit.
2390 --
2391
2392 IF (l_prev_rec.overlap_flag='N' AND l_next_rec.overlap_flag = 'N') THEN
2393 IF l_debug_on THEN
2394 WSH_DEBUG_SV.POP (l_module_name);
2395 END IF;
2396 RETURN;
2397 END IF;
2398 END IF;
2399 END IF;
2400 --
2401 -- If we reach here then it means that match has not been found or overlap flag is Y;
2402 --
2403 --
2404 -- Algorithm here is -
2405 -- 1. Get all previous rules with limit = L
2406 -- 2. Get all next rules with limit = U.
2407 -- 3. Take intersection of rules returned in above tables.
2408 -- 4. All the rules returned have to be sent back.
2409 --
2410 get_lower_breaks( p_cache => p_cache,
2411 p_start_idx => p_attribute_key,
2412 p_fixed_key => p_fixed_key,
2413 p_null_key => l_null_key,
2414 x_rule_tab => lower_breaks,
2415 x_return_status => x_return_status);
2416
2417 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2418 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2419 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2420 END IF;
2421 END IF;
2422
2423 IF (lower_breaks IS NULL) THEN
2424 IF l_debug_on THEN
2425 WSH_DEBUG_SV.POP (l_module_name);
2426 END IF;
2427 RETURN;
2428 END IF;
2429
2430 get_higher_breaks( p_cache => p_cache,
2431 p_start_idx => p_attribute_key,
2432 p_fixed_key => p_fixed_key,
2433 p_null_key => l_null_key,
2434 x_rule_tab => higher_breaks,
2435 x_return_status => x_return_status);
2436
2437 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2438 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2439 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2440 END IF;
2441 END IF;
2442
2443 IF (higher_breaks IS NULL) THEN
2444 IF l_debug_on THEN
2445 WSH_DEBUG_SV.POP (l_module_name);
2446 END IF;
2447 RETURN;
2448 END IF;
2449
2450 match_rules := lower_breaks MULTISET INTERSECT higher_breaks;
2451
2452 IF (match_rules IS NOT NULL) THEN
2453
2454 IF (x_result_tab IS NULL ) THEN
2455 x_result_tab := match_rules;
2456 ELSE
2457 x_result_tab := x_result_tab MULTISET UNION DISTINCT match_rules;
2458 END IF;
2459 END IF;
2460
2461 IF l_debug_on THEN
2462 WSH_DEBUG_SV.POP (l_module_name);
2463 END IF;
2464
2465 EXCEPTION
2466 WHEN others THEN
2467
2468 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_range_cache');
2469 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2470
2471 IF l_debug_on THEN
2472 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2473 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2474 END IF;
2475 --
2476 END search_range_cache;
2477
2478 --***************************************************************************--
2479 --========================================================================
2480 -- PROCEDURE : get_all_rules_in_cache PRIVATE
2481 --
2482 -- PARAMETERS: p_cache Range Cache to be searched
2483 -- x_result_tab Matching Rules
2484 -- x_return_status Return Status
2485 --
2486 -- COMMENT : This API returns all the rules which match the attribute in the range
2487 -- cache.
2488 --***************************************************************************--
2489 PROCEDURE get_all_rules_in_cache( p_cache IN RANGE_MATCH_CACHE,
2490 x_result_tab OUT NOCOPY rule_match_tab,
2491 x_return_status OUT NOCOPY VARCHAR2)
2492 IS
2493
2494 l_rule_tab range_match_tab;
2495 itr NUMBER;
2496 l_rule_tab_itr VARCHAR2(32767);
2497 match_rules rule_match_tab;
2498
2499 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2500 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_all_rules_in_cache';
2501
2502 BEGIN
2503
2504 IF l_debug_on THEN
2505 wsh_debug_sv.push (l_module_name);
2506 END IF;
2507
2508 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2509
2510 l_rule_tab_itr := p_cache.FIRST;
2511
2512 IF p_cache.FIRST IS NOT NULL THEN
2513 LOOP
2514 l_rule_tab := p_cache(l_rule_tab_itr);
2515 itr := l_rule_tab.FIRST;
2516
2517 IF (itr is NOT NULL) THEN
2518 LOOP
2519 IF (x_result_tab IS NULL) THEN
2520 x_result_tab:= rule_match_tab(l_rule_tab(itr).rule_id);
2521 ELSE
2525
2522 x_result_tab.EXTEND;
2523 x_result_tab(x_result_tab.COUNT) :=l_rule_tab(itr).rule_id;
2524 END IF;
2526 EXIT when itr = l_rule_tab.LAST;
2527 itr := l_rule_tab.NEXT(itr);
2528 END LOOP;
2529 END IF;
2530
2531 EXIT when l_rule_tab_itr = p_cache.LAST;
2532 l_rule_tab_itr := p_cache.NEXT(l_rule_tab_itr);
2533
2534 END LOOP;
2535 END IF;
2536
2537 IF l_debug_on THEN
2538 WSH_DEBUG_SV.POP (l_module_name);
2539 END IF;
2540
2541 EXCEPTION
2542 WHEN others THEN
2543
2544 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_all_rules_in_cache');
2545 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2546
2547 IF l_debug_on THEN
2548 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2549 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2550 END IF;
2551 --
2552 END get_all_rules_in_cache;
2553
2554 --***************************************************************************--
2555 --========================================================================
2556 -- PROCEDURE : search_min_high_transit_time PRIVATE
2557 --
2558 -- PARAMETERS: p_result_tab Rules to be evaluated to find the rule with minimum
2559 -- high transit time.
2560 -- x_rule_id Rule with minimum maximum transit time
2561 -- x_return_status Return Status
2562 --
2563 -- COMMENT : This API returns the rule with the minimum high transit time from
2564 -- a given set of rules
2565 --
2566 --***************************************************************************--
2567 PROCEDURE search_min_high_transit_time( p_result_tab IN RULE_MATCH_TAB,
2568 x_rule_id OUT NOCOPY NUMBER,
2569 x_return_status OUT NOCOPY VARCHAR2)
2570 IS
2571 l_rule_str VARCHAR2(4000);
2572 sql_string VARCHAR2(4000);
2573 itr NUMBER;
2574
2575 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2576 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_min_high_transit_time';
2577
2578 BEGIN
2579 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2580 itr := p_result_tab.FIRST;
2581
2582 IF (itr IS NOT NULL) THEN
2583 LOOP
2584 IF (l_rule_str IS NULL) THEN
2585 l_rule_str := TO_CHAR(p_result_tab(itr));
2586 ELSE
2587 l_rule_str := l_rule_str ||','||TO_CHAR(p_result_tab(itr));
2588 END IF;
2589
2590 EXIT WHEN itr = p_result_tab.LAST;
2591 itr := p_result_tab.NEXT(itr);
2592 END LOOP;
2593 END IF;
2594
2595 sql_string := 'SELECT rule_id FROM '
2596 || ' (SELECT rule_id'
2597 || ' FROM FTE_SEL_RULE_RESTRICTIONS '
2598 || ' WHERE attribute_name=''TRANSIT_TIME'' '--AND ROWNUM=1'
2599 || ' AND rule_id IN ('
2600 || l_rule_str
2601 || ')'
2602 || ' ORDER BY attribute_value_to_number)'
2603 || ' WHERE ROWNUM=1';
2604
2605 IF l_debug_on THEN
2606 WSH_DEBUG_SV.logmsg(l_module_name,'Rule String is '||l_rule_str);
2607 WSH_DEBUG_SV.logmsg(l_module_name,'SQL String is '||sql_string);
2608 END IF;
2609
2610 EXECUTE IMMEDIATE sql_string INTO x_rule_id;
2611
2612 IF l_debug_on THEN
2613 WSH_DEBUG_SV.logmsg(l_module_name,'Rule Id is '||x_rule_id);
2614 wsh_debug_sv.pop(l_module_name);
2615 END IF;
2616
2617 EXCEPTION
2618 WHEN others THEN
2619 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_min_high_transit_time');
2620 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2621
2622 IF l_debug_on THEN
2623 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2624 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2625 END IF;
2626 END search_min_high_transit_time;
2627
2628
2629 --***************************************************************************--
2630 --========================================================================
2631 -- PROCEDURE : find_rule_for_key PRIVATE
2632 --
2633 -- PARAMETERS: p_fixed_key Fixed key to be used for search
2634 -- Group_id-FromRegion-ToRegion Combination
2635 --
2636 -- p_group_id Group id
2637 -- p_from_postal_flag From postal flag is true,if from postal code
2638 -- has to be searched
2639 -- p_to_postal_flag To postal flag is true , if to postal code has to
2640 -- be searched.
2641 -- p_from_postal_code From postal code of the entity.
2642 -- p_to_postal_code To postal code of the entity
2643 -- p_weight Weight of the entity.
2644 -- p_volume Volume of the entity.
2645 -- p_transit_time Transit time associated with the entity
2646 -- p_fob_code FOB code
2647 -- x_rule_id Matching rule id.
2648 -- x_return_status Return status
2649 --
2650 -- COMMENT : The API finds the rule that matches the attributes.
2651 -- g_rule_not_found is returned if no match is found.
2652 --***************************************************************************--
2653 PROCEDURE find_rule_for_key( p_fixed_key IN VARCHAR2,
2654 p_group_id IN NUMBER,
2655 p_from_postal_flag IN BOOLEAN,
2656 p_to_postal_flag IN BOOLEAN,
2657 p_from_postal_code IN VARCHAR2,
2658 p_to_postal_code IN VARCHAR2,
2659 p_weight IN NUMBER,
2660 p_volume IN NUMBER,
2661 p_transit_time IN NUMBER,
2662 p_fob_code IN VARCHAR2,
2663 x_rule_id OUT NOCOPY NUMBER,
2664 x_return_status OUT NOCOPY VARCHAR2)
2665 IS
2666
2667
2671 WHERE group_id = p_group_id;
2668 CURSOR c_get_group_name IS
2669 SELECT name
2670 FROM FTE_SEL_GROUPS
2672
2673 l_result_set RULE_MATCH_TAB ;
2674 l_temp_set RULE_MATCH_TAB ;
2675 l_attribute_tab WSH_UTIL_CORE.ID_TAB_TYPE;
2676 l_attribute_key VARCHAR2(32767);
2677 itr NUMBER;
2678
2679 l_group_name VARCHAR2(32767);
2680
2681 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2682 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'FIND_RULE_FOR_KEY';
2683
2684 OVERLAPPING_RULES_EXIST EXCEPTION;
2685 BEGIN
2686
2687 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2688
2689 IF l_debug_on THEN
2690 wsh_debug_sv.push(l_module_name);
2691 END IF;
2692
2693 x_rule_id := g_rule_not_found;
2694
2695 l_attribute_tab := g_groups_cache(p_group_id).attribute_tab;
2696 --
2697 -- Method : Even if attribute is null,we need to get all the rules where the attribute is null.
2698 -- Exceptions : Postal codes.In postal codes we need to check whether they are present or not.
2699 -- Check for postal codes as Null has been taken care of before.
2700 --
2701 IF (l_attribute_tab.EXISTS(g_fob_code_idx)) THEN
2702
2703 IF (p_fob_code IS NOT NULL) THEN
2704
2705 --
2706 --Compare with the old engine.
2707 --
2708
2709 IF l_debug_on THEN
2710 wsh_debug_sv.logmsg(l_module_name,'Searching FOB (Not null) ');
2711 END IF;
2712
2713
2714 l_attribute_key := get_key_for_char( p_fixed_key => p_fixed_key ,
2715 p_char => p_fob_code);
2716
2717 search_exact_cache( p_cache => g_fob_cache,
2718 p_fixed_key => p_fixed_key,
2719 p_attribute_key => l_attribute_key,
2720 p_search_null => TRUE,
2721 x_result_tab => l_temp_set,
2722 x_return_status => x_return_status);
2723
2724 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2725 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2726 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2727 END IF;
2728 END IF;
2729 ELSE
2730
2731 IF l_debug_on THEN
2732 wsh_debug_sv.logmsg(l_module_name,'Searching FOB (Null) ');
2733 END IF;
2734
2735 search_exact_cache_for_null( p_cache => g_fob_cache,
2736 p_fixed_key => p_fixed_key,
2737 x_result_tab => l_temp_set,
2738 x_return_status => x_return_status);
2739
2740 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2741 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2742 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2743 END IF;
2744 END IF;
2745 END IF;
2746
2747 IF l_debug_on THEN
2748 IF l_temp_set IS NOT NULL THEN
2749 itr := l_temp_set.FIRST;
2750 LOOP
2751 wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
2752 EXIT WHEN itr = l_temp_set.LAST;
2753 itr := l_temp_set.NEXT(itr);
2754 END LOOP;
2755 END IF;
2756 END IF;
2757
2758 IF (l_temp_set IS NULL) THEN
2759 --No valid rule found
2760 IF l_debug_on THEN
2761 wsh_debug_sv.pop(l_module_name);
2762 END IF;
2763 RETURN;
2764 ELSE
2765 -- This is the first attribute. Here no need to use intersect.
2766 l_result_set := l_temp_set;
2767 END IF;
2768
2769 IF l_debug_on THEN
2770 IF l_result_set IS NOT NULL THEN
2771 itr := l_result_set.FIRST;
2772 LOOP
2773 wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
2774 EXIT WHEN itr = l_result_set.LAST;
2775 itr := l_result_set.NEXT(itr);
2776 END LOOP;
2777 END IF;
2778 END IF;
2779
2780 END IF; -- IF (l_attribute_tab.EXISTS(g_fob_code_idx)) THEN
2781
2782 --
2783 -- Only if weight exists in the group,we will be searching in the
2784 -- cache.
2785 --
2786 IF (l_attribute_tab.EXISTS(g_wt_idx)) THEN
2787
2788 IF (p_weight IS NOT NULL) THEN
2789
2790
2791 IF l_debug_on THEN
2792 wsh_debug_sv.logmsg(l_module_name,'Searching Weight (Not Null) ');
2793 END IF;
2794
2795 l_attribute_key := get_key_for_num( p_fixed_key => p_fixed_key ,
2796 p_number => p_weight);
2797
2798 search_range_cache( p_cache => g_weight_cache,
2799 p_fixed_key => p_fixed_key,
2800 p_attribute_key => l_attribute_key,
2801 p_search_null => TRUE,
2802 p_match_upper_limit => FALSE,
2803 x_result_tab => l_temp_set,
2804 x_return_status => x_return_status);
2805
2806 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2807 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2808 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2809 END IF;
2810 END IF;
2811
2812 ELSE
2813
2814 IF l_debug_on THEN
2815 wsh_debug_sv.logmsg(l_module_name,'Searching Weight (Null) ');
2816 END IF;
2817
2818 search_range_cache_for_null( p_cache => g_weight_cache,
2819 p_fixed_key => p_fixed_key,
2820 x_result_tab => l_temp_set,
2821 x_return_status => x_return_status);
2822
2823 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2824 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2825 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2826 END IF;
2827 END IF;
2828
2829 END IF;
2830
2831 IF l_debug_on THEN
2832 IF l_temp_set IS NOT NULL THEN
2833 itr := l_temp_set.FIRST;
2834 LOOP
2838 END LOOP;
2835 wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
2836 EXIT WHEN itr = l_temp_set.LAST;
2837 itr := l_temp_set.NEXT(itr);
2839 END IF;
2840 END IF;
2841
2842 IF (l_temp_set IS NULL) THEN
2843 -- No valid rule is found.
2844 IF l_debug_on THEN
2845 wsh_debug_sv.pop(l_module_name);
2846 END IF;
2847 RETURN;
2848 ELSE
2849 IF (l_result_set IS NULL) THEN
2850 l_result_set := l_temp_set;
2851 ELSE
2852 l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
2853
2854 IF (l_result_set.COUNT = 0) THEN
2855 IF l_debug_on THEN
2856 wsh_debug_sv.pop(l_module_name);
2857 END IF;
2858 RETURN;
2859 END IF;
2860 END IF;
2861 END IF;
2862
2863 IF l_debug_on THEN
2864 IF l_result_set IS NOT NULL THEN
2865 itr := l_result_set.FIRST;
2866 LOOP
2867 wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
2868 EXIT WHEN itr = l_result_set.LAST;
2869 itr := l_result_set.NEXT(itr);
2870 END LOOP;
2871 END IF;
2872 END IF;
2873
2874 END IF; -- IF (l_attribute_tab.EXISTS(g_wt_idx)) THEN
2875
2876 --
2877 -- Only if volume exists in the group we will be searching for it.
2878 --
2879 IF (l_attribute_tab.EXISTS(g_vol_idx)) THEN
2880
2881 IF (p_volume IS NOT NULL) THEN
2882
2883 IF l_debug_on THEN
2884 wsh_debug_sv.logmsg(l_module_name,'Searching Volume (Not Null) ');
2885 END IF;
2886
2887 l_attribute_key := get_key_for_num( p_fixed_key => p_fixed_key ,
2888 p_number => p_volume);
2889
2890 search_range_cache( p_cache => g_volume_cache,
2891 p_fixed_key => p_fixed_key,
2892 p_attribute_key => l_attribute_key,
2893 p_match_upper_limit => FALSE,
2894 p_search_null => TRUE,
2895 x_result_tab => l_temp_set,
2896 x_return_status => x_return_status);
2897
2898 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2899 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2900 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2901 END IF;
2902 END IF;
2903 ELSE
2904
2905 IF l_debug_on THEN
2906 wsh_debug_sv.logmsg(l_module_name,'Searching Volume (Null) ');
2907 END IF;
2908
2909 search_range_cache_for_null( p_cache => g_volume_cache,
2910 p_fixed_key => p_fixed_key,
2911 x_result_tab => l_temp_set,
2912 x_return_status => x_return_status);
2913
2914 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2915 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2916 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2917 END IF;
2918 END IF;
2919 END IF;
2920
2921 IF l_debug_on THEN
2922 IF l_temp_set IS NOT NULL THEN
2923 itr := l_temp_set.FIRST;
2924 LOOP
2925 wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
2926 EXIT WHEN itr = l_temp_set.LAST;
2927 itr := l_temp_set.NEXT(itr);
2928 END LOOP;
2929 END IF;
2930 END IF;
2931
2932 IF (l_temp_set IS NULL) THEN
2933 -- No valid rule is found.
2934 IF l_debug_on THEN
2935 wsh_debug_sv.pop(l_module_name);
2936 END IF;
2937 RETURN;
2938 ELSE
2939 IF (l_result_set IS NULL) THEN
2940 l_result_set := l_temp_set;
2941 ELSE
2942 l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
2943
2944 IF (l_result_set.COUNT = 0 ) THEN
2945 IF l_debug_on THEN
2946 wsh_debug_sv.pop(l_module_name);
2947 END IF;
2948 RETURN;
2949 END IF;
2950 END IF;
2951 END IF;
2952
2953 IF l_debug_on THEN
2954 IF l_result_set IS NOT NULL THEN
2955 itr := l_result_set.FIRST;
2956 LOOP
2957 wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
2958 EXIT WHEN itr = l_result_set.LAST;
2959 itr := l_result_set.NEXT(itr);
2960 END LOOP;
2961 END IF;
2962 END IF;
2963
2964 END IF; -- IF (l_attribute_tab.EXISTS(g_vol_idx)) THEN
2965
2966 IF (l_attribute_tab.EXISTS(g_fr_post_idx)) THEN
2967
2968 IF (p_from_postal_flag) AND (p_from_postal_code IS NOT NULL) THEN
2969 --
2970 -- Do not search for NULL postal codes as that is handled in a
2971 -- different priority.
2972 --
2973 IF l_debug_on THEN
2974 wsh_debug_sv.logmsg(l_module_name,'Searching From Postal Code ');
2975 END IF;
2976
2977
2978 l_attribute_key := get_key_for_char( p_fixed_key => p_fixed_key,
2979 p_char => p_from_postal_code);
2980
2981 search_range_cache( p_cache => g_from_postal_cache,
2982 p_fixed_key => p_fixed_key,
2983 p_attribute_key => l_attribute_key,
2984 p_match_upper_limit=> TRUE,
2985 p_search_null => FALSE,
2986 x_result_tab => l_temp_set,
2987 x_return_status => x_return_status);
2988
2989 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2990 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2991 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2992 END IF;
2993 END IF;
2994
2995 ELSE
2996 --
2997 -- If postal code is null or flag is false then we will search the
2998 -- cache only for null values.
2999 --
3000
3001 IF l_debug_on THEN
3002 wsh_debug_sv.logmsg(l_module_name,'Searching From Postal Code(null) ');
3003 END IF;
3004
3005
3009 x_return_status => x_return_status);
3006 search_range_cache_for_null(p_cache => g_from_postal_cache,
3007 p_fixed_key => p_fixed_key,
3008 x_result_tab => l_temp_set,
3010
3011 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3012 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3013 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3014 END IF;
3015 END IF;
3016 END IF;
3017
3018 IF l_debug_on THEN
3019 IF l_temp_set IS NOT NULL THEN
3020 itr := l_temp_set.FIRST;
3021 LOOP
3022 wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
3023 EXIT WHEN itr = l_temp_set.LAST;
3024 itr := l_temp_set.NEXT(itr);
3025 END LOOP;
3026 END IF;
3027 END IF;
3028 --
3029 -- If no valid result set is retrieved we return back.
3030 --
3031 IF (l_temp_set IS NULL) THEN
3032 --No valid rule found
3033 IF l_debug_on THEN
3034 wsh_debug_sv.pop (l_module_name);
3035 END IF;
3036 RETURN;
3037 ELSE
3038 IF (l_result_set IS NULL) THEN
3039 l_result_set := l_temp_set;
3040 ELSE
3041
3042 l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
3043 IF (l_result_set.COUNT=0) THEN
3044 IF l_debug_on THEN
3045 wsh_debug_sv.pop (l_module_name);
3046 END IF;
3047 RETURN;
3048 END IF;
3049 END IF;
3050 END IF;
3051
3052 IF l_debug_on THEN
3053 IF l_result_set IS NOT NULL THEN
3054 itr := l_result_set.FIRST;
3055 LOOP
3056 wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
3057 EXIT WHEN itr = l_result_set.LAST;
3058 itr := l_result_set.NEXT(itr);
3059 END LOOP;
3060 END IF;
3061 END IF;
3062
3063 END IF;
3064
3065 --
3066 --Checking for to postal Code
3067 --
3068 IF (l_attribute_tab.EXISTS(g_to_post_idx)) THEN
3069
3070 IF (p_to_postal_flag) AND (p_to_postal_code IS NOT NULL) THEN
3071
3072 IF l_debug_on THEN
3073 wsh_debug_sv.logmsg(l_module_name,'Searching to Postal Code ');
3074 END IF;
3075
3076
3077 --
3078 -- Do not search for NULL postal codes as that is handled in a
3079 -- different priority.
3080 --
3081 l_attribute_key := get_key_for_char( p_fixed_key => p_fixed_key,
3082 p_char => p_to_postal_code);
3083
3084 search_range_cache(p_cache => g_to_postal_cache,
3085 p_fixed_key => p_fixed_key,
3086 p_attribute_key => l_attribute_key,
3087 p_match_upper_limit=> TRUE,
3088 p_search_null => FALSE,
3089 x_result_tab => l_temp_set,
3090 x_return_status => x_return_status);
3091
3092 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3093 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3094 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3095 END IF;
3096 END IF;
3097 ELSE
3098 --
3099 -- If postal code is null or flag is false then we will search the
3100 -- cache only for null values.
3101 --
3102 IF l_debug_on THEN
3103 wsh_debug_sv.logmsg(l_module_name,'Searching to Postal Code(null) ');
3104 END IF;
3105
3106 search_range_cache_for_null( p_cache => g_to_postal_cache,
3107 p_fixed_key => p_fixed_key,
3108 x_result_tab => l_temp_set,
3109 x_return_status => x_return_status);
3110
3111 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3112 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3113 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3114 END IF;
3115 END IF;
3116 END IF;
3117
3118 IF l_debug_on THEN
3119 IF l_temp_set IS NOT NULL THEN
3120 itr := l_temp_set.FIRST;
3121 LOOP
3122 wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
3123 EXIT WHEN itr = l_temp_set.LAST;
3124 itr := l_temp_set.NEXT(itr);
3125 END LOOP;
3126 END IF;
3127 END IF;
3128 --
3129 -- If no valid result set is retrieved we return back.
3130 --
3131 IF (l_temp_set IS NULL) THEN
3132 --No valid rule found
3133 IF l_debug_on THEN
3134 wsh_debug_sv.pop (l_module_name);
3135 END IF;
3136 RETURN;
3137 ELSE
3138
3139 IF (l_result_set IS NULL) THEN
3140 l_result_set := l_temp_set;
3141 ELSE
3142 l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
3143 IF (l_result_set.COUNT = 0) THEN
3144 IF l_debug_on THEN
3145 wsh_debug_sv.pop (l_module_name);
3146 END IF;
3147 RETURN;
3148 END IF;
3149 END IF;
3150 END IF;
3151
3152 IF l_debug_on THEN
3153 IF l_result_set IS NOT NULL THEN
3154 itr := l_result_set.FIRST;
3155 LOOP
3156 wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
3157 EXIT WHEN itr = l_result_set.LAST;
3158 itr := l_result_set.NEXT(itr);
3159 END LOOP;
3160 END IF;
3161 END IF;
3162
3163 END IF;
3164 --
3165 -- Search for transit time:
3166 -- If transit time is 0, null or less than 1, get the rule which has
3167 -- the minimum maximum transit time.
3168 --
3169 IF (l_attribute_tab.EXISTS(g_transit_time_idx)) THEN
3170
3171 IF (p_transit_time IS NOT NULL AND (p_transit_time >= 1)) THEN
3172
3173
3174 IF l_debug_on THEN
3175 wsh_debug_sv.logmsg(l_module_name,'Searching Transit Time (Not Null)');
3176 END IF;
3177
3181 -- When we store 0-4, in database it actually stores (0-5).
3178 l_attribute_key := get_key_for_num( p_fixed_key => p_fixed_key,
3179 p_number => p_transit_time);
3180
3182 -- Upper range break should be taken as < instead of <=.
3183
3184 search_range_cache( p_cache => g_transit_cache,
3185 p_fixed_key => p_fixed_key,
3186 p_attribute_key => l_attribute_key,
3187 -- Upper limit will NOT be seached
3188 p_match_upper_limit => FALSE,
3189 p_search_null => TRUE,
3190 x_result_tab => l_temp_set,
3191 x_return_status => x_return_status);
3192
3193
3194 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3195 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3196 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3197 END IF;
3198 END IF;
3199
3200 IF l_debug_on THEN
3201 IF l_temp_set IS NOT NULL THEN
3202 itr := l_temp_set.FIRST;
3203 LOOP
3204 wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
3205 EXIT WHEN itr = l_temp_set.LAST;
3206 itr := l_temp_set.NEXT(itr);
3207 END LOOP;
3208 END IF;
3209 END IF;
3210
3211 IF (l_temp_set IS NULL) THEN
3212 -- No valid rule is found.
3213 IF l_debug_on THEN
3214 wsh_debug_sv.pop (l_module_name);
3215 END IF;
3216 RETURN;
3217 ELSE
3218 IF (l_result_set IS NULL) THEN
3219 l_result_set := l_temp_set;
3220 ELSE
3221 l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
3222 IF (l_result_set.COUNT = 0 ) THEN
3223 IF l_debug_on THEN
3224 wsh_debug_sv.pop (l_module_name);
3225 END IF;
3226 RETURN;
3227 END IF;
3228 END IF;
3229 END IF;
3230
3231 IF l_debug_on THEN
3232 IF l_result_set IS NOT NULL THEN
3233 itr := l_result_set.FIRST;
3234 LOOP
3235 wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
3236 EXIT WHEN itr = l_result_set.LAST;
3237 itr := l_result_set.NEXT(itr);
3238 END LOOP;
3239 END IF;
3240 END IF;
3241 ELSE
3242 IF (l_result_set IS NULL) THEN
3243 get_all_rules_in_cache(p_cache => g_transit_cache,
3244 x_result_tab => l_result_set,
3245 x_return_status => x_return_status);
3246 END IF;
3247
3248 IF (l_result_set IS NOT NULL AND l_result_set.COUNT>1) THEN
3249 --
3250 -- In all the selected rules, find the rule that has minimum maximum transit time
3251 -- (If only 1 rule then do nothing)
3252 --
3253
3254 IF l_debug_on THEN
3255 wsh_debug_sv.logmsg(l_module_name,'Searching min high transit time ');
3256 END IF;
3257
3258 search_min_high_Transit_Time( p_result_tab => l_result_set,
3259 x_rule_id => x_rule_id,
3260 x_return_status => x_return_status);
3261
3262 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3263 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3264 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3265 END IF;
3266 END IF;
3267
3268 IF l_debug_on THEN
3269 wsh_debug_sv.pop (l_module_name);
3270 END IF;
3271 RETURN;
3272 END IF;
3273 END IF;
3274 END IF;
3275
3276 --
3277 --if result_set is NOT NULL then get the value
3278 --
3279
3280 IF (l_result_set IS NOT NULL) THEN
3281
3282 IF (l_result_set.COUNT=1) THEN
3283 x_rule_id := l_result_set(l_result_set.FIRST);
3284 ELSE
3285 RAISE OVERLAPPING_RULES_EXIST;
3286 END IF;
3287 END IF;
3288
3289 EXCEPTION
3290
3291 WHEN OVERLAPPING_RULES_EXIST THEN
3292
3293 OPEN c_get_group_name;
3294 FETCH c_get_group_name INTO l_group_name;
3295 CLOSE c_get_group_name;
3296
3297 FND_MESSAGE.SET_NAME('FTE','FTE_POSTAL_CODE_OVERLAP');
3298 FND_MESSAGE.SET_TOKEN('RULE_NAME',l_group_name);
3299 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3300 WSH_UTIL_CORE.add_message(x_return_status);
3301
3302 IF l_debug_on THEN
3303 WSH_DEBUG_SV.logmsg(l_module_name,'Overlapping Rules Exist for Group'||l_group_name);
3304 WSH_DEBUG_SV.pop(l_module_name);
3305 END IF;
3306
3307 WHEN others THEN
3308 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.find_rule_for_key');
3309 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3310
3311 IF l_debug_on THEN
3312 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3313 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3314 END IF;
3315
3316 END find_rule_for_key;
3317
3318
3319 --***************************************************************************--
3320 --========================================================================
3321 -- PROCEDURE : get_matching_rule PRIVATE
3322 --
3323 -- PARAMETERS: p_info Attributes of the entity to be searched for
3324 -- x_rule_id Matching Rule
3325 -- x_return_status Return Status
3326 --
3327 -- COMMENT : The API returns the rule which matches attribute values passed in p_info.
3328 -- If no rule is found matching it returns g_rule_not_found
3329 --
3330 --***************************************************************************--
3331 PROCEDURE get_matching_rule( p_info IN FTE_ACS_CACHE_PKG.fte_cs_entity_attr_rec,
3332 x_rule_id OUT NOCOPY NUMBER,
3333 x_return_status OUT NOCOPY VARCHAR2)
3334
3335 IS
3336
3340
3337 l_group_cache_rec GROUP_CACHE_REC;
3338 l_attribute_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3339 l_priority_tab PRIORITY_TAB_TYPE;
3341 from_pregion_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3342 from_all_region_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3343 from_postal_zone_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3344
3345
3346 to_pregion_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3347 to_all_region_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3348 to_postal_zone_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3349
3350 from_postal_code WSH_LOCATIONS.POSTAL_CODE%TYPE;
3351 to_postal_code WSH_LOCATIONS.POSTAL_CODE%TYPE;
3352
3353 p_itr NUMBER;
3354 priority NUMBER;
3355
3356 l_fixed_key VARCHAR2(32767);
3357
3358 from_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3359 to_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3360 from_postal_flag BOOLEAN;
3361 to_postal_flag BOOLEAN;
3362
3363 from_itr NUMBER;
3364 to_itr NUMBER;
3365
3366 from_region NUMBER;
3367 to_region NUMBER;
3368
3369 l_sort_flag BOOLEAN := FALSE;
3370
3371 l_rule_id NUMBER;
3372 l_weight_val NUMBER;
3373 l_volume_val NUMBER;
3374
3375 l_return_status VARCHAR2(1);
3376
3377 FTE_CS_WGHT_CONV_ERR EXCEPTION;
3378 FTE_CS_VOL_CONV_ERR EXCEPTION;
3379
3380 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3381 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'get_matching_rule';
3382
3383
3384 BEGIN
3385
3386 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3387
3388 IF l_debug_on THEN
3389 wsh_debug_sv.push (l_module_name);
3390 WSH_DEBUG_SV.log(l_module_name,'Group Id ',p_info.group_id);
3391 WSH_DEBUG_SV.log(l_module_name,'Delivery Id ',p_info.delivery_id);
3392 WSH_DEBUG_SV.log(l_module_name,'Trip Id ',p_info.trip_id);
3393 WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3394 WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3395 WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3396 WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3397 WSH_DEBUG_SV.log(l_module_name,'Transit Time ',p_info.transit_time);
3398 WSH_DEBUG_SV.log(l_module_name,'Ship From Id ',p_info.ship_from_location_id);
3399 WSH_DEBUG_SV.log(l_module_name,'Ship To Id ',p_info.ship_to_location_id);
3400 WSH_DEBUG_SV.log(l_module_name,'Fob Code ',p_info.fob_code);
3401 END IF;
3402
3403 IF NOT (g_table_initialized) THEN
3404
3405 initialize_tables(x_return_status => l_return_status);
3406
3407 IF l_debug_on THEN
3408 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3409 END IF;
3410
3411 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3412 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3413 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3414 END IF;
3415 END IF;
3416 END IF;
3417
3418 IF NOT(g_groups_cache.EXISTS(p_info.group_id)) THEN
3419
3420 build_cache( p_group_id => p_info.group_id,
3421 x_return_status => l_return_status);
3422
3423 IF l_debug_on THEN
3424 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3425 END IF;
3426
3427 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3428 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3429 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3430 END IF;
3431 END IF;
3432
3433 END IF;
3434
3435 l_group_cache_rec := g_groups_cache(p_info.group_id);
3436
3437 l_attribute_tab := l_group_cache_rec.attribute_tab;
3438 l_priority_tab := l_group_cache_rec.priority_tab;
3439
3440 --
3441 -- Do weight-volume Conversions;
3442 --
3443 IF (l_attribute_tab.EXISTS(g_wt_idx)) THEN
3444 -- Change weight
3445 IF (l_group_cache_rec.weight_uom_code <> p_info.weight_uom_code) THEN
3446
3447 IF l_debug_on THEN
3448 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_ACS_RULE_UTIL_PKG.CONV_TO_BASE_UOM');
3449 END IF;
3450 --
3451 l_weight_val:=FTE_ACS_RULE_UTIL_PKG.CONV_TO_BASE_UOM(p_input_value => p_info.weight,
3452 p_from_uom => p_info.weight_uom_code,
3453 p_to_uom => l_group_cache_rec.weight_uom_code);
3454
3455 IF (l_weight_val < 0 ) THEN
3456 RAISE FTE_CS_WGHT_CONV_ERR;
3457 END IF;
3458 ELSE
3459 l_weight_val := p_info.weight;
3460 END IF;
3461
3462 IF l_debug_on THEN
3463 WSH_DEBUG_SV.logmsg(l_module_name,'Weight '||l_weight_val||'Weight UOM '||l_group_cache_rec.weight_uom_code);
3464 END IF;
3465
3466 END IF;
3467
3468
3469
3470 IF (l_attribute_tab.EXISTS(g_vol_idx)) THEN
3471 -- Change volume
3472 IF (l_group_cache_rec.volume_uom_code <> p_info.volume_uom_code) THEN
3473
3474 IF l_debug_on THEN
3475 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_ACS_RULE_UTIL_PKG.CONV_TO_BASE_UOM');
3476 END IF;
3477 --
3478 l_volume_val:=FTE_ACS_RULE_UTIL_PKG.CONV_TO_BASE_UOM(p_input_value => p_info.volume,
3479 p_from_uom => p_info.volume_uom_code,
3480 p_to_uom => l_group_cache_rec.volume_uom_code);
3481
3482 IF (l_volume_val < 0 ) THEN
3483 RAISE FTE_CS_VOL_CONV_ERR;
3484 END IF;
3485 ELSE
3486 l_volume_val := p_info.volume;
3487 END IF;
3488
3489 IF l_debug_on THEN
3490 WSH_DEBUG_SV.logmsg(l_module_name,'Volume '||l_volume_val||'Volume UOM '||l_group_cache_rec.volume_uom_code);
3491 END IF;
3492 END IF;
3493
3494 IF (l_attribute_tab.EXISTS(g_fr_reg_idx)) THEN
3498
3495
3496 -- We encapsulate region type 3 as
3497 -- pregion(Region type 2,1,0) + postal code
3499 FTE_ACS_RULE_UTIL_PKG.get_formated_regions(
3500 p_location_id => p_info.ship_from_location_id,
3501 x_region_tab => from_pregion_tab,
3502 x_all_region_tab => from_all_region_tab,
3503 x_postal_zone_tab => from_postal_zone_tab,
3504 x_return_status => l_return_status);
3505
3506 IF l_debug_on THEN
3507 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3508 END IF;
3509
3510 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3511 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3512 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3513 END IF;
3514 END IF;
3515 --
3516 -- Depending on results obtained above we can remove certain priorities from the list.
3517 -- There is always a possibility that the location is not associated with some regions.
3518
3519 IF (from_all_region_tab.count = 0 ) THEN
3520
3521 IF l_debug_on THEN
3522 WSH_DEBUG_SV.logmsg(l_module_name,'No From Region Associated with the entity');
3523 END IF;
3524
3525 l_priority_tab := l_priority_tab MULTISET EXCEPT g_from_region_priority;
3526 l_sort_flag := TRUE;
3527
3528 END IF;
3529
3530 --
3531 -- IF (from_pregion_tab.count=0) is NOT NEEDED here.We will check it after checking postal codes.
3532 -- Above check is not needed because because all the regions of type 2,1,0 are already present
3533 -- in from_all_region_tab.
3534 --
3535 -- Following can never happen
3536 -- a) Location is associated to a zones and not to a region
3537 -- b) Location is associated to a region of type 3 and not to a region of type 0
3538 --
3539
3540 IF (from_postal_zone_tab.count = 0) THEN
3541
3542 IF l_debug_on THEN
3543 WSH_DEBUG_SV.logmsg(l_module_name,'No From Postal Zone Associated with the entity');
3544 END IF;
3545
3546 l_priority_tab := l_priority_tab MULTISET EXCEPT g_from_pzone_priority;
3547 l_sort_flag := TRUE;
3548 END IF;
3549 END IF;
3550
3551
3552 IF (l_attribute_tab.EXISTS(g_to_reg_idx)) THEN
3553
3554 FTE_ACS_RULE_UTIL_PKG.get_formated_regions(
3555 p_location_id => p_info.ship_to_location_id,
3556 x_region_tab => to_pregion_tab,
3557 x_all_region_tab => to_all_region_tab,
3558 x_postal_zone_tab => to_postal_zone_tab,
3559 x_return_status => l_return_status);
3560
3561 IF l_debug_on THEN
3562 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3563 END IF;
3564
3565 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3566 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3567 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3568 END IF;
3569 END IF;
3570
3571 --
3572 -- Depending on results obtained above we can remove certain priorities from the list.
3573 -- There is always a possibility that the location is not associated with some regions.
3574 --
3575
3576 IF (to_all_region_tab.count = 0 ) THEN
3577
3578 IF l_debug_on THEN
3579 WSH_DEBUG_SV.logmsg(l_module_name,'No To Region Associated with the entity');
3580 END IF;
3581 l_priority_tab := l_priority_tab MULTISET EXCEPT g_to_region_priority;
3582 l_sort_flag := TRUE;
3583 END IF;
3584
3585 IF (to_postal_zone_tab.count = 0) THEN
3586
3587 IF l_debug_on THEN
3588 WSH_DEBUG_SV.logmsg(l_module_name,'No To Postal Zone Associated with the entity');
3589 END IF;
3590 l_priority_tab := l_priority_tab MULTISET EXCEPT g_to_pzone_priority;
3591 l_sort_flag := TRUE;
3592 END IF;
3593 END IF;
3594
3595
3596 IF (l_attribute_tab.EXISTS(g_fr_post_idx)) THEN
3597
3598 FTE_ACS_RULE_UTIL_PKG.get_postal_code(p_location_id => p_info.ship_from_location_id,
3599 x_postal_code => from_postal_code,
3600 x_return_status => l_return_status);
3601
3602 IF l_debug_on THEN
3603 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3604 END IF;
3605
3606 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3607 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3608 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3609 END IF;
3610 END IF;
3611
3612 --
3613 -- We cannot remove postal codes from the prioirity logic here.
3614 -- If postal code is null then we will search only the null cache.
3615 -- If postal code is present then we will search the cache without null.
3616 -- The case of prioirty checking with and without postal codes has been taken
3617 -- care in priority logic.
3618 --
3619 -- Input has Group What to do How do we handle
3620 -- Postal code Attribute
3621 -- Yes Yes Search None of the priorites are removed.
3622 -- Yes No Do not search Priorities are removed
3623 -- No Yes Search for null match If parameter is null then check only for null cache.
3624 -- No. No Do not search The priority will be removed.
3625 --
3626 END IF;
3627
3628
3629 IF (l_attribute_tab.EXISTS(g_to_post_idx)) THEN
3630
3631 FTE_ACS_RULE_UTIL_PKG.get_postal_code(p_location_id => p_info.ship_to_location_id,
3632 x_postal_code => to_postal_code,
3633 x_return_status => l_return_status);
3634
3635 IF l_debug_on THEN
3636 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3637 END IF;
3638
3639 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3640 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3641 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3642 END IF;
3646 IF (l_priority_tab.COUNT <> 1 and l_sort_flag) THEN
3643 END IF;
3644 END IF;
3645
3647
3648 sort_priority_tab( p_priority_tab => l_priority_tab,
3649 x_return_status => l_return_status);
3650
3651 IF l_debug_on THEN
3652 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3653 END IF;
3654
3655 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3656 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3657 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3658 END IF;
3659 END IF;
3660 END IF;
3661
3662 p_itr := l_priority_tab.FIRST;
3663
3664 IF (p_itr) IS NOT NULL THEN
3665 LOOP
3666 priority := l_priority_tab(p_itr);
3667
3668 IF l_debug_on THEN
3669 WSH_DEBUG_SV.log(l_module_name,'Current Priority ',priority);
3670 END IF;
3671
3672
3673 IF (priority = 1 ) THEN
3674 -- * TO_POSTAL * TO_REGION/ZONE_ID * FROM_POSTAL * FROM_REGION/ZONE_ID *
3675 to_postal_flag := TRUE;
3676 from_postal_flag := TRUE;
3677 to_tab := to_pregion_tab;
3678 from_tab := from_pregion_tab;
3679
3680 ELSIF (priority = 2) THEN
3681 --* TO_POSTAL * TO_REGION/ZONE_ID * FROM_POSTAL * - *
3682 to_postal_flag := TRUE;
3683 from_postal_flag := TRUE;
3684 to_tab := to_pregion_tab;
3685 from_tab := g_null_tab;
3686
3687 ELSIF (priority = 3 ) THEN
3688 -- * TO_POSTAL * TO_REGION_ID * FROM POSTAL ZONE * - *
3689 to_postal_flag := TRUE;
3690 from_postal_flag := FALSE;
3691 to_tab := to_pregion_tab;
3692 from_tab := from_postal_zone_tab;
3693
3694 ELSIF (priority = 4 ) THEN
3695 -- * TO_POSTAL *TO_REGION_ID * FROM_REGION_ID * - *
3696 to_postal_flag := TRUE;
3697 from_postal_flag := FALSE;
3698 to_tab := to_pregion_tab;
3699 from_tab := from_all_region_tab;
3700
3701 ELSIF (priority = 5 ) THEN
3702 -- * TO_POSTAL * TO_REGION_ID * - * - *
3703 to_postal_flag := TRUE;
3704 from_postal_flag := FALSE;
3705 to_tab := to_pregion_tab;
3706 from_tab := g_null_tab;
3707
3708 ELSIF (priority = 6 ) THEN
3709 -- * TO_POSTAL * - * FROM_POSTAL * FROM_REGION_ID *
3710 to_postal_flag := TRUE;
3711 from_postal_flag := TRUE;
3712 to_tab := g_null_tab;
3713 from_tab := to_pregion_tab;
3714
3715 ELSIF (priority = 7 ) THEN
3716 -- * TO POSTAL ZONE * - * FROM_POSTAL * FROM_REGION_ID *
3717 to_postal_flag := FALSE;
3718 from_postal_flag := TRUE;
3719 to_tab := to_postal_zone_tab;
3720 from_tab := from_pregion_tab;
3721
3722 ELSIF (priority = 8 ) THEN
3723 -- * TO_POSTAL * - * FROM_POSTAL * - *
3724 to_postal_flag := TRUE;
3725 from_postal_flag := TRUE;
3726 to_tab := g_null_tab;
3727 from_tab := g_null_tab;
3728
3729 ELSIF (priority = 9 ) THEN
3730 -- * TO_POSTAL * - * FROM_POSTAL_ZONE * - *
3731 to_postal_flag := TRUE;
3732 from_postal_flag := FALSE;
3733 to_tab := g_null_tab;
3734 from_tab := from_postal_zone_tab;
3735
3736 ELSIF (priority = 10 ) THEN
3737 -- * TO_POSTAL_ZONE * - * FROM_POSTAL * - *
3738 to_postal_flag := FALSE;
3739 from_postal_flag := TRUE;
3740 to_tab := to_postal_zone_tab;
3741 from_tab := g_null_tab;
3742
3743 ELSIF (priority = 11 ) THEN
3744 -- * TO POSTAL ZONE * - * FROM POSTAL ZONE * - *
3745 to_postal_flag := FALSE;
3746 from_postal_flag := FALSE;
3747 to_tab := to_postal_zone_tab;
3748 from_tab := from_postal_zone_tab;
3749
3750 ELSIF (priority = 12) THEN
3751 -- * TO POSTAL * - * FROM REGION * - *
3752 to_postal_flag := TRUE;
3753 from_postal_flag := FALSE;
3754 to_tab := g_null_tab;
3755 from_tab := from_all_region_tab;
3756
3757 ELSIF (priority = 13 ) THEN
3758 -- * TO POSTAL ZONE * - * FROM_REGION_ID * - *
3759 to_postal_flag := FALSE;
3760 from_postal_flag := FALSE;
3761 to_tab := to_postal_zone_tab;
3762 from_tab := from_all_region_tab;
3763
3764 ELSIF (priority = 14 ) THEN
3765 -- * TO POSTAL * - * - * - *
3766 to_postal_flag := TRUE;
3767 from_postal_flag := FALSE;
3768 to_tab := g_null_tab;
3769 from_tab := g_null_tab;
3770
3771 ELSIF (priority = 15 ) THEN
3772 -- * TO POSTAL ZONE * - * - * - *
3773 to_postal_flag := FALSE;
3774 from_postal_flag := FALSE;
3775 to_tab := to_postal_zone_tab;
3776 from_tab := g_null_tab;
3777
3778 ELSIF (priority = 16 ) THEN
3779 -- * TO_REGION_ID * - * FROM_POSTAL * FROM_REGION_ID *
3780 to_postal_flag := FALSE;
3781 from_postal_flag := TRUE;
3782 to_tab := to_all_region_tab;
3783 from_tab := from_pregion_tab;
3784
3785 ELSIF (priority = 17 ) THEN
3786 -- * TO_REGION_ID * - * FROM_POSTAL * - *
3787 to_postal_flag := FALSE;
3788 from_postal_flag := TRUE;
3789 to_tab := to_all_region_tab;
3790 from_tab := g_null_tab;
3791
3792 ELSIF (priority = 18 ) THEN
3793 -- * TO_REGION_ID * - * FROM POSTAL ZONE * - *
3794 to_postal_flag := FALSE;
3795 from_postal_flag := FALSE;
3796 to_tab := to_all_region_tab;
3797 from_tab := from_postal_zone_tab;
3798
3799 ELSIF (priority = 19 ) THEN
3800 -- * TO_REGION_ID * - * FROM_REGION_ID * - *
3801 to_postal_flag := FALSE;
3802 from_postal_flag := FALSE;
3803 to_tab := to_all_region_tab;
3804 from_tab := from_all_region_tab;
3805
3806 ELSIF (priority = 20 ) THEN
3807 -- * TO_REGION_ID * - * - * - *
3808 to_postal_flag := FALSE;
3809 from_postal_flag := FALSE;
3813 ELSIF (priority = 21 ) THEN
3810 to_tab := to_all_region_tab;
3811 from_tab := g_null_tab;
3812
3814 -- * - * - * FROM_POSTAL * FROM_REGION_ID *
3815 to_postal_flag := FALSE;
3816 from_postal_flag := TRUE;
3817 to_tab := g_null_tab;
3818 from_tab := from_pregion_tab;
3819
3820 ELSIF (priority = 22 ) THEN
3821 -- * - * - * FROM_POSTAL * - *
3822 to_postal_flag := FALSE;
3823 from_postal_flag := TRUE;
3824 to_tab := g_null_tab;
3825 from_tab := g_null_tab;
3826
3827 ELSIF (priority = 23 ) THEN
3828 --* - * - * - * FROM POSTAL ZONE *
3829 to_postal_flag := FALSE;
3830 from_postal_flag := FALSE;
3831 to_tab := g_null_tab;
3832 from_tab := from_postal_zone_tab;
3833
3834 ELSIF (priority = 24 ) THEN
3835 --* - * - * FROM_REGION_ID * - *
3836 to_postal_flag := FALSE;
3837 from_postal_flag := FALSE;
3838 to_tab := g_null_tab;
3839 from_tab := from_all_region_tab;
3840
3841 ELSIF (priority = 25) THEN
3842 --* - * - * - * - *
3843 to_postal_flag := FALSE;
3844 from_postal_flag := FALSE;
3845 to_tab := g_null_tab;
3846 from_tab := g_null_tab;
3847
3848 END IF;
3849
3850 --
3851 -- Here we should be sure that from table and to table are not empty table.
3852 -- In case they are null we have some extra priorities in our table.
3853 -- These should have not been evaluated;
3854 --
3855 -- In cases where from and to table is null we are using table g_null_tab.
3856 -- g_null_tab has 1 entry ie g_num_absent
3857 --
3858
3859 IF (from_tab.COUNT<>0 AND to_tab.COUNT<>0) THEN
3860
3861 IF l_debug_on THEN
3862 WSH_DEBUG_SV.log(l_module_name,'Calling Find Rule for key for Priority',priority);
3863 END IF;
3864
3865 -- Loop over the tables here only.
3866
3867 to_itr := to_tab.FIRST;
3868 IF (to_itr IS NOT NULL) THEN
3869 LOOP
3870
3871 to_region := to_tab(to_itr);
3872 from_itr := from_tab.FIRST;
3873
3874 IF (from_itr IS NOT NULL) THEN
3875 LOOP
3876
3877 from_region := from_tab(from_itr);
3878 l_fixed_key := get_fixed_key ( p_group_id => p_info.group_id,
3879 p_from_region_id => from_region,
3880 p_to_region_id => to_region);
3881
3882 --
3883 -- If we put a from_region - to_region cache here we can
3884 -- control when we should call the API and when we should not.
3885 --
3886 --
3887 -- Only if From To region has rules registered we will proceed for search
3888 --
3889
3890 IF (g_from_to_region_cache.EXISTS(l_fixed_key)) THEN
3891
3892 find_rule_for_key( p_fixed_key => l_fixed_key,
3893 p_group_id => p_info.group_id,
3894 p_from_postal_flag => from_postal_flag,
3895 p_to_postal_flag => to_postal_flag,
3896 p_from_postal_code => from_postal_code,
3897 p_to_postal_code => to_postal_code,
3898 p_weight => l_weight_val,
3899 p_volume => l_volume_val,
3900 p_transit_time => p_info.transit_time,
3901 p_fob_code => p_info.fob_code,
3902 x_rule_id => l_rule_id,
3903 x_return_status => l_return_status);
3904
3905 IF l_debug_on THEN
3906 WSH_DEBUG_SV.log(l_module_name,'l_rule_id ', l_rule_id);
3907 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3908 END IF;
3909
3910 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3911 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3912 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3913 END IF;
3914 END IF;
3915
3916 IF (l_rule_id <> g_rule_not_found) THEN
3917
3918 x_rule_id := l_rule_id ;
3919
3920 IF l_debug_on THEN
3921 WSH_DEBUG_SV.log(l_module_name,'x_rule_id',x_rule_id);
3922 WSH_DEBUG_SV.POP (l_module_name);
3923 END IF;
3924 RETURN;
3925 END IF;
3926 END IF;
3927
3928 EXIT WHEN from_itr = from_tab.LAST;
3929 from_itr := from_tab.NEXT(from_itr);
3930
3931 END LOOP;
3932 END IF;
3933
3934 EXIT WHEN to_itr = to_tab.LAST;
3935 to_itr := to_tab.NEXT(to_itr);
3936
3937 END LOOP;
3938 END IF;
3939
3940 END IF;
3941 EXIT WHEN p_itr = l_priority_tab.LAST;
3942 p_itr := l_priority_tab.NEXT(p_itr);
3943
3944 END LOOP;
3945 END IF;
3946
3947 -- If we reach here then no rule was found for the delivery
3948 x_rule_id := g_rule_not_found;
3949
3950 IF l_debug_on THEN
3951 WSH_DEBUG_SV.POP (l_module_name);
3952 END IF;
3953
3954 EXCEPTION
3955
3956 WHEN FTE_CS_VOL_CONV_ERR THEN
3957
3958 FND_MESSAGE.SET_NAME('FTE','FTE_CS_VOL_CONV_ERR');
3959 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3960 WSH_UTIL_CORE.add_message(x_return_status);
3961
3962 IF l_debug_on THEN
3963 WSH_DEBUG_SV.pop(l_module_name,'Volume UOM Conversion Error');
3964 END IF;
3965
3966 WHEN FTE_CS_WGHT_CONV_ERR THEN
3967
3968 FND_MESSAGE.SET_NAME('FTE','FTE_CS_WGHT_CONV_ERR');
3969 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3970 WSH_UTIL_CORE.add_message(x_return_status);
3971
3972 IF l_debug_on THEN
3973 WSH_DEBUG_SV.pop(l_module_name,'Volume UOM Conversion Error');
3974 END IF;
3975
3976 WHEN others THEN
3977
3978 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_matching_rule');
3982 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3979 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3980
3981 IF l_debug_on THEN
3983 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3984 END IF;
3985 --
3986 END get_matching_rule;
3987
3988 --***************************************************************************
3989 --===========================================================================
3990 -- PROCEDURE : build_rule_result_cache RRIVATE
3991 --
3992 -- PARAMETERS: p_rule_id Rule Id
3993 -- x_return_status Return Status
3994 --
3995 -- COMMENT : The API builds Rules Result Cache.
3996 -- a) The Rule Result store the results associated with a rule in order of
3997 -- increasing leg sequence/ rank .
3998 -- b) Result Attribute Cache stores the result attributites and there values.
3999 --***************************************************************************--
4000
4001 PROCEDURE build_rule_result_cache( p_rule_id IN NUMBER,
4002 x_return_status OUT NOCOPY VARCHAR2)
4003
4004 IS
4005
4006 -- -----------------------------------------------------------------
4007 -- get results for a rule
4008 -- -----------------------------------------------------------------
4009 cursor c_get_result_id IS
4010 select fsras.result_id
4011 from fte_sel_result_assignments fsras
4012 where fsras.rule_id = p_rule_id;
4013
4014 -- -----------------------------------------------------------------
4015 -- get result attributes names and values
4016 -- -----------------------------------------------------------------
4017 cursor c_get_result_attributes(p_result_id NUMBER) IS
4018 select fsra.attribute_code,
4019 fsra.attribute_value
4020 from fte_sel_result_attributes fsra
4021 where fsra.result_id = p_result_id;
4022
4023
4024 l_result_id_tab result_id_tab;
4025 l_attr_tab fte_attr_code_val_tab_type;
4026 l_cs_result_rec fte_cs_result_attr_rec;
4027 l_sorted_result_tab result_id_tab;
4028
4029 l_result_id NUMBER;
4030
4031 itr NUMBER;
4032 l_itr NUMBER;
4033 l_seq NUMBER;
4034
4035 INVALID_RESULT_ATTRIBUTE EXCEPTION;
4036 NO_RESULTS_FOR_RULE EXCEPTION;
4037
4038 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
4039 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'build_rule_result_cache';
4040
4041 BEGIN
4042
4043 --
4044 -- Values are not in cache.
4045 -- Query the database tables to return results;
4046 --
4047 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4048
4049 IF l_debug_on THEN
4050 WSH_DEBUG_SV.push(l_module_name);
4051 WSH_DEBUG_SV.log(l_module_name,'p_rule_id',p_rule_id);
4052 END IF;
4053
4054
4055 OPEN c_get_result_id;
4056 FETCH c_get_result_id BULK COLLECT INTO l_result_id_tab;
4057 CLOSE c_get_result_id;
4058
4059 itr := l_result_id_tab.FIRST;
4060
4061 IF (itr IS NOT NULL) THEN
4062 LOOP
4063 l_result_id := l_result_id_tab(itr);
4064
4065 IF l_debug_on THEN
4066 WSH_DEBUG_SV.log(l_module_name,'Result id to be queried',l_result_id);
4067 END IF;
4068
4069 IF NOT(g_result_attribute_cache.EXISTS(l_result_id)) THEN
4070
4071 l_attr_tab.DELETE;
4072 l_cs_result_rec := NULL;
4073
4074 OPEN c_get_result_attributes(l_result_id);
4075 FETCH c_get_result_attributes BULK COLLECT INTO l_attr_tab;
4076 CLOSE c_get_result_attributes;
4077
4078 l_itr :=l_attr_tab.FIRST;
4079
4080 IF (l_itr IS NOT NULL) THEN
4081
4082 --
4083 -- Setting the default values(If Rank is NULL , it should be considered 1)
4084 -- Defaulting it to 1, otherwise it will be overwritten
4085 -- Converting the attribute values to record type.
4086 -- Default type is 'RANK'
4087
4088 l_cs_result_rec.rank := 1;
4089 l_cs_result_rec.result_type := 'RANK';
4090
4091 LOOP
4092
4093 IF (l_attr_tab(l_itr).attr_code = 'RANK') THEN
4094 l_cs_result_rec.rank := l_attr_tab(l_itr).attr_val;
4095 l_cs_result_rec.result_type := 'RANK';
4096
4097 ELSIF (l_attr_tab(l_itr).attr_code = 'LEG_SEQUENCE') THEN
4098 -- LEG_SEQUENCE attribute code INDICATES we result_type is MULTILEG
4099 l_cs_result_rec.leg_sequence := l_attr_tab(l_itr).attr_val;
4100 l_cs_result_rec.result_type := 'MULTILEG';
4101
4102 ELSIF (l_attr_tab(l_itr).attr_code = 'LEG_DESTINATION') THEN
4103 l_cs_result_rec.leg_destination := l_attr_tab(l_itr).attr_val;
4104
4105 -- ELSIF (l_attr_tab(l_itr).attr_code = 'ITINERARY') THEN
4106 -- l_cs_result_rec.itinerary_id := l_attr_tab(l_itr).attr_val;
4107
4108 ELSIF (l_attr_tab(l_itr).attr_code = 'CARRIER') THEN
4109 l_cs_result_rec.carrier_id := l_attr_tab(l_itr).attr_val;
4110
4111 ELSIF (l_attr_tab(l_itr).attr_code = 'MODE_OF_TRANSPORT') THEN
4112 l_cs_result_rec.mode_of_transport:= l_attr_tab(l_itr).attr_val;
4113
4114 ELSIF (l_attr_tab(l_itr).attr_code = 'SERVICE_LEVEL') THEN
4115 l_cs_result_rec.service_level := l_attr_tab(l_itr).attr_val;
4116
4117 ELSIF (l_attr_tab(l_itr).attr_code = 'FREIGHT_TERMS') THEN
4118 l_cs_result_rec.freight_terms_code := l_attr_tab(l_itr).attr_val;
4119
4120 ELSIF (l_attr_tab(l_itr).attr_code = 'CONSIGNEE_CAR_ACNO') THEN
4121 l_cs_result_rec.consignee_carrier_ac_no := l_attr_tab(l_itr).attr_val;
4122
4123 -- ELSIF (l_attr_tab(l_itr).attr_code = 'TRACK_ONLY_FLAG') THEN
4124 -- l_cs_result_rec.track_only_flag := l_attr_tab(l_itr).attr_val;
4125 ELSE
4126 RAISE INVALID_RESULT_ATTRIBUTE;
4127 END IF;
4128
4132 END LOOP;
4129 EXIT WHEN l_itr = l_attr_tab.LAST;
4130 l_itr := l_attr_tab.NEXT(l_itr);
4131
4133
4134 END IF;
4135
4136 --
4137 -- Now store the result attributes in the cache;
4138 --
4139 g_result_attribute_cache(l_result_id) := l_cs_result_rec;
4140 END IF;
4141
4142 --
4143 -- Either we will have multileg or Rank : Calculate the temp sequence
4144 -- At this stage we have result attributes stored in the cache.
4145 --
4146
4147 IF (g_result_attribute_cache(l_result_id).result_type = 'MULTILEG') THEN
4148 l_seq := g_result_attribute_cache(l_result_id).leg_sequence;
4149 ELSIF (g_result_attribute_cache(l_result_id).result_type = 'RANK') THEN
4150 l_seq := g_result_attribute_cache(l_result_id).rank;
4151 END IF;
4152
4153 --
4154 l_sorted_result_tab(l_seq) := l_result_id;
4155 --
4156
4157 EXIT WHEN itr = l_result_id_tab.LAST;
4158 itr := l_result_id_tab.NEXT(itr);
4159
4160 END LOOP;
4161
4162 g_rule_result_cache(p_rule_id) := l_sorted_result_tab;
4163
4164 ELSE
4165 RAISE NO_RESULTS_FOR_RULE;
4166 END IF;
4167
4168 IF l_debug_on THEN
4169 WSH_DEBUG_SV.POP(l_module_name);
4170 END IF;
4171
4172
4173 EXCEPTION
4174 WHEN INVALID_RESULT_ATTRIBUTE THEN
4175 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4176 --
4177 IF l_debug_on THEN
4178 WSH_DEBUG_SV.pop(l_module_name,'Invalid Attribute associated with Rule'||p_rule_id);
4179 END IF;
4180
4181 WHEN NO_RESULTS_FOR_RULE THEN
4182
4183 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4184 --
4185 IF l_debug_on THEN
4186 WSH_DEBUG_SV.pop(l_module_name,'No Results Associated with Rule'||p_rule_id);
4187 END IF;
4188
4189 WHEN OTHERS THEN
4190 --
4191 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.BUILD_RULE_RESULT_CACHE');
4192 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4193 --
4194
4195 IF l_debug_on THEN
4196 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4197 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4198 END IF;
4199
4200 END build_rule_result_cache;
4201
4202
4203 --***************************************************************************--
4204 --===========================================================================
4205 -- PROCEDURE : get_results_for_rule PRIVATE
4206 --
4207 -- PARAMETERS: p_rule_id Rule Id.
4208 -- x_result_tab Results Attributes associated with the rule.
4209 -- x_return_status Return Status
4210 --
4211 -- COMMENT : For a given rule id queries FTE_SEL_RESULT_ASSIGNMENTS and FTE_SEL_RESULT_ATTRIBUTES
4212 -- to return the result.Caching is used in this procedure.
4213 --
4214 --
4215 -- ALGORITHM :
4216 -- 1. Check global cache to see if associated results exist.
4217 -- 2. If NOT , build the cache .
4218 -- 3. Return attributes from the cache.
4219 --***************************************************************************--
4220 PROCEDURE get_results_for_rule( p_rule_id IN NUMBER,
4221 x_result_tab OUT NOCOPY FTE_ACS_CACHE_PKG.fte_cs_result_attr_tab,
4222 x_return_status OUT NOCOPY VARCHAR2)
4223
4224 IS
4225
4226 l_tab RESULT_ID_TAB;
4227 itr NUMBER;
4228 l_cnt NUMBER := 0;
4229 l_return_status VARCHAR2(1);
4230 l_itr NUMBER;
4231
4232 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
4233 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'get_results_for_rule';
4234
4235 NO_RESULTS_FOR_RULE EXCEPTION;
4236
4237 BEGIN
4238
4239 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4240
4241 IF l_debug_on THEN
4242 WSH_DEBUG_SV.push(l_module_name);
4243 WSH_DEBUG_SV.log(l_module_name,'p_rule_id',p_rule_id);
4244 END IF;
4245
4246 IF NOT(g_rule_result_cache.EXISTS(p_rule_id)) THEN
4247
4248 BUILD_RULE_RESULT_CACHE( p_rule_id => p_rule_id,
4249 x_return_status => l_return_status);
4250
4251 IF l_debug_on THEN
4252 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
4253 END IF;
4254
4255 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4256 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4257 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4258 END IF;
4259 END IF;
4260 END IF;
4261
4262 l_tab := g_rule_result_cache(p_rule_id);
4263
4264 IF l_debug_on THEN
4265
4266 IF (l_tab.COUNT>0) THEN
4267 itr := l_tab.FIRST;
4268 LOOP
4269 WSH_DEBUG_SV.log(l_module_name,'Result id',l_tab(itr));
4270 EXIT WHEN itr = l_tab.LAST;
4271 itr := l_tab.NEXT(itr);
4272 END LOOP;
4273 END IF;
4274 END IF;
4275
4276 itr := l_tab.FIRST;
4277 IF (itr IS NOT NULL) THEN
4278 LOOP
4279 x_result_tab(l_cnt) := g_result_attribute_cache(l_tab(itr));
4280 EXIT WHEN itr = l_tab.LAST;
4281 itr := l_tab.NEXT(itr);
4282 l_cnt:= l_cnt+1;
4283 END LOOP;
4284 ELSE
4285 RAISE NO_RESULTS_FOR_RULE;
4286 END IF;
4287
4288 IF l_debug_on THEN
4289
4290 l_itr := x_result_tab.FIRST;
4291
4292 IF (l_itr IS NOT NULL) THEN
4293 LOOP
4294 WSH_DEBUG_SV.logmsg(l_module_name,'Output Record for result');
4295 WSH_DEBUG_SV.logmsg(l_module_name,'result_type '||x_result_tab(l_itr).result_type);
4296 WSH_DEBUG_SV.logmsg(l_module_name,'rank '||x_result_tab(l_itr).rank);
4297 WSH_DEBUG_SV.logmsg(l_module_name,'leg_destination '||x_result_tab(l_itr).leg_destination);
4298 WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4299 WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4300 WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4301 WSH_DEBUG_SV.logmsg(l_module_name,'service_level '||x_result_tab(l_itr).service_level);
4302 WSH_DEBUG_SV.logmsg(l_module_name,'freight_terms_code '||x_result_tab(l_itr).freight_terms_code);
4303 WSH_DEBUG_SV.logmsg(l_module_name,'consignee_carrier_ac_no '||x_result_tab(l_itr).consignee_carrier_ac_no);
4304 WSH_DEBUG_SV.logmsg(l_module_name,'result_level '||x_result_tab(l_itr).result_level);
4305
4306 EXIT WHEN l_itr = x_result_tab.LAST;
4307 l_itr := x_result_tab.NEXT(l_itr);
4308 END LOOP;
4309 END IF;
4310 WSH_DEBUG_SV.POP (l_module_name);
4311 END IF;
4312
4313 EXCEPTION
4314
4315 WHEN NO_RESULTS_FOR_RULE THEN
4316
4317 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4318 --
4319 IF l_debug_on THEN
4320 WSH_DEBUG_SV.pop(l_module_name,'No Results Associated with Rule'||p_rule_id);
4321 END IF;
4322
4323 WHEN OTHERS THEN
4324
4325 WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.GET_RESULTS_FOR_RULE');
4326 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4327 --
4328 IF l_debug_on THEN
4329 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4330 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4331 END IF;
4332
4333 END get_results_for_rule;
4334
4335 END FTE_ACS_CACHE_PKG;
[Home] [Help]