UkbBR5kq

1CREATE TABLE td_consumoconcepto
2(
3  td_idconsumoconcepto character varying(12) NOT NULL,
4  tm_idproyecto character varying(10),
5  tm_idpropiedad character varying(10),
6  tm_idconcepto character varying(10),
7  tm_per_mes smallint,
8  tm_per_ano smallint,
9  td_importe numeric(9,2),
10  activo smallint,
11  idusuarioreg integer,
12  fechareg timestamp without time zone,
13  idusuarioact integer,
14  fechaact timestamp without time zone,
15  CONSTRAINT td_consumoconcepto_pkey PRIMARY KEY (td_idconsumoconcepto)
16)
17WITH (
18  OIDS=FALSE
19);
20ALTER TABLE td_consumoconcepto
21  OWNER TO postgres;
22
23DROP FUNCTION IF EXISTS pa_propiedad_consumo_concepto_listar(character varying, character varying, smallint, smallint, character varying, character varying);
24
25CREATE OR REPLACE FUNCTION pa_propiedad_consumo_concepto_listar(
26IN tipo character varying, 
27IN idproyecto character varying,
28IN anho smallint,
29IN mes smallint,
30IN idpropiedad character varying,
31IN idconcepto character varying,
32OUT td_idconsumoescalonable character varying,
33OUT tm_idconcepto character varying,
34OUT tm_idpropiedad character varying,
35OUT tm_descripcionpropiedad character varying,
36OUT td_importe numeric)
37  RETURNS SETOF record AS
38$BODY$
39
40BEGIN
41  IF tipo = '1' THEN 
42	RETURN QUERY SELECT '0'::character varying, idconcepto, a.tm_idpropiedad, a.tm_descripcionpropiedad, 0::numeric
43	FROM tm_propiedad AS a 
44		LEFT JOIN td_consumoconcepto AS conscpto ON a.tm_idpropiedad = conscpto.tm_idpropiedad
45	WHERE a.tm_idproyecto = idproyecto AND a.tm_idtipopropiedad = 'DPT' AND a.activo = 1;
46   ELSIF tipo = '2' THEN
47
48     RETURN QUERY SELECT COALESCE(b.td_idconsumoconcepto, '0'), idconcepto, a.tm_idpropiedad, a.tm_descripcionpropiedad, COALESCE(b.td_importe, 0)
49
50     FROM tm_propiedad AS a 
51
52        LEFT JOIN td_consumoconcepto AS b ON a.tm_idpropiedad = b.tm_idpropiedad AND b.tm_per_ano = anho AND b.tm_per_mes = mes AND b.activo = 1 AND b.tm_idconcepto = idconcepto
53
54     WHERE a.tm_idproyecto = idproyecto AND a.tm_idtipopropiedad = 'DPT' AND a.activo = 1;
55
56  END IF;
57END;
58
59$BODY$
60  LANGUAGE plpgsql VOLATILE
61  COST 100
62  ROWS 1000;
63ALTER FUNCTION pa_propiedad_consumo_concepto_listar(character varying, character varying, smallint, smallint, character varying, character varying)
64  OWNER TO postgres;
65
66DROP FUNCTION IF EXISTS pa_propiedad_concepto_exportar(character varying, character varying);
67
68CREATE OR REPLACE FUNCTION pa_propiedad_concepto_exportar(IN idproyecto character varying, IN idconcepto character varying, OUT codigoproyecto character varying, OUT idpropiedad character varying, OUT propiedad character varying, OUT tm_idconcepto character varying, OUT importe numeric)
69  RETURNS SETOF record AS
70$BODY$
71BEGIN
72	
73	RETURN QUERY SELECT tm_idproyecto, tm_idpropiedad, tm_descripcionpropiedad, idconcepto, 0::numeric
74	FROM tm_propiedad WHERE tm_idproyecto = idproyecto AND tm_idtipopropiedad = 'DPT' AND activo = 1;
75END$BODY$
76  LANGUAGE plpgsql VOLATILE
77  COST 100
78  ROWS 1000;
79ALTER FUNCTION pa_propiedad_concepto_exportar(character varying, character varying)
80  OWNER TO postgres;