-- Chronological inventory of purchases (-) and sales (+)
create table inventory (
id int not null,
settleDate date not null,
qty int not null,
cost decimal(4,2) null,
position int not null);
-- Hierarchy for fifo match
create table f_fifo (
parent_id int null,
node_id int not null,
ord int not null,
settleDate date not null,
qty int not null,
cost decimal(4,2) not null);
create sequence ord start with 1 increment by 1;
insert into inventory values(1004286312,to_date('10/01/2019','MM/DD/YYYY'),-100,10.00,-100);
insert into inventory values(1004286313,to_date('10/03/2019','MM/DD/YYYY'),-100,11.00,-100);
insert into inventory values(1004286314,to_date('10/05/2019','MM/DD/YYYY'), 150,null, 150);
insert into inventory values(1004286315,to_date('10/06/2019','MM/DD/YYYY'),-100,10.80,-100);
insert into inventory values(1004286316,to_date('10/07/2019','MM/DD/YYYY'), 50,null, 50);
create or replace procedure sp_fifo (in sId int)
as
begin
declare sPosition, diff, pPosition, pId int;
declare sSettleDate date;
declare pCost decimal(10,2);
-- The oldest purchase (fifo)
select id, position, cost
into pId, pPosition, pCost default null, 0, null
from inventory where id =
(select min(id)
from inventory where position < 0)
for update;
-- The sale
select position, settleDate
into sPosition, sSettleDate default 0, null
from inventory where id = :sId for update;
if :pPosition < 0 and :sPositon > 0 then
diff = greatest (:sPosition + :pPosition, 0);
update inventory set position = position + (:sPosition - :diff) where id = :pId;
update inventory set position = :diff where id = :sId;
insert into f_fifo values (:pId,:sId,ord.nextval,:sSettleDate, :sPosition - :diff,:pCost);
commit;
call sp_fifo (:sId);
end if;
end;
create or replace procedure sp_unwind (in sId int)
as
begin
using sqlscript_print as prtlib;
using sqlscript_string as strlib;
declare msg nvarchar(5000) =
strlib:format('recursion limit {} exceeded. continuing.',32);
-- Restart fifo if recursion limit reached.
declare continue handler for sql_error_code 1340
begin
prtlib:print_line(:msg);
call sp_fifo(:sId);
end;
call sp_fifo(:sId);
end;
call sp_unwind (1004286314); -- unwind the first sale
call sp_unwind (1004286316); -- unwind the second sale
id settleDate ps qty cost
---------- ---------- -------- ---- -----
1004286312 2019-10-01 purchase -100 10.00
1004286313 2019-10-03 purchase -100 11.00
1004286314 2019-10-05 sale 150
1004286315 2019-10-06 purchase -100 10.80
1004286316 2019-10-07 sale 50
create view h_fifo as
select *
from hierarchy (
source (
select * from f_fifo
union all
select null as parent_id, id as node_id, id as ord, settleDate, qty, cost
from inventory where qty < 0)
sibling order by ord);
ps parent_id node_id settleDate qty cost
-------- ---------- ---------- ---------- ---- -----
purchase null 1004286312 2019-10-01 -100 10.00
purchase null 1004286313 2019-10-03 -100 11.00
sale 1004286312 1004286314 2019-10-05 100 10.00
sale 1004286313 1004286314 2019-10-05 50 11.00
purchase null 1004286315 2019-10-06 -100 10.80
sale 1004286313 1004286316 2019-10-07 50 11.00
select
case when hierarchy_aggregate_type = 0 then 'entry' else 'balance' end "type",
case when hierarchy_level = 1 then 'purchase'
when hierarchy_level != 1 and hierarchy_aggregate_type = 0 then 'sale'
else null end "ps",
node_id "id",
settleDate "settleDate",
qty "qty",
cost "cost",
qty*cost "notional",
balance "balance"
from hierarchy_descendants_aggregate (
source h_fifo
measures (
sum(qty*cost) as balance
) with total null
) order by hierarchy_aggregate_type,settleDate,ord;
type ps id settleDate qty cost notional balance
------- -------- ----------- ---------- ---- ----- -------- --------
entry purchase 1004286312 2019-10-01 -100 10.00 -1000.00 0.00
entry purchase 1004286313 2019-10-03 -100 11.00 -1100.00 0.00
entry sale 1004286314 2019-10-05 100 10.00 1000.00 1000.00
entry sale 1004286314 2019-10-05 50 11.00 550.00 550.00
entry purchase 1004286315 2019-10-06 -100 10.80 -1080.00 -1080.00
entry sale 1004286316 2019-10-07 50 11.00 550.00 550.00
balance -1080.00
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
6 | |
4 | |
3 | |
3 | |
3 | |
2 | |
2 | |
2 |