Skip to content

cp_claimprofile_get_sc_book

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_claimprofile_get_sc_book
Calling libraries SAIF.Business.View.Policy
Calling classes clsPolicySecurity

Purpose

cp_claimprofile_get_sc_book returns the active policy book for a loss-control (safety consultant) consultant, used by clsPolicySecurity.GetPolicyListForSafetyConsultant. It is based on the older cp_lc_active_book procedure per an in-source comment.

Procedures / Methods

cp_claimprofile_get_sc_book

Field Value
Returns POLICY_NAME, POLICY_NUMBER
Called by clsPolicySecurity.GetPolicyListForSafetyConsultant (clsPolicySecurity.vb:313-322)

Description: Supports wildcard matching on the loss-control mail id, excludes flat-cancelled periods, and lists only active policies with an assigned loss-control consultant (see rules PR-BVP-053 through PR-BVP-055 in Business Rules below). A local variable, @bds_cutoff_dt, is assigned from eis_prod.dbo.fn_policy_cutoff_dt() but never referenced in the returned select — an observed dead-local quirk.

Parameters

Parameter Direction Type Default Description
@losscontrol IN varchar(8) — Loss-control mail id; % wildcard supported

Business Rules

Rules extracted from this procedure's logic:

Rule ID Rule Condition Action
PR-BVP-053 The loss-control filter supports wildcard matching charindex('%', @losscontrol) > 0 Filter polTeam.Lss_mail_id LIKE rtrim(@losscontrol); otherwise exact match = @losscontrol
PR-BVP-054 Flat-cancelled periods are excluded from the safety-consultant book T1.PLCY_PD_STAT_CD = 'FC' Exclude the row
PR-BVP-055 Only active policies with an assigned loss-control consultant are listed wPol.ACTV_PLCY_FL = 'Y' AND Lss_mail_id not null/empty Include the policy

These rules are documented in business-rules.md.

Operations

Operations and processes implemented in this procedure:

Operation Description Trigger
List safety-consultant book Returns the active policy book for a loss-control consultant clsPolicySecurity.GetPolicyListForSafetyConsultant (clsPolicySecurity.vb:313-322)

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

Source Code

Transact-SQL stored procedure 
-- Based on cp_lc_active_book
CREATE PROCEDURE dbo.cp_claimprofile_get_sc_book
    @losscontrol varchar(8)
AS
DECLARE @bds_cutoff_dt datetime
SELECT  @bds_cutoff_dt = eis_prod.dbo.fn_policy_cutoff_dt()   -- declared, not used

SELECT wPol.POLICY_NAME, wPol.POLICY_NUMBER
  FROM ...
 WHERE (   (charindex('%', @losscontrol) > 0
            AND polTeam.Lss_mail_id LIKE rtrim(@losscontrol))
        OR (charindex('%', @losscontrol) = 0
            AND polTeam.Lss_mail_id = @losscontrol))
   AND T1.PLCY_PD_STAT_CD <> 'FC'     -- exclude flat cancel
   AND wPol.ACTV_PLCY_FL = 'Y'
   AND polTeam.Lss_mail_id IS NOT NULL
   AND rtrim(polTeam.Lss_mail_id) <> ''

Sources

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