asked on

T-SQL: Joining Tables

Hi:

Below are three blocks of T-SQL code, each followed by an image of the records retrieved from the specific block above it.

I'm trying to develop a manufacturing "Work in Process" report showing data from the PNMAST table where the data in the BM010115.CPN_I field is in the BM010115.PPN_I field and BM010115.SUBCAT_I = 1.

Please help me modify the syntax of the first T-SQL code block - after its UNION ALL clause - to accommodate this.

The main thing is that I don't want 506 as the FormulaId of the PNMAST table.

Finally, please keep in mind that the images that I have attached are simply the first few records of the tables and not all of the hundreds or even thousands of records in those tables/.

Thank you!

Software Engineer

select NULL as [LOTSEQNBR], FG.BatchNo as [FGBATCH], FG.FormulaId as [FG], FG.[Labor Cost] as [FGLABOR], FG.[OverHead Cost] as [FGOH], FG.[Material Cost] as [FGMAT], 
FG.[Labor Cost] + FG.[OverHead Cost] + FG.[Material Cost] as [FGCOST],
NULL as [FORMULA],
NULL as [FORMULAMAT], NULL AS [FORUMLALABOR], NULL AS [FORMULAOH],
NULL AS [Component], NULL as [LOT], NULL as [LOTQTY], NULL as [LOTUNITCOST]
from BM_View_Dashboard_ProductionCostAnalysis FG
INNER JOIN BM010115 BOM ON FG.FormulaId = BOM.PPN_I
where FG.FormulaId = '506' and FG.BatchNo = '50418G12D' and BOM.SUBCAT_I = 1
UNION ALL
select DISTINCT NULL as [LOTSEQNBR], NULL as [FGBATCH], NULL as [FG], NULL as [FGLABOR], NULL as [FGOH], NULL as [FGMAT], NULL as [FGCOST], BOM.PPN_I as [FORMULA],
PROD.BatchMatCostActual as [FORMULAMAT], PROD.BatchLabortCostActual AS [FORMULALABOR], PROD.BatchOverHeadCostActual AS [FORMULAOH],
--CASE WHEN PROD.BatchMatCostActual = 0 and PROD.BatchLabortCostActual = 0 and 
--PROD.BatchOverHeadCostActual = 0
--THEN 
INV.ITEMNMBR as [Component], 
NULL as [LOT], NULL as [LOTQTY], NULL as [LOTUNITCOST]
from PNMAST PROD
INNER JOIN BM010115 BOM ON PROD.FormulaId = BOM.PPN_I
INNER JOIN BMMTrxHdr BMHDR ON PROD.BatchNo = BMHDR.BMDocNo
INNER JOIN BMMTrxDtl BMDTL ON BMHDR.Dex_Row_Id = BMDTL.RefDex_Row_Id
INNER JOIN IV30300 INV ON BMDTL.LNSeqNo = INV.LNSEQNBR 
where --PROD.FormulaId IN ('506N')
BMHDR.IVDocNmbr IN ('081007', '081008', '081009')
and INV.LNSEQNBR = 16384 and INV.ITEMNMBR IN ('506N', '1880', '1355') 
and INV.DOCTYPE = 1 --and INV.DOCNUMBR IN ('081007', '081008', '081009')

Open in new window

select * from BM010115 where PPN_I = '506'

Open in new window

select * from PNMAST where FormulaId = '506N'

Open in new window

Scott Pletcher

You need to provide data as CREATE TABLE with INSERT statements. A splat of data on the screen is, frankly, useless to us, since we can't write, nor test, SQL against it.

Software Engineer

ASKER

Hi Scott:

Please provide me the syntax, on how to conduct this.

Thank you!

John

Scott Pletcher

