FPY_JOB_CRPT_VW

(SQL View)
 Index Back

View for Reports

Job Comparison Report View

SELECT DISTINCT 'EMP' ,FPY_PERSON_NUM ,EMPL_RCD ,'FUSE' ,T.FPY_EXECDTTM FROM PS_FPY_ASG_XREF X1 ,PS_FPY_EXECDTTM_VW T WHERE X1.FPY_PER_ORG='EMP' AND NOT EXISTS ( SELECT 'X' FROM PS_JOB A WHERE A.EMPLID=X1.EMPLID AND A.EMPL_RCD=X1.EMPL_RCD AND A.PER_ORG='EMP') UNION ALL SELECT 'EMP' ,EMPLID ,EMPL_RCD ,'PSFT' ,T.FPY_EXECDTTM FROM PS_JOB A ,PS_FPY_EXECDTTM_VW T WHERE A.PER_ORG='EMP' AND NOT EXISTS( SELECT 'X' FROM PS_FPY_ASG_XREF X1 WHERE X1.EMPLID=A.EMPLID AND X1.EMPL_RCD=A.EMPL_RCD AND X1.FPY_PER_ORG='EMP') AND A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD) UNION ALL SELECT DISTINCT 'CWR' ,FPY_PERSON_NUM ,EMPL_RCD ,'FUSE' ,T.FPY_EXECDTTM FROM PS_FPY_ASG_XREF X1 ,PS_FPY_EXECDTTM_VW T WHERE X1.FPY_PER_ORG='CWK' AND NOT EXISTS ( SELECT 'X' FROM PS_JOB A WHERE A.EMPLID=X1.EMPLID AND A.EMPL_RCD=X1.EMPL_RCD AND A.PER_ORG='CWR') UNION ALL SELECT 'CWR' ,EMPLID ,EMPL_RCD ,'PSFT' ,T.FPY_EXECDTTM FROM PS_JOB A ,PS_FPY_EXECDTTM_VW T WHERE A.PER_ORG='CWR' AND NOT EXISTS( SELECT 'X' FROM PS_FPY_ASG_XREF X1 WHERE X1.EMPLID=A.EMPLID AND X1.EMPL_RCD=A.EMPL_RCD AND X1.FPY_PER_ORG='CWK') AND A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD) UNION ALL SELECT 'POI' ,FPY_PERSON_NUM ,EMPL_RCD ,'FUSE' ,T.FPY_EXECDTTM FROM PS_FPY_ASG_XREF X1 ,PS_FPY_EXECDTTM_VW T WHERE X1.FPY_PER_ORG='POI' AND NOT EXISTS ( SELECT 'X' FROM PS_JOB A WHERE A.EMPLID=X1.EMPLID AND A.EMPL_RCD=X1.EMPL_RCD AND A.PER_ORG='POI') UNION ALL SELECT 'EMP' ,EMPLID ,EMPL_RCD ,'PSFT' ,T.FPY_EXECDTTM FROM PS_JOB A ,PS_FPY_EXECDTTM_VW T WHERE A.PER_ORG='POI' AND NOT EXISTS( SELECT 'X' FROM PS_FPY_ASG_XREF X1 WHERE X1.EMPLID=A.EMPLID AND X1.EMPL_RCD=A.EMPL_RCD AND X1.FPY_PER_ORG='POI') AND A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 FPY_RPT_CONDITION Character(3) VARCHAR2(3) NOT NULL Report Condition
ALL=All
CWR=Contigent Worker
EMP=Employee
POI=Person of Interest
SBU=Business Unit
SCO=Company
SDP=Department
SJC=Job Code
SLC=Location
SPO=Position
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 FPY_AVAIL_CD Character(4) VARCHAR2(4) NOT NULL Availability Code
FUSE=Fusion
PSFT=Peoplesoft
5 FPY_EXECDTTM DateTime(26) TIMESTAMP Last Run Datetime