SET TERM ^ ;

create or alter procedure PR_REPAIR_REPRICE_MDLP
as
declare variable PART_ID integer;
declare variable CHILD_PART_ID integer;
declare variable DOC_ID integer;
begin
  --процедура нужна для исправлений всех кодов маркировки, после некорректных переоценок
  --(если была ошибка в процедуре PR_DOC_PRIHOD_COMMIT_RECURSE)

 --шаг 1: удаляем все ошибочные записи, которых нет в детализации документов (был косяк в чеках)
   delete from mark_detail m where quant < 0 and part_id <> 0 and
        not exists(select dd.id from doc_detail dd where dd.doc_id = m.doc_id and dd.part_id=m.part_id);

  --шаг 2 определяем все ошибочные позиции и создаем корректировочную запись к ним
  INSERT INTO MARK_DETAIL (DOC_ID, PART_ID, MARK_DATA, QUANT, COMMITDATE, INSERTDT, DOCNUM, DOC_DATE, DDA_ID, PLACE_ID_IN,
         PLACE_ID_OUT, GTIN, SERIES_NUMBER, ACTIVEPOS, SERIA, KIZ, EXPIRATION_DATE, SSCC, ACCEPTED, STATUS, AGREGATION,
         COST, VATVALUE, MDLP_STATUS, ERRORTEXT, CUR_MD)
  select DOC_ID, PART_ID, MARK_DATA, abs(QUANT), COMMITDATE, '2000-01-01 00:00:00', DOCNUM, DOC_DATE, DDA_ID, PLACE_ID_IN,
         PLACE_ID_OUT, GTIN, SERIES_NUMBER, 1, SERIA, KIZ, EXPIRATION_DATE, SSCC, ACCEPTED, STATUS, AGREGATION,
         COST, VATVALUE, MDLP_STATUS, ERRORTEXT, CUR_MD
 from mark_detail m where quant < 0 and
    exists(select id from docs d where d.id= m.doc_id and d.status=1 and d.doc_type=8) and
    not exists(select id from mark_detail m2 where m2.doc_id = m.doc_id and m2.mark_data = m.mark_data and m2.quant > 0) and
    (select pr.motherpart_id from pr_getmotherpart(m.part_id) pr) not in (0,m.part_id);

  --шаг 3 возвращаем у проблемных записей родительскую партию, как должно быть
  update mark_detail m
   set part_id = (select pr.motherpart_id from pr_getmotherpart(m.part_id) pr)
  where quant < 0 and
    exists(select id from docs d where d.id= m.doc_id and d.status=1 and d.doc_type=8) and
    exists(select id from mark_detail m2 where m2.doc_id = m.doc_id and m2.mark_data = m.mark_data and m2.quant > 0 and INSERTDT = '2000-01-01 00:00:00') and
    (select pr.motherpart_id from pr_getmotherpart(m.part_id) pr) not in (0,m.part_id);

 --шаг 4: если есть партии в переоценках, которые не записались в mark_detail, заменяем их на дочерние, которые есьт на остатках
  for select d.part_id from
                (select part_id, sum(quant) as quant from mark_detail m where part_id > 0
                    group by part_id
                 having sum(quant) > 0) d
        where (not exists(select w.part_id from warebase w where w.part_id = d.part_id and quant > 0.01))
--        and part_id = 2695
        into :part_id do
  begin
     select id, doc_id from parts p where p.motherpart_id = :part_id into :CHILD_PART_ID, :doc_id;
     if (CHILD_PART_ID is not null) then
         if (exists(select part_id from warebase w where w.part_id = :CHILD_PART_ID)) then
           update mark_detail m set part_id = :CHILD_PART_ID, doc_id = :doc_id, INSERTDT = '2000-01-01 00:00:00' where part_id = :part_id;
  end

  suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT,INSERT,DELETE,UPDATE ON MARK_DETAIL TO PROCEDURE PR_REPAIR_REPRICE_MDLP;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_REPAIR_REPRICE_MDLP;
GRANT SELECT ON DOCS TO PROCEDURE PR_REPAIR_REPRICE_MDLP;
GRANT EXECUTE ON PROCEDURE PR_GETMOTHERPART TO PROCEDURE PR_REPAIR_REPRICE_MDLP;
GRANT SELECT ON WAREBASE TO PROCEDURE PR_REPAIR_REPRICE_MDLP;
GRANT SELECT ON PARTS TO PROCEDURE PR_REPAIR_REPRICE_MDLP;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_REPAIR_REPRICE_MDLP TO SYSDBA;