Skip to Content
0

Help With BOM Query

Dec 07, 2016 at 02:31 AM

97

avatar image
Rahul Fern

Hello Experts

I have written a query, doesn't do what I need it to do

So need some help fixing it

When the query is run, what I need the query to display is

Stock Code,

Description,

Date the Stock Code was created.

User name of the person who created the BOM,

Date the stock code was last modified,

Name of the person who last modified it

SELECT 
T0.[Code] as 'Stock Code', 
T0.[U_BOM_Desc], 
T0.[CreateDate] as 'Date BOM Created', 
T1.[U_NAME] as 'BOM Created By', 
T0.[UpdateDate] as 'Date BOM Updated', 
T0.[UserSign2] as 'BOM Updated By' 
FROM OITT T0  INNER
JOIN OUSR T1 ON T0.[UserSign2] = T1.[USERID] 
WHERE 
T0.[CreateDate] =[%0] AND 
T0.[CreateDate] =[%1] 
GROUP BY 
T0.[Code], 
T0.[U_BOM_Desc], 
T0.[CreateDate], 
T1.[U_NAME], 
T0.[UpdateDate], 
T0.[UserSign2]

Can someone please help and guide me and tell me where i am going wrong

Thank You

Regards

Rahul

SAP Business One
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Sort by:
Best Answer
avatar image
DIEGO LOTHER Answered Dec 09, 2016 at 11:50 PM
1

Hi Rahul,

This should do the work.

SELECT 
	T0.Code AS 'Stock Code',
	T1.ItemName AS 'BOM Desc', 
	T0.CreateDate as 'Date BOM Created', 
	T2.U_NAME as 'BOM Created By', 
	T0.UpdateDate as 'Date BOM Updated', 
	T3.U_NAME as 'BOM Updated By' 
FROM 
	OITT T0 
	INNER JOIN OITM T1 ON T1.ItemCode = T0.Code
	INNER JOIN OUSR T2 ON T2.USERID = T0.UserSign
	LEFT JOIN OUSR T3 ON T3.USERID = T0.UserSign2
WHERE 
  T0.CreateDate BETWEEN [%0] AND [%1]

Kind Regards,

Diego Lother

Show 1 Share
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
Rahul Fern
Dec 12, 2016 at 05:09 AM

Hi Diego

Thanks for that. It works just how I wanted

Now I need to decifer how you did it, so I can learn and wont have to ask next time

Thanks a lot

Much appreciated

Regards

Rahul

0
Comment
avatar image
Nagarajan K Answered Dec 07, 2016 at 09:07 AM
0

Hi,

Have you tried with standard "Change log" function for history?

Let me know still you need query.

Thanks

Share
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
avatar image
Rahul Fern Answered Dec 08, 2016 at 03:32 AM
0

Hi Nagarajan

The standard Change log function can check only one stock code at a time

With the query

I can find out by date range. How many stock codes were created? Created by whom? How many were modified? Modified by whom?

Yes please I need the query

Thanks

Share
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users