Skip to Content
0

How can I join OBTN table to OIGE or IGE1 or OWOR tables SAP B1?

Jan 11 at 04:17 PM

280

avatar image

Hi all,

I wrote query which has OIGE, IGE1, and OWOR tables but I failed to add butch number of finished product

The following is my query.

SELECT 
 T1.baseentry, T2.[ItemCode], T1.[ItemCode], T1.[Dscription], (select T3.[ItemName] from OITM T3 where T3.[ItemCode]=T2.[ItemCode]) as ItemDescription, T1.[StockPrice], T1.[INMPrice], T2.[ItemCode], T2.[CmpltQty], T2.[RjctQty], T1.[Quantity], (T1.[StockPrice]*T1.[Quantity]) as Total_Amount, T2.[PostDate],
T0.[SysRate] FROM [dbo].[OIGE]  T0 INNER JOIN IGE1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner join OWOR T2 on T1.baseentry=T2.docentry

How can I add butch number of finished product, I though I will get it from OBTN table but I do know how to join it in my query

Please anyone can help me

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Agustin Marcos Cividanes Jan 12 at 08:57 AM
2

Hi

you have to use the tables OITL and ITL1.

The table OITL relates the document in the inventory log, and ITL1 saves the batch numbers for each line of the document.

You have to join by docentry and filter by doctype in OITL.

Kind regards

Agustín

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Thank you Agustin

0

Hi Agustin,

ITL1 and OITL tables do not have field of batch numbers

I think the table which has batch number of finished product is OBTN but it does not have relationship with OWOR or OIGE or IGE1 tables. How can I join it with that tables?

0

Hi

join ITL1 and OBTN by sysnumber and itemcode columns.

Kind regards

Agustín

0

Thank you so much Agustin, it worked

I did the following query

select distinct
T5.[DocEntry], T1.baseentry, T5.[ItemCode], T1.[ItemCode], T1.[Dscription], (select N3.[ItemName] from OITM N3 where N3.[ItemCode]=T5.[ItemCode]) as ItemDescription, T1.[StockPrice], T1.[INMPrice], T5.[ItemCode], T5.[CmpltQty], T5.[RjctQty], T1.[Quantity], (T1.[StockPrice]*T1.[Quantity]) as Total_Amount, T5.[PostDate],T0.[SysRate]
,
( select top 1 V4.DistNumber from      OIGN V0  INNER JOIN IGN1 V1 ON V0.[DocEntry] = V1.[DocEntry] inner join OITM V2 on V1.ItemCode = V2.ItemCode
     inner join (select A0.DocEntry, A0.DocLine, A1.SysNumber, -sum(A1.Quantity) as AllocQty from OITL A0 inner join ITL1 A1 on A0.LogEntry = A1.LogEntry where A0.DocType = 59 group by A0.DocEntry, A0.DocLine, A1.SysNumber) V3 on V1.DocEntry = V3.DocEntry and V1.LineNum = V3.DocLine
     inner join OBTN V4 on V3.SysNumber = V4.SysNumber and V1.ItemCode = V4.ItemCode inner join OWOR V5 on V1.BaseEntry = V5.DocEntry  where V5.DocEntry  = T5.DocEntry 
) as 'batch number'
from
     OIGE T0
    inner join IGE1 T1 on T0.DocEntry = T1.DocEntry
 inner join OITM T2 on T1.ItemCode = T2.ItemCode
    full join (select S0.DocEntry, S0.DocLine, S1.SysNumber, -sum(S1.Quantity) as AllocQty from OITL S0 inner join ITL1 S1 on S0.LogEntry = S1.LogEntry where S0.DocType = 60 group by S0.DocEntry, S0.DocLine, S1.SysNumber) T3 on T1.DocEntry = T3.DocEntry and T1.LineNum = T3.DocLine
     full join OBTN T4 on T3.SysNumber = T4.SysNumber and T1.ItemCode = T4.ItemCode
    inner join OWOR T5 on T1.BaseEntry = T5.DocEntry order by T1.[ItemCode] asc
0