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 ;

AGM (4) Gestion Tributaria (3) IAE

 

1. IAE Bonificacione y exenciones (IAEBONIFICACIONESEXENCIONES.xls)

-------------------------------------
-- IAE BONIFICACIONES Y EXENCIONES 
-------------------------------------
SELECT 
  IIAE.NUMERO_FIJO_IIAE AS REFERENCIA_IAE,
  IIAE.NIF_IIAE AS NIF,
  IIAE.NOMBRE_IIAE AS NOMBRE_RS,
  TBFI.COD_BBIA AS COD_Z_BON_EXE,
  IIAE.BENEF_FISCAL_LIT_IIAE AS DESCRIPCION,
  IIAE.PORCENTAJE_BENEF_FISCAL_IIAE AS PORC_BON,
  TO_CHAR(BEIA.ANYO_INICIO_BEIA)|| '0101'   AS F_INI_BE,
  IIAE.FECHA_LIMITE_BENEF_IIAE AS F_FIN_BE
--  SELECT *
FROM "OPS$GTTORA".IIAE_INFO_IAE IIAE
LEFT JOIN OPS$GTTORA.OTIA_OBJETO_TRIBUTARIO_IAE OTIA ON OTIA.NUMERO_FIJO_OTIA  =IIAE.NUMERO_FIJO_IIAE AND OTIA.EJER_EFECTIVIDAD_OTIA =IIAE.EJER_EFECTIVIDAD_IIAE 
LEFT JOIN "OPS$GTTORA".BEIA_BENEFICIOS_IAE BEIA ON BEIA.ID_EPER_OT =OTIA.ID_EPER_OT 
  AND NOT EXISTS (
    SELECT * FROM OPS$GTTORA.BEIA_BENEFICIOS_IAE BEIA1 
    WHERE BEIA1.ID_EPER_OT =OTIA.ID_EPER_OT 
      AND BEIA1.ANYO_INICIO_BEIA<BEIA.ANYO_INICIO_BEIA
  )
  LEFT JOIN "OPS$GTTORA".TBFI_TIPO_BENEFICIO_FISCAL_IAE TBFI ON TBFI.ID_TBFI =beia.ID_TBFI 
WHERE IIAE.EJER_EFECTIVIDAD_IIAE >=2023 AND IIAE.PORCENTAJE_BENEF_FISCAL_IIAE >'000'
ORDER BY IIAE.NUMERO_FIJO_IIAE ; 

2. IAE Domiciliaciones (IAEDOMICILIACIONES.xls)

-------------------------------------
-- IAE DOMICILIACIONES
-------------------------------------
SELECT
  IIAE.NUMERO_FIJO_IIAE AS REFERENCIA_IAE,
  IIAE.NIF_IIAE AS NIF,
  IIAE.NOMBRE_IIAE AS NOMBRE_RS,
  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".IIAE_INFO_IAE IIAE
LEFT JOIN OPS$GTTORA.OTIA_OBJETO_TRIBUTARIO_IAE OTIA ON OTIA.NUMERO_FIJO_OTIA  =IIAE.NUMERO_FIJO_IIAE AND OTIA.EJER_EFECTIVIDAD_OTIA =IIAE.EJER_EFECTIVIDAD_IIAE 
LEFT JOIN OPS$GTTORA.DOMI_DOMICILIACIONES DOMI 
    ON DOMI.ID_EPER_OT=OTIA.ID_EPER_OT 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 DOMI1.ESTADO_DOMI='A'
        AND DOMI1.FECHA_ALTA_DOMI>DOMI.FECHA_ALTA_DOMI
    )
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER ON EPER.ID_EPER = OTIA.ID_EPER_OT
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_PPP ON EPER_PPP.ID_PERS =EPER.ID_EPER 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 IIAE.EJER_EFECTIVIDAD_IIAE >=2023 AND DOMI.IBAN_DOMI IS NOT NULL OR REGE.IBAN_REGE IS NOT NULL
ORDER BY IIAE.NUMERO_FIJO_IIAE ; 

AGM (3) Gestion Tributaria (2) Vehiculos

 

1. Vehículos (CENSOVEHICULOS.xls) 

