Order Consolidation

Dynamics 365 Business Central/NAV User Forum
1

Hi NAV experts,

Just wanted to start a discussion on the interrelationship among Sales Order, Requisition Worksheet and Purchase Order. I was wondering if any one of you ever came across this business requirement.

This is not uncommon in wholesale sector, where indent Sales Orders are received from retailers after the selling period and Purchase Orders are raised and placed to a few vendors/factories locally or overseas.

Our company is in the fashion (color and size) industry and we handle at least 3000 new UPC/EAN-13 bar codes every season (250 styles * 8 sizes * 2 colorways). The Req. Worksheet, while a good place to plan purchases based on actual demand (Sales Orders), it can only be used to manually enter quantity and Req. Lines are strictly copied from Sales Lines. Navision seems to have assumed a one-to-one mapping down to the lines when calculating plans, which is very time-consuming for users if the quantity has to be manually adjusted in every line. Considering we have over 120 customers and we usually create 3 to 4 Sales Orders for each customer (for different Shipment Dates) and each order has on average 50 line items/item variants.

We invested in Navision hoping that it would at least be smart enough to provide some sort of order consolidation functionality at a mouse click, only to discover there is none.

What we were after was a conceptually very simple solution - to be able to raise Purchase Orders based on Sales Orders. This makes sense because Sales Orders + Sales Forecast always drive Purchase Orders and it is definitely not a company-specific requirement.

In the end we had to implement an algorithm in a codeunit which can combine quantities of the same Item in Sales Lines into a single Requisition Line before we Make [Purchase] Order.

Is there any powerful feature in Navision we haven’t discovered yet?

Scott

2

Here is an idea, it is not standard navision but could be implemented with a simple Non-printing report. Create a non-printing report that run against the sales line table and totals the parts on all the orders, then put that number in your item card order point.

This way you can order based on maximun quanitity instead of existing orders. The requisition worksheet will suggest one order line for each item, instead of separate lines for each sales order.

the setup of the sku card ordering parameters should be as follows.

Reordering parameters

