Lets start by pricing. It is always a good practice to have multiple pricelists to be free to choose the pricing scheme for each individual customer or POS. So we create the pricelist table:
/*Price list definition *pr1_kind: S if this pricelist is used in Wholesale Sales R is this pricelist is used in Retail Sales P if refers to products purchased C if it refers to product Cost. Cost prices are updated by a costing procedure. Not in the scope of this thread. */ create table pricelist1 ( pr1_code number(10) not null pr1_name varchar2(30) not null, pr1_kind varchar2(1) not null, pr1_currency varchar2(3) not null, primary key(pr1_code) );
On each pricelist we should have the prices per item. In order to have price history we add a date field that keeps the date the price starts to be effective. A new record for the same product at another date will change the price. For example product 1 has the price of 10$ up to 13/6/2010. After that date the price should be 12$ (nothing is getting cheaper these days…). To handle this case we should add 2 records:
1/1/2000, product 1, 10$ (a very old date)
13/6/2010, product 1, 12$
Now the prices table:
/* Pricelist Prices * pr2_date: The date the price is effective. */ create table pricelist2 ( pr2_code number(10) not null references pricelist1(pr1_code), pr2_date date not null pr2_itemno number(10) not null, pr2_price number(10,2) not null, primary key(pr2_code, pr2_date, pr2_itemno) );
Because there are many prices per product, when looking for THE price, we should first know the pricelist.
This can be found in many ways depending on our needs. As an example when selling to a customer we can define
at the customer table the preferred pricelist to use:
create table customer ( cus_code number(10) not null, ...... cus_prlist number(10) not null references pricelist1(pr1_code) ..... );
If on the other hand we have a retail store we can define the preferred pricelist at the store level.
In order to simplify code, we will create a PL/SQL (I only use Oracle….) package to handle prices:
create or replace package prices_pkg as function GetPrice(pPrList in pricelist1.pr1_code%type, pDate in date, pItemNo in pricelist2.pr2_itemno%type ) return pricelist2.pr2_price%type; end; create or replace package body prices_pkg as function GetPrice(pPrList in pricelist1.pr1_code%type, pDate in date, pItemNo in pricelist2.pr2_itemno%type ) return pricelist2.pr2_price%type is cursor c1 is select pr2_price from pricelist2, pricelist1 where pr2_code = pPrList and pr2_date <= pDate and pr2_itemno = pItemNo and pr1_code = pr2_code order by pr2_date desc; f1 c1%rowtype; begin --Fetch only one value. This will fetch the last record before pDate date. open c1; fetch c1 into f1; close c1; return f1.pr2_price; end; end;
The SQL to get the current prices for some products will be:
select prd_itemno, prd_descr, pricelist_pkg.GetPrice(1, sysdate, prd_itemno) as price from products where .....
So simple. Another bonus of this scheme:We can anytime change the GetPrice function to handle more complex situations. For example if we need the price always in local currency, we can add a Currency->LCY transforamtion in the function without changing a single line of code in the application.
I will answer the other parts in another post.