Установка Единого Окна — различия между версиями

Версия 15:17, 14 июля 2016

Лешины скрипты

create or alter procedure PR_AUTO_WARESLINK (
    DOC_ID DM_ID_NULL,
    ACTIVE_ID DM_ID_NULL)
as
declare variable DDA_BARCODE DM_TEXT1024;
declare variable DDA_WARE_ID DM_UUID_NULL;
declare variable DDA_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDA_ORIG_IZGID DM_UUID_NULL;
declare variable DDA_ORIG_NAMEID DM_UUID_NULL;
declare variable DDA_COUNTRY_ID DM_UUID_NULL;
declare variable DDA_IZG_ID DM_UUID_NULL;
declare variable DDA_NAME_ID DM_UUID_NULL;
declare variable DDA_Z_ID DM_UUID_NULL;
declare variable DDA_SKLAD_ID DM_UUID_NULL;
declare variable DDALOG_BARCODE DM_TEXT1024;
declare variable DDALOG_WARE_ID DM_UUID_NULL;
declare variable DDALOG_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDALOG_ORIG_IZGID DM_UUID_NULL;
declare variable DDALOG_ORIG_NAMEID DM_UUID_NULL;
declare variable DDALOG_COUNTRY_ID DM_UUID_NULL;
declare variable DDALOG_IZG_ID DM_UUID_NULL;
declare variable DDALOG_NAME_ID DM_UUID_NULL;
declare variable DDALOG_Z_ID DM_UUID_NULL;
declare variable DDALOG_SKLAD_ID DM_UUID_NULL;
declare variable DDALOG2_BARCODE DM_TEXT1024;
declare variable DDALOG2_WARE_ID DM_UUID_NULL;
declare variable DDALOG2_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDALOG2_ORIG_IZGID DM_UUID_NULL;
declare variable DDALOG2_ORIG_NAMEID DM_UUID_NULL;
declare variable DDALOG2_COUNTRY_ID DM_UUID_NULL;
declare variable DDALOG2_IZG_ID DM_UUID_NULL;
declare variable DDALOG2_NAME_ID DM_UUID_NULL;
declare variable DDALOG2_Z_ID DM_UUID_NULL;
declare variable DDALOG2_SKLAD_ID DM_UUID_NULL;
begin

 select bcode_izg, ware_id,
   (select val_id from PR_GETVAL_ID(dda.sname, 0, dda.part_type)) as name_id,
   (select val_id from PR_GETVAL_ID(dda.sizg, 3, dda.part_type)) as izg_id,
   (select val_id from PR_GETVAL_ID(dda.scountry, 2, dda.part_type)) as country_id,
   (select val_id from PR_GETVAL_ID(dda.sorig_name, 1, dda.part_type)) as orig_name_id,
   (select val_id from PR_GETVAL_ID(dda.sorig_izg, 6, dda.part_type)) as orgi_izg_id,
   (select val_id from PR_GETVAL_ID(dda.sorig_country, 5, dda.part_type)) as orig_country_id,
   Z_ID, SKLAD_ID
 from doc_detail_active dda where doc_id = :doc_id and id = :active_id
 into dda_barcode, dda_ware_id, dda_name_id, dda_izg_id, dda_country_id, dda_orig_nameid, dda_orig_izgid, dda_orig_countryid, dda_z_id, dda_sklad_id;

 select first 1 bcode_izg, ware_id,
   (select val_id from PR_GETVAL_ID(ddal.sname, 0, ddal.part_type)) as name_id,
   (select val_id from PR_GETVAL_ID(ddal.sizg, 3, ddal.part_type)) as izg_id,
   (select val_id from PR_GETVAL_ID(ddal.scountry, 2, ddal.part_type)) as country_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_name, 1, ddal.part_type)) as orig_name_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_izg, 6, ddal.part_type)) as orgi_izg_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_country, 5, ddal.part_type)) as orig_country_id,
   Z_ID, SKLAD_ID
  from doc_detail_active_log ddal where doc_id = :doc_id and id = :active_id order by log_id
  into ddalog_barcode, ddalog_ware_id, ddalog_name_id, ddalog_izg_id, ddalog_country_id, ddalog_orig_nameid, ddalog_orig_izgid, ddalog_orig_countryid, ddalog_z_id, ddalog_sklad_id;

  select first 1 skip 1 bcode_izg, ware_id,
   (select val_id from PR_GETVAL_ID(ddal.sname, 0, ddal.part_type)) as name_id,
   (select val_id from PR_GETVAL_ID(ddal.sizg, 3, ddal.part_type)) as izg_id,
   (select val_id from PR_GETVAL_ID(ddal.scountry, 2, ddal.part_type)) as country_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_name, 1, ddal.part_type)) as orig_name_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_izg, 6, ddal.part_type)) as orgi_izg_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_country, 5, ddal.part_type)) as orig_country_id,
   Z_ID, SKLAD_ID
  from doc_detail_active_log ddal where doc_id = :doc_id and id = :active_id order by log_id
  into ddalog2_barcode, ddalog2_ware_id, ddalog2_name_id, ddalog2_izg_id, ddalog2_country_id, ddalog2_orig_nameid, ddalog2_orig_izgid, ddalog2_orig_countryid, ddalog2_z_id, ddalog2_sklad_id;

 if ( ((dda_ware_id <> '-1') and (dda_ware_id <> '0') and (dda_ware_id is not null)) and
    ((ddalog2_ware_id <> '-1') and (ddalog2_ware_id <> '0') and (ddalog2_ware_id is not null))
    and (dda_barcode = ddalog2_barcode)
    and (dda_ware_id <> ddalog2_ware_id) )
 then
 begin
   update wares set name_id = :dda_name_id, izg_id = :dda_izg_id, country_id = :dda_country_id, orig_name_id = :dda_orig_nameid,
    orig_izg_id = :dda_orig_izgid, orig_country_id = :dda_orig_countryid, barcode = :dda_barcode where id = :ddalog2_ware_id;
   exit;
 end

 if ( (ddalog_ware_id = '0') or (ddalog_ware_id = '-1') or (ddalog_ware_id is null) ) then
 begin
   insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE, Z_ID, SKLAD_ID)
   values (:dda_ware_id, :ddalog_name_id, :ddalog_izg_id, :ddalog_country_id, :ddalog_orig_nameid, :ddalog_orig_izgid,
    :ddalog_orig_countryid, :ddalog_barcode, :ddalog_z_id, :ddalog_sklad_id);

 end
 else if ( ((dda_ware_id <> '0') and (dda_ware_id <> '-1') and (dda_ware_id is not null)) and
  ((dda_name_id <> ddalog_name_id) or (dda_izg_id <> ddalog_izg_id) or (dda_country_id <> ddalog_country_id) or (dda_orig_nameid <> ddalog_orig_nameid)
  or (dda_orig_izgid <> ddalog_orig_izgid) or (dda_orig_countryid <> ddalog_orig_countryid))
  and (dda_barcode = ddalog_barcode)
  )  then
 begin
   update wares set name_id = :dda_name_id, izg_id = :dda_izg_id, country_id = :dda_country_id, orig_name_id = :dda_orig_nameid,
    orig_izg_id = :dda_orig_izgid, orig_country_id = :dda_orig_countryid, barcode = :dda_barcode where id = :ddalog_ware_id;
 end

