Oracle 如何基于评估系统创建FIFO报告

我想在我的库存中实现FIFO库存模型。
所以我面临一些问题，但在盯着查询之前，我描述了我的表的物理结构。

create table purchase_mas
(
stock_id        number 
pur_date date,
product_code number,
product_name varchar2(10),
stock_qty  number,
stock_rate      number(7,2)
);

create table sale_mas
(
sale_id        number 
sale_date date,
product_code number,
product_name varchar2(10),
sale_qty  number,
sale_rate number(7,2)
);

insert into purchase_mas values (1,'01-Jan-17',1,'A',10,110);
insert into purchase_mas values (2,'02-Jan-17',1,'A',20,120);
insert into purchase_mas values (3,'02-Jan-17',2,'B',5,60);
insert into purchase_mas values (4,'03-Jan-17',1,'A',12,70);
insert into purchase_mas values (5,'05-Jan-17',3,'C',30,130);
insert into purchase_mas values (6,'06-Jan-17',2,'B',12,90);

commit;


insert into sale_mas values (11,'03-Jan-17',1,'A',4,130);
insert into sale_mas values (12,'03-Jan-17',1,'A',5,140);
insert into sale_mas values (13,'04-Jan-17',1,'A',7,160);

commit;
复制

我想喜欢以下销售报告。
但是下面的专栏只是为了理解目的。
actual_sal_qty，stock_id，remaing_stock，actual_stk_qty，actual_stk_qty_after_sale。

sale_id sale_date product_code product_name qty rate actual_sal_qty stock_id remaing_stock actual_stk_qty actual_stk_qty_after_sale stock_rate
11 03-Jan-17 1                A 4 130 4            1            6  30       26    110
12 03-Jan-17 1         A 5 140 5            1            1  26       21    110
13 04-Jan-17 1                A 7 160 1            1            0  21       14    110
13 04-Jan-17 1                A 7 160 6            2            14  21       14    120
复制

基本先进先出股票模型是如何工作的？
我的库存开始日期是01-1月-17产品A有30数量。
假设我在03-1月-17销售数量为130美元的产品A的数量为4。
当我运行销售报告时，我想知道我的库存价格，因此销售Id 11能够从库存id 1中获得数量。
库存Id 1剩余6数量，库存id 2剩余20数量，库存率为110。

当我在同一天向其他客户出售同一产品A的数量5时，
这里的股票id 1能够给出5个数量。
当我看到销售报告时，股票利率是110的。

当我在03-1月-17购买数量为12的产品，但不影响销售报告时，

当我在04-1月-17销售相同产品A的数量为7时，
在这里，股票id 1只能提供1个数量。
股票id 2能够提供6个数量
当我看到销售报告时，库存率是不同的，所以库存率是 (( 110*1) (120*6))/7这里7是销售数量。
库存率为118.58。


如何使用plsql或游标编程或选择查询。

因此，您想以先进先出的方式将销售分配给股票购买吗？即，销售总是消耗您首先拥有的最旧的股票？

如果是这样，你可以做这样的事情:

-首先，计算库存和销售额的运行总数，以及直到每一行的总计。您可以通过以下方式找到先前的总数:

rows between unbounded preceding and 1 preceding
复制

在运行和的window子句中。然后加入在产品代码上加入的库存和销售数据。还过滤:

-当前库存总计> = 先前的销售总计和
-当前销售总额> = 以前的库存总额

with stock as (
  select p.*, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and current row
         ), stock_qty) run_tot
  from   purchase_mas p
), sales as (
  select s.*, 
         sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and current row
         ) run_tot, 
         nvl(sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot 
  from   sale_mas s
)
  select pur_date, sale_date, stock_qty, sale_qty,
         sa.prev_run_tot sale_prev, sa.run_tot sale_tot, 
         st.prev_run_tot stock_prev, st.run_tot stock_tot
  from   stock st
  join   sales sa
  on     st.product_code = sa.product_code
  and    sa.run_tot >= st.prev_run_tot
  and    st.run_tot >= sa.prev_run_tot
  order  by pur_date, sale_date, sale_id;

PUR_DATE              SALE_DATE             STOCK_QTY  SALE_QTY  SALE_PREV  SALE_TOT  STOCK_PREV  STOCK_TOT  
01-JAN-0017 00:00:00  03-JAN-0017 00:00:00  10         4         0          4         0           10         
01-JAN-0017 00:00:00  03-JAN-0017 00:00:00  10         5         4          9         0           10         
01-JAN-0017 00:00:00  04-JAN-0017 00:00:00  10         7         9          16        0           10         
02-JAN-0017 00:00:00  04-JAN-0017 00:00:00  20         7         9          16        10          30         
复制

然后为每行分配正确的数量，返回最小的:

-销售数量
-库存数量
-整体销售总额-前v库存总额
-总库存总额-前一个销售总额

要涵盖当前销售额与当前库存总额相匹配的情况，还应过滤此计算结果> 0的情况。

with stock as (
  select p.*, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and current row
         ), stock_qty) run_tot
  from   purchase_mas p
), sales as (
  select s.*, 
         sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and current row
         ) run_tot, 
         nvl(sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot 
  from   sale_mas s
)
  select pur_date, sale_date, stock_qty, sale_qty,
         sa.prev_run_tot, sa.run_tot, st.prev_run_tot, st.run_tot,
         least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) qty
  from   stock st
  join   sales sa
  on     st.product_code = sa.product_code
  and    sa.run_tot >= st.prev_run_tot
  and    st.run_tot >= sa.prev_run_tot
  and    least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) > 0
  order  by pur_date, sale_date, sale_id;

PUR_DATE              SALE_DATE             STOCK_QTY  SALE_QTY  PREV_RUN_TOT  RUN_TOT  PREV_RUN_TOT  RUN_TOT  QTY  
01-JAN-0017 00:00:00  03-JAN-0017 00:00:00  10         4         0             4        0             10       4    
01-JAN-0017 00:00:00  03-JAN-0017 00:00:00  10         5         4             9        0             10       5    
01-JAN-0017 00:00:00  04-JAN-0017 00:00:00  10         7         9             16       0             10       1    
02-JAN-0017 00:00:00  04-JAN-0017 00:00:00  20         7         9             16       10            30       6 
复制

注意: 这假设您始终可以将销售分配给下一个可用库存，即使它在销售后到达和/或只有在有库存的情况下才能进行销售。也就是说，您没有10个库存单位可用的情况，而是卖出20个。或者你可以，但是你把出售剩下的10个分配给下一次股票购买，即使这在几周后到达...

HT给金·伯格·汉森 (Kim Berg Hansen) 撰写的FIFO分析文章，为这个答案提供了基础:
http://www.kibeha.dk/2012/11/analytic-fifo-multiplied-part-1.html