WM_WC_OVERPM_VW

(SQL View)
Index Back

WM Workcenter Overdur PMs

WM Workcenter Overdur PMs

SELECT A.BUSINESS_UNIT , A.PM_SCHD_ID , A.BUSINESS_UNIT_AM , A.ASSET_ID , G.TAG_NUMBER , G.SERIAL_ID , G.VIN , A.MAINT_TYPE , A.NEXT_DUE_DT , A.MAINT_LOOP_ID , A.LOCATION , B.SHOP_ID , A.SETID , F.DESCR100 , C.DESCR254 , A.PERCENT_DUE , A.WO_ID , B.STATUS_CD , %DateDiff (C.REQ_START_DT, %CURRENTDATEIN) , %DateDiff (C.REQ_END_DT, %CURRENTDATEIN) , %DateDiff ( A.NEXT_DUE_DT, %CURRENTDATEIN) , C.REQ_START_DT , C.REQ_END_DT , C.WO_TASK_ID , B.WO_TYPE , B.SRVC_GRP_ID , B.PRIORITY_CD , B.SCHEDULER_CODE , %DateDiff (C.SCHED_START_DT, %CURRENTDATEIN) , %DateDiff (C.SCHED_END_DT, %CURRENTDATEIN) , C.SCHED_START_DT , C.SCHED_END_DT , G.ASSET_TYPE , G.ASSET_SUBTYPE , G.ASSET_CRITICALITY , H.LOCATION , H.AREA_ID , G.MANUFACTURER , G.MODEL , G.PROD_VERSION , %DateDiff (C.ACTL_START_DT, %CURRENTDATEIN) , %DateDiff (C.ACTL_END_DT, %CURRENTDATEIN) , C.ACTL_START_DT , C.ACTL_END_DT FROM PS_WM_PM A , PS_WM_WO_HDR B , PS_WM_WO_TSK C , PS_WM_PM_SCHD F , PS_ASSET G , PS_ASSET_LOCATION H WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WO_ID = B.WO_ID AND B.INTERNAL_STATUS NOT IN ('05', '06', '07') AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.WO_ID = C.WO_ID AND A.BUSINESS_UNIT = F.BUSINESS_UNIT AND A.PM_SCHD_ID = F.PM_SCHD_ID AND B.BUSINESS_UNIT_AM = G.BUSINESS_UNIT AND B.ASSET_ID = G.ASSET_ID AND G.BUSINESS_UNIT = H.BUSINESS_UNIT AND G.ASSET_ID = H.ASSET_ID AND H.EFFDT = ( SELECT MAX(I.EFFDT) FROM PS_ASSET_LOCATION I WHERE I.BUSINESS_UNIT = H.BUSINESS_UNIT AND I.ASSET_ID = H.ASSET_ID AND I.EFFDT <= A.NEXT_DUE_DT ) AND H.EFFSEQ = ( SELECT MIN(J.EFFSEQ) FROM PS_ASSET_LOCATION J WHERE J.BUSINESS_UNIT = H.BUSINESS_UNIT AND J.ASSET_ID = H.ASSET_ID AND J.EFFDT = H.EFFDT )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_WM_NONVW

2 PM_SCHD_ID Character(10) VARCHAR2(10) NOT NULL Preventive Maintenance Schedule ID
3 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit
4 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
5 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
6 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
7 VIN Character(18) VARCHAR2(18) NOT NULL VIN
8 MAINT_TYPE Character(10) VARCHAR2(10) NOT NULL This field is used for maintenance type
9 NEXT_DUE_DT Date(10) DATE Preventive Maintenance due date
10 MAINT_LOOP_ID Character(15) VARCHAR2(15) NOT NULL Maintenance Loop ID
11 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
12 SHOP_ID Character(10) VARCHAR2(10) NOT NULL Shop Identification
13 SETID Character(5) VARCHAR2(5) NOT NULL SetID
14 DESCR Character(30) VARCHAR2(30) NOT NULL Description
15 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
16 PERCENT_DUE Signed Number(16,0) DECIMAL(15) NOT NULL Preventive Maintenance percent due
17 WO_ID Character(10) VARCHAR2(10) NOT NULL Work Order Identification
18 STATUS_CD Character(4) VARCHAR2(4) NOT NULL User defined Status Code
19 WM_DAYS_OVERDUE Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Required Start
20 WM_DAYS_OVERDUE2 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Required End
21 WM_DAYS_UPCOMING Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Creation Date
22 REQ_START_DT Date(10) DATE Required Start
23 REQ_END_DT Date(10) DATE Required End
24 WO_TASK_ID Number(5,0) INTEGER NOT NULL Task Number
25 WO_TYPE Character(5) VARCHAR2(5) NOT NULL Work Order Type
26 SRVC_GRP_ID Character(10) VARCHAR2(10) NOT NULL Service Group
27 PRIORITY_CD Character(3) VARCHAR2(3) NOT NULL Priority Code
28 SCHEDULER_CODE Character(10) VARCHAR2(10) NOT NULL Scheduler Code
29 WM_DAYS_OVERDUE1 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Schedule Start
30 WM_DAYS_OVERDUE3 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Schedule End
31 SCHED_START_DT Date(10) DATE Scheduled Start Date
32 SCHED_END_DT Date(10) DATE Scheduled End Date
33 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
34 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype
35 ASSET_CRITICALITY Character(2) VARCHAR2(2) NOT NULL Asset Criticality
36 ASSET_LOCATION Character(10) VARCHAR2(10) NOT NULL Asset Location
37 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID
38 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
39 MODEL Character(30) VARCHAR2(30) NOT NULL Model
40 PROD_VERSION Character(10) VARCHAR2(10) NOT NULL Product Version
41 WM_DAYS_OVERDUE4 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Actual Start
42 WM_DAYS_OVERDUE5 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Actual End
43 ACTL_START_DT Date(10) DATE Actual Start Date
44 ACTL_END_DT Date(10) DATE Actual End Date