Установка Единого Окна

Сжатие товаров в одиночной аптеки перед внедрением единого окна

Сжатие товаров

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

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; 

Славины скрипты

1

set term ^;

create trigger WARES_LOG_BI_COWATMP for WARES_LOG
active before insert position 0
as
begin
 if (new.SKLAD_ID is null) then
  new.SKLAD_ID = '';
end^

set term; ^

create table TMP_LOG (SKEY DM_TEXT, SVALUE DM_TEXT);

alter table WARES_LOG add ACTUAL_WARE_ID DM_UUID_NULL;

create index WARES_LOG_ACTUAL_WARE_ID on WARES_LOG (ACTUAL_WARE_ID);

create generator GEN_VALS_CARANTINE_ID;

create table VALS_CARANTINE (ID DM_ID not null, CUR_ID DM_UUID, NEW_ID DM_UUID, INSERTDT DM_DATETIME, FROM_PROFILE_ID DM_ID_NULL);
set term ^;

create or alter trigger VALS_CARANTINE_BI for VALS_CARANTINE
active before insert position 0
as
begin
 if (new.ID is null) then
  new.ID = gen_id(GEN_VALS_CARANTINE_ID, 1);
 new.INSERTDT = current_timestamp;
 if (new.FROM_PROFILE_ID is null) then
  select FROM_PROFILE_ID
  from G$DISTRIBUTE
  where UUID = new.NEW_ID
  into new.FROM_PROFILE_ID;
end^

set term ; ^ 
set term ^ ;

create or alter trigger VALS_BIU_DODOUBLES for VALS
active before insert or update position 0
as
declare variable ID type of DM_UUID;
begin
 select ID
 from VALS
 where VTYPE = new.VTYPE
       and SVALUE = new.SVALUE
       and ALTTYPE = new.ALTTYPE
       and D$UUID <> new.D$UUID
 into :ID;
 if (ID is null) then
  exit;
 /*
0 - приведенное наименование
3 - приведенный производитель
2 - приведенная страна
1 - ориг. наименование
6 - ориг. производитель
5 - ориг. страна
Запускает цепочку апдейтов. При изменении wares проверяются дубли, и, в случае нахождения, правятся партии
*/
 if (new.VTYPE = 0) then
  update WARES
  set NAME_ID = new.ID
  where NAME_ID = :ID;
 else
 if (new.VTYPE = 3) then
  update WARES
  set IZG_ID = new.ID
  where IZG_ID = :ID;
 else
 if (new.VTYPE = 2) then
  update WARES
  set COUNTRY_ID = new.ID
  where COUNTRY_ID = :ID;
 else
 if (new.VTYPE = 1) then
  update WARES
  set ORIG_NAME_ID = new.ID
  where ORIG_NAME_ID = :ID;
 else
 if (new.VTYPE = 6) then
  update WARES
  set ORIG_IZG_ID = new.ID
  where ORIG_IZG_ID = :ID;
 else
 if (new.VTYPE = 5) then
  update WARES
  set ORIG_COUNTRY_ID = new.ID
  where ORIG_COUNTRY_ID = :ID;
 else
  exit;
 insert into VALS_CARANTINE (CUR_ID, NEW_ID)
 values (:ID, new.ID);
 delete from VALS
 where ID = :ID;
end^

set term;
^

set term ^;

create or alter trigger WARES_BIU_COWA for WARES
active before insert or update position 0
as
declare variable CUR_ID type of DM_UUID;
begin
 select D$UUID
 from WARES
 where NAME_ID = new.NAME_ID
       and IZG_ID = new.IZG_ID
       and COUNTRY_ID = new.COUNTRY_ID
       and BARCODE = new.BARCODE
       and D$UUID <> new.D$UUID
 into :CUR_ID;
 if (CUR_ID is null) then
  exit;
 if (not exists(select ID
                from WARES_LOG
                where ID = :CUR_ID
                      and ACTUAL_WARE_ID = new.ID)) then
 begin
  insert into WARES_LOG (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
                         BARCODE, Z_ID, SKLAD_ID, INSERTDT, PACKET, L_ID, ACTUAL_WARE_ID)
  select ID,
         NAME_ID,
         IZG_ID,
         COUNTRY_ID,
         ORIG_CODE,
         ORIG_NAME_ID,
         ORIG_IZG_ID,
         ORIG_COUNTRY_ID,
         BARCODE,
         Z_ID,
         SKLAD_ID,
         INSERTDT,
         PACKET,
         L_ID,
         new.D$UUID
  from WARES
  where D$UUID = :CUR_ID;
 end
 update PARTS
 set WARE_ID = new.D$UUID
 where WARE_ID = :CUR_ID;
 update DOC_DETAIL_ACTIVE
 set WARE_ID = new.D$UUID
 where WARE_ID = :CUR_ID;
 delete from WARES
 where D$UUID = :CUR_ID;
end^

set term;
^

set term ^;

create or alter trigger PARTS_BIU_COWA for PARTS
active before insert or update position 0
as
declare variable WARE_ID type of DM_UUID;
begin
 if (exists(select ID
            from WARES
            where ID = new.WARE_ID)) then
  exit;
 /* select max(actual_ware_id) from wares_log where id=new.ware_id into :ware_id; */
 select max(ACTUAL_WARE_ID)
 from WARES_LOG
 where ID = new.WARE_ID
       and (exists(select ID
                   from WARES W
                   where W.D$UUID = ACTUAL_WARE_ID))
 into :WARE_ID;
 if (WARE_ID is null) then
  exit;
 /* if (exists(select id from wares where d$uuid=:ware_id)) then */
 new.WARE_ID = :WARE_ID;
end^

set term;
^

set term ^;

create or alter trigger WARES_BD_COWA for WARES
active before delete position 0
as
declare variable WARE_ID type of DM_UUID;
begin
 if (not exists(select ID
                from PARTS
                where WARE_ID = old.ID)) then
  exit;
 /*  select max(actual_ware_id) from wares_log where id=old.id into ware_id; */
 select max(ACTUAL_WARE_ID)
 from WARES_LOG WL
 where ID = old.ID
       and (exists(select ID
                   from WARES W
                   where W.ID = WL.ACTUAL_WARE_ID))
 into WARE_ID;
 if (WARE_ID is null) then
  insert into TMP_LOG (SKEY, SVALUE)
  values ('Нет ware по acual_ware!', old.ID);
 else
  update PARTS
  set WARE_ID = :WARE_ID
  where WARE_ID = old.ID;
end^

set term;
^

set term ^;

create or alter procedure PR_TMP_CHANGEWARES_CONSTR
as
declare variable UUID type of DM_UUID;
declare variable NAME_ID type of DM_UUID;
declare variable IZG_ID type of DM_UUID;
declare variable COUNTRY_ID type of DM_UUID;
declare variable BARCODE type of DM_TEXT;
declare variable ACTUAL_UUID type of DM_UUID;
declare variable EXISTING_ACTUAL_WARE_ID type of DM_UUID;
begin
 /*  delete from wares_log wl where (ACTUAL_WARE_ID is null) and (not exists (select id from wares w where w.id=wl.id)); */
 for select min(D$UUID),
            NAME_ID,
            IZG_ID,
            COUNTRY_ID,
            BARCODE
     from WARES
     group by NAME_ID, IZG_ID, COUNTRY_ID, BARCODE
     having count(1) > 1
     into ACTUAL_UUID,
          NAME_ID,
          IZG_ID,
          COUNTRY_ID,
          BARCODE
 do
 begin
  EXISTING_ACTUAL_WARE_ID = null;
  select max(ACTUAL_WARE_ID)
  from WARES_LOG
  where NAME_ID = :NAME_ID
        and IZG_ID = :IZG_ID
        and COUNTRY_ID = :COUNTRY_ID
        and BARCODE = :BARCODE
  into EXISTING_ACTUAL_WARE_ID;
  if (EXISTING_ACTUAL_WARE_ID is not null) then
  begin
   if (exists(select ID
              from WARES
              where D$UUID = :EXISTING_ACTUAL_WARE_ID)) then
    ACTUAL_UUID = EXISTING_ACTUAL_WARE_ID;
  end
  for select D$UUID
      from WARES
      where NAME_ID = :NAME_ID
            and IZG_ID = :IZG_ID
            and COUNTRY_ID = :COUNTRY_ID
            and BARCODE = :BARCODE
            and D$UUID <> :ACTUAL_UUID
      into UUID
  do
  begin
   insert into WARES_LOG (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
                          BARCODE, Z_ID, SKLAD_ID, INSERTDT, PACKET, L_ID, ACTUAL_WARE_ID)
   select ID,
          NAME_ID,
          IZG_ID,
          COUNTRY_ID,
          ORIG_CODE,
          ORIG_NAME_ID,
          ORIG_IZG_ID,
          ORIG_COUNTRY_ID,
          BARCODE,
          Z_ID,
          SKLAD_ID,
          INSERTDT,
          PACKET,
          L_ID,
          :ACTUAL_UUID
   from WARES
   where D$UUID = :UUID;
   --на триггере      update parts set ware_id=:actual_uuid where ware_id=:uuid;
   delete from WARES
   where D$UUID = :UUID;
  end
 end
end^

set term;
^

set term ^;

create or alter trigger WARES_LOG_AI_COWATMP for WARES_LOG
active after insert position 0
as
begin
 if (new.ACTUAL_WARE_ID is null) then
  exit;
 if (exists(select ID
            from WARES
            where ID = new.ACTUAL_WARE_ID)) then
  delete from WARES
  where ID = new.ID;
 else
  insert into TMP_LOG (SKEY, SVALUE)
  values ('WARES_LOG_AI_COWATMP', new.ACTUAL_WARE_ID);
end^

set term;
^

alter trigger WARES_AD_DISTR inactive;
alter trigger WARES_BU_DISTR inactive;
alter trigger WARES_LOG_BI_DISTR inactive;
alter trigger WARES_LOG_AI0 inactive;
alter trigger PARTS_AU0 inactive;

alter trigger PARTS_BU_DISTR inactive;
alter trigger PARTS_BU_G$SYNC inactive;
alter trigger PARTS_BU0 inactive;

4

/* Insert into wares_log */

3

alter trigger PARTS_BU_DISTR active;
alter trigger PARTS_BU_G$SYNC active;
alter trigger PARTS_BU0 active;

alter trigger WARES_LOG_BI_DISTR active;
alter trigger WARES_AD_DISTR active;
alter trigger WARES_BU_DISTR active;

create index WARES_IDX_U on WARES(NAME_ID, IZG_ID, COUNTRY_ID, BARCODE);

alter table WARES drop constraint UNQ1_WARES;
alter table WARES drop constraint WARES_IDX1;
create index WARES_IDX1 on WARES(NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID);

drop trigger WARES_LOG_BI_COWATMP;

alter trigger WARES_AU0 inactive;

alter trigger WARES_BU0 inactive;
alter trigger WARES_BIU_COWA inactive;
alter trigger WARES_SNAME_BU0 inactive;
alter trigger WARES_BU_DISTR inactive;

4

update WARES
set BARCODE = ''
where BARCODE is null;
alter trigger WARES_BU0 active;
alter trigger WARES_BIU_COWA active;
alter trigger WARES_SNAME_BU0 active;
alter trigger WARES_BU_DISTR active;

create unique index WARES_IDX_UUID on WARES(D$UUID);

5

execute procedure PR_TMP_CHANGEWARES_CONSTR;

6

drop index WARES_IDX_U;
create unique index WARES_IDX_U on WARES (NAME_ID, IZG_ID, COUNTRY_ID, BARCODE);

drop trigger WARES_LOG_AI_COWATMP; 

7

/* update or insert into wares */

8

ALTER TRIGGER WARES_AU0 ACTIVE;
ALTER TRIGGER WARES_LOG_AI0 ACTIVE;
ALTER TRIGGER PARTS_AU0 ACTIVE;

ALTER TRIGGER PARTS_BU_DISTR INACTIVE;

9

execute procedure DBGPR_MAKEWAREBASEFROMDOCS(0);

10

ALTER TRIGGER PARTS_BU_DISTR ACTIVE;