SAIF.Business.View.Policy business rules¶

This page captures business rules enforced by SAIF.Business.View.Policy.

BR-VP-001 Valid policy number required¶

Field	Value
Statement	A policy number must be greater than zero to be considered valid.
Trigger	Any retrieval/validation of a `clsPolicyData` object.
Enforcement	`clsPolicyData.AddBusinessRules` → `Rules.AddRule(AddressOf ValidPolicyNum, "PolicyNum")`.
Outcome	`IsValid` is `False` and a broken-rule message ("Error: Invalid policy number.") is attached when `PolicyNum <= 0`.
SQL	SQL-VP-008
Source	`SAIF.Business.View.Policy\clsPolicyData.vb`

BR-VP-002 Secure-policy AD resource gate¶

Field	Value
Statement	Access to a policy flagged with the SAIF secure-policy code (`SecurePolicyCode = clsConstants.SAIF`) requires the current security principal to be authorized for the AD resource `SAIFpolicyInfo`.
Trigger	`clsPolicySecurity.IsPolicyValidForUser(clsPolicyData)`.
Enforcement	`clsPolicySecurity.vb` (`IsPolicyValidForUser`), calling `SecurityPrincipal.IsAuthorized("SAIFpolicyInfo")`.
Outcome	Access denied (`False`) regardless of user type when the check fails.
Source	`SAIF.Business.View.Policy\clsPolicySecurity.vb`

BR-VP-003 Policy validity and non-zero policy number required for access¶

Field	Value
Statement	A policy must be valid (`IsValid = True`) and have a non-zero policy number to be accessible.
Trigger	`clsPolicySecurity.IsPolicyValidForUser`.
Enforcement	`clsPolicySecurity.vb`.
Outcome	Access denied when either condition fails.
Source	`SAIF.Business.View.Policy\clsPolicySecurity.vb`

BR-VP-004 Unconditional access for internal staff user types¶

Field	Value
Statement	SAIF employees, MCO users, medical-provider users, and bookkeepers are always granted access to any (non-SAIF-secured) policy.
Trigger	`IsPolicyValidForUser` `Select Case` on `UserType.UserType`.
Enforcement	`clsPolicySecurity.vb`.
Outcome	`hasAccess = True`.
Source	`SAIF.Business.View.Policy\clsPolicySecurity.vb`

BR-VP-005 Agent ownership match required¶

Field	Value
Statement	An agent may access a policy only if the agent's user number matches the policy's agent number or the policy's agency sequence number.
Trigger	`IsPolicyValidForUser` for `UserTypes.Agent`.
Enforcement	`clsPolicySecurity.vb`.
Outcome	Access granted only on a match.
Source	`SAIF.Business.View.Policy\clsPolicySecurity.vb`

BR-VP-006 Policyholder ownership affiliation match required¶

Field	Value
Statement	A policyholder may access a policy only if it is their own policy or a policy for which they are recognized as a parent affiliate (per `Associations.Parents`).
Trigger	`IsPolicyValidForUser` for `UserTypes.PolicyHolder`, delegating to `IsPolicyValidForParentPolicy`.
Enforcement	`clsPolicySecurity.vb`.
Outcome	Access granted only when the requested policy number equals the user's own number, or is found among the user's parent-affiliation associations.
Source	`SAIF.Business.View.Policy\clsPolicySecurity.vb`

BR-VP-007 Group membership required¶

Field	Value
Statement	A group user may access a policy only if the policy is a member of that user's configured group/chapter, per the `cp_policy_in_group` stored procedure.
Trigger	`IsPolicyValidForUser` for `UserTypes.Group`.
Enforcement	`clsPolicySecurity.IsInGroup`.
Outcome	Access granted only when the stored procedure returns a positive count.
SQL	SQL-VP-033
Source	`SAIF.Business.View.Policy\clsPolicySecurity.vb`

BR-VP-008 Loss-control consultant assignment match required¶

Field	Value
Statement	A loss-control consultant may access a policy only if they are the assigned Loss Control Consultant team member for that policy (matched by masked or unmasked username prefix).
Trigger	`IsPolicyValidForUser` for `UserTypes.LossControl`.
Enforcement	`clsPolicySecurity.vb` (`CanSafetyConsultantAccessPolicy`).
Outcome	Access denied if no team assignment exists or the username does not match.
Source	`SAIF.Business.View.Policy\clsPolicySecurity.vb`

BR-VP-009 Fee-auditor vendor policy audit-record match required¶

Field	Value
Statement	A fee auditor may access a policy only if there is a non-expired audit record (`PA_AUDIT`) for that policy associated with the auditor's vendor number.
Trigger	`IsPolicyValidForUser` for `UserTypes.Auditor`.
Enforcement	`clsPolicySecurity.CanAuditorAccessPolicy`.
Outcome	Access granted only when the query returns a positive count.
Tables	PA_AUDIT, PA_AUDITOR, PA_POLICY_PERIOD
SQL	SQL-VP-037
Source	`SAIF.Business.View.Policy\clsPolicySecurity.vb`

BR-VP-010 Demo-user agency agent data masking¶

Field	Value
Statement	When displaying agency/agent data to a demo user (`UserAccessLevel` is one of the `Demo*` levels), non-blank agency/agent names other than `"SAIF"` are replaced with the literal `"SAIF APPOINTED AGENT"`, and contact fields (phone, address, email) are replaced with masking constants.
Trigger	Any read of `clsAgency` properties (`AgencyName`, `AgentName`, `PhoneFormatted`, etc.) or `Fill`.
Enforcement	`clsAgency.vb`.
Outcome	PII is not exposed to demo users.
SQL	SQL-VP-001
Source	`SAIF.Business.View.Policy\clsAgency.vb`

BR-VP-011 Demo-user address contact data masking¶

Field	Value
Statement	When displaying policy address/contact data to a demo user (`DemoAgent`/`DemoGroup`/`DemoPolicyholder`), contact name, phone, email, and Officer of Notice are replaced with masking constants (and the Officer of Notice lookup query is skipped entirely).
Trigger	Read of `clsPolicyAddress.ContactName`/`ContactPhone`/`ContactEmailAddress`/`PolicyEmailAddress`/`OfficerOfNotice`.
Enforcement	`clsPolicyAddress.vb`.
Outcome	PII is not exposed to demo users; the Officer-of-Notice database round trip is avoided for demo users.
Tables	BUSINESS_AFFILIATE, AFFILIATE
SQL	SQL-VP-007
Source	`SAIF.Business.View.Policy\clsPolicyAddress.vb`

BR-VP-012 Team-assignment masking preserves real username for authorization¶

