GPGB_CO_REV_VW

(SQL View)
 Index Back

Court Order Review View

This view is used to fetch employee's payroll processed court orders details

SELECT A.EMPLID , A.EMPL_RCD , A.GPGB_CO_ORD_REF , A.GPGB_CO_LEG , A.ACTIVE_FLAG , A.GPGB_CO_ORDER , A.GPGB_CO_HRT , A.START_DATE , A.SEQNUM , A.GPGB_CO_AMT_PAYBLE , B.CALC_RSLT_VAL , CASE WHEN (A.GPGB_CO_ORDER ='DEO' OR A.GPGB_CO_ORDER ='CMA' OR A.GPGB_CO_ORDER='CAO' OR (A.GPGB_CO_ORDER='DEA' AND A.GPGB_CO_NDR <> 0)) THEN 0 ELSE (A.GPGB_CO_AMT_PAYBLE -B.CALC_RSLT_VAL ) END , A.END_DATE FROM PS_GPGB_EE_CO_DTL A ,PS_GP_RSLT_ACUM B ,PS_GP_PIN C ,PS_GP_PYE_PRC_STAT D WHERE A.EMPLID=B.EMPLID AND A.EMPL_RCD=B.EMPL_RCD AND A.GPGB_CO_ORD_REF=B.USER_KEY1 AND A.INSTANCE=( SELECT MAX(A1.INSTANCE) FROM PS_GPGB_EE_CO_DTL A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD AND A1.GPGB_CO_ORD_REF=A.GPGB_CO_ORD_REF) AND B.RSLT_SEG_NUM=( SELECT MAX(B1.RSLT_SEG_NUM) FROM PS_GP_RSLT_ACUM B1 WHERE B1.EMPLID=B.EMPLID AND B1.EMPL_RCD=B.EMPL_RCD AND B1.CAL_RUN_ID=B.CAL_RUN_ID AND B1.GP_PAYGROUP=B.GP_PAYGROUP AND B1.CAL_ID=B.CAL_ID AND B1.ORIG_CAL_RUN_ID=B.ORIG_CAL_RUN_ID) AND B.PIN_NUM=C.PIN_NUM AND C.PIN_CODE='CO AC PAID TD GBR' AND D.EMPLID=B.EMPLID AND D.EMPL_RCD=B.EMPL_RCD AND D.CAL_RUN_ID=B.CAL_RUN_ID AND D.GP_PAYGROUP=B.GP_PAYGROUP AND D.CAL_ID=B.CAL_ID AND D.ORIG_CAL_RUN_ID=B.ORIG_CAL_RUN_ID AND D.CALC_TYPE='P' AND D.PRD_BGN_DT=( SELECT MAX(D1.PRD_BGN_DT) FROM PS_GP_PYE_PRC_STAT D1 WHERE D1.EMPLID=D.EMPLID AND D1.EMPL_RCD=D.EMPL_RCD AND D1.CALC_TYPE='P') AND D.PRC_ORD_TS=( SELECT MAX(D2.PRC_ORD_TS) FROM PS_GP_PYE_PRC_STAT D2 WHERE D2.EMPLID=D.EMPLID AND D2.EMPL_RCD=D.EMPL_RCD AND D2.CALC_TYPE='P' AND D2.PRD_BGN_DT=D.PRD_BGN_DT AND D2.PRD_END_DT=D.PRD_END_DT ) UNION SELECT A.EMPLID , A.EMPL_RCD , A.GPGB_CO_ORD_REF , A.GPGB_CO_LEG , A.ACTIVE_FLAG , A.GPGB_CO_ORDER , A.GPGB_CO_HRT , A.START_DATE , A.SEQNUM , A.GPGB_CO_AMT_PAYBLE , 0 , 0 , A.END_DATE FROM PS_GPGB_EE_CO_DTL A WHERE A.INSTANCE=( SELECT MAX(A1.INSTANCE) FROM PS_GPGB_EE_CO_DTL A1 WHERE A1.EMPLID=A.EMPLID AND A1.EMPL_RCD=A.EMPL_RCD AND A1.GPGB_CO_ORD_REF=A.GPGB_CO_ORD_REF) AND NOT EXISTS ( SELECT 'X' FROM PS_GP_RSLT_ACUM B ,PS_GP_PIN C WHERE A.EMPLID=B.EMPLID AND A.EMPL_RCD=B.EMPL_RCD AND A.GPGB_CO_ORD_REF=B.USER_KEY1 AND B.PIN_NUM=C.PIN_NUM AND C.PIN_CODE='CO AC PAID TD GBR' )

# 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

Prompt Table: GPGB_STARTER_VW

3 GPGB_CO_ORD_REF Character(25) VARCHAR2(25) NOT NULL GPUK Court Order Reference
4 GPGB_CO_LEG Character(2) VARCHAR2(2) NOT NULL Used for UK and NI court orders to define the regulatory region - different rules exist for Scotland, England & Wales and Northern Ireland
EW=England and Wales
NI=Northern Ireland
SC=Scotland
5 ACTIVE_FLAG Character(1) VARCHAR2(1) NOT NULL Active Flag
A=Active
I=Inactive

Default Value: A

6 GPGB_CO_ORDER Character(3) VARCHAR2(3) NOT NULL UK Court Orders

Prompt Table: %RECNAME_EDIT

7 GPGB_CO_HRT Character(1) VARCHAR2(1) NOT NULL High/Low Deduction rate indicator
8 START_DATE Date(10) DATE Start Date for Gen Standing PO
9 SEQNUM Number(3,0) SMALLINT NOT NULL Sequence Number
10 GPGB_CO_AMT_PAYBLE Number(9,2) DECIMAL(8,2) NOT NULL GPUK Court Amount Payable
11 GPGB_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Adjustment
12 GPGB_AMT_BAL Signed Number(20,6) DECIMAL(18,6) NOT NULL GP UK - Balance Amount
13 END_DATE Date(10) DATE End Date