migration_po_master_record_hdr.sql

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

select 
item = identity(int,1,1),
b.HPN,
'A' as Status,
a.ID as Vendor_ID,
b.CPN,
b.MPN,
b.MODEL,
b.UoM,
min(b.unitprice) as Unit_Price,
b.curunit as Currency,
a.pono as Last_PO,
'N' as Blocked,
max(b.created_dt) as Creation_dt
into #last_po_item
from ord_mst a,ord_item b where a.pono = b.pono and isnull(b.hpn,'')<>''
and b.hpn in(select hpn from GL_BCMTL_Mstr)
group by b.HPN,
a.ID,
b.CPN,
b.MPN,
b.MODEL,
b.UoM,
b.curunit,
a.pono

go

select max(item) as item,hpn into #conditions from #last_po_item group by hpn
go


insert into PO_Master_Record_Hdr
(
HPN,
Status,
Vendor_id,
CPN,
MPN,
MODEL,
UoM,
Unit_Price,
Currency,
Last_PO,
Blocked
)
select
a.HPN,
a.Status,
a.Vendor_id,
a.CPN,
a.MPN,
a.MODEL,
a.UoM,
a.Unit_Price,
a.Currency,
a.Last_PO,
a.Blocked
from
#last_po_item a, #conditions b 
where a.item = b.item
go

drop table #conditions
drop table #last_po_item
go

insert into PO_Master_Record_item
(
HPN,
Vendor_id,
Lead_Time,
MOQ,
MPQ,
Unit_Price,
Currency,
Priority,
Active,
Created_by,
Created_dt,
Updated_by,
Updated_dt
)
select 
HPN,
Vendor_id,
0,
0,
0,
Unit_Price,
Currency,
'H',
0,
'Admin',
getdate(),
'Admin',
getdate()
from PO_Master_Record_hdr
go