Сохранение в партиях данных об оригинальной поставке товара - История изменений

Loginovgrigoriy в 13:37, 14 ноября 2017

2017-11-14T13:37:13Z

Loginovgrigoriy: /* Выполнить скрипт */

2017-11-10T11:40:19Z

‎Выполнить скрипт

Agk: /* Выполнить скрипт */

2017-11-10T11:33:37Z

‎Выполнить скрипт

Agk: /* Вручную исправить объекты */

2017-11-10T11:19:23Z

‎Вручную исправить объекты

Agk: Новая страница: «=Выполнить скрипт=

 /*1)Добавление полей в PARTS,DOC_DETAIL_ACTIVE,DOC_DETAIL_ACTIVE_TREB + PR_NEW_PART*/ ALTER TABLE PARTS A…»

2017-11-10T11:18:57Z

Новая страница: «=Выполнить скрипт= <pre> ‎1)Добавление полей в PARTS,DOC_DETAIL_ACTIVE,DOC_DETAIL_ACTIVE_TREB + PR_NEW_PART: ALTER TABLE PARTS A…»

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

=Выполнить скрипт=

<pre>
/*1)Добавление полей в PARTS,DOC_DETAIL_ACTIVE,DOC_DETAIL_ACTIVE_TREB + PR_NEW_PART*/
ALTER TABLE PARTS ADD MOTHERPART_AGENT_ID DM_ID;
ALTER TABLE PARTS ADD MOTHERPART_SAGENT_ID DM_TEXT;
ALTER TABLE PARTS ADD MOTHERPART_DOCNUM DM_TEXT;
ALTER TABLE PARTS ADD MOTHERPART_DOCDATE DM_DATE;

ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_AGENT_ID DM_ID;
ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_SAGENT_ID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_DOCNUM DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_DOCDATE DM_DATE;

ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_AGENT_ID DM_ID;
ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_SAGENT_ID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_DOCNUM DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_DOCDATE DM_DATE;

SET TERM ^ ;

create or alter procedure PR_NEW_PART (
DOC_ID type of DM_ID,
PARENT_ID type of DM_ID,
WARE_ID type of DM_UUID_NULL,
PRICE type of DM_DOUBLE,
PRICE_O type of DM_DOUBLE,
PRICE_Z type of DM_DOUBLE,
PRICE_R type of DM_DOUBLE,
QUANT type of DM_DOUBLE,
BARCODE type of DM_TEXT,
BARCODE1 type of DM_TEXT,
DEP type of DM_ID,
GODENDO type of DM_DATETIME,
SERIA type of DM_TEXT,
NDS type of DM_DOUBLE,
SUM_NDSO type of DM_DOUBLE,
SERT type of DM_TEXT,
DATESERT type of DM_DATETIME,
KEMVSERT type of DM_TEXT,
SDSERT type of DM_DATETIME,
REGN type of DM_TEXT,
NGTD type of DM_TEXT,
EDIZM type of DM_TEXT,
NAC type of DM_DOUBLE,
MOTHERPART_ID type of DM_ID,
PART_TYPE DM_STATUS,
BASE_AGENT_ID DM_ID,
SKLAD_ID type of DM_TEXT,
CONTRACT_ID DM_ID,
DOC_DETAIL_ACTIVE_ID DM_ID,
GROUP_ID DM_ID,
MOTHERPART_UUID DM_UUID_NULL = 0,
EGAIS_ID DM_TEXT1024 = null,
EGAIS_REGID DM_TEXT1024 = null,
EGAIS_BREGID DM_TEXT1024 = null,
EGAIS_ALCCODE DM_TEXT1024 = null,
EGAIS_QUANT DM_DOUBLE = 0,
CAPACITY DM_DOUBLE = 0,
ALC_VOLUME DM_DOUBLE = 0,
EGAIS_PRODUCER_ID DM_TEXT1024 = null,
EGAIS_BARCODE DM_TEXT1024 = null,
EGAIS_TYPE DM_TEXT = null,
PRODUCER_INN DM_TEXT1024 = null,
PRODUCER_KPP DM_TEXT1024 = null,
BOTTLINGDATE DM_DATETIME = null,
EGAIS_PRODUCT_VCODE DM_TEXT = null)
returns (
P_ID type of DM_ID)
as
declare variable ORIG_SNAME DM_TEXT = 0;
declare variable ORIG_SIZG DM_TEXT = 0;
declare variable ORIG_SCOUNTRY DM_TEXT = 0;
declare variable ORIG_BCODE_IZG DM_TEXT;
declare variable MOTHERPART_AGENT_ID DM_ID;
declare variable MOTHERPART_SAGENT_ID DM_TEXT;
declare variable MOTHERPART_DOCNUM DM_TEXT;
declare variable MOTHERPART_DOCDATE DM_DATE;
begin

