bp_clm_segmntn_complexity_score¶
Overview¶
| Field | Value |
|---|---|
| Schema | dbo |
| Type | SQL Server procedure (database object type: SQL_STORED_PROCEDURE) |
| Database | analyticsDb (SQL Server) |
| Calling libraries | SAIF.Data.NHibernate |
| Calling classes | ClaimScoreRepository |
Purpose¶
This procedure implements: bp_clm_segmntn_complexity_score (→ dbo.bp_clm_segmntn_complexity_score_dtl, dbo.bp_clm_segmntn_complexity_score_dtl_2026, eis_staging.dbo.bp_send_message — not retrieved**)
Procedures / Methods¶
bp_clm_segmntn_complexity_score (procedure)¶
| Field | Value |
|---|---|
| Returns | cursor/result set |
| Called by | SQL-001 (ClaimScoreRepository.GetClaimScoreCategory) |
No parameters were extracted from the retrieved signature; refer to the Source Code section below*.
Business Rules¶
Rules extracted from this procedure's logic:
| Rule ID | Rule | Condition | Action |
|---|---|---|---|
| PROC-RULE-020 | Each request gets a new model sequence number | Every call | NEXT VALUE FOR dbo.CSG_ModelSeqNum |
| PROC-RULE-021 | The request is persisted before scoring | Every call | INSERT into ANALYTICS_DB.dbo.CSG_CLM_SGMNTN_REQUEST_CLAIM with rtrim'd text parameters and PLCY_ID from EIS_PROD.dbo.WCS_POLICY |
| PROC-RULE-022 | Current model scores; 2026 model runs silently | After insert | EXEC _dtl (result) then _dtl_2026 (silent mode) |
| PROC-RULE-023 | The primary injury type code is stored only | @primaryInjuryTypeCode | Saved on the request row; not passed to the model |
| PROC-RULE-024 | Failure notification | CATCH block | Read BDS_CTL_MESSAGES MSG_ID = 28 and EXEC eis_staging.dbo.bp_send_message; RETURN error number |
These rules are documented in business-rules.md.
Operations¶
Operations and processes implemented in this procedure:
| Operation | Description | Trigger |
|---|---|---|
| Score claim complexity | Persist request and run segmentation models | SQL-001 (ClaimScoreRepository.GetClaimScoreCategory) |
These operations are also listed in operations-processes.md.
Source Code¶
bp_clm_segmntn_complexity_score (procedure)
-- Create Procedure
CREATE PROCEDURE [dbo].[bp_clm_segmntn_complexity_score]
@claimID decimal(15,0),
@claimTypeDesc varchar(20),
@policyNumber int,
@workerID decimal(15,0),
@gender varchar(5),
@injuryNatureDesc varchar(255),
@injuryeventDesc varchar(255),
@injurySourceDesc varchar(255),
@primarybodypartDesc varchar(255),
@dateofbirth datetime,
@homezipcode decimal(5,0),
@workerclasscode smallint,
@primaryInjuryTypeCode varchar(255) -- 04/08/26 New Parameter
AS
/*--------------------------------------------------------------------------------
Procedure Name: bp_clm_segmntn_complexity_score
Date Written: 08/06/2018
Author: Mike Mitchell (SAIF)
Installation: SAIF Corporation
Source File Name: bp_clm_segmntn_complexity_score.sql
Description: This procedure is called from the Claims entry system and returns
claim-complexity rating.
Change Log:
08/06/18 MICMIT New procedure.
04/08/26 MICMIT Adding a new parameter that will be passed into this procedure. It
will be saved into the CSG_CLM_SGMNTN_REQUEST_CLAIM table and passed
to the next procedure to be used in applying business rules, but
won't be sent to the actual model or saved into any additional tables.
07/16/26 MICMIT Added temporary code to call new 2026 model (running in silent mode) as
well as the existing production model.
---------------------------------------------------------------------------------*/
DECLARE @ReturnCode integer
DECLARE @Error integer
DECLARE @Parm1 varchar(255)
DECLARE @Parm2 varchar(255)
DECLARE @Parm3 varchar(255)
DECLARE @Parm4 varchar(255)
DECLARE @Parm5 varchar(255)
DECLARE @Parm6 varchar(255)
DECLARE @CSG_ModelSeqNum int
Declare @MsgId integer
Declare @Message varchar(255)
Declare @ShortMessage varchar(100)
Declare @PlcyId int
BEGIN TRY
SET @ReturnCode = 0
SET @Error = 0
-- Get next Model Seq Num
SET @CSG_ModelSeqNum = NEXT VALUE for dbo.CSG_ModelSeqNum
-- Get the PLCY_ID for the Policy that is getting passed to this procedure. It may be helpful later in reporting.
Set @PlcyId = (Select PLCY_ID
from EIS_PROD.dbo.WCS_POLICY
where PLCY_NUM = @policyNumber)
Insert into ANALYTICS_DB.dbo.CSG_CLM_SGMNTN_REQUEST_CLAIM
(CSG_MODEL_SEQ_NUM,
CLM_ID,
CLM_TYP_DESC,
PLCY_NUM,
WRKR_ID,
GENDER,
INJ_NATUR_DESC,
INJ_EVENT_SHRT_NAM,
INJ_SRC_SHRT_NAM,
PRIM_BODY_PART_DESC,
BIRTH_DT,
HOME_ZIP_CD,
IDXPROV_JCL_CD,
PLCY_ID,
PRIM_INJ_TYP_DESC) -- 04/08/26 New column (Named as a DESC instead of a CD - it really is more of a DESC)
Values (@CSG_ModelSeqNum,
@claimID,
rtrim(@claimTypeDesc),
@policyNumber,
@workerID,
rtrim(@gender),
rtrim(@injuryNatureDesc),
rtrim(@injuryeventDesc),
rtrim(@injurySourceDesc),
rtrim(@primarybodypartDesc),
@dateofbirth,
@homezipcode,
@workerclasscode,
@PlcyId,
@primaryInjuryTypeCode) -- 04/08/26 New parameter value
exec @ReturnCode=dbo.bp_clm_segmntn_complexity_score_dtl @CSG_ModelSeqNum, @claimID, @claimTypeDesc, @policyNumber, @workerID, @gender, @injuryNatureDesc,
@injuryeventDesc, @injurySourceDesc, @primarybodypartDesc, @dateofbirth, @homezipcode, @workerclasscode, @primaryInjuryTypeCode -- 04/08/26 Added new parameter
exec @ReturnCode=dbo.bp_clm_segmntn_complexity_score_dtl_2026 @CSG_ModelSeqNum, @claimID, @claimTypeDesc, @policyNumber, @workerID, @gender, @injuryNatureDesc,
@injuryeventDesc, @injurySourceDesc, @primarybodypartDesc, @dateofbirth, @homezipcode, @workerclasscode, @primaryInjuryTypeCode -- 04/08/26 Added new parameter
RETURN(@ReturnCode) -- Successful Execution
END TRY
BEGIN CATCH
SET @ReturnCode = ERROR_NUMBER()
SET @Message = (select MSG_DESC
from EIS_STAGING.dbo.BDS_CTL_MESSAGES
where MSG_ID = 28)
SET @Parm1 = 'bp_clm_segmntn_complexity_score'
SET @Parm2 = error_line()
SET @Parm3 = cast(@CSG_ModelSeqNum as varchar)
SET @Parm4 = error_message()
SET @Parm6 = CONVERT(varchar,getdate(),21)
-- Claims Complexity Module procedure &1 failed on line &2 with return code &3 and error message: &4
EXEC eis_staging.dbo.bp_send_message
@MsgId = 28,
@ProcName = @Parm1,
@Parm1 = @Parm1,
@Parm2 = @Parm2,
@Parm3 = @Parm3,
@Parm4 = @Parm4,
@Parm5 = @Parm1,
@Parm6 = @Parm6,
@Parm9 = 'nowait'
END CATCH
/* Test Procedure
Declare @ReturnCode int
Declare @claimID decimal(15,0) = 123456
Declare @claimTypeDesc varchar(20) = 'Disabling'
Declare @policyNumber int = 12354
Declare @workerID decimal(15,0) = 35424
Declare @gender varchar(5) = 'U'
Declare @injuryNatureDesc varchar(50) = 'Infection'
Declare @injuryeventDesc varchar(30) = 'Misc. Causes'
Declare @injurySourceDesc varchar(30) = 'Cumulative, NOC'
Declare @primarybodypartDesc varchar(40) = 'Arm, Lower'
Declare @dateofbirth datetime = '06/01/1973'
Declare @homezipcode decimal(5,0) = 97352
Declare @workerclasscode smallint = 7960
Declare @primaryInjuryTypeCode varchar(50) = 'Occupational Injury'
exec @ReturnCode=dbo.bp_clm_segmntn_complexity_score @claimID, @claimTypeDesc, @policyNumber, @workerID, @gender, @injuryNatureDesc,
@injuryeventDesc, @injurySourceDesc, @primarybodypartDesc, @dateofbirth, @homezipcode, @workerclasscode, @primaryInjuryTypeCode
*/
Related Documentation¶
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¶
- SQL Server:
dbo.bp_clm_segmntn_complexity_score - Legends CLI query executed 2026-09-24:
legends sqlserver query --name bp_clm_segmntn_complexity_score --output analysis/sqlserver-source/bp-clm-segmntn-complexity-score.json - Calling code:
Data/SAIF.Data.NHibernate/Mappings/Claims/Information/ClaimsAnalytics.hbm.xml - Calling code:
Data/SAIF.Data.NHibernate/Repositories/Claims/Information/ClaimScoreRepository.cs