12. INVENTORY DASHBOARD

12. INVENTORY DASHBOARD


ENTIDADES GOLD DEL TABLERO DE INVENTARIOS 

El Tablero de Inventario está compuesto por entidades que consolidan información clave de órdenes de compra, órdenes de venta, órdenes de producción y tablas descriptivas (tablas TXT). Su objetivo es proporcionar una proyección de stock a futuro, permitiendo una gestión eficiente de inventarios basada en:

  • Materiales comprometidos (en proceso de venta). 
  • Stock entrante (en proceso de compra). 
  • Transferencia de stock (movimientos internos de materiales). 
  • Stock en producción (materiales en órdenes de fabricación). 

Estas entidades permiten visualizar la disponibilidad de materiales y prever posibles faltantes o excesos, optimizando la planificación de suministros y el cumplimiento de pedidos.



Entidades que componen el Tablero de Inventarios 

Las siguientes entidades forman parte de este modelo:

  1. OI_GOLD_SD_OPEN_TRACKER_ECC.sql
  2. OI_GOLD_SD_OPEN_TRACKER_RECIVINGS_ECC.sql
  3. OI_GOLD_SD_INVENTORY_ECC.sql

OI_GOLD_SD_OPEN_TRACKER_ECC 

Este modelo pertenece a la capa Gold de la arquitectura medallón y se compone de la entidad Silver de órdenes de venta. Su propósito es identificar órdenes de venta no completadas con fecha de entrega a futuro, permitiendo estimar el stock comprometido.



Dependencias 

  • OI_SILVER_SD_SALES_ORDER_ECC (Órdenes de Venta - Capa Silver)
    Contiene información consolidada de las órdenes de venta dentro del módulo SD en SAP ECC.
    <client>_<environment>_SD_ORDENES_VENTA_SILVER
  • MARD (Stock de Material por Almacén)
    Almacena información sobre los niveles de inventario de los materiales en cada almacén, permitiendo gestionar la disponibilidad de stock en tiempo real.
    <client>_<environment>_MM_MATERIAL_ALMACEN
  • T001W (Centros de Planta)
    Contiene la lista de centros en SAP, representando ubicaciones físicas donde se realizan operaciones logísticas, productivas o de almacenamiento.
    <client>_<environment>_DM_PLANTAS
  • TVKOT (Organización de Ventas - Descripción)
    Contiene las descripciones de las organizaciones de ventas en diferentes idiomas, permitiendo identificar las estructuras comerciales dentro de SAP.
    <client>_<environment>_DM_ORGANIZACION_VENTAS_TXT
  • TVTWT (Canales de Distribución - Descripción)
    Almacena las descripciones de los canales de distribución utilizados en SAP SD, facilitando la segmentación y gestión de ventas.
    <client>_<environment>_DM_CANAL_DISTRBUCION_TXT
  • TSPAT (Grupo de Estadísticas de Materiales)
    Contiene información sobre la clasificación de materiales para estadísticas de ventas y análisis de desempeño comercial.
    <client>_<environment>_DM_SECTOR_COMERCIAL_TXT
  • T023T (Grupo de Materiales - Descripción)
    Almacena las descripciones de los grupos de materiales en diferentes idiomas, facilitando la categorización de productos.
    <client>_<environment>_DM_GRUPO_MATERIAL_TXT
  • TVKBT (Grupos de Clientes - Descripción)
    Contiene las descripciones de los grupos de clientes en SAP.
    <client>_<environment>_DM_OFICINA_VENTAS_TXT
  • T179T (Jerarquía de Materiales - Descripción)
    Almacena la información estructurada de la jerarquía de materiales, facilitando la clasificación de productos dentro del sistema.
    <client>_<environment>_DM_JERARQUIA_MATERIALES_TXT
  • MAKT (Descripción de Materiales)
    Contiene las descripciones de los materiales en diferentes idiomas.
    <client>_<environment>_MM_MATERIAL_DESCRIPCION
  • KNA1 (Maestro de Clientes - Datos Generales)
    Almacena los datos generales de los clientes en SAP.
    <client>_<environment>_SD_MAESTRO_CLIENTES


Supuestos 

  • Se consideran órdenes de venta completadas únicamente aquellos registros donde statusglob_vbuk = 'C'. 
  • El estado del inventario se calcula con la suma de la cantidad disponible de stock libre para su uso, consolidado por planta y material:
    SUM(mard.libre_utiliz) AS disponible, agrupado por planta y material. 
  • El estado de entrega se determina como la diferencia en días entre la fecha actual y la fecha de entrega estimada, calculada de la siguiente manera:
    dateDiff('day', NOW(), toDate(sso.feentrega_vbep, '%Y%m%d')) AS delivery_time. 


Instalación 

La instalación de esta entidad sigue el mismo proceso que OI_SILVER_SD_SALES_ORDER_ECC, ya que ambas forman parte de una instalación en cadena.

Es importante asegurarse de que todas las dependencias y entidades previas en la cadena de instalación estén correctamente configuradas antes de proceder con la ejecución.

Para desplegar el modelo, ejecutar el siguiente comando:

docker-compose up -d dbt_oi_sales_order --build


