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

HITB Essentials Series– HITB Reset Tool and SEE Tables

I have been recently searching the internet for details regarding the HITB Inventory Reset tool regarding specific information on the SEE tables; (SEE99997), (SEE99998) and (SEE99999) but in vain !  Therefore, I am taking this opportunity to include essential details on the HITB Inventory Reset Tool and the role of these tables throughout the process as part of the HITB Essentials Series.

HITB Inventory Reset Tool uses three primary tables throughout the process to manage the process and come up with the desired result of an Inventory Modules that is clean of errors, reconciled internally, free of integrity errors and tied to GL. In order to do so, each of the above three tables has a specific functionality that is highly essential.

First of all, it is worth to mention the basic four stored procedures that are used with the Inventory HITB Reset Tool, which are;

• ivHITBBalanceQTYONHND
• ivHitbRunDataIntegrityChecks
• ivHitbDataInsert
• ivHITBSiteSegment

 

SEE Tables:

1- SEE99999 | Data Integrity Checks

In order for the Reset tool to fill out the HITB table and provide the HITB report, inventory must be cleaned of any associated errors. In order to write in precise records in SEE30303, and tie the Inventory Module to General Ledger Correctly. Which is all comes under “Data Integrity Checks” function.

In this essence, this table; which might be the primary SEE table in HITB Reset Process, is responsible of the Data Integrity Checks Function. It uses the four procedures above in order to prepare and insert all the corrupted instances in Inventory and as related to GL.

Integrity Checks can be divided into two main levels (Inventory and GL), within each level, there are several errors that are recorded to be well.

Error Message Level Values:

• Inventory = 2
• GL = 1

 

Error Message Level	Error Message Description	Error Message Number	Comment
2	Receipt QTY's do not match IV QTY's	101	Quantity for specific Transaction Location in IV00102 (RCRDTYPE = 2)
2	Receipt QTY's do not match IV QTY's	102	Quantity for ALL Transaction Location in IV00102 (RCRDTYPE = 1)
2	Average Item receipt QTY on hand does not equal IV QTY on hand	103
2	Receipt layer QTY is 0, but not marked as sold	104	Incorrect RCPTSOLD field in IV10200. QTYRECVD = QTYSOLD and RCPTSOLD = 0
2	Receipt layer QTY is not 0, but is marked as sold	105	QTYRECVD <> (QTYSOLD + QTYRESERVED) and RCPTSOLD = and PCHSRCTY <> 4
2	Layer is not an override and QTY sold is greater than QTY received	106	QTYSOLD > QTYRECVD and PCHSRCTY <> 4
2	Invalid purchase receipt type	107	PCHSRCTY not in (0,1,2,3,4,5,6,7,8)
2	Serial/Lot tracked item's receipt layer adjusted unit cost does not equal unit cost	108	b.VCTNMTHD in (1,2) and b.ITMTRKOP <> 1 and a.RCPTSOLD = 0 and a.ADJUNITCOST <> a.UNITCOST
2	Item Type not "Sales/Invoice or Discontinued	111	ITEMTYPE not in (1,2)
2	Open layer exists in purchase receipts table where valuation method not stamped	112	QTYRECVD <> QTYSOLD and VCTNMTHD = 0 and PCHSRCTY <> 4
2	Item is on open PO Return	113	Item is on an open po number in POP10300 WHERE POPTYPE in (4,5,6,7)
1	Account not found	109	ACTINDX = 0
1	Account type Profit and Loss	110	Account PSTNGTYP = 1 in GL00100

 

SEE99999 Fields:

• ITEMNMBR: Item Number
• LOCATNID: Location ID
• ERMSGLVL: Error Message Level
• ERRDESCR: Error Message Description
• ERMSGNBR: Error Message Number
• USERID: User ID
• DATE1: Execution Date
• TIME1: Time Stamp
• DEX_ROW_ID: Dex Row ID

 

2- SEE99998 | HITB Staging Table

HITB reset tool will record the Inventory balance brought forward in this table as a staging area before being populated to SEE30303. The balances are primarily retrieved from Purchase Receipt Layer (IV10200) .

Therefore, all records will have a fixed value of "BBF" (Balance Brought Forward) within the HSMODUL (Host Module) field.

 

SEE99998 Fields:

• ITEMNMBR
• LOCNCODE
• DOCDATE
• GLPOSTDT
• DOCTYPE
• DOCNUMBR
• RCTSEQNM
• HSTMODUL
• VCTNMTHD
• QTYTYPE
• PCHSRCTY
• RCPTNMBR
• VENDORID
• TRXREFERENCE
• IsOverrideReceipt
• DECPLQTY
• DECPLCUR
• IVIVINDX
• LOCIVIVINDX
• TRXQTY
• TRXQTYInBase
• VARIANCEQTY
• UOFM
• UNITCOST
• EXTDCOST
• CRDTAMNT
• DEBITAMT
• DEX_ROW_ID

