SET TERM ^ ;

create or alter procedure PR_EDITCUSTOMDOC (
    DOC_ID type of DM_ID,
    SESSION_ID type of DM_ID,
    RGUID type of DM_RGUID)
returns (
    CUR_DOC_ID type of DM_ID)
as
declare variable VNUM type of DM_ID_NULL;
declare variable DOC_TYPE type of DM_ID;
declare variable DOC_BASE_TYPE type of DM_ID;
declare variable DOC_STATUS type of DM_STATUS;
declare variable DOC_AGENT_ID type of DM_ID;
declare variable DOCNUM type of DM_TEXT;
declare variable DOC_CAPTION type of DM_TEXT;
declare variable BASE_AGENT_ID integer;
declare variable DOCDATE type of DM_DATETIME;
declare variable EGAIS_NUM DM_TEXT;
declare variable EGAIS_ID DM_TEXT;
declare variable EGAIS_UNITTYPE DM_TEXT;
declare variable EGAIS_STATUS DM_STATUS;
declare variable AGENTS_CONTRACT_ID DM_ID;
declare variable PART_ID DM_ID;
declare variable QUANT DM_DOUBLE;
declare variable SUMMA DM_DOUBLE;
declare variable SUM_DSC DM_DOUBLE;
declare variable DISCOUNT DM_DOUBLE;
declare variable SUM_NDSO DM_DOUBLE;
declare variable ACCEPTANCE DM_TEXT;
declare variable AGENT_FIRST DM_TEXT1024;
declare variable DATE_FIRST_PRIHOD DM_DATE;
declare variable MARKING DM_ID;
declare variable DATEPROD DM_DATE;
declare variable GTIN_SUBJ DM_TEXT;
declare variable ACCEPTTYPE DM_ID;
declare variable CUR_DDA_ID DM_ID;
declare variable DCARD DM_TEXT;
declare variable SUMMA_O DM_DOUBLE;
declare variable NAC DM_DOUBLE;
declare variable COMMENTS DM_BLOBTEXT;
declare variable AGENT_PLACE DM_TEXT;
declare variable OWNER_ID DM_ID;
declare variable DOWNER DM_ID;
declare variable DCREATER DM_ID;
begin
  select d.doc_type, d.status, dt.base_type, dt.caption, d.docnum, d.docdate, d.agent_id, d.vnum, d.base_agent_id, EGAIS_NUM, EGAIS_ID, EGAIS_UNITTYPE, EGAIS_STATUS,
  d.AGENTS_CONTRACT_ID, d.COMMENTS, d.agent_place,d.owner,d.creater from docs d left join doc_types dt on d.doc_type=dt.id where d.id=:doc_id
    into :doc_type, :doc_status, :doc_base_type, :doc_caption, :docnum, :docdate, :doc_AGENT_ID,:vnum, :BASE_AGENT_ID, :EGAIS_NUM, :EGAIS_ID,
    :EGAIS_UNITTYPE, :EGAIS_STATUS, :AGENTS_CONTRACT_ID, :COMMENTS, :agent_place,:downer,:dcreater;

  if (doc_type is null) then exception EX_CANTFINDDOC_ID;
  if (doc_status in (0,-2,2)) then exception EX_WRONGDOCSTATUS;
  if (doc_base_type=3) then exception EX_WRONGDOC_BASE_TYPE;

  /*add AVO 17/09/2020*/
  if (DOC_STATUS = 1) then --если документ проведен
   begin
    --проверям собственника документа, если не он сообщаем и выходим
    select USER_ID from sessions where id=:SESSION_ID into :owner_id;
    if ((:owner_id<>:downer) or (:owner_id<>:dcreater))  then
     begin
      if (not exists (select gd.id from group_detail gd where gd.grouptable='USERS' and gd.grouptable_id=:owner_id and gd.group_id=-12)) then
       exception ex_wrongdocstatus 'Документ создан другим пользователем '||(select username from users where id=:dcreater)||'. Удаление или редактирование возможно только под ним!';
     end
   end
/*end add*/

  cur_doc_id=gen_id(gen_docs_id,1);
  insert into docs (ID,PARENT_ID,DOC_TYPE,STATUS,AGENT_ID,RGUID,AUDIT_ID,DOCNUM,DOCDATE,DELETED_DOC_ID, BASE_AGENT_ID, EGAIS_NUM, EGAIS_ID, EGAIS_UNITTYPE, EGAIS_STATUS,AGENTS_CONTRACT_ID, COMMENTS)
    values (:cur_doc_id,0,:DOC_TYPE,0,:doc_AGENT_ID,:RGUID,:session_ID,:DOCNUM,:DOCDATE,:DOC_ID, :BASE_AGENT_ID, :EGAIS_NUM, :EGAIS_ID, :EGAIS_UNITTYPE, :EGAIS_STATUS,:AGENTS_CONTRACT_ID, :COMMENTS);
  -- Обновление номера документа на старое значение
  UPDATE docs set vnum = :vnum, caption=:doc_caption || ' №' || :vnum, agent_place=:agent_place  where id = :cur_doc_id;

  if (doc_base_type=1) then --приход
  begin
   if (doc_status=1) then --
   begin
/*     insert into DOC_DETAIL_ACTIVE (doc_id,part_id,quant,summa,discount,koef,sum_dsc,sum_ndso)
       select :cur_doc_id,part_id,quant,summa,discount,0,sum_dsc,sum_ndso from doc_detail where doc_id=:doc_id;*/
      for
          select PART_ID, QUANT, SUMMA, DISCOUNT, SUM_DSC, SUM_NDSO, ACCEPTANCE, AGENT_FIRST, DATE_FIRST_PRIHOD,
                 MARKING, DATEPROD, GTIN_SUBJ, ACCEPTTYPE
          from DOC_DETAIL
          where DOC_ID = :DOC_ID
          into :PART_ID, :QUANT, :SUMMA, :DISCOUNT, :SUM_DSC, :SUM_NDSO, :ACCEPTANCE, :AGENT_FIRST, :DATE_FIRST_PRIHOD,
               :MARKING, :DATEPROD, :GTIN_SUBJ, :ACCEPTTYPE
      do
      begin
        CUR_DDA_ID = gen_id(GEN_DOC_DETAIL_ACTIVE_ID, 1);
        insert into DOC_DETAIL_ACTIVE (ID, DOC_ID, PART_ID, QUANT, SUMMA, DISCOUNT, KOEF, SUM_DSC, SUM_NDSO, ACCEPTANCE,
                                       AGENT_FIRST, DATE_FIRST_PRIHOD, MARKING, DATEPROD, GTIN_SUBJ, ACCEPTTYPE)
        values (:CUR_DDA_ID, :CUR_DOC_ID, :PART_ID, :QUANT, :SUMMA, :DISCOUNT, 0, :SUM_DSC, :SUM_NDSO, :ACCEPTANCE,
                :AGENT_FIRST, :DATE_FIRST_PRIHOD, :MARKING, :DATEPROD, :GTIN_SUBJ, :ACCEPTTYPE);
        update MARK_DETAIL M
        set M.DOC_ID = :CUR_DOC_ID,
            M.DDA_ID = :CUR_DDA_ID
        where M.DOC_ID = :DOC_ID and
              M.PART_ID = :PART_ID;
        /*add AVO 07/09/2020 Группу маркированного товара переводим на DOC_DETAIL_ACTIVE*/
        if (:MARKING=1) then
        update GROUP_DETAIL gd set gd.grouptable_id=:CUR_DDA_ID, gd.grouptable='DOC_DETAIL_ACTIVE' where gd.grouptable='PARTS' and gd.group_id=-43 and gd.group_id=:PART_ID;
      end
      update DOC_DETAIL_ACTIVE dda set dda.part_id=iif((select count(*) from doc_detail where part_id = dda.part_id) > 1,dda.part_id,0) where dda.doc_id=:cur_doc_id;
   end
   else
   begin
     if (doc_status=-1) then
     begin
       insert into DOC_DETAIL_ACTIVE (doc_id,part_id,quant,summa,discount,koef,sum_dsc)
         select :cur_doc_id,part_id,quant,summa,discount,0,sum_dsc from doc_detail_deleted where doc_id=:doc_id;

       update DOC_DETAIL_ACTIVE dda set dda.part_id=iif((select count(*) from doc_detail where part_id = dda.part_id) > 1,dda.part_id,0) where dda.doc_id=:cur_doc_id;
     end 
   end
  end
  else if ((doc_base_type=2) or (doc_base_type=7)) then  --расход
  begin
    if (doc_status=1) then
    begin
