proc_poprint_out.sql

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

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


create procedure Proc_POPrint_Out
@Order_Number varchar(30) = null,
@pg_count int output 
as
 declare
   @row_count int,
   @bal int, 
   @i   int
begin
		
	Create table #tb_Aut_Generate
(Order_number varchar(30) not null,
 Itemseq 	int null,
 Item_code      int null,
 HPN            VARCHAR(18) NULL,
 CPN            VARCHAR(30) NULL,
 MPN            VARCHAR(30) NULL,
 Description    varchar(120) NULL,
 Model          varchar(30) NULL,
 UoM            CHAR(3) NULL,
 Unit_Price     float,
 Currency       CHAR(3) null,
 Qty	numeric(18,2) null,
 Amount numeric(18,4) null,
 deliv_dt       varchar(12) null
)
insert into #tb_Aut_Generate(
Order_number,
itemseq,
Item_code,
HPN,
CPN,
MPN,
Description,
model,
uom,
unit_price,
Currency,
Qty,
Amount,
Deliv_Dt)
select 
 PONO,
itemseq,
Item,
HPN,
CPN,
MPN,
material_nm,
model,
uom,
unitprice,
curunit,
quantity,
Amount = convert(numeric(18,4),unitprice * quantity),
dbo.ConvertDateToStr(deli_day)
 from ord_item  
where pono=@Order_number
 order by itemseq

select @i = 0
select @row_count =isnull((select count(*) from  #tb_Aut_Generate where Order_Number= @order_number),0)

            if(@row_count<16) 
            begin
                while (16-@row_count)-@i>0
		begin
                	insert into #tb_Aut_Generate(Order_number)values(@Order_number)
			select @i = @i+1
		end
            end
            else if(@row_count>16) and (round((@row_count % 16),1)<>0) 
            begin
                select @bal = ((round((@row_count /16),1)+1) * 16) -@row_count
               
               while (@bal-@i)>0
		begin
                   insert into #tb_Aut_Generate(Order_number)values(@Order_number)
		   select @i = @i+1
	 	end
           end 
             
		if(round((@row_count / 16),1)<>0) 
                 select @pg_count = round((@row_count / 16),1)+1
                 else
                 select  @pg_count = round((@row_count / 16),1)
		 if(@pg_count<=0) 
		select @Pg_count = 1
      		 
	   select * from #tb_Aut_Generate
        
			select  @pg_count
	drop table #tb_Aut_Generate
end
go

/*
 declare
 @Pg_count int 
 exec Proc_POPrint_Out 'PO04-03234',@pg_count output

exec Proc_Get_PO_Amount 'PO04-03234'

*/