PY_SRCH_GDED_VW

(SQL View)
 Index Back

General Deductions View

This view is used by SES to build the General Deductions Index

SELECT N.EMPLID , N.NAME , N.LAST_NAME_SRCH , N.MIDDLE_NAME , N.SECOND_LAST_SRCH , N.NAME_AC , N.NAME_DISPLAY_SRCH , N.LAST_NAME , N.FIRST_NAME , N.SECOND_LAST_NAME , N.NAME_DISPLAY , N.LASTUPDDTTM , J.EMPLID , J.COMPANY , J.EFFDT , J.LASTUPDDTTM , C.DESCR , GD.LAST_UPDATE_DATE FROM PS_NAMES N , PS_JOB J , PS_COMPANY_TBL C , PS_GENL_DEDUCTION GD WHERE N.EMPLID = J.EMPLID AND N.NAME_TYPE ='PRI' AND N.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_NAMES A_ED WHERE N.EMPLID = A_ED.EMPLID AND N.NAME_TYPE = A_ED.NAME_TYPE AND A_ED.EFFDT <= %CurrentDateIn) AND J.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_JOB B WHERE J.EMPLID=B.EMPLID AND J.EMPL_RCD=B.EMPL_RCD AND B.EFFDT<=%CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID=J.EMPLID AND C.EMPL_RCD=J.EMPL_RCD AND C.EFFDT=J.EFFDT) AND C.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_COMPANY_TBL B WHERE B.COMPANY = C.COMPANY AND B.EFFDT <= %CurrentDateIn AND B.EFF_STATUS = 'A') AND GD.EMPLID = J.EMPLID AND GD.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_GENL_DEDUCTION A_ED WHERE GD.EMPLID = A_ED.EMPLID AND GD.COMPANY = A_ED.COMPANY AND GD.DEDCD = A_ED.DEDCD AND A_ED.EFFDT <= %CurrentDateIn ) AND GD.COMPANY = C.COMPANY

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 NAME Character(50) VARCHAR2(50) NOT NULL Name
3 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
4 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
5 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
6 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
7 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
8 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
9 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
10 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
11 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
12 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
13 EMPLID2 Character(11) VARCHAR2(11) NOT NULL Field is referenced in state record used by Cobra Upgrade.
14 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
15 EFFDT Date(10) DATE Effective Date

Default Value: %date

16 LASTUPDDTTM_J DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
17 DESCR Character(30) VARCHAR2(30) NOT NULL Description
18 LAST_UPDATE_DATE Date(10) DATE Date of last update