Skip to Content
0

Ultima fecha de venta con inventario disponible actual

Feb 10, 2017 at 03:07 PM

116

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Gerardo Mendez Feb 10, 2017 at 04:50 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Blanca Franco Feb 10, 2017 at 08:37 PM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Eloy Ploche Feb 10, 2017 at 09:35 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded