Overview

This article provides information about acquiring commission attributes, including the contract start and end dates from the Spark database.

Information

Some commission attributes can be found in the Spark database, stored in [ClientCustomer].[CustomerCommission]. This table, however, does not provide information about the contract start and end dates used to determine the timeframe commissions need to be paid out. To get this information, as well as other commission attributes, the following queries must be run in the Spark database:

select custID from Customer where CustNo in ('<account number>');

Once the customer ID has been acquired, the following will provide the required commission attributes:

select CustNo, CT.BeginDate [Contract Start Date], CT.Enddate [Contract EndDate],
CC.BrokerCode, CC.MarketerCode, CC.SalesChannelCode, CC.AggregatorFee
From Customer C
Join Contract CT on CT.ContractID=C.ContractID
Join ClientCustomer.CustomerCommission  CC on CC.CustID=C.CustID
Where 1=1
and CC.CustomerCommissionId=(Select Max(CustomerCommissionId)
 From ClientCustomer.CustomerCommission where CustID=C.CustID)
and C.CustID=<Customer ID>;