Oracle function

create function

CREATE OR REPLACE Function Check_Bond_By_Item(p_Organization_Id Number,

                                              p_Dept_Part       Varchar2,

                                              p_ebpt_item_type  number)

  Return Varchar2 Is

  x_Hs_Code_t Msl_Bnd_Ems_Lines.Hs_Code_t%Type;

  x_site      varchar2(10);

  cursor Cur_ems is

    Select Ems_No

      From Msl_Bnd_Ems_Headers Mbeh

     Where Mbeh.Site = x_Site

       And Ems_Type = 8;

  x_Return Varchar2(2);

Begin

  begin

    Select Site  Into x_Site

      From Org_Organization_Web Oow

     Where Oow.Organization_Id = p_Organization_Id;

  Exception

    When Others Then

      x_Site := '';

  end;

  for C1r in Cur_ems loop

    Begin

      Select Hs_Code_t Into x_Hs_Code_t

        From Msl_Bnd_Ebpt

       where Dept_Part = p_Dept_Part

         And Ems_No = C1r.Ems_No

         And Hs_Code_t is not null

         And ebpt_item_type = p_ebpt_item_type

         And rownum = 1;

    Exception

      When Others Then

        Begin

          Select Hs_Code_t Into x_Hs_Code_t

            From Msl_Bnd_Twins_Ebpt

           Where Dept_Part = p_Dept_Part

             And Ems_No = C1r.Ems_No

             And Hs_Code_t is not null

             And ebpt_item_type = p_ebpt_item_type

             And rownum = 1;

        Exception

          When Others Then

            x_Return := 'N';

        End;

    End;

    If x_Hs_Code_t is not null then

      x_Return := 'Y';

      goto return_value;

    End If;

  end loop;

  <<return_value>>

  Return nvl(x_Return, 'N');

End;