remove_vendas_por_caixa_unico_dia_2.sql

DO language plpgsql $$
DECLARE
        L BIGINT;
        C VARCHAR(3);
        D DATE;
begin
        L := 1;
        C := '004';
        D := '2017-09-17';

        raise notice '% - Processo iniciado.', timeofday()::timestamp;

        INSERT INTO FILA_SINCRONIZACAO_PANAMA (ID, IDENTIFICADOR, PRIORIDADE, ENTIDADE_ID, OPERACAO)
            (SELECT NEXTVAL('SQ_FILA_SINCRONIZACAO_PANAMA'), 'VENDA', 2, VENDA.id, 'EXCLUSAO'
            FROM (SELECT T.TRNSEQ::TEXT||'-'||T.CXANUM::TEXT||'-'||T.LOJCOD::TEXT||'-'||TO_CHAR(T.TRNDAT, 'yyyy-MM-dd') AS "id"
                FROM TRANSACAO T
                WHERE T.TRNTIP IN ('1','7') AND T.LOJCOD = L AND T.CXANUM = C AND T.TRNDAT = D )
            VENDA);

        DELETE FROM REDUCAO WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM ESTATISTICA_PRODUTO_VENDA WHERE EPV_LOJCOD = L AND EPV_CXANUM =  C AND EPV_DATA_VENDA =  D;
        
        DELETE FROM ESTOQUE_MOVIMENTACAO WHERE MOV_ID IN (
                SELECT
                        MOV_ID
                FROM
                        ESTOQUE_MOVIMENTACAO
                WHERE
                        LOJ_CODIGO = L AND
                        MOV_DATA = D AND
                        ((MOV_TIPO='VENDA' AND SUBSTRING(MOV_HISTORICO, 14, 3) = C) OR
                        (MOV_TIPO='CANCELAMENTO' AND SUBSTRING(MOV_HISTORICO, 27, 3) = C))
        );
  
        DELETE FROM ITEM_VENDA WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D ;
        DELETE FROM FINALIZACAO WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM TRANSACAO WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM TRANSACAOITEMTEFDLL WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM TRANSACAOTEFDLL WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM ITEM_RECEBIMENTO WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM ITEM_PAGAMENTO WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM ITEM_PLANO_PAGAMENTO WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM PREVENDA WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM OCORRENCIA_PDV WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM SANGRIA_CONCILIACAO WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;
        DELETE FROM TRANSACAO_XMLNOTA WHERE LOJCOD = L AND CXANUM =  C AND TRNDAT =  D;

        raise notice '% - Processo concluido.', timeofday()::timestamp;
end
$$;