cancel
Showing results for 
Search instead for 
Did you mean: 

Current Stock status and SKU SOLD during specific date

shahmed
Participant
0 Kudos

Hi Experts. I have this following query working to see the current stocks in SAP and quantity sold, say last one year. the problem i have here is, if there is no invoice for a particular SKU during given timeframe then this report doesn't fetch the data of that SKU. I tried even ISNULL to fetch that but certainly missing something. please advise.

here is the query:

Select A.[ItemCode], A.[ItemName] [itemDescription], A.[OnHand] [In Stock], A.[IsCommited], A.[OnOrder] [Ordered], ((A.[OnHand] - A.[IsCommited]) + A.OnOrder) [Available], SUM(A.Quantity) [Quantity Sold], A.[Price]

From(

SELECT

T2.[ItemCode], T2.[ItemName], isnull(T4.[OnHand],0) as 'OnHand', T4.[IsCommited], T4.[OnOrder],

ISNULL(SUM(T1.[Quantity]),0) as 'Quantity', T3.[Price]

FROM

OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN ITM1 T3 ON T2.[ItemCode] = T3.[ItemCode] INNER JOIN OITW T4 ON T2.[ItemCode] = T4.[ItemCode] INNER JOIN OMRC T5 ON T2.[FirmCode] = T5.[FirmCode]

WHERE

T0.[CANCELED] = 'N' and

T0.[DocDate] >=[%0] and

T0.[DocDate] <=[%1] and

T5.[FirmName] = 'ABC' and

T3.[PriceList] = '2' and

T4.[WhsCode] = '100'

Group by

T2.[ItemCode], T2.[ItemName], T4.[OnHand],T4.[IsCommited], T4.[OnOrder], T3.[Price]

Union all

SELECT

T2.[ItemCode], T2.[ItemName], isnull(T4.[OnHand],0) as 'OnHand', T4.[IsCommited], T4.[OnOrder], ISNULL(SUM(T1.[Quantity])*-1,0) as 'Quantity', T3.[Price]

FROM

ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN ITM1 T3 ON T2.[ItemCode] = T3.[ItemCode] INNER JOIN OITW T4 ON T2.[ItemCode] = T4.[ItemCode] INNER JOIN OMRC T5 ON T2.[FirmCode] = T5.[FirmCode]

WHERE

T0.[CANCELED] = 'N' and

T0.[DocDate] >=[%0] and

T0.[DocDate] <=[%1] and

T5.[FirmName] = 'ABC' and

T3.[PriceList] = '2' and

T4.[WhsCode] = '100'

Group by

T2.[ItemCode],

T2.[ItemName], T4.[OnHand], T4.[IsCommited], T4.[OnOrder], T3.[Price] )A

Group by

A.[ItemCode], A.[ItemName], A.[OnHand], A.[IsCommited], A.[OnOrder], A.[Price]

Accepted Solutions (1)

Accepted Solutions (1)

LoHa
Active Contributor

Hi Shahzad,

I don't really know, why this happens.

Let's try something new

/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom as datetime
/* WHERE */
Set @DocDateFrom = /* T0.F_RefDate */ '[%0]'

/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateTo as datetime
/* WHERE */
Set @DocDateTo = /* T0.T_RefDate */ '[%1]'

SELECT 
	 [OITM].[ItemCode]
	,[OITM].[ItemName]
	,ISNULL([OITW].[OnHand],0) AS [OnHand]
	,ISNULL([OITW].[IsCommited],0) AS [IsCommited]
	,ISNULL([OITW].[OnOrder],0) AS [OnOrder]
	,(ISNULL([OITW].[OnHand],0) - ISNULL([OITW].[IsCommited],0) + ISNULL([OITW].[OnOrder],0)) AS [Available]
	,(
	  SELECT 
		ISNULL(SUM([INV1].[Quantity]),0)
	  FROM
		[INV1]
			INNER JOIN [OINV] ON [OINV].[DocEntry] =[INV1].[DocEntry] AND [OINV].[CANCELED] = 'N' AND [OINV].[DocDate] between @DocDateFrom AND @DocDateTo
	  WHERE 
		[INV1].[ItemCode] = [OITM].[ItemCode]
	 ) 
	 -
	 (
	  SELECT 
		ISNULL(SUM([RIN1].[Quantity]),0)
	  FROM
		[RIN1]
			INNER JOIN [ORIN] ON [ORIN].[DocEntry] = [RIN1].[DocEntry] AND [ORIN].[CANCELED] = 'N' AND [ORIN].[DocDate] between @DocDateFrom AND @DocDateTo
	  WHERE 
		[RIN1].[ItemCode] = [OITM].[ItemCode]
	 )
	 AS [SumSold]
	,[ITM1].[Price]
FROM
	[OITM]
		LEFT JOIN [ITM1] ON [ITM1].[ItemCode] = [OITM].[ItemCode] AND [ITM1].[PriceList] = 2
		LEFT JOIN [OITW] ON [OITW].[ItemCode] = [OITM].[ItemCode] AND [OITW].[WhsCode] = '100'
		LEFT JOIN [OMRC] ON [OMRC].[FirmCode] = [OITM].[FirmCode]

WHERE 
	[OMRC].[FirmName] = 'ABC'

regards Lothar

shahmed
Participant
0 Kudos

Thank you Lothar Once again. can you advise if i need to make FirmName as filter like the date filter in above query.?

LoHa
Active Contributor

It is always the same like in the Query

/**SELECT FROM [OMRC] T3 **/
DECLARE @FirmCode as smallint
/* WHERE */
Set @FirmCode = /* T3.FirmCode*/ '[%3]'

SELECT @FirmCode

regards Lothar

Answers (2)

Answers (2)

LoHa
Active Contributor

Hi Shahzad,

try this

SELECT 
	 [OITM].[ItemCode]
	,[OITM].[ItemName]
	,ISNULL([OITW].[OnHand],0) AS [OnHand]
	,ISNULL([OITW].[IsCommited],0) AS [IsCommited]
	,ISNULL([OITW].[OnOrder],0) AS [OnOrder]
	,(ISNULL([OITW].[OnHand],0) - ISNULL([OITW].[IsCommited],0) + ISNULL([OITW].[OnOrder],0)) AS [Available]
	,(
	  SELECT 
		ISNULL(SUM([INV1].[Quantity]),0)
	  FROM
		[INV1]
			INNER JOIN [OINV] ON [OINV].[DocEntry] =[INV1].[DocEntry] AND [OINV].[CANCELED] = 'N' AND [OINV].[DocDate] between '[%0]' AND '[%1]'
	  WHERE 
		[INV1].[ItemCode] = [OITM].[ItemCode]
	 ) 
	 -
	 (
	  SELECT 
		ISNULL(SUM([RIN1].[Quantity]),0)
	  FROM
		[RIN1]
			INNER JOIN [ORIN] ON [ORIN].[DocEntry] = [RIN1].[DocEntry] AND [ORIN].[CANCELED] = 'N' AND [ORIN].[DocDate] between '[%0]' AND '[%1]'
	  WHERE 
		[RIN1].[ItemCode] = [OITM].[ItemCode]
	 )
	 AS [SumSold]
	,[ITM1].[Price]
FROM
	[OITM]
		LEFT JOIN [ITM1] ON [ITM1].[ItemCode] = [OITM].[ItemCode] AND [ITM1].[PriceList] = 2
		LEFT JOIN [OITW] ON [OITW].[ItemCode] = [OITM].[ItemCode] AND [OITW].[WhsCode] = '100'
		LEFT JOIN [OMRC] ON [OMRC].[FirmCode] = [OITM].[FirmCode]

WHERE 
	[OMRC].[FirmName] = 'ABC'<br>

regards

Lothar

shahmed
Participant
0 Kudos

Thank you, Lothar.

but i am getting this error message once i run the above query.

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near >

LoHa
Active Contributor
0 Kudos

Hi Shahzad,

at the end of the query is a <br> did you copy it too?

Please check if I used all your parameters right, like Whs / Firmname

regards Lothar

shahmed
Participant
0 Kudos

yes, I added that br although i am not familier with that.

and the rest of he codes looks fine to me. not sure why i am getting this error.

