V7 new tables that replaces V6 tables

[dimuthu]

Could you please tell me what are the new V7 tables that replaces BO_BuildMain & BO_BuildLines in V6. Is there any documentation that list new tables which replaces V6 tables.

Thanks
Dimuthu

[Mike.Sheen]

Could you please tell me what are the new V7 tables that replaces BO_BuildMain & BO_BuildLines in V6.

BM_WorkOrder and BM_WorkOrderInputs are the equivalent of BO_BuildMain and BO_BuildLines.

Is there any documentation that list new tables which replaces V6 tables.

The best I can give you right now is relevant parts of the upgrade script which convert the V6 bills to V7 bills and workorders - I hope that is sufficient.

Mike

Code: Select all

-- Convert v6 Bills to v7 Bills
INSERT INTO BM_Main (RecID, BillNo, Description, IsEnabled, LastSavedDateTime, BM_ProductionLine_RecID)
SELECT BO_Main.RecID, BO_Main.InventoryNo, IN_Main.Description, CASE BO_Main.Status WHEN 0 THEN 1 ELSE 0 END, BO_Main.LastSavedDateTime,
(SELECT TOP 1 RecID FROM BM_ProductionLine WHERE BM_ProductionArea_RecID = (SELECT TOP 1 RecID FROM BM_ProductionArea WHERE Name = 'Default'))
FROM BO_Main
JOIN IN_Main ON IN_Main.InventoryID = BO_Main.InventoryID
WHERE BO_Main.BuildLineID = ''

-- Add a stage for each bill
INSERT INTO BM_Stages (RecID, BM_Main_RecID, ItemNo, Name, BM_ProductionLineWorkCentre_RecID, RequiredCapacity)
SELECT NewID(), BM_Main.RecID,
1, 'Stage 1',
(SELECT TOP 1 BM_ProductionLineWorkCentre.RecID FROM BM_ProductionLineWorkCentre WHERE BM_ProductionLineWorkCentre.BM_ProductionLine_RecID = BM_Main.BM_ProductionLine_RecID ORDER BY BM_ProductionLineWorkCentre.ItemNo),
0
FROM BM_Main
LEFT JOIN BM_Stages ON BM_Stages.BM_Main_RecID = BM_Main.RecID
WHERE BM_Stages.RecID IS NULL

-- Add the inputs to the stage
INSERT INTO BM_Outputs(RecID, BM_Stages_RecID, ItemNo, InventoryID, IsRatio, IsPrimary, Quantity,  Note, CostPercentage)
SELECT NewID(),
(SELECT TOP 1 RecID FROM BM_Stages WHERE Name = 'Stage 1' AND BM_Stages.BM_Main_RecID = BM_Main.RecID),
1,
BO_Main.InventoryID, 1, 1, BO_Main.BaseQty, NULL, 100
FROM BO_Main
JOIN BM_Main ON BM_Main.RecID = BO_Main.RecID
JOIN BM_Stages ON BM_Stages.BM_Main_RecID = BM_Main.RecID


-- Now for the Work Orders --

--Work orders attached to bill
INSERT INTO BM_WorkOrder(RecID, WorkOrderNo, Reference, BM_Main_RecID, IN_LogicalID, DateCreated, DateRequired, PlannedStartDate, ActualStartDate, DateCompleted, ProductionQuantity, LastSavedDateTime, BM_WarehouseProductionLine_RecID, Status, Parent_BM_WorkOrder_RecID, Parent_BM_WorkOrderInputs_RecID, WorkOrderType, Reversal_BM_WorkOrder_RecID, Migrated)
SELECT BO_BuildMain.RecID, BO_BuildMain.batchno, BO_BuildMain.Reference, BO_BuildLines.BoMID BM_Main_RecID,
BO_BuildMain.WarehouseID, BO_BuildMain.InitiatedDate, BO_BuildMain.ProductionDate, BO_BuildMain.ProductionDate, BO_BuildMain.ProductionDate,
BO_BuildMain.ProductionDate, (SELECT COALESCE(SUM(StockQty), 0) FROM Bo_BuildBillDetailsHS JOIN Bo_BuildMainHS ON Bo_BuildMainHS.RunNo = Bo_BuildBillDetailsHS.BillRunNo WHERE Bo_BuildMainHS.RecID = BO_BuildMain.RecID), BO_BuildMain.LastSavedDateTime, NULL, CASE BO_BuildMain.Status WHEN 2 THEN 3 ELSE BO_BuildMain.Status END Status,
(SELECT TOP 1 RecID FROM BO_BuildMain SubAssembly WHERE SubAssembly.BatchNo = BO_BuildMain.ExplodeID) Parent_BM_WorkOrder_RecID,
NULL Parent_BM_WorkOrderInputs_RecID, 0 WorkOrderType, NULL Reversal_BM_WorkOrder_RecID, 1
FROM BO_BuildMain
JOIN BO_BuildLines ON BO_BuildLines.BuildID = BO_BuildMain.RecID
WHERE BO_BuildLines.BoMID IN (SELECT RecID FROM BM_Main)

