GP_KB_RS_ANL_VW

(SQL View)
 Index Back

Gross & Net Pay Variance View

Used to store Gross and Net Pay Variance for storing Insights analytics

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.CAL_RUN_ID , B.CAL_RUN_ID , A.GP_PAYGROUP , A.PIN_NUM , A.CAL_ID , B.CAL_ID , A.PRC_ORD_TS , B.PRC_ORD_TS , A.ORIG_CAL_RUN_ID , A.RSLT_SEG_NUM , A.EMPLID %Concat %Cast(A.EMPL_RCD,NUMBER,CHARACTER) , A.GP_RPT_RETRO , A.SEG_BGN_DT , A.SEG_END_DT , A.SLICE_BGN_DT , A.SLICE_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 , A.BUSINESS_DESCR , A.PAY_ENTITY , A.COUNTRY , A.COMPANY , A.GP_PA_COMP_DESCR , A.LOCATION , A.LOCATION_DESCR , A.CONTRACT_TYPE , A.ESTABID , A.ESTAB_DESCR , A.DEPTID , A.DEPT_DESCR , A.FTE , A.JOBCODE , A.JOBCODE_DESCR , A.SETID_JOBCODE , A.SETID_DEPT , A.SETID_LOCATION , A.GP_INCLUDE_RETRO , A.FIRST_NAME , A.LAST_NAME , A.PYE_CALC_STAT , A.FREQUENCY_ID , A.GP_SEG_COUNT , A.GROUP_LIST_ID , A.STRM_NUM , A.PIN_GROSS_NUM , A.GP_CALC_VAL1 , A.GP_CALC_VAL1 , B.GP_CALC_VAL1 , CASE WHEN %Round(B.GP_CALC_VAL1, 2) <> 0 THEN %Round(A.GP_CALC_VAL1, 2) - %Round(B.GP_CALC_VAL1, 2) ELSE %Round(A.GP_CALC_VAL1, 2) END , A.PIN_NET_NUM , A.GP_CALC_VAL2 , A.GP_CALC_VAL2 , B.GP_CALC_VAL2 , CASE WHEN %Round(B.GP_CALC_VAL2, 2) <> 0 THEN %Round(A.GP_CALC_VAL2, 2) - %Round(B.GP_CALC_VAL2, 2) ELSE %Round(A.GP_CALC_VAL2, 2) END , A.FULL_PART_TIME , A.SUPERVISOR_ID , A.EMPL_STATUS , A.GP_PA_HIRE_DT , A.TERMINATION_DT , A.CONTRACT_NUM , A.GP_SEG_EXISTS , CASE WHEN B.CAL_RUN_ID IS NULL THEN 'Y' ELSE 'N' END , A.CURRENCY_CD , A.PYMT_DT , A.SETID_EMPL_CLASS , A.EMPL_CLASS FROM PS_GP_KB_RSLT_VW A LEFT OUTER JOIN PS_GP_KB_RSLT_VW B ON A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.GP_PAYGROUP = B.GP_PAYGROUP AND A.RUN_TYPE = B.RUN_TYPE AND A.PYMT_DT > B.PYMT_DT WHERE B.CAL_ID IS NULL OR B.CAL_ID IN ( SELECT C.CAL_ID FROM PS_GP_CALENDAR C , PS_GP_CAL_RUN_DTL D , PS_GP_CAL_RUN E WHERE C.GP_PAYGROUP = B.GP_PAYGROUP AND C.RUN_TYPE = B.RUN_TYPE AND D.GP_PAYGROUP = C.GP_PAYGROUP AND D.CAL_ID = C.CAL_ID AND E.CAL_RUN_ID = D.CAL_RUN_ID AND E.CAL_RUN_ID = B.CAL_RUN_ID AND E.RUN_FINALIZED_IND = 'Y' AND C.PYMT_DT = ( SELECT MAX(C2.PYMT_DT) FROM PS_GP_CALENDAR C2 , PS_GP_CAL_RUN_DTL D2 , PS_GP_CAL_RUN E2 WHERE C2.GP_PAYGROUP = C.GP_PAYGROUP AND C2.RUN_TYPE = C.RUN_TYPE AND D2.GP_PAYGROUP = C2.GP_PAYGROUP AND D2.CAL_ID = C2.CAL_ID AND E2.CAL_RUN_ID = D2.CAL_RUN_ID AND E2.RUN_FINALIZED_IND = 'Y' AND C2.PYMT_DT < A.PYMT_DT))

# 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_PRVS_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 PIN_NUM Number(8,0) INTEGER NOT NULL PIN Number
7 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
8 GP_PRVS_CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
9 CURR_PRC_ORD_TS DateTime(26) TIMESTAMP Process Order Timestamp
10 PRVS_PRC_ORD_TS DateTime(26) TIMESTAMP Process Order Timestamp
11 ORIG_CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Original Calendar Group ID
12 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number
13 GP_EMPL_COUNT Character(14) VARCHAR2(14) NOT NULL Employee count per segment
14 GP_RPT_RETRO Character(1) VARCHAR2(1) NOT NULL Retroactivity Type
1=Original
2=Recalculation
3=Prior Calculation
4=Last Version/Revision
5=Result Delta - Corrective
6=Result Delta - Forwarded
15 SEG_BGN_DT Date(10) DATE Payee Process Segment's Begin Date
16 SEG_END_DT Date(10) DATE Payee Process Segment's End Date
17 SLICE_BGN_DT Date(10) DATE Element Slice Begin Date
18 SLICE_END_DT Date(10) DATE Element Slice End Date
19 PRD_BGN_DT Date(10) DATE Period Begin Date
20 PRD_END_DT Date(10) DATE Pay Period End Date
21 GP_RPT_BGN_DT Date(10) DATE Payment begin date
22 GP_RPT_END_DT Date(10) DATE Payment end date
23 RUN_TYPE Character(10) VARCHAR2(10) NOT NULL Run Type Name
24 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
25 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
26 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity
27 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
28 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
29 GP_PA_COMP_DESCR Character(30) VARCHAR2(30) NOT NULL Used in payroll analytics
30 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
31 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
32 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type
33 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
34 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
35 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
36 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
37 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
38 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
39 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
40 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
41 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
42 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
43 GP_INCLUDE_RETRO Character(2) VARCHAR2(2) NOT NULL used in payroll analytics
N=No
Y=Yes
44 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
45 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
46 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
47 FREQUENCY_ID Character(5) VARCHAR2(5) NOT NULL Defines a frequency and a set of parameters used for frequency conversion. Typically, these frequencies reflect how often an event occurs. Some examples are compensation frequency and pay frequency.
48 GP_SEG_COUNT Number(3,0) SMALLINT NOT NULL Used in payroll analytics
49 GROUP_LIST_ID Character(10) VARCHAR2(10) NOT NULL Group List
50 STRM_NUM Number(4,0) SMALLINT NOT NULL Stream Number
51 PIN_GROSS_NUM Number(8,0) INTEGER NOT NULL Element number field
52 SEG_PIN_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
53 GP_C_PIN_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
54 GP_P_PIN_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
55 GP_DIFF_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
56 PIN_NET_NUM Number(8,0) INTEGER NOT NULL Element number field
57 SEG_PIN_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
58 GP_C_PIN_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Net Pay Value
59 GP_P_PIN_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Net Pay Value
60 GP_DIFF_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
61 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
62 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
63 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
64 GP_PA_HIRE_DT Date(10) DATE used in payroll analytics
65 TERMINATION_DT Date(10) DATE Termination Date
66 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
67 GP_SEG_EXISTS Character(1) VARCHAR2(1) NOT NULL multiple segments exist
N=No
Y=Yes

Default Value: N

68 GP_EXIST_CURR_CAL Character(1) VARCHAR2(1) NOT NULL exists only in current calendar
N=No
Y=Yes

Default Value: N

69 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
70 PYMT_DT Date(10) DATE Payment Date
71 SETID_EMPL_CLASS Character(5) VARCHAR2(5) NOT NULL Employee class Set ID
72 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification