finally_install_bk.sql

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

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
/*
     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,