Function ot get the on hand quantity by passing item code as parameter

CREATE OR REPLACE FUNCTION xx_get_on_hand_qty (
   m_inventory_item   IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   concatedstring         VARCHAR2 (20000);
   v_item_desc            VARCHAR2 (500);              --Added on 09-Jun-2009

   CURSOR get_on_hand_qty (
      c_segment1   IN   VARCHAR2
   )
   IS
      SELECT   SUM (a.primary_quantity) qty_on_hand,
               b.description,               

           --Added on 09-Jun-2009
               b.primary_uom_code uom_code,
               c.NAME org_name,
               c.organization_id
          FROM mtl_material_transactions a,
               mtl_system_items_b b,
               hr_organization_units c
         WHERE a.organization_id = b.organization_id
           AND a.inventory_item_id = b.inventory_item_id
           AND a.organization_id = c.organization_id
           AND b.organization_id = c.organization_id
           AND b.segment1 = c_segment1
      GROUP BY b.segment1,
               b.primary_uom_code,
               a.inventory_item_id,
               c.NAME,
               c.organization_id,
               b.description                            --Added on 09-Jun-2009
        HAVING SUM (a.primary_quantity) > 0      ---1 --Changed on 09-Jun-2009
      ORDER BY c.organization_id;

   lcu_get_tax_type_rec   get_on_hand_qty%ROWTYPE;
BEGIN
   --Added Below on 09-Jun-2009
   BEGIN
      SELECT DISTINCT description
                 INTO v_item_desc
                 FROM mtl_system_items_b msi
                WHERE msi.segment1 = m_inventory_item;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_item_desc := NULL;
   END;

   --End of Added on 09-Jun-2009
   concatedstring :=
      (   'The ON Hand Quantity for the ITEM : '
       || m_inventory_item
       || ': '
       || v_item_desc
       || ' IS '
       || CHR (10)
       || CHR (10)
      );

   OPEN get_on_hand_qty (m_inventory_item);

   FETCH get_on_hand_qty
    INTO lcu_get_tax_type_rec;

   IF get_on_hand_qty%NOTFOUND
   THEN
      concatedstring :=
         (   'The ON Hand Quantity for the ITEM : '
          || m_inventory_item
          || ' IS : 0'
         );

      CLOSE get_on_hand_qty;
   ELSE
      CLOSE get_on_hand_qty;

      FOR c1 IN get_on_hand_qty (m_inventory_item)
      LOOP
         concatedstring :=
               concatedstring
            || 'In : '
            || c1.org_name
            || ' Quantity Available : '
            || c1.qty_on_hand
            || CHR (10);
         DBMS_OUTPUT.put_line (concatedstring);
      END LOOP;
   END IF;

   concatedstring :=
         concatedstring
      || CHR (10)
      || 'I have checked with above circles and no spare stock is available for stock transfer.'
      || ' Hence this Indent is being raised.';
   --       RETURN (NVL(concatedstring),'Error');
   RETURN (concatedstring);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      concatedstring := 'Everything Failed!!!';
      RETURN (concatedstring);
   WHEN OTHERS
   THEN
      concatedstring :=
         (   'The ON Hand Quantity for the ITEM : '
          || m_inventory_item
          || ' IS : 0'
         );
      RETURN (concatedstring);
END xx_get_on_hand_qty;
/


CREATE OR REPLACE FUNCTION XX_GET_ON_HAND_QTY2(m_inventory_item IN VARCHAR2)
  RETURN VARCHAR2 IS
  concatedstring varchar2(20000);
  CURSOR get_on_hand_qty(c_segment1 IN VARCHAR2) IS
    SELECT SUM(a.primary_quantity) qty_on_hand,
           b.primary_uom_code uom_code,
           c.NAME ORG_NAME,
           c.organization_id
      FROM mtl_material_transactions a,
           mtl_system_items_b        b,
           hr_organization_units     c
     WHERE a.organization_id = b.organization_id
       AND a.inventory_item_id = b.inventory_item_id
       AND a.organization_id = c.organization_id
       AND b.organization_id = c.organization_id
       AND b.SEGMENT1 = c_segment1
     GROUP BY b.segment1,
              b.primary_uom_code,
              a.inventory_item_id,
              c.name,
              c.organization_id
    HAVING SUM(a.primary_quantity) > -1
     ORDER BY c.organization_id;
  lcu_get_tax_type_rec get_on_hand_qty%ROWTYPE;

BEGIN
  concatedstring := ('The ON Hand Quantity for the ITEM : ' ||
                    m_inventory_item || ' IS ' || CHR(10) || CHR(10));

  OPEN get_on_hand_qty(m_inventory_item);

  FETCH get_on_hand_qty
    into lcu_get_tax_type_rec;

  IF get_on_hand_qty%NOTFOUND THEN
 
    concatedstring := ('The ON Hand Quantity for the ITEM : ' ||
                      m_inventory_item || ' IS : 0');
    CLOSE get_on_hand_qty;
 
  ELSE
    CLOSE get_on_hand_qty;
    FOR C1 IN get_on_hand_qty(m_inventory_item) LOOP
      concatedstring := concatedstring || 'In : ' || C1.ORG_NAME ||
                        ' Quantity Available : ' || C1.qty_on_hand ||
                        CHR(10);
      dbms_output.put_line(concatedstring);
    END LOOP;
 
  END IF;
  --       RETURN (NVL(concatedstring),'Error');
  RETURN(concatedstring);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    concatedstring := 'Everything Failed!!!';
    RETURN(concatedstring);
  WHEN OTHERS THEN
    concatedstring := (SUBSTR(SQLCODE, 1, 6) || ': ' ||
                      SUBSTR(SQLERRM, 1, 200) /*'The ON Hand Quantity for the ITEM : ' || m_inventory_item || ' IS : 0'*/
                      );
    dbms_output.put_line(concatedstring);
    RETURN(concatedstring);
END XX_GET_ON_HAND_QTY2;
/


CREATE OR REPLACE FUNCTION xx_get_on_hand_qty4 (
   m_inventory_item   IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   concatedstring         VARCHAR2 (20000);
   concatedstring1         VARCHAR2 (20000);  
   v_item_desc            VARCHAR2 (500);              --Added on 09-Jun-2009

   CURSOR get_on_hand_qty (
      c_segment1   IN   VARCHAR2
   )
   IS     
      select
       sum(transaction_quantity) qty_on_hand,
       mtlb.description,
       mtlb.primary_uom_code uom_code,
       getorg(mtlo.organization_id) org_name,
       mtlo.organization_id
      from
          mtl_onhand_quantities mtlo,
          mtl_system_items_b mtlb
     where
       mtlb.inventory_item_id = mtlo.inventory_item_id
       and mtlb.organization_id = mtlo.organization_id
       and mtlb.segment1 = c_segment1
     group by mtlb.description, mtlb.primary_uom_code, mtlo.organization_id;

   lcu_get_tax_type_rec   get_on_hand_qty%ROWTYPE;
BEGIN

      FOR c1 IN get_on_hand_qty (m_inventory_item)
      LOOP
         concatedstring1 :=
               concatedstring1
            || 'In : '
            || c1.org_name
            || ' Quantity Available : '
            || c1.qty_on_hand
            || CHR (10);
          
         if c1.description is not null then
            v_item_desc := c1.description;
         end if;
         DBMS_OUTPUT.put_line (concatedstring1);
      END LOOP;


   if concatedstring1 is null then
  
   BEGIN
      SELECT DISTINCT description
                 INTO v_item_desc
                 FROM mtl_system_items_b msi
                WHERE msi.segment1 = m_inventory_item;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_item_desc := NULL;
   END;
  
      concatedstring1 :=
      (   'The ON Hand Quantity for the ITEM : '
       || m_inventory_item
       || ': '
       || v_item_desc
       || ' IS '
       || CHR (10)
       || CHR (10)
      );
  
      concatedstring1 := concatedstring1 ||
         (   'The ON Hand Quantity for the ITEM : '
          || m_inventory_item
          || ' IS : 0'
         );
  elsif concatedstring1 is not null then
  
   concatedstring :=
      (   'The ON Hand Quantity for the ITEM : '
       || m_inventory_item
       || ': '
       || v_item_desc
       || ' IS '
       || CHR (10)
       || CHR (10)
      );
 
  --  concatedstring := concatedstring1;
 
  end if;

   concatedstring := concatedstring ||
         concatedstring1
      || CHR (10)
      || 'I have checked with above circles and no spare stock is available for stock transfer.'
      || ' Hence this Indent is being raised.';
   --       RETURN (NVL(concatedstring),'Error');
  
   RETURN (concatedstring);
  
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      concatedstring := 'Everything Failed!!!';
      RETURN (concatedstring);
   WHEN OTHERS
   THEN
      concatedstring :=
         (   'The ON Hand Quantity for the ITEM : '
          || m_inventory_item
          || ' IS : 0'
         );
      RETURN (concatedstring);
END xx_get_on_hand_qty4;
/