Skip to end of metadata
Go to start of metadata

In the Hop Islands Company, the sales person's income is based on both commissions and reaching or over reaching their sales targets.

To establish this process in SAP Business One, a user defined table (UDT) containing additional User Defined Fields (UDF) representing the sales persons target for each month of the year and the achievements percentage can be created.

In the UDT, the Name and the Code fields are created by default and are both mandatory and unique. In this UDT, these fields are used to hold the sales person code and name as defined in the Sales Employees/ Buyers Setup window.

This data can be found using a simple query on the Sales Employees/ Buyers Setup SQL table OSPL.


The data can then be uploaded to the UDT, using the Data Transfer workbench if desired.
Once the UDT has the sales person code matching their monthly targets and achievements, it enables the creation of reports with several tables, linking them through the sales person code. The following table gives a sample list of some tables which can be linked in this manner through the sales person's code.

Tables

Description

Linking Code

OSPL

Sales Employees/Buyers Setup

SlpCode

[@YourTablename]

Your table description

Code

OQUR

Sales Quotation

SlpCode

ORDR

Sales ORDER

SlpCode

ODLN

Delivery

SlpCode

ORDN

Return

SlpCode

ODPI

Down Payment

SlpCode

ORIN

Credit note

SlpCode

OCRD

Business Partner Master Data

SlpCode

OOPR

Sales Opportunity

SlpCode



The following includes two sample queries which pull such reports. Please note that these are sample queries and as such needs to be tested to ensure suitability for your customer's requirements.

Query 1

/* The following report provides the achievement for the month of July for Sales Person 1,
querying the sales invoice  and sales credits notes header tables and the Sales Target table*/.

SELECT
	((sum (convert (numeric(19,2),T1.[DocTotal]))-
sum (convert (numeric(19,2),T2.[DocTotal])))-
 	convert (numeric(19,2),T0.[U_07])) [Achievement]
FROM
	[dbo].[@SALESTARGET]  T0
	Inner Join
	OINV T1 on
	T0.Code = T1.SlpCode
	Inner Join
	ORIN T2 on
	T0.Code = T2.SlpCode and
	T1.SlpCode = T2.SlpCode


WHERE
	T1.SlpCode = 1 and
	Month(T1.[DocDate]) = 7
Group by
	T0.[U_07] /* field holding the target for the month of July*/

Query 2

/*The following report enable to calculate the part of the salary based on the Achievement.
All the information is now taken from the Sales Target table*/

SELECT
         Case
	   When T0.[U_CAchievement] > 150 then ((T0.[U_CurCom]* T0.[U_CAchievement])/100) + 200
	   When T0.[U_CAchievement] between 100 and 149.99 then ((T0.[U_CurCom]* T0.[U_CAchievement])/100) +100
	   When T0.[U_CAchievement] between 85 and 99.99 then ((T0.[U_CurCom]* T0.[U_CAchievement])/100) -50
	Else 0
	End "Achivement"

FROM
	[dbo].[@SALESTARGET]  T0

Group by
	T0.U_CAchievement,
	T0.[U_CurCom]

Note that access to the UDT can be restricted using Additional Authorisation.