Data Structures

Objectives

After completing this lesson, you will be able to:

Identify the main Broker based tables in APM.
Identify the role of customer based tables in APM.
Provide an overview of the transaction data and how it is processed in APM to calculate commissions payments.

Data Structures

While APM can be used for many applications, the system was originally developed as an insurance-specific commissioning system. Therefore, it provides entities to capture insurance specific information necessary to compute and pay commissions.

This lesson contains information about the data structure of the most commonly queried tables and fields in APM.

For each of the four topics listed below, we will provide a list of tables, a table relationship diagram, a table of required and key fields, and an example of a basic query.

Broker related tables
Customer related tables
Address related tables
Transaction related table

Broker Tables

The list below shows the tables related to brokers, producers, vendors, and their related data. While there may seem to be a lot of overlap among these tables, each has a specific purpose. Brokers, producers, and vendors can represent the same individual or corporate entity or three separate entities. Each entity has distinct roles in commission processing. To learn more about the relationship between brokers, producers and vendors.

Primary Broker Related Tables

TheBroker table contains information about hierarches and the assignment of customer and policy ownership.
TheBroker Detail table contains details about the broker.
The Producer table contains information about the producer’s credentials and other demographic data. For example, licenses and appointments are associated with the producer.
The Vendor is the entity that receives compensation. This table is used to assign payment vouchers and statements. A vendor is also called a payee .
The Broker Vendor table contains the Vendor assignment rules for the Broker.
The Producer License table contains all licensing information for the producer. Each license can qualify for one or more lines of business.
The Producer Appointment table contains all appointment information for the producer. Each appointment is for one or more lines of business.

Broker table Required and Key fields

Required Fields	Description
BrokerNo	Unique object number (Primary Key) used to reference the object. This number is generated automatically.
BrokerId	Broker Identifier. This must be unique in the Broker table.
Key Fields
BrokerType	Contains the broker designation for a point in time.
Individual	Indicates whether the Broker is an individual versus an organization.
Name	Name of the Broker.
NPN	National Producer Number. Sometimes used as the BrokerId.

Example Query

This query returns the Broker ID, Name, Broker Type, and NPN for the broker with Broker ID "CC001".

Code Snippet

1234
select b.brokerid, b.name,b.brokertype,b.npn 
from broker b
where b.brokerid='CC001'

Producer table Required and Key fields

Required Fields	Description
ProducerNo	Unique object number (Primary Key) used to reference the object. This number is generated automatically.
ProducerId	Unique identifier for the Producer.
Name	Full name of the Producer.
Key Fields
BrokerNoSource	Links the Producer to its associated Broker record.

Example Query

The query below shows a similar result to the Broker query example, but uses a join with the Producer table to also show the Producer ID.

Code Snippet

1234
select b.brokerid, p.producerid, b.name,b.brokertype,b.npn
from broker b
inner join producer p on p.brokernosource=b.brokerno
where b.brokerid='CC001'

Vendor table Required and Key fields

Required Fields	Description
VendorNo	Unique object number (Primary Key) used to reference the Vendor. This number is generated automatically.
VendorId	Unique identifier for the Vendor.
Key Fields
BrokerNoSource	Links the vendor to its associated broker.
BankAcct	Bank account of the Vendor.
BankRtId	Bank routing number of the Vendor’s bank.
Individual	Indicates whether the Vendor is an individual versus an organization.
MinPayout	The minimum payout balance, owed to Vendor, that will trigger payment.
ReferenceId	A reference to the external Vendor identification.
TaxId	The Tax Identification of the Vendor.

Example Query:

The query below shows a similar result to the Broker query example, but uses a join with the Vendor table to also show the Vendor ID.

Code Snippet

1234
select b.brokerid, v.vendorid, b.name,b.brokertype,b.npn
from broker b
inner join vendor v on v.brokernosource=b.brokerno
where b.brokerid='CC001'

Broker Detail table Required and Key Fields

The Broker Detail (BrokerDetail) record provides details for the parent Broker that can change over time. Multiple Active Broker Detail records can be attached to the Broker record at a time to allow the same broker to belong to multiple hierarchies.

Required Fields	Description
BrokerDetailNo	Unique object number (Primary Key) used to reference the Broker Detail object. This number is generated automatically.
BrokerNo	Points to the parent Broker record for this Broker Detail
DatEff	The Effective Date of this broker detail
BrokerStatus	The status of the broker. Common options include Active, Inactive, or Termed.
ScheduleNo	Identifies the commission schedule.
Key Fields
DatExp	The expiration date of the Broker Detail.
BrokerNoField	When this field is populated, the Broker has an upline associated to it and will generate another TranHis/BrokerHistory payment record during Posting/Payout process.
BusType	The Business Type. If populated, allows multiple active Broker Detail records with multiple hierarchies to be active for the same period.

