/*
  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'
        ELSE 'Concluido'
      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(si.actual_qty) FROM t_stored_item si, t_location l
       WHERE l.location_id = si.location_id
       AND si.item_number = ord.item_number
       AND si.wh_id = orm.wh_id
       AND l.TYPE = 'I'
       GROUP BY si.wh_id, si.item_number) AS "QTD PECAS BUF"
     , (SELECT SUM(si.actual_qty) FROM t_stored_item si, t_location l
        WHERE l.location_id = si.location_id
        AND si.item_number = ord.item_number
        AND si.wh_id = orm.wh_id
        AND l.TYPE != 'I'
        --and l.location_id like 'RECE%'
        AND l.location_id NOT IN ('PRE_RECE_TRANSF','RETENCAO')
        AND NOT EXISTS (SELECT 1 FROM t_tran_log t
                        WHERE l.wh_id = t.wh_id
                        AND l.TYPE = 'I'
                        AND t.location_id_2 = l.location_id
                        AND t.wh_id = si.wh_id
                        AND t.hu_id = si.hu_id) -- Armazenado alguma vez
        GROUP BY si.wh_id, si.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;