-------------------------------------
-- VEHICULOS
-------------------------------------
SELECT
  OTIV.ID_OTIV AS ID_IVTM_VEH,
  OTIV.ID_TIVE || '?????' AS COD_Z_VEH,
  OTIV.ID_PERS_OTIV AS ID_SUJETO,
  '46240' AS  COD_ENTIDAD,
  '?????' AS COD_Z_INCID,
  CASE 
  	WHEN NVL(REGE.IBAN_REGE, DOMI.IBAN_DOMI) IS NOT NULL THEN 'S'
  	ELSE 'N'
  END AS DOMICILIADO,
  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,
  --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,
 OTIV.NUM_MATRICULA_OTIV AS MATRICULA,
 CASE
 	WHEN SUBSTR(TRIM(OTIV.NUM_MATRICULA_OTIV),1,1)>'9' THEN SUBSTR(TRIM(OTIV.NUM_MATRICULA_OTIV),1,1)
 	ELSE ''
 END ||
 CASE
 	WHEN SUBSTR(TRIM(OTIV.NUM_MATRICULA_OTIV),2,1)>'9' THEN SUBSTR(TRIM(OTIV.NUM_MATRICULA_OTIV),2,1)
 	ELSE ''
 END AS MAT_LETRAS_ANT,
 REGEXP_REPLACE(TRIM(OTIV.NUM_MATRICULA_OTIV), '[^0-9]+', '') AS MAT_NUMERO,
 REGEXP_REPLACE(SUBSTR(TRIM(OTIV.NUM_MATRICULA_OTIV),3) , '[0-9]+', '') AS MAT_LETRAS_POS,
 OTIV.NUM_BASTIDOR_OTIV AS BASTIDOR, 
 regexp_replace(OTIV.MARCA_MODELO_OTIV,'(((\w+)\s+){1}).*','\1')  AS MARCA,	
 REPLACE(OTIV.MARCA_MODELO_OTIV,regexp_replace(OTIV.MARCA_MODELO_OTIV,'(((\w+)\s+){1}).*','\1'),'')  AS MODELO,
 OTIV.CILINDRADA_OTIV AS CC,
 OTIV.POTENCIA_FISCAL_OTIV  AS CV,
 --OTIV.NUM_PLAZAS_OTIV  AS PLAZAS,
 LSOT.PLAZAS  AS PLAZAS,
 LSOT.NUM_PLAZAS_MAX AS PLAZAS_TXT,
 --OTIV.TARA_OTIV AS KG,
 LSOT.TARA_LSOT AS KG,
 '?????' AS ID_CALCULO,
 '?????' AS USAR_DT,
 '?????' AS BE_OFICIO_PETICION,
 DIRE.COD_PROV AS DT_COD_PROVINCIA,
 DIRE.COD_CAT_MUNE  AS  DT_COD_MUNICIPIO,
 DIRE.ID_CALL AS DT_ID_VIA,
 DIRE.NUM_DIRE AS DT_NUMERO,
 DIRE.BLOQUE_DIRE  AS DT_PORTAL,
 DIRE.ESCALERA_DIRE AS DT_ESCALERA,
 DIRE.PLANTA_DIRE AS DT_PISO,
 DIRE.PUERTA_DIRE AS DT_PUERTA,
 DIRE.DISTRITO_POSTAL_DIRE AS DT_CP,
 DIRE.REF_CAT_1_DIRE || DIRE.REF_CAT_2_DIRE || DIRE.REF_CAT_NUM_DIRE || DIRE.REF_CAT_DC_1_DIRE || DIRE.REF_CAT_DC_2_DIRE AS  DT_REFCAT,
 --'?????' AS ID_TARIFA,---ÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑ
 CASE
	--WHEN OTIV.ID_TIVE='TU' THEN OTIV.POTENCIA_FISCAL_OTIV 
	WHEN OTIV.ID_TIVE='TU' AND OTIV.POTENCIA_FISCAL_OTIV<8   THEN 'TURISM08' 
	WHEN OTIV.ID_TIVE='TU' AND OTIV.POTENCIA_FISCAL_OTIV>=8  AND OTIV.POTENCIA_FISCAL_OTIV<12 THEN 'TURISM12' 
	WHEN OTIV.ID_TIVE='TU' AND OTIV.POTENCIA_FISCAL_OTIV>=12 AND OTIV.POTENCIA_FISCAL_OTIV<16 THEN 'TURISM16'
	WHEN OTIV.ID_TIVE='TU' AND OTIV.POTENCIA_FISCAL_OTIV>=16 AND OTIV.POTENCIA_FISCAL_OTIV<20 THEN 'TURISM20'
	WHEN OTIV.ID_TIVE='TU' AND OTIV.POTENCIA_FISCAL_OTIV>=20 THEN 'TURISM99'