end;

GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_AUTO_WARESLINK;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_AUTO_WARESLINK;
GRANT SELECT ON DOC_DETAIL_ACTIVE_LOG TO PROCEDURE PR_AUTO_WARESLINK;
GRANT INSERT ON WARES_LOG TO PROCEDURE PR_AUTO_WARESLINK;
GRANT SELECT,UPDATE ON WARES TO PROCEDURE PR_AUTO_WARESLINK;
GRANT EXECUTE ON PROCEDURE PR_AUTO_WARESLINK TO SYSDBA;


create or alter procedure PR_MAKEGOODWAREVALUES (
    SNAME type of DM_TEXT,
    SORIG_NAME type of DM_TEXT,
    SIZG type of DM_TEXT,
    SORIG_IZG type of DM_TEXT,
    SCOUNTRY type of DM_TEXT,
    SORIG_COUNTRY type of DM_TEXT,
    ORIG_CODE type of DM_TEXT,
    ACTIVE_ID type of DM_ID,
    ALTTYPE DM_STATUS,
    BARCODE DM_TEXT1024 = '')
returns (
    GOODNAME type of DM_TEXT,
    GOODIZG type of DM_TEXT,
    GOODCOUNTRY type of DM_TEXT,
    GOODBARCODE DM_TEXT)
