Business Portal Requisitions – User Properties

 

In Requisition Management, user properties are managed under user-list web part. In this post, I provide an SQL View for requisition users properties.

Resource Definitions: 

ReqMgmtSetupUser Table - Account Access, Item Number Access, Price Access, Site Access and Vendor Access Fields

1 = Editable
2 = Not Editable
3 = Hidden

ReqMgmtSetupUser Table – Has Final Approval, Can Change Approver, Is Final Approver, Is Extended Approver Fields

1 = Yes
2 = No

SQL View for Requisition Management User Properties

The following script retrieves the properties for all requisition management users. As retrieved from Requisition Management Setup User Table.

---  Tables Included:

• DYNAMICS..ReqMgmtSetupUser
• DYNAMICS..MbfUser

USE DYNAMICS
SELECT  Name AS UserName,
        CASE HasApprovalLimit
            WHEN 1 THEN 'Yes'
            WHEN 0 THEN 'No'
            ELSE 'None' END AS HasApprovalLimit,
        ApprovalLimit,
        CASE CanChangeApprover
            WHEN 1 THEN 'Yes'
            WHEN 0 THEN 'No'
            ELSE 'None' END AS CanChangeApprover,
        CASE IsFinalApprover
            WHEN 1 THEN 'Yes'
            WHEN 0 THEN 'No'
            ELSE 'None' END AS IsFinalApprover,
        CASE IsExtendedApprover
            WHEN 1 THEN 'Yes'
            WHEN 0 THEN 'No'
            ELSE 'None' END AS IsExtendedApprover,
        CASE AccountAccess
            WHEN 1 THEN 'Editable'
            WHEN 2 THEN 'Not Editable'
            WHEN 3 THEN 'Hidden'
            ELSE 'None' END AS AccountAccess,
        CASE ItemNumberAccess
            WHEN 1 THEN 'Editable'
            WHEN 2 THEN 'Not Editable'
            WHEN 3 THEN 'Hidden'
            ELSE 'None' END AS ItemNumberAccess,
        CASE PriceAccess
            WHEN 1 THEN 'Editable'
            WHEN 2 THEN 'Not Editable'
            WHEN 3 THEN 'Hidden'
            ELSE 'None' END AS PriceAccess,
        CASE SiteAccess
            WHEN 1 THEN 'Editable'
            WHEN 2 THEN 'Not Editable'
            WHEN 3 THEN 'Hidden'
            ELSE 'None' END AS SiteAccess,   
        CASE VendorAccess
            WHEN 1 THEN 'Editable'
            WHEN 2 THEN 'Not Editable'
            WHEN 3 THEN 'Hidden'
            ELSE 'None' END AS VendorAccess,
        AllowSelfFinalApproval   
        FROM DYNAMICS..ReqMgmtSetupUser AS A
        LEFT OUTER JOIN DYNAMICS..MbfUser AS B
        ON A.ID = B.ID

Best Regards,
Mahmoud M. AlSaadi