Просмотр исходного текста страницы Исправление от 04/09/2020

<pre>
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;
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 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;

  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;

  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 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,UPDATE ON GROUP_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 GM$PR_PRIHOD_DOC_TREB (
    DOC_ID integer)
as
declare variable DOC_TYPE DM_STATUS;
declare variable AGENT_ID DM_ID_NULL;
declare variable G$PROFILE_ID DM_ID_NULL;
declare variable DOC_COMMITDATE DM_DATE;
declare variable DOC_UUID DM_ID_NULL;
declare variable TO_PROFILE_ID DM_ID_NULL;
declare variable FIRST_AGENT_ID DM_ID_NULL;
begin
  select doc_type, agent_id, cast(commitdate as dm_date)
  from docs where id=:doc_id into :doc_type, :agent_id, :doc_commitdate;
  if (doc_type not in (6,11)) then  exit;

  --select gp.id from G$PROFILES gp where gp.agent_id=:agent_id into :g$profile_id;
  select (select N from GET_INTEGER(email)) from agents where id=:agent_id into :to_profile_id;
  select (select N from GET_INTEGER(p.param_value)) from params p where p.param_id='CODE_PROFILE' into :g$profile_id;
  if (g$profile_id is null or to_profile_id is null) then exit;
  DOC_TYPE=40;

  --Сохраняем контрагента в переменной FIRST_AGENT_ID если есть необходимо расскомментировтаь
  select FIRST_AGENT_ID from docs where id=:doc_id into :FIRST_AGENT_ID;
  if (FIRST_AGENT_ID is null) then FIRST_AGENT_ID = (select id from agents where email=(select p.param_value from params p where p.param_id='CODE_PROFILE'));

  --27/05/2020 AVO Добавлено полуение ID  первоначального контрагента
  INSERT INTO DOCS_TREB (ID,PARENT_ID, DOC_TYPE, STATUS, AGENT_ID, DOCNUM, DOCDATE,
  RGUID, INSERTDT, POSTDT, AUDIT_ID, VNUM, VSHIFT, CREATER, OWNER, COMMITDATE,
  DEVICE_NUM, SUMMA, SUMMA_O, SUM_NDSO, CALCSUMMA, COMMENTS, SUMM1, SUMM2, SUMM3, SUMM4,
  CHECKDATA, COMMITSESSION_ID, SUM_DSC, CASHDATA, PRICE_TYPE, CURRENCY_ID, BASE_AGENT_ID,
  CONTRACT_ID, G$PROFILE_ID, LINK_ID, DELETED_DOC_ID,TO_PROFILE_ID,/*27/05/2020 add AVO*/FIRST_AGENT,
  /*04/09/2020 add AVO*/AGENT_PLACE)
  select ID,PARENT_ID,:doc_type, 1 as STATUS, AGENT_ID, DOCNUM, DOCDATE,
         RGUID, INSERTDT, POSTDT, AUDIT_ID, VNUM, VSHIFT, CREATER, OWNER, COMMITDATE,
         DEVICE_NUM, SUMMA, SUMMA_O, SUM_NDSO, CALCSUMMA, COMMENTS, SUMM1, SUMM2, SUMM3, SUMM4,
         CHECKDATA, COMMITSESSION_ID, SUM_DSC, CASHDATA, PRICE_TYPE, CURRENCY_ID, BASE_AGENT_ID,
         CONTRACT_ID, :g$profile_id as G$PROFILE_ID, LINK_ID, DELETED_DOC_ID,:to_profile_id as TO_PROFILE_ID,
         /*27/05/2020 add AVO*/:first_agent_id as first_agent_id,/*04/09/2020 add AVO*/AGENT_PLACE
  from docs where id=:doc_id
  returning id into :doc_uuid;

  INSERT INTO DOC_DETAIL_ACTIVE_TREB
  (ID,PARENT_ID, PART_ID, PRICE,PRICE_O, QUANT, DISCOUNT, SUMMA,
   SUMMA_O, DCARD, INSERTDT, SUM_NDSO, NAC,  HUMAN_QUANT, SUM_DSC,
   PART_TYPE, BASE_AGENT_ID, G$PROFILE_ID, TO_PROFILE_ID,
   STATUS, DOC_ID, SNAME, SUM_NDSR, ORIG_CODE, SKLAD_ID,
   BARCODE1, BCODE_IZG,BARCODE, PRICE_Z, PRICE_R, SERIA, NDS, SERT, DATESERT, KEMVSERT,SDSERT,
   GODENDO, REGN, NGTD, EDIZM, SIZG, SCOUNTRY,  DEP, GNVLS, SORIG_NAME,SORIG_IZG,SORIG_COUNTRY,MNN,
   WARE_ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,MOTHERPART_UUID,
   -- 23/07/2020 AVO Добавлены поля для передачи по маркировке
   MARKING,GTIN_SUBJ,ACCEPTTYPE,DATEPROD)
   select
   dd.ID,0, 0, p.PRICE,p.PRICE_O, -1*(dd.QUANT), dd.DISCOUNT, -1*(dd.SUMMA),
   -1*(dd.SUMMA_O),dd.DCARD, dd.INSERTDT, -1*(dd.SUM_NDSO), p.NAC, dd.HUMAN_QUANT,
   dd.SUM_DSC,p.PART_TYPE, 0, :g$profile_id, :to_profile_id,
   0, :doc_uuid, vn.svalue,  dd.SUM_NDSR,  w.ORIG_CODE,  p.SKLAD_ID,
   p.BARCODE1, w.barcode,p.BARCODE, p.PRICE_Z, p.PRICE_R, p.SERIA, p.NDS, p.SERT, p.DATESERT, p.KEMVSERT,
   p.SDSERT, p.GODENDO, p.REGN, p.NGTD, p.EDIZM, vi.svalue, vc.svalue,p.DEP,
   (select membership from PR_MEMBERSHIPS('PARTS=' || part_id || ';PARTS.NAME_ID=' || name_id ||';',ascii_char(13)||ascii_char(10),1)),
   '','','','',p.WARE_ID,w.NAME_ID,w.IZG_ID,w.COUNTRY_ID,
   w.ORIG_NAME_ID,w.ORIG_IZG_ID,w.ORIG_COUNTRY_ID,p.d$uuid,MARKING,GTIN_SUBJ,ACCEPTTYPE,DATEPROD
   from doc_detail dd
   left join parts p on p.id=dd.part_id
   left join wares w on w.id=p.ware_id
   left join vals vn on vn.id=w.name_id
   left join vals vi on vi.id=w.izg_id
   left join vals vc on vc.id=w.country_id
   where dd.doc_id=:doc_id;
