I recently ran into an issue drilling back on a Purchase Order from a Sales Order with drop shipment lines. The exact error was “Purchase Order No. must have a value in Sales Line…….It cannot be zero or empty” as shown in the screen shot below:
Purchase Order No. must have a value in Sales Line
On investigation this only affected the lines on the Sales Order that had been shipped and invoiced. If I drilled back on an outstanding line on the Sales Order the Purchase Order is displayed without issue. After troubleshooting this it seems when you receive and invoice the line item on a drop shipment the system removes the link back to the Purchase Order and consequently you receive an error trying to drill down.
I’ve detailed how you can recreate the issue below, with a neat trick that can be used to view the data in the Sales Order tables via your browser.
To recreate the error first add a Sales Order with more than one drop shipment line as per below:
Sales Lines marked as Drop Shipment
Next create the Purchase Order and pull through the Sales Order using the function below
Function to Get Sales Order lines
Finally go back to the Sales Order and ship and invoice the first line
The first line has been shipped and invoiced
Now highlight the first line and try and drill back to the Purchase Order using the option below
On doing this you are prompted with the error
Purchase Order No. must have a value in Sales Line
Therefore following this document I added &table=37 to the end of my URL to view the full contents of the Sales Line table
On viewing the data in the table I can see the Purchase Order Number has been removed from the shipped and invoiced line
Purchase Order No. and Purch Order Line No. removed once shipped and invoiced
This is preventing me from drilling back on the Purchase Order. If you check the Purchase Line table (table 39) you can see the Sales Order number has also been removed, so you wouldn’t be able to drill down on the Sales Order from the Purchase Order either.
I also worked through the issue on my local Dynamics NAV install and observed the same behaviour, therefore I’m sure this is by design, however I’m unsure of the reasons. It seems the link to the Purchase Order is permanently removed once the sales line has been shipped and invoiced.
I also checked table 113, which is the Sales Invoice Line table, but this also doesn’t seem to keep the link back to the purchase order.
I’d be interested to know if I’m missing something here. Perhaps there is a way to get back to the Purchase Order from the sales document once its been posted. If there is I’d be very grateful if you could let me know.
A client reported the following error on the posting journals when trying to post both Receivables and Payables Transactions. “This transaction contains multi-currency error(s)”
This transaction contains multicurrency errors
This was followed by a message informing them that the batch had gone to recovery.
I checked the usual things like exchange rate expiration dates however everything seemed fine.
The client then explained they had overwritten some exchange rates accidentally but had since changed these back however the error persisted.
I delved a little deeper using SQL and noticed that although the dates and rates were the same the TIME1 value in the MC020102 table for the transactions differed slightly from the TIME1 value in the DYNAMICS..MC00101 for the exchange rate in question.
Affected transaction showing differing TIME1 value from setup
As a test I changed one of the transactions by updating the TIME1 value in the MC020102 table to match the relevant TIME1 row in MC00100 using the script below.
UPDATE MCTRX
SET MCtrx.time1 = MC.time1
FROM mc020102 MCTRX
INNER JOIN dyn2018r2..mc00100 mc
ON mc.exgtblid = MCTRX.exgtblid
AND mc.xchgrate = MCTRX.xchgrate
AND mc.exchdate = MCTRX.exchdate
WHERE MCTRX.docnumbr = 'SALES00000001004'
I then printed an edit list of the batch and the error had gone.
Now I knew how to fix the issue I realised I could also resolve this by just opening the transactions in the entry window and re-selecting the exchange rate however there were 1000’s of affected transactions so I used the script below. (this was affecting all multi currency transactions in WORK batches so I just had to filter on DCSTATUS)
UPDATE MCTRX
SET MCtrx.time1 = MC.time1
FROM mc020102 MCTRX
INNER JOIN dyn2018r2..mc00100 mc
ON mc.exgtblid = MCTRX.exgtblid
AND mc.xchgrate = MCTRX.xchgrate
AND mc.exchdate = MCTRX.exchdate
WHERE mc.time1 <> MCTRX.time1
AND DCSTATUS = 1 --add this so only WORK trx are affected
After running this script all sales transactions posted fine.
Next I turned to the payables transactions which just involved a small tweak to the original script and also an update to a different table to correct currencies for the multi currency payables payment batch header.
--First fix Payables multi currency trx
UPDATE MCTRX
SET MCtrx.time1 = MC.time1
FROM mc020103 MCTRX --Changed to MC020103 for payables trx
INNER JOIN dyn2018r2..mc00100 mc
ON mc.exgtblid = MCTRX.exgtblid
AND mc.xchgrate = MCTRX.xchgrate
AND mc.exchdate = MCTRX.exchdate
WHERE mc.time1 <> MCTRX.time1
AND DCSTATUS = 1
--Also fix the batch header currency values for payment batches
UPDATE batch
SET batch.time1 = mc.time1
FROM mc00500 batch --batch header multi currency table
INNER JOIN dynamics..mc00100 mc
ON mc.exgtblid = batch.exgtblid
AND mc.xchgrate = batch.xchgrate
AND mc.exchdate = batch.exchdate
WHERE mc.time1 <> batch.time1
Once I’d ran through all this the transactions posted fine.
Hopefully this will help someone in the future who may face a similar issue however always remember to have a good backup of your data before running any SQL scripts and test whenever possible.
You receive the message “The Posting Date is not within your range of allowed posting dates” when trying to post a Purchase Invoice in Business Central.
According to the user setup the “Posting Date” of the document I’m posting is within the allowed posting range so why won’t the system allow me to post it?
Background – Value Entries
To provide a little more detail I’m trying to post a Purchase Invoice that I’ve matched to a Posted Purchase Receipt and I’ve increased the Unit Cost on the Purchase Invoice as the price has changed since the goods were received. I’ve also sold the items on a Sales Invoice before I’ve tried posting the Purchase Invoice.
Therefore, if we look at the value entries of this item prior to attempting to post the Purchase Invoice they are as follows
We have a value entry for the Posted Purchase Receipt showing a date of 12/05/2017 and a “Cost Amount (Expected)” of £10.00 (this the amount I used when posting the Purchase Receipt)
We also have a value entry for the Sales Invoice showing a Posting date of 25/05/2017 and a “Cost Amount (Expected)” of £10.00.
Details of the Purchase Invoice
The Purchase Invoice I’m posting is dated 01/06/2017 and I’ve amended the Unit Cost from the original £10.00 that pulled through from the Posted Purchase Receipt to £12.00
Now when I try and post this transaction, I receive the message
Therefore, just to confirm the Posting Date of the Purchase Invoice is within my allowed posting dates below is a screen shot of the User Setup window showing my Allow Posting Dates
The dates are also within the General Ledger allowed posting dates as shown below
Therefore, at first glance its not apparent why the system isn’t allowing me to post this document? The Purchase Invoice posting date is 01/06/17 and this is within my range of allowed posting dates?
The Issue – Automatic Cost Adjustment and Adjust Cost Item Entries
When posting the Purchase Invoice, the system has detected that the cost has changed from the Posted Purchase Receipt, and as this has been sold on a Sales Invoice, the cost of goods sold need adjusting.
The system therefore tries to post an adjustment using the Posting Date of the entry its adjusting (in this case the Sale Entry on the 25/05/17), which is in May, and as this falls outside of my posting range I receive the error “The Posting Date is not within your range of allowed Posting Dates”.
** Please note there is a complex method for selecting the Posting Date if the Posting Date of the entry being adjusted also fell outside of the “Allowed Posting Dates” of the General Ledger and also if “Inventory Periods” are being used. More information on this can be found in this Microsoft document https://docs.microsoft.com/en-us/previous-versions/dynamicsnav-2016/dn948192(v=nav.90)
** Its also worth noting I’m getting this message when posting the Purchase Invoice because the option “Automatic Cost Adjustment” is set to “Always” in Inventory Setup. This means the system checks for cost adjustments when you post the transaction. If this wasn’t set to “Always”, then depending on its setting its possible the document would post however when the “Adjust Cost Item Entries” batch job was subsequently run the error would occur.
See below for my Inventory Setup
Solution
There are two possible solutions to my issue here. The first is to change my “Allowed Posting Dates” in the “User Setup” to 25/05/2017 through to 30/06/2017. This will then include the posting date of the entry that will be adjusted.
Alternatively, I could change the “Allowed Posting Dates” in the General Ledger Setup to 01/06/2017 through to 30/06/2017. Then, as per the article I linked to, the system would use the date of 01/06/2017 for the adjustment entries, (i.e. the first open date in the General Ledger Setup) which does fall in my allowed periods to post to.
Therefore I’ll change my “Allowed Posting Dates” in the User Setup as per below
And now when I post the Purchase Invoice this is succecssful
If I now view the Value Entries you can see the adjustment entry created with a Posting Date of 25/05/17.
Conclusion
Although this is a simple example it shows why you may encounter this error when it seems the postings date configuration on the User Setup should allow a document to post.
I recently had an issue where a user was stuck in a batch in the Bank Management module. You can usually run the “Clear Activity” option to resolve the issue however on this occasion it didn’t work. I found I had to manually delete a row in an SQL table to clear the lock and allow the user access to the batch.
The exact issue they received is as follows:
With this error message the first thing to try is to clear activity and although this didn’t work on this occasion I’ll detail the steps below.
Ensure there are no users in Dynamics GP and click “Yes” to the message below. (rather than asking everyone to log out I usually just ensure there’s nobody doing any Bank Management activities)
After doing this you will be prompted with the message below confirming activity is cleared
However I found I still couldn’t access the batch. On investigation there is still some activity for the batch in the CBEU1020 table which needed clearing. I therefore ran the SQL query below in the company database to delete the row. (replace TWO18 with the name of your company database)
Within Dynamics NAV \ Business Central you can switch Expected Cost Posting to G/L both ON and OFF via the option below in Inventory Setup.
In this post I’ve been playing with this feature to see how things work and how the various postings differ to Dynamics GP. I also take a look at how the Value Entries in Inventory play a pivotal role in this. I end by taking a closer look at the SQL tables involved and how things fit together.
Expected Costs in Dynamics GP
When you receive goods via a Shipment transaction in Dynamics GP a Purchase Accrual is automatically created to a General Ledger accrual account to record the expected cost in the General Ledger. This account is generally taken from the Creditor Card as per below:
Dynamics GP – Creditor Account Maintenance
The
opposite debit entry is taken from the Inventory Item card as per below:
Dynamics GP – Item Account Maintenance
Let’s add a Purchase Order Shipment transaction in Dynamics GP and see this in action:
Dynamics GP – Receiving Transaction Entry
As you can
see from the screen shot above, I’m receiving one Inventory item, and this has
created an accrual distribution crediting the 000-2111-00 accrued purchases
account I specified on the creditor card. The balancing debit side is to the
Inventory code that we specified on the Inventory Item card.
Now let’s
see what happens when we invoice the Shipment:
Dynamics GP – Purchase Invoice Entry
Just as expected the accrual is reversed via a Debit entry to the 000-2111-00 accrued purchases account and the accounts payable is credited. Therefore, the balance in the accrual account is now nil.
There’s no way to disable this behaviour in Dynamics GP. When you post a “Shipment” for some Inventory Items General Ledger entries are always created. (however you can prevent the entries posting through to the General Ledger via the Posting Setup)
Expected Costs in Dynamics NAV \ Business Central
Before we look at Expected Costing in Dynamics NAV \ Business Central we first have to take a step back and look at the various inventory entries that are created when you post inventory transactions.
When you post an inventory transaction in Dynamics NAV \ Business Central the system creates a minimum of two inventory entries: an Item Ledger Entry and a Value Entry. The Item Ledger Entry records the change in quantity and the Value Entry records the change in inventory values. For the purposes of this post we just need to know that when posting Purchase receipts Value Entries are created for Expected Costs, and when you post Purchase Invoices, Value Entries are created for Actual Costs, and Expected Costs are reversed.
Expected Cost Posting to G/L – Switched ON
Unlike Dynamics GP you can switch ON and OFF accrual postings in Dynamics NAV \ Business Central via the Expected Cost Posting to G/L option in Inventory Setup. When you switch Expected Cost Posting to G/L ON interim accounts are used to post the accrual and inventory entries for Purchase receipt transactions.
The equivalent Dynamics GP accrued purchases account is called Invt. Accrual Acc. (Interim) and is specified in the General Posting Setup window and is selected based on the Posting Groups used on the Item and Creditor. (see my previous post for more details on the posting groups). I’ve highlighted this below
Dynamics NAV \ Business Central – General Posting Setup
The Inventory code for the debit side of the transaction is taken from the Inventory Posting Group and again is based on the combination of posting groups used. I’ve highlighted this below
Dynamics NAV \ Business Central – Inventory Posting Setup
The key difference here is Dynamics GP doesn’t use an interim Inventory account whereas Dynamics NAV \ Business Central does.
In my Cronus demo data, the option Expected Cost Posting to G/L is currently switched ON so let’s see how this works when I create a Purchase Order for an Inventory item and receive it.
Here’s my Purchase Order and after clicking Post I’m going to choose receive so I receive the items into the Inventory:
When I view the item I can see this has created the following Value Entry (Number 454) which shows the Cost Amount (Expected) and Expected Cost Posted to G/L both populated.
If I highlight the Value Entry and click Navigate > General Ledger I can see the G/L Entries associated with the Value Entry
As expected,
we can see the 5510 Accrual account is being credited and the debit entry is
posting to the 2111 “Interim” Inventory code.
Now let’s invoice the purchase order and take a look at the G/L entries. First I click Post and select Invoice on the Purchase Order. (Incidentally if I were to select Receive and Invoice the system recognises I’ve already received the items. It doesn’t receive them again)
This has created the a new Value Entry (Number 455) . There’s a few things to note here. Firstly the Cost Amount (Expected) and the Expected Cost to G/L have been reversed. Secondly the Cost Amount (Actual) and Cost Posted to G/L have been populated.
Again if I highlight the Value Entry and choose Navigate > General Ledger we can the G/L Entries associated with this Value Entry.
As you can see the original entries created via the Purchase Receipt have been reversed by posting a debit to the 5510 Inventory accrual account and a credit to the 2111 Interim Inventory account. The system has then posted new entries to the 2210 Resale items inventory account (debit) and the direct cost applied account (credit). (for more info on the direct cost applied account see my previous post).
So that’s it. Although there are a few extra distributions to Dynamics GP everything makes sense. Its also apparent that the Value Entries have a direct relation to the G/L entries that are created.
Expected Costing to G/L – Switched OFF
Now let’s see what happens when we post a Purchase Receipt with the Expected Cost Posting to G/L switched OFF. First I switch the option OFF and then create and post the Purchase Receipt
This has created the following Value Entries (Number 456)
The key thing to note here is that although the Cost Amount (Expected) is populated the Expected Cost Posted to G/L isn’t. This means no G/L Entries have been created. To prove this click Navigate > General Ledger to view any G/L Entries
Let’s now invoice the Purchase Order and see what happens:
This has created the following Value Entry
This Value Entry records the Cost Amount (Expected) being reversed and the Cost Amount (Actual) and Cost Posted to G/L are also populated. Therefore we get the following G/L Entries
As expected no expected cost postings have been created or reversed for this transaction.
Bonus – G/L Item Ledger Relation and Post Value to G/L SQL tables
An unexpected bonus of writing this post was the chance to geek out on some of the Dynamics NAV tables. Unlike Dynamics GP, I don’t have much of a grasp of the SQL tables in Dynamics NAV however while going through the various scenario’s I was curious about how a couple of things worked which encouraged me to dig a little deeper.
The first thing was how I was able to drill down on the G/L Entries from the Value Entries screen? This meant there must be a direct or indirect relationship between the tables.
After some digging I found the link was via the G_L – Item Ledger Relation table. Therefore writing the SQL query below enabled me to join the G/L Entries and Value Entries table to see all the details for Value Entry Number 455
The next thing I was curious about was what would happen if the Expected Cost Posting to G/L was switched from OFF to ON when there were lots of Purchase Receipts that hadn’t yet been invoiced?
I found the answer to this question lay in the two prompts you receive when you switch Expected Cost Posting to G/L from OFF to ON (or ON to OFF). Below are the two messages you get when toggling the setting
Dynamics NAV \ Business Central – Inventory Setup
As per the first message it seems when you switch the option Expected Cost Posting to G/L ON the system determines if the Actual Costs for the Purchase Receipt have been posted and if not a record is written for that Value Entry to the Post Value Entry to G_L SQL table. This has a link back to the Value Entry so the system knows to create the Expected Cost interim postings for this Value Entry.
To show this in action, I switched the Expected Cost Posting to G/L option OFF and queried the SQL table:
SQL Query on Post Value Entry to GL Table
As per the image above the SQL table is currently blank.
I then created
a Purchase Order and received it as per below:
Dynamics NAV \ Business Central – Purchase Order
After posting this I queried the Post Value Entry to G/L SQL table again to see if any new rows had been added and the table was still blank
SQL Query on Post Value Entry to GL Table
I then checked the Value Entry for my receipt and as per the screen shot below the Cost Amount (Expected) is populated but the Expected Cost Posted to G/L is blank. As this is only the Purchase Receipt the Cost Posted to G/L is also zero.
Dynamics NAV \ Business Central – Value Entries
I then went back to Inventory Setup and switched Expected Cost Posting to G/L back ON and clicked YES to the prompt and now when I check the Post Value Entry to G/L table its populated as per below
SQL Query on Post Value Entry to GL Table
After toggling the Expected Cost Posting to G/L option to ON the system has determined that this Value Entry has no G/L Entries for the Expected Costs and has inserted a record into the Post Value Entry to G_L table with a direct link back to the Value Entry that was created when I posted my receipt.
Now if I run the Post Inventory Cost to G/L batch job as instructed in the second message G/L entries are created for the purchase receipt, the SQL table is cleared, and the Value Entry is updated. See below:
The report output
of the “Post Inventory Cost to G/L” shows entries have been created:
Dynamics NAV \ Business Central – Post Inventory Cost to G/L
Below are the expected cost General Ledger entries created to the interim accounts. (in my previous example these were created immediately because I had Post Expected Costs to G/L switched ON)
Dynamics NAV \ Business Central – General Ledger Entries
And finally the Expected Cost Posted to G/L field on the Value Entry has been updated to show the General Ledger entries have been created and posted.
Dynamics NAV \ Business Central – Value Entries
If I now check the Post Value Entry to G_L table in SQL I can see its been cleared.
Incidentally if I were to switch OFF Expected Cost Posting to G/L before running the Post Inventory Cost to G/L batch job the SQL row is removed from the Post Value Entry to G_L table.
Conclusion
In conclusion I find the way Dynamics GP deals with expected costs to be a much more conventional and simple approach however there’s no doubting that Dynamics NAV \ Business Central gives more flexibility.
Although I don’t know much about the inner workings of Dynamics NAV \ Business Central it also seems to me that G/L Entries are created based on the Value Entries.
In another post I hope to look at how Expected Costs work with Sales Shipments and Sales Invoices.
All ERP systems aim to make data entry simple, fast and accurate. One way to achieve this is to default as much data as possible when the user is entering transactions, including the General Ledger distributions. In this post I aim to show how Dynamics NAV \ Business Central defaults the General Ledger codes when entering a Sales Invoice using inventory Items. Also, as I come from a Dynamics GP background, I’ll start off by providing a quick overview of how Dynamics GP achieves this, to offer a comparison between the two Dynamics systems.
The Dynamics GP way
In Dynamics
GP you enter default General Ledger codes on entities like customers, vendors,
items, fixed assets and then the “catch all” which is the Posting Accounts Setup window.
Once this has been configured the General Ledger codes default automatically onto the transaction. For example, when creating a Sales Invoice for an inventory item usually the control account would default from the customer card and the revenue code would default from the inventory item (you can change this but usually it would be setup this way). At this point the user can potentially edit and change the General Ledger codes on the transaction prior to posting thus overriding the system defaults. Being able to edit the General Ledger codes inside the transaction gives the user more flexibility however it can also introduce mistakes or errors. An example would be someone changing the control account, which would likely cause a reconciliation issue at month end.
Dynamics GP – Default SOP Distributions can be edited as per above
The Dynamics NAV \ Business Central way
From a Dynamics GP perspective things change quite dramatically when you look at how Dynamics NAV \ Business Central defaults the General Ledger codes. Instead of assigning specific General Ledger codes on customers, vendors, items, fixed assets, you assign Posting Groups to each of these entities. It’s the posting groups that have the General Ledger codes assigned and based on the combination of the posting groups used, general ledger postings are automatically performed when the transaction is posted. This means unlike Dynamics GP you can’t edit or change the default General Ledger codes prior to posting which gives less flexibility but there’s also less chance of mistakes being made.
So how do Dynamics NAV \ Business Central posting groups work?
There are two main types of posting groups – Specific and General.
Specific posting groups are used to default the control accounts. For example, I’ve assigned the specific Customer Posting Group “DOMESTIC” to the customer below:
Business Central – Customer Card
If we open the Customer Posting Group window and look at the setup, we can see when I post a transaction for this customer the General Ledger code 40400 will be used for the receivables control account.
Business Central – Customer Posting Groups
Moving on
to the General Posting Groups things
become a little more complex.
General
posting groups can be split into two further groups – Business and Product. You
assign “Business” posting groups to customers and vendors and “Product”
postings groups to Items.
The
combination of the General Business
Posting group from the customer and the General
Product Posting Group from the item determines the General Ledger codes
that will be used. This is something that is best explained via an image, so
I’ve included a screen shot below of the General
Posting Setup window from my demo version of Business Central.
Business Central – General Posting Setup
As you can see the posting groups form a matrix and the combination of “Business” (labelled Gen. Bus. Posting Group in the window) and “Product” (labelled Gen. Prod. Posting Group in the window) determines the General Ledger codes used when you post a transaction.
Therefore,
based on the General Posting Setup
above, if a customer has been assigned a Gen.
Bus Posting Group of DOMESTIC as per below:
Business Central – Customer Card
And the
item they are buying has been assigned a Gen.
Prod Posting Group of RETAIL as per below:
Business Central – Item Card
Then based on the General Posting Setup matrix defined in the General Posting Setup window the General Ledger sales account that will be used when the transaction is posted is 10200.
Defaulting the VAT Codes – VAT Posting Groups
A similar concept
is used when Business Central is determining the VAT percentage and VAT General
Ledger codes to be used. The matrix is defined in the VAT Posting Setup window as per below:
Business Central – VAT Posting Setup
So, in this
example if the VAT Bus. Posting Group
on the customer was “DOMESTIC” and the VAT
Prod. Posting Group used on the Item is “STANDARD” the VAT percentage used
would be 20 and the General Ledger code used would be 56100.
So there you have it. A very quick overview of how Dynamics NAV \ Business Central uses Posting Groups to create the General Ledger distributions when posting a Sales Invoice with inventory items.
A colleague and I worked on a very interesting support ticket today. A client reported that when logging into Dynamics GP using their own Dynamics GP User ID they received the error “No Obligations Found” when retrieving the obligations for the VAT Return however it worked fine when logged in as “sa”.
**Please note there has been a service pack to correct this exact issue however this has been applied to the clients system so it can’t be causing this issue**.
As this worked with “sa” we checked the SQL Error log and surprisingly we found the following messages recorded at the time the routine had been ran:
As you can see the error log is scattered with “Login failed for user” messages for the user in the question.
The next part of the investigation was to setup a SQL Profile Trace to see what SQL statements were running just prior to the login failed message and we found the following:
As you can see the SQL statement “select SERVERPROPERTY(‘ServerName’)” runs just prior to the login failed errors.
My colleague then recalled they are using a DNS Alias for the SQL server name in the ODBC connection rather than the actual SQL server name.
We therefore ran “select SERVERPROPERTY(‘ServerName’)” on the server and, as expected, this returns the actual SQL server name rather than the alias name that is used in the server name in the ODBC connection on the clients PC.
Intrigued that the issue could somehow be related to how GP encrypts passwords using the SQL server name we setup the following test:
Created an additional ODBC connection on the clients PC pointing to the actual SQL server name rather than DNS Alias name.
Logged into Dynamics GP as “sa” using this new ODBC and created a new GP user, copying the security of the existing user. (so the password gets encrypted using the actual SQL server name)
Finally, we logged into GP using the new ODBC and the new GP user.
After successfully logging in using the new user, and the ODBC pointing at the actual SQL server name, we walked through retrieving the obligations and the whole process worked. We also checked the SQL error log and there were no “Login Failed” error messages for the user.
I have to admit at this point I’m guessing as to what is going on however based on the results of this isolated test I can only assume that somehow during this process the users Dynamics GP credentials are being re-submitted to SQL using the actual SQL server name, and as the users password is encrypted with the SQL server DNS Alias name, its failing with “Login failed”. This works when logged in as “sa” because this is the only User ID that doesn’t have its password encrypted by GP. (its not encrypted because its the SQL System Administrator account)
Therefore although using a DNS alias can help with server changes (ironically because of how GP encrypts the password), this has got us thinking that using a DNS Alias for the SQL server could be causing other issues with GP that we don’t know about, so we may avoid this in the future if we can.
After discovering this little oddity I’d be interested if anyone else has any other experience of issues when using a DNS Alias for the SQL server.
Disclaimer :- I’d usually attempt to recreate this type of issue on my own system before documenting and sharing however I can’t in this instance as I don’t have an account with HMRC to complete the full process. However given how interesting this does seem I thought it worthwhile sharing anyway.
I recently came across the following error when trying to post an Inventory Pick in Dynamics NAV 2018
On investigation this is being caused because although the Inventory Pick had been created from the Sales Order, the Sales Order had subsequently been shipped and invoiced directly from the Sales Order screen rather than via the Inventory Pick. (this surprised me as I assumed the system would prevent the Sales Order from being shipped and invoiced from the Sales Order if an unposted Inventory Pick existed for it)
For clarity, as I understand things when a location has the “Require Pick” option ticked in the location setup (important to note “Require Shipment” isn’t ticked) the usual process for shipping and invoicing a Sales Order would be:
Create the Sales Order.
Release the Sales Order.
Create the Inventory Pick in a push or pull fashion from the Sales Order.
Post the shipment from the Inventory Pick.
Post the Invoice from the Sales Order.
It seems that somewhere between point 3 and 4 someone had opened the Sales Order and clicked “Post” and selected “Shipment and Invoice” even though the Inventory Pick was still unposted.
On investigation this removes the internal “Warehouse Request” from the warehouse request table and when you try and post the Inventory Pick you are presented with the error “The Warehouse Request does not exist”.
As far I can see the only option here is to delete the Inventory Pick as it can’t be progressed. (I also assume the goods have been shipped)
I’ve tested this same process on a version of Dynamics 365 Business Central and it seems to prevent the Sales Order being shipped and invoiced if an Inventory Pick exists for it. When you do try you get the message below:
This does make more sense to me, I’m just surprised Dynamics NAV doesn’t prevent it.
As with all issues I did learn something new and also had fun looking at the importance of the internal Warehouse Request with all this. This is something I’m hoping to look at it more depth in future.
This week I dealt with a very bizarre issue affecting all Sales Order Processing Transactions that I thought was worth documenting. It started with a simple support request explaining that Sales Order Processing Invoice transactions weren’t posting due to missing distributions. See below
Missing SOP Distributions
When I connected to investigate further I found the Sales Order Processing Distribution Work\History table (SOP10102) was, with the exception of a hand full of rows, completely blank. (and those rows related to the most recently added SOP transactions)
As this table contains the distributions for both Work and History documents all distributions on posted Sales Order Processing transactions had been lost as well.
Before I even considered how this could have happened I set about trying to find out which user could have done this, and when it happened, so I could recover the missing data.
The first clue for when this could have happened lay with the SQL transaction log backups. As this table would have had hundreds of thousands of rows, I suspected this would have generated lots of SQL transaction log, so I checked the size of the transaction log backups and this highlighted a larger than normal LOG backup at midday (I could do this as the SQL database is in FULL recovery model and the client performs hourly transaction log backups)
Large transaction log backup at midday
I now suspected the issue had arose between 11am and midday so I set about querying the contents of this SQL transaction log backup file looking for transactions with deletes on the SOP10102 table using the query below.
select
[begin time] as BeginTime,
[End Time] as EndTime,
SUSER_SNAME ([transaction sid]) as UserID,
object_name(object_id) as TableName, *
FROM fn_dump_dblog
(NULL, NULL, N'DISK', 1, N'D:\...\backup_2019_10_07_120002_5428125.TRN',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as log1
left join sys.partitions part
on part.partition_id=log1.partitionid
where
[transaction id] IN
(select
[transaction id]
FROM fn_dump_dblog
(NULL, NULL, N'DISK', 1, N'D:\...\backup_2019_10_07_120002_5428125.TRN',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as log1
left join sys.partitions part
on part.partition_id=log1.partitionid
where
operation = 'LOP_DELETE_ROWS' and
object_name(object_id) in ('SOP10102'))
order by [Transaction ID]
I scanned through the results and found the transaction id below which was logging lots of deletes on the SOP10102 table
Transaction showing deletes on SOP10102
I then took this Transaction ID and ran the following SQL query to get full details on this SQL Transaction
select
[begin time] as BeginTime,
[End Time] as EndTime,
SUSER_SNAME ([transaction sid]) as UserID,
object_name(object_id) as TableName, *
FROM fn_dump_dblog
(NULL, NULL, N'DISK', 1, N'D:\...\backup_2019_10_07_120002_5428125.TRN',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as log1
left join sys.partitions part
on part.partition_id=log1.partitionid
where [transaction id] IN ('0000:088fba17')
This query returned 1,466,128 rows all of which are deletes to SOP10102. (this is more than the number of rows in the table which is likely because of the additional deletes on the SQL non clustered indexes that also need maintaining)
Lots of deletes on SOP10102 table
Following on from this I’m now know the user who has caused the deletes, and the exact time it happened. I also know this SQL Login ID relates to a GP user which means this has to have happened inadvertently while using GP. (you can’t use a Dynamics GP SQL Login to access the data via any tool other than Dynamics GP as the password is encrypted)
I asked the user in question if they noticed anything strange at this time like GP crashing or hanging but unfortunately they don’t recall. Therefore this is still a total mystery I’m afraid.
To recover the data I restored the backup to a new database right up to the point of the data going missing i.e. I restored the FULL SQL backup and all LOG backups with NORECOVERY with the exception of the last LOG backup which I restored as per below
I then copied the data back using the SQL query below
INSERT INTO LIVEDB..SOP10102 (SOPTYPE, SOPNUMBE, SEQNUMBR, DISTTYPE, DistRef, ACTINDX, DEBITAMT, ORDBTAMT, CRDTAMNT, ORCRDAMT, CURRNIDX, TRXSORCE, POSTED, Contract_Exchange_Rate) SELECT A.SOPTYPE, A.SOPNUMBE, A.SEQNUMBR, A.DISTTYPE, A.DistRef, A.ACTINDX, A.DEBITAMT, A.ORDBTAMT, A.CRDTAMNT, A.ORCRDAMT, A.CURRNIDX, A.TRXSORCE, A.POSTED, A.Contract_Exchange_Rate FROM RESTOREDDB..SOP10102 A LEFT JOIN LIVEDB..SOP10102 b on a.SOPNUMBE=b.SOPNUMBE and a.SOPTYPE=b.SOPTYPE WHERE B.SOPNUMBE IS NULL
After running the INSERT query the client confirmed that all the data had been recovered.
Although I didn’t get to the bottom of the missing POP data I did have my suspicions however there are no related third parties that could have caused this issue with SOP10102.
Unfortunately I suspect this will also remain an unsolved mystery I just hope it doesn’t happen again.
