CREATE OR REPLACE PROCEDURE PRC_AFW_ITEM_VIEWS (
	--Standard variables
	i_operation IN CHAR,
	i_option IN CHAR,

	--View variables
	i_view_id IN NUMBER,
	i_adv_txt_search IN CHAR DEFAULT 'N', --N: None, M: Match words, S: Semantic
	i_language IN NUMBER DEFAULT 1,
	i_text_pattern VARCHAR2 DEFAULT '',

	--Paging variables
	i_where_clause IN VARCHAR2,
	i_order_clause IN VARCHAR2,
	i_ext_join IN VARCHAR2 DEFAULT NULL,
	i_page_number IN NUMBER,
	i_page_size IN NUMBER,
	o_total_rows OUT NUMBER,
	cur_out OUT SYS_REFCURSOR
) AS

/* Local variables */
v_select_clause VARCHAR2(32767);
v_sql_sentence VARCHAR2(32767);
v_from_clause VARCHAR2(32767);
v_ext_top_join VARCHAR2(32767);
v_ext_bot_join VARCHAR2(32767);
v_pattern VARCHAR2(2048);
v_wcard_pattern CHAR(23);
v_key VARCHAR2(30);
v_type VARCHAR2(30);
v_lower_bound NUMBER;
v_higer_bound NUMBER;
v_join_scope VARCHAR2(4);

/* Tracing variables */
v_log_process SMALLINT;
v_procedure_name VARCHAR2(30);
v_start_date DATE;
v_end_date DATE;
v_execution_time NUMBER(11, 0);
v_err_cod NUMBER;
v_err_msg VARCHAR2(255);

