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
![WIP]()
![BOM]()
![PNMAST]()
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')
select * from BM010115 where PPN_I = '506'
select * from PNMAST where FormulaId = '506N'
* T-SQLMicrosoft SQL ServerProgramming Languages-Other
Last Comment
Software Engineer
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.
ASKER
Hi Scott:
Please provide me the syntax, on how to conduct this.
Thank you!
John
Please provide me the syntax, on how to conduct this.
Thank you!
John
CREATE TABLE dbo.BM010115 (
LOTSEQNBR int NULL,
FGBATCH varchar(30) NULL, ...
INSERT INTO dbo.BM010115 VALUES
(NULL, '50418G12D', ...).
(NULL, NULL, ..., 506, ...),
...
LOTSEQNBR int NULL,
FGBATCH varchar(30) NULL, ...
INSERT INTO dbo.BM010115 VALUES
(NULL, '50418G12D', ...).
(NULL, NULL, ..., 506, ...),
...
ASKER
Well, that table is already created.
On your system, not on ours.
Good luck with your q, I will allow others to follow up from this point.
Good luck with your q, I will allow others to follow up from this point.
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
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
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.