--------------------------------------------------------
--  DDL for Function FNC_AD_AUTH
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE FUNCTION "ENVX_OPER"."FNC_AD_AUTH" (
        p_username   IN   VARCHAR2,
        p_password   IN   VARCHAR2 
    ) RETURN BOOLEAN IS

        l_ldap_host   VARCHAR2(500) := 'ldaplc.lc.gc.local';
        l_ldap_port   VARCHAR2(500) := '389';
        l_ldap_base   VARCHAR2(500) := 'DC=lc,DC=gc,DC=local';
        l_dn_user     VARCHAR2(500);
        l_retval      PLS_INTEGER;
        l_session     dbms_ldap.session;
		v_boolean BOOLEAN;
        log_program_name    VARCHAR2(50) := 'pkg_sicd_commons.fnc_ad_auth';
    BEGIN

        IF p_username IS NULL OR p_password IS NULL THEN
            raise_application_error(-20000, 'Debe especificar sus credenciales.');
        END IF;

        dbms_ldap.use_exception := true;

        SELECT
            dn
        INTO l_dn_user
        FROM
            TABLE ( apex_ldap.search(
            p_username => 'CN=LCU SICD LDAP,OU=Usuarios de Servicios,DC=lc,DC=gc,DC=local', 
            p_pass => 'martesSICDsegura10', 
            p_host => 'ldaplc.lc.gc.local', 
            p_search_base => 'DC=lc,DC=gc,DC=local', 
            p_search_filter => '&(objectClass=person)(userPrincipalName='||p_username||'@lc.gc.local)(memberOf=CN=LCGU_SICD_Users,OU=Grupos,DC=lc,DC=gc,DC=local)',
            p_attribute_names => 'CN') );

        IF length(l_dn_user) > 1 THEN

            l_session := DBMS_LDAP.init(l_ldap_host,l_ldap_port);
            l_retval := dbms_ldap.simple_bind_s(ld => l_session, dn => l_dn_user, passwd => p_password);
            l_retval := dbms_ldap.unbind_s(ld => l_session);
			v_boolean := true;
            RETURN v_boolean;

        END IF;

        v_boolean := false;
		RETURN v_boolean;

    EXCEPTION

        WHEN OTHERS THEN
            apex_util.set_custom_auth_status(p_status => 'Usuario o Contraseña incorrectos');

            IF length(l_session) > 1 THEN
                l_retval := dbms_ldap.unbind_s(ld => l_session);
            END IF;

            ------------------------------------------------------------------        
            log_collector(
                pi_program_name  => log_program_name,
                pi_log_level     => 2, --- WARNING
                pi_log_message   => 'ERROR: ' || sqlerrm
            );
            ------------------------------------------------------------------

            v_boolean := false;
            RETURN v_boolean;
    END;

/