🏠 Menú principal

← Volver a vistas materializadas

---

SIMACMOBILE.VISTA_REPORTE_METAS_GANEMAS

Base de datos origen: ORCLSMM

Tipo de objeto: MATERIALIZED VIEW

SQL

  CREATE MATERIALIZED VIEW "SIMACMOBILE"."VISTA_REPORTE_METAS_GANEMAS" ("ANO", "MES", "ID_SUCURSAL", "CLIENTE_NIT", "ID_VENDEDOR", "CANTIDAD_ITEM", "MONTO_UNITARIO_ITEM", "CLIENTE_SITE", "CLIENTE_NOMBRE", "ID_CLIENTE", "CODIGO_VENDEDOR")
  SEGMENT CREATION IMMEDIATE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING  NO INMEMORY 
  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, ID_SUERP, PARTY_SITE_NAME
    FROM (
        SELECT id_clerp, site, nit, SE.ID_SUERP, PARTY_SITE_NAME,
               ROW_NUMBER() OVER (PARTITION BY SUBSTR(site, 1, INSTR(site, '-', 1, 1) - 1), NIT ORDER BY 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 ('100', '101', '200', '201', '202', '300', '400', '500', '501', '502', '600')
    ) ranked
    WHERE rn = 1
)

SELECT 
    EXTRACT(YEAR FROM VHR.FECHA_VENTA) AS ANO,
    EXTRACT(MONTH FROM VHR.FECHA_VENTA) AS MES,
    SE.ID_SUERP AS ID_SUCURSAL,
    VHR.CLIENTE_NIT,
    NVL(VE.ID_VERP, 0) AS ID_VENDEDOR,
    NVL(SUM(
        CASE
            WHEN VHR.DESC_ITEM NOT LIKE 'QP_%' AND VHR.MONTO_UNITARIO_ITEM > 0 THEN
                VHR.CANTIDAD_ITEM
            WHEN VHR.DESC_ITEM NOT LIKE 'QP_%' AND VHR.MONTO_UNITARIO_ITEM < 0 THEN
                -1 * (VHR.CANTIDAD_ITEM)
            ELSE
                0
        END
    ), 0) AS CANTIDAD_ITEM,
    NVL(SUM(VHR.MONTO_UNITARIO_ITEM * VHR.CANTIDAD_ITEM), 0) AS MONTO_UNITARIO_ITEM,
    CE.SITE AS CLIENTE_SITE,
    CE.PARTY_SITE_NAME AS CLIENTE_NOMBRE,
    CE.ID_CLERP AS ID_CLIENTE,
    VHR.CODIGO_VENDEDOR AS CODIGO_VENDEDOR
FROM SIMACMOBILE.VENTA_HISTORICO_ERP VHR 
INNER JOIN SIMACMOBILE.REFERENCIA_ERP RE
    ON VHR.CODIGO_ITEM = RE.CODIGO
INNER JOIN SIMACMOBILE.ITEM_CATEGORIA_ERP ICE
    ON ICE.ID_ICERP = RE.ID_ICERP
INNER JOIN SIMACMOBILE.SUCURSAL_ERP SE
    ON SE.CODIGO = SUBSTR(VHR.NUMERO_FACTURA, 1, 3)
LEFT JOIN SIMACMOBILE.VENDEDOR_ERP VE
    ON VE.CODIGO = VHR.CODIGO_VENDEDOR
   AND VE.ID_SUERP NOT IN (287)
INNER JOIN INFO_CLIENTE CE
    ON VHR.CLIENTE_NIT = CE.NIT
    AND (
        CE.ID_SUERP = SE.ID_SUERP OR
        (CE.ID_SUERP = 17 AND SE.ID_SUERP IN (17, 289)) OR
        (CE.ID_SUERP = 10 AND SE.ID_SUERP IN (10, 290, 291)) OR
        (CE.ID_SUERP = 295 AND SE.ID_SUERP = 295) OR
        (CE.ID_SUERP = 106 AND SE.ID_SUERP = 106) OR
        (CE.ID_SUERP = 13 AND SE.ID_SUERP IN (13, 292, 293)) OR
        (CE.ID_SUERP = 140 AND SE.ID_SUERP = 140)
    )
WHERE  
    ICE.CODIGO_CATEGORIA LIKE '%BAT_AUTO%'
    AND RE.SUBLINEA_COMERCIAL LIKE 'BATERIA%'
    AND NOT REGEXP_LIKE(RE.CODIGO, '_.*_')
    AND (VHR.TIPO_TRANS LIKE 'FV_%' OR VHR.TIPO_TRANS LIKE 'NC_%')
GROUP BY 
    EXTRACT(YEAR FROM VHR.FECHA_VENTA),
    EXTRACT(MONTH FROM VHR.FECHA_VENTA),
    SE.ID_SUERP,
    VHR.CLIENTE_NIT,
    NVL(VE.ID_VERP, 0),
    CE.SITE,
    CE.PARTY_SITE_NAME,
    CE.ID_CLERP,
    VHR.CODIGO_VENDEDOR;

---

← Volver al esquema SIMACMOBILE

🏠 Volver al menú principal