RS_WM_CURJOB_VW

(SQL View)
 Index Back

Worker Search

This view is used to show any worker that has been ever activated in Resource Management. PRAD--Modified SQL to ensure only Work order Mgmt resources are shown by view.

SELECT A.EMPLID ,C.SYSTEM_SOURCE , J.BUSINESS_UNIT , J.LOCATION , J.JOBCODE , J.DEPTID , J.STD_HOURS , J.STD_HRS_FREQUENCY , J.SUPERVISOR_ID FROM %Table(RS_WORKER_TBL) A , %Table(RS_WRKR_EFFDT) C , %Table(JOB) J WHERE A.EMPLID = C.EMPLID AND C.EFFDT = ( SELECT MAX(M.EFFDT) FROM %Table(RS_WRKR_EFFDT) M WHERE M.EMPLID = A.EMPLID AND M.SYSTEM_SOURCE = C.SYSTEM_SOURCE AND M.EFFDT <= %CurrentDateIn) AND C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(H.EFFDT) FROM %Table(JOB) H WHERE J.EMPLID = H.EMPLID AND J.EMPL_RCD = H.EMPL_RCD AND H.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(I.EFFSEQ) FROM %Table(JOB) I WHERE J.EMPLID = I.EMPLID AND J.EMPL_RCD = I.EMPL_RCD AND J.EFFDT = I.EFFDT )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 SYSTEM_SOURCE Character(3) VARCHAR2(3) NOT NULL Identifies the application or source system that generated a journal entry. Release 8.80 - 11/14/2002 - RVlasic - Removed GDM (JrnlGen - Deduction Management) from Translate Values. SUJ---Included a label as Product. Release 8.9 - 05/11/2005 - RVlasic - Added PRV (Variance Pricing) and PKK (KK Budget Journal) and deleted GLK for Project Costing per Brian Cohen.
3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
4 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
5 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
7 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
8 STD_HRS_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Standard Work Period
9 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID