/****** Object:  Table [dbo].[publ_PurchaseOrderTypes]    Script Date: 9/11/2019 7:14:11 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'publ_PurchaseOrderTypes'))
BEGIN
	CREATE TABLE [dbo].[publ_PurchaseOrderTypes](
		[id] [int] IDENTITY(1,1) NOT NULL,
		[shortName] [nvarchar](2) NOT NULL,
		[name] [nvarchar](20) NOT NULL,
	 CONSTRAINT [PK_publ_PurchaseOrderTypes] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

/****** Object:  Table [dbo].[publ_PurchaseOrderStatuses]    Script Date: 9/11/2019 7:14:21 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'publ_PurchaseOrderStatuses'))
BEGIN
	CREATE TABLE [dbo].[publ_PurchaseOrderStatuses](
		[id] [int] IDENTITY(1,1) NOT NULL,
		[name] [nvarchar](50) NOT NULL,
	 CONSTRAINT [PK_publ_PurchaseOrderStatus] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

/****** Object:  Table [dbo].[publ_PurchaseOrderDocumentTypes]    Script Date: 9/11/2019 7:14:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'publ_PurchaseOrderDocumentTypes'))
BEGIN
	CREATE TABLE [dbo].[publ_PurchaseOrderDocumentTypes](
		[id] [int] IDENTITY(1,1) NOT NULL,
		[shortName] [nvarchar](3) NOT NULL,
		[name] [nvarchar](20) NOT NULL,
	 CONSTRAINT [PK_publ_PurchaseDocumentType] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

/****** Object:  Table [dbo].[publ_PurchaseOrderDocumentStatuses]    Script Date: 9/11/2019 7:15:04 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'publ_PurchaseOrderDocumentStatuses'))
BEGIN
	CREATE TABLE [dbo].[publ_PurchaseOrderDocumentStatuses](
		[id] [int] IDENTITY(1,1) NOT NULL,
		[name] [nvarchar](20) NOT NULL,
	 CONSTRAINT [PK_publ_PurchaseOrderDocumentStatuses] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'publ_PurchaseOrderQuoteItemFinal'))
BEGIN
	CREATE TABLE [dbo].[publ_PurchaseOrderQuoteItemFinal](
		[id] [int] IDENTITY(1,1) NOT NULL,
		[purchaseOrderId] [int] NOT NULL,
		[quoteItemFinalId] [int] NOT NULL,
	 CONSTRAINT [PK_publ_PurchaseOrderQuoteItemFinal] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END


/****** Object:  Table [dbo].[publ_PurchaseOrders]    Script Date: 9/11/2019 7:14:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'publ_PurchaseOrders'))
BEGIN
	CREATE TABLE [dbo].[publ_PurchaseOrders](
		[id] [int] IDENTITY(1,1) NOT NULL,
		[quoteId] [int] NOT NULL,
		[supplierId] [int] NULL,
		[code] [nvarchar](200) NOT NULL,
		[date] [date] NOT NULL,
		[typeId] [int] NULL,
		[statusId] [int] NOT NULL,
		[documentStatusId] [int] NULL,
		[documentTypeId] [int] NOT NULL,
		[forexAmount] [decimal](18, 2) NULL,
		[exclAmountZAR] [decimal](18, 2) NULL,
		[inclAmountZAR] [decimal](18, 2) NULL,
		[currencyId] [int] NULL,
	 CONSTRAINT [PK_publ_PurchaseOrder] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[publ_PurchaseOrders]  WITH CHECK ADD  CONSTRAINT [FK_publ_PurchaseOrders_publ_PurchaseOrderDocumentStatuses] FOREIGN KEY([documentStatusId])
	REFERENCES [dbo].[publ_PurchaseOrderDocumentStatuses] ([id])

	ALTER TABLE [dbo].[publ_PurchaseOrders] CHECK CONSTRAINT [FK_publ_PurchaseOrders_publ_PurchaseOrderDocumentStatuses]

	ALTER TABLE [dbo].[publ_PurchaseOrders]  WITH CHECK ADD  CONSTRAINT [FK_publ_PurchaseOrders_publ_PurchaseOrderDocumentTypes] FOREIGN KEY([documentTypeId])
	REFERENCES [dbo].[publ_PurchaseOrderDocumentTypes] ([id])

	ALTER TABLE [dbo].[publ_PurchaseOrders] CHECK CONSTRAINT [FK_publ_PurchaseOrders_publ_PurchaseOrderDocumentTypes]

	ALTER TABLE [dbo].[publ_PurchaseOrders]  WITH CHECK ADD  CONSTRAINT [FK_publ_PurchaseOrders_publ_PurchaseOrderStatuses] FOREIGN KEY([statusId])
	REFERENCES [dbo].[publ_PurchaseOrderStatuses] ([id])

	ALTER TABLE [dbo].[publ_PurchaseOrders] CHECK CONSTRAINT [FK_publ_PurchaseOrders_publ_PurchaseOrderStatuses]

	ALTER TABLE [dbo].[publ_PurchaseOrders]  WITH CHECK ADD  CONSTRAINT [FK_publ_PurchaseOrders_publ_PurchaseOrderTypes] FOREIGN KEY([typeId])
	REFERENCES [dbo].[publ_PurchaseOrderTypes] ([id])

	ALTER TABLE [dbo].[publ_PurchaseOrders] CHECK CONSTRAINT [FK_publ_PurchaseOrders_publ_PurchaseOrderTypes]

END