You’re offline. This is a read only version of the page.
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.
Share
Reportcount := 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
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:
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.
All responses (
Answers (
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;
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.
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;
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.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156