cp_policy_in_group¶

Overview¶

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

Purpose¶

cp_policy_in_group answers the group-user access question by counting employer-group memberships for a policy. The retrieved body first decides whether the request should use parent-group, group-only, or group-plus-chapter logic, then returns a howMany count for the selected branch.

Procedures / Methods¶

cp_policy_in_group¶

Field	Value
Returns	Result set containing `howMany` membership count
Called by	`clsPolicySecurity.IsInGroup`
Description	Returns a membership count for the supplied policy, group key, and optional chapter filter.

Parameters¶

Parameter	Direction	Type	Default	Description
`@groupID`	IN	`varchar(10)`	—	Parent or group key to evaluate.
`@chapters`	IN	`varchar(10)`	—	Optional chapter filter.
`@policyNum`	IN	`int`	—	Policy number to test.

Business Rules¶

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

Rule ID	Summary
BR-VP-046	`@UseSql` is set to 1, 2, or 3 based on whether the input key matches a parent group, a group, or a group plus chapter filter.
BR-VP-047	Group-key detection ignores rows whose `GRP_TYP_CD = 'PARNT'`.
BR-VP-048	Each branch returns `count(*)`; the chapter filter is added only when `@UseSql = 3`.

BR-VP-046 Policy-in-group membership chooses a parent, group, or group-and-chapter query path¶

Field	Value
Statement	The membership-check procedure selects a parent-group, group-only, or group-plus-chapter query path by inspecting the supplied group key and whether a chapter string was supplied.
Trigger	`cp_policy_in_group` executes.
Enforcement	The procedure probes `WCS_EMPLOYER_GROUP` to set `@UseSql`, then runs the matching `if @UseSql = ...` `SELECT count(*)` block.
Outcome	Only one membership-count query runs for the supplied key and chapter combination.
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json`

BR-VP-047 Ordinary group-key detection excludes parent-group rows¶

Field	Value
Statement	When deciding whether the supplied key should be treated as a regular group key, the procedure excludes employer-group rows whose `GRP_TYP_CD = 'PARNT'`.
Trigger	`cp_policy_in_group` evaluates the group-key branch.
Enforcement	The `grp_assn_key` probe applies `AND grp_typ_cd <> 'PARNT'` before setting `@UseSql = 2`.
Outcome	Parent-group-only keys are not misclassified as ordinary group keys.
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json`

BR-VP-048 Policy-in-group returns membership as a count and applies chapter filtering only in branch three¶

Field	Value
Statement	The membership-check procedure returns a numeric `howMany` count for every branch, and the chapter filter applies only in the group-plus-chapter branch.
Trigger	`cp_policy_in_group` executes.
Enforcement	Each branch selects `count(*) howMany`, while only the `@UseSql = 3` branch adds `GroupCheck.chapCd like rtrim(@chapters)`.
Outcome	Callers receive a count-based membership answer, with chapter filtering limited to the explicit chapter branch.
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json`

Operations¶

The retrieved database source implements the following internal operations.

Operation	Description	Trigger
Resolve the policy-in-group query branch	Determine whether the supplied key should use the parent, group-only, or group-plus-chapter membership query.	Caller invokes `cp_policy_in_group`.
Count matching group memberships	Return a `howMany` count for the selected branch based on the policies most recent covered period and employer-group associations.	A branch selected by `@UseSql` executes.

Resolve the policy-in-group query branch¶

Field	Value
Description	Determine whether the supplied key should use the parent, group-only, or group-plus-chapter membership query.
Trigger	Caller invokes `cp_policy_in_group`.
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json`

Count matching group memberships¶

