[Home] [Help]
PACKAGE BODY: APPS.HZ_LOCATION_V2PUB
Source
1 PACKAGE BODY hz_location_v2pub AS
2 /*$Header: ARH2LOSB.pls 120.35 2006/07/19 23:30:25 baianand noship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 g_debug_count NUMBER := 0;
9 --g_debug BOOLEAN := FALSE;
10
11 -- Bug 2197181: added for mix-n-match project.
12
13 g_loc_mixnmatch_enabled VARCHAR2(1);
14 g_loc_selected_datasources VARCHAR2(255);
15 g_loc_is_datasource_selected VARCHAR2(1) := 'N';
16 g_loc_entity_attr_id NUMBER;
17
18 --------------------------------------
19 -- declaration of private procedures and functions
20 --------------------------------------
21
22 /*PROCEDURE enable_debug;
23
24 PROCEDURE disable_debug;
25 */
26
27
28 PROCEDURE do_create_location(
29 p_location_rec IN OUT NOCOPY location_rec_type,
30 x_location_id OUT NOCOPY NUMBER,
31 x_return_status IN OUT NOCOPY VARCHAR2
32 );
33
34 -- Modified the below procedure to add new parameters for address validation.
35 -- This is for bug # 4652309. The new parameters will be passed thro the
36 -- new update_location overloaded API.
37 PROCEDURE do_update_location(
38 p_location_rec IN OUT NOCOPY location_rec_type,
39 p_do_addr_val IN VARCHAR2,
40 p_object_version_number IN OUT NOCOPY NUMBER,
41 x_addr_val_status OUT NOCOPY VARCHAR2,
42 x_addr_warn_msg OUT NOCOPY VARCHAR2,
43 x_return_status IN OUT NOCOPY VARCHAR2
44 );
45
46 PROCEDURE fill_geometry(
47 p_loc_rec IN OUT NOCOPY location_rec_type,
48 x_return_status OUT NOCOPY VARCHAR2
49 );
50
51 PROCEDURE update_location_search(
52 p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
53 p_new_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
54 );
55
56 FUNCTION isModified(p_old_value IN VARCHAR2,
57 p_new_value IN VARCHAR2
58 ) RETURN BOOLEAN;
59
60 PROCEDURE check_obsolete_columns (
61 p_create_update_flag IN VARCHAR2,
62 p_location_rec IN location_rec_type,
63 p_old_location_rec IN location_rec_type DEFAULT NULL,
64 x_return_status IN OUT NOCOPY VARCHAR2
65 );
66
67 --------------------------------------
68 -- private procedures and functions
69 --------------------------------------
70
71 /**
72 * PRIVATE PROCEDURE enable_debug
73 *
74 * DESCRIPTION
75 * Turn on debug mode.
76 *
77 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
78 * HZ_UTILITY_V2PUB.enable_debug
79 *
80 * MODIFICATION HISTORY
81 *
82 * 07-23-2001 Jianying Huang o Created.
83 *
84 */
85
86 /*PROCEDURE enable_debug IS
87 BEGIN
88 g_debug_count := g_debug_count + 1;
89
90 IF g_debug_count = 1 THEN
91 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
92 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
93 THEN
94 hz_utility_v2pub.enable_debug;
95 g_debug := TRUE;
96 END IF;
97 END IF;
98 END enable_debug;
99 */
100
101
102 /**
103 * PRIVATE PROCEDURE disable_debug
104 *
105 * DESCRIPTION
106 * Turn off debug mode.
107 *
108 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
109 * HZ_UTILITY_V2PUB.disable_debug
110 *
111 * MODIFICATION HISTORY
112 *
113 * 07-23-2001 Jianying Huang o Created.
114 *
115 */
116
117 /*PROCEDURE disable_debug IS
118 BEGIN
119 IF g_debug THEN
120 g_debug_count := g_debug_count - 1;
121 IF g_debug_count = 0 THEN
122 hz_utility_v2pub.disable_debug;
123 g_debug := FALSE;
124 END IF;
125 END IF;
126 END disable_debug;
127 */
128
129 PROCEDURE do_create_location (
130 p_location_rec IN OUT NOCOPY location_rec_type,
131 x_location_id OUT NOCOPY NUMBER,
132 x_return_status IN OUT NOCOPY VARCHAR2
133 ) IS
134 l_rowid ROWID := NULL;
135 l_key VARCHAR2(2000);
136 l_dummy VARCHAR2(1);
137 l_debug_prefix VARCHAR2(30) := '';
138 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
139 l_msg_count number;
140 l_message_count NUMBER;
141 l_msg_data varchar2(2000);
142 l_return_status varchar2(1);
143 l_timezone_id number;
144
145 -- ACNG add call to location profile: BEGIN
146 l_location_profile_id NUMBER := NULL;
147 l_prov_state_admin_code VARCHAR2(60);
148 l_end_date DATE;
149 -- ACNG add call to location profile: END
150
151 CURSOR val IS
152 SELECT 'Y'
153 FROM hz_locations hl
154 WHERE hl.location_id = p_location_rec.location_id;
155 BEGIN
156 -- Debug info.
157 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
158 hz_utility_v2pub.debug(p_message=>'do_create_location (+)',
159 p_prefix=>l_debug_prefix,
160 p_msg_level=>fnd_log.level_procedure);
161 END IF;
162
163 --If primary key value is passed, check for uniqueness.
164 IF p_location_rec.location_id IS NOT NULL AND
165 p_location_rec.location_id <> fnd_api.g_miss_num
166 THEN
167 -- J. del Callar: changed from select...into to a cursor. It's faster
168 -- for the default condition, which is no duplicates found.
169 OPEN val;
170 FETCH val INTO l_dummy;
171 IF val%FOUND THEN
172 CLOSE val;
173 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
174 fnd_message.set_token('COLUMN', 'location_id');
175 fnd_msg_pub.add;
176 RAISE fnd_api.g_exc_error;
177 END IF;
178 CLOSE val;
179 END IF;
180
181 -- validate the input record
182 hz_registry_validate_v2pub.validate_location(
183 'C',
184 p_location_rec,
185 l_rowid,
186 x_return_status
187 );
188
189 IF x_return_status = fnd_api.g_ret_sts_error THEN
190 RAISE fnd_api.g_exc_error;
191 END IF;
192
193 -- call address key generation program
194 l_key := hz_fuzzy_pub.generate_key (
195 'ADDRESS',
196 NULL,
197 p_location_rec.address1,
198 p_location_rec.address2,
199 p_location_rec.address3,
200 p_location_rec.address4,
201 p_location_rec.postal_code,
202 NULL,
203 NULL
204 );
205
206 -- Debug info.
207 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
208 hz_utility_v2pub.debug(p_message=>'Key Generated : '||l_key,
209 p_prefix =>l_debug_prefix,
210 p_msg_level=>fnd_log.level_statement);
211 END IF;
212
213 p_location_rec.address_key := l_key;
214
215 -- Debug info.
216 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
217 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.insert_row (+)',
218 p_prefix=>l_debug_prefix,
219 p_msg_level=>fnd_log.level_procedure);
220 END IF;
221
222 -- this is for handling orig_system_reference defaulting
223 IF p_location_rec.location_id = fnd_api.g_miss_num THEN
224 p_location_rec.location_id := NULL;
225 END IF;
226
227 -- Debug info.
228 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
229 hz_utility_v2pub.debug(p_message=>'p_location_rec.actual_content_source = '||
230 p_location_rec.actual_content_source,
231 p_prefix =>l_debug_prefix,
232 p_msg_level=>fnd_log.level_statement);
233 END IF;
234
235 if p_location_rec.timezone_id is null or
236 p_location_rec.timezone_id = fnd_api.g_miss_num
237 then
238 l_message_count := fnd_msg_pub.count_msg();
239 hz_timezone_pub.get_timezone_id(
240 p_api_version => 1.0,
241 p_init_msg_list => FND_API.G_FALSE,
242 p_postal_code => p_location_rec.postal_code,
243 p_city => p_location_rec.city,
244 p_state => p_location_rec.state,
245 p_country => p_location_rec.country,
246 x_timezone_id => l_timezone_id,
247 x_return_status => l_return_status ,
248 x_msg_count =>l_msg_count ,
249 x_msg_data => l_msg_data);
250 if l_return_status <> fnd_api.g_ret_sts_success
251 then -- we don't raise error
252 l_timezone_id := null;
253 FOR i IN 1..(l_msg_count - l_message_count) LOOP
254 fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
255 END LOOP;
256 l_return_status := FND_API.G_RET_STS_SUCCESS;
257 end if;
258 -- fix for bug # 5286032.
259 -- the above derived timezone_id was not passing into the insert_row procedure.
260 p_location_rec.timezone_id := l_timezone_id;
261 end if;
262
263
264 -- call table-handler to insert the record
265 hz_locations_pkg.insert_row (
266 x_location_id => p_location_rec.location_id,
267 x_attribute_category => p_location_rec.attribute_category,
268 x_attribute1 => p_location_rec.attribute1,
269 x_attribute2 => p_location_rec.attribute2,
270 x_attribute3 => p_location_rec.attribute3,
271 x_attribute4 => p_location_rec.attribute4,
272 x_attribute5 => p_location_rec.attribute5,
273 x_attribute6 => p_location_rec.attribute6,
274 x_attribute7 => p_location_rec.attribute7,
275 x_attribute8 => p_location_rec.attribute8,
276 x_attribute9 => p_location_rec.attribute9,
277 x_attribute10 => p_location_rec.attribute10,
278 x_attribute11 => p_location_rec.attribute11,
279 x_attribute12 => p_location_rec.attribute12,
280 x_attribute13 => p_location_rec.attribute13,
281 x_attribute14 => p_location_rec.attribute14,
282 x_attribute15 => p_location_rec.attribute15,
283 x_attribute16 => p_location_rec.attribute16,
284 x_attribute17 => p_location_rec.attribute17,
285 x_attribute18 => p_location_rec.attribute18,
286 x_attribute19 => p_location_rec.attribute19,
287 x_attribute20 => p_location_rec.attribute20,
288 x_orig_system_reference => p_location_rec.orig_system_reference,
289 x_country => p_location_rec.country,
290 x_address1 => p_location_rec.address1,
291 x_address2 => p_location_rec.address2,
292 x_address3 => p_location_rec.address3,
293 x_address4 => p_location_rec.address4,
294 x_city => p_location_rec.city,
295 x_postal_code => p_location_rec.postal_code,
296 x_state => p_location_rec.state,
297 x_province => p_location_rec.province,
298 x_county => p_location_rec.county,
299 x_address_key => p_location_rec.address_key,
300 x_address_style => p_location_rec.address_style,
301 x_validated_flag => p_location_rec.validated_flag,
302 x_address_lines_phonetic => p_location_rec.address_lines_phonetic,
303 x_po_box_number => p_location_rec.po_box_number,
304 x_house_number => p_location_rec.house_number,
305 x_street_suffix => p_location_rec.street_suffix,
306 x_street => p_location_rec.street,
307 x_street_number => p_location_rec.street_number,
308 x_floor => p_location_rec.floor,
309 x_suite => p_location_rec.suite,
310 x_postal_plus4_code => p_location_rec.postal_plus4_code,
311 x_position => p_location_rec.position,
312 x_location_directions => p_location_rec.location_directions,
313 x_address_effective_date => p_location_rec.address_effective_date,
314 x_address_expiration_date => p_location_rec.address_expiration_date,
315 x_clli_code => p_location_rec.clli_code,
316 x_language => p_location_rec.language,
317 x_short_description => p_location_rec.short_description,
318 x_description => p_location_rec.description,
319 x_content_source_type => p_location_rec.content_source_type,
320 x_loc_hierarchy_id => p_location_rec.loc_hierarchy_id,
321 x_sales_tax_geocode => p_location_rec.sales_tax_geocode,
322 x_sales_tax_inside_city_limits => p_location_rec.sales_tax_inside_city_limits,
323 x_fa_location_id => p_location_rec.fa_location_id,
324 x_geometry => p_location_rec.geometry,
325 x_object_version_number => 1,
326 x_timezone_id => p_location_rec.timezone_id,
327 x_created_by_module => p_location_rec.created_by_module,
328 x_application_id => p_location_rec.application_id,
329 x_geometry_status_code => p_location_rec.geometry_status_code,
330
331 x_actual_content_source => p_location_rec.actual_content_source,
332 -- Bug 2670546.
333 x_delivery_point_code => p_location_rec.delivery_point_code
334 );
335
336 x_location_id := p_location_rec.location_id;
337
338 -- ACNG add call to location profile: BEGIN
339
340 IF(p_location_rec.state IS NOT NULL) THEN
341 l_prov_state_admin_code := p_location_rec.state;
342 ELSIF(p_location_rec.province IS NOT NULL) THEN
343 l_prov_state_admin_code := p_location_rec.province;
344 ELSE
345 l_prov_state_admin_code := NULL;
346 END IF;
347
348 l_end_date := to_date('4712.12.31 00:01','YYYY.MM.DD HH24:MI');
349
350 hz_location_profiles_pkg.Insert_Row (
351 x_location_profile_id => l_location_profile_id
352 ,x_location_id => x_location_id
353 ,x_actual_content_source => p_location_rec.actual_content_source
354 ,x_effective_start_date => sysdate
355 ,x_effective_end_date => l_end_date
356 ,x_validation_sst_flag => 'Y'
357 ,x_validation_status_code => NULL
358 ,x_date_validated => NULL
359 ,x_address1 => p_location_rec.address1
360 ,x_address2 => p_location_rec.address2
361 ,x_address3 => p_location_rec.address3
362 ,x_address4 => p_location_rec.address4
363 ,x_city => p_location_rec.city
364 ,x_postal_code => p_location_rec.postal_code
365 ,x_prov_state_admin_code => l_prov_state_admin_code
366 ,x_county => p_location_rec.county
367 ,x_country => p_location_rec.country
368 ,x_object_version_number => 1
369 );
370 -- ACNG add call to location profile: END
371
372 -- Debug info.
373 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
374 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.insert_row (-) ' ||
375 'x_location_id = ' || p_location_rec.location_id,
376 p_prefix=>l_debug_prefix,
377 p_msg_level=>fnd_log.level_procedure);
378 -- ACNG add call to location profile: BEGIN
379 hz_utility_v2pub.debug(p_message=>'hz_location_profiles_pkg.insert_row (-) ' ||
380 'l_location_profile_id = ' || l_location_profile_id,
381 p_prefix=>l_debug_prefix,
382 p_msg_level=>fnd_log.level_procedure);
383 -- ACNG add call to location profile: END
384 hz_utility_v2pub.debug(p_message=>'do_create_location (-)',
385 p_prefix=>l_debug_prefix,
386 p_msg_level=>fnd_log.level_procedure);
387
388 END IF;
389
390 if p_location_rec.orig_system is not null
391 and p_location_rec.orig_system <>fnd_api.g_miss_char
392 then
393 l_orig_sys_reference_rec.orig_system := p_location_rec.orig_system;
394 l_orig_sys_reference_rec.orig_system_reference := p_location_rec.orig_system_reference;
395 l_orig_sys_reference_rec.owner_table_name := 'HZ_LOCATIONS';
396 l_orig_sys_reference_rec.owner_table_id := p_location_rec.location_id;
397 l_orig_sys_reference_rec.created_by_module := p_location_rec.created_by_module;
398
399 hz_orig_system_ref_pub.create_orig_system_reference(
400 FND_API.G_FALSE,
401 l_orig_sys_reference_rec,
402 x_return_status,
403 l_msg_count,
404 l_msg_data);
405 IF x_return_status <> fnd_api.g_ret_sts_success THEN
406 RAISE FND_API.G_EXC_ERROR;
407 END IF;
408 end if;
409
410
411 END do_create_location;
412
413 -- Modified the below procedure to add new parameters for address validation.
414 -- This is for bug # 4652309. The new parameters will be passed thro the
415 -- new update_location overloaded API.
416 PROCEDURE do_update_location(
417 p_location_rec IN OUT NOCOPY LOCATION_REC_TYPE,
418 p_do_addr_val IN VARCHAR2,
419 p_object_version_number IN OUT NOCOPY NUMBER,
420 x_addr_val_status OUT NOCOPY VARCHAR2,
421 x_addr_warn_msg OUT NOCOPY VARCHAR2,
422 x_return_status IN OUT NOCOPY VARCHAR2
423 ) IS
424
425 l_object_version_number NUMBER;
426 l_rowid ROWID;
427 l_geometry hz_locations.geometry%TYPE := hz_geometry_default;
428 l_key VARCHAR2(2000);
429 l_debug_prefix VARCHAR2(30) := '';
430 db_city hz_locations.city%TYPE;
431 db_state hz_locations.state%TYPE;
432 db_country hz_locations.country%TYPE;
433 db_county hz_locations.county%TYPE;
434 db_province hz_locations.province%TYPE;
435 db_postal_code hz_locations.postal_code%TYPE;
436 db_address1 hz_locations.address1%TYPE;
437 db_address2 hz_locations.address2%TYPE;
438 db_address3 hz_locations.address3%TYPE;
439 db_address4 hz_locations.address4%TYPE;
440 db_content_source_type hz_locations.content_source_type%TYPE;
441
442 -- ACNG add call to location profile: BEGIN
443 l_location_profile_rec hz_location_profile_pvt.location_profile_rec_type;
444 l_profile_content_source VARCHAR2(30);
445 l_return_status VARCHAR2(30);
446 l_msg_count NUMBER;
447 l_msg_data VARCHAR2(2000);
448 l_tax_validation_failed VARCHAR2(1);
449 l_allow_update_std VARCHAR2(1);
450 l_date_validated DATE;
451 l_validation_status_code VARCHAR2(30);
452 -- ACNG add call to location profile: END
453
454 -- Bug 2983977
455 l_loc_id NUMBER;
456 -- Bug 4693719 : Added for local assignment
457 l_acs hz_locations.actual_content_source%TYPE;
458 db_actual_content_source hz_locations.actual_content_source%TYPE;
459
460
461 BEGIN
462 -- Debug info.
463 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
464 hz_utility_v2pub.debug(p_message=>'do_update_location (+)',
465 p_prefix=>l_debug_prefix,
466 p_msg_level=>fnd_log.level_procedure);
467 END IF;
468
469 -- if party_site_id is not passed in, but orig system parameters are passed in
470 -- get party_site_id
471
472 IF (p_location_rec.orig_system is not null
473 and p_location_rec.orig_system <>fnd_api.g_miss_char)
474 and (p_location_rec.orig_system_reference is not null
475 and p_location_rec.orig_system_reference <>fnd_api.g_miss_char)
476 and (p_location_rec.location_id = FND_API.G_MISS_NUM or p_location_rec.location_id is null) THEN
477 hz_orig_system_ref_pub.get_owner_table_id
478 (p_orig_system => p_location_rec.orig_system,
479 p_orig_system_reference => p_location_rec.orig_system_reference,
480 p_owner_table_name => 'HZ_LOCATIONS',
481 x_owner_table_id => p_location_rec.location_id,
482 x_return_status => x_return_status);
483 IF x_return_status <> fnd_api.g_ret_sts_success THEN
484 RAISE FND_API.G_EXC_ERROR;
485 END IF;
486 END IF;
487
488 -- check whether record has been updated by another user
489
490 -- Bug 2197181: selecting actual_content_source for mix-n-match project.
491
492 BEGIN
493 SELECT hl.object_version_number,
494 hl.rowid,
495 hl.geometry,
496 hl.country,
497 hl.address1,
498 hl.address2,
499 hl.address3,
500 hl.address4,
501 hl.city,
502 hl.postal_code,
503 hl.state,
504 hl.province,
505 hl.county,
506 hl.content_source_type,
507 hl.actual_content_source,
508 -- Bug 4693719 : select ACS
509 hl.actual_content_source,
510 hl.date_validated,
511 hl.validation_status_code
512 INTO l_object_version_number,
513 l_rowid,
514 l_geometry,
515 db_country,
516 db_address1,
517 db_address2,
518 db_address3,
519 db_address4,
520 db_city,
521 db_postal_code,
522 db_state,
523 db_province,
524 db_county,
525 db_content_source_type,
526 l_profile_content_source,
527 db_actual_content_source,
528 l_date_validated,
529 l_validation_status_code
530 FROM hz_locations hl
531 WHERE hl.location_id = p_location_rec.location_id
532 FOR UPDATE OF hl.location_id NOWAIT;
533
534 IF NOT ((p_object_version_number IS NULL
535 AND l_object_version_number IS NULL)
536 OR (p_object_version_number IS NOT NULL AND
537 l_object_version_number IS NOT NULL AND
538 p_object_version_number = l_object_version_number))
539 THEN
540 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
541 fnd_message.set_token('TABLE', 'hz_locations');
542 fnd_msg_pub.add;
543 RAISE fnd_api.g_exc_error;
544 END IF;
545
546 p_object_version_number := NVL(l_object_version_number, 1) + 1;
547
548 EXCEPTION
549 WHEN NO_DATA_FOUND THEN
550 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
551 fnd_message.set_token('RECORD', 'location');
552 fnd_message.set_token('VALUE',
553 NVL(TO_CHAR(p_location_rec.location_id),'null'));
554 fnd_msg_pub.add;
555 RAISE fnd_api.g_exc_error;
556 END;
557
558 -- ACNG
559 -- raise error if the update location profile option is turned off and
560 -- the address has been validated before
561 l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
562 IF(l_allow_update_std = 'N' AND
563 l_date_validated IS NOT NULL AND
564 l_validation_status_code IS NOT NULL) THEN
565 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_NO_UPDATE');
566 FND_MSG_PUB.ADD;
567 RAISE fnd_api.g_exc_error;
568 END IF;
569
570 IF(p_location_rec.actual_content_source IS NOT NULL) THEN
571 IF(l_profile_content_source <> p_location_rec.actual_content_source) THEN
572 l_profile_content_source := p_location_rec.actual_content_source;
573 -- Bug 4693719 : ACS should not be set to NULL
574 -- p_location_rec.actual_content_source := NULL;
575 END IF;
576 END IF;
577 -- ACNG
578
579 -- call for validations.
580 hz_registry_validate_v2pub.validate_location(
581 'U',
582 p_location_rec,
583 l_rowid,
584 x_return_status
585 );
586
587 IF x_return_status = fnd_api.g_ret_sts_error THEN
588 RAISE fnd_api.g_exc_error;
589 END IF;
590
591 -- conditions to check if p_location_rec.geometry holds the default value.
592
593 IF p_location_rec.geometry.sdo_gtype <> fnd_api.g_miss_num
594 OR p_location_rec.geometry.sdo_srid <> fnd_api.g_miss_num
595 OR p_location_rec.geometry.sdo_point IS NOT NULL
596 OR p_location_rec.geometry.sdo_elem_info IS NOT NULL
597 OR p_location_rec.geometry.sdo_ordinates IS NOT NULL
598 OR p_location_rec.geometry IS NULL
599 THEN
600 l_geometry := p_location_rec.geometry;
601 END IF;
602
603 -- call address key generation program
604 l_key := hz_fuzzy_pub.generate_key (
605 'ADDRESS',
606 NULL,
607 p_location_rec.address1,
608 p_location_rec.address2,
609 p_location_rec.address3,
610 p_location_rec.address4,
611 p_location_rec.postal_code,
612 NULL,
613 NULL
614 );
615
616 -- Debug info.
617 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
618 hz_utility_v2pub.debug(p_message=>'Key generated : '||l_key,
619 p_prefix =>l_debug_prefix,
620 p_msg_level=>fnd_log.level_statement);
621 END IF;
622
623
624 p_location_rec.address_key := l_key;
625 p_location_rec.geometry := l_geometry;
626
627 -- Debug info.
628 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
629 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.Update_Row (+) ',
630 p_prefix=>l_debug_prefix,
631 p_msg_level=>fnd_log.level_procedure);
632 END IF;
633
634 -- Bug 4693719 : pass NULL if the secure data is not updated
635 IF HZ_UTILITY_V2PUB.G_UPDATE_ACS = 'Y' THEN
636 l_acs := nvl(p_location_rec.actual_content_source, 'USER_ENTERED');
637 ELSE
638 l_acs := NULL;
639 END IF;
640
641 -- call to table-handler to update the record
642 hz_locations_pkg.update_row (
643 x_rowid => l_rowid,
644 x_location_id => p_location_rec.location_id,
645 x_attribute_category => p_location_rec.attribute_category,
646 x_attribute1 => p_location_rec.attribute1,
647 x_attribute2 => p_location_rec.attribute2,
648 x_attribute3 => p_location_rec.attribute3,
649 x_attribute4 => p_location_rec.attribute4,
650 x_attribute5 => p_location_rec.attribute5,
651 x_attribute6 => p_location_rec.attribute6,
652 x_attribute7 => p_location_rec.attribute7,
653 x_attribute8 => p_location_rec.attribute8,
654 x_attribute9 => p_location_rec.attribute9,
655 x_attribute10 => p_location_rec.attribute10,
656 x_attribute11 => p_location_rec.attribute11,
657 x_attribute12 => p_location_rec.attribute12,
658 x_attribute13 => p_location_rec.attribute13,
659 x_attribute14 => p_location_rec.attribute14,
660 x_attribute15 => p_location_rec.attribute15,
661 x_attribute16 => p_location_rec.attribute16,
662 x_attribute17 => p_location_rec.attribute17,
663 x_attribute18 => p_location_rec.attribute18,
664 x_attribute19 => p_location_rec.attribute19,
665 x_attribute20 => p_location_rec.attribute20,
666 x_orig_system_reference => p_location_rec.orig_system_reference,
667 x_country => p_location_rec.country,
668 x_address1 => p_location_rec.address1,
669 x_address2 => p_location_rec.address2,
670 x_address3 => p_location_rec.address3,
671 x_address4 => p_location_rec.address4,
672 x_city => p_location_rec.city,
673 x_postal_code => p_location_rec.postal_code,
674 x_state => p_location_rec.state,
675 x_province => p_location_rec.province,
676 x_county => p_location_rec.county,
677 x_address_key => p_location_rec.address_key,
678 x_address_style => p_location_rec.address_style,
679 x_validated_flag => p_location_rec.validated_flag,
680 x_address_lines_phonetic => p_location_rec.address_lines_phonetic,
681 x_po_box_number => p_location_rec.po_box_number,
682 x_house_number => p_location_rec.house_number,
683 x_street_suffix => p_location_rec.street_suffix,
684 x_street => p_location_rec.street,
685 x_street_number => p_location_rec.street_number,
686 x_floor => p_location_rec.floor,
687 x_suite => p_location_rec.suite,
688 x_postal_plus4_code => p_location_rec.postal_plus4_code,
689 x_position => p_location_rec.position,
690 x_location_directions => p_location_rec.location_directions,
691 x_address_effective_date => p_location_rec.address_effective_date,
692 x_address_expiration_date => p_location_rec.address_expiration_date,
693 x_clli_code => p_location_rec.clli_code,
694 x_language => p_location_rec.language,
695 x_short_description => p_location_rec.short_description,
696 x_description => p_location_rec.description,
697 -- Bug 2197181 : content_source_type is obsolete and it is non-updateable.
698 x_content_source_type => NULL,
699 x_loc_hierarchy_id => p_location_rec.loc_hierarchy_id,
700 x_sales_tax_geocode => p_location_rec.sales_tax_geocode,
701 x_sales_tax_inside_city_limits => p_location_rec.sales_tax_inside_city_limits,
702 x_fa_location_id => p_location_rec.fa_location_id,
703 x_geometry => p_location_rec.geometry,
704 x_object_version_number => p_object_version_number,
705 x_timezone_id => p_location_rec.timezone_id,
706 x_created_by_module => p_location_rec.created_by_module,
707 x_application_id => p_location_rec.application_id,
708 x_geometry_status_code => p_location_rec.geometry_status_code,
709 -- Bug 4693719 : Pass correct value for ACS
710 x_actual_content_source => l_acs,
711 -- Bug 2670546
712 x_delivery_point_code => p_location_rec.delivery_point_code
713 );
714
715 -- ACNG add call to location profile: BEGIN
716 -- check if change occur on those columns where affecting location profile
717 -- and also if the l_actual_content_source is not USER_ENTERED or DNB
718 -- if yes, then do update. Otherwise, do nothing
719
720 IF((p_location_rec.country IS NOT NULL AND
721 NVL(db_country, fnd_api.g_miss_char) <> p_location_rec.country)
722 OR (p_location_rec.address1 IS NOT NULL AND
723 NVL(db_address1,fnd_api.g_miss_char) <> p_location_rec.address1)
724 OR (p_location_rec.address2 IS NOT NULL AND
725 NVL(db_address2,fnd_api.g_miss_char) <> p_location_rec.address2)
726 OR (p_location_rec.address3 IS NOT NULL AND
727 NVL(db_address3,fnd_api.g_miss_char) <> p_location_rec.address3)
728 OR (p_location_rec.address4 IS NOT NULL AND
729 NVL(db_address4,fnd_api.g_miss_char) <> p_location_rec.address4)
730 OR (p_location_rec.city IS NOT NULL AND
731 NVL(db_city, fnd_api.g_miss_char) <> p_location_rec.city)
732 OR (p_location_rec.postal_code IS NOT NULL AND
733 NVL(db_postal_code, fnd_api.g_miss_char) <> p_location_rec.postal_code)
734 OR (p_location_rec.state IS NOT NULL AND
735 NVL(db_state, fnd_api.g_miss_char) <> p_location_rec.state)
736 OR (p_location_rec.province IS NOT NULL AND
737 NVL(db_province,fnd_api.g_miss_char) <> p_location_rec.province)
738 OR (p_location_rec.county IS NOT NULL AND
739 NVL(db_county, fnd_api.g_miss_char) <> p_location_rec.county))
740 --OR NOT(l_profile_content_source in ('USER_ENTERED', 'DNB'))
741 THEN
742
743 l_location_profile_rec.location_profile_id := NULL;
744 l_location_profile_rec.location_id := p_location_rec.location_id;
745 -- Bug 4693719 : Keep ACS in sync with hz_locations
746 l_location_profile_rec.actual_content_source := nvl(l_acs, l_profile_content_source);
747 l_location_profile_rec.effective_start_date := NULL;
748 l_location_profile_rec.effective_end_date := NULL;
749 l_location_profile_rec.date_validated := NULL;
750 l_location_profile_rec.city := p_location_rec.city;
751
752 -- Bug 3395521.Passed the old database values if the user had passed NULL
753 -- for the following columns.
754 l_location_profile_rec.city := NVL(p_location_rec.city,db_city);
755 l_location_profile_rec.postal_code := NVL(p_location_rec.postal_code,db_postal_code);
756 l_location_profile_rec.county := NVL(p_location_rec.county,db_county);
757 l_location_profile_rec.country := NVL(p_location_rec.country,db_country);
758 l_location_profile_rec.address1 := NVL(p_location_rec.address1,db_address1);
759 l_location_profile_rec.address2 := NVL(p_location_rec.address2,db_address2);
760 l_location_profile_rec.address3 := NVL(p_location_rec.address3,db_address3);
761 l_location_profile_rec.address4 := NVL(p_location_rec.address4,db_address4);
762 l_location_profile_rec.validation_status_code := fnd_api.g_miss_char;
763 l_location_profile_rec.date_validated := fnd_api.g_miss_date;
764
765 IF(p_location_rec.state IS NULL) THEN
766 IF(p_location_rec.province IS NULL) OR (p_location_rec.province = fnd_api.g_miss_char) THEN
767 l_location_profile_rec.prov_state_admin_code := db_state;
768 ELSE
769 IF(db_state IS NULL) THEN
770 l_location_profile_rec.prov_state_admin_code := p_location_rec.province;
771 ELSE
772 l_location_profile_rec.prov_state_admin_code := db_state;
773 END IF;
774 END IF;
775 ELSIF(p_location_rec.state = fnd_api.g_miss_char) THEN
776 IF(p_location_rec.province IS NULL) THEN
777 l_location_profile_rec.prov_state_admin_code := db_province;
778 ELSIF(p_location_rec.province = fnd_api.g_miss_char) THEN
779 l_location_profile_rec.prov_state_admin_code := fnd_api.g_miss_char;
780 ELSE
781 l_location_profile_rec.prov_state_admin_code := p_location_rec.province;
782 END IF;
783 ELSE
784 l_location_profile_rec.prov_state_admin_code := p_location_rec.state;
785 END IF;
786
787 l_return_status := FND_API.G_RET_STS_SUCCESS;
788
789 hz_location_profile_pvt.update_location_profile (
790 p_location_profile_rec => l_location_profile_rec
791 ,x_return_status => l_return_status
792 ,x_msg_count => l_msg_count
793 ,x_msg_data => l_msg_data );
794
795 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
796 RAISE fnd_api.g_exc_error;
797 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
798 RAISE fnd_api.g_exc_unexpected_error;
799 END IF;
800
801 END IF;
802
803 -- ACNG add call to location profile: END
804
805 -- Debug info.
806 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
807 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row (-) ',
808 p_prefix=>l_debug_prefix,
809 p_msg_level=>fnd_log.level_procedure);
810 END IF;
811
812 -- update de-normalized location components in HZ_PARTIES for parties
813 -- having this location as an identifying location. There can be multiple
814 -- such parties.
815
816 -- Bug 2197181: As part of Mix n Match project, the location components
817 -- need to be de-normalized irrespective of the content_source, hence
818 -- commenting out NOCOPY the condition.
819
820 -- IF db_content_source_type = 'USER_ENTERED'
821 -- THEN
822 DECLARE
823 l_party_id NUMBER;
824
825 CURSOR c1 IS
826 SELECT hps.party_id
827 FROM hz_party_sites hps
828 WHERE hps.location_id = p_location_rec.location_id
829 AND hps.identifying_address_flag = 'Y';
830 BEGIN
831 IF (p_location_rec.country IS NOT NULL AND
832 NVL(db_country, fnd_api.g_miss_char) <> p_location_rec.country)
833 OR (p_location_rec.address1 IS NOT NULL AND
834 NVL(db_address1,fnd_api.g_miss_char) <> p_location_rec.address1)
835 OR (p_location_rec.address2 IS NOT NULL AND
836 NVL(db_address2,fnd_api.g_miss_char) <> p_location_rec.address2)
837 OR (p_location_rec.address3 IS NOT NULL AND
838 NVL(db_address3,fnd_api.g_miss_char) <> p_location_rec.address3)
839 OR (p_location_rec.address4 IS NOT NULL AND
840 NVL(db_address4,fnd_api.g_miss_char) <> p_location_rec.address4)
841 OR (p_location_rec.city IS NOT NULL AND
842 NVL(db_city, fnd_api.g_miss_char) <> p_location_rec.city)
843 OR (p_location_rec.postal_code IS NOT NULL AND
844 NVL(db_postal_code, fnd_api.g_miss_char) <> p_location_rec.postal_code)
845 OR (p_location_rec.state IS NOT NULL AND
846 NVL(db_state, fnd_api.g_miss_char) <> p_location_rec.state)
847 OR (p_location_rec.province IS NOT NULL AND
848 NVL(db_province,fnd_api.g_miss_char) <> p_location_rec.province)
849 OR (p_location_rec.county IS NOT NULL AND
850 NVL(db_county, fnd_api.g_miss_char) <> p_location_rec.county)
851 THEN
852 BEGIN
853 OPEN c1;
854 LOOP
855 FETCH c1 INTO l_party_id;
856 EXIT WHEN c1%NOTFOUND;
857
858 -- Debug info.
859 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
860 hz_utility_v2pub.debug(p_message=>'Denormalizing party with ID: ' ||
861 l_party_id,
862 p_prefix =>l_debug_prefix,
863 p_msg_level=>fnd_log.level_statement);
864 END IF;
865
866 -- Bug 2246041: Denormalization should not be done for Remit To
867 -- Addresses.
868
869 IF l_party_id <> -1 THEN
870 SELECT party_id
871 INTO l_party_id
872 FROM hz_parties
873 WHERE party_id = l_party_id
874 FOR UPDATE NOWAIT;
875
876 UPDATE hz_parties
877 SET country = DECODE(p_location_rec.country,
878 NULL, db_country,
879 fnd_api.g_miss_char, NULL,
880 p_location_rec.country),
881 address1 = DECODE(p_location_rec.address1,
882 NULL, db_address1,
883 fnd_api.g_miss_char, NULL,
884 p_location_rec.address1),
885 address2 = DECODE(p_location_rec.address2,
886 NULL, db_address2,
887 fnd_api.g_miss_char, NULL,
888 p_location_rec.address2),
889 address3 = DECODE(p_location_rec.address3,
890 NULL, db_address3,
891 fnd_api.g_miss_char, NULL,
892 p_location_rec.address3),
893 address4 = DECODE(p_location_rec.address4,
894 NULL, db_address4,
895 fnd_api.g_miss_char, NULL,
896 p_location_rec.address4),
897 city = DECODE(p_location_rec.city,
898 NULL, db_city,
899 fnd_api.g_miss_char, NULL,
900 p_location_rec.city),
901 postal_code = DECODE(p_location_rec.postal_code,
902 NULL, db_postal_code,
903 fnd_api.g_miss_char, NULL,
904 p_location_rec.postal_code),
905 state = DECODE(p_location_rec.state,
906 NULL, db_state,
907 fnd_api.g_miss_char, NULL,
908 p_location_rec.state),
909 province = DECODE(p_location_rec.province,
910 NULL, db_province,
911 fnd_api.g_miss_char, NULL,
912 p_location_rec.province),
913 county = DECODE(p_location_rec.county,
914 NULL, db_county,
915 fnd_api.g_miss_char, NULL,
916 p_location_rec.county),
917 last_update_date = hz_utility_v2pub.last_update_date,
918 last_updated_by = hz_utility_v2pub.last_updated_by,
919 last_update_login = hz_utility_v2pub.last_update_login,
920 request_id = hz_utility_v2pub.request_id,
921 program_id = hz_utility_v2pub.program_id,
922 program_application_id = hz_utility_v2pub.program_application_id,
923 program_update_date = hz_utility_v2pub.program_update_date
924 WHERE party_id = l_party_id;
925
926 END IF; -- Only if address is not a Remit to.
927 END LOOP;
928 CLOSE c1;
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
933 fnd_message.set_token('TABLE', 'HZ_PARTIES');
934 fnd_msg_pub.add;
935 CLOSE c1;
936 RAISE fnd_api.g_exc_error;
937 END;
938 END IF; -- location components have been modified
939 END;
940 -- END IF; -- p_location_rec.content_source_type = 'USER_ENTERED'
941
942
943 -- Bug 2983977.Added call to update loc_assignment records corresponding to this location_id.
944
945 HZ_TAX_ASSIGNMENT_V2PUB.update_loc_assignment (
946 p_location_id => p_location_rec.location_id,
947 p_do_addr_val => p_do_addr_val,
948 x_addr_val_status => x_addr_val_status,
949 x_addr_warn_msg => x_addr_warn_msg,
950 x_return_status => l_return_status,
951 x_msg_count => l_msg_count,
952 x_msg_data => l_msg_data);
953
954 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
955 RAISE fnd_api.g_exc_error;
956 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
957 RAISE fnd_api.g_exc_unexpected_error;
958 END IF;
959
960
961
962 -- Debug info.
963 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
964 hz_utility_v2pub.debug(p_message=>'do_update_location (-)',
965 p_prefix=>l_debug_prefix,
966 p_msg_level=>fnd_log.level_procedure);
967 END IF;
968
969 END do_update_location;
970
971 PROCEDURE fill_geometry(
972 p_loc_rec IN OUT NOCOPY LOCATION_REC_TYPE,
973 x_return_status OUT NOCOPY VARCHAR2
974 ) IS
975 l_api_version CONSTANT NUMBER := 1.0;
976 l_csf_installed_flag VARCHAR2(30) := 'N';
977 l_cursor VARCHAR2(2000);
978 l_country VARCHAR2(100);
979 l_state VARCHAR2(100);
980 l_city VARCHAR2(100);
981 l_zip VARCHAR2(100);
982 l_hn VARCHAR2(100);
983 l_st VARCHAR2(400);
984
985 BEGIN
986 -- Bug 2334810: Obsoleted code that was superseded by the eLocations
987 -- integration by removing references to CSF_LF*
988 x_return_status := 'N';
989 END fill_geometry;
990
991 --------------------------------------
992 -- public procedures and functions
993 --------------------------------------
994
995 /**
996 * PROCEDURE create_location
997 *
998 * DESCRIPTION
999 * Creates location(overloaded procedure with address validation).
1000 *
1001 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1002 *
1003 * ARGUMENTS
1004 * IN:
1005 * p_init_msg_list Initialize message stack if it is set to
1006 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1007 * p_location_rec Location record.
1008 * p_do_addr_val Do address validation if 'Y'
1009 * IN/OUT:
1010 * OUT:
1011 * x_location_id Location ID.
1012 * x_addr_val_status Address validation status based on address validation level.
1013 * x_addr_warn_msg Warning message if x_addr_val_status is 'W'
1014 * x_return_status Return status after the call. The status can
1015 * be FND_API.G_RET_STS_SUCCESS (success),
1016 * FND_API.G_RET_STS_ERROR (error),
1017 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1018 * x_msg_count Number of messages in message stack.
1019 * x_msg_data Message text if x_msg_count is 1.
1020 *
1021 * NOTES
1022 *
1023 * MODIFICATION HISTORY
1024 *
1025 * 10-04-2005 Baiju Nair o Created.
1026 *
1027 */
1028
1029 PROCEDURE create_location (
1030 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1031 p_location_rec IN LOCATION_REC_TYPE,
1032 p_do_addr_val IN VARCHAR2,
1033 x_location_id OUT NOCOPY NUMBER,
1034 x_addr_val_status OUT NOCOPY VARCHAR2,
1035 x_addr_warn_msg OUT NOCOPY VARCHAR2,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2
1039 ) IS
1040
1041 l_location_rec LOCATION_REC_TYPE := p_location_rec;
1042 l_fill_geo_status VARCHAR2(10);
1043 l_debug_prefix VARCHAR2(30) := '';
1044
1045 -- Bug 3594731: fix the bug 3517181 in the main code line.
1046
1047 dss_return_status VARCHAR2(1);
1048 dss_msg_count NUMBER;
1049 dss_msg_data VARCHAR2(2000);
1050 l_test_security VARCHAR2(1);
1051
1052 l_addr_val_level VARCHAR2(30);
1053
1054 BEGIN
1055
1056 -- standard start of API savepoint
1057 SAVEPOINT create_location;
1058
1059 -- Check if API is called in debug mode. If yes, enable debug.
1060 --enable_debug;
1061
1062 -- Debug info.
1063 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1064 hz_utility_v2pub.debug(p_message=>'create_location (+)',
1065 p_prefix=>l_debug_prefix,
1066 p_msg_level=>fnd_log.level_procedure);
1067 END IF;
1068
1069 -- initialize message list if p_init_msg_list is set to TRUE.
1070 IF FND_API.to_Boolean(p_init_msg_list) THEN
1071 FND_MSG_PUB.initialize;
1072 END IF;
1073
1074 -- initialize API return status to success.
1075 x_return_status := FND_API.G_RET_STS_SUCCESS;
1076 x_addr_val_status := NULL;
1077
1078 fill_geometry(l_location_rec, l_fill_geo_status);
1079
1080 -- Bug 2197181: added for mix-n-match project. first load data
1081 -- sources for this entity. Then assign the actual_content_source
1082 -- to the real data source. The value of content_source_type is
1083 -- depended on if data source is seleted. If it is selected, we reset
1084 -- content_source_type to user-entered. We also check if user
1085 -- has the privilege to create user-entered data if mix-n-match
1086 -- is enabled.
1087
1088 -- Bug 2444678: Removed caching.
1089
1090 /* SSM SST Integration and Extension
1091 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1092
1093 -- IF g_loc_mixnmatch_enabled IS NULL THEN
1094 HZ_MIXNM_UTILITY.LoadDataSources(
1095 p_entity_name => 'HZ_LOCATIONS',
1096 p_entity_attr_id => g_loc_entity_attr_id,
1097 p_mixnmatch_enabled => g_loc_mixnmatch_enabled,
1098 p_selected_datasources => g_loc_selected_datasources );
1099 -- END IF;
1100 */
1101
1102 HZ_MIXNM_UTILITY.AssignDataSourceDuringCreation (
1103 p_entity_name => 'HZ_LOCATIONS',
1104 p_entity_attr_id => g_loc_entity_attr_id,
1105 p_mixnmatch_enabled => g_loc_mixnmatch_enabled,
1106 p_selected_datasources => g_loc_selected_datasources,
1107 p_content_source_type => l_location_rec.content_source_type,
1108 p_actual_content_source => l_location_rec.actual_content_source,
1109 x_is_datasource_selected => g_loc_is_datasource_selected,
1110 x_return_status => x_return_status );
1111
1112 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1113 RAISE FND_API.G_EXC_ERROR;
1114 END IF;
1115
1116 -- report error on obsolete columns based on profile
1117 IF NVL(FND_PROFILE.VALUE('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'Y') = 'Y' THEN
1118 check_obsolete_columns (
1119 p_create_update_flag => 'C',
1120 p_location_rec => l_location_rec,
1121 x_return_status => x_return_status
1122 );
1123
1124 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1125 RAISE FND_API.G_EXC_ERROR;
1126 END IF;
1127 END IF;
1128
1129 -- call to business logic.
1130 do_create_location(
1131 l_location_rec,
1132 x_location_id,
1133 x_return_status);
1134
1135 -- If p_do_addr_val = 'Y' and create_location is success, call the address validation procedure.
1136 IF (p_do_addr_val = 'Y' AND x_location_id is NOT NULL AND x_return_status = FND_API.g_ret_sts_success) THEN
1137 HZ_GNR_PUB.validateLoc(
1138 p_location_id => x_location_id,
1139 p_init_msg_list => FND_API.G_FALSE,
1140 x_addr_val_level => l_addr_val_level,
1141 x_addr_warn_msg => x_addr_warn_msg,
1142 x_addr_val_status => x_addr_val_status,
1143 x_return_status => x_return_status,
1144 x_msg_count => x_msg_count,
1145 x_msg_data => x_msg_data);
1146
1147 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1148 RAISE FND_API.G_EXC_ERROR;
1149 end if;
1150
1151 END IF;
1152
1153
1154 -- Bug 3711629: remove the dss check in create location API.
1155 /*
1156 -- Bug 3594731: fix the bug 3517181 in the main code line.
1157 -- IN A NUTSHELL THE DSS CHECK THAT IS NEEDED IN THE LOCATION API, FOR SECURING
1158 -- THE HZ_LOCATIONS ENTITY WAS INADVERTENTLY MISSED DURING THE CODING PHASE OF
1159 -- THE DSS PROJECT
1160
1161 l_test_security :=
1162 hz_dss_util_pub.test_instance(
1163 p_operation_code => 'INSERT',
1164 p_db_object_name => 'HZ_LOCATIONS',
1165 p_instance_pk1_value => x_location_id,
1166 p_user_name => fnd_global.user_name,
1167 x_return_status => dss_return_status,
1168 x_msg_count => dss_msg_count,
1169 x_msg_data => dss_msg_data);
1170
1171 IF dss_return_status <> fnd_api.g_ret_sts_success THEN
1172 RAISE FND_API.G_EXC_ERROR;
1173 END IF;
1174
1175 IF (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) THEN
1176 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_SECURITY_FAIL');
1177 FND_MESSAGE.SET_TOKEN('USER_NAME',fnd_global.user_name);
1178 FND_MESSAGE.SET_TOKEN('OPER_NAME','INSERT');
1179 FND_MESSAGE.SET_TOKEN('OBJECT_NAME','HZ_LOCATIONS');
1180 FND_MSG_PUB.ADD;
1181 RAISE FND_API.G_EXC_ERROR;
1182 END IF;
1183 */
1184
1185 -- Invoke business event system.
1186
1187 -- SSM SST Integration and Extension
1188 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1189 -- There is no need to check if the data-source is selected.
1190
1191 IF x_return_status = FND_API.G_RET_STS_SUCCESS /*AND
1192 -- Bug 2197181: Added below condition for Mix-n-Match
1193 g_loc_is_datasource_selected = 'Y'*/
1194 THEN
1195 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
1196 HZ_BUSINESS_EVENT_V2PVT.create_location_event (
1197 l_location_rec);
1198 END IF;
1199
1200 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1201 -- populate function for integration service
1202 HZ_POPULATE_BOT_PKG.pop_hz_locations(
1203 p_operation => 'I',
1204 p_location_id => x_location_id );
1205 END IF;
1206 END IF;
1207
1208 -- standard call to get message count and if count is 1, get message info.
1209 FND_MSG_PUB.Count_And_Get(
1210 p_encoded => FND_API.G_FALSE,
1211 p_count => x_msg_count,
1212 p_data => x_msg_data);
1213 -- Debug info.
1214 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1215 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1216 p_msg_data=>x_msg_data,
1217 p_msg_type=>'WARNING',
1218 p_msg_level=>fnd_log.level_exception);
1219 END IF;
1220 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1221 hz_utility_v2pub.debug(p_message=>'create_location (-)',
1222 p_prefix=>l_debug_prefix,
1223 p_msg_level=>fnd_log.level_procedure);
1224 END IF;
1225
1226 -- Check if API is called in debug mode. If yes, disable debug.
1227 --disable_debug;
1228
1229 EXCEPTION
1230 WHEN FND_API.G_EXC_ERROR THEN
1231 ROLLBACK TO create_location;
1232 x_return_status := FND_API.G_RET_STS_ERROR;
1233 FND_MSG_PUB.Count_And_Get(
1234 p_encoded => FND_API.G_FALSE,
1235 p_count => x_msg_count,
1236 p_data => x_msg_data);
1237
1238 -- Debug info.
1239 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1240 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1241 p_msg_data=>x_msg_data,
1242 p_msg_type=>'ERROR',
1243 p_msg_level=>fnd_log.level_error);
1244 END IF;
1245 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1246 hz_utility_v2pub.debug(p_message=>'create_location (-)',
1247 p_prefix=>l_debug_prefix,
1248 p_msg_level=>fnd_log.level_procedure);
1249 END IF;
1250
1251 -- Check if API is called in debug mode. If yes, disable debug.
1252 --disable_debug;
1253
1254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1255 ROLLBACK TO create_location;
1256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1257 FND_MSG_PUB.Count_And_Get(
1258 p_encoded => FND_API.G_FALSE,
1259 p_count => x_msg_count,
1260 p_data => x_msg_data);
1261
1262 -- Debug info.
1263 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1264 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1265 p_msg_data=>x_msg_data,
1266 p_msg_type=>'UNEXPECTED ERROR',
1267 p_msg_level=>fnd_log.level_error);
1268 END IF;
1269 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1270 hz_utility_v2pub.debug(p_message=>'create_location (-)',
1271 p_prefix=>l_debug_prefix,
1272 p_msg_level=>fnd_log.level_procedure);
1273 END IF;
1274
1275 -- Check if API is called in debug mode. If yes, disable debug.
1276 --disable_debug;
1277
1278 WHEN OTHERS THEN
1279 ROLLBACK TO create_location;
1280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1281
1282 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1283 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1284 FND_MSG_PUB.ADD;
1285
1286 FND_MSG_PUB.Count_And_Get(
1287 p_encoded => FND_API.G_FALSE,
1288 p_count => x_msg_count,
1289 p_data => x_msg_data);
1290
1291 -- Debug info.
1292 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1293 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1294 p_msg_data=>x_msg_data,
1295 p_msg_type=>'SQL ERROR',
1296 p_msg_level=>fnd_log.level_error);
1297 END IF;
1298 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1299 hz_utility_v2pub.debug(p_message=>'create_location (-)',
1300 p_prefix=>l_debug_prefix,
1301 p_msg_level=>fnd_log.level_procedure);
1302 END IF;
1303
1304 -- Check if API is called in debug mode. If yes, disable debug.
1305 --disable_debug;
1306
1307 END create_location;
1308
1309 /**
1310 * PROCEDURE create_location
1311 *
1312 * DESCRIPTION
1313 * Creates location.
1314 *
1315 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1316 * HZ_BUSINESS_EVENT_V2PVT.create_location_event
1317 *
1318 * ARGUMENTS
1319 * IN:
1320 * p_init_msg_list Initialize message stack if it is set to
1321 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1322 * p_location_rec Location record.
1323 * IN/OUT:
1324 * OUT:
1325 * x_location_id Location ID.
1326 * x_return_status Return status after the call. The status can
1327 * be FND_API.G_RET_STS_SUCCESS (success),
1328 * FND_API.G_RET_STS_ERROR (error),
1329 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1330 * x_msg_count Number of messages in message stack.
1331 * x_msg_data Message text if x_msg_count is 1.
1332 *
1333 * NOTES
1334 *
1335 * MODIFICATION HISTORY
1336 *
1337 * 07-23-2001 Indrajit Sen o Created.
1338 * 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
1339 * For non-profile entities, the concept of
1340 * select/de-select data-sources is obsoleted.
1341 */
1342
1343 PROCEDURE create_location (
1344 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1345 p_location_rec IN LOCATION_REC_TYPE,
1346 x_location_id OUT NOCOPY NUMBER,
1347 x_return_status OUT NOCOPY VARCHAR2,
1348 x_msg_count OUT NOCOPY NUMBER,
1349 x_msg_data OUT NOCOPY VARCHAR2
1350 ) IS
1351
1352 l_addr_val_status VARCHAR2(30);
1353 l_addr_warn_msg VARCHAR2(2000);
1354
1355 BEGIN
1356
1357 create_location(
1358 p_init_msg_list => p_init_msg_list,
1359 p_location_rec => p_location_rec,
1360 p_do_addr_val => 'N',
1361 x_location_id => x_location_id,
1362 x_addr_val_status => l_addr_val_status,
1363 x_addr_warn_msg => l_addr_warn_msg,
1364 x_return_status => x_return_status,
1365 x_msg_count => x_msg_count,
1366 x_msg_data => x_msg_data);
1367
1368 EXCEPTION WHEN OTHERS THEN
1369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1370 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1371 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1372 FND_MSG_PUB.ADD;
1373 FND_MSG_PUB.Count_And_Get(
1374 p_encoded => FND_API.G_FALSE,
1375 p_count => x_msg_count,
1376 p_data => x_msg_data);
1377 END create_location;
1378
1379 /**
1380 * PROCEDURE update_location
1381 *
1382 * DESCRIPTION
1383 * Updates location(overloaded procedure with address validation).
1384 *
1385 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1386 *
1387 * ARGUMENTS
1388 * IN:
1389 * p_init_msg_list Initialize message stack if it is set to
1390 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1391 * p_location_rec Location record.
1392 * p_do_addr_val Do address validation if 'Y'
1393 * IN/OUT:
1394 * p_object_version_number Used for locking the being updated record.
1395 * OUT:
1396 * x_addr_val_status Address validation status based on address validation level.
1397 * x_addr_warn_msg Warning message if x_addr_val_status is 'W'
1398 * x_return_status Return status after the call. The status can
1399 * be FND_API.G_RET_STS_SUCCESS (success),
1400 * FND_API.G_RET_STS_ERROR (error),
1401 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1402 * x_msg_count Number of messages in message stack.
1403 * x_msg_data Message text if x_msg_count is 1.
1404 *
1405 * NOTES
1406 *
1407 * MODIFICATION HISTORY
1408 *
1409 * 10-04-2005 Baiju Nair o Created.
1410 *
1411 */
1412
1413 PROCEDURE update_location (
1414 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
1415 p_location_rec IN LOCATION_REC_TYPE,
1416 p_do_addr_val IN VARCHAR2,
1417 p_object_version_number IN OUT NOCOPY NUMBER,
1418 x_addr_val_status OUT NOCOPY VARCHAR2,
1419 x_addr_warn_msg OUT NOCOPY VARCHAR2,
1420 x_return_status OUT NOCOPY VARCHAR2,
1421 x_msg_count OUT NOCOPY NUMBER,
1422 x_msg_data OUT NOCOPY VARCHAR2
1423 ) IS
1424
1425 CURSOR c_standalone_location IS
1426 SELECT 1
1427 FROM hz_party_sites
1428 WHERE location_id = p_location_rec.location_id
1429 AND status NOT IN ('M', 'D')
1430 AND ROWNUM = 1;
1431
1432 l_location_rec LOCATION_REC_TYPE := p_location_rec;
1433 l_old_location_rec LOCATION_REC_TYPE;
1434 l_fill_geo_status VARCHAR2(10);
1435 l_data_source_from VARCHAR2(30);
1436
1437 l_msg_count number;
1438 l_message_count number;
1439 l_msg_data varchar2(2000);
1440 l_return_status varchar2(1);
1441
1442 l_changed_flag varchar2(1) := 'N';
1443 l_debug_prefix VARCHAR2(30) := '';
1444
1445 -- Bug 3594731: fix the bug 3517181 in the main code line.
1446
1447 dss_return_status VARCHAR2(1);
1448 dss_msg_count NUMBER;
1449 dss_msg_data VARCHAR2(2000);
1450 l_test_security VARCHAR2(1);
1451 l_dummy NUMBER;
1452 l_standalone_location VARCHAR2(1);
1453
1454 BEGIN
1455
1456 -- standard start of API savepoint
1457 SAVEPOINT update_location;
1458
1459 -- Check if API is called in debug mode. If yes, enable debug.
1460 --enable_debug;
1461
1462 -- Debug info.
1463 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1464 hz_utility_v2pub.debug(p_message=>'update_location (+)',
1465 p_prefix=>l_debug_prefix,
1466 p_msg_level=>fnd_log.level_procedure);
1467 END IF;
1468
1469 -- initialize message list if p_init_msg_list is set to TRUE.
1470 IF FND_API.to_Boolean(p_init_msg_list) THEN
1471 FND_MSG_PUB.initialize;
1472 END IF;
1473
1474 -- initialize API return status to success.
1475 x_return_status := FND_API.G_RET_STS_SUCCESS;
1476
1477 -- if location_id is not passed in, but orig system parameters are passed in
1478 -- get location_id
1479
1480 IF (p_location_rec.orig_system is not null
1481 and p_location_rec.orig_system <>fnd_api.g_miss_char)
1482 and (p_location_rec.orig_system_reference is not null
1483 and p_location_rec.orig_system_reference <>fnd_api.g_miss_char)
1484 and (p_location_rec.location_id = FND_API.G_MISS_NUM or p_location_rec.location_id is null) THEN
1485 hz_orig_system_ref_pub.get_owner_table_id
1486 (p_orig_system => p_location_rec.orig_system,
1487 p_orig_system_reference => p_location_rec.orig_system_reference,
1488 p_owner_table_name => 'HZ_LOCATIONS',
1489 x_owner_table_id => l_location_rec.location_id,
1490 x_return_status => x_return_status);
1491 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1492 RAISE FND_API.G_EXC_ERROR;
1493 END IF;
1494 END IF;
1495
1496 -- Bug 3711629: remove the dss check in create location API.
1497 -- during update, skip the dss check if the location is
1498 -- a standalone location.
1499 -- Bug 3818648: check dss profile before call test_instance.
1500 --
1501 -- Bug 3594731: fix the bug 3517181 in the main code line.
1502 -- IN A NUTSHELL THE DSS CHECK THAT IS NEEDED IN THE LOCATION API, FOR SECURING
1503 -- THE HZ_LOCATIONS ENTITY WAS INADVERTENTLY MISSED DURING THE CODING PHASE OF
1504 -- THE DSS PROJECT
1505
1506 IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' THEN
1507
1508 OPEN c_standalone_location;
1509 FETCH c_standalone_location INTO l_dummy;
1510 IF c_standalone_location%FOUND THEN
1511 l_standalone_location := 'N';
1512 ELSE
1513 l_standalone_location := 'Y';
1514 END IF;
1515 CLOSE c_standalone_location;
1516
1517 IF l_standalone_location = 'N' THEN
1518 l_test_security :=
1519 hz_dss_util_pub.test_instance(
1520 p_operation_code => 'UPDATE',
1521 p_db_object_name => 'HZ_LOCATIONS',
1522 p_instance_pk1_value => l_location_rec.location_id,
1523 p_user_name => fnd_global.user_name,
1524 x_return_status => dss_return_status,
1525 x_msg_count => dss_msg_count,
1526 x_msg_data => dss_msg_data);
1527
1528 IF dss_return_status <> fnd_api.g_ret_sts_success THEN
1529 RAISE FND_API.G_EXC_ERROR;
1530 END IF;
1531
1532 IF (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) THEN
1533 --
1534 -- Bug 3835601: replaced the dss message with a more user friendly message
1535 --
1536 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
1537 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
1538 fnd_message.get_string('AR', 'HZ_DSS_PARTY_ADDRESSES'));
1539 FND_MSG_PUB.ADD;
1540 RAISE FND_API.G_EXC_ERROR;
1541 END IF;
1542 END IF;
1543 END IF;
1544
1545 -- Get old records. Will be used by business event system.
1546 get_location_rec (
1547 p_location_id => l_location_rec.location_id,
1548 x_location_rec => l_old_location_rec,
1549 x_return_status => x_return_status,
1550 x_msg_count => x_msg_count,
1551 x_msg_data => x_msg_data);
1552
1553 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1554 RAISE FND_API.G_EXC_ERROR;
1555 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1557 END IF;
1558
1559 fill_geometry(l_location_rec, l_fill_geo_status);
1560
1561 -- Bug 2197181: added for mix-n-match project. first load data
1562 -- sources for this entity.
1563
1564 -- Bug 2444678: Removed caching.
1565
1566 -- IF g_loc_mixnmatch_enabled IS NULL THEN
1567 /* SSM SST Integration and Extension
1568 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1569 * There is no need to check if the data-source is selected.
1570
1571 HZ_MIXNM_UTILITY.LoadDataSources(
1572 p_entity_name => 'HZ_LOCATIONS',
1573 p_entity_attr_id => g_loc_entity_attr_id,
1574 p_mixnmatch_enabled => g_loc_mixnmatch_enabled,
1575 p_selected_datasources => g_loc_selected_datasources );
1576 */
1577 -- END IF;
1578
1579 -- Bug 2197181: added for mix-n-match project.
1580 -- check if the data source is seleted.
1581
1582 /* SSM SST Integration and Extension
1583 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1584 * There is no need to check if the data-source is selected.
1585
1586 g_loc_is_datasource_selected :=
1587 HZ_MIXNM_UTILITY.isDataSourceSelected (
1588 p_selected_datasources => g_loc_selected_datasources,
1589 p_actual_content_source => l_old_location_rec.actual_content_source );
1590 */
1591 IF (p_location_rec.country IS NOT NULL AND
1592 NVL(l_old_location_rec.country, fnd_api.g_miss_char) <> p_location_rec.country)
1593 OR (p_location_rec.city IS NOT NULL AND
1594 NVL(l_old_location_rec.city, fnd_api.g_miss_char) <> p_location_rec.city)
1595 OR (p_location_rec.state IS NOT NULL AND
1596 NVL(l_old_location_rec.state, fnd_api.g_miss_char) <>p_location_rec.state)
1597 OR (p_location_rec.postal_code IS NOT NULL AND
1598 NVL(l_old_location_rec.postal_code, fnd_api.g_miss_char) <> p_location_rec.postal_code)
1599 then
1600 l_changed_flag := 'Y';
1601 end if;
1602
1603 if l_changed_flag = 'Y' and (p_location_rec.timezone_id is null or p_location_rec.timezone_id = fnd_api.g_miss_num)
1604 then
1605 if p_location_rec.country IS NULL
1606 then
1607 l_location_rec.country := l_old_location_rec.country;
1608 end if;
1609 if p_location_rec.postal_code IS NULL
1610 then
1611 l_location_rec.postal_code := l_old_location_rec.postal_code;
1612 end if;
1613 if p_location_rec.city IS NULL
1614 then
1615 l_location_rec.city := l_old_location_rec.city;
1616 end if;
1617 if p_location_rec.state IS NULL
1618 then
1619 l_location_rec.state := l_old_location_rec.state;
1620 end if;
1621 l_message_count := fnd_msg_pub.count_msg();
1622 hz_timezone_pub.get_timezone_id(
1623 p_api_version => 1.0,
1624 p_init_msg_list => FND_API.G_FALSE,
1625 p_postal_code => l_location_rec.postal_code,
1626 p_city => l_location_rec.city,
1627 p_state => l_location_rec.state,
1628 p_country => l_location_rec.country,
1629 x_timezone_id => l_location_rec.timezone_id,
1630 x_return_status => l_return_status ,
1631 x_msg_count =>l_msg_count ,
1632 x_msg_data => l_msg_data);
1633 if l_return_status <> fnd_api.g_ret_sts_success
1634 then -- we don't raise error
1635 l_location_rec.timezone_id := fnd_api.g_miss_num;
1636 FOR i IN 1..(l_msg_count - l_message_count) LOOP
1637 fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
1638 END LOOP;
1639 l_return_status := FND_API.G_RET_STS_SUCCESS;
1640 end if;
1641 end if;
1642
1643 -- report error on obsolete columns based on profile
1644 IF NVL(FND_PROFILE.VALUE('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'Y') = 'Y' THEN
1645 check_obsolete_columns (
1646 p_create_update_flag => 'U',
1647 p_location_rec => l_location_rec,
1648 p_old_location_rec => l_old_location_rec,
1649 x_return_status => x_return_status
1650 );
1651
1652 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1653 RAISE FND_API.G_EXC_ERROR;
1654 END IF;
1655 END IF;
1656
1657 -- call to business logic.
1658 -- Modified the below call to pass new parameters for address validation.
1659 -- This is for bug # 4652309. The new parameters will be passed thro the
1660 -- new update_location overloaded API. If it is called from old
1661 -- update_location API, then p_do_addr_val will be 'N'
1662 do_update_location(
1663 l_location_rec,
1664 p_do_addr_val,
1665 p_object_version_number,
1666 x_addr_val_status,
1667 x_addr_warn_msg,
1668 x_return_status);
1669
1670 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1671 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'Before the Supplier Denorm Call',
1672 p_msg_level=>fnd_log.level_procedure);
1673 END IF;
1674
1675 AP_TCA_SUPPLIER_SYNC_PKG.SYNC_Supplier_Sites(x_return_status => x_return_status,
1676 x_msg_count => x_msg_count,
1677 x_msg_data => x_msg_data,
1678 x_location_id => l_location_rec.location_id,
1679 x_party_site_id => NULL);
1680
1681 IF x_return_status = fnd_api.g_ret_sts_error THEN
1682 RAISE fnd_api.g_exc_error;
1683 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1684 RAISE fnd_api.g_exc_unexpected_error;
1685 END IF;
1686
1687 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1688 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'After the Supplier Denorm Call',
1689 p_msg_level=>fnd_log.level_procedure);
1690 END IF;
1691
1692 -- Call to indicate location update to DQM
1693 HZ_DQM_SYNC.sync_location(l_location_rec.location_id,'U');
1694 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1695 update_location_search(l_old_location_rec,l_location_rec);
1696 END IF;
1697
1698 -- Invoke business event system.
1699
1700 -- SSM SST Integration and Extension
1701 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1702 -- There is no need to check if the data-source is selected.
1703
1704 IF x_return_status = FND_API.G_RET_STS_SUCCESS /*AND
1705 -- Bug 2197181: Added below condition for Mix-n-Match
1706 g_loc_is_datasource_selected = 'Y'*/
1707 THEN
1708 l_old_location_rec.orig_system := p_location_rec.orig_system;
1709 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
1710 HZ_BUSINESS_EVENT_V2PVT.update_location_event (
1711 l_location_rec,
1712 l_old_location_rec);
1713 END IF;
1714
1715 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1716 -- populate function for integration service
1717 HZ_POPULATE_BOT_PKG.pop_hz_locations(
1718 p_operation => 'U',
1719 p_location_id => l_location_rec.location_id );
1720 END IF;
1721 END IF;
1722
1723 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
1724
1725 -- standard call to get message count and if count is 1, get message info.
1726 FND_MSG_PUB.Count_And_Get(
1727 p_encoded => FND_API.G_FALSE,
1728 p_count => x_msg_count,
1729 p_data => x_msg_data);
1730
1731 -- Debug info.
1732 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1733 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1734 p_msg_data=>x_msg_data,
1735 p_msg_type=>'WARNING',
1736 p_msg_level=>fnd_log.level_exception);
1737 END IF;
1738 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1739 hz_utility_v2pub.debug(p_message=>'update_location (-)',
1740 p_prefix=>l_debug_prefix,
1741 p_msg_level=>fnd_log.level_procedure);
1742 END IF;
1743
1744
1745 -- Check if API is called in debug mode. If yes, disable debug.
1746 --disable_debug;
1747
1748 EXCEPTION
1749 WHEN FND_API.G_EXC_ERROR THEN
1750 ROLLBACK TO update_location;
1751 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
1752 x_return_status := FND_API.G_RET_STS_ERROR;
1753 FND_MSG_PUB.Count_And_Get(
1754 p_encoded => FND_API.G_FALSE,
1755 p_count => x_msg_count,
1756 p_data => x_msg_data);
1757
1758 -- Debug info.
1759 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1760 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1761 p_msg_data=>x_msg_data,
1762 p_msg_type=>'ERROR',
1763 p_msg_level=>fnd_log.level_error);
1764 END IF;
1765 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1766 hz_utility_v2pub.debug(p_message=>'update_location (-)',
1767 p_prefix=>l_debug_prefix,
1768 p_msg_level=>fnd_log.level_procedure);
1769 END IF;
1770
1771 -- Check if API is called in debug mode. If yes, disable debug.
1772 --disable_debug;
1773
1774 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1775 ROLLBACK TO update_location;
1776 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
1777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1778 FND_MSG_PUB.Count_And_Get(
1779 p_encoded => FND_API.G_FALSE,
1780 p_count => x_msg_count,
1781 p_data => x_msg_data);
1782
1783 -- Debug info.
1784 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1785 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1786 p_msg_data=>x_msg_data,
1787 p_msg_type=>'UNEXPECTED ERROR',
1788 p_msg_level=>fnd_log.level_error);
1789 END IF;
1790 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1791 hz_utility_v2pub.debug(p_message=>'update_location (-)',
1792 p_prefix=>l_debug_prefix,
1793 p_msg_level=>fnd_log.level_procedure);
1794 END IF;
1795
1796 -- Check if API is called in debug mode. If yes, disable debug.
1797 --disable_debug;
1798
1799 WHEN OTHERS THEN
1800 ROLLBACK TO update_location;
1801 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
1802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1803
1804 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1805 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1806 FND_MSG_PUB.ADD;
1807
1808 FND_MSG_PUB.Count_And_Get(
1809 p_encoded => FND_API.G_FALSE,
1810 p_count => x_msg_count,
1811 p_data => x_msg_data);
1812
1813 -- Debug info.
1814 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1815 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1816 p_msg_data=>x_msg_data,
1817 p_msg_type=>'SQL ERROR',
1818 p_msg_level=>fnd_log.level_error);
1819 END IF;
1820 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1821 hz_utility_v2pub.debug(p_message=>'update_location (-)',
1822 p_prefix=>l_debug_prefix,
1823 p_msg_level=>fnd_log.level_procedure);
1824 END IF;
1825
1826 -- Check if API is called in debug mode. If yes, disable debug.
1827 --disable_debug;
1828
1829 END update_location;
1830
1831 /**
1832 * PROCEDURE update_location
1833 *
1834 * DESCRIPTION
1835 * Updates location.
1836 *
1837 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1838 * HZ_BUSINESS_EVENT_V2PVT.update_location_event
1839 *
1840 * ARGUMENTS
1841 * IN:
1842 * p_init_msg_list Initialize message stack if it is set to
1843 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1844 * p_location_rec Location record.
1845 * IN/OUT:
1846 * p_object_version_number Used for locking the being updated record.
1847 * OUT:
1848 * x_return_status Return status after the call. The status can
1849 * be FND_API.G_RET_STS_SUCCESS (success),
1850 * FND_API.G_RET_STS_ERROR (error),
1851 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1852 * x_msg_count Number of messages in message stack.
1853 * x_msg_data Message text if x_msg_count is 1.
1854 *
1855 * NOTES
1856 *
1857 * MODIFICATION HISTORY
1858 *
1859 * 07-23-2001 Indrajit Sen o Created.
1860 * 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
1861 * For non-profile entities, the concept of
1862 * select/de-select data-sources is obsoleted.
1863 *
1864 */
1865
1866 PROCEDURE update_location (
1867 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
1868 p_location_rec IN LOCATION_REC_TYPE,
1869 p_object_version_number IN OUT NOCOPY NUMBER,
1870 x_return_status OUT NOCOPY VARCHAR2,
1871 x_msg_count OUT NOCOPY NUMBER,
1872 x_msg_data OUT NOCOPY VARCHAR2
1873 ) IS
1874
1875 l_addr_val_status VARCHAR2(30);
1876 l_addr_warn_msg VARCHAR2(2000);
1877
1878 BEGIN
1879
1880 update_location(
1881 p_init_msg_list => p_init_msg_list,
1882 p_location_rec => p_location_rec,
1883 p_do_addr_val => 'N',
1884 p_object_version_number => p_object_version_number,
1885 x_addr_val_status => l_addr_val_status,
1886 x_addr_warn_msg => l_addr_warn_msg,
1887 x_return_status => x_return_status,
1888 x_msg_count => x_msg_count,
1889 x_msg_data => x_msg_data);
1890
1891 EXCEPTION WHEN OTHERS THEN
1892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1893 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1894 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1895 FND_MSG_PUB.ADD;
1896 FND_MSG_PUB.Count_And_Get(
1897 p_encoded => FND_API.G_FALSE,
1898 p_count => x_msg_count,
1899 p_data => x_msg_data);
1900 END update_location;
1901
1902 /**
1903 * PROCEDURE get_location_rec
1904 *
1905 * DESCRIPTION
1906 * Gets location record.
1907 *
1908 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1909 * hz_locations_PKG.Select_Row
1910 *
1911 * ARGUMENTS
1912 * IN:
1913 * p_init_msg_list Initialize message stack if it is set to
1914 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1915 * p_location_id Location ID.
1916 * IN/OUT:
1917 * OUT:
1918 * x_location_rec Location record.
1919 * x_return_status Return status after the call. The status can
1920 * be FND_API.G_RET_STS_SUCCESS (success),
1921 * FND_API.G_RET_STS_ERROR (error),
1922 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1923 * x_msg_count Number of messages in message stack.
1924 * x_msg_data Message text if x_msg_count is 1.
1925 *
1926 * NOTES
1927 *
1928 * MODIFICATION HISTORY
1929 *
1930 * 07-23-2001 Indrajit Sen o Created.
1931 * 25-JAN-2002 Joe del Callar Bug 2200569: added the column
1932 * geometry status for spatial data
1933 * integration.
1934 * 14-NOV-2003 Rajib Ranjan Borah o Bug 2670546.Reintroduced column
1935 * delivery_point_code
1936 */
1937
1938 PROCEDURE get_location_rec (
1939 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1940 p_location_id IN NUMBER,
1941 x_location_rec OUT NOCOPY location_rec_type,
1942 x_return_status OUT NOCOPY VARCHAR2,
1943 x_msg_count OUT NOCOPY NUMBER,
1944 x_msg_data OUT NOCOPY VARCHAR2
1945 ) IS
1946 l_debug_prefix VARCHAR2(30) := '';
1947 BEGIN
1948 -- Check if API is called in debug mode. If yes, enable debug.
1949 --enable_debug;
1950
1951 -- Debug info.
1952 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1953 hz_utility_v2pub.debug(p_message=>'get_location_rec (+)',
1954 p_prefix=>l_debug_prefix,
1955 p_msg_level=>fnd_log.level_procedure);
1956 END IF;
1957
1958 -- Initialize message list if p_init_msg_list is set to TRUE.
1959 IF fnd_api.to_boolean(p_init_msg_list) THEN
1960 fnd_msg_pub.initialize;
1961 END IF;
1962
1963 -- Initialize API return status to success.
1964 x_return_status := fnd_api.g_ret_sts_success;
1965
1966 -- Check whether primary key has been passed in.
1967 IF p_location_id IS NULL OR
1968 p_location_id = fnd_api.g_miss_num THEN
1969 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
1970 fnd_message.set_token('COLUMN', 'location_id');
1971 fnd_msg_pub.add;
1972 RAISE fnd_api.g_exc_error;
1973 END IF;
1974
1975 x_location_rec.location_id := p_location_id;
1976
1977 -- Debug info.
1978 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1979 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.Select_Row (+)',
1980 p_prefix=>l_debug_prefix,
1981 p_msg_level=>fnd_log.level_procedure);
1982 END IF;
1983
1984 hz_locations_pkg.select_row (
1985 x_location_id => x_location_rec.location_id,
1986 x_attribute_category => x_location_rec.attribute_category,
1987 x_attribute1 => x_location_rec.attribute1,
1988 x_attribute2 => x_location_rec.attribute2,
1989 x_attribute3 => x_location_rec.attribute3,
1990 x_attribute4 => x_location_rec.attribute4,
1991 x_attribute5 => x_location_rec.attribute5,
1992 x_attribute6 => x_location_rec.attribute6,
1993 x_attribute7 => x_location_rec.attribute7,
1994 x_attribute8 => x_location_rec.attribute8,
1995 x_attribute9 => x_location_rec.attribute9,
1996 x_attribute10 => x_location_rec.attribute10,
1997 x_attribute11 => x_location_rec.attribute11,
1998 x_attribute12 => x_location_rec.attribute12,
1999 x_attribute13 => x_location_rec.attribute13,
2000 x_attribute14 => x_location_rec.attribute14,
2001 x_attribute15 => x_location_rec.attribute15,
2002 x_attribute16 => x_location_rec.attribute16,
2003 x_attribute17 => x_location_rec.attribute17,
2004 x_attribute18 => x_location_rec.attribute18,
2005 x_attribute19 => x_location_rec.attribute19,
2006 x_attribute20 => x_location_rec.attribute20,
2007 x_orig_system_reference => x_location_rec.orig_system_reference,
2008 x_country => x_location_rec.country,
2009 x_address1 => x_location_rec.address1,
2010 x_address2 => x_location_rec.address2,
2011 x_address3 => x_location_rec.address3,
2012 x_address4 => x_location_rec.address4,
2013 x_city => x_location_rec.city,
2014 x_postal_code => x_location_rec.postal_code,
2015 x_state => x_location_rec.state,
2016 x_province => x_location_rec.province,
2017 x_county => x_location_rec.county,
2018 x_address_key => x_location_rec.address_key,
2019 x_address_style => x_location_rec.address_style,
2020 x_validated_flag => x_location_rec.validated_flag,
2021 x_address_lines_phonetic => x_location_rec.address_lines_phonetic,
2022 x_po_box_number => x_location_rec.po_box_number,
2023 x_house_number => x_location_rec.house_number,
2024 x_street_suffix => x_location_rec.street_suffix,
2025 x_street => x_location_rec.street,
2026 x_street_number => x_location_rec.street_number,
2027 x_floor => x_location_rec.floor,
2028 x_suite => x_location_rec.suite,
2029 x_postal_plus4_code => x_location_rec.postal_plus4_code,
2030 x_position => x_location_rec.position,
2031 x_location_directions => x_location_rec.location_directions,
2032 x_address_effective_date => x_location_rec.address_effective_date,
2033 x_address_expiration_date => x_location_rec.address_expiration_date,
2034 x_clli_code => x_location_rec.clli_code,
2035 x_language => x_location_rec.language,
2036 x_short_description => x_location_rec.short_description,
2037 x_description => x_location_rec.description,
2038 x_content_source_type => x_location_rec.content_source_type,
2039 x_loc_hierarchy_id => x_location_rec.loc_hierarchy_id,
2040 x_sales_tax_geocode => x_location_rec.sales_tax_geocode,
2041 x_sales_tax_inside_city_limits => x_location_rec.sales_tax_inside_city_limits,
2042 x_fa_location_id => x_location_rec.fa_location_id,
2043 x_geometry => x_location_rec.geometry,
2044 x_timezone_id => x_location_rec.timezone_id,
2045 x_created_by_module => x_location_rec.created_by_module,
2046 x_application_id => x_location_rec.application_id,
2047 x_geometry_status_code => x_location_rec.geometry_status_code,
2048 x_actual_content_source => x_location_rec.actual_content_source,
2049 -- Bug 2670546
2050 x_delivery_point_code => x_location_rec.delivery_point_code
2051 );
2052
2053 -- Debug info.
2054 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2055 hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.select_row (-)',
2056 p_prefix=>l_debug_prefix,
2057 p_msg_level=>fnd_log.level_procedure);
2058 END IF;
2059
2060 --Standard call to get message count and if count is 1, get message info.
2061 fnd_msg_pub.count_and_get(
2062 p_encoded => fnd_api.g_false,
2063 p_count => x_msg_count,
2064 p_data => x_msg_data
2065 );
2066
2067 -- Debug info.
2068 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2069 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2070 p_msg_data=>x_msg_data,
2071 p_msg_type=>'WARNING',
2072 p_msg_level=>fnd_log.level_exception);
2073 END IF;
2074 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2075 hz_utility_v2pub.debug(p_message=>'get_location_rec (-)',
2076 p_prefix=>l_debug_prefix,
2077 p_msg_level=>fnd_log.level_procedure);
2078 END IF;
2079
2080 -- Check if API is called in debug mode. If yes, disable debug.
2081 --disable_debug;
2082 EXCEPTION
2083 WHEN fnd_api.g_exc_error THEN
2084 x_return_status := fnd_api.g_ret_sts_error;
2085 fnd_msg_pub.count_and_get(
2086 p_encoded => fnd_api.g_false,
2087 p_count => x_msg_count,
2088 p_data => x_msg_data
2089 );
2090
2091 -- Debug info.
2092 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2093 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2094 p_msg_data=>x_msg_data,
2095 p_msg_type=>'ERROR',
2096 p_msg_level=>fnd_log.level_error);
2097 END IF;
2098 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2099 hz_utility_v2pub.debug(p_message=>'get_location_rec (-)',
2100 p_prefix=>l_debug_prefix,
2101 p_msg_level=>fnd_log.level_procedure);
2102 END IF;
2103
2104 -- Check if API is called in debug mode. If yes, disable debug.
2105 --disable_debug;
2106
2107 WHEN fnd_api.g_exc_unexpected_error THEN
2108 x_return_status := fnd_api.g_ret_sts_unexp_error;
2109
2110 fnd_msg_pub.count_and_get(
2111 p_encoded => fnd_api.g_false,
2112 p_count => x_msg_count,
2113 p_data => x_msg_data
2114 );
2115
2116 -- Debug info.
2117 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2118 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2119 p_msg_data=>x_msg_data,
2120 p_msg_type=>'UNEXPECTED ERROR',
2121 p_msg_level=>fnd_log.level_error);
2122 END IF;
2123 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2124 hz_utility_v2pub.debug(p_message=>'get_location_rec (-)',
2125 p_prefix=>l_debug_prefix,
2126 p_msg_level=>fnd_log.level_procedure);
2127 END IF;
2128
2129 -- Check if API is called in debug mode. If yes, disable debug.
2130 --disable_debug;
2131
2132 WHEN OTHERS THEN
2133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2134
2135 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2136 fnd_message.set_token('ERROR' ,SQLERRM);
2137 fnd_msg_pub.add;
2138
2139 fnd_msg_pub.count_and_get(
2140 p_encoded => fnd_api.g_false,
2141 p_count => x_msg_count,
2142 p_data => x_msg_data
2143 );
2144
2145 -- Debug info.
2146 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2147 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2148 p_msg_data=>x_msg_data,
2149 p_msg_type=>'SQL ERROR',
2150 p_msg_level=>fnd_log.level_error);
2151 END IF;
2152 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2153 hz_utility_v2pub.debug(p_message=>'get_location_rec (-)',
2154 p_prefix=>l_debug_prefix,
2155 p_msg_level=>fnd_log.level_procedure);
2156 END IF;
2157
2158 -- Check if API is called in debug mode. If yes, disable debug.
2159 --disable_debug;
2160
2161 END get_location_rec;
2162
2163 /**
2164 * PROCEDURE fill_geometry_for_locations
2165 *
2166 * DESCRIPTION
2167 * Concurrent program to fill geometry column in hz_locations.
2168 *
2169 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2170 *
2171 * ARGUMENTS
2172 * OUT:
2173 * p_errbuf Error buffer.
2174 * p_retcode Return code.
2175 *
2176 * NOTES
2177 *
2178 * MODIFICATION HISTORY
2179 *
2180 * 07-23-2001 Indrajit Sen o Created.
2181 *
2182 */
2183
2184 PROCEDURE fill_geometry_for_locations(
2185 p_errbuf OUT NOCOPY VARCHAR2,
2186 p_retcode OUT NOCOPY NUMBER
2187 ) IS
2188 l_status VARCHAR2(10);
2189 l_count NUMBER;
2190 l_loc_rec LOCATION_REC_TYPE;
2191 CURSOR c_locations IS
2192 SELECT COUNTRY,
2193 STATE,
2194 CITY,
2195 POSTAL_CODE,
2196 ADDRESS1,
2197 STREET,
2198 STREET_SUFFIX,
2199 STREET_NUMBER,
2200 HOUSE_NUMBER
2201 FROM hz_locations
2202 WHERE GEOMETRY IS NULL OR
2203 GEOMETRY = hz_geometry_default
2204 FOR UPDATE OF GEOMETRY;
2205
2206 BEGIN
2207
2208 p_retcode := 0;
2209 l_count := 0;
2210 FOR rec IN c_locations
2211 LOOP
2212 l_loc_rec.country := rec.country;
2213 l_loc_rec.state := rec.state;
2214 l_loc_rec.city := rec.city;
2215 l_loc_rec.postal_code := rec.postal_code;
2216 l_loc_rec.address1 := rec.address1;
2217 l_loc_rec.street := rec.street;
2218 l_loc_rec.street_suffix := rec.street_suffix;
2219 l_loc_rec.street_number := rec.street_number;
2220 l_loc_rec.house_number := rec.house_number;
2221
2222 fill_geometry(l_loc_rec, l_status);
2223
2224 IF (l_status = 'E') THEN
2225 EXIT;
2226 ELSIF (l_status = 'Y') THEN
2227 UPDATE hz_locations SET GEOMETRY = l_loc_rec.geometry
2228 WHERE CURRENT OF c_locations;
2229 l_count := l_count + 1;
2230 END IF;
2231 END LOOP;
2232 COMMIT;
2233
2234 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Records processed: ' || l_count);
2235
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in filling geometry column: ' || SQLERRM);
2239
2240 END fill_geometry_for_locations;
2241 /*----------------------------------------------------------------------------*
2242 | procedure |
2243 | update_location_search |
2244 | |
2245 | DESCRIPTION |
2246 | This procedure updates the address_text column of |
2247 | hz_cust_acct_sites_all with the NULL value |
2248 | only to change the address_text column status |
2249 | so that interMedia index can be created on it to perform text searches. |
2250 | |
2251 | NOTE :- After Calling this procedure the user has to execute the |
2252 | Customer Text Data Creation concurrent program to see the changes. |
2253 | |
2254 | PARAMETERS |
2255 | INPUT |
2256 | p_old_location_rec |
2257 | p_location_rec |
2258 | OUTPUT |
2259 | |
2260 | |
2261 | HISTORY |
2262 | 15-Mar-2004 Ramesh Ch Created |
2263 *----------------------------------------------------------------------------*/
2264
2265 PROCEDURE update_location_search(p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
2266 p_new_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
2267 )
2268 IS
2269 Cursor c_locations(p_location_id NUMBER) IS
2270 SELECT ac.CUST_ACCT_SITE_ID
2271 FROM HZ_LOCATIONS loc, HZ_PARTY_SITES ps,
2272 HZ_CUST_ACCT_SITES_ALL ac
2273 WHERE loc.LOCATION_ID=p_location_id
2274 AND loc.LOCATION_ID = ps.LOCATION_ID
2275 AND ps.PARTY_SITE_ID=ac.PARTY_SITE_ID;
2276 TYPE siteidtab IS TABLE OF HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID%TYPE;
2277 l_siteidtab siteidtab;
2278
2279 BEGIN
2280 savepoint update_location_search;
2281 -- Bug Fix:4006266
2282 IF( isModified(p_old_location_rec.address1 ,p_new_location_rec.address1)
2283 OR isModified(p_old_location_rec.address2 ,p_new_location_rec.address2)
2284 OR isModified(p_old_location_rec.address3 ,p_new_location_rec.address3)
2285 OR isModified(p_old_location_rec.address4 ,p_new_location_rec.address4)
2286 OR isModified(p_old_location_rec.city ,p_new_location_rec.city)
2287 OR isModified(p_old_location_rec.state ,p_new_location_rec.state)
2288 OR isModified(p_old_location_rec.postal_code ,p_new_location_rec.postal_code)
2289 OR isModified(p_old_location_rec.province ,p_new_location_rec.province)
2290 ) THEN
2291 OPEN c_locations(p_old_location_rec.location_id);
2292 FETCH c_locations BULK COLLECT INTO l_siteidtab;
2293 CLOSE c_locations;
2294 IF l_siteidtab.COUNT >0 THEN
2295 FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
2296 update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
2297 END IF;
2298 END IF;
2299 EXCEPTION
2300 WHEN OTHERS THEN
2301 ROLLBACK TO update_location_search;
2302 RAISE;
2303 END;
2304 FUNCTION isModified(p_old_value IN VARCHAR2,p_new_value IN VARCHAR2) RETURN BOOLEAN
2305 IS
2306 BEGIN
2307 IF p_new_value IS NOT NULL AND p_new_value <> FND_API.G_MISS_CHAR THEN
2308 RETURN NVL(NOT (p_old_value=p_new_value),TRUE);
2309 ELSIF (p_old_value IS NOT NULL AND p_old_value <> FND_API.G_MISS_CHAR)
2310 AND p_new_value = FND_API.G_MISS_CHAR THEN
2311 RETURN TRUE;
2312 ELSE
2313 RETURN FALSE;
2314 END IF;
2315 END;
2316
2317 /**
2318 * PRIVATE PROCEDURE check_obsolete_columns
2319 *
2320 * DESCRIPTION
2321 * Check if user is using obsolete columns.
2322 *
2323 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2324 *
2325 * MODIFICATION HISTORY
2326 *
2327 * 07-25-2005 Jianying Huang o Created.
2328 *
2329 */
2330
2331 PROCEDURE check_obsolete_columns (
2332 p_create_update_flag IN VARCHAR2,
2333 p_location_rec IN location_rec_type,
2334 p_old_location_rec IN location_rec_type DEFAULT NULL,
2335 x_return_status IN OUT NOCOPY VARCHAR2
2336 ) IS
2337
2338 BEGIN
2339
2340 -- check floor
2341 IF (p_create_update_flag = 'C' AND
2342 p_location_rec.floor IS NOT NULL AND
2343 p_location_rec.floor <> FND_API.G_MISS_CHAR) OR
2344 (p_create_update_flag = 'U' AND
2345 p_location_rec.floor IS NOT NULL AND
2346 p_location_rec.floor <> p_old_location_rec.floor)
2347 THEN
2348 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
2349 FND_MESSAGE.SET_TOKEN('COLUMN', 'floor');
2350 FND_MSG_PUB.ADD;
2351 x_return_status := FND_API.G_RET_STS_ERROR;
2352 END IF;
2353
2354 -- check house_number
2355 IF (p_create_update_flag = 'C' AND
2356 p_location_rec.house_number IS NOT NULL AND
2357 p_location_rec.house_number <> FND_API.G_MISS_CHAR) OR
2358 (p_create_update_flag = 'U' AND
2359 p_location_rec.house_number IS NOT NULL AND
2360 p_location_rec.house_number <> p_old_location_rec.house_number)
2361 THEN
2362 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
2363 FND_MESSAGE.SET_TOKEN('COLUMN', 'house_number');
2364 FND_MSG_PUB.ADD;
2365 x_return_status := FND_API.G_RET_STS_ERROR;
2366 END IF;
2367
2368 -- check po_box_number
2369 IF (p_create_update_flag = 'C' AND
2370 p_location_rec.po_box_number IS NOT NULL AND
2371 p_location_rec.po_box_number <> FND_API.G_MISS_CHAR) OR
2372 (p_create_update_flag = 'U' AND
2373 p_location_rec.po_box_number IS NOT NULL AND
2374 p_location_rec.po_box_number <> p_old_location_rec.po_box_number)
2375 THEN
2376 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
2377 FND_MESSAGE.SET_TOKEN('COLUMN', 'po_box_number');
2378 FND_MSG_PUB.ADD;
2379 x_return_status := FND_API.G_RET_STS_ERROR;
2380 END IF;
2381
2382 -- check street
2383 IF (p_create_update_flag = 'C' AND
2384 p_location_rec.street IS NOT NULL AND
2385 p_location_rec.street <> FND_API.G_MISS_CHAR) OR
2386 (p_create_update_flag = 'U' AND
2387 p_location_rec.street IS NOT NULL AND
2388 p_location_rec.street <> p_old_location_rec.street)
2389 THEN
2390 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
2391 FND_MESSAGE.SET_TOKEN('COLUMN', 'street');
2392 FND_MSG_PUB.ADD;
2393 x_return_status := FND_API.G_RET_STS_ERROR;
2394 END IF;
2395
2396 -- check street_number
2397 IF (p_create_update_flag = 'C' AND
2398 p_location_rec.street_number IS NOT NULL AND
2399 p_location_rec.street_number <> FND_API.G_MISS_CHAR) OR
2400 (p_create_update_flag = 'U' AND
2401 p_location_rec.street_number IS NOT NULL AND
2402 p_location_rec.street_number <> p_old_location_rec.street_number)
2403 THEN
2404 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
2405 FND_MESSAGE.SET_TOKEN('COLUMN', 'street_number');
2406 FND_MSG_PUB.ADD;
2407 x_return_status := FND_API.G_RET_STS_ERROR;
2408 END IF;
2409
2410 -- check street_suffix
2411 IF (p_create_update_flag = 'C' AND
2412 p_location_rec.street_suffix IS NOT NULL AND
2413 p_location_rec.street_suffix <> FND_API.G_MISS_CHAR) OR
2414 (p_create_update_flag = 'U' AND
2415 p_location_rec.street_suffix IS NOT NULL AND
2416 p_location_rec.street_suffix <> p_old_location_rec.street_suffix)
2417 THEN
2418 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
2419 FND_MESSAGE.SET_TOKEN('COLUMN', 'street_suffix');
2420 FND_MSG_PUB.ADD;
2421 x_return_status := FND_API.G_RET_STS_ERROR;
2422 END IF;
2423
2424 -- check suite
2425 IF (p_create_update_flag = 'C' AND
2426 p_location_rec.suite IS NOT NULL AND
2427 p_location_rec.suite <> FND_API.G_MISS_CHAR) OR
2428 (p_create_update_flag = 'U' AND
2429 p_location_rec.suite IS NOT NULL AND
2430 p_location_rec.suite <> p_old_location_rec.suite)
2431 THEN
2432 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
2433 FND_MESSAGE.SET_TOKEN('COLUMN', 'suite');
2434 FND_MSG_PUB.ADD;
2435 x_return_status := FND_API.G_RET_STS_ERROR;
2436 END IF;
2437
2438 END check_obsolete_columns;
2439
2440 END HZ_LOCATION_V2PUB;