Field	Value
Statement	When a team assignment's `MaskData` flag is set, the team member's username, full name, phone, and extension are replaced with masking constants, while the real username is preserved internally as `UserIdName` for downstream access checks (see BR-VP-008).
Trigger	`clsTeamAssignment.Fill`.
Enforcement	`clsTeamAssignment.vb`.
Outcome	Masked display values are exposed to callers, but authorization matching (loss-control consultant check) still uses the real username.
SQL	SQL-VP-024
Source	`SAIF.Business.View.Policy\clsTeamAssignment.vb`

BR-VP-013 Affiliated-policy agent agency filter¶

Field	Value
Statement	An affiliated policy is included in a `clsAffiliatedPolicyCollection` only if it matches the requested agent number/agency sequence-number filter (or if no filter, i.e. both are zero, is supplied).
Trigger	`clsAffiliatedPolicyCollection.AfterRetrieve`.
Enforcement	`clsAffiliatedPolicyCollection.vb`.
Outcome	Rows not matching the agent/agency filter are excluded from the collection even though they were returned by the stored procedure.
SQL	SQL-VP-004
Source	`SAIF.Business.View.Policy\clsAffiliatedPolicyCollection.vb`

BR-VP-014 Non-employer account number must be non-zero¶

Field	Value
Statement	A non-employer account number must be non-zero to be considered valid.
Trigger	Any retrieval/validation of `clsNonEmployerAccount`.
Enforcement	`clsNonEmployerAccount.AddBusinessRules` → `Rules.AddRule(AddressOf IsValidAccountNumber, "AccountNumber")`.
Outcome	`IsValid` is `False` when `AccountNumber = 0`.
SQL	SQL-VP-032
Source	`SAIF.Business.View.Policy\clsNonEmployerAccount.vb`

BR-VP-015 Specific and current requests prefer policy-period employer-liability rows¶

Field	Value
Statement	Specific and current limit requests first read `POLICY_PERIOD_EMP_LIAB` rows joined to `EMPLOYERS_LIABILITY_LIMITS` when an in-effect override exists for the requested policy period.
Trigger	`getCurrentLimitsForPolicyNum` or `getSpecificLimitsForPolicyNum` executes for a policy period with matching `POLICY_PERIOD_EMP_LIAB` rows.
Enforcement	Each procedure opens the cursor with a first `SELECT` or `UNION` branch over `POLICY_PERIOD_EMP_LIAB` and `EMPLOYERS_LIABILITY_LIMITS` before fallback branches.
Outcome	The returned cursor exposes policy-specific employer-liability limits instead of standard defaults.
Stored procedure cross-reference	coveragelimits_pkg.BR-VP-015
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json`

BR-VP-016 Limit retrieval falls back to standard factor rows when no specific override exists¶

Field	Value
Statement	If no qualifying policy-specific employer-liability row exists, the package returns standard limit rows where `standard_limits_flag = 'Y'` and the factor effective dates cover the evaluation date.
Trigger	A current, specific, future, or all-limits lookup executes without a matching specific period override.
Enforcement	Fallback branches join `EMPLOYERS_LIABILITY_LIMITS` to `EMPLOYERS_LIABILITY_FACTORS` and require `standard_limits_flag = 'Y'` plus `NOT EXISTS` checks against `POLICY_PERIOD_EMP_LIAB`.
Outcome	Callers still receive formatted limit values even when a policy period has no explicit override row.
Stored procedure cross-reference	coveragelimits_pkg.BR-VP-016
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json`

BR-VP-017 Future and historical lookups use the policy periods own effective context¶

Field	Value
Statement	Future and older-period lookups compare effective-dated factor rows against the requested policy period or its expiration date rather than always using `SYSDATE`.
Trigger	`getFutureLimitsForPolicyNum` or the historical branches inside `getSpecificLimitsForPolicyNum` execute.
Enforcement	`getFutureLimitsForPolicyNum` compares adjustment windows to `pp.policy_period_eff_date`, and historical branches in `getSpecificLimitsForPolicyNum` use `PeriodEffDate`, `policy_period_exp_date`, or `policy_period_non_wcis` dates.
Outcome	Returned limits reflect the requested future or historical policy period, not only the current-day configuration.
Stored procedure cross-reference	coveragelimits_pkg.BR-VP-017
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json`

BR-VP-018 Gap detection ignores flat-cancel and prospect periods¶

Field	Value
Statement	Gap detection ignores policy periods with status `FC` or `PPROS` before comparing one periods cancel or expiration date to the next periods effective date.
Trigger	`getgaps` or `getgapindicator` executes.
Enforcement	Both routines filter out `policy_period_status_code` values `FC` and `PPROS` and compare only the remaining periods.
Outcome	Only meaningful coverage gaps are returned or counted.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-018
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-019 Assigned-risk policy periods mask risk-type and plan data until the info page prints¶

Field	Value
Statement	For policy periods with status `RF`, the package suppresses `risk_type_code` and plan description values until `SP72347_INFO_PAGE_PRINTED_YN(policy_num, policy_period_eff_date)` returns `Y`.
Trigger	`getpolicyperiod` or `getpolicyperiods` retrieves an `RF` policy period.
Enforcement	Both procedures use `CASE` expressions that return empty strings for `RF` rows when `SP72347_INFO_PAGE_PRINTED_YN` is not `Y`.
Outcome	Assigned-risk policy-period detail stays masked until the information page is printed.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-019
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-020 Claim-address requests may fall back to the policy address¶

Field	Value
Statement	If a claim-address request finds no active claim location and `ingetmailaddrwhennotfound = 'Y'`, the procedure changes the requested location code to `POL` before opening the cursor.
Trigger	`getpolicyaddress` executes for address type `CLM` and no current `CLM` location exists.
Enforcement	The procedure counts qualifying `CLM` locations, then assigns `v_addr_type := 'POL'` when fallback is enabled and the count is zero.
Outcome	Callers receive the policy mailing or location address instead of an empty claim-address result.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-020
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-021 Address retrieval prefers current contacts and the latest effective location row¶

Field	Value
Statement	Policy-address lookups join only active contacts, constrain location rows to the latest qualifying effective date, and return a single top-ranked row; location-sequence lookups also rank contact types before applying `ROWNUM <= 1`.
Trigger	`getpolicyaddress` or `getpolicyaddressbylocseqnum` executes.
Enforcement	`getpolicyaddress` filters `contact_exp_date IS NULL`, constrains the effective date with a `MAX(loc_eff_date)` subquery, and limits the result with `ROWNUM <= 1`; `getpolicyaddressbylocseqnum` orders contact types and also applies `ROWNUM <= 1`.
Outcome	The package returns one current address and contact record rather than multiple stale rows.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-021
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-022 Affiliate-type classification follows a fixed hierarchy¶

Field	Value
Statement	Affiliate-type resolution checks for manual policyholder affiliations first, then wrap and leasing relationships, then subaccount and state-agency relationships, then experience-combination memberships, and finally premium-discount combinations.
Trigger	`getpolicyaffiliatetype` executes.
Enforcement	The function evaluates each affiliation family in sequence and returns immediately on the first matching `SELECT ... INTO`.
Outcome	A policy receives the first matching affiliate classification in the packages explicit precedence order.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-022
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-023 Parent-policy resolution depends on the resolved affiliate type¶

Field	Value
Statement	Parent-policy resolution chooses the parent lookup strategy from the affiliate type returned by `getpolicyaffiliatetype`, including manual affiliation, relationship, combination, premium-discount, or self-parent fallback paths.
Trigger	`getparentpolicy` executes.
Enforcement	The function branches on `v_affil_type` and queries `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, `combo_policy_period`, or the input policy number.
Outcome	Callers receive the appropriate parent policy number for the resolved affiliation model.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-023
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-024 Affiliate total rows are optional and vary by affiliation family¶

Field	Value
Statement	Affiliate lists add a single totals row only when `in_include_affil_totals = 'TRUE'`, and the totals label and selection logic vary by affiliate family.
Trigger	`getpolicyaffiliatelist` executes.
Enforcement	The procedure builds `v_totals_description` and `v_affil_type`, then includes totals-row `UNION` branches only when `UPPER(in_include_affil_totals) = 'TRUE'`.
Outcome	Callers can opt into or suppress the family-specific totals row for affiliate displays.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-024
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-025 Wrap-up legal-name lookup climbs from member to relationship policy to rate account¶

Field	Value
Statement	When a wrap-up member references a different relationship policy, `getwrapuplegalname` first loads that policy legal name and then, if a rate account exists, replaces it with the rate-account policy legal name.
Trigger	`getwrapuplegalname` executes for a wrap-up member policy.
Enforcement	The function selects `policy_relationship_policy_num` and `legal_name`, then conditionally selects the relationship policy and finally the `rate_account` policy.
Outcome	The returned legal name reflects the top-level wrap-up or rate-account owner when available.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-025
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-026 Policy header retrieval normalizes null financial amounts to zero¶

Field	Value
Statement	Policy-header retrieval wraps `current_balance` and `premium_estimate` in `NVL(..., 0)` before returning them.
Trigger	`getpolicy` executes.
Enforcement	The `SELECT` list uses `NVL(current_balance, 0)` and `NVL(premium_estimate, 0)`.
Outcome	Callers receive zero instead of null for the retrieved balance and premium-estimate fields.
Stored procedure cross-reference	pk73094_policy_data.BR-VP-026
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json`

BR-VP-027 Account-name lookup ranks active and parent policies first¶

Field	Value
Statement	Account-name lookup ranks policy candidates so active or pending-cancel statuses sort first, cancelled next, parent or self policies ahead of member policies, and later cancel dates ahead of earlier ones.
Trigger	`get_account_name` executes with a non-null account number.
Enforcement	The cursor query computes `policy_status_order`, `policy_relationship_order`, and `cancel_date`, orders by those values, and assigns the first fetched `legal_name` to `v_account_name`.
Outcome	The returned account name comes from the preferred policy row for that account number.
Stored procedure cross-reference	pk73094_policy_data.BR-VP-027
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json`

BR-VP-028 Policy waiver count equals the number of waiver-detail rows for the policy¶

Field	Value
Statement	The waiver-count helper returns the number of `WAIVER_DETAIL` rows whose `POLICY_NUM` equals the input policy number.
Trigger	`get_policy_waiver_count` executes.
Enforcement	The function runs `SELECT COUNT(*) INTO v_count FROM WAIVER_DETAIL WHERE POLICY_NUM = PolicyNum` and returns `v_count`.
Outcome	Callers receive a simple numeric waiver count for the policy.
Stored procedure cross-reference	pk82033_thirdparty.BR-VP-028
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk82033_thirdparty.json`

BR-VP-029 Pending-cancel policies only derive the demanded status¶

Field	Value
Statement	When the policy status group is `PENDCA`, the function derives cancellation status only by checking for a demand row with `COLLECTION_ACT_RESPONSE_CODE IS NULL`.
Trigger	`SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `PENDCA`.
Enforcement	The main program routes `PENDCA` to `pending_cancel_status`, which calls only `look_for_demand_status`.
Outcome	A pending-cancel policy returns `Demanded` when an open demand exists; otherwise it returns null.
Stored procedure cross-reference	sp72268_get_cancel_status.BR-VP-029
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-030 Cancelled policies use a fixed precedence order for derived collection statuses¶

Field	Value
Statement	For cancelled policies, the function derives collection status in a fixed order: Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, and finally Closed.
Trigger	`SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `CANCEL`.
Enforcement	`cancel_status` calls the local derivation procedures only while `v_cancel_status` remains null, then defaults to `Closed`.
Outcome	The first matching collection condition determines the returned cancellation status.
Stored procedure cross-reference	sp72268_get_cancel_status.BR-VP-030
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-031 Defaulted and demanded statuses come from policy-demand response codes¶

Field	Value
Statement	The function derives `Defaulted` from a `POLICY_DEMAND` row whose `COLLECTION_ACT_RESPONSE_CODE = 'D'` and derives `Demanded` from a `POLICY_DEMAND` row whose response code is null.
Trigger	`look_for_default_status` or `look_for_demand_status` executes.
Enforcement	Each local procedure opens a cursor over `POLICY_DEMAND` with its respective response-code filter and assigns the corresponding status when a row is found.
Outcome	Demand and default statuses are driven directly by the collection-action response state.
Stored procedure cross-reference	sp72268_get_cancel_status.BR-VP-031
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-032 Application policies return saifQuote only when WCIS does not own the submission¶

Field	Value
Statement	For application-status policies, the function returns `saifQuote` only when `pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N'` and no prior status has been assigned.
Trigger	`SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `APP`.
Enforcement	The main program routes `APP` to `saifquote_status`, which tests `pk73102_policy.does_wcis_own_submission` before assigning `v_cancel_status := 'saifQuote'`.
Outcome	Application policies are labeled `saifQuote` only for non-WCIS-owned submissions.
Stored procedure cross-reference	sp72268_get_cancel_status.BR-VP-032
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-033 Information-page status is returned as Y or N¶

Field	Value
Statement	The function returns `Y` when `sp72253_info_page_printed(in_policy_num, in_policy_period_eff_date)` is true and `N` otherwise.
Trigger	`SP72347_INFO_PAGE_PRINTED_YN` executes.
Enforcement	The function checks the wrapped Boolean function in an `IF ... THEN RETURN 'Y' ELSE RETURN 'N' END IF` block.
Outcome	Callers receive a character flag instead of a PL/SQL Boolean.
Stored procedure cross-reference	sp72347_info_page_printed_yn.BR-VP-033
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72347_info_page_printed_yn.json`

