if object_id('dbo.GL_Status') is not null
drop table dbo.GL_Status
go
Create table GL_Status
(
 Module_id CHAR(4) NOT NULL DEFAULT '2000',
 Status_Type 	CHAR(1) NOT NULL DEFAULT 'R',--N: Number/Order Status,R:--Record Status
 Status_Key	CHAR(1) NOT NULL DEFAULT 'A',
 Description    varchar(30) not null,
 Alt_nm		varchar(30) not null
)
go
	
insert into GL_Status values('2000','N','O','Opening','開放的')
insert into GL_Status values('2000','N','A','Active','活動的')
insert into GL_Status values('2000','N','C','Cancelled','已取消的')
insert into GL_Status values('2000','N','D','Deleted','已刪除的')
insert into GL_Status values('2000','N','F','Finished','已完成的')
insert into GL_Status values('2000','N','B','Blocked','已鎖定的')
--insert into GL_Status values('2000','N','Y','Completed','已完成的')
--insert into GL_Status values('2000','N','N','Noncomplete','未完成的')

insert into GL_Status values('2000','R','A','Active','活動的')
insert into GL_Status values('2000','R','C','Cancelled','已取消的')
insert into GL_Status values('2000','R','D','Deleted','已刪除的')
insert into GL_Status values('2000','R','F','Finished','已完成的')
insert into GL_Status values('2000','R','B','Blocked','已鎖定的')
--insert into GL_Status values('2000','R','Y','Completed','已完成的')
--insert into GL_Status values('2000','R','N','Noncomplete','未完成的')

go


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

Create table IM_Location
(
 Plant char(4) not null,
 Location varchar(120) not null,
 Alt_nm   varchar(120) not null
)
go
insert into IM_Location values('1200','Hip Fung Shenzhen PingHu Plant','協豐電子深圳平湖廠')
insert into IM_Location values('1300','Hip Fung Shenzhen LongHua Plant','協豐電子深圳龍華廠')
go

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

Create table IM_ISSUE_Type
(
 Issue_Type CHAR(3) NOT NULL constraint pk_issue_type primary key,
 ISSUE_Desc varchar(120) not null
)
go

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

create table IM_LOG_Mstr
(
 Item  int not null constraint pk_im_log_mstr primary key,
 Plant char(4) not null,
 Version  numeric(5,2) not null default 1.0,
 Status	CHAR(1) NOT NULL DEFAULT 'A',
 Priority	CHAR(1) not null default ('L'),
 IM_DT	datetime not null default getdate(),
 Vendor_id CHAR(8) NOT NULL constraint fk_vendor_id references PO_VENDOR_Mstr(vendor_id),
 ISSUE_TYPE CHAR(3) NOT NULL constraint fk_issue_type references IM_Issue_Type(Issue_Type),
 DN_Number varchar(15)  null,
 Order_Number varchar(80) null,
 HPN	      VARCHAR(18) null,
 CPN	      VARCHAR(30) null,
 Buyer	      varchar(30) null,
 Report_dt    datetime not null default getdate(),
 Rcv_dt       datetime not null default getdate(),
 Read_flag    CHAR(1) NOT NULL,
 Reply_flag   CHAR(1) NOT NULL,
 Reply_dt     datetime not null default getdate(),
 Reply_by     varchar(15) not null,
 Remark	      varchar(250) null,
 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