1 | /*Ejecutar antes de restaurar.*/
|
---|
2 |
|
---|
3 | *- Crear el usuario "replicacion" contraseña "hwxn440"
|
---|
4 | (en caso de olvidarse y correr la restauracion correr esto:
|
---|
5 | GRANT ALL ON SCHEMA replicacion TO replicacion;
|
---|
6 | GRANT ALL ON TABLE replicacion.rep_instrucciones TO replicacion;
|
---|
7 | despues de restaurar)
|
---|
8 |
|
---|
9 | CREATE OR REPLACE FUNCTION comp_mon_cotiz_base(integer)
|
---|
10 | RETURNS double precision AS
|
---|
11 | $BODY$--recibe id de comp de cliente y devuelve cotiz. de la moneda del comprobante
|
---|
12 | select 1::float8$BODY$
|
---|
13 | LANGUAGE 'sql' IMMUTABLE;
|
---|
14 |
|
---|
15 | CREATE OR REPLACE FUNCTION art_comp_en_rd(integer, integer)
|
---|
16 | RETURNS integer AS
|
---|
17 | $BODY$select 0::int4$BODY$
|
---|
18 | LANGUAGE 'sql' IMMUTABLE;
|
---|
19 |
|
---|
20 | CREATE OR REPLACE FUNCTION cant_cd_rd_por_comp(integer, integer)
|
---|
21 | RETURNS double precision AS
|
---|
22 | 'SELECT 0::float8'
|
---|
23 | LANGUAGE 'sql' IMMUTABLE;
|
---|
24 |
|
---|
25 | CREATE OR REPLACE FUNCTION cant_cd(integer, integer)
|
---|
26 | RETURNS double precision AS
|
---|
27 | 'SELECT 0::float8'
|
---|
28 | LANGUAGE 'sql' IMMUTABLE;
|
---|
29 |
|
---|
30 | CREATE OR REPLACE FUNCTION art_rd_en_comp(integer, integer)
|
---|
31 | RETURNS integer AS
|
---|
32 | $BODY$select 0::int4$BODY$
|
---|
33 | LANGUAGE 'sql' STABLE;
|
---|
34 |
|
---|
35 | CREATE OR REPLACE FUNCTION art_rd_en_oc(integer, integer)
|
---|
36 | RETURNS integer AS
|
---|
37 | $BODY$select 0::int4$BODY$
|
---|
38 | LANGUAGE 'sql' STABLE;
|
---|
39 |
|
---|
40 | CREATE OR REPLACE FUNCTION cant_ocd_rd_por_rd(integer, integer)
|
---|
41 | RETURNS double precision AS
|
---|
42 | 'SELECT 0::float8'
|
---|
43 | LANGUAGE 'sql' STABLE;
|
---|
44 |
|
---|
45 | CREATE OR REPLACE FUNCTION cant_rd(integer, integer)
|
---|
46 | RETURNS double precision AS
|
---|
47 | 'SELECT 0::float8'
|
---|
48 | LANGUAGE 'sql' STABLE;
|
---|
49 |
|
---|
50 | CREATE OR REPLACE FUNCTION cant_cd_rd_por_rd(integer, integer)
|
---|
51 | RETURNS double precision AS
|
---|
52 | 'SELECT 0::float8'
|
---|
53 | LANGUAGE 'sql' STABLE;
|
---|
54 |
|
---|
55 | /* Si el restore tiene nueve errores, está ok:WARNING: errors ignored on restore: 9
|
---|
56 | Son las 9 funcs. que ya existen.
|
---|
57 |
|
---|
58 | ASÍ EL RESTORE DEMORA 6 MINUTOS*/
|
---|
59 |
|
---|
60 | Correr esto para agregar unos campos
|
---|
61 |
|
---|
62 | ALTER TABLE sujetos ADD COLUMN memo character varying(4096);
|
---|
63 | DROP VIEW comp_v;
|
---|
64 | CREATE OR REPLACE VIEW comp_v AS
|
---|
65 | SELECT c.id_comp, c.letra, c.id_puesto, c.nro_comp, c.fecha, c.id_area,
|
---|
66 | c.id_sujeto, c.obse, c.id_usuario, c.imp_total,
|
---|
67 | c.obse_impre, c.id_moneda, c.estado_imp, c.fec_auto, c.obse_auto,
|
---|
68 | c.id_usuario_auto, c.logcausas_auto, c.id_tipo_comp,
|
---|
69 | c.pago, c.total_fiscal, c.id_vendedor, c.rem_fiscal, c.ult_modif,
|
---|
70 | c.cotiz_base_comp, c.id_rubro_comp, c.nroz,
|
---|
71 | c.id_comp_terceros, c.id_cp, ((COALESCE(ct.tc, ''::character
|
---|
72 | varying)::text || COALESCE(c.letra, ''::character varying)::text) ||
|
---|
73 | '-'::text)
|
---|
74 | || compdes(c.id_puesto, c.nro_comp)::text AS nro, m.moneda, m.signo,
|
---|
75 | s.sujeto, a.area, s.doc AS cuit, s.direccion, s.memo, ci.condicion,
|
---|
76 | ci.cod_cf AS id_cond_cf, ci.completra, ci.ci, l.localidad,
|
---|
77 | sdt.tipo_doc_cf, ct.signo_comp, ct.tipo_comp, ct.tc, ct.esventa,
|
---|
78 | cr.rubro_comp,
|
---|
79 | s.cod_postal, p.provincia, a.id_tipo_comp_simple, cts.tipo_comp AS
|
---|
80 | tipo_comp_simple, cp.descripcion AS descr_cp
|
---|
81 | FROM comp c
|
---|
82 | LEFT JOIN sujetos s ON c.id_sujeto = s.id_sujeto
|
---|
83 | LEFT JOIN condiciones_iva ci ON s.id_condicion = ci.id_condicion
|
---|
84 | LEFT JOIN localidades l ON s.cod_postal::text = l.id_localidad::text
|
---|
85 | LEFT JOIN provincias p ON l.id_provincia = p.id_provincia
|
---|
86 | LEFT JOIN areas a ON c.id_area = a.id_area
|
---|
87 | LEFT JOIN monedas m ON c.id_moneda = m.id_moneda
|
---|
88 | LEFT JOIN sujetos_doc_tipos sdt ON s.id_tipo_doc = sdt.id_tipo_doc
|
---|
89 | LEFT JOIN comp_tipos ct ON c.id_tipo_comp = ct.id_tipo_comp
|
---|
90 | LEFT JOIN comp_rubros cr ON c.id_rubro_comp = cr.id_rubro_comp
|
---|
91 | LEFT JOIN comp_tipos cts ON a.id_tipo_comp_simple = cts.id_tipo_comp
|
---|
92 | LEFT JOIN centros_productivos cp ON c.id_cp = cp.id_cp;
|
---|
93 |
|
---|
94 |
|
---|
95 |
|
---|
96 |
|
---|
97 |
|
---|
98 | /*Ejecutar después de restaurar*/
|
---|
99 | CREATE OR REPLACE FUNCTION comp_mon_cotiz_base(integer)
|
---|
100 | RETURNS double precision AS
|
---|
101 | $BODY$--recibe id de comp de cliente y devuelve cotiz. de la moneda del comprobante
|
---|
102 | select cotiz_base_comp from comp where id_comp = $1$BODY$
|
---|
103 | LANGUAGE 'sql' STABLE;
|
---|
104 |
|
---|
105 |
|
---|
106 | CREATE OR REPLACE FUNCTION art_comp_en_rd(integer, integer)
|
---|
107 | RETURNS integer AS
|
---|
108 | $BODY$select distinct rd.id_articulo from cd_rd
|
---|
109 | left join remitos_detalles rd on cd_rd.id_rem = rd.id_remito and cd_rd.id_rem_det = rd.id_det
|
---|
110 | where cd_rd.id_comp = $1 and cd_rd.id_comp_det = $2$BODY$
|
---|
111 | LANGUAGE 'sql' STABLE;
|
---|
112 | COMMENT ON FUNCTION art_comp_en_rd(integer, integer) IS 'Dada una fila de comprobante devuelve los arts de los remitos det. asociados. Deberia ser un solo art. y el mismo que el del detalle del comp';
|
---|
113 |
|
---|
114 |
|
---|
115 | CREATE OR REPLACE FUNCTION cant_cd_rd_por_comp(integer, integer)
|
---|
116 | RETURNS double precision AS
|
---|
117 | 'SELECT coalesce(sum(coalesce(cant,0)),0) FROM cd_rd WHERE id_comp = $1 AND id_comp_det = $2'
|
---|
118 | LANGUAGE 'sql' STABLE;
|
---|
119 |
|
---|
120 | CREATE OR REPLACE FUNCTION cant_cd(integer, integer)
|
---|
121 | RETURNS double precision AS
|
---|
122 | 'SELECT coalesce(cant,0) FROM comp_det WHERE id_comp = $1 AND id_det = $2'
|
---|
123 | LANGUAGE 'sql' STABLE;
|
---|
124 |
|
---|
125 | CREATE OR REPLACE FUNCTION art_rd_en_comp(integer, integer)
|
---|
126 | RETURNS integer AS
|
---|
127 | $BODY$select distinct cd.id_articulo from cd_rd
|
---|
128 | left join comp_det cd on cd_rd.id_comp = cd.id_comp and cd_rd.id_comp_det = cd.id_det
|
---|
129 | where cd_rd.id_rem = $1 and cd_rd.id_rem_det = $2$BODY$
|
---|
130 | LANGUAGE 'sql' STABLE;
|
---|
131 | COMMENT ON FUNCTION art_rd_en_comp(integer, integer) IS 'Dada una fila de remito devuelve los arts de comp_det asociados. Deberia ser un solo art. y el mismo que el del detalle del remito';
|
---|
132 |
|
---|
133 | CREATE OR REPLACE FUNCTION art_rd_en_oc(integer, integer)
|
---|
134 | RETURNS integer AS
|
---|
135 | $BODY$select distinct ocd.id_articulo from ocd_rd
|
---|
136 | left join ordenes_compra_det ocd on ocd_rd.id_oc = ocd.id_oc and ocd_rd.id_oc_det = ocd.id_det
|
---|
137 | where ocd_rd.id_rem = $1 and ocd_rd.id_rem_det = $2$BODY$
|
---|
138 | LANGUAGE 'sql' STABLE;
|
---|
139 | COMMENT ON FUNCTION art_rd_en_oc(integer, integer) IS 'Dada una fila de remito devuelve los arts de las ordenes_compra_det asociados. Deberia ser un solo art. y el mismo que el del detalle del remito';
|
---|
140 |
|
---|
141 | CREATE OR REPLACE FUNCTION cant_ocd_rd_por_rd(integer, integer)
|
---|
142 | RETURNS double precision AS
|
---|
143 | 'SELECT coalesce(sum(coalesce(cant,0)),0) FROM ocd_rd WHERE id_rem = $1 AND id_rem_det = $2'
|
---|
144 | LANGUAGE 'sql' STABLE;
|
---|
145 |
|
---|
146 | CREATE OR REPLACE FUNCTION cant_rd(integer, integer)
|
---|
147 | RETURNS double precision AS
|
---|
148 | 'SELECT coalesce(cant,0) FROM remitos_detalles WHERE id_remito = $1 and id_det = $2'
|
---|
149 | LANGUAGE 'sql' STABLE;
|
---|
150 |
|
---|
151 | CREATE OR REPLACE FUNCTION cant_cd_rd_por_rd(integer, integer)
|
---|
152 | RETURNS double precision AS
|
---|
153 | 'SELECT coalesce(sum(coalesce(cant,0)),0) FROM cd_rd WHERE id_rem = $1 AND id_rem_det = $2'
|
---|
154 | LANGUAGE 'sql' STABLE;
|
---|
155 |
|
---|
156 |
|
---|
157 |
|
---|
158 |
|
---|
159 | DROP VIEW sujetos_v;
|
---|
160 | CREATE OR REPLACE VIEW sujetos_v AS
|
---|
161 | SELECT s.*, s.doc AS cuit, ci.condicion, ci.completra,
|
---|
162 | ci.id_tipo_doc_pred, t.transporte, td.tipo_doc, l.localidad,
|
---|
163 | le.localidad AS localidad_efec, rgt.afip_norm, l.id_provincia,
|
---|
164 | p.provincia, ci.id_condicion_tercero, td.id_tipo_doc_tercero,
|
---|
165 | lt.sujeto AS lug_tra, pl.listap AS listap_cli, ec.estado_civil,
|
---|
166 | pr.profesion, c.calle AS dir_calle, pa.pais AS nacionalidad, ci.ci,
|
---|
167 | lpad(s.id_sujeto::text, 13, 0::text) AS cod_barras
|
---|
168 | FROM sujetos s
|
---|
169 | LEFT JOIN localidades l ON s.cod_postal::text = l.id_localidad::text
|
---|
170 | LEFT JOIN localidades le ON s.cod_postal_efec::text = le.id_localidad::text
|
---|
171 | LEFT JOIN provincias p ON l.id_provincia = p.id_provincia
|
---|
172 | LEFT JOIN transporte t ON s.id_transporte = t.id_transporte
|
---|
173 | LEFT JOIN condiciones_iva ci ON s.id_condicion = ci.id_condicion
|
---|
174 | LEFT JOIN ret_gan_tipos rgt ON rgt.id_rg = s.id_rg
|
---|
175 | LEFT JOIN sujetos_doc_tipos td ON s.id_tipo_doc = td.id_tipo_doc
|
---|
176 | LEFT JOIN sujetos lt ON s.id_lug_tra = lt.id_sujeto
|
---|
177 | LEFT JOIN precios_listas pl ON s.id_listap_cli = pl.id_listap
|
---|
178 | LEFT JOIN estados_civiles ec ON s.id_estado_civil = ec.id_estado_civil
|
---|
179 | LEFT JOIN profesiones pr ON s.id_profesion = pr.id_profesion
|
---|
180 | LEFT JOIN calles c ON s.id_calle = c.id_calle
|
---|
181 | LEFT JOIN paises pa ON s.id_nacionalidad = pa.id_pais;
|
---|
182 |
|
---|
183 |
|
---|
184 |
|
---|
185 |
|
---|
186 | CREATE OR REPLACE VIEW comp_combo_v AS
|
---|
187 | SELECT NULL::integer AS id_comp, NULL::integer AS id_det, NULL::integer AS
|
---|
188 | id_listap, NULL::integer AS id_articulo, NULL::character varying AS
|
---|
189 | descripcion, NULL::character varying AS descrart, NULL::numeric AS precio,
|
---|
190 | NULL::numeric AS cant, NULL::numeric AS p_unitario, NULL::numeric AS costo,
|
---|
191 | NULL::numeric AS iva, NULL::numeric AS impint, NULL::double precision AS
|
---|
192 | cant_en_combo, NULL::double precision AS subtotal;
|
---|
193 | COMMENT ON VIEW comp_combo_v IS 'Tabla que se usa para enganiar al motor de
|
---|
194 | regente para que nos cree el atributo info en el listado de componentes, es
|
---|
195 | necesario para cuando se usan los combos y la habilidad para detallar las
|
---|
196 | partes de los mismos';
|
---|
197 | CREATE OR REPLACE FUNCTION art_cant_en_combo(integer, integer) RETURNS
|
---|
198 | double precision AS
|
---|
199 | $BODY$--$1 idarticulo, $2 idcombo Devuelve la cantidad del art id_articulo($1) que participa en el combo $2
|
---|
200 | select cant from articulos_combos where id_articulo = $1 and
|
---|
201 | id_art_combo =$2;$BODY$
|
---|
202 | LANGUAGE 'sql' STABLE;
|
---|
203 |
|
---|
204 |
|
---|
205 |
|
---|
206 | ALTER TABLE sujetos ADD COLUMN memo character varying(4096);
|
---|
207 | DROP VIEW comp_v;
|
---|
208 | CREATE OR REPLACE VIEW comp_v AS
|
---|
209 | SELECT c.id_comp, c.letra, c.id_puesto, c.nro_comp, c.fecha, c.id_area,
|
---|
210 | c.id_sujeto, c.obse, c.id_usuario, c.imp_total,
|
---|
211 | c.obse_impre, c.id_moneda, c.estado_imp, c.fec_auto, c.obse_auto,
|
---|
212 | c.id_usuario_auto, c.logcausas_auto, c.id_tipo_comp,
|
---|
213 | c.pago, c.total_fiscal, c.id_vendedor, c.rem_fiscal, c.ult_modif,
|
---|
214 | c.cotiz_base_comp, c.id_rubro_comp, c.nroz,
|
---|
215 | c.id_comp_terceros, c.id_cp, ((COALESCE(ct.tc, ''::character
|
---|
216 | varying)::text || COALESCE(c.letra, ''::character varying)::text) ||
|
---|
217 | '-'::text)
|
---|
218 | || compdes(c.id_puesto, c.nro_comp)::text AS nro, m.moneda, m.signo,
|
---|
219 | s.sujeto, a.area, s.doc AS cuit, s.direccion, s.memo, ci.condicion,
|
---|
220 | ci.cod_cf AS id_cond_cf, ci.completra, ci.ci, l.localidad,
|
---|
221 | sdt.tipo_doc_cf, ct.signo_comp, ct.tipo_comp, ct.tc, ct.esventa,
|
---|
222 | cr.rubro_comp,
|
---|
223 | s.cod_postal, p.provincia, a.id_tipo_comp_simple, cts.tipo_comp AS
|
---|
224 | tipo_comp_simple, cp.descripcion AS descr_cp
|
---|
225 | FROM comp c
|
---|
226 | LEFT JOIN sujetos s ON c.id_sujeto = s.id_sujeto
|
---|
227 | LEFT JOIN condiciones_iva ci ON s.id_condicion = ci.id_condicion
|
---|
228 | LEFT JOIN localidades l ON s.cod_postal::text = l.id_localidad::text
|
---|
229 | LEFT JOIN provincias p ON l.id_provincia = p.id_provincia
|
---|
230 | LEFT JOIN areas a ON c.id_area = a.id_area
|
---|
231 | LEFT JOIN monedas m ON c.id_moneda = m.id_moneda
|
---|
232 | LEFT JOIN sujetos_doc_tipos sdt ON s.id_tipo_doc = sdt.id_tipo_doc
|
---|
233 | LEFT JOIN comp_tipos ct ON c.id_tipo_comp = ct.id_tipo_comp
|
---|
234 | LEFT JOIN comp_rubros cr ON c.id_rubro_comp = cr.id_rubro_comp
|
---|
235 | LEFT JOIN comp_tipos cts ON a.id_tipo_comp_simple = cts.id_tipo_comp
|
---|
236 | LEFT JOIN centros_productivos cp ON c.id_cp = cp.id_cp; |
---|