Skip to content

cp_get_agent_online_policy_list

Overview

Field Value
Schema eisD0P11.dbo
Type SQL Server stored procedure (BDS)
Database SQL Server (eisD0P11, referred to internally as "BDS")
Status ✅ found — retrieved via Legends CLI
Query executed legends sqlserver query --name cp_get_agent_online_policy_list
Calling libraries SAIF.Business.View.Policy
Calling classes clsPolicySecurity

Purpose

cp_get_agent_online_policy_list returns active and recently-closed policies for an agent or agency, with display flags for rate visibility, sales plan classification, pending-cancel status, current certificates, and group name, used by clsPolicySecurity.GetPolicyListForAgent.

Procedures / Methods

cp_get_agent_online_policy_list

Field Value
Returns Policy list rows with displayType, pendingCancel, salesPlan, groupName, viewableRate, currentCerts display flags
Called by clsPolicySecurity.GetPolicyListForAgent (clsPolicySecurity.vb:324-336)

Description: Combines an active-policy branch (displayType = 'O') and a closed-within-five-years branch (displayType = 'C') via UNION, deriving several display flags per row (see rules PR-BVP-056 through PR-BVP-062 in Business Rules below).

Parameters

Parameter Direction Type Default Description
@AgentNum IN int — Agent or agency number
@ForAgency IN char(3) — YES ⇒ treat @AgentNum as an agency number

Business Rules

Rules extracted from this procedure's logic:

Rule ID Rule Condition Action
PR-BVP-056 The list combines active policies and policies closed within the last five years active: ACTV_PLCY_FL = 'Y'; closed: ACTV_PLCY_FL = 'N' AND plcy_pd_eff_dt >= DateAdd(YYYY,-5,getDate()) UNION both branches; displayType 'O' for active, 'C' for closed
PR-BVP-057 @ForAgency switches the identifier between agency and agent upper(@ForAgency) = 'YES' → match wAgt.agncy_num = @AgentNum; otherwise match wAgt.agt_num = @AgentNum Restrict the result to that book of business
PR-BVP-058 Rate information is viewable only for the policy's own rate account or an in-book rate account rat_acct IS NULL → viewableRate = 'N'; rat_acct = plcy_num → 'Y'; otherwise 'Y' only when the rate account's agent of record is the same agent/agency Set viewableRate
PR-BVP-059 Sales plan is derived from the policy plan description 'Guaranteed Cost' → STAND; 'Assigned Risk' → ARP; 'State Agency' → STATE; otherwise RETRO Set salesPlan
PR-BVP-060 A policy in status 3 is flagged as pending cancel WCS_POLICY.PLCY_STAT_CD = '3' pendingCancel = 'Y' (added 12/22/2025, CYRSWI)
PR-BVP-061 Current certificates flag reflects existence of certificate detail rows matching wcs_certificate_detail rows exist currentCerts = 'Y'; the closed branch hard-codes 'N' for both currentCerts and viewableRate
PR-BVP-062 Group name is shown only for unexpired group memberships GRP_MBRSHP_EXP_DT > getdate() groupName = isnull(GRP_ASSN_CD, ' ')

These rules are documented in business-rules.md.

Operations

Operations and processes implemented in this procedure:

Operation Description Trigger
List agent online policies Returns active and recently closed policies for an agent or agency with display flags clsPolicySecurity.GetPolicyListForAgent (clsPolicySecurity.vb:324-336)

These operations are also listed in operations-processes.md with source reference back to this document.

Source Code

Transact-SQL stored procedure 
CREATE PROCEDURE dbo.cp_get_agent_online_policy_list
    @AgentNum  int,
    @ForAgency char(3)
AS
-- Active branch (displayType 'O')
SELECT ..., 'O' AS displayType,
       CASE WHEN wp.PLCY_STAT_CD = '3' THEN 'Y' ELSE 'N' END AS pendingCancel,
       -- 12/22/2025 CYRSWI: flag pending cancel
       CASE WHEN pln.descr = 'Guaranteed Cost' THEN 'STAND'
            WHEN pln.descr = 'Assigned Risk'   THEN 'ARP'
            WHEN pln.descr = 'State Agency'    THEN 'STATE'
            ELSE 'RETRO' END                                  AS salesPlan,
       isnull(eg.GRP_ASSN_CD, ' ')                            AS groupName
  FROM WCS_POLICY wp
  ...
  LEFT JOIN group tables ON ... AND ps.GRP_MBRSHP_EXP_DT > getdate()
 WHERE wp.ACTV_PLCY_FL = 'Y'
   AND ((upper(@ForAgency) =  'YES' AND wAgt.agncy_num = @AgentNum)
     OR (upper(@ForAgency) <> 'YES' AND wAgt.agt_num   = @AgentNum))

UNION

-- Closed-within-5-years branch (displayType 'C', currentCerts/viewableRate hard 'N')
SELECT ..., 'C' AS displayType, 'N' AS currentCerts, 'N' AS viewableRate, ...
 WHERE wp.ACTV_PLCY_FL = 'N'
   AND pd.plcy_pd_eff_dt >= DateAdd(YYYY, -5, getDate())
   AND ((upper(@ForAgency) =  'YES' AND wAgt.agncy_num = @AgentNum)
     OR (upper(@ForAgency) <> 'YES' AND wAgt.agt_num   = @AgentNum))

ORDER BY 4   -- policy_key

Sources

  • SQL Server BDS source: eisD0P11.dbo.cp_get_agent_online_policy_list
  • Legends CLI query executed 2026-08-31: legends sqlserver query --name cp_get_agent_online_policy_list
  • Calling code: SAIF.Business.View.Policy/clsPolicySecurity.vb:324-336