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