Developer ToolsFree generatorPDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS Customer Creation API Script Generator (TCA HZ V2 APIs)

This generator writes one PL/SQL block that creates a complete Oracle EBS R12 organization customer through the public TCA V2 APIs: party and account, location, party site, account site in an operating unit, and BILL_TO and SHIP_TO site uses. It stops and rolls back on the first API error and prints every message from the FND message stack.

Fill in

FND user_id

Responsibility ID

Responsibility application ID

222 is Receivables.

Operating unit ORG_ID

Customer name

Account number (blank if automatic numbering)

Address line 1

City

Postal code

State

Country (ISO 2-letter)

Created by module

Must be a code in lookup HZ_CREATED_BY_MODULES.

Generated code

Download:

Create customer, address and BILL_TO / SHIP_TO site uses

-- Tables written: HZ_PARTIES, HZ_ORGANIZATION_PROFILES, HZ_CUST_ACCOUNTS, HZ_CUSTOMER_PROFILES,
-- HZ_LOCATIONS, HZ_PARTY_SITES, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL
SET SERVEROUTPUT ON
DECLARE
  l_acct_rec          hz_cust_account_v2pub.cust_account_rec_type;
  l_org_rec           hz_party_v2pub.organization_rec_type;
  l_prof_rec          hz_customer_profile_v2pub.customer_profile_rec_type;
  l_loc_rec           hz_location_v2pub.location_rec_type;
  l_ps_rec            hz_party_site_v2pub.party_site_rec_type;
  l_site_rec          hz_cust_account_site_v2pub.cust_acct_site_rec_type;
  l_use_rec           hz_cust_account_site_v2pub.cust_site_use_rec_type;

  l_cust_account_id   NUMBER;
  l_account_number    VARCHAR2(30);
  l_party_id          NUMBER;
  l_party_number      VARCHAR2(30);
  l_profile_id        NUMBER;
  l_location_id       NUMBER;
  l_party_site_id     NUMBER;
  l_party_site_number VARCHAR2(30);
  l_cust_acct_site_id NUMBER;
  l_bill_to_use_id    NUMBER;
  l_ship_to_use_id    NUMBER;

  l_return_status     VARCHAR2(1);
  l_msg_count         NUMBER;
  l_msg_data          VARCHAR2(2000);
  e_api_error         EXCEPTION;

  PROCEDURE check_status(p_step IN VARCHAR2) IS
  BEGIN
    IF l_return_status <> fnd_api.g_ret_sts_success THEN
      DBMS_OUTPUT.PUT_LINE(p_step || ' failed: status ' || l_return_status || ', ' || l_msg_count || ' message(s)');
      FOR i IN 1 .. NVL(l_msg_count, 0) LOOP
        DBMS_OUTPUT.PUT_LINE('  ' || fnd_msg_pub.get(p_msg_index => i, p_encoded => fnd_api.g_false));
      END LOOP;
      RAISE e_api_error;
    END IF;
    DBMS_OUTPUT.PUT_LINE(p_step || ' OK');
  END check_status;

