AGM (2) Gestion Tributaria (1) Tasas

 

1. Tasas (EXPEDIENTES_TASAS.xls)

En principio solo tenemos basura, vados, cajeros y parking.

-------------------------------------
-- TASAS 
-------------------------------------
SELECT 
  OT.NIF_SP_OT   AS NIF,
  OT.NOMBRE_SP_OT  AS NOMBRE,
  CORG.DESC_CORTA_CORG AS TASA,
  SCOR.DESCR_CORTA_SCOR || '-' || EDU.DESCR_TARI AS TARIFA,
  NVL(REGE.IBAN_REGE, DOMI.IBAN_DOMI)  AS IBAN,
  DOTP.DESCR_OBJ_TRIB_DOTP AS OBJETO_TRIBUTARIO,
  DOMI.FECHA_ALTA_DOMI AS FECHA_DOMICILIACION,
  BONI.DESC_BONI  AS TIPO_BONIFICACION,
  DSTP.FECHA_INI_BONI_DSTP  AS F_INI_BE,
  DSTP.FECHA_FIN_BONI_DSTP AS F_FIN_BE,
  BONI.PORCENTAJE_BONI  AS BONIFICACION,
  OT.FECHA_ALTA_OT AS FECHA_ALTA,
  'CODIGO CONCEPTO' AS ETIQUETA1,
  SCOR.COD_CONC || '-' || DSTP.COD_TSUC AS VALOR1,
  'NÚM.FIXE' AS ETIQUETA2,
  OT.NUM_FIJO_OT AS VALOR2,
  CASE 
    WHEN SCOR.COD_CONC IN ('BA','CA','GU','PP') THEN 'ADREÇA TRIBUTÀRIA'  
    ELSE ' '
  END AS ETIQUETA3,
  CASE
    WHEN SCOR.COD_CONC IN ('BA','CA','GU','PP') THEN TRIM(NVL(DOTP.COD_SIGL_OT_DOTP    ,'PZ'                    )) || '-' ||
                                                     TRIM(NVL(DOTP.NOMBRE_CALL_OT_DOTP ,'PAIS VALENCIA  PASSEIG')) || '-' ||
                                                     TRIM(NVL(TO_CHAR(DOTP.NUM_OT_DOTP),'31'                    )) || '-' ||
                                                     TRIM(NVL(DOTP.DUP_OT_DOTP         ,'.'                     )) || '-' ||
	                                  	     TRIM(NVL(DOTP.BLOQUE_OT_DOTP      ,'.'                     )) || '-' ||
	                                  	     TRIM(NVL(DOTP.ESCALERA_OT_DOTP    ,'.'                     )) || '-' ||
	                                   	     TRIM(NVL(DOTP.PLANTA_OT_DOTP      ,'.'                     )) || '-' ||
	                                  	     TRIM(NVL(DOTP.PUERTA_OT_DOTP      ,'.'                     ))
  	ELSE ' '
  END AS VALOR3,
  CASE 
    WHEN SCOR.COD_CONC IN ('BA','PP') THEN 'IMPORT'  
    WHEN SCOR.COD_CONC IN ('CA','GU') THEN 'IMP.UNITAT'
    ELSE ' '
  END AS ETIQUETA4,
  CASE 
    WHEN SCOR.COD_CONC IN ('BA','CA','GU','PP') THEN TO_CHAR(EDU.VALOR_FIJA_TARI/100,'999999.00')
    ELSE ' '
  END AS VALOR4,
  CASE 
    WHEN SCOR.COD_CONC LIKE 'BA' THEN 'REF.CADASTRAL'    
    WHEN SCOR.COD_CONC LIKE 'GU' THEN EDU.NOMBRE_UNIDADES_TARI
    WHEN SCOR.COD_CONC LIKE 'CA' THEN 'UNITATS'
    WHEN SCOR.COD_CONC LIKE 'PP' THEN 'SOT.-PLAÇA'
    ELSE ' '
  END AS ETIQUETA5,
  CASE 
    WHEN SCOR.COD_CONC LIKE 'BA'      THEN FIN.REF_CATASTRAL_1_FIN || 
                                           FIN.REF_CATASTRAL_2_FIN || 
	                                   FIN.REF_CATASTRAL_3_FIN || 
	                                   FIN.REF_CATASTRAL_4_FIN || 
	                                   FIN.REF_CATASTRAL_DC_FIN   
    WHEN SCOR.COD_CONC IN ('GU','CA') THEN TO_CHAR(DSUT.UNIDADES_DSUT)
    WHEN SCOR.COD_CONC LIKE 'PP' THEN DOTP.PLANTA_OT_DOTP || '-' || DOTP.PUERTA_OT_DOTP  
    ELSE ' '
  END AS VALOR5,
  CASE 
    WHEN SCOR.COD_CONC IN ('GU') THEN 'PLACA NÚM'
    ELSE ' '
  END AS ETIQUETA6,
  CASE
    WHEN SCOR.COD_CONC IN ('GU') THEN OT.NUM_FIJO_OT
    ELSE ' '
  END AS VALOR6
