GPFR_RSLT_N2_VW(SQL View) |
Index Back |
---|---|
Result view: Net + retroResult Table for the net to be paid (including retro) - Part 2 |
SELECT B.EMPLID ,B.CAL_RUN_ID ,B.EMPL_RCD ,B.GP_PAYGROUP ,B.CAL_ID ,B.RSLT_SEG_NUM ,D.PIN_NET_NUM ,D.PIN_NET_VAL ,B.PYMT_KEY1 ,B.PYMT_KEY2 ,B.PYMT_KEY3 ,B.PYMT_KEY4 ,D.CAL_ID ,'R' FROM PS_GP_PYE_SEG_STAT B , PS_GP_PYE_SEG_STAT D , PS_GP_PYE_PRC_STAT PB , PS_GP_PYE_PRC_STAT PD WHERE D.EMPLID = B.EMPLID AND D.CAL_RUN_ID = B.CAL_RUN_ID AND D.EMPL_RCD = B.EMPL_RCD AND D.GP_PAYGROUP = B.GP_PAYGROUP AND D.CAL_ID <> B.CAL_ID AND D.PYMT_KEY1 = B.PYMT_KEY1 AND D.PYMT_KEY2 = B.PYMT_KEY2 AND D.PYMT_KEY3 = B.PYMT_KEY3 AND D.PYMT_KEY4 = B.PYMT_KEY4 AND D.CALC_ACTION IN ('C','V') AND D.RSLT_REV_NUM = 1 AND B.RSLT_SEG_NUM = ( SELECT MIN(C.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT C WHERE C.EMPLID = B.EMPLID AND C.CAL_RUN_ID = B.CAL_RUN_ID AND C.EMPL_RCD = B.EMPL_RCD AND C.GP_PAYGROUP = B.GP_PAYGROUP AND C.CAL_ID = B.CAL_ID AND C.PYMT_KEY1 = B.PYMT_KEY1 AND C.PYMT_KEY2 = B.PYMT_KEY2 AND C.PYMT_KEY3 = B.PYMT_KEY3 AND C.PYMT_KEY4 = B.PYMT_KEY4 AND B.CALC_ACTION = 'C' AND B.RSLT_VER_NUM = 1 AND B.RSLT_REV_NUM = 1 ) AND NOT EXISTS ( SELECT 'X' FROM PS_GP_RSLT_DELTA SUBD WHERE SUBD.CAL_RUN_ID = B.CAL_RUN_ID AND SUBD.CAL_ID = B.CAL_ID AND SUBD.GP_PAYGROUP = B.GP_PAYGROUP AND SUBD.PIN_NUM = B.PIN_NET_NUM) AND PB.EMPLID = B.EMPLID AND PB.CAL_RUN_ID = B.CAL_RUN_ID AND PB.EMPL_RCD = B.EMPL_RCD AND PB.GP_PAYGROUP = B.GP_PAYGROUP AND PB.CAL_ID = B.CAL_ID AND PB.CALC_TYPE='P' AND PD.EMPLID = D.EMPLID AND PD.CAL_RUN_ID = D.CAL_RUN_ID AND PD.EMPL_RCD = D.EMPL_RCD AND PD.GP_PAYGROUP = D.GP_PAYGROUP AND PD.CAL_ID = D.CAL_ID AND PD.CALC_TYPE='P' |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
2 | CAL_RUN_ID | Character(18) | VARCHAR2(18) NOT NULL | Calendar Run Id |
3 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
4 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
5 | CAL_ID | Character(18) | VARCHAR2(18) NOT NULL | calendar id |
6 | RSLT_SEG_NUM | Number(4,0) | SMALLINT NOT NULL | Result Segment Number |
7 | PIN_NET_NUM | Number(8,0) | INTEGER NOT NULL | Element number field |
8 | PIN_NET_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Net Pay Value |
9 | 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 |
10 | 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 |
11 | 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 |
12 | 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 |
13 | GPFR_CAL_ID | Character(18) | VARCHAR2(18) NOT NULL | GPFR: Calendar ID |
14 | GPFR_ORIGIN_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Indicates if the result comes from a normal calculation, or a retro
C=Corrective delta E=Earning or deduction F=Forwarded delta M=Retro net to be paid (minus) P=Retro to be paid (plus) |