Updating Contact Information

Oracle iStore has a very tight integration with the TCA Architecture or the Customer information that can be managed via the Customer Standard form. Although there are lot of fields Oracle CRM modules update in the backend, there are certain significant fields that are required for further market analysis and statistics.

Job Title is one such field that does not get updated and this is related to the Contact information with in the Customer Standard information. There are standard APIs in TCA that allow us to insert or update records, however to understand how this can be put to best use needs some good understanding of pl/sql and the TCA Architecture.

Here’s a sample that has been used to update the Job Title of a Contact for a Customer Account

PACKAGE BODY XXMCG_UPDATE_CONTACT AS                                                         

PROCEDURE update_contact (p_user_name in varchar2, p_job_title in varchar2, p_return_status out varchar2)

  IS                                                                            

p_ocon_rec             hz_party_contact_v2pub.org_contact_rec_type;

x_org_contact_id NUMBER;                                                       

x_party_rel_id NUMBER;                                                         

x_party_id NUMBER;                                                              

x_party_number VARCHAR2(2000);                                                 

x_return_status VARCHAR2(2000);                                                

x_msg_count NUMBER;                                                            

x_msg_data VARCHAR2(2000);                                                     

contact_error  exception;                                                      

l_err_msg varchar2(2000);                                                      

l_step   varchar2(150);                                                        

l_from_party_id  NUMBER;                                                       

l_to_party_id  NUMBER;                                                         

l_per_party_id   NUMBER;

    l_person_object_version         NUMBER;

    l_person_last_update_date       DATE;

    l_org_contact_rowid             ROWID;

    l_org_contact_object_version    NUMBER;

    l_org_contact_last_update_date  DATE;

    l_rel_rowid                     ROWID;

    l_rel_object_version            NUMBER;

    l_rel_last_update_date          DATE;

    l_dummy_id                      NUMBER;

    l_rel_id                        NUMBER;

    l_party_id                      NUMBER;

    l_party_rowid                   ROWID;

    l_party_object_version          NUMBER;

    l_party_last_update_date        DATE;

    l_contact_pt_rowid              ROWID;

    l_contact_pt_object_version     NUMBER;

    l_contact_pt_last_update_date   DATE;

    l_acct_role_rowid               ROWID;

    l_acct_role_object_version      NUMBER;

    l_acct_role_last_update_date    DATE;

cursor cOrgContactId IS                                    

select  distinct d.cust_account_id , org_contact_id

from fnd_user f, hz_cust_account_roles hz,  hz_parties a, hz_cust_accounts d ,

        hz_org_contacts org_cont,

        hz_relationships rel

 where f.customer_id = hz.party_id and hz.cust_account_id = d.cust_account_id     

     AND  hz.party_id = rel.party_id

      AND  org_cont.party_relationship_id = rel.relationship_id

     AND  rel.subject_id = a.party_id

     AND  rel.subject_table_name = ‘HZ_PARTIES’

     AND  rel.object_table_name = ‘HZ_PARTIES’ and

user_name = p_user_name;

BEGIN                                                                          

      p_ocon_rec.job_title  := p_job_title;

                              for c in cOrgContactId

                              loop

                                          p_ocon_rec.org_contact_id  := c.org_contact_id;

dbms_output.put_line(‘Org Contact ID ‘||c.org_contact_id|| ‘For UserName -’||p_user_name);   

                              end loop;

    l_step := ‘Get Object Version from hz_org_contacts’;

     BEGIN

    SELECT last_update_date,

           object_version_number

      INTO l_org_contact_last_update_date,

           l_org_contact_object_version

      FROM hz_org_contacts

     WHERE org_contact_id = p_ocon_rec.org_contact_id ;

     EXCEPTION

     WHEN NO_DATA_FOUND THEN

        dbms_output.put_line(‘Org contact ID not Found ‘);

     WHEN OTHERS THEN

        dbms_output.put_line(‘Error in Step -’||l_step);

    END;

                             l_step :=‘Call to Update contact API’;

                         <Call us to get more information on what API to be used>

 

                   IF x_return_status = ‘E’ THEN

                            dbms_output.put_line(‘Contact API returns Status =  ‘||x_return_status);

                             dbms_output.put_line(‘Error in update of contact – ‘||x_msg_data);

                             p_return_status := ‘E’;

                             Raise contact_error;

                     else

                         dbms_output.put_line(‘Updated the hz_org_contacts table’); 

                        p_return_status := x_return_status;

                        commit;

                     end if;

 EXCEPTION

        When contact_error THEN

            dbms_output.put_line(‘Error in Step ‘||l_step);

             dbms_output.put_line(‘Executing Rollback’);

             rollback;

 END  update_contact;

 end xxmcg_update_contact;