Установка МНН

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск

1) Просим всех выйти из базы.

2) Создаем резервную копию базы данных

3) Выполняем скрипт 


 SET SQL DIALECT 3;

/*--TEMP_MNN---------------------------------------------------------------*/
CREATE SEQUENCE GEN_TEMP_MNN_ID;
CREATE TABLE TEMP_MNN (
    ID      DM_ID,
    SNAME   DM_TEXT,
    MNN     DM_TEXT,
    TNN     DM_TEXT,
    KOEF1   DM_DOUBLE,
    STATUS  DM_STATUS
);
COMMIT;

SET TERM ^ ;
CREATE OR ALTER TRIGGER TEMP_MNN_BI FOR TEMP_MNN
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.id IS NULL) THEN NEW.id = GEN_ID(gen_temp_mnn_id,1);
END
^
SET TERM ; ^
COMMIT;

/*--MNN_VALS---------------------------------------------------------------*/
CREATE TABLE MNN_VALS (SVALUE DM_TEXT);
COMMIT;
CREATE INDEX MNN_VALS_IDX1 ON MNN_VALS (SVALUE);
COMMIT;

/*--PR_REPART_SNAME--------------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_REPART_SNAME (
    SNAME DM_TEXT)
returns (
    R DM_TEXT)
as
declare variable RES DM_TEXT;
declare variable K DM_ID_NULL;
declare variable TEMP DM_TEXT;
declare variable TEK_NAME DM_TEXT;
begin
 k=0;
 res=sname||' ';
 r='';
 temp='';
 res=replace(res,'  ',' ');
 while (position(' ',res)>0) do
  begin
    k=k+1;
    if (position(' ',res)>1) then  temp=SUBSTRING(res from 1 for position(' ',res)-2);
    if (trim(temp)<>'') then r=r||temp||' ';
    res=SUBSTRING(res from position(' ',res)+1);
    if (k>1000) then exit;
  end

  if (char_length(r)>0) then  r=substring(r from 1 for CHAR_LENGTH(r)-1);
  suspend;
end^
SET TERM ; ^
COMMIT;

GRANT EXECUTE ON PROCEDURE PR_REPART_SNAME TO SYSDBA;
COMMIT;

/*--PR_DELETE_SYMBOL-------------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_DELETE_SYMBOL (
    S DM_TEXT)
returns (
    RES DM_TEXT)
as
declare variable TEMP DM_TEXT;
declare variable K DM_ID_NULL;
declare variable TEK_NAME DM_TEXT;
begin
  k=0;
  res=s||' ';

  res=replace(res,',','');
  res=replace(res,'.','');

  res=replace(res,'1','');
  res=replace(res,'2','');
  res=replace(res,'3','');
  res=replace(res,'4','');
  res=replace(res,'5','');
  res=replace(res,'6','');
  res=replace(res,'7','');
  res=replace(res,'8','');
  res=replace(res,'9','');
  res=replace(res,'0','');
  res=replace(res,'(','');
  res=replace(res,')','');

  if (position('МАЗЬ',UPPER(:res))=1) then
  begin
    res=replace(res,'Мазь ','');
    res=res||' Мазь';
  end

  if (position('КРЕМ',UPPER(:res))=1) then
  begin
    res=replace(res,'Крем ','');
    res=res||' Крем';
  end

  if (position('БАЛЬЗАМ',UPPER(:res))=1) then
  begin
    res=replace(res,'Бальзам ','');
    res=res||' Бальзам';
  end

 if (position('КАПЛИ',UPPER(:res))=1) then
  begin
    res=replace(res,'Капли ','');
    res=res||' капли';
  end

  if (position('ПАСТИЛК',UPPER(:res))=1) then
  begin
    res=replace(res,'Пастилки ','');
    res=res||' пастилки';
  end

  res=replace(res,' к-та ',' кислота ');
  res=replace(res,' к-та-',' кислота ');
  res=replace(res,'детский','детс');
  res=replace(res,'/','');
  res=replace(res,'№','');
  res=replace(res,'N','');
  res=replace(res,'+','');
  res=replace(res,'%','');
  res=replace(res,' мг ',' ');
  res=replace(res,'-',' ');
  res=replace(res,' мкг ',' ');
  res=replace(res,' мл ',' ');
  res=replace(res,' п/о ',' ');
  res=replace(res,' н/ст ',' ');
  res=replace(res,'   ',' ');
  res=replace(res,'  ',' ');

  res=(select r from pr_repart_sname(:res));

  res=trim(res);
  suspend;
end^
SET TERM ; ^
COMMIT;

GRANT EXECUTE ON PROCEDURE PR_REPART_SNAME TO PROCEDURE PR_DELETE_SYMBOL;
GRANT EXECUTE ON PROCEDURE PR_DELETE_SYMBOL TO PROCEDURE PR_GET_MNN;
GRANT EXECUTE ON PROCEDURE PR_DELETE_SYMBOL TO SYSDBA;
COMMIT;

/*--PR_GET_MNN-------------------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_GET_MNN (
    SNAME DM_TEXT,
    TYPE_FIND DM_TEXT)
returns (
    MNN DM_TEXT,
    TNN DM_TEXT,
    PR_TNN DM_TEXT,
    R_TNN DM_TEXT)
as
declare variable NAME DM_TEXT;
declare variable TEK_MNN DM_TEXT;
declare variable TEK_TNN DM_TEXT;
declare variable TEMP_TNN DM_TEXT;
declare variable P1 DM_ID_NULL;
declare variable P2 DM_ID_NULL;
declare variable PR_LENGTH DM_ID_NULL;
begin
  if (type_find='SNAME') then
  begin
    name=(select res from pr_delete_symbol(:sname));
    mnn=''; tnn='';
    pr_length=4;
    delete from temp_mnn;
    for select m.sname, m.mnn_lat, m.prepared_sname, m.replace_sname
    from out$mnn m order by m.sname
    into :tek_tnn, :tek_mnn, :pr_tnn, :r_tnn do
    begin
      if (position(UPPER(trim(:tek_tnn)),UPPER(trim(:sname)))=1) then  --неприведенные наименования вхождение с начало слова
      begin
        insert into temp_mnn(sname,mnn,tnn,koef1,status)
        values (:sname,:tek_mnn,:tek_tnn,100*char_length(:tek_tnn)/char_length(:sname),2);
      end

      if (position(UPPER(trim(:r_tnn)),UPPER(trim(:name)))=1 and char_length(trim(replace(:r_tnn,' ','')))>:pr_length)
      then --приведенные наименования, тнн поменяно местами вхождение c начала слова
      begin
        insert into temp_mnn(sname,mnn,tnn,koef1,status)
        values (:name,:tek_mnn,:r_tnn||'  ==  '||:tek_tnn,100*char_length(:r_tnn)/char_length(:name),1);
      end

     p1=position(UPPER(trim(:pr_tnn)),UPPER(trim(:name)));

     if (p1=1 and char_length(trim(replace(:pr_tnn,' ','')))>:pr_length) then  --приведенные наименования, вхождение с начало слова
      begin
        insert into temp_mnn(sname,mnn,tnn,koef1,status)
        values (:name,:tek_mnn,:pr_tnn||'  ==  '||:tek_tnn,100*char_length(:pr_tnn)/char_length(:name),1);
      end


      p1=position(UPPER(trim(:pr_tnn)),UPPER(trim(:name)));
      if (p1>1 and p1<round(CHAR_LENGTH(:name)/2) and char_length(trim(replace(:pr_tnn,' ','')))>:pr_length) then  --приведенные наименования, но вхождение раньше половины слова
      begin
        insert into temp_mnn(sname,mnn,tnn,koef1,status)
        values (:name,:tek_mnn,:pr_tnn||'  ==  '||:tek_tnn,100*char_length(:pr_tnn)/char_length(:name),0);
      end

      p2=position(UPPER(trim(:r_tnn)),UPPER(trim(:name)));
      if (p2>1 and p2<round(char_length(:name)/2) and char_length(trim(replace(:r_tnn,' ','')))>:pr_length) then --приведенные наименования, тнн поменяно местами, но вхождение раньше половины слова
      begin
        insert into temp_mnn(sname,mnn,tnn,koef1,status)
        values (:name,:tek_mnn,:r_tnn||'  ==  '||:tek_tnn,100*char_length(:r_tnn)/char_length(:name),0);
      end
    end
    mnn=(select first 1 mnn from temp_mnn where koef1>75 and status=2 order by koef1 desc);
    tnn=(select first 1 tnn from temp_mnn where koef1>75 and status=2 order by koef1 desc);

    if ((mnn='') or (mnn is null)) then  mnn=(select first 1 mnn from temp_mnn where koef1>99 order by status desc);
    if ((tnn='') or (tnn is null)) then  tnn=(select first 1 tnn from temp_mnn where koef1>99 order by status desc);

    if ((mnn='') or (mnn is null)) then  mnn=(select first 1 mnn from temp_mnn where koef1>10 and status=2 order by koef1 desc);  --можт меньше 20 ?
    if ((tnn='') or (tnn is null)) then  tnn=(select first 1 tnn from temp_mnn where koef1>10 and status=2 order by koef1 desc);

    if ((mnn='') or (mnn is null)) then mnn=(select first 1 mnn from temp_mnn where koef1>10 and status=1 order by koef1 desc);
    if ((tnn='') or (tnn is null)) then tnn=(select first 1 tnn from temp_mnn where koef1>10 and status=1 order by koef1 desc);

    if ((mnn='') or (mnn is null)) then mnn=(select first 1 mnn from temp_mnn where koef1>10  order by koef1 desc);
    if ((tnn='') or (tnn is null)) then tnn=(select first 1 tnn from temp_mnn where koef1>10  order by koef1 desc);

    if ((mnn='') or (mnn is null)) then mnn=(select first 1 mnn from temp_mnn where char_length(tnn)>=3 and status=2  order by koef1 desc);
    if ((tnn='') or (tnn is null)) then tnn=(select first 1 tnn from temp_mnn where char_length(tnn)>=3 and status=2 order by koef1 desc);

  end
  suspend;
end^
SET TERM ; ^
COMMIT;

COMMENT ON PARAMETER PR_GET_MNN.TYPE_FIND IS 'SNAME наименование, BARCODE - шк изготовителя';
GRANT EXECUTE ON PROCEDURE PR_DELETE_SYMBOL TO PROCEDURE PR_GET_MNN;
GRANT SELECT,INSERT,DELETE ON TEMP_MNN TO PROCEDURE PR_GET_MNN;
GRANT SELECT ON OUT$MNN TO PROCEDURE PR_GET_MNN;
GRANT EXECUTE ON PROCEDURE PR_GET_MNN TO SYSDBA;
COMMIT;

/*--PR_GETVAL_ID-----------------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_GETVAL_ID (
    SVALUE type of DM_TEXT,
    VTYPE type of DM_STATUS,
    ALTTYPE DM_STATUS,
    MNN DM_TEXT = '')
returns (
    VAL_ID type of DM_ID)
as
declare variable MNN_2 DM_TEXT;
begin
  if (:svalue is null) then svalue='';
    select id from vals where vtype=:vtype and svalue=:svalue and alttype=:alttype into :val_id;

  if ((val_id is not null) and (:mnn<>'') and (:mnn is not null) AND (vtype=0))
      then update vals set preparedvalue=:mnn where id=:val_id;

  if (val_id is null) then
  begin
    val_id=GEN_ID(GEN_VALS_ID,1);
    mnn_2=null;
    if ((:mnn<>'') and (:mnn is not null) and (vtype=0))
      then mnn_2=:mnn;
    if (vtype=0) then
      if ((:mnn='') or (:mnn is null)) then
        mnn_2=(select mnn from pr_get_mnn(:svalue,'SNAME'));
    insert into vals (id, vtype, svalue, alttype, preparedvalue) values (:val_id,:vtype,:svalue, :alttype, :mnn_2);
  end 
  suspend;
end^
SET TERM ; ^
COMMIT;

GRANT SELECT,INSERT,UPDATE ON VALS TO PROCEDURE PR_GETVAL_ID;
GRANT EXECUTE ON PROCEDURE PR_GET_MNN TO PROCEDURE PR_GETVAL_ID;
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO SYSDBA;
COMMIT;

/*--PR_GET_WARE------------------------------------------------------------*/
SET TERM ^ ;
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,
    SKLAD_ID DM_TEXT,
    ALTTYPE DM_STATUS,
    MNN DM_TEXT = '')
