Estimados buenas tardes. Tengo una consulta Tengo esta query:
DECLARE @FECHAINICIAL DATE
DECLARE @FECHAFINAL DATE
SET @FECHAINICIAL = '2010-01-01'
SET @FECHAFINAL = '2025-07-07'
SELECT
T0.ItemCode AS [Codigo de articulo],
T0.ItemName AS [Nombre Material],
T0.DistNumber AS [Numero de Lote],
T0.InDate AS [Fecha de ingreso],
(
SELECT
SUM(CASE
WHEN T1.Direction = 0 THEN T1.Quantity -- Ingreso
WHEN T1.Direction = 1 THEN -T1.Quantity -- Egreso
ELSE 0
END)
FROM IBT1 T1
WHERE
T1.ItemCode = T0.ItemCode
AND T1.BatchNum = T0.DistNumber
AND T1.WhsCode = '01'
AND T1.CreateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL
) AS [Stock a consulta],
(
SELECT TOP 1 T2.CalcPrice
FROM OINM T2
WHERE T2.ItemCode = T0.ITEMcODE
and t2.Warehouse = '01' AND T2.CreateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL
ORDER BY T2.DOCDATE DESC
) AS [C.U a consulta],
(select T4.[Rate]
from ORTT T4 where T4.[RateDate]=T0.[InDate] and T4.[Currency]='USD'
AND T4.RateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL) as [Tipo de cambio F.I],
(SELECT TOP 1 T2.CalcPrice
FROM OINM T2
WHERE T2.ItemCode = T0.ITEMcODE
and t2.Warehouse = '01' AND T2.CreateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL
ORDER BY T2.DOCDATE DESC)/(select T4.[Rate] from ORTT T4 where T4.[RateDate]=T0.[InDate] and T4.[Currency]='USD'
and T4.RateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL) as 'Costo Unit. USD',
((SELECT TOP 1 T2.CalcPrice
FROM OINM T2
WHERE T2.ItemCode = T0.ITEMcODE
and t2.Warehouse = '01' AND T2.CreateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL
ORDER BY T2.DOCDATE DESC)/(select T4.[Rate] from ORTT T4 where T4.[RateDate]=T0.[InDate] and T4.[Currency]='USD'
and T4.RateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL))*(
SELECT
SUM(CASE
WHEN T1.Direction = 0 THEN T1.Quantity -- Ingreso
WHEN T1.Direction = 1 THEN -T1.Quantity -- Egreso
ELSE 0
END)
FROM IBT1 T1
WHERE
T1.ItemCode = T0.ItemCode
AND T1.BatchNum = T0.DistNumber
AND T1.WhsCode = '01'
AND T1.CreateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL
) as [Costo Total USD],
(
SELECT TOP 1
T1.CardName
FROM IBT1 T1
WHERE
T1.ItemCode = T0.ItemCode
AND T1.BatchNum = T0.DistNumber
AND T1.WhsCode = '99'
AND T1.Direction = '0'
AND T1.CreateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL
) AS [Proveedor],
(SELECT STUFF((
SELECT DISTINCT ', ' + T5.CardName
FROM OPOR T5
INNER JOIN POR1 T6 ON T5.DocEntry = T6.DocEntry
WHERE T6.ItemCode = T0.ItemCode
AND T5.DocDate BETWEEN @FECHAINICIAL AND T0.InDate
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) AS [Proveedores historicos]
FROM OBTN T0
WHERE (
SELECT
SUM(CASE
WHEN T1.Direction = 0 THEN T1.Quantity -- Ingreso
WHEN T1.Direction = 1 THEN -T1.Quantity -- Egreso
ELSE 0
END)
FROM IBT1 T1
WHERE
T1.ItemCode = T0.ItemCode
AND T1.BatchNum = T0.DistNumber
AND T1.WhsCode = '01'
AND T1.CreateDate BETWEEN @FECHAINICIAL AND @FECHAFINAL) > 0
AND T0.DistNumber = '702501-5337-01'í
Pues el objetivo es mostrar el precio unitario de artículos con lotes a una fecha que yo consulte que puede ser pasada a la fecha de hoy . Mi duda es si saben como el SAP calcula el precio de un articulo. Me explico.
Según esta Query opto por juntar el valor de CalcPrice de la tabla OINM que calcula los movimientos que tuvo el articulo. Hago la prueba por ejemplo al 07/07/2025 y me sale este resultado.
En una base pasada a la fecha del 15/07/2025
Ejecuto una query simple de
SELECT distinct
T0.[ItemCode], T2.[ItemName], T0.[DistNumber],
T0.[InDate], T1.[Quantity],T3.[OnHand], T3.[AvgPrice] as 'Costo Unitario',
(select T4.[Rate] from ORTT T4 where T4.[RateDate]=T0.[InDate] and T4.[Currency]='USD') as 'TC_USD',
T3.[AvgPrice]/(select T4.[Rate] from ORTT T4 where T4.[RateDate]=T0.[InDate] and T4.[Currency]='USD') as 'Costo Unit. USD',
(T3.[AvgPrice]/(select T4.[Rate] from ORTT T4 where T4.[RateDate]=T0.[InDate] and T4.[Currency]='USD'))*T1.[Quantity] as 'Costo Total USD',
(select T5.[CardName] from OCRD T5 where T5.[CardCode]=T2.[CardCode]) as Proveedor
FROM OBTN T0
--INNER JOIN OBTQ T1 ON T0.[AbsEntry] = T1.[SysNumber]
INNER JOIN OBTQ T1 ON T0.[SysNumber] = T1.[SysNumber] and T0.[ItemCode]=T1.[ItemCode]
INNER JOIN OITM T2 ON T0.[ItemCode] = T2.[ItemCode]
INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode] and T3.[WhsCode]=T1.[WhsCode]
WHERE T3.[WhsCode] ='01' and T1.[Quantity] >0 and
T2.[ItmsGrpCod]='102' and
--T0.[ItemCode] ='62103033' and
T0.[InDate] between [%0] and [%1]
Y sale que el precio unitario es otro
Y revisando el historial de movimientos en la OINM toma el valor siguiente a la fecha que delimita mi @fechafinal
Alguien sabe como calcular ese precio? que cuando hago una consulta directa el valor es diferente?
Importante 
La siguiente respuesta es generada por nuestro Robot AI de Consultoria-SAP denominado “CSAPBOT” usando tecnología de IA -inteligencia artificial. Por lo tanto, puede haber datos incorrectos. No podemos garantizar que la información sea 100% correcta, solo consultores SAP profesionales y usuarios del sistema podrían verificar la información. ¿Te animas a hacerlo? ¿Aceptas el reto? Puedes responder este debate si sigue abierto!