BAS_CONF_STM_VW (SQL View)	Index Back
Confirmation Statement View Used to get current versions confirmation statements for employees.

BAS_CONF_STM_VW

(SQL View)

Confirmation Statement View

Used to get current versions confirmation statements for employees.

SELECT EMPLID , BENEFIT_RCD_NBR , SCHED_ID , EVENT_ID , EFFDT , EFFSEQ , EVENT_DT , CONFIRM_NOTIFY_DT FROM PS_BAS_STMT_SUMM S WHERE BAS_STMT_TYPE = 'C' AND EFFDT = ( SELECT MAX(EFFDT) FROM PS_BAS_STMT_SUMM S1 WHERE S1.BAS_STMT_TYPE = S.BAS_STMT_TYPE AND S1.SCHED_ID = S.SCHED_ID AND S1.EMPLID = S.EMPLID AND S1.BENEFIT_RCD_NBR = S.BENEFIT_RCD_NBR AND S1.EVENT_ID = S.EVENT_ID AND S1.EFFDT <= %CurrentDateIn ) AND EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_BAS_STMT_SUMM S2 WHERE S2.BAS_STMT_TYPE = S.BAS_STMT_TYPE AND S2.SCHED_ID = S.SCHED_ID AND S2.EMPLID = S.EMPLID AND S2.BENEFIT_RCD_NBR = S.BENEFIT_RCD_NBR AND S2.EVENT_ID = S.EVENT_ID AND S2.EFFDT = S.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.

3 SCHED_ID Character(6) VARCHAR2(6) NOT NULL Schedule ID

4 EVENT_ID Number(6,0) INTEGER NOT NULL Event Identification

5 EFFDT Date(10) DATE Effective Date
Default Value: %date

6 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

7 EVENT_DT Date(10) DATE Event Date

8 CONFIRM_NOTIFY_DT Date(10) DATE Date Notified of Confirmation

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	BENEFIT_RCD_NBR	Number(3,0)	SMALLINT NOT NULL	Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
3	SCHED_ID	Character(6)	VARCHAR2(6) NOT NULL	Schedule ID
4	EVENT_ID	Number(6,0)	INTEGER NOT NULL	Event Identification
5	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
6	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
7	EVENT_DT	Date(10)	DATE	Event Date
8	CONFIRM_NOTIFY_DT	Date(10)	DATE	Date Notified of Confirmation