CREATE TABLE dbo.BM010115 (
LOTSEQNBR int NULL,
FGBATCH varchar(30) NULL, ...
INSERT INTO dbo.BM010115 VALUES
(NULL, '50418G12D', ...).
(NULL, NULL, ..., 506, ...),
...

Software Engineer

ASKER

Well, that table is already created.

Scott Pletcher

On your system, not on ours.

Good luck with your q, I will allow others to follow up from this point.

Software Engineer

ASKER

Here is the BM010115 CREATE TABLE script:

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BM010115](
	[PPN_I] [char](31) NOT NULL,
	[CPN_I] [char](31) NOT NULL,
	[BOMCAT_I] [smallint] NOT NULL,
	[BOMNAME_I] [char](15) NOT NULL,
	[BOMTYPE_I] [smallint] NOT NULL,
	[BOMSEQ_I] [int] NOT NULL,
	[POSITION_NUMBER] [smallint] NOT NULL,
	[SUBCAT_I] [smallint] NOT NULL,
	[SUBNAME_I] [char](15) NOT NULL,
	[SUB_REV_LEVEL_SEQ_I] [int] NOT NULL,
	[QUANTITY_I] [numeric](19, 5) NOT NULL,
	[OPTPERCENT_I] [int] NOT NULL,
	[SCRAPPERCENT_I] [int] NOT NULL,
	[FLOORSTOCK_I] [tinyint] NOT NULL,
	[EFFECTIVEINDATE_I] [datetime] NOT NULL,
	[EFFECTIVEOUTDATE_I] [datetime] NOT NULL,
	[ALTERNATE_I] [tinyint] NOT NULL,
	[ALTERNATEPARTFOR_I] [char](31) NOT NULL,
	[ALT_FOR_BOM_SEQ_I] [int] NOT NULL,
	[LEADTIMEOFFSET_I] [numeric](19, 5) NOT NULL,
	[LEADTIMEOFFSETINC_I] [smallint] NOT NULL,
	[BOMUSERDEF1_I] [char](31) NOT NULL,
	[BOMUSERDEF2_I] [char](31) NOT NULL,
	[BOMSINGLELOT_I] [tinyint] NOT NULL,
	[BOMENGAPPROVAL_I] [tinyint] NOT NULL,
	[WCID_I] [char](11) NOT NULL,
	[LOCNCODE] [char](11) NOT NULL,
	[BACKFLUSHITEM_I] [tinyint] NOT NULL,
	[CHANGEDATE_I] [datetime] NOT NULL,
	[USERID] [char](15) NOT NULL,
	[OPTIONED_ITEM_I] [tinyint] NOT NULL,
	[ACTUAL_CONSUMED_CHECK_I] [tinyint] NOT NULL,
	[FIXED_QTY_I] [numeric](19, 5) NOT NULL,
	[UOFM] [char](9) NOT NULL,
	[U_Of_M_2] [char](9) NOT NULL,
	[QTYBSUOM] [numeric](19, 5) NOT NULL,
	[OFFSET_FROM_I] [smallint] NOT NULL,
	[MFGNOTEINDEX_I] [numeric](19, 5) NOT NULL,
	[MFGNOTEINDEX2_I] [numeric](19, 5) NOT NULL,
	[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKBM010115] PRIMARY KEY CLUSTERED 
(
	[PPN_I] ASC,
	[BOMCAT_I] ASC,
	[BOMNAME_I] ASC,
	[CPN_I] ASC,
	[BOMSEQ_I] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BM010115]  WITH CHECK ADD CHECK  ((datepart(hour,[CHANGEDATE_I]) = 0 and datepart(minute,[CHANGEDATE_I]) = 0 and datepart(second,[CHANGEDATE_I]) = 0 and datepart(millisecond,[CHANGEDATE_I]) = 0))
GO

ALTER TABLE [dbo].[BM010115]  WITH CHECK ADD CHECK  ((datepart(hour,[EFFECTIVEINDATE_I]) = 0 and datepart(minute,[EFFECTIVEINDATE_I]) = 0 and datepart(second,[EFFECTIVEINDATE_I]) = 0 and datepart(millisecond,[EFFECTIVEINDATE_I]) = 0))
GO

ALTER TABLE [dbo].[BM010115]  WITH CHECK ADD CHECK  ((datepart(hour,[EFFECTIVEOUTDATE_I]) = 0 and datepart(minute,[EFFECTIVEOUTDATE_I]) = 0 and datepart(second,[EFFECTIVEOUTDATE_I]) = 0 and datepart(millisecond,[EFFECTIVEOUTDATE_I]) = 0))
GO

Open in new window

Software Engineer

ASKER

Here is the CREATE TABLE script for PNMAST:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PNMAST](
	[BatchNo] [nvarchar](15) NOT NULL,
	[BatchType] [char](1) NOT NULL,
	[FormulaId] [nvarchar](20) NULL,
	[ItemKey] [nvarchar](80) NULL,
	[LocationKey] [nvarchar](15) NULL,
	[Description] [nvarchar](80) NULL,
	[Status] [nvarchar](1) NOT NULL,
	[DrawFromLocation] [nvarchar](15) NULL,
	[PostToLocation] [nvarchar](15) NULL,
	[BatchTicketDate] [datetime] NULL,
	[Notes] [nvarchar](3500) NULL,
	[CustKey] [nvarchar](25) NULL,
	[EntryDate] [datetime] NULL,
	[SchStartDate] [datetime] NULL,
	[SchCompletionDate] [datetime] NULL,
	[ActStartDate] [datetime] NULL,
	[ActCompletionDate] [datetime] NULL,
	[FixedCostLaborID] [nvarchar](20) NULL,
	[FixedCostOverheadID] [nvarchar](20) NULL,
	[FixedCostHours] [float] NULL,
	[FixedCostAmount] [decimal](22, 6) NULL,
	[FixedOverheadAmount] [decimal](22, 6) NULL,
	[VariableCostLaborID] [nvarchar](20) NULL,
	[VariableCostOverheadID] [nvarchar](20) NULL,
	[VariableLaborAmount] [decimal](22, 6) NULL,
	[VariableOverheadAmount] [decimal](22, 6) NULL,
	[SetupCostLabourId] [nvarchar](20) NULL,
	[SetupCostOverheadID] [nvarchar](20) NULL,
	[SetupCostHours] [float] NULL,
	[SetupLaborAmount] [decimal](22, 6) NULL,
	[SetupOverheadAmount] [decimal](22, 6) NULL,
	[SpecGravOverride] [float] NULL,
	[SysDocId] [int] NULL,
	[ProcessCellId] [nvarchar](20) NULL,
	[RunHours] [float] NULL,
	[LastLineSeqNo] [int] NULL,
	[WIPAccount] [nvarchar](80) NULL,
	[WIPBalance] [decimal](22, 6) NULL,
	[BatchMatCostStd] [decimal](22, 6) NULL,
	[BatchMatCostActual] [decimal](22, 6) NULL,
	[BatchLaborCostStd] [decimal](22, 6) NULL,
	[BatchLabortCostActual] [decimal](22, 6) NULL,
	[BatchOverHeadCostStd] [decimal](22, 6) NULL,
	[BatchOverHeadCostActual] [decimal](22, 6) NULL,
	[TotalByPCost] [decimal](22, 6) NULL,
	[TotalWeightUsed] [float] NULL,
	[TotalVolumetUsed] [float] NULL,
	[TotalFGWeightYielded] [float] NULL,
	[TotalFGVolumeYielded] [float] NULL,
	[TotalByWeightYielded] [float] NULL,
	[TotalByVolumeYielded] [float] NULL,
	[BatchVolume] [float] NULL,
	[BatchWeight] [float] NULL,
	[OrderWeight] [float] NULL,
	[OrderVolume] [float] NULL,
	[TotalStdCost] [decimal](22, 6) NULL,
	[TotalActualCost] [decimal](22, 6) NULL,
	[SONumber] [nvarchar](50) NULL,
	[OldLossFactor] [float] NULL,
	[OldLossConstant] [float] NULL,
	[AplDate] [datetime] NULL,
	[FMRevisionNumber] [nvarchar](10) NULL,
	[RecUserid] [nvarchar](15) NULL,
	[Recdate] [datetime] NULL,
	[User1] [nvarchar](225) NULL,
	[User2] [nvarchar](225) NULL,
	[User3] [nvarchar](225) NULL,
	[User4] [nvarchar](50) NULL,
	[User5] [nvarchar](50) NULL,
	[User6] [datetime] NULL,
	[User7] [float] NULL,
	[User8] [float] NULL,
	[User9] [decimal](22, 6) NULL,
	[User10] [decimal](22, 6) NULL,
	[User11] [int] NULL,
	[User12] [int] NULL,
	[TransactionStatus] [nvarchar](15) NULL,
	[CreatedBy] [nvarchar](15) NULL,
	[ConsiderVariableCostBasedOnBatchWtPnmast] [bit] NULL,
	[FixedCostAmountStd] [decimal](22, 6) NULL,
	[FixedOverheadAmountStd] [decimal](22, 6) NULL,
	[VariableLaborAmountStd] [decimal](22, 6) NULL,
	[VariableOverheadAmountStd] [decimal](22, 6) NULL,
	[SetupLaborAmountStd] [decimal](22, 6) NULL,
	[SetupOverheadAmountStd] [decimal](22, 6) NULL,
 CONSTRAINT [PK_PNMAST] PRIMARY KEY CLUSTERED 
(
	[BatchNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_Status]  DEFAULT ('N') FOR [Status]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_FixedCostFactor]  DEFAULT ((0)) FOR [FixedCostHours]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_FixedCostAmount]  DEFAULT ((0)) FOR [FixedCostAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_FixedOverheadAmount]  DEFAULT ((0)) FOR [FixedOverheadAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_VariableLaborAmount]  DEFAULT ((0)) FOR [VariableLaborAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_VariableOverheadAmount]  DEFAULT ((0)) FOR [VariableOverheadAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SetupCostFactor]  DEFAULT ((0)) FOR [SetupCostHours]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SetupLaborAmount]  DEFAULT ((0)) FOR [SetupLaborAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SetupOverheadAmount]  DEFAULT ((0)) FOR [SetupOverheadAmount]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SpecGravOverride]  DEFAULT ((0)) FOR [SpecGravOverride]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_SysDocId]  DEFAULT ((0)) FOR [SysDocId]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_RunHours]  DEFAULT ((0)) FOR [RunHours]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_LastLineSeqNo]  DEFAULT ((0)) FOR [LastLineSeqNo]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_WIPBalance]  DEFAULT ((0)) FOR [WIPBalance]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchMatCostStd]  DEFAULT ((0)) FOR [BatchMatCostStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchMatCostActual]  DEFAULT ((0)) FOR [BatchMatCostActual]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchLaborCostStd]  DEFAULT ((0)) FOR [BatchLaborCostStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchLabortCostActual]  DEFAULT ((0)) FOR [BatchLabortCostActual]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchOverHeadCostStd]  DEFAULT ((0)) FOR [BatchOverHeadCostStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_BatchOverHeadCostActual]  DEFAULT ((0)) FOR [BatchOverHeadCostActual]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalByPCost]  DEFAULT ((0)) FOR [TotalByPCost]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalWeightUsed]  DEFAULT ((0)) FOR [TotalWeightUsed]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalVolumetUsed]  DEFAULT ((0)) FOR [TotalVolumetUsed]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalWeightYielded]  DEFAULT ((0)) FOR [TotalFGWeightYielded]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalVolumeYielded]  DEFAULT ((0)) FOR [TotalFGVolumeYielded]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalByWeightYielded]  DEFAULT ((0)) FOR [TotalByWeightYielded]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalByVolumeYielded]  DEFAULT ((0)) FOR [TotalByVolumeYielded]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalBatchVolume]  DEFAULT ((0)) FOR [BatchVolume]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalBatchVolume1]  DEFAULT ((0)) FOR [BatchWeight]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_OrderWeight]  DEFAULT ((0)) FOR [OrderWeight]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_OrderVolume]  DEFAULT ((0)) FOR [OrderVolume]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalStdCost]  DEFAULT ((0)) FOR [TotalStdCost]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_TotalActualCost]  DEFAULT ((0)) FOR [TotalActualCost]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_OldLossFactor]  DEFAULT ((0)) FOR [OldLossFactor]
