;WITH vContractTypes as(
SELECT ISNULL(B.DisplayName,B.SubElementName) AS label,
B.SubElementName, A.ManagementPackId,B.DisplayStringDimKey
FROM ManagementPackDim A
Left JOIN DisplayStringDim B ON A.ManagementPackId = B.ManagementPackId
AND B.LanguageCode = @LanguageCode
AND B.ElementName = 'Provance.ITAssetManagement.Report.EndofContracts2'
WHERE A.MPName = 'Provance.ServiceManager.Itam.Software.Reports'
AND B.SubElementName like '%')
--Use this section to rebuild the default values below if there are changes
--SELECT DISTINCT 'INSERT INTO #ContractType (label,value) select ''[' + OptionSet.LABEL + ']'',''' + OptionSet.VALUE + '''
--where not exists (select label from #ContractType where value = ''' + VALUE + ''')'
INSERT INTO #ContractType (label,value)
SELECT DISTINCT OptionSet.Label, OptionSet.Value
FROM
(
SELECT C.label, 'AL' AS value FROM vContractTypes C WHERE C.SubElementName = 'R' union all -- @SubElementName union all
SELECT C.label, 'WA' AS value FROM vContractTypes C WHERE C.SubElementName = 'G' union all -- @WarrantySubElementName union all
SELECT C.label, 'LE' AS value FROM vContractTypes C WHERE C.SubElementName = 'D' union all -- @LeaseSubElementName union all
SELECT C.label, 'DI' AS value FROM vContractTypes C WHERE C.SubElementName = 'C' union all -- @DisposalSubElementName union all
SELECT C.label, 'SV' AS value FROM vContractTypes C WHERE C.SubElementName = 'H' union all -- @SupportSubElementName union all
SELECT C.label, 'SM' AS value FROM vContractTypes C WHERE C.SubElementName = 'F' union all -- @MaintenanceSubElementName union all
SELECT C.label, 'SA' AS value FROM vContractTypes C WHERE C.SubElementName = 'E' union all -- @LicenseAgreementSubElementName union all
SELECT C.label, 'UD' AS value FROM vContractTypes C WHERE C.SubElementName = 'W' -- User Defined
) OptionSet
--INSERT INTO #ContractType (label,value)
--select isnull(@LanguageCode,'null'),'LanguageCode'
--where not exists (select label from #ContractType where value = 'AL')
-- Default values
-- We have seen an invalid language code being passed. These default values will allow the report to continue.
INSERT INTO #ContractType (label,value) select '[All]', 'AL' where not exists (select label from #ContractType where value = 'AL')
INSERT INTO #ContractType (label,value) select '[Warranty Agreements]', 'WA' where not exists (select label from #ContractType where value = 'WA')
INSERT INTO #ContractType (label,value) select '[Lease Schedules & Agreements ]', 'LE' where not exists (select label from #ContractType where value = 'LE')
INSERT INTO #ContractType (label,value) select '[Disposal Agreements]', 'DI' where not exists (select label from #ContractType where value = 'DI')
INSERT INTO #ContractType (label,value) select '[Hardware Maintenance & Support Agreements]', 'SV' where not exists (select label from #ContractType where value = 'SV')
INSERT INTO #ContractType (label,value) select '[Software Maintenance & Support Agreements]', 'SM' where not exists (select label from #ContractType where value = 'SM')
INSERT INTO #ContractType (label,value) select '[Software License Agreements]', 'SA' where not exists (select label from #ContractType where value = 'SA')
INSERT INTO #ContractType (label,value) select '[User Defined Agreements]', 'UD' where not exists (select label from #ContractType where value = 'UD')
select * from #ContractType a
order by a.label
drop table #ContractType</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="label">
<DataField>label</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="value">
<DataField>value</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="LT_Report">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ManagementPackSystemName">
<Value>Provance.Asset.Reports</Value>
</QueryParameter>
<QueryParameter Name="@ReportSystemName">
<Value>Provance.Report.AgreementSummary</Value>
</QueryParameter>
<QueryParameter Name="@LanguageCode">
<Value>=Code.GetReportLocLanguageCode()</Value>
</QueryParameter>
<QueryParameter Name="@Scope">
<Value>%</Value>
</QueryParameter>
</QueryParameters>
<CommandType>StoredProcedure</CommandType>
<CommandText>ReportDisplayStringGet</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="XML_F52E2B61_18A1_11d1_B105_00805F49916B">
<DataField>XML_F52E2B61-18A1-11d1-B105-00805F49916B</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="SupportContract">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query SupportContract AgreementSummary
SELECT *
FROM (
SELECT A.DisplayName AgreementCode,
F.DisplayName Supplier,
A.Id,
A.DisplayName,
B.EffectiveDate,
B.ExpiryDate ExpirationDate,
0 RelatedAssetCount,
D.ProvanceAgreementDimKey AgreementDimKey,
case when b.ExpiryDate is null then 'UN'
when datediff(d,GETDATE(),b.ExpiryDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),b.ExpiryDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),b.ExpiryDate) ExpiredDays
FROM ProvanceHardwareMaintenanceAgreementDimvw A
INNER JOIN ProvanceHardwareAssetStandardHasHardwareMaintenanceAgreementFactvw B ON A.ProvanceHardwareMaintenanceAgreementDimKey = B.HardwareAssetStandardHasHardwareMaintenanceAgreement_ProvanceHardwareMaintenanceAgreementDimKey
AND B.DeletedDate IS NULL
JOIN ProvanceAgreementDimvw D ON A.BaseManagedEntityId = D.BaseManagedEntityId
LEFT JOIN ProvanceAgreementHasSupplierFactvw E ON D.ProvanceAgreementDimKey = E.ProvanceAgreementDimKey
LEFT JOIN ProvanceCompanyDim F ON E.AgreementHasSupplier_ProvanceCompanyDimKey = F.ProvanceCompanyDimKey
LEFT JOIN ProvanceAgreementHasOrganizationFactvw G ON D.ProvanceAgreementDimKey = G.ProvanceAgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasCostCenterFactvw H ON D.ProvanceAgreementDimKey = H.ProvanceAgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasLocationFactvw I ON D.ProvanceAgreementDimKey = I.ProvanceAgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasUserFactvw J ON D.ProvanceAgreementDimKey = J.ProvanceAgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted = 0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @objectStatusPendingDelete)
AND (('WA' in (@ContractType)) OR ('AL' in (@ContractType)))
AND ((@ExpiredDateBegin is null) or (b.ExpiryDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo is null) or (b.ExpiryDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin is null) or (B.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo is null) or (B.EffectiveDate <= Convert(DateTime, Convert(varchar(50),@EffectiveDateTo,102) + ' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (E.AgreementHasSupplier_ProvanceCompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_ProvanceOrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_ProvanceCostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_ProvanceLocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementHasUser_UserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="supplier">
<DataField>Supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="RelatedAssetCount">
<DataField>RelatedAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="DisposalContract">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query DisposalContract AgreementSummary
--declare @ThresholdContractExpired int, @ThresholdContractCloseToExpired int,@objectStatusPendingDelete varchar(255),@ContractType varchar(255),
--@ExpiredDateBegin datetime,@ExpiredDateTo datetime, @EffectiveDateBegin datetime, @EffectiveDateTo datetime, @Supplier int, @OrganizationId int,@OrganizationDimKeyList varchar(255), @CostCenterDimKeyList VARCHAR(255), @CostCenterId int,
--@LocationId int, @LocationDimKeyList varchar(255),@ContractManagerId int, @Threshold varchar(255)
SELECT *
FROM (
SELECT A.DisplayName AgreementCode,
F.DisplayName Supplier,
A.Id,
A.DisplayName,
a.EffectiveDate,
a.ExpiryDate ExpirationDate,
0 RelatedAssetCount,
D.ProvanceAgreementDimKey AgreementDimKey,
case when a.ExpiryDate is null then 'UN'
when datediff(d,GETDATE(),a.ExpiryDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),a.ExpiryDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),a.ExpiryDate) ExpiredDays
FROM ProvanceDisposalAgreementDimvw A
JOIN ProvanceAgreementDimvw D ON A.BaseManagedEntityId = D.BaseManagedEntityId
LEFT JOIN ProvanceAgreementHasSupplierFactvw E ON D.ProvanceAgreementDimKey = E.ProvanceAgreementDimKey
LEFT JOIN ProvanceCompanyDim F ON E.AgreementHasSupplier_ProvanceCompanyDimKey = F.ProvanceCompanyDimKey
LEFT JOIN ProvanceAgreementHasOrganizationFactvw G ON D.ProvanceAgreementDimKey = G.ProvanceAgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasCostCenterFactvw H ON D.ProvanceAgreementDimKey = H.ProvanceAgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasLocationFactvw I ON D.ProvanceAgreementDimKey = I.ProvanceAgreementDimKey AND I.DeletedDate IS NULL
WHERE A.IsDeleted = 0
AND ISNULL(a.ObjectStatus,'') <> @objectStatusPendingDelete
AND (('WA' in (@ContractType)) OR ('AL' in (@ContractType)))
AND ((@ExpiredDateBegin is null) or (a.ExpiryDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo is null) or (a.ExpiryDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin is null) or (a.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo is null) or (a.EffectiveDate <= Convert(DateTime, Convert(varchar(50),@EffectiveDateTo,102) + ' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (E.AgreementHasSupplier_ProvanceCompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_ProvanceOrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_ProvanceCostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_ProvanceLocationDimKey in (@LocationDimKeyList)))
and ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR exists (select 'x' from ProvanceAgreementHasUserFactvw j where D.ProvanceAgreementDimKey = J.ProvanceAgreementDimKey AND J.DeletedDate IS NULL
and J.AgreementHasUser_UserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="supplier">
<DataField>Supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="relatedAssetCount">
<DataField>RelatedAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="SWMaintance">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query SWMaintance AgreementSummary
SELECT *
FROM (
SELECT A.DisplayName AgreementCode,
'' AgreementNumber,
'' Publisher,
F.DisplayName Supplier,
A.Id,
A.DisplayName,
a.EffectiveDate,
a.ExpiryDate ExpirationDate,
0 RelatedAssetCount,
D.ProvanceAgreementDimKey AgreementDimKey,
case when a.ExpiryDate is null then 'UN'
when datediff(d,GETDATE(),a.ExpiryDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),a.ExpiryDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),a.ExpiryDate) ExpiredDays
FROM ProvanceSoftwareSupportAgreementDimvw A
-- INNER JOIN ProvanceHardwareAssetStandardHasSoftwareSupportAgreementFactvw B ON A.ProvanceSoftwareSupportAgreementDimKey = B.HardwareAssetStandardHasSoftwareSupportAgreement_ProvanceSoftwareSupportAgreementDimKey
-- AND B.DeletedDate IS NULL
JOIN ProvanceAgreementDimvw D ON A.BaseManagedEntityId = D.BaseManagedEntityId
LEFT JOIN ProvanceAgreementHasSupplierFactvw E ON D.ProvanceAgreementDimKey = E.ProvanceAgreementDimKey
LEFT JOIN ProvanceCompanyDim F ON E.AgreementHasSupplier_ProvanceCompanyDimKey = F.ProvanceCompanyDimKey
LEFT JOIN ProvanceAgreementHasOrganizationFactvw G ON D.ProvanceAgreementDimKey = G.ProvanceAgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasCostCenterFactvw H ON D.ProvanceAgreementDimKey = H.ProvanceAgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasLocationFactvw I ON D.ProvanceAgreementDimKey = I.ProvanceAgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasUserFactvw J ON D.ProvanceAgreementDimKey = J.ProvanceAgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted = 0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @objectStatusPendingDelete)
AND (('WA' in (@ContractType)) OR ('AL' in (@ContractType)))
AND ((@ExpiredDateBegin is null) or (a.ExpiryDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo is null) or (a.ExpiryDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin is null) or (a.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo is null) or (a.EffectiveDate <= Convert(DateTime, Convert(varchar(50),@EffectiveDateTo,102) + ' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (E.AgreementHasSupplier_ProvanceCompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_ProvanceOrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_ProvanceCostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_ProvanceLocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementHasUser_UserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Supplier">
<DataField>Supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Publisher">
<DataField>Publisher</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementNumber">
<DataField>AgreementNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="RelatedAssetCount">
<DataField>RelatedAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="SWLicenseAgreement">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query SWLicenseAgreement AgreementSummary
SELECT *
FROM (
SELECT A.DisplayName AgreementCode, A.DisplayName AgreementNumber, '' PublisherName, '' LicensingProgramValue,
F.DisplayName Supplier,
A.Id,
A.DisplayName,
a.EffectiveDate,
a.ExpiryDate ExpirationDate,
0 RelatedAssetCount,
D.ProvanceAgreementDimKey AgreementDimKey,
case when a.ExpiryDate is null then 'UN'
when datediff(d,GETDATE(),a.ExpiryDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),a.ExpiryDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),a.ExpiryDate) ExpiredDays
FROM ProvanceSoftwareLicensingAgreementDimvw A
-- INNER JOIN ProvanceHardwareAssetStandardHasSoftwareLicensingAgreementFactvw B ON A.ProvanceSoftwareLicensingAgreementDimKey = B.HardwareAssetStandardHasSoftwareLicensingAgreement_ProvanceSoftwareLicensingAgreementDimKey
-- AND B.DeletedDate IS NULL
JOIN ProvanceAgreementDimvw D ON A.BaseManagedEntityId = D.BaseManagedEntityId
LEFT JOIN ProvanceAgreementHasSupplierFactvw E ON D.ProvanceAgreementDimKey = E.ProvanceAgreementDimKey
LEFT JOIN ProvanceCompanyDim F ON E.AgreementHasSupplier_ProvanceCompanyDimKey = F.ProvanceCompanyDimKey
LEFT JOIN ProvanceAgreementHasOrganizationFactvw G ON D.ProvanceAgreementDimKey = G.ProvanceAgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasCostCenterFactvw H ON D.ProvanceAgreementDimKey = H.ProvanceAgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasLocationFactvw I ON D.ProvanceAgreementDimKey = I.ProvanceAgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasUserFactvw J ON D.ProvanceAgreementDimKey = J.ProvanceAgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted = 0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @objectStatusPendingDelete)
AND (('WA' in (@ContractType)) OR ('AL' in (@ContractType)))
AND ((@ExpiredDateBegin is null) or (a.ExpiryDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo is null) or (a.ExpiryDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin is null) or (a.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo is null) or (a.EffectiveDate <= Convert(DateTime, Convert(varchar(50),@EffectiveDateTo,102) + ' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (E.AgreementHasSupplier_ProvanceCompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_ProvanceOrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_ProvanceCostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_ProvanceLocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementHasUser_UserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Supplier">
<DataField>Supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementNumber">
<DataField>AgreementNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="LicensingProgramValue">
<DataField>LicensingProgramValue</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PublisherName">
<DataField>PublisherName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="RelatedAssetCount">
<DataField>RelatedAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="Dummy">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>select 1</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="ID">
<DataField />
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="ContractAdminSettings">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>--Query ContractAdminSettings AgreementSummary
-- The full outer join will ensure we have a value even if there is no row in ProvanceAssetAdminSettings.
SELECT DISTINCT TOP 1 ISNULL(B.AgreementExpiredThreshold,1) ExpiredThresholdDays, ISNULL(B.AgreementExpiryWarningThreshold,365) CloseToExpiredThresholdDays
FROM (select 1 C1) a
full outer join ProvanceAssetAdminSettingsDim B on B.IsDeleted = 0
--AND SourceId = @SourceId</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="ExpiredThresholdDays">
<DataField>ExpiredThresholdDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CloseToExpiredThresholdDays">
<DataField>CloseToExpiredThresholdDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="ParameterDisplayName">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>--Query ParameterDisplayName AgreementSummary
declare @sSupplierDisplayName NVARCHAR(4000),
@sLocationDisplayName NVARCHAR(4000),
@sCostCenterDisplayName NVARCHAR(4000),
@sOrganizationDisplayName NVARCHAR(4000),
@sContractManagerDisplayName NVARCHAR(4000)
SELECT @sSupplierDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceCompanyDimvw A
WHERE A.ProvanceCompanyDimKey = @Supplier
AND A.IsDeleted = 0
SELECT @sLocationDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceLocationDimvw A
WHERE A.ProvanceLocationDimKey = @LocationId
AND A.IsDeleted = 0
SELECT @sCostCenterDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceCostCenterDimvw A
WHERE A.ProvanceCostCenterDimKey = @CostCenterId
AND A.IsDeleted = 0
SELECT @sOrganizationDisplayName = substring(A.DisplayName,1,4000)
FROM ProvanceOrganizationDimvw A
WHERE A.ProvanceOrganizationDimKey = @CostCenterId
AND A.IsDeleted = 0
SELECT @sContractManagerDisplayName = substring(A.DisplayName,1,4000)
FROM UserDimVw A
WHERE A.UserDimKey = @ContractManagerId
AND A.IsDeleted = 0
SELECT @sSupplierDisplayName SupplierDisplayName,
@sLocationDisplayName LocationDisplayName,
@sCostCenterDisplayName CostCenterDisplayName,
@sOrganizationDisplayName OrganizationDisplayName,
@sContractManagerDisplayName ContractManagerDisplayName</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="SupplierDisplayName">
<DataField>SupplierDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="LocationDisplayName">
<DataField>LocationDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CostCenterDisplayName">
<DataField>CostCenterDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrganizationDisplayName">
<DataField>OrganizationDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContractManagerDisplayName">
<DataField>ContractManagerDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="UserDefined">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>--Query UserDefined AgreementSummary
SELECT *
FROM (
SELECT A.DisplayName AgreementCode,
'' AgreementNumber,
F.DisplayName Supplier,
'' AgreementType,
F.DisplayName SupplierName,
A.Id,
A.DisplayName,
a.EffectiveDate,
a.ExpiryDate ExpirationDate,
0 RelatedAssetCount,
D.ProvanceAgreementDimKey AgreementDimKey,
case when a.ExpiryDate is null then 'UN'
when datediff(d,GETDATE(),a.ExpiryDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),a.ExpiryDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),a.ExpiryDate) ExpiredDays
FROM ProvanceUserDefinedAgreementDimvw A
-- INNER JOIN ProvanceHardwareAssetStandardHasUserDefinedAgreementFactvw B ON A.ProvanceUserDefinedAgreementDimKey = B.HardwareAssetStandardHasUserDefinedAgreement_ProvanceUserDefinedAgreementDimKey
-- AND B.DeletedDate IS NULL
JOIN ProvanceAgreementDimvw D ON A.BaseManagedEntityId = D.BaseManagedEntityId
LEFT JOIN ProvanceAgreementHasSupplierFactvw E ON D.ProvanceAgreementDimKey = E.ProvanceAgreementDimKey
LEFT JOIN ProvanceCompanyDim F ON E.AgreementHasSupplier_ProvanceCompanyDimKey = F.ProvanceCompanyDimKey
LEFT JOIN ProvanceAgreementHasOrganizationFactvw G ON D.ProvanceAgreementDimKey = G.ProvanceAgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasCostCenterFactvw H ON D.ProvanceAgreementDimKey = H.ProvanceAgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasLocationFactvw I ON D.ProvanceAgreementDimKey = I.ProvanceAgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN ProvanceAgreementHasUserFactvw J ON D.ProvanceAgreementDimKey = J.ProvanceAgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted = 0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @objectStatusPendingDelete)
AND (('WA' in (@ContractType)) OR ('AL' in (@ContractType)))
AND ((@ExpiredDateBegin is null) or (a.ExpiryDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo is null) or (a.ExpiryDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin is null) or (a.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo is null) or (a.EffectiveDate <= Convert(DateTime, Convert(varchar(50),@EffectiveDateTo,102) + ' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (E.AgreementHasSupplier_ProvanceCompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_ProvanceOrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_ProvanceCostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_ProvanceLocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementHasUser_UserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Supplier">
<DataField>Supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementNumber">
<DataField>AgreementNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="AgreementType">
<DataField>AgreementType</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierName">
<DataField>SupplierName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="RelatedAssetCount">
<DataField>RelatedAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="Threshold">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@LanguageCode">
<Value>=Code.GetReportLocLanguageCode()</Value>
</QueryParameter>
</QueryParameters>
<CommandText>--Query Threshold AgreementSummary
create table #ThresholdType (label varchar(255) null, value varchar(255) null)
;WITH vThresholdTypes as(
SELECT ISNULL(B.DisplayName,B.SubElementName) AS label,
B.SubElementName, A.ManagementPackId,B.DisplayStringDimKey
FROM ManagementPackDim A
Left JOIN DisplayStringDim B ON A.ManagementPackId = B.ManagementPackId
AND B.LanguageCode = @LanguageCode
AND B.ElementName = 'Provance.ITAssetManagement.Report.EndOfContracts2'
WHERE A.MPName = 'Provance.ServiceManager.Itam.Software.Reports'
AND B.SubElementName like '%' )
--Use this section to rebuild the default values below if there are changes
--SELECT DISTINCT 'INSERT INTO #ThresholdType (label,value) select ''[' + OptionSet.LABEL + ']'',''' + OptionSet.VALUE + '''
--where not exists (select label from #ThresholdType where value = ''' + VALUE + ''')'
INSERT INTO #ThresholdType (label,value)
SELECT DISTINCT OptionSet.Label, OptionSet.Value
FROM
(
SELECT C.label, 'AL' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'R' union all
SELECT C.label, 'EX' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'X' union all
SELECT C.label, 'CL' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'Y' union all
SELECT C.label, 'GO' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'Z' union all
SELECT C.label, 'UN' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'ZA'
) OptionSet
--INSERT INTO #ThresholdType (label,value)
--select @LanguageCode,'languagecode'
--where not exists (select label from #ThresholdType where value = 'AL')
-- Default values
-- We have seen an invalid language code being passed. These default values will allow the report to continue.
INSERT INTO #ThresholdType (label,value) select '[All]','AL' where not exists (select label from #ThresholdType where value = 'AL')
INSERT INTO #ThresholdType (label,value) select '[Close]','CL' where not exists (select label from #ThresholdType where value = 'CL')
INSERT INTO #ThresholdType (label,value) select '[Expired]','EX' where not exists (select label from #ThresholdType where value = 'EX')
INSERT INTO #ThresholdType (label,value) select '[Good]','GO' where not exists (select label from #ThresholdType where value = 'GO')
INSERT INTO #ThresholdType (label,value) select '[Unknown]','UN' where not exists (select label from #ThresholdType where value = 'UN')
declare @LocationDimKey table (LocationDimKey int)
DECLARE @iRowcount int
IF @LocationId IS NOT NULL and @LocationId > 0
BEGIN
INSERT INTO @LocationDimKey
VALUES(@LocationId)
SELECT @iRowcount = @@ROWCOUNT
WHILE @iRowcount > 0
BEGIN
INSERT INTO @LocationDimKey
SELECT A.ProvanceLocationDimKey
FROM ProvanceLocationIsContainedByLocationFactvw A
WHERE A.DeletedDate IS NULL
AND A.ProvanceLocationDimKey not IN (SELECT LocationDimKey FROM @LocationDimKey)
AND A.LocationIsContainedByLocation_ProvanceLocationDimKey IN (SELECT LocationDimKey FROM @LocationDimKey)
SELECT @iRowcount = @@ROWCOUNT
END
END
select LocationDimKey from @LocationDimKey
union
select 0
order by LocationDimKey
/*
select a.DisplayName,a.ProvanceLocationDimKey,b.* from ProvanceLocationDimvw a
left join ProvanceLocationIsContainedByLocationFactvw b
on a.ProvanceLocationDimKey = b.ProvanceLocationDimKey
where DisplayName like 'aa%'
declare @CostCenterDimKey table (CostCenterDimKey int)
DECLARE @iRowcount int
IF @CostCenterId IS NOT NULL and @CostCenterId > 0
BEGIN
INSERT INTO @CostCenterDimKey
VALUES(@CostCenterId)
SELECT @iRowcount = @@ROWCOUNT
WHILE @iRowcount > 0
BEGIN
INSERT INTO @CostCenterDimKey
SELECT A.ProvanceCostCenterDimKey
FROM ProvanceCostCenterIsContainedByCostCenterFactvw A
WHERE A.DeletedDate IS NULL
AND A.ProvanceCostCenterDimKey not IN (SELECT CostCenterDimKey FROM @CostCenterDimKey)
AND A.CostCenterIsContainedByCostCenter_ProvanceCostCenterDimKey IN (SELECT CostCenterDimKey FROM @CostCenterDimKey)
SELECT @iRowcount = @@ROWCOUNT
END
END
select CostCenterDimKey from @CostCenterDimKey
union
select 0
order by CostCenterDimKey</CommandText>
</Query>
<Fields>
<Field Name="CostCenterDimKey">
<DataField>CostCenterDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="OrganizationDimKeyQuery">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>--Query OrganizationDimKeyQuery
declare @OrganizationId int select @Organizationid = 8
declare @OrganizationDimKey table (OrganizationDimKey int)
DECLARE @iRowcount int
IF @OrganizationId IS NOT NULL and @OrganizationId > 0
BEGIN
INSERT INTO @OrganizationDimKey
VALUES(@OrganizationId)
SELECT @iRowcount = @@ROWCOUNT
WHILE @iRowcount > 0
BEGIN
INSERT INTO @OrganizationDimKey
SELECT A.ProvanceOrganizationDimKey
FROM ProvanceOrganizationIsContainedByOrganizationFactvw A
WHERE A.DeletedDate IS NULL
AND A.ProvanceOrganizationDimKey not IN (SELECT OrganizationDimKey FROM @OrganizationDimKey)
AND A.OrganizationIsContainedByOrganization_ProvanceOrganizationDimKey IN (SELECT OrganizationDimKey FROM @OrganizationDimKey)
Const TimeZoneParameterName As String = "TimeZone"
Const SD_BaseTypeParameterName As String = "StartDate_BaseType"
Const SD_BaseValueParameterName As String = "StartDate_BaseValue"
Const SD_OffsetTypeParameterName As String = "StartDate_OffsetType"
Const SD_OffsetValueParameterName As String = "StartDate_OffsetValue"
Const ED_BaseTypeParameterName As String = "EndDate_BaseType"
Const ED_BaseValueParameterName As String = "EndDate_BaseValue"
Const ED_OffsetTypeParameterName As String = "EndDate_OffsetType"
Const ED_OffsetValueParameterName As String = "EndDate_OffsetValue"
Const IsRelativeTimeSupported As Boolean = False
Const TimeTypeParameterName As String = "TimeType"
Const TimeWeekMapParameterName As String = "TimeWeekMap"
Dim LocTables As System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)
Dim ReportTimeZone As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
Dim ReportStartDate As DateTime
Dim ReportEndDate As DateTime
Dim ReportTime As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
Dim ReportCulture As System.Globalization.CultureInfo
Dim ParameterProcessor As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor
Dim TargetList As String
Protected Overrides Sub OnInit()
LocTables = new System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)()
ReportTimeZone = Microsoft.EnterpriseManagement.Reporting.TimeZoneInformation.Current
ReportStartDate = DateTime.MinValue
ReportEndDate = DateTime.MinValue
ReportTime = Nothing
ReportCulture = System.Globalization.CultureInfo.GetCultureInfo(Report.User("Language"))
ParameterProcessor = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor(ReportCulture)
TargetList =Nothing
End Sub
'Public Function GetCallingManagementGroupId() As String
' Return 'Microsoft.EnterpriseManagement.Reporting.ReportingConfiguration.M'anagementGroupId
'End Function
Public Function GetReportLocLanguageCode() As String
Return ReportCulture.ThreeLetterWindowsLanguageName
End Function
Public Function GetReportLCID() As Integer
Return ReportCulture.LCID
End Function
Public Function GetLocTable(Name As String) As Microsoft.EnterpriseManagement.Reporting.XmlStringTable
Dim LocTable As Microsoft.EnterpriseManagement.Reporting.XmlStringTable
If Not LocTables.TryGetValue(Name, LocTable) Then
LocTable = New Microsoft.EnterpriseManagement.Reporting.XmlStringTable(LocTableStringQuery, Report.Parameters(Name).Value)
LocTables.Add(Name, LocTable)
End If
Return LocTable
End Function
Public Function GetReportTimeZone() As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
If IsNothing(ReportTimeZone) Then ReportTimeZone = Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation.FromValueString(Report.Parameters(TimeZoneParameterName).Value)
Return ReportTimeZone
End Function
Public Function ToDbDate(ByVal DateValue As DateTime) As DateTime
return GetReportTimeZone.ToUniversalTime(DateValue)
End Function
Public Function ToReportDate(ByVal DateValue As DateTime) As DateTime
return GetReportTimeZone.ToLocalTime(DateValue)
End Function
Public Function GetReportStartDate() As DateTime
If (ReportStartDate = DateTime.MinValue) Then
If (IsRelativeTimeSupported) Then
ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
Else
ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value)
End if
End If
return ReportStartDate
End Function
Public Function GetReportEndDate() As DateTime
If (ReportEndDate = DateTime.MinValue) Then
If (IsRelativeTimeSupported) Then
ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
If IsBusinessHours(GetReportTimeFilter()) Then ReportEndDate = ReportCulture.Calendar.AddDays(ReportEndDate, 1)
Else
ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value)
End if
End If
return ReportEndDate
End Function
Public Function GetReportTimeFilter() As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
If IsNothing(ReportTime) Then ReportTime = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime(Report.Parameters(TimeTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, CStr(Join(Report.Parameters(TimeWeekMapParameterName).Value, ",")))
return ReportTime
End Function
Public Function IsBusinessHours(Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As Boolean
return (Not IsNothing(Value)) And (Value.TimeType = Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTimeType.Business)
End Function
Public Function FormatDateTime(Format As String, Value As DateTime) As String
return Value.ToString(Format, ReportCulture)
End Function
Public Function FormatNumber(Format As String, Value As Decimal) As String
return Value.ToString(Format, ReportCulture)
End Function
Public Function FormatString(Format As String, ParamArray Values() as Object) As String
return String.Format(ReportCulture, Format, Values)
End Function
Public Function NullFormatString(Format As String, Value as String) As String
return IIF(String.IsNullOrEmpty(Value), String.Empty, String.Format(ReportCulture, Format, Value))
End Function
Public Function FormatBusinessHours(Format As String, Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As String
Dim result As String
If IsBusinessHours(Value) Then
Dim firstDay As DayOfWeek
Dim days As System.Collections.Generic.List(Of String)
firstDay = ReportCulture.DateTimeFormat.FirstDayOfWeek
days = new System.Collections.Generic.List(Of String)()
For loopDay As DayOfWeek = DayOfWeek.Sunday To DayOfWeek.Saturday
Dim day As DayOfWeek
day = CType((CInt(loopDay) + CInt(firstDay)) Mod 7, DayOfWeek)
If value.WeekMap.Contains(day) Then days.Add(ReportCulture.DateTimeFormat.GetAbbreviatedDayName(day))
Next loopDay
result = FormatString(Format, DateTime.Today.Add(Value.StartTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), DateTime.Today.Add(Value.EndTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), String.Join(",", days.ToArray()))
Else
result = String.Empty
End if
return result
End Function
Public Function BuildXmlValueList(ByVal ValueList() As Object) As String
Return Microsoft.EnterpriseManagement.Reporting.MultiValueParameter.ToXml("Data", "Value", ValueList)
End Function
REM -------------------------------------------------
Dim ReportFormatList As System.Collections.Generic.Dictionary(Of String, IDataFormatter)
Const FormatListParameterName As String = "DataFormat"
REM -------------------------------------------------
Public Interface IDataFormatter
Function FormatData(ByVal Value As String) As String
End Interface
Public Class RankDataFormatter
Implements IDataFormatter
Private Series As System.Collections.Generic.SortedDictionary(Of Decimal, String)
Private Culture As System.Globalization.CultureInfo
Public Sub New(ByVal Config As System.Xml.XmlNode, ByVal ReportCulture As System.Globalization.CultureInfo)
Culture = ReportCulture
Series = New System.Collections.Generic.SortedDictionary(Of Decimal, String)()
For Each SeriesXml As System.Xml.XmlNode In Config.ChildNodes
Series.Add(Decimal.Parse(SeriesXml.Attributes("Rank").Value), SeriesXml.Attributes("Format").Value)
Next
End Sub
Public Function FormatData(ByVal Value As String) As String Implements IDataFormatter.FormatData
Dim Result As String = CDec(Value).ToString("G", Culture)
For Each FormatItem As System.Collections.Generic.KeyValuePair(Of Decimal, String) In Series
Dim RunningValue As Decimal = Math.Round(Value / FormatItem.Key)
If RunningValue > 0 Then
Result = String.Format(Culture, FormatItem.Value, RunningValue.ToString("G", Culture))
Else
Exit For
End If
Next
Return Result
End Function
End Class
Public Class LookupDataFormatter
Implements IDataFormatter
Private MappingTable As System.Collections.Generic.IDictionary(Of String, String)
Public Sub New(ByVal Config As System.Xml.XmlNode)
MappingTable = New System.Collections.Generic.Dictionary(Of String, String)(StringComparer.OrdinalIgnoreCase)
For Each SeriesXml As System.Xml.XmlNode In Config.ChildNodes
MappingTable.Add(Decimal.Parse(SeriesXml.Attributes("Value").Value), SeriesXml.Attributes("Label").Value)
Next
End Sub
Public Function FormatData(ByVal Value As String) As String Implements IDataFormatter.FormatData
Dim Result As String
If MappingTable.ContainsKey(Value) Then
Result = MappingTable(Value)
Else
Result = Value
End If
Return Result
End Function
End Class
Public Function GetReportFormatList() As System.Collections.Generic.Dictionary(Of String, IDataFormatter)
If IsNothing(ReportFormatList) Then
Dim Xml As System.Xml.XmlDocument = New System.Xml.XmlDocument()
Xml.LoadXml(Report.Parameters(FormatListParameterName).Value)
ReportFormatList = New System.Collections.Generic.Dictionary(Of String, IDataFormatter)(StringComparer.OrdinalIgnoreCase)
For Each Node As System.Xml.XmlNode In Xml.DocumentElement.ChildNodes
Dim Formatter As IDataFormatter = Nothing
Select Case Node.Attributes("Type").Value.ToUpper()
Case "RANK"
Formatter = New RankDataFormatter(Node, ReportCulture)
Case "LOOKUP"
Formatter = New LookupDataFormatter(Node)
End Select
If Not IsNothing(Formatter) Then
ReportFormatList.Add(Node.Attributes("Name").Value, Formatter)
End If
Next
End If
Return ReportFormatList
End Function
Public Function FormatData(ByVal FormatName As String, ByVal DataType As String, ByVal Value As String)
Dim FormatList As System.Collections.Generic.Dictionary(Of String, IDataFormatter) = GetReportFormatList()
If Not String.IsNullOrEmpty(FormatName) And Not IsNothing(FormatList) Then
If FormatList.ContainsKey(FormatName) Then
Return FormatList(FormatName).FormatData(Value)
End If
End If
If Not String.IsNullOrEmpty(DataType) Then
If DataType = "DateTime" Then
Return FormatDateTime("g", CDate(Value))
ElseIf DataType.StartsWith("UInt") Then
Return FormatNumber("G", Value)
End If
End If
Return Value
End Function
REM ----------------------------------------
Public Dim CurrentObjectList As String
Public Function InitList(ByRef List As String) As String
List = String.Empty
Return List
End Function
Public Function AddListItem(ByRef List As String, Item as String) As String
List = List + Item
Return List
End Function
Public Function GetObjectList(ByVal OptionsXml As String) As String()
Dim Xml As System.Xml.XmlDocument
Xml = New System.Xml.XmlDocument()
Xml.LoadXml(OptionsXml)
Dim Result As System.Collections.Generic.List(Of String)
Result = New System.Collections.Generic.List(Of String)
For Each ObjectNode As System.Xml.XmlNode In Xml.SelectNodes("/Value/Object")
If Not Result.Contains(ObjectNode.InnerText) Then
Result.Add(ObjectNode.InnerText)
End If
Next
Return Result.ToArray()
End Function
</Script></Code>
<Language>en-US</Language>
<CodeModules>
<CodeModule>Microsoft.EnterpriseManagement.Reporting.Code, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</CodeModule>
<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>
</CodeModules>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>9bf3e609-5344-43d6-b1aa-066d834bca0b</rd:ReportID>
</Report>
Home