--	WHEN OTIV.ID_TIVE='AU' THEN OTIV.NUM_PLAZAS__OTIV
	WHEN OTIV.ID_TIVE='AU' AND OTIV.NUM_PLAZAS_OTIV <21 THEN 'AUTOB21'
	WHEN OTIV.ID_TIVE='AU' AND OTIV.NUM_PLAZAS_OTIV>=21 AND OTIV.NUM_PLAZAS_OTIV<=50 THEN 'AUTOB50'
	WHEN OTIV.ID_TIVE='AU' AND OTIV.NUM_PLAZAS_OTIV >50 THEN 'AUTOB99'
--	WHEN OTIV.ID_TIVE='CA' THEN OTIV.CARGA_UTIL_OTIV
	WHEN OTIV.ID_TIVE='CA' AND OTIV.CARGA_UTIL_OTIV<1000   THEN 'CAMION01K'
	WHEN OTIV.ID_TIVE='CA' AND OTIV.CARGA_UTIL_OTIV>=1000  AND OTIV.CARGA_UTIL_OTIV<3000 THEN 'CAMION03K'
	WHEN OTIV.ID_TIVE='CA' AND OTIV.CARGA_UTIL_OTIV>=3000  AND OTIV.CARGA_UTIL_OTIV<10000 THEN 'CAMION10K'
	WHEN OTIV.ID_TIVE='CA' AND OTIV.CARGA_UTIL_OTIV>=10000 THEN 'CAMION99K'
--	WHEN OTIV.ID_TIVE='TR' THEN OTIV.POTENCIA_FISCAL_OTIV
	WHEN OTIV.ID_TIVE='TR' AND OTIV.POTENCIA_FISCAL_OTIV<16 THEN 'TRACTOR16'
	WHEN OTIV.ID_TIVE='TR' AND OTIV.POTENCIA_FISCAL_OTIV>16 AND  OTIV.POTENCIA_FISCAL_OTIV<=25 THEN 'TRACTOR25'
	WHEN OTIV.ID_TIVE='TR' AND OTIV.POTENCIA_FISCAL_OTIV>25 THEN 'TRACTOR99'	
--	WHEN OTIV.ID_TIVE='RE' THEN OTIV.CARGA_UTIL_OTIV
--  WHEN OTIV.ID_TIVE='SE' THEN OTIV.CARGA_UTIL_OTIV	
	WHEN OTIV.ID_TIVE IN ('RE','SE') AND OTIV.CARGA_UTIL_OTIV<=750  THEN 'REMSEM750'
	WHEN OTIV.ID_TIVE IN ('RE','SE') AND OTIV.CARGA_UTIL_OTIV>750   AND OTIV.CARGA_UTIL_OTIV<1000 THEN 'REMSEM01T'
	WHEN OTIV.ID_TIVE IN ('RE','SE') AND OTIV.CARGA_UTIL_OTIV>=1000 AND OTIV.CARGA_UTIL_OTIV<3000 THEN 'REMSEM03T'
	WHEN OTIV.ID_TIVE IN ('RE','SE') AND OTIV.CARGA_UTIL_OTIV>=3000 THEN 'REMSEM99T'
--	WHEN OTIV.ID_TIVE='CI' THEN OTIV.CILINDRADA_OTIV	
    WHEN OTIV.ID_TIVE='CI' THEN 'CICLOM'
