---
title: "SQL-001: Affiliated policy list retrieval"
summary: Retrieve affiliated policies for a policy and affiliate type.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-31
library: SAIF.Business.View.Policy
reference_type: package
---

# SQL-001: Affiliated policy list retrieval

`clsAffiliatedPolicyCollection` uses this call to list the policies affiliated with a given policy under one affiliation family. The affiliate type supplied by the caller selects which family of relationships the procedure walks, and an optional totals row can be appended for presentation.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-001 |
| **Operation type** | Read |
| **Execution method** | Stored procedure |
| **Database** | Oracle |
| **Schema** | `[unqualified]`** |
| **Command type** | StoredProcedure |
| **Business object** | `clsAffiliatedPolicyCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:in_policy_num` | Input | Decimal | not specified | Policy number whose affiliated-policy list is requested. |
| `:in_affiliate_type` | Input | String | 25 | Affiliate-type code, upper-cased from `clsPolicyData.AffiliateTypes`. |
| `:in_include_affil_totals` | Input | String | 5 | Literal `TRUE`/`FALSE` selected by `MyCriteria.IncludeAffiliateTotals`; controls whether a totals row is appended. |
| `recordset` | Output (Cursor) | REF CURSOR | N/A | Result set of affiliated policies. |

## Tables

No table is referenced directly in the .NET source. The tables read by the procedure body reside in the Oracle database and are described under [pk72846_view_policy](../procedures/pk72846_view_policy.md).

## Sample SQL

```sql
-- Stored procedure call (procedure body resides in the Oracle database - WCIS_DBA.pk72846_view_policy)
CALL pk72846_view_policy.getpolicyaffiliatelist(
  in_policy_num           => :in_policy_num,
  in_affiliate_type       => :in_affiliate_type,
  in_include_affil_totals => :in_include_affil_totals,
  recordset               => :recordset)  -- REF CURSOR output
```

## Operations and processes

- [Retrieve affiliated policies](../operations/retrieve-affiliated-policies.md)

## Business rules

- [BR-011: Affiliated-policy visibility depends on user access level and affiliate type](../business-rules.md#br-011-affiliated-policy-visibility-depends-on-user-access-level-and-affiliate-type)
- [BR-020: Affiliated policies are filtered to the requesting agent or agency when specified](../business-rules.md#br-020-affiliated-policies-are-filtered-to-the-requesting-agent-or-agency-when-specified)

> **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/clsAffiliatedPolicyCollection.vb`