BR-VP-034 Bound-policy status is returned as Y or N¶

Field	Value
Statement	The function returns `Y` when `sp72325_chk_if_policy_bound(in_policy_num, in_policy_period_eff_date)` is true and `N` otherwise.
Trigger	`sp73089_get_policy_bound_flag` executes.
Enforcement	The function sets `flag := 'Y'` or `N` based on the wrapped Boolean function and returns `flag`.
Outcome	Callers receive a character flag instead of a PL/SQL Boolean.
Stored procedure cross-reference	sp73089_get_policy_bound_flag.BR-VP-034
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73089_get_policy_bound_flag.json`

BR-VP-035 External-client email lookup requires trimmed case-insensitive matches on mail ID and last name¶

Field	Value
Statement	The function selects an email address only from `SYSTEM_USER_CONTRACTOR` rows whose trimmed `LAST_NAME` and `SYSTEM_USER_ID` both match the trimmed uppercase input values.
Trigger	`sp73341_external_client_lookup` executes.
Enforcement	The cursor query filters with `upper(trim(last_name)) = upper(trim(in_last_name))` and `upper(trim(system_user_id)) = upper(trim(in_mailid))`.
Outcome	Only contractor rows matching both supplied identity values qualify.
Stored procedure cross-reference	sp73341_external_client_lookup.BR-VP-035
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json`

BR-VP-036 External-client lookup returns null when no contractor row is found¶

Field	Value
Statement	When the contractor cursor fetch does not find a row, the function assigns `v_email_address := null` before returning.
Trigger	`sp73341_external_client_lookup` executes and the cursor fetch is not found.
Enforcement	The body checks `if su_contractor_cur%notfound then v_email_address := null; end if;`.
Outcome	Callers receive null instead of an exception or stale email value when no contractor row matches.
Stored procedure cross-reference	sp73341_external_client_lookup.BR-VP-036
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json`

BR-VP-037 Group policy-list retrieval selects a parent, group, or group-and-chapter branch from the input key¶

Field	Value
Statement	The group-policy-list procedure classifies the input as a parent-group query, a group-key query, or a group-key-plus-specific-chapter query before running the corresponding `SELECT` branch.
Trigger	`cp_claimprofile_get_group_policy_list` executes.
Enforcement	The procedure sets `@UseSql` by probing `WCS_EMPLOYER_GROUP.PARENT_GRP`, `GRP_ASSN_KEY`, and the presence of `@SpecificChapterCode`, then runs the matching `if @UseSql = ...` query block.
Outcome	Only one query branch returns rows for the requested group context.
Stored procedure cross-reference	cp_claimprofile_get_group_policy_list.BR-VP-037
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_group_policy_list.json`

BR-VP-038 Group policy-list retrieval keeps only active or still-effective memberships as of the report date¶

Field	Value
Statement	The group-policy-list procedure returns only rows where the policy is active and either the most recent coverage flag is set or the last in-group period still covers the report date; group-membership expiration must also be on or after `@rptDate`.
Trigger	Any of the query branches in `cp_claimprofile_get_group_policy_list` executes.
Enforcement	Each branch requires `wp.ACTV_PLCY_FL = 'Y'`, `pd_sum.GRP_MBRSHP_EXP_DT >= @rptDate`, and either `polPer.MOST_RECENT_PP_COV_FL = 'Y'` or the alternate last-in-group effective-date condition.
Outcome	Expired policies and memberships outside the requested report date are excluded from the returned group book.
Stored procedure cross-reference	cp_claimprofile_get_group_policy_list.BR-VP-038
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_group_policy_list.json`

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.
Stored procedure cross-reference	cp_claimprofile_get_sc_book.BR-VP-039
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.
Stored procedure cross-reference	cp_claimprofile_get_sc_book.BR-VP-040
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json`

BR-VP-041 External-client lookup returns the latest active non-test registrant that matches the supplied ID and last name¶

Field	Value
Statement	The external-client lookup returns only rows whose trimmed `ID_NUM` and `USER_LAST_NAM` match the input values, whose `ACTIVE_FL = 'Y'`, whose `TEST_USER_FL = 'N'`, and whose `UPDATE_DATE` is greatest.
Trigger	`cp_external_client_lookup` executes.
Enforcement	The query filters on trimmed ID and last-name equality, active and non-test flags, orders by `reg.UPDATE_DATE DESC`, and selects `TOP 1 *`.
Outcome	Callers receive the most recently updated active production registrant row for the supplied identity values.
Stored procedure cross-reference	cp_external_client_lookup.BR-VP-041
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json`

BR-VP-042 Current certificate flag requires a certificate detail row that spans the policy period¶

Field	Value
Statement	The agent online policy list sets `currentCerts = 'Y'` only when at least one `wcs_certificate_detail` row exists for the policy whose certificate period covers the returned policy period.
Trigger	`cp_get_agent_online_policy_list` returns an open-policy row.
Enforcement	The open-policy `SELECT` computes `currentCerts` with a `CASE` expression over a correlated `COUNT(*)` subquery on `wcs_certificate_detail`.
Outcome	Policies without a covering certificate-detail row return `currentCerts = 'N'`.
Stored procedure cross-reference	cp_get_agent_online_policy_list.BR-VP-042
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json`

BR-VP-043 Viewable-rate flag requires rate-account ownership by the requesting agent or agency¶

Field	Value
Statement	The agent online policy list sets `viewableRate = 'Y'` only when the policy is its own rate account or the rate-account policy is owned by the requesting agent or agency; null rate accounts return `N`.
Trigger	`cp_get_agent_online_policy_list` returns an open-policy row.
Enforcement	The open-policy `SELECT` uses nested `CASE` logic over `rat_acct` and a correlated query that matches the requesting agent or agency based on `@ForAgency` and `@AgentNum`.
Outcome	Rate-account detail is marked viewable only for the owning agent or agency.
Stored procedure cross-reference	cp_get_agent_online_policy_list.BR-VP-043
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json`

BR-VP-044 Closed policies are limited to the last five years¶

Field	Value
Statement	The procedures closed-policy branch includes only policies whose policy-period effective date is greater than or equal to `DateAdd(YYYY, -5, getDate())`.
Trigger	The closed-policy `UNION` branch executes.
Enforcement	The second `SELECT` adds `and polPer.plcy_pd_eff_dt >= DateAdd(YYYY, -5, getDate())`.
Outcome	Older closed policies are excluded from the agent online list.
Stored procedure cross-reference	cp_get_agent_online_policy_list.BR-VP-044
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json`

BR-VP-045 Pending-cancel flag is derived from the base policy status code¶

Field	Value
Statement	Both the open-policy and closed-policy branches derive `pendingCancel` as `Y` only when the joined base policy row has `PLCY_STAT_CD = '3'`.
Trigger	`cp_get_agent_online_policy_list` returns any policy row.
Enforcement	Each `SELECT` branch computes `pendingCancel` with `case when wPolBase.PLCY_STAT_CD = '3' then 'Y' else 'N' end`.
Outcome	Returned policy rows carry an explicit pending-cancel indicator.
Stored procedure cross-reference	cp_get_agent_online_policy_list.BR-VP-045
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json`

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.
Stored procedure cross-reference	cp_policy_in_group.BR-VP-046
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.
Stored procedure cross-reference	cp_policy_in_group.BR-VP-047
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.
Stored procedure cross-reference	cp_policy_in_group.BR-VP-048
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json`

BR-VP-015 Specific and current requests prefer policy-period employer-liability rows¶

Field	Value
Statement	Specific and current limit requests first read `POLICY_PERIOD_EMP_LIAB` rows joined to `EMPLOYERS_LIABILITY_LIMITS` when an in-effect override exists for the requested policy period.
Trigger	`getCurrentLimitsForPolicyNum` or `getSpecificLimitsForPolicyNum` executes for a policy period with matching `POLICY_PERIOD_EMP_LIAB` rows.
Enforcement	Each procedure opens the cursor with a first `SELECT` or `UNION` branch over `POLICY_PERIOD_EMP_LIAB` and `EMPLOYERS_LIABILITY_LIMITS` before fallback branches.
Outcome	The returned cursor exposes policy-specific employer-liability limits instead of standard defaults.
Stored procedure cross-reference	coveragelimits_pkg.BR-VP-015
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json`

BR-VP-016 Limit retrieval falls back to standard factor rows when no specific override exists¶

Field	Value
Statement	If no qualifying policy-specific employer-liability row exists, the package returns standard limit rows where `standard_limits_flag = 'Y'` and the factor effective dates cover the evaluation date.
Trigger	A current, specific, future, or all-limits lookup executes without a matching specific period override.
Enforcement	Fallback branches join `EMPLOYERS_LIABILITY_LIMITS` to `EMPLOYERS_LIABILITY_FACTORS` and require `standard_limits_flag = 'Y'` plus `NOT EXISTS` checks against `POLICY_PERIOD_EMP_LIAB`.
Outcome	Callers still receive formatted limit values even when a policy period has no explicit override row.
Stored procedure cross-reference	coveragelimits_pkg.BR-VP-016
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json`

BR-VP-017 Future and historical lookups use the policy periods own effective context¶

Field	Value
Statement	Future and older-period lookups compare effective-dated factor rows against the requested policy period or its expiration date rather than always using `SYSDATE`.
Trigger	`getFutureLimitsForPolicyNum` or the historical branches inside `getSpecificLimitsForPolicyNum` execute.
Enforcement	`getFutureLimitsForPolicyNum` compares adjustment windows to `pp.policy_period_eff_date`, and historical branches in `getSpecificLimitsForPolicyNum` use `PeriodEffDate`, `policy_period_exp_date`, or `policy_period_non_wcis` dates.
Outcome	Returned limits reflect the requested future or historical policy period, not only the current-day configuration.
Stored procedure cross-reference	coveragelimits_pkg.BR-VP-017
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json`

BR-VP-018 Gap detection ignores flat-cancel and prospect periods¶

Field	Value
Statement	Gap detection ignores policy periods with status `FC` or `PPROS` before comparing one periods cancel or expiration date to the next periods effective date.
Trigger	`getgaps` or `getgapindicator` executes.
Enforcement	Both routines filter out `policy_period_status_code` values `FC` and `PPROS` and compare only the remaining periods.
Outcome	Only meaningful coverage gaps are returned or counted.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-018
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-019 Assigned-risk policy periods mask risk-type and plan data until the info page prints¶

Field	Value
Statement	For policy periods with status `RF`, the package suppresses `risk_type_code` and plan description values until `SP72347_INFO_PAGE_PRINTED_YN(policy_num, policy_period_eff_date)` returns `Y`.
Trigger	`getpolicyperiod` or `getpolicyperiods` retrieves an `RF` policy period.
Enforcement	Both procedures use `CASE` expressions that return empty strings for `RF` rows when `SP72347_INFO_PAGE_PRINTED_YN` is not `Y`.
Outcome	Assigned-risk policy-period detail stays masked until the information page is printed.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-019
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-020 Claim-address requests may fall back to the policy address¶

Field	Value
Statement	If a claim-address request finds no active claim location and `ingetmailaddrwhennotfound = 'Y'`, the procedure changes the requested location code to `POL` before opening the cursor.
Trigger	`getpolicyaddress` executes for address type `CLM` and no current `CLM` location exists.
Enforcement	The procedure counts qualifying `CLM` locations, then assigns `v_addr_type := 'POL'` when fallback is enabled and the count is zero.
Outcome	Callers receive the policy mailing or location address instead of an empty claim-address result.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-020
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-021 Address retrieval prefers current contacts and the latest effective location row¶

Field	Value
Statement	Policy-address lookups join only active contacts, constrain location rows to the latest qualifying effective date, and return a single top-ranked row; location-sequence lookups also rank contact types before applying `ROWNUM <= 1`.
Trigger	`getpolicyaddress` or `getpolicyaddressbylocseqnum` executes.
Enforcement	`getpolicyaddress` filters `contact_exp_date IS NULL`, constrains the effective date with a `MAX(loc_eff_date)` subquery, and limits the result with `ROWNUM <= 1`; `getpolicyaddressbylocseqnum` orders contact types and also applies `ROWNUM <= 1`.
Outcome	The package returns one current address and contact record rather than multiple stale rows.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-021
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-022 Affiliate-type classification follows a fixed hierarchy¶

Field	Value
Statement	Affiliate-type resolution checks for manual policyholder affiliations first, then wrap and leasing relationships, then subaccount and state-agency relationships, then experience-combination memberships, and finally premium-discount combinations.
Trigger	`getpolicyaffiliatetype` executes.
Enforcement	The function evaluates each affiliation family in sequence and returns immediately on the first matching `SELECT ... INTO`.
Outcome	A policy receives the first matching affiliate classification in the packages explicit precedence order.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-022
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-023 Parent-policy resolution depends on the resolved affiliate type¶

Field	Value
Statement	Parent-policy resolution chooses the parent lookup strategy from the affiliate type returned by `getpolicyaffiliatetype`, including manual affiliation, relationship, combination, premium-discount, or self-parent fallback paths.
Trigger	`getparentpolicy` executes.
Enforcement	The function branches on `v_affil_type` and queries `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, `combo_policy_period`, or the input policy number.
Outcome	Callers receive the appropriate parent policy number for the resolved affiliation model.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-023
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-024 Affiliate total rows are optional and vary by affiliation family¶

