?? finally_install_bk.sql
字號:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
/*
Caution!!!!!!!
change ord_mst table 's Status column datatype from int to char(1)
*/
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
if object_id('dbo.GL_BCMTL_Mstr') is not null
drop table dbo.GL_BCMTL_Mstr
go
Create table GL_BCMTL_Mstr
(
Client VARCHAR(4) NOT NULL DEFAULT '1000',
HPN varchar(18) not null constraint PK_GL_HPN_Basic primary key,
CPN varchar(50) null,
MPN varchar(50) null,
VPN varchar(50) null,
Material varchar(250) NOT NULL,
MTL_Type CHAR(4) NULL Default 'RMAT',
UoM CHAR(3) NOT NULL,
MTL_GROUP CHAR(4) NULL,
EXT_MTL_GROUP CHAR(4) NULL,
Old_HPN VARCHAR(18) NULL,
Division CHAR(4) NULL,
Vaild_Dt Datetime not null default getdate(),
Gross_Weight numeric(18,5) null default 0.00,
Weight_Unit CHAR(3) NULL,
Net_Weight numeric(18,5) null default 0.00,
Volume numeric(18,5) null,
Volume_Unit CHAR(3) NULL,
Size_Dimen numeric(18,5) null,
Packing_Type CHAR(4) NULL,
Language CHAR(2) NOT NULL default 'EN',
Mult_Language bit not null default 0,
Consignment_Flag bit not null default 0,
Customer_ID CHAR(5) NULL,
Status CHAR(1) NOT NULL Default 'A',
Blocked_Flag bit not null default 0,
Deleted_Flag bit not null default 0,
Remark varchar(250) null,
Creation_dt datetime not null default getdate(),
Created_By VARCHAR(15) NOT NULL,
Update_dt Datetime not null default getdate(),
Updated_By varchar(15) not null
)
go
-------------------------------------------------------------------------------
if object_id('dbo.PO_Master_Record_Hdr') is not null
drop table dbo.PO_Master_Record_Hdr
go
Create table PO_Master_Record_Hdr
(
HPN varchar(18) not null constraint pk_PO_HPN primary key references GL_BCMTL_Mstr(HPN),
Status CHAR(1) NOT NULL Default 'A',
Vendor_id varchar(10) not null,
CPN varchar(30) null,
MPN Varchar(30) null,
MODEL VARCHAR(30) NULL,
UoM CHAR(3) NOT NULL,
Unit_Price Float not null default 0.0000,
Currency CHAR(3) NOT NULL,
Last_PO VARCHAR(30) NULL,
Blocked CHAR(1) NOT NULL default 'N'
)
go
/*---------------------------------------------------------------------------------------------*/
if object_id('dbo.PO_Master_Record_item') is not null
drop table dbo.PO_Master_Record_item
go
Create table PO_Master_Record_item
(
HPN varchar(18) not null constraint fk_hpn_mstr references PO_Master_Record_Hdr(HPN),
Vendor_id varchar(10) not 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,
Currency CHAR(3) not null,
Priority CHAR(1) default 'L',
Active bit not null default 0,
Created_by varchar(15) not null,
Created_dt datetime not null,
Updated_by varchar(15) null,
Updated_dt datetime not null
)
go
-------------------------------------------------------------------------------
if object_id('dbo.GL_Org_Type') is not null
drop table dbo.GL_Org_Type
go
create table GL_Org_Type
(
Org_type_cd CHAR(2) NOT NULL,
Org_Desc varchar(80) null,
Alt_nm varchar(80) null
)
go
insert into GL_Org_Type values('BA','Business Area','Business Area')
insert into GL_Org_Type values('CO','Company Code',null)
insert into GL_Org_Type values('DP','Department',null)
insert into GL_Org_Type values('PG','Purchasing Group',null)
insert into GL_Org_Type values('PL','Plant',null)
insert into GL_Org_Type values('PO','Purchasing Organization',null)
insert into GL_Org_Type values('SO','Sale Organization',null)
go
-------------------------------------------------------------------------------
if object_id('dbo.PO_Number_List') is not null
drop table PO_Number_List
go
create table PO_Number_List
(
item int identity(1,1) not null primary key,
Order_number varchar(12) not null,
who_used varchar(15) not null,
used_dt datetime not null default getdate()
)
go
-------------------------------------------------------------------------------
if object_id('dbo.GL_mm_Mstr') is not null
drop view dbo.GL_mm_Mstr
go
create view GL_mm_Mstr
as
select itemno as HPN,
CPN,
MPN,
Description,
Cust_no as Customer_ID,
Unit as Basic_UoM,
Item_typ as Material_Type,
Consign_flat as Consign_flag,
Fir_oper as Created_by,
Fir_date as Creation_dt,
cher as Updated_by,
che_date as Update_dt,
che as Approved
from baseitem
go
-------------------------------------------------------------------------------
if object_id('dbo.PO_Quo_References') is not null
drop table dbo.PO_Quo_References
go
create table PO_Quo_References
(
Vendor_ID varchar(10) not null,
Order_Number varchar(30) not null,
HPN varchar(18) not null,
CPN varchar(32) null,
MOQ numeric(18,2) not null default 0.00,
Unit_Price float not null default 0.000,
Currency CHAR(3) not null,
Modify_dt datetime not null default getdate()
)
-------------------------------------------------------------------------------
if object_id('dbo.viw_Price_History') is not null
drop view dbo.viw_Price_History
go
create view viw_Price_History
as
select hpn,unitprice as Unit_Price ,curunit as Currency,max(created_dt) as Creation_dt
from ord_item where isnull(hpn,'')<>''
group by hpn,unitprice,curunit
go
-------------------------------------------------------------------------------
if object_id('dbo.GL_mm_Mstr') is not null
drop view dbo.GL_mm_Mstr
go
create view GL_mm_Mstr
as
select itemno as HPN,
CPN,
MPN,
Description,
Cust_no as Customer_ID,
Unit as Basic_UoM,
Item_typ as Material_Type,
Consign_flat as Consign_flag,
Fir_oper as Created_by,
Fir_date as Creation_dt,
cher as Updated_by,
che_date as Update_dt,
che as Approved
from baseitem
-------------------------------------------------------------------------------
go
update ord_mst
set last_up = b.created_dt,
post_date = b.created_dt,
rev_date = b.created_dt,
Signatured_dt = b.created_dt,
confirmed_dt = b.created_dt
from ord_mst a,ord_item b where a.pono = b.pono
and a.last_up>'2004-01-01'
and b.itemseq = 1
go
-------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xord_mst]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xord_mst]
GO
CREATE TABLE [dbo].[xord_mst] (
[xitemseq] [int] IDENTITY (1, 1) NOT NULL ,
[action_type] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[action_date] [datetime] NULL ,
[PONO] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[ID] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[vendor_nm] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[Prno] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[discount] [numeric](4, 2) NULL ,
[deli_loc] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[Total] [numeric](15, 2) NULL ,
[Completed] [char] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[post_date] [datetime] NULL ,
[remark] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[status] [char] (1) NULL ,
[last_up] [datetime] NULL ,
[Creator] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Last_Modify] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
-------------------------------------------------------------------------------
if object_id('dbo.PO_VND_ID_Number') is not null
drop table dbo.PO_VND_ID_Number
go
create table PO_VND_ID_Number
(
number int not null default 1,
vendor_id varchar(8) not null
)
declare
@maxid int
select @maxid = isnull(count(*),0)+1 from ven_cust_mst
--select @maxid
insert into PO_VND_ID_Number values(@maxid,'V0000'+convert(char(3),(@maxid-1)))
go
-------------------------------------------------------------------------------
if object_id('dbo.PO_Price_Log') is not null
drop table dbo.PO_Price_Log
go
create table PO_Price_Log
(
item int not null,
Line int not null default (1),
HPN VARCHAR(18) NULL,
Status CHAR(1) not null default 'A',
OLD_Price float not null default 0.0000,
New_Price float not null default 0.0000,
Modify_dt datetime not null default getdate(),
Modified_By varchar(15) not null
)
-------------------------------------------------------------------------------
if object_id('dbo.PO_Number_List') is not null
drop table PO_Number_List
go
create table PO_Number_List
(
item int identity(1,1) not null primary key,
Order_number varchar(12) not null,
who_used varchar(15) not null,
used_dt datetime not null default getdate()
)
go
-------------------------------------------------------------------------------
if object_id('dbo.GL_Payment_Mstr') is not null
drop table dbo.GL_Payment_Mstr
go
create table GL_Payment_Mstr
(
item int identity(100,1) not null,
Payment varchar(120) not null,
Language CHAR(2) null default 'EN'
)
go
insert into GL_Payment_Mstr(payment,language)
select payterms ,'EN' from po_vendor_mstr
where len(payterms)>4
group by payterms
------------------------------------------------------------------------------------------
go
if object_id('dbo.Cancel_PO_Log') is not null
drop table dbo.Cancel_PO_Log
go
create table Cancel_PO_Log
(
Order_Number varchar(30) not null constraint pk_po_log primary key,
Action_type VARCHAR(30) NOT NULL,
Action_dt datetime not null default getdate(),
Action_by varchar(15) not null,
Reason varchar(250) not null,
Remark varchar(250) null
)
---------------------------------------------------------------------------------------------------------
go
if object_id('dbo.GL_Currency') is not null
drop table dbo.GL_Currency
go
create table GL_Currency
(
Currency CHAR(3) NOT NULL,
Description VARCHAR(30) NULL,
Target_currency CHAR(3) NULL,
Actived CHAR(1) not NULL default 'N',
Rate Numeric(18,5) not null default 1.0000
)
go
insert into GL_Currency
(
Currency,
Description,
Target_Currency,
Actived,
Rate
)
select
curunit,
'NULL',
curunit,
'N',
1.0000
FROM mat_mst
where len(curunit)=3
group by curunit
------------------------------------------------------------------------------------------------
go
alter table ord_mst
add Customer_ID CHAR(5) NULL,
currency CHAR(3) NOT NULL default 'HKD',
Payterms varchar(120) NOT NULL default 'C.O.D',
Printed char(1) not null default 'N',
Printed_By VARCHAR(15) Not null default 'Admin',
Buyer_Sig Char(1) NOT NULL Default 'N',
Signatured_by VARCHAR(15) NULL,
Signatured_dt datetime not null default getdate(),
Confirmed_Sig CHAR(1) NOT NULL DEFAULT 'N',
Confirmed_by VARCHAR(15) NULL,
Confirmed_dt datetime not null default getdate(),
Approved_Sig Char(1) NOT NULL Default 'N',
Approved_By VARCHAR(15) NULL,
Approved_dt datetime not null default Getdate(),
Closed_Flag CHAR(1) NOT NULL Default 'N',
Closed_By VARCHAR(15) NULL,
Closed_Dt Datetime not null default Getdate()
go
/*-------------------------------------------------------
alter table Ord_mst
alter Column status char(1) not null
************* need to manually alter the table ord_mst 'status field ***************
*/
alter table ord_item
add--rice_effective_dt datetime null,
--ice_expiration_dt datetime null,
mtl_type_cd char(4) not null default 'RMAT'
go
update ord_item
set price_effective_dt = created_dt,
price_expiration_dt = '2012-12-31'
go
if object_id('dbo.PO_Adjust_Item') is not null
drop table dbo.PO_Adjust_Item
go
Create table PO_Adjust_Item
(
Order_Number varchar(30) constraint pk_po_adjust primary key,
Force_Completed CHAR(1) NOT NULL Default 'N',
Completed_by varchar(15) null,
Completed_dt datetime not null default getdate(),
Completed_Reason VARCHAR(120) NULL
)
go
if object_id('dbo.PO_Adjust_Line') is not null
drop table dbo.PO_Adjust_Line
go
create table PO_Adjust_Line
(
Order_Number varchar(30) not null constraint fk_po_adjust_line references PO_Adjust_Item(Order_number),
Item int not null,
Item_code int,
HPN varchar(18) null,
CPN varchar(32) null,
Description varchar(250) not null,
UoM CHAR(3) NULL,
Ord_Qty Numeric(18,2) not null default 0.00,
Adj_Ord_Qty numeric(18,2) not null default 0.00,
GR_Qty numeric(18,2) not null default 0.00,
Adj_GR_Qty Numeric(18,2) not null default 0.00,
Balance_Qty numeric(18,2) not null default 0.00,
Adj_Bal_QTY numeric(18,2) not null default 0.00,
iscom CHAR(1) not null default 'N',
Adjust_dt datetime not null default getdate(),
Adjusted_by varchar(15) not 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)
)
------------------------------------------------
go
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,
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -