Mastering Oracle R12 TCA API: A Complete Roadmap

tca api oracle r12. Trading Community Architechure

TCA API in Oracle Apps R12

Learning the Oracle R12 Trading Community Architecture (TCA) APIs can feel like navigating a confusing system. But do not fear! This guide provides a clear, step-by-step roadmap to help you master TCA API Oracle R12. Whether you’re a seasoned Oracle developer or just starting out, we’ll walk you through the essentials.

Table of Contents

TCA API in Oracle EBS

Why Learn TCA API in Oracle Ebs?

Clarifying TCA Architecture

Mastering Party API

Creating and Updating Parties

Create Parties

Update Parties

Creating Customer Profiles

Creating Organization Profiles

Updating Organization Profiles

Manage Customer Accounts and Sites API

Create Customer Account

Update Customer Account

Manage Locations API

Create Location

Update Location

Party Sites API

Create a Party Site

Update Party Site

Manage Account Sites API

Create Customer Account Site

Update Customer Acct Site

Create a Customer Site Use

Update a Customer Site Use

Manage Contacts API

Managing Contact Person for a Customer Organization

Managing Contact Points (Phone, Email, Fax)

Create Person

Create Org Contact

Phone Contact Point

Create Email Contact Point

Advanced API Mastery

Integrating TCA APIs with Other Modules

Why Learn TCA API in Oracle R12?

Before diving in, let’s understand why TCA APIs are critical:

  • Automate Customer Management: Streamline creation, updates, and relationship tracking.
  • Integrate Seamlessly: Sync Oracle R12 with external systems for clean data flow.
  • Enhance Data Accuracy: Maintain consistent, centralized customer data.
  • Customize Business Processes: Tailor Oracle to your organization’s needs.

Clarifying TCA Architecture

Core Components Explained

  • TCA consists of:
    • Party: The core identity (Person, Organization, Relationship).
    • Account: Financial/business relationships with the party.
    • Location: Physical or logical addresses tied to a party.
    • Relationships: Connections between parties (e.g., Contact of an Org).
  • Party Layer holds the universal facts, while Account Layer handles customer-specific details.
  • Understand the key TCA tables:
    • HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_LOCATIONS, HZ_PARTY_RELATIONSHIPS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES
    • Example SQL:
      • SELECT PARTY_NAME, PARTY_TYPE FROM HZ_PARTIES WHERE ROWNUM < 5;

Introduction to Essential of TCA API Oracle R12

  • Important TCA APIs in Oracle:
    • HZ_PARTY_V2PUB – Manage parties
    • HZ_CUST_ACCOUNT_V2PUB – Manage customer accounts
    • HZ_CUST_ACCOUNT_SITE_V2PUB – Manage account sites
    • HZ_CUST_SITE_USE_V2PUB – Manage site uses
    • HZ_LOCATION_V2PUB – Manage locations
    • HZ_PARTY_CONTACT_V2PUB – Manage contacts
    • HZ_CONTACT_POINT_V2PUB – Manage phone, email, fax, URL
    • HZ_PARTY_SITE_V2PUB – Manage party sites
    • HZ_ORG_PROFILE_V2PUB – Manage organization profiles
    • HZ_CUSTOMER_PROFILE_V2PUB – Manage customer profiles
  • APIs follow PL/SQL procedure standards. Documentation is available on My Oracle Support.

Mastering Party API (Oracle R12)

Creating and Updating Parties

  • Firstly, a large retailer acquires a new wholesale supplier. you use CREATE_ORGANIZATION to add the supplier as a new party. Later, you discover the supplier’s legal name changed, and use UPDATE_ORGANIZATION to reflect this change.
  • Use HZ_PARTY_V2PUB.CREATE_ORGANIZATION and HZ_PARTY_V2PUB.UPDATE_ORGANIZATION.

Create Parties (HZ_PARTY_V2PUB.CREATE_ORGANIZATION)

In the below example of hz_party_v2pub.create_person API to create a record in hz_parties table. This api will also create one record in hz_organization_profiles table

HZ_PARTY_V2PUB.CREATE_ORGANIZATION
DECLARE
   l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
   l_party_id         HZ_PARTIES.PARTY_ID%TYPE;
   l_party_number     HZ_PARTIES.PARTY_NUMBER%TYPE;
   l_profile_id       HZ_ORGANIZATION_PROFILES.ORGANIZATION_PROFILE_ID%TYPE;
   l_return_status    VARCHAR2(100);
   l_msg_count        NUMBER;
   l_msg_data         VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   l_organization_rec.organization_name := 'XYZ Corporation';
   l_organization_rec.created_by_module := 'HZ_CPUI';
   DBMS_OUTPUT.PUT_LINE('Calling hz_party_v2pub.create_organization API');
   --Call hz_party_v2pub.create_organization
   hz_party_v2pub.create_organization
             (
              p_init_msg_list    => FND_API.G_TRUE,
              p_organization_rec => l_organization_rec,
              x_return_status    => l_return_status,
              x_msg_count        => l_msg_count,
              x_msg_data         => l_msg_data,
              x_party_id         => l_party_id,
              x_party_number     => l_party_number,
              x_profile_id       => l_profile_id
             );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Organization Creation is Successful ');
      DBMS_OUTPUT.PUT_LINE('Party Id         ='|| l_party_id);
      DBMS_OUTPUT.PUT_LINE('Party Number     ='|| l_party_number);
      DBMS_OUTPUT.PUT_LINE('Profile Id       ='|| l_profile_id);  
   ELSE
      DBMS_OUTPUT.put_line ('Creation of Organization failed:'||l_msg_data);
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
   END IF;
   DBMS_OUTPUT.PUT_LINE('Organization Creation Complete');
END;
HZ_PARTY_V2PUB.CREATE_ORGANIZATION

Calling hz_party_v2pub.create_organization API
Organization Creation is Successful
Party Id =18041584
Party Number =8021947
Profile Id =34804658
Organization Creation Complete

HZ API hz_party_v2pub.create_person will create a record in the HZ_PARTIES table and another record in HZ_PERSON_PROFILES table.

Update Parties(HZ_PARTY_V2PUB.UPDATE_ORGANIZATION)

In this scenario, you asked to update the Tax Registration Number of few customers in customer profile. Hence, you can store the customer number, customer name and tax registration number in a temporary table. The, you fetch the information through a PL/SQL Loop. Finally you need to call HZ_PARTY_V2PUB.update_organization api to update the tax registration number.

HZ_PARTY_V2PUB.update_organization
DECLARE
   -- Cursor to fetch records from Temporary table for customer master data
   CURSOR c_party
   IS
   SELECT DISTINCT tmp.account_number,   -- Customer Account Number
	        hp.party_id,
          hp.party_name,
          hp.object_version_number party_version_number,
          tmp.tax_registration_no
     FROM xxar_cust_tax_ref_tmp  tmp,--Temporary table
          hz_parties hp,
		      hz_cust_accounts hca
    WHERE 1=1
      AND process_status = 'N' -- Only process new records
	    AND hca.account_number=tmp.account_number
	    AND hca.party_id=hp.party_id
    ;           
   --
   l_total              NUMBER   := 0;
   l_success            NUMBER   := 0;
   l_error              NUMBER   := 0;
    -- Variables for API parameters
   l_organization_rec   hz_party_v2pub.organization_rec_type;  
   l_cust_account_rec   hz_cust_account_v2pub.cust_account_rec_type;
   l_cust_site_rec      hz_cust_account_site_v2pub.cust_acct_site_rec_type;
   l_party_name         hz_parties.party_name%TYPE;
   l_object_ver_number  NUMBER := 0;
   -- Output variables for API response
   l_return_status      VARCHAR2(10);
   l_msg_count          NUMBER;
   l_msg_data           VARCHAR2(2000);
   l_err_msg            VARCHAR2(2000);
   l_profile_id         NUMBER;
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   FOR party_rec IN c_party 
   LOOP
      BEGIN
	       l_total  := l_total + 1;
         -- Assign values for customer account updates
		     l_party_name        := party_rec.party_name;
         l_object_ver_number := party_rec.party_version_number;
		     l_organization_rec.organization_name  := party_rec.party_name;
         l_organization_rec.party_rec.party_id := party_rec.party_id;
		     l_organization_rec.tax_reference  :=party_rec.tax_registration_no;
         -- Call API to update party
		     HZ_PARTY_V2PUB.update_organization (
                     p_init_msg_list              => FND_API.G_TRUE,
                     p_organization_rec           => l_organization_rec,
                     p_party_object_version_number=> l_object_ver_number,
                     x_profile_id                 => l_profile_id,
                     x_return_status              => l_return_status,
                     x_msg_count                  => l_msg_count,
                     x_msg_data                   => l_msg_data
                    );   
         -- Log result for customer site update
         IF l_return_status = 'S' 
		     THEN
            -- Mark the record as processed
            UPDATE xxar_cust_tax_ref_tmp
               SET process_status = 'S',
                   error_message  = NULL
             WHERE account_number = party_rec.account_number
               ;
            l_success := l_success + 1;
         ELSE
		        l_error   := l_error + 1;
            DBMS_OUTPUT.PUT_LINE('Error updating account_number ' || 
            party_rec.account_number || ': ' || l_msg_data);
            -- Mark the record with error
            UPDATE xxar_cust_tax_ref_tmp
               SET process_status = 'E',
                   error_message  = l_msg_data
            WHERE account_number = party_rec.account_number
              ;
         END IF;
      EXCEPTION
         WHEN OTHERS THEN
		        l_err_msg := SQLERRM;
            DBMS_OUTPUT.PUT_LINE('Unexpected error for Customer Account' || party_rec.account_number || ': '||l_err_msg);
			      DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
            -- Mark the record with exception
            UPDATE xxar_cust_tax_ref_tmp
               SET process_status = 'E',
                   error_message  = l_err_msg
             WHERE account_number = party_rec.account_number     
		    ;
      END;
      IF MOD(l_total, 50) = 0
	  THEN 
	     COMMIT;
	  END IF;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Total Selected parties: '||l_total);
   DBMS_OUTPUT.PUT_LINE('Total parties successfully Updated: '||l_success);
   DBMS_OUTPUT.PUT_LINE('Total Error parties: '||l_error);
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
	  l_err_msg := SQLERRM;
      DBMS_OUTPUT.PUT_LINE('Unexpected error for party name ' || l_party_name || ': '||l_err_msg);
	  DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
END;
HZ_PARTY_V2PUB.update_organization

Total Selected Parties : 50

Total Parties successfully Updated: 50

Total Error  Parties: 0

Creating Customer Profiles

A marketing department requires to store specific information about retail customers, like buying habits or preferred product categories. This API allows for the creation of custom profiles for each customer.

  • Use HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile to create the customer profile.

Creating Organization Profiles

  • Use HZ_PARTIES_V2PUB.CREATE_ORGANIZATION.
  • Use Case: To track information about the company’s suppliers, you need to store information like the company’s business classification, supplier tier, and payment terms. This API allows for the storage of this information.

Updating Organization Profiles

  • Use HZ_PARTY_V2PUB.update_organization.
  • Use Case: To track information about the company’s suppliers, you need to store information like the company’s business classification, supplier tier, and payment terms. This API allows for the update of this information.

Manage Customer Accounts

Customer Accounts and Sites

When a customer signs a new contract, you create a customer account using HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT, assigning them a unique account number and business classification. If their credit limit changes, HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT is used.

Create Customer Account (HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT)

API HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT creates a new party as well as customer account. If you want to create only customer account and assign the account to an existing party record, you need to populate l_organization_rec.party_rec.party_id.

HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT
DECLARE
   l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
   l_cust_acct_rec    HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
   l_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
   l_cust_account_id  HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;
   l_account_number   HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
   l_party_id         HZ_PARTIES.PARTY_ID%TYPE;
   l_party_number     HZ_PARTIES.PARTY_NUMBER%TYPE;
   l_profile_id       HZ_ORGANIZATION_PROFILES.ORGANIZATION_PROFILE_ID%TYPE;
   l_return_status    VARCHAR2(100);
   l_msg_count        NUMBER;
   l_msg_data         VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   --Populating organization record
   l_organization_rec.organization_name := 'XYZ Corporation';
   --For existing party use party_id
   l_organization_rec.party_rec.party_id:= 18041584;
   l_organization_rec.created_by_module := 'HZ_CPUI';
   l_cust_acct_rec.account_name         := 'XYZ Account';
   l_cust_acct_rec.created_by_module    := 'BO_API';
   --Calling hz_cust_account_v2pub.create_cust_account
   DBMS_OUTPUT.PUT_LINE('Calling hz_cust_account_v2pub.create_cust_account api');
   HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT
             (
              p_init_msg_list       => FND_API.G_TRUE,
              p_cust_account_rec    =>l_cust_acct_rec,
              p_organization_rec    =>l_organization_rec,
              p_customer_profile_rec=>l_cust_profile_rec,
              p_create_profile_amt  =>FND_API.G_FALSE,
              x_cust_account_id     =>l_cust_account_id,
              x_account_number      =>l_account_number,
              x_party_id            =>l_party_id,
              x_party_number        =>l_party_number,
              x_profile_id          =>l_profile_id,
              x_return_status       =>l_return_status,
              x_msg_count           =>l_msg_count,
              x_msg_data            =>l_msg_data
             );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Party and Customer Account Creation is Successful ');
      DBMS_OUTPUT.PUT_LINE('Party Id         ='|| l_party_id);
      DBMS_OUTPUT.PUT_LINE('Party Number     ='|| l_party_number);
      DBMS_OUTPUT.PUT_LINE('Profile Id       ='|| l_profile_id);  
      DBMS_OUTPUT.PUT_LINE('CUST_ACCOUNT_ID  ='|| l_cust_account_id);
      DBMS_OUTPUT.PUT_LINE('Account Number   ='|| l_account_number);  
   ELSE
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Party and Customer Account Creation failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Party and Customer Account Creation Complete');
END;
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT

Calling hz_cust_account_v2pub.create_cust_account api
Party and Customer Account Creation is Successful
Party Id =18041584
Party Number =8021947
Profile Id =
CUST_ACCOUNT_ID =3087426
Account Number =10259581
Party and Customer Account Creation Complete

Update Customer Account in TCA API Oracle R12

After sometime your customer realize that account name should be updated to “XYZ US Account”. You can use hz_cust_account_v2pub.update_cust_account to update the customer account.

HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT
DECLARE
   l_cust_acct_rec    HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
   l_cust_account_id  HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE := 3087426;
   l_account_number   HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
   l_obj_version_num  NUMBER := 1;
   l_return_status    VARCHAR2(100);
   l_msg_count        NUMBER;
   l_msg_data         VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   l_cust_acct_rec.account_name         := 'XYZ US Account';
   l_cust_acct_rec.cust_account_id      := l_cust_account_id;
   --Calling hz_cust_account_v2pub.create_cust_account
   DBMS_OUTPUT.PUT_LINE('Calling hz_cust_account_v2pub.update_cust_account api');
   HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT
             (
             p_init_msg_list         => FND_API.G_TRUE,
             p_cust_account_rec      => l_cust_acct_rec,
             p_object_version_number => l_obj_version_num,
             x_return_status         => l_return_status,
             x_msg_count             => l_msg_count,
             x_msg_data              => l_msg_data
             );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Customer Account Update is Successful '); 
   ELSE
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Customer Account Update failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Customer Account Update Complete');
END;
HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT

Manage Locations

Oracle TCA R12 API to Create Location (HZ_LOCATION_V2PUB.CREATE_LOCATION)

  • Use Oracle R12 API HZ_LOCATION_V2PUB.CREATE_LOCATION
  • A company expands its operations to a new city. Use this API to add the new office location to the system, ensuring it’s available for address assignments.
TCA Oracle R12 API HZ_LOCATION_V2PUB.CREATE_LOCATION
DECLARE
   l_location_rec     HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
   l_location_id      NUMBER;
   l_return_status    VARCHAR2(100);
   l_msg_count        NUMBER;
   l_msg_data         VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   --Populating Location Record
   l_location_rec.address1          := '123 subway';
   l_location_rec.address2          := 'Enodeas Building';
   l_location_rec.city              := 'New York';
   l_location_rec.postal_code       := '10010';
   l_location_rec.state             := 'NY';
   l_location_rec.country           := 'US';
   l_location_rec.created_by_module := 'BO_API';
   --Calling hz_location_v2pub.create_location
   DBMS_OUTPUT.PUT_LINE('Calling hz_location_v2pub.create_location api');
   HZ_LOCATION_V2PUB.CREATE_LOCATION
             (
			  p_init_msg_list => FND_API.G_TRUE,
              p_location_rec  => l_location_rec,
              x_location_id   => l_location_id,
			  x_return_status => l_return_status,
              x_msg_count     => l_msg_count,
              x_msg_data      => l_msg_data
             );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Location Creation is Successful '); 
	  DBMS_OUTPUT.PUT_LINE('Location Id = '||l_location_id); 
   ELSE
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Location Creation failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Location Creation Complete');
END;
TCA Oracle R12 API HZ_LOCATION_V2PUB.CREATE_LOCATION

Calling hz_location_v2pub.create_location api
Location Creation is Successful
Location Id = 5931889
Location Creation Complete

Oracle TCA R12 API to Update Location (HZ_LOCATION_V2PUB.UPDATE_LOCATION)

With the help of the TCA API HZ_LOCATION_V2PUB.UPDATE_LOCATION, you can update the existing customer address stored in the table “HZ_LOCATIONS”

HZ_LOCATION_V2PUB.UPDATE_LOCATION
DECLARE
   l_location_rec     HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
   l_location_id      NUMBER;
   l_obj_version_num  NUMBER := 1;
   l_return_status    VARCHAR2(100);
   l_msg_count        NUMBER;
   l_msg_data         VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   --Location Record
   l_location_rec.location_id       := 5931889;
   l_location_rec.address2          := 'Enodeas Main Building';
   --Calling hz_location_v2pub.update_location
   DBMS_OUTPUT.PUT_LINE('Calling hz_location_v2pub.update_location api');
   HZ_LOCATION_V2PUB.UPDATE_LOCATION
             (
			        p_init_msg_list        => FND_API.G_TRUE,
              p_location_rec         => l_location_rec,
              p_object_version_number=> l_obj_version_num,
			        x_return_status        => l_return_status,
              x_msg_count            => l_msg_count,
              x_msg_data             => l_msg_data
             );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Location Update is Successful '); 
   ELSE
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Location Update failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Location Update Complete');
END;
HZ_LOCATION_V2PUB.UPDATE_LOCATION

Manage Party Sites

Create a Party Site (HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE) using TCA API in R12 Oracle Apps

This TCA API in Oracle Ebs is used to create a Party Site for a party. Party Site api associates an existing party with a location. This TCA API creates a record in the HZ_PARTY_SITES table. You can also create multiple party sites with multiple locations and mark one of those party sites as identifying for that party.

TCA API HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE in Oracle Apps R12
DECLARE
   l_party_site_rec    HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
   l_party_site_id     HZ_PARTY_SITES.PARTY_SITE_ID%TYPE;
   l_party_site_number HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE;
   l_return_status     VARCHAR2(100);
   l_msg_count         NUMBER;
   l_msg_data          VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   --Populating Party Site Record
   l_party_site_rec.party_id                 := 18041584;
   l_party_site_rec.location_id              := 5931889;
   l_party_site_rec.identifying_address_flag := 'Y';
   l_party_site_rec.created_by_module        := 'BO_API';
   --Calling hz_party_site_v2pub.create_party_site
   DBMS_OUTPUT.PUT_LINE('Calling hz_party_site_v2pub.create_party_site api');
   HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE
            (
             p_init_msg_list     => FND_API.G_TRUE,
             p_party_site_rec    => l_party_site_rec,
             x_party_site_id     => l_party_site_id,
             x_party_site_number => l_party_site_number,
             x_return_status     => l_return_status,
             x_msg_count         => l_msg_count,
             x_msg_data          => l_msg_data
            );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Party Site Creation is Successful '); 
	    DBMS_OUTPUT.PUT_LINE('Party Site Number = '||l_party_site_number); 
	    DBMS_OUTPUT.PUT_LINE('Party Site Id = '||l_party_site_id); 
   ELSE
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Party Site Creation failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Party Site Creation Complete');
END;
TCA API HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE in Oracle Apps R12

Calling hz_party_site_v2pub.create_party_site api
Party Site Creation is Successful
Party Site Number = 10619235
Party Site Id = 14421651
Party Site Creation Complete

Update Party Site TCA API Oracle R12(hz_party_site_v2pub.update_party_site)

HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE
DECLARE
   l_party_site_rec    HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
   l_obj_ver_number    NUMBER := 1;
   l_return_status     VARCHAR2(100);
   l_msg_count         NUMBER;
   l_msg_data          VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   --Populating Party Site Record
   l_party_site_rec.party_site_id            := 14421651;
   l_party_site_rec.addressee                := '123 Example Avenue';
   l_party_site_rec.created_by_module        := 'BO_API';
   --Calling hz_party_site_v2pub.update_party_site
   DBMS_OUTPUT.PUT_LINE('Calling hz_party_site_v2pub.update_party_site api');
   HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE
            (
             p_init_msg_list         => FND_API.G_TRUE,
             p_party_site_rec        => l_party_site_rec,
			       p_object_version_number => l_obj_ver_number,
             x_return_status         => l_return_status,
             x_msg_count             => l_msg_count,
             x_msg_data              => l_msg_data
            );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Party Site Update is Successful '); 
   ELSE
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Party Site Update failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Party Site Update Complete');
END;
HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE

Addressee of hz_party_sites of the above party_site_id will be updated by the TCA API in Oracle R12.

Manage Account Sites

Create Customer Account Site TCA API in Oracle R12 (HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE)

This TCA API in Oracle R12 is used to create a customer account site for the customer account using an existing Party Site.

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE
DECLARE
   l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
   l_cust_acct_site_id  HZ_CUST_ACCT_SITES.CUST_ACCT_SITE_ID%TYPE;
   l_return_status      VARCHAR2(100);
   l_msg_count          NUMBER;
   l_msg_data           VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   --Populating Cust Acct Site Record
   l_cust_acct_site_rec.cust_account_id      := 3087426;
   l_cust_acct_site_rec.party_site_id        := 14421651;
   l_cust_acct_site_rec.created_by_module    := 'BO_API';
   --Calling hz_cust_account_site_v2pub.create_cust_acct_site
   DBMS_OUTPUT.PUT_LINE('Calling hz_cust_account_site_v2pub.create_cust_acct_site api');
   HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE
            (
			       p_init_msg_list      => FND_API.G_TRUE,
             p_cust_acct_site_rec => l_cust_acct_site_rec,
             x_cust_acct_site_id  => l_cust_acct_site_id,
             x_return_status      => l_return_status,
             x_msg_count          => l_msg_count,
             x_msg_data           => l_msg_data
            );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Cust Acct Site Creation is Successful '); 
	    DBMS_OUTPUT.PUT_LINE('Cust Acct Site Id = '||l_cust_acct_site_id); 
   ELSE
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Cust Acct Site Creation failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Cust Acct Site Creation Complete');
END;
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE

Calling hz_cust_account_site_v2pub.create_cust_acct_site api
Cust Acct Site Creation is Successful
Cust Acct Site Id = 8267923
Cust Acct Site Creation Complete

Update Customer Acct Site TCA API Oracle R12(HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_acct_site)

This TCA API in Oracle R12 is used to Update a Customer Account site for a customer account and a Party Site.

HZ_CUST_ACCOUNT_SITE_V2PUB.UPDATE_CUST_ACCT_SITE
DECLARE
   l_cust_acct_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE;
   l_obj_ver_number     NUMBER := 1;
   l_return_status      VARCHAR2(100);
   l_msg_count          NUMBER;
   l_msg_data           VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR')
   fnd_client_info.set_org_context(81)
   --Populating Cust Acct Site Record
   l_cust_acct_site_rec.cust_acct_site_id       := 3087426;
   l_cust_acct_site_rec.customer_category_code  := 'PHARMACY';
   --Calling hz_cust_account_site_v2pub.update_cust_acct_site
   DBMS_OUTPUT.PUT_LINE('Calling hz_cust_account_site_v2pub.update_cust_acct_site api');
   HZ_CUST_ACCOUNT_SITE_V2PUB.UPDATE_CUST_ACCT_SITE
            (
             p_init_msg_list        => FND_API.G_TRUE,
             p_cust_acct_site_rec   => l_cust_acct_site_rec,
             p_object_version_number=> l_obj_ver_number,
             x_return_status        => l_return_status,
             x_msg_count            => l_msg_count,
             x_msg_data             => l_msg_data
            );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Cust Acct Site Update is Successful '); 
   ELSE
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Cust Acct Site Update failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Cust Acct Site Update Complete');
END;
HZ_CUST_ACCOUNT_SITE_V2PUB.UPDATE_CUST_ACCT_SITE

Create a Customer Site Use TCA R12 API (HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE)

When setting up a new site for a customer account, it is required to define it as either a “Bill To” or “Ship To” location.You need to use HZ_CUST_SITE_USE_V2PUB.CREATE_CUST_SITE_USE.

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE
DECLARE
   l_cust_site_use_rec    HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
   l_customer_profile_rec 
   HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
   l_site_use_id          HZ_CUST_SITE_USES.SITE_USE_ID%TYPE;
   l_return_status        VARCHAR2(100);
   l_msg_count            NUMBER;
   l_msg_data             VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   --Populating Cust Acct Site Record  
   l_cust_site_use_rec.cust_acct_site_id    := 8267923;
   l_cust_site_use_rec.site_use_code        := 'SHIP_TO';
   l_cust_site_use_rec.location             := 'NEWYORK';
   l_cust_site_use_rec.created_by_module    := 'BO_API';
   --Calling hz_cust_account_site_v2pub.create_cust_site_use
   DBMS_OUTPUT.PUT_LINE('Calling 
    hz_cust_account_site_v2pub.create_cust_site_use api');
   HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE
                   (
                     p_init_msg_list         => FND_API.G_TRUE,
                     p_cust_site_use_rec     => l_cust_site_use_rec,
                     p_customer_profile_rec  => l_customer_profile_rec,
                     p_create_profile        => FND_API.G_TRUE,
                     p_create_profile_amt    => FND_API.G_TRUE,
                     x_site_use_id           => l_site_use_id,
                     x_return_status         => l_return_status,
                     x_msg_count             => l_msg_count,
                     x_msg_data              => l_msg_data
                    );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Cust Site Use Creation is Successful '); 
	    DBMS_OUTPUT.PUT_LINE('site_use_id = '||l_site_use_id); 
   ELSE
      ROLLBACK;
	    DBMS_OUTPUT.put_line ('Cust Site Use Creation failed:'||l_msg_data);
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Cust Site Use Creation failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Cust Site Use Creation Complete');
END;
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE

Update a Customer Site Use TCA R12 API (HZ_CUST_ACCOUNT_SITE_V2PUB.UPDATE_CUST_SITE_USE)

In the following example you can understand how a customer site use ship method can be updated using TCA API. This way you can update the Customer Site Use using API in Oracle Apps R12.

HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_site_use
DECLARE
   l_cust_site_use_rec  HZ_CUST_ACCOUNT_SITE_V2PUB.cust_site_use_rec_type;
   l_ship_method        hz_cust_site_uses_all.ship_via%TYPE := '000001_DHL_A_INTLBOND';
   l_obj_ver_number     NUMBER := 1;
   l_return_status      VARCHAR2(100);
   l_msg_count          NUMBER;
   l_msg_data           VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   --Populating Cust Acct Site Record
   l_cust_site_use_rec.site_use_id        := 10584023;
   l_cust_site_use_rec.cust_acct_site_id  := 8267923;
   l_cust_site_use_rec.site_use_code      := 'SHIP_TO';
   l_cust_site_use_rec.ship_via           := l_ship_method;
   --Calling hz_cust_account_site_v2pub.update_cust_site_use
   DBMS_OUTPUT.PUT_LINE('Calling hz_cust_account_site_v2pub.update_cust_site_use api');
   -- Call API to update customer account
   HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_site_use(
       p_init_msg_list        => FND_API.G_TRUE,
       p_cust_site_use_rec    => l_cust_site_use_rec,
       p_object_version_number=> l_obj_ver_number,
       x_return_status        => l_return_status,
       x_msg_count            => l_msg_count,
       x_msg_data             => l_msg_data
   );
   IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Cust Site Use Update is Successful '); 
   ELSE
      ROLLBACK;
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Cust Site Use Update failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Cust Site Use Update Complete');
END;
HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_site_use

Calling hz_cust_account_site_v2pub.update_cust_site_use api
Cust Site Use Update is Successful
Cust Site Use Update Complete

Manage Contacts

Managing Contact Person for a Customer Organization

The API inserts a new record into the HZ_ORG_CONTACTS table to register the organizational contact. Simultaneously, it creates a corresponding relationship in the HZ_RELATIONSHIPS table, where the contact person acts as the subject and the associated organization or individual serves as the object. The relationship type and code are derived from the values passed by the calling program.

  • API: HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT.

Managing Contact Points (Phone, Email, Fax)

  • Use HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT
  • Store the customer service phone number and support email address for a client organization’s delivery site, making it easy for internal teams to find contact information.

Create Person using HZ_PARTY_V2PUB.CREATE_PERSON

Before creating the party contact person needs to be created and assign as subject_id in the party_rel_rec record type in HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT api.

HZ_PARTY_V2PUB.CREATE_PERSON
DECLARE
   l_person_rec    HZ_PARTY_V2PUB.PERSON_REC_TYPE;
   l_party_id      HZ_PARTIES.PARTY_ID%TYPE;
   l_party_number  HZ_PARTIES.PARTY_NUMBER%TYPE;
   l_profile_id    NUMBER;
   l_return_status VARCHAR2(100);
   l_msg_count     NUMBER;
   l_msg_data      VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   -- Initializing the Mandatory API parameters
   l_person_rec.person_pre_name_adjunct := 'MR.';
   l_person_rec.person_first_name       := 'Sachin';
   l_person_rec.person_last_name        := 'Jadav';
   l_person_rec.created_by_module       := 'BO_API';
   --Calling hz_party_v2pub.create_person
   DBMS_OUTPUT.PUT_LINE('Calling hz_party_v2pub.create_person api');
   HZ_PARTY_V2PUB.CREATE_PERSON (
        p_init_msg_list   => FND_API.G_TRUE,  
        p_person_rec      => l_person_rec,  
        x_party_id        => l_party_id,  
        x_party_number    => l_party_number,  
        x_profile_id      => l_profile_id,  
        x_return_status   => l_return_status,  
        x_msg_count       => l_msg_count,  
        x_msg_data        => l_msg_data  
    );
  IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Person Creation is Successful '); 
	  DBMS_OUTPUT.PUT_LINE('party_id='||l_party_id);
      DBMS_OUTPUT.PUT_LINE('party_number='||l_party_number);
      DBMS_OUTPUT.PUT_LINE('profile_id: '||l_profile_id); 
   ELSE
      ROLLBACK;
	  DBMS_OUTPUT.put_line ('Person Creation failed:'||l_msg_data);
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Person Creation failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Person Creation Complete');
END;
HZ_PARTY_V2PUB.CREATE_PERSON

Calling hz_party_v2pub.create_person api
Person Creation is Successful
party_id=18041600
party_number=8021963
profile_id: 26487177
Person Creation Complete

Create Org Contact HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT

HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT
DECLARE
   l_org_contact_rec      HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
   l_org_contact_id       HZ_ORG_CONTACTS.ORG_CONTACT_ID%TYPE;
   l_party_rel_id         HZ_ORG_CONTACTS.PARTY_RELATIONSHIP_ID%TYPE;
   l_party_id             HZ_PARTIES.PARTY_ID%TYPE;
   l_party_number         HZ_PARTIES.PARTY_NUMBER%TYPE;
   l_return_status        VARCHAR2(100);
   l_msg_count            NUMBER;
   l_msg_data             VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   -- Initializing the Mandatory API parameters
   l_org_contact_rec.job_title                        := 'Consultant';
   l_org_contact_rec.decision_maker_flag              := 'N';
   --l_org_contact_rec.job_title_code                   := 'CFO';
   l_org_contact_rec.created_by_module                := 'BO_API';
   l_org_contact_rec.party_rel_rec.subject_id         :=  18041600;
   l_org_contact_rec.party_rel_rec.subject_type       := 'PERSON';
   l_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
   l_org_contact_rec.party_rel_rec.object_id          :=  18041584;
   l_org_contact_rec.party_rel_rec.object_type        := 'ORGANIZATION';
   l_org_contact_rec.party_rel_rec.object_table_name  := 'HZ_PARTIES';
   l_org_contact_rec.party_rel_rec.relationship_code  := 'CONTACT_OF';
   l_org_contact_rec.party_rel_rec.relationship_type  := 'CONTACT';
   l_org_contact_rec.party_rel_rec.start_date         :=  SYSDATE;
   --Calling hz_cust_account_site_v2pub.create_cust_site_use
   DBMS_OUTPUT.PUT_LINE('Calling hz_party_contact_v2pub.create_org_contact api');
   HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT
             (
              p_init_msg_list    => FND_API.G_TRUE,
              p_org_contact_rec  => l_org_contact_rec,
              x_org_contact_id   => l_org_contact_id,
              x_party_rel_id     => l_party_rel_id,
              x_party_id         => l_party_id,
              x_party_number     => l_party_number,
              x_return_status    => l_return_status,
              x_msg_count        => l_msg_count,
              x_msg_data         => l_msg_data
                   );
IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Org Contact Creation is Successful '); 
	  DBMS_OUTPUT.PUT_LINE('org_contact_id = '||l_org_contact_id); 
	  DBMS_OUTPUT.PUT_LINE('party_rel_id = '||l_party_rel_id); 
	  DBMS_OUTPUT.PUT_LINE('party_id = '||l_party_id); 
   ELSE
      ROLLBACK;
	  DBMS_OUTPUT.put_line ('Org Contact Creation failed:'||l_msg_data);
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Org Contact Creation failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Org Contact Creation Complete');
END;
HZ_PARTY_CONTACT_V2PUB.CREATE_ORG_CONTACT

Calling hz_party_contact_v2pub.create_org_contact api
Org Contact Creation is Successful
org_contact_id = 17311343
party_rel_id = 18765815
party_id = 18041601
Org Contact Creation Complete

Create Phone Contact using HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT

HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT
DECLARE
   l_contact_point_rec    HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
   l_edi_rec              HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
   l_email_rec            HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
   l_phone_rec            HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
   l_telex_rec            HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
   l_web_rec              HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
   l_contact_point_id     HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
   l_return_status        VARCHAR2(100);
   l_msg_count            NUMBER;
   l_msg_data             VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   -- Initializing the Mandatory API parameters
   --Contact Record
   l_contact_point_rec.contact_point_type    := 'PHONE';
   l_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
   l_contact_point_rec.owner_table_id        := 18041584;
   l_contact_point_rec.primary_flag          := 'Y';
   l_contact_point_rec.contact_point_purpose := 'BUSINESS';
   l_contact_point_rec.created_by_module     := 'BO_API';
   --Phone Record
   l_phone_rec.phone_country_code            := '1';
   l_phone_rec.phone_number                  := '856-784-521';
   l_phone_rec.phone_line_type               := 'MOBILE';
   --Calling hz_contact_point_v2pub.create_contact_point
   DBMS_OUTPUT.PUT_LINE('Calling hz_contact_point_v2pub.create_contact_point api');
   HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT
             (
               p_init_msg_list      => FND_API.G_TRUE,
               p_contact_point_rec  => l_contact_point_rec,
               p_edi_rec            => l_edi_rec,
               p_email_rec          => l_email_rec,
               p_phone_rec          => l_phone_rec,
               p_telex_rec          => l_telex_rec,
               p_web_rec            => l_web_rec,
               x_contact_point_id   => l_contact_point_id,
               x_return_status      => l_return_status,
               x_msg_count          => l_msg_count,
               x_msg_data           => l_msg_data
              );
IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Contact Point Creation is Successful '); 
	    DBMS_OUTPUT.PUT_LINE('contact_point_id = '||l_contact_point_id); 
   ELSE
      ROLLBACK;
	    DBMS_OUTPUT.put_line ('Contact Point Creation failed:'||l_msg_data);
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Contact Point Creation failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Contact Point Creation Complete');
END;
HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT

Calling hz_contact_point_v2pub.create_contact_point api
Contact Point Creation is Successful
contact_point_id = 19266280
Contact Point Creation Complete

Create Email Contact Point using HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT

SQL
DECLARE
   l_contact_point_rec    HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
   l_edi_rec              HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
   l_email_rec            HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
   l_phone_rec            HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
   l_telex_rec            HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
   l_web_rec              HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
   l_contact_point_id     HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
   l_return_status        VARCHAR2(100);
   l_msg_count            NUMBER;
   l_msg_data             VARCHAR2(2000);
BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   --Initiate the EBS Environment for API processing
   fnd_global.apps_initialize(1516, 20678, 222);
   mo_global.init('AR');
   fnd_client_info.set_org_context(81);
   -- Initializing the Mandatory API parameters
   --Contact Record
   l_contact_point_rec.contact_point_type    := 'EMAIL';
   l_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
   l_contact_point_rec.owner_table_id        := 18041584;
   l_contact_point_rec.primary_flag          := 'Y';
   l_contact_point_rec.contact_point_purpose := 'BUSINESS';
   l_contact_point_rec.created_by_module     := 'BO_API';
   --Phone Record
   l_email_rec.email_format                  := 'MAILHTML';
   l_email_rec.email_address                 := 'contact@xyzcorp.com';
   
   --Calling hz_contact_point_v2pub.create_contact_point
   DBMS_OUTPUT.PUT_LINE('Calling hz_contact_point_v2pub.create_contact_point api');
   HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT
             (
               p_init_msg_list      => FND_API.G_TRUE,
               p_contact_point_rec  => l_contact_point_rec,
               p_edi_rec            => l_edi_rec,
               p_email_rec          => l_email_rec,
               p_phone_rec          => l_phone_rec,
               p_telex_rec          => l_telex_rec,
               p_web_rec            => l_web_rec,
               x_contact_point_id   => l_contact_point_id,
               x_return_status      => l_return_status,
               x_msg_count          => l_msg_count,
               x_msg_data           => l_msg_data
              );
IF l_return_status = 'S' 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Contact Point Creation is Successful '); 
	  DBMS_OUTPUT.PUT_LINE('contact_point_id = '||l_contact_point_id); 
   ELSE
      ROLLBACK;
	  DBMS_OUTPUT.put_line ('Contact Point Creation failed:'||l_msg_data);
      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
         dbms_output.put_line( i|| ') '|| l_msg_data);
      END LOOP;
      DBMS_OUTPUT.put_line ('Contact Point Creation failed:'||l_msg_data);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Contact Point Creation Complete');
END;
SQL

Advanced API Mastery

Handling Complex Data

Use PL/SQL collections and structures for nested inputs. Wrap APIs inside reusable PL/SQL packages.

Performance Optimization

  • Bulk data processing with FORALL.
  • Log messages using FND_MSG_PUB.GET.

Integrating TCA APIs with Other Modules

Order Management (OM)

Validate and create customers prior to order creation

Receivables (AR)

Ensure customer accounts and profiles are created to enable transactions.

Web Services

Expose these APIs via SOA or custom REST services.

Essential Learning Resources:

  • Oracle Documentation: TCA API Reference
  • My Oracle Support: API patches, tips, and performance advice
  • Online Communities: Oracle Forums, Stack Overflow
  • Practical Examples: GitHub repositories with working code

Your Journey to TCA api Oracle R12 Mastery Starts Now!

This roadmap gives you the structure and tools to become proficient in Oracle TCA API. Whether you’re automating bulk customer creation or integrating with external systems, these APIs are your gateway to data efficiency. Happy coding, and may your TCA data always be clean!

This Post Has One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.