proc_epcs_gr_overview.sql

来自「工厂采购系统,DELPHI+SQL SERVER,三层技术」· SQL 代码 · 共 49 行

if object_id('dbo.Proc_EPCS_GR_Overview') is not null
drop procedure dbo.Proc_EPCS_GR_Overview
go

create procedure Proc_EPCS_GR_Overview
@order_num varchar(30) = null,
@hpn varchar(18) = null
as
begin
if(isnull(@hpn,'')='') 
begin
   
select  
convert(varchar(50),itemseq) as line,
DNNO as [DN Number],
HPN,
CPN,
MPN,
convert(varchar(50),Created_dt) as [Creation Date],
created_by as [Created By],
convert(varchar(50),in_quantity) as [GR Quantity] 
From gtr_item 
where pono =@order_num 
union
all
select '','','','','','','GR Total:',convert(varchar(50),sum(in_quantity))  from gtr_item
where pono =@order_num  group by  hpn

end
else
begin
select  
convert(varchar(50),itemseq) as line,
DNNO as [DN Number],
HPN,
CPN,
MPN,
convert(varchar(50),Created_dt) as [Creation Date],
created_by as [Created By],
convert(varchar(50),in_quantity) as [GR Quantity] 
From gtr_item 
where pono =@order_num and hpn =@Hpn
union
all
select '','','','','','','GR Total:',convert(varchar(50),sum(in_quantity))  from gtr_item
where pono =@order_num and hpn =@hpn  group by  hpn
end
end