?? finally_install_bk.sql
字號:
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,
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -