Showing posts with label TCA Apis. Show all posts
Showing posts with label TCA Apis. Show all posts

Friday, July 11, 2014

TCA API: Sample Code to Create AR Customer & Site from AP Supplier & Supplier Site using same Party


Doc ID 1447883.1

We will use following sql to find out Party details from Supplier tables

SELECT DISTINCT a.party_number, ps.party_site_number, a.party_id,
'CUST-' ||a.party_id||'-'||ass.org_id AS customer_ref,
'ADD-'||ps.party_site_id||'-'||ass.org_id AS address_ref,
ps.party_site_id,
ass.address_line1,
ass.city, ass.state, ass.zip, ass.country,
a.orig_system_reference AS party_ref,
a.party_name AS customer_name, 
ass.org_id
FROM ap_suppliers ap,ap_supplier_sites_all ass,
hz_parties a,hz_party_sites ps
WHERE ap.vendor_id = ass.vendor_id
AND a.party_id = ap.party_id
AND ps.party_id = ap.party_id
AND ass.party_site_id = ps.party_site_id
AND a.party_name = ap.vendor_name
and a.party_id = 12465 -- Please pass your Party_id or Comment this line
and NOT Exists (select 1 from hz_cust_accounts a
where a.party_id = a.party_id);


DECLARE
 l_num_user_id       NUMBER;
 l_num_appl_id       NUMBER;
 l_num_resp_id       NUMBER;
 cust_account_rec_type   hz_cust_account_v2pub.cust_account_rec_type;
 l_num_obj_ver_num     NUMBER;
 l_chr_return_status    VARCHAR2 (2000);
 l_num_msg_count      NUMBER;
 l_chr_msg_data       VARCHAR2 (500);
 l_num_profile_id      NUMBER;
 l_organization_rec     hz_party_v2pub.organization_rec_type;
 l_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;
 l_num_cust_id       NUMBER;
 l_chr_acct_num       VARCHAR2 (500);
 l_num_party_id       NUMBER;
 l_chr_party_number     VARCHAR2 (500);
 l_cust_acct_site_use_rec  hz_cust_account_site_v2pub.cust_site_use_rec_type;
 l_cust_acct_site_rec    hz_cust_account_site_v2pub.cust_acct_site_rec_type;
 l_chr_sit_return_status  VARCHAR2 (500);
 l_num_sit_msg_count    NUMBER;
 l_chr_sit_msg_data     VARCHAR2 (500);
 l_chr_situ_return_status  VARCHAR2 (500);
 l_num_situ_msg_count    NUMBER;
 l_chr_situ_msg_data    VARCHAR2 (500);
 l_num_site_use_id     NUMBER;
 CURSOR update_base_tables_cur
 IS
   SELECT DISTINCT a.party_number, ps.party_site_number, a.party_id,
           'CUST-' ||a.party_id||'-'||ass.org_id AS customer_ref,
           'ADD-'||ps.party_site_id||'-'||ass.org_id AS address_ref,
           ps.party_site_id,
           ass.address_line1,
           ass.city, ass.state, ass.zip, ass.country,
           'N' primary_site_use_flag,
           a.orig_system_reference AS party_ref,
           a.party_name AS customer_name, ass.org_id
        FROM apps.ap_suppliers ap,
           apps.ap_supplier_sites_all ass,
           apps.hz_parties a,
           apps.hz_party_sites ps
        WHERE ap.vendor_id = ass.vendor_id
         AND a.party_id = ap.party_id
         AND ps.party_id = ap.party_id
         AND ass.party_site_id = ps.party_site_id
         AND a.party_name = ap.vendor_name
         and a.party_id = 12465 -- modify this part/query
         --and ass.org_id = 204
         and NOT Exists (select 1 from hz_cust_accounts a
         where a.party_id = a.party_id);
BEGIN
FND_GLOBAL.APPS_INITIALIZE( &user_id, &responsibility_id, 222); -- input from 1st sql
dbms_output.put_line('***************************');
 FOR update_base_tables_rec IN update_base_tables_cur
 LOOP
   NULL;
   cust_account_rec_type.cust_account_id := fnd_api.g_miss_num;
   cust_account_rec_type.account_name := update_base_tables_rec.customer_name;
   l_organization_rec.party_rec.party_id := update_base_tables_rec.party_id;
   l_organization_rec.party_rec.party_number := update_base_tables_rec.party_number;
   l_organization_rec.organization_name := update_base_tables_rec.customer_name;
   cust_account_rec_type.orig_system_reference := update_base_tables_rec.customer_ref;
   l_customer_profile_rec.party_id := update_base_tables_rec.party_id;
   l_customer_profile_rec.profile_class_id := 0 ; -- use DEFAULT profile with id=0
   l_customer_profile_rec.created_by_module := 'HZ_CPUI';
   cust_account_rec_type.created_by_module := 'HZ_CPUI';
   hz_cust_account_v2pub.create_cust_account
             (p_init_msg_list       => fnd_api.g_false,
              p_cust_account_rec     => cust_account_rec_type,
              p_organization_rec     => l_organization_rec,
              p_customer_profile_rec   => l_customer_profile_rec,
              p_create_profile_amt    => fnd_api.g_true,
              x_cust_account_id      => l_num_cust_id,
              x_account_number      => l_chr_acct_num,
              x_party_id         => l_num_party_id,
              x_party_number       => l_chr_party_number,
              x_profile_id        => l_num_profile_id,
              x_return_status       => l_chr_return_status,
              x_msg_count         => l_num_msg_count,
              x_msg_data         => l_chr_msg_data
             );
dbms_output.put_line('x_return_status: '||l_chr_return_status);
dbms_output.put_line('x_cust_account_id: '||l_num_cust_id);
dbms_output.put_line('x_account_number: '||l_chr_acct_num);
dbms_output.put_line('x_party_id: '||l_num_party_id);
   IF l_chr_return_status != 'S'
   THEN
    --Display all the error messages
    FOR j IN 1 .. fnd_msg_pub.count_msg
    LOOP
      DBMS_OUTPUT.put_line (j);
      l_chr_msg_data :=
            fnd_msg_pub.get (p_msg_index   => j,
                     p_encoded    => 'F');
      DBMS_OUTPUT.put_line ('Message(' || j || '):= ' || l_chr_msg_data);
    END LOOP;
   END IF;
   BEGIN
    SELECT cust_account_id
     INTO l_cust_acct_site_rec.cust_account_id
     FROM hz_cust_accounts
     WHERE orig_system_reference = update_base_tables_rec.customer_ref;
   EXCEPTION
    WHEN OTHERS
    THEN
      l_cust_acct_site_rec.cust_account_id := fnd_api.g_miss_num;
   END;
   l_cust_acct_site_rec.party_site_id := update_base_tables_rec.party_site_id;
   l_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
   l_cust_acct_site_rec.orig_system_reference := update_base_tables_rec.address_ref;
   l_cust_acct_site_rec.status := 'A';
   l_cust_acct_site_rec.org_id := update_base_tables_rec.org_id;
   mo_global.init ('ONT');
   mo_global.set_policy_context (p_access_mode   => 'S',
                  p_org_id      => update_base_tables_rec.org_id
                 );
   hz_cust_account_site_v2pub.create_cust_acct_site
                (p_init_msg_list      => 'T',
                p_cust_acct_site_rec   => l_cust_acct_site_rec,
                x_cust_acct_site_id    => l_num_obj_ver_num,
                x_return_status      => l_chr_sit_return_status,
                x_msg_count        => l_num_sit_msg_count,
                x_msg_data        => l_chr_sit_msg_data
                );
dbms_output.put_line('x_cust_acct_site_id: '||l_num_obj_ver_num);
dbms_output.put_line('x_return_status: '||l_chr_sit_return_status);
   IF l_chr_sit_return_status != 'S'
   THEN
    --Display all the error messages
    FOR j IN 1 .. fnd_msg_pub.count_msg
    LOOP
      DBMS_OUTPUT.put_line (j);
      l_chr_sit_msg_data :=
            fnd_msg_pub.get (p_msg_index   => j,
                     p_encoded    => 'F');
      DBMS_OUTPUT.put_line (  'Site Message('
                 || j
                 || '):= '
                 || l_chr_sit_msg_data
                );
    END LOOP;
   END IF;
   BEGIN
    SELECT cust_acct_site_id
     INTO l_cust_acct_site_use_rec.cust_acct_site_id
     FROM hz_cust_acct_sites_all
     WHERE orig_system_reference = update_base_tables_rec.address_ref;
   EXCEPTION
    WHEN OTHERS
    THEN
      l_cust_acct_site_use_rec.cust_acct_site_id := fnd_api.g_miss_num;
   END;
   l_cust_acct_site_use_rec.org_id := update_base_tables_rec.org_id;
   l_cust_acct_site_use_rec.site_use_code := 'BILL_TO';
   l_cust_acct_site_use_rec.status := 'A';
   l_cust_acct_site_use_rec.primary_flag := 'Y';
   l_cust_acct_site_use_rec.orig_system_reference :=
                      update_base_tables_rec.address_ref;
   l_cust_acct_site_use_rec.created_by_module := 'HZ_CPUI';
   mo_global.set_policy_context (p_access_mode   => 'S',
                  p_org_id      => update_base_tables_rec.org_id
                 );
   hz_cust_account_site_v2pub.create_cust_site_use
              (p_init_msg_list       => 'T',
              p_cust_site_use_rec     => l_cust_acct_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_num_site_use_id,
              x_return_status       => l_chr_situ_return_status,
              x_msg_count         => l_num_situ_msg_count,
              x_msg_data         => l_chr_situ_msg_data
              );
dbms_output.put_line('x_site_use_id: '||l_num_site_use_id);
dbms_output.put_line('x_return_status: '||l_chr_situ_return_status);
   IF l_chr_situ_return_status != 'S'
   THEN
    --Display all the error messages
    FOR j IN 1 .. fnd_msg_pub.count_msg
    LOOP
      DBMS_OUTPUT.put_line (j);
      l_chr_situ_msg_data :=
            fnd_msg_pub.get (p_msg_index   => j,
                     p_encoded    => 'F');
      DBMS_OUTPUT.put_line (  'Site Use Message('
                 || j
                 || '):= '
                 || l_chr_situ_msg_data
                );
    END LOOP;
   END IF;
 END LOOP;
END;
/

Oracle Trading Community: TCA API Overview, Setup, Sample Scripts and Troubleshooting (Doc ID 1637029.2)

Metalink Id for TCA API's

Oracle Trading Community: TCA API Overview, Setup, Sample Scripts and Troubleshooting (Doc ID 1637029.2)

Tuesday, April 29, 2014

How to run Api Hz_party_search.Find_parties Example


Doc ID 804145.1 
 
 --Create Party
DECLARE
   l_organization_rec   hz_party_v2pub.organization_rec_type;
   p_party_id           NUMBER;
   p_party_number       VARCHAR2 (20);
   p_profile_id         NUMBER;
   l_return_status      VARCHAR2 (2000);
   l_msg_count          NUMBER;
   l_msg_data           VARCHAR2 (2000);
BEGIN
   l_organization_rec.organization_name := 'Test Party';
   l_organization_rec.created_by_module := 'HZ_TRADING_COMMUNITY';
   hz_party_v2pub.create_organization ('T',
                                       l_organization_rec,
                                       l_return_status,
                                       l_msg_count,
                                       l_msg_data,
                                       p_party_id,
                                       p_party_number,
                                       p_profile_id);
   DBMS_OUTPUT.put_line ('l_return_status:' || l_return_status);
   IF l_msg_count > 1
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         DBMS_OUTPUT.put_line (
               i
            || '. '
            || SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255));
      END LOOP;
   END IF;
