Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Monday, July 29, 2013

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

Unit of Measurement Setup

 

2- Item Card Maintenance:

Item Card

3- Receiving Transaction Entry:

Unit Cost = 50
UOM = Case

Receiving Transaction Entry

 

4- Currency Setup:

Currency decimals Places = 2

Currency Setup

 

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 )

Record

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):

SmartList

 

Calculation

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

Sunday, July 28, 2013

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
 

Friday, July 26, 2013

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