[Home] [Help]
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;
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:
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
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 (
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.
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,
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);
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;
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);
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:
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:
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
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,
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',
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:
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
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:
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
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
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.
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:
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
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
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)
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)
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)
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)
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)
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)
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)
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))
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
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;
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
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;
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;
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;
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
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
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;
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:
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;
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:
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)
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)
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)
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)
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)
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)
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)
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)
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
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
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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'
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;
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:
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:
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:
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.
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: *
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
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,
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.
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:
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
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
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:
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.
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,
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,
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;
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:
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
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');
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');
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.
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
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
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,
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);
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.
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,
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);
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.
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;
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.
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.
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.
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: *
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
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,
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(
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:
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.
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: *
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
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,
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.
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:
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
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,
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',
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:
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.
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
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: --
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');
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:
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;
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:
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);
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:
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)
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)
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';
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:
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;
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);
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;
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
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:
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.
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;
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:
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
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',
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.
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;
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.
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(
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);
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.
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(
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);
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.
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;
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.
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:
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.
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: *
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
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,
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(
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:
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.
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.
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: *
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
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,
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.
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
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;
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:
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:
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,
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
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:
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,
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,
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:
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;
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:
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;
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;
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;
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
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
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
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
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
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
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
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
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
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
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
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
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
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: