Create Person Party and Org Contact for a Organization Pary in Oracle R12

 --It is always a good idea to initialize apps and set global policy context before using standard API

CREATE OR REPLACE PROCEDURE create_person_party (
   p_in_first_name                IN       VARCHAR2,
   p_in_org_party_id              IN       VARCHAR2,
   p_in_party_number              IN       VARCHAR2,
   p_in_cdh_person_party_number   IN       VARCHAR2,
   x_ou_ebs_per_party_id          OUT      VARCHAR2,
   x_ou_org_contact_id            OUT      VARCHAR2
)
IS
   v_person_rec         hz_party_v2pub.person_rec_type;
   v_org_contact_rec    hz_party_contact_v2pub.org_contact_rec_type;
   x_ou_party_rel_id    NUMBER;
   x_ou_party_id        NUMBER;
   x_ou_party_number    VARCHAR2 (2000);
   x_ou_profile_id      NUMBER;
   x_ou_return_status   VARCHAR2 (2000);
   x_ou_msg_count       NUMBER;
   x_ou_msg_data        VARCHAR2 (2000);
   v_message            VARCHAR2 (5000);
   v_err_message        VARCHAR2 (5000);
   v_ebs_per_party_id   NUMBER                                      := NULL;  --Party ID of the relationship
   v_org_contact_id     NUMBER                                      := NULL;
BEGIN
   fnd_msg_pub.delete_msg (NULL);
   fnd_msg_pub.initialize;
   v_person_rec.person_first_name := p_in_first_name;
   v_person_rec.created_by_module := 'TCA_V2_API';
   hz_party_v2pub.create_person (p_init_msg_list      => 'T',
                                 p_person_rec         => v_person_rec,
                                 x_party_id           => x_ou_party_id,
                                 x_party_number       => x_ou_party_number,
                                 x_profile_id         => x_ou_profile_id,
                                 x_return_status      => x_ou_return_status,
                                 x_msg_count          => x_ou_msg_count,
                                 x_msg_data           => x_ou_msg_data
                                );

   IF x_ou_return_status <> 'S'
   THEN
      FOR j IN 1 .. x_ou_msg_count
      LOOP
         fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
         v_err_message :=
              v_err_message
              || ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
      END LOOP;

      DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
   ELSE
      v_ebs_per_party_id := x_ou_party_id;
      v_org_contact_rec.created_by_module := 'TCA_V2_API';
      v_org_contact_rec.party_rel_rec.subject_id := v_ebs_per_party_id;
      v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
      v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
      v_org_contact_rec.party_rel_rec.object_id := p_in_org_party_id;
      v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
      v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
      v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
      v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
      v_org_contact_rec.party_rel_rec.start_date := SYSDATE;
      v_org_contact_rec.party_rel_rec.status := 'A';
      hz_party_contact_v2pub.create_org_contact ('T',
                                                 v_org_contact_rec,
                                                 x_ou_org_contact_id,
                                                 x_ou_party_rel_id,
                                                 x_ou_party_id,
                                                 x_ou_party_number,
                                                 x_ou_return_status,
                                                 x_ou_msg_count,
                                                 x_ou_msg_data
                                                );
      COMMIT;

      IF x_ou_return_status <> 'S'
      THEN
         FOR j IN 1 .. x_ou_msg_count
         LOOP
            fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
            v_err_message :=
                  v_err_message
               || ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
         END LOOP;

         DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
      ELSE
         DBMS_OUTPUT.put_line ('Org Contact ID = ' || x_ou_org_contact_id);
         DBMS_OUTPUT.put_line (' Party Relationship id = ' || x_ou_party_id);
         v_ebs_per_party_id := x_ou_party_id;
         v_org_contact_id := x_ou_org_contact_id;
      END IF;
   END IF;

   x_ou_org_contact_id := v_org_contact_id;
   x_ou_ebs_per_party_id := v_ebs_per_party_id;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END create_person_party;

Read More

API to create Contact Point ID for a Party in Oracle R12 Example

 -- Make Sure you initialize apps and set mo_global.set_policy_context  

PROCEDURE create_contact_point_id (
      p_in_contact_point_type   IN       VARCHAR2,
      p_in_contact_point        IN       VARCHAR2,
      p_in_party_id             IN       VARCHAR2,
      x_out_cont_point_id       OUT      VARCHAR2
   )
   IS
      v_cpr                   hz_contact_point_v2pub.contact_point_rec_type;
      v_edi_rec               hz_contact_point_v2pub.edi_rec_type;
      v_email_rec             hz_contact_point_v2pub.email_rec_type;
      v_phone_rec             hz_contact_point_v2pub.phone_rec_type;
      v_telex_rec             hz_contact_point_v2pub.telex_rec_type;
      v_web_rec               hz_contact_point_v2pub.web_rec_type;
      x_ou_return_status      VARCHAR2 (2000);
      x_ou_msg_count          NUMBER;
      x_ou_contact_point_id   NUMBER;
      x_ou_msg_data           VARCHAR2 (2000);
      x_ou_output_message     VARCHAR2 (2000)                          := ' ';
      v_message               VARCHAR2 (5000);
      v_err_message           VARCHAR2 (5000);
   BEGIN
 

      IF p_in_contact_point_type = 'PHONE'
      THEN
         v_phone_rec.phone_number := p_in_contact_point;
         v_phone_rec.phone_line_type := 'GEN';
      ELSIF p_in_contact_point_type = 'EMAIL'
      THEN
         v_email_rec.email_format := 'MAILTEXT';
         v_email_rec.email_address := p_in_contact_point;
      END IF;

      /* build contact point rec */
      v_cpr.contact_point_id := NULL;
      v_cpr.contact_point_type := p_in_contact_point_type;
      v_cpr.status := 'A';
      v_cpr.owner_table_name := 'HZ_PARTIES';
      v_cpr.owner_table_id := p_in_party_id;
      v_cpr.primary_flag := 'N';
      v_cpr.created_by_module := 'TCA_V2_API';

      /* call api to create rec */
      IF p_in_contact_point IS NOT NULL
      THEN
         hz_contact_point_v2pub.create_contact_point
                                (p_init_msg_list          => fnd_api.g_true,
                                 p_contact_point_rec      => v_cpr,
                                 p_edi_rec                => v_edi_rec,
                                 p_email_rec              => v_email_rec,
                                 p_phone_rec              => v_phone_rec,
                                 p_telex_rec              => v_telex_rec,
                                 p_web_rec                => v_web_rec,
                                 x_contact_point_id       => x_ou_contact_point_id,
                                 x_return_status          => x_ou_return_status,
                                 x_msg_count              => x_ou_msg_count,
                                 x_msg_data               => x_ou_msg_data
                                );
         COMMIT;
         dbms_output.put_line (x_ou_msg_data);
         dbms_output.put_line (x_ou_return_status);
         dbms_output.put_line (x_ou_msg_count);

         IF x_ou_return_status <> 'S'
         THEN
         
            FOR j IN 1 .. x_ou_msg_count
            LOOP
               fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
               v_err_message :=
                     v_err_message
                  || ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
            END LOOP;

            dbms_output.put_line ('v_err_message: ' || v_err_message);

         ELSE
            x_out_cont_point_id := x_ou_contact_point_id;
         END IF;
      ELSE
         dbms_output.put_line ('No Valid Value for Contact Point Creation: ');
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         dbms_output.put_line (SQLERRM);
   END create_contact_point_id;

Read More

Update OSR in Oracle R12 for HZ_PARTY_SITES table

 -- Make Sure you initialize apps and set mo_global.set_policy_context   


set serveroutput on;
DECLARE
      v_xref_rec                hz_orig_system_ref_pub.orig_sys_reference_rec_type;
      v_object_version_number   NUMBER :=1 ;
      v_return_status           VARCHAR2 (240);
      v_msg_count               NUMBER;
      v_msg_data                VARCHAR2 (4000);
      v_error_message           VARCHAR2 (4000) := NULL;
BEGIN
             v_error_message                        := NULL;
             v_return_status                        := NULL;
             v_msg_count                            := NULL;
             v_msg_data                             := NULL;
           
              v_xref_rec.orig_system           := 'Legacy System';
              v_xref_rec.orig_system_reference := '00138888';  --New OSR
              v_xref_rec.owner_table_name      := 'HZ_PARTY_SITES';
              v_xref_rec.owner_table_id        := '3578'; --Party Site ID
              v_xref_rec.old_orig_system_reference := '00138AA2'; --Old OSR
              v_xref_rec.created_by_module         := 'HZ_IMPORT';
           
               hz_orig_system_ref_pub.update_orig_system_reference (
                              p_init_msg_list            => fnd_api.g_true,
                              p_orig_sys_reference_rec   => v_xref_rec,
                              p_object_version_number    => v_object_version_number,
                              x_return_status            => v_return_status,
                              x_msg_count                => v_msg_count,
                              x_msg_data                 => v_msg_data
                            );
               commit;
               IF v_return_status <> fnd_api.g_ret_sts_success
               THEN
                     IF v_msg_count > 1
                     THEN
                            FOR i IN 1 .. v_msg_count
                            LOOP
                                v_error_message      :=    (v_error_message ||fnd_msg_pub.get (p_encoded => fnd_api.g_true));
                            END LOOP;
                     ELSE
                            v_error_message   := v_msg_data;
                     END IF;    
               END IF;

            dbms_output.put_line(v_return_status||' '||'OSR Process failed with error: ' || v_error_message);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Read More

Query to find the orig system reference for a Customer/Party and other TCA entities

If the data was imported into the EBS system from ABC system and ABC's party id was used as the cross system , the below query can be used to cross-link them

select ORIG_SYSTEM_REFERENCE from apps.HZ_ORIG_SYS_REFERENCES where owner_table_name = 'HZ_PARTIES' and ORIG_SYSTEM = ‘ABC’ and OWNER_TABLE_ID = <party_id in EBS>

ORIG_SYSTEM_REFERENCE = PARTY_ID in ABC

ORIG_SYSTEM = ABC

OWNER_TABLE_ID = Party ID in EBS

OWNER_TABLE_NAME = HZ_PARTIES

Read More

Important TCA lookups

Lookup for Customer Class code

select lookup_type,lookup_code,meaning from ar_lookups where lookup_type in (select class_category from HZ_CLASS_CATEGORIES);


Lookup for Customer Type

select lookup_type,lookup_code,meaning from ar_lookups where lookup_type='CUSTOMER_TYPE';


Lookup for Party Type
select lookup_type,lookup_code,meaning,description from ar_lookups where lookup_type='PARTY_TYPE';


Lookup for Sales Channel

select  lookup_type,lookup_code,meaning from oe_lookups where lookup_type='SALES_CHANNEL';


Lookup for SIC code

select  lookup_type,lookup_code,meaning from ar_lookups where lookup_type='SIC_CODE_TYPE';


Lookup for Status

SELECT lookup_type,lookup_code,
  meaning,
  start_date_active,
  end_date_active,
  description
FROM ar_lookups
WHERE lookup_type = 'REGISTRY_STATUS'
AND enabled_flag  = 'Y';


Lookup for Territory/Countries
SELECT TERRITORY_CODE,NLS_TERRITORY FROM FND_TERRITORIES;

Read More

Customer Conversion/Interface Techniques in Oracle EBS R12

These 3 methods can all be used to create customer records. Following are some considerations to help you decide which method to use:


Customer Interface:

If you want to create accounts and the volume is data is high, then it is better to use Customer Interface program. These would be more optimal for bulk processing than the row by row TCA APIs. It also creates the Party records as by-product.

TCA APIs:

TCA APIs are the best if you want to process a smaller volume of data. TCA APIs are designed for performing row by row operations as if you were entering data into the Customer form. They are not designed for bulk loading of data.
TCA APIs do not scale well for large volumes of data as they consume a lot of CPU time by inserting records into related tables one at a time, see Bug 4724425 TCA APIS BURNING CPU TIME BY RUNNING HUGE NUMBER OF DECODE STATEMENTS


Bulk Import:

The Bulk Import only creates Parties, not Accounts. So, if you use Bulk Import, you still need to create Accounts via TCA APIs if accounts are needed as well.

Read More

TCA Parties Table Drill Down

SELECT * FROM hz_parties WHERE party_name = 'Saikam INTL' -- party_id = 39266,party_number(registry_id)= 1849,party_type = organization

SELECT * FROM hz_cust_accounts WHERE party_id = 563212 -- cust_account_id=10042,account_number=1180

SELECT * FROM hz_cust_acct_sites_all WHERE cust_account_id = 147077 -- cust_acct_site_id=1247,party_site_id=30119

SELECT * FROM hz_cust_site_uses_all WHERE cust_acct_site_id = 117353 --site_use_id=1266,location=1267,

SELECT * FROM hz_party_sites WHERE party_id = 563212  --party_site_id = 30119,location_id=1023,party_site_number=1598

SELECT * FROM hz_locations WHERE location_id =127754

SELECT * FROM hz_party_site_uses WHERE party_site_id = 30119 --party_site_use_id = 29029

SELECT * FROM hz_customer_profiles WHERE cust_account_id = 10042 --cust_account_profile_id=11043,party_id

SELECT * FROM hz_organization_profiles WHERE party_id=39266 --organization_profile_id=30101,

SELECT * FROM hz_person_profiles WHERE party_id=39266

Read More