Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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