GPES_RSLT_AC_VW(SQL View) |
Index Back |
|---|---|
Accumulator result viewAccumulator result view |
| SELECT ACC.EMPLID , ACC.EMPL_RCD , ACC.CAL_RUN_ID , ACC.CAL_ID , ACC.PIN_NUM , ACC.USER_KEY1 , ACC.USER_KEY2 , ACC.USER_KEY3 , ACC.USER_KEY4 , PYE.RSLT_VER_NUM , MIN(PYE.SEG_BGN_DT) , MAX(PYE.SEG_END_DT) , MAX(F.PIN_FRML_FLD3_NUM) , SUM(ACC.CALC_RSLT_VAL) , PYE.PYMT_KEY1 , PYE.PYMT_KEY2 , PYE.PYMT_KEY3 , PYE.PYMT_KEY4 FROM PS_GP_RSLT_ACUM ACC , PS_GP_FORMULA_DTL F , PS_GP_PYE_SEG_STAT PYE WHERE PYE.EMPLID = ACC.EMPLID AND PYE.CAL_RUN_ID = ACC.CAL_RUN_ID AND PYE.EMPL_RCD = ACC.EMPL_RCD AND PYE.CAL_ID = ACC.CAL_ID AND PYE.GP_PAYGROUP = ACC.GP_PAYGROUP AND PYE.ORIG_CAL_RUN_ID = ACC.ORIG_CAL_RUN_ID AND PYE.RSLT_SEG_NUM = ACC.RSLT_SEG_NUM AND PYE.CALC_ACTION <> 'V' AND ACC.PIN_NUM = PIN_FRML_FLD1_NUM 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) GROUP BY ACC.EMPLID, ACC.EMPL_RCD, ACC.CAL_RUN_ID, ACC.CAL_ID, ACC.PIN_NUM, ACC.USER_KEY1, ACC.USER_KEY2, ACC.USER_KEY3, ACC.USER_KEY4, 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 Rcd Nbr |
| 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 | USER_KEY1 | Character(25) | VARCHAR2(25) NOT NULL | User Key 1 |
| 7 | USER_KEY2 | Character(25) | VARCHAR2(25) NOT NULL | User Key 2 |
| 8 | USER_KEY3 | Character(25) | VARCHAR2(25) NOT NULL | User Key 3 |
| 9 | USER_KEY4 | Character(25) | VARCHAR2(25) NOT NULL | User Key 4 |
| 10 | RSLT_VER_NUM | Number(4,0) | SMALLINT NOT NULL | Result Version Number |
| 11 | SEG_BGN_DT | Date(10) | DATE | Payee Process Segment's Begin Date |
| 12 | SEG_END_DT | Date(10) | DATE | Payee Process Segment's End Date |
| 13 | PIN_FRML_FLD3_NUM | Number(8,0) | INTEGER NOT NULL | Element number field |
| 14 | CALC_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Calculation Numeric Result |
| 15 | 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 |
| 16 | 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 |
| 17 | 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 |
| 18 | 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 |