Agk в 13:36, 18 марта 2021

2021-03-18T13:36:03Z

Avo в 04:53, 15 декабря 2020

2020-12-15T04:53:32Z

Avo в 09:26, 26 ноября 2020

2020-11-26T09:26:09Z

Avo: Новая страница: «

 SET TERM ^ ;  create or alter procedure PR_DOC_DETAIL_INSERT (     PARENT_ID bigint,     DOC_ID bigint,     PART_ID bigint,     QUANT type of DM_DOUBLE,…»

2020-11-26T09:25:37Z

Новая страница: «<pre> SET TERM ^ ; create or alter procedure PR_DOC_DETAIL_INSERT ( PARENT_ID bigint, DOC_ID bigint, PART_ID bigint, QUANT type of DM_DOUBLE,…»

Новая страница

<pre>
SET TERM ^ ;

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

SET TERM ; ^

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

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

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

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

SET TERM ^ ;

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

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

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

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

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

SET TERM ; ^

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

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

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

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

SET TERM ^ ;

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

SET TERM ; ^

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

GRANT SELECT ON DOCS TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE GM$PR_PRIHOD_DOC_TREB TO PROCEDURE PR_DOC_RASHOD_COMMIT;

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

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

</pre>

@@ Строка 12: / Строка 12: @@
    if ((select D.DOC_TYPE
         from DOCS D
-        where D.ID = :DOC_ID) <> 8) then
+        where D.ID = :DOC_ID) not in (8,58)) then
    begin
      update MARK_DETAIL

← Предыдущая		Версия 09:26, 26 ноября 2020
Строка 1:		Строка 1:
	<pre>		<pre>
		+	SET TERM ^ ;
		+
		+	create or alter procedure PR_UPDATEMARKBYPART (
		+	DOC_ID DM_ID,
		+	PART_ID DM_ID,
		+	ACTIVE_ID DM_ID)
		+	as
		+	begin
		+	--Если не переоценка то
		+	--Обновляем партию привязанную к SGTIN
		+	if ((select D.DOC_TYPE
		+	from DOCS D
		+	where D.ID = :DOC_ID) <> 8) then
		+	begin
		+	update MARK_DETAIL
		+	set PART_ID = :PART_ID
		+	where DDA_ID = :ACTIVE_ID;
		+	end
		+	else
		+	-- При переоценки вставляем записи в mark_detail
		+	begin
		+	insert into MARK_DETAIL (DOC_ID, PART_ID, MARK_DATA, QUANT, DOCNUM, DOC_DATE, DDA_ID, PLACE_ID_IN, PLACE_ID_OUT,
		+	GTIN, SERIES_NUMBER, EXPIRATION_DATE, ACTIVEPOS, SERIA, KIZ, SSCC, ACCEPTED, MDLP_STATUS,
		+	ERRORTEXT)
		+	select :DOC_ID, :PART_ID, MARK_DATA, -QUANT, DOCNUM, DOC_DATE, DDA_ID, PLACE_ID_IN, PLACE_ID_OUT, GTIN,
		+	SERIES_NUMBER, EXPIRATION_DATE, 1, SERIA, KIZ, SSCC, ACCEPTED, 1, ''
		+	from MARK_DETAIL MD
		+	where MD.DDA_ID = :ACTIVE_ID;
		+	end
		+	suspend;
		+	end^
		+
		+	SET TERM ; ^
		+
		+	/* Следующие операторы GRANT сгенерированы автоматически */
		+
		+	GRANT SELECT ON DOCS TO PROCEDURE PR_UPDATEMARKBYPART;
		+	GRANT SELECT,INSERT,UPDATE ON MARK_DETAIL TO PROCEDURE PR_UPDATEMARKBYPART;
		+
		+	/* Существующие привилегии на эту процедуру */
		+
		+	GRANT EXECUTE ON PROCEDURE PR_UPDATEMARKBYPART TO SYSDBA;
		+
	SET TERM ^ ;		SET TERM ^ ;

Обновление процедур проведения документа - маркировка - История изменений

Agk в 13:36, 18 марта 2021

Avo в 04:53, 15 декабря 2020

Avo в 09:26, 26 ноября 2020

Avo: Новая страница: «
SET TERM ^ ; create or alter procedure PR_DOC_DETAIL_INSERT ( PARENT_ID bigint, DOC_ID bigint, PART_ID bigint, QUANT type of DM_DOUBLE,…»