In our system, the Suppliers|Orders|Generate Orders function has been generating some funny quantities. After some digging, I think that there are two errors in the logic.

1. no accounting for a possible change in package size

The 'whats in the pipeline' logic looks for orders that are not CANCELLED with delivery status not FULL and sums qty ordered less qty received less qty cancelled - which gives the qty outstanding. However, this is in units of package size - and the code makes the assumption that this never changes - ie each order uses the same package size - though this could change because of orders to different suppliers, or changes in packaging by the one supplier.

The SQL currently looks in part like:

       select  productParticipation.entity_id as product_id,
               stockParticipation.entity_id as stock_id,
               sum(forderItem.quantity) as orderedQty,
               sum(receivedQty.value) as receivedQty,
               sum(cancelledQty.value) as cancelledQty,
               orderPackageSize.value as orderPackageSize

It should in fact work with stocking units rather that ordering units - ie it should look like:

       select  productParticipation.entity_id as product_id,
               stockParticipation.entity_id as stock_id,
               sum(forderItem.quantity*orderPackageSize.value) as orderedQty,
               sum(receivedQty.value*orderPackageSize.value) as receivedQty,
               sum(cancelledQty.value*orderPackageSize.value) as cancelledQty

Of course the code in OrderGenerator.java also need to be adjusted to take account of the change to stocking units - ie from:

                BigDecimal orderedQty = getDecimal("orderedQty", set);
                BigDecimal receivedQty = getDecimal("receivedQty", set);
                BigDecimal cancelledQty = getDecimal("cancelledQty", set);
                int orderPackageSize = set.getInt("orderPackageSize");
                int size = (packageSize != 0) ? packageSize : orderPackageSize;
                if (size != 0) {
                    BigDecimal decSize = BigDecimal.valueOf(size);
                    BigDecimal onOrder = orderedQty.subtract(receivedQty).subtract(cancelledQty).multiply(decSize);

                    BigDecimal current = quantity.add(onOrder); // the on-hand and on-order stock
                    BigDecimal toOrder = ZERO;
                    BigDecimal units = idealQty.subtract(current); // no. of units required to get to idealQty
                    if (!MathRules.equals(ZERO, units)) {
                        // Round up as the desired no. may be less than a packageSize, but must order a whole pack.
                        toOrder = units.divide(decSize, 0, RoundingMode.UP);
                    }

to something like:

                BigDecimal orderedQty = getDecimal("orderedQty", set);
                BigDecimal receivedQty = getDecimal("receivedQty", set);
                BigDecimal cancelledQty = getDecimal("cancelledQty", set);
                int orderPackageSize = set.getInt("orderPackageSize");
                int size = (packageSize != 0) ? packageSize : orderPackageSize;
                if (size != 0) {
                    BigDecimal decSize = BigDecimal.valueOf(size);
                    BigDecimal onOrder = orderedQty.subtract(receivedQty).subtract(cancelledQty);

                    BigDecimal current = quantity.add(onOrder); // the on-hand and on-order stock
                    BigDecimal toOrder = ZERO;
                    BigDecimal units = idealQty.subtract(current); // no. of units required to get to idealQty
                    if (!MathRules.equals(ZERO, units)) {
                        // Round up as the desired no. may be less than a packageSize, but must order a whole pack.
                        toOrder = units.divide(decSize, 0, RoundingMode.UP);
                    }

Note that this problem is not the cause of our funnies - but came to light as I was staring at the code.

2. no accounting for over-deliveries

If more of an item is received than is ordered, then the above logic mis-calculates the quantity required.

Assume that the current stock is 3, and the ideal qty is 10. If we previously ordered 10 and received 25, and cancelled 0, then onOrder =  10-25-0 = -15, and toOrder = 10 - (3 + (-15) ) = 22.

That is the over-delivery should be ignored, but it is not.

The problem is that onOrder should be calculated as max(0,(ordered-cancelled-received)) so that any over deliveries are ignored. In the above example orOrder would then be 0, and toOrder = 10 - (3+0) = 7

Note that both the above problems show themselves only in peculiar conditions. In our case we have an inexperienced logistics person causing the system to have old orders with delivery status PART which include over-deliveries.  Hence, I think that I can straighten things out by changing the order status of these old part delived orders from POSTED to CANCELLED so that the Generate Orders function will ignore them.

Regards, Tim G