XUDF Oracle

XUDF Oracle

-- **************************************************************************
-- gart_unidefs_get_canbase
-- DEISTER WebStudio XSQL-UDFUNC  -  Engine: oracle
-- **************************************************************************
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

)
    RETURN  number
AS
    -- =================================
    -- Definition of variables
    -- =================================
    m_udmbas garticul.udmbas%TYPE;
    m_udmaux garticul.udmaux%TYPE;
    m_udmrel gart_unidefs.udmrel%TYPE;
    m_candes gvenpedl.canped%TYPE;

    -- =====================================================================
    -- Debug
    -- To activate debug mesages, execute command line $ set serveroutput on
    -- =====================================================================


BEGIN
    -- =================================
    -- Function body
    -- =================================
    m_candes := NULL;
    BEGIN
    SELECT udmbas, udmaux
           INTO m_udmbas, m_udmaux
           FROM garticul
          WHERE codigo  = p_codart
        ;
    EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
    END;
    IF m_udmbas IS NULL THEN
      RAISE_APPLICATION_ERROR (-20000, 'Artículo: [' || p_codart || '] no encontrado en garticul');
    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(0, p_codart, p_varlog, p_udmdoc, NULL, m_udmbas, p_candoc, NULL);
    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(0, p_codart, p_varlog, p_udmalt, NULL, m_udmbas, p_canalt, NULL);
      ELSE
              m_candes := gart_uniconv_get_cantid(0, p_codart, p_varlog, p_udmdoc, NULL, m_udmbas, p_candoc, NULL);

      END IF;
    END IF;
    RETURN m_candes;

END;
.
run