GPCN_AWE_RS_VW

(SQL View)
 Index Back

View for AWE Reports

This is the view that will be used to query all the data for the reports.

SELECT RS.PROCESS_INSTANCE , RS.EMPLID , RS.CAL_RUN_ID , RS.EMPL_RCD , RS.GP_PAYGROUP , PRC.CAL_PRD_ID , RS.CAL_ID , RS.RSLT_SEG_NUM , RS.PIN_NUM , RS.PIN_CODE , RS.PIN_NM , RS.CALC_RSLT_VAL , PERSON.NAME , LOCATION.DESCR AS LOCATION_NAME , COMPANY.DESCR AS COMPANY_DESCR , DEPT.DESCR AS DEPTNAME FROM PS_GPCN_AWE_RS RS , PS_NAMES PERSON , PS_LOCATION_TBL LOCATION , PS_COMPANY_TBL COMPANY , PS_DEPT_TBL DEPT , PS_GP_PYE_PRC_STAT PRC , PS_JOB JOB , PS_GP_CAL_PRD PRD WHERE RS.EMPLID = PERSON.EMPLID AND RS.CAL_RUN_ID = PRC.CAL_RUN_ID AND RS.EMPL_RCD = PRC.EMPL_RCD AND RS.GP_PAYGROUP = PRC.GP_PAYGROUP AND RS.CAL_ID = PRC.CAL_ID AND PRC.ORIG_CAL_RUN_ID = PRC.CAL_RUN_ID AND RS.EMPLID = JOB.EMPLID AND RS.EMPL_RCD = JOB.EMPL_RCD AND RS.GP_PAYGROUP = JOB.GP_PAYGROUP AND RS.EMPLID = PRC.EMPLID AND JOB.LOCATION = LOCATION.LOCATION AND JOB.COMPANY = COMPANY.COMPANY AND JOB.DEPTID = DEPT.DEPTID AND PRC.CAL_PRD_ID = PRD.CAL_PRD_ID AND JOB.SETID_DEPT = DEPT.SETID AND PERSON.NAME_TYPE = 'PRI' AND PERSON.EFFDT = ( SELECT MAX(N.EFFDT) FROM PS_NAMES N WHERE N.EMPLID = PERSON.EMPLID AND N.NAME_TYPE = 'PRI' AND N.EFFDT <= PRD.PRD_END_DT) AND LOCATION.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_LOCATION_TBL D_ED WHERE LOCATION.SETID = D_ED.SETID AND LOCATION.LOCATION = D_ED.LOCATION AND D_ED.EFFDT <= PRD.PRD_END_DT) AND COMPANY.EFFDT = ( SELECT MAX(E_ED.EFFDT) FROM PS_COMPANY_TBL E_ED WHERE COMPANY.COMPANY = E_ED.COMPANY AND E_ED.EFFDT <= PRD.PRD_END_DT) AND DEPT.EFFDT = ( SELECT MAX(F_ED.EFFDT) FROM PS_DEPT_TBL F_ED WHERE DEPT.SETID = F_ED.SETID AND DEPT.DEPTID = F_ED.DEPTID AND F_ED.EFFDT <= PRD.PRD_END_DT) AND JOB.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_JOB C_ED WHERE JOB.EMPLID = C_ED.EMPLID AND JOB.EMPL_RCD = C_ED.EMPL_RCD AND C_ED.EFFDT <= PRD.PRD_END_DT) AND JOB.EFFSEQ = ( SELECT MAX(C_ES.EFFSEQ) FROM PS_JOB C_ES WHERE JOB.EMPLID = C_ES.EMPLID AND JOB.EMPL_RCD = C_ES.EMPL_RCD AND JOB.EFFDT = C_ES.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
5 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
6 CAL_PRD_ID Character(10) VARCHAR2(10) NOT NULL Calendar Period ID
7 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
8 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number
9 PIN_NUM Number(8,0) INTEGER NOT NULL PIN Number
10 PIN_CODE Character(22) VARCHAR2(22) NOT NULL Unique identifier of a PIN (alternate to PIN number). critical for use in merging packages of elements from one database onto another when PINs may or may not be renumbered as they are moved across. Constructed from PIN_NM + COUNTRY of the element definition (GP_PIN)
11 PIN_NM Character(18) VARCHAR2(18) NOT NULL Element Name field
12 CALC_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Numeric Result
13 NAME Character(50) VARCHAR2(50) NOT NULL Name
14 LOCATION_NAME Character(30) VARCHAR2(30) NOT NULL Location Name
15 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
16 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department Name