pg

-- Function: lst_aut_ope_pen(bigint, bigint, bigint, bigint, bigint, bigint)

-- DROP FUNCTION lst_aut_ope_pen(bigint, bigint, bigint, bigint, bigint, bigint);

CREATE OR REPLACE FUNCTION lst_aut_ope_pen(
    idclt bigint,
    idemp bigint,
    idpro bigint,
    idusremp bigint,
    numpag bigint,
    catperpag bigint)
  RETURNS SETOF type_aut_leg_pen AS
$BODY$

DECLARE totalRegistros BIGINT := 0;
	flagHolding BOOLEAN := false;
	idEmpresa BIGINT := 0;

BEGIN
	/*
	** Autor: avalenzuela
	** Fecha: 02-07-2015
	** Descripción: Listado de autorizaciones operativas pendientes por usuario.
	**
	** Modificación
	** Autor: avalenzuela
	** Fecha: 22-08-2106
	** Descripción: Se corrige la query para que liste los usuarios genericos.
	**
	** Modificación
	** Autor: avalenzuela
	** Fecha: 21-10-2106
	** Descripción: Se incorpora el tipo de producto para buscar autorizadores.
	*/

	idEmpresa := idemp;
	IF idEmpresa < 0 THEN
		idEmpresa:=(idEmpresa*-1);
		flagHolding:=true;
	END IF;

	SELECT
		count(1) INTO totalRegistros
	FROM
		autorizaciones aut
		JOIN rel_autpro_aut relaut
		ON relaut.id_aut = aut.id_aut
		JOIN autorizaciones_productos autpro
		ON relaut.id_autpro = autpro.id_autpro
		LEFT JOIN perfiles prf
		ON prf.id_prf = relaut.id_atz_resp
		JOIN rel_emp_pro relemppro
		ON autpro.id_relemppro = relemppro.id_relemppro
		JOIN empresas emp
		ON relemppro.id_emp = emp.id_emp
		JOIN productos pro
		ON relemppro.id_pro = pro.id_pro
	WHERE
		emp.id_clt = idClt
		AND (emp.id_emp = idEmpresa OR (emp.emp_id_emp = idEmpresa AND flagHolding))
		AND (idPro = 0 OR pro.id_pro = idPro) --Cero todos los productos
		AND pro.id_tippro = (SELECT id_tippro FROM tipos_productos WHERE cod_tippro = 'operacionales')
		AND upper(relaut.est_rel_aut) = 'P'--Listo solo las pendientes
		AND upper(aut.est_aut) = 'P'
		AND autpro.est_autpro in ('H','M')
		AND (idUsrEmp = -1 OR (relaut.id_atz_resp = 2 OR relaut.id_atz_resp IN (SELECT id_atz FROM autorizadores WHERE id_val_atz = idUsrEmp AND id_tippro = (SELECT id_tippro FROM tipos_productos WHERE cod_tippro = 'operacionales')))) -- [id_atz_resp = 2] id de genérico operacionales
		AND relaut.ord_rel_aut = (
			SELECT min(relauttmp.ord_rel_aut)
			FROM rel_autpro_aut relauttmp
			WHERE relauttmp.id_autpro = autpro.id_autpro
				AND relauttmp.id_aut = aut.id_aut
				AND relauttmp.est_rel_aut = 'P'
			)
		AND 0 = (
			SELECT count(1)
			FROM rel_autpro_aut relauttmp
			WHERE relauttmp.id_autpro = autpro.id_autpro
				AND relauttmp.id_aut = aut.id_aut
				AND relauttmp.est_rel_aut <> 'P'
				AND id_atz_mod = idUsrEmp
			);

	RETURN QUERY (
		SELECT
			id_aut,
			id_rel_aut,
			id_trx_aut,
			fec_ing_trx_aut,
			fec_exp_trx_aut,
			est_aut,
			id_autpro,
			id_atz_resp,
			tot_atz,
			can_atz,
			id_pro,
			dsc_pro,
			id_emp,
			raz_soc_emp,
			totalRegistros
		FROM (
		SELECT
			aut.id_aut,
			relaut.id_rel_aut,
			aut.id_trx_aut,
			aut.fec_ing_trx_aut,
			aut.fec_exp_trx_aut,
			aut.est_aut,
			autpro.id_autpro,
			relaut.id_atz_resp,
			(select count(1) from rel_autpro_aut where id_aut = aut.id_aut and id_autpro = autpro.id_autpro) as tot_atz,
			(select count(1) from rel_autpro_aut where id_aut = aut.id_aut and id_autpro = autpro.id_autpro and est_rel_aut = 'A') as can_atz,
			pro.id_pro,
			pro.dsc_pro,
			emp.id_emp,
			emp.raz_soc_emp,
			row_number() over() as pagina
		FROM
			autorizaciones aut
			JOIN rel_autpro_aut relaut
			ON relaut.id_aut = aut.id_aut
			JOIN autorizaciones_productos autpro
			ON relaut.id_autpro = autpro.id_autpro
			LEFT JOIN perfiles prf
			ON prf.id_prf = relaut.id_atz_resp
			JOIN rel_emp_pro relemppro
			ON autpro.id_relemppro = relemppro.id_relemppro
			JOIN empresas emp
			ON relemppro.id_emp = emp.id_emp
			JOIN productos pro
			ON relemppro.id_pro = pro.id_pro
		WHERE
			emp.id_clt = idClt
			AND (emp.id_emp = idEmpresa OR (emp.emp_id_emp = idEmpresa AND flagHolding))
			AND (idPro = 0 OR pro.id_pro = idPro) --Cero todos los productos
			AND pro.id_tippro = (SELECT id_tippro FROM tipos_productos WHERE cod_tippro = 'operacionales')
			AND upper(relaut.est_rel_aut) = 'P'--Listo solo las pendientes
			AND upper(aut.est_aut) = 'P'
			AND autpro.est_autpro in ('H','M')
			AND (idUsrEmp = -1 OR (relaut.id_atz_resp = 2 OR relaut.id_atz_resp IN (SELECT id_atz FROM autorizadores WHERE id_val_atz = idUsrEmp AND id_tippro = (SELECT id_tippro FROM tipos_productos WHERE cod_tippro = 'operacionales')))) -- [id_atz_resp = 2] id de genérico operacionales
			AND (idUsrEmp = -1 OR (autpro.atz_ord_cre = 'N' OR (relaut.ord_rel_aut = (
				SELECT min(relauttmp.ord_rel_aut)
				FROM rel_autpro_aut relauttmp
				WHERE relauttmp.id_autpro = autpro.id_autpro
					AND relauttmp.id_aut = aut.id_aut
					AND relauttmp.est_rel_aut = 'P'
				)
			AND 0 = (
				SELECT count(1)
				FROM rel_autpro_aut relauttmp
				WHERE relauttmp.id_autpro = autpro.id_autpro
					AND relauttmp.id_aut = aut.id_aut
					AND relauttmp.est_rel_aut <> 'P'
					AND id_atz_mod IN (SELECT id_atz FROM autorizadores WHERE id_val_atz = idUsrEmp)
				))))
			) as tabla
		WHERE
			numPag = 0 OR pagina BETWEEN (((numPag - 1) * catPerPag)+1) AND (numPag * catPerPag)
		ORDER BY id_aut ASC
	);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION lst_aut_ope_pen(bigint, bigint, bigint, bigint, bigint, bigint)
  OWNER TO postgres;
COMMENT ON FUNCTION lst_aut_ope_pen(bigint, bigint, bigint, bigint, bigint, bigint) IS 'Listado de autorizaciones operativas pendientes por perfil.';