Wednesday, January 24, 2024

Application Object Library(AOL)


1.     Creation of USER:

NAVIGATION: SYSTEM ADMINISTRATOR à SECURITY à USER à DEFINE

CREATE A NEW USER:

Enter user name, password and Re-enter the password. 




Click on save to create a new user.

Query to check user is created or not:

SELECT *
FROM   FND_USER
WHERE  USER_NAME LIKE 'TESTUSER';


To Add Responsibilities: -

    Click on Direct Responsibilities tab 
    Select responsibility from LOV.
    Then click on save.



Create an user through API

I.          CREATE AN USER

 

/*=======================================================

-- API TO CREATE AN USER

-- FND_USER_PKG.CREATEUSER

==========================================================*/

DECLARE
  P_USER_NAME                  VARCHAR2(200)     := 'SYSCNTEST' ;
  P_OWNER                      VARCHAR2(200)     := NULL;
  P_UNENCRYPTED_PASSWORD       VARCHAR2(200)     := 'ORACLE123';
  P_SESSION_NUMBER             NUMBER            :=USERENV('SESSIONID');
  P_START_DATE                 DATE              := SYSDATE;
  P_END_DATE                   DATE              := NULL;
  P_LAST_LOGON_DATE            DATE              := NULL;
  P_DESCRIPTION                VARCHAR2(200)     := 'TEAMSEARCH';
  P_PASSWORD_DATE              DATE              := SYSDATE - 1;
  P_PASSWORD_ACCESSES_LEFT     NUMBER            := 1000;
  P_PASSWORD_LIFESPAN_ACCESSES NUMBER            := 1000;
  P_PASSWORD_LIFESPAN_DAYS     NUMBER            := 1000;
  P_EMPLOYEE_ID                NUMBER            := NULL;
  P_EMAIL_ADDRESS              VARCHAR2(200)     := NULL;
  P_FAX                        VARCHAR2(200)     := NULL;
  P_CUSTOMER_ID                NUMBER            := NULL;
  P_SUPPLIER_ID                NUMBER            := NULL;
  V_USER_ID                    NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID       => 1318,
                            RESP_ID       => 21623,
                            RESP_APPL_ID  => 660);
FND_USER_PKG.CREATEUSER(
      X_USER_NAME                   => P_USER_NAME,
      X_OWNER                       => P_OWNER,
      X_UNENCRYPTED_PASSWORD        => P_UNENCRYPTED_PASSWORD,
      X_SESSION_NUMBER              => P_SESSION_NUMBER,
      X_START_DATE                  => P_START_DATE,
      X_END_DATE                    => P_END_DATE,
      X_LAST_LOGON_DATE             => P_LAST_LOGON_DATE,
      X_DESCRIPTION                 => P_DESCRIPTION,
      X_PASSWORD_DATE               => P_PASSWORD_DATE,
      X_PASSWORD_ACCESSES_LEFT      => P_PASSWORD_ACCESSES_LEFT,
      X_PASSWORD_LIFESPAN_ACCESSES  => P_PASSWORD_LIFESPAN_ACCESSES,
      X_PASSWORD_LIFESPAN_DAYS      => P_PASSWORD_LIFESPAN_DAYS,
      X_EMPLOYEE_ID                 => P_EMPLOYEE_ID,
      X_EMAIL_ADDRESS               => P_EMAIL_ADDRESS,
      X_FAX                         => P_FAX,
      X_CUSTOMER_ID                 => P_CUSTOMER_ID,
      X_SUPPLIER_ID                 => P_SUPPLIER_ID
      );
 
  SELECT USER_ID
  INTO V_USER_ID
  FROM FND_USER
  WHERE USER_NAME = P_USER_NAME;
 
  DBMS_OUTPUT.PUT_LINE ('USER_ID : ' ||V_USER_ID);
COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('ERROR WHILE CREATING A USER: '||SQLERRM);
END;

------------------------------------
QUERY TO GET USER
-----------------------------------
SELECT *
FROM FND_USER
WHERE USER_NAME LIKE 'SYSCNTEST';

User syscntest created in oracle apps.

Log in with the user account that has just created by API.


We have successfully login with new user.


II.         DISABLE USER

/*=======================================================
-- API TO DISABLE USER
-- FND_USER_PKG.DISABLEUSER
==========================================================*/

-------SCRIPT-------

BEGIN
 FND_USER_PKG.DISABLEUSER(USERNAME => 'SYSCNTEST') ;
END;

 Disabled the existing user syscntest

       III.          CHANGE/UPDATE THE USER PASSWORD

/*=======================================================
-- API TO CHANGE/UPDATE THE USER PASSWORD
-- FND_USER_PKG.CHANGEPASSWORD
==========================================================*/

-------SCRIPT-------

SET SERVEROUTPUT ON;
 
DECLARE
  LB_RESULT BOOLEAN;
