8 Replies Latest reply on Jul 21, 2012 3:05 PM by 948783

    Supplier Site Address Update

      I have a requirement to update supplier site address in EBS Release 12.

      As in Release 12 these addresses are moved to TCA and can be attached to mutiple sites, is there any way to update address for only one of the site attached to address. Eg:

      I have one supplier XYZ with 2 addresses A1. Address A1 is having two sites S1 and S2. I have to update address for S2 to A2 but address for S1 should remain A1. This is very valid business scenario.

      I am not able to do it from screen as I can not create a new site code S2 for same supplier XYZ in same OU with in address A2. Also, customer is not agreeing on update existing S2 code to something else to enable create the same in A2.

      Any solutions are welcome.

      Thanks in advance.
        • 1. Re: Supplier Site Address Update
          Any solutions for this ? I am facing the same problem in AP.
          • 2. Re: Supplier Site Address Update
            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

            SELECT hl.object_version_number
            ,hl.location_id -------------------------------->location_id of a site
            INTO l_object_version_number
            FROM po_vendors pv
            ,po_vendor_sites_all pvsa
            ,hz_party_sites hps
            ,hz_locations hl
            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
            • 3. Re: Supplier Site Address Update
              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 ?
              • 4. Re: Supplier Site Address Update
                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.


                • 5. Re: Supplier Site Address Update

                  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.

                  • 6. Re: Supplier Site Address Update
                    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

                    V1----> H1
                    V2----> H1
                    V3----> H1
                    V4----> W1
                    V5----> W1

                    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)
                    V3----> H1
                    V4----> W1
                    V5----> W1
                    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
                    • 7. Re: Supplier Site Address Update
                      Partially correct.

                      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.

                      • 8. Re: Supplier Site Address Update
                        Hi Rishi,
                        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