Draft Invoice SQL query

[former_member261836]

Hi Everyone,

I am having one query issue with respect to one draft A/P invoice how to fetch the respect posted A/P Invoice in the below query.

SELECT distinct T1.[SeriesName] as 'Location',T0.[CardCode] as 'Supplier-Code', lower(t0.CardName) as 'Vendor Name',t0.docentry as 'Draft No',T0.[DocDate] as 'Draft-Date', t0.NumAtCard as 'Supplier-Invoice No',T0.[U_PWN] as 'Way-Bill No',t0.U_ID as 'Way-Bill Date',t0.U_MD as 'Dispatch Mode', T0.[U_LRDN] ,t0.U_LRDATE,t0.U_DispatchCases, ' ' as 'GRN-Date',t0.U_DOP as 'Freight Payment-Date',t0.U_CNN as 'Cheque Number',t0.U_Bank as 'Bank', t0.U_Amount as 'Freight Payment-Amount',' ' as 'Status', tp.DocEntry as 'AP Invoice Link',tp.DocNum as 'A/P Invoice No',tp.DocDate as 'Date', tp.DocTotal as 'Amount',ty.DocEntry AS 'Outgoing Payment No', ty.DocDate as 'Outgoing Payment Date', ty.DocTotal AS 'Outgoing Payment Total' FROM ODRF T0 INNER JOIN NNM1 T1 ON T0.[Series] = T1.[Series] left join OPCH tp on tp.DocEntry = t0.draftKey left JOIN [dbo].[VPM2] Tw ON Tw.Docentry = Tp.DocEntry left join [dbo].[OVPM] Ty on ty.DocNum = tw.DocNum where t0.ObjType=18

[former_member233854 · Accepted solution]

You just mistyped the alias. It is t0.DocEntry = tp.draftKey instead of tp.DocEntry = T0.draftKey

SELECT distinct T1.[SeriesName] as 'Location',T0.[CardCode] as 'Supplier-Code', lower(t0.CardName) as 'Vendor Name',t0.docentry as 'Draft No',T0.[DocDate] as 'Draft-Date', t0.NumAtCard as 'Supplier-Invoice No',T0.[U_PWN] as 'Way-Bill No',t0.U_ID as 'Way-Bill Date',t0.U_MD as 'Dispatch Mode', T0.[U_LRDN] ,t0.U_LRDATE,t0.U_DispatchCases, ' ' as 'GRN-Date',t0.U_DOP as 'Freight Payment-Date',t0.U_CNN as 'Cheque Number',t0.U_Bank as 'Bank', t0.U_Amount as 'Freight Payment-Amount',' ' as 'Status', tp.DocEntry as 'AP Invoice Link',tp.DocNum as 'A/P Invoice No',tp.DocDate as 'Date', tp.DocTotal as 'Amount',ty.DocEntry AS 'Outgoing Payment No', ty.DocDate as 'Outgoing Payment Date', ty.DocTotal AS 'Outgoing Payment Total' FROM ODRF T0 INNER JOIN NNM1 T1 ON T0.[Series] = T1.[Series] left join OPCH tp on t0.DocEntry = tp.draftKey left JOIN [dbo].[VPM2] Tw ON Tw.Docentry = Tp.DocEntry left join [dbo].[OVPM] Ty on ty.DocNum = tw.DocNum where t0.ObjType=18

[former_member261836]

Thanks You very much ..Danilo Kasparian