/* SCRIPT PARA RETORNAR STATUS DA EXPEDIÇÃO
 * VERSÃO:          1.0
 * ALTERAÇÂO:       11/11/2016
 * MODIFICADO POR : LUCAS CHINELATE
 */

DECLARE
   idExpedicao            NUMBER;
   deletaReservas         VARCHAR(1);
   deletaRessuprimentos   VARCHAR(1);
   retornaStatusExpedicao VARCHAR(1);

   idReservaSaida         VARCHAR(10000);
   idReservaRessuprimento VARCHAR(10000);
   idOndasRessuprimento   VARCHAR(10000);
BEGIN
  idExpedicao := :idExpedicao;
  deletaReservas := 'S';
  deletaRessuprimentos := 'S';
  retornaStatusExpedicao := 'S';

  /* DELETA OS RESSUPRIMENTOS GERADOS PELA EXPEDIÇÂO */
  IF (deletaRessuprimentos = 'S') THEN

      --GUARDEI EM UMA VARIAVEL OS IDS DAS ONDAS DE RESSUPRIMENTO
      SELECT ONDA INTO idOndasRessuprimento
        FROM (SELECT LISTAGG(COD_ONDA_RESSUPRIMENTO,',') WITHIN GROUP (ORDER BY COD_ONDA_RESSUPRIMENTO) ONDA
                FROM (SELECT DISTINCT COD_ONDA_RESSUPRIMENTO
                         FROM ONDA_RESSUPRIMENTO_PEDIDO ORP
                         LEFT JOIN PEDIDO P ON ORP.COD_PEDIDO = P.COD_PEDIDO
                         LEFT JOIN CARGA C ON C.COD_CARGA = P.COD_CARGA
                         WHERE C.COD_EXPEDICAO = idExpedicao));

      --GUARDEI EM UMA VARIAVEL OS IDS DAS RESERVAS DE ESTOQUE GERADAS PELA ONDA DE RESSUPRIMENTO
      SELECT RESERVA INTO idReservaRessuprimento
        FROM (SELECT LISTAGG(COD_RESERVA_ESTOQUE,',') WITHIN GROUP (ORDER BY COD_RESERVA_ESTOQUE) RESERVA
                FROM RESERVA_ESTOQUE_ONDA_RESSUP REOR
               INNER JOIN ONDA_RESSUPRIMENTO_OS  ORO ON ORO.COD_ONDA_RESSUPRIMENTO_OS = REOR.COD_ONDA_RESSUPRIMENTO_OS
               WHERE COD_ONDA_RESSUPRIMENTO IN (SELECT REGEXP_SUBSTR(idOndasRessuprimento, '[^,]+', 1, LEVEL) AS R FROM DUAL
                                               CONNECT BY REGEXP_SUBSTR (idOndasRessuprimento, '[^,]+', 1, LEVEL) IS NOT NULL));

      --DELETO OS REGISTROS NA TABELA RESERVA_ESTOQUE_ONDA_RESSUP
       DELETE FROM RESERVA_ESTOQUE_ONDA_RESSUP
        WHERE COD_ONDA_RESSUPRIMENTO_OS IN
            (SELECT COD_ONDA_RESSUPRIMENTO_OS FROM ONDA_RESSUPRIMENTO_OS
              WHERE COD_ONDA_RESSUPRIMENTO IN (SELECT REGEXP_SUBSTR(idOndasRessuprimento, '[^,]+', 1, LEVEL) AS R FROM DUAL
                                              CONNECT BY REGEXP_SUBSTR (idOndasRessuprimento, '[^,]+', 1, LEVEL) IS NOT NULL));

       --DELETO AS RESERVAS DE ESTOQUE
       DELETE FROM RESERVA_ESTOQUE
       WHERE COD_RESERVA_ESTOQUE IN (SELECT REGEXP_SUBSTR(idReservaRessuprimento, '[^,]+', 1, LEVEL) AS R FROM DUAL
                                    CONNECT BY REGEXP_SUBSTR (idReservaRessuprimento, '[^,]+', 1, LEVEL) IS NOT NULL);

        --DELETO ONDA_RESSUPRIMENTO_OS_PRODUTO
       DELETE FROM ONDA_RESSUPRIMENTO_OS_PRODUTO
        WHERE COD_ONDA_RESSUPRIMENTO_OS IN
            (SELECT COD_ONDA_RESSUPRIMENTO_OS FROM ONDA_RESSUPRIMENTO_OS
              WHERE COD_ONDA_RESSUPRIMENTO IN (SELECT REGEXP_SUBSTR(idOndasRessuprimento, '[^,]+', 1, LEVEL) AS R FROM DUAL
                                              CONNECT BY REGEXP_SUBSTR (idOndasRessuprimento, '[^,]+', 1, LEVEL) IS NOT NULL));

        --DELETO ONDA_RESSUPRIMENTO_OS
       DELETE FROM ONDA_RESSUPRIMENTO_OS
        WHERE COD_ONDA_RESSUPRIMENTO IN (SELECT REGEXP_SUBSTR(idOndasRessuprimento, '[^,]+', 1, LEVEL) AS R FROM DUAL
                                        CONNECT BY REGEXP_SUBSTR (idOndasRessuprimento, '[^,]+', 1, LEVEL) IS NOT NULL);

       --DELETO AS ORDENS DE SERVIÇO
       DELETE FROM ORDEM_SERVICO
        WHERE COD_OS IN (SELECT COD_OS FROM ONDA_RESSUPRIMENTO_OS
                          WHERE COD_ONDA_RESSUPRIMENTO IN (SELECT REGEXP_SUBSTR(idOndasRessuprimento, '[^,]+', 1, LEVEL) AS R FROM DUAL
                                                          CONNECT BY REGEXP_SUBSTR (idOndasRessuprimento, '[^,]+', 1, LEVEL) IS NOT NULL));

        --DELETO ONDA_RESSUPRIMENTO_PEDIDO
       DELETE FROM ONDA_RESSUPRIMENTO_PEDIDO
        WHERE COD_ONDA_RESSUPRIMENTO IN (SELECT REGEXP_SUBSTR(idOndasRessuprimento, '[^,]+', 1, LEVEL) AS R FROM DUAL
                                        CONNECT BY REGEXP_SUBSTR (idOndasRessuprimento, '[^,]+', 1, LEVEL) IS NOT NULL);

       --DELETO ONDA_RESSUPRIMENTO
       DELETE FROM ONDA_RESSUPRIMENTO
        WHERE COD_ONDA_RESSUPRIMENTO IN (SELECT REGEXP_SUBSTR(idOndasRessuprimento, '[^,]+', 1, LEVEL) AS R FROM DUAL
                                        CONNECT BY REGEXP_SUBSTR (idOndasRessuprimento, '[^,]+', 1, LEVEL) IS NOT NULL);


  END IF;

  /* DELETA AS RESERVAS DE SAIDA DA EXPEDIÇÃO */
  IF (deletaReservas = 'S') THEN
      SELECT RESERVAS INTO idReservaSaida
        FROM (SELECT LISTAGG(COD_RESERVA_ESTOQUE,',')
              WITHIN GROUP (ORDER BY COD_RESERVA_ESTOQUE) RESERVAS
                FROM RESERVA_ESTOQUE_EXPEDICAO WHERE COD_EXPEDICAO = idExpedicao);

      DELETE FROM RESERVA_ESTOQUE_EXPEDICAO WHERE COD_EXPEDICAO = idExpedicao;

      DELETE FROM RESERVA_ESTOQUE
      WHERE COD_RESERVA_ESTOQUE IN (SELECT REGEXP_SUBSTR(idReservaSaida, '[^,]+', 1, LEVEL) AS R FROM DUAL
                                    CONNECT BY REGEXP_SUBSTR (idReservaSaida, '[^,]+', 1, LEVEL) IS NOT NULL);
  END IF;

  /* RETORNA O STATUS DA EXPEDIÇÃO */
  IF (retornaStatusExpedicao = 'S') THEN
    UPDATE EXPEDICAO SET COD_STATUS = 462, DTH_FINALIZACAO = NULL WHERE COD_EXPEDICAO IN(idExpedicao);

    DELETE FROM ETIQUETA_CONFERENCIA WHERE COD_ETIQUETA_SEPARACAO IN (
    SELECT CODBARRAS FROM V_ETIQUETA_SEPARACAO WHERE EXPEDICAO IN (idExpedicao));

    DELETE FROM ETIQUETA_SEPARACAO WHERE COD_ETIQUETA_SEPARACAO IN(
    SELECT CODBARRAS FROM V_ETIQUETA_SEPARACAO WHERE EXPEDICAO IN (idExpedicao));

    DELETE FROM ETIQUETA_MAE_QUEBRA WHERE COD_ETIQUETA_MAE IN (
    SELECT COD_ETIQUETA_MAE FROM ETIQUETA_MAE WHERE COD_EXPEDICAO IN (idExpedicao));

    DELETE FROM EXPEDICAO_ANDAMENTO WHERE COD_EXPEDICAO IN (idExpedicao);

    DELETE FROM ETIQUETA_MAE WHERE COD_EXPEDICAO IN (idExpedicao);

    DELETE FROM MAPA_SEPARACAO_EMB_CLIENTE WHERE COD_MAPA_SEPARACAO IN (
    SELECT COD_MAPA_SEPARACAO FROM MAPA_SEPARACAO WHERE COD_EXPEDICAO IN (idExpedicao));

    DELETE FROM MAPA_SEPARACAO_CONFERENCIA WHERE COD_MAPA_SEPARACAO IN (
    SELECT COD_MAPA_SEPARACAO FROM MAPA_SEPARACAO WHERE COD_EXPEDICAO IN (idExpedicao));

    DELETE FROM MAPA_SEPARACAO_QUEBRA WHERE COD_MAPA_SEPARACAO IN (
    SELECT COD_MAPA_SEPARACAO FROM MAPA_SEPARACAO WHERE COD_EXPEDICAO IN (idExpedicao));

    DELETE FROM MAPA_SEPARACAO_PRODUTO WHERE COD_MAPA_SEPARACAO IN (
    SELECT COD_MAPA_SEPARACAO FROM MAPA_SEPARACAO WHERE COD_EXPEDICAO IN (idExpedicao));

    DELETE FROM MAPA_SEPARACAO_PEDIDO WHERE COD_MAPA_SEPARACAO IN (
    SELECT COD_MAPA_SEPARACAO FROM MAPA_SEPARACAO WHERE COD_EXPEDICAO IN (idExpedicao));

    DELETE FROM MAPA_SEPARACAO WHERE COD_EXPEDICAO IN (idExpedicao);

    DELETE FROM ORDEM_SERVICO WHERE COD_EXPEDICAO IN (idExpedicao);

    UPDATE PEDIDO SET CONFERIDO = 0, IND_ETIQUETA_MAPA_GERADO = 'N' WHERE COD_CARGA IN (
    SELECT COD_CARGA FROM CARGA WHERE COD_EXPEDICAO IN (idExpedicao));

    UPDATE PEDIDO_PRODUTO SET QTD_ATENDIDA = 0, QTD_CORTADA = 0 WHERE COD_PEDIDO IN (
    SELECT COD_PEDIDO FROM PEDIDO WHERE COD_CARGA IN (
    SELECT COD_CARGA FROM CARGA WHERE COD_EXPEDICAO IN (idExpedicao)));

    UPDATE VOLUME_PATRIMONIO  SET IND_OCUPADO = 'N' WHERE COD_VOLUME_PATRIMONIO IN (
    SELECT COD_VOLUME_PATRIMONIO FROM EXPEDICAO_VOLUME_PATRIMONIO WHERE COD_EXPEDICAO IN (idExpedicao));

    DELETE FROM EXPEDICAO_VOLUME_PATRIMONIO WHERE COD_EXPEDICAO IN (idExpedicao);

  END IF;

END;