select o.buy_price, o.whole_price, o.retail_price,
       cast(c.buy_price+mw.margin as integer) as catalog_whole_price,
       rp.o_retail_price as catalog_retail_price
from trd_orders_units ou
join trd_orders o on o.id=ou.id_order
join trd_catalog c on c.id=o.id_catalog
join trd_product_types pt on pt.id=c.id_product_type
left join trd_margin_wholesale mw on mw.id_firm=c.id_firm and mw.id_product_type=c.id_product_type
left join p_get_retail_price(o.order_num, o.id_catalog, c.id_firm, c.buy_price+mw.margin) rp on 1=1
where o.order_num=:order_num

create or alter procedure P_GET_RETAIL_PRICE (
    I_ORDER_NUM D_INTEGER,
    I_ID_CATALOG D_ID,
    I_ID_FIRM D_ID,
    I_WHOLESALE_PRICE D_PRICE)
returns (
    O_RETAIL_PRICE D_PRICE)
as
declare variable V_MARGIN D_PRICE;
declare variable V_QUANTITY D_INTEGER;
begin
  select sum(ou.quantity)
  from trd_orders o
  join trd_orders_units ou on ou.id_order=o.id
  where o.order_num=:i_order_num
    and o.id_catalog=:i_id_catalog
  into :v_quantity;

  select m.margin
  from trd_margin_retail m
  where m.id_firm=:i_id_firm
    and m.quantity=(select max(m.quantity)
                    from trd_margin_retail m
                    where m.id_firm=:i_id_firm and m.quantity<=:v_quantity)
  into :v_margin;
  o_retail_price=i_wholesale_price+v_margin;
  suspend;
end

ALTER TABLE TRD_MARGIN_RETAIL ADD CONSTRAINT UNQ1_TRD_MARGIN_RETAIL UNIQUE (ID_FIRM, QUANTITY);

  select sum(ou.quantity)
  from trd_orders o
  join trd_orders_units ou on ou.id_order=o.id
  where o.order_num=:i_order_num
    and o.id_catalog=:i_id_catalog
  into :v_quantity;