as
declare variable WARE_ID type of DM_UUID_NULL;
declare variable NAME_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
begin

 /*
  select val_id from pr_getval_id(:sname,0,:alttype) into :name_id;
  select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
  select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
  select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
  select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
  select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
 */

  select id from vals where vtype=0 and alttype=:alttype and svalue=:sname into :name_id;
  select id from vals where vtype=1 and alttype=:alttype and svalue=:sorig_name into :orig_name_id;
  select id from vals where vtype=3 and alttype=:alttype and svalue=:sizg into :izg_id;
  select id from vals where vtype=6 and alttype=:alttype and svalue=:sorig_izg into :orig_izg_id;
  select id from vals where vtype=2 and alttype=:alttype and svalue=:scountry into :country_id;
  select id from vals where vtype=5 and alttype=:alttype and svalue=:sorig_country into :orig_country_id;


  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,1, :barcode) into :ware_id;
  if (ware_id is not null) then
  begin
  /* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
    select sname, sizg, scountry, barcode from VW_WARES where id=:ware_id into :goodname, :goodizg, :goodcountry, :goodbarcode;
  /* if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then */
  /* insert into doc_detail_active_log(id,sname,sizg,scountry,part_type) values (:active_id,:sname,:sizg,:scountry,:alttype); */
  /* goodname='2';--sname; */
  if (goodname is null)   then goodname = sname;
  if (goodizg is null)  then goodizg = sizg;
  if (goodcountry is null)  then goodcountry = scountry;
  if (goodname is null)   then goodname = sorig_name;
  if (goodizg is null)  then goodizg = sorig_izg;
  if (goodcountry is null)  then goodcountry = sorig_country;
  if (goodbarcode is null)  then goodbarcode = barcode;
    suspend;
    exit;
  end 

  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,null, :barcode) into :ware_id;
  if (ware_id is not null) then
  begin
    /* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
    /* goodname='1';--sname; */
    goodname=sname;
    goodizg=sizg;
    goodcountry=scountry;
    goodbarcode=barcode;

    suspend;
    exit;
  end 


  ware_id=null;
  if (orig_name_id is not null) then
  begin
    select first 1 id from wares where orig_name_id=:orig_name_id order by id desc into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_name_id=:orig_name_id order by log_id desc into :ware_id;
  end
  if ((ware_id is null) and (name_id is not null)) then
  begin
    select first 1 id from wares where name_id=:name_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where name_id=:name_id order by log_id desc into :ware_id;
  end
  if (ware_id is null) then
  begin
    /* goodname='3';--sname; */
    goodname=sname;
    name_id=null;
  end
  else
  begin
    /* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
    select w.name_id, v.svalue from WARES w left join vals v on w.name_id=v.id where w.id=:ware_id into :name_id, :goodname;
  end


  ware_id=null;
  if (orig_izg_id is not null) then
  begin
    select first 1 id from wares where orig_izg_id=:orig_izg_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_izg_id=:orig_izg_id order by log_id desc into :ware_id;
  end
  if ((ware_id is null) and (izg_id is not null)) then
  begin
    select first 1 id from wares where izg_id=:izg_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where izg_id=:izg_id order by log_id desc into :ware_id;
  end
  if (ware_id is null) then
    goodizg=sizg;
  else
  begin
    /* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
    select w.izg_id, v.svalue from WARES w left join vals v on w.izg_id=v.id where w.id=:ware_id into :izg_id, :goodizg;
  end


  ware_id=null;
  if (orig_country_id is not null) then
  begin
    select first 1 id from wares where orig_country_id=:orig_country_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_country_id=:orig_country_id order by log_id desc into :ware_id;
  end
  if ((ware_id is null) and (country_id is not null)) then
  begin
    select first 1 id from wares where country_id=:country_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where country_id=:country_id order by log_id desc into :ware_id;
  end
  if (ware_id is null) then
    goodcountry=scountry;
  else
  begin
    /* select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id; */
    select w.country_id, v.svalue from WARES w left join vals v on w.country_id=v.id where w.id=:ware_id into :country_id, :goodcountry;
  end

 /* if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then */
 /*   insert into doc_detail_active_log(id,sname,sizg,scountry, part_type) values (:active_id,:sname,:sizg,:scountry,:alttype); */

  /* goodname='3'; */
  goodbarcode=barcode;

  if (goodname is null)   then goodname = sname;
  if (goodizg is null)  then goodizg = sizg;
  if (goodcountry is null)  then goodcountry = scountry;
  if (goodname is null)   then goodname = sorig_name;
  if (goodizg is null)  then goodizg = sorig_izg;
  if (goodcountry is null)  then goodcountry = sorig_country;

  suspend;
end;


create or alter procedure PR_GET_WARE (
    SNAME type of DM_TEXT,
    SIZG type of DM_TEXT,
    SCOUNTRY type of DM_TEXT,
    ORIG_CODE type of DM_TEXT = '',
    SORIG_NAME type of DM_TEXT = '',
    SORIG_IZG type of DM_TEXT = '',
    SORIG_COUNTRY type of DM_TEXT = '',
    BARCODE type of DM_TEXT = '',
    Z_ID type of DM_ID = 0,
    SKLAD_ID DM_TEXT = '',
    ALTTYPE DM_STATUS = 0,
    MNN DM_TEXT = '')
returns (
    W_ID type of DM_UUID_NULL)
as
declare variable NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
begin

  select VAL_ID from PR_GETVAL_ID(:SNAME, 0, :ALTTYPE, :MNN) into :NAME_ID;
  select VAL_ID from PR_GETVAL_ID(:SIZG, 3, :ALTTYPE) into :IZG_ID;
  select VAL_ID from PR_GETVAL_ID(:SCOUNTRY, 2, :ALTTYPE) into :COUNTRY_ID;
  select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
  select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
  select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
  /*
  select first 1 id from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
  ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
  ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id -- into :w_id;
  and barcode=:barcode into :w_id;
  */
  select ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and BARCODE = :BARCODE into :W_ID;

  if (W_ID is null) then
  begin
    /* exception EX_WRONG_OPER; */
    /* w_id=gen_id(gen_wares_id,1); */
    W_ID = uuid_to_char(gen_uuid());
    insert into WARES (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE, Z_ID, SKLAD_ID)
    values (:W_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, :BARCODE, :Z_ID, :SKLAD_ID);
  end
  suspend;
end;


create or alter procedure PR_GETWARE_BY_IDS (
    NAME_ID type of DM_UUID_NULL,
    IZG_ID type of DM_UUID_NULL,
    COUNTRY_ID type of DM_UUID_NULL,
    ORIG_CODE type of DM_TEXT,
    ORIG_NAME_ID type of DM_UUID_NULL,
    ORIG_IZG_ID type of DM_UUID_NULL,
    ORIG_COUNTRY_ID type of DM_UUID_NULL,
    DOINSERT type of DM_STATUS,
    SEARCHINLOGS type of DM_STATUS,
    BARCODE DM_TEXT)
returns (
    WARE_ID type of DM_UUID_NULL)
as
begin
  if (orig_code is null) then orig_code='';
  if (searchinlogs is null) then searchinlogs=0;
  if (searchinlogs=1) then
  begin 
    select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode
	order by log_insertdt desc into :ware_id;
	select id from wares where id = :ware_id into :ware_id;
  end
  else
  begin
    select first 1 id from wares where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode
    into :ware_id;
  end
  if ((ware_id is null) and (DOINSERT=1) and (searchinlogs<>1)) then
  begin
    /* ware_id=gen_id(gen_wares_id,1); */
    ware_id=UUID_TO_CHAR(GEN_UUID());
    insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,Z_ID,SKLAD_ID, barcode)
     values
       (:ware_id,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,0,'',:barcode);
  end
  suspend;
end;


create or alter procedure PR_DOC_COMMIT (
    DOC_ID type of DM_ID,
    SESSION_ID type of DM_ID)
