Business Portal Requisitions – Tables, and All Requisitions SQL View

In Business Portal, Requisitions Management, several tables are deployed to manage the requisition process until the requisition is migrated into Dynamics GP. In this post, I am listing down Requisitions Tables along with SQL view for all Requisitions Details linked to GP Purchase Order.

 

All requisitions Tables are included in the Dynamics DB, starting with a prefix ‘ReqMgmt’ as shown below;

- ReqMgmtApprovalTree   
- ReqMgmtApprovalTreeDefaultNode   
- ReqMgmtApprovalTreeNode
- ReqMgmtAuditHistoryDocument
- ReqMgmtAuditHistoryLines
- ReqMgmtDocument
- ReqMgmtLines
- ReqMgmtOpenTasks
- ReqMgmtSetupCompany
- ReqMgmtSetupGenericRole
- ReqMgmtSetupSystem
- ReqMgmtSetupUser

Resource Definitions

ReqMgmtDocument Table – Status Field

1 = Created
2 = Pending for Final Approval
3 = Rejected
4 = Pending Transfer To PO
5 = Transferred To PO
6 = Voided

ReqMgmtLines Table – LineStatus Field

1 = Created
2 = Final Approved
3 = Transferred To PO
4 = Voided

SQL View for All Requisitions and Details  - with PO Number

The following script retrieves all Requisitions with Line Item details (inventorial and non inventorial), their status and next assignment in workflow as long the associated Purchase Order Number in Dynamics GP.

--- Tables Included':

• DYNAMICS..ReqMgmtDocument
• DYNAMICS..ReqMgmtLines
• DYNAMICS..SY01500
• COMPANYDB..IV00101

SELECT  A.CompanyID AS CompanyID,
        C.CMPNYNAM AS CompanyName,
        A.DocumentID AS RequisitionID,
        B.PurchaseOrderDocument,
        CASE A.Status
                WHEN 1 THEN 'Created'
                WHEN 2 THEN 'Pending Final Approval'
                WHEN 3 THEN 'Rejected'
                WHEN 4 THEN 'Pending Transfer To PO'
                WHEN 5 THEN 'Transferred To PO'
                WHEN 6 THEN 'Voided'
                ELSE        'NONE'
                END ReqStatus,
        A.CREATEDDATE AS Req_CreatedDate,
        A.OriginatingName AS CreatedBy,
        A.ModifiedDate AS Req_ModifiedDate,
        A.LastToModifyName AS LastModifiedBy,
        A.CurrentAssignedName AS CurrentlyAssignedTo,
        A.Comment AS ReqComment,
        A.CopiedFrom AS CopiedFromReq,
        B.Item,
        CASE ISNULL(D.ITEMNMBR,'NULL')
               WHEN 'NULL' THEN 'NonInventorial'
               ELSE             'Inventorial'
               END ItemType,
        B.ItemDescription,
        B.ItemText AS ItemText,
        B.ItemComment as LineItemComment,
        B.Quantity,
        B.UnitPrice,
        B.ExtendedPrice,
        B.UnitOfMeasure,
        B.Site,
        B.Vendor,
        CASE B.LineStatus
               WHEN 1 THEN 'Created'
               WHEN 2 THEN 'Final Approved'
               WHEN 3 THEN 'Transfered To PO'
               WHEN 4 THEN 'Voided'
               ELSE        'NONE'
               END AS ReqLineStatus,   
        A.CurrentAssignedName AS CurrentlyAssignedTo,
        B.RequiredBy,
        B.PurchaseAccount AS PurchaseAccountIndex
        FROM DYNAMICS..ReqMgmtDocument  AS A
LEFT OUTER JOIN DYNAMICS..ReqMgmtLines  AS B
ON A.DocumentID = B.DocumentID
LEFT OUTER JOIN DYNAMICS..SY01500 AS C
ON A.CompanyID = C.CMPANYID
LEFT OUTER JOIN IV00101 AS D
ON B.Item = D.ITEMNMBR
ORDER BY CompanyName, RequisitionID

 

Best Regards,

Mahmoud M. AlSaadi