Исправление от 04/09/2020 — различия между версиями
Материал из wiki.standart-n.ru
Avo (обсуждение | вклад) (Новая страница: «<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 (…») |
Avo (обсуждение | вклад) |
||
| (не показана одна промежуточная версия этого же участника) | |||
| Строка 522: | Строка 522: | ||
matching (doc_id,part_id,MARK_DATA,SERIES_NUMBER); | matching (doc_id,part_id,MARK_DATA,SERIES_NUMBER); | ||
| − | + | --AVO 08/10/2020 Отключено, так как есть треггер в таблице doc_detail_active где смотрится на поле marking - если маркированный ставим группу | |
| + | /*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') | 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); | + | MATCHING (GROUP_ID, PARENT_ID, GROUPTABLE_ID, GROUPTABLE);*/ |
end | end | ||
end | end | ||
Текущая версия на 17:24, 8 октября 2020
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);
--AVO 08/10/2020 Отключено, так как есть треггер в таблице doc_detail_active где смотрится на поле marking - если маркированный ставим группу
/*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 ; ^
