--- title: "pk72226_team_assignment.regional_office_yn stored procedure documentation" summary: "Analysis of Oracle package/procedure including business rules and operations." status: draft owner: Technical Writer last_reviewed: 2026-07-31 library: SAIF.Business.Policy.Core reference_type: package source_schema: "WCIS_DBA" source_name: "pk72226_team_assignment.regional_office_yn" source_type: "Package function" oracle_status: "found" --- # 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 ```sql 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](index.md) - [Business rules](../business-rules.md) - [Operations and processes](../operations-processes.md) - [SQL catalog](../sql/index.md) > **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`