as
declare variable WRONG_WARES DM_STATUS;
declare variable BASE_TYPE type of DM_STATUS;
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 DSCSUMMA type of DM_DOUBLE;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable PRICE_TYPE DM_STATUS;
declare variable CONTRACT_ID DM_ID;
declare variable W_PARAM DM_STATUS;
declare variable WARES_MODE DM_STATUS;
begin
  select DT.BASE_TYPE, D.PRICE_TYPE, D.CONTRACT_ID from DOCS D
  left join DOC_TYPES DT on D.DOC_TYPE = DT.ID where D.ID = :DOC_ID into :BASE_TYPE, :PRICE_TYPE, :CONTRACT_ID;
  if (BASE_TYPE in (1, 3, 6, 8)) then
  begin

    wrong_wares = 0; wares_mode = 1;
    select PARAM_VALUE from PR_GETPARAMVALUE('WARES_MODE', 0) into wares_mode;
    if (wares_mode = 0) then
    begin
      select count(id) from doc_detail_active where doc_id = :doc_id and ((ware_id = '0') or (ware_id = '-1') or (ware_id = '')) into wrong_wares;
      if (wrong_wares > 0) then exception EX_WRONG_OPER;
    end

    execute procedure PR_DOC_PRIHOD_COMMIT(:DOC_ID, 0);
  end
  else
  if (BASE_TYPE in (2, 7, 9)) then
    execute procedure PR_DOC_RASHOD_COMMIT(:DOC_ID, 0);
  else
    exception EX_WRONGDOC_BASE_TYPE;
  delete from DOC_DETAIL_ACTIVE where DOC_ID = :DOC_ID;
  if (BASE_TYPE = 3) then
  begin
    delete from WAREBASE where PART_ID in (select PART_ID from DOC_DETAIL where DOC_ID = :DOC_ID and QUANT < 0 and PART_TYPE = 1);
  end
  select sum(SUMMA), sum(SUMMA_O), sum(SUM_NDSO), sum(PRICE * QUANT / 10000), sum(SUM_DSC) from DOC_DETAIL where DOC_ID = :DOC_ID into :SUMMA,
       :SUMMA_O, :SUM_NDSO, :DSCSUMMA, :SUM_DSC;
  DSCSUMMA = DSCSUMMA * 10000;
  update DOCS set COMMITSESSION_ID = :SESSION_ID, STATUS = 1, POSTDT = 'now',
                  CHECKDATA = (select CHECKDATA from PR_DOCCHECKDATA(:DOC_ID)), SUMMA = :SUMMA, SUMMA_O = :SUMMA_O,
                  SUM_NDSO = :SUM_NDSO, CALCSUMMA = :DSCSUMMA, SUM_DSC = :SUM_DSC, CASHDATA = (iif(:BASE_TYPE = 1, -:SUMMA, null)) where ID = :DOC_ID;
  execute procedure PR_UPDBLOCKINFO_BY_DOC(:DOC_ID, 1);
  /* if ((base_type = 2) and (contract_id<>0)) then */
  if (CONTRACT_ID <> 0) then
  begin
    if (BASE_TYPE = 2) then
      execute procedure PR_CREDITCOMMIT(:CONTRACT_ID);
    if (BASE_TYPE in (8, 9)) then
      execute procedure PR_CREDITREINITCOMMIT(:CONTRACT_ID, :SESSION_ID);
    /* execute procedure PR_CREDITCOMMIT(:contract_id); */
  end
  execute procedure PR_DOEVENT('EV_DOCCOMMIT');
end;


CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.ID is null) then
    new.ID = gen_id(GEN_DOC_DETAIL_ACTIVE_ID, 1);
  new.INSERTDT = 'now';
  if (new.BASE_AGENT_ID is null) then
    new.BASE_AGENT_ID = 0;
  if (new.STATUS is null) then
    new.STATUS = 0;
  if (new.PARENT_ID is null) then
    new.PARENT_ID = 0;
  if (new.PART_TYPE is null) then
    new.PART_TYPE = 0;


  /* первая строчка =  до привидения, оригианльные значения накладной */
  /* select first 1 ID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BCODE_IZG into new.WARE_ID; */

  insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
                                     DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
                                     BARCODE1, BCODE_IZG,
                                     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, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
                                     PART_TYPE, BASE_AGENT_ID)
  values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC,
          new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z,
          new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG,
          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.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_ID);
  /* -- */

  if ((new.PART_ID is null) or (new.PART_ID = 0)) then
  begin
    /* >cf20110923 */
    /* if (new.ware_id<0) then */
    if (new.WARE_ID = '-1') then
    begin
      select GOODNAME, GOODIZG, GOODCOUNTRY, GOODBARCODE from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.bcode_izg)
      into new.SNAME, new.SIZG, new.SCOUNTRY, new.BCODE_IZG;
    end
    /* <cf20110923 */
    new.PART_ID = 0;
    select ID from VALS where VTYPE = 0 and ALTTYPE = new.PART_TYPE and SVALUE = new.SNAME into new.NAME_ID;
    select ID from VALS where VTYPE = 3 and ALTTYPE = new.PART_TYPE and SVALUE = new.SIZG into new.IZG_ID;
    select ID from VALS where VTYPE = 2 and ALTTYPE = new.PART_TYPE and SVALUE = new.SCOUNTRY into new.COUNTRY_ID;
    select ID from VALS where VTYPE = 1 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_NAME into new.ORIG_NAME_ID;
    select ID from VALS where VTYPE = 6 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_IZG into new.ORIG_IZG_ID;
    select ID from VALS where VTYPE = 5 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_COUNTRY into new.ORIG_COUNTRY_ID;
    new.WARE_ID = 0;
    select first 1 ID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BCODE_IZG into new.WARE_ID;

    /* вторая строчка = после приведения */
    insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
                                     DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
                                     BARCODE1, BCODE_IZG,
                                     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, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
                                     PART_TYPE, BASE_AGENT_ID)
    values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC,
          new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z,
          new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG,
          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.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_ID);
    /* -- */

  end
  if (new.PART_PARENT_ID is null) then
    new.PART_PARENT_ID = 0;
  if (new.WARE_ID is null) then
    new.WARE_ID = 0;
  if (new.NAME_ID is null) then
    new.NAME_ID = 0;
  if (new.IZG_ID is null) then
    new.IZG_ID = 0;
  if (new.COUNTRY_ID is null) then
    new.COUNTRY_ID = 0;
  if (new.ORIG_NAME_ID is null) then
    new.ORIG_NAME_ID = 0;
  if (new.ORIG_IZG_ID is null) then
    new.ORIG_IZG_ID = 0;
  if (new.ORIG_COUNTRY_ID is null) then
    new.ORIG_COUNTRY_ID = 0;
  if (new.Z_ID is null) then
    new.Z_ID = 0;
  if (new.SKLAD_ID is null) then
    new.SKLAD_ID = '';
  if (new.MOTHERPART_ID is null) then
    new.MOTHERPART_ID = 0;
  if (new.DOC_DETAIL_ID is null) then
    new.DOC_DETAIL_ID = 0;

  if (new.PART_ID > 0) then /* заполняем значения партии */
  begin
    select WARE_ID, PRICE, PRICE_O, PRICE_Z, PRICE_R,
           /* QUANT, */
           /* BARCODE, */
           BARCODE1,
           /* KRITK, */
           GODENDO, SERIA, NDS,
           /* SUM_NDSO, */
           SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, NAC, DEP, PART_TYPE, BASE_AGENT_ID, GROUP_ID from PARTS where ID = new.PART_ID into new.WARE_ID,
         new.PRICE, new.PRICE_O, new.PRICE_Z, new.PRICE_R,
         /* new.QUANT, */
         /* new.BARCODE, */
         new.BARCODE1,
         /* new.KRITK,*/
         new.GODENDO, new.SERIA, new.NDS,
         /* new.SUM_NDSO, */
         new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN, new.NGTD, new.EDIZM, new.NAC, new.DEP,
         new.PART_TYPE, new.BASE_AGENT_ID, new.GROUP_ID;
  end
  if (new.GROUP_ID is null) then
    new.GROUP_ID = 0;
  /* if (new.ware_id>0) then -- заполняем значения позиции */
  if (new.WARE_ID not in ('-1', '0')) then /* заполняем значения позиции */
  begin
    select WW.NAME_ID,
           (select SVALUE from VALS where ID = WW.NAME_ID), WW.IZG_ID,
           (select SVALUE from VALS where ID = WW.IZG_ID), WW.COUNTRY_ID,
           (select SVALUE from VALS where ID = WW.COUNTRY_ID), WW.ORIG_CODE, WW.ORIG_NAME_ID,
           (select SVALUE from VALS where ID = WW.ORIG_NAME_ID), WW.ORIG_IZG_ID,
           (select SVALUE from VALS where ID = WW.ORIG_IZG_ID), WW.ORIG_COUNTRY_ID,
           (select SVALUE from VALS where ID = WW.ORIG_COUNTRY_ID), WW.BARCODE, WW.Z_ID, WW.SKLAD_ID from WARES WW where WW.ID = new.WARE_ID into new.NAME_ID,
         new.SNAME, new.IZG_ID, new.SIZG, new.COUNTRY_ID, new.SCOUNTRY, new.ORIG_CODE, new.ORIG_NAME_ID, new.SORIG_NAME,
         new.ORIG_IZG_ID, new.SORIG_IZG, new.ORIG_COUNTRY_ID, new.SORIG_COUNTRY, new.BCODE_IZG, new.Z_ID, new.SKLAD_ID;
  end
  else
  begin
    /* if (new.ware_id<0) then */
    if (new.WARE_ID = '-1') then
    begin
      select GOODNAME, GOODIZG, GOODCOUNTRY from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.bcode_izg) into new.SNAME,
           new.SIZG, new.SCOUNTRY;
      new.WARE_ID = 0;
    end
  end
  if (new.QUANT is null) then
    new.QUANT = 0;
  if (new.PRICE is null) then
    new.PRICE = 0;
  if (new.PRICE_O is null) then
    new.PRICE_O = 0;
  if (new.NDS is null) then
    new.NDS = 0;
  if (new.SUMMA is null) then
    new.SUMMA = new.QUANT * new.PRICE;
  if (new.SUM_DSC is null) then
    new.SUM_DSC = 0;
  if (new.SUMMA_O is null) then
  begin
    new.SUMMA_O = new.QUANT * new.PRICE_O;
  end
  else
  begin
    if (new.QUANT <> 0) then
      new.PRICE_O = new.SUMMA_O / new.QUANT;
  end
  if (new.SUM_NDSO is null) then
    new.SUM_NDSO = new.SUMMA_O * new.NDS / 100;
  if (((new.NAC is null) or (new.NAC = 0)) and
      new.PRICE_O <> 0) then
    new.NAC = (new.PRICE - new.PRICE_O) * 100 / new.PRICE_O;
  if (new.DEP is null) then
  begin
    select first 1 ID from DEPS where NDS = new.NDS order by ID into new.DEP;
  end
  if (new.DEP is null) then
    new.DEP = 0;
  /* -> 20130301-ann gen_barcode_by_name */
  if ((new.BARCODE is null) or (trim(new.BARCODE) = '')) then
  begin
    if ((select PARAM_VALUE from PR_GETPARAMVALUE('GEN_BARCODE', 0)) = 0) then
      select RES_BCODE from UTPR_GETCHECKSUM_EAN13(new.ID) into new.BARCODE;

    /* else select res_bcode from utpr_getchecksum_ean13(new.name_id,'88') into new.barcode; */
    else
      select RES_BCODE from UTPR_GETCHECKSUM_EAN13((select L_ID from VALS where D$UUID = new.NAME_ID), '88') into new.BARCODE;
  end
  /* -> 20130301-ann gen_barcode_by_name */
  if (new.PART_PARENT_ID > 0) then
  begin
    update DOC_DETAIL_ACTIVE set QUANT = QUANT - new.QUANT, SUMMA = SUMMA - new.SUMMA, SUMMA_O = SUMMA_O - new.SUMMA_O, SUM_NDSO = SUM_NDSO - new.SUM_NDSO where ID = new.PART_PARENT_ID;
    execute procedure PR_COPY_MEMBERSHIP('DOC_DETAIL_ACTIVE', new.PART_PARENT_ID, new.ID);
  end
end;


CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BU0 FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE UPDATE POSITION 0
as
begin
    /*20160513
    if ((new.SNAME <> old.SNAME) or (new.NAME_ID is null) or (new.NAME_ID = '0')) then
      select VAL_ID from PR_GETVAL_ID(new.SNAME, 0, new.PART_TYPE, new.MNN) into new.NAME_ID;
    */

    if (new.NAME_ID is null) then
      new.NAME_ID = 0;
    if ((new.SUMMA is null) and
        ((new.QUANT <> 0) and
        (new.PRICE <> 0))) then
    begin
      new.SUMMA = new.QUANT * new.PRICE;
      if (new.SUM_DSC is not null) then
        new.SUMMA = new.SUMMA - new.SUM_DSC;
    end
    if (((new.SUMMA_O = 0) or (new.SUMMA_O is null)) and
        ((new.QUANT <> 0) and
        (new.PRICE_O <> 0))) then
      new.SUMMA_O = new.QUANT * new.PRICE_O;
    if (new.NAME_ID <> old.NAME_ID) then
      execute procedure PR_COPYGROUPS(old.NAME_ID, new.NAME_ID);
    if (new.SUM_DSC is null) then
      if (new.SUMMA <> old.SUMMA) then
      begin
        new.SUM_DSC = (1 + new.DISCOUNT / 100) * 100 * new.SUMMA / (100 + old.DISCOUNT) - 100 * new.SUMMA / (100 + old.DISCOUNT);
        --new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount);
      end
    if (new.SUM_DSC is null) then
      if (new.DISCOUNT <> old.DISCOUNT) then
      begin
        new.SUM_DSC = (1 + new.DISCOUNT / 100) * 100 * old.SUMMA / (100 + old.DISCOUNT) - 100 * old.SUMMA / (100 + old.DISCOUNT);
        new.SUMMA = (1 + new.DISCOUNT / 100) * 100 * old.SUMMA / (100 + old.DISCOUNT);
      end

select id from vals where vtype=0 and alttype=new.part_type and svalue=new.sname into new.name_id;
if (new.name_id is null) then new.name_id=0;
select id from vals where vtype=3 and alttype=new.part_type and svalue=new.sizg into new.izg_id;
if (new.izg_id is null) then new.izg_id=0;
select id from vals where vtype=2 and alttype=new.part_type and svalue=new.scountry into new.country_id;
if (new.country_id is null) then new.country_id=0;
select id from vals where vtype=1 and alttype=new.part_type and svalue=new.sorig_name into new.orig_name_id;
if (new.orig_name_id is null) then new.orig_name_id=0;
select id from vals where vtype=6 and alttype=new.part_type and svalue=new.sorig_izg into new.orig_izg_id;
if (new.orig_izg_id is null) then new.orig_izg_id=0;
select id from vals where vtype=5 and alttype=new.part_type and svalue=new.sorig_country into new.orig_country_id;
if (new.orig_country_id is null) then new.orig_country_id=0;


  insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
                                     DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
                                     BARCODE1, BCODE_IZG,
                                     --KRITK,
                                     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, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
                                     PART_TYPE, BASE_AGENT_ID)
  values (old.ID, old.PARENT_ID, old.DOC_ID, old.PART_ID, old.PART_PARENT_ID, old.DOC_DETAIL_ID, old.PRICE, old.NAC,
          old.QUANT, old.DISCOUNT, old.SUMMA, old.SUMMA_O, old.DCARD, old.WARE_ID, old.PRICE_O, old.PRICE_Z,
          old.PRICE_R, old.BARCODE, old.BARCODE1, old.BCODE_IZG,
          --old.KRITK,
          old.GODENDO, old.SERIA, old.NDS, old.SUM_NDSO, old.SERT, old.DATESERT, old.KEMVSERT, old.SDSERT, old.REGN,
          old.NGTD, old.EDIZM, old.NAME_ID, old.IZG_ID, old.COUNTRY_ID, old.ORIG_CODE, old.ORIG_NAME_ID,
          old.ORIG_IZG_ID, old.ORIG_COUNTRY_ID, old.Z_ID, old.SKLAD_ID, old.SNAME, old.SIZG, old.SCOUNTRY,
          old.SORIG_NAME, old.SORIG_IZG, old.SORIG_COUNTRY, old.INSERTDT, old.INFO, old.KOEF, old.MOTHERPART_ID,
          old.DEP, old.SUM_DSC, old.HUMAN_QUANT, old.CUSTOMDRAW, old.PART_TYPE, old.BASE_AGENT_ID);
end;


CREATE OR ALTER VIEW VW_WARES(
    ID,
    NAME_ID,
    SNAME,
    IZG_ID,
    SIZG,
    COUNTRY_ID,
    SCOUNTRY,
    ORIG_CODE,
    ORIG_NAME_ID,
    SORIG_NAME,
    ORIG_IZG_ID,
    SORIG_IZG,
    ORIG_COUNTRY_ID,
    SORIG_COUNTRY,
    BARCODE,
    Z_ID,
    SKLAD_ID,
    INSERTDT,
    PACKET,
    MGN_NAME,
    MGN_ID,
    MGN_SOURCE)
AS
select
w.ID,
w.NAME_ID,
w.sname,--(select svalue from vals where id = w.name_id),
w.IZG_ID,
(select svalue from vals where id = w.izg_id),
w.COUNTRY_ID,
(select svalue from vals where id = w.country_id),
w.ORIG_CODE,
w.ORIG_NAME_ID,
(select svalue from vals where id = w.orig_name_id),
w.ORIG_IZG_ID,
(select svalue from vals where id = w.orig_izg_id),
w.ORIG_COUNTRY_ID,
(select svalue from vals where id = w.orig_country_id),
w.BARCODE,
w.Z_ID,
w.SKLAD_ID,
w.INSERTDT,
w.PACKET,
w.mgn_name,
w.mgn_id,
w.mgn_source
from wares w
;



create or alter procedure PR_DOC_DETAIL_ACTIVE_ONCOMMIT (
    ACTIVE_ID type of DM_ID,
    COMMIT_ID type of DM_ID,
    DOC_ID type of DM_ID,
    COMMITDATE type of DM_DATETIME)
as
begin
/*
  эта процедура выполняется для каждой записи, добавляемой в DOC_DETAIL в процедурах проведения документов
*/
  /* Для вречей*/
  update doc_detail_doctor set doc_detail_id=:commit_id, doc_id=:doc_id, commitdate=:commitdate where DOC_DETAIL_ACTIVE_ID=:active_id;
  /* Старые рецепты*/
  update DOC_DETAIL_MAKE set doc_detail_id=:commit_id, doc_id=:doc_id, commitdate=:commitdate where DOC_DETAIL_ACTIVE_ID=:active_id;
  /* Новые рецепты*/
 update recepts set table_id=:commit_id, table_name='DOC_DETAIL', status=1 where doc_detail_active_id=:active_id; --для рецептов

 execute procedure PR_AUTO_WARESLINK(:DOC_ID, :ACTIVE_ID);

end;