Текущая версия на 11:33, 17 апреля 2017

Ошибка

Ошибка проявляется у клиентов, при вставке из буфера в активный документ, если у клиента еще нет Единого окна:

"Input parameter mismatch for procedure PR_MAKEGOODWAREVALUES"

Исправление процедур

SET TERM ^ ;

create or alter procedure PR_GETWARE_BY_IDS (
    NAME_ID type of DM_UUID_NULL,
    IZG_ID type of DM_UUID_NULL,
    COUNTRY_ID type of DM_UUID_NULL,
    ORIG_CODE type of DM_TEXT,
    ORIG_NAME_ID type of DM_UUID_NULL,
    ORIG_IZG_ID type of DM_UUID_NULL,
    ORIG_COUNTRY_ID type of DM_UUID_NULL,
    DOINSERT type of DM_STATUS,
    SEARCHINLOGS type of DM_STATUS,
    BARCODE DM_TEXT)
returns (
    WARE_ID type of DM_UUID_NULL)
as
begin
  if (orig_code is null) then orig_code='';
  if (searchinlogs is null) then searchinlogs=0;
  if (searchinlogs=1) then
  begin 
    select first 1 id from wares_log 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
      and barcode=:barcode
      into :ware_id;
      if ((select id from wares where id = :ware_id) is null) then ware_id = null;
  end
  else
  begin
    select first 1 id from wares where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and
      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
      and barcode=:barcode
      into :ware_id;
  end
  if ((ware_id is null) and (DOINSERT=1) and (searchinlogs<>1)) then
  begin
--    ware_id=gen_id(gen_wares_id,1);
    ware_id=UUID_TO_CHAR(GEN_UUID());
    insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,Z_ID,SKLAD_ID, barcode)
     values
       (:ware_id,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,0,'',:barcode);
  end

  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON WARES_LOG TO PROCEDURE PR_GETWARE_BY_IDS;
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GETWARE_BY_IDS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MGNLINK;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE4PART;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_MAKEGOODWAREVALUES (
    SNAME type of DM_TEXT,
    SORIG_NAME type of DM_TEXT,
    SIZG type of DM_TEXT,
    SORIG_IZG type of DM_TEXT,
    SCOUNTRY type of DM_TEXT,
    SORIG_COUNTRY type of DM_TEXT,
    ORIG_CODE type of DM_TEXT,
    ACTIVE_ID type of DM_ID,
    ALTTYPE DM_STATUS,
    BARCODE DM_TEXT)
returns (
    GOODNAME type of DM_TEXT,
    GOODIZG type of DM_TEXT,
    GOODCOUNTRY type of DM_TEXT,
    GOODBARCODE DM_TEXT)
as
declare variable WARE_ID type of DM_UUID_NULL;
declare variable NAME_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
begin
  if ((sname is null) and (sorig_name is not null)) then sname=sorig_name;
  if ((sizg is null) and (sorig_izg is not null)) then sizg=sorig_izg;
  if ((sizg is not null) and (sorig_izg is null)) then sorig_izg=sizg;
  if ((scountry is null) and (sorig_country is not null)) then scountry=sorig_country;
  if ((scountry is not null) and (sorig_country is null)) then sorig_country=scountry;
/*  -- ищем id-шники -- */
  select id from vals where vtype=0 and alttype=:alttype and svalue=:sname into :name_id;
  select id from vals where vtype=1 and alttype=:alttype and svalue=:sorig_name into :orig_name_id;
  select id from vals where vtype=3 and alttype=:alttype and svalue=:sizg into :izg_id;
  select id from vals where vtype=6 and alttype=:alttype and svalue=:sorig_izg into :orig_izg_id;
  select id from vals where vtype=2 and alttype=:alttype and svalue=:scountry into :country_id;
  select id from vals where vtype=5 and alttype=:alttype and svalue=:sorig_country into :orig_country_id;

/*  -- ищем в логах -- */
  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,1, :barcode) into :ware_id;
  if (ware_id is not null) then --нашли - подставляем текущие значения!
  begin
    select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select sname, sizg, scountry from VW_WARES where id=:ware_id into :goodname, :goodizg, :goodcountry;
    if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then
      insert into doc_detail_active_log(id,sname,sizg,scountry,part_type) values (:active_id,:sname,:sizg,:scountry,:alttype);
    --goodname='2';--sname;
    suspend;
    exit;
  end 
/*  -- ищем в текущих товарах -- */
  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,null, :barcode) into :ware_id;
  if (ware_id is not null) then --нашли - ничо больше не надо!
  begin
    select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    --goodname='1';--sname;
    goodname=sname;
    goodizg=sizg;
    goodcountry=scountry;
    suspend;
    exit;
  end 

/* -- Приводим наименование -- */
  ware_id=null;
  if (orig_name_id is not null) then
  begin
    select first 1 id from wares where orig_name_id=:orig_name_id order by id desc into :ware_id;
    if (ware_id is null) then
      select first 1 id from wares_log where orig_name_id=:orig_name_id order by log_id desc into :ware_id;
  end
  if ((ware_id is null) and (name_id is not null)) then
  begin
    select first 1 id from wares where name_id=:name_id into :ware_id;
    if (ware_id is null) then
      select first 1 id from wares_log where name_id=:name_id order by log_id desc into :ware_id;
  end
  if (ware_id is null) then
  begin
    --goodname='3';--sname;
    goodname=sname;
    name_id=null;
  end
  else
  begin
    select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select w.name_id, v.svalue from WARES w left join vals v on w.name_id=v.id where w.id=:ware_id into :name_id, :goodname;
  end

/* -- Приводим изготовителя -- */
  ware_id=null;
  if (orig_izg_id is not null) then
  begin
    select first 1 id from wares where orig_izg_id=:orig_izg_id into :ware_id;
    if (ware_id is null) then
      select first 1 id from wares_log where orig_izg_id=:orig_izg_id order by log_id desc into :ware_id;
  end
  if ((ware_id is null) and (izg_id is not null)) then
  begin
    select first 1 id from wares where izg_id=:izg_id into :ware_id;
    if (ware_id is null) then
      select first 1 id from wares_log where izg_id=:izg_id order by log_id desc into :ware_id;
  end
  if (ware_id is null) then
    goodizg=sizg;
  else
  begin
    select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select w.izg_id, v.svalue from WARES w left join vals v on w.izg_id=v.id where w.id=:ware_id into :izg_id, :goodizg;
  end

/* -- Приводим страну -- */
  ware_id=null;
  if (orig_country_id is not null) then
  begin
    select first 1 id from wares where orig_country_id=:orig_country_id into :ware_id;
    if (ware_id is null) then
      select first 1 id from wares_log where orig_country_id=:orig_country_id order by log_id desc into :ware_id;
  end
  if ((ware_id is null) and (country_id is not null)) then
  begin
    select first 1 id from wares where country_id=:country_id into :ware_id;
    if (ware_id is null) then
      select first 1 id from wares_log where country_id=:country_id order by log_id desc into :ware_id;
  end
  if (ware_id is null) then
    goodcountry=scountry;
  else
  begin
    select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select w.country_id, v.svalue from WARES w left join vals v on w.country_id=v.id where w.id=:ware_id into :country_id, :goodcountry;
  end

  if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then
    insert into doc_detail_active_log(id,sname,sizg,scountry, part_type) values (:active_id,:sname,:sizg,:scountry,:alttype);

  --goodname='3';

  suspend;
end

/*
select
  sname,
  (select goodname from pr_makegoodwarevalues(sname, sorig_name, sizg, sorig_izg, scountry, sorig_country, orig_code,0)),
  sorig_name,
  sizg,
  (select goodizg from pr_makegoodwarevalues(sname, sorig_name, sizg, sorig_izg, scountry, sorig_country, orig_code,0)),
  sorig_izg,
  scountry,
  (select goodcountry from pr_makegoodwarevalues(sname, sorig_name, sizg, sorig_izg, scountry, sorig_country, orig_code,0)),
  sorig_country,
  orig_code
from
  doc_detail_active
where doc_id=4036
*/^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON VALS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT EXECUTE ON PROCEDURE PR_GETACTUALWAREID TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT SELECT ON VW_WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT INSERT ON DOC_DETAIL_ACTIVE_LOG TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT SELECT ON WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT SELECT ON WARES_LOG TO PROCEDURE PR_MAKEGOODWAREVALUES;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO SYSDBA;

Исправление триггера DOC_DETAIL_ACTIVE_BI

Находим вызов PR_MAKEGOODWAREVALUES и меняем

 select GOODNAME, GOODIZG, GOODCOUNTRY from PR_MAKEGOODWAREVALUES(new.sname,new.sorig_name,new.sizg,new.sorig_izg,new.scountry,new.sorig_country,new.orig_code,new.id,new.part_type)
 into new.sname, new.sizg,new.scountry;

на

 select GOODNAME, GOODIZG, GOODCOUNTRY, GOODBARCODE from PR_MAKEGOODWAREVALUES(new.sname,new.sorig_name,new.sizg,new.sorig_izg,new.scountry,new.sorig_country,new.orig_code,new.id,new.part_type,new.bcode_izg)
 into new.sname, new.sizg,new.scountry, new.BCODE_IZG;

Теги

вставка из буфера, вставка из Excel, ошибка при вставке, PR_MAKEGOODWAREVALUES