Allocate purchase orders to customer orders

andreavicgl — Tue, 25 Oct 2022 09:35:03 GMT

Hi, I can't get a solution to this problem.

I have two tables, one with the customers orders and the other one with the purchase orders.

I need to allocate to each customer order, one or more purchase orders that it takes to complete the customer order for the qty required. If the purchase qty is more than the order qty, the exceed qty will be used from the following order.

The only rule i have is that, the first purchase order (by date) covers the first customer order (by date),

Can anyone help me?

Thank's

Re: Allocate purchase orders to customer orders

andreavicgl — Thu, 27 Oct 2022 06:52:48 GMT

Following, my tables:

CUSTOMER ORDER
RowNumber	Material	OrderDate	Order	OrderQty
1	A	01/01/1987	SCORTA SICUREZZA	960
2	A	03/10/2022	2225100740	204
3	A	03/10/2022	2225100800	288
4	A	03/10/2022	2225100820	192
1	B	01/01/1987	SCORTA SICUREZZA	1.000
2	B	23/09/2022	3168	6
3	B	23/09/2022	5768	8
4	B	23/09/2022	2220703100	16
5	B	04/10/2022	2220702980	96

PURCHASE ORDER
RowNumber	Material	PurchaseDate	PurchaseOrder	PurchaseQty
5	A	01/01/1987	GIACENZA	407
6	A	12/09/2022	M01PB02230-938-19	480
7	A	19/09/2022	M01PB02230-938-20	480
6	B	01/01/1987	GIACENZA	587
7	B	03/10/2022	OFAC222871-1-1	350
8	B	24/10/2022	OFAC223015-1-1	900

And the table I need to create:

WHAT I NEED
RowNumber	Material	OrderDate	Order	PurchaseOrder	AllocateQty
1	A	01/01/1987	SCORTA SICUREZZA	GIACENZA	407
1	A	01/01/1987	SCORTA SICUREZZA	M01PB02230-938-19	480
1	A	01/01/1987	SCORTA SICUREZZA	M01PB02230-938-20	73
2	A	03/10/2022	2225100740	M01PB02230-938-20	204
3	A	03/10/2022	2225100800	M01PB02230-938-20	203
3	A	03/10/2022	2225100800	NOT COVERED	85
4	A	03/10/2022	2225100820	NOT COVERED	192
1	B	01/01/1987	SCORTA SICUREZZA	GIACENZA	587
1	B	01/01/1987	SCORTA SICUREZZA	OFAC222871-1-1	350
1	B	01/01/1987	SCORTA SICUREZZA	OFAC223015-1-1	63
2	B	23/09/2022	3168	OFAC223015-1-1	6
3	B	23/09/2022	5768	OFAC223015-1-1	8
4	B	23/09/2022	2220703100	OFAC223015-1-1	16
5	B	04/10/2022	2220702980	OFAC223015-1-1	96

Thank's

topic Allocate purchase orders to customer orders in App Development

Allocate purchase orders to customer orders

Re: Allocate purchase orders to customer orders