Business Portal Requisitions – Approval Hierarchy SQL View

 

In Requisition Management, Approval Hierarchy is managed on the Top-Level Business Portal web site by considering the following groups (Requisition Creator, Approver and Purchaser) in order to draw the route through which a requisition will flow and get approvals before being migrated to Purchase Order in Dynamics GP. As follows, I provide an SQL View for the Approval Hierarchy for each Dynamics GP Company.

SQL View for Requisition Management – Approval Hierarchy

The following script retrieves the Approval Hierarchy as setup under company settings. Each dynamics GP has one active approval hierarchy.

--- Tables Included:

• DYNAMICS..ReqMgmtApprovalTreeNode
• DYNAMICS..ReqMgmtApprovalTreeDefaultNode
• DYNAMICS..ReqMgmtApprovalTree
• DYNAMICS..MbfUser
• DYNAMICS..SY01500

 

SELECT  Y.GPCompanyID,
        Y.GPCompanyName,
        X.ApprovalHierarchy,
        X.UserName,
        X.ApproverName
        FROM
            (SELECT E.Text AS ApprovalHierarchy,
                    ISNULL(D.Name,A.Text) AS UserName,
                    ISNULL(C.Name,'') AS ApproverName,
                    A.ResidentID,
                    B.ResidentID AS Parent_ID,
                    ISNULL(A.ParentID,'') AS ParentID ,
                    A.PersistentTreeID,
                    A.GroupRootID,
                    E.ID
                    FROM ReqMgmtApprovalTreeNode AS A
                    LEFT OUTER JOIN
                    ReqMgmtApprovalTreeDefaultNode AS B
                    ON A.ParentID = B.PersistentTreeNodeID
                    LEFT OUTER JOIN MbfUser AS C
                    ON B.ResidentID = C.ID
                    LEFT OUTER JOIN MbfUser AS D
                    ON A.ResidentID = D.ID
                    LEFT OUTER JOIN
                    ReqMgmtApprovalTree AS E
                    ON A.PersistentTreeID = E.ID
                    ) AS X LEFT OUTER JOIN
            (SELECT B.CompanyID AS GPCompanyID,
                    C.CMPNYNAM AS GPCompanyName,
                    A.Text AS ApprovalHierarchy
                    FROM ReqMgmtApprovalTree AS A
                    LEFT OUTER JOIN
                    ReqMgmtSetupCompany AS B
                    ON A.ID = B.ApprovalTreeID
                    LEFT OUTER JOIN SY01500 AS C
                    ON B.CompanyID = C.CMPANYID
             ) AS Y
            ON X.ApprovalHierarchy = Y.ApprovalHierarchy
            ORDER BY Y.GPCompanyID, X.ApproverName

 

Best Regards,
Mahmoud M. AlSaadi