--  WHEN OTIV.ID_TIVE='MO' THEN OTIV.CILINDRADA_OTIV
    WHEN OTIV.ID_TIVE='MO' AND OTIV.CILINDRADA_OTIV<125   THEN 'MOTOCI125'
    WHEN OTIV.ID_TIVE='MO' AND OTIV.CILINDRADA_OTIV>=125  AND OTIV.CILINDRADA_OTIV<250   THEN 'MOTOCI250'
    WHEN OTIV.ID_TIVE='MO' AND OTIV.CILINDRADA_OTIV>=250  AND OTIV.CILINDRADA_OTIV<500   THEN 'MOTOCI500'
    WHEN OTIV.ID_TIVE='MO' AND OTIV.CILINDRADA_OTIV>=500  AND OTIV.CILINDRADA_OTIV<=1000 THEN 'MOTOCI01K'
    WHEN OTIV.ID_TIVE='MO' AND OTIV.CILINDRADA_OTIV>=1000 THEN 'MOTOCI99K'
	ELSE ''
  END AS ID_TARIFA,
 OTIV.ID_TBIV AS COD_Z_BON_EXE,
 CASE 
 	WHEN OTIV.ANYO_INICIO_OTIV IS NOT NULL AND OTIV.ID_TBIV IS NOT NULL THEN '01/01/' || TO_CHAR(OTIV.ANYO_INICIO_OTIV)
 	ELSE NULL
 END AS F_INI_BE,
 CASE 
 	WHEN OTIV.ANYO_FIN_OTIV IS NOT NULL AND OTIV.ID_TBIV IS NOT NULL THEN '31/12/' || TO_CHAR(OTIV.ANYO_FIN_OTIV)
 	ELSE NULL
 END AS F_FIN_BE,
 '?????'||OTIV.MAT_COD_TRAMITE_OTIV AS TRAMITE_ALTA,
 --OTIV.FECHA_PRIMERA_MATRI AS F_ALTA,	
 '?????' AS F_ALTA,
 OTIV.FECHA_TRAMITE_OTIV AS F_ALTA_PADRON,
 CASE 
 	--WHEN SUBSTR(OTIV.COD_TOIV,1) IN ('B') THEN OTIV.FECHA_TRAMITE_OTIV
	WHEN LSOT.TIPO_REGISTRO_LSOT ='B' THEN OTIV.FECHA_TRAMITE_OTIV
 	ELSE NULL
 END AS F_BAJA_PADRON,
 CASE 
 	--WHEN SUBSTR(OTIV.COD_TOIV,1) IN ('B') THEN 'S'
	WHEN LSOT.TIPO_REGISTRO_LSOT ='B' THEN 'S' 
 	ELSE 'N'
 END || '??????' AS BAJA_EN_PADRON,
 '?????' AS TRAMITE_BAJA,
 '?????' AS BAJA,
 OTIV.NIF_SP_OTIV AS NIF_TRAFICO,
 NVL(PPPP.FECHA_OPERACION_PPP, DOMI.FECHA_ALTA_DOMI) AS FECHA_DOMICILIACION,
 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,	
 OTIV.SERVICIO_TIT_OTIV  AS COD_Z_SERVICIO,	
 OTIV.PROVINCIA_SP_OTIV AS PROVINCIA_FISCAL,
 OTIV.MUNICIPIO_SP_OTIV AS MUNICIPIO_FISCAL,
 '?????' AS LOCALIDAD_FISCAL,
 OTIV.DOMICILIO_NOTTIT_OTIV AS DOMICILIO_FISCAL,
 OTIV.CP_NOTTIT_OTIV AS CP_FISCAL,
 '????' AS NIVE,
 OTIV.FECHA_MATRICULACION_OTIV  AS F_MATRICULACION,
 OTIV.FECHA_PRIMERA_MATRI AS F_PRIMERA_MATRICULACION,
 OTIV.CLAS_INDUSTRIA_OTIV AS CLASIFICACION_INDUSTRIA,
 OTIV.CAT_HOMOLOGACIONUE_OTIV  AS CATEGORIA_UE,
 OTIV.POTENCIA_NETA_OTIV  AS POTENCIA_NETA,
 OTIV.TARA_OTIV AS TARA,
 OTIV.COMBUSTIBLE_OTIV AS COMBUSTIBLE,
 OTIV.TIPO_ALIMENTACION_OTIV AS TIPO_ALIMENTACION,
 OTIV.CONSUMO_OTIV AS CONSUMO,
 OTIV.NIVEL_EMISIONES_OTIV AS NIVEL_EMISIONES,
 OTIV.EMISIONES_CO2_OTIV AS EMISIONES_CO2,
 OTIV.IND_TRANSFERENCIA_OTIV AS INDICADOR_TRANSFERENCIA,
 OTIV.IND_SUSTRACCION_OTIV AS INDICADOR_SUSTRACCION,
 OTIV.IND_PRECINTO_OTIV AS INDICADOR_PRECINTO,
 OTIV.IND_EMBARGO_OTIV AS INDICADOR_EMBARGO,
 OTIV.CAT_ELECTRICA_OTIV AS CATEGORIA_ELECTRICO,
 OTIV.AUTO_ELECTRICA_OTIV AS AUTONOMIA_ELECTRICA,
 '?????' AS MASA_ORDEN_MARCHA,	
 '?????' AS COD_Z_RES_NOT,
 '?????' AS FECHA_ALTA_MATRICULA,
 'N' AS PAGO_FRACCIONADO,
 '?????' AS CLASE_AMBIENTAL,
 NULL AS F_PAGO_FRACCIONADO,
 OTIV.TIPO_VEHICULO_OTIV AS TIPO_VEHICULO,
 NVL(PERS_PPPP.NIF_PERG ,DOMI.NIF_TITULAR_DOMI)   AS DOM_NIF,
 NVL(PERS_PPPP.NOMBRE_PERG, DOMI.TITULAR_CUENTA_DOMI) AS DOM_TITULAR,
 '?????' AS CODIDGO_MULTI_ENTIDAD,
 '?????' AS MANDATO_SEPA,
 'S' AS CENSADO
