Unit Cost and Currency Decimals Setup

Regardless of the fact that Dynamics GP decimals; either quantity or currency, is fixed during the setup, A business might encounter several cases in which a variance might result from this issue.

The fact is that Dynamics GP deals with decimals as accurate as possible, in order to prevent any variances.

I would like to discuss this issue through a case study in order to thoroughly show how accurate Dynamics GP could be in dealing with currency decimals.

1- Item Card Unit of Measurement:

    Case = 6 Pieces

 

2- Item Card Maintenance:

3- Receiving Transaction Entry:

Unit Cost = 50
UOM = Case

 

4- Currency Setup:

Currency decimals Places = 2

 

Now, when the receiving is posted. Dynamics GP encounters a a challenge in dealing with the item unit cost, taking into consideration the following issues

1- IV10200 | Purchase Receipt Work

In purchase receipt work, the cost layer is recorded at the "Base" unit of measurement, considering the currency decimals limitation (which is 2 )

Unit Cost for Base Quantity 50/6 = 8.3333333333   ??

Calculation Methodology:
In order to ensure that the cost is recorded accurately with zero variance, Dynamics GP calculates cost as follows (Smart list below is the Inventory Purchase Receipt):

 

Dynamics GP splits the transaction quantity into two layers, the first layer quantity is (Total Quantity - 1) , and the other layers quantity is (1). In order to calculate the cost;

-  Total Extended Cost (50) / Total Quantity (6) = 8.3333333333  
-   First Layer Cost: Round(8.3333333333,2) = 8.33
-   Second Layer Cost: Total Cost (50) - [ Rounded First Layer Cost (8.33) * First Layer Quantity (5)] = 8.35

 

Best Regards,
Mahmoud M. AlSaadi

Inventory versus GL

Inventory versus General Ledger reconciliation process has become much more easier with the new "IV Reconcile" tool in Dynamics GP 2013. Although, still a headache for previous version. The script below provides IV-GL matching per journal entry per account.

SQL SCRIPT - Inventory versus GL per Journal Entry per Account

--Tables Included:

---SEE30303 | Historical Inventory Trial Balance
---GL20000  | Year-to-Date Transaction Open
---GL30000  | Account Transaction History

 

DECLARE @AccountIndex NVARCHAR(MAX)
SET @AccountIndex = 'Fill in Account Index'

SELECT  *
FROM    ( SELECT    *
          FROM      ( SELECT   'OPEN' AS Status ,
                               [TRXDATE] ,
                               [JRNENTRY] ,
                               [ACTINDX] ,
                               SUM([DEBITAMT]) Dr ,
                               SUM([CRDTAMNT]) Cr
                      FROM     GL20000
                      GROUP BY [TRXDATE] ,
                               [JRNENTRY] ,
                               [ACTINDX]
                      UNION ALL
                      SELECT   'History',
                               [TRXDATE],
                               [JRNENTRY],
                               [ACTINDX],
                               SUM([DEBITAMT]) Dr,
                               SUM([CRDTAMNT]) Cr
                      FROM     GL30000
                      GROUP BY [TRXDATE] ,
                               [JRNENTRY] ,
                               [ACTINDX]
                     ) AS C
                      WHERE  C.ACTINDX = @AccountIndex
        ) AS X
        FULL OUTER JOIN
        ( SELECT    [GLPOSTDT] ,
                    [JRNENTRY] ,
                    [IVIVINDX] ,
                    SUM([DEBITAMT]) AS Dr ,
                    SUM([CRDTAMNT]) AS Cr
                    FROM      ( SELECT  [GLPOSTDT] ,
                                        [JRNENTRY] ,
                                        [IVIVINDX] ,
                                        [DEBITAMT] ,
                                        [CRDTAMNT]
                                FROM    [SEE30303] AS M
                                UNION ALL
                                SELECT  [GLPOSTDT],
                                        [JRNENTRY],
                                        [IVIVOFIX],
                                        [CRDTAMNT],
                                        [DEBITAMT]
                                FROM  [SEE30303] AS N
                  ) AS C
                    WHERE     [IVIVINDX] = @AccountIndex
                    GROUP BY  [JRNENTRY],
                              [IVIVINDX],
                              [GLPOSTDT]
                    ) AS Y ON X.JRNENTRY = Y.JRNENTRY
                    WHERE  
                    ( ISNULL(X.Dr, 0) - ISNULL(X.Cr, 0) <>
                    ISNULL(Y.Dr, 0) - ISNULL(Y.Cr,0) )

 

Best Regards,
Mahmoud M. AlSaadi 

Inventory Transaction Flow

Understanding inventory transaction flow comes as an essential value for those who are engaged in errors troubleshooting, reconciliation and reports generation. Therefore, I am including a thorough explanation of the following points;

