Importing Fixed Assets which are fully depreciated in Prior Years.

 

When importing Fixed Assets into Dynamics GP, several assets may be fully depreciated in “prior years”, which means (Depreciated to Date) will fall in prior years.

Based on your calculation, the asset is supposed to be fully depreciated at (let’s say) 10/02/2005.

The following error message pops up stating that (Depreciated to Date) should be the last day of the year, as shown below;

 

Solution:

All dates within prior years won’t be accept but (31/12). Therefore, regardless of the date at which the asset was fully depreciated, the depreciated date should be the last day of the year.

Helping Note!

When changing sensitive fields data, the depreciated to date value may be recalculated again and set to other than the last day of the year. This needs to be considered when considering mass entry tool such as Macro. Refer to support Article ID: 858354.

Best Regards,
Mahmoud M. AlSaadi

Inventory Transaction - User Who Posted !

 

Mostly in all Dynamics GP Modules, the information of “Who Posted This Transaction” is always at the tips of our fingers. Although, this piece of details is not available when digging in inventory tables.  Does it mean that I can not know who posted an inventory transactions ? The answer is simply, You can.

When the posting setup is configured to Post Through GL, the transaction automatically generates associated Journal Entries. In this essence, we can still get this information from GL Table, because the user who posted the IV transaction is the same who generated the GL journal entry.

The SQL Script below retrieves the following details for every single inventory transactions;

• Source Document
• IV Document Source
• IV Document Number
• Journal Entry
• Transaction Year
• Open/Closed Year
• Last User (Last user who edited the transaction)
• Last Edit Date
• User Who Posted (The user who actually posted the IV transaction )
• Posting Date

 

In order to retrieve the following details for a certain document number/s, include them in the “Where” condition in the following SQL Statement.

--- Tables Included:

• GL20000: Year to Date Transaction Open
• GL30000: Account Transaction History
• SEE30303: Historical Inventory Trial Balance

SELECT * FROM
(
    SELECT  DISTINCT
            SOURCDOC AS SourceDocument,
            ORGNTSRC AS IVDocumentSource,
            ORCTRNUM AS IVDocumentNumber,
            JRNENTRY AS JournalEntry,
            OPENYEAR AS TransactionYear,
           'Opean Year' AS 'Open/Closed Year',
            LASTUSER AS LastUser,
            LSTDTEDT AS LastEditDate, 
            USWHPSTD AS UserWhoPosted,
            ORPSTDDT AS PostingDate
    FROM GL20000
    UNION ALL
    SELECT  DISTINCT
            SOURCDOC,
            ORGNTSRC,
            ORCTRNUM,
            JRNENTRY,
            HSTYEAR,
            'History Year',
            LASTUSER,
            LSTDTEDT, 
            USWHPSTD,
            ORPSTDDT           
    FROM GL30000
)   AS A 
    WHERE A.JournalEntry IN
    (
     SELECT DISTINCT JRNENTRY
    FROM SEE30303
    WHERE DOCNUMBR = '#IVDocumentNumber'
    )

 

Helping Note !

The script considers the SEE30303 table. In case the HITB is not populated, you need to change the “WHERE Condition” in the script above to retrieve the IV document number from GL tables as follows;

WHERE A.IVDocumentNumber = '#IVDocumentNumber'

 

Best Regards,
Mahmoud M. AlSaadi

GP 2013 Document Attachments – FAQs

 

With GP 2013, document attachment has been widely considered as a desired feature. In this post, I am including common inquiries and answers about this feature.

Q1 - Where to find illustrative resources explaining document attachment feature on a step by step basis ?

On Microsoft Dynamics GP 2013 Guide: System User's Guide, chapter 19 | Document Attachments. The following content is thoroughly illustrated;

Q2 - Can I attach documents to payable transactions Journal entries ?

Until GP2013 SP2, document attachment doesn’t include neither payable transaction nor General Entry in GL. The following master records and transactions are supported by this feature.

Although, several suggestions have been provided on Microsoft Connect to add this feature for payable and General Entry.

Q3 - What are the document attachment tables ?

• CO40100 | Document Attachment Setup
• CO00101 | Document Attachment Master
• CO00102 | Document Attachment Reference
• CO00103 | Document Attachment Properties
• CO00104 | Document Attachment Status
• CO00105 | Document Attachment E-mail

Q4 – When I attach a document, the original file is deleted !

That’s simply a configuration check, on the Document Attachment setup screen (Microsoft Dynamics > Tools > Setup > Company > Document Attachment), there is an option to delete original attachment, if checked, the original will be deleted. As shown below;

Q5 - What is the “default location” field on the setup window for Document Attachment?

When you click on attachment icon either on a master record or transaction, the “Select an attachment”window will default to retrieve all the files in this path, from which you will attach a selected document.

Q6- When I delete an attachment, the file is still be shown on the “Deleted” tab, how can I completely remove an attachment ?

Dynamics GP manages the deletion of attachments by categorizing the deleted documents in a separate tab, which can still be shown on the document attachment window.

To completely remove an attachment, I would go for a database delete statement which is well considered.

Q7- Where are the attachment stored ?

Document attachment in Dynamics GP considers SQL Server solution for storing files and documents in the database.

Q8- What does the Document Attachment Utility do ?

Although it doesn’t provide any message or notification on completion, but if you follow the process on SQL Profiler, you will find that it does check all the attachments which fall (on or before) a date specified at the utility window, any of these with no link to a certain transaction or master record will be permanently deleted.

Best Regards,
Mahmoud M. AlSaadi

Requisition Attachments

 

When using requisition management as a solution to manage Approval Hierarchy, clients commonly ask for attachment. Unfortunately, this very simple feature is not yet provided as an out of the box functionality in Business portal, Requisition Management. What is the simple, efficient and effective work around solution for this !

Simply, create a separate SharePoint list that is not at all connected to the requisition management site. The list has got to include the following details;

Requisition Number: which is the auto number generated when a new requisition is created, unique and could be the primary key.

Purchase Order Number: once a requisition is finally approved and migrated into Dynamics GP, a purchase order number is generated in GP. This piece of detail can be recorded in the list above for “mapping purposes”

Attachment: simply attach the files; image, PDF, excel or whatever document you want to keep for a specific requisition.

In a previous post, Business Portal Requisition, the requisition life cycle is shown. The steps are;

1. Create Requisition
2. Submit for Approvals
3. Approvals
4. Final Approvals
5. Transfer to PO

Applying this solution will add up an addition step to the five steps above in order to “manually” attach documents to requisition.

For the mean time, although this is a manual work-around, but it won’t take more than five minutes to be created and deployed, to meet the need of requisition attachments.

Best Regards,
Mahmoud M. AlSaadi

GP Cards Configuration – Automated Paperless Solution (2 of 2)

 

In the previous post, GP Cards Configuration – Automated Paperless Solution (1 of 2) the proposed solution has been illustrated without digging into considerable details. In this post, practical cases studies on both; Item Card and Vendor Card are illustrated.

Item Card Configuration | InfoPath Form

The following template represents how “Item Card Form” can be automated with InfoPath designer. Rules are validations are built in the form to ensure that only correct data is entered.

The InfoPath form is divided into four basic sections, each of them is dedicated to receive data from a certain node of the approval chain.

1. Item card details: this section should be filled out by any employee in the company who deals with new items. It could be the warehouse keeper, purchasing clerk, production supervisor… etc.
2. First Level Approval: this section is dedicated to retrieve the first level approval.
3. Second Level Approval: in case there is more than one approval to configure a new item on the system, this section is dedicated to retrieve the second approval.
4. GP Item Card Configuration: once all approvals are corrected, GP administrator can proceed with the configuration of the item on the system, and fill out the fields within this section accordingly.

On the form completion, a notification mail should be forwarded to all associated parties within the company including the “new item code” and associated details.

Below, is the illustration of the various rules that can be applied on the form as part of the validation layer.

 

Vendor Configuration | InfoPath Form

In addition to the form above, another template is provided below that represents the automation of the vendor card of Dynamics GP.

 

Following with the same criteria, customer, fixed asset and GL Account template can be created on InfoPath along with the validation layer accordingly.

Once the InfoPath for is designed and created. It won’t take more than a few minutes to publish the form into SharePoint document library.

In SharePoint Designer, workflow steps can be outlined to ensure that the old-fashioned paper base system is replaced with an effective automated solution.

Best Regards,
Mahmoud M. AlSaadi

GP Cards Configuration – Automated Paperless Solution (1 of 2)

 

When implementing an Enterprise Resources Planning System, clients are mostly keen to replace their current paper work with automated solutions which are  fully integrated with the backbone ERP system of the business, in order to  effectively and efficiently manage the Approval Chain.

In Dynamics GP, configuring a new customer, vendor, item, fixed asset or GL account always requires sensitive data to be filled out and approved before being configured on the system, such data will highly affect the quality of the transactions and reports generated.

In this series of two posts, I will try to explain the value of using certain MS products together to provide a very effective automated solution for managing Approvals specifically for GP Cards (customer, vendor, FA, item and GL account ).

Below is a proposal for using three common MS products together to provide the maximum added value to the business with Dynamics GP, these products are:

 

1- Form Management :

The very essential value of InfoPath is to replace the paper forms with elegant e-forms, with a validation layer within to ensure that the data filled out is correct.

2- Document Repository:

elegant e-forms require an online repository for archiving the documents. SharePoint library can be the best mate for InfoPath.

InfoPath forms can be published into SharePoint Library, as shown below;

What’s valuable about Publishing an InfoPath form into SharePoint Library is that “meta data” within the form can be published into a grid view in the document library, which gives powerful filtration and search capabilities.

3- Workflow Management:

Finally and most importantly, the approval cycle can be managed with SharePoint Designer, through which a customized and up to the need workflow can be designed in order to automate the approval collections of the old-fashioned paper work.

When deployed on the top of the solution, the SharePoint designer provides the end-users who are filling out the forms and the approvers an added value through mail notifications on every single step in the process.

All in all, the proposed solution can be outlined as drafted below, to eliminate paper work, ensure correct data to be filled out, approvals to be well managed, forms to be archived and most importantly the “Sensitive Cards” in Dynamics GP to be well configured.

 

Helping Note !

The automated solution proposed above can be further enhanced to automatically migrate the data that has been fully approved to Dynamics GP. Since data of InfoPath is stored in an XML format. eConnect can be considered including the following eConnect Schema:

• Item: IVItemMasterType
• Customer: RMCustomerMasterType
• Vendor: PMVendorMasterType
• Fixed Asset: FACreateAssetIDType
• GL Account: GLAccountType

In the next post of this series, I will apply the concept above on a practical case study through which Item and Vendor cards are designed and well configured in InfoPath.

When integrated, Simple MS products can do such a great job !

Best Regards,
Mahmoud M. AlSaadi

Inventory Balance Between Two Dates

 

Commonly asked to determine the inventory balance and cost for a certain item on a certain location between two dates. In this post, I provide a stored procedure which retrieves the following report;

The balances are grouped by the document type. In addition, Master Quantity data are provided along with the summary to provide better insight for analysts.

• Quantity on Hand: Calculated from the Purchase Receipt Layer
• Quantity Allocated: retrieved “as is” from Item Quantity Master
• Quantity Damaged: retrieved “as is” from Item Quantity Master
• Quantity on Order: retrieved “as is” from Item Quantity Master

--- Tables Included:

• SEE30303: Historical Inventory Trial Balance
• IV10200: Purchase Receipt Layers
• IV00101: Item Master
• IV00102: Item Quantity Master

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[IV_ITM_LOCTN_Summary]  -- Item Summary Between Two Datese per Location
    (
      @ITEMNMBR CHAR(31) ,
      @TRXLOCTN CHAR(11) ,
      @FromDate DATETIME ,
      @ToDate DATETIME
    )
AS
SET nocount ON
SELECT  IVSUM.DocumentType,
        IVSUM.ITEMNMBR,
        IVSUM.ItemType,
        IVSUM.Item_Inactive,
        IVSUM.LOCNCODE,
        IVSUM.Quantity,
        IVSUM.Value,
        IVBLNC.QuantityOnHand AS 'Quantity On Hand',
        IVQMSTR.QTYONORD AS 'Quantity On ORDER',
        IVQMSTR.QTYDMGED AS 'Quantity Damaged',
        IVQMSTR.ATYALLOC AS 'Allocated Quantity'
        FROM
