Skip to content

cp_claimprofile_get_sc_book

Overview

Field Value
Schema dbo
Type Standalone procedure
Database SQL Server (eisD0P11)
Calling libraries SAIF.Business.View.Policy
Calling classes clsPolicySecurity

Purpose

cp_claimprofile_get_sc_book returns the active policies assigned to a loss-control consultant. The retrieved body matches the consultant by mail ID or wildcard pattern, excludes flat-cancel and inactive policies, and sorts the result by policy number and policy name.

Procedures / Methods

cp_claimprofile_get_sc_book

Field Value
Returns Result set of consultant-book policy rows
Called by clsPolicySecurity.GetPolicyListForSafetyConsultant
Description Returns the active policy list for a loss-control consultant.

Parameters

Parameter Direction Type Default Description
@losscontrol IN varchar(8) — Loss-control mail ID or wildcard pattern.

Business Rules

The retrieved database source embeds the following server-side rules.

Rule ID Summary
BR-VP-039 The procedure accepts either an exact mail ID or a wildcard pattern depending on whether the input contains %.
BR-VP-040 Only active policies whose most recent period is not flat-cancel and whose loss-control mail ID is populated are returned.

BR-VP-039 Safety-consultant book retrieval supports exact and wildcard loss-control mail IDs

Field Value
Statement The safety-consultant book procedure matches polTeam.LSS_MAIL_ID either exactly or with LIKE, depending on whether the supplied @losscontrol value contains %.
Trigger cp_claimprofile_get_sc_book executes.
Enforcement The WHERE clause checks charindex('%', @losscontrol) and applies either equality or LIKE rtrim(@losscontrol).
Outcome Callers can request a single consultant or a wildcard consultant pattern.
Source SQL Server retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json

BR-VP-040 Safety-consultant book excludes flat-cancel and inactive policies

Field Value
Statement The safety-consultant book returns only active policies whose most recent policy period is not FC and whose loss-control mail ID is present.
Trigger cp_claimprofile_get_sc_book executes.
Enforcement The WHERE clause requires T1.PLCY_PD_STAT_CD <> 'FC', wPol.ACTV_PLCY_FL = 'Y', and non-null and non-empty polTeam.Lss_mail_id.
Outcome Flat-cancel, inactive, and unassigned policies are excluded from the consultants book.
Source SQL Server retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json

Operations

The retrieved database source implements the following internal operations.

Operation Description Trigger
Match loss-control consultant identity Interpret the supplied loss-control argument as either an exact mail ID or a wildcard pattern. Caller invokes cp_claimprofile_get_sc_book.
Return the active safety-consultant book Return active, non-flat-cancel policies assigned to the matched loss-control consultant. The filtered query over policy, period, team, and office tables executes.

Match loss-control consultant identity

Field Value
Description Interpret the supplied loss-control argument as either an exact mail ID or a wildcard pattern.
Trigger Caller invokes cp_claimprofile_get_sc_book.
Source SQL Server retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json

Return the active safety-consultant book

Field Value
Description Return active, non-flat-cancel policies assigned to the matched loss-control consultant.
Trigger The filtered query over policy, period, team, and office tables executes.
Source SQL Server retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json

Calling code references

Class Method Source File
clsPolicySecurity GetPolicyListForSafetyConsultant SAIF.Business.View.Policy\clsPolicySecurity.vb

Source Code

SQL Server procedure source 
Create  procedure dbo.cp_claimprofile_get_sc_book
        @losscontrol varchar(8)
        --@region varchar(30),
        --@renewdate datetime
as
/*--------------------------------------------------------------------------------
Procedure Name:     cp_claimprofile_get_sc_book
Date Written:       02/25/2010
Author:         Jerry Todd (SAIF)
Installation:       SAIF Corporation
Source File Name:   cp_claimprofile_get_sc_book
Description:        This procedure gets Accounts
                    It is based on cp_lc_active_book
Modification Log:       
--------------------------------------------------------------------------------*/
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


Declare
    @bds_cutoff_dt datetime
    set @bds_cutoff_dt= eis_prod.dbo.fn_policy_cutoff_dt()

 select
  wPol.plcy_nam POLICY_NAME, 
------
  wPol.PLCY_NUM POLICY_NUMBER
------

from eis_prod.dbo.WCS_POLICY wPol
--  join eis_prod.dbo.
join eis_prod.dbo.WCS_POLICY_PERIOD T1
    on wPol.plcy_num=T1.plcy_num and T1.MOST_RECENT_PP_COV_FL='Y'
join eis_prod.dbo.WCS_POLICY_PERIOD_SUM T2
    on T1.PLCY_PD_ID=T2.PLCY_PD_ID
join eis_prod.dbo.wcs_policy_sum wSum 
    on wPol.plcy_id=wSum.plcy_id
-------------------------------
--get agency information
    --join EIS_PROD.dbo.WCS_AGENT wa 
    --  on wa.AGT_ID=wSum.AGT_ID
--------------------------------
JOIN eis_prod.dbo.wcs_office wo
    on wo.OFC_ID = wSum.ofc_id  
join eis_prod.dbo.wcs_policy_team polTeam 
    on polTeam.plcy_team_id=wSum.plcy_team_id

left outer join eis_prod.dbo.wcs_team_mbr polMembrDW
    on polTeam.MKTG_REP_MAIL_ID = polMembrDW.USER_MAIL_ID

join eis_prod.dbo.wcs_team_mbr polMembrLC
        on polTeam.LSS_MAIL_ID = polMembrLC.USER_MAIL_ID
left join eisd0p11.dbo.dbs_county cty on wPol.county_cd = cty.county_cd

Where ((polTeam.Lss_mail_id = @losscontrol
  and charindex('%', @losscontrol) = 0)
  or 
   (polTeam.Lss_mail_id like rtrim(@losscontrol)
 and charindex('%', @losscontrol) > 0)) 
--excludes Flat Cancel
AND T1.PLCY_PD_STAT_CD<>'FC'
AND wPol.ACTV_PLCY_FL='Y'



AND ((polTeam.Lss_mail_id is not null) and (polTeam.Lss_mail_id != ''))


order by  POLICY_NUMBER,POLICY_NAME


Return

Sources

  • SQL Server retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json
  • Calling code: SAIF.Business.View.Policy\clsPolicySecurity.vb