IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'Cireson_AssetManagement_GetSoftwareAssetDetails'
)
DROP PROCEDURE dbo.Cireson_AssetManagement_GetSoftwareAssetDetails
GO
Select
S.Name,
S.CiresonSoftwareAssetDimKey,
S.[Version],
S.[Description],
S.Manufacturer,
S.IsBundle,
S.SoftwarePattern,
S.VersionPattern,
S.ExclusionPattern,
S.CountAllMatches,
S.IsUnlimited,
S.InstallCount,
S.IsOS,
S.AvailableCount,
S.PurchaseCount,
S.SoftwareMeteringUsageCount,
S.LastUpdated,
S.WarningThresholdPercent,
S.ExpectedDate,
S.ReceivedDate,
S.Cost,
SoftwareAssetStatusDS.DisplayName as [Status],
SoftwareAssetLicenceStatusDS.DisplayName as [LicenceStatus],
SoftwareAssetTypeDS.DisplayName as [Type],
PrimaryUser.DisplayName as [PrimaryUser],
Custodian.DisplayName as [Custodian],
PrimaryUser.UserDimKey as [PrimaryUserUserDimKey],
Custodian.UserDimKey as [CustodianUserDimKey],
Location.DisplayName as [Location],
Organisation.DisplayName as [Organisation],
Vendor.DisplayName as [Vendor],
SupportContract.DisplayName as [SupportContract],
CostCenter.DisplayName as [CostCenter],
PurchaseOrder.DisplayName as [PurchaseOrder],
Invoice.DisplayName as [Invoice],
SupportContract.ContractStartDate as [ContractStartDate],
SupportContract.ContractEndDate as [ContractEndDate],
SupportContractStatusDS.DisplayName as [SupportContractStatus],
ManufacturerDS.DisplayName as [ManufacturerEnum],
S.TotalPurchaseCount,
S.DowngradeLicences,
S.DowngradesAvailable,
S.UpgradeLicences,
S.UpgradesAvailable,
S.AuthorizedComputerCount,
S.UnauthorizedComputerCount,
S.AuthorizedInstallCount,
S.IsNamed,
S.CountLicenceSeats,
CPUModeDS.DisplayName as [CPUMode]
from
dbo.CiresonSoftwareAssetDimvw S
LEFT OUTER JOIN dbo.CiresonSoftwareAssetStatusvw SoftwareAssetStatusEnum
ON S.SoftwareAssetStatus_CiresonSoftwareAssetStatusId = SoftwareAssetStatusEnum.CiresonSoftwareAssetStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw SoftwareAssetStatusDS
ON SoftwareAssetStatusEnum.EnumTypeId = SoftwareAssetStatusDS.BaseManagedEntityId
AND ((SoftwareAssetStatusDS.LanguageCode = @LanguageCode AND SoftwareAssetStatusDS.DisplayName != null) OR SoftwareAssetStatusDS.LanguageCode = 'ENU')
LEFT OUTER JOIN dbo.CiresonSoftwareAssetCPUModevw CPUModeEnum
ON S.CPUMode_CiresonSoftwareAssetCPUModeId = CPUModeEnum.CiresonSoftwareAssetCPUModeId
LEFT OUTER JOIN dbo.DisplayStringDimvw CPUModeDS
ON CPUModeEnum.EnumTypeId = CPUModeDS.BaseManagedEntityId
AND ((CPUModeDS.LanguageCode = @LanguageCode AND CPUModeDS.DisplayName != null) OR CPUModeDS.LanguageCode = 'ENU')
LEFT OUTER JOIN dbo.CiresonSoftwareAssetTypevw SoftwareAssetTypeEnum
ON S.SoftwareAssetType_CiresonSoftwareAssetTypeId = SoftwareAssetTypeEnum.CiresonSoftwareAssetTypeId
LEFT OUTER JOIN dbo.DisplayStringDimvw SoftwareAssetTypeDS
ON SoftwareAssetTypeEnum.EnumTypeId = SoftwareAssetTypeDS.BaseManagedEntityId
AND ((SoftwareAssetTypeDS.LanguageCode = @LanguageCode AND SoftwareAssetTypeDS.DisplayName != null) OR SoftwareAssetTypeDS.LanguageCode = 'ENU')
LEFT OUTER JOIN dbo.CiresonSoftwareAssetLicenceStatusvw SoftwareAssetLicenceStatusEnum
ON S.LicenceStatus_CiresonSoftwareAssetLicenceStatusId = SoftwareAssetLicenceStatusEnum.CiresonSoftwareAssetLicenceStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw SoftwareAssetLicenceStatusDS
ON SoftwareAssetLicenceStatusEnum.EnumTypeId = SoftwareAssetLicenceStatusDS.BaseManagedEntityId
AND ((SoftwareAssetLicenceStatusDS.LanguageCode = @LanguageCode AND SoftwareAssetLicenceStatusDS.DisplayName != null) OR SoftwareAssetLicenceStatusDS.LanguageCode = 'ENU')
LEFT OUTER JOIN dbo.CiresonSoftwareAssetRelatesToPrimaryUserFactvw PU
ON PU.CiresonSoftwareAssetDimKey = S.CiresonSoftwareAssetDimKey
AND PU.DeletedDate is null
LEFT OUTER JOIN
dbo.UserDimvw PrimaryUser
ON PrimaryUser.UserDimKey = PU.SoftwareAssetHasPrimaryUser_UserDimKey
AND PrimaryUser.IsDeleted = 0
LEFT OUTER JOIN dbo.CiresonSoftwareAssetRelatesToOwnedByUserFactvw OU
ON OU.CiresonSoftwareAssetDimKey = S.CiresonSoftwareAssetDimKey
AND OU.DeletedDate is null
LEFT OUTER JOIN
dbo.UserDimvw Custodian
ON Custodian.UserDimKey = OU.ConfigItemOwnedByUser_UserDimKey
AND Custodian.IsDeleted = 0
LEFT OUTER JOIN dbo.CiresonSoftwareAssetRelatesToOrganizationFactvw Org
ON Org.CiresonSoftwareAssetDimKey = S.CiresonSoftwareAssetDimKey
AND Org.DeletedDate is null
LEFT OUTER JOIN
dbo.CiresonOrganizationDimvw Organisation
ON Organisation.CiresonOrganizationDimKey = Org.SoftwareAssetHasOrganization_CiresonOrganizationDimKey
AND Organisation.IsDeleted = 0
LEFT OUTER JOIN dbo.CiresonSoftwareAssetRelatesToLocationFactvw Loc
ON Loc.CiresonSoftwareAssetDimKey = S.CiresonSoftwareAssetDimKey
AND Loc.DeletedDate is null
LEFT OUTER JOIN
dbo.CiresonLocationDimvw Location
ON Location.CiresonLocationDimKey = Loc.SoftwareAssetHasLocation_CiresonLocationDimKey
AND Location.IsDeleted = 0
LEFT OUTER JOIN dbo.CiresonSoftwareAssetRelatesToVendorFactvw Vend
ON Vend.CiresonSoftwareAssetDimKey = S.CiresonSoftwareAssetDimKey
AND Vend.DeletedDate is null
LEFT OUTER JOIN
dbo.CiresonVendorDimvw Vendor
ON Vendor.CiresonVendorDimKey = Vend.SoftwareAssetHasVendor_CiresonVendorDimKey
AND Vendor.IsDeleted = 0
LEFT OUTER JOIN dbo.CiresonSoftwareAssetRelatesToSupportContractFactvw Con
ON Con.CiresonSoftwareAssetDimKey = S.CiresonSoftwareAssetDimKey
AND Con.DeletedDate is null
LEFT OUTER JOIN
dbo.CiresonSupportContractDimvw SupportContract
ON SupportContract.CiresonSupportContractDimKey = Con.SoftwareAssetHasSupportContract_CiresonSupportContractDimKey
AND SupportContract.IsDeleted = 0
LEFT OUTER JOIN dbo.CiresonSoftwareAssetRelatesToCostCenterFactvw CC
ON CC.CiresonSoftwareAssetDimKey = S.CiresonSoftwareAssetDimKey
AND CC.DeletedDate is null
LEFT OUTER JOIN
dbo.CiresonCostCenterDimvw CostCenter
ON CostCenter.CiresonCostCenterDimKey = CC.SoftwareAssetHasCostCenter_CiresonCostCenterDimKey
AND CostCenter.IsDeleted = 0
LEFT OUTER JOIN dbo.CiresonSoftwareAssetRelatesToPurchaseOrderFactvw PO
ON PO.CiresonSoftwareAssetDimKey = S.CiresonSoftwareAssetDimKey
AND PO.DeletedDate is null
LEFT OUTER JOIN
dbo.CiresonPurchaseOrderDimvw PurchaseOrder
ON PurchaseOrder.CiresonPurchaseOrderDimKey = PO.SoftwareAssetHasPurchaseOrder_CiresonPurchaseOrderDimKey
AND PurchaseOrder.IsDeleted = 0
LEFT OUTER JOIN dbo.CiresonSoftwareAssetRelatesToInvoiceFactvw INV
ON INV.CiresonSoftwareAssetDimKey = S.CiresonSoftwareAssetDimKey
AND INV.DeletedDate is null
LEFT OUTER JOIN
dbo.CiresonInvoiceDimvw Invoice
ON Invoice.CiresonInvoiceDimKey = INV.SoftwareAssetHasInvoice_CiresonInvoiceDimKey
AND Invoice.IsDeleted = 0
LEFT OUTER JOIN dbo.CiresonSupportContractContractStatusvw SupportContractStatusEnum
ON SupportContract.ContractStatus_CiresonSupportContractContractStatusId = SupportContractStatusEnum.CiresonSupportContractContractStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw SupportContractStatusDS
ON SupportContractStatusEnum.EnumTypeId = SupportContractStatusDS.BaseManagedEntityId
AND ((SupportContractStatusDS.LanguageCode = @LanguageCode AND SupportContractStatusDS.DisplayName != null) OR SupportContractStatusDS.LanguageCode = 'ENU')
LEFT OUTER JOIN dbo.CiresonSoftwareAssetManufacturervw ManufacturerEnum
ON S.ManufacturerEnum_CiresonSoftwareAssetManufacturerId = ManufacturerEnum.CiresonSoftwareAssetManufacturerId
LEFT OUTER JOIN dbo.DisplayStringDimvw ManufacturerDS
ON ManufacturerEnum.EnumTypeId = ManufacturerDS.BaseManagedEntityId
AND ((ManufacturerDS.LanguageCode = @LanguageCode AND ManufacturerDS.DisplayName != null) OR ManufacturerDS.LanguageCode = 'ENU')
WHERE
S.CiresonSoftwareAssetDimKey = @SoftwareAssetDimKey
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.Cireson_AssetManagement_GetSoftwareAssetDetails TO ReportUser
GO
Home