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;