XUDF Postgres

XUDF Postgres

-- **************************************************************************
-- gart_unidefs_get_canbase
-- DEISTER WebStudio XSQL-UDFUNC  -  Engine: postgres
-- **************************************************************************
CREATE OR REPLACE FUNCTION gart_unidefs_get_canbase(    p_codart IN gvenpedl.codart%TYPE,
    p_varlog IN gvenpedl.varlog%TYPE,
    p_udmdoc IN gvenpedl.udmven%TYPE,
    p_udmalt IN gvenpedl.udmalt%TYPE,
    p_candoc IN gvenpedl.canped%TYPE,
    p_canalt IN gvenpedl.canalt%TYPE
)
 RETURNS  decimal(12,3)
AS $$
DECLARE
    -- =================================
    -- Definition of variables
    -- =================================
    m_udmbas garticul.udmbas%TYPE;
    m_udmaux garticul.udmaux%TYPE;
    m_udmrel gart_unidefs.udmrel%TYPE;
    m_candes gvenpedl.canped%TYPE;
    m_msg VARCHAR(255);
BEGIN
    -- =================================
    -- Function body
    -- =================================
    m_candes := NULL;
    SELECT udmbas, udmaux
           INTO m_udmbas, m_udmaux
           FROM garticul
          WHERE codigo  = p_codart
        ;
    IF m_udmbas IS NULL THEN
      m_msg :='Artículo: [' || p_codart || '] no encontrado en garticul';
      RAISE EXCEPTION '%', m_msg;
    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;
    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
      m_candes := gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar), m_udmbas, p_candoc, CAST(NULL AS decimal));
    END IF;
    IF m_candes IS NULL AND p_udmalt IS NOT NULL THEN
      m_udmrel := gart_unidefs_get_udmrel(
                    p_codart, p_varlog, p_udmalt
                );
      IF m_udmrel = 'B' THEN
        m_candes := gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmalt, CAST(NULL AS varchar), m_udmbas, p_canalt, CAST(NULL AS decimal));
      ELSE
              m_candes := gart_uniconv_get_cantid(CAST(0 AS smallint), p_codart, p_varlog, p_udmdoc, CAST(NULL AS varchar), m_udmbas, p_candoc, CAST(NULL AS decimal));

      END IF;
    END IF;
    RETURN m_candes;

END
$$ LANGUAGE 'plpgsql';