SET TERM ^ ;

create or alter procedure DBGPR_DELETE_ACTIVE_DETAIL (
    ACTIVE_DOC_ID type of DM_ID)
as
begin
  delete from doc_detail_active_mmbsh where doc_id = :active_doc_id;
  insert into doc_detail_deleted
(
PARENT_ID,
DOC_ID,
PART_ID,
PART_PARENT_ID,
DOC_DETAIL_ID,
PRICE,
NAC,
QUANT,
DISCOUNT,
SUMMA,
SUMMA_O,
DCARD,
WARE_ID,
PRICE_O,
PRICE_Z,
PRICE_R,
BARCODE,
BARCODE1,
GODENDO,
SERIA,
NDS,
SUM_NDSO,
SUM_NDSR,
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
)
    select
PARENT_ID,
DOC_ID,
PART_ID,
PART_PARENT_ID,
DOC_DETAIL_ID,
PRICE,
NAC,
QUANT,
DISCOUNT,
SUMMA,
SUMMA_O,
DCARD,
WARE_ID,
PRICE_O,
PRICE_Z,
PRICE_R,
BARCODE,
BARCODE1,
GODENDO,
SERIA,
NDS,
SUM_NDSO,
SUM_NDSR,
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
    from doc_detail_active da where doc_id = :active_doc_id;
--  delete from doc_detail_active where doc_id = :doc_id;
end^

SET TERM ; ^

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

GRANT SELECT,DELETE ON DOC_DETAIL_ACTIVE_MMBSH TO PROCEDURE DBGPR_DELETE_ACTIVE_DETAIL;
GRANT INSERT ON DOC_DETAIL_DELETED TO PROCEDURE DBGPR_DELETE_ACTIVE_DETAIL;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE DBGPR_DELETE_ACTIVE_DETAIL;

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

GRANT EXECUTE ON PROCEDURE DBGPR_DELETE_ACTIVE_DETAIL TO SYSDBA;


SET TERM ^ ;

create or alter procedure DBGPR_SETDOCSUMMS
as
declare variable DOC_ID type of DM_ID;
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 DSCSUMMA type of DM_DOUBLE;
declare variable SUM_DSC type of DM_DOUBLE;
begin
  for select id from docs where status=1 into :doc_id do
  begin
    select
      sum(summa),
      sum(summa_o),
      sum(sum_ndso),
      sum(sum_ndsr),
      sum(price*quant),
      sum(sum_dsc)
    from
      doc_detail
    where
      doc_id=:doc_id into :SUMMA, :SUMMA_O, :SUM_NDSO, :SUM_NDSR, :DSCSUMMA, :sum_dsc;
    update docs set SUMMA=:SUMMA, SUMMA_O=:SUMMA_O, SUM_NDSO=:SUM_NDSO, SUM_NDSR=:SUM_NDSR, CALCSUMMA=:DSCSUMMA, SUM_DSC=:sum_dsc where id=:doc_id;
  end
end^

SET TERM ; ^

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

GRANT SELECT,UPDATE ON DOCS TO PROCEDURE DBGPR_SETDOCSUMMS;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE DBGPR_SETDOCSUMMS;

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

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

SET TERM ^ ;

create or alter procedure PR_CORRECTPART (
    DOC_ID type of DM_ID,
    PART_ID type of DM_ID,
    QUANT type of DM_DOUBLE)
returns (
    ID type of DM_ID,
    MSG DM_TEXT)
as
declare variable NAC type of DM_DOUBLE;
declare variable PARENT_ID type of DM_ID;
begin
  if (quant<0) then quant=0;
  select id, msg from pr_rashodpart(:doc_id,:part_id,:quant,null,null,null,null,1) into :parent_id, :msg;
  if (parent_id<=0) then
   begin
    id=parent_id;
    suspend;
    exit;
    --exception EX_RASHOD_ERROR;
   end
  select id from doc_detail_active where doc_id=:doc_id and parent_id=:parent_id and part_id=0 into id;
  if (id is null) then
   begin
    id = gen_id(gen_doc_detail_active_id,1);
    insert into doc_detail_active (id,parent_id,doc_id,part_id,quant,nac,koef,motherpart_id)
    values (:id,:parent_id,:doc_id,:part_id,:quant,:nac,0,:part_id);
    update doc_detail_active set part_id=0 where id=:id;
    /*insert into group_detail (group_id,parent_id,grouptable_id,grouptable) select group_id, 0, cast(:id as dm_text), 'DOC_DETAIL_ACTIVE'
    from group_detail where grouptable_id=cast(:part_id as dm_text) and grouptable='PARTS'; */
    insert into group_detail (group_id,parent_id,grouptable_id,grouptable)
    select group_id, 0, cast(:id as dm_text), 'DOC_DETAIL_ACTIVE' from group_detail gd where
    grouptable_id=cast(:part_id as dm_text) and grouptable='PARTS' and
    -- 974777 A.K. 06/10/2020
    (not exists(select id from GROUP_DETAIL gd1 where gd1.GROUP_ID=gd.GROUP_ID and gd1.GROUPTABLE_ID=cast(:id as dm_text) and gd1.GROUPTABLE='DOC_DETAIL_ACTIVE'));
   end
  else
   begin
    update doc_detail_active set quant=:quant, summa=:quant*price, summa_o=:quant*price_o,
    sum_ndso=round((:quant*price_o)*nds/(100+nds),2),
    sum_ndsr=(:quant*price)*(select d.ndsr from deps d where d.id=dep)/(100+(select d.ndsr from deps d where d.id=dep))
    where id=:id;
   end
  suspend;
end^

SET TERM ; ^

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

GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_CORRECTPART;
GRANT SELECT,INSERT,UPDATE ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_CORRECTPART;
GRANT SELECT,INSERT ON GROUP_DETAIL TO PROCEDURE PR_CORRECTPART;
GRANT SELECT ON DEPS TO PROCEDURE PR_CORRECTPART;

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

GRANT EXECUTE ON PROCEDURE PR_CORRECTPART TO TRIGGER DOC_DETAIL_ACTIVE_TREB_BI0;
GRANT EXECUTE ON PROCEDURE PR_CORRECTPART TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_CORRECTPART TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_CORRECTPART TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_DOC_COMMIT (
    DOC_ID type of DM_ID,
    SESSION_ID type of DM_ID)
