/* Server version: WI-V6.3.4.18393 Firebird 2.1 
   SQLDialect: 3. ODS: 11.1. Forced writes: On. Sweep inteval: 20000.
   Page size: 8192. Cache pages: 9500 (76000 Kb). Read-only: False. */
SET SQL DIALECT 3;

/* Create Unique... */
ALTER TABLE MENU_LINE ADD CONSTRAINT UNQ1_MENU_LINE UNIQUE (ID_STOCK, ID_MENU_HEAD, ID_MENU_GROUP, STOCK_NAME);

/* Alter Procedure... */
/* Alter (EAI_MAP_ALLINCLUSIVE_MODIFY) */
SET TERM ^ ;

ALTER PROCEDURE EAI_MAP_ALLINCLUSIVE_MODIFY(ID_OBJECT INTEGER,
HOTEL VARCHAR(50),
ID_PAYMENT_TYPE INTEGER,
OPERATION VARCHAR(10),
COMPATIBLE_GUEST_BOARD VARCHAR(30),
PAYABLE_STOCK_GROUP_LIST VARCHAR(1000))
 AS
declare variable id_stock_group integer;
  declare variable id_eai_map_allinclusive integer;
begin
  if (:operation = 'add') then begin
    insert into EAI_MAP_ALLINCLUSIVE (ID_OBJECT, HOTEL, ID_PAYMENT_TYPE, COMPATIBLE_GUEST_BOARD)
    values (:id_object, :hotel, :id_payment_type, :compatible_guest_board)
    returning ID_EAI_MAP_ALLINCLUSIVE into :id_eai_map_allinclusive;

    for
      select distinct trim(sp.ITEM)
      from   SYSTEM_STRING_PARSER (:PAYABLE_STOCK_GROUP_LIST, ',') sp
      into   :id_stock_group
    do begin
      insert into EAI_MAP_ALL_INCLUSIVE_PAYABLE (ID_EAI_MAP_ALL_INCLUSIVE, ID_STOCK_GROUP)
      values (:id_eai_map_allinclusive, :id_stock_group);
    end 
  
  end else if (:operation = 'del') then begin
    delete from  EAI_MAP_ALLINCLUSIVE A
    where  A.ID_OBJECT = :id_object
           and A.HOTEL = :hotel
           and coalesce(a.COMPATIBLE_GUEST_BOARD, '') = coalesce(:COMPATIBLE_GUEST_BOARD, '');
  end

  insert into EAI_MAP_ALLINCLUSIVE_LOG (ID_OBJECT, HOTEL, ID_PAYMENT_TYPE, OPERATION)
  values (:id_object, :hotel, :id_payment_type, :operation);
end
^

