Cost Layers Analysis – SQL Script

 

Understanding how cost layers are sorted out according to the valuation method configured is important, it helps anticipating the cost to be withdrawn. In addition, when this is combined with the sales details such as unit price, several indicators can be figured out.

In this post, I am providing an SQL script which sorts out all the inventory purchase receipts (In documents) and associated details (Out documents). In case the out document is a sales invoice, associated sales invoice details are provided. This is illustrated below;

Cost layer status can be either one of the following:

• Closed: All the quantity received have been consumed
• Open: Partial or all of the quantity received is available for future consumption

Helping Note !

The prefix (In) is used to distinguish details retrieved from purchase receipt work (IV10200). While the prefix (Out) is used to distinguish data retrieved from purchase receipt work details (IV10201). Additionally, (SLS) is used to distinguish data retrieved from (SOP30300).

 

 

 

 

Tables Included:

·         IV10200  | Purchase Receipt Work

·         IV10200  | Purchase Receipt Work Details

·         SOP30300 | Sales Transactions Amounts History

 

 

SELECT  CASE A.RCPTSOLD

          WHEN 1 THEN 'Closed'

          WHEN 0 THEN 'Open'

          ELSE 'NA'

        END AS 'Cost Layer Status' ,

        A.RCPTNMBR AS 'In Receipt Number' ,

        CASE A.PCHSRCTY

          WHEN 1 THEN 'Adjustment'

          WHEN 2 THEN 'Variance'

          WHEN 3 THEN 'Transfer'

          WHEN 4 THEN 'Override'

          WHEN 5 THEN 'Receipt'

          WHEN 6 THEN 'Return'

          WHEN 7 THEN 'Assembly'

          WHEN 8 THEN 'In-Transit'

          ELSE 'NA'

        END AS 'In Transaction Type' ,

        A.DATERECD AS 'In Date Received' ,

        A.ITEMNMBR AS 'In Item Number' ,

        A.TRXLOCTN AS 'In Transaction Location' ,

        A.QTYRECVD AS 'In Quantity Received' ,

        A.QTYSOLD AS 'In Quantity Sold' ,

        A.UNITCOST AS 'In Unit Cost' ,

        A.RCTSEQNM AS 'In Receipt Sequence Number' ,

        ISNULL(B.ORIGInDOCID, ' ') AS 'Out Document Number' ,

        ISNULL(B.DOCDATE, ' ') AS 'Out Document Date' ,

        ISNULL(B.ITEMNMBR, ' ') AS 'Out Item Number' ,

        ISNULL(B.TRXLOCTN, ' ') AS 'Out Transaction Location' ,

        ISNULL(B.QTYSOLD, 0) AS 'Out Quantity Sold' ,

        ISNULL(B.UNITCOST, 0) AS 'Out Unit Cost' ,

        ISNULL(B.SRCRCTSEQNM, ' ') AS 'Out Source Receipt Sequence Number' ,

        ISNULL(C.SOPTYPE, ' ') AS 'SLS SOP Type' ,

        ISNULL(C.SOPNUMBE, ' ') AS 'SLS SOP Number' ,

        ISNULL(C.UNITPRCE, 0) AS 'SLS SOP Unit Price'

FROM    IV10200 AS A

        LEFT OUTER JOIN IV10201 AS B ON A.ITEMNMBR = B.ITEMNMBR

                                        AND A.TRXLOCTN = B.TRXLOCTN

                                        AND A.RCTSEQNM = B.SRCRCTSEQNM

        LEFT OUTER JOIN ( SELECT    CASE SOPTYPE

                                      WHEN 1 THEN 'Quote'

                                      WHEN 2 THEN 'Order'

                                      WHEN 3 THEN 'Invoice'

                                      WHEN 4 THEN 'Return'

                                      WHEN 5 THEN 'Back Order'

                                      WHEN 6 THEN 'Fulfillment Order'

                                      ELSE 'NA'

                                    END AS SOPTYPE ,

                                    SOPNUMBE ,

                                    ITEMNMBR ,

                                    LOCNCODE ,

                                    UNITCOST ,

                                    UNITPRCE

                          FROM      SOP30300

                        ) AS C ON B.ORIGInDOCID = C.SOPNUMBE

                                  AND B.ITEMNMBR = C.ITEMNMBR

                                  AND B.TRXLOCTN = C.LOCNCODE

 

 

Best Regards,
Mahmoud M. AlSaadi

Clear Data for Inventory Module –The Proper approach

 

Although this process is not something you would do not even once, but if you had to do it, there are essential information that should be considered for comprehensive inventory cleaning.

In addition to the above, This could be important for the implementers as in early stages of the project, clear data is a valid option.

Helping Note !

In case you have upgraded from previous version and have not deployed the HITB reset tool, this article would be of no benefit for you. In this essence, deploying the HITB reset tool would be of a higher priority for you, HITB Essentials Series – HITB Reset Tool Deployment

Case Study

In the following GP company; Fabrikam, considerable number of inventory transactions is available. Go to SQL management studio and run a simple select statement on the following tables;

• IV30300 | Inventory Transaction History
• IV10200 | Purchase receipt work
• IV10201 | Purchase receipt work details
• SEE30303 | Historical Inventory Trial Balance

 

 

Now, going to the clear data, we have several options to choose from. On the clear data window (Microsoft Dynamics GP > Maintenance > Clear Data). Click on “Display” menu and ensure that the “Logical” option is checked.

When choosing “ inventory series “, you will have the following options to choose from:

• Bill of Materials Cards
• Bill of Materials Setup
• Bill of Materials Transaction History
• Bill of Materials Transactions
• Inventory Control
• Inventory Landed Cost Cards
• Inventory Purchase Receipts
• Inventory Reports Options
• Inventory Sales Summary
• Inventory Site Setup
• Inventory Transaction History
• Inventory Transaction Work
• Inventory UOM Schedule Setup
• Item Category Setup
• Item Class Setup
• Item Lot Attribute Master
• Item Lot Category Master
• Item Master
• Item Serial Number Master
• Stock Calendar
• Stock Count
• Stock Count History

In our scenario, I will choose to clear all of the above. So, once inserting all the above into the “selected tables” area, click on “Ok”

 

Now, going back to run the select statements on the inventory tables, we can clearly see that the HITB table is not affect at all with the clear data process.

In case this has not been noticed in initial stages after the inventory clear data, the following symptoms might be encountered;

1. Inconsistent numbers among Inventory reports, inquiries and smart lists. In terms of cost and quantity balance
2. Inventory Reconcile to General Ledger will not tie

 

Resolution

The HITB tables should be cleared out in order for the process to be comprehensive and complete.

• Go to ( Dynamics GP > Maintenance > SQL )
• Choose the company database
• Under product, choose “HITB report”
• Highlight the “Inventory Transaction History Detail”
• On the right side, check the options to “drop and create table”
• Process

 

Now, the final result for the inventory tables can be seen below, as all tables have been cleared out

 

Helping Note !

In case you are not clearing all the inventory data, just the transactions and keeping the setup, cards , reports options, site setup and other configuration, you should apply the same approach mentioned above. Additional step is required upon completion, which is Inventory Reconcile (In order to reconcile the balances in IV00102 against the inventory transactions and come down to zero)

 

Best Regards,
Mahmoud M. AlSaadi

Checkbook Setup – Important Assignment Concept

 

In a previous checkbook- General Ledger reconciliation with one of the clients where no Reconcile to GL tool is available (GP 10.0),the checkbook balance doesn’t tie to the Cash account balance.

Troubleshooting Checklist

• Cash receipts posted on the AR module, without being deposited. This results with increasing the GL cash account balance without increasing the checkbook balance
• Cash account included on a bank transaction. >> See following link for further illustration
• Journal Entries posted on the General ledger directly with no corresponding checkbook transaction
• Checkbook transaction no posted to General Ledger
• Deposit without receipt posted on the Bank Module, which doesn’t post any general ledger transactions, but increase the checkbook balance
• … Further potential causes are provided in Dynamics GP support article The checkbook balance and the general ledger cash account do not balance in Microsoft Dynamics GP – KB 864652

Fortunately, non of the above was the case as all the transactions were entered correctly. Although, there is a variance between the Checkbook and General Ledger !

It just happened that several checkbooks are assigned to one General Ledger Account, which makes it impossible for the current checkbook balances and current cash balance to tie.

The bottom line is, it is highly recommended to have one-to one assignment type checkbook management, in which only one Checkbook is assigned to one General Ledger account.

 

This brings us to an essential point, reconciling checkbooks versus general ledger for Dynamics GP prior to GP 2013 (Checkbook Reconcile to GL Tool) could be a suffering sometimes. Therefore; in the next post, T SQL query will be provided to match the current Checkbook balance versus General Ledger balance and categorize results as Matched, Un Matched and Potentially Matched transactions.

 