--Work orders NOT attached to bills
INSERT INTO BM_WorkOrder(RecID, WorkOrderNo, Reference, BM_Main_RecID, IN_LogicalID, DateCreated, DateRequired, PlannedStartDate, ActualStartDate, DateCompleted, ProductionQuantity, LastSavedDateTime, BM_WarehouseProductionLine_RecID, Status, Parent_BM_WorkOrder_RecID, Parent_BM_WorkOrderInputs_RecID, WorkOrderType, Reversal_BM_WorkOrder_RecID, Migrated)
SELECT BO_BuildMain.RecID, BO_BuildMain.batchno, BO_BuildMain.Reference, NULL BM_Main_RecID,
BO_BuildMain.WarehouseID, BO_BuildMain.InitiatedDate, BO_BuildMain.ProductionDate, BO_BuildMain.ProductionDate, BO_BuildMain.ProductionDate,
BO_BuildMain.ProductionDate, (SELECT COALESCE(SUM(StockQty), 0) FROM Bo_BuildBillDetailsHS JOIN Bo_BuildMainHS ON Bo_BuildMainHS.RunNo = Bo_BuildBillDetailsHS.BillRunNo WHERE Bo_BuildMainHS.RecID = BO_BuildMain.RecID), BO_BuildMain.LastSavedDateTime, NULL, CASE BO_BuildMain.Status WHEN 2 THEN 3 ELSE BO_BuildMain.Status END Status,
(SELECT TOP 1 RecID FROM BO_BuildMain SubAssembly WHERE SubAssembly.BatchNo = BO_BuildMain.ExplodeID) Parent_BM_WorkOrder_RecID,
NULL Parent_BM_WorkOrderInputs_RecID, 0 WorkOrderType, NULL Reversal_BM_WorkOrder_RecID, 1
FROM BO_BuildMain
JOIN BO_BuildLines ON BO_BuildLines.BuildID = BO_BuildMain.RecID
WHERE BO_BuildLines.BoMID NOT IN (SELECT RecID FROM BM_Main)

--Work order stages for work orders attached to a receipe
INSERT INTO BM_WorkOrderStages(RecID, BM_WorkOrder_RecID, ItemNo, Name, BM_WarehouseProductionLineWorkCentre_RecID, RequiredCapacity, Status, StartDate)
SELECT NewID(), BM_WorkOrder.RecID, BM_Stages.ItemNo, BM_Stages.Name, NULL, BM_Stages.RequiredCapacity, CASE BM_WorkOrder.Status WHEN 3 THEN 2 WHEN 2 THEN 1 ELSE 0 END, BM_WorkOrder.ActualStartDate
FROM BM_WorkOrder
JOIN BM_Main ON BM_Main.RecID = BM_WorkOrder.BM_Main_RecID
JOIN BM_Stages ON BM_Stages.BM_Main_RecID = BM_Main.RecID
WHERE Migrated = 1

--Work order stages for work orders NOT attached to a receipe
INSERT INTO BM_WorkOrderStages(RecID, BM_WorkOrder_RecID, ItemNo, Name, BM_WarehouseProductionLineWorkCentre_RecID, RequiredCapacity, Status, StartDate)
SELECT NewID(), BM_WorkOrder.RecID, 1, 'Stage 1', NULL, 0, CASE BM_WorkOrder.Status WHEN 3 THEN 2 WHEN 2 THEN 1 ELSE 0 END, BM_WorkOrder.ActualStartDate
FROM BM_WorkOrder
WHERE Migrated = 1 AND BM_Main_RecID IS NULL

