---
title: "SQL-026: Policy association union query"
summary: Retrieve all policy associations across four association families.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-31
library: SAIF.Business.View.Policy
reference_type: package
---

# SQL-026: Policy association union query

`PolicyAssociationList` uses this statement to gather every policy associated with a given policy in one round trip. Four families of association — manual affiliations, system-derived policy relationships, premium-discount combinations, and experience-rating combinations — are combined with `UNION` into a single, uniformly shaped result.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-026 |
| **Operation type** | Read |
| **Execution method** | Inline SQL assembled from four concatenated constants |
| **Database** | Oracle |
| **Schema** | `[unqualified]`** |
| **Command type** | Text |
| **Business object** | `PolicyAssociationList` |
| **Criteria property** | N/A (module-level `Const`, not `IBusinessCriteria`) |
| **Lifecycle method** | `GetPolicyAssociationList` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:PolicyNumber` | Input | Decimal | not specified | Policy number whose associations are requested; bound once and reused across every `UNION` branch. |
| `:ecDate` | Input | Date | not specified | As-of date scoping the experience-rating combination branch; defaults to the data-source date. |
| `:cppDate` | Input | Date | not specified | As-of date scoping the discount-combination branch; defaults to the policy's latest policy-period effective date. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [POLICY_RELATIONSHIP](../database-tables.md#policy-relationship) | SELECT | PARENT_POLICY_NUM, MBR_POLICY_NUM, AFFIL_TYP_CD |
| [POLICY](../database-tables.md#policy) | SELECT | POLICY_NUM, POLICY_RELATIONSHIP_POLICY_NUM, POLICY_RELATIONSHIP_CODE, RATE_ACCOUNT |
| [COMBO_POLICY_PERIOD](../database-tables.md#combo-policy-period) | SELECT | POLICY_NUM, POLICY_PERIOD_EFF_DATE, COMBO_PERIOD_SEQ_NUM, ACTIVE_MEMBER_FLAG, MIN_PREMIUM_MEMBER_FLAG |
| [POLICY_PERIOD](../database-tables.md#policy-period) | JOIN | POLICY_NUM, POLICY_PERIOD_EFF_DATE, POLICY_PERIOD_EXP_DATE |
| [ER_COMBINATION_POLICY](../database-tables.md#er-combination-policy) | SELECT | POLICY_NUM, ER_COMBINATION_SEQ_NUM, EFF_DATE, EXP_DATE |

## Sample SQL

```sql
 SELECT   mbr_policy_num as PolicyNumber,
        'M.' || pr.affil_typ_cd as RelationshipType,
        DECODE(parent_policy_num, mbr_policy_num, 'Y', 'N') as IsParent
    FROM policy_relationship pr
   WHERE (   pr.parent_policy_num = :PolicyNumber
          OR pr.parent_policy_num =
                           (SELECT parent_policy_num
                              FROM policy_relationship
                             WHERE mbr_policy_num = :PolicyNumber))
UNION
 SELECT distinct parent_policy_num AS PolicyNumber,
        'M.' || pr.affil_typ_cd AS RelationshipType,
        case when parent_policy_num = (SELECT DISTINCT parent_policy_num
                                         FROM policy_relationship
                                        WHERE mbr_policy_num = :PolicyNumber
                                           OR parent_policy_num = :PolicyNumber) then 'Y'
             else 'N' end AS IsParent
  FROM policy_relationship pr
 WHERE (pr.parent_policy_num = :PolicyNumber
    OR pr.parent_policy_num = (SELECT DISTINCT parent_policy_num
                                 FROM policy_relationship
                                WHERE mbr_policy_num = :PolicyNumber))
UNION
SELECT   policy_num as PolicyNumber,
        p.policy_relationship_code,
         DECODE(policy_relationship_policy_num, policy_num, 'Y', 'N')
    FROM POLICY p
   WHERE (   p.policy_relationship_policy_num = :PolicyNumber
          OR p.policy_relationship_policy_num =
                          (SELECT policy_relationship_policy_num
                             FROM POLICY
                            WHERE policy_num = :PolicyNumber))
UNION
SELECT      cpp2.policy_num as PolicyNumber,
            'DiscountCombination',
         NVL(cpp2.min_premium_member_flag, 'N')
    FROM POLICY p
        ,combo_policy_period cpp
        ,policy_period pp
        ,combo_policy_period cpp2
   WHERE cpp.policy_num = :PolicyNumber
     AND cpp.active_member_flag = 'Y'
     AND cpp.policy_period_eff_date = pp.policy_period_eff_date
     AND cpp.policy_num = pp.policy_num
     AND pp.policy_period_eff_date <= :cppDate
     AND pp.policy_period_exp_date > :cppDate
     AND cpp.combo_period_seq_num = cpp2.combo_period_seq_num
     AND cpp2.active_member_flag = 'Y'
     AND cpp2.policy_num = p.policy_num
     AND cpp.active_member_flag = 'Y'
UNION
Select
    ecp2.POLICY_NUM as PolicyNumber,
    'ExperienceCombination',
     DECODE(ecp2.policy_num, p.rate_account, 'Y', 'N')
From
    policy p, er_combination_policy ecp, er_combination_policy ecp2
Where ecp.policy_num = :PolicyNumber
  and ecp.eff_date <= :ecDate
  and nvl(ecp.exp_date,:ecDate + 1) > :ecDate
  and ecp.policy_num = p.policy_num
  and ecp.er_combination_seq_num = ecp2.er_combination_seq_num
  and ecp2.eff_date <= :ecDate
  and nvl(ecp2.exp_date,:ecDate + 1) > :ecDate
```

## Operations and processes

- [Resolve policy associations](../operations/resolve-policy-associations.md)

## Business rules

- [BR-015: Unrecognized relationship codes are classified as not specified](../business-rules.md#br-015-unrecognized-relationship-codes-are-classified-as-not-specified)
- [BR-016: Manually maintained affiliations are distinguished by an M-prefixed code](../business-rules.md#br-016-manually-maintained-affiliations-are-distinguished-by-an-m-prefixed-code)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts. Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Sources

- `Business/SAIF.Business.View.Policy/PolicyAssociations.vb`