SET TERM ^ ;

create or alter procedure PR_AGGREGATE_PARTS (
    AGENT_ID integer,
    SESSIONID integer)
as
declare variable DOC_ID integer;
declare variable SNAME DM_TEXT;
declare variable PRICE DM_DOUBLE;
declare variable SERT DM_TEXT;
declare variable PRICE_O DM_DOUBLE;
declare variable ALL_QUANT DM_DOUBLE;
declare variable PART_ID integer;
declare variable REALQUANT DM_DOUBLE;
declare variable ID integer;
declare variable POS_CNT integer;
declare variable VERB DM_TEXT1024;
declare variable RASHOD_MINUS DM_TEXT1024;
begin
doc_id = null;

  --Учет остатков по наименованию и цене ВКЛЮЧЕН
  if (not exists(select id from PARAMS p where p.param_id = 'AGGREGATE_PARTS' and param_value = '1')) then exit;

  --Включаем расход в минус, принудительно
  select param_value from params where param_id='RASHOD_MINUS' into :RASHOD_MINUS;
  update params set param_value = 1 where param_id='RASHOD_MINUS';


  --выборка всех позиций, которые будем корректировать
  for select sname, price, coalesce(sert,'') as sert, count(1) from VW_WAREBASE
        where realquant <> 0
--        and sname = 'Ситец однотонный ш0,8 м 100% хл БОРДО'
      group by sname, price, sert
      having count(part_id)>1
      into :sname, :price, :sert, :pos_cnt do
  begin
    all_quant = 0;
    --определяем партии, которые будем корректировать
    for select part_id, realquant from VW_WAREBASE where sname=:sname and
                                         coalesce(sert,'')=:sert and
                                        abs(price-:price) < 0.01 and
                                        realquant <> 0
                    order by part_id-- desc
                    into :part_id, :realquant do
    begin
      if (doc_id is null) then select doc_id from PR_NEWDOC(23,:AGENT_ID,0,'',null,:SessionID) into :doc_id;
      pos_cnt = pos_cnt - 1;
      if (pos_cnt <> 0) then
        begin
          all_quant = coalesce(all_quant,0) + realquant;
          select id from PR_RASHODPART(:doc_id,:part_id,:realquant,0,'',1,0) into :id;
        end
      else
        select id from PR_RASHODPART(:doc_id,:part_id,-:all_quant,0,'',1,0) into :id;
    end


  end

  update doc_detail_active set summa=price*quant where doc_id=:doc_id;
  if (doc_id is not null) then execute procedure PR_DOC_COMMIT(:doc_id,:SessionID);

  --Возвращаем как было
  update params set param_value = :RASHOD_MINUS where param_id='RASHOD_MINUS';

  delete from warebase where REALQUANT  = 0  AND  QUANT  = 0;

--  select VERB from PR_CLEANWAREBASE(0) into :VERB;
end^

SET TERM ; ^

/* Следующие операторы GRANT сгенерированы автоматически */

GRANT SELECT,UPDATE ON PARAMS TO PROCEDURE PR_AGGREGATE_PARTS;
GRANT SELECT ON VW_WAREBASE TO PROCEDURE PR_AGGREGATE_PARTS;
GRANT EXECUTE ON PROCEDURE PR_NEWDOC TO PROCEDURE PR_AGGREGATE_PARTS;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_AGGREGATE_PARTS;
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_AGGREGATE_PARTS;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_AGGREGATE_PARTS;
GRANT SELECT,DELETE ON WAREBASE TO PROCEDURE PR_AGGREGATE_PARTS;

/* Существующие привилегии на эту процедуру */

GRANT EXECUTE ON PROCEDURE PR_AGGREGATE_PARTS TO SYSDBA;