Script to update the Menu Entries

DECLARE
     G_LAST_UPDATE_DATE DATE := SYSDATE;
     G_LAST_UPDATED_BY   NUMBER ;
     G_LAST_UPDATE_LOGIN NUMBER ;
     G_APPLICATION_ID    NUMBER ;
     G_RESPONSIBILITY_ID NUMBER ;
     L_FUNCTION_ID   NUMBER;
   
    CURSOR C_UPDATE_MENU
    IS
SELECT FMEV.MENU_ID,
       FMEV.ENTRY_SEQUENCE,
       FMEV.SUB_MENU_ID,
       FMEV.GRANT_FLAG,
       FMEV.PROMPT,
       FMEV.DESCRIPTION,
       FM.MENU_NAME
 FROM  FND_MENU_ENTRIES_VL FMEV,
       FND_FORM_FUNCTIONS FF,
       FND_MENUS FM
 WHERE FMEV.MENU_ID = FM.MENU_ID
   AND FMEV.FUNCTION_ID = FF.FUNCTION_ID
   AND FF.FUNCTION_NAME = 'BNE_CREATE_DOCUMENT'
   AND FM.MENU_NAME LIKE 'XX%';
 
  BEGIN
 
    SELECT USER_ID
      INTO G_LAST_UPDATED_BY
      FROM FND_USER
     WHERE USER_NAME      = 'ORACLE12.1.0';
 
  G_LAST_UPDATE_LOGIN := G_LAST_UPDATED_BY;
 
    SELECT FUNCTION_ID
      INTO L_FUNCTION_ID
      FROM FND_FORM_FUNCTIONS
     WHERE FUNCTION_NAME = 'BNE_ADI_CREATE_DOCUMENT';
   
    FOR C_UPDATE_MENU_REC IN C_UPDATE_MENU
    LOOP
      BEGIN
        FND_MENU_ENTRIES_PKG.UPDATE_ROW( X_MENU_ID               =>   C_UPDATE_MENU_REC.MENU_ID,
                                         X_ENTRY_SEQUENCE        =>   C_UPDATE_MENU_REC.ENTRY_SEQUENCE,
                                         X_SUB_MENU_ID           =>   C_UPDATE_MENU_REC.SUB_MENU_ID,
                                         X_FUNCTION_ID           =>   L_FUNCTION_ID,
                                         X_GRANT_FLAG            =>   C_UPDATE_MENU_REC.GRANT_FLAG,
                                         X_PROMPT                =>   C_UPDATE_MENU_REC.PROMPT,
                                         X_DESCRIPTION           =>   C_UPDATE_MENU_REC.DESCRIPTION,
                                         X_LAST_UPDATE_DATE      =>   G_LAST_UPDATE_DATE,
                                         X_LAST_UPDATED_BY       =>   G_LAST_UPDATED_BY,
                                         X_LAST_UPDATE_LOGIN     =>   G_LAST_UPDATE_LOGIN
                                       );
      EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('Failed the update Menu '||C_UPDATE_MENU_REC.MENU_NAME);
      END;
    END LOOP;
    COMMIT;
  --  Compiling Security Profile
  DECLARE
  L_REQUEST_ID  NUMBER;
  BEGIN
      SELECT APPLICATION_ID,RESPONSIBILITY_ID
      INTO G_APPLICATION_ID,G_RESPONSIBILITY_ID
      FROM FND_RESPONSIBILITY_TL
     WHERE RESPONSIBILITY_NAME = 'System Administrator';

 Fnd_Global.apps_initialize(G_LAST_UPDATED_BY,G_RESPONSIBILITY_ID,G_APPLICATION_ID);

     L_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(
                                           APPLICATION => 'FND',
                                           PROGRAM     => 'FNDSCMPI',
                                           ARGUMENT1   => 'No');
    DBMS_OUTPUT.PUT_LINE ('Request ID: '||L_REQUEST_ID);                                      
  EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('Error While compiling Security Profile');
  END;
 
  END; 
/
 

Script to Update the form Function

DECLARE
  G_LAST_UPDATE_DATE DATE := SYSDATE;
  G_LAST_UPDATED_BY   NUMBER ;
  G_LAST_UPDATE_LOGIN NUMBER ;
   
    CURSOR C_UPDATE_FUNCTION
    IS
      SELECT FUNCTION_ID,
        WEB_HOST_NAME,
        WEB_AGENT_NAME,
        WEB_HTML_CALL,
        WEB_ENCRYPT_PARAMETERS,
        WEB_SECURED,
        WEB_ICON,
        OBJECT_ID,
        REGION_APPLICATION_ID,
        FORM_ID,
        REGION_CODE,
        FUNCTION_NAME,
        APPLICATION_ID,
        PARAMETERS,
        TYPE,
        USER_FUNCTION_NAME,
        DESCRIPTION,
        MAINTENANCE_MODE_SUPPORT,
        CONTEXT_DEPENDENCE,
        JRAD_REF_PATH
      FROM FND_FORM_FUNCTIONS_VL
      WHERE FUNCTION_NAME LIKE 'XX%';
  BEGIN
  SELECT USER_ID
  INTO G_LAST_UPDATED_BY
  FROM FND_USER
  WHERE USER_NAME      = 'ORACLE12.1.0';
  G_LAST_UPDATE_LOGIN := G_LAST_UPDATED_BY;
 
 
    FOR C_UPDATE_FUNCTION_REC IN C_UPDATE_FUNCTION
    LOOP
      BEGIN
        FND_FORM_FUNCTIONS_PKG.UPDATE_ROW ( X_FUNCTION_ID              =>  C_UPDATE_FUNCTION_REC.FUNCTION_ID,
                                               X_WEB_HOST_NAME            =>  C_UPDATE_FUNCTION_REC.WEB_HOST_NAME,
                                            X_WEB_AGENT_NAME           =>  C_UPDATE_FUNCTION_REC.WEB_AGENT_NAME,
                                            X_WEB_HTML_CALL            =>  'BneApplicationService',  
                                            X_WEB_ENCRYPT_PARAMETERS   =>  C_UPDATE_FUNCTION_REC.WEB_ENCRYPT_PARAMETERS,
                                            X_WEB_SECURED              =>  C_UPDATE_FUNCTION_REC.WEB_SECURED,
                                            X_WEB_ICON                 =>  C_UPDATE_FUNCTION_REC.WEB_ICON,
                                            X_OBJECT_ID                =>  C_UPDATE_FUNCTION_REC.OBJECT_ID,
                                            X_REGION_APPLICATION_ID    =>  C_UPDATE_FUNCTION_REC.REGION_APPLICATION_ID,
                                            X_REGION_CODE              =>  C_UPDATE_FUNCTION_REC.REGION_CODE,
                                            X_FUNCTION_NAME            =>  C_UPDATE_FUNCTION_REC.FUNCTION_NAME,
                                            X_APPLICATION_ID           =>  C_UPDATE_FUNCTION_REC.APPLICATION_ID,
                                            X_FORM_ID                  =>  C_UPDATE_FUNCTION_REC.FORM_ID,
                                            X_PARAMETERS               =>  C_UPDATE_FUNCTION_REC.PARAMETERS,
                                            X_TYPE                     =>  C_UPDATE_FUNCTION_REC.TYPE,
                                            X_USER_FUNCTION_NAME       =>  C_UPDATE_FUNCTION_REC.USER_FUNCTION_NAME,
                                            X_DESCRIPTION              =>  C_UPDATE_FUNCTION_REC.DESCRIPTION,
                                            X_LAST_UPDATE_DATE         =>  G_LAST_UPDATE_DATE,
                                            X_LAST_UPDATED_BY          =>  G_LAST_UPDATED_BY,
                                            X_LAST_UPDATE_LOGIN        =>  G_LAST_UPDATE_LOGIN,
                                            X_MAINTENANCE_MODE_SUPPORT =>  C_UPDATE_FUNCTION_REC.MAINTENANCE_MODE_SUPPORT,
                                            X_CONTEXT_DEPENDENCE       =>  C_UPDATE_FUNCTION_REC.CONTEXT_DEPENDENCE,
                                            X_JRAD_REF_PATH            =>  C_UPDATE_FUNCTION_REC.JRAD_REF_PATH
                                         );
      EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('Failed the update function '||C_UPDATE_FUNCTION_REC.FUNCTION_NAME);
      END;
    END LOOP;
   COMMIT;
  END;
  /

Dividing pipe delimited string into columns

Example :-

     String   -   CI-CT-WESTPORT-06880.02
Column1   -   CI
Column2   -   CT
Column3   -   WESTPORT
Column4   -    06880.02


Solution :-

 SELECT REGEXP_SUBSTR('CI-CT-WESTPORT-06880.02', '[^-]+', 1, 1) COLUMN1,
                REGEXP_SUBSTR('CI-CT-WESTPORT-06880.02', '[^-]+', 1, 2) COLUMN2,
                REGEXP_SUBSTR('CI-CT-WESTPORT-06880.02', '[^-]+', 1, 3) COLUMN3,
                REGEXP_SUBSTR('CI-CT-WESTPORT-06880.02', '[^-]+', 1, 4) COLUMN4
    FROM DUAL;


You can use regexp_substr only for (10g +).

Step by Step Process to add JSP page to Oracle Application

Below are the steps to add JSP page to Oracle Application

1. Upload the .jsp page to $OA_HTML top
2. Run " export PATH=$PATH:$FND_TOP/patch/115/bin "
3. Run " ojspCompile.pl --compile -s 'JSPFileName.jsp' --flush "
4. Create a form function with type "SSWA jsp function" and web call as "JSPFileName.jsp".
5. Bounce the server.

In R12 .class file is stored in $COMMON_TOP/_pages and in R11 is stored in $OA_HTML

How to use FND_GLOBAL for SSWA jsp function

When you use FND_GLOBAL.USER_ID or FND_GLOBAL.RESP_ID for SSWA jsp function, it always returns -1.

To get the correct value of these functions you can use ICX_SEC.

procedure testing
IS
l_session boolean;
BEGIN
l_session := icx_sec.validateSession(c_update => FALSE);
htp.p('USER: ' || icx_sec.g_user_id );
htp.br;
htp.p('RESP: ' || icx_sec.g_responsibility_id );
END;

Results of launching the function I created referring to procedure testing is :

USER: 5008
RESP: 40398

Query to get the USER password

First compile the below package


create or replace
PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
end get_pwd;
/
create or replace
PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;

Now run this query to get the password

SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = 'USER';