Cowmaster's Corner

Monday, December 14, 2015

Dynamics NAV - How to fix an existing item cost that is currently incorrect - Wrong Costs - Cost Revaluation

Method 1 - The Proper Way - Only works if cost is coming from invoicing

Do return transactions to cancel the original invoices
Re-receive at the correct cost at the original date

Method 2 - Using a Revaluation Journal

Run Adjust Costs against the item you wish to revalue to ensure no outstanding adjust cost jobs exist
Ensure that the posting periods are open
Open Revaluation Journal Entry window

Click Calculate Inventory Value
Enter the date the costing error happened
Enter filter for item no.

If using average costing, calculate per item (not item ledger entry), location, variant

Enter any filters as required
Leave all other fields blank, click ok
System will populate the revaluation journal with all value entries
Change the Unit Cost (Revalued) column to the value you want
Post the revaluation entry
Run the adjust cost
This will also resolve any sales at historical incorrect costs.

Method 3 - Reset Stock (This is your only option if using average costing)

Adjust all stock out as at yesterday, or some previous date period that will allow you to adjust in new stock using a different date

We do not want to do the adjust out and adjust in on the same date or it will cause the average cost to average incorrectly
Example

Change posting dates to 30-Jan-20

Whse. Phys. Inv. journal>Set to 0>Register (Ignore this step if not using warehousing)
Physical Inventory Journal>Set to 0, post

Change posting dates to 31-Jan-20

Whse. Phys. Inv. journal>Set to Stock amount>Register (Ignore this step if not using warehousing)
Physical Inventory Journal>Set to stock amount>Set new Cost>, post

This way it correctly calculates a new average cost on 31-Jan-20

Friday, December 11, 2015

Dynamics NAV - User Permissions View - User Access List

View showing all users and all assigned permission groups

SELECT dbo.[Access Control].[Company Name], dbo.[User].[User Name], dbo.[Access Control].[Role ID]

FROM dbo.[Access Control] INNER JOIN

dbo.[User] ON dbo.[Access Control].[User Security ID] = dbo.[User].[User Security ID]

Thursday, December 10, 2015

Dynamics NAV - LS Retail - Data Director - Jobs stuck in "PreProcessed"

There is some large job that is stuck in Data Director being processed, and sticking the queue.

Resolution:
Restart the Data Director Service.
You will lose any jobs that are currently in the queue. Remember to reset the replication counters and re-send them.

Launch DD configuration Tool
Select Head Office
Click Stop DD
Click Start DD

Dynamics NAV - LS Retail - Scheduler Job Record Filter

Edit Subjob>Navigate>From Table Filters

Use Filter Type "Filter" its the only one that works
It will insert this value directly into the SQL query
It can only hold 12 characters

Eg. Filter1 value to filter a date should be
='20160120'
If using the sql filter

Also try '2016-6-20' because the field only accepts 12 characters in the filter

Example - How to set the filter to the current date for a subjob using CAL code

SJFilt."Value 1" := '='+ '''' + FORMAT(DATE2DMY(TODAY,3),4)+
CONVERTSTR(FORMAT(DATE2DMY(TODAY,2),2),' ','0')+ CONVERTSTR(FORMAT(DATE2DMY(TODAY,1),2),' ','0') + ''''

SJFilt."Value 1" := '='+ '''' + FORMAT(DATE2DMY(CALCDATE('<1D>',TODAY),3),4)+ CONVERTSTR(FORMAT(DATE2DMY(CALCDATE('<1D>',TODAY),2),2),' ','0')+ CONVERTSTR(FORMAT(DATE2DMY(CALCDATE('<1D>',TODAY),1),2) ,' ','0')+ '''' ;

Example2 - How to set the filter to the current date for a subjob using sql

update [Cronus].[dbo].[Cronus$Scheduler Subjob Filter] set [Value 1] = '>'+'''' + cast(year(getdate()) as varchar) + right('0' + cast(month(getdate()) as varchar),2) + right('0'+case when day(getdate()) = 1 then cast(day(getdate()) as varchar) else cast(day(getdate())-1 as varchar) end,2) + '''' FROM [Cronus].[dbo].[Cronus$Scheduler Subjob Filter] where [Subjob ID] = 'ITEM_DATE_FILTER'

Wednesday, December 9, 2015

Dynamics NAV - LS Retail - POS Replication - How to resend the last x action updates

Reduce the replication counter on the corresponding replication counter line on the job header for the number of actions you wish to resend
You can check the Preaction Log or the Preaction list to see what jobs have already gone through, or are pending to go through.

Dynamics NAV 2013 R2 - How to resolve CONSISTENCY error

Import this codeunit (save as text and import and compile)

---------------------------------------------------------------------------------------------

OBJECT Codeunit 50089 Single Instance CU-GL Consist
{
OBJECT-PROPERTIES
{
    Date=10/29/15;
    Time=[ 1:15:17 PM];
    Modified=Yes;
    Version List=MOD01;
}
PROPERTIES
{
    Permissions=TableData 17=rimd;
    SingleInstance=Yes;
    OnRun=BEGIN
            IF NOT StoreToTemp THEN BEGIN
              StoreToTemp := TRUE;
            END ELSE
              PAGE.RUN(0,TempGLEntry);
          END;
}
CODE
{
    VAR
      TempGLEntry@1000000000 : TEMPORARY Record 17;
      StoreToTemp@1000000001 : Boolean;
    PROCEDURE InsertGL@1000000000(GLEntry@1000000000 : Record 17);
    BEGIN
      IF StoreToTemp THEN BEGIN
        TempGLEntry := GLEntry;
        IF NOT TempGLEntry.INSERT THEN BEGIN
           TempGLEntry.DELETEALL;
           TempGLEntry.INSERT;
       END;
      END;
    END;
    BEGIN
    {
      Used for debugging GL Consistency errors.
      See CU 12
      Steps:
      1. Run CU and it will start in memory
      2. Run any posting that generates a consistency error
      3. Run CU again and the GL that would have posted will be shown
    }
    END.
}
}

----------------------------------------------------------------------------------------------

Modify C12 - FinishPosting

Look for these lines
Insert the code between //MOD

Add Global Variable SingleCU = Codeunit, Single Instance CU-GL Consist
Run CU and it will start in memory
Run any posting that generates a consistency error
Run CU again and the GL that would have posted will be shown
Copy and paste the results to Excel
Sort by account
Subtotal by account
Manually Sum totals for all Sales
Manually Sum totals for all Receipts
Compare the totals and work through the figures to find where the numbers do not match
General problem areas

Rounding causes minor differences and no rounding account is setup, so rounding differences are lost, causing inconsistencies
VAT causes rounding and above problem
Discounts cause rounding and above problem
Currency Exchange rates cause the above problem
Records are missing from the Sales_Trans_Entry table if dealing with statements
Total Payments do not match Total receipts

To resolve the issue

Determine the root cause of the differences to prevent future errors
Cancel, reverse, void or redo the transaction if possible
LAST RESORT: You may need to modify the Sales_Trans_Entry table record values to force a match to allow the current transaction to post if dealing with statements

Wednesday, December 2, 2015

Dynamics NAV - Change VAT %

When changing VAT, the problem of re-calculating Unit Price, and Unit Price Including VAT occurs

The Unit Price or Unit Price including VAT will not change unless the price is actually changed manually, or in a package

To resolve this issue

Stop Nav Scheduler to prevent changes from going to terminals
Export Current Items and Sales Prices

Export all Item no,, VAT Prod Posting Group, Unit Prices, Unit Price including VAT to excel package template

Split this into 2 templates,

one for items with new prices - IC
one for items where prices are not changing - INC

Export all Sales Prices with no ending date to excel package template

Split this into 2 templates,

one for items with new prices - PC
one for items where prices are not changing - PNC

Change VAT % (see methods below)
Import Item updates

For the INC List, set all item card prices including vat to 0

Import and apply this package
Then set all prices to the new selling price including VAT
Import and apply this package

This is to get NAV to reverse calculate the correct Unit Price without VAT

For the IC list, just import and apply

End all current Sales Prices

This is done as a precaution to ensure no old prices are missed
Run a SQL update to put an ending date on all sales prices with no ending date
update [mytable] set [Ending Date] = '2016-01-31' where [Ending Date] < 0

Import all new Sales Prices

For the PC List

Change the Starting Dates
Update PC template with new sales prices
Import and apply

For the PNC list

Change the starting dates
Import and apply

Re-Export all Items and Sales prices and compare to import lists to confirm changes went in correctly

In some instances, the unit prices on the item cards are incorrectly updated after importing the sales prices

Configure any custom jobs to push changes to terminals
Start NAV scheduler to push all changes to terminals

------------------------------------------------------------------------------------------------------

VAT CHANGE METHODS

Method1 - Change the existing rate and only affect new transactions moving forward

http://www.tvisiontech.co.uk/news_5.html
Search for VAT Posting Setup

Change current VAT %
Create new VAT Codes for the old rate to handle old transactions being entered before the vat change date. (eg. VATOLD)
Manually select the new vat code (VATOLD) if you need to handle transactions for the old vat %

Check and adjust item prices, including VAT and sales prices.

Use the batch job Adjust Item Costs/Prices.

Push all changes to terminals

Table VAT Posting Setup 325 to all terminals
Table VAT Code 99001560 to all terminals
Item
Sales Prices

Method 2 - Create a new Rate and only affect new transactions going forward

All existing transactions containing VAT should be posted
New VAT Posting Group should be created with new VAT% (keep the old existing ones)
New VAT Posting Setups should be created with new VAT% (keep the old existing ones)
New VAT Code should be created with new VAT % (keep the old existing ones)
Update all VAT Product Posting Groups on Customers, Vendors, Items, GL
Push all changes to terminals

Table VAT Posting Setup 325 to all terminals
Table VAT Code 99001560 to all terminals
Customers
Vendors
Items
GL Accounts

Only new transactions going forward will have the new VAT %

Method 3 - Change all unposted transactions, and all new transactions going forward

Create new VAT Codes
Create new VAT Posting Groups
Search for VAT Rate Change
Setup VAT Prod Posting Group conversion to define the old VAT code and the new VAT code to replace it with

Setup the VAT Rate Change Setup

Test the VAT conversion

Clear the VAT Rate change Tool Completed Checkbox

Clear the Perform Conversion Checkbox

Once this is cleared, running the convert function will only simulate a conversion

The actual conversion will not happen

Review the VAT Change Log Entry

Print all VAT reports before conversion

Perform the VAT Conversion

Clear the VAT Rate change Tool Completed Checkbox

Mark the Perform Conversion Checkbox

Review the VAT Change Log entry

Review sample data

Print all VAT reports after conversion

Push all changes to terminals

Push the values for Table VAT Posting Setup 325 to all terminals

Push the values for Table VAT Code 99001560 to all terminals

These changes will affect current unposted transactions, and new transactions going forward

Historical transactions will remain with the VAT% they had when they were posted