split into rows by maximum value of a accumulated column

Question

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|
+--------+--------+---------+---------+----------+---------------+

Answer 1

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:

PAGE_NUM	ORDER_ID	DENOM_ID	DENOM_VAL	AMOUNT	NEW_AMOUNT	MAX_PAGE_AMOUNT
1	AAAAAAAA	EUR100	100	2800000	1000000	1000000
2	AAAAAAAA	EUR100	100	2800000	1000000	1000000
3	AAAAAAAA	EUR100	100	2800000	800000	1000000
3	AAAAAAAA	EUR050	50	700000	200000	1000000
4	AAAAAAAA	EUR050	50	700000	500000	1000000
4	AAAAAAAA	EUR010	10	150000	150000	1000000

fiddle