• Batch 
• Inventory Work and Detail
• Inventory Posting
• Inventory Inquiries, Smart Lists and Reports Data sources 

Batch Header

#CompanyDB..SY00500    | Posting Definitions Master
DYNAMICS..SY00800      | Batch Activity

To manage posting and prevent multiple users from having conflicts during batch operations, GP Dynamics depends on (Batch Status) and (Batch Activity File) The batch status could be;

1. Available
2. Batch receiving
3. Marked to post
4. Marked to print
5. Marked to update
6. Posting interrupted

During operations on the Batch | when any activity is applied on the batch, GP adds an activity record to the (Batch Activity File) indicating the operation applied on the batch, and changes the batch status accordingly 

Completion | When posting and printing and posting are complete, dynamics removes the batch from the activity file (in case it is not recurring) and clear the (batch status) field.

Help Note !

The batch number is not unique, it can be reused once an existing batch has been posted (in case it is not recurring) 

Inventory Work Header and Detail

#CompanyDB..IV10000 | Inventory Transaction Work
#CompanyDB..IV10001 | Inventory Transaction Amounts Work

Summary of transactions within a Batch is stored in the Work Header File. These transactions are entered either from Transaction Entry Window or Transfer Entry Window

The details of the transactions are stored in the Work Detail File and linked with their associated header file On Posting | the transactions are deleted from both files (Header and Details) and moved to History (in case of a single use Batch)

Quantity Allocation for on-hand quantity | the quantities associated with the saved transactions must be reserved and shown in the item card inquiry (the reserved quantity are shown under the quantity allocated field in the item card)

Work Header: Transfer

Bach Number	Bach Source	Document Number	Document Date	Transaction Quantity	Posting Date	GL Posting Date
BCH_1_3	IV_Trans	Transfer1111	2013-07-01	120	2013-07-01	01072013
BCH_1_4	IV_Trans	Transfer1112	2013-07-01	20	2013-07-01	01072013

Work Details: Transfer

Document Number	Bach Source	UOM	QTY Base UOM	Unit Cost	Extended  Cost	From Location	To Location
Transfer1111	100XLG	Each	1	10	1200	Main	North
Transfer1112	128 SDRAM	Each	1	20	400	Main	South

Inventory Transactions Posting

#CompanyDB..IV30300 | Inventory Transactions Amounts History

#CompanyDB..IV10200 | Inventory Purchase Receipts Work

#CompanyDB..IV10201 | Inventory Purchase Receipts Work Details

#CompanyDB..SEE30303 | Historical Inventory Trial Balance

On posting, the transaction details are removed from both; work header and detail files; in case of a single use batch, and moved into the following tables

As for GL Posting, it is based on the (post to General Ledger Check Box) on the batch posting window, which determines of the transactions Details are moved into GL as well.

IV30300 | Inventory Transaction Amounts History

Document No.	Doc Date	Item Number	UOFM	TRX QTY	Unit Cost	EXD Cost	Location Code	To LOCTN	QTY Base UOM
Transfer1111	2013-07-01	100XLG	Each	100	10	1000	Main	North	1

IV10200 | Inventory Purchase Receipts Work

Receipt Number	DATERECD	Item Number	Location Code	QTYRECVD	QTYSOLD	QTYCOMTD	QTYRESERVED	UNITCOST	ADJUNITCOST
Transfer1111	2013-07-01	100XLG	North	100	0	0	0	10	10

IV10201 | Inventory Purchase Receipts Work Detail

Item Number	TRXLOCTN	Doc Date	Document No.	QTY SOLD	UNITCOST
100XLG	Main	2013-07-01	Transfer1111	100	10

SEE30303 | Historical Inventory Trial Balance (Linked to GL through Journal Entry Field)

Item Number	Document No.	LOCN CODE	UOFM	BASE QTY	Unit Cost	EXD Cost	JRNENTRY	Debit	Credit
100XLG	Transfer1111	North	Each	100	10	1000	125	1000	0
100XLG	Transfer1111	Main	Each	-100	10	-1000	125	0	1000

Inventory Inquiries, Smart Lists and Reports Data sources

Inventory Table	Inventory Window
SEE30303	HITB Activity (Historical Inventory Trial Balance) | Report and Smart List
IV10200	Stock Status |Report and Smart List
IV10200	Inventory Purchase Receipt |Report and Smart List
IV30300 and IV10200	Item Stock Inquiry
IV10200 and IV10201	Cost Layers (QTY Sold) | Smart List

Finally, a transaction flow diagram is provided below in order to illustrate the ideas explained above.

Inventory Transaction Flow

Best Regards, 

Mahmoud M. AlSaadi