/*      insert into doc_detail_active (doc_id,part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,koef,sum_dsc)
        select :cur_doc_id,part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,1,sum_dsc from doc_detail where doc_id=:doc_id;*/

      for
          select PART_ID, QUANT, DCARD, SUMMA, SUMMA_O, NAC, DISCOUNT, SUM_NDSO, SUM_DSC, ACCEPTANCE, AGENT_FIRST, DATE_FIRST_PRIHOD,
                 MARKING, DATEPROD, GTIN_SUBJ, ACCEPTTYPE
          from DOC_DETAIL
          where DOC_ID = :DOC_ID
          into :PART_ID, :QUANT, :DCARD, :SUMMA, :SUMMA_O, :NAC, :DISCOUNT, :SUM_NDSO, :SUM_DSC, :ACCEPTANCE, :AGENT_FIRST, :DATE_FIRST_PRIHOD,
               :MARKING, :DATEPROD, :GTIN_SUBJ, :ACCEPTTYPE
      do
      begin
        CUR_DDA_ID = gen_id(GEN_DOC_DETAIL_ACTIVE_ID, 1);
        insert into DOC_DETAIL_ACTIVE (ID, DOC_ID, PART_ID, QUANT, DCARD, SUMMA, SUMMA_O, NAC, DISCOUNT, SUM_NDSO, KOEF, SUM_DSC, ACCEPTANCE,
                                       AGENT_FIRST, DATE_FIRST_PRIHOD, MARKING, DATEPROD, GTIN_SUBJ, ACCEPTTYPE)
        values (:CUR_DDA_ID, :CUR_DOC_ID, :PART_ID, :QUANT, :DCARD, :SUMMA, :SUMMA_O, :NAC, :DISCOUNT, :SUM_NDSO, 1, :SUM_DSC, :ACCEPTANCE,
                :AGENT_FIRST, :DATE_FIRST_PRIHOD, :MARKING, :DATEPROD, :GTIN_SUBJ, :ACCEPTTYPE);
        update MARK_DETAIL M
        set M.DOC_ID = :CUR_DOC_ID,
            M.DDA_ID = :CUR_DDA_ID
        where M.DOC_ID = :DOC_ID and
              M.PART_ID = :PART_ID;
      end
    end
    else
    begin
      if (doc_status=-1) then
      begin
        insert into doc_detail_active (doc_id,part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,koef,sum_dsc)
          select :cur_doc_id,part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,1,sum_dsc from doc_detail_deleted where doc_id=:doc_id;
      end
    end 
  end

  suspend;
end^

SET TERM ; ^

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

GRANT SELECT,INSERT,UPDATE ON DOCS TO PROCEDURE PR_EDITCUSTOMDOC;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_EDITCUSTOMDOC;
GRANT SELECT ON SESSIONS TO PROCEDURE PR_EDITCUSTOMDOC;
GRANT SELECT,UPDATE ON GROUP_DETAIL TO PROCEDURE PR_EDITCUSTOMDOC;
GRANT SELECT ON USERS TO PROCEDURE PR_EDITCUSTOMDOC;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_EDITCUSTOMDOC;
GRANT SELECT,INSERT,UPDATE ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_EDITCUSTOMDOC;
GRANT SELECT,UPDATE ON MARK_DETAIL TO PROCEDURE PR_EDITCUSTOMDOC;
GRANT SELECT ON DOC_DETAIL_DELETED TO PROCEDURE PR_EDITCUSTOMDOC;

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

GRANT EXECUTE ON PROCEDURE PR_EDITCUSTOMDOC TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_EDITCUSTOMDOC TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_DOC_DETAIL_INSERT (
    PARENT_ID bigint,
    DOC_ID bigint,
    PART_ID bigint,
    QUANT type of DM_DOUBLE,
    DISCOUNT double precision,
    SUMMA double precision,
    SUMMA_O double precision,
    PRICE numeric(15,4),
    SUM_NDSO numeric(15,4),
    SUM_NDSR DM_DOUBLE,
    NAC numeric(15,4),
    COMMITDATE type of DM_DATE,
    HUMAN_QUANT type of DM_TEXT,
    SUM_DSC type of DM_DOUBLE,
    DCARD type of DM_TEXT,
    ACTIVE_ID type of DM_ID,
    PART_TYPE DM_STATUS,
    MAKE_ID DM_ID_NULL = null,
    ACCEPTANCE DM_TEXT = null,
    AGENT_FIRST DM_TEXT = null,
    DATE_FIRST_PRIHOD DM_DATETIME = null,
    MARKING DM_ID_NULL = null,
    DATEPROD DM_DATE = null,
    GTIN_SUBJ DM_TEXT = null,
    ACCEPTTYPE DM_ID = null)
as
declare variable NEW_ID type of DM_ID;
begin
  new_id=gen_id(gen_doc_detail_id,1);
  insert into doc_detail
    (id,PARENT_ID,DOC_ID,PART_ID,QUANT,DISCOUNT,SUMMA,summa_o, sum_ndso,SUM_NDSR, price, nac, doc_commitdate, human_quant,SUM_DSC,dcard, part_type,make_id,
    acceptance,agent_first,date_first_prihod
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,MARKING,DATEPROD,GTIN_SUBJ,ACCEPTTYPE
/* end 26/06/2020 AVO*/
    )
    /* (PARENT_ID,DOC_ID,PART_ID,QUANT,DISCOUNT,SUMMA,summa_o) */
   values
    /* (:PARENT_ID,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o); */
    (:new_id,:PARENT_ID,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o, :sum_ndso,:SUM_NDSR, :price, :nac, :commitdate, :human_quant,:SUM_DSC,:dcard, :part_type,:make_id,
    :acceptance,:agent_first,:DATE_FIRST_PRIHOD
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,:MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/* end 26/06/2020 AVO*/
    );
   execute procedure PR_DOC_DETAIL_ACTIVE_ONCOMMIT(:active_id,:new_id,:doc_id,:commitdate);
end^

SET TERM ; ^

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

GRANT INSERT ON DOC_DETAIL TO PROCEDURE PR_DOC_DETAIL_INSERT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_ACTIVE_ONCOMMIT TO PROCEDURE PR_DOC_DETAIL_INSERT;

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

GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_WARELIST_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_NEW_PART (
    DOC_ID type of DM_ID,
    PARENT_ID type of DM_ID,
    WARE_ID type of DM_UUID_NULL,
    PRICE type of DM_DOUBLE,
    PRICE_O type of DM_DOUBLE,
    PRICE_Z type of DM_DOUBLE,
    PRICE_R type of DM_DOUBLE,
    QUANT type of DM_DOUBLE,
    BARCODE type of DM_TEXT,
    BARCODE1 type of DM_TEXT,
    DEP type of DM_ID,
    GODENDO type of DM_DATETIME,
    SERIA type of DM_TEXT,
    NDS type of DM_DOUBLE,
    SUM_NDSO type of DM_DOUBLE,
    SERT type of DM_TEXT,
    DATESERT type of DM_DATETIME,
    KEMVSERT type of DM_TEXT,
    SDSERT type of DM_DATETIME,
    REGN type of DM_TEXT,
    NGTD type of DM_TEXT,
    EDIZM type of DM_TEXT,
    NAC type of DM_DOUBLE,
    MOTHERPART_ID type of DM_ID,
    PART_TYPE DM_STATUS,
    BASE_AGENT_ID DM_ID,
    SKLAD_ID type of DM_TEXT,
    CONTRACT_ID DM_ID,
    DOC_DETAIL_ACTIVE_ID DM_ID,
    GROUP_ID DM_ID,
    DATE_FIRST_PRIHOD DM_DATETIME = null,
    AGENT_FIRST DM_TEXT = null)
returns (
    P_ID type of DM_ID)
as
begin
  p_id=gen_id(gen_parts_id,1);
  insert into parts
    (ID,parent_id,doc_id,WARE_ID,PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE,BARCODE1,DEP,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,
    EDIZM,NAC,motherpart_id,sklad_id,part_type,BASE_AGENT_ID,contract_id, doc_detail_active_id, group_id,date_first_prihod,agent_first)
  values
    (:p_id,:parent_id,:doc_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,
    :SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id,:sklad_id,:part_type,:BASE_AGENT_ID,:contract_id,:doc_detail_active_id, :group_id,:date_first_prihod,:agent_first);
  suspend;
end^

SET TERM ; ^

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

