Closed DO Item with Invoice Number Query

[kedalenechong]

Hi All

Please help advise how to enhance this Query to show the actual Invoice Number of Closed Delivery Order.   Right now it is only showing internal Target Entry from DO.

SELECT T0."DocDate", T0.DocEntry, T0.[DocNum] 'DO No', T0.[CardCode], T0.[CardName], T1.[VisOrder] + 1 'LineNo.', T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T2.Telephone as 'Sales Employee', T1.[TargetType], T1.[TrgetEntry] FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] LEFT OUTER JOIN INV1 T3 on T1.[TargetType]=T3.[TrgetEntry] LEFT OUTER JOIN OINV T4 ON T4.[DocEntry] = T3.[DocEntry]
WHERE T0."DocStatus" = 'C'

Kedalene Chong

[markus_schaefer242]

Hi, 

please check again your JOIN to INV1... 

LEFT OUTER JOIN INV1 T3 on T1.[TargetType]=T3.[DocEntry]
DocEntry is needed instead of TargetEntry

Hope that helps.

Greetings
Markus

[markus_schaefer242]

Sorry... my mistake:

[markus_schaefer242]

LEFT OUTER JOIN INV1 T3 on T1.[TrgetEntry]=T3.[DocEntry]

[kedalenechong]

SELECT T0."DocDate", T0.DocEntry, T0.[DocNum] 'DO No', T0.[CardCode], T0.[CardName], T1.[VisOrder] + 1 'LineNo.', T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T2.Telephone as 'Sales Employee', T1.[TargetType], T1.[TrgetEntry], T4.DocNum FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] LEFT OUTER JOIN INV1 T3 on T1.[TargetType]=T3.[TrgetEntry] LEFT OUTER JOIN OINV T4 ON T4.[DocEntry] = T1.[TrgetEntry] WHERE T0."DocStatus" = 'C'

[kedalenechong]

I managed to modify my Query and get the result

[kedalenechong]

Sorry how to accept your Answer as Solution