-- Add the outputs to the workorders
INSERT INTO BM_WorkOrderOutputs(RecID, BM_WorkOrder_RecID, ItemNo, InventoryID, IsRatio, IsPrimary, Quantity, QuantityOutput, QuantityWrittenoff, Note, CostPercentage, LastSavedDateTime)
SELECT BO_BuildLines.RecID, BM_WorkOrder.RecID, 1, BO_BuildLines.InventoryID, 1, 1, BO_BuildLines.BaseQty, BO_BuildLines.QtyAssembled, 0, NULL, 100, BO_BuildLines.LastSavedDateTime
FROM BM_WorkOrder
JOIN BO_BuildMain ON BO_BuildMain.RecID = BM_WorkOrder.RecID
JOIN BO_BuildLines ON BO_BuildLines.BuildID = BO_BuildMain.RecID
WHERE Migrated = 1

-- Inputs
INSERT INTO BM_WorkOrderInputs(RecID, BM_WorkOrderStages_RecID, ItemNo, InventoryID, IsRatio, Quantity, QuantityOnBackOrder, Note, LastSavedDateTime, SubAssembly, SubAssemblyExplodePolicy, BO_BuildMain_RecID, BO_Lines_RecID)
SELECT NEWID(), (SELECT TOP 1 RecID FROM BM_WorkOrderStages WHERE BM_WorkOrder_RecID = BM_WorkOrder.RecID) BM_WorkOrderStages_RecID,
Bo_Lines.LineNum, In_Main.InventoryID, CASE Bo_Lines.QtyType WHEN 0 THEN 1 ELSE 0 END IsRatio, Bo_Lines.Qty Quantity, 0 QuantityOnBackOrder, 
BO_Lines.Notes Note, BO_Lines.LastSavedDateTime, NULL, 0, BO_BuildMain.RecID, BO_Lines.RecID
FROM Bo_Lines
JOIN In_Main ON Bo_Lines.InventoryID = In_Main.InventoryID
JOIN Bo_Main ON Bo_Main.RecID = Bo_Lines.BomID
JOIN Bo_BuildLines ON Bo_BuildLines.RecID = Bo_Main.BuildLineID
JOIN BO_BuildMain ON Bo_BuildLines.BuildID = BO_BuildMain.RecID
JOIN BM_WorkOrder ON BM_WorkOrder.RecID = Bo_BuildLines.BuildID
WHERE BM_WorkOrder.Migrated = 1
AND BO_Main.InventoryID = BO_BuildLines.InventoryID
ORDER BY BM_WorkOrder.WorkOrderNo, BO_Lines.LineNum

-- Input Line Details
INSERT INTO BM_WorkOrderInputDetails(RecID, Parent_RecID, IN_SOH_LinkID, Cost, SpecialPrice, Quantity, SerialNo, ExpiryDate, BinLocation, LastSavedDateTime)
SELECT BO_BuildUsageDetails.RecID, BM_WorkOrderInputs.RecID, BO_BuildUsageDetails.SOHID, BO_BuildUsageDetails.Cost, 0 SpecicalPrice, BO_BuildUsageDetails.QtyTaken,
BO_BuildUsageDetails.SerialNo, BO_BuildUsageDetails.ExpiryDate, BO_BuildUsageDetails.BinLocation, BO_BuildMainHS.LastSavedDateTime
FROM BO_BuildUsageDetails
JOIN BO_BuildMainHS ON BO_BUILDUsageDetails.BillRunNo = BO_BuildMainHS.RunNo
JOIN IN_Main ON BO_BUILDUsageDetails.InvID = IN_Main.InventoryID
JOIN BM_WorkOrder ON BM_WorkOrder.RecID = Bo_BuildMainHS.RecID
JOIN BM_WorkOrderStages ON BM_WorkOrderStages.BM_WorkOrder_RecID = BM_WorkOrder.RecID
JOIN BM_WorkOrderInputs ON BM_WorkOrderInputs.BM_WorkOrderStages_RecID  = BM_WorkOrderStages.RecID
LEFT JOIN IN_SOH ON IN_SOH.LinkID = BO_BuildUsageDetails.SOHID
WHERE BO_BUILDUsageDetails.IsComponent = 1
AND BM_WorkOrderInputs.InventoryID = BO_BuildUsageDetails.InvID
AND BM_WorkOrderInputs.BO_Lines_RecID = BO_BuildUsageDetails.LineID
AND BM_WorkOrderInputs.BO_BuildMain_RecID = BO_BuildMainHS.RecID
AND BM_WorkOrder.Migrated = 1
AND (BO_BuildUsageDetails.SOHID IS NULL OR BO_BuildUsageDetails.SOHID IN (SELECT LinkID FROM IN_SOH))