All parameters are set on the SKU card, accessed from item card à item button à Stockkeeping units, then select your branch and press the SKU button

  • Tab – Replenishment

    • Replenishment System – options are

  • Purchase = Purchase from a vendor

  • Prod. Order = Uses Navision Manufacturing to build item (we don’t use this)

  • Transfer = Transfer from another location

  • Purchase

    • Vendor No. = Primary vendor item is purchased from
    • Vendor Item No. = Vendors number,
    • Lead time = How long you expect it to take from when you order to when it arrives, it uses this to determine timing of purchase orders (ok to leave blank) This is more important for manufacturing, system would try to time PO’s with production runs.

  • Transfer

    • Transfer-from code = location you would have this transferred from if you set the Replenishment system above to be Transfer

  • Tab – Planning

    • Reordering Policy
      • Blank = do not reorder using the requisition system (use this if you don’t want to have this item called up in a requisition worksheets – same as do not restock in the old system.
      • Maximum Qty. (to start this is what we will use)
    • Safety Stock Quantity = equal minimum value of inventory you want on hand. Once it falls below this level it will be ordered.
    • Maximum Inventory = amount you will order up to, once you fall below the Safety Stock.

There are many different things that can be set, but most relate to manufacturing and production orders which we do not use.

3

Hi themave,

Thanks for your suggestion. With this requirement, knowing the Qty. on Sales Orders in an Item Card may help if the same item in different Sales Orders has only one Shipment Date. But if we need to raise Purchase Orders based on the Requested Delivery Dates in Sales Orders, the Item table would not help us at all. Again, we must always go back to the source tables - Sales Header and Sales Line.

Reordering generally does not apply to fashion industry because most fashion companies do not to repeat or re-order the same styles season after season. Even if they do repeat the same styles or run a similar version, it would most likely under a different bar code (i.e., different combination of Item No., Variant Code, and Unit of Measure Code). That’s why companies in the fashion industry use a lot of new bar codes every year.

Scott

4

Ok, sounds like more of what you need is the order calculations that the older version of navision did, if you have access to version 2.0, report 699 takes a simpler ordering approach to calculate the requsition worksheet, it looks at time period, item card reorder point and then the qauntities on po’s so’s ect and orders the items one line for each item needed. There is also a report that calculates it for locations per items is was part of advanced distribution report 99002581

here is a cut and past of the cal code in the report.

Documentation()

OnPreDataItem()

OnAfterGetRecord()
Window.UPDATE(1,“No.”);

Item.SETRANGE(“Date Filter”,0D,EndingDate);
PeriodEndingDate := 0D;

IF Item.“Reorder Cycle” = ‘’ THEN
CalcItemAvailable
ELSE BEGIN
PeriodEndingDate := CALCDATE(Item.“Reorder Cycle”,WORKDATE) - 1;
WHILE PeriodEndingDate < EndingDate DO BEGIN
CalcItemAvailable;
PeriodEndingDate := CALCDATE(Item.“Reorder Cycle”,PeriodEndingDate + 1) - 1;
END;
END;

OnPostDataItem()

SetReqWkshLine(NewReqWkshLine : Record “Requisition Line”)
ReqLine := NewReqWkshLine;

SetReqWkshName(NewReqWkshName : Record “Requisition Wksh. Name”)
ReqLine.“Worksheet Template Name” := NewReqWkshName.“Worksheet Template Name”;
ReqLine.“Journal Batch Name” := NewReqWkshName.Name;

InsertReqWkshLine()
WITH ReqLine DO BEGIN
“Line No.” := “Line No.” + 10000;
Type := Type::Item;
VALIDATE(“No.”,Item.“No.”);
TESTFIELD(“Qty. per Unit of Measure”);
VALIDATE(Quantity,ROUND(ReorderQty / “Qty. per Unit of Measure”,0.00001));
IF PeriodEndingDate <> 0D THEN
“Expected Receipt Date” := PeriodEndingDate;
INSERT;
END;

CalcItemAvailable()
WITH Item DO BEGIN
CALCFIELDS(“Quantity on Hand”,“Qty. on Purch. Order”,“Qty. on Sales Order”,“Scheduled Receipt (Qty.)”,“Scheduled Need (Qty.)”);
QtyAvailable :=
“Quantity on Hand” + “Qty. on Purch. Order” - “Qty. on Sales Order” + “Scheduled Receipt (Qty.)” - “Scheduled Need (Qty.)”;
IF QtyAvailable < “Reorder Point” THEN BEGIN
ReorderQty :=
AvailableMgt.GetItemReorderQty(Item,Item.“Requisition Method Code”,QtyAvailable);
InsertReqWkshLine;
END;
END;

5

Hi themave,

Can’t find 99002581 in Navision 4.0 SP1. Anything for NAV 4.0 SP1?

Scott

6

It is not in the newer versions, it is from version 2 with advanced distribution, the code was changed completly in newer vesions because the location item was change to new table stockkeeping unit.

here is the basic code. for it, you would need to base it on the stockkeeping unit table, this was based on the location and lacation item table

Documentation()

OnPreDataItem()

OnAfterGetRecord()

Window.UPDATE(2,“Item No.”);

Window.UPDATE(3,“Variant Code”);

SETRANGE(“Date Filter”,0D,EndingDate);

PeriodEndingDate := 0D;

Item.GET(“Item No.”);

IF Item.“Reorder Cycle” = ‘’ THEN

CalcItemAvailable

ELSE BEGIN

PeriodEndingDate := CALCDATE(Item.“Reorder Cycle”,WORKDATE) - 1;

WHILE PeriodEndingDate < EndingDate DO BEGIN

CalcItemAvailable;

PeriodEndingDate := CALCDATE(Item.“Reorder Cycle”,PeriodEndingDate + 1) - 1;

END;

END;

OnPostDataItem()

SetReqWkshLine(NewReqWkshLine : Record “Requisition Line”)

ReqLine := NewReqWkshLine;

SetReqWkshName(NewReqWkshName : Record “Requisition Wksh. Name”)

ReqLine.“Worksheet Template Name” := NewReqWkshName.“Worksheet Template Name”;

ReqLine.“Journal Batch Name” := NewReqWkshName.Name;

InsertReqWkshLine()

WITH ReqLine DO BEGIN

“Line No.” := “Line No.” + 10000;

Type := Type::Item;

“Department Code” := Location.“Department Code”;

VALIDATE(“No.”,“Location Item”.“Item No.”);

IF “Location Item”.“Variant Code” <> ‘’ THEN

VALIDATE(“Variant Code”,“Location Item”.“Variant Code”);

VALIDATE(“Location Code”,“Location Item”.“Location Code”);

VALIDATE(“Vendor No.”,“Location Item”.“Repl. Source Code”);

TESTFIELD(“Qty. per Unit of Measure”);

VALIDATE(Quantity,ROUND(ReorderQty / “Qty. per Unit of Measure”,0.00001));

IF PeriodEndingDate <> 0D THEN

“Expected Receipt Date” := PeriodEndingDate;

INSERT;

END;

CalcItemAvailable()

WITH “Location Item” DO BEGIN

CALCFIELDS(“Quantity on Hand”,“Qty. on Sales Order”,“Qty. on Purch. Order”,

“Qty. Defective”,“Qty on PCM Returned to Vendor”);

QtyAvailable := “Quantity on Hand” - “Qty. on Sales Order” + “Qty. on Purch. Order” -

“Qty. Defective” - “Qty on PCM Returned to Vendor”;

ReorderQty := InventoryManagement.GetMinMaxItemReorderQty(“Location Item”,QtyAvailable);

IF ReorderQty <> 0 THEN

InsertReqWkshLine;

END;

7

themave,

Just had a look at your code segment. There is no reference to SalesLine or SalesHeader. With CalcItemAvailable() it is just calculating the FlowFields: Qty. on Sales Order, and Qty. on Purch. Order, NOT Quantity by Shipment Date/Requested Delivery Date. Because Dates are never stored in the Item table, we can never get such info from the Item table. The temporal dimension (all these dates) is only associated with Sales Order and will always reside in the SalesHeader and SalesLine tables.

I ended up adding a Planning Method and using different WorksheetNames to store the planning results.

OnRun()

PurchSetup.RESET;
IF PurchSetup.GET AND PurchSetup.“Consolidate Purchase Orders” = TRUE THEN BEGIN
Init(‘REQ.’,PlanningMethod::“By Brand & Item Category”);
SetRecFilters;
CreateReqLinesUsingItems; // Does not carry Sales Order information (e.g., Shipment Date) across to Requisition Line
//CreateReqLinesUsingSalesLines; // Carries all Sales Order information across but very slow
END ELSE
//Consolidate Sales Orders in Purchases & Payables Setup must be enabled in order to use Calculate Plan in the Req. Worksheet.
MESSAGE(Text001);

PopulateReqWkshNames()
{
/////////////////////////////////////////////////////////////////////// IMPLEMENTATION 1: Before Requisition Line table is populated
// STEP 1: Create worksheet names (equivalent to pages)
CASE PlanningMethod OF

PlanningMethod::“By Brand & Item Category”:
BEGIN // Example: GB-GIRLS|GB-BOYS|GG-INFB|GG-INFG
NoOfWorksheets := NoOfBrandNItemCatFilterValues;
ToArray(JnlBatchName,GetBrandNItemCategoryFilter);
END;

PlanningMethod::“By Brand & Product Group”:
BEGIN // Example: GB-GIRLS|GB-BOYS|GG-INFB|GG-INFG
NoOfWorksheets := NoOfBrandNProdGrpFilterValues;
ToArray(JnlBatchName,GetBrandNItemCategoryFilter);
END;

PlanningMethod::“By Item Category & Product Group”:
BEGIN // Example: GB-HA|GB-SI|GB-SH
NoOfWorksheets := NoOfItemCatProdGrpFilterValues;
ToArray(JnlBatchName,GetItemCatNProductGroupFilter);
END;

PlanningMethod::“By Shipment Date”:
BEGIN // Example: 07/01/06|08/01/06|09/01/06
NoOfWorksheets := NoOfShipmentDateFilterValues;
ToArray(JnlBatchName,GetShipmentDateFilter); // May have overflow error from dates like 06/01/06…06/03/06
END;

END;

// STEP 2: Populate ReqWkshName table using values in JnlBatchName[]
TempNoOfWorksheets := 1;

WHILE TempNoOfWorksheets <= NoOfWorksheets DO BEGIN
ReqWkshName.INIT;
ReqWkshName.“Worksheet Template Name” := CurrReqWkshTemplateName;
ReqWkshName.Name := JnlBatchName[TempNoOfWorksheets];
ReqWkshName.Description := ReqWkshName.“Worksheet Template Name”+’ FOR ‘+ReqWkshName.Name;
ReqWkshName.“Planning Method” := PlanningMethod;
ReqWkshName.INSERT;
TempNoOfWorksheets := TempNoOfWorksheets+1;
END;
}
//////////////////////////////////////////////////////////////////////// IMPLEMENTATION 2: After Requisition Line table is populated
ReqLine.RESET;
IF ReqLine.FIND(’-’) THEN BEGIN
REPEAT
IF NOT ReqWkshName.GET(ReqLine.“Worksheet Template Name”,ReqLine.“Journal Batch Name”) THEN BEGIN
ReqWkshName.INIT;
ReqWkshName.“Worksheet Template Name” := ReqLine.“Worksheet Template Name”;
ReqWkshName.Name := ReqLine.“Journal Batch Name”;
ReqWkshName.Description := ReqWkshName.“Worksheet Template Name”+’ FOR '+ReqWkshName.Name;
ReqWkshName.“Planning Method” := PlanningMethod;
ReqWkshName.INSERT;
COMMIT;
END;
UNTIL ReqLine.NEXT = 0;
END;

8

Hi

Tell me where I go wrong [:)]

