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`.

```sql
-- 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 |

```sql
-- 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)

```sql
-- 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
```sql
INNER JOIN AFW_GeneralLedgerDepartment
  ON AFW_GeneralLedgerDepartment.GLDeptCode = AFW_BasicPolInfo.GLDeptCode
WHERE AFW_GeneralLedgerDepartment.ShortName IN ('Dept1', 'Dept8', 'Dept9')
```

### Get Policy Status Description
```sql
INNER JOIN AFW_PRCode RenewalRptFlag
  ON RenewalRptFlag.Code = AFW_BasicPolInfo.RenewalRptFlag
  AND RenewalRptFlag.AttrCode = 'PF'
```

### Get Employee Formatted Name
```sql
AFW_Employee.LastName + ', ' + AFW_Employee.FirstName AS FormattedName
```

### Filter Active Policies (Exclude Deleted)
```sql
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)
```