finally_install_bk.sql

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

			 getdate(),
			@completed_reason
			)
		end
		else 
		begin
			update po_adjust_item
				set force_completed = 'Y',
				    completed_by = @completed_by,
				    completed_reason = @completed_reason
			where order_number = @order_number
		end	
end
end

--select * from po_adjust_line
go
---------------------------------------------------------------------------------------------------------------------------
if object_id('dbo.Proc_Cancel_PO') is not null
drop procedure dbo.Proc_Cancel_PO
go

Create procedure Proc_Cancel_PO
  @Order_Number varchar(30) = NULL,
  @Action_type  VARCHAR(30) = NULL,
  @Action_by    varchar(15) = null,
  @Reason       varchar(250) = null,
  @Remark       varchar(250) =null
as

begin
      
	if not exists(select order_number from  Cancel_po_log where order_number =@order_number)
      begin
	insert into Cancel_po_log
	(
	 Order_Number,
	 Action_type,
	 Action_dt,
	 Action_by,
	 Reason,
	 Remark
	)
	values
	(
	 @Order_Number,
	 @Action_type,
	 getdate(),
	 @Action_by,
	 @Reason,
	 @Remark
	)
	if(@action_type ='C')
	begin
		update ord_mst 
			set status ='C'
		where pono = @order_number
	end
	else if(@action_type ='D')
	begin
		update ord_mst 
			set status ='D'
		where pono = @order_number	
	end
	else if(@action_type ='A')
	begin
		update ord_mst 
			set status ='O'
		where pono = @order_number	
	end
   end
end
-----------------------------------------------------------------------------------------------
go

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

create   procedure Proc_Get_Order_Header_info
@pono varchar(30)=null
as

if (@pono is not null) 
begin
select a.discount,
a.prno,a.cust_nm,
a.deli_loc,a.post_date,a.remark,b.vendor_nm as description,
b.address,b.cont1,b.tel,b.fax,b.payterms from ord_mst a,
 po_vendor_mstr b  where a.id=b.vendor_id and a.pono=@Pono 
return
end
------------------------------------------------------------------------------------------------
go

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


create     procedure Proc_Get_PO_Number
@flag        char(1) = null,    --------temporary or post to table Y/N
@identify    varchar(4)  = null, 
@who_used    varchar(15) = null,
@Ord_num_out varchar(12) = null output
as
declare 
@Order_num varchar(12),
@ID  char(4),
@Year char(2),
@maxitem int,
@temp_num varchar(4)
begin

	
	if(upper(@identify)='POM') 
	begin
	select @id = 'POM'
	select @maxitem =  isnull(Number,1) from PO_Order_Number where IDEN='POM' 
	end
	else if (@identify ='POL')
	begin
		select @id = 'POL'
		select @maxitem =  isnull(Number,1) from   PO_Order_Number where IDEN='POL' 
	end
	else if(@identify='POML')
        begin
		select @id = 'POML'
		select @maxitem =  isnull(Number,1) from   PO_Order_Number where IDEN='POML' 
	end
	else
	begin
		select @id = 'PO'
		select @maxitem =  isnull(Number,1) from   PO_Order_Number where IDEN='PO' 
	end
 	select @Year = isnull(right(convert(char(4),datepart(YY,getdate())),2),'04')
	--select @id = isnull(IDEN,'PO') from order_number
	select @temp_num = convert(char(4),@maxitem)
 	
	if(@identify = 'POML') 
 	select @Order_num =rtrim(ltrim(@ID))+@year+'-'+ REPLICATE('0',4-len(@temp_num))+@temp_num
	else
	 	select @Order_num =rtrim(ltrim(@ID))+@year+'-'+ REPLICATE('0',5-len(@temp_num))+@temp_num
	select @Ord_num_out = @Order_num

    	if(upper(@flag) = 'Y') 
	begin
		begin 	transaction
	
			if(upper(@identify) ='POM')
			begin	
				update po_order_number
				set Number = Number + 1,
		 		  last_number = @ord_num_out 
				 where IDEN='POM' 
			end
			else if(@identify = 'POL')
			begin
				update po_order_number
				set Number = Number + 1,
				last_number = @ord_num_out
				where IDEN='POL'
			end
			else if(@identify = 'POML')
			begin
				update po_order_number
					set Number = Number + 1,
					last_number = @ord_num_out
					where IDEN='POML'
			end
			else
			begin
				update po_order_number
					set Number = Number + 1,
					last_number = @ord_num_out
					where IDEN='PO'
			end
		 	
			insert into po_number_list
			(order_number,who_used,used_dt)
			values(@ord_num_out,@who_used,getdate())
	
	if(@@error  =0) 
	commit
	else 
	rollback
	
	end
	select @Ord_num_out
