Assignation counting up to maximum

I have the below 2 tables:

aux1:

PC	A	MAXI
PC1	A1	1
PC1	A2	2
PC2	A1	1
PC2	A2	2

aux:

VK	D	PC
VK1	8	PC1
VK2	7	PC1
VK3	6	PC2
VK4	5	PC2
VK5	4	PC1
VK6	3	PC1
VK7	2	PC2
VK8	1	PC2

I would like to obtain the following output:

VK	D	PC	A	ORDER_A_PC	ORDER_PC
VK1	8	PC1	A1	1	1
VK2	7	PC1	A2	1	2
VK3	6	PC2	A1	1	1
VK4	5	PC2	A2	1	2
VK5	4	PC1	A2	2	3
VK6	3	PC1	NA	NA	NA
VK7	2	PC2	A2	2	3
VK8	1	PC2	NA	NA	NA

The logic for the columns is:

A: ordering aux by D desc and partitioning by PC, assign A (A1 or A2) until the count over partition reaches the maxi configured in table aux1 for each PC and A.
ORDER_A_PC: ordered count over partition by A and PC until the count exceeds the maxi for that PC, A in table aux1
ORDER_PC: same as ORDER_A_PC but partitioned by just PC.

with aux (vk, pc) as (
    select 'VK1', 'PC1' from dual union all
    select 'VK2', 'PC1' from dual union all
    select 'VK3', 'PC2' from dual union all
    select 'VK4', 'PC2' from dual union all
    select 'VK5', 'PC1' from dual union all
    select 'VK6', 'PC1' from dual union all
    select 'VK7', 'PC2' from dual union all
    select 'VK8', 'PC2' from dual),
aux1 (pc, a, maxi) as (
    select 'PC1', 'A1', 1 from dual union all 
    select 'PC1', 'A2', 2 from dual union all 
    select 'PC2', 'A1', 1 from dual union all 
    select 'PC2', 'A2', 2 from dual)
select * from aux

Answer

Nice little problem. You can distribute the assets preprocessing the ranges and then joining by range.

For example, you can do:

with aux (vk, pc) as (
    select 'VK1', 'PC1' from dual union all
    select 'VK2', 'PC1' from dual union all
    select 'VK3', 'PC2' from dual union all
    select 'VK4', 'PC2' from dual union all
    select 'VK5', 'PC1' from dual union all
    select 'VK6', 'PC1' from dual union all
    select 'VK7', 'PC2' from dual union all
    select 'VK8', 'PC2' from dual),
aux1 (pc, a, maxi) as (
    select 'PC1', 'A1', 1 from dual union all 
    select 'PC1', 'A2', 2 from dual union all 
    select 'PC2', 'A1', 1 from dual union all 
    select 'PC2', 'A2', 2 from dual),
s as (
  select a.*,
    coalesce(sum(maxi) over(partition by pc order by pc, a 
      rows between unbounded preceding and 1 preceding), 0) + 1 as first_maxi,
    sum(maxi) over(partition by pc order by pc, a) as last_maxi
  from aux1 a
),
x as (
  select a.*,
    row_number() over(order by vk) as rn
  from aux a
),
y as (
  select a.*,
    (select count(*) from x b where b.rn <= a.rn and b.pc = a.pc) as cnt
  from x a
)
select
  y.vk, y.pc, s.a,
  case when s.pc is not null then row_number() 
    over(partition by y.pc, s.a order by y.rn) end as order_a_pc,
  case when s.pc is not null then row_number() 
    over(partition by y.pc order by y.rn) end as order_pc
from y
left join s on s.pc = y.pc and y.cnt between s.first_maxi and s.last_maxi
order by y.rn

Result:

VK   PC   A       ORDER_A_PC  ORDER_PC
---  ---  ------  ----------  --------
VK1  PC1  A1               1         1
VK2  PC1  A2               1         2
VK3  PC2  A1               1         1
VK4  PC2  A2               1         2
VK5  PC1  A2               2         3
VK6  PC1  <null>      <null>    <null>
VK7  PC2  A2               2         3
VK8  PC2  <null>      <null>    <null>

See running example at db<>fiddle.