AGM (6) Gestion Tributaria (5) IBI Rústica

 

1. IBI rústica exenciones (RUSTICABONIFICACIONESEXENCIONES.xls)

--==========================================================
-- 1. RUSTICA BONIFICACIONES
--==========================================================
SELECT 
  IIBU.REF_CAT_1_IIBU || IIBU.REF_CAT_2_IIBU || IIBU.REF_CAT_NUMSEC_IIBU || IIBU.REF_CAT_DC_1_IIBU || IIBU.REF_CAT_DC_2_IIBU AS REF_CATASTRAL,
  IBCO.NIF_IBCO AS NIF,
  IBCO.NOMBRE_IBCO AS TX_NOMBRE_COMPLETO,
  --TO_CHAR(BEIB.ID_TBGI)||'.'||TO_CHAR(BEIB.CATEGORIA_BEIB)  AS COD_Z_BON_EXE,
  CASE 
  	WHEN BEIB.ID_TBGI=19 THEN TO_CHAR(BEIB.ID_TBGI)||'.'||TO_CHAR(BEIB.CATEGORIA_BEIB) 
  	ELSE TO_CHAR(BEIB.ID_TBGI)
  END AS COD_Z_BON_EXE, 	
  --TBGI.DESCR_TBGI || ' (' || TBGI.DESCR_NORMA_TBGI || ')' AS DESCRIPCION,
  TBGI.DESCR_TBGI ||
  CASE 
  	WHEN BEIB.ID_TBGI=19 THEN '-Cat.'||TO_CHAR(BEIB.CATEGORIA_BEIB) 
  	ELSE ''
  END || ' (' || TBGI.DESCR_NORMA_TBGI || ')' AS DESCRIPCION, 	
  CASE 
  	WHEN BEIB.ID_TBGI=19 AND BEIB.CATEGORIA_BEIB=1 THEN 25.0
  	WHEN BEIB.ID_TBGI=19 AND BEIB.CATEGORIA_BEIB=2 THEN 50.0
  	WHEN BEIB.ID_TBGI=33                           THEN 95.0  
  	ELSE                                               100.0
  END AS PORCENTAJE,
  BEIB.FECHA_DESDE_BEIB AS F_INI_BE,
  BEIB.FECHA_HASTA_BEIB AS F_FIN_BE
FROM OPS$GTTORA.IBCO_IBI_COTITULARES IBCO
JOIN OPS$GTTORA.IIBU_INFO_IBI_URBANA IIBU ON IIBU.ID_REGISTRO_IIBU = IBCO.ID_REGISTRO_IIBU AND IIBU.CLASE_BIEN_IIBU ='RU'
  AND NOT EXISTS (
    SELECT * FROM "OPS$GTTORA".IIBU_INFO_IBI_URBANA IIBU1 
    WHERE IIBU1.NUM_FIJO_IIBU=IIBU.NUM_FIJO_IIBU 
      AND IIBU1.CLASE_BIEN_IIBU='RU' 
      AND IIBU1.ID_SOPORTE_IIBU >IIBU.ID_SOPORTE_IIBU
  ) 
JOIN OPS$GTTORA.BEIB_BENEFICIOS_IBI BEIB ON BEIB.ID_EPER_OT=IBCO.ID_EPER_OT AND BEIB.FECHA_HASTA_BEIB>=IBCO.EJERC_PADRON_IBCO AND BEIB.ESTADO_BEIB='A'
  AND NOT EXISTS (
    SELECT * FROM OPS$GTTORA.BEIB_BENEFICIOS_IBI BEIB1 
    WHERE BEIB1.ID_EPER_OT=IBCO.ID_EPER_OT 
      AND BEIB1.FECHA_HASTA_BEIB>=IBCO.EJERC_PADRON_IBCO 
      AND BEIB1.ESTADO_BEIB='A' 
      AND BEIB1.ID_EPER_BEIB >BEIB.ID_EPER_BEIB 
  )    
JOIN OPS$GTTORA.TBGI_TIPOS_BENEF_GENERICOS TBGI ON TBGI.ID_TBGI =BEIB.ID_TBGI 
--LEFT JOIN OPS$GTTORA.MGIB_MARCA_GESTION_IBI MGIB ON MGIB.ID_EPER_OT = IBCO.ID_EPER_OT AND MGIB.GESTION_MGIB ='C' 
WHERE IBCO.EJERC_PADRON_IBCO=2023 AND IBCO.COD_DERECHO_IBCO IN ('PR','NP') 
  AND (IBCO.PRINCIPAL_IBCO ='S' OR (IBCO.PRINCIPAL_IBCO ='N' AND NVL(MGIB.GESTION_MGIB,'N') ='C'))