end^

SET TERM ; ^

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

GRANT SELECT ON DOCS TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT EXECUTE ON PROCEDURE GET_INTEGER TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT SELECT ON AGENTS TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT SELECT ON PARAMS TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT INSERT ON DOCS_TREB TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT INSERT ON DOC_DETAIL_ACTIVE_TREB TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT EXECUTE ON PROCEDURE PR_MEMBERSHIPS TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT SELECT ON PARTS TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT SELECT ON WARES TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;
GRANT SELECT ON VALS TO PROCEDURE GM$PR_PRIHOD_DOC_TREB;

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

GRANT EXECUTE ON PROCEDURE GM$PR_PRIHOD_DOC_TREB TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE GM$PR_PRIHOD_DOC_TREB TO PROCEDURE PR_ENAKL_PRIHOD_DOC_TREB_RESEND;
GRANT EXECUTE ON PROCEDURE GM$PR_PRIHOD_DOC_TREB TO SYSDBA;


SET TERM ^ ;

CREATE OR ALTER trigger docs_treb_bi for docs_treb
active before insert position 0
as
declare variable new_doc_id dm_id;
declare variable doc_type dm_id;
declare variable tek_d$uuid dm_uuid;
declare variable agent_id dm_id_null;
begin
  if (new.to_profile_id=cast((select p.param_value from params p where p.param_id='CODE_PROFILE') as dm_id)) then
  begin
    new_doc_id=gen_id(gen_docs_id,1);
    if (new.doc_type=41) then
      doc_type=6;
    else if (new.doc_type=39) then
      doc_type=8;
    else if (new.doc_type=40) then
--25/06/2020 AVO если используется первоначальный контрагент, то делаем приход поставщика
      doc_type=1;--приход поставщика
    -- doc_type=2;--приход перемеением
--end 25/06/2020
    else doc_type=1;

    select id from agents where deletedt is null and email=cast(new.g$profile_id as dm_text) into :agent_id;

--27/05/2020 AVO Если документ расход перемещением, то нам нужно при создании нового документа вернуть первоначального контрагента
    if (new.doc_type=40) then
     if (new.first_agent is not null) then
      agent_id = new.first_agent;
--end 27/05/2020

    if (agent_id is null) then agent_id=-2;
    update or insert into docs (ID,PARENT_ID,DOC_TYPE,AGENT_ID,DOCDATE,RGUID,AUDIT_ID,COMMITDATE,STATUS,VNUM,DOCNUM,DOC_TREB_ID,AGENT_PLACE) values
     (:new_doc_id, 0,:doc_type,:agent_id,new.docdate,new.rguid,0,new.commitdate,iif(new.status=1,2,-1),new.vnum,new.docnum,new.id,new.agent_place) matching (RGUID);
--20170105 А.К. заявка 861654, 862594
    for select d$uuid from doc_detail_active_treb where doc_id=new.id  into :tek_d$uuid do
--       update doc_detail_active set doc_id=:new_doc_id where doc_detail_active_treb_d$uuid=:tek_d$uuid;
       update doc_detail_active set doc_id=:new_doc_id where doc_id = 0 and doc_detail_active_treb_d$uuid=:tek_d$uuid;
  end
end^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger doc_detail_active_treb_bi0 for doc_detail_active_treb
active before insert or update position 0
AS
declare variable doc_id dm_id;
declare variable dd_id dm_id;
declare variable real_q_warebase dm_double;
begin

--заявка 723238 По умолчанию дата вставки приходит из реального документа, а триггер DOC_DETAIL_ACTIVE_TREB_BI_DISTR удаляет неактуальные строки
   new.insertdt=current_timestamp;

-- AVO 26/02/2020  цену розничную вычисленную из суммы
   new.price = coalesce(abs(new.summa/new.quant),0);