Field	Value
Statement	Affiliate lists add a single totals row only when `in_include_affil_totals = 'TRUE'`, and the totals label and selection logic vary by affiliate family.
Trigger	`getpolicyaffiliatelist` executes.
Enforcement	The procedure builds `v_totals_description` and `v_affil_type`, then includes totals-row `UNION` branches only when `UPPER(in_include_affil_totals) = 'TRUE'`.
Outcome	Callers can opt into or suppress the family-specific totals row for affiliate displays.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-024
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-025 Wrap-up legal-name lookup climbs from member to relationship policy to rate account¶

Field	Value
Statement	When a wrap-up member references a different relationship policy, `getwrapuplegalname` first loads that policy legal name and then, if a rate account exists, replaces it with the rate-account policy legal name.
Trigger	`getwrapuplegalname` executes for a wrap-up member policy.
Enforcement	The function selects `policy_relationship_policy_num` and `legal_name`, then conditionally selects the relationship policy and finally the `rate_account` policy.
Outcome	The returned legal name reflects the top-level wrap-up or rate-account owner when available.
Stored procedure cross-reference	pk72846_view_policy.BR-VP-025
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-026 Policy header retrieval normalizes null financial amounts to zero¶

Field	Value
Statement	Policy-header retrieval wraps `current_balance` and `premium_estimate` in `NVL(..., 0)` before returning them.
Trigger	`getpolicy` executes.
Enforcement	The `SELECT` list uses `NVL(current_balance, 0)` and `NVL(premium_estimate, 0)`.
Outcome	Callers receive zero instead of null for the retrieved balance and premium-estimate fields.
Stored procedure cross-reference	pk73094_policy_data.BR-VP-026
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json`

BR-VP-027 Account-name lookup ranks active and parent policies first¶

Field	Value
Statement	Account-name lookup ranks policy candidates so active or pending-cancel statuses sort first, cancelled next, parent or self policies ahead of member policies, and later cancel dates ahead of earlier ones.
Trigger	`get_account_name` executes with a non-null account number.
Enforcement	The cursor query computes `policy_status_order`, `policy_relationship_order`, and `cancel_date`, orders by those values, and assigns the first fetched `legal_name` to `v_account_name`.
Outcome	The returned account name comes from the preferred policy row for that account number.
Stored procedure cross-reference	pk73094_policy_data.BR-VP-027
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json`

BR-VP-028 Policy waiver count equals the number of waiver-detail rows for the policy¶

Field	Value
Statement	The waiver-count helper returns the number of `WAIVER_DETAIL` rows whose `POLICY_NUM` equals the input policy number.
Trigger	`get_policy_waiver_count` executes.
Enforcement	The function runs `SELECT COUNT(*) INTO v_count FROM WAIVER_DETAIL WHERE POLICY_NUM = PolicyNum` and returns `v_count`.
Outcome	Callers receive a simple numeric waiver count for the policy.
Stored procedure cross-reference	pk82033_thirdparty.BR-VP-028
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk82033_thirdparty.json`

BR-VP-029 Pending-cancel policies only derive the demanded status¶

Field	Value
Statement	When the policy status group is `PENDCA`, the function derives cancellation status only by checking for a demand row with `COLLECTION_ACT_RESPONSE_CODE IS NULL`.
Trigger	`SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `PENDCA`.
Enforcement	The main program routes `PENDCA` to `pending_cancel_status`, which calls only `look_for_demand_status`.
Outcome	A pending-cancel policy returns `Demanded` when an open demand exists; otherwise it returns null.
Stored procedure cross-reference	sp72268_get_cancel_status.BR-VP-029
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-030 Cancelled policies use a fixed precedence order for derived collection statuses¶

Field	Value
Statement	For cancelled policies, the function derives collection status in a fixed order: Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, and finally Closed.
Trigger	`SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `CANCEL`.
Enforcement	`cancel_status` calls the local derivation procedures only while `v_cancel_status` remains null, then defaults to `Closed`.
Outcome	The first matching collection condition determines the returned cancellation status.
Stored procedure cross-reference	sp72268_get_cancel_status.BR-VP-030
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-031 Defaulted and demanded statuses come from policy-demand response codes¶

Field	Value
Statement	The function derives `Defaulted` from a `POLICY_DEMAND` row whose `COLLECTION_ACT_RESPONSE_CODE = 'D'` and derives `Demanded` from a `POLICY_DEMAND` row whose response code is null.
Trigger	`look_for_default_status` or `look_for_demand_status` executes.
Enforcement	Each local procedure opens a cursor over `POLICY_DEMAND` with its respective response-code filter and assigns the corresponding status when a row is found.
Outcome	Demand and default statuses are driven directly by the collection-action response state.
Stored procedure cross-reference	sp72268_get_cancel_status.BR-VP-031
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-032 Application policies return saifQuote only when WCIS does not own the submission¶

Field	Value
Statement	For application-status policies, the function returns `saifQuote` only when `pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N'` and no prior status has been assigned.
Trigger	`SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `APP`.
Enforcement	The main program routes `APP` to `saifquote_status`, which tests `pk73102_policy.does_wcis_own_submission` before assigning `v_cancel_status := 'saifQuote'`.
Outcome	Application policies are labeled `saifQuote` only for non-WCIS-owned submissions.
Stored procedure cross-reference	sp72268_get_cancel_status.BR-VP-032
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json`

BR-VP-033 Information-page status is returned as Y or N¶

Field	Value
Statement	The function returns `Y` when `sp72253_info_page_printed(in_policy_num, in_policy_period_eff_date)` is true and `N` otherwise.
Trigger	`SP72347_INFO_PAGE_PRINTED_YN` executes.
Enforcement	The function checks the wrapped Boolean function in an `IF ... THEN RETURN 'Y' ELSE RETURN 'N' END IF` block.
Outcome	Callers receive a character flag instead of a PL/SQL Boolean.
Stored procedure cross-reference	sp72347_info_page_printed_yn.BR-VP-033
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72347_info_page_printed_yn.json`

BR-VP-034 Bound-policy status is returned as Y or N¶

Field	Value
Statement	The function returns `Y` when `sp72325_chk_if_policy_bound(in_policy_num, in_policy_period_eff_date)` is true and `N` otherwise.
Trigger	`sp73089_get_policy_bound_flag` executes.
Enforcement	The function sets `flag := 'Y'` or `N` based on the wrapped Boolean function and returns `flag`.
Outcome	Callers receive a character flag instead of a PL/SQL Boolean.
Stored procedure cross-reference	sp73089_get_policy_bound_flag.BR-VP-034
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73089_get_policy_bound_flag.json`

