seubert-claims/dev/DATABASE_REFERENCE.md

AFW Database Reference

Server: 63.89.3.224 Database: A1100080D1

---

Departments

ShortName	GLDeptCode	Full Name
Dept0	000	00-Administration
Dept1	001	01-Commercial Lines
Dept2	002	02-Private Client
Dept3	003	03-Surety
Dept4	004	04-Group Benefits
Dept5	005	05-Life Insurance
Dept6	006	06-Retirement Benefits
Dept7	007	DC Operations
Dept8	008	08-Select Commercial
Dept9	009	09-Transportation
Dept10	010	10-Real Estate/HealthCare
Dept11	011	Streamline Captive
Dept12	012	12-Construction

---

Key Tables

AFW_BasicPolInfo

Core policy information table.

Column	Type	Description
PolId	uniqueidentifier	Primary key - Policy ID
CustId	uniqueidentifier	Foreign key to AFW_Customer
PolNo	varchar(25)	Policy number
ShortPolNo	varchar(25)	Short policy number
PolEffDate	datetime	Policy effective date
PolExpDate	datetime	Policy expiration date
PolType	char(1)	Policy type
PolSubType	char(1)	Policy sub-type (P=Policy)
PolTypeLOB	varchar(150)	Line of business
Status	char(1)	Policy status (D=Deleted)
CoCode	varchar(3)	Parent company code
WritingCoCode	varchar(3)	Writing company code
ExecCode	varchar(3)	Executive/Producer code
CsrCode	varchar(3)	CSR code
BrokerCode	varchar(3)	Broker code
GLDivCode	varchar(3)	GL Division code
GLDeptCode	varchar(3)	GL Department code
GLBrnchCode	char(3)	GL Branch code
GLGrpCode	char(3)	GL Group code
BillMethod	varchar(1)	Billing method code
TypeOfBus	smallint	Type of business code
RenewalRptFlag	char(1)	Renewal/Status flag
IsContinuous	char(1)	Continuous policy flag
IsFinanced	varchar(1)	Financed flag
FullTermPremium	money	Full term premium amount
ChangedBy	varchar(3)	Last modified by
ChangedDate	datetime	Last modified date
EnteredDate	datetime	Entry date

AFW_Customer

Customer/insured information.

Column	Type	Description
CustId	uniqueidentifier	Primary key - Customer ID
CustNo	int	Customer number
FirmNameCust	varchar(75)	Firm/customer name
LastName	varchar(51)	Last name (individual)
FirstName	varchar(50)	First name (individual)
Addr1	varchar(75)	Address line 1
Addr2	varchar(75)	Address line 2
City	varchar(30)	City
State	char(2)	State code
ZipCode	varchar(9)	ZIP code
TypeCust	char(1)	Customer type
EMail	varchar(150)	Email address
BusPhone	varchar(7)	Business phone
ANotId	uniqueidentifier	Notation ID
Prod1Code	char(3)	Producer code
CsrCode	char(3)	CSR code
GLDivCode	char(3)	GL Division code
GLDeptCode	char(3)	GL Department code
Active	char(1)	Active status

AFW_Company

Insurance company/carrier information.

Column	Type	Description
CoId	uniqueidentifier	Primary key - Company ID
CoCode	char(3)	Company code
Name	varchar(75)	Company full name
ShortName	varchar(6)	Company short name
Type	char(1)	Company type
Status	char(1)	Status
NAIC	varchar(6)	NAIC code
ParentCoCode	char(3)	Parent company code

AFW_GeneralLedgerDepartment

Department lookup table.

Column	Type	Description
GLDeptCode	char(3)	Primary key - Department code
Name	varchar(25)	Department full name
ShortName	char(6)	Department short name (Dept1, Dept2, etc.)
Status	char(1)	Status
IsHide	char(1)	Hidden flag

AFW_Employee

Employee/personnel information.

Column	Type	Description
EmpCode	varchar(3)	Primary key - Employee code
LastName	varchar(20)	Last name
FirstName	varchar(16)	First name
MiddleName	varchar(11)	Middle name
ShortName	varchar(6)	Short name
Title	varchar(64)	Job title
EMail	varchar(150)	Email address
Status	char(1)	Status
IsRep	char(1)	Is representative flag
IsProd	char(1)	Is producer flag
EmpSupervisorCode	varchar(3)	Supervisor employee code
DefaultGLDivCode	char(3)	Default GL Division
DefaultGLDeptCode	char(3)	Default GL Department

AFW_PolicyPersonnel

Links policies to additional personnel (reps, executives).