GRANT INSERT ON PARTS TO PROCEDURE PR_NEW_PART;

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

GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA;

 SET TERM ^ ;

create or alter procedure PR_UPDATEMARKBYPART (
    DOC_ID DM_ID,
    PART_ID DM_ID,
    ACTIVE_ID DM_ID)
as
begin
--Если не переоценка то
--Обновляем партию привязанную к SGTIN
  if ((select D.DOC_TYPE
       from DOCS D
       where D.ID = :DOC_ID) <> 8) then
  begin
    update MARK_DETAIL
    set PART_ID = :PART_ID
    where DDA_ID = :ACTIVE_ID;
  end
  else
-- При переоценки вставляем записи в mark_detail
  begin
    insert into MARK_DETAIL (DOC_ID, PART_ID, MARK_DATA, QUANT, DOCNUM, DOC_DATE, DDA_ID, PLACE_ID_IN, PLACE_ID_OUT,
                             GTIN, SERIES_NUMBER, EXPIRATION_DATE, ACTIVEPOS, SERIA, KIZ, SSCC, ACCEPTED, MDLP_STATUS,
                             ERRORTEXT)
    select :DOC_ID, :PART_ID, MARK_DATA, -QUANT, DOCNUM, DOC_DATE, DDA_ID, PLACE_ID_IN, PLACE_ID_OUT, GTIN,
           SERIES_NUMBER, EXPIRATION_DATE, 1, SERIA, KIZ, SSCC, ACCEPTED, 1, ''
    from MARK_DETAIL MD
    where MD.DDA_ID = :ACTIVE_ID;
  end
  suspend;
end^

SET TERM ; ^

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

GRANT SELECT ON DOCS TO PROCEDURE PR_UPDATEMARKBYPART;
GRANT SELECT,INSERT,UPDATE ON MARK_DETAIL TO PROCEDURE PR_UPDATEMARKBYPART;

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

GRANT EXECUTE ON PROCEDURE PR_UPDATEMARKBYPART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_UPDATEMARKBYPART TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_DOC_PRIHOD_COMMIT_RECURSE (
    DOC_ID type of DM_ID,
    DOC_PARENT_ID type of DM_ID,
    PART_PARENT_ID type of DM_ID,
    DOC_COMMITDATE type of DM_DATE,
    CONTRACT_ID DM_ID)