Modelo sql ejecutado 

  1. {{ config(
  2.    order_by='(mandante_vbak, docvtas_vbak, posicion_vbap)',
  3.    engine='ReplacingMergeTree(_version)',
  4.    materialized='incremental',
  5.    incremental_strategy='append',
  6.     pre_hook=[
  7.         "{% if is_incremental() %}
  8.             ALTER TABLE {{this}} DELETE
  9.             WHERE (docvtas_vbak) NOT IN (
  10.                 SELECT docvtas_vbak 
  11.                 FROM {{ ref('OI_SILVER_SD_SALES_ORDER_ECC') }})
  12.         {% endif %}"
  13.     ],
  14.     post_hook=[
  15.         "ALTER TABLE {{this}} DELETE
  16.        WHERE statusglob_vbuk = 'C'",
  17.     ]
  18. ) }}

  20. /*
  21. * SOLO PROCESARÁ VENTAS NUEVAS O REALIZARÁ UN RECALCULO EN CASO DE UN VENTA ELIMINADA.
  22. */
  23. WITH new_data AS (
  24.     SELECT

  26.         sso.mandante_vbak as mandante_vbak,
  27.         sso.docvtas_vbak as docvtas_vbak, 
  28.         sso.posicion_vbap as posicion_vbap,
  29.         sso.material_vbap as material_vbap, 
  30.         sso.centro_vbap as centro_vbap,
  31.         sso.ctdped_vbep as ctdped_vbep,
  32.         formatDateTime(toDate(sso.feentrega_vbep, '%Y%m%d'), '%Y-%m-%d') as fecha_entrega,
  33.         formatDateTime(toDate(sso.feentrega_vbep, '%Y%m%d'), '%Y-%m') as mes_entrega,

  35.         sso.solic_vbak as solic_vbak,
  36.         sso.grclient_vbkd as grclient_vbkd,
  37.         sso.pais_vbpa as pais_vbpa,
  38.         sso.feentrega_vbep as feentrega_vbep,
  39.         sso.fechsol_vbak as fechsol_vbak,
  40.         sso.direccion_vbpa as direccion_vbpa,
  41.         sso.condexp_vbak as condexp_vbak,
  42.         sso.orgventas_vbak as orgventas_vbak,
  43.         sso.candistr_vbak as candistr_vbak,
  44.         sso.oficvta_vbak as oficvta_vbak,
  45.         sso.jquiaprod_vbap as jquiaprod_vbap,
  46.         sso.sector_vbap as sector_vbap,
  47.         sso.priorentr_vbap as priorentr_vbap,
  48.         sso.gpoartic_vbap as gpoartic_vbap,
  49.         sso.ruta_vbap as ruta_vbap,
  50.         sso.zona_vbpa as zona_vbpa,
  51.         sso.statusglob_vbuk as statusglob_vbuk,
  52.         sso._version as updated_at

  54.     FROM {{ ref('OI_SILVER_SD_SALES_ORDER_ECC')}} as sso FINAL
  55.     {% if is_incremental() %}
  56.     WHERE sso._version >= (SELECT max(updated_at) FROM {{this}})
  57.     {% endif %}
  58. ),

  60. inventory as (
  61.     SELECT 
  62.         mard.mandante as mandante,
  63.        mard.material as material,
  64.        mard.centro as centro,
  65.         SUM(mard.libre_utiliz) disponible
  66.         FROM {{ source('oneconnect', 'PR0_B_MM_MATERIAL_ALMACEN') }} mard FINAL
  67.         GROUP BY mard.mandante, mard.material, mard.centro
  68. )

  70. SELECT DISTINCT
  71.     sso.mandante_vbak as mandante_vbak,
  72.    sso.docvtas_vbak as docvtas_vbak, 
  73.    sso.posicion_vbap as posicion_vbap,
  74.    sso.material_vbap as material_vbap, 
  75.    sso.centro_vbap as centro_vbap,
  76.    sso.ctdped_vbep as ctdped_vbep,
  77.    sso.fecha_entrega as fecha_entrega,
  78.    sso.mes_entrega as mes_entrega,

  80.    sso.solic_vbak as solic_vbak,
  81.    sso.grclient_vbkd as grclient_vbkd,
  82.    sso.pais_vbpa as pais_vbpa,
  83.    sso.feentrega_vbep as feentrega_vbep,
  84.    sso.fechsol_vbak as fechsol_vbak,
  85.    sso.direccion_vbpa as direccion_vbpa,
  86.    sso.condexp_vbak as condexp_vbak,
  87.    sso.orgventas_vbak as orgventas_vbak,
  88.    sso.candistr_vbak as candistr_vbak,
  89.    sso.oficvta_vbak as oficvta_vbak,
  90.    sso.jquiaprod_vbap as jquiaprod_vbap,
  91.    sso.sector_vbap as sector_vbap,
  92.    sso.priorentr_vbap as priorentr_vbap,
  93.    sso.gpoartic_vbap as gpoartic_vbap,
  94.    sso.ruta_vbap as ruta_vbap,
  95.    sso.zona_vbpa as zona_vbpa,
  96.    sso.statusglob_vbuk as statusglob_vbuk,
  97.    sso.updated_at as updated_at,
  98.    dateDiff('day',  NOW(), toDate(sso.feentrega_vbep, '%Y%m%d')) as delivery_time,
  99.     LEAST(delivery_time, 8) as status,
  100.     
  101.     ssiv.disponible as stock_disponible, 

  103.     t001w.nom1 as centro,
  104.    t001w.idioma  AS idioma,
  105.    tvkot.denomin AS orgventas,
  106.    tvtwt.denomin AS candistr,
  107.    tspat.denomin AS sector,
  108.    t023t.gpoartic1 AS gpoartic,
  109.    tvkbt.denomin AS oficvta,
  110.    t179t.denom  as jquiaprod,

  112.     makt.denominacion as material,
  113.     kna1.nombre1 as cliente,
  114.     --tvsbt.denomin as condexp,

  116. --     tvrot.name as route,
  117. -- tzon.ctryreg as ctryreg,
  118. -- tzon.descript as zone_txt,
  119.    now64() as _version
  120. FROM new_data as sso
  121. --TODO: CAMBIAR LUEGO
  122. LEFT JOIN inventory as ssiv 
  123.     ON ssiv.material  = sso.material_vbap and ssiv.centro  = sso.centro_vbap
  124. LEFT JOIN (SELECT cp, nom1, centro , idioma   FROM {{ source('oneconnect', 'PR0_B_DM_PLANTAS') }} FINAL) AS  t001w  on t001w.centro  = sso.centro_vbap AND t001w.idioma ='S'
  125. LEFT JOIN (SELECT denomin, orgventas, idioma FROM {{ source('oneconnect', 'PR0_B_DM_ORGANIZACION_VENTAS_TXT') }} FINAL) AS tvkot on tvkot.orgventas  = sso.orgventas_vbak  and tvkot.idioma = t001w.idioma
  126. LEFT JOIN (SELECT denomin, candistr, idioma FROM {{ source('oneconnect', 'PR0_B_DM_CANAL_DISTRBUCION_TXT') }} FINAL) AS tvtwt  on tvtwt.candistr  = sso.candistr_vbak  and tvtwt.idioma = t001w.idioma
  127. LEFT JOIN (SELECT idioma, sector, denomin FROM {{ source('oneconnect', 'PR0_B_DM_SECTOR_COMERCIAL_TXT') }} FINAL) AS  tspat  on tspat.sector  = sso.sector_vbap and tspat.idioma = t001w.idioma
  128. LEFT JOIN (SELECT gpoartic1, gpoartic, idioma FROM {{ source('oneconnect', 'PR0_B_DM_GRUPO_MATERIAL_TXT') }} FINAL) AS t023t  on  t023t.gpoartic = sso.gpoartic_vbap and t023t.idioma = t001w.idioma
  129. LEFT JOIN (SELECT idioma, oficvta, denomin  FROM {{ source('oneconnect', 'PR0_B_DM_OFICINA_VENTAS_TXT') }} FINAL) AS tvkbt  on tvkbt.oficvta = sso.oficvta_vbak and tvkbt.idioma = t001w.idioma
  130. LEFT JOIN (SELECT idioma, jquiaprod, denom  FROM {{ source('oneconnect', 'PR0_B_DM_JERARQUIA_MATERIALES_TXT') }} FINAL) AS t179t  on t179t.jquiaprod = sso.jquiaprod_vbap and t179t.idioma = t001w.idioma
  131. LEFT JOIN (SELECT denominacion, material, idioma FROM {{ source('oneconnect', 'PR0_B_MM_MATERIAL_DESCRIPCION') }} FINAL) AS makt  on makt.material = sso.material_vbap and makt.idioma = t001w.idioma
  132. LEFT JOIN (SELECT nombre1, cliente, idioma FROM {{ source('oneconnect', 'PR0_B_SD_MAESTRO_CLIENTES') }} FINAL) AS kna1  on kna1.cliente = sso.solic_vbak and kna1.idioma = t001w.idioma
  133. --LEFT JOIN (SELECT idioma, condexp, denomin FROM {{ source('oneconnect', 'PR0_B_DM_COND_EXPEDICION_TXT') }} FINAL) AS tvsbt  on tvsbt.condexp = sso.condexp_vbak and tvsbt.idioma = t001w.idioma

  135. ORDER BY fecha_entrega ASC



OI_GOLD_SD_OPEN_TRACKER_RECIVINGS_ECC 

Este modelo consolida información clave de órdenes de compra, órdenes de producción y transferencias de stock, enfocándose en aquellos registros con fechas de entrega futuras y estados no completados. Su objetivo es proporcionar una visión integral de las operaciones pendientes, facilitando el seguimiento y la planificación del inventario.

Componentes del Modelo:

  • Órdenes de compra: Información sobre los materiales solicitados a proveedores, asegurando visibilidad de los suministros entrantes. 
  • Órdenes de producción: Datos de los productos en proceso de fabricación, permitiendo anticipar la disponibilidad de manufactura. 
  • Transferencias de stock: Registros de movimientos de materiales entre plantas, optimizando la gestión de inventario interno. 

El modelo filtra los registros con estados distintos a "completado" y fechas de entrega posteriores a la fecha actual, asegurando un seguimiento preciso de las actividades en curso y su impacto en la disponibilidad de stock.



Dependencias 

  • OI_SILVER_MM_PURCHASE_ORDER_ECC (Órdenes de Compra - Capa Silver)
    Contiene información consolidada de las órdenes de compra dentro del módulo MM en SAP ECC, proporcionando visibilidad sobre los materiales solicitados a proveedores.
    OI_SILVER_MM_PURCHASE_ORDER_ECC
  • OI_SILVER_PP_PRODUCTION_ORDER_ECC (Órdenes de Producción - Capa Silver)
    Almacena datos estructurados de las órdenes de producción en el módulo PP de SAP, permitiendo el análisis y seguimiento de materiales en proceso de fabricación.
    OI_SILVER_PP_PRODUCTION_ORDER_ECC
  • MARD (Stock de Material por Almacén)
    Almacena información sobre los niveles de inventario de los materiales en cada almacén, permitiendo gestionar la disponibilidad de stock en tiempo real.
    <client>_<environment>_MM_MATERIAL_ALMACEN
  • T001W (Centros de Planta)
    Contiene la lista de centros en SAP, representando ubicaciones físicas donde se realizan operaciones logísticas, productivas o de almacenamiento.
    <client>_<environment>_DM_PLANTAS
  • MAKT (Descripción de Materiales)
    Contiene las descripciones de los materiales en diferentes idiomas.
    <client>_<environment>_MM_MATERIAL_DESCRIPCION


