GPFR_AT_RSLT_VW

(SQL View)
Index Back

AT/ILL: scrollselect view

AT/ILL: scrollselect view. Used as the view to retrieve the employees with an absence to take into consideration for data retrieval.

SELECT E.GB_GROUP_ID , A.EMPLID , A.EMPL_RCD , A.BGN_DT , A.ORIG_BEGIN_DT , B.NAME , A.PIN_TAKE_NUM , A.END_DT , 'N' , %Substring(F.GP_ELN_ATTR5, 1, 2) , OPR.ROWSECCLASS FROM PS_GP_ABS_EVENT A , PS_PERSON_NAME B , PS_GB_GRP_RES_TBL E , PS_GP_ELN_PIN_ATTR F ,PS_SJT_PERSON SEC ,PSOPRDEFN OPR WHERE A.EMPLID = SEC.EMPLID AND A.EMPL_RCD = SEC.EMPL_RCD AND A.EMPLID = E.EMPLID AND A.EMPL_RCD = E.EMPL_RCD AND B.EMPLID = E.EMPLID AND SEC.APPT_TYPE <> '1' AND (EXISTS ( SELECT 'X' FROM PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS SOC WHERE CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = SEC.SCRTY_TYPE_CD AND CLS.SCRTY_KEY1 = SEC.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = SEC.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = SEC.SCRTY_KEY3 AND ((CLS.TREE = 'Y' AND SOC.CLASSID = OPR.ROWSECCLASS AND SOC.SEC_RSC_FLG IN ('1', '3')) OR (CLS.TREE = 'N')) AND SOC.OPRID = OPR.OPRID AND SOC.CLASSID = CLS.CLASSID)) AND E.VERSIONGBQDM = ( SELECT MAX(EE.VERSIONGBQDM) FROM PS_GB_GRP_RES_TBL EE WHERE EE.GB_GROUP_ID = E.GB_GROUP_ID ) AND E.JOB_EFFDT = ( SELECT MAX(EE.JOB_EFFDT) FROM PS_GB_GRP_RES_TBL EE WHERE EE.GB_GROUP_ID = E.GB_GROUP_ID AND EE.VERSIONGBQDM = E.VERSIONGBQDM AND EE.EMPLID = E.EMPLID AND EE.EMPL_RCD= E.EMPL_RCD AND EE.JOB_EFFDT <= %CurrentDateIn) AND A.PIN_TAKE_NUM = F.PIN_NUM AND A.VOIDED_IND ='N' GROUP BY E.GB_GROUP_ID, A.EMPLID, A.EMPL_RCD, A.BGN_DT, A.ORIG_BEGIN_DT, B.NAME, A.PIN_TAKE_NUM, A.END_DT, %Substring(F.GP_ELN_ATTR5, 1, 2) , OPR.ROWSECCLASS

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
4 BGN_DT Date(10) DATE Begin Date
5 ORIG_BEGIN_DT Date(10) DATE original begin date
6 NAME Character(50) VARCHAR2(50) NOT NULL Name
7 PIN_NUM Number(8,0) INTEGER NOT NULL PIN Number
8 END_DT Date(10) DATE end date
9 GPFR_AT_ID_PRINTED Character(1) VARCHAR2(1) NOT NULL GPFR: Printing Already Requested.

Y/N Table Edit

Default Value: N

10 GPFR_ABS_FAMILY Character(2) VARCHAR2(2) NOT NULL Stores the values of the type of absence related to the illness, work accident and maternity certificates
IL=Illness
LT=Long Term Illness
MT=Maternity
OD=Occupational Disease
PT=Paternity
WA=Work Accident
11 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security