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 …
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.
IF OBJECT_ID ('z_IUQ2_Temp_Index_Keys', 'U') IS NOT NULLDROP TABLE z_IUQ2_Temp_Index_Keys;
-- Generate list of indexes with key listcreate 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_KeysSELECT(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 DescriptionSI.name,SI.Index_ID,index_col(object_name(SIC.object_id),SIC.index_id,SIC.Index_Column_ID),
-- Index StatsUS.user_updates,US.user_seeks + US.user_scans + US.user_lookups User_Reads,
-- Index blocksIStats.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,
-- DatesCASE 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_seekWHEN (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_lookupEND 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(CASEWHEN (ps.index_id < 2) THEN row_countELSE 0END)AS [rows],SUM(ps.reserved_page_count) AS reserved,SUM(CASEWHEN (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 usedFROM sys.dm_db_partition_stats psGROUP 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 usedFROM 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 DESCGO
-- Populate key stringDECLARE IndexCursor CURSOR FOR SELECTF_Obj_ID, F_Index_ID FROMz_IUQ2_Temp_Index_KeysFOR UPDATE OFIndex_Key_List
DECLARE @objID int DECLARE @IndID int DECLARE @KeyString VARCHAR(MAX)
SET @KeyString = NULLOPEN IndexCursorSET NOCOUNT ONFETCH 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, @IndIDEND;CLOSE IndexCursorDEALLOCATE IndexCursor GO
-- clean up table to one line per indexDELETE FROM z_IUQ2_Temp_Index_KeysWHERE [F_Index_Column_ID] > 1 GO
-- Select resultsSELECT[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 WHENF_User_Reads = 0 THEN F_User_UpdatesELSEF_User_Updates / F_User_ReadsEND 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_FieldsFROM 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 descORDER 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.
"false" (the default)ensuring Web Services are running on a fixed culture,
"true" use the Culturethat is logged for the calling user identity in the User Personalization Table,if any -->
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:
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.
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:
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:
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.