CREATE PROCEDURE [dbo].[usp_DM_CheckDocumentAccessForUser]
-- Add the parameters for the stored procedure here
@ContactCode BIGINT,
@DocumentCode BIGINT,
@IsAdmin BIT,
@IsSupplier BIT,
@ACEntityId INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @AccessFlag AS INT
-- Access Flag for Document Security
/*
0 - No Document Found (Document Not Exist in DB)
1 - Access Allowed
2 - Document is Deleted 
3 - Access Not Allowed
*/

-- By default we will set Access Flag as No Document Found
SET @AccessFlag = 0;

IF (@IsSupplier = 1)
BEGIN
-- Validation for Supplier
DECLARE @UsersPartnerCode as BIGINT
SELECT @UsersPartnerCode = PartnerCode 
FROM PRN_Contacts WHERE ContactCode = @ContactCode

IF EXISTS(SELECT 1
FROM dbo.DM_Documents DC
INNER JOIN dbo.DM_DocumentStakeholder DS
ON DC.DocumentCode = DS.DocumentCode 
WHERE DC.IsDeleted = 0 AND DC.IsTemplate = 0 AND  
(DC.IsSupplierVisible = 1 or DC.DocumentTypeCode =8) AND DS.IsDeleted=0 AND DC.DocumentCode = @DocumentCode AND
(
(DS.ContactCode = @ContactCode AND DS.StakeholderType IN (1,2) ) 
OR
(DS.PartnerCode = @UsersPartnerCode AND DS.StakeholderType=3 ) 
) AND
(DC.DocumentTypeCode <> 5 OR DC.DocumentStatus <> 1)
)
BEGIN
SET @AccessFlag = 1;
END
END
ELSE
BEGIN
-- Validation for Buyer
IF EXISTS(SELECT 1
FROM dbo.DM_Documents DC
LEFT JOIN dbo.DM_DocumentStakeholder DS
ON DC.DocumentCode = DS.DocumentCode AND 
(DS.ContactCode = @ContactCode AND DS.StakeholderType IN (4,5,7,8,9,11,12,13,15)
--Consider Proxy in case of Team member and Approver
OR (StakeholderType IN (4,5) AND ProxyContactCode = @ContactCode)) 
AND DS.IsDeleted=0
LEFT JOIN dbo.DM_DocumentPas DPAS
ON DC.DocumentCode = DPAS.DocumentCode
LEFT JOIN dbo.PRN_ContactsPASMapping CPM
ON DPAS.PasCode = CPM.PASCode and CPM.IsActive =1
LEFT JOIN dbo.DM_DocumentBU DBU
ON DC.DocumentCode = DBU.DocumentCode
LEFT JOIN PRN_ContactORGMapping COM
ON DBU.BUCode = COM.OrgEntityCode AND COM.ContactCode = @ContactCode
LEFT JOIN dbo.ORG_EntityDetails CBU
ON COM.OrgEntityCode = CBU.EntityDetailCode AND CBU.EntityId=@ACEntityId
LEFT JOIN dbo.DM_DocumentRegion DR
ON DC.DocumentCode = DR.DocumentCode
LEFT JOIN PRN_ContactRegionMapping CRM
ON DR.RegionCode = CRM.RegionId AND CRM.ContactCode = @ContactCode
WHERE DC.IsDeleted = 0 AND (DC.IsBuyerVisible = 1  or DC.DocumentTypeCode =8) AND 
DC.DocumentCode = @DocumentCode AND 
(
( -- Access Control For Repository mode
DC.IsTemplate = 0 AND
(
--Below condition returns Forms without any access control
(DC.DocumentTypeCode = 11) OR
--Below Condition is to show non confidential scorecard to all user if published
(DC.DocumentTypeCode = 12  AND DC.IsConfidential = 0 AND DC.DocumentStatus = 44) OR
--Below condition returns the documents that are created by the 
--Contact Code passed
(DC.Creator = @ContactCode) OR
--Below condition returns the documents in which the passed contact code
-- is Approver Or Team Member Or Buyer Or Receiver or Requester 
(DS.DocumentStakeholderId IS NOT NULL) OR
--Below condition will return non-P2P, Non- Contracts and Non-Forms documents based on Access control i.e. Category, Region and BU
--Catelog = 6,Requisition = 7,PO = 8,Invoice = 9,
(DC.DocumentTypeCode NOT IN (5,6,7,8,9,10,12) AND CPM.PASCode IS NOT NULL AND CRM.RegionId IS NOT NULL AND CBU.EntityDetailCode IS NOT NULL) OR
--Below condition will return Contract documents based on Access control i.e. Category, Region and BU only if the contract is not confidential
(DC.DocumentTypeCode = 5 AND DC.IsConfidential = 0 AND CPM.PASCode IS NOT NULL AND CRM.RegionId IS NOT NULL AND CBU.EntityDetailCode IS NOT NULL) OR
--Below condition will return P2P documents based on Access control i.e. BU
(DC.DocumentTypeCode IN (6,7,8,9,10,14,15) AND CBU.EntityDetailCode IS NOT NULL) 
)
)
OR
( -- Access Control For Template
DC.IsTemplate = 1 AND
(
--For Admin return all Templates
@IsAdmin = 1 OR
(
-- For Non- Admin access control will be on the basis of Document Type
-- Except for Item, Form, Assessment and Contract Templates the ACS will be based on Category
(DC.DocumentTypeCode < 11 AND DC.DocumentTypeCode <> 5 AND CPM.PASCode IS NOT NULL) OR
--For Contract Templates , Access control will be on Category, Region and BU
(DC.DocumentTypeCode = 5 AND CPM.PASCode IS NOT NULL AND CRM.RegionId IS NOT NULL AND CBU.EntityDetailCode IS NOT NULL) OR
-- For Item Type Templates the ACS will be based on BU and only Published (44) Templates will be visible
(DC.DocumentTypeCode = 13 AND CBU.EntityDetailCode IS NOT NULL AND DC.DocumentStatus = 44) OR
-- For Forms and Assessment, ACS is open
(DC.DocumentTypeCode IN (11,12))
)
)
)
)
)
BEGIN
SET @AccessFlag = 1;
END
END


IF (@AccessFlag = 0)
BEGIN
-- Check if Document is Deleted than set flag accordingly
IF EXISTS(SELECT 1 FROM DM_Documents WHERE DocumentCode = @DocumentCode AND IsDeleted = 1)
BEGIN
SET @AccessFlag = 2;
END
ELSE IF EXISTS(SELECT 1 FROM DM_Documents WHERE DocumentCode = @DocumentCode)
BEGIN
SET @AccessFlag = 3;
END

END

SELECT @AccessFlag AS ACCESS

END

Can you please explain an little what you problem is? The SP is running longer compared to what? What are the exact symptoms. What troubleshooting have you done already?

Thanks,
Jan

Can you please explain a bit more what problem you are encountering? The SP is running longer compared to what? What are the exact symptoms. What troubleshooting have you done already?

Thanks,
Jan

• Edited by Jan EngelsbergMicrosoft employee 15 hours 13 minutes ago

Can you please explain a bit more what problem you are encountering? The SP is running longer compared to what? What are the exact symptoms. What troubleshooting have you done already?

Thanks,
Jan

• Edited by Jan EngelsbergMicrosoft employee 15 hours 24 minutes ago

Can you please explain a bit more what problem you are encountering? The SP is running longer compared to what? What are the exact symptoms. What troubleshooting have you done already?

Thanks,
Jan

• Edited by Jan EngelsbergMicrosoft employee Wednesday, July 08, 2015 3:54 PM

Can you please explain a bit more what problem you are encountering? The SP is running longer compared to what? What are the exact symptoms. What troubleshooting have you done already?

Thanks,
Jan

• Edited by Jan EngelsbergMicrosoft employee Wednesday, July 08, 2015 3:54 PM

