Inventario a una Fecha determinada con su costo

ppierce · 24 Septiembre, 2024 18:59

Buenas tardes

Acudosa a usted para solicitar la ayuda de si tiene un query que arroje el inventario a una fecha deterninada con su costo por items. @willy_caldero. Agradeceira toda la ayuda posibible

Saludos Cordiales

Benru · 24 Septiembre, 2024 20:38

Hola, buenas tardes:

Tengo uno que tiene un uso similar. Te invito a revisarlo y ver si te resulta útil.

SELECT 
  CAST( T2."ItemCode" AS varchar(100)) AS "LinkItem",
  T2."ItemCode", 
  T2."ItemName", 
  T1."WhsCode", 
  T1."WhsName",
  IFNULL((SELECT (SUM(Y."InQty") - SUM(Y."OutQty"))
          FROM "OINM" Y 
          WHERE Y."ItemCode" = T2."ItemCode" 
            AND Y."Warehouse" = T1."WhsCode" 
            AND Y."DocDate" <= {?DocDate}), 0) AS "Stock",
  IFNULL((SELECT (SUM(Y."TransValue")) 
          FROM "OINM" Y 
          WHERE Y."ItemCode" = T2."ItemCode" 
            AND Y."Warehouse" = T1."WhsCode" 
            AND Y."DocDate" <= {?DocDate}), 0) AS "Costo Total",
  T3."ItmsGrpNam"
  --T2."LastPurPrc" AS "UltimoPrecioCompra",
 --T0."AvgPrice" AS "PrecioPromedio"
FROM 
  "OITM" T2
  INNER JOIN "OITW" T0 ON T0."ItemCode" = T2."ItemCode"
  INNER JOIN "OWHS" T1 ON T1."WhsCode" = T0."WhsCode"
  INNER JOIN "OITB" T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod"
WHERE 
  (SELECT (SUM(Y."InQty") - SUM(Y."OutQty"))
   FROM "OINM" Y  
   WHERE Y."ItemCode" = T2."ItemCode" 
     AND Y."Warehouse" = T1."WhsCode" 
     AND Y."DocDate" <= {?DocDate}) != 0 
ORDER BY 
  T2."ItemCode", 
  T1."WhsCode";

ppierce · 25 Septiembre, 2024 13:40

Buenos dias
Benru
Primero que todo gracias por la informacion (query) tengo una consulta la variable {?DocDate}) a que a que tabla pertenece o debo cambiarla por la fecha final al corte de mi inventario. Ya que al momento de correctala me da error en esta variable.
error: sg 102, Level 15, State 1, Line 11
Incorrect syntax near ‘?’.

SIn mas por el momoenti y a la espera de tus comentarios

Benru · 25 Septiembre, 2024 15:38

Hola, buen día, ppierce. Te sugiero que pruebes con el “DocDate” de la OINM.

ppierce · 25 Septiembre, 2024 16:10

DECLARE @dataInicio DATETIME
DECLARE @dataFinal DATETIME
DECLARE @tempo INT

SET @tempo = (SELECT TOP 1 T0.TRANSNUM FROM OINM T0 WHERE T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1])
SET @dataInicio = (SELECT '[%0]')
SET @dataFinal = (SELECT '[%1]')

SELECT
    [Artículo] = t1.itemcode,
    [Desc. Artículo] = t1.ItemName,
    [Unidad de Medida] = t1.InvntryUom,
    [Stock inicial] = ISNULL(COALESCE(t0.saldoinic, 0), 0),
    [Entradas] = ISNULL(t1.entradas, 0),
    Consumo = ISNULL(t1.consumo, 0),
    [Consumo medio diario] = ISNULL(t1.consumo / DATEDIFF(day, @dataInicio, @dataFinal), 0),
  /*[costo] =   ISNULL(COALESCE(t1.AvgPrice,0),0),*/
    [Stock final] = ISNULL(COALESCE(t0.saldoinic, 0) + COALESCE(t1.saldofin, 0), 0),
    [Stock promedio] = ISNULL((COALESCE(t0.saldoinic, 0) + (COALESCE(t0.saldoinic, 0) + t1.saldofin)) / 2, 0),
    [Rotación stock] = CASE
        WHEN ISNULL((COALESCE(t0.saldoinic, 0) + (COALESCE(t0.saldoinic, 0) + t1.saldofin)) / 2, 0) = 0 THEN 0
        ELSE ISNULL(t1.consumo / ((COALESCE(t0.saldoinic, 0) + (COALESCE(t0.saldoinic, 0) + t1.saldofin)) / 2), 0)
    END,
    [Cobertura de stock] = CASE
        WHEN ISNULL(COALESCE(t1.consumo / DATEDIFF(day, @dataInicio, @dataFinal), 0), 0) = 0 THEN 0
        ELSE ISNULL(((COALESCE(t0.saldoinic, 0) + (COALESCE(t0.saldoinic, 0) + t1.saldofin)) / 2) / (t1.consumo / DATEDIFF(day, @dataInicio, @dataFinal)), 0)
    END
