Logic for BOM Implosion using bompimpl.imploder_userexit API

DECLARE
   i                             INTEGER;
   l_next_seq_num                NUMBER;
   l_err_msg                     VARCHAR2 (2000);
   l_err_code                    VARCHAR2 (2000);
   l_rev_date                    VARCHAR2 (30)
                                    := TO_CHAR (SYSDATE + 1
                                              , 'YYYY/MM/DD HH24:MI:SS');

   TYPE item_id_tbl_typ IS VARRAY (2) OF NUMBER;

   item_id_tbl                   item_id_tbl_typ;
BEGIN
   item_id_tbl         := item_id_tbl_typ (405199, 405214);

   BEGIN
      DELETE FROM xx_bom_implosion_temp_stg;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   DELETE FROM bom_implosion_temp;

   FOR i IN 1 .. item_id_tbl.COUNT
   LOOP
      l_next_seq_num       := bom_implosion_temp_s.NEXTVAL;

      bompimpl.imploder_userexit (
         sequence_id                     => l_next_seq_num
       , eng_mfg_flag                    => 1
       , org_id                          => 103
       , impl_flag                       => 1
       , display_option                  => 1
       , levels_to_implode               => 59
       , item_id                         => item_id_tbl ( i)
       , impl_date                       => l_rev_date
       , err_msg                         => l_err_msg
       , err_code                        => l_err_code);

      INSERT INTO xx_bom_implosion_temp_stg
         SELECT * FROM bom_implosion_temp;

      COMMIT;
   END LOOP;
END;

Script to Get the Sales Order Total Amount in Oracle Order Managment

DECLARE
   p_header_id                   NUMBER := 1084842;
   p_subtotal                    NUMBER;
   p_discount                    NUMBER;
   p_charges                     NUMBER;
   p_tax                         NUMBER;
   p_total                       NUMBER;
BEGIN
   apps.oe_oe_totals_summary.order_totals (p_header_id,
                                           p_subtotal,
                                           p_discount,
                                           p_charges,
                                           p_tax);
   p_total                         := p_subtotal + p_charges + p_tax;
   DBMS_OUTPUT.put_line (   'Order Subtotal : '
                         || p_subtotal
                         || ' Discount : '
                         || p_discount
                         || ' Charges : '
                         || p_charges
                         || ' Tax Amount : '
                         || p_tax
                         || ' Order Total:'
                         || p_total);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'UBEXP_ERROR In Main : '
                            || SUBSTR (SQLERRM, 1, 250));
END;

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;