Column	Type	Description
PolId	uniqueidentifier	Policy ID
PolPId	uniqueidentifier	Policy Personnel ID
EmpCode	char(3)	Employee code
EmpType	char(1)	Employee type (R=Rep, P=Producer/Exec)
IsPrimary	char(1)	Is primary flag (Y/N)
Percentage	float	Commission percentage
FlatAmount	money	Flat commission amount
Position	smallint	Position/order
EnteredDate	datetime	Entry date

AFW_PRCode

Lookup/reference codes table.

Column	Type	Description
AttrCode	char(3)	Attribute code (category)
Code	varchar(10)	Code value
Description	varchar(150)	Description
SortNo	smallint	Sort order
IsHide	char(1)	Hidden flag

---

Lookup Values (AFW_PRCode)

Type of Business (AttrCode = 'TB')

Code	Description
0	All
1	Personal Lines
2	Commercial Lines
3	Non Property & Casualty
4	Benefits
5	Life
6	Health
7	Financial Services

Billing Method (AttrCode = 'BM')

Code	Description
A	Agency bill
F	1st Installment Agency Bill, Remaining Direct Bill
P	Direct bill

Policy Status/Renewal Flag (AttrCode = 'PF')

Code	Description
A	Active
C	Cancelled
E	Expired
N	Non-Renewed
R	Renewed
W	Rewritten
T	Not taken
I	Include
Q	Quote

---

Employee Assignment to Policies

Employees can be assigned to policies in two ways: Primary Assignment (direct fields on the policy) and Additional Personnel (via the AFW_PolicyPersonnel table).

Primary Assignment (AFW_BasicPolInfo)

Every policy has three primary employee fields:

Field	Role	Description
ExecCode	Executive/Producer	Primary producer/account executive responsible for the policy
CsrCode	CSR	Customer Service Representative handling day-to-day service
BrokerCode	Broker	External broker (optional, can be NULL)

These are required fields (except BrokerCode) and link to AFW_Employee.EmpCode.

-- Example: Get primary personnel for a policy
SELECT
    bp.PolNo,
    exec.LastName + ', ' + exec.FirstName AS Executive,
    csr.LastName + ', ' + csr.FirstName AS CSR,
    broker.LastName + ', ' + broker.FirstName AS Broker
FROM AFW_BasicPolInfo bp
INNER JOIN AFW_Employee exec ON exec.EmpCode = bp.ExecCode
INNER JOIN AFW_Employee csr ON csr.EmpCode = bp.CsrCode
LEFT JOIN AFW_Employee broker ON broker.EmpCode = bp.BrokerCode

Additional Personnel (AFW_PolicyPersonnel)

Policies can have additional personnel assignments beyond the primary exec/csr/broker. These are stored in AFW_PolicyPersonnel.

Employee Types (EmpType)

Code	Description	Usage
P	Producer/Executive	Additional executives, account managers
R	Representative	Additional CSRs, service reps
B	Broker	Additional brokers
T	Sales Center Rep	Telemarketing/sales center staff

Primary vs Additional

IsPrimary	Meaning
Y	Primary person of this type for the policy
N	Additional/secondary person of this type

A policy can have:

• One primary Producer (EmpType='P', IsPrimary='Y')
• Multiple additional Producers (EmpType='P', IsPrimary='N')
• One primary Rep (EmpType='R', IsPrimary='Y')
• Multiple additional Reps (EmpType='R', IsPrimary='N')

Commission Fields

Field	Description
Method	Commission method: A=Amount/Percentage, P=Percentage only
Percentage	Commission split percentage
FlatAmount	Flat commission amount
FeeMethod	Fee calculation method
FeePercentage	Fee percentage
ProductionCreditSplitPercentage	Production credit split %
IsSuspended	Whether commission is suspended

-- Example: Get all personnel for a policy with their types
SELECT
    pp.EmpType,
    CASE pp.EmpType
        WHEN 'P' THEN 'Producer/Exec'
        WHEN 'R' THEN 'Representative'
        WHEN 'B' THEN 'Broker'
        WHEN 'T' THEN 'Sales Center Rep'
    END AS RoleDescription,
    pp.IsPrimary,
    emp.LastName + ', ' + emp.FirstName AS Name,
    pp.Percentage AS CommissionPct
FROM AFW_PolicyPersonnel pp
INNER JOIN AFW_Employee emp ON emp.EmpCode = pp.EmpCode
WHERE pp.PolId = @PolId
ORDER BY pp.EmpType, pp.IsPrimary DESC, pp.Position

Customer-Level Personnel (AFW_CustAddPersonnel)

Customers can also have default personnel assignments that may cascade to new policies.

