Script to Create Excel using UTL_FILE

DECLARE
   v_fh                          UTL_FILE.file_type;
   v_dir                         VARCHAR2 (30) := 'XX_INTF_DIR';
   v_file                        VARCHAR2 (30) := 'custom_value_sets.xls';

   PROCEDURE run_query (p_sql IN VARCHAR2)
   IS
      v_v_val                       VARCHAR2 (4000);
      v_n_val                       NUMBER;
      v_d_val                       DATE;
      v_ret                         NUMBER;
      c                             NUMBER;
      d                             NUMBER;
      col_cnt                       INTEGER;
      f                             BOOLEAN;
      rec_tab                       DBMS_SQL.desc_tab;
      col_num                       NUMBER;
   BEGIN
      c                               := DBMS_SQL.open_cursor;
      -- parse the SQL statement
      DBMS_SQL.parse (c, p_sql, DBMS_SQL.native);
      -- start execution of the SQL statement
      d                               := DBMS_SQL.execute (c);
      -- get a description of the returned columns
      DBMS_SQL.describe_columns (c, col_cnt, rec_tab);

      -- bind variables to columns
      FOR j IN 1 .. col_cnt
      LOOP
         CASE rec_tab (j).col_type
            WHEN 1
            THEN
               DBMS_SQL.define_column (c,
                                       j,
                                       v_v_val,
                                       4000);
            WHEN 2
            THEN
               DBMS_SQL.define_column (c, j, v_n_val);
            WHEN 12
            THEN
               DBMS_SQL.define_column (c, j, v_d_val);
            ELSE
               DBMS_SQL.define_column (c,
                                       j,
                                       v_v_val,
                                       4000);
         END CASE;
      END LOOP;

      -- Output the column headers
      UTL_FILE.put_line (v_fh, '<ss:Row>');

      FOR j IN 1 .. col_cnt
      LOOP
         UTL_FILE.put_line (v_fh, '<ss:Cell>');
         UTL_FILE.put_line (v_fh,
                               '<ss:Data ss:Type="String">'
                            || rec_tab (j).col_name
                            || '</ss:Data>');
         UTL_FILE.put_line (v_fh, '</ss:Cell>');
      END LOOP;

      UTL_FILE.put_line (v_fh, '</ss:Row>');

      -- Output the data
      LOOP
         v_ret                           := DBMS_SQL.fetch_rows (c);
         EXIT WHEN v_ret = 0;
         UTL_FILE.put_line (v_fh, '<ss:Row>');

         FOR j IN 1 .. col_cnt
         LOOP
            CASE rec_tab (j).col_type
               WHEN 1
               THEN
                  DBMS_SQL.COLUMN_VALUE (c, j, v_v_val);
                  UTL_FILE.put_line (v_fh, '<ss:Cell>');
                  UTL_FILE.put_line (v_fh,
                                        '<ss:Data ss:Type="String">'
                                     || v_v_val
                                     || '</ss:Data>');
                  UTL_FILE.put_line (v_fh, '</ss:Cell>');
               WHEN 2
               THEN
                  DBMS_SQL.COLUMN_VALUE (c, j, v_n_val);
                  UTL_FILE.put_line (v_fh, '<ss:Cell>');
                  UTL_FILE.put_line (v_fh,
                                        '<ss:Data ss:Type="Number">'
                                     || TO_CHAR (v_n_val)
                                     || '</ss:Data>');
                  UTL_FILE.put_line (v_fh, '</ss:Cell>');
               WHEN 12
               THEN
                  DBMS_SQL.COLUMN_VALUE (c, j, v_d_val);
                  UTL_FILE.put_line (v_fh, '<ss:Cell ss:StyleID="OracleDate">');
                  UTL_FILE.put_line (v_fh,
                                        '<ss:Data ss:Type="DateTime">'
                                     || TO_CHAR (v_d_val, 'YYYY-MM-DD"T"HH24:MI:SS')
                                     || '</ss:Data>');
                  UTL_FILE.put_line (v_fh, '</ss:Cell>');
               ELSE
                  DBMS_SQL.COLUMN_VALUE (c, j, v_v_val);
                  UTL_FILE.put_line (v_fh, '<ss:Cell>');
                  UTL_FILE.put_line (v_fh,
                                        '<ss:Data ss:Type="String">'
                                     || v_v_val
                                     || '</ss:Data>');
                  UTL_FILE.put_line (v_fh, '</ss:Cell>');
            END CASE;
         END LOOP;

         UTL_FILE.put_line (v_fh, '</ss:Row>');
      END LOOP;

      DBMS_SQL.close_cursor (c);
   END;

   --
   PROCEDURE start_workbook
   IS
   BEGIN
      UTL_FILE.put_line (v_fh, '<?xml version="1.0"?>');
      UTL_FILE.put_line (v_fh,
                         '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">');
   END;

   PROCEDURE end_workbook
   IS
   BEGIN
      UTL_FILE.put_line (v_fh, '</ss:Workbook>');
   END;

   --
   PROCEDURE start_worksheet (p_sheetname IN VARCHAR2)
   IS
   BEGIN
      UTL_FILE.put_line (v_fh,
                            '<ss:Worksheet ss:Name="'
                         || p_sheetname
                         || '">');
      UTL_FILE.put_line (v_fh, '<ss:Table>');
   END;

   PROCEDURE end_worksheet
   IS
   BEGIN
      UTL_FILE.put_line (v_fh, '</ss:Table>');
      UTL_FILE.put_line (v_fh, '</ss:Worksheet>');
   END;

   --
   PROCEDURE set_date_style
   IS
   BEGIN
      UTL_FILE.put_line (v_fh, '<ss:Styles>');
      UTL_FILE.put_line (v_fh, '<ss:Style ss:ID="OracleDate">');
      UTL_FILE.put_line (v_fh, '<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>');
      UTL_FILE.put_line (v_fh, '</ss:Style>');
      UTL_FILE.put_line (v_fh, '</ss:Styles>');
   END;
