· 7 years ago · Feb 10, 2019, 07:52 AM
1
2drop function if exists mcom.fn_referentes_info(varchar, varchar, varchar, varchar, varchar, varchar);
3create function mcom.fn_referentes_info(
4 host varchar,
5 puerto varchar,
6 usuario varchar,
7 contrasenia varchar,
8 bd varchar,
9 codigoReferente varchar)
10 returns TABLE(
11 perfil_competencia text,
12 ambito_profesional text,
13 sector_productivo text,
14 ocupacion text,
15 tipo text)
16language plpgsql
17as $$
18BEGIN
19
20 RETURN QUERY select *
21 from dblink(
22
23DROP FUNCTION if exists mcom.fn_oferta_horario(integer [], integer, integer, integer, integer);
24CREATE OR REPLACE FUNCTION mcom.fn_oferta_horario(
25 idreferente integer [],
26 anio integer,
27 idcentro integer,
28 trimestre integer,
29 metodo integer)
30 RETURNS TABLE(id_referente bigint, nombre_referente text, idoferta bigint, fechainicio date, fechafin date,
31 grupo_oferta character varying, cupo_oferta_beca_nacional integer,
32 cupo_oferta_fondos_propios integer, iddepartamento smallint, departamento varchar(25))
33LANGUAGE 'plpgsql'
34AS $BODY$
35BEGIN
36 RETURN QUERY SELECT DISTINCT cl.id_estructura_formativa as id_referente,
37 UPPER(C.descripcion) AS nombre_referente,
38 o.id as id_oferta,
39 o.finicio as fechainincio,
40 o.ffin as fechafin,
41 o.grupo as grupo_oferta,
42 P.cupo AS cupo_beca_nacional,
43 P.cupofp AS cupo_fondos_propios,
44 td.id_departamento as iddepartamento,
45 td.nombre as departamento
46 FROM registro_cobranza.rg_oferta o
47 INNER JOIN sac.acuerdos_detalles acd ON (acd.ID = o.id_acuerdo_deta)
48 INNER JOIN sac.acuerdos ac ON (acd.acuerdoid = ac.acuerdoid)
49 INNER JOIN registro_cobranza.cu_curso_clasificacion cl ON (
50 acd.id_curso_clasificacion = cl.ID
51 )
52 INNER JOIN registro_cobranza.cu_estructura_formativa ef ON (
53 ef.ID = cl.id_estructura_formativa
54 )
55 INNER JOIN registro_cobranza.cu_cat_curso C ON cl.id_curso = C.ID
56 LEFT JOIN registro_cobranza.indicadores_vis
57 ON registro_cobranza.indicadores_vis.ID = o.id_indicador_detalle
58 INNER JOIN registro_cobranza.rg_programacion P ON P.ID = o.id_programacion
59 INNER JOIN registro_cobranza.cu_det_rama_familia drf ON C.id_det_rama_familia = drf.ID
60 INNER JOIN public.tb_departamento td on td.id_departamento = o.id_dpto
61 where ef.id in (SELECT * FROM unnest(ARRAY [$1]))
62 AND o.anio_ofertado = $2
63 AND C.id_tipo_evento = 4
64 AND o.id_centro = $3
65 AND o.activo = 1
66 and o.id not in (SELECT * FROM UNNEST(ARRAY(select id_oferta from mcom.tb_convocatoria_detalle)))
67 AND P.trimestre = $4
68 AND P.metodo_evaluacion = $5;
69end;
70$BODY$;