VC_PLAN_HIST_VW (SQL View)	Index Back
Plan member History Variable Comp: This view is used to load historical eligibility status for plan members. Note: This view originally joined to EMPLMT_SRCH_GBL. This had poor performance, so the view now directly incorporates the SQL from EMPLMT_SRCH_GBL. Further, the view fieldlist was modified to remove the standard security subrecords, which included many more fields than necessary.

VC_PLAN_HIST_VW

(SQL View)

Plan member History

Variable Comp: This view is used to load historical eligibility status for plan members. Note: This view originally joined to EMPLMT_SRCH_GBL. This had poor performance, so the view now directly incorporates the SQL from EMPLMT_SRCH_GBL. Further, the view fieldlist was modified to remove the standard security subrecords, which included many more fields than necessary.

SELECT OPR.OPRID , A.VC_PLAN_ID , A.VC_PAYOUT_PRD_ID , A.GB_GROUP_ID , A.EMPLID , A.EMPL_RCD , A.AS_OF_DATE , NM.NAME , A.VC_ELIG_STATUS FROM PS_VC_MEM_DATA A , PS_VC_PLAN_MEM D , %Sql(SCRTY_PER_FROM) , PS_PERSON_NAME NM WHERE 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)) AND SEC.EMPLID = A.EMPLID AND SEC.EMPL_RCD = A.EMPL_RCD AND %Sql(SCRTY_WHERE, 'PPLJOB') AND %Sql(SCRTY_NO_APPT1) AND NM.EMPLID = A.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

2 VC_PLAN_ID Character(10) VARCHAR2(10) NOT NULL VC Plan ID

3 VC_PAYOUT_PRD_ID Character(10) VARCHAR2(10) NOT NULL Payout Period ID

4 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.

5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSON_NAME

6 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
Prompt Table: VC_ACT_PERS_VW

7 AS_OF_DATE Date(10) DATE As of Date

8 NAME Character(50) VARCHAR2(50) NOT NULL Name

9 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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	VC_PLAN_ID	Character(10)	VARCHAR2(10) NOT NULL	VC Plan ID
3	VC_PAYOUT_PRD_ID	Character(10)	VARCHAR2(10) NOT NULL	Payout Period ID
4	GB_GROUP_ID	Character(15)	VARCHAR2(15) NOT NULL	Group Build ID.
5	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON_NAME
6	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr Prompt Table: VC_ACT_PERS_VW
7	AS_OF_DATE	Date(10)	DATE	As of Date
8	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
9	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