end

go
------------------------------------------------------------------------------------------





IF object_id('dbo.proc_Get_vendor_id') is not null
drop procedure dbo.proc_Get_vendor_id
go

create procedure Proc_get_vendor_id
@flag   char(1)  = null,
@vendor_id_out char(8) = null output
as
declare 
@maxitem int ,
@temp_number char(4),
@vendor_id char(8)
begin
	
	select @maxitem =  isnull(Number,1) from PO_VND_ID_Number
	select @temp_number = convert(int,@maxitem)

 	select @vendor_id  ='V'+ REPLICATE('00',4-len(@temp_number))+@temp_number
	select @vendor_id_out = @vendor_id
	

	 if(@flag = 'T')
	begin
		update PO_VND_ID_Number
			set Number = Number + 1,
			Vendor_Id = @vendor_id_out
	end	


select @vendor_id_out
end

/*


declare
	@vendor_id char(8) 
	exec  proc_Get_vendor_id 'F',@vendor_id out

*/

/*
select * from po_vnd_id_number
update po_vnd_id_number
	set number = number -1
*/
----------------------------------------------------------------------------------------

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

Create Procedure Proc_Ord_Qty_Add
@pono varchar(30) = null,
@Qty  numeric(18,2) = 0.0,
@itemseq int	= null
as
declare
  @old_qty numeric(18,2) 

  select @old_qty = isnull(quantity,0) from ord_item
	where pono =@pono and itemseq =@itemseq and iscom ='Y'

  if(@Qty >@old_qty)
 begin
	update ord_item
		set iscom ='N'
	where pono =@pono and itemseq =@itemseq
 end

 if exists(select  pono from ord_mst where pono =@pono
 and completed ='YES' or status = 'F')
 begin
	update ord_mst 
		set completed ='NO',
		    status = 'O'
	where pono =@pono
 end

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

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

Create procedure Proc_Order_Header_Upd
@Order_number_out varchar(30)=null,
@Order_type CHAR(4) = NULL,
@id	char(8)=null,
@vendor_nm varchar(255) = null,
@customer_id CHAR(5) = NULL,
@cust_nm   varchar(80) = null,
@prno	varchar(30)=null,
@discount numeric(4,2)=null,
@currency  CHAR(3) = NULL,
@payterms  varchar(120) = null,
@deli_loc varchar(80)=null,
@remark	  varchar(255)=null,
@post_date datetime=null,
@Creator   char(10)=null,
@last_modify char(10)= null
as

begin

if  not exists(select  pono from ord_mst where pono=@Order_number_out) 
  begin	
	insert into ord_mst(
			pono,
			Order_type,
			id,
	vendor_nm,
	customer_id,
	cust_nm,
	prno,
	discount,
	currency,
	payterms,
	deli_loc,
	post_date,
	last_up,
	remark,
	Creator,
	Last_modify,
	version,
	rev_date,
	status,
	revsion_by)
values(
	upper(@Order_number_out),
	@order_type,
	@id,
	@vendor_nm,
	@customer_id,
	@cust_nm,
	upper(@prno),
	@discount,
	@currency,
	@payterms,
	@deli_loc,
	getdate(),
	getdate(),
	@remark,
	@Creator,
	@Last_modify,
	1.0,
	getdate(),
	'O',
	@creator)
     end
   
return 
end

GO
-------------------------------------------------------------------------------------------------------
if object_id('dbo.Proc_Order_Item_Upd') is not null
drop procedure dbo.Proc_Order_Item_Upd
go

Create Procedure Proc_Order_Item_Upd
@pono		varchar(30)=null,
@itemseq	int=1,
@item		int=null,
@hpn		varchar(18) =null,
@mtl_type_cd    char(4) = null,
@mpn		varchar(30) =null,
@cpn		varchar(30) = null,
@material_nm	varchar(255)=null,
@unitprice	VARCHAR(53)=null,
@curunit	char(3) =null,
@model		varchar(30)=null,
@uom		char(8)=null,
@quantity	numeric(12,2)=0.00,
@amount		numeric(15,3)=0.00,
@moq		int = 0,
@mpq		int = 0,
@deli_day	datetime,
@confirmed_dt	varchar(10) = null,
@created_by	char(10) = null,
@action_cd	char(1)=null,
@Last_Modify	char(10) = Null,
@price_effective_dt datetime = null,
@price_expiration_dt datetime = null,
@Error_Code 	int = null output

