sys_refcursor to dynamic record/table type

DECLARE
   l_refcursor                          SYS_REFCURSOR;
   l_cols                        NUMBER;
   l_desc                        DBMS_SQL.desc_tab;
   l_curs                        INTEGER;
   l_varchar                     VARCHAR2(4000);

   FUNCTION xx_ref_cursor
      RETURN SYS_REFCURSOR
   IS
      l_refcursor                   SYS_REFCURSOR;
   BEGIN
      OPEN l_refcursor FOR
         SELECT inventory_item_id, segment1, description
           FROM mtl_system_items_b
          WHERE ROWNUM <= 10;

      RETURN l_refcursor;
   END;
BEGIN
   l_refcursor := xx_ref_cursor;
   l_curs                                              := DBMS_SQL.to_cursor_number( l_refcursor);
   DBMS_SQL.describe_columns(
      c                               => l_curs
    , col_cnt                         => l_cols
    , desc_t                          => l_desc);

   FOR i IN 1 .. l_cols
   LOOP
      DBMS_SQL.define_column(
         l_curs
       , i
       , l_varchar
       , 4000);
   END LOOP;

   WHILE DBMS_SQL.fetch_rows( l_curs) > 0
   LOOP
      FOR i IN 1 .. l_cols
      LOOP
         DBMS_SQL.COLUMN_VALUE(
            l_curs
          , i
          , l_varchar);
         DBMS_OUTPUT.put_line(
               'Row Number :'
            || DBMS_SQL.last_row_count
            || ': '
            || l_desc( i).col_name
            || ' = '
            || l_varchar);
      END LOOP;
   END LOOP;

   DBMS_SQL.close_cursor( l_curs);
END;
/

Create JDBC generic data sources

Create JDBC generic data sources

Before you begin

Make sure that the JDBC drivers that you want to use to create database connections are installed on all servers on which you want to deploy the data source. Some JDBC drivers are installed with WebLogic Server, including Oracle Type 4 JDBC drivers for DB2, Informix, MS SQL Server, and Sybase. For more information about working with JDBC drivers, see Using JDBC Drivers with WebLogic Server.

In WebLogic Server, you configure database connectivity by adding JDBC data sources to your WebLogic domain. A data source is a J2EE standard method of configuring connectivity to a database. Each WebLogic data source contains a pool of database connections. Applications look up the data source on the JNDI tree or in the local application context and then reserve a database connection with the get Connection method. Data sources and their connection pools provide connection management processes that help keep your system running efficiently.

To create a JDBC data source:

1.       If you have not already done so, in the Change Center of the Administration Console, click Lock & Edit (see Use the Change Center).

2.       In the Domain Structure tree, expand Services, then select Data Sources.

3.       On the Summary of Data Sources page, click New and select Generic Data Source.

4.       On the JDBC Data Sources Properties page, enter or select the following information:

Name - Enter a name for this JDBC data source. This name is used in the configuration file (config.xml) and throughout the Administration Console whenever referring to this data source.

JNDI Name - Enter the JNDI path to where this JDBC data source will be bound. Applications look up the data source on the JNDI tree by this name when reserving a connection.

Database Type - Select the DBMS of the database that you want to connect to. If your DBMS is not listed, select other.

Click Next to continue.

5.       Select the database driver:

Database Driver - Select the JDBC driver you want to use to connect to the database. The list includes common JDBC drivers for the selected DBMS.

Note: You must install JDBC drivers before you can use them to create database connections. Some JDBC drivers are installed with WebLogic Server, but many are not installed.

6.       On the Transaction Options page, follow these steps. Depending on the driver you selected on the JDBC Data Source Properties page, you may not need to specify any of these options.

Supports Global Transactions - Select this check box (the default) to enable global transaction support in this data source. Clear this check box to disable (ignore) global transactions in this data source. In most cases, you should leave the option selected. See Configure Transaction Options for Generic Data Sources.

If you selected Supports Global Transactions, select an option for transaction processing: (available options vary depending on whether you select an XA driver or a non-XA driver)

·         Two-Phase Commit - Select this option to enable standard XA processing.

This option is only available when you select an XA JDBC driver to make database connections.

