Query Based on Recursion to Deduct total quantity from lines

We had a requirement that the total quantity should be deducted from the possible containing lines so for example

Total Available Quantity = 5

Existing Quantity = 7

Here the 7 quantity has been split into 4 lines. The query should dynamically only take the available quantity lines till it gets consumed. Below is the logic for the same using recursion.

WITH params AS (
    -- Define the total amount to deduct (e.g., 50)
    SELECT 5 AS total_needed FROM dual
),
ordered_stock AS (
    -- Order stock by priority (e.g., oldest batch_id first)
    SELECT TRANSACTION_TEMP_ID,
           TRANSACTION_QUANTITY,
           PRIMARY_QUANTITY,
           ROW_NUMBER() OVER (ORDER BY TRANSACTION_TEMP_ID) as rn
    FROM mtl_material_transactions_temp
     where move_order_line_id = 52502755
),
deduction_tree (rn, TRANSACTION_TEMP_ID,  TRANSACTION_QUANTITY, PRIMARY_QUANTITY, remaining_needed, taken) AS (
    -- 1. Anchor Member: Start with the first batch
    SELECT 
        rn,
        TRANSACTION_TEMP_ID
        ,TRANSACTION_QUANTITY
       , PRIMARY_QUANTITY
       
        ,(SELECT total_needed FROM params) - TRANSACTION_QUANTITY AS remaining_needed,
        CASE 
            WHEN (SELECT total_needed FROM params) >= TRANSACTION_QUANTITY THEN TRANSACTION_QUANTITY
            ELSE (SELECT total_needed FROM params)
        END AS taken
    FROM ordered_stock
    WHERE rn = 1

    UNION ALL

    -- 2. Recursive Member: Deduct from subsequent batches
    SELECT 
        os.rn,
        os.TRANSACTION_TEMP_ID
        ,os.TRANSACTION_QUANTITY
       , os.PRIMARY_QUANTITY
       , dt.remaining_needed - os.TRANSACTION_QUANTITY,
        CASE 
            WHEN dt.remaining_needed >= os.TRANSACTION_QUANTITY THEN os.TRANSACTION_QUANTITY
            ELSE CASE WHEN dt.remaining_needed < 0 THEN 0 ELSE dt.remaining_needed END
        END AS taken
    FROM deduction_tree dt
    JOIN ordered_stock os ON os.rn = dt.rn + 1
    WHERE dt.remaining_needed > 0 -- Stop when nothing more is needed
)
SELECT 
    oss.TRANSACTION_TEMP_ID,
    dt.TRANSACTION_QUANTITY
    ,nvl(dt.taken, 0) AS amount_used
FROM deduction_tree dt, ordered_stock oss where dt.TRANSACTION_TEMP_ID(+) = oss.TRANSACTION_TEMP_ID;

Reproduce packages from one user to another

Export configuration manifest

guix package --export-manifest > manifest.scm

Export channels

guix describe -f channels > channels.scm

Reproducing on Target machine

guix time-machine -C channels.scm --package -m manifest.scm

Installing tamil language in Gnu guix

As I have recently installed gnu guix I needed tamil 99 keyboard and also when opening the web pages it was coming as boxes. I have installed by below command for user wise

guix install font-lohit font-gnu-freefont font-google-noto emacs-tamil99

Once Installed i have restarted the system to apply changes

Open emacs and press C-\ to change the keyboard input method and type tamil99.