Rewriting Query – avoiding UNION in Oracle SQL

I got a requirement based on this I will also optimize the Query

Requirement : We have Items and there will be substitute Items defined. Now if the Item has Subsitute Item then we need Subsititute Item as well the Item given. If the item is given without subsitute the same Item should come

Scenario: We have 3 items A, B, C respectively. B has been substituted to A. C has no substitution
When A is called then we need A and B item.
When B item is called we need A
When C item is called we need C

Usually how we start to Handle is by writing below using UNION

select distinct m.* from mtl_system_items_b m, mtl_related_items r
where 1=1 and m.inventory_item_id = r.related_item_id and m.organization_id = 101 -- Organization ID to get Unique Items
and r.INVENTORY_ITEM_ID = :P_INVENTORY_ITEM_ID
union
select distinct m.* from mtl_system_items_b m, mtl_related_items r
where 1=1 and m.inventory_item_id = r.inventory_item_id and m.organization_id = 101 -- Organization ID to get Unique Items
and r.INVENTORY_ITEM_ID = :P_INVENTORY_ITEM_ID
union
select distinct m.* from mtl_system_items_b m, mtl_related_items r
where 1=1 and m.inventory_item_id = r.inventory_item_id(+) and m.organization_id = 101 -- Organization ID to get Unique Items
and m.INVENTORY_ITEM_ID = :P_INVENTORY_ITEM_ID;

We can remove the UNION keyword by rewriting to below Query

select distinct rm.* from mtl_related_items r, mtl_system_items_b rm -- Need to remove Master org and optimize
where 1=1 and rm.INVENTORY_ITEM_ID = r.RELATED_ITEM_ID(+)
and rm.organization_id = 101 and (rm.INVENTORY_ITEM_ID = (select distinct RELATED_ITEM_ID from mtl_system_items_b m, mtl_related_items r
where 1=1 and m.inventory_item_id = r.related_item_id and m.organization_id = 101
and r.INVENTORY_ITEM_ID = :p_INVENTORY_ITEM_ID )
or rm.INVENTORY_ITEM_ID = nvl(:p_INVENTORY_ITEM_ID,rm.INVENTORY_ITEM_ID));

On Further Optimizing

select rm.* from mtl_system_items_b rm
where rm.organization_id = 101
and (rm.INVENTORY_ITEM_ID in (select RELATED_ITEM_ID from mtl_system_items_b m, mtl_related_items r
where 1=1 and m.inventory_item_id = r.related_item_id and m.organization_id = 101 -- Organization ID to get Unique Items
and r.INVENTORY_ITEM_ID = :P_INVENTORY_ITEM_ID )
or rm.INVENTORY_ITEM_ID = nvl(:P_INVENTORY_ITEM_ID,rm.INVENTORY_ITEM_ID));

This query can be even Optimized and the same scenario can be rewritten using below concepts and many more based on our creativity.

1. Hierarchial Query
2. Using With Clause

If you have any other concepts of rewriting or optimize please share

Radio Button selection changes in Radio Group OAF

While using Radio Group in OAF and mapping a transient VO will cause the inconsistency in selected Radio button. To prevent this change the transient VO Attribute to a EO based VO Attribute and then this Issue will not occur.