Help to sqlquery

[Former Member]

Hi.

I need help to create a query to return de batch quantity.

The sap table IBT1 have the registers in/out of batches.

This table have the information i need, but is dificult to calculate, my question is:

I have de quantity of batchNum in other table? if no have the quantity in other table a need one query to calculate the batchNum quantity in IBT1

Thanks

[Former Member]

Hi,

to get the value from the OIBT just put a join to the table.

try this :

select

T2.DocNum,

T2.CardCode,

T0.BatchNum,

T0.WhsCode,

T1.ItemCode,

T1.Dscription,

T0.Quantity

from

OIBT T3

Inner join IBT1 T0 On T0.ItemCode=T3.ItemCode and T0.BatchNum=T3.BatchNum

inner join DLN1 T1 on T0.BaseEntry = T1.DocEntry and T0.BaseLinNum = T1.LineNum

inner join ODLN T2 on T1.DocEntry = T2.DocEntry

where

T1.ItemCode = 'ABCDEFG'

Kind Regards

Mohana

[Former Member]

Hi.

Thanks, but the information i need is present in the table OIBT on field quantity.

Thank you.

[Former Member]

hi,

try add this in the select.

select

T2.DocNum,

T2.CardCode,

T0.BatchNum,

T0.WhsCode,

T1.ItemCode,

T1.Dscription,

T3.Quantity

from

OIBT T3 INNER JOIN IBT1 T0

ON T0.BatchNum = T3.BatchNum

inner join DLN1 T1 on T0.BaseEntry = T1.DocEntry and T0.BaseLinNum = T1.LineNum and T0.BaseType = 15

inner join ODLN T2 on T1.DocEntry = T2.DocEntry

where

T1.ItemCode = 'ABCDEFG'

att,

Edited by: wesley souza on Sep 8, 2008 9:49 AM

[former_member201110]

Hi Jo,

Unfortunately, batch movements are only available in the IBT1 table.

You don't mention what you query is trying to do. The link between the AR, AP and stock transactions is the BaseType, BaseEntry and BaseLinNum fields.

For example, the following query will show all the sales deliveries of a particular item that have batch numbers:

select  
	T2.DocNum,
	T2.CardCode,
	T0.BatchNum,
	T0.WhsCode,
	T1.ItemCode,
	T1.Dscription,
	T0.Quantity
from 
	IBT1 T0 
	inner join DLN1 T1 on T0.BaseEntry = T1.DocEntry and T0.BaseLinNum = T1.LineNum and T0.BaseType = 15
	inner join ODLN T2 on T1.DocEntry = T2.DocEntry
where
	T1.ItemCode = 'ABCDEFG'

Kind Regards,

Owen