FROM 
(
    SELECT
        a1.itemcode,
        a1.ItemName,
        a1.InvntryUom,
         /*p1.AvgPrice,*/
        /*s1.Price,*/
        saldoInic = ISNULL(SUM(COALESCE(s1.Inqty, 0)) - SUM(COALESCE(s1.outqty, 0)), 0)
    FROM OITM a1
    JOIN OINM s1 ON a1.itemcode = s1.itemcode
   /*JOIN OITW p1 ON a1.[ItemCode] = p1.[ItemCode]*/
    WHERE s1.docdate < @dataInicio
    GROUP BY
        a1.itemcode,
        a1.ItemName,
        a1.InvntryUom
/*p1.AvgPrice */
      /*s1.Price*/
)       
AS t0
INNER JOIN (
    SELECT
        a.itemcode,
       /*p.AvgPrice, */
       /*s.Price,*/
        a.ItemName,
        a.InvntryUom,
        entradas = ISNULL(SUM(s.Inqty), 0),
        consumo = ISNULL(SUM(s.outqty), 0),
        saldoFin = ISNULL(SUM(s.Inqty) - SUM(s.outqty), 0)
         
    FROM OINM s
    JOIN OITM a ON a.itemcode = s.itemcode
  /*JOIN OITW p ON a.[ItemCode] = p.[ItemCode]*/
    WHERE s.docdate BETWEEN @dataInicio AND @dataFinal
    GROUP BY
        a.itemcode,
        a.ItemName,
        a.InvntryUom
     /* p.AvgPrice */
        /*s.Price*/
) t1 ON t1.itemcode = t0.itemcode

ppierce · 25 Septiembre, 2024 16:13

@Benru encontre esta consulta en foro me trate todo lo que ando buscando pero al colocarle la colunma .AvgPrice con la union de esta forma JOIN OITW p1 ON a1.[ItemCode] = p1.[ItemCode] me distorciona o me arroja resultado erroners no se que estoy haciendo mal. si puidera revisarlas me pueda ayudar con el error te lo agradeceria.

Saludo

csapBot · 13 Octubre, 2024 13:56

Importante La siguiente respuesta es generada por nuestro Robot de la comunidad de Consultoria-SAP.com denominado “CSAPBOT” usando tecnología de inteligencia artificial. Por lo tanto, puede haber datos incorrectos. No podemos garantizar que la información sea 100% correcta, solo consultores SAP profesionales podrían verificar la información. Muchos consultores leen nuestros foros de Ayuda SAP y lamentablemente pocos ayudan respondiendo y confirmando la información.

Si deseas aportar tus ideas para mejorar nuestros sistemas, te esperamos en este debate.

¡Hola Erick!

El problema que mencionas es bastante común cuando se intenta unir varias tablas en una consulta compleja en SAP Business One, especialmente cuando estás incluyendo cálculos de inventario y costos. En este caso, al intentar traer el precio promedio (AvgPrice) de la tabla OITW, estás generando uniones que pueden distorsionar el resultado, debido a la forma en que se gestionan los precios por almacén.

Te recomendaría hacer algunos ajustes para evitar la distorsión de datos y obtener el AvgPrice correctamente. Aquí tienes algunas sugerencias:

1. Evita la Distorsión en la Unión

El problema principal ocurre debido a cómo se están generando los JOIN con la tabla OITW. Al tener múltiples registros de precios por almacén, puedes estar duplicando las filas de tus artículos, lo cual distorsiona los resultados agregados de entradas, consumos y saldos.

Para evitar esto, deberías traer el costo promedio en una subconsulta que se agregue a la estructura principal sin interferir con las demás uniones de inventario.

2. Revisar el Uso de Fechas

En el código original, estás utilizando la tabla OINM, que contiene transacciones de inventario. Sin embargo, para obtener el costo promedio correctamente en la fecha deseada, podrías necesitar un enfoque más claro para asegurarte de que la fecha de inventario y la fecha de costo coincidan.

Propuesta de Ajuste

A continuación te muestro una versión ajustada de tu consulta para evitar la distorsión de datos al obtener el costo promedio (AvgPrice):

DECLARE @dataInicio DATETIME
DECLARE @dataFinal DATETIME

SET @dataInicio = [%0]
SET @dataFinal = [%1]

SELECT
    t1.itemcode AS [Artículo],
    t1.ItemName AS [Desc. Artículo],
    t1.InvntryUom AS [Unidad de Medida],
    ISNULL(t0.saldoinic, 0) AS [Stock inicial],
    ISNULL(t1.entradas, 0) AS [Entradas],
    ISNULL(t1.consumo, 0) AS [Consumo],
    ISNULL(t1.consumo / DATEDIFF(day, @dataInicio, @dataFinal), 0) AS [Consumo medio diario],
    ISNULL(t0.AvgPrice, 0) AS [Costo promedio],
    ISNULL(t0.saldoinic + t1.saldofin, 0) AS [Stock final],
    ISNULL((t0.saldoinic + (t0.saldoinic + t1.saldofin)) / 2, 0) AS [Stock promedio],
    CASE
        WHEN ISNULL((t0.saldoinic + (t0.saldoinic + t1.saldofin)) / 2, 0) = 0 THEN 0
        ELSE ISNULL(t1.consumo / ((t0.saldoinic + (t0.saldoinic + t1.saldofin)) / 2), 0)
    END AS [Rotación stock],
    CASE
        WHEN ISNULL(t1.consumo / DATEDIFF(day, @dataInicio, @dataFinal), 0) = 0 THEN 0
        ELSE ISNULL(((t0.saldoinic + (t0.saldoinic + t1.saldofin)) / 2) / (t1.consumo / DATEDIFF(day, @dataInicio, @dataFinal)), 0)
    END AS [Cobertura de stock]
FROM (
    SELECT
        a1.itemcode,
        a1.ItemName,
        a1.InvntryUom,
        saldoInic = ISNULL(SUM(COALESCE(s1.Inqty, 0) - COALESCE(s1.Outqty, 0)), 0),
        AvgPrice = (SELECT TOP 1 OITW.AvgPrice
                    FROM OITW
                    WHERE OITW.ItemCode = a1.ItemCode
                    AND OITW.WhsCode = s1.WhsCode
                    ORDER BY OITW.CreateDate DESC)
    FROM OITM a1
    JOIN OINM s1 ON a1.itemcode = s1.itemcode
    WHERE s1.DocDate < @dataInicio
    GROUP BY a1.itemcode, a1.ItemName, a1.InvntryUom, s1.WhsCode
) AS t0
INNER JOIN (
    SELECT
        a.itemcode,
        a.ItemName,
        a.InvntryUom,
        SUM(s.Inqty) AS entradas,
        SUM(s.Outqty) AS consumo,
        SUM(s.Inqty - s.Outqty) AS saldoFin
    FROM OINM s
    JOIN OITM a ON a.itemcode = s.itemcode
    WHERE s.DocDate BETWEEN @dataInicio AND @dataFinal
    GROUP BY a.itemcode, a.ItemName, a.InvntryUom
) AS t1 ON t1.itemcode = t0.itemcode

Explicación de Cambios:

Costo Promedio (AvgPrice): Ahora se obtiene a través de una subconsulta dentro de la primera selección (t0), lo que asegura que el costo promedio se obtenga para el último precio registrado antes de la fecha de inicio. Evitamos así duplicaciones en la unión.
Fechas y Saldos: La lógica de cálculo de entradas, salidas y saldos permanece intacta. La tabla OINM se sigue utilizando para registrar movimientos de inventario, pero se ha ajustado para evitar distorsiones con la tabla de costos OITW.

Advertencia Importante:

ADVERTENCIA: Antes de ejecutar consultas que manipulen directamente los datos en la base de datos de SAP Business One, asegúrate de probar las consultas en un entorno de pruebas. Un error en las consultas SQL podría afectar el sistema y comprometer la integridad de los datos.

Resumen Consultoria-SAP: Con este ajuste en la consulta SQL, deberías poder obtener el inventario con su costo promedio a una fecha determinada sin distorsiones. La clave ha sido manejar correctamente la unión con la tabla de costos y separar las lógicas en subconsultas para evitar duplicidades. ¡No dudes en probar esta nueva versión y adaptar las condiciones según tus necesidades!

¡Estamos aquí para ayudarte en Consultoria-SAP.com!