proc_order_item_upd.sql

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

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 
@vendor_id varchar(10),
@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 
				select @vendor_id = ID from  ord_mst 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
                            )
				
			
			  if exists(select hpn from PO_Quo_References where vendor_id =@vendor_id 
				and hpn = @hpn and moq =@moq)
			 begin
			    update  PO_Quo_References
				set unit_price  = @unitprice,
				    modify_dt = getdate()
				where vendor_id =@vendor_id and hpn = @hpn and moq = @moq	
			 end
				else
			 begin
				insert into PO_Quo_References
				(vendor_id,Order_number,hpn,cpn,MOQ,Unit_Price,currency,modify_dt)
				values(@vendor_id,@pono,@hpn,@cpn,@moq,@unitprice,@curunit,getdate())
			 end
				
			  update ord_mst
				set status ='O',
					completed ='YES'
				where pono =@pono

	  			
	
				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

				  if exists(select hpn from PO_Quo_References where vendor_id =@vendor_id 
				and hpn = @hpn and moq = @moq)
			 begin
			    update  PO_Quo_References
				set unit_price  = @unitprice,
				    modify_dt = getdate()
				where vendor_id =@vendor_id and hpn = @hpn and moq = @moq	
			 end
				else
			 begin
				insert into PO_Quo_References
				(vendor_id,Order_number,hpn,cpn,MOQ,Unit_Price,currency,modify_dt)
				values(@vendor_id,@pono,@hpn,@cpn,@moq,@unitprice,@curunit,getdate())
			 end

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