returns (
    W_ID type of DM_ID)
as
declare variable NAME_ID type of DM_ID;
declare variable IZG_ID type of DM_ID;
declare variable COUNTRY_ID type of DM_ID;
declare variable ORIG_NAME_ID type of DM_ID;
declare variable ORIG_IZG_ID type of DM_ID;
declare variable ORIG_COUNTRY_ID type of DM_ID;
begin
  if (orig_code is null) then orig_code='';
  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;
  if (w_id is null) then
  begin
--    exception EX_WRONG_OPER;
    w_id=gen_id(gen_wares_id,1);
    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^
SET TERM ; ^
COMMIT;

GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GET_WARE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO SYSDBA;
COMMIT;

/*--DOC_DETAIL_ACTIVE------------------------------------------------------*/
ALTER TABLE DOC_DETAIL_ACTIVE ADD MAKE_ID DM_ID_NULL;
ALTER TABLE DOC_DETAIL_ACTIVE ADD MNN DM_TEXT;
COMMIT;

SET TERM ^ ;
CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI_MNN FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE INSERT POSITION 3
AS
begin
  if (new.name_id>0) then new.mnn=(select preparedvalue from vals where id=new.name_id);
  if (new.mnn is null) then new.mnn=(select MNN from pr_get_mnn(new.sname,'SNAME'));
