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