Common Debugging Framework in Oracle Application


Debugging plays a very crucial rule when you develop something and in oracle application also it is no different. Prior to Oracle E-Business Suite 11i, each module used in Oracle EBS had its own debugging methodology. However, with the Common Debugging Framework initiative, Oracle has introduced a common set of profile options and tables that can be used to debug any application across all technologies used in oracle apps..

Starting in 11.5.10, FND has incorporated a debugging technique to enable debug messages to get stored into the table FND_LOG_MESSAGES. This method was introduced in 11.5.10 and it is available in subsequent releases.

There are few profile options to enable and retrieve the debug messages. Here are those profile options.

Profile Name Suggested value Comments
FND: Debug Log Enabled YES This turns the debugging feature on
FND: Debug Log Filename  NULL Use when you want debug messages to get stored to a file
FND: Debug Log Level STATEMENT Following are options listed from least to most detailed debugging : Unexpected, Error, Exception, Event, Procedure, Statement
FND: Debug Log Module % Indicate what modules to debug. You can use something like ‘ar%’ or even  ‘%arp_rounding%’ to limit modules debugged

Sample setting to debug everything:

FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module %

Sample setting to debug ONLY Receivables:

FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module ar% 

Sample Program:


Create or replace PACKAGE BODY xx_debug_pkg
AS
   g_level_statement      CONSTANT NUMBER         := fnd_log.level_statement;
   g_level_procedure      CONSTANT NUMBER         := fnd_log.level_procedure;
   g_level_event          CONSTANT NUMBER         := fnd_log.level_event;
   g_level_exception      CONSTANT NUMBER         := fnd_log.level_exception;
   g_level_error          CONSTANT NUMBER         := fnd_log.level_error;
   g_level_unexpected     CONSTANT NUMBER         := fnd_log.level_unexpected;
   g_default_module       CONSTANT VARCHAR2 (240) := 'Any_Package_Name';
   g_level_log_disabled   CONSTANT NUMBER         := 99;
   g_log_level                     NUMBER;
   g_log_enabled                   BOOLEAN;

PROCEDURE DEBUG (
      p_log_level    IN   NUMBER,
      p_module       IN   VARCHAR2,
      p_message      IN   VARCHAR2,
      p_request_id   IN   NUMBER
   )
   IS
   BEGIN
      fnd_profile.put ('AFLOG_MODULE', g_default_module);

      IF ((p_module IS NULL) OR (p_message IS NULL))
      THEN
         fnd_file.put_line
            (fnd_file.LOG,
                'Error in package:'
             || g_default_module
             || ' , module : debug :Parameters p_module and  p_message cant be null'
            );
      ELSE
         BEGIN
            IF (NVL ((fnd_profile.VALUE ('AFLOG_ENABLED')), 'N') = 'Y')
            THEN

                  IF (p_message IS NOT NULL AND p_log_level >= g_log_level)
                  THEN
                     fnd_log_repository.init;
                     fnd_log.STRING (p_log_level, g_default_module,
                                     p_message);
               END IF;
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                     'Error in package: '
                                  || g_default_module
                                  || ', module : debug, at check point 1 :'
                                  || SQLERRM
                                 );
         END;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'Error in package:'
                            || g_default_module
                            || ' , module : debug, at check point 2 :'
                            || SQLERRM
                           );
   END DEBUG;
END xx_debug_pkg;

Debugging an API from SQL*Plus

You can enable FND logging for just one single PL/SQL API. Here is how we can do it from SQL*Plus:

1. From SQL*Plus, issue the following:

fnd_global.apps_initialize(fnd_user_id, fnd_resp_id, fnd_appl_id);
fnd_profile.put('AFLOG_ENABLED', 'Y');
fnd_profile.put('AFLOG_MODULE', '%');
fnd_profile.put('AFLOG_LEVEL','1'); -- Level 1 is Statement Level
fnd_log_repository.init;

2. Call the desired API.

3. Call step 1 again, but this time set AFLOG_ENABLED to N.

Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test. If you are tracking the debug messages for a concurrent request, note down the Concurrent Request id. Otherwise, note down current max value of log sequence retrieved as follows:

SELECT MAX(LOG_SEQUENCE)
FROM FND_LOG_MESSAGES;

If you are debugging a concurrent process:

SELECT log.module , log.message_text message
FROM fnd_log_messages log,
            fnd_log_transaction_context con