FROM OPS$GTTORA.OTIV_OBJETO_TRIBUTARIO_IVTM OTIV 
LEFT JOIN OPS$GTTORA.LSOT_LINEAS_SOP_TRAFICO_IVTM LSOT ON LSOT.MATRICULA =OTIV.NUM_MATRICULA_OTIV 
  AND NOT EXISTS (SELECT * FROM OPS$GTTORA.LSOT_LINEAS_SOP_TRAFICO_IVTM LSOT1 WHERE LSOT1.ID_SOOT = LSOT.ID_SOOT  AND LSOT1.ID_LSOT >LSOT.ID_LSOT)
--
JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_DIRE ON EPER_DIRE.ID_PERS = OTIV.ID_PERS_OTIV AND EPER_DIRE.TIPO_EPER = 'DI'
JOIN OPS$GTTORA.DIRE_DIRECCIONES DIRE ON DIRE.ID_EPER =EPER_DIRE.ID_EPER 
  AND NOT EXISTS (
    SELECT *
    FROM OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_DIRE1 
    JOIN OPS$GTTORA.DIRE_DIRECCIONES DIRE1  
      ON EPER_DIRE1.ID_PERS = OTIV.ID_PERS_OTIV AND EPER_DIRE.TIPO_EPER = 'DI'
      AND DIRE1.ID_EPER = EPER_DIRE1.ID_EPER AND DIRE1.FECHA_MODIFICACION_DIRE >DIRE.FECHA_MODIFICACION_DIRE)
LEFT JOIN OPS$GTTORA.DOMI_DOMICILIACIONES DOMI 
    ON DOMI.ID_EPER_OT=OTIV.ID_EPER_OT  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 DOMI1.FECHA_ALTA_DOMI>DOMI.FECHA_ALTA_DOMI)
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_PPP ON EPER_PPP.ID_PERS = OTIV.ID_PERS_OTIV  AND EPER_PPP.TIPO_EPER ='PP'
LEFT JOIN OPS$GTTORA.PPPP_PAGO_PLAZOS PPPP ON PPPP.ID_EPER=EPER_PPP.ID_EPER 
LEFT JOIN OPS$GTTORA.REGE_REGISTRO_ENTRADA REGE ON REGE.ID_EPER_SP= PPPP.ID_EPER_SP 
LEFT JOIN OPS$GTTORA.PERS_PERSONAS PERS_PPPP ON PERS_PPPP.ID_PERS = EPER_PPP.ID_PERS 
-- Tablas de codigo - Descripción
LEFT JOIN OPS$GTTORA.SIOT_SITUACION_OT_IVTM SIOT ON SIOT.ID_SIOT=OTIV.ID_SIOT 
LEFT JOIN OPS$GTTORA.TOIV_TIPO_OPERACION_IVTM TOIV ON TOIV.COD_TOIV=OTIV.COD_TOIV 
LEFT JOIN OPS$GTTORA.ORIG_ORIGENES_IVTM ORIG ON ORIG.ID_ORIG = OTIV.ID_ORIG
LEFT JOIN OPS$GTTORA.TIMA_TIPO_MATRICULAS_IVTM TIMA ON TIMA.ID_TIMA= OTIV.ID_TIMA
LEFT JOIN OPS$GTTORA.CLVE_CLASE_VEHICULO_IVTM CLVE ON CLVE.ID_CLVE= OTIV.ID_CLVE 
LEFT JOIN OPS$GTTORA.SCVE_SUBCLASE_VEHICULO_IVTM SCVE ON SCVE.ID_SCVE = OTIV.ID_SCVE 
LEFT JOIN OPS$GTTORA.TIVE_TIPO_VEHICULO_IVTM TIVE  ON TIVE.ID_TIVE = OTIV.ID_TIVE 
LEFT JOIN OPS$GTTORA.DAEI_DATO_ECONOMICO_IVTM DAEI ON DAEI.COD_DAEI =TIVE.COD_DAEI   --viene de TIVE_TIPO_VEHICULO_IVTM
LEFT JOIN OPS$GTTORA.BISB_SUBTIPO_BIEN BISB ON BISB.COD_BISB =TIVE.COD_BISB  --viene de TIVE_TIPO_VEHICULO_IVTM
WHERE OTIV.REG_ACTIVO='S' --AND OTIV.ID_SIOT <>'B'
ORDER BY OTIV.NUM_MATRICULA_OTIV ;

