🏠 Menú principal

← Volver a vistas materializadas

---

SIMACMOBILE.VM_GARANTIA_CLIENTES_ENERGITECA

Base de datos origen: ORCLSMM

Tipo de objeto: MATERIALIZED VIEW

SQL

  CREATE MATERIALIZED VIEW "SIMACMOBILE"."VM_GARANTIA_CLIENTES_ENERGITECA" ("NIT", "NOMBRE", "DEPARTAMENTO", "CIUDAD", "CORREO", "TELEFONO_OFICINA", "CODIGO", "NOMBRE_SUCURSAL", "CODIGO_ORGANIZACION", "CODIGO_CEDI")
  SEGMENT CREATION IMMEDIATE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  BUILD DEFERRED
  USING INDEX 
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
  AS WITH INFO_CLIENTE AS (
    SELECT
        ID_CLERP,
        SITE,
        NIT,
        SE.ID_SUERP,
        PARTY_SITE_NAME,
        ROW_NUMBER() OVER (
            PARTITION BY NIT 
            ORDER BY 
            CLIENTE_ERP.ESTADO_REGISTRO ASC,
                CASE 
                    WHEN SUBSTR(SITE, -3) = '000' THEN 1 
                    WHEN SUBSTR(SITE, -3) = '001' THEN 2 
                    ELSE 3 
                END, 
                SITE ASC
        ) AS RN
    FROM
        SIMACMOBILE.CLIENTE_ERP
    INNER JOIN 
        SIMACMOBILE.SUCURSAL_ERP SE ON SE.CODIGO_REGIONAL = SUBSTR(SITE, 1, INSTR(SITE, '-', 1, 1) - 1)
    AND 
        SE.CODIGO IN (
            SELECT
                CODIGO
            FROM
                SIMACMOBILE.VISTA_SUCURSALES_ERP
            WHERE
                TIPO_SUCURSAL = 'Cedi Principal'
        )
)
SELECT 
    CE.NIT,
    CE.PARTY_SITE_NAME     AS NOMBRE,
    DEPE.DEPARTAMENTO,
    CIUE.CIUDAD,
    CE.CORREO,
    CE.TELEFONO_OFICINA,
    SE.CODIGO,
    SE.NOMBRE              AS NOMBRE_SUCURSAL,
    SE.CODIGO_ORGANIZACION,
    SE.CODIGO              AS CODIGO_CEDI
FROM 
    INFO_CLIENTE IC
    JOIN SIMACMOBILE.CLIENTE_ERP CE
    ON IC.ID_CLERP = CE.ID_CLERP
    JOIN SIMACMOBILE.VISTA_SUCURSALES_ERP SE
    ON SE.ID_SUERP = IC.ID_SUERP
    JOIN SIMACMOBILE.CIUDAD_ERP CIUE
    ON CIUE.ID_CIUERP = CE.ID_CIUERP
    JOIN SIMACMOBILE.DEPARTAMENTO_ERP DEPE
    ON DEPE.ID_DEPERP = CE.ID_DEPERP
WHERE 
    RN = 1 
    AND CE.ESTADO_REGISTRO = 'A'
    AND CE.SITE NOT LIKE '%-9__'
    AND CE.NIT NOT LIKE '%INACTIVO%';

---

← Volver al esquema SIMACMOBILE

🏠 Volver al menú principal