BEGIN
  LB_RESULT := FND_USER_PKG.CHANGEPASSWORD(USERNAME    => 'SYSCNTEST ',
                                           NEWPASSWORD => 'DBA#1234'
                                          );
  IF LB_RESULT
  THEN
    DBMS_OUTPUT.PUT_LINE('SUCESSFULLY UPDATED');
  END IF;
END;

       IV.          API TO CHANGE/UPDATE THE USER

/*=======================================================
-- API TO CHANGE/UPDATE THE USER
-- FND_USER_PKG.UPDATEUSER
==========================================================*/

 
DECLARE
    V_USER_NAME      VARCHAR2(100) := 'SYSCNTEST';
    V_EMAIL_ADDRESS  VARCHAR2(100) := 'TESTUSER@XYZ.COM';
 
BEGIN
   FND_USER_PKG.UPDATEUSER(X_USER_NAME                  => V_USER_NAME,
                           X_OWNER                      => NULL,
                           X_UNENCRYPTED_PASSWORD       => NULL,
                           X_SESSION_NUMBER             => 0,
                           X_START_DATE                 => NULL,
                           X_END_DATE                   => NULL,
                           X_LAST_LOGON_DATE            => NULL,
                           X_DESCRIPTION                => NULL,
                           X_PASSWORD_DATE              => NULL,
                           X_PASSWORD_ACCESSES_LEFT     => NULL,
                           X_PASSWORD_LIFESPAN_ACCESSES => NULL,
                           X_PASSWORD_LIFESPAN_DAYS     => NULL,
                           X_EMPLOYEE_ID                => NULL,
                           X_EMAIL_ADDRESS              => V_EMAIL_ADDRESS,
                           X_FAX                        => NULL,
                           X_CUSTOMER_ID                => NULL,
                           X_SUPPLIER_ID                => NULL,
                           X_USER_GUID                  => NULL,
                           X_CHANGE_SOURCE              => NULL);
   COMMIT;
           DBMS_OUTPUT.PUT_LINE('USER '|| V_USER_NAME|| ' IS UPDATED SUCCESSFULLY');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR ENCOUNTERED WHILE UPDATING THE USER AND THE ERROR IS ' || SQLERRM);
END;

2.     Responsibility

I.          API TO ADD A RESPONSIBILITY TO A USER

/*=======================================================
-- API TO ADD A RESPONSIBILITY TO A USER
-- FND_USER_PKG.ADDRESP
==========================================================*/

---------SCRIPT----------

SET SERVEROUTPUT ON
DECLARE
   LV_USER_NAME        VARCHAR2 (20) := 'SYSCNTEST';
   LV_REQ_RESP_KEY     VARCHAR2 (50) := 'APPLICATION_DEVELOPER';
   LV_DESCRIPTION      VARCHAR2 (100):= 'ADDING RESPONSIBILITY TO USER USING API';
   LV_REQ_RESP_NAME    VARCHAR2 (200);
   LV_APPL_SHRT_NAME   VARCHAR2 (20);
   LV_APPL_NAME        VARCHAR2 (50);
   LV_RESP_KEY         VARCHAR2 (50);
 