(
SELECT  DocumentType,
        ITEMNMBR,
        ItemType,
        LOCNCODE,
        Item_Inactive,
        SUM(TRXQTYInBase) AS Quantity,
        SUM(Extended_Cost) AS Value
        FROM
        (

         Select    CASE A.DOCTYPE
                        WHEN 1 THEN 'Adjustment'
                        WHEN 2 THEN 'Variance'
                        WHEN 3 THEN 'Transfer'
                        WHEN 4 THEN 'Receiving'
                        WHEN 5 THEN 'Sales Return'
                        WHEN 6 THEN 'Sales'
                        WHEN 7 THEN 'BOM-Assembly'
                        ELSE        'Cost ADJ'
                    END AS DocumentType,
                    A.ITEMNMBR,
                    CASE B.ITEMTYPE
                        WHEN 1 THEN 'Sales Inventory'
                        WHEN 2 THEN 'Discontinued'
                        WHEN 3 THEN 'Kit'
                        WHEN 4 THEN 'Miscellaneous Charges'
                        WHEN 5 THEN 'Services'
                        WHEN 6 THEN 'Flat Fee'
                        ELSE        'N.A'
                    END AS ItemType,
                    CASE B.INACTIVE
                        WHEN 0 THEN 'No'
                        WHEN 1 THEN 'Yes'
                        ELSE        'N.A'
                    END AS Item_Inactive,
                    A.DOCDATE,
                    A.LOCNCODE,
                    A.TRXQTYInBase,
                    A.UNITCOST,
                    A.TRXQTYInBase * A.UNITCOST

                                        AS  Extended_Cost
                    FROM dbo.SEE30303 AS A
                    LEFT OUTER JOIN dbo.IV00101 AS B
                    ON A.ITEMNMBR = B.ITEMNMBR
                    WHERE    A.ITEMNMBR =  @ITEMNMBR AND
                            A.LOCNCODE = @TRXLOCTN AND 
          (A.DOCDATE BETWEEN @FromDate AND @ToDate)       
        ) AS IVTRX
        GROUP BY DocumentType,
                 ITEMNMBR,
                 ItemType,
                 Item_Inactive,
                 LOCNCODE
                
) AS IVSUM
LEFT OUTER JOIN
(
    SELECT  ITEMNMBR,TRXLOCTN,
            SUM(QTYRECVD-QTYSOLD) AS QuantityOnHand
    FROM IV10200
   GROUP BY ITEMNMBR , TRXLOCTN
) AS IVBLNC
ON    IVSUM.ITEMNMBR = IVBLNC.ITEMNMBR AND
      IVSUM.LOCNCODE = IVBLNC.TRXLOCTN
LEFT OUTER JOIN dbo.IV00102 AS IVQMSTR
ON    IVQMSTR.ITEMNMBR = IVBLNC.ITEMNMBR AND
      IVQMSTR.LOCNCODE = IVBLNC.TRXLOCTN

SET nocount OFF   
GO

 

To run the procedure, run the following code including the required parameters;

EXECUTE [dbo].[IV_ITM_LOCTN_Summary]
               '#ItemNumber'
              ,'#Location Code'
              ,'#2013-01-01'
              ,'#2013-12-31'
GO

 

Best Regards,
Mahmoud M. AlSaadi

Inventory Reconciliation Flow

 

When Dynamics GP administrators encounter  an incorrect item balance or allocation, posting corruption and many other issues related to inventory, the first thing that comes in mind is to “Inventory Reconciliation”. Therefore, it is essential to understand the Inventory Reconciliation Process flow in order to determine how things will get corrected.

The following graphs shows the steps performed in Inventory Reconciliation for each item. Arrows directions are used to represent the “Data Flow”

 

Summary:

Below as an overall summarized representation for the several processes being involved in the process.

 

Best Regards,
Mahmoud M. AlSaadi

Adding Fixed Asset from Payable Management

 

A step by step illustration on how to add a fixed asset from Payable Management, which is integrated with Fixed Asset Module. This post provide further details for Microsoft Support Article ID: 2761966. The whole process is shown below in the graph as it is applied on the system.

It is a common mistake that the “Purchase” is directly applied on the system from Fixed Asset. The truth is, the AP invoice and specifically the distribution initiate the process. A prerequisite is to check the setup as provided in the support Article mentioned at the beginning of the post above.

AP Invoice

The first step to start with is the AP Invoice, in which all the usual invoice details are to be filled out. The acquisition cost value will be retrieved from the “Purchases” field in this window.