as
declare variable PART_ID type of DM_ID;
declare variable WARE_ID type of DM_UUID_NULL;
declare variable SNAME type of DM_TEXT;
declare variable SIZG type of DM_TEXT;
declare variable SCOUNTRY type of DM_TEXT;
declare variable ORIG_CODE type of DM_TEXT;
declare variable SORIG_NAME type of DM_TEXT;
declare variable SORIG_IZG type of DM_TEXT;
declare variable SORIG_COUNTRY type of DM_TEXT;
declare variable BARCODE type of DM_TEXT;
declare variable Z_ID type of DM_ID;
declare variable SKLAD_ID DM_TEXT;
declare variable PRICE type of DM_DOUBLE;
declare variable PRICE_O type of DM_DOUBLE;
declare variable PRICE_Z type of DM_DOUBLE;
declare variable PRICE_R type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable BARCODE1 type of DM_TEXT;
declare variable DEP type of DM_ID;
declare variable KRITK type of DM_DOUBLE;
declare variable GODENDO type of DM_DATETIME;
declare variable SERIA type of DM_TEXT;
declare variable NDS type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable SERT type of DM_TEXT;
declare variable DATESERT type of DM_DATETIME;
declare variable KEMVSERT type of DM_TEXT;
declare variable SDSERT type of DM_DATETIME;
declare variable REGN type of DM_TEXT;
declare variable NGTD type of DM_TEXT;
declare variable EDIZM type of DM_TEXT;
declare variable PARENT_ID type of DM_ID;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable ACTIVE_ID type of DM_ID;
declare variable NAC type of DM_DOUBLE;
declare variable DOC_DETAIL_ID type of DM_ID;
declare variable MOTHERPART_ID type of DM_ID;
declare variable BCODE_IZG type of DM_TEXT;
declare variable HUMAN_QUANT type of DM_TEXT;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable GROUP_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
declare variable MNN DM_TEXT;
declare variable MOTHERPART_UUID DM_UUID_NULL;
declare variable EGAIS_ID DM_TEXT1024;
declare variable EGAIS_BREGID DM_TEXT1024;
declare variable EGAIS_REGID DM_TEXT1024;
declare variable EGAIS_ALCCODE DM_TEXT1024;
declare variable CAPACITY DM_DOUBLE;
declare variable ALC_VOLUME DM_DOUBLE;
declare variable EGAIS_PRODUCER_ID DM_TEXT1024;
declare variable EGAIS_QUANT DM_DOUBLE;
declare variable EGAIS_BARCODE DM_TEXT1024;
declare variable EGAIS_TYPE DM_TEXT;
declare variable PRODUCER_INN DM_TEXT1024;
declare variable PRODUCER_KPP DM_TEXT1024;
declare variable BOTTLINGDATE DM_DATETIME;
declare variable EGAIS_PRODUCT_VCODE DM_TEXT;
declare variable DATE_FIRST_PRIHOD DM_DATETIME = null;
declare variable AGENT_FIRST DM_TEXT = null;
declare variable ACCEPTANCE DM_TEXT = null;
declare variable DATEPROD DM_DATE;
declare variable MARKING DM_ID_NULL;
declare variable GTIN_SUBJ DM_TEXT;
declare variable ACCEPTTYPE DM_ID_NULL;
begin
    --whith SUM_NDSR
    for
    select
    /*ware_id*/  sname,sizg,scountry,orig_code,sorig_name,sorig_izg,sorig_country,barcode,z_id,sklad_id,
    /*part_id*/  PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE1,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SUM_NDSR,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,
    PARENT_ID,DISCOUNT,SUMMA,summa_o, id, part_id, nac, doc_detail_id, motherpart_id, dep, bcode_izg, human_quant, SUM_DSC, dcard,
    part_type, BASE_AGENT_ID, group_id, make_id, mnn, motherpart_uuid,
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_QUANT, EGAIS_BARCODE,
    EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE,acceptance,agent_first,DATE_FIRST_PRIHOD
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,MARKING,DATEPROD,GTIN_SUBJ,ACCEPTTYPE
/* end 26/06/2020 AVO*/
    from doc_detail_active where doc_id=:doc_id and part_parent_id=:doc_parent_id
    into
    :sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:barcode,:z_id,:sklad_id,
    :PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE1,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SUM_NDSR,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,
    :PARENT_ID,:DISCOUNT,:SUMMA,:summa_o, :active_id, :part_id, :nac, :doc_detail_id, :motherpart_id, :dep, :bcode_izg, :human_quant, :SUM_DSC, :dcard,
    :part_type, :BASE_AGENT_ID, :group_id,:make_id, :mnn, :motherpart_uuid,
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_QUANT, :EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE,:acceptance,:agent_first,:DATE_FIRST_PRIHOD
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,:MARKING,:DATEPROD,GTIN_SUBJ,ACCEPTTYPE
/* end 26/06/2020 AVO*/
    do
    begin
    if ((price<0) or (price is null)) then exception EX_PRICE;
    if (/*(price_o<0.001) or */(price_o is null)) then exception EX_PRICE_O;
    if (/*(quant<0.00001) or */(quant is null)) then exception EX_QUANT;
    if (/*(summa<0.00001) or */(summa is null)) then exception EX_SUMMA;
    if (/*(summa_o<0.00001) or */(summa_o is null)) then exception EX_SUMMA_O;
    --    if ((NDS<0.00001) or (NDS is null)) then exception EX_NDS;
    if (/*(SUM_NDSO<0) or */(SUM_NDSO is null or SUM_NDSR is null )) then exception EX_SUM_NDSO;
    select w_id from pr_get_ware(:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:bcode_izg,:z_id,:sklad_id,:part_type,:mnn)
    into :ware_id;
    if ((part_id=0) or (part_id is NULL)) then
        select p_id from pr_new_part(:DOC_ID,:part_parent_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,:GODENDO,:SERIA,:NDS,
        :SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id, :part_type,:BASE_AGENT_ID, :sklad_id, :contract_id, :active_id,
        :group_id,:date_first_prihod,:agent_first) into :part_id;

    execute procedure PR_DOC_DETAIL_INSERT(:doc_detail_id,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o,:price,:sum_ndso,:sum_ndsr,:nac,:doc_commitdate,:human_quant,:SUM_DSC,:dcard,:active_id,:part_type,:make_id,:acceptance,:agent_first,:DATE_FIRST_PRIHOD
/*26/06/2020 AVO Добавлены поля для маркировки*/
    ,:MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/* end 26/06/2020 AVO*/
    );
    -- обновляем членство в группах
    execute procedure PR_UPDGROUPDETAIL_ACTIVE2PARTS(:active_id,:part_id);


    --25/11/2020 Обновляем партию привязанную к SGTIN
    execute procedure PR_UPDATEMARKBYPART(:DOC_ID, :PART_ID, :ACTIVE_ID);
    --update  MARK_DETAIL   set part_id=:part_id
    --           where DDA_ID = :ACTIVE_ID;
    -- 24/03/2020 <--

    -- Обновляем атрибут для партии
    -- execute procedure PR_UPGRADE_ATTR(:active_id,:part_id);

    -- присваиваем ценам партии
    update prices set part_id=:part_id where dda_id=:active_id;
    update or insert into prices (part_id,price_type,currency_id, price,dda_id) values (:part_id,0,0,:price,:active_id) matching (PART_ID,PRICE_TYPE);
    execute procedure PR_DOC_PRIHOD_COMMIT_RECURSE(:doc_id,:active_id,:part_id,:doc_commitdate,:contract_id);
    end
    end^

