API to Create Request Group in Oracle APPS R12

Introduction

When we work with request group which a part of any responsibility, we often need to create a new custom request group to hold our requests. We can create a new request group from the front end through the application. However, in case we have numerous requests to create, it’s recommended to check whether Oracle provides an API to do the same job from the backend or not.

This article will give an API code example to create a request group from the backend.

Goal

Create a request group using API in Oracle APPS.
Standard API name: “FND_PROGRAM.REQUEST_GROUP“.

Forms Or Pages

API to Create Request Group in Oracle APPS R12 Figure 1 — Figure 1

API Parameters Clarifications:

group: Name of the request group.
application: Name of group’s application (e.g., Application Object Library).
code: Optional request group code.
description: Optional description of the request group.

API Code Example

------------------------------------------ 
-- Description: API to create request group in oracle EBS R12
-- Created By: Hassan @ Oraask.com
-- Creation Date: 27-OCT-2022
------------------------------------------

DECLARE
  L_REQUEST_GROUP       VARCHAR2 (32767);
  L_APPLICATION         VARCHAR2 (32767);
  L_CODE                VARCHAR2 (32767);
  L_DESCRIPTION         VARCHAR2 (32767);
  L_REQUEST_GROUP_ID    NUMBER;
  L_REQUEST_GROUP_CNT   NUMBER;
  L_ERROR_MSG           VARCHAR2 (4000);
BEGIN
  DBMS_OUTPUT.PUT_LINE ('********************************');
  L_REQUEST_GROUP := 'Oraask All Reports';
  L_APPLICATION := 'Application Object Library';
  L_CODE := 'OSK_ALL_REPORTS';
  L_DESCRIPTION := 'Oraask Reports';

  BEGIN
    SELECT COUNT (*)
    INTO   L_REQUEST_GROUP_CNT
    FROM   FND_REQUEST_GROUPS
    WHERE  REQUEST_GROUP_NAME = L_REQUEST_GROUP;
  END;


  IF (L_REQUEST_GROUP_CNT > 0) THEN
    FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
    FND_MESSAGE.SET_TOKEN ('MESSAGE', ' SEQ: 01' || ' - Request Group Already Exists');
    L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 4000);
    RAISE FND_API.G_EXC_ERROR;
  END IF;

  FND_PROGRAM.REQUEST_GROUP (REQUEST_GROUP   => L_REQUEST_GROUP
                            ,APPLICATION     => L_APPLICATION
                            ,CODE            => L_CODE
                            ,DESCRIPTION     => L_DESCRIPTION);

  BEGIN
    SELECT REQUEST_GROUP_ID
    INTO   L_REQUEST_GROUP_ID
    FROM   FND_REQUEST_GROUPS
    WHERE  REQUEST_GROUP_NAME = L_REQUEST_GROUP;

    IF (L_REQUEST_GROUP_ID IS NOT NULL) THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE ('Request Group Created Successfully');
      DBMS_OUTPUT.PUT_LINE ('L_REQUEST_GROUP_ID = ' || L_REQUEST_GROUP_ID);
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
      FND_MESSAGE.SET_TOKEN ('MESSAGE', ' SEQ: 02' || ' - Cannot Create Request Group: ' || SQLERRM);
      L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 4000);
      RAISE FND_API.G_EXC_ERROR;
  END;


  DBMS_OUTPUT.PUT_LINE ('********************************');
EXCEPTION
  WHEN FND_API.G_EXC_ERROR THEN
    DBMS_OUTPUT.PUT_LINE (L_ERROR_MSG);
    DBMS_OUTPUT.PUT_LINE ('********************************');
    ROLLBACK;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('Create Request Group Failed with Error: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE ('********************************');
    ROLLBACK;
END;

Explanation:

Firstly, We assigned the required values to our local variable
Then, we get the count of the current request group name we will create to check if it already exists or not. If it already exists, we raise an exception and print the error message
The next step is to call the standard API to create the request group.
We then get the new request group id recently created by the standard API.
If the above query returns the new request id, it means the request group has been created, so commit the transaction and print the success message to the dbms output to reflect on the application.
In case the above query returns no record, that means the request group is not created, then raise an exception and print the error message.

Output

Request Group Created Successfully
L_REQUEST_GROUP_ID = 1001807

Final Result

API to Create Request Group in Oracle APPS R12 Figure 2 — Figure 2

Conclusion

This article gives an example of creating a request group 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.