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

[former_member183402]

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

[agustin_marcoscividanes · Accepted solution]

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

[former_member183402 · Accepted solution]

Thank you Agustin

[former_member183402 · Accepted solution]

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?

[agustin_marcoscividanes · Accepted solution]

Hi

join ITL1 and OBTN by sysnumber and itemcode columns.

Kind regards

Agustín

[former_member183402 · Accepted solution]

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

[former_member655351]

I am using this query for GOODS ISSUES for PRODUCTION (OIGE) and ISSUE BATCHES. Its working fine but if I have number of ITEMS in GOODS ISSUE then same batch will get repeated instead of separate batches for issued items.

If you have any solution for the same. Please guide me.

Thanks in Advance.

Regards,

Nikhil