GP_PA_RSAN_VW

(SQL View)
 Index Back

Payroll analytics AE record

source view used for payroll analytics to fetch data for thresholds.

SELECT A.EMPLID , A.EMPL_RCD , A.CAL_RUN_ID , A.GP_PAYGROUP , A.CAL_ID , A.ORIG_CAL_RUN_ID , A.RSLT_SEG_NUM , A.PIN_NUM , A.SEG_BGN_DT , A.SEG_END_DT , A.PRD_BGN_DT , A.PRD_END_DT , A.GP_RPT_BGN_DT , A.GP_RPT_END_DT , A.RUN_TYPE , A.BUSINESS_UNIT , ( SELECT D.DESCR FROM PS_BUS_UNIT_TBL_HR D WHERE D.BUSINESS_UNIT = A.BUSINESS_UNIT ) , A.PAY_ENTITY , A.COUNTRY , A.COMPANY , ( SELECT E.DESCR FROM PS_COMPANY_TBL E WHERE E.COMPANY = A.COMPANY AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_COMPANY_TBL E1 WHERE E1.COMPANY = E.COMPANY AND E1.EFFDT <= A.SEG_END_DT)) , A.LOCATION , ( SELECT F.DESCR FROM PS_LOCATION_TBL F WHERE F.LOCATION = A.LOCATION AND A.SETID_LOCATION = F.SETID AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_LOCATION_TBL F1 WHERE F1.LOCATION=F.LOCATION AND F1.EFF_STATUS = 'A' AND F.SETID = F1.SETID AND F1.EFFDT <= A.SEG_END_DT)), CONTRACT_TYPE , ESTABID , ( SELECT G.DESCR FROM PS_ESTAB_TBL G WHERE G.ESTABID = A.ESTABID AND G.EFFDT = ( SELECT MAX(G1.EFFDT) FROM PS_ESTAB_TBL G1 WHERE G1.ESTABID=G.ESTABID AND G1.EFF_STATUS = 'A' AND G1.EFFDT <= A.SEG_END_DT)) , A.DEPTID , ( SELECT B.DESCR FROM PS_DEPT_TBL B WHERE B.DEPTID = A.DEPTID AND A.SETID_DEPT =B.SETID AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_DEPT_TBL B1 WHERE B1.DEPTID = B.DEPTID AND B1.EFF_STATUS = 'A' AND B1.SETID = B.SETID AND B1.EFFDT <= A.SEG_END_DT)) , A.FTE , A.JOBCODE , ( SELECT C.DESCR FROM PS_JOBCODE_TBL C WHERE C.JOBCODE = A.JOBCODE AND A.SETID_JOBCODE =C.SETID AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_JOBCODE_TBL C1 WHERE C1.JOBCODE = C.JOBCODE AND C1.EFF_STATUS = 'A' AND C1.SETID = C.SETID AND C1.EFFDT <= A.SEG_END_DT)) , A.GP_INCLUDE_RETRO , A.FIRST_NAME , A.LAST_NAME , A.CALC_VAL , A.CALC_ADJ_VAL , A.PYE_CALC_STAT , A.GP_SEG_COUNT , A.PIN_GROSS_VAL , A.PIN_GROSS_NUM , A.PIN_NET_VAL , A.PIN_NET_NUM , A.FULL_PART_TIME , A.SUPERVISOR_ID , A.EMPL_STATUS , A.GP_PA_HIRE_DT , A.TERMINATION_DT , A.CONTRACT_NUM ,A.PRC_ORD_TS , A.GP_SEG_EXISTS , A.GP_CALC_VAL2 , A.GP_CALC_VAL1 FROM PS_GP_PA_RSLT_ANL A

# 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 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
5 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
6 ORIG_CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Original Calendar Group ID
7 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number
8 PIN_NUM Number(8,0) INTEGER NOT NULL PIN Number
9 SEG_BGN_DT Date(10) DATE Payee Process Segment's Begin Date
10 SEG_END_DT Date(10) DATE Payee Process Segment's End Date
11 PRD_BGN_DT Date(10) DATE Period Begin Date
12 PRD_END_DT Date(10) DATE Pay Period End Date
13 GP_RPT_BGN_DT Date(10) DATE Payment begin date
14 GP_RPT_END_DT Date(10) DATE Payment end date
15 RUN_TYPE Character(10) VARCHAR2(10) NOT NULL Run Type Name
16 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
17 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
18 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity
19 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
20 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
21 GP_PA_COMP_DESCR Character(30) VARCHAR2(30) NOT NULL Used in payroll analytics
22 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
23 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
24 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type
25 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
26 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
27 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
28 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
29 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
30 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
31 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
32 GP_INCLUDE_RETRO Character(2) VARCHAR2(2) NOT NULL used in payroll analytics
N=No
Y=Yes
33 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
34 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
35 CALC_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Value
36 CALC_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Adjustment Value
37 PYE_CALC_STAT Character(2) VARCHAR2(2) NOT NULL Calculation Status: field indicates the processing status of a segment (payment)
00=Identified
02=Calculation Error
03=Calculation Error - Bypassed
04=Calculation Error - By Rule
11=No Segment Created
50=Calculation Successful
55=Frozen For Further Calc
70=Finalized
75=Finalized - With Banking
38 GP_SEG_COUNT Number(3,0) SMALLINT NOT NULL Used in payroll analytics
39 PIN_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
40 PIN_GROSS_NUM Number(8,0) INTEGER NOT NULL Element number field
41 PIN_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Net Pay Value
42 PIN_NET_NUM Number(8,0) INTEGER NOT NULL Element number field
43 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
44 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
45 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
46 GP_PA_HIRE_DT Date(10) DATE used in payroll analytics
47 TERMINATION_DT Date(10) DATE Termination Date
48 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
49 PRC_ORD_TS DateTime(26) TIMESTAMP Process Order Timestamp
50 GP_SEG_EXISTS Character(1) VARCHAR2(1) NOT NULL multiple segments exist
N=No
Y=Yes

Y/N Table Edit

Default Value: N

51 GP_CALC_VAL2 Signed Number(20,6) DECIMAL(18,6) NOT NULL GP Calculation value 2
52 GP_CALC_VAL1 Signed Number(20,6) DECIMAL(18,6) NOT NULL GP Calculation value 1