Uploading files into Oracle EBS App Server from OAF

In this article we shall see OAF capability of uploading any file into EBS file system(with application owner user credentials). Very bad user can bring down the system as well, by uploading junk files .

We shall create below components for making this utility to work:-

1.       OAF Page(\xxcust\oracle\apps\fnd\util\webui\XxcustFileUploadPG) to capture user entered details like

a.       Destination directory

b.       Local File to be uploaded.

2.       OAF Controller(xxcust.oracle.apps.fnd.util.webui.XxcustFileUploadCO) for page created in Step-1

3.       Function, Menu and responsibility for accessing the page.

OAF Page for Upload

OAF upload page is very simple page with

ü  MessageTextInput for “Directory”

ü  “MessageFileUpload” for selecting the local file with “browse” button

ü  “Submit” button for final submission for uploading.

Page looks like below (code can be downloaded from the link provided at the end of the post):-

OAF Controller

Controller has code for reading the local file and uploading the same to server.

Following is the method for uploading the selected local file, call this method from processFormRequest method on click of “Submit” button

    public void upLoadFile(OAPageContext pageContext,OAWebBean webBean)

    {

     String filePath = "";

     System.out.println("Default File Path---->"+filePath);

     String fileUrl = null;

     try

     {

      DataObject fileUploadData =  pageContext.getNamedDataObject("MessageFileUpload");

      //FileUploading is my MessageFileUpload Bean Id

      if(fileUploadData!=null)

      {

           String uFileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");

           String contentType = (String) fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");

           pageContext.writeDiagnostics(this,"User File Name---->"+uFileName,OAFwkConstants.STATEMENT);

   

           FileOutputStream output = null;

           InputStream input = null;

   

           BlobDomain uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, uFileName);

           pageContext.writeDiagnostics(this,"uploadedByteStream---->"+uploadedByteStream,OAFwkConstants.STATEMENT);

           String directory =  pageContext.getParameter("directory");

           File file = new File(directory, uFileName);

           pageContext.writeDiagnostics(this,"File output---->"+file,OAFwkConstants.STATEMENT);

   

           output = new FileOutputStream(file);

   

           pageContext.writeDiagnostics(this,"output----->"+output,OAFwkConstants.STATEMENT);

           input = uploadedByteStream.getInputStream();

   

           pageContext.writeDiagnostics(this,"input---->"+input,OAFwkConstants.STATEMENT);

           byte abyte0[] = new byte[0x19000];

           int i;

   

           while((i = input.read(abyte0)) > 0){

               output.write(abyte0, 0, i);

           }

           output.close();

           input.close();

      }

      throw new OAException("File Uploaded Successfully!",OAException.CONFIRMATION);

     }

     catch(Exception ex)

     {

      throw new OAException(ex.getMessage(), OAException.ERROR);

     }

    }

Invocation from processFormRequest:-

    public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)

    {

         super.processFormRequest(pageContext, webBean);

         if(pageContext.getParameter("Submit")!=null)

         {

          upLoadFile(pageContext,webBean);

         }

    }

Following are the screenshots of utility in action:-

After uploading file successfully

When error occurs during file upload

Function Creation

Screenshots for function creation:-

HTML Call in “Web HTML” should be “OA.jsp?page=/xxcust/oracle/apps/fnd/util/webui/XxcustFileUploadPG”

Menu & Responsibility creation

This section I leave to the reader.

You can download OAF Page and controller class from here.

Adding Responsibilities to User and setting the profile options from PL/SQL Script.

In this article we shall see how to:-

1.      add multiple responsibilities to a user in EBS

2.      Set profile option value at user level.

