[Home] [Help]
PACKAGE BODY: APPS.WSH_LOCATIONS_PKG
Source
1 PACKAGE BODY WSH_LOCATIONS_PKG as
2 /* $Header: WSHLOCMB.pls 120.12.12000000.2 2007/10/09 13:15:56 arimukhe 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
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 --
1050 -- Debug Statements
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
1327 WSH_FACILITIES_INTEGRATION.Create_Facilities(p_location_ids => pUpdateLocationIdTbl,
1328 p_company_names => pUpdateOwnerNameTbl,
1329 p_site_names => pUpdateLocCodeTbl,
1330 x_return_status => x_return_status,
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,
1454 p_cityTbl => pInsertCityTbl,
1455 p_postal_codeTbl => pInsertPostalCodeTbl,
1456 p_party_site_numberTbl => l_dummytbl,
1457 p_location_codeTbl => pInsertLocCodeTbl,
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
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_relationships r, po_vendors v
1982 WHERE r.relationship_type = 'POS_VENDOR_PARTY' AND
1983 r.subject_id = v.vendor_id AND
1984 r.object_id = l_party_id;
1985
1986 cnt NUMBER;
1987 l_owner_type NUMBER;
1988 l_party_id NUMBER;
1989 l_owner_id NUMBER;
1990 l_organization_id NUMBER;
1991
1992 l_dup_pk NUMBER := 0;
1993 l_return_status VARCHAR2(1) := NULL;
1994 --
1995 l_debug_on BOOLEAN;
1996 --
1997 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LOCATION_OWNER';
1998 --
1999 BEGIN
2000
2001 --
2002 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2003 --
2004 IF l_debug_on IS NULL
2005 THEN
2006 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2007 END IF;
2008 --
2009 --
2010 -- Debug Statements
2011 --
2012 IF l_debug_on THEN
2013 WSH_DEBUG_SV.push(l_module_name);
2014 --
2015 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
2016 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE_CODE',P_LOCATION_SOURCE_CODE);
2017 END IF;
2018 --
2019 -- Found out that this API is getting called multiple times for the same location_id
2020 IF l_debug_on THEN
2021 WSH_DEBUG_SV.logmsg(l_module_name,' getting owner for location '||p_location_id);
2022 END IF;
2023
2024 /*
2025 OPEN get_location_info(p_location_id);
2026 FETCH get_location_info INTO l_party_id, l_owner_id;
2027 CLOSE get_location_info;
2028 */
2029
2030 -- AG this check not required
2031 -- a location might already be linked to a party
2032 -- and can still get linked to a second (or third, fourth ..) party
2033 -- as long as it is not a duplicate entry
2034
2035 --IF (l_party_id IS NULL) THEN
2036
2037 cnt := pLocOwnerIdTbl.count;
2038
2039 IF (p_location_source_code = 'HR') THEN
2040
2041 OPEN get_org_info(p_location_id);
2042 LOOP
2043 FETCH get_org_info INTO l_organization_id;
2044 EXIT WHEN get_org_info%NOTFOUND;
2045
2046 -- AG check duplicate record
2047
2048 check_duplicate_rec
2049 ( p_wsh_location_id => p_location_id,
2050 p_owner_party_id => l_organization_id,
2051 p_owner_type => 1,
2052 x_dup_rec_pk => l_dup_pk,
2053 x_return_status => l_return_status);
2054
2055 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2056
2057 IF (l_dup_pk = -1) THEN
2058 -- Duplicate row does not exist.
2059 IF l_debug_on THEN
2060 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record does not exist for location '||p_location_id|| ' and organization id '||l_organization_id);
2061 END IF;
2062
2063 cnt := cnt + 1;
2064 pLocLocationIdTbl(cnt) := p_location_id;
2065 pLocOwnerIdTbl(cnt) := l_organization_id;
2066 pLocOwnerTypeTbl(cnt) := 1;
2067
2068 ELSE
2069 -- duplicate row exists
2070 IF l_debug_on THEN
2071 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record exists for location '||p_location_id|| ' and organization id '||l_organization_id);
2072 END IF;
2073 END IF;
2074 END IF;
2075
2076 END LOOP;
2077
2078 IF get_org_info%ROWCOUNT = 0 THEN
2079
2080 -- AG check duplicate record
2081
2082 check_duplicate_rec
2083 ( p_wsh_location_id => p_location_id,
2084 p_owner_party_id => -1,
2085 p_owner_type => 1,
2086 x_dup_rec_pk => l_dup_pk,
2087 x_return_status => l_return_status);
2088
2089 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2090
2091 IF (l_dup_pk = -1) THEN
2092 -- Duplicate row does not exist.
2093 IF l_debug_on THEN
2094 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record does not exist for location '||p_location_id|| ' and organization id -1 ');
2095 END IF;
2096
2097 cnt := cnt + 1;
2098 pLocLocationIdTbl(cnt) := p_location_id;
2099 pLocOwnerIdTbl(cnt) := -1;
2100 pLocOwnerTypeTbl(cnt) := 1;
2101
2102 ELSE
2103 -- duplicate row exists
2104 IF l_debug_on THEN
2105 WSH_DEBUG_SV.logmsg(l_module_name,' location owner record exists for location '||p_location_id|| ' and organization id -1 ');
2106 END IF;
2107 END IF;
2108 END IF;
2109
2110
2111 END IF;
2112 CLOSE get_org_info;
2113
2114 ELSE
2115
2116 FOR parties IN get_party_info(p_location_id) LOOP
2117
2118 /*
2119 -- check duplicate record
2120
2121 cnt := cnt + 1;
2122
2123 pLocLocationIdTbl(cnt) := p_location_id;
2124 pLocOwnerIdTbl(cnt) := parties.party_id;
2125
2126 */
2127 l_owner_type := 2;
2128
2129 OPEN check_party_carrier_supplier(parties.party_id);
2130 FETCH check_party_carrier_supplier INTO l_owner_type;
2131 CLOSE check_party_carrier_supplier;
2132
2133 -- If party is carrier OR supplier,
2134 -- l_owner_type will be 3 OR 4 ( <> 2)
2135 -- Otherwise, value of l_owner_type will not change
2136
2137 --
2138 IF l_debug_on THEN
2139 WSH_DEBUG_SV.log(l_module_name,'l_owner_type after check_party_carrier_supplier : ',l_owner_type);
2140 END IF;
2141 --
2142
2143 IF (l_owner_type IS NULL) THEN
2144 l_owner_type := 2;
2145 END IF;
2146
2147 -- AG check duplicate record
2148
2149 check_duplicate_rec
2150 ( p_wsh_location_id => p_location_id,
2151 p_owner_party_id => parties.party_id,
2152 p_owner_type => l_owner_type,
2153 x_dup_rec_pk => l_dup_pk,
2154 x_return_status => l_return_status);
2155
2156 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2157
2158 IF (l_dup_pk = -1) THEN
2159 -- Duplicate row does not exist.
2160 IF l_debug_on THEN
2161 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 );
2162 END IF;
2163
2164 cnt := cnt + 1;
2165 pLocLocationIdTbl(cnt) := p_location_id;
2166 pLocOwnerIdTbl(cnt) := parties.party_id;
2167 pLocOwnerTypeTbl(cnt) := l_owner_type;
2168
2169 ELSE
2170 -- duplicate row exists
2171 IF l_debug_on THEN
2172 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 );
2173 END IF;
2174 END IF;
2175 END IF;
2176
2177 END LOOP;
2178
2179 END IF;
2180
2181 --END IF;
2182
2183 --
2184 -- Debug Statements
2185 --
2186 IF l_debug_on THEN
2187 WSH_DEBUG_SV.pop(l_module_name);
2188 END IF;
2189 --
2190 END get_location_owner;
2191
2192
2193
2194 PROCEDURE insert_location_owners(pLocationIdTbl IN ID_Tbl_Type,
2195 p_location_source_code IN VARCHAR2,
2196 x_return_status OUT NOCOPY VARCHAR2)
2197 IS
2198 i NUMBER;
2199 j NUMBER;
2200 cnt NUMBER;
2201 l_start NUMBER;
2202 l_error_code NUMBER;
2203 l_loc_id NUMBER;
2204 --
2205 l_debug_on BOOLEAN;
2206 --
2207 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_LOCATION_OWNERS';
2208 --
2209 BEGIN
2210 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2211 --
2212 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2213 --
2214 IF l_debug_on IS NULL
2215 THEN
2216 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2217 END IF;
2218 --
2219 --
2220 -- Debug Statements
2221 --
2222 IF l_debug_on THEN
2223 WSH_DEBUG_SV.push(l_module_name);
2224 --
2225 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_SOURCE_CODE',P_LOCATION_SOURCE_CODE);
2226 END IF;
2227 --
2228 pLocLocationIdTbl.delete;
2229 pLocOwnerIdTbl.delete;
2230 pLocOwnerTypeTbl.delete;
2231 IF l_debug_on THEN
2232 WSH_DEBUG_SV.logmsg(l_module_name,' Number of locations being processed '||pLocationIdTbl.count);
2233 END IF;
2234 FOR cnt IN 1..pLocationIdTbl.count LOOP
2235 get_location_owner(pLocationIdTbl(cnt),p_location_source_code);
2236 END LOOP;
2237 IF l_debug_on THEN
2238 WSH_DEBUG_SV.logmsg(l_module_name,' Inserting owner table count '||pLocLocationIdTbl.count);
2239 END IF;
2240 IF (pLocLocationIdTbl.count > 0) THEN
2241
2242 BEGIN
2243 l_start := pLocLocationIdTbl.FIRST;
2244 LOOP
2245 BEGIN
2246 FORALL i in l_start..pLocLocationIdTbl.LAST
2247 --Primary key - location_owner_id
2248 Insert into WSH_LOCATION_OWNERS
2249 ( LOCATION_OWNER_ID,
2250 WSH_LOCATION_ID,
2251 OWNER_PARTY_ID,
2252 OWNER_TYPE,
2253 CREATION_DATE,
2254 CREATED_BY,
2255 LAST_UPDATE_DATE,
2256 LAST_UPDATED_BY,
2257 LAST_UPDATE_LOGIN)
2258 Values (
2259 wsh_location_owners_s.nextval,
2260 pLocLocationIdTbl(i),
2261 pLocOwnerIdTbl(i),
2262 pLocOwnerTypeTbl(i),
2263 SYSDATE,
2264 1,
2265 SYSDATE,
2266 1,
2267 1);
2268 EXIT;
2269 EXCEPTION
2270 WHEN OTHERS THEN
2271 l_error_code := SQLCODE;
2272 l_loc_id := pLocLocationIdTbl(l_start + sql%rowcount);
2273 --ORA:00001 is the unique constraint violation. We are attempting
2274 --to insert a facility that already exists.
2275 IF ( l_error_code = -1 ) THEN
2276 IF l_debug_on THEN
2277 WSH_DEBUG_SV.logmsg(l_module_name,'Duplicate owner_party_id found for location_id '||l_loc_id);
2278 END IF;
2279 l_start := l_start + sql%rowcount + 1;
2280 ELSE
2281 IF l_debug_on THEN
2282 WSH_DEBUG_SV.logmsg(l_module_name,'UNEXPECTED ERROR WHILE CREATING LOCATION OWNER FOR LOCATION '||l_loc_id);
2283 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR MESSAGE '||SQLERRM);
2284 END IF;
2285 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2286 RETURN;
2287 END IF;
2288 END;
2289 END LOOP;
2290 END;
2291 END IF;
2292
2293 --
2294 -- Debug Statements
2295 --
2296 IF l_debug_on THEN
2297 WSH_DEBUG_SV.pop(l_module_name);
2298 END IF;
2299 --
2300 END insert_location_owners;
2301
2302
2303
2304 -----------------------------------------------------------------------------------
2305 -- Start of comments
2306 -- API name : Create_Geometry
2307 -- Type : Public
2308 -- Pre-reqs : None.
2309 -- Function : Create a MDSYS.SDO_GEOMETRY Object from a longitude and latitude.
2310 --
2311 -- Parameters :
2312 -- p_longitude IN NUMBER The Longitude.
2313 -- p_latitude IN NUMBER The Latitude.
2314 --
2315 -- x_geometry OUT MDSYS.SDO_GEOMETRY The geometry object created.
2316 -- x_status OUT VARCHAR2
2317 -- x_error_msg OUT VARCHAR2
2318 --
2319 -- Version : 1.0
2320 -- Previous version 1.0
2321 -- Initial version 1.0
2322 -- End of comments
2323 -----------------------------------------------------------------------------------
2324 PROCEDURE Create_Geometry (p_longitude IN NUMBER,
2325 p_latitude IN NUMBER,
2326 x_geometry OUT NOCOPY MDSYS.SDO_GEOMETRY,
2327 x_return_status OUT NOCOPY VARCHAR2,
2328 x_error_msg OUT NOCOPY VARCHAR2 ) IS
2329
2330 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_GEOMETRY';
2331 l_debug_on BOOLEAN;
2332 BEGIN
2333 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2334 IF l_debug_on IS NULL THEN
2335 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2336 END IF;
2337
2338 IF l_debug_on THEN
2339 WSH_DEBUG_SV.push(l_module_name);
2340 END IF;
2341
2342 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2343
2344 x_geometry := MDSYS.SDO_GEOMETRY(2001, --SDO_GTYPE: Geometry type.
2345 -- 2 indicates two-dimensional, 1 indicates a single point
2346 8307, --SDO_SRID: 8307 is SRID for 'Long/Lat (WGS 84)' coordinate system
2347 MDSYS.SDO_POINT_TYPE(p_longitude, p_latitude, NULL),
2348 NULL, -- SDO_ELEM_INFO: Not needed if point_type
2349 NULL); -- SDO_ORDINATES: Not needed if point_type
2350
2351 IF l_debug_on THEN
2352 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status is : '||x_return_status);
2353 WSH_DEBUG_SV.pop(l_module_name);
2354 END IF;
2355
2356 EXCEPTION
2357 WHEN OTHERS THEN
2358 x_error_msg := 'ERROR: During Geometry Creation ' || sqlerrm;
2359 IF l_debug_on THEN
2360 WSH_DEBUG_SV.LogMsg(l_module_name, x_error_msg);
2361 WSH_DEBUG_SV.pop(l_module_name);
2362 END IF;
2363 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2364
2365 END Create_Geometry;
2366
2367
2368 --
2369 -- Procedure: convert_internal_cust_location
2370 -- Parameters:
2371 -- p_location_id location to convert
2372 -- x_internal_org_location_id physical location id
2373 -- if not NULL, it is converted.
2374 -- if NULL, the location is not
2375 -- an internal customer location.
2376 -- x_return_status return status
2377 --
2378 -- Description:
2379 -- Attempt to convert the putative internal customer
2380 -- location into the internal organization location.
2381 -- If it is internal customer location, the API will
2382 -- populate x_internal_org_location_id as a physical
2383 -- location.
2384 -- Otherwise, x_internal_org_location_id will be NULL.
2385 --
2386 --
2387 -- Bug#5307873:new input parameter p_customer_id has been added.
2388 PROCEDURE Convert_internal_cust_location(
2389 p_internal_cust_location_id IN NUMBER,
2390 p_customer_id IN NUMBER DEFAULT NULL,
2391 x_internal_org_location_id OUT NOCOPY NUMBER,
2392 x_return_status OUT NOCOPY VARCHAR2)
2393 IS
2394 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONVERT_INTERNAL_CUST_LOCATION';
2395 --
2396 l_debug_on BOOLEAN;
2397 --
2398 CURSOR c_convert(p_int_cust_loc_id NUMBER,p_customer_id NUMBER) IS
2399 SELECT ploc.LOCATION_ID internal_org_location_id
2400 FROM PO_LOCATION_ASSOCIATIONS_ALL ploc,
2401 hz_cust_site_uses_all site_uses,
2402 hz_cust_acct_sites_all acct_sites,
2403 HZ_PARTY_SITES sites
2404 WHERE ploc.SITE_USE_ID = site_uses.SITE_USE_ID
2405 AND site_uses.CUST_ACCT_SITE_ID = acct_sites.CUST_ACCT_SITE_ID
2406 AND acct_sites.PARTY_SITE_ID = sites.PARTY_SITE_ID
2407 AND ploc.CUSTOMER_ID = acct_sites.CUST_ACCOUNT_ID
2408 AND sites.location_id = p_int_cust_loc_id
2409 AND sites.location_id = p_int_cust_loc_id
2410 AND ploc.CUSTOMER_ID = nvl(p_customer_id,ploc.CUSTOMER_ID);
2411 --
2412 l_cache_rec Generic_Cache_Rec_Typ;
2413 l_index NUMBER;
2414 l_rs VARCHAR2(1);
2415 l_flag VARCHAR2(1);
2416
2417 BEGIN
2418 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2419 --
2420 IF l_debug_on IS NULL THEN
2421 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2422 END IF;
2423
2424 IF l_debug_on THEN
2425 WSH_DEBUG_SV.push(l_module_name);
2426 END IF;
2427 --
2428 --
2429 l_cache_rec.input_param1 := to_char(p_internal_cust_location_id)||'-'||to_char(p_customer_id);
2430
2431 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2432
2433 get_table_index(
2434 p_validate_rec => l_cache_rec,
2435 p_generic_tab => g_int_loc_cache,
2436 x_index => l_index,
2437 x_return_status => l_rs,
2438 x_flag => l_flag);
2439
2440 IF (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
2441 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2442 l_flag := NULL;
2443 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2444 END IF;
2445
2446 IF l_flag = 'U' THEN
2447 OPEN c_convert(p_internal_cust_location_id,p_customer_id);
2448 FETCH c_convert INTO l_cache_rec.output_param1;
2449 IF c_convert%NOTFOUND THEN
2450 l_cache_rec.output_param1 := NULL;
2451 END IF;
2452 CLOSE c_convert;
2453 g_int_loc_cache(l_index) := l_cache_rec;
2454 END IF;
2455
2456 IF g_int_loc_cache.EXISTS(l_index) THEN
2457 x_internal_org_location_id := g_int_loc_cache(l_index).output_param1;
2458 ELSE
2459 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2460 x_internal_org_location_id := NULL;
2461 END IF;
2462
2463 --
2464 --
2465 IF l_debug_on THEN
2466 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2467 WSH_DEBUG_SV.pop(l_module_name);
2468 END IF;
2469
2470 EXCEPTION
2471 WHEN OTHERS THEN
2472 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2473 IF c_convert%ISOPEN THEN
2474 CLOSE c_convert;
2475 END IF;
2476 WSH_UTIL_CORE.DEFAULT_HANDLER(
2477 'WSH_LOCATIONS_PKG.convert_internal_cust_location',
2478 l_module_name);
2479
2480 IF l_debug_on THEN
2481 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2482 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2483 END IF;
2484
2485 END convert_internal_cust_location;
2486
2487
2488
2489 --
2490 -- Function: convert_internal_cust_location
2491 -- Parameters:
2492 -- p_location_id location to convert
2493 --
2494 -- Return:
2495 -- internal organizatoin location ID
2496 -- NULL if not converted or no mapping exists.
2497 -- NOT NULL if successfully converted and mapping exists.
2498 -- Description:
2499 -- Wrapper function for procedure convert_internal_cust_location
2500 --
2501 --
2502 FUNCTION Convert_internal_cust_location(
2503 p_internal_cust_location_id IN NUMBER)
2504 RETURN NUMBER
2505 IS
2506 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONVERT_INTERNAL_CUST_LOCATION[function]';
2507 --
2508 l_debug_on BOOLEAN;
2509 --
2510 l_internal_org_location_id NUMBER;
2511 l_rs VARCHAR2(1);
2512 BEGIN
2513 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2514 --
2515 IF l_debug_on IS NULL THEN
2516 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2517 END IF;
2518
2519 IF l_debug_on THEN
2520 WSH_DEBUG_SV.push(l_module_name);
2521 END IF;
2522 --
2523 --
2524
2525 convert_internal_cust_location(
2526 p_internal_cust_location_id => p_internal_cust_location_id,
2527 x_internal_org_location_id => l_internal_org_location_id,
2528 x_return_status => l_rs
2529 );
2530
2531 IF (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
2532 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2533 l_internal_org_location_id := NULL;
2534 END IF;
2535
2536 --
2537 --
2538 IF l_debug_on THEN
2539 WSH_DEBUG_SV.log(l_module_name, 'returning', l_internal_org_location_id);
2540 WSH_DEBUG_SV.pop(l_module_name);
2541 END IF;
2542
2543 RETURN l_internal_org_location_id;
2544
2545 EXCEPTION
2546 WHEN OTHERS THEN
2547 WSH_UTIL_CORE.DEFAULT_HANDLER(
2548 'WSH_LOCATIONS_PKG.convert_internal_cust_location',
2549 l_module_name);
2550 IF l_debug_on THEN
2551 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2552 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2553 END IF;
2554 RETURN NULL;
2555
2556 END convert_internal_cust_location;
2557
2558
2559
2560 END WSH_LOCATIONS_PKG;