13 Replies Latest reply: May 9, 2012 1:58 AM by IdrisAli RSS

    Convert customers of type person to organization

    JayPatwa
      Hello Everyone,

      Is it possible to convert a party type of person to organization by some TCA API?
        • 1. Re: Convert customers of type person to organization
          746384
          Hi,
          Even I am looking for an exact API. But still now didnot come across any such API for changing the customer type from person to organization. If any one knows pls let us know.
          • 2. Re: Convert customers of type person to organization
            Sujoy
            Hi,

            I have not come across any TCA API to do that.
            However, I have updated customers of type 'ORGANIZATION' to 'PERSON' using Customer Interface(RACUST). I believe that you should be able to do the reverse also.

            182965.1 - RACUST: How the Customer Interface program works In Update Mode. INSERT_UPDATE_FLAG = U

            Regards,
            Sujoy
            • 3. Re: Convert customers of type person to organization
              JayPatwa
              Thanks Sujoy.
              That sounds promising. Was it in R12?
              By any chance you can share the code you used to achieve your requirement?

              Also, I am wondering how the customer looked like when you updated the type from Organization to person.
              For eg. For Organization, there is customer name. But for Person there is First name and Last name. How did that work?

              Regards.
              • 4. Re: Convert customers of type person to organization
                Sujoy
                Hi JayPatwa,

                I did that on an 11.5.10.2 installation. Hope that it work on R12.
                We split the organization name into first,middle and last names based on some logic. When we queried the customer from front end, all three parts of the name were displayed.
                Below are the major steps that I followed. Of course, I had to remove the details due to company policies :)
                Check the interface table documents about the PERSON_FLAG ...there was something to be wary of regarding this column.
                --set person flag
                   l_person_flag:='Y';
                --split name into first, middle and last
                    l_last_name   :=last_name;
                    l_middle_name :=middle_name;
                    l_first_name  :=first_name;
                --obtain the orig_system_reference from hz_parties
                      SELECT orig_system_reference 
                        INTO l_add_ref  
                        FROM hz_parties 
                       WHERE party_number=consumer_id;
                --insert into ra_customers_interface_all
                    INSERT INTO ra_customers_interface_all
                              ( 
                                orig_system_customer_ref
                                ,insert_update_flag
                                ,customer_name
                                ,customer_name_phonetic
                                ,person_flag
                                ,person_first_name
                                ,person_last_name
                                ,customer_status
                                ,customer_attribute_category
                                ,customer_attribute5
                                ,customer_attribute6
                                ,last_updated_by
                                ,last_update_date
                                ,created_by
                                ,creation_date
                                ,last_update_login
                                ,url
                              )
                         VALUES
                              (  
                                l_add_ref
                                ,'U'
                                ,l_consumer_name
                                ,'Alternate Name'
                                ,l_person_flag
                                ,l_first_name
                                ,l_last_name
                                ,'A'
                                ,'Input extra consumer details'
                                ,consumer_class
                                ,consumer_catg 
                                ,FND_PROFILE.VALUE('USER_ID')
                                ,SYSDATE
                                ,FND_PROFILE.VALUE('USER_ID')
                                ,SYSDATE
                                ,FND_PROFILE.VALUE('LOGIN_ID')
                                ,consumer_url 
                               );
                --obtain the orig_system_reference
                --if the orig_system_reference does not exist then generate a new orig_system_address_reference 
                --after checking that the customer exists in hca_cust_accounts
                        SELECT hcasa.orig_system_reference
                          INTO l_cust_address_ref
                          FROM hz_cust_acct_sites_all hcasa
                               ,hz_cust_accounts      hca
                               ,hz_parties            hp
                         WHERE hp.party_id=hca.party_id
                           AND hca.cust_account_id=hcasa.cust_account_id
                           AND hp.party_number=consumer_id;
                --insert into ra_customers_interface_all (for address details)
                      INSERT INTO ra_customers_interface_all
                                (
                                  orig_system_customer_ref
                                  ,orig_system_address_ref
                                  ,insert_update_flag
                                  ,customer_name
                                  ,person_flag
                                  ,customer_status
                                  ,person_first_name
                                  ,person_last_name
                                  ,address1
                                  ,address2
                                  ,address3
                                  ,address4
                                  ,city
                                  ,province
                                  ,state
                                  ,country
                                  ,postal_code
                                  ,org_id
                                  ,last_updated_by
                                  ,last_update_date
                                  ,created_by
                                  ,creation_date
                                  ,last_update_login
                                 )
                           VALUES
                                (
                                  l_add_ref
                                  ,l_cust_address_ref
                                  ,'U'
                                  ,l_CONSUMER_name
                                  ,l_person_flag
                                  ,'A'
                                  ,l_first_name
                                  ,l_last_name
                                  ,address1
                                  ,address2
                                  ,address3
                                  ,address4
                                  ,'abcd'
                                  ,NULL
                                  ,'abcd'
                                  ,country
                                  ,l.postal_code
                                  ,fnd_profile.VALUE('ORG_ID')
                                  ,fnd_profile.VALUE('USER_ID')
                                  ,SYSDATE
                                  ,fnd_profile.VALUE('USER_ID')
                                  ,SYSDATE
                                  ,fnd_profile.VALUE('LOGIN_ID')
                                 );
                --submit RACUST program
                Hope this helps.

                Regards,
                Sujoy
                • 5. Re: Convert customers of type person to organization
                  JayPatwa
                  Thanks Sujoy.
                  We are on R12.
                  In my case we need to convert from Person to Organization.
                  There is no column in the table which says that change the party type to Organization.
                  If i put person_flag = 'N', it says that you cannot update party_type using customer interface.
                  So, I guess we cannot achieve this requirement.
                  • 6. Re: Convert customers of type person to organization
                    Sujoy
                    Hi,

                    Regarding the PERSON_FLAG, the TCA reference guide instructs users to enter Y if your customer is a person. Also the valid values are 'Y','N' and null.
                    Metalink Note#736422.1
                    The TCA FAQ(https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=FAQ&id=220584.1) says that populating PERSON_FLAG as 'N' or null will load a customer as organization.
                    Also check Note#736422.1.

                    Based on the above, I would have tried updating the customer with PERSON_FLAG null just to see if it works.....otherwise there is Oracle Support


                    Regards,
                    Sujoy
                    • 7. Re: Convert customers of type person to organization
                      JayPatwa
                      Yes Sujoy.
                      I have tried all the values.
                      If I try 'Y' in person_flag, it will obviously update the customer (that is already of type PERSON) details as a PERSON.
                      If I try 'N' in person_flag, it it throws error that party_type cannot be updated using Customer Interface because the customer is a PERSON and i set the flag as 'N'.
                      If I keep it NULL, it behaves same as 'Y' assuming that I am updating the PERSON record.

                      It only updates the HZ_CUST_ACCOUNTS table and not hz_parties tables.

                      I am curious what happens in your case.
                      You are updating an ORGANIZATION to PERSON. Does it update hz_parties?
                      Does it also create a new record in hz_organization_profiles?
                      Does it delete record from hz_person_profiles because customer us now a PERSON?

                      Regards,
                      Jay
                      • 8. Re: Convert customers of type person to organization
                        Sujoy
                        Hi Jay,

                        Seems you have to take this up with Oracle Support.
                        As for you questions...well, it has been more than a couple of years since then but I do remember that the hz_parties table did get updated. I cannot confirm the other two questions.
                        Do update this thread if you find a solution for your requirement.


                        Regards,
                        Sujoy
                        • 9. Re: Convert customers of type person to organization
                          746384
                          Hi all,
                          Just wanted to reconfirm this. Yes, even we got the same error 'w3 --> You cannot update the PARTY_TYPE using Customer Interface. Please do not specify a value for PARTY_TYPE when the INSERT_UPDATE_FLAG is set to U', while trying to update the party_type using the customer interface. It is not working for both the cases.(Org to person, Person to Org). We have decided to log a SR with Oracle, mean while if any body has a good luck on this. pls share the same.
                          • 10. Re: Convert customers of type person to organization
                            JayPatwa
                            Do update if you get a solution on the SR.
                            I will also update the thread as soon as I find something.
                            • 11. Re: Convert customers of type person to organization
                              JayPatwa
                              Thanks Sujoy.
                              The way the customer screens are designed in R12, it seems that Oracle does not allow this anymore.
                              I will update the thread if I find otherwise.
                              • 12. Re: Convert customers of type person to organization
                                Sujoy
                                Best of Luck :)
                                • 13. Re: Convert customers of type person to organization
                                  IdrisAli
                                  Hi Jay,

                                  I don't think thats possible using UI/ API or Customer Interface.

                                  If you have not done any transactions on the Person Party , You can purge it ( Party Purge). And create another Organization Party

                                  Incase you want the same Party Id , You can create the Organization Party using API and pass Party Id of Person Party as a parameter to API , This will ensure new Org is created with same party Id.

                                  -Idris