Data Structures

Objectives

After completing this lesson, you will be able to:

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

Data Structures

While APMe 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 APMe.

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 FieldsDescription
BrokerNoUnique object number (Primary Key) used to reference the object. This number is generated automatically.
BrokerIdBroker Identifier. This must be unique in the Broker table.
Key Fields 
BrokerTypeContains the broker designation for a point in time.
IndividualIndicates whether the Broker is an individual versus an organization.
NameName of the Broker.
NPNNational 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
select b.brokerid, b.name,b.brokertype,b.npn 
from broker b
where b.brokerid='CC001'
Expand

Producer table Required and Key fields

Required FieldsDescription
ProducerNoUnique object number (Primary Key) used to reference the object. This number is generated automatically.
ProducerIdUnique identifier for the Producer.
NameFull name of the Producer.
Key Fields 
BrokerNoSourceLinks 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
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'
Expand

Vendor table Required and Key fields

Required FieldsDescription
VendorNoUnique object number (Primary Key) used to reference the Vendor. This number is generated automatically.
VendorIdUnique identifier for the Vendor.
Key Fields 
BrokerNoSourceLinks the vendor to its associated broker.
BankAcctBank account of the Vendor.
BankRtIdBank routing number of the Vendor’s bank.
IndividualIndicates whether the Vendor is an individual versus an organization.
MinPayoutThe minimum payout balance, owed to Vendor, that will trigger payment.
ReferenceIdA reference to the external Vendor identification.
TaxIdThe 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
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'
Expand

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 FieldsDescription
BrokerDetailNoUnique object number (Primary Key) used to reference the Broker Detail object. This number is generated automatically.
BrokerNoPoints to the parent Broker record for this Broker Detail
DatEffThe Effective Date of this broker detail
BrokerStatusThe status of the broker. Common options include Active, Inactive, or Termed.
ScheduleNoIdentifies the commission schedule.
Key Fields 
DatExpThe expiration date of the Broker Detail.
BrokerNoFieldWhen this field is populated, the Broker has an upline associated to it and will generate another TranHis/BrokerHistory payment record during Posting/Payout process.
BusTypeThe 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
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'

Expand

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 FieldsDescription
BrokerVendorNoPrimary Key for the record. This value is autogenerated by APMe when the record is added to the BrokerVendor table.
BrokerNoPoints to the associated Broker if the contents match the Broker ID.
VendorNoPoints to the associated Vendor if the contents match the Vendor ID.
DatEffEffective date of the relationship to the Payee.
Key Fields 
DatExpThe expiration date of the Broker Vendor relationship.
BusTypeThe 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
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'
Expand

