View: dbo.Client_Benefit_View
View definition
CREATE VIEW dbo.Client_Benefit_View
AS
SELECT
cb.client_benefit_KEY
, cb.client_KEY
, cb.description
, cb.graduated_allowance_calculation__length_of_service_selection_KEY
, cb.accrual_method_KEY
, cb.benefit_accrual_balance_reset_type_KEY
, cb.basis__compare_to_date_type_KEY
, cb.include_overtime_hours
, cb.include_doubletime_hours
, cb.allowance_amount
, cb.available_accrual_limit
, cb.per_month_accrual_limit
, cb.annual_accrual_limit
, cb.per_check_accrual_limit
, cb.reset_balances_to_zero
, cb.carryover_maximum
, cb.balance_reset_date
, cb.report_description
, cb.department_specific
, cb.selection_scope_KEY
, cb.client_benefit_guid
, cb.sequence_number
, cb.benefit_period_usage_type_KEY
, cb.record_status_KEY
, cb.insurepay_accruable_benefit_type_KEY
, cb.xactpay_accruable_benefit_type_KEY
, cb.accrue_benefits_on_benefit_hours_used
, cb.work_hours_per_benefit_allowance
, cb.accrual_delay_value
, cb.accrual_delay__benefit_eligibility_duration_type_KEY
, cb.availability_delay_value
, cb.availability_delay__benefit_eligibility_duration_type_KEY
, cb.annual_usage_limit
, cb.per_month_usage_limit
, cb.per_check_usage_limit
-- to maintain consistency, added a new column as 'active_status_KEY' to fetch the active status.
, CASE WHEN cb.record_status_KEY = 2
THEN 2
ELSE 1
END AS active_status_KEY
FROM
dbo.Client_Benefit AS cb
;