tb_po_order_number.sql

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

if object_id('dbo.PO_Order_Number') is not null
drop table dbo.PO_Order_Number
go

create table PO_Order_Number
(
 IDEN  CHAR(4) NOT NULL DEFAULT 'PO',
 Number int not null default 1,
 Last_Number VARCHAR(30) NOT NULL
)

declare
 @f1 int,
 @f2 int,
 @f3 int,
 @f4 int

select @f1 =isnull(count(*),0)+1  from ord_mst where pono like 'PO04%'
select @f2 =isnull(count(*),0)+1  from ord_mst where pono like 'POL04%'
select @f3 =isnull(count(*),0)+1  from ord_mst where pono like 'POM04%'
select @f4 =isnull(count(*),0)+1  from ord_mst where pono like 'POML04%'

insert into PO_Order_Number (IDEN,Number,Last_Number)values('PO',@f1-1,'PO04-00'+convert(varchar(4),@f1-1))

insert into PO_Order_Number (IDEN,Number,Last_Number)values('POL',@f2-1,'POL04-00'+convert(varchar(4),@f2-1))

insert into PO_Order_Number (IDEN,Number,Last_Number)values('POM',@f3-1,'POM04-0'+convert(varchar(4),@f3-1))
insert into PO_Order_Number (IDEN,Number,Last_Number)values('POML',@f4,'POML04-000'+convert(char(1),@f4))


select * from PO_Order_Number