Sales Person Commission Payout Report

[Former Member]

Good Day Mentors,

I am currently working on a report that ought to compute for Sales Agent commission.

Sources for gross sales computation is A/R Invoice(OINV) and A/R Credit Memo (ORIN).

Client also wants to exclude the VAT from the computation of gross sales as well as all credit memos issued for a specific period in which the commission of the agent will be based on.

DocTotal in both OINV and ORIN already have the VAT(VatSumSy) included in them upon commit into the databse. So i subtracted it from the DocTotal. The formula I came up with

OINV.DocTotal - OINV.VatSumSy -  ORIN.DocTotal - ORIN.VatSumSy 

Now, the main source of the problem is the part in which the report ought to have a date range facility. Since the commissions for the sales people will be computed every month.

Edited by: Sean Yu on Apr 4, 2009 9:46 AM

[Former Member · Accepted solution]

Hi, I am not an expert, but I think your problem is because you have WHERE clauses that include fields on your ORIN file, therefore it will only extract customers where they have credits. You could imbed the link to credits (and invoices) as sub-reports... Similar example...

SELECT T0.[GroupCode], T0.CardCode,

(SELECT Count(T1.DocNum) FROM [ORIN] T1 WHERE T1.[CardCode]=T0.[CardCode]),

(SELECT Count(T2.DocNum) FROM [OINV] T2 WHERE T2.[CardCode]=T0.[CardCode])

FROM OCRD T0

WHERE T0.[GroupCode] IS NOT NULL

This will list all customer regardless of whether they have any credits and/or invoices...

[Former Member]

Hi Simon,

Thanks for the advise. I re-wrote my query based on your suggestion. Got it to work already. Should have thought of this before.

Regards,

Sean

[Former Member]

I made two separate queries. One which computes the gross sales from OINV table minus the VAT. See Code below:

SELECT T0.[ChannlBP] 'Upline Code',
       (SELECT CardName
       FROM   OCRD
       WHERE  CardCode = T0.CHannlBP) 'Upline Name',
       T0.[CardCode] 'Downline Code',
       T0.[CardName] 'Downline Name',
       SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) 'GrossSales',
       Rate = CASE WHEN ( (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) <= 10000) THEN .03 WHEN ((SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) > 10000 
       AND (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) <= 49999) THEN .025 WHEN ((SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) > 50000 
       AND (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) <= 149999) THEN .02 WHEN ( (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) > 150000) THEN .015 ELSE 0 END,
       (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) * (CASE WHEN ( (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) <= 10000) THEN .03 WHEN ((SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) > 10000 
       AND (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) <= 49999) THEN .025 WHEN ((SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) > 50000 
       AND (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) <= 149999) THEN .02 WHEN ( (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) ) > 150000) THEN .015 ELSE 0 END) 'Commission'
FROM   OCRD T0 INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
WHERE  T1.[DocDate] BETWEEN [%0] 
       AND [%1] 
       AND T0.[ChannlBP] IS NOT NULL 
       AND T0.[frozenFor] LIKE '%N' 
       OR T0.[validFor] LIKE '%Y' GROUP BY T0.[ChannlBP], T0.[CardCode], T0.[CardName]

The second, computes the total Credit Memo issued from ORIN. Both queries using DocDate for date range. Code below:

SELECT T0.[ChannlBP] 'Upline Code',
       (SELECT CardName
       FROM   OCRD
       WHERE  CardCode = T0.CHannlBP) 'Upline Name',
       T0.[CardCode] 'Downline Code',
       T0.[CardName] 'Downline Name',
       SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) 'CM Issued'
FROM  
OCRD T0 INNER JOIN ORIN T1 ON T0.CardCode = T1.CardCode
WHERE  T1.[DocDate] BETWEEN [%0] 
          AND [%1] 
          AND 
       T0.[ChannlBP] IS NOT NULL 
       AND T0.[frozenFor] LIKE '%N' 
       OR T0.[validFor] LIKE '%Y' GROUP BY T0.[ChannlBP], T0.[CardCode], T0.[CardName] 

[Former Member]

I am now trying to incorporate both queries into a single one.

Current code

/*SELECT FROM [dbo].[OINV] T1*/
declare @fromdate as datetime 

   /* WHERE */
set @fromdate=   /* T1.[DocDate] */
              '[%0]' 

   /*SELECT FROM dbo.OINV T3*/
declare @tilldate as datetime 

   /* WHERE */
set @tilldate=   /* T1.[DocDate] */
              '[%1]' 

SELECT T0.[ChannlBP] 'Upline Code',
       (SELECT CardName
       FROM   OCRD
       WHERE  CardCode = T0.CHannlBP) 'Upline Name',
       T0.[CardCode] 'Downline Code',
       T0.[CardName] 'Downline Name',
       SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy)  'GrossSales',
       Rate = CASE WHEN ( (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) <= 10000) THEN .03 WHEN ((SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) > 10000 
       AND (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) <= 49999) THEN .025 WHEN ((SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) > 50000 
       AND (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) <= 149999) THEN .02 WHEN ( (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) > 150000) THEN .015 ELSE 0 END,
       (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) * (CASE WHEN ( (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) <= 10000) THEN .03 WHEN ((SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) > 10000 
       AND (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) <= 49999) THEN .025 WHEN ((SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) > 50000 
       AND (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) <= 149999) THEN .02 WHEN ( (SUM (T1.[DocTotal]) - SUM (T1.VatSumSy) - SUM (T2.[DocTotal]) - SUM (T2.VatSumSy) ) > 150000) THEN .015 ELSE 0 END) 'Commission'
FROM   OCRD T0 INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode INNER JOIN ORIN T2 ON T1.CardCode = T2.CardCode
WHERE  T1.[DocDate] >= @fromdate 
       AND T1.[DocDate] <= @tilldate 
       AND T2.[DocDate] >= @fromdate 
       AND T2.[DocDate] <= @tilldate 
       AND T0.[ChannlBP] IS NOT NULL 
       AND T0.[frozenFor] LIKE '%N' 
       OR T0.[validFor] LIKE '%Y' GROUP BY T0.[ChannlBP], T0.[CardCode], T0.[CardName]

The query should be able to show all of the Sales people who have Credit Memos issues to them as well as Sales people who don;t have Cm's issued to them.

But what I am currently getting from my query above are the people who only have CM's issued to them. I am unable to get the sales people who don't have CM's in ORIN table for the specified date range, but of course have records in OINV table.

Did I link the tables incorrectly?

Also, the commission rate is also being computed in this query using CASE function as you can see from the code above. Which makes the query complex and very rigid. Is there also a good way of handling the commission rate? I am also thinking that in the future, client might change the commission rate. Be it increase the ranges or change the rate for each range. Is there a good way of handling that other than an AddOn? Since currently everything is hardcoded in the query.

Thanks in advance for everyone's time,

Sean