SIMACMOBILE.VISTA_VENTA_HISTORICO_ERP

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

  CREATE MATERIALIZED VIEW "SIMACMOBILE"."VISTA_VENTA_HISTORICO_ERP" ("ID_VEHIERP", "CODIGO", "CLIENTE_NIT", "CLIENTE_RAZON_SOCIAL", "VENDEDOR", "CATEGORIA_REFERENCIA", "SUCURSAL", "SUCURSAL_VENDEDOR", "MARCA", "CANTIDAD", "VALOR_UNITARIO", "FECHA_VENTA", "TIPO_TRANS", "SUBLINEA_COMERCIAL", "EQUIVALENCIA_GALONES", "DESC_ITEM", "TRANSACCION_ORIGINAL", "NUMERO_FACTURA", "ID_SUERP", "LINEA", "DOCUMENTO_ORACLE", "CLIENTE_SITE", "ORDEN_PEDIDO", "SEGMENTO1", "SEGMENTO2", "SEGMENTO3", "SEGMENTO4", "SEGMENTO5", "SEGMENTO6", "SEGMENTO7", "SEGMENTO8", "SEGMENTO9", "SEGMENTO10")
  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 SELECT 
VEHI.ID_VEHIERP,
REER.CODIGO,
VEHI.CLIENTE_NIT,
VEHI.CLIENTE_RAZON_SOCIAL,
VEHI.CODIGO_VENDEDOR AS VENDEDOR,
ITCA.CATEGORIA_REFERENCIA,
VEHI.CODIGO_SUCURSAL_ORACLE AS SUCURSAL,
CASE WHEN SUBSTR(VEH.CODIGO_VENDEDOR,1,3) IS NULL THEN SUBSTR(VEHI.CODIGO_VENDEDOR,1,3) else SUBSTR(VEH.CODIGO_VENDEDOR,1,3) end AS SUCURSAL_VENDEDOR,
REER.MARCA,
VEHI.CANTIDAD_ITEM AS CANTIDAD,
VEHI.MONTO_UNITARIO_ITEM AS VALOR_UNITARIO,
VEHI.FECHA_VENTA,
VEHI.TIPO_TRANS,
REER.SUBLINEA_COMERCIAL,
REER.EQUIVALENCIA_GALONES,
CASE WHEN VEHI.TIPO_TRANS LIKE '%FV%' OR VEHI.TIPO_TRANS LIKE '%ND%' THEN
            CASE WHEN (VEHI.CANTIDAD_ITEM * VEHI.MONTO_UNITARIO_ITEM)<0 THEN
                 CASE WHEN   VEHI.DESC_ITEM LIKE '%QP_%' THEN VEHI.DESC_ITEM ELSE 'QP_'||VEHI.DESC_ITEM  END
            ELSE
             VEHI.DESC_ITEM
            END 
        WHEN VEHI.TIPO_TRANS LIKE '%NC%' THEN
            CASE WHEN (VEHI.CANTIDAD_ITEM * VEHI.MONTO_UNITARIO_ITEM)>0 THEN
                 CASE WHEN   VEHI.DESC_ITEM LIKE '%QP_%' THEN VEHI.DESC_ITEM ELSE 'QP_'||VEHI.DESC_ITEM  END
            ELSE
             VEHI.DESC_ITEM
            END
        ELSE
        VEHI.DESC_ITEM
        END AS DESC_ITEM,
VEHI.TRANSACCION_ORIGINAL,
VEHI.NUMERO_FACTURA,
VEHI.ID_SUERP,
REER.LINEA,
VEHI.DOCUMENTO_ORACLE,
VEHI.CLIENTE_SITE,
VEHI.ORDEN_PEDIDO,
VEHI.SEGMENTO1,
VEHI.SEGMENTO2,
VEHI.SEGMENTO3,
VEHI.SEGMENTO4,
VEHI.SEGMENTO5,
VEHI.SEGMENTO6,
VEHI.SEGMENTO7,
VEHI.SEGMENTO8,
VEHI.SEGMENTO9,
VEHI.SEGMENTO10
FROM simacmobile.VENTA_HISTORICO_ERP VEHI
JOIN simacmobile.REFERENCIA_ERP REER ON REER.ID_REERP = VEHI.ID_REERP 
JOIN simacmobile.ITEM_CATEGORIA_ERP ITCA ON ITCA.ID_ICERP = REER.ID_ICERP 
left join (select documento_oracle,numero_factura,sucursal, max(CODIGO_VENDEDOR) CODIGO_VENDEDOR from  simacmobile.VENTA_HISTORICO_ERP
where codigo_sucursal_oracle in ('100','101','200','201','202','300','400','500','501','502','503','600') and TIPO_TRANS LIKE '%FV%'
group by documento_oracle,numero_factura,sucursal) VEH on VEHI.TRANSACCION_ORIGINAL = VEH.documento_oracle AND SUBSTR(VEHI.CODIGO_SUCURSAL_ORACLE,1,1) = SUBSTR(veh.CODIGO_VENDEDOR,1,1)
WHERE   VEHI.CODIGO_ITEM not in (SELECT codigo FROM SIMACMOBILE.REFERENCIA_ERP re JOIN SIMACMOBILE.REFERENCIA_EXCLUIDA_IND rei ON rei.ID_REERP = re.ID_REERP WHERE rei.CEDI = 1 AND rei.ESTADO_REGISTRO = 'A');
← Volver al esquema SIMACMOBILE
🏠 Volver al menú principal