BEGIN
 
   SELECT FAV.APPLICATION_SHORT_NAME,
          FAV.APPLICATION_NAME,
          FRV.RESPONSIBILITY_NAME
     INTO LV_APPL_SHRT_NAME,
          LV_APPL_NAME,
          LV_REQ_RESP_NAME
     FROM FND_APPLICATION_VL FAV,
          FND_RESPONSIBILITY_VL FRV
    WHERE FRV.APPLICATION_ID = FAV.APPLICATION_ID
      AND FRV.RESPONSIBILITY_KEY = LV_REQ_RESP_KEY;
 
   FND_USER_PKG.ADDRESP (USERNAME         => LV_USER_NAME,
                         RESP_APP         => LV_APPL_SHRT_NAME,
                         RESP_KEY         => LV_REQ_RESP_KEY,
                         SECURITY_GROUP   => 'STANDARD',
                         DESCRIPTION      => LV_DESCRIPTION,
                         START_DATE       => SYSDATE,
                         END_DATE         => NULL
                                              );
   COMMIT;
 
   DBMS_OUTPUT.PUT_LINE ('THE RESPONSIBILITY ' || LV_REQ_RESP_NAME || ' IS ADDED TO THE USER ' || LV_USER_NAME);
 
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('RESPONSIBILITY IS NOT ADDED DUE TO ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));
      ROLLBACK;
END;

Assigned application developer responsibility to the user syscntest.


   II.          API TO REMOVE A RESPONSIBILITY FROM USER
/*=======================================================
-- API TO REMOVE A RESPONSIBILITY FROM USER
-- FND_USER_PKG.DELRESP
==========================================================*/

DECLARE
   V_USER_NAME             VARCHAR2 (100) := 'SYSCNTEST';
   V_RESPONSIBILITY_NAME   VARCHAR2 (100) := 'APPLICATION DEVELOPER';
   V_APPLICATION_NAME      VARCHAR2 (100) := NULL;
   V_RESPONSIBILITY_KEY    VARCHAR2 (100) := NULL;
   V_SECURITY_GROUP        VARCHAR2 (100) := NULL;
BEGIN
   SELECT FA.APPLICATION_SHORT_NAME,
          FR.RESPONSIBILITY_KEY,
          FRG.SECURITY_GROUP_KEY
     INTO V_APPLICATION_NAME,
          V_RESPONSIBILITY_KEY,
          V_SECURITY_GROUP
     FROM FND_RESPONSIBILITY FR,
          FND_APPLICATION FA,
          FND_SECURITY_GROUPS FRG,
          FND_RESPONSIBILITY_TL FRT
    WHERE FR.APPLICATION_ID = FA.APPLICATION_ID
      AND FR.DATA_GROUP_ID = FRG.SECURITY_GROUP_ID
      AND FR.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
      AND FRT.LANGUAGE = USERENV ('LANG')
      AND FRT.RESPONSIBILITY_NAME = V_RESPONSIBILITY_NAME;
 
   FND_USER_PKG.DELRESP (USERNAME         => V_USER_NAME,
                         RESP_APP         => V_APPLICATION_NAME,
                         RESP_KEY         => V_RESPONSIBILITY_KEY,
                         SECURITY_GROUP   => V_SECURITY_GROUP);
 
  
DBMS_OUTPUT.PUT_LINE(   'RESPONSIBLITY '
                        || V_RESPONSIBILITY_NAME
                        || ' IS REMOVED FROM THE USER '
                        || V_USER_NAME
                        || ' SUCCESSFULLY');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE('ERROR ENCOUNTERED WHILE DELETING RESPONSIBILTY FROM THE USER AND THE ERROR IS ' || SQLERRM);
COMMIT;
END;

Removed application developer responsibility to the user syscntest.



Query to get active responsibilities assigned to a particular user:


SELECT FU.USER_ID,
       FU.USER_NAME,
       FU.DESCRIPTION,
       FRT.RESPONSIBILITY_ID RESPONSIBILITY_ID,
       FRT.RESPONSIBILITY_NAME RESPONSIBILITY,
       FAT.APPLICATION_ID APPLICATION_ID,
       FAT.APPLICATION_NAME APPLICATION_NAME
 FROM FND_USER FU,
      FND_USER_RESP_GROUPS_DIRECT FURG,
      FND_RESPONSIBILITY_TL FRT,
      FND_APPLICATION_TL FAT
WHERE 1=1
  AND FU.USER_ID = FURG.USER_ID
  AND FURG.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
  AND FRT.APPLICATION_ID = FAT.APPLICATION_ID
  AND FAT.LANGUAGE = FRT.LANGUAGE
  AND FRT.LANGUAGE = 'US'
  AND NVL(TRUNC(FURG.END_DATE),SYSDATE+1) >= SYSDATE
  AND FU.USER_NAME LIKE '<USER_NAME>'    --'SYSCNTEST'
ORDER BY FU.USER_NAME;

 
-->TO CHECK ACTIVE RESPONSIBILITIES OF THE USER:

SELECT b.user_name,
         b.user_id,
         b.start_date,
         b.end_date,       
         b.description,
         ftl.application_name,
         c.responsibility_name,
-->TO CHECK THE ACTIVE RESPONSBILIOTS OF THE USER
         a.start_date responsibility_start_date,
         a.end_date responsibility_end_date,
         a.description
    FROM fnd_user_resp_groups_direct a,
         fnd_user b,
         fnd_responsibility_vl c,       
         FND_APPLICATION FA,
         fnd_application_tl ftl
   WHERE a.user_id = b.user_id
     AND a.responsibility_id = c.responsibility_id
     AND fa.application_id = a.responsibility_application_id
     AND fa.application_id = ftl.application_id
     AND ftl.language   = USERENV('LANG')
     AND SYSDATE BETWEEN a.start_date AND NVL(a.end_date,SYSDATE + 1)
     AND SYSDATE BETWEEN b.start_date AND NVL(b.end_date,SYSDATE + 1)
     and sysdate between c.start_date and nvl(c.end_date,sysdate + 1)
     and UPPER(b.user_name) = UPPER('<USER_NAME>');

3.     Concurrent Program

Navigation: Application Developer -> Concurrent -> Executable

I. Create an Executable

Types of execution methods:

/*=======================================================
-- API to create executable
-- apps.fnd_program.executable
==========================================================*/


DECLARE
   L_EXECUTABLE            VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
   L_APPLICATION           VARCHAR2 (200) := 'FND';
   L_SHORT_NAME            VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
   L_DESCRIPTION           VARCHAR2 (200) := 'TEST SCRIPT FOR CREATING EXECUTABLE                                               FROM BACKEND';
   L_EXECUTION_METHOD      VARCHAR2 (200) := 'PL/SQL STORED PROCEDURE';
   L_EXECUTION_FILE_NAME   VARCHAR2 (200) :='XXX_EMP_INSERT_UPDATE.XXX_EMP_INSERT';
   L_SUBROUTINE_NAME       VARCHAR2 (200) := NULL;
   L_ICON_NAME             VARCHAR2 (200) := NULL;
   L_LANGUAGE_CODE         VARCHAR2 (200) := 'US';
   L_EXECUTION_FILE_PATH   VARCHAR2 (200) := NULL;
   L_CHECK                 VARCHAR2 (2);
BEGIN
 
 --CALLING API TO CREATE EXECUTABLE
 
   APPS.FND_PROGRAM.EXECUTABLE
                  (EXECUTABLE               => L_EXECUTABLE,
                   APPLICATION              => L_APPLICATION,
                   SHORT_NAME               => L_SHORT_NAME,
                   DESCRIPTION              => L_DESCRIPTION,
                   EXECUTION_METHOD         => L_EXECUTION_METHOD,
                   EXECUTION_FILE_NAME      => L_EXECUTION_FILE_NAME,
                   SUBROUTINE_NAME          => L_SUBROUTINE_NAME,
                   ICON_NAME                => L_ICON_NAME,
                   LANGUAGE_CODE            => L_LANGUAGE_CODE,
                   EXECUTION_FILE_PATH      => L_EXECUTION_FILE_PATH
                               );
   COMMIT;
   BEGIN
   --TO CHECK WHETHER EXECUTABLE IS CREATED OR NOT
      SELECT 'Y'
        INTO L_CHECK
        FROM FND_EXECUTABLES
       WHERE EXECUTABLE_NAME = L_EXECUTABLE;
       DBMS_OUTPUT.PUT_LINE ('EXECUTABLE CREATED SUCCESSFULLY');
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.PUT_LINE ('EXECUTABLE REGISTRATION FAILED');
   END;
END;

Effected Table:


SELECT *
FROM   FND_EXECUTABLES
WHERE  EXECUTABLE_NAME LIKE 'XX_EMP_INSERT_EXE';

II. Create a Concurrent Program for above Executable.

Navigation: Application Developer à Concurrent à Program

Define a concurrent program with the chosen executable created.

   I.          API TO CREATE CONCURRENT PROGRAM

/*=======================================================
-- API TO CREATE CONCURRENT PROGRAM
-- FND_PROGRAM.REGISTER
==========================================================*/

---- SCRIPT

DECLARE
V_NAME VARCHAR2 (50) := 'XXX EMPLOYEE INSERT PROGRAM';
V_SHORT_NAME VARCHAR2 (50) := 'XX_EMP_INSERT_EXE';
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID       => 1318,
                            RESP_ID       => 21623,
                            RESP_APPL_ID  => 660
   );
 
FND_PROGRAM.REGISTER (PROGRAM                   => V_NAME,
                       APPLICATION              => 'FND',
                       ENABLED                  => 'Y',
                       SHORT_NAME               => V_SHORT_NAME,
                       EXECUTABLE_SHORT_NAME    => V_SHORT_NAME,
                       EXECUTABLE_APPLICATION   => 'FND',
                       STYLE                    => 'A4',
                       OUTPUT_TYPE              => 'TEXT',
                       USE_IN_SRS               => 'Y'
                       );
 COMMIT;
 DBMS_OUTPUT.PUT_LINE ('SUCCEEFULLY CREATED CONCURRENT PROGRAM '|| V_SHORT_NAME);
END;

Effected Table:


SELECT *
FROM   FND_CONCURRENT_PROGRAMS_TL;

Click on parameters to create a parameter in concurrent program.



       I.          API TO CREATE PARAMETERS IN THE CONCURRENT PROGRAM

/*=======================================================
-- API TO CREATE PARAMETERS IN THE CONCURRENT PROGRAM
-- FND_PROGRAM.PARAMETERS
==========================================================*/

---- SCRIPT FOR NUMBER DATA TYPE
SET SERVEROUTPUT ON
DECLARE
  L_PROGRAM_SHORT_NAME      VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
  L_APPLICATION             VARCHAR2 (200) := 'FND';
  L_SEQUENCE                NUMBER         := 10;
  L_PARAMETER               VARCHAR2 (200) := 'STUDENT ID';
  L_DESCRIPTION             VARCHAR2 (200) := 'STUDENT ID';
  L_ENABLED                 VARCHAR2 (200) := 'Y';
  L_VALUE_SET               VARCHAR2 (200) := 'NUMBER';
  L_DEFAULT_TYPE            VARCHAR2 (200) := NULL;
  L_DEFAULT_VALUE           VARCHAR2 (200) := NULL;
  L_REQUIRED                VARCHAR2 (200) :='N';
  L_ENABLE_SECURITY         VARCHAR2 (200) :='N';
  L_RANGE                   VARCHAR2 (200) := NULL;
  L_DISPLAY                 VARCHAR2 (200) := 'Y';
  L_DISPLAY_SIZE            NUMBER         := 50;
  L_DESCRIPTION_SIZE        NUMBER         := 50;
  L_CONCATENATED_DESCR_SIZE NUMBER         := 50;
  L_PROMPT                  VARCHAR2 (200) := 'STUDENT ID';
  L_TOKEN                   VARCHAR2 (200) := NULL;
  L_CD_PARAMETER            VARCHAR2 (200) := NULL;
  L_CHECK                   VARCHAR2 (2);
