This content has been marked as final. Show 8 replies
Any solutions for this ? I am facing the same problem in AP.
I didn't understand your scenario well.According to my understanding every supplier site has its own address stored in hz_locations.
Even if two sites have same address still two entries are created in hz_locations with different location_id's pointing to respective sites.This is the api used to update the location
,hl.location_id -------------------------------->location_id of a site
FROM po_vendors pv
WHERE pvsa.vendor_id = pv.vendor_id
AND pvsa.vendor_site_code = p_vendor_site_code ----> vendor site code
AND hps.party_site_id = pvsa.party_site_id
AND hl.location_id = hps.location_id;
p_location_rec => l_location_rec,
p_object_version_number => l_object_version_number,
x_return_status => l_site_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
mmm. We are talking about the front-end screens.
I go to Supplier Page, pick one supplier and under that I go to Address Book where I have two Addresses listed.
Home Address, Work Address.
I have five different sites (H1,H2,H3,H4,H5) linked to the Home Address and 3 of them linked to Work Address.(W1,W2,W3)
Our requirement is: W1, W2 needs to be updated to use Home Address.
How do you do this via front-end screens ?
You have to actually create new sites mapped to the new address by end-dating the old ones.
Following steps can be executed to fulfil the requirements.
1. Go to "Address Book" - Click on the Manage sites icon for the respective site then deactivate the relevant sites from the old address by entering the "Inactive Date".
2. Go back to "Address Book" again. Click on the Manage sites icon and click on "Create" to create new sites.
Looks like this change has been implemented by Oracle to establish traceability.
But user does not want to change site code. I think there is a primary on vendor id , vendor site code and org id. We cannot create a new site with same site code.
I will check on locations table. I doubt we have different location id for each address asssociated to vendor site. This will defeat purpose of TCA of centralising the address.
After trying your scenario many time from front end i understood this
When a vendor site is created with vendor site code it will be assigned to a location_id .This combination will be stored in hz_party_sites.Now you cannot delete this combination from the base table but you can deactivate it And most important thing is Vendor site code is UNIQUE for each vendor .for eg:(V1,V2,V3,V4,V5) are vendor sites and (H1,W1) are location_ids
When it comes to your scenario what you are doing is you are trying to create a new combination with vendor site code with other location_id which is not possible because it violate unique constraint of vendor site code for that vendor.
V1----> H1 (deactivated)
V2----> H1 (deactivated)
V1-----> W1 (Unique constraint violation as V1 is already preasent in table but in deactived state)
V2------> W1 (Unique constraint violation as V2 is already preasent in table but in deactived state)
But you can update the location address assigned to that location_id.but the problem it will change all the addresses of vendor sites for which this location_id is assigned.
Edited by: 945780 on Jul 20, 2012 12:04 PM
Edited by: 945780 on Jul 20, 2012 12:08 PM
Unique constraint is on V1, VS1 and O1.
V1 - Vendor
VS1 - Vendor Site
O1 - Org id
All VS1, VS2 and VS3 are having same address (Location id in table).
I need to update address for VS3. VS1 and VS2 address should not change and user doesnt want to change site code for VS3
If address attached to these sites is updated, it will update for all three sites.
If user deactivates VS3 and creates new one, unique constraint violated. You cannot create multiple sites for a vendor with same site code in an operating unit.
I Logged SR with oracle support and they dint have any answer apart from creating a new site code.
Sorry i think i didn't explained well in my post.But i tried to explain the same thing. In your scenario Unique constraint violation occur at hz_party_sites table where vendor site is assigned to location_id(address).Because primary key is define on party_site_id in hz_party_sites which is directly related to vendor_site_id
Only solution for your problem is create different location for each vendor site even if they have same address. So that one vendor site location address doesn't depend on other vendor site location address.It will solve the problem of maintaining same vendor site code even after updating the address.But it will create lot of duplicate addresses in EBS.But i think there is no other way.
If you get any update on SR from oracle please let us know
Edited by: 945780 on Jul 21, 2012 7:56 AM