END;
l_return_status:S

PL/SQL procedure successfully completed.

-- In our test case DQM profile HZ: DQM syncronization method  is set at Batch - Ran the DQM synchronization program so the party is moved to --the HZ Staged tables

--- Now Find that party
 
DECLARE
   l_party_rec         hz_party_search.party_search_rec_type;
   l_site_list         hz_party_search.party_site_list;
   l_cont_list         hz_party_search.contact_list;
   l_cont_point_list   hz_party_search.contact_point_list;
   l_return_id         NUMBER;
   l_num_matches       NUMBER;
   l_ret_status        VARCHAR2 (2000);
   l_msg_count         NUMBER;
   l_msg_data          VARCHAR2 (2000);
   l_rule_id           NUMBER := 61;
BEGIN
   l_party_rec.party_name := 'Test Party';
   hz_party_search.find_parties (
p_init_msg_list        => fnd_api.g_true,
x_rule_id              => l_rule_id,
p_party_search_rec     => l_party_rec,
p_party_site_list      => l_site_list,
p_contact_list         => l_cont_list,
p_contact_point_list   => l_cont_point_list,
p_restrict_sql         => NULL-- ,p_match_type => NULL
,
p_search_merged        => 'N',
x_search_ctx_id        => l_return_id,
x_num_matches          => l_num_matches,
x_return_status        => l_ret_status,
x_msg_count            => l_msg_count,
x_msg_data             => l_msg_data);

   DBMS_OUTPUT.put_line ('l_return_status:' || l_ret_status);
   DBMS_OUTPUT.put_line ('l_num_matches:' || l_num_matches);
   IF l_ret_status <> 'S'
   THEN
      IF l_msg_count > 1
      THEN
         FOR i IN 1 .. l_msg_count
         LOOP
            DBMS_OUTPUT.put_line (
                  i
               || '.'
               || SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
                          1,
                          255));
         END LOOP;
      END IF;
   END IF;
END;
 
 
l_return_status:S
l_num_matches:1


PL/SQL procedure successfully completed.

Wednesday, January 15, 2014

TCA Api to Update the Party Information (HZ_PARTY_V2PUB.UPDATE_ORGANIZATION)

DECLARE
   l_init_msg_list                                   VARCHAR2(200);
   l_organization_rec                                apps.hz_party_v2pub.organization_rec_type;
   l_party_rec                                       apps.hz_party_v2pub.party_rec_type;
   l_party_object_version_number                     NUMBER;
   x_profile_id                                      NUMBER;
   l_error_message                                   VARCHAR2(2000);
   l_msg_index_out                                   NUMBER;
   x_return_status                                   VARCHAR2(200);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2(200);
