SQL-VP-031 Retrieve WCIS-era payroll classification detail

Selected in clsPayrollClassInfoCollection.BeforeRetrieve when no ClassCoverageSetID is supplied (the WCIS-era branch); the alternate PMS-era branch is SQL-VP-030. Composed of a UNION of a "with federal minimum wage" branch and a fallback branch without it.

Classification

Field	Value
SQL ID	SQL-VP-031
Operation type	Read
Execution method	Inline SQL (constant, reconstructed from VB & _ continuations, UNION)
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsPayrollClassInfoCollection (via clsPayrollClassInfo.RETRIEVE_WCIS_SQL)
Criteria property	BeforeRetrieve (branch: else)
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
prem_hist_set_id	Input	Decimal/ID	N/A	Premium-history set ID identifying the WCIS-era detail set.
period_start_date	Input	Date	N/A	Policy period start date filter.
period_end_date	Input	Date	N/A	Policy period end date filter.

Tables

Table	Access	Columns used
PREM_HIST_CLASS_COVERAGE	SELECT / JOIN	PREM_HIST_SET_ID, MANUAL_CLASS_CODE, PAYROLL_AMOUNT
MANUAL_CLASS_DESCRIPTION	JOIN	MANUAL_CLASS_CODE, MANUAL_CLASS_DESCRIPTION
PREM_HIST_ADJ_RATE_CLASS	JOIN	PREM_HIST_SET_ID, RATE
POLICY_PERIOD	JOIN	PREM_HIST_SET_ID, POLICY_NUM, PERIOD_START_DATE, PERIOD_END_DATE, POLICY_PERIOD_EFF_DATE
LOCATION	JOIN	POLICY_NUM, LOCATION_SEQ_NUM
FEDERAL_MINIMUM_WAGE	LEFT JOIN	EFF_DATE, EXP_DATE, MINIMUM_WAGE_RATE

Sample SQL

SELECT
  PREM_HIST_CLASS_COVERAGE.PREM_HIST_SET_ID,
  PREM_HIST_CLASS_COVERAGE.MANUAL_CLASS_CODE,
  MANUAL_CLASS_DESCRIPTION.MANUAL_CLASS_DESCRIPTION,
  PREM_HIST_CLASS_COVERAGE.PAYROLL_AMOUNT,
  PREM_HIST_ADJ_RATE_CLASS.RATE,
  POLICY_PERIOD.POLICY_NUM,
  LOCATION.LOCATION_SEQ_NUM,
  FEDERAL_MINIMUM_WAGE.MINIMUM_WAGE_RATE
FROM
  PREM_HIST_CLASS_COVERAGE
  INNER JOIN MANUAL_CLASS_DESCRIPTION
    ON PREM_HIST_CLASS_COVERAGE.MANUAL_CLASS_CODE = MANUAL_CLASS_DESCRIPTION.MANUAL_CLASS_CODE
  INNER JOIN PREM_HIST_ADJ_RATE_CLASS
    ON PREM_HIST_CLASS_COVERAGE.PREM_HIST_SET_ID = PREM_HIST_ADJ_RATE_CLASS.PREM_HIST_SET_ID
  INNER JOIN POLICY_PERIOD
    ON PREM_HIST_CLASS_COVERAGE.PREM_HIST_SET_ID = POLICY_PERIOD.PREM_HIST_SET_ID
  INNER JOIN LOCATION
    ON POLICY_PERIOD.POLICY_NUM = LOCATION.POLICY_NUM
  LEFT JOIN FEDERAL_MINIMUM_WAGE
    ON POLICY_PERIOD.POLICY_PERIOD_EFF_DATE BETWEEN FEDERAL_MINIMUM_WAGE.EFF_DATE AND FEDERAL_MINIMUM_WAGE.EXP_DATE
WHERE
  PREM_HIST_CLASS_COVERAGE.PREM_HIST_SET_ID = :prem_hist_set_id
  AND POLICY_PERIOD.PERIOD_START_DATE = :period_start_date
  AND POLICY_PERIOD.PERIOD_END_DATE = :period_end_date

UNION

SELECT
  PREM_HIST_CLASS_COVERAGE.PREM_HIST_SET_ID,
  PREM_HIST_CLASS_COVERAGE.MANUAL_CLASS_CODE,
  MANUAL_CLASS_DESCRIPTION.MANUAL_CLASS_DESCRIPTION,
  PREM_HIST_CLASS_COVERAGE.PAYROLL_AMOUNT,
  PREM_HIST_ADJ_RATE_CLASS.RATE,
  POLICY_PERIOD.POLICY_NUM,
  LOCATION.LOCATION_SEQ_NUM,
  NULL AS MINIMUM_WAGE_RATE
FROM
  PREM_HIST_CLASS_COVERAGE
  INNER JOIN MANUAL_CLASS_DESCRIPTION
    ON PREM_HIST_CLASS_COVERAGE.MANUAL_CLASS_CODE = MANUAL_CLASS_DESCRIPTION.MANUAL_CLASS_CODE
  INNER JOIN PREM_HIST_ADJ_RATE_CLASS
    ON PREM_HIST_CLASS_COVERAGE.PREM_HIST_SET_ID = PREM_HIST_ADJ_RATE_CLASS.PREM_HIST_SET_ID
  INNER JOIN POLICY_PERIOD
    ON PREM_HIST_CLASS_COVERAGE.PREM_HIST_SET_ID = POLICY_PERIOD.PREM_HIST_SET_ID
  INNER JOIN LOCATION
    ON POLICY_PERIOD.POLICY_NUM = LOCATION.POLICY_NUM
WHERE
  PREM_HIST_CLASS_COVERAGE.PREM_HIST_SET_ID = :prem_hist_set_id
  AND POLICY_PERIOD.PERIOD_START_DATE = :period_start_date
  AND POLICY_PERIOD.PERIOD_END_DATE = :period_end_date

Reconstructed from the very large multi-line VB & _ concatenated RETRIEVE_WCIS_SQL constant spanning both a "with federal minimum wage" branch and a UNION fallback branch without it; exact column aliases/join predicates are inferred from the concatenated string structure captured across multiple source reads and should be spot-checked against the live constant before being treated as byte-exact.

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

• SAIF.Business.View.Policy\clsPayrollClassInfo.vb
• SAIF.Business.View.Policy\clsPayrollClassInfoCollection.vb