AGM (1) Territorio y personas

1. Municipios (MUNICIPIOS.xls)

/*==========================================================
 * 1. MUNICIPIOS
 *==========================================================*/
SELECT DISTINCT 
  TO_CHAR(MUNE.COD_PROV,'00')                AS COD_PROVINCIA,
  TO_CHAR(MUNE.ID_MUNE_INE,'00000')          AS COD_MUNICIPIO,
  MUNE.NOMBRE_MUNE_INE                       AS NOMBRE,
  TO_CHAR(DIRE.DISTRITO_POSTAL_DIRE,'00000') AS CODIGO_POSTAL
FROM      OPS$GTTORA.MUNE_MUNICIPIOS_ESPANA MUNE
LEFT JOIN OPS$GTTORA.DIRE_DIRECCIONES DIRE ON DIRE.COD_CAT_MUNE =  MUNE.COD_CAT 
          AND NOT EXISTS (SELECT DIRE1.* FROM OPS$GTTORA.DIRE_DIRECCIONES DIRE1 WHERE DIRE1.COD_CAT_MUNE=DIRE.COD_CAT_MUNE AND DIRE1.DISTRITO_POSTAL_DIRE<>DIRE.DISTRITO_POSTAL_DIRE) 
ORDER BY TO_CHAR(MUNE.ID_MUNE_INE,'00000');

2. Provincias (PROVINCIAS.xls) 

/*==========================================================
 * 2. PROVINCIAS
 *==========================================================*/
SELECT DISTINCT 
  TO_CHAR(PROV.COD_PROV,'00') AS COD_PROVINCIA,
  PROV.NOMBRE_PROV AS NOMBRE
FROM OPS$GTTORA.PROV_PROVINCIAS PROV; 

3. Terceros

/*==========================================================
 * 3. TERCEROS
 *==========================================================*/
SELECT DISTINCT
  PERS.ID_PERS                   AS ID_SUJETO,
  PERS.NOMBRE_DESGLOSADO_PERS    AS NOMBRE,
  ''                             AS AP2_PART,
  PERS.APELLIDO2_PERS            AS APELLIDO2,
  ''                             AS AP1_PART,
  PERS.APELLIDO1_PERS            AS APELLIDO1,
  PERS.NOMBRE_PERG               AS RAZONSOCIAL,
  PERS.NIF_PERG                  AS NIF,
  PERS.COD_TPER                  AS FIS_JUR,
  ''                             AS DF_MEMO,
  DIRE.NUM_DIRE                  AS DF_NUMERO,
  DIRE.BLOQUE_DIRE               AS DF_PORTAL, 
  DIRE.ESCALERA_DIRE             AS DF_ESCALERA, 
  DIRE.PLANTA_DIRE               AS DF_PISO, 
  DIRE.PUERTA_DIRE               AS DF_PUERTA, 
  TO_CHAR(DIRE.DISTRITO_POSTAL_DIRE,'00000') AS DF_CODIGO_POSTAL, 
  DIRE.ID_CALL                   AS ID_VIA, 
  TO_CHAR(DIRE.COD_CAT_MUNE,'00000') AS COD_MUNICIPIO, 
  TO_CHAR(DIRE.COD_PROV,'00')    AS COD_PROVINCIA, 
  PERS.TELEFONO_PERS             AS TELEFONO, 
  PERS.TELEFONO2_PERS            AS MOVIL, 
  PERS.FAX_PERS                  AS FAX, 
  PERS.EMAIL_PERS                AS E_MAIL, 
  PERS.NOMBRE_PERG               AS TX_NOMBRE_COMPLETO, 
  DIRE.CALLE_DIRE                AS TX_DF_COMPLETA, 
  DIRE.POBLACION_DIRE            AS TX_DF_MUNICIPIO, 
  DIRE.PROVINCIA_DIRE            AS TX_DF_PROVINCIA,
  CASE 
    WHEN PPPP.ID_EPER IS NULL THEN 'N'
    ELSE                           'S'
  END                            AS PAGO_FRACCIONADO,
  CASE 
    WHEN PPPP.ID_EPER IS NULL THEN NULL
    ELSE                           PPPP.FECHA_PPPP
  END                            AS F_PAGO_FRACCIONADO,
  CASE 
    WHEN NVL(PERS.CONC_ACRE_PERS,0) >0  THEN 'S'
    ELSE                         'N'
  END                            AS ESTA_CONCURSO_ACREEDORES,
  CASE 
    WHEN NVL(PERS.FALLECIDO_PERS ,0) >0 THEN 'S'
    ELSE                         'N'
  END                            AS ESTA_FALLECIDO,
  'N????'                        AS ES_RGAP,
  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,
  CASE
  	  WHEN (NVL(PERS.CONSENT_ELECTR_PERS,0)>0 AND LENGTH(TRIM(NVL(PERS.EMAIL_PERS,'')))>0) OR NVL(PERS.OBLIGADO_PERS,1) >0 THEN 'S'
  	  ELSE                      'N'
  END                           AS NOTIFICACION_ELECTRONICA,
  NULL                          AS F_INI_ACREEDOR,
  NULL                          AS F_FIN_ACREEDOR
FROM      OPS$GTTORA.PERS_PERSONAS PERS
JOIN      OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_DIRE ON EPER_DIRE.ID_PERS = PERS.ID_PERS AND EPER_DIRE.TIPO_EPER ='DI'
JOIN      OPS$GTTORA.DIRE_DIRECCIONES DIRE ON DIRE.ID_EPER =EPER_DIRE.ID_EPER 
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_PPP ON EPER_PPP.ID_PERS = PERS.ID_PERS 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.EPER_ELEMENTOS_PERSONA EPER_DOMI ON EPER_DOMI.ID_PERS  = PERS.ID_PERS AND EPER_DOMI.TIPO_EPER ='DM'
LEFT JOIN OPS$GTTORA.DOMI_DOMICILIACIONES DOMI ON DOMI.ID_EPER=EPER_DOMI.ID_EPER AND DOMI.ESTADO_DOMI='A'
ORDER BY PERS.NOMBRE_PERG;
 

4. Terceros Pago Fraccionado (TERCEROSPAGOFRACCIONADO.xls)

/*==========================================================
 * 4. TERCEROS PAGO FRACCIONADO
 *==========================================================*/
SELECT DISTINCT
  PERS.ID_PERS                   AS ID_SUJETO,
  PERS.NOMBRE_DESGLOSADO_PERS    AS NOMBRE,
  ''                             AS AP2_PART,
  PERS.APELLIDO2_PERS            AS APELLIDO2,
  ''                             AS AP1_PART,
  PERS.APELLIDO1_PERS            AS APELLIDO1,
  PERS.NOMBRE_PERG               AS RAZONSOCIAL,
  PERS.NIF_PERG                  AS NIF,
  PERS.COD_TPER                  AS FIS_JUR,
  ''                             AS DF_MEMO,
  DIRE.NUM_DIRE                  AS DF_NUMERO,
  DIRE.BLOQUE_DIRE               AS DF_PORTAL, 
  DIRE.ESCALERA_DIRE             AS DF_ESCALERA, 
  DIRE.PLANTA_DIRE               AS DF_PISO, 
  DIRE.PUERTA_DIRE               AS DF_PUERTA, 
  TO_CHAR(DIRE.DISTRITO_POSTAL_DIRE,'00000') AS DF_CODIGO_POSTAL, 
  DIRE.ID_CALL                   AS ID_VIA, 
  TO_CHAR(DIRE.COD_CAT_MUNE,'00000') AS COD_MUNICIPIO, 
  TO_CHAR(DIRE.COD_PROV,'00')    AS COD_PROVINCIA, 
  PERS.TELEFONO_PERS             AS TELEFONO, 
  PERS.TELEFONO2_PERS            AS MOVIL, 
  PERS.FAX_PERS                  AS FAX, 
  PERS.EMAIL_PERS                AS E_MAIL, 
  PERS.NOMBRE_PERG               AS TX_NOMBRE_COMPLETO, 
  DIRE.CALLE_DIRE                AS TX_DF_COMPLETA, 
  DIRE.POBLACION_DIRE            AS TX_DF_MUNICIPIO, 
  DIRE.PROVINCIA_DIRE            AS TX_DF_PROVINCIA,
  CASE 
    WHEN PPPP.ID_EPER IS NULL THEN 'N'
    ELSE                           'S'
  END                            AS PAGO_FRACCIONADO,
  SUBSTR(REGE.IBAN_REGE, 5, 4)   AS DOM_ENTIDAD,
  SUBSTR(REGE.IBAN_REGE, 9, 4)   AS DOM_OFICINA,
  SUBSTR(REGE.IBAN_REGE,13, 2)   AS DOM_DC,
  SUBSTR(REGE.IBAN_REGE,15,10)   AS DOM_CUENTA,
  SUBSTR(REGE.IBAN_REGE, 1, 2)   AS DOM_COD_PAIS,
  SUBSTR(REGE.IBAN_REGE, 3, 2)   AS DOM_IBAN_DC
--
FROM      OPS$GTTORA.PERS_PERSONAS PERS
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_DIRE ON EPER_DIRE.ID_PERS = PERS.ID_PERS AND EPER_DIRE.TIPO_EPER ='DI'
LEFT JOIN OPS$GTTORA.DIRE_DIRECCIONES DIRE ON DIRE.ID_EPER =EPER_DIRE.ID_EPER 
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER_PPP ON EPER_PPP.ID_PERS = PERS.ID_PERS AND EPER_PPP.TIPO_EPER ='PP'
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 
ORDER BY PERS.NOMBRE_PERG;
 

5. Tipo de Vía (TIPO_VIA.xls)

/*==========================================================
 * 5. TIPOS DE VIA
 *==========================================================*/
SELECT
COD_SIGL    AS COD_Z_VIA,
DESCR_SIGL  AS Z_VIA
FROM OPS$GTTORA.SIGL_SIGLAS 
ORDER BY COD_SIGL; 

6. Vías (VIAS.xls)

/*==========================================================
 * 6. VIAS
 *==========================================================*/
SELECT
COD_PROV                       AS COD_PROVINCIA,
TO_CHAR(COD_CAT_MUNE,'00000')  AS COD_MUNICIPIO,
ID_CALL                        AS ID_VIA,
COD_SIGL                       AS COD_Z_VIA,
NOMBRE_CALL                    AS NOMBRE,
TO_CHAR(COD_CAT_MUNE,'00000')  AS COD_DGC,
NULL                           AS CP
FROM OPS$GTTORA.CALL_CALLEJERO CALL 
ORDER BY COD_PROV, COD_CAT_MUNE, NOMBRE  ;