Create a specific purchasing algorithm

I have a view that has product codes, product alternative codes (up to 4), minimum target value, amount in hand, and purchasing request. This view is the output of a detailed stored procedure that is stored in a temp table.

Every time it runs, the output is placed into a table that I created, and then I have to use this table to create a view that meets my requirements. In the table, there are approximately 3000 products, some of which have special cases that need to be considered. One of them is my main problem right now. Here is a snippet of the table that has alternative products that need to be considered (please keep in mind that some products don't have an alternative in this table as well, so it's a bit crowded. I am only giving a snippet of the table that is needed to understand the problem):

code	alternative_1	alternative_2	alternative_3	minimum_target	amount_in_hand	purchasing_request
A156	B156	NULL	NULL	15	16	0
B156	A156	D156	C156	30	65	0
C156	A156	D156	B156	40	50	0
D156	B156	C156	A156	0	0	150

Here is the DDL and DML of the sample data:

CREATE TABLE alternatives (
    code VARCHAR(5) PRIMARY KEY,
    alternative_1 VARCHAR(5),
    alternative_2 VARCHAR(5),
    alternative_3 VARCHAR(5),
    minimum_target INT,
    amount_in_hand INT,
    purchasing_request INT
);

INSERT INTO alternatives (code, alternative_1, alternative_2, alternative_3, minimum_target, amount_in_hand, purchasing_request)
VALUES ('A156', 'B156', NULL, NULL, 15, 16, 0);

INSERT INTO alternatives (code, alternative_1, alternative_2, alternative_3, minimum_target, amount_in_hand, purchasing_request)
VALUES ('B156', 'A156', 'D156', 'C156', 30, 65, 0);

INSERT INTO alternatives (code, alternative_1, alternative_2, alternative_3, minimum_target, amount_in_hand, purchasing_request)
VALUES ('C156', 'A156', 'D156', 'B156', 40, 50, 0);

INSERT INTO alternatives (code, alternative_1, alternative_2, alternative_3, minimum_target, amount_in_hand, purchasing_request)
VALUES ('D156', 'B156', 'C156', 'A156', 0, 0, 150);

Assume that there is a purchase request opened for the product D156 for the amount of 150. When a new purchasing request is opened, the first thing that needs to be checked is the minimum_target value of the product. If this value is not set, if it is 0, then you need to consider the alternatives of this product and you can't purchase the product itself. In the example, since D156 product have a minimum target value of 0, the alternatives will be considered.

The first alternative is B156, there are 65 of them in hand. You can use them without passing the minimum_target amount hence you use 35. Then you move on to the second alternative, C156. You can use 10 without passing the minimum. Now you used a total of 45, you still need 105 to complete the request. You move on to the last alternative A156. You can only use 1. In the end, you still need 104 more. What you need to do is, create a purchasing request for the amount still needed from the first alternative of the product, in this case, it's B156. This amount will be seen by the responsible person, they will make the purchase.

code	alternative_1	alternative_2	alternative_3	minimum_target	amount_in_hand	purchasing_amount
A156	B156	NULL	NULL	15	16	0
B156	A156	D156	C156	30	65	104
C156	A156	D156	B156	40	50	0
D156	B156	C156	A156	0	0	0

Overall, I can say that all I need is a view that gives me the amount to be purchased according to the algorithm, but at the same time, I need to be able to update the amounts in hand. For this, I think I will need to edit the procedure. I unfortunately can't provide the procedure, so you can say that I am a bit lost. When it comes to editing the procedure for updating the amounts, maybe just an idea or a general example might help. There are several possible cases in this, but I am hoping that I can handle them once this main problem is solved. If needed, I can mention other possible cases as well. Thank you

Solution

Well, its not pretty but it seems to work for your situation where you could (it seems) have at most 3 replacement items.

Its literally 4 CTEs, each working out how much stock it can provide and then passing the remainder onto the next one.

It should stop calculating if all the stock is allocated, and it should, fingers crossed, work with multiple products at a time.

Note the need to maintain the alternative columns from the original record, while otherwise selecting values from the current record.

