Query on batch number in Invoice

[Former Member]

Hi Experts,

I would like to get the batch number picked during the Delivery document and shown in the print layout in A/R Invoice. Unfortunately, PLD of Invoice with Batch will not show the Batch numbers as they're already linked in the DR document.

My workaround is to attach a FMS in the INV1 field line to get the batch number when the Invoice is copied from the DR.

Any help with the query is very much appreciated.

We are using 8.81 PL7.

Thanks,

Don

[Former Member]

Hi Don..........

Please check this........

Select (Case When T1.BaseType=15 then I1.BatchNum else I11.BatchNum end) 'BatchNum', T0.DocNum 'Inv. No' 	 
FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCTG T2 ON 
T0.GroupNum = T2.GroupNum LEFT Join INV12 T3 On T3.DocEntry=T0.DocEntry LEFT JOIN OITM T4 On T4.ItemCode=T1.ItemCode LEFT JOIN OCHP T5 On T4.ChapterID=T5.AbsEntry
LEFT Join DLN1 T6 On T6.DocEntry=T1.BaseEntry and T6.LineNum=T1.BaseLine 
left outer join IBT1 I1 on T1.ItemCode=I1.ItemCode   and (T6.DocEntry=I1.BaseEntry and T6.ObjType=I1.BaseType)
left outer join OBTN T8 on T8.ItemCode=I1.ItemCode and I1.BatchNum=T8.DistNumber
LEFT Join WTR1 T16 On T16.DocEntry=T1.BaseEntry and T16.LineNum=T1.BaseLine 
left outer join IBT1 I11 on T1.ItemCode=I11.ItemCode   and (T16.DocEntry=I11.BaseEntry and T16.ObjType=I11.BaseType)
left outer join OBTN T18 on T18.ItemCode=I11.ItemCode and I11.BatchNum=T18.DistNumber
LEFT Outer Join OWHS T20 On T20.WhsCode=T0.Filler

Run this query you get Invoice No. and its Batch.....

You may insert required c0olumns also.....

Regards,

Rahul

[Former Member]

Hi Rahul,

Thanks for the query, however, I only need a query to be attached as FMS in the Invoice line which will return me the batch number which was assigned in the Delivery document link.

Many thanks.

Don

[former_member203638]

Don try this;

DECLARE @LOTE AS NVARCHAR(300)
DECLARE @TXT NVARCHAR(MAX)
SET @TXT = ''
DECLARE P CURSOR FOR 
Select Distinct T2.DistNumber 
From ITL1 T0 
INNER JOIN OITL T1 ON  T0.LogEntry = T1.LogEntry
INNER JOIN OBTN T2 ON T0.ItemCode = T2.ItemCode and T0.SysNumber = T2.SysNumber
INNER JOIN DLN1 T3 ON T1.DocEntry = T3.DocEntry
Where T3.DocEntry = $[INV1.BaseEntry] and T3.ItemCode = $[INV1.Itemcode] and T3.LineNum = $[INV1.LineNum] and T2.ItemCode= $[INV1.ItemCode]
OPEN P
FETCH NEXT FROM P INTO @LOTE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TXT =@TXT + @LOTE + ',' 
FETCH NEXT FROM P INTO @LOTE
END 
CLOSE P
DEALLOCATE P
SELECT Case When Right(rtrim(@txt),1)=',' Then SUBSTRING(rtrim(@TXT),1,LEN(rtrim(@TXT))-1)
ELSE @TXT END as Lote

Regards,

Alessandro.