CREATE EXTENSION IF NOT EXISTS postgres_fdw;
DROP SCHEMA IF EXISTS fdw_1109 CASCADE;
DROP SERVER IF EXISTS srv_1109 CASCADE;
CREATE SCHEMA fdw_1109;
CREATE SERVER srv_1109 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.0.17.3', port '5414', dbname 'sigem1109');
CREATE USER MAPPING FOR current_user SERVER srv_1109 OPTIONS (user 'postgres', password 'x25yvaga2017');
IMPORT FOREIGN SCHEMA public LIMIT TO (v_liq_entidad_totalxobjeto) FROM SERVER srv_1109 INTO fdw_1109;

CREATE OR REPLACE VIEW public.vw_lotes_morosidad_1109 AS 
SELECT 
    lot.*, 
    liq.inm_ficha, 
    liq.inm_ctacatastral, 
    liq.trb_total_deuda, 
    liq.trb_total_pago, 
    liq.ultimo_pago 
FROM public.e1109_lotes_activos lot
LEFT JOIN fdw_1109.v_liq_entidad_totalxobjeto liq ON lot.snc_cuenta = REPLACE(liq.inm_ctacatastral, '-', '');