select iif(trim(coalesce(sorig_name, '')) = '', sname, sorig_name),
iif(trim(coalesce(sorig_izg, '')) = '', sizg, sorig_izg),
iif(trim(coalesce(sorig_country, '')) = '', scountry, sorig_country),
bcode_izg,
motherpart_agent_id,
motherpart_sagent_id,
motherpart_doc_num,
motherpart_doc_date
from doc_detail_active where id = :doc_detail_active_id
into :orig_sname, :orig_sizg, :orig_scountry, :orig_bcode_izg, :motherpart_agent_id, :motherpart_sagent_id, :motherpart_docnum, :motherpart_docdate;

--Если не указаны реквизиты материнской партии, получаем их 2017-11-10 А.К.
if (coalesce(:motherpart_agent_id,0) = 0) then
begin
if (coalesce(:motherpart_id,0) <> 0) then
select p.AGENT_ID, a.caption, d.docnum, cast(d.docdate as dm_date) from pr_getmotherpart(:motherpart_id) p
left join agents a on p.agent_id = a.id
left join docs d on p.doc_id = d.id
into :motherpart_agent_id, :motherpart_sagent_id, :motherpart_docnum, :motherpart_docdate;
else
select d.AGENT_ID, a.caption, d.docnum, cast(d.docdate as dm_date) from docs d
left join agents a on d.agent_id = a.id
into :motherpart_agent_id, :motherpart_sagent_id, :motherpart_docnum, :motherpart_docdate;
end

p_id=gen_id(gen_parts_id,1);
insert into parts
(ID,parent_id,doc_id,WARE_ID,PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE,BARCODE1,DEP,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,
DATESERT,KEMVSERT,SDSERT,REGN,NGTD,
EDIZM,NAC,motherpart_id,sklad_id,part_type,BASE_AGENT_ID,contract_id, doc_detail_active_id, group_id, motherpart_uuid,
EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, EGAIS_QUANT, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_BARCODE,
EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE, ORIG_SNAME, ORIG_SIZG, ORIG_SCOUNTRY,ORIG_BCODE_IZG,
MOTHERPART_AGENT_ID, MOTHERPART_SAGENT_ID, MOTHERPART_DOCNUM, MOTHERPART_DOCDATE)
values
(:p_id,:parent_id,:doc_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,
:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,
:SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id,:sklad_id,:part_type,:BASE_AGENT_ID,:contract_id,:doc_detail_active_id, :group_id, :motherpart_uuid,
:EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_BARCODE,
:EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE, :ORIG_SNAME, :ORIG_SIZG, :ORIG_SCOUNTRY, :ORIG_BCODE_IZG,
:MOTHERPART_AGENT_ID, :MOTHERPART_SAGENT_ID, :MOTHERPART_DOCNUM, :MOTHERPART_DOCDATE);
suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_NEW_PART;
GRANT EXECUTE ON PROCEDURE PR_GETMOTHERPART TO PROCEDURE PR_NEW_PART;
GRANT SELECT ON AGENTS TO PROCEDURE PR_NEW_PART;
GRANT SELECT ON DOCS TO PROCEDURE PR_NEW_PART;
GRANT INSERT ON PARTS TO PROCEDURE PR_NEW_PART;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA;
</pre>

=Вручную исправить объекты=
==/* DOC_DETAIL_ACTIVE_BI где идет выборка from parts добавить поля*/==
==/* в DOC_DETAIL_ACTIVE_TREB_BI0 где идет вставка update or insert into doc_detail_active*/==
==/*в GM$PR_PRIHOD_DOC_TREB где идет вставка в DOC_DETAIL_ACTIVE_TREB */==
<pre>
MOTHERPART_AGENT_ID,
MOTHERPART_SAGENT_ID,
MOTHERPART_DOCNUM,
MOTHERPART_DOCDATE

в

new.MOTHERPART_AGENT_ID,
new.MOTHERPART_SAGENT_ID,
new.MOTHERPART_DOCNUM,
new.MOTHERPART_DOCDATE
</pre>

@@ Строка 3: / Строка 3: @@
 <pre>
 /*1)Добавление полей в PARTS,DOC_DETAIL_ACTIVE,DOC_DETAIL_ACTIVE_TREB + PR_NEW_PART*/
-ALTER TABLE PARTS ADD MOTHERPART_AGENT_ID DM_ID;
+ALTER TABLE PARTS ADD MOTHERPART_AGENT_ID DM_ID_NULL;
 ALTER TABLE PARTS ADD MOTHERPART_SAGENT_ID DM_TEXT;
 ALTER TABLE PARTS ADD MOTHERPART_DOCNUM DM_TEXT;
 ALTER TABLE PARTS ADD MOTHERPART_DOCDATE DM_DATE;
-ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_AGENT_ID DM_ID;
+ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_AGENT_ID DM_ID_NULL;
 ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_SAGENT_ID DM_TEXT;
 ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_DOCNUM DM_TEXT;
 ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_DOCDATE DM_DATE;
-ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_AGENT_ID DM_ID;
+ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_AGENT_ID DM_ID_NULL;
 ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_SAGENT_ID DM_TEXT;
 ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_DOCNUM DM_TEXT;

@@ Строка 102: / Строка 102: @@
          select d.AGENT_ID, a.caption, d.docnum, cast(d.docdate as dm_date) from docs d
           left join agents a on d.agent_id = a.id
          into :motherpart_agent_id, :motherpart_sagent_id, :motherpart_docnum, :motherpart_docdate;
    end

@@ Строка 86: / Строка 86: @@
    motherpart_agent_id,
    motherpart_sagent_id,
-   motherpart_doc_num,
+   motherpart_docnum,
-   motherpart_doc_date
+   motherpart_docdate
      from doc_detail_active where id = :doc_detail_active_id
    into :orig_sname, :orig_sizg, :orig_scountry, :orig_bcode_izg, :motherpart_agent_id, :motherpart_sagent_id, :motherpart_docnum, :motherpart_docdate;
@@ Строка 141: / Строка 141: @@
 GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA;
 </pre>
 =Вручную исправить объекты=

@@ Строка 144: / Строка 144: @@
 =Вручную исправить объекты=
-==/* DOC_DETAIL_ACTIVE_BI где идет выборка from parts добавить поля*/==
+==DOC_DETAIL_ACTIVE_BI где идет выборка from parts добавить поля==
-==/* в DOC_DETAIL_ACTIVE_TREB_BI0 где идет вставка update or insert into doc_detail_active*/==
+==в DOC_DETAIL_ACTIVE_TREB_BI0 где идет вставка update or insert into doc_detail_active==
-==/*в GM$PR_PRIHOD_DOC_TREB где идет вставка в DOC_DETAIL_ACTIVE_TREB */==
+==в GM$PR_PRIHOD_DOC_TREB где идет вставка в DOC_DETAIL_ACTIVE_TREB==
 <pre>
 MOTHERPART_AGENT_ID,