FPY_SETUP_SUM

(SQL View)
 Index Back

Setup for Summary Reports

Setup for Summary Report View

SELECT 'SBU' , ( SELECT COUNT(*) FROM PS_BUS_UNIT_TBL_HR B WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_BU_XREF X1 WHERE X1.BUSINESS_UNIT= B.BUSINESS_UNIT) ) , ( SELECT COUNT(*) FROM PS_FPY_BU_XREF X1 WHERE NOT EXISTS( SELECT 'X' FROM PS_BUS_UNIT_TBL_HR B WHERE B.BUSINESS_UNIT=X1.BUSINESS_UNIT ) ) ,'DSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SBU' , ( SELECT COUNT(*) FROM PS_BUS_UNIT_TBL_HR B WHERE RTRIM(B.BUSINESS_UNIT) IS NOT NULL) , ( SELECT COUNT(*) FROM PS_FPY_BU_XREF X1 ) ,'CSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SCO' , ( SELECT COUNT(*) FROM PS_COMPANY_TBL D WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_COMPNY_XREF X1 WHERE X1.COMPANY =D.COMPANY ) AND RTRIM(D.COMPANY) IS NOT NULL AND D.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_COMPANY_TBL D1 WHERE D1.COMPANY = D.COMPANY )) , ( SELECT COUNT(*) FROM PS_FPY_COMPNY_XREF X1 WHERE NOT EXISTS( SELECT 'X' FROM PS_COMPANY_TBL D WHERE D.COMPANY = X1.COMPANY)) ,'DSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SCO' , ( SELECT COUNT(*) FROM PS_COMPANY_TBL B WHERE RTRIM(B.COMPANY) IS NOT NULL AND B.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_COMPANY_TBL D1 WHERE D1.COMPANY = B.COMPANY )) , ( SELECT COUNT(*) FROM PS_FPY_COMPNY_XREF X1 ) ,'CSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SDP' , ( SELECT COUNT( *) FROM PS_DEPT_TBL D WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_DEPT_XREF X1 WHERE D.SETID=X1.SETID AND D.DEPTID=X1.DEPTID) AND D.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID=D.SETID AND D1.DEPTID=D.DEPTID)) , ( SELECT COUNT( * ) FROM PS_FPY_DEPT_XREF X1 WHERE NOT EXISTS( SELECT 'X' FROM PS_DEPT_TBL D WHERE D.SETID=X1.SETID AND D.DEPTID=X1.DEPTID )) ,'DSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SDP' ,( SELECT COUNT(*) FROM PS_DEPT_TBL D WHERE D.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID=D.SETID AND D1.DEPTID=D.DEPTID) ) , ( SELECT COUNT(*) FROM PS_FPY_DEPT_XREF X1 ) ,'CSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SLC' , ( SELECT COUNT(*) FROM PS_LOCATION_TBL D WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_LOCTN_XREF X1 WHERE X1.SETID=D.SETID AND X1.LOCATION =D.LOCATION ) AND RTRIM(D.LOCATION) IS NOT NULL AND D.EFFDT=(( SELECT MAX(D1.EFFDT) FROM PS_LOCATION_TBL D1 WHERE D1.LOCATION = D.LOCATION AND D1.SETID=D.SETID ))) , ( SELECT COUNT(*) FROM PS_FPY_LOCTN_XREF X1 WHERE NOT EXISTS( SELECT D.LOCATION FROM PS_LOCATION_TBL D WHERE D.SETID=X1.SETID AND D.LOCATION =X1.LOCATION) ) ,'DSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SLC' , ( SELECT COUNT(*) FROM PS_LOCATION_TBL B WHERE RTRIM(B.LOCATION) IS NOT NULL AND B.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_LOCATION_TBL D1 WHERE D1.LOCATION = B.LOCATION AND D1.SETID=B.SETID) ) , ( SELECT COUNT(*) FROM PS_FPY_LOCTN_XREF X1 ) ,'CSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SJC' , ( SELECT COUNT(*) FROM PS_JOBCODE_TBL D WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_JOBCD_XREF X1 WHERE X1.SETID=D.SETID AND X1.JOBCODE=D.JOBCODE ) AND D.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_JOBCODE_TBL D1 WHERE D1.SETID=D.SETID AND D1.JOBCODE=D.JOBCODE )) , ( SELECT COUNT(*) FROM PS_FPY_JOBCD_XREF X1 WHERE NOT EXISTS( SELECT 'X' FROM PS_JOBCODE_TBL D WHERE D.SETID=X1.SETID AND D.JOBCODE=X1.JOBCODE ) ) ,'DSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SJC' , ( SELECT COUNT(*) FROM PS_JOBCODE_TBL B WHERE RTRIM(B.JOBCODE) IS NOT NULL AND B.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_JOBCODE_TBL D1 WHERE D1.SETID=B.SETID AND D1.JOBCODE=B.JOBCODE ) ) , ( SELECT COUNT(*) FROM PS_FPY_JOBCD_XREF X1 ) ,'CSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SPO' , ( SELECT COUNT(*) FROM PS_POSITION_DATA D WHERE NOT EXISTS( SELECT 'X' FROM PS_FPY_POSTN_XREF X1 WHERE X1.POSITION_NBR=D.POSITION_NBR ) AND D.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_POSITION_DATA D1 WHERE D1.POSITION_NBR=D.POSITION_NBR ) ) , ( SELECT COUNT(*) FROM PS_FPY_POSTN_XREF X1 WHERE NOT EXISTS( SELECT 'X' FROM PS_POSITION_DATA D WHERE D.POSITION_NBR= X1.POSITION_NBR ) ) ,'DSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'SPO' , ( SELECT COUNT(*) FROM PS_POSITION_DATA B WHERE B.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_POSITION_DATA D1 WHERE D1.POSITION_NBR=B.POSITION_NBR ) ) , ( SELECT COUNT(*) FROM PS_FPY_POSTN_XREF X1 ) ,'CSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'EMP' , ( SELECT COUNT(*) FROM PS_JOB A WHERE 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) AND A.PER_ORG='EMP') , ( SELECT COUNT(DISTINCT FPY_PERSON_ID) FROM PS_FPY_ASG_XREF X1 WHERE 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') AND X1.FPY_PER_ORG='EMP') ,'DSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'EMP' , ( SELECT COUNT(*) FROM PS_JOB A WHERE A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD ) AND A.PER_ORG='EMP') , ( SELECT COUNT(DISTINCT FPY_PERSON_ID) FROM PS_FPY_ASG_XREF X1 WHERE X1.FPY_PER_ORG='EMP') ,'CSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'CWR' , ( SELECT COUNT(*) FROM PS_JOB A WHERE 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) AND A.PER_ORG='CWR') , ( SELECT COUNT(DISTINCT FPY_PERSON_ID) FROM PS_FPY_ASG_XREF X1 WHERE 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') AND X1.FPY_PER_ORG='CWK') ,'DSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'CWR' , ( SELECT COUNT(*) FROM PS_JOB A WHERE A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD) AND A.PER_ORG='CWR' ) , ( SELECT COUNT(DISTINCT FPY_PERSON_ID) FROM PS_FPY_ASG_XREF X1 WHERE X1.FPY_PER_ORG='CWK') ,'CSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'POI' , ( SELECT COUNT(*) FROM PS_JOB A WHERE 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) AND A.PER_ORG='POI') , ( SELECT COUNT(DISTINCT FPY_PERSON_ID) FROM PS_FPY_ASG_XREF X1 WHERE 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') AND X1.FPY_PER_ORG='POI') ,'DSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION UNION ALL SELECT 'POI' , ( SELECT COUNT(*) FROM PS_JOB A WHERE A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD) AND A.PER_ORG='POI') , ( SELECT COUNT(DISTINCT FPY_PERSON_ID) FROM PS_FPY_ASG_XREF X1 WHERE X1.FPY_PER_ORG='POI') ,'CSET' , ( SELECT MAX(FPY_EXECDTTM) FROM PS_FPY_REF_RUNCNTL ) FROM PS_INSTALLATION

# 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_CNT_PSFT Number(6,0) INTEGER NOT NULL Peoplesoft Count
3 FPY_CNT_FUSION Number(6,0) INTEGER NOT NULL Fusion Count
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