GPFR_RSLT_DR_VW (SQL View)	Index Back
Result view: Daily Dat Recalc Result Table for absence daily datas. View for the recalculation

GPFR_RSLT_DR_VW

(SQL View)

Result view: Daily Dat Recalc

Result Table for absence daily datas. View for the recalculation

SELECT 'RETRO' , G.EMPLID , G.EMPL_RCD , G.CAL_RUN_ID , G.GP_PAYGROUP , G.CAL_ID , G.ORIG_CAL_RUN_ID , G.RSLT_SEG_NUM , G.PYMT_KEY1 , G.PYMT_KEY2 , G.PYMT_KEY3 , G.PYMT_KEY4 , G.CALC_ACTION , G.PRD_TYPE , G.CURRENCY_CD , C.CURRENCY_CD , B.CAL_RUN_ID , B.CAL_ID , B.RSLT_SEG_NUM , A.RSLT_VER_NUM , A.RSLT_REV_NUM , B.ABS_BGN_DT , B.PIN_TAKE_NUM , B.DAY_COUNT , B.DAY_COUNT_PAID , B.BEGIN_ENTL_BAL , B.END_ENTL_BAL , B.ABSENCE_DATE , B.SCHED_HRS , B.PARTIAL_HRS , B.ORIG_BEGIN_DT FROM PS_GP_PYE_SEG_STAT A , PS_GP_PYE_PRC_STAT C , PS_GP_RSLT_ABS B , PS_GPFR_GUIDE_VW G WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.CAL_RUN_ID = B.CAL_RUN_ID AND A.GP_PAYGROUP = B.GP_PAYGROUP AND A.CAL_ID = B.CAL_ID AND A.ORIG_CAL_RUN_ID = B.ORIG_CAL_RUN_ID AND A.RSLT_SEG_NUM= B.RSLT_SEG_NUM AND A.EMPLID = C.EMPLID AND A.EMPL_RCD = C.EMPL_RCD AND A.GP_PAYGROUP = C.GP_PAYGROUP AND A.CAL_ID = C.CAL_ID AND A.CAL_RUN_ID = C.CAL_RUN_ID AND A.ORIG_CAL_RUN_ID = C.ORIG_CAL_RUN_ID AND A.CALC_ACTION <> 'V' AND C.PRD_TYPE = 'R' AND C.CALC_TYPE = 'A' AND C.EMPLID = G.EMPLID AND C.CAL_RUN_ID = G.CAL_RUN_ID AND C.EMPL_RCD = G.EMPL_RCD AND C.GP_PAYGROUP = G.GP_PAYGROUP AND A.PYMT_KEY1 = G.PYMT_KEY1 AND A.PYMT_KEY2 = G.PYMT_KEY2 AND A.PYMT_KEY3 = G.PYMT_KEY3 AND A.PYMT_KEY4 = G.PYMT_KEY4 AND G.RSLT_SEG_NUM= ( SELECT MIN(G2.RSLT_SEG_NUM) FROM PS_GPFR_GUIDE_VW G2 WHERE G2.EMPLID = G.EMPLID AND G2.CAL_RUN_ID = G.CAL_RUN_ID AND G2.EMPL_RCD = G.EMPL_RCD AND G2.GP_PAYGROUP = G.GP_PAYGROUP AND G2.CAL_ID = G.CAL_ID AND G2.ORIG_CAL_RUN_ID = G.ORIG_CAL_RUN_ID AND G2.PYMT_KEY1 = G.PYMT_KEY1 AND G2.PYMT_KEY2 = G.PYMT_KEY2 AND G2.PYMT_KEY3 = G.PYMT_KEY3 AND G2.PYMT_KEY4 = G.PYMT_KEY4 )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 DESCR15 Character(15) VARCHAR2(15) NOT NULL Short Description

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

4 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id

5 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group

6 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id

7 ORIG_CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Original Calendar Group ID

8 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number

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 CALC_ACTION Character(1) VARCHAR2(1) NOT NULL Calculate Action
C=Calculate
R=Calculate and Replace
V=Reverse
W=Reverse and Replace

