Calling Oracle EBS APIs From PL/SQL: A Practical Introduction

Why Use EBS APIs Instead of Direct DML

Oracle E-Business Suite stores data across thousands of tables with complex interdependencies, validation rules, and workflow triggers. Inserting or updating rows directly in EBS tables bypasses all of this logic and almost always leads to data corruption, failed concurrent requests, and unsupported customizations. Oracle provides public PL/SQL APIs for most business operations, and using them is the only supported approach for programmatic data manipulation.

Anatomy of an EBS API Call

EBS APIs follow a consistent pattern. They accept an API version number, initialization parameters, and business-specific input parameters. They return a status, message count, and message data through OUT parameters:

DECLARE
    l_return_status  VARCHAR2(1);
    l_msg_count      NUMBER;
    l_msg_data       VARCHAR2(2000);
    l_customer_id    NUMBER;
BEGIN
    -- Initialize the EBS environment
    FND_GLOBAL.APPS_INITIALIZE(
        user_id      => 1234,
        resp_id      => 50000,
        resp_appl_id => 222
    );

    -- Call the Create Customer 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_person_rec       => l_person_rec,
        p_customer_profile_rec => l_profile_rec,
        x_cust_account_id  => l_customer_id,
        x_return_status    => l_return_status,
        x_msg_count        => l_msg_count,
        x_msg_data         => l_msg_data
    );

    IF l_return_status != FND_API.G_RET_STS_SUCCESS THEN
        -- Retrieve error messages from the stack
        FOR i IN 1 .. l_msg_count LOOP
            DBMS_OUTPUT.PUT_LINE(FND_MSG_PUB.GET(i, FND_API.G_FALSE));
        END LOOP;
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END;

FND_GLOBAL.APPS_INITIALIZE

Every EBS API call requires a valid application context. FND_GLOBAL.APPS_INITIALIZE sets the user, responsibility, and application context that determines security, multi-org access, and profile values. Without this call, APIs will fail with cryptic errors about missing profiles or invalid organizations. The user_id, resp_id, and resp_appl_id values can be found in the FND_USER, FND_RESPONSIBILITY, and FND_APPLICATION tables.

MO_GLOBAL for Multi-Org

For APIs that operate within an operating unit context, call MO_GLOBAL.INIT or MO_GLOBAL.SET_POLICY_CONTEXT after APPS_INITIALIZE to set the correct organization:

MO_GLOBAL.SET_POLICY_CONTEXT('S', 204); -- Single org, org_id 204

Finding the Right API

Oracle documents public APIs in the Integration Repository (available through EBS, Navigation: Integrated SOA Gateway). You can also search Oracle Support for “API User Guide” for your specific module. Package names typically follow the pattern MODULE_ENTITY_PUB, such as AP_INVOICES_PKG, HZ_CUST_ACCOUNT_V2PUB, or OE_ORDER_PUB.