You have a sales requirement for 50 item A due 30/07/06

You have a sales requirement for 50 item A due 01/08/06

You want to see a purchase requirement for 100 item A due 30/07/06?

I am a little unclear here on your requirement, you need consolidation of items on a purchase order driven from different demand points, so naturally they all have the earliest delivery date, otherwise they have to remain separate on the purchase order to display the different required dates.

Anyway firstly you can have the requisition worksheet modified in any number of manners where it fails to meet your needs. I have been involved in many re-writes.

However you can get your requirement with standard functionality. A reorder policy of “Fixed Reorder Policy” and a Reorder Cycle of 6M (it could be 2D in my example and still work but you probably want to expland it to a sensile look forward period in your industry). That is it, run teh calculate plan you will get one line for 100. What am I missing [:D]

9

Hi Steven,

Let me clarify my requirement. The company I work for is in wholesale distribution and over 95% of our sales orders are indent (placed after selling season) and we then have 2 to 3 months to make them before we ship.

On the demand side we have:

  • Customer A ordered 2 units of Item No. A66611 under SO12345
  • Customer B ordered 10 units of Item No. A66611 under SO12346
  • Customer C ordered 7 units of Item No. A66611under SO12347

On the supply side:

  • A66611 is an Item that can be purchased from one supplier under PO22222