Supuestos 

  • Se considera Orden de Compra no completadas a los registros de la entidad OI_SILVER_MM_PURCHASE_ORDER_ECC filtros:

...

fechaentrgestad_eket <> '00000000'

and fechaentrgestad_eket <> ''

and cantidad_pedido_ekpo <> 0

and tipo_posicion_ekpo <> '7'

and entregado_compl_ekpo <> 'X'

and material_ekpo <> ''

...


  • Se considera Transferencia de Stock no completados a los registros de la entidad OI_SILVER_MM_PURCHASE_ORDER_ECC con filtros:

...

fechaentrgestad_eket <> '00000000'

and fechaentrgestad_eket <> ''

and cantidad_pedido_ekpo <> 0

and tipo_posicion_ekpo = '7'

and entregado_compl_ekpo <> 'X'

and material_ekpo <> ''

...


  • Se considera Orden de Producción no completados o en progreso a los registros de la entidad OI_SILVER_PP_PRODUCTION_ORDER_ECC con los siguientes filtros:

...

fin_programado_afpo <> '' 

and fin_programado_afpo  <> '00000000'

and notiffinal_afru <> 'X'

...




Instalación 

docker-compose up -d dbt_oi_production_order --build



Modelo sql ejecutado 

  1. {{ config(
  2.    order_by='(operacion, nro_orden, posicion, material, centro)',
  3.    engine='ReplacingMergeTree(_version)',
  4.    materialized='incremental',
  5.    incremental_strategy='append'
  6. ) }}

  8. WITH inventory as (
  9.     
  10.     SELECT 
  11.         mard.mandante as mandante,
  12.        mard.material as material,
  13.        mard.centro as centro,
  14.         SUM(mard.libre_utiliz) disponible
  15.     FROM {{ source('oneconnect', 'PR0_B_MM_MATERIAL_ALMACEN') }} mard FINAL
  16.     GROUP BY mard.mandante, mard.material, mard.centro
  17. ),

  19. purchase_orders as (

  21.    SELECT
  22.        'Orden de Compra' as operacion,
  23.         ospo.doccompras_ekpo as nro_orden,
  24.         ospo.posicion_ekpo as posicion,
  25.         ospo.centro_ekpo as centro, 
  26.         ospo.material_ekpo as material, 
  27.         formatDateTime(toDate(ospo.fechaentrgestad_eket, '%Y%m%d'), '%Y-%m-%d') as fecha_entrega,
  28.         ospo.fechaentrgestad_eket as fecha_entrega_str,
  29.         ospo.cantidad_pedido_ekpo as cantidad_pedido,
  30.         formatDateTime(toDate(ospo.fechaentrgestad_eket, '%Y%m%d'), '%Y-%m') as mes_entrega,
  31.         ospo._version as purchase_updated_at,
  32.        toDateTime64(0, 8) as transfer_updated_at,
  33.        toDateTime64(0, 8) as production_updated_at

  35.     FROM {{ ref('OI_SILVER_MM_PURCHASE_ORDER_ECC') }} AS ospo FINAL
  36.     WHERE
  37.         
  38.         ospo.fechaentrgestad_eket <> '00000000'
  39.         and ospo.fechaentrgestad_eket <> ''
  40.         and ospo.cantidad_pedido_ekpo <> 0
  41.         and ospo.tipo_posicion_ekpo <> '7'
  42.         and ospo.entregado_compl_ekpo <> 'X'
  43.         and ospo.material_ekpo <> ''
  44.         {% if is_incremental() %}
  45.         and ospo._version >= (select max(purchase_updated_at) from {{this}})
  46.         {% endif %}
  47. ),

  49. stock_orders as (

  51.    SELECT
  52.         'Transferencia de Stock' as operacion,
  53.         ospo.doccompras_ekpo as nro_orden,
  54.         ospo.posicion_ekpo as posicion,
  55.         ospo.centro_ekpo as centro_ekpo, 
  56.         ospo.material_ekpo as material, 
  57.         formatDateTime(toDate(ospo.fechaentrgestad_eket, '%Y%m%d'), '%Y-%m-%d') as fecha_entrega,
  58.         ospo.fechaentrgestad_eket as fecha_entrega_str,
  59.         ospo.cantidad_pedido_ekpo as cantidad_pedido,
  60.         formatDateTime(toDate(ospo.fechaentrgestad_eket, '%Y%m%d'), '%Y-%m') as mes_entrega,
  61.        toDateTime64(0, 8) as purchase_updated_at,
  62.        ospo._version as transfer_updated_at,
  63.        toDateTime64(0, 8) as production_updated_at
  64.     FROM {{ ref('OI_SILVER_MM_PURCHASE_ORDER_ECC') }} AS ospo FINAL
  65.     WHERE
  66.         ospo.fechaentrgestad_eket <> '00000000'
  67.         and ospo.fechaentrgestad_eket <> ''
  68.        and ospo.cantidad_pedido_ekpo <> 0
  69.        and ospo.tipo_posicion_ekpo = '7'
  70.        and ospo.entregado_compl_ekpo <> 'X'
  71.        and ospo.material_ekpo <> ''
  72.         {% if is_incremental() %}
  73.         and ospo._version >= (select max(transfer_updated_at) from {{this}})
  74.         {% endif %}
  75. ),

  77. production_orders as (

  79.    SELECT
  80.        'Orden de Producción' as operacion,
  81.         ssp.orden_afpo as nro_orden,
  82.         ssp.n_posicion_afpo as posicion,
  83.         ssp.centro_afpo as centro,
  84.         ssp.numero_material_afpo as material,
  85.         formatDateTime(toDate(ssp.fin_programado_afpo, '%Y%m%d'), '%Y-%m-%d') as fecha_entrega,
  86.         ssp.fin_programado_afpo as fecha_entrega_str,
  87.         ssp.cantidad_pos_afpo as cantidad_pedido,
  88.         formatDateTime(toDate(ssp.fin_programado_afpo, '%Y%m%d'), '%Y-%m') as mes_entrega,
  89.        toDateTime64(0, 8) as purchase_updated_at,
  90.        toDateTime64(0, 8) as transfer_updated_at,
  91.        ssp._version as production_updated_at
  92.     FROM {{ ref('OI_SILVER_PP_PRODUCTION_ORDER_ECC') }} AS ssp FINAL
  93.     where 
  94.         ssp.fin_programado_afpo <> '' 
  95.         and ssp.fin_programado_afpo  <> '00000000'
  96.         and ssp.notiffinal_afru <> 'X'
  97.         {% if is_incremental() %}
  98.         and ssp._version >= (select max(production_updated_at) from {{this}})
  99.         {% endif %}
  100. ),

  102. plants as (
  103.     SELECT
  104.         nom1, 
  105.         centro, 
  106.         idioma   
  107.     FROM {{ source('oneconnect', 'PR0_B_DM_PLANTAS') }} 
  108.     FINAL
  109. ),

  111. material_desc as (
  112.     SELECT 
  113.         denominacion, 
  114.         material, 
  115.         idioma 
  116.     FROM {{ source('oneconnect', 'PR0_B_MM_MATERIAL_DESCRIPCION') }} 
  117.     FINAL
  118. )

  120. SELECT 
  121.    orders.operacion as operacion,
  122.     orders.nro_orden as nro_orden,
  123.     orders.posicion as posicion,
  124.     orders.centro as centro,
  125.    orders.material as material,
  126.     orders.fecha_entrega as fecha_entrega,
  127.    orders.fecha_entrega_str as fecha_entrega_str,
  128.    orders.cantidad_pedido as cantidad_pedido,
  129.     orders.mes_entrega as mes_entrega,
  130.    orders.purchase_updated_at as purchase_updated_at,
  131.    orders.transfer_updated_at as transfer_updated_at,
  132.    orders.production_updated_at as production_updated_at,
  133.    mard.disponible as disponible,
  134.    t001w.nom1  as desc_centro,
  135.    makt.denominacion AS desc_material,
  136.    now64() as _version
  137. FROM (
  138.    SELECT *
  139.    FROM purchase_orders
  140.    UNION ALL
  141.    SELECT *
  142.    FROM  stock_orders
  143.    UNION ALL
  144.    SELECT *
  145.    FROM production_orders
  146. ) AS orders
  147. left JOIN inventory as mard on mard.material = orders.material and mard.centro = orders.centro
  148. left JOIN plants AS t001w  on t001w.centro  = orders.centro AND t001w.idioma = 'S'
  149. left JOIN material_desc as makt on makt.material = mard.material and makt.idioma = t001w.idioma


OI_GOLD_SD_INVENTORY_ECC 

Este modelo proporciona una proyección dinámica del estado del inventario a futuro, integrando información clave de múltiples fuentes para ofrecer una visión completa de la disponibilidad de stock en el tiempo.

Componentes del Modelo:

El modelo combina diversas fuentes de datos para generar una previsión precisa del inventario:

  • Órdenes de ventas: Cantidades comprometidas para entregas futuras, reduciendo la disponibilidad de stock. 
  • Órdenes de compras: Materiales en tránsito desde proveedores, aumentando la disponibilidad proyectada. 
  • Órdenes de producción: Productos en proceso de fabricación, impactando el inventario una vez finalizados. 
  • Transferencias de stock entre plantas: Movimientos internos planificados que afectan la disponibilidad en distintas ubicaciones. 


Dependencias 
  • OI_SILVER_MM_PURCHASE_ORDER_ECC (Órdenes de Compra - Capa Silver)
    Contiene información consolidada de las órdenes de compra dentro del módulo MM en SAP, proporcionando visibilidad sobre los materiales solicitados a proveedores y su impacto en la planificación de inventario.
    <client>_<environment>_MM_ORDENES_COMPRA_SILVER
  • OI_SILVER_PP_PRODUCTION_ORDER_ECC (Órdenes de Producción - Capa Silver)
    Almacena datos estructurados de las órdenes de producción en el módulo PP de SAP, permitiendo el análisis y seguimiento de materiales en proceso de fabricación.
    <client>_<environment>_PP_ORDENES_PRODUCCION_SILVER
  • OI_GOLD_SD_OPEN_TRACKER_ECC (Seguimiento de Órdenes Abiertas - Capa Gold)
    Consolida información de órdenes de venta no completadas con fechas de entrega a futuro, proporcionando un seguimiento del stock comprometido y su impacto en la disponibilidad de inventario.
    <client>_<environment>_SD_ORDENES_ABIERTAS_GOLD
  • MARC (Datos de Material por Centro)
    Almacena información específica de cada material por centro.
    <client>_<environment>_MM_MATERIAL_PLANTA
  • MARD (Stock de Material por Almacén)
    Almacena información sobre los niveles de inventario de los materiales en cada almacén, permitiendo gestionar la disponibilidad de stock en tiempo real.
    <client>_<environment>_MM_MATERIAL_ALMACEN
  • T001W (Centros de Planta)
    Contiene la lista de centros en SAP, representando ubicaciones físicas donde se realizan operaciones logísticas, productivas o de almacenamiento.
    <client>_<environment>_DM_PLANTAS
  • MAKT (Descripción de Materiales)
    Contiene las descripciones de los materiales en diferentes idiomas.
    <client>_<environment>_MM_MATERIAL_DESCRIPCION