WHERE con.transaction_id = < request_id >
AND con.transaction_type = 'REQUEST'
AND con.transaction_context_id = log.transaction_context_id
ORDER BY log.log_sequence;

Otherwise use this:

SELECT module, message_text
FROM fnd_log_messages
WHERE log_sequence > &max_log_from_step2
ORDER BY log_sequence;

Debugging OA pages 

  a. Enable the profile option: FND: Debug Log Enabled — Set to Yes

  b. Enable the profile option: FND: Debug Log Level — Set to Statement level

  c. Add the below piece of code in your OA page 

   boolean isLoggingEnabled = pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT);
    if (isLoggingEnabled)
        {
          pageContext.writeDiagnostics(this, "your log statement", OAFwkConstants.STATEMENT);
        }

To see log stmt on browser append below string to browser URL and click on enter

  &aflog_level=statement

Reference MOS Notes:

  • How to enable and retrieve FND debug log messages [ID 433199.1]
  • How Can Trace and Debug Be Turned On For A Concurrent Request? [ID 759389.1]
  • How to Collect an FND Diagnostics Trace (aka FND: Debug) [ID 372209.1]
  • How to Obtain Debug Log in R12 [ID 787727.1]
Advertisements

Oracle Project Foundation APIs


This article gives a brief description of the APIs that you can use to integrate project data from an external system with Oracle Projects. The procedures discussed below are located in the public API package PA_PROJECT_PUB.

 

Project Procedures:

1] CREATE_PROJECT

CREATE_PROJECT is a PL/SQL procedure that creates a project in Oracle Projects using a template or an existing project.

The Parameters:

Name Description
P_API_VERSION_NUMBER API standard version number
P_COMMIT API standard (default = ‘F’) indicates if transaction will be committed
P_INIT_MSG_LIST API standard (default = ‘F’) indicates if message stack will be initialized
P_MSG_COUNT API standard count of error messages
P_MSG_DATA API standard error message
P_RETURN_STATUS API standard Return of the API success/failure/unexpected error)
P_WORKFLOW_STARTED Shows if a workflow has been started (Y or N)
P_PM_PRODUCT_CODE Identifier of the external systems from which the project was imported
P_PROJECT_IN Input project details
P_PROJECT_OUT Output project details
P_KEY_MEMBERS The identification code for the role that the members have on the project
P_CLASS_CATEGORIES Identification code for the categories by which the project is classified
P_TASKS_IN Input task details of the project
P_TASKS_OUT Output task details of the project
P_ORG_ROLES Identifier for organization roles for project
P_STRUCTURE_IN Identifier of structure data
P_EXT_ATTR_TBL_IN Identifier of external attributes

2] DELETE_PROJECT

DELETE_PROJECT is a PL/SQL procedure used to delete a project and its tasks from Oracle Projects.

The Parameters:

Name Description
P_API_VERSION_NUMBER API standard version number
P_COMMIT API standard (default = ‘F’) indicates if transaction will be committed
P_INIT_MSG_LIST API standard (default = ‘F’) indicates if message stack will be initialized
P_MSG_COUNT API standard count of error messages
P_MSG_DATA API standard error message
P_RETURN_STATUS API standard Return of the API success/failure/unexpected error)
P_PM_PRODUCT_CODE Identifier of the external systems from which the project was imported
P_PM_PROJECT_REFERENCE The reference code that uniquely identifies the project in the external system
P_PA_PROJECT_ID The reference code that uniquely identifies the project in Oracle Projects

3] UPDATE_PROJECT

UPDATE_PROJECT is a PL/SQL procedure that updates project and task information from your external system to Oracle Projects to reflect changes you have made in the external system. UPDATE_PROJECT uses composite datatypes.

The Parameters:

Name Description
P_API_VERSION_NUMBER API standard version number
P_COMMIT API standard (default = ‘F’) indicates if transaction will be committed
P_INIT_MSG_LIST API standard (default = ‘F’) indicates if message stack will be initialized
P_MSG_COUNT API standard count of error messages
P_MSG_DATA API standard error message
P_RETURN_STATUS API standard Return of the API success/failure/unexpected error)
P_WORKFLOW_STARTED Shows if a workflow has been started (Y or N)
P_PM_PRODUCT_CODE Identifier of the external systems from which the project was imported
P_PROJECT_IN Input project details
P_PROJECT_OUT Output project details
P_KEY_MEMBERS The identification code for the role that the members have on the project
P_CLASS_CATEGORIES Identification code for the categories by which the project is classified
P_TASKS_IN Input task details of the project
P_TASKS_OUT Output task details of the project
P_ORG_ROLES Identifier for organization roles for project
P_STRUCTURE_IN Identifier of structure data
P_PASS_ENTIRE_STRUCTURE Flag indicating whether to pass entire structure
P_EXT_ATTR_TBL_IN Identifier of external attributes.

