--- title: "SP73029_GET_NONWCIS_GOV_CLASS 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: "*(unqualified; standalone)*" source_name: "SP73029_GET_NONWCIS_GOV_CLASS" source_type: "Standalone function" oracle_status: "found" --- # SP73029_GET_NONWCIS_GOV_CLASS ## Overview | Field | Value | | --- | --- | | **Schema** | *(unqualified; standalone)* | | **Type** | Standalone function | | **Database** | Oracle | | **Calling libraries** | `SAIF.Business.Policy.Core` | | **Calling classes** | `clsLegacyPolicyPeriod` | ## Purpose Same payroll-contest algorithm as the WCIS version but over legacy class-coverage tables and truncated to four digits ## Procedures / Methods ### SP73029_GET_NONWCIS_GOV_CLASS | Field | Value | | --- | --- | | **Returns** | See PL/SQL source below. | | **Called by** | `clsLegacyPolicyPeriod` | **Description:** Same payroll-contest algorithm as the WCIS version but over legacy class-coverage tables and truncated to four digits #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_policy_num` | IN | `NUMBER` | — | Legacy policy number whose non-WCIS governing class is being returned. | | `in_pol_per_eff_date` | IN | `DATE` | — | Legacy policy-period effective date used to select the governing class row. | ## Business Rules | Rule ID | Rule | Condition | Action | | --- | --- | --- | --- | | PROC-RULE-033 | For legacy (non-WCIS) periods the governing class is derived from the most recent class-coverage set | `MAX(class_coverage_setid)` from `PPNW_CLASS_COVERAGE` for `policy_num` + `policy_period_eff_date` | That set drives the payroll cursors; if no set exists the class is `'NONE'` | | PROC-RULE-034 | Legacy governing class is a 4-digit truncation of the manual class number | `SUBSTR(manual_class_num,1,4)` is grouped and compared | The returned `VARCHAR2(6)` therefore carries a 4-character value | | PROC-RULE-035 | Clerical, sales and drivers classes are excluded from the legacy governing-class contest | `SUBSTR(manual_class_num,1,4) NOT IN ('8810','8742','7380')` | Excluded classes are reconsidered only if no other class qualifies | | PROC-RULE-036 | Default governing class is `'NONE'` | No class-coverage detail rows found | Return `'NONE'` | ## Operations | Operation | Description | Trigger | | --- | --- | --- | | Derive the legacy governing class | Same payroll-contest algorithm as the WCIS version but over legacy class-coverage tables and truncated to four digits | `clsLegacyPolicyPeriod.GetGoverningClass` (SQL-061) | ## Source Code
PL/SQL source ```sql FUNCTION sp73029_get_nonwcis_gov_class /****************************************************************************** NAME: SP73029_get_nonwcis_gov_class Procedure Title : Get Governing Class Name : SP62022_min_prem_gov_class Prepared By : bricot Date Last updated : 03/01/2006 bricot 03/01/2006 Initial version - Get governing class for non-wcis periods. Left Commented sections in case these would be needed in future releases. *********************************************************/ ( in_policy_num IN NUMBER , in_pol_per_eff_date IN DATE) RETURN VARCHAR2 IS v_seq_num NUMBER; /* to store minimum rate_file_seq_num */ max_payroll NUMBER; /* Maximum payroll, to determine governing class */ v_gov_class VARCHAR2(6) := 'NONE'; v_set_id NUMBER; /*********************************************************** This cursor gets the most recent set id if the function call has passed a null or 0 set id. ***********************************************************/ CURSOR set_id_cur IS SELECT MAX(class_coverage_setid) FROM ppnw_class_coverage WHERE policy_num = in_policy_num AND policy_period_eff_date = in_pol_per_eff_date; /*********************************************************** Create a cursor that gets the sum of payroll for each manual class on the particular set ***********************************************************/ CURSOR max_payroll_cur IS SELECT SUBSTR(manual_class_num , 1 , 4) manual_class_num , SUM(NVL(manual_class_period_payroll, 0)) sum_payroll FROM ppnw_class_coverage_detail WHERE class_coverage_setid = v_set_id AND SUBSTR(manual_class_num , 1 , 4) NOT IN('8810', '8742', '7380') GROUP BY SUBSTR(manual_class_num , 1 , 4); payroll_rec max_payroll_cur%ROWTYPE; CURSOR max_payroll_excluded_cur IS SELECT SUBSTR(manual_class_num , 1 , 4) manual_class_num , SUM(NVL(manual_class_period_payroll, 0)) sum_payroll FROM ppnw_class_coverage_detail WHERE class_coverage_setid = v_set_id GROUP BY SUBSTR(manual_class_num , 1 , 4); payroll_excluded_rec max_payroll_excluded_cur%ROWTYPE; CURSOR get_lowest_class_cur IS SELECT MIN(SUBSTR(manual_class_num , 1 , 4)) manual_class_num FROM ppnw_class_coverage_detail WHERE class_coverage_setid = v_set_id; get_lowest_class_rec get_lowest_class_cur%ROWTYPE; /*********************************************************** Create a cursor to retrieve the minimum premium from manual class base for the established governing class *********************************************************** CURSOR governing_class_cur IS SELECT min_premium FROM manual_class_base WHERE substr(manual_class_num,1,4) = v_gov_class AND rate_file_seq_num = v_seq_num; CURSOR get_min_seq_cur IS SELECT min(rate_file_seq_num) FROM prem_hist_class_coverage WHERE prem_hist_set_id = v_set_id; /******************************************************/ /****************** local procedure(s) ****************/ /******************************************************/ /** This procedure closes all the open cursors **/ PROCEDURE close_cursors IS BEGIN IF set_id_cur%ISOPEN THEN CLOSE set_id_cur; END IF; IF max_payroll_cur%ISOPEN THEN CLOSE max_payroll_cur; END IF; IF max_payroll_excluded_cur%ISOPEN THEN CLOSE max_payroll_excluded_cur; END IF; IF get_lowest_class_cur%ISOPEN THEN CLOSE get_lowest_class_cur; END IF; -- IF governing_class_cur%ISOPEN THEN -- CLOSE governing_class_cur; -- END IF; -- IF get_min_seq_cur%ISOPEN THEN -- CLOSE get_min_seq_cur; -- END IF; END; BEGIN /************************************************************** If a valid set id has not been passed in, get the most recent ***************************************************************/ -- IF (v_set_id IS NULL) OR (v_set_id < 1) THEN IF NOT set_id_cur%ISOPEN THEN OPEN set_id_cur; END IF; FETCH set_id_cur INTO v_set_id; IF set_id_cur%NOTFOUND THEN close_cursors; max_payroll := 0; v_gov_class := 'NONE'; END IF; -- close_cursors; -- END IF; IF v_set_id IS NOT NULL THEN /*********************************************************** Set the governing_class to 'NONE' in case there are no class coverage records. If one is found, it replaces the default value. ***********************************************************/ max_payroll := 0; v_gov_class := 'NONE'; FOR payroll_rec IN max_payroll_cur LOOP IF ( (payroll_rec.sum_payroll >= max_payroll) AND (payroll_rec.manual_class_num IS NOT NULL)) THEN max_payroll := payroll_rec.sum_payroll; v_gov_class := payroll_rec.manual_class_num; END IF; END LOOP; /********************************************************* If there is no governing class, check for one of the otherwise excluded classes **********************************************************/ IF ( (v_gov_class IS NULL) OR (v_gov_class = 'NONE')) THEN max_payroll := 0; v_gov_class := 'NONE'; FOR payroll_excluded_rec IN max_payroll_excluded_cur LOOP IF ( (payroll_excluded_rec.sum_payroll >= max_payroll) AND (payroll_excluded_rec.manual_class_num IS NOT NULL)) THEN max_payroll := payroll_excluded_rec.sum_payroll; v_gov_class := payroll_excluded_rec.manual_class_num; END IF; END LOOP; END IF; /* if the payroll on the policy is zero then use the lowest numeric class */ IF max_payroll = 0 THEN OPEN get_lowest_class_cur; FETCH get_lowest_class_cur INTO get_lowest_class_rec; IF get_lowest_class_cur%NOTFOUND THEN close_cursors; max_payroll := 0; v_gov_class := 'NONE'; ELSE v_gov_class := get_lowest_class_rec.manual_class_num; END IF; CLOSE get_lowest_class_cur; END IF; /********************************************************* If there is no governing class, set the min_premium to 0, else find the minimum from manual_class_base ********************************************************** IF ((v_gov_class IS NULL) or (v_gov_class = 'NONE')) THEN io_minimum_premium := 0; ELSE IF not get_min_seq_cur%ISOPEN THEN OPEN get_min_seq_cur; END IF; FETCH get_min_seq_cur INTO v_seq_num; IF get_min_seq_cur%NOTFOUND THEN close_cursors; raise_application_error(-20001, 'No Data Found in PHCC in SP62022'); ELSE IF not governing_class_cur%ISOPEN THEN OPEN governing_class_cur; END IF; FETCH governing_class_cur INTO io_minimum_premium; IF governing_class_cur%NOTFOUND THEN close_cursors; raise_application_error(-20001, 'No Data Found in MCB in SP62022'); ELSE close_cursors; END IF; END IF; END IF; */ END IF; RETURN v_gov_class; END sp73029_get_nonwcis_gov_class; ```
## 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: SP73029_GET_NONWCIS_GOV_CLASS - `Business\SAIF.Business.Policy.Core\clsLegacyPolicyPeriod.vb`