· 6 years ago · Apr 02, 2019, 01:42 AM
1
2CREATE FUNCTION [dbo].[BPv3_F_ConcatenatedList]
3(
4 @ObjectType VARCHAR(100)
5, @Delimiter VARCHAR(10)
6, @UniqueID INT
7)
8RETURNS VARCHAR(MAX) --changed 11/20/17 from 1000
9AS
10
11--EPerlstein: Created 7/6/12 for CMP v3
12--PFraysse: 08/10/16 Update to include processing of 'ClientTypes'
13--PFraysse: 12/05/16 Update to include processing of 'ExpertReportResultMedCodeLongForm'
14--EPerlstein: 2/6/17 Added 'WCCaseStatuses' to concatenate case statuses for all client's WC cases
15--PFraysse: 02/14/17 Added 'AllCaseStatuses' to Concatenate Current and Previous Case Statuses for ANY Cases
16--EPerlstein 3/20/17: Changed 'MedCodeShortForm'
17--EPerlstein 3/20/17: Added 'Conf Chart Economic Report' for Confidential Chart for Matters
18--EPerlstein 3/21/17: Added CASE to SELECT for 'ICDCPT'
19--EPerlstein 5/24/17: Added 'MotionDefendant'
20--EPerlstein 10/3/17: Added 'DiseasesCodesForLAChart'
21--EPerlstein 10/6/17: Added 'DefsPerCaseForLAChart'
22--EPerlstein 10/16/17: Added 'DiscoveryTypeForLAChart'
23--EPerlstein 10/17/17: Added 'DefCodesNoClaimsStatus'
24--EPerlstein 10/24/17: Added 'ReleasableMedCodes'
25--EPerlstein 10/26/17: Added 'DiseasesCodesForCPORogs'
26--EPerlstein 11/17/17: Added 'WitnessesPerHistoryID'
27--bknight 12/18/17: Added 'CaseStatuses' with heavy error trapping
28--EPerlstein 05/04/18: Added 'CounselNameAddress'
29--Eperlstein 06/25/18: Added 'DiscoveryTypesForOverdueLetters' for CMPVIII-309
30--EPerlstein 07/05/18: Added 'WCCaseTypes' for CMPVIII-307
31--EPerlstein 07/19/18: Changed 'WCCaseStatuses' to deal with duplicates
32--EPerlstein 08/02/18: Added 'MedCodeLongFormPlusDiagnosisDate' for CMPVIII-307
33--DSorensen 08/02/18: Added 'Notes'
34--DSorensen 08/03/18: Removed 'Notes' --No good
35--EPerlstein 12/17/18: Changed WCCaseStatuses as noted below
36--EPerlstein 01/25/19: Changed Where clause of 'OtherClaims' to pull from correct location
37--EPerlstein 03/23/19: Added 'AsbestosCaseTypes'
38--EPerlstein 03/28/19: Modified 'ReleasableMedCodes' - Changed WHERE EXISTS to WHERE FinalDiseaseList.t_Name IN
39
40BEGIN
41
42 DECLARE @ConcatenatedList VARCHAR(MAX)
43 SET @ConcatenatedList = ''
44 DECLARE @FieldID Int
45 DECLARE @SectionID int
46
47 IF @ObjectType = 'MedCodeShortForm'
48 BEGIN
49 --DECLARE @FieldID Int
50 SELECT @FieldID = i_FieldID
51 FROM Cmn_CustomFields WHERE t_Label = 'Disease Code'
52
53 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + InnerQuery.MedicalCode
54 FROM
55 (
56 SELECT DISTINCT MedCodes.MedicalCode
57 FROM Cmn_CustomData_Associations
58 INNER JOIN Cmn_CustomSectionsSelected
59 ON Cmn_CustomSectionsSelected.i_SelectedSectionID = Cmn_CustomData_Associations.i_SelectedSectionID
60 JOIN Cmn_Keywords ON Cmn_CustomData_Associations.i_RecordUniqueID = Cmn_Keywords.i_KeywordID
61 LEFT OUTER JOIN BP_TempObjects.dbo.BPv3_MedicalCodes AS MedCodes ON Cmn_Keywords.t_Name = MedCodes.Disease
62 WHERE Cmn_CustomData_Associations.i_FieldID = @FieldID
63 AND Cmn_CustomSectionsSelected.i_RecordUniqueID = @UniqueID
64 AND MedCodes.MedicalCode <> 'NULL' -- Updated by PFraysse on 10/17/16 / Changed by EPerlstein 3/20/17 from 'IS NOT NULL'
65 ) AS InnerQuery
66 END
67 ELSE IF @ObjectType = 'MedCodeLongForm' --EPerlstein: Created 9/26/14
68 BEGIN
69 SELECT @FieldID = i_FieldID
70 FROM Cmn_CustomFields WHERE t_Label = 'Disease Code'
71 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + InnerQuery.t_Name
72 FROM
73 (
74 SELECT DISTINCT Cmn_Keywords.t_Name
75 FROM Cmn_CustomData_Associations
76 INNER JOIN Cmn_CustomSectionsSelected
77 ON Cmn_CustomSectionsSelected.i_SelectedSectionID = Cmn_CustomData_Associations.i_SelectedSectionID
78 JOIN Cmn_Keywords ON Cmn_CustomData_Associations.i_RecordUniqueID = Cmn_Keywords.i_KeywordID
79 WHERE Cmn_CustomData_Associations.i_FieldID = @FieldID
80 AND Cmn_CustomSectionsSelected.i_RecordUniqueID = @UniqueID
81 ) AS InnerQuery
82
83 END
84 ELSE IF @ObjectType = 'ExpertReportResultMedCodes'
85 BEGIN
86 SELECT @FieldID = i_FieldID
87 from Cmn_CustomFields
88 JOIN Cmn_CustomSections AS CS ON Cmn_CustomFields.i_SectionID = CS.i_SectionID
89 where Cmn_CustomFields.t_Label = 'Results'
90 AND CS.t_Title = 'Expert Report'
91
92 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + MedCodes.MedicalCode
93 FROM Cmn_CustomData_Associations
94 INNER JOIN Cmn_CustomSectionsSelected
95 ON Cmn_CustomSectionsSelected.i_SelectedSectionID = Cmn_CustomData_Associations.i_SelectedSectionID
96 JOIN Cmn_Keywords ON Cmn_CustomData_Associations.i_RecordUniqueID = Cmn_Keywords.i_KeywordID
97 LEFT OUTER JOIN BP_TempObjects.dbo.BPv3_MedicalCodes AS MedCodes ON Cmn_Keywords.t_Name = MedCodes.Disease
98 WHERE Cmn_CustomData_Associations.i_FieldID = @FieldID
99 AND Cmn_CustomSectionsSelected.i_RecordUniqueID = @UniqueID
100 END
101 --PFraysse 12/05/16: Activated for the [BPv3_SP_ExpertReportNotification] conversion
102 ELSE IF @ObjectType = 'ExpertReportResultMedCodeLongForm'
103 BEGIN
104 SELECT @FieldID = i_FieldID
105 from Cmn_CustomFields
106 JOIN Cmn_CustomSections AS CS ON Cmn_CustomFields.i_SectionID = CS.i_SectionID
107 where Cmn_CustomFields.t_Label = 'Results'
108 AND CS.t_Title = 'Expert Report'
109 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + InnerQuery.t_Name
110 FROM
111 (
112 SELECT DISTINCT Cmn_Keywords.t_Name
113 FROM Cmn_CustomData_Associations
114 INNER JOIN Cmn_CustomSectionsSelected
115 ON Cmn_CustomSectionsSelected.i_SelectedSectionID
116 = Cmn_CustomData_Associations.i_SelectedSectionID
117 JOIN Cmn_Keywords ON Cmn_CustomData_Associations.i_RecordUniqueID
118 = Cmn_Keywords.i_KeywordID
119 WHERE Cmn_CustomData_Associations.i_FieldID = @FieldID
120 AND Cmn_CustomSectionsSelected.i_RecordUniqueID = @UniqueID
121 ) AS InnerQuery
122 END
123
124 ELSE IF @ObjectType = 'ECOM'
125 BEGIN
126 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + 'ECO-M: ' + ISNULL(CONVERT(VARCHAR(1000),DocumentNotes.Data),'')
127 FROM Doc_Documents
128 JOIN Doc_DocumentContacts ON Doc_Documents.i_DocumentID = Doc_DocumentContacts.i_DocumentID
129 INNER JOIN Cmn_Keywords
130 ON Cmn_Keywords.i_KeywordID = Doc_Documents.k_Type
131 LEFT OUTER JOIN (
132 Select t_Label AS Label, t_Data AS Data, Cmn_CustomData.i_RecordUniqueID AS UniqueID
133 From Cmn_CustomData
134 JOIN Cmn_CustomFields ON Cmn_CustomData.i_FieldID = Cmn_CustomFields.i_FieldID
135 WHERE Cmn_CustomFields.i_FieldID = (select i_FieldID
136 from Cmn_CustomFields where t_Label = 'Notes'
137 and i_SectionID = (Select Cmn_CustomSections.i_SectionID
138 FROM Cmn_CustomSections
139 WHERE Cmn_CustomSections.t_Title = 'Expert Report'))
140 ) AS DocumentNotes ON Doc_Documents.i_DocumentID = DocumentNotes.UniqueID
141 WHERE Cmn_Keywords.t_Name = 'Expert Medical|Economic Loss - Medical Bills'
142 AND Doc_DocumentContacts.i_ContactAddressBookID = @UniqueID
143 GROUP BY DocumentNotes.Data
144 ORDER BY DocumentNotes.Data DESC --EPerlstein 2/27/17: Added for date sort since date seems to be at beginning of text field
145 END
146 ELSE IF @ObjectType = 'ICDCPT'
147 BEGIN
148 select @ConcatenatedList = @ConcatenatedList + @Delimiter + CASE
149 WHEN ExamDate.Data IS NULL THEN ISNULL(CONVERT(VARCHAR(1000),DocumentNotes.Data),'')
150 WHEN ExamDate.Data LIKE '%N/A%' THEN ISNULL(CONVERT(VARCHAR(1000),DocumentNotes.Data),'')
151 ELSE ISNULL(CONVERT(VARCHAR(10),ExamDate.Data,101),'') + ' ' + ISNULL(CONVERT(VARCHAR(1000),DocumentNotes.Data),'')
152 END
153 FROM Doc_Documents
154 JOIN Doc_DocumentContacts AS DC ON Doc_Documents.i_DocumentID = DC.i_DocumentID
155 JOIN Cmn_Keywords ON Doc_Documents.k_Type = Cmn_Keywords.i_KeywordID
156 LEFT OUTER JOIN (
157 Select t_Label AS Label, t_Data AS Data, Cmn_CustomData.i_RecordUniqueID AS UniqueID
158 From Cmn_CustomData
159 JOIN Cmn_CustomFields ON Cmn_CustomData.i_FieldID = Cmn_CustomFields.i_FieldID
160 WHERE Cmn_CustomFields.i_FieldID = (select i_FieldID
161 from Cmn_CustomFields where t_Label = 'Exam Date')
162 ) AS ExamDate ON Doc_Documents.i_DocumentID = ExamDate.UniqueID
163 LEFT OUTER JOIN (
164 Select t_Label AS Label, t_Data AS Data, Cmn_CustomData.i_RecordUniqueID AS UniqueID
165 From Cmn_CustomData
166 JOIN Cmn_CustomFields ON Cmn_CustomData.i_FieldID = Cmn_CustomFields.i_FieldID
167 WHERE Cmn_CustomFields.i_FieldID = (select i_FieldID
168 from Cmn_CustomFields where t_Label = 'Notes'
169 and i_SectionID = (Select Cmn_CustomSections.i_SectionID
170 FROM Cmn_CustomSections
171 WHERE Cmn_CustomSections.t_Title = 'Expert Report'))
172 ) AS DocumentNotes ON Doc_Documents.i_DocumentID = DocumentNotes.UniqueID
173 WHERE Cmn_Keywords.t_Name = 'Expert Medical|ICD / CPT'
174 AND DC.i_ContactAddressBookID = @UniqueID
175 GROUP BY ExamDate.Data, DocumentNotes.Data
176 END
177 ELSE IF @ObjectType = 'ClientRelationshipNonHeir'
178 --EPerlstein 7/9/14: This is patterned after the v1 version, which pulled heirs but
179 --didn't included the heir designation in the output list.
180 BEGIN
181 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Field_Relationships.Name
182 FROM Cmn_CustomSectionsSelected AS CSS
183 LEFT OUTER JOIN (
184 SELECT (select t_Name from Cmn_Keywords
185 where Cmn_Keywords.i_KeywordID = CDA.i_RecordUniqueID) AS Name
186 ,CD.i_SelectedSectionID, t_Data, CDA.i_RecordUniqueID
187 FROM Cmn_CustomData CD
188 JOIN Cmn_CustomData_Associations AS CDA ON CD.i_SelectedSectionID = CDA.i_SelectedSectionID
189 WHERE CDA.i_FieldID = (SELECT i_FieldID
190 FROM Cmn_CustomFields
191 WHERE i_SectionID = (SELECT i_SectionID FROM Cmn_CustomFields
192 WHERE t_Label = 'Relationships')
193 AND t_Label = 'Relationships')
194 ) Field_Relationships ON CSS.i_SelectedSectionID = Field_Relationships.i_SelectedSectionID
195 JOIN Cmn_CustomData_Associations AS CDA ON CSS.i_SelectedSectionID = CDA.i_SelectedSectionID
196 JOIN AB_Contacts ON CDA.i_RecordUniqueID = AB_Contacts.i_ContactID
197 JOIN Cas_CaseParties ON AB_Contacts.i_ContactID = Cas_CaseParties.A_Party
198 JOIN Cmn_Keywords ON Cas_CaseParties.k_Role = Cmn_Keywords.i_KeywordID
199 WHERE CSS.i_SectionID = (SELECT i_SectionID FROM Cmn_CustomFields
200 WHERE t_Label = 'Relationships')
201 AND Cas_CaseParties.i_CaseID = @UniqueID
202 AND CDA.i_RecordID = 13 --Contact Record
203 AND Cmn_Keywords.t_Name = 'Lead Plaintiff'
204 AND Field_Relationships.Name <> 'Heir'
205 GROUP BY Field_Relationships.Name
206 END
207 ELSE IF @ObjectType = 'OtherClaims'
208 BEGIN
209 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Cmn_Keywords.t_Name
210 FROM Cmn_CustomData_Associations
211 INNER JOIN Cmn_CustomSectionsSelected
212 ON Cmn_CustomSectionsSelected.i_SelectedSectionID = Cmn_CustomData_Associations.i_SelectedSectionID
213 JOIN Cmn_Keywords ON Cmn_CustomData_Associations.i_RecordUniqueID = Cmn_Keywords.i_KeywordID
214 WHERE Cmn_CustomData_Associations.i_FieldID = (select i_FieldID
215 FROM Cmn_CustomFields where t_Label = 'Other Claims'
216 AND Cmn_CustomFields.i_SectionID = dbo.BPv3_F_GetSectionID ('PartyMedical','Medical Info'))
217 --WHERE Cmn_CustomData_Associations.i_FieldID = (select i_FieldID
218 -- FROM Cmn_CustomFields where t_Label = 'Other Claims' AND i_DataType = 2)
219 AND Cmn_CustomSectionsSelected.i_RecordUniqueID = @UniqueID
220 END
221 ELSE IF @ObjectType = 'CaseTypes'
222 BEGIN
223 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Cmn_Keywords.t_Name
224 FROM Cas_CaseTypes
225 JOIN Cmn_Keywords ON Cas_CaseTypes.k_CaseType = Cmn_Keywords.i_KeywordID
226 WHERE Cas_CaseTypes.i_CaseID = @UniqueID
227 END
228 ELSE IF @ObjectType = 'AsbestosCaseTypes' --EPerlstein - Added 3/23/19
229 BEGIN
230 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Cmn_Keywords.t_Name
231 FROM Cas_CaseTypes
232 JOIN Cmn_Keywords ON Cas_CaseTypes.k_CaseType = Cmn_Keywords.i_KeywordID
233 JOIN BPv3_V_AsbestosCaseTypes ON BPv3_V_AsbestosCaseTypes.i_CaseID = Cas_CaseTypes.i_CaseID
234 WHERE Cas_CaseTypes.i_CaseID = @UniqueID
235 END
236 ELSE IF @ObjectType = 'CaseStatuses'
237 BEGIN
238 --
239 -- Step 1: get status from Case
240 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Cmn_Keywords.t_Name
241 FROM Cas_Cases
242 INNER JOIN Cmn_Keywords ON Cas_Cases.k_Status = Cmn_Keywords.i_KeywordID
243 WHERE Cas_Cases.i_CaseID = @UniqueID
244 --
245 -- Step 2: get custom status (if any)
246 -- NOTE: it is necessary to use OPTION (FORCE ORDER) in order to prevent issues resulting
247 -- from the final JOIN, Cmn_CustomData.t_Data = Cmn_Keywords.i_KeywordID (varchar = int -- BAD)
248 -- also put 'Other Status' and 'Status' filtering conditions in the JOIN
249 -- rather than the WHERE clause to help with this effort
250 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Cmn_Keywords.t_Name
251 FROM Cas_Cases
252 INNER JOIN Cmn_CustomSectionsSelected ON Cas_Cases.i_CaseID = Cmn_CustomSectionsSelected.i_RecordUniqueID
253 INNER JOIN Cmn_CustomSections
254 ON Cmn_CustomSectionsSelected.i_SectionID = Cmn_CustomSections.i_SectionID
255 AND Cmn_CustomSections.t_Title = 'Other Status'
256 INNER JOIN Cmn_CustomFields
257 ON Cmn_CustomSections.i_SectionID = Cmn_CustomFields.i_SectionID
258 AND Cmn_CustomFields.t_Label = 'Status'
259 INNER JOIN Cmn_CustomData
260 ON Cmn_CustomSectionsSelected.i_SelectedSectionID = Cmn_CustomData.i_SelectedSectionID
261 AND Cmn_CustomFields.i_FieldID = Cmn_CustomData.i_FieldID
262 AND ISNUMERIC(Cmn_CustomData.t_Data) = 1 --<<< also an attempt to try to trap errors, bubt not foolproof
263 INNER JOIN Cmn_Keywords
264 ON Cmn_CustomData.t_Data = Cmn_Keywords.i_KeywordID
265 WHERE Cas_Cases.i_CaseID = @UniqueID
266 OPTION (FORCE ORDER) -- REMOVE THIS AT YOUR OWN RISK!!!
267 --
268 END
269 ELSE IF @ObjectType = 'AltNames'
270 BEGIN
271 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + ' @' + AN.t_AlternateName
272 FROM AB_Parties_AlternateNames AS AN
273 --JOIN Cmn_Keywords AS NameType ON AN.k_Type = NameType.i_KeywordID
274 WHERE A_Party = @UniqueID
275 --AND NameType.t_Name <> 'BACK LINK'
276 END
277 ELSE IF @ObjectType = 'Economic Report'
278 BEGIN
279 SELECT @SectionID = dbo.BPv3_F_GetSectionID ('Contact','Expert Info')
280 --PRINT @SectionID
281
282 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + ExpertCode.Data + + ': ' + ISNULL(CONVERT(VARCHAR(1000),DocumentNotes.Data),'')
283 FROM Doc_Documents
284 JOIN Doc_DocumentContacts AS DC ON Doc_Documents.i_DocumentID = DC.i_DocumentID
285 JOIN Cmn_Keywords AS DocType ON Doc_Documents.k_Type = DocType.i_KeywordID
286 LEFT OUTER JOIN (
287 SELECT DC.i_ContactAddressBookID AS ExpertID, DC.i_DocumentID
288 FROM Doc_DocumentContacts AS DC
289 JOIN Cmn_Keywords ON DC.k_ContactType = Cmn_Keywords.i_KeywordID
290 WHERE Cmn_Keywords.t_Name = 'Expert'
291 AND DC.i_ContactAddressBookID <> 39548506
292 ) AS DocExpert ON Doc_Documents.i_DocumentID = DocExpert.i_DocumentID
293 LEFT OUTER JOIN (
294 Select t_Label AS Label, t_Data AS Data, Cmn_CustomData.i_RecordUniqueID AS UniqueID
295 From Cmn_CustomData
296 JOIN Cmn_CustomFields ON Cmn_CustomData.i_FieldID = Cmn_CustomFields.i_FieldID
297 WHERE Cmn_CustomFields.i_FieldID = (select i_FieldID
298 from Cmn_CustomFields where t_Label = 'Notes'
299 and i_SectionID = (Select Cmn_CustomSections.i_SectionID
300 FROM Cmn_CustomSections
301 WHERE Cmn_CustomSections.t_Title = 'Expert Report'))
302 ) AS DocumentNotes ON Doc_Documents.i_DocumentID = DocumentNotes.UniqueID
303 LEFT OUTER JOIN (
304 Select t_Label AS Label, t_Data AS Data, Cmn_CustomData.i_RecordUniqueID AS UniqueID
305 From Cmn_CustomData
306 JOIN Cmn_CustomFields ON Cmn_CustomData.i_FieldID = Cmn_CustomFields.i_FieldID
307 WHERE Cmn_CustomFields.i_FieldID = (select i_FieldID
308 from Cmn_CustomFields where t_Label = 'Expert Code'
309 and i_SectionID = @SectionID)
310 ) AS ExpertCode ON DocExpert.ExpertID = ExpertCode.UniqueID
311 WHERE DocType.t_Name = 'Expert Medical|Economic Report'
312 AND DC.i_ContactAddressBookID = @UniqueID --@IPContactID
313 GROUP BY ExpertCode.Data, DocumentNotes.Data
314 END
315 ELSE IF @ObjectType = 'Overall Exposure'
316 BEGIN
317 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Cmn_Keywords.t_Name
318 FROM Cmn_CustomData_Associations
319 INNER JOIN Cmn_CustomSectionsSelected
320 ON Cmn_CustomSectionsSelected.i_SelectedSectionID = Cmn_CustomData_Associations.i_SelectedSectionID
321 JOIN Cmn_Keywords ON Cmn_CustomData_Associations.i_RecordUniqueID = Cmn_Keywords.i_KeywordID
322 WHERE Cmn_CustomData_Associations.i_FieldID = (select i_FieldID
323 from Cmn_CustomFields where t_Label = 'Overall Exposure')
324 AND Cmn_CustomSectionsSelected.i_RecordUniqueID = @UniqueID
325 END
326 ELSE IF @ObjectType = 'Gould Medicare Document Descriptions'
327 BEGIN
328 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Doc_Documents.a_Description
329 FROM Doc_Documents
330 JOIN Doc_DocumentContacts AS DC ON Doc_Documents.i_DocumentID = DC.i_DocumentID
331 JOIN AB_Contacts AS OrderedFrom ON Doc_Documents.A_OrderedFrom = OrderedFrom.i_ContactID
332 WHERE Doc_Documents.t_Title LIKE 'Treating Medical|Medicare%'
333 AND OrderedFrom.t_Name LIKE 'Gould%'
334 AND DC.i_ContactAddressBookID = @UniqueID
335 END
336 ELSE IF @ObjectType = 'Service Information - Counsel'
337 --EPerlstein: Added 5/24/16 for Service Information report
338 BEGIN
339 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Counsel.t_Name + ' - ' + CA.t_City
340 FROM AB_Parties_ContactLists AS CL
341 JOIN AB_Parties_ContactLists_Contacts AS CLC ON CL.i_ContactListID = CLC.i_ContactListID
342 JOIN AB_Contacts AS Counsel ON CLC.A_Contact = Counsel.i_ContactID
343 LEFT OUTER JOIN AB_Contacts_Addresses AS CA ON CLC.A_Contact = CA.i_ContactID
344 LEFT OUTER JOIN Cmn_Keywords AS Title ON CLC.k_Title = Title.i_KeywordID
345 WHERE Title.t_Name = 'Counsel'
346 AND CL.b_Default = 1
347 and CL.A_Party = @UniqueID
348 END
349 --PFraysse: Added 8/09/16 in V3 'ClientTypes' are in the 'ContactTypes' category
350 ELSE IF @ObjectType = 'ClientTypes'
351 BEGIN
352 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + KeyType.t_Name
353 FROM AB_Contacts_Types AS CT
354 JOIN Cmn_Keywords AS KeyType ON CT.k_Type = KeyType.i_KeywordID
355 JOIN Cmn_KeywordLists AS KeyList ON KeyType.i_KeywordListID = KeyList.i_KeywordListID
356 WHERE KeyList.t_KeywordListName = 'ContactTypes'
357 AND CT.i_ContactID = @UniqueID
358 END
359 --PFraysse 09/30/16: Activated for the [SP_DiscoveryDue] conversion
360 ELSE IF @ObjectType = 'DiscoveryTypes'
361 BEGIN
362 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + InnerQuery.t_Name
363 FROM
364 (
365 SELECT Cmn_Keywords.t_Name
366 FROM Mat_Discovery_Types
367 JOIN Cmn_Keywords ON Mat_Discovery_Types.k_Type = Cmn_Keywords.i_KeywordID
368 WHERE Mat_Discovery_Types.i_DiscoveryID = @UniqueID
369 ) AS InnerQuery
370 END
371 --EPerlstein 2/6/17: Added to concatenate case statuses for all client's WC cases
372 --EPerlstein 7/19/18: Changed to add DISTINCT to handle duplicates
373 --EPerlstein 12/17/18: Added Case Type to determine a prefix to the status based on Living or Death WC case
374 ELSE IF @ObjectType = 'WCCaseStatuses'
375 BEGIN
376 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + -- WCStatusQuery.t_Name
377 CASE
378 WHEN CHARINDEX('Living', WCStatusQuery.CaseType, 1) > 0 THEN 'L (' + WCStatusQuery.t_Name + ')'
379 WHEN CHARINDEX('Death', WCStatusQuery.CaseType, 1) > 0 THEN 'D (' + WCStatusQuery.t_Name + ')'
380 ELSE WCStatusQuery.t_Name END
381 FROM
382 (
383 SELECT DISTINCT CaseStatus.t_Name, CaseTypes.CaseType
384 FROM Cas_Cases
385 JOIN (
386 SELECT Cas_CaseNumbers.i_CaseID, Cas_CaseNumbers.t_CaseNumber AS MatterNumber
387 FROM Cas_CaseNumbers
388 WHERE Cas_CaseNumbers.b_Primary = 1
389 ) AS CaseNumbers ON Cas_Cases.i_CaseID = CaseNumbers.i_CaseID
390 JOIN (
391 SELECT Cas_CaseParties.i_CaseID, Cas_CaseParties.A_Party, Cas_CaseParties.b_Primary,
392 AB_Contacts.t_Name AS IPName, AB_Contacts.d_DateOfDeath, AB_Contacts.b_Deceased
393 FROM Cas_CaseParties
394 JOIN AB_Contacts ON Cas_CaseParties.A_Party = AB_Contacts.i_ContactID
395 JOIN Cmn_Keywords ON Cas_CaseParties.k_Role = Cmn_Keywords.i_KeywordID
396 WHERE Cmn_Keywords.t_Name = 'Lead Injured Party'
397 ) AS InjuredParty ON Cas_Cases.i_CaseID = InjuredParty.i_CaseID
398 JOIN Cmn_Keywords AS CaseStatus ON Cas_Cases.k_Status = CaseStatus.i_KeywordID
399 LEFT OUTER JOIN (
400 SELECT Cas_CaseTypes.i_CaseID, Cmn_Keywords.t_Name AS CaseType
401 FROM Cas_CaseTypes
402 JOIN Cmn_Keywords ON Cas_CaseTypes.k_CaseType = Cmn_Keywords.i_KeywordID
403 ) AS CaseTypes ON Cas_Cases.i_CaseID = CaseTypes.i_CaseID
404 WHERE InjuredParty.A_Party = @UniqueID
405 AND dbo.BPv3_F_IsWorkersCompCase(Cas_Cases.i_CaseID) = 1
406 ) AS WCStatusQuery
407 END
408 --PFraysse 2/15/17: Added to concatenate ALL [Case Status] for a CaseID
409 -- Used in [BPv3_SP_DefaultJudgmentReport]
410 ELSE IF @ObjectType = 'AllCaseStatuses'
411 BEGIN
412 -- Pulling the Current Case Status from [Cmn_Keywords] table
413 SELECT @ConcatenatedList = ' ' +KCaseStatus.t_Name
414 FROM Cas_Cases
415 JOIN Cmn_Keywords AS KCaseStatus ON Cas_Cases.k_Status = KCaseStatus.i_KeywordID
416 WHERE Cas_cases.i_CaseID =
417 (SELECT Cas_CaseParties.i_CaseID
418 FROM Cas_CaseParties
419 JOIN AB_Contacts ON Cas_CaseParties.A_Party = AB_Contacts.i_ContactID
420 JOIN Cmn_Keywords ON Cas_CaseParties.k_Role = Cmn_Keywords.i_KeywordID
421 WHERE Cmn_Keywords.t_Name = 'Lead Injured Party'
422 AND Cas_CaseParties.i_CaseID = @UniqueID)
423
424
425 DECLARE @iFieldID_OtherStatus int
426 SELECT @iFieldID_OtherStatus = i_FieldID
427 FROM Cmn_CustomFields
428 WHERE t_Label = 'Status'
429 AND i_SectionID = dbo.BPv3_F_GetSectionID ('Case','Other Status')
430
431 -- Pulling Others Case Status from [Cmn_CustomData] table if exists
432 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + OtherStatusFields
433 FROM Cas_Cases
434 LEFT OUTER JOIN (
435 SELECT dbo.Cmn_CustomFields_FormatCustomData(@iFieldID_OtherStatus
436 , OtherStatus.t_Data, 0) AS OtherStatusFields
437 , Cmn_CustomSectionsSelected.i_RecordUniqueID
438 FROM Cmn_CustomSectionsSelected
439 JOIN (
440 SELECT i_SelectedSectionID, t_Data
441 FROM Cmn_CustomData
442 WHERE i_FieldID = @iFieldID_OtherStatus
443 ) AS OtherStatus ON OtherStatus.i_SelectedSectionID = Cmn_CustomSectionsSelected.i_SelectedSectionID
444 ) AS OS ON OS.i_RecordUniqueID = Cas_Cases.i_CaseID
445 WHERE Cas_cases.i_CaseID = @UniqueID
446 AND OtherStatusFields <> ''
447 END
448 ELSE IF @ObjectType = 'Conf Chart Economic Report' --EPerlstein: Added 3/20/17 for Confidential Chart for Matters
449 BEGIN
450 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + ExpertCode.Data + + ': ' + ISNULL(CONVERT(VARCHAR(1000),DocumentNotes.Data),'')
451 FROM Doc_Documents
452 JOIN Doc_DocumentContacts AS DC ON Doc_Documents.i_DocumentID = DC.i_DocumentID
453 JOIN Cmn_Keywords AS DocType ON Doc_Documents.k_Type = DocType.i_KeywordID
454 LEFT OUTER JOIN (
455 SELECT DC.i_ContactAddressBookID AS ExpertID, DC.i_DocumentID
456 FROM Doc_DocumentContacts AS DC
457 JOIN Cmn_Keywords ON DC.k_ContactType = Cmn_Keywords.i_KeywordID
458 WHERE Cmn_Keywords.t_Name = 'Expert'
459 AND DC.i_ContactAddressBookID <> 39548506
460 ) AS DocExpert ON Doc_Documents.i_DocumentID = DocExpert.i_DocumentID
461 LEFT OUTER JOIN (
462 Select t_Label AS Label, t_Data AS Data, Cmn_CustomData.i_RecordUniqueID AS UniqueID
463 From Cmn_CustomData
464 JOIN Cmn_CustomFields ON Cmn_CustomData.i_FieldID = Cmn_CustomFields.i_FieldID
465 WHERE Cmn_CustomFields.i_FieldID = (select i_FieldID
466 from Cmn_CustomFields where t_Label = 'Notes'
467 and i_SectionID = (Select Cmn_CustomSections.i_SectionID
468 FROM Cmn_CustomSections
469 WHERE Cmn_CustomSections.t_Title = 'Expert Report'))
470 ) AS DocumentNotes ON Doc_Documents.i_DocumentID = DocumentNotes.UniqueID
471 LEFT OUTER JOIN (
472 Select t_Label AS Label, t_Data AS Data, Cmn_CustomData.i_RecordUniqueID AS UniqueID
473 From Cmn_CustomData
474 JOIN Cmn_CustomFields ON Cmn_CustomData.i_FieldID = Cmn_CustomFields.i_FieldID
475 WHERE Cmn_CustomFields.i_FieldID = (select i_FieldID
476 from Cmn_CustomFields where t_Label = 'Expert Code'
477 and i_SectionID = dbo.BPv3_F_GetSectionID ('Contact','Expert Info'))
478 ) AS ExpertCode ON DocExpert.ExpertID = ExpertCode.UniqueID
479 WHERE DocType.t_Name IN ('Expert Medical|Economic Report',
480 'Expert Medical|Eco-Limited','Expert Medical|Economic No Household Services Loss','Expert Medical|Economic No Report QSTaire')
481 AND DC.i_ContactAddressBookID = @UniqueID --@IPContactID
482 GROUP BY ExpertCode.Data, DocumentNotes.Data
483 END
484 ELSE IF @ObjectType = 'MotionDefendant' --Added 5/24/17 for BPv3_SP_TwoWeekDueDates
485 BEGIN
486 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + AB_Contacts.t_Name
487 FROM Mat_MotionParties AS MP
488 JOIN AB_Contacts on MP.A_Party = AB_Contacts.i_ContactID
489 where MP.i_MotionID = @UniqueID
490 GROUP BY AB_Contacts.t_Name
491 ORDER BY AB_Contacts.t_Name
492 END
493 ELSE IF @ObjectType = 'DiseasesCodesForLAChart' --Added 10/34/17 for BPv3_SP_LA_CMC_MainQuery
494 BEGIN
495 SELECT @FieldID = i_FieldID
496 FROM Cmn_CustomFields WHERE t_Label = 'Disease Code'
497 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + InnerQuery.t_Name
498 FROM
499 (
500 SELECT DISTINCT Cmn_Keywords.t_Name
501 FROM Cmn_CustomData_Associations
502 INNER JOIN Cmn_CustomSectionsSelected
503 ON Cmn_CustomSectionsSelected.i_SelectedSectionID = Cmn_CustomData_Associations.i_SelectedSectionID
504 JOIN Cmn_Keywords ON Cmn_CustomData_Associations.i_RecordUniqueID = Cmn_Keywords.i_KeywordID
505 WHERE Cmn_CustomData_Associations.i_FieldID = @FieldID
506 AND Cmn_CustomSectionsSelected.i_RecordUniqueID = @UniqueID
507 AND Cmn_Keywords.t_Name IN (
508 'Anal Cancer','Asbestosis','Asbestosis on CT only','Benign Pleural Effusion',
509 'Buccal Cancer','Colon Cancer','Duodenum Cancer','Esophageal Cancer',
510 'Kidney/Renal Cancer','Laryngeal Cancer','Lung Cancer','Lymphoma',
511 'Mesothelioma','Other Asbestos Related Cancer','Oxygen Dependent',
512 'Pancreatic Cancer','Pharyngeal Cancer','Pleural Disease','Rectal Cancer',
513 'Stomach Cancer','Tongue Cancer','Tonsil Cancer','Vocal Cord Cancer')
514 ) AS InnerQuery
515 END
516 ELSE IF @ObjectType = 'DefsPerCaseForLAChart' --Added 10/6/17 for LC CMC Chart
517 BEGIN
518
519 ;WITH GetServiceList (S_ServiceID, S_Party, S_Status)
520 AS
521 ( SELECT DISTINCT i_ServiceID, A_Party , Cmn_Keywords.t_Name
522 FROM Mat_Service
523 JOIN Cmn_Keywords ON Mat_Service.k_Status = Cmn_Keywords.i_KeywordID
524 WHERE
525 (
526 Cmn_Keywords.t_Name IN ('Extension to Answer Granted',
527 'Cross Complaint Filed','Clerk''s Judgment Pending','Formal Service/Settling Defendant',
528 'Open Extension Granted','Complaint in Intervention Filed','Informal',
529 'Default Pending','Default Rejected by Court','Default w/ DRD for Review',
530 'Informal Answer Requested','Motion to Change Venue Granted','Notice of Appearance',
531 'Add to Service List','Previously Served/Other Firm')
532 OR Cmn_Keywords.t_Name LIKE 'Answer%'
533 OR Cmn_Keywords.t_Name LIKE 'Served%'
534 OR Cmn_Keywords.t_Name LIKE 'Pending%'
535 )
536 )
537
538 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Defendant.DefCode
539 FROM (
540 SELECT DISTINCT Cas_CaseParties.i_CaseID, Cas_CaseParties.A_Party, AB_Contacts.t_Name,
541 Fin_Claim.i_ClaimID, Cas_CaseParties.i_CasePartyID AS MatterDefendantID,
542 DefendantCode.DefCode
543 FROM Cas_CaseParties
544 JOIN Cmn_Keywords ON Cas_CaseParties.k_Role = Cmn_Keywords.i_KeywordID
545 JOIN AB_Contacts ON Cas_CaseParties.A_Party = AB_Contacts.i_ContactID
546 JOIN Fin_Claim ON Cas_CaseParties.i_CaseID = Fin_Claim.i_CaseID
547 AND AB_Contacts.i_ContactID = Fin_Claim.i_DefendantAddressBookID
548 JOIN (
549 SELECT ABPA.t_AlternateName As DefCode, ABPA.A_Party AS DefContactID
550 FROM AB_Parties_AlternateNames AS ABPA
551 JOIN Cmn_Keywords AS AlternateNamesType ON AlternateNamesType.i_KeywordID = ABPA.k_type
552 WHERE AlternateNamesType.t_name = 'DEFCODE'
553 ) AS DefendantCode ON DefendantCode.DefContactID = AB_Contacts.i_ContactID
554 WHERE Cmn_Keywords.t_Name LIKE '%Defendant'
555 AND Cas_CaseParties.i_CaseID = @UniqueID
556 AND AB_Contacts.b_Active = 1
557 AND Fin_Claim.b_Active = 1
558 ) AS Defendant
559 JOIN Fin_Claim ON Fin_Claim.i_ClaimID = Defendant.i_ClaimID
560 LEFT OUTER JOIN Cmn_Keywords AS ClaimStatus ON Fin_Claim.k_Status = ClaimStatus.i_KeywordID
561 LEFT OUTER JOIN Cmn_Keywords AS ClaimType ON Fin_Claim.k_Type = ClaimType.i_KeywordID
562 JOIN Mat_ServiceCases ON Mat_ServiceCases.i_CaseID = Defendant.i_CaseID
563 JOIN GetServiceList ON GetServiceList.S_Party = Defendant.A_Party
564 AND GetServiceList.S_ServiceID = Mat_ServiceCases.i_ServiceID
565 WHERE ClaimStatus.t_Name IN ('Informal','') OR ClaimStatus.t_Name IS NULL
566 AND (
567 ClaimType.t_Name IN ('Service Pending Test Serve','Moratorium')
568 OR ClaimType.t_Name LIKE 'Formal%'
569 OR ClaimType.t_Name LIKE 'Informal%'
570 )
571 ORDER BY Defendant.DefCode
572 END
573 ELSE IF @ObjectType = 'DiscoveryTypeForLAChart'
574 BEGIN
575 SELECT @ConcatenatedList =
576 CASE
577 WHEN DiscoveryTypes.t_Name LIKE 'DIN%' THEN @ConcatenatedList + @Delimiter + 'JCF'
578 WHEN DiscoveryTypes.t_Name LIKE 'DFINT%' THEN @ConcatenatedList + @Delimiter + 'Special Rogs'
579 WHEN DiscoveryTypes.t_Name LIKE 'DRFA%' THEN @ConcatenatedList + @Delimiter + 'RFA'
580 WHEN DiscoveryTypes.t_Name LIKE 'DRFP%' THEN @ConcatenatedList + @Delimiter + 'RFP'
581 WHEN DiscoveryTypes.t_Name LIKE 'DSINT%' THEN @ConcatenatedList + @Delimiter + 'Supp Rogs'
582 WHEN DiscoveryTypes.t_Name LIKE 'DMED%' THEN @ConcatenatedList + @Delimiter + 'Medicare Rogs'
583 WHEN DiscoveryTypes.t_Name LIKE 'DSJ%' THEN @ConcatenatedList + @Delimiter + 'Supp JCF'
584 WHEN DiscoveryTypes.t_Name LIKE 'DSR%' THEN @ConcatenatedList + @Delimiter + 'Supp RFA'
585 WHEN DiscoveryTypes.t_Name LIKE 'DSTA%' THEN @ConcatenatedList + @Delimiter + 'SACROGs Set 1'
586 WHEN DiscoveryTypes.t_Name LIKE 'DSTN%' THEN @ConcatenatedList + @Delimiter + 'SACROGs Friction'
587 WHEN DiscoveryTypes.t_Name LIKE 'GO12%' THEN @ConcatenatedList + @Delimiter + 'Def SACROGs Friction'
588 WHEN DiscoveryTypes.t_Name LIKE 'GO17%' THEN @ConcatenatedList + @Delimiter + 'Def SACROGs Set 1'
589 WHEN DiscoveryTypes.t_Name LIKE 'INT_J%' THEN @ConcatenatedList + @Delimiter + 'JCF'
590 WHEN DiscoveryTypes.t_Name LIKE 'RFA%' THEN @ConcatenatedList + @Delimiter + 'RFA'
591 WHEN DiscoveryTypes.t_Name LIKE 'RFP%' THEN @ConcatenatedList + @Delimiter + 'RFP'
592 WHEN DiscoveryTypes.t_Name LIKE 'SINT %' THEN @ConcatenatedList + @Delimiter + 'Supp Rogs'
593 WHEN DiscoveryTypes.t_Name LIKE 'DLASTD %' THEN @ConcatenatedList + @Delimiter + 'SACROGS'
594 WHEN DiscoveryTypes.t_Name LIKE 'DLASTDF%' THEN @ConcatenatedList + @Delimiter + 'Friction SACROGS'
595 WHEN DiscoveryTypes.t_Name LIKE 'DSTDF%' THEN @ConcatenatedList + @Delimiter + 'Friction SACROGS'
596 WHEN DiscoveryTypes.t_Name LIKE 'DSTDI%' THEN @ConcatenatedList + @Delimiter + 'SACROGS'
597 END
598 FROM Mat_Discovery_Types AS MDT
599 LEFT OUTER JOIN Cmn_Keywords AS DiscoveryTypes ON MDT.k_Type = DiscoveryTypes.i_KeywordID
600 WHERE DiscoveryTypes.t_Name NOT IN ('Alameda Dieden Interrogatories','Corporate Documents',
601 'DRSOD - Request for Statement of Damages','DSDT - Subpoena Duces Tecum',
602 'INT.L1 - Case Specific Interrogatories','K1 - Friction Case Specific Interrogatories',
603 'Utah Standard Interrogatories')
604 AND MDT.i_DiscoveryID = @UniqueID
605 END
606 ELSE IF @ObjectType = 'DefCodesNoClaimsStatus'
607 BEGIN
608 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + Defendant.DefCode
609 FROM (
610 SELECT DISTINCT Cas_CaseParties.i_CaseID, Cas_CaseParties.A_Party, AB_Contacts.t_Name,
611 Fin_Claim.i_ClaimID, Cas_CaseParties.i_CasePartyID AS MatterDefendantID,
612 DefendantCode.DefCode
613 FROM Cas_CaseParties
614 JOIN Cmn_Keywords ON Cas_CaseParties.k_Role = Cmn_Keywords.i_KeywordID
615 JOIN AB_Contacts ON Cas_CaseParties.A_Party = AB_Contacts.i_ContactID
616 JOIN Fin_Claim ON Cas_CaseParties.i_CaseID = Fin_Claim.i_CaseID
617 AND AB_Contacts.i_ContactID = Fin_Claim.i_DefendantAddressBookID
618 LEFT OUTER JOIN Cmn_Keywords AS ClaimStatus ON Fin_Claim.k_Status = ClaimStatus.i_KeywordID
619 JOIN (
620 SELECT ABPA.t_AlternateName As DefCode, ABPA.A_Party AS DefContactID
621 FROM AB_Parties_AlternateNames AS ABPA
622 JOIN Cmn_Keywords AS AlternateNamesType ON AlternateNamesType.i_KeywordID = ABPA.k_type
623 WHERE AlternateNamesType.t_name = 'DEFCODE'
624 ) AS DefendantCode ON DefendantCode.DefContactID = AB_Contacts.i_ContactID
625 WHERE Cmn_Keywords.t_Name LIKE '%Defendant'
626 AND Cas_CaseParties.i_CaseID = @UniqueID
627 AND AB_Contacts.b_Active = 1
628 AND Fin_Claim.b_Active = 1
629 AND ClaimStatus.t_Name IS NULL
630 ) AS Defendant
631 END
632 ELSE IF @ObjectType = 'ReleasableMedCodes'
633 BEGIN
634 --10/24/17: A distinct list of Diseases from the Results field on the Expert Reports
635 --where release = 'Yes' is used to pull only those from the complete list of
636 --diseases for the IP (Requested for the 2-5 by James Nevin).
637
638 SELECT @FieldID = i_FieldID
639 from Cmn_CustomFields
640 JOIN Cmn_CustomSections AS CS ON Cmn_CustomFields.i_SectionID = CS.i_SectionID
641 where Cmn_CustomFields.t_Label = 'Results'
642 AND CS.t_Title = 'Expert Report'
643
644 DECLARE @DiseaseFieldID Int
645 select @DiseaseFieldID = i_FieldID
646 from Cmn_CustomFields where t_Label = 'Disease Code'
647
648 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + MC.MedicalCode
649 FROM (
650 SELECT DISTINCT InnerQuery.t_Name
651 FROM (
652 SELECT DISTINCT Cmn_Keywords.t_Name, CSS.i_RecordUniqueID
653 FROM Cmn_CustomData_Associations AS CDA
654 JOIN Cmn_CustomSectionsSelected AS CSS ON CSS.i_SelectedSectionID = CDA.i_SelectedSectionID
655 JOIN Cmn_Keywords ON CDA.i_RecordUniqueID = Cmn_Keywords.i_KeywordID
656 WHERE CDA.i_FieldID = @FieldID
657 ) AS InnerQuery
658 WHERE InnerQuery.i_RecordUniqueID IN (
659 --Pull list of documentids where release = Yes
660 SELECT Doc_Documents.i_DocumentID
661 FROM Doc_Documents
662 JOIN Doc_DocumentContacts AS DC ON Doc_Documents.i_DocumentID = DC.i_DocumentID
663 JOIN Cmn_Keywords ON Doc_Documents.k_Type = Cmn_Keywords.i_KeywordID
664 JOIN Cmn_KeywordLists AS KL ON Cmn_Keywords.i_KeywordListID = KL.i_KeywordListID
665 JOIN (
666 Select t_Label AS Label, Cmn_Keywords.t_Name AS Data, Cmn_CustomData.i_RecordUniqueID AS UniqueID
667 From Cmn_CustomData
668 JOIN Cmn_CustomFields ON Cmn_CustomData.i_FieldID = Cmn_CustomFields.i_FieldID
669 JOIN Cmn_Keywords ON Cmn_CustomData.t_Data = Cmn_Keywords.i_KeywordID
670 WHERE Cmn_CustomFields.i_FieldID = (select i_FieldID
671 from Cmn_CustomFields where t_Label = 'Release?')
672 AND Cmn_Keywords.t_Name = 'Yes'
673 ) AS Release ON Doc_Documents.i_DocumentID = Release.UniqueID
674 WHERE KL.t_KeywordListName = 'DocumentTypes'
675 AND DC.i_ContactAddressBookID = @UniqueID
676 )
677 ) AS FinalDiseaseList --list of distinct diseases from all releasable expert reports for the client
678 LEFT OUTER JOIN BP_TempObjects.dbo.BPv3_MedicalCodes AS MC ON FinalDiseaseList.t_Name = MC.Disease
679 --WHERE EXISTS ( --these disease codes are pulled from the client > medical > disease codes
680 WHERE FinalDiseaseList.t_Name IN (
681 SELECT dbo.Cmn_CustomFields_FormatCustomData(@DiseaseFieldID, DiseaseCodeField.t_Data, 0) as DiseaseCode
682 FROM Cmn_CustomSectionsSelected AS CSS
683 JOIN Cmn_CustomData_Associations AS CDA ON CSS.i_SelectedSectionID = CDA.i_SelectedSectionID
684 JOIN Cmn_Keywords ON CDA.i_RecordUniqueID = Cmn_Keywords.i_KeywordID
685 LEFT OUTER JOIN (
686 SELECT i_SelectedSectionID, t_Data
687 FROM Cmn_CustomData
688 WHERE i_FieldID = @DiseaseFieldID
689 ) DiseaseCodeField ON CSS.i_SelectedSectionID = DiseaseCodeField.i_SelectedSectionID
690 WHERE CSS.i_SectionID = dbo.BPv3_F_GetSectionID ('PartyMedical', 'Disease Codes')
691 AND CSS.i_RecordUniqueID = @UniqueID
692 )
693 END
694 ELSE IF @ObjectType = 'DiseasesCodesForCPORogs' --Added 10/26/17 for BPv3_SP_GetCPOInterrogInfo for WMAC CPO Rogs
695 BEGIN
696 SELECT @FieldID = i_FieldID
697 FROM Cmn_CustomFields WHERE t_Label = 'Disease Code'
698 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + InnerQuery.t_Name
699 FROM
700 (
701 SELECT DISTINCT Cmn_Keywords.t_Name
702 FROM Cmn_CustomData_Associations
703 INNER JOIN Cmn_CustomSectionsSelected
704 ON Cmn_CustomSectionsSelected.i_SelectedSectionID = Cmn_CustomData_Associations.i_SelectedSectionID
705 JOIN Cmn_Keywords ON Cmn_CustomData_Associations.i_RecordUniqueID = Cmn_Keywords.i_KeywordID
706 WHERE Cmn_CustomData_Associations.i_FieldID = @FieldID
707 AND Cmn_CustomSectionsSelected.i_RecordUniqueID = @UniqueID
708 AND Cmn_Keywords.t_Name IN (
709 'Anal Cancer','Asbestosis','Asbestosis on CT only',
710 'Buccal Cancer','Colon Cancer','Esophageal Cancer',
711 'Kidney/Renal Cancer','Laryngeal Cancer','Lung Cancer','Lymphoma',
712 'Mesothelioma','Other Asbestos Related Cancer',
713 'Pharyngeal Cancer','Pleural Disease','Rectal Cancer',
714 'Stomach Cancer','Tongue Cancer','Tonsil Cancer','Vocal Cord Cancer')
715 ) AS InnerQuery
716 END
717 ELSE IF @ObjectType = 'WitnessesPerHistoryID' --Added 11/17/17 for BPv3_SP_ClientInfoForShips (Client/Witness Search by Employer/Job Site)
718 BEGIN
719 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + InnerQuery.WitnessInfo
720 FROM
721 (
722 SELECT AB_Contacts.t_Name +
723 CASE WHEN ContactAddress.AddressInfo IS NOT NULL THEN '|' + ContactAddress.AddressInfo ELSE '' END +
724 CASE WHEN IPDefaultPhone.t_Number IS NOT NULL THEN '|' + IPDefaultPhone.t_Number ELSE '' END AS WitnessInfo
725 From Cmn_CustomData
726 JOIN Cmn_CustomFields ON Cmn_CustomData.i_FieldID = Cmn_CustomFields.i_FieldID
727 LEFT OUTER JOIN AB_Contacts ON Cmn_CustomData.t_Data = AB_Contacts.i_ContactID
728 LEFT OUTER JOIN (
729 SELECT CA.i_ContactID, CA.i_AddressID,
730 CASE WHEN LEN(CA.t_Line2) = 0 AND LEN(CA.t_Line1) > 0
731 THEN CA.t_Line1 + '|' + CA.t_City + ', ' + IPKeyState.t_Name + ' ' + CA.t_PostalCode
732 WHEN LEN(CA.t_Line2) > 0 AND LEN(CA.t_Line1) > 0
733 THEN CA.t_Line1 + '|' + CA.t_Line2 + '|' + CA.t_City + ', ' + IPKeyState.t_Name + ' ' + CA.t_PostalCode
734 END AS AddressInfo
735 FROM AB_Contacts_Addresses AS CA
736 LEFT OUTER JOIN Cmn_Keywords AS IPKeyState ON CA.k_State = IPKeyState.i_KeywordID
737 WHERE CA.b_Active = 1
738 ) AS ContactAddress ON ContactAddress.i_ContactID = AB_Contacts.i_ContactID
739 LEFT OUTER JOIN (
740 SELECT CAM.i_AddressID
741 FROM AB_Contacts_Addresses_Map AS CAM
742 WHERE CAM.b_DefaultMailing = 1
743 ) AS ContactAddressMap ON ContactAddress.i_AddressID = ContactAddressMap.i_AddressID
744 LEFT OUTER JOIN (
745 SELECT * FROM AB_Contacts_VoicePhones
746 ) AS IPDefaultPhone ON AB_Contacts.i_ContactID = IPDefaultPhone.i_ContactID
747 WHERE Cmn_CustomFields.i_FieldID = (SELECT i_FieldID
748 FROM Cmn_CustomFields WHERE t_Label = 'Witness'
749 AND Cmn_CustomFields.i_SectionID = dbo.BPv3_F_GetSectionID ('Event','Witness'))
750 AND Cmn_CustomData.t_Data LIKE '%[0-9]%'
751 AND Cmn_CustomData.i_RecordUniqueID = @UniqueID
752 ) AS InnerQuery
753 END
754 ELSE IF @ObjectType = 'CounselNameAddress' --EPerlstein: Added 5/4/2018 for CMPVIII-331 Final Action report
755 BEGIN
756 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + InnerQuery.Counsel
757 FROM
758 (
759 SELECT DISTINCT CounselAndAddress.Counsel, CL.A_Party
760 FROM AB_Parties_ContactLists AS CL
761 JOIN AB_Parties_ContactLists_Contacts AS CLC ON CL.i_ContactListID = CLC.i_ContactListID
762 LEFT OUTER JOIN (
763 SELECT DISTINCT AB_Contacts.i_ContactID, AB_Contacts.t_Name +
764 CASE WHEN ContactAddress.AddressInfo IS NOT NULL THEN '|' + ContactAddress.AddressInfo ELSE '' END AS Counsel
765 FROM AB_Contacts
766 LEFT OUTER JOIN (
767 SELECT CA.i_ContactID, CA.i_AddressID,
768 CASE WHEN LEN(CA.t_Line2) = 0 AND LEN(CA.t_Line1) > 0
769 THEN CA.t_Line1 + '|' + CA.t_City + ', ' + IPKeyState.t_Name + ' ' + CA.t_PostalCode
770 WHEN LEN(CA.t_Line2) > 0 AND LEN(CA.t_Line1) > 0
771 THEN CA.t_Line1 + '|' + CA.t_Line2 + '|' + CA.t_City + ', ' + IPKeyState.t_Name + ' ' + CA.t_PostalCode
772 END AS AddressInfo
773 FROM AB_Contacts_Addresses AS CA
774 LEFT OUTER JOIN Cmn_Keywords AS IPKeyState ON CA.k_State = IPKeyState.i_KeywordID
775 WHERE CA.b_Active = 1
776 ) AS ContactAddress ON ContactAddress.i_ContactID = AB_Contacts.i_ContactID
777 LEFT OUTER JOIN (
778 SELECT CAM.i_AddressID
779 FROM AB_Contacts_Addresses_Map AS CAM
780 WHERE CAM.b_DefaultMailing = 1
781 ) AS ContactAddressMap ON ContactAddress.i_AddressID = ContactAddressMap.i_AddressID
782 ) AS CounselAndAddress ON CLC.A_Contact = CounselAndAddress.i_ContactID
783 LEFT OUTER JOIN Cmn_Keywords AS Title ON CLC.k_Title = Title.i_KeywordID
784 WHERE (Title.t_Name = 'Settlement Counsel' OR Title.t_Name = 'Counsel')
785 AND Title.t_Name IS NOT NULL
786 AND CL.b_Default = 1
787 AND CL.A_Party = @UniqueID
788 ) AS InnerQuery
789 END
790 --EPerlstein 6/25/18: For use in Overdue Reponse Letters (CMPVIII-309)
791 ELSE IF @ObjectType = 'DiscoveryTypesForOverdueLetters'
792 BEGIN
793 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + InnerQuery.DiscoveryTypes
794 FROM
795 (
796 SELECT CASE WHEN SUBSTRING(Cmn_Keywords.t_Name, CHARINDEX(' - ',Cmn_Keywords.t_Name) + 2, LEN(Cmn_Keywords.t_Name) - (CHARINDEX(' - ',Cmn_Keywords.t_Name))) LIKE '%to Def.'
797 THEN LTRIM(RTRIM(SUBSTRING(Cmn_Keywords.t_Name, CHARINDEX(' - ',Cmn_Keywords.t_Name) + 2, LEN(Cmn_Keywords.t_Name) - (CHARINDEX(' - ',Cmn_Keywords.t_Name)) - 9)))
798 ELSE LTRIM(RTRIM(SUBSTRING(Cmn_Keywords.t_Name, CHARINDEX(' - ',Cmn_Keywords.t_Name) + 2, LEN(Cmn_Keywords.t_Name) - (CHARINDEX(' - ',Cmn_Keywords.t_Name)))))
799 END AS DiscoveryTypes
800 FROM Mat_Discovery_Types
801 JOIN Cmn_Keywords ON Mat_Discovery_Types.k_Type = Cmn_Keywords.i_KeywordID
802 WHERE Mat_Discovery_Types.i_DiscoveryID = @UniqueID
803 ) AS InnerQuery
804 END
805 --EPerlstein 7/5/18: Added to concatenate case types for all client's WC cases
806 ELSE IF @ObjectType = 'WCCaseTypes'
807 BEGIN
808 SELECT @ConcatenatedList = @ConcatenatedList + @Delimiter + CaseTypes.t_Name
809 FROM Cas_Cases
810 JOIN (
811 SELECT Cas_CaseNumbers.i_CaseID, Cas_CaseNumbers.t_CaseNumber AS MatterNumber
812 FROM Cas_CaseNumbers
813 WHERE Cas_CaseNumbers.b_Primary = 1
814 ) AS CaseNumbers ON Cas_Cases.i_CaseID = CaseNumbers.i_CaseID
815 JOIN (
816 SELECT Cas_CaseParties.i_CaseID, Cas_CaseParties.A_Party, Cas_CaseParties.b_Primary,
817 AB_Contacts.t_Name AS IPName, AB_Contacts.d_DateOfDeath, AB_Contacts.b_Deceased
818 FROM Cas_CaseParties
819 JOIN AB_Contacts ON Cas_CaseParties.A_Party = AB_Contacts.i_ContactID
820 JOIN Cmn_Keywords ON Cas_CaseParties.k_Role = Cmn_Keywords.i_KeywordID
821 WHERE Cmn_Keywords.t_Name = 'Lead Injured Party'
822 ) AS InjuredParty ON Cas_Cases.i_CaseID = InjuredParty.i_CaseID
823 JOIN Cas_CaseTypes ON Cas_Cases.i_CaseID = Cas_CaseTypes.i_CaseID
824 JOIN Cmn_Keywords AS CaseTypes ON Cas_CaseTypes.k_CaseType = CaseTypes.i_KeywordID
825 WHERE InjuredParty.A_Party = @Unique