[Home] [Help]
PACKAGE BODY: APPS.WSH_CARRIER_ADDRESS_PKG
Source
1 PACKAGE BODY WSH_CARRIER_ADDRESS_PKG as
2 /* $Header: WSHADTHB.pls 120.1 2005/10/28 01:12:24 skattama noship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CARRIER_ADDRESS_PKG';
6 --
7
8 /*--------------------------------------------------------------
9 PROCEDURE : Create_AddressInfo
10 PURPOSE : This procedure creates a location, party site and
11 and party site use.
12 ---------------------------------------------------------------*/
13
14 PROCEDURE Create_Addressinfo (
15 p_carrier_id IN NUMBER ,
16 p_status IN VARCHAR2 ,
17 p_site_NUMBER IN OUT NOCOPY VARCHAR2 ,
18 p_address1 IN VARCHAR2 ,
19 p_address2 IN VARCHAR2 ,
20 p_address3 IN VARCHAR2 ,
21 p_address4 IN VARCHAR2 ,
22 p_city IN VARCHAR2 ,
23 p_state IN VARCHAR2 ,
24 p_province IN VARCHAR2 ,
25 p_postal_code IN VARCHAR2 ,
26 p_country IN VARCHAR2 ,
27 p_county IN VARCHAR2 ,
28 x_location_id IN OUT NOCOPY NUMBER ,
29 x_party_site_id IN OUT NOCOPY NUMBER ,
30 x_return_status OUT NOCOPY VARCHAR2 ,
31 x_exception_msg OUT NOCOPY VARCHAR2 ,
32 x_position OUT NOCOPY NUMBER ,
33 x_procedure OUT NOCOPY VARCHAR2 ,
34 x_sqlerr OUT NOCOPY VARCHAR2 ,
35 x_sql_code OUT NOCOPY VARCHAR2 ) IS
36
37 -- General Declarations.
38 l_return_status VARCHAR2(100);
39 l_msg_count NUMBER;
40 l_msg_data VARCHAR2(2000);
41 l_party_NUMBER VARCHAR2(100);
42 l_profile_id NUMBER;
43 l_exception_msg VARCHAR2(1000);
44 HZ_FAIL_EXCEPTION exception;
48 -- Declarations for 'GENERAL_MAIL_TO' type site.
45 l_position NUMBER;
46 l_procedure VARCHAR2(100);
47
49 l_address_party_site_id NUMBER;
50 l_site_use_type_id NUMBER;
51 l_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
52 l_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
53 l_party_site_id NUMBER;
54 l_party_site_use_id NUMBER;
55 l_party_site_NUMBER VARCHAR2(100);
56
57 -- Declarations for Location Creation.
58 l_loc_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
59 l_loc_id NUMBER;
60
61 --
62 l_debug_on BOOLEAN;
63 --
64 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_ADDRESSINFO';
65 --
66
67 BEGIN
68
69 -- Initialize the status to SUCCESS.
70
71 --
72 -- Debug Statements
73 --
74 --
75 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
76 --
77 IF l_debug_on IS NULL
78 THEN
79 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
80 END IF;
81 --
82 IF l_debug_on THEN
83 WSH_DEBUG_SV.push(l_module_name);
84 --
85 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
86 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
87 WSH_DEBUG_SV.log(l_module_name,'P_SITE_NUMBER',P_SITE_NUMBER);
88 WSH_DEBUG_SV.log(l_module_name,'P_ADDRESS1',P_ADDRESS1);
89 WSH_DEBUG_SV.log(l_module_name,'P_ADDRESS2',P_ADDRESS2);
90 WSH_DEBUG_SV.log(l_module_name,'P_ADDRESS3',P_ADDRESS3);
91 WSH_DEBUG_SV.log(l_module_name,'P_ADDRESS4',P_ADDRESS4);
92 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
93 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
94 WSH_DEBUG_SV.log(l_module_name,'P_PROVINCE',P_PROVINCE);
95 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
96 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
97 WSH_DEBUG_SV.log(l_module_name,'P_COUNTY',P_COUNTY);
98 END IF;
99 --
100 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
101
102 -- Initialize Messages.
103 fnd_msg_pub.initialize();
104
105 -- Put information into l_loc_rec.
106 l_loc_rec.address1 := p_address1;
107 l_loc_rec.address2 := p_address2;
108 l_loc_rec.address3 := p_address3;
109 l_loc_rec.address4 := p_address4;
110 l_loc_rec.city := substr(p_city,0,60);
111 l_loc_rec.state := p_state;
112 l_loc_rec.postal_code := p_postal_code;
113 l_loc_rec.province := substr(p_province,0,60);
114 l_loc_rec.country := substr(p_country,0,60);
115 l_loc_rec.county := substr(p_county,0,60);
116 l_loc_rec.created_by_module := 'ORACLE_SHIPPING';
117
118 -- Create Location.
119
120 l_position := 10;
121 l_procedure := 'Calling TCA API Create_Location';
122
123 --
124 -- Debug Statements
125 --
126 IF l_debug_on THEN
127 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_LOCATION_V2PUB.CREATE_LOCATION',WSH_DEBUG_SV.C_PROC_LEVEL);
128 END IF;
129 --
130
131 HZ_LOCATION_V2PUB.Create_Location
132 (
133 p_init_msg_list => FND_API.G_TRUE,
134 p_location_rec => l_loc_rec,
135 x_location_id => l_loc_id,
136 x_return_status => l_return_status,
137 x_msg_count => l_msg_count,
138 x_msg_data => l_msg_data
139 );
140
141 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
142 x_return_status := l_return_status;
143 RAISE HZ_FAIL_EXCEPTION;
144 END IF;
145
146 -- Get the Location ID.
147 x_location_id := l_loc_id;
148
149 ---------------------------------------------------
150 -- Create the Site Information by calling the
151 -- TCA API: HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE.
152 ---------------------------------------------------
153
154 -- Put information into l_site_rec.
155
156 l_site_rec.party_id := p_carrier_id;
157 l_site_rec.location_id := l_loc_id;
158 l_site_rec.status := p_status;
159 l_site_rec.party_site_NUMBER := p_site_NUMBER;
160 l_site_rec.created_by_module := 'ORACLE_SHIPPING';
161
162
163 -- Create the Party Site.
164
165 l_position := 20;
166 l_procedure := 'Calling TCA API Create_Location';
167
168 --
169 -- Debug Statements
170 --
171 IF l_debug_on THEN
172 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_SITE_V2PUB.Create_Party_Site',WSH_DEBUG_SV.C_PROC_LEVEL);
173 END IF;
174 --
175
176 HZ_PARTY_SITE_V2PUB.Create_Party_Site
177 (
178 p_init_msg_list => FND_API.G_TRUE,
179 p_party_site_rec => l_site_rec,
180 x_party_site_id => l_party_site_id,
181 x_party_site_NUMBER => l_party_site_NUMBER,
182 x_return_status => l_return_status,
183 x_msg_count => l_msg_count,
184 x_msg_data => l_msg_data
185 );
186
187 p_site_number := l_party_site_NUMBER;
188
192 END IF;
189 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
190 x_return_status := l_return_status;
191 RAISE HZ_FAIL_EXCEPTION;
193
194 -- Get the Party Site ID.
195
196 x_party_site_id := l_party_site_id;
197
198
199 --------------------------------------------------------
200 -- Create the Party Site Use Information by calling the
201 -- TCA API: hz_party_site_v2pub.create_party_site_use.
202 --------------------------------------------------------
203
204 -- Put the information into site_rec.
205 -- l_site_use_rec.begin_date := trunc(SYSDATE);
206 l_site_use_rec.site_use_type := 'GENERAL_MAIL_TO';
207 l_site_use_rec.party_site_id := l_party_site_id;
208 l_site_use_rec.created_by_module := 'ORACLE_SHIPPING';
209
210 -- Create a Party site use.
211
212 --
213 -- Debug Statements
214 --
215 IF l_debug_on THEN
216 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_SITE_V2PUB.Create_Party_Site_Use',WSH_DEBUG_SV.C_PROC_LEVEL);
217 END IF;
218 --
219
220 HZ_PARTY_SITE_V2PUB.Create_Party_Site_Use
221 (
222 p_init_msg_list => FND_API.G_TRUE,
223 p_party_site_use_rec => l_site_use_rec,
224 x_party_site_use_id => l_party_site_use_id,
225 x_return_status => l_return_status,
226 x_msg_count => l_msg_count,
227 x_msg_data => l_msg_data
228 );
229
230 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
231 x_return_status := l_return_status;
232 RAISE HZ_FAIL_EXCEPTION;
233 END IF;
234
235 --
236 -- Debug Statements
237 --
238 IF l_debug_on THEN
239 WSH_DEBUG_SV.pop(l_module_name);
240 END IF;
241 --
242
243 EXCEPTION
244
245 WHEN HZ_FAIL_EXCEPTION THEN
246 x_exception_msg := l_msg_data;
247 x_position := l_position;
248 x_procedure := l_procedure;
249 x_sqlerr := sqlerrm;
250 x_sql_code := sqlcode;
251
252 --
253 -- Debug Statements
254 --
255 IF l_debug_on THEN
256 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
257 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
258 END IF;
259 --
260
261 END CREATE_ADDRESSINFO;
262
263 /*--------------------------------------------------------------
264 PROCEDURE : Update_AddressInfo
265 PURPOSE : This procedure updates the location information
266 ---------------------------------------------------------------*/
267
268 PROCEDURE UPDATE_ADDRESSINFO
269 (
270 P_CARRIER_PARTY_ID IN NUMBER,
271 P_SITE_NUMBER IN VARCHAR2,
272 P_STATUS IN VARCHAR2,
273 P_PARTY_SITE_ID IN NUMBER,
274 P_LOCATION_ID IN NUMBER,
275 P_ADDRESS1 IN VARCHAR2,
276 P_ADDRESS2 IN VARCHAR2,
277 P_ADDRESS3 IN VARCHAR2,
278 P_ADDRESS4 IN VARCHAR2,
279 P_CITY IN VARCHAR2,
280 P_STATE IN VARCHAR2,
281 P_PROVINCE IN VARCHAR2,
282 P_POSTAL_CODE IN VARCHAR2,
283 P_COUNTRY IN VARCHAR2,
284 P_COUNTY IN VARCHAR2,
285 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
286 X_EXCEPTION_MSG OUT NOCOPY VARCHAR2,
287 X_POSITION OUT NOCOPY NUMBER,
288 X_PROCEDURE OUT NOCOPY VARCHAR2,
289 X_SQLERR OUT NOCOPY VARCHAR2,
290 X_SQL_CODE OUT NOCOPY VARCHAR2
291 )
292
293 is
294
295 l_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
296 l_position NUMBER;
297 l_procedure VARCHAR2(100);
298 l_loc_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
299 l_return_status VARCHAR2(100);
300 l_msg_count NUMBER;
301 l_msg_data VARCHAR2(2000);
302 l_party_id NUMBER;
303 l_party_NUMBER VARCHAR2(100);
304 l_profile_id NUMBER;
305 HZ_FAIL_EXCEPTION EXCEPTION;
306 l_location_id NUMBER;
307
308 l_site_object_NUMBER NUMBER;
309 l_location_object_NUMBER NUMBER;
310
311 CURSOR Get_Site_Object_Number(p_party_site_id NUMBER) IS
312 select object_version_NUMBER
313 from hz_party_sites
314 where party_site_id = p_party_site_id;
315
316 CURSOR Get_Location_Object_Number(p_location_id NUMBER) IS
317 select object_version_number
318 from hz_locations
319 where location_id = p_location_id;
320
321 --
322 l_debug_on BOOLEAN;
323 --
324 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ADDRESSINFO';
325 --
326
327
328 BEGIN
329
330 -- Initialize the status to SUCCESS.
331
332 --
333 -- Debug Statements
334 --
335 --
336 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
337 --
338 IF l_debug_on IS NULL
339 THEN
340 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
341 END IF;
342 --
343 IF l_debug_on THEN
344 WSH_DEBUG_SV.push(l_module_name);
345 --
349 WSH_DEBUG_SV.log(l_module_name,'P_PARTY_SITE_ID',P_PARTY_SITE_ID);
346 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_PARTY_ID',P_CARRIER_PARTY_ID);
347 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
348 WSH_DEBUG_SV.log(l_module_name,'P_SITE_NUMBER',P_SITE_NUMBER);
350 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
351 WSH_DEBUG_SV.log(l_module_name,'P_ADDRESS1',P_ADDRESS1);
352 WSH_DEBUG_SV.log(l_module_name,'P_ADDRESS2',P_ADDRESS2);
353 WSH_DEBUG_SV.log(l_module_name,'P_ADDRESS3',P_ADDRESS3);
354 WSH_DEBUG_SV.log(l_module_name,'P_ADDRESS4',P_ADDRESS4);
355 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
356 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
357 WSH_DEBUG_SV.log(l_module_name,'P_PROVINCE',P_PROVINCE);
358 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
359 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
360 WSH_DEBUG_SV.log(l_module_name,'P_COUNTY',P_COUNTY);
361 END IF;
362 --
363
364 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
365
366 -- Initialize Messages.
367 fnd_msg_pub.initialize();
368
369 -- Put Information into site_rec.
370 l_site_rec.party_site_id := p_party_site_id;
371 -- l_site_rec.party_id := p_carrier_party_id;
372 -- l_site_rec.location_id := p_location_id;
373 -- l_site_rec.party_site_number := p_site_number;
374 l_site_rec.status := p_status;
375
376 -- Get last_update_date for the Party Site.
377 OPEN Get_Site_Object_Number(p_party_site_id);
378 FETCH Get_Site_Object_Number INTO l_site_object_number;
379 CLOSE Get_Site_Object_Number;
380
381 -- Update the Party Site Information.
382
383 l_position := 10;
384 l_procedure := 'Calling TCA API Update_Party_site';
385
386 --
387 -- Debug Statements
388 --
389 IF l_debug_on THEN
390 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_SITE_V2PUB.Update_Party_Site',WSH_DEBUG_SV.C_PROC_LEVEL);
391 END IF;
392 --
393
394 HZ_PARTY_SITE_V2PUB.Update_Party_Site
395 (
396 p_init_msg_list => FND_API.G_TRUE,
397 p_party_site_rec => l_site_rec,
398 p_object_version_number => l_site_object_number,
399 x_return_status => l_return_status,
400 x_msg_count => l_msg_count,
401 x_msg_data => l_msg_data
402 );
403
404 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
405 x_return_status := l_return_status;
406 RAISE HZ_FAIL_EXCEPTION;
407 END IF;
408
409 -- Put Information into loc_rec.
410 l_loc_rec.location_id := p_location_id;
411 l_loc_rec.country := substr(p_country,0,60);
412 l_loc_rec.county := nvl(substr(p_county,0,60), fnd_api.g_miss_char);
413 l_loc_rec.address1 := p_address1;
414 l_loc_rec.address2 := nvl(p_address2, fnd_api.g_miss_char);
415 l_loc_rec.address3 := nvl(p_address3, fnd_api.g_miss_char);
416 L_loc_rec.address4 := nvl(p_address4, fnd_api.g_miss_char);
417 l_loc_rec.city := nvl(substr(p_city,0,60), fnd_api.g_miss_char);
418 l_loc_rec.state := nvl(p_state, fnd_api.g_miss_char);
419 l_loc_rec.postal_code := nvl(p_postal_code, fnd_api.g_miss_char);
420 l_loc_rec.province := nvl(substr(p_province,0,60), fnd_api.g_miss_char);
421
422 -- Get last_update_date for the Location.
423
424 OPEN Get_Location_Object_Number(p_location_id);
425 FETCH Get_Location_Object_Number INTO l_location_object_number;
426 CLOSE Get_Location_Object_Number;
427
428 -- Update the Location Information.
429 l_position := 20;
430 l_procedure := 'Calling TCA API Update_Location';
431
432 --
433 -- Debug Statements
434 --
435 IF l_debug_on THEN
436 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_LOCATION_V2PUB.Update_Location',WSH_DEBUG_SV.C_PROC_LEVEL);
437 END IF;
438 --
439
440 HZ_LOCATION_V2PUB.Update_Location
441 (
442 p_init_msg_list => FND_API.G_TRUE,
443 p_location_rec => l_loc_rec,
444 p_object_version_number => l_location_object_number,
445 x_return_status => l_return_status,
446 x_msg_count => l_msg_count,
447 x_msg_data => l_msg_data
448 );
449
450
451 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
452 x_return_status := l_return_status;
453 RAISE HZ_FAIL_EXCEPTION;
454 END IF;
455
456 --
457 -- Debug Statements
458 --
459 IF l_debug_on THEN
460 WSH_DEBUG_SV.pop(l_module_name);
461 END IF;
462 --
463
464 EXCEPTION
465 WHEN NO_DATA_FOUND THEN
466 x_exception_msg := 'EXCEPTION : No Data Found';
467 x_position := l_position;
468 x_procedure := l_procedure;
469 x_sqlerr := sqlerrm;
470 x_sql_code := sqlcode;
471 x_return_status := 'E';
472 --
473 -- Debug Statements
474 --
475 IF l_debug_on THEN
476 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
477 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
478 END IF;
482 WHEN HZ_FAIL_EXCEPTION THEN
479 --
480
481
483 x_exception_msg := l_msg_data;
484 x_position := l_position;
485 x_procedure := l_procedure;
486 x_sqlerr := sqlerrm;
487 x_sql_code := sqlcode;
488
489 --
490 -- Debug Statements
491 --
492 IF l_debug_on THEN
493 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
494 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
495 END IF;
496 --
497
498 WHEN OTHERS THEN
499 x_exception_msg := 'EXCEPTION : Others';
500 x_position := l_position;
501 x_procedure := l_procedure;
502 x_sqlerr := sqlerrm;
503 x_sql_code := sqlcode;
504 x_return_status := 'E';
505
506 --
507 -- Debug Statements
508 --
509 IF l_debug_on THEN
510 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
511 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
512 END IF;
513 --
514
515 END UPDATE_ADDRESSINFO;
516
517 /*--------------------------------------------------------------
518 PROCEDURE : Concatenate_Address
519 PURPOSE : This procedure concatenates the various address
520 components.
521 ---------------------------------------------------------------*/
522
523 FUNCTION Concatenate_Address(
524 p_address1 IN VARCHAR2,
525 p_address2 IN VARCHAR2,
526 p_address3 IN VARCHAR2,
527 p_address4 IN VARCHAR2,
528 p_city IN VARCHAR2,
529 p_postal_code IN VARCHAR2,
530 p_state IN VARCHAR2,
531 p_province IN VARCHAR2,
532 p_country IN VARCHAR2,
533 p_county IN VARCHAR2 ) return VARCHAR2 IS
534
535 l_address VARCHAR2(1500);
536
537 BEGIN
538
539 --
540 l_address := p_address1;
541
542 IF ( p_address2 IS NOT NULL ) THEN
543 l_address := l_address || ', ' || p_address2;
544 END IF;
545
546 IF ( p_address3 IS NOT NULL ) THEN
547 l_address := l_address || ', ' || p_address3;
548 END IF;
549
550 IF ( p_address4 IS NOT NULL ) THEN
551 l_address := l_address || ', ' || p_address4;
552 END IF;
553
554 IF ( p_city IS NOT NULL ) THEN
555 l_address := l_address || ', ' || p_city;
556 END IF;
557
558 IF ( p_county IS NOT NULL ) THEN
559 l_address := l_address || ', ' || p_county;
560 END IF;
561
562 IF ( p_state IS NOT NULL ) THEN
563 l_address := l_address || ', ' || p_state;
564 END IF;
565
566 IF ( p_province IS NOT NULL ) THEN
567 l_address := l_address || ', ' || p_province;
568 END IF;
569
570 IF ( p_postal_code IS NOT NULL ) THEN
571 l_address := l_address || ', ' || p_postal_code;
572 END IF;
573
574 IF ( p_country IS NOT NULL ) THEN
575 l_address := l_address || ', ' || p_country;
576 END IF;
577
578 RETURN( l_address );
579
580 END;
581
582 END WSH_CARRIER_ADDRESS_PKG;