GPGB_NI_CHG_VW

(SQL View)
 Index Back

VIEW TO HOLD CHANGE IN NI CATS

GP UK PAYSLIP CHANGES 2016- BUG 20462321

SELECT A.EMPLID ,A.EMPL_RCD ,A.CAL_RUN_ID ,A.GP_PAYGROUP ,A.CAL_ID ,A.RSLT_SEG_NUM ,A.GPGB_NI_CATEGORY ,A.GPGB_TAX_PERIOD ,A.GPGB_TAX_BGN_YR ,B.CAL_RUN_ID ,B.GP_PAYGROUP ,B.CAL_ID ,B.RSLT_SEG_NUM ,B.GPGB_NI_CATEGORY ,B.GPGB_TAX_PERIOD ,B.GPGB_TAX_BGN_YR FROM PS_GPGB_EE_RSLT A ,PS_GPGB_EE_RSLT B WHERE A.GPGB_TAX_PERIOD > 0 AND A.EMPLID=B.EMPLID AND A.EMPL_RCD=B.EMPL_RCD AND B.GPGB_TAX_PERIOD >0 AND B.GPGB_TAX_PERIOD = ( SELECT MAX(B_ED.GPGB_TAX_PERIOD) FROM PS_GPGB_EE_RSLT B_ED WHERE B_ED.EMPLID=B.EMPLID AND B_ED.GPGB_TAX_BGN_YR=B.GPGB_TAX_BGN_YR AND B_ED.GPGB_TAX_PERIOD < A.GPGB_TAX_PERIOD ) AND A.GPGB_TAX_BGN_YR=B.GPGB_TAX_BGN_YR AND NOT A.GPGB_NI_CATEGORY = B.GPGB_NI_CATEGORY AND A.CAL_RUN_ID=A.ORIG_CAL_RUN_ID AND B.CAL_RUN_ID=B.ORIG_CAL_RUN_ID AND EXISTS ( SELECT 'X' FROM PS_GP_CAL_RUN_DTL C WHERE A.GP_PAYGROUP = C.GP_PAYGROUP AND A.CAL_RUN_ID = C.CAL_RUN_ID AND A.CAL_ID = C.CAL_ID AND C.CALC_TYPE = 'P') AND EXISTS ( SELECT 'X' FROM PS_GP_CAL_RUN_DTL C WHERE B.GP_PAYGROUP = C.GP_PAYGROUP AND B.CAL_RUN_ID = C.CAL_RUN_ID AND B.CAL_ID = C.CAL_ID AND C.CALC_TYPE = 'P')

# 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 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number
7 GPGB_NI_CATEGORY Character(2) VARCHAR2(2) NOT NULL NI Category Code
8 GPGB_TAX_PERIOD Number(3,0) SMALLINT NOT NULL Tax Period
9 GPGB_TAX_BGN_YR Number(4,0) SMALLINT NOT NULL Tax Year Begins In This Calendar Year
10 GP_PREV_CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Previous Calendar Group ID
11 GPGB_RC_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
12 GP_PREV_CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
13 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
14 GPGB_NI_CAT_OLD Character(2) VARCHAR2(2) NOT NULL NI Category Code
A=Standard Not C/Out
A1=IOM - CATEGORY A
AA=Standard Not C/Out in APP
AA=Standard Not C/Out in APP
B=Reduced Not C/Out
BA=Reduced Not C/Out in APP
BA=Reduced Not C/Out in APP
C=Retirement Age Exemption
CA=Co Deferrment in APPS
CO=C/Out Deferment in COSR Scheme
D=Standard C/Out Salary Related
D1=IOM - CATEGORY D
E=Reduced C/Out Salary Related
F=Standard C/Out Money Purchase
F=Standard C/Out Money Purchase
G=Reduced C/Out Money Purchase
G=Reduced C/Out Money Purchase
J=Deferment Not C/Out
L=Deferment C/Out Salary Related
S=Deferment C/Out Money Purchase
S=Deferment C/Out Money Purchase
X=Other Exemption
15 MONTH_FACTOR Number(3,0) SMALLINT NOT NULL Period length in Months
16 YEAR Character(4) VARCHAR2(4) NOT NULL Character field to represent a year