· 7 years ago · Sep 23, 2018, 05:10 AM
1if exists (select * from dbo.sysobjects where id = object_id(N'dbo.sp_IBGCMP_xml_violation_getList') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
2 drop procedure dbo.sp_IBGCMP_xml_violation_getList
3GO
4---------------------------------------------------------------------------------
5-- Name: sp_IBGCMP_xml_violation_getList
6--
7-- Purpose: Get the violation rows by selection criteria.
8-- Returns:
9--
10-- Remarks:
11---------------------------------------------------------------------------------
12PRINT 'Creating sp_IBGCMP_xml_violation_getList'
13GO
14CREATE Procedure [dbo].[sp_IBGCMP_xml_violation_getList]
15(
16 @session_id ut_session,
17 @portfolioId int = NULL,
18 @filterFromDate datetime = NULL,
19 @filterToDate datetime = NULL,
20 @includeClose char(1) = 'N'
21)
22As
23 SET NOCOUNT ON
24
25 DECLARE @requestId int,
26 @dateLast datetime,
27 @err int
28
29 set @err=0
30
31 -- defauts
32 if @filterFromDate IS NULL
33 SET @filterFromDate = 'January 15, 1900'
34
35 if @filterToDate IS NULL
36 SET @filterToDate = GETDATE()
37 else
38 SET @filterToDate = Cast((CAST(@filterToDate as int) + 1) as datetime)
39
40 -- adjust toDate to include closing information from the next day snapshot
41 declare @nextSnapshotId int
42 declare @nextSnapshotDate datetime
43
44 -- get next day snapshot
45 select top 1 @nextSnapshotId= IBGFEX_snapshot_id, @nextSnapshotDate= dateAsOf
46 from ibgfex_snapshot
47 where dateAsOf > @filterToDate
48 order by dateAsOf asc
49
50 set @filterToDate= COALESCE(@nextSnapshotDate, @filterToDate)
51
52 -- exclude exported scenarios
53 DECLARE @TB_Scenarios_excluded TABLE (scenario_id int)
54 INSERT INTO @TB_Scenarios_excluded
55 select IBGSCE_scenario_id
56 from IBGSCE_scenario
57 where IBGFEX_snapshot_id =@nextSnapshotId and track_compliance<> 'Y'
58
59
60
61
62 DECLARE @TB_Violations TABLE ( violationId int, portfolioId int, currentDate datetime )
63
64 DECLARE @TB_Portfolios TABLE ( violationId int, currentDate datetime )
65
66 DECLARE @TB_AlerteStatus TABLE ( violationId int, alertCurrently char(1) )
67
68 DECLARE @TB_ViolationDates TABLE ( violationId int, lastDate datetime )
69 INSERT INTO @TB_ViolationDates
70 SELECT
71 IBGCMP_violation_id,
72 MAX(CASE S.track_compliance WHEN 'Y' THEN S.dateCreated ELSE dateExported END) as LastDate
73 FROM dbo.IBGSCE_solution_diagnostics D
74 INNER JOIN dbo.IBGSCE_scenario_portfolios P
75 ON P.mq_request_id = D.mq_request_id
76 INNER JOIN dbo.IBGSCE_scenario S
77 ON S.IBGSCE_scenario_id = P.IBGSCE_scenario_id
78 LEFT OUTER JOIN @TB_Scenarios_excluded EXCLUDED
79 ON S.IBGSCE_scenario_id = EXCLUDED.scenario_id
80 WHERE
81 D.IBGCMP_violation_id IS NOT NULL
82 And (
83 S.track_compliance = 'Y'
84 OR S.dateExported IS NOT NULL
85 )
86 AND EXCLUDED.scenario_id IS NULL
87 GROUP BY
88 D.IBGCMP_violation_id
89
90
91 -- Selection of violation based on search criteria
92
93 -- Add to #violations as selection of violationId based on search criteria
94 INSERT INTO @TB_Violations
95 SELECT DISTINCT
96 IBGCMP_violation_id, IBGSCE_portfolio_id, P.lastDate
97 FROM
98 dbo.IBGCMP_violation V
99
100 LEFT JOIN @TB_ViolationDates P
101 ON P.violationId = V.IBGCMP_violation_id
102 WHERE
103 -- by portfolio
104 IBGSCE_portfolio_id = IsNull(@portfolioId, IBGSCE_portfolio_id)
105 -- violation period needs to intersect the from-to interval
106 and not (
107 IsNull(closed_date, @filterFromDate ) < @filterFromDate -- if was closed date before from
108 or opened_date >= @filterToDate -- if was not opened after toDate
109 )
110 --by closed status
111 and (
112 closed_date Is NULL
113 or (closed_date Is Not NULL And IsNull(@includeClose, 'N') = 'Y')
114 )
115
116
117
118
119
120
121 INSERT INTO @TB_Portfolios (violationId, currentDate)
122 SELECT DISTINCT
123 V.violationId,
124 MAX(CASE S.track_compliance WHEN 'Y' THEN S.dateCreated ELSE dateExported END) As LastDate
125 FROM
126 @TB_Violations V
127 INNER JOIN dbo.IBGSCE_scenario_portfolios P
128 ON P.IBGSCE_portfolio_id = V.portfolioId
129 INNER HASH JOIN dbo.IBGSCE_scenario S
130 ON S.IBGSCE_scenario_id = P.IBGSCE_scenario_id
131
132 WHERE
133 P.rebalStatus = 'done'
134 And (
135 S.track_compliance = 'Y'
136 OR S.dateExported IS NOT NULL
137 )
138
139
140 GROUP BY
141 V.violationId
142
143 INSERT INTO @TB_AlerteStatus
144 SELECT DISTINCT
145 V.violationId,
146 CASE V.currentDate
147 WHEN P.currentDate THEN 'Y'
148 ELSE 'N'
149 END
150 FROM
151 @TB_Violations V
152 INNER JOIN @TB_Portfolios P
153 ON P.violationId = V.violationId
154
155 SELECT
156 1 as TAG,
157 NULL as PARENT,
158 NULL as [violationList!1],
159 NULL as [violation!2!violationId],
160 NULL as [violation!2!portfolioId],
161 NULL as [violation!2!isReported],
162 NULL as [violation!2!isCurrentlyInAlert],
163 NULL as [violation!2!openedDate!element],
164 NULL as [violation!2!openedDateOverride!element],
165 NULL as [violation!2!closedDate!element],
166 NULL as [violation!2!closedDateOverride!element],
167 NULL as [violation!2!titleFr!element],
168 NULL as [violation!2!titleEn!element],
169 NULL as [violation!2!reviewLastDate!element],
170 NULL as [violation!2!reviewFrequency!element],
171 NULL as [violation!2!reviewFrequencyType!element],
172 NULL as [violation!2!justificationFr!element],
173 NULL as [violation!2!justificationEn!element],
174 NULL as [violation!2!notes!element]
175
176 UNION ALL
177
178 -- diagnostics
179 SELECT DISTINCT
180 2,1,
181 NULL,
182 IBGCMP_violation_id, IBGSCE_portfolio_id, reported,
183 CASE WHEN closed_date IS NULL THEN IsNull(alertCurrently,'N') ELSE 'N' END,
184 opened_date, opened_date_override,
185 closed_date, closed_date_override,
186 title_fr, title_en,
187 review_last_date, review_frequence, review_frequency_type,
188 comment_fr, comment_en, notes
189 FROM
190 IBGCMP_violation CMP
191 INNER JOIN @TB_Violations V
192 ON V.violationId = CMP.IBGCMP_violation_id
193 LEFT JOIN @TB_AlerteStatus TB
194 ON TB.violationId = V.violationId
195
196 ORDER BY
197 TAG ASC, [violation!2!violationId]
198
199 FOR XML EXPLICIT
200
201 return @err
202
203GO
204/*
205exec sp_IBGCMP_xml_violation_getList
206 'SESSION' --@session_id ut_session
207 ,1001 --@portfolioId int= NULL
208 ,NULL --@filterFromDate datetime= NULL
209 ,NULL --@filterToDate datetime= NULL
210 ,NULL --@isClosed char(1)= NULL
211*/
212--sp_IBGCMP_xml_violation_getList 'SESSION' --, NULL, '2010-10-01', '2010-10-30', 'N'