as
declare variable BASE_TYPE type of DM_STATUS;
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 DSCSUMMA type of DM_DOUBLE;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable PRICE_TYPE DM_STATUS;
declare variable CONTRACT_ID DM_ID;
begin
select dt.base_type, d.price_type, d.contract_id from docs d left join doc_types dt on d.doc_type=dt.id where d.id=:doc_id into :base_type, :price_type, :contract_id;
if (base_type = 4) then execute procedure pr_doc_virtual_commit(:doc_id);
else
if (base_type in (1,3,6,8)) then
begin
execute procedure pr_doc_prihod_commit(:doc_id,0);
end
else if (base_type in (2,7,9)) then
execute procedure pr_doc_rashod_commit(:doc_id,0);
else
exception EX_WRONGDOC_BASE_TYPE;

/* nicky edit */
if (exists (select * from docs d where d.id = :doc_id and d.doc_type in (3, 9))) then
  begin
    insert into egais_detail (doc_detail_id, egais_barcode, producer_inn, producer_kpp) select dd.id, dda.egais_barcode, dda.producer_inn, dda.producer_kpp
      from doc_detail dd join doc_detail_active dda on dd.doc_id = dda.doc_id and dd.part_id = dda.part_id where dd.doc_id = :doc_id and char_length(dda.egais_barcode) > 0;
  end
/* nicky edit end*/

delete from doc_detail_active where doc_id=:doc_id;
if (base_type = 3) then
begin
delete from warebase where part_id in (select part_id from doc_detail where doc_id = :doc_id and quant < 0 and part_type = 1);
end
if (base_type = 4) then
select sum(summa), sum(summa_o), sum(sum_ndso),sum(SUM_NDSR), sum(price*quant/10000), sum(sum_dsc) from
doc_detail_virtual where doc_id=:doc_id into :SUMMA, :SUMMA_O, :SUM_NDSO,:SUM_NDSR, :DSCSUMMA, :sum_dsc;
else
select sum(summa), sum(summa_o), sum(sum_ndso),sum(SUM_NDSR), sum(price*quant/10000), sum(sum_dsc) from
doc_detail where doc_id=:doc_id into :SUMMA, :SUMMA_O, :SUM_NDSO,:SUM_NDSR, :DSCSUMMA, :sum_dsc;
DSCSUMMA=DSCSUMMA*10000;
update docs set
commitsession_id=:session_id,
status=1,
POSTDT='now',
checkdata=(select CHECKDATA from PR_DOCCHECKDATA(:doc_id)),
SUMMA=:SUMMA,
SUMMA_O=:SUMMA_O,
SUM_NDSO=:SUM_NDSO,
SUM_NDSR=:SUM_NDSR,
CALCSUMMA=:DSCSUMMA,
sum_dsc=:sum_dsc,
cashdata=(iif(:base_type=1,-:SUMMA,null))
where id=:doc_id;
execute procedure PR_UPDBLOCKINFO_BY_DOC(:doc_id,1);
--if ((base_type = 2) and (contract_id<>0)) then
if (contract_id<>0) then
begin
if (base_type = 2) then
execute procedure PR_CREDITCOMMIT(:contract_id);
if (base_type in  (8,9)) then
execute procedure PR_CREDITREINITCOMMIT(:contract_id,:session_id);
--    execute procedure PR_CREDITCOMMIT(:contract_id);
end
execute procedure pr_doevent('EV_DOCCOMMIT');
end^

SET TERM ; ^

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

GRANT SELECT,UPDATE ON DOCS TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_VIRTUAL_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT INSERT ON EGAIS_DETAIL TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT,DELETE ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT,DELETE ON WAREBASE TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT ON DOC_DETAIL_VIRTUAL TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOCCHECKDATA TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_UPDBLOCKINFO_BY_DOC TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_CREDITCOMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_CREDITREINITCOMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOEVENT TO PROCEDURE PR_DOC_COMMIT;

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

GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_AGGREGATE_PARTS;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_CLEAR_PERESORT;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_CREDITREINIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_DOC_SPLITBYBASEAGENT;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE UTPR_RESTORE_DEL_DOC;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_DOC_REACTIVATE (
    ID DM_ID)
as
declare variable AUDIT_ID DM_ID;
begin
  if (exists(select * from docs where id = :id and doc_type in (3, 9) and status = 1)) then
    begin
      insert into doc_detail_active (parent_id, doc_id, part_id, price, quant, discount, summa, summa_o, sum_ndso, sum_ndsr, nac, human_quant, sum_dsc, dcard, part_type, make_id, base_agent_id)
        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, make_id, base_agent_id
        from doc_detail where doc_id = :id;
      select audit_id from docs where id = :id into :audit_id;
      select cancel_result from pr_doc_cancel(:id, 0, :audit_id, 0) into :audit_id;
      update docs set docnum = null, vnum = 0, vshift = 0, device_num = null, status = 0, summ1 = 0, summ2 = 0, summ3 = 0, summ4 = 0 where id = :id;
      delete from doc_detail_deleted where doc_id = :id;
      --delete from doc_pays where doc_id = :id;
    end
end^

SET TERM ; ^

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

GRANT SELECT,UPDATE ON DOCS TO PROCEDURE PR_DOC_REACTIVATE;
GRANT INSERT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_REACTIVATE;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_DOC_REACTIVATE;
GRANT EXECUTE ON PROCEDURE PR_DOC_CANCEL TO PROCEDURE PR_DOC_REACTIVATE;
GRANT SELECT,DELETE ON DOC_DETAIL_DELETED TO PROCEDURE PR_DOC_REACTIVATE;

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

GRANT EXECUTE ON PROCEDURE PR_DOC_REACTIVATE TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_PREPARECANCELINGDOC (
    DOC_ID type of DM_ID,
    RGUID type of DM_RGUID,
    SESSION_ID type of DM_ID)
returns (
    NEW_DOC_ID type of DM_ID)
as
declare variable BASE_TYPE type of DM_ID;
declare variable DOC_TYPE type of DM_ID;
declare variable AGENT_ID type of DM_ID;
declare variable DOCNUM type of DM_TEXT;
declare variable DOCDATE type of DM_DATETIME;
declare variable CAPTION type of DM_TEXT;
declare variable VNUM type of DM_ID;
declare variable VSHIFT type of DM_ID;
declare variable PART_ID type of DM_ID;
declare variable QUANT type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable NAC type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable KOEF type of DM_STATUS;
begin
  exception EX_WRONG_OPER;
  select d.doc_type, d.agent_id, d.docnum, d.docdate, d.caption, d.vnum, d.vshift, dt.base_type
   from docs d left join doc_types dt on d.doc_type=dt.id
   where d.id=:doc_id
   into :doc_type, :agent_id, :docnum, :docdate, :caption, :vnum, :vshift, :base_type;
  if ((base_type<>1) or (base_type<>2)) then exception EX_WRONGDOC_BASE_TYPE;
  if (base_type=1) then koef=1; else koef=0;
  new_doc_id=gen_id(gen_docs_id,1);
  insert into docs (ID,PARENT_ID,DOC_TYPE,STATUS,AGENT_ID,RGUID,AUDIT_ID,DOCNUM,DOCDATE,caption,vnum,vshift) values
    (:NEW_DOC_ID,:DOC_ID,:DOC_TYPE,0,:AGENT_ID,:RGUID,:session_ID,:DOCNUM,:DOCDATE,:caption,:vnum,:vshift);
  for
    select part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,SUM_NDSR from doc_detail where doc_id=:doc_id
     into :part_id,:quant,:dcard,:summa,:summa_o,:nac,:discount,:sum_ndso,:SUM_NDSR
  do
  begin
    insert into doc_detail_active (doc_id,part_id,quant,dcard,summa,summa_o,nac,discount,sum_ndso,SUM_NDSR,koef)
        values (:new_doc_id,:part_id,-:quant,:dcard,-:summa,-:summa_o,:nac,-:discount,-:sum_ndso,-:SUM_NDSR,:koef);
  end
  suspend;
end^

SET TERM ; ^

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

GRANT SELECT,INSERT ON DOCS TO PROCEDURE PR_PREPARECANCELINGDOC;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_PREPARECANCELINGDOC;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_PREPARECANCELINGDOC;
GRANT INSERT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_PREPARECANCELINGDOC;

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

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

SET TERM ^ ;

create or alter procedure PR_PRIHODPART (
    DOC_ID type of DM_ID,
    PART_ID type of DM_ID_NULL,
    QUANT type of DM_DOUBLE,
    SNAME type of DM_TEXT,
    SUMMA type of DM_DOUBLE,
    DISCOUNT type of DM_DOUBLE = 0,
    CREATENEWPART type of DM_STATUS = null,
    SUM_DSC type of DM_DOUBLE = 0,
    PART_TYPE DM_STATUS = 0,
    POS_ID DM_ID = null)
returns (
    ID type of DM_ID)
as
begin
if (((part_id is null) or (part_id = 0)) and (pos_id > 0))  then
begin
insert into doc_detail_active (PARENT_ID, DOC_ID, PART_ID,PART_PARENT_ID,DOC_DETAIL_ID,PRICE,NAC,QUANT,DISCOUNT,SUMMA,SUMMA_O,DCARD,WARE_ID,PRICE_O,PRICE_Z,PRICE_R,BARCODE,BARCODE1,GODENDO,SERIA,NDS,SUM_NDSO,SUM_NDSR,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,PACKET,VPART_ID,GNVLS, MOTHERPART_UUID)
select PARENT_ID, :DOC_ID as DOC_ID ,PART_ID,PART_PARENT_ID,DOC_DETAIL_ID,PRICE,NAC,QUANT,DISCOUNT,SUMMA,SUMMA_O,DCARD,WARE_ID,PRICE_O,PRICE_Z,PRICE_R,BARCODE,BARCODE1,GODENDO,SERIA,NDS,SUM_NDSO,SUM_NDSR,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,PACKET,VPART_ID,GNVLS, MOTHERPART_UUID from doc_detail_active where id = :pos_id;
end
else if ( (part_id is null) or (part_id = 0) ) then
begin
id=gen_id(gen_doc_detail_active_id,1);
insert into DOC_DETAIL_ACTIVE (id,doc_id,sname,quant,koef,SUM_DSC, part_type) values (:id,:doc_id,:sname,:quant,0,:SUM_DSC, :part_type);
end
else
begin
--    select price from parts where id=:part_id into :price;
if (summa is null) then
begin
select price*:quant from parts where id=:part_id into :summa;
--      summa=quant*price;
--      discount=0;
end
--    else
--    begin
--      discount=quant*price-summa;
--    end
select id from doc_detail_active where doc_id=:doc_id and part_id=:part_id into :id;
if (id is null) then
begin
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) values (:id,:doc_id,:part_id,:quant,:summa,:discount,0,:SUM_DSC);
if (createnewpart=1) then
update DOC_DETAIL_ACTIVE set part_id=0 where id=:id;
end
else
begin
--      if (quant=0) then
if (quant between -0.00001 and 0.000001) then
delete from  doc_detail_active where id=:id;
else
update doc_detail_active set quant=:quant, summa=:summa, summa_o=:quant*price_o, discount=:discount,
sum_ndso=round((:quant*price_o)*nds/(100+nds),2),
sum_ndsr=(:quant*price)*(select d.ndsr from deps d where d.id=dep)/(100+(select d.ndsr from deps d where d.id=dep)),
SUM_DSC=:SUM_DSC where id=:id;
end
end
suspend;
end^

SET TERM ; ^

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

GRANT SELECT,INSERT,DELETE,UPDATE ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_PRIHODPART;
GRANT SELECT ON PARTS TO PROCEDURE PR_PRIHODPART;
GRANT SELECT ON DEPS TO PROCEDURE PR_PRIHODPART;

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

GRANT EXECUTE ON PROCEDURE PR_PRIHODPART TO PROCEDURE PR_DOC_CANCELDEFER;
GRANT EXECUTE ON PROCEDURE PR_PRIHODPART TO PROCEDURE PR_DOC_SPLITBYBASEAGENT;
GRANT EXECUTE ON PROCEDURE PR_PRIHODPART TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_PRIHODPART TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_PRIHODPART TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_RASHODPART (
    DOC_ID type of DM_ID,
    PART_ID type of DM_ID,
    QUANT type of DM_DOUBLE,
    SUMMA DM_DOUBLE,
    DCARD type of DM_TEXT,
    DONTCHECKQUANT type of DM_STATUS,
    DISCOUNT type of DM_DOUBLE,
    IGNOREZEROQUANT type of DM_STATUS = null,
    SUM_DSC type of DM_DOUBLE = 0,
    ACTIVE_ID bigint = null)
returns (
    ID type of DM_ID,
    MSG DM_TEXT)
as
declare variable BASE_QUANT type of DM_DOUBLE;
declare variable ACTIVE_QUANT type of DM_DOUBLE;
declare variable PRICE type of DM_DOUBLE;
declare variable PRICE_O type of DM_DOUBLE;
declare variable NDS type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable NAC type of DM_DOUBLE;
declare variable PART_TYPE DM_STATUS;
declare variable PRICE_TYPE DM_ID;
declare variable CONTRACT_ID DM_ID;
declare variable CONTRACTDOC_ID DM_ID;
declare variable PART_CONTRACT_ID DM_ID;
declare variable DOC_BASE_TYPE DM_ID;
declare variable BARCODE DM_TEXT;
declare variable RASHOD_MINUS DM_ID_NULL;
declare variable DOC_TYPE DM_ID_NULL;
declare variable FLAG DM_ID_NULL;
declare variable DEP DM_ID;
declare variable NDSR DM_DOUBLE;
begin
  flag = 0;

  /*Выборка параметра разрешено/запрещено отрицательные остатки*/
  select param_value from params where param_id='RASHOD_MINUS' into :RASHOD_MINUS;

  select price_type, contract_id, (select dt.base_type from doc_types dt where dt.id=d.doc_type), d.doc_type from docs d where id=:doc_id into :price_type, :contract_id, :doc_base_type, :doc_type;

  /*Если кассовый документ игнорируем настройки в менеджере*/
  if (doc_type in (3,9)) then flag=1;

  select contract_id, barcode from parts where id=:part_id into :part_contract_id, :barcode;
  if (contract_id=0) then
  begin
    if (part_contract_id<>0) then
    begin
      id=-2;
      msg='Партия принадлежит договору кредита. Расход невозможен!';
      suspend;
      exit;
    end
  end
  else
  begin
--    if ((contract_id<>part_contract_id) and (doc_base_type<>8)) then
    if (((doc_base_type=8) and (part_contract_id<>0)) or
        (doc_base_type<>8) and (contract_id<>part_contract_id))  then
    begin
      id=-3;
      msg='Партия принадлежит другому договору кредита. Расход невозможен!';
      suspend;
      exit;
    end
  end 
  if (price_type<>0) then
  begin
    select price from prices where part_id=:part_id and price_type=:price_type into :price;
--    if ((price is null) or (price=0)) then
    if ((price is null) or (price between -0.000001 and 0.000001)) then
    begin
      msg='У партии не установлен тип цены, соответствующий документу!';
      id=-1;
      suspend;
      exit;
    end
  end
  else
    price=null;
  quant=quant*-1;
  select sum(quant*koef) from doc_detail_active where doc_id<>:doc_id and part_id=:part_id into :active_quant;
  if (active_quant is null) then active_quant=0;
--  select quant, price, price_o, nds, part_type from warebase where part_id=:part_id into :base_quant, :price, :price_o, :nds, :part_type;
/*AVO 24/03/2020
  add ",nac"*/
  select quant, iif(:price is null, price,  :price), price_o, nds, part_type,dep,nac from warebase where
   part_id=:part_id into :base_quant, :price, :price_o, :nds, :part_type,:dep,:nac;
--  if (((active_quant+base_quant+quant)<0) and ((DONTCHECKQUANT<>1) or (DONTCHECKQUANT is null) ) and (part_type=0)) then

  if ((RASHOD_MINUS=1) and (flag=0)) then --разрешить минус в расходе в менеджере
   DONTCHECKQUANT=1; 
  if ((RASHOD_MINUS=0) and (flag=0)) then
   DONTCHECKQUANT=0;

  if (((active_quant+base_quant+quant)<-0.00001) and ((DONTCHECKQUANT<>1) or (DONTCHECKQUANT is null) ) and (part_type=0)) then
  begin
    id=0;
    msg='Позиция не была добавлена в текущий документ! Проверьте остаток по позиции!';
  end 
  else
  begin
/*AVO 24/03/2020
  add flag=0 для расчета суммы розничной исходя из цены и кол-ва*/
    if ((flag=0) or (summa is null)) then
     begin
      summa=quant*price;
     end
-- end 24/03/2020

    if (price_o = 0) then
      nac=0;
/*AVO 24/03/2020
      Убрал принудительный расчет наценки, так как он считает не верно
      наценку берем из warebase */
--    else
--      nac=(price-price_o)*100/price_o;

    summa_o=quant*price_o;
    if (ACTIVE_ID is null) then
    begin
      select id from doc_detail_active where doc_id=:doc_id and part_id=:part_id into :id;
    end
    else
    begin
      if (ACTIVE_ID=0) then
        id=null;
      else
        id=active_id;
    end
    if (id is null) then
    begin
      if ((quant<>0) or (IGNOREZEROQUANT is not null)) then
      begin
        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,SUM_NDSR,koef,SUM_DSC, barcode) values
        (:id,:doc_id,:part_id,:quant,:dcard,:summa,:summa_o,:nac,:discount,
        round(:summa_o*:nds/(100+:nds),2),
        round(:summa*(select d.ndsr from deps d where d.id=:dep)/(100+(select d.ndsr from deps d where d.id=:dep)),2),
        1,:SUM_DSC, :barcode);
      end
      ELSE
        id=0;
    end
    else
    begin
      if ((quant between -0.000001 and 0.000001) and (IGNOREZEROQUANT is null)) then
        delete from  doc_detail_active where id=:id;
      else
        update doc_detail_active set quant=:quant, summa=:summa, summa_o=:summa_o, nac=:nac ,discount=:discount,
        sum_ndso=round(:summa_o*:nds/(100+:nds),2),
        sum_ndsr=:summa*(select d.ndsr from deps d where d.id=:dep)/(100+(select d.ndsr from deps d where d.id=:dep)),
        SUM_DSC=:SUM_DSC where id=:id;
    end 
  end
  suspend;
end^

SET TERM ; ^

COMMENT ON PROCEDURE PR_RASHODPART IS
'Добавление партии в расходный доукумент';

COMMENT ON PARAMETER PR_RASHODPART.ACTIVE_ID IS
'NULL - одна партия на документ; 0 - может быть несколько партий в одном док-те; >0 - ID в DOC_DETAIL_ACTIVE';

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

GRANT SELECT ON PARAMS TO PROCEDURE PR_RASHODPART;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_RASHODPART;
GRANT SELECT ON DOCS TO PROCEDURE PR_RASHODPART;
GRANT SELECT ON PARTS TO PROCEDURE PR_RASHODPART;
GRANT SELECT ON PRICES TO PROCEDURE PR_RASHODPART;
GRANT SELECT,INSERT,DELETE,UPDATE ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_RASHODPART;
GRANT SELECT ON WAREBASE TO PROCEDURE PR_RASHODPART;
GRANT SELECT ON DEPS TO PROCEDURE PR_RASHODPART;

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

GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_AGGREGATE_PARTS;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_CLEAR_PERESORT;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_CORRECTPART;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_CREATECURDOCFROMCUSTOMDOC;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_CREDITREINIT;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_DOC_CANCELDEFER;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_DOC_SPLITBYBASEAGENT;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_MAKERASHODFROMPRIHOD;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_ORDER_CASTING;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO PROCEDURE PR_RASHOD_FEFO;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_RASHODPART TO SYSDBA;

SET TERM ^ ;

create or alter procedure UTPR_RESTORE_DEL_DOC (
    DEL_DOC_ID DM_ID)
returns (
    DOC_ID DM_ID)
as
begin
  if ((select status from docs where id=:del_doc_id)<>-1) then  exception EX_WRONGDOC_BASE_TYPE;

  doc_id=gen_id(gen_docs_id,1);
  insert into docs (
    ID,
    PARENT_ID,
    DOC_TYPE,
    STATUS,
    AGENT_ID,
    DOCNUM,
    DOCDATE,
    CAPTION,
    POSTDT,
    AUDIT_ID,
    VNUM,
    VSHIFT,
    CREATER,
    OWNER,
    COMMITDATE,
    DEVICE_NUM,
    SUMMA,
    SUMMA_O,
    SUM_NDSO,
    SUM_NDSR,
    CALCSUMMA,
    COMMENTS,
    SUMM1,
    SUMM2,
    SUMM3,
    SUMM4,
    CHECKDATA,
    COMMITSESSION_ID,
    SUM_DSC,
    CASHDATA,
    PRICE_TYPE,
    CURRENCY_ID,
    BASE_AGENT_ID,
    CONTRACT_ID,
    DELETED_DOC_ID,
    OPLATA,
    DATE_OPL,
    MAKE_ID,
    DOC_PAY_DATE,
    BONUS)
    select
    :DOC_ID,
    PARENT_ID,
    DOC_TYPE,
    0,
    AGENT_ID,
    DOCNUM,
    DOCDATE,
    CAPTION,
    POSTDT,
    AUDIT_ID,
    VNUM,
    VSHIFT,
    CREATER,
    OWNER,
    COMMITDATE,
    DEVICE_NUM,
    SUMMA,
    SUMMA_O,
    SUM_NDSO,
    SUM_NDSR,
    CALCSUMMA,
    COMMENTS,
    SUMM1,
    SUMM2,
    SUMM3,
    SUMM4,
    CHECKDATA,
    COMMITSESSION_ID,
    SUM_DSC,
    CASHDATA,
    PRICE_TYPE,
    CURRENCY_ID,
    BASE_AGENT_ID,
    CONTRACT_ID,
    :del_doc_id,
    OPLATA,
    DATE_OPL,
    MAKE_ID,
    DOC_PAY_DATE,
    BONUS from docs where id=:del_doc_id;

    insert into doc_detail_active (
    PARENT_ID,
    DOC_ID,
    PART_ID,
    PART_PARENT_ID,
    PRICE,
    NAC,
    QUANT,
    DISCOUNT,
    SUMMA,
    SUMMA_O,
    DCARD,
    WARE_ID,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    BARCODE,
    BARCODE1,
    GODENDO,
    SERIA,
    NDS,
    SUM_NDSO,
    SUM_NDSR,
    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,
    MOTHERPART_ID,
    DEP,
    BCODE_IZG,
    SUM_DSC,
    BASE_AGENT_ID,
    ACCOMP_ID)

    select
    PARENT_ID,
    :DOC_ID,
    PART_ID,
    PART_PARENT_ID,
    PRICE,
    NAC,
    QUANT,
    DISCOUNT,
    SUMMA,
    SUMMA_O,
    DCARD,
    WARE_ID,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    BARCODE,
    BARCODE1,
    GODENDO,
    SERIA,
    NDS,
    SUM_NDSO,
    SUM_NDSR,
    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,
    MOTHERPART_ID,
    DEP,
    BCODE_IZG,
    SUM_DSC,
    BASE_AGENT_ID,
    ACCOMP_ID from doc_detail_deleted where doc_id=:del_doc_id;

    execute procedure pr_doc_commit(:doc_id,0);
  suspend;
end^

SET TERM ; ^

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

GRANT SELECT,INSERT ON DOCS TO PROCEDURE UTPR_RESTORE_DEL_DOC;
GRANT INSERT ON DOC_DETAIL_ACTIVE TO PROCEDURE UTPR_RESTORE_DEL_DOC;
GRANT SELECT ON DOC_DETAIL_DELETED TO PROCEDURE UTPR_RESTORE_DEL_DOC;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE UTPR_RESTORE_DEL_DOC;

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

GRANT EXECUTE ON PROCEDURE UTPR_RESTORE_DEL_DOC TO SYSDBA;

SET SQL DIALECT 3;



SET TERM ^ ;



CREATE OR ALTER TRIGGER DOCS_BU0 FOR DOCS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin

  --Заявка 538709 А.К. Сквозная нумерация на расход оптовый / расход  на комиссию / Расход по льготе в рамках одного года (согласовано с Н.Н.)
  if ( ( (new.doc_type <> old.doc_type) or (new.vnum <> old.vnum) or (extract(YEAR from new.commitdate)  <> extract(YEAR from old.commitdate)) ) and (new.agent_id<>-1) ) then
  begin
    if (new.doc_type = 36) then
    begin
      if (exists (select 1 from docs d where doc_type in (1,36) and id <> new.id and d.vnum = new.vnum and extract(YEAR from commitdate)=extract(YEAR from new.commitdate))) then
        select max(vnum)+1 from docs where doc_type in (1,36) and id <> new.id and extract(YEAR from commitdate)=extract(YEAR from new.commitdate) into new.vnum;
    end
    else if (new.doc_type in (11,39,100)) then
    begin
      if (exists (select 1 from docs d where doc_type in (11,39,100) and id <> new.id and d.vnum = new.vnum and extract(YEAR from commitdate)=extract(YEAR from new.commitdate))) then
        select max(vnum)+1 from docs where doc_type in (11,39,100) and id <> new.id and extract(YEAR from commitdate)=extract(YEAR from new.commitdate) into new.vnum;
    end

  end

    --908390 A.K. 2018-04-23
    --cквозная нумерация на приход/расход перемешение в рамках одного года
    if (new.doc_type in (6)) then
    begin
      if (exists (select 1 from docs d where doc_type in (6) and id <> new.id and d.vnum = new.vnum and extract(YEAR from commitdate)=extract(YEAR from new.commitdate))) then
        select max(vnum)+1 from docs where doc_type in (6) and id <> new.id and extract(YEAR from commitdate)=extract(YEAR from new.commitdate) into new.vnum;
    end
    else if (new.doc_type in (2)) then
    begin
      if (exists (select 1 from docs d where doc_type in (2) and id <> new.id and d.vnum = new.vnum and extract(YEAR from commitdate)=extract(YEAR from new.commitdate))) then
        select max(vnum)+1 from docs where doc_type in (2) and id <> new.id and extract(YEAR from commitdate)=extract(YEAR from new.commitdate) into new.vnum;
    end


    if (new.vnum is null) then new.vnum=1;


insert into docs_log (
ID,
PARENT_ID,
DOC_TYPE,
STATUS,
AGENT_ID,
DOCNUM,
DOCDATE,
CAPTION,
RGUID,
INSERTDT,
POSTDT,
AUDIT_ID,
VNUM,
VSHIFT,
CREATER,
OWNER,
COMMITDATE,
PACKET,
DEVICE_NUM,
SUMMA,
SUMMA_O,
SUM_NDSO,
sum_ndsr,
CALCSUMMA,
COMMENTS,
SUMM1,
SUMM2,
SUMM3,
SUMM4,
CHECKDATA,
COMMITSESSION_ID,
SUM_DSC,
CASHDATA,
PRICE_TYPE,
CURRENCY_ID,
BASE_AGENT_ID,
CONTRACT_ID
)
values (
old.ID,
old.PARENT_ID,
old.DOC_TYPE,
old.STATUS,
old.AGENT_ID,
old.DOCNUM,
old.DOCDATE,
old.CAPTION,
old.RGUID,
old.INSERTDT,
old.POSTDT,
old.AUDIT_ID,
old.VNUM,
old.VSHIFT,
old.CREATER,
old.OWNER,
old.COMMITDATE,
old.PACKET,
old.DEVICE_NUM,
old.SUMMA,
old.SUMMA_O,
old.SUM_NDSO,
old.sum_ndsr,
old.CALCSUMMA,
old.COMMENTS,
old.SUMM1,
old.SUMM2,
old.SUMM3,
old.SUMM4,
old.CHECKDATA,
old.COMMITSESSION_ID,
old.SUM_DSC,
old.CASHDATA,
old.PRICE_TYPE,
old.CURRENCY_ID,
old.BASE_AGENT_ID,
old.contract_id
);
 if ((old.commitdate<>new.commitdate) or
    (old.commitdate is null and new.commitdate is not null) or
    (old.commitdate is not null and new.commitdate is null)) then
  begin
   if (new.status=1) then
    update doc_detail set doc_detail.doc_commitdate=cast(new.commitdate as date) where doc_id=new.id;
  end
 if ((new.status<>old.status) and (new.status=1) and (new.make_id is not null) and new.doc_type=11) then
  update makes set status=4 where id=new.make_id;
 if ((new.status<>old.status) and (new.status=-1) and (new.make_id is not null) and new.doc_type=11) then
  update makes set status=1 where id=new.make_id;
 if ( (new.doc_type<>old.doc_type) or (new.docnum<>old.docnum) ) then
  select caption || ' №' || new.docnum from doc_types where id=new.doc_type into new.caption;
  --12/12/2019 AVO принудительно обновляем данные по дате оплаты
 if (new.doc_type in (1,20)) then
  begin
   -- если дату исправляем сами пересчет не нужен
   if ( new.doc_pay_date<>old.doc_pay_date) then exit;

   if ((select credit_depth from agents where id = new.agent_id) is not null) then
     new.doc_pay_date = dateadd ((select credit_depth from agents where id = new.agent_id) day to new.commitdate);
    --avo 09/04/2019 если не заполнено в контрагентах, заполняем текущими
   else new.doc_pay_date = current_date;
  end
end
^

SET TERM ; ^


SET SQL DIALECT 3;



SET TERM ^ ;



CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BU0 FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
begin

if (new.nds<>old.nds) then  --Изменение НДС только через отдел   OlgaV 20141114
begin
  select first 1 id from deps where status=0 and nds=new.nds into new.dep;
  new.nds=old.nds;
end
if (new.dep<>old.dep) then
  select nds from deps where id=new.dep into new.nds;
if ((new.nds<>old.nds) or (new.sum_ndso is null) or (new.sum_ndsr is null) ) then
begin
  new.sum_ndso=round(new.summa_o*new.nds/(100+new.nds),2);
  new.sum_ndsr=coalesce(new.summa*(select ndsr from deps where id=new.dep)/(100+(select ndsr from deps where id=new.dep)),0);
end       --Пересчет сумм НДС Olgav20141114

if ( (new.sname <> old.sname) or (new.name_id is null) or (new.name_id = '0') )   then
select val_id from pr_getval_id(new.sname,0,new.part_type,new.mnn) into new.name_id;
--select id from vals where vtype=0 and alttype=new.part_type and svalue=new.sname into new.name_id;
if (new.name_id is null) then new.name_id=0;
if ( (new.summa is null) and ((new.quant<>0) and (new.price<>0)) ) then
begin
new.summa=new.quant*new.price;
if (new.sum_dsc is not null) then new.summa = new.summa - new.sum_dsc;
end
if ( ((new.summa_o = 0) or (new.summa_o is null) ) and ((new.quant<>0) and (new.price_o<>0)) ) then
new.summa_o=new.quant*new.price_o;
/*05/07/2019 940760, так же в Стандартах М
/*AVO отключено выполнение PR_COPYGROUPS в связи с присвоением не нужных групп*/
--if (new.name_id <> old.name_id) then execute procedure PR_COPYGROUPS(old.name_id, new.name_id);
if (new.sum_dsc is null) then
if (new.summa<>old.summa) then
begin
new.sum_dsc=(1+new.discount/100)*100*new.summa/(100+old.discount)-100*new.summa/(100+old.discount);
--new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount);
end
if (new.sum_dsc is null) then
if (new.discount<>old.discount) then
begin
new.sum_dsc=(1+new.discount/100)*100*old.summa/(100+old.discount)-100*old.summa/(100+old.discount);
new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount);
end
if (abs(new.sum_ndso-round((new.quant*new.price_o)*new.nds/(100+new.nds),2))>0.05) then
  new.sum_ndso=round((new.quant*new.price_o)*new.nds/(100+new.nds),2);
if (abs(new.sum_ndsr-(new.summa)*(100+(select d.ndsr from deps d where d.id=new.dep))/(100+(select d.ndsr from deps d where d.id=new.dep)))>0.05) Then
  new.sum_ndsr=coalesce((new.summa)*(select d.ndsr from deps d where d.id=new.dep)/(100+(select d.ndsr from deps d where d.id=new.dep)),0);
select id from vals where vtype=3 and alttype=new.part_type and svalue=new.sizg into new.izg_id;
if (new.izg_id is null) then new.izg_id=0;
select id from vals where vtype=2 and alttype=new.part_type and svalue=new.scountry into new.country_id;
if (new.country_id is null) then new.country_id=0;
select id from vals where vtype=1 and alttype=new.part_type and svalue=new.sorig_name into new.orig_name_id;
if (new.orig_name_id is null) then new.orig_name_id=0;
select id from vals where vtype=6 and alttype=new.part_type and svalue=new.sorig_izg into new.orig_izg_id;
if (new.orig_izg_id is null) then new.orig_izg_id=0;
select id from vals where vtype=5 and alttype=new.part_type and svalue=new.sorig_country into new.orig_country_id;
if (new.orig_country_id is null) then new.orig_country_id=0;
end

if ((old.part_id<>0) and (new.part_id=0) and (select deleted_doc_id from docs where id=new.doc_id) is null ) then
 begin
  if ((select PARAM_VALUE from PR_GETPARAMVALUE('GEN_BARCODE',0))=0) then
   select res_bcode from utpr_getchecksum_ean13(new.id) into new.barcode;
  else --add 958898 AVO 19/03/2020
   select res_bcode from utpr_getchecksum_ean13((select cast(trim(l_id) as dm_text) from vals where d$uuid=new.name_id),'88') into new.barcode;
--old 19/03/2020 --select res_bcode from utpr_getchecksum_ean13((select l_id from vals where d$uuid=new.name_id),'88') into new.barcode;
 end

if ((select PARAM_VALUE from pr_getparamvalue('CHANGE_BRC_FOR_CORRECT','0')) = '0') Then --Менять ШК в док-тах корректировки
  if ((select d.doc_type from docs d where d.id=new.doc_id) in (7,8,22) and new.quant>0 and new.barcode<>'' and new.barcode is not null) then
    new.barcode=(select barcode from doc_detail_active where id=new.parent_id);

if (new.bcode_izg is null) then new.bcode_izg = ''; --20160419 A.K.
if (new.orig_code is null) then new.orig_code='';

exit;
insert into doc_detail_active_log
(ID,
PARENT_ID,
DOC_ID,
PART_ID,
PART_PARENT_ID,
DOC_DETAIL_ID,
PRICE,
NAC,
QUANT,
DISCOUNT,
SUMMA,
SUMMA_O,
DCARD,
WARE_ID,
PRICE_O,
PRICE_Z,
PRICE_R,
BARCODE,
BARCODE1,
BCODE_IZG,
--KRITK,
GODENDO,
SERIA,
NDS,
SUM_NDSO,
SUM_NDSR,
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,
SUM_DSC,
HUMAN_QUANT,
customdraw,
part_type,
base_agent_id
)
values
(
old.ID,
old.PARENT_ID,
old.DOC_ID,
old.PART_ID,
old.PART_PARENT_ID,
old.DOC_DETAIL_ID,
old.PRICE,
old.NAC,
old.QUANT,
old.DISCOUNT,
old.SUMMA,
old.SUMMA_O,
old.DCARD,
old.WARE_ID,
old.PRICE_O,
old.PRICE_Z,
old.PRICE_R,
old.BARCODE,
old.BARCODE1,
old.bcode_izg,
--old.KRITK,
old.GODENDO,
old.SERIA,
old.NDS,
old.SUM_NDSO,
old.SUM_NDSR,
old.SERT,
old.DATESERT,
old.KEMVSERT,
old.SDSERT,
old.REGN,
old.NGTD,
old.EDIZM,
old.NAME_ID,
old.IZG_ID,
old.COUNTRY_ID,
old.ORIG_CODE,
old.ORIG_NAME_ID,
old.ORIG_IZG_ID,
old.ORIG_COUNTRY_ID,
old.Z_ID,
old.SKLAD_ID,
old.SNAME,
old.SIZG,
old.SCOUNTRY,
old.SORIG_NAME,
old.SORIG_IZG,
old.SORIG_COUNTRY,
old.INSERTDT,
old.INFO,
old.KOEF,
old.MOTHERPART_ID,
old.DEP,
OLD.sum_dsc,
old.HUMAN_QUANT,
old.customdraw,
old.part_type,
old.base_agent_id
);
end
^

SET TERM ; ^


SET SQL DIALECT 3;



SET TERM ^ ;



CREATE OR ALTER TRIGGER DOC_DETAIL_AD0 FOR DOC_DETAIL
ACTIVE AFTER DELETE POSITION 0
AS
begin
  insert into doc_detail_deleted
    (PARENT_ID, DOC_ID, PART_ID, DOC_DETAIL_ID, QUANT, DISCOUNT, SUMMA, SUMMA_O, DCARD, SUM_NDSO,SUM_NDSR, doc_detail_INSERTDT,sum_dsc)
  values
    (old.PARENT_ID, old.DOC_ID, old.PART_ID, old.ID, old.QUANT, old.DISCOUNT, old.SUMMA, old.SUMMA_O, old.DCARD, old.SUM_NDSO,old.SUM_NDSR, old.INSERTDT,old.sum_dsc);
  execute procedure pr_setpartrealquant(old.part_id,old.doc_id);
end
^

SET TERM ; ^


SET SQL DIALECT 3;



SET TERM ^ ;



CREATE OR ALTER TRIGGER DOC_DETAIL_BU0 FOR DOC_DETAIL
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  insert into doc_detail_log(
ID,
PARENT_ID,
DOC_ID,
PART_ID,
PRICE,
QUANT,
DISCOUNT,
SUMMA,
SUMMA_O,
DCARD,
INSERTDT,
SUM_NDSO,
SUM_NDSR,
NAC,
PACKET,
DOC_COMMITDATE,
HUMAN_QUANT,
SUM_DSC,
part_type,
base_agent_id
) values(
old.ID,
old.PARENT_ID,
old.DOC_ID,
old.PART_ID,
old.PRICE,
old.QUANT,
old.DISCOUNT,
old.SUMMA,
old.SUMMA_O,
old.DCARD,
old.INSERTDT,
old.SUM_NDSO,
OLD.SUM_NDSR,
old.NAC,
old.PACKET,
old.DOC_COMMITDATE,
old.HUMAN_QUANT,
old.SUM_DSC,
old.part_type,
old.base_agent_id);
end
^

SET TERM ; ^


SET SQL DIALECT 3;


CREATE GENERATOR GEN_DOC_DETAIL_DELETED_ID;

SET TERM ^ ;



CREATE OR ALTER TRIGGER DOC_DETAIL_DELETED_BI FOR DOC_DETAIL_DELETED
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_DOC_DETAIL_DELETED_id,1);
  new.insertdt='now';

  if (new.packet is null) then new.packet=0;
  if (new.parent_id is null) then new.parent_id=0;
  if (new.part_id is null) then new.part_id=0;
  if (new.part_parent_id is null) then new.part_parent_id=0;
  if (new.ware_id is null) then new.ware_id=0;
  if (new.name_id is null) then new.name_id=0;
  if (new.izg_id is null) then new.izg_id=0;
  if (new.country_id is null) then new.country_id=0;
  if (new.orig_name_id is null) then new.orig_name_id=0;
  if (new.orig_izg_id is null) then new.orig_izg_id=0;
  if (new.orig_country_id is null) then new.orig_country_id=0;
  if (new.z_id is null) then new.z_id=0;
  if (new.sklad_id is null) then new.sklad_id='';
  if (new.motherpart_id is null) then new.motherpart_id=0;
  if (new.doc_detail_id is null) then new.doc_detail_id=0;

  if (new.part_id>0) then -- заполняем значения партии
  begin
    select
WARE_ID,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
--QUANT,
BARCODE,
BARCODE1,
--KRITK,
GODENDO,
SERIA,
NDS,
--SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
nac,
dep,
BASE_AGENT_ID,
coalesce((select motherpart_id from pr_getmotherpart(new.part_id)),0)
    from parts where id=new.part_id into
new.WARE_ID,
new.PRICE,
new.PRICE_O,
new.PRICE_Z,
new.PRICE_R,
--new.QUANT,
new.BARCODE,
new.BARCODE1,
--new.KRITK,
new.GODENDO,
new.SERIA,
new.NDS,
--new.SUM_NDSO,
new.SERT,
new.DATESERT,
new.KEMVSERT,
new.SDSERT,
new.REGN,
new.NGTD,
new.EDIZM,
new.nac,
new.dep,
new.BASE_AGENT_ID,
new.motherpart_id;
  end 

  if (new.base_agent_id is null) then
    new.base_agent_id=0;

--  if (new.ware_id>0) then -- заполняем значения позиции
  if (new.ware_id not in ('-1', '0')) then -- заполняем значения позиции
  begin
    select
ww.NAME_ID,
(select svalue from vals where id=ww.name_id),
ww.IZG_ID,
(select svalue from vals where id=ww.IZG_ID),
ww.COUNTRY_ID,
(select svalue from vals where id=ww.COUNTRY_ID),
ww.ORIG_CODE,
ww.ORIG_NAME_ID,
(select svalue from vals where id=ww.ORIG_NAME_ID),
ww.ORIG_IZG_ID,
(select svalue from vals where id=ww.ORIG_IZG_ID),
ww.ORIG_COUNTRY_ID,
(select svalue from vals where id=ww.ORIG_COUNTRY_ID),
ww.BARCODE,
ww.Z_ID,
ww.SKLAD_ID
    from wares ww where ww.id=new.ware_id into
new.NAME_ID,
new.SNAME,
new.IZG_ID,
new.SIZG,
new.COUNTRY_ID,
new.scountry,
new.ORIG_CODE,
new.ORIG_NAME_ID,
new.sorig_name,
new.ORIG_IZG_ID,
new.sorig_izg,
new.ORIG_COUNTRY_ID,
new.sorig_country,
new.bcode_izg,
new.Z_ID,
new.SKLAD_ID;
  end 

  if (new.quant is null) then new.quant=0;
  if (new.price is null) then new.price=0;
  if (new.price_o is null) then new.price_o=0;
  if (new.nds is null) then new.nds=0;
  if (new.summa is null) then new.summa=new.quant*new.price;
  if (new.summa_o is null) then new.summa_o=new.quant*new.price_o;

  if (new.sum_ndso is null) then  new.sum_ndso=round(new.summa_o*new.nds/(100+new.nds),2);
  if (new.sum_ndsr is null) then new.sum_ndsr=new.summa*(select ndsr from deps where id=new.dep)/(100+(select ndsr from deps where id=new.dep));

end
^

SET TERM ; ^