Description;

To access the data in the Commission tables – E,g, plan names and rates for Salespersons

Concept

Pull the commission plans and rates from Storedata using SQL or Crystal as well as listings by Salespersons of the Commission Plans each are on.

Cautions

None. These queries only reports. There are no changes to the Cyrious database.

Steps

  1. Commission Plans are set up in the Control/Setup/Pricing Setup/Commission Plans page. The setup provides a form to assign commissions to Salespersons1, 2, and 3 with different rates for each and even different rates depending on how many are receiving commissions. Each plan is then assigned a unique name. Each plan also offers the option to base the commission on Total Sales or Gross Margin.
  2. The two tables which contain the commission information entered in the Commission Plans form are the Element table and the CommissionRates table. The CommissionPlanID is the link to the ID of the Element table and the Employee table. The following code will provide the names of salespersons who have a commission plan assigned to them and the name of that plan. The Commission Plan assigned to the Salesperson is entered in Control's Setup/Employee Setup/Employee Information/Commission Plan field.

Control does not provide handles to link companies or transactions to a Commission Plan. This must be done with UDFs.

  1. SELECT

E.ID as EmployeeID

, E.FirstName + ' ' + E.LastName as Salesperson

, E.CommissionPlanID

, CP.ElementName as PlanName

, CR.Rate1Salesperson1

, CR.Rate2Salesperson1

, CR.Rate2Salesperson2

, CR.Rate3Salesperson1

, CR.Rate3Salesperson2

, CR.Rate3Salesperson3

FROM Employee E WITH(NOLOCK)

-- The element table is what stores the plans.

LEFT JOIN Element CP WITH(NOLOCK) ON CP.ID = E.CommissionPlanID AND CP.ClassTypeID = 13000

-- The commission rate stores the rates for the plan.

LEFT JOIN CommissionRate CR WITH(NOLOCK) on CR.CommissionPlanID = CP.ID

-- filter out all employees that don't have a commissionplan set.

WHERE ISNULL(E.CommissionPlanID,0) > 0

Source

Contributor: Steve Gillispie based on input and code from Brandon , Acorn Sign Graphics

Date: 717/2017

Version: Control 6.1_

You could leave a comment if you were logged in.