Steps to get the Access to Integration Administrtor and SOA Monitor In Integration Repository for Integrated SOA Gateqway

1. Log in as a user with User Management Responsibility Like   Sysadmin.
2. Click “User”.
3. Put the User under “User Name” > go.
4. Then click “Update” button (Pencil).
5. Click “Assign Role”.
6. Under “Search By > Roles and Responsibilities” Put the Responsibilities like “System Integration Analyst” and Click “go”.
7. Check mark and click “Select”.
8. Put “Justification” and click “Save”.
9. Keep doing for from Step 5 to 8 for “System Integration Developer” and “Irep Administrator”.
10. Log out from Self Service and Log in as a user who been     assigned 3 roles.
11. Select Integrated SOA Gateway and should have the following    under “Integrated SOA      Gateway” menu.
* Integration Repository
* SOA Monitor
12. The function for Integration Repository and SOA Monitor should be visible.

Integrated SOA Gateway (Integration Repository)

Efficient SQL Statements

Driving Tables (RBO Only)

The structure of the FROM and WHERE clauses of DML statements can be tailored to improve the performance of the statement. The rules vary depending on whether the database engine is using the Rule or Cost based optimizer. The situation is further complicated by the fact that the engine may perform a Merge Join or a Nested Loop join to retrieve the data. Despite this, there are a few rules you can use to improve the performance of your SQL.

Oracle processes result sets a table at a time. It starts by retrieving all the data for the first (driving) table. Once this data is retrieved it is used to limit the number of rows processed for subsequent (driven) tables. In the case of multiple table joins, the driving table limits the rows processed for the first driven table. Once processed, this combined set of data is the driving set for the second driven table etc. Roughly translated into English, this means that it is best to process tables that will retrieve a small number of rows first. The optimizer will do this to the best of it's ability regardless of the structure of the DML, but the following factors may help.

Both the Rule and Cost based optimizer select a driving table for each query. If a decision cannot be made, the order of processing is from the end of the FROM clause to the start. Therefore, you should always place your driving table at the end of the FROM clause. Subsequent driven tables should be placed in order so that those retrieving the most rows are nearer to the start of the FROM clause. Confusingly, the WHERE clause should be written in the opposite order, with the driving tables conditions first and the final driven table last. i.e.

FROM  d, c, b, a

WHERE a.join_column = 12345

AND   a.join_column = b.join_column

AND   b.join_column = c.join_column

AND   c.join_column = d.join_column;

If we now want to limit the rows brought back from the "D" table we may write the following.

FROM  d, c, b, a

WHERE a.join_column = 12345

AND   a.join_column = b.join_column

AND   b.join_column = c.join_column

AND   c.join_column = d.join_column

AND   d.name = 'JONES';

Depending on the number of rows and the presence of indexes, Oracle my now pick "D" as the driving table. Since "D" now has two limiting factors (join_column and name), it may be a better candidate as a driving table so the statement may be better written as follows.

FROM  c, b, a, d

WHERE d.name = 'JONES'

AND   d.join_column = 12345

AND   d.join_column = a.join_column

AND   a.join_column = b.join_column

AND   b.join_column = c.join_column

This grouping of limiting factors will guide the optimizer more efficiently making table "D" return relatively few rows, and so make it a more efficient driving table.

Remember, the order of the items in both the FROM and WHERE clause will not force the optimizer to pick a specific table as a driving table, but it may influence it's decision. The grouping of limiting conditions onto a single table will reduce the number of rows returned from that table, and will therefore make it a stronger candidate for becoming the driving table.

Build IN Clause dynamically and use it in SELECT statement

CREATE OR REPLACE TYPE xx_string_tbl_typ AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION xx_build_dynamic_in_clause( p_in_clause_strg IN VARCHAR2)
   RETURN xx_string_tbl_typ
