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):
4. LeasingCompany, StateAgency, SubAccount, Wrapup → predicate p.policy_num = :PolicyNumber or p.policy_relationship_policy_num = :PolicyNumber.
5. ExperienceCombination → predicate p.rate_account = :PolicyNumber.
6. 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).
7. Any other value → throw ApplicationException("Unsupported association type encountered when retrieving policy periods: …") (BR-012).
8. Bind :PolicyNumber and execute.
9. Consolidate supporting references.
10. Related business rules: BR-004, BR-005, BR-010, BR-011, BR-012, BR-013, BR-014, BR-016, BR-031, BR-032
11. Related SQL statements: SQL-078, SQL-079, SQL-080, SQL-081, SQL-082, SQL-083, SQL-088
12. Related tables: POLICY_PERIOD

Process flow

The following diagram visualizes this operation's control flow.

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
• Business rules
• SQL catalog
• Database tables

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