[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);
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: --
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:
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;
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:
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
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 ');
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);
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);
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
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);
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);
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);
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);
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);
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;
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:
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;
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;
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,
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:
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;
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:
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
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;
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:
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:
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:
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:
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;
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
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;
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
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:
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;
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:
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
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);
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);
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);
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:
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:
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;
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
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);
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'));
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;
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:
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();
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:
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:
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
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
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;
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',
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;
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);
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: --
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
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:
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,
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,
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
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:
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 (
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,
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:
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
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:
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:
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
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);
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);
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);
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;
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: --
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:
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;
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;
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
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;
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
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,
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
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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
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;
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:
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:
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;
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
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';
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
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
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
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
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:
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
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:
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
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:
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
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
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
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
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:
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
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);
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;