FROM OPS$GTTORA.DOTP_DATOS_OT_TASAS_PP DOTP               
  LEFT JOIN OPS$GTTORA.DSTP_DATOS_SUBC_TASAS_PP DSTP      
    ON DSTP.ID_DOTP=DOTP.ID_DOTP
  LEFT JOIN OPS$GTTORA.DSUT_DSTP_UNIDADES_TARIFAS DSUT      
    ON DSUT.ID_DOTP =DSTP.ID_DOTP AND DSUT.NUMERO_ORDEN_DSTP =DSTP.NUMERO_ORDEN_DSTP 
  LEFT JOIN XIMO_SCHM.EDU_TARI_TARIFAS EDU                   
    ON EDU.ID_GRTR =DSTP.ID_GRTR 
  JOIN OPS$GTTORA.OTPP_OPERACIONES_TASAS_PP OTPP             
    ON OTPP.ID_OTPP=DOTP.ID_OTPP                            
   AND NOT EXISTS 
     (SELECT * 
      FROM OPS$GTTORA.OTPP_OPERACIONES_TASAS_PP OTPP1 
      WHERE OTPP1.ID_EPER_OT =OTPP.ID_EPER_OT AND OTPP1.ID_OTPP>OTPP.ID_OTPP)
  JOIN OPS$GTTORA.OT_OBJETO_TRIBUTARIO OT                   
     ON OT.ID_EPER_OT =OTPP.ID_EPER_OT    
  LEFT JOIN OPS$GTTORA.BONI_BONIFICACIONES BONI             --(19,390) Tipos de bonificaciones
    ON BONI.ID_BONI =DSTP.ID_BONI
  --LEFT JOIN OPS$GTTORA.TBOT_TIPO_BAJA_OT_TASAS_PP TBOT      --(19,390) Tipos de baja 
  --  ON TBOT.COD_TBOT=OTPP.COD_TBOT  
  LEFT JOIN OPS$GTTORA.SCOR_SUBCONCEPTO_ORGANISMO SCOR      --(19,390)Subconceptos
    ON SCOR.ID_SCOR=EDU.ID_SCOR
   AND SCOR.ELIMINADO_SCOR='N' 
  LEFT JOIN OPS$GTTORA.CORG_CONCEPTOS_ORGANISMO CORG        --(19,084) Conceptos del organismo
    ON CORG.COD_CONC =OT.COD_CONC
    AND CORG.ELIMINADO_CORG='N' 
  -- 
  LEFT JOIN OPS$GTTORA.DOMI_DOMICILIACIONES DOMI 
    ON DOMI.ID_EPER_OT=OT.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_PPP ON EPER_PPP.ID_PERS = DOTP.ID_EPER_TIT  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.EPER_ELEMENTOS_PERSONA EPER 
  --  ON EPER.ID_EPER=DOTP.ID_EPER_OT_ASOCIADO_DOTP
  LEFT JOIN OPS$GTTORA.FIN_FINCAS FIN
    ON FIN.ID_EPER_OT=DOTP.ID_EPER_OT_ASOCIADO_DOTP AND OT.COD_CONC = 'BA' 
    AND NOT EXISTS (
      SELECT * FROM OPS$GTTORA.FIN_FINCAS FIN1 
      WHERE FIN1.ID_EPER_OT=DOTP.ID_EPER_OT_ASOCIADO_DOTP 
        AND FIN1.FECHA_MODIFICACION_FIN>FIN.FECHA_MODIFICACION_FIN
    )
  --  
  WHERE OTPP.COD_TOTP<>'B' 
  ORDER BY OT.NOMBRE_SP_OT;