问题产生的原因，在商品资料未发送到之前，配送单、调拨单、返库单的审核产生此问题。
begin tran
declare @outlet_id varchar(3),
@store_id varchar(12)
select @store_id='00000000'
select @outlet_id='D01'
--对于门店
--1、插入 配送单和调入单 stk_fifo 中不存在的mis_bill_fifo批次
-- select d.* from quota_m m,mis_bill_fifo d
-- where m.quota_bi=d.bill_bi and m.status='2' and d.fifo_id not in(select fifo_id from stk_fifo)
--配送单--添加
insert into stk_fifo(fifo_id,aid,store_id,supplier_id,q,quota_in_q,tax_entry_price,entry_stock_bi,duty_paragraph)
--select @fifo_id,@aid,@store_id,@sup_id,@q,@q,@bill_tax_price,@bill_bi,duty_paragraph from article where aid=@aid
select d.fifo_id,d.aid,@store_id,supplier_id,q,q,t_fifo_price,'','' from quota_m m,mis_bill_fifo d
where m.quota_bi=d.bill_bi and m.status='2' and rtrim(d.fifo_id)+d.aid not in(select rtrim(fifo_id)+aid from stk_fifo)
--配送单--修改数量 select * from stk_fifo
update stk_fifo
set quota_in_q=a.q
from
(
select d.fifo_id,d.aid,sum(q) as q
from quota_m m,mis_bill_fifo d
where m.quota_bi=d.bill_bi and m.status='2'
group by d.fifo_id,d.aid
)a
where stk_fifo.fifo_id=a.fifo_id and stk_fifo.aid=a.aid
--调入单--添加
insert into stk_fifo(fifo_id,aid,store_id,supplier_id,q,exchange_in_q,tax_entry_price,entry_stock_bi,duty_paragraph)
select d.fifo_id,d.aid,@store_id,supplier_id,q,q,t_fifo_price,'',''
from transfer_m m,mis_bill_fifo d
where m.transfer_bi=d.bill_bi and m.status='2' and
in_outlet_id=@outlet_id and rtrim(d.fifo_id)+d.aid not in(select rtrim(fifo_id)+aid from stk_fifo)
--调入单--修改数量
update stk_fifo
set exchange_in_q=a.q
from
(
select d.fifo_id,d.aid,sum(q) as q
from transfer_m m,mis_bill_fifo d
where m.transfer_bi=d.bill_bi and m.status='2' and in_outlet_id=@outlet_id
group by d.fifo_id,d.aid
)a
where stk_fifo.fifo_id=a.fifo_id and stk_fifo.aid=a.aid
--调整库存数
update stk_fifo
set q = isnull(quota_in_q,0) + isnull(exchange_in_q ,0) + isnull( entry_q,0)
-isnull(return_q,0) - isnull(exchange_out_q,0) - isnull( quota_out_q,0)
-isnull(sale_q,0) - isnull(profit_loss_q,0) - isnull(inv_profit_q,0)
declare
@aid varchar(8),
@stock_q decimal(13,4) ,
@fifo_tax_entry_a decimal(13,4)
select @fifo_tax_entry_a=0
declare cur_stock cursor for
select b.aid,b.stock_q from
(
select aid,sum(q) as q from stk_fifo group by aid) a,
(
select aid, sum(stock_q) as stock_q from article_storespace group by aid) b
where a.aid=b.aid and a.q<>b.stock_q
open cur_stock
fetch cur_stock into @aid,@stock_q
while @@fetch_status=0
begin
execute usp_stk_adjust_fifo_use_stock_q_no_inv @store_id,@aid,@stock_q,'Y',@fifo_tax_entry_a output
fetch cur_stock into @aid,@stock_q
end
close cur_stock
deallocate cur_stock
select b.aid,b.stock_q from
(
select aid,sum(q) as q from stk_fifo group by aid) a,
(
select aid, sum(stock_q) as stock_q from article_storespace group by aid) b
where a.aid=b.aid and a.q<>b.stock_q
rollback