proc_srclist_upd.sql

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

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

create procedure Proc_SrcList_Upd
@action_cd              char(1) = null,
@item			int = null,
@vendor_id		varchar(10) = null,
@vendor_nm		varchar(250) = null,
@sug_vendor_id		varchar(10) = null,
@sug_vendor_nm		varchar(250) = null,
@HPN			varchar(18) = null,
@MTL_TYPE_CD		char(4) = null,
@Priority		char(1) = null,
@Description		varchar(250) = null,
@CPN			varchar(32) = null,
@MPN			varchar(32) = null,
@MODEL			varchar(30) = null,
@Lead_Time		int = null,
@MOQ			int = null,
@MPQ			int = null,
@Unit_Price		varchar(53) = null,
@Currency		char(3) = null,
@Item_Class_cd		char(2) = null,
@Basic_UoM		char(3) = null,
@Vendor_UoM		char(3) = null,
@Customer_ID		char(5) = null,
@Status 		CHAR(1) = NULL,
@effectived_dt		datetime = null,
@Expiration_dt		datetime = null,
@Created_by		varchar(15) = null,
@Updated_by		varchar(15) = null,
@Return_ID              int = null output

as
  declare 
	@maxitem int 
begin
	select @Return_ID = -1
	if(@action_cd ='N')
	begin
		select @maxitem =isnull(max(item),0)+1 from po_information_Record
		insert into PO_information_Record
		(               
              item     ,
              vendor_id     ,
              vendor_nm     ,
              sug_vendor_id     ,
              sug_vendor_nm     ,
              HPN     ,
              MTL_TYPE_CD     ,
              Priority     ,
              Description     ,
              CPN     ,
              MPN     ,
              MODEL     ,
              Lead_Time     ,
              MOQ     ,
              MPQ     ,
              unit_price,
              Currency     ,
              Item_Class_cd     ,
              Basic_UoM     ,
              Vendor_UoM     ,
              Customer_ID     ,
              Status     ,
              effectived_dt     ,
              Expiration_dt     ,
              Created_by     ,
              Creation_dt     ,
              Updated_by     ,
              Update_dt     
		)
		values
		(
	       @maxitem     ,
               @vendor_id     ,
               @vendor_nm     ,
               @sug_vendor_id     ,
               @sug_vendor_nm     ,
               @HPN     ,
               @MTL_TYPE_CD     ,
               @Priority     ,
               @Description     ,
               @CPN     ,
               @MPN     ,
               @MODEL     ,
               @Lead_Time     ,
               @MOQ     ,
               @MPQ     ,
               convert(float,@Unit_Price) ,
               @Currency     ,
               @Item_Class_cd     ,
               @Basic_UoM     ,
               @Vendor_UoM     ,
               @Customer_ID     ,
               'A'     ,
               @effectived_dt     ,
               @Expiration_dt     ,
               @Created_by     ,
               getdate()     ,
               @Updated_by     ,
               getdate()     
		)
		
		select @Return_ID = @@Error
	 end
		else if(@action_cd ='M')
		begin
			update PO_Information_Record
				
			set 
            			  vendor_id       =         @vendor_id,
             			  vendor_nm       =         @vendor_nm,
              			  sug_vendor_id       =         @sug_vendor_id,
            			  sug_vendor_nm       =         @sug_vendor_nm,
             			  HPN       =         @HPN,
              MTL_TYPE_CD       =         @MTL_TYPE_CD,
              Priority       =         @Priority,
              Description       =         @Description,
              CPN       =         @CPN,
              MPN       =         @MPN,
              MODEL       =         @MODEL,
              Lead_Time       =         @Lead_Time,
              MOQ       =         @MOQ,
              MPQ       =         @MPQ,
              --Unit_Price       =         @Unit_Price,
              --Currency       =         @Currency,
              Item_Class_cd       =         @Item_Class_cd,
              Basic_UoM       =         @Basic_UoM,
              Vendor_UoM       =         @Vendor_UoM,
              Customer_ID       =         @Customer_ID,
              Status       =         @Status,
              effectived_dt       =         @effectived_dt,
              Expiration_dt       =         @Expiration_dt,
              Updated_by       =         @Updated_by,
              Update_dt       =         getdate()
			where item = @item

			  update po_price_mstr
					set vendor_id = @vendor_id,
					hpn = @hpn,
					description = @description,
					cpn = @cpn,
					mpn = @mpn,
					model = @model,
					currency = @currency,
					basic_uom = @basic_uom,
					vendor_uom = @vendor_uom,
					status = @status
					where item  = @item
			 
						

		select @Return_ID = @@error
	end

	select @Return_ID 
end

go