VC_GRP_HIST_VW

(SQL View)
 Index Back

Plan member History

Variable Comp: This view is used to load historical eligibility status for plan members.

SELECT C.GB_OPRID , C.PNLGRPNAME , A.VC_PLAN_ID , A.VC_PAYOUT_PRD_ID , A.GB_GROUP_ID , A.EMPLID , A.EMPL_RCD , B.NAME , D.PRORATE_FACTOR , A.AS_OF_DATE , A.VC_ELIG_STATUS FROM PS_VC_MEM_DATA A , PS_PERSON_NAME B , PS_VC_CHK_CPGRP_VW C , PS_VC_PLAN_MEM D WHERE A.EMPLID = B.EMPLID AND A.VC_PLAN_ID = C.VC_PLAN_ID AND A.VC_PAYOUT_PRD_ID = C.VC_PAYOUT_PRD_ID AND A.GB_GROUP_ID = C.GB_GROUP_ID AND A.VC_PLAN_ID = D.VC_PLAN_ID AND A.VC_PAYOUT_PRD_ID = D.VC_PAYOUT_PRD_ID AND A.GB_GROUP_ID = D.GB_GROUP_ID AND A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND (A.AS_OF_DATE <> D.AS_OF_DATE OR (A.AS_OF_DATE = D.AS_OF_DATE AND A.VC_ELIG_STATUS <> D.VC_ELIG_STATUS))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GB_OPRID Character(30) VARCHAR2(30) NOT NULL User ID
2 PNLGRPNAME Character(18) VARCHAR2(18) NOT NULL Component Name
3 VC_PLAN_ID Character(10) VARCHAR2(10) NOT NULL VC Plan ID

Prompt Table: VC_PLAN_TBL

4 VC_PAYOUT_PRD_ID Character(10) VARCHAR2(10) NOT NULL Payout Period ID
5 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.
6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
7 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
8 NAME Character(50) VARCHAR2(50) NOT NULL Name
9 PRORATE_FACTOR Number(8,3) DECIMAL(7,3) NOT NULL Prorate Factor
10 AS_OF_DATE Date(10) DATE As of Date
11 VC_ELIG_STATUS Character(2) VARCHAR2(2) NOT NULL Eligibility Status
AE=Auto Eligible
AI=Auto Ineligible
EP=Eligible with Proration
ME=Manual Eligible
MI=Manual Ineligble