with cte1 as (
  select code original_code, a1.*
    , v.available
    , case when a1.minimum_target > 0 then case when a1.purchasing_request > v.available then v.available else a1.purchasing_request end else 0 end assigned
    , purchasing_request - case when a1.minimum_target > 0 then case when a1.purchasing_request > v.available then v.available else a1.purchasing_request end else 0 end purchasing_request_new
    , 0 order_by
  from alternatives a1
  cross apply (values (a1.amount_in_hand - a1.minimum_target)) v (available)
  cross apply (values (case when a1.minimum_target > 0 then case when a1.purchasing_request > v.available then v.available else a1.purchasing_request end else 0 end)) z (assigned)
  where purchasing_request > 0
), cte2 as (
  select cte1.original_code, a2.code, cte1.alternative_1, cte1.alternative_2, cte1.alternative_3
    , a2.minimum_target, a2.amount_in_hand, a2.purchasing_request
    , v.available
    , case when a2.minimum_target > 0 then case when cte1.purchasing_request_new > v.available then v.available else cte1.purchasing_request_new end else 0 end assigned
    , cte1.purchasing_request_new - case when a2.minimum_target > 0 then case when cte1.purchasing_request_new > v.available then v.available else cte1.purchasing_request_new end else 0 end purchasing_request_new
    , 1 order_by
  from alternatives a2
  cross apply (values (a2.amount_in_hand - a2.minimum_target)) v (available)
  join cte1 on cte1.alternative_1 = a2.code
  where cte1.purchasing_request_new > 0
), cte3 as (
  select cte2.original_code, a3.code, cte2.alternative_1, cte2.alternative_2, cte2.alternative_3
    , a3.minimum_target, a3.amount_in_hand, a3.purchasing_request
    , v.available
    , case when a3.minimum_target > 0 then case when cte2.purchasing_request_new > v.available then v.available else cte2.purchasing_request_new end else 0 end assigned
    , cte2.purchasing_request_new - case when a3.minimum_target > 0 then case when cte2.purchasing_request_new > v.available then v.available else cte2.purchasing_request_new end else 0 end purchasing_request_new
    , 2 order_by
  from alternatives a3
  cross apply (values (a3.amount_in_hand - a3.minimum_target)) v (available)
  join cte2 on cte2.alternative_2 = a3.code
  where cte2.purchasing_request_new > 0
), cte4 as (
  select cte3.original_code, a4.code, cte3.alternative_1, cte3.alternative_2, cte3.alternative_3
    , a4.minimum_target, a4.amount_in_hand, a4.purchasing_request
    , v.available
    , case when a4.minimum_target > 0 then case when cte3.purchasing_request_new > v.available then v.available else cte3.purchasing_request_new end else 0 end assigned
    , cte3.purchasing_request_new - case when a4.minimum_target > 0 then case when cte3.purchasing_request_new > v.available then v.available else cte3.purchasing_request_new end else 0 end purchasing_request_new
    , 3 order_by
  from alternatives a4
  cross apply (values (a4.amount_in_hand - a4.minimum_target)) v (available)
  join cte3 on cte3.alternative_3 = a4.code
  where cte3.purchasing_request_new > 0
)
select *,
  case when order_by = 1 then min(purchasing_request_new) over (partition by original_code) else 0 end purchase_request_final
from (
  select * from cte1
  union all
  select * from cte2
  union all
  select * from cte3
  union all
  select * from cte4
) x
order by order_by;

Returns:

original_code	code	alternative_1	alternative_2	alternative_3	minimum_target	amount_in_hand	purchasing_request	available	assigned	purchasing_request_new	order_by	purchase_request_final
D156	D156	B156	C156	A156	0	0	150	0	0	150	0	0
D156	B156	B156	C156	A156	30	65	0	35	35	115	1	104
D156	C156	B156	C156	A156	40	50	0	10	10	105	2	0
D156	A156	B156	C156	A156	15	16	0	1	1	104	3	0

Where: available, assigned, purchasing_request_new, order_by and purchasing_request_final are working columns that can be removed as desired.

DBFiddle