Oracle SQL use subquery value in another subquery

Before I go any further please mind that I am not well experienced with SQL.

I have one query that is getting a single value (netto value) such as:

WITH cte_value_net AS (
    SELECT
        nvl(min(value_net),0) as value_net
    FROM (
        SELECT
            i.serial as serial,
            nvl(lag(i.value_net) OVER (PARTITION BY i.serial ORDER BY i.month), i.value_net) as value_net
        FROM 
            inventory i
        WHERE 
            i.ctypde IN (
                SELECT
                    ctypde
                FROM
                    appar ap
                WHERE 
                    ap.serial = in_serial -- this is the variable I want to set
            )
        AND 
            i.month IN (to_char(add_months(sysdate, -1), 'YYYYMM'), to_char(add_months(sysdate, -2), 'YYYYMM'))
        AND
            i.serial = in_serial -- this is the variable I want to set
    ) vn
    GROUP BY vn.serial
)

In here I have to feed in the variable in_serial that I thought I could get from another subquery such as:

SELECT
    (SELECT * FROM cte_value_net) AS value_net
FROM (
    SELECT
        lap.serial AS in_serial
    FROM
        applap lap
)

but I can not wrap my head around it why this in_serial is not visible to my custom CTE. Could someone explain me how can I propagate the value from subquery like this?

The error I am obviously getting is:

SQL Error [904] [42000]: ORA-00904: "IN_SERIAL"

Unfortunately I do not have any sample data. What I want to achieve is that I could feed in the returned in_serial from main subquery to my CTE.

Before I can get value_net I need my main query to return the in_serial, otherwise I do not have access to that value.

Solution

The trick I use is to produce an extra CTE that I usually call params that includes a single row with all computed parameters. Then, it's a matter of performing a CROSS JOIN with this CTE in any other CTE, subquery or main query, as needed.

For example:

with
params as ( -- 1. Create a CTE that returns a single row
  select serial as in_serial from applap
),
cte_value_net AS (
  select ...
  from inventory i
  cross join params -- 2. cross join against the CTE anywhere you need it
  where ...
    and i.serial = params.in_serial -- 3. Use the parameter
)
select ...