Custom based Tables in APMe

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 APMe 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 FieldsDescription
CustomerNoPrimary Key for the record. This value is auto generated by APMe when the record is added to the Customer table.
CustomerIdUnique name for the Customer. This ID is typically mapped from the Enrollment System Id.
NameThe name of the customer.
Key Fields 
CustomerTypeIdentifier 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 FieldsDescription
CustPolicyNoPrimary Key for the record. This value is autogenerated when the record is added to the CustPolicy table.
CustomerNoThe Customer Identifier. Points to the associated Customer in the Customer table.
CarrierNoRequired field and is typically defaulted to a standard value in base configurations.
DatEffEffective date of the coverage indicated by the Policy Details.
PolicyIdAlthough 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 
DatExpThe expiration date of the policy.
PolStateThe state or province in which the policy was issued.
ProCodeThe Product Code of the policy.
ProStaProcessing status of the record. A value of 0 means the record is open; a value of 1 means the record is active.
TermPeriodContains the termination period of the policy and represents the last effective period of the policy.
TermTypeSignifies 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
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’
Expand

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 FieldsDescription
AddressNoPrimary Key for the Address record. This value is autogenerated.
AdrValIdentifies the entity (such as the Broker or Customer) to which the address is related.
AdrObjectThe type of entity (such as Broker or Customer) to which the address is related.
AdrTypeThe type of Address. HOME and OFFICE are typical types.
NameName of the Contact.
AdrLine1First line of the Address
CityCity of the Address
StateCdState of the Address
ZipCdZip /Postal Code
Key Fields 
DatEff/DatExpEffective/Expiration Dates of the address to track changes over time.
ProStaProcessing 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
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'
Expand

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 FieldsDescription
TranHeadNoThe Primary Key for the Transaction record. This value is autogenerated by APMe.
InpKeyConstructed Key to easily identify transactions and to allow subsequent transactions to aggregate into a single Tranhead.
CustomerNoPointer to Object Number of Customer record.
Key Fields – Run List 
RunListNoCompIdentifies the RunListNo of the Computation/Payout run.
RunListNoPostIdentifies the RunListNo of the Posting run.
RunListNoRepostIdentifies the RunList of the RepostTransactions process that updated this transaction.
Key Fields – Dates 
BillPeriodPeriod in which the transaction was last processed/reprocessed.
ActivityPeriodPeriod in which the transaction was initially processed
DatCurTypically the current period; aligns with the Premium Due Date
DatCurBCUsed to find the effective BrokerCustomer. This will default to the DatCur value.
DatCurBDUsed to find the effective BrokerDetail. This will default to the DatCur value.
DatCurBVDate used to match to the BrokerVendor during Posting.
Key Fields - Entity 
BrokerNoPrimaryThe primary BrokerNo of the transaction.
VendorNoPrimaryThe primary VendorNo of the transaction.
CustomerMatchNoThis is the pointer to the CustomerMatch used in the transaction processing.
BrokerNoUsed to relate the transaction to a broker. This field will only be populated if the Broker is passed in on the source transaction.
VendorNoUsed 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 
RepostFlagWhen set to 'Y', this TranHead should be re-posted.
ComputeFlagWhen set to 'Y', this TranHead should be processed by the Computation.
TranStatusIndicates the status of transaction Computation or Reposting.
Key Fields - Retroactivity 
AuditNoRetroReferences the Audit record of the configuration change that triggered the re-computation of this transaction.
InTranHeadNoRetroPopulated when the transaction is marked by either the RETROADD or RETROCHANGE RetroObject triggering event.
RetroObjectReferences 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 FieldsDescription
TranHisNoPrimary Key for the record. This value is autogenerated by APMe when the record is added to the TranHis table.
TranHeadNoPointer to the TranHead record associated to this Transaction History record.
BrokerNoPointer to the Broker record.
VendorNoPointer to the Vendor Record.
Key Fields 
RecTypeThe 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.
ObjMajPoints to the TranHis record that is the source of this record. Only used if the Transaction History was generated by a computation.
BrokerNoObjMajPoints 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
ParentTranHisNoPoints to the parent TranHis record, if applicable.
TranHisNoRetroThis 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.
BrokerCustomerNoObject number of the BrokerCustomer table used for this record. This is either zero or ObjectNo of the BrokerCustomer entity related to this record.
SchGridNoObject Number of the Schedule grid for this TranHis record.
TranSourceidentifies the source of the transaction. A full list of Transaction Sources can be found in the Data Dictionary.
RetroAdjIndicates 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

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

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 FieldsDescription
BrokerHistoryNoPrimary Key for the record. This value is autogenerated by APMe when the record is added to the BrokerHistory table.
BrokerNoPointer to Object Number of Broker record.
VendorNoPointer to Object Number of Vendor Record
Key Fields 
RecTypeThe 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.
AmtCommComputed Commission amount total.
RunListNoObject number of RunList associated to the Post process that created this record. This is updated during the update processing.
RunListNoVoucherObject number of RunList associated to the Voucher Generate process that updated the paid amounts of this record. This is updated during the GenVoucher process.
VoucherNoObject 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.
VoucherExcludeIndicates if this BrokerHistory should be included in the Voucher amount. Values include: 0 = No, 1 = Yes.
HoldStatusThe hold status for this transaction. Valid values include = 0 (Not Evaluated), 1 (Not Held), 2 (Held), 3 (Released) and 4 (Forfeited).
HoldReasonCdThis is the reason code for the hold. This is copied from the related BrokerHold.
HoldActionUserName of user that released/forfeited the hold for this transaction.
HoldActionDateDate that this transaction was released/forfeited.
HoldActionTimeTime that this transaction was released/forfeited.
BrokerHistoryNoHoldPoints to the BrokerHistory record responsible for the BrokerHistory to be held.
VoucherNoPaidIndicates the Voucher under which payment was released for this BrokerHistory record.
RunListNoPaidIndicates 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.
RetroAdjIndicates 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
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'
Expand

Log in to track your progress & complete quizzes