WM_INSP_EWK2_VW

(SQL View)
 Index Back

WO Inspect Eligible Worker Vw2

This view is intended for use as the Employee ID (EMPLID) prompt table for an (instantiated) Inspection Activity with: (a) Craft ID specified; and (b) corresponding parent WO Task having either no Sched Labor record(s) with same Craft ID specified or else having none with Employee ID assigned for Craft.

SELECT A.BUSINESS_UNIT , A.WO_ID , A.WO_TASK_ID , E.CRAFT_ID , D.EMPLID , D.NAME , E.PRIMARY_CRAFT , D.SHOP_ID , D.SHIFT_ID , F.SUPERVISOR_ID , F2.NAME AS SUPERVISOR_NAME FROM PS_WM_TSK_INSPECT A , PS_WM_WO_TSK B , PS_WM_WO_HDR C , PS_WM_SHOP_RES_VW D , PS_RS_RES_CRAFT E , PS_WM_ELGBL_WRKR F LEFT OUTER JOIN PS_WM_ELGBL_WRKR F2 ON F2.EMPLID = F.SUPERVISOR_ID WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.WO_ID = A.WO_ID AND B.WO_TASK_ID = A.WO_TASK_ID AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.WO_ID = B.WO_ID AND D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.SHOP_ID = C.SHOP_ID AND D.EFFDT = ( SELECT MAX(D2.EFFDT) FROM PS_WM_SHOP_RES_VW D2 WHERE D2.BUSINESS_UNIT = D.BUSINESS_UNIT AND D2.SHOP_ID = D.SHOP_ID AND D2.EMPLID = D.EMPLID AND D2.EFFDT <= B.SCHED_START_DT AND D2.EFF_STATUS = 'A') AND E.EMPLID = D.EMPLID AND F.EMPLID = E.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 WO_ID Character(10) VARCHAR2(10) NOT NULL Work Order Identification
3 WO_TASK_ID Number(5,0) INTEGER NOT NULL Task Number
4 CRAFT_ID Character(16) VARCHAR2(16) NOT NULL Craft Identification

Prompt Table: RS_CRAFT_DETAIL

5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: WM_ELGBL_WRKR

6 NAME Character(50) VARCHAR2(50) NOT NULL Name
7 PRIMARY_CRAFT Character(1) VARCHAR2(1) NOT NULL SANT--This field is used in the Change profile to identify the Primary Craft

Y/N Table Edit

Default Value: N

8 SHOP_ID Character(10) VARCHAR2(10) NOT NULL Shop Identification
9 SHIFT_ID Character(10) VARCHAR2(10) NOT NULL Shift ID
10 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
11 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name