1 PACKAGE BODY WSH_MAP_LOCATION_REGION_PKG AS
2 /* $Header: WSHMLORB.pls 120.16.12010000.2 2008/08/04 12:31:42 suppal ship $ */
3
4 /*===========================================================================+
5 | PROCEDURE |
6 | Map_Locations |
7 | |
8 | DESCRIPTION |
9 | This procedure selects the minimum and maximum location id |
10 | and fires the child concurrent program depending on the |
11 | value of parameter p_num_of_instances |
12 | |
13 +===========================================================================*/
14
15 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_MAP_LOCATION_REGION_PKG';
16
17 -- Bug 4722963
18 -- Tables to be used for bulk operations on WSH_REGION_LOCATIONS in APIs, insert_records/ update_records / delete_records
19 l_location_id_tab TableNumbers;
20 l_region_id_tab TableNumbers;
21 l_region_type_tab TableNumbers;
22 l_exceptions_tab TableVarchar;
23 l_location_source_tab TableVarchar;
24 l_parent_region_tab TableVarchar;
25
26 /*TYPE upd_loc_id_rec IS RECORD
27 (
28 l_upd_loc_id NUMBER,
29 l_log_excep BOOLEAN
30 );*/
31
32 --TYPE upd_loc_id_tab IS TABLE OF upd_loc_id_rec INDEX BY BINARY_INTEGER;
33
34 --l_upd_loc_id_tab upd_loc_id_tab;
35 l_del_loc_id_tab TableNumbers;
36 l_upd_loc_id_tab TableNumbers;
37 l_upd_loc_excp_tab TableBoolean;
38
39
40 -- Bug 4722963 end
41
42 PROCEDURE Map_Locations (
43 p_errbuf OUT NOCOPY VARCHAR2,
44 p_retcode OUT NOCOPY NUMBER,
45 p_map_regions IN VARCHAR2,
46 p_location_type IN VARCHAR2,
47 p_num_of_instances IN NUMBER,
48 p_start_date IN VARCHAR2,
49 p_end_date IN VARCHAR2,
50 p_fte_installed IN VARCHAR2 default NULL,
51 p_create_facilities IN VARCHAR2 default NULL) IS
52
53
54 l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
55 l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
56 l_new_request_id NUMBER := 0;
57 i NUMBER := 0;
58 l_sqlcode NUMBER;
59 l_sqlerr VARCHAR2(2000);
60 l_return_status VARCHAR2(10);
61 l_completion_status VARCHAR2(30);
62 l_retcode NUMBER;
63 l_errbuf VARCHAR2(2000);
64 l_num_of_instances NUMBER;
65 l_mode VARCHAR2(30);
66 l_insert_flag VARCHAR2(1);
67 l_debug_on BOOLEAN;
68 l_req_data VARCHAR2(50);
69 l_temp BOOLEAN;
70 l_this_request NUMBER;
71 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_LOCATIONS';
72 l_start_date DATE;
73 l_end_date DATE;
74 l_import_start_date Date;
75
76 BEGIN
77
78 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
79 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
80
81 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
82 IF l_debug_on IS NULL THEN
83 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
84 END IF;
85
86 IF l_debug_on THEN
87 WSH_DEBUG_SV.push(l_module_name);
88 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
89 WSH_DEBUG_SV.log(l_module_name,'P_MAP_REGIONS',P_MAP_REGIONS);
90 WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',P_NUM_OF_INSTANCES);
91 WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
92 WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
93 WSH_DEBUG_SV.log(l_module_name,'P_FTE_INSTALLED',P_FTE_INSTALLED);
94 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_FACILITIES',P_CREATE_FACILITIES);
95 END IF;
96
97 l_mode := 'MAP';
98 l_completion_status := 'NORMAL';
99
100 IF p_num_of_instances is null or p_num_of_instances = 0 then
101 l_num_of_instances := 1;
102 ELSE
103 l_num_of_instances := p_num_of_instances;
104 END IF;
105
106 -- Bug 4740786
107 l_req_data := fnd_conc_global.request_data;
108
109 IF l_debug_on THEN
110 WSH_DEBUG_SV.log(l_module_name, 'l_req_data', l_req_data);
111 END IF;
112
113 IF l_req_data IS NOT NULL THEN
114 l_req_data := SUBSTR(l_req_data, 1,1);
115 END IF;
116
117 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
118 l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
119
120 IF l_debug_on THEN
121 WSH_DEBUG_SV.log(l_module_name,'l_START_DATE',l_START_DATE);
122 WSH_DEBUG_SV.log(l_module_name,'l_END_DATE',l_END_DATE);
123 END IF;
124
125 -- If l_req_data is not null, that means, import shipping locations has been executed.
126 -- Call Location to region Mapping concurrent program and return.
127 IF l_req_data IS NULL THEN
128 IF p_location_type = 'EXTERNAL' THEN
129
130 IF l_debug_on THEN
131 WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is External');
132 END IF;
133
134 EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
135 FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
136 FROM HZ_LOCATIONS
137 WHERE last_update_date >= nvl(:start_date, last_update_date)
138 AND last_update_date < nvl(:end_date, last_update_date+1)
139 )
140 GROUP BY WORKER'
141 BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
142 USING l_num_of_instances, l_start_date, l_end_date;
143
144 ELSIF p_location_type = 'INTERNAL' THEN
145
146 IF l_debug_on THEN
147 WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is Internal');
148 END IF;
149
150 EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
151 FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
152 FROM HR_LOCATIONS_ALL
153 WHERE last_update_date >= nvl(:start_date, last_update_date)
154 AND last_update_date < nvl(:end_date, last_update_date+1)
155 )
156 GROUP BY WORKER'
157 BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
158 USING l_num_of_instances, l_start_date, l_end_date;
159
160 ELSIF p_location_type = 'BOTH' THEN
161
162 IF l_debug_on THEN
163 WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is Both');
164 END IF;
165
166 EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
167 FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
168 FROM WSH_HR_LOCATIONS_V
169 WHERE last_update_date >= nvl(:start_date, last_update_date)
170 AND last_update_date < nvl(:end_date, last_update_date+1)
171 )
172 GROUP BY WORKER'
173 BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
174 USING l_num_of_instances, l_start_date, l_end_date;
175
176
177 END IF;
178
179 IF l_debug_on THEN
180 WSH_DEBUG_SV.logmsg(l_module_name, 'l_worker_min_tab.count : '||l_worker_min_tab.count||
181 ' l_worker_max_tab.count : '||l_worker_max_tab.count || ' p_num_of_instances : '||p_num_of_instances ||
182 ' l_num_of_instances : ' ||l_num_of_instances);
183 END IF;
184
185 l_import_start_date := sysdate;
186
187 IF l_debug_on THEN
188 wsh_debug_sv.log(l_module_name, 'l_import_start_date', l_import_start_date);
189 END IF;
190
191 IF l_worker_min_tab.count <>0 and p_num_of_instances > 0 THEN
192
193 FOR i in 1..l_worker_min_tab.count
194 LOOP
195
196 IF l_debug_on THEN
197 WSH_DEBUG_SV.logmsg(l_module_name, 'Value of i : '|| i ||' l_worker_min_tab(i) : '||l_worker_min_tab(i)||
198 ' l_worker_max_tab(i) : '||l_worker_max_tab(i));
199 END IF;
200
201 l_new_request_id := FND_REQUEST.SUBMIT_REQUEST(
202 application => 'WSH',
203 program => 'WSHMAPCD',
204 description => 'Import Shipping Locations - Child '||to_char(i),
205 start_time => NULL,
206 sub_request => TRUE,
207 argument1 => p_location_type,
208 argument2 => p_map_regions,
209 argument3 => l_worker_min_tab(i),
210 argument4 => l_worker_max_tab(i),
211 argument5 => p_start_date,
212 argument6 => p_end_date,
213 argument7 => p_create_facilities);
214
215 IF l_debug_on THEN
216 WSH_DEBUG_SV.log(l_module_name, 'Child request ID ', l_new_request_id);
217 END IF;
218
219 IF l_new_request_id = 0 THEN
220 WSH_UTIL_CORE.printmsg('Error Submitting concurrent request for worker : '||i);
221 l_completion_status := 'ERROR';
222 END IF;
223
224 END LOOP;
225
226 IF l_debug_on THEN
227 WSH_DEBUG_SV.logmsg(l_module_name,'Setting Parent Request to pause');
228 END IF;
229
230 FND_CONC_GLOBAL.Set_Req_Globals ( Conc_Status => 'PAUSED', Request_Data => to_char(1)||':'|| to_char(l_import_start_date, 'YYYY/MM/DD HH24:MI:SS'));
231
232 ELSIF l_worker_min_tab.count <>0 AND nvl(p_num_of_instances,0) = 0 THEN
233
234 IF l_debug_on THEN
235 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Map_Locations_Child_Program',WSH_DEBUG_SV.C_PROC_LEVEL);
236 WSH_DEBUG_SV.log(l_module_name,'l_worker_min_tab(1): ', l_worker_min_tab(1));
237 WSH_DEBUG_SV.log(l_module_name,'l_worker_max_tab(1): ', l_worker_max_tab(1));
238 END IF;
239
240 Map_Locations_Child_Program (
241 p_errbuf => l_errbuf,
242 p_retcode => l_retcode,
243 p_location_type => p_location_type,
244 p_map_regions => p_map_regions,
245 p_from_location => l_worker_min_tab(1),
246 p_to_location => l_worker_max_tab(1),
247 p_start_date => p_start_date,
248 p_end_date => p_end_date,
249 p_create_facilities => p_create_facilities );
250
251 IF l_debug_on THEN
252 WSH_DEBUG_SV.logmsg(l_module_name,'Return Code from Map_Locations_Child_Program : '||l_retcode);
253 END IF;
254
255 IF l_retcode = '2' THEN
256 l_completion_status := 'ERROR';
257 ELSIF l_retcode = '1' THEN
258 l_completion_status := 'WARNING';
259 END IF;
260
261 IF p_map_regions = 'Y' THEN
262
263 WSH_REGIONS_SEARCH_PKG.Process_All_Locations(
264 p_dummy1 => NULL,
265 p_dummy2 => NULL,
266 p_mode => l_mode,
267 p_insert_flag => l_insert_flag,
268 p_location_type => p_location_type,
269 p_start_date => p_start_date,
270 p_end_date => p_end_date,
271 p_num_of_instances => p_num_of_instances
272 );
273 END IF;
274
275 END IF;
276
277 ELSIF l_req_data = '1' and p_map_regions = 'Y' THEN
278
279 WSH_REGIONS_SEARCH_PKG.Process_All_Locations(
280 p_dummy1 => NULL,
281 p_dummy2 => NULL,
282 p_mode => l_mode,
283 p_insert_flag => l_insert_flag,
284 p_location_type => p_location_type,
285 p_start_date => p_start_date,
286 p_end_date => p_end_date,
287 p_num_of_instances => p_num_of_instances
288 );
289 ELSE
290 WSH_REGIONS_SEARCH_PKG.get_child_requests_status(x_completion_status => l_completion_status);
291
292 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
293 END IF;
294
295 IF l_debug_on THEN
296 WSH_DEBUG_SV.log(l_module_name,'ERRBUF',p_errbuf);
297 WSH_DEBUG_SV.log(l_module_name,'RETCODE',p_retcode);
298 WSH_DEBUG_SV.pop(l_module_name);
299 END IF;
300
301 -- end Bug 4740786
302 EXCEPTION
303
304 WHEN No_Data_Found THEN
305
306 WSH_UTIL_CORE.printmsg('No matching records for the entered parameters');
307 IF l_debug_on THEN
308 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
309 END IF;
310
311 WHEN others THEN
312 l_sqlcode := SQLCODE;
313 l_sqlerr := SQLERRM;
314
315 WSH_UTIL_CORE.printmsg('Exception occurred in Map_Locations Program');
316 WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
317 WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
318
319 l_completion_status := 'ERROR';
320 p_errbuf := 'Exception occurred in Map_Locations Program';
321 p_retcode := '2';
322
323 IF l_debug_on THEN
324 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
325 END IF;
326
327 END Map_Locations;
328
329 /*===========================================================================+
330 | PROCEDURE |
331 | Map_Location_Child_Program |
332 | |
333 | DESCRIPTION |
334 | This is just a wrapper routine and call the main processing |
335 | API Mapping_Regions_Main. This procedure is also by the |
336 | TCA Callout API Rule_Location. |
337 | |
338 +===========================================================================*/
339
340 -- Will the conc program fail because of the new parameter
341 PROCEDURE Map_Locations_Child_Program (
342 p_errbuf OUT NOCOPY VARCHAR2,
343 p_retcode OUT NOCOPY NUMBER,
344 p_location_type IN VARCHAR2,
345 p_map_regions IN VARCHAR2,
346 p_from_location IN NUMBER,
347 p_to_location IN NUMBER,
348 p_start_date IN VARCHAR2,
349 p_end_date IN VARCHAR2,
350 p_create_facilities IN VARCHAR2 default NULL) IS
351
352 l_return_status VARCHAR2(20);
353 l_sqlcode NUMBER;
354 l_sqlerr VARCHAR2(2000);
355 l_completion_status VARCHAR2(30);
356 l_temp BOOLEAN;
357
358
359 l_debug_on BOOLEAN;
360 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_LOCATIONS_CHILD_PROGRAM';
361
362 BEGIN
363
364
365 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
366
367 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
368 IF l_debug_on IS NULL THEN
369 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
370 END IF;
371 IF l_debug_on THEN
372 WSH_DEBUG_SV.push(l_module_name);
373 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
374 WSH_DEBUG_SV.log(l_module_name,'P_MAP_REGIONS',P_MAP_REGIONS);
375 WSH_DEBUG_SV.log(l_module_name,'P_FROM_LOCATION',P_FROM_LOCATION);
376 WSH_DEBUG_SV.log(l_module_name,'P_TO_LOCATION',P_TO_LOCATION);
377 WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
378 WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
379 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_FACILITIES',P_CREATE_FACILITIES);
380 END IF;
381
382 l_completion_status := 'NORMAL';
383
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LOCATIONS_PKG.Process_Locations',WSH_DEBUG_SV.C_PROC_LEVEL);
386 END IF;
387 WSH_LOCATIONS_PKG.Process_Locations (
388 p_location_type => p_location_type,
389 p_from_location => p_from_location,
390 p_to_location => p_to_location,
391 p_start_date => p_start_date,
392 p_end_date => p_end_date,
393 p_create_facilities => p_create_facilities,
394 x_return_status => l_return_status,
395 x_sqlcode => l_sqlcode,
396 x_sqlerr => l_sqlerr);
397
398 IF l_debug_on THEN
399 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_LOCATIONS_PKG.Process_Locations : '|| l_return_status);
400 END IF;
401
402
403 IF l_return_status NOT IN
404 (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
405
406 WSH_UTIL_CORE.printmsg('Failed in Procedure Process_Locations');
407 WSH_UTIL_CORE.printmsg(l_sqlcode);
408 WSH_UTIL_CORE.printmsg(l_sqlerr);
409 l_completion_status := 'ERROR';
410 END IF;
411
412 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
413
414 IF l_completion_status = 'NORMAL' THEN
415 p_errbuf := 'Map_Locations_Child_Program completed successfully';
416 p_retcode := '0';
417 ELSIF l_completion_status = 'WARNING' THEN
418 p_errbuf := 'Map_Locations_Child_Program is completed with warning';
419 p_retcode := '1';
420 ELSIF l_completion_status = 'ERROR' THEN
421 p_errbuf := 'Map_Locations_Child_Program is completed with error';
422 p_retcode := '2';
423 END IF;
424
425
426 IF l_debug_on THEN
427 WSH_DEBUG_SV.log(l_module_name,'ERRBUF',p_errbuf);
428 WSH_DEBUG_SV.log(l_module_name,'RETCODE',p_retcode);
429 WSH_DEBUG_SV.pop(l_module_name);
430 END IF;
431
432 EXCEPTION
433 WHEN others THEN
434 l_completion_status := 'ERROR';
435 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
436 p_errbuf := 'Exception occurred in Map_Locations_Child_Program';
437 p_retcode := '2';
438
439 l_sqlcode := SQLCODE;
440 l_sqlerr := SQLERRM;
441 WSH_UTIL_CORE.printmsg('Exception occurred in Map_Locations_Child_Program');
442 WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
443 WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
444 IF l_debug_on THEN
445 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
446 END IF;
447
448 END Map_Locations_Child_Program;
449
450 -- Bug 4722963
451
452 /*===========================================================================+
453 | PROCEDURE |
454 | Insert_records |
455 | |
456 | DESCRIPTION |
457 | This API bulk inserts data into WSH_REGION_LOCATIONS table |
458 | |
459 +===========================================================================*/
460
461 PROCEDURE Insert_records
462 (
463 x_return_status OUT NOCOPY VARCHAR2
464 ) IS
465
466 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_RECORDS';
467 l_exception_msg_count NUMBER;
468 l_debug_on BOOLEAN;
469
470 BEGIN
471
472 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
473
474 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
475 IF l_debug_on IS NULL THEN
476 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
477 END IF;
478
479 IF l_debug_on THEN
480 WSH_DEBUG_SV.push(l_module_name);
481 wsh_debug_sv.logmsg(l_module_name, 'before bulk insert:' ||to_char(sysdate, 'dd/mm/yyyy hh:mi:ss'));
482 wsh_debug_sv.logmsg(l_module_name, 'Number of records to be bulk inserted '||l_location_id_tab.count);
483 END IF;
484
485 FORALL i IN l_location_id_tab.first .. l_location_id_tab.last
486
487 INSERT INTO WSH_REGION_LOCATIONS(
488 region_id,
489 location_id,
490 exception_type,
491 region_type,
492 parent_region_flag,
493 location_source,
494 creation_date,
495 created_by,
496 last_update_date,
497 last_updated_by,
498 last_update_login )
499 values (
500 l_region_id_tab(i),
501 l_location_id_tab(i),
502 l_exceptions_tab(i),
503 l_region_type_tab(i),
504 l_parent_region_tab(i),
505 l_location_source_tab(i),
506 sysdate,
507 fnd_global.user_id,
508 sysdate,
509 fnd_global.user_id,
510 fnd_global.login_id
511 );
512
513
514 IF l_debug_on THEN
515 wsh_debug_sv.logmsg(l_module_name, 'after bulk insert:' ||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'));
516 WSH_DEBUG_SV.pop(l_module_name);
517 END IF;
518 EXCEPTION
519
520 WHEN Others THEN
521
522 WSH_UTIL_CORE.printmsg(' Error in Insert_records : ' || sqlerrm);
523 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
524 IF l_debug_on THEN
525 WSH_DEBUG_SV.pop(l_module_name);
526 END IF;
527
528 END Insert_records;
529
530 /*===========================================================================+
531 | PROCEDURE |
532 | Delete_records |
533 | |
534 | DESCRIPTION |
535 | This API bulk delete records from WSH_REGION_LOCATIONS table |
536 | |
537 +===========================================================================*/
538
539 PROCEDURE Delete_records
540 (
541 x_return_status OUT NOCOPY VARCHAR2
542 ) IS
543
544 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_RECORDS';
545 l_exception_msg_count NUMBER;
546 l_debug_on BOOLEAN;
547
548 BEGIN
549 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
550 IF l_debug_on IS NULL THEN
551 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
552 END IF;
553
554 IF l_debug_on THEN
555 WSH_DEBUG_SV.push(l_module_name);
556 wsh_debug_sv.log(l_module_name, 'Number of records to be bulk deleted', l_del_loc_id_tab.count);
557 wsh_debug_sv.logmsg(l_module_name, 'before bulk delete:' ||to_char(sysdate, 'dd/mm/yyyy hh:mi:ss'));
558 END IF;
559
560
561 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
562
563 FORALL i IN l_del_loc_id_tab.first .. l_del_loc_id_tab.last
564
565 DELETE from wsh_region_locations where location_id = l_del_loc_id_tab(i)
566 and ( ( region_id in (select wrt.region_id from wsh_regions_tl wrt,
567 wsh_regions wr
568 where wrt.region_id = wr.region_id
569 and wrt.language = USERENV('LANG'))
570 ) OR region_id IS NULL);
571
572
573 IF l_debug_on THEN
574 wsh_debug_sv.logmsg(l_module_name, 'after bulk delete:' ||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'));
575 WSH_DEBUG_SV.pop(l_module_name);
576 END IF;
577
578 EXCEPTION
579 WHEN Others THEN
580 WSH_UTIL_CORE.printmsg(' Error in Delete_records: ' || sqlerrm);
581 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
582 IF l_debug_on THEN
583 WSH_DEBUG_SV.pop(l_module_name);
584 END IF;
585 END Delete_records;
586
587
588 /*===========================================================================+
589 | PROCEDURE |
590 | Update_records |
591 | |
592 | DESCRIPTION |
593 | This API updates records in WSH_REGION_LOCATIONS table |
594 | |
595 +===========================================================================*/
596
597
598 PROCEDURE Update_records
599 (
600 x_return_status OUT NOCOPY VARCHAR2
601 ) IS
602
603 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_RECORDS';
604
605 l_return_status VARCHAR2(20);
606 l_exception_msg_count NUMBER;
607 l_exception_msg_data VARCHAR2(15000);
608 l_dummy_exception_id NUMBER;
609 i NUMBER;
610 l_debug_on BOOLEAN;
611
612 BEGIN
613 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
614 IF l_debug_on IS NULL THEN
615 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
616 END IF;
617
618 IF l_debug_on THEN
619 WSH_DEBUG_SV.push(l_module_name);
620 wsh_debug_sv.logmsg(l_module_name, 'before bulk update:' ||to_char(sysdate, 'dd/mm/yyyy hh:mi:ss'));
621 END IF;
622
623 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
624
625 FORALL cnt IN l_upd_loc_id_tab.first .. l_upd_loc_id_tab.last
626
627 UPDATE wsh_region_locations
628 SET exception_type = 'Y'
629 WHERE location_id = l_upd_loc_id_tab(cnt)
630 and region_id in (select wrt.region_id from wsh_regions_tl wrt,
631 wsh_regions wr
632 where wrt.region_id = wr.region_id
633 and wrt.language = USERENV('LANG'));
634
635 IF l_debug_on THEN
636 wsh_debug_sv.logmsg(l_module_name, 'after bulk update:' ||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'));
637 END IF;
638
639 i := l_upd_loc_excp_tab.first;
640 IF i IS NOT NULL THEN
641 LOOP
642
643
644 -- Vijay 08/25: added call to put exception WSH_LOCATION_REGIONS_2_ERR
645 --Bug 4893034 Log exception for Active Locations only
646
647 l_dummy_exception_id := NULL;
648
649 IF l_upd_loc_excp_tab(i) THEN
650 wsh_xc_util.log_exception(
651 p_api_version => 1.0,
652 x_return_status => l_return_status,
653 x_msg_count => l_exception_msg_count,
654 x_msg_data => l_exception_msg_data,
655 x_exception_id => l_dummy_exception_id ,
656 p_logged_at_location_id => l_upd_loc_id_tab(i),
657 p_exception_location_id => l_upd_loc_id_tab(i),
658 p_logging_entity => 'SHIPPER',
659 p_logging_entity_id => FND_GLOBAL.USER_ID,
660 p_exception_name => 'WSH_LOCATION_REGIONS_2',
661 p_message => 'WSH_LOCATION_REGIONS_2_ERR'
662 );
663 END IF;
664
665 EXIT WHEN i = l_upd_loc_excp_tab.LAST;
666 i := l_upd_loc_excp_tab.NEXT(i);
667
668 END LOOP;
669 END IF;
670
671 IF l_debug_on THEN
672 wsh_debug_sv.logmsg(l_module_name, 'after log exceptions:' ||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'));
673 WSH_DEBUG_SV.pop(l_module_name);
674 END IF;
675
676 EXCEPTION
677 WHEN Others THEN
678 WSH_UTIL_CORE.printmsg(' Error in Update_records: ' || sqlerrm);
679 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
680 IF l_debug_on THEN
681 WSH_DEBUG_SV.pop(l_module_name);
682 END IF;
683
684 END Update_records;
685
686 /*===========================================================================+
687 | PROCEDURE |
688 | Process_records |
689 | |
690 | DESCRIPTION |
691 | This API inserts/updates/deletes records in |
692 | WSH_REGION_LOCATIONS table. |
693 | Calls APIs Insert_records/Update_records and delete_records |
694 | |
695 +===========================================================================*/
696
697 PROCEDURE Process_records
698 (
699 x_return_status OUT NOCOPY VARCHAR2
700 ) IS
701
702 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_RECORDS';
703 l_return_status VARCHAR2(20);
704 delete_failed EXCEPTION;
705 insert_failed EXCEPTION;
706 update_failed EXCEPTION;
707
708 l_debug_on BOOLEAN;
709
710 BEGIN
711 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
712 IF l_debug_on IS NULL THEN
713 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
714 END IF;
715
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.push(l_module_name);
718 END IF;
719
720 IF l_debug_on THEN
721 wsh_debug_sv.log(l_module_name, 'l_location_id_tab.COUNT', l_location_id_tab.COUNT);
722 wsh_debug_sv.log(l_module_name, 'l_del_loc_id_tab.COUNT', l_del_loc_id_tab.COUNT);
723 WSH_DEBUG_SV.log(l_module_name, 'l_upd_loc_id_tab.COUNT', l_upd_loc_id_tab.COUNT);
724
725 wsh_debug_sv.logmsg(l_module_name, 'before process_records:' ||to_char(sysdate, 'dd/mm/yyyy hh:mi:ss'));
726 END IF;
727
728 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
729
730 SAVEPOINT before_db_update;
731
732 Delete_records(x_return_status => l_return_status);
733 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
734 RAISE delete_failed;
735 END IF;
736
737 Insert_Records(x_return_status => l_return_status);
738 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
739 RAISE insert_failed;
740 END IF;
741
742 Update_Records(x_return_status => l_return_status);
743 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
744 RAISE update_failed;
745 END IF;
746
747 l_location_id_tab.delete;
748 l_region_id_tab.delete;
749 l_region_type_tab.delete;
750 l_exceptions_tab.delete;
751 l_location_source_tab.delete;
752 l_parent_region_tab.delete;
753 l_del_loc_id_tab.delete;
754 l_upd_loc_id_tab.delete;
755 l_upd_loc_excp_tab.delete;
756
757 IF l_debug_on THEN
758 WSH_DEBUG_SV.pop(l_module_name);
759 END IF;
760
761 EXCEPTION
762
763 WHEN delete_failed THEN
764 WSH_UTIL_CORE.printmsg(' Error in Process_records - delete: ' || sqlerrm);
765 ROLLBACK to before_db_update;
766 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
767 IF l_debug_on THEN
768 WSH_DEBUG_SV.pop(l_module_name);
769 END IF;
770 WHEN insert_failed THEN
771 WSH_UTIL_CORE.printmsg(' Error in Process_records - insert: ' || sqlerrm);
772 ROLLBACK to before_db_update;
773 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
774 IF l_debug_on THEN
775 WSH_DEBUG_SV.pop(l_module_name);
776 END IF;
777
778 WHEN update_failed THEN
779 WSH_UTIL_CORE.printmsg(' Error in Process_records - update: ' || sqlerrm);
780 ROLLBACK to before_db_update;
781 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
782 IF l_debug_on THEN
783 WSH_DEBUG_SV.pop(l_module_name);
784 END IF;
785 WHEN Others THEN
786 WSH_UTIL_CORE.printmsg('Error in Process_records : ' || sqlerrm);
787 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
788 IF l_debug_on THEN
789 WSH_DEBUG_SV.pop(l_module_name);
790 END IF;
791 END;
792
793 -- Bug 4722963 end
794
795 /*===========================================================================+
796 | PROCEDURE |
797 | Mapping_Regions_Main |
798 | |
799 | DESCRIPTION |
800 | This API selects all the location data into PL/SQL table |
801 | types and calls the Map_Location_To_Region by passing the |
802 | location information |
803 | |
804 +===========================================================================*/
805
806 PROCEDURE Mapping_Regions_Main (
807 p_location_type IN VARCHAR2,
808 p_from_location IN NUMBER,
809 p_to_location IN NUMBER,
810 p_start_date IN VARCHAR2,
811 p_end_date IN VARCHAR2,
812 p_insert_flag IN BOOLEAN default TRUE, -- Bug 4722963
813 x_return_status OUT NOCOPY VARCHAR2,
814 x_sqlcode OUT NOCOPY NUMBER,
815 x_sqlerr out NOCOPY varchar2) IS
816
817
818 l_return_status VARCHAR2(20);
819 l_sqlcode NUMBER;
820 l_sqlerr VARCHAR2(2000);
821 l_current_rows NUMBER;
822 l_remaining_rows NUMBER;
823 l_previous_rows NUMBER;
824 l_batchsize NUMBER := 500;
825 l_location_source VARCHAR2(4);
826 l_start_date DATE;
827 l_end_date DATE;
828
829 l_loc_tab TableNumbers; -- Location ID Table Type
830 l_state_tab TableVarchar; -- State Table Type
831 l_city_tab TableVarchar; -- City Table Type
832 l_postal_code_tab TableVarchar; -- Postal Code Table Type
833 l_ter_code_tab TableVarchar; -- Territory Code Table Type
834 l_ter_sn_tab TableVarchar; -- Territory Short Name Table Type
835 l_loc_source_tab TableVarchar; -- Location Source Table Type
836 l_inactive_date_tab TableDate; -- Inactive Date Table Type
837
838 -- Cursor Declarations
839
840 CURSOR Get_External_Locations (l_start_date DATE, l_end_date DATE) IS
841 SELECT
842 l.wsh_location_id,
843 t.territory_short_name,
844 t.territory_code,
845 nvl(l.state, l.province) state,
846 l.city city,
847 l.postal_code,
848 l.inactive_date
849 FROM
850 wsh_locations l,
851 fnd_territories_tl t
852 WHERE
853 t.territory_code = l.country and
854 t.language = userenv('LANG') and
855 l.wsh_location_id between p_from_location and p_to_location and
856 l.location_source_code = 'HZ' and
857 l.last_update_date >= nvl(l_start_date, l.last_update_date) and
858 l.last_update_date < nvl(l_end_date, l.last_update_date+1)
859 order by t.territory_code;
860
861 CURSOR Get_Internal_Locations (l_start_date DATE, l_end_date DATE) IS
862 SELECT
863 l.wsh_location_id,
864 t.territory_short_name,
865 t.territory_code,
866 nvl(l.state, l.province) state,
867 l.city city,
868 l.postal_code,
869 l.inactive_date
870 FROM
871 wsh_locations l,
872 fnd_territories_tl t
873 WHERE
874 t.territory_code = l.country and
875 t.language = userenv('LANG') and
876 l.wsh_location_id between p_from_location and p_to_location and
877 l.location_source_code = 'HR' and
878 l.last_update_date >= nvl(l_start_date, l.last_update_date) and
879 l.last_update_date < nvl(l_end_date, l.last_update_date+1)
880 order by t.territory_code;
881
882 CURSOR Get_Both_Locations (l_start_date DATE, l_end_date DATE) IS
883 SELECT
884 l.wsh_location_id,
885 t.territory_short_name,
886 t.territory_code,
887 nvl(l.state, l.province) state,
888 l.city city,
889 l.postal_code,
890 l.location_source_code source,
891 l.inactive_date
892 FROM
893 wsh_locations l,
894 fnd_territories_tl t
895 WHERE
896 t.territory_code = l.country and
897 t.language = userenv('LANG') and
898 l.wsh_location_id between p_from_location and p_to_location and
899 l.last_update_date >= nvl(l_start_date, l.last_update_date) and
900 l.last_update_date < nvl(l_end_date, l.last_update_date+1)
901 order by t.territory_code;
902
903
904 l_debug_on BOOLEAN;
905 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAPPING_REGIONS_MAIN';
906
907 BEGIN
908
909 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
910 IF l_debug_on IS NULL THEN
911 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
912 END IF;
913 IF l_debug_on THEN
914 WSH_DEBUG_SV.push(l_module_name);
915 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
916 WSH_DEBUG_SV.log(l_module_name,'P_FROM_LOCATION',P_FROM_LOCATION);
917 WSH_DEBUG_SV.log(l_module_name,'P_TO_LOCATION',P_TO_LOCATION);
918 WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
919 WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
920 END IF;
921
922 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
923
924 ----------------------------------------------------------------------
925 -- Depending on the location type, fetch all the data into respective
926 -- PL/SQL tables. The call Map_Location_To_Region to map the data.
927 ----------------------------------------------------------------------
928
929 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
930 l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
931
932 IF l_debug_on THEN
933 WSH_DEBUG_SV.log(l_module_name,'l_START_DATE',l_START_DATE);
934 WSH_DEBUG_SV.log(l_module_name,'l_END_DATE',l_END_DATE);
935 END IF;
936
937 l_previous_rows := 0;
938
939 IF p_location_type = 'EXTERNAL' THEN
940
941 l_location_source := 'HZ';
942
943 IF l_debug_on THEN
944 WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is External');
945 END IF;
946
947 OPEN Get_External_Locations(l_start_date, l_end_date);
948 LOOP
949 FETCH Get_External_Locations BULK COLLECT INTO
950 l_loc_tab,
951 l_ter_sn_tab,
952 l_ter_code_tab,
953 l_state_tab,
954 l_city_tab,
955 l_postal_code_tab,
956 l_inactive_date_tab
957 LIMIT l_Batchsize;
958
959 l_current_rows := Get_External_Locations%rowcount ;
960 l_remaining_rows := l_current_rows - l_previous_rows;
961
962 IF (l_remaining_rows <= 0) then
963 EXIT;
964 END IF;
965
966 l_previous_rows := l_current_rows ;
967
968 -----------------------------------------------------
969 -- Loop through the entire PL/SQL table and call the
970 -- Map_Location_To_Region by passing corresponding
971 -- parameters.
972 -----------------------------------------------------
973
974 IF l_debug_on THEN
975 WSH_DEBUG_SV.log(l_module_name, 'l_ter_sn_tab.COUNT', l_ter_sn_tab.COUNT);
976 END IF;
977
978 IF l_ter_sn_tab.COUNT > 0 THEN
979
980 FOR i in l_ter_sn_tab.FIRST..l_ter_sn_tab.LAST
981 LOOP
982
983 IF l_debug_on THEN
984 WSH_DEBUG_SV.logmsg(l_module_name,'Processing Location id : '|| l_loc_tab(i));
985 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Map_Location_To_Region',WSH_DEBUG_SV.C_PROC_LEVEL);
986 END IF;
987
988 Map_Location_To_Region (
989 p_country => l_ter_sn_tab(i),
990 p_country_code => l_ter_code_tab(i),
991 p_state => l_state_tab(i),
992 p_city => l_city_tab(i),
993 p_postal_code => l_postal_code_tab(i),
994 p_location_id => l_loc_tab(i),
995 p_inactive_date => l_inactive_date_tab(i),
996 p_location_source => l_location_source,
997 p_insert_flag => p_insert_flag, -- Bug 4722963
998 x_return_status => l_return_status,
999 x_sqlcode => l_sqlcode,
1000 x_sqlerr => l_sqlerr );
1001
1002 IF l_debug_on THEN
1003 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from Map_Location_To_Region : '|| l_return_status);
1004 END IF;
1005
1006 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1007
1008 IF l_debug_on THEN
1009 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in API Map_Location_To_Region for location : '||l_loc_tab(i));
1010 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1011 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1012 ELSE
1013 WSH_UTIL_CORE.printmsg('Failed in API Map_Location_To_Region for location : '||l_loc_tab(i));
1014 WSH_UTIL_CORE.printmsg(l_sqlcode);
1015 WSH_UTIL_CORE.printmsg(l_sqlerr);
1016 END IF;
1017
1018 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1019 END IF;
1020
1021 -- WSH_UTIL_CORE.println('After call to Map_Location_To_Region');
1022 -- WSH_UTIL_CORE.println('Processing Next Location');
1023 -- WSH_UTIL_CORE.println('*******************************************');
1024 END LOOP;
1025
1026 -- Bug 4722963
1027
1028 IF NOT p_insert_flag AND
1029 (l_location_id_tab.COUNT > 0 OR l_del_loc_id_tab.COUNT > 0 OR l_upd_loc_id_tab.COUNT > 0 )
1030 THEN
1031
1032 Process_Records(x_return_status => l_return_status);
1033
1034 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1035 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1036 END IF;
1037
1038 END IF;
1039 -- Bug 4722963 end
1040 END IF;
1041
1042 EXIT WHEN Get_External_Locations%NOTFOUND;
1043 END LOOP;
1044
1045 IF Get_External_Locations%ISOPEN THEN
1046 CLOSE Get_External_Locations;
1047 END IF;
1048
1049 ELSIF p_location_type = 'INTERNAL' then
1050
1051 l_location_source := 'HR';
1052
1053 IF l_debug_on THEN
1054 WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is Internal');
1055 END IF;
1056 OPEN Get_Internal_Locations(l_start_date, l_end_date);
1057 LOOP
1058 FETCH Get_Internal_Locations BULK COLLECT INTO
1059 l_loc_tab,
1060 l_ter_sn_tab,
1061 l_ter_code_tab,
1062 l_state_tab,
1063 l_city_tab,
1064 l_postal_code_tab,
1065 l_inactive_date_tab
1066 LIMIT l_Batchsize;
1067
1068 l_current_rows := Get_Internal_Locations%rowcount ;
1069 l_remaining_rows := l_current_rows - l_previous_rows;
1070
1071 IF (l_remaining_rows <= 0) then
1072 EXIT;
1073 END IF;
1074
1075 l_previous_rows := l_current_rows ;
1076
1077 -----------------------------------------------------
1078 -- Loop through the entire PL/SQL table and call the
1079 -- Map_Location_To_Region by passing corresponding
1080 -- parameters.
1081 -----------------------------------------------------
1082
1083 IF l_debug_on THEN
1084 WSH_DEBUG_SV.log(l_module_name, 'l_ter_sn_tab.COUNT', l_ter_sn_tab.COUNT);
1085 END IF;
1086
1087 IF l_ter_sn_tab.COUNT > 0 THEN
1088
1089 FOR i in l_ter_sn_tab.FIRST..l_ter_sn_tab.LAST
1090 LOOP
1091
1092 IF l_debug_on THEN
1093 WSH_DEBUG_SV.logmsg(l_module_name,'Processing Location id : '|| l_loc_tab(i));
1094 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Map_Location_To_Region',WSH_DEBUG_SV.C_PROC_LEVEL);
1095 END IF;
1096
1097 Map_Location_To_Region (
1098 p_country => l_ter_sn_tab(i),
1099 p_country_code => l_ter_code_tab(i),
1100 p_state => l_state_tab(i),
1101 p_city => l_city_tab(i),
1102 p_postal_code => l_postal_code_tab(i),
1103 p_location_id => l_loc_tab(i),
1104 p_inactive_date => l_inactive_date_tab(i),
1105 p_location_source => l_location_source,
1106 p_insert_flag => p_insert_flag,
1107 x_return_status => l_return_status,
1108 x_sqlcode => l_sqlcode,
1109 x_sqlerr => l_sqlerr );
1110
1111 IF l_debug_on THEN
1112 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from Map_Location_To_Region : '|| l_return_status);
1113 END IF;
1114
1115 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1116
1117 IF l_debug_on THEN
1118 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in API Map_Location_To_Region for location : '||l_loc_tab(i));
1119 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1120 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1121 ELSE
1122 WSH_UTIL_CORE.printmsg('Failed in API Map_Location_To_Region for location : '||l_loc_tab(i));
1123 WSH_UTIL_CORE.printmsg(l_sqlcode);
1124 WSH_UTIL_CORE.printmsg(l_sqlerr);
1125 END IF;
1126 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1127 END IF;
1128
1129 -- WSH_UTIL_CORE.println('After call to Map_Location_To_Region');
1130 -- WSH_UTIL_CORE.println('Processing Next Location');
1131 -- WSH_UTIL_CORE.println('*******************************************');
1132 END LOOP;
1133
1134 -- Bug 4722963
1135
1136 IF NOT p_insert_flag AND
1137 (l_location_id_tab.COUNT > 0 OR l_del_loc_id_tab.COUNT > 0 OR l_upd_loc_id_tab.COUNT > 0 )
1138 THEN
1139 Process_Records(x_return_status => l_return_status);
1140 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1141 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1142 END IF;
1143 END IF;
1144 -- Bug 4722963 end
1145 END IF;
1146
1147 EXIT WHEN Get_Internal_Locations%NOTFOUND;
1148 END LOOP;
1149
1150 IF Get_Internal_Locations%ISOPEN THEN
1151 CLOSE Get_Internal_Locations;
1152 END IF;
1153
1154 ELSIF p_location_type = 'BOTH' THEN
1155
1156 IF l_debug_on THEN
1157 WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is Both');
1158 END IF;
1159
1160 OPEN Get_Both_Locations(l_start_date, l_end_date);
1161 LOOP
1162 FETCH Get_Both_Locations BULK COLLECT INTO
1163 l_loc_tab,
1164 l_ter_sn_tab,
1165 l_ter_code_tab,
1166 l_state_tab,
1167 l_city_tab,
1168 l_postal_code_tab,
1169 l_loc_source_tab,
1170 l_inactive_date_tab
1171 LIMIT l_Batchsize;
1172
1173 l_current_rows := Get_Both_Locations%rowcount ;
1174 l_remaining_rows := l_current_rows - l_previous_rows;
1175
1176 IF (l_remaining_rows <= 0) then
1177 EXIT;
1178 END IF;
1179
1180 l_previous_rows := l_current_rows ;
1181
1182 -----------------------------------------------------
1183 -- Loop through the entire PL/SQL table and call the
1184 -- Map_Location_To_Region by passing corresponding
1185 -- parameters.
1186 -----------------------------------------------------
1187 IF l_debug_on THEN
1188 WSH_DEBUG_SV.log(l_module_name, 'l_ter_sn_tab.COUNT', l_ter_sn_tab.COUNT);
1189 END IF;
1190
1191 IF l_ter_sn_tab.COUNT > 0 THEN
1192
1193 FOR i in l_ter_sn_tab.FIRST..l_ter_sn_tab.LAST
1194 LOOP
1195
1196 IF l_debug_on THEN
1197 WSH_DEBUG_SV.logmsg(l_module_name,'Processing Location id : '|| l_loc_tab(i));
1198 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Map_Location_To_Region',WSH_DEBUG_SV.C_PROC_LEVEL);
1199 END IF;
1200 Map_Location_To_Region (
1201 p_country => l_ter_sn_tab(i),
1202 p_country_code => l_ter_code_tab(i),
1203 p_state => l_state_tab(i),
1204 p_city => l_city_tab(i),
1205 p_postal_code => l_postal_code_tab(i),
1206 p_location_id => l_loc_tab(i),
1207 p_location_source => l_loc_source_tab(i),
1208 p_inactive_date => l_inactive_date_tab(i),
1209 p_insert_flag => p_insert_flag,
1210 x_return_status => l_return_status,
1211 x_sqlcode => l_sqlcode,
1212 x_sqlerr => l_sqlerr );
1213
1214
1215 IF l_debug_on THEN
1216 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from Map_Location_To_Region : '|| l_return_status);
1217 END IF;
1218
1219 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1220
1221 IF l_debug_on THEN
1222 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in API Map_Location_To_Region for location : '||l_loc_tab(i));
1223 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1224 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1225 ELSE
1226 WSH_UTIL_CORE.printmsg('Failed in API Map_Location_To_Region for location : '||l_loc_tab(i));
1227 WSH_UTIL_CORE.printmsg(l_sqlcode);
1228 WSH_UTIL_CORE.printmsg(l_sqlerr);
1229 END IF;
1230
1231 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1232 END IF;
1233
1234 -- WSH_UTIL_CORE.println('After call to Map_Location_To_Region');
1235 -- WSH_UTIL_CORE.println('Processing Next Location');
1236 -- WSH_UTIL_CORE.println('*******************************************');
1237 END LOOP;
1238
1239 -- Bug 4722963
1240 IF NOT p_insert_flag AND
1241 (l_location_id_tab.COUNT > 0 OR l_del_loc_id_tab.COUNT > 0 OR l_upd_loc_id_tab.COUNT > 0 )
1242 THEN
1243 Process_Records(x_return_status => l_return_status);
1244 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1245 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1246 END IF;
1247 END IF;
1248 -- Bug 4722963 end
1249 END IF;
1250
1251 EXIT WHEN Get_Both_Locations%NOTFOUND;
1252 END LOOP;
1253
1254 IF Get_Both_Locations%ISOPEN THEN
1255 CLOSE Get_Both_Locations;
1256 END IF;
1257 END IF;
1258 x_return_status := l_return_status;
1259
1260 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1261 commit;
1262 END IF;
1263
1264 IF l_debug_on THEN
1265 WSH_DEBUG_SV.pop(l_module_name);
1266 END IF;
1267
1268 EXCEPTION
1269
1270 WHEN No_Data_Found THEN
1271
1272 IF l_debug_on THEN
1273 WSH_DEBUG_SV.logmsg(l_module_name,'No records found for the entered parameters');
1274 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1275 ELSE
1276 WSH_UTIL_CORE.printmsg('No records found for the entered parameters');
1277 END IF;
1278
1279 WHEN Others THEN
1280 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1281 x_sqlcode := SQLCODE;
1282 x_sqlerr := SQLERRM;
1283
1284 IF l_debug_on THEN
1285 WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Mapping_Regions_Main ');
1286 WSH_DEBUG_SV.logmsg(l_module_name,x_sqlcode);
1287 WSH_DEBUG_SV.logmsg(l_module_name,x_sqlerr);
1288 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1289 ELSE
1290 WSH_UTIL_CORE.printmsg('When Others of Procedure Mapping_Regions_Main ');
1291 WSH_UTIL_CORE.printmsg(x_sqlcode);
1292 WSH_UTIL_CORE.printmsg(x_sqlerr);
1293 END IF;
1294
1295 END Mapping_Regions_Main;
1296
1297 /*===========================================================================+
1298 | PROCEDURE |
1299 | Map_Location_To_Region |
1300 | |
1301 | DESCRIPTION |
1302 | This API does the main mapping process. It calls the API |
1303 | WSH_REGIONS_SEARCH_PKG.Get_Region_Info which inturn returns |
1304 | the region id. For this particuar region, the parent regions |
1305 | are also obtained and all these are inserted into the |
1306 | intersection table. |
1307 | |
1308 +===========================================================================*/
1309
1310 PROCEDURE Map_Location_To_Region (
1311 p_country IN VARCHAR2,
1312 p_country_code IN VARCHAR2,
1313 p_state IN VARCHAR2,
1314 p_city IN VARCHAR2,
1315 p_postal_code IN VARCHAR2,
1316 p_location_id IN NUMBER,
1317 p_location_source IN VARCHAR2,
1318 p_inactive_date IN DATE,
1319 p_insert_flag IN BOOLEAN DEFAULT TRUE, -- Bug 4722963
1320 x_return_status OUT NOCOPY VARCHAR2,
1321 x_sqlcode OUT NOCOPY NUMBER,
1322 x_sqlerr OUT NOCOPY VARCHAR2) IS
1323
1324
1325 l_region_info WSH_REGIONS_SEARCH_PKG.region_rec;
1326 l_region_type NUMBER := 0;
1327 l_region_id NUMBER := 0;
1328 l_region_table WSH_REGIONS_SEARCH_PKG.region_table;
1329 l_country l_region_info.country%TYPE;
1330 l_return_status VARCHAR2(10);
1331 Insert_Failed EXCEPTION;
1332 l_sqlcode NUMBER;
1333 l_sqlerr VARCHAR2(2000);
1334 l_region_type_const NUMBER := 0 ;
1335 l_parent_region VARCHAR2(1) := 'N';
1336 l_rows_before NUMBER := 0;
1337 l_rows_after NUMBER := 0;
1338 l_exists VARCHAR2(10);
1339 l_location_source VARCHAR2(4);
1340 l_status NUMBER := 0;
1341 i NUMBER := 0;
1342 j NUMBER := 0;
1343 l_log_exception BOOLEAN := FALSE;
1344
1345 --Variables: Start of fix for bug 5125837
1346 TYPE Reg_Rec_Type IS RECORD (
1347 REGION_ID WSH_REGION_LOCATIONS.REGION_ID%TYPE,
1348 REGION_TYPE WSH_REGION_LOCATIONS.REGION_TYPE%TYPE,
1349 EXCEPTION_TYPE WSH_REGION_LOCATIONS.EXCEPTION_TYPE%TYPE,
1350 PARENT_REGION_FLAG WSH_REGION_LOCATIONS.PARENT_REGION_FLAG%TYPE );
1351
1352 TYPE Reg_Tab_Type IS TABLE OF Reg_Rec_Type INDEX BY BINARY_INTEGER;
1353
1354 l_region_detail_tab Reg_Tab_Type;
1355 l_region_counter NUMBER;
1356 l_max_counter NUMBER;
1357 l_insert BOOLEAN;
1358 --Variables: End of fix for bug 5125837
1359
1360 --Commented below 2 Cursors for bug 5125837
1361 /****
1362
1363 CURSOR Check_Location_Exists(c_location_id IN NUMBER) IS
1364 select 'exists'
1365 from wsh_region_locations
1366 where location_id = c_location_id;
1367
1368
1369 CURSOR get_loc_region_count (p_location_id NUMBER) IS
1370 SELECT count(*)
1371 FROM wsh_region_locations
1372 WHERE location_id = p_location_id
1373 AND ( ( region_id IN
1374 (
1375 SELECT wrt.region_id
1376 FROM wsh_regions_tl wrt, wsh_regions wr
1377 WHERE wrt.region_id = wr.region_id
1378 AND wrt.language = USERENV('LANG'))
1379 )
1380 OR region_id IS NULL);
1381 ****/
1382
1383 l_exception_msg_count NUMBER;
1384 l_exception_msg_data VARCHAR2(15000);
1385 l_dummy_exception_id NUMBER;
1386
1387 --
1388 l_debug_on BOOLEAN;
1389 --
1390
1391 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_LOCATION_TO_REGION';
1392
1393 BEGIN
1394 --bug 7158136
1395 --x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1396 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1397
1398 j := l_location_id_tab.COUNT;
1399 --
1400 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1401 --
1402 IF l_debug_on IS NULL
1403 THEN
1404 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1405 END IF;
1406 --
1407 --
1408 -- Debug Statements
1409 --
1410 IF l_debug_on THEN
1411 WSH_DEBUG_SV.push(l_module_name);
1412 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1413 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1414 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1415 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1416 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1417 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1418 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE',P_LOCATION_SOURCE);
1419 WSH_DEBUG_SV.log(l_module_name,'P_INACTIVE_DATE',P_INACTIVE_DATE);
1420 END IF;
1421
1422 l_region_info.country_code := p_country_code;
1423 l_region_info.country := p_country;
1424
1425 IF (p_country_code IS NULL) THEN
1426 l_country := p_country;
1427 END IF;
1428
1429 --Bug 6670302 Removed the restriction on length of state and city
1430 l_region_info.state := p_state;
1431 l_region_info.city := p_city;
1432
1433 l_region_info.postal_code_from := p_postal_code;
1434 l_region_info.postal_code_to := p_postal_code;
1435
1436 IF (p_postal_code IS NOT NULL) THEN
1437 l_region_type := 3;
1438 ELSIF (p_city IS NOT NULL) THEN
1439 l_region_type := 2;
1440 ELSIF (p_state IS NOT NULL) THEN
1441 l_region_type := 1;
1442 END IF;
1443
1444 -- START affected area
1445 -- change call to use get_all_region_matches
1446
1447 IF l_debug_on THEN
1448 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_ALL_REGION_MATCHES');
1449 END IF;
1450
1451 WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(
1452 p_country => l_country,
1453 p_country_region => l_region_info.country_region,
1454 p_state => l_region_info.state,
1455 p_city => l_region_info.city,
1456 p_postal_code_from => l_region_info.postal_code_from,
1457 p_postal_code_to => l_region_info.postal_code_to,
1458 p_country_code => l_region_info.country_code,
1459 p_country_region_code => l_region_info.country_region_code,
1460 p_state_code => l_region_info.state_code,
1461 p_city_code => l_region_info.city_code,
1462 -- p_lang_code => null,
1463 p_lang_code => USERENV('LANG'),
1464 p_location_id => null,
1465 p_zone_flag => 'N',
1466 x_status => l_status,
1467 x_regions => l_region_table);
1468
1469 IF l_status = 1 THEN
1470
1471 IF l_debug_on THEN
1472 WSH_DEBUG_SV.logmsg(l_module_name,'get_all_region_matches could not find matching regions for location : ' || p_location_id);
1473 END IF;
1474
1475 END IF;
1476
1477 -- END affected area
1478
1479 --Commented for bug 5125837
1480 /*
1481 OPEN Check_Location_Exists(p_location_id);
1482 FETCH Check_Location_Exists INTO l_exists;
1483 CLOSE Check_Location_Exists;
1484 */
1485
1486 --Added for bug 5125837
1487 l_exists := null;
1488 BEGIN
1489 select region_id, region_type, exception_type, parent_region_flag
1490 BULK COLLECT INTO l_region_detail_tab
1491 from wsh_region_locations
1492 where location_id = p_location_id
1493 and ( ( region_id in
1494 ( select wrt.region_id
1495 from wsh_regions_tl wrt,
1496 wsh_regions wr
1497 where wrt.region_id = wr.region_id
1498 and wrt.language = USERENV('LANG') )
1499 ) OR region_id IS NULL )
1500 order by region_type desc;
1501
1502 IF l_region_detail_tab.COUNT > 0 THEN
1503 l_exists := 'Y';
1504 END IF;
1505
1506 EXCEPTION
1507 WHEN no_data_found THEN
1508 l_exists := null;
1509 END;
1510
1511 l_rows_before := l_region_detail_tab.COUNT;
1512 --End bug 5125837
1513
1514 SAVEPOINT WSH_LOCATION_EXISTS;
1515
1516 ---------------------------------------------------------------
1517 -- If a region is existing already, delete the records so that
1518 -- fresh mappings are inserted. Savepoint is issued before
1519 -- doing this.
1520 ---------------------------------------------------------------
1521
1522 --Added for bug 5125837
1523 l_region_counter := 1;
1524
1525 IF l_debug_on THEN
1526 --commeted during bug 7158136 as log message is not getting printed
1527 --WSH_DEBUG_SV.logmsg(l_module_name,'No. of existing mapped regions ',l_rows_before);
1528 WSH_DEBUG_SV.log(l_module_name,'No. of existing mapped regions ',l_rows_before);
1529 END IF;
1530
1531 l_location_source := p_location_source;
1532
1533 IF p_location_source = 'TCA' THEN
1534 l_location_source := 'HZ';
1535 END IF;
1536
1537 IF l_region_table.COUNT = 0 THEN
1538
1539 IF l_debug_on THEN
1540 WSH_DEBUG_SV.logmsg(l_module_name,'No matching regions were found for location : ' || p_location_id);
1541 END IF;
1542
1543 -----------------------------------------------------------
1544 -- If no region is found still insert the the location with
1545 -- region id as null and exception_flag Y
1546 -----------------------------------------------------------
1547
1548 --Added for bug 5125837
1549 IF ( l_region_detail_tab.COUNT = 1 AND
1550 l_region_detail_tab(1).region_id is null AND
1551 l_region_detail_tab(1).region_type is null AND
1552 l_region_detail_tab(1).Parent_Region_Flag = l_parent_region )
1553 THEN --{
1554
1555 IF (l_region_detail_tab(1).Exception_Type = 'N') THEN
1556
1557 UPDATE wsh_region_locations
1558 SET exception_type = 'Y',
1559 last_updated_by = fnd_global.user_id,
1560 last_update_date = sysdate,
1561 last_update_login = fnd_global.login_id
1562 where location_id = p_location_id;
1563 END IF;
1564 --
1565 ELSE
1566
1567 -- Bug 4722963 - Added p_insert_flag
1568
1569 -- If p_insert_flag is false, then insert data into pl/sql tables
1570 -- to be bulk processed later while doing delete, insert and update operations
1571 -- This is done for performance reasons since bulk operations are
1572 -- better in terms of performance
1573
1574 IF p_insert_flag THEN
1575 IF ( l_region_detail_tab.COUNT > 1 ) THEN
1576 DELETE from wsh_region_locations where location_id = p_location_id
1577 and ( ( region_id in (select wrt.region_id from wsh_regions_tl wrt,
1578 wsh_regions wr
1579 where wrt.region_id = wr.region_id
1580 and wrt.language = USERENV('LANG'))
1581 ) OR region_id IS NULL);
1582 END IF;
1583 ELSE
1584 IF ( l_region_detail_tab.COUNT > 1 ) THEN
1585 l_del_loc_id_tab(l_del_loc_id_tab.COUNT) := p_location_id;
1586 END IF;
1587 END IF;
1588
1589 -----------------------------------------------------------
1590 -- If no region is found still insert the the location with
1591 -- region id as null and exception_flag Y
1592 -----------------------------------------------------------
1593
1594 IF p_insert_flag THEN
1595 Insert_Record (
1596 p_location_id => p_location_id,
1597 p_region_id => NULL,
1598 p_region_type => NULL,
1599 p_exception => 'Y',
1600 p_location_source => l_location_source,
1601 p_parent_region => l_parent_region,
1602 x_return_status => l_return_status);
1603
1604 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1605 IF l_debug_on THEN
1606 WSH_DEBUG_SV.logmsg(l_module_name,'Insert failed for Null Region for Location : '||p_location_id);
1607 ELSE
1608 WSH_UTIL_CORE.printmsg('Insert failed for Null Region for Location : '||p_location_id);
1609 END IF;
1610 RAISE Insert_Failed;
1611 END IF;
1612 ELSE
1613
1614 l_location_id_tab(j) := p_location_id;
1615 l_region_id_tab(j) := NULL;
1616 l_region_type_tab(j) := NULL;
1617 l_exceptions_tab(j) := 'Y';
1618 l_location_source_tab(j) := l_location_source;
1619 l_parent_region_tab(j) := l_parent_region;
1620 j := j+1;
1621 END IF;
1622
1623 END IF; --}
1624
1625 l_rows_after := 1; -- Bug 3736133
1626 --End Bug 5125837
1627
1628 ELSE
1629
1630 --Added for bug 5125837
1631 --Before looping check whether wsh_region_locations table contains only
1632 --one record where region_id and region_type is NULL.
1633
1634 IF ( l_region_detail_tab.COUNT = 1 AND
1635 l_region_detail_tab(1).region_id is null AND
1636 l_region_detail_tab(1).region_type is null )
1637 THEN
1638 DELETE from wsh_region_locations where location_id = p_location_id
1639 and ( ( region_id in (select wrt.region_id from wsh_regions_tl wrt,
1640 wsh_regions wr
1641 where wrt.region_id = wr.region_id
1642 and wrt.language = USERENV('LANG'))
1643 ) OR region_id IS NULL);
1644 --
1645 IF l_debug_on THEN
1646 WSH_DEBUG_SV.log(l_module_name,'No of rows deleted : ' || sql%rowcount );
1647 END IF;
1648 --
1649 END IF;
1650
1651 -----------------------------------------------------------
1652 -- If some regions are found, then insert everything in the intersection
1653 -- table. If the region is a parent region, set the parent
1654 -- flag accordingly.
1655 -----------------------------------------------------------
1656
1657 IF l_debug_on THEN
1658 WSH_DEBUG_SV.logmsg(l_module_name,' Looping over l_region_table for inserting into the intersection table ');
1659 END IF;
1660
1661 l_rows_after := 0; -- Bug 3736133
1662
1663 i := l_region_table.FIRST;
1664 --Added for bug 5125837
1665 l_max_counter := l_region_detail_tab.COUNT;
1666 LOOP -- 3. region hierarchy
1667
1668 IF l_region_table(i).region_type >= 0 THEN
1669
1670 IF l_region_table(i).region_type <> l_region_type THEN
1671 l_parent_region := 'Y';
1672 ELSE
1673 l_parent_region := 'N';
1674 END IF;
1675
1676 l_insert := TRUE;
1677 IF ( l_region_counter <= l_max_counter ) THEN --{
1678 LOOP --{
1679 IF l_debug_on THEN
1680 WSH_DEBUG_SV.logmsg(l_module_name,'Start of Loop' );
1681 END IF;
1682
1683 IF ( l_region_detail_tab(l_region_counter).region_type = l_region_table(i).region_type) THEN --{
1684
1685 IF NOT ( l_region_detail_tab(l_region_counter).region_id = l_region_table(i).region_id AND
1686 l_region_detail_tab(l_region_counter).exception_type = 'N' AND
1687 l_region_detail_tab(l_region_counter).parent_region_flag = l_parent_region)
1688 THEN --{
1689
1690 UPDATE wsh_region_locations
1691 SET region_id = l_region_table(i).region_id,
1692 exception_type = 'N',
1693 parent_region_flag = l_parent_region,
1694 last_updated_by = fnd_global.user_id,
1695 last_update_date = sysdate,
1696 last_update_login = fnd_global.login_id
1697 WHERE location_id = p_location_id
1698 AND region_type = l_region_table(i).region_type;
1699
1700 END IF; --}
1701
1702 l_rows_after := l_rows_after + 1;
1703 l_region_counter := l_region_counter + 1;
1704 l_insert := FALSE;
1705 EXIT;
1706
1707 ELSIF (l_region_detail_tab(l_region_counter).region_type < l_region_table(i).region_type) THEN
1708 l_insert := TRUE;
1709 EXIT;
1710 ELSE
1711
1712 DELETE FROM wsh_region_locations
1713 WHERE location_id = p_location_id
1714 AND region_type = l_region_detail_tab(l_region_counter).region_type
1715 AND region_id in
1716 ( select wrt.region_id
1717 from wsh_regions_tl wrt,
1718 wsh_regions wr
1719 where wr.region_id = wrt.region_id
1720 and wrt.language = USERENV('LANG') );
1721
1722 IF l_debug_on THEN
1723 WSH_DEBUG_SV.logmsg(l_module_name,'No of Regions deleted : ' || sql%rowcount );
1724 END IF;
1725
1726 END IF; --}
1727
1728 l_region_counter := l_region_counter + 1;
1729 EXIT WHEN (l_region_counter > l_max_counter);
1730
1731 IF l_debug_on THEN
1732 WSH_DEBUG_SV.logmsg(l_module_name,'End of Loop' );
1733 END IF;
1734 END LOOP; --}
1735 END IF; --}
1736
1737 IF l_insert THEN --{
1738
1739 IF l_debug_on THEN
1740 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Insert_Record for location id : ' || p_location_id ||
1741 ' Region Id : ' || l_region_table(i).region_id ||
1742 ' Region Type : ' || l_region_table(i).region_type ||
1743 ' Parent Region : ' || l_parent_region);
1744 END IF;
1745 IF p_insert_flag THEN
1746 Insert_Record (
1747 p_location_id => p_location_id,
1748 p_region_id => l_region_table(i).region_id,
1749 p_region_type => l_region_table(i).region_type,
1750 p_exception => 'N',
1751 p_location_source => l_location_source,
1752 p_parent_region => l_parent_region,
1753 x_return_status => l_return_status);
1754
1755 IF l_debug_on THEN
1756 WSH_DEBUG_SV.logmsg(l_module_name,'After calling Insert_Record for location id :' || p_location_id);
1757 END IF;
1758
1759 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1760 IF l_debug_on THEN
1761 WSH_DEBUG_SV.logmsg(l_module_name,'Insert failed for Region '||l_region_table(i).region_id||' for Location : '||p_location_id);
1762 ELSE
1763 WSH_UTIL_CORE.printmsg('Insert failed for Region '||l_region_table(i).region_id||' for Location : '||p_location_id);
1764 END IF;
1765 RAISE Insert_Failed;
1766 END IF;
1767 ELSE
1768 l_location_id_tab(j) := p_location_id;
1769 l_region_id_tab(j) := l_region_table(i).region_id;
1770 l_region_type_tab(j) := l_region_table(i).region_type;
1771 l_exceptions_tab(j) := 'N';
1772 l_location_source_tab(j) := l_location_source;
1773 l_parent_region_tab(j) := l_parent_region;
1774 j := j+1;
1775 END IF;
1776
1777 l_rows_after := l_rows_after + 1; -- Bug 3736133
1778
1779 END IF; --}
1780 END IF;
1781
1782 EXIT WHEN i = l_region_table.LAST;
1783 i := l_region_table.NEXT(i);
1784
1785 END LOOP; -- 4. region hierarchy
1786
1787 END IF;
1788
1789 IF l_debug_on THEN
1790 WSH_DEBUG_SV.log(l_module_name,'No. of mapped regions after deletion and reinsert ', l_rows_after);
1791 END IF;
1792
1793 --------------------------------------------------------
1794 -- If the number of locations that was matching before
1795 -- is less than now or no match is found, update the exception flag
1796 -- to 'Y'
1797 --------------------------------------------------------
1798
1799 IF (l_rows_after < l_rows_before AND l_rows_before > 0) OR l_region_table.COUNT = 0 THEN
1800
1801 -- There is a possibility that exception flags are updated for mappings
1802 -- in one language, but they are visible in the UI
1803 -- from another language.
1804 -- 02/04 discussed and validated with Rohit
1805 -- the user will have to map again
1806 --Bug 4893034 Log exception for Active Locations only
1807 IF TRUNC(sysdate) <= TRUNC(NVL(p_inactive_date, sysdate)) THEN
1808 l_log_exception := TRUE;
1809 END IF;
1810
1811 IF p_insert_flag THEN
1812 UPDATE wsh_region_locations
1813 SET exception_type = 'Y',
1814 --Added for bug 5125837
1815 last_update_date = sysdate,
1816 last_updated_by = FND_GLOBAL.user_id,
1817 last_update_login = FND_GLOBAL.login_id
1818 WHERE location_id = p_location_id
1819 and region_id in (select wrt.region_id from wsh_regions_tl wrt,
1820 wsh_regions wr
1821 where wrt.region_id = wr.region_id
1822 and wrt.language = USERENV('LANG'));
1823
1824 -- Vijay 08/25: added call to put exception WSH_LOCATION_REGIONS_2_ERR
1825
1826 IF l_log_exception THEN
1827 wsh_xc_util.log_exception(
1828 p_api_version => 1.0,
1829 x_return_status => l_return_status,
1830 x_msg_count => l_exception_msg_count,
1831 x_msg_data => l_exception_msg_data,
1832 x_exception_id => l_dummy_exception_id ,
1833 p_logged_at_location_id => p_location_id,
1834 p_exception_location_id => p_location_id,
1835 p_logging_entity => 'SHIPPER',
1836 p_logging_entity_id => FND_GLOBAL.USER_ID,
1837 p_exception_name => 'WSH_LOCATION_REGIONS_2',
1838 p_message => 'WSH_LOCATION_REGIONS_2_ERR'
1839 );
1840 END IF;
1841 ELSE
1842 l_upd_loc_id_tab(l_upd_loc_id_tab.COUNT) := p_location_id;
1843 l_upd_loc_excp_tab(l_upd_loc_excp_tab.COUNT) := l_log_exception;
1844 END IF;
1845
1846 END IF;
1847
1848 --------------------------------------------------------
1849 -- If the number of regions being matched is only one set
1850 -- exception WSH_LOCATION_REGIONS_1_ERR
1851 --------------------------------------------------------
1852
1853 -- Bug 4451703
1854 -- l_dummy_exception_id has to be intialized to NULL otherwise
1855 -- it takes the same value returned by previous log_exception
1856 -- and errors out when trying to update the existing exception.
1857
1858 IF (l_rows_after = l_rows_before AND l_rows_before = 1) THEN
1859 --
1860 l_dummy_exception_id := NULL;
1861 --
1862 --Bug 4893034 Log exception for Active Locations only
1863 IF TRUNC(sysdate) <= TRUNC(NVL(p_inactive_date, sysdate)) THEN
1864 wsh_xc_util.log_exception(
1865 p_api_version => 1.0,
1866 x_return_status => l_return_status,
1867 x_msg_count => l_exception_msg_count,
1868 x_msg_data => l_exception_msg_data,
1869 x_exception_id => l_dummy_exception_id ,
1870 p_logged_at_location_id => p_location_id,
1871 p_exception_location_id => p_location_id,
1872 p_logging_entity => 'SHIPPER',
1873 p_logging_entity_id => FND_GLOBAL.USER_ID,
1874 p_exception_name => 'WSH_LOCATION_REGIONS_1',
1875 p_message => 'WSH_LOCATION_REGIONS_1_ERR'
1876 );
1877 END IF;
1878
1879 END IF;
1880
1881 x_return_status := l_return_status;
1882
1883 IF l_debug_on THEN
1884 WSH_DEBUG_SV.pop(l_module_name);
1885 END IF;
1886
1887 EXCEPTION
1888
1889 WHEN Insert_Failed THEN
1890
1891 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1892 IF l_debug_on THEN
1893
1894 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in API Insert_Record');
1895 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INSERT_FAILED');
1896 ELSE
1897 WSH_UTIL_CORE.printmsg('Failed in API Insert_Record');
1898 END IF;
1899
1900 rollback to wsh_location_exists;
1901
1902 WHEN Others THEN
1903
1904 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1905 l_sqlcode := SQLCODE;
1906 l_sqlerr := SQLERRM;
1907 IF l_debug_on THEN
1908 WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1909 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1910 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1911 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1912 ELSE
1913 WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1914 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1915 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1916 END IF;
1917
1918
1919 rollback to wsh_location_exists;
1920
1921 END Map_Location_To_Region;
1922
1923 /*===========================================================================+
1924 | FUNCTION |
1925 | Insert_Record |
1926 | |
1927 | DESCRIPTION |
1928 | This API just inserts the record into intersection table |
1929 | |
1930 +===========================================================================*/
1931
1932 Procedure Insert_Record
1933 (
1934 p_location_id IN NUMBER,
1935 p_region_id IN NUMBER,
1936 p_region_type IN NUMBER,
1937 p_exception IN VARCHAR2,
1938 p_location_source IN VARCHAR2,
1939 p_parent_region IN VARCHAR2,
1940 x_return_status OUT NOCOPY VARCHAR2
1941 ) IS
1942
1943 l_region_id NUMBER := 0;
1944 l_sqlcode NUMBER;
1945 l_sqlerr VARCHAR2(2000);
1946
1947 BEGIN
1948
1949 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1950
1951 INSERT INTO WSH_REGION_LOCATIONS(
1952 region_id,
1953 location_id,
1954 exception_type,
1955 region_type,
1956 parent_region_flag,
1957 location_source,
1958 creation_date,
1959 created_by,
1960 last_update_date,
1961 last_updated_by,
1962 last_update_login )
1963 values (
1964 p_region_id,
1965 p_location_id,
1966 p_exception,
1967 p_region_type,
1968 p_parent_region,
1969 p_location_source,
1970 sysdate,
1971 fnd_global.user_id,
1972 sysdate,
1973 fnd_global.user_id,
1974 fnd_global.login_id
1975 );
1976
1977 EXCEPTION
1978
1979 WHEN Others THEN
1980
1981 WSH_UTIL_CORE.printmsg(' Insert into WSH_REGION_LOCATIONS failed : ' || sqlerrm);
1982 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1983
1984 End Insert_Record;
1985
1986
1987 /*===========================================================================+
1988 | FUNCTION |
1989 | Rule_Location |
1990 | |
1991 | DESCRIPTION |
1992 | This is the rule function for the following TCA events : |
1993 | # oracle.apps.ar.hz.Location.create |
1994 | # oracle.apps.ar.hz.Location.update |
1995 | This calls the Mapping_Regions_Main API to recreate the |
1996 | mapping once a location gets created or a location gets |
1997 | updated. |
1998 | |
1999 +===========================================================================*/
2000
2001 FUNCTION Rule_Location(
2002 p_subscription_guid in raw,
2003 p_event in out NOCOPY wf_event_t)
2004 RETURN VARCHAR2 IS
2005
2006 i_status varchar2(200);
2007 myList wf_parameter_list_t;
2008 pos number := 1;
2009
2010 l_return_status VARCHAR2(20);
2011 l_return_status1 VARCHAR2(20);
2012 p_location_id NUMBER;
2013 l_sqlcode NUMBER;
2014 l_sqlerr VARCHAR2(2000);
2015
2016 l_org_id NUMBER;
2017 l_user_id NUMBER;
2018 l_resp_id NUMBER;
2019 l_resp_appl_id NUMBER;
2020 l_security_group_id NUMBER;
2021
2022 l_pkg_name VARCHAR2(200);
2023 l_proc_name VARCHAR2(200);
2024 e_loc EXCEPTION;
2025
2026 --
2027 l_debug_on BOOLEAN;
2028 --
2029 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Rule_Location';
2030
2031 BEGIN
2032
2033 l_org_id := p_event.getValueForParameter('ORG_ID');
2034 l_user_id := p_event.getValueForParameter('USER_ID');
2035 l_resp_id := p_event.getValueForParameter('RESP_ID');
2036 l_resp_appl_id := p_event.getValueForParameter('RESP_APPL_ID');
2037 l_security_group_id := p_event.getValueForParameter('SECURITY_GROUP_ID');
2038
2039 fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id,l_security_group_id);
2040
2041 --
2042 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2043 --
2044 IF l_debug_on IS NULL
2045 THEN
2046 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2047 END IF;
2048 --
2049 --
2050 -- Debug Statements
2051 --
2052 IF l_debug_on THEN
2053 WSH_DEBUG_SV.push(l_module_name);
2054 WSH_DEBUG_SV.log(l_module_name,'USER_ID: ',l_user_id);
2055 WSH_DEBUG_SV.log(l_module_name,'RESP_ID : ',l_resp_id);
2056 WSH_DEBUG_SV.log(l_module_name,'RESP_APPL_ID: ',l_resp_appl_id);
2057 WSH_DEBUG_SV.log(l_module_name,'SECURITY_GROUP_ID: ',l_security_group_id);
2058 WSH_DEBUG_SV.log(l_module_name,'USERENV LANG: ',USERENV('LANG'));
2059 END IF;
2060
2061 myList := p_event.getParameterList();
2062
2063 IF (myList is null) THEN
2064 IF l_debug_on THEN
2065 WSH_DEBUG_SV.pop(l_module_name);
2066 END IF;
2067 return NULL;
2068 END IF;
2069
2070 pos := myList.LAST;
2071
2072 WHILE (pos is not null)
2073 LOOP
2074
2075 IF myList(pos).getName() = 'LOCATION_ID' THEN
2076 p_location_id := myList(pos).getValue();
2077 END IF;
2078
2079 pos := myList.PRIOR(pos);
2080
2081 END LOOP;
2082
2083
2084 WSH_LOCATIONS_PKG.Process_Locations(
2085 p_location_type => 'EXTERNAL'
2086 , p_from_location => p_location_id
2087 , p_to_location => p_location_id
2088 , p_start_date => NULL
2089 , p_end_date => NULL
2090 , x_return_status => l_return_status1
2091 , x_sqlcode => l_sqlcode
2092 , x_sqlerr => l_sqlerr );
2093
2094 IF l_debug_on THEN
2095 WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
2096 END IF;
2097
2098 IF ( l_return_status1 NOT IN
2099 (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) ) THEN
2100 l_pkg_name := 'WSH_LOCATIONS_PKG';
2101 l_proc_name := 'Process_Locations';
2102 raise e_loc;
2103 END IF;
2104
2105 WSH_MAP_LOCATION_REGION_PKG.Mapping_Regions_Main(
2106 p_location_type => 'EXTERNAL'
2107 , p_from_location => p_location_id
2108 , p_to_location => p_location_id
2109 , p_start_date => NULL
2110 , p_end_date => NULL
2111 , p_insert_flag => TRUE
2112 , x_return_status => l_return_status
2113 , x_sqlcode => l_sqlcode
2114 , x_sqlerr => l_sqlerr );
2115
2116 IF l_debug_on THEN
2117 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2118 END IF;
2119
2120 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2121 IF l_debug_on THEN
2122 WSH_DEBUG_SV.pop(l_module_name);
2123 END IF;
2124 return 'SUCCESS';
2125 ELSE
2126 l_pkg_name := 'WSH_MAP_LOCATION_REGION_PKG';
2127 l_proc_name := 'Mapping_Regions_Main';
2128 raise e_loc;
2129 /*
2130 IF l_debug_on THEN
2131 WSH_DEBUG_SV.pop(l_module_name);
2132 END IF;
2133 return 'ERROR';
2134 */
2135 END IF;
2136
2137
2138 EXCEPTION
2139
2140 WHEN e_loc THEN
2141 WF_CORE.CONTEXT(l_pkg_name,l_proc_name,
2142 p_event.getEventName( ), p_subscription_guid);
2143 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2144
2145 IF l_debug_on THEN
2146 WSH_DEBUG_SV.pop(l_module_name);
2147 END IF;
2148 return 'ERROR';
2149 WHEN Others THEN
2150 WF_CORE.CONTEXT('WSH_MAP_LOCATIONS_REGIONS', 'Rule_Location',
2151 p_event.getEventName( ), p_subscription_guid);
2152 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2153 IF l_debug_on THEN
2154 WSH_DEBUG_SV.pop(l_module_name);
2155 END IF;
2156 return 'ERROR';
2157
2158 END Rule_Location;
2159
2160 PROCEDURE Transfer_Location (
2161 p_source_type IN VARCHAR2,
2162 p_source_location_id IN NUMBER,
2163 p_transfer_location IN BOOLEAN,
2164 p_online_region_mapping IN BOOLEAN,
2165 p_caller IN VARCHAR2 DEFAULT NULL,
2166 x_loc_rec OUT NOCOPY loc_rec_type,
2167 x_return_status OUT NOCOPY VARCHAR2 ) IS
2168
2169 l_exists VARCHAR2(10);
2170 l_errbuf VARCHAR2(1000);
2171 l_retcode NUMBER;
2172 l_location_source_type VARCHAR2(20);
2173 l_map_regions VARCHAR2(1);
2174
2175 CURSOR Get_Location_Data IS
2176 SELECT wsh_location_id,
2177 source_location_id,
2178 location_source_code,
2179 location_code,
2180 ui_location_code,
2181 address1,
2182 address2,
2183 address3,
2184 address4,
2185 country,
2186 state,
2187 province,
2188 county,
2189 city,
2190 postal_code,
2191 inactive_date
2192 FROM wsh_locations
2193 WHERE source_location_id = p_source_location_id;
2194
2195
2196 CURSOR Get_Location_Data1 IS
2197 SELECT wsh_location_id,
2198 source_location_id,
2199 location_source_code,
2200 location_code,
2201 ui_location_code,
2202 address1,
2203 address2,
2204 address3,
2205 address4,
2206 country,
2207 state,
2208 province,
2209 county,
2210 city,
2211 postal_code,
2212 inactive_date
2213 FROM wsh_locations
2214 WHERE wsh_location_id = p_source_location_id;
2215
2216 l_return_status VARCHAR2(20);
2217 l_sqlcode NUMBER;
2218 l_sqlerr VARCHAR2(2000);
2219 --
2220 l_debug_on BOOLEAN;
2221 --
2222 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Transfer_Location';
2223 BEGIN
2224
2225 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2226 --
2227 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2228 --
2229 IF l_debug_on IS NULL
2230 THEN
2231 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2232 END IF;
2233 IF l_debug_on THEN
2234 WSH_DEBUG_SV.push(l_module_name);
2235 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2236 --
2237 END IF;
2238 --
2239 -- Check if the location exists. If yes, then return the
2240 -- location data
2241 --
2242
2243 IF p_source_type = 'HR' or p_source_type = 'HZ' or p_source_type = 'HR_HZ' THEN
2244
2245 OPEN Get_Location_Data;
2246 FETCH Get_Location_Data INTO
2247 x_loc_rec.wsh_location_id,
2248 x_loc_rec.source_location_id,
2249 x_loc_rec.location_source_code,
2250 x_loc_rec.location_code,
2251 x_loc_rec.ui_location_code,
2252 x_loc_rec.address1,
2253 x_loc_rec.address2,
2254 x_loc_rec.address3,
2255 x_loc_rec.address4,
2256 x_loc_rec.country,
2257 x_loc_rec.state,
2258 x_loc_rec.province,
2259 x_loc_rec.county,
2260 x_loc_rec.city,
2261 x_loc_rec.postal_code,
2262 x_loc_rec.inactive_date;
2263
2264 CLOSE Get_Location_Data;
2265
2266 ELSIF p_source_type = 'WSH' THEN
2267
2268 OPEN Get_Location_Data1;
2269 FETCH Get_Location_Data1 INTO
2270 x_loc_rec.wsh_location_id,
2271 x_loc_rec.source_location_id,
2272 x_loc_rec.location_source_code,
2273 x_loc_rec.location_code,
2274 x_loc_rec.ui_location_code,
2275 x_loc_rec.address1,
2276 x_loc_rec.address2,
2277 x_loc_rec.address3,
2278 x_loc_rec.address4,
2279 x_loc_rec.country,
2280 x_loc_rec.state,
2281 x_loc_rec.province,
2282 x_loc_rec.county,
2283 x_loc_rec.city,
2284 x_loc_rec.postal_code,
2285 x_loc_rec.inactive_date;
2286
2287 CLOSE Get_Location_Data1;
2288
2289 END IF;
2290
2291 --
2292 -- If a location is found, then return.
2293 --
2294
2295 IF (x_loc_rec.wsh_location_id IS NOT NULL) THEN
2296 IF l_debug_on THEN
2297 WSH_DEBUG_SV.pop(l_module_name);
2298 END IF;
2299 RETURN;
2300 END IF;
2301
2302 --
2303 -- If a location is not found and if p_transfer_location
2304 -- is true, then create the location record in WSH_LOCATIONS
2305 -- table.
2306 --
2307
2308 IF ((p_source_location_id IS NOT NULL) AND
2309 (p_source_type in ('HR','HZ','HR_HZ')) AND
2310 (p_transfer_location)) THEN
2311
2312 IF p_source_type = 'HR' THEN
2313 l_location_source_type := 'INTERNAL' ;
2314 ELSIF p_source_type = 'HZ' THEN
2315 l_location_source_type := 'EXTERNAL' ;
2316 ELSIF p_source_type = 'HR_HZ' THEN
2317 l_location_source_type := 'BOTH' ;
2318 END IF;
2319
2320 IF p_online_region_mapping THEN
2321 l_map_regions := 'Y';
2322 ELSE
2323 l_map_regions := 'N';
2324 END IF;
2325
2326 --
2327 IF l_debug_on THEN
2328 WSH_DEBUG_SV.log(l_module_name,'-----------------------------');
2329 WSH_DEBUG_SV.log(l_module_name,'Calling procedure Process_Locations');
2330 END IF;
2331 WSH_LOCATIONS_PKG.Process_Locations (
2332 p_location_type => l_location_source_type,
2333 p_from_location => p_source_location_id,
2334 p_to_location => p_source_location_id,
2335 p_start_date => NULL,
2336 p_end_date => NULL,
2337 p_caller => p_caller,
2338 x_return_status => l_return_status,
2339 x_sqlcode => l_sqlcode,
2340 x_sqlerr => l_sqlerr);
2341
2342 IF l_return_status NOT IN
2343 (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2344 --
2345 IF l_debug_on THEN
2346 WSH_DEBUG_SV.log(l_module_name,'Failed in Procedure Process_Locations');
2347 END IF;
2348 END IF;
2349
2350 IF l_map_regions = 'Y' AND l_return_status NOT IN
2351 (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2352
2353 IF l_debug_on THEN
2354 WSH_DEBUG_SV.log(l_module_name,'----------------------------------');
2355 WSH_DEBUG_SV.log(l_module_name,'*** Map Regions parameter is Yes ***');
2356 WSH_DEBUG_SV.log(l_module_name,'Calling procedure Mapping_Regions_Main');
2357 END IF;
2358
2359 Mapping_Regions_Main (
2360 p_location_type => l_location_source_type,
2361 p_from_location => p_source_location_id,
2362 p_to_location => p_source_location_id,
2363 p_start_date => NULL,
2364 p_end_date => NULL,
2365 p_insert_flag => TRUE,
2366 x_return_status => l_return_status,
2367 x_sqlcode => l_sqlcode,
2368 x_sqlerr => l_sqlerr);
2369
2370 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2371 IF l_debug_on THEN
2372 WSH_DEBUG_SV.log(l_module_name,'Failed in Procedure Mapping_Regions_Main');
2373 END IF;
2374 END IF;
2375 END IF;
2376
2377 /*
2378 WSH_MAP_LOCATION_REGION_PKG.Map_Locations_Child_Program (
2379 p_errbuf => l_errbuf,
2380 p_retcode => l_retcode,
2381 p_location_type => l_location_source_type,
2382 p_map_regions => l_map_regions,
2383 p_from_location => p_source_location_id,
2384 p_to_location => p_source_location_id,
2385 p_start_date => NULL,
2386 p_end_date => NULL);
2387 */
2388
2389 OPEN Get_Location_Data;
2390 FETCH Get_Location_Data INTO
2391 x_loc_rec.wsh_location_id,
2392 x_loc_rec.source_location_id,
2393 x_loc_rec.location_source_code,
2394 x_loc_rec.location_code,
2395 x_loc_rec.ui_location_code,
2396 x_loc_rec.address1,
2397 x_loc_rec.address2,
2398 x_loc_rec.address3,
2399 x_loc_rec.address4,
2400 x_loc_rec.country,
2401 x_loc_rec.state,
2402 x_loc_rec.province,
2403 x_loc_rec.county,
2404 x_loc_rec.city,
2405 x_loc_rec.postal_code,
2406 x_loc_rec.inactive_date;
2407
2408 CLOSE Get_Location_Data;
2409
2410 END IF;
2411
2412 IF x_loc_rec.wsh_location_id IS NULL THEN
2413 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2414 END IF;
2415 IF l_debug_on THEN
2416 WSH_DEBUG_SV.log(l_module_name,'Returning wsh_location_id : '||x_loc_rec.wsh_location_id);
2417 WSH_DEBUG_SV.pop(l_module_name);
2418 END IF;
2419
2420 EXCEPTION
2421
2422 WHEN OTHERS THEN
2423 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2424 IF l_debug_on THEN
2425 WSH_DEBUG_SV.pop(l_module_name);
2426 END IF;
2427
2428 END Transfer_Location;
2429
2430 /*===========================================================================+
2431 | PROCEDURE |
2432 | Location_User_Hook_API |
2433 | |
2434 | DESCRIPTION |
2435 | This API is called by Create_Location/Update_Location |
2436 | Business Process - After Process User Hook. |
2437 | This ensures that the whenever HR location is created or |
2438 | updated, corresponding changes in WSH_LOCATIONS and |
2439 | WSH_REGION_LOCATIONS happens |
2440 +===========================================================================*/
2441
2442 PROCEDURE Location_User_Hook_API(
2443 p_location_id IN NUMBER) IS
2444
2445 l_return_status VARCHAR2(15);
2446 l_return_status1 VARCHAR2(15);
2447 l_sqlcode NUMBER;
2448 l_sqlerr VARCHAR2(2000);
2449 l_location_id NUMBER;
2450 l_wsh_location_id NUMBER;
2451 l_organization_id NUMBER;
2452 map_region BOOLEAN := FALSE;
2453 terr_short_name FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%TYPE;
2454 terr_code FND_TERRITORIES_TL.TERRITORY_CODE%TYPE;
2455 l_region HR_LOCATIONS_ALL.REGION_2%TYPE;
2456 l_city HR_LOCATIONS_ALL.TOWN_OR_CITY%TYPE;
2457 l_postal_code HR_LOCATIONS_ALL.POSTAL_CODE%TYPE;
2458 l_location_source VARCHAR2(4);
2459 l_inactive_date DATE;
2460
2461 --Bug 6940375
2462 l_wsh_loc_id NUMBER;
2463 --Bug 6940375
2464
2465 CURSOR Get_Internal_Locations IS
2466 SELECT
2467 l.location_id,
2468 t.territory_short_name,
2469 t.territory_code,
2470 l.region_2 state,
2471 l.town_or_city city,
2472 l.postal_code,
2473 l.inactive_date
2474 FROM
2475 hr_locations_all l,
2476 fnd_territories_tl t
2477 WHERE
2478 t.territory_code = l.country and
2479 t.language = userenv('LANG') and
2480 l.location_id = p_location_id;
2481
2482 --Bug 6940375 Creating a new Cursor
2483
2484 CURSOR Get_Internal_Loc IS
2485 SELECT
2486 l.location_id
2487 FROM
2488 hr_locations_all l
2489 WHERE
2490 l.location_id = p_location_id;
2491
2492 --Bug 6940375
2493
2494 CURSOR check_wsh_loc(c_loc_id IN NUMBER) IS
2495 SELECT wsh_location_id
2496 FROM wsh_locations
2497 WHERE source_location_id = c_loc_id
2498 AND location_source_code = 'HR';
2499
2500 CURSOR check_company(c_loc_id IN NUMBER) IS
2501 SELECT ou.organization_id
2502 FROM hr_all_organization_units ou,
2503 mtl_parameters mp
2504 WHERE mp.organization_id = ou.organization_id
2505 AND ou.location_id = c_loc_id;
2506
2507 BEGIN
2508
2509 l_location_source := 'HR';
2510
2511 --
2512 -- Create a record in WSH_LOCATIONS
2513 --
2514 --Bug 6940375 Start (Check if the HR location is deleted,
2515 --then delete location from wsh_locations)
2516 OPEN Get_Internal_Loc;
2517 FETCH Get_Internal_Loc INTO
2518 l_wsh_loc_id;
2519 IF Get_Internal_Loc%NOTFOUND THEN
2520 CLOSE Get_Internal_Loc;
2521 DELETE FROM wsh_locations
2522 WHERE wsh_location_id = p_location_id;
2523 RETURN ;
2524 END IF;
2525 CLOSE Get_Internal_Loc;
2526
2527 -- Should update only
2528 -- if caller is 'HR' and company is not found
2529 -- should not create
2530 WSH_LOCATIONS_PKG.Process_Locations(
2531 p_location_type => 'INTERNAL'
2532 , p_from_location => p_location_id
2533 , p_to_location => p_location_id
2534 , p_start_date => NULL
2535 , p_end_date => NULL
2536 , p_caller => 'HR'
2537 , x_return_status => l_return_status1
2538 , x_sqlcode => l_sqlcode
2539 , x_sqlerr => l_sqlerr );
2540
2541 --
2542 -- Create a record in WSH_REGION_LOCATIONS
2543 --
2544
2545 OPEN Get_Internal_Locations;
2546 FETCH Get_Internal_Locations INTO
2547 l_location_id,
2548 terr_short_name,
2549 terr_code,
2550 l_region,
2551 l_city,
2552 l_postal_code,
2553 l_inactive_date;
2554
2555 CLOSE Get_Internal_Locations;
2556
2557
2558 -- if company is not found
2559 -- Should go forward only in case of update
2560 -- should not in case of create
2561 --
2562 OPEN check_wsh_loc(p_location_id);
2563 FETCH check_wsh_loc INTO l_wsh_location_id;
2564 CLOSE check_wsh_loc;
2565
2566 IF l_wsh_location_id IS NOT NULL THEN
2567 map_region := TRUE;
2568 ELSE
2569 -- check if company exists for p_location_id
2570 OPEN check_company(p_location_id);
2571 FETCH check_company INTO l_organization_id;
2572 CLOSE check_company;
2573
2574 IF l_organization_id IS NOT NULL THEN
2575 map_region := TRUE;
2576 END IF;
2577 END IF;
2578
2579 -- Calling the API Map_Location_To_Region and not Mapping_Regions_Main
2580 -- (as done in function rule_location) because the API that is being
2581 -- called during User Hook should not have any commit statements. The
2582 -- records will be commited by the HRMS API's.
2583 --
2584 IF map_region THEN
2585
2586 Map_Location_To_Region (
2587 p_country => terr_short_name,
2588 p_country_code => terr_code,
2589 p_state => l_region,
2590 p_city => l_city,
2591 p_postal_code => l_postal_code,
2592 p_location_id => l_location_id,
2593 p_location_source => l_location_source,
2594 p_inactive_date => l_inactive_date,
2595 x_return_status => l_return_status,
2596 x_sqlcode => l_sqlcode,
2597 x_sqlerr => l_sqlerr );
2598
2599 END IF;
2600
2601 END Location_User_Hook_API;
2602
2603 PROCEDURE Get_Transit_Time(p_ship_from_loc_id IN NUMBER,
2604 p_ship_to_site_id IN NUMBER,
2605 p_ship_method_code IN VARCHAR2 DEFAULT NULL,
2606 p_carrier_id IN NUMBER,
2607 p_service_code IN VARCHAR2,
2608 p_mode_code IN VARCHAR2,
2609 p_from IN VARCHAR2,
2610 x_transit_time OUT NOCOPY NUMBER,
2611 x_return_status OUT NOCOPY VARCHAR2) IS
2612
2613 l_ship_method_code VARCHAR2(30);
2614 l_prev_ship_method VARCHAR2(30);
2615 l_session_id NUMBER;
2616 l_transit_time NUMBER;
2617 Region_Loc_zone_tab WSH_UTIL_CORE.Id_Tab_Type;
2618 Loc_Region_zone_tab WSH_UTIL_CORE.Id_Tab_Type;
2619 get_From_Region_tab WSH_UTIL_CORE.Id_Tab_Type;
2620 get_To_Region_tab WSH_UTIL_CORE.Id_Tab_Type ;
2621 l_return_status VARCHAR2(55);
2622 dummy number (5);
2623 CURSOR get_session_id IS
2624 SELECT mrp_atp_schedule_temp_s.nextVal
2625 FROM dual;
2626
2627 --
2628 l_debug_on BOOLEAN;
2629 --
2630 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TRANSIT_TIME';
2631 --
2632 /*This Cursor gets transit time if transit time is defined between two locations */
2633 cursor get_Transit_Loc_Loc (p_from_loc_id NUMBER, p_to_loc_id NUMBER, p_ship_method VARCHAR2) IS
2634 select intransit_time
2635 from mtl_interorg_ship_methods
2636 where from_location_id = p_from_loc_id
2637 and to_location_id = p_to_loc_id
2638 and ship_method = p_ship_method_code;
2639
2640 /*This Cursor gets transit time if transit time is defined between FROM Region TO location */
2641 CURSOR get_Transit_Region_Loc (p_from_region_id NUMBER, p_to_loc_id NUMBER, p_ship_method VARCHAR2) IS
2642 SELECT intransit_time
2643 FROM mtl_interorg_ship_methods
2644 WHERE from_region_id = p_from_region_id
2645 AND to_location_id = p_to_loc_id
2646 AND ship_method = p_ship_method_code;
2647
2648 /*This Cursor gets transit time if transit time is defined between FROM Location TO region */
2649 CURSOR get_Transit_Loc_Region (p_from_loc_id NUMBER, p_to_region_id NUMBER, p_ship_method VARCHAR2) IS
2650 SELECT intransit_time
2651 FROM mtl_interorg_ship_methods
2652 WHERE from_location_id = p_from_loc_id
2653 AND to_region_id = p_to_region_id
2654 AND ship_method = p_ship_method_code;
2655
2656 /*This Cursor gets transit time if transit time is defined between FROM region TO region*/
2657 CURSOR get_Transit_Region_Region (p_from_region_id NUMBER, p_to_region_id NUMBER, p_ship_method VARCHAR2) IS
2658 SELECT intransit_time
2659 FROM mtl_interorg_ship_methods
2660 WHERE from_region_id = p_from_region_id
2661 AND to_region_id = p_to_region_id
2662 AND ship_method = p_ship_method_code;
2663
2664 CURSOR get_ship_method_cur IS
2665 SELECT ship_method_code
2666 FROM wsh_carrier_services
2667 WHERE carrier_id = p_carrier_id
2668 AND enabled_flag = 'Y'
2669 AND ( (p_mode_code IS NULL AND mode_of_transport IS NULL)
2670 OR (p_mode_code IS NOT NULL AND mode_of_transport = p_mode_code)
2671 )
2672 AND ( (p_service_code IS NULL AND service_level IS NULL)
2673 OR (p_service_code IS NOT NULL AND service_level = p_service_code)
2674 );
2675
2676 BEGIN
2677 --
2678 l_transit_time := NULL;
2679 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2680 --
2681 IF l_debug_on IS NULL
2682 THEN
2683 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2684 END IF;
2685 --
2686 --
2687 -- Debug Statements
2688 --
2689 IF l_debug_on THEN
2690 WSH_DEBUG_SV.push(l_module_name);
2691 --
2692 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOC_ID',P_SHIP_FROM_LOC_ID);
2693 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
2694 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
2695 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
2696 WSH_DEBUG_SV.log(l_module_name,'P_SERVICE_CODE',P_SERVICE_CODE);
2697 WSH_DEBUG_SV.log(l_module_name,'P_MODE_CODE',P_MODE_CODE);
2698 END IF;
2699 --
2700
2701 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
2702 x_transit_time := null;
2703
2704 IF p_ship_method_code IS NULL THEN
2705
2706 OPEN get_ship_method_cur;
2707 FETCH get_ship_method_cur INTO l_ship_method_code;
2708 CLOSE get_ship_method_cur;
2709 IF l_debug_on THEN
2710 WSH_DEBUG_SV.logmsg(l_module_name,'ship method derived is ' || l_ship_method_code);
2711 END IF;
2712 ELSE
2713 l_ship_method_code := p_ship_method_code;
2714 END IF;
2715
2716 IF l_debug_on THEN
2717 WSH_DEBUG_SV.logmsg(l_module_name, 'Check for cached transit time');
2718 END IF;
2719
2720 IF (p_from = 'OM') THEN
2721 -- check if the transit time for this ship method , origin and destination is already cached
2722 FOR k IN 1..g_ship_method_tab.COUNT LOOP
2723 IF (l_ship_method_code = g_ship_method_tab(k)) AND
2724 (p_ship_from_loc_id = g_ship_from_loc_tab(k)) AND
2725 (p_ship_to_site_id = g_ship_to_site_tab(k)) THEN
2726 --(g_transit_time_tab(k) IS NOT NULL) THEN
2727 x_transit_time := g_transit_time_tab(k);
2728
2729 IF l_debug_on THEN
2730 WSH_DEBUG_SV.logmsg(l_module_name,'found ship method transit time information cached');
2731 WSH_DEBUG_SV.pop(l_module_name);
2732 END IF;
2733
2734 RETURN;
2735 END IF;
2736 END LOOP;
2737 END IF;
2738
2739 IF l_debug_on THEN
2740 WSH_DEBUG_SV.logmsg(l_module_name, 'Transit Time Not cached',WSH_DEBUG_SV.C_PROC_LEVEL);
2741 END IF;
2742
2743 --The ship method returned by MSC_ATP_PROC.ATP_Shipping_Lead_Time could be different
2744 --from the ship method passed in. If it doesn't find the transit time, it returns
2745 --the default ship method and default ship method transit time.
2746 l_prev_ship_method := l_ship_method_code;
2747
2748
2749 --OM Callers pass the ship_to_site_id, and get the transit time from the
2750 --MSC_ATP_PROC.Shiping_Lead_Time API
2751 IF (p_from = 'OM') THEN
2752
2753 OPEN get_session_id;
2754 FETCH get_session_id INTO l_session_id;
2755 CLOSE get_session_id;
2756
2757 IF l_debug_on THEN
2758 WSH_DEBUG_SV.logmsg(l_module_name,'MSC_ATP session id = ' || l_session_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2759 END IF;
2760
2761 MSC_ATP_PROC.ATP_Shipping_Lead_Time(p_from_loc_id => p_ship_from_loc_id,
2762 p_to_customer_site_id => p_ship_to_site_id,
2763 p_session_id => l_session_id,
2764 x_ship_method => l_ship_method_code,
2765 x_intransit_time => l_transit_time,
2766 x_return_status => x_return_status);
2767
2768 IF l_debug_on THEN
2769 WSH_DEBUG_SV.logmsg(l_module_name,'MSC_ATP_PROC.ATP_Shipping_Lead_Time is ' || l_transit_time,WSH_DEBUG_SV.C_PROC_LEVEL);
2770 END IF;
2771
2772 --FTE callers pass in the ultimate_dropoff_location_id
2773 ELSE
2774
2775 --Bug 4653381 Start
2776 /* From Location, To Location */
2777 OPEN get_Transit_Loc_Loc(p_ship_from_loc_id,p_ship_to_site_id,l_ship_method_code);
2778 FETCH get_Transit_Loc_Loc INTO l_transit_time;
2779 CLOSE get_Transit_Loc_Loc;
2780 IF l_transit_time IS NOT NULL THEN
2781 IF l_debug_on THEN
2782 WSH_DEBUG_SV.log(l_module_name,'Found the transit time from Location to location: ', l_transit_time);
2783 END IF;
2784 x_transit_time := l_transit_time;
2785 RETURN;
2786 END IF;
2787
2788 /* From Region, To Location */
2789
2790
2791 WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches(p_location_id =>p_ship_from_loc_id,
2792 x_region_tab => get_From_Region_tab,
2793 p_lang_code=>USERENV('LANG'),
2794 x_return_status =>l_return_status);
2795
2796 IF get_From_Region_tab.count >0 THEN
2797 FOR i IN get_From_Region_tab.FIRST..get_From_Region_tab.LAST LOOP
2798 OPEN get_Transit_Region_Loc(get_From_Region_tab(i),p_ship_to_site_id,p_ship_method_code);
2799 FETCH get_Transit_Region_Loc into l_transit_time;
2800 CLOSE get_Transit_Region_Loc;
2801
2802 IF l_transit_time IS NOT NULL THEN
2803 IF l_debug_on THEN
2804 WSH_DEBUG_SV.log(l_module_name,'Found the transit time from Region to Location : ',l_transit_time);
2805 END IF;
2806 x_transit_time :=l_transit_time;
2807 RETURN;
2808 END IF;
2809
2810
2811 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2812 p_region_id =>get_From_Region_tab(i),
2813 x_zone_tab =>Region_Loc_zone_tab,
2814 x_return_status =>l_return_status);
2815
2816 IF Region_Loc_zone_tab.count >0 THEN
2817 FOR j in Region_Loc_zone_tab.FIRST..Region_Loc_zone_tab.LAST LOOP
2818 OPEN get_Transit_Region_Loc(Region_Loc_zone_tab(j),p_ship_to_site_id,p_ship_method_code);
2819 FETCH get_Transit_Region_Loc INTO l_transit_time;
2820 CLOSE get_Transit_Region_Loc;
2821
2822 IF l_transit_time IS NOT NULL THEN
2823 IF l_debug_on THEN
2824 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Region to Location : ',l_transit_time);
2825 END IF;
2826 x_transit_time :=l_transit_time;
2827 RETURN;
2828 END IF;
2829 END LOOP;
2830 END IF;
2831 END LOOP;
2832 END IF;
2833
2834
2835 /* From Location, To Region */
2836
2837 WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches(p_location_id =>p_ship_to_site_id,
2838 p_lang_code=>USERENV('LANG'),
2839 x_region_tab => get_To_Region_tab,
2840 x_return_status =>l_return_status);
2841
2842
2843 IF get_To_Region_tab.COUNT >0 THEN
2844 FOR i in get_To_Region_tab.FIRST..get_To_Region_tab.LAST LOOP
2845 OPEN get_Transit_Loc_Region(p_ship_from_loc_id,get_To_Region_tab(i),p_ship_method_code);
2846 FETCH get_Transit_Loc_Region INTO l_transit_time;
2847 CLOSE get_Transit_Loc_Region;
2848
2849 IF l_transit_time IS NOT NULL THEN
2850 IF l_debug_on THEN
2851 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Location to Region : ',l_transit_time);
2852 END IF;
2853 x_transit_time :=l_transit_time;
2854 RETURN;
2855 END IF;
2856
2857
2858 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2859 p_region_id =>get_To_Region_tab(i),
2860 x_zone_tab =>Loc_Region_zone_tab,
2861 x_return_status =>l_return_status);
2862
2863 IF Loc_Region_zone_tab.count >0 THEN
2864 FOR j in Loc_Region_zone_tab.FIRST..Loc_Region_zone_tab.LAST LOOP
2865 OPEN get_Transit_Loc_Region(p_ship_from_loc_id,Loc_Region_zone_tab(j),p_ship_method_code);
2866 FETCH get_Transit_Loc_Region INTO l_transit_time;
2867 CLOSE get_Transit_Loc_Region;
2868 IF l_transit_time IS NOT NULL THEN
2869 IF l_debug_on THEN
2870 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Location to Region : ',l_transit_time);
2871 END IF;
2872 x_transit_time :=l_transit_time;
2873 RETURN;
2874 END IF;
2875 END LOOP;
2876 END IF;
2877 END LOOP;
2878 END IF;
2879
2880
2881 /* From Region, To Region */
2882
2883 --1)Loop over to_region
2884
2885 IF get_To_Region_tab.COUNT >0 THEN
2886 FOR i in get_To_Region_tab.FIRST..get_To_Region_tab.LAST LOOP
2887 IF get_From_Region_tab.COUNT >0 THEN
2888 FOR j in get_From_Region_tab.FIRST..get_From_Region_tab.LAST LOOP
2889 OPEN get_Transit_Region_Region (get_From_Region_tab(j),get_To_Region_tab(i),p_ship_method_code);
2890 FETCH get_Transit_Region_Region INTO l_transit_time;
2891 CLOSE get_Transit_Region_Region;
2892
2893 IF l_transit_time IS NOT NULL THEN
2894 IF l_debug_on THEN
2895 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Region to Region : ',l_transit_time);
2896 END IF;
2897 x_transit_time :=l_transit_time;
2898 RETURN;
2899 END IF;
2900
2901 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2902 p_region_id =>get_From_Region_tab(j),
2903 x_zone_tab =>Region_Loc_zone_tab,
2904 x_return_status =>l_return_status);
2905 IF Region_Loc_zone_tab.count >0 THEN
2906 FOR m in Region_Loc_zone_tab.FIRST..Region_Loc_zone_tab.LAST LOOP
2907 OPEN get_Transit_Region_Region (Region_Loc_zone_tab(m),get_To_Region_tab(i),p_ship_method_code);
2908 FETCH get_Transit_Region_Region INTO l_transit_time;
2909 CLOSE get_Transit_Region_Region;
2910 IF l_transit_time IS NOT NULL THEN
2911 IF l_debug_on THEN
2912 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Zone to Zone : ',l_transit_time);
2913 END IF;
2914 x_transit_time :=l_transit_time;
2915 RETURN;
2916 END IF;
2917 END LOOP;
2918 END IF;
2919
2920
2921 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2922 p_region_id =>get_To_Region_tab(i),
2923 x_zone_tab =>Loc_Region_zone_tab,
2924 x_return_status =>l_return_status);
2925 IF Loc_Region_zone_tab.count >0 THEN
2926 FOR n IN Loc_Region_zone_tab.FIRST..Loc_Region_zone_tab.LAST LOOP
2927 IF Region_Loc_zone_tab.COUNT > 0 THEN
2928 FOR p IN Region_Loc_zone_tab.FIRST..Region_Loc_zone_tab.LAST LOOP
2929 OPEN get_Transit_Region_Region(Region_Loc_zone_tab(p),Loc_Region_zone_tab(n),p_ship_method_code);
2930 FETCH get_Transit_Region_Region INTO l_transit_time;
2931 CLOSE get_Transit_Region_Region;
2932 IF l_transit_time IS NOT NULL THEN
2933 IF l_debug_on THEN
2934 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Zone to Zone : ',l_transit_time);
2935 END IF;
2936 x_transit_time :=l_transit_time;
2937 RETURN;
2938 END IF;
2939 END LOOP ;
2940 END IF;
2941 END LOOP;
2942 END IF ;
2943
2944 END LOOP; --End of from_region (j)
2945 END IF;
2946 END LOOP; --End of To_region (i)
2947 END IF;
2948
2949 --2)Loop over from_region
2950
2951 IF get_From_Region_tab.COUNT >0 THEN
2952 FOR i in get_From_Region_tab.FIRST..get_From_Region_tab.LAST LOOP
2953 IF get_To_Region_tab.COUNT >0 THEN
2954 FOR j in get_To_Region_tab.FIRST..get_To_Region_tab.LAST LOOP
2955 OPEN get_Transit_Region_Region (get_From_Region_tab(i),get_To_Region_tab(j),p_ship_method_code);
2956 FETCH get_Transit_Region_Region INTO l_transit_time;
2957 CLOSE get_Transit_Region_Region;
2958
2959 IF l_transit_time IS NOT NULL THEN
2960 IF l_debug_on THEN
2961 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Region to Region : ',l_transit_time);
2962 END IF;
2963 x_transit_time :=l_transit_time;
2964 RETURN;
2965 END IF;
2966
2967 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2968 p_region_id =>get_To_Region_tab(j),
2969 x_zone_tab =>Loc_Region_zone_tab,
2970 x_return_status =>l_return_status);
2971 IF Loc_Region_zone_tab.count >0 THEN
2972 FOR m in Loc_Region_zone_tab.FIRST..Loc_Region_zone_tab.LAST LOOP
2973 OPEN get_Transit_Region_Region (get_From_Region_tab(i),Loc_Region_zone_tab(m),p_ship_method_code);
2974 FETCH get_Transit_Region_Region INTO l_transit_time;
2975 CLOSE get_Transit_Region_Region;
2976 IF l_transit_time IS NOT NULL THEN
2977 IF l_debug_on THEN
2978 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from from Region to Zone : ',l_transit_time);
2979 END IF;
2980 x_transit_time :=l_transit_time;
2981 RETURN;
2982 END IF;
2983 END LOOP;
2984 END IF;
2985
2986
2987 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2988 p_region_id =>get_From_Region_tab(i),
2989 x_zone_tab =>Region_Loc_zone_tab,
2990 x_return_status =>l_return_status);
2991 IF Region_Loc_zone_tab.count >0 THEN
2992 FOR n IN Region_Loc_zone_tab.FIRST..Region_Loc_zone_tab.LAST LOOP
2993 IF Loc_Region_zone_tab.COUNT >0 THEN
2994 FOR p IN Loc_Region_zone_tab.FIRST..Loc_Region_zone_tab.LAST LOOP
2995 OPEN get_Transit_Region_Region(Region_Loc_zone_tab(n),Loc_Region_zone_tab(p),p_ship_method_code);
2996 FETCH get_Transit_Region_Region INTO l_transit_time;
2997 CLOSE get_Transit_Region_Region;
2998 IF l_transit_time IS NOT NULL THEN
2999 IF l_debug_on THEN
3000 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Zone to Zone : ',l_transit_time);
3001 END IF;
3002 x_transit_time :=l_transit_time;
3003 RETURN;
3004 END IF;
3005 END LOOP ;
3006 END IF;
3007 END LOOP;
3008 END IF ;
3009
3010 END LOOP; --End of To_region (j)
3011 END IF;
3012 END LOOP; --End of From_region (i)
3013 END IF;
3014
3015
3016 --Bug 4653381 END
3017 END IF;
3018
3019 IF (p_from = 'OM') THEN
3020 --Cache the transit times
3021 IF (l_ship_method_code IS NOT NULL) THEN
3022 g_ship_method_tab(g_ship_method_tab.COUNT + 1) := l_ship_method_code;
3023 g_ship_from_loc_tab(g_ship_from_loc_tab.COUNT + 1) := p_ship_from_loc_id;
3024 g_ship_to_site_tab(g_ship_to_site_tab.COUNT + 1) := p_ship_to_site_id;
3025 g_transit_time_tab(g_transit_time_tab.COUNT + 1) := l_transit_time;
3026 END IF;
3027
3028 -- Bug 3357380
3029 -- If the ship_method is changed by ATP API, it means that Transit Time
3030 -- for the original ship method is not defined.
3031 IF (l_prev_ship_method <> l_ship_method_code) THEN
3032 g_ship_method_tab(g_ship_method_tab.COUNT + 1) := l_prev_ship_method;
3033 g_ship_from_loc_tab(g_ship_from_loc_tab.COUNT + 1) := p_ship_from_loc_id;
3034 g_ship_to_site_tab(g_ship_to_site_tab.COUNT + 1) := p_ship_to_site_id;
3035 g_transit_time_tab(g_transit_time_tab.COUNT + 1) := NULL;
3036 l_transit_time := NULL;
3037 END IF;
3038 END IF;
3039
3040 IF l_debug_on THEN
3041 WSH_DEBUG_SV.log(l_module_name,'Transit Time: ', l_transit_time);
3042 END IF;
3043
3044 x_transit_time := l_transit_time;
3045
3046 --
3047 -- Debug Statements
3048 --
3049 IF l_debug_on THEN
3050 WSH_DEBUG_SV.pop(l_module_name);
3051 END IF;
3052 --
3053 EXCEPTION
3054 WHEN OTHERS THEN
3055 IF l_debug_on THEN
3056 WSH_DEBUG_SV.log(l_module_name,'Exception occurred in WSH_MAP_LOCATION_REGION_PKG');
3057 WSH_DEBUG_SV.log(l_module_name,'SQLCODE: ',sqlcode);
3058 WSH_DEBUG_SV.log(l_module_name,'SQLERRM: ',SUBSTR(SQLERRM,1,200));
3059 WSH_DEBUG_SV.pop(l_module_name);
3060 END IF;
3061
3062
3063 END Get_Transit_Time;
3064
3065 --==============================================================================
3066 -- PROCEDURE : PREDEL_LOC_VALIDATION Added for bug Bug 6940375
3067 --
3068 -- PARAMETERS : p_location_id Input location id
3069 -- DESCRIPTION : This procedure checks if an Internal location is eligible
3070 -- for deletion. Shipping raises error if the location exists
3071 -- in shipping tables.
3072 --===============================================================================
3073
3074 PROCEDURE PREDEL_LOC_VALIDATION (p_location_id number)
3075 IS
3076 --
3077 v_delete_permitted varchar2(1) := NULL;
3078 l_msg varchar2(30) := 'WSH_LOC_RECORD_EXISTS';
3079 l_token varchar2(30);
3080 WSH_LOC_EXISTS EXCEPTION;
3081 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3082 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PREDEL_LOC_VALIDATION';
3083 BEGIN
3084
3085 IF l_debug_on THEN
3086 wsh_debug_sv.push(l_module_name);
3087 wsh_debug_sv.LOG(l_module_name, 'p_location_id', p_location_id);
3088 END IF;
3089 BEGIN
3090 l_token := 'WSH_DELIVERY_DETAILS';
3091 IF l_debug_on THEN
3092 wsh_debug_sv.LOG(l_module_name, 'Validating records in table : ', l_token);
3093 END IF;
3094 SELECT 'N'
3095 INTO v_delete_permitted
3096 FROM WSH_DELIVERY_DETAILS
3097 WHERE SHIP_FROM_LOCATION_ID = P_LOCATION_ID
3098 AND ROWNUM =1;
3099
3100 IF v_delete_permitted IS NOT NULL THEN
3101 RAISE WSH_LOC_EXISTS ;
3102 END IF;
3103
3104 EXCEPTION
3105 WHEN NO_DATA_FOUND THEN
3106 IF l_debug_on THEN
3107 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3108 END IF;
3109 END ;
3110
3111 BEGIN
3112 l_token := 'WSH_CALENDAR_ASSIGNMENTS';
3113 IF l_debug_on THEN
3114 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3115 END IF;
3116 v_delete_permitted := NULL;
3117 SELECT 'N'
3118 INTO v_delete_permitted
3119 FROM WSH_CALENDAR_ASSIGNMENTS WCA, WSH_LOCATIONS WSH
3120 WHERE WCA.LOCATION_ID = P_LOCATION_ID
3121 AND WSH.WSH_LOCATION_ID = WCA.LOCATION_ID
3122 AND WSH.LOCATION_SOURCE_CODE = 'HR'
3123 AND ROWNUM = 1;
3124
3125 IF v_delete_permitted IS NOT NULL THEN
3126 RAISE WSH_LOC_EXISTS ;
3127 END IF;
3128
3129 EXCEPTION
3130 WHEN NO_DATA_FOUND THEN
3131 IF l_debug_on THEN
3132 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3133 END IF;
3134 END ;
3135
3136
3137 BEGIN
3138 l_token := 'WSH_DOC_SEQUENCE_CATEGORIES';
3139 IF l_debug_on THEN
3140 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3141 END IF;
3142 v_delete_permitted := NULL;
3143 SELECT 'N'
3144 INTO v_delete_permitted
3145 FROM WSH_DOC_SEQUENCE_CATEGORIES WDO, WSH_LOCATIONS WSH
3146 WHERE WDO.LOCATION_ID = P_LOCATION_ID
3147 AND WSH.WSH_LOCATION_ID = WDO.LOCATION_ID
3148 AND WSH.LOCATION_SOURCE_CODE = 'HR'
3149 AND ROWNUM = 1;
3150
3151 IF v_delete_permitted IS NOT NULL THEN
3152 RAISE WSH_LOC_EXISTS ;
3153 END IF;
3154
3155 EXCEPTION
3156 WHEN NO_DATA_FOUND THEN
3157 IF l_debug_on THEN
3158 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3159 END IF;
3160 END ;
3161
3162
3163 BEGIN
3164 l_token := 'WSH_PICKING_RULES';
3165 IF l_debug_on THEN
3166 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3167 END IF;
3168 v_delete_permitted := NULL;
3169 SELECT 'N'
3170 INTO v_delete_permitted
3171 FROM WSH_PICKING_RULES
3172 WHERE SHIP_FROM_LOCATION_ID = P_LOCATION_ID
3173 AND ROWNUM = 1;
3174
3175 IF v_delete_permitted IS NOT NULL THEN
3176 RAISE WSH_LOC_EXISTS ;
3177 END IF;
3178
3179 EXCEPTION
3180 WHEN NO_DATA_FOUND THEN
3181 IF l_debug_on THEN
3182 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3183 END IF;
3184 END ;
3185
3186
3187 BEGIN
3188 l_token := 'WSH_REGION_LOCATIONS';
3189 IF l_debug_on THEN
3190 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3191 END IF;
3192 v_delete_permitted := NULL;
3193 SELECT 'N'
3194 INTO v_delete_permitted
3195 FROM WSH_REGION_LOCATIONS
3196 WHERE LOCATION_ID = P_LOCATION_ID
3197 AND REGION_ID IS NOT NULL
3198 AND ROWNUM = 1 ;
3199
3200 IF v_delete_permitted IS NOT NULL THEN
3201 RAISE WSH_LOC_EXISTS ;
3202 END IF;
3203
3204 EXCEPTION
3205 WHEN NO_DATA_FOUND THEN
3206 IF l_debug_on THEN
3207 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3208 END IF;
3209 END ;
3210
3211 BEGIN
3212 l_token := 'WSH_SHIPPING_PARAMETERS';
3213 IF l_debug_on THEN
3214 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3215 END IF;
3216 v_delete_permitted := NULL;
3217 SELECT 'N'
3218 INTO v_delete_permitted
3219 FROM WSH_SHIPPING_PARAMETERS
3220 WHERE LOCATION_ID = P_LOCATION_ID
3221 AND ROWNUM = 1 ;
3222
3223 IF v_delete_permitted IS NOT NULL THEN
3224 RAISE WSH_LOC_EXISTS ;
3225 END IF;
3226
3227 EXCEPTION
3228 WHEN NO_DATA_FOUND THEN
3229 IF l_debug_on THEN
3230 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3231 END IF;
3232 END ;
3233
3234 IF l_debug_on THEN
3235 wsh_debug_sv.LOG(l_module_name, 'No records exists in shipping tables for location id : ', p_location_id);
3236 WSH_DEBUG_SV.pop(l_module_name);
3237 END IF;
3238
3239
3240 EXCEPTION
3241 WHEN WSH_LOC_EXISTS THEN
3242 IF l_debug_on THEN
3243 wsh_debug_sv.LOG(l_module_name, 'Records found in table : ', l_token);
3244 WSH_DEBUG_SV.pop(l_module_name);
3245 END IF;
3246 FND_MESSAGE.SET_NAME('WSH','WSH_LOC_RECORD_EXISTS');
3247 FND_MESSAGE.SET_TOKEN('TABLE_NAME', l_token);
3248 APP_EXCEPTION.RAISE_EXCEPTION;
3249
3250 END PREDEL_LOC_VALIDATION;
3251
3252
3253 END WSH_MAP_LOCATION_REGION_PKG;
3254
[Home] [Help]