Cacher is the code snippet organizer for pro developers

CREATE OR REPLACE FUNCTION inventario_gral_seguro() 
 RETURNS TABLE (
 referencia VARCHAR,
 unidad varchar,
 descripcion varchar,
 precio numeric(9,2),
 categoria varchar,
 subcategoria varchar,
 subtipo varchar,
 cantidad numeric(9,2),
 total numeric(9,2),
 paca_madre varchar,
 almacen varchar
) 
AS $$
BEGIN
 RETURN QUERY 
 
WITH inventario AS(
	SELECT
		CAST(a.f_referencia as varchar) AS referencia,
		--CASE
		--	WHEN a.f_idcategoria = 32 THEN CAST('LB' as varchar)
		--	WHEN a.f_idcategoria = 1 THEN CAST('UNITS' as varchar)
		--	ELSE CAST(b.f_descripcion as varchar)
		--END 
		CAST(b.f_descripcion as varchar) AS unidad,
		CAST(a.f_descripcion as varchar) AS descripcion,
		CASE
			WHEN(
				a.f_idcategoria = 77
				OR(
					a.f_referencia SIMILAR TO '[A-Z]\d{4}'
					AND a.f_idcategoria = 32
				)
			)
			AND(
				a.f_descripcion NOT ilike '%TALLO%'
				AND a.f_descripcion NOT ilike '%PICADURA%'
			) then CAST(get_costo_producto(trans.paca) as numeric(9,2))

			ELSE CAST(
				get_costo_producto(a.f_referencia) AS NUMERIC(
					9,
					2
				)
			)
		END AS precio,
		CASE
			WHEN a.f_idcategoria IN(
				68,
				69,
				54,
				52,
				63,
				56,
				69,
				53,
				55,
				71,
				66,
				58,
				60,
				65,
				67,
				59,
				57,
				61,
				62,
				64
			) THEN CAST('MATERIALES EMPAQUE' as varchar)
			ELSE CAST(get_categoria(a.f_idcategoria) as varchar)
		END AS categoria,
		CASE
			WHEN a.f_idcategoria = 32 THEN CAST(d.f_descripcion as varchar)
			WHEN c.f_almacen IS NOT null and a.f_idcategoria = 1 THEN CAST(get_descripcion_almacen(c.f_almacen) as varchar)
			ELSE CAST(get_categoria(a.f_idcategoria) as varchar)
		END AS subcategoria,
		CAST(d.f_descripcion as varchar) AS subtipo,
		CAST(get_existencia_general_almacen(a.f_referencia) as numeric(9,2)) AS cantidad,
		CAST(get_existencia_general_almacen(a.f_referencia) as numeric(9,2))* CASE
			WHEN(
				a.f_idcategoria = 77
				OR(
					a.f_referencia SIMILAR TO '[A-Z]\d{4}'
					AND a.f_idcategoria = 32
				)
			)
			AND(
				a.f_descripcion NOT ilike '%TALLO%'
				AND a.f_descripcion NOT ilike '%PICADURA%'
			) then CAST(get_costo_producto(trans.paca) as numeric(9,2))

			ELSE CAST(
				get_costo_producto(a.f_referencia) AS NUMERIC(
					9,
					2
				)
			)
		END AS total, CAST(trans.paca as varchar) AS paca_madre, CAST(c.f_almacen as varchar) as almacen
	FROM
		t_productos_sucursal a
	LEFT JOIN t_unidades b ON
		a.f_idunidad = b.f_idunidad
	LEFT JOIN t_almacen_productos c ON
		c.f_referencia = a.f_referencia
		AND get_descripcion_almacen(c.f_almacen) ilike '%SEMI%'
		AND c.f_existencia > 0
    LEFT JOIN (
    with derivados as(
    select max(f_cantidad) as cantidad,
    SUBSTRING(f_serie from '[A-Z\*]{3,}\d{10,}') as paca,
    f_referencia
    from produccion.t_detalle_transformacion_paca
	group by f_referencia, paca)
    select d1.f_referencia, d1.paca, d1.cantidad
    from derivados d1, 
    	(
    	select max(d0.cantidad) as qty, d0.f_referencia as reference from derivados d0 group by d0.f_referencia
    	) d2 where d1.cantidad = d2.qty and d1.f_referencia = d2.reference    	
    ) trans on 
    trans.f_referencia = a.f_referencia
	LEFT JOIN t_subtipos d ON
		d.f_id = a.f_subtipo
	WHERE
		a.f_existencia <> 0
		AND a.f_idcategoria NOT IN(
			76,
			79,
			80,
			75
		)
) SELECT
	*
FROM
	inventario
/*WHERE
	precio IS NOT NULL*/
ORDER BY
	categoria DESC,
	subcategoria,
	subtipo ;
	
END; $$
LANGUAGE 'plpgsql';