Can you please explain a bit more what problem you are encountering? The SP is running longer compared to what? What are the exact symptoms. What troubleshooting have you done already?

Thanks,
Jan

• Edited by Jan EngelsbergMicrosoft employee Wednesday, July 08, 2015 3:54 PM

Raj,

Can you Explain what problem you are facing in your procedure. 

Regards

ChetanV

Hi Chetan,

This proc is a nested one to the main SP. So while tuning, its observed that the above proc takes longer time to execute. Also only the below part in the proc delays the execution time. if i comment this portion then it whole procedures takes only 10 sec for execution. Below code is present in the where clause.

Note: though its a OR condition.

--Below condition returns the documents in which the passed contact code
-- is Approver Or Team Member Or Buyer Or Receiver or Requester 

(DS.DocumentStakeholderId IS NOT NULL) OR
--Below condition will return non-P2P, Non- Contracts and Non-Forms documents based on Access control i.e. Category, Region and BU
--Catelog = 6,Requisition = 7,PO = 8,Invoice = 9,
(DC.DocumentTypeCode NOT IN (5,6,7,8,9,10,12) AND CPM.PASCode IS NOT NULL AND CRM.RegionId IS NOT NULL AND CBU.EntityDetailCode IS NOT NULL) OR
--Below condition will return Contract documents based on Access control i.e. Category, Region and BU only if the contract is not confidential
(DC.DocumentTypeCode = 5 AND DC.IsConfidential = 0 AND CPM.PASCode IS NOT NULL AND CRM.RegionId IS NOT NULL AND CBU.EntityDetailCode IS NOT NULL) OR
--Below condition will return P2P documents based on Access control i.e. BU
(DC.DocumentTypeCode IN (6,7,8,9,10,14,15) AND CBU.EntityDetailCode IS NOT NULL) 
)

Raj,

Have you looked at the query plan to see what is the difference when you take out the conditions (see here on how to get them)?

When you say it takes longer to execute: What do you compare to? What has changed in the data or query before you observed the change in execution time?

Have you checked you that you have appropriate indexes in place for the joins and where clause (see here on how to find missing indexes)?

Jan

Raj,

Have you looked at the query plan to see what is the difference when you take out the conditions (see here on how to get them)?

When you say it takes longer to execute: What do you compare to? What has changed in the data or query before you observed the change in execution time?

Have you checked you that you have appropriate indexes in place for the joins and where clause (see here on how to find missing indexes)?

Jan

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator 21 hours 53 minutes ago

Raj,

Have you looked at the query plan to see what is the difference when you take out the conditions (see here on how to get them)?

When you say it takes longer to execute: What do you compare to? What has changed in the data or query before you observed the change in execution time?

Have you checked you that you have appropriate indexes in place for the joins and where clause (see here on how to find missing indexes)?

Jan

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator Friday, July 10, 2015 9:14 AM

Raj,

Have you looked at the query plan to see what is the difference when you take out the conditions (see here on how to get them)?

When you say it takes longer to execute: What do you compare to? What has changed in the data or query before you observed the change in execution time?

Have you checked you that you have appropriate indexes in place for the joins and where clause (see here on how to find missing indexes)?

Jan

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator Friday, July 10, 2015 9:14 AM
• Marked as answer by Girish PrajwalMicrosoft contingent staff, Moderator 16 hours 58 minutes ago

Raj,

Have you looked at the query plan to see what is the difference when you take out the conditions (see here on how to get them)?

When you say it takes longer to execute: What do you compare to? What has changed in the data or query before you observed the change in execution time?

Have you checked you that you have appropriate indexes in place for the joins and where clause (see here on how to find missing indexes)?

Jan

• Proposed as answer by Girish PrajwalMicrosoft contingent staff, Moderator Friday, July 10, 2015 9:14 AM
• Marked as answer by Girish PrajwalMicrosoft contingent staff, Moderator Monday, July 13, 2015 2:09 PM