--679291 А.К. 20160804
--  doc_id=coalesce((select d.id from docs d left join docs_treb dt on dt.rguid=d.rguid where dt.id=new.doc_id AND
  doc_id=coalesce((select first 1 d.id from docs_treb dt left join docs d on dt.rguid=d.rguid where dt.id=new.doc_id AND dt.g$profile_id=new.g$profile_id AND
      DT.to_profile_id=cast((select p.param_value from params p where p.param_id='CODE_PROFILE') as dm_id)),0);

  if ((new.to_profile_id=cast((select p.param_value from params p where p.param_id='CODE_PROFILE') as dm_id))
  and (select first 1 d.status from docs d where d.id=:doc_id) not in (1,-1)) then
  begin
    if ((select first 1 doc_type from docs_treb where id=new.doc_id and to_profile_id=cast((select p.param_value from params p where p.param_id='CODE_PROFILE') as dm_id))=40) then --прих перемещение
    begin
      delete from doc_detail_active where DOC_DETAIL_ACTIVE_TREB_D$UUID  = new.d$uuid; --Заявка 760688 А.К.
      update or insert into doc_detail_active (gnvls,DOC_ID,PART_ID,PART_PARENT_ID,PRICE,NAC,QUANT,DISCOUNT,SUMMA,SUMMA_O,WARE_ID,PRICE_O,PRICE_Z,PRICE_R,BARCODE,BARCODE1,GODENDO,SERIA,NDS,SUM_NDSO,SERT,
      DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,Z_ID,SKLAD_ID,SNAME,SIZG,SCOUNTRY,SORIG_NAME,SORIG_IZG,
      SORIG_COUNTRY,INSERTDT,INFO,KOEF,MOTHERPART_ID,DEP,BCODE_IZG,HUMAN_QUANT,SUM_DSC,CUSTOMDRAW,STATUS,PART_TYPE,BASE_AGENT_ID,GROUP_ID,DOC_DETAIL_ACTIVE_TREB_D$UUID,MOTHERPART_UUID
      /*04/09/2020 AVO Добавлены поля для маркировки*/
      ,MARKING,DATEPROD,GTIN_SUBJ,ACCEPTTYPE)
      values
      (new.gnvls,:doc_id,new.PART_ID,new.PART_PARENT_ID,new.PRICE,new.NAC,new.QUANT,new.DISCOUNT,new.SUMMA,
      new.SUMMA_O,new.WARE_ID,new.PRICE_O,new.PRICE_Z,new.PRICE_R,new.BARCODE,new.BARCODE1,new.GODENDO,new.SERIA,new.NDS,new.SUM_NDSO,new.SERT,
      new.DATESERT,new.KEMVSERT,new.SDSERT,new.REGN,new.NGTD,new.EDIZM,new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.ORIG_CODE,new.ORIG_NAME_ID,new.ORIG_IZG_ID,new.ORIG_COUNTRY_ID,
      new.Z_ID,new.SKLAD_ID,new.SNAME,new.SIZG,new.SCOUNTRY,new.SORIG_NAME,new.SORIG_IZG,  new.SORIG_COUNTRY,new.INSERTDT,new.INFO,new.KOEF,new.MOTHERPART_ID,
      new.DEP,new.BCODE_IZG,new.HUMAN_QUANT,new.SUM_DSC,new.CUSTOMDRAW,new.STATUS,new.PART_TYPE,new.BASE_AGENT_ID,new.GROUP_ID,new.d$uuid,new.motherpart_uuid
       /*04/09/2020 AVO Добавлены поля для маркировки*/
      ,new.marking,new.dateprod,new.gtin_subj,new.accepttype
       )
      matching (DOC_DETAIL_ACTIVE_TREB_D$UUID) ;
      if (new.parent_id <>0 ) then
        update doc_detail_active set parent_id=(select first 1 da.id from doc_detail_active da where da.doc_detail_active_treb_d$uuid=
        (select first 1 ddt.d$uuid from doc_detail_active_treb ddt where ddt.id=new.parent_id)) where DOC_DETAIL_ACTIVE_TREB_D$UUID=new.d$uuid;
      if ((select first 1 id from doc_detail_active_treb where parent_id=new.id)>0) then
        update doc_detail_active set parent_id=(select first 1 da.id from doc_detail_active da where da.doc_detail_active_treb_d$uuid=new.d$uuid)
        where id=(select first 1 da.id from doc_detail_active da where da.doc_detail_active_treb_d$uuid=
        (select first 1 ddt.d$uuid from doc_detail_active_treb ddt where ddt.parent_id=new.id)) ;
    end
    if ((select first 1 doc_type from docs_treb where id=new.doc_id and to_profile_id=cast((select p.param_value from params p where p.param_id='CODE_PROFILE') as dm_id))=39) then --требование переоценки
    begin
      --Заявка 760688 А.К.
      --select id from PR_CORRECTPART(:doc_id,new.part_id,abs(new.quant)) into :dd_id;
      --update doc_detail_active set price=new.price,DOC_DETAIL_ACTIVE_TREB_D$UUID=new.d$uuid,nac=new.nac where id=:dd_id;
      select first 1 realquant from warebase where part_id=new.part_id and price <> new.price into :real_q_warebase;
      if ((coalesce(real_q_warebase,0)) < 0.01) then exit;
      if (:real_q_warebase > abs(new.quant)) then real_q_warebase = new.quant;
      select id from PR_CORRECTPART(:doc_id,new.part_id,abs(:real_q_warebase)) into :dd_id;
      update doc_detail_active set price=new.price, summa = new.price*quant, DOC_DETAIL_ACTIVE_TREB_D$UUID=new.d$uuid,nac=new.nac where id=:dd_id;


    end
  end
end^

SET TERM ; ^

SET TERM ^ ;



CREATE OR ALTER TRIGGER SET_MARKING_GROUP FOR DOC_DETAIL_ACTIVE
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
begin
/*add AVO 04/09/2020
Для позиций проверяем, если товар маркированный, то ставим группу маркировки принудительно
*/
  if (new.marking=1) then
   begin
--Проверяем есть ли группа, если есть уже - выходим
    if ((select count(id) from GROUP_DETAIL where GROUP_ID =-43 and GROUPTABLE='DOC_DETAIL_ACTIVE' and GROUPTABLE_ID=new.id)>0) then
     exit;
    if ((not exists(select id from GROUP_DETAIL where GROUP_ID=-43 and GROUPTABLE_ID=new.id and GROUPTABLE='DOC_DETAIL_ACTIVE'))) then
     UPDATE OR INSERT INTO GROUP_DETAIL (GROUP_ID, PARENT_ID, GROUPTABLE_ID, GROUPTABLE) VALUES (-43, 0, new.id, 'DOC_DETAIL_ACTIVE')
         MATCHING (GROUP_ID, PARENT_ID, GROUPTABLE_ID, GROUPTABLE);
   end
end
^

SET TERM ; ^



/******************************************************************************/
/***               Generated by IBExpert 04.09.2020 11:18:08                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE MARK_DETAIL_TREB (
    ID                             DM_ID NOT NULL /* DM_ID = BIGINT */,
    DOC_ID                         DM_ID /* DM_ID = BIGINT */,
    PART_ID                        DM_ID /* DM_ID = BIGINT */,
    MARK_DATA                      DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    QUANT                          DM_STATUS /* DM_STATUS = INTEGER */,
    COMMITDATE                     DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    INSERTDT                       DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    DOCNUM                         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    DOC_DATE                       DM_DATE /* DM_DATE = DATE */,
    DDA_ID                         DM_ID /* DM_ID = BIGINT */,
    PLACE_ID_IN                    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    PLACE_ID_OUT                   DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    GTIN                           DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SERIES_NUMBER                  DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    EXPIRATION_DATE                DM_DATE /* DM_DATE = DATE */,
    ACTIVEPOS                      DM_ID /* DM_ID = BIGINT */,
    SERIA                          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    KIZ                            DM_TEXT_BIG /* DM_TEXT_BIG = VARCHAR(10000) */,
    EXPIRETION_DATE                DM_DATE /* DM_DATE = DATE */,
    SSCC                           DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    ACCEPTED                       DM_ID /* DM_ID = BIGINT */,
    STATUS                         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    AGREGATION                     DM_ID /* DM_ID = BIGINT */,
    DOC_DETAIL_ACTIVE_TREB_D$UUID  DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    D$UUID                         DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    D$SRVUPDDT                     DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);




/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE UNIQUE INDEX MARK_DETAIL_TREB_IDX1 ON MARK_DETAIL_TREB (D$UUID);
CREATE UNIQUE INDEX MARK_DETAIL_TREB_IDX2 ON MARK_DETAIL_TREB (DOC_DETAIL_ACTIVE_TREB_D$UUID,DOC_ID,MARK_DATA,SERIES_NUMBER);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: MARK_DETAIL_TREB_AD_DISTR */
CREATE OR ALTER TRIGGER MARK_DETAIL_TREB_AD_DISTR FOR MARK_DETAIL_TREB
ACTIVE AFTER DELETE POSITION 0
AS
begin
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('MARK_DETAIL_TREB',old.d$uuid,2,null) matching (TABLENAME,UUID);
end
^


/* Trigger: MARK_DETAIL_TREB_BI_DISTR */
CREATE OR ALTER TRIGGER MARK_DETAIL_TREB_BI_DISTR FOR MARK_DETAIL_TREB
ACTIVE BEFORE INSERT POSITION 0
AS
begin
    if (new.d$srvupddt is null) then
    begin
      new.d$srvupddt='2000-01-01';
      if (new.d$uuid is null) then
        new.d$uuid=UUID_TO_CHAR(GEN_UUID());
      update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('MARK_DETAIL_TREB',new.d$uuid,0,null) matching (TABLENAME,UUID);
    end
end
^


/* Trigger: MARK_DETAIL_TREB_BU_DISTR */
CREATE OR ALTER TRIGGER MARK_DETAIL_TREB_BU_DISTR FOR MARK_DETAIL_TREB
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
    if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
        update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('MARK_DETAIL_TREB',new.d$uuid,1,null) matching (TABLENAME,UUID);
end
^

SET TERM ; ^


SET TERM ^ ;



CREATE OR ALTER TRIGGER MARK_DETAIL_TREB_BIU1 FOR MARK_DETAIL_TREB
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
declare variable da_id dm_id;
declare variable doc_id dm_id;
begin
  if (new.d$srvupddt<>'2000-01-01') then
    if (exists(select id from doc_detail_active where DOC_DETAIL_ACTIVE_TREB_D$UUID = new.doc_detail_active_treb_d$uuid)) then
    begin
        select id, doc_id from doc_detail_active where DOC_DETAIL_ACTIVE_TREB_D$UUID  = new.doc_detail_active_treb_d$uuid into :da_id, :doc_id;
        update or 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,EXPIRATION_DATE,ACTIVEPOS,SERIA,KIZ,EXPIRETION_DATE,SSCC,ACCEPTED,STATUS,AGREGATION)
          values (:DOC_ID,0,new.MARK_DATA,abs(new.QUANT),new.COMMITDATE,new.INSERTDT,new.DOCNUM,new.DOC_DATE,:DA_ID,new.PLACE_ID_IN,new.PLACE_ID_OUT,new.GTIN,new.SERIES_NUMBER,new.EXPIRATION_DATE,1,new.SERIA,new.KIZ,new.EXPIRETION_DATE,new.SSCC,new.ACCEPTED,new.STATUS,new.AGREGATION)
        matching (doc_id,part_id,MARK_DATA,SERIES_NUMBER);

        /*if (not exists(select id from GROUP_DETAIL where GROUP_ID=-43 and GROUPTABLE_ID=:da_id and GROUPTABLE='DOC_DETAIL_ACTIVE')) then
         UPDATE OR INSERT INTO GROUP_DETAIL (GROUP_ID, PARENT_ID, GROUPTABLE_ID, GROUPTABLE) VALUES (-43, 0, :da_id, 'DOC_DETAIL_ACTIVE')
         MATCHING (GROUP_ID, PARENT_ID, GROUPTABLE_ID, GROUPTABLE);*/
    end
