Install.Cireson.AssetManagement.GetContractList (Resource)

Element properties:

Source Code:

<Resource ID="Install.Cireson.AssetManagement.GetContractList" Accessibility="Public" FileName="Install.Cireson.AssetManagement.GetContractList.sql" HasNullStream="false"/>

File Content: Install.Cireson.AssetManagement.GetContractList.sql

SET ANSI_NULLS ON


GO





SET QUOTED_IDENTIFIER ON


GO





IF EXISTS (


  SELECT * 


    FROM INFORMATION_SCHEMA.ROUTINES 


   WHERE SPECIFIC_SCHEMA = N'dbo'


     AND SPECIFIC_NAME = N'Cireson_AssetManagement_GetContractList' 


)


   DROP PROCEDURE dbo.Cireson_AssetManagement_GetContractList


GO





CREATE PROCEDURE [dbo].[Cireson_AssetManagement_GetContractList]


		@Name nvarchar(100),


		@LanguageCode nvarchar(5),	


		@CostCenter nvarchar(max),


		@Vendor nvarchar(max),


		@ContractStatus nvarchar(max)


AS


BEGIN


	


	-- V3


	SET NOCOUNT ON;


	


	IF(@Name Is Null) BEGIN SET @Name = '' END


    IF(@CostCenter = '-1') BEGIN SET @CostCenter = '' END


    IF(@CostCenter Is Null) BEGIN SET @CostCenter = '' END  


	IF (@Vendor Is Null) BEGIN SET @Vendor = '' END


	IF (@Vendor = '-1') BEGIN SET @Vendor = '' END


	IF (@ContractStatus Is Null) BEGIN SET @ContractStatus = '' END	


	IF (@ContractStatus = '-1') BEGIN SET @ContractStatus = '' END


	


	DECLARE @tableCostCenter TABLE (value nvarchar(256))  


	INSERT @tableCostCenter (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@CostCenter) 


	


	DECLARE @tableVendor TABLE (value nvarchar(256))  


	INSERT @tableVendor (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Vendor) 	


	


	DECLARE @tableContractStatus TABLE (value nvarchar(256))  


	INSERT @tableContractStatus (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@ContractStatus) 


	


	DECLARE @tableContracts TABLE


	(


	Name nvarchar(256),


	StartDate DateTime,


	EndDate DateTime,


	IsMaster bit,	


	Span nvarchar(256),


	Unit nvarchar(256),


	ContractStatus nvarchar(256),


	Vendor nvarchar(256),


	CostCenter nvarchar(256),


	Custodian nvarchar(256),


	[Type] nvarchar(256)


	)


	


	INSERT INTO @tableContracts


  


	SELECT


	W.Name,


	W.WarrantyStartDate,


	W.WarrantyEndDate,


	W.IsMaster,


	W.Span,


	UnitDS.DisplayName,


	StatusDS.DisplayName,


	Vendor.DisplayName,


	CostCenter.DisplayName,


	Custodian.DisplayName,


	'Warranty'


	


	FROM CiresonWarrantyDimvw W


	


	INNER JOIN


	dbo.ConfigItemDimvw CI


	ON W.EntityDimKey = CI.EntityDimKey


	


	LEFT OUTER JOIN	


	dbo.ConfigItemOwnedByUserFactvw


	ON dbo.ConfigItemOwnedByUserFactvw.ConfigItemDimKey = CI.ConfigItemDimKey


	AND ConfigItemOwnedByUserFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN


	dbo.UserDimvw AS Custodian 


	ON dbo.ConfigItemOwnedByUserFactvw.ConfigItemOwnedByUser_UserDimKey = Custodian.UserDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonWarrantyRelatesToCostCenterFactvw


	ON dbo.CiresonWarrantyRelatesToCostCenterFactvw.CiresonWarrantyDimKey = W.CiresonWarrantyDimKey


	AND CiresonWarrantyRelatesToCostCenterFactvw.DeletedDate Is Null	


	


	LEFT OUTER JOIN	


	dbo.CiresonCostCenterDimVw AS CostCenter 


	ON dbo.CiresonWarrantyRelatesToCostCenterFactvw.WarrantyHasCostCenter_CiresonCostCenterDimKey = CostCenter.CiresonCostCenterDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonWarrantyRelatesToVendorFactvw


	ON dbo.CiresonWarrantyRelatesToVendorFactvw.CiresonWarrantyDimKey = W.CiresonWarrantyDimKey


	AND CiresonWarrantyRelatesToVendorFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN	


	dbo.CiresonVendorDimvw AS Vendor 


	ON dbo.CiresonWarrantyRelatesToVendorFactvw.WarrantyHasVendor_CiresonVendorDimKey = Vendor.CiresonVendorDimKey		


		


	LEFT OUTER JOIN	


	dbo.CiresonWarrantyContractStatusvw AS StatusEnum 


	ON StatusEnum.CiresonWarrantyContractStatusId = W.ContractStatus_CiresonSupportContractContractStatusId


	


	LEFT OUTER JOIN	


	dbo.DisplayStringDimvw StatusDS


	ON StatusEnum.EnumTypeId = StatusDS.BaseManagedEntityId


	AND ((StatusDS.LanguageCode = @LanguageCode AND StatusDS.DisplayName != null) OR StatusDS.LanguageCode = 'ENU')


	


	LEFT OUTER JOIN	


	dbo.CiresonContractUnit AS UnitEnum 


	ON UnitEnum.CiresonContractUnitId = W.Unit_CiresonContractUnitId


	


	LEFT OUTER JOIN	


	dbo.DisplayStringDimvw UnitDS


	ON UnitEnum.EnumTypeId = UnitDS.BaseManagedEntityId


	AND ((UnitDS.LanguageCode = @LanguageCode AND UnitDS.DisplayName != null) OR UnitDS.LanguageCode = 'ENU')


	


	WHERE 	


	


	((@Name = '') OR W.Name LIKE '%' + @Name + '%') AND


	((0 IN (SELECT value FROM @tableCostCenter)) OR (CostCenter.CiresonCostCenterDimKey IN (SELECT value FROM @tableCostCenter))) AND


    ((0 IN (SELECT value FROM @tableVendor)) OR (Vendor.CiresonVendorDimKey IN (SELECT value FROM @tableVendor))) AND


	((@ContractStatus = '' ) OR (W.ContractStatus_CiresonSupportContractContractStatusId In (Select value from @tableContractStatus)))	


	


	AND W.IsDeleted = 0


	


	INSERT INTO @tableContracts


  


	SELECT


	L.Name,


	L.LeaseStartDate,


	L.LeaseEndDate,


	L.IsMaster,


	L.Span,


	UnitDS.DisplayName,


	StatusDS.DisplayName,


	Vendor.DisplayName,


	CostCenter.DisplayName,


	Custodian.DisplayName,


	'Lease'


	


	FROM CiresonLeaseDimvw L


	


	INNER JOIN


	dbo.ConfigItemDimvw CI


	ON L.EntityDimKey = CI.EntityDimKey


	


	LEFT OUTER JOIN	


	dbo.ConfigItemOwnedByUserFactvw


	ON dbo.ConfigItemOwnedByUserFactvw.ConfigItemDimKey = CI.ConfigItemDimKey


	AND ConfigItemOwnedByUserFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN


	dbo.UserDimvw AS Custodian 


	ON dbo.ConfigItemOwnedByUserFactvw.ConfigItemOwnedByUser_UserDimKey = Custodian.UserDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonLeaseRelatesToCostCenterFactvw


	ON dbo.CiresonLeaseRelatesToCostCenterFactvw.CiresonLeaseDimKey = L.CiresonLeaseDimKey


	AND CiresonLeaseRelatesToCostCenterFactvw.DeletedDate Is Null	


	


	LEFT OUTER JOIN	


	dbo.CiresonCostCenterDimVw AS CostCenter 


	ON dbo.CiresonLeaseRelatesToCostCenterFactvw.LeaseHasCostCenter_CiresonCostCenterDimKey = CostCenter.CiresonCostCenterDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonLeaseRelatesToVendorFactvw


	ON dbo.CiresonLeaseRelatesToVendorFactvw.CiresonLeaseDimKey = L.CiresonLeaseDimKey


	AND CiresonLeaseRelatesToVendorFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN	


	dbo.CiresonVendorDimvw AS Vendor 


	ON dbo.CiresonLeaseRelatesToVendorFactvw.LeaseHasVendor_CiresonVendorDimKey = Vendor.CiresonVendorDimKey		


		


	LEFT OUTER JOIN	


	dbo.CiresonLeaseContractStatusvw AS StatusEnum 


	ON StatusEnum.CiresonLeaseContractStatusId = L.ContractStatus_CiresonSupportContractContractStatusId


	


	LEFT OUTER JOIN	


	dbo.DisplayStringDimvw StatusDS


	ON StatusEnum.EnumTypeId = StatusDS.BaseManagedEntityId


	AND ((StatusDS.LanguageCode = @LanguageCode AND StatusDS.DisplayName != null) OR StatusDS.LanguageCode = 'ENU')


	


	LEFT OUTER JOIN	


	dbo.CiresonContractUnit AS UnitEnum 


	ON UnitEnum.CiresonContractUnitId = L.Unit_CiresonContractUnitId


	


	LEFT OUTER JOIN	


	dbo.DisplayStringDimvw UnitDS


	ON UnitEnum.EnumTypeId = UnitDS.BaseManagedEntityId


	AND ((UnitDS.LanguageCode = @LanguageCode AND UnitDS.DisplayName != null) OR UnitDS.LanguageCode = 'ENU')


	


	WHERE 	


	


	((@Name = '') OR L.Name LIKE '%' + @Name + '%') AND


	((0 IN (SELECT value FROM @tableCostCenter)) OR (CostCenter.CiresonCostCenterDimKey IN (SELECT value FROM @tableCostCenter))) AND


	((0 IN (SELECT value FROM @tableVendor)) OR (Vendor.CiresonVendorDimKey IN (SELECT value FROM @tableVendor))) AND


	((@ContractStatus = '' ) OR (L.ContractStatus_CiresonSupportContractContractStatusId In (Select value from @tableContractStatus)))	


	


	AND L.IsDeleted = 0


	


	INSERT INTO @tableContracts


  


	SELECT


	S.Name,


	S.ContractStartDate,


	S.ContractEndDate,


	0,


	S.Span,


	'',


	StatusDS.DisplayName,


	Vendor.DisplayName,


	CostCenter.DisplayName,


	Custodian.DisplayName,


	'Support Contract' 


	


	FROM CiresonSupportContractDimvw S


	


	INNER JOIN


	dbo.ConfigItemDimvw CI


	ON S.EntityDimKey = CI.EntityDimKey


	


	LEFT OUTER JOIN	


	dbo.ConfigItemOwnedByUserFactvw


	ON dbo.ConfigItemOwnedByUserFactvw.ConfigItemDimKey = CI.ConfigItemDimKey


	AND ConfigItemOwnedByUserFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN


	dbo.UserDimvw AS Custodian 


	ON dbo.ConfigItemOwnedByUserFactvw.ConfigItemOwnedByUser_UserDimKey = Custodian.UserDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonSupportContractRelatesToCostCenterFactvw


	ON dbo.CiresonSupportContractRelatesToCostCenterFactvw.CiresonSupportContractDimKey = S.CiresonSupportContractDimKey


	AND CiresonSupportContractRelatesToCostCenterFactvw.DeletedDate Is Null	


	


	LEFT OUTER JOIN	


	dbo.CiresonCostCenterDimVw AS CostCenter 


	ON dbo.CiresonSupportContractRelatesToCostCenterFactvw.SupportContractHasCostCenter_CiresonCostCenterDimKey = CostCenter.CiresonCostCenterDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonSupportContractRelatesToVendorFactvw


	ON dbo.CiresonSupportContractRelatesToVendorFactvw.CiresonSupportContractDimKey = S.CiresonSupportContractDimKey


	AND CiresonSupportContractRelatesToVendorFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN	


	dbo.CiresonVendorDimvw AS Vendor 


	ON dbo.CiresonSupportContractRelatesToVendorFactvw.SupportContractHasVendor_CiresonVendorDimKey = Vendor.CiresonVendorDimKey		


		


	LEFT OUTER JOIN	


	dbo.CiresonSupportContractContractStatusvw AS StatusEnum 


	ON StatusEnum.CiresonSupportContractContractStatusId = S.ContractStatus_CiresonSupportContractContractStatusId


	


	LEFT OUTER JOIN	


	dbo.DisplayStringDimvw StatusDS


	ON StatusEnum.EnumTypeId = StatusDS.BaseManagedEntityId


	AND ((StatusDS.LanguageCode = @LanguageCode AND StatusDS.DisplayName != null) OR StatusDS.LanguageCode = 'ENU')


	


	LEFT OUTER JOIN	


	dbo.CiresonContractUnit AS UnitEnum 


	ON UnitEnum.CiresonContractUnitId = S.Unit_CiresonContractUnitId


	


	LEFT OUTER JOIN	


	dbo.DisplayStringDimvw UnitDS


	ON UnitEnum.EnumTypeId = UnitDS.BaseManagedEntityId


	AND ((UnitDS.LanguageCode = @LanguageCode AND UnitDS.DisplayName != null) OR UnitDS.LanguageCode = 'ENU')


	


	WHERE 	


	


	((@Name = '') OR S.Name LIKE '%' + @Name + '%') AND


	((0 IN (SELECT value FROM @tableCostCenter)) OR (CostCenter.CiresonCostCenterDimKey IN (SELECT value FROM @tableCostCenter))) AND


	((0 IN (SELECT value FROM @tableVendor)) OR (Vendor.CiresonVendorDimKey IN (SELECT value FROM @tableVendor))) AND


	((@ContractStatus = '' ) OR (StatusDS.DisplayName IS NOT NULL AND StatusDS.IsDeleted <> 1


		AND StatusDS.DisplayName <> '' AND S.ContractStatus_CiresonSupportContractContractStatusId In (SELECT value FROM @tableContractStatus)))	


	


	AND S.IsDeleted = 0	


	


	SELECT * FROM @tableContracts ORDER BY [Type],Name


	


END





GO





GRANT EXECUTE ON dbo.Cireson_AssetManagement_GetContractList TO ReportUser


GO