Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Sunday, November 3, 2013

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 E.Text AS ApprovalHierarchy,
                    ISNULL(D.Name,A.Text) AS UserName,
                    ISNULL(C.Name,'') AS ApproverName,
                    B.ResidentID AS Parent_ID,
                    ISNULL(A.ParentID,'') AS ParentID ,
                    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

No comments:

Post a Comment