14 PRD_TYPE Character(1) VARCHAR2(1) NOT NULL Period Type
C=Current
R=Retro

15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

16 CURRENCY_CD2 Character(3) VARCHAR2(3) NOT NULL Currency Code

17 SRC_CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Source Calendar Run ID

18 SRC_CAL_ID Character(18) VARCHAR2(18) NOT NULL Source Calendar ID

19 RSLT_SEG_NUM2 Number(4,0) SMALLINT NOT NULL Copy of field RSLT_SEG_NUM used when multiple segment (payment) numbers are needed joined into a single view

20 RSLT_VER_NUM Number(4,0) SMALLINT NOT NULL Result Version Number

21 RSLT_REV_NUM Number(4,0) SMALLINT NOT NULL Result Revision Number

22 ABS_BGN_DT Date(10) DATE Absence Begin Date

23 PIN_TAKE_NUM Number(8,0) INTEGER NOT NULL Absence Take
Prompt Table: GP_PIN

24 DAY_COUNT Signed Number(20,6) DECIMAL(18,6) NOT NULL Day Count

25 DAY_COUNT_PAID Signed Number(20,6) DECIMAL(18,6) NOT NULL Day Count Paid

26 BEGIN_ENTL_BAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Begin Entitlement Balance

27 END_ENTL_BAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Entitlement Balance

28 ABSENCE_DATE Date(10) DATE Absence Date

29 SCHED_HRS Signed Number(6,2) DECIMAL(4,2) NOT NULL Scheduled Work Hours

30 PARTIAL_HRS Signed Number(10,5) DECIMAL(8,5) NOT NULL Partial Hours

31 ORIG_BEGIN_DT Date(10) DATE original begin date

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	DESCR15	Character(15)	VARCHAR2(15) NOT NULL	Short Description
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
4	CAL_RUN_ID	Character(18)	VARCHAR2(18) NOT NULL	Calendar Run Id
5	GP_PAYGROUP	Character(10)	VARCHAR2(10) NOT NULL	Global Payroll pay group
6	CAL_ID	Character(18)	VARCHAR2(18) NOT NULL	calendar id
7	ORIG_CAL_RUN_ID	Character(18)	VARCHAR2(18) NOT NULL	Original Calendar Group ID
8	RSLT_SEG_NUM	Number(4,0)	SMALLINT NOT NULL	Result Segment Number
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	CALC_ACTION	Character(1)	VARCHAR2(1) NOT NULL	Calculate Action C=Calculate R=Calculate and Replace V=Reverse W=Reverse and Replace
14	PRD_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Period Type C=Current R=Retro
15	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
16	CURRENCY_CD2	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
17	SRC_CAL_RUN_ID	Character(18)	VARCHAR2(18) NOT NULL	Source Calendar Run ID
18	SRC_CAL_ID	Character(18)	VARCHAR2(18) NOT NULL	Source Calendar ID
19	RSLT_SEG_NUM2	Number(4,0)	SMALLINT NOT NULL	Copy of field RSLT_SEG_NUM used when multiple segment (payment) numbers are needed joined into a single view
20	RSLT_VER_NUM	Number(4,0)	SMALLINT NOT NULL	Result Version Number
21	RSLT_REV_NUM	Number(4,0)	SMALLINT NOT NULL	Result Revision Number
22	ABS_BGN_DT	Date(10)	DATE	Absence Begin Date
23	PIN_TAKE_NUM	Number(8,0)	INTEGER NOT NULL	Absence Take Prompt Table: GP_PIN
24	DAY_COUNT	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Day Count
25	DAY_COUNT_PAID	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Day Count Paid
26	BEGIN_ENTL_BAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Begin Entitlement Balance
27	END_ENTL_BAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Entitlement Balance
28	ABSENCE_DATE	Date(10)	DATE	Absence Date
29	SCHED_HRS	Signed Number(6,2)	DECIMAL(4,2) NOT NULL	Scheduled Work Hours
30	PARTIAL_HRS	Signed Number(10,5)	DECIMAL(8,5) NOT NULL	Partial Hours
31	ORIG_BEGIN_DT	Date(10)	DATE	original begin date