What is and when we will be using backflushing consumption? Method for issuing (reducing on-hand quantities) materials to a manufacturing order. With backflushing, the material is issued automatically when production is posted against an operation. The backflushing program will use the quantity completed to calculate through the bill of material the quantities of the components used, and reduce on-hand balances by this amount. 

Things to consider …

• When manufacturing requires long lead time: this will mean that stock won’t be correct from the time it is actually used in the shop floor until production is finished (and consumption is backflushed)
• Check impact on inventory accounts, on hand balance, planning
• Items should be those where we always have inventory for
• When we are using item tracking on the components: this means that lot/serial needs to be entered
• BOM and routing accuracy is critical since it triggers consumption
• If this is a multi-level and partner customized to backflush the entire bom levels: why not using a production order with one single line?

What NAV design is on this topic? Manufacturing Training material provides the following explanation: When you use the backward method, this is based on actual quantities. Having this in mind, if you scrap, you might want to take care of this since backflushing is based on actual quantities and once you manually post scrap, NAV expects you to manually post actual consumption as well.

How to model a vendor where they only ship on Wednesdays? This is just an example ...

That is something we need to be careful with. First of all, keep in mind the difference with Sales. In there, we have the "Shipping Agent" where we can define an "Agent Service" (F5790). This shipping agents are what we (our Cronus company) uses to define how are we shipping this including schedule (by setting a calendar) and shipping time (by setting it as well). Now, if we are back in Purchases ... we don't have this. Thus, we cannot define which shipping days is the vendor using. One of the reasons here is that we (Cronus company) have the right (is our business process) to choose the shipping agent we would like to work with. But, it is the vendor right to use that.

Another perspective here is the following. The vendor's lead time is based on: time required to enter the order + time required to manufactured (or purchase) the item we are requesting + time required in vendor's warehouse + shipping time from vendor's to our Cronus location. Thus, it is not just the shipment time. It is all the time it requires on the vendor's to fulfill our purchase requirement and have it ready in our warehouse. From this perspective, when we define (as an example) a lead time of 4W for a given vendor, what we are actually setting is the time it requires to prepare+produce+ship the goods. Thus, those 14W includes everything.

Lastly, we don't have such a "Vendor Shipment Date" in the purchase line. Thus, we don't care about when do they plan to ship this. We care about the "Order Date" which is when we need to place the order in the vendor's to ensure it arrives on time ("Expected Receipt Date"). For the current design, the shipment date for the vendor is an internal process they need to care about.

And, here is your request. Sometimes you are requesting how we setup a vendor which ships on Wednesdays and have a 4W shipping time into our warehouse. That is an issue since we don't have shipping agent as in Sales we do. For instance:

- What about setting a vendor calendar with Wednesdays as the only working day? It will work to set the Wednesday as the "Order date" but, is that what we require? Remember, from a NAV design "Order Date" is the date when we need to place the order but not the vendor's shipment date. Moreover, it will consider the 10D as 10 weekdays since we consider that vendor only works on Wednesdays and we need 4W (28 days or 28 Wednesdays) to produce+prepare+ship.

- What about setting Wednesdays as the only working day in our Cronus calendar? No way, we might have different calendar and we should not forcing doing this.

Thus, what can we do in here? First, we need to understand this design and the fact we don't have such a similar functionality in Purchases as in Sales like "Shipping Agent". Then, can we set a calendar with Wednesdays as the only working date and 4D as lead time?

This is only the same query which is already here:

Table Information including Index information (Usage, Blocks and Reads)

But I noticed that the query somehow got mal-formed to a point where it could not run. So here is a cleaned-up version.

In summary: This query shows a list of all tables and indexes in a SQL database to help identifying in which tables the most blocks happen, and to give a starting point for index tuning. For further details, refer to the original blog post.

--use NAVDatabase

IF OBJECT_ID ('z_IUQ2_Temp_Index_Keys', 'U') IS NOT NULL
DROP TABLE z_IUQ2_Temp_Index_Keys;

-- Generate list of indexes with key list
create table z_IUQ2_Temp_Index_Keys
([l1] [bigint] NOT NULL,
[F_Obj_ID] [bigint] NOT NULL,
[F_Schema_Name] [nvarchar] (128) NULL,
[F_Table_Name] [nvarchar] (128) NOT NULL,
[F_Row_Count] [bigint] NULL,
[F_Reserved] [bigint] NULL,
[F_Data] [bigint] NULL,
[F_Index_Size] [bigint] NULL,
[F_UnUsed] [bigint] NULL,
[F_Index_Name] [nvarchar] (128) NULL,
[F_Index_ID] [bigint] NOT NULL,
[F_Column_Name] [nvarchar] (128) NULL,
[F_User_Updates] [bigint] NULL,
[F_User_Reads] [bigint] NULL,
[F_Locks] [bigint] NULL,
[F_Blocks] [bigint] NULL,
[F_Block_Wait_Time] [bigint] NULL,
[F_Last_Used] [datetime] NULL,
[F_Index_Type] [nvarchar] (128) NOT NULL,
[F_Index_Column_ID] [bigint] NOT NULL,
[F_Last_Seek] [datetime] NULL,
[F_Last_Scan] [datetime] NULL,
[F_Last_Lookup] [datetime] NULL,
[Index_Key_List] [nvarchar] (MAX) NULL
)
GO

