if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOMMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOMMaster]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JobCard]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JobCard]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductionSchedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProductionSchedule]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductionScheduleDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProductionScheduleDetail]
GO

CREATE TABLE [dbo].[BOMMaster] (
	[BOMID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[BOMCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[WorkOrder] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ProductId] [numeric](18, 0) NOT NULL ,
	[Qty] [numeric](18, 3) NULL ,
	[Remarks] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedBy] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[modifiedby] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[isApproved] [bit] NULL ,
	[ProductCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Criteria] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StartDate] [datetime] NULL ,
	[FinishDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JobCard] (
	[JobCardID] [numeric](18, 0) NOT NULL ,
	[JobCardNo] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[JobCardDate] [datetime] NULL ,
	[Status] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PSCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BOMCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Precedence] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ItemCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Quantity] [numeric](18, 3) NULL ,
	[Category] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CompletionDate] [datetime] NULL ,
	[CommencementDate] [datetime] NULL ,
	[SpecialRequirement] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Remarks] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Sno] [numeric](18, 0) NULL ,
	[MRNNo] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedBy] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ModifiedBy] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[QtyReceived] [numeric](18, 2) NULL ,
	[JobCardStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ProductionSchedule] (
	[PSID] [numeric](18, 0) NOT NULL ,
	[PSCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[BOMCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ScheduleDate] [datetime] NOT NULL ,
	[TargetDateOfCompletion] [datetime] NULL ,
	[Remarks] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedBy] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ModifiedBy] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[isApproved] [bit] NULL ,
	[PSType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StartDate] [datetime] NULL ,
	[FinishDate] [datetime] NULL ,
	[MaterialIssued] [bit] NULL ,
	[Status] [numeric](18, 0) NULL ,
	[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StaggarationID] [numeric](18, 0) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ProductionScheduleDetail] (
	[PSCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ItemCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[QtyRqd] [numeric](18, 3) NULL ,
	[WIPInhouse] [numeric](18, 3) NULL ,
	[InhouseReq] [numeric](18, 3) NULL ,
	[QtyToBeOrdered] [numeric](18, 3) NULL ,
	[StartPlanDate] [datetime] NULL ,
	[StartPlanTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StartFinishDate] [datetime] NULL ,
	[StartFinishTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ActualStartDate] [datetime] NULL ,
	[ActualStartTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ActualFinishDate] [datetime] NULL ,
	[ActualFinishTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[JOB_RQN_NO] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Supplier] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Remarks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Lvl] [numeric](18, 0) NULL ,
	[SNo] [numeric](18, 0) NULL ,
	[TotalTime] [numeric](18, 2) NULL ,
	[SR] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[QtyMO] [numeric](18, 3) NULL ,
	[MaterialIssuedForJobCard] [bit] NULL ,
	[BOMStatus] [numeric](18, 0) NULL ,
	[STGItemID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SuspendedItem] [bit] NULL ,
	[STGSQ] [numeric](18, 0) NULL 
) ON [PRIMARY]
GO