SCH_REQ_SMRY_VW

(SQL View)
 Index Back

Emp Sch Request Source view


SELECT A.TRANSACTION_NBR , A.EMPLID , A.EMPL_RCD , NAM.NAME_DISPLAY , A.START_DATE , A.END_DATE , C.DESCR , A.SCH_TARGET_EMPLID , A.SCH_TARGET_EMPLRCD , '' , PH.EMPLOYEE_PHOTO , %DateDiff(A.START_DATE,A.END_DATE) +1 , D.DESCR , '' , S.COMMENTS FROM PS_SCH_REQ_SS_DAT A LEFT OUTER JOIN PS_SCH_REQ_SS_TBL S ON A.EMPLID=S.EMPLID AND A.EMPL_RCD = S.EMPL_RCD AND A.TRANSACTION_NBR=S.TRANSACTION_NBR LEFT OUTER JOIN PS_SCH_EMP_SCHD_VW D ON A.EMPLID=D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD , PS_NAMES NAM , PS_JOB JOB , PS_JOBCODE_TBL C , PS_USER_PH_LIST_FL PH WHERE A.EMPLID=PH.EMPLID AND NAM.EMPLID = A.EMPLID AND NAM.NAME_TYPE = 'PRI' AND NAM.EFFDT = ( SELECT MAX (NM1.EFFDT) FROM PS_NAMES NM1 WHERE NAM.EMPLID = NM1.EMPLID AND NM1.NAME_TYPE = NAM.NAME_TYPE AND NM1.EFFDT <= %CurrentDateIn) AND JOB.EMPLID = A.EMPLID AND JOB.EMPL_RCD = A.EMPL_RCD AND JOB.SETID_JOBCODE = C.SETID AND JOB.JOBCODE = C.JOBCODE AND C.EFF_STATUS = 'A' AND %EffdtCheck(JOBCODE_TBL, C, %CurrentDateIn) AND JOB.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE JOB.EMPLID=JOB2.EMPLID AND JOB.EMPL_RCD=JOB2.EMPL_RCD AND JOB2.EFFDT<=%CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT = JOB.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 TRANSACTION_NBR Number(15,0) DECIMAL(15) NOT NULL 08/02/99 NAS: Changed the size of this field from 10 to 15. Based on Jim Hillman's decision to make the field at a standard size.
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
5 START_DATE Date(10) DATE Start Date for Gen Standing PO
6 END_DATE Date(10) DATE End Date
7 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
8 SCH_TARGET_EMPLID Character(11) VARCHAR2(11) NOT NULL Schedule Target EmplId
9 SCH_TARGET_EMPLRCD Number(3,0) SMALLINT NOT NULL Schedule Target EmplRcd
10 DESCR30 Character(30) VARCHAR2(30) NOT NULL Description
11 EMPLOYEE_PHOTO Image / Attachment(256) VARCHAR2(256) Employee Photograph
12 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
13 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
14 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
15 COMMENTS Long Character CLOB Comment