WM_PM_FLTR_VW

(SQL View)
 Index Back

WM WorkCenter


SELECT DISTINCT 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 , A.SHOP_ID , B.WO_ID , B.PRIORITY_CD , B.WO_TYPE , B.STATUS_CD , B.SRVC_GRP_ID , %DateDiff ( A.NEXT_DUE_DT, %CURRENTDATEIN) , D.SCHEDULER_CODE , G.ASSET_TYPE , G.ASSET_SUBTYPE , G.ASSET_CRITICALITY , H.LOCATION , H.AREA_ID , G.MANUFACTURER , G.MODEL , G.PROD_VERSION FROM PS_WM_PM A , PS_WM_WO_HDR B , PS_WM_SHOP_SCHED_H E , PS_WM_SHOP_SCHEDLR D , PS_ASSET G , PS_ASSET_LOCATION H WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND ((A.WO_ID = B.WO_ID AND A.WO_ID <> ' ') OR (A.WO_ID = ' ') ) AND B.INTERNAL_STATUS NOT IN ('05', '06', '07') AND E.BUSINESS_UNIT = D.BUSINESS_UNIT AND E.SHOP_ID = D.SHOP_ID AND E.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_WM_SHOP_SCHED_H C WHERE C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.SHOP_ID = D.SHOP_ID AND C.EFFDT <= A.NEXT_DUE_DT ) AND E.EFF_STATUS = 'A' AND D.BUSINESS_UNIT = B.BUSINESS_UNIT AND D.SHOP_ID = B.SHOP_ID AND D.EFFDT = E.EFFDT AND D.PRIMARY_FLG = 'Y' 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 WO_ID Character(10) VARCHAR2(10) NOT NULL Work Order Identification
14 PRIORITY_CD Character(3) VARCHAR2(3) NOT NULL Priority Code

Prompt Table: WM_PRIORITY

15 WO_TYPE Character(5) VARCHAR2(5) NOT NULL Work Order Type
16 STATUS_CD Character(4) VARCHAR2(4) NOT NULL User defined Status Code

Prompt Table: WM_STATUS_HDR

17 SRVC_GRP_ID Character(10) VARCHAR2(10) NOT NULL Service Group

Default Value: OPR_DEF_TBL_WM.SRVC_GRP_ID

Prompt Table: WM_SRVC_GRP_BU

18 WM_DAYS_UPCOMING Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Due Date
19 SCHEDULER_CODE Character(10) VARCHAR2(10) NOT NULL Scheduler Code
20 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
21 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype

Prompt Table: AM_SUBTYPE
Set Control Field: ASSET_TYPE

22 ASSET_CRITICALITY Character(2) VARCHAR2(2) NOT NULL Asset Criticality
23 ASSET_LOCATION Character(10) VARCHAR2(10) NOT NULL Asset Location
24 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID
25 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
26 MODEL Character(30) VARCHAR2(30) NOT NULL Model
27 PROD_VERSION Character(10) VARCHAR2(10) NOT NULL Product Version