Change Log For BoM- SAP Business One

[joseph_antony]

Hello,

I'm trying to create a report for the change in quantity made on a BoM.

SELECT
T0.Code,
T1.Code,
T1.Quantity 'Current Qty',
T2.Quantity 'Old Qty',
T1.LogInstanc,
T2.LogInstanc,
T0.UpdateDate,
T3.USER_CODE 'Updated By'
FROM OITT T0
INNER JOIN ITT1 T1 ON T0.Code = T1.Father
INNER JOIN ATT1 T2 ON T2.Code = T1.Code
INNER JOIN OUSR T3 ON T3.USERID = T0.UserSign2
WHERE
T0.Code = '10596860'AND
DATEDIFF(DD,T0.UpdateDate, GETDATE()) <=1
AND T1.Quantity <> T2.Quantity

This report gives me multiple lines (I can see based on the T2.loginstance) and I would like to see only the latest change to the quantity made.

Also, how to get the details of components added or removed from the ITT1 Table?

Any assistance would be appreciated.

Regards,

Joseph

[LoHa · Accepted solution]

Hi Joseph,

please try this

DECLARE @Father AS NVARCHAR(max) = '10596860'

SELECT
T1.Father,
T1.Code,
T1.Quantity 'Current Qty',
T0.UpdateDate,
T3.USER_CODE 'Updated By',
Hist.*
FROM 
OITT T0
INNER JOIN ITT1 T1 ON T0.Code = T1.Father
OUTER APPLY
	(SELECT 
		Father
		,Code
		,Quantity
		,LogInstanc 
		FROM ATT1 
		WHERE LogInstanc =	(
							SELECT 
							Max(T99.LogInstanc) 
							FROM ATT1 T99 
							WHERE 
							T99.Quantity <> T1.Quantity AND T99.Code = T1.Code AND T99.Father = T1.Father AND T99.ChildNum = T1.ChildNum
							) 
		AND Code = T1.Code AND Father = T1.Father AND ChildNum = T1.ChildNum) Hist

INNER JOIN OUSR T3 ON T3.USERID = T0.UserSign2
WHERE
T1.Father = @Father
AND
DATEDIFF(DD,T0.UpdateDate, GETDATE()) <=1
AND T1.Quantity <> Hist.Quantity

regards Lothar

[joseph_antony · Accepted solution]

Thanks Lothar.

[joseph_antony · Accepted solution]

SELECT DISTINCT T0.Code 'Parent', T1.Code 'Component', T1.Quantity 'Current Qty', T2.Quantity 'Old Qty', T3.USER_CODE 'Updated By' FROM OITT T0 INNER JOIN ITT1 T1 ON T0.Code = T1.Father LEFT JOIN ATT1 T2 ON T2.Code = T1.Code AND T2.Father = T1.Father AND T2.ChildNum = T1.ChildNum And T2.LogInstanc = (SELECT Max(H.LogInstanc) FROM ATT1 H WHERE H.Quantity <> T1.Quantity AND H.Code = T1.Code AND H.Father = T1.Father AND H.ChildNum = T1.ChildNum) INNER JOIN OUSR T3 ON T3.USERID = T0.UserSign2 WHERE DATEDIFF(DD,T0.UpdateDate, GETDATE()) <=7 AND T1.Quantity <> T2.Quantity AND T1.[Type]<>290 --AND T1.Father = '10117392'