[Home] [Help]
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);
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);
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);
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);
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);
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;
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:
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';
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';
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);
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.
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
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
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
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:
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(
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);
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:
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:
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 (
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,
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';
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:
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
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
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;
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:
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);
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);
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);
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);
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);
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;
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:
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';
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:
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,
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
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:
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
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
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:
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);
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:
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
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:
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:
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:
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);
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:
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:
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;
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
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
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:
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);
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;
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:
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
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
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
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:
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);
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);
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'));
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;
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:
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:
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:
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;
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);
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;
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
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);
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);
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);
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;
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:
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;
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:
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;
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:
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
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 (
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),
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:
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));
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);
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);
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
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 (
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),
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:
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));
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);
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);
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
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),
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),
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:
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));
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);
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);
1261: commit;
1262: END IF;
1263:
1264: IF l_debug_on THEN
1265: WSH_DEBUG_SV.pop(l_module_name);
1266: END IF;
1267:
1268: EXCEPTION
1269:
1269:
1270: WHEN No_Data_Found THEN
1271:
1272: IF l_debug_on THEN
1273: WSH_DEBUG_SV.logmsg(l_module_name,'No records found for the entered parameters');
1274: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1275: ELSE
1276: WSH_UTIL_CORE.printmsg('No records found for the entered parameters');
1277: END IF;
1270: WHEN No_Data_Found THEN
1271:
1272: IF l_debug_on THEN
1273: WSH_DEBUG_SV.logmsg(l_module_name,'No records found for the entered parameters');
1274: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1275: ELSE
1276: WSH_UTIL_CORE.printmsg('No records found for the entered parameters');
1277: END IF;
1278:
1281: x_sqlcode := SQLCODE;
1282: x_sqlerr := SQLERRM;
1283:
1284: IF l_debug_on THEN
1285: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Mapping_Regions_Main ');
1286: WSH_DEBUG_SV.logmsg(l_module_name,x_sqlcode);
1287: WSH_DEBUG_SV.logmsg(l_module_name,x_sqlerr);
1288: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1289: ELSE
1282: x_sqlerr := SQLERRM;
1283:
1284: IF l_debug_on THEN
1285: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Mapping_Regions_Main ');
1286: WSH_DEBUG_SV.logmsg(l_module_name,x_sqlcode);
1287: WSH_DEBUG_SV.logmsg(l_module_name,x_sqlerr);
1288: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1289: ELSE
1290: WSH_UTIL_CORE.printmsg('When Others of Procedure Mapping_Regions_Main ');
1283:
1284: IF l_debug_on THEN
1285: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Mapping_Regions_Main ');
1286: WSH_DEBUG_SV.logmsg(l_module_name,x_sqlcode);
1287: WSH_DEBUG_SV.logmsg(l_module_name,x_sqlerr);
1288: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1289: ELSE
1290: WSH_UTIL_CORE.printmsg('When Others of Procedure Mapping_Regions_Main ');
1291: WSH_UTIL_CORE.printmsg(x_sqlcode);
1284: IF l_debug_on THEN
1285: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Mapping_Regions_Main ');
1286: WSH_DEBUG_SV.logmsg(l_module_name,x_sqlcode);
1287: WSH_DEBUG_SV.logmsg(l_module_name,x_sqlerr);
1288: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1289: ELSE
1290: WSH_UTIL_CORE.printmsg('When Others of Procedure Mapping_Regions_Main ');
1291: WSH_UTIL_CORE.printmsg(x_sqlcode);
1292: WSH_UTIL_CORE.printmsg(x_sqlerr);
1400: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1401: --
1402: IF l_debug_on IS NULL
1403: THEN
1404: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1405: END IF;
1406: --
1407: --
1408: -- Debug Statements
1407: --
1408: -- Debug Statements
1409: --
1410: IF l_debug_on THEN
1411: WSH_DEBUG_SV.push(l_module_name);
1412: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1413: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1414: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1415: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1408: -- Debug Statements
1409: --
1410: IF l_debug_on THEN
1411: WSH_DEBUG_SV.push(l_module_name);
1412: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1413: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1414: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1415: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1416: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1409: --
1410: IF l_debug_on THEN
1411: WSH_DEBUG_SV.push(l_module_name);
1412: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1413: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1414: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1415: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1416: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1417: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1410: IF l_debug_on THEN
1411: WSH_DEBUG_SV.push(l_module_name);
1412: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1413: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1414: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1415: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1416: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1417: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1418: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE',P_LOCATION_SOURCE);
1411: WSH_DEBUG_SV.push(l_module_name);
1412: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1413: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1414: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1415: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1416: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1417: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1418: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE',P_LOCATION_SOURCE);
1419: WSH_DEBUG_SV.log(l_module_name,'P_INACTIVE_DATE',P_INACTIVE_DATE);
1412: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1413: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1414: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1415: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1416: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1417: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1418: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE',P_LOCATION_SOURCE);
1419: WSH_DEBUG_SV.log(l_module_name,'P_INACTIVE_DATE',P_INACTIVE_DATE);
1420: END IF;
1413: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1414: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1415: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1416: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1417: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1418: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE',P_LOCATION_SOURCE);
1419: WSH_DEBUG_SV.log(l_module_name,'P_INACTIVE_DATE',P_INACTIVE_DATE);
1420: END IF;
1421:
1414: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1415: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1416: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1417: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1418: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE',P_LOCATION_SOURCE);
1419: WSH_DEBUG_SV.log(l_module_name,'P_INACTIVE_DATE',P_INACTIVE_DATE);
1420: END IF;
1421:
1422: l_region_info.country_code := p_country_code;
1415: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1416: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1417: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1418: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE',P_LOCATION_SOURCE);
1419: WSH_DEBUG_SV.log(l_module_name,'P_INACTIVE_DATE',P_INACTIVE_DATE);
1420: END IF;
1421:
1422: l_region_info.country_code := p_country_code;
1423: l_region_info.country := p_country;
1444: -- START affected area
1445: -- change call to use get_all_region_matches
1446:
1447: IF l_debug_on THEN
1448: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_ALL_REGION_MATCHES');
1449: END IF;
1450:
1451: WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(
1452: p_country => l_country,
1468:
1469: IF l_status = 1 THEN
1470:
1471: IF l_debug_on THEN
1472: WSH_DEBUG_SV.logmsg(l_module_name,'get_all_region_matches could not find matching regions for location : ' || p_location_id);
1473: END IF;
1474:
1475: END IF;
1476:
1523: l_region_counter := 1;
1524:
1525: IF l_debug_on THEN
1526: --commeted during bug 7158136 as log message is not getting printed
1527: --WSH_DEBUG_SV.logmsg(l_module_name,'No. of existing mapped regions ',l_rows_before);
1528: WSH_DEBUG_SV.log(l_module_name,'No. of existing mapped regions ',l_rows_before);
1529: END IF;
1530:
1531: l_location_source := p_location_source;
1524:
1525: IF l_debug_on THEN
1526: --commeted during bug 7158136 as log message is not getting printed
1527: --WSH_DEBUG_SV.logmsg(l_module_name,'No. of existing mapped regions ',l_rows_before);
1528: WSH_DEBUG_SV.log(l_module_name,'No. of existing mapped regions ',l_rows_before);
1529: END IF;
1530:
1531: l_location_source := p_location_source;
1532:
1536:
1537: IF l_region_table.COUNT = 0 THEN
1538:
1539: IF l_debug_on THEN
1540: WSH_DEBUG_SV.logmsg(l_module_name,'No matching regions were found for location : ' || p_location_id);
1541: END IF;
1542:
1543: -----------------------------------------------------------
1544: -- If no region is found still insert the the location with
1602: x_return_status => l_return_status);
1603:
1604: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1605: IF l_debug_on THEN
1606: WSH_DEBUG_SV.logmsg(l_module_name,'Insert failed for Null Region for Location : '||p_location_id);
1607: ELSE
1608: WSH_UTIL_CORE.printmsg('Insert failed for Null Region for Location : '||p_location_id);
1609: END IF;
1610: RAISE Insert_Failed;
1642: and wrt.language = USERENV('LANG'))
1643: ) OR region_id IS NULL);
1644: --
1645: IF l_debug_on THEN
1646: WSH_DEBUG_SV.log(l_module_name,'No of rows deleted : ' || sql%rowcount );
1647: END IF;
1648: --
1649: END IF;
1650:
1654: -- flag accordingly.
1655: -----------------------------------------------------------
1656:
1657: IF l_debug_on THEN
1658: WSH_DEBUG_SV.logmsg(l_module_name,' Looping over l_region_table for inserting into the intersection table ');
1659: END IF;
1660:
1661: l_rows_after := 0; -- Bug 3736133
1662:
1676: l_insert := TRUE;
1677: IF ( l_region_counter <= l_max_counter ) THEN --{
1678: LOOP --{
1679: IF l_debug_on THEN
1680: WSH_DEBUG_SV.logmsg(l_module_name,'Start of Loop' );
1681: END IF;
1682:
1683: IF ( l_region_detail_tab(l_region_counter).region_type = l_region_table(i).region_type) THEN --{
1684:
1719: where wr.region_id = wrt.region_id
1720: and wrt.language = USERENV('LANG') );
1721:
1722: IF l_debug_on THEN
1723: WSH_DEBUG_SV.logmsg(l_module_name,'No of Regions deleted : ' || sql%rowcount );
1724: END IF;
1725:
1726: END IF; --}
1727:
1728: l_region_counter := l_region_counter + 1;
1729: EXIT WHEN (l_region_counter > l_max_counter);
1730:
1731: IF l_debug_on THEN
1732: WSH_DEBUG_SV.logmsg(l_module_name,'End of Loop' );
1733: END IF;
1734: END LOOP; --}
1735: END IF; --}
1736:
1736:
1737: IF l_insert THEN --{
1738:
1739: IF l_debug_on THEN
1740: WSH_DEBUG_SV.logmsg(l_module_name,'Calling Insert_Record for location id : ' || p_location_id ||
1741: ' Region Id : ' || l_region_table(i).region_id ||
1742: ' Region Type : ' || l_region_table(i).region_type ||
1743: ' Parent Region : ' || l_parent_region);
1744: END IF;
1752: p_parent_region => l_parent_region,
1753: x_return_status => l_return_status);
1754:
1755: IF l_debug_on THEN
1756: WSH_DEBUG_SV.logmsg(l_module_name,'After calling Insert_Record for location id :' || p_location_id);
1757: END IF;
1758:
1759: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1760: IF l_debug_on THEN
1757: END IF;
1758:
1759: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1760: IF l_debug_on THEN
1761: WSH_DEBUG_SV.logmsg(l_module_name,'Insert failed for Region '||l_region_table(i).region_id||' for Location : '||p_location_id);
1762: ELSE
1763: WSH_UTIL_CORE.printmsg('Insert failed for Region '||l_region_table(i).region_id||' for Location : '||p_location_id);
1764: END IF;
1765: RAISE Insert_Failed;
1786:
1787: END IF;
1788:
1789: IF l_debug_on THEN
1790: WSH_DEBUG_SV.log(l_module_name,'No. of mapped regions after deletion and reinsert ', l_rows_after);
1791: END IF;
1792:
1793: --------------------------------------------------------
1794: -- If the number of locations that was matching before
1880:
1881: x_return_status := l_return_status;
1882:
1883: IF l_debug_on THEN
1884: WSH_DEBUG_SV.pop(l_module_name);
1885: END IF;
1886:
1887: EXCEPTION
1888:
1890:
1891: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1892: IF l_debug_on THEN
1893:
1894: WSH_DEBUG_SV.logmsg(l_module_name,'Failed in API Insert_Record');
1895: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INSERT_FAILED');
1896: ELSE
1897: WSH_UTIL_CORE.printmsg('Failed in API Insert_Record');
1898: END IF;
1891: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1892: IF l_debug_on THEN
1893:
1894: WSH_DEBUG_SV.logmsg(l_module_name,'Failed in API Insert_Record');
1895: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INSERT_FAILED');
1896: ELSE
1897: WSH_UTIL_CORE.printmsg('Failed in API Insert_Record');
1898: END IF;
1899:
1904: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1905: l_sqlcode := SQLCODE;
1906: l_sqlerr := SQLERRM;
1907: IF l_debug_on THEN
1908: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1909: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1910: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1911: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1912: ELSE
1905: l_sqlcode := SQLCODE;
1906: l_sqlerr := SQLERRM;
1907: IF l_debug_on THEN
1908: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1909: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1910: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1911: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1912: ELSE
1913: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1906: l_sqlerr := SQLERRM;
1907: IF l_debug_on THEN
1908: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1909: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1910: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1911: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1912: ELSE
1913: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1914: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1907: IF l_debug_on THEN
1908: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1909: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1910: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1911: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1912: ELSE
1913: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1914: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1915: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1909: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1910: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1911: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1912: ELSE
1913: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1914: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1915: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1916: END IF;
1917:
1910: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1911: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1912: ELSE
1913: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1914: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1915: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1916: END IF;
1917:
1918:
1911: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1912: ELSE
1913: WSH_DEBUG_SV.logmsg(l_module_name,'When Others of Procedure Map_Location_To_Region for location : '||p_location_id);
1914: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlcode);
1915: WSH_DEBUG_SV.logmsg(l_module_name,l_sqlerr);
1916: END IF;
1917:
1918:
1919: rollback to wsh_location_exists;
2042: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2043: --
2044: IF l_debug_on IS NULL
2045: THEN
2046: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2047: END IF;
2048: --
2049: --
2050: -- Debug Statements
2049: --
2050: -- Debug Statements
2051: --
2052: IF l_debug_on THEN
2053: WSH_DEBUG_SV.push(l_module_name);
2054: WSH_DEBUG_SV.log(l_module_name,'USER_ID: ',l_user_id);
2055: WSH_DEBUG_SV.log(l_module_name,'RESP_ID : ',l_resp_id);
2056: WSH_DEBUG_SV.log(l_module_name,'RESP_APPL_ID: ',l_resp_appl_id);
2057: WSH_DEBUG_SV.log(l_module_name,'SECURITY_GROUP_ID: ',l_security_group_id);
2050: -- Debug Statements
2051: --
2052: IF l_debug_on THEN
2053: WSH_DEBUG_SV.push(l_module_name);
2054: WSH_DEBUG_SV.log(l_module_name,'USER_ID: ',l_user_id);
2055: WSH_DEBUG_SV.log(l_module_name,'RESP_ID : ',l_resp_id);
2056: WSH_DEBUG_SV.log(l_module_name,'RESP_APPL_ID: ',l_resp_appl_id);
2057: WSH_DEBUG_SV.log(l_module_name,'SECURITY_GROUP_ID: ',l_security_group_id);
2058: WSH_DEBUG_SV.log(l_module_name,'USERENV LANG: ',USERENV('LANG'));
2051: --
2052: IF l_debug_on THEN
2053: WSH_DEBUG_SV.push(l_module_name);
2054: WSH_DEBUG_SV.log(l_module_name,'USER_ID: ',l_user_id);
2055: WSH_DEBUG_SV.log(l_module_name,'RESP_ID : ',l_resp_id);
2056: WSH_DEBUG_SV.log(l_module_name,'RESP_APPL_ID: ',l_resp_appl_id);
2057: WSH_DEBUG_SV.log(l_module_name,'SECURITY_GROUP_ID: ',l_security_group_id);
2058: WSH_DEBUG_SV.log(l_module_name,'USERENV LANG: ',USERENV('LANG'));
2059: END IF;
2052: IF l_debug_on THEN
2053: WSH_DEBUG_SV.push(l_module_name);
2054: WSH_DEBUG_SV.log(l_module_name,'USER_ID: ',l_user_id);
2055: WSH_DEBUG_SV.log(l_module_name,'RESP_ID : ',l_resp_id);
2056: WSH_DEBUG_SV.log(l_module_name,'RESP_APPL_ID: ',l_resp_appl_id);
2057: WSH_DEBUG_SV.log(l_module_name,'SECURITY_GROUP_ID: ',l_security_group_id);
2058: WSH_DEBUG_SV.log(l_module_name,'USERENV LANG: ',USERENV('LANG'));
2059: END IF;
2060:
2053: WSH_DEBUG_SV.push(l_module_name);
2054: WSH_DEBUG_SV.log(l_module_name,'USER_ID: ',l_user_id);
2055: WSH_DEBUG_SV.log(l_module_name,'RESP_ID : ',l_resp_id);
2056: WSH_DEBUG_SV.log(l_module_name,'RESP_APPL_ID: ',l_resp_appl_id);
2057: WSH_DEBUG_SV.log(l_module_name,'SECURITY_GROUP_ID: ',l_security_group_id);
2058: WSH_DEBUG_SV.log(l_module_name,'USERENV LANG: ',USERENV('LANG'));
2059: END IF;
2060:
2061: myList := p_event.getParameterList();
2054: WSH_DEBUG_SV.log(l_module_name,'USER_ID: ',l_user_id);
2055: WSH_DEBUG_SV.log(l_module_name,'RESP_ID : ',l_resp_id);
2056: WSH_DEBUG_SV.log(l_module_name,'RESP_APPL_ID: ',l_resp_appl_id);
2057: WSH_DEBUG_SV.log(l_module_name,'SECURITY_GROUP_ID: ',l_security_group_id);
2058: WSH_DEBUG_SV.log(l_module_name,'USERENV LANG: ',USERENV('LANG'));
2059: END IF;
2060:
2061: myList := p_event.getParameterList();
2062:
2061: myList := p_event.getParameterList();
2062:
2063: IF (myList is null) THEN
2064: IF l_debug_on THEN
2065: WSH_DEBUG_SV.pop(l_module_name);
2066: END IF;
2067: return NULL;
2068: END IF;
2069:
2091: , x_sqlcode => l_sqlcode
2092: , x_sqlerr => l_sqlerr );
2093:
2094: IF l_debug_on THEN
2095: WSH_DEBUG_SV.log(l_module_name,'l_return_status1',l_return_status1);
2096: END IF;
2097:
2098: IF ( l_return_status1 NOT IN
2099: (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) ) THEN
2113: , x_sqlcode => l_sqlcode
2114: , x_sqlerr => l_sqlerr );
2115:
2116: IF l_debug_on THEN
2117: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2118: END IF;
2119:
2120: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2121: IF l_debug_on THEN
2118: END IF;
2119:
2120: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2121: IF l_debug_on THEN
2122: WSH_DEBUG_SV.pop(l_module_name);
2123: END IF;
2124: return 'SUCCESS';
2125: ELSE
2126: l_pkg_name := 'WSH_MAP_LOCATION_REGION_PKG';
2127: l_proc_name := 'Mapping_Regions_Main';
2128: raise e_loc;
2129: /*
2130: IF l_debug_on THEN
2131: WSH_DEBUG_SV.pop(l_module_name);
2132: END IF;
2133: return 'ERROR';
2134: */
2135: END IF;
2142: p_event.getEventName( ), p_subscription_guid);
2143: WF_EVENT.setErrorInfo(p_event, 'ERROR');
2144:
2145: IF l_debug_on THEN
2146: WSH_DEBUG_SV.pop(l_module_name);
2147: END IF;
2148: return 'ERROR';
2149: WHEN Others THEN
2150: WF_CORE.CONTEXT('WSH_MAP_LOCATIONS_REGIONS', 'Rule_Location',
2150: WF_CORE.CONTEXT('WSH_MAP_LOCATIONS_REGIONS', 'Rule_Location',
2151: p_event.getEventName( ), p_subscription_guid);
2152: WF_EVENT.setErrorInfo(p_event, 'ERROR');
2153: IF l_debug_on THEN
2154: WSH_DEBUG_SV.pop(l_module_name);
2155: END IF;
2156: return 'ERROR';
2157:
2158: END Rule_Location;
2227: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2228: --
2229: IF l_debug_on IS NULL
2230: THEN
2231: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2232: END IF;
2233: IF l_debug_on THEN
2234: WSH_DEBUG_SV.push(l_module_name);
2235: WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2230: THEN
2231: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2232: END IF;
2233: IF l_debug_on THEN
2234: WSH_DEBUG_SV.push(l_module_name);
2235: WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2236: --
2237: END IF;
2238: --
2231: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2232: END IF;
2233: IF l_debug_on THEN
2234: WSH_DEBUG_SV.push(l_module_name);
2235: WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2236: --
2237: END IF;
2238: --
2239: -- Check if the location exists. If yes, then return the
2293: --
2294:
2295: IF (x_loc_rec.wsh_location_id IS NOT NULL) THEN
2296: IF l_debug_on THEN
2297: WSH_DEBUG_SV.pop(l_module_name);
2298: END IF;
2299: RETURN;
2300: END IF;
2301:
2324: END IF;
2325:
2326: --
2327: IF l_debug_on THEN
2328: WSH_DEBUG_SV.log(l_module_name,'-----------------------------');
2329: WSH_DEBUG_SV.log(l_module_name,'Calling procedure Process_Locations');
2330: END IF;
2331: WSH_LOCATIONS_PKG.Process_Locations (
2332: p_location_type => l_location_source_type,
2325:
2326: --
2327: IF l_debug_on THEN
2328: WSH_DEBUG_SV.log(l_module_name,'-----------------------------');
2329: WSH_DEBUG_SV.log(l_module_name,'Calling procedure Process_Locations');
2330: END IF;
2331: WSH_LOCATIONS_PKG.Process_Locations (
2332: p_location_type => l_location_source_type,
2333: p_from_location => p_source_location_id,
2342: IF l_return_status NOT IN
2343: (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2344: --
2345: IF l_debug_on THEN
2346: WSH_DEBUG_SV.log(l_module_name,'Failed in Procedure Process_Locations');
2347: END IF;
2348: END IF;
2349:
2350: IF l_map_regions = 'Y' AND l_return_status NOT IN
2350: IF l_map_regions = 'Y' AND l_return_status NOT IN
2351: (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2352:
2353: IF l_debug_on THEN
2354: WSH_DEBUG_SV.log(l_module_name,'----------------------------------');
2355: WSH_DEBUG_SV.log(l_module_name,'*** Map Regions parameter is Yes ***');
2356: WSH_DEBUG_SV.log(l_module_name,'Calling procedure Mapping_Regions_Main');
2357: END IF;
2358:
2351: (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2352:
2353: IF l_debug_on THEN
2354: WSH_DEBUG_SV.log(l_module_name,'----------------------------------');
2355: WSH_DEBUG_SV.log(l_module_name,'*** Map Regions parameter is Yes ***');
2356: WSH_DEBUG_SV.log(l_module_name,'Calling procedure Mapping_Regions_Main');
2357: END IF;
2358:
2359: Mapping_Regions_Main (
2352:
2353: IF l_debug_on THEN
2354: WSH_DEBUG_SV.log(l_module_name,'----------------------------------');
2355: WSH_DEBUG_SV.log(l_module_name,'*** Map Regions parameter is Yes ***');
2356: WSH_DEBUG_SV.log(l_module_name,'Calling procedure Mapping_Regions_Main');
2357: END IF;
2358:
2359: Mapping_Regions_Main (
2360: p_location_type => l_location_source_type,
2368: x_sqlerr => l_sqlerr);
2369:
2370: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2371: IF l_debug_on THEN
2372: WSH_DEBUG_SV.log(l_module_name,'Failed in Procedure Mapping_Regions_Main');
2373: END IF;
2374: END IF;
2375: END IF;
2376:
2412: IF x_loc_rec.wsh_location_id IS NULL THEN
2413: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2414: END IF;
2415: IF l_debug_on THEN
2416: WSH_DEBUG_SV.log(l_module_name,'Returning wsh_location_id : '||x_loc_rec.wsh_location_id);
2417: WSH_DEBUG_SV.pop(l_module_name);
2418: END IF;
2419:
2420: EXCEPTION
2413: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2414: END IF;
2415: IF l_debug_on THEN
2416: WSH_DEBUG_SV.log(l_module_name,'Returning wsh_location_id : '||x_loc_rec.wsh_location_id);
2417: WSH_DEBUG_SV.pop(l_module_name);
2418: END IF;
2419:
2420: EXCEPTION
2421:
2421:
2422: WHEN OTHERS THEN
2423: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2424: IF l_debug_on THEN
2425: WSH_DEBUG_SV.pop(l_module_name);
2426: END IF;
2427:
2428: END Transfer_Location;
2429:
2679: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2680: --
2681: IF l_debug_on IS NULL
2682: THEN
2683: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2684: END IF;
2685: --
2686: --
2687: -- Debug Statements
2686: --
2687: -- Debug Statements
2688: --
2689: IF l_debug_on THEN
2690: WSH_DEBUG_SV.push(l_module_name);
2691: --
2692: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOC_ID',P_SHIP_FROM_LOC_ID);
2693: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
2694: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
2688: --
2689: IF l_debug_on THEN
2690: WSH_DEBUG_SV.push(l_module_name);
2691: --
2692: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOC_ID',P_SHIP_FROM_LOC_ID);
2693: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
2694: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
2695: WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
2696: WSH_DEBUG_SV.log(l_module_name,'P_SERVICE_CODE',P_SERVICE_CODE);
2689: IF l_debug_on THEN
2690: WSH_DEBUG_SV.push(l_module_name);
2691: --
2692: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOC_ID',P_SHIP_FROM_LOC_ID);
2693: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
2694: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
2695: WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
2696: WSH_DEBUG_SV.log(l_module_name,'P_SERVICE_CODE',P_SERVICE_CODE);
2697: WSH_DEBUG_SV.log(l_module_name,'P_MODE_CODE',P_MODE_CODE);
2690: WSH_DEBUG_SV.push(l_module_name);
2691: --
2692: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOC_ID',P_SHIP_FROM_LOC_ID);
2693: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
2694: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
2695: WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
2696: WSH_DEBUG_SV.log(l_module_name,'P_SERVICE_CODE',P_SERVICE_CODE);
2697: WSH_DEBUG_SV.log(l_module_name,'P_MODE_CODE',P_MODE_CODE);
2698: END IF;
2691: --
2692: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOC_ID',P_SHIP_FROM_LOC_ID);
2693: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
2694: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
2695: WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
2696: WSH_DEBUG_SV.log(l_module_name,'P_SERVICE_CODE',P_SERVICE_CODE);
2697: WSH_DEBUG_SV.log(l_module_name,'P_MODE_CODE',P_MODE_CODE);
2698: END IF;
2699: --
2692: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOC_ID',P_SHIP_FROM_LOC_ID);
2693: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
2694: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
2695: WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
2696: WSH_DEBUG_SV.log(l_module_name,'P_SERVICE_CODE',P_SERVICE_CODE);
2697: WSH_DEBUG_SV.log(l_module_name,'P_MODE_CODE',P_MODE_CODE);
2698: END IF;
2699: --
2700:
2693: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
2694: WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
2695: WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
2696: WSH_DEBUG_SV.log(l_module_name,'P_SERVICE_CODE',P_SERVICE_CODE);
2697: WSH_DEBUG_SV.log(l_module_name,'P_MODE_CODE',P_MODE_CODE);
2698: END IF;
2699: --
2700:
2701: x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
2706: OPEN get_ship_method_cur;
2707: FETCH get_ship_method_cur INTO l_ship_method_code;
2708: CLOSE get_ship_method_cur;
2709: IF l_debug_on THEN
2710: WSH_DEBUG_SV.logmsg(l_module_name,'ship method derived is ' || l_ship_method_code);
2711: END IF;
2712: ELSE
2713: l_ship_method_code := p_ship_method_code;
2714: END IF;
2713: l_ship_method_code := p_ship_method_code;
2714: END IF;
2715:
2716: IF l_debug_on THEN
2717: WSH_DEBUG_SV.logmsg(l_module_name, 'Check for cached transit time');
2718: END IF;
2719:
2720: IF (p_from = 'OM') THEN
2721: -- check if the transit time for this ship method , origin and destination is already cached
2726: --(g_transit_time_tab(k) IS NOT NULL) THEN
2727: x_transit_time := g_transit_time_tab(k);
2728:
2729: IF l_debug_on THEN
2730: WSH_DEBUG_SV.logmsg(l_module_name,'found ship method transit time information cached');
2731: WSH_DEBUG_SV.pop(l_module_name);
2732: END IF;
2733:
2734: RETURN;
2727: x_transit_time := g_transit_time_tab(k);
2728:
2729: IF l_debug_on THEN
2730: WSH_DEBUG_SV.logmsg(l_module_name,'found ship method transit time information cached');
2731: WSH_DEBUG_SV.pop(l_module_name);
2732: END IF;
2733:
2734: RETURN;
2735: END IF;
2736: END LOOP;
2737: END IF;
2738:
2739: IF l_debug_on THEN
2740: WSH_DEBUG_SV.logmsg(l_module_name, 'Transit Time Not cached',WSH_DEBUG_SV.C_PROC_LEVEL);
2741: END IF;
2742:
2743: --The ship method returned by MSC_ATP_PROC.ATP_Shipping_Lead_Time could be different
2744: --from the ship method passed in. If it doesn't find the transit time, it returns
2754: FETCH get_session_id INTO l_session_id;
2755: CLOSE get_session_id;
2756:
2757: IF l_debug_on THEN
2758: WSH_DEBUG_SV.logmsg(l_module_name,'MSC_ATP session id = ' || l_session_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2759: END IF;
2760:
2761: MSC_ATP_PROC.ATP_Shipping_Lead_Time(p_from_loc_id => p_ship_from_loc_id,
2762: p_to_customer_site_id => p_ship_to_site_id,
2765: x_intransit_time => l_transit_time,
2766: x_return_status => x_return_status);
2767:
2768: IF l_debug_on THEN
2769: WSH_DEBUG_SV.logmsg(l_module_name,'MSC_ATP_PROC.ATP_Shipping_Lead_Time is ' || l_transit_time,WSH_DEBUG_SV.C_PROC_LEVEL);
2770: END IF;
2771:
2772: --FTE callers pass in the ultimate_dropoff_location_id
2773: ELSE
2778: FETCH get_Transit_Loc_Loc INTO l_transit_time;
2779: CLOSE get_Transit_Loc_Loc;
2780: IF l_transit_time IS NOT NULL THEN
2781: IF l_debug_on THEN
2782: WSH_DEBUG_SV.log(l_module_name,'Found the transit time from Location to location: ', l_transit_time);
2783: END IF;
2784: x_transit_time := l_transit_time;
2785: RETURN;
2786: END IF;
2800: CLOSE get_Transit_Region_Loc;
2801:
2802: IF l_transit_time IS NOT NULL THEN
2803: IF l_debug_on THEN
2804: WSH_DEBUG_SV.log(l_module_name,'Found the transit time from Region to Location : ',l_transit_time);
2805: END IF;
2806: x_transit_time :=l_transit_time;
2807: RETURN;
2808: END IF;
2820: CLOSE get_Transit_Region_Loc;
2821:
2822: IF l_transit_time IS NOT NULL THEN
2823: IF l_debug_on THEN
2824: WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Region to Location : ',l_transit_time);
2825: END IF;
2826: x_transit_time :=l_transit_time;
2827: RETURN;
2828: END IF;
2847: CLOSE get_Transit_Loc_Region;
2848:
2849: IF l_transit_time IS NOT NULL THEN
2850: IF l_debug_on THEN
2851: WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Location to Region : ',l_transit_time);
2852: END IF;
2853: x_transit_time :=l_transit_time;
2854: RETURN;
2855: END IF;
2866: FETCH get_Transit_Loc_Region INTO l_transit_time;
2867: CLOSE get_Transit_Loc_Region;
2868: IF l_transit_time IS NOT NULL THEN
2869: IF l_debug_on THEN
2870: WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Location to Region : ',l_transit_time);
2871: END IF;
2872: x_transit_time :=l_transit_time;
2873: RETURN;
2874: END IF;
2891: CLOSE get_Transit_Region_Region;
2892:
2893: IF l_transit_time IS NOT NULL THEN
2894: IF l_debug_on THEN
2895: WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Region to Region : ',l_transit_time);
2896: END IF;
2897: x_transit_time :=l_transit_time;
2898: RETURN;
2899: END IF;
2908: FETCH get_Transit_Region_Region INTO l_transit_time;
2909: CLOSE get_Transit_Region_Region;
2910: IF l_transit_time IS NOT NULL THEN
2911: IF l_debug_on THEN
2912: WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Zone to Zone : ',l_transit_time);
2913: END IF;
2914: x_transit_time :=l_transit_time;
2915: RETURN;
2916: END IF;
2930: FETCH get_Transit_Region_Region INTO l_transit_time;
2931: CLOSE get_Transit_Region_Region;
2932: IF l_transit_time IS NOT NULL THEN
2933: IF l_debug_on THEN
2934: WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Zone to Zone : ',l_transit_time);
2935: END IF;
2936: x_transit_time :=l_transit_time;
2937: RETURN;
2938: END IF;
2957: CLOSE get_Transit_Region_Region;
2958:
2959: IF l_transit_time IS NOT NULL THEN
2960: IF l_debug_on THEN
2961: WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Region to Region : ',l_transit_time);
2962: END IF;
2963: x_transit_time :=l_transit_time;
2964: RETURN;
2965: END IF;
2974: FETCH get_Transit_Region_Region INTO l_transit_time;
2975: CLOSE get_Transit_Region_Region;
2976: IF l_transit_time IS NOT NULL THEN
2977: IF l_debug_on THEN
2978: WSH_DEBUG_SV.log(l_module_name,'Got the transit time from from Region to Zone : ',l_transit_time);
2979: END IF;
2980: x_transit_time :=l_transit_time;
2981: RETURN;
2982: END IF;
2996: FETCH get_Transit_Region_Region INTO l_transit_time;
2997: CLOSE get_Transit_Region_Region;
2998: IF l_transit_time IS NOT NULL THEN
2999: IF l_debug_on THEN
3000: WSH_DEBUG_SV.log(l_module_name,'Got the transit time from Zone to Zone : ',l_transit_time);
3001: END IF;
3002: x_transit_time :=l_transit_time;
3003: RETURN;
3004: END IF;
3037: END IF;
3038: END IF;
3039:
3040: IF l_debug_on THEN
3041: WSH_DEBUG_SV.log(l_module_name,'Transit Time: ', l_transit_time);
3042: END IF;
3043:
3044: x_transit_time := l_transit_time;
3045:
3046: --
3047: -- Debug Statements
3048: --
3049: IF l_debug_on THEN
3050: WSH_DEBUG_SV.pop(l_module_name);
3051: END IF;
3052: --
3053: EXCEPTION
3054: WHEN OTHERS THEN
3052: --
3053: EXCEPTION
3054: WHEN OTHERS THEN
3055: IF l_debug_on THEN
3056: WSH_DEBUG_SV.log(l_module_name,'Exception occurred in WSH_MAP_LOCATION_REGION_PKG');
3057: WSH_DEBUG_SV.log(l_module_name,'SQLCODE: ',sqlcode);
3058: WSH_DEBUG_SV.log(l_module_name,'SQLERRM: ',SUBSTR(SQLERRM,1,200));
3059: WSH_DEBUG_SV.pop(l_module_name);
3060: END IF;
3053: EXCEPTION
3054: WHEN OTHERS THEN
3055: IF l_debug_on THEN
3056: WSH_DEBUG_SV.log(l_module_name,'Exception occurred in WSH_MAP_LOCATION_REGION_PKG');
3057: WSH_DEBUG_SV.log(l_module_name,'SQLCODE: ',sqlcode);
3058: WSH_DEBUG_SV.log(l_module_name,'SQLERRM: ',SUBSTR(SQLERRM,1,200));
3059: WSH_DEBUG_SV.pop(l_module_name);
3060: END IF;
3061:
3054: WHEN OTHERS THEN
3055: IF l_debug_on THEN
3056: WSH_DEBUG_SV.log(l_module_name,'Exception occurred in WSH_MAP_LOCATION_REGION_PKG');
3057: WSH_DEBUG_SV.log(l_module_name,'SQLCODE: ',sqlcode);
3058: WSH_DEBUG_SV.log(l_module_name,'SQLERRM: ',SUBSTR(SQLERRM,1,200));
3059: WSH_DEBUG_SV.pop(l_module_name);
3060: END IF;
3061:
3062:
3055: IF l_debug_on THEN
3056: WSH_DEBUG_SV.log(l_module_name,'Exception occurred in WSH_MAP_LOCATION_REGION_PKG');
3057: WSH_DEBUG_SV.log(l_module_name,'SQLCODE: ',sqlcode);
3058: WSH_DEBUG_SV.log(l_module_name,'SQLERRM: ',SUBSTR(SQLERRM,1,200));
3059: WSH_DEBUG_SV.pop(l_module_name);
3060: END IF;
3061:
3062:
3063: END Get_Transit_Time;
3077: v_delete_permitted varchar2(1) := NULL;
3078: l_msg varchar2(30) := 'WSH_LOC_RECORD_EXISTS';
3079: l_token varchar2(30);
3080: WSH_LOC_EXISTS EXCEPTION;
3081: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3082: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PREDEL_LOC_VALIDATION';
3083: BEGIN
3084:
3085: IF l_debug_on THEN
3082: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PREDEL_LOC_VALIDATION';
3083: BEGIN
3084:
3085: IF l_debug_on THEN
3086: wsh_debug_sv.push(l_module_name);
3087: wsh_debug_sv.LOG(l_module_name, 'p_location_id', p_location_id);
3088: END IF;
3089: BEGIN
3090: l_token := 'WSH_DELIVERY_DETAILS';
3083: BEGIN
3084:
3085: IF l_debug_on THEN
3086: wsh_debug_sv.push(l_module_name);
3087: wsh_debug_sv.LOG(l_module_name, 'p_location_id', p_location_id);
3088: END IF;
3089: BEGIN
3090: l_token := 'WSH_DELIVERY_DETAILS';
3091: IF l_debug_on THEN
3088: END IF;
3089: BEGIN
3090: l_token := 'WSH_DELIVERY_DETAILS';
3091: IF l_debug_on THEN
3092: wsh_debug_sv.LOG(l_module_name, 'Validating records in table : ', l_token);
3093: END IF;
3094: SELECT 'N'
3095: INTO v_delete_permitted
3096: FROM WSH_DELIVERY_DETAILS
3103:
3104: EXCEPTION
3105: WHEN NO_DATA_FOUND THEN
3106: IF l_debug_on THEN
3107: wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3108: END IF;
3109: END ;
3110:
3111: BEGIN
3110:
3111: BEGIN
3112: l_token := 'WSH_CALENDAR_ASSIGNMENTS';
3113: IF l_debug_on THEN
3114: wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3115: END IF;
3116: v_delete_permitted := NULL;
3117: SELECT 'N'
3118: INTO v_delete_permitted
3128:
3129: EXCEPTION
3130: WHEN NO_DATA_FOUND THEN
3131: IF l_debug_on THEN
3132: wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3133: END IF;
3134: END ;
3135:
3136:
3136:
3137: BEGIN
3138: l_token := 'WSH_DOC_SEQUENCE_CATEGORIES';
3139: IF l_debug_on THEN
3140: wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3141: END IF;
3142: v_delete_permitted := NULL;
3143: SELECT 'N'
3144: INTO v_delete_permitted
3154:
3155: EXCEPTION
3156: WHEN NO_DATA_FOUND THEN
3157: IF l_debug_on THEN
3158: wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3159: END IF;
3160: END ;
3161:
3162:
3162:
3163: BEGIN
3164: l_token := 'WSH_PICKING_RULES';
3165: IF l_debug_on THEN
3166: wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3167: END IF;
3168: v_delete_permitted := NULL;
3169: SELECT 'N'
3170: INTO v_delete_permitted
3178:
3179: EXCEPTION
3180: WHEN NO_DATA_FOUND THEN
3181: IF l_debug_on THEN
3182: wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3183: END IF;
3184: END ;
3185:
3186:
3186:
3187: BEGIN
3188: l_token := 'WSH_REGION_LOCATIONS';
3189: IF l_debug_on THEN
3190: wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3191: END IF;
3192: v_delete_permitted := NULL;
3193: SELECT 'N'
3194: INTO v_delete_permitted
3203:
3204: EXCEPTION
3205: WHEN NO_DATA_FOUND THEN
3206: IF l_debug_on THEN
3207: wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3208: END IF;
3209: END ;
3210:
3211: BEGIN
3210:
3211: BEGIN
3212: l_token := 'WSH_SHIPPING_PARAMETERS';
3213: IF l_debug_on THEN
3214: wsh_debug_sv.LOG(l_module_name, 'Validating records in table', l_token);
3215: END IF;
3216: v_delete_permitted := NULL;
3217: SELECT 'N'
3218: INTO v_delete_permitted
3226:
3227: EXCEPTION
3228: WHEN NO_DATA_FOUND THEN
3229: IF l_debug_on THEN
3230: wsh_debug_sv.LOG(l_module_name, 'No records found in table : ', l_token);
3231: END IF;
3232: END ;
3233:
3234: IF l_debug_on THEN
3231: END IF;
3232: END ;
3233:
3234: IF l_debug_on THEN
3235: wsh_debug_sv.LOG(l_module_name, 'No records exists in shipping tables for location id : ', p_location_id);
3236: WSH_DEBUG_SV.pop(l_module_name);
3237: END IF;
3238:
3239:
3232: END ;
3233:
3234: IF l_debug_on THEN
3235: wsh_debug_sv.LOG(l_module_name, 'No records exists in shipping tables for location id : ', p_location_id);
3236: WSH_DEBUG_SV.pop(l_module_name);
3237: END IF;
3238:
3239:
3240: EXCEPTION
3239:
3240: EXCEPTION
3241: WHEN WSH_LOC_EXISTS THEN
3242: IF l_debug_on THEN
3243: wsh_debug_sv.LOG(l_module_name, 'Records found in table : ', l_token);
3244: WSH_DEBUG_SV.pop(l_module_name);
3245: END IF;
3246: FND_MESSAGE.SET_NAME('WSH','WSH_LOC_RECORD_EXISTS');
3247: FND_MESSAGE.SET_TOKEN('TABLE_NAME', l_token);
3240: EXCEPTION
3241: WHEN WSH_LOC_EXISTS THEN
3242: IF l_debug_on THEN
3243: wsh_debug_sv.LOG(l_module_name, 'Records found in table : ', l_token);
3244: WSH_DEBUG_SV.pop(l_module_name);
3245: END IF;
3246: FND_MESSAGE.SET_NAME('WSH','WSH_LOC_RECORD_EXISTS');
3247: FND_MESSAGE.SET_TOKEN('TABLE_NAME', l_token);
3248: APP_EXCEPTION.RAISE_EXCEPTION;