-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE name = N'ProvanceProc_GetHardwareAssetDetailReport'
AND type = 'P'
)
DROP PROCEDURE dbo.ProvanceProc_GetHardwareAssetDetailReport
GO
CREATE PROCEDURE [dbo].[ProvanceProc_GetHardwareAssetDetailReport]
@LocationId INT,
@CostCenterId INT,
@OrganizationId INT,
@SupplierId INT,
@ManufacturerId INT,
@StatusId INT,
@TypeId INT,
@AgeRange VARCHAR(50),
@ExcludeLeased BIT = 0,
@ExcludeOwned BIT =0,
@IncludeDisposed BIT = 0,
@UserLanguage VARCHAR(50),
@AssetTag VARCHAR(255)
AS
BEGIN
-- Set @AssetTag to null if spaces or empty string
SELECT @AssetTag = nullif(ltrim(rtrim(@AssetTag)),'')
-- make sure @IncludeDisposed is not null
IF @IncludeDisposed IS NULL
BEGIN
SELECT @IncludeDisposed = 0
END
-- make sure @ExcludeLeased is not null
IF @ExcludeLeased IS NULL
BEGIN
SELECT @ExcludeLeased = 0
END
-- make sure @ExcludeOwned is not null
IF @ExcludeOwned IS NULL
BEGIN
SELECT @ExcludeOwned = 0
END
-- adjust @statusId to null value if not a specific id selected
IF @StatusId = -1
BEGIN
SELECT @StatusId = null
END
-- adjust @IncludeDisposed to 1 if Returned or Disposed are the @StatusId selected
Select @DisposedId = A.HardwareAssetLifecycleStatusId
FROM HardwareAssetLifecycleStatus A
WHERE A.EnumTypeId = @LifecycleStatusDisposed
SELECT @ReturnedId = A.HardwareAssetLifecycleStatusId
FROM HardwareAssetLifecycleStatus A
WHERE A.EnumTypeId = @LifecycleStatusReturned
IF @StatusId IN (@DisposedId, @ReturnedId)
BEGIN
SELECT @IncludeDisposed = 1
END
-- adjust @TypeId to null if no specific type selected
If @TypeId = -1
BEGIN
SELECT @TypeId = null
END
-- set start/end date for Age of Asset date range
IF @AgeRange = @ageOptGreaterFive
BEGIN
SELECT @EndDate = DATEADD(YY,-5,GETDATE())
END
ELSE IF @AgeRange = @ageOptBetweenTwoFive
BEGIN
SELECT @StartDate = DATEADD(YY,-5, GETDATE()),
@EndDate = DATEADD(YY,-2,GETDATE())
END
ELSE IF @AgeRange = @ageOptLessTwo
BEGIN
SELECT @StartDate = DATEADD(YY,-2, GETDATE())
END
ELSE IF @AgeRange = @ageOptUnknown
BEGIN
SELECT @StartDate = DATEADD(DD,1,GETDATE()),
@EndDate = DATEADD(DD,1,GETDATE())
END
-- get list of organizations under passed parent organization
IF @OrganizationId IS NOT NULL
BEGIN
--prime the orgIDs table
INSERT INTO #orgIDs
VALUES(@OrganizationId)
SELECT @rowcount = @@ROWCOUNT
WHILE @rowcount > 0
BEGIN
INSERT INTO #orgIDs
SELECT A.OrganizationContainsChildOrganization_OrganizationDimKey
FROM OrganizationChildOrganizationFactvw A
WHERE A.DeletedDate IS NULL
AND A.OrganizationDimKey IN (SELECT orgID
FROM #orgIDs)
AND A.OrganizationContainsChildOrganization_OrganizationDimKey NOT IN (SELECT orgID
FROM #orgIDs)
SELECT @rowcount = @@ROWCOUNT
END
END
-- get list of cost centers under passed parent cost center
IF @CostCenterId IS NOT NULL
BEGIN
--prime the #costcenterIDs table
INSERT INTO #costCenterIDs
VALUES(@CostCenterId)
SELECT @rowcount = @@ROWCOUNT
WHILE @rowcount > 0
BEGIN
INSERT INTO #costCenterIDs
SELECT A.CostCenterContainsChildCostCenter_CostCenterDimKey
FROM CostCenterChildCostCenterFactvw A
WHERE A.DeletedDate IS NULL
AND A.CostCenterDimKey IN (SELECT ccId
FROM #costCenterIDs)
AND A.CostCenterContainsChildCostCenter_CostCenterDimKey NOT IN (SELECT ccId
FROM #costCenterIDs)
SELECT @rowcount = @@ROWCOUNT
END
END
-- get list of locations under passed parent location
IF @LocationId IS NOT NULL
BEGIN
--prime the #locIDs table
INSERT INTO #locIDs
VALUES(@LocationId)
SELECT @rowcount = @@ROWCOUNT
WHILE @rowcount > 0
BEGIN
INSERT INTO #locIDs
SELECT A.LocationContainsChildLocation_LocationDimKey
FROM LocationChildLocationFactvw A
WHERE A.DeletedDate IS NULL
AND A.LocationDimKey IN (SELECT locID
FROM #locIDs)
AND A.LocationContainsChildLocation_LocationDimKey NOT IN (SELECT locID
FROM #locIDs)
SELECT @rowcount = @@ROWCOUNT
END
END
-- retrieve data
SELECT A.SerialNumber, A.HardwareAssetDimKey, A.AssetTag, A.DisplayName, B.HardwareAssetLifecycleStatusValue,
D.DisplayName AS Location, F.DisplayName AS CostCenter, H.DisplayName AS Organization,
J.DisplayName AS Supplier, N.DisplayName AS Manufacturer, A.ReceivedDate, P.HardwareAssetTypeValue AS HardwareType
FROM HardwareAssetDimvw A
--INNER JOIN HardwareAssetLifecycleStatus B ON A.LifecycleStatus_HardwareAssetLifecycleStatusId = B.HardwareAssetLifecycleStatusId
INNER JOIN (SELECT A.HardwareAssetLifecycleStatusId, A.EnumTypeId,
ISNULL(B.DisplayName, A.HardwareAssetLifecycleStatusValue) AS HardwareAssetLifecycleStatusValue
FROM HardwareAssetLifecycleStatus A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @UserLanguage
) B ON A.LifecycleStatus_HardwareAssetLifecycleStatusId = B.HardwareAssetLifecycleStatusId
LEFT OUTER JOIN HardwareAssetLocationFactvw C ON A.HardwareAssetDimKey = C.HardwareAssetDimKey
AND C.DeletedDate IS NULL
LEFT OUTER JOIN LocationDimvw D ON C.HardwareAssetHasLocation_LocationDimKey = D.LocationDimKey
AND D.IsDeleted = 0
AND (D.ObjectStatus IS NULL OR D.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN HardwareAssetCostCenterFactvw E ON A.HardwareAssetDimKey = E.HardwareAssetDimKey
AND E.DeletedDate IS NULL
LEFT OUTER JOIN CostCenterDimvw F ON E.HardwareAssetHasCostCenter_CostCenterDimKey = F.CostCenterDimKey
AND F.IsDeleted = 0
AND (F.ObjectStatus IS NULL OR F.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN HardwareAssetOrganizationFactvw G ON A.HardwareAssetDimKey = G.HardwareAssetDimKey
AND G.DeletedDate IS NULL
LEFT OUTER JOIN OrganizationDimvw H ON G.HardwareAssetHasOrganization_OrganizationDimKey = H.OrganizationDimKey
AND H.IsDeleted = 0
AND (H.ObjectStatus IS NULL OR H.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN HardwareAssetSupplierFactvw I ON A.HardwareAssetDimKey = I.HardwareAssetDimKey
AND I.DeletedDate IS NULL
LEFT OUTER JOIN CompanyDimvw J ON I.HardwareAssetHasSupplier_CompanyDimKey = J.CompanyDimKey
AND J.IsDeleted = 0
AND (J.ObjectStatus IS NULL OR J.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN HardwareAssetHardwareCatalogItemFactvw K ON A.HardwareAssetDimKey = K.HardwareAssetDimKey
AND K.DeletedDate IS NULL
LEFT OUTER JOIN HardwareCatalogDimvw L ON K.HardwareAssetIsHardwareCatalogItem_HardwareCatalogDimKey = L.HardwareCatalogDimKey
AND L.IsDeleted = 0
AND (L.ObjectStatus IS NULL OR L.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN HardwareCatalogItemManufacturerFactvw M ON L.HardwareCatalogDimKey = M.HardwareCatalogDimKey
AND M.DeletedDate IS NULL
LEFT OUTER JOIN CompanyDimvw N ON M.HardwareCatalogItemHasManufacturer_CompanyDimKey = N.CompanyDimKey
AND N.IsDeleted = 0
AND (N.ObjectStatus IS NULL OR N.ObjectStatus <> @ObjectStatusPendingDelete)
LEFT OUTER JOIN ( SELECT AA.HardwareAssetDimKey, BB.LeaseContractDimKey
FROM HardwareAssetLeaseContractFactvw AA
INNER JOIN LeaseContractDim BB ON AA.HardwareAssetHasLease_LeaseContractDimKey = BB.LeaseContractDimKey
AND BB.IsDeleted = 0
AND (BB.ObjectStatus IS NULL OR BB.ObjectStatus <> @ObjectStatusPendingDelete)
WHERE AA.DeletedDate IS NULL) O ON A.HardwareAssetDimKey = O.HardwareAssetDimKey
LEFT OUTER JOIN ( SELECT A.HardwareAssetTypeId, A.EnumTypeId,
ISNULL(B.DisplayName, A.HardwareAssetTypeValue) AS HardwareAssetTypeValue
FROM HardwareAssetType A
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId
AND B.LanguageCode = @UserLanguage
) P ON A.Type_HardwareAssetTypeId = P.HardwareAssetTypeId
WHERE A.IsDeleted = 0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @ObjectStatusPendingDelete)
--include/exclude disposed hardware assets
AND (B.EnumTypeId NOT IN (@LifecycleStatusDisposed, @LifecycleStatusReturned) OR @IncludeDisposed = 1)
-- include/exclude owned hardware assets
AND (O.LeaseContractDimKey IS NOT NULL OR @ExcludeOwned = 0)
-- include/exclude leased hardware assets
AND (O.LeaseContractDimKey IS NULL OR @ExcludeLeased = 0)
-- filter by age of asset
AND (A.ReceivedDate BETWEEN @StartDate AND @EndDate OR
(@StartDate IS NULL AND A.ReceivedDate < @EndDate) OR
(@EndDate IS NULL AND A.ReceivedDate > @StartDate) OR
(@StartDate IS NULL AND @EndDate IS NULL) OR
(@AgeRange = @ageOptUnknown AND A.ReceivedDate IS NULL))
-- filter by hardware asset lifecycle status
AND (A.LifecycleStatus_HardwareAssetLifecycleStatusId = @StatusId OR @StatusId IS NULL)
-- filter by hardware asset type
AND (A.Type_HardwareAssetTypeId = @TypeId OR @TypeId IS NULL)
-- filter by manufacturer id
AND (N.CompanyDimKey = @ManufacturerId OR @ManufacturerId IS NULL OR @ManufacturerId < 1)
-- filter by supplier id
AND (J.CompanyDimKey = @SupplierId OR @SupplierId IS NULL OR @SupplierId < 1)
-- filter by top level organization id
AND (H.OrganizationDimKey IN (SELECT orgID
FROM #orgIDs) OR (@OrganizationId = -1 AND H.OrganizationDimKey IS NULL) OR @OrganizationId IS NULL OR @OrganizationId < 1)
-- filter by top level cost center id
AND (F.CostCenterDimKey IN (SELECT ccID
FROM #costCenterIDs) OR (@CostCenterId = -1 AND F.CostCenterDimKey IS NULL) OR @CostCenterId IS NULL OR @CostCenterId <1)
-- filter by top level location id
AND (D.LocationDimKey IN (SELECT locId
FROM #locIDs) OR (@LocationId = -1 AND D.LocationDimKey IS NULL) OR @LocationId IS NULL OR @LocationId < 1)
AND (@AssetTag is null or A.AssetTag like @AssetTag)
END
GO
GRANT EXECUTE ON dbo.ProvanceProc_GetHardwareAssetDetailReport TO reportuser
GO
Home