Skip to Content
author's profile photo Former Member
Former Member

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

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on May 21, 2015 at 10:52 AM

    Hi Wendy ,

    Try this

    SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], CASE WHEN T0.CANCELED='N' THEN

    T0.[DocTotal] ELSE T0.DocTotal*-1 END AS 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]

    or

    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]

    where T0.CANCELED='N'

    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]

    --Manish

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 21, 2015 at 10:58 AM

    Hi Wendy

    Please try below

    SELECT 'AR'[Type],T0.[DocNum], T0.[DocDate], T0.CANCELED

    , T0.[CardCode], T0.[CardName]

    ,Case When T0.CANCELED <>'C' then T0.[DocTotal] else -T0.[DocTotal] end [DocTotal]

    ,Case When T0.CANCELED <>'C' then T0.[VatSum] else -T0.[VatSum] end [VatSum]

    ,Case When T0.CANCELED <>'C' then T0.[TotalExpns] else -T0.[TotalExpns] end [TotalExpns]

    , T1.[SlpName]

    FROM OINV T0

    INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]

    Where T0.DocDate>=[%0] and T0.DocDate<=[%1]

    UNION All

    SELECT 'AR CN'[Type], T0.[DocNum], T0.[DocDate], T0.CANCELED

    , T0.[CardCode], T0.[CardName]

    ,Case When T0.CANCELED <>'C' then -T0.[DocTotal] else T0.[DocTotal] end [DocTotal]

    ,Case When T0.CANCELED <>'C' then -T0.[VatSum] else T0.[VatSum] end [VatSum]

    ,Case When T0.CANCELED <>'C' then -T0.[TotalExpns] else T0.[TotalExpns] end [TotalExpns]

    , T1.[SlpName]

    FROM ORIN T0

    INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]

    Where T0.DocDate>=[%0] and T0.DocDate<=[%1]

    Thanks

    Unnikrishnan

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.