IF EXISTS
(
SELECT [name]
FROM [sys].[objects]
WHERE
[object_id] = OBJECT_ID(N'[dbo].[FIM_Report_MPR_SP_GetHistory]')
AND [type] in (N'P', N'PC')
)
BEGIN
DROP PROCEDURE [dbo].[FIM_Report_MPR_SP_GetHistory];
END
GO
BEGIN
--********************************************************
--* *
--* Copyright (C) Microsoft. All rights reserved. *
--* *
--********************************************************
/**********************************************************************************************************************
* http://sharepoint/sites/fim/Shared%20Documents/FIM%202010%20R2%20Technical%20Docs/Reporting/Functional%20Specs/Reporting_Spec_FIM2010_R2_M0.docm
* 9.7.3 REPORT DESIGN
* 9.7.3.2 DEFAULTS
* Report Column Descriptions
* Default Description
* Primary Sort Order Committed Date, Most Recent at top
*
* Secondary Sort Order Attribute Name, A at top
*
* Filter Unfiltered by default
*
* 9.6.3.3 FILTERING CRITERIA
* All columns in the report must be filterable.
*
* Current Management Policy Rule Display Name
* Management Policy Rule Object ID
* Operation Type
* Attribute Name
* Attribute Value
* Committed Date
* Request Object ID
* Requestor Display Name
* Approver Display Names
* Management Policy Rule Names
*
* Extended Data - Requested Additions by Test team
* ExtendedDataRequestorID (Guid as nvarchar(40))
* ExtendedDataApproverIDs (semicolon separated Guids as nvarchar(40))
* ExtendedDataManagementPolicyRuleIDs comma (semicolon separated Guids as nvarchar(40))
*
* 9.6.3.5 REMOVAL OF EXTENDED DATA PARAMETER
* REQUIREMENT OF START END DATE
* Addition of RequestTargetDetailObjectType (Target), limiting the RTD Data to @PersonTypeDimKey
******************************************************************************************************************/
SET NOCOUNT ON;
BEGIN TRY
/*****************************************************************************************************************
*
* 1. Define tables and variables and set defaults
*
*****************************************************************************************************************/
IF(@attributeName = N'*')
BEGIN
SELECT @attributeName = NULL;
END
-- Set committed date end to 1 day + input time.
-- This is done because SQL server assumes @committedDateEnd 12:00:00 am
-- and data from the last day are omitted.
SELECT @committedDateEnd = DATEADD(day, 1, @committedDateEnd);
--Get the ObjectTypeDIMKey for MPR
DECLARE @mprTypeDimKey INT;
SELECT @mprTypeDimKey =
(
SELECT TOP 1 [otv].[FIMObjectTypeDIMKey]
FROM [dbo].[FIMObjectTypeDimvw] AS [otv]
INNER JOIN [dbo].[FIMManagementPolicyRuleDimvw] AS [p] ON
[otv].[FIMObjectID] = [p].[FIMObjectTypeID]
)
/*****************************************************************************************************************
*
* 2. Execute the query
*
*****************************************************************************************************************/
BEGIN
SELECT DISTINCT
[MPR].[FIMDisplayName] AS [Current Management Policy Rule Display Name],
[MPR].[FIMObjectID] AS [Management Policy Rule Object ID],
[RTD].[FIMRequestRequestStatusMode] AS [Operation Type],
[AT].[FIMAttributeTypeName] AS [Attribute Name],
[RTD].[FIMRequestTargetDetailAttributeValue] AS [Attribute Value],
[Rf].[FIMRequestCommittedTime] AS [Committed Date],
[R].[FIMObjectID] AS [Request Object ID],
[RCe].[FIMDisplayName] AS [Requestor Display Name],
[RA].[ARCDISPLAYNAMES] AS [Approver Display Names],
[RMPR].[MPRDISPLAYNAMES] AS [Management Policy Rule Names],
[RCe].[FIMObjectID] AS [ExtendedDataRequestorID],
[RA].[FIMAPPROVALRESPONSECREATOROBJECTIDS] AS [ExtendedDataApproverIDs],
[RMPR].[MPROBJECTIDS] AS [ExtendedDataManagementPolicyRuleIDs]
FROM [dbo].[FIMManagementPolicyRuleDimvw] AS [MPR]
INNER JOIN [dbo].[FIMRequestTargetDetailFactvw] AS [RTD] ON
[MPR].[FIMObjectID] = [RTD].[FIMRequestTargetDetailTarget]
INNER JOIN [dbo].[FIMAttributeTypeDimvw] AS [AT] ON
[RTD].[FIMAttributeTypeDimKey] = [AT].[FIMAttributeTypeDimKey]
INNER JOIN [dbo].[FIMRequestFactvw] AS [Rf] ON
[RTD].[FIMRequestDimKey] = [Rf].[FIMRequestDimKey]
INNER JOIN [dbo].[FIMRequestDimvw] AS R ON
[Rf].[FIMRequestDimKey] = [R].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMEntityDimvw] AS [RCe] ON
[Rf].[FIMCreator] = [RCe].[FIMObjectID]
LEFT OUTER JOIN [dbo].[FIMRequestApprovalResponseCreatorvw] AS [approver] ON -- Match single Approver
[Rf].[FIMRequestDimKey] = [approver].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMRequestMprvw] AS [managementPolicyRule] ON -- Match single MPR
[Rf].[FIMRequestDimKey] = [managementPolicyRule].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMRequestApprovalResponseCreatorsvw] AS RA ON -- Get Approvers
[Rf].[FIMRequestDimKey] = [RA].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMRequestMprsvw] AS [RMPR] ON -- Get MPRs
[Rf].[FIMRequestDimKey] = [RMPR].[FIMRequestDimKey]
WHERE
[Rf].[FIMRequestCommittedTime] >= @committedDateStart
AND [Rf].[FIMRequestCommittedTime] < @committedDateEnd
AND [RTD].[FIMObjectTypeDimKey] = @mprTypeDimKey
AND
(
@currentManagementPolicyRuleDisplayName IS NULL
OR [MPR].[FIMDisplayName] = @currentManagementPolicyRuleDisplayName
)
AND
(
@managementPolicyRuleObjectID IS NULL
OR [MPR].[FIMObjectID] = @managementPolicyRuleObjectID
)
AND
(
@operationType IS NULL
OR [RTD].[FIMRequestRequestStatusMode] = @operationType
)
AND
(
@attributeName IS NULL
OR [AT].[FIMAttributeTypeName] = @attributeName
)
AND
(
@attributeValue IS NULL
OR [RTD].[FIMRequestTargetDetailAttributeValue] = @attributeValue
)
AND
(
@requestObjectID IS NULL
OR [R].[FIMObjectID] = @requestObjectID
)
AND
(
@requestorDisplayName IS NULL
OR [RCe].[FIMDisplayName] = @requestorDisplayName
)
AND
(
@approverDisplayName IS NULL
OR [approver].[FIMApprovalResponseCreatorDisplayName] = @approverDisplayName
)
AND
(
@managementPolicyRuleName IS NULL
OR [managementPolicyRule].[MPRDisplayName] = @managementPolicyRuleName
)
ORDER BY [Rf].[FIMRequestCommittedTime] DESC, [AT].[FIMAttributeTypeName];
END
END TRY
BEGIN CATCH
DECLARE @errorMessage NVARCHAR(MAX);
SET @errorMessage = ERROR_MESSAGE();
RAISERROR(@errorMessage, 16, 1);
END CATCH
END
GO
GRANT EXECUTE ON [dbo].[FIM_Report_MPR_SP_GetHistory] TO reportuser
GO
Home