SET TERM ; ^

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

GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_UPDGROUPDETAIL_ACTIVE2PARTS TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_UPDATEMARKBYPART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT SELECT,INSERT,UPDATE ON PRICES TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;

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

GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO PROCEDURE PR_DOC_PRIHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_DOC_RASHOD_COMMIT (
    DOC_ID type of DM_ID,
    DBG type of DM_STATUS)
as
declare variable PARENT_ID type of DM_ID;
declare variable PART_ID type of DM_ID;
declare variable PRICE type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable NAC type of DM_DOUBLE;
declare variable DOC_COMMITDATE type of DM_DATE;
declare variable HUMAN_QUANT type of DM_TEXT;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable ACTIVE_ID type of DM_ID;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
declare variable ACCEPTTYPE DM_ID_NULL;
declare variable MARKING DM_ID_NULL;
declare variable DATEPROD DM_DATE;
declare variable GTIN_SUBJ DM_TEXT;
begin
  select cast(commitdate as date) from docs where id=:doc_id into :doc_commitdate;
  for select PARENT_ID,DOC_ID,PART_ID,PRICE,QUANT,DISCOUNT,SUMMA,summa_o, sum_ndso,SUM_NDSR, nac, human_quant, SUM_DSC, dcard, part_type, id, make_id
/*26/06/2020 AVO Добавлены поля для маркировки*/
   ,MARKING,DATEPROD,GTIN_SUBJ,ACCEPTTYPE
/* end 26/06/2020 AVO*/
  from doc_detail_active where doc_id=:doc_id
  into :PARENT_ID,:DOC_ID,:PART_ID,:PRICE,:QUANT,:DISCOUNT,:SUMMA,:summa_o, :sum_ndso,:SUM_NDSR, :nac, :human_quant, :SUM_DSC, :dcard, :part_type, :active_id, :make_id
/*26/06/2020 AVO Добавлены поля для маркировки*/
  ,:MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/* end 26/06/2020 AVO*/
  do
   execute procedure PR_DOC_DETAIL_INSERT(:PARENT_ID,:DOC_ID,:PART_ID,QUANT,:DISCOUNT,:SUMMA,:summa_o, :price,:sum_ndso,:SUM_NDSR, :nac, :doc_commitdate, :human_quant, :SUM_DSC,:dcard,:active_id,:part_type,:make_id
   /*26/06/2020 AVO Добавлены поля для маркировки*/
   ,null, null, null, :MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/* end 26/06/2020 AVO*/
    );
  --execute procedure GM$PR_PRIHOD_DOC_TREB(:doc_id);
end^

SET TERM ; ^

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

GRANT SELECT ON DOCS TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_RASHOD_COMMIT;

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

GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_DOC_WARELIST_COMMIT (
    DOC_ID type of DM_ID,
    DOC_PARENT_ID type of DM_ID,
    PART_PARENT_ID type of DM_ID,
    DOC_COMMITDATE type of DM_DATE,
    CONTRACT_ID DM_ID)