Best Regards,
Mahmoud M. AlSaadi

HITB Essentials Series – HITB Reset Tool Deployment

 

Following up with the HITB Essentials Series, in a previous post HITB Reset Tool and SEE Tables, several technical aspects of the inventory reset process were provided.

In this post, a quick overview for the overall process is provided on a step by step bases.

HITB Reset Tool Installation

1- Download the appropriate HITB build associated for your Dynamics GP version on the customer source reference : Customer Source Download - Historical Inventory Trial Balance Inventory Reset Tool for Microsoft Dynamics GP

2- The download will have two main files which are;

• IVReset.cnk | This is to be copied to your Dynamics GP Folder (Program Files > Microsoft Dynamics > GP )
• HITBIVResetProcedures.sql | This script will create all the stored procedures required for the IV Reset Process. Further details related to these procedures can be found here.

First copy the .cnk file into your Dynamics GP folder, then run Microsoft Dynamics GP (Right Click > Run as Admin) and click yes to include the new code.

Helping Note  “Sign in to Dynamics GP With (SA) account”. It is better to log all Dynamics GP users out of the systems before starting this process.

Second, run the sql script on your SQL Management Studio using the (SA) account as well.

HITB Reset Tool – Steps

Now that you have completed the installation, you will need to proceed with the IV Reset Process, including several steps as thoroughly provided below;

Prerequisites

Post all pending inventory-related transactions. Then go to (Microsoft Dynamics GP menu > Tools > Utilities > Inventory > HITB IV  Reset Tool)

 

Step One and Two | Inventory reconciliation and Data Integrity

Before inventory reset, it is important to run inventory reconciliation and Data integrity, in order to ensure that no corrupted data are considered through the reset.

Helping Note !

Along with the download of HITB, there is an FAQ document including essential information

For further explanations, refer to the FQ document for specific details:

• HITB_SkipReconciles=TRUE
• HITB_SkipErrorChecking=TRUE
• HITB_SkipClearingTransactions=TRUE
• HITB_SkipVersionChecks=TRUE
• HITB_DebugFile=c:\somefile.txt

 

 

Step Thee|  Populate Tables

At the end of this step, you will get a report on the variances to be considered throughout the inventory reset process. This is a very important part before you proceed.

This is called the “Inventory Staging Report”

 

Step Four and Five |  Create General Ledger Clearing Transactions and HITB records

Include an offset account in the following two steps, to be considered in the reset process.

 

 

At the end of the steps above, you will have a corresponding HITB (SEE30303) but not yet recorded in SEE30303, rather in a staging table

In addition, clearing GL transactions are already calculated and ready to be saved within Dynamics GP

Steps 6,7 and 8 | Finalizing

 

 

Best Regards,
Mahmoud M. AlSaadi

Cost Adjustment (2 out of 4) Shipment and Enter Match Invoice

In the previous post of this series ; Cost Adjustment (1 of 4) Override Documents, the first of four scenarios in which cost variance documents are calculated was thoroughly explained. In this post, we will move on with the second scenario of cost adjustment.

When using the shipment – enter match approach, the cost recorded at the shipment could be different than the one on the invoice. For instance, we received one piece of item (X) with cost of 150, then upon receiving the invoice it shows that the cost is different like 160. The following journal entries are recorded;

Business Perspective

Upon Receiving | Purchasing > Transactions > Receiving Transaction Entry (Shipment)

Upon Invoicing | Purchasing > Transactions > Enter Match Invoice

Technical Perspective

Once the shipment is posted, the following records will be written in inventory tables

Now, when an invoice with different unit cost (160) is matched to the shipment, a new cost adjustment record is thrown in SEE30303 (HITB), as well as other modifications in other tables;

• IV30300 records an addition record with the total difference as an extended cost
• IV10200 updates the unit cost field with the new unit cost, while the old unit cost is saved in another field which is ADJUNITCOST, Adjusted Unit Cost
• SEE30303 records an addition record with all the cost adjustment details. Unit cost, extended cost and the associated Journal Entry created on the general ledger level

Helping Note !

This scenario supposes that the shipment is not consumed at all until the time that the invoice is recorded. The third scenarios of this series illustrate how sales documents posted before the invoice is recorded will be updated accordingly to reflect correct cost of goods sold.

Best Regards,
Mahmoud M. AlSaadi