step3.sql

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

----------------------------------------------------------------------------
---先更屏蔽Identity off---

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
)
select 

item,
1,
vendor_id,
HPN,
CPN,
MPN,
Description,
MODEL,
Basic_UoM,
Vendor_UoM,
MOQ,
MPQ,
Currency,
Unit_Price,
getdate(),
Expiration_dt,
getdate(),
getdate(),
'admin',
'admin'
from po_information_record 

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