BR-VP-035 External-client email lookup requires trimmed case-insensitive matches on mail ID and last name¶

Field	Value
Statement	The function selects an email address only from `SYSTEM_USER_CONTRACTOR` rows whose trimmed `LAST_NAME` and `SYSTEM_USER_ID` both match the trimmed uppercase input values.
Trigger	`sp73341_external_client_lookup` executes.
Enforcement	The cursor query filters with `upper(trim(last_name)) = upper(trim(in_last_name))` and `upper(trim(system_user_id)) = upper(trim(in_mailid))`.
Outcome	Only contractor rows matching both supplied identity values qualify.
Stored procedure cross-reference	sp73341_external_client_lookup.BR-VP-035
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json`

BR-VP-036 External-client lookup returns null when no contractor row is found¶

Field	Value
Statement	When the contractor cursor fetch does not find a row, the function assigns `v_email_address := null` before returning.
Trigger	`sp73341_external_client_lookup` executes and the cursor fetch is not found.
Enforcement	The body checks `if su_contractor_cur%notfound then v_email_address := null; end if;`.
Outcome	Callers receive null instead of an exception or stale email value when no contractor row matches.
Stored procedure cross-reference	sp73341_external_client_lookup.BR-VP-036
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json`

BR-VP-037 Group policy-list retrieval selects a parent, group, or group-and-chapter branch from the input key¶

Field	Value
Statement	The group-policy-list procedure classifies the input as a parent-group query, a group-key query, or a group-key-plus-specific-chapter query before running the corresponding `SELECT` branch.
Trigger	`cp_claimprofile_get_group_policy_list` executes.
Enforcement	The procedure sets `@UseSql` by probing `WCS_EMPLOYER_GROUP.PARENT_GRP`, `GRP_ASSN_KEY`, and the presence of `@SpecificChapterCode`, then runs the matching `if @UseSql = ...` query block.
Outcome	Only one query branch returns rows for the requested group context.
Stored procedure cross-reference	cp_claimprofile_get_group_policy_list.BR-VP-037
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_group_policy_list.json`

BR-VP-038 Group policy-list retrieval keeps only active or still-effective memberships as of the report date¶

Field	Value
Statement	The group-policy-list procedure returns only rows where the policy is active and either the most recent coverage flag is set or the last in-group period still covers the report date; group-membership expiration must also be on or after `@rptDate`.
Trigger	Any of the query branches in `cp_claimprofile_get_group_policy_list` executes.
Enforcement	Each branch requires `wp.ACTV_PLCY_FL = 'Y'`, `pd_sum.GRP_MBRSHP_EXP_DT >= @rptDate`, and either `polPer.MOST_RECENT_PP_COV_FL = 'Y'` or the alternate last-in-group effective-date condition.
Outcome	Expired policies and memberships outside the requested report date are excluded from the returned group book.
Stored procedure cross-reference	cp_claimprofile_get_group_policy_list.BR-VP-038
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_group_policy_list.json`

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.
Stored procedure cross-reference	cp_claimprofile_get_sc_book.BR-VP-039
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.
Stored procedure cross-reference	cp_claimprofile_get_sc_book.BR-VP-040
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json`

BR-VP-041 External-client lookup returns the latest active non-test registrant that matches the supplied ID and last name¶

Field	Value
Statement	The external-client lookup returns only rows whose trimmed `ID_NUM` and `USER_LAST_NAM` match the input values, whose `ACTIVE_FL = 'Y'`, whose `TEST_USER_FL = 'N'`, and whose `UPDATE_DATE` is greatest.
Trigger	`cp_external_client_lookup` executes.
Enforcement	The query filters on trimmed ID and last-name equality, active and non-test flags, orders by `reg.UPDATE_DATE DESC`, and selects `TOP 1 *`.
Outcome	Callers receive the most recently updated active production registrant row for the supplied identity values.
Stored procedure cross-reference	cp_external_client_lookup.BR-VP-041
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json`

BR-VP-042 Current certificate flag requires a certificate detail row that spans the policy period¶

Field	Value
Statement	The agent online policy list sets `currentCerts = 'Y'` only when at least one `wcs_certificate_detail` row exists for the policy whose certificate period covers the returned policy period.
Trigger	`cp_get_agent_online_policy_list` returns an open-policy row.
Enforcement	The open-policy `SELECT` computes `currentCerts` with a `CASE` expression over a correlated `COUNT(*)` subquery on `wcs_certificate_detail`.
Outcome	Policies without a covering certificate-detail row return `currentCerts = 'N'`.
Stored procedure cross-reference	cp_get_agent_online_policy_list.BR-VP-042
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json`

BR-VP-043 Viewable-rate flag requires rate-account ownership by the requesting agent or agency¶

Field	Value
Statement	The agent online policy list sets `viewableRate = 'Y'` only when the policy is its own rate account or the rate-account policy is owned by the requesting agent or agency; null rate accounts return `N`.
Trigger	`cp_get_agent_online_policy_list` returns an open-policy row.
Enforcement	The open-policy `SELECT` uses nested `CASE` logic over `rat_acct` and a correlated query that matches the requesting agent or agency based on `@ForAgency` and `@AgentNum`.
Outcome	Rate-account detail is marked viewable only for the owning agent or agency.
Stored procedure cross-reference	cp_get_agent_online_policy_list.BR-VP-043
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json`

BR-VP-044 Closed policies are limited to the last five years¶