On the Payable Transaction Entry Distribution window, when the PURCH distribution type is debited for the “Purchase Posting Account”, the “Purchase” button will appear in the Fixed Asset General Information Window to allow adding the asset as entered on the AP Invoice as soon as the AP invoice is posted.

Helping Note !

The trigger account mentioned above should have already been defined in the Purchase Posting Accounts on the following path (Microsoft Dynamics GP > Tools > Setup > Fixed Assets > Purchasing Posting Accounts)

 

Fixed Asset – General Information Window

Now on the Asset General Information (Cards > Fixed Assets > Asset General Information) enter an asset ID, and then click on “Purchase Button” to select the document number originating from AP.

All associated details will be inherited into the newly added asset, such as acquisition cost and Asset Description.

 

Fixed Asset GL Posting

The next step now is to generate the records of this specific asset, and to be transferred into General Ledger. Go to Tools > Routines > Fixed Asset > GL Posting

Fill out the specific Asset ID created in step (2), and then click “Process”. The details of the generated Journal Entry will be shown before posting the Batch.

Important Note ! 
(For old Dynamics GP versions – Prior to GP2013)

The Fixed Asset GL posting window has been redesigned with the introduce of Dynamics GP 2013 as it is showing above. Prior to GP 2013, the screen included only the following fields:

• Beginning Period
• Ending Period
• Transaction Date
• Comment

Here is the old Fixed Asset GL Posting window:

Suppose the asset is added in 22/04/2015, then you will have to include the beginning period as (2015004) and the ending period as (2015004), which is practically the year (2014)and the month (004). Then include the GL posting data in the transaction date and click continue.

Unfortunately, you will not be able to see the transactions or a validation report before that. After that, a batch will be immediately created in the General Ledger module, Journal entry window.

General Ledger Posting

On the General Ledger (Transactions > Financial > General), a batch will be created after the previous step is processed. Posting this batch will do the following;

• Clear the Purchase Posting Account.
• Posting the acquisition cost into the associated Asset Cost account

 

 

Below is the posting Journal Reports generated after both (AP Invoice) and (General Ledger Posting) as a reference; 

• AP Invoice Posting will generate the following Journal Entry

• General Ledger Posting will generate the following Journal Entry

 

Best Regards,
Mahmoud M. AlSaadi

Requisition Management – How to Manage Delegations

When it comes to requisition approval, there is always a necessity to manage delegations among different users in the approval hierarchy. In this post, I will try to provide a comprehensive illustration on how to manage this requirement.

1- Extended Approver:

On the user properties web part, a user can be granted an “Extended Approver”, this will allow the user to be delegated to approve pending requests assigned to anyone under them in the Approval Hierarchy.

 

2- Change Approver:

Another possible way to apply the delegation is to grant the users (C,D,E and F) the privilege to change their approver. This option enables users to submit their requests to a higher level in the Approval Hierarchy.

3- Replace Level:

Another possible way is to replace the level with another user/role in the approval hierarchy to be delegated. When replacing the user/role, a warning message will pop up stating that all current “work” documents will be assigned to the new user/role.

 

Best Regards,
Mahmoud M. AlSaadi

Delete Item Site Assignment

Throughout the implementation phase, clients are mostly busy with delivering precise and accurate opening balances, without paying considerable attention to other essential details such as: Item-Site Assignment master file. They usually request to assign the item to all sites (regardless of which sites the item is physically assigned to). Later on, deleting this assignment could be one of the riskiest things you could imagine.

An inventory transaction is recorded primarily in the following four tables, which are;

• IV30300 | Inventory Transaction Amount History
• IV10200 | Inventory Purchase Receipt Work
• IV10201 | Inventory Purchase Receipt Details
• SEE30303 | Historical Inventory Trial Balance

On the Item-Site assignment window (Cards > Inventory > Quantities/Site), deleting the Item-Site assignment delete all associated records in both tables (IV10200, IV10201), leaving only (IV30300, SEE30303). Which means, you will lose the following reports:

• Smart List > Inventory Transactions
• Purchase Receipt Inquiry
• Purchase Receipt Report

Below, is a view on the tables on how the records associated with the Item-Site “delete” will be deleted from the Purchase Receipt Layers Work and Details.

Before delete, there are records associated with the item-site assignment:

After deleting the assignment, the records will be completely deleted from IV10200 and IV10201 as shown below.

 

Best Regards,
Mahmoud M. AlSaadi

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

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

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