BAS_STMT_SUM_VW

(SQL View)
 Index Back

Lists benefits statements


SELECT DISTINCT S.BAS_STMT_TYPE , S.EMPLID , S.BENEFIT_RCD_NBR , S.SCHED_ID , S.EVENT_ID , S.EFFDT , S.EFFSEQ , S.EVENT_DT , S.CONFIRM_NOTIFY_DT , S.COMPANY , B.SETID_JOBCODE , B.JOBCODE FROM PS_BAS_STMT_SUMM S ,PS_BAS_PARTIC A , PS_JOB B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND A.JOB_EFFSEQ = B.EFFSEQ AND A.SCHED_ID = S.SCHED_ID AND A.EMPLID = S.EMPLID AND A.BENEFIT_RCD_NBR = S.BENEFIT_RCD_NBR AND A.EVENT_ID = S.EVENT_ID AND S.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 BAS_STMT_TYPE Character(1) VARCHAR2(1) NOT NULL Benefits Statement Type
C=Confirmation Statement
E=Enrollment Preview
S=Submitted Enrollment

Default Value: C

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 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.
4 SCHED_ID Character(6) VARCHAR2(6) NOT NULL Schedule ID
5 EVENT_ID Number(6,0) INTEGER NOT NULL Event Identification
6 EFFDT Date(10) DATE Effective Date

Default Value: %date

7 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
8 EVENT_DT Date(10) DATE Event Date
9 CONFIRM_NOTIFY_DT Date(10) DATE Date Notified of Confirmation
10 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
11 SETID Character(5) VARCHAR2(5) NOT NULL SetID
12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code