🏠 Menú principal

← Volver a vistas simples

---

SIMACMOBILE.VISTA_RECOGIDAS_GANEMAS

Base de datos origen: ORCLSMM

Tipo de objeto: VIEW

SQL

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "SIMACMOBILE"."VISTA_RECOGIDAS_GANEMAS" ("CLIENTE_NIT", "BAPUS_RECOGIDAS", "ANO", "MES", "ID_PERIODO_CONTABLE", "SITE_CLIENTE", "ID_CLIENTE", "ID_VENDEDOR", "ID_SUCURSAL") 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_BAPUS AS (
    SELECT
        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,
        CE.SITE AS SITE_CLIENTE,
        SE.ID_SUERP AS ID_SUCURSAL
    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 
        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) AND CE.ID_SUERP = SE.ID_SUERP
    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
)

SELECT 
    SB.CLIENTE_NIT,
    SB.BAPUS_RECOGIDAS,
    SB.ANO,
    SB.MES,
    SB.ID_PERIODO_CONTABLE,
    SB.SITE_CLIENTE,
    CE.ID_CLERP AS ID_CLIENTE,
    MIN(VERP.ID_VERP) AS ID_VENDEDOR,
    SB.ID_SUCURSAL
FROM 
    SUM_BAPUS SB
LEFT JOIN 
    SIMACMOBILE.CLIENTE_ERP CE ON SB.SITE_CLIENTE = CE.SITE
LEFT JOIN 
    SIMACMOBILE.venta_historico_erp VHR ON SB.CLIENTE_NIT = VHR.CLIENTE_NIT AND SB.ANO = EXTRACT(YEAR FROM VHR.FECHA_VENTA) AND SB.MES = EXTRACT(MONTH FROM VHR.FECHA_VENTA)
LEFT JOIN 
    SIMACMOBILE.VENDEDOR_ERP VERP ON VHR.CODIGO_VENDEDOR = VERP.CODIGO
WHERE 
    VERP.ESTADO_REGISTRO = 'A'
GROUP BY 
    SB.CLIENTE_NIT,
    SB.BAPUS_RECOGIDAS,
    SB.ANO,
    SB.MES,
    SB.ID_PERIODO_CONTABLE,
    SB.SITE_CLIENTE,
    CE.ID_CLERP,
    SB.ID_SUCURSAL;

---

← Volver al esquema SIMACMOBILE

🏠 Volver al menú principal