Load-Execute-Fetch Procedures:

The following is the list of API’s for Load-Execute-Fetch and should be executed in the order of sequence.

  • INIT_PROJECT
  • LOAD_PROJECT
  • LOAD_TASK
  • LOAD_CLASS_CATEGORY
  • LOAD_KEY_MEMBER
  • EXECUTE_CREATE_PROJECT/EXECUTE_UPDATE_PROJECT
  • FETCH_TASK
  • CLEAR_PROJECT

Check Procedures:

CHECK_DELETE_PROJECT_OK This API is used to determine if you can delete a project.
CHECK_CHANGE_PROJECT_ORG_OK This API is used to determine if you can change the CARRYING_OUT_ORGANIZATION_ID field for a particular project or task.
CHECK_CHANGE_PARENT_OK This API is used to determine if you can move a task from one parent task to another.
CHECK_UNIQUE_PROJECT_REFERENCE This API is used to determine if a new or changed project reference(PM_PROJECT_REFERENCE) is unique
CHECK_ADD_SUBTASK_OK This API is used to determine if a subtask can be added to a parent task.
CHECK_DELETE_TASK_OK This API is used to determine if you can delete a task.
CHECK_TASK_NUMBER_CHANGE_OK This API is used to determine if you can change a tasks number.
CHECK_UNIQUE_TASK_NUMBER This API is used to determine if a new or changed task number is unique within a project.
CHECK_UNIQUE_TASK_REFERENCE This API is used to determine if a new or changed task reference (PM_TASK_REFERENCE) is unique

You can refer Oracle Projects APIs, Client Extensions, and Open Interfaces for the record and table types used. Go to Oracle Integration Repository for more details in the above procedures.

Thanks..Have a nice day!

CONCSUB Utility: Run your Concurrent Program from OS Level


CONCSUB is a utility to submit the concurrent request from operating system level to run concurrent program, without having to log on to oracle applications.

The Syntax:

CONCSUB <APPS username>/<APPS password> \
<responsibility application short name> \
<responsibility name> \
<username> \
[WAIT=N|Y|<n seconds>] \
CONCURRENT \
<program application short name> \
<program name> \
[PROGRAM_NAME=<description>] \

[ORG_ID=<#>] - R12 onwards only
[REPEAT_TIME=<resubmission time>] \
[REPEAT_INTERVAL= <number>] \
[REPEAT_INTERVAL_UNIT=< resubmission unit>] \
[REPEAT_INTERVAL_TYPE=< resubmission type>] \
[REPEAT_END=<resubmission end date and time>] \
[START=<date>] \
[IMPLICIT=< type of concurrent request> \
[<parameter 1> ... <parameter n>]

The Parameters:

Parameter Name Required? Comment
<username/password> Yes The ORACLE username and password that provides access to the data that the program uses.
<responsibility application short name> Yes The application short name of the responsibility whose concurrent processing options to be used.
<responsibility name> Yes The name of the responsibility. If the name of the responsibility includes spaces, enclose that name in double quotes.
<username> Yes The uppercase username of the application user whose concurrent processing options to use.
<WAIT> No A flag that indicates whether to wait for the submitted request to complete. If one leaves this parameter out, the default value of N makes CONCSUB return to the operating system prompt without waiting for the request to complete. Set WAIT=Y to have CONCSUB check the request status every 60 seconds and returns to the operating system prompt when the request is completed.
<CONCURRENT> Yes A flag that separates the program specific parameters from the operating system parameters.
<program application short name> Yes The application short name of the concurrent program.
<program name> Yes The uppercase name of the program. It must be the short name that was enter in the Concurrent Programs window when defining a concurrent program.
<PROGRAM_NAME> No A descriptive name for your program.
<ORG_ID> No Introduced in R12, set to org id required for the report to be run with.
<REPEAT TIME> No The time of day to resubmit the request.  The format for the time is HH24:MI or HH24:MI:SS
<REPEAT_INTERVAL> No The interval between resubmission (a positive integer or real number). Use this parameter along with REPEAT_INTERVAL_UNIT to specify the time between resubmissions.
<REPEAT_INTERVAL_UNIT> No The unit of time used for the interval between resubmissions. The available units are MINUTES, HOURS, DAYS or MONTHS. Use this parameter along with REPEAT_INTERVAL to specify the time between resubmissions.
<REPEAT_INTERVAL_TYPE> No Whether to time the resubmission interval from the requested start time of the request or from its completion. Set this parameter either to START or END. The default value is START.
<REPEAT_END> No The date and time to stop resubmitting the concurrent request.
<START> No A start date and time for the program in this format:
DDMONRR HH24:MI:SS (as in 07€“APR02 18:32:05€™)
Because this date format includes a space, one must enclose the date in double quotation marks and single quotation marks.
<IMPLICIT> No Whether to show this concurrent request on the View Requests form. Specify NO, YES, ERROR or WARNING. The value IMPLICIT=NO allows the request to appear on the View Request form. The default value is NO.
<REPEAT_DAYS> No The number of days after which to repeat the concurrent request, calculated from the last requested start date.
<parameter 1> …<parameter n> No The program specific parameters. If a parameter includes spaces, enclose that parameter in double quotes, then in single quotes. If a parameter contains a double quotation mark as part of the argument, precede that mark with a backslash [\].

Here is an example of the command to run CONCSUB:

$ CONCSUB APPS/APPS \
SYSADMIN \
System Administrator \
SYSADMIN \
WAIT=N \
CONCURRENT \
FND \
FNDFMRTC \
PROGRAM_NAME=Register Custom Tables Weekly \
REPEAT_INTERVAL=7 \
REPEAT_INTERVAL_UNIT=DAYS \
REPEAT_INTERVAL_TYPE=START \
START='"08“JUN96 23:55:00€"'
CGL
APPLSYS
ALL
CGL

Notes:

1] For parameters that follow the CONCURRENT parameter and include spaces, enclose the parameter argument in double quotes, then again in single quotes. Oracle Application Object Library requires this syntax because it parses the argument string twice.

2] The above example uses the UNIX line continuation character ‘\’, not all operating systems support the use of this character.  If the line continuation character is not supported then the CONCSUB command should be submitted as one continuous line.

3] COCNSUB is also used to shutdown the concurrent managers, however to start the concurrent manager you have to use startmgr utility.

CONCSUB apps/apps SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND SHUTDOWN

CONCSUB apps/apps SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND ABORT

4] The CONCSUB executable is located at $FND_TOP/bin/CONCSUB.

Assam: Gateway to North East India


Hello…Here I am putting few clicks that I have taken during my visit to my hometown in assam. It a beautiful and diverse state with an incredible range of cultures and landscapes located in the northeast of India. Assam has many fascinating things to see and experience. Travel to Assam and you will find everything, from spectacular scenery and rare wildlife to vibrant cultures and soul-stirring places of pilgrimage.

Spread over hundred acres of land, the extensive tea gardens in Assam are known to produce some of the finest and most expensive teas in the world. Situated along the Brahmaputra river these Tea Gardens never fails to mesmerize the visitors with its unending beauty. There are about 846 Tea Gardens in Assam. They are known to produce 400 million kgs of tea per year. Robert Bruce, an official of the British empire, who is credited with discovery of tea in 1823, gave publicity of the existence of the plant, the leaves of which were boiled to prepare the tea.

Assam is also famous for Oil and can boast of having the oldest oil refinery in the country.

The pictures of Majuli. Majuli or majoli which lies in the Brahmaputra River is one of the world’s largest freshwater river islands.Majuli lies about 200km east of Guwahati, Assam’s largest city, and can be reached by ferry from the town of Jorhat. The majority of majuli’s inhabitants are tribal’s, mainly Mishings, who migrated here from Arunachal Pradesh hundreds of years ago. There are 144 villages with about 1.5 million people. The island measures about 880 square kilometers in area. The wetland is a hotspot for flora and fauna, harboring many rare and endangered avifauna species including migratory birds that arrive in the winter season.

The lush green paddy-fields of assam……….

And the roads….

100000 visits and counting!


Today, I saw the 100000th visitor reached the blog. It feels great to achieve such huge number of hits in just a year. The number may be look small in front of big blogs and other sites, but for a little independent blog like this, it looks huge.

I appreciate everyone who takes the time to read my blog.  I especially appreciate those who have left their views and who have liked the posts. I hope that I can continue to bring more articles in various topics related to apps in the upcoming days.

Creating the blog has not only increased my knowledge, but also providing me a great platform to interact with some awesome people like you around the world.

Again, thanks for visiting…

Oracle Workflow Process Components


Depending on the workflow process you wish to create, you need to define all or some of the following types of components to make up the process.

Item Type: A grouping of workflow components into a high level category. All components of a workflow process must be associated with a specific item type. An item type can contain multiple processes.

Item Type Attribute: A feature of the item type which stores information that can be globally referenced by any activity in a process. Also referred to as an item attribute.

Process Activity: A series of actions that need to be performed to accomplish a business goal. A process is represented by a workflow diagram. A process can include function activities, notification activities, event activities, and other process activities (subprocesses), as well as the transitions between these activities.

Event Activity: A business event modeled as an activity so that it can be included in a workflow process.

Notification Activity: A unit of work that requires human intervention. A notification activity sends a message to a performer.

Function Activity: An automated unit of work usually defined as a PL/SQL stored procedure. A function activity can also run an external function. In the standalone version of Oracle Workflow, a function activity can also run a Java program on the middle tier.

Message:  The information sent by a notification activity. The message may request the performer (the role receiving the message) to do some work or may simply provide information.

Lookup Type: A list of values that can be referenced by any activity in a workflow process. The values in the list are called lookup codes.

Transition: The relationship that defines the completion of one activity and the activation of another activity within a process. In a process diagram, a transition is represented as an arrow between two activities.

Item: A specific business document or transaction.

Process Instance: A unique item being managed by a process.

Oracle Workflow: Why to use it?


Oracle Workflow is a series of tools designed to facilitate the creation and management of business process
models. It manages business processes according to defined rules (workflow process definitions), including the activities occurring as part of the process and the relationships of those activities. 

The benefits:

  • In e-business, it can be used as a part of the integration hub to make communicate different enterprises with each other over the internet for an end-to-end business.
  • Lets you continuously model complex business scenarios with drag & drop interface.
  • Model sophisticated business processes that include looping, branching, parallel processing, rendezvous, and more.
  • It can help save time by reducing repetitive data entry tasks, automating the approval hierarchies, automatically delivering notifications and reminders of work to be done and also providing self-service monitoring capabilities.
  • It defines and implements your business policies, streamlines the entire process, captures exceptions and takes action and is capable to adapt your processes as your business changes.
  • E-business accelerates the demand for system integration and communication is required between systems both within and beyond the enterprise. Business event-based workflows allow
    modeling of cross-system processes, enabling business process-based integration.
  • Oracle Workflow helps you design applications for change using workflow processes, enabling continuous business process improvement.
  • Tightly integrated with Oracle Database and provides published PL/SQL & Java APIs to drive workflows.

Form Personalization, Call a Function, And Return Value To Screen?


In from personalization it is possible to call a database function that retrieves values based on other data entered on the screen by the user. The function then returns these values to a field on the screen. We can use a SELECT statement to call the function, put the data entered by user in some fields as required parameters to the function and finally assign the value returned by the function to a field in the form.

Before doing the exercise, please note the below points:

1] Every property that takes a string can either be processed literally or evaluated at runtime.

2] If you type a string in that does not start with ‘=’, then the exact value you type in will be used at runtime.

3] If the string you type starts with ‘=’, then the text immediately after that character will be evaluated at runtime. This allows you to write complex logic that can include references such as:

  • SQL operators, such as ||, TO_CHAR, DECODE, and NVL
  • Bind variables (:block.field), including: system: global and: parameter values. Use the ‘Add Item…’ button to assist with item names.
  • Calls to server-side functions that do not have OUT parameters.

4] To use SELECT statement, you must follow these rules:

  • The text must start with ‘=SELECT’
  • The column being selected must evaluate to a CHAR, with a length no longer than 2000 bytes.
  • Your SELECT statement should only return one row, but if more than one is returned only the value of the first row will be used.

Here is a simple example using a function in the database that queries values, then a personalization that sets the value queried. This example is defined in the miscellaneous transactions form (INVTTMTX.fmb) and passes the Project Information (Project Number, Task Number, Expenditure Type and Expenditure Org) to the function.

