Quickly Load Data into Agent Performance Management (APM) using Import Formats

[PhillipButts]

It has been said on many occasions, "Your system is only as good as your data". This statement becomes reality when reviewing Payout reports and payment discrepancies are found due to incorrect configuration data. Getting your IT department to send a file to correct the bad data (for example: Enrollment data) may take days (or even weeks) due to stringent Internal Change Control processes. With APM's built in ETL (Extract, Transform and Load) functionality, the process of correcting data can only take a few minutes.

Note: Please review your internal processes for loading data in APM to ensure Compliance.

In this Blog, we will create an Import Format to load Manual Adjustments into APM.

DATA MODEL

The first step is to understand the data model of the entity that you are trying to import and ultimately post.

Each entity has system defined, required fields that must be populated for the record to be posted.  Additionally, user configured Entity Edits can be defined to make additional fields required for successful posting/processing.

To review the required fields:

• Navigate to the APM Data Dictionary which can be located under the  ? in the top right menu (under Internal Resources).
• Enter the entity that you are reviewing in the Search Bar (AdjustmentHis) for Manual Adjustments
• Select Export AdjustmentHis to PDF
• Leave Include Child/Parent entities unchecked
• Select Confirm. 

Figure 1: Data Dictionary Export 

• The AdjustmentHis Data Dictionary entry will open.
• The required fields are indicated by an " * "

  Figure 2: Required Fields 

• Take Note of the fields that are marked as required and create an Excel Spreadsheet with each required Field as a Column Header. You can ignore System Generated (AdjustmentHisNo) and Defaulted (ProSta) fields.
• "Some" translation will need to be performed to create the Column Headers. For Example: BrokerNo is a required field but you will need to create the Column Header as "BrokerId".
• For fields that require a Code such as AdjType, navigate to the Code Type Search screen and identify the Code Type Stored Value(s) that you will need to populate on the spreadsheet.

   Figure 3: Code Type Stored Values

• Identify additional fields that you want to add to the Manual Adjustment Import Format (including any fields that may be needed for future use).
  • For example:
    • Financial Entity (FinEntity) is not required but is always used for financial tracking.
    • Customer and PolicyId are not required but are often used to tie an Adjustment to a specific Member (Policy).
• List of Required fields as determined from the Data Dictionary:
  • BrokerId
  • VendorId
  • BillYear
  • BillMonth
  • Amount
  • PremMonth
  • PremYear
  • AdjType
  • AdjDesc
• Create a spreadsheet with the column headers with the same name (including case) as the fields that you want to include in the Import Format. Column order does not matter.
• Include a row (or several rows) of data. Having data in the spreadsheet will help confirm the mapping in the Import Format.

Figure 4: Import Format Spreadsheet Template

• Navigate to Integration > Configuration > Import Format Search and select + to create a new Import Format.
• Select User Defined on the next screen.
• Enter the details of the Import Format on the next screen:
  • ImportFormatId: Unique Name of the Import Format
  • Name: User Friendly name of the Import Format
  • InEntityName: Name of the Table that you will be importing data to
  • File Type:  In this case, we are selecting MS Excel but other options include delimited and Fixed Length.
  • Header Rows: Number of rows before the actual data begins. It is always a good idea to use a header row with Column Names as it makes the mapping process easier.
  • Trailer Rows: Number of rows after the data stops. This is typically set to 0.
  • Sample File:  Navigate to and upload the sample file created above.
  • Optional Settings: Additional Advanced settings can be configured in the Optional Setting Section but those settings are out of scope for this exercise.

Figure 5: Import Format Creation

• Select Next
• The next screen shows the header fields that have imported.  Review these fields for accuracy and select Next.
• The Field Mapping screen will now display that allows you to see the fields that have been imported on the right and the available fields in InAdjustmentHis on the left. Notice there is data from the spreadsheet in the fields on the right which confirms the data was imported properly into the Import Format.Figure 6: Field Mapping
• Since we have used the actual field names in the spreadsheet, we can automatically map the fields from the spreadsheet directly to the InAdjustmentHis fields by simply clicking the Red Cone/Blue Cube Icon as shown below.
• There is now data from the spreadsheet template visible in the InAdjustmentHis fields on the left of the screen. You can confirm the mappings from this view.

Figure 7: Automatically Mapping Fields

• Additionally, you can manually add mappings via the Simple Text or Expression.
• Simply click the Simple Text or Expression box and drag it to the field that you want to add a mapping.
• In this case, we will add a Simple Text to the Comments field and type in a text Comment.

Figure 8: Adding a Simple Text Mapping to a Field

• Select Complete.
• The Import Format Search Screen is displayed. Navigate to the Import Format that you just created and activate it.
• The Import Format is now ready to be used.

Loading Data

• Populate the Spreadsheet that you created as a template with additional rows as needed.
• Navigate to the Integration Portal: Integration > Inbound Data > File Search . Select + to create a new Infile.
• Populate the necessary fields on the InFile Detail screen
  • InFileId: Unique Name for the InFile
  • Name: User friendly name for the Infile (Can be the same as the InFileId)
  • Payout Type Id: Id of the Payout Type that will process and pay the adjustments.
  • Process Year: Automatically populated based on the system period.
  • Process Month: Automatically populated based on the system period.
  • Date Effective: Automatically populated based on the current System Date
• Select Save.
• Select Ready.

Figure 9: Creating an InFile

• Once the InFile has been created, Select the + under the Import Request section.

Figure 10: Creating an Import Request

• Select your Import Format from the dropdown selection. 
• Upload Type should be client since you created the Adjustment Spreadsheet on your local computer drive.
• Navigate to the directory where the Adjustment spreadsheet is located and select the file.

Figure 10: Uploading a File

• Select Next.
• Select Complete.

Figure 11: Importing a File

• Review the import request for any Errors or Warnings.
• Select Post to post the Adjustments from InAdjustmentHis to AdjustmentHis.
• Review the errors and make corrections (if any).
• Run Payout and review the adjustments to make sure they were vouchered and on the Agent's statement.

APM's Import Format functionality is a powerful tool to help maintain data quality in your system as well as provide quick Payment Adjustments to Agents if a data issue is found. 

Feel free to drop a comment or question on this blog. We would love to hear from you!

Please Follow additional APM resources at:

APM WorkZone Site (Requires invitation) 

For Invitation Please email phillip.butts@sap.com or dean.patterson@sap.com 

APM Blogs  

APM Help Docs