
As part of the APM subscription, each customer is licensed for a set number of Users. One component of the User Licensing metric is the ACTIVEBROKERDETAILCOUNT. This measurement counts the number of distinct brokers having at least 1 Broker Detail (also called Producer Detail) with Active status AND is Effective for the reporting Period. This number is exclusive of brokers who have a computed transaction in the measurement reporting period. Over time, it may be useful to terminate brokers in APM who are no longer selling nor receiving commission. Terminating inactive brokers can help you avoid unnecessary overage charges. This process comprised of 4 steps:
Note: This process is intended for Intermediate to Advanced users of APM and should be performed in a test environment prior to executing in Production.
CREATING A SQL TO IDENTIFY BROKER/BROKER DETAIL RECORDS FOR TERMINATION
The first step in terminating inactive brokers is to create a query so that a list of brokers can be reviewed and approved for termination. To export this data in a meaningful way, the query should have useful fields to help you make a terminate decision.
The base SQL query would be similar to :
In addition, the query has a useful join to reduce the number of records that are returned.
The revised SQL query would now be similar to:
Finally, the “where” clause contains constraints to check for BrokerDetail.DatExp is null and BrokerDetail.ProSta=1 to only include broker detail records that do not have an Expiration Date and are Active.
By default, APM is configured to allow 1 Broker Detail for a given time span (You may have seen the overlapping Broker Detail error message when posting Broker Detail records in the past). However, when posting a Broker Detail record, the incoming InBrokerDetail record is considered a match to an existing Broker Detail record
if :
This matching functionality allows the user to update an existing record through integration without creating a new record.
APM allows additional fields to be added to this matching key via process.match.brokerdetail under Administrator >Configuration> Options of APM.
Figure 1: process.match.brokerdetail Option
In this example, the fields (Match1, Match8 and Business Type) have been added to the matching key for Broker Detail. These fields will need to be added to the SQL executed to return Broker Detail records to terminate.
Click the Pencil to view the option and click Reopen to view the Label and the actual field name.
Note: If this option only shows the label and not the field, the field names will have to be determined by clicking the label on the Broker Detail screen to bring up the Data Dictionary to reveal the Database Column name.
As a result of the configuration of this option, the SQL will need to be updated to the following to include the fields listed in the process.match.brokerdetail option. The resulting SQL is shown below:
A final step to the query is to add a blank field to mark the records in the query output to be terminated and to order the records. The Order By clause will be helpful in terming multiple batches of brokers if the count is greater than 10,000. The final query should look similar to below:
Depending on the number of rows that your query returns (less than 10,000), you may be able to use SQL Workbench to export the data to be used in the termination of Brokers.
Navigate to Administrator >Tools > Sql WorkBench
Note: Execute this query first in a Test environment to get a sense of the number of records returned by the query and the execution time of the query. If more than 10,000 records are returned, an extract will need to be created or another batch of brokers will need to be termed after the first batch is successfully termed.
Figure 2: SQL Workbench window with query
In the upper right corner of the SQL Workbench window, some options are displayed.
Figure 3:SQL Workbench Download Excel Option
3. A file is downloaded to your computer with the name in the format of: SqlWorkBenchStdFIxxxxxxxxxxxxxxxxx.xls.
4. Locate this file and open it to review contents.
5. Mark any records that you want to terminate with a CAPITAL Y as shown below. APMs expression language (Groovy) is case sensitive.
Figure 4: Modified Excel Output
6. Save the file to your preferred local (for ex: My Documents or Downloads) location on your Computer.
7. In preparation for creating the import format, open the spreadsheet and delete all the rows with the exception of the header and 3 - 4 rows in the spreadsheet and save the file as bdtermtemplate.xls. Import formats can use a template of the excel to simplify mapping the data but has a 5 MB limit. Removing the extra data in the Workbench output allows us to use a minimal spreadsheet as a template.
Figure 5: Excel Template
In this step, we will create a custom Import Format based on the Excel output that was created in the prior step from SQL Workbench.
Figure 6: Import Format Field Mapping Screen
9. Enter the desired termination date for ALL of the Broker Detail records in YYYYMMDD format.
10. Select Complete.
11. The Field Mapping field will close and take you back to the Import Format search screen.
12. Select the Import Format that was just completed and scroll down to the Option Settings section and expand the section.
13. The Skip Record section is where we will add logic to exclude the records without a ‘Y’ in the Terminate column in our spreadsheet output. The Skip expression will skip the record if the expression evaluates to TRUE so the expression will look like:
Note: The expression language used (Groovy) is Case Sensitive so the column name must be exact.
Figure 7: Import Format Skip Record Expression
14. Select Save and then Activate to complete the task of defining the Import Format.
In this step, we will import the list of Brokers that were created with the SQL Workbench with the custom Import Format that was created in the prior step from SQL Workbench.
Add Basic Details for the InFile
2. On the File Search screen, choose (+) to create a new InFile.
3. Enter a value for the InFile ID BrokerDetailTermination YYYYMMDD
4. Enter values for the Name (Optional).
The description of the InFile. The value defaults from the File ID when this field is left blank.
5. Enter values for the Eff Date (Optional). This date is the date the infile was created,
6. Billing Month (Optional). This field refers to the billing month that represents the application fiscal period. The value defaults to the current period's bill month.
7. Enter values for the Billing Year (Optional). The billing year that represents the application fiscal period. The value defaults to the current period's bill year.
8. Enter values for the Payout Type ID. In this example, select the Payout Type that Broker Details (or Broker Demographic info )are normally imported. In this example, Client Configuration will be used.
9. Enter values for the Comments. Add a descriptive comment, as needed.
10. Save your changes.
11. In the Import Requests section, choose Add Import Request.
12. On the Import Format Selection screen of the wizard, select the Import Format from the Import Format dropdown.
In this example, please select the name of your import format. In this example, the Import Format is BDTerm.
13. Select the Upload Type
14. Select Client and Choose File
15. Choose Next. The file is uploaded into memory. The Import Request Summary screen of the wizard appears with the ID of the new Import Request.
16. Choose Complete.
17. The data is uploaded into InBrokerDetail and you will notice that the total number of records imported into APM may not match the total rows of the SQL WorkBench output. This is because the import format only imported the records that had manually been marked Y in the Terminate column.
Figure 8: InFile Detail Screen
In most configurations, termination of a Broker Detail will trigger retroactivity to reevaluate payments that are tied to this broker. Depending upon the Brokers selected for termination and the volume of transactions associated to the terminated Brokers, it may beneficial to temporarily turn off retroactivity for the Broker Detail retro object prior to posting the termination file. The Broker Details being terminated did not have any payment history attached to them so retro process would be unnecessary for this termination.
Note: Performance and Regression Testing of disabling/enabling Broker Detail retro is strongly suggested.
To turn off the retroactivity object for Broker Detail:
Figure 9: Retroactivity Search Screen
Once the retroactivity status has been confirmed, navigate back to the Infile, Select Ready and then Post.
Review any errors and make sure the desired records were terminated.
Reminder: Activate the Producer Detail Retroactivity.
Deploying to Prod
Once you have tested this process in a test environment, you can execute this process in PROD.
Figure 9: Importing and Exporting an Import Format
In conclusion, terminating Broker Details is a way to manage and maintain the Active User Counts and a way to terminate inactive Producers in APM.
Feel free to drop a comment or question on this blog.
Additional APM Resources are available at:
APM WorkZone Site (invitation only)
Please email phillip.butts@sap.com or dean.patterson@sap.com for access.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
9 | |
9 | |
7 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |