· 7 years ago · Mar 04, 2019, 11:28 AM
1USE [RALSupport]
2GO
3
4/****** Object: StoredProcedure [dbo].[SetupMaintenanceItemErrorChecking] Script Date: 4/03/2019 12:22:14 ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11ALTER PROCEDURE [dbo].[SetupMaintenanceItemErrorChecking] (@SourceDB NVARCHAR(50))
12AS
13
14DECLARE @DestDB NVARCHAR(50) = (SELECT DB_NAME())
15
16EXECUTE ('IF EXISTS (SELECT TOP 1 1 FROM '+@DestDB+'.sys.procedures WHERE name = ''MaintenanceItemErrorChecking'')
17 BEGIN
18 DROP PROCEDURE dbo.MaintenanceItemErrorChecking
19 END')
20
21EXECUTE ('CREATE PROCEDURE dbo.MaintenanceItemErrorChecking
22AS
23
24 IF EXISTS (SELECT TOP 1 1 FROM sys.tables WHERE name = ''MaintenanceItemErrorChecking'')
25 BEGIN
26 DROP TABLE dbo.MaintenanceItemErrorChecking
27 END
28
29 SELECT
30 tMIMSLocation.ID tMIMSLocation_ID
31 , tMIMS.tMSRevision_ID
32 , tModel.ID tModel_ID
33 , tMIMSModel.sPart_ID
34 , tMIMSModelApplicableSerialNo.SerialNoFrom
35 , tMIMSModelApplicableSerialNo.SerialNoTo
36 , tMIMSLifeCode.tLifeCode_ID
37 , tMIMSLifeCode.LifeCodeLife
38 , tMIMSLifeCode.LifeCodeThreshold
39 , tMIMSLifeCode.LifeCodeTolerance
40 , tMIMSLifeCode.Sampling
41 , tMI.OneTime
42 , tMI.FixedCalendarLife
43 , tMI.VariableLife
44 , tMI.NoExtension
45 , tMI.Scrap
46 , ROW_NUMBER() OVER (ORDER BY tMIMSLocation.ID) ID
47 INTO #MaintenanceProgramLogicalConfiguration
48 FROM '+@SourceDB+'.dbo.tModelAttachedModelLocationTree
49 LEFT JOIN '+@SourceDB+'.dbo.tModelAttachedModelLocation ON tModelAttachedModelLocationTree.tModelAttachedModelLocation_ID = tModelAttachedModelLocation.ID
50 JOIN '+@SourceDB+'.dbo.tModel ON tModelAttachedModelLocationTree.tModel_ID = tModel.ID OR tModelAttachedModelLocation.tModel_ID = tModel.ID
51 JOIN '+@SourceDB+'.dbo.tMIMSModel
52 LEFT JOIN '+@SourceDB+'.dbo.tMIMSModelApplicableSerialNo ON tMIMSModelApplicableSerialNo.tMIMSModel_ID = tMIMSModel.ID
53 ON tMIMSModel.tModel_ID = tModel.ID
54 JOIN '+@SourceDB+'.dbo.tMIMS
55 JOIN '+@SourceDB+'.dbo.tMIMSLocation ON tMIMSLocation.tMIMS_ID = tMIMS.ID AND tMIMSLocation.Closed = 0
56 JOIN '+@SourceDB+'.dbo.tMI ON tMIMS.tMI_ID = tMI.ID
57 JOIN '+@SourceDB+'.dbo.tMIMSLifeCode ON tMIMSLifeCode.tMIMS_ID = tMIMS.ID
58 LEFT JOIN '+@SourceDB+'.dbo.tMSRevision ON tMIMS.tMSRevision_ID = tMSRevision.ID AND tMSRevision.RetiredDate = ''1900-01-01''
59 ON tMIMSModel.tMIMS_ID = tMIMS.ID
60 WHERE tMSRevision.ID IS NOT NULL OR tMIMS.tMSRevision_ID = 0
61 GROUP BY
62 tMIMSLocation.ID
63 , tMIMS.tMSRevision_ID
64 , tModel.ID
65 , tMIMSModel.sPart_ID
66 , tMIMSModelApplicableSerialNo.SerialNoFrom
67 , tMIMSModelApplicableSerialNo.SerialNoTo
68 , tMIMSLifeCode.tLifeCode_ID
69 , tMIMSLifeCode.LifeCodeLife
70 , tMIMSLifeCode.LifeCodeThreshold
71 , tMIMSLifeCode.LifeCodeTolerance
72 , tMIMSLifeCode.Sampling
73 , tMI.OneTime
74 , tMI.FixedCalendarLife
75 , tMI.VariableLife
76 , tMI.NoExtension
77 , tMI.Scrap
78
79
80 CREATE CLUSTERED INDEX IX_MPLC_PK ON #MaintenanceProgramLogicalConfiguration (ID)
81 CREATE INDEX IX_MPLC_1 ON #MaintenanceProgramLogicalConfiguration (tModel_ID, sPart_ID, SerialNoFrom, SerialNoTo)
82 CREATE INDEX IX_MPLC_2 ON #MaintenanceProgramLogicalConfiguration (tMIMSLocation_ID, tMSRevision_ID, tLifeCode_ID, LifeCodeLife, LifeCodeThreshold)
83 CREATE INDEX IX_MPLC_3 ON #MaintenanceProgramLogicalConfiguration (OneTime)
84 CREATE INDEX IX_MPLC_4 ON #MaintenanceProgramLogicalConfiguration (FixedCalendarLife)
85 CREATE INDEX IX_MPLC_5 ON #MaintenanceProgramLogicalConfiguration (VariableLife)
86
87
88 SELECT
89 tAsset.AssetNo
90 , tAsset.ID tAsset_ID
91 , tAsset.SerialNo
92 , sPart.PartNo
93 , tModel.Model
94 , tReg.Reg
95 , tMI.MI
96 , tMI.Title
97 , tMIMSLocation.ID tMIMSLocation_ID
98 , tAssetMIMSLocation.ID tAssetMIMSLocation_ID
99 , tAssetMIMSLocation.NotApplicable
100 , tAssetMIMSLocation.OnExtension
101 , tAssetMIMSLocation.UpdatedNotConfirmed
102 , tAssetMIMSLocation.ThresholdUsed
103 , '+@SourceDB+'.dbo.tfnAssetMIMSThresholdUsed(tAsset.ID, tMIMSLocation.ID) CalculatedThresholdUsed
104 , tLifeCode.LifeCode
105 , MPLC.LifeCodeLife LogicalLifeCodeLife
106 , MPLC.LifeCodeThreshold LogicalLifeCodeThreshold
107 , MPLC.LifeCodeTolerance LogicalLifeCodeTolerance
108 , tRegJourneyLogBookLifeCodeEvents.LifeTotal
109 , tAssetHistoryLifeCode.FitLifeCodeTotal
110 , tAssetHistoryLifeCode.LifeCodeLife
111 , tAssetHistoryLifeCode.LifeCodeDue
112 , tAssetHistoryLifeCode.DueDate
113 , tAssetHistoryLifeCode.LifeCodeDueBeforeExt
114 , tAssetHistoryLifeCode.DueDateBeforeExt
115 , tReg.Sampling RegSampling
116 , tMIMS.Sampling MIMSSampling
117 , tAssetHistoryLifeCode.Sampling PhysicalLifeCodeSampling
118 , MPLC.Sampling LogicalLifeCodeSampling
119 , CASE
120 WHEN (tAssetHistoryLifeCode.LifeCodeLife != CASE
121 WHEN '+@SourceDB+'.dbo.tfnAssetMIMSThresholdUsed(tAsset.ID, tMIMSLocation.ID) = 1
122 THEN MPLC.LifeCodeThreshold
123 ELSE MPLC.LifeCodeLife
124 END OR tAssetHistoryLifeCode.ID IS NULL)
125 THEN 1
126 ELSE 0
127 END PhysicalIntervalNotEqualLogicalInterval
128 , CASE WHEN ThresholdUsed = 0 AND tMI.VariableLife = 1 THEN 1 ELSE 0 END VariableLifeMINotOnThreshold
129 , CASE
130 WHEN '+@SourceDB+'.dbo.tfnAssetMIMSThresholdUsed(tAsset.ID, tMIMSLocation.ID) = 0 AND MPLC.LifeCodeLife = 0 AND MPLC.LifeCodeThreshold > 0
131 THEN 1
132 ELSE 0
133 END NotTerminated
134 , CASE
135 WHEN '+@SourceDB+'.dbo.IsCalendarLifeCode(tAssetHistoryLifeCode.tLifeCode_ID) =1
136 THEN CASE
137 WHEN DATEDIFF(DAY, tAssetHistoryLifeCode.DueDate, sOrderTask.TaskCertifiedDate) != tAssetHistoryLifeCode.LifeCodeLife
138 THEN 1
139 ELSE 0
140 END
141 ELSE CASE
142 WHEN LifeCodeDue - FitLifeCodeTotal != tAssetHistoryLifeCode.LifeCodeLife
143 THEN 1
144 ELSE 0
145 END
146 END FitToDueNotEqualInterval
147 , CASE
148 WHEN '+@SourceDB+'.dbo.IsCalendarLifeCode(tAssetHistoryLifeCode.tLifeCode_ID) = 0
149 THEN CASE
150 WHEN tRegJourneyLogBookLifeCodeEvents.LifeTotal != FitLifeCodeTotal
151 THEN 1
152 ELSE 0
153 END
154 ELSE 0
155 END FitNotEqualLogBookLifeTotal
156 , CASE
157 WHEN tAssetHistoryLifeCode.tLogBook_ID != LogBookHistory.tLogBook_ID
158 THEN 1
159 ELSE 0
160 END LogBookIDWrongControl
161 , CASE WHEN tAssetHistoryLifeCode.TSNOk = 0 AND tAssetHistoryLifeCode.TSCOOk = 0 AND '+@SourceDB+'.dbo.IsCalendarLifeCode(tLifeCode.ID) = 0 THEN 1 ELSE 0 END TSNTSCONotTicked
162 INTO '+@DestDB+'.dbo.MaintenanceItemErrorChecking
163 FROM #MaintenanceProgramLogicalConfiguration MPLC
164 JOIN '+@SourceDB+'.dbo.tLifeCode ON MPLC.tLifeCode_ID = tLifeCode.ID
165 JOIN '+@SourceDB+'.dbo.tAsset
166 JOIN '+@SourceDB+'.dbo.tModel ON tAsset.tModel_ID = tModel.ID
167 JOIN '+@SourceDB+'.dbo.sPart ON tAsset.sPart_ID = sPart.ID
168 ON MPLC.tModel_ID = tAsset.tModel_ID AND tAsset.sPart_ID = ISNULL(NULLIF(MPLC.sPart_ID,0), tAsset.sPart_ID) AND tAsset.SerialNo BETWEEN ISNULL(MPLC.SerialNoFrom,tAsset.SerialNo) AND ISNULL(MPLC.SerialNoTo,tAsset.SerialNo)
169 LEFT JOIN '+@SourceDB+'.dbo.tAssetTree AT2Reg
170 JOIN '+@SourceDB+'.dbo.tReg
171 JOIN '+@SourceDB+'.dbo.tRegStatus ON tRegStatus.ID = tReg.tRegStatus_ID
172 ON AT2Reg.tAsset_IDTop = tReg.tAsset_ID
173 ON AT2Reg.tAsset_ID = tAsset.ID
174 JOIN '+@SourceDB+'.dbo.tMIMSLocation
175 JOIN '+@SourceDB+'.dbo.tMIMS
176 LEFT JOIN '+@SourceDB+'.dbo.tMSRevision
177 JOIN '+@SourceDB+'.dbo.tMS ON tMSRevision.tMS_ID = tMS.ID
178 ON tMIMS.tMSRevision_ID = tMSRevision.ID
179 ON tMIMSLocation.tMIMS_ID = tMIMS.ID
180 JOIN '+@SourceDB+'.dbo.tMI ON tMIMS.tMI_ID = tMI.ID
181 ON MPLC.tMIMSLocation_ID = tMIMSLocation.ID
182 LEFT JOIN '+@SourceDB+'.dbo.tAssetMIMSLocation
183 LEFT JOIN '+@SourceDB+'.dbo.sOrderTask
184 JOIN '+@SourceDB+'.dbo.sOrder ON sOrderTask.sOrder_ID = sOrder.ID
185 ON tAssetMIMSLocation.sOrderTask_ID = sOrderTask.ID
186 ON tAssetMIMSLocation.tAsset_ID = tAsset.ID AND tAssetMIMSLocation.tMIMSLocation_ID = MPLC.tMIMSLocation_ID AND ActiveRecord = 1
187 LEFT JOIN '+@SourceDB+'.dbo.tAssetHistoryLifeCode ON tAssetHistoryLifeCode.tAssetMIMSLocation_ID = tAssetMIMSLocation.ID AND tAssetHistoryLifeCode.tLifeCode_ID = MPLC.tLifeCode_ID AND (tAssetHistoryLifeCode.Sampling = MPLC.Sampling)
188 OUTER APPLY (SELECT tLogBook_ID FROM '+@SourceDB+'.dbo.tfnAssetLogBookHeirachy(tAsset.ID) WHERE Level = 1) LogBookHierarchy
189 LEFT JOIN
190 (
191 SELECT tRegJourneyLogBook.tLogBook_ID
192 , tRegJourney.ID tRegJourney_ID
193 , LifeTotal
194 , tRegJourneyLogBookLifeCodeEvents.tLifeCode_ID
195 FROM '+@SourceDB+'.dbo.tRegJourneyLogBook
196 JOIN
197 (
198 SELECT tRegJourneyLogBook.tLogBook_ID tLogBook_ID
199 , MAX(TakeOffTime) TakeOffTime
200 FROM '+@SourceDB+'.dbo.tRegJourney
201 JOIN '+@SourceDB+'.dbo.tRegJourneyLogBook
202 ON tRegJourneyLogBook.tRegJourney_ID = tRegJourney.ID
203 GROUP BY tRegJourneyLogBook.tLogBook_ID
204 ) MaxJourney
205 ON tRegJourneyLogBook.tLogBook_ID = MaxJourney.tLogBook_ID
206 JOIN '+@SourceDB+'.dbo.tRegJourney
207 ON tRegJourneyLogBook.tRegJourney_ID = tRegJourney.ID
208 AND MaxJourney.TakeOffTime = tRegJourney.TakeOffTime
209 JOIN '+@SourceDB+'.dbo.tRegJourneyLogBookLifeCodeEvents
210 ON tRegJourneyLogBookLifeCodeEvents.tRegJourneyLogBook_ID = tRegJourneyLogBook.ID
211 ) LogBookHistory
212 ON LogBookHistory.tLogBook_ID = LogBookHierarchy.tLogBook_ID AND LogBookHistory.tLifeCode_ID = tAssetHistoryLifeCode.tLifeCode_ID
213 LEFT JOIN '+@SourceDB+'.dbo.sOrderTaskRegJourneyLogBook
214 JOIN '+@SourceDB+'.dbo.tRegJourneyLogBook ON sOrderTaskRegJourneyLogBook.tRegJourneyLogBook_ID = tRegJourneyLogBook.ID
215 JOIN '+@SourceDB+'.dbo.tRegJourneyLogBookLifeCodeEvents ON tRegJourneyLogBookLifeCodeEvents.tRegJourneyLogBook_ID = tRegJourneyLogBook.ID
216 ON sOrderTaskRegJourneyLogBook.sOrderTask_ID = sOrderTask.ID AND tRegJourneyLogBook.tLogBook_ID = LogBookHierarchy.tLogBook_ID AND tRegJourneyLogBookLifeCodeEvents.tLifeCode_ID = tLifeCode.ID
217 WHERE (tReg.tMSRevision_ID = MPLC.tMSRevision_ID OR ISNULL(tReg.tMSRevision_ID, NULLIF(tAsset.tMSRevision_IDDetached,0)) = MPLC.tMSRevision_ID OR MPLC.tMSRevision_ID = 0)
218 AND tAssetMIMSLocation.NotApplicable = 0
219 AND tRegStatus.Active = 1
220 AND ((MPLC.Sampling = 1 AND tReg.Sampling = 1 AND tMIMS.Sampling = 1) OR (tMIMS.Sampling = 0))')
221
222
223
224GO