pk73211_premium_audit.get_payroll_classes¶

Overview¶

Field	Value
Schema	`WCIS_DBA`
Type	Package procedure (ref cursor out)
Database	Oracle / WCIS
Calling libraries	`SAIF.Business.Policy.Audit.Premium`
Calling classes	`clsAuditPolicyPeriod`

Purpose¶

Opens a ref cursor of distinct four-digit manual class numbers and their rates from the newest premium-history set

Procedures / Methods¶

get_payroll_classes¶

Field	Value
Returns	See Oracle signature / caller behavior
Called by	`GetPayrollClasses` (SQL-043)

Description: Opens a ref cursor of distinct four-digit manual class numbers and their rates from the newest premium-history set

Business Rules¶

Rule ID	Rule	Condition	Action
PROC-RULE-030	Payroll classes are reported at four-digit granularity	`SUBSTR(phcc.manual_class_num, 1, 4)` is selected and grouped	Six-digit sub-classes collapse into their four-digit parent
PROC-RULE-031	Only the newest premium-history set contributes classes	`phs.prem_hist_set_id = (SELECT MAX(phs2.prem_hist_set_id) …)`	Older sets are ignored
PROC-RULE-032	Class and rate together form the reporting grain	`GROUP BY SUBSTR(manual_class_num,1,4), phcc.manual_class_rate`	A class carrying two rates is returned twice
PROC-RULE-033	Any failure is escalated with full context	`WHEN OTHERS`	`pk73090_error_handler.raise_an_error('get_payroll_classes: ' \

Operations¶

Operation	Description	Trigger
Get the policy period's payroll classes	Opens a ref cursor of distinct four-digit manual class numbers and their rates from the newest premium-history set	`clsAuditPolicyPeriod.GetPayrollClasses` (SQL-043); OP-009

Source Code¶

PL/SQL package or method excerpt

   PROCEDURE get_payroll_classes(
      in_policynumber                IN     policy_period_rating_plan.policy_num%TYPE
    , in_policyperiodeffectivedate   IN     policy_period_rating_plan.policy_period_eff_date%TYPE
    , out_recordset                     OUT ref_cursor)
   IS
   BEGIN
      OPEN out_recordset FOR
         SELECT SUBSTR(phcc.manual_class_num
                     , 1
                     , 4)
                   manualclassnumber
              , phcc.manual_class_rate manualclassrate
           FROM prem_hist_class_coverage phcc
                JOIN prem_hist_summary phs ON phs.prem_hist_set_id = phcc.prem_hist_set_id
          WHERE     phs.policy_num = in_policynumber
                AND phs.policy_period_eff_date = in_policyperiodeffectivedate
                AND phs.prem_hist_set_id =
                       (SELECT MAX( phs2.prem_hist_set_id)
                          FROM prem_hist_summary phs2
                         WHERE     phs2.policy_num = phs.policy_num
                               AND phs2.policy_period_eff_date = phs.policy_period_eff_date)
         GROUP BY SUBSTR(phcc.manual_class_num
                       , 1
                       , 4)
                , phcc.manual_class_rate;
   EXCEPTION
      WHEN OTHERS THEN
         pk73090_error_handler.raise_an_error(
           'get_payroll_classes: ' || in_policynumber || ',' ||
           in_policyperiodeffectivedate || ',' || SQLERRM);
   END get_payroll_classes;

   /********************************************************************
   | Name             : insert_pol_per_payroll_audit
   | Prepared By      : carade
   | Created          : 09/18/2008
   | Description      : Insert a payroll audit.
   | Modification History
   | ------------------------------------------------------------------*/

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: pk73211_premium_audit.get_payroll_classes
Calling code: `analysis\oracle-source\pk73211_premium_audit.sql; clsAuditPolicyPeriod.vb