CREATE OR REPLACE VIEW Q022DV02
(Q022BT00_ID_GRUPO, Q022DT00_ID_CUES, Q022DT00_IDEN_LICEN, Q022DT00_FEC_ENT, Q022DT00_ESTADO_LICEN, 
 Q022DT00_CAUSA_BAJA, Q022DT00_ENTI, Q022DT00_TVIA, Q022DT00_CALLE, Q022DT00_POR_CL_POR, 
 Q022DT00_BLOQ, Q022DT00_NUME, Q022DT00_BIS, Q022DT00_CDIR, Q022DT00_RS_PROMO, 
 Q022DT00_CIF_DNI, Q022DT00_MUN_PROV, Q022DT00_MUN_MUNI, Q022DT00_ENTI_PRO, Q022DT00_TVIA_PRO, 
 Q022DT00_CALLE_PRO, Q022DT00_POR_CL_POR_PRO, Q022DT00_BLOQ_PRO, Q022DT00_NUME_PRO, Q022DT00_BIS_PRO, 
 Q022DT00_CDIR_PRO, Q022DT00_TLF, Q022DT00_MAIL, Q022DT00_FAX, Q022DT00_WEB, 
 Q022DT00_PRESUPUESTO, Q022DT00_F_INI, Q022DT00_F_FIN, Q022DT00_TIPO_OBRA, Q022DT00_USO_RES, 
 Q022DT00_ACTIVIDAD, Q022DT00_NVN, Q022DT00_NVR, Q022DT00_NVL, Q022DT00_NVVPO, 
 Q022DT00_NVVS, Q022DT00_NVPOT, Q022DT00_NVALDOT, Q022DT00_NVREAL, Q022DT00_NVVTMG, 
 Q022DT00_NVVTME, Q022DT00_NTOT, Q022DT00_NSP45, Q022DT00_NSP75, Q022DT00_NSP90, 
 Q022DT00_NSP120, Q022DT00_NSPT, Q022DT00_PRINC_USO, Q022DT00_PU_ESPECIF, Q022DT00_PRECIO_MED, 
 Q022DT00_OBS, Q022DT00_NORA_T17_LOCALIZACION, Q022DT00_DIR_PRO_EXTERNO, Q022DT00_DIR_NUEVA, FCA02T00_CODMUNICIP, 
 FCA02T00_CODPROVINC)
AS 
SELECT 
  Q022DS00.Q022BT00_ID_GRUPO        ,
  Q022DT00_ID_CUES         ,
  Q022DT00_IDEN_LICEN      ,
  Q022DT00_FEC_ENT         ,
  Q022DT00_ESTADO_LICEN    ,
  Q022DT00_CAUSA_BAJA      ,
  Q022DT00_ENTI            ,
  Q022DT00_TVIA            ,
  Q022DT00_CALLE           ,
  Q022DT00_POR_CL_POR      ,
  Q022DT00_BLOQ            ,
  Q022DT00_NUME            ,
  Q022DT00_BIS             ,
  Q022DT00_CDIR            ,
  Q022DT00_RS_PROMO        ,
  Q022DT00_CIF_DNI         ,
  Q022DT00_MUN_PROV        ,
  Q022DT00_MUN_MUNI        ,
  Q022DT00_ENTI_PRO        ,
  Q022DT00_TVIA_PRO        ,
  Q022DT00_CALLE_PRO       ,
  Q022DT00_POR_CL_POR_PRO  ,
  Q022DT00_BLOQ_PRO        ,
  Q022DT00_NUME_PRO        ,
  Q022DT00_BIS_PRO         ,
  Q022DT00_CDIR_PRO        ,
  Q022DT00_TLF             ,
  Q022DT00_MAIL            ,
  Q022DT00_FAX             ,
  Q022DT00_WEB             ,
  Q022DT00_PRESUPUESTO     ,
  Q022DT00_F_INI           ,
  Q022DT00_F_FIN           ,
  Q022DT00_TIPO_OBRA       ,
  Q022DT00_USO_RES         ,
  Q022DT00_ACTIVIDAD       ,
  Q022DT00_NVN             ,
  Q022DT00_NVR             ,
  Q022DT00_NVL             ,
  Q022DT00_NVVPO           ,
  Q022DT00_NVVS            ,
  Q022DT00_NVPOT           ,
  Q022DT00_NVALDOT         ,
  Q022DT00_NVREAL          ,
  Q022DT00_NVVTMG          ,
  Q022DT00_NVVTME          ,
  Q022DT00_NTOT            ,
  Q022DT00_NSP45           ,
  Q022DT00_NSP75           ,
  Q022DT00_NSP90           ,
  Q022DT00_NSP120          ,
  Q022DT00_NSPT            ,
  Q022DT00_PRINC_USO       ,
  Q022DT00_PU_ESPECIF      ,
  Q022DT00_PRECIO_MED      ,
  Q022DT00_OBS  ,
   CASE WHEN ( TRIM(Q022DT00_CALLE) = 0 OR TRIM(Q022DT00_CALLE) = '' OR TRIM(Q022DT00_CALLE) IS NULL) THEN NORA_T17_LOCALIDAD.DS_O
       ELSE NORA_T17_LOCALIDAD.DS_O  || DECODE(NORA_T17_LOCALIDAD.DS_O,'','',', ')  || NORA_T17_CALLE.DS_O || ' ' ||  DECODE ( Q022DT00_BLOQ, '0' , '','','', 'Bloque:'||Q022DT00_BLOQ)  || ' '  || DECODE ( Q022DT00_NUME, '0' , '','','',  'Nš:'||Q022DT00_NUME) || ' '||  DECODE( Q022DT00_BIS, '0','','','','Bis: '||Q022DT00_BIS)
  END DIRECCION,
  Q022DT00_DIR_PRO_EXTERNO,
  Q022DT00_DIR_NUEVA,
  Q022BS00.FCA02T00_CODMUNICIP, Q022BS00.FCA02T00_CODPROVINC 
FROM Q022DS00, NORA_T17_LOCALIDAD,NORA_T17_CALLE , Q022BS00
where Q022DS00.Q022DT00_ENTI=NORA_T17_LOCALIDAD.ID(+)   and
Q022DS00.Q022DT00_CALLE=NORA_T17_CALLE.ID(+) AND 
 Q022DS00.Q022BT00_ID_GRUPO=Q022BS00.Q022BT00_ID_GRUPO
/


CREATE OR REPLACE VIEW NORA_T17_MUNICIPIO_2
(ID, DS_O, AUTONOMIA_ID, ID_MUNI, DS_O_MUNI, 
 ID_MULTIPLE)
AS 
select NORA_T17_PROVINCIA.ID, NORA_T17_PROVINCIA.DS_O, NORA_T17_PROVINCIA.AUTONOMIA_ID,
NORA_T17_MUNICIPIO.ID, NORA_T17_MUNICIPIO.DS_O as DS_O_1, NORA_T17_MUNICIPIO.ID||'#'||NORA_T17_MUNICIPIO.DS_O AS COD_DESC
from 
NORA_T17_PROVINCIA, NORA_T17_MUNICIPIO
where 
NORA_T17_PROVINCIA.id=NORA_T17_MUNICIPIO.PROVINCIA_ID AND AUTONOMIA_ID=16 
UNION
select '48' as ID , 'Bizkaia' as DS_O ,'16' as AUTONOMIA_ID, 't' as ID_1, ' TODOS' as DS_O_1, 't#TODOS' as cod_desc  from dual
union
select '20' as ID , 'Gipuzkoa' as DS_O ,'16' as AUTONOMIA_ID, 't' as ID_1, ' TODOS' as DS_O_1, 't#TODOS' as cod_desc  from dual
union
select '01' as ID , 'Araba' as DS_O ,'16' as AUTONOMIA_ID, 't' as ID_1, ' TODOS' as DS_O_1, 't#TODOS' as cod_desc  from dual
order by DS_O_1
/


CREATE OR REPLACE SYNONYM NORA_T17_PROV_MUNI FOR NORA_T17_MUNICIPIO_2
/


CREATE OR REPLACE VIEW Q021MV01
(Q021MT00_ID, Q022FT00_IDUSUARIO, FCA02T00_CODPROVINC, ID, DS_O)
AS 
select q021mt00.Q021MT00_ID, q021mt00.Q022FT00_IDUSUARIO,
q021mt00.FCA02T00_CODPROVINC, nora_t17_municipio.ID,  nora_t17_municipio.DS_O
from q021mt00, nora_t17_municipio
where 
q021mt00.FCA02T00_CODPROVINC=nora_t17_municipio.PROVINCIA_ID and
q021mt00.FCA02T00_CODMUNICIP=nora_t17_municipio.ID
/


CREATE OR REPLACE SYNONYM Q021MS01V01 FOR Q021MV01
/





