SELECT dept,supp,class,style,item_desc,vpn,color FROM  (SELECT DISTINCT /*+parallel(4) ordered */ im.dept, isp.supplier supp, im.class, im.item_parent style, (SELECT im1.item_desc
                               FROM rmsapps.item_master im1
                              WHERE im1.item = im.item_parent) item_desc, isp.vpn, im.diff_2 color FROM item_supplier isp, item_master im, (SELECT NVL(a.item,'0') item
                                FROM (SELECT DISTINCT /*+ use_hash */ il.item
                                        FROM item_loc il,
                                             repl_eligibility bre
                                       WHERE bre.status = 'A'
                                         AND il.status = 'A'
                                                     AND il.item = bre.item
                                                     AND il.loc = bre.location
                                                     AND EXISTS (SELECT im2.item
                                                                   FROM item_master im2
                                                                  WHERE im2.item = il.item
                                                                    AND im2.forecast_ind = 'Y'
                                                                    AND im2.item_level = 2
                                                                    AND im2.pack_ind = 'N' AND im2.dept = 292) AND EXISTS(SELECT NULL /*+ index */
                                                                                                                          FROM item_loc_hist ilh
                                                                                                                          WHERE (eow_date) < ADD_MONTHS(SYSDATE,-12)
                                                                                                                          AND ilh.item=il.item AND il.loc=ilh.loc )) a ) b  WHERE im.item = isp.item  AND im.item = b.item 
 ORDER BY dept, class, supp, style, color);