Diccionario de Datos
Diccionario de datos de los modelos de COÉXITO
| 🏠 Menú principal | ← Volver a vistas materializadas |
SIMACMOBILE.VM_REPORTE_INFORME_DETALLE_FACTURA_USEM
Base de datos origen: ORCLSMM
Tipo de objeto: MATERIALIZED VIEW
SQL
CREATE MATERIALIZED VIEW "SIMACMOBILE"."VM_REPORTE_INFORME_DETALLE_FACTURA_USEM" ("LINEA", "SUBCATEGORIA", "COD_ENERGITECA", "ENERGITECA", "ZONA", "CODIGO_VENDEDOR", "CLIENTE_NIT", "NOMBRE_CLIENTE", "NUMERO_FACTURA", "DOCUMENTO_ORACLE", "FECHA_VENTA", "REFERENCIA", "DESC_ITEM", "CANTIDAD", "VALOR_UNITARIO", "TOTAL", "ANIO", "MES", "DIA")
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
SUCURSALES AS (
SELECT ID_SUERP,
CODIGO,
NOMBRE
FROM SIMACMOBILE.VISTA_SUCURSALES_ERP vse
WHERE vse.ESTADO_REGISTRO = 'A'
AND vse.TIPO_SUCURSAL = 'Energiteca'
),
FACTURAS_LINEAS AS (
SELECT
SUB.LINEA,
SUB.SUBCATEGORIA,
SUERP.CODIGO AS COD_ENERGITECA,
SUERP.NOMBRE AS ENERGITECA,
ZO.CODIGO AS ZONA,
VEHI.VENDEDOR AS CODIGO_VENDEDOR,
VEHI.CLIENTE_NIT,
CLIE.NOMBRE AS NOMBRE_CLIENTE,
VEHI.NUMERO_FACTURA,
VEHI.DOCUMENTO_ORACLE,
VEHI.FECHA_VENTA,
VEHI.CODIGO AS REFERENCIA,
VEHI.DESC_ITEM,
CASE WHEN SUB.LINEA = 'LUBRICANTES' THEN
CASE WHEN VEHI.VALOR_UNITARIO > 0
AND VEHI.DESC_ITEM NOT LIKE '%QP_%' THEN VEHI.CANTIDAD * VEHI.EQUIVALENCIA_GALONES
ELSE CASE WHEN VEHI.VALOR_UNITARIO < 0
AND VEHI.DESC_ITEM NOT LIKE '%QP_%' THEN (-1 * ABS(VEHI.CANTIDAD)) * VEHI.EQUIVALENCIA_GALONES ELSE 0 END END
ELSE
CASE WHEN VEHI.VALOR_UNITARIO > 0
AND VEHI.DESC_ITEM NOT LIKE '%QP_%' THEN VEHI.CANTIDAD
ELSE CASE WHEN VEHI.VALOR_UNITARIO < 0
AND VEHI.DESC_ITEM NOT LIKE '%QP_%' THEN (-1 * ABS(VEHI.CANTIDAD)) ELSE 0 END END
END AS CANTIDAD,
VEHI.VALOR_UNITARIO AS VALOR_UNITARIO,
VEHI.VALOR_UNITARIO*VEHI.CANTIDAD AS TOTAL,
EXTRACT(YEAR FROM VEHI.FECHA_VENTA) ANIO,
EXTRACT(MONTH FROM VEHI.FECHA_VENTA) MES,
EXTRACT(DAY FROM VEHI.FECHA_VENTA) DIA
FROM
SIMACMOBILE.VISTA_VENTA_HISTORICO_ERP VEHI
JOIN (
SELECT
HORE.SUBCATEGORIA,
HORE.EXCLUIR,
HORE.LINEA,
HORE.MARCA,
CARE.CODIGO_CATEGORIA
FROM
SIMACMOBILE.HOMOLOGACION_CATEGORIA_REFERENCIA_USEM_ERP HORE
JOIN SIMACMOBILE.CATEGORIAXREFERENCIA_ERP CARE ON CARE.ID_CAREERP = HORE.ID_CAREERP
) SUB ON SUB.CODIGO_CATEGORIA = VEHI.CATEGORIA_REFERENCIA AND SUB.MARCA = VEHI.MARCA AND (SUB.LINEA <> 'REPUESTOS' OR (SUB.LINEA = 'REPUESTOS' AND SUB.MARCA <> 'COEXITO'))
JOIN SUCURSALES SUERP ON SUERP.ID_SUERP = VEHI.ID_SUERP
JOIN SIMACMOBILE.SUCURSAL_ZONA_USEM_ERP SUZO ON SUZO.ID_SUERP = SUERP.ID_SUERP
JOIN SIMACMOBILE.ZONA_USEM_ERP ZO ON ZO.ID_ZOUSERP = SUZO.ID_ZOUSERP
LEFT JOIN (SELECT NIT, MAX(PARTY_SITE_NAME) NOMBRE FROM SIMACMOBILE.CLIENTE_ERP GROUP BY NIT) CLIE ON CLIE.NIT = VEHI.CLIENTE_NIT
WHERE
VEHI.CODIGO NOT IN (SELECT re.CODIGO FROM SIMACMOBILE.REFERENCIA_ERP re JOIN SIMACMOBILE.REFERENCIA_EXCLUIDA_IND rei ON rei.ID_REERP = re.ID_REERP
WHERE rei.USEM = 1 AND rei.ESTADO_REGISTRO = 'A') AND
VEHI.CLIENTE_NIT <> '890300225' AND
VEHI.FECHA_VENTA >= ADD_MONTHS(SYSDATE, -12)
AND (SUB.LINEA <> 'LLANTA' OR (SUB.LINEA = 'LLANTA' AND SUB.SUBCATEGORIA <> 'MOTO'))
),
MARCAS_PROPIAS AS (
SELECT
'PROPIAS' AS "LINEA",
'PROPIAS' AS "SUBCATEGORIA",
SUERP.CODIGO AS COD_ENERGITECA,
SUERP.NOMBRE AS ENERGITECA,
ZO.CODIGO AS ZONA,
VEHI.VENDEDOR AS CODIGO_VENDEDOR,
VEHI.CLIENTE_NIT,
CLIE.NOMBRE AS NOMBRE_CLIENTE,
VEHI.NUMERO_FACTURA,
VEHI.DOCUMENTO_ORACLE,
VEHI.FECHA_VENTA,
VEHI.CODIGO AS REFERENCIA,
VEHI.DESC_ITEM,
VEHI.CANTIDAD AS CANTIDAD,
VEHI.VALOR_UNITARIO AS VALOR_UNITARIO,
VEHI.VALOR_UNITARIO*VEHI.CANTIDAD AS TOTAL,
EXTRACT(YEAR FROM VEHI.FECHA_VENTA) ANIO,
EXTRACT(MONTH FROM VEHI.FECHA_VENTA) MES,
EXTRACT(DAY FROM VEHI.FECHA_VENTA) DIA
FROM
SIMACMOBILE.VISTA_VENTA_HISTORICO_ERP VEHI
JOIN (
SELECT
HORE.SUBCATEGORIA,
HORE.EXCLUIR,
HORE.LINEA,
HORE.MARCA,
CARE.CODIGO_CATEGORIA
FROM
SIMACMOBILE.HOMOLOGACION_CATEGORIA_REFERENCIA_USEM_ERP HORE
JOIN SIMACMOBILE.CATEGORIAXREFERENCIA_ERP CARE ON CARE.ID_CAREERP = HORE.ID_CAREERP
) SUB ON SUB.CODIGO_CATEGORIA = VEHI.CATEGORIA_REFERENCIA AND SUB.MARCA = VEHI.MARCA AND SUB.LINEA = 'REPUESTOS' AND SUB.MARCA = 'COEXITO'
JOIN SUCURSALES SUERP ON SUERP.ID_SUERP = VEHI.ID_SUERP
JOIN SIMACMOBILE.SUCURSAL_ZONA_USEM_ERP SUZO ON SUZO.ID_SUERP = SUERP.ID_SUERP
JOIN SIMACMOBILE.ZONA_USEM_ERP ZO ON ZO.ID_ZOUSERP = SUZO.ID_ZOUSERP
LEFT JOIN (SELECT NIT, MAX(PARTY_SITE_NAME) NOMBRE FROM SIMACMOBILE.CLIENTE_ERP GROUP BY NIT) CLIE ON CLIE.NIT = VEHI.CLIENTE_NIT
WHERE
VEHI.FECHA_VENTA >= ADD_MONTHS(SYSDATE, -12)
),
MANO_OBRA AS (
SELECT
'MANO_DE_OBRA' AS "LINEA",
'MANO_DE_OBRA' AS "SUBCATEGORIA",
SUERP.CODIGO AS COD_ENERGITECA,
SUERP.NOMBRE AS ENERGITECA,
ZO.CODIGO AS ZONA,
VEHI.CODIGO_VENDEDOR,
VEHI.CLIENTE_NIT,
CLIE.NOMBRE AS NOMBRE_CLIENTE,
VEHI.NUMERO_FACTURA,
VEHI.DOCUMENTO_ORACLE,
VEHI.FECHA_VENTA,
VEHI.CODIGO_ITEM AS REFERENCIA,
VEHI.DESC_ITEM,
VEHI.CANTIDAD_ITEM AS CANTIDAD,
VEHI.MONTO_UNITARIO_ITEM AS VALOR_UNITARIO,
VEHI.MONTO_UNITARIO_ITEM*VEHI.CANTIDAD_ITEM AS TOTAL,
EXTRACT(YEAR FROM VEHI.FECHA_VENTA) ANIO,
EXTRACT(MONTH FROM VEHI.FECHA_VENTA) MES,
EXTRACT(DAY FROM VEHI.FECHA_VENTA) DIA
FROM
SIMACMOBILE.VENTA_HISTORICO_ERP VEHI
JOIN SIMACMOBILE.REFERENCIA_ERP re ON VEHI.ID_REERP = re.ID_REERP AND re.CODIGO LIKE '07_%' AND re.LINEA = 'MANO_DE_OBRA'
JOIN SUCURSALES SUERP ON SUERP.ID_SUERP = VEHI.ID_SUERP
JOIN SIMACMOBILE.SUCURSAL_ZONA_USEM_ERP SUZO ON SUZO.ID_SUERP = SUERP.ID_SUERP
JOIN SIMACMOBILE.ZONA_USEM_ERP ZO ON ZO.ID_ZOUSERP = SUZO.ID_ZOUSERP
LEFT JOIN (SELECT NIT, MAX(PARTY_SITE_NAME) NOMBRE FROM SIMACMOBILE.CLIENTE_ERP GROUP BY NIT) CLIE ON CLIE.NIT = VEHI.CLIENTE_NIT
WHERE
VEHI.FECHA_VENTA >= ADD_MONTHS(SYSDATE, -12)
)
SELECT
*
FROM MANO_OBRA MO
UNION ALL
SELECT
*
FROM MARCAS_PROPIAS MP
UNION ALL
SELECT
*
FROM FACTURAS_LINEAS FL;