Column	Description
CustId	Customer ID
EmpCode	Employee code
TypeOfEmp	Employee type (P=Producer, R=Rep)
TypeOfBus	Type of business this applies to
IsPrimary	Primary flag

Employee Role Flags (AFW_Employee)

Employees have role flags indicating their capabilities:

Flag	Description
IsRep	Can be assigned as a Representative
IsProd	Can be assigned as a Producer
IsTeleMarketer	Telemarketing role
IsOther	Other role type

Common combinations:

• IsRep=Y, IsProd=Y - Can serve as both producer and rep (most common)
• IsRep=N, IsProd=Y - Producer only
• IsRep=Y, IsProd=N - Rep/CSR only

Query: Get All Personnel for a Policy (Full)

-- Primary personnel from AFW_BasicPolInfo
SELECT
    'Primary Exec' AS Assignment,
    bp.ExecCode AS EmpCode,
    exec.LastName + ', ' + exec.FirstName AS Name
FROM AFW_BasicPolInfo bp
INNER JOIN AFW_Employee exec ON exec.EmpCode = bp.ExecCode
WHERE bp.PolId = @PolId

UNION ALL

SELECT
    'Primary CSR' AS Assignment,
    bp.CsrCode,
    csr.LastName + ', ' + csr.FirstName
FROM AFW_BasicPolInfo bp
INNER JOIN AFW_Employee csr ON csr.EmpCode = bp.CsrCode
WHERE bp.PolId = @PolId

UNION ALL

-- Additional personnel from AFW_PolicyPersonnel
SELECT
    CASE
        WHEN pp.EmpType = 'P' AND pp.IsPrimary = 'Y' THEN 'Add''l Exec (Primary)'
        WHEN pp.EmpType = 'P' AND pp.IsPrimary = 'N' THEN 'Add''l Exec'
        WHEN pp.EmpType = 'R' AND pp.IsPrimary = 'Y' THEN 'Add''l Rep (Primary)'
        WHEN pp.EmpType = 'R' AND pp.IsPrimary = 'N' THEN 'Add''l Rep'
        WHEN pp.EmpType = 'B' THEN 'Add''l Broker'
    END AS Assignment,
    pp.EmpCode,
    emp.LastName + ', ' + emp.FirstName
FROM AFW_PolicyPersonnel pp
INNER JOIN AFW_Employee emp ON emp.EmpCode = pp.EmpCode
WHERE pp.PolId = @PolId

---

Key Views

View Name	Description
AFW_PolicyBizVw	Policy business summary view
AFW_PolicyVw	Full policy view
AFW_PolicyShortVw	Abbreviated policy view
AFW_CustomerShortVw	Abbreviated customer view
AFW_EmployeeVw	Employee view
AFW_CompanyVw	Company view
AFW_BrokerVw	Broker view
AFW_BusinessUnitVw	Business unit view
AFW_InvoiceTransactionVw	Invoice transaction view
AFW_PolicyTransactionFactVw	Policy transaction fact view

---

Common Query Patterns

Filter by Department

INNER JOIN AFW_GeneralLedgerDepartment
  ON AFW_GeneralLedgerDepartment.GLDeptCode = AFW_BasicPolInfo.GLDeptCode
WHERE AFW_GeneralLedgerDepartment.ShortName IN ('Dept1', 'Dept8', 'Dept9')

Get Policy Status Description

INNER JOIN AFW_PRCode RenewalRptFlag
  ON RenewalRptFlag.Code = AFW_BasicPolInfo.RenewalRptFlag
  AND RenewalRptFlag.AttrCode = 'PF'

Get Employee Formatted Name

AFW_Employee.LastName + ', ' + AFW_Employee.FirstName AS FormattedName

Filter Active Policies (Exclude Deleted)

WHERE AFW_BasicPolInfo.Status != 'D'
  AND PolSubType = 'P'

---

Table Relationships

AFW_Customer (CustId)
    └── AFW_BasicPolInfo (CustId) ──┬── AFW_Company [ParentCompany] (CoCode)
                                    ├── AFW_Company [WritingCompany] (WritingCoCode)
                                    ├── AFW_GeneralLedgerDepartment (GLDeptCode)
                                    ├── AFW_Employee [ExecCode] (EmpCode)
                                    ├── AFW_Employee [CsrCode] (EmpCode)
                                    ├── AFW_PRCode [TypeOfBusiness] (TypeOfBus, AttrCode='TB')
                                    ├── AFW_PRCode [BillMethod] (BillMethod, AttrCode='BM')
                                    ├── AFW_PRCode [Status] (RenewalRptFlag, AttrCode='PF')
                                    └── AFW_PolicyPersonnel (PolId)
                                            └── AFW_Employee (EmpCode)