=select xx_getaccount_pkg.main(''||${item.MTL_TRX_LINE.ITEM.value}||'',
''||${item.MTL_TRX_LINE.SOURCE_PROJECT_NUMBER.value}||'',
''||${item.MTL_TRX_LINE.SOURCE_TASK_NUMBER.value}||'',
''||${item.MTL_TRX_LINE.EXPENDITURE_TYPE.value}||'',
''||${item.MTL_TRX_LINE.EXPENDITURE_ORG.value}||'') from dual

Note that the variable in this case is written as “${item.MTL_TRX_LINE.ITEM.value}”. The text “MTL_TRX_LINE.ITEM.” is the Block and Field where the item number is written. The syntax around the block and field name ensures that the callout is made to replace the value of the item before passing the text to the function. You can use similar naming for your own fields finding the name of the Block and Field using Help > Diagnostics > Examine. Also don’t put semicolon (;) after the statement.

Open the form that you want to personalize, then choose Help > Diagnostics > Custom Code > Personalize. In this case, the miscellaneous transactions form is opened.

Enter the main information about when this personalization will be active.

Use the ‘Validate’ button to test if the syntax of your string is valid. If the evaluation fails, the processing engine will return an ORA error as if the string had been part of a SQL expression. Otherwise, it will display the text exactly as it would appear at runtime in the current context.

Enter the action information detailing what the personalization will do. Here select the ‘Property Name’ as VALUE and then put the above sql statement.

Save the changes and test the personalization.

In this case, the Project Information (Project Number, Task Number, Expenditure Type and Expenditure Org) is passed from the screen to the database function. The function then derives the GL Account from the above parameters. The value is then replaced for the Account field on the screen.

Moving Form Personalizations Between Instances


Once you create and test personalizations in your test instance, you can move them to production instances. Personalizations can be extracted by the loader on a per-function basis or per-form basis (that is, each loader file will contain all of the personalizations for a single function or form, respectively). Note that upon uploading, all prior personalizations for that function are first deleted, and then the contents of the loader file are inserted.

The loader syntax is as follows:

To download rules for a particular function:

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES function_name=

Note: this style is not recommended, as the personalizations that affect a particular function can now be a mix of function- and form-level rules.

To download rules for a particular form:

FNDLOAD/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lctFND_FORM_CUSTOM_RULES form_name=

To download all personalizations (all forms and functions):

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES

Upload:

FNDLOAD / 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct

Item Category Creation APIs


There are few APIs in INV_ITEM_CATEGORY_PUB package related to item category. This article will follow a category flexfield structure. Please refer the below post for more detail.

How to Create Category and Category Set in Oracle Inventory?

INV_ITEM_CATEGORY_PUB.Create_Category

DECLARE
l_category_rec    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
l_return_status   VARCHAR2(80);
l_error_code      NUMBER;
l_msg_count       NUMBER;
l_msg_data        VARCHAR2(80);
l_out_category_id NUMBER;
BEGIN
  l_category_rec.segment1 := 'RED';

  SELECT f.ID_FLEX_NUM
    INTO l_category_rec.structure_id
    FROM FND_ID_FLEX_STRUCTURES f
   WHERE f.ID_FLEX_STRUCTURE_CODE = 'INV_COLORS';

  l_category_rec.description := 'Red';

  INV_ITEM_CATEGORY_PUB.Create_Category
          (
          p_api_version   => 1.0,
          p_init_msg_list => FND_API.G_FALSE,
          p_commit        => FND_API.G_TRUE,
          x_return_status => l_return_status,
          x_errorcode     => l_error_code,
          x_msg_count     => l_msg_count,
          x_msg_data      => l_msg_data,
          p_category_rec  => l_category_rec,
          x_category_id   => l_out_category_id
          );
  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Creation of Item Category is Successful : '||l_out_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Creation of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB. Delete_Category

DECLARE
l_return_status VARCHAR2(80);
l_error_code    NUMBER;
l_msg_count     NUMBER;
l_msg_data      VARCHAR2(80);
l_category_id   NUMBER;
BEGIN
  SELECT mcb.CATEGORY_ID
    INTO l_category_id
    FROM mtl_categories_b mcb
   WHERE mcb.SEGMENT1='RED'
     AND mcb.STRUCTURE_ID =
        (SELECT mcs_b.STRUCTURE_ID
           FROM mtl_category_sets_b mcs_b
          WHERE mcs_b.CATEGORY_SET_ID =
               (SELECT mcs_tl.CATEGORY_SET_ID
                  FROM mtl_category_sets_tl mcs_tl
                 WHERE CATEGORY_SET_NAME ='INV_COLORS_SET'
                 )
        );

    INV_ITEM_CATEGORY_PUB.Delete_Category
          (
          p_api_version     => 1.0,
          p_init_msg_list   => FND_API.G_FALSE,
          p_commit          => FND_API.G_TRUE,
          x_return_status   => l_return_status,
          x_errorcode       => l_error_code,
          x_msg_count       => l_msg_count,
          x_msg_data        => l_msg_data,
          p_category_id     => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Deletion of Item Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Deletion of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB.Update_Category_Description
Updates the category description.

DECLARE
         l_return_status VARCHAR2(80);
         l_error_code    NUMBER;
         l_msg_count     NUMBER;
         l_msg_data      VARCHAR2(80);
         l_category_id   NUMBER;
         l_description   VARCHAR2(80);
BEGIN
      select mcb.CATEGORY_ID into l_category_id
        from mtl_categories_b mcb
       where mcb.SEGMENT1='BLACK'
         and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
             from mtl_category_sets_b mcs_b
             where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                 from mtl_category_sets_tl mcs_tl
                 where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      l_description := 'new black color';

     INV_ITEM_CATEGORY_PUB.Update_Category_Description (
       p_api_version     => 1.0,
       p_init_msg_list   => FND_API.G_FALSE,
       p_commit          => FND_API.G_TRUE,
       x_return_status   => l_return_status,
       x_errorcode       => l_error_code,
       x_msg_count       => l_msg_count,
       x_msg_data        => l_msg_data,
       p_category_id     => l_category_id,
       p_description     => l_description);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Update of Item Category Description is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Update of Item Category Description Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

Use following API for assigning a category to a category set. A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.

INV_ITEM_CATEGORY_PUB.Create_Valid_Category

Create a record in mtl_category_set_valid_cats.

DECLARE
        l_return_status   VARCHAR2(80);
        l_error_code      NUMBER;
        l_msg_count       NUMBER;
        l_msg_data        VARCHAR2(80);
        l_category_set_id NUMBER;
        l_category_id     NUMBER;
BEGIN
       select mcs_tl.CATEGORY_SET_ID into l_category_set_id
         from mtl_category_sets_tl mcs_tl
        where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

       select mcb.CATEGORY_ID into l_category_id
         from mtl_categories_b mcb
        where mcb.SEGMENT1='RED'
          and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
              from mtl_category_sets_b mcs_b
              where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                    from mtl_category_sets_tl mcs_tl
                    where CATEGORY_SET_NAME ='INV_COLORS_SET'));

       INV_ITEM_CATEGORY_PUB.Create_Valid_Category (
             p_api_version        => 1.0,
             p_init_msg_list      => FND_API.G_FALSE,
             p_commit             => FND_API.G_TRUE,
             x_return_status      => l_return_status,
             x_errorcode          => l_error_code,
             x_msg_count          => l_msg_count,
             x_msg_data           => l_msg_data,
             p_category_set_id    => l_category_set_id,
             p_category_id        => l_category_id,
             p_parent_category_id => NULL );

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Create Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Create Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB.Delete_Valid_Category

Delete the record from mtl_category_set_valid_cats.

DECLARE
           l_return_status    VARCHAR2(80);
           l_error_code       NUMBER;
           l_msg_count        NUMBER;
           l_msg_data         VARCHAR2(80);
           l_category_set_id  NUMBER;
           l_category_id      NUMBER;
BEGIN
         select mcs_tl.CATEGORY_SET_ID into l_category_set_id
           from mtl_category_sets_tl mcs_tl
          where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

         select mcb.CATEGORY_ID into l_category_id
           from mtl_categories_b mcb
          where mcb.SEGMENT1='RED'
            and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
                from mtl_category_sets_b mcs_b
                where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                  from mtl_category_sets_tl mcs_tl
                  where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      INV_ITEM_CATEGORY_PUB.Delete_Valid_Category (
            p_api_version      => 1.0,
            p_init_msg_list    => FND_API.G_FALSE,
            p_commit           => FND_API.G_TRUE,
            x_return_status    => l_return_status,
            x_errorcode        => l_error_code,
            x_msg_count        => l_msg_count,
            x_msg_data         => l_msg_data,
            p_category_set_id  => l_category_set_id,
            p_category_id      => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Delete Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Delete Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

The above scripts are tested in R12.1.3

Thanks….