WM_WO_KIBANA_VW

(SQL View)
 Index Back

WO Insights Analytics View

WO Insights Analytics View

SELECT A.BUSINESS_UNIT , A.WO_ID , TSK.WO_TASK_ID , TSK.WO_ID %Concat '-' %Concat TSK.WO_TASK_ID , TSK.BUSINESS_UNIT %Concat '-' %Concat TSK.WO_ID %Concat '-' %Concat TSK.WO_TASK_ID , A.WO_TYPE , WT.DESCR , A.SRVC_GRP_ID , CASE WHEN A.WO_TYPE = M.WO_TYPE THEN 'Y' ELSE ' ' END , S.SHOP_ID , S.DESCR , P.DESCRSHORT , TSK.DESCR254 , A.MAINT_TYPE , B.DESCR50_MIXED , TSK.BUSINESS_UNIT_AM , TSK.ASSET_ID , D.TAG_NUMBER , D.SERIAL_ID , D.DESCR , TSK.REQ_START_DT , TSK.REQ_END_DT , TSK.SCHED_START_DT , TSK.SCHED_END_DT , TSK.COMPLTED_DT , CASE WHEN TSK.COMPLTED_DT <= TSK.REQ_END_DT THEN '1' WHEN TSK.COMPLTED_DT > TSK.REQ_END_DT THEN '2' ELSE '3' END , D.ASSET_TYPE , D.ASSET_SUBTYPE , T.DESCR , D.MANUFACTURER , D.MODEL , PRB.DESCR50 , G.DESCR , CASE WHEN TSK.LAST_UPDATE_DTTM > A.LAST_UPDATE_DTTM THEN TSK.LAST_UPDATE_DTTM ELSE A.LAST_UPDATE_DTTM END , J.DESCR , K.DESCR , TSK.AREA_ID , J.ADDRESS1 , TSK.ACTL_COST_LBR + TSK.ACTL_COST_MAT + TSK.ACTL_COST_TLS + TSK.ACTL_COST_PO FROM PS_WM_WO_HDR A , ((((((PS_WM_WO_TSK TSK LEFT OUTER JOIN PS_ASSET D ON TSK.BUSINESS_UNIT_AM = D.BUSINESS_UNIT AND D.ASSET_ID = TSK.ASSET_ID ) LEFT OUTER JOIN PS_AM_CRIT_LVL G ON G.ASSET_CRITICALITY = D.ASSET_CRITICALITY ) LEFT OUTER JOIN PS_WM_LOCATION_VW J ON J.BUSINESS_UNIT = TSK.BUSINESS_UNIT_AM AND J.LOCATION = TSK.LOCATION) LEFT OUTER JOIN PS_WM_LOCATION_VW K ON K.BUSINESS_UNIT = TSK.BUSINESS_UNIT_AM AND K.LOCATION = TSK.ASSET_LOCATION) LEFT OUTER JOIN PS_WM_SUBTYPE_VW2 T ON T.BUSINESS_UNIT = TSK.BUSINESS_UNIT_AM AND T.ASSET_TYPE = D.ASSET_TYPE AND T.ASSET_SUBTYPE = D.ASSET_SUBTYPE) LEFT OUTER JOIN PS_WM_PCR_BU_PRB PRB ON PRB.BUSINESS_UNIT = TSK.BUSINESS_UNIT AND PRB.PROBLEM_GROUP = TSK.PROBLEM_GROUP AND PRB.PROBLEM_CD = TSK.PROBLEM_CD) , PS_BUS_UNIT_TBL_WM M , PS_WM_SHOP_DEFN S , PS_WM_PRIORITY P , PS_SET_CNTRL_REC S1 , PS_WM_STATUS_TSK B , PS_SET_CNTRL_REC S2 , PS_WM_WO_TYPE WT , PS_SET_CNTRL_REC S3 WHERE A.BUSINESS_UNIT = M.BUSINESS_UNIT AND A.BUSINESS_UNIT = TSK.BUSINESS_UNIT AND A.WO_ID = TSK.WO_ID AND S.BUSINESS_UNIT = A.BUSINESS_UNIT AND S.SHOP_ID = A.SHOP_ID AND P.PRIORITY_CD = A.PRIORITY_CD AND S1.SETCNTRLVALUE = A.BUSINESS_UNIT AND S1.RECNAME = 'WM_PRIORITY' AND S1.SETID = P.SETID AND B.STATUS_CD = TSK.STATUS_CD AND S2.SETCNTRLVALUE = A.BUSINESS_UNIT AND S2.RECNAME = 'WM_STATUS_TSK' AND S2.SETID = B.SETID AND WT.WO_TYPE = A.WO_TYPE AND S3.SETCNTRLVALUE = A.BUSINESS_UNIT AND S3.RECNAME = 'WM_WO_TYPE' AND S3.SETID = WT.SETID

