Due to some less than perfect management of our ERP system, I need to edit data loaded from the database in the script (it regards the received quantity on purchase orders).
When there have been several deliveries and thus several records made for the same order line, the registered quantity received gets messed up (in the database). However for every quantity registered there's a field where the quantity already invoiced is registered. By using the quantity invoiced for one data entry I can calculate the quantity actually received for another data entry.
In other words I need to compare values from the same field.
How can I do this in the script?
I need to compare all data for each order line and calculate new value and connect them to the right delivery.
This is the kind of logic I would need to implement (for every order line):
Load OrderLineID
For i = 1 to count(QtyInvoiced)-1 if(Max(QtyReceived, i) > Min(QtyAlreadyInvoiced, i), Min(QtyAlreadyInvoiced, i+1) - Min(QtyAlreadyInvoiced, i)) as QtyActualReceived, OrderLineID + QtyInvoiced as OrderLineIDNew, Resident OrderDetails group by OrderLineID
I want to implement the following logic on this data:
For i = 1 to count(QtyInvoiced)-1 if(Max(QtyReceived, i) > Min(QtyAlreadyInvoiced, i), Min(QtyAlreadyInvoiced, i+1) - Min(QtyAlreadyInvoiced, i)) as QtyActualReceived, OrderLineID + QtyInvoiced as OrderLineIDNew, Resident OrderDetails group by OrderLineID
Could you explain BusinessLogic for this algorithm or business logic related to the data you provided:
For i = 1 to count(QtyInvoiced)-1 if(Max(QtyReceived, i) > Min(QtyAlreadyInvoiced, i), Min(QtyAlreadyInvoiced, i+1) - Min(QtyAlreadyInvoiced, i)) as QtyActualReceived, OrderLineID + QtyInvoiced as OrderLineIDNew, Resident OrderDetails group by OrderLineID
I need this because I don't have field QtyAlreadyInvoiced, And it would be easier for me to understand the logic.
Sorry, I made mistake, there's no QtyAlreadyInvoiced (they're all supposed to be QtyInvoiced
so it would look like following:
For i = 1 to count(QtyInvoiced)-1 if(Max(QtyReceived, i) > Min(QtyInvoiced, i), Min(QtyInvoiced, i+1) - Min(QtyInvoiced, i)) as QtyActualReceived, OrderLineID + QtyInvoiced as OrderLineIDNew,
