Skip to Content
0
Former Member
May 21, 2015 at 10:18 AM

AR Cancellation Invoice - How can I show this as a 'minus?'

395 Views

Hi

I have written a basic query, as below, to give me total sales figures. From using the fields below, I can calculate total sales, taking all credits into account.

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[VatSum], T0.[TotalExpns], T1.[SlpName] FROM OINV T0 INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]

UNION

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], -T0.[DocTotal], -T0.[VatSum], -T0.[TotalExpns], T1.[SlpName] FROM ORIN T0 INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]

However, I've hit a problem. When an AR invoice has been raised and then cancelled, the AR cancellation document has been included in the results from the below query as a debit and not a credit, effectively doubling the sales figure for that particular customer. How do I get AR cancellation documents to show as 'minus?'

(And I know the Sales Analysis on SAP calculates this correctly but I am using sql to produce a report in another piece of reporting software we have so I can't use this).

Any help would be appreciated.

Regards

Wendy