GPFR_DA_SGDT_VW

(SQL View)
 Index Back

Extracted Data Struct Defn

DADS related subrecord: create in order to define the extracted data structures.

SELECT B.GPFR_DA_SNDREF , B.GPFR_DA_COMPANY , B.EMPLID , A.EMPL_RCD , A.SEG_BGN_DT FROM PS_GP_PYE_SEG_STAT A , PS_GPFR_DA_DAT B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.RSLT_VER_NUM = ( SELECT MAX(R.RSLT_VER_NUM) FROM PS_GP_PYE_SEG_STAT R WHERE A.EMPLID = R.EMPLID AND A.CAL_RUN_ID = R.CAL_RUN_ID AND A.EMPL_RCD = R.EMPL_RCD AND A.GP_PAYGROUP = R.GP_PAYGROUP AND A.CAL_ID = R.CAL_ID AND A.ORIG_CAL_RUN_ID = R.ORIG_CAL_RUN_ID) AND A.RSLT_REV_NUM = ( SELECT MAX(S.RSLT_REV_NUM) FROM PS_GP_PYE_SEG_STAT S WHERE A.EMPLID = S.EMPLID AND A.CAL_RUN_ID = S.CAL_RUN_ID AND A.EMPL_RCD = S.EMPL_RCD AND A.GP_PAYGROUP = S.GP_PAYGROUP AND A.CAL_ID = S.CAL_ID AND A.ORIG_CAL_RUN_ID = S.ORIG_CAL_RUN_ID) AND EXISTS ( SELECT 'X' FROM PS_GP_CAL_RUN_DTL T WHERE T.CALC_TYPE = 'P' AND A.CAL_ID = T.CAL_ID AND A.GP_PAYGROUP = T.GP_PAYGROUP AND A.CAL_RUN_ID = T.CAL_RUN_ID AND PRD_END_DT <= ( SELECT MAX(T.GPFR_DA_ENDDT) FROM PS_GPFR_DA_DAT T WHERE T.EMPLID = A.EMPLID AND T.EMPL_RCD = A.EMPL_RCD) AND PRD_BGN_DT >= ( SELECT MIN(T.GPFR_DA_BGNDT) FROM PS_GPFR_DA_DAT T WHERE T.EMPLID = A.EMPLID AND T.EMPL_RCD = A.EMPL_RCD)) AND B.GPFR_DA_BGNDT IS NULL AND GPFR_DA_ENDDT IS NULL AND NOT EXISTS ( SELECT 'X' FROM PS_GPFR_DA_DAT C WHERE C.GPFR_DA_STATUS = 'I' AND A.SEG_BGN_DT BETWEEN C.GPFR_DA_BGNDT AND C.GPFR_DA_ENDDT AND A.EMPLID = C.EMPLID AND A.EMPL_RCD = C.EMPL_RCD )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GPFR_DA_SNDREF Character(10) VARCHAR2(10) NOT NULL DADS related field
2 GPFR_DA_COMPANY Character(3) VARCHAR2(3) NOT NULL DADS related field
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
5 GPFR_DA_BGNDT Date(10) DATE DADS related field