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