AS
   xx_string_tbl   xx_string_tbl_typ := xx_string_tbl_typ();
   l_in_clause     VARCHAR2(4000) :=    p_in_clause_strg || ','; 
-- length can be increase till 32767
   i                                            NUMBER;
BEGIN
   LOOP
      i                                         :=
         INSTR(l_in_clause
             , ',');
      EXIT WHEN NVL(i, 0) = 0;
      xx_string_tbl.EXTEND;
      xx_string_tbl( xx_string_tbl.LAST)        :=
         TRIM(SUBSTR(l_in_clause
                   , 1
                   , i - 1));
      l_in_clause                               :=
         SUBSTR(l_in_clause
              , i + 1);
   END LOOP;

   RETURN xx_string_tbl;
END;
/
--- Test Script to test the Function
SELECT *
  FROM mtl_system_items_b
 WHERE organization_id = &organization_id
   AND inventory_item_id IN (SELECT * FROM TABLE( xx_build_dynamic_in_clause( '&1,&2,&3')))

-- Another method by using PIPELINED function

CREATE OR REPLACE FUNCTION xx_build_dynamic_in_clause( p_in_clause_strg IN VARCHAR2)
   RETURN xx_string_tbl_typ
   PIPELINED
AS
l_in_clause  VARCHAR2(4000):=    p_in_clause_strg || ',';                            -- length can be increase till 32767
   i  NUMBER;
BEGIN
   LOOP
      i                                         :=
         INSTR(l_in_clause
             , ',');
      EXIT WHEN NVL(i, 0) = 0;
      PIPE ROW (TRIM(SUBSTR(l_in_clause
                          , 1
                          , i - 1)));
      l_in_clause                               :=
         SUBSTR(l_in_clause
              , i + 1);
   END LOOP;

   RETURN;
END;
/

--- Test Script to test the Function
SELECT *
  FROM mtl_system_items_b
 WHERE organization_id = &organization_id
   AND inventory_item_id IN (SELECT * FROM TABLE( xx_build_dynamic_in_clause( '&1,&2,&3')))

API to get the On hand quantity details for Item in Inveontory

PROCEDURE get_onhand_quantities(
   p_item_name              IN            VARCHAR2
 , p_organziation_id        IN            NUMBER
 , p_subinv                 IN            VARCHAR2 DEFAULT NULL
 , l_qty_oh                    OUT NOCOPY NUMBER
 , l_qty_res_oh                OUT NOCOPY NUMBER
 , l_qty_res                   OUT NOCOPY NUMBER
 , l_qty_sug                   OUT NOCOPY NUMBER
 , l_qty_att                   OUT NOCOPY NUMBER
 , l_qty_atr                   OUT NOCOPY NUMBER)
IS
   CURSOR c_get_item_id(
    c_item_name                                   VARCHAR2
  , c_organization_id                             NUMBER)
   IS
      SELECT inventory_item_id
           , organziation_id
        FROM mtl_system_items_b
       WHERE segment1 = c_item_name
         AND organization_id = c_organization_id;

   l_api_return_status                          VARCHAR2(1);
   l_qty_oh                                     NUMBER;
   l_qty_res_oh                                 NUMBER;
   l_qty_res                                    NUMBER;
   l_qty_sug                                    NUMBER;
   l_qty_att                                    NUMBER;
   l_qty_atr                                    NUMBER;
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2(250);
   x_return_msg                                 VARCHAR2(4000);
   l_msg_index_out                              NUMBER;
   l_organziation_id                            NUMBER;
   l_item_id                                    NUMBER;
BEGIN
   OPEN c_get_item_id(p_item_name
                    , p_organziation_id);

   LOOP
      FETCH c_get_item_id
         INTO l_item_id
            , l_organziation_id;

      EXIT WHEN c_get_item_id%NOTFOUND;
      apps.inv_quantity_tree_grp.clear_quantity_cache;

      apps.inv_quantity_tree_pub.query_quantities(
         p_api_version_number                      => 1.0
       , p_init_msg_lst                            => apps.fnd_api.g_false
       , x_return_status                           => l_api_return_status
       , x_msg_count                               => l_msg_count
       , x_msg_data                                => l_msg_data
       , p_organization_id                         => l_organziation_id
       , p_inventory_item_id                       => l_item_id
       , p_tree_mode                               => apps.inv_quantity_tree_pub.g_transaction_mode
       , p_onhand_source                           => 3
       , p_is_revision_control                     => FALSE
       , p_is_lot_control                          => FALSE
       , p_is_serial_control                       => FALSE
       , p_revision                                => NULL
       , p_lot_number                              => NULL
       , p_subinventory_code                       => p_subinv
       , p_locator_id                              => NULL
       , x_qoh                                     => l_qty_oh
       , x_rqoh                                    => l_qty_res_oh
       , x_qr                                      => l_qty_res
       , x_qs                                      => l_qty_sug
       , x_att                                     => l_qty_att
       , x_atr                                     => l_qty_atr);

      IF l_api_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line(   'Total on hand quantity : '
                              || l_qty_oh
                              || ' Quantity res oh :'
                              || l_qty_res_oh
                              || ' Quantity reserved :'
                              || l_qty_res
                              || ' Quantity sug :'
                              || l_qty_sug
                              || ' Quantity Available to transat :'
                              || l_qty_att
                              || ' Quantity available to reserve :'
                              || l_qty_atr);
      ELSE
         FOR i IN 1 .. l_msg_count
         LOOP
            fnd_msg_pub.get(p_msg_index                               => i
                          , p_encoded                                 => fnd_api.g_false
                          , p_data                                    => l_msg_data
                          , p_msg_index_out                           => l_msg_index_out);

            IF x_return_msg IS NULL
            THEN
               x_return_msg                              :=
                  SUBSTR(l_msg_data
                       , 1
                       , 250);
            ELSE
               x_return_msg                              :=
                     x_return_msg
                  || ','
                  || SUBSTR(l_msg_data
                          , 1
                          , 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line(   ' QUERY_QUANTITIES API Failure : '
                              || x_return_msg);
      END IF;
   END LOOP;

   CLOSE c_get_item_id;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   'UNEXP_ERROR : '
                           || SUBSTR(SQLERRM
                                   , 1
                                   , 250));
END;
/

Script to Remove the Duplicate Strings from a String and return Unique String (Comma Separated)

Below script can be used to remove the repeating (Duplicate String ) String from a string and return unique string Concatenated by comma (replace comma in below example with any string separator)


FUNCTION remove_duplicate_strings( p_string IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_temp_string                                VARCHAR2(4000);
   l_loop_cnt                                   NUMBER := 0;
   l_string                                     VARCHAR2(4000);
   l_final_sting                                VARCHAR2(4000) := ' ';

   TYPE string_tbl_typ IS TABLE OF VARCHAR2(4000)
      INDEX BY VARCHAR(4000);

   string_tbl                                   string_tbl_typ;
BEGIN
   l_temp_string                             :=
      REGEXP_REPLACE(
         p_string
       , '[,]+'
       , ',');

   LOOP
      l_loop_cnt                                := l_loop_cnt + 1;
      l_string                                  :=
         REGEXP_SUBSTR(
            p_string
          , '[^,]+'
          , 1
          , l_loop_cnt);

      EXIT WHEN l_string IS NULL;

      IF NOT string_tbl.EXISTS( l_string)
      THEN
         string_tbl( l_string)                     := l_string;
         l_final_sting                             :=
               l_final_sting
            || ','
            || l_string;
      END IF;
   END LOOP;

   l_final_sting                             := TRIM( BOTH ',' FROM TRIM( l_final_sting));

   RETURN l_final_sting;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(
            SQLERRM
         || CHR( 10)
         || DBMS_UTILITY.format_error_backtrace);
      RETURN p_string;
END;

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).