Goods Issue Vs Goods Receipt

[Former Member]

Hi All,

I have the following Querry, i want to see how much of items were received and how much of items were issued. the items and quantities keep repeating.

SELECT T0.[ItemCode], T0.[Dscription], T0.[Quantity], T1.[ItemCode], T2.[ItemName], T1.[IssuedQty], T2.[BWeight1] FROM IGN1 T0 , WOR1 T1 INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode WHERE T0.[ItemCode] Like 'fs%%' and  T1.[ItemCode] Like '%%db%%' and  T0.[DocDate] >= '[%0]' and  T0.[DocDate] <= '[%1]'

Any help would be appreciated.

Thanks

[Former Member · Accepted solution]

Hi Evans

You are joining to the IGN1 table which is not only used for production receipts but also for all other Goods Receipts under Inventory Transactions. If you only want the Productions Receipts, then include T0.BaseType = 202.

You could of course also get the same information using the view OINM.This view has an Inqty and OutQty column. Simply select the TransType's you need and if necessary join to the OITM table.

However, you do not mention production so I am not sure why you are looking at the WOR1 table, maybe explain in more detail exactly what you are trying to achieve and I can assist you better.

Kind regards

Peter Juby

[Former Member · Accepted solution]

Thanks Peter,

During production am issuing components and from goods receipt am receiving the scraps manually.

i need to make an analysis of the raw materials issued and the scraps received to get my scraps Percentage.

(scraps Received/Issued Components)*100

Thanks

[Former Member · Accepted solution]

Hi Evans

The problem is the relationship between the 'fs%%' item and the '%%db%%' item. You will not get them next to each other as the query stands. Can you give me an example of an item from each table (IGN1 and WOR1) that have a relationship so I can help you map them in the query somehow.

Kind regards

Peter Juby

[Former Member · Accepted solution]

Thanks Peter,

I used OINM and i managed to get it. However the Date selection is only working for T0.[OutQty], the T0.[InQty] figure is same all through.

Is there anything wrong with this querry.

SELECT T0.[ItemCode], T0.[Dscription], sum(T0.[InQty]) as 'Scraps Received', sum(T0.[OutQty]) as 'Blocks Issued' FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[ItemCode] like 'FS%%' and T0.[TransType] = '59' or  T0.[ItemCode] like '%%db%%' and  T0.[TransType] = '60' and  T0.[DocDate] >= '[%0]' and T0.[DocDate] <='[%1]' GROUP BY T0.[ItemCode], T0.[Dscription]

[Former Member · Accepted solution]

Hi Evans

Try this:

SELECT T0.[ItemCode], T0.[Dscription], sum(T0.[InQty]) as 'Scraps Received', sum(T0.[OutQty]) as 'Blocks Issued'

FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE (T0.[ItemCode] like 'FS%%' and T0.[TransType] = '59' and  T0.[DocDate] >= '[%0]' and T0.[DocDate] <='[%1]')

or (T0.[ItemCode] like '%%db%%' and  T0.[TransType] = '60' and  T0.[DocDate] >= '[%0]' and T0.[DocDate] <='[%1]')

GROUP BY T0.[ItemCode], T0.[Dscription]

Mixing OR and AND in the WHERE clause can be tricky.

Kind regards

Peter Juby

[Former Member · Accepted solution]

Thanks Very Much Peter.

That's what i wanted.