Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV (Archived)

PO covers SO

(0) ShareShare
ReportReport
Posted on by Microsoft Employee

Trying to find out which PO line can cover the SO lines.

For example I have multiply sales order lines for the same item and I got 2 purchase orders incoming so I need to find out which of the purchase order have enough qty to cover the outstanding qty on sales order.

ItemNo SO No. Outstanding Qty PO No's.
70916 158473 1 13897,13896
70916 158474 1 13897,13896
70916 158475 2 13897,13896

One of the PO's have 2 items of 70916 incoming, the other have 4 items incoming. So the first PO can cover the first 2 sales order lines and the second PO can cover the third SO. 

I needs to be in order so that the PO with the earliest expected receipt date will be servering the first SO that it can cover.

This is my code as of now - in a codeunit.

count := 0;
PurchaseOrderLine.SETRANGE("Document Type",PurchaseOrderLine."Document Type"::Order);
PurchaseOrderLine.SETRANGE(Type,PurchaseOrderLine.Type::Item);
PurchaseOrderLine.FIND('-');
REPEAT 
  Item.SETRANGE("No.",PurchaseOrderLine."No.");
  Item.SETFILTER(Inventory,'<=0');
  IF Item.FIND('-') THEN BEGIN
    REPEAT
      SalesOrderLine.SETRANGE("No.",Item."No.");    
      SalesOrderLine.SETRANGE("Document Type",SalesOrderLine."Document Type"::Order);
      SalesOrderLine.SETRANGE(Type,SalesOrderLine.Type::Item);
      IF SalesOrderLine.FIND('-') THEN BEGIN
        REPEAT
              //
              OutstandingQtySO := SalesOrderLine."Outstanding Quantity";
              RemainingQty := (PurchaseOrderLine."Outstanding Quantity" - PurchaseOrderLine."Qty. to Receive");
              IF OutstandingQtySO <= RemainingQty THEN BEGIN
                 OutstandingQtySO += SalesOrderLine."Outstanding Quantity";
                 RemainingQty := RemainingQty - OutstandingQtySO;
                 DocNoPO += PurchaseOrderLine."Document No." + ', ';
                 count += 1;
                   IF SalesOrderLine."Shipment Date" <= PurchaseOrderLine."Expected Receipt Date" THEN BEGIN            
                      //count += 1;
                      //MESSAGE('Doc no. ' + SalesOrderLine."Document No." + ' Shipment Date is ' + FORMAT(SalesOrderLine."Shipment Date") + ' and Expected Receipt Date is ' + FORMAT(PurchaseOrderLine."Expected Receipt Date"));
                   END;
              END ELSE BEGIN
                OutstandingQtySO := 0;
              END;
              //
        UNTIL SalesOrderLine.NEXT = 0;
      END;
    UNTIL Item.NEXT = 0;
  END;
UNTIL PurchaseOrderLine.NEXT = 0;

MESSAGE(DocNoPO);
MESSAGE(FORMAT(count));

Globals.

Name DataType Subtype Length
PurchaseOrderLine Record Purchase Line
Item Record Item
SalesOrderLine Record Sales Line
SalesOrderHeader Record Sales Header
count Integer
OutstandingQtySO Integer
DocNoPO Text
RemainingQty Integer

*This post is locked for comments

  • Suggested answer
    Alexander Ermakov Profile Picture
    Alexander Ermakov 28,094 on at
    RE: PO covers SO

    User do not need to fill that in manually. It is filled automatically when the PO is created on the basis of SO, e.g. using Special Orders functionality:

    msdn.microsoft.com/.../hh173940(v=nav.90).aspx

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PO covers SO

    Alexander how would that work? When making a purchase order line the user manually then type in the "Sales Order No." and then I can use it to filter on?

    Vishal I made a temp tabel with as you suggested - this is what my code looks like now, but still needs some improvement since the Sales Line starts over and finds the same Purchase Line that are already used to cover the quantity of other Sales Line.

    Sales Line - OnAfterGetRecord()
    //IF "Shipment Date" >= WORKDATE THEN
    //  CurrReport.SKIP;
    Cust.GET("Bill-to Customer No.");
    
    SalesOrderLine.SETRANGE("Bill-to Customer No.",Cust."No.");
    SalesOrderLine.SETFILTER("No.",'<>' + Item."No.");
    //SalesOrderLine.SETRANGE(SalesOrderLine."Document No.",'70547'); // test
    OtherBackOrders := SalesOrderLine.FINDFIRST;
    
    
    Foo := 0;
    QuantitySO := 0;
    InventoryItem := 0;
    CanBeProduced := 0;
    MinCanBeProduced := 0;
    counter := 0;
    //TempPO.DELETEALL;
    
    SalesOrderLine.SETRANGE("No.",Item."No.");
    SalesOrderLine.FIND('-');
    QuantitySO := SalesOrderLine.Quantity;
    InventoryItem := Item.Inventory;
    Foo := InventoryItem - QuantitySO;
    
    SalesHeader.SETRANGE(SalesHeader."No.",SalesOrderLine."Document No.");
    SalesHeader.FIND('-');
    SOOrdreDato := SalesHeader."Order Date";
    
    BOMComponent.SETRANGE(BOMComponent."Parent Item No.",ItemRec."No."); 
        IF BOMComponent.FIND('-') THEN BEGIN
          REPEAT
            ItemRec.SETRANGE(ItemRec."No.",BOMComponent."Parent Item No.");
            IF ItemRec.FIND('-') THEN BEGIN
               ItemRec.CALCFIELDS(Inventory);
               MinCanBeProduced := 9999;
               IF (ItemRec.Inventory <> 0) AND (MinCanBeProduced < CanBeProduced) THEN BEGIN     
                CanBeProduced := (BOMComponent."Quantity per" / ItemRec.Inventory); 
                MinCanBeProduced := CanBeProduced;      
               END ELSE BEGIN
                CanBeProduced := 0;
                 END;
            END;                                       
          UNTIL BOMComponent.NEXT = 0;
        END;
    
    PurchaseLine.RESET;
    PurchaseLine.SETRANGE("No.",Item."No.");
    PurchaseLine.SETRANGE(Type,PurchaseLine.Type::Item);
    PurchaseLine.SETRANGE("Document Type",PurchaseLine."Document Type"::Order);
    //PurchaseLine.SETCURRENTKEY("Document Type",Type,"No.","Variant Code","Drop Shipment","Location Code","Expected Receipt Date");
    //PurchaseLine.ASCENDING(TRUE);
    Completed := FALSE;
    
    IF PurchaseLine.FIND('+') THEN BEGIN
      DocNoPurchaseLine := '';
      QuantityPurchaseLine := 0;
      OutstandingQtyPurchaseLine := 0;
      DatoPurchaseLine := 0D;
      SumVariable := 0;
      QuantityTotalPurchaseLine := 0;
      DisponibleInventory := 0;
      DisponibleInventoryTotal := 0;
      QuantityMinusInventory := 0;
      QtyToReceivePurchaseLine := 0;
      QuantityTotalPurchaseLine := 0;
      FirstPO := 0;
      FirstPODocNo := PurchaseLine."Document No.";
    
      REPEAT
    
        DocNoPurchaseLine := DocNoPurchaseLine + ', ' + PurchaseLine."Document No.";
        QuantityPurchaseLine := (PurchaseLine."Outstanding Quantity" - PurchaseLine."Qty. to Receive");
        OutstandingQtyPurchaseLine := PurchaseLine."Outstanding Quantity" - PurchaseLine."Qty. to Receive";
        QtyToReceivePurchaseLine := PurchaseLine."Qty. to Receive";
        QuantityTotalPurchaseLine += QuantityPurchaseLine;
        DatoPurchaseLine := PurchaseLine."Expected Receipt Date";
        SumVariable := OutstandingQtyPurchaseLine + (PurchaseLine."Outstanding Quantity" - PurchaseLine."Qty. to Receive");
        QuantityMinusInventory := Item.Inventory - SalesOrderLine.Quantity;
        
        DisponibleInventory :=  
                + QtyToReceivePurchaseLine
                + CanBeProduced;
      
        DisponibleInventoryTotal += DisponibleInventory;
        
    
        
        //Start calc of PO covering SO
                TempPO.RESET;
                TempPO.SETRANGE("No.",PurchaseLine."No.");//DISP PÅ PO
                TempPO.SETRANGE("Document No.",PurchaseLine."Document No.");
                TempPO.SETRANGE("Location Code",PurchaseLine."Location Code");
    
                IF NOT(TempPO.FINDFIRST) THEN BEGIN
                  TempPO.INIT;
                  TempPO."Document No." := PurchaseLine."Document No.";
                  TempPO."No." := PurchaseLine."No.";
                  TempPO."Location Code" := PurchaseLine."Location Code";
                  TempPO."Expected Receipt Date" := PurchaseLine."Expected Receipt Date";
                  TempPO.Disponibel := PurchaseLine."Outstanding Quantity" - PurchaseLine."Qty. to Receive";
                  TempPO.INSERT;
                END;
    
                IF TempPO.Disponibel > 0 THEN BEGIN              
                  TempPO.Disponibel -= SalesOrderLine."Qty. to Ship";
                  PurchaseLine.MODIFY;
                  TempPO.MODIFY;
                  
                END;
      
                IF TempPO.Disponibel <= 0 THEN 
                  Completed := TRUE;
                  //Calc of PO Covering SO end.
                  
      UNTIL (PurchaseLine.NEXT(-1) = 0) OR Completed;
      
      DocNoPurchaseLine := DELSTR(DocNoPurchaseLine, 1, 2);  
      DisponibleInventoryTotal := DisponibleInventoryTotal + QuantityMinusInventory;  
      FirstPO := PurchaseLine."Outstanding Quantity" - PurchaseLine."Qty. to Receive";
    
    END
    ELSE BEGIN 
      DocNoPurchaseLine := '';
      QuantityPurchaseLine := 0;
      OutstandingQtyPurchaseLine := 0;
      DatoPurchaseLine := 0D;
      SumVariable := 0;
      QuantityTotalPurchaseLine := 0;
      DisponibleInventory := 0;
      DisponibleInventoryTotal := Foo + CanBeProduced;
      //QuantityMinusInventory := 0;
      QtyToReceivePurchaseLine := 0;
      QuantityTotalPurchaseLine := 0;
      FirstPO := 0;
    
    END;
  • Suggested answer
    Alexander Ermakov Profile Picture
    Alexander Ermakov 28,094 on at
    RE: PO covers SO

    Also, in the Purchase Order Line there are fields "Sales Order No." and "Sales Order Line No.", which can be filled in if you have made related setup and processes. Having this in place, would not require any programming from you.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PO covers SO

    Try this.

    OutstandingQtySO := SalesOrderLine."Outstanding Quantity";

    RemainingQty := (PurchaseOrderLine."Outstanding Quantity");

    IF OutstandingQtySO <= RemainingQty THEN BEGIN

      OutstandingQtySO := 0;

      RemainingQty := RemainingQty - OutstandingQtySO;                                    

    END ELSE BEGIN

      OutstandingQtySO -= RemainingQty;

      RemainingQty := 0;

    END;

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PO covers SO

    I have assigned the PO with SO.

    I also have data as showing in the example above but needs to find out which is able to get enough qty to stastfy the need of the SO.

    OutstandingQtySO := SalesOrderLine."Outstanding Quantity";
    RemainingQty := (PurchaseOrderLine."Outstanding Quantity" - PurchaseOrderLine."Qty. to Receive");
        IF OutstandingQtySO <= RemainingQty THEN BEGIN
           OutstandingQtySO += SalesOrderLine."Outstanding Quantity";
           RemainingQty := RemainingQty - OutstandingQtySO;                                    
        END ELSE BEGIN
           OutstandingQtySO := 0;
        END;

    I am not sure about this part of my code and how it could be done the right way.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PO covers SO

    You can sort Purchase Lines by Expected Receipt Date and sort Sales Lines by Shipment Date.

    This way, you can assign PO with SO.

    Also, try to create one dummy table and enter the related data mapping in that table. This would help you to analyse the data in much better way.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans