if exists(select 1 from sysobjects where name='pr_report_item_sum_by_item_no')
  drop procedure pr_report_item_sum_by_item_no
go

 create proc pr_report_item_sum_by_item_no
 (
	@as_from char(10),
	@as_to char(10),
	@as_branch_no nvarchar(6),
	@as_item_no varchar(20),
	@as_item_subno varchar(20),
	@as_item_clsno varchar(20),
	@as_suppid varchar(20),
	@as_sup_flag char(1),
	@as_item_pp varchar(20)

 )
 as
 begin 
 
	declare @as_amt_total numeric(17,4)
 
	create table #report_item_sum_by_item_no
	(
		item_no char(20), item_name varchar(40), sale_qty numeric(17,4), sale_amt numeric(17,4), ret_qty numeric(17,4), 
		ret_amt numeric(17,4), giv_qty numeric(17,4), giv_amt numeric(17,4), item_subno varchar(15), item_subname varchar(20) null,
		unit_no char(4) null, item_size varchar(20) null, item_clsno char(12), item_clsname varchar(20), place_no char(10) null,
		item_sup_flag char(1), pp_name varchar(30), stock_qty numeric(17,4), as_amt_total numeric(17,4), sale_price numeric(17,4),
		branch_no char(6)
	)

	insert into #report_item_sum_by_item_no
	select a.item_no, b.item_name, sum(a.sale_qty) as sale_qty, sum(a.sale_amt) as sale_amt, sum(a.ret_qty) as ret_qty,   
		sum(a.ret_amt) as ret_amt, sum(a.giv_qty) as giv_qty,  sum(a.giv_amt) as giv_amt, b.item_subno, b.item_subname,
		b.unit_no, b.item_size, b.item_clsno, '', '',				
		b.item_sup_flag, b.item_brand, 0, sum(a.sale_amt - a.ret_amt), b.sale_price,		
		a.branch_no
	from t_rm_daysum a, t_bd_item_info b 
	where a.oper_date between @as_from and @as_to
	and a.item_no = b.item_no
	and a.branch_no like @as_branch_no	
	and b.item_subno like @as_item_subno
	and b.item_clsno like @as_item_clsno
	and a.item_no like @as_item_no   
	and a.supcust_no like @as_suppid   
	and b.item_sup_flag like @as_sup_flag  
	and b.item_brand like @as_item_pp
	and (dbo.ISGrantItem(a.item_no)=1) 
	and (dbo.ISGrantBranch(a.branch_no)=1)
	group by a.item_no, b.item_name, a.branch_no, b.item_subno, b.item_subname, b.unit_no, b.item_size, b.item_clsno, b.item_sup_flag, b.item_brand, b.sale_price, b.branch_no
		
	update #report_item_sum_by_item_no
	set item_clsname = a.item_clsname
	from t_bd_item_cls a
	where #report_item_sum_by_item_no.item_clsno = a.item_clsno
	
	update #report_item_sum_by_item_no
	set pp_name = a.code_name
	from t_bd_base_code a
	where #report_item_sum_by_item_no.pp_name = a.code_id and a.type_no='PP'

	update #report_item_sum_by_item_no
	set place_no = a.place_no
	from t_bd_place_item_info a
	where #report_item_sum_by_item_no.item_no = a.item_no and  #report_item_sum_by_item_no.branch_no = a.branch_no  
	
	--¸üÐÂ¿â´æÊý¾Ý
	select sum(a.stock_qty) stock_qty, a.item_no 
	into #temp_stock 
	from t_im_branch_stock a, (select distinct item_no from #report_item_sum_by_item_no where item_no like @as_item_no) b 
	where a.item_no = b.item_no and a.branch_no like @as_branch_no
	group by a.item_no 
	
	update #report_item_sum_by_item_no
	set stock_qty = a.stock_qty
	from #temp_stock a
	where #report_item_sum_by_item_no.item_no = a.item_no 	

	select @as_amt_total = sum(as_amt_total) 
	from #report_item_sum_by_item_no 
		
	select item_no, item_name, sum(sale_qty) as sale_qty, sum(sale_amt) as sale_amt, sum(ret_qty) as ret_qty, 
		sum(ret_amt) as ret_amt, sum(giv_qty) as giv_qty, sum(giv_amt) as giv_amt, item_subno, item_subname,
		unit_no, item_size, item_clsno, item_clsname, place_no,
		item_sup_flag, pp_name, stock_qty , @as_amt_total as as_amt_total, sale_price	
	from #report_item_sum_by_item_no 
	group by item_no, item_name, item_subno, item_subname,unit_no, item_size, item_clsno, item_clsname, place_no,item_sup_flag, pp_name, stock_qty, sale_price


end
go