WITH IV_CTE ([OutSide Counsel Docket Number], [IV Docket No.], [Country], [Case Type], [Filing Type], [Status]
			, [Priority Date], [Parent Country], [Parent Filing Date], [Parent Number], [Patent Number]
			, [Publication Number], [Application Date], [Application Number], [Grant Date]
			, [Publication Date], [Expiration Date], [Owners], [Title])
AS
(
	SELECT
	C.IRN AS [OutSide Counsel Docket Number],
	CN.REFERENCENO AS [IV Docket No.],
	Country = CASE WHEN C.COUNTRYCODE = 'PCT' THEN 'WO' ELSE C.COUNTRYCODE END,
	[Case Type] = CASE WHEN C.CASECATEGORY = 'D' THEN 'Divisional' ELSE NULL END,
	[Filing Type] =
	CASE
		WHEN C.CASECATEGORY = 'K' AND C.COUNTRYCODE = 'EP' THEN 'PCT/EPC'
		WHEN C.CASECATEGORY = 'K' AND C.COUNTRYCODE <> 'EP' THEN 'PCT/National'
		WHEN C.CASECATEGORY = 'X' THEN 'EP/National'
		WHEN C.CASECATEGORY = 'N' AND C.COUNTRYCODE = 'EP' THEN 'EPC'
		WHEN C.CASECATEGORY = 'N' AND C.COUNTRYCODE <> 'EP' THEN 'National'
		WHEN C.CASECATEGORY = 'R' THEN 'National'
		WHEN C.CASECATEGORY = 'D' AND C.COUNTRYCODE = 'EP' THEN 'EPC'
		WHEN C.CASECATEGORY = 'D' AND C.COUNTRYCODE <> 'EP' THEN 'National'
	END,
	[Status] = ISNULL(
	CASE
		WHEN CT.SHORTTEXT IS NOT NULL THEN CT.SHORTTEXT
		WHEN C.STATUSCODE = 8 THEN 'Filed'
		WHEN C.STATUSCODE = -210 THEN 'Granted'
		WHEN C.STATUSCODE = -210 AND C.COUNTRYCODE = 'EP' THEN 'Inactive'
		WHEN C.STATUSCODE = -216 THEN 'Abandoned'
		ELSE 'Inactive'
	END, 'N/A'),
	[Priority Date] = 
	CASE
		WHEN CE.EVENTDATE IS NULL THEN CONVERT(VARCHAR, CE4.EVENTDATE, 101)
		ELSE CONVERT(VARCHAR, CE.EVENTDATE, 101)
	END,
	[Parent Country] =
	CASE
		WHEN C.CASECATEGORY = 'D' THEN CASE WHEN C.COUNTRYCODE = 'PCT' THEN 'WO' ELSE C.COUNTRYCODE END
		WHEN C.CASECATEGORY = 'K' THEN 'WO'
		WHEN C.CASECATEGORY = 'N' THEN [RC2].COUNTRYCODE
		WHEN C.CASECATEGORY = 'X' THEN 'EP'
		WHEN C.CASECATEGORY = 'R' THEN 'EP'
	END,
	[Parent Filing Date] =
	CASE
		WHEN C.CASECATEGORY = 'D' THEN CONVERT(VARCHAR, CE2.EVENTDATE, 101)
		WHEN C.CASECATEGORY = 'K' THEN CONVERT(VARCHAR, CE7.EVENTDATE, 101)
		WHEN C.CASECATEGORY = 'N' THEN CONVERT(VARCHAR, CE.EVENTDATE, 101)
		WHEN C.CASECATEGORY = 'X' THEN CONVERT(VARCHAR, CE4.EVENTDATE, 101)
		WHEN C.CASECATEGORY = 'R' THEN CONVERT(VARCHAR, CE2.EVENTDATE, 101)
	END,
	[Parent Number] =
	CASE
		WHEN C.CASECATEGORY = 'D' THEN O1.OFFICIALNUMBER
		WHEN C.CASECATEGORY = 'K' THEN O2.OFFICIALNUMBER
		WHEN C.CASECATEGORY = 'N' THEN O3.OFFICIALNUMBER
		WHEN C.CASECATEGORY = 'X' THEN O4.OFFICIALNUMBER
		WHEN C.CASECATEGORY = 'R' THEN O1.OFFICIALNUMBER
	END,
	[Patent Number] = 
	CASE
		WHEN C.COUNTRYCODE = 'DE' THEN ISNULL(CASE WHEN LEFT(RIGHT(O7.OFFICIALNUMBER, 3), 1) = '-' THEN SUBSTRING(O7.OFFICIALNUMBER, 1, (LEN(O7.OFFICIALNUMBER) - 3)) ELSE O7.OFFICIALNUMBER END, '')
		ELSE O5.OFFICIALNUMBER
	END,
	[Publication Number] = 
	CASE
		WHEN C.CASECATEGORY = 'X' THEN O8.OFFICIALNUMBER
		WHEN O6.OFFICIALNUMBER IS NULL AND O9.OFFICIALNUMBER IS NOT NULL THEN O9.OFFICIALNUMBER
		WHEN O6.OFFICIALNUMBER IS NULL AND O9.OFFICIALNUMBER IS NULL THEN O8.OFFICIALNUMBER
		ELSE O6.OFFICIALNUMBER
	END,
	[Application Date] = 
	CASE
		WHEN C.COUNTRYCODE = 'HK' THEN CONVERT(VARCHAR, CE9.EVENTDATE, 101)
		ELSE CONVERT(VARCHAR, CE4.EVENTDATE, 101)
	END,
	[Application Number] = O4.OFFICIALNUMBER,
	[Grant Date] = CONVERT(VARCHAR, CE5.EVENTDATE, 101),
	[Publication Date] = 
	CASE
		WHEN C.CASECATEGORY = 'X' AND CE6.EVENTDATE IS NULL THEN CONVERT(VARCHAR, CE8.EVENTDATE, 101)
		ELSE CONVERT(VARCHAR, CE6.EVENTDATE, 101)
	END,
	[Expiration Date] = CONVERT(VARCHAR, DATEADD(YY, 20, CE4.EVENTDATE), 101),
	--[Owners] = STUFF((SELECT TOP 100 PERCENT ',' + n.NAME FROM [NAME] n INNER JOIN CASENAME cn ON cn.NAMENO = n.NAMENO WHERE cn.CASEID = c.CASEID AND CN.NAMETYPE = 'O' ORDER BY n.NAMENO FOR XML PATH('')),1,1,'')
	[Owners] = TC.DESCRIPTION,
	[Title] = C.TITLE
	FROM CASES C
	INNER JOIN CASENAME CN ON CN.CASEID = C.CASEID AND CN.NAMETYPE = 'I' AND CN.SEQUENCE = 0
	INNER JOIN NAME N ON N.NAMENO = CN.NAMENO
	LEFT OUTER JOIN CASECATEGORY CC ON CC.CASETYPE = C.CASETYPE AND CC.CASECATEGORY = C.CASECATEGORY
	LEFT OUTER JOIN STATUS S ON S.STATUSCODE = C.STATUSCODE
	LEFT OUTER JOIN CASETEXT CT ON CT.CASEID = C.CASEID AND CT.TEXTTYPE = 'IV'
	LEFT OUTER JOIN CASEEVENT CE ON CE.CASEID = C.CASEID AND CE.EVENTNO = -1
	LEFT OUTER JOIN CASEEVENT CE2 ON CE2.CASEID = C.CASEID AND CE2.EVENTNO = -220
	LEFT OUTER JOIN CASEEVENT CE3 ON CE3.CASEID = C.CASEID AND CE3.EVENTNO = -232
	LEFT OUTER JOIN CASEEVENT CE4 ON CE4.CASEID = C.CASEID AND CE4.EVENTNO = -21
	LEFT OUTER JOIN CASEEVENT CE5 ON CE5.CASEID = C.CASEID AND CE5.EVENTNO = -8
	LEFT OUTER JOIN CASEEVENT CE6 ON CE6.CASEID = C.CASEID AND CE6.EVENTNO = -11879
	LEFT OUTER JOIN CASEEVENT CE7 ON CE7.CASEID = C.CASEID AND CE7.EVENTNO = 232
	LEFT OUTER JOIN CASEEVENT CE9 ON CE9.CASEID = C.CASEID AND CE9.EVENTNO = -4
	LEFT OUTER JOIN OFFICIALNUMBERS O1 ON O1.CASEID = C.CASEID AND O1.NUMBERTYPE = 'B' AND O1.ISCURRENT = 1
	LEFT OUTER JOIN OFFICIALNUMBERS O2 ON O2.CASEID = C.CASEID AND O2.NUMBERTYPE = 'I' AND O2.ISCURRENT = 1
	LEFT OUTER JOIN OFFICIALNUMBERS O3 ON O3.CASEID = C.CASEID AND O3.NUMBERTYPE = 'T' AND O3.ISCURRENT = 1
	LEFT OUTER JOIN OFFICIALNUMBERS O4 ON O4.CASEID = C.CASEID AND O4.NUMBERTYPE = 'A' AND O4.ISCURRENT = 1
	LEFT OUTER JOIN OFFICIALNUMBERS O5 ON O5.CASEID = C.CASEID AND O5.NUMBERTYPE = 'R' AND O5.ISCURRENT = 1
	LEFT OUTER JOIN OFFICIALNUMBERS O6 ON O6.CASEID = C.CASEID AND O6.NUMBERTYPE = 'P' AND O6.ISCURRENT = 1
	LEFT OUTER JOIN OFFICIALNUMBERS O7 ON O7.CASEID = C.CASEID AND O7.NUMBERTYPE = 'E' AND O7.ISCURRENT = 1
	LEFT OUTER JOIN OFFICIALNUMBERS O9 ON O9.CASEID = C.CASEID AND O9.NUMBERTYPE = 'V' AND O9.ISCURRENT = 1
	LEFT OUTER JOIN RELATEDCASE RC1 ON RC1.CASEID = C.CASEID AND RC1.RELATIONSHIP = 'PAR'
	LEFT OUTER JOIN RELATEDCASE [RC2] ON RC2.CASEID = C.CASEID AND RC2.RELATIONSHIP = 'BAS' 
	LEFT OUTER JOIN RELATEDCASE RC3 ON RC3.CASEID = C.CASEID AND RC3.RELATIONSHIP = 'EPP'
	LEFT OUTER JOIN CASES C2 ON C2.CASEID = RC3.RELATEDCASEID
	LEFT OUTER JOIN CASEEVENT CE8 ON CE8.CASEID = C2.CASEID AND CE8.EVENTNO = -11879
	LEFT OUTER JOIN OFFICIALNUMBERS O8 ON O8.CASEID = C2.CASEID AND O8.NUMBERTYPE = 'P' AND O8.ISCURRENT = 1
	LEFT OUTER JOIN TABLEATTRIBUTES TA ON TA.GENERICKEY = C.CASEID AND TA.PARENTTABLE = 'CASES' AND TA.TABLETYPE = 5008
	LEFT OUTER JOIN TABLECODES TC ON TC.TABLECODE = TA.TABLECODE
	WHERE N.NAMECODE = 'I1889400'
)
SELECT [OutSide Counsel Docket Number], [IV Docket No.], Country, [Case Type], [Filing Type], [Status]
		, [Priority Date], [Parent Country], [Parent Filing Date], [Parent Number], [Patent Number]
		, [Publication Number], [Application Date], [Application Number], [Grant Date]
		, [Publication Date], [Expiration Date], [Owners], [Title]
FROM IV_CTE
WHERE [Status] <> 'REMOVE FROM REPORT'
AND LEFT([OutSide Counsel Docket Number], 1) <> 'G'
ORDER BY [IV Docket No.] ASC