GPES_RSLT_ED_VW(SQL View) |
Index Back |
|---|---|
Earnings/deduction result viewEarnings/deduction result view |
| SELECT RSLT.EMPLID , RSLT.EMPL_RCD , RSLT.CAL_RUN_ID , RSLT.CAL_ID , RSLT.PIN_NUM , PYE.RSLT_VER_NUM , MIN(PYE.SEG_BGN_DT) , MAX(PYE.SEG_END_DT) , MAX(F.PIN_FRML_FLD3_NUM) , MAX(PCT_RSLT_VAL) , SUM(RSLT.CALC_RSLT_VAL) , PYE.PYMT_KEY1 , PYE.PYMT_KEY2 , PYE.PYMT_KEY3 , PYE.PYMT_KEY4 FROM PS_GP_RSLT_ERN_DED RSLT , PS_GP_FORMULA_DTL F , PS_GP_PYE_SEG_STAT PYE WHERE PYE.EMPLID = RSLT.EMPLID AND PYE.CAL_RUN_ID = RSLT.CAL_RUN_ID AND PYE.EMPL_RCD = RSLT.EMPL_RCD AND PYE.CAL_ID = RSLT.CAL_ID AND PYE.GP_PAYGROUP = RSLT.GP_PAYGROUP AND PYE.ORIG_CAL_RUN_ID = RSLT.ORIG_CAL_RUN_ID AND PYE.RSLT_SEG_NUM = RSLT.RSLT_SEG_NUM AND PYE.CALC_ACTION <> 'V' AND F.PIN_NUM = ( SELECT PIN_NUM FROM PS_GP_PIN WHERE PIN_CODE = 'RTR FM MAPPING ESP') AND F.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GP_FORMULA FR , PS_GP_PYE_PRC_STAT PR WHERE PR.EMPLID = PYE.EMPLID AND PR.CAL_RUN_ID = PYE.CAL_RUN_ID AND PR.EMPL_RCD = PYE.EMPL_RCD AND PR.CAL_ID = PYE.CAL_ID AND PR.GP_PAYGROUP = PYE.GP_PAYGROUP AND PR.ORIG_CAL_RUN_ID = PYE.ORIG_CAL_RUN_ID AND FR.PIN_NUM = F.PIN_NUM AND FR.EFFDT <= PR.PRD_END_DT) AND RSLT.PIN_NUM = F.PIN_FRML_FLD1_NUM GROUP BY RSLT.EMPLID, RSLT.EMPL_RCD, RSLT.CAL_RUN_ID, RSLT.CAL_ID, RSLT.PIN_NUM, PYE.RSLT_VER_NUM, PYE.SEG_BGN_DT, PYE.SEG_END_DT ,PYE.PYMT_KEY1,PYE.PYMT_KEY2,PYE.PYMT_KEY3,PYE.PYMT_KEY4 |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | CAL_RUN_ID | Character(18) | VARCHAR2(18) NOT NULL | Calendar Run Id |
| 4 | CAL_ID | Character(18) | VARCHAR2(18) NOT NULL | calendar id |
| 5 | PIN_NUM | Number(8,0) | INTEGER NOT NULL | PIN Number |
| 6 | RSLT_VER_NUM | Number(4,0) | SMALLINT NOT NULL | Result Version Number |
| 7 | SEG_BGN_DT | Date(10) | DATE | Payee Process Segment's Begin Date |
| 8 | SEG_END_DT | Date(10) | DATE | Payee Process Segment's End Date |
| 9 | PIN_FRML_FLD3_NUM | Number(8,0) | INTEGER NOT NULL | Element number field |
| 10 | PCT_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Percent Result Value |
| 11 | CALC_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Calculation Numeric Result |
| 12 | PYMT_KEY1 | Character(25) | VARCHAR2(25) NOT NULL | Payment key field. Used for optional tracking of a payment per Department, Company, Establishment or Contract Number |
| 13 | PYMT_KEY2 | Character(25) | VARCHAR2(25) NOT NULL | Payment key field. Used for optional tracking of a payment per Department, Company, Establishment or Contract Number |
| 14 | PYMT_KEY3 | Character(25) | VARCHAR2(25) NOT NULL | Payment key field. Used for optional tracking of a payment per Department, Company, Establishment or Contract Number |
| 15 | PYMT_KEY4 | Character(25) | VARCHAR2(25) NOT NULL | Payment key field. Used for optional tracking of a payment per Department, Company, Establishment or Contract Number |