1 PACKAGE BODY FTE_ACS_RULE_UTIL_PKG AS
2 /* $Header: FTEACSXB.pls 120.3 2005/07/14 22:47:52 alksharm noship $ */
3 -- -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_ACS_RULE_UTIL_PKG --
6 -- TYPE: PACKAGE BODY --
7 -- DESCRIPTION: Contains utility procedures for carrier selection module --
8 -- --
9 -- --
10 -- CHANGE CONTROL LOG --
11 -- --
12 -- DATE VERSION BY BUG DESCRIPTION --
13 -- ---------- ------- -------- ------- ---------------------------------- --
14 -- 2002/02/04 H ABLUNDEL Created. --
15 -- --
16 -- 2002/04/15 H ABLUNDEL 2322867 Changed the location cursor to --
17 -- get info from wsh_hr_locations_v --
18 -- instead of hz_locations --
19 -- --
20 -- 2002/04/15 H ABLUNDEL 2322867 changed the cursor in insert_temp_ --
21 -- table procedure to check for LIKE --
22 -- from and to regions as the atribute--
23 -- FROM_REGION_ID is not stored --
24 -- --
25 -- 2002/04/24 H ABLUNDEL 2338937 PROCEDURE: UPDATE_REGION_INFO --
26 -- changed the cursor in update_region--
27 -- _info procedure to check for LIKE --
28 -- from and to regions as the atribute--
29 -- FROM_REGION_ID is not stored --
30 -- --
31 -- 2002/04/30 H ABLUNDEL 2345069 PROCEDURE: UPDATE_REGION_INFO --
32 -- added a check to make sure that the--
33 -- regions table coming back has at --
34 -- least one record - was causing an --
35 -- unexpected error --
36 -- --
37 -- 2002/06/11 POST H ABLUNDEL ------- Made code more generic and made --
38 -- performance changes --
39 -- --
40 -- 2003/01/02 I DEHSU 2734094 PROCEDURE: PROCESS_TEMP_DATA --
41 -- to check for zones use region_type --
42 -- instead of zone_level --
43 -- --
44 -- 2003/01/09 I ABLUNDEL 2733856 PROCEDURE: INSERT_TEMP_DATA --
45 -- added debug messages to show the --
46 -- data being added into the global --
47 -- temp table. --
48 -- PROCEDURE: PROCESS_TEMP_DATA --
49 -- After the from zip code is returned--
50 -- in a cached situation the to_zip --
51 -- value was being ignored and the --
52 -- from zip value was inserted into --
53 -- the global temp table in the value --
54 -- for the to-zip, thus this was --
55 -- causing the first pass to work, but--
56 -- the second pass would not. added --
57 -- a reset of l_zip_code after getting--
58 -- the from_zip value --
59 -- --
60 -- --
61 -- 2003/02/10 I ABLUNDEL 2742257 PROCEDURE: PROCESS_TEMP_DATA --
62 -- modified the code to return back --
63 -- a transit time even if a transit --
64 -- time value has been passed in. --
65 -- --
66 -- 2003/01/20 I ABLUNDEL 2759845 PROCEDURE: GET_LOCATION_DATA --
67 -- replaced the two location cursors --
68 -- to use WSH_LOCATIONS_V --
69 -- --
70 -- 2003/01/21 I ABLUNDEL 2761503 PROCEDURE: PROCESS_TEMP_DATA --
71 -- PROCEDURE: GET_LOCATION_DATA --
72 -- FUNCTION: COMPARE_REGION_TYPES --
73 -- FUNCTION: GET_OBJECT_ID --
74 -- FUNCTION: DERIVE_LEAD_TIME --
75 -- In the exception handlers added --
76 -- code to check if all cursors are --
77 -- closed - if not close them --
78 -- --
79 -- 2003/02/21 I ABLUNDEL 2807908 PROCEDURE: PROCESS_TEMP_DATA --
80 -- Modified code for transit time --
81 -- processing to do generic T-Time --
82 -- value if T-Time is <1, 0 or null --
83 -- --
84 -- -------------------------------------------------------------------------- --
85
86 --
87 --R12 Enhancement
88 --
89 TYPE fte_cs_bulk_entity_gtt IS RECORD( delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE,
90 trip_id_tab WSH_UTIL_CORE.ID_TAB_TYPE,
91 delivery_name_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE,
92 trip_name_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE,
93 organization_id_tab WSH_UTIL_CORE.ID_TAB_TYPE,
94 triporigin_internal_org_id_tab WSH_UTIL_CORE.ID_TAB_TYPE,
95 customer_id_tab WSH_UTIL_CORE.ID_TAB_TYPE,
96 customer_site_id_tab WSH_UTIL_CORE.ID_TAB_TYPE,
97 gross_weight_tab WSH_UTIL_CORE.ID_TAB_TYPE,
98 weight_uom_code_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE,
99 volume_tab WSH_UTIL_CORE.ID_TAB_TYPE,
100 volume_uom_code_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE,
101 initial_pickup_loc_id_tab WSH_UTIL_CORE.ID_TAB_TYPE,
102 ultimate_dropoff_loc_id_tab WSH_UTIL_CORE.ID_TAB_TYPE,
103 initial_pickup_date_tab WSH_UTIL_CORE.DATE_TAB_TYPE,
104 ultimate_dropoff_date_tab WSH_UTIL_CORE.DATE_TAB_TYPE,
105 freight_terms_code_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE,
106 fob_code_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE,
107 search_level_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE,
108 transit_time_tab WSH_UTIL_CORE.ID_TAB_TYPE);
109
110 TYPE fte_cs_group_rec_type IS RECORD(GROUP_ID NUMBER,
111 START_DATE DATE,
112 END_DATE DATE);
113
114 TYPE fte_cs_group_rec_tab IS TABLE OF fte_cs_group_rec_type INDEX BY BINARY_INTEGER;
115 TYPE fte_cs_entity_group_cache IS TABLE OF fte_cs_group_rec_tab INDEX BY BINARY_INTEGER;
116
117 --
118 -- Global caches
119 --
120 g_site_cache_tab fte_cs_entity_group_cache;
121 g_cust_cache_tab fte_cs_entity_group_cache;
122 g_org_cache_tab fte_cs_entity_group_cache;
123
124 -- Enterprise level cache is not indexed by any number.
125 g_ship_cache_tab fte_cs_group_rec_tab;
126 g_flag_active VARCHAR2(1) :='A';
127 g_object_id NUMBER := 1;
128
129 --
130 --R12 Data Strucutes End
131 --
132
133 --
134 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_ACS_RULE_UTIL_PKG';
135 --
136 -- -------------------------------------------------------------------------- --
137 -- --
138 -- NAME: CONV_TO_BASE_UOM --
139 -- --
140 -- TYPE: FUNCTION --
141 -- --
142 -- PARAMETERS (IN): p_input_value IN NUMBER --
143 -- p_from_uom IN VARCHAR2 (Input UoM) --
144 -- p_to_uom IN VARCHAR2 (base UoM) --
145 -- --
146 -- PARAMETERS (OUT): none --
147 -- --
148 -- PARAMETERS (IN OUT): none --
149 -- --
150 -- RETURN: NUMBER - the converted value --
151 -- --
152 -- DESCRIPTION: This function converts a value from the input --
153 -- attribute uom to a base uom to be used in the query --
154 -- for the selection search --
155 -- --
156 -- --
157 -- CHANGE CONTROL LOG --
158 -- ------------------ --
159 -- --
160 -- DATE VERSION BY BUG DESCRIPTION --
161 -- ---------- ------- -------- ------- ---------------------------------- --
162 -- 2002/07/01 POST H ABLUNDEL Created --
163 -- --
164 -- -------------------------------------------------------------------------- --
165 FUNCTION CONV_TO_BASE_UOM(p_input_value IN NUMBER,
166 p_from_uom IN VARCHAR2,
167 p_to_uom IN VARCHAR2) RETURN NUMBER IS
168
169 --
170 -- Local Variable Definitions
171 --
172 l_error_text VARCHAR2(2000);
173
174 l_new_conv_value NUMBER;
175
176
177 --
178 l_debug_on BOOLEAN;
179 --
180 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONV_TO_BASE_UOM';
181 --
182 BEGIN
183
184 --
185 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
186 --
187 IF l_debug_on IS NULL
188 THEN
189 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
190 END IF;
191 --
192 --
193 -- Debug Statements
194 --
195 IF l_debug_on THEN
196 WSH_DEBUG_SV.push(l_module_name);
197 --
198 WSH_DEBUG_SV.log(l_module_name,'P_INPUT_VALUE',P_INPUT_VALUE);
199 WSH_DEBUG_SV.log(l_module_name,'P_FROM_UOM',P_FROM_UOM);
200 WSH_DEBUG_SV.log(l_module_name,'P_TO_UOM',P_TO_UOM);
201 END IF;
202 --
203 IF (p_input_value = 0) THEN
204 --
205 -- Zero is always zero!
206 --
207 --
208 -- Debug Statements
209 --
210 IF l_debug_on THEN
211 WSH_DEBUG_SV.pop(l_module_name);
212 END IF;
213 --
214 RETURN(0);
215 END IF;
216
217
218 IF ((p_from_uom is null) OR
219 (p_to_uom is null)) THEN
220 --
221 -- One or both of the UoMs is null, therfore
222 -- we cannot perform the conversion
223 --
224 --
225 -- Debug Statements
226 --
227 IF l_debug_on THEN
228 WSH_DEBUG_SV.pop(l_module_name);
229 END IF;
230 --
231 RETURN(-99999);
232 ELSE
233 --
234 -- Debug Statements
235 --
236 IF l_debug_on THEN
237 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_CONVERT.INV_UM_CONVERT',WSH_DEBUG_SV.C_PROC_LEVEL);
238 END IF;
239 --
240 l_new_conv_value := INV_CONVERT.inv_um_convert(null,
241 5,
242 p_input_value,
243 p_from_uom,
244 p_to_uom,
245 null,
246 null);
247 --
248 -- Debug Statements
249 --
250 IF l_debug_on THEN
251 WSH_DEBUG_SV.pop(l_module_name);
252 END IF;
253 --
254 RETURN(l_new_conv_value);
255
256 END IF;
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 l_error_text := SQLERRM;
261 FND_MESSAGE.SET_NAME('FTE','FTE_CS_CONV_VALUE_TO_UOM_ERR');
262 --
263 -- Debug Statements
264 --
265 IF l_debug_on THEN
266 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_ACS_RULE_UTIL_PKG.CONV_VAL_TO_BASE_UOM IS ' ||L_ERROR_TEXT );
267 END IF;
268 --
269 WSH_UTIL_CORE.default_handler('FTE_ACS_RULE_UTIL_PKG.CONV_TO_BASE_UOM');
270 --
271 -- Debug Statements
272 --
273 IF l_debug_on THEN
274 WSH_DEBUG_SV.pop(l_module_name);
275 END IF;
276 --
277 RETURN(-99999);
278
279 --
280 -- Debug Statements
281 --
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
284 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
285 END IF;
286 --
287 END CONV_TO_BASE_UOM;
288
289 PROCEDURE compare_ranges(p_attr_name IN VARCHAR2,
290 p_attr_from_number_tab IN FTE_ID_TAB_TYPE,
291 p_attr_to_number_tab IN FTE_ID_TAB_TYPE,
292 p_attr_from_char_tab IN FTE_NAME_TAB_TYPE,
293 p_attr_to_char_tab IN FTE_NAME_TAB_TYPE,
294 p_compare_start IN NUMBER,
295 p_compare_end IN NUMBER,
296 p_overlap_flag_tab IN OUT NOCOPY FTE_NAME_TAB_TYPE) is
297
298 base_value_from VARCHAR2(30);
299 base_value_to VARCHAR2(30);
300 cmp_value_from VARCHAR2(30);
301 cmp_value_to VARCHAR2(30);
302
303 base_value_from_number NUMBER;
304 base_value_to_number NUMBER;
305 cmp_value_from_number NUMBER;
306 cmp_value_to_number NUMBER;
307
308 begin
309
310 if (p_attr_name like '%CODE') then
311 FOR base_idx in p_compare_start..p_compare_end-1 LOOP
312 base_value_from := p_attr_from_char_tab(base_idx);
313 base_value_to := p_attr_to_char_tab(base_idx);
314 if (base_value_to is null) then
315 base_value_to := base_value_from;
316 end if;
317
318 FOR cmp_idx in base_idx+1..p_compare_end LOOP
319 cmp_value_from := p_attr_from_char_tab(cmp_idx);
320 cmp_value_to := p_attr_to_char_tab(cmp_idx);
321 if (cmp_value_to is null) then
322 cmp_value_to := cmp_value_from;
323 end if;
324
325 if (cmp_value_to >= base_value_from AND
326 cmp_value_from <= base_value_to) then
327
328 FOR idx in p_compare_start..p_compare_end LOOP
329 p_overlap_flag_tab(idx) := 'Y';
330 END LOOP;
331 return;
332
333 end if;
334 END LOOP;
335 END LOOP;
336 else
337 FOR base_idx in p_compare_start..p_compare_end-1 LOOP
338 base_value_from_number := p_attr_from_number_tab(base_idx);
339 base_value_to_number := p_attr_to_number_tab(base_idx);
340
341 FOR cmp_idx in base_idx+1..p_compare_end LOOP
342 cmp_value_from_number := p_attr_from_number_tab(cmp_idx);
343 cmp_value_to_number := p_attr_to_number_tab(cmp_idx);
344
345 if (cmp_value_to_number > base_value_from_number AND
346 cmp_value_from_number < base_value_to_number) then
347
348 FOR idx in p_compare_start..p_compare_end LOOP
349 p_overlap_flag_tab(idx) := 'Y';
350 END LOOP;
351 return;
352
353 end if;
354 END LOOP;
355 END LOOP;
356 end if;
357
358 FOR idx in p_compare_start..p_compare_end LOOP
359 p_overlap_flag_tab(idx) := 'N';
360 END LOOP;
361
362 end COMPARE_RANGES;
363
364 PROCEDURE set_attr_overlap_flag(p_group_id IN NUMBER,
365 p_attr_name IN VARCHAR2) is
366 l_prev_region_from NUMBER := -2;
367 l_prev_region_to NUMBER := -2;
368 l_compare_start NUMBER := 0;
369 l_compare_end NUMBER := 0;
370
371 l_from_region_tab FTE_ID_TAB_TYPE := FTE_ID_TAB_TYPE();
372 l_to_region_tab FTE_ID_TAB_TYPE := FTE_ID_TAB_TYPE();
373 l_attr_from_number_tab FTE_ID_TAB_TYPE := FTE_ID_TAB_TYPE();
374 l_attr_to_number_tab FTE_ID_TAB_TYPE := FTE_ID_TAB_TYPE();
375 l_attr_from_char_tab FTE_NAME_TAB_TYPE := FTE_NAME_TAB_TYPE();
376 l_attr_to_char_tab FTE_NAME_TAB_TYPE := FTE_NAME_TAB_TYPE();
377 l_rule_attribute_id_tab FTE_ID_TAB_TYPE := FTE_ID_TAB_TYPE();
378 l_overlap_flag_tab FTE_NAME_TAB_TYPE := FTE_NAME_TAB_TYPE();
379
380
381 -- Cursor to grab the specified attribute values ordered by
382 -- FROM_REGION_ID and TO_REGION_ID
383 -- If FROM_REGION_ID or TO_REGION_ID attribute doesn't exist in the table
384 -- convert the value to -1 for number comparision purpose
385 -- Note that this query joins with FTE_SEL_RULES table
386 -- to eliminate the junk data that exist in FTE_SEL_RULE_RESTRICTIONS table
387 -- but don't have the corresponding rule in FTE_SEL_RULES table
388
389 CURSOR c_get_ranges(x_group_id NUMBER, x_attr_name VARCHAR2) IS
390 select nvl(fr.attribute_value_from_number, -1) from_region,
391 nvl(tr.attribute_value_from_number, -1) to_region,
392 attr.attribute_value_from_number, attr.attribute_value_to_number,
393 attr.attribute_value_from, attr.attribute_value_to,
394 attr.rule_attribute_id, attr.range_overlap_flag
395 from fte_sel_rule_restrictions attr, fte_sel_rule_restrictions fr,
396 fte_sel_rule_restrictions tr, fte_sel_rules rr
397 where attr.group_id = x_group_id and attr.attribute_name = x_attr_name
398 and fr.rule_id (+)= attr.rule_id
399 and fr.attribute_name (+)= 'FROM_REGION_ID'
400 and tr.rule_id (+)= attr.rule_id
401 and tr.attribute_name (+)= 'TO_REGION_ID'
402 and rr.rule_id = attr.rule_id
403 order by from_region, to_region;
404
405 BEGIN
406
407 OPEN c_get_ranges(p_group_id, p_attr_name);
408 FETCH c_get_ranges BULK COLLECT INTO
409 l_from_region_tab, l_to_region_tab,
410 l_attr_from_number_tab, l_attr_to_number_tab,
411 l_attr_from_char_tab, l_attr_to_char_tab,
412 l_rule_attribute_id_tab, l_overlap_flag_tab;
413 CLOSE c_get_ranges;
414
415 if (l_rule_attribute_id_tab.COUNT > 1) then
416
417 FOR lc IN 1..l_rule_attribute_id_tab.COUNT LOOP
418
419 if (l_from_region_tab(lc) <> l_prev_region_from or
420 l_to_region_tab(lc) <> l_prev_region_to) then
421
422 l_compare_end := lc-1;
423 if (l_compare_start = l_compare_end) then
424 if (l_compare_start <> 0) then
425 l_overlap_flag_tab(lc-1) := 'N';
426 end if;
427 else
428 compare_ranges(p_attr_name,
429 l_attr_from_number_tab, l_attr_to_number_tab,
430 l_attr_from_char_tab, l_attr_to_char_tab,
431 l_compare_start, l_compare_end, l_overlap_flag_tab);
432 end if;
433 l_compare_start := lc;
434 l_prev_region_from := l_from_region_tab(lc);
435 l_prev_region_to := l_to_region_tab(lc);
436 end if;
437 END LOOP;
438
439 l_compare_end := l_rule_attribute_id_tab.COUNT;
440 if (l_compare_start = l_compare_end) then
441 l_overlap_flag_tab(l_rule_attribute_id_tab.COUNT) := 'N';
442 else
443 compare_ranges(p_attr_name,
444 l_attr_from_number_tab, l_attr_to_number_tab,
445 l_attr_from_char_tab, l_attr_to_char_tab,
446 l_compare_start, l_compare_end, l_overlap_flag_tab);
447 end if;
448
449 elsif (l_rule_attribute_id_tab.COUNT = 1) then
450 l_overlap_flag_tab(1) := 'N';
451 end if;
452
453 if (l_rule_attribute_id_tab.COUNT >= 1) then
454
455 FORALL i IN 1..l_rule_attribute_id_tab.COUNT
456 UPDATE fte_sel_rule_restrictions
457 SET range_overlap_flag = l_overlap_flag_tab(i)
458 WHERE rule_attribute_id = l_rule_attribute_id_tab(i);
459 end if;
460
461 END SET_ATTR_OVERLAP_FLAG;
462
463
464 PROCEDURE SET_RANGE_OVERLAP_FLAG(p_group_id IN NUMBER) is
465 begin
466 set_attr_overlap_flag(p_group_id, 'DISPLAY_WEIGHT');
467 set_attr_overlap_flag(p_group_id, 'DISPLAY_VOLUME');
468 set_attr_overlap_flag(p_group_id, 'TRANSIT_TIME');
469 set_attr_overlap_flag(p_group_id, 'FROM_REGION_POSTAL_CODE');
470 set_attr_overlap_flag(p_group_id, 'TO_REGION_POSTAL_CODE');
471 end SET_RANGE_OVERLAP_FLAG;
472
473 -- SBAKSHI (R12- Enhancement)
474 --***************************************************************************--
475 --========================================================================
476 -- PROCEDURE : get_formatted_regions PRIVATE
477 --
478 -- PARAMETERS: p_location_id Location id.
479 -- x_region_tab Has regions of type 0,1,2
480 -- x_all_region_tab Has regions of type 0,1,2 and the zones.
481 -- x_postal_zone_tab Has zones associated with region of type 3
482 -- x_return_status Return Status
483 --
484 -- COMMENT : The API returns the regions and zones associated with a location.Sequence of
485 -- regions is same as is returned by WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches
486 --
487 --***************************************************************************--
488 PROCEDURE GET_FORMATED_REGIONS( p_location_id IN NUMBER,
489 x_region_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
490 x_all_region_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
491 x_postal_zone_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
492 x_return_status OUT NOCOPY VARCHAR2)
493 IS
494
495 l_postal_tab WSH_UTIL_CORE.ID_TAB_TYPE;
496 l_region_tab WSH_UTIL_CORE.ID_TAB_TYPE;
497 l_postal_code_tab WSH_UTIL_CORE.ID_TAB_TYPE;
498 l_region_table WSH_REGIONS_SEARCH_PKG.region_table;
499
500 l_language VARCHAR2(720);
501 l_zone_flag VARCHAR2(1):='Y';
502
503 itr NUMBER;
504 l_count NUMBER;
505
506 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
507 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'get_formatted_regions';
508
509 BEGIN
510
511 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
512
513 IF l_debug_on THEN
514 wsh_debug_sv.push (l_module_name);
515 END IF;
516
517 IF (l_language is null) THEN
518 l_language := nvl(userenv('LANG'),'US');
519 END IF;
520
521 WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(p_country => null,
522 p_country_region => null,
523 p_state => null, --l_state,
524 p_city => null, -- l_city,
525 p_postal_code_from => null,
526 p_postal_code_to => null,
527 p_country_code => null, --l_country,
528 p_country_region_code => null,
529 p_state_code => null,
530 p_city_code => null,
531 p_lang_code => l_language,
532 p_location_id => p_location_id,
533 p_zone_flag => l_zone_flag,
534 x_status => x_return_status,
535 x_regions => l_region_table);
536
537
538 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
539 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
540 raise FND_API.G_EXC_UNEXPECTED_ERROR;
541 END IF;
542 END IF;
543
544 itr := l_region_table.FIRST;
545
546 IF itr IS NOT NULL THEN
547 LOOP
548 IF (l_region_table(itr).region_type IN (0,1,2)) THEN
549 l_count := x_region_tab.COUNT;
550 x_region_tab(l_count) := l_region_table(itr).region_id;
551 END IF;
552
553 IF (l_region_table(itr).region_type NOT IN (3,11) AND l_region_table(itr).zone_level<>3) THEN
554 l_count := x_all_region_tab.COUNT;
555 x_all_region_tab(l_count) := l_region_table(itr).region_id;
556 END IF;
557
558 IF (l_region_table(itr).zone_level = 3 AND l_region_table(itr).region_type = 10) THEN
559 l_count := x_postal_zone_tab.COUNT;
560 x_postal_zone_tab(l_count) := l_region_table(itr).region_id;
561 END IF;
562
563 EXIT WHEN itr = l_region_table.LAST;
564 itr := l_region_table.NEXT(itr);
565
566 END LOOP;
567 END IF;
568
569 IF l_debug_on THEN
570 WSH_DEBUG_SV.log(l_module_name,'Found Regions for location ',p_location_id);
571 WSH_DEBUG_SV.POP(l_module_name);
572 END IF;
573
574 EXCEPTION
575
576 WHEN others THEN
577
578 WSH_UTIL_CORE.default_handler('FTE_ACS_UTIL_PKG.GET_FORMATTED_REGION');
579 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
580
581 IF l_debug_on THEN
582 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
583 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
584 END IF;
585 --
586 END GET_FORMATED_REGIONS;
587
588 --***************************************************************************--
589 --========================================================================
590 -- PROCEDURE : get_postal_code PRIVATE
591 --
592 -- PARAMETERS: p_location_id Location id.
593 -- x_postal_code Postal Code associated with the location
594 -- x_return_status Return Status
595 --
596 -- COMMENT : The API returns the postal codes for a location.
597 --
598 --***************************************************************************--
599 PROCEDURE GET_POSTAL_CODE(p_location_id IN NUMBER,
600 x_postal_code OUT NOCOPY VARCHAR2,
601 x_return_status OUT NOCOPY VARCHAR2)
602
603 IS
604
605 CURSOR c_get_postal_code IS
606 SELECT postal_code
607 FROM wsh_locations
608 WHERE wsh_location_id = p_location_id;
609
610 l_postal_code WSH_LOCATIONS.POSTAL_CODE%TYPE;
611
612 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
613 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'get_postal_code';
614
615 BEGIN
616
617 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
618
619 IF l_debug_on THEN
620 wsh_debug_sv.push (l_module_name);
621 END IF;
622
623 OPEN c_get_postal_code;
624 FETCH c_get_postal_code INTO l_postal_code;
625 CLOSE c_get_postal_code;
626
627 x_postal_code := l_postal_code;
628
629 IF l_debug_on THEN
630 WSH_DEBUG_SV.POP (l_module_name);
631 END IF;
632
633 EXCEPTION
634
635 WHEN OTHERS THEN
636 IF c_get_postal_code%ISOPEN THEN
637 CLOSE c_get_postal_code;
638 END IF;
639
640 WSH_UTIL_CORE.default_handler('FTE_ACS_UTIL_PKG.get_postal_code');
641 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
642
643 IF l_debug_on THEN
644 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
645 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
646 END IF;
647 --
648 END GET_POSTAL_CODE;
649
650
651 --***************************************************************************--
652 --========================================================================
653 -- PROCEDURE : insert_into_gtt PRIVATE
654 --
655 -- PARAMETERS: p_input_data Entity related information.
656 -- x_return_status Return Status
657 --
658 -- COMMENT : Inserts the entity related data into Global Temporary Table
659 --
660 --***************************************************************************--
661 PROCEDURE INSERT_INTO_GTT(p_input_data IN FTE_ACS_PKG.fte_cs_entity_tab_type,
662 x_return_status OUT NOCOPY VARCHAR2)
663 IS
664 l_cnt NUMBER;
665 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
666 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_INTO_GTT';
667 l_first NUMBER;
668 l_last NUMBER;
669 l_insert_gtt_rec FTE_CS_BULK_ENTITY_GTT;
670 i NUMBER;
671 itr NUMBER;
672
673 BEGIN
674 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
675
676 IF l_debug_on THEN
677 wsh_debug_sv.push (l_module_name);
678 WSH_DEBUG_SV.logmsg(l_module_name,'Number of input records'||p_input_data.COUNT);
679 WSH_DEBUG_SV.logmsg(l_module_name,'Inserting data into GTT');
680 itr := p_input_data.FIRST;
681 LOOP
682 WSH_DEBUG_SV.logmsg(l_module_name,'***NEW RECORD ********');
683 WSH_DEBUG_SV.log(l_module_name,'delivery_id ',p_input_data(itr).delivery_id);
684 WSH_DEBUG_SV.log(l_module_name,'trip_id ',p_input_data(itr).trip_id);
685 WSH_DEBUG_SV.log(l_module_name,'delivery_name ',p_input_data(itr).delivery_name);
686 WSH_DEBUG_SV.log(l_module_name,'trip_name ',p_input_data(itr).trip_name);
687 WSH_DEBUG_SV.log(l_module_name,'organization_id ',p_input_data(itr).organization_id);
688 WSH_DEBUG_SV.log(l_module_name,'triporigin_internalorg_id ',p_input_data(itr).triporigin_internalorg_id);
689 WSH_DEBUG_SV.log(l_module_name,'customer_id ',p_input_data(itr).customer_id);
690 WSH_DEBUG_SV.log(l_module_name,'customer_site_id ',p_input_data(itr).customer_site_id);
691 WSH_DEBUG_SV.log(l_module_name,'gross_weight ', p_input_data(itr).gross_weight);
692 WSH_DEBUG_SV.log(l_module_name,'weight_uom_code ',p_input_data(itr).weight_uom_code);
693 WSH_DEBUG_SV.log(l_module_name,'volume ', p_input_data(itr).volume);
694 WSH_DEBUG_SV.log(l_module_name,'volume_uom_code ', p_input_data(itr).volume_uom_code);
695 WSH_DEBUG_SV.log(l_module_name,'initial_pickup_loc_id ', p_input_data(itr).initial_pickup_loc_id);
696 WSH_DEBUG_SV.log(l_module_name,'ultimate_dropoff_loc_id ', p_input_data(itr).ultimate_dropoff_loc_id);
697 WSH_DEBUG_SV.log(l_module_name,'initial_pickup_date ', nvl(p_input_data(itr).initial_pickup_date,SYSDATE));
698 WSH_DEBUG_SV.log(l_module_name,'ultimate_dropoff_date ', p_input_data(itr).ultimate_dropoff_date);
699 WSH_DEBUG_SV.log(l_module_name,'freight_terms_code ',p_input_data(itr).freight_terms_code);
700 WSH_DEBUG_SV.log(l_module_name,'fob_code ',p_input_data(itr).fob_code);
701 WSH_DEBUG_SV.log(l_module_name,'start_search_level ',p_input_data(itr).start_search_level);
702 WSH_DEBUG_SV.log(l_module_name,'transit_time ',p_input_data(itr).transit_time);
703
704 EXIT WHEN itr = p_input_data.LAST;
705 itr := p_input_data.NEXT(itr);
706 END LOOP;
707 END IF;
708
709 --
710 -- For Bulk insert we need to have record of tables instead of table of records.
711 --
712
713 i := 1;
714 l_first := i;
715 itr := p_input_data.FIRST;
716 LOOP
717 l_insert_gtt_rec.delivery_id_tab(i) := p_input_data(itr).delivery_id;
718 l_insert_gtt_rec.trip_id_tab(i) := p_input_data(itr).trip_id;
719 l_insert_gtt_rec.delivery_name_tab(i) := p_input_data(itr).delivery_name;
720 l_insert_gtt_rec.trip_name_tab(i) := p_input_data(itr).trip_name;
721 l_insert_gtt_rec.organization_id_tab(i) := p_input_data(itr).organization_id;
722 l_insert_gtt_rec.triporigin_internal_org_id_tab(i) := p_input_data(itr).triporigin_internalorg_id;
723 l_insert_gtt_rec.customer_id_tab(i) := p_input_data(itr).customer_id;
724 l_insert_gtt_rec.customer_site_id_tab(i) := p_input_data(itr).customer_site_id;
725 l_insert_gtt_rec.gross_weight_tab(i) := p_input_data(itr).gross_weight;
726 l_insert_gtt_rec.weight_uom_code_tab(i) := p_input_data(itr).weight_uom_code;
727 l_insert_gtt_rec.volume_tab(i) := p_input_data(itr).volume;
728 l_insert_gtt_rec.volume_uom_code_tab(i) := p_input_data(itr).volume_uom_code;
729 l_insert_gtt_rec.initial_pickup_loc_id_tab(i) := p_input_data(itr).initial_pickup_loc_id;
730 l_insert_gtt_rec.ultimate_dropoff_loc_id_tab(i) := p_input_data(itr).ultimate_dropoff_loc_id;
731 l_insert_gtt_rec.initial_pickup_date_tab(i) := nvl(p_input_data(itr).initial_pickup_date,SYSDATE);
732 l_insert_gtt_rec.ultimate_dropoff_date_tab(i) := p_input_data(itr).ultimate_dropoff_date;
733 l_insert_gtt_rec.freight_terms_code_tab(i) := p_input_data(itr).freight_terms_code;
734 l_insert_gtt_rec.fob_code_tab(i) := p_input_data(itr).fob_code;
735 l_insert_gtt_rec.search_level_tab(i) := p_input_data(itr).start_search_level;
736 l_insert_gtt_rec.transit_time_tab(i) := p_input_data(itr).transit_time;
737
738 EXIT WHEN itr = p_input_data.LAST;
739 itr := p_input_data.NEXT(itr);
740 i := i+1;
741 END LOOP;
742 l_last := i;
743
744 FORALL j IN l_first..l_last
745
746 INSERT INTO FTE_SEL_SEARCH_ENTITIES_TMP(
747 delivery_id,
748 trip_id ,
749 delivery_name,
750 trip_name,
751 organization_id,
752 triporigin_internalorg_id,
753 customer_id,
754 customer_site_id,
755 gross_weight,
756 weight_uom_code,
757 volume,
758 volume_uom_code,
759 initial_pickup_loc_id,
760 ultimate_dropoff_loc_id,
761 initial_pickup_date,
762 ultimate_dropoff_date,
763 freight_terms_code,
764 fob_code,
765 search_level,
766 transit_time)
767 VALUES(
768 l_insert_gtt_rec.delivery_id_tab(j),
769 l_insert_gtt_rec.trip_id_tab(j),
770 l_insert_gtt_rec.delivery_name_tab(j),
771 l_insert_gtt_rec.trip_name_tab(j),
772 l_insert_gtt_rec.organization_id_tab(j),
773 l_insert_gtt_rec.triporigin_internal_org_id_tab(j),
774 l_insert_gtt_rec.customer_id_tab(j),
775 l_insert_gtt_rec.customer_site_id_tab(j),
776 l_insert_gtt_rec.gross_weight_tab(j),
777 l_insert_gtt_rec.weight_uom_code_tab(j),
778 l_insert_gtt_rec.volume_tab(j),
779 l_insert_gtt_rec.volume_uom_code_tab(j),
780 l_insert_gtt_rec.initial_pickup_loc_id_tab(j),
781 l_insert_gtt_rec.ultimate_dropoff_loc_id_tab(j),
782 l_insert_gtt_rec.initial_pickup_date_tab(j),
783 l_insert_gtt_rec.ultimate_dropoff_date_tab(j),
784 l_insert_gtt_rec.freight_terms_code_tab(j),
785 l_insert_gtt_rec.fob_code_tab(j),
786 l_insert_gtt_rec.search_level_tab(j),
787 l_insert_gtt_rec.transit_time_tab(j));
788
789 IF l_debug_on THEN
790 wsh_debug_sv.pop (l_module_name);
791 END IF;
792
793 EXCEPTION
794 WHEN OTHERS THEN
795 --
796 WSH_UTIL_CORE.default_handler('FTE_ACS_RULE_UTIL_PKG.INSERT_INTO_GTT');
797 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
798 --
799 IF l_debug_on THEN
800 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
801 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
802 END IF;
803 --
804 END insert_into_gtt;
805
806
807 --***************************************************************************--
808 --========================================================================
809 -- PROCEDURE : format_entity_info PRIVATE
810 --
811 -- PARAMETERS: p_input_cs_tab Input Data to be formated.
812 -- p_entity Entity information
813 -- x_return_status Return Status
814 --
815 -- COMMENT : a) Determines the transit time if not specified.
816 -- b) Determines the start search level.
817 --
818 --***************************************************************************--
819 PROCEDURE FORMAT_ENTITY_INFO( p_input_cs_tab IN OUT NOCOPY FTE_ACS_PKG.fte_cs_entity_tab_type,
820 p_entity IN VARCHAR2,
821 x_return_status OUT NOCOPY VARCHAR2)
822 IS
823
824 itr NUMBER;
825 l_param_rec WSH_SHIPPING_PARAMS_PVT.PARAMETER_VALUE_REC_TYP;
826 l_search_level VARCHAR2(1);
827 l_itr NUMBER;
828 l_sysdate DATE := sysdate;
829
830 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
831 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FORMAT_ENTITY_INFO';
832
833 BEGIN
834
835 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
836
837 IF l_debug_on THEN
838 wsh_debug_sv.push (l_module_name);
839 WSH_DEBUG_SV.logmsg(l_module_name,'Number of input records'||p_input_cs_tab.COUNT);
840 END IF;
841
842 itr := p_input_cs_tab.FIRST;
843
844 IF (itr IS NOT NULL) THEN
845 LOOP
846 --
847
848 -- 3. AG modify initial pickupdate and ultimatedropoff date if required
849 IF (p_input_cs_tab(itr).initial_pickup_date is not null) THEN
850 IF (p_input_cs_tab(itr).initial_pickup_date < l_sysdate) THEN
851 p_input_cs_tab(itr).initial_pickup_date := l_sysdate;
852 END IF;
853 ELSE
854 p_input_cs_tab(itr).initial_pickup_date := l_sysdate;
855 END IF;
856
857 IF (p_input_cs_tab(itr).ultimate_dropoff_date is not null) THEN
858 IF (p_input_cs_tab(itr).ultimate_dropoff_date < l_sysdate) THEN
859 p_input_cs_tab(itr).ultimate_dropoff_date := l_sysdate;
860 END IF;
861 END IF;
862
863 --1) When Caller is order management.Transit time is passed.
864 --
865 IF ( p_input_cs_tab(itr).transit_time IS NULL) THEN
866 IF ( p_input_cs_tab(itr).ultimate_dropoff_date is NOT NULL) THEN
867 p_input_cs_tab(itr).transit_time := p_input_cs_tab(itr).ultimate_dropoff_date
868 - nvl(p_input_cs_tab(itr).initial_pickup_date, sysdate);
869 END IF;
870 END IF;
871
872
873 IF (p_entity IN ('DLVY','PSEUDO_DLVY')) THEN
874 --
875 -- 2) Get the Start Search Level based on the Freight Term.
876 --
877 l_param_rec.organization_id := p_input_cs_tab(itr).organization_id;
878 l_param_rec.param_name(1) := 'FREIGHT_TERMS';
879
880 WSH_SHIPPING_PARAMS_PVT.GET( x_param_value_info => l_param_rec,
881 x_return_status => x_return_status);
882
883 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
884 raise FND_API.G_EXC_UNEXPECTED_ERROR;
885 END IF;
886
887 l_itr := l_param_rec.param_value_chr.FIRST;
888
889 l_search_level := 'S';
890 IF (l_itr IS NOT NULL) THEN
891 LOOP
892 IF ( p_input_cs_tab(itr).freight_terms_code = l_param_rec.param_value_chr(l_itr)) THEN
893 --For the freight term start at Organization level.
894 l_search_level := 'O';
895 EXIT;
896 END IF;
897 EXIT WHEN l_itr = l_param_rec.param_value_chr.LAST;
898 l_itr := l_param_rec.param_value_chr.NEXT(l_itr);
899 END LOOP;
900 END IF;
901
902 p_input_cs_tab(itr).start_search_level := l_search_level;
903 ELSE
904 -- In trip we always start with internal organization id
905 p_input_cs_tab(itr).start_search_level := 'I';
906 END IF;
907
908 EXIT WHEN itr = p_input_cs_tab.LAST;
909 itr := p_input_cs_tab.NEXT(itr);
910 END LOOP;
911 END IF;
912
913 IF l_debug_on THEN
914 wsh_debug_sv.pop (l_module_name);
915 END IF;
916 EXCEPTION
917 WHEN OTHERS THEN
918 --
919 WSH_UTIL_CORE.default_handler('FTE_ACS_RULE_UTIL_PKG.FORMAT_ENTITY_INFO');
920 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
921 --
922 IF l_debug_on THEN
923 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
924 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
925 END IF;
926 --
927 END FORMAT_ENTITY_INFO;
928
929 --***************************************************************************--
930 --==========================================================================
931 -- PROCEDURE : get_ship_method_code PRIVATE
932 --
933 -- PARAMETERS: p_carrier_id Carrier Id
934 -- p_service_level Service Level
935 -- p_mode_of_transport Mode of Transport
936 -- p_org_id Organization Id
937 -- x_ship_method_code Ship Method Code
938 -- x_return_message Return Message
939 -- x_return_status Return Status
940 --
941 -- COMMENT : Determines the ship method for input carrier id, service level and
942 -- mode of transport.
943 --***************************************************************************--
944 PROCEDURE GET_SHIP_METHOD_CODE(p_carrier_id IN NUMBER,
945 p_service_level IN VARCHAR2,
946 p_mode_of_transport IN VARCHAR2,
947 p_org_id IN NUMBER,
948 x_ship_method_code OUT NOCOPY VARCHAR2,
949 x_return_status OUT NOCOPY VARCHAR2,
950 x_return_message OUT NOCOPY VARCHAR2)
951 IS
952 l_error_code NUMBER; -- Oracle SQL Error code
953 l_error_text VARCHAR2(2000); -- Oracle SQL Error Text
954 l_enabled_flag VARCHAR2(1) := 'Y'; -- Indicates enabled status
955 l_ship_method_code VARCHAR2(30); -- holder to return ship method code
956
957 cursor c_get_ship_method(p_carrier_id NUMBER,
958 p_service_level VARCHAR2,
959 p_mode_of_transport VARCHAR2,
960 p_organization_id NUMBER) IS
961 select wcs.ship_method_code
962 from wsh_org_carrier_services wocs,
963 wsh_carrier_services wcs
964 where wcs.carrier_id = p_carrier_id
965 and wcs.service_level = p_service_level
966 and wcs.mode_of_transport = p_mode_of_transport
967 and wcs.enabled_flag = 'Y'
968 and wcs.carrier_service_id = wocs.carrier_service_id
969 and wocs.organization_id = p_organization_id
970 and wocs.enabled_flag = 'Y';
971 --
972 l_debug_on BOOLEAN;
973 --
974 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_SHIP_METHOD_CODE';
975 --
976 BEGIN
977
978 --
979 -- Initialize the return parameters
980 --
981 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
982 --
983 IF l_debug_on IS NULL
984 THEN
985 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
986 END IF;
987 --
988 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
989 x_return_message := null;
990
991 IF l_debug_on THEN
992 WSH_DEBUG_SV.push(l_module_name);
993 WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Id '||p_carrier_id);
994 WSH_DEBUG_SV.logmsg(l_module_name, 'Service Level '||p_service_level);
995 WSH_DEBUG_SV.logmsg(l_module_name, 'Mode of transport '||p_mode_of_transport);
996 WSH_DEBUG_SV.logmsg(l_module_name, 'Organization Id '||p_org_id);
997 END IF;
998 --
999
1000 IF (p_carrier_id IS NOT NULL) THEN
1001
1002 OPEN c_get_ship_method(p_carrier_id,
1003 p_service_level,
1004 p_mode_of_transport,
1005 p_org_id);
1006 FETCH c_get_ship_method INTO x_ship_method_code;
1007 CLOSE c_get_ship_method;
1008
1009 ELSE
1010 x_ship_method_code := null;
1011 END IF; --
1012
1013 --
1014 -- Debug Statements
1015 --
1016 IF l_debug_on THEN
1017 WSH_DEBUG_SV.logmsg(l_module_name, 'Ship Method Code'||x_ship_method_code);
1018 WSH_DEBUG_SV.pop(l_module_name);
1019 END IF;
1020 --
1021
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 l_error_code := SQLCODE;
1025 l_error_text := SQLERRM;
1026 --
1027 -- Debug Statements
1028 --
1029 IF l_debug_on THEN
1030 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM WSH_UTIL_CORE.GET_SHIP_METHOD_CODE IS ' ||L_ERROR_TEXT );
1031 WSH_DEBUG_SV.pop(l_module_name);
1032 END IF;
1033 --
1034 WSH_UTIL_CORE.default_handler('WSH_UTIL_CORE.GET_SHIP_METHOD_CODE');
1035 FND_MESSAGE.SET_NAME('WSH','WSH_FTE_GET_SMC_ERROR');
1036 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1037 x_return_message := 'WSH_FTE_GET_SMC_ERROR';
1038 WSH_UTIL_CORE.add_message(x_return_status);
1039
1040 END GET_SHIP_METHOD_CODE;
1041
1042
1043 --***************************************************************************--
1044 --==========================================================================
1045 -- PROCEDURE : get_candidate_records PRIVATE
1046 --
1047 -- PARAMETERS: p_search_level Whether search has to be done at customer site, customer,
1048 -- organization or enterprise level.
1049 -- p_guery_gtt Should the GTT be used or not.
1050 -- p_single_rec This parameter is used for single records(GTT is not used)
1051 -- x_output_tab Output result table.
1052 -- x_return_status Return status
1053 --
1054 -- COMMENT : Determines the candidate records at the given level.
1055 -- a) For multiple records queries global temp table and returns the associated group
1056 -- b) For single record uses the group cache to determine the associated group.
1057 --
1058 -- Groups that lie within the following limits are valid at a level
1059 --
1060 -- START DATE | END DATE
1061 -- ----------------------------------------------------
1062 -- <= pickupdate | NULL
1063 -- <= pickupdate | >= pickupdate
1064 -- NULL | >= pickupdate
1065 -- NULL | NULL
1066 --
1067 --
1068 --***************************************************************************--
1069 PROCEDURE GET_CANDIDATE_RECORDS( p_search_level IN VARCHAR2,
1070 p_query_gtt IN BOOLEAN,
1071 p_single_rec IN FTE_ACS_PKG.FTE_CS_ENTITY_REC_TYPE DEFAULT NULL,
1072 x_output_tab OUT NOCOPY FTE_ACS_CACHE_PKG.FTE_CS_ENTITY_ATTR_TAB,
1073 x_return_status OUT NOCOPY VARCHAR2)
1074 IS
1075
1076
1077 CURSOR c_get_entity_by_site IS
1078 SELECT fsg.group_id,
1079 fsset.delivery_id,
1080 fsset.trip_id,
1081 fsset.gross_weight,
1082 fsset.weight_uom_code,
1083 fsset.volume,
1084 fsset.volume_uom_code,
1085 fsset.transit_time,
1086 fsset.initial_pickup_loc_id,
1087 fsset.ultimate_dropoff_loc_id,
1088 fsset.fob_code
1089 -- We do not fetch start date and end date here as we are not going to cache this information.
1090 FROM FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
1091 FTE_SEL_GROUP_ASSIGNMENTS FSGA,
1092 FTE_SEL_GROUPS FSG
1093 WHERE fsga.customer_site_id = fsset.customer_site_id
1094 AND fsga.group_id = fsg.group_id
1095 AND fsg.object_id = g_object_id
1096 --AND nvl(fsg.group_status_flag,'A') NOT IN ('D','I')
1097 AND nvl(fsg.group_status_flag,'A') = g_flag_active
1098 AND (fsg.start_date < fsset.initial_pickup_date OR
1099 fsg.start_date is null)
1100 AND (fsg.end_date > fsset.initial_pickup_date OR
1101 fsg.end_date is null)
1102 AND fsset.rule_id IS NULL
1103 AND fsset.search_level <> 'O'
1104 ORDER BY fsg.group_id;
1105
1106
1107 CURSOR c_get_entity_by_cust IS
1108 SELECT fsg.group_id,
1109 fsset.delivery_id,
1110 fsset.trip_id,
1111 fsset.gross_weight,
1112 fsset.weight_uom_code,
1113 fsset.volume,
1114 fsset.volume_uom_code,
1115 fsset.transit_time,
1116 fsset.initial_pickup_loc_id,
1117 fsset.ultimate_dropoff_loc_id,
1118 fsset.fob_code
1119 -- We do not fetch start date and end date here as we are not going to cache this information.
1120 FROM FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
1121 FTE_SEL_GROUP_ASSIGNMENTS FSGA,
1122 FTE_SEL_GROUPS FSG
1123 WHERE fsga.customer_id = fsset.customer_id
1124 AND fsga.group_id = fsg.group_id
1125 AND fsg.object_id = g_object_id
1126 --AND nvl(fsg.group_status_flag,'A') NOT IN ('D','I')
1127 AND nvl(fsg.group_status_flag,'A') = g_flag_active
1128 AND (fsg.start_date < fsset.initial_pickup_date OR
1129 fsg.start_date is null)
1130 AND (fsg.end_date > fsset.initial_pickup_date OR
1131 fsg.end_date is null)
1132 AND fsset.rule_id IS NULL
1133 AND fsset.search_level <>'O'
1134 ORDER BY fsg.group_id;
1135
1136
1137 CURSOR c_get_entity_by_org IS
1138 SELECT fsg.group_id,
1139 fsset.delivery_id,
1140 fsset.trip_id,
1141 fsset.gross_weight,
1142 fsset.weight_uom_code,
1143 fsset.volume,
1144 fsset.volume_uom_code,
1145 fsset.transit_time,
1146 fsset.initial_pickup_loc_id,
1147 fsset.ultimate_dropoff_loc_id,
1148 fsset.fob_code
1149 -- We do not fetch start date and end date here as we are not going to cache this information.
1150 FROM FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
1151 FTE_SEL_GROUP_ASSIGNMENTS FSGA,
1152 FTE_SEL_GROUPS FSG
1153 WHERE fsga.organization_id = fsset.organization_id
1154 AND fsga.group_id = fsg.group_id
1155 AND fsg.object_id = g_object_id
1156 --AND nvl(fsg.group_status_flag,'A') NOT IN ('D','I')
1157 AND nvl(fsg.group_status_flag,'A') = g_flag_active
1158 AND (fsg.start_date < fsset.initial_pickup_date OR
1159 fsg.start_date is null)
1160 AND (fsg.end_date > fsset.initial_pickup_date OR
1161 fsg.end_date is null)
1162 AND fsset.rule_id IS NULL
1163 ORDER BY fsg.group_id;
1164
1165
1166 --
1167 -- All entities that have a matching trip origin organziation id;
1168 --
1169 CURSOR c_get_entity_by_trip_org IS
1170 SELECT fsg.group_id,
1171 fsset.delivery_id,
1172 fsset.trip_id,
1173 fsset.gross_weight,
1174 fsset.weight_uom_code,
1175 fsset.volume,
1176 fsset.volume_uom_code,
1177 fsset.transit_time,
1178 fsset.initial_pickup_loc_id,
1179 fsset.ultimate_dropoff_loc_id,
1180 fsset.fob_code
1181 -- We do not fetch start date and end date here as we are not going to cache this information.
1182 FROM FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
1183 FTE_SEL_GROUP_ASSIGNMENTS FSGA,
1184 FTE_SEL_GROUPS FSG
1185 WHERE fsga.organization_id = fsset.triporigin_internalorg_id
1186 AND fsga.group_id = fsg.group_id
1187 AND fsg.object_id = g_object_id
1188 --AND nvl(fsg.group_status_flag,'A') NOT IN ('D','I')
1189 AND nvl(fsg.group_status_flag,'A') = g_flag_active
1190 AND (fsg.start_date < fsset.initial_pickup_date OR
1191 fsg.start_date is null)
1192 AND (fsg.end_date > fsset.initial_pickup_date OR
1193 fsg.end_date is null)
1194 AND fsset.rule_id IS NULL
1195 ORDER BY fsg.group_id;
1196
1197 --
1198 -- All the deliveries with enterprise rules have to be queried
1199 --
1200 CURSOR c_get_entity_by_ship IS
1201 SELECT fsg.group_id,
1202 fsset.delivery_id,
1203 fsset.trip_id,
1204 fsset.gross_weight,
1205 fsset.weight_uom_code,
1206 fsset.volume,
1207 fsset.volume_uom_code,
1208 fsset.transit_time,
1209 fsset.initial_pickup_loc_id,
1210 fsset.ultimate_dropoff_loc_id,
1211 fsset.fob_code
1212 -- We do not fetch start date and end date here as we are not going to cache this information.
1213 FROM FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
1214 FTE_SEL_GROUPS FSG,
1215 FTE_SEL_GROUP_ASSIGNMENTS assign
1216 WHERE --assigned_flag = 'E'
1217 --AND
1218 fsg.object_id = g_object_id
1219 --AND nvl(fsg.group_status_flag,'A') NOT IN ('D','I')
1220 AND nvl(fsg.group_status_flag,'A') = g_flag_active
1221 AND (fsg.start_date < fsset.initial_pickup_date OR
1222 fsg.start_date is null)
1223 AND (fsg.end_date > fsset.initial_pickup_date OR
1224 fsg.end_date is null)
1225 AND fsset.rule_id IS NULL
1226 and fsg.group_id = assign.group_id
1227 and assign.customer_id is null and assign.CUSTOMER_SITE_ID is null and assign.ORGANIZATION_ID is null
1228
1229 ORDER BY fsg.group_id;
1230
1231 --
1232 -- For individual entity - We will not be using GTT in this case.
1233 -- As this information is cached we need to maintain the start date and end date.
1234 --
1235 CURSOR c_get_site_group(p_customer_site_id NUMBER,
1236 p_ship_date DATE) IS
1237 SELECT fsg.group_id,
1238 fsg.start_date,
1239 fsg.end_date
1240 FROM fte_sel_group_assignments fsga,
1241 fte_sel_groups fsg
1242 WHERE fsga.customer_site_id = p_customer_site_id
1243 AND fsga.group_id = fsg.group_id
1244 AND fsg.object_id = g_object_id
1245 --AND nvl(fsg.group_status_flag,'A') NOT IN ('D','I')
1246 AND nvl(fsg.group_status_flag,'A') = g_flag_active
1247 AND (fsg.start_date < p_ship_date OR
1248 fsg.start_date is null)
1249 AND (fsg.end_date > p_ship_date OR
1250 fsg.end_date is null);
1251
1252 --
1253 -- For Customers
1254 --
1255 CURSOR c_get_cust_group(p_customer_id NUMBER,
1256 p_ship_date DATE) IS
1257 SELECT fsg.group_id,
1258 fsg.start_date,
1259 fsg.end_date
1260 FROM fte_sel_group_assignments fsga,
1261 fte_sel_groups fsg
1262 WHERE fsga.customer_id = p_customer_id
1263 AND fsga.group_id = fsg.group_id
1264 AND fsg.object_id = g_object_id
1265 --AND nvl(fsg.group_status_flag,'A') NOT IN ('D','I')
1266 AND nvl(fsg.group_status_flag,'A') = g_flag_active
1267 AND (fsg.start_date < p_ship_date OR
1268 fsg.start_date is null)
1269 AND (fsg.end_date > p_ship_date OR
1270 fsg.end_date is null);
1271
1272 --
1273 -- For Organizations , The Same cursor can be used for using Trip Internal Organzations
1274 --
1275 CURSOR c_get_org_group(p_org_id NUMBER,
1276 p_ship_date DATE) IS
1277 SELECT fsg.group_id,
1278 fsg.start_date,
1279 fsg.end_date
1280 FROM fte_sel_group_assignments fsga,
1281 fte_sel_groups fsg
1282 WHERE fsga.organization_id = p_org_id
1283 AND fsga.group_id = fsg.group_id
1284 AND fsg.object_id = g_object_id
1285 --AND nvl(fsg.group_status_flag,'A') NOT IN ('D','I')
1286 AND nvl(fsg.group_status_flag,'A') = g_flag_active
1287 AND (fsg.start_date < p_ship_date OR
1288 fsg.start_date is null)
1289 AND (fsg.end_date > p_ship_date OR
1290 fsg.end_date is null);
1291
1292 --
1293 -- For Enterprise
1294 --
1295 CURSOR c_get_ship_group(p_ship_date DATE) IS
1296 SELECT fsg.group_id,
1297 fsg.start_date,
1298 fsg.end_date
1299 FROM fte_sel_groups fsg,
1300 FTE_SEL_GROUP_ASSIGNMENTS assign
1301 WHERE --assigned_flag = 'E'
1302 --AND
1303 fsg.object_id = g_object_id
1304 --AND nvl(fsg.group_status_flag,'A') NOT IN ('D','I')
1305 AND nvl(fsg.group_status_flag,'A') = g_flag_active
1306 AND (fsg.start_date < p_ship_date OR
1307 fsg.start_date is null)
1308 AND (fsg.end_date > p_ship_date OR
1309 fsg.end_date is null)
1310 and fsg.group_id = assign.group_id
1311 and assign.customer_id is null and assign.CUSTOMER_SITE_ID is null and assign.ORGANIZATION_ID is null;
1312
1313 itr NUMBER;
1314
1315 l_group_rec_tab FTE_CS_GROUP_REC_TAB;
1316 l_group_rec FTE_CS_GROUP_REC_TYPE;
1317 l_organization_id NUMBER;
1318
1319
1320 l_result_found BOOLEAN := FALSE;
1321 l_group_id NUMBER;
1322
1323 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1324 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||'GET_CANDIDATE_RECORDS';
1325 BEGIN
1326
1327 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1328
1329 IF l_debug_on THEN
1330
1331 wsh_debug_sv.push(l_module_name);
1332 WSH_DEBUG_SV.log(l_module_name,'p_search_level',p_search_level);
1333
1334 IF (p_query_gtt) THEN
1335 WSH_DEBUG_SV.logmsg(l_module_name,'Use Global Temporary Table');
1336 ELSE
1337 WSH_DEBUG_SV.logmsg(l_module_name,'Use Single Record');
1338 END IF;
1339
1340 END IF;
1341
1342 --
1343 -- If we are using GTT then we do not need to store this information anywhere else.
1344 --
1345 IF (p_query_gtt) THEN
1346
1347 IF (p_search_level = 'S') THEN
1348
1349 OPEN c_get_entity_by_site;
1350 FETCH c_get_entity_by_site BULK COLLECT INTO x_output_tab;
1351 CLOSE c_get_entity_by_site;
1352
1353 ELSIF (p_search_level ='C') THEN
1354
1355 OPEN c_get_entity_by_cust;
1356 FETCH c_get_entity_by_cust BULK COLLECT INTO x_output_tab;
1357 CLOSE c_get_entity_by_cust;
1358
1359 ELSIF (p_search_level ='O') THEN
1360
1361 OPEN c_get_entity_by_org;
1362 FETCH c_get_entity_by_org BULK COLLECT INTO x_output_tab;
1363 CLOSE c_get_entity_by_org;
1364
1365 ELSIF (p_search_level ='E') THEN
1366
1367 OPEN c_get_entity_by_ship;
1368 FETCH c_get_entity_by_ship BULK COLLECT INTO x_output_tab;
1369 CLOSE c_get_entity_by_ship;
1370
1371 ElSIF (p_search_level ='I') THEN
1372 --
1373 -- We cannot use same cursor as organization ,because column in the
1374 -- join changes here
1375 --
1376 OPEN c_get_entity_by_trip_org;
1377 FETCH c_get_entity_by_trip_org BULK COLLECT INTO x_output_tab;
1378 CLOSE c_get_entity_by_trip_org;
1379
1380 END IF;
1381 ELSE
1382
1383 -- Single Record.
1384 -- And start level of the delivery is not 'O'
1385 IF (p_search_level = 'S' AND p_single_rec.start_search_level <> 'O') THEN
1386
1387 IF (g_site_cache_tab.EXISTS(p_single_rec.customer_site_id)) THEN
1388
1389 l_group_rec_tab := g_site_cache_tab(p_single_rec.customer_site_id);
1390 itr := l_group_rec_tab.FIRST;
1391
1392 IF (itr IS NOT NULL) THEN
1393 LOOP
1394 l_group_rec := l_group_rec_tab(itr);
1395
1396 IF (l_group_rec.start_date IS NULL AND (l_group_rec.end_date IS NULL OR l_group_rec.end_date >= p_single_rec.initial_pickup_date))
1397 OR (l_group_rec.start_date <= p_single_rec.initial_pickup_date AND( l_group_rec.end_date IS NULL OR l_group_rec.end_date >= p_single_rec.initial_pickup_date))
1398 THEN
1399 l_group_id := l_group_rec.group_id;
1400 l_result_found := TRUE;
1401 EXIT ;
1402 END IF;
1403 EXIT WHEN itr = l_group_rec_tab.LAST;
1404 itr := l_group_rec_tab.NEXT(itr);
1405 END LOOP;
1406 END IF;
1407 END IF;
1408
1409 --
1410 -- Record could have been there in the cache.But its not necessary that a match will happen
1411 -- Dates may not match
1412 --
1413 IF NOT(l_result_found) THEN
1414
1415 OPEN c_get_site_group( p_customer_site_id => p_single_rec.customer_site_id,
1416 p_ship_date => nvl(p_single_rec.initial_pickup_date,sysdate));
1417
1418 FETCH c_get_site_group INTO l_group_rec;
1419 CLOSE c_get_site_group;
1420
1421 IF (l_group_rec.group_id IS NOT NULL) THEN
1422 l_result_found := TRUE;
1423 l_group_id := l_group_rec.group_id;
1424
1425 -- This may be the first time the database query is run
1426 IF (g_site_cache_tab.EXISTS(p_single_rec.customer_site_id)) THEN
1427 l_group_rec_tab := g_site_cache_tab(p_single_rec.customer_site_id);
1428 l_group_rec_tab(l_group_rec_tab.COUNT+1) := l_group_rec;
1429 g_site_cache_tab(p_single_rec.customer_site_id) := l_group_rec_tab;
1430 ELSE
1431 l_group_rec_tab(0) := l_group_rec;
1432 g_site_cache_tab(p_single_rec.customer_site_id) := l_group_rec_tab;
1433 END IF;
1434
1435 END IF;
1436 END IF;
1437
1438 ELSIF (p_search_level ='C' AND p_single_rec.start_search_level <> 'O') THEN
1439
1440 IF (g_cust_cache_tab.EXISTS(p_single_rec.customer_id)) THEN
1441
1442 l_group_rec_tab := g_cust_cache_tab(p_single_rec.customer_id);
1443 itr := l_group_rec_tab.FIRST;
1444 IF (itr IS NOT NULL) THEN
1445 LOOP
1446 l_group_rec := l_group_rec_tab(itr);
1447 IF (l_group_rec.start_date IS NULL AND (l_group_rec.end_date IS NULL OR l_group_rec.end_date >= p_single_rec.initial_pickup_date))
1448 OR (l_group_rec.start_date <= p_single_rec.initial_pickup_date AND ( l_group_rec.end_date IS NULL OR l_group_rec.end_date >= p_single_rec.initial_pickup_date))
1449 THEN
1450 l_group_id := l_group_rec.group_id;
1451 l_result_found := TRUE;
1452 EXIT ;
1453 END IF;
1454 EXIT WHEN itr = l_group_rec_tab.LAST;
1455 itr := l_group_rec_tab.NEXT(itr);
1456 END LOOP;
1457 END IF;
1458 END IF;
1459
1460 IF NOT(l_result_found) THEN
1461
1462 OPEN c_get_cust_group(p_customer_id => p_single_rec.customer_id,
1463 p_ship_date => nvl(p_single_rec.initial_pickup_date,sysdate));
1464 FETCH c_get_cust_group INTO l_group_rec;
1465 CLOSE c_get_cust_group;
1466
1467 IF (l_group_rec.group_id IS NOT NULL) THEN
1468 l_result_found := TRUE;
1469 l_group_id := l_group_rec.group_id;
1470
1471 IF (g_cust_cache_tab.EXISTS(p_single_rec.customer_id)) THEN
1472 l_group_rec_tab := g_cust_cache_tab(p_single_rec.customer_id);
1473 l_group_rec_tab(l_group_rec_tab.COUNT+1) := l_group_rec;
1474 g_cust_cache_tab(p_single_rec.customer_id) := l_group_rec_tab;
1475 ELSE
1476 l_group_rec_tab(0) := l_group_rec;
1477 g_cust_cache_tab(p_single_rec.customer_id) := l_group_rec_tab;
1478 END IF;
1479 END IF;
1480
1481 END IF;
1482
1483 ELSIF (p_search_level ='O' OR p_search_level= 'I') THEN
1484
1485
1486 IF (p_search_level = 'O' ) THEN
1487 l_organization_id := p_single_rec.organization_id ;
1488 ELSE
1489 l_organization_id := p_single_rec.triporigin_internalorg_id;
1490 END IF;
1491
1492
1493 IF (g_org_cache_tab.EXISTS(l_organization_id)) THEN
1494 l_group_rec_tab := g_org_cache_tab(l_organization_id);
1495 itr := l_group_rec_tab.FIRST;
1496
1497 IF (itr IS NOT NULL) THEN
1498 LOOP
1499 l_group_rec := l_group_rec_tab(itr);
1500
1501 IF (l_group_rec.start_date IS NULL AND (l_group_rec.end_date IS NULL OR l_group_rec.end_date >= p_single_rec.initial_pickup_date))
1502 OR (l_group_rec.start_date <= p_single_rec.initial_pickup_date AND ( l_group_rec.end_date IS NULL OR l_group_rec.end_date >= p_single_rec.initial_pickup_date))
1503 THEN
1504 l_group_id := l_group_rec.group_id;
1505 l_result_found := TRUE;
1506 EXIT ;
1507 END IF;
1508
1509 EXIT WHEN itr = l_group_rec_tab.LAST;
1510 itr := l_group_rec_tab.NEXT(itr);
1511 END LOOP;
1512 END IF;
1513 END IF;
1514
1515 IF NOT(l_result_found) THEN
1516
1517 OPEN c_get_org_group( p_org_id => l_organization_id,
1518 p_ship_date => nvl(p_single_rec.initial_pickup_date,sysdate));
1519 FETCH c_get_org_group INTO l_group_rec;
1520 CLOSE c_get_org_group;
1521
1522 IF (l_group_rec.group_id IS NOT NULL) THEN
1523 l_result_found := TRUE;
1524 l_group_id := l_group_rec.group_id;
1525
1526 IF (g_org_cache_tab.EXISTS(l_organization_id)) THEN
1527 l_group_rec_tab := g_org_cache_tab(l_organization_id);
1528 l_group_rec_tab(l_group_rec_tab.COUNT+1) := l_group_rec;
1529 g_org_cache_tab(l_organization_id) := l_group_rec_tab;
1530 ELSE
1531 l_group_rec_tab(0) := l_group_rec;
1532 g_org_cache_tab(l_organization_id) := l_group_rec_tab;
1533 END IF;
1534 END IF;
1535 END IF;
1536
1537 ELSIF (p_search_level ='E') THEN
1538
1539 --
1540 -- Here we will not index by id.
1541 -- The treatement will be different here
1542 -- We have only 1 ENTERPRISE;
1543 --
1544 itr := g_ship_cache_tab.FIRST;
1545 IF (itr IS NOT NULL) THEN
1546 LOOP
1547 l_group_rec := g_ship_cache_tab(itr);
1548 IF (l_group_rec.start_date IS NULL AND (l_group_rec.end_date IS NULL OR l_group_rec.end_date >= p_single_rec.initial_pickup_date))
1549 OR(l_group_rec.start_date <= p_single_rec.initial_pickup_date AND ( l_group_rec.end_date IS NULL OR l_group_rec.end_date >= p_single_rec.initial_pickup_date))
1550 THEN
1551 l_group_id := l_group_rec.group_id;
1552 l_result_found := TRUE;
1553 EXIT ;
1554 END IF;
1555 EXIT WHEN itr = g_ship_cache_tab.LAST;
1556 itr := g_ship_cache_tab.NEXT(itr);
1557 END LOOP;
1558 END IF;
1559
1560 IF NOT(l_result_found) THEN
1561
1562 OPEN c_get_ship_group( p_ship_date => nvl(p_single_rec.initial_pickup_date,sysdate));
1563 FETCH c_get_ship_group INTO l_group_rec;
1564 CLOSE c_get_ship_group;
1565
1566 IF (l_group_rec.group_id IS NOT NULL) THEN
1567 l_group_id := l_group_rec.group_id;
1568 l_result_found := TRUE;
1569 g_ship_cache_tab(g_ship_cache_tab.COUNT) := l_group_rec;
1570 END IF;
1571 END IF;
1572
1573 END IF;--(if p_search_level)
1574
1575 IF (l_result_found) THEN
1576 --
1577 -- Assign values to out records.
1578 --
1579 x_output_tab(1).group_id := l_group_id;
1580 x_output_tab(1).delivery_id := p_single_rec.delivery_id;
1581 x_output_tab(1).trip_id := p_single_rec.trip_id;
1582 x_output_tab(1).weight := p_single_rec.gross_weight;
1583 x_output_tab(1).weight_uom_code := p_single_rec.weight_uom_code;
1584 x_output_tab(1).volume := p_single_rec.volume;
1585 x_output_tab(1).volume_uom_code := p_single_rec.volume_uom_code;
1586 x_output_tab(1).transit_time := p_single_rec.transit_time;
1587 x_output_tab(1).ship_from_location_id := p_single_rec.initial_pickup_loc_id;
1588 x_output_tab(1).ship_to_location_id := p_single_rec.ultimate_dropoff_loc_id;
1589 x_output_tab(1).fob_code := p_single_rec.fob_code;
1590 END IF;
1591
1592 END IF; --IF (p_query_gtt) THEN
1593
1594 IF l_debug_on THEN
1595 WSH_DEBUG_SV.log(l_module_name, 'CANDIDATE RECORDS RETURNED',x_output_tab.COUNT);
1596 WSH_DEBUG_SV.pop(l_module_name);
1597 END IF;
1598
1599 EXCEPTION
1600 WHEN others THEN
1601
1602 IF (c_get_entity_by_site%ISOPEN) THEN
1603 CLOSE c_get_entity_by_site;
1604 END IF;
1605
1606 IF (c_get_entity_by_cust%ISOPEN) THEN
1607 CLOSE c_get_entity_by_cust ;
1608 END IF;
1609
1610 IF (c_get_entity_by_org%ISOPEN) THEN
1611 CLOSE c_get_entity_by_org;
1612 END IF;
1613
1614 IF (c_get_entity_by_ship%ISOPEN) THEN
1615 CLOSE c_get_entity_by_ship ;
1616 END IF;
1617
1618 IF (c_get_site_group%ISOPEN) THEN
1619 CLOSE c_get_site_group;
1620 END IF;
1621
1622 IF ( c_get_cust_group%ISOPEN) THEN
1623 CLOSE c_get_cust_group;
1624 END IF;
1625
1626 IF ( c_get_org_group%ISOPEN) THEN
1627 CLOSE c_get_org_group;
1628 END IF;
1629
1630 IF (c_get_ship_group%ISOPEN) THEN
1631 CLOSE c_get_ship_group ;
1632 END IF;
1633
1634 WSH_UTIL_CORE.default_handler('FTE_ACS_UTIL_PKG.get_candidate_records');
1635 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1636 --
1637 IF l_debug_on THEN
1638 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1639 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1640 END IF;
1641 --
1642 END GET_CANDIDATE_RECORDS;
1643
1644 --
1645 -- R12 End Enhancement
1646 --
1647 END FTE_ACS_RULE_UTIL_PKG;
[Home] [Help]