SEE99997 | HITB Reset Tool Steps

This table records the steps through which the HITB Reset Tool accomplishes. There are primarily six steps as shown below;

Helping Now:

One of the quality checks that are performed in order to ensure that the HITB Reset tool has been implemented correctly is to find all steps recorded in this table (as log details).

SEE99997 Fields

• SEQNUMBR: Sequence Number
• Step: Step
• STRTDATE: Start Date
• STRTTIME: Start Time
• Status: Status
• BACHNUMB: Bach Number
• ReconStat: Reconciliation Status
• ERRSTATE: Error Status
• DEX_ROW_ID:Dex Row ID

Step    Step Description

10      Run Reconcile

20      Data Integrity Checks

30      Populate HITB Staging Table

40      Create GL Clearing Transactions

50      Create GL Transactions and HITB Records

60      Finalize IV Reset

 

Best Regards,

Mahmoud M. AlSaadi

GP2010 SP4 “Issue Resolved” - Enter/Match Partial Cost Modification

 

Recently this year, I have made a post on the Dynamics GP Community regarding a specific Modules Behaviors originating from Purchase Order Processing. The post written in mid of April,2013. It primarily shows how the “Dynamics GP” could cause an imbalance Inventory versus GL because of different modules behaviors.

MVP. Mohammad Daoud has a considerable input by further testing the issue on a test environment for all GP Versions; GP 10.0, 2010 and 2013, with the same results returned. He directed the issue to Microsoft Connect to be considered.

The article was posted on GP Essentials Blog by mid of July, 2013 titles as “Enter/Match Partial Cost Modification - Inventory versus General Ledger Test”

 

After approximately five months, by almost mid of September, the GP2010 SP4 is released with this issue resolved !

It’s really something you would be proud of when contributing to the community of Dynamics GP.

But I would still have a question rolling in mind, why the fix has not been considered in further GP releases ? Most importantly, GP2013 SP2 !

The bottom line is, thanks to Microsoft Connect, and specifically MVP Mohammad Daoud.

 

Best Regards,

Mahmoud M. AlSaadi

HITB Essentials Series – Zero Quantity and Negative/Positive Cost

One essential part of reconciling inventory tables is to check the Extended Cost for each item within Historical Inventory Trial Balance (SEE30303) versus The Extended Cost in Purchase Receipt Layer (IV10200)

Theoretically speaking, these two values must match for each item number. Although, for several reasons these two values are not the same.

This post is part of the HITB Essentials Series.In this post, I will be providing the script through which we could compare the HITB with the Purchase Receipt Layer only, to get an initial assessment of the Inventory Variances per item.

HITB Essentials Series will reveal several critical inventory issues regarding primarily two issues (Internal Inventory Reconciliation – IV Tables) and (External Inventory Reconciliation – Inventory versus GL)

Tables Included:

• IV10200  | Purchase Receipt Layer Work
• SEE30303 | Historical Inventory Trial Balance

SELECT
   ISNULL(X.[ITEMNMBR],Y.[ITEMNMBR]) [ITEMNMBR],
   X.HITB_Quantity_Available,
   X.HITB_Cost,
   Y.IV_Quantity_Available,
   Y.IV_EX_Cost,
   X.HITB_Cost - Y.IV_EX_Cost AS Cost_Variance
FROM
(
   SELECT  [ITEMNMBR],
   SUM([TRXQTYInBase]) HITB_Quantity_Available,
   SUM([EXTDCOST]) HITB_Cost
   FROM [SEE30303]
   GROUP BY [ITEMNMBR]
)   AS X    ------  Calculate the Extended_Cost per Item [SEE30303]

FULL OUTER JOIN   

(
   SELECT  [ITEMNMBR],
   SUM([QTYRECVD]-[QTYSOLD]) IV_Quantity_Available,
   SUM(([QTYRECVD]-[QTYSOLD])*[UNITCOST]) IV_EX_Cost
   FROM [IV10200]
   GROUP BY [ITEMNMBR]
)   AS Y    ------  Calculate the Extended_Cost per Item [IV10200]

ON X.[ITEMNMBR] = Y.[ITEMNMBR]
WHERE ABS( X.HITB_Cost - Y.IV_EX_Cost) > 1  OR
X.HITB_Quantity_Available<> Y.IV_Quantity_Available

 

Best Regards,

Mahmoud M. AlSaadi