ORDER BY IIBU.NUM_FIJO_IIBU, IBCO.NOMBRE_IBCO ;

2. IBI urbana DOMICILIACIONES  (RUSTICADOMICILIACIONES.xls)

--==========================================================
-- 2. RUSTICA DOMICILIACIONES
--==========================================================
SELECT 
  IIBU.REF_CAT_1_IIBU || IIBU.REF_CAT_2_IIBU || IIBU.REF_CAT_NUMSEC_IIBU || IIBU.REF_CAT_DC_1_IIBU || IIBU.REF_CAT_DC_2_IIBU AS REF_CATASTRAL,
  IBCO.NIF_IBCO AS NIF,
  IBCO.NOMBRE_IBCO AS TX_NOMBRE_COMPLETO,
  SUBSTR(NVL(REGE.IBAN_REGE,DOMI.IBAN_DOMI), 1, 2)   AS DOM_COD_PAIS,
  SUBSTR(NVL(REGE.IBAN_REGE,DOMI.IBAN_DOMI), 3, 2)   AS DOM_IBAN_DC,
  SUBSTR(NVL(REGE.IBAN_REGE,DOMI.IBAN_DOMI), 5, 4)   AS DOM_ENTIDAD,
  SUBSTR(NVL(REGE.IBAN_REGE,DOMI.IBAN_DOMI), 9, 4)   AS DOM_OFICINA,
  SUBSTR(NVL(REGE.IBAN_REGE,DOMI.IBAN_DOMI),13, 2)   AS DOM_DC,
  SUBSTR(NVL(REGE.IBAN_REGE,DOMI.IBAN_DOMI),15,10)   AS DOM_CUENTA,
  NVL(PPPP.FECHA_OPERACION_PPP, DOMI.FECHA_ALTA_DOMI) AS FECHA_DOMICILIACION,
  '?????' AS MANDATO_SEPA
FROM OPS$GTTORA.IBCO_IBI_COTITULARES IBCO
JOIN OPS$GTTORA.IIBU_INFO_IBI_URBANA IIBU ON IIBU.ID_REGISTRO_IIBU = IBCO.ID_REGISTRO_IIBU AND IIBU.CLASE_BIEN_IIBU ='RU'
  AND NOT EXISTS (
    SELECT * FROM "OPS$GTTORA".IIBU_INFO_IBI_URBANA IIBU1 
    WHERE IIBU1.NUM_FIJO_IIBU=IIBU.NUM_FIJO_IIBU 
      AND IIBU1.CLASE_BIEN_IIBU='RU' 
      AND IIBU1.ID_SOPORTE_IIBU >IIBU.ID_SOPORTE_IIBU
  ) 
LEFT JOIN (
  OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER 
  JOIN OPS$GTTORA.DOMI_DOMICILIACIONES DOMI 
    ON DOMI.ID_EPER = EPER.ID_EPER 
   AND EPER.TIPO_EPER ='DM'
   AND DOMI.COD_CONC='IR' 
   AND DOMI.ESTADO_DOMI='A') ON EPER.ID_PERS=IBCO.ID_PERS_IBCO 
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_PPP ON EPER_PPP.ID_PERS = IBCO.ID_PERS_IBCO AND EPER_PPP.TIPO_EPER ='PP'
LEFT JOIN OPS$GTTORA.PPPP_PAGO_PLAZOS PPPP ON PPPP.ID_EPER=EPER_PPP.ID_EPER AND PPPP.COD_PPSI ='AC'
LEFT JOIN OPS$GTTORA.REGE_REGISTRO_ENTRADA REGE ON REGE.ID_EPER_SP= PPPP.ID_EPER_SP 
WHERE IBCO.EJERC_PADRON_IBCO=2023 --AND IBCO.COD_DERECHO_IBCO IN ('PR','NP') 
  AND IBCO.PRINCIPAL_IBCO ='S' 
  AND (REGE.IBAN_REGE IS NOT NULL OR DOMI.IBAN_DOMI IS NOT NULL)
ORDER BY IBCO.NOMBRE_IBCO ;