海典H1 赠品库存(赠品有库存但是会员积分兑换显示0）

wbv123

ALTER trigger [dbo].[tr_u_pst_out_u] on [dbo].[u_pst_out] for insert,update,delete
as
declare @trigger varchar(8000)
declare @2808 varchar(10),@status_i int,@status_d int
declare @busno varchar(10),@pstid varchar(20),@makeno varchar(100),@invalidate datetime,@newqty numeric(16,6)
declare @newawaitqty numeric(16,6),@newprice numeric(16,4),@ifavg int ,@groupid varchar(10),@pstqty numeric(16,6)
declare @3060 varchar(10)
declare @pstoutno varchar(20)

set nocount on

if object_id(‘tempdb..#disable_u_pst_out’) is not null return

select @status_i = status from inserted
select @status_d = status from deleted

if update(pstoutno) and (@status_i = 0 and @status_d = 0)
begin
rollback
raiserror(‘不允许修改赠品出库单号!’,16,1)
return
end

if exists(select * from deleted where status <> 0)
begin
rollback
raiserror(‘只允许修改未生效的赠品出库单!’,16,1)
return
end

select pstoutno,busno,pstid,makeno,pstqty into #pstdist_c1 from inserted where outtype not like ‘%盘点%’
insert into #pstdist_c1 select pstoutno,busno,pstid,makeno,0 – pstqty from deleted where outtype not like ‘%盘点%’

select pstoutno,busno,pstid,makeno,sum(pstqty) as pstqty into #pstdist_c2 from #pstdist_c1 group by pstoutno,busno,pstid,makeno
–生效和作废，都要减去待出库
if (@status_i = 1 and @status_d = 0) or (@status_i = 2 and @status_d = 0)
begin
insert into #pstdist_c2 select pstoutno,busno,pstid,makeno,0 – pstqty from inserted where outtype not like ‘%盘点%’
end
–删除数量为0的资料
delete #pstdist_c2 where pstqty = 0

–处理待出库
IF exists(select * from #pstdist_c2 b )
BEGIN
select @ifavg=0,@newqty=0

declare await_pst cursor local for
select b.busno,b.pstid,b.makeno,b.pstqty
from #pstdist_c2 b
open await_pst
fetch await_pst into @busno,@pstid,@makeno,@newawaitqty

while @@fetch_status = 0
Begin
exec p_inout_pst @busno , @pstid , @makeno, @invalidate ,@newqty , @newawaitqty , @newprice , @ifavg
if @@error <> 0
begin
CLOSE await_pst;
DEALLOCATE await_pst;
rollback
raiserror(‘执行p_inout_pst增加库存失败’,16,1)
return
end
fetch await_pst into @busno,@pstid,@makeno,@newawaitqty
End

CLOSE await_pst;

DEALLOCATE await_pst;   

End

–生效,增加库存
IF exists(select * from deleted a,inserted b where a.pstoutno = b.pstoutno and a.status = 0 and b.status = 1)
BEGIN
–插入库存分布记录
select @ifavg=0,@newawaitqty=0

select b.pstoutno,b.busno,b.pstid,b.makeno,sum(b.pstqty) as pstqty,sum(b.checkqty) as checkqty,b.outtype,case when b.outtype like ‘%盘点%’ then ‘group1’ else ‘group2’ end as groupid
into #pstout
from deleted a,inserted b
where a.pstoutno = b.pstoutno and a.status = 0 and b.status = 1
group by b.pstoutno,b.busno,b.pstid,b.makeno,b.outtype

select @2808 = para from c_sys_ini where ini =’2808′
select @3060 = para from c_sys_ini where ini =’3060′

if @3060 = ‘0’
begin
If exists(select * from #pstout a left join u_pst_store b on a.busno = b.busno and a.pstid = b.pstid where a.pstqty > b.pstqty and a.groupid = ‘group2′) and @2808=’0’
Begin
rollback
raiserror(‘赠品出库单所示的品种在指定业务机构库存数量不够出库!’,16,1)
return
End
end
else
begin
If exists(select * from #pstout a left join u_pst_store_c b on a.busno = b.busno and a.pstid = b.pstid and a.makeno = b.makeno where a.pstqty > b.pstqty and a.groupid = ‘group2′) and @2808=’0’
Begin
rollback
raiserror(‘赠品出库单所示的品种在指定业务机构库存数量不够出库!’,16,1)
return
End
end

declare inout_pst cursor local for
select b.busno,b.pstid,b.makeno, -1 * b.pstqty,b.groupid,b.pstoutno
from #pstout b;
open inout_pst
fetch inout_pst into @busno,@pstid,@makeno,@newqty,@groupid ,@pstoutno

while @@fetch_status = 0
Begin
–盘点
if @groupid = ‘group1’
begin
–不按批号管理(批号为空),总库存减去盘点库存为需要出库数量
if isnull(@makeno,”) = ”
select @pstqty = b.pstqty – a.checkqty from #pstout a,u_pst_store b where a.busno = b.busno and a.pstid = b.pstid and a.pstoutno = @pstoutno
–按批号管理(批号不为空),批号库存减去盘点库存为需要出库数量
else
select @pstqty = b.pstqty – a.checkqty from #pstout a,u_pst_store_c b where a.busno = b.busno and a.pstid = b.pstid and a.makeno = b.makeno and a.pstoutno = @pstoutno

    set @newqty = -1 * @pstqty

    --回写出库数量

    update A

    set pstqty= @pstqty

    from u_pst_out a,#pstout b

    where a.busno = b.busno and a.pstid = b.pstid  

    and a.pstoutno=b.pstoutno and a.pstoutno = @pstoutno

    if @@error <> 0

    begin

        rollback

        raiserror('盘点增加赠品库存失败1!',16,1)

        return

    end

end  

exec p_inout_pst  @busno ,  @pstid ,      @makeno,  @invalidate ,@newqty ,  @newawaitqty ,  @newprice ,  @ifavg      

if @@error <> 0      

  begin   

    CLOSE inout_pst;

    DEALLOCATE inout_pst;  

        rollback      

        raiserror('执行p_inout_pst增加库存失败',16,1)   

        return      

end      

fetch inout_pst into @busno,@pstid,@makeno,@newqty,@groupid,@pstoutno   

End

CLOSE inout_pst;
DEALLOCATE inout_pst;

drop table #pstout

End

/*–生效,增加库存
select @2808 = para from c_sys_ini where ini =’2808′

if exists(select * from deleted a,inserted b where a.pstoutno = b.pstoutno and a.status = 0 and b.status = 1)

BEGIN
select b.pstoutno,b.busno,b.pstid,sum(b.pstqty) as pstqty,sum(b.checkqty) as checkqty,b.outtype,case when b.outtype like ‘%盘点%’ then ‘group1’ else ‘group2’ end as groupid
into #pstout
from deleted a,inserted b
where a.pstoutno = b.pstoutno and a.status = 0 and b.status = 1
group by b.busno,b.pstid,b.outtype,b.pstoutno

    select  busno,pstid,sum(pstqty) as pstqty,sum(checkqty) as checkqty, groupid   into #pstout_sum from  #pstout

    group  by  busno,pstid,groupid

If exists(select * from #pstout a left join u_pst_store b on a.busno = b.busno and a.pstid = b.pstid where b.pstqty is null and a.outtype not like ‘%盘点%’)
Begin
rollback
raiserror(‘赠品出库单所示的品种在指定业务机构没有库存记录!’,16,1)
return
End

If exists(select * from #pstout a left join u_pst_store b on a.busno = b.busno and a.pstid = b.pstid where a.pstqty > b.pstqty and b.pstqty is not null and a.outtype not like ‘%盘点%’) and @2808=’0′
Begin
rollback
raiserror(‘赠品出库单所示的品种在指定业务机构库存数量不够出库!’,16,1)
return
End

If exists(select * from #pstout a left join u_pst_store b on a.busno = b.busno and a.pstid = b.pstid  where b.pstqty is null   and  b.busno is null and b.pstid is null and  a.outtype  like  '%盘点%')

Begin

    rollback

    raiserror('赠品盘点所示的品种在指定业务机构没有库存记录,生效失败！',16,1)

    return

End

If exists(select * from u_pst_store a,#pstout b where a.busno = b.busno and a.pstid = b.pstid  and b.outtype  not like  '%盘点%')

Begin

    update A

    set pstqty = a.pstqty - b.pstqty

    from u_pst_store a,#pstout_sum b

    where a.busno = b.busno and a.pstid = b.pstid  and  b.groupid='group2'

    if @@error <> 0

    begin

        rollback

        raiserror('增加赠品库存失败1!',16,1)

        return

    end

/*

    if exists(select * from u_pst_store a,#pstout b where a.busno = b.busno and a.pstid = b.pstid  and b.outtype  not like  '%盘点%')

   update A

    set awaitqty = isnull(a.awaitqty,0) - b.pstqty

    from u_pst_store a,#pstout_sum b

    where a.busno = b.busno and a.pstid = b.pstid  and  b.groupid='group2'

    if @@error <> 0

    begin

        rollback

        raiserror('冲减待出库数量出错1!',16,1)

        return

    end

    */

End

    else

    Begin

    update A

    set pstqty= c.pstqty-b.checkqty

    from u_pst_out a,#pstout b ,u_pst_store c

    where a.busno = b.busno and a.pstid = b.pstid  and a.pstoutno=b.pstoutno and a.pstid=c.pstid and a.busno=c.busno and  b.groupid ='group1'

    if @@error <> 0

    begin

        rollback

        raiserror('盘点增加赠品库存失败1!',16,1)

        return

    end

    update A

    set pstqty = b.checkqty

    from u_pst_store a,#pstout_sum b

    where a.busno = b.busno and a.pstid = b.pstid   and  b.groupid ='group1'

    if @@error <> 0

    begin

        rollback

        raiserror('盘点增加赠品库存失败1!',16,1)

        return

    end

    /*

    update A

    set awaitqty = isnull(a.awaitqty,0) - b.checkqty

    from u_pst_store a,#pstout_sum b

    where a.busno = b.busno and a.pstid = b.pstid   and  b.groupid ='group1'

    if @@error <> 0

    begin

        rollback

        raiserror('盘点增加赠品库存失败1!',16,1)

        return

    end   

    */  

    End

END

/*
if exists(select * from deleted a,inserted b where a.pstoutno = b.pstoutno and a.status = 0 and b.status = 2)
BEGIN
update A
set awaitqty = isnull(a.awaitqty,0) – b.pstqty
from u_pst_store a,inserted b
where a.busno = b.busno and a.pstid = b.pstid
if @@error <> 0
begin
rollback
raiserror(‘盘点增加赠品库存失败1!’,16,1)
return
end

END
*/
*/
declare @triggername varchar(100)
select @triggername = object_name(@@procid)
if exists(select 1 from c_trigger_ext(nolock) where triggername = @triggername and status_use = 1 and ltrim(rtrim(isnull(sql,”))) <> ”)
begin
select * into #inserted from inserted
select * into #deleted from deleted
exec p_trigger_ext @triggername
drop table #inserted,#deleted
end