Установка МНН — различия между версиями
Natali (обсуждение | вклад) (Новая страница: «1) Просим всех выйти из базы. 2) Создаем резервную копию базы данных 3) Выполняем скрипт…») |
Natali (обсуждение | вклад) |
||
Строка 4: | Строка 4: | ||
3) Выполняем скрипт | 3) Выполняем скрипт | ||
+ | <nowiki> | ||
+ | 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) Загружаем сетку | + | 10) Загружаем сетку для справочника в кассире [[Файл: Сетка_для_справочника_mnn_в_кассире.zip|Сетка для справочника МНН в кассире]]. |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | 11) В настройках в полях поиска добавляем поле mnn. | |
− | + | 12) После закрытия настроек проверяем поиск по МНН, должен работать. | |
− | + | 13) Применяем профиль текущего пользователя ко остальным кассирам (кроме специфических, например пользователь "Заказ" или "Льгота" и что то в этом роде). | |
− | + | 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. После обновления менеджера, проверить приходуется ли электронная накладная