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:
- Creating the SQL for your APM configuration
- Exporting the Data from APM
- Marking Brokers for Termination
- Creating the import format
- Importing and Posting the Termination Data
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.
- BrokerId: This field is a key field used to match the InBrokerDetail to a BrokerDetail when posting.
- Name: This field is not needed to terminate a Broker but it is helpful to recognize Agents/Agencies by name rather than Id.
- DatEff: This field is a key field used to match the InBrokerDetail to a BrokerDetail when posting.
- FstDate: This field is not needed to terminate a Broker but it is helpful to recognize when Agents/Agencies were loaded into APM.
- LstDate: This field is not needed to terminate a Broker but it is helpful to recognize when Agents/Agencies were last updated in APM.
- WritingAgent: This field is a key field used to match the InBrokerDetail to a BrokerDetail when posting.
- BrokerRoleId: This field is a key field used to match the InBrokerDetail to a BrokerDetail when posting. (This key field is rarely used but must be included to ensure accuracy).
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 section below filters out BrokerDetail records that have had a payment processed against it in the last 12 months. Terminating a Broker Detail record that has a transaction tied to it will not reduce User counts since the Users are counted in TRANBROKERCOUNT portion of the User Metric.
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.
Additional SQL Considerations
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 :
- IBD.BrokerId is equivalent to BD.BrokerNo
- IBD.DatEff equals BD.DatEff
- IBD.BrokerRoleId is equivalent to BD.BrokerRoleNo
- IBD.WritingAgent equals BD.WritingAgent
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:
Exporting Rows through SQL Workbench
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.
- Limit results?: When this box is checked, the limit specified in the system option ui.sqlworkbench.query.max.rows.default is enforced. This value is typically set to 200.
- Max Rows: When the Limit results? box is unchecked, the Max Rows can be specified to increase the number of rows returned. The configurable limit can be set in the option ui.sqlworkbench.max.rows.overide up to 10,000 rows, which also a system defined limit.
- Override timeout?: When this box is unchecked, the timeout limit specified in the system option ui.sqlworkbench.query.timeout.default is enforced. This value can be set to a minimum of 2 minutes up to a maximum of 15 minutes. Default timeout value is 5 minutes.
- Timeout: When the Override timeout? box is unchecked, the query timeout can be set in this box up to the system limit of 20 minutes. The configurable limit can be set in the option ui.sqlworkbench.query.timeout.override up to 20 minutes, which also a system defined limit. This value can be set to a minimum of 2 minutes up to a maximum of 20 minutes. Default override timeout value is 15 minutes.
- Execute the query until the number of rows returned is less than the maximum system limit of 10,000 rows or is less than the number specified in the Max Rows option to ensure all of the desired records are returned.
- Select the EXCEL button under the query output.
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
CREATING AN IMPORT FORMAT
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.
- In the Portal menu, navigate to (Integration) > Configuration Data and select Import Format Search.
- Select ( + ) to add an Import Format detail.
- User Defined is preselected select Next.
- Enter:
- ImportFormatId:
- Name:
- InEntityName: In this example, select InBrokerDetail
- FileType: In this example, select MSExcel
- HeaderRows: Enter 1
- TrailerRows: Leave blank
- Choose File: Select the trimmed down file that was created in step 7 in the previous section (for example: bdtermtemplate.xls )
- Select Next
- Select Next since the fields are mapped properly from the sample file.
- Use the Auto Map action to automatically map source and target fields that share the same name. In this example,7 fields are automatically mapped (BrokerId, BrokerRoleId,BusType,DatEff,Match1, Match8 and WritingAgent).
- Click the Simple Text box in the Data Sources section and drag and drop it in the DatExp field.
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.
MANUALLY IMPORTING THE BROKER DETAIL TERMINATION FILE
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.
- In the Portal menu, navigate to (Integration) > Inbound Data and select File Search.
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
- Navigate to your file export file from SQL WorkBench and select Open.
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
ADDITIONAL CONSIDERATIONS: RETROACTIVITY
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:
- Navigate to Administrator>Commission Settings> Retroactivity Search (or use the Quick Nav and type in retroactivity search).
- Select the Producer Detail Change record
- Select Reopen
- Enter a comment and select Save
- You can now post the termination file.
- After you have posted the file, Navigate back to this screen.
- Select Activate.
- Enter a comment and select Save.
Figure 9: Retroactivity Search Screen
Posting the File
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.
- In Test, Navigate to Administrator>Configuration>Import Format Search. Select the Broker Detail Termination Import Format and select Export, saving the download to your computer.
- In Prod, Navigate to Administrator>Configuration>Import Format Search and import the Broker Detail Termination export created in Step 1 above.
Figure 9: Importing and Exporting an Import Format
- Execute the SQL WorkBench Query in Prod and save the output.
- Mark the Broker Detail records to terminate with a Y in the Terminate column.
- Import the file using the import format imported from Test in Step 2 (above).
- Optional: Turn off Retroactivity object for Broker Detail.
- Post file and review results.
- Optional: Turn on Retroactivity object for Broker Detail
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.
- SAP Managed Tags:
| User | Count |
|---|---|
| 12 | |
| 3 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 |