DECLARE

      l_needed_resp varchar2(4000):=   'Alert Manager#

                                        Application Administrator#

                                        Functional Administrator#

                                        System Administration#

                                        System Administrator#

                                        Workflow Administrator Web (New)#

                                        XML Publisher Administrator#';

      CURSOR cur IS

          WITH qry AS (

          SELECT    l_needed_resp resp_string

          FROM    dual

          )

          SELECT    trim(replace(regexp_substr (resp_string, '[^#]+', 1, ROWNUM),chr(10),'')) resp

          FROM    qry

          CONNECT BY LEVEL <= LENGTH(regexp_replace (resp_string, '[^#]+')) + 1

          ;

    lc_user_name              VARCHAR2(100)    := 'XXTEST';

    lc_resp_appl_short_name   VARCHAR2(100)    ;

    lc_responsibility_key     VARCHAR2(100)    ;

    lc_security_group_key     VARCHAR2(100)    ;

    ld_resp_start_date        DATE             := TO_DATE('11-SEP-2016');

    ld_resp_end_date          DATE             := NULL;           

    l_success     BOOLEAN;

    l_user_id     NUMBER;

  BEGIN

      --

      -- This will set value of profile option 'ONT_DEBUG_LEVEL' at User level to 1

      --

      select user_id

      into  l_user_id

      from fnd_user

      where user_name = lc_user_name;

     

      --FND: Diagnostics

      l_success := FND_PROFILE.save

                   ( x_name                 => 'FND_DIAGNOSTICS'

                   , x_value                => 'Y'

                   , x_level_name           => 'USER'

                   , x_level_value          => l_user_id  -- Given User Id

                   , x_level_value_app_id   => NULL

                   ) ;

      COMMIT;            

      IF l_success

      THEN

         DBMS_OUTPUT.put_line('FND: Diagnostics Profile Updated successfully at User Level');

      ELSE

         DBMS_OUTPUT.put_line('FND: Diagnostics Profile Update Failed at User Level. Error:'||sqlerrm);

      END IF;

                        

      --  Utilities:Diagnostics          

      l_success := FND_PROFILE.save

                   ( x_name                 => 'DIAGNOSTICS'

                   , x_value                => 'Y'

                   , x_level_name           => 'USER'

                   , x_level_value          => l_user_id  -- Given User Id

                   , x_level_value_app_id   => NULL

                   ) ;   

                  

      COMMIT;            

      IF l_success

      THEN

         DBMS_OUTPUT.put_line('Utilities:Diagnostics Profile Updated successfully at User Level');

      ELSE

         DBMS_OUTPUT.put_line('Utilities:Diagnostics Profile Update Failed at User Level. Error:'||sqlerrm);

      END IF;

                        

      -- Personalize Self-Service Defn                  

      l_success := FND_PROFILE.save

                   ( x_name                 => 'FND_CUSTOM_OA_DEFINTION'

                   , x_value                => 'Y'

                   , x_level_name           => 'USER'

                   , x_level_value          => l_user_id  -- Given User Id

                   , x_level_value_app_id   => NULL

                   ) ;  

      COMMIT;            

      IF l_success

      THEN

         DBMS_OUTPUT.put_line('Personalize Self-Service Defn Profile Updated successfully at User Level');

      ELSE

         DBMS_OUTPUT.put_line('Personalize Self-Service Defn Profile Update Failed at User Level. Error:'||sqlerrm);

      END IF;

                          

      -- FND: Personalization Region Link Enabled                  

      l_success := FND_PROFILE.save

                   ( x_name                 => 'FND_PERSONALIZATION_REGION_LINK_ENABLED'

                   , x_value                => 'Y'

                   , x_level_name           => 'USER'

                   , x_level_value          => l_user_id  -- Given User Id

                   , x_level_value_app_id   => NULL

                   ) ;

      COMMIT;                                                                                          

      IF l_success

      THEN

         DBMS_OUTPUT.put_line('FND: Personalization Region Link Enabled Profile Updated successfully at User Level');

      ELSE

         DBMS_OUTPUT.put_line('FND: Personalization Region Link Enabled Profile Update Failed at User Level. Error:'||sqlerrm);

      END IF;     

      FOR rec IN cur LOOP

      BEGIN

        dbms_output.put_line('Resp:' || rec.resp);

        

        SELECT frv.responsibility_key, fa.application_short_name,

                                       (SELECT security_group_key

                                          FROM fnd_security_groups

                                         WHERE security_group_id IN (

                                                  SELECT data_group_id

                                                    FROM fnd_responsibility_vl frv,

                                                         fnd_application fa

                                                   WHERE responsibility_key = frv.responsibility_key

                                                     AND frv.application_id = fa.application_id

                                                     AND fa.application_short_name = fa.application_short_name))

                                                                                               security_group

        INTO lc_responsibility_key,lc_resp_appl_short_name,lc_security_group_key

        FROM fnd_responsibility_vl frv, fnd_application fa

        WHERE responsibility_name = rec.resp

              AND frv.application_id = fa.application_id;

          DBMS_OUTPUT.PUT_LINE ( 'lc_responsibility_key = ' || lc_responsibility_key );

          DBMS_OUTPUT.PUT_LINE ( 'lc_resp_appl_short_name = ' || lc_resp_appl_short_name );

          DBMS_OUTPUT.PUT_LINE ( 'lc_security_group_key = ' || lc_security_group_key );                        

                              

            BEGIN

                 fnd_user_pkg.addresp

                 (   username            => lc_user_name,

                    resp_app             => lc_resp_appl_short_name,

                    resp_key             => lc_responsibility_key,

                    security_group       => lc_security_group_key,

                    description         => NULL,

                    start_date           => ld_resp_start_date,

                    end_date            => ld_resp_end_date

                );

            DBMS_OUTPUT.PUT_LINE('************ADDED RESP');

             COMMIT;

            EXCEPTION

                        WHEN OTHERS THEN

                                    DBMS_OUTPUT.PUT_LINE(SQLERRM);

            END;

        EXCEPTION

                    WHEN OTHERS THEN

                                DBMS_OUTPUT.PUT_LINE(SQLERRM);

        END;                  

      END LOOP;

  END;

  /

As you can see that you can specify multiple responsibilities to be attached using “#” and new line delimiter as defined by “l_needed_resp” PL/SQL variable.

This script will speedup the process of setting up the user responsibility and needed profile option values.