-- Output Line Details
INSERT INTO BM_WorkOrderOutputDetails(RecID, Parent_RecID, IN_SOH_LinkID, Cost, SpecialPrice, Quantity, SerialNo, ExpiryDate, BinLocation, LastSavedDateTime)
SELECT NewID(), BM_WorkOrderOutputs.RecID, BO_BuildUsageDetails.SOHID, BO_BuildUsageDetails.Cost, 0 SpecicalPrice, BO_BuildUsageDetails.QtyTaken,
BO_BuildUsageDetails.SerialNo, BO_BuildUsageDetails.ExpiryDate, BO_BuildUsageDetails.BinLocation, BO_BuildMainHS.LastSavedDateTime
FROM BO_BuildMainHS
JOIN BO_BuildLinesHS ON BO_BuildMainHS.RecID = BO_BuildLinesHS.BuildID
JOIN BO_BuildBillDetailsHS ON BO_BuildLinesHS.HistoryID = BO_BuildBillDetailsHS.HistoryID
JOIN BO_BuildUsageDetails ON BO_BuildBillDetailsHS.BillID = BO_BuildUsageDetails.LineID
JOIN IN_Main ON BO_BUILDUsageDetails.InvID = IN_Main.InventoryID
JOIN BM_WorkOrder ON BM_WorkOrder.RecID = Bo_BuildMainHS.RecID
JOIN BM_WorkOrderOutputs ON BM_WorkOrderOutputs.BM_WorkOrder_RecID = BM_WorkOrder.RecID
LEFT JOIN IN_SOH ON IN_SOH.LinkID = BO_BuildUsageDetails.SOHID
WHERE BO_BUILDUsageDetails.IsComponent = 0
AND BM_WorkOrderOutputs.InventoryID = BO_BuildUsageDetails.InvID
AND BM_WorkOrder.Migrated = 1
AND (BO_BuildUsageDetails.SOHID IS NULL OR BO_BuildUsageDetails.SOHID IN (SELECT LinkID FROM IN_SOH))




[dimuthu]

Thanks Mike.

[dimuthu]

Hi Mike,

Thanks for the scripts. Could you please tell me what is the new V7 field for "BO_BuildMain.Reversed". I could not find in the new table structure.

This is my V6 table link

Select bl.Qty
From BO_BuildMain bm, BO_BuildLines bl
Where bl.BuildID = bm.RecID
And bm.Status = 1
And bm.Reversed = 0
And bl.InventoryID = @InventoryID
And bm.WarehouseID = @IN_LogicalID


Following is my new V7 table link

SELECT BM_WorkOrderInputs.Quantity
FROM BM_WorkOrder INNER JOIN
BM_WorkOrderStages ON BM_WorkOrder.RecID = BM_WorkOrderStages.BM_WorkOrder_RecID INNER JOIN
BM_WorkOrderInputs ON BM_WorkOrderStages.RecID = BM_WorkOrderInputs.BM_WorkOrderStages_RecID
WHERE BM_WorkOrder.IN_LogicalID = @IN_LogicalID AND
BM_WorkOrderInputs.InventoryID = @InventoryID

Regards
Dimuthu

[Mike.Sheen]

Hi Dimuthu,

There is no "reversed" flag as such in the new data structure - but work orders with a non-null Reversal_BM_WorkOrder_RecID in the BM_WorkOrder table can be considered reversed.

Mike

[dimuthu]

Thanks Mike.