IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_AAW_SP_GetPendingApplicationRequests]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].ServiceManager_Report_AAW_SP_GetPendingApplicationRequests
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_GetPendingApplicationRequests
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 IN ('ServiceRequestStatusEnum.New', 'ServiceRequestStatusEnum.OnHold', 'ServiceRequestStatusEnum.InProgress', 'ServiceRequestStatusEnum.Submitted')
AND ApprovalStatus.ApprovalResultStatusValue NOT IN ('Approved', 'Denied')
END
GO
USE [DWDataMart]
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_AAW_SP_GetPendingApplicationRequests TO reportuser
GO
Home