Add in Sales Order Total and A/R Invoice Total

[former_member698071]

Hi all,

I am currently working on a query to compare Sales vs A/R Invoice. I am trying to add in both a column for Sales Order Total and A/R Invoice Total. I managed to get the Sales Order Total working but when I try to add in T3.[DocTotal] as 'A/R Invoice Total' it gives me an error saying Invalid column name 'DocTotal'. It is referring to the one for T3 because it works perfectly fine when I remove it.

The following code is what I have so far:

SELECT T0.[DocDueDate] as 'Delivery Date', T0.[DocNum] as 'Sales Order No.', T0.[DocStatus] as 'SO Status', T0.[CANCELED] AS 'Canceled', T0.[CardName] as 'Customer Name', T9.[SlpName] as 'Sales Employee', T0.[U_Class], T6.[ItmsGrpCod] as 'Item Group', T1.[ItemCode] as 'Item No.', T1.[Dscription] as 'Item Description', T1.[Quantity] as 'SO Qty', T1.[OpenQty] as 'SO Open Qty', T0.[DocTotal] as 'SO Total', T2.[Quantity] as 'Delivered Qty', T2.[OpenQty] as 'Delivered Open Qty', T3.[Quantity] as 'Invoice Qty', T3.[DocTotal] as 'A/R Invoice Total'

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T5 ON T5.[ItemCode] = T1.[ItemCode]

LEFT JOIN DLN1 T2 on T2.[BaseEntry]=T1.[DocEntry] and T2.[ItemCode]=T1.[ItemCode]

left join inv1 T3 on T3.[BaseEntry]=T2.[DocEntry] and T3.[ItemCode]=T2.[ItemCode]

left join inv1 T4 on T4.[BaseEntry] = T1.[DocEntry] and T4.[ItemCode] = T1.[ItemCode]

left Join OITB T6 on T6.[ItmsGrpCod] = T5.[ItmsGrpCod]

left outer join OSLP T9 on T9.[SlpCode] = T1.[SlpCode]

WHERE T0.[DocDueDate] >=[%0] and T0.[DocDueDate] <=[%1]

Thanks!

[kothandaraman_nagarajan]

Hi,

In your query, there is no OINV table included to get document total. Also the query logic is correct.

Just corrected your query to get data,

SELECT T0.[DocDueDate] as 'Delivery Date', T0.[DocNum] as 'Sales Order No.', T0.[DocStatus] as 'SO Status', T0.[CANCELED] AS 'Canceled', T0.[CardName] as 'Customer Name', T9.[SlpName] as 'Sales Employee', T0.[U_Class], T6.[ItmsGrpCod] as 'Item Group', T1.[ItemCode] as 'Item No.', T1.[Dscription] as 'Item Description', T1.[Quantity] as 'SO Qty', T1.[OpenQty] as 'SO Open Qty', T0.[DocTotal] as 'SO Total', T2.[Quantity] as 'Delivered Qty', T2.[OpenQty] as 'Delivered Open Qty', T3.[Quantity] as 'Invoice Qty', T10.[DocTotal] as 'A/R Invoice Total'

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T5 ON T5.[ItemCode] = T1.[ItemCode]

LEFT JOIN DLN1 T2 on T2.[BaseEntry]=T1.[DocEntry] and T2.[ItemCode]=T1.[ItemCode] and T2.baseline = T1.LineNum and T2.[ItemCode]=T1.[ItemCode] and T2.BaseType =17

left join inv1 T3 on T3.[BaseEntry]=T2.[DocEntry] and T3.baseline = T2.LineNum and T3.[ItemCode]=T2.[ItemCode] and T3.BaseType = 15

left join inv1 T4 on T4.[BaseEntry] = T1.[DocEntry] and T4.baseline = T3.LineNum and T4.[ItemCode] = T1.[ItemCode]

left Join OITB T6 on T6.[ItmsGrpCod] = T5.[ItmsGrpCod]

left outer join OSLP T9 on T9.[SlpCode] = T1.[SlpCode] INNER JOIN OINV T10 on T10.Docentry= T2.Docentry WHERE T0.[DocDueDate] >=[%0] and T0.[DocDueDate] <=[%1]

Regards,

Nagarajan