Explain Oracle EBS Public APIs and TCA Architecture in Oracle Applications

13 / 60

PL/SQLintermediate

Oracle EBS Public APIs and TCA Architecture

Explain why Oracle exposes public PL/SQL APIs and how they should be used instead of direct table DML

EBS ships hundreds of public PL/SQL APIs, and they are the supported way to create or change application data. Writing directly to the base tables is the mistake this topic exists to prevent.

Public APIs are like using a bank's teller window instead of reaching behind the counter into the vault yourself — the teller (API) enforces all the necessary verification, logging, and compliance steps that a raw vault transaction would skip entirely.

Key Concepts

Why direct DML is wrong. An invoice is not a row. Creating one properly involves validation, defaulting, accounting, workflow notifications, and audit columns.

    INSERT INTO ap_invoices_all ...     -- a row that looks like an invoice

Nothing validated it, no workflow fired, no accounting was created, and the next patch may change the table. The row exists and the business object does not.

The standard calling convention, which is consistent across nearly every Oracle API:

    AP_INVOICE_PKG.CREATE_INVOICE(
        p_api_version   => 1.0,
        p_init_msg_list => FND_API.G_TRUE,
        x_return_status => l_status,
        x_msg_count     => l_msg_count,
        x_msg_data      => l_msg_data,
        ...);

    IF l_status <> FND_API.G_RET_STS_SUCCESS THEN
        FOR i IN 1 .. l_msg_count LOOP
            l_msg := FND_MSG_PUB.GET(p_msg_index => i,
                                     p_encoded   => FND_API.G_FALSE);
        END LOOP;
    END IF;

    x_return_status:  'S' success   'E' expected error   'U' unexpected

Because the convention is the same everywhere, one wrapper handles the error retrieval for every API you call. That consistency is the point of the pattern.

TCA is how customers and suppliers are modelled, and the separation is the thing to understand:

    Party            a person or organisation -- who they are
    Customer Account the commercial relationship -- how we trade
                     with them

One party can be both a customer and a supplier without being stored twice. A company that buys from you and sells to you is one party with two relationships, which is exactly what the old flat customer model could not express.

HZ_PARTY_V2PUB and HZ_CUST_ACCOUNT_V2PUB are the APIs, and the _V2PUB suffix marks the public, supported version.

HZ_PARTY_V2PUBHZ_CUST_ACCOUNT_V2PUB_V2PUB

Finding the right API. Oracle's Integration Repository lists every public API with its parameters. A package that is not in there is not public, whatever its name suggests, and calling it is as unsupported as direct DML.

The interview signal. "How would you create 500 suppliers?" A candidate reaching for INSERT has said something important. The answer is the TCA APIs, in a loop, checking the return status on each — slower than direct DML and correct, which is why the open interface pattern exists for genuinely large volumes.

INSERT

What the interviewer is probing.1. "Why use a public API rather than inserting into base tables?" Probing: the discipline. Stalls: "Speed." Moves up: direct inserts skip validation, derived columns and dependent records, so the data looks right and breaks downstream processes.

2. "What is TCA?" Probing: the vocabulary. Stalls: "Customers." Moves up: the trading community architecture — parties, party sites, accounts and relationships as the shared customer model across modules.

3. "What is the difference between a party and an account?" Probing: the key distinction. Stalls: "Nothing." Moves up: a party is the real-world entity; an account is the commercial relationship with it, so one party can have several accounts.

4. "How do you know an API is supported?" Probing: the practical check. Stalls: "Try it." Moves up: it is listed in the Integration Repository as public — anything else can change without notice in a patch.