·         Logging Last Resource - Select this option to enable a non-XA JDBC connection to participate in global transactions using the Logging Last Resource (LLR) transaction optimization. Recommended in place of Emulate Two-Phase Commit.

This option is only available when you select a non-XA JDBC driver to make database connections.

·         Emulate Two-Phase Commit - Enables a non-XA JDBC connection to emulate participation in distributed transactions using JTA. Select this option only if your application can tolerate heuristic conditions.

This option is only available when you select a non-XA JDBC driver to make database connections.

·         One-Phase Commit - Select this option to enable the non-XA connection to participate in a global transaction as the only transaction participant.

This option is only available when you select a non-XA JDBC driver to make database connections.

For more information about transaction options, see JDBC Data Source Transaction Options.

Click Next to continue.

7.       On the Connection Properties page, enter values for the following properties:

Service Name - This field is available only if you selected one of the available Oracle RAC Service-Instance connections drivers. Specify the service name of the database to which you want to connect. This must be the same for each data source in a given multi data source. For more information on configuring data sources to connect to Oracle RAC services, see Configuring Connections to Services on Oracle RAC Nodes.

Database Name - Enter the name of the database that you want to connect to. Exact database name requirements vary by JDBC driver and by DBMS.

Host Name - Enter the DNS name or IP address of the server that hosts the database. If you are creating an Oracle GridLink service-instance connection, this must be the same for each data source in a given multi data source.

Port - Enter the port on which the database server listens for connections requests.

Database User Name - Enter the database user account name that you want to use for each connection in the data source.

Password/Confirm Password - Enter the password for the database user account.

Click Next to continue.

8.       On the Test Database Connection page, review the connection parameters and click Test Configuration.

WebLogic attempts to create a connection from the Administration Server to the database. Results from the connection test are displayed at the top of the page. If the test is unsuccessful, you should correct any configuration errors and retry the test.

If the JDBC driver you selected is not installed on the Administration Server, you should click Next to skip this step.

Click Next to continue.

9.       On the Select Targets page, select the servers or clusters on which you want to deploy the data source.

10.   Click Finish to save the JDBC data source configuration and deploy the data source to the targets that you selected.

11.   To activate these changes, in the Change Center of the Administration Console, click Activate Changes.

Not all changes take effect immediately—some require a restart (see Use the Change Center).

What is the Cloud

 Nice post by Yahoo Tech group to understand what is cloud. Read the post to get better understanding on "What is Cloud?"

By Kaye Foley

We’ve all heard of it. And chances are good you’ve come across it in some way or another if you use the Internet. (And I’m assuming you do, since you’re reading this right now.) I’m talking about “the cloud,” and so far, forecasts have called for recognition but little understanding.

So let’s clear it up.

The cloud is actually a network of computers that work together, performing different functions, to help store and process enormous amounts of data. This is called “cloud computing,” and these computer networks are housed inside warehouses across the globe known as “data centers.”

Rather than saving your files locally on your computer’s hard drive, you can go online and send your digital belongings to these data centers, where there’s practically unlimited storage space. This means as long as you have an Internet connection, you can access your files anytime, on any device.  

But the cloud isn’t useful only as personal storage space. Businesses are getting into cloud computing as well. Big tech companies, like Amazon and Google, own and operate cloud computing platforms and offer space on the cloud to other businesses. For example, Netflix’s operation is powered through Amazon Web Services. Businesses save on costs and increase efficiency when they don’t need to maintain their own software, hard drives or computer networks.

It’s not all sunshine and rainbows, though. Some experts and users worry about the security of the cloud. Just take a look back at the iCloud software glitch in 2014 that allowed hackers to release private photos from the personal accounts of celebrities. Options for safeguarding against these attacks include using strong passwords and two-step verification as well as encrypting material before sending it to the cloud.

There’s also concern over cloud control. When an item is deleted, how can you be sure it’s completely gone? And who owns the files uploaded to the cloud--you or the company? Well, that comes down to reading the fine print and ultimately trusting the company. 

Still, this cloud cover isn’t rolling away anytime soon. Ninety percent of global Internet users are on it, and 80 percent of small businesses in the U.S. are expected to use cloud computing by 2020. So at least after watching this video you can say, “Now I get it.”

Refer to the link for Video: https://news.yahoo.com/video/now-cloud-223437934.html

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;
/