WM_WO_FLTR_VW

(SQL View)
 Index Back

WM WorkCenter

Overdue WOs filter

SELECT A.BUSINESS_UNIT , A.WO_ID , A.PRIORITY_CD , B.REQ_START_DT , A.SHOP_ID , B.REQ_END_DT , A.WO_SOURCE_CD , A.WO_TYPE , A.BUSINESS_UNIT_WR , A.WR_ID , A.WO_APPROVAL_STATUS , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.MAINT_TYPE , B.STATUS_CD , A.COMPLTED_DT , B.BUSINESS_UNIT_AM , B.ASSET_ID , G.TAG_NUMBER , G.SERIAL_ID , G.VIN , A.SRVC_GRP_ID , A.ENTERED_DTTM ,B.SCHED_START_DT , B.SCHED_END_DT , B.ACTL_START_DT , B.ACTL_END_DT , %DateDiff (B.REQ_START_DT, %CURRENTDATEIN) , %DateDiff (B.SCHED_START_DT, %CURRENTDATEIN) , %DateDiff (B.REQ_END_DT, %CURRENTDATEIN) , %DateDiff (B.SCHED_END_DT, %CURRENTDATEIN) , A.SCHEDULER_CODE , G.ASSET_TYPE , G.ASSET_SUBTYPE , G.ASSET_CRITICALITY , H.LOCATION , H.AREA_ID , %DateDiff (B.ACTL_START_DT, %CURRENTDATEIN) , %DateDiff (B.ACTL_END_DT, %CURRENTDATEIN) , G.MANUFACTURER , G.MODEL , G.PROD_VERSION , %Round (%decdiv (%decdiv (%DateTimeDiff (A.ENTERED_DTTM , %CURRENTDATEtimeIN), 60), 24) , 0) FROM PS_WM_WO_HDR A , PS_WM_WO_TSK B , PS_ASSET G , PS_ASSET_LOCATION H WHERE B.INTERNAL_STATUS NOT IN ('05', '06', '07') AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WO_ID = B.WO_ID AND A.BUSINESS_UNIT_AM = G.BUSINESS_UNIT AND A.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.REQ_START_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 WO_ID Character(10) VARCHAR2(10) NOT NULL Work Order Identification
3 PRIORITY_CD Character(3) VARCHAR2(3) NOT NULL Priority Code

Prompt Table: WM_PRIORITY
Set Control Field: BUSINESS_UNIT

4 REQ_START_DT Date(10) DATE Required Start
5 SHOP_ID Character(10) VARCHAR2(10) NOT NULL Shop Identification
6 REQ_END_DT Date(10) DATE Required End
7 WO_SOURCE_CD Character(4) VARCHAR2(4) NOT NULL Work order source code
1=Third Party
2=CRM Help Desk
3=Work Order
4=Service Request
5=Preventive Maintenance
6=Project Costing
7=Express Work Order
8=Condition-based Maintenance
9=WO Inspection

Default Value: 3

8 WO_TYPE Character(5) VARCHAR2(5) NOT NULL Work Order Type
9 BUSINESS_UNIT_WR Character(5) VARCHAR2(5) NOT NULL Work Request Business Unit

Prompt Table: SP_BU_WR_NONVW

10 WR_ID Character(15) VARCHAR2(15) NOT NULL Work Request ID

Prompt Table: WM_WR_WO_SRCH

11 WO_APPROVAL_STATUS Character(1) VARCHAR2(1) NOT NULL work order approval status
A=Approved
C=Cancelled
E=Denied
I=Initial
P=Pending Approval

Default Value: I

12 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Default Value: BUS_UNIT_TBL_WM.BUSINESS_UNIT_PC

Prompt Table: BUS_UNIT_TBL_PC

13 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: WM_PC_PRJ_DVW

14 MAINT_TYPE Character(10) VARCHAR2(10) NOT NULL This field is used for maintenance type
15 STATUS_CD Character(4) VARCHAR2(4) NOT NULL User defined Status Code

Prompt Table: WM_STATUS_HDR
Set Control Field: BUSINESS_UNIT

16 COMPLTED_DT Date(10) DATE Completed Date
17 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit
18 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
19 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
20 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
21 VIN Character(18) VARCHAR2(18) NOT NULL VIN
22 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

23 ENTERED_DTTM DateTime(26) TIMESTAMP Entered Datetime
24 SCHED_START_DT Date(10) DATE Scheduled Start Date
25 SCHED_END_DT Date(10) DATE Scheduled End Date
26 ACTL_START_DT Date(10) DATE Actual Start Date
27 ACTL_END_DT Date(10) DATE Actual End Date
28 WM_DAYS_OVERDUE Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Required Start
29 WM_DAYS_OVERDUE1 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Schedule Start
30 WM_DAYS_OVERDUE2 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Required End
31 WM_DAYS_OVERDUE3 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Schedule End
32 SCHEDULER_CODE Character(10) VARCHAR2(10) NOT NULL Scheduler Code
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 WM_DAYS_OVERDUE4 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Actual Start
39 WM_DAYS_OVERDUE5 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Actual End
40 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
41 MODEL Character(30) VARCHAR2(30) NOT NULL Model
42 PROD_VERSION Character(10) VARCHAR2(10) NOT NULL Product Version
43 WM_DAYS_UPCOMING Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Creation Date