ALTER VIEW Query.Software AS
SELECT
	ILP.lintComputerNodeId AS [ComputerNodeId],
	TN.lintParentId AS [ParentNodeId], 
	P.txtProductName AS [Product],
	P.lintProductCategoryId AS [Category],
	CASE WHEN DL.iLicenseStatusId IN (1,4) THEN 0 ELSE 1 END AS [NeedsLicense],
	IH.dteInstallDetected AS [FirstInstallationDetected], 
	IH.dteInstalled AS [Installed],
	TD.dteCreated AS [LastDetectionDateTime],
	IH.txtDetProductVersion AS [LastDetectedProductVersion],
	IH.txtDetProductName AS [LastDetectedProductName],
	SLPC.txtInventoryNum AS [LicenseInventoryNumber],
	SLPC.ActivationKey AS [LicenseActivationKey],
	COALESCE(ass.txtId, d.txtIdNumber2) AS [LicenseInvoiceNumber],
	SLPC.txtCertificateNo AS [LicenseCertificateNumber],
	(case P.lintProductCategoryId when 5 then null else (cast(case when IH.dteInstalled IS NULL AND ILP.lintLicProductId is null then 0 else 1 end as bit)) end) [Authorized],
	IH.txtDetCDKey AS [CDKey],
	IH.txtMemo AS [Description],
	LH.txtLicName AS [LicenceName],
	TC.txtName AS [Publisher],
	(case ILP.lintProductPackFromId WHEN NULL THEN 1 ELSE 0 end) AS [SoftwarePack],
	P.lintProductTypeId AS [Type],
	IH.liInstalledPersonId AS [User],
	SLPC.txtMemo AS [Comment]
FROM vComputer AS PC
	INNER JOIN vSwInstLicProfile_ProductComputer AS ILP ON ILP.lintComputerNodeId=PC.intNodeId
	LEFT JOIN (SELECT MAX(dteCreated) AS dteCreated, lintComputerNodeId FROM tblDetect GROUP BY lintComputerNodeId) AS TD ON TD.lintComputerNodeId = PC.intNodeId
	LEFT JOIN vProduct AS P ON P.intProductId=ILP.liProductId AND ILP.liProductId IS NOT NULL
	LEFT JOIN tblInstHist AS IH ON IH.intInstHistId=ILP.intInstHistId
	LEFT JOIN vSwLic_ProductComputer AS SLPC ON SLPC.intLicHistId=ILP.intLicHistId AND SLPC.NodeId=PC.intNodeId AND SLPC.lintProductId=ILP.liProductId
	LEFT JOIN tblCompany AS TC ON TC.intCompanyId = P.lintProducerCompanyId
	LEFT JOIN tblLicHist AS LH ON LH.intLicHistId=ILP.intLicHistId
	LEFT JOIN tblDict AS DL ON DL.iLicenseStatusId=ILP.iLicStatusId AND DL.iLicenseStatusId IS NOT NULL
	LEFT JOIN tblNode AS TN ON TN.intNodeId = ILP.lintComputerNodeId
	LEFT JOIN
	(
		SELECT lintLicHistId, MIN(lintAtomId) AS lintAtomId FROM tblLicHistAtom 
		GROUP BY lintLicHistId
	) AS lha ON lha.lintLicHistId=ILP.intLicHistId
	LEFT JOIN tblAsset AS ass ON ass.lintAtomId=lha.lintAtomId
	LEFT JOIN tblDocument AS d ON d.lintAtomId=lha.lintAtomId and d.lintDocumentKindId=1
WHERE PC.bolHidden=0
	AND PC.bolScrapped=0
	AND IH.dteUninstalled IS NULL