end
^
SET TERM ; ^
COMMIT;

/*--PR_DOC_DETAIL_INSERT---------------------------------------------------*/
ALTER TABLE DOC_DETAIL ADD MAKE_ID DM_ID_NULL;
COMMIT;

SET TERM ^ ;
create or alter procedure PR_DOC_DETAIL_INSERT (
    PARENT_ID bigint,
    DOC_ID bigint,
    PART_ID bigint,
    QUANT type of DM_DOUBLE,
    DISCOUNT double precision,
    SUMMA double precision,
    SUMMA_O double precision,
    PRICE numeric(15,4),
    SUM_NDSO numeric(15,4),
    NAC numeric(15,4),
    COMMITDATE type of DM_DATE,
    HUMAN_QUANT type of DM_TEXT,
    SUM_DSC type of DM_DOUBLE,
    DCARD type of DM_TEXT,
    ACTIVE_ID type of DM_ID,
    PART_TYPE DM_STATUS,
    MAKE_ID DM_ID_NULL = null)
as
declare variable NEW_ID type of DM_ID;
begin
  new_id=gen_id(gen_doc_detail_id,1);
  insert into doc_detail
    (id,PARENT_ID,DOC_ID,PART_ID,QUANT,DISCOUNT,SUMMA,summa_o, sum_ndso, price, nac, doc_commitdate, human_quant,SUM_DSC,dcard, part_type,make_id)
    /* (PARENT_ID,DOC_ID,PART_ID,QUANT,DISCOUNT,SUMMA,summa_o) */
   values
    /* (:PARENT_ID,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o); */
    (:new_id,:PARENT_ID,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o, :sum_ndso, :price, :nac, :commitdate, :human_quant,:SUM_DSC,:dcard, :part_type,:make_id);
   execute procedure PR_DOC_DETAIL_ACTIVE_ONCOMMIT(:active_id,:new_id,:doc_id,:commitdate);
end^
SET TERM ; ^

GRANT INSERT ON DOC_DETAIL TO PROCEDURE PR_DOC_DETAIL_INSERT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_ACTIVE_ONCOMMIT TO PROCEDURE PR_DOC_DETAIL_INSERT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO SYSDBA;
COMMIT;

/*--PR_DOC_RASHOD_COMMIT---------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_DOC_RASHOD_COMMIT (
    DOC_ID type of DM_ID,
    DBG type of DM_STATUS)
as
declare variable PARENT_ID type of DM_ID;
declare variable PART_ID type of DM_ID;
declare variable PRICE type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
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 NAC type of DM_DOUBLE;
declare variable DOC_COMMITDATE type of DM_DATE;
declare variable HUMAN_QUANT type of DM_TEXT;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable ACTIVE_ID type of DM_ID;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
begin
  select cast(commitdate as date) from docs where id=:doc_id into :doc_commitdate;
  for
    select PARENT_ID,DOC_ID,PART_ID,PRICE,QUANT,DISCOUNT,SUMMA,summa_o, sum_ndso, nac, human_quant, SUM_DSC, dcard, part_type, id, make_id
      from doc_detail_active where doc_id=:doc_id
      into :PARENT_ID,:DOC_ID,:PART_ID,:PRICE,:QUANT,:DISCOUNT,:SUMMA,:summa_o, :sum_ndso, :nac, :human_quant, :SUM_DSC, :dcard, :part_type, :active_id, :make_id
   do
   begin
     execute procedure PR_DOC_DETAIL_INSERT(:PARENT_ID,:DOC_ID,:PART_ID,QUANT,:DISCOUNT,:SUMMA,:summa_o, :price,:sum_ndso, :nac, :doc_commitdate, :human_quant, :SUM_DSC,:dcard,:active_id,:part_type,:make_id);
   end
end^
SET TERM ; ^

GRANT SELECT ON DOCS TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO SYSDBA;

/*--PR_DOC_PRIHOD_COMMIT_RECURSE-------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_DOC_PRIHOD_COMMIT_RECURSE (
    DOC_ID type of DM_ID,
    DOC_PARENT_ID type of DM_ID,
    PART_PARENT_ID type of DM_ID,
    DOC_COMMITDATE type of DM_DATE,
    CONTRACT_ID DM_ID)
as
declare variable PART_ID type of DM_ID;
declare variable WARE_ID type of DM_ID;
declare variable SNAME type of DM_TEXT;
declare variable SIZG type of DM_TEXT;
declare variable SCOUNTRY type of DM_TEXT;
declare variable ORIG_CODE type of DM_TEXT;
declare variable SORIG_NAME type of DM_TEXT;
declare variable SORIG_IZG type of DM_TEXT;
declare variable SORIG_COUNTRY type of DM_TEXT;
declare variable BARCODE type of DM_TEXT;
declare variable Z_ID type of DM_ID;
declare variable SKLAD_ID DM_TEXT;
declare variable PRICE type of DM_DOUBLE;
declare variable PRICE_O type of DM_DOUBLE;
declare variable PRICE_Z type of DM_DOUBLE;
declare variable PRICE_R type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable BARCODE1 type of DM_TEXT;
declare variable DEP type of DM_ID;
declare variable KRITK type of DM_DOUBLE;
declare variable GODENDO type of DM_DATETIME;
declare variable SERIA type of DM_TEXT;
declare variable NDS type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SERT type of DM_TEXT;
declare variable DATESERT type of DM_DATETIME;
declare variable KEMVSERT type of DM_TEXT;
declare variable SDSERT type of DM_DATETIME;
declare variable REGN type of DM_TEXT;
declare variable NGTD type of DM_TEXT;
declare variable EDIZM type of DM_TEXT;
declare variable PARENT_ID type of DM_ID;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable ACTIVE_ID type of DM_ID;
declare variable NAC type of DM_DOUBLE;
declare variable DOC_DETAIL_ID type of DM_ID;
declare variable MOTHERPART_ID type of DM_ID;
declare variable BCODE_IZG type of DM_TEXT;
declare variable HUMAN_QUANT type of DM_TEXT;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable GROUP_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
declare variable MNN DM_TEXT;
begin
  for
    select
/*ware_id*/  sname,sizg,scountry,orig_code,sorig_name,sorig_izg,sorig_country,barcode,z_id,sklad_id,
/*part_id*/  PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE1,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,
      PARENT_ID,DISCOUNT,SUMMA,summa_o, id, part_id, nac, doc_detail_id, motherpart_id, dep, bcode_izg, human_quant, SUM_DSC, dcard,
      part_type, BASE_AGENT_ID, group_id, make_id, mnn
    from doc_detail_active where doc_id=:doc_id and part_parent_id=:doc_parent_id
    into
      :sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:barcode,:z_id,:sklad_id,
      :PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE1,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,
      :PARENT_ID,:DISCOUNT,:SUMMA,:summa_o, :active_id, :part_id, :nac, :doc_detail_id, :motherpart_id, :dep, :bcode_izg, :human_quant, :SUM_DSC, :dcard,
      :part_type, :BASE_AGENT_ID, :group_id,:make_id, :mnn
   do
  begin
--    if ((price<0.001) or (price is null)) then exception EX_PRICE;
    if ((price<0) or (price is null)) then exception EX_PRICE;
    if (/*(price_o<0.001) or */(price_o is null)) then exception EX_PRICE_O;
    if (/*(quant<0.00001) or */(quant is null)) then exception EX_QUANT;
    if (/*(summa<0.00001) or */(summa is null)) then exception EX_SUMMA;
    if (/*(summa_o<0.00001) or */(summa_o is null)) then exception EX_SUMMA_O;
--    if ((NDS<0.00001) or (NDS is null)) then exception EX_NDS;
    if (/*(SUM_NDSO<0) or */(SUM_NDSO is null)) then exception EX_SUM_NDSO;

    select w_id from pr_get_ware(:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:bcode_izg,:z_id,:sklad_id,:part_type,:mnn)
    into :ware_id;

    if ((part_id=0) or (part_id is NULL)) then
      select p_id from pr_new_part(:DOC_ID,:part_parent_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, :part_type,:BASE_AGENT_ID, :sklad_id, :contract_id, :active_id, :group_id) into :part_id;

    execute procedure PR_DOC_DETAIL_INSERT(:doc_detail_id,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o,:price,:sum_ndso,:nac,:doc_commitdate,:human_quant,:SUM_DSC,:dcard,:active_id,:part_type,:make_id);
-- обновляем членство в группах
    execute procedure PR_UPDGROUPDETAIL_ACTIVE2PARTS(:active_id,:part_id);
-- присваиваем ценам партии
    update prices set part_id=:part_id where dda_id=:active_id;
    update or insert into prices (part_id,price_type,currency_id, price,dda_id) values (:part_id,0,0,:price,:active_id) matching (PART_ID,PRICE_TYPE);

    execute procedure PR_DOC_PRIHOD_COMMIT_RECURSE(:doc_id,:active_id,:part_id,:doc_commitdate,:contract_id);
  end
end^
SET TERM ; ^
COMMIT;

GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_UPDGROUPDETAIL_ACTIVE2PARTS TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT SELECT,INSERT,UPDATE ON PRICES TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO SYSDBA;
COMMIT;

/*--VW_DOC_DETAIL_ACTIVE---------------------------------------------------*/
CREATE OR ALTER VIEW VW_DOC_DETAIL_ACTIVE(
    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,
    MMBSH,
    NDSBYDEP,
    REALQUANT,
    PRICE_DSC,
    BLOCK_QUANT,
    SUM_DSC,
    HUMAN_QUANT,
    CUSTOMDRAW,
    STATUS,
    OLDPRICE,
    PART_TYPE,
    PRICES,
    BASE_AGENT_ID,
    SBASE_AGENT_ID,
    GNVLS,
    GROUP_ID,
    MNN)
AS
SELECT
da.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,
(select intvalue from vals where id=da.name_id), --KRITK,
GODENDO,
SERIA,
da.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,
da.INSERTDT,
INFO,
KOEF,
MOTHERPART_ID,
DEP,
(select membership from PR_MEMBERSHIPS('PARTS=' || da.part_id || ';DOC_DETAIL_ACTIVE=' || da.id || ';PARTS.NAME_ID=' || da.name_id || ';PARTS.IZG_ID=' ||da.izg_id ||';',ascii_char(13)||ascii_char(10),1)),
deps.nds,
(select realquant from warebase w where w.part_id=da.part_id),
iif(da.quant between -0.000001 and 0.000001,0,cast(da.summa/da.quant as numeric(15,4))),
 (select BLOCK_QUANT from warebase where part_id=da.part_id),
da.SUM_DSC,
da.human_quant,
DA.CUSTOMDRAW,
da.STATUS,
iif(da.parent_id<>0, (select ddd.price from DOC_DETAIL_ACTIVE ddd where ddd.id=da.parent_id),0),
part_type,
(select s from PR_GETPARTPRICES(da.part_id,da.id)),
da.BASE_AGENT_ID,
(select caption from agents where id=da.BASE_AGENT_ID),
da.gnvls,
da.group_id,
da.mnn
  FROM
    DOC_DETAIL_ACTIVE da left join deps on da.dep=deps.id
  order by da.parent_id, da.sname, da.id
;
COMMIT;
GRANT SELECT ON VW_DOC_DETAIL_ACTIVE TO PROCEDURE PR_PART_HISTORY;
GRANT SELECT ON VW_DOC_DETAIL_ACTIVE TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT, UPDATE, DELETE, INSERT, REFERENCES ON VW_DOC_DETAIL_ACTIVE TO STANDART WITH GRANT OPTION;
COMMIT;

/*--VW_WAREBASE_KASSA------------------------------------------------------*/
CREATE OR ALTER VIEW VW_WAREBASE_KASSA(
    PART_ID,
    WARE_ID,
    SNAME,
    SIZG,
    SCOUNTRY,
    ORIG_CODE,
    SORIG_NAME,
    SORIG_IZG,
    SORIG_COUNTRY,
    BCODE_IZG,
    PRICE,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    QUANT,
    BARCODE,
    BARCODE1,
    DEP,
    KRITK,
    GODENDO,
    SERIA,
    SUM_NDSO,
    SERT,
    DATESERT,
    KEMVSERT,
    SDSERT,
    REGN,
    NGTD,
    EDIZM,
    DOC_ID,
    DOCNUM,
    DOCDATE,
    DOCCAPTION,
    DOCAGENT,
    DOCVNUM,
    DOCVSHIFT,
    INSERTDT,
    UPDATEDT,
    ENDDT,
    NDS,
    REALQUANT,
    PART_PARENT_ID,
    NAME_ID,
    IZG_ID,
    COUNTRY_ID,
    NAC,
    BLOCK_QUANT,
    BLOCK_COUNT,
    MMBSH,
    PART_TYPE,
    BASE_AGENT_ID,
    GROUPS,
    CAPTION,
    CONTRACT_ID,
    SCONTRACT_ID,
    GROUP_ID,
    VPART_ID,
    PACKET,
    SORDERNAME,
    MNN)
AS
select
  w.PART_ID,
  w.WARE_ID,
  w.SNAME,
  w.SIZG,
  w.SCOUNTRY,
  w.ORIG_CODE,
  w.SORIG_NAME,
  w.SORIG_IZG,
  w.SORIG_COUNTRY,
  w.BCODE_IZG,
  w.PRICE,
  w.PRICE_O,
  w.PRICE_Z,
  w.PRICE_R,
  w.QUANT,
  w.BARCODE,
  w.BARCODE1,
  w.DEP,
  w.KRITK,
  w.GODENDO,
  w.SERIA,
  w.SUM_NDSO,
  w.SERT,
  w.DATESERT,
  w.KEMVSERT,
  w.SDSERT,
  w.REGN,
  w.NGTD,
  w.EDIZM,
  w.doc_id,
  w.docnum,
  w.docdate,
  w.doccaption,
  w.docagent,
  w.docvnum,
  w.docvshift,
  w.INSERTDT,
  w.UPDATEDT,
  w.ENDDT,
  w.NDS ,
  w.REALQUANT,
  w.PART_PARENT_ID, w.NAME_ID, w.IZG_ID, w.COUNTRY_ID,w.NAC, w.BLOCK_QUANT, w.BLOCK_COUNT,
  w.MMBSH,w.PART_TYPE,w.BASE_AGENT_ID,
  (select membership from PR_MEMBERSHIPS('PARTS=' || w.part_id || ';PARTS.NAME_ID=' || w.name_id || ';PARTS.IZG_ID=' || w.izg_id ||';',ascii_char(13)||ascii_char(10))) as groups, a.caption,
  W.contract_id,
  W.scontract_id,
  w.group_id,
  w.vpart_id,
  w.packet,
  w.SNAME,
  v.preparedvalue
from warebase w
  join docs d on w.doc_id = d.id
  join agents a on d.agent_id = a.id
  join vals v on w.name_id=v.id and v.vtype=0
;
COMMIT;

/*--VW_WAREBASE------------------------------------------------------------*/
CREATE OR ALTER VIEW VW_WAREBASE(
    PART_ID,
    WARE_ID,
    SNAME,
    SIZG,
    SCOUNTRY,
    ORIG_CODE,
    SORIG_NAME,
    SORIG_IZG,
    SORIG_COUNTRY,
    BCODE_IZG,
    PRICE,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    QUANT,
    BARCODE,
    BARCODE1,
    DEP,
    KRITK,
    GODENDO,
    SERIA,
    SUM_NDSO,
    SERT,
    DATESERT,
    KEMVSERT,
    SDSERT,
    REGN,
    NGTD,
    EDIZM,
    DOC_ID,
    DOCNUM,
    DOCDATE,
    DOCCAPTION,
    DOCAGENT,
    DOCVNUM,
    DOCVSHIFT,
    INSERTDT,
    UPDATEDT,
    ENDDT,
    NDS,
    REALQUANT,
    PART_PARENT_ID,
    NAME_ID,
    IZG_ID,
    COUNTRY_ID,
    NAC,
    BLOCK_QUANT,
    BLOCK_COUNT,
    MMBSH,
    PART_TYPE,
    PRICES,
    BASE_AGENT_ID,
    SBASE_AGENT_ID,
    CONTRACT_ID,
    SCONTRACT_ID,
    GROUP_ID,
    SGROUP_ID,
    VPART_ID,
    SKLAD_ID,
    MNN)
AS
select
    PART_ID,
    WARE_ID,
    SNAME,
    SIZG,
    SCOUNTRY,
    ORIG_CODE,
    SORIG_NAME,
    SORIG_IZG,
    SORIG_COUNTRY,
    BCODE_IZG,
    PRICE,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    QUANT,
    BARCODE,
    BARCODE1,
    DEP,
    (select intvalue from vals where id=name_id), --KRITK,
    GODENDO,
    SERIA,
    SUM_NDSO,
    SERT,
    DATESERT,
    KEMVSERT,
    SDSERT,
    REGN,
    NGTD,
    EDIZM,
    DOC_ID, /*(select pr.doc_id from pr_getmotherpart(part_id) pr), */
    DOCNUM,  /*(select (select ddd.docnum from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
    cast (DOCDATE as dm_date), /*(select (select ddd.docdate from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
    DOCCAPTION, /*(select (select ddd.caption from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
    DOCAGENT, /*(select (select ddd.sagent from vw_docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
    DOCVNUM, /*(select (select ddd.vnum from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
    DOCVSHIFT, /*(select (select ddd.vshift from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
    INSERTDT,
    UPDATEDT,
    ENDDT,
    NDS,
    REALQUANT,
    PART_PARENT_ID,
    NAME_ID,
    IZG_ID,
    COUNTRY_ID,
    NAC,
    BLOCK_QUANT,
    BLOCK_COUNT,
    (select membership from PR_MEMBERSHIPS('PARTS=' || wb.part_id || ';PARTS.NAME_ID=' || wb.name_id || ';PARTS.IZG_ID=' || wb.izg_id ||';',ascii_char(13)||ascii_char(10),1)),
    part_type,
    (select s from PR_GETPARTPRICES(wb.part_id,0)),
    BASE_AGENT_ID,
    (select caption from agents where id=wb.BASE_AGENT_ID),
    CONTRACT_ID,
    SCONTRACT_ID,
    GROUP_ID,
    (select gr.caption from groups gr where gr.id=wb.group_id),
    VPART_ID,
    (select sklad_id from parts where parts.id=wb.part_id),
    (select preparedvalue from vals where id=name_id)  --mnn
from WAREBASE wb
;
COMMIT;

/*--SP$WDICTS--------------------------------------------------------------*/
INSERT INTO SP$WDICTS (PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INSERTDT, INI, BMP, SORTING, FRAMECLASS)
  VALUES (0, 'МНН', NULL, 'MNN                            ', 3, NULL, '[insertsql]

[deletesql_selected]

[deletesql]

[refreshsql]
select * from OUT$MNN where id=:ID

[selectsqlwithdeleted]

[selectsql]
select * from OUT$MNN order by sname

[form_show]
position=8
left=0
Top=0
Width=1024
Height=800
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[form_get]
position=8
left=0
Top=0
Width=1024
Height=800
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[main]
sourcetablename=OUT$MNN
returnfieldname=ID
captionfieldname=SNAME
keyfieldname=ID
ViewID=OUT$MNN
RootGroupTableName=
ShowCaption=Справочник МНН
GetCaption=Справочник МНН
InitTMSGroup_id=
GroupSelect=0
foldergroup=
initfolder_id=
dataset=0
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=SNAME||'' ''||coalesce(mnn_rus,'''')
AllwaysPartial=1

[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0

[editfields]', NULL, NULL, NULL); 
COMMIT;

4) Проверяем, есть ли логирование таблицы VALS, если есть, то убираем. (Лоирование на все остальные таблицы тоже должно быть отключено).

5) Заполняем таблицу VALS МНН-нами, действие очень долгое) формула для примерного расчета времени: ~1,5 сек* ассортимент = Время в секундах. 

 /*
execute block
as
declare variable id dm_id;
declare variable sname dm_text;
begin
  for select id, svalue from vals where (vtype = 0) and ((preparedvalue is null) or (preparedvalue = '')) into :id, :sname do
    begin
      select mnn from pr_get_mnn(:sname, 'SNAME') into :sname;
      update vals set preparedvalue = :sname where id = :id;
    end
end
*/

execute block
as
declare variable id dm_id;
declare variable sname dm_text;
begin
  for select id, svalue from vals where (vtype = 0) and ((preparedvalue is null) or (preparedvalue = '')) and
    id in (select name_id from vw_warebase group by name_id) into :id, :sname do
    begin
      select mnn from pr_get_mnn(:sname, 'SNAME') into :sname;
      update vals set preparedvalue = :sname where id = :id;
    end
end

7) В сетеке остатков в менеджере (Под каждым пользователем) и в кассире добавляем колонку mnn.

8) В кассире у текущего пользователя в верхн панель выводим кнопку МНН (Горячие клавиши Ctrl+M)

9) Открываем справочник по этой кнопке

10) Загружаем сетку для справочника в кассире Файл:Сетка для справочника mnn в кассире.zip.

11) В настройках в полях поиска добавляем поле mnn.

12) После закрытия настроек проверяем поиск по МНН, должен работать.

13) Применяем профиль текущего пользователя ко остальным кассирам (кроме специфических, например пользователь "Заказ" или "Льгота" и что то в этом роде).

14) В менеджере нажимаем F3 проверяем наличие справочника МНН.Файл:Сетка для справочника mnn в менеджере.zip.


12. В менеджере проверяем редактируется ли поле МНН, если нет то идем в подпрограмму "редактирование поля" и добавляем это поле в разрешенное для редактирования (UPPERCASE(program_result.values['fieldname'])='MNN')or 13. Если поле все таки не обновляется, то обновляем версию менеджера до последней с соответсвующим обновлением базы 14. После обновления менеджера, проверить приходуется ли электронная накладная

Источник — «http://wiki.standart-n.ru/index.php?title=Установка_МНН&oldid=2417»