Diccionario de Datos
Diccionario de datos de los modelos de COÉXITO
| 🏠 Menú principal | ← Volver a vistas materializadas |
SIMACMOBILE.VM_GVTA_COMERCIO
Base de datos origen: ORCLSMM
Tipo de objeto: MATERIALIZED VIEW
SQL
CREATE MATERIALIZED VIEW "SIMACMOBILE"."VM_GVTA_COMERCIO" ("ID_COMERCIO", "ID_GLOBAL_COMERCIO", "NOMBRE", "EMAIL", "TELEFONO", "UBICACION_DIRECCION", "UBICACION_COMUNA", "CIUDAD", "DEPARTAMENTO", "PAIS", "COORDENADA_LATITUD", "COORDENADA_LONGITUD", "INACTIVO", "COMERCIO_CLAVE", "FECHA_CREACION", "FECHA_PROXIMA_VISITA", "FECHA_SIGUIENTE_ENTREGA", "CANAL", "SUBCANAL", "PLAN_VISITA", "RUTA_VISITA", "ID_CENTRO_DISTRIBUCION", "CODIGO_POSTAL", "RUTA_DISTRIBUCION", "CENTRO_VENTAS", "VENDEDOR_RESPONSABLE", "LISTA_PRECIO_ASOCIADA", "FECHA_MODIFICACION", "CATEGORIA_CLIENTE", "VENDEDOR_RESPONSABLE_AR")
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
/* =========================
1) Lista(s) de precio asociada(s)
(se deja la lógica tal como está para no alterar casos borde)
========================= */
FNCLP_LISTA AS (
SELECT COALESCE(CLE.SITE, RP.NIT_SITE) AS ID_COMERCIO,
LISTAGG(GLP.CODIGO, ', ') WITHIN GROUP(ORDER BY GLP.CODIGO) AS LISTA_PRECIO_ASOCIADA
FROM (
SELECT GTLPC.ID_GVTATLP,
GTLPC.NIT_SITE,
GTLPC.INDICADOR_SITE,
GTLPC.PRIORIDAD,
CASE
WHEN GTLPC.NIT_SITE NOT LIKE '%-%' THEN GTLPC.NIT_SITE
ELSE REGEXP_SUBSTR(GTLPC.NIT_SITE, '[0-9]+', 1, 2)
END AS NIT_ESTANDARIZADO,
CASE
WHEN GTLPC.INDICADOR_SITE = 0 THEN
ROW_NUMBER() OVER (
PARTITION BY GTLPC.ID_GVTATLP,
CASE
WHEN GTLPC.NIT_SITE NOT LIKE '%-%' THEN GTLPC.NIT_SITE
ELSE REGEXP_SUBSTR(GTLPC.NIT_SITE, '[0-9]+', 1, 2)
END
ORDER BY GTLPC.INDICADOR_SITE ASC
)
ELSE 1
END AS RN
FROM SIMACMOBILE.GVTA_TIPO_LISTA_PRECIO_CLIENTE GTLPC
) RP
INNER JOIN SIMACMOBILE.GVTA_TIPO_LISTA_PRECIO GLP
ON GLP.ID_GVTATLP = RP.ID_GVTATLP
LEFT JOIN SIMACMOBILE.CLIENTE_ERP CLE
ON CLE.NIT = RP.NIT_SITE
WHERE ( (RP.INDICADOR_SITE = 0 AND RP.RN = 1) OR (RP.INDICADOR_SITE = 1) )
AND (CLE.ESTADO_REGISTRO = 'A' OR CLE.ESTADO_REGISTRO IS NULL)
AND (CLE.SITE NOT LIKE '%INACTIVO%' OR CLE.SITE IS NULL)
GROUP BY COALESCE(CLE.SITE, RP.NIT_SITE)
),
/* =========================
2) Rutero: calcular LISTAGG una vez
========================= */
RUTAS_BASE AS (
SELECT ID_CLERP,
LISTAGG(
DISTINCT NVL(TRIM(RUTA_VISITA), '1-5V,2-4V,2-5V'),
', '
) WITHIN GROUP(ORDER BY TRIM(RUTA_VISITA)) AS RUTA_VISITA
FROM SIMACMOBILE.GVTA_RUTERO
WHERE ESTADO_REGISTRO = 'A'
GROUP BY ID_CLERP
),
RUTAS_AGRUPADAS AS (
SELECT ID_CLERP,
RUTA_VISITA,
SIMACMOBILE.FN_OBTENER_DIAS_RUTERO(RUTA_VISITA) AS PLAN_VISITA
FROM RUTAS_BASE
),
/* =========================
3) Vendedor responsable:
- Si existe rutero activo => lista de N vendedores del rutero (coma)
- Si NO existe rutero => 1 vendedor fallback (ID_VERP mayor / más nuevo)
========================= */
RUTERO_VEND_LIST AS (
SELECT r.id_clerp,
LISTAGG(
DISTINCT TRIM(r.codigo_vendedor),
', '
) WITHIN GROUP (ORDER BY TRIM(r.codigo_vendedor)) AS vendedores_rutero
FROM SIMACMOBILE.GVTA_RUTERO r
WHERE r.estado_registro = 'A'
AND r.codigo_vendedor IS NOT NULL
GROUP BY r.id_clerp
),
VEND_FALLBACK AS (
SELECT nit,
codigo_vendedor
FROM (
SELECT SUBSTR(clv.site, 7, LENGTH(clv.site) - 10) AS nit,
verp.codigo AS codigo_vendedor,
ROW_NUMBER() OVER(
PARTITION BY SUBSTR(clv.site, 7, LENGTH(clv.site) - 10)
ORDER BY clv.id_cliverp DESC
) rn
FROM SIMACMOBILE.CLIENTE_VENDEDOR_ERP clv
JOIN SIMACMOBILE.VENDEDOR_ERP verp
ON verp.id_verp = clv.id_verp
AND verp.estado_registro = 'A'
WHERE clv.estado_registro = 'A'
)
WHERE rn = 1
),
/* =========================
4) Base de clientes (aplica filtros una sola vez)
========================= */
CLIENTES_BASE AS (
SELECT /*+ MATERIALIZE */
CLE.*,
VSU.CODIGO AS VSU_CODIGO,
VSU.CODIGO_REGIONAL AS VSU_CODIGO_REGIONAL
FROM SIMACMOBILE.CLIENTE_ERP CLE
JOIN SIMACMOBILE.VISTA_SUCURSALES_ERP VSU
ON VSU.ID_SUERP = CLE.ID_SUERP
WHERE VSU.TIPO_SUCURSAL IN ('Cedi Principal', 'Cedi Secundario')
AND CLE.ESTADO_REGISTRO = 'A'
AND EXISTS (
SELECT 1
FROM SIMACMOBILE.GVTA_TIPO_LISTA_PRECIO_CLIENTE GTLPC
WHERE GTLPC.ESTADO_REGISTRO = 'A'
AND GTLPC.NIT_SITE IN (CLE.SITE, CLE.NIT)
)
)
SELECT
CB.SITE AS ID_COMERCIO,
CB.NIT AS ID_GLOBAL_COMERCIO,
SIMACMOBILE.FN_TO_CAMEL_CASE(CB.PARTY_SITE_NAME) AS NOMBRE,
CASE
WHEN REGEXP_LIKE(CB.CORREO,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') THEN LOWER(CB.CORREO)
WHEN CB.CORREO IS NULL THEN 'correo@correo.com'
ELSE NULL
END AS EMAIL,
CASE
WHEN REGEXP_LIKE(CB.NUMERO_TELEFONO, '^3[0-9]{9}$') THEN CB.NUMERO_TELEFONO
ELSE NULL
END AS TELEFONO,
CB.DIRECCION || ' - ' || SIMACMOBILE.FN_TO_CAMEL_CASE(CIUE.CIUDAD) AS UBICACION_DIRECCION,
CB.VSU_CODIGO AS UBICACION_COMUNA,
SIMACMOBILE.FN_TO_CAMEL_CASE(CIUE.CIUDAD) AS CIUDAD,
SIMACMOBILE.FN_TO_CAMEL_CASE(DEP.DEPARTAMENTO) AS DEPARTAMENTO,
PAE.PAIS AS PAIS,
CB.LATITUD AS COORDENADA_LATITUD,
CB.LONGITUD AS COORDENADA_LONGITUD,
0 AS INACTIVO,
0 AS COMERCIO_CLAVE,
CB.FECHA_CREACION,
CAST(NULL AS VARCHAR2(50)) AS FECHA_PROXIMA_VISITA,
CAST(NULL AS VARCHAR2(50)) AS FECHA_SIGUIENTE_ENTREGA,
TPC.TIPO AS CANAL,
CLC.CLASIFICACION AS SUBCANAL,
NVL(RUTA.PLAN_VISITA, 'monday') AS PLAN_VISITA,
NVL(RUTA.RUTA_VISITA, '1-1V') AS RUTA_VISITA,
CB.VSU_CODIGO_REGIONAL || '00' AS ID_CENTRO_DISTRIBUCION,
CIUE.CODIGO_DANE AS CODIGO_POSTAL,
CB.RUTA_DISTRIBUCION,
CB.VSU_CODIGO AS CENTRO_VENTAS,
/* prioridad: rutero => fallback */
COALESCE(RV.vendedores_rutero, VF.codigo_vendedor) AS VENDEDOR_RESPONSABLE,
FNCLP.LISTA_PRECIO_ASOCIADA,
CB.FECHA_MODIFICACION,
'Sin categoria' AS CATEGORIA_CLIENTE,
VEAR.CODIGO AS VENDEDOR_RESPONSABLE_AR
FROM CLIENTES_BASE CB
LEFT JOIN SIMACMOBILE.CIUDAD_ERP CIUE
ON CIUE.ID_CIUERP = CB.ID_CIUERP
JOIN SIMACMOBILE.DEPARTAMENTO_ERP DEP
ON DEP.ID_DEPERP = CB.ID_DEPERP
JOIN SIMACMOBILE.PAIS_ERP PAE
ON PAE.ID_PAISERP = DEP.ID_PAISERP
LEFT JOIN SIMACMOBILE.TIPO_CLIENTE_ERP TPC
ON TPC.ID_TICLERP = CB.ID_TICLERP
LEFT JOIN SIMACMOBILE.CLASIFICACION_CANAL_ERP CLC
ON CLC.ID_CCERP = CB.ID_CCERP
LEFT JOIN RUTAS_AGRUPADAS RUTA
ON RUTA.ID_CLERP = CB.ID_CLERP
LEFT JOIN FNCLP_LISTA FNCLP
ON FNCLP.ID_COMERCIO = CB.SITE
LEFT JOIN RUTERO_VEND_LIST RV
ON RV.ID_CLERP = CB.ID_CLERP
LEFT JOIN VEND_FALLBACK VF
ON VF.NIT = CB.NIT
LEFT JOIN SIMACMOBILE.VENDEDOR_ERP VEAR
ON VEAR.ID_VERP = CB.ID_VERP;