WM_FANL_SRMR_VW

(SQL View)
 Index Back

Fail Analysis Mtr Read Srch Vw

WM Failure Analysis Meter Reading Search View - level 7 view, used to calculate MMUBF (Mean Meter Units Betw Failure) - ref level 6 view WM_FANL_BSMR_VW

SELECT A.BUSINESS_UNIT , A.WO_ID , A.WO_TASK_ID , A.FAILURE_ID , D.METER_TYPE , D.METER_UOM , D.METER_OPTION , D.READING , D.CUM_READING , D.READING_DATE , C.WO_TYPE , C.SRVC_GRP_ID , C.PRIORITY_CD , C.WO_SOURCE_CD , B.STATUS_CD , B.BUSINESS_UNIT_AM , B.ASSET_ID , B.ASSET_LOCATION , B.AREA_ID , B.PROBLEM_GROUP , B.PROBLEM_CD , B.CAUSE_CD , B.RESOLUTION_CD , B.WARRANTY_NAME , B.VENDOR_ID , B.CLAIM_ID , B.BUSINESS_UNIT_PC , B.PROJECT_ID , B.ACTIVITY_ID , B.CREW_BU , B.CREW_SHOP , B.CREW_ID , B.SUPERVISOR , D.ASSET_DESCR30 , D.ASSET_TYPE , D.ASSET_SUBTYPE , D.MFG_ID , D.MODEL , D.IN_SERVICE_DT , D.FAILURE_DT , D.PREV_FAILURE_DT , D.FAIL_DAYS_BASIS_DT , A.DESCR254 AS FAILURE_DESCR , A.SEVERITY_CD , A.FAILURE_START_DT , A.FAILURE_START_TIME , A.FAILURE_END_DT , A.FAILURE_END_TIME , 1 , 1 , %Round(%DateDiff(D.FAIL_DAYS_BASIS_DT, D.FAILURE_DT), 0) , (D.CUM_READING - D.BASIS_CUM_READING) , A.TOTAL_REPAIR_HRS , (A.REPAIR_COST_LBR + A.REPAIR_COST_MAT + A.REPAIR_COST_PO + A.REPAIR_COST_TLS) , A.REPAIR_COST_LBR , A.REPAIR_COST_MAT , A.REPAIR_COST_PO , A.REPAIR_COST_TLS , B.CURRENCY_CD FROM PS_WM_WO_FAILURE A , PS_WM_WO_TSK B , PS_WM_WO_HDR C , PS_WM_FANL_BSMR_VW D WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.WO_ID = A.WO_ID AND B.WO_TASK_ID = A.WO_TASK_ID AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.WO_ID = B.WO_ID AND D.BUSINESS_UNIT = A.BUSINESS_UNIT AND D.WO_ID = A.WO_ID AND D.WO_TASK_ID = A.WO_TASK_ID AND D.FAILURE_ID = A.FAILURE_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
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 FAILURE_ID Character(10) VARCHAR2(10) NOT NULL Failure ID
5 METER_TYPE Character(30) VARCHAR2(30) NOT NULL Meter Type
6 METER_UOM Character(3) VARCHAR2(3) NOT NULL Meter Unit of Measure

Prompt Table: UNITS_TBL

7 METER_OPTION Character(1) VARCHAR2(1) NOT NULL Meter Option
D=Decreasing
I=Increasing
R=Tolerance Range
8 READING Signed Number(15,6) DECIMAL(13,6) NOT NULL Reading
9 CUM_READING Signed Number(21,4) DECIMAL(19,4) NOT NULL Cumulative Reading
10 READING_DATE Date(10) DATE Reading Date
11 WO_TYPE Character(5) VARCHAR2(5) NOT NULL Work Order Type

Prompt Table: WM_WO_TYPE

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

Prompt Table: WM_SRVC_GRP_BU

13 PRIORITY_CD Character(3) VARCHAR2(3) NOT NULL Priority Code

Prompt Table: WM_PRIORITY

14 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
15 STATUS_CD Character(4) VARCHAR2(4) NOT NULL User defined Status Code

Prompt Table: WM_STATUS_TSK

16 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit

Prompt Table: SP_BU_AM_NONVW

17 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification

Prompt Table: WM_ASSET_VWA

18 ASSET_LOCATION Character(10) VARCHAR2(10) NOT NULL Asset Location

Prompt Table: LOCATION_VW

19 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID

Prompt Table: WM_AREA_PMT_VW

20 PROBLEM_GROUP Character(15) VARCHAR2(15) NOT NULL Problem Group

Prompt Table: WM_PCR_GRP_VW

21 PROBLEM_CD Character(15) VARCHAR2(15) NOT NULL Problem code

Prompt Table: WM_PCR_PRBAC_VW

22 CAUSE_CD Character(15) VARCHAR2(15) NOT NULL Cause Code

Prompt Table: WM_PCR_CSEAC_VW

23 RESOLUTION_CD Character(15) VARCHAR2(15) NOT NULL Resolution code

Prompt Table: WM_PCR_RESAC_VW

24 WARRANTY_NAME Character(40) VARCHAR2(40) NOT NULL This field is used to store the name of the warranty.

Prompt Table: WM_ASSET_WRTYVW

25 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: VNDR_FS

26 CLAIM_ID Character(15) VARCHAR2(15) NOT NULL Claim ID
27 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Prompt Table: SP_BU_PC_NONVW

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

Prompt Table: WM_PC_PRJ_VW

29 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

Prompt Table: PROJ_ACT_FS

30 CREW_BU Character(5) VARCHAR2(5) NOT NULL Crew Business Unit

Prompt Table: SP_BU_WM_NONVW

31 CREW_SHOP Character(10) VARCHAR2(10) NOT NULL Crew Shop ID

Prompt Table: WM_CREWSHOP_VW

32 CREW_ID Character(10) VARCHAR2(10) NOT NULL Crew Name

Prompt Table: WM_PREFCREW_VW

33 SUPERVISOR Character(30) VARCHAR2(30) NOT NULL Supervisor

Prompt Table: WM_SHOP_RES_VW2

34 ASSET_DESCR30 Character(30) VARCHAR2(30) NOT NULL Asset Description
35 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
36 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype

Prompt Table: AM_SUBTYPE

37 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID

Prompt Table: MANUFACTURER

38 MODEL Character(30) VARCHAR2(30) NOT NULL Model

Prompt Table: MFG_MODEL

39 IN_SERVICE_DT Date(10) DATE In Service Date
40 FAILURE_DT Date(10) DATE Failure date
41 PREV_FAILURE_DT Date(10) DATE Previous Failure Date
42 FAIL_DAYS_BASIS_DT Date(10) DATE Days to Failure Basis Date
43 FAILURE_DESCR Character(254) VARCHAR2(254) NOT NULL Failure Description
44 SEVERITY_CD Character(5) VARCHAR2(5) NOT NULL Severity Code

Prompt Table: WM_FAIL_SEVRTY

45 FAILURE_START_DT Date(10) DATE Failure Start Date
46 FAILURE_START_TIME Time(15) TIMESTAMP Failure Start Time
47 FAILURE_END_DT Date(10) DATE Failure End Date
48 FAILURE_END_TIME Time(15) TIMESTAMP Failure End Time
49 WM_FAIL_SUMM_CNT Number(8,0) INTEGER NOT NULL Summary Count
50 WM_FAIL_REC_COUNT Number(8,0) INTEGER NOT NULL Failure Count
51 DAYS_BETW_FAILURE Number(8,0) INTEGER NOT NULL Mean Time Betw Failure (Days)
52 UNITS_BETW_FAILURE Signed Number(23,6) DECIMAL(21,6) NOT NULL Mean Meter Units Betw Failure
53 TOTAL_REPAIR_HRS Number(16,3) DECIMAL(15,3) NOT NULL Mean Time to Repair (Hrs)
54 TOTAL_REPAIR_COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Mean Cost to Repair
55 REPAIR_COST_LBR Signed Number(28,3) DECIMAL(26,3) NOT NULL Labor Repair Cost
56 REPAIR_COST_MAT Signed Number(28,3) DECIMAL(26,3) NOT NULL Material Repair Cost
57 REPAIR_COST_PO Signed Number(28,3) DECIMAL(26,3) NOT NULL Procurement Repair Cost
58 REPAIR_COST_TLS Signed Number(28,3) DECIMAL(26,3) NOT NULL Tools Repair Cost
59 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code