GPCH_GL_E1ER_VW

(SQL View)
 Index Back

View employees with GL error

Employees with GL error in selected month

SELECT G.CAL_RUN_ID , G.EMPLID , N.NAME , G.CAL_ID , %Substring(G.PIN_CHART1_VAL,1,10) , M.HPYP_CC_ID , SUM(G.CALC_RSLT_VAL) FROM PS_GP_GL_DATA G , PS_NAMES N , PS_GL_ACCOUNT_TBL A , PS_BUS_UNIT_TBL_HR BU , PS_GP_CC_MAP M , PS_GP_CALENDAR C WHERE G.EMPLID = N.EMPLID AND N.NAME_TYPE = 'PRI' AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N.EMPLID = N1.EMPLID AND N.NAME_TYPE = N1.NAME_TYPE ) AND G.ACCOUNT = A.ACCOUNT AND G.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.DEFAULT_SETID = A.SETID AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_GL_ACCOUNT_TBL A1 WHERE A.SETID = A1.SETID AND A.ACCOUNT = A1.ACCOUNT AND A1.EFFDT <= G.POSTING_DATE ) AND G.GP_PAYGROUP = C.GP_PAYGROUP AND G.CAL_ID = C.CAL_ID AND G.PIN_CHART1_VAL = M.PIN_CHART2_VAL AND G.PAY_ENTITY = M.PAY_ENTITY AND G.BUSINESS_UNIT = M.BUSINESS_UNIT AND M.EFFDT = ( SELECT MAX(M1.EFFDT) FROM PS_GP_CC_MAP M1 WHERE M.PAY_ENTITY = M1.PAY_ENTITY AND M.BUSINESS_UNIT = M1.BUSINESS_UNIT AND M.HPYP_CNTRL_AREA = M1.HPYP_CNTRL_AREA AND M.PIN_CHART1_VAL = M1.PIN_CHART1_VAL AND M.PIN_CHART2_VAL = M1.PIN_CHART2_VAL AND M1.EFFDT <= C.CALC_THRU_DT ) GROUP BY G.CAL_RUN_ID, G.EMPLID,N.NAME, G.CAL_ID, G.PIN_CHART1_VAL, M.HPYP_CC_ID HAVING SUM(G.CALC_RSLT_VAL) <> 0.00 UNION SELECT DISTINCT G.CAL_RUN_ID , G.EMPLID , N.NAME , G.CAL_ID , %Substring(G.PIN_CHART1_VAL,1,10) , 'E R R O R' , 0.00 FROM PS_GP_GL_DATA G , PS_NAMES N , PS_GL_ACCOUNT_TBL A , PS_BUS_UNIT_TBL_HR BU , PS_GP_CALENDAR C WHERE G.EMPLID = N.EMPLID AND N.NAME_TYPE = 'PRI' AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N.EMPLID = N1.EMPLID AND N.NAME_TYPE = N1.NAME_TYPE ) AND G.ACCOUNT = A.ACCOUNT AND G.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.DEFAULT_SETID = A.SETID AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_GL_ACCOUNT_TBL A1 WHERE A.SETID = A1.SETID AND A.ACCOUNT = A1.ACCOUNT AND A1.EFFDT <= G.POSTING_DATE ) AND G.GP_PAYGROUP = C.GP_PAYGROUP AND G.CAL_ID = C.CAL_ID AND NOT EXISTS ( SELECT 'x' FROM PS_GP_CC_MAP M WHERE G.PIN_CHART1_VAL = M.PIN_CHART2_VAL AND G.PAY_ENTITY = M.PAY_ENTITY AND G.BUSINESS_UNIT = M.BUSINESS_UNIT AND M.EFFDT = ( SELECT MAX(M1.EFFDT) FROM PS_GP_CC_MAP M1 WHERE M.PAY_ENTITY = M1.PAY_ENTITY AND M.BUSINESS_UNIT = M1.BUSINESS_UNIT AND M.HPYP_CNTRL_AREA = M1.HPYP_CNTRL_AREA AND M.PIN_CHART1_VAL = M1.PIN_CHART1_VAL AND M.PIN_CHART2_VAL = M1.PIN_CHART2_VAL AND M1.EFFDT <= C.CALC_THRU_DT ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
5 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
6 HPYP_CC_ID Character(12) VARCHAR2(12) NOT NULL Cost Center ID.
7 GPCH_RP_AMOUNT1 Signed Number(20,6) DECIMAL(18,6) NOT NULL Rate Result Value