BEGIN
  fnd_global.apps_initialize(user_id => 1318, resp_id => 50559, resp_appl_id => 222);
  mo_global.init('AR');
  mo_global.set_policy_context('S', 204);

  -- 1. Organization party + customer account (default profile class)
  l_org_rec.organization_name   := 'Acme Precision Components Inc';
  l_org_rec.created_by_module   := 'TCA_V2_API';
  l_acct_rec.account_name       := 'Acme Precision Components Inc';
  l_acct_rec.account_number     := '[Account number (blank if automatic numbering)]';  -- NULL when automatic customer numbering is on
  l_acct_rec.created_by_module  := 'TCA_V2_API';

  hz_cust_account_v2pub.create_cust_account(
    p_init_msg_list        => fnd_api.g_true,
    p_cust_account_rec     => l_acct_rec,
    p_organization_rec     => l_org_rec,
    p_customer_profile_rec => l_prof_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);
  check_status('create_cust_account ' || l_account_number);

  -- 2. Location
  l_loc_rec.country           := 'US';
  l_loc_rec.address1          := '400 Industrial Parkway';
  l_loc_rec.city              := 'Dayton';
  l_loc_rec.postal_code       := '45402';
  l_loc_rec.state             := 'OH';
  l_loc_rec.created_by_module := 'TCA_V2_API';

  hz_location_v2pub.create_location(
    p_init_msg_list => fnd_api.g_true,
    p_location_rec  => l_loc_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);
  check_status('create_location ' || l_location_id);

  -- 3. Party site
  l_ps_rec.party_id                 := l_party_id;
  l_ps_rec.location_id              := l_location_id;
  l_ps_rec.identifying_address_flag := 'Y';
  l_ps_rec.created_by_module        := 'TCA_V2_API';

  hz_party_site_v2pub.create_party_site(
    p_init_msg_list     => fnd_api.g_true,
    p_party_site_rec    => l_ps_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);
  check_status('create_party_site ' || l_party_site_number);

  -- 4. Account site in operating unit 204
  l_site_rec.cust_account_id   := l_cust_account_id;
  l_site_rec.party_site_id     := l_party_site_id;
  l_site_rec.org_id            := 204;
  l_site_rec.created_by_module := 'TCA_V2_API';

  hz_cust_account_site_v2pub.create_cust_acct_site(
    p_init_msg_list      => fnd_api.g_true,
    p_cust_acct_site_rec => l_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);
  check_status('create_cust_acct_site ' || l_cust_acct_site_id);

  -- 5. BILL_TO site use
  l_use_rec.cust_acct_site_id := l_cust_acct_site_id;
  l_use_rec.site_use_code     := 'BILL_TO';
  l_use_rec.primary_flag      := 'Y';
  l_use_rec.org_id            := 204;
  l_use_rec.created_by_module := 'TCA_V2_API';
  -- l_use_rec.location := 'DAYTON-BILL';  -- required only when Automatic Site Numbering is off

  hz_cust_account_site_v2pub.create_cust_site_use(
    p_init_msg_list        => fnd_api.g_true,
    p_cust_site_use_rec    => l_use_rec,
    p_customer_profile_rec => l_prof_rec,
    p_create_profile       => fnd_api.g_false,
    p_create_profile_amt   => fnd_api.g_false,
    x_site_use_id          => l_bill_to_use_id,
    x_return_status        => l_return_status,
    x_msg_count            => l_msg_count,
    x_msg_data             => l_msg_data);
  check_status('create_cust_site_use BILL_TO ' || l_bill_to_use_id);

  -- 6. SHIP_TO site use linked to the bill-to
  l_use_rec.site_use_code       := 'SHIP_TO';
  l_use_rec.bill_to_site_use_id := l_bill_to_use_id;

  hz_cust_account_site_v2pub.create_cust_site_use(
    p_init_msg_list        => fnd_api.g_true,
    p_cust_site_use_rec    => l_use_rec,
    p_customer_profile_rec => l_prof_rec,
    p_create_profile       => fnd_api.g_false,
    p_create_profile_amt   => fnd_api.g_false,
    x_site_use_id          => l_ship_to_use_id,
    x_return_status        => l_return_status,
    x_msg_count            => l_msg_count,
    x_msg_data             => l_msg_data);
  check_status('create_cust_site_use SHIP_TO ' || l_ship_to_use_id);

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Created account ' || l_account_number || ' (cust_account_id ' || l_cust_account_id || ', party_id ' || l_party_id || ')');
EXCEPTION
  WHEN e_api_error THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Rolled back, nothing created.');
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    RAISE;
END;
/

Check what was created

SELECT p.party_name, ca.account_number, ps.party_site_number, l.address1, l.city,
       su.site_use_code, su.site_use_id, su.location, su.primary_flag, su.status
FROM   hz_cust_accounts ca
JOIN   hz_parties p               ON p.party_id = ca.party_id
JOIN   hz_cust_acct_sites_all cas ON cas.cust_account_id = ca.cust_account_id
JOIN   hz_party_sites ps          ON ps.party_site_id = cas.party_site_id
JOIN   hz_locations l             ON l.location_id = ps.location_id
JOIN   hz_cust_site_uses_all su   ON su.cust_acct_site_id = cas.cust_acct_site_id
WHERE  p.party_name = 'Acme Precision Components Inc'
AND    cas.org_id = 204;

Uses named notation, so parameter order cannot go wrong, but confirm the record fields and overloads in the Oracle Trading Community Architecture API Reference for your release. Run in a test instance first. Get user_id, resp_id and resp_appl_id from FND_USER and FND_RESPONSIBILITY for a Receivables responsibility with access to the operating unit.

Planning a customer load into EBS?

Send a sample of your legacy customer file and we will map it to the TCA records and flag the fields that will fail validation.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Fill in the application context (user, Receivables responsibility and its application ID 222), the operating unit, the customer name and address and the created-by-module code. Leave the account number blank if Receivables System Options has Automatic Customer Numbering enabled; otherwise type the number. Paste the block into SQL Developer or SQL*Plus connected as APPS with server output on. Each step prints OK or the full error list. The final query shows the account, party site, address and site uses so you can confirm the result before using the customer on an order.

The R12 TCA customer model

TCA separates who the customer is from how you trade with them. HZ_PARTIES holds the organization, HZ_LOCATIONS the physical address and HZ_PARTY_SITES the link between them; these are shared across all operating units. HZ_CUST_ACCOUNTS is the commercial account on top of the party, and HZ_CUSTOMER_PROFILES carries credit and statement settings from a profile class. The operating-unit striped tables are HZ_CUST_ACCT_SITES_ALL, which makes a party site usable by one OU, and HZ_CUST_SITE_USES_ALL, which gives that site its business purposes such as BILL_TO and SHIP_TO. The APIs must be called in this order because each one needs the ID returned by the previous call.

create_cust_account: party_id, cust_account_id, profile
create_location then create_party_site: location_id, party_site_id
create_cust_acct_site: cust_acct_site_id per OU
create_cust_site_use: site_use_id per purpose

Common API errors

An invalid created_by_module error means the value is not an enabled code in lookup HZ_CREATED_BY_MODULES; TCA_V2_API is the usual seeded value. An account number required error means automatic customer numbering is off; a location required error on the site use means Automatic Site Numbering is off, so uncomment the location line. Org-related errors usually mean MO_GLOBAL context was not set or the responsibility has no access to that operating unit. Duplicate primary flag errors happen when the account already has a primary BILL_TO in the OU. If the Customer Interface (RA_CUSTOMERS_INTERFACE_ALL) was used before, note that R12 sites usually move to the APIs or bulk import instead.

Worked example

With the defaults, the block creates party Acme Precision Components Inc, an automatically numbered account, a location at 400 Industrial Parkway, Dayton OH 45402, a party site marked as the identifying address, an account site in operating unit 204, a primary BILL_TO use and a primary SHIP_TO use whose bill-to points at the new BILL_TO. The verification query then returns two rows, one per site use, both with status A. The customer can be selected in Sales Orders and AR Transactions immediately.

Frequently Asked Questions

Which API creates a customer account in Oracle EBS R12?

HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT. The overload that takes an ORGANIZATION_REC_TYPE creates the organization party and the account together and returns the cust_account_id, account number, party_id and profile ID. Addresses and site uses are added afterwards with HZ_LOCATION_V2PUB, HZ_PARTY_SITE_V2PUB and HZ_CUST_ACCOUNT_SITE_V2PUB.

What are the main customer tables in Oracle EBS?

HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_CUSTOMER_PROFILES, HZ_LOCATIONS, HZ_PARTY_SITES, HZ_CUST_ACCT_SITES_ALL and HZ_CUST_SITE_USES_ALL. Contacts add HZ_RELATIONSHIPS, HZ_ORG_CONTACTS and HZ_CUST_ACCOUNT_ROLES. Only the account site and site use tables are operating-unit specific.

How do I read the error messages from TCA APIs?

The APIs return x_return_status S, E or U and put messages on the FND message stack. Loop from 1 to x_msg_count calling FND_MSG_PUB.GET(p_msg_index => i, p_encoded => FND_API.G_FALSE) to get readable text, as the check_status procedure in this script does. Pass p_init_msg_list => FND_API.G_TRUE so each call starts with an empty stack.

Do I need to set the org context before calling the site APIs?

Yes. Account sites and site uses are striped by ORG_ID and validated against multi-org security, so call FND_GLOBAL.APPS_INITIALIZE with a responsibility that can access the operating unit, then MO_GLOBAL.INIT('AR') and MO_GLOBAL.SET_POLICY_CONTEXT('S', org_id), and set org_id on the site and site use records.

If you are loading customers from a legacy ERP and want an AI-assisted cleanse and load through these APIs on your own servers, with human approval before anything is written, we can set that up.

Talk to an Expert Explore More Free Tools