SIMACMOBILE.VISTA_BAPUS_RECOGIDAS_GANEMAS

Base de datos origen: ORCLSMM
Tipo de objeto: MATERIALIZED VIEW
SQL

  CREATE MATERIALIZED VIEW "SIMACMOBILE"."VISTA_BAPUS_RECOGIDAS_GANEMAS" ("CLIENTE_NIT", "BAPUS_RECOGIDAS", "ANO", "MES", "ID_PERIODO_CONTABLE", "SITE_CLIENTE", "ID_CLIENTE", "ID_VENDEDOR", "ID_SUCURSAL")
  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,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
)

, SUM_BAPUS1 AS (
SELECT 
    X.CLIENTE_NIT,
    X.BAPUS_RECOGIDAS,
    X.ANO,
    X.MES,
    X.ID_PERIODO_CONTABLE,
    X.SITE_CLIENTE,
    CE.ID_CLERP AS ID_CLIENTE,
    VERP.ID_VERP AS ID_VENDEDOR,
    X.ID_SUCURSAL,
    X.ID_SUERP
FROM (
    SELECT DISTINCT
        VHR.CLIENTE_NIT,
        FLOOR(SUM(VHR.CANTIDAD_ITEM * RE.EQUIVALENCIA_GANEMAS)) AS BAPUS_RECOGIDAS, 
        EXTRACT(YEAR FROM VHR.FECHA_VENTA) AS ANO,
        EXTRACT(MONTH FROM VHR.FECHA_VENTA) AS MES,
        PCE.ID_PECOERP AS ID_PERIODO_CONTABLE,
         VHR.CODIGO_VENDEDOR  AS CODIGO_VENDEDOR,
        CE.SITE AS SITE_CLIENTE,
        SE.ID_SUERP AS ID_SUCURSAL,
        CE.ID_SUERP
    FROM 
        SIMACMOBILE.venta_historico_erp VHR
    INNER JOIN 
        SIMACMOBILE.REFERENCIA_ERP RE ON VHR.CODIGO_ITEM = RE.CODIGO
    INNER JOIN 
        SIMACMOBILE.PERIODO_CONTABLE_ERP PCE ON PCE.ANO = EXTRACT(YEAR FROM VHR.FECHA_VENTA) AND PCE.MES = EXTRACT(MONTH FROM VHR.FECHA_VENTA)
    INNER JOIN 
        SIMACMOBILE.INFO_CLIENTE CE ON VHR.CLIENTE_NIT = CE.NIT
    INNER JOIN 
        SIMACMOBILE.SUCURSAL_ERP SE ON SE.CODIGO = SUBSTR(VHR.CODIGO_SUCURSAL_ORACLE, 1, 3)
    WHERE 
        VHR.TIPO_TRANS LIKE 'BP%'
        AND RE.SUBLINEA_COMERCIAL = 'BATERIAS_USADAS'
        AND RE.EQUIVALENCIA_GANEMAS IS NOT NULL
        --and VHR.CLIENTE_NIT = '900504269'
    GROUP BY 
        VHR.CLIENTE_NIT,
        EXTRACT(YEAR FROM VHR.FECHA_VENTA),
        EXTRACT(MONTH FROM VHR.FECHA_VENTA),
        PCE.ID_PECOERP,
        CE.SITE,
        SE.ID_SUERP, VHR.CODIGO_VENDEDOR,
        CE.ID_SUERP
) X
LEFT JOIN 
    SIMACMOBILE.CLIENTE_ERP CE ON X.SITE_CLIENTE = CE.SITE
LEFT JOIN 
    SIMACMOBILE.VENDEDOR_ERP VERP ON X.CODIGO_VENDEDOR = VERP.CODIGO AND VERP.ID_SUERP =  X.ID_SUCURSAL
where VERP.ESTADO_REGISTRO = 'A' AND X.ID_SUERP = X.ID_SUCURSAL
)
, SUM_BAPUS2 AS (
SELECT 
    X.CLIENTE_NIT,
    X.BAPUS_RECOGIDAS,
    X.ANO,
    X.MES,
    X.ID_PERIODO_CONTABLE,
    X.SITE_CLIENTE,
    CE.ID_CLERP AS ID_CLIENTE,
    VERP.ID_VERP AS ID_VENDEDOR,
    X.ID_SUCURSAL,
    X.ID_SUERP
FROM (
    SELECT DISTINCT
        VHR.CLIENTE_NIT,
        FLOOR(SUM(VHR.CANTIDAD_ITEM * RE.EQUIVALENCIA_GANEMAS)) AS BAPUS_RECOGIDAS, 
        EXTRACT(YEAR FROM VHR.FECHA_VENTA) AS ANO,
        EXTRACT(MONTH FROM VHR.FECHA_VENTA) AS MES,
        PCE.ID_PECOERP AS ID_PERIODO_CONTABLE,
         VHR.CODIGO_VENDEDOR  AS CODIGO_VENDEDOR,
        CE.SITE AS SITE_CLIENTE,
        SE.ID_SUERP AS ID_SUCURSAL,
        CE.ID_SUERP
    FROM 
        SIMACMOBILE.venta_historico_erp VHR
    INNER JOIN 
        SIMACMOBILE.REFERENCIA_ERP RE ON VHR.CODIGO_ITEM = RE.CODIGO
    INNER JOIN 
        SIMACMOBILE.PERIODO_CONTABLE_ERP PCE ON PCE.ANO = EXTRACT(YEAR FROM VHR.FECHA_VENTA) AND PCE.MES = EXTRACT(MONTH FROM VHR.FECHA_VENTA)
    INNER JOIN 
        SIMACMOBILE.INFO_CLIENTE CE ON VHR.CLIENTE_NIT = CE.NIT
    INNER JOIN 
        SIMACMOBILE.SUCURSAL_ERP SE ON SE.CODIGO = SUBSTR(VHR.CODIGO_SUCURSAL_ORACLE, 1, 3)
    WHERE 
        VHR.TIPO_TRANS LIKE 'BP%'
        AND RE.SUBLINEA_COMERCIAL = 'BATERIAS_USADAS'
        AND RE.EQUIVALENCIA_GANEMAS IS NOT NULL
        --and VHR.CLIENTE_NIT = '900504269'
    GROUP BY 
        VHR.CLIENTE_NIT,
        EXTRACT(YEAR FROM VHR.FECHA_VENTA),
        EXTRACT(MONTH FROM VHR.FECHA_VENTA),
        PCE.ID_PECOERP,
        CE.SITE,
        SE.ID_SUERP, VHR.CODIGO_VENDEDOR,
        CE.ID_SUERP
) X
LEFT JOIN 
    SIMACMOBILE.CLIENTE_ERP CE ON X.SITE_CLIENTE = CE.SITE
LEFT JOIN 
    SIMACMOBILE.VENDEDOR_ERP VERP ON X.CODIGO_VENDEDOR = VERP.CODIGO --AND VERP.ID_SUERP =  X.ID_SUCURSAL
where VERP.ESTADO_REGISTRO = 'A' AND X.ID_SUERP = X.ID_SUCURSAL
)

,

AGGREGATED_SUM_BAPUS1 AS (
    SELECT 
        CLIENTE_NIT,
        SUM(BAPUS_RECOGIDAS) AS BAPUS_RECOGIDAS,
        ANO,
        MES,
        ID_PERIODO_CONTABLE,
        SITE_CLIENTE,
        ID_CLIENTE,
        MIN(ID_VENDEDOR) AS ID_VENDEDOR,
        ID_SUCURSAL
    FROM SUM_BAPUS1
    GROUP BY 
        CLIENTE_NIT,
        ANO,
        MES,
        ID_PERIODO_CONTABLE,
        SITE_CLIENTE,
        ID_CLIENTE,
        ID_SUCURSAL
),

AGGREGATED_SUM_BAPUS2 AS (
    SELECT 
        CLIENTE_NIT,
        SUM(BAPUS_RECOGIDAS) AS BAPUS_RECOGIDAS,
        ANO,
        MES,
        ID_PERIODO_CONTABLE,
        SITE_CLIENTE,
        ID_CLIENTE,
        MIN(ID_VENDEDOR) AS ID_VENDEDOR,
        ID_SUCURSAL
    FROM SUM_BAPUS2
    GROUP BY 
        CLIENTE_NIT,
        ANO,
        MES,
        ID_PERIODO_CONTABLE,
        SITE_CLIENTE,
        ID_CLIENTE,
        ID_SUCURSAL
)

SELECT * FROM AGGREGATED_SUM_BAPUS1

UNION

SELECT * FROM AGGREGATED_SUM_BAPUS2;
← Volver al esquema SIMACMOBILE
🏠 Volver al menú principal