if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLCOMPANY9]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPLANT
]
DROP
CONSTRAINT
RefTBLCOMPANY9
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLCOMPANY2]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDER
]
DROP
CONSTRAINT
RefTBLCOMPANY2
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLITEM11]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPLANTITEM
]
DROP
CONSTRAINT
RefTBLITEM11
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLITEM4]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDERLINE
]
DROP
CONSTRAINT
RefTBLITEM4
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLPLANT10]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPLANTITEM
]
DROP
CONSTRAINT
RefTBLPLANT10
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLPLANT5]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDERLINE
]
DROP
CONSTRAINT
RefTBLPLANT5
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLPURCHASEGROUP7]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEGROUPUSER
]
DROP
CONSTRAINT
RefTBLPURCHASEGROUP7
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLPURCHASEGROUP8]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDER
]
DROP
CONSTRAINT
RefTBLPURCHASEGROUP8
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLPURCHASEORDER1]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDERLINE
]
DROP
CONSTRAINT
RefTBLPURCHASEORDER1
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLUSER6]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEGROUPUSER
]
DROP
CONSTRAINT
RefTBLUSER6
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[RefTBLVENDOR3]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsForeignKey
‘
)
=
1
)
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDER
]
DROP
CONSTRAINT
RefTBLVENDOR3
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLCOMPANY]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLCOMPANY
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLITEM]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLITEM
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLPLANT]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLPLANT
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLPLANTITEM]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLPLANTITEM
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLPURCHASEGROUP]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLPURCHASEGROUP
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLPURCHASEGROUPUSER]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLPURCHASEGROUPUSER
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLPURCHASEORDER]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLPURCHASEORDER
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLPURCHASEORDERLINE]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLPURCHASEORDERLINE
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLUSER]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLUSER
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[TBLVENDOR]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsUserTable
‘
)
=
1
)
drop
table
[
dbo
]
.
[
TBLVENDOR
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLCOMPANY
]
(
[
COMPANY_ID
]
[
nvarchar
]
(
4
)
NOT
NULL
,
[
COMPANY_NAME
]
[
nvarchar
]
(
70
)
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLITEM
]
(
[
ITEM_ID
]
[
nvarchar
]
(
18
)
NOT
NULL
,
[
ITEM_DESCRIPTION
]
[
nvarchar
]
(
50
)
NULL
,
[
UNIT
]
[
nvarchar
]
(
10
)
NULL
,
[
PRICE
]
[
decimal
]
(
18
,
6
)
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLPLANT
]
(
[
PLANT_ID
]
[
nvarchar
]
(
4
)
NOT
NULL
,
[
COMPANY_ID
]
[
nvarchar
]
(
4
)
NOT
NULL
,
[
PLANT_DESCRIPTION
]
[
nvarchar
]
(
70
)
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLPLANTITEM
]
(
[
PLANT_ID
]
[
nvarchar
]
(
4
)
NOT
NULL
,
[
ITEM_ID
]
[
nvarchar
]
(
18
)
NOT
NULL
,
[
UNIT
]
[
nvarchar
]
(
10
)
NULL
,
[
ITEM_CATEGORY
]
[
nvarchar
]
(
3
)
NULL
,
[
PURCHASE_CATEGORY
]
[
nvarchar
]
(
5
)
NULL
,
[
STOCK_OPTION
]
[
int
]
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLPURCHASEGROUP
]
(
[
PURCHASE_GROUP_ID
]
[
nvarchar
]
(
3
)
NOT
NULL
,
[
DESCRIPTION
]
[
nvarchar
]
(
50
)
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLPURCHASEGROUPUSER
]
(
[
USER_ID
]
[
nvarchar
]
(
18
)
NOT
NULL
,
[
PURCHASE_GROUP_ID
]
[
nvarchar
]
(
3
)
NOT
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLPURCHASEORDER
]
(
[
PO_NO
]
[
nvarchar
]
(
18
)
NOT
NULL
,
[
COMPANY_ID
]
[
nvarchar
]
(
4
)
NOT
NULL
,
[
VENDOR_ID
]
[
nvarchar
]
(
12
)
NOT
NULL
,
[
PURCHASE_GROUP_ID
]
[
nvarchar
]
(
3
)
NOT
NULL
,
[
CREATE_DATE
]
[
datetime
]
NOT
NULL
,
[
CURRENCY
]
[
nvarchar
]
(
5
)
NOT
NULL
,
[
TOTAL_AMOUNT
]
[
decimal
]
(
18
,
0
)
NULL
,
[
PAYMENT_TERMS
]
[
nvarchar
]
(
30
)
NULL
,
[
APPROVE_STATE
]
[
nvarchar
]
(
3
)
NULL
,
[
APPROVED_BY
]
[
nvarchar
]
(
16
)
NULL
,
[
APPROVED_DATE
]
[
int
]
NULL
,
[
APPROVED_TIME
]
[
int
]
NULL
,
[
CURRENT_PO_LINE
]
[
nvarchar
]
(
5
)
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLPURCHASEORDERLINE
]
(
[
PO_NO
]
[
nvarchar
]
(
18
)
NOT
NULL
,
[
PO_LINE
]
[
nvarchar
]
(
5
)
NOT
NULL
,
[
PLANT_ID
]
[
nvarchar
]
(
4
)
NOT
NULL
,
[
ITEM_ID
]
[
nvarchar
]
(
18
)
NOT
NULL
,
[
UNIT
]
[
nvarchar
]
(
10
)
NULL
,
[
PRICE
]
[
decimal
]
(
18
,
6
)
NULL
,
[
QUANTITY
]
[
decimal
]
(
18
,
6
)
NULL
,
[
REQUEST_DATE
]
[
int
]
NULL
,
[
LINE_STATE
]
[
int
]
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLUSER
]
(
[
USER_ID
]
[
nvarchar
]
(
18
)
NOT
NULL
,
[
USER_NAME
]
[
nvarchar
]
(
40
)
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
TBLVENDOR
]
(
[
VENDOR_ID
]
[
nvarchar
]
(
12
)
NOT
NULL
,
[
VENDOR_NAME
]
[
nvarchar
]
(
50
)
NULL
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLCOMPANY
]
WITH
NOCHECK
ADD
CONSTRAINT
[
PK_TBLCOMPANY
]
PRIMARY
KEY
CLUSTERED
(
[
COMPANY_ID
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLITEM
]
WITH
NOCHECK
ADD
CONSTRAINT
[
PK_TBLITEM
]
PRIMARY
KEY
CLUSTERED
(
[
ITEM_ID
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPLANT
]
WITH
NOCHECK
ADD
CONSTRAINT
[
PK_TBLPLANT
]
PRIMARY
KEY
CLUSTERED
(
[
PLANT_ID
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEGROUP
]
WITH
NOCHECK
ADD
CONSTRAINT
[
PK_TBLPURCHASEGROUP
]
PRIMARY
KEY
CLUSTERED
(
[
PURCHASE_GROUP_ID
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDER
]
WITH
NOCHECK
ADD
CONSTRAINT
[
PK_TBLPURCHASEORDER
]
PRIMARY
KEY
CLUSTERED
(
[
PO_NO
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDERLINE
]
WITH
NOCHECK
ADD
CONSTRAINT
[
PK_TBLPURCHASEORDERLINE
]
PRIMARY
KEY
CLUSTERED
(
[
PO_NO
]
,
[
PO_LINE
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLUSER
]
WITH
NOCHECK
ADD
CONSTRAINT
[
PK_TBLUSER
]
PRIMARY
KEY
CLUSTERED
(
[
USER_ID
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLVENDOR
]
WITH
NOCHECK
ADD
CONSTRAINT
[
PK_TBLVENDOR
]
PRIMARY
KEY
CLUSTERED
(
[
VENDOR_ID
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPLANTITEM
]
ADD
CONSTRAINT
[
Entity1PK
]
PRIMARY
KEY
NONCLUSTERED
(
[
PLANT_ID
]
,
[
ITEM_ID
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEGROUPUSER
]
ADD
CONSTRAINT
[
PKTBLPURCHASEGROUPUSER
]
PRIMARY
KEY
NONCLUSTERED
(
[
USER_ID
]
,
[
PURCHASE_GROUP_ID
]
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPLANT
]
ADD
CONSTRAINT
[
RefTBLCOMPANY9
]
FOREIGN
KEY
(
[
COMPANY_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLCOMPANY
]
(
[
COMPANY_ID
]
)
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPLANTITEM
]
ADD
CONSTRAINT
[
RefTBLITEM11
]
FOREIGN
KEY
(
[
ITEM_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLITEM
]
(
[
ITEM_ID
]
),
CONSTRAINT
[
RefTBLPLANT10
]
FOREIGN
KEY
(
[
PLANT_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLPLANT
]
(
[
PLANT_ID
]
)
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEGROUPUSER
]
ADD
CONSTRAINT
[
RefTBLPURCHASEGROUP7
]
FOREIGN
KEY
(
[
PURCHASE_GROUP_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLPURCHASEGROUP
]
(
[
PURCHASE_GROUP_ID
]
),
CONSTRAINT
[
RefTBLUSER6
]
FOREIGN
KEY
(
[
USER_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLUSER
]
(
[
USER_ID
]
)
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDER
]
ADD
CONSTRAINT
[
RefTBLCOMPANY2
]
FOREIGN
KEY
(
[
COMPANY_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLCOMPANY
]
(
[
COMPANY_ID
]
),
CONSTRAINT
[
RefTBLPURCHASEGROUP8
]
FOREIGN
KEY
(
[
PURCHASE_GROUP_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLPURCHASEGROUP
]
(
[
PURCHASE_GROUP_ID
]
),
CONSTRAINT
[
RefTBLVENDOR3
]
FOREIGN
KEY
(
[
VENDOR_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLVENDOR
]
(
[
VENDOR_ID
]
)
GO
ALTER
TABLE
[
dbo
]
.
[
TBLPURCHASEORDERLINE
]
ADD
CONSTRAINT
[
RefTBLITEM4
]
FOREIGN
KEY
(
[
ITEM_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLITEM
]
(
[
ITEM_ID
]
),
CONSTRAINT
[
RefTBLPLANT5
]
FOREIGN
KEY
(
[
PLANT_ID
]
)
REFERENCES
[
dbo
]
.
[
TBLPLANT
]
(
[
PLANT_ID
]
),
CONSTRAINT
[
RefTBLPURCHASEORDER1
]
FOREIGN
KEY
(
[
PO_NO
]
)
REFERENCES
[
dbo
]
.
[
TBLPURCHASEORDER
]
(
[
PO_NO
]
)
GO