BEGIN

	--Selection operations
	IF i_operation = 'S' THEN
		
		SELECT 'SELECT' || CHR(10) || CHR(9) || REPLACE(LOWER(columns), ',', ',' || CHR(10) || CHR(9)),
			'FROM ' || ref_name
		INTO v_select_clause,
			v_from_clause
		FROM AFW_VIEWS
		WHERE view_id = i_view_id;

		v_lower_bound := i_page_size * i_page_number - i_page_size + 1;
		v_higer_bound := i_page_size * i_page_number;

		IF NOT REGEXP_LIKE(i_ext_join, '[[:alpha:]]') AND REGEXP_LIKE(i_ext_join, '[0-9]') THEN
		BEGIN
			
			SELECT j.join_text,
				CASE WHEN ISNULL (j.columns, '') = '' THEN 'TOP' ELSE 'BOTH' END
			INTO v_ext_top_join,
				v_join_scope
			FROM AFW_JOINS j
			JOIN AFW_VIEW_JOINS vj ON vj.join_id = j.join_id
			WHERE j.join_id = TO_NUMBER(i_ext_join)
			AND vj.view_id = i_view_id;

		EXCEPTION WHEN NO_DATA_FOUND THEN
			NULL;
		END;
		ELSE
			v_ext_top_join := i_ext_join;

			SELECT
				CASE WHEN has_external_columns = 1 THEN 'BOTH' ELSE 'TOP' END
			INTO v_join_scope
			FROM AFW_VIEWS
			WHERE view_id = i_view_id;

		END IF;

		IF v_join_scope = 'BOTH' THEN
			v_ext_bot_join := v_ext_top_join;

		END IF;

		IF i_adv_txt_search = 'M' THEN
			v_ext_top_join := v_ext_top_join || CHR(10) || FUN_AFW_FULLTEXT_JOIN(i_text_pattern);

		END IF;

		IF i_option = 'A' THEN

			v_type := 'V';
			v_key := 'ComposedId';

		ELSIF i_option = 'B' THEN
			
			v_type := 'N';
			v_key := 'CiId';

		ELSIF i_option = 'C' THEN
			
			v_type := 'N';
			v_key := 'CodUsuario';

		ELSIF i_option = 'D' THEN
			
			v_type := 'N';
			v_key := 'SolutionId';

		END IF;

	ELSIF i_operation = 'I' THEN

		RAISE_APPLICATION_ERROR(-20001, 'Operation not implemented');

	ELSIF i_operation = 'D' THEN

		RAISE_APPLICATION_ERROR(-20001, 'Operation not implemented');

	ELSIF i_operation = 'U' THEN

		RAISE_APPLICATION_ERROR(-20001, 'Operation not implemented');

	END IF;

	v_sql_sentence := REPLACE (
		REPLACE (
			'DECLARE page0 T_PAGE_ROW;'
			|| CHR(10) || '	page T_PAGE_ROW;'
			|| CHR(10) || CHR(10) || 'BEGIN'
			|| CHR(10) || CHR(10) || CHR(9) || 'SELECT'
			|| CHR(10) || CHR(9) || '	PAGE_ROW ('
			|| CHR(10) || CHR(9) || CASE v_type WHEN 'N' THEN '		{key},' ELSE '		NULL,' END
			|| CHR(10) || CHR(9) || CASE v_type WHEN 'V' THEN '		{key},' ELSE '		NULL,' END
			|| CHR(10) || CHR(9) || '		ROW_NUMBER() OVER(ORDER BY {order})'
			|| CHR(10) || CHR(9) || '	)'
			|| CHR(10) || CHR(9) || 'BULK COLLECT INTO page0'
			|| CHR(10) || CHR(9) || v_from_clause || ' t'
			|| CHR(10) || CHR(9) || v_ext_top_join
			|| CHR(10) || CHR(9) || 'WHERE ' || i_where_clause || ';'
			|| CHR(10) || CHR(10) || CHR(9) || ':o_rows := SQL%ROWCOUNT;'
			|| CHR(10) || CHR(10) || CHR(9) || 'SELECT'
			|| CHR(10) || CHR(9) || '	PAGE_ROW ('
			|| CHR(10) || CHR(9) || '		RowKeyN,'
			|| CHR(10) || CHR(9) || '		RowKeyV,'
			|| CHR(10) || CHR(9) || '		n'
			|| CHR(10) || CHR(9) || '	)'
			|| CHR(10) || CHR(9) || 'BULK COLLECT INTO page'
			|| CHR(10) || CHR(9) || 'FROM TABLE(page0)'
			|| CHR(10) || CHR(9) || 'WHERE n BETWEEN :i_min AND :i_max;'
			|| CHR(10) || CHR(10) || CHR(9) || 'OPEN :cur_out FOR'
			|| CHR(10) || v_select_clause
			|| CHR(10) || CHR(9) || v_from_clause || ' t'
			|| CHR(10) || CHR(9) || 'JOIN TABLE(page) c ON c.RowKey' || v_type || ' = t.{key}'
			|| CHR(10) || CHR(9) || v_ext_bot_join
			|| CHR(10) || CHR(9) || 'ORDER BY c.n' || ';'
			|| CHR(10) || CHR(10) || 'END;',
			'{key}',
			v_key
		),
		'{order}',
		NVL(i_order_clause, v_key)
	);
	
	BEGIN
		
		IF i_view_id IN (9, 10) THEN
			
			v_wcard_pattern := '[[:space:]]';
			v_pattern := v_wcard_pattern || REPLACE (FUN_AFW_GET_STRING_AT (i_text_pattern, 0, '|'), ' ', v_wcard_pattern || '|' || v_wcard_pattern) || v_wcard_pattern;

			EXECUTE IMMEDIATE
				v_sql_sentence
			USING
				OUT o_total_rows,
				v_lower_bound,
				v_higer_bound,
				cur_out,
				v_pattern;

		ELSE

			EXECUTE IMMEDIATE
				v_sql_sentence
			USING
				OUT o_total_rows,
				v_lower_bound,
				v_higer_bound,
				cur_out;

		END IF;
		
		IF v_log_process = 1 THEN
			
			v_end_date := SYSDATE;
			v_execution_time := (v_end_date - v_start_date) / 24 / 60 / 60 / 1000;

			INSERT INTO AFW_SQL_TIMES (
				sqlt_database_object,
				sqlt_operation,
				sqlt_option,
				sqlt_started,
				sqlt_finished,
				sqlt_elapsed_cs,
				sqlt_sentence
			) VALUES (
				v_procedure_name,
				i_operation,
				i_option,
				v_start_date,
				v_end_date,
				v_execution_time,
				v_sql_sentence
			);
		
		END IF;

	EXCEPTION WHEN OTHERS THEN
		
		EXCEPTION_HANDLER (
			i_procedure_operation => i_operation,
			i_procedure_option => i_option,
			i_error_sentence => v_sql_sentence,
			i_error_procedure => v_procedure_name,
			o_return_number => v_err_cod,
			o_return_message => v_err_msg
		);

	END;

END;