BEGIN
   l_init_msg_list                                := 1.0;
   l_party_rec.party_id                           := 4302;
   l_party_rec.attribute4                         := 'Valid';
   l_organization_rec.party_rec                   := l_party_rec;
   x_profile_id                                   := NULL;
   x_return_status                                := NULL;
   x_msg_count                                    := NULL;
   x_msg_data                                     := NULL;
   SELECT object_version_number
     INTO l_party_object_version_number
     FROM hz_parties
    WHERE party_id = l_party_rec.party_id
      AND status = 'A';
   apps.hz_party_v2pub.update_organization(p_init_msg_list               => apps.fnd_api.g_true
                                         , p_organization_rec            => l_organization_rec
                                         , p_party_object_version_number => l_party_object_version_number
                                         , x_profile_id                  => x_profile_id
                                         , x_return_status               => x_return_status
                                         , x_msg_count                   => x_msg_count
                                         , x_msg_data                    => x_msg_data
                                          );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get(p_msg_index                   => i, p_encoded => fnd_api.g_false, p_data => x_msg_data
                            , p_msg_index_out               => l_msg_index_out);

         IF l_error_message IS NULL
         THEN
            l_error_message                                := SUBSTR(x_msg_data, 1, 250);
         ELSE
            l_error_message                                := l_error_message || ' /' || SUBSTR(x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line('*****************************************');
      DBMS_OUTPUT.put_line('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line('*****************************************');
      DBMS_OUTPUT.put_line('Attribute4 for Party : ' || l_party_rec.party_id || ' Updated Successfully ');
      DBMS_OUTPUT.put_line('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line('Unexpected Error ' || SUBSTR(SQLERRM, 1, 250));
END;

Monday, September 23, 2013

Script tot Create an account site use Using TCA API

DECLARE
   p_cust_site_use_rec                               hz_cust_account_site_v2pub.cust_site_use_rec_type;
   p_customer_profile_rec                            hz_customer_profile_v2pub.customer_profile_rec_type;
   x_site_use_id                                     NUMBER;
   x_return_status                                   VARCHAR2 (2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (2000);
   l_created_by_module                               VARCHAR2 (240);
   l_error_message                                   VARCHAR2 (2000);
   l_msg_index_out                                   NUMBER;
BEGIN
   DBMS_APPLICATION_INFO.set_client_info ('&ORG_ID');

   SELECT lookup_code
     INTO l_created_by_module
     FROM fnd_lookup_values
    WHERE lookup_type = 'HZ_CREATED_BY_MODULES'
      AND ROWNUM = 1
      AND enabled_flag = 'Y'
      AND NVL (end_date_active, SYSDATE) >= SYSDATE;

   p_cust_site_use_rec.cust_acct_site_id                    := '<cust_acct_site_id>';
   p_cust_site_use_rec.site_use_code                        := '<BILL_TO/SHIP_TO>';
   p_cust_site_use_rec.created_by_module                    := l_created_by_module;
   hz_cust_account_site_v2pub.create_cust_site_use (p_init_msg_list               => fnd_api.g_true
                                                  , p_cust_site_use_rec           => p_cust_site_use_rec
                                                  , p_customer_profile_rec        => p_customer_profile_rec
                                                  , p_create_profile              => fnd_api.g_false
                                                  , p_create_profile_amt          => fnd_api.g_false
                                                  , x_site_use_id                 => x_site_use_id
                                                  , x_return_status               => x_return_status
                                                  , x_msg_count                   => x_msg_count
                                                  , x_msg_data                    => x_msg_data
                                                   );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Account Site Use Created Successfully ');
      DBMS_OUTPUT.put_line ('Site Use id : ' || x_site_use_id);
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;

Create an account site Using TCA API

DECLARE
   p_cust_acct_site_rec                              hz_cust_account_site_v2pub.cust_acct_site_rec_type;
   x_return_status                                   VARCHAR2 (2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (2000);
   x_cust_acct_site_id                               NUMBER;
   l_created_by_module                               VARCHAR2 (240);
   l_error_message                                   VARCHAR2 (2000);
   l_msg_index_out                                   NUMBER;
BEGIN
   DBMS_APPLICATION_INFO.set_client_info ('&ORG_ID');

   SELECT lookup_code
     INTO l_created_by_module
     FROM fnd_lookup_values
    WHERE lookup_type = 'HZ_CREATED_BY_MODULES'
      AND ROWNUM = 1
      AND enabled_flag = 'Y'
      AND NVL (end_date_active, SYSDATE) >= SYSDATE;

   p_cust_acct_site_rec.cust_account_id                     := '<cust_account_id>';
   p_cust_acct_site_rec.party_site_id                       := '<party_site_id>';
   p_cust_acct_site_rec.LANGUAGE                            := 'US';
   p_cust_acct_site_rec.created_by_module                   := l_created_by_module;
   hz_cust_account_site_v2pub.create_cust_acct_site (p_init_msg_list               => fnd_api.g_true
                                                   , p_cust_acct_site_rec          => p_cust_acct_site_rec
                                                   , x_cust_acct_site_id           => x_cust_acct_site_id
                                                   , x_return_status               => x_return_status
                                                   , x_msg_count                   => x_msg_count
                                                   , x_msg_data                    => x_msg_data
                                                    );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Account Site Created Successfully ');
      DBMS_OUTPUT.put_line ('Cust Acct Site Id : ' || x_cust_acct_site_id);
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;

Script tot Create a party site using TCA API

DECLARE
   p_party_site_rec                                  hz_party_site_v2pub.party_site_rec_type;
   x_party_site_id                                   NUMBER;
   x_party_site_number                               VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (2000);
   l_created_by_module                               VARCHAR2 (240);
   l_error_message                                   VARCHAR2 (2000);
   l_msg_index_out                                   NUMBER;
BEGIN
   DBMS_APPLICATION_INFO.set_client_info ('&ORG_ID');

   SELECT lookup_code
     INTO l_created_by_module
     FROM fnd_lookup_values
    WHERE lookup_type = 'HZ_CREATED_BY_MODULES'
      AND ROWNUM = 1
      AND enabled_flag = 'Y'
      AND NVL (end_date_active, SYSDATE) >= SYSDATE;

   p_party_site_rec.party_id                                := '<party_id>';
   p_party_site_rec.location_id                             := '<Location Id>';
   p_party_site_rec.identifying_address_flag                := 'Y';
   p_party_site_rec.created_by_module                       := l_created_by_module;
   hz_party_site_v2pub.create_party_site (p_init_msg_list               => 'T'
                                        , p_party_site_rec              => p_party_site_rec
                                        , x_party_site_id               => x_party_site_id
                                        , x_party_site_number           => x_party_site_number
                                        , x_return_status               => x_return_status
                                        , x_msg_count                   => x_msg_count
                                        , x_msg_data                    => x_msg_data
                                         );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Party Site Created Successfully ');
      DBMS_OUTPUT.put_line ('Party Site id: ' || x_party_site_id);
      DBMS_OUTPUT.put_line ('Party Site Number: ' || x_party_site_number);
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;

Create a physical location Using TCA API

DECLARE
   p_location_rec                                    hz_location_v2pub.location_rec_type;
   x_location_id                                     NUMBER;
   x_return_status                                   VARCHAR2 (2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (2000);
   l_created_by_module                               VARCHAR2 (240);
   l_error_message                                   VARCHAR2 (2000);
   l_msg_index_out                                   NUMBER;
BEGIN
   DBMS_APPLICATION_INFO.set_client_info ('&ORG_ID');
   SELECT lookup_code
     INTO l_created_by_module
     FROM fnd_lookup_values
    WHERE lookup_type = 'HZ_CREATED_BY_MODULES'
      AND ROWNUM = 1
      AND enabled_flag = 'Y'
      AND NVL (end_date_active, SYSDATE) >= SYSDATE;

   p_location_rec.country                                   := 'US';
   p_location_rec.address1                                  := 'TCA API Test Location';
   p_location_rec.city                                      := 'san Mateo';
   p_location_rec.postal_code                               := '94401';
   p_location_rec.state                                     := 'CA';
   p_location_rec.created_by_module                         := l_created_by_module;
   hz_location_v2pub.create_location (p_init_msg_list               => fnd_api.g_true
                                    , p_location_rec                => p_location_rec
                                    , x_location_id                 => x_location_id
                                    , x_return_status               => x_return_status
                                    , x_msg_count                   => x_msg_count
                                    , x_msg_data                    => x_msg_data
                                     );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Location Created Successfully ');
      DBMS_OUTPUT.put_line ('Location id : ' || x_location_id);
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;