tb_parts_block.sql

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

select Item =identity(int,1,1),b.itemno as HPN,a.CPN,b.MPN,B.Description,unit as UoM,substring(b.itemno,14,5) as Customer_id,b.item_typ as Mtl_Type,b.consign_flat as Consign_Flag
 into #source_Data from PO_BLOCKED_PARTS a,baseitem b where a.cpn = b.cpn and isnull(b.isdeleted,'')=''


CREATE table PO_Blocked_Parts
(
 HPN VARCHAR(50) NOT NULL CONSTRAINT PK_PO_BLOCKED PRIMARY KEY,
 CPN VARCHAR(50) NOT NULL,
 MPN VARCHAR(50) NOT NULL,
 Description VARCHAR(250) NOT NULL,
 UoM CHAR(30) NOT NULL,
 Customer_ID CHAR(50) NULL,
 MTL_Type CHAR(50) NULL,
 Blocked bit not null default 1,
 Locked_By varchar(15) not null default 'admin',
 Remark varchar(120) null
)


insert into PO_Blocked_Parts(HPN,CPN,MPN,DESCRIPTION,UOM,Customer_id,mtl_type,Blocked,Locked_by,Remark)
select distinct HPN,CPN,MPN,DESCRIPTION,UOM,Customer_id,mtl_type,1,'Admin','禁止下有铅料PO' from #source_data
where HPN not in(select hpn from PO_Blocked_Parts)
GO

drop table #source_Data


select hpn from po_blocked_parts
group by hpn