-- View: he_v_plantillac163

-- DROP VIEW he_v_plantillac163;

CREATE OR REPLACE VIEW he_v_plantillac163 AS
 SELECT ROW_NUMBER() OVER (ORDER BY he_fitxa.he_fitxa_id) AS he_v_plantillac163_id, to_char('Y'::text) AS btn, he_fitxa.ad_client_id, he_fitxa.ad_org_id, he_fitxa.isactive, he_fitxa.created, he_fitxa.createdby, he_fitxa.updated, he_fitxa.updatedby, 'AUTORITZACIÓ PAGAMENTS TERRASSES I JARDINS'::CHARACTER VARYING AS fx_titol, he_get_codi_document('pltc163'::CHARACTER VARYING, he_fitxa.he_fitxa_id, he_fitxa.promo) AS fx_codi_document, he_get_barcode39_document('pltc163'::CHARACTER VARYING,
he_fitxa.he_fitxa_id, he_fitxa.promo) AS fx_barcode39_document, he_get_data_alfanumerica('CAT'::CHARACTER VARYING) AS fx_data_cat, he_get_data_alfanumerica('CAS'::CHARACTER VARYING) AS fx_data_cas,
he_fitxa.he_fitxa_id AS soci_ident, he_get_tract_soci(he_fitxa.he_ad_sexe_ls) AS soci_tractament, COALESCE(he_fitxa.nom, '** no info **'::CHARACTER VARYING) AS soci_nom, COALESCE(he_fitxa.cognoms, '** no info **'::CHARACTER VARYING) AS soci_cognoms, COALESCE(he_fitxa.dni, '** no info **'::CHARACTER VARYING) AS soci_dni, COALESCE(he_fitxa.poblacio, '** no info **'::CHARACTER VARYING) AS soci_poblacio, COALESCE(he_fitxa.adreca, '** no info **'::CHARACTER VARYING) AS soci_adreca, COALESCE(he_fitxa.codipostal, '** no info **'::CHARACTER VARYING) AS soci_cpostal, COALESCE(he_fitxa.soci_promo, 0::NUMERIC) AS soci_num_dintre_promo, COALESCE(he_fitxa.soci_gener, 0::NUMERIC) AS soci_num_general, COALESCE(he_fitxa.num_preins, 0::NUMERIC) AS soci_num_preinscrit, COALESCE(he_promocio.name, '** no info **'::CHARACTER VARYING) AS promo_name, COALESCE(he_promocio.poblacio, '** no info **'::CHARACTER VARYING) AS promo_poblacio, COALESCE(he_promocio.escriptura_pub, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS promo_data_def_escriptura_pub, he_get_data_alfanumerica_excel(COALESCE(he_promocio.prev_escriptura_pub, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone), 'CAT'::CHARACTER(1)) AS promo_data_prev_escriptura_pub, COALESCE(he_promocio.avant_prj, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS promo_data_def_avant_prj, he_get_data_alfanumerica_excel(COALESCE(he_promocio.prev_avant_prj, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone), 'CAT'::CHARACTER(1)) AS promo_data_prev_avant_prj, COALESCE(he_promocio.llic_obra, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS promo_data_def_llic_obra, he_get_data_alfanumerica_excel(COALESCE(he_promocio.prev_llic_obra, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone), 'CAT'::CHARACTER(1)) AS promo_data_prev_llic_obra, COALESCE(he_promocio.adjud_hab_def, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS promo_data_def_adjud_hab, he_get_data_alfanumerica_excel(COALESCE(he_promocio.prev_adjud_hab_socis, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone), 'CAT'::CHARACTER(1)) AS promo_data_prev_adjud_hab, COALESCE(he_promocio.act_repl, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS promo_data_def_acta_replanteig, he_get_data_alfanumerica_excel(COALESCE(he_promocio.prev_act_repl, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone), 'CAT'::CHARACTER(1)) AS promo_data_prev_acta_replanteig, COALESCE(he_promocio.claus_def, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS promo_data_def_claus, he_get_data_alfanumerica_excel(COALESCE(he_promocio.claus_socis, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone), 'CAT'::CHARACTER(1)) AS promo_data_prev_claus_socis,
 he_cont_soc.aparking AS contr_id_parking, COALESCE(he_cont_soc.preu_parking, 0::NUMERIC) AS contr_preu_parking, COALESCE(he_cont_soc.iva_imp_total, 0::NUMERIC) AS contr_iva_imp_parking, COALESCE(he_cont_soc.fons_reserva_perc, 0::NUMERIC) AS contr_fons_reserva_perc, COALESCE(he_cont_soc.fons_reserva_imp, 0::NUMERIC) AS contr_fons_reserva_imp, COALESCE(he_cont_soc.preu_total, 0::NUMERIC) AS contr_preu_total, COALESCE(he_cont_soc.name, '** no info **'::CHARACTER VARYING) AS contr_tipologia_contracte, COALESCE(he_pla_pagaments.iva_perc_preu_final_total, 0::NUMERIC) AS pp_iva_perc_preu_final_total, COALESCE(he_pla_pagaments.preu_final, 0::NUMERIC) AS pp_preu_final_sense_iva, COALESCE(he_pla_pagaments.finsaclaus_estudiprevi, 0::NUMERIC) AS pp_fins_claus_sortida, COALESCE(he_pla_pagaments.hipoteca, 0::NUMERIC) AS pp_hipoteca, COALESCE(he_pla_pagaments.data_entrada, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS pp_data_entrada, COALESCE(he_pla_pagaments.entrada, 0::NUMERIC) AS pp_imp_entrada, COALESCE(he_pla_pagaments.iva_perc_entrada, 0::NUMERIC) AS pp_iva_perc_entrada, COALESCE(he_pla_pagaments.iva_imp_entrada, 0::NUMERIC) AS pp_iva_imp_entrada, COALESCE(he_pla_pagaments.entrada_total, 0::NUMERIC) AS pp_imp_entrada_total, COALESCE(he_pla_pagaments.npm, 0::NUMERIC) AS pp_num_pagaments_mensuals, COALESCE(he_pla_pagaments.data_inici_pm, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS pp_data_inici_pag_mensuals, COALESCE(he_pla_pagaments.data_fi_pm, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS pp_data_fi_pag_mensuals, COALESCE(he_pla_pagaments.quota_m, 0::NUMERIC) AS pp_imp_mensual, COALESCE(he_pla_pagaments.iva_perc_quota_m, 0::NUMERIC) AS pp_iva_perc_mensual, COALESCE(he_pla_pagaments.iva_imp_mensual, 0::NUMERIC) AS pp_iva_imp_mensual, COALESCE(he_pla_pagaments.imp_total_mensual, 0::NUMERIC) AS pp_imp_mensual_total, COALESCE(he_pla_pagaments.n_pg_seme_01, 0::NUMERIC) AS pp_num_pag_sem01, COALESCE(he_pla_pagaments.n_pg_seme_02, 0::NUMERIC) AS pp_num_pag_sem02, COALESCE(he_pla_pagaments.n_pg_seme_03, 0::NUMERIC) AS pp_num_pag_sem03, COALESCE(he_pla_pagaments.n_pg_seme_04, 0::NUMERIC) AS pp_num_pag_sem04, COALESCE(he_pla_pagaments.n_pg_seme_05, 0::NUMERIC) AS pp_num_pag_sem05, COALESCE(he_pla_pagaments.pg_seme_01, 0::NUMERIC) AS pp_base_imp_sem01, COALESCE(he_pla_pagaments.pg_seme_02, 0::NUMERIC) AS pp_base_imp_sem02, COALESCE(he_pla_pagaments.pg_seme_03, 0::NUMERIC) AS pp_base_imp_sem03, COALESCE(he_pla_pagaments.pg_seme_04, 0::NUMERIC) AS pp_base_imp_sem04, COALESCE(he_pla_pagaments.pg_seme_05, 0::NUMERIC) AS pp_base_imp_sem05, COALESCE(he_pla_pagaments.alf_pg_seme_01, ''::CHARACTER VARYING) AS pp_data_paga_sem01, COALESCE(he_pla_pagaments.alf_pg_seme_02, ''::CHARACTER VARYING) AS pp_data_paga_sem02, COALESCE(he_pla_pagaments.alf_pg_seme_03, ''::CHARACTER VARYING) AS pp_data_paga_sem03, COALESCE(he_pla_pagaments.alf_pg_seme_04, ''::CHARACTER VARYING) AS pp_data_paga_sem04, COALESCE(he_pla_pagaments.alf_pg_seme_05, ''::CHARACTER VARYING) AS pp_data_paga_sem05, COALESCE(he_pla_pagaments.imp_compl_inici_obres, 0::NUMERIC) AS pp_imp_compl_inici_obres, COALESCE(to_char(he_pla_pagaments.data_pag_compl_inici_obres, 'mm-yyyy'::text), ''::text) AS pp_data_pag_compl_inici_obres, COALESCE(he_pla_pagaments.pcc, 0::NUMERIC) AS pp_imp_compl_claus, COALESCE(to_char(he_pla_pagaments.data_pag_compl_claus, 'mm-yyyy'::text), ''::text) AS pp_data_pag_compl_claus, COALESCE(he_pla_pagaments.n_pg_comp_01, 0::NUMERIC) AS pp_num_pag_comp01, COALESCE(he_pla_pagaments.pg_comp_01, 0::NUMERIC) AS pp_base_imp_comp01, COALESCE(he_pla_pagaments.alf_pg_comp_01, ''::CHARACTER VARYING) AS pp_data_paga_comp01, COALESCE(he_pla_pagaments.n_pg_comp_02, 0::NUMERIC) AS pp_num_pag_comp02, COALESCE(he_pla_pagaments.pg_comp_02, 0::NUMERIC) AS pp_base_imp_comp02, COALESCE(he_pla_pagaments.alf_pg_comp_02, ''::CHARACTER VARYING) AS pp_data_paga_comp02, COALESCE(he_pla_pagaments.n_pg_comp_03, 0::NUMERIC) AS pp_num_pag_comp03, COALESCE(he_pla_pagaments.pg_comp_03, 0::NUMERIC) AS pp_base_imp_comp03, COALESCE(he_pla_pagaments.alf_pg_comp_03, ''::CHARACTER VARYING) AS pp_data_paga_comp03, COALESCE(he_pla_pagaments.n_pg_comp_04, 0::NUMERIC) AS pp_num_pag_comp04, COALESCE(he_pla_pagaments.pg_comp_04, 0::NUMERIC) AS pp_base_imp_comp04, COALESCE(he_pla_pagaments.alf_pg_comp_04, ''::CHARACTER VARYING) AS pp_data_paga_comp04, COALESCE(he_pla_pagaments.n_pg_comp_05, 0::NUMERIC) AS pp_num_pag_comp05, COALESCE(he_pla_pagaments.pg_comp_05, 0::NUMERIC) AS pp_base_imp_comp05, COALESCE(he_pla_pagaments.alf_pg_comp_05, ''::CHARACTER VARYING) AS pp_data_paga_comp05, COALESCE(he_pla_pagaments.pcc, 0::NUMERIC) AS pp_imp_final_escrip, COALESCE(he_pla_pagaments.iva_perc_final_escrip, 0::NUMERIC) AS pp_iva_perc_final_escrip, COALESCE(he_pla_pagaments.iva_imp_final_escrip, 0::NUMERIC) AS pp_iva_imp_final_escrip, COALESCE(he_pla_pagaments.fons_reserva_perc, 0::NUMERIC) AS pp_fons_reserva_perc, COALESCE(he_pla_pagaments.fons_reserva_imp, 0::NUMERIC) AS pp_fons_reserva_imp, COALESCE(he_pla_pagaments.pcc_total, 0::NUMERIC) AS pp_imp_final_escripturacio_total, COALESCE(( SELECT desv.m2t_sobreterres
           FROM he_desvinculat desv
          WHERE desv.he_desvinculat_id = he_cont_soc.aterrassa), 0::NUMERIC) AS element_terrassa_m2_sobreterres, COALESCE(( SELECT desv.m2t_sobrecoberta
           FROM he_desvinculat desv
          WHERE desv.he_desvinculat_id = he_cont_soc.aterrassa), 0::NUMERIC) AS element_terrassa_m2_sobreforjat, COALESCE(he_promocio.claus_def, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS promo_data_def_lliurament, COALESCE(he_promocio.cert_fiobra, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS promo_data_prev_fi_obra, COALESCE(he_promocio.claus, '1900-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AS promo_data_prev_lliurament, COALESCE(he_cont_soc.iva_perc_total, 0::NUMERIC) AS contr_iva_perc_park, he_cont_soc.he_ad_econtracte_ls, he_pla_pagaments.he_pla_pagaments_id, he_fitxa.he_ad_control_ls AS soci_control, COALESCE(( SELECT desv.bloc
           FROM he_desvinculat desv
          WHERE desv.he_desvinculat_id = he_cont_soc.avivenda), ''::CHARACTER VARYING) AS element_vivenda_bloc, COALESCE(( SELECT desv.tipologia
           FROM he_desvinculat desv
          WHERE desv.he_desvinculat_id = he_cont_soc.avivenda), ''::CHARACTER VARYING) AS element_vivenda_tipologia, COALESCE(( SELECT desv.escala
           FROM he_desvinculat desv
          WHERE desv.he_desvinculat_id = he_cont_soc.avivenda), ''::CHARACTER VARYING) AS element_vivenda_escala, COALESCE(( SELECT desv.planta
           FROM he_desvinculat desv
          WHERE desv.he_desvinculat_id = he_cont_soc.avivenda), ''::CHARACTER VARYING) AS element_vivenda_planta, COALESCE(( SELECT desv.porta
           FROM he_desvinculat desv
          WHERE desv.he_desvinculat_id = he_cont_soc.avivenda), ''::CHARACTER VARYING) AS element_vivenda_porta, COALESCE(( SELECT desv.numero
           FROM he_desvinculat desv
          WHERE desv.he_desvinculat_id = he_cont_soc.aparking), ''::CHARACTER VARYING) AS element_garatge_num, COALESCE(( SELECT desv.numero
           FROM he_desvinculat desv
          WHERE desv.he_desvinculat_id = he_cont_soc.atraster), ''::CHARACTER VARYING) AS element_traster_num, COALESCE(he_promocio.num_hab, 0::NUMERIC) AS promo_num_hab, COALESCE(he_promocio.sector, '** no info **'::CHARACTER VARYING) AS promo_sector
   FROM he_fitxa, he_cont_soc, he_pla_pagaments, he_promocio
  WHERE he_fitxa.he_promocio_id IS NOT NULL AND he_cont_soc.avivenda IS NOT NULL AND he_fitxa.he_promocio_id = he_promocio.he_promocio_id AND he_fitxa.he_fitxa_id = he_cont_soc.he_fitxa_id AND he_pla_pagaments.he_cont_soc_id = he_cont_soc.he_cont_soc_id AND he_pla_pagaments.tipus_pr::text = 'terrassa'::text AND he_pla_pagaments.he_pla_pagaments_id = (( SELECT MAX(pp2.he_pla_pagaments_id) AS MAX
           FROM he_pla_pagaments pp2
          WHERE pp2.he_cont_soc_id = he_cont_soc.he_cont_soc_id))
  ORDER BY he_pla_pagaments.he_pla_pagaments_id, he_promocio.name, he_fitxa.cognoms;

ALTER TABLE he_v_plantillac163 OWNER TO postgres;