---
title: "Configure dynamic period retrieval SQL"
summary: "Configure dynamic period retrieval SQL."
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Core
reference_type: package
operation_id: "OP-004"
trigger: "internal - invoked while executing OP-003"
---

# Configure dynamic period retrieval SQL

Configure dynamic period retrieval SQL describes the corresponding policy-core workflow and its BOF or Oracle touchpoints.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-004 |
| **Trigger** | internal - invoked while executing OP-003 |
| **Inputs** | `RetrievalModes` value, policy number, optional `StandardAssociationTypes` value |
| **Outputs** | a fully-formed SQL statement plus bound parameters |
| **Primary components** | ``clsPolicyPeriodCollection.Criteria.ConfigureDataRequest` / `clsPolicyPeriodBaseCollection.Criteria` (`clsPolicyPeriodCollection.vb`, `clsPolicyPeriodBaseCollection.vb`)` |
| **Entry point** | `Business\SAIF.Business.Policy.Core` |

## Steps
1. Switch on `RetrievalModes`.
2. `PolicyNumber` mode → `ConfigurePolicyNumberRetrieval` performs `String.Format(RetrieveSQL, "", " WHERE policy_period.policy_num = :PolicyNumber ")`.
3. `AssociationType` mode → `ConfigureAssociationTypeRetrieval` branches on `StandardAssociationTypes` ([BR-011](../business-rules.md#br-011-association-based-period-retrieval-uses-a-different-predicate-per)):
   - `LeasingCompany`, `StateAgency`, `SubAccount`, `Wrapup` → predicate `p.policy_num = :PolicyNumber or p.policy_relationship_policy_num = :PolicyNumber`.
   - `ExperienceCombination` → predicate `p.rate_account = :PolicyNumber`.
   - `ManualAffiliation`, `ManualWrapup` → build a `UNION` of member-side and parent-side joins on `POLICY_RELATIONSHIP` with `affil_typ_cd = '{0}'` interpolated from `clsAssociationTypeCode.GetAssociationTypeCode(AssociationType).Substring(1)`.
   - Any other value → throw `ApplicationException("Unsupported association type encountered when retrieving policy periods: …")` ([BR-012](../business-rules.md#br-012-an-association-type-outside-the-supported-set-is)).
4. Bind `:PolicyNumber` and execute.
5. Consolidate supporting references.
   - Related business rules: [BR-004](../business-rules.md#br-004-the-current-period-is-the-first-period-flagged), [BR-005](../business-rules.md#br-005-experience-rating-base-periods-are-those-whose-begin-date), [BR-010](../business-rules.md#br-010-a-policys-period-history-is-the-union-of), [BR-011](../business-rules.md#br-011-association-based-period-retrieval-uses-a-different-predicate-per), [BR-012](../business-rules.md#br-012-an-association-type-outside-the-supported-set-is), [BR-013](../business-rules.md#br-013-standard-premium-is-sourced-from-wcis-for-wcis), [BR-014](../business-rules.md#br-014-a-modern-policy-period-is-considered-issued-when), [BR-016](../business-rules.md#br-016-coverage-bound-status-is-sourced-from-wcis-for-modern), [BR-031](../business-rules.md#br-031-a-policy-period-is-flat-cancelled-when-its-effective), [BR-032](../business-rules.md#br-032-preliminary-audit-is-required-when-the-wcis-edit)
   - Related SQL statements: [SQL-078](../sql/SQL-078-saif-business-policy-core.md), [SQL-079](../sql/SQL-079-saif-business-policy-core.md), [SQL-080](../sql/SQL-080-saif-business-policy-core.md), [SQL-081](../sql/SQL-081-saif-business-policy-core.md), [SQL-082](../sql/SQL-082-saif-business-policy-core.md), [SQL-083](../sql/SQL-083-saif-business-policy-core.md), [SQL-088](../sql/SQL-088-saif-business-policy-core.md)
   - Related tables: [POLICY_PERIOD](../database-tables.md#policy_period)

## Process flow

The following diagram visualizes this operation's control flow.

```mermaid
flowchart TD
    Start([Trigger]) --> S1[Apply policy-core logic]
    S1 --> D{Validation or branch?}
    D -->|Continue| S2[Execute BOF SQL or Oracle call]
    D -->|Stop| EndErr([Return error or empty result])
    S2 --> EndOk([Return populated result])
```

## Failure and edge-case behavior
- Error, warning, and empty-result behavior follows the rules and SQL cited in the steps above.
- Stored-procedure-backed paths depend on Oracle-side logic outside this repository.**

## Related documentation
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **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.Policy.Core\clsPolicy.vb`
- `Business\SAIF.Business.Policy.Core\clsPolicyPeriodBase.vb`
- `Business\SAIF.Business.Policy.Core\clsPolicyPeriod.vb`
- `Business\SAIF.Business.Policy.Core\clsLegacyPolicyPeriod.vb`
- `Business\SAIF.Business.Policy.Core\clsCancelReasonOverride.vb`
- `Business\SAIF.Business.Policy.Core\WCISInquiry.vb`