cancel
Showing results for 
Search instead for 
Did you mean: 

Non Invoiced Stock Query

0 Kudos

Hi, i am looking to create a query which prompts the user to select a warehouse code and a start and end date then display all the itemcodes in that warehouse with current available stock and against each item the qty of units invoiced for the time period. If the item has not been invoiced then the returned value can be null or 0.

Below is what i have created so far but it is not returning all item codes - just itemcodes added via goods receipt po for the same time period.

/* SELECT * FROM OITM Tx WHERE Tx.[DfltWH] = '[%0]' */

select * from (

SELECT T5.[ItmsGrpNam], T0.[ItemCode], convert(int,T4.[OnHand]) - convert(int,T4.[IsCommited]) as 'Stock',

ISNULL(SUM(T1.OutQty),0) [Issued Qty]

FROM [OITM] T0

LEFT JOIN OINM T1 ON T1.[ItemCode] = T0.[ItemCode] INNER JOIN OITW T4 ON T0.ItemCode = T4.ItemCode

INNER JOIN OITB T5 ON T0.ItmsGrpCod = T5.ItmsGrpCod

AND T1.TransType IN (13,20)

AND (T1.[DocDate] >=[%3] AND T1.[DocDate] <= [%4])

WHERE T0.[DfltWH] = '[%0]'

GROUP BY T5.[ItmsGrpNam],T0.[ItemCode], convert(int,T4.[OnHand]) - convert(int,T4.[IsCommited])

) AS X

WHERE [Issued Qty] = 0

Please can anyone help?

Many thanks

Alan

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Alan

Try this:

SELECT T5.[ItmsGrpNam], T0.[ItemCode], CONVERT(INT, T4.[OnHand]) - CONVERT(INT, T4.[IsCommited]) AS [Stock],

ISNULL((SELECT SUM(T1.[OutQty])

  FROM OINM T1

  WHERE T1.[ItemCode] = T0.[ItemCode] AND (T1.[DocDate] >='[%1]' AND T1.[DocDate] <= '[%2]')),0) AS [Issued Qty]

FROM OITM T0 INNER JOIN OITW T4 on T0.[ItemCode] = T4.[ItemCode] AND T4.[WhsCode] = '[%0]'

INNER JOIN OITB T5 ON T0.[ItmsGrpCod] = T5.[ItmsGrpCod]

WHERE T0.[InvntItem] = 'Y'

Kind regards

Peter Juby

0 Kudos

Hi Peter, thanks for this - works perfectly 😉

Former Member
0 Kudos

Hi Alan

Only a pleasure.

Kind regards

Peter Juby

0 Kudos

Hi Peter, is it possible to include the qty of times each ItemCode has had 0 in stock.. I can check this against a single itemcode in stock posting list and check down the balance for all the zeros...

Not sure if this information is retained in OIMN?

Kindest regards,

Alan

Former Member
0 Kudos

Hi Alan

OINM is a view in SQL. It used to be the Stock movement Table prior to 2007A if I recall correctly. There are 6 tables that replaced it for FIFO handling, etc.

However, the OINM structure can still be used effectively and is probably the quickest way. It is possible to get the balance or nett effect of the InQty and OutQty and determine the zero points.

Hope this helps.

Kind regards

Peter Juby

0 Kudos

Hi Peter, thanks for your reply. I have been working on a new query just to break this down and to show the stock postings InQty & OutQty for a selected itemcode and calculate a running balance but i can't calculate the balance at any point in time other than now...

Please can you provide some example code for me to work with?

Kindest regards,

Alan

Former Member
0 Kudos

Hi Alan

Try the below query to get the running balance for an item. Item A00004 is from the demo database:

SELECT T0.ItemCode, T0.Dscription, T0.DocDate, (T0.InQty-T0.OutQty) AS [Movement],

ISNULL((T0.InQty-T0.OutQty) + (SELECT SUM(T1.InQty-T1.OutQty) FROM OINM T1 WHERE T1.ItemCode = T0.ItemCode AND T1.TransNum < T0.TransNum),0) AS [Balance]

FROM OINM T0

WHERE T0.ItemCode = 'A00004' AND (T0.InQty-T0.OutQty) <> 0

ORDER BY T0.TransNum

There is a time stamp field that you can also use if you want to sort by time within a day, but the TransNum is sequential and should be ok to sort on.

Kind regards

Peter Juby

0 Kudos

Hi Peter, thanks for this - this is exactly what I am looking for.. I can work with this.

Kindest regards,

Alan

Former Member
0 Kudos

Hi Alan

Glad to hear that. Let me know if you have any other issues with the query.

Kind regards

Peter Juby

Answers (0)