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

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(Новая страница: «1) Просим всех выйти из базы. 2) Создаем резервную копию базы данных 3) Выполняем скрипт…»)
 
Строка 4: Строка 4:
  
 
3) Выполняем скрипт  
 
3) Выполняем скрипт  
 +
<nowiki>
  
 +
SET SQL DIALECT 3;
  
−
SET SQL DIALECT 3;
 
 
/*--TEMP_MNN---------------------------------------------------------------*/
 
/*--TEMP_MNN---------------------------------------------------------------*/
 
CREATE SEQUENCE GEN_TEMP_MNN_ID;
 
CREATE SEQUENCE GEN_TEMP_MNN_ID;
Строка 18: Строка 19:
 
);
 
);
 
COMMIT;
 
COMMIT;
 +
 
SET TERM ^ ;
 
SET TERM ^ ;
 
CREATE OR ALTER TRIGGER TEMP_MNN_BI FOR TEMP_MNN
 
CREATE OR ALTER TRIGGER TEMP_MNN_BI FOR TEMP_MNN
Строка 28: Строка 30:
 
SET TERM ; ^
 
SET TERM ; ^
 
COMMIT;
 
COMMIT;
 +
 
/*--MNN_VALS---------------------------------------------------------------*/
 
/*--MNN_VALS---------------------------------------------------------------*/
 
CREATE TABLE MNN_VALS (SVALUE DM_TEXT);
 
CREATE TABLE MNN_VALS (SVALUE DM_TEXT);
Строка 33: Строка 36:
 
CREATE INDEX MNN_VALS_IDX1 ON MNN_VALS (SVALUE);
 
CREATE INDEX MNN_VALS_IDX1 ON MNN_VALS (SVALUE);
 
COMMIT;
 
COMMIT;
 +
 
/*--PR_REPART_SNAME--------------------------------------------------------*/
 
/*--PR_REPART_SNAME--------------------------------------------------------*/
 
SET TERM ^ ;
 
SET TERM ^ ;
Строка 48: Строка 52:
 
  res=sname||' ';
 
  res=sname||' ';
 
  r='';
 
  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;
 +
 +
</nowiki>
  
 
4) Проверяем, есть ли логирование таблицы VALS, если есть, то убираем. (Лоирование на все остальные таблицы тоже должно быть отключено).
 
4) Проверяем, есть ли логирование таблицы VALS, если есть, то убираем. (Лоирование на все остальные таблицы тоже должно быть отключено).
Строка 54: Строка 1051:
 
5) Заполняем таблицу VALS МНН-нами, действие очень долгое) формула для примерного расчета времени: ~1,5 сек* ассортимент = Время в секундах.  
 
5) Заполняем таблицу VALS МНН-нами, действие очень долгое) формула для примерного расчета времени: ~1,5 сек* ассортимент = Время в секундах.  
  
−
 
+
<nowiki>
−
/*
+
/*
 
execute block
 
execute block
 
as
 
as
Строка 68: Строка 1065:
 
end
 
end
 
*/
 
*/
 +
 
execute block
 
execute block
 
as
 
as
Строка 80: Строка 1078:
 
     end
 
     end
 
end
 
end
−
 
+
</nowiki>
  
 
7) В сетеке остатков в менеджере (Под каждым пользователем)  и в кассире добавляем колонку mnn.
 
7) В сетеке остатков в менеджере (Под каждым пользователем)  и в кассире добавляем колонку mnn.
Строка 88: Строка 1086:
 
9) Открываем справочник по этой кнопке
 
9) Открываем справочник по этой кнопке
  
−
10) Загружаем сетку "Сетка для справочника mnn в кассире"
+
10) Загружаем сетку для справочника в кассире [[Файл: Сетка_для_справочника_mnn_в_кассире.zip|Сетка для справочника МНН в кассире]].
−
 
+
−
Прикрепленный файл  [[Файл: Сетка_для_справочника_mnn_в_кассире.zip|Сетка для справочника МНН в кассире]]
+
−
 
+
−
11) В настройках в полях поиска добавляем поле mnn
+
  
−
12) После закрытия настроек проверяем поиск по МНН, должен работать
+
11) В настройках в полях поиска добавляем поле mnn.
  
−
13) Применяем профиль текущего пользователя ко остальным кассирам (кроме специфических, например пользователь "Заказ" или "Льгота" и что то в этом роде)
+
12) После закрытия настроек проверяем поиск по МНН, должен работать.
  
−
14) В менеджере нажимаем F3 проверяем наличие справочника МНН
+
13) Применяем профиль текущего пользователя ко остальным кассирам (кроме специфических, например пользователь "Заказ" или "Льгота" и что то в этом роде).
  
−
Прикрепленный файл  [[Файл: Сетка_для_справочника_mnn_в_менеджере.zip|Сетка для справочника МНН в менеджере]]
+
14) В менеджере нажимаем F3 проверяем наличие справочника МНН.[[Файл: Сетка_для_справочника_mnn_в_менеджере.zip|Сетка для справочника МНН в менеджере]].
  
  

Версия 16:50, 22 апреля 2015

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»