XUDF Db2u

XUDF Db2u

-- **************************************************************************
-- gart_unidefs_get_canbase
-- DEISTER WebStudio XSQL-UDFUNC  -  Engine: db2u
-- **************************************************************************
CREATE FUNCTION gart_unidefs_get_canbase(
    p_codart ,
    p_varlog ,
    p_udmdoc ,
    p_udmalt ,
    p_candoc ,
    p_canalt 
)
    RETURNS  decimal(12,3)
    NO EXTERNAL ACTION NOT DETERMINISTIC READS SQL DATA
BEGIN ATOMIC
    -- Variable para los exceptions
    DECLARE m_errorMessageText char(80);

    -- =================================
    -- Definition of variables
    -- =================================
    DECLARE m_udmbas ;
    DECLARE m_udmaux ;
    DECLARE m_udmrel ;
    DECLARE m_candes ;

    -- =================================
    -- Function body
    -- =================================
    SET m_candes = NULL;
    SET (m_udmbas, m_udmaux) = (SELECT udmbas, udmaux
          
           FROM garticul
          WHERE codigo  = p_codart
        );
    IF m_udmbas IS NULL THEN
      SET m_errorMessageText = 'Artículo: [' || p_codart || '] no encontrado en garticul';
      SIGNAL SQLSTATE  '75002'
         SET MESSAGE_TEXT= m_errorMessageText;
    END IF;
    IF p_udmdoc = m_udmbas THEN
      RETURN p_candoc;
    END IF;
    IF p_udmalt IS NOT NULL AND p_udmalt = m_udmbas  THEN
      RETURN p_canalt;
    END IF;
    SET m_udmrel = gart_unidefs_get_udmrel(
            p_codart, p_varlog, p_udmdoc
        );
    IF 
                m_udmrel = 'B' OR (m_udmrel = 'A' AND p_udmalt IS NULL)
             THEN
      SET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar(254)), m_udmbas, p_candoc, CAST(NULL AS decimal));
    END IF;
    IF m_candes IS NULL AND p_udmalt IS NOT NULL THEN
      SET m_udmrel = gart_unidefs_get_udmrel(
                    p_codart, p_varlog, p_udmalt
                );
      IF m_udmrel = 'B' THEN
        SET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmalt, CAST(NULL AS varchar(254)), m_udmbas, p_canalt, CAST(NULL AS decimal));
      ELSE
              SET m_candes = gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar(254)), m_udmbas, p_candoc, CAST(NULL AS decimal));

      END IF;
    END IF;
    RETURN m_candes;
END;