WM_WC_OVERWO_VW

(SQL View)
 Index Back

WM Workcenter

WM Workcenter Overdue WOs

SELECT A.BUSINESS_UNIT , A.WO_ID , B.WO_TASK_ID , A.PRIORITY_CD , B.REQ_START_DT , A.SHOP_ID , B.DESCR254 , 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 , 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 , A.SRVC_GRP_ID , 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) , A.ENTERED_DTTM 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 WO_TASK_ID Number(5,0) INTEGER NOT NULL Task Number
4 PRIORITY_CD Character(3) VARCHAR2(3) NOT NULL Priority Code

Prompt Table: WM_PRIORITY

5 REQ_START_DT Date(10) DATE Required Start
6 SHOP_ID Character(10) VARCHAR2(10) NOT NULL Shop Identification
7 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
8 REQ_END_DT Date(10) DATE Required End
9 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

10 WO_TYPE Character(5) VARCHAR2(5) NOT NULL Work Order Type
11 BUSINESS_UNIT_WR Character(5) VARCHAR2(5) NOT NULL Work Request Business Unit

Prompt Table: SP_BU_WR_NONVW

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

Prompt Table: WM_WR_WO_SRCH

13 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

14 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

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

Prompt Table: WM_PC_PRJ_DVW

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

Prompt Table: WM_STATUS_HDR

18 COMPLTED_DT Date(10) DATE Completed Date
19 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit
20 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
21 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
22 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
23 VIN Character(18) VARCHAR2(18) NOT NULL VIN
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 SRVC_GRP_ID Character(10) VARCHAR2(10) NOT NULL Service Group
34 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
35 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype
36 ASSET_CRITICALITY Character(2) VARCHAR2(2) NOT NULL Asset Criticality
37 ASSET_LOCATION Character(10) VARCHAR2(10) NOT NULL Asset Location
38 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID
39 WM_DAYS_OVERDUE4 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Actual Start
40 WM_DAYS_OVERDUE5 Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Actual End
41 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
42 MODEL Character(30) VARCHAR2(30) NOT NULL Model
43 PROD_VERSION Character(10) VARCHAR2(10) NOT NULL Product Version
44 WM_DAYS_UPCOMING Signed Number(5,0) DECIMAL(4) NOT NULL Days Past Creation Date
45 ENTERED_DTTM DateTime(26) TIMESTAMP Entered Datetime