Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Products (
  ProdId number generated always as identity primary key
, ProdName varchar2(20) not null
);
create table Stores (
  StoreId number generated always as identity primary key
, StoreName varchar2(20) not null
);
create table Customers (
  CustomerId number generated always as identity primary key
, CustomerName varchar2(20) not null
);
create table Prices (
  PriceId number generated always as identity primary key
, ProdId number not null
, Price number
, ValidFrom date default on null sysdate
, constraint fk_Prices_Product foreign key (ProdId) references Products (ProdId)
);
create unique index uniq_prices_product_price on Prices (ProdId, ValidFrom);
create table Orders (
  OrderId number generated always as identity primary key
, CustomerId number not null
, StoreId number not null
, OrderedAt date default on null sysdate
, constraint fk_Orders_Customer foreign key (CustomerId) references Customers (CustomerId)
, constraint fk_Orders_Store foreign key (StoreId) references Stores (StoreId)
);
create table OrderLines (
  OrderLineId number generated always as identity primary key
, OrderId number not null
, ProdId number not null
, ProdQuantity number not null
, constraint fk_OrderLines_Order foreign key (OrderId) references Orders (OrderId)
, constraint fk_OrderLines_Prod foreign key (ProdId) references Products (ProdId)
);
create table Payments (
  PaymentId number generated always as identity primary key
, OrderId number not null
, PaidAt date default on null sysdate
, PaidAmount number not null
, constraint fk_Payments_Order foreign key (OrderId) references Orders (OrderId)
);
create view Prices_V as
select
  p.*
, coalesce(
    lead(p.ValidFrom) over (partition by p.ProdId order by p.ValidFrom)
  , to_date('9999', 'YYYY')
  ) ValidTo
from Prices p;
create view Orders_V as
select
  o.*
, (
    select sum(ol.ProdQuantity * p.Price)
    from OrderLines ol
    join Prices_V p on (p.ProdId = ol.ProdId and o.OrderedAt between p.ValidFrom and p.ValidTo)
    where o.OrderId = ol.OrderId
  ) Total
, (
    select sum(PaidAmount)
    from Payments p
    where p.OrderId = o.OrderId
  ) TotalPaid
from Orders o
insert into Products(ProdName)
select 'Prod A' from dual union all
select 'Prod B' from dual;
2 rows affected

insert into Stores(StoreName) values ('Store A');
1 rows affected

insert into Customers(CustomerName) 
select 'Customer A' from dual union all
select 'Customer B' from dual;
2 rows affected

insert into Prices(ProdId, Price, ValidFrom)
select 1, 10, sysdate - 10 from dual union all
select 1, 12, sysdate - 2 from dual union all
select 1, 14, sysdate + 3 from dual union all
select 2, 100, sysdate - 10 from dual union all
select 2,  90, sysdate - 2 from dual union all
select 2,  null, sysdate + 5 from dual;
6 rows affected

insert into Orders(CustomerId, StoreId, OrderedAt)
select 1 cid, 1 stoid, sysdate - 5 from dual union all
select 2, 1, sysdate - 5 from dual union all
select 2, 1, sysdate - 1 from dual;
3 rows affected

insert into OrderLines(OrderId, ProdId, ProdQuantity)
select 1 ordid, 1 prodid, 3 prodquant from dual union all
select 1, 2, 2 from dual union all
select 2, 2, 10 from dual union all
select 3, 2, 10 from dual;
4 rows affected

insert into Payments(OrderId, PaidAmount) values (2, 500);
1 rows affected

select * from Prices_V order by ProdId, ValidFrom;


PRICEID
PRODID
PRICE
VALIDFROM
VALIDTO


1
1
10
16-JAN-21
24-JAN-21

2
1
12
24-JAN-21
29-JAN-21

3
1
14
29-JAN-21
01-JAN-99

4
2
100
16-JAN-21
24-JAN-21

5
2
90
24-JAN-21
31-JAN-21

6
2
null
31-JAN-21
01-JAN-99


select * from OrderLines order by OrderId, ProdId;


ORDERLINEID
ORDERID
PRODID
PRODQUANTITY


1
1
1
3

2
1
2
2

3
2
2
10

4
3
2
10


select * from Orders_v order by OrderId;


ORDERID
CUSTOMERID
STOREID
ORDEREDAT
TOTAL
TOTALPAID


1
1
1
21-JAN-21
230
null

2
2
1
21-JAN-21
1000
500

3
2
1
25-JAN-21
900
null
PRICEID	PRODID	PRICE	VALIDFROM	VALIDTO
1	1	10	16-JAN-21	24-JAN-21
2	1	12	24-JAN-21	29-JAN-21
3	1	14	29-JAN-21	01-JAN-99
4	2	100	16-JAN-21	24-JAN-21
5	2	90	24-JAN-21	31-JAN-21
6	2	null	31-JAN-21	01-JAN-99
ORDERID	CUSTOMERID	STOREID	ORDEREDAT	TOTAL	TOTALPAID
1	1	1	21-JAN-21	230	null
2	2	1	21-JAN-21	1000	500
3	2	1	25-JAN-21	900	null