ServiceManager.Report.AAW.SP.ApprovedDeniedApplicationRequests.Install (ReportResource)

Element properties:

TypeReportResource
File NameServiceManager.Report.AAW.SP.GetApprovedDeniedApplicationRequests.Install.sql
AccessibilityPublic

Source Code:

<ReportResource ID="ServiceManager.Report.AAW.SP.ApprovedDeniedApplicationRequests.Install" Accessibility="Public" FileName="ServiceManager.Report.AAW.SP.GetApprovedDeniedApplicationRequests.Install.sql" HasNullStream="false" MIMEType="application/octet-stream"/>

File Content: ServiceManager.Report.AAW.SP.GetApprovedDeniedApplicationRequests.Install.sql

USE [DWDataMart]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_AAW_SP_GetApprovedDeniedApplicationRequests]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].ServiceManager_Report_AAW_SP_GetApprovedDeniedApplicationRequests
GO

USE [DWDataMart]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Microsoft
-- Create date:
-- Last Updated : 4/5/2012
-- Description: Returns list of Approved Denied Application Requests
-- =============================================
CREATE PROCEDURE [dbo].ServiceManager_Report_AAW_SP_GetApprovedDeniedApplicationRequests
AS
BEGIN

SELECT AppReq.RequestId, UserDim.UserName, App.Name, ApprovalStatus.ApprovalResultStatusValue, SR.Description, CONVERT(date, AppReq.CreatedDate) AS CreatedDate
FROM AAW_ApplicationRequestvw AS AppReq
LEFT JOIN AAW_ApplicationRequestHasRequestingUserFactvw AppHasRequestingUser
ON AppReq.AAW_ApplicationRequestKey = AppHasRequestingUser.AAW_ApplicationRequestKey
AND AppReq.IsDeleted = 0
LEFT JOIN ServiceRequestDim AS SR
ON SR.EntityDimKey = AppReq.EntityDimKey
LEFT JOIN UserDim
ON AppHasRequestingUser.ApplicationRequestHasRequestingUser_UserDimKey = UserDim.UserDimKey
LEFT JOIN AAW_ApplicationRequestHasRequestedApplicationFactvw AppReqHasApp
ON AppReq.AAW_ApplicationRequestKey = AppReqHasApp.AAW_ApplicationRequestKey
AND AppReqHasApp.DeletedDate IS NULL
LEFT JOIN AAW_Applicationvw App
ON AppReqHasApp.ApplicationRequestHasRequestedApplication_AAW_ApplicationKey = App.AAW_ApplicationKey
JOIN ApprovalResultStatusvw ApprovalStatus
ON ApprovalStatus.ApprovalResultStatusId = AppReq.ApprovalResult_ApprovalResultStatusId
AND AppReq.Status = 'ServiceRequestStatusEnum.Completed'
AND ApprovalStatus.ApprovalResultStatusValue = 'Approved'
UNION
SELECT AppReq.RequestId, UserDim.UserName, App.Name, ApprovalStatus.ApprovalResultStatusValue, SR.Description, CONVERT(date, AppReq.CreatedDate) AS CreatedDate
FROM AAW_ApplicationRequestvw AS AppReq
LEFT JOIN AAW_ApplicationRequestHasRequestingUserFactvw AppHasRequestingUser
ON AppReq.AAW_ApplicationRequestKey = AppHasRequestingUser.AAW_ApplicationRequestKey
AND AppReq.IsDeleted = 0
LEFT JOIN ServiceRequestDim AS SR
ON SR.EntityDimKey = AppReq.EntityDimKey
LEFT JOIN UserDim
ON AppHasRequestingUser.ApplicationRequestHasRequestingUser_UserDimKey = UserDim.UserDimKey
LEFT JOIN AAW_ApplicationRequestHasRequestedApplicationFactvw AppReqHasApp
ON AppReq.AAW_ApplicationRequestKey = AppReqHasApp.AAW_ApplicationRequestKey
AND AppReqHasApp.DeletedDate IS NULL
LEFT JOIN AAW_Applicationvw App
ON AppReqHasApp.ApplicationRequestHasRequestedApplication_AAW_ApplicationKey = App.AAW_ApplicationKey
JOIN ApprovalResultStatusvw ApprovalStatus
ON ApprovalStatus.ApprovalResultStatusId = AppReq.ApprovalResult_ApprovalResultStatusId
AND AppReq.Status = 'ServiceRequestStatusEnum.Failed'
AND ApprovalStatus.ApprovalResultStatusValue = 'Denied'

END
GO

USE [DWDataMart]
GO

GRANT EXECUTE ON dbo.ServiceManager_Report_AAW_SP_GetApprovedDeniedApplicationRequests TO reportuser
GO