BEGIN
   v_fh                            :=
      UTL_FILE.fopen (UPPER (v_dir),
                      v_file,
                      'w',
                      32767);
   start_workbook;
   set_date_style;
   start_worksheet ('CUSTOM_VALUESET');
   run_query ('SELECT ffvs.flex_value_set_id,
       ffvs.flex_value_set_name,
       ffvt.value_column_name,
       ffvt.meaning_column_name,
       ffvt.id_column_name,
       ffvt.application_table_name,
       ffvt.additional_where_clause
  FROM fnd_flex_value_sets ffvs, fnd_flex_validation_tables ffvt
 WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id
     AND UPPER (ffvs.flex_value_set_name) LIKE ''XX%''') ;
   end_worksheet;
/*When needs to create Multiple Sheets in a workbook*/
--   start_worksheet ('DUAL');
--   run_query ('SELECT SYSDATE FROM DUAL');
--   end_worksheet;
   end_workbook;
   UTL_FILE.fclose (v_fh);
END;

Script to Create New FND_USER and Assign Responsilities and also Update/Create Profile For ICX_FORMS_LAUNCHER

------------------------------------------------------------------------------------------------
-- Replace the Value for l_user_name for creating multiple users and assign the responsibilities
------------------------------------------------------------------------------------------------

DECLARE
   l_user_name                VARCHAR2 (100);
   l_user_password            VARCHAR2 (100) := 'welcome1';
   l_user_start_date          DATE := SYSDATE;
   l_user_end_date            VARCHAR2 (100) := NULL;
   l_password_date            VARCHAR2 (100) := SYSDATE;
   l_password_lifespan_days   NUMBER;
   l_person_id                NUMBER;
   l_email_address            VARCHAR2 (100);
   l_count                    NUMBER;
   l_change_password          BOOLEAN;
   l_user_id                  NUMBER;

   PROCEDURE assign_responsibility (p_user_name IN VARCHAR2)
   IS
      CURSOR c_resp
      IS
         SELECT fav.application_short_name,
                fav.application_name,
                frv.responsibility_key,
                frv.responsibility_name
           FROM fnd_application_vl fav, fnd_responsibility_vl frv
          WHERE     frv.application_id = fav.application_id
                AND frv.responsibility_name IN ('US Receivables Super User',
                                                'US Service Contracts Super User',
                                                'US Order Management Super User',
                                                'Installed Base User');

      l_appl_short_name   fnd_application_vl.application_short_name%TYPE;
      l_resp_name         fnd_responsibility_vl.responsibility_name%TYPE;
      l_resp_key          fnd_responsibility_vl.responsibility_key%TYPE;
      l_description       VARCHAR2 (100)
         := 'Adding Responsibility to the user using script';
      l_count             NUMBER;
   BEGIN
      FOR resp_rec IN c_resp
      LOOP
         SELECT COUNT (1)
           INTO l_count
           FROM fnd_user fuser,
                per_people_f per,
                fnd_user_resp_groups furg,
                fnd_responsibility_tl frt
          WHERE     fuser.employee_id = per.person_id
                AND fuser.user_id = furg.user_id
                AND (   TO_CHAR (fuser.end_date) IS NULL
                     OR fuser.end_date > SYSDATE)
                AND frt.responsibility_id = furg.responsibility_id
                AND (   TO_CHAR (furg.end_date) IS NULL
                     OR furg.end_date > SYSDATE)
                AND frt.language = 'US'
                AND fuser.user_name = p_user_name
                AND frt.responsibility_name = resp_rec.responsibility_name;

         IF l_count = 0
         THEN
            l_appl_short_name := resp_rec.application_short_name;
            l_resp_key := resp_rec.responsibility_key;
            l_resp_name := resp_rec.responsibility_name;


            BEGIN
               fnd_user_pkg.addresp (username         => p_user_name,
                                     resp_app         => l_appl_short_name,
                                     resp_key         => l_resp_key,
                                     security_group   => 'STANDARD',
                                     description      => l_description,
                                     start_date       => SYSDATE,
                                     end_date         => NULL);
               COMMIT;
               DBMS_OUTPUT.put_line (
                     'The responsibility : '
                  || l_resp_name
                  || ' is added to the user '
                  || p_user_name);
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (
                        'Responsibility '
                     || l_resp_name
                     || ' IS NOT added to the user '
                     || p_user_name
                     || ' due to '
                     || SQLCODE
                     || '; '
                     || SUBSTR (SQLERRM, 1, 250));
                  DBMS_OUTPUT.put_line ('');
                  ROLLBACK;
            END;
         ELSE
            DBMS_OUTPUT.put_line (
                  'Responsibility : '
               || resp_rec.responsibility_name
               || ' Already assigned');
         END IF;
      END LOOP;
   END;

   PROCEDURE update_profile (p_user_id IN NUMBER)
   IS
      l_result   BOOLEAN;
   BEGIN
      l_result :=
         fnd_profile.save (
            x_name                 => 'ICX_FORMS_LAUNCHER',
            x_value                => 'https://www.google.com',
            x_level_name           => 'USER',
            x_level_value          => p_user_id,
            x_level_value_app_id   => NULL,
            x_level_value2         => NULL);

      IF l_result
      THEN
         DBMS_OUTPUT.put_line ('Profile Updated');
      ELSE
         DBMS_OUTPUT.put_line ('Profile Not Updated');
      END IF;

      COMMIT;
   END;
BEGIN
   fnd_global.apps_initialize (17959, 20420, 1);

   FOR i IN 1 .. 200
   LOOP
      l_user_name := 'TESTUSER' || i;
      fnd_user_pkg.createuser (
         x_user_name                => l_user_name,
         x_owner                    => NULL,
         x_unencrypted_password     => l_user_password,
         x_start_date               => l_user_start_date,
         x_end_date                 => l_user_end_date,
         x_password_date            => l_password_date,
         x_password_lifespan_days   => l_password_lifespan_days,
         x_employee_id              => 12345,
         x_email_address            => 'xxxx@gmail.com');

      COMMIT;

      SELECT user_id
        INTO l_user_id
        FROM fnd_user
       WHERE user_name = l_user_name;

      IF l_user_id IS NOT NULL AND l_user_id > 0
      THEN
         assign_responsibility (l_user_name);
         update_profile (l_user_id);

         BEGIN
            jtf_auth_bulkload_pkg.assign_role (l_user_name,
                                               'CSI_NORMAL_USER');
            jtf_auth_bulkload_pkg.assign_role (l_user_name, 'CSI_ADMIN_USER');
            jtf_auth_bulkload_pkg.assign_role (l_user_name,
                                               'CSI_READ_ONLY_USER');
            COMMIT;
            DBMS_OUTPUT.put_line ('Assign IB Roles Successfully!!!');
         END;

         l_change_password :=
            fnd_user_pkg.changepassword (l_user_name, 'welcome123');

         IF l_change_password
         THEN
            DBMS_OUTPUT.put_line ('Password Changed successfully!!!');
            COMMIT;
         ELSE
            DBMS_OUTPUT.put_line ('Unable to Change Password!!!');
            ROLLBACK;
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (
               'User : '
            || l_user_name
            || ' Not found, hence unable to assign the responsibilities');
      END IF;

      l_user_name := NULL;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

SQL query to get the details of Scheduled Concurrent Requests in Oracle applications

  SELECT r.request_id
       , p.user_concurrent_program_name
         || NVL2 (r.description
                , ' ('
                  || r.description
                  || ')'
                , NULL)
            conc_prog
       , s.user_name requestor
       , r.argument_text arguments
       , r.requested_start_date next_run
       , r.last_update_date last_run
       , r.hold_flag on_hold
       , r.increment_dates
       , DECODE (c.class_type,  'P', 'Periodic',  'S', 'On Specific Days',  'X', 'Advanced',  c.class_type) schedule_type
       , CASE
            WHEN c.class_type = 'P'
            THEN
               'Repeat every '
               || SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)
               || DECODE (SUBSTR (c.class_info
                                ,   INSTR (c.class_info
                                         , ':'
                                         , 1
                                         , 1)
                                  + 1
                                , 1)
                        , 'N', ' minutes'
                        , 'M', ' months'
                        , 'H', ' hours'
                        , 'D', ' days')
               || DECODE (SUBSTR (c.class_info
                                ,   INSTR (c.class_info
                                         , ':'
                                         , 1
                                         , 2)
                                  + 1
                                , 1)
                        , 'S', ' from the start of the prior run'
                        , 'C', ' from the completion of the prior run')
            WHEN c.class_type = 'S'
            THEN
               NVL2 (dates.dates
                   , 'Dates: '
                     || dates.dates
                     || '. '
                   , NULL)
               || DECODE (SUBSTR (c.class_info, 32, 1), '1', 'Last day of month ')
               || DECODE (SIGN (TO_NUMBER (SUBSTR (c.class_info, 33)))
                        , '1', 'Days of week: '
                               || DECODE (SUBSTR (c.class_info, 33, 1), '1', 'Su ')
                               || DECODE (SUBSTR (c.class_info, 34, 1), '1', 'Mo ')
                               || DECODE (SUBSTR (c.class_info, 35, 1), '1', 'Tu ')
                               || DECODE (SUBSTR (c.class_info, 36, 1), '1', 'We ')
                               || DECODE (SUBSTR (c.class_info, 37, 1), '1', 'Th ')
                               || DECODE (SUBSTR (c.class_info, 38, 1), '1', 'Fr ')
                               || DECODE (SUBSTR (c.class_info, 39, 1), '1', 'Sa '))
         END
            AS schedule
       , c.date1 start_date
       , c.date2 end_date
       , c.class_info
    FROM apps.fnd_concurrent_requests r
       , apps.fnd_conc_release_classes c
       , apps.fnd_concurrent_programs_tl p
       , apps.fnd_user s
       , (WITH date_schedules
               AS (SELECT release_class_id, RANK () OVER (PARTITION BY release_class_id ORDER BY s) a, s
                     FROM (SELECT c.class_info
                                , l
                                , c.release_class_id
                                , DECODE (SUBSTR (c.class_info, l, 1), '1', TO_CHAR (l)) s
                             FROM (    SELECT LEVEL l
                                         FROM DUAL
                                   CONNECT BY LEVEL <= 31)
                                , apps.fnd_conc_release_classes c
                            WHERE c.class_type = 'S'
                              AND INSTR (SUBSTR (c.class_info, 1, 31), '1') > 0)
                    WHERE s IS NOT NULL)
              SELECT release_class_id, SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates
                FROM apps.date_schedules
          START WITH a = 1
          CONNECT BY NOCYCLE PRIOR a = a - 1
            GROUP BY release_class_id) dates
   WHERE r.phase_code = 'P'
     AND c.application_id = r.release_class_app_id
     AND c.release_class_id = r.release_class_id
     AND NVL (c.date2, SYSDATE + 1) > SYSDATE
     AND c.class_type IS NOT NULL
     AND p.concurrent_program_id = r.concurrent_program_id
     AND p.language = 'US'
     AND dates.release_class_id(+) = r.release_class_id
     AND r.requested_by = s.user_id
ORDER BY conc_prog, on_hold, next_run;

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

Resolved this Error By performing following Steps : 
Set the system environment variable, as follows :

  1.  Ensure that all browser windows are closed.
  2.  Access the Advanced System settings from the Control Panel.
  3.  On a Windows 7 client machine, this is done as follows : Navigate to the Control Panel, select the System item, select the Advanced system settings option.
   4. On a Windows XP client machine, this is done as follows : Navigate to the Control Panel, locate and open the System item, in the System Properties dialog, navigate to the Advanced tab.
    5. Select the Environment Variables button.
    6. Go through both the User variables box and the System variables box, looking for an existing variable called JAVA_TOOL_OPTIONS.
    7. Assuming no existing JAVA_TOOL_OPTIONS variable is found, select the New button in the System variables block, at the bottom of the screen.
    8.In the resultant New System Variable dialog, create a new variable with the following information:

Variable name  : JAVA_TOOL_OPTIONS
Variable value : -Djava.vendor="New Oracle"

(Note the leading hyphen "-" in the value)

   9. Select the OK button in the Environment Variables dialog, then the OK button in the System Properties dialog to save this change.

This works by changing the vendor information for Java from Oracle to anything else; I used the string "New Oracle" to avoid confusion; you can use the "Sun Microsystems Inc." string too.

This then stops the Java code in Forms 10g (specifically, the FRMALL.JAR server-side package, I believe) from thinking that the Java client is an older version of JInitiator, which is no longer supported - hence the confusing message.

Note that this problem does not occur in Oracle Forms 11g, as the relevant JAR package has been updated. However, you can run Oracle Forms 11g systems with this workaround in place.

Hope this resolve your issue,

Unable to locate android build tools in the specified location (Oracle ADF Mobile)

If you are facing the error while deploying the Mobile app on the Andriod emulator and the error message says "Uable to locate android build tools in the specified location" in the Platform-tools Folder then the Andriod SDK version that you are trying to install on your local machine has the required files moved to the Build-tools --> 17.0.0 folder. To make it work you need to follow the following steps.

1. Open the Command Prompt as "Run As Administrator".
2. In the Commpand promt execute the Following commands

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
C:\>mklink c:\AndroidSDK\sdk\platform-tools\aapt.exe C:\AndroidSDK\sdk\build-tools\17.0.0\aapt.exe

output for the Above Script :- symbolic link created for c:\AndroidSDK\sdk\platform-tools\aapt.exe <<===>> C:\AndroidSDK\sdk\build-tools\17.0.0\aapt.exe

C:\>mklink /D c:\AndroidSDK\sdk\platform-tools\lib c:\AndroidSDK\sdk\build-tools
\17.0.0\lib
output for the Above Script :- symbolic link created for c:\AndroidSDK\sdk\platform-tools\lib <<===>> c:\AndroidSDK\sdk\build-tools\17.0.0\lib


C:\>mklink c:\AndroidSDK\sdk\platform-tools\dx.bat C:\AndroidSDK\sdk\build-tools\17.0.0\dx.bat

output for the Above Script :- symbolic link created for c:\AndroidSDK\sdk\platform-tools\dx.bat <<===>> C:\AndroidSDK\sdk\build-tools\17.0.0\dx.bat


Once you are done with the above setup then you can try deploying Your mobile app to Andriod Emulator.


I Hope this will help you in getting rid of the Issue................