GPCH_EE_MAP_VW

(SQL View)
 Index Back

Department and cost center map

Department and cost center mapping for all emplids.

SELECT J.EMPLID , J.EMPL_RCD , J.EFFDT , J.EMPL_STATUS , N.NAME , J.DEPTID ,J.COMPANY , CC.DESCR , CC.HPYP_CC_ID , CC.DESCR40 FROM PS_JOB J , PS_NAMES N , PS_GPCH_CC_MAP_VW CC WHERE J.EMPLID = N.EMPLID AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N.EMPLID = N1.EMPLID AND N1.EFFDT <= J.EFFDT ) AND J.DEPTID = CC.PIN_CHART2_VAL AND CC.EFFDT = ( SELECT MAX(CC1.EFFDT) FROM PS_GPCH_CC_MAP_VW CC1 WHERE CC.PIN_CHART2_VAL = CC1.PIN_CHART2_VAL AND CC1.EFFDT <= J.EFFDT) UNION SELECT J.EMPLID , J.EMPL_RCD , J.EFFDT , J.EMPL_STATUS , N.NAME , J.DEPTID ,J.COMPANY , '' , '' , '' FROM PS_JOB J , PS_NAMES N WHERE J.EMPLID = N.EMPLID AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N.EMPLID = N1.EMPLID AND N1.EFFDT <= J.EFFDT ) AND NOT EXISTS ( SELECT 'X' FROM PS_GPCH_CC_MAP_VW CC WHERE J.DEPTID = CC.PIN_CHART2_VAL AND CC.EFFDT = ( SELECT MAX(CC1.EFFDT) FROM PS_GPCH_CC_MAP_VW CC1 WHERE CC.PIN_CHART2_VAL = CC1.PIN_CHART2_VAL AND CC1.EFFDT <= J.EFFDT) )

# 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 Rcd Nbr
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
7 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
9 HPYP_CC_ID Character(12) VARCHAR2(12) NOT NULL Cost Center ID.
10 DESCR40 Character(40) VARCHAR2(40) NOT NULL Description