as
declare variable PART_ID type of DM_ID;
declare variable WARE_ID type of DM_UUID_NULL;
declare variable SNAME type of DM_TEXT;
declare variable SIZG type of DM_TEXT;
declare variable SCOUNTRY type of DM_TEXT;
declare variable ORIG_CODE type of DM_TEXT;
declare variable SORIG_NAME type of DM_TEXT;
declare variable SORIG_IZG type of DM_TEXT;
declare variable SORIG_COUNTRY type of DM_TEXT;
declare variable BARCODE type of DM_TEXT;
declare variable Z_ID type of DM_ID;
declare variable SKLAD_ID DM_TEXT;
declare variable PRICE type of DM_DOUBLE;
declare variable PRICE_O type of DM_DOUBLE;
declare variable PRICE_Z type of DM_DOUBLE;
declare variable PRICE_R type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable BARCODE1 type of DM_TEXT;
declare variable DEP type of DM_ID;
declare variable KRITK type of DM_DOUBLE;
declare variable GODENDO type of DM_DATETIME;
declare variable SERIA type of DM_TEXT;
declare variable NDS type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable SERT type of DM_TEXT;
declare variable DATESERT type of DM_DATETIME;
declare variable KEMVSERT type of DM_TEXT;
declare variable SDSERT type of DM_DATETIME;
declare variable REGN type of DM_TEXT;
declare variable NGTD type of DM_TEXT;
declare variable EDIZM type of DM_TEXT;
declare variable PARENT_ID type of DM_ID;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable ACTIVE_ID type of DM_ID;
declare variable NAC type of DM_DOUBLE;
declare variable DOC_DETAIL_ID type of DM_ID;
declare variable MOTHERPART_ID type of DM_ID;
declare variable BCODE_IZG type of DM_TEXT;
declare variable HUMAN_QUANT type of DM_TEXT;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable GROUP_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
declare variable ACCEPTTYPE DM_ID_NULL;
declare variable MARKING DM_ID_NULL;
declare variable DATEPROD DM_DATE;
declare variable GTIN_SUBJ DM_TEXT;
begin
  for
    select
      sname,sizg,scountry,orig_code,sorig_name,sorig_izg,sorig_country,barcode,z_id,sklad_id,
      PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE1,GODENDO,SERIA,NDS,SUM_NDSO,SUM_NDSR,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,
      PARENT_ID,DISCOUNT,SUMMA,summa_o, id, part_id, nac, doc_detail_id, motherpart_id, dep, bcode_izg, human_quant, SUM_DSC, dcard,
      part_type, BASE_AGENT_ID, group_id, make_id
      /*26/06/2020 AVO Добавлены поля для маркировки*/
      ,MARKING,DATEPROD,GTIN_SUBJ,ACCEPTTYPE
     /* end 26/06/2020 AVO*/
    from doc_detail_active where doc_id=:doc_id
    into
      :sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:barcode,:z_id,:sklad_id,
      :PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE1,:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SUM_NDSR,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,
      :PARENT_ID,:DISCOUNT,:SUMMA,:summa_o, :active_id, :part_id, :nac, :doc_detail_id, :motherpart_id, :dep, :bcode_izg, :human_quant, :SUM_DSC, :dcard,
      :part_type, :BASE_AGENT_ID, :group_id,:make_id
      /*26/06/2020 AVO Добавлены поля для маркировки*/
      ,:MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/*    end 26/06/2020 AVO*/
   do
  begin
    select w_id from pr_get_ware(:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:bcode_izg,:z_id,:sklad_id,:part_type) into :ware_id;
    execute procedure PR_DOC_DETAIL_INSERT(:doc_detail_id,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o,:price,:sum_ndso,:SUM_NDSR,:nac,:doc_commitdate,:human_quant,:SUM_DSC,:dcard,:active_id,:part_type,:make_id
    /*26/06/2020 AVO Добавлены поля для маркировки*/
   ,null, null, null, :MARKING,:DATEPROD,:GTIN_SUBJ,:ACCEPTTYPE
/* end 26/06/2020 AVO*/
    );
  end
end^

SET TERM ; ^

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

GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_WARELIST_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_WARELIST_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_WARELIST_COMMIT;

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

GRANT EXECUTE ON PROCEDURE PR_DOC_WARELIST_COMMIT TO SYSDBA;