Сохранение в партиях данных об оригинальной поставке товара — различия между версиями
Материал из wiki.standart-n.ru
Agk (обсуждение | вклад) (Новая страница: «=Выполнить скрипт= <pre> →1)Добавление полей в PARTS,DOC_DETAIL_ACTIVE,DOC_DETAIL_ACTIVE_TREB + PR_NEW_PART: ALTER TABLE PARTS A…») |
|||
(не показаны 3 промежуточные версии 2 участников) | |||
Строка 3: | Строка 3: | ||
<pre> | <pre> | ||
/*1)Добавление полей в PARTS,DOC_DETAIL_ACTIVE,DOC_DETAIL_ACTIVE_TREB + PR_NEW_PART*/ | /*1)Добавление полей в PARTS,DOC_DETAIL_ACTIVE,DOC_DETAIL_ACTIVE_TREB + PR_NEW_PART*/ | ||
− | ALTER TABLE PARTS ADD MOTHERPART_AGENT_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_SAGENT_ID DM_TEXT; | ||
ALTER TABLE PARTS ADD MOTHERPART_DOCNUM DM_TEXT; | ALTER TABLE PARTS ADD MOTHERPART_DOCNUM DM_TEXT; | ||
ALTER TABLE PARTS ADD MOTHERPART_DOCDATE DM_DATE; | ALTER TABLE PARTS ADD MOTHERPART_DOCDATE DM_DATE; | ||
− | ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_AGENT_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_SAGENT_ID DM_TEXT; | ||
ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_DOCNUM 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 ADD MOTHERPART_DOCDATE DM_DATE; | ||
− | ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_AGENT_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_SAGENT_ID DM_TEXT; | ||
ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_DOCNUM DM_TEXT; | ALTER TABLE DOC_DETAIL_ACTIVE_TREB ADD MOTHERPART_DOCNUM DM_TEXT; | ||
Строка 86: | Строка 86: | ||
motherpart_agent_id, | motherpart_agent_id, | ||
motherpart_sagent_id, | motherpart_sagent_id, | ||
− | + | motherpart_docnum, | |
− | + | motherpart_docdate | |
from doc_detail_active where id = :doc_detail_active_id | 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; | into :orig_sname, :orig_sizg, :orig_scountry, :orig_bcode_izg, :motherpart_agent_id, :motherpart_sagent_id, :motherpart_docnum, :motherpart_docdate; | ||
Строка 102: | Строка 102: | ||
select d.AGENT_ID, a.caption, d.docnum, cast(d.docdate as dm_date) from docs d | 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 | left join agents a on d.agent_id = a.id | ||
+ | where d.id = :doc_id | ||
into :motherpart_agent_id, :motherpart_sagent_id, :motherpart_docnum, :motherpart_docdate; | into :motherpart_agent_id, :motherpart_sagent_id, :motherpart_docnum, :motherpart_docdate; | ||
end | end | ||
Строка 141: | Строка 142: | ||
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA; | GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA; | ||
</pre> | </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> | <pre> | ||
MOTHERPART_AGENT_ID, | MOTHERPART_AGENT_ID, |
Текущая версия на 18:37, 14 ноября 2017
Содержание
Выполнить скрипт
/*1)Добавление полей в PARTS,DOC_DETAIL_ACTIVE,DOC_DETAIL_ACTIVE_TREB + PR_NEW_PART*/ 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_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_NULL; 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_docnum, 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; --Если не указаны реквизиты материнской партии, получаем их 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 where d.id = :doc_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;
Вручную исправить объекты
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
MOTHERPART_AGENT_ID, MOTHERPART_SAGENT_ID, MOTHERPART_DOCNUM, MOTHERPART_DOCDATE в new.MOTHERPART_AGENT_ID, new.MOTHERPART_SAGENT_ID, new.MOTHERPART_DOCNUM, new.MOTHERPART_DOCDATE