[Home] [Help]
PACKAGE BODY: APPS.IBE_ADDRESS_V2PVT
Source
1 PACKAGE BODY IBE_ADDRESS_V2PVT AS
2 /* $Header: IBEVADB.pls 120.9 2006/01/09 00:59:56 banatara ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_ADDRESS_V2PVT';
6 l_true VARCHAR2(1) := FND_API.G_TRUE;
7
8
9 ----------------- private procedures -----------------------------------------
10
11 PROCEDURE do_create_address(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_location IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
16 p_party_site IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
17 p_primary_billto IN VARCHAR2 := FND_API.G_FALSE,
18 p_primary_shipto IN VARCHAR2 := FND_API.G_FALSE,
19 p_billto IN VARCHAR2 := FND_API.G_FALSE,
20 p_shipto IN VARCHAR2 := FND_API.G_FALSE,
21 p_default_primary IN VARCHAR2,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25 x_location_id OUT NOCOPY NUMBER,
26 x_party_site_id OUT NOCOPY NUMBER
27 );
28
29 PROCEDURE do_delete_address(
30 p_api_version IN NUMBER,
31 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
32 p_commit IN VARCHAR2 := FND_API.G_FALSE,
33 p_party_id IN NUMBER,
34 p_party_site_id IN NUMBER,
35 p_ps_object_version_number IN NUMBER,
36 p_bill_object_version_number IN NUMBER,
37 p_ship_object_version_number IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2);
41
42 FUNCTION is_location_changed(
43 p_location IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
44 ) RETURN BOOLEAN;
45
46 FUNCTION is_party_site_changed(
47 p_party_site IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
48 ) RETURN BOOLEAN;
49
50 FUNCTION is_party_site_use_changed(
51 p_party_site_use IN OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE
52 ) RETURN VARCHAR;
53
54 -----------------------public procedures -------------------------------------
55
56 PROCEDURE create_address(
57 p_api_version IN NUMBER,
58 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
59 p_commit IN VARCHAR2 := FND_API.G_FALSE,
60 p_location IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
61 p_party_site IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
62 p_primary_billto IN VARCHAR2 := FND_API.G_FALSE,
63 p_primary_shipto IN VARCHAR2 := FND_API.G_FALSE,
64 p_billto IN VARCHAR2 := FND_API.G_FALSE,
65 p_shipto IN VARCHAR2 := FND_API.G_FALSE,
66 p_default_primary IN VARCHAR2 := FND_API.G_TRUE,
67 x_return_status OUT NOCOPY VARCHAR2,
68 x_msg_count OUT NOCOPY NUMBER,
69 x_msg_data OUT NOCOPY VARCHAR2,
70 x_location_id OUT NOCOPY NUMBER,
71 x_party_site_id OUT NOCOPY NUMBER)
72 IS
73
74 l_api_name CONSTANT VARCHAR2(30) := 'create_address';
75 l_api_version CONSTANT NUMBER := 1.0;
76
77 l_party_site HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE := p_party_site;
78
79 l_count NUMBER;
80 l_gen_party_site_number VARCHAR2(1);
81 l_party_site_number VARCHAR2(30) := p_party_site.party_site_number;
82 l_party_site_use HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
83 l_party_site_use_id NUMBER;
84 l_loc_id NUMBER;
85
86 BEGIN
87
88 --IBE_UTIL.enable_debug();
89
90 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
91 IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.create_address');
92 END IF;
93
94 -- standard start of API savepoint
95 SAVEPOINT create_address_pvt;
96
97 -- standard call to check for call compatibility
98 IF NOT FND_API.compatible_api_call(l_api_version,
99 p_api_version,
100 l_api_name,
101 G_PKG_NAME)
102 THEN
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104 END IF;
105
106 -- initialize message list if p_init_msg_list is set to TRUE.
107 IF FND_API.to_boolean(p_init_msg_list) THEN
108 FND_MSG_PUB.initialize;
109 END IF;
110
111 begin
112 do_create_address(
113 p_api_version,
117 p_party_site,
114 p_init_msg_list,
115 p_commit,
116 p_location,
118 p_primary_billto,
119 p_primary_shipto,
120 p_billto,
121 p_shipto,
122 p_default_primary,
123 x_return_status,
124 x_msg_count,
125 x_msg_data,
126 x_location_id,
127 x_party_site_id
128 );
129 end;
130 --3639679 begin
131 if p_primary_shipto = FND_API.G_TRUE then
132 UPDATE
133 IBE_ORD_ONECLICK_ALL
134 SET
135 LAST_UPDATE_DATE = sysdate,
136 SHIP_TO_PTY_SITE_ID = x_party_site_id
137 WHERE
138 party_id = p_party_site.party_id
139 and SHIP_TO_PTY_SITE_ID is null;
140 end if;
141
142 if p_primary_billto = FND_API.G_TRUE then
143 UPDATE
144 IBE_ORD_ONECLICK_ALL
145 SET
146 LAST_UPDATE_DATE = sysdate,
147 BILL_TO_PTY_SITE_ID = x_party_site_id
148 WHERE
149 party_id = p_party_site.party_id
150 and BILL_TO_PTY_SITE_ID is null;
151 end if;
152 --3639679 end
153
154 -- standard check of p_commit
155 IF FND_API.to_boolean(p_commit) THEN
156 commit;
157 END IF;
158
159 -- standard call to get message count and if count is 1, get message info
160 FND_MSG_PUB.count_and_get(
161 p_encoded => FND_API.G_FALSE,
162 p_count => x_msg_count,
163 p_data => x_msg_data
164 );
165
166 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
167 IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.create_address');
168 END IF;
169
170 --IBE_UTIL.disable_debug();
171
172 EXCEPTION
173 WHEN FND_API.G_EXC_ERROR THEN
174 --IBE_UTIL.enable_debug();
175
176 ROLLBACK TO create_address_pvt;
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 FND_MSG_PUB.count_and_get(
179 p_encoded => FND_API.G_FALSE,
180 p_count => x_msg_count,
181 p_data => x_msg_data
182 );
183 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
184 IBE_UTIL.debug('G_EXC_ERROR exception');
185 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
186 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
187 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
188 IBE_UTIL.debug('error text : '|| SQLERRM);
189 END IF;
190
191 --IBE_UTIL.disable_debug();
192
193 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
194 --IBE_UTIL.enable_debug();
195
196 ROLLBACK TO create_address_pvt;
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 FND_MSG_PUB.count_and_get(
199 p_encoded => FND_API.G_FALSE,
200 p_count => x_msg_count,
201 p_data => x_msg_data
202 );
203 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
204 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
205 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
206 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
207 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
208 IBE_UTIL.debug('error text : '|| SQLERRM);
209 END IF;
210
211 --IBE_UTIL.disable_debug();
212
213 WHEN OTHERS THEN
214 --IBE_UTIL.enable_debug();
215
216 ROLLBACK TO create_address_pvt;
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
218 FND_MSG_PUB.count_and_get(
219 p_encoded => FND_API.G_FALSE,
220 p_count => x_msg_count,
221 p_data => x_msg_data
222 );
223 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
224 IBE_UTIL.debug('OTHER exception');
225 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
226 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
227 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
228 IBE_UTIL.debug('error text : '|| SQLERRM);
229 END IF;
230
231 --IBE_UTIL.disable_debug();
232 END;
233
234 PROCEDURE update_address(
235 p_api_version IN NUMBER,
236 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
237 p_commit IN VARCHAR2 := FND_API.G_FALSE,
238 p_party_site_id IN NUMBER,
239 p_ps_object_version_number IN NUMBER,
240 p_bill_object_version_number IN NUMBER,
241 p_ship_object_version_number IN NUMBER,
242 p_location IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
243 p_party_site IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
244 p_primary_billto IN VARCHAR2 := NULL,
245 p_primary_shipto IN VARCHAR2 := NULL,
246 p_billto IN VARCHAR2 := NULL,
247 p_shipto IN VARCHAR2 := NULL,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2,
251 x_location_id OUT NOCOPY NUMBER,
252 x_party_site_id OUT NOCOPY NUMBER)
253 IS
254
255 l_api_name VARCHAR2(30) := 'update_address';
256 l_api_version NUMBER := 1.0;
257
258 l_party_site_use_id NUMBER;
259
260 l_loc_changed BOOLEAN := false;
261 l_ps_changed BOOLEAN := false;
262 l_psu_changed VARCHAR(1);
263 l_chk_ps HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE := p_party_site;
264 l_chk_loc HZ_LOCATION_V2PUB.LOCATION_REC_TYPE := p_location;
265 l_chk_psu HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
266 l_ps_object_version_number NUMBER := p_ps_object_version_number;
267 l_bill_object_version_number NUMBER := p_bill_object_version_number;
268 l_ship_object_version_number NUMBER := p_ship_object_version_number;
269 l_loc_object_version_number NUMBER := 1;
270
271 CURSOR c_party_site_use(l_site_use_id NUMBER) IS
275 ORDER BY party_site_use_id DESC;
272 SELECT object_version_number
273 FROM hz_party_site_uses
274 WHERE party_site_use_id = l_site_use_id
276
277 CURSOR c_get_location_ovn(l_location_id VARCHAR2) IS
278 Select object_version_number
279 from hz_locations
280 where location_id = l_location_id;
281
282 BEGIN
283
284 --IBE_UTIL.enable_debug();
285
286 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
287 IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.update_address');
288 END IF;
289
290 -- standard start of API savepoint
291 SAVEPOINT update_address_pvt;
292
293 -- standard call to check for call compatibility
294 IF NOT FND_API.compatible_api_call(l_api_version,
295 p_api_version,
296 l_api_name,
297 G_PKG_NAME)
298 THEN
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 END IF;
301
302 -- initialize message list if p_init_msg_list is set to TRUE.
303 IF FND_API.to_boolean(p_init_msg_list) THEN
304 FND_MSG_PUB.initialize;
305 END IF;
306
307 --
308 -- Check if anything is changed before decide what to do
309 --
310 l_loc_changed := is_location_changed(p_location);
311 l_chk_ps.party_site_id := p_party_site_id;
312 l_ps_changed := is_party_site_changed(l_chk_ps);
313
314
315 --
316 -- Process the following conditions:
317 -- 1. not loc, not ps, not psu => do nothing
318 -- 2. not loc
319 -- 2.1 check ps, if changed, create
320 -- 2.2 check psu, if changed, update, otherwise create
321 -- 3. else create new loc, ps, psu as usual
322 --
323
324 IF l_loc_changed = false AND
325 l_ps_changed = false AND
326 l_psu_changed = 'F' THEN
327
328 x_return_status := 'S';
329 x_location_id := p_location.location_id;
330 x_party_site_id := p_party_site_id;
331
332
333 --
334 -- Do nothing
335 --
336
337 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
338 IBE_UTIL.debug('no need to update anything');
339 END IF;
340
341 ELSIF l_loc_changed = false then
342
343 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
344 IBE_UTIL.debug('party_site update status: ' || x_return_status);
345 END IF;
346
347 --
348 -- Update party_site if it's changed
349 --
350 if l_ps_changed = true then
351 -- l_chk_ps.party_id := FND_API.G_MISS_NUM;
352
353 HZ_PARTY_SITE_V2PUB.update_party_site(
354 p_init_msg_list,
355 l_chk_ps,
356 l_ps_object_version_number,
357 x_return_status,
358 x_msg_count,
359 x_msg_data
360 );
361 end if;
362
363 --check BILL_TO usage
364 -- value T=changed, F=not changed, N=usage not found
365 l_chk_psu.party_site_id := l_chk_ps.party_site_id;
366 l_chk_psu.site_use_type := 'BILL_TO';
367 --TCA API doesn't alllowing unsetting primary, NOOP
368 l_chk_psu.primary_per_type := 'N';
369 --if both billto flags are false, inactivate billto usage
370 if p_primary_billto = FND_API.G_FALSE and p_billto = FND_API.G_FALSE then
371 l_chk_psu.status := 'I';
372 elsif p_primary_billto = FND_API.G_TRUE then
373 l_chk_psu.status := 'A';
374 l_chk_psu.primary_per_type := 'Y';
375 elsif p_billto = FND_API.G_TRUE then
376 l_chk_psu.status := 'A';
377 --TCA API doesn't alllowing unsetting primary, NOOP
378 l_chk_psu.primary_per_type := 'N';
379 end if;
380 --see if BILL_TO PSU is changed or need to create new one
381 l_psu_changed := is_party_site_use_changed(l_chk_psu);
382 --if l_psu_changed = 'F', do nothing as nothing changed
383 -- if BILL _TO PSU is changed, update PSU record
384 if l_psu_changed = 'T' THEN
385 OPEN c_party_site_use(l_chk_psu.party_site_use_id);
386 FETCH c_party_site_use INTO l_bill_object_version_number;
387 HZ_PARTY_SITE_V2PUB.update_party_site_use(
388 p_init_msg_list,
389 l_chk_psu,
390 l_bill_object_version_number,
391 x_return_status,
392 x_msg_count,
393 x_msg_data
394 );
395 close c_party_site_use;
396 --if BILL _TO PSU record not found, create a new one
397 elsif l_psu_changed = 'N' and (p_billto = FND_API.G_TRUE or p_primary_billto = FND_API.G_TRUE) THEN
398 l_chk_psu.created_by_module := l_chk_ps.created_by_module;
399 l_chk_psu.party_site_use_id := NULL;
400 if l_chk_psu.created_by_module is NULL then
401 l_chk_psu.created_by_module := 'USER MANAGEMENT';
402 end if;
403 l_chk_psu.application_id := 671;
404 HZ_PARTY_SITE_V2PUB.create_party_site_use(
405 p_init_msg_list,
406 l_chk_psu,
407 l_party_site_use_id,
408 x_return_status,
409 x_msg_count,
410 x_msg_data
411 );
412 end if;
413
414 --check SHIP_TO usage record
415 -- value T=changed, F=not changed, N=usage not found
416 l_chk_psu.party_site_id := l_chk_ps.party_site_id;
417 l_chk_psu.site_use_type := 'SHIP_TO';
418 --TCA API doesn't alllowing unsetting primary, NOOP
419 l_chk_psu.primary_per_type := 'N';
420 --if both shipto flags are false, inactivate shipto usage
421 if p_primary_shipto = FND_API.G_FALSE and p_shipto = FND_API.G_FALSE then
422 l_chk_psu.status:='I';
426 elsif p_shipto = FND_API.G_TRUE then
423 elsif p_primary_shipto = FND_API.G_TRUE then
424 l_chk_psu.status:='A';
425 l_chk_psu.primary_per_type:='Y';
427 l_chk_psu.status:='A';
428 --TCA API doesn't alllowing unsetting primary, NOOP
429 l_chk_psu.primary_per_type:='N';
430 end if;
431 --see if SHIP _TO PSU is changed or need to create one
432 l_psu_changed := is_party_site_use_changed(l_chk_psu);
433
434 -- if SHIP_TO PSU is changed, update PSU record
435 if l_psu_changed = 'T' THEN
436 l_chk_psu.created_by_module := NULL;
437 OPEN c_party_site_use(l_chk_psu.party_site_use_id);
438 FETCH c_party_site_use INTO l_ship_object_version_number;
439 HZ_PARTY_SITE_V2PUB.update_party_site_use(
440 p_init_msg_list,
441 l_chk_psu,
442 l_ship_object_version_number,
443 x_return_status,
444 x_msg_count,
445 x_msg_data
446 );
447 close c_party_site_use;
448 --if SHIP_TO PSU record not found, create a new one
449 elsif l_psu_changed = 'N' and (p_shipto = FND_API.G_TRUE or p_primary_shipto = FND_API.G_TRUE) THEN
450 l_chk_psu.party_site_use_id := NULL;
451 l_chk_psu.created_by_module := l_chk_ps.created_by_module;
452 if l_chk_psu.created_by_module is NULL then
453 l_chk_psu.created_by_module := 'USER MANAGEMENT';
454 end if;
455 l_chk_psu.application_id := 671;
456 HZ_PARTY_SITE_V2PUB.create_party_site_use(
457 p_init_msg_list,
458 l_chk_psu,
459 l_party_site_use_id,
460 x_return_status,
461 x_msg_count,
462 x_msg_data
463 );
464 end if;
465
466 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
467 RAISE FND_API.G_EXC_ERROR;
468 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
470 END IF;
471
472 --
473 -- set return parameters
474 --
475 x_location_id := p_location.location_id;
476 x_party_site_id := p_party_site_id;
477 if x_return_status is null then
478 x_return_status := 'S';
479 end if;
480
481 ELSIF l_loc_changed = true and l_ps_changed = false THEN
482
483 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
484 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.update_address, location changed but no PS change');
485 END IF;
486
487 Open c_get_location_ovn(p_location.location_id);
488 FETCH c_get_location_ovn INTO l_loc_object_version_number;
489 --Convert null to G_Miss, as TCA is ignoring null
490 l_chk_loc.address2 := nvl(l_chk_loc.address2,FND_API.G_MISS_CHAR);
491 l_chk_loc.address3 := nvl(l_chk_loc.address3,FND_API.G_MISS_CHAR);
492 l_chk_loc.address4 := nvl(l_chk_loc.address4,FND_API.G_MISS_CHAR);
493 l_chk_loc.city := nvl(l_chk_loc.city,FND_API.G_MISS_CHAR);
494 l_chk_loc.postal_code := nvl(l_chk_loc.postal_code,FND_API.G_MISS_CHAR);
495 l_chk_loc.state := nvl(l_chk_loc.state,FND_API.G_MISS_CHAR);
496 l_chk_loc.province := nvl(l_chk_loc.province,FND_API.G_MISS_CHAR);
497 l_chk_loc.county := nvl(l_chk_loc.county,FND_API.G_MISS_CHAR);
498 l_chk_loc.address_lines_phonetic := nvl(l_chk_loc.address_lines_phonetic,FND_API.G_MISS_CHAR);
499 --End conversion
500 HZ_LOCATION_V2PUB.update_location(
501 p_init_msg_list,
502 l_chk_loc,
503 l_loc_object_version_number,
504 x_return_status,
505 x_msg_count,
506 x_msg_data
507 );
508 Close c_get_location_ovn;
509
510 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
511 RAISE FND_API.G_EXC_ERROR;
512 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
514 END IF;
515
516 --check BILL_TO usage
517 -- value T=changed, F=not changed, N=usage not found
518 l_chk_psu.party_site_id := l_chk_ps.party_site_id;
519 l_chk_psu.site_use_type := 'BILL_TO';
520 --TCA API doesn't alllowing unsetting primary, NOOP
521 l_chk_psu.primary_per_type := 'N';
522 --if both billto flags are false, inactivate billto usage
523 if p_primary_billto = FND_API.G_FALSE and p_billto = FND_API.G_FALSE then
524 l_chk_psu.status := 'I';
525 elsif p_primary_billto = FND_API.G_TRUE then
526 l_chk_psu.status := 'A';
527 l_chk_psu.primary_per_type := 'Y';
528 elsif p_billto = FND_API.G_TRUE then
529 l_chk_psu.status := 'A';
530 --TCA API doesn't alllowing unsetting primary, NOOP
531 l_chk_psu.primary_per_type := 'N';
532 end if;
533 --see if BILL_TO PSU is changed or need to create new one
534 l_psu_changed := is_party_site_use_changed(l_chk_psu);
535 --if l_psu_changed = 'F', do nothing as nothing changed
536 -- if BILL _TO PSU is changed, update PSU record
537 if l_psu_changed = 'T' THEN
538 OPEN c_party_site_use(l_chk_psu.party_site_use_id);
539 FETCH c_party_site_use INTO l_bill_object_version_number;
540 HZ_PARTY_SITE_V2PUB.update_party_site_use(
541 p_init_msg_list,
542 l_chk_psu,
543 l_bill_object_version_number,
544 x_return_status,
545 x_msg_count,
546 x_msg_data
547 );
548 close c_party_site_use;
549 --if BILL _TO PSU record not found, create a new one
550 elsif l_psu_changed = 'N' and (p_billto = FND_API.G_TRUE or p_primary_billto = FND_API.G_TRUE) THEN
554 l_chk_psu.created_by_module := 'USER MANAGEMENT';
551 l_chk_psu.created_by_module := l_chk_ps.created_by_module;
552 l_chk_psu.party_site_use_id := NULL;
553 if l_chk_psu.created_by_module is NULL then
555 end if;
556 l_chk_psu.application_id := 671;
557 HZ_PARTY_SITE_V2PUB.create_party_site_use(
558 p_init_msg_list,
559 l_chk_psu,
560 l_party_site_use_id,
561 x_return_status,
562 x_msg_count,
563 x_msg_data
564 );
565 end if;
566
567 --check SHIP_TO usage record
568 -- value T=changed, F=not changed, N=usage not found
569 l_chk_psu.party_site_id := l_chk_ps.party_site_id;
570 l_chk_psu.site_use_type := 'SHIP_TO';
571 --TCA API doesn't alllowing unsetting primary, NOOP
572 l_chk_psu.primary_per_type := 'N';
573 --if both shipto flags are false, inactivate shipto usage
574 if p_primary_shipto = FND_API.G_FALSE and p_shipto = FND_API.G_FALSE then
575 l_chk_psu.status:='I';
576 elsif p_primary_shipto = FND_API.G_TRUE then
577 l_chk_psu.status:='A';
578 l_chk_psu.primary_per_type:='Y';
579 elsif p_shipto = FND_API.G_TRUE then
580 l_chk_psu.status:='A';
581 --TCA API doesn't alllowing unsetting primary, NOOP
582 l_chk_psu.primary_per_type:='N';
583 end if;
584 --see if SHIP _TO PSU is changed or need to create one
585 l_psu_changed := is_party_site_use_changed(l_chk_psu);
586
587 -- if SHIP_TO PSU is changed, update PSU record
588 if l_psu_changed = 'T' THEN
589 l_chk_psu.created_by_module := NULL;
590 OPEN c_party_site_use(l_chk_psu.party_site_use_id);
591 FETCH c_party_site_use INTO l_ship_object_version_number;
592 HZ_PARTY_SITE_V2PUB.update_party_site_use(
593 p_init_msg_list,
594 l_chk_psu,
595 l_ship_object_version_number,
596 x_return_status,
597 x_msg_count,
598 x_msg_data
599 );
600 close c_party_site_use;
601 --if SHIP_TO PSU record not found, create a new one
602 elsif l_psu_changed = 'N' and (p_shipto = FND_API.G_TRUE or p_primary_shipto = FND_API.G_TRUE) THEN
603 l_chk_psu.party_site_use_id := NULL;
604 l_chk_psu.created_by_module := l_chk_ps.created_by_module;
605 if l_chk_psu.created_by_module is NULL then
606 l_chk_psu.created_by_module := 'USER MANAGEMENT';
607 end if;
608 l_chk_psu.application_id := 671;
609 HZ_PARTY_SITE_V2PUB.create_party_site_use(
610 p_init_msg_list,
611 l_chk_psu,
612 l_party_site_use_id,
613 x_return_status,
614 x_msg_count,
615 x_msg_data
616 );
617 end if;
618
619 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
620 RAISE FND_API.G_EXC_ERROR;
621 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 x_location_id := p_location.location_id;
626 x_party_site_id := p_party_site_id;
627 if x_return_status is null then
628 x_return_status := 'S';
629 end if;
630
631 ELSE --Both Loc and PartySite are change, create new address, ps
632
633 --
634 -- delete an existing party site
635 --
636
637 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
638 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.delete_address(+)');
639 END IF;
640
641 do_delete_address(
642 p_api_version,
643 p_init_msg_list,
644 p_commit,
645 p_party_site.party_id,
646 p_party_site_id,
647 l_ps_object_version_number,
648 l_bill_object_version_number,
649 l_ship_object_version_number,
650 x_return_status,
651 x_msg_count,
652 x_msg_data
653 );
654
655 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
656 RAISE FND_API.G_EXC_ERROR;
657 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
659 END IF;
660
661 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
662 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.delete_address(-)');
663 IBE_UTIL.debug('party_site_id: ' || to_char(p_party_site_id) || ' deleted');
664 END IF;
665
666
667 --
668 -- create a new location and party site
669 --
670
671 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
672 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.do_create_address(+)');
673 END IF;
674 if l_chk_ps.created_by_module is NULL then
675 l_chk_ps.created_by_module := 'USER MANAGEMENT';
676 end if;
677 l_chk_ps.application_id := 671;
678 if l_chk_loc.created_by_module is NULL then
679 l_chk_loc.created_by_module := 'USER MANAGEMENT';
680 end if;
681 l_chk_loc.application_id := 671;
682 do_create_address(
683 p_api_version,
684 p_init_msg_list,
685 p_commit,
686 l_chk_loc,
687 l_chk_ps,
688 p_primary_billto,
689 p_primary_shipto,
690 p_billto,
691 p_shipto,
692 FND_API.G_FALSE,
693 x_return_status,
694 x_msg_count,
695 x_msg_data,
696 x_location_id,
697 x_party_site_id
698 );
699
700 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
704 END IF;
701 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.do_create_address(-)');
702 IBE_UTIL.debug('location_id = ' || to_char(x_location_id));
703 IBE_UTIL.debug('party_site_id = ' || to_char(x_party_site_id));
705
706 --
707 -- Update one click setting with new party site id
708 --
709 --fix 2766830
710 UPDATE
711 IBE_ORD_ONECLICK_ALL
712 SET
713 LAST_UPDATE_DATE = sysdate,
714 SHIP_TO_PTY_SITE_ID = x_party_site_id
715 WHERE
716 SHIP_TO_PTY_SITE_ID = p_party_site_id;
717
718 UPDATE
719 IBE_ORD_ONECLICK_ALL
720 SET
721 LAST_UPDATE_DATE = sysdate,
722 BILL_TO_PTY_SITE_ID = x_party_site_id
723 WHERE
724 BILL_TO_PTY_SITE_ID = p_party_site_id;
725
726 END IF;
727
728
729 -- standard check of p_commit
730 IF FND_API.to_boolean(p_commit) THEN
731 commit;
732 END IF;
733
734 -- standard call to get message count and if count is 1, get message info
735 FND_MSG_PUB.count_and_get(
736 p_encoded => FND_API.G_FALSE,
737 p_count => x_msg_count,
738 p_data => x_msg_data
739 );
740
741 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
742 IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.update_address');
743 END IF;
744
745 --IBE_UTIL.disable_debug();
746
747 EXCEPTION
748 WHEN FND_API.G_EXC_ERROR THEN
749 --IBE_UTIL.enable_debug();
750
751 ROLLBACK TO update_address_pvt;
752 x_return_status := FND_API.G_RET_STS_ERROR;
753 FND_MSG_PUB.count_and_get(
754 p_encoded => FND_API.G_FALSE,
755 p_count => x_msg_count,
756 p_data => x_msg_data
757 );
758 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
759 IBE_UTIL.debug('G_EXC_ERROR exception');
760 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
761 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
762 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
763 IBE_UTIL.debug('error text : '|| SQLERRM);
764 END IF;
765
766 --IBE_UTIL.disable_debug();
767
768 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
769 --IBE_UTIL.enable_debug();
770
771 ROLLBACK TO update_address_pvt;
772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773 FND_MSG_PUB.count_and_get(
774 p_encoded => FND_API.G_FALSE,
775 p_count => x_msg_count,
776 p_data => x_msg_data
777 );
778 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
779 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
780 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
781 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
782 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
783 IBE_UTIL.debug('error text : '|| SQLERRM);
784 END IF;
785
786 --IBE_UTIL.disable_debug();
787
788 WHEN OTHERS THEN
789 --IBE_UTIL.enable_debug();
790
791 ROLLBACK TO update_address_pvt;
792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
793 FND_MSG_PUB.count_and_get(
794 p_encoded => FND_API.G_FALSE,
795 p_count => x_msg_count,
796 p_data => x_msg_data
797 );
798 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
799 IBE_UTIL.debug('OTHER exception');
800 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
801 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
802 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
803 IBE_UTIL.debug('error text : '|| SQLERRM);
804 END IF;
805
806 --IBE_UTIL.disable_debug();
807 END;
808
809
810 PROCEDURE delete_address(
811 p_api_version IN NUMBER,
812 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
813 p_commit IN VARCHAR2 := FND_API.G_FALSE,
814 p_party_id IN NUMBER,
815 p_party_site_id IN NUMBER,
816 p_ps_object_version_number IN NUMBER,
817 p_bill_object_version_number IN NUMBER,
818 p_ship_object_version_number IN NUMBER,
819 x_return_status OUT NOCOPY VARCHAR2,
820 x_msg_count OUT NOCOPY NUMBER,
821 x_msg_data OUT NOCOPY VARCHAR2)
822 IS
823
824 l_api_name CONSTANT VARCHAR2(30) := 'delete_address';
825 l_api_version CONSTANT NUMBER := 1.0;
826 l_party_site HZ_PARTY_SITE_V2PUB.party_site_rec_type;
827 l_ps_object_version_number NUMBER := p_ps_object_version_number;
828 l_bill_object_version_number NUMBER := p_bill_object_version_number;
829 l_ship_object_version_number NUMBER := p_ship_object_version_number;
830
831 BEGIN
832
833 --IBE_UTIL.enable_debug();
834
835 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
836 IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.delete_address');
837 END IF;
838
839 -- standard start of API savepoint
840 SAVEPOINT delete_address_pvt;
841
842 -- standard call to check for call compatibility
843 IF NOT FND_API.compatible_api_call(l_api_version,
844 p_api_version,
845 l_api_name,
846 G_PKG_NAME)
847 THEN
848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
849 END IF;
850
851 -- initialize message list if p_init_msg_list is set to TRUE.
852 IF FND_API.to_boolean(p_init_msg_list) THEN
853 FND_MSG_PUB.initialize;
854 END IF;
855
856 --
857 -- delete the address
861 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.delete_address(+)');
858 --
859
860 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
862 END IF;
863
864 do_delete_address(
865 p_api_version,
866 p_init_msg_list,
867 p_commit,
868 p_party_id,
869 p_party_site_id,
870 p_ps_object_version_number,
871 p_bill_object_version_number,
872 p_ship_object_version_number,
873 x_return_status,
874 x_msg_count,
875 x_msg_data
876 );
877
878 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
879 RAISE FND_API.G_EXC_ERROR;
880 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
882 END IF;
883
884 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
885 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.do_delete_address(-)');
886 IBE_UTIL.debug('party_site_id: ' || to_char(p_party_site_id) || ' deleted');
887 END IF;
888
889
890 --
891 -- Update one click record
892 -- Set party site id to null
893 --
894 --fix 2766830
895 UPDATE
896 IBE_ORD_ONECLICK_ALL
897 SET
898 ENABLED_FLAG = 'N',
899 LAST_UPDATE_DATE = sysdate,
900 SHIP_TO_PTY_SITE_ID = null
901 WHERE
902 SHIP_TO_PTY_SITE_ID = p_party_site_id;
903
904 UPDATE
905 IBE_ORD_ONECLICK_ALL
906 SET
907 ENABLED_FLAG = 'N',
908 LAST_UPDATE_DATE = sysdate,
909 BILL_TO_PTY_SITE_ID = null
910 WHERE
911 BILL_TO_PTY_SITE_ID = p_party_site_id;
912
913
914 -- standard check of p_commit
915 IF FND_API.to_boolean(p_commit) THEN
916 commit;
917 END IF;
918
919 -- standard call to get message count and if count is 1, get message info
920 FND_MSG_PUB.count_and_get(
921 p_encoded => FND_API.G_FALSE,
922 p_count => x_msg_count,
923 p_data => x_msg_data
924 );
925
926 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
927 IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.delete_address');
928 END IF;
929
930 --IBE_UTIL.disable_debug();
931
932 EXCEPTION
933 WHEN FND_API.G_EXC_ERROR THEN
934 --IBE_UTIL.enable_debug();
935
936 ROLLBACK TO delete_address_pvt;
937 x_return_status := FND_API.G_RET_STS_ERROR;
938 FND_MSG_PUB.count_and_get(
939 p_encoded => FND_API.G_FALSE,
940 p_count => x_msg_count,
941 p_data => x_msg_data
942 );
943 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
944 IBE_UTIL.debug('G_EXC_ERROR exception');
945 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
946 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
947 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
948 IBE_UTIL.debug('error text : '|| SQLERRM);
949 END IF;
950
951 --IBE_UTIL.disable_debug();
952
953 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
954 --IBE_UTIL.enable_debug();
955
956 ROLLBACK TO delete_address_pvt;
957 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
958 FND_MSG_PUB.count_and_get(
959 p_encoded => FND_API.G_FALSE,
960 p_count => x_msg_count,
961 p_data => x_msg_data
962 );
963 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
964 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
965 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
966 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
967 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
968 IBE_UTIL.debug('error text : '|| SQLERRM);
969 END IF;
970
971 --IBE_UTIL.disable_debug();
972
973 WHEN OTHERS THEN
974 --IBE_UTIL.enable_debug();
975
976 ROLLBACK TO delete_address_pvt;
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 FND_MSG_PUB.count_and_get(
979 p_encoded => FND_API.G_FALSE,
980 p_count => x_msg_count,
981 p_data => x_msg_data
982 );
983 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
984 IBE_UTIL.debug('OTHER exception');
985 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
986 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
987 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
988 IBE_UTIL.debug('error text : '|| SQLERRM);
989 END IF;
990
991 --IBE_UTIL.disable_debug();
992
993 END;
994
995 PROCEDURE set_address_usage(
996 p_api_version IN NUMBER,
997 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
998 p_commit IN VARCHAR2 := FND_API.G_FALSE,
999 p_party_site_id IN NUMBER,
1000 p_primary_flag IN VARCHAR2 := FND_API.G_FALSE,
1001 p_site_use_type IN VARCHAR2,
1002 p_createdby IN VARCHAR2 := 'User Management',
1003 x_return_status OUT NOCOPY VARCHAR2,
1004 x_msg_count OUT NOCOPY NUMBER,
1005 x_msg_data OUT NOCOPY VARCHAR2,
1006 x_party_site_use_id OUT NOCOPY NUMBER)
1007 IS
1008
1009 l_api_version NUMBER := 1.0;
1010 l_api_name VARCHAR2(30) := 'set_primary_address';
1011
1012 l_party_site_use HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
1013 l_party_site_use_id NUMBER;
1014 --V2
1015 l_object_version_number NUMBER;
1016 --l_last_update_date DATE;
1017
1018 --v2
1019 CURSOR c_party_site_use IS
1020 SELECT party_site_use_id, object_version_number
1021 FROM ( SELECT party_site_use_id, object_version_number
1022 FROM hz_party_site_uses
1023 WHERE party_site_id = p_party_site_id
1024 AND site_use_type = p_site_use_type
1025 ORDER BY status, party_site_use_id DESC
1029 BEGIN
1026 )
1027 WHERE rownum = 1;
1028
1030
1031 --IBE_UTIL.enable_debug();
1032
1033 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1034 IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.set_primary_address');
1035 END IF;
1036
1037 -- standard start of API savepoint
1038 SAVEPOINT set_address__usage_pvt;
1039
1040 -- standard call to check for call compatibility
1041 IF NOT FND_API.compatible_api_call(l_api_version,
1042 p_api_version,
1043 l_api_name,
1044 G_PKG_NAME)
1045 THEN
1046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1047 END IF;
1048
1049 -- initialize message list if p_init_msg_list is set to TRUE.
1050 IF FND_API.to_boolean(p_init_msg_list) THEN
1051 FND_MSG_PUB.initialize;
1052 END IF;
1053
1054 -- try finding an existing party site use
1055
1056 OPEN c_party_site_use;
1057 FETCH c_party_site_use INTO l_party_site_use_id, l_object_version_number;
1058
1059
1060 if (p_primary_flag = FND_API.G_TRUE) then
1061 l_party_site_use.primary_per_type := 'Y';
1062 end if;
1063
1064 IF c_party_site_use%FOUND THEN
1065
1066 -- update an existing party site use
1067 l_party_site_use.party_site_use_id := l_party_site_use_id;
1068 l_party_site_use.status := 'A';
1069
1070 HZ_PARTY_SITE_V2PUB.update_party_site_use (
1071 p_init_msg_list,
1072 l_party_site_use,
1073 l_object_version_number,
1074 x_return_status,
1075 x_msg_count,
1076 x_msg_data);
1077
1078 x_party_site_use_id := l_party_site_use_id;
1079
1080 ELSE
1081
1082 -- create a party site if not found
1083 l_party_site_use.party_site_id := p_party_site_id;
1084 l_party_site_use.site_use_type := p_site_use_type;
1085 l_party_site_use.created_by_module := p_createdby;
1086
1087 l_party_site_use.application_id := 671;
1088 --V2
1089 -- l_party_site_use.begin_date := sysdate;
1090 l_party_site_use.status := 'A';
1091
1092
1093 HZ_PARTY_SITE_V2PUB.create_party_site_use (
1094 p_init_msg_list,
1095 l_party_site_use,
1096 x_party_site_use_id,
1097 x_return_status,
1098 x_msg_count,
1099 x_msg_data);
1100 --V2
1101 /*HZ_PARTY_PUB.create_party_site_use(
1102 p_api_version,
1103 p_init_msg_list,
1104 p_commit,
1105 l_party_site_use,
1106 x_return_status,
1107 x_msg_count,
1108 x_msg_data,
1109 x_party_site_use_id
1110 );
1111 */
1112 END IF;
1113
1114 CLOSE c_party_site_use;
1115
1116 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1117 RAISE FND_API.G_EXC_ERROR;
1118 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1120 END IF;
1121
1122 -- standard check of p_commit
1123 IF FND_API.to_boolean(p_commit) THEN
1124 commit;
1125 END IF;
1126
1127 -- standard call to get message count and if count is 1, get message info
1128 FND_MSG_PUB.count_and_get(
1129 p_encoded => FND_API.G_FALSE,
1130 p_count => x_msg_count,
1131 p_data => x_msg_data
1132 );
1133
1134 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1135 IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.set_primary_address');
1136 END IF;
1137
1138 --IBE_UTIL.disable_debug();
1139
1140 EXCEPTION
1141 WHEN FND_API.G_EXC_ERROR THEN
1142 --IBE_UTIL.enable_debug();
1143
1144 ROLLBACK TO set_address__usage_pvt;
1145 x_return_status := FND_API.G_RET_STS_ERROR;
1146 FND_MSG_PUB.count_and_get(
1147 p_encoded => FND_API.G_FALSE,
1148 p_count => x_msg_count,
1149 p_data => x_msg_data
1150 );
1151 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1152 IBE_UTIL.debug('G_EXC_ERROR exception');
1153 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1154 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1155 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1156 IBE_UTIL.debug('error text : '|| SQLERRM);
1157 END IF;
1158
1159 --IBE_UTIL.disable_debug();
1160
1161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1162 --IBE_UTIL.enable_debug();
1163
1164 ROLLBACK TO set_address__usage_pvt;
1165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 FND_MSG_PUB.count_and_get(
1167 p_encoded => FND_API.G_FALSE,
1168 p_count => x_msg_count,
1169 p_data => x_msg_data
1170 );
1171 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1172 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
1173 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1174 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1175 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1176 IBE_UTIL.debug('error text : '|| SQLERRM);
1177 END IF;
1178
1179 --IBE_UTIL.disable_debug();
1180
1181 WHEN OTHERS THEN
1182 --IBE_UTIL.enable_debug();
1183
1184 ROLLBACK TO set_address__usage_pvt;
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186 FND_MSG_PUB.count_and_get(
1187 p_encoded => FND_API.G_FALSE,
1188 p_count => x_msg_count,
1189 p_data => x_msg_data
1190 );
1191 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1192 IBE_UTIL.debug('OTHER exception');
1193 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1194 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1195 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1199 --IBE_UTIL.disable_debug();
1196 IBE_UTIL.debug('error text : '|| SQLERRM);
1197 END IF;
1198
1200 END;
1201
1202 PROCEDURE get_primary_addr_details(
1203 p_api_version IN NUMBER,
1204 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1205 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1206 p_party_id IN NUMBER,
1207 p_site_use_type IN VARCHAR2,
1208 p_org_id IN NUMBER,
1209 p_alt_party_id IN NUMBER,
1210 x_return_status OUT NOCOPY VARCHAR2,
1211 x_msg_count OUT NOCOPY NUMBER,
1212 x_msg_data OUT NOCOPY VARCHAR2,
1213 x_party_site_id OUT NOCOPY NUMBER,
1214 x_party_id OUT NOCOPY NUMBER
1215 )
1216 IS
1217 BEGIN
1218 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1219 IBE_UTIL.debug('Starting getPrimary Addr Details ');
1220 END IF;
1221 x_party_id := p_party_id;
1222 get_primary_addr_id
1223 (p_api_version => p_api_version,
1224 p_party_id => p_party_id,
1225 p_site_use_type => p_site_use_type,
1226 p_org_id => p_org_id,
1227 x_return_status => x_return_status,
1228 x_msg_count => x_msg_count,
1229 x_msg_data => x_msg_data,
1230 x_party_site_id => x_party_site_id
1231 );
1232 if(x_party_site_id = FND_API.G_MISS_NUM OR x_party_site_id is null) THEN
1233 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1234 IBE_UTIL.debug(' getPrimary Addr Details of alt PartyId ');
1235 END IF;
1236 x_party_id := p_alt_party_id;
1237 get_primary_addr_id
1238 (p_api_version => p_api_version,
1239 p_party_id => p_alt_party_id,
1240 p_site_use_type => p_site_use_type,
1241 p_org_id => p_org_id,
1242 x_return_status => x_return_status,
1243 x_msg_count => x_msg_count,
1244 x_msg_data => x_msg_data,
1245 x_party_site_id => x_party_site_id
1246 );
1247 END IF;
1248 if(x_party_Site_id = FND_API.G_MISS_NUM OR x_party_site_id is null) then
1249 x_party_id := null;
1250 end if;
1251 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1252 IBE_UTIL.debug('Ending getPrimary Addr Details with party_site_id '||x_party_site_id);
1253 END IF;
1254 END;
1255
1256 PROCEDURE get_primary_addr_id(
1257 p_api_version IN NUMBER,
1258 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1259 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1260 p_party_id IN NUMBER,
1261 p_site_use_type IN VARCHAR2,
1262 p_org_id IN NUMBER,
1263 p_get_org_prim_addr IN VARCHAR2 := FND_API.G_FALSE,
1264 x_return_status OUT NOCOPY VARCHAR2,
1265 x_msg_count OUT NOCOPY NUMBER,
1266 x_msg_data OUT NOCOPY VARCHAR2,
1267 x_party_site_id OUT NOCOPY NUMBER)
1268 IS
1269
1270 l_api_name CONSTANT VARCHAR2(30) := 'get_primary_addr_id';
1271 l_api_version CONSTANT NUMBER := 1.0;
1272 site_type VARCHAR2(30) := ' ';
1273 hr_type VARCHAR2(30) := ' ';
1274 l_party_id NUMBER;
1275 l_org_id NUMBER;
1276 l_contact_org_id NUMBER;
1277
1278 cursor c_getPrimAddrId_2(l_party_id NUMBER,l_org_id NUMBER,hr_type VARCHAR2,site_type VARCHAR2) IS
1279 SELECT ps.party_site_id
1280 FROM hz_party_sites ps, hz_party_site_uses psu, hz_locations loc, hr_organization_information hr
1281 WHERE
1282 ps.party_id = l_party_id AND
1283 ps.status = 'A' AND
1284 ps.location_id = loc.location_id AND
1285 ps.party_site_id = psu.party_site_id AND
1286 psu.primary_per_type = 'Y' AND
1287 psu.site_use_type = site_type AND
1288 psu.status = 'A' AND
1289 NVL(psu.end_date,sysdate+1) > sysdate AND
1290 hr.organization_id = l_org_id AND
1291 hr.org_information_context = hr_type AND
1292 hr.org_information1 = loc.country AND
1293 ( NOT EXISTS (
1294 SELECT 1 FROM hz_cust_acct_sites_all cas1
1295 WHERE cas1.party_site_id = ps.party_site_id
1296 AND cas1.org_id = MO_GLOBAL.get_current_org_id()) OR
1297 ( EXISTS ( SELECT 1 FROM hz_cust_acct_sites_all
1298 WHERE party_site_id = ps.party_site_id
1299 AND org_id = MO_GLOBAL.get_current_org_id()
1300 AND status = 'A') AND
1301 (
1302 NOT EXISTS (
1303 SELECT 1 FROM hz_cust_acct_sites_all cas2, hz_cust_site_uses_all csu2
1304 WHERE csu2.cust_acct_site_id = cas2.cust_acct_site_id
1305 AND cas2.party_site_id = ps.party_site_id
1306 AND cas2.org_id = MO_GLOBAL.get_current_org_id()
1307 AND csu2.site_use_code = site_type) OR
1308 EXISTS (
1309 SELECT 1
1310 FROM hz_cust_acct_sites_all cas, hz_cust_site_uses_all csu
1311 WHERE cas.party_site_id = ps.party_site_id AND
1312 cas.org_id = MO_GLOBAL.get_current_org_id() AND
1313 csu.cust_acct_site_id (+) = cas.cust_acct_site_id AND
1314 NVL(csu.status,'A') = 'A' AND
1315 NVL(csu.site_use_code,site_type) = site_type)
1316 )
1317 )
1318 );
1319
1320 cursor c_getPrimAddrId_1(l_party_id NUMBER,l_org_id NUMBER,hr_type VARCHAR2,site_type VARCHAR2) IS
1321 SELECT ps.party_site_id
1322 FROM hz_party_sites ps, hz_party_site_uses psu
1323 WHERE
1324 ps.party_id = l_party_id AND
1325 ps.status = 'A' AND
1326 ps.party_site_id = psu.party_site_id AND
1330 NVL(psu.end_date,sysdate+1) > sysdate AND
1327 psu.primary_per_type = 'Y' AND
1328 psu.site_use_type = site_type AND
1329 psu.status = 'A' AND
1331 NOT EXISTS (
1332 SELECT 1
1333 FROM hr_organization_information hr
1334 WHERE
1335 hr.organization_id = l_org_id AND
1336 hr.org_information_context = hr_type AND
1337 rownum = 1) AND
1338 ( NOT EXISTS (
1339 SELECT 1 FROM hz_cust_acct_sites_all cas1
1340 WHERE cas1.party_site_id = ps.party_site_id
1341 AND cas1.org_id = MO_GLOBAL.get_current_org_id()) OR
1342 ( EXISTS ( SELECT 1 FROM hz_cust_acct_sites_all
1343 WHERE party_site_id = ps.party_site_id
1344 AND org_id = MO_GLOBAL.get_current_org_id()
1345 AND status = 'A') AND
1346 (
1347 NOT EXISTS (
1348 SELECT 1 FROM hz_cust_acct_sites_all cas2, hz_cust_site_uses_all csu2
1349 WHERE csu2.cust_acct_site_id = cas2.cust_acct_site_id
1350 AND cas2.party_site_id = ps.party_site_id
1351 AND cas2.org_id = MO_GLOBAL.get_current_org_id()
1352 AND csu2.site_use_code = site_type) OR
1353 EXISTS (
1354 SELECT 1
1355 FROM hz_cust_acct_sites_all cas, hz_cust_site_uses_all csu
1356 WHERE cas.party_site_id = ps.party_site_id AND
1357 cas.org_id = MO_GLOBAL.get_current_org_id() AND
1358 csu.cust_acct_site_id (+) = cas.cust_acct_site_id AND
1359 NVL(csu.status,'A') = 'A' AND
1360 NVL(csu.site_use_code,site_type) = site_type)
1361 )
1362 )
1363 );
1364
1365 cursor c_getOrgPartyId(l_party_id number) IS
1366 select subject_id from hz_relationships
1367 where party_id = l_party_id and subject_type = 'ORGANIZATION';
1368
1369 BEGIN
1370
1371 --IBE_UTIL.enable_debug();
1372
1373 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1374 IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.get_primary_addr_id for party_id'||p_party_id);
1375 END IF;
1376
1377
1378
1379
1380 -- standard start of API savepoint
1381 SAVEPOINT get_primary_addr_id_pvt;
1382
1383 -- standard call to check for call compatibility
1384 IF NOT FND_API.compatible_api_call(l_api_version,
1385 p_api_version,
1386 l_api_name,
1387 G_PKG_NAME)
1388 THEN
1389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1390 END IF;
1391
1392 -- initialize message list if p_init_msg_list is set to TRUE.
1393 IF FND_API.to_boolean(p_init_msg_list) THEN
1394 FND_MSG_PUB.initialize;
1395 END IF;
1396
1397 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1398 IBE_UTIL.debug('p_site_use_type = '||p_site_use_type);
1399 END IF;
1400
1401
1402 IF (p_site_use_type = 'S') THEN
1403 site_type := 'SHIP_TO';
1404 hr_type := 'SHIP_TO_COUNTRY';
1405 END IF;
1406 IF (p_site_use_type = 'B') THEN
1407 site_type := 'BILL_TO';
1408 hr_type := 'BILL_TO_COUNTRY';
1409 END IF;
1410
1411 x_party_site_id := null;
1412
1413 OPEN c_getPrimAddrId_1(p_party_id,p_org_id,hr_type,site_type);
1414 FETCH c_getPrimAddrId_1 INTO x_party_site_id;
1415 CLOSE c_getPrimAddrId_1;
1416
1417 IF (x_party_site_id IS NULL) THEN
1418 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1419 IBE_UTIL.debug('Accessing the SECOND cursor');
1420 END IF;
1421 OPEN c_getPrimAddrId_2(p_party_id,p_org_id,hr_type,site_type);
1422 FETCH c_getPrimAddrId_2 INTO x_party_site_id;
1423 CLOSE c_getPrimAddrId_2;
1424 ELSE
1425 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1426 IBE_UTIL.debug('NOT Accessing the SECOND cursor');
1427 END IF;
1428 END IF;
1429
1430
1431 /* madesai - 7/10 fixed bug 2608767 */
1432 if x_party_site_id IS NULL then
1433 if FND_API.to_boolean(p_get_org_prim_addr) then
1434 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1435 IBE_UTIL.debug('ContactPrimAddr not found,get Org Primary Addr');
1436 END IF;
1437 OPEN c_getOrgPartyId(p_party_id);
1438 FETCH c_getOrgPartyId INTO l_contact_org_id;
1439 if c_getOrgPartyId%notfound then
1440 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1441 IBE_UTIL.debug('Org Id is not found');
1442 END IF;
1443 x_party_site_id := null;
1444 else
1445 get_primary_addr_id
1446 (p_api_version => p_api_version,
1447 p_party_id => l_contact_org_id,
1448 p_site_use_type => p_site_use_type,
1449 p_org_id => p_org_id,
1450 x_return_status => x_return_status,
1451 x_msg_count => x_msg_count,
1452 x_msg_data => x_msg_data,
1453 x_party_site_id => x_party_site_id
1454 );
1455 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1456 IBE_UTIL.debug('Primary address for orgId'||l_contact_org_id||'is '||x_party_site_id);
1457 END IF;
1458 end if;
1459 else
1460 x_party_site_id := null;
1461 end if;--p_get_org_prim_addr of loop
1462 end if;--getPrimAddrId cursor end
1463 x_return_status := FND_API.G_RET_STS_SUCCESS;
1464 -- standard check of p_commit
1465 IF FND_API.to_boolean(p_commit) THEN
1466 commit;
1467 END IF;
1468
1469 -- standard call to get message count and if count is 1, get message info
1473 p_data => x_msg_data
1470 FND_MSG_PUB.count_and_get(
1471 p_encoded => FND_API.G_FALSE,
1472 p_count => x_msg_count,
1474 );
1475
1476 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1477 IBE_UTIL.debug('Ending getPrimary Addr with party_site_id '||x_party_site_id);
1478 IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.get_primary_address');
1479 END IF;
1480
1481 --IBE_UTIL.disable_debug();
1482
1483 EXCEPTION
1484 WHEN OTHERS THEN
1485 --IBE_UTIL.enable_debug();
1486
1487 ROLLBACK TO get_primary_addr_id_pvt;
1488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1489 FND_MSG_PUB.count_and_get(
1490 p_encoded => FND_API.G_FALSE,
1491 p_count => x_msg_count,
1492 p_data => x_msg_data
1493 );
1494 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1495 IBE_UTIL.debug('OTHER exception');
1496 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1497 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1498 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1499 IBE_UTIL.debug('error text : '|| SQLERRM);
1500 END IF;
1501
1502 --IBE_UTIL.disable_debug();
1503 END;
1504
1505
1506 PROCEDURE get_primary_address(
1507 p_api_version IN NUMBER,
1508 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1509 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1510 p_party_id IN NUMBER,
1511 p_site_use_type IN VARCHAR2,
1512 x_return_status OUT NOCOPY VARCHAR2,
1513 x_msg_count OUT NOCOPY NUMBER,
1514 x_msg_data OUT NOCOPY VARCHAR2,
1515 x_party_site_id OUT NOCOPY NUMBER,
1516 x_location_id OUT NOCOPY NUMBER)
1517 IS
1518
1519 l_api_name CONSTANT VARCHAR2(30) := 'get_primary_address';
1520 l_api_version CONSTANT NUMBER := 1.0;
1521
1522 BEGIN
1523
1524 --IBE_UTIL.enable_debug();
1525
1526 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1527 IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.get_primary_address');
1528 END IF;
1529
1530 -- standard start of API savepoint
1531 SAVEPOINT get_primary_address_pvt;
1532
1533 -- standard call to check for call compatibility
1534 IF NOT FND_API.compatible_api_call(l_api_version,
1535 p_api_version,
1536 l_api_name,
1537 G_PKG_NAME)
1538 THEN
1539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1540 END IF;
1541
1542 -- initialize message list if p_init_msg_list is set to TRUE.
1543 IF FND_API.to_boolean(p_init_msg_list) THEN
1544 FND_MSG_PUB.initialize;
1545 END IF;
1546
1547 BEGIN
1548 SELECT DISTINCT
1549 party_site_id, location_id
1550 INTO
1551 x_party_site_id, x_location_id
1552 FROM
1553 hz_party_sites_v
1554 WHERE
1555 party_id = p_party_id AND
1556 site_use_type = p_site_use_type AND
1557 status = 'A' AND
1558 primary_per_type = 'Y';
1559 EXCEPTION
1560 WHEN NO_DATA_FOUND THEN
1561 x_party_site_id := -1;
1562 x_location_id := -1;
1563 WHEN TOO_MANY_ROWS THEN
1564 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1565 IBE_UTIL.debug('TOO_MANY_ROWS exception found');
1566 IBE_UTIL.debug('p_party_id = ' || to_char(p_party_id));
1567 IBE_UTIL.debug('p_site_use_type = ' || p_site_use_type);
1568 END IF;
1569 x_party_site_id := -1;
1570 x_location_id := -1;
1571 END;
1572
1573 x_return_status := FND_API.G_RET_STS_SUCCESS;
1574
1575 -- standard check of p_commit
1576 IF FND_API.to_boolean(p_commit) THEN
1577 commit;
1578 END IF;
1579
1580 -- standard call to get message count and if count is 1, get message info
1581 FND_MSG_PUB.count_and_get(
1582 p_encoded => FND_API.G_FALSE,
1583 p_count => x_msg_count,
1584 p_data => x_msg_data
1585 );
1586
1587 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1588 IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.get_primary_address');
1589 END IF;
1590
1591 --IBE_UTIL.disable_debug();
1592
1593 EXCEPTION
1594 WHEN OTHERS THEN
1595 --IBE_UTIL.enable_debug();
1596
1597 ROLLBACK TO get_primary_address_pvt;
1598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1599 FND_MSG_PUB.count_and_get(
1600 p_encoded => FND_API.G_FALSE,
1601 p_count => x_msg_count,
1602 p_data => x_msg_data
1603 );
1604 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1605 IBE_UTIL.debug('OTHER exception');
1606 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
1607 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
1608 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
1609 IBE_UTIL.debug('error text : '|| SQLERRM);
1610 END IF;
1611
1612 --IBE_UTIL.disable_debug();
1613 END;
1614
1615
1616 PROCEDURE do_create_address(
1617 p_api_version IN NUMBER,
1618 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1619 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1620 p_location IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
1621 p_party_site IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
1622 p_primary_billto IN VARCHAR2 := FND_API.G_FALSE,
1623 p_primary_shipto IN VARCHAR2 := FND_API.G_FALSE,
1624 p_billto IN VARCHAR2 := FND_API.G_FALSE,
1625 p_shipto IN VARCHAR2 := FND_API.G_FALSE,
1626 p_default_primary IN VARCHAR2,
1627 x_return_status OUT NOCOPY VARCHAR2,
1631 x_party_site_id OUT NOCOPY NUMBER)
1628 x_msg_count OUT NOCOPY NUMBER,
1629 x_msg_data OUT NOCOPY VARCHAR2,
1630 x_location_id OUT NOCOPY NUMBER,
1632 IS
1633
1634 l_location HZ_LOCATION_V2PUB.LOCATION_REC_TYPE := p_location;
1635 l_party_site HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE := p_party_site;
1636
1637 l_count NUMBER;
1638 l_gen_party_site_number VARCHAR2(1);
1639 l_party_site_use HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
1640 l_party_site_number VARCHAR2(30) := p_party_site.party_site_number;
1641 l_party_site_use_id NUMBER;
1642 l_loc_id NUMBER;
1643 l_prim_site_id NUMBER;
1644 l_prim_loc_id NUMBER;
1645 l_primary_billto VARCHAR2(1) := p_primary_billto;
1646 l_primary_shipto VARCHAR2(1) := p_primary_shipto;
1647 l_createdby VARCHAR2(150) :='User Management';
1648 l_org_id NUMBER;
1649 l_addr_val VARCHAR2(30);
1650 l_lock_flag VARCHAR2(1) := FND_API.G_FALSE;
1651
1652 BEGIN
1653
1654 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1655 IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.do_create_address');
1656 END IF;
1657
1658 --
1659 -- create a location
1660 --
1661
1662 l_location.location_id := FND_API.G_MISS_NUM;
1663 l_location.address_effective_date := sysdate;
1664
1665 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1666 IBE_UTIL.debug('hz_location_v2pub.create_location(+)');
1667 END IF;
1668
1669 --V2
1670 /* HZ_location_v2pub.create_location(
1671 p_api_version,
1672 p_init_msg_list,
1673 p_commit,
1674 l_location,
1675 x_return_status,
1676 x_msg_count,
1677 x_msg_data,
1678 x_location_id
1679 );*/
1680
1681 HZ_LOCATION_V2PUB.create_location (
1682 p_init_msg_list,
1683 l_location,
1684 x_location_id,
1685 x_return_status,
1686 x_msg_count,
1687 x_msg_data
1688 );
1689
1690 l_location.location_id := x_location_id;
1691 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1692 RAISE FND_API.G_EXC_ERROR;
1693 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1695 END IF;
1696
1697 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1698 IBE_UTIL.debug('hz_location_v2pub.create_location(-)');
1699 IBE_UTIL.debug('locaiton_id = ' || x_location_id);
1700 END IF;
1701
1702
1703 --
1704 -- prepare to create a party site
1705 --
1706
1707 l_party_site.party_site_id := FND_API.G_MISS_NUM;
1708 l_party_site.location_id := x_location_id;
1709 l_party_site.status := 'A';
1710 --V2
1711 --l_party_site.start_date_active := sysdate;
1712
1713 -- if GENERATE_PARTY_SITE_NUMBER is 'N' and party site number
1714 -- is not passed it, generate from sequence till a unique value
1715 -- is obtained.
1716
1717 l_gen_party_site_number :=
1718 fnd_profile.value('HZ_GENERATE_PARTY_SITE_NUMBER');
1719
1720 IF l_gen_party_site_number = 'N' THEN
1721 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1722 IBE_UTIL.debug('HZ_GENERATE_PARTY_SITE_NUMBER is off');
1723 END IF;
1724
1725 IF l_party_site_number = FND_API.G_MISS_CHAR OR
1726 l_party_site_number IS NULL THEN
1727
1728 l_count := 1;
1729
1730 WHILE l_count > 0 LOOP
1731
1732 SELECT to_char(hz_party_site_number_s.nextval)
1733 INTO l_party_site_number
1734 FROM dual;
1735
1736 SELECT COUNT(*) INTO l_count
1737 FROM hz_party_sites_v
1738 WHERE party_site_number = l_party_site_number;
1739
1740 END LOOP;
1741
1742 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1743 IBE_UTIL.debug('party_site_number from sequence : ' || l_party_site_number);
1744 END IF;
1745
1746 l_party_site.party_site_number := l_party_site_number;
1747
1748 END IF;
1749 END IF;
1750
1751 --
1752 -- create a party site
1753 --
1754
1755 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1756 IBE_UTIL.debug('hz_party_v2pub.create_party_site(+)');
1757 END IF;
1758
1759 --V2
1760 /* HZ_PARTY_PUB.create_party_site(
1761 p_api_version,
1762 p_init_msg_list,
1763 p_commit,
1764 l_party_site,
1765 x_return_status,
1766 x_msg_count,
1767 x_msg_data,
1768 x_party_site_id,
1769 l_party_site_number
1770 );
1771 */
1772
1773 HZ_PARTY_SITE_V2PUB.create_party_site (
1774 p_init_msg_list,
1775 l_party_site,
1776 x_party_site_id,
1777 l_party_site_number,
1778 x_return_status,
1779 x_msg_count,
1780 x_msg_data
1781 );
1782
1783 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1784 RAISE FND_API.G_EXC_ERROR;
1785 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1787 END IF;
1788
1789 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1790 IBE_UTIL.debug('hz_party_site_v2pub.create_party_site(-)');
1791 IBE_UTIL.debug('party_site_id = ' || x_party_site_id);
1792 END IF;
1793
1794 --
1795 -- if p_default_primary is true, create a primary address
1796 -- if user does not already have one.
1797 --
1798
1802
1799 IF p_default_primary = FND_API.G_TRUE THEN
1800
1801 -- check primary billing address
1803 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1804 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.get_primary_address(+)');
1805 END IF;
1806
1807 IBE_ADDRESS_V2PVT.get_primary_address(
1808 p_api_version,
1809 p_init_msg_list,
1810 p_commit,
1811 l_party_site.party_id,
1812 'BILL_TO',
1813 x_return_status,
1814 x_msg_count,
1815 x_msg_data,
1816 l_prim_site_id,
1817 l_prim_loc_id
1818 );
1819
1820 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1821 RAISE FND_API.G_EXC_ERROR;
1822 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1824 END IF;
1825
1826 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1827 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.get_primary_address(-)');
1828 IBE_UTIL.debug('l_prim_site_id = ' || l_prim_site_id);
1829 END IF;
1830
1831 IF (l_prim_site_id = -1) THEN
1832 -- no primary billing address
1833 l_primary_billto := FND_API.G_TRUE;
1834 END IF;
1835
1836 -- check primary shipping address
1837
1838 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1839 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.get_primary_address(+)');
1840 END IF;
1841
1842 IBE_ADDRESS_V2PVT.get_primary_address(
1843 p_api_version,
1844 p_init_msg_list,
1845 p_commit,
1846 l_party_site.party_id,
1847 'SHIP_TO',
1848 x_return_status,
1849 x_msg_count,
1850 x_msg_data,
1851 l_prim_site_id,
1852 l_prim_loc_id
1853 );
1854
1855 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1856 RAISE FND_API.G_EXC_ERROR;
1857 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1859 END IF;
1860
1861 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1862 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.get_primary_address(-)');
1863 IBE_UTIL.debug('l_prim_site_id = ' || l_prim_site_id);
1864 END IF;
1865
1866 IF (l_prim_site_id = -1) THEN
1867 -- no primary shipping address
1868 l_primary_shipto := FND_API.G_TRUE;
1869 END IF;
1870
1871 END IF;
1872
1873
1874 --
1875 -- create a party site use for bill to or ship to
1876 --
1877
1878 IF (l_primary_billto = FND_API.G_TRUE OR p_billto = FND_API.G_TRUE)THEN
1879
1880 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1881 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.set_primary_address(+) for BILL_TO');
1882 END IF;
1883 l_createdby := l_location.created_by_module;
1884 IBE_ADDRESS_V2PVT.set_address_usage(
1885 p_api_version,
1886 p_init_msg_list,
1887 P_commit,
1888 x_party_site_id,
1889 l_primary_billto,
1890 'BILL_TO',
1891 l_createdby,
1892 x_return_status,
1893 x_msg_count,
1894 x_msg_data,
1895 l_party_site_use_id
1896 );
1897
1898 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1899 RAISE FND_API.G_EXC_ERROR;
1900 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1901 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1902 END IF;
1903
1904 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1905 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.set_primary_address(-)');
1906 IBE_UTIL.debug('party_site_use_id = ' || l_party_site_use_id);
1907 END IF;
1908
1909 END IF;
1910
1911 IF (l_primary_shipto = FND_API.G_TRUE OR p_shipto = FND_API.G_TRUE) THEN
1912 l_createdby := l_location.created_by_module;
1913 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1914 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.set_primary_address(+) for SHIP_TO');
1915 END IF;
1916 IBE_ADDRESS_V2PVT.set_address_usage(
1917 p_api_version,
1918 p_init_msg_list,
1919 P_commit,
1920 x_party_site_id,
1921 l_primary_shipto,
1922 'SHIP_TO',
1923 l_createdby,
1924 x_return_status,
1925 x_msg_count,
1926 x_msg_data,
1927 l_party_site_use_id
1928 );
1929
1930 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1931 RAISE FND_API.G_EXC_ERROR;
1932 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1933 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1934 END IF;
1935
1936 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1937 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.set_address_usage(-)');
1938 IBE_UTIL.debug('party_site_use_id = ' || l_party_site_use_id);
1939 END IF;
1940
1941 END IF;
1942
1943
1944 --
1945 -- For debug purpose
1946 --
1947 l_org_id := MO_GLOBAL.get_current_org_id();
1948
1949 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1950 IBE_UTIL.debug('Current user org id = ' || l_org_id);
1951 IBE_UTIL.debug('=== Checking system parameters ===');
1952 --IBE_UTIL.debug('sysparm.org_id = ' || to_char(arp_standard.sysparm.org_id));
1953 --IBE_UTIL.debug('sysparm.default_country = ' || arp_standard.sysparm.default_country);
1954 IBE_UTIL.debug('country = ' || p_location.country);
1955 IBE_UTIL.debug('address1 = ' || p_location.address1);
1956 IBE_UTIL.debug('city = ' || p_location.city);
1957 IBE_UTIL.debug('county = ' || p_location.county);
1958 IBE_UTIL.debug('state = ' || p_location.state);
1959 IBE_UTIL.debug('postal_code = ' || p_location.postal_code);
1960 END IF;
1961 --
1962 -- End of debug purpose
1963 --
1964
1965 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1969 HZ_TAX_ASSIGNMENT_V2PUB.create_loc_assignment(
1966 IBE_UTIL.debug('hz_tax_assignment_pub.create_loc_assignment(+)');
1967 END IF;
1968
1970 p_init_msg_list,
1971 l_location.location_id,
1972 l_lock_flag,
1973 l_location.created_by_module,
1974 l_location.application_id,
1975 x_return_status,
1976 x_msg_count,
1977 x_msg_data,
1978 l_loc_id
1979 );
1980 --V2
1981 /* HZ_TAX_ASSIGNMENT_PUB.create_loc_assignment(
1982 p_api_version,
1983 p_init_msg_list,
1984 p_commit,
1985 x_location_id,
1986 x_return_status,
1987 x_msg_count,
1988 x_msg_data,
1989 l_loc_id,
1990 FND_API.G_TRUE
1991 );
1992 */
1993 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1994 RAISE FND_API.G_EXC_ERROR;
1995 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1997 END IF;
1998
1999 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2000 IBE_UTIL.debug('hz_tax_assignment_pub.create_loc_assignment(-)');
2001 IBE_UTIL.debug('loc_id = ' || to_char(l_loc_id));
2002 END IF;
2003
2004 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2005 IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.do_create_address');
2006 END IF;
2007
2008 END;
2009
2010 PROCEDURE do_delete_address(
2011 p_api_version IN NUMBER,
2012 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2013 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2014 p_party_id IN NUMBER,
2015 p_party_site_id IN NUMBER,
2016 p_ps_object_version_number IN NUMBER,
2017 p_bill_object_version_number IN NUMBER,
2018 p_ship_object_version_number IN NUMBER,
2019 x_return_status OUT NOCOPY VARCHAR2,
2020 x_msg_count OUT NOCOPY NUMBER,
2021 x_msg_data OUT NOCOPY VARCHAR2)
2022 IS
2023
2024 cursor c_psu is
2025 select party_site_use_id,site_use_type,object_version_number
2026 from hz_party_site_uses
2027 where party_site_id = p_party_site_id
2028 for update nowait;
2029
2030 l_api_name CONSTANT VARCHAR2(30) := 'delete_address';
2031 l_api_version CONSTANT NUMBER := 1.0;
2032 l_party_site HZ_PARTY_SITE_V2PUB.party_site_rec_type;
2033 l_party_site_use HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
2034 l_party_site_use_id NUMBER;
2035 l_site_use_type VARCHAR(30);
2036 l_ps_object_version_number NUMBER := p_ps_object_version_number;
2037 l_psu_object_version_number NUMBER;
2038
2039 BEGIN
2040
2041 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2042 IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.do_delete_address');
2043 END IF;
2044
2045 --
2046 -- update party site
2047 --
2048
2049 l_party_site.party_site_id := p_party_site_id;
2050 l_party_site.status := 'I';
2051 l_party_site_use.status := 'I';
2052 --fix bug 3382268
2053 l_party_site_use.primary_per_type := 'N';
2054
2055 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2056 IBE_UTIL.debug('hz_party_v2pub.update_party_site(+)');
2057 END IF;
2058
2059 HZ_PARTY_SITE_V2PUB.update_party_site (
2060 p_init_msg_list,
2061 l_party_site,
2062 l_ps_object_version_number,
2063 x_return_status,
2064 x_msg_count,
2065 x_msg_data);
2066
2067 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2068 IBE_UTIL.debug('hz_party_v2pub.update_party_site(-)');
2069 IBE_UTIL.debug('hz_party_v2pub.update_party_site_use(+)');
2070 END IF;
2071 --delete all psu for a given party site that's to be deleted
2072 open c_psu;
2073 Loop
2074 fetch c_psu into l_party_site_use_id,l_site_use_type,l_psu_object_version_number;
2075 EXIT When c_psu%NOTFOUND;
2076 l_party_site_use.party_site_use_id := l_party_site_use_id;
2077 /*if l_site_use_type = 'BILL_TO' then
2078 l_psu_object_version_number := p_bill_object_version_number;
2079 elsif l_site_use_type = 'SHIP_TO' then
2080 l_psu_object_version_number := p_ship_object_version_number;
2081 end if;
2082 */
2083 HZ_PARTY_SITE_V2PUB.update_party_site_use(
2084 p_init_msg_list,
2085 l_party_site_use,
2086 l_psu_object_version_number,
2087 x_return_status,
2088 x_msg_count,
2089 x_msg_data
2090 );
2091 End Loop;
2092 close c_psu;
2093
2094 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2095 IBE_UTIL.debug('hz_party_v2pub.update_party_site_use(-)');
2096 END IF;
2097
2098 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2099 RAISE FND_API.G_EXC_ERROR;
2100 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2102 END IF;
2103
2104 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2105 IBE_UTIL.debug('hz_party_v2pub.update_party_site(-)');
2106 IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.do_delete_address');
2107 END IF;
2108
2109 END;
2110
2111 --
2112 -- Check if the user input has changed from the database
2113 --
2114 FUNCTION is_location_changed(
2115 p_location IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
2116 ) RETURN BOOLEAN
2117 IS
2118 l_changed BOOLEAN := false;
2119 l_dummy NUMBER;
2120 l_gmiss VARCHAR2(1) := NULL;
2121 BEGIN
2122
2123 l_gmiss := FND_API.G_MISS_CHAR;
2124 -- for debug purpose
2125 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2126 IBE_UTIL.debug('location_id = ' || p_location.location_id || '.');
2130 IBE_UTIL.debug('address4 = ' || p_location.address4 || '.');
2127 IBE_UTIL.debug('address1 = ' || p_location.address1 || '.');
2128 IBE_UTIL.debug('address2 = ' || p_location.address2 || '.');
2129 IBE_UTIL.debug('address3 = ' || p_location.address3 || '.');
2131 IBE_UTIL.debug('city = ' || p_location.city || '.');
2132 IBE_UTIL.debug('county = ' || p_location.county || '.');
2133 IBE_UTIL.debug('province = ' || p_location.province || '.');
2134 IBE_UTIL.debug('state = ' || p_location.state || '.');
2135 IBE_UTIL.debug('postal_code = ' || p_location.postal_code || '.');
2136 IBE_UTIL.debug('country = ' || p_location.country || '.');
2137 IBE_UTIL.debug('address_lines_phonetic = ' || p_location.address_lines_phonetic || '.');
2138 END IF;
2139
2140 BEGIN
2141 SELECT location_id INTO l_dummy
2142 FROM hz_locations
2143 WHERE location_id = p_location.location_id
2144 AND nvl(address1, l_gmiss) = nvl(p_location.address1, l_gmiss)
2145 AND nvl(address2, l_gmiss) = nvl(p_location.address2, l_gmiss)
2146 AND nvl(address3, l_gmiss) = nvl(p_location.address3, l_gmiss)
2147 AND nvl(address4, l_gmiss) = nvl(p_location.address4, l_gmiss)
2148 AND nvl(city, l_gmiss) = nvl(p_location.city, l_gmiss)
2149 AND nvl(county, l_gmiss) = nvl(p_location.county, l_gmiss)
2150 AND nvl(province, l_gmiss) = nvl(p_location.province, l_gmiss)
2151 AND nvl(state, l_gmiss) = nvl(p_location.state, l_gmiss)
2152 AND nvl(postal_code, l_gmiss) = nvl(p_location.postal_code, l_gmiss)
2153 AND nvl(country, l_gmiss) = nvl(p_location.country, l_gmiss)
2154 AND nvl(address_lines_phonetic, l_gmiss) = nvl(p_location.address_lines_phonetic, l_gmiss);
2155 EXCEPTION
2156 WHEN NO_DATA_FOUND THEN
2157 l_changed := true;
2158 END;
2159
2160 IF l_changed THEN
2161 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2162 IBE_UTIL.debug('location is changed');
2163 END IF;
2164 ELSE
2165 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2166 IBE_UTIL.debug('location is not changed');
2167 END IF;
2168 END IF;
2169
2170 return l_changed;
2171 END;
2172
2173
2174 --
2175 -- Check if the user input has changed from the database
2176 --
2177 FUNCTION is_party_site_changed(
2178 p_party_site IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
2179 ) RETURN BOOLEAN
2180 IS
2181 l_changed BOOLEAN := false;
2182 l_dummy NUMBER;
2183 l_gmiss VARCHAR2(1) := NULL;
2184 BEGIN
2185
2186 l_gmiss := FND_API.G_MISS_CHAR;
2187 -- for debug purpose
2188 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2189 IBE_UTIL.debug('party_site_id = ' || p_party_site.party_site_id || '.');
2190 IBE_UTIL.debug('party_site_name = ' || p_party_site.party_site_name || '.');
2191 IBE_UTIL.debug('addressee = ' || p_party_site.addressee || '.');
2192 END IF;
2193
2194 BEGIN
2195 SELECT party_site_id INTO l_dummy
2196 FROM hz_party_sites
2197 WHERE party_site_id = p_party_site.party_site_id
2198 AND nvl(party_site_name,l_gmiss)=nvl(p_party_site.party_site_name,l_gmiss)
2199 AND nvl(addressee,l_gmiss)= nvl(p_party_site.addressee,l_gmiss)
2200 AND (identifying_address_flag = 'Y' or p_party_site.identifying_address_flag='N');
2201 EXCEPTION
2202 WHEN NO_DATA_FOUND THEN
2203 l_changed := true;
2204 END;
2205
2206 IF l_changed THEN
2207 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2208 IBE_UTIL.debug('party_site is changed');
2209 END IF;
2210 ELSE
2211 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2212 IBE_UTIL.debug('party_site is not changed');
2213 END IF;
2214 END IF;
2215
2216 return l_changed;
2217 END;
2218
2219
2220 --
2221 -- Check if update is necessary for party_site_use
2222 --
2223
2224 FUNCTION is_party_site_use_changed(
2225 p_party_site_use IN OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE
2226 ) RETURN VARCHAR
2227 IS
2228 l_psu_id NUMBER := NULL;
2229 --T is changed, F is not changed, N is not found
2230 --default value is no change
2231 l_status VARCHAR(1) := 'F';
2232 BEGIN
2233
2234 --
2235 -- check if a party_site_use exists for the given usage
2236 --
2237 BEGIN
2238 SELECT party_site_use_id
2239 INTO l_psu_id
2240 FROM ( SELECT distinct party_site_use_id,status FROM hz_party_site_uses
2241 WHERE site_use_type = p_party_site_use.site_use_type
2242 AND party_site_id = p_party_site_use.party_site_id
2243 AND (primary_per_type = 'N' OR primary_per_type = 'Y')
2244 ORDER BY status,party_site_use_id desc)
2245 WHERE rownum=1;
2246
2247 p_party_site_use.party_site_use_id := l_psu_id;
2248 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2249 IBE_UTIL.debug('assign party site use ID: '||p_party_site_use.party_site_use_id);
2250 END IF;
2251 EXCEPTION
2252 WHEN NO_DATA_FOUND THEN
2253 l_status := 'N';
2254 END;
2255
2256
2257 IF l_status = 'N' THEN
2258 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2259 IBE_UTIL.debug('party_site_use is not found');
2260 END IF;
2261 ELSE
2262 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2263 IBE_UTIL.debug('party_site_use is found');
2264 END IF;
2265 BEGIN
2266 SELECT distinct party_site_use_id INTO l_psu_id
2267 FROM hz_party_site_uses
2268 WHERE NVL(status, 'A') = p_party_site_use.status
2269 AND party_site_use_id = p_party_site_use.party_site_use_id
2270 AND primary_per_type = p_party_site_use.primary_per_type;
2271 EXCEPTION
2275 IBE_UTIL.debug('l_status is set to true');
2272 WHEN NO_DATA_FOUND THEN
2273 l_status := 'T';
2274 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2276 END IF;
2277 END;
2278 END IF;
2279
2280 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2281 IBE_UTIL.debug('l_status is: '||l_status);
2282 END IF;
2283 return l_status;
2284 END;
2285
2286
2287
2288 PROCEDURE valid_usages (
2289 p_api_version IN NUMBER,
2290 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2291 p_party_site_id IN NUMBER,
2292 p_operating_unit_id IN NUMBER,
2293 p_usage_codes IN JTF_VARCHAR2_TABLE_100,
2294 x_return_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2295 x_return_status OUT NOCOPY VARCHAR2,
2296 x_msg_count OUT NOCOPY NUMBER,
2297 x_msg_data OUT NOCOPY VARCHAR2)
2298 IS
2299
2300 l_api_name CONSTANT VARCHAR2(30) := 'Valid_Usages';
2301 l_api_version CONSTANT NUMBER := 1.0;
2302
2303 l_psite_terr_code VARCHAR2(30);
2304 l_flag BOOLEAN;
2305
2306 CURSOR c1(l_c_party_site_id IN NUMBER)
2307 IS SELECT country FROM hz_locations
2308 WHERE location_id IN
2309 (SELECT location_id FROM hz_party_sites
2310 WHERE party_site_id = l_c_party_site_id);
2311
2312 CURSOR c2(l_c_operating_unit_id IN NUMBER, l_c_usage_code IN VARCHAR2)
2313 IS SELECT org_information1 FROM hr_organization_information
2314 WHERE organization_id = l_c_operating_unit_id
2315 AND org_information_context = l_c_usage_code;
2316
2317 BEGIN
2318
2319 --IBE_UTIL.enable_debug();
2320
2321 -- Standard call to check for call compatibility.
2322 IF NOT FND_API.Compatible_API_Call(l_api_version,
2323 p_api_version,
2324 l_api_name,
2325 G_PKG_NAME)
2326 THEN
2327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2328 END IF;
2329
2330 -- Initialize message list if p_init_msg_list is set to TRUE.
2331 IF FND_API.to_Boolean(p_init_msg_list) THEN
2332 FND_MSG_PUB.initialize;
2333 END IF;
2334
2335 -- Initialize API return status to success
2336 x_return_status := FND_API.G_RET_STS_SUCCESS;
2337
2338 --
2339 -- Get the country associated with the party site
2340 --
2341 OPEN c1(p_party_site_id);
2342 FETCH c1 INTO l_psite_terr_code;
2343 IF (c1%NOTFOUND) THEN
2344 CLOSE c1;
2345 FND_MESSAGE.Set_Name('IBE', 'IBE_OU_GET_PSITE_COUNTRY_FAIL');
2346 FND_MESSAGE.Set_Token('PARTY_SITE_ID', p_party_site_id);
2347 FND_MSG_PUB.Add;
2348 RAISE FND_API.G_EXC_ERROR;
2349 END IF;
2350 CLOSE c1;
2351
2352 x_return_codes := JTF_VARCHAR2_TABLE_100();
2353 FOR i IN 1..p_usage_codes.COUNT LOOP
2354
2355 x_return_codes.EXTEND();
2356 x_return_codes(i) := FND_API.G_RET_STS_ERROR;
2357
2358 --
2359 -- Process SHIP_TO_COUNTRY usage code
2360 --
2361
2362 -- Set flag to false. l_flag is used to check if there is at least one
2363 -- entry for ship to countries. If flag is true, then there is at least
2364 -- one entry in ship to countries.
2365 l_flag := FALSE;
2366
2367 IF (p_usage_codes(i) = 'SHIP_TO_COUNTRY') THEN
2368
2369 FOR r2 IN c2(p_operating_unit_id, p_usage_codes(i)) LOOP
2370
2371 IF (l_flag = FALSE) THEN
2372 l_flag := TRUE;
2373 END IF;
2374
2375 IF (r2.org_information1 = l_psite_terr_code) THEN
2376 x_return_codes(i) := FND_API.G_RET_STS_SUCCESS;
2377 EXIT;
2378 END IF;
2379
2380 END LOOP; -- end for r2
2381
2382 IF (l_flag = FALSE) THEN
2383 x_return_codes(i) := FND_API.G_RET_STS_SUCCESS;
2384 END IF;
2385
2386 END IF; -- end if SHIP_TO_COUNTRY
2387
2388
2389 --
2390 -- Process BILL_TO_COUNTRY usage code
2391 --
2392
2393 -- Set flag to false. l_flag is used to check if there is at least one
2394 -- entry for bill to countries. If flag is true, then there is at least
2395 -- one entry in bill to countries.
2396 l_flag := FALSE;
2397
2398 IF (p_usage_codes(i) = 'BILL_TO_COUNTRY') THEN
2399
2400 FOR r2 IN c2(p_operating_unit_id, p_usage_codes(i)) LOOP
2401
2402 IF (l_flag = FALSE) THEN
2403 l_flag := TRUE;
2404 END IF;
2405
2406 IF (r2.org_information1 = l_psite_terr_code) THEN
2407 x_return_codes(i) := FND_API.G_RET_STS_SUCCESS;
2408 EXIT;
2409 END IF;
2410
2411 END LOOP; -- end for r2
2412
2413 IF (l_flag = FALSE) THEN
2414 x_return_codes(i) := FND_API.G_RET_STS_SUCCESS;
2415 END IF;
2416
2417 END IF; -- end if BILL_TO_COUNTRY
2418
2419 END LOOP; -- end for i
2420
2421 --IBE_UTIL.disable_debug();
2422
2423 EXCEPTION
2424
2425 WHEN FND_API.G_EXC_ERROR THEN
2426 x_return_status := FND_API.G_RET_STS_ERROR;
2427 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2428 p_data => x_msg_data,
2429 p_encoded => 'F');
2430
2431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2433 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2434 p_data => x_msg_data,
2435 p_encoded => 'F');
2436
2437 WHEN OTHERS THEN
2441 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2438 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2439 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2440 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2442 FND_MSG_PUB.Add;
2443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2444
2445 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2446 THEN
2447 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2448 END IF;
2449
2450 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2451 p_data => x_msg_data,
2452 p_encoded => 'F');
2453
2454 END;
2455
2456 PROCEDURE copy_party_site (
2457 p_api_version IN NUMBER,
2458 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2459 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2460 p_party_site IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
2461 p_location IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
2462 x_party_site_id OUT NOCOPY NUMBER,
2463 x_return_status OUT NOCOPY VARCHAR2,
2464 x_msg_count OUT NOCOPY NUMBER,
2465 x_msg_data OUT NOCOPY VARCHAR2)
2466 IS
2467
2468 l_party_site_id NUMBER;
2469 l_party_site_number NUMBER;
2470 lx_msg_data VARCHAR2(2000);
2471 l_api_name CONSTANT VARCHAR2(30) := 'copy_party_site';
2472
2473 BEGIN
2474
2475 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2476 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.copy_pary_site:Begin ');
2477 END IF;
2478 /*PROCEDURE create_party_site (
2479 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2480 p_party_site_rec IN PARTY_SITE_REC_TYPE,
2481 x_party_site_id OUT NOCOPY NUMBER,
2482 x_party_site_number OUT NOCOPY VARCHAR2,
2483 x_return_status OUT NOCOPY VARCHAR2,
2484 x_msg_count OUT NOCOPY NUMBER,
2485 x_msg_data OUT NOCOPY VARCHAR2
2486 );
2487 */
2488
2489 HZ_PARTY_SITE_V2PUB.create_party_site (
2490 p_init_msg_list => p_init_msg_list,
2491 p_party_site_rec => p_party_site,
2492 x_party_site_id => l_party_site_id,
2493 x_party_site_number => l_party_site_number,
2494 x_return_status => x_return_status,
2495 x_msg_count => x_msg_count,
2496 x_msg_data => x_msg_data );
2497
2498 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2499 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2500 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.copy_pary_site:Expected error ');
2501 END IF;
2502
2503 RAISE FND_API.G_EXC_ERROR;
2504 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2505 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2506 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.copy_pary_site:Unexpected error ');
2507 END IF;
2508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2509 END IF;
2510
2511 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2512 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.copy_pary_site:Done ');
2513 IBE_UTIL.debug('party_site_id = ' || x_party_site_id);
2514 END IF;
2515
2516 x_party_site_id := l_party_site_id;
2517
2518 EXCEPTION
2519
2520 WHEN FND_API.G_EXC_ERROR THEN
2521 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2522 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.copy_pary_site:In the expected exception block ');
2523 END IF;
2524
2525 x_return_status := FND_API.G_RET_STS_ERROR;
2526 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2527 p_data => x_msg_data,
2528 p_encoded => 'F');
2529
2530 for k in 1 .. x_msg_count loop
2531 lx_msg_data := fnd_msg_pub.get( p_msg_index => k,
2532 p_encoded => 'F');
2533
2534 IBE_UTIL.debug('Error msg: '||substr(lx_msg_data,1,240));
2535 end loop;
2536
2537
2538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2539 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2540 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.copy_pary_site:In the unexpected exception block ');
2541 END IF;
2542
2543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2544 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2545 p_data => x_msg_data,
2546 p_encoded => 'F');
2547
2548 for k in 1 .. x_msg_count loop
2549 lx_msg_data := fnd_msg_pub.get( p_msg_index => k,
2550 p_encoded => 'F');
2551
2552 IBE_UTIL.debug('Error msg: '||substr(lx_msg_data,1,240));
2553 end loop;
2554
2555
2556 WHEN OTHERS THEN
2557 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2558 IBE_UTIL.debug('IBE_ADDRESS_V2PVT.copy_pary_site:In the others exception block ');
2559 END IF;
2560
2561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2562
2563 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2564 THEN
2565 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2566 END IF;
2567
2568 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2569 p_data => x_msg_data,
2570 p_encoded => 'F');
2571 for k in 1 .. x_msg_count loop
2572 lx_msg_data := fnd_msg_pub.get( p_msg_index => k,
2573 p_encoded => 'F');
2574
2575 IBE_UTIL.debug('Error msg: '||substr(lx_msg_data,1,240));
2576 end loop;
2577
2578
2579 END;
2580
2581
2582 END IBE_ADDRESS_V2PVT;