2. Bonificaciones de Vehículos (IVTMBONIFICACIONES.xls) 

-------------------------------------
-- BONIFICACIONES DE VEHICULOS
-------------------------------------
SELECT 
  OTIV.NUM_MATRICULA_OTIV AS MATRICULA,
  OTIV.NUM_BASTIDOR_OTIV AS BASTIDOR, 
  OTIV.NIF_SP_OTIV AS NIF,
  OTIV.NOMBRE_SP_OTIV AS TX_NOMBRE_COMPLETO,
  OTIV.ID_TBIV AS COD_Z_BON_EXE,
  CASE 
    WHEN OTIV.ID_TBIV IS NULL THEN 'NO BONIFICAT '
    WHEN OTIV.ID_TBIV ='AY'   THEN 'BONIFICAT-AJUNTAMENT'
    WHEN OTIV.ID_TBIV ='CR'   THEN 'BONIFICAT-CREU ROJA'
    WHEN OTIV.ID_TBIV ='VA'   THEN 'BONIFICAT-AGRÍCOLA'
    WHEN OTIV.ID_TBIV ='VH'   THEN 'BONIFICAT-HISTÒRIC'
    WHEN OTIV.ID_TBIV ='VM'   THEN 'BONIFICAT-DISCAPACITAT'
    WHEN OTIV.ID_TBIV ='VO'   THEN 'BONIFICAT-VEHICLE OFICIAL'
    ELSE                           'NO BONIFICAT'
  END AS DESCRIPCION,
  OTIV.PORCENTAJE_BENEF_FISCAL_OTIV AS PORCENTAJE,
  CASE 
    WHEN OTIV.ANYO_INICIO_OTIV IS NOT NULL AND OTIV.ID_TBIV IS NOT NULL THEN '01/01/' || TO_CHAR(OTIV.ANYO_INICIO_OTIV)
    ELSE NULL
  END AS F_INI_BE,
  CASE 
    WHEN OTIV.ANYO_FIN_OTIV IS NOT NULL AND OTIV.ID_TBIV IS NOT NULL THEN '31/12/' || TO_CHAR(OTIV.ANYO_FIN_OTIV)
    ELSE NULL
  END AS F_FIN_BE
FROM OPS$GTTORA.OTIV_OBJETO_TRIBUTARIO_IVTM OTIV 
WHERE OTIV.REG_ACTIVO='S' AND OTIV.PORCENTAJE_BENEF_FISCAL_OTIV >0
ORDER BY OTIV.NUM_MATRICULA_OTIV ;

3. Domiciliaciones Vehículos (IVTMDOMICILIACIONES.xls) 

------------------------------------
-- DOMICILIACIONES DE VEHICULOS
-------------------------------------
SELECT 
  OTIV.NUM_MATRICULA_OTIV AS MATRICULA,
  OTIV.NUM_BASTIDOR_OTIV AS BASTIDOR, 
  OTIV.NOMBRE_SP_OTIV 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.OTIV_OBJETO_TRIBUTARIO_IVTM OTIV 
LEFT JOIN OPS$GTTORA.LSOT_LINEAS_SOP_TRAFICO_IVTM LSOT ON LSOT.MATRICULA =OTIV.NUM_MATRICULA_OTIV 
  AND NOT EXISTS (SELECT * FROM OPS$GTTORA.LSOT_LINEAS_SOP_TRAFICO_IVTM LSOT1 WHERE LSOT1.ID_SOOT = LSOT.ID_SOOT  AND LSOT1.ID_LSOT >LSOT.ID_LSOT)
LEFT JOIN OPS$GTTORA.DOMI_DOMICILIACIONES DOMI 
    ON DOMI.ID_EPER_OT=OTIV.ID_EPER_OT  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 DOMI1.FECHA_ALTA_DOMI>DOMI.FECHA_ALTA_DOMI)
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_PPP ON EPER_PPP.ID_PERS = OTIV.ID_PERS_OTIV  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 OTIV.REG_ACTIVO='S' 
  AND (REGE.IBAN_REGE IS NOT NULL OR DOMI.IBAN_DOMI IS NOT NULL)
ORDER BY OTIV.NUM_MATRICULA_OTIV ;