Regarding linking Sales Order with Invoice

[Former Member]

Hi

I am trying to connect Sales order to Invoice .I have already tried to baseRef key to connect from Invoice to Delivery and delivery to Order .However , sometimes delivery option is skipped for item like labor service . So I want to link my invoice with sales order , can someone help me on this

Bishal

[Former Member · Accepted solution]

Dear Bishal,

Maybe you can use my query.

SELECT DISTINCT T1.[DocNum] [S.Order], T1.[CardName], T1.[DocTotal], T3.[DocNum] [DPR], T3.[DpmDrawn] [Drawed], TB.DocNum [Bank], T5.[DocNum] [DP Inv.], T7.DocNum [A/R Invoice]

FROM RDR1 T0

LEFT JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN DPI1 T2 ON T2.BaseEntry = T0.DocEntry AND T2.[BaseType] = '17'

LEFT JOIN ODPI T3 ON T2.DocEntry = T3.DocEntry

LEFT JOIN ORCT TB ON T3.ReceiptNum = TB.DocEntry

LEFT JOIN DPI1 T4 ON T3.DocEntry = T4.BaseEntry

LEFT JOIN ODPI T5 ON T4.DocEntry = T5.DocEntry

LEFT JOIN INV1 T6 ON T0.TrgetEntry = T6.DocEntry AND T6.BaseDocNum = T1.DocNum

LEFT JOIN OINV T7 ON T6.DocEntry = T7.DocEntry

ORDER BY T1.[DocNum]

Regards

Nghia