SET TERM ^ ;

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

  --шаг 1 определяем все ошибочные позиции и создаем корректировочную запись к ним
  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, EXPIRETION_DATE, 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, EXPIRETION_DATE, 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);

  --шаг 2 возвращаем у проблемных записей родительскую партию, как должно быть
  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);


  suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT,INSERT,UPDATE ON MARK_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;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_REPAIR_REPRICE_MDLP TO SYSDBA;