Field	Value
Statement	The procedures closed-policy branch includes only policies whose policy-period effective date is greater than or equal to `DateAdd(YYYY, -5, getDate())`.
Trigger	The closed-policy `UNION` branch executes.
Enforcement	The second `SELECT` adds `and polPer.plcy_pd_eff_dt >= DateAdd(YYYY, -5, getDate())`.
Outcome	Older closed policies are excluded from the agent online list.
Stored procedure cross-reference	cp_get_agent_online_policy_list.BR-VP-044
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json`

BR-VP-045 Pending-cancel flag is derived from the base policy status code¶

Field	Value
Statement	Both the open-policy and closed-policy branches derive `pendingCancel` as `Y` only when the joined base policy row has `PLCY_STAT_CD = '3'`.
Trigger	`cp_get_agent_online_policy_list` returns any policy row.
Enforcement	Each `SELECT` branch computes `pendingCancel` with `case when wPolBase.PLCY_STAT_CD = '3' then 'Y' else 'N' end`.
Outcome	Returned policy rows carry an explicit pending-cancel indicator.
Stored procedure cross-reference	cp_get_agent_online_policy_list.BR-VP-045
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json`

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.
Stored procedure cross-reference	cp_policy_in_group.BR-VP-046
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.
Stored procedure cross-reference	cp_policy_in_group.BR-VP-047
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.
Stored procedure cross-reference	cp_policy_in_group.BR-VP-048
Source	SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json`

Stored procedure business rules¶

Business rules extracted directly from the retrieved Oracle and SQL Server procedure and package source are summarized below.

Rule ID	Rule	Source	Link
BR-VP-015	Specific and current limit lookups return `POLICY_PERIOD_EMP_LIAB` rows when an in-effect policy-period override exists.	`coveragelimits_pkg`	Details
BR-VP-016	When no period-specific limit record qualifies, the package falls back to standard-limit rows from `EMPLOYERS_LIABILITY_FACTORS`.	`coveragelimits_pkg`	Details
BR-VP-017	Future and historical limit lookups evaluate factors against the requested policy-period dates instead of `SYSDATE`.	`coveragelimits_pkg`	Details
BR-VP-018	Coverage-gap detection excludes flat-cancel and `PPROS` periods before comparing adjacent periods.	`pk72846_view_policy`	Details
BR-VP-019	`RF` policy periods blank risk-type and plan output until `SP72347_INFO_PAGE_PRINTED_YN` returns `Y`.	`pk72846_view_policy`	Details
BR-VP-020	When callers allow fallback and no active `CLM` location exists, address retrieval switches to `POL`.	`pk72846_view_policy`	Details
BR-VP-021	Address lookups limit results to active contacts and the most recent effective matching location, then return only the first ranked row.	`pk72846_view_policy`	Details
BR-VP-022	Affiliate detection checks policyholder or manual affiliations first, then relationship, combination, and premium-discount affiliations.	`pk72846_view_policy`	Details
BR-VP-023	Parent-policy lookup chooses `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, or premium-member logic based on affiliate type.	`pk72846_view_policy`	Details
BR-VP-024	Affiliate lists add a totals row only when `in_include_affil_totals = 'TRUE'`, using family-specific labels.	`pk72846_view_policy`	Details
BR-VP-025	Wrap-up legal-name resolution starts on the member row, then follows the relationship policy and rate account when present.	`pk72846_view_policy`	Details
BR-VP-026	`getpolicy` converts null `current_balance` and `premium_estimate` values to zero before returning them.	`pk73094_policy_data`	Details
BR-VP-027	`get_account_name` orders candidate policies by status, parent and self relationship, and latest cancel date before taking the first row.	`pk73094_policy_data`	Details
BR-VP-028	`get_policy_waiver_count` returns `COUNT(*)` from `WAIVER_DETAIL` for the supplied policy number.	`pk82033_thirdparty`	Details
BR-VP-029	Policies in status group `PENDCA` run only the demand-status branch.	`sp72268_get_cancel_status`	Details
BR-VP-030	Cancelled policies evaluate Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, then Closed.	`sp72268_get_cancel_status`	Details
BR-VP-031	`Defaulted` requires response code `D`; `Demanded` requires a policy-demand row with a null response code.	`sp72268_get_cancel_status`	Details
BR-VP-032	Status group `APP` yields `saifQuote` only when `pk73102_policy.does_wcis_own_submission(...) = 'N'`.	`sp72268_get_cancel_status`	Details
BR-VP-033	The wrapper converts `sp72253_info_page_printed(...)` Boolean output into `Y` or `N`.	`sp72347_info_page_printed_yn`	Details
BR-VP-034	The wrapper converts `sp72325_chk_if_policy_bound(...)` Boolean output into `Y` or `N`.	`sp73089_get_policy_bound_flag`	Details
BR-VP-035	The function matches `SYSTEM_USER_CONTRACTOR` rows only when both `system_user_id` and `last_name` match the trimmed uppercase inputs.	`sp73341_external_client_lookup`	Details
BR-VP-036	If the cursor fetch finds no row, the function explicitly returns null.	`sp73341_external_client_lookup`	Details
BR-VP-037	`@UseSql` is set to 1, 2, or 3 depending on whether the input resolves as a parent group, a group key, or a group key with chapters.	`cp_claimprofile_get_group_policy_list`	Details
BR-VP-038	Rows must be active or still within the report-date membership window, and membership expiration must be on or after `@rptDate`.	`cp_claimprofile_get_group_policy_list`	Details
BR-VP-039	The procedure accepts either an exact mail ID or a wildcard pattern depending on whether the input contains `%`.	`cp_claimprofile_get_sc_book`	Details
BR-VP-040	Only active policies whose most recent period is not flat-cancel and whose loss-control mail ID is populated are returned.	`cp_claimprofile_get_sc_book`	Details
BR-VP-041	The procedure returns `TOP 1` from `Net_Registrant` after trimmed ID and last-name matching, restricted to active non-test rows and ordered by latest update date.	`cp_external_client_lookup`	Details
BR-VP-042	`currentCerts` is `Y` only when a certificate-detail row covers the returned policy period.	`cp_get_agent_online_policy_list`	Details
BR-VP-043	`viewableRate` is `Y` only when the rate account is self or the rate-account policy is owned by the requesting agent or agency.	`cp_get_agent_online_policy_list`	Details
BR-VP-044	The closed-policy branch returns only policies whose current-period effective date is within the last five years.	`cp_get_agent_online_policy_list`	Details
BR-VP-045	`pendingCancel` is `Y` when `WCS_POLICY.PLCY_STAT_CD = '3'` and `N` otherwise.	`cp_get_agent_online_policy_list`	Details
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.	`cp_policy_in_group`	Details
BR-VP-047	Group-key detection ignores rows whose `GRP_TYP_CD = 'PARNT'`.	`cp_policy_in_group`	Details
BR-VP-048	Each branch returns `count(*)`; the chapter filter is added only when `@UseSql = 3`.	`cp_policy_in_group`	Details

Notes¶

No concrete, domain-specific inherited business rules were found for this library's framework base classes (clsBusinessBase, clsBusinessCollection, clsBusinessCollectionBase, clsAddress) — the retrieved framework documentation describes only generic rule-registration/broken-rule-tracking mechanisms (via clsRulesManager/IDataErrorInfo), not concrete rules attributable to a specific statement, trigger, and outcome. No Inherited business rules section is included per the omit-if-empty guidance.
Mark inferred statements with * and unknown statements with **. Do not use bold inline confidence labels.

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¶

SAIF.Business.View.Policy\clsPolicyData.vb
SAIF.Business.View.Policy\clsPolicySecurity.vb
SAIF.Business.View.Policy\clsAgency.vb
SAIF.Business.View.Policy\clsPolicyAddress.vb
SAIF.Business.View.Policy\clsTeamAssignment.vb
SAIF.Business.View.Policy\clsAffiliatedPolicyCollection.vb
SAIF.Business.View.Policy\clsNonEmployerAccount.vb