Using CONNECT BY PRIOR for a hierarchical query - Oracle SQL

I think you're looking for something like:

with rec(root_order, order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date)
as (
  -- anchor member
  select
    orders.order_number as root_order,
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
  union all
  -- recursive member
  select rec.root_order,
    requirements.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from rec
  join requirements on rec.replenishment_detail = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select *
from rec
order by root_order, order_number, requirement_id;

The anchor member is essentially your original query, except it add the root_order and I made the first join an inner one to reduce the noise a bit (a lot of the 87 rows from your original only have order_number with everything else null).

The recursive member then joins the rec.replenishment_detail (child order number) to requirements.order_number to walk down the hierarchy. It doesn't need to refer to the actual orders table again (unless you actually want some other field from that, in which case it's trivial to include it).

With your sample data that produces 65 rows of output, including:

ROOT_ORDER ORDER_NUMBER REQUIR REPLE REPLEN REPLENISHMENT_DETAIL REPLENISHM
---------- ------------ ------ ----- ------ -------------------- ----------
...
       300          130 AA-171 RO532 Make                    727 2018-05-17
       300          130 AA-171 RO92  Bought                  493 2018-04-01
       300          130 AA-785 RO573 Make                    616 2018-04-01
       300          130 AA-785 RO561 Make                    453 2018-05-13
       300          130 AA-785 RO775 Make                    974 2018-08-07
       300          130 AA-785 RO139 Make                    698 2018-07-16
       300          130 AA-785 RO252 Make                    190 2018-08-02
       300          300 AA-300 RO601 Bought                  963 2018-07-15
       300          300 AA-300 RO111 Make                    251 2018-10-23
       300          300 AA-300 RO435 Make                    837 2018-03-04
       300          300 AA-300 RO608 Make                    850 2018-04-27
       300          300 AA-468 RO96  Bought                  279 2018-06-11
       300          300 AA-468 RO120 Make                    333 2018-07-28
       300          300 AA-516 RO754 Bought                  874 2018-06-09
       300          300 AA-516 RO245 Bought                  130 2018-02-06
       300          300 AA-516 RO734 Make                    415 2018-05-05
       300          300 AA-744 RO169 Make                    804 2018-02-02
       300          300 AA-744 RO576 Make                    452 2018-06-09
       300          300 AA-744 RO592 Bought                  967 2018-01-16
       300          300 AA-744 RO104 Make                    232 2019-01-30
       300          493 AA-400 RO4   Bought                  591 2018-04-17
       300          493 AA-401                                             
...

db<>fiddle based on your original.

Notice though that it also includes the 'child' orders independently:

...
       130          130 AA-171 RO92  Bought                  493 2018-04-01
       130          130 AA-171 RO532 Make                    727 2018-05-17
       130          130 AA-785 RO775 Make                    974 2018-08-07
       130          130 AA-785 RO561 Make                    453 2018-05-13
       130          130 AA-785 RO252 Make                    190 2018-08-02
       130          130 AA-785 RO573 Make                    616 2018-04-01
       130          130 AA-785 RO139 Make                    698 2018-07-16
       130          493 AA-400 RO4   Bought                  591 2018-04-17
       130          493 AA-401                                             
...
       493          493 AA-400 RO4   Bought                  591 2018-04-17
       493          493 AA-401                                             
...

etc. You could start from a specific target order (i.e. have the anchor member have where orders.order_number = 300) but it isn't clear if that's what you want. If it isn't, and you don't want to see the lower orders on their own too, then you need a way to identify a top-level order. One way to do that might be to exclude any orders which appear as any replenishment_detail value, by adding a not exists(...) filter:

with rec(root_order, order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date)
as (
  -- anchor member
  select
    orders.order_number as root_order,
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
  where not exists (
    select *
    from replenishments
    where replenishment_detail = orders.order_number
  )
  union all
  -- recursive member
  select rec.root_order,
    requirements.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from rec
  join requirements on rec.replenishment_detail = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select *
from rec
order by root_order, order_number, requirement_id;

which now only gets 54 rows and excludes the 130/493/etc. root-order rows above.

db<>fiddle

Since you actually asked about a hierarchical query rather than a recursive one, here's how you could do that:

with cte (order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date, is_root_order)
as (
  select
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date,
    case when exists (
      select *
      from replenishments
      where replenishment_detail = orders.order_number
    ) then 'N' else 'Y' end
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select connect_by_root(order_number) as root_order,
  order_number, requirement_id, replenishment_id, replenishment_type,
  replenishment_detail, replenishment_date
from cte
start with is_root_order = 'Y'
connect by order_number = prior replenishment_detail;

The CTE is again pretty much your original query, with a case expression and exists clause to decide if each order is a 'root' one, as before - but now as a flag rather than a filter. The hierarchical query is then fairly straightforward, using that flag in its starts with clause.

Yet another db<>fiddle.

(I've just realised that this is pretty much what @StewAshton said to do; my CTE is essentially his 'substitute your joins' step. The only other real difference is that he's moved the flag calculation directly into the starts with clause, which might actually be slightly more efficient as it doesn't have to hit the replenishments table again...)

I generally prefer the recursive CTE approach, but the hierarchical one is attractive here just for its brevity. You might want to compare the performance of both approaches against your real data though.