BEGIN
  --CALLING API TO CREATE A PARAMETER FOR A CONCURRENT PROGRAM DEFINITION
   APPS.FND_PROGRAM.PARAMETER
           (PROGRAM_SHORT_NAME            => L_PROGRAM_SHORT_NAME,
            APPLICATION                   => L_APPLICATION,
            SEQUENCE                      => L_SEQUENCE,
            PARAMETER                     => L_PARAMETER,
            DESCRIPTION                   => L_DESCRIPTION,
            ENABLED                       => L_ENABLED,
            VALUE_SET                     => L_VALUE_SET,
            DEFAULT_TYPE                  => L_DEFAULT_TYPE,
            DEFAULT_VALUE                 => L_DEFAULT_VALUE,
            REQUIRED                      => L_REQUIRED,
            ENABLE_SECURITY               => L_ENABLE_SECURITY,
            RANGE                         => L_RANGE,
            DISPLAY                       => L_DISPLAY,
            DISPLAY_SIZE                  => L_DISPLAY_SIZE,
            DESCRIPTION_SIZE              => L_DESCRIPTION_SIZE,
            CONCATENATED_DESCRIPTION_SIZE => L_CONCATENATED_DESCR_SIZE,
            PROMPT                        => L_PROMPT,
            TOKEN                         => L_TOKEN,
            CD_PARAMETER                  => L_CD_PARAMETER
           ); 
 
  BEGIN
    --TO CHECK WHETHER A PARAMETER IS ASSIGNED TO A CONCURRENT PROGRAM OR NOT
     SELECT 'Y'
       INTO L_CHECK
       FROM FND_DESCR_FLEX_COLUMN_USAGES
      WHERE DESCRIPTIVE_FLEXFIELD_NAME = '$SRS$.'
      || L_PROGRAM_SHORT_NAME
    AND END_USER_COLUMN_NAME = L_PARAMETER;
    --
    DBMS_OUTPUT.PUT_LINE ('CONCURRENT PROGRAM PARAMETER REGISTERED SUCCESSFULLY');
COMMIT;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('CONCURRENT PROGRAM PARAMETER REGISTRATION FAILED');
  END;
END;
 

/*=======================================================
-- API TO DELETE PARAMETERS TO THE CONCURRENT PROGRAM
-- FND_PROGRAM.DELETE_PARAMETER
==========================================================*/

DECLARE
   V_PROGRAM_SHORT_NAME   VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';--CONCURRENT PROGRAM SHORT NAME;
   V_APPLICATION          VARCHAR2 (200) := 'FND';-- APPLICATION SHORTNAME;
   V_PARAMETER            VARCHAR2 (200) := 'STUDENT ID';    -- PARAMETER NAME;
BEGIN
   APPS.FND_PROGRAM.DELETE_PARAMETER
                   (PROGRAM_SHORT_NAME      => V_PROGRAM_SHORT_NAME,
                    APPLICATION             => V_APPLICATION,
                    PARAMETER               => V_PARAMETER
                   );
   COMMIT;
END;

 
/*=======================================================
-- API DELETE CONCURRENT PROGRAM AND EXECUTABLE
-- FND_PROGRAM.DELETE_PROGRAM
==========================================================*/

BEGIN
FND_PROGRAM.DELETE_PROGRAM('CONCURRENT_PROGRAM_NAME','APPLICATION_SHORT_NAME');
FND_PROGRAM.DELETE_EXECUTABLE('CONCURRENT_PROGRAM_NAME','APPLICATION_SHORT_NAME');
COMMIT;
END;

III. Add Concurrent Program to Request group.

Navigation: System Administration -> Security -> Responsibility -> Define

Query responsibility name and copy request group name.

    Copy the Request Group Name.
    Now,

Navigation: System Administration -> Security -> Responsibility -> Request

Query the request group and add concurrent program to the request group


Effected Table:


SELECT *
FROM   FND_REQUEST_GROUPS
WHERE REQUEST_GROUP_NAME LIKE 'SYSTEM ADMIN% REP%';

       I.          API TO ADD CONCURRENT PROGRAM TO THE REQUEST GROUP

/*=======================================================
-- API ADD CONCURRENT PROGRAM TO THE REQUEST GROUP
-- FND_PROGRAM.ADD_TO_GROUP
==========================================================*/


SET SERVEROUTPUT ON
DECLARE
  L_PROGRAM_SHORT_NAME  VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
  L_PROGRAM_APPLICATION VARCHAR2 (200) := 'FND';
  L_REQUEST_GROUP       VARCHAR2 (200) := 'SYSTEM ADMINISTRATOR REPORTS';
  L_GROUP_APPLICATION   VARCHAR2 (200) := 'APPLICATION OBJECT LIBRARY';
  --
BEGIN
  --CALLING API TO ASSIGN CONCURRENT PROGRAM TO A REQEST GROUP
   APPS.FND_PROGRAM.ADD_TO_GROUP
                  (PROGRAM_SHORT_NAME  => L_PROGRAM_SHORT_NAME,
                   PROGRAM_APPLICATION => L_PROGRAM_APPLICATION,
                   REQUEST_GROUP       => L_REQUEST_GROUP,
                   GROUP_APPLICATION   => L_GROUP_APPLICATION                           
                  ); 
  COMMIT;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('ADDING CONCURRENT PROGRAM TO REQUEST GROUP FAILED');
END;
 

   API REMOVE CONCURRENT PROGRAM TO THE REQUEST GROUP

/*=======================================================
-- API REMOVE CONCURRENT PROGRAM TO THE REQUEST GROUP
-- FND_PROGRAM. REMOVE_FROM_GROUP
==========================================================*/


SET SERVEROUTPUT ON
DECLARE
  L_PROGRAM_SHORT_NAME  VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
  L_PROGRAM_APPLICATION VARCHAR2 (200) := 'FND';
  L_REQUEST_GROUP       VARCHAR2 (200) := 'SYSTEM ADMINISTRATOR REPORTS';
  L_GROUP_APPLICATION   VARCHAR2 (200) := 'APPLICATION OBJECT LIBRARY';
  --
BEGIN
  --CALLING API TO ASSIGN CONCURRENT PROGRAM TO A REQEST GROUP
   APPS.FND_PROGRAM.REMOVE_FROM_GROUP
                (PROGRAM_SHORT_NAME  => L_PROGRAM_SHORT_NAME,
                 PROGRAM_APPLICATION => L_PROGRAM_APPLICATION,
                 REQUEST_GROUP       => L_REQUEST_GROUP,
                 GROUP_APPLICATION   => L_GROUP_APPLICATION                    
                ); 
  COMMIT;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('ADDING CONCURRENT PROGRAM TO REQUEST GROUP FAILED');
END;

       API TO SUBMIT CONCURRENT PROGRAM
/*=======================================================
-- API TO SUBMIT CONCURRENT PROGRAM
-- FND_REQUEST.SUBMIT_REQUEST
==========================================================*/


DECLARE
   L_REQUEST_ID NUMBER;
BEGIN
   FND_GLOBAL.APPS_INITIALIZE (USER_ID      =>1318
                              ,RESP_ID      =>20420
                              ,RESP_APPL_ID =>1);
 
   L_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST
( APPLICATION => 'FND'
                   ,PROGRAM     => 'XXX_STUDENT_INSERT'
                   ,DESCRIPTION => NULL
                   ,START_TIME  => SYSDATE
                   ,SUB_REQUEST => FALSE
                   ,ARGUMENT1   => '140'
                   ,ARGUMENT2   => 'JK'
                   ,ARGUMENT3   => 'EEE'
                   ,ARGUMENT4   => '12');
   COMMIT;
  
   IF L_REQUEST_ID = 0 THEN
      DBMS_OUTPUT.PUT_LINE('REQUEST NOT SUBMITTED ERROR '|| FND_MESSAGE.GET);
   ELSE
      DBMS_OUTPUT.PUT_LINE('REQUEST SUBMITTED SUCCESSFULLY REQUEST ID ' || L_REQUEST_ID);
   END IF;
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('UNEXPECTED ERRRO ' || SQLERRM);  
END;

-- Query to get responsibilities for the concurrent program
 

SELECT FCPT.USER_CONCURRENT_PROGRAM_NAME,
       FCP.CONCURRENT_PROGRAM_NAME,
       FAT.APPLICATION_NAME,
       FRG.REQUEST_GROUP_NAME,
       FNRT.RESPONSIBILITY_NAME
  FROM APPS.FND_CONCURRENT_PROGRAMS FCP,
       APPS.FND_CONCURRENT_PROGRAMS_TL FCPT,
       APPS.FND_APPLICATION_TL FAT,
       APPS.FND_REQUEST_GROUP_UNITS FRGU,
       APPS.FND_REQUEST_GROUPS FRG,
       APPS.FND_RESPONSIBILITY FNR,
       APPS.FND_RESPONSIBILITY_TL FNRT
 WHERE 1=1
   AND FCP.CONCURRENT_PROGRAM_ID  = FCPT.CONCURRENT_PROGRAM_ID
   AND FAT.APPLICATION_ID         = FCP.APPLICATION_ID
   AND FRGU.REQUEST_UNIT_ID       = FCP.CONCURRENT_PROGRAM_ID
   AND FRGU.UNIT_APPLICATION_ID   = FCP.APPLICATION_ID
   AND FNR.REQUEST_GROUP_ID       = FRG.REQUEST_GROUP_ID
   AND FRG.REQUEST_GROUP_ID       = FRGU.REQUEST_GROUP_ID
   AND FNRT.RESPONSIBILITY_ID     = FNR.RESPONSIBILITY_ID
   AND FRG.APPLICATION_ID         = FAT.APPLICATION_ID
   AND FAT.LANGUAGE = 'US'
   AND FNRT.LANGUAGE = 'US'
   AND FCP.CONCURRENT_PROGRAM_NAME LIKE 'XX_EMP_INSERT_EXE';

--> QUERY FOR CONCURRENT PROGRAM NAME, CONCURRENT PROGRAM NAME, APPLICATION NAME AND RESPONSIBILITY NAME:

 

SELECT FCPT.USER_CONCURRENT_PROGRAM_NAME,
       FCP.CONCURRENT_PROGRAM_NAME,
       FAT.APPLICATION_NAME,
       FET.EXECUTABLE_NAME,
       FATE.APPLICATION_NAME EXE_APPLICATION,
       FLV.MEANING           EXECUTION_METHOD,
       FET.EXECUTION_FILE_NAME,
       FCP.ENABLE_TRACE
  FROM FND_CONCURRENT_PROGRAMS_TL FCPT,
       FND_CONCURRENT_PROGRAMS    FCP,
       FND_APPLICATION_TL         FAT,
       FND_EXECUTABLES            FET,
       FND_APPLICATION_TL         FATE,
       FND_LOOKUP_VALUES          FLV
 WHERE FCPT.CONCURRENT_PROGRAM_ID   = FCP.CONCURRENT_PROGRAM_ID
   AND FCPT.APPLICATION_ID          = FCP.APPLICATION_ID
   AND FCP.APPLICATION_ID           = FAT.APPLICATION_ID
   AND FCPT.APPLICATION_ID          = FAT.APPLICATION_ID
   AND FCP.EXECUTABLE_ID            = FET.EXECUTABLE_ID
   AND FCP.EXECUTABLE_APPLICATION_ID = FET.APPLICATION_ID
   AND FET.APPLICATION_ID           = FATE.APPLICATION_ID
   AND FLV.LOOKUP_TYPE              = 'CP_EXECUTION_METHOD_CODE'
   AND FLV.LOOKUP_CODE              = FET.EXECUTION_METHOD_CODE
   AND FCPT.USER_CONCURRENT_PROGRAM_NAME = '<CONCURRENT PROGRAM NAME>';





4.     Lookups

Navigation: Application Developer à Application à Lookups à Common

To Create a lookup, fill the all mandatory fields and click on save.

Query to find Lookup is created or not.

SELECT *

FROM   FND_LOOKUP_TYPES

WHERE  LOOKUP_TYPE LIKE 'XXX_REPONSIBILITY_NAME_LOOKUP';


Add values to the existing Lookup.


Query to find the values of an Lookup.

SELECT *

FROM   FND_LOOKUP_VALUES

WHERE  LOOKUP_TYPE LIKE 'XXX_REPONSIBILITY_NAME_LOOKUP';



  API TO CREATE A LOKKUP TYPE:

DECLARE
   V_ROWID    VARCHAR2 (400);
BEGIN
   FND_LOOKUP_TYPES_PKG.INSERT_ROW
                  (X_ROWID                    => V_ROWID,
                   X_LOOKUP_TYPE              => 'XXX_TEST_LOOKUP',
                   X_SECURITY_GROUP_ID        => 0,
                   X_VIEW_APPLICATION_ID      => 3,
                   X_APPLICATION_ID           => 0,
                   X_CUSTOMIZATION_LEVEL      => 'U',
                   X_MEANING                  => 'XXX_TEST_LOOKUP',
                   X_DESCRIPTION              => 'XXX_TEST_LOOKUP',
                   X_CREATION_DATE            => SYSDATE,
                   X_CREATED_BY               => 0,
                   X_LAST_UPDATE_DATE         => SYSDATE,
                   X_LAST_UPDATED_BY          => 0,
                   X_LAST_UPDATE_LOGIN        => -1);
                                   
   DBMS_OUTPUT.PUT_LINE (V_ROWID);
   COMMIT;
END;
/
Here, X_VIEW_APPLICATION_ID = "3", means this lookup type is a Common Lookup

SELECT *

FROM  FND_LOOKUP_TYPES

WHERE LOOKUP_TYPE LIKE 'XXX_TEST_LOOKUP';



  API to Add values to a existing lookup:


DECLARE
   V_ROWID    VARCHAR2 (400);