CREATE NONCLUSTERED INDEX [Object_ID_Index] ON [dbo].
[z_IUQ2_Temp_Index_Keys]
(
[F_Obj_ID]
ASC
)
GO

CREATE NONCLUSTERED INDEX [Index_ID_Index] ON [dbo].
[z_IUQ2_Temp_Index_Keys]
(
[F_Index_ID]
ASC
)
GO

CREATE NONCLUSTERED INDEX [RowCount_ID_Index] ON [dbo].
[z_IUQ2_Temp_Index_Keys]
(
[F_Row_Count]
ASC
)
GO

INSERT INTO z_IUQ2_Temp_Index_Keys
SELECT
(
row_number() over(order by a3.name, a2.name))%2 as l1,
a1.object_id,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(
a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(
CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(
CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,

-- Index Description
SI.name,
SI.Index_ID,
index_col(object_name(SIC.object_id),SIC.index_id,SIC.Index_Column_ID),

-- Index Stats
US.user_updates,
US.user_seeks + US.user_scans + US.user_lookups User_Reads,

-- Index blocks
IStats.row_lock_count + IStats.page_lock_count,
IStats.row_lock_wait_count + IStats.page_lock_wait_count,
IStats.row_lock_wait_in_ms + IStats.page_lock_wait_in_ms,

-- Dates
CASE
WHEN
  (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_scan,'00:00:00.000')) and
  (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000'))
THEN
  US.last_user_seek
WHEN
  (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_seek,'00:00:00.000')) and
  (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000'))
THEN
  US.last_user_scan
ELSE
  US.last_user_lookup
END AS Last_Used_For_Reads,
SI.type_desc,
SIC.index_column_id,
US.last_user_seek,
US.last_user_scan,
US.last_user_lookup,
''

FROM
(
SELECT
ps.object_id,
SUM
(
CASE
WHEN
  (ps.index_id < 2)
THEN
  row_count
ELSE
  0
END
)
AS [rows],
SUM(ps.reserved_page_count) AS reserved,
SUM
(
CASE
WHEN
  (ps.index_id < 2)
THEN
  (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE
  (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
)

AS data,
SUM (ps.used_page_count) AS used
FROM
sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1

LEFT OUTER JOIN
(
SELECT
it.parent_id,
SUM (ps.reserved_page_count) AS reserved,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps

INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)

INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
INNER JOIN sys.indexes SI ON (SI.object_id = a1."object_id")
INNER JOIN sys.index_columns SIC ON (SIC.object_id = SI.object_id and SIC.index_id = SI.index_id)
LEFT OUTER JOIN sys.dm_db_index_usage_stats US ON (US.object_id = SI.object_id and US.index_id = SI.index_id and US.database_id = db_id())
LEFT OUTER JOIN sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) IStats ON (IStats.object_id = SI.object_id and IStats.index_id = SI.index_id and IStats.database_id = db_id())

WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY row_count DESC
GO

-- Populate key string
DECLARE IndexCursor CURSOR FOR SELECT
F_Obj_ID,
F_Index_ID
FROM
z_IUQ2_Temp_Index_Keys
FOR UPDATE OF
Index_Key_List

DECLARE @objID int
DECLARE @IndID int
DECLARE @KeyString VARCHAR(MAX)

SET @KeyString = NULL
OPEN IndexCursor
SET NOCOUNT ON
FETCH NEXT FROM IndexCursor INTO @ObjID, @IndID

WHILE @@fetch_status = 0 BEGIN
  SET @KeyString = ''
  SELECT @KeyString = COALESCE(@KeyString,'') + F_Column_Name + ', '
  FROM z_IUQ2_Temp_Index_Keys
  WHERE F_Obj_ID = @ObjID and F_Index_ID = @IndID
  ORDER BY F_Index_ID, F_Index_Column_ID
  SET @KeyString = LEFT(@KeyString,LEN(@KeyString) -2)
  UPDATE z_IUQ2_Temp_Index_Keys
  SET Index_Key_List = @KeyString
  WHERE CURRENT OF IndexCursor
  FETCH NEXT FROM IndexCursor INTO @ObjID, @IndID
END;
CLOSE IndexCursor
DEALLOCATE IndexCursor
GO

-- clean up table to one line per index
DELETE FROM  z_IUQ2_Temp_Index_Keys
WHERE [F_Index_Column_ID] > 1
GO

-- Select results
SELECT
[F_Table_Name] TableName,
[F_Row_Count] No_Of_Records,
[F_Data] Data_Size,
[F_Index_Size] Index_Size,
[F_Index_Name] Index_Name,
[F_User_Updates] Index_Updates,
[F_User_Reads] Index_Reads,
CASE WHEN
F_User_Reads = 0 THEN F_User_Updates
ELSE
F_User_Updates / F_User_Reads
END AS Updates_Per_Read,
[F_Locks] Locks,
[F_Blocks] Blocks,
[F_Block_Wait_Time] Block_Wait_Time,
[F_Last_Used] Index_Last_Used,
[F_Index_Type] Index_Type,
[Index_Key_List] Index_Fields
FROM z_IUQ2_Temp_Index_Keys

--order by F_Row_Count desc, F_Table_Name, [F_Index_ID]
--order by F_User_Updates desc
--order by Blocks desc
--order by Block_Wait_Time desc
--order by Updates_Per_Read desc
ORDER BY F_Table_Name

After collecting Partner's and Customer's feedback about the changes introduced on build 32558 regarding Web Services (remember), DEV team has decided to introduce a way of avoiding them if desired.

 In order to do so a new feature has been introduced on build 33137 (KB Article 2667345), this new feature allows you to add a new key to CustomSettings.Config file that will revert changes performed on build 32558 and therefore language used or WS will be the one that is logged for the calling user identity in the User Personalization Table, if any.

 This is the key that has to be manually added (Meaning is not present on the Config File)to the NAV Server configuration file:

<!--

The default Culture in which SOAP Web Service calls are run.

Supported values

"false" (the default)
ensuring Web Services are running on a fixed culture,

"true" use the Culture
that is logged for the calling user identity in the User Personalization Table,
if any -->

 <add
key="ServicesCultureDefaultUserPersonalization"
value="false"/>

If we select false then everything keeps the same and what was applied on build 32558 remains, thus you will see all error messages in English and culture (Decimals, dates, etc.) will work with EN-US values.

However if we set the key to true then the Culture that is logged for the calling user identity in the User Personalization Table (if any) will apply.

 KB article for this Hotfix is placed on the next link:

https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;2667345

Please be aware that this change (32558 build one) was introduced to break the dependency of WS language to RTC one so applying this HF and setting this key to true will enable that dependency back again which could be buggy on  mltilanguage environments where different languages are present for different NAV clients (RTC, WS, Classic Client). 

What was introduced on build 32558 guaranteed that WS will always take EN-US as default language building a more predictable scenario.

Anyhow and following your feedback we have asked DEV for a way to let NAV administrators to make this decision and this is the answer to this.

 I hope you find this helpful.

 Best regards

Diego García Álvarez

Dynamics NAV Senior Support Engineer 

Lead time calculation is the calculation that takes place to calculate "Expected Receipt Date" from the "Order Date" or the other way around. Similarly, sales have the calculation to calculate "Planned Delivery Date" from "Shipment Date" or the other way around as well. Here, we will be focusing on the Purchase side but this will have same explanation for the Sales.

In any case, the interesting piece I would like to cover is how determines what calendar to consider when calculating lead times. As you know, there are many different calendars in NAV:

• Company calendar
• Location calendar
• Vendor calendar
• Shipping Agent calendar
• ...

But, how all those relate when this calculation takes place? First of all, we need to have the following NAV premise in mind: if we don't have vendor calendar defined, NAV considers location calendar. Thus, if for whatever reason you have a location calendar while vendor does not have any (7 days per week), NAV will calculate the expected receipt date based on the location calendar. Here, most of the times, we wouldn't like this since we don't want the vendor lead time to be impacted by our location/warehouse calendar. If you are aware of this premise, you will understand better the design and you will have to setup a workaround: to create a vendor calendar with all days as working days (7 days per week). Having this in mind, NAV will find the vendor calendar (all working days) and will use this together with the vendor lead time.

Now, what are the different calculations that NAV is doing with lead times and calendars? a backward calculation and a forward calculation. Lets try to explain this a bit:

• Forward. Here, we are calculating the expected receipt date (when do we have this in our company's premises?). This calculation is being done from the "Order Date". In other words, we now the purchase "Order Date" and we need to calculate the purchase "Expected Receipt Date" together with lead times and calendars. As mentioned above, if we don't have vendor calendar, it will use the location calendar. If we have vendor calendar (workaround: 7 days/week working days), it will use this vendor calendar together with the lead time and, once this final date is found, it will round to the next working day as per the location calendar (so, we won't set the "Expected Receipt Date" to a non-working date based on our location calendar but to the next working).
• Backward. Here, we know the "Expected Receipt Date" (for instance, this is a requisite from our planning) and we need to calculate the "Order Date". The logic is similar to the above while we are calculation this backward (we know when do we want to have this available "Expected ..." and we would like to determine the date when we need to place this purchase "Order Date").

Here, the main thing is the fact about location calendar being used when vendor calendar is not set. As long as you are aware of this, you will anticipate issues by setting a 7 d/week calendar for the vendor to ensure this will be considered.