as
declare 
@maxitem int, 
@Recount int, 
@newitemseq int 
begin
	select @Error_Code = -1  					---Couldn't Execute it
 	if  not  exists(select pono from ord_mst where pono = @pono)
	select @Error_Code = -2 					--Not Data Found int PO Header
	else
	begin								--Start Execute Procedure--------------
		if(upper(@action_cd)='N') 
		begin
	 	
			if exists(select * 
			from ord_item where pono =@pono and item =@item and
	    		(convert(char(4),datepart(yy, deli_day))
           		  +'/'+convert(char(2),datepart(mm, deli_day))
           		 +'/'+convert(char(2),datepart(dd, deli_day))) 
	
            		  =(convert(char(4),datepart(yy,@deli_day))
             		   +'/'+convert(char(2),datepart(mm,@deli_day))
               			 +'/'+convert(char(2),datepart(dd,@deli_day)))
	  		  )
			 select @Error_Code = - 3				---Duplicate The Delivery Date same HPN/Item------
			else
			
			 begin 
	
				select @newitemseq= isnull(max(itemseq)+1,1) from ord_item where pono = @pono 

      				insert into ord_item(	                            
                            pono,
                            itemseq,
                            item,
                            hpn,
                            mtl_type_cd,
                            mpn,
                            cpn,
                            material_nm,
                            unitprice,
                            curunit,
                            model,
                            uom,
                            quantity,
                            received_qty,
                            balance_qty,
                            amount,
                            moq,
                            mpq,
                            deli_day,
                            confirmed_dt,
                            created_by,
                            last_modify,
                            price_effective_dt,
                            price_expiration_dt)
                            values(
                            upper(@pono),
                            @newitemseq,
                            @item,
                            @hpn,
                            @mtl_type_cd,
                            @mpn,
                            @cpn,
                            @material_nm,
                            convert(float,@unitprice),
                            @curunit,
                            @model,
                            @uom,
                            @quantity,
                            0,
                            @quantity,
                            @amount,
                            @moq,
                            @mpq,
                            @deli_day,
                            @confirmed_dt,
                            @created_by,
                            @last_modify,
                            @price_effective_dt,
                            @price_expiration_dt
                            )

			    update ord_mst 
				    set status = 'O',
					completed = 'NO'

				select @Error_Code = @@Error	    
			end
	end
	
--------------------------------------Modify Item---------------------------------------------------------------
	else if (upper(@action_cd) = 'M')	
	begin
		if not exists(select * from ord_item 
		where pono=@pono and itemseq=@itemseq) 
		
		select @Error_Code = -4 						-------- Target Record Not Found----------------		

		--------if add  Qty process it--------------------------
		else
		begin
			exec Proc_Ord_Qty_Add  @pono,@Quantity,@itemseq

			update ord_item 
  				set 
				item=@item,
				hpn=@hpn,	
				mtl_type_cd = @mtl_type_cd,	
				mpn=@mpn,	
				cpn =@cpn,
				material_nm=@material_nm,	
				unitprice= convert(float,@unitprice),	
				curunit=@curunit,	
				model=@model,
				uom=@uom,
				quantity=@quantity,
				balance_qty =@quantity - Received_qty,
				amount=@amount,
				moq = @moq,
				mpq = @mpq,
				last_upd_dt = getdate(),
				deli_day=@deli_day,
				confirmed_dt = @confirmed_dt,
				last_modify = @last_modify,
				price_effective_dt = @price_effective_dt,
				price_expiration_dt = @price_expiration_dt
				where pono=@pono and itemseq=@itemseq
				
				 update ord_item 
					set iscom =  case when ((Received_qty - Quantity)=0) then 'Y' else 'N' end
					where pono = @Pono 
				        and isnull((Received_qty - Quantity),0) = 0

				
				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
			
			
		end
	end
		else if(upper(@action_cd) ='D')
		begin
			
			 if  exists (select pono from ord_mst where pono = @Pono and status = 'F') 
			 select @Error_Code = -5						-------Order Already Completed		
			 else
				if  exists(select itemseq from ord_item where pono = @pono
		    	        and itemseq = @itemseq and received_qty>0)  
				select @Error_code = -6  					-------Order Line already started good received----------
			  else
		
		     begin