BEGIN
FND_LOOKUP_VALUES_PKG.INSERT_ROW (
               X_ROWID                 => V_ROWID,
               X_LOOKUP_TYPE           => 'XXX_TEST_LOOKUP',
               X_SECURITY_GROUP_ID     => 0,
               X_VIEW_APPLICATION_ID   => 3,
               X_LOOKUP_CODE           => 'CODE1',
               X_TAG                   => NULL,
               X_ATTRIBUTE_CATEGORY    => NULL,
               X_ATTRIBUTE1            => NULL,
               X_ATTRIBUTE2            => NULL,
               X_ATTRIBUTE3            => NULL,
               X_ATTRIBUTE4            => NULL,
               X_ENABLED_FLAG          => 'Y',
               X_START_DATE_ACTIVE     => SYSDATE,
               X_END_DATE_ACTIVE       => NULL,
               X_TERRITORY_CODE        => NULL,
               X_ATTRIBUTE5            => NULL,
               X_ATTRIBUTE6            => NULL,
               X_ATTRIBUTE7            => NULL,
               X_ATTRIBUTE8            => NULL,
               X_ATTRIBUTE9            => NULL,
               X_ATTRIBUTE10           => NULL,
               X_ATTRIBUTE11           => NULL,
               X_ATTRIBUTE12           => NULL,
               X_ATTRIBUTE13           => NULL,
               X_ATTRIBUTE14           => NULL,
               X_ATTRIBUTE15           => NULL,
               X_MEANING               => 'XXX_TEST_LOOKUP',
               X_DESCRIPTION           => 'XXX_TEST_LOOKUP',
               X_CREATION_DATE         => SYSDATE,
               X_CREATED_BY            => 0,
               X_LAST_UPDATE_DATE      => SYSDATE,
               X_LAST_UPDATED_BY       => 0,
               X_LAST_UPDATE_LOGIN     => -1
            );
       
   COMMIT;
END;
/


SELECT *
FROM   FND_LOOKUP_VALUES
WHERE  LOOKUP_TYPE LIKE 'XXX_TEST_LOOKUP';



4.    Values Sets

A value set is used in Oracle Applications to restrict the values entered by a user. 
For example, when submitting a concurrent program, we would like user to enter only valid values in the parameter. 

Uses: In a concurrent program, we may want users to enter only number between 1 and 100 for a particular parameter. 
In a concurrent program, we may want users to enter only Yes or No for a particular parameter.

Types of Value Sets: There are total 8 types of value sets there in Oracle EBS, They are
  1.     Dependent
  2.     Independent
  3.     None
  4.     Pair
  5.     Special
  6.     Table
  7.     Translatable Independent
  8.     Translatable Dependent    
Here is an example for Creation of Independent Value Set.

Navigation: Application Developer à Application à Validation à Set


Here, Mandatory fields are
  • Value set Name = User Defined Name
  • List Type           = List of Values , Long List of Values, Pop List
  • Security Type    = No Security, Hierarchical Security, Non Hierarchical Security
  • Format Type      = Char, Date, Date time, Number, Standard date, Standard Date time, time
  • Maximum Size  = Not more then 240
  • Validation Type = Refer Above.
After creating value set, we need to add values to the Value Set.

Navigation: Application Developer à Application à Validation à Values


Enter Value Set name and Click on Find.


Now we need to create a Dependent value set for above Independent Value set.


Now we need to Add values to the Dependent values set based on the dependent value set values.

Here 
Name = Dependent Value Set.
Independent Value = One value from the Independent value set
then Click on Find.


Do this for all values in the Independent value set values.





  Query to get value set values:

 SELECT FFVS.FLEX_VALUE_SET_ID,
       FFVS.FLEX_VALUE_SET_NAME,
       FFVS.DESCRIPTION SET_DESCRIPTION,
       FFVS.VALIDATION_TYPE,
       FFV.FLEX_VALUE,
       FFVT.DESCRIPTION VALUE_DESCRIPTION,
       FFV.ENABLED_FLAG,
       FFV.LAST_UPDATE_DATE,
       FFV.LAST_UPDATED_BY,
       FFV.ATTRIBUTE1,
       FFV.ATTRIBUTE2,
       FFV.ATTRIBUTE3 --INCLUDE ATTRIBUTE VALUES BASED ON DFF SEGMENTS
  FROM FND_FLEX_VALUE_SETS FFVS,
       FND_FLEX_VALUES FFV,
       FND_FLEX_VALUES_TL FFVT
 WHERE FFVS.FLEX_VALUE_SET_ID   = FFV.FLEX_VALUE_SET_ID
   AND FFV.FLEX_VALUE_ID        = FFVT.FLEX_VALUE_ID
   AND FFVT.LANGUAGE            = USERENV('LANG')
   AND FLEX_VALUE_SET_NAME LIKE 'XX_COUNTRY_SET'
 ORDER BY FLEX_VALUE


No comments:

Post a Comment