proc_adj_item_upd.sql

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

 
 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,
			 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