1 | <?xml version="1.0" encoding="UTF-8"?> |
---|
2 | <CDADescriptor> |
---|
3 | <DataSources> |
---|
4 | <Connection id="CodArancelario" type="sql.jndi"> |
---|
5 | <Jndi>smid</Jndi> |
---|
6 | </Connection> |
---|
7 | <Connection id="CodArancelario2" type="sql.jndi"> |
---|
8 | <Jndi>smid</Jndi> |
---|
9 | </Connection> |
---|
10 | <Connection id="PlantasxCodA" type="sql.jndi"> |
---|
11 | <Jndi>smid</Jndi> |
---|
12 | </Connection> |
---|
13 | <Connection id="Ventas" type="sql.jndi"> |
---|
14 | <Jndi>smid</Jndi> |
---|
15 | </Connection> |
---|
16 | <Connection id="sqlAnyo" type="sql.jndi"> |
---|
17 | <Jndi>smid</Jndi> |
---|
18 | </Connection> |
---|
19 | </DataSources> |
---|
20 | <DataAccess access="public" cache="true" cacheDuration="3600" connection="CodArancelario" |
---|
21 | id="CodArancelario" |
---|
22 | type="sql"> |
---|
23 | <Columns/> |
---|
24 | <Parameters/> |
---|
25 | <Query>SELECT DISTINCT |
---|
26 | "dimension_codigoarancelario"."codigo" as Cod, ("dimension_codigoarancelario"."codigo" || ' - ' || "dimension_codigoarancelario"."descripcion_ref") as descr |
---|
27 | FROM |
---|
28 | "dimension_codigoarancelario" |
---|
29 | WHERE |
---|
30 | character_length("dimension_codigoarancelario"."codigo") = 2 |
---|
31 | ORDER BY |
---|
32 | Cod asc</Query> |
---|
33 | </DataAccess> |
---|
34 | <DataAccess access="public" cache="true" cacheDuration="3600" connection="CodArancelario2" |
---|
35 | id="CodArancelario2" |
---|
36 | type="sql"> |
---|
37 | <Columns/> |
---|
38 | <Parameters> |
---|
39 | <Parameter default="Codigo" name="Codigo" type="String"/> |
---|
40 | </Parameters> |
---|
41 | <Query>SELECT |
---|
42 | "dimension_codigoarancelario"."codigo" || ' - ' || "dimension_codigoarancelario"."descripcion_ref" |
---|
43 | FROM |
---|
44 | "dimension_codigoarancelario" INNER JOIN "hechos_bienesproducidos_empresa" ON "dimension_codigoarancelario"."id" = "hechos_bienesproducidos_empresa"."id_codigo_arancelario" |
---|
45 | WHERE |
---|
46 | dimension_codigoarancelario.codigo LIKE ${Codigo} || '%' |
---|
47 | GROUP BY |
---|
48 | "dimension_codigoarancelario"."codigo","dimension_codigoarancelario"."descripcion_ref"</Query> |
---|
49 | </DataAccess> |
---|
50 | <DataAccess access="public" cache="true" cacheDuration="3600" connection="PlantasxCodA" |
---|
51 | id="PlantasxCodA" |
---|
52 | type="sql"> |
---|
53 | <Columns/> |
---|
54 | <Parameters> |
---|
55 | <Parameter default="Codigo" name="Codigo" type="String"/> |
---|
56 | </Parameters> |
---|
57 | <Query>SELECT |
---|
58 | "dimension_codigoarancelario"."codigo" AS codigo, |
---|
59 | "dimension_codigoarancelario"."descripcion_ref" AS Descrip, |
---|
60 | count(distinct("hechos_bienesproducidos_empresa"."id_empresa")) AS total |
---|
61 | FROM |
---|
62 | "hechos_bienesproducidos_empresa" INNER JOIN "dimension_codigoarancelario" ON "hechos_bienesproducidos_empresa"."id_codigo_arancelario" = "dimension_codigoarancelario"."id" |
---|
63 | WHERE |
---|
64 | dimension_codigoarancelario.codigo LIKE ${Codigo}||'%' |
---|
65 | GROUP BY |
---|
66 | codigo, |
---|
67 | Descrip |
---|
68 | ORDER BY |
---|
69 | 1 ASC</Query> |
---|
70 | </DataAccess> |
---|
71 | <DataAccess access="public" cache="true" cacheDuration="3600" connection="Ventas" |
---|
72 | id="Ventas" |
---|
73 | type="sql"> |
---|
74 | <Columns/> |
---|
75 | <Parameters> |
---|
76 | <Parameter default="cod" name="cod" type="String"/> |
---|
77 | <Parameter default="anyo" name="anyo" type="String"/> |
---|
78 | </Parameters> |
---|
79 | <Query>(SELECT |
---|
80 | "dimension_empresa"."razon_social" AS Empresa, |
---|
81 | sum("hechos_bienesproducidos_empresa"."medida_monto_venta") AS Monto |
---|
82 | FROM |
---|
83 | "dimension_codigoarancelario" INNER JOIN "hechos_bienesproducidos_empresa" ON "dimension_codigoarancelario"."id" = "hechos_bienesproducidos_empresa"."id_codigo_arancelario" |
---|
84 | INNER JOIN "dimension_empresa" ON "hechos_bienesproducidos_empresa"."id_empresa" = "dimension_empresa"."id" |
---|
85 | INNER JOIN "dimension_tiempo" ON "hechos_bienesproducidos_empresa"."id_itempo" = "dimension_tiempo"."id" |
---|
86 | WHERE |
---|
87 | dimension_codigoarancelario.codigo LIKE ${cod} |
---|
88 | AND CAST(dimension_tiempo.anyo AS text) = ${anyo} |
---|
89 | GROUP BY |
---|
90 | Empresa |
---|
91 | ORDER BY Monto DESC |
---|
92 | LIMIT 10) |
---|
93 | UNION |
---|
94 | (SELECT |
---|
95 | 'Otras Empresas' as Empresa, |
---|
96 | sum("hechos_bienesproducidos_empresa"."medida_monto_venta") as Monto |
---|
97 | FROM |
---|
98 | "dimension_codigoarancelario" INNER JOIN "hechos_bienesproducidos_empresa" ON "dimension_codigoarancelario"."id" = "hechos_bienesproducidos_empresa"."id_codigo_arancelario" |
---|
99 | INNER JOIN "dimension_empresa" ON "hechos_bienesproducidos_empresa"."id_empresa" = "dimension_empresa"."id" |
---|
100 | INNER JOIN "dimension_tiempo" ON "hechos_bienesproducidos_empresa"."id_itempo" = "dimension_tiempo"."id" |
---|
101 | WHERE |
---|
102 | dimension_codigoarancelario.codigo LIKE ${cod} AND CAST(dimension_tiempo.anyo AS text) = ${anyo} AND dimension_empresa.id not in ( |
---|
103 | SELECT |
---|
104 | "hechos_bienesproducidos_empresa"."id_empresa" |
---|
105 | FROM |
---|
106 | "dimension_codigoarancelario" INNER JOIN "hechos_bienesproducidos_empresa" ON "dimension_codigoarancelario"."id" = "hechos_bienesproducidos_empresa"."id_codigo_arancelario" |
---|
107 | INNER JOIN "dimension_empresa" ON "hechos_bienesproducidos_empresa"."id_empresa" = "dimension_empresa"."id" |
---|
108 | INNER JOIN "dimension_tiempo" ON "hechos_bienesproducidos_empresa"."id_itempo" = "dimension_tiempo"."id" |
---|
109 | WHERE |
---|
110 | dimension_codigoarancelario.codigo LIKE ${cod} |
---|
111 | AND CAST(dimension_tiempo.anyo AS text) = ${anyo} |
---|
112 | GROUP BY 1 |
---|
113 | ) |
---|
114 | ORDER BY Monto DESC) |
---|
115 | ORDER BY 2 ASC</Query> |
---|
116 | </DataAccess> |
---|
117 | <DataAccess access="public" cache="true" cacheDuration="3600" connection="sqlAnyo" |
---|
118 | id="sqlAnyo" |
---|
119 | type="sql"> |
---|
120 | <Columns/> |
---|
121 | <Parameters> |
---|
122 | <Parameter default="cod" name="cod" type="String"/> |
---|
123 | </Parameters> |
---|
124 | <Query>(SELECT '--Seleccione--') |
---|
125 | UNION |
---|
126 | (SELECT |
---|
127 | CAST(dimension_tiempo.anyo AS text) as anyo |
---|
128 | FROM |
---|
129 | "dimension_codigoarancelario" INNER JOIN "hechos_bienesproducidos_empresa" ON "dimension_codigoarancelario"."id" = "hechos_bienesproducidos_empresa"."id_codigo_arancelario" |
---|
130 | INNER JOIN "dimension_tiempo" ON "hechos_bienesproducidos_empresa"."id_itempo" = "dimension_tiempo"."id" |
---|
131 | WHERE |
---|
132 | dimension_codigoarancelario.codigo LIKE ${cod} |
---|
133 | GROUP BY |
---|
134 | anyo) |
---|
135 | ORDER BY 1 desc</Query> |
---|
136 | </DataAccess> |
---|
137 | </CDADescriptor> |
---|