What is this view being used in my query? I'm running EXPLAIN PLAN on this query:

SELECT DISTINCT l.item_num, l.repl_code
FROM item_w l
JOIN item i ON l.item_num = i.item_num
LEFT JOIN item_w r ON l.item_num = r.item_num AND l.repl_code = r.whse_code
WHERE r.whse_code IS NULL 
  AND l.repl_code IN (SELECT DISTINCT whse_code FROM item_w)
  AND i.sa_item NOT LIKE 'Z%'
ORDER BY l.item_num, l.repl_code


The query works as desired but the plan is telling me that it is using objects named VW_DTP_F6D0ECAD, VW_DTP_53909802 and VW_DTP_69531A7E which I'm not able to find in my database.

What are these objects and how are they being created?
Views starting with VW_DTP showing up in the execution plan is the result of one of the cost-based query transformations available since 11.2, called distinct placement.

It happens with statements using the DISTINCT clause, just like yours. It is an absolutely acceptable an quite obvious execution plan to join the tables and filter the rows, and after that, find distinct rows. However, finding distinct rows at the time of accessing the tables, before joining them, may be more effective, and the database may choose to transform the query based on the estimated cost.