ТНВЭД — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
Строка 1: Строка 1:
−
*
 
 
* Новая вспомогательная таблица TNVED
 
* Новая вспомогательная таблица TNVED
 
  <nowiki>
 
  <nowiki>
Строка 51: Строка 50:
 
* Заполняем коды  
 
* Заполняем коды  
 
  <nowiki>
 
  <nowiki>
−
update wares w set tnvedcode=(select first 1  t.tnvedcode from tnved t where t.barcode=w.barcode)  where  exists  (select first 1  t.tnvedcode from tnved t where t.barcode=w.barcode);
 
−
update wares w set tnvedcode=(select first 1  t.tnvedcode from tnved t where t.sname=w.sname) where  exists  (select first 1  t.tnvedcode from tnved t where t.sname=w.sname) and coalesce(trim(w.tnvedcode),'')='';
 
−
update wares w set tnvedcode=(select first 1 ww.tnvedcode from wares ww where ww.name_id=w.name_id and ww.tnvedcode is not null )  where exists  (select first 1 ww.tnvedcode from wares ww  where ww.name_id=w.name_id and ww.tnvedcode is not null )  and w.tnvedcode is null;
 
−
 
 
EXECUTE BLOCK
 
EXECUTE BLOCK
 
AS
 
AS
Строка 63: Строка 58:
 
   for select t.barcode,t.tnvedcode from tnved t into :b,:t do
 
   for select t.barcode,t.tnvedcode from tnved t into :b,:t do
 
   begin
 
   begin
−
     update wares set tnvedcode=:t where barcode containing :b and tnvedcode is null;
+
     for select id from wares where barcode containing :b into :ware_id do
 +
      update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values ('TNVEDCODE',:ware_id,:t) matching (ATTR_SID,ATTR_LINKID);
 
   end
 
   end
 
END;
 
END;
 
  </nowiki>
 
  </nowiki>
 
* Обновить менеджер до версии не ниже 273.71
 
* Обновить менеджер до версии не ниже 273.71

Версия 17:32, 17 августа 2018

  • Новая вспомогательная таблица TNVED 
/******************************************************************************/
/***               Generated by IBExpert 16.08.2018 12:21:47                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE TNVED (
    TNVEDCODE  DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    BARCODE    DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    SNAME      DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */
);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE TNVED ADD CONSTRAINT PK_TNVED PRIMARY KEY (TNVEDCODE, BARCODE, SNAME);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX TNVED_IDX1 ON TNVED (TNVEDCODE);
CREATE INDEX TNVED_IDX2 ON TNVED (BARCODE);
CREATE INDEX TNVED_IDX3 ON TNVED (SNAME);


/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/
  • Выполнить файл tnved.sql
  • Заполняем коды 
EXECUTE BLOCK
AS
declare variable b dm_text;
declare variable t dm_text;
declare variable ware_id dm_uuid;
BEGIN
  for select t.barcode,t.tnvedcode from tnved t into :b,:t do
  begin
    for select id from wares where barcode containing :b into :ware_id do
      update or insert into ATTRIBUTE_DETAIL(ATTR_SID,ATTR_LINKID,ATTR_SVALUE) values ('TNVEDCODE',:ware_id,:t) matching (ATTR_SID,ATTR_LINKID);
  end
END;
  • Обновить менеджер до версии не ниже 273.71
Источник — «http://wiki.standart-n.ru/index.php?title=ТНВЭД&oldid=6741»