# 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 WM_WO_TASK_ID Character(30) VARCHAR2(30) NOT NULL Work Order-Task ID
5 WM_BU_WO_TSK_ID Character(30) VARCHAR2(30) NOT NULL Business Unit - Work Order - Task ID concantenated field for Insights unique task count.
6 WO_TYPE Character(5) VARCHAR2(5) NOT NULL Work Order Type

Prompt Table: WM_WO_TYPE

7 WO_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Work Order Type Description
8 SRVC_GRP_ID Character(10) VARCHAR2(10) NOT NULL Service Group
9 WM_PM Character(1) VARCHAR2(1) NOT NULL PM
10 SHOP_ID Character(10) VARCHAR2(10) NOT NULL Shop Identification
11 SHOP_DESCR Character(30) VARCHAR2(30) NOT NULL Shop
12 WM_PRIORITY_DESCR Character(10) VARCHAR2(10) NOT NULL Priority
13 TASK_DESCR Character(254) VARCHAR2(254) NOT NULL Task Description
14 MAINT_TYPE Character(10) VARCHAR2(10) NOT NULL This field is used for maintenance type

Prompt Table: MAINT_TYPE_TBL
Set Control Field: BUSINESS_UNIT

15 STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL Status Description
16 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit

Prompt Table: SP_BUS_AM_NONVW

17 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification

Prompt Table: WM_ASSET_VWA

18 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
19 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
20 ASSET_DESCR30 Character(30) VARCHAR2(30) NOT NULL Asset Description
21 REQ_START_DT Date(10) DATE Required Start
22 REQ_END_DT Date(10) DATE Required End
23 SCHED_START_DT Date(10) DATE Scheduled Start Date
24 SCHED_END_DT Date(10) DATE Scheduled End Date
25 COMPLTED_DT Date(10) DATE Completed Date
26 WM_COMPLT_ON_TIME Character(1) VARCHAR2(1) NOT NULL Completed on Time
1=Completed On Time
2=Completed Late
3=Not Complete
27 ASSET_TYPE Character(3) VARCHAR2(3) NOT NULL Asset Type
010=IT Hardware
020=IT Software
040=Equipment
050=Property
060=Fleet
070=Machinery
080=Furniture
090=Facility
100=Intangible
200=Asset Retirement Obligations
999=Other
28 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype
29 WM_SUBTYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Asset Subtype
30 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
31 MODEL Character(30) VARCHAR2(30) NOT NULL Model
32 PROBLEM_DESCR Character(50) VARCHAR2(50) NOT NULL Problem Code Description
33 CRITICALITY_DESCR Character(30) VARCHAR2(30) NOT NULL Criticality
34 LAST_UPDATE_DTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
35 WORK_LOC_DESCR Character(30) VARCHAR2(30) NOT NULL Work Location
36 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Asset Location
37 AREA_DESCR Character(30) VARCHAR2(30) NOT NULL Area Description
38 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
39 ACTL_COST_TOT Number(27,3) DECIMAL(26,3) NOT NULL Actual Total Cost