Hi,
I’m requesting your help on this because I cannot figure how to resolve it.

Here is the issue:
File 1: For a same product I have multiple customer’s order (on-line buying: OO#).
File 2: For that same product, I have multiple purchase order (PO#) to fill the above OO#.

I must display, for each OO# what is the remaining back orders (BO) once PO# fill the requested quantity.

The key between the two files is the product#.

Example of what I should display (they’re all related to the same product):

OO# OO_Qty PO# PO_Available_Qty BO_Qty
1000 2 AAA 1 1
1000 BBB 14 0
2000 2 BBB 13 0
3000 2 BBB 11 0
4000 6 BBB 9 0
5000 2 BBB 3 0
6000 1 BBB 1 0

I’ve tried to use MATCH or JOIN but I’m still not able to have PO# BBB associated with OO# 1000 or 3000 and following; it’s only associated with OO# 2000.

With MATCH I have OO# 1000 associated with PO# AAA but not with BBB, and I have PO# BBB associated with OO# 2000 and not OO# 1000, 3000, 4000, 5000, 6000.

With JOIN UNIQUE I have similar result as with MATCH.

With JOIN MULTIPLE I have both PO# associated with each OO#.

Any ideas?

This message has been edited. Last edited by: MartinY,

I'm not 100 percent sure what you are asking, but we relate orders to products and products to purchase orders all the time.

Depending on your data, MATCH FILE should work for you - except maybe for the backorders, where you could be looking for something that isn't there - missing data in other words.

Do these purchase orders come from the same customers who are placing the online orders? Or is it a case that you get an online order and then cut your own purchase order to an outside supplier (this is what we do).

In the former case (customer and PO from the same source)you should be able to produce a decent result fairly easily, although you might have to do some date magic to match things up.

If it's the latter case, and assuming more than one customer could purchase a given product in a short timeframe, then it becomes a lot more amorphous and difficult to match a given customer with a given PO, unless you system automatically creates a PO within a few seconds of the customer order being received (more date magic).

Hi George,
My case is that customers place orders but the PO are made manually by a clerk to fill orders and/or replenish inventory.

The goal of the report is to see if I will have enough inventory to answer customer's orders by the time the orders are promised to be delivered. So the only link that I have between OO and PO is the product number. Then the remaining it's easy code to compare OO promised date and PO receiving date.

The issue is to assign PO to OO until requested units are filled.

I assume that if you have inventory the order is completed right away and does not result in an open order.

Do you have a promised delivery date for your open orders on a per product basis?

And an expected delivey date on your POs on a per product basis?

I think you need to think of this in terms of dates. Without knowing what your data looks like, something on the order of:

MATCH FILE PURCHASES
PRINT
PURCHASE_QUANTITY
EXPECTED_DATE
PO
BY PRODUCT_CODE
RUN
FILE OPENORDERS
PRINT
ORDER_NUMBER
ORDER_QUANTITY
DELIVERY_DATE
BY PRODUCT_CODE
AFTER MATCH HOLD
END

Then you want to:

a) Sum the order quantities for all orders. If the quantity is less than the purchase quantity and the purchase date is greater than the highest delivery date, you are good to go. All orders will be filled within the specified PO.

b) If the sum of the order quantities is greater than the puchase quantity it means some orders won't get fulfilled. So you need to fill the earliest orders and leave the rest as open orders.

I would probably try and do this on a timeline basis with dates of both purchases and orders down the left side and a column on the right that gets incremented with a purchase and decremented with an order. When there is a negative value you have an open order and when it's positive you should have inventory.

I know this is a bit vague - the wizards may want to weigh in with something more concrete !

This is good and right if I only have one PO for one or multiple OO (based on product number), and this situation I manage it with a MATCH as you did. I have dates as in your example and play with them the same way.

The issue is when there is multiple OO and multiple PO for the same product. MATCH doesn't do the job and JOIN MULTIPLE does to much: all PO are joined to all OO.

I'm trying to do the join with as much PO as necessary to fill the OO but when PO qty are all used to fill an OO, it must not be joined to another OO. Kind of WHERE based JOIN but can't get it

But that doesn't make a lot of business sense. Assume you have two POs for product ABC with a quantity of 10 each.

Then you get three orders for product ABC with a quantity of 6 each.

Are you telling me that you will fill the first order from PO # 1 (leaving a remainder of 4) and the second order from PO#2 (leaving another remainder of 4 in stock)? And then you get to the third order for 6 and you don't fulfil it because you don't have a PO with a remaining quantity of 6? Even though you actually have 8 of the item in stock?

What about FIFO ? That's how most businesses operate.

IMHO you should fulfil the first order of 6 from PO#1, the second order of 6 as 4 from PO #1 and 2 from PO#2 and the third order of 6 also from PO#2.

Unless you are actually going to update a database such that the order number and the PO get linked permanently when the order is completed I don't see how you can make a permanent record based only on product code.

Your original request was to know if you would have product on hand to fulfil orders by the expected shipment date. As I said before, if you create a table that shows shipment dates for open orders plus the expected delivery dates on the relevant products (from POs) then for any given date you can see where the quantity required is more or less than what you need.

I think that MATCH FILE vs JOIN isn't the issue here at all. With a couple of DEFINES, either one will produce a hold file that you can use to produce the timeline that is IMHO what you should use as your starting point.

Once you have that timeline you'll be able to figure out how to use the data to do things like flag situations where the quantity on order isn't enough to fulfil orders in time to meet delivery dates.

This message has been edited. Last edited by: George Patton,

Hi George,
Sorry, maybe I miss-explain myself, but this is exactly what I should do :

quote:

IMHO you should fulfil the first order of 6 from PO#1, the second order of 6 as 4 from PO #1 and 2 from PO#2 and the third order of 6 also from PO#2.

And this is exactly what I'm not able to perform :-(

The client want to see which PO fill which OO :

OO#1 order 6 units - PO#1, 10 units available, used 6, missing 0
OO#2 order 6 units - PO#1, 4 units available, used 4, missing 2
OO#2 order 6 units (information from OO#2 should not be repeated, written for understanding) - PO#2, 10 units available, used 2, missing 0
OO#3 order 6 units - PO#2, 8 units available, used 6, missing 0
OO#4 order 6 units - PO#2, 2 units available, used 2, missing 4