Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 12 at 08:57 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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