proc_price_upd.sql

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

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

create Procedure Proc_Price_upd
@action_cd char(1) = null,
@item int = null,
@line int = null,
@vendor_id char(5) = null,
@Unit_Price  varchar(53) = null,
@Effective_dt datetime = null,
@Expire_dt    datetime = null,
@created_by   varchar(15) = null,
@updated_by   varchar(175) = null,
@status      CHAR(1) = NULL,
@Return_ID   Int  = null output

as
  declare
	@maxitem int
begin
	select @Return_ID = -1  --unknown error
	if (@action_cd ='N') 
	begin 
		/*if exists(select item from po_price_mstr
			where item = @item and  moq = @moq and mpq = @mpq and
			(effective_dt in(@effective_dt) or expire_dt in(@expire_dt))
			)
			select @Return_ID = -2  --Date already exists in database
		 else*/

		begin
			select @maxitem = isnull(max(Line),0)+1 from po_price_mstr where  item = @item
			insert into PO_Price_Mstr
			(
			item,
			line,
			Vendor_id,
			HPN,
			CPN,
			MPN,
			Description,
			Model,
			Basic_uom,
			Vendor_uom,
			MOQ,	
			MPQ,
			Currency,
			Unit_Price,
			Effective_dt,
			Expire_dt,
			Creation_dt,
			Update_dt,
			Created_by,
			Updated_by,
			Status
			)
			select 
			Item,
			@maxitem,
			Vendor_ID,
			HPN,
			CPN,
			MPN,
			Description,
			Model,	
			Basic_UoM,
			Vendor_UoM,
			MOQ,
			MPQ,
			Currency,
			convert(float,@Unit_Price),
			@Effective_dt,
			@Expire_dt,
			getdate(),
			getdate(),
			@Created_by,
			@updated_by,
			'A'
		from po_information_Record where item = @item 
		end
		select @Return_ID = @@error
	end

else if(@action_cd ='M')
begin
	
	  begin
		insert into PO_Price_Log
		(item,line,hpn,status,OLD_Price,New_Price,Modify_dt,Modified_by)
		select item,line,hpn,status,unit_price,@unit_Price,getdate(),updated_by 
		from po_price_mstr where item = @item and line = @line
		
    		update po_price_mstr
	    set unit_Price = @unit_Price,
		effective_dt = @effective_dt,
		expire_dt    = @expire_dt,
		status = @status,
		update_dt = getdate(),
		updated_by = @updated_by
		where item = @item  and line = @line
	   select @Return_id = @@error
	 end
	end
	select @Return_id
	
end
go
   
--sp_help po_price_log
/*
alter table po_price_Mstr 
add Line int not null default 1
*/