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	SELECT	PARENT_POLICY_NUM, MBR_POLICY_NUM, AFFIL_TYP_CD
POLICY	SELECT	POLICY_NUM, POLICY_RELATIONSHIP_POLICY_NUM, POLICY_RELATIONSHIP_CODE, RATE_ACCOUNT
COMBO_POLICY_PERIOD	SELECT	POLICY_NUM, POLICY_PERIOD_EFF_DATE, COMBO_PERIOD_SEQ_NUM, ACTIVE_MEMBER_FLAG, MIN_PREMIUM_MEMBER_FLAG
POLICY_PERIOD	JOIN	POLICY_NUM, POLICY_PERIOD_EFF_DATE, POLICY_PERIOD_EXP_DATE
ER_COMBINATION_POLICY	SELECT	POLICY_NUM, ER_COMBINATION_SEQ_NUM, EFF_DATE, EXP_DATE

Sample 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

Business rules

• BR-015: Unrecognized relationship codes are classified as not specified
• 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