finally_install_bk.sql

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

b.Depot as Location,
b.Unit as UoM,
b.Rec_Qty
 from  base_recdepot a, base_recdepotc b
where a.add_id = b.single_id

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

if object_id('dbo.Viw_Ord_For_GR') is not null
 drop view Viw_Ord_For_GR
go
create view Viw_Ord_For_GR
as
select a.vendor_nm,a.pono,a.prno,b.hpn,b.cpn,b.mpn,sum(quantity) as ord_qty
from ord_mst a,ord_item b where a.pono =b.pono
group by a.vendor_nm,a.pono,a.prno,b.hpn,b.cpn,b.mpn

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_GR_Amount') is not null
drop procedure dbo.Proc_Get_GR_Amount
go

Create procedure Proc_Get_GR_Amount
@action_cd CHAR(1) = NULL,
@order_number varchar(30) = null,
@Item          int = null
as
begin
	if(@action_cd ='A')
	begin
		select   isnull(Sum(quantity),0) as Order_Total,
		 isnull(sum(Received_qty),0) as GR_Total,
		 isnull(sum(Balance_qty),0) as Bal_Total
		from ord_item     where pono =@order_number  and  iscom='N'
	end
	  else
	begin
		select   isnull(Sum(quantity),0) as Order_Total,
		 isnull(sum(Received_qty),0) as GR_Total,
		 isnull(sum(Balance_qty),0) as Bal_Total
		from ord_item     where pono =@order_number
		and item =@item and iscom ='N'
	end


end
go

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

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

Create procedure Proc_Get_PO_Amount
@order_number varchar(30) = null
as
declare
	@discount numeric(18,3),
	@currency char(3)
begin
	
	select  @discount = isnull(discount,1) from ord_mst where 
	pono =@order_number
	
	

	select @currency = isnull(curunit,'HKD') from ord_item
    where 
	pono =@order_number
	group by curunit
	
	if(@discount>0) 
	select @discount = @discount/100.0
	else
	select @discount = 0.00
	begin
		select sum(quantity*unitprice) as Sub_total,
		isnull(sum(quantity*unitprice)*@discount,0) as Discount_total,
		(sum(quantity*unitprice) - (sum(quantity*unitprice)*@discount)) as GR_Total,
		  @currency as Currency,@discount as discount
		from ord_item     where pono =@order_number
	end
	
end
go

/*

 Proc_Get_PO_Amount 'PO-0005/03' 
 
 exec proc_Get_PO_Amount 'PO-0002/04'


*/


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





ALTER     procedure Ord_mst_Rev
@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
as
begin
if exists(select * from ord_mst where pono=@pono)
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
where pono=@pono

if(@@error<>0)
print('update error!')
return
end








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

Create procedure Proc_PO_GR_Upd
@Order_Number   varchar(30) = null,
@Dn_number      varchar(30) = null,
@itemseq        int = null,
@item_code  int=0,
@GR_Qty    numeric(12,3)=0.00,
@in_date	datetime=null,
@vendor_id	char(8) = null,
@created_by	varchar(10) = null,
@last_upd_by    varchar(10) = null,
@action_cd	char(1)=null

as
declare 
	@maxitem int,
	@Order_Total numeric(18,2),
	@Bal_total numeric(18,2)
	
if (@action_cd ='N') 
begin
	select @Order_total = isnull(sum(Quantity),0),@Bal_total =  isnull(sum(Balance_Qty),0) from ord_item 
	where pono =@order_number and item = @item_code

	if(@GR_Qty <=@Bal_total) 
	begin
		select @maxitem = isnull(itemseq,0)+1 from gtr_item where pono =@order_number
	
	 insert into gtr_item(
	Pono,
	dnno,
	itemseq,
	item,
	in_quantity,
	in_date,
	created_by,
	created_dt,
	last_upd_by,
	last_upd_dt,
	hpn,
	cpn,
	mpn,
	item_nm,
	vendor_id

)
select 
	@order_number,
	@dn_number,
	@maxitem,
	item,
	@gr_qty,
	@in_date,
	@created_by,
	getdate(),
	@created_by,
	getdate(),
	hpn,
	cpn,
	mpn,
	material_nm,
	@vendor_id
	from ord_item where pono =@order_number and itemseq = @itemseq
end

	if(not exists(select pono from ord_item where pono=@order_number and iscom ='N'))
	begin
		update ord_mst 
			set completed='YES',
	   		 Status ='F'
		where pono= @order_number

	end
end

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

Create Procedure Proc_PO_Status
@Order_Number varchar(18) = null
as
begin
	select  isnull(sum(quantity),0) as Qty,
	isnull(sum(received_qty),0) as GR_Qty, 
	(isnull(sum(quantity),0) - isnull(sum(received_qty),0)) as Outstd_qty,
	case when  (sum(quantity) - sum(received_qty)<=0) then 'YES' else 'NO' end as Status
	from ord_item where pono =@order_number
	
end
go

-- Proc_PO_Status 'PO04--32223'
-----------------------------------------------------------------------------------------------



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

create procedure Proc_Sign_upd
@action_cd    CHAR(1) = NULL,
@Order_number  VARCHAR(30) = NULL,
@Signatured_by VARCHAR(15) = NULL,
@Confirmed_by  VARCHAR(15) = NULL,
@Approved_by   VARCHAR(15) = NULL
as
begin
	if (@action_cd = 'S')
	begin
		if not exists(select order_number from  PO_Signature_List where order_number = @order_number)
		begin
			insert into PO_Signature_List
			(
			Order_number,
                        Status,
		        Signatured,
			Signatured_dt,
			Signatured_By,
			Confirmed,
			Confirmed_By,
			Confirmed_dt,
			Approved,
			Approved_By,
			Approved_Dt
			)
			values
			(
			 @order_number,
			 'A',
			 'Y',
			 getdate(),
			 @signatured_by,
			 'N',
			 '',
			 Getdate(),
			 'N',
			 '',
			getdate()
			)
		 end
				
	end
		else if(@action_cd ='C')
		begin
			update PO_Signature_List
				set confirmed = 'Y',
				    confirmed_by = @confirmed_by,
				    confirmed_dt = getdate()
			where order_number = @order_number
		end
	else if(@action_cd = 'A')
	begin
		update PO_Signature_List
			set approved = 'Y',
			 approved_by = @approved_by,
			 Approved_dt = getdate()
		where order_number = @order_number
	end
end
go

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



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

create procedure Proc_PO_Adjust_Line_upd
@order_number  varchar(30) = null,
@item          int = null,
@Adj_Ord_Qty   numeric(18,2)= null,
@Adj_GR_Qty    Numeric(18,2)= null,
@Adj_Bal_QTY   numeric(18,2) = null,
@Adjusted_by   varchar(15) =  null
as
begin
	insert into po_adjust_line
  (
  Order_number,
  Item,         
  Item_code,    
  HPN,		
  CPN,           
  Description ,  
  UoM,           
  Ord_Qty ,      
  Adj_Ord_Qty ,  
  GR_Qty,        
  Adj_GR_Qty ,   
  Balance_Qty ,  
  Adj_Bal_QTY ,  
  iscom,         
  Adjust_dt ,    
  Adjusted_by   
)
select 
  @Order_number,
  itemseq,         
  Item,    
  HPN,		
  CPN,           
  material_nm ,  
  UoM,           
  Quantity ,      
  @Adj_Ord_Qty ,  
  received_qty,        
  @Adj_GR_Qty ,   
  Balance_Qty ,  
  @Adj_Bal_QTY ,  
  case when(@Adj_Ord_Qty <= @Adj_GR_Qty) then 'Y' else 'N' end,         
  getdate() ,    
  @Adjusted_by  
from ord_item where pono = @order_number and itemseq = @item

if(isnull(@Adj_Bal_QTY,0) <= 0) 
update ord_item
	set iscom ='Y',
	received_Qty = @Adj_GR_Qty,
	balance_qty = 0
	where pono =@order_number and itemseq = @item
else
    update ord_item
	set iscom ='N',
	    received_Qty = @Adj_GR_Qty,
	    balance_qty = @Adj_Bal_QTY
	where pono =@order_number and itemseq = @item


 if not exists(select iscom from ord_item where pono = @order_number and iscom ='N') 
 update  ord_mst
	     set completed = 'YES',
		status = 'F'
	where pono = @order_number

else
	 update  ord_mst
	     set completed = 'NO',
		status = 'O'
	where pono = @order_number

end
 

go

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

create procedure PO_Adjust_Item_Upd
@action_cd char(1) = null,
@order_number varchar(30) =  null,
@Force_Completed char(1) = null,
@completed_by    varchar(15) = null,
@completed_Reason  varchar(120) = null
as
begin
	if(@action_cd ='F')
	begin
		if not exists(select order_number from po_adjust_item 
		where order_number = @order_number)
		begin
			insert into po_adjust_item values
			(@order_number,
			 'Y',
			 @Completed_by,
			 getdate(),
			@completed_reason
			)
			insert into po_adjust_line
			(order_number,item,item_code,hpn,cpn,description,uom,ord_qty,adj_ord_qty,gr_qty,adj_gr_qty,balance_qty,adj_bal_qty,iscom,adjust_dt,adjusted_by)
			select @order_number,itemseq,item,hpn,cpn,material_nm,uom,quantity,quantity,received_qty,received_qty,balance_qty,balance_qty,'Y',getdate(),@completed_by 
			from ord_item where pono = @order_number order by itemseq
				
			update ord_mst 	
				set completed ='YES',
				status = 'F'
				where pono = @order_number
			
			update ord_item
				set 
				received_qty = quantity,
				--balance_qty = 0,
				iscom = 'Y'
				where pono = @order_number

		end
		else 
		begin
			update po_adjust_item
				set force_completed = 'Y',
				    completed_by = @completed_by,
				    completed_reason = @completed_reason
			where order_number = @order_number
			
			update ord_mst 	
				set completed ='YES',
				status = 'F'
				where pono = @order_number
			
			update ord_item
				set
				received_qty = quantity,
				--balance_qty = 0,
				iscom = 'Y'
				where pono = @order_number

		end		
		
	end 

     else
	begin
			if not exists(select order_number from po_adjust_item where order_number = @order_number)
		begin
			insert into po_adjust_item values
			(@order_number,
			 'N',
			 NULL,