Skip to Content
avatar image
Former Member

Ultima fecha de venta con inventario disponible actual

Hola compañeros de SAP, actualmente estoy intentando crear un query en SAP Business One para saber la ultima fecha de venta de los articulos de stock junto con saber el inventario disponible, el almacén,el grupo de articulos y el costo del material, pero al programar solo me trae la penultima fecha y lo que estoy buscando es precisamente la ultima. Por favor si me pueden ayudar...

Este es la programacion que le estoy dando:

SELECT T0.ItemCode, T0.Dscription, T0.OnHand AS 'Stock Total', T0.AvgPrice AS 'ValPromGen',

T1.WhsCode, T1.AvgPrice as 'ValPromAlm', T1.OnHand, T0.[Ult.Fecha]

FROM (

SELECT T10.ItemCode, T10.Dscription, T11.OnHand, T11.AvgPrice,

'Ult.Fecha'=(SELECT TOP 1 DocDate FROM OINM WHERE TransType = 13

AND ItemCode = T10.ItemCode ORDER BY DocDate DESC)

FROM INV1 T10 INNER JOIN OITM T11 ON T10.ItemCode = T11.ItemCode

WHERE T11.Onhand > 0

GROUP BY T10.ItemCode, T10.Dscription, T11.OnHand, T11.AvgPrice

) T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode AND T1.OnHand > 0

WHERE T0.[Ult.Fecha] NOT BETWEEN '20100101' AND '20100419'

ORDER BY T0.ItemCode

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Feb 10, 2017 at 04:50 PM

    Hola compañera.

    Disculpa, no cuento con mucho tiempo para analizar la consulta pero checa si puedes sacar alguna idea de este query que he realizado de manera rápida:

    SELECT TX.ItemCode,  TX.DocDate, (Select T0.OnHand FROM OITM T0 WHERE T0.ItemCode = TX.ItemCode) as Stock FROM INV1 TX Where TX.DocDate = (select MAX(DocDate) FROM INV1 T0 Where T0.ItemCode = Tx.ItemCode) GROUP BY TX.ItemCode,  TX.DocDate
    

    Saludos, quedo pendiente.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 10, 2017 at 08:37 PM

    Estimado Gerardo, me funcionó perfectamente.... Muchas Gracias

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 10, 2017 at 09:35 PM

    Hola Bianca,

    Suponiendo que trabajas con costos distintos por almacen, creo que esto te puede ayudar.

    De ser asi elimina de la consulta la linea T0.[AvgPrice]

    SELECT distinct
    T0.[ItemCode],
    T3.[ItmsGrpNam],
    T1.[WhsCode],
    T0.[AvgPrice],
    ((select OITW.[OnHand]-OITW.[IsCommited]+OITW.[OnOrder] from OITW Where T0.[ItemCode] =OITW. [ItemCode] and T1.[WhsCode]=OITW.[WhsCode])) as Disponible,
    ((select OITW.[AvgPrice] from OITW Where T0.[ItemCode]=OITW.[ItemCode] and T1.[WhsCode]=OITW.[WhsCode] )) as Costo,
    (( select top 1 inv1.[DocDate] from inv1 where inv1.[ItemCode]=T0.[ItemCode] and inv1.WhsCode=T1.[WhsCode] order by inv1.[DocDate] desc)) as Ultima_Fecha
    FROM
    OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN INV1 T2 ON T0.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T3 ON T0.[ItmsGrpCod] = T3.[ItmsGrpCod]
    WHERE T1.[OnHand] >0
    ORDER BY 1,3

    Saludos

    Eloy

    Add comment
    10|10000 characters needed characters exceeded