PERS_SMOKER_VW3 (SQL View)	Index Back
PERSSmoker Current Outer Join Extension of the PERSON object for Smoker Current Information. This view has an outer join to PERSON so that all persons are retrieved regardless of whether they have a record in PER_SMOKER. Please review all database types when modifying the SQL.

PERS_SMOKER_VW3

(SQL View)

PERSSmoker Current Outer Join

Extension of the PERSON object for Smoker Current Information. This view has an outer join to PERSON so that all persons are retrieved regardless of whether they have a record in PER_SMOKER. Please review all database types when modifying the SQL.

SELECT P.EMPLID ,A.SMOKER_DT ,A.SMOKER FROM PS_PERSON P , PS_PERS_SMOKER A WHERE P.EMPLID = A.EMPLID (+) and SMOKER_DT = ( SELECT MAX(SMOKER_DT) FROM PS_PERS_SMOKER B WHERE A.EMPLID = B.EMPLID AND B.SMOKER_DT <= %CurrentDateIn)

Parent record: PERSON

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Default Value: PERSON.EMPLID
Prompt Table: PERSON

2 SMOKER_DT Date(10) DATE NOT NULL Smoker Status Date

3 SMOKER Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) an employee or dependent is a smoker.
N=Non Smoker
Y=Smoker