SQL-034 Policy association list

This SQL retrieves the full policy association list for a policy by unioning manual relationships, policy relationships, discount combinations, and experience combinations.

Classification

Field	Value
SQL ID	SQL-034
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	PolicyAssociationList
Criteria property	RetrieveSQL
Lifecycle method	GetPolicyAssociationList

Parameters

Parameter	Direction	Type	Max length	Description
:PolicyNumber	Input	Decimal	N/A	Policy number whose association list is requested.
:cppDate	Input	Date	N/A	Combined-policy-period date used to filter discount combinations.
:ecDate	Input	Date	N/A	Experience-combination date used to filter experience combinations.

Tables

Table	Access	Columns used
POLICY_RELATIONSHIP	SELECT	mbr_policy_num, affil_typ_cd, parent_policy_num
POLICY	SELECT	policy_num, policy_relationship_code, policy_relationship_policy_num, rate_account
COMBO_POLICY_PERIOD	SELECT	policy_num, active_member_flag, policy_period_eff_date, combo_period_seq_num, min_premium_member_flag
POLICY_PERIOD	SELECT	policy_num, policy_period_eff_date, policy_period_exp_date
ER_COMBINATION_POLICY	SELECT	policy_num, eff_date, exp_date, er_combination_seq_num

Sample SQL

-- Reconstructed from ManualAffiliationSQL UNION PolicyRelationshipSQL
--                    UNION DiscountCombinationSQL UNION ERCombinationSQL
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

• Retrieve policy associations

Business rules

• BR-VP-014: Source policy excluded from associations by default
• BR-VP-024: Default association dates

Sources

• SAIF.Business.View.Policy/PolicyAssociations.vb