22325358 DSUPCONTR-8588 DT - Processo de zera saldo do acordo considerando acordos reprovados e cancelados

-- ACORDOS COM VERBA
SELECT a.Nroacordo, a.Nroempresa, a.Seqfornecedor, a.Seqcomprador,
       a.Vlracordo,
       Fsaldoacordopromoc(a.Nroempresa, a.Nroacordo, a.Seqfornecedor) AS Vlrsaldo,
       'S' AS Indexisteverba, a.Nroempresa Nroempacordo, a.situacaoacordo, a.statusacordo
  FROM Msu_Acordopromoc a
 WHERE a.Apporigem = 2
   AND a.Dtafim + 1 <= Trunc(SYSDATE)
   and a.Statusacordo IN ('A')
   AND Fsaldoacordopromoc(a.Nroempresa, a.Nroacordo, a.Seqfornecedor) > 0
   AND EXISTS (SELECT 1
          FROM Mrl_Custoverba w
         WHERE w.Nroacordo = a.Nroacordo
           AND w.Nroempresaacordo = a.Nroempresa
           AND Nvl(w.Indtipacordo, 'R') = 'R'
           AND w.Statusverba = 'A')
   AND NOT EXISTS
 (SELECT 1
          FROM Mrl_Custoverba x
         WHERE x.Nroacordo = a.Nroacordo
           AND x.Nroempresaacordo = a.Nroempresa
           AND Nvl(x.Indtipacordo, 'R') = 'R'
           AND x.Statusverba = 'A'
           AND x.Dtafinal >= Trunc(SYSDATE)
           AND x.Qtdlimiteverba >
               Decode(Nvl(x.Qtdutilizadaverba, 0), 0, -1, x.Qtdutilizadaverba))
--
UNION ALL
--
-- ACORDOS SEM VERBA
SELECT b.Nroacordo, b.Nroempresa, b.Seqfornecedor, b.Seqcomprador,
       b.Vlracordo,
       Fsaldoacordopromoc(b.Nroempresa, b.Nroacordo, b.Seqfornecedor) AS Vlrsaldo,
       'N' AS Indexisteverba, b.Nroempresa Nroempacordo, b.situacaoacordo, b.statusacordo
  FROM Msu_Acordopromoc b
 WHERE b.Apporigem = 2
   AND b.Dtafim + 1 <= Trunc(SYSDATE)
   AND Fsaldoacordopromoc(b.Nroempresa, b.Nroacordo, b.Seqfornecedor) > 0
   AND NOT EXISTS (SELECT 1
          FROM Mrl_Custoverba y
         WHERE y.Nroacordo = b.Nroacordo
           AND y.Nroempresaacordo = b.Nroempresa
           AND Nvl(y.Indtipacordo, 'R') = 'R'
           AND y.Statusverba = 'A'); 

pkg_adm_acordoverba.SP_CONSUMOVERBA_ZERASALDO(pdData => trunc(sysdate));