Create Customer using create_cust_account API

The following script helps to create a Customer with basic information (mandatory information) in Oracle Applications TCA Architecture.

DECLARE
    l_cust_account_rec       hz_cust_account_v2pub.cust_account_rec_type;
    l_org_rec                hz_party_v2pub.organization_rec_type;
    l_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;
    l_account_name           hz_cust_accounts.account_name%TYPE;

    l_msg_count              NUMBER;
    l_msg_data               VARCHAR2 (4000);
    l_err_msg                VARCHAR2 (4000);
    l_return_status          VARCHAR2 (1);
    l_cust_account_id        NUMBER;
    l_account_number         hz_cust_accounts.account_number%TYPE;
    l_party_id               hz_cust_accounts.party_id%TYPE;
    l_party_number           hz_parties.party_id%TYPE;
    l_cust_acct_profile_id   hz_customer_profiles.cust_account_profile_id%TYPE;
BEGIN
    l_cust_account_rec.account_name := 'Test Customer 98765432';
    l_cust_account_rec.account_number := '98765432';
    l_cust_account_rec.customer_type := 'R';
    l_cust_account_rec.attribute1 := SYSDATE;
    l_cust_account_rec.created_by_module := 'HZ_CPUI';
    l_org_rec.organization_name := 'Test Customer 98765432';

    hz_cust_account_v2pub.create_cust_account (
        p_init_msg_list          => fnd_api.g_true,
        p_cust_account_rec       => l_cust_account_rec,
        p_organization_rec       => l_org_rec,
        p_customer_profile_rec   => l_customer_profile_rec,
        p_create_profile_amt     => fnd_api.g_true,
        x_return_status          => l_return_status,
        x_msg_count              => l_msg_count,
        x_msg_data               => l_msg_data,
        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_cust_acct_profile_id);

    DBMS_OUTPUT.put_line ('API Return status is: ' || l_return_status);
    
    IF l_return_status <> 'S'
    THEN 
        DBMS_OUTPUT.put_line ('Error message is: ' || SUBSTR (l_msg_data, 200));
    END IF; 
    
    IF l_return_status = 'S'
    THEN
        BEGIN
            SELECT account_name
              INTO l_account_name
              FROM hz_cust_accounts
             WHERE account_number = '98769876';

            DBMS_OUTPUT.put_line (
                   'Customer Account created with Account number: '
                || l_account_number
                || ' and Cust Account ID: '
                || l_cust_account_id
                || ' and Account Name: '
                || l_account_name);
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line (
                    'Error during deriving Account Name: ' || SQLERRM);
        END;
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Error during creating account: ' || SQLERRM);
END;

The DMBS Output is shown as below:

Upon running the script above, a Customer is created. The customer can be found in Hz_Cust_accounts table.

Below is the query to check the customer and some important columns in the Hz_Cust_Accounts table.

SELECT cust_account_id,
       party_id,
       account_number,
       status,
       customer_type,
       account_name,
       created_by_module,
       h.*
  FROM hz_cust_accounts h
 WHERE account_number = '98765432';

 

 

Leave a Reply

  • (will not be published)