CREATE OR REPLACE PROCEDURE SAARANDA8.PRC_ACC_CONFIGURATION (
	i_operation IN CHAR DEFAULT NULL,
	i_option IN CHAR DEFAULT NULL,
	io_acc_id IN OUT NUMBER,
	i_acc_value IN VARCHAR2 DEFAULT NULL,
	i_acc_description IN VARCHAR2 DEFAULT NULL,
	i_codusuario IN NUMBER DEFAULT NULL,
	o_err_cod OUT NUMBER,
	o_err_msg OUT VARCHAR2,
	cur_out OUT PK_ASC_OBJECTS.asc_return_data
) AS

v_next_id NUMBER(10, 0);
v_exists NUMBER;

BEGIN

	/* Operaciones de consulta */
	OPEN cur_out FOR
	SELECT NULL
	FROM DUAL;

	IF i_operation = 'S' THEN

		/* Consulta */
		IF i_option = 'A' THEN

			OPEN cur_out FOR
			SELECT
				acc_id,
				acc_description,
				acc_value
			FROM ACC_CONFIGURATION ;

		END IF;

		IF i_option = 'B' THEN

			OPEN cur_out FOR
			SELECT
				acc_id,
				acc_value,
				acc_description
			FROM ACC_CONFIGURATION
			WHERE acc_id = io_acc_id;

		END IF;
 
		IF i_option = 'C' THEN

			SELECT COUNT(1)
			INTO v_exists
			FROM usuarios
			WHERE UPPER(email) = UPPER(i_acc_description)
			AND user_active = 1;

			IF v_exists > 0 THEN
				
				OPEN cur_out FOR
				SELECT 
					MAX(codusuario) codusuario
				FROM usuarios
				WHERE UPPER(email) = UPPER(i_acc_description)
				AND user_active = 1;
			
			ELSE
				
				OPEN cur_out FOR
				SELECT
					MAX(codusuario) codusuario
				FROM usuarios
				WHERE user_active = 1
				AND (
					UPPER(email) LIKE UPPER(i_acc_description) || ';%'
                    OR UPPER(email) LIKE '%;' || UPPER(i_acc_description)
                    OR UPPER(email) LIKE '%;' || UPPER(i_acc_description) || ';%'
				);
				
			END IF;

		END IF;

	END IF;

	/* Operaciones de insercion */
	IF i_operation = 'I' THEN

		IF i_option = 'A' THEN 

			SELECT NVL(MAX(acc_id), 0) + 1
			INTO v_next_id
			FROM ACC_CONFIGURATION;

			io_acc_id := v_next_id;

			INSERT INTO ACC_CONFIGURATION (
				acc_id,
				acc_description,
				acc_value
			) VALUES (
				io_acc_id,
				i_acc_description,
				i_acc_value
			);

		END IF;

	END IF;

	/* Operaciones de actualizacion */
	IF i_operation = 'U' THEN

		IF i_option = 'A' THEN

			UPDATE ACC_CONFIGURATION
			SET acc_value = i_acc_value
			WHERE acc_id = io_acc_id;

		END IF;

	END IF;

	/* Operaciones de borrado */
	IF i_operation = 'D' THEN

		IF i_option = 'A' THEN

			DELETE ACC_CONFIGURATION
			WHERE acc_id = io_acc_id;

		END IF;

	END IF;

END;
/