Linking Delivery document with Invoice in SAP B1

[krishnam_mathur]

Hi Team,

Need some help with linking of invoice to their deliveries. I am using below query but seems like its not giving me correct output.

SELECT T0.[DocNum] as 'Del DocNum', T0.[DocDate] as 'Del DocDate', T3.[DocNum] as 'Inv Docnum', T3.[DocDate] as 'Inv Docdate' FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join INV1 T2 on T2.[BaseEntry] = T1.Docentry and T2.[BaseLine] = T1.[LineNum] INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] WHERE T0.[DocDate] between [%0] and [%1] group by T0.[DocNum], T0.[DocDate],T3.[DocNum], T3.[DocDate]

[zal_parchem2 · Accepted solution]

Great Krishnam - that explanation helps immensely!!!

Your SQL needs "DATEPART" using MM. Try this code then...and let us know if that works...

Here is the results for October 2018:

Using this SQL:

SELECT DISTINCT

T1.[DocNum] as 'Del DocNum', 
T1.[DocDate] as 'Del DocDate', 
T3.[DocNum] as 'Inv Docnum', 
T3.[DocDate] as 'Inv Docdate' 

FROM DLN1 T0 
INNER JOIN ODLN T1 ON T0.[DocEntry] = T1.[DocEntry] 
LEFT OUTER JOIN INV1 T2 on T2.[BaseEntry] = T1.Docentry and T2.[BaseLine] = T0.[LineNum] AND T2.ItemCode = T0.ItemCode
INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] AND T0.TrgetEntry = T3.DocEntry

WHERE 
T1.[DocDate] between '[%0]' and '[%1]'
T0.LineNum = 0
AND DATEPART(MM, T1.DocDate) < > DATEPART(MM, T3.DocDate)

[krishnam_mathur · Accepted solution]

Hi Friend,

It still have problem.

For del docnum the actual invoice number is 1657344.. Plus actually the posting month and date is same in both document.

[zal_parchem2 · Accepted solution]

Hello Krishnam - I added a few new restrictions to the code above, see if that helps...

Notice I added AND T0.LineNum = 0 in the WHERE Section and...

T0.TrgetEntry = T3.DocEntry in the INNER JOIN for OINV

Zal

[krishnam_mathur]

Thanks a lot zal.parchem2 . It works now. Excellent!

[zal_parchem2]

Hurray Krishnam Mathor - I knew we would get it!!! It was the new restrictions for sure...

Please mark this as answered so others know - this was a good one...

Regards, Zal

[zal_parchem2]

Hello Krishnam...

When you say it does not seem to be working correctly, what do you mean? Repeated Lines or Invoice is not found?

Here is a link for you also:

Recreate the Relationship Map

Maybe a screen print of the SQL results might help...with a short description of your problem...

Thanks and Best Regards, Zal

Try this code:

SELECT DISTINCT

T1.[DocNum] as 'Del DocNum', 
T1.[DocDate] as 'Del DocDate', 
T3.[DocNum] as 'Inv Docnum', 
T3.[DocDate] as 'Inv Docdate' 

FROM DLN1 T0 
INNER JOIN ODLN T1 ON T0.[DocEntry] = T1.[DocEntry] 
LEFT OUTER JOIN INV1 T2 on T2.[BaseEntry] = T1.Docentry and T2.[BaseLine] = T0.[LineNum] AND T2.ItemCode = T0.ItemCode
INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] 

WHERE T1.[DocDate] between '[%0]' and '[%1]'

[krishnam_mathur]

Hi Friend,

Thanks for the reply but my target is to find out the deliveries for which invoices have different posting month. Like Delivery posted in 11/28/2019 and Invoice posted on 12/01/2019. That they have different month.

When I am using below query the delivery document number is not related to invoice document number.

SELECT DISTINCT T1.[DocNum] as 'Del DocNum', T1.[DocDate] as 'Del DocDate', T3.[DocNum] as 'Inv Docnum', T3.[DocDate] as 'Inv Docdate' FROM DLN1 T0 INNER JOIN ODLN T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT OUTER JOIN INV1 T2 on T2.[BaseEntry] = T0.Docentry and T2.[BaseLine] = T0.[LineNum] AND T2.ItemCode = T0.ItemCode INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] where format(t1.Docdate,'MMyyyy')<>format(t3.docdate,'MMyyyy') --WHERE T1.[DocDate] between '[%0]' and '[%1]'

the output gives delivery number 1569939 for which invoice is 4428 which is not correct...