Introduction

We may sometimes need to define our own custom user-defined table, or “UDT”, to hold data like bank’s swift codes across different countries. in Oracle EBS, we have a function called “Table Structure” that we can define our table inside the application. However, sometimes we need to define these user-defined tables from the backend using public APIs.

In this article, we will create a user-defined table from the backend using Oracle standard API.

Goal

Create a user-defined table UDT using API in Oracle APPS R12

Standard API name: PAY_USER_TABLE_API.CREATE_USER_TABLE

Forms Or Pages

API Description

This API creates a user table record.

Prerequisites :

The business group where this record is to be created should exist.

API OUT Parameters :

API Parameters Clarifications:

• param: p_validate If true, validation alone will be performed, and the database will remain unchanged. The database will be modified if false and all validation checks pass.
• param: p_effective_date Determines when the DateTrack operation comes into force.
• param: p_business_group_id The user table’s business group.
• param: p_legislation_code The user table’s legislation.
• param: p_range_or_match Indicates whether the user key is an exact match (M) or within range (R). Defaults to M.
• param: p_user_key_units The data type of the user key number (N), date (D), text (T)). Defaults to N.
• param: p_user_table_name Name of the user table.
• param: p_user_row_title The user title for the rows.
• param: p_user_table_id If p_validate is false, this uniquely identifies the user table created. If p_validate is true, this parameter will be null.
• param: p_object_version_number If p_validate is false, then set it to the version number of the created user table. If p_validate is true, then the value will be null.

API Code Example

------------------------------------------ 
-- Description: API to define User-Defined Table UDT in oracle EBS R12
-- Created By: Hassan @ Oraask.com
-- Creation Date: 21-MAR-2023
------------------------------------------

DECLARE
  L_BUSINESS_GROUP_ID       NUMBER := 202;
  L_EFFECTIVE_DATE          DATE := TO_DATE ('01-JAN-2000', 'DD-MON-YYYY');
  L_RANGE_OR_MATCH          VARCHAR2 (1) := 'M'; -- Match
  L_USER_KEY_UNITS          VARCHAR2 (1) := 'T'; -- Text
  L_USER_TABLE_NAME         VARCHAR2 (100) := 'OSK_BANKS_SWIFTCODES';
  L_USER_TABLE_ID           NUMBER := NULL;
  L_OBJECT_VERSION_NUMBER   NUMBER := NULL;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('********************************');

  PAY_USER_TABLE_API.CREATE_USER_TABLE (P_VALIDATE                => FALSE
                                       ,P_EFFECTIVE_DATE          => L_EFFECTIVE_DATE
                                       ,P_BUSINESS_GROUP_ID       => L_BUSINESS_GROUP_ID
                                       ,P_RANGE_OR_MATCH          => L_RANGE_OR_MATCH
                                       ,P_USER_KEY_UNITS          => L_USER_KEY_UNITS
                                       ,P_USER_TABLE_NAME         => L_USER_TABLE_NAME
                                       ,P_USER_ROW_TITLE          => NULL
                                       ,P_USER_TABLE_ID           => L_USER_TABLE_ID
                                       ,P_OBJECT_VERSION_NUMBER   => L_OBJECT_VERSION_NUMBER);

  IF (L_USER_TABLE_ID IS NOT NULL) THEN
    DBMS_OUTPUT.PUT_LINE (L_USER_TABLE_NAME || ' has been Created with ID: ' || L_USER_TABLE_ID);
    COMMIT;
  ELSE
    DBMS_OUTPUT.PUT_LINE ('hr_utility.get_message = ' || HR_UTILITY.GET_MESSAGE);
  END IF;

  DBMS_OUTPUT.PUT_LINE ('********************************');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE ('********************************');
    DBMS_OUTPUT.PUT_LINE ('The API PAY_USER_TABLE_API.CREATE_USER_TABLE Ended with Error : ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE ('********************************');
END;

Output

OSK_BANKS_SWIFTCODES has been Created with ID: 654665

Final Result

Conclusion

This article gives an example of creating a user-defined table in Oracle EBS using API from the backend.

Please don’t use this code directly on production environment, instead test it on test environment first to make sure that API working correctly as per your requirements.Hopefully, it was clear and concise. Share to Spread Knowledge.