Query Buffer Barcellos Corrigida

/*
  Query criada por Barcellos. Chamado 122490.

*/

SELECT ARMAZEM
      ,ITM.BR_ITEM_PAI
      ,ITEM
      ,ITM.br_hie_sub_class AS Subclasse
      ,ALOCACAO
      ,TIPO
      ,DATAALOCACAO
      ,USF_GET_SKU_PACK_QTY(ITEM, ARMAZEM, "QTD ALOCADA") AS "QTD ALOCADA PECAS"
      ,ONDA
      ,CASE
        WHEN ONDA IS NULL
          THEN 'Nao Atribuido'
        WHEN EXISTS (SELECT 1 FROM t_pick_detail PKD2
                  WHERE PKD2.wh_id = cons.ARMAZEM
                    AND PKD2.wave_id = cons.ONDA
                    AND PKD2.item_number = cons.ITEM
                    AND PKD2.TYPE <> 'PP'
                    AND PKD2.status = 'RELEASED')
          THEN 'Pendente'
        /* Add Djonathan 31/01/17 */
        WHEN EXISTS (SELECT 1 FROM t_pick_detail PKD2
                     WHERE PKD2.wh_id = cons.ARMAZEM
                     AND PKD2.wave_id = cons.ONDA
                     AND PKD2.item_number = cons.ITEM
                     AND PKD2.TYPE <> 'PP'
                     AND PKD2.status = 'STAGED')
          THEN 'Em Stage'
        WHEN EXISTS (SELECT 1 FROM t_pick_detail PKD2
                     WHERE PKD2.wh_id = cons.ARMAZEM
                     AND PKD2.wave_id = cons.ONDA
                     AND PKD2.item_number = cons.ITEM
                     AND PKD2.TYPE <> 'PP'
                     AND PKD2.status = 'SHIPPED')
          THEN 'Expedido'
        -- ELSE 'Concluido'
        ELSE (SELECT pkd2.status FROM t_pick_detail PKD2
              WHERE PKD2.wh_id = cons.ARMAZEM
              AND PKD2.wave_id = cons.ONDA
              AND PKD2.item_number = cons.ITEM
              AND PKD2.TYPE <> 'PP')
       /* Fim add DJ */
      END AS STATUS
      ,"CRIACAO ONDA"
      ,"RELEASED DATE"
      ,"ULTIMA SEPAR"
      ,USF_GET_SKU_PACK_QTY(ITEM, ARMAZEM, "QTD SEP") AS "QTD SEP PECAS"
      ,USF_GET_SKU_PACK_QTY(ITEM, ARMAZEM, "QTD DIS") AS "QTD DIS PECAS"
      ,USF_GET_SKU_PACK_QTY(ITEM, ARMAZEM, "QTD EXP") AS "QTD EXP PECAS"
      ,USF_GET_SKU_PACK_QTY(ITEM, ARMAZEM, "QTD PECAS BUF") AS "QTD PECAS BUF"
      ,USF_GET_SKU_PACK_QTY(ITEM, ARMAZEM, "QTD PECAS PRE BUF") AS "QTD PECAS PRE BUF"
FROM (SELECT  orm.wh_id AS "ARMAZEM"
     ,ord.item_number AS "ITEM"
     ,ord.order_number AS "ALOCACAO"
     ,(SELECT t.text FROM t_lookup t WHERE t.lookup_id = orm.type_id) AS "TIPO"
     ,orm.order_date AS "DATAALOCACAO"
     ,SUM(ord.qty) AS "QTD ALOCADA"
     ,wm.wave_id AS "ONDA"
     ,wm.CREATED_DATE AS "CRIACAO ONDA"
     ,wm.released_date AS "RELEASED DATE"
     ,(SELECT MAX(TO_CHAR(tl.end_tran_date, 'DD/MM/YYYY') || ' ' || TO_CHAR(tl.end_tran_time,'HH:MI:SS')) FROM t_tran_log tl
       WHERE tl.tran_type IN ('326','327','328')
       AND tl.control_number IN (SELECT pkd.order_number FROM t_pick_detail pkd
                                 WHERE pkd.wave_id = wm.wave_id
                                 AND pkd.item_number = ord.item_number
                                 AND (pkd.work_type = '26' OR pkd.work_type = '23'))) AS "ULTIMA SEPAR"
     ,(SELECT SUM(pkd.staged_quantity) FROM t_pick_detail pkd
       WHERE pkd.wave_id = wm.wave_id
       AND pkd.item_number = ord.item_number
       AND (pkd.work_type = '26' OR pkd.work_type = '23')) AS "QTD SEP"
     ,SUM(pkd.staged_quantity) AS "QTD DIS"
     ,SUM(pkd.shipped_quantity) AS "QTD EXP"
     ,(SELECT SUM(sto.actual_qty) FROM t_stored_item sto, t_location loc
       WHERE loc.location_id = sto.location_id
       AND sto.item_number = ord.item_number
       AND sto.wh_id = orm.wh_id
       AND loc.TYPE = 'I'
       AND sto.wh_id = loc.wh_id -- Add DJ 31/07/17
       GROUP BY si.wh_id, si.item_number) AS "QTD PECAS BUF"
     , (SELECT SUM(sto.actual_qty)
        FROM t_stored_item sto, t_location loc
        WHERE loc.location_id = sto.location_id
        AND sto.wh_id = loc.wh_id -- Add DJ 31/07/17
        AND sto.item_number = ord.item_number
        AND sto.wh_id = orm.wh_id
        AND loc.TYPE != 'I'
        --and loc.location_id like 'RECE%'
        AND loc.location_id NOT IN ('PRE_RECE_TRANSF','RETENCAO')
        AND NOT EXISTS (SELECT 1 FROM t_tran_log LOG
                        WHERE loc.wh_id = LOG.wh_id
                        AND loc.TYPE = 'I'
                        AND LOG.location_id_2 = loc.location_id
                        AND LOG.wh_id = sto.wh_id
                        AND LOG.hu_id = sto.hu_id) -- Armazenado alguma vez
        GROUP BY sto.wh_id, sto.item_number;) AS "QTD PECAS PRE BUF"
      FROM t_order orm
        INNER JOIN t_order_detail ord
          ON ord.order_number = orm.order_number
          AND ord.wh_id = orm.wh_id
        LEFT JOIN t_pick_detail pkd
          ON pkd.order_number = orm.order_number
          AND pkd.wh_id = orm.wh_id
          AND pkd.line_number = ord.line_number
          AND pkd.item_number = ord.item_number
        LEFT JOIN t_wave_master wm
          ON wm.wave_id = pkd.wave_id
          AND wm.wh_id = pkd.wh_id
WHERE TRUNC(orm.order_date) >= TRUNC(SYSDATE - 90)
AND orm.type_id IN ('1670','1902')
AND orm.wh_id = '114'
GROUP BY orm.wh_id, ord.item_number, ord.order_number, orm.type_id, orm.order_date, wm.wave_id, wm.released_date, wm.created_date
ORDER BY orm.order_date DESC) cons
INNER JOIN t_item_master ITM
   ON ITM.wh_id = cons.ARMAZEM
   AND ITM.item_number = cons.ITEM;

SELECT * FROM t_pick_detail
WHERE wave_id = 'wave31.07.11.16cx';