pk72226_team_assignment.regional_office_yn

Overview

Field	Value
Schema	WCIS_DBA
Type	Package function
Database	Oracle
Calling libraries	SAIF.Business.Policy.Core
Calling classes	WCISInquiry

Purpose

Applies four ordered tests (premium threshold, temp agency, FELA/Maritime jurisdiction, entity 50) to decide regional-office handling

Procedures / Methods

regional_office_yn

Field	Value
Returns	See PL/SQL source below.
Called by	WCISInquiry

Description: Applies four ordered tests (premium threshold, temp agency, FELA/Maritime jurisdiction, entity 50) to decide regional-office handling

Parameters

Parameter	Direction	Type	Default	Description
in_policy_num	IN	policy.policy_num%TYPE	—	Policy number to test for regional-office assignment.
in_policy_period_eff_date	IN	policy_period.policy_period_eff_date%TYPE	—	Policy-period effective date used by the premium, jurisdiction, and wrapper lookups.

Business Rules

Rule ID	Rule	Condition	Action
PROC-RULE-003	A policy is a regional-office policy when its manual premium exceeds the configured threshold	pk73059_prem_hist_summary.determine_manual_premium(policy, eff_date) > SUB_CONSTANTS.manual_premium_threshold_amt	Return 'Y' immediately; no further tests are evaluated
PROC-RULE-004	A temporary-staffing agency is a regional-office policy	Below the premium threshold and the submission answered 'Y' to ACORD form 'SAIF' question '09' (SUB_POLICY_RESPONSE joined to SUB_ACORD_QUESTION for the policy's SUB_POLICY_SEQ_NUM)	Return 'Y'
PROC-RULE-005	A FELA or Maritime jurisdiction makes a policy a regional-office policy	Below threshold, not a temp agency, and the latest premium-history set (MAX(prem_hist_set_id) from PREM_HIST_PREM_SUMMARY) contains a PREM_HIST_CLASS_COVERAGE row with manual_class_type_code IN ('M','FELA')	Return 'Y'
PROC-RULE-006	Entity code 50 makes a policy a regional-office policy	None of the above matched and COUNT(*) > 0 for SUB_POLICY rows with entity_code = '50'	Return 'Y'; otherwise the default 'N' stands
PROC-RULE-007	Any unexpected error is suppressed and treated as "not regional office"	WHEN OTHERS	Return 'N' - failures are silently downgraded, not raised

Operations

Operation	Description	Trigger
Classify a policy as regional-office	Applies four ordered tests (premium threshold, temp agency, FELA/Maritime jurisdiction, entity 50) to decide regional-office handling	WCISInquiry.IsRegionalOfficePolicy (SQL-121)

Source Code

PL/SQL source

FUNCTION regional_office_yn(
         in_policy_num policy.policy_num%type
       , in_policy_period_eff_date policy_period.policy_period_eff_date%type)
  RETURN VARCHAR2
  IS

    v_regional_ind VARCHAR2(1) := 'N';
    v_manual_premium NUMBER;
    v_count          number;

    CURSOR csr_temp_agency (in_policy_num  IN policy.policy_num%type) IS
        SELECT 'Y'
         FROM sub_policy_response r
         JOIN sub_acord_question q
         ON q.sub_acord_question_seq_num = r.sub_acord_question_seq_num
         WHERE q.acord_form_num = 'SAIF'
         AND q.question_num =  '09'
         AND r.response_flag_code = 'Y'
         AND r.sub_policy_seq_num = (select sub_policy_seq_num
                                   from sub_policy
                                   where policy_num = in_policy_num);

    CURSOR csr_jurisdiction_code(in_policy_num  IN policy.policy_num%type,
                                 in_policy_period_eff_date IN policy_period.policy_period_eff_date%TYPE) IS
        SELECT 'Y' FROM prem_hist_class_coverage
        WHERE manual_class_type_code IN ('M', 'FELA')
        AND prem_hist_set_id = (SELECT MAX(prem_hist_set_id)
                                FROM prem_hist_prem_summary
                                WHERE policy_num = in_policy_num
                                AND policy_period_eff_date = in_policy_period_eff_date)
        AND policy_num = in_policy_num
        AND policy_period_eff_date = in_policy_period_eff_date;

  BEGIN

    -- Get the manual premium threshold
    SELECT manual_premium_threshold_amt
      INTO v_manual_premium
      FROM sub_constants;

    -- Check  if manual premium is greater than 20000
    IF pk73059_prem_hist_summary.determine_manual_premium
                   (in_policy_num, in_policy_period_eff_date) > v_manual_premium
    THEN
          v_regional_ind := 'Y';
    ELSE

        -- If the submission is not a leasing company
        IF v_regional_ind = 'N' THEN
         -- Check if the submission is a temp agency
           OPEN csr_temp_agency(in_policy_num);
           FETCH csr_temp_agency INTO v_regional_ind;
           CLOSE csr_temp_agency;
        END IF;

        -- If the submission is not a temp agency
        IF v_regional_ind = 'N' THEN
          -- Check if the jurisdiction code is FELA or Maritime
           OPEN csr_jurisdiction_code(in_policy_num, in_policy_period_eff_date);
           FETCH csr_jurisdiction_code INTO v_regional_ind;
           CLOSE csr_jurisdiction_code;
        END IF;

        -- check if entity 50
        if v_regional_ind = 'N' then
            select count(*)
              into v_count
              from sub_policy
             where policy_num = in_policy_num
               and entity_code = '50';

            if v_count > 0 then
                v_regional_ind := 'Y';
            end if;
        end if;

    END IF;

    RETURN v_regional_ind;
  EXCEPTION
   WHEN OTHERS THEN
    RETURN 'N';
  END;

   /* ===========================================================================
    Name              : get_team_assignment_name
    Prepared By       : valrey
    Created           : 06/11/2012
    Description       : Returns the team assignment name

    Modification History
    --------------------------------------------------------------------------
  =========================================================================== */

Related Documentation

• Procedure catalog
• Business rules
• Operations and processes
• SQL catalog

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

• Oracle DBA_SOURCE: pk72226_team_assignment.regional_office_yn
• Business\SAIF.Business.Policy.Core\WCISInquiry.vb