Текущая версия на 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