end
^

SET TERM ; ^

SET TERM ^ ;



CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_TREB_MARK_BI0 FOR DOC_DETAIL_ACTIVE_TREB
ACTIVE BEFORE INSERT POSITION 1
AS
begin
  if (new.d$srvupddt='2000-01-01') then
  begin
    insert into MARK_DETAIL_TREB (ID,DOC_ID,PART_ID,MARK_DATA,QUANT,COMMITDATE,INSERTDT,DOCNUM,DOC_DATE,DDA_ID,PLACE_ID_IN,PLACE_ID_OUT,GTIN,SERIES_NUMBER,EXPIRATION_DATE,ACTIVEPOS,SERIA,KIZ,EXPIRETION_DATE,SSCC,ACCEPTED,STATUS,AGREGATION, DOC_DETAIL_ACTIVE_TREB_D$UUID)
    select ID,DOC_ID,'0',MARK_DATA,QUANT,COMMITDATE,INSERTDT,DOCNUM,DOC_DATE,DDA_ID,PLACE_ID_IN,PLACE_ID_OUT,GTIN,SERIES_NUMBER,EXPIRATION_DATE,ACTIVEPOS,SERIA,KIZ,EXPIRETION_DATE,SSCC,ACCEPTED,STATUS,AGREGATION, new.d$uuid
      from MARK_DETAIL
      where DOC_ID = new.doc_id;
  end
end
^

SET TERM ; ^

ALTER TABLE AGENTS_PLACES ADD D$UUID DM_UUID;

ALTER TABLE AGENTS_PLACES ADD D$SRVUPDDT DM_DATETIME;

CREATE UNIQUE INDEX AGENTS_PLACES_IDX1 ON AGENTS_PLACES (D$UUID);

SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: MARK_DETAIL_TREB_AD_DISTR */
CREATE OR ALTER TRIGGER AGENTS_PLACES_AD_DISTR FOR AGENTS_PLACES
ACTIVE AFTER DELETE POSITION 0
AS
begin
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('AGENTS_PLACES',old.d$uuid,2,null) matching (TABLENAME,UUID);
end
^

 /* Trigger: MARK_DETAIL_TREB_BI_DISTR */
CREATE OR ALTER TRIGGER AGENTS_PLACES_BI_DISTR FOR AGENTS_PLACES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
    if (new.d$srvupddt is null) then
    begin
      new.d$srvupddt='2000-01-01';
      if (new.d$uuid is null) then
        new.d$uuid=UUID_TO_CHAR(GEN_UUID());
      update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('AGENTS_PLACES',new.d$uuid,0,null) matching (TABLENAME,UUID);
    end
end
^


/* Trigger: MARK_DETAIL_TREB_BU_DISTR */
CREATE OR ALTER TRIGGER AGENTS_PLACES_BU_DISTR FOR AGENTS_PLACES
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
    if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
        update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('AGENTS_PLACES',new.d$uuid,1,null) matching (TABLENAME,UUID);
end
^

SET TERM ; ^

</pre>