/* empty dependent procedure body */
/* Clear: IMPORT_MENU_LINE$ENT for: MENU_MODIFY_LINE */
ALTER PROCEDURE IMPORT_MENU_LINE$ENT(ID_MENU_HEAD INTEGER,
INVENT_NAME VARCHAR(30),
STOCK_NAME VARCHAR(50),
MENU_STOCK_NAME VARCHAR(50),
MENU_GROUP_NAME VARCHAR(50),
MENU_GROUP_LINK_NAME VARCHAR(50),
TAG INTEGER)
 RETURNS(ID_MENU_LINE INTEGER,
ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
 BEGIN SUSPEND; END
^

/* empty dependent procedure body */
/* Clear: MENU_MODIFY_MULTILINE for: MENU_MODIFY_LINE */
ALTER PROCEDURE MENU_MODIFY_MULTILINE(ID_MENU_HEAD INTEGER,
ID_INVENT INTEGER,
ID_MENU_GROUP INTEGER,
MENU_GROUP_LINK INTEGER,
TAG INTEGER,
STOCK_LIST VARCHAR(4000),
OPERATION VARCHAR(10))
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
 BEGIN SUSPEND; END
^

/* Alter (MENU_MODIFY_LINE) */
ALTER PROCEDURE MENU_MODIFY_LINE(ID_MENU_LINE INTEGER,
ID_MENU_HEAD INTEGER,
ID_INVENT INTEGER,
STOCK_NAME VARCHAR(50),
ID_STOCK INTEGER,
ID_MENU_GROUP INTEGER,
MENU_GROUP_LINK INTEGER,
TAG INTEGER,
OPERATION VARCHAR(10))
 RETURNS(NEW_ID_MENU_LINE INTEGER,
ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
begin
  err_code = 0;
  err_text = '';
  if (:operation not in('add', 'mod', 'del')) then begin
    err_code = -1;
    err_text = 'operation unknown !';
  end

  if (operation = 'add') then begin
    if (exists(
      select *
      from   MENU_LINE ML
             join STOCK S on S.ID_STOCK = ML.ID_STOCK
      where  ML.ID_MENU_HEAD = :id_menu_head
             and S.ID_STOCK = :id_stock
             and S.IS_MODIFIER = 0
      )
    ) then begin
      err_code = -54;
    end

    begin
      new_id_menu_line = gen_id(gen_menu_line_id,1);
      insert into MENU_LINE(ID_MENU_LINE, STOCK_NAME ,ID_MENU_HEAD,
        ID_INVENT, ID_STOCK, ID_MENU_GROUP, MENU_GROUP_LINK, TAG)
      values (:new_id_menu_line, :stock_name, :id_menu_head,
        :id_invent, :id_stock, :id_menu_group, :menu_group_link, :tag);

      when sqlcode -803 do begin
        ERR_CODE = -55;
        exit;
      end
    end

    insert into MENU_LINE_PRICE(ID_MENU_LINE, ID_MENU_PRICE_LIST, PROFIT_PERCENT, SALE_PRICE)
    select :new_id_menu_line, MPL.ID_MENU_PRICE_LIST, MPL.DEF_PROFIT_PERCENT, 0
    from   MENU_PRICE_LIST MPL
    where  MPL.ID_MENU_HEAD = :id_menu_head;
  end else if (operation = 'mod') then begin
    update MENU_LINE
    set    STOCK_NAME = :stock_name,
           ID_MENU_GROUP = :id_menu_group,
           ID_INVENT = :id_invent,
           MENU_GROUP_LINK = :menu_group_link
    where  ID_MENU_LINE = :id_menu_line;

    when sqlcode -803 do begin
      ERR_CODE = -55;
      exit;
    end
  end else if (operation = 'del') then begin
    delete from MENU_LINE
    where  ID_MENU_LINE = :id_menu_line;
  end

  when sqlcode -803 do exit;
end
^

/* empty dependent procedure body */
/* Clear: IMPORT_MENU_HEAD$ENT for: MENU_MODIFY_MENU */
ALTER PROCEDURE IMPORT_MENU_HEAD$ENT(MENU_NAME VARCHAR(50),
OBJECT_NAME VARCHAR(30),
COL_NO INTEGER,
ROW_NO INTEGER,
FONT_DATA BLOB,
UUID VARCHAR(40))
 RETURNS(ID_MENU_HEAD INTEGER,
ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
 BEGIN SUSPEND; END
^

/* Alter (MENU_MODIFY_MENU) */
ALTER PROCEDURE MENU_MODIFY_MENU(ID_CLIENT_POS INTEGER,
ID_MENU_HEAD INTEGER,
OPERATION VARCHAR(10),
NEW_ID_OBJECT INTEGER,
NEW_MENU_NAME VARCHAR(50),
NEW_COL_NO INTEGER,
NEW_ROW_NO INTEGER,
NEW_FONT_DATA BLOB)
 RETURNS(NEW_ID_MENU_HEAD INTEGER,
ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
declare variable user_name varchar(20);
  declare variable id_menu_price_list integer;
  declare variable new_id_menu_price_list integer;
  declare variable prefix varchar(50);
  declare variable tmp_sale_price numeric(15, 4);
  declare variable tmp_profit_percent numeric(15, 4);
  declare variable def_profit_percent numeric(15, 4);
  declare variable id_menu_line integer;
  declare variable old_id_object integer;
  declare variable db_type varchar(50);
begin
  err_code = 0;
  err_text = '';
  if (:operation not in('copy', 'mod', 'del', 'add')) then begin
    err_code = -1;
    err_text = 'operation unknown !';
  end

  execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'CURRENT_USER')
  returning_values  :user_name;

  NEW_ID_MENU_HEAD = ID_MENU_HEAD;
  if (:operation = 'del') then begin
    for
      select MPL.ID_MENU_PRICE_LIST
      from   MENU_PRICE_LIST MPL
      where  MPL.ID_MENU_HEAD = :id_menu_head
      into   :id_menu_price_list
    do begin
      execute procedure MENU_MODIFY_PRICELIST(:id_client_pos, :id_menu_price_list, :id_menu_head, '',
        '', 0, 0, 0, 0, 0, 0, 'menudel')
      returning_values  :new_id_menu_price_list, :err_code, :err_text;
      if (:err_code < 0) then exit;
    end
    delete from MENU_HEAD MH where MH.ID_MENU_HEAD = :id_menu_head;
  end else
  if (:operation = 'mod') then begin
    execute procedure SYSTEM_GET_VALUE(:id_client_pos, 'DB_TYPE')
    returning_values  :db_type;
    if (:db_type in ('CENTRAL', 'CENTRAL_V2')) then begin
      select MH.ID_OBJECT
      from   MENU_HEAD MH
      where  MH.ID_MENU_HEAD = :id_menu_head
      into   :old_id_object;
      if (:new_id_object <> :old_id_object) then begin
        err_code = -156;
        err_text = 'In Enterprise mode changing the OBJECT of the MENU is IMPOSSIBLE !';
        exit;
      end
    end
    update MENU_HEAD MH
    set    MH.ID_OBJECT = :new_id_object,
           MH.MENU_NAME = :new_menu_name,
           MH.COL_NO    = :new_col_no,
           MH.ROW_NO    = :new_row_no,
           MH.FONT_DATA = :new_font_data
    where  MH.ID_MENU_HEAD = :id_menu_head;

    update MENU_PRICE_LIST MPL
    set    MPL.MODIFY_TIMESTAMP = current_timestamp
    where  MPL.ID_MENU_HEAD = :id_menu_head;
  end else if (:operation = 'copy') then begin

    execute procedure SYSTEM_GET_TEXT(:id_client_pos, 'COPY_OF')
    returning_values :prefix;

    new_id_menu_head = gen_id(gen_menu_head_id, 1);
    insert into MENU_HEAD(ID_MENU_HEAD, ID_OBJECT, MENU_NAME, COL_NO, ROW_NO, FONT_DATA)
    select :new_id_menu_head, :new_id_object, substring(:prefix||' '||MH.MENU_NAME from 1 for 50), MH.COL_NO, MH.ROW_NO, MH.FONT_DATA
    from   MENU_HEAD MH
    where  MH.ID_MENU_HEAD = :id_menu_head;

    insert into MENU_LINE(STOCK_NAME, ID_MENU_HEAD, ID_INVENT, ID_STOCK, ID_MENU_GROUP, MENU_GROUP_LINK)
    select ML.STOCK_NAME, :new_id_menu_head, ML.ID_INVENT, ML.ID_STOCK, ML.ID_MENU_GROUP, ML.MENU_GROUP_LINK
    from   MENU_LINE ML
    where  ML.ID_MENU_HEAD = :id_menu_head;

    for
      select MPL.ID_MENU_PRICE_LIST, MPL.DEF_PROFIT_PERCENT
      from   MENU_PRICE_LIST MPL
      where  MPL.ID_MENU_HEAD = :id_menu_head
      into   :id_menu_price_list, :def_profit_percent
    do begin
      new_id_menu_price_list = gen_id(gen_menu_price_list_id, 1);
      insert into MENU_PRICE_LIST (ID_MENU_PRICE_LIST, ID_MENU_HEAD, PRICE_LIST_NAME,
        PRICE_LIST_TYPE, DEF_PROFIT_PERCENT, IS_BASE, ID_SECURE_LEVEL, ID_SURCHARGE,
        ROUND_DEC, IS_ACTIVE, CHANGED_USER, CHANGED_DATETIME, STATUS)
      select :new_id_menu_price_list, :new_id_menu_head, MPL2.PRICE_LIST_NAME, MPL2.PRICE_LIST_TYPE,
        MPL2.DEF_PROFIT_PERCENT, MPL2.IS_BASE, MPL2.ID_SECURE_LEVEL, MPL2.ID_SURCHARGE,
        MPL2.ROUND_DEC, 0, :user_name, current_timestamp, 2
      from  MENU_PRICE_LIST MPL2
      where MPL2.ID_MENU_PRICE_LIST = :id_menu_price_list;

      for
        select MLP.SALE_PRICE, MLP.PROFIT_PERCENT, ML_NEW.ID_MENU_LINE
        from   MENU_LINE ML
               join MENU_LINE ML_NEW on ML_NEW.ID_STOCK = ML.ID_STOCK
                                        and ML_NEW.ID_MENU_HEAD = :new_id_menu_head
                                        and ML_NEW.ID_MENU_GROUP = ML.ID_MENU_GROUP
                                        and ML_NEW.STOCK_NAME = ML.STOCK_NAME
               left outer join MENU_LINE_PRICE MLP on MLP.ID_MENU_LINE = ML.ID_MENU_LINE
                                                      and MLP.ID_MENU_PRICE_LIST = :id_menu_price_list
        where  ML.ID_MENU_HEAD = :id_menu_head
        into   :tmp_sale_price, :tmp_profit_percent, :id_menu_line
      do begin
        if ((:tmp_sale_price is null) and (:tmp_profit_percent is null)) then begin
          tmp_sale_price     = null;
          tmp_profit_percent = :def_profit_percent;
        end
        insert into MENU_LINE_PRICE(ID_MENU_LINE, ID_MENU_PRICE_LIST, PROFIT_PERCENT, SALE_PRICE)
        values (:id_menu_line, :new_id_menu_price_list, :tmp_profit_percent, :tmp_sale_price);
      end
    end

  end else if (:operation = 'add') then begin
      new_id_menu_head = gen_id(gen_menu_head_id, 1);
      insert into MENU_HEAD(ID_MENU_HEAD, ID_OBJECT, MENU_NAME, COL_NO, ROW_NO, FONT_DATA)
      values (:new_id_menu_head, :new_id_object, :new_menu_name, :new_col_no, :new_row_no, :new_font_data);
  
      new_id_menu_price_list = gen_id(gen_menu_price_list_id, 1);
      insert into MENU_PRICE_LIST (ID_MENU_PRICE_LIST, ID_MENU_HEAD, PRICE_LIST_NAME,
        PRICE_LIST_TYPE, DEF_PROFIT_PERCENT, IS_BASE, ID_SECURE_LEVEL, ID_SURCHARGE,
        ROUND_DEC, IS_ACTIVE, CHANGED_USER, CHANGED_DATETIME, STATUS)
      values (:new_id_menu_price_list, :new_id_menu_head, :new_menu_name,
        'PRICE', 10, 1,24, null, 0.01, 0, :user_name, current_timestamp, 2);
  end
end
^

/* Alter (MENU_MODIFY_MULTILINE) */
ALTER PROCEDURE MENU_MODIFY_MULTILINE(ID_MENU_HEAD INTEGER,
ID_INVENT INTEGER,
ID_MENU_GROUP INTEGER,
MENU_GROUP_LINK INTEGER,
TAG INTEGER,
STOCK_LIST VARCHAR(4000),
OPERATION VARCHAR(10))
 RETURNS(ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
declare variable dbg varchar(2000);
  declare variable new_id integer;
  declare variable stock_name varchar(50);
  declare variable id_stock integer;
  declare variable id_menu_line integer;
begin
  err_code = 0;
  err_text = '';
  if (:operation not in('add', 'del', 'mod;mg', 'mod;mgl', 'mod;inv', 'mod;osn')) then begin
    err_code = -1;
    err_text = 'operation unknown !';
  end

  if (:operation = 'add') then begin
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:stock_list, ',') SSP
      into   :id_stock
    do begin
      select S.STOCK_NAME
      from   STOCK S
      where  S.ID_STOCK = :id_stock
      into   :stock_name;
  
      execute procedure MENU_MODIFY_LINE(null, :id_menu_head, :id_invent, :stock_name, :id_stock,
                        :id_menu_group, :menu_group_link, :tag, 'add')
      returning_values  :new_id, :err_code, :err_text;
      if (:err_code < 0) then exit;
    end
  end else if (:operation = 'del') then begin
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:stock_list, ',') SSP
      into   :id_menu_line
    do begin
      execute procedure MENU_MODIFY_LINE(:id_menu_line, null, null, null, null,
                        null, null, null, 'del')
      returning_values  :new_id, :err_code, :err_text;
      if (:err_code < 0) then exit;
    end
  end else if (:operation = 'mod;mgl') then begin
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:stock_list, ',') SSP
      into   :id_menu_line
    do begin
       update MENU_LINE ML
       set ML.MENU_GROUP_LINK = :menu_group_link,
           ML.TAG = :TAG
       where  ML.ID_MENU_LINE = :id_menu_line;
    end
  end else if (:operation = 'mod;mg') then begin
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:stock_list, ',') SSP
      into   :id_menu_line
    do begin
       update MENU_LINE ML
       set ML.ID_MENU_GROUP = :id_menu_group,
           ML.TAG = :TAG
       where  ML.ID_MENU_LINE = :id_menu_line;
    end
  end else if (:operation = 'mod;inv') then begin
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:stock_list, ',') SSP
      into   :id_menu_line
    do begin
       update MENU_LINE ML
       set ML.ID_INVENT = :ID_INVENT,
           ML.TAG = :TAG
       where  ML.ID_MENU_LINE = :id_menu_line;
    end
  end else if (:operation = 'mod;osn') then begin
    for
      select SSP.ITEM
      from   SYSTEM_STRING_PARSER(:stock_list, ',') SSP
      into   :id_menu_line
    do begin
       update MENU_LINE ML
       set ML.STOCK_NAME = (select S.STOCK_NAME from STOCK S where S.ID_STOCK = ML.ID_STOCK),
           ML.TAG = :TAG
       where  ML.ID_MENU_LINE = :id_menu_line;
    end
  end
end
^

/* Restore proc. body: IMPORT_MENU_HEAD$ENT */
ALTER PROCEDURE IMPORT_MENU_HEAD$ENT(MENU_NAME VARCHAR(50),
OBJECT_NAME VARCHAR(30),
COL_NO INTEGER,
ROW_NO INTEGER,
FONT_DATA BLOB,
UUID VARCHAR(40))
 RETURNS(ID_MENU_HEAD INTEGER,
ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
declare variable OPERATION varchar(3);
declare variable ID_OBJECT integer;
begin
  select mh.ID_MENU_HEAD
    from MENU_HEAD mh
    where mh.UUID = :UUID
    into :ID_MENU_HEAD;

  if (ID_MENU_HEAD is null) then begin
    select mh.ID_MENU_HEAD
      from MENU_HEAD mh
      where mh.MENU_NAME = :MENU_NAME
      into :ID_MENU_HEAD;
  end

  execute procedure LOOKUP_OBJECT$ENT(:OBJECT_NAME)
  returning_values :ID_OBJECT;

  OPERATION = 'add';
  if (ID_MENU_HEAD is not null) then begin
    OPERATION = 'mod';
    delete from MENU_LINE ml
      where ml.ID_MENU_HEAD = :ID_MENU_HEAD;
  end

  execute procedure MENU_MODIFY_MENU (null, :ID_MENU_HEAD, :OPERATION, :ID_OBJECT,
    :MENU_NAME, :COL_NO, :ROW_NO, :FONT_DATA)
  returning_values :ID_MENU_HEAD, :ERR_CODE, :ERR_TEXT;

  update MENU_HEAD mh
    set mh.UUID = :UUID
    where mh.ID_MENU_HEAD =: ID_MENU_HEAD;

  if (OPERATION = 'add') then begin
    delete from MENU_PRICE_LIST mpl
    where mpl.ID_MENU_HEAD = :ID_MENU_HEAD;
  end
end
^

/* Restore proc. body: IMPORT_MENU_LINE$ENT */
ALTER PROCEDURE IMPORT_MENU_LINE$ENT(ID_MENU_HEAD INTEGER,
INVENT_NAME VARCHAR(30),
STOCK_NAME VARCHAR(50),
MENU_STOCK_NAME VARCHAR(50),
MENU_GROUP_NAME VARCHAR(50),
MENU_GROUP_LINK_NAME VARCHAR(50),
TAG INTEGER)
 RETURNS(ID_MENU_LINE INTEGER,
ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
declare variable ID_INVENT integer;
declare variable ID_STOCK integer;
declare variable ID_MENU_GROUP integer;
declare variable ID_MENU_GROUP_LINK integer;
begin
  execute procedure LOOKUP_INVENT$ENT(:INVENT_NAME)
  returning_values :ID_INVENT;

  execute procedure LOOKUP_STOCK$ENT(:STOCK_NAME)
  returning_values :ID_STOCK;

  execute procedure LOOKUP_MENU_GROUP$ENT(:MENU_GROUP_NAME)
  returning_values :ID_MENU_GROUP;

  execute procedure LOOKUP_MENU_GROUP$ENT(:MENU_GROUP_LINK_NAME)
  returning_values :ID_MENU_GROUP_LINK;

  execute procedure MENU_MODIFY_LINE(null, :ID_MENU_HEAD, :ID_INVENT, :MENU_STOCK_NAME,
    :ID_STOCK, :ID_MENU_GROUP, :ID_MENU_GROUP_LINK, :TAG, 'add')
  returning_values :ID_MENU_LINE, :ERR_CODE, :ERR_TEXT;

  delete from MENU_LINE_PRICE mlp
  where mlp.ID_MENU_LINE = :ID_MENU_LINE;
end
^

/* Alter Procedure... */
/* Alter (IMPORT_MENU_HEAD$ENT) */
ALTER PROCEDURE IMPORT_MENU_HEAD$ENT(MENU_NAME VARCHAR(50),
OBJECT_NAME VARCHAR(30),
COL_NO INTEGER,
ROW_NO INTEGER,
FONT_DATA BLOB,
UUID VARCHAR(40))
 RETURNS(ID_MENU_HEAD INTEGER,
ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
declare variable OPERATION varchar(3);
declare variable ID_OBJECT integer;
begin
  select mh.ID_MENU_HEAD
    from MENU_HEAD mh
    where mh.UUID = :UUID
    into :ID_MENU_HEAD;

  if (ID_MENU_HEAD is null) then begin
    select mh.ID_MENU_HEAD
      from MENU_HEAD mh
      where mh.MENU_NAME = :MENU_NAME
      into :ID_MENU_HEAD;
  end

  execute procedure LOOKUP_OBJECT$ENT(:OBJECT_NAME)
  returning_values :ID_OBJECT;

  OPERATION = 'add';
  if (ID_MENU_HEAD is not null) then begin
    OPERATION = 'mod';
    delete from MENU_LINE ml
      where ml.ID_MENU_HEAD = :ID_MENU_HEAD;
  end

  execute procedure MENU_MODIFY_MENU (null, :ID_MENU_HEAD, :OPERATION, :ID_OBJECT,
    :MENU_NAME, :COL_NO, :ROW_NO, :FONT_DATA)
  returning_values :ID_MENU_HEAD, :ERR_CODE, :ERR_TEXT;

  update MENU_HEAD mh
    set mh.UUID = :UUID
    where mh.ID_MENU_HEAD =: ID_MENU_HEAD;

  if (OPERATION = 'add') then begin
    delete from MENU_PRICE_LIST mpl
    where mpl.ID_MENU_HEAD = :ID_MENU_HEAD;
  end
end
^

/* Alter (IMPORT_MENU_LINE$ENT) */
ALTER PROCEDURE IMPORT_MENU_LINE$ENT(ID_MENU_HEAD INTEGER,
INVENT_NAME VARCHAR(30),
STOCK_NAME VARCHAR(50),
MENU_STOCK_NAME VARCHAR(50),
MENU_GROUP_NAME VARCHAR(50),
MENU_GROUP_LINK_NAME VARCHAR(50),
TAG INTEGER)
 RETURNS(ID_MENU_LINE INTEGER,
ERR_CODE INTEGER,
ERR_TEXT VARCHAR(255))
 AS
declare variable ID_INVENT integer;
declare variable ID_STOCK integer;
declare variable ID_MENU_GROUP integer;
declare variable ID_MENU_GROUP_LINK integer;
begin
  execute procedure LOOKUP_INVENT$ENT(:INVENT_NAME)
  returning_values :ID_INVENT;

  execute procedure LOOKUP_STOCK$ENT(:STOCK_NAME)
  returning_values :ID_STOCK;

  execute procedure LOOKUP_MENU_GROUP$ENT(:MENU_GROUP_NAME)
  returning_values :ID_MENU_GROUP;

  execute procedure LOOKUP_MENU_GROUP$ENT(:MENU_GROUP_LINK_NAME)
  returning_values :ID_MENU_GROUP_LINK;

  execute procedure MENU_MODIFY_LINE(null, :ID_MENU_HEAD, :ID_INVENT, :MENU_STOCK_NAME,
    :ID_STOCK, :ID_MENU_GROUP, :ID_MENU_GROUP_LINK, :TAG, 'add')
  returning_values :ID_MENU_LINE, :ERR_CODE, :ERR_TEXT;

  delete from MENU_LINE_PRICE mlp
  where mlp.ID_MENU_LINE = :ID_MENU_LINE;
end
^

SET TERM ; ^

