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