AGM (5) Gestion Tributaria (4) IBI urbana

 

1. IBI urbana cotitulares (DIVISIONRECIBOTITULARES.xls)

--==========================================================
-- 1. IBI COTITULARES
--==========================================================
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,
  CASE 
  	WHEN REGE.IBAN_REGE IS NOT NULL THEN 'S'
  	ELSE NVL(DOMI.SE_PUEDE_FRACC_DOMI,'N')
  END AS PAGO_FRACCIONADO,
  NVL(PPPP.FECHA_OPERACION_PPP, DOMI.FECHA_ALTA_DOMI) AS F_PAGO_FRACCIONADO,
  IBCO.COEF_PROPIEDAD_IBCO,
  BEIB.ID_TBGI  AS COD_Z_BON_EXE,
  BEIB.FECHA_DESDE_BEIB AS F_INI_BE,
  BEIB.FECHA_HASTA_BEIB AS F_FIN_BE,
  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
--IMPORTANTE MGIB.GESTION_MGIB ='C' PARA FRACCIONAR EL RECIBO EN CADA COPROPIETARIO
JOIN OPS$GTTORA.MGIB_MARCA_GESTION_IBI MGIB ON MGIB.ID_EPER_OT = IBCO.ID_EPER_OT AND MGIB.GESTION_MGIB ='C' 
JOIN OPS$GTTORA.IIBU_INFO_IBI_URBANA IIBU ON IIBU.ID_REGISTRO_IIBU = IBCO.ID_REGISTRO_IIBU AND IIBU.CLASE_BIEN_IIBU ='UR'
  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='UR' 
      AND IIBU1.ID_SOPORTE_IIBU >IIBU.ID_SOPORTE_IIBU
  ) 
LEFT JOIN  OPS$GTTORA.DOMI_DOMICILIACIONES DOMI 
  ON DOMI.ID_EPER_OT=IBCO.ID_EPER_OT AND TRIM(DOMI.NIF_SP_DOMI) =TRIM(IBCO.NIF_IBCO) AND DOMI.ESTADO_DOMI='A'
  AND NOT EXISTS (
    SELECT * FROM OPS$GTTORA.DOMI_DOMICILIACIONES DOMI1 
    WHERE DOMI1.ID_EPER_OT=DOMI.ID_EPER_OT 
      AND TRIM(DOMI1.NIF_SP_DOMI) =TRIM(IBCO.NIF_IBCO) 
      AND DOMI.ESTADO_DOMI='A'
      AND DOMI1.FECHA_ALTA_DOMI>DOMI.FECHA_ALTA_DOMI
  )
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_PPP ON EPER_PPP.ID_PERS = IBCO.ID_EPER_SP 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 
LEFT 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
WHERE IBCO.EJERC_PADRON_IBCO=2023 AND IBCO.COD_DERECHO_IBCO IN ('PR','NP') --  AND  IBCO.ID_EPER_OT IN (91318566,91363636)
ORDER BY IIBU.NUM_FIJO_IIBU, IBCO.NOMBRE_IBCO ;

2. IBI urbana bonificaciones  (URBANABONIFICACIONES.xls)

--==========================================================
-- 2. IBI 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 ='UR'
  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='UR' 
      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 ;

3. IBI urbana domiciliaciones  (URBANADOMICILIACIONES.xls)

--==========================================================
-- 3. IBI 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
--IMPORTANTE MGIB.GESTION_MGIB ='C' PARA FRACCIONAR EL RECIBO EN CADA COPROPIETARIO
JOIN OPS$GTTORA.MGIB_MARCA_GESTION_IBI MGIB ON MGIB.ID_EPER_OT = IBCO.ID_EPER_OT AND MGIB.GESTION_MGIB ='C' 
JOIN OPS$GTTORA.IIBU_INFO_IBI_URBANA IIBU ON IIBU.ID_REGISTRO_IIBU = IBCO.ID_REGISTRO_IIBU AND IIBU.CLASE_BIEN_IIBU ='UR'
  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='UR' 
      AND IIBU1.ID_SOPORTE_IIBU >IIBU.ID_SOPORTE_IIBU
  ) 
LEFT JOIN  OPS$GTTORA.DOMI_DOMICILIACIONES DOMI 
  ON DOMI.ID_EPER_OT=IBCO.ID_EPER_OT AND TRIM(DOMI.NIF_SP_DOMI) =TRIM(IBCO.NIF_IBCO) AND DOMI.ESTADO_DOMI='A'
  AND NOT EXISTS (
    SELECT * FROM OPS$GTTORA.DOMI_DOMICILIACIONES DOMI1 
    WHERE DOMI1.ID_EPER_OT=DOMI.ID_EPER_OT 
      AND TRIM(DOMI1.NIF_SP_DOMI) =TRIM(IBCO.NIF_IBCO) 
      AND DOMI.ESTADO_DOMI='A'
      AND DOMI1.FECHA_ALTA_DOMI>DOMI.FECHA_ALTA_DOMI
  )
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_PPP ON EPER_PPP.ID_PERS = IBCO.ID_EPER_SP 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 
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'))
  AND (REGE.IBAN_REGE IS NOT NULL OR DOMI.IBAN_DOMI IS NOT NULL)
ORDER BY IIBU.NUM_FIJO_IIBU, IBCO.NOMBRE_IBCO ;

4. IBI urbana 3 PLAZOS  (URBANATRESPLAZOS.xls)

En principio solo se dejan 3 plazos si se ha domiciliado. No se incluyen los planes personalizados de pago "PPPP"

--==========================================================
-- 4. IBI 3 PLAZOS
--==========================================================
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,
  DOMI.SE_PUEDE_FRACC_DOMI AS PAGO_FRACCIONADO,
  DOMI.FECHA_ALTA_DOMI AS F_PAGO_FRACCIONADO
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 ='UR'
  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='UR' 
      AND IIBU1.ID_SOPORTE_IIBU >IIBU.ID_SOPORTE_IIBU
  ) 
JOIN  OPS$GTTORA.DOMI_DOMICILIACIONES DOMI 
  ON DOMI.ID_EPER_OT=IBCO.ID_EPER_OT AND TRIM(DOMI.NIF_SP_DOMI) =TRIM(IBCO.NIF_IBCO) AND DOMI.ESTADO_DOMI='A' AND DOMI.SE_PUEDE_FRACC_DOMI='S'
  AND NOT EXISTS (
    SELECT * FROM OPS$GTTORA.DOMI_DOMICILIACIONES DOMI1 
    WHERE DOMI1.ID_EPER_OT=DOMI.ID_EPER_OT 
      AND TRIM(DOMI1.NIF_SP_DOMI) =TRIM(IBCO.NIF_IBCO) 
      AND DOMI.ESTADO_DOMI='A'
      AND DOMI1.FECHA_ALTA_DOMI>DOMI.FECHA_ALTA_DOMI
  )
--IMPORTANTE MGIB.GESTION_MGIB ='C' PARA FRACCIONAR EL RECIBO EN CADA COPROPIETARIO
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 ;