Supuestos 

  • Se considera Stock disponible a la operacion SUM(mard.libre_utiliz) agrupado por planta y material. 
  • Se considera Cantidad Recibido por día a la operación ventana de la cantidad esperada de las ordenes de compras, ordenes de producción o transferencias de stock de la entidad OI_GOLD_SD_OPEN_TRACKER_RECIVINGS_ECC :
​

...

SUM(cantidad_pedido_dia) OVER (PARTITION BY material, centro   ORDER BY fecha_entrega ASC) as cantidad_pedido_dia_ventana,

...

​
  • Se considera Cantidad de perdido al día a la operación ventana de la cantidad comprometida de las ordenes de ventas:

...

SUM(cantidad_pedido_dia) OVER (PARTITION BY material, centro  ORDER BY fecha_entrega ASC) as cantidad_pedido_dia_ventana

...

​
  • Se considera Stock futuro a la operación :

...

(stock_disponible + cantidad_recibido_dia_ventana - cantidad_pedido_dia_ventana) as future_stock,

...

​
  • Se considera Stock actual a la operación :

...

(stock_disponible - cantidad_pedido_dia_ventana)  as actual_stock,

...

​

​

​
Instalación 

docker-compose up -d dbt_oi_sales_order --build


​
​

Modelo sql ejecutado 

  1. {{ config(
  2.    order_by='(material, centro, fecha_entrega_str)',
  3.    engine='ReplacingMergeTree(_version)',
  4.    materialized='incremental',
  5.    incremental_strategy='append'
  6. ) }}
  7. /* new_sales_orders: muestra los nuevas ordenes de ventas registradas.
  8. * sales_orders_affected: muestra todas las ordenes de ventas pasadas en el mes y mismo material.
  9. * sales_orders_sumary: Calculo de las ventas por días y periodo.
  10. */
  11. WITH new_sales_orders as (
  12.    select DISTINCT
  13.        got.fecha_entrega as fecha_entrega,
  14.        got.feentrega_vbep as fecha_entrega_str,
  15.        got.mes_entrega  as mes_entrega,
  16.        got.docvtas_vbak as orden,
  17.        got.material_vbap as material,
  18.        got.centro_vbap as centro,
  19.        got.ctdped_vbep as cantidad_pedido, -- KWMENG
  20.        got._version as _version
  21.    FROM {{ ref('OI_GOLD_SD_OPEN_TRACKER_ECC')}} got FINAL
  22.    {% if is_incremental() %}
  23.    WHERE got._version >= (select max(got_version) from {{this}})
  24.    {% endif %}
  25. ),

  27. sales_orders_affected as (
  28.    SELECT 
  29.        *
  30.    FROM new_sales_orders
  31.    {% if is_incremental() %}
  32.     UNION DISTINCT
  33.     SELECT
  34.         got.fecha_entrega as fecha_entrega,
  35.        got.feentrega_vbep as fecha_entrega_str,
  36.        got.mes_entrega  as mes_entrega,
  37.        got.docvtas_vbak as orden,
  38.        got.material_vbap as material,
  39.        got.centro_vbap as centro,
  40.        got.ctdped_vbep as cantidad_pedido,
  41.        got._version as _version
  42.     FROM {{ ref('OI_GOLD_SD_OPEN_TRACKER_ECC')}} AS got FINAL
  43.     where (got.centro_vbap, got.material_vbap) IN (SELECT DISTINCT centro, material FROM new_sales_orders)
  44.     {% endif %}
  45. ),

  47. sales_orders_sumary as (
  48.         SELECT 
  49.            fecha_entrega,
  50.            fecha_entrega_str,
  51.            material,
  52.            centro,
  53.            SUM(cantidad_pedido) as cantidad_pedido_dia,
  54.            MAX(_version) AS _version
  55.        FROM sales_orders_affected
  56.        GROUP BY fecha_entrega,fecha_entrega_str, material, centro
  57. ),

  59. new_orders_receiving  as (
  60.    SELECT DISTINCT 
  61.             operacion,
  62.             fecha_entrega,
  63.             fecha_entrega_str,
  64.             mes_entrega,
  65.            nro_orden,
  66.            cantidad_pedido,
  67.             centro,
  68.             material,
  69.             _version
  70.         FROM {{ ref('OI_GOLD_SD_OPEN_TRACKER_RECIVINGS_ECC')}}
  71.         FINAL
  72.         {% if is_incremental() %}
  73.        WHERE _version >= (select max(goptr_version) from {{this}}) 
  74.        {% endif %}
  75. ),

  77. orders_receiving_affected as (
  78.    SELECT 
  79.        *
  80.    FROM new_orders_receiving
  81.    {% if is_incremental() %}
  82.     UNION DISTINCT
  83.     SELECT
  84.         operacion,
  85.        fecha_entrega,
  86.        fecha_entrega_str,
  87.        mes_entrega,
  88.        nro_orden,
  89.        cantidad_pedido,
  90.        centro,
  91.        material,
  92.        _version as goptr_version
  93.     FROM {{ ref('OI_GOLD_SD_OPEN_TRACKER_RECIVINGS_ECC')}}
  94.    FINAL
  95.     where (centro, material) IN (SELECT DISTINCT centro, material FROM new_orders_receiving)
  96.     {% endif %}
  97. ),

  99. orders_receiving_sumary as (
  100.    SELECT 
  101.         fecha_entrega,
  102.         fecha_entrega_str,
  103.        centro,
  104.        material,
  105.        SUM(cantidad_pedido) as cantidad_recibido_dia,
  106.        MAX(_version) as _version
  107.     FROM orders_receiving_affected
  108.     GROUP BY fecha_entrega, fecha_entrega_str, material, centro
  109. ),

  111. material_quantity_sumary as (
  112.     SELECT *,
  113.     -- SUM(sumary.cantidad_recibido_dia - sumary.quantity_sales_order_day) OVER (PARTITION BY material, centro   ORDER BY fecha_entrega ASC) as orders_sumary,
  114.    SUM(sumary.cantidad_pedido_dia) OVER (PARTITION BY material, centro   ORDER BY fecha_entrega ASC) as cantidad_pedido_dia_ventana,
  115.    SUM(sumary.cantidad_recibido_dia) OVER (PARTITION BY material, centro   ORDER BY fecha_entrega ASC) as cantidad_recibido_dia_ventana
  116.     -- MAX(sumary.cantidad_pedido_total) OVER (PARTITION BY material, centro ) as cantidad_pedido_total
  117.     FROM (
  118.        SELECT 
  119.            greatest(rqs.fecha_entrega, oqs.fecha_entrega) as fecha_entrega,
  120.            greatest(rqs.fecha_entrega_str, oqs.fecha_entrega_str) as fecha_entrega_str,
  121.            greatest(rqs.material, oqs.material) as material,
  122.            greatest(rqs.centro, oqs.centro) as centro,
  123.            
  124.            coalesce(rqs.cantidad_recibido_dia,0) as cantidad_recibido_dia,
  125.            --coalesce(rqs.order_recivings_sum_period,0) as quantity_receivings_period,
  126.            rqs._version as goptr_version,
  127.            
  128.            coalesce(oqs.cantidad_pedido_dia,0) as cantidad_pedido_dia,
  129.            -- coalesce(oqs.cantidad_pedido_total,0) as cantidad_pedido_total,
  130.        
  131.            
  132.            oqs._version as got_version
  133.        FROM sales_orders_sumary as oqs
  134.        FULL JOIN orders_receiving_sumary as rqs 
  135.            ON rqs.centro = oqs.centro and rqs.material = oqs.material and rqs.fecha_entrega_str = oqs.fecha_entrega_str
  136.     ) as sumary
  137. ),

  139. status_inventory as (
  140.    SELECT 
  141.        mard.mandante as mandante,
  142.        mard.material as material,
  143.        mard.centro as centro,
  144.        -- SUM(mard.stockintfr) AS in_transit,
  145.        -- SUM(mard.restricted) AS on_order,
  146.        -- SUM(mard.restricted) AS in_quality_control,
  147.        -- SUM(mard.blocked) AS allocated, --SPEME
  148.        -- SUM(mard.returns) AS damaged, --RETME
  149.        SUM(mard.libre_utiliz) stock_disponible, --LABST
  150.        MAX(marc.stock_maximo) AS stock_maximo, -- MABST
  151.        MAX(marc.stock_seguridad) AS stock_seguridad, --EISBE
  152.        CASE
  153.             WHEN stock_disponible > stock_maximo THEN 'Excess Stock'
  154.             WHEN stock_disponible < stock_seguridad THEN 'Low Stock'
  155.             WHEN stock_disponible = 0 THEN 'Out of Stock'
  156.             ELSE 'Balanced Stock'
  157.        END as status,
  158.        max(mard._version) as _version
  159.    FROM {{ source('oneconnect', 'PR0_B_MM_MATERIAL_ALMACEN') }} mard FINAL
  160.    INNER JOIN (
  161.        SELECT stock_maximo, stock_seguridad, centro, material 
  162.        FROM {{ source('oneconnect', 'PR0_B_MM_MATERIAL_PLANTA') }} FINAL
  163.    ) AS marc
  164.        ON mard.centro = marc.centro and mard.material = marc.material
  165.    {% if is_incremental() %}
  166.    WHERE 
  167.        mard._version >= (select max(mard_version) from {{this}}) 
  168.        OR mard.material in (select material from material_quantity_sumary)
  169.    {% endif %}
  170.    GROUP BY mard.mandante, mard.material, mard.centro
  171. )

  173. SELECT 
  174.    mqs.fecha_entrega as fecha_entrega,
  175.    mqs.fecha_entrega_str as fecha_entrega_str,
  176.    mqs.material as material,
  177.    mqs.centro as centro,

  179.    mqs.cantidad_recibido_dia as cantidad_recibido_dia,
  180.    mqs.cantidad_recibido_dia_ventana as cantidad_recibido_dia_ventana,
  181.    -- mqs.cantidad_pedido_total as cantidad_pedido_total,

  183.    mqs.cantidad_pedido_dia as cantidad_pedido_dia,
  184.    mqs.cantidad_pedido_dia_ventana as cantidad_pedido_dia_ventana,
  185.    --mqs.on_hand as quantity_sales_order_period,

  187.    ssin.stock_maximo as stock_maximo,
  188.    ssin.stock_seguridad as stock_seguridad,
  189.    ssin.status as status,
  190.    ssin.stock_disponible  as stock_disponible,
  191.    (ssin.stock_disponible + mqs.cantidad_recibido_dia_ventana - mqs.cantidad_pedido_dia_ventana) as future_stock,
  192.    (ssin.stock_disponible - mqs.cantidad_pedido_dia_ventana)  as actual_stock,
  193.    --SUM(mqs.cantidad_recibido_dia - mqs.quantity_sales_order_day) OVER (PARTITION BY mqs.material, mqs.centro   ORDER BY mqs.fecha_entrega ASC) as future_stock,
  194.    
  195.    t001w.nom1  as plant,
  196.    t001w.cp  as codigo_postal,
  197.    makt.denominacion as desc_material,
  198.    -- ssin.in_transit as  in_transit,
  199.    -- ssin.on_order as on_order,
  200.    -- ssin.allocated as allocated,
  201.    -- ssin.damaged as damaged,
  202.    -- ssin.in_quality_control as in_quality_control,

  204.    ssin._version as updated_at,
  205.    ssin._version as mard_version,
  206.    mqs.goptr_version as goptr_version,
  207.    mqs.got_version as got_version,
  208.    now64() as _version
  209. FROM material_quantity_sumary as mqs 
  210. LEFT JOIN status_inventory ssin
  211.    ON mqs.material = ssin.material and mqs.centro = ssin.centro
  212. LEFT JOIN ( SELECT cp, nom1, centro, idioma FROM {{ source('oneconnect', 'PR0_B_DM_PLANTAS') }} FINAL ) AS  t001w  
  213.    ON t001w.centro  = mqs.centro AND t001w.idioma ='S'
  214. LEFT JOIN (SELECT denominacion, material, idioma FROM {{ source('oneconnect', 'PR0_B_MM_MATERIAL_DESCRIPCION') }} FINAL) AS makt  
  215.    ON makt.material = mqs.material and makt.idioma ='S'


    • Related Articles

    • INVENTORY DASHBOARD

      Tablero de Inventarios Descripción General Propósito: El Tablero de Inventario está compuesto por entidades que consolidan información clave de órdenes de compra, órdenes de venta y órdenes de producción para mostrar los materiales entrantes y ...

    • INVOICE DASHBOARD

      Tablero de Facturación Descripción General Propósito: Análisis de ventas y facturación mensual. Fuente de Datos: ClickHouse. Instalación del Tablero Paso 1: Importar el JSON en Grafana Iniciar sesión en Grafana. Ir a Dashboards → Manage → Import. ...

    • PRODUCCTION DASHBOARD

      Tablero de Producción Descripción General Propósito: Seguimiento de ordenes y el seguimiento del performance de los procesos de producción, como la cantidad, calidad y eficiencia. Fuente de Datos: ClickHouse. Instalación del Tablero Paso 1: Importar ...

    • 13 PRODUCCTION DASHBOARD

      ENTIDADES GOLD DEL TABLERO DE PRODUCCIÓN El Tablero de Producción está compuesto por múltiples entidades Gold, proporcionando información clave sobre las órdenes de producción y su ejecución por período. Estas entidades permiten el análisis detallado ...

    • 11 INVOICE DASHBOARD

      ENTIDAD GOLD DE TABLERO DE FACTURAS También conocido como Tablero de Ventas, este modelo pertenece a la capa Gold de la arquitectura de medallón y está compuesto por la entidad BI_SILVER_SD_INVOICE_ECC. Su objetivo principal es proporcionar una ...