create or replace
package app_utils
as

function is_monday( p_date in date)
return boolean
;

end app_utils;
/

create or replace
package body app_utils
as

function is_monday( p_date in date)
return boolean
is
  l_day varchar2(10);
begin
  select to_char( p_date, 'DAY')
  into   l_day
  from   dual
  ;
  return l_day='MONDAY';
exception
  when others
  then
    return false;  
end is_monday;

end app_utils;
/

create or replace
package app_product
as
	
function is_onhotlist( p_product_id in number)
return boolean
;

function get_standard_product_discount
return number
;

function get_hotlist_product_discount
return number
;

function get_product_price( p_product_id in number)
return number
;

end app_product;
/

create or replace
package body app_product
as

function is_onhotlist( p_product_id in number)
return boolean
is
begin
  if p_product_id < 20
  then
    return true;
  else
    return mod(p_product_id,3) = 0;
  end if;    
end is_onhotlist;

function get_standard_product_discount
return number
is
begin
  return 8;
end get_standard_product_discount;

function get_hotlist_product_discount
return number
is
begin
  return 17;
end get_hotlist_product_discount;

function get_product_price( p_product_id in number)
return number
is
begin
  -- some non-sensical formula to calculate the product price
  return 145 * mod(p_product_id ,5) + 13* to_number(to_char(sysdate ,'MI'));
end get_product_price;

end app_product;
/

create or replace 
package app_sales
is

function get_quote
( p_product_id in number
, p_quantity  in number
, p_delivery_date in date
, p_currency in varchar2 default 'eur'
) return number
;
end;
/


create or replace 
package body app_sales
is

function get_quote
( p_product_id in number
, p_quantity  in number
, p_delivery_date in date
, p_currency in varchar2 default 'eur'
) return number
is
  l_discount number(3); -- discount percentage
  l_date_discount number(3); -- discount percentage because of the delivery date (deliveries on Monday are cheaper)
  l_product_discount number(3); -- discount percentage - some products are heavily pushed
  l_standard_price number(10,2); -- price as calculated in euros without applying any discounts
  l_price number(10,2); -- price after applying discounts
  l_quote number(10,2); -- quote after currency conversion of price
begin
  l_discount:= 10;
  -- monday deliveries get an extra 15% discount
  if app_utils.is_monday(p_delivery_date)
  then
      l_date_discount := 15;
  else      
      l_date_discount := 0;
  end if;
  -- a special discount rate is used for products on the hotlist; if the product is not on the hotlist, 
  -- then the standard discount rate applies that is used for all products ordered through the webservice
  if app_product.is_onhotlist(p_product_id)
  then
    l_product_discount:= app_product.get_hotlist_product_discount;
  else
    l_product_discount:= app_product.get_standard_product_discount;
  end if;
  l_discount:= l_discount + l_date_discount + l_product_discount;
  l_standard_price:= p_quantity * app_product.get_product_price( p_product_id);
  -- calculate the price as the standard_price times the discount-percentage
  l_price:= l_standard_price * (100 - l_discount)/100;
  -- price is calculated in Euros; the quote may hvan been requested in a different currency (p_currency)
  -- and must be converted in that case
  l_quote:= round(100*l_price * app_currency.get_conversion_rate('eur', p_currency))/100;
  return l_quote;
end get_quote;

end app_sales;
/

create or replace 
package app_currency
is

function get_conversion_rate
( p_source_currency in varchar2 default 'eur'
, p_target_currency in varchar2
) return number
;

end app_currency;
/


create or replace 
package body app_currency
is

function get_conversion_rate
( p_source_currency in varchar2 default 'eur'
, p_target_currency in varchar2
) return number
is
begin
  if p_source_currency = 'eur'
  then
    if p_target_currency = 'usd'
    then  
      return 1.22;
    elsif p_target_currency = 'gbp'
    then
      return 0.65;
    else 
      return 1;
    end if;
  elsif p_source_currency = 'usd'
  then
    if p_target_currency = 'eur'
    then  
      return 1/1.22;
    elsif p_target_currency = 'gbp'
    then
      return 0.65/1.22;
    else 
      return 1;
    end if;
  else
    return 1;
  end if;
end get_conversion_rate;

end app_currency;
/


SQL> select app_sales.get_quote( 9, 23, sysdate+13, 'usd')
  2  from dual
  3  /

APP_SALES.GET_QUOTE(9,23,SYSDATE+13,'USD')
------------------------------------------
                                  24928.78