WM_WC_UPCPM_VW

(SQL View)
 Index Back

wm Workcenter Upcoming PMS

wm Workcenter Upcoming Preventive Maintenance

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 , A.SETID , D.DESCR100 , B.DESCR254 , A.PERCENT_DUE , A.WO_ID , B.STATUS_CD , %DateDiff ( A.NEXT_DUE_DT, %CURRENTDATEIN) , B.WO_TYPE , B.SRVC_GRP_ID , B.SCHEDULER_CODE , G.ASSET_TYPE , G.ASSET_SUBTYPE , G.ASSET_CRITICALITY , H.LOCATION , H.AREA_ID , G.MANUFACTURER , G.MODEL , G.PROD_VERSION , B.PRIORITY_CD FROM PS_WM_PM A , PS_WM_WO_HDR B , PS_WM_PM_SCHD D , PS_ASSET G , PS_ASSET_LOCATION H WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WO_ID = B.WO_ID AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.PM_SCHD_ID = D.PM_SCHD_ID AND B.INTERNAL_STATUS NOT IN ('05', '06', '07') 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 ) UNION 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 , A.SETID ,D.DESCR100 , ' ' , A.PERCENT_DUE , A.WO_ID , ' ' , %DateDiff ( A.NEXT_DUE_DT, %CURRENTDATEIN) , ' ' , ' ' , ' ' , 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_PM_SCHD D , PS_ASSET G , PS_ASSET_LOCATION H WHERE A.WO_ID = ' ' AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.PM_SCHD_ID = D.PM_SCHD_ID AND D.BUSINESS_UNIT_AM = G.BUSINESS_UNIT AND D.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_UPCOMING Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Creation Date
20 WO_TYPE Character(5) VARCHAR2(5) NOT NULL Work Order Type
21 SRVC_GRP_ID Character(10) VARCHAR2(10) NOT NULL Service Group
22 SCHEDULER_CODE Character(10) VARCHAR2(10) NOT NULL Scheduler Code
23 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
24 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype
25 ASSET_CRITICALITY Character(2) VARCHAR2(2) NOT NULL Asset Criticality
26 ASSET_LOCATION Character(10) VARCHAR2(10) NOT NULL Asset Location
27 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID
28 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
29 MODEL Character(30) VARCHAR2(30) NOT NULL Model
30 PROD_VERSION Character(10) VARCHAR2(10) NOT NULL Product Version
31 PRIORITY_CD Character(3) VARCHAR2(3) NOT NULL Priority Code