FPY_COM_RPT_VW

(SQL View)
 Index Back

View for Reports

Setup Comparison Report View

SELECT 'SBU' , FPY_BUSINESS_UNIT ,'A' ,DESCR254 ,'FUSE' ,T.FPY_EXECDTTM FROM PS_FPY_BU_XREF X1 ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_BUS_UNIT_TBL_HR A WHERE A.BUSINESS_UNIT= X1.BUSINESS_UNIT) UNION ALL SELECT 'SBU' , BUSINESS_UNIT ,'A' ,DESCR ,'PSFT' ,T.FPY_EXECDTTM FROM PS_BUS_UNIT_TBL_HR A ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_BU_XREF X1 WHERE X1.BUSINESS_UNIT=A.BUSINESS_UNIT) UNION ALL SELECT 'SCO' ,%NumToChar(FPY_LEGALENTITY_ID) ,'A' ,DESCR254 ,'FUSE' ,T.FPY_EXECDTTM FROM PS_FPY_COMPNY_XREF X1 ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_COMPANY_TBL A WHERE A.COMPANY=X1.COMPANY) UNION ALL SELECT 'SCO' ,COMPANY ,EFF_STATUS ,DESCR ,'PSFT' ,T.FPY_EXECDTTM FROM PS_COMPANY_TBL A ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_COMPNY_XREF X1 WHERE X1.COMPANY=A.COMPANY) AND A.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_COMPANY_TBL D1 WHERE D1.COMPANY = A.COMPANY ) UNION ALL SELECT 'SDP' , %NumToChar(FPY_DEPT_ID ) ,FPY_EFF_STATUS ,DESCR254 ,'FUSE' ,T.FPY_EXECDTTM FROM PS_FPY_DEPT_XREF X1 ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_DEPT_TBL A WHERE A.SETID=X1.SETID AND A.DEPTID= X1.DEPTID) UNION ALL SELECT 'SDP' ,DEPTID ,EFF_STATUS ,DESCR ,'PSFT' ,T.FPY_EXECDTTM FROM PS_DEPT_TBL A ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_DEPT_XREF X1 WHERE X1.SETID=A.SETID AND X1.DEPTID= A.DEPTID) AND A.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID=A.SETID AND D1.DEPTID=A.DEPTID) UNION ALL SELECT 'SLC' ,%NumToChar(FPY_LOCATION_ID ) ,FPY_EFF_STATUS ,DESCR254 ,'FUSE' ,T.FPY_EXECDTTM FROM PS_FPY_LOCTN_XREF X1 ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_LOCATION_TBL A WHERE A.SETID=X1.SETID AND A.LOCATION= X1.LOCATION) UNION ALL SELECT 'SLC' ,LOCATION ,EFF_STATUS ,DESCR ,'PSFT' ,T.FPY_EXECDTTM FROM PS_LOCATION_TBL A ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_LOCTN_XREF X1 WHERE X1.SETID=A.SETID AND X1.LOCATION= A.LOCATION) AND A.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_LOCATION_TBL D1 WHERE D1.LOCATION = A.LOCATION AND D1.SETID=A.SETID ) UNION ALL SELECT 'SJC' , %NumToChar(FPY_JOB_ID ) ,'A' ,DESCR254 ,'FUSE' ,T.FPY_EXECDTTM FROM PS_FPY_JOBCD_XREF X1 ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_JOBCODE_TBL A WHERE A.SETID=X1.SETID AND A.JOBCODE= X1.JOBCODE) UNION ALL SELECT 'SJC' ,JOBCODE ,EFF_STATUS ,DESCR ,'PSFT' ,T.FPY_EXECDTTM FROM PS_JOBCODE_TBL A ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_JOBCD_XREF X1 WHERE X1.SETID=A.SETID AND X1.JOBCODE= A.JOBCODE) AND A.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_JOBCODE_TBL D1 WHERE D1.SETID=A.SETID AND D1.JOBCODE=A.JOBCODE ) UNION ALL SELECT 'SPO' ,%NumToChar(FPY_POSITION_ID ) ,FPY_EFF_STATUS ,FPY_NAME ,'FUSE' ,T.FPY_EXECDTTM FROM PS_FPY_POSTN_XREF X1 ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_POSITION_DATA A WHERE A.POSITION_NBR= X1.POSITION_NBR) UNION ALL SELECT 'SPO' ,POSITION_NBR ,EFF_STATUS ,DESCR ,'PSFT' ,T.FPY_EXECDTTM FROM PS_POSITION_DATA A ,PS_FPY_EXECDTTM_VW T WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_POSTN_XREF X1 WHERE X1.POSITION_NBR= A.POSITION_NBR) AND A.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_POSITION_DATA D1 WHERE D1.POSITION_NBR=A.POSITION_NBR )

# 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 FPY_KEY_STR1_VAL Character(120) VARCHAR2(120) NOT NULL Key String Value 1
3 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive
4 FPY_DESCR Long Character CLOB Description
5 FPY_AVAIL_CD Character(4) VARCHAR2(4) NOT NULL Availability Code
FUSE=Fusion
PSFT=Peoplesoft
6 FPY_EXECDTTM DateTime(26) TIMESTAMP Last Run Datetime