Now can Navision automatically consolidate the all 3 Sales Orders by combining the quantity so it can raise a single PO22222?

Because we are in fashion industry, re-order is a rara scenario.

Any idea?

Scott

10

Hi Scott

The last paragraph of my posting above, I believe, gives you exactly what you want, one purchase order with 3 lines on it.

11

Hmm, I don’t think this can be done without any mods, at least I don’t know how.

Anyway, I have a customer, who does something similar to your scenario (although probably on a much smaller scale). He gets Sales Orders from different clients, and then makes one order at his supplier based on these orders (usually he does it daily). The way we solved this, was by doing a Batch Report (like David suggested), that goes through all Sales Order Lines (selected by Date), and adds the neccessary items (those, where the main supplier is the one from the Purchase Order) to the Purchase Order from where he runs the Batch Report, or updates the Quantity, if the item is allready there. After going through all the Sales Orders, the quantities get converted to the next whole “Purchase Unit of Measure”.

The way I read your requirements, I would use a similar approach in your case, and not go through the “Requisition Worksheet” at all.

12

Hi Daniel

Sorry am I missing something here? What is wrong with my suggestion, it will see the 3 separate requirements and amalgamate them as one line on a PO on the requisition worksheet. Clearly I am missing something [:^)] Having read it back I still do not see where I am going wrong, i am clearly missing something [:D]

13

Hi Steve,

Actually this is not the behavior we want; we want the ability to combine Quantity in Sales Line and create only one Purchase Line, using the following keys:

Type::Item,No.,Variant Code,Vendor No.,Shipment Date (basic ordering approach for reselling)

Type::Item,No.,Variant Code,Vendor No.,Shipment Date,Item Category,Product Group (usually ordering approach for manufacturing company because vendors have different capabilties in different product groups and categories)

It makes little sense from an operational perspective to enter 100 lines of the same Item No. because there is 100 different customers ordering the same Item. We handle over 250 styles (2500 Item Variants due to different colors and sizes for each style) every season and each customer would order well over 200 Item Variants so you can imagine why we prefer not to opt for the one Sales Line to one Purchase Line approach. Also, we would then have to touch this many number of lines when we we enter Quantity to Receive and all of a sudden we become the data entry slaves to the system.

Looks like Navision uses a simplistic one-to-one approach.

Scott

14

Hi Daniel,

Perhaps the Req. Worksheet can be used as a temp summary table to combine quantity for each Item No. before you Make Order. This is what we did to handle “Order Consolidation”. We are essentially combining the Quantity of the same Item No. in all Sales Lines and create one Purchase Line entry.

Scott

15

Actually this is not the behavior we want; we want the ability to combine Quantity in Sales Line and create only one Purchase Line, using the following keys:

But this is what I have said it does [:D]

16

Hi Steven,

no, I guess you were not missing anything, rather it was me [A]…So strike my first sentence from the record please, I know better now…

Tried out what you were suggesting, at it does, what’s been asked in the thread (haven’t tried it out with variants though), even combining the quantities.

In my clients case, it wasn’t enough, because he sometimes is not up to date with his purchase deliveries, and he get’s better prices, if he orders minimum quantity (I would have to modify here, as far as I know, to round up).

17

Thanks Daniel

I had the horrible feeling I was missing something obvious and being a fool. I still may of course be, it is just that it has not been pointed out to me yet [:D]

18

Steven, I think you made the fundamental mistake of assuming that everyone had actually read your reply before replying to it [:P] [;)]

19

Or perhaps it was read, but misunderstood ?

20

So did you try my suggestion Scott?