/****** Object:  Table [dbo].[Nonconform_Item_Master]    Script Date: 5/14/2019 8:06:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Nonconform_Item_Master]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Nonconform_Item_Master](
	[item_number] [varchar](15) NOT NULL,
	[item_description] [varchar](40) NULL,
	[item_class] [varchar](1) NULL,
	[item_type] [varchar](1) NULL,
	[item_source] [varchar](1) NULL,
	[item_category] [varchar](1) NULL,
	[item_status] [varchar](1) NULL,
	[item_supplier] [varchar](10) NULL,
	[item_cost] [decimal](11, 2) NULL,
	[item_price] [decimal](11, 2) NULL,
	[item_image1] [image] NULL,
 CONSTRAINT [PK_NC_ITEM_NO] PRIMARY KEY CLUSTERED 
(
	[item_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[Nonconform_nonconformance]    Script Date: 5/14/2019 8:06:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Nonconform_nonconformance]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Nonconform_nonconformance](
	[nc_tag_num] [int] IDENTITY(1000,1) NOT NULL,
	[assigned_qe] [varchar](10) NULL,
	[create_date] [date] NULL,
	[create_time] [time](7) NULL,
	[create_user] [varchar](50) NULL,
	[defect_description] [varchar](256) NULL,
	[defect_qty] [int] NULL,
	[image1] [image] NULL,
	[item_number] [varchar](55) NULL,
	[po_line_no] [varchar](10) NULL,
	[po_no] [varchar](20) NULL,
	[reason] [varchar](10) NULL,
	[status] [varchar](10) NULL,
	[type] [varchar](1) NULL,
	[disposition] [varchar](1) NULL,
 CONSTRAINT [PK_NC_TAG_NUM] PRIMARY KEY CLUSTERED 
(
	[nc_tag_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[Nonconform_Reason]    Script Date: 5/14/2019 8:06:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Nonconform_Reason]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Nonconform_Reason](
	[reason] [varchar](1) NOT NULL,
	[reason_description] [varchar](40) NULL,
	[reason_QE_user] [varchar](10) NULL,
	[reason_status] [varchar](1) NULL,
 CONSTRAINT [PK_NC_REASON] PRIMARY KEY CLUSTERED 
(
	[reason] 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
GO
/****** Object:  Table [dbo].[Nonconform_summary]    Script Date: 5/14/2019 8:06:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Nonconform_summary]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Nonconform_summary](
	[NCS_Month] [varchar](10) NOT NULL,
	[NCS_Reason] [varchar](10) NOT NULL,
	[NCS_Qty] [int] NULL,
	[NCS_Count] [int] NULL,
	[NCS_Date] [date] NULL,
 CONSTRAINT [PK_NCS_Month] PRIMARY KEY CLUSTERED 
(
	[NCS_Month] ASC,
	[NCS_Reason] 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
GO
/****** Object:  View [dbo].[Nonconform_Item_Dashboard]    Script Date: 5/14/2019 8:06:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Nonconform_Item_Dashboard]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[Nonconform_Item_Dashboard] AS SELECT CONVERT(VARCHAR(6), create_date, 112) AS [NC_YYYYMM], reason AS NC_REASON, Count(*) AS NC_COUNT, Sum(defect_qty) AS NC_QTY FROM dbo.Nonconform_nonconformance GROUP BY CONVERT(VARCHAR(6), create_date, 112), reason;
' 
GO
