pk73059_prem_hist_summary.get_6_digit_gov_class¶

Overview¶

Field	Value
Schema	WCIS_DBA
Type	Package procedure (OUT parameter)
Database	Oracle
Calling libraries	`SAIF.Business.Policy.Core`
Calling classes	`clsPolicyPeriod`

Purpose¶

Picks the manual class with the greatest summed payroll in the latest premium-history set, excluding clerical/sales/drivers, with two fallback passes

Procedures / Methods¶

get_6_digit_gov_class¶

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

Description: Picks the manual class with the greatest summed payroll in the latest premium-history set, excluding clerical/sales/drivers, with two fallback passes

Parameters¶

Parameter	Direction	Type	Default	Description
`in_policy_num`	IN	`policy_period.policy_num%TYPE`	—	Policy number whose governing class is being selected.
`in_pol_per_eff_date`	IN	`policy_period.policy_period_eff_date%TYPE`	—	Policy-period effective date used to choose the matching premium-history set.
`out_governing_class`	OUT	`prem_hist_class_coverage.manual_class_num%TYPE`	—	Six-digit governing class returned to the caller.

Business Rules¶

Rule ID	Rule	Condition	Action
PROC-RULE-018	The governing class is drawn from the most recent premium-history set for the policy period	`MAX(prem_hist_set_id)` from `PREM_HIST_SUMMARY` for `policy_num` + `policy_period_eff_date`	That set id drives all subsequent payroll cursors
PROC-RULE-019	Clerical, sales and drivers classes are excluded from the governing-class contest	`SUBSTR(manual_class_num,1,4) NOT IN ('8810','8742','7380')`	Those classes do not participate in the maximum-payroll comparison
PROC-RULE-020	The governing class is the manual class with the greatest summed period payroll	`SUM(NVL(manual_class_period_payroll,0)) >= max_payroll` and class not null, iterating the cursor	Retain that `manual_class_num` as the governing class
PROC-RULE-021	If no eligible class wins, the excluded classes are reconsidered, then the lowest class number is used	Governing class still `NULL`/`'NONE'` after the first pass	Re-run the contest over all classes; failing that, take `MIN(manual_class_num)`

Operations¶

Operation	Description	Trigger
Derive the six-digit governing class (WCIS)	Picks the manual class with the greatest summed payroll in the latest premium-history set, excluding clerical/sales/drivers, with two fallback passes	`clsPolicyPeriod.Get6DigitGoverningClass` (SQL-078)

Source Code¶

PL/SQL source

PROCEDURE get_6_digit_gov_class(
      in_policy_num         IN     policy_period.policy_num%TYPE
    , in_pol_per_eff_date   IN     policy_period.policy_period_eff_date%TYPE
    , out_governing_class      OUT prem_hist_class_coverage.manual_class_num%TYPE)
   IS
      max_payroll            NUMBER; /* Maximum payroll, to determine governing class */
      v_prem_hist_set_id     prem_hist_summary.prem_hist_set_id%TYPE;

      /***********************************************************
      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(prem_hist_set_id)
           FROM prem_hist_summary
          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 manual_class_num
              , SUM(NVL(manual_class_period_payroll, 0)) sum_payroll
           FROM prem_hist_class_coverage
          WHERE prem_hist_set_id = v_prem_hist_set_id
                AND SUBSTR(manual_class_num
                         , 1
                         , 4) NOT IN ('8810', '8742', '7380')
         GROUP BY manual_class_num;

      payroll_rec            max_payroll_cur%ROWTYPE;

      CURSOR max_payroll_excluded_cur
      IS
         SELECT manual_class_num
              , SUM(NVL(manual_class_period_payroll, 0)) sum_payroll
           FROM prem_hist_class_coverage
          WHERE prem_hist_set_id = v_prem_hist_set_id
         GROUP BY manual_class_num;

      payroll_excluded_rec   max_payroll_excluded_cur%ROWTYPE;

      CURSOR get_lowest_class_cur
      IS
         SELECT MIN(manual_class_num) manual_class_num
           FROM prem_hist_class_coverage
          WHERE prem_hist_set_id = v_prem_hist_set_id;

      get_lowest_class_rec   get_lowest_class_cur%ROWTYPE;

      /** local procedure(s) **/

      /** This procedure closes all the open cursors **/

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: pk73059_prem_hist_summary.get_6_digit_gov_class
Business\SAIF.Business.Policy.Core\clsPolicyPeriod.vb