step2.sql

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

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


CREATE TABLE PO_Information_Record
(
item		int not null constraint pk_po_item_mstr primary key,
vendor_id	CHAR(8) NOT NULL,
vendor_nm	VARCHAR(120) NOT NULL,
sug_vendor_id   CHAR(8) NULL,
sug_vendor_nm	VARCHAR(120) NOT NULL,
HPN		VARCHAR(18) NULL,
MTL_TYPE_CD	CHAR(4) NOT NULL DEFAULT 'RMAT',
Priority	CHAR(1) NOT NULL DEFAULT 'H',
Description	VARCHAR(255) NOT NULL,
CPN		VARCHAR(32) NULL,
MPN		VARCHAR(32) NULL,
MODEL		VARCHAR(80) NULL,
Lead_Time	int not null default 0,
MOQ		int not null default 0,
MPQ		int not null default 0,
Unit_Price	float not null default 0.0000001,
Currency	CHAR(3) NOT NULL DEFAULT 'RMB',
Item_Class_cd	CHAR(2) NULL,
Basic_UoM	CHAR(3) NOT NULL DEFAULT 'PCS',
Vendor_UoM	CHAR(3) NOT NULL DEFAULT 'PCS',
Customer_ID	CHAR(5) NULL,
Status          CHAR(1) NOT NULL DEFAULT 'A',
effectived_dt   datetime not null default getdate(),
Expiration_dt   Datetime not null default Getdate(),
Created_by	VARCHAR(15) NOT NULL,
Creation_dt	datetime not null default getdate(),
Updated_by	VARCHAR(15) NOT NULL ,
Update_dt	Datetime not null default getdate()
)
go

delete from mat_mst where material_nm  is null
go

update mat_mst 
	set vendor_nm = b.description
from mat_mst a,ven_cust_mst b
where a.id = b.id and a.vendor_nm is null
go


if object_id('dbo.PO_Price_Mstr') IS Not null
drop table dbo.PO_Price_Mstr
go

create table PO_Price_Mstr
(
 Itemseq   int identity(1,1) not null primary key,
 item int   not null constraint fk_item references PO_Information_Record(Item),
 Line int   not null default 1,
 Vendor_id CHAR(8) NOT NULL,
 HPN       VARCHAR(18)  NULL,
 CPN       VARCHAR(32) NULL,
 MPN       VARCHAR(32) NULL,
 Status    CHAR(1) not null Default 'A',
 Description varchar(250) not null,
 Model     varchar(80) null,
 Basic_uom char(3) not null,
 Vendor_uom CHAR(3) null,
 MOQ        Int  null default 0,
 MPQ        int null default 0,
 Currency   CHAR(3)  not null,
 Unit_Price   float not null default 0.000001,
 Effective_dt datetime not null default getdate(),
 Expire_dt    datetime not null default getdate(),
 Creation_dt  datetime not null default getdate(),
 Update_dt    datetime not null default getdate(),
 Created_by   VARCHAR(15) NOT NULL,
 Updated_by   VARCHAR(15) 
)
------------------------------------------------
insert into PO_Information_Record
(
item,
vendor_id,
vendor_nm,
sug_vendor_id,
sug_vendor_nm,
HPN,
MTL_TYPE_CD,
Priority,
Description,
CPN,
MPN,
MODEL,
Lead_Time,
MOQ,
MPQ,
Unit_Price,
Currency,
Basic_UoM,
Vendor_UoM,
Status,
effectived_dt,
Expiration_dt,
Created_by,
Creation_dt,
Updated_by,
Update_dt
)
select 
item,
id,
vendor_nm,
id,
vendor_nm,
partnum,
'RMAT',
'H',
material_nm,
cpn,
matr_code,
model,
lead_time,
moq,
mpq,
unitprice,
isnull(curunit,'RMB'),
isnull(Uom,'PCS'),
isnull(Uom,'PCS'),
'A',
getdate(),
convert(datetime,'2012-12-31'),
isnull(create_by,'admin'),
isnull(create_dt,getdate()),
isnull(update_by,'admin'),
isnull(last_up,getdate())
from mat_mst order by item
go



drop table po_item_up_his_log
drop table po_item_up_his
go


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

create view mat_mst 
as
select 
item,
vendor_id as id,
vendor_Nm,
hpn as partnum,
cpn,
mpn as matr_code,
description as material_nm,
model,
unit_price as unitprice,
currency as curunit,
moq,
mpq,
basic_uom as Uom,
lead_time,
creation_dt as create_dt,
created_by as create_by,
update_dt as last_upd_dt,
update_dt as last_up,
updated_by as update_by,
mtl_type_cd as matl_typ,
item_class_cd
from po_information_record
go
-----------------------------------------------------------------------------




go 

-----------------------------------------------------------------------------------------------------------------------
if object_id('dbo.trg_Source_List_new') is not null
drop trigger dbo.trg_Source_List_New
go


create trigger trg_Source_List_new
on dbo.PO_Information_Record
for insert
as
begin
	insert into po_price_mstr
(item,
 Line,
 vendor_id,
 hpn,
 cpn,
 mpn,
 Description,
 model,
 Basic_uom,
 vendor_uom,
 moq,
 mpq,
 currency,
 unit_price,
 effective_dt,
 expire_dt,
 creation_dt,
 update_dt,
 created_by,
 updated_by,
 status
)
select 
 item,
 1,
 vendor_id,
 hpn,
 cpn,
 mpn,
 Description,
 model,
 Basic_uom,
 vendor_uom,
 moq,
 mpq,
 currency,
 unit_price,
 getdate(),
 expiration_dt,
 getdate(),
 getdate(),
 created_by,
 updated_by,
 status
from inserted
end