[Home] [Help]
PACKAGE BODY: APPS.WSH_LOCATIONS_PKG
Source
1 PACKAGE BODY WSH_LOCATIONS_PKG as
2 /* $Header: WSHLOCMB.pls 120.14 2011/03/30 02:52:12 brana ship $ */
3 --
4 -- Package: WSH_LOCATIONS_PKG
5 --
6 -- Purpose: To populate data in WSH_LOCATIONS with the data in
7 -- HZ_LOCATIONS, HR_LOCATIONS and PO_VENDOR_SITES.
8 --
9 --
10
11 --
12 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_LOCATIONS_PKG';
13 --
14
15 TYPE Generic_Cache_Rec_Typ IS RECORD(
16 INPUT_PARAM1 VARCHAR2(500),
17 OUTPUT_PARAM1 VARCHAR2(500)
18 );
19
20 TYPE Generic_Cache_Tab_Typ IS TABLE OF Generic_Cache_Rec_Typ INDEX BY BINARY_INTEGER;
21
22 g_int_loc_cache Generic_Cache_Tab_Typ;
23
24
25 --========================================================================
26 -- PROCEDURE : get_table_index
27 --
28 -- COMMENT : Validate using Hash (internal API)
29 -- uses Hash and avoids linear scans while using PL/SQL tables
30 -- PARAMETERS:
31 -- p_validate_rec -- Input Key to be validated
32 -- x_generic_tab -- populated for existing cached records
33 -- x_index -- New index which can be used for x_flag = U
34 -- x_return_status -- S,E,U,W
35 -- x_flag -- U to use this index,D to indicate valid record
36 --
37 -- HISTORY : Bug 3821688
38 -- NOTE : For performance reasons, no debug calls are added
39 --========================================================================
40 PROCEDURE get_table_index
41 (p_validate_rec IN Generic_Cache_Rec_Typ,
42 p_generic_tab IN Generic_Cache_Tab_Typ,
43 x_index OUT NOCOPY NUMBER,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_flag OUT NOCOPY VARCHAR2
46 )IS
47
48 c_hash_base CONSTANT NUMBER := 1;
49 c_hash_size CONSTANT NUMBER := power(2, 25);
50
51 l_hash_string VARCHAR2(3000) := NULL;
52 l_index NUMBER;
53 l_hash_exists BOOLEAN := FALSE;
54
55 l_flag VARCHAR2(1);
56
57 BEGIN
58
59 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
60
61 -- need to hash this index
62 -- Key (for hash) : param1
63 l_hash_string := p_validate_rec.input_param1;
64
65 -- Hash returns a common index if l_hash_string is identical
66 l_index := dbms_utility.get_hash_value (
67 name => l_hash_string,
68 base => c_hash_base,
69 hash_size => c_hash_size);
70 WHILE NOT l_hash_exists LOOP
71 IF p_generic_tab.EXISTS(l_index) THEN
72 IF (
73 ((p_generic_tab(l_index).input_param1 = p_validate_rec.input_param1)
74 OR
75 (p_generic_tab(l_index).input_param1 IS NULL AND
76 p_validate_rec.input_param1 IS NULL))
77 ) THEN
78 -- exact match found at this index
79 l_flag := 'D';
80 EXIT;
81 ELSE
82
83 -- Index exists but key does not match this table element
84 -- Bump l_index till key matches or table element does not exist
85 l_index := l_index + 1;
86 END IF;
87 ELSE
88 -- Index is not used in the table, can be used to create a new record
89 l_hash_exists := TRUE; -- to exit from the loop
90 l_flag := 'U';
91 END IF;
92 END LOOP;
93
94 x_index := l_index;
95 x_flag := l_flag;
96
97 END get_table_index;
98
99
100
101
102 -----------------------------------------------------------------------------------
103 -- Start of comments
104 -- API name : Get_Missing_Timezones
105 -- Type : Public
106 -- Pre-reqs : None.
107 -- Function : Obtain timezone codes from the Geocoding API for the locations that
108 -- are missing timezone codes.
109 --
110 -- Parameters :
111 -- p_LocationId_Tbl IN ID_Tbl_Type,
112 -- p_Country_Tbl IN Address_Tbl_Type
113 -- p_State_Tbl IN Address_Tbl_Type
114 -- p_Province_Tbl IN Address_Tbl_Type
115 -- p_County_Tbl IN Address_Tbl_Type
116 -- p_City_Tbl IN Address_Tbl_Type
117 -- p_Postal_Code_Tbl IN Address_Tbl_Type
118 -- p_Timezone_Tbl IN LocationCode_Tbl_Type
119 --
120 -- l_debug_on IN BOOLEAN
121 -- x_return_status OUT VARCHAR2
122 -- x_error_msg OUT VARCHAR2
123 --
124 -- Version : 1.0
125 -- Previous version 1.0
126 -- Initial version 1.0
127 -- End of comments
128 -----------------------------------------------------------------------------------
129 PROCEDURE Get_Missing_Timezones (p_LocationId_Tbl IN ID_Tbl_Type,
130 p_Country_Tbl IN Address_Tbl_Type,
131 p_State_Tbl IN Address_Tbl_Type,
132 p_Province_Tbl IN Address_Tbl_Type,
133 p_County_Tbl IN Address_Tbl_Type,
134 p_City_Tbl IN Address_Tbl_Type,
135 p_Postal_Code_Tbl IN Address_Tbl_Type,
136 l_debug_on IN BOOLEAN,
137 x_Latitude_Tbl IN OUT NOCOPY ID_Tbl_Type,
138 x_Longitude_Tbl IN OUT NOCOPY ID_Tbl_Type,
139 x_Timezone_Tbl IN OUT NOCOPY LocationCode_Tbl_Type,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_error_msg OUT NOCOPY VARCHAR2) IS
142
143 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_MISSING_TIMEZONES';
144 l_msg_count VARCHAR2(100);
145 l_location location_rec_type;
146
147 BEGIN
148
149 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
150
151 IF l_debug_on THEN
152 WSH_DEBUG_SV.push(l_module_name);
153 END IF;
154
155 FOR i IN p_LocationId_Tbl.FIRST..p_LocationId_Tbl.LAST LOOP
156 IF (x_Timezone_Tbl(i) IS NULL OR
157 x_Longitude_Tbl(i) IS NULL OR
158 x_Latitude_Tbl(i) IS NULL) THEN
159
160 l_location.COUNTRY := p_Country_Tbl(i);
161 l_location.STATE := p_State_Tbl(i);
162 l_location.PROVINCE := p_Province_Tbl(i);
163 l_location.COUNTY := p_County_Tbl(i);
164 l_location.CITY := p_City_Tbl(i);
165 l_location.POSTAL_CODE := p_Postal_Code_Tbl(i);
166 l_location.LATITUDE := NULL;
167 l_location.LONGITUDE := NULL;
168 l_location.TIMEZONE_CODE := NULL;
169 l_location.GEOMETRY := NULL;
170
171 IF l_debug_on THEN
172 WSH_DEBUG_SV.LogMsg(l_module_name, 'Get_Lat_Long_and_Timezone: ' || p_LocationId_Tbl(i));
173 END IF;
174
175 WSH_GEOCODING.Get_Lat_Long_and_TimeZone(p_api_version => 1.0,
176 p_init_msg_list => NULL,
177 x_return_status => x_return_status,
178 x_msg_count => l_msg_count,
179 x_msg_data => x_error_msg,
180 l_location => l_location);
181
182 IF l_debug_on THEN
183 WSH_DEBUG_SV.Log(l_module_name, 'latitude', l_location.LATITUDE);
184 WSH_DEBUG_SV.Log(l_module_name, 'longitude', l_location.LONGITUDE);
185 WSH_DEBUG_SV.Log(l_module_name, 'timezone_code', l_location.TIMEZONE_CODE);
186 END IF;
187
188 --Update the Latitude, Longitude, Geometry and Timezone Code if
189 --The source values are null.
190
191 IF (x_Latitude_Tbl(i) IS NULL) THEN
192 x_Latitude_Tbl(i) := l_location.LATITUDE;
193 END IF;
194
195 IF (x_Longitude_Tbl(i) IS NULL) THEN
196 x_Longitude_Tbl(i) := l_location.LONGITUDE;
197 END IF;
198
199 IF (x_Timezone_Tbl(i) IS NULL) THEN
200 x_Timezone_Tbl(i) := l_location.TIMEZONE_CODE;
201 END IF;
202
203 END IF;
204
205 END LOOP;
206
207 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
208 x_error_msg := 'Error in WSH_GEOCODING.Get_Lat_Long_and_TimeZone ';
209 --x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
210 END IF;
211
212 IF l_debug_on THEN
213 WSH_DEBUG_SV.pop(l_module_name);
214 END IF;
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 x_error_msg := 'Unexpected Error in Get_Missing_Timezones: ' || sqlerrm;
219 IF l_debug_on THEN
220 WSH_DEBUG_SV.logmsg(l_module_name, x_error_msg);
221 -- Bug 4775798
222 ELSE
223 WSH_UTIL_CORE.printmsg(x_error_msg);
224 END IF;
225 --x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
226 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
227
228 IF l_debug_on THEN
229 WSH_DEBUG_SV.pop(l_module_name);
230 END IF;
231
232 END Get_Missing_Timezones;
233
234
235
236 -----------------------------------------------------------------------------------
237 -- Start of comments
238 -- API name : Update_Geometries
239 -- Type : Public
240 -- Pre-reqs : None.
241 -- Function : Updates the wsh_location table and updates the given location_ids
242 -- with geometry objects obtained from the given longitudes and latitudes
243 --
244 -- Parameters :
245 -- p_location_ids IN ID_Tbl_Type,
246 -- p_latitudes IN ID_Tbl_Type,
247 -- p_longitudes IN ID_Tbl_Type,
248 -- l_debug_on IN BOOLEAN
249 -- x_return_status OUT NOCOPY VARCHAR2
250 -- x_error_msg OUT NOCOPY VARCHAR2
251 --
252 -- Version : 1.0
253 -- Previous version 1.0
254 -- Initial version 1.0
255 -- End of comments
256 -----------------------------------------------------------------------------------
257 PROCEDURE Update_Geometries (p_location_ids IN ID_Tbl_Type,
258 p_latitudes IN ID_Tbl_Type,
259 p_longitudes IN ID_Tbl_Type,
260 l_debug_on IN BOOLEAN,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_error_msg OUT NOCOPY VARCHAR2) IS
263
264 k NUMBER;
265 l_location_id NUMBER;
266 l_latitude NUMBER;
267 l_longitude NUMBER;
268 l_geometry MDSYS.SDO_GEOMETRY;
269 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_GEOMETRIES';
270
271 BEGIN
272
273 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
274
275 IF l_debug_on THEN
276 WSH_DEBUG_SV.push(l_module_name);
277 END IF;
278
279 k := p_location_ids.FIRST;
280 LOOP
281 l_location_id := p_location_ids(k);
282 l_latitude := p_latitudes(k);
283 l_longitude := p_longitudes(k);
284
285 IF (l_latitude IS NOT NULL AND
286 l_longitude IS NOT NULL) THEN
287
288 IF l_debug_on THEN
289 WSH_DEBUG_SV.LogMsg(l_module_name,
290 'Create Geometry for Lat/Lon: ' || l_latitude || '/'|| l_longitude);
291 END IF;
292
293 Create_Geometry (p_longitude => l_longitude,
294 p_latitude => l_latitude,
295 x_geometry => l_geometry,
296 x_return_status => x_return_status,
297 x_error_msg => x_error_msg);
298
299
300 IF (l_geometry IS NOT NULL) THEN
301
302 IF l_debug_on THEN
303 WSH_DEBUG_SV.LogMsg(l_module_name, 'Geometry Created Succesfully');
304 END IF;
305
306 BEGIN
307 UPDATE wsh_locations
308 SET geometry = l_geometry
309 WHERE wsh_location_id = p_location_ids(k);
310 EXCEPTION
311 WHEN OTHERS THEN
312 IF l_debug_on THEN
313 WSH_DEBUG_SV.LogMsg(l_module_name,
314 'Update Geometry failed for location id : ' || p_location_ids(k));
315 END IF;
316 x_error_msg := 'UNEXP. Error in Update Geometries: ' || sqlerrm;
317 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
318 END;
319 END IF;
320
321 END IF;
322
323 EXIT WHEN k = p_location_ids.LAST;
324 k := p_location_ids.NEXT(k);
325 END LOOP;
326
327 IF l_debug_on THEN
328 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status is : '||x_return_status);
329 WSH_DEBUG_SV.pop(l_module_name);
330 END IF;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 x_error_msg := 'UNEXP. Error in Update Geometries: ' || sqlerrm;
335 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
336 IF l_debug_on THEN
337 WSH_DEBUG_SV.pop(l_module_name);
338 END IF;
339 --NULL;
340 END;
341
342
343 /*===========================================================================+
344 | PROCEDURE |
345 | Process_Locations |
346 | |
347 | DESCRIPTION |
348 | |
349 | This procedure will populate the WSH_LOCATIONS table |
350 | with the locations in HZ_LOCATIONS (whose usage is deliver_to |
351 | or ship_to) and HR_LOCATIONS |
352 | |
353 +===========================================================================*/
354
355 --
356 -- Parameters
357 --
358 -- p_location_type Location Type (EXTERNAL/INTERNAL/BOTH)
359 -- p_from_location From Location ID
360 -- p_to_location To Location ID
361 -- p_start_date Start Date
362 -- p_end_date End Date
363 -- If the Start Date and End Date are not null then the locations which are updated
364 -- in this date range will be considered.
365
366 PROCEDURE Process_Locations
367 (
368 p_location_type IN VARCHAR2,
369 p_from_location IN NUMBER,
370 p_to_location IN NUMBER,
371 p_start_date IN VARCHAR2,
372 p_end_date IN VARCHAR2,
373 p_create_facilities IN VARCHAR2 default NULL,
374 p_caller IN VARCHAR2 default NULL,
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_sqlcode OUT NOCOPY NUMBER,
377 x_sqlerr OUT NOCOPY VARCHAR2
378 ) IS
379
380 l_sqlcode NUMBER;
381 l_sqlerr VARCHAR2(2000);
382 l_log_level NUMBER;
383 l_batchsize NUMBER := 500;
384 l_from_location NUMBER;
385 l_to_location NUMBER;
386
387 pUpdateLocationIdTbl ID_Tbl_Type;
388 pInsertLocationIdTbl ID_Tbl_Type;
389
390 tempTbl ID_Tbl_Type;
391
392 i NUMBER;
393 j NUMBER;
394 l_location_source_code VARCHAR2(3);
395 l_previous_rows NUMBER;
396 l_current_rows NUMBER;
397 l_remaining_rows NUMBER;
398
399 l_geometry MDSYS.SDO_GEOMETRY;
400 l_start_date DATE;
401 l_end_date DATE;
402 -- Cursor Declarations
403
404 CURSOR Get_Ext_Update_Loc (l_start_date DATE, l_end_date DATE) IS
405 SELECT SOURCE_LOCATION_ID,
406 HZ.ADDRESS1,
407 HZ.ADDRESS2,
408 HZ.ADDRESS3,
409 HZ.ADDRESS4,
410 HZ.COUNTRY,
411 HZ.STATE,
412 HZ.PROVINCE,
413 HZ.COUNTY,
414 HZ.CITY,
415 HZ.POSTAL_CODE ,
416 HZ.ADDRESS_EXPIRATION_DATE ,
417 HPS.PARTY_SITE_NUMBER,
418 -- BUGFIX 4604769 added Postal code and country
419 -- Bugfix 4995478 added Address2
420 substrb((HPS.PARTY_SITE_NUMBER||' : '||HZ.ADDRESS1||'-'||HZ.ADDRESS2||'-'||HZ.CITY||'-'||NVL(HZ.STATE,HZ.PROVINCE)||'-'|| HZ.POSTAL_CODE||'-'||HZ.COUNTRY),1,500),
421 HP.PARTY_NAME,
422 WSH.LATITUDE,
423 WSH.LONGITUDE,
424 NVL(TZ.TIMEZONE_CODE, WSH.TIMEZONE_CODE),
425 SYSDATE
426 FROM WSH_LOCATIONS WSH, HZ_LOCATIONS HZ, HZ_PARTY_SITES HPS, HZ_PARTIES HP, FND_TIMEZONES_B TZ --3842898 : Replaced fnd_timezones_vl with fnd_timezones_b
427 WHERE SOURCE_LOCATION_ID between p_from_location and p_to_location
428 AND nvl(hz.last_update_date,sysdate) >= nvl(l_start_date, nvl(hz.last_update_date,sysdate))
429 AND nvl(hz.last_update_date,sysdate) < nvl(l_end_date, nvl(hz.last_update_date,sysdate)+1)
430 AND hz.location_id = wsh.source_location_id
431 AND LOCATION_SOURCE_CODE = 'HZ'
432 AND hps.location_id = hz.location_id
433 AND hp.party_id = hps.party_id
434 AND hps.party_site_number = wsh.location_code --bug 6281430: considering existing location_code for updation
435 /* in
436 (
437 SELECT first_value(party_site_number)
438 OVER (ORDER BY status ASC) AS party_site_number
439 FROM hz_party_sites hps1
440 WHERE hps1.location_id = hz.location_id
441 )*/
442 AND hz.timezone_id = tz.upgrade_tz_id(+)
443 ORDER BY HZ.LOCATION_ID;
444
445 CURSOR Get_Ext_Insert_Loc (l_start_date DATE, l_end_date DATE) IS
446 SELECT HZ.LOCATION_ID,
447 HZ.ADDRESS1,
448 HZ.ADDRESS2,
449 HZ.ADDRESS3,
450 HZ.ADDRESS4,
451 HZ.COUNTRY,
452 HZ.STATE,
453 HZ.PROVINCE,
454 HZ.COUNTY,
455 HZ.CITY,
456 HZ.POSTAL_CODE ,
457 HZ.ADDRESS_EXPIRATION_DATE ,
458 HPS.PARTY_SITE_NUMBER,
459 -- BUGFIX 4604769 added Postal code and country
460 -- Bugfix 4995478 added Address2
461 substrb((HPS.PARTY_SITE_NUMBER||' : '||HZ.ADDRESS1||'-'||HZ.ADDRESS2||'-'||HZ.CITY||'-'||NVL(HZ.STATE,HZ.PROVINCE)||'-'|| HZ.POSTAL_CODE||'-'||HZ.COUNTRY),1,500),
462 HP.PARTY_NAME,
463 NULL,
464 NULL,
465 TZ.TIMEZONE_CODE
466 FROM HZ_LOCATIONS HZ, HZ_PARTY_SITES HPS, HZ_PARTIES HP, FND_TIMEZONES_VL TZ
467 WHERE HZ.LOCATION_ID between p_from_location and p_to_location
468 AND nvl(hz.last_update_date,sysdate) >= nvl(l_start_date, nvl(hz.last_update_date,sysdate))
469 AND nvl(hz.last_update_date,sysdate) < nvl(l_end_date, nvl(hz.last_update_date,sysdate)+1)
470 AND hz.location_id NOT IN ( select /*+ INDEX (wl,WSH_LOCATIONS_N1) */
471 wl.source_location_id
472 from wsh_locations wl
473 where wl.location_source_code = 'HZ'
474 and wl.source_location_id = hz.location_id)
475 AND hps.location_id = hz.location_id
476 AND hp.party_id = hps.party_id
477 AND hps.party_site_number in
478 (
479 SELECT first_value(party_site_number)
480 OVER (ORDER BY status ASC) AS party_site_number
481 FROM hz_party_sites hps1
482 WHERE hps1.location_id = hz.location_id
483 )
484 AND hz.timezone_id = tz.upgrade_tz_id(+)
485 ORDER BY HZ.LOCATION_ID;
486
487 -- Affected by p_caller
488 CURSOR Get_Int_Update_Loc (l_start_date DATE, l_end_date DATE) IS
489 SELECT SOURCE_LOCATION_ID,
490 hr.ADDRESS_LINE_1,
491 hr.ADDRESS_LINE_2,
492 hr.ADDRESS_LINE_3,
493 hr.LOC_INFORMATION13,
494 hr.COUNTRY,
495 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2), -- Bug 5108734
496 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3), -- Bug 5108734
497 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1), -- Bug 5108734
498 hr.TOWN_OR_CITY,
499 hr.POSTAL_CODE,
500 hr.INACTIVE_DATE,
501 HRTL.LOCATION_CODE,
502 -- BUGFIX 4604769 added Postal code and country
503 -- Bugfix 4995478 added Address_line_2
504 substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
505 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
506 OU.NAME,
507 WSH.LATITUDE,
508 WSH.LONGITUDE,
509 NVL(HR.TIMEZONE_CODE, WSH.TIMEZONE_CODE),
510 SYSDATE
511 FROM WSH_LOCATIONS WSH, HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL, hr_organization_units ou, mtl_parameters mp
512 WHERE SOURCE_LOCATION_ID between p_from_location and p_to_location
513 AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
514 AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
515 AND hr.location_id = wsh.source_location_id
516 AND HRTL.LOCATION_ID = HR.LOCATION_ID
517 AND HRTL.LANGUAGE = USERENV('LANG')
518 AND LOCATION_SOURCE_CODE = 'HR'
519 AND hr.location_id = ou.location_id
520 AND ou.organization_id = mp.organization_id
521 union
522 SELECT SOURCE_LOCATION_ID,
523 hr.ADDRESS_LINE_1,
524 hr.ADDRESS_LINE_2,
525 hr.ADDRESS_LINE_3,
526 hr.LOC_INFORMATION13,
527 hr.COUNTRY,
528 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2), -- Bug 5108734
529 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3), -- Bug 5108734
530 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1), -- Bug 5108734
531 hr.TOWN_OR_CITY,
532 hr.POSTAL_CODE,
533 hr.INACTIVE_DATE,
534 HRTL.LOCATION_CODE,
535 -- BUGFIX 4604769 added Postal code and country
536 -- Bugfix 4995478 added Address_line_2
537 substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
538 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
539 'XXX' NAME,
540 WSH.LATITUDE,
541 WSH.LONGITUDE,
542 NVL(HR.TIMEZONE_CODE, WSH.TIMEZONE_CODE),
543 SYSDATE
544 FROM WSH_LOCATIONS WSH, HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL,
545 PO_LOCATION_ASSOCIATIONS_ALL PLA
546 WHERE SOURCE_LOCATION_ID between p_from_location and p_to_location
547 AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
548 AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
549 AND hr.location_id = wsh.source_location_id
550 AND HRTL.LOCATION_ID = HR.LOCATION_ID
551 AND HRTL.LANGUAGE = USERENV('LANG')
552 AND LOCATION_SOURCE_CODE = 'HR'
553 AND hr.location_id = pla.location_id
554 AND hr.location_id not in (select ou.location_id from
555 hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
556 and ou.location_id = hr.location_id)
557 union
558 SELECT SOURCE_LOCATION_ID,
559 hr.ADDRESS_LINE_1,
560 hr.ADDRESS_LINE_2,
561 hr.ADDRESS_LINE_3,
562 hr.LOC_INFORMATION13,
563 hr.COUNTRY,
564 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2), -- Bug 5108734
565 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3), -- Bug 5108734
566 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1), -- Bug 5108734
567 hr.TOWN_OR_CITY,
568 hr.POSTAL_CODE,
569 hr.INACTIVE_DATE,
570 HRTL.LOCATION_CODE,
571 -- BUGFIX 4604769 added Postal code and country
572 -- Bugfix 4995478 added ADDRESS_LINE_2
573 substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
574 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
575 'XXX' NAME,
576 WSH.LATITUDE,
577 WSH.LONGITUDE,
578 NVL(HR.TIMEZONE_CODE, WSH.TIMEZONE_CODE),
579 SYSDATE
580 FROM WSH_LOCATIONS WSH, HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL
581 WHERE SOURCE_LOCATION_ID between p_from_location and p_to_location
582 AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
583 AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
584 AND hr.location_id = wsh.source_location_id
585 AND HRTL.LOCATION_ID = HR.LOCATION_ID
586 AND HRTL.LANGUAGE = USERENV('LANG')
587 AND LOCATION_SOURCE_CODE = 'HR'
588 AND p_caller IN ('PO','HR')
589 AND hr.location_id not in (select ou.location_id from
590 hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
591 and ou.location_id = hr.location_id)
592 ORDER BY SOURCE_LOCATION_ID;
593
594
595 -- Affected by p_caller
596 CURSOR Get_Int_Insert_Loc (l_start_date DATE, l_end_date DATE) IS
597 SELECT HR.LOCATION_ID internal_location_id,
598 hr.ADDRESS_LINE_1,
599 hr.ADDRESS_LINE_2,
600 hr.ADDRESS_LINE_3,
601 hr.LOC_INFORMATION13,
602 hr.COUNTRY,
603 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2), -- Bug 5108734
604 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3), -- Bug 5108734
605 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1), -- Bug 5108734
606 hr.TOWN_OR_CITY,
607 hr.POSTAL_CODE,
608 hr.INACTIVE_DATE,
609 HRTL.LOCATION_CODE,
610 -- BUGFIX 4604769 added Postal code and country
611 -- Bugfix 4995478 added ADDRESS_LINE_2
612 substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
613 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
614 OU.NAME,
615 NULL,
616 NULL,
617 HR.TIMEZONE_CODE
618 FROM HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL, hr_organization_units ou, mtl_parameters mp
619 WHERE HR.LOCATION_ID between p_from_location and p_to_location
620 AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
621 AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
622 AND hr.location_id NOT IN ( select wl.source_location_id from wsh_locations wl
623 where wl.location_source_code = 'HR'
624 and wl.source_location_id = hr.location_id)
625 AND HRTL.LOCATION_ID = HR.LOCATION_ID
626 AND HRTL.LANGUAGE = USERENV('LANG')
627 AND hr.location_id = ou.location_id
628 AND ou.organization_id = mp.organization_id
629 union
630 SELECT HR.LOCATION_ID internal_location_id,
631 hr.ADDRESS_LINE_1,
632 hr.ADDRESS_LINE_2,
633 hr.ADDRESS_LINE_3,
634 hr.LOC_INFORMATION13,
635 hr.COUNTRY,
636 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2),
637 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3),
638 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1),
639 hr.TOWN_OR_CITY,
640 hr.POSTAL_CODE,
641 hr.INACTIVE_DATE,
642 HRTL.LOCATION_CODE,
643 -- BUGFIX 4604769 added Postal code and country
644 -- Bugfix 4995478 added ADDRESS_LINE_2
645 substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
646 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
647 'XXX' NAME,
648 NULL,
649 NULL,
650 HR.TIMEZONE_CODE
651 FROM HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL,
652 PO_LOCATION_ASSOCIATIONS_ALL PLA
653 WHERE HR.LOCATION_ID between p_from_location and p_to_location
654 AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
655 AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
656 AND hr.location_id NOT IN ( select wl.source_location_id from wsh_locations wl
657 where wl.location_source_code = 'HR'
658 and wl.source_location_id = hr.location_id)
659 AND HRTL.LOCATION_ID = HR.LOCATION_ID
660 AND HRTL.LANGUAGE = USERENV('LANG')
661 AND hr.location_id = pla.location_id
662 AND hr.location_id not in (select ou.location_id from
663 hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
664 and ou.location_id = hr.location_id)
665 union
666 SELECT HR.LOCATION_ID internal_location_id,
667 hr.ADDRESS_LINE_1,
668 hr.ADDRESS_LINE_2,
669 hr.ADDRESS_LINE_3,
670 hr.LOC_INFORMATION13,
671 hr.COUNTRY,
672 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2),
673 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3),
674 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1),
675 hr.TOWN_OR_CITY,
676 hr.POSTAL_CODE,
677 hr.INACTIVE_DATE,
678 HRTL.LOCATION_CODE,
679 -- BUGFIX 4604769 added Postal code and country
680 -- Bugfix 4995478 added ADDRESS_LINE_2
681 substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
682 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
683 'XXX' NAME,
684 NULL,
685 NULL,
686 HR.TIMEZONE_CODE
687 FROM HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL
688 WHERE HR.LOCATION_ID between p_from_location and p_to_location
689 AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
690 AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
691 AND hr.location_id NOT IN ( select wl.source_location_id from wsh_locations wl
692 where wl.location_source_code = 'HR'
693 and wl.source_location_id = hr.location_id)
694 AND HRTL.LOCATION_ID = HR.LOCATION_ID
695 AND HRTL.LANGUAGE = USERENV('LANG')
696 AND p_caller = 'PO'
697 AND hr.location_id not in (select ou.location_id from
698 hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
699 and ou.location_id = hr.location_id)
700 -- Added following UNION for bug 4337181 (FP of Bug 4255943)
701 union
702 SELECT HR.LOCATION_ID internal_location_id,
703 hr.ADDRESS_LINE_1,
704 hr.ADDRESS_LINE_2,
705 hr.ADDRESS_LINE_3,
706 hr.LOC_INFORMATION13,
707 hr.COUNTRY,
708 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2),
709 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3),
710 DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1),
711 hr.TOWN_OR_CITY,
712 hr.POSTAL_CODE,
713 hr.INACTIVE_DATE,
714 HRTL.LOCATION_CODE,
715 -- BUGFIX 404769 added Postal code and country
716 -- Bugfix 4995478 added ADDRESS_LINE_2
717 substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
718 DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
719 'XXX' NAME,
720 NULL,
721 NULL,
722 HR.TIMEZONE_CODE
723 FROM HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL
724 WHERE HR.LOCATION_ID between p_from_location and p_to_location
725 AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
726 AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
727 AND hr.location_id NOT IN ( select wl.source_location_id from wsh_locations wl
728 where wl.location_source_code = 'HR'
729 and wl.source_location_id = hr.location_id)
730 AND HR.Ship_To_Site_Flag = 'Y'
731 AND HRTL.LOCATION_ID = HR.LOCATION_ID
732 AND HRTL.LANGUAGE = USERENV('LANG')
733 AND nvl(p_caller, 'WSH') in ( 'WSH', 'HR' )
734 AND hr.location_id not in (select ou.location_id from
735 hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
736 and ou.location_id = hr.location_id)
737 ORDER BY internal_location_id
738 ;
739
740 --
741 l_debug_on BOOLEAN;
742 l_use_custom_ui_location VARCHAR2(1);
743 l_custom_ui_loc_codeTbl LocationCode_Tbl_Type;
744 l_dummytbl LocationCode_Tbl_Type ;
745 --
746 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_LOCATIONS';
747 --
748 BEGIN
749
750 --
751 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
752 --
753 IF l_debug_on IS NULL
754 THEN
755 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
756 END IF;
757 --
758 --
759 -- Debug Statements
760 --
761 IF l_debug_on THEN
762 WSH_DEBUG_SV.push(l_module_name);
763 --
764 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
765 WSH_DEBUG_SV.log(l_module_name,'P_FROM_LOCATION',P_FROM_LOCATION);
766 WSH_DEBUG_SV.log(l_module_name,'P_TO_LOCATION',P_TO_LOCATION);
767 WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
768 WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
769 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_FACILITIES',P_CREATE_FACILITIES);
770 WSH_DEBUG_SV.log(l_module_name,'P_CALLER',P_CALLER);
771 END IF;
772
773 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
774 l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
775
776 IF l_debug_on THEN
777 WSH_DEBUG_SV.log(l_module_name,'l_START_DATE',l_START_DATE);
778 WSH_DEBUG_SV.log(l_module_name,'l_END_DATE', l_END_DATE);
779 END IF;
780
781 --
782 -- l_log_level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
783
784 /*
785 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
786
787 IF l_log_level IS NOT NULL THEN
788 WSH_UTIL_CORE.Set_Log_Level(l_log_level);
789 END IF;
790 */
791 IF l_debug_on THEN
792 -- WSH_DEBUG_SV.logmsg(l_module_name,'In the procedure Process_Locations');
793 WSH_DEBUG_SV.logmsg(l_module_name,'Processing Locations between ' || p_from_location || ' and '|| p_to_location);
794 END IF;
795 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
796
797 -- As a part of Bug 4995478 the derivation of ui_location_code is custom enabled.
798 -- Added Dummy call to check if customization is enabled --
799 WSH_CUSTOM_PUB.ui_location_code (
800 p_location_type => l_location_source_code,
801 p_location_idTbl => pUpdateLocationIdTbl,
802 p_address_1Tbl => pUpdateAddress1Tbl ,
803 p_address_2Tbl => pUpdateAddress2Tbl ,
804 p_countryTbl => pUpdateCountryTbl,
805 p_stateTbl => pUpdateStateTbl ,
806 p_provinceTbl => pUpdateProvinceTbl ,
807 p_countyTbl => pUpdateCountyTbl ,
808 p_cityTbl => pUpdateCityTbl ,
809 p_postal_codeTbl => pUpdatePostalCodeTbl ,
810 p_party_site_numberTbl => pUpdateLocCodeTbl,
811 p_location_codeTbl => l_dummytbl,
812 x_use_custom_ui_location => l_use_custom_ui_location,
813 x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl) ;
814 --
815 IF l_use_custom_ui_location = 'Y' THEN
816 --
817 IF l_debug_on THEN
818 WSH_DEBUG_SV.logmsg(l_module_name,' Processing Custom Code for Locations');
819 END IF;
820 --
821 END IF;
822
823
824 l_previous_rows := 0;
825
826 IF p_location_type = 'EXTERNAL' OR p_location_type = 'BOTH' THEN
827 IF l_debug_on THEN
828 WSH_DEBUG_SV.logmsg(l_module_name,'Processing the External Locations');
829 END IF;
830
831 l_location_source_code := 'HZ';
832
833 OPEN Get_Ext_Update_Loc (l_start_date, l_end_date);
834 LOOP
835 FETCH Get_Ext_Update_Loc BULK COLLECT INTO
836 pUpdateLocationIdTbl,
837 pUpdateAddress1Tbl,
838 pUpdateAddress2Tbl,
839 pUpdateAddress3Tbl,
840 pUpdateAddress4Tbl,
841 pUpdateCountryTbl,
842 pUpdateStateTbl,
843 pUpdateProvinceTbl,
844 pUpdateCountyTbl,
845 pUpdateCityTbl,
846 pUpdatePostalCodeTbl,
847 pUpdateExpDateTbl,
848 pUpdateLocCodeTbl,
849 pUpdateUILocCodeTbl,
850 pUpdateOwnerNameTbl,
851 pLatitudeTbl,
852 pLongitudeTbl,
853 pTimezoneTbl,
854 pLastUpdateDateTbl
855 LIMIT l_Batchsize;
856
857 l_current_rows := Get_Ext_Update_Loc%rowcount ;
858 l_remaining_rows := l_current_rows - l_previous_rows;
859
860 IF (l_remaining_rows <= 0) then
861 EXIT;
862 END IF;
863
864 l_previous_rows := l_current_rows ;
865
866 IF pUpdateLocationIdTbl.COUNT <> 0 THEN
867 IF l_debug_on THEN
868 WSH_DEBUG_SV.logmsg(l_module_name,' found '||pUpdateLocationIdTbl.COUNT||' hz locations for update ');
869 END IF;
870
871 --
872 --Bug 4995478 Start
873 IF l_use_custom_ui_location = 'Y' THEN
874 --
875 l_custom_ui_loc_codeTbl.delete ;
876 --
877 WSH_CUSTOM_PUB.ui_location_code (
878 p_location_type => l_location_source_code,
879 p_location_idTbl => pUpdateLocationIdTbl,
880 p_address_1Tbl => pUpdateAddress1Tbl,
881 p_address_2Tbl => pUpdateAddress2Tbl,
882 p_countryTbl => pUpdateCountryTbl,
883 p_stateTbl => pUpdateStateTbl,
884 p_provinceTbl => pUpdateProvinceTbl,
885 p_countyTbl => pUpdateCountyTbl,
886 p_cityTbl => pUpdateCityTbl,
887 p_postal_codeTbl => pUpdatePostalCodeTbl,
888 p_party_site_numberTbl => pUpdateLocCodeTbl,
889 p_location_codeTbl => l_dummytbl,
890 x_use_custom_ui_location => l_use_custom_ui_location,
891 x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl) ;
892 --
893 IF l_custom_ui_loc_codeTbl.count >0 THEN
894 pUpdateUILocCodeTbl.delete;
895 pUpdateUILocCodeTbl := l_custom_ui_loc_codeTbl;
896 END IF;
897 --
898 END IF;
899 --Bug 4995478 End
900 --
901
902 -- Bug 3373128 : OMFST:J:FTE: LOCATION CODE DISPLAYED TWICE
903 -- Commenting the code for get_site_number
904 -- pUpDateUILocCode and pUpdateLocCodeTb1 populated in the cursor.
905 -- get_site_number(pUpdateLocationIdTbl, pUpdateLocCodeTbl, pUpdateUILocCodeTbl);
906
907 Get_Missing_Timezones(p_LocationId_Tbl => pUpdateLocationIdTbl,
908 p_Country_Tbl => pUpdateCountryTbl,
909 p_State_Tbl => pUpdateStateTbl,
910 p_Province_Tbl => pUpdateProvinceTbl,
911 p_County_Tbl => pUpdateCountyTbl,
912 p_City_Tbl => pUpdateCityTbl,
913 p_Postal_Code_Tbl => pUpdatePostalCodeTbl,
914 l_debug_on => l_debug_on,
915 x_Latitude_Tbl => pLatitudeTbl,
916 x_Longitude_Tbl => pLongitudeTbl,
917 x_Timezone_Tbl => pTimezoneTbl,
918 x_return_status => x_return_status,
919 x_error_msg => x_sqlerr);
920
921 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
922 IF l_debug_on THEN
923 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Missing_Timezones : ' || x_sqlerr);
924 END IF;
925 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
926 END IF;
927
928 Update_Locations(pUpdateLocationIdTbl,l_location_source_code,x_return_status);
929
930 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
931 IF l_debug_on THEN
932 WSH_DEBUG_SV.logmsg(l_module_name,'Error in Update_Locations ');
933 WSH_DEBUG_SV.pop(l_module_name);
934 END IF;
935 --x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
936 return;
937 END IF;
938 IF l_debug_on THEN
939 WSH_DEBUG_SV.logmsg(l_module_name,'Successful in the update operation');
940 END IF;
941
942 --Create facilities for the locations, if necessary.
943 --'N' is the default for the <p_create_facilities> parameter.
944 -- 1. If this procedure is explicitly called with 'Y', then we create facilities.
945 -- 2. If <p_create_facilities> is NULL, then we create facilities only if TP is installed.
946 IF (UPPER(p_create_facilities) = 'Y' OR
947 (p_create_facilities IS NULL AND WSH_UTIL_CORE.Tp_Is_Installed = 'Y')) THEN
948 i := pUpdateLocationIdTbl.COUNT;
949 IF l_debug_on THEN
950 WSH_DEBUG_SV.logmsg(l_module_name,
951 'Calling WSH_FACILITIES_INTEGRATION.CREATE_FACILITIES with ' ||i|| ' External locations.');
952 END IF;
953 BEGIN
954
955 WSH_FACILITIES_INTEGRATION.Create_Facilities(p_location_ids => pUpdateLocationIdTbl,
956 p_company_names => pUpdateOwnerNameTbl,
957 p_site_names => pUpdateLocCodeTbl,
958 x_return_status => x_return_status,
959 x_error_msg => x_sqlerr);
960
961 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
962 IF l_debug_on THEN
963 WSH_DEBUG_SV.logmsg(l_module_name,'Create Facilities: ' || x_sqlerr);
964 END IF;
965 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
966 END IF;
967
968 EXCEPTION
969 WHEN OTHERS THEN
970 IF l_debug_on THEN
971 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXP. EXCEPTION WHILE CREATING FACILITIES: ' || sqlerrm);
972 END IF;
973 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
974 END;
975 END IF;
976
977 --If TP is installed, manually create a Geometry object from the latitude and longitude
978 -- and update the location with the geometry object.
979 IF (WSH_UTIL_CORE.Tp_Is_Installed = 'Y') THEN
980 BEGIN
981 Update_Geometries(p_location_ids => pUpdateLocationIdTbl,
982 p_latitudes => pLatitudeTbl,
983 p_longitudes => pLongitudeTbl,
984 l_debug_on => l_debug_on,
985 x_return_status => x_return_status,
986 x_error_msg => x_sqlerr);
987 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
988 IF l_debug_on THEN
989 WSH_DEBUG_SV.logmsg(l_module_name,'Update_Geometries : ' || x_sqlerr);
990 END IF;
991 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
992 END IF;
993 EXCEPTION
994 WHEN OTHERS THEN
995 IF l_debug_on THEN
996 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXP. Exception while updating geometries: ' || sqlerrm);
997 END IF;
998 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
999 END;
1000 END IF;
1001
1002 END IF;
1003
1004 EXIT WHEN Get_Ext_Update_Loc%NOTFOUND;
1005 END LOOP;
1006
1007 IF Get_Ext_Update_Loc%ISOPEN THEN
1008 CLOSE Get_Ext_Update_Loc;
1009 END IF;
1010
1011 ----------------------------------------------------------------
1012 l_previous_rows := 0;
1013 OPEN Get_Ext_Insert_Loc (l_start_date, l_end_date);
1014 LOOP
1015 FETCH Get_Ext_Insert_Loc BULK COLLECT INTO
1016 pInsertLocationIdTbl,
1017 pInsertAddress1Tbl,
1018 pInsertAddress2Tbl,
1019 pInsertAddress3Tbl,
1020 pInsertAddress4Tbl,
1021 pInsertCountryTbl,
1022 pInsertStateTbl,
1023 pInsertProvinceTbl,
1024 pInsertCountyTbl,
1025 pInsertCityTbl,
1026 pInsertPostalCodeTbl,
1027 pInsertExpDateTbl,
1028 pInsertLocCodeTbl,
1029 pInsertUILocCodeTbl,
1030 pInsertOwnerNameTbl,
1031 pLatitudeTbl,
1032 pLongitudeTbl,
1033 pTimezoneTbl
1034 LIMIT l_batchsize;
1035
1036 l_current_rows := Get_Ext_Insert_Loc%rowcount ;
1037 l_remaining_rows := l_current_rows - l_previous_rows;
1038
1039 IF (l_remaining_rows <= 0) then
1040 EXIT;
1041 END IF;
1042
1043 l_previous_rows := l_current_rows ;
1044
1045 IF pInsertLocationIdTbl.COUNT = 0 THEN
1046 IF l_debug_on THEN
1050 -- Debug Statements
1047 WSH_DEBUG_SV.logmsg(l_module_name,'Number of locations to be inserted or updated in this range is 0');
1048 END IF;
1049 --
1051 --
1052 IF l_debug_on THEN
1053 WSH_DEBUG_SV.pop(l_module_name);
1054 END IF;
1055 --
1056 return ;
1057 END IF;
1058
1059 IF pInsertLocationIdTbl.COUNT <> 0 THEN
1060 IF l_debug_on THEN
1061 WSH_DEBUG_SV.logmsg(l_module_name,' found '||pInsertLocationIdTbl.COUNT||' hz locations for insert ');
1062 END IF;
1063
1064 --
1065 --Bug 4995478 Start
1066 IF l_use_custom_ui_location = 'Y' THEN
1067 --
1068 l_custom_ui_loc_codeTbl.delete ;
1069 --
1070 WSH_CUSTOM_PUB.ui_location_code (
1071 p_location_type => l_location_source_code,
1072 p_location_idTbl => pInsertLocationIdTbl ,
1073 p_address_1Tbl => pInsertAddress1Tbl,
1074 p_address_2Tbl => pInsertAddress2Tbl,
1075 p_countryTbl => pInsertCountryTbl ,
1076 p_stateTbl => pInsertStateTbl ,
1077 p_provinceTbl => pInsertProvinceTbl,
1078 p_countyTbl => pInsertCountyTbl ,
1079 p_cityTbl => pInsertCityTbl ,
1080 p_postal_codeTbl => pInsertPostalCodeTbl,
1081 p_party_site_numberTbl => pInsertLocCodeTbl,
1082 p_location_codeTbl => l_dummytbl,
1083 x_use_custom_ui_location => l_use_custom_ui_location,
1084 x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl) ;
1085 --
1086 IF l_custom_ui_loc_codeTbl.count >0 THEN
1087 pInsertUILocCodeTbl.delete;
1088 pInsertUILocCodeTbl := l_custom_ui_loc_codeTbl;
1089 END IF;
1090 --
1091 END IF;
1092 --
1093 --Bug 4995478 End
1094 -- Vijay: commenting out
1095 -- get_site_number(pInsertLocationIdTbl, pInsertLocCodeTbl, pInsertUILocCodeTbl);
1096
1097 Get_Missing_Timezones(p_LocationId_Tbl => pInsertLocationIdTbl,
1098 p_Country_Tbl => pInsertCountryTbl,
1099 p_State_Tbl => pInsertStateTbl,
1100 p_Province_Tbl => pInsertProvinceTbl,
1101 p_County_Tbl => pInsertCountyTbl,
1102 p_City_Tbl => pInsertCityTbl,
1103 p_Postal_Code_Tbl => pInsertPostalCodeTbl,
1104 l_debug_on => l_debug_on,
1105 x_Latitude_Tbl => pLatitudeTbl,
1106 x_Longitude_Tbl => pLongitudeTbl,
1107 x_Timezone_Tbl => pTimezoneTbl,
1108 x_return_status => x_return_status,
1109 x_error_msg => x_sqlerr);
1110
1111 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1112 IF l_debug_on THEN
1113 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Missing_Timezones : ' || x_sqlerr);
1114 END IF;
1115 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1116 END IF;
1117
1118 Insert_Locations(pInsertLocationIdTbl,l_location_source_code,x_return_status);
1119
1120 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1121 IF l_debug_on THEN
1122 -- Bug 4775798
1123 WSH_DEBUG_SV.logmsg(l_module_name,'Error in Insert_Locations ');
1124 WSH_DEBUG_SV.pop(l_module_name);
1125 END IF;
1126 return;
1127 END IF;
1128 IF l_debug_on THEN
1129 WSH_DEBUG_SV.logmsg(l_module_name,'Successful in the insert operation');
1130 END IF;
1131 --Create facilities for the locations, if necessary.
1132 --'N' is the default for the <p_create_facilities> parameter.
1133 -- 1. If this procedure is explicitly called with 'Y', then we create facilities.
1134 -- 2. If <p_create_facilities> is NULL, then we create facilities only if TP is installed.
1135 IF (UPPER(p_create_facilities) = 'Y' OR
1136 (p_create_facilities IS NULL AND WSH_UTIL_CORE.Tp_Is_Installed = 'Y')) THEN
1137 i := pInsertLocationIdTbl.COUNT;
1138 IF l_debug_on THEN
1139 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_FACILITIES_INTEGRATION.CREATE_FACILITIES with ' ||i|| 'External locations.');
1140 END IF;
1141 BEGIN
1142
1143 WSH_FACILITIES_INTEGRATION.Create_Facilities(p_location_ids => pInsertLocationIdTbl,
1144 p_company_names => pInsertOwnerNameTbl,
1145 p_site_names => pInsertLocCodeTbl,
1146 x_return_status => x_return_status,
1147 x_error_msg => x_sqlerr);
1148
1149 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1150 IF l_debug_on THEN
1151 WSH_DEBUG_SV.logmsg(l_module_name,'Create Facilities: ' || x_sqlerr);
1152 END IF;
1153 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1154 END IF;
1155
1156 EXCEPTION
1157 WHEN OTHERS THEN
1158 IF l_debug_on THEN
1159 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXP. EXCEPTION WHILE CREATING FACILITIES: ' || sqlerrm);
1160 END IF;
1161 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1162 END;
1163
1164 END IF;
1165
1166
1167 --If TP is installed, manually create a Geometry object from the latitude and longitude
1168 -- and update the location with the geometry object.
1169 IF (WSH_UTIL_CORE.Tp_Is_Installed = 'Y') THEN
1170 BEGIN
1171 Update_Geometries(p_location_ids => pInsertLocationIdTbl,
1172 p_latitudes => pLatitudeTbl,
1173 p_longitudes => pLongitudeTbl,
1174 l_debug_on => l_debug_on,
1175 x_return_status => x_return_status,
1176 x_error_msg => x_sqlerr);
1177 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1178 IF l_debug_on THEN
1179 WSH_DEBUG_SV.logmsg(l_module_name,'Update_Geometries : ' || x_sqlerr);
1180 END IF;
1181 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1182 END IF;
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 IF l_debug_on THEN
1186 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXP. Exception while updating geometries: ' || sqlerrm);
1187 END IF;
1188 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1189 END;
1190 END IF;
1191
1192 END IF;
1193
1194 EXIT WHEN Get_Ext_Insert_Loc%NOTFOUND;
1195 END LOOP;
1196
1197 IF Get_Ext_Insert_Loc%ISOPEN THEN
1198 CLOSE Get_Ext_Insert_Loc;
1199 END IF;
1200
1201 END IF; -- End of External Location Type
1202
1203 l_previous_rows := 0;
1204
1205 IF p_location_type = 'INTERNAL' OR p_location_type = 'BOTH' THEN
1206 IF l_debug_on THEN
1207 WSH_DEBUG_SV.logmsg(l_module_name,'Processing the Internal Locations');
1208 END IF;
1209 l_location_source_code := 'HR';
1210
1211 OPEN Get_Int_Update_Loc (l_start_date, l_end_date);
1212 LOOP
1213 FETCH Get_Int_Update_Loc BULK COLLECT INTO
1214 pUpdateLocationIdTbl,
1215 pUpdateAddress1Tbl,
1216 pUpdateAddress2Tbl,
1217 pUpdateAddress3Tbl,
1218 pUpdateAddress4Tbl,
1219 pUpdateCountryTbl,
1220 pUpdateStateTbl,
1221 pUpdateProvinceTbl,
1222 pUpdateCountyTbl,
1223 pUpdateCityTbl,
1224 pUpdatePostalCodeTbl,
1225 pUpdateExpDateTbl,
1226 pUpdateLocCodeTbl,
1227 pUpdateUILocCodeTbl,
1228 pUpdateOwnerNameTbl,
1229 pLatitudeTbl,
1230 pLongitudeTbl,
1231 pTimezoneTbl,
1232 pLastUpdateDateTbl
1233 LIMIT l_Batchsize;
1234
1235 l_current_rows := Get_Int_Update_Loc%rowcount ;
1236 l_remaining_rows := l_current_rows - l_previous_rows;
1237
1238 IF (l_remaining_rows <= 0) then
1239 EXIT;
1240 END IF;
1241
1242 l_previous_rows := l_current_rows ;
1243
1244 IF pUpdateLocationIdTbl.COUNT <> 0 THEN
1245 IF l_debug_on THEN
1246 WSH_DEBUG_SV.logmsg(l_module_name,' found '||pUpdateLocationIdTbl.COUNT||' hr locations for update ');
1247 END IF;
1248
1249 --
1250 --Bug 4995478 Start
1251 IF l_use_custom_ui_location = 'Y' THEN
1252 --
1253 l_custom_ui_loc_codeTbl.delete ;
1254 --
1255 WSH_CUSTOM_PUB.ui_location_code (
1256 p_location_type => l_location_source_code,
1257 p_location_idTbl => pUpdateLocationIdTbl,
1258 p_address_1Tbl => pUpdateAddress1Tbl,
1259 p_address_2Tbl => pUpdateAddress2Tbl,
1260 p_countryTbl => pUpdateCountryTbl,
1261 p_stateTbl => pUpdateStateTbl,
1262 p_provinceTbl => pUpdateProvinceTbl,
1263 p_countyTbl => pUpdateCountyTbl,
1264 p_cityTbl => pUpdateCityTbl,
1265 p_postal_codeTbl => pUpdatePostalCodeTbl,
1266 p_party_site_numberTbl => l_dummytbl,
1267 p_location_codeTbl => pUpdateLocCodeTbl,
1268 x_use_custom_ui_location => l_use_custom_ui_location,
1269 x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl);
1270 --
1271 IF l_custom_ui_loc_codeTbl.count >0 THEN
1272 pUpdateUILocCodeTbl.delete;
1273 pUpdateUILocCodeTbl := l_custom_ui_loc_codeTbl;
1274 END IF;
1275 --
1276 END IF;
1277 --
1278 --Bug 4995478 End
1279 Get_Missing_Timezones(p_LocationId_Tbl => pUpdateLocationIdTbl,
1280 p_Country_Tbl => pUpdateCountryTbl,
1281 p_State_Tbl => pUpdateStateTbl,
1282 p_Province_Tbl => pUpdateProvinceTbl,
1283 p_County_Tbl => pUpdateCountyTbl,
1284 p_City_Tbl => pUpdateCityTbl,
1285 p_Postal_Code_Tbl => pUpdatePostalCodeTbl,
1286 l_debug_on => l_debug_on,
1287 x_Latitude_Tbl => pLatitudeTbl,
1288 x_Longitude_Tbl => pLongitudeTbl,
1289 x_Timezone_Tbl => pTimezoneTbl,
1290 x_return_status => x_return_status,
1291 x_error_msg => x_sqlerr);
1292
1293 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1294 IF l_debug_on THEN
1295 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Missing_Timezones : ' || x_sqlerr);
1296 END IF;
1297 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1298 END IF;
1299
1300 Update_Locations(pUpdateLocationIdTbl,l_location_source_code,x_return_status);
1301
1302 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1303
1304 --x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1305 IF l_debug_on THEN
1306 WSH_DEBUG_SV.logmsg(l_module_name,'Error in Update_Locations ');
1307 WSH_DEBUG_SV.pop(l_module_name);
1308 END IF;
1309 return;
1310 END IF;
1311 IF l_debug_on THEN
1312 WSH_DEBUG_SV.logmsg(l_module_name,'Successful in the update operation');
1313 END IF;
1314 --Create facilities for the locations, if necessary.
1315 --'N' is the default for the <p_create_facilities> parameter.
1316 -- 1. If this procedure is explicitly called with 'Y', then we create facilities.
1317 -- 2. If <p_create_facilities> is NULL, then we create facilities only if TP is installed.
1318 IF (UPPER(p_create_facilities) = 'Y' OR
1319 (p_create_facilities IS NULL AND WSH_UTIL_CORE.Tp_Is_Installed = 'Y')) THEN
1320 i := pUpdateLocationIdTbl.COUNT;
1321 IF l_debug_on THEN
1322 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_FACILITIES_INTEGRATION.CREATE_FACILITIES with ' ||i|| 'Internal locations.');
1323 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_FACILITIES_INTEGRATION.CREATE_FACILITIES with ' ||pUpdateOwnerNameTbl.COUNT|| 'Company Names.');
1324 END IF;
1325 BEGIN
1326
1330 x_return_status => x_return_status,
1327 WSH_FACILITIES_INTEGRATION.Create_Facilities(p_location_ids => pUpdateLocationIdTbl,
1328 p_company_names => pUpdateOwnerNameTbl,
1329 p_site_names => pUpdateLocCodeTbl,
1331 x_error_msg => x_sqlerr);
1332
1333
1334 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1335 IF l_debug_on THEN
1336 WSH_DEBUG_SV.logmsg(l_module_name,'Create Facilities: ' || x_sqlerr);
1337 END IF;
1338 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1339 END IF;
1340
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 IF l_debug_on THEN
1344 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXP. EXCEPTION WHILE CREATING FACILITIES: ' || sqlerrm);
1345 END IF;
1346 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1347 END;
1348 END IF;
1349
1350 --If TP is installed, manually create a Geometry object from the latitude and longitude
1351 -- and update the location with the geometry object.
1352 IF (WSH_UTIL_CORE.Tp_Is_Installed = 'Y') THEN
1353 BEGIN
1354 Update_Geometries(p_location_ids => pUpdateLocationIdTbl,
1355 p_latitudes => pLatitudeTbl,
1356 p_longitudes => pLongitudeTbl,
1357 l_debug_on => l_debug_on,
1358 x_return_status => x_return_status,
1359 x_error_msg => x_sqlerr);
1360 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1361 IF l_debug_on THEN
1362 WSH_DEBUG_SV.logmsg(l_module_name,'Update_Geometries : ' || x_sqlerr);
1363 END IF;
1364 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1365 END IF;
1366 EXCEPTION
1367 WHEN OTHERS THEN
1368 IF l_debug_on THEN
1369 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXP. Exception while updating geometries: ' || sqlerrm);
1370 END IF;
1371 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1372 END;
1373 END IF;
1374
1375 END IF;
1376
1377 EXIT WHEN Get_Int_Update_Loc%NOTFOUND;
1378 END LOOP;
1379
1380 IF Get_Int_Update_Loc%ISOPEN THEN
1381 CLOSE Get_Int_Update_Loc;
1382 END IF;
1383
1384 --------------------------------------------------
1385 l_previous_rows := 0;
1386
1387 OPEN Get_Int_Insert_Loc (l_start_date, l_end_date);
1388 LOOP
1389 FETCH Get_Int_Insert_Loc BULK COLLECT INTO
1390 pInsertLocationIdTbl,
1391 pInsertAddress1Tbl,
1392 pInsertAddress2Tbl,
1393 pInsertAddress3Tbl,
1394 pInsertAddress4Tbl,
1395 pInsertCountryTbl,
1396 pInsertStateTbl,
1397 pInsertProvinceTbl,
1398 pInsertCountyTbl,
1399 pInsertCityTbl,
1400 pInsertPostalCodeTbl,
1401 pInsertExpDateTbl,
1402 pInsertLocCodeTbl,
1403 pInsertUILocCodeTbl,
1404 --pUpdateOwnerNameTbl,
1405 pInsertOwnerNameTbl,
1406 pLatitudeTbl,
1407 pLongitudeTbl,
1408 pTimezoneTbl
1409 LIMIT l_Batchsize;
1410
1411 l_current_rows := Get_Int_Insert_Loc%rowcount ;
1412 l_remaining_rows := l_current_rows - l_previous_rows;
1413
1414 IF (l_remaining_rows <= 0) then
1415 EXIT;
1416 END IF;
1417
1418 l_previous_rows := l_current_rows ;
1419
1420 IF pInsertLocationIdTbl.COUNT = 0 THEN
1421 IF l_debug_on THEN
1422 WSH_DEBUG_SV.logmsg(l_module_name,'Number of locations to be inserted or updated in this range is 0');
1423 END IF;
1424 --
1425 -- Debug Statements
1426 --
1427 IF l_debug_on THEN
1428 WSH_DEBUG_SV.pop(l_module_name);
1429 END IF;
1430 --
1431 return ;
1432 END IF;
1433
1434 IF pInsertLocationIdTbl.COUNT <> 0 THEN
1435 IF l_debug_on THEN
1436 WSH_DEBUG_SV.logmsg(l_module_name,' found '||pInsertLocationIdTbl.COUNT||' hr locations for insert ');
1437 END IF;
1438
1439 --
1440 --Bug 4995478 Start
1441 IF l_use_custom_ui_location = 'Y' THEN
1442 --
1443 l_custom_ui_loc_codeTbl.delete ;
1444 --
1445 WSH_CUSTOM_PUB.ui_location_code (
1446 p_location_type => l_location_source_code,
1447 p_location_idTbl => pInsertLocationIdTbl,
1448 p_address_1Tbl => pInsertAddress1Tbl,
1449 p_address_2Tbl => pInsertAddress2Tbl,
1450 p_countryTbl => pInsertCountryTbl,
1451 p_stateTbl => pInsertStateTbl,
1452 p_provinceTbl => pInsertProvinceTbl,
1453 p_countyTbl => pInsertCountyTbl,
1457 p_location_codeTbl => pInsertLocCodeTbl,
1454 p_cityTbl => pInsertCityTbl,
1455 p_postal_codeTbl => pInsertPostalCodeTbl,
1456 p_party_site_numberTbl => l_dummytbl,
1458 x_use_custom_ui_location => l_use_custom_ui_location,
1459 x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl);
1460 --
1461 IF l_custom_ui_loc_codeTbl.count >0 THEN
1462 pInsertUILocCodeTbl.delete;
1463 pInsertUILocCodeTbl := l_custom_ui_loc_codeTbl;
1464 END IF;
1465 --
1466 END IF;
1467 --
1468 --Bug 4995478 End
1469
1470 Get_Missing_Timezones(p_LocationId_Tbl => pInsertLocationIdTbl,
1471 p_Country_Tbl => pInsertCountryTbl,
1472 p_State_Tbl => pInsertStateTbl,
1473 p_Province_Tbl => pInsertProvinceTbl,
1474 p_County_Tbl => pInsertCountyTbl,
1475 p_City_Tbl => pInsertCityTbl,
1476 p_Postal_Code_Tbl => pInsertPostalCodeTbl,
1477 l_debug_on => l_debug_on,
1478 x_Latitude_Tbl => pLatitudeTbl,
1479 x_Longitude_Tbl => pLongitudeTbl,
1480 x_Timezone_Tbl => pTimezoneTbl,
1481 x_return_status => x_return_status,
1482 x_error_msg => x_sqlerr);
1483
1484 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1485 IF l_debug_on THEN
1486 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Missing_Timezones : ' || x_sqlerr);
1487 END IF;
1488 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1489 END IF;
1490
1491 Insert_Locations(pInsertLocationIdTbl, l_location_source_code,x_return_status);
1492
1493 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1494
1495 --x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1496 IF l_debug_on THEN
1497 WSH_DEBUG_SV.logmsg(l_module_name,'Error in Insert_Locations ');
1498 WSH_DEBUG_SV.pop(l_module_name);
1499 END IF;
1500 return;
1501 END IF;
1502 IF l_debug_on THEN
1503 WSH_DEBUG_SV.logmsg(l_module_name,'Successful in the insert operation');
1504 END IF;
1505 --Create facilities for the locations, if necessary.
1506 --'N' is the default for the <p_create_facilities> parameter.
1507 -- 1. If this procedure is explicitly called with 'Y', then we create facilities.
1508 -- 2. If <p_create_facilities> is NULL, then we create facilities only if TP is installed.
1509 IF (UPPER(p_create_facilities) = 'Y' OR
1510 (p_create_facilities IS NULL AND WSH_UTIL_CORE.Tp_Is_Installed = 'Y')) THEN
1511 i := pInsertLocationIdTbl.COUNT;
1512 IF l_debug_on THEN
1513 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_FACILITIES_INTEGRATION.CREATE_FACILITIES with ' ||i|| 'Internal locations.');
1514 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_FACILITIES_INTEGRATION.CREATE_FACILITIES with ' ||pInsertOwnerNameTbl.COUNT|| 'Company Names.');
1515 END IF;
1516 BEGIN
1517 WSH_FACILITIES_INTEGRATION.Create_Facilities(p_location_ids => pInsertLocationIdTbl,
1518 p_company_names => pInsertOwnerNameTbl,
1519 p_site_names => pInsertLocCodeTbl,
1520 x_return_status => x_return_status,
1521 x_error_msg => x_sqlerr);
1522
1523 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1524 IF l_debug_on THEN
1525 WSH_DEBUG_SV.logmsg(l_module_name,'Create Facilities: ' || x_sqlerr);
1526 END IF;
1527 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1528 END IF;
1529
1530 EXCEPTION
1531 WHEN OTHERS THEN
1532 IF l_debug_on THEN
1533 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXP. EXCEPTION WHILE CREATING FACILITIES: ' || sqlerrm);
1534 END IF;
1535 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1536 END;
1537 END IF;
1538
1539 --If TP is installed, manually create a Geometry object from the latitude and longitude
1540 -- and update the location with the geometry object.
1541 IF (WSH_UTIL_CORE.Tp_Is_Installed = 'Y') THEN
1542 BEGIN
1543 Update_Geometries(p_location_ids => pInsertLocationIdTbl,
1544 p_latitudes => pLatitudeTbl,
1545 p_longitudes => pLongitudeTbl,
1546 l_debug_on => l_debug_on,
1547 x_return_status => x_return_status,
1548 x_error_msg => x_sqlerr);
1549 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1550 IF l_debug_on THEN
1551 WSH_DEBUG_SV.logmsg(l_module_name,'Update_Geometries : ' || x_sqlerr);
1552 END IF;
1553 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1554 END IF;
1555 EXCEPTION
1556 WHEN OTHERS THEN
1557 IF l_debug_on THEN
1558 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXP. Exception while updating geometries: ' || sqlerrm);
1559 END IF;
1560 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1561 END;
1562 END IF;
1563
1564 END IF;
1565 EXIT WHEN Get_Int_Insert_Loc%NOTFOUND;
1566 END LOOP;
1567
1568 --------------------------------------------------
1569
1570 IF Get_Int_Insert_Loc%ISOPEN THEN
1571 CLOSE Get_Int_Insert_Loc;
1572 END IF;
1573
1574 END IF; -- End of Internal Location Type
1575
1576 --
1577 -- Debug Statements
1578 --
1579 IF l_debug_on THEN
1580 WSH_DEBUG_SV.pop(l_module_name);
1581 END IF;
1582 --
1583 EXCEPTION
1584 WHEN others THEN
1585 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1586 l_sqlcode := SQLCODE;
1587 l_sqlerr := SQLERRM;
1588 --
1589 -- Debug Statements
1590 --
1591 IF l_debug_on THEN
1592 -- Bug 4775798
1593 WSH_DEBUG_SV.logmsg(l_module_name,'In the Others Exception of Locations_Child');
1594 WSH_DEBUG_SV.logmsg(l_module_name,'SQLCODE : ' || l_sqlcode);
1595 WSH_DEBUG_SV.logmsg(l_module_name,'SQLERRM : ' || l_sqlerr);
1596 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1597
1598 ELSE
1599 WSH_UTIL_CORE.printmsg('In the Others Exception of Locations_Child');
1600 WSH_UTIL_CORE.printmsg(l_sqlcode);
1601 WSH_UTIL_CORE.printmsg(l_sqlerr);
1602 END IF;
1603 --
1604 END Process_Locations;
1605
1606
1607 PROCEDURE get_site_number(pLocationIdTbl IN ID_Tbl_Type,
1608 pLocationCodeTbl IN OUT NOCOPY LocationCode_Tbl_Type,
1609 pUILocationCodeTbl IN OUT NOCOPY LocationCode_Tbl_Type)
1610 IS
1611 CURSOR get_site_number(l_location_id NUMBER) IS
1612 SELECT party_site_number
1613 FROM hz_party_sites hps
1614 WHERE hps.location_id = l_location_id;
1615
1616 i NUMBER;
1617 l_site_number1 VARCHAR2(30);
1618 l_site_number2 VARCHAR2(30);
1619
1620 --
1621 l_debug_on BOOLEAN;
1622 --
1623 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_SITE_NUMBER';
1624 --
1625 BEGIN
1626
1627 --
1628 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1629 --
1630 IF l_debug_on IS NULL
1631 THEN
1632 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1633 END IF;
1634 --
1635 --
1636 -- Debug Statements
1637 --
1638 IF l_debug_on THEN
1639 WSH_DEBUG_SV.push(l_module_name);
1640 END IF;
1641 --
1642 FOR i IN 1..pLocationIdTbl.count LOOP
1643
1644 l_site_number1 := null;
1645 l_site_number2 := null;
1646
1647 pLocationCodeTbl(i) := null;
1648
1649 OPEN get_site_number(pLocationIdTbl(i));
1650 FETCH get_site_number INTO l_site_number1;
1651
1652 IF (l_site_number1 IS NOT NULL) THEN
1653
1654 -- Vijay: commenting out to be in sync with upgrade script changes on 5/12/03
1655 -- FETCH get_site_number INTO l_site_number2;
1656
1657 -- IF (l_site_number2 IS NULL) THEN
1658
1659 pLocationCodeTbl(i) := l_site_number1;
1660 pUILocationCodeTbl(i) := l_site_number1 || ' : ' || pUILocationCodeTbl(i);
1661
1662 -- END IF;
1663
1664 END IF;
1665 CLOSE get_site_number;
1666
1667 END LOOP;
1668
1669 --
1670 -- Debug Statements
1671 --
1672 IF l_debug_on THEN
1673 WSH_DEBUG_SV.pop(l_module_name);
1674 END IF;
1675 --
1676 END get_site_number;
1677
1678
1679 PROCEDURE insert_locations(pInsertLocationIdTbl IN ID_Tbl_Type,
1680 p_location_source_code IN VARCHAR2,
1681 x_return_status OUT NOCOPY VARCHAR2)
1682 IS
1683 i NUMBER;
1684 j NUMBER;
1685 l_error_code NUMBER;
1686 l_start NUMBER;
1687 l_loc_id NUMBER;
1688 --
1689 l_debug_on BOOLEAN;
1690 --
1691 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_LOCATIONS';
1692 --
1693 BEGIN
1694
1695 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1696 --
1697 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1698 --
1699 IF l_debug_on IS NULL
1700 THEN
1701 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1702 END IF;
1703 --
1704 --
1705 -- Debug Statements
1706 --
1707 IF l_debug_on THEN
1708 WSH_DEBUG_SV.push(l_module_name);
1709 --
1710 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE_CODE',P_LOCATION_SOURCE_CODE);
1711 END IF;
1712 --
1713 BEGIN
1714 l_start := pInsertLocationIdTbl.FIRST;
1715
1716 LOOP
1717 BEGIN
1718 forall i in l_start..pInsertLocationIdTbl.LAST
1719 Insert into WSH_LOCATIONS
1720 (WSH_LOCATION_ID,
1721 SOURCE_LOCATION_ID,
1722 LOCATION_SOURCE_CODE,
1723 LOCATION_CODE,
1724 ADDRESS1,
1725 ADDRESS2,
1726 ADDRESS3,
1727 ADDRESS4,
1728 COUNTRY,
1729 STATE,
1730 PROVINCE,
1731 COUNTY,
1732 CITY,
1733 POSTAL_CODE,
1734 INACTIVE_DATE,
1735 CREATION_DATE,
1736 CREATED_BY,
1737 LAST_UPDATE_DATE,
1738 LAST_UPDATED_BY,
1739 UI_LOCATION_CODE,
1740 LATITUDE,
1741 LONGITUDE,
1742 TIMEZONE_CODE)
1743 Values (pInsertLocationIdTbl(i),
1744 pInsertLocationIdTbl(i),
1745 p_location_source_code,
1746 pInsertLocCodeTbl(i),
1747 pInsertAddress1Tbl(i),
1748 pInsertAddress2Tbl(i),
1749 pInsertAddress3Tbl(i),
1750 pInsertAddress4Tbl(i),
1751 pInsertCountryTbl(i),
1752 pInsertStateTbl(i),
1753 pInsertProvinceTbl(i),
1754 pInsertCountyTbl(i),
1755 pInsertCityTbl(i),
1756 pInsertPostalCodeTbl(i),
1757 pInsertExpDateTbl(i),
1758 SYSDATE,
1759 1,
1760 SYSDATE,
1761 1,
1762 pInsertUILocCodeTbl(i),
1763 pLatitudeTbl(i),
1764 pLongitudeTbl(i),
1765 pTimezoneTbl(i));
1766 EXIT;
1767 EXCEPTION
1768 WHEN OTHERS THEN
1769 l_error_code := SQLCODE;
1770 l_loc_id := pInsertLocationIdTbl(l_start + sql%rowcount);
1771 --ORA:00001 is the unique constraint violation. We are attempting
1772 --to insert a facility that already exists.
1773 IF ( l_error_code = -1 ) THEN
1774 IF l_debug_on THEN
1775 WSH_DEBUG_SV.logmsg(l_module_name,'Duplicate location id found for location_id '||l_loc_id);
1776 END IF;
1777 l_start := l_start + sql%rowcount + 1;
1778 ELSE
1779 IF l_debug_on THEN
1780 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXPECTED ERROR WHILE CREATING WSH_LOCATION LOCATION '||l_loc_id);
1781 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR MESSAGE '||SQLERRM);
1782 -- Bug 4775798
1783 ELSE
1784 WSH_UTIL_CORE.printmsg('UNEXPECTED ERROR WHILE CREATING WSH_LOCATION FOR LOCATION '||l_loc_id);
1785 WSH_UTIL_CORE.printmsg('ERROR MESSAGE '||SQLERRM);
1786 END IF;
1787 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1788 IF l_debug_on THEN
1789 WSH_DEBUG_SV.pop(l_module_name);
1790 END IF;
1791 RETURN;
1792 END IF;
1793 END;
1794 END LOOP;
1795 END;
1796
1797 --IF (WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
1798 insert_location_owners(pInsertLocationIdTbl, p_location_source_code,x_return_status);
1799 --END IF;
1800
1801 --
1802 -- Debug Statements
1803 --
1804 IF l_debug_on THEN
1805 WSH_DEBUG_SV.pop(l_module_name);
1806 END IF;
1807 --
1808 -- Bug 4775798
1809 EXCEPTION
1810 WHEN OTHERS THEN
1811 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1812 WSH_UTIL_CORE.DEFAULT_HANDLER( 'WSH_LOCATIONS_PKG.insert_locations', l_module_name);
1813 IF l_debug_on THEN
1814 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1815 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1816 ELSE
1817 WSH_UTIL_CORE.printmsg('Unexpected error in Insert_Locations');
1818 WSH_UTIL_CORE.printmsg(SQLERRM);
1819 END IF;
1820 END insert_locations;
1821
1822 PROCEDURE update_locations(pUpdateLocationIdTbl IN ID_Tbl_Type,
1823 p_location_source_code IN VARCHAR2,
1824 x_return_status OUT NOCOPY VARCHAR2)
1825 IS
1826 i NUMBER;
1827 j NUMBER;
1828 l_error_code NUMBER;
1829 l_loc_id NUMBER;
1830 --
1831 l_debug_on BOOLEAN;
1832 --
1833 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_LOCATIONS';
1834 --
1835 BEGIN
1836
1837 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1838 --
1839 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1840 --
1841 IF l_debug_on IS NULL
1842 THEN
1843 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1844 END IF;
1845 --
1846 --
1847 -- Debug Statements
1848 --
1849 IF l_debug_on THEN
1850 WSH_DEBUG_SV.push(l_module_name);
1851 --
1852 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE_CODE',P_LOCATION_SOURCE_CODE);
1853 END IF;
1854 --
1855 BEGIN
1856 forall i in pUpdateLocationIdTbl.FIRST..pUpdateLocationIdTbl.LAST
1857 update WSH_LOCATIONS
1858 set location_code = pUpdateLocCodeTbl(i),
1859 address1 = pUpdateAddress1Tbl(i),
1860 address2 = pUpdateAddress2Tbl(i),
1861 address3 = pUpdateAddress3Tbl(i),
1862 address4 = pUpdateAddress4Tbl(i),
1863 country = pUpdateCountryTbl(i),
1864 state = pUpdateStateTbl(i),
1865 province = pUpdateProvinceTbl(i),
1866 county = pUpdateCountyTbl(i),
1867 city = pUpdateCityTbl(i),
1868 postal_code = pUpdatePostalCodeTbl(i),
1869 inactive_date = pUpdateExpDateTbl(i),
1870 latitude = pLatitudeTbl(i),
1871 longitude = pLongitudeTbl(i),
1872 timezone_code = pTimezoneTbl(i),
1873 ui_location_code = pUpdateUILocCodeTbl(i),
1874 last_update_date = pLastUpdateDateTbl(i)
1875 where SOURCE_LOCATION_ID = pUpdateLocationIdTbl(i)
1876 and location_source_code = p_location_source_code;
1877
1878 EXCEPTION
1879 WHEN OTHERS THEN
1880 l_loc_id := pUpdateLocCodeTbl(pUpdateLocationIdTbl.FIRST + sql%rowcount);
1881 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1882 IF l_debug_on THEN
1883 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXPECTED ERROR WHILE UPDATING WSH_LOCATION FOR LOCATION '||l_loc_id);
1884 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR MESSAGE '||SQLERRM);
1885 WSH_DEBUG_SV.pop(l_module_name);
1886 -- Bug 4775798
1887 ELSE
1888 WSH_UTIL_CORE.printmsg('UNEXPECTED ERROR WHILE UPDATING WSH_LOCATION FOR LOCATION '||l_loc_id);
1889 WSH_UTIL_CORE.printmsg('ERROR MESSAGE '||SQLERRM);
1890 END IF;
1891 return;
1892 END;
1893 --IF (WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
1894 insert_location_owners(pUpdateLocationIdTbl, p_location_source_code,x_return_status);
1895 --END IF;
1896
1897 --
1898 -- Debug Statements
1899 --
1900 IF l_debug_on THEN
1901 WSH_DEBUG_SV.pop(l_module_name);
1902 END IF;
1903 --
1904 -- Bug 4775798
1905 EXCEPTION
1906 WHEN OTHERS THEN
1907 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1908 WSH_UTIL_CORE.DEFAULT_HANDLER( 'WSH_LOCATIONS_PKG.update_locations', l_module_name);
1909 IF l_debug_on THEN
1910 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1911 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1912 ELSE
1913 WSH_UTIL_CORE.printmsg('Unexpected error in Update_Locations');
1914 WSH_UTIL_CORE.printmsg(SQLERRM);
1915 END IF;
1916 END update_locations;
1917
1918 PROCEDURE check_duplicate_rec
1919 ( p_wsh_location_id IN NUMBER,
1920 p_owner_party_id IN NUMBER,
1921 p_owner_type IN NUMBER,
1922 x_dup_rec_pk OUT NOCOPY NUMBER,
1923 x_return_status OUT NOCOPY VARCHAR2)
1924 IS
1925
1926 CURSOR c_check_duplicate(p_location_id IN NUMBER, p_owner_type IN NUMBER, p_party_id IN NUMBER) IS
1927 SELECT location_owner_id
1928 FROM wsh_location_owners
1929 WHERE wsh_location_id = p_location_id
1930 AND owner_type = p_owner_type
1931 AND owner_party_id = p_party_id;
1932
1933 l_dup_owner_id NUMBER;
1934
1935 BEGIN
1936 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1937
1938 OPEN c_check_duplicate(p_wsh_location_id,p_owner_type,p_owner_party_id);
1939 FETCH c_check_duplicate INTO l_dup_owner_id;
1940 IF (c_check_duplicate%NOTFOUND) THEN
1941 x_dup_rec_pk := -1;
1942 ELSE
1943 x_dup_rec_pk := l_dup_owner_id;
1944 END IF;
1945 CLOSE c_check_duplicate;
1946
1947 EXCEPTION
1948 WHEN others THEN
1949 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1950 END check_duplicate_rec;
1951
1952 PROCEDURE get_location_owner(p_location_id IN NUMBER,
1953 p_location_source_code IN VARCHAR2) IS
1954
1955 /*
1956 CURSOR get_location_info(c_location_id IN NUMBER) IS
1957 SELECT owner_party_id, location_owner_id
1958 FROM wsh_location_owners
1959 WHERE wsh_location_id = c_location_id;
1960 */
1961
1962 CURSOR get_party_info(c_location_id IN NUMBER) IS
1963 SELECT distinct ps.party_id
1964 FROM hz_party_sites ps
1965 WHERE ps.location_id = c_location_id;
1966
1967 -- TODO
1968 -- might not be tied to internal org
1969 CURSOR get_org_info(c_location_id IN NUMBER) IS
1970 SELECT ou.organization_id
1971 FROM hr_organization_units ou, mtl_parameters mp
1972 WHERE ou.organization_id = mp.organization_id
1973 AND ou.location_id = c_location_id;
1974 --bugfix 11843792 modified query for supplier
1975 CURSOR check_party_carrier_supplier(l_party_id IN NUMBER) IS
1976 SELECT 3
1977 FROM wsh_carriers c
1978 WHERE c.carrier_id = l_party_id
1979 UNION ALL
1980 SELECT 4
1981 FROM hz_parties hz,
1982 ap_suppliers su,
1983 hz_party_usg_assignments hzusg
1984 WHERE hz.party_id = su.party_id
1985 AND hz.party_id = hzusg.party_id
1986 AND hzusg.PARTY_USAGE_CODE = 'SUPPLIER'
1987 AND su.party_id = l_party_id;
1988
1989 cnt NUMBER;
1990 l_owner_type NUMBER;
1991 l_party_id NUMBER;
1992 l_owner_id NUMBER;
1993 l_organization_id NUMBER;
1994
1995 l_dup_pk NUMBER := 0;
1996 l_return_status VARCHAR2(1) := NULL;
1997 --
1998 l_debug_on BOOLEAN;
1999 --
2000 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LOCATION_OWNER';
2001 --
2002 BEGIN
2003
2004 --
2005 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2006 --
2007 IF l_debug_on IS NULL
2008 THEN
2009 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2010 END IF;
2011 --
2012 --
2013 -- Debug Statements
2014 --
2015 IF l_debug_on THEN
2016 WSH_DEBUG_SV.push(l_module_name);
2017 --
2018 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
2019 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE_CODE',P_LOCATION_SOURCE_CODE);
2020 END IF;
2021 --
2022 -- Found out that this API is getting called multiple times for the same location_id
2023 IF l_debug_on THEN
2024 WSH_DEBUG_SV.logmsg(l_module_name,' getting owner for location '||p_location_id);
2025 END IF;
2026
2027 /*
2028 OPEN get_location_info(p_location_id);
2029 FETCH get_location_info INTO l_party_id, l_owner_id;
2030 CLOSE get_location_info;
2031 */
2032
2033 -- AG this check not required
2034 -- a location might already be linked to a party
2035 -- and can still get linked to a second (or third, fourth ..) party
2036 -- as long as it is not a duplicate entry
2037
2038 --IF (l_party_id IS NULL) THEN
2039
2040 cnt := pLocOwnerIdTbl.count;
2041
2042 IF (p_location_source_code = 'HR') THEN
2043
2044 OPEN get_org_info(p_location_id);
2045 LOOP
2046 FETCH get_org_info INTO l_organization_id;
2047 EXIT WHEN get_org_info%NOTFOUND;
2048
2049 -- AG check duplicate record
2050
2051 check_duplicate_rec
2052 ( p_wsh_location_id => p_location_id,
2053 p_owner_party_id => l_organization_id,
2054 p_owner_type => 1,
2055 x_dup_rec_pk => l_dup_pk,
2056 x_return_status => l_return_status);
2057
2058 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2059
2060 IF (l_dup_pk = -1) THEN
2061 -- Duplicate row does not exist.
2062 IF l_debug_on THEN
2063 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record does not exist for location '||p_location_id|| ' and organization id '||l_organization_id);
2064 END IF;
2065
2066 cnt := cnt + 1;
2067 pLocLocationIdTbl(cnt) := p_location_id;
2068 pLocOwnerIdTbl(cnt) := l_organization_id;
2069 pLocOwnerTypeTbl(cnt) := 1;
2070
2071 ELSE
2072 -- duplicate row exists
2073 IF l_debug_on THEN
2074 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record exists for location '||p_location_id|| ' and organization id '||l_organization_id);
2075 END IF;
2076 END IF;
2077 END IF;
2078
2079 END LOOP;
2080
2081 IF get_org_info%ROWCOUNT = 0 THEN
2082
2083 -- AG check duplicate record
2084
2085 check_duplicate_rec
2086 ( p_wsh_location_id => p_location_id,
2087 p_owner_party_id => -1,
2088 p_owner_type => 1,
2089 x_dup_rec_pk => l_dup_pk,
2090 x_return_status => l_return_status);
2091
2092 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2093
2094 IF (l_dup_pk = -1) THEN
2095 -- Duplicate row does not exist.
2096 IF l_debug_on THEN
2097 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record does not exist for location '||p_location_id|| ' and organization id -1 ');
2098 END IF;
2099
2100 cnt := cnt + 1;
2101 pLocLocationIdTbl(cnt) := p_location_id;
2102 pLocOwnerIdTbl(cnt) := -1;
2103 pLocOwnerTypeTbl(cnt) := 1;
2104
2105 ELSE
2106 -- duplicate row exists
2107 IF l_debug_on THEN
2108 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record exists for location '||p_location_id|| ' and organization id -1 ');
2109 END IF;
2110 END IF;
2111 END IF;
2112
2113
2114 END IF;
2115 CLOSE get_org_info;
2116
2117 ELSE
2118
2119 FOR parties IN get_party_info(p_location_id) LOOP
2120
2121 /*
2122 -- check duplicate record
2123
2124 cnt := cnt + 1;
2125
2126 pLocLocationIdTbl(cnt) := p_location_id;
2127 pLocOwnerIdTbl(cnt) := parties.party_id;
2128
2129 */
2130 l_owner_type := 2;
2131
2132 OPEN check_party_carrier_supplier(parties.party_id);
2133 FETCH check_party_carrier_supplier INTO l_owner_type;
2134 CLOSE check_party_carrier_supplier;
2135
2136 -- If party is carrier OR supplier,
2137 -- l_owner_type will be 3 OR 4 ( <> 2)
2138 -- Otherwise, value of l_owner_type will not change
2139
2140 --
2141 IF l_debug_on THEN
2142 WSH_DEBUG_SV.log(l_module_name,'l_owner_type after check_party_carrier_supplier : ',l_owner_type);
2143 END IF;
2144 --
2145
2146 IF (l_owner_type IS NULL) THEN
2147 l_owner_type := 2;
2148 END IF;
2149
2150 -- AG check duplicate record
2151
2152 check_duplicate_rec
2153 ( p_wsh_location_id => p_location_id,
2154 p_owner_party_id => parties.party_id,
2155 p_owner_type => l_owner_type,
2156 x_dup_rec_pk => l_dup_pk,
2157 x_return_status => l_return_status);
2158
2159 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2160
2161 IF (l_dup_pk = -1) THEN
2162 -- Duplicate row does not exist.
2163 IF l_debug_on THEN
2164 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record does not exist for location '||p_location_id|| ' and party id '||parties.party_id||' and owner type '||l_owner_type );
2165 END IF;
2166
2167 cnt := cnt + 1;
2168 pLocLocationIdTbl(cnt) := p_location_id;
2169 pLocOwnerIdTbl(cnt) := parties.party_id;
2170 pLocOwnerTypeTbl(cnt) := l_owner_type;
2171
2172 ELSE
2173 -- duplicate row exists
2174 IF l_debug_on THEN
2175 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record exists for location '||p_location_id|| ' and party id '||parties.party_id||' and owner type '||l_owner_type );
2176 END IF;
2177 END IF;
2178 END IF;
2179
2180 END LOOP;
2181
2182 END IF;
2183
2184 --END IF;
2185
2186 --
2187 -- Debug Statements
2188 --
2189 IF l_debug_on THEN
2190 WSH_DEBUG_SV.pop(l_module_name);
2191 END IF;
2192 --
2193 END get_location_owner;
2194
2195
2196
2197 PROCEDURE insert_location_owners(pLocationIdTbl IN ID_Tbl_Type,
2198 p_location_source_code IN VARCHAR2,
2199 x_return_status OUT NOCOPY VARCHAR2)
2200 IS
2201 i NUMBER;
2202 j NUMBER;
2203 cnt NUMBER;
2204 l_start NUMBER;
2205 l_error_code NUMBER;
2206 l_loc_id NUMBER;
2207 --
2208 l_debug_on BOOLEAN;
2209 --
2210 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_LOCATION_OWNERS';
2211 --
2212 BEGIN
2213 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2214 --
2215 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2216 --
2217 IF l_debug_on IS NULL
2218 THEN
2219 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2220 END IF;
2221 --
2222 --
2223 -- Debug Statements
2224 --
2225 IF l_debug_on THEN
2226 WSH_DEBUG_SV.push(l_module_name);
2227 --
2228 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE_CODE',P_LOCATION_SOURCE_CODE);
2229 END IF;
2230 --
2231 pLocLocationIdTbl.delete;
2232 pLocOwnerIdTbl.delete;
2233 pLocOwnerTypeTbl.delete;
2234 IF l_debug_on THEN
2235 WSH_DEBUG_SV.logmsg(l_module_name,' Number of locations being processed '||pLocationIdTbl.count);
2236 END IF;
2237 FOR cnt IN 1..pLocationIdTbl.count LOOP
2238 get_location_owner(pLocationIdTbl(cnt),p_location_source_code);
2239 END LOOP;
2240 IF l_debug_on THEN
2241 WSH_DEBUG_SV.logmsg(l_module_name,' Inserting owner table count '||pLocLocationIdTbl.count);
2242 END IF;
2243 IF (pLocLocationIdTbl.count > 0) THEN
2244
2245 BEGIN
2246 l_start := pLocLocationIdTbl.FIRST;
2247 LOOP
2248 BEGIN
2249 FORALL i in l_start..pLocLocationIdTbl.LAST
2250 --Primary key - location_owner_id
2251 Insert into WSH_LOCATION_OWNERS
2252 ( LOCATION_OWNER_ID,
2253 WSH_LOCATION_ID,
2254 OWNER_PARTY_ID,
2255 OWNER_TYPE,
2256 CREATION_DATE,
2257 CREATED_BY,
2258 LAST_UPDATE_DATE,
2259 LAST_UPDATED_BY,
2260 LAST_UPDATE_LOGIN)
2261 Values (
2262 wsh_location_owners_s.nextval,
2263 pLocLocationIdTbl(i),
2264 pLocOwnerIdTbl(i),
2265 pLocOwnerTypeTbl(i),
2266 SYSDATE,
2267 1,
2268 SYSDATE,
2269 1,
2270 1);
2271 EXIT;
2272 EXCEPTION
2273 WHEN OTHERS THEN
2274 l_error_code := SQLCODE;
2275 l_loc_id := pLocLocationIdTbl(l_start + sql%rowcount);
2276 --ORA:00001 is the unique constraint violation. We are attempting
2277 --to insert a facility that already exists.
2278 IF ( l_error_code = -1 ) THEN
2279 IF l_debug_on THEN
2280 WSH_DEBUG_SV.logmsg(l_module_name,'Duplicate owner_party_id found for location_id '||l_loc_id);
2281 END IF;
2282 l_start := l_start + sql%rowcount + 1;
2283 ELSE
2284 IF l_debug_on THEN
2285 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXPECTED ERROR WHILE CREATING LOCATION OWNER FOR LOCATION '||l_loc_id);
2286 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR MESSAGE '||SQLERRM);
2287 END IF;
2288 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2289 RETURN;
2290 END IF;
2291 END;
2292 END LOOP;
2293 END;
2294 END IF;
2295
2296 --
2297 -- Debug Statements
2298 --
2299 IF l_debug_on THEN
2300 WSH_DEBUG_SV.pop(l_module_name);
2301 END IF;
2302 --
2303 END insert_location_owners;
2304
2305
2306
2307 -----------------------------------------------------------------------------------
2308 -- Start of comments
2309 -- API name : Create_Geometry
2310 -- Type : Public
2311 -- Pre-reqs : None.
2312 -- Function : Create a MDSYS.SDO_GEOMETRY Object from a longitude and latitude.
2313 --
2314 -- Parameters :
2315 -- p_longitude IN NUMBER The Longitude.
2316 -- p_latitude IN NUMBER The Latitude.
2317 --
2318 -- x_geometry OUT MDSYS.SDO_GEOMETRY The geometry object created.
2319 -- x_status OUT VARCHAR2
2320 -- x_error_msg OUT VARCHAR2
2321 --
2322 -- Version : 1.0
2323 -- Previous version 1.0
2324 -- Initial version 1.0
2325 -- End of comments
2326 -----------------------------------------------------------------------------------
2327 PROCEDURE Create_Geometry (p_longitude IN NUMBER,
2328 p_latitude IN NUMBER,
2329 x_geometry OUT NOCOPY MDSYS.SDO_GEOMETRY,
2330 x_return_status OUT NOCOPY VARCHAR2,
2331 x_error_msg OUT NOCOPY VARCHAR2 ) IS
2332
2333 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_GEOMETRY';
2334 l_debug_on BOOLEAN;
2335 BEGIN
2336 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2337 IF l_debug_on IS NULL THEN
2338 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2339 END IF;
2340
2341 IF l_debug_on THEN
2342 WSH_DEBUG_SV.push(l_module_name);
2343 END IF;
2344
2345 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2346
2347 x_geometry := MDSYS.SDO_GEOMETRY(2001, --SDO_GTYPE: Geometry type.
2348 -- 2 indicates two-dimensional, 1 indicates a single point
2349 8307, --SDO_SRID: 8307 is SRID for 'Long/Lat (WGS 84)' coordinate system
2350 MDSYS.SDO_POINT_TYPE(p_longitude, p_latitude, NULL),
2351 NULL, -- SDO_ELEM_INFO: Not needed if point_type
2352 NULL); -- SDO_ORDINATES: Not needed if point_type
2353
2354 IF l_debug_on THEN
2355 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status is : '||x_return_status);
2356 WSH_DEBUG_SV.pop(l_module_name);
2357 END IF;
2358
2359 EXCEPTION
2360 WHEN OTHERS THEN
2361 x_error_msg := 'ERROR: During Geometry Creation ' || sqlerrm;
2362 IF l_debug_on THEN
2363 WSH_DEBUG_SV.LogMsg(l_module_name, x_error_msg);
2364 WSH_DEBUG_SV.pop(l_module_name);
2365 END IF;
2366 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2367
2368 END Create_Geometry;
2369
2370
2371 --
2372 -- Procedure: convert_internal_cust_location
2373 -- Parameters:
2374 -- p_location_id location to convert
2375 -- x_internal_org_location_id physical location id
2376 -- if not NULL, it is converted.
2377 -- if NULL, the location is not
2378 -- an internal customer location.
2379 -- x_return_status return status
2380 --
2381 -- Description:
2382 -- Attempt to convert the putative internal customer
2383 -- location into the internal organization location.
2384 -- If it is internal customer location, the API will
2385 -- populate x_internal_org_location_id as a physical
2386 -- location.
2387 -- Otherwise, x_internal_org_location_id will be NULL.
2388 --
2389 --
2390 -- Bug#5307873:new input parameter p_customer_id has been added.
2391 PROCEDURE Convert_internal_cust_location(
2392 p_internal_cust_location_id IN NUMBER,
2393 p_customer_id IN NUMBER DEFAULT NULL,
2394 x_internal_org_location_id OUT NOCOPY NUMBER,
2395 x_return_status OUT NOCOPY VARCHAR2)
2396 IS
2397 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONVERT_INTERNAL_CUST_LOCATION';
2398 --
2399 l_debug_on BOOLEAN;
2400 --
2401 CURSOR c_convert(p_int_cust_loc_id NUMBER,p_customer_id NUMBER) IS
2402 SELECT ploc.LOCATION_ID internal_org_location_id
2403 FROM PO_LOCATION_ASSOCIATIONS_ALL ploc,
2404 hz_cust_site_uses_all site_uses,
2405 hz_cust_acct_sites_all acct_sites,
2406 HZ_PARTY_SITES sites
2407 WHERE ploc.SITE_USE_ID = site_uses.SITE_USE_ID
2408 AND site_uses.CUST_ACCT_SITE_ID = acct_sites.CUST_ACCT_SITE_ID
2409 AND acct_sites.PARTY_SITE_ID = sites.PARTY_SITE_ID
2410 AND ploc.CUSTOMER_ID = acct_sites.CUST_ACCOUNT_ID
2411 AND sites.location_id = p_int_cust_loc_id
2412 AND sites.location_id = p_int_cust_loc_id
2413 AND ploc.CUSTOMER_ID = nvl(p_customer_id,ploc.CUSTOMER_ID);
2414 --
2415 l_cache_rec Generic_Cache_Rec_Typ;
2416 l_index NUMBER;
2417 l_rs VARCHAR2(1);
2418 l_flag VARCHAR2(1);
2419
2420 BEGIN
2421 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2422 --
2423 IF l_debug_on IS NULL THEN
2424 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2425 END IF;
2426
2427 IF l_debug_on THEN
2428 WSH_DEBUG_SV.push(l_module_name);
2429 END IF;
2430 --
2431 --
2432 l_cache_rec.input_param1 := to_char(p_internal_cust_location_id)||'-'||to_char(p_customer_id);
2433
2434 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2435
2436 get_table_index(
2437 p_validate_rec => l_cache_rec,
2438 p_generic_tab => g_int_loc_cache,
2439 x_index => l_index,
2440 x_return_status => l_rs,
2441 x_flag => l_flag);
2442
2443 IF (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
2444 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2445 l_flag := NULL;
2446 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2447 END IF;
2448
2449 IF l_flag = 'U' THEN
2450 OPEN c_convert(p_internal_cust_location_id,p_customer_id);
2451 FETCH c_convert INTO l_cache_rec.output_param1;
2452 IF c_convert%NOTFOUND THEN
2453 l_cache_rec.output_param1 := NULL;
2454 END IF;
2455 CLOSE c_convert;
2456 g_int_loc_cache(l_index) := l_cache_rec;
2457 END IF;
2458
2459 IF g_int_loc_cache.EXISTS(l_index) THEN
2460 x_internal_org_location_id := g_int_loc_cache(l_index).output_param1;
2461 ELSE
2462 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2463 x_internal_org_location_id := NULL;
2464 END IF;
2465
2466 --
2467 --
2468 IF l_debug_on THEN
2469 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2470 WSH_DEBUG_SV.pop(l_module_name);
2471 END IF;
2472
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2476 IF c_convert%ISOPEN THEN
2477 CLOSE c_convert;
2478 END IF;
2479 WSH_UTIL_CORE.DEFAULT_HANDLER(
2480 'WSH_LOCATIONS_PKG.convert_internal_cust_location',
2481 l_module_name);
2482
2483 IF l_debug_on THEN
2484 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2485 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2486 END IF;
2487
2488 END convert_internal_cust_location;
2489
2490
2491
2492 --
2493 -- Function: convert_internal_cust_location
2494 -- Parameters:
2495 -- p_location_id location to convert
2496 --
2497 -- Return:
2498 -- internal organizatoin location ID
2499 -- NULL if not converted or no mapping exists.
2500 -- NOT NULL if successfully converted and mapping exists.
2501 -- Description:
2502 -- Wrapper function for procedure convert_internal_cust_location
2503 --
2504 --
2505 FUNCTION Convert_internal_cust_location(
2506 p_internal_cust_location_id IN NUMBER)
2507 RETURN NUMBER
2508 IS
2509 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONVERT_INTERNAL_CUST_LOCATION[function]';
2510 --
2511 l_debug_on BOOLEAN;
2512 --
2513 l_internal_org_location_id NUMBER;
2514 l_rs VARCHAR2(1);
2515 BEGIN
2516 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2517 --
2518 IF l_debug_on IS NULL THEN
2519 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2520 END IF;
2521
2522 IF l_debug_on THEN
2523 WSH_DEBUG_SV.push(l_module_name);
2524 END IF;
2525 --
2526 --
2527
2528 convert_internal_cust_location(
2529 p_internal_cust_location_id => p_internal_cust_location_id,
2530 x_internal_org_location_id => l_internal_org_location_id,
2531 x_return_status => l_rs
2532 );
2533
2534 IF (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
2535 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2536 l_internal_org_location_id := NULL;
2537 END IF;
2538
2539 --
2540 --
2541 IF l_debug_on THEN
2542 WSH_DEBUG_SV.log(l_module_name, 'returning', l_internal_org_location_id);
2543 WSH_DEBUG_SV.pop(l_module_name);
2544 END IF;
2545
2546 RETURN l_internal_org_location_id;
2547
2548 EXCEPTION
2549 WHEN OTHERS THEN
2550 WSH_UTIL_CORE.DEFAULT_HANDLER(
2551 'WSH_LOCATIONS_PKG.convert_internal_cust_location',
2552 l_module_name);
2553 IF l_debug_on THEN
2554 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2555 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2556 END IF;
2557 RETURN NULL;
2558
2559 END convert_internal_cust_location;
2560
2561
2562
2563 END WSH_LOCATIONS_PKG;