finally_install_bk.sql

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

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




-------------------------------------------------------------------------------------------------------------------------------------  
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.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 

-----------------------------------------------------------------------------------------------------------------------
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
----------------------------------------------------------------------------
go

---先更屏蔽Identity off---

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
)
select 

item,
1,
vendor_id,
HPN,
CPN,
MPN,
Description,
MODEL,
Basic_UoM,
Vendor_UoM,
MOQ,
MPQ,
Currency,
Unit_Price,
getdate(),
Expiration_dt,
getdate(),
getdate(),
'admin',
'admin'
from po_information_record 

go

-------------------------------------------------------------------------------------------------
/*
	change ord_mst  status from int to char(1) default 'O'
*/

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

CREATE TABLE PO_Vendor_Mstr (
	item int NOT NULL ,
	Vendor_ID char (8) not  NULL,
	Status    Char(1) null default 'A',
	title	  varchar(30) not null default 'Company',	
	Vendor_class char (1) NULL,
	Vendor_nm varchar (255) not null,
	Vendor_nm1 varchar(255) null,	
	search_terms1 varchar(30) null,
	search_terms2 varchar(30) null,
	street 	      varchar(120) null,
	house_number  varchar(30) null,
	postal_code   varchar(7) null,
	city	      varchar(30) null,
	country	      varchar(30) null,
	language      varchar(30) null,
	TEL varchar (30)  NULL ,
	TEL1 varchar(30)   null,
	FAX varchar (30)  NULL ,
	FAX1 varchar(30) null,
	extersion   varchar(30) null,
	mobile_phone1 varchar(30) null,
	mobile_phone2 varchar(30) null,
	cont1 varchar (30)  NULL ,
	cont2 varchar (30)  NULL ,	
	address  varchar(255) not null,
	address1  varchar(255) null,
	address2  varchar(255) null,
	deft_Address varchar (255)  NULL ,
	Deft_Currency  CHAR(3) NULL,
	Mult_Currency  bit null default 0,
	Currency1      CHAR(3) null,
	Currency2      CHAR(3) null,
	inv_verify_flag bit null default 0,
	MOQ	       Numeric(18,3) default 0.00,
	Deliv_Plant    CHAR(4) null,
	Pur_org	       CHAR(4) null,
	Plant	       CHAR(4) NULL,
	Pur_grp	       CHAR(4) NULL,
	payterms varchar (125)  NULL ,
	Account_no	VARCHAR(8) NULL,
	discount	numeric(18,3) null default 0.00,
	handling_chrg   numeric(18,3) null default 0.00,
	website varchar (80)  NULL ,
	email varchar (80)  NULL ,
	Remark varchar (255) null,
	creation_dt datetime NULL, 
	created_by  varchar(15) not null,
	update_dt datetime not null default getdate(),
	updated_by varchar(15) not null,
	CONSTRAINT [PK_po_vendor_mst] PRIMARY KEY  CLUSTERED 
	(
		[item],
		[vendor_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO


insert into po_vendor_mstr
(
item,
Vendor_ID,
vendor_class,
vendor_nm,
TEL,
FAX,
cont1,
cont2,
Address,
deft_address,
payterms,
website,
email,
Remark,
creation_dt,
created_by,
update_dt,
updated_by
)
select 
item,
ID,
'V',
Description,
TEL,
FAX,
cont1,
cont2,
Address,
Address,
payterms,
website,
email,
bz,
Updatetime,
'admin',
getdate(),
'admin'
from ven_cust_mst 

go



   update ord_mst
	  set status = 'O'
	  where completed ='NO'
go

    update Ord_mst 
	   set status ='F'
	   where completed ='YES'
go


update ord_mst
	set currency = b.curunit,
	Printed ='Y'
 from ord_mst a,(select pono,curunit from ord_item  where isnull(curunit,'')<>'' 
group by pono,curunit ) b
where a.pono = b.pono

go

update ord_mst 
	set payterms = b.payterms
from ord_mst a,po_vendor_mstr b
where a.id = b.vendor_id

go

------------------------------------------------------------------------------------------------
alter table gtr_item
drop column  total_quantity,
balance,
Remark,
uom,
vendor_nm,
ord_item_seq

go


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

Create table GL_Currency_Mstr
(
 Currency	CHAR(3) NOT NULL constraint PK_GL_Currency primary key,
 Description    varchar(30) null,
 lang           CHAR(2) NOT NULL DEFAULT 'EN'
)

go

insert into GL_Currency_Mstr
(
 Currency,
 Lang
)
select 
curunit,
'EN'
from stockmanage..ord_item
where isnull(curunit,'')<>'' group by curunit
order by curunit




-------------------------------------------------------------------------------------

IF OBJECT_ID('DBO.GL_SYS_Module') is not null
drop table DBO.GL_SYS_Module
go

create table GL_SYS_Module
(
  Module_ID  CHAR(4) NOT NULL PRIMARY KEY,
  Description VARCHAR(120) NOT NULL
)

INSERT INTO GL_SYS_Module
values
(
 '2000',
 'Purchasing Application'
)
go
------------------------------------------------------------------------------------
if object_id('dbo.GL_GUI_Component') is not null
drop table dbo.GL_GUI_Component
go

create table GL_GUI_Component
(
 Item  int identity(1,1) not null primary key,
 Module_ID CHAR(4) NOT NULL constraint fk_gl_gui references GL_SYS_Module(Module_ID),
 Form_ID   VARCHAR(30) NOT NULL,
 Component_Name VARCHAR(30) NOT NULL,
 Default_caption varchar(120) not null,
 EN        VARCHAR(250) null,
 ZH        VARCHAR(250) NULL,
 CH        VARCHAR(250) NULL
)

go

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

Create table GL_UoM_Mstr
(
  UoM_cd   CHAR(3) not null constraint pk_uom_mstr primary  key,
  Dim_cd   varchar(30) null,
  iso_cd   varchar(15) null,
  lang_cd  CHAR(3) not null default 'ENG',
  Description varchar(30) null,
  alt_nm    varchar(30) null
)
go

insert into GL_UoM_Mstr
(
	uom_cd,
	lang_cd
)
select 
	uom,
	'ENG'
from stockmanage..ord_item
	group by uom
go
-----------------------------------------------------------------------------------
if object_id('dbo.PO_Signature_List') is not null
drop table dbo.PO_Signature_List
go

create table PO_Signature_List
(
  Order_number varchar(30) not null primary key,
  Status       CHAR(1) NOT NULL DEFAULT 'A',
  Signatured   CHAR(1) NOT NULL DEFAULT 'N',
  Signatured_dt datetime not null default getdate(),
  Signatured_By VARCHAR(15) NOT NULL,
  Confirmed     CHAR(1) NOT NULL DEFAULT 'N',
  Confirmed_By  VARCHAR(15) NULL,
  Confirmed_dt  Datetime not null default getdate(),
  Approved      CHAR(1) NOT NULL DEFAULT 'N',
  Approved_By   VARCHAR(15) NULL,
  Approved_Dt   datetime not null default getdate()
)

go

insert into PO_Signature_List
(
 Order_number,
 Status,
 Signatured,
 Signatured_dt,
 Signatured_by,
 Confirmed,
 Confirmed_by,
 Confirmed_dt,
 Approved
)
select 
	pono,
	'A',
	'Y',
	getdate(),
	isnull(creator,'admin'),
	'Y',
	'Ricky',
	getdate(),
	'N'
	from ord_mst


 go
---------------------------------------------------------------------------------

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

create table User_Signature_Library
(
  userid varchar(15) not null,
  user_desc varchar(30) not null,
  signature_img image null
)
go

insert into User_Signature_Library
(
 userid,
 user_desc
)
select userid,user_nm from pu_user_mstr

go
-----------------------------------------------------------------------------------------------
if object_id('dbo.viw_MIS_GR_List') is not null
 drop view viw_MIS_GR_List
go

create  view viw_MIS_GR_List
as
select a.Pono,a.dn_no,a.fir_date as gr_date,a.cher as gr_by,b.ItemNo as Hpn,