finally_install_bk.sql

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

        			delete from ord_item 
					where pono=@pono and itemseq=@itemseq

          				select @maxitem=(select isnull(max(itemseq),0)  from ord_item  where pono=@pono)

         		 		select @Recount=(select isnull(count(*),0) from ord_item  where pono=@pono)

     					if(@itemseq<=@maxitem) 
         				begin
            				 	if(@itemseq<>@maxitem) and (@Recount>=1)
	        		 		begin
	          					 update ord_item
								set itemseq=@itemseq
	          							where pono=@pono and itemseq=@maxitem
             					end
           					else
     			   			begin
          		 				update ord_item
							 set itemseq=1
	          					where pono=@pono and itemseq=@itemseq
         					end
      	      				end

				
				update ord_mst
					set completed = case when((select isnull(count(*),0) pono from ord_item where pono =@Pono and  iscom ='N')>0) then 'NO' ELSE 'YES' End,
					status = case when((select isnull(count(*),0) pono from ord_item where pono =@Pono and  iscom ='N')>0) then 'O' ELSE 'F' End
					where pono =@pono
					
					select @Error_Code = @@Error
		end
       end
	  else 
		select @Error_Code = -7		----Invalid Operation
	end
		select @Error_Code
end
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


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

CREATE       procedure Proc_Order_Revision
@pono		varchar(30)=null,
@prno		varchar(80)=null,
@discount 	numeric(4,2)=null,
@payterms	varchar(120) = null,
@deli_loc	varchar(80)=null,
@customer_ID    CHAR(5) = NULL,
@cust_nm        varchar(15)= null,
@remark		varchar(255)=null,
@rev_date	datetime=null,
@revsion_by	varchar(12) = null,
@Error_Code 	int = null output 
as
begin
	select @Error_Code = -1
	
	if  not exists(select pono  from ord_mst where pono=@pono)
	 select @Error_Code = -2  --------not record found
	else
	begin
		update ord_mst
		set
		prno=@prno,
		discount=@discount,
		payterms = @payterms,
		deli_loc=@deli_loc,
		customer_id = @customer_ID,
        	cust_nm = @cust_nm,
		remark=@remark,
		rev_date=getdate(),
		revsion_by = @revsion_by,
		version = version + 1.0
	where pono=@pono
	
	select @Error_Code = @@Error
	end
	
	select @Error_Code
end
go

-------------------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


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

CREATE       procedure Proc_Order_Revision
@pono		varchar(30)=null,
@prno		varchar(80)=null,
@discount 	numeric(4,2)=null,
@payterms	varchar(120) = null,
@deli_loc	varchar(80)=null,
@customer_ID    CHAR(5) = NULL,
@cust_nm        varchar(15)= null,
@remark		varchar(255)=null,
@rev_date	datetime=null,
@revsion_by	varchar(12) = null,
@Error_Code 	int = null output 
as
begin
	select @Error_Code = -1
	
	if  not exists(select pono  from ord_mst where pono=@pono)
	 select @Error_Code = -2  --------not record found
	else
	begin
		update ord_mst
		set
		prno=@prno,
		discount=@discount,
		payterms = @payterms,
		deli_loc=@deli_loc,
		customer_id = @customer_ID,
        	cust_nm = @cust_nm,
		remark=@remark,
		rev_date=getdate(),
		revsion_by = @revsion_by,
		version = version + 1.0
	where pono=@pono
	
	select @Error_Code = @@Error
	end
	
	select @Error_Code
end
go

------------------------------------------------------------------------------------------------------------------------

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       numeric(18,2) = null,
@MPQ       numeric(18,3) = 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 moq = @moq,
		mpq = @mpq,
	        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
*/
---------------------------------------------------------------------------------------------
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

-----------------------------------------------------------------------------------------------------------------------------------

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

create procedure Proc_Vendor_Mstr_Upd
@Action_cd 		CHAR(1) = NULL,
@Vendor_ID		CHAR(8) = NULL,
@Vendor_class		char(1) = null,
@Vendor_nm		VARCHAR(250) = NULL,
@TEL			VARCHAR(80) = NULL,
@FAX			VARCHAR(80)= NULL,
@cont1			VARCHAR(80) = NULL,
@cont2			VARCHAR(80) = NULL,
@Address		VARCHAR(250) = NULL,
@payterms		VARCHAR(120) = NULL,
@Currency		CHAR(3) = NULL,
@Pay_Day		int = null,
@website		varchar(120) = null,
@email			varchar(120) = null,
@Remark			varchar(120) = null,
@created_by		varchar(15) = null,
@updated_by		varchar(15) = null
as
if(@action_cd='N') 
begin
declare 
@newitem int ,
@new_vendor_id char(8) 
select @newitem= isnull(max(item)+1,1) from PO_vendor_mstr
exec Proc_Get_Vendor_ID 'F',@new_vendor_id  output
begin
insert into PO_Vendor_Mstr(
	item,
	Vendor_id,
	Vendor_Class,
	vendor_nm,
	tel,
	fax,
	cont1,
	cont2,
	address,
	payterms,
	deft_currency,
	pay_day,
	website,
	email,
	Remark,
	creation_dt,
	created_by,
	update_dt,
	updated_by
	)
values(
	@newitem,
	@new_vendor_id,
	@vendor_class,
	@vendor_nm,
	@tel,
	@fax,
	@cont1,
	@cont2,
	@address,
	@payterms,
	@currency,
	0,
	@website,
	@email,
	@Remark,
	getdate(),
	@created_by,
	getdate(),
	@updated_by
)

return 
end
end

if(@action_cd='M') 
begin
  update po_vendor_mstr 
	set
vendor_nm=@vendor_nm ,
vendor_class = @vendor_class,
tel=@tel,
fax=@fax,
cont1=@cont1,
cont2=@cont2,
address=@address,
payterms=@payterms,
deft_currency = @currency,
website=@website,
email=@email,
update_dt=getdate(),
updated_by = @updated_by,
remark = @remark
where  vendor_id=@vendor_id
return 
end

if (@action_cd='D') 
begin
if exists(select * from po_vendor_mstr where  vendor_id = @vendor_id)
begin
if not exists(select ID from mat_mst where id = @vendor_id)
delete from po_vendor_mstr where  vendor_id=@vendor_id
else
print('cannot delete current vendor')
end
return
end
go
--------------------------------------------------------------------------------------------------------------