Field	Value
Description	Return a `howMany` count for the selected branch based on the policies most recent covered period and employer-group associations.
Trigger	A branch selected by `@UseSql` executes.
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json`

Calling code references¶

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

Source Code¶

SQL Server procedure source

Create Procedure dbo.cp_policy_in_group 
--  LIST PARAMETERS

    @groupID varchar(10),
    @chapters varchar(10), 
    @policyNum int
AS

/*--------------------------------------------------------------------------------
Procedure Name:   cp_policy_in_group
Date Written:     8/21/2001
Author:       Melinda Hillier (SAIF)
Installation:     SAIF Corporation
Source File Name: cp_policy_in_group
Description:      If the policy is a member of the group return
                  a count
Modifications:  05/09/02 - Removed the where condition for plcy_eff_dt
                    to be within 4 years of today's date.
        08/13/2003 - JERTOD:
            replaced EIS_SAIF_EMPR with VW_EIS_SAIF_EMPR
        09/17/2003 - JERTOD:
            replaced VW_EIS_SAIF_EMPR with WCS_Policy; changed ALTER to CREATE proc
            ALTER  Procedure dbo.cp_policy_in_group @groupNum int,
                @chapters varchar(10), @policyNum int
        12/01/2004 - JERTOD:
            changed the proc to eliminate the need to use
            a group pms number as a paramter
            changed @groupNum parameter to a string for @groupID
            left the old select in. DELETE IT AT NEXT MODIFICATION
        02/27/2006 - JERTOD: updated for BDS Release 10
        08/21/2013 - JERTOD: changed @UseSql triage to correctly evaluate group parents. they now have an extra row in the 
                                group table 
        8/21/2013: JERTOD changed the @GroupLevel evaluation to exclude group parents from being evaluated to a group
--------------------------------------------------------------------------------*/
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


DECLARE @UseSql int

set @UseSql=2

/* END THE PROCEDURE */



----------------------------------------------
--RELEASE 10

/************************/
/* INSPECT THE GROUPKEYCODE PASSED INTO PROC */
/************************/

--if we find the GroupKeyCode in the parent column we'll do the parent select
if (select count(distinct parent_grp) from eis_prod.dbo.wcs_employer_group 
    where ((parent_grp=rtrim(@groupID)) and (rtrim(@groupID)!='')))>0 
    begin
        set @UseSql=1
    end

--if we find the GroupKeyCode in the group column then we'll do the group select
if (select count(distinct grp_assn_key) from eis_prod.dbo.wcs_employer_group 
    where grp_assn_key=rtrim(@groupID)  AND grp_typ_cd <> 'PARNT' )>0 
    begin
        set @UseSql=2
    end

--unless the group key has only one entry, then we'll use its chapter code
--to discriminate it from the other keys 
--if it has more than one distinct chapter then we'll check all chapters
--unless the chapter code is specified
/*
if(select COUNT(DISTINCT WEG.pms_grp_sub_cd) 
        from eis_prod.dbo.WCS_EMPLOYER_GROUP WEG 
        where (WEG.grp_assn_key=@groupID) and (not weg.pms_grp_num is null))=1
begin
        set @chapters=(select DISTINCT WEG.pms_grp_sub_cd
        from eis_prod.dbo.WCS_EMPLOYER_GROUP WEG 
        where (WEG.grp_assn_key=@groupID) and (not weg.pms_grp_num is null))
        set @UseSql=3
end
*/

--if its a group and it has a chapter code we'll do the group chapter thingy
if (@UseSql=2) and (rtrim(@chapters)>'')
    begin
        set @UseSql=3
    end

/************************/
/* GROUP PARENT CODE PASSED IN */
/************************/
if @UseSql=1
SELECT 
--@UseSql   testnum,
count(*) howMany--,@UseSql whichOne
FROM
(select wGrp.parent_grp gKey from 
EIS_PROD.dbo.VW_WCS_POLICY_NO_SUB  wPol
join EIS_PROD.dbo.VW_WCS_POLICY_PD_NO_SUB  polPer
    on wPol.plcy_num=polPer.plcy_num and polPer.MOST_RECENT_PP_COV_FL='Y'
join EIS_PROD.dbo.WCS_POLICY_PERIOD_SUM  pPdSum
    on pPdSum.plcy_pd_id=polPer.plcy_pd_id
join EIS_PROD.dbo.WCS_EMPLOYER_GROUP wGrp
    on wGrp.EMP_GRP_ID=pPdSum.EMP_GRP_ID
where wPol.plcy_num=@policyNum) as GroupCheck

where GroupCheck.gKey=@GroupId

/************************/
/* GROUP WITHOUT A SPECIFIC CHAPTER CODE */
/************************/
if @UseSql=2
select 
--@UseSql   testnum,
count (*) howMany

FROM
(select wGrp.grp_assn_key gKey from 
EIS_PROD.dbo.VW_WCS_POLICY_NO_SUB  wPol
join EIS_PROD.dbo.VW_WCS_POLICY_PD_NO_SUB  polPer
    on wPol.plcy_num=polPer.plcy_num and polPer.MOST_RECENT_PP_COV_FL='Y'
join EIS_PROD.dbo.WCS_POLICY_PERIOD_SUM  pPdSum
    on pPdSum.plcy_pd_id=polPer.plcy_pd_id
join EIS_PROD.dbo.WCS_EMPLOYER_GROUP wGrp
    on wGrp.EMP_GRP_ID=pPdSum.EMP_GRP_ID
where wPol.plcy_num=@policyNum) as GroupCheck

WHERE 
GroupCheck.gKey=@GroupId


/************************/
/* GROUP WITH A SPECIFIC CHAPTER */
/************************/
if @UseSql=3
select 
--@UseSql   testnum,
count (*) howMany

FROM
(select wGrp.grp_assn_key gKey,wGrp.chap_cd chapCd,wGrp.emp_grp_id from 
EIS_PROD.dbo.VW_WCS_POLICY_NO_SUB  wPol
join EIS_PROD.dbo.VW_WCS_POLICY_PD_NO_SUB  polPer
    on wPol.plcy_num=polPer.plcy_num and polPer.MOST_RECENT_PP_COV_FL='Y'
join EIS_PROD.dbo.WCS_POLICY_PERIOD_SUM  pPdSum
    on pPdSum.plcy_pd_id=polPer.plcy_pd_id
join EIS_PROD.dbo.WCS_EMPLOYER_GROUP wGrp
    on wGrp.EMP_GRP_ID=pPdSum.EMP_GRP_ID
where wPol.plcy_num=@policyNum) as GroupCheck

WHERE 
GroupCheck.gKey=@GroupId
and GroupCheck.chapCd like rtrim(@chapters)

/* END THE PROCEDURE */



Return

Sources¶

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

cp_policy_in_group¶

Overview¶

Purpose¶

Procedures / Methods¶

cp_policy_in_group¶

Parameters¶

Business Rules¶

BR-VP-046 Policy-in-group membership chooses a parent, group, or group-and-chapter query path¶

BR-VP-047 Ordinary group-key detection excludes parent-group rows¶

BR-VP-048 Policy-in-group returns membership as a count and applies chapter filtering only in branch three¶

Operations¶

Resolve the policy-in-group query branch¶

Count matching group memberships¶

Calling code references¶

Source Code¶

Related documentation¶

Sources¶