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

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;

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

SP73029_GET_NONWCIS_GOV_CLASS¶

Overview¶

Purpose¶

Procedures / Methods¶

SP73029_GET_NONWCIS_GOV_CLASS¶

Parameters¶

Business Rules¶

Operations¶

Source Code¶

Related Documentation¶

Sources¶