PY_SRCH_GARN_VW

(SQL View)
 Index Back

VIEW FOR GARNISHMENTS

This view is used by SES to search Garnishments

SELECT J.EMPLID ,J.EFFDT ,J.LASTUPDDTTM ,C.COMPANY ,C.DESCR ,N.LAST_NAME_SRCH ,N.FIRST_NAME_SRCH ,N.SECOND_LAST_SRCH ,N.NAME_AC ,N.NAME_DISPLAY ,N.NAME_DISPLAY_SRCH ,N.LASTUPDDTTM ,N.NAME ,N.LAST_NAME ,N.FIRST_NAME ,N.SECOND_LAST_NAME ,N.EFFDT ,G.GARNID ,G.DOC_RCVD_DT FROM PS_JOB J , PS_NAMES N , PS_COMPANY_TBL C , PS_GARN_SPEC G 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(JB.EFFDT) FROM PS_JOB JB WHERE J.EMPLID=JB.EMPLID AND J.EMPL_RCD=JB.EMPL_RCD AND JB.EFFDT<=%CurrentDateIn) AND J.EFFSEQ =( SELECT MAX(JC.EFFSEQ) FROM PS_JOB JC WHERE JC.EMPLID=J.EMPLID AND JC.EMPL_RCD=J.EMPL_RCD AND JC.EFFDT=J.EFFDT) AND C.COMPANY = J.COMPANY AND C.EFFDT = ( SELECT MAX(CT.EFFDT) FROM PS_COMPANY_TBL CT WHERE CT.COMPANY = C.COMPANY AND CT.EFFDT <= %CurrentDateIn AND CT.EFF_STATUS = 'A') AND G.EMPLID = N.EMPLID AND G.COMPANY = C.COMPANY

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EFFDT Date(10) DATE Effective Date

Default Value: %date

3 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.
4 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
6 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
7 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
8 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
9 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
10 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
11 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
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 NAME Character(50) VARCHAR2(50) NOT NULL Name
14 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
15 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
16 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
17 EFFDT_NAME Date(10) DATE Effective Date
18 GARNID Character(6) VARCHAR2(6) NOT NULL Garnishment id.
19 DOC_RCVD_DT Date(10) DATE Document Received Date