1 PACKAGE BODY WSH_MAP_LOCATION_REGION_PKG AS
2 /* $Header: WSHMLORB.pls 120.16.12010000.3 2009/04/01 08:37:06 ueshanka 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 --R12.1.1 Standalone Project - Start
1262 --Commit the data only if its Normal Mode
1263 IF G_MODE = 'STANDALONE' THEN
1264 --
1265 IF l_debug_on THEN
1266 WSH_DEBUG_SV.logmsg(l_module_name, 'Code executed in standalone mode so not commiting the data');
1267 END IF;
1268 --
1269 ELSE
1270 --
1271 IF l_debug_on THEN
1272 WSH_DEBUG_SV.logmsg(l_module_name, 'Code executed in normal mode, so commit the data');
1273 END IF;
1274 --
1275 COMMIT;
1276 END IF;
1277 --R12.1.1 Standalone Project - End
1278 END IF;
1279
1280 IF l_debug_on THEN
1281 WSH_DEBUG_SV.pop(l_module_name);
1282 END IF;
1283
1284 EXCEPTION
1285
1286 WHEN No_Data_Found THEN
1287
1288 IF l_debug_on THEN
1289 WSH_DEBUG_SV.logmsg(l_module_name,'No records found for the entered parameters');
1290 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1291 ELSE
1292 WSH_UTIL_CORE.printmsg('No records found for the entered parameters');
1293 END IF;
1294
1295 WHEN Others THEN
1296 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1297 x_sqlcode := SQLCODE;
1298 x_sqlerr := SQLERRM;
1299
1300 IF l_debug_on THEN
1301 WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Mapping_Regions_Main ');
1302 WSH_DEBUG_SV.logmsg(l_module_name,x_sqlcode);
1303 WSH_DEBUG_SV.logmsg(l_module_name,x_sqlerr);
1304 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1305 ELSE
1306 WSH_UTIL_CORE.printmsg('When Others of Procedure Mapping_Regions_Main ');
1307 WSH_UTIL_CORE.printmsg(x_sqlcode);
1308 WSH_UTIL_CORE.printmsg(x_sqlerr);
1309 END IF;
1310
1311 END Mapping_Regions_Main;
1312
1313 /*===========================================================================+
1314 | PROCEDURE |
1315 | Map_Location_To_Region |
1316 | |
1317 | DESCRIPTION |
1318 | This API does the main mapping process. It calls the API |
1319 | WSH_REGIONS_SEARCH_PKG.Get_Region_Info which inturn returns |
1320 | the region id. For this particuar region, the parent regions |
1321 | are also obtained and all these are inserted into the |
1322 | intersection table. |
1323 | |
1324 +===========================================================================*/
1325
1326 PROCEDURE Map_Location_To_Region (
1327 p_country IN VARCHAR2,
1328 p_country_code IN VARCHAR2,
1329 p_state IN VARCHAR2,
1330 p_city IN VARCHAR2,
1331 p_postal_code IN VARCHAR2,
1332 p_location_id IN NUMBER,
1333 p_location_source IN VARCHAR2,
1334 p_inactive_date IN DATE,
1335 p_insert_flag IN BOOLEAN DEFAULT TRUE, -- Bug 4722963
1336 x_return_status OUT NOCOPY VARCHAR2,
1337 x_sqlcode OUT NOCOPY NUMBER,
1338 x_sqlerr OUT NOCOPY VARCHAR2) IS
1339
1340
1341 l_region_info WSH_REGIONS_SEARCH_PKG.region_rec;
1342 l_region_type NUMBER := 0;
1343 l_region_id NUMBER := 0;
1344 l_region_table WSH_REGIONS_SEARCH_PKG.region_table;
1345 l_country l_region_info.country%TYPE;
1346 l_return_status VARCHAR2(10);
1347 Insert_Failed EXCEPTION;
1348 l_sqlcode NUMBER;
1349 l_sqlerr VARCHAR2(2000);
1350 l_region_type_const NUMBER := 0 ;
1351 l_parent_region VARCHAR2(1) := 'N';
1352 l_rows_before NUMBER := 0;
1353 l_rows_after NUMBER := 0;
1354 l_exists VARCHAR2(10);
1355 l_location_source VARCHAR2(4);
1356 l_status NUMBER := 0;
1357 i NUMBER := 0;
1358 j NUMBER := 0;
1359 l_log_exception BOOLEAN := FALSE;
1360
1361 --Variables: Start of fix for bug 5125837
1362 TYPE Reg_Rec_Type IS RECORD (
1363 REGION_ID WSH_REGION_LOCATIONS.REGION_ID%TYPE,
1364 REGION_TYPE WSH_REGION_LOCATIONS.REGION_TYPE%TYPE,
1365 EXCEPTION_TYPE WSH_REGION_LOCATIONS.EXCEPTION_TYPE%TYPE,
1366 PARENT_REGION_FLAG WSH_REGION_LOCATIONS.PARENT_REGION_FLAG%TYPE );
1367
1368 TYPE Reg_Tab_Type IS TABLE OF Reg_Rec_Type INDEX BY BINARY_INTEGER;
1369
1370 l_region_detail_tab Reg_Tab_Type;
1371 l_region_counter NUMBER;
1372 l_max_counter NUMBER;
1373 l_insert BOOLEAN;
1374 --Variables: End of fix for bug 5125837
1375
1376 --Commented below 2 Cursors for bug 5125837
1377 /****
1378
1379 CURSOR Check_Location_Exists(c_location_id IN NUMBER) IS
1380 select 'exists'
1381 from wsh_region_locations
1382 where location_id = c_location_id;
1383
1384
1385 CURSOR get_loc_region_count (p_location_id NUMBER) IS
1386 SELECT count(*)
1387 FROM wsh_region_locations
1388 WHERE location_id = p_location_id
1389 AND ( ( region_id IN
1390 (
1391 SELECT wrt.region_id
1392 FROM wsh_regions_tl wrt, wsh_regions wr
1393 WHERE wrt.region_id = wr.region_id
1394 AND wrt.language = USERENV('LANG'))
1395 )
1396 OR region_id IS NULL);
1397 ****/
1398
1399 l_exception_msg_count NUMBER;
1400 l_exception_msg_data VARCHAR2(15000);
1401 l_dummy_exception_id NUMBER;
1402
1403 --
1404 l_debug_on BOOLEAN;
1405 --
1406
1407 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_LOCATION_TO_REGION';
1408
1409 BEGIN
1410 --bug 7158136
1411 --x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1412 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1413
1414 j := l_location_id_tab.COUNT;
1415 --
1416 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1417 --
1418 IF l_debug_on IS NULL
1419 THEN
1420 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1421 END IF;
1422 --
1423 --
1424 -- Debug Statements
1425 --
1426 IF l_debug_on THEN
1427 WSH_DEBUG_SV.push(l_module_name);
1428 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1429 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1430 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1431 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1432 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1433 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1434 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE',P_LOCATION_SOURCE);
1435 WSH_DEBUG_SV.log(l_module_name,'P_INACTIVE_DATE',P_INACTIVE_DATE);
1436 END IF;
1437
1438 l_region_info.country_code := p_country_code;
1439 l_region_info.country := p_country;
1440
1441 IF (p_country_code IS NULL) THEN
1442 l_country := p_country;
1443 END IF;
1444
1445 --Bug 6670302 Removed the restriction on length of state and city
1446 l_region_info.state := p_state;
1447 l_region_info.city := p_city;
1448
1449 l_region_info.postal_code_from := p_postal_code;
1450 l_region_info.postal_code_to := p_postal_code;
1451
1452 IF (p_postal_code IS NOT NULL) THEN
1453 l_region_type := 3;
1454 ELSIF (p_city IS NOT NULL) THEN
1455 l_region_type := 2;
1456 ELSIF (p_state IS NOT NULL) THEN
1457 l_region_type := 1;
1458 END IF;
1459
1460 -- START affected area
1461 -- change call to use get_all_region_matches
1462
1463 IF l_debug_on THEN
1464 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_ALL_REGION_MATCHES');
1465 END IF;
1466
1467 WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(
1468 p_country => l_country,
1469 p_country_region => l_region_info.country_region,
1470 p_state => l_region_info.state,
1471 p_city => l_region_info.city,
1472 p_postal_code_from => l_region_info.postal_code_from,
1473 p_postal_code_to => l_region_info.postal_code_to,
1474 p_country_code => l_region_info.country_code,
1475 p_country_region_code => l_region_info.country_region_code,
1476 p_state_code => l_region_info.state_code,
1477 p_city_code => l_region_info.city_code,
1478 -- p_lang_code => null,
1479 p_lang_code => USERENV('LANG'),
1480 p_location_id => null,
1481 p_zone_flag => 'N',
1482 x_status => l_status,
1483 x_regions => l_region_table);
1484
1485 IF l_status = 1 THEN
1486
1487 IF l_debug_on THEN
1488 WSH_DEBUG_SV.logmsg(l_module_name,'get_all_region_matches could not find matching regions for location : ' || p_location_id);
1489 END IF;
1490
1491 END IF;
1492
1493 -- END affected area
1494
1495 --Commented for bug 5125837
1496 /*
1497 OPEN Check_Location_Exists(p_location_id);
1498 FETCH Check_Location_Exists INTO l_exists;
1499 CLOSE Check_Location_Exists;
1500 */
1501
1502 --Added for bug 5125837
1503 l_exists := null;
1504 BEGIN
1505 select region_id, region_type, exception_type, parent_region_flag
1506 BULK COLLECT INTO l_region_detail_tab
1507 from wsh_region_locations
1508 where location_id = p_location_id
1509 and ( ( region_id in
1510 ( select wrt.region_id
1511 from wsh_regions_tl wrt,
1512 wsh_regions wr
1513 where wrt.region_id = wr.region_id
1514 and wrt.language = USERENV('LANG') )
1515 ) OR region_id IS NULL )
1516 order by region_type desc;
1517
1518 IF l_region_detail_tab.COUNT > 0 THEN
1519 l_exists := 'Y';
1520 END IF;
1521
1522 EXCEPTION
1523 WHEN no_data_found THEN
1524 l_exists := null;
1525 END;
1526
1527 l_rows_before := l_region_detail_tab.COUNT;
1528 --End bug 5125837
1529
1530 SAVEPOINT WSH_LOCATION_EXISTS;
1531
1532 ---------------------------------------------------------------
1533 -- If a region is existing already, delete the records so that
1534 -- fresh mappings are inserted. Savepoint is issued before
1535 -- doing this.
1536 ---------------------------------------------------------------
1537
1538 --Added for bug 5125837
1539 l_region_counter := 1;
1540
1541 IF l_debug_on THEN
1542 --commeted during bug 7158136 as log message is not getting printed
1543 --WSH_DEBUG_SV.logmsg(l_module_name,'No. of existing mapped regions ',l_rows_before);
1544 WSH_DEBUG_SV.log(l_module_name,'No. of existing mapped regions ',l_rows_before);
1545 END IF;
1546
1547 l_location_source := p_location_source;
1548
1549 IF p_location_source = 'TCA' THEN
1550 l_location_source := 'HZ';
1551 END IF;
1552
1553 IF l_region_table.COUNT = 0 THEN
1554
1555 IF l_debug_on THEN
1556 WSH_DEBUG_SV.logmsg(l_module_name,'No matching regions were found for location : ' || p_location_id);
1557 END IF;
1558
1559 -----------------------------------------------------------
1560 -- If no region is found still insert the the location with
1561 -- region id as null and exception_flag Y
1562 -----------------------------------------------------------
1563
1564 --Added for bug 5125837
1565 IF ( l_region_detail_tab.COUNT = 1 AND
1566 l_region_detail_tab(1).region_id is null AND
1567 l_region_detail_tab(1).region_type is null AND
1568 l_region_detail_tab(1).Parent_Region_Flag = l_parent_region )
1569 THEN --{
1570
1571 IF (l_region_detail_tab(1).Exception_Type = 'N') THEN
1572
1573 UPDATE wsh_region_locations
1574 SET exception_type = 'Y',
1575 last_updated_by = fnd_global.user_id,
1576 last_update_date = sysdate,
1577 last_update_login = fnd_global.login_id
1578 where location_id = p_location_id;
1579 END IF;
1580 --
1581 ELSE
1582
1583 -- Bug 4722963 - Added p_insert_flag
1584
1585 -- If p_insert_flag is false, then insert data into pl/sql tables
1586 -- to be bulk processed later while doing delete, insert and update operations
1587 -- This is done for performance reasons since bulk operations are
1588 -- better in terms of performance
1589
1590 IF p_insert_flag THEN
1591 IF ( l_region_detail_tab.COUNT > 1 ) THEN
1592 DELETE from wsh_region_locations where location_id = p_location_id
1593 and ( ( region_id in (select wrt.region_id from wsh_regions_tl wrt,
1594 wsh_regions wr
1595 where wrt.region_id = wr.region_id
1596 and wrt.language = USERENV('LANG'))
1597 ) OR region_id IS NULL);
1598 END IF;
1599 ELSE
1600 IF ( l_region_detail_tab.COUNT > 1 ) THEN
1601 l_del_loc_id_tab(l_del_loc_id_tab.COUNT) := p_location_id;
1602 END IF;
1603 END IF;
1604
1605 -----------------------------------------------------------
1606 -- If no region is found still insert the the location with
1607 -- region id as null and exception_flag Y
1608 -----------------------------------------------------------
1609
1610 IF p_insert_flag THEN
1611 Insert_Record (
1612 p_location_id => p_location_id,
1613 p_region_id => NULL,
1614 p_region_type => NULL,
1615 p_exception => 'Y',
1616 p_location_source => l_location_source,
1617 p_parent_region => l_parent_region,
1618 x_return_status => l_return_status);
1619
1620 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1621 IF l_debug_on THEN
1622 WSH_DEBUG_SV.logmsg(l_module_name,'Insert failed for Null Region for Location : '||p_location_id);
1623 ELSE
1624 WSH_UTIL_CORE.printmsg('Insert failed for Null Region for Location : '||p_location_id);
1625 END IF;
1626 RAISE Insert_Failed;
1627 END IF;
1628 ELSE
1629
1630 l_location_id_tab(j) := p_location_id;
1631 l_region_id_tab(j) := NULL;
1632 l_region_type_tab(j) := NULL;
1633 l_exceptions_tab(j) := 'Y';
1634 l_location_source_tab(j) := l_location_source;
1635 l_parent_region_tab(j) := l_parent_region;
1636 j := j+1;
1637 END IF;
1638
1639 END IF; --}
1640
1641 l_rows_after := 1; -- Bug 3736133
1642 --End Bug 5125837
1643
1644 ELSE
1645
1646 --Added for bug 5125837
1647 --Before looping check whether wsh_region_locations table contains only
1648 --one record where region_id and region_type is NULL.
1649
1650 IF ( l_region_detail_tab.COUNT = 1 AND
1651 l_region_detail_tab(1).region_id is null AND
1652 l_region_detail_tab(1).region_type is null )
1653 THEN
1654 DELETE from wsh_region_locations where location_id = p_location_id
1655 and ( ( region_id in (select wrt.region_id from wsh_regions_tl wrt,
1656 wsh_regions wr
1657 where wrt.region_id = wr.region_id
1658 and wrt.language = USERENV('LANG'))
1659 ) OR region_id IS NULL);
1660 --
1661 IF l_debug_on THEN
1662 WSH_DEBUG_SV.log(l_module_name,'No of rows deleted : ' || sql%rowcount );
1663 END IF;
1664 --
1665 END IF;
1666
1667 -----------------------------------------------------------
1668 -- If some regions are found, then insert everything in the intersection
1669 -- table. If the region is a parent region, set the parent
1670 -- flag accordingly.
1671 -----------------------------------------------------------
1672
1673 IF l_debug_on THEN
1674 WSH_DEBUG_SV.logmsg(l_module_name,' Looping over l_region_table for inserting into the intersection table ');
1675 END IF;
1676
1677 l_rows_after := 0; -- Bug 3736133
1678
1679 i := l_region_table.FIRST;
1680 --Added for bug 5125837
1681 l_max_counter := l_region_detail_tab.COUNT;
1682 LOOP -- 3. region hierarchy
1683
1684 IF l_region_table(i).region_type >= 0 THEN
1685
1686 IF l_region_table(i).region_type <> l_region_type THEN
1687 l_parent_region := 'Y';
1688 ELSE
1689 l_parent_region := 'N';
1690 END IF;
1691
1692 l_insert := TRUE;
1693 IF ( l_region_counter <= l_max_counter ) THEN --{
1694 LOOP --{
1695 IF l_debug_on THEN
1696 WSH_DEBUG_SV.logmsg(l_module_name,'Start of Loop' );
1697 END IF;
1698
1699 IF ( l_region_detail_tab(l_region_counter).region_type = l_region_table(i).region_type) THEN --{
1700
1701 IF NOT ( l_region_detail_tab(l_region_counter).region_id = l_region_table(i).region_id AND
1702 l_region_detail_tab(l_region_counter).exception_type = 'N' AND
1703 l_region_detail_tab(l_region_counter).parent_region_flag = l_parent_region)
1704 THEN --{
1705
1706 UPDATE wsh_region_locations
1707 SET region_id = l_region_table(i).region_id,
1708 exception_type = 'N',
1709 parent_region_flag = l_parent_region,
1710 last_updated_by = fnd_global.user_id,
1711 last_update_date = sysdate,
1712 last_update_login = fnd_global.login_id
1713 WHERE location_id = p_location_id
1714 AND region_type = l_region_table(i).region_type;
1715
1716 END IF; --}
1717
1718 l_rows_after := l_rows_after + 1;
1719 l_region_counter := l_region_counter + 1;
1720 l_insert := FALSE;
1721 EXIT;
1722
1723 ELSIF (l_region_detail_tab(l_region_counter).region_type < l_region_table(i).region_type) THEN
1724 l_insert := TRUE;
1725 EXIT;
1726 ELSE
1727
1728 DELETE FROM wsh_region_locations
1729 WHERE location_id = p_location_id
1730 AND region_type = l_region_detail_tab(l_region_counter).region_type
1731 AND region_id in
1732 ( select wrt.region_id
1733 from wsh_regions_tl wrt,
1734 wsh_regions wr
1735 where wr.region_id = wrt.region_id
1736 and wrt.language = USERENV('LANG') );
1737
1738 IF l_debug_on THEN
1739 WSH_DEBUG_SV.logmsg(l_module_name,'No of Regions deleted : ' || sql%rowcount );
1740 END IF;
1741
1742 END IF; --}
1743
1744 l_region_counter := l_region_counter + 1;
1745 EXIT WHEN (l_region_counter > l_max_counter);
1746
1747 IF l_debug_on THEN
1748 WSH_DEBUG_SV.logmsg(l_module_name,'End of Loop' );
1749 END IF;
1750 END LOOP; --}
1751 END IF; --}
1752
1753 IF l_insert THEN --{
1754
1755 IF l_debug_on THEN
1756 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Insert_Record for location id : ' || p_location_id ||
1757 ' Region Id : ' || l_region_table(i).region_id ||
1758 ' Region Type : ' || l_region_table(i).region_type ||
1759 ' Parent Region : ' || l_parent_region);
1760 END IF;
1761 IF p_insert_flag THEN
1762 Insert_Record (
1763 p_location_id => p_location_id,
1764 p_region_id => l_region_table(i).region_id,
1765 p_region_type => l_region_table(i).region_type,
1766 p_exception => 'N',
1767 p_location_source => l_location_source,
1768 p_parent_region => l_parent_region,
1769 x_return_status => l_return_status);
1770
1771 IF l_debug_on THEN
1772 WSH_DEBUG_SV.logmsg(l_module_name,'After calling Insert_Record for location id :' || p_location_id);
1773 END IF;
1774
1775 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1776 IF l_debug_on THEN
1777 WSH_DEBUG_SV.logmsg(l_module_name,'Insert failed for Region '||l_region_table(i).region_id||' for Location : '||p_location_id);
1778 ELSE
1779 WSH_UTIL_CORE.printmsg('Insert failed for Region '||l_region_table(i).region_id||' for Location : '||p_location_id);
1780 END IF;
1781 RAISE Insert_Failed;
1782 END IF;
1783 ELSE
1784 l_location_id_tab(j) := p_location_id;
1785 l_region_id_tab(j) := l_region_table(i).region_id;
1786 l_region_type_tab(j) := l_region_table(i).region_type;
1787 l_exceptions_tab(j) := 'N';
1788 l_location_source_tab(j) := l_location_source;
1789 l_parent_region_tab(j) := l_parent_region;
1790 j := j+1;
1791 END IF;
1792
1793 l_rows_after := l_rows_after + 1; -- Bug 3736133
1794
1795 END IF; --}
1796 END IF;
1797
1798 EXIT WHEN i = l_region_table.LAST;
1799 i := l_region_table.NEXT(i);
1800
1801 END LOOP; -- 4. region hierarchy
1802
1803 END IF;
1804
1805 IF l_debug_on THEN
1806 WSH_DEBUG_SV.log(l_module_name,'No. of mapped regions after deletion and reinsert ', l_rows_after);
1807 END IF;
1808
1809 --------------------------------------------------------
1810 -- If the number of locations that was matching before
1811 -- is less than now or no match is found, update the exception flag
1812 -- to 'Y'
1813 --------------------------------------------------------
1814
1815 IF (l_rows_after < l_rows_before AND l_rows_before > 0) OR l_region_table.COUNT = 0 THEN
1816
1817 -- There is a possibility that exception flags are updated for mappings
1818 -- in one language, but they are visible in the UI
1819 -- from another language.
1820 -- 02/04 discussed and validated with Rohit
1821 -- the user will have to map again
1822 --Bug 4893034 Log exception for Active Locations only
1823 IF TRUNC(sysdate) <= TRUNC(NVL(p_inactive_date, sysdate)) THEN
1824 l_log_exception := TRUE;
1825 END IF;
1826
1827 IF p_insert_flag THEN
1828 UPDATE wsh_region_locations
1829 SET exception_type = 'Y',
1830 --Added for bug 5125837
1831 last_update_date = sysdate,
1832 last_updated_by = FND_GLOBAL.user_id,
1833 last_update_login = FND_GLOBAL.login_id
1834 WHERE location_id = p_location_id
1835 and region_id in (select wrt.region_id from wsh_regions_tl wrt,
1836 wsh_regions wr
1837 where wrt.region_id = wr.region_id
1838 and wrt.language = USERENV('LANG'));
1839
1840 -- Vijay 08/25: added call to put exception WSH_LOCATION_REGIONS_2_ERR
1841
1842 IF l_log_exception THEN
1843 wsh_xc_util.log_exception(
1844 p_api_version => 1.0,
1845 x_return_status => l_return_status,
1846 x_msg_count => l_exception_msg_count,
1847 x_msg_data => l_exception_msg_data,
1848 x_exception_id => l_dummy_exception_id ,
1849 p_logged_at_location_id => p_location_id,
1850 p_exception_location_id => p_location_id,
1851 p_logging_entity => 'SHIPPER',
1852 p_logging_entity_id => FND_GLOBAL.USER_ID,
1853 p_exception_name => 'WSH_LOCATION_REGIONS_2',
1854 p_message => 'WSH_LOCATION_REGIONS_2_ERR'
1855 );
1856 END IF;
1857 ELSE
1858 l_upd_loc_id_tab(l_upd_loc_id_tab.COUNT) := p_location_id;
1859 l_upd_loc_excp_tab(l_upd_loc_excp_tab.COUNT) := l_log_exception;
1860 END IF;
1861
1862 END IF;
1863
1864 --------------------------------------------------------
1865 -- If the number of regions being matched is only one set
1866 -- exception WSH_LOCATION_REGIONS_1_ERR
1867 --------------------------------------------------------
1868
1869 -- Bug 4451703
1870 -- l_dummy_exception_id has to be intialized to NULL otherwise
1871 -- it takes the same value returned by previous log_exception
1872 -- and errors out when trying to update the existing exception.
1873
1874 IF (l_rows_after = l_rows_before AND l_rows_before = 1) THEN
1875 --
1876 l_dummy_exception_id := NULL;
1877 --
1878 --Bug 4893034 Log exception for Active Locations only
1879 IF TRUNC(sysdate) <= TRUNC(NVL(p_inactive_date, sysdate)) THEN
1880 wsh_xc_util.log_exception(
1881 p_api_version => 1.0,
1882 x_return_status => l_return_status,
1883 x_msg_count => l_exception_msg_count,
1884 x_msg_data => l_exception_msg_data,
1885 x_exception_id => l_dummy_exception_id ,
1886 p_logged_at_location_id => p_location_id,
1887 p_exception_location_id => p_location_id,
1888 p_logging_entity => 'SHIPPER',
1889 p_logging_entity_id => FND_GLOBAL.USER_ID,
1890 p_exception_name => 'WSH_LOCATION_REGIONS_1',
1891 p_message => 'WSH_LOCATION_REGIONS_1_ERR'
1892 );
1893 END IF;
1894
1895 END IF;
1896
1897 x_return_status := l_return_status;
1898
1899 IF l_debug_on THEN
1900 WSH_DEBUG_SV.pop(l_module_name);
1901 END IF;
1902
1903 EXCEPTION
1904
1905 WHEN Insert_Failed THEN
1906
1907 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1908 IF l_debug_on THEN
1909
1910 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in API Insert_Record');
1911 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INSERT_FAILED');
1912 ELSE
1913 WSH_UTIL_CORE.printmsg('Failed in API Insert_Record');
1914 END IF;
1915
1916 rollback to wsh_location_exists;
1917
1918 WHEN Others THEN
1919
1920 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1921 l_sqlcode := SQLCODE;
1922 l_sqlerr := SQLERRM;
1923 IF l_debug_on THEN
1924 WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1925 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1926 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1927 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1928 ELSE
1929 WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1930 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1931 WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1932 END IF;
1933
1934
1935 rollback to wsh_location_exists;
1936
1937 END Map_Location_To_Region;
1938
1939 /*===========================================================================+
1940 | FUNCTION |
1941 | Insert_Record |
1942 | |
1943 | DESCRIPTION |
1944 | This API just inserts the record into intersection table |
1945 | |
1946 +===========================================================================*/
1947
1948 Procedure Insert_Record
1949 (
1950 p_location_id IN NUMBER,
1951 p_region_id IN NUMBER,
1952 p_region_type IN NUMBER,
1953 p_exception IN VARCHAR2,
1954 p_location_source IN VARCHAR2,
1955 p_parent_region IN VARCHAR2,
1956 x_return_status OUT NOCOPY VARCHAR2
1957 ) IS
1958
1959 l_region_id NUMBER := 0;
1960 l_sqlcode NUMBER;
1961 l_sqlerr VARCHAR2(2000);
1962
1963 BEGIN
1964
1965 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1966
1967 INSERT INTO WSH_REGION_LOCATIONS(
1968 region_id,
1969 location_id,
1970 exception_type,
1971 region_type,
1972 parent_region_flag,
1973 location_source,
1974 creation_date,
1975 created_by,
1976 last_update_date,
1977 last_updated_by,
1978 last_update_login )
1979 values (
1980 p_region_id,
1981 p_location_id,
1982 p_exception,
1983 p_region_type,
1984 p_parent_region,
1985 p_location_source,
1986 sysdate,
1987 fnd_global.user_id,
1988 sysdate,
1989 fnd_global.user_id,
1990 fnd_global.login_id
1991 );
1992
1993 EXCEPTION
1994
1995 WHEN Others THEN
1996
1997 WSH_UTIL_CORE.printmsg(' Insert into WSH_REGION_LOCATIONS failed : ' || sqlerrm);
1998 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1999
2000 End Insert_Record;
2001
2002
2003 /*===========================================================================+
2004 | FUNCTION |
2005 | Rule_Location |
2006 | |
2007 | DESCRIPTION |
2008 | This is the rule function for the following TCA events : |
2009 | # oracle.apps.ar.hz.Location.create |
2010 | # oracle.apps.ar.hz.Location.update |
2011 | This calls the Mapping_Regions_Main API to recreate the |
2012 | mapping once a location gets created or a location gets |
2013 | updated. |
2014 | |
2015 +===========================================================================*/
2016
2017 FUNCTION Rule_Location(
2018 p_subscription_guid in raw,
2019 p_event in out NOCOPY wf_event_t)
2020 RETURN VARCHAR2 IS
2021
2022 i_status varchar2(200);
2023 myList wf_parameter_list_t;
2024 pos number := 1;
2025
2026 l_return_status VARCHAR2(20);
2027 l_return_status1 VARCHAR2(20);
2028 p_location_id NUMBER;
2029 l_sqlcode NUMBER;
2030 l_sqlerr VARCHAR2(2000);
2031
2032 l_org_id NUMBER;
2033 l_user_id NUMBER;
2034 l_resp_id NUMBER;
2035 l_resp_appl_id NUMBER;
2036 l_security_group_id NUMBER;
2037
2038 l_pkg_name VARCHAR2(200);
2039 l_proc_name VARCHAR2(200);
2040 e_loc EXCEPTION;
2041
2042 --
2043 l_debug_on BOOLEAN;
2044 --
2045 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Rule_Location';
2046
2047 BEGIN
2048
2049 l_org_id := p_event.getValueForParameter('ORG_ID');
2050 l_user_id := p_event.getValueForParameter('USER_ID');
2051 l_resp_id := p_event.getValueForParameter('RESP_ID');
2052 l_resp_appl_id := p_event.getValueForParameter('RESP_APPL_ID');
2053 l_security_group_id := p_event.getValueForParameter('SECURITY_GROUP_ID');
2054
2055 fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id,l_security_group_id);
2056
2057 --
2058 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2059 --
2060 IF l_debug_on IS NULL
2061 THEN
2062 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2063 END IF;
2064 --
2065 --
2066 -- Debug Statements
2067 --
2068 IF l_debug_on THEN
2069 WSH_DEBUG_SV.push(l_module_name);
2070 WSH_DEBUG_SV.log(l_module_name,'USER_ID: ',l_user_id);
2071 WSH_DEBUG_SV.log(l_module_name,'RESP_ID : ',l_resp_id);
2072 WSH_DEBUG_SV.log(l_module_name,'RESP_APPL_ID: ',l_resp_appl_id);
2073 WSH_DEBUG_SV.log(l_module_name,'SECURITY_GROUP_ID: ',l_security_group_id);
2074 WSH_DEBUG_SV.log(l_module_name,'USERENV LANG: ',USERENV('LANG'));
2075 END IF;
2076
2077 myList := p_event.getParameterList();
2078
2079 IF (myList is null) THEN
2080 IF l_debug_on THEN
2081 WSH_DEBUG_SV.pop(l_module_name);
2082 END IF;
2083 return NULL;
2084 END IF;
2085
2086 pos := myList.LAST;
2087
2088 WHILE (pos is not null)
2089 LOOP
2090
2091 IF myList(pos).getName() = 'LOCATION_ID' THEN
2092 p_location_id := myList(pos).getValue();
2093 END IF;
2094
2095 pos := myList.PRIOR(pos);
2096
2097 END LOOP;
2098
2099
2100 WSH_LOCATIONS_PKG.Process_Locations(
2101 p_location_type => 'EXTERNAL'
2102 , p_from_location => p_location_id
2103 , p_to_location => p_location_id
2104 , p_start_date => NULL
2105 , p_end_date => NULL
2106 , x_return_status => l_return_status1
2107 , x_sqlcode => l_sqlcode
2108 , x_sqlerr => l_sqlerr );
2109
2110 IF l_debug_on THEN
2111 WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
2112 END IF;
2113
2114 IF ( l_return_status1 NOT IN
2115 (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) ) THEN
2116 l_pkg_name := 'WSH_LOCATIONS_PKG';
2117 l_proc_name := 'Process_Locations';
2118 raise e_loc;
2119 END IF;
2120
2121 WSH_MAP_LOCATION_REGION_PKG.Mapping_Regions_Main(
2122 p_location_type => 'EXTERNAL'
2123 , p_from_location => p_location_id
2124 , p_to_location => p_location_id
2125 , p_start_date => NULL
2126 , p_end_date => NULL
2127 , p_insert_flag => TRUE
2128 , x_return_status => l_return_status
2129 , x_sqlcode => l_sqlcode
2130 , x_sqlerr => l_sqlerr );
2131
2132 IF l_debug_on THEN
2133 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2134 END IF;
2135
2136 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2137 IF l_debug_on THEN
2138 WSH_DEBUG_SV.pop(l_module_name);
2139 END IF;
2140 return 'SUCCESS';
2141 ELSE
2142 l_pkg_name := 'WSH_MAP_LOCATION_REGION_PKG';
2143 l_proc_name := 'Mapping_Regions_Main';
2144 raise e_loc;
2145 /*
2146 IF l_debug_on THEN
2147 WSH_DEBUG_SV.pop(l_module_name);
2148 END IF;
2149 return 'ERROR';
2150 */
2151 END IF;
2152
2153
2154 EXCEPTION
2155
2156 WHEN e_loc THEN
2157 WF_CORE.CONTEXT(l_pkg_name,l_proc_name,
2158 p_event.getEventName( ), p_subscription_guid);
2159 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2160
2161 IF l_debug_on THEN
2162 WSH_DEBUG_SV.pop(l_module_name);
2163 END IF;
2164 return 'ERROR';
2165 WHEN Others THEN
2166 WF_CORE.CONTEXT('WSH_MAP_LOCATIONS_REGIONS', 'Rule_Location',
2167 p_event.getEventName( ), p_subscription_guid);
2168 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2169 IF l_debug_on THEN
2170 WSH_DEBUG_SV.pop(l_module_name);
2171 END IF;
2172 return 'ERROR';
2173
2174 END Rule_Location;
2175
2176 PROCEDURE Transfer_Location (
2177 p_source_type IN VARCHAR2,
2178 p_source_location_id IN NUMBER,
2179 p_transfer_location IN BOOLEAN,
2180 p_online_region_mapping IN BOOLEAN,
2181 p_caller IN VARCHAR2 DEFAULT NULL,
2182 x_loc_rec OUT NOCOPY loc_rec_type,
2183 x_return_status OUT NOCOPY VARCHAR2 ) IS
2184
2185 l_exists VARCHAR2(10);
2186 l_errbuf VARCHAR2(1000);
2187 l_retcode NUMBER;
2188 l_location_source_type VARCHAR2(20);
2189 l_map_regions VARCHAR2(1);
2190
2191 CURSOR Get_Location_Data IS
2192 SELECT wsh_location_id,
2193 source_location_id,
2194 location_source_code,
2195 location_code,
2196 ui_location_code,
2197 address1,
2198 address2,
2199 address3,
2200 address4,
2201 country,
2202 state,
2203 province,
2204 county,
2205 city,
2206 postal_code,
2207 inactive_date
2208 FROM wsh_locations
2209 WHERE source_location_id = p_source_location_id;
2210
2211
2212 CURSOR Get_Location_Data1 IS
2213 SELECT wsh_location_id,
2214 source_location_id,
2215 location_source_code,
2216 location_code,
2217 ui_location_code,
2218 address1,
2219 address2,
2220 address3,
2221 address4,
2222 country,
2223 state,
2224 province,
2225 county,
2226 city,
2227 postal_code,
2228 inactive_date
2229 FROM wsh_locations
2230 WHERE wsh_location_id = p_source_location_id;
2231
2232 l_return_status VARCHAR2(20);
2233 l_sqlcode NUMBER;
2234 l_sqlerr VARCHAR2(2000);
2235 --
2236 l_debug_on BOOLEAN;
2237 --
2238 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Transfer_Location';
2239 BEGIN
2240
2241 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2242 --
2243 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2244 --
2245 IF l_debug_on IS NULL
2246 THEN
2247 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2248 END IF;
2249 IF l_debug_on THEN
2250 WSH_DEBUG_SV.push(l_module_name);
2251 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2252 --
2253 END IF;
2254 --
2255 -- Check if the location exists. If yes, then return the
2256 -- location data
2257 --
2258
2259 IF p_source_type = 'HR' or p_source_type = 'HZ' or p_source_type = 'HR_HZ' THEN
2260
2261 OPEN Get_Location_Data;
2262 FETCH Get_Location_Data INTO
2263 x_loc_rec.wsh_location_id,
2264 x_loc_rec.source_location_id,
2265 x_loc_rec.location_source_code,
2266 x_loc_rec.location_code,
2267 x_loc_rec.ui_location_code,
2268 x_loc_rec.address1,
2269 x_loc_rec.address2,
2270 x_loc_rec.address3,
2271 x_loc_rec.address4,
2272 x_loc_rec.country,
2273 x_loc_rec.state,
2274 x_loc_rec.province,
2275 x_loc_rec.county,
2276 x_loc_rec.city,
2277 x_loc_rec.postal_code,
2278 x_loc_rec.inactive_date;
2279
2280 CLOSE Get_Location_Data;
2281
2282 ELSIF p_source_type = 'WSH' THEN
2283
2284 OPEN Get_Location_Data1;
2285 FETCH Get_Location_Data1 INTO
2286 x_loc_rec.wsh_location_id,
2287 x_loc_rec.source_location_id,
2288 x_loc_rec.location_source_code,
2289 x_loc_rec.location_code,
2290 x_loc_rec.ui_location_code,
2291 x_loc_rec.address1,
2292 x_loc_rec.address2,
2293 x_loc_rec.address3,
2294 x_loc_rec.address4,
2295 x_loc_rec.country,
2296 x_loc_rec.state,
2297 x_loc_rec.province,
2298 x_loc_rec.county,
2299 x_loc_rec.city,
2300 x_loc_rec.postal_code,
2301 x_loc_rec.inactive_date;
2302
2303 CLOSE Get_Location_Data1;
2304
2305 END IF;
2306
2307 --
2308 -- If a location is found, then return.
2309 --
2310
2311 IF (x_loc_rec.wsh_location_id IS NOT NULL) THEN
2312 IF l_debug_on THEN
2313 WSH_DEBUG_SV.pop(l_module_name);
2314 END IF;
2315 RETURN;
2316 END IF;
2317
2318 --
2319 -- If a location is not found and if p_transfer_location
2320 -- is true, then create the location record in WSH_LOCATIONS
2321 -- table.
2322 --
2323
2324 IF ((p_source_location_id IS NOT NULL) AND
2325 (p_source_type in ('HR','HZ','HR_HZ')) AND
2326 (p_transfer_location)) THEN
2327
2328 IF p_source_type = 'HR' THEN
2329 l_location_source_type := 'INTERNAL' ;
2330 ELSIF p_source_type = 'HZ' THEN
2331 l_location_source_type := 'EXTERNAL' ;
2332 ELSIF p_source_type = 'HR_HZ' THEN
2333 l_location_source_type := 'BOTH' ;
2334 END IF;
2335
2336 IF p_online_region_mapping THEN
2337 l_map_regions := 'Y';
2338 ELSE
2339 l_map_regions := 'N';
2340 END IF;
2341
2342 --
2343 IF l_debug_on THEN
2344 WSH_DEBUG_SV.log(l_module_name,'-----------------------------');
2345 WSH_DEBUG_SV.log(l_module_name,'Calling procedure Process_Locations');
2346 END IF;
2347 WSH_LOCATIONS_PKG.Process_Locations (
2348 p_location_type => l_location_source_type,
2349 p_from_location => p_source_location_id,
2350 p_to_location => p_source_location_id,
2351 p_start_date => NULL,
2352 p_end_date => NULL,
2353 p_caller => p_caller,
2354 x_return_status => l_return_status,
2355 x_sqlcode => l_sqlcode,
2356 x_sqlerr => l_sqlerr);
2357
2358 IF l_return_status NOT IN
2359 (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2360 --
2361 IF l_debug_on THEN
2362 WSH_DEBUG_SV.log(l_module_name,'Failed in Procedure Process_Locations');
2363 END IF;
2364 END IF;
2365
2366 IF l_map_regions = 'Y' AND l_return_status NOT IN
2367 (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2368
2369 IF l_debug_on THEN
2370 WSH_DEBUG_SV.log(l_module_name,'----------------------------------');
2371 WSH_DEBUG_SV.log(l_module_name,'*** Map Regions parameter is Yes ***');
2372 WSH_DEBUG_SV.log(l_module_name,'Calling procedure Mapping_Regions_Main');
2373 END IF;
2374
2375 Mapping_Regions_Main (
2376 p_location_type => l_location_source_type,
2377 p_from_location => p_source_location_id,
2378 p_to_location => p_source_location_id,
2379 p_start_date => NULL,
2380 p_end_date => NULL,
2381 p_insert_flag => TRUE,
2382 x_return_status => l_return_status,
2383 x_sqlcode => l_sqlcode,
2384 x_sqlerr => l_sqlerr);
2385
2386 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2387 IF l_debug_on THEN
2388 WSH_DEBUG_SV.log(l_module_name,'Failed in Procedure Mapping_Regions_Main');
2389 END IF;
2390 END IF;
2391 END IF;
2392
2393 /*
2394 WSH_MAP_LOCATION_REGION_PKG.Map_Locations_Child_Program (
2395 p_errbuf => l_errbuf,
2396 p_retcode => l_retcode,
2397 p_location_type => l_location_source_type,
2398 p_map_regions => l_map_regions,
2399 p_from_location => p_source_location_id,
2400 p_to_location => p_source_location_id,
2401 p_start_date => NULL,
2402 p_end_date => NULL);
2403 */
2404
2405 OPEN Get_Location_Data;
2406 FETCH Get_Location_Data INTO
2407 x_loc_rec.wsh_location_id,
2408 x_loc_rec.source_location_id,
2409 x_loc_rec.location_source_code,
2410 x_loc_rec.location_code,
2411 x_loc_rec.ui_location_code,
2412 x_loc_rec.address1,
2413 x_loc_rec.address2,
2414 x_loc_rec.address3,
2415 x_loc_rec.address4,
2416 x_loc_rec.country,
2417 x_loc_rec.state,
2418 x_loc_rec.province,
2419 x_loc_rec.county,
2420 x_loc_rec.city,
2421 x_loc_rec.postal_code,
2422 x_loc_rec.inactive_date;
2423
2424 CLOSE Get_Location_Data;
2425
2426 END IF;
2427
2428 IF x_loc_rec.wsh_location_id IS NULL THEN
2429 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2430 END IF;
2431 IF l_debug_on THEN
2432 WSH_DEBUG_SV.log(l_module_name,'Returning wsh_location_id : '||x_loc_rec.wsh_location_id);
2433 WSH_DEBUG_SV.pop(l_module_name);
2434 END IF;
2435
2436 EXCEPTION
2437
2438 WHEN OTHERS THEN
2439 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2440 IF l_debug_on THEN
2441 WSH_DEBUG_SV.pop(l_module_name);
2442 END IF;
2443
2444 END Transfer_Location;
2445
2446 /*===========================================================================+
2447 | PROCEDURE |
2448 | Location_User_Hook_API |
2449 | |
2450 | DESCRIPTION |
2451 | This API is called by Create_Location/Update_Location |
2452 | Business Process - After Process User Hook. |
2453 | This ensures that the whenever HR location is created or |
2454 | updated, corresponding changes in WSH_LOCATIONS and |
2455 | WSH_REGION_LOCATIONS happens |
2456 +===========================================================================*/
2457
2458 PROCEDURE Location_User_Hook_API(
2459 p_location_id IN NUMBER) IS
2460
2461 l_return_status VARCHAR2(15);
2462 l_return_status1 VARCHAR2(15);
2463 l_sqlcode NUMBER;
2464 l_sqlerr VARCHAR2(2000);
2465 l_location_id NUMBER;
2466 l_wsh_location_id NUMBER;
2467 l_organization_id NUMBER;
2468 map_region BOOLEAN := FALSE;
2469 terr_short_name FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%TYPE;
2470 terr_code FND_TERRITORIES_TL.TERRITORY_CODE%TYPE;
2471 l_region HR_LOCATIONS_ALL.REGION_2%TYPE;
2472 l_city HR_LOCATIONS_ALL.TOWN_OR_CITY%TYPE;
2473 l_postal_code HR_LOCATIONS_ALL.POSTAL_CODE%TYPE;
2474 l_location_source VARCHAR2(4);
2475 l_inactive_date DATE;
2476
2477 --Bug 6940375
2478 l_wsh_loc_id NUMBER;
2479 --Bug 6940375
2480
2481 CURSOR Get_Internal_Locations IS
2482 SELECT
2483 l.location_id,
2484 t.territory_short_name,
2485 t.territory_code,
2486 l.region_2 state,
2487 l.town_or_city city,
2488 l.postal_code,
2489 l.inactive_date
2490 FROM
2491 hr_locations_all l,
2492 fnd_territories_tl t
2493 WHERE
2494 t.territory_code = l.country and
2495 t.language = userenv('LANG') and
2496 l.location_id = p_location_id;
2497
2498 --Bug 6940375 Creating a new Cursor
2499
2500 CURSOR Get_Internal_Loc IS
2501 SELECT
2502 l.location_id
2503 FROM
2504 hr_locations_all l
2505 WHERE
2506 l.location_id = p_location_id;
2507
2508 --Bug 6940375
2509
2510 CURSOR check_wsh_loc(c_loc_id IN NUMBER) IS
2511 SELECT wsh_location_id
2512 FROM wsh_locations
2513 WHERE source_location_id = c_loc_id
2514 AND location_source_code = 'HR';
2515
2516 CURSOR check_company(c_loc_id IN NUMBER) IS
2517 SELECT ou.organization_id
2518 FROM hr_all_organization_units ou,
2519 mtl_parameters mp
2520 WHERE mp.organization_id = ou.organization_id
2521 AND ou.location_id = c_loc_id;
2522
2523 BEGIN
2524
2525 l_location_source := 'HR';
2526
2527 --
2528 -- Create a record in WSH_LOCATIONS
2529 --
2530 --Bug 6940375 Start (Check if the HR location is deleted,
2531 --then delete location from wsh_locations)
2532 OPEN Get_Internal_Loc;
2533 FETCH Get_Internal_Loc INTO
2534 l_wsh_loc_id;
2535 IF Get_Internal_Loc%NOTFOUND THEN
2536 CLOSE Get_Internal_Loc;
2537 DELETE FROM wsh_locations
2538 WHERE wsh_location_id = p_location_id;
2539 RETURN ;
2540 END IF;
2541 CLOSE Get_Internal_Loc;
2542
2543 -- Should update only
2544 -- if caller is 'HR' and company is not found
2545 -- should not create
2546 WSH_LOCATIONS_PKG.Process_Locations(
2547 p_location_type => 'INTERNAL'
2548 , p_from_location => p_location_id
2549 , p_to_location => p_location_id
2550 , p_start_date => NULL
2551 , p_end_date => NULL
2552 , p_caller => 'HR'
2553 , x_return_status => l_return_status1
2554 , x_sqlcode => l_sqlcode
2555 , x_sqlerr => l_sqlerr );
2556
2557 --
2558 -- Create a record in WSH_REGION_LOCATIONS
2559 --
2560
2561 OPEN Get_Internal_Locations;
2562 FETCH Get_Internal_Locations INTO
2563 l_location_id,
2564 terr_short_name,
2565 terr_code,
2566 l_region,
2567 l_city,
2568 l_postal_code,
2569 l_inactive_date;
2570
2571 CLOSE Get_Internal_Locations;
2572
2573
2574 -- if company is not found
2575 -- Should go forward only in case of update
2576 -- should not in case of create
2577 --
2578 OPEN check_wsh_loc(p_location_id);
2579 FETCH check_wsh_loc INTO l_wsh_location_id;
2580 CLOSE check_wsh_loc;
2581
2582 IF l_wsh_location_id IS NOT NULL THEN
2583 map_region := TRUE;
2584 ELSE
2585 -- check if company exists for p_location_id
2586 OPEN check_company(p_location_id);
2587 FETCH check_company INTO l_organization_id;
2588 CLOSE check_company;
2589
2590 IF l_organization_id IS NOT NULL THEN
2591 map_region := TRUE;
2592 END IF;
2593 END IF;
2594
2595 -- Calling the API Map_Location_To_Region and not Mapping_Regions_Main
2596 -- (as done in function rule_location) because the API that is being
2597 -- called during User Hook should not have any commit statements. The
2598 -- records will be commited by the HRMS API's.
2599 --
2600 IF map_region THEN
2601
2602 Map_Location_To_Region (
2603 p_country => terr_short_name,
2604 p_country_code => terr_code,
2605 p_state => l_region,
2606 p_city => l_city,
2607 p_postal_code => l_postal_code,
2608 p_location_id => l_location_id,
2609 p_location_source => l_location_source,
2610 p_inactive_date => l_inactive_date,
2611 x_return_status => l_return_status,
2612 x_sqlcode => l_sqlcode,
2613 x_sqlerr => l_sqlerr );
2614
2615 END IF;
2616
2617 END Location_User_Hook_API;
2618
2619 PROCEDURE Get_Transit_Time(p_ship_from_loc_id IN NUMBER,
2620 p_ship_to_site_id IN NUMBER,
2621 p_ship_method_code IN VARCHAR2 DEFAULT NULL,
2622 p_carrier_id IN NUMBER,
2623 p_service_code IN VARCHAR2,
2624 p_mode_code IN VARCHAR2,
2625 p_from IN VARCHAR2,
2626 x_transit_time OUT NOCOPY NUMBER,
2627 x_return_status OUT NOCOPY VARCHAR2) IS
2628
2629 l_ship_method_code VARCHAR2(30);
2630 l_prev_ship_method VARCHAR2(30);
2631 l_session_id NUMBER;
2632 l_transit_time NUMBER;
2633 Region_Loc_zone_tab WSH_UTIL_CORE.Id_Tab_Type;
2634 Loc_Region_zone_tab WSH_UTIL_CORE.Id_Tab_Type;
2635 get_From_Region_tab WSH_UTIL_CORE.Id_Tab_Type;
2636 get_To_Region_tab WSH_UTIL_CORE.Id_Tab_Type ;
2637 l_return_status VARCHAR2(55);
2638 dummy number (5);
2639 CURSOR get_session_id IS
2640 SELECT mrp_atp_schedule_temp_s.nextVal
2641 FROM dual;
2642
2643 --
2644 l_debug_on BOOLEAN;
2645 --
2646 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TRANSIT_TIME';
2647 --
2648 /*This Cursor gets transit time if transit time is defined between two locations */
2649 cursor get_Transit_Loc_Loc (p_from_loc_id NUMBER, p_to_loc_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_location_id = p_to_loc_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 location */
2657 CURSOR get_Transit_Region_Loc (p_from_region_id NUMBER, p_to_loc_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_location_id = p_to_loc_id
2662 AND ship_method = p_ship_method_code;
2663
2664 /*This Cursor gets transit time if transit time is defined between FROM Location TO region */
2665 CURSOR get_Transit_Loc_Region (p_from_loc_id NUMBER, p_to_region_id NUMBER, p_ship_method VARCHAR2) IS
2666 SELECT intransit_time
2667 FROM mtl_interorg_ship_methods
2668 WHERE from_location_id = p_from_loc_id
2669 AND to_region_id = p_to_region_id
2670 AND ship_method = p_ship_method_code;
2671
2672 /*This Cursor gets transit time if transit time is defined between FROM region TO region*/
2673 CURSOR get_Transit_Region_Region (p_from_region_id NUMBER, p_to_region_id NUMBER, p_ship_method VARCHAR2) IS
2674 SELECT intransit_time
2675 FROM mtl_interorg_ship_methods
2676 WHERE from_region_id = p_from_region_id
2677 AND to_region_id = p_to_region_id
2678 AND ship_method = p_ship_method_code;
2679
2680 CURSOR get_ship_method_cur IS
2681 SELECT ship_method_code
2682 FROM wsh_carrier_services
2683 WHERE carrier_id = p_carrier_id
2684 AND enabled_flag = 'Y'
2685 AND ( (p_mode_code IS NULL AND mode_of_transport IS NULL)
2686 OR (p_mode_code IS NOT NULL AND mode_of_transport = p_mode_code)
2687 )
2688 AND ( (p_service_code IS NULL AND service_level IS NULL)
2689 OR (p_service_code IS NOT NULL AND service_level = p_service_code)
2690 );
2691
2692 BEGIN
2693 --
2694 l_transit_time := NULL;
2695 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2696 --
2697 IF l_debug_on IS NULL
2698 THEN
2699 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2700 END IF;
2701 --
2702 --
2703 -- Debug Statements
2704 --
2705 IF l_debug_on THEN
2706 WSH_DEBUG_SV.push(l_module_name);
2707 --
2708 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOC_ID',P_SHIP_FROM_LOC_ID);
2709 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
2710 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
2711 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
2712 WSH_DEBUG_SV.log(l_module_name,'P_SERVICE_CODE',P_SERVICE_CODE);
2713 WSH_DEBUG_SV.log(l_module_name,'P_MODE_CODE',P_MODE_CODE);
2714 END IF;
2715 --
2716
2717 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
2718 x_transit_time := null;
2719
2720 IF p_ship_method_code IS NULL THEN
2721
2722 OPEN get_ship_method_cur;
2723 FETCH get_ship_method_cur INTO l_ship_method_code;
2724 CLOSE get_ship_method_cur;
2725 IF l_debug_on THEN
2726 WSH_DEBUG_SV.logmsg(l_module_name,'ship method derived is ' || l_ship_method_code);
2727 END IF;
2728 ELSE
2729 l_ship_method_code := p_ship_method_code;
2730 END IF;
2731
2732 IF l_debug_on THEN
2733 WSH_DEBUG_SV.logmsg(l_module_name, 'Check for cached transit time');
2734 END IF;
2735
2736 IF (p_from = 'OM') THEN
2737 -- check if the transit time for this ship method , origin and destination is already cached
2738 FOR k IN 1..g_ship_method_tab.COUNT LOOP
2739 IF (l_ship_method_code = g_ship_method_tab(k)) AND
2740 (p_ship_from_loc_id = g_ship_from_loc_tab(k)) AND
2741 (p_ship_to_site_id = g_ship_to_site_tab(k)) THEN
2742 --(g_transit_time_tab(k) IS NOT NULL) THEN
2743 x_transit_time := g_transit_time_tab(k);
2744
2745 IF l_debug_on THEN
2746 WSH_DEBUG_SV.logmsg(l_module_name,'found ship method transit time information cached');
2747 WSH_DEBUG_SV.pop(l_module_name);
2748 END IF;
2749
2750 RETURN;
2751 END IF;
2752 END LOOP;
2753 END IF;
2754
2755 IF l_debug_on THEN
2756 WSH_DEBUG_SV.logmsg(l_module_name, 'Transit Time Not cached',WSH_DEBUG_SV.C_PROC_LEVEL);
2757 END IF;
2758
2759 --The ship method returned by MSC_ATP_PROC.ATP_Shipping_Lead_Time could be different
2760 --from the ship method passed in. If it doesn't find the transit time, it returns
2761 --the default ship method and default ship method transit time.
2762 l_prev_ship_method := l_ship_method_code;
2763
2764
2765 --OM Callers pass the ship_to_site_id, and get the transit time from the
2766 --MSC_ATP_PROC.Shiping_Lead_Time API
2767 IF (p_from = 'OM') THEN
2768
2769 OPEN get_session_id;
2770 FETCH get_session_id INTO l_session_id;
2771 CLOSE get_session_id;
2772
2773 IF l_debug_on THEN
2774 WSH_DEBUG_SV.logmsg(l_module_name,'MSC_ATP session id = ' || l_session_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2775 END IF;
2776
2777 MSC_ATP_PROC.ATP_Shipping_Lead_Time(p_from_loc_id => p_ship_from_loc_id,
2778 p_to_customer_site_id => p_ship_to_site_id,
2779 p_session_id => l_session_id,
2780 x_ship_method => l_ship_method_code,
2781 x_intransit_time => l_transit_time,
2782 x_return_status => x_return_status);
2783
2784 IF l_debug_on THEN
2785 WSH_DEBUG_SV.logmsg(l_module_name,'MSC_ATP_PROC.ATP_Shipping_Lead_Time is ' || l_transit_time,WSH_DEBUG_SV.C_PROC_LEVEL);
2786 END IF;
2787
2788 --FTE callers pass in the ultimate_dropoff_location_id
2789 ELSE
2790
2791 --Bug 4653381 Start
2792 /* From Location, To Location */
2793 OPEN get_Transit_Loc_Loc(p_ship_from_loc_id,p_ship_to_site_id,l_ship_method_code);
2794 FETCH get_Transit_Loc_Loc INTO l_transit_time;
2795 CLOSE get_Transit_Loc_Loc;
2796 IF l_transit_time IS NOT NULL THEN
2797 IF l_debug_on THEN
2798 WSH_DEBUG_SV.log(l_module_name,'Found the transit time from Location to location: ', l_transit_time);
2799 END IF;
2800 x_transit_time := l_transit_time;
2801 RETURN;
2802 END IF;
2803
2804 /* From Region, To Location */
2805
2806
2807 WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches(p_location_id =>p_ship_from_loc_id,
2808 x_region_tab => get_From_Region_tab,
2809 p_lang_code=>USERENV('LANG'),
2810 x_return_status =>l_return_status);
2811
2812 IF get_From_Region_tab.count >0 THEN
2813 FOR i IN get_From_Region_tab.FIRST..get_From_Region_tab.LAST LOOP
2814 OPEN get_Transit_Region_Loc(get_From_Region_tab(i),p_ship_to_site_id,p_ship_method_code);
2815 FETCH get_Transit_Region_Loc into l_transit_time;
2816 CLOSE get_Transit_Region_Loc;
2817
2818 IF l_transit_time IS NOT NULL THEN
2819 IF l_debug_on THEN
2820 WSH_DEBUG_SV.log(l_module_name,'Found the transit time from Region to Location : ',l_transit_time);
2821 END IF;
2822 x_transit_time :=l_transit_time;
2823 RETURN;
2824 END IF;
2825
2826
2827 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2828 p_region_id =>get_From_Region_tab(i),
2829 x_zone_tab =>Region_Loc_zone_tab,
2830 x_return_status =>l_return_status);
2831
2832 IF Region_Loc_zone_tab.count >0 THEN
2833 FOR j in Region_Loc_zone_tab.FIRST..Region_Loc_zone_tab.LAST LOOP
2834 OPEN get_Transit_Region_Loc(Region_Loc_zone_tab(j),p_ship_to_site_id,p_ship_method_code);
2835 FETCH get_Transit_Region_Loc INTO l_transit_time;
2836 CLOSE get_Transit_Region_Loc;
2837
2838 IF l_transit_time IS NOT NULL THEN
2839 IF l_debug_on THEN
2840 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Region to Location : ',l_transit_time);
2841 END IF;
2842 x_transit_time :=l_transit_time;
2843 RETURN;
2844 END IF;
2845 END LOOP;
2846 END IF;
2847 END LOOP;
2848 END IF;
2849
2850
2851 /* From Location, To Region */
2852
2853 WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches(p_location_id =>p_ship_to_site_id,
2854 p_lang_code=>USERENV('LANG'),
2855 x_region_tab => get_To_Region_tab,
2856 x_return_status =>l_return_status);
2857
2858
2859 IF get_To_Region_tab.COUNT >0 THEN
2860 FOR i in get_To_Region_tab.FIRST..get_To_Region_tab.LAST LOOP
2861 OPEN get_Transit_Loc_Region(p_ship_from_loc_id,get_To_Region_tab(i),p_ship_method_code);
2862 FETCH get_Transit_Loc_Region INTO l_transit_time;
2863 CLOSE get_Transit_Loc_Region;
2864
2865 IF l_transit_time IS NOT NULL THEN
2866 IF l_debug_on THEN
2867 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Location to Region : ',l_transit_time);
2868 END IF;
2869 x_transit_time :=l_transit_time;
2870 RETURN;
2871 END IF;
2872
2873
2874 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2875 p_region_id =>get_To_Region_tab(i),
2876 x_zone_tab =>Loc_Region_zone_tab,
2877 x_return_status =>l_return_status);
2878
2879 IF Loc_Region_zone_tab.count >0 THEN
2880 FOR j in Loc_Region_zone_tab.FIRST..Loc_Region_zone_tab.LAST LOOP
2881 OPEN get_Transit_Loc_Region(p_ship_from_loc_id,Loc_Region_zone_tab(j),p_ship_method_code);
2882 FETCH get_Transit_Loc_Region INTO l_transit_time;
2883 CLOSE get_Transit_Loc_Region;
2884 IF l_transit_time IS NOT NULL THEN
2885 IF l_debug_on THEN
2886 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Location to Region : ',l_transit_time);
2887 END IF;
2888 x_transit_time :=l_transit_time;
2889 RETURN;
2890 END IF;
2891 END LOOP;
2892 END IF;
2893 END LOOP;
2894 END IF;
2895
2896
2897 /* From Region, To Region */
2898
2899 --1)Loop over to_region
2900
2901 IF get_To_Region_tab.COUNT >0 THEN
2902 FOR i in get_To_Region_tab.FIRST..get_To_Region_tab.LAST LOOP
2903 IF get_From_Region_tab.COUNT >0 THEN
2904 FOR j in get_From_Region_tab.FIRST..get_From_Region_tab.LAST LOOP
2905 OPEN get_Transit_Region_Region (get_From_Region_tab(j),get_To_Region_tab(i),p_ship_method_code);
2906 FETCH get_Transit_Region_Region INTO l_transit_time;
2907 CLOSE get_Transit_Region_Region;
2908
2909 IF l_transit_time IS NOT NULL THEN
2910 IF l_debug_on THEN
2911 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Region to Region : ',l_transit_time);
2912 END IF;
2913 x_transit_time :=l_transit_time;
2914 RETURN;
2915 END IF;
2916
2917 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2918 p_region_id =>get_From_Region_tab(j),
2919 x_zone_tab =>Region_Loc_zone_tab,
2920 x_return_status =>l_return_status);
2921 IF Region_Loc_zone_tab.count >0 THEN
2922 FOR m in Region_Loc_zone_tab.FIRST..Region_Loc_zone_tab.LAST LOOP
2923 OPEN get_Transit_Region_Region (Region_Loc_zone_tab(m),get_To_Region_tab(i),p_ship_method_code);
2924 FETCH get_Transit_Region_Region INTO l_transit_time;
2925 CLOSE get_Transit_Region_Region;
2926 IF l_transit_time IS NOT NULL THEN
2927 IF l_debug_on THEN
2928 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Zone to Zone : ',l_transit_time);
2929 END IF;
2930 x_transit_time :=l_transit_time;
2931 RETURN;
2932 END IF;
2933 END LOOP;
2934 END IF;
2935
2936
2937 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2938 p_region_id =>get_To_Region_tab(i),
2939 x_zone_tab =>Loc_Region_zone_tab,
2940 x_return_status =>l_return_status);
2941 IF Loc_Region_zone_tab.count >0 THEN
2942 FOR n IN Loc_Region_zone_tab.FIRST..Loc_Region_zone_tab.LAST LOOP
2943 IF Region_Loc_zone_tab.COUNT > 0 THEN
2944 FOR p IN Region_Loc_zone_tab.FIRST..Region_Loc_zone_tab.LAST LOOP
2945 OPEN get_Transit_Region_Region(Region_Loc_zone_tab(p),Loc_Region_zone_tab(n),p_ship_method_code);
2946 FETCH get_Transit_Region_Region INTO l_transit_time;
2947 CLOSE get_Transit_Region_Region;
2948 IF l_transit_time IS NOT NULL THEN
2949 IF l_debug_on THEN
2950 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Zone to Zone : ',l_transit_time);
2951 END IF;
2952 x_transit_time :=l_transit_time;
2953 RETURN;
2954 END IF;
2955 END LOOP ;
2956 END IF;
2957 END LOOP;
2958 END IF ;
2959
2960 END LOOP; --End of from_region (j)
2961 END IF;
2962 END LOOP; --End of To_region (i)
2963 END IF;
2964
2965 --2)Loop over from_region
2966
2967 IF get_From_Region_tab.COUNT >0 THEN
2968 FOR i in get_From_Region_tab.FIRST..get_From_Region_tab.LAST LOOP
2969 IF get_To_Region_tab.COUNT >0 THEN
2970 FOR j in get_To_Region_tab.FIRST..get_To_Region_tab.LAST LOOP
2971 OPEN get_Transit_Region_Region (get_From_Region_tab(i),get_To_Region_tab(j),p_ship_method_code);
2972 FETCH get_Transit_Region_Region INTO l_transit_time;
2973 CLOSE get_Transit_Region_Region;
2974
2975 IF l_transit_time IS NOT NULL THEN
2976 IF l_debug_on THEN
2977 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Region to Region : ',l_transit_time);
2978 END IF;
2979 x_transit_time :=l_transit_time;
2980 RETURN;
2981 END IF;
2982
2983 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
2984 p_region_id =>get_To_Region_tab(j),
2985 x_zone_tab =>Loc_Region_zone_tab,
2986 x_return_status =>l_return_status);
2987 IF Loc_Region_zone_tab.count >0 THEN
2988 FOR m in Loc_Region_zone_tab.FIRST..Loc_Region_zone_tab.LAST LOOP
2989 OPEN get_Transit_Region_Region (get_From_Region_tab(i),Loc_Region_zone_tab(m),p_ship_method_code);
2990 FETCH get_Transit_Region_Region INTO l_transit_time;
2991 CLOSE get_Transit_Region_Region;
2992 IF l_transit_time IS NOT NULL THEN
2993 IF l_debug_on THEN
2994 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from from Region to Zone : ',l_transit_time);
2995 END IF;
2996 x_transit_time :=l_transit_time;
2997 RETURN;
2998 END IF;
2999 END LOOP;
3000 END IF;
3001
3002
3003 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
3004 p_region_id =>get_From_Region_tab(i),
3005 x_zone_tab =>Region_Loc_zone_tab,
3006 x_return_status =>l_return_status);
3007 IF Region_Loc_zone_tab.count >0 THEN
3008 FOR n IN Region_Loc_zone_tab.FIRST..Region_Loc_zone_tab.LAST LOOP
3009 IF Loc_Region_zone_tab.COUNT >0 THEN
3010 FOR p IN Loc_Region_zone_tab.FIRST..Loc_Region_zone_tab.LAST LOOP
3011 OPEN get_Transit_Region_Region(Region_Loc_zone_tab(n),Loc_Region_zone_tab(p),p_ship_method_code);
3012 FETCH get_Transit_Region_Region INTO l_transit_time;
3013 CLOSE get_Transit_Region_Region;
3014 IF l_transit_time IS NOT NULL THEN
3015 IF l_debug_on THEN
3016 WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Zone to Zone : ',l_transit_time);
3017 END IF;
3018 x_transit_time :=l_transit_time;
3019 RETURN;
3020 END IF;
3021 END LOOP ;
3022 END IF;
3023 END LOOP;
3024 END IF ;
3025
3026 END LOOP; --End of To_region (j)
3027 END IF;
3028 END LOOP; --End of From_region (i)
3029 END IF;
3030
3031
3032 --Bug 4653381 END
3033 END IF;
3034
3035 IF (p_from = 'OM') THEN
3036 --Cache the transit times
3037 IF (l_ship_method_code IS NOT NULL) THEN
3038 g_ship_method_tab(g_ship_method_tab.COUNT + 1) := l_ship_method_code;
3039 g_ship_from_loc_tab(g_ship_from_loc_tab.COUNT + 1) := p_ship_from_loc_id;
3040 g_ship_to_site_tab(g_ship_to_site_tab.COUNT + 1) := p_ship_to_site_id;
3041 g_transit_time_tab(g_transit_time_tab.COUNT + 1) := l_transit_time;
3042 END IF;
3043
3044 -- Bug 3357380
3045 -- If the ship_method is changed by ATP API, it means that Transit Time
3046 -- for the original ship method is not defined.
3047 IF (l_prev_ship_method <> l_ship_method_code) THEN
3048 g_ship_method_tab(g_ship_method_tab.COUNT + 1) := l_prev_ship_method;
3049 g_ship_from_loc_tab(g_ship_from_loc_tab.COUNT + 1) := p_ship_from_loc_id;
3050 g_ship_to_site_tab(g_ship_to_site_tab.COUNT + 1) := p_ship_to_site_id;
3051 g_transit_time_tab(g_transit_time_tab.COUNT + 1) := NULL;
3052 l_transit_time := NULL;
3053 END IF;
3054 END IF;
3055
3056 IF l_debug_on THEN
3057 WSH_DEBUG_SV.log(l_module_name,'Transit Time: ', l_transit_time);
3058 END IF;
3059
3060 x_transit_time := l_transit_time;
3061
3062 --
3063 -- Debug Statements
3064 --
3065 IF l_debug_on THEN
3066 WSH_DEBUG_SV.pop(l_module_name);
3067 END IF;
3068 --
3069 EXCEPTION
3070 WHEN OTHERS THEN
3071 IF l_debug_on THEN
3072 WSH_DEBUG_SV.log(l_module_name,'Exception occurred in WSH_MAP_LOCATION_REGION_PKG');
3073 WSH_DEBUG_SV.log(l_module_name,'SQLCODE: ',sqlcode);
3074 WSH_DEBUG_SV.log(l_module_name,'SQLERRM: ',SUBSTR(SQLERRM,1,200));
3075 WSH_DEBUG_SV.pop(l_module_name);
3076 END IF;
3077
3078
3079 END Get_Transit_Time;
3080
3081 --==============================================================================
3082 -- PROCEDURE : PREDEL_LOC_VALIDATION Added for bug Bug 6940375
3083 --
3084 -- PARAMETERS : p_location_id Input location id
3085 -- DESCRIPTION : This procedure checks if an Internal location is eligible
3086 -- for deletion. Shipping raises error if the location exists
3087 -- in shipping tables.
3088 --===============================================================================
3089
3090 PROCEDURE PREDEL_LOC_VALIDATION (p_location_id number)
3091 IS
3092 --
3093 v_delete_permitted varchar2(1) := NULL;
3094 l_msg varchar2(30) := 'WSH_LOC_RECORD_EXISTS';
3095 l_token varchar2(30);
3096 WSH_LOC_EXISTS EXCEPTION;
3097 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3098 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PREDEL_LOC_VALIDATION';
3099 BEGIN
3100
3101 IF l_debug_on THEN
3102 wsh_debug_sv.push(l_module_name);
3103 wsh_debug_sv.LOG(l_module_name, 'p_location_id', p_location_id);
3104 END IF;
3105 BEGIN
3106 l_token := 'WSH_DELIVERY_DETAILS';
3107 IF l_debug_on THEN
3108 wsh_debug_sv.LOG(l_module_name, 'Validating records in table : ', l_token);
3109 END IF;
3110 SELECT 'N'
3111 INTO v_delete_permitted
3112 FROM WSH_DELIVERY_DETAILS
3113 WHERE SHIP_FROM_LOCATION_ID = P_LOCATION_ID
3114 AND ROWNUM =1;
3115
3116 IF v_delete_permitted IS NOT NULL THEN
3117 RAISE WSH_LOC_EXISTS ;
3118 END IF;
3119
3120 EXCEPTION
3121 WHEN NO_DATA_FOUND THEN
3122 IF l_debug_on THEN
3123 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3124 END IF;
3125 END ;
3126
3127 BEGIN
3128 l_token := 'WSH_CALENDAR_ASSIGNMENTS';
3129 IF l_debug_on THEN
3130 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3131 END IF;
3132 v_delete_permitted := NULL;
3133 SELECT 'N'
3134 INTO v_delete_permitted
3135 FROM WSH_CALENDAR_ASSIGNMENTS WCA, WSH_LOCATIONS WSH
3136 WHERE WCA.LOCATION_ID = P_LOCATION_ID
3137 AND WSH.WSH_LOCATION_ID = WCA.LOCATION_ID
3138 AND WSH.LOCATION_SOURCE_CODE = 'HR'
3139 AND ROWNUM = 1;
3140
3141 IF v_delete_permitted IS NOT NULL THEN
3142 RAISE WSH_LOC_EXISTS ;
3143 END IF;
3144
3145 EXCEPTION
3146 WHEN NO_DATA_FOUND THEN
3147 IF l_debug_on THEN
3148 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3149 END IF;
3150 END ;
3151
3152
3153 BEGIN
3154 l_token := 'WSH_DOC_SEQUENCE_CATEGORIES';
3155 IF l_debug_on THEN
3156 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3157 END IF;
3158 v_delete_permitted := NULL;
3159 SELECT 'N'
3160 INTO v_delete_permitted
3161 FROM WSH_DOC_SEQUENCE_CATEGORIES WDO, WSH_LOCATIONS WSH
3162 WHERE WDO.LOCATION_ID = P_LOCATION_ID
3163 AND WSH.WSH_LOCATION_ID = WDO.LOCATION_ID
3164 AND WSH.LOCATION_SOURCE_CODE = 'HR'
3165 AND ROWNUM = 1;
3166
3167 IF v_delete_permitted IS NOT NULL THEN
3168 RAISE WSH_LOC_EXISTS ;
3169 END IF;
3170
3171 EXCEPTION
3172 WHEN NO_DATA_FOUND THEN
3173 IF l_debug_on THEN
3174 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3175 END IF;
3176 END ;
3177
3178
3179 BEGIN
3180 l_token := 'WSH_PICKING_RULES';
3181 IF l_debug_on THEN
3182 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3183 END IF;
3184 v_delete_permitted := NULL;
3185 SELECT 'N'
3186 INTO v_delete_permitted
3187 FROM WSH_PICKING_RULES
3188 WHERE SHIP_FROM_LOCATION_ID = P_LOCATION_ID
3189 AND ROWNUM = 1;
3190
3191 IF v_delete_permitted IS NOT NULL THEN
3192 RAISE WSH_LOC_EXISTS ;
3193 END IF;
3194
3195 EXCEPTION
3196 WHEN NO_DATA_FOUND THEN
3197 IF l_debug_on THEN
3198 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3199 END IF;
3200 END ;
3201
3202
3203 BEGIN
3204 l_token := 'WSH_REGION_LOCATIONS';
3205 IF l_debug_on THEN
3206 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3207 END IF;
3208 v_delete_permitted := NULL;
3209 SELECT 'N'
3210 INTO v_delete_permitted
3211 FROM WSH_REGION_LOCATIONS
3212 WHERE LOCATION_ID = P_LOCATION_ID
3213 AND REGION_ID IS NOT NULL
3214 AND ROWNUM = 1 ;
3215
3216 IF v_delete_permitted IS NOT NULL THEN
3217 RAISE WSH_LOC_EXISTS ;
3218 END IF;
3219
3220 EXCEPTION
3221 WHEN NO_DATA_FOUND THEN
3222 IF l_debug_on THEN
3223 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3224 END IF;
3225 END ;
3226
3227 BEGIN
3228 l_token := 'WSH_SHIPPING_PARAMETERS';
3229 IF l_debug_on THEN
3230 wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3231 END IF;
3232 v_delete_permitted := NULL;
3233 SELECT 'N'
3234 INTO v_delete_permitted
3235 FROM WSH_SHIPPING_PARAMETERS
3236 WHERE LOCATION_ID = P_LOCATION_ID
3237 AND ROWNUM = 1 ;
3238
3239 IF v_delete_permitted IS NOT NULL THEN
3240 RAISE WSH_LOC_EXISTS ;
3241 END IF;
3242
3243 EXCEPTION
3244 WHEN NO_DATA_FOUND THEN
3245 IF l_debug_on THEN
3246 wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3247 END IF;
3248 END ;
3249
3250 IF l_debug_on THEN
3251 wsh_debug_sv.LOG(l_module_name, 'No records exists in shipping tables for location id : ', p_location_id);
3252 WSH_DEBUG_SV.pop(l_module_name);
3253 END IF;
3254
3255
3256 EXCEPTION
3257 WHEN WSH_LOC_EXISTS THEN
3258 IF l_debug_on THEN
3259 wsh_debug_sv.LOG(l_module_name, 'Records found in table : ', l_token);
3260 WSH_DEBUG_SV.pop(l_module_name);
3261 END IF;
3262 FND_MESSAGE.SET_NAME('WSH','WSH_LOC_RECORD_EXISTS');
3263 FND_MESSAGE.SET_TOKEN('TABLE_NAME', l_token);
3264 APP_EXCEPTION.RAISE_EXCEPTION;
3265
3266 END PREDEL_LOC_VALIDATION;
3267
3268
3269 END WSH_MAP_LOCATION_REGION_PKG;
3270
[Home] [Help]