GO

ALTER TABLE [dbo].[PNMAST] ADD  CONSTRAINT [DF_PNMAST_OldLossConstant]  DEFAULT ((0)) FOR [OldLossConstant]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [ConsiderVariableCostBasedOnBatchWtPnmast]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [FixedCostAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [FixedOverheadAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [VariableLaborAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [VariableOverheadAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [SetupLaborAmountStd]
GO

ALTER TABLE [dbo].[PNMAST] ADD  DEFAULT ((0)) FOR [SetupOverheadAmountStd]
GO

Open in new window

Software Engineer

ASKER

Here is the INSERT INTO script for BM010115:

INSERT INTO [dbo].[BM010115]
           ([PPN_I]
           ,[CPN_I]
           ,[BOMCAT_I]
           ,[BOMNAME_I]
           ,[BOMTYPE_I]
           ,[BOMSEQ_I]
           ,[POSITION_NUMBER]
           ,[SUBCAT_I]
           ,[SUBNAME_I]
           ,[SUB_REV_LEVEL_SEQ_I]
           ,[QUANTITY_I]
           ,[OPTPERCENT_I]
           ,[SCRAPPERCENT_I]
           ,[FLOORSTOCK_I]
           ,[EFFECTIVEINDATE_I]
           ,[EFFECTIVEOUTDATE_I]
           ,[ALTERNATE_I]
           ,[ALTERNATEPARTFOR_I]
           ,[ALT_FOR_BOM_SEQ_I]
           ,[LEADTIMEOFFSET_I]
           ,[LEADTIMEOFFSETINC_I]
           ,[BOMUSERDEF1_I]
           ,[BOMUSERDEF2_I]
           ,[BOMSINGLELOT_I]
           ,[BOMENGAPPROVAL_I]
           ,[WCID_I]
           ,[LOCNCODE]
           ,[BACKFLUSHITEM_I]
           ,[CHANGEDATE_I]
           ,[USERID]
           ,[OPTIONED_ITEM_I]
           ,[ACTUAL_CONSUMED_CHECK_I]
           ,[FIXED_QTY_I]
           ,[UOFM]
           ,[U_Of_M_2]
           ,[QTYBSUOM]
           ,[OFFSET_FROM_I]
           ,[MFGNOTEINDEX_I]
           ,[MFGNOTEINDEX2_I])
     VALUES
           (<PPN_I, char(31),>
           ,<CPN_I, char(31),>
           ,<BOMCAT_I, smallint,>
           ,<BOMNAME_I, char(15),>
           ,<BOMTYPE_I, smallint,>
           ,<BOMSEQ_I, int,>
           ,<POSITION_NUMBER, smallint,>
           ,<SUBCAT_I, smallint,>
           ,<SUBNAME_I, char(15),>
           ,<SUB_REV_LEVEL_SEQ_I, int,>
           ,<QUANTITY_I, numeric(19,5),>
           ,<OPTPERCENT_I, int,>
           ,<SCRAPPERCENT_I, int,>
           ,<FLOORSTOCK_I, tinyint,>
           ,<EFFECTIVEINDATE_I, datetime,>
           ,<EFFECTIVEOUTDATE_I, datetime,>
           ,<ALTERNATE_I, tinyint,>
           ,<ALTERNATEPARTFOR_I, char(31),>
           ,<ALT_FOR_BOM_SEQ_I, int,>
           ,<LEADTIMEOFFSET_I, numeric(19,5),>
           ,<LEADTIMEOFFSETINC_I, smallint,>
           ,<BOMUSERDEF1_I, char(31),>
           ,<BOMUSERDEF2_I, char(31),>
           ,<BOMSINGLELOT_I, tinyint,>
           ,<BOMENGAPPROVAL_I, tinyint,>
           ,<WCID_I, char(11),>
           ,<LOCNCODE, char(11),>
           ,<BACKFLUSHITEM_I, tinyint,>
           ,<CHANGEDATE_I, datetime,>
           ,<USERID, char(15),>
           ,<OPTIONED_ITEM_I, tinyint,>
           ,<ACTUAL_CONSUMED_CHECK_I, tinyint,>
           ,<FIXED_QTY_I, numeric(19,5),>
           ,<UOFM, char(9),>
           ,<U_Of_M_2, char(9),>
           ,<QTYBSUOM, numeric(19,5),>
           ,<OFFSET_FROM_I, smallint,>
           ,<MFGNOTEINDEX_I, numeric(19,5),>
           ,<MFGNOTEINDEX2_I, numeric(19,5),>)
GO

Open in new window

Software Engineer

ASKER

Here is the PNMAST INSERT INTO script:

INSERT INTO [dbo].[PNMAST]
           ([BatchNo]
           ,[BatchType]
           ,[FormulaId]
           ,[ItemKey]
           ,[LocationKey]
           ,[Description]
           ,[Status]
           ,[DrawFromLocation]
           ,[PostToLocation]
           ,[BatchTicketDate]
           ,[Notes]
           ,[CustKey]
           ,[EntryDate]
           ,[SchStartDate]
           ,[SchCompletionDate]
           ,[ActStartDate]
           ,[ActCompletionDate]
           ,[FixedCostLaborID]
           ,[FixedCostOverheadID]
           ,[FixedCostHours]
           ,[FixedCostAmount]
           ,[FixedOverheadAmount]
           ,[VariableCostLaborID]
           ,[VariableCostOverheadID]
           ,[VariableLaborAmount]
           ,[VariableOverheadAmount]
           ,[SetupCostLabourId]
           ,[SetupCostOverheadID]
           ,[SetupCostHours]
           ,[SetupLaborAmount]
           ,[SetupOverheadAmount]
           ,[SpecGravOverride]
           ,[SysDocId]
           ,[ProcessCellId]
           ,[RunHours]
           ,[LastLineSeqNo]
           ,[WIPAccount]
           ,[WIPBalance]
           ,[BatchMatCostStd]
           ,[BatchMatCostActual]
           ,[BatchLaborCostStd]
           ,[BatchLabortCostActual]
           ,[BatchOverHeadCostStd]
           ,[BatchOverHeadCostActual]
           ,[TotalByPCost]
           ,[TotalWeightUsed]
           ,[TotalVolumetUsed]
           ,[TotalFGWeightYielded]
           ,[TotalFGVolumeYielded]
           ,[TotalByWeightYielded]
           ,[TotalByVolumeYielded]
           ,[BatchVolume]
           ,[BatchWeight]
           ,[OrderWeight]
           ,[OrderVolume]
           ,[TotalStdCost]
           ,[TotalActualCost]
           ,[SONumber]
           ,[OldLossFactor]
           ,[OldLossConstant]
           ,[AplDate]
           ,[FMRevisionNumber]
           ,[RecUserid]
           ,[Recdate]
           ,[User1]
           ,[User2]
           ,[User3]
           ,[User4]
           ,[User5]
           ,[User6]
           ,[User7]
           ,[User8]
           ,[User9]
           ,[User10]
           ,[User11]
           ,[User12]
           ,[TransactionStatus]
           ,[CreatedBy]
           ,[ConsiderVariableCostBasedOnBatchWtPnmast]
           ,[FixedCostAmountStd]
           ,[FixedOverheadAmountStd]
           ,[VariableLaborAmountStd]
           ,[VariableOverheadAmountStd]
           ,[SetupLaborAmountStd]
           ,[SetupOverheadAmountStd])
     VALUES
           (<BatchNo, nvarchar(15),>
           ,<BatchType, char(1),>
           ,<FormulaId, nvarchar(20),>
           ,<ItemKey, nvarchar(80),>
           ,<LocationKey, nvarchar(15),>
           ,<Description, nvarchar(80),>
           ,<Status, nvarchar(1),>
           ,<DrawFromLocation, nvarchar(15),>
           ,<PostToLocation, nvarchar(15),>
           ,<BatchTicketDate, datetime,>
           ,<Notes, nvarchar(3500),>
           ,<CustKey, nvarchar(25),>
           ,<EntryDate, datetime,>
           ,<SchStartDate, datetime,>
           ,<SchCompletionDate, datetime,>
           ,<ActStartDate, datetime,>
           ,<ActCompletionDate, datetime,>
           ,<FixedCostLaborID, nvarchar(20),>
           ,<FixedCostOverheadID, nvarchar(20),>
           ,<FixedCostHours, float,>
           ,<FixedCostAmount, decimal(22,6),>
           ,<FixedOverheadAmount, decimal(22,6),>
           ,<VariableCostLaborID, nvarchar(20),>
           ,<VariableCostOverheadID, nvarchar(20),>
           ,<VariableLaborAmount, decimal(22,6),>
           ,<VariableOverheadAmount, decimal(22,6),>
           ,<SetupCostLabourId, nvarchar(20),>
           ,<SetupCostOverheadID, nvarchar(20),>
           ,<SetupCostHours, float,>
           ,<SetupLaborAmount, decimal(22,6),>
           ,<SetupOverheadAmount, decimal(22,6),>
           ,<SpecGravOverride, float,>
           ,<SysDocId, int,>
           ,<ProcessCellId, nvarchar(20),>
           ,<RunHours, float,>
           ,<LastLineSeqNo, int,>
           ,<WIPAccount, nvarchar(80),>
           ,<WIPBalance, decimal(22,6),>
           ,<BatchMatCostStd, decimal(22,6),>
           ,<BatchMatCostActual, decimal(22,6),>
           ,<BatchLaborCostStd, decimal(22,6),>
           ,<BatchLabortCostActual, decimal(22,6),>
           ,<BatchOverHeadCostStd, decimal(22,6),>
           ,<BatchOverHeadCostActual, decimal(22,6),>
           ,<TotalByPCost, decimal(22,6),>
           ,<TotalWeightUsed, float,>
           ,<TotalVolumetUsed, float,>
           ,<TotalFGWeightYielded, float,>
           ,<TotalFGVolumeYielded, float,>
           ,<TotalByWeightYielded, float,>
           ,<TotalByVolumeYielded, float,>
           ,<BatchVolume, float,>
           ,<BatchWeight, float,>
           ,<OrderWeight, float,>
           ,<OrderVolume, float,>
           ,<TotalStdCost, decimal(22,6),>
           ,<TotalActualCost, decimal(22,6),>
           ,<SONumber, nvarchar(50),>
           ,<OldLossFactor, float,>
           ,<OldLossConstant, float,>
           ,<AplDate, datetime,>
           ,<FMRevisionNumber, nvarchar(10),>
           ,<RecUserid, nvarchar(15),>
           ,<Recdate, datetime,>
           ,<User1, nvarchar(225),>
           ,<User2, nvarchar(225),>
           ,<User3, nvarchar(225),>
           ,<User4, nvarchar(50),>
           ,<User5, nvarchar(50),>
           ,<User6, datetime,>
           ,<User7, float,>
           ,<User8, float,>
           ,<User9, decimal(22,6),>
           ,<User10, decimal(22,6),>
           ,<User11, int,>
           ,<User12, int,>
           ,<TransactionStatus, nvarchar(15),>
           ,<CreatedBy, nvarchar(15),>
           ,<ConsiderVariableCostBasedOnBatchWtPnmast, bit,>
           ,<FixedCostAmountStd, decimal(22,6),>
           ,<FixedOverheadAmountStd, decimal(22,6),>
           ,<VariableLaborAmountStd, decimal(22,6),>
           ,<VariableOverheadAmountStd, decimal(22,6),>
           ,<SetupLaborAmountStd, decimal(22,6),>
           ,<SetupOverheadAmountStd, decimal(22,6),>)
GO

Open in new window

ASKER CERTIFIED SOLUTION

Software Engineer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial