----到sql查询分析器中选择赢通帐套数据库执行
if object_id('fn_getcustaccout','FN') is not null
drop function fn_getcustaccout
go
create function fn_getcustaccout
(@sheet_no varchar(100),@flag char(1))
returns NUMERIC(18,2)
as
begin
declare @accout NUMERIC(18,2)
if @flag='1'
begin
select @accout =sum(payment_amt) from view_pos_sale_payment where sheet_no=@sheet_no and payment_no='25'
end
else
begin
select @accout= sum( IsNull((a.sheet_amt - (isnull(a.paid_amt,0) + isnull(a.free_amt,0) + isnull(a.order_amt,0) ))* a.pay_type,0.00) )-(select IsNull(sum(total_amt - use_amt),0.00) from t_cust_prepay_flow WHERE cust_no =b.cust_no and sheet_no like 'CY%' and app_sign='1' ) from
view_pos_sale_master b join t_cust_accout_flow a on (a.cust_no = b.cust_no) where b.sheet_No=@sheet_no group by b.cust_no
if @flag='2'
begin
select @accout=@accout-sum(payment_amt) from view_pos_sale_payment where sheet_no=@sheet_no and payment_no='25'
end
end
return @accout
end

----到小票设计中添加此计算列
---取本单挂账金额
db_data3('select dbo.fn_getcustaccout(?,?)',max(sheet_no),'1')
---取除本次外的客户欠款金额
db_data3('select dbo.fn_getcustaccout(?,?)',max(sheet_no),'2')
---取客户合计欠款金额
db_data3('select dbo.fn_getcustaccout(?,?)',max(sheet_no),'3')