split into rows by maximum value of a accumulated column

I can't find how to split in the rows from an accumulated sum. I have the following results:

+---------+-----------------+---------+---------------+
|ORDER_ID|DENOM_ID|DENOM_VAL|   AMOUNT|MAX_PAGE_AMOUNT|
+--------+--------+---------+---------+---------------+
|AAAAAAAA|EUR100  |      100|2,800,000|     1,000,000 |
|AAAAAAAA|EUR050  |       50|  700,000|     1,000,000 |
|AAAAAAAA|EUR010  |       10|  150,000|     1,000,000 |
+--------+--------+---------+---------+---------------+

• ORDER_ID: Order number
• DENOM_ID: Currency denomination ID
• DENOM_VAL: Real currency value
• AMOUNT: Total amount of currency denomination
• MAX_PAGE_AMOUNT: Max. currency per report page

The SQL query should generate the following rows:

+-------+--------+--------+---------+---------+----------+---------------+
|PAG_NUM|ORDER_ID|DENOM_ID|DENOM_VAL|   AMOUNT|NEW_AMOUNT|MAX_PAGE_AMOUNT|
+-------+--------+--------+---------+---------+----------+---------------+
|      1|AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
|      2|AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
|      3|AAAAAAAA|EUR100  |      100|2,800,000|   800,000|      1,000,000|
|      3|AAAAAAAA|EUR050  |       50|  700,000|   200,000|      1,000,000|
|      4|AAAAAAAA|EUR050  |       50|  700,000|   300,000|      1,000,000|
|      4|AAAAAAAA|EUR010  |       10|  150,000|   150,000|      1,000,000|
+-------+--------+--------+---------+---------+----------+---------------+

• PAG_NUM: Page number
• ORDER_ID: Order number
• DENOM_ID: Currency denomination ID
• DENOM_VAL: Real currency value
• AMOUNT: Total amount of currency denomination
• NEW_AMOUNT: New total amount that cannot exceed MAX_PAGE_AMOUNT per page
• MAX_PAGE_AMOUNT: Max. amount by report page

Summary:
The summed amounts per page cannot exceed MAX_PAGE_AMOUNT, when that occurs AMOUNT must be divided/split in a new page until it does not exceed the value of MAX_PAGE_AMOUNT.

My current SQL is:

WITH cte_dat AS (
 SELECT order_id, denom_id, denom_val, amount, max_page_amount
 FROM my_table
),
cte_rec (order_id, denom_id, denom_val, amount, max_page_amount) AS (
 SELECT order_id, denom_id, denom_val, amount, max_page_amount
 FROM cte_dat
 UNION ALL
 SELECT order_id, denom_id, denom_val, amount, amount - max_page_amount AS amount
 FROM cte_rec
 WHERE (amount - max_page_amount) > 0
)
SELECT order_id, denom_id, denom_val, LEAST(amount, max_page_amount) AS amount, max_page_amount
FROM cte_rec
ORDER BY order_id, denom_val DESC, amount DESC

Results:

+--------+--------+---------+---------+----------+---------------+
|ORDER_ID|DENOM_ID|DENOM_VAL|   AMOUNT|MAX_AMOUNT|MAX_PAGE_AMOUNT|
+--------+--------+---------+---------+----------+---------------+
|AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
|AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
|AAAAAAAA|EUR100  |      100|2,800,000|   800,000|      1,000,000|
|AAAAAAAA|EUR050  |       50|  700,000|   700,000|      1,000,000|
|AAAAAAAA|EUR010  |       10|  150,000|   150,000|      1,000,000|
+--------+--------+---------+---------+----------+---------------+

You can use an analytic function to generate the running totals for the amounts and use a recursive query to split that into pages:

WITH cte_rec (
  order_id,
  denom_id,
  denom_val,
  amount,
  max_page_amount,
  prev_total,
  total,
  start_page,
  end_page
) AS (
  SELECT order_id,
         denom_id,
         denom_val,
         amount,
         max_page_amount,
         SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM) - amount,
         SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM),
         GREATEST(
           CEIL(
             (SUM(amount)
                OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM) - amount)
             / max_page_amount
           ),
           1
         ),
         CEIL(
           SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount, ROWNUM)
           / max_page_amount
         )
  FROM   my_table
UNION ALL
  SELECT order_id,
         denom_id,
         denom_val,
         amount,
         max_page_amount,
         prev_total,
         total,
         start_page + 1,
         end_page
  FROM   cte_rec
  WHERE  start_page < end_page
)
SEARCH DEPTH FIRST BY order_id, denom_val DESC, amount DESC SET o_id
SELECT start_page AS page_num,
       order_id,
       denom_id,
       denom_val,
       amount,
       LEAST(start_page * max_page_amount, total)
         - GREATEST((start_page - 1) * max_page_amount, prev_total) AS new_amount,
       max_page_amount
FROM   cte_rec;

Which, for the sample data:

CREATE TABLE my_table (ORDER_ID, DENOM_ID, DENOM_VAL, AMOUNT, MAX_PAGE_AMOUNT) AS
SELECT 'AAAAAAAA', 'EUR100', 100, 2800000, 1000000 FROM DUAL UNION ALL
SELECT 'AAAAAAAA', 'EUR050',  50,  700000, 1000000 FROM DUAL UNION ALL
SELECT 'AAAAAAAA', 'EUR010',  10,  150000, 1000000 FROM DUAL;

Outputs:

fiddle

Use CONNECT BY LEVEL to generate the necessary number of rows based on the division of amount by max_page_amount.

SELECT
      order_id
    , denom_id
    , denom_val
    , amount
    , max_page_amount
FROM my_table CONNECT BY LEVEL <= CEIL(amount / max_page_amount)
WHERE PRIOR order_id = order_id
    AND PRIOR denom_id = denom_id
    AND PRIOR denom_val = denom_val
    AND PRIOR amount = amount
    AND PRIOR max_page_amount = max_page_amount

CEIL is used to ensure that all the rows are included even if the division is not exact, and the PRIOR keyword is used to ensure that the repeated rows have the same values as the original row.

If you prefer not to use Oracle specific functions, then using a "recursive CTE" should also work:

WITH CTE
AS (
    SELECT order_id, denom_id, denom_val, amount, max_page_amount, 1 AS lvl
    FROM my_table
    
    UNION ALL
    
    SELECT order_id, denom_id, denom_val, amount, max_page_amount, lvl + 1
    FROM CTE
    WHERE lvl < CEIL(amount / max_page_amount)
    )
SELECT
      order_id
    , denom_id
    , denom_val
    , amount
    , max_page_amount
FROM CTE;

There is another solution based on calculation of intervals on the data and buckets of max_page_amount, it could be interesting to compare the performances on your data set, here recursion is used only to generate the buckets:

with intervals as (
	select d.*, lag(sum_amount,1,0) over(order by rn) as start_interval, sum_amount as end_interval
	from (
		select rownum as rn,  order_id, denom_id, denom_val, amount, max_page_amount,
		sum(amount) over(order by rownum) as sum_amount
		from my_table 
	) d
)
, maxdata as (
	select max_page_amount, sum(amount) as max_amount from my_table1
	group by max_page_amount
)
, buckets as (
	select level as bucket_id, (level-1) * max_page_amount as start_bucket, level * max_page_amount as end_bucket 
	from maxdata
	connect by (level-1) * max_page_amount < max_amount 
)
select bucket_id as page_num, order_id, denom_id, denom_val, amount, max_page_amount,
	case when end_interval > end_bucket 
		then 
			least(max_page_amount, end_bucket - start_interval)
		else
			least(amount, end_interval - start_bucket)
	end as new_page_amount
from intervals
join buckets on start_bucket <= end_interval and end_bucket >= start_interval
;