This article provides information about acquiring commission attributes, including the contract start and end dates from the Spark database.
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>;