proc_price_upd_rev1.0.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,
@MOQ	    int = null,
@Unit_Price  varchar(53) = null,
@effective_dt datetime = null,
@expiration_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,
	@last_item 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 status ='A' and
			(datepart(yy,effective_dt)>=datepart(yy,@effective_dt) 
			and datepart(mm, effective_dt)>=datepart(mm,@effective_dt)
   			and datepart(dd, effective_dt)>=datepart(dd,@effective_dt)
			))
			select @Return_ID = -2 --duplicate key unit Price
			else if
			 exists(select item from po_price_mstr
			where item = @item   and status ='A'and 
			--	unit_Price = @unit_Price and
			 @effective_dt <=(select max(effective_dt) from po_price_mstr where item =@item and status ='A'))
			
			select @Return_ID = -3   --effective must be larger than exists last effective date
			
			else if
			 exists(select item from po_price_mstr
			where item = @item   and status ='A'and 
			unit_Price = @unit_Price and
			 line =(select max(line) from po_price_mstr where item =@item and status ='A'))
			
			select @Return_ID = -4  --Need't to modify unit Price
			
		 else

		begin
			select @last_item = isnull(max(line),1) from po_price_mstr where item = @item
			and  status ='A'
			
			update po_price_mstr
				set expire_dt = @effective_dt-1
			where item = @item and line = @last_item 
		
			select @maxitem = isnull(max(Line),0)+1 from po_price_mstr where  item = @item
			--and status ='A'
			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,
			@expiration_dt,  --default ten years 
			getdate(),
			getdate(),
			@Created_by,
			@updated_by,
			'A'
		from po_information_Record where item = @item 
		select @Return_ID = @@error
		end
		select @Return_ID
		
	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,
		moq = @moq,
		--effective_dt = @effective_dt,
		expire_dt    = @expiration_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
*/