LoHa
Active Contributor
0 Kudos

You have to delete the <br> it is because of copy and paste in the editor window. I forgot to write it clearly

shahmed
Participant
0 Kudos

even i delete the br thing, it still gives me the same error. 😞

shahmed
Participant
0 Kudos

very interesting, I used the above query on three different servers and each time it give me different error.

1- ''(ITW1)

2 -[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near select

3- [Microsoft][ODBC Driver 13 for SQL Server][SQL Server] invalid column name OnOrder.

shahmed
Participant
0 Kudos

and now ''user-defined value (CSHS) error.

😞

shahmed
Participant
0 Kudos

Amazing Lother. this one worked.

mgregur
Active Contributor
0 Kudos

Hi,

your query will not work since you are starting from Invoices / Credit Notes. As this is the starting table, any item which is not present on them will not be shown. You need to have OITM as starting table, and then left join from OITM to INV1 / RIN1 (inner join will also remove any items which are not present on the documents).

Lothar gave you a good solution on this, using subselect for getting INV / RIN data into your select, I just wanted to clarify what is wrong so that you learn for next time.

BR,

Matija

shahmed
Participant
0 Kudos

Thank you Matija.

i tried following the steps you reffered above. but i am still missing the those SKU for which i have stocks but there is no invoice history. here is the revised code:

SELECT A.[ItemCode], A.[ItemName], A.[OnHand], A.[IsCommited], A.[OnOrder], isnull(SUM(A.[Quantity]),0) as 'Quantity', A.[Price]

FROM (

SELECT

T0.[ItemCode], T0.[ItemName], isnull(T5.[OnHand],0) [OnHand], isnull(T5.[IsCommited],0) [IsCommited], isnull(T5.[OnOrder],0) [OnOrder], isnull(SUM(T2.[Quantity]),0) as 'Quantity', T1.[Price]

FROM

OITM T0 LEFT JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode] LEFT JOIN INV1 T2 ON T0.[ItemCode] = T2.[ItemCode] LEFT JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] LEFT JOIN OMRC T4 ON T0.[FirmCode] = T4.[FirmCode] LEFT JOIN OITW T5 ON T0.[ItemCode] = T5.[ItemCode]

WHERE

T4.[FirmName] = 'ABC' and T5.[WhsCode] = '100' and T1.[PriceList] = '2' and T3.[CANCELED] = 'N'

GROUP BY T0.[ItemCode], T0.[ItemName], T5.[OnHand], T5.[IsCommited], T5.[OnOrder], T1.[Price]

Union ALL

SELECT

T0.[ItemCode], T0.[ItemName], isnull(T5.[OnHand],0) [OnHand], isnull(T5.[IsCommited],0) [IsCommited], isnull(T5.[OnOrder],0) [OnOrder], isnull(SUM(T2.[Quantity])*-1,0) as 'Quantity', T1.[Price]

FROM

OITM T0 LEFT JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode] LEFT JOIN RIN1 T2 ON T0.[ItemCode] = T2.[ItemCode] LEFT JOIN ORIN T3 ON T2.[DocEntry] = T3.[DocEntry] LEFT JOIN OMRC T4 ON T0.[FirmCode] = T4.[FirmCode] LEFT JOIN OITW T5 ON T0.[ItemCode] = T5.[ItemCode]

WHERE

T4.[FirmName] = 'ABC' and T5.[WhsCode] = '100' and T1.[PriceList] = '2' and T3.[CANCELED] = 'N'

GROUP BY T0.[ItemCode], T0.[ItemName], T5.[OnHand], T5.[IsCommited], T5.[OnOrder], T1.[Price] ) A

GROUP BY

A.[ItemCode], A.[ItemName], A.[OnHand], A.[IsCommited], A.[OnOrder], A.[Price]

mgregur
Active Contributor
0 Kudos

Hi,

the moment you set a condition like this:

and T3.[CANCELED] = 'N'

you are forcing a field from OINV table, ignoring all records that have no link to this table. This in return removes all items which are not on Invoices, as they cannot fulfill this condition.

BR,

Matija

shahmed
Participant
0 Kudos

Thank you Matija. but this didn't work. 😞