Example Query:

The query below shows the Broker ID and name from the Broker record, and the Effective Date, Expiration Date, Business Type, Writing Agent, and Broker Type from the Broker Detail. Note that this query returns three records for broker Jeff Simpson; the three Effective Dates have different Business Types, Writing Agent statuses, and Broker Types.

Code Snippet

1234567
select b.brokerid, b.name, bd.dateff,bd.datexp, bd.bustype,
bd.writingagent, bd.brokertype
from brokerdetail bd
inner join broker b on bd.brokerno=b.brokerno
where b.brokerid='CC109'

Broker Vendor table Required and Key Fields

This record provides details of the relationship of the Vendor to the parent Broker. Since these details change over time, this record is effective dated. Multiple Active Broker Vendor records can be attached to the Broker record at a time to allow distinct payee relationships.

Required Fields	Description
BrokerVendorNo	Primary Key for the record. This value is autogenerated by APM when the record is added to the BrokerVendor table.
BrokerNo	Points to the associated Broker if the contents match the Broker ID.
VendorNo	Points to the associated Vendor if the contents match the Vendor ID.
DatEff	Effective date of the relationship to the Payee.
Key Fields
DatExp	The expiration date of the Broker Vendor relationship.
BusType	The Business Type. If populated, allows multiple active Broker Vendor records with multiple hierarchies to be active for the same period.

Example Query:

The query below returns the Broker ID and name from the Broker table, and the Vendor ID, Tax Name, Effective and Expiration Dates, and Processing Status from the Vendor table, using the Broker Number (BrokerNo) to match the broker and vendor. The screenshot shows that the broker, Joshua Simpson, has three associated vendors, as seen in the Producer to Pay Entity Relationships section.

Code Snippet

123456
select b.brokerid, b.name,v.vendorid, v.taxname, bv.dateff,
bv.datexp, bv.prosta
from brokervendor bv
inner join broker b on bv.brokerno=b.brokerno
inner join vendor v on bv.vendorno=v.vendorno
where b.brokerid='171334'

Custom based Tables in APM

The Customer table contains the customer, book of business, policy, or any identifiable part of business that is associated with Broker or several Brokers. The term Customer may be replaced by Employer, Book of Business, Policy, etc.

The CustPolicy table contains information on policies associated with a customer.
The Address table contains addresses for various entities, including the customer.
CustomerMatch is used to hold the matching groups for the specified Customer.
Customer Application is used to track customer applications.

Customer table Required and Key Fields

The customer record is the master record, as it relates to a single covered entity. An example of a customer mapped into APM would be an Employee Group, a member of an individual health plan, or a policy holder. The customer record typically only contains basic demographic information and does not contain date sensitive coverage details.

Required Fields	Description
CustomerNo	Primary Key for the record. This value is auto generated by APM when the record is added to the Customer table.
CustomerId	Unique name for the Customer. This ID is typically mapped from the Enrollment System Id.
Name	The name of the customer.
Key Fields
CustomerType	Identifier to help group the Customer for reporting purposes.

CustPolicy table Required and Key Fields

The table provides coverage details for the parent customer that can change over time. Multiple CustPolicy records can be attached to the Customer record at a time (including multiple active policies)

Required Fields	Description
CustPolicyNo	Primary Key for the record. This value is autogenerated when the record is added to the CustPolicy table.
CustomerNo	The Customer Identifier. Points to the associated Customer in the Customer table.
CarrierNo	Required field and is typically defaulted to a standard value in base configurations.
DatEff	Effective date of the coverage indicated by the Policy Details.
PolicyId	Although this value can be mapped from the Enrollment system, SAP Best Practice is to create a PolicyId with CustomerId, an element that helps define the coverage (Such as Product) and Effective Date. This constructed PolicyId will help Business Users and Agents easily identify source of commission payments.
Key Fields
DatExp	The expiration date of the policy.
PolState	The state or province in which the policy was issued.
ProCode	The Product Code of the policy.
ProSta	Processing status of the record. A value of 0 means the record is open; a value of 1 means the record is active.
TermPeriod	Contains the termination period of the policy and represents the last effective period of the policy.
TermType	Signifies that the policy has been terminated or cancelled, and is required if TermPeriod or DatExp is specified.

Example Query:

This query uses the CustomerNo field on the Customer and Customer Policy records to display a list of customers and policies in the state of Illinois:

Code Snippet

123456
select C.CustomerId, CP.PolicyId, CP.DatEff, CP.DatExp, CP.CarrierNo,
CP.PolState, CP.ProCode, CP.ProSta
from Customer C
inner join CustPolicy CP on C.CustomerNo=CP.CustomerNo
where C.PolState=’IL’

Address table Required and Key Fields

The entity can contain addresses for Customers, Brokers and Vendors. When querying the Address table, you should specify the AdrObject (CUSTOMER, BROKER or VENDOR) as well as the AdrVal, which is the Object Number of the entity to which the Address is related.

Required Fields	Description
AddressNo	Primary Key for the Address record. This value is autogenerated.
AdrVal	Identifies the entity (such as the Broker or Customer) to which the address is related.
AdrObject	The type of entity (such as Broker or Customer) to which the address is related.
AdrType	The type of Address. HOME and OFFICE are typical types.
Name	Name of the Contact.
AdrLine1	First line of the Address
City	City of the Address
StateCd	State of the Address
ZipCd	Zip /Postal Code
Key Fields
DatEff/DatExp	Effective/Expiration Dates of the address to track changes over time.
ProSta	Processing status of the record.

Example Query:

The query below uses the AdrObj and AdrVal fields to display address information for a single broker.

Code Snippet

12345
select b.brokerid, b.name,adr.adrtype, adr.adrline1, adr.city,
adr.statecd, adr.zipcd
from address adr
inner join broker b on adr.adrobject='BROKER' and adr.adrval=b.brokerno
where b.brokerid='CC001'

Primary Transaction Related Tables

The master transaction record is stored in the TranHead(short for Transaction Header) table, so named because it contains the header information for the transaction. This includes an auto-generated primary key called TranHeadNo and a pointer to the customer record called CustomerNo.
The TranHis (Transaction History) table holds the billing information for the vendor. It is used as the source information to calculate the commission amount. Key fields in this table include the Transaction History Number (TranHisNo), Transaction Head Number (TranHeadNo), Broker Number (BrokerNo), and Vendor Number (VendorNo).
The BrokerHistory table contains the summary of all broker balances, including earned and paid commissions by billing period. This is important for keeping track of outstanding balances, so they can be properly applied to current payouts.

TranHead Required and Key Fields

The TranHead, or Transaction Header, table contains the static header information for the billing transaction. It contains the following required and key fields.

Required Fields	Description
TranHeadNo	The Primary Key for the Transaction record. This value is autogenerated by APM.
InpKey	Constructed Key to easily identify transactions and to allow subsequent transactions to aggregate into a single Tranhead.
CustomerNo	Pointer to Object Number of Customer record.
Key Fields – Run List
RunListNoComp	Identifies the RunListNo of the Computation/Payout run.
RunListNoPost	Identifies the RunListNo of the Posting run.
RunListNoRepost	Identifies the RunList of the RepostTransactions process that updated this transaction.
Key Fields – Dates
BillPeriod	Period in which the transaction was last processed/reprocessed.
ActivityPeriod	Period in which the transaction was initially processed
DatCur	Typically the current period; aligns with the Premium Due Date
DatCurBC	Used to find the effective BrokerCustomer. This will default to the DatCur value.
DatCurBD	Used to find the effective BrokerDetail. This will default to the DatCur value.
DatCurBV	Date used to match to the BrokerVendor during Posting.
Key Fields - Entity
BrokerNoPrimary	The primary BrokerNo of the transaction.
VendorNoPrimary	The primary VendorNo of the transaction.
CustomerMatchNo	This is the pointer to the CustomerMatch used in the transaction processing.
BrokerNo	Used to relate the transaction to a broker. This field will only be populated if the Broker is passed in on the source transaction.
VendorNo	Used to relate the transaction to a vendor. This field will only be populated if the Vendor is passed in on the source transaction.
Key Fields – Process
RepostFlag	When set to 'Y', this TranHead should be re-posted.
ComputeFlag	When set to 'Y', this TranHead should be processed by the Computation.
TranStatus	Indicates the status of transaction Computation or Reposting.
Key Fields - Retroactivity
AuditNoRetro	References the Audit record of the configuration change that triggered the re-computation of this transaction.
InTranHeadNoRetro	Populated when the transaction is marked by either the RETROADD or RETROCHANGE RetroObject triggering event.
RetroObject	References the type of retroactivity that triggered the re-computation of this transaction.

TranHis Required and Key Fields

The TranHis (Transaction History) table holds the billing information for the vendor. It contains the following required and key fields.

Required Fields	Description
TranHisNo	Primary Key for the record. This value is autogenerated by APM when the record is added to the TranHis table.
TranHeadNo	Pointer to the TranHead record associated to this Transaction History record.
BrokerNo	Pointer to the Broker record.
VendorNo	Pointer to the Vendor Record.
Key Fields
RecType	The Record Type. This defines the type of the record for computation purposes. Example values include Writing Agent, Add-On, or Cap Reversal. A full list of record types can be found in the Data Dictionary.
ObjMaj	Points to the TranHis record that is the source of this record. Only used if the Transaction History was generated by a computation.
BrokerNoObjMaj	Points to the Broker of the TranHis which is represented by the ObjMaj. Used to uniquely identify TranHis associated with the same TranHead during retroactivity processing
ParentTranHisNo	Points to the parent TranHis record, if applicable.
TranHisNoRetro	This field is only populated for retroactive adjustment TranHis rows. It points to the original TranHis posted and is used to determine the current amount paid so the adjustment amount may be correctly calculated.
BrokerCustomerNo	Object number of the BrokerCustomer table used for this record. This is either zero or ObjectNo of the BrokerCustomer entity related to this record.
SchGridNo	Object Number of the Schedule grid for this TranHis record.
TranSource	identifies the source of the transaction. A full list of Transaction Sources can be found in the Data Dictionary.
RetroAdj	Indicates if this transaction was generated to retroactively adjust the transaction amounts.

Example Query

The query below returns the record type, the retroactivity adjustment status, the parent transaction history number, and the commission amount for a single transaction header.

Code Snippet

123456

SELECT RECTYPE,RETROADJ,PARENTTRANHISNO,AMTCOMM
FROM TRANHIS
WHERE TRANHEADNO =14785583550850000
ORDER BY TRANHISNO

BrokerHistory Required and Key Fields

The BrokerHistory table contains the summary of all broker balances, including earned and paid commissions by billing period. It contains the following required and key fields.

Required Fields	Description
BrokerHistoryNo	Primary Key for the record. This value is autogenerated by APM when the record is added to the BrokerHistory table.
BrokerNo	Pointer to Object Number of Broker record.
VendorNo	Pointer to Object Number of Vendor Record
Key Fields
RecType	The type of record for computation purposes. Example values include Writing Agent, Add-On, or Cap Reversal. A full list of record types can be found in the Data Dictionary.
AmtComm	Computed Commission amount total.
RunListNo	Object number of RunList associated to the Post process that created this record. This is updated during the update processing.
RunListNoVoucher	Object number of RunList associated to the Voucher Generate process that updated the paid amounts of this record. This is updated during the GenVoucher process.
VoucherNo	Object number of Voucher associated to updating this record with amounts paid. This is updated during the voucher processing with the object number of the Voucher table.
VoucherExclude	Indicates if this BrokerHistory should be included in the Voucher amount. Values include: 0 = No, 1 = Yes.
HoldStatus	The hold status for this transaction. Valid values include = 0 (Not Evaluated), 1 (Not Held), 2 (Held), 3 (Released) and 4 (Forfeited).
HoldReasonCd	This is the reason code for the hold. This is copied from the related BrokerHold.
HoldActionUser	Name of user that released/forfeited the hold for this transaction.
HoldActionDate	Date that this transaction was released/forfeited.
HoldActionTime	Time that this transaction was released/forfeited.
BrokerHistoryNoHold	Points to the BrokerHistory record responsible for the BrokerHistory to be held.
VoucherNoPaid	Indicates the Voucher under which payment was released for this BrokerHistory record.
RunListNoPaid	Indicates the RunList of the process that resulted in the update to the VoucherNoPaid field from null to the VocuherNo of the Voucher that released this BrokerHistory for payment.
RetroAdj	Indicates if this transaction was generated to retroactively adjust the transaction amounts.

SQL Example

The query below returns the Broker ID and Name from the Broker record, the Commission amount from the Broker History, and the Transaction Header number from a single Transaction Header.

Code Snippet

123456
select b.brokerid, b.name, bh.amtcomm, thd.tranheadno
from brokerhistory bh
inner join tranhead thd on bh.tranheadno=thd.tranheadno
inner join tranhis ths on ths.brokerhistoryno=bh.brokerhistoryno
inner join broker b on ths.brokerno=b.brokerno
where thd.tranheadno = '14713860951700000'

Next lesson