proc_gr_history_list.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

/*
   Exec  Proc_Get_GR_History 'A','POL05-00156','',''
  Exec  Proc_Get_GR_History 'P','PO05-01537','447100C47603-CS007',null
  Exec  Proc_Get_GR_History 'Z','POM04-01200','',null



*/
if object_id('dbo.Proc_Get_GR_History') is not null
drop procedure dbo.Proc_Get_GR_History
go

Create procedure Proc_Get_GR_History
@action_cd  char(1) =null,
@order_number varchar(30),
@hpn	      varchar(18) = null,
@cpn	      varchar(30) = null
as
begin
    if exists (select pono from gtr_item where pono =@order_number) 
    begin
	 if(@action_cd ='P')  --Hip Fung Partnumber
	begin		
	select
 	distinct  
	a.Dnno as [DN No.],
        a.HPN,
	a.CPN,
	a.MPN,
	a.Item_nm as [Description],	
	convert(varchar(50),a.in_quantity) as [GR Qty],
	b.uom,
	--a.in_date as [GR Date],
	a.Created_by as [Created By],
	convert(varchar(50),a.created_dt) as [Creation Date],	
	last_upd_by as  [Checked By],
	convert(varchar(50),last_upd_dt) as  [Checked Date]
	
	from gtr_item  a,viw_PO_Item_List b 
	where  a.pono = b.pono and  a.pono =@order_number
	and a.hpn = @hpn		
end
	else if (@action_cd='C') --CPN
	begin
		select
 		distinct  
		a.Dnno as [DN No.],
        	a.HPN,
		a.CPN,
		a.MPN,
		a.Item_nm as [Description],	
		convert(varchar(50),a.in_quantity) as [GR Qty],
		b.uom,
		--a.in_date as [GR Date],
		a.Created_by as [Created By],
		convert(varchar(50),a.created_dt) as [Creation Date],	
		last_upd_by as  [Checked By],
		convert(varchar(50),last_upd_dt) as  [Checked Date]
	
		from gtr_item  a,viw_PO_Item_List b 
		where  a.pono = b.pono and  a.pono =@order_number
		and a.cpn = @cpn
	
	end
	else
	begin
		
	select
 	distinct  
	a.Dnno as [DN No.],
        a.HPN,
	a.CPN,
	a.MPN,
	a.Item_nm as [Description],	
	convert(varchar(50),a.in_quantity) as [GR Qty],
	b.uom,
	--a.in_date as [GR Date],
	a.Created_by as [Created By],
	convert(varchar(50),a.created_dt) as [Creation Date],	
	last_upd_by as  [Checked By],
	convert(varchar(50),last_upd_dt) as  [Checked Date]
	
	from gtr_item  a,viw_PO_Item_List b 
	where  a.pono = b.pono and  a.pono =@order_number
	
	end
   end	
	else
	begin
		if(@action_cd ='P') -- Hip fung partnumber
		begin
			select 
			distinct 
			--a.order_number as [Order Number],
			a.Dn_Number as [DN No.],
			a.HPN,
			b.CPN,
			b.MPN,
			b.description as [Description],
			a.Warehouse,
			a.UoM,
			a.Rcv_Qty as [GR Qty],
			a.Actual_Qty as [Actual Qty],
			isnull(a.Return_Qty,0) as [Return Qty],
			a.Created_by as [Created By],
			a.Creation_dt as [Creation Date]
			from PO_MIS_Rcv_List a, bas_item b 
			where a.hpn = b.itemno and a.order_number = @order_number
			and a.hpn = @hpn

		end
		else if(@action_cd ='C') 
		begin
			select 
			distinct 
			--a.order_number as [Order Number],
			a.Dn_Number as [DN No.],
			a.HPN,
			b.CPN,
			b.MPN,
			b.description as [Description],
			a.Warehouse,
			a.UoM,
			a.Rcv_Qty as [GR Qty],
			a.Actual_Qty as [Actual Qty],
			isnull(a.Return_Qty,0) as [Return Qty],
			a.Created_by as [Created By],
			a.Creation_dt as [Creation Date]
			from PO_MIS_Rcv_List a, bas_item b 
			where a.hpn = b.itemno and a.order_number = @order_number
			and b.cpn = @cpn

	    end
		else if(@action_cd ='A')
	begin
	    		select 
			distinct 
			--a.order_number as [Order Number],
			a.Dn_Number as [DN No.],
			a.HPN,
			b.CPN,
			b.MPN,
			b.description as [Description],
			a.Warehouse,
			a.UoM,
			a.Rcv_Qty as [GR Qty],
			a.Actual_Qty as [Actual Qty],
			isnull(a.Return_Qty,0) as [Return Qty],
			a.Created_by as [Created By],
			a.Creation_dt as [Creation Date]
			from PO_MIS_Rcv_List a, bas_item b 
			where a.hpn = b.itemno and a.order_number = @order_number
		
		
		end
	end
	
end
		
/*
select top 10 * from gtr_item
select top 10 * from base_recdepot
select top 10 * from base_recdepotc
*/