🏠 Menú principal

← Volver a vistas materializadas

---

SIMACMOBILE.VM_GVTA_CLIENTE_VENDEDOR

Base de datos origen: ORCLSMM

Tipo de objeto: MATERIALIZED VIEW

SQL

  CREATE MATERIALIZED VIEW "SIMACMOBILE"."VM_GVTA_CLIENTE_VENDEDOR" ("ID_VENDEDOR", "ID_COMERCIO", "FECHA_CREACION", "FECHA_MODIFICACION")
  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
/* =========================================================
   Segmentación inline (equivalente funcional a VM_GVTA_COMERCIO)
   - Sin depender de la MV
   ========================================================= */
CLIENTES_SEGMENTADOS AS (
  SELECT /*+ MATERIALIZE */
         cle.id_clerp,
         cle.site,
         cle.nit
    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
  id_vendedor,
  id_comercio,
  fecha_creacion,
  fecha_modificacion
FROM (
  SELECT
      ve.codigo AS id_vendedor,
      cle.site  AS id_comercio,
      clv.fecha_creacion,
      clv.fecha_modificacion,
      ROW_NUMBER() OVER (
        PARTITION BY ve.codigo, cle.site
        ORDER BY clv.fecha_modificacion DESC NULLS LAST,
                 clv.id_cliverp       DESC
      ) AS rn
    FROM CLIENTES_SEGMENTADOS cle
    JOIN simacmobile.cliente_vendedor_erp clv
      ON SUBSTR(clv.site, 7, LENGTH(clv.site) - 10) = cle.nit
    JOIN simacmobile.vendedor_erp ve
      ON ve.id_verp = clv.id_verp
   WHERE clv.estado_registro = 'A'
     AND ve.estado_registro  = 'A'
     AND SUBSTR(TRIM(ve.codigo), 4, 1) = 'C'
)
WHERE rn = 1;

---

← Volver al esquema SIMACMOBILE

🏠 Volver al menú principal