Baub Eis
asked on
Help Troubleshooting ASP classic call to a store procedure in Sql Server 2017
I am integrating two databases. One database I designed, another is a manufacturing database. When I try to call a stored procedure from an asp classic page I get a
Operation is not allowed when the object is closed
error in the browser developers tool. so i set a test site up that was configured to run asp.net and the same stored procedure call worked no problem. So I am wondring are there things in a stored procedure that wouldn't work in classic asp? I know I should have started this site in asp.net, but old habits are hard to break. Just looking for some ideas to troubleshoot my issues. If I create just a short 1 parameter stored procedure and do a select from the database in classic asp it works. So I didn't know if there was a way to step through the stored procedure as it was running to figure out what might be causing the issue?
Operation is not allowed when the object is closed
error in the browser developers tool. so i set a test site up that was configured to run asp.net and the same stored procedure call worked no problem. So I am wondring are there things in a stored procedure that wouldn't work in classic asp? I know I should have started this site in asp.net, but old habits are hard to break. Just looking for some ideas to troubleshoot my issues. If I create just a short 1 parameter stored procedure and do a select from the database in classic asp it works. So I didn't know if there was a way to step through the stored procedure as it was running to figure out what might be causing the issue?
"Operation is not allowed when the object is closed "
I'd guess this refers to the connection to the database server itself. Are you sure the connection exists and is open?
I'd guess this refers to the connection to the database server itself. Are you sure the connection exists and is open?
How To Create a Database Connection from an ASP Page in IIS
ASKER
Yes I am able to connect to the db. Its an asp classic versus asp.net thing. I created another testing site setup as .net to test and the stored procedure worked no problem.
I was able to run a basic stored procedure on the DB and it works, so I'm guessing its something somewhere in the stored procedure that doesn't like asp classic? I could post the stored procedure? Or I could email to someone. don't really want it shown public
I was able to run a basic stored procedure on the DB and it works, so I'm guessing its something somewhere in the stored procedure that doesn't like asp classic? I could post the stored procedure? Or I could email to someone. don't really want it shown public
This is because an ADODB.Recordset object is closed. You mentioned that it is a classic ASP application than it must use ADO (Active Data Objects Library). You should find a object.MoveNext or object("fieldname") expression somewhere is the code. These lines must be after an object.Open line.
But we would need more information to help.
But we would need more information to help.
ASKER
This is the stored procedure. Not sure if this is how I should post this code or not?
-------------------------- ----------
USE [HiTekLLCMIETrakSandbox]
GO
/****** Object: StoredProcedure [dbo].[usp_ReleaseWorkOrder] Script Date: 4/30/2020 4:09:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ReleaseWorkOrder]
@WorkOrderPK int = -1
AS
BEGIN
SET NOCOUNT ON
-- ADD THIS TO ABOVE TRIGGER
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @WorkOrderAssemblyPK int
DECLARE @WorkOrderReleaseFK int
DECLARE @RouterFK int
DECLARE @ItemRouterFK int
DECLARE @WorkCenterFK int
DECLARE @ItemFK int
DECLARE @ItemTypeFK int
DECLARE @DivisionFK int
DECLARE @WorkCenterDivisionFK int
DECLARE @RelatedWC int
DECLARE @DefaultWIPInventoryLocationFK int
DECLARE @UserFK int
DECLARE @NeedDelete bit
DECLARE @Count int
DECLARE @OrderBy int
DECLARE @RouterWorkCenterPK int
DECLARE @WorkOrderReleasePK int
DECLARE @ParentRouterFK int
DECLARE @QuantityRequired decimal(15,5)
DECLARE @QuantityToPull decimal(15,3)
DECLARE @QuantityToFabricate decimal(15,3)
DECLARE @QuantityHardware decimal(15,3)
DECLARE @QuantityMaterial decimal(15,3)
DECLARE @QuantityOutsideProcessing decimal(15,3)
DECLARE @ParentWorkOrderReleaseQuantityRequired decimal(15,3)
DECLARE @ProgramNumber nvarchar(100)
DECLARE @OperationFK int
DECLARE @SupplierFK int
DECLARE @UnitOfMeasureSetFK int
DECLARE @AgainstGrain bit
DECLARE @BatchSize decimal(15,3)
DECLARE @BlankLength decimal(15,5)
DECLARE @BlankWidth decimal(15,5)
DECLARE @CertificationsRequired bit
DECLARE @Color nvarchar(50)
DECLARE @Comment nvarchar(MAX)
DECLARE @DaysOut int
DECLARE @DoubleSided bit
DECLARE @GoodUntil datetime
DECLARE @Instructions nvarchar(MAX)
DECLARE @LagTime decimal
DECLARE @LeadTime int
DECLARE @LotPrice int
DECLARE @MinimumPiecePrice decimal(15,3)
DECLARE @Nestable bit
DECLARE @NonAmortizedItem bit
DECLARE @OveragePercentage decimal(9,3)
DECLARE @CoveragePercentage decimal(9,3)
DECLARE @Overlap decimal(15,5)
DECLARE @PartWidth decimal(15,5)
DECLARE @PartLength decimal(15,5)
DECLARE @PartsPerBlank decimal(15,3)
DECLARE @PartsPerBlankScrapPercentage decimal(9,3)
DECLARE @PartsRequired decimal(15,5)
DECLARE @PiecesPerHour decimal(15,5)
DECLARE @PieceWeight decimal(15,5)
DECLARE @Price decimal(15,5)
DECLARE @Price2 decimal(15,5)
DECLARE @Price3 decimal(15,5)
DECLARE @Price4 decimal(15,5)
DECLARE @Price5 decimal(15,5)
DECLARE @Price6 decimal(15,5)
DECLARE @Price7 decimal(15,5)
DECLARE @Price8 decimal(15,5)
DECLARE @Price9 decimal(15,5)
DECLARE @Price10 decimal(15,5)
DECLARE @Price11 decimal(15,5)
DECLARE @Price12 decimal(15,5)
DECLARE @Quantity1 decimal(15,5)
DECLARE @Quantity2 decimal(15,5)
DECLARE @Quantity3 decimal(15,5)
DECLARE @Quantity4 decimal(15,5)
DECLARE @Quantity5 decimal(15,5)
DECLARE @Quantity6 decimal(15,5)
DECLARE @Quantity7 decimal(15,5)
DECLARE @Quantity8 decimal(15,5)
DECLARE @Quantity9 decimal(15,5)
DECLARE @Quantity10 decimal(15,5)
DECLARE @Quantity11 decimal(15,5)
DECLARE @Quantity12 decimal(15,5)
DECLARE @ProductOrder int
DECLARE @SetStatusToPullFromInventory bit
DECLARE @RouterWorkCenterQuantityRequired decimal(15,5)
DECLARE @QuantityPerInverse decimal(15,3)
DECLARE @RunEmployees int
DECLARE @SafetyStockLength decimal(15,5)
DECLARE @SafetyStockWidth decimal(15,5)
DECLARE @SequenceNumber decimal
DECLARE @SetupEmployees int
DECLARE @SplitMachines int
DECLARE @StockLength decimal(15,5)
DECLARE @StockWidth decimal(15,5)
DECLARE @StockPieces int
DECLARE @StockPiecesScrapPercentage decimal(9,3)
DECLARE @UnattendedPercentage decimal(9,3)
DECLARE @StopSequence bit
DECLARE @MinutesPerPart decimal(15,3)
DECLARE @SetupPieces decimal(15,3)
DECLARE @SetupTime decimal(15,3)
DECLARE @StagingTime decimal(15,3)
DECLARE @TransitTime decimal(15,3)
DECLARE @TearDownTime decimal (15,3)
DECLARE @Thickness decimal(15,5)
DECLARE @Tolerance decimal(15,5)
DECLARE @ToolingComment nvarchar(MAX)
DECLARE @UnattendedOperation bit
DECLARE @UseExactMaterialCalculation bit
DECLARE @UserDefined1 decimal(15,5)
DECLARE @UserDefined2 decimal(15,5)
DECLARE @UserDefined3 decimal(15,5)
DECLARE @UserDefinedChar1 nvarchar(50)
DECLARE @UserDefinedChar2 nvarchar(50)
DECLARE @VendorUnit decimal(20,8)
DECLARE @MarkNumber nvarchar(50)
DECLARE @BOMRevision nvarchar(20)
DECLARE @WaitTime decimal(15,5)
DECLARE @WeightFactor decimal(15,9)
DECLARE @DefaultMachinesExist bit
DECLARE @DoNotPrintBOM bit
DECLARE @DropPercentage decimal(15,5)
DECLARE @SetupCharge decimal(17,5)
DECLARE @Minimum decimal(17,5)
DECLARE @RequiredComment bit
DECLARE @WorkOrderAssemblyBOMStatusPK int
DECLARE @WorkOrderAssemblyLaborStatusPK int
DECLARE @PartNumber nvarchar(100)
DECLARE @QuantityRequired2 decimal(15,5)
DECLARE @QuantityToPull2 decimal(15,3)
DECLARE @QuantityToFabricate2 decimal(15,3)
DECLARE @QuantityHardware2 decimal(15,3)
DECLARE @QuantityMaterial2 decimal(15,3)
DECLARE @QuantityOutsideProcessing2 decimal(15,3)
DECLARE @CurrentQuantityRequired decimal(15,5)
DECLARE @CurrentQuantityToPull decimal(15,3)
DECLARE @CurrentQuantityToFabricate decimal(15,3)
DECLARE @CurrentQuantityHardware decimal(15,3)
DECLARE @CurrentQuantityMaterial decimal(15,3)
DECLARE @CurrentQuantityOutsideProcessing decimal(15,3)
DECLARE @DefaultWIPLocationDescription nvarchar(100)
DECLARE @CommonSetupDescription nvarchar(100)
DECLARE @LastSequenceNumber int
DECLARE @TotalWorkOrderReleaseRows int
DECLARE @TotalRowCount int
-- create temp table to handle assembly creation
Create Table #WorkOrderReleaseTempTable (WorkOrderReleasePK int)
UPDATE WorkOrder SET ReleaseProcessing = 1 WHERE (WorkOrderPK = @WorkOrderPK)
SET @DivisionFK=(SELECT DivisionFK FROM WorkOrder WHERE (WorkOrderPK = @WorkOrderPK))
--------------------------------
-- Clean up WorkOrderAssembly
--------------------------------
-- ### ISSUE TO DEAL WITH, IF THERE HAS BEEN A PURCHASE ORDER CREATED, ETC FOR THE
-- ### ITEM YOU WILL NOT BE ABLE TO DELETE
--
-- The code below skips all the Added Items. The problem is if an item was added we may get duplicate
-- sequences on the labor side because we added a sequence to #1 and then re-releasing the system adds
-- another sequence 1, etc.
-- WE ALSO SKIP WHEN WorkOrderNestFK>0 because that is nested and we reference those nests
--
DECLARE SelectWorkOrderAssembly_Cursor CURSOR FAST_FORWARD LOCAL
FOR SELECT
WorkOrderAssemblyPK,
WorkOrderReleaseFK,
RouterFK,
ItemRouterFK,
WorkCenterFK,
ItemFK,
SequenceNumber
FROM
WorkOrderAssembly
WHERE
((WorkOrderFK = @WorkOrderPK) AND ISNULL(IsAddOn,0)=0 AND (ISNULL(WorkOrderNestFK,0)=0))
OPEN SelectWorkOrderAssembly_Cursor
FETCH NEXT FROM SelectWorkOrderAssembly_Cursor
INTO
@WorkOrderAssemblyPK,
@WorkOrderReleaseFK,
@RouterFK,
@ItemRouterFK,
@WorkCenterFK,
@ItemFK,
@SequenceNumber
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @NeedDelete = 1
IF (@WorkOrderReleaseFK IS NOT NULL)
BEGIN
-- First compare to see if WorkOrderAssembly.RouterFK is equal to WorkOrderRelease.RouterFK
SET @Count = (SELECT COUNT(*) FROM WorkOrderRelease WHERE (WorkOrderReleasePK = @WorkOrderReleaseFK) AND (RouterFK = @RouterFK))
IF (@Count > 0)
BEGIN
-- Second Compare against router now
IF (@RouterFK IS NOT NULL)
BEGIN
IF (@ItemRouterFK IS NOT NULL)
BEGIN
SET @Count = (SELECT COUNT(*) FROM RouterWorkCenter WHERE ((RouterFK = @RouterFK) AND (ItemRouterFK = @ItemRouterFK)))
IF (@Count = 1)
BEGIN
SET @NeedDelete = 0
END
END
ELSE IF (@WorkCenterFK IS NOT NULL)
BEGIN
SET @Count = (SELECT COUNT(*) FROM RouterWorkCenter WHERE ((RouterFK = @RouterFK) AND (WorkCenterFK = @WorkCenterFK) AND ((SequenceNumber IS NULL) OR ((SequenceNumber IS NOT NULL) AND (SequenceNumber = @SequenceNumber)))))
IF (@Count = 1)
BEGIN
SET @NeedDelete = 0
END
END
ELSE IF (@ItemFK IS NOT NULL)
BEGIN
-- WHY WE DON'T DELETE W/O SEQUENCE NUMBER
-- BECAUSE IF THE ITEM IS INITIALLY ATTACHED TO SEQUENCE ONE, AND MOVES TO SEQUENCE TWO
-- WE JUST WANT TO MODIFY, NOT DELETE. WE DO NOT WANT A NEW WORK ORDER ASSEMBLY IN THIS
-- SITUATION.
--
SET @Count = (SELECT COUNT(*) FROM RouterWorkCenter WHERE ((RouterFK = @RouterFK) AND (ItemFK = @ItemFK)))
IF (@Count = 1)
BEGIN
SET @NeedDelete = 0
END
END
END
END
END
IF (@NeedDelete = 1)
BEGIN
DELETE FROM WorkOrderAssembly WHERE (WorkOrderAssemblyPK = @WorkOrderAssemblyPK)
END
FETCH NEXT FROM SelectWorkOrderAssembly_Cursor
INTO
@WorkOrderAssemblyPK,
@WorkOrderReleaseFK,
@RouterFK,
@ItemRouterFK,
@WorkCenterFK,
@ItemFK,
@SequenceNumber
END
CLOSE SelectWorkOrderAssembly_Cursor
DEALLOCATE SelectWorkOrderAssembly_Cursor
--------------------------------
-- SET ALL ADDED SEQUENCES TO NULL AND RENUMBER AT THE END
-- SO THERE ARE NO DUPLICATE SEQUENCES
--------------------------------
UPDATE WorkOrderAssembly SET SequenceNumber=null FROM WorkOrderAssembly WHERE (WorkOrderFK = @WorkOrderPK) AND (IsAddOn = 1)
------------------------------------------------
-- CLEAN DANGLING WorkOrderRelease rows
-----------------------------------------------
DELETE FROM WorkOrderRelease WHERE WorkOrderFK=@WorkOrderPK AND ParentRouterFK IS NOT NULL AND ISNULL(QuantityShipped,0)=0
AND (SELECT COUNT(*) FROM WorkOrderRelease wrParent WHERE wrParent.WorkOrderFK=@WorkOrderPK AND RouterFK=WorkOrderRelease.ParentRouterFK)=0
SET @TotalWorkOrderReleaseRows = (SELECT COUNT(*) FROM WorkOrderRelease WHERE WorkOrderFK=@WorkOrderPK)
SET @TotalRowCount=0
--------------------------------
-- Generate Work Order Assembly
--------------------------------
DECLARE SelectWorkOrderRelease_Cursor CURSOR FAST_FORWARD LOCAL
FOR SELECT
WorkOrderReleasePK,
RouterFK,
ParentRouterFK,
QuantityRequired,
QuantityToPull,
QuantityToFabricate,
QuantityHardware,
QuantityMaterial,
QuantityOutsideProcessing
FROM
WorkOrderRelease
WHERE
(WorkOrderFK = @WorkOrderPK)
OPEN SelectWorkOrderRelease_Cursor
FETCH NEXT FROM SelectWorkOrderRelease_Cursor
INTO
@WorkOrderReleasePK,
@RouterFK,
@ParentRouterFK,
@QuantityRequired,
@QuantityToPull,
@QuantityToFabricate,
@QuantityHardware,
@QuantityMaterial,
@QuantityOutsideProcessing
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Set Page Count
SET @TotalRowCount=@TotalRowCount+1
UPDATE WorkOrderRelease SET PageOf=@TotalWorkOrderReleaseRows,PageNumber=@TotalRowCount WHERE WorkOrderReleasePK=@WorkOrderReleasePK
-- CLEANING WorkOrderRelease. This is a double check on the work order release
-- when a parents, parent is a pull we get a danglig work order release which
-- has no ParentFK. We want to delete this WorkOrderRelease. This does not happen
-- when the CleanChildren button is hit before releasing.
---------------
-- THIS CODE WAS MOVED TO A SINGLE STATEMENT ABOVE
--------------
/*
DECLARE @FoundParentCount int
IF (@ParentRouterFK IS NOT NULL)
BEGIN
SET @FoundParentCount=(SELECT COUNT(*) FROM WorkOrderRelease WHERE WorkOrderFK=@WorkOrderPK AND RouterFK=@ParentRouterFK)
IF @FoundParentCount=0
BEGIN
DELETE WorkOrderRelease WHERE WorkOrderReleasePK=@WorkOrderReleasePK
CONTINUE
END
END
*/
----------------------------------------------------------------------------
-- THIS LOOP GOES THROUGH THE RouterWorkCenter table for all items which are
-- sequences. Each sequence adds an entry to the WorkOrderAssembly table.
----------------------------------------------------------------------------
DECLARE SelectRouterWorkCenter_Cursor1 CURSOR FAST_FORWARD LOCAL
FOR SELECT
RouterWorkCenterPK,
ItemRouterFK,
WorkCenterFK,
ItemFK,
OperationFK,
SupplierFK,
UnitOfMeasureSetFK,
DefaultWIPInventoryLocationFK,
UserFK,
AgainstGrain,
BlankLength,
BlankWidth,
CertificationsRequired,
Color,
Comment,
DaysOut,
DoubleSided,
GoodUntil,
Instructions,
LagTime,
LeadTime,
MinimumPiecePrice,
Nestable,
NonAmortizedItem,
OveragePercentage,
CoveragePercentage,
Overlap,
PartWidth,
PartLength,
PartsPerBlank,
PartsPerBlankScrapPercentage,
PartsRequired,
PiecesPerHour,
PieceWeight,
Price,
ProductOrder,
SetStatusToPullFromInventory,
QuantityRequired,
QuantityPerInverse,
RunEmployees,
ISNULL(SetupPieces,0),
SafetyStockLength,
SafetyStockWidth,
SequenceNumber,
SetupEmployees,
SplitMachines,
StockLength,
StockWidth,
StockPieces,
StockPiecesScrapPercentage,
UnattendedPercentage,
StopSequence,
MinutesPerPart,
SetupTime,
StagingTime,
TransitTime,
TearDownTime,
Thickness,
Tolerance,
ToolingComment,
UnattendedOperation,
UseExactMaterialCalculation,
UserDefined1,
UserDefined2,
UserDefined3,
UserDefinedChar1,
UserDefinedChar2,
VendorUnit,
MarkNumber,
WaitTime,
WeightFactor,
[BatchSize],
OrderBy,
LotPrice,
RequiredComment,
SetupCharge,
Minimum,
CommonSetupDescription
FROM
RouterWorkCenter
WHERE
((RouterFK = @RouterFK) AND (WorkCenterFK IS NOT NULL))
ORDER BY
SequenceNumber
OPEN SelectRouterWorkCenter_Cursor1
FETCH NEXT FROM SelectRouterWorkCenter_Cursor1
INTO
@RouterWorkCenterPK,
@ItemRouterFK,
@WorkCenterFK,
@ItemFK,
@OperationFK,
@SupplierFK,
@UnitOfMeasureSetFK,
@DefaultWIPInventoryLocationFK,
@UserFK,
@AgainstGrain,
@BlankLength,
@BlankWidth,
@CertificationsRequired,
@Color,
@Comment,
@DaysOut,
@DoubleSided,
@GoodUntil,
@Instructions,
@LagTime,
@LeadTime,
@MinimumPiecePrice,
@Nestable,
@NonAmortizedItem,
@OveragePercentage,
@CoveragePercentage,
@Overlap,
@PartWidth,
@PartLength,
@PartsPerBlank,
@PartsPerBlankScrapPercentage,
@PartsRequired,
@PiecesPerHour,
@PieceWeight,
@Price,
@ProductOrder,
@SetStatusToPullFromInventory,
@RouterWorkCenterQuantityRequired,
@QuantityPerInverse,
@RunEmployees,
@SetupPieces,
@SafetyStockLength,
@SafetyStockWidth,
@SequenceNumber,
@SetupEmployees,
@SplitMachines,
@StockLength,
@StockWidth,
@StockPieces,
@StockPiecesScrapPercentage,
@UnattendedPercentage,
@StopSequence,
@MinutesPerPart,
@SetupTime,
@StagingTime,
@TransitTime,
@TearDownTime,
@Thickness,
@Tolerance,
@ToolingComment,
@UnattendedOperation,
@UseExactMaterialCalculation,
@UserDefined1,
@UserDefined2,
@UserDefined3,
@UserDefinedChar1,
@UserDefinedChar2,
@VendorUnit,
@MarkNumber,
@WaitTime,
@WeightFactor,
@BatchSize,
@OrderBy,
@LotPrice,
@RequiredComment,
@SetupCharge,
@Minimum,
@CommonSetupDescription
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @WorkOrderAssemblyBOMStatusPK = NULL
SET @WorkOrderAssemblyLaborStatusPK = 2 -- (SELECT TOP 1 WorkOrderAssemblyLaborStatusPK FROM WorkOrderAssemblyLaborStatus WHERE Code = '002')
SELECT @PartNumber=PartNumber,@BOMRevision=Revision FROM Item WHERE ItemPK = @ItemFK
SET @CurrentQuantityRequired = @QuantityRequired
SET @CurrentQuantityToPull = @QuantityToPull
SET @CurrentQuantityToFabricate = @QuantityToFabricate
SET @CurrentQuantityHardware = @QuantityHardware
SET @CurrentQuantityMaterial = @QuantityMaterial
SET @CurrentQuantityOutsideProcessing = @QuantityOutsideProcessing
SET @WorkOrderAssemblyPK = (SELECT TOP 1 WorkOrderAssemblyPK FROM WorkOrderAssembly WHERE (WorkOrderFK = @WorkOrderPK) AND (WorkOrderReleaseFK = @WorkOrderReleasePK) AND (RouterFK = @RouterFK) AND (WorkCenterFK = @WorkCenterFK) AND ((SequenceNumber IS NULL ) OR ((SequenceNumber IS NOT NULL ) AND (SequenceNumber = @SequenceNumber))))
--
-- IF THE WORK ORDER DIVISION DOES NOT MATCH THE DIVISION OF THE WORK CENTER, TRY TO SWITCH
--
SET @WorkCenterDivisionFK = (SELECT DivisionFK FROM WorkCenter WHERE WorkCenterPK=@WorkCenterFK)
IF @DivisionFK IS NOT NULL AND @WorkCenterDivisionFK IS NOT NULL AND @WorkCenterDivisionFK<>@DivisionFK
BEGIN
SET @RelatedWC = (SELECT TOP 1 RelatedWorkCenterFK FROM WorkCenterRelationship JOIN WorkCenter w ON w.WorkCenterPK=RelatedWorkCenterFK WHERE WorkCenterFK=@WorkCenterFK AND w.DivisionFK=@DivisionFK)
IF @RelatedWC IS NOT NULL
BEGIN
SET @WorkCenterFK=@RelatedWC
END
END
SET @DefaultMachinesExist=0
IF (SELECT COUNT(*) FROM RouterWorkCenterMachine WHERE RouterWorkCenterFK=@RouterWorkCenterPK)>0
BEGIN
SET @DefaultMachinesExist=1
END
SET @DefaultWIPLocationDescription=(SELECT TOP 1 Description FROM Location WHERE LocationPK=@DefaultWIPInventoryLocationFK)
IF (@WorkOrderAssemblyPK IS NULL)
BEGIN
IF (@QuantityToFabricate <> 0)
BEGIN
INSERT INTO WorkOrderAssembly
(
WorkOrderFK,
RouterFK,
ParentRouterFK,
ItemRouterFK,
WorkCenterFK,
ItemFK,
OperationFK,
SupplierFK,
UnitOfMeasureSetFK,
DefaultWIPInventoryLocationFK,
UserFK,
WIPMovementLocation,
WorkOrderAssemblyBOMStatusFK,
WorkOrderAssemblyLaborStatusFK,
WorkOrderReleaseFK,
PartNumber,
AgainstGrain,
BlankLength,
BlankWidth,
CertificationsRequired,
Color,
Comment,
DaysOut,
DoubleSided,
GoodUntil,
Instructions,
LagTime,
LeadTime,
MinimumPiecePrice,
Nestable,
NonAmortizedItem,
OveragePercentage,
CoveragePercentage,
Overlap,
PartWidth,
PartLength,
PartsPerBlank,
PartsPerBlankScrapPercentage,
PartsRequired,
PiecesPerHour,
PieceWeight,
Price,
ProductOrder,
QuantityPerInverse,
RunEmployees,
SafetyStockLength,
SafetyStockWidth,
SequenceNumber,
SetupEmployees,
SplitMachines,
StockLength,
StockWidth,
StockPieces,
StockPiecesScrapPercentage,
UnattendedPercentage,
StopSequence,
MinutesPerPart,
SetupTime,
StagingTime,
TransitTime,
TearDownTime,
Thickness,
Tolerance,
ToolingComment,
UnattendedOperation,
UseExactMaterialCalculation,
UserDefined1,
UserDefined2,
UserDefined3,
UserDefinedChar1,
UserDefinedChar2,
VendorUnit,
MarkNumber,
Revision,
WaitTime,
WeightFactor,
SetStatusToPullFromInventory,
QuantityRequired,
QuantityPull,
QuantityToFabricate,
QuantityOrdered,
QuantityIssued,
QuantityScrapped,
Inspected,
IsAddOn,
IsEdited,
DefaultMachinesExist,
Released,
[BatchSize],
OrderBy,
LotPrice,
RequiredComment,
SetupCharge,
SetupPieces,
Minimum,
CommonSetupDescription
)
VALUES
(
@WorkOrderPK,
@RouterFK,
@ParentRouterFK,
@ItemRouterFK,
@WorkCenterFK,
@ItemFK,
@OperationFK,
@SupplierFK,
@UnitOfMeasureSetFK,
@DefaultWIPInventoryLocationFK,
@UserFK,
@DefaultWIPLocationDescription,
@WorkOrderAssemblyBOMStatusPK,
@WorkOrderAssemblyLaborStatusPK,
@WorkOrderReleasePK,
@PartNumber,
@AgainstGrain,
@BlankLength,
@BlankWidth,
@CertificationsRequired,
@Color,
@Comment,
@DaysOut,
@DoubleSided,
@GoodUntil,
@Instructions,
@LagTime,
@LeadTime,
@MinimumPiecePrice,
@Nestable,
@NonAmortizedItem,
@OveragePercentage,
@CoveragePercentage,
@Overlap,
@PartWidth,
@PartLength,
@PartsPerBlank,
@PartsPerBlankScrapPercentage,
@PartsRequired,
@PiecesPerHour,
@PieceWeight,
@Price,
@ProductOrder,
@QuantityPerInverse,
@RunEmployees,
@SafetyStockLength,
@SafetyStockWidth,
@SequenceNumber,
@SetupEmployees,
@SplitMachines,
@StockLength,
@StockWidth,
@StockPieces,
@StockPiecesScrapPercentage,
@UnattendedPercentage,
@StopSequence,
@MinutesPerPart,
@SetupTime,
@StagingTime,
@TransitTime,
@TearDownTime,
@Thickness,
@Tolerance,
@ToolingComment,
@UnattendedOperation,
@UseExactMaterialCalculation,
@UserDefined1,
@UserDefined2,
@UserDefined3,
@UserDefinedChar1,
@UserDefinedChar2,
@VendorUnit,
@MarkNumber,
@BOMRevision,
@WaitTime,
@WeightFactor,
@SetStatusToPullFromInventory,
@CurrentQuantityRequired,
@CurrentQuantityToPull,
@CurrentQuantityToFabricate+@SetupPieces,
0,
0,
0,
0,
0,
0,
@DefaultMachinesExist,
1,
@BatchSize,
@OrderBy,
@LotPrice,
@RequiredComment,
@SetupCharge,
@SetupPieces,
@Minimum,
@CommonSetupDescription
)
END
END
ELSE
BEGIN
IF (@QuantityToFabricate <> 0)
BEGIN
UPDATE WorkOrderAssembly
SET
--WorkOrderFK = @WorkOrderFK,
--RouterFK = @RouterFK,
ParentRouterFK = @ParentRouterFK,
ItemRouterFK = @ItemRouterFK,
WorkCenterFK = @WorkCenterFK,
ItemFK = @ItemFK,
OperationFK = @OperationFK,
SupplierFK = @SupplierFK,
UnitOfMeasureSetFK = @UnitOfMeasureSetFK,
DefaultWIPInventoryLocationFK=@DefaultWIPInventoryLocationFK,
UserFK = @UserFK,
WIPMovementLocation = (SELECT TOP 1 Description FROM Location WHERE LocationPK=@DefaultWIPInventoryLocationFK),
WorkOrderAssemblyBOMStatusFK = @WorkOrderAssemblyBOMStatusPK,
WorkOrderAssemblyLaborStatusFK = @WorkOrderAssemblyLaborStatusPK,
--WorkOrderReleaseFK = @WorkOrderReleasePK,
PartNumber = @PartNumber,
AgainstGrain = @AgainstGrain,
[BatchSize] = @BatchSize,
BlankLength = @BlankLength,
BlankWidth = @BlankWidth,
CertificationsRequired = @CertificationsRequired,
Color = @Color,
Comment = @Comment,
DaysOut = @DaysOut,
DoubleSided = @DoubleSided,
GoodUntil = @GoodUntil,
Instructions = @Instructions,
LagTime = @LagTime,
LeadTime = @LeadTime,
LotPrice = @LotPrice,
MinimumPiecePrice = @MinimumPiecePrice,
Nestable = @Nestable,
NonAmortizedItem = @NonAmortizedItem,
OveragePercentage = @OveragePercentage,
CoveragePercentage = @CoveragePercentage,
Overlap = @Overlap,
PartWidth = @PartWidth,
PartLength = @PartLength,
PartsPerBlank = @PartsPerBlank,
PartsPerBlankScrapPercentage = @PartsPerBlankScrapPercentage,
PartsRequired = @PartsRequired,
PiecesPerHour = @PiecesPerHour,
PieceWeight = @PieceWeight,
Price = @Price,
ProductOrder = @ProductOrder,
QuantityPerInverse = @QuantityPerInverse,
RunEmployees = @RunEmployees,
SafetyStockLength = @SafetyStockLength,
SafetyStockWidth = @SafetyStockWidth,
SequenceNumber = @SequenceNumber,
SetupEmployees = @SetupEmployees,
SplitMachines = @SplitMachines,
StockLength = @StockLength,
StockWidth = @StockWidth,
StockPieces = @StockPieces,
StockPiecesScrapPercentage = @StockPiecesScrapPercentage,
UnattendedPercentage = @UnattendedPercentage,
StopSequence = @StopSequence,
MinutesPerPart = @MinutesPerPart,
SetupCharge = @SetupCharge,
SetupTime = @SetupTime,
StagingTime = @StagingTime,
TransitTime = @TransitTime,
TearDownTime = @TearDownTime,
Thickness = @Thickness,
Tolerance = @Tolerance,
ToolingComment = @ToolingComment,
UnattendedOperation = @UnattendedOperation,
UseExactMaterialCalculation = @UseExactMaterialCalculation,
UserDefined1 = @UserDefined1,
UserDefined2 = @UserDefined2,
UserDefined3 = @UserDefined3,
UserDefinedChar1 = @UserDefinedChar1,
UserDefinedChar2 = @UserDefinedChar2,
VendorUnit = @VendorUnit,
MarkNumber = @MarkNumber,
Revision = @BOMRevision,
WaitTime = @WaitTime,
WeightFactor = @WeightFactor,
SetStatusToPullFromInventory = @SetStatusToPullFromInventory,
QuantityRequired = @CurrentQuantityRequired,
QuantityPull = @CurrentQuantityToPull,
QuantityToFabricate = @CurrentQuantityToFabricate+@SetupPieces,
QuantityOrdered = 0,
QuantityIssued = 0,
QuantityScrapped = 0,
Inspected = 0,
IsAddOn = 0,
IsEdited = 0,
OrderBy = @OrderBy,
DefaultMachinesExist=@DefaultMachinesExist,
-- old way with nests, Released = (SELECT (CASE WHEN (ISNULL(Released,0)=0 AND ISNULL(IsNested,0)=0 AND ISNULL(DropFlag,0)=0) THEN 1 ELSE 0 END)),
Released = (SELECT (CASE WHEN (ISNULL(Released,0)=0 AND ISNULL(DropFlag,0)=0) THEN 1 ELSE 0 END)),
SetupPieces = @SetupPieces,
RequiredComment = @RequiredComment,
Minimum = @Minimum,
CommonSetupDescription = @CommonSetupDescription
WHERE
(WorkOrderAssemblyPK = @WorkOrderAssemblyPK)
-- Update Released
-- UPDATE WorkOrderAssembly SET Released = 1 WHERE ((WorkOrderAssemblyPK = @WorkOrderAssemblyPK) AND (((Released IS NULL) OR ((Released IS NOT NULL) AND (Released <> 1))) AND ((IsNested IS NULL) OR ((IsNested IS NOT NULL) AND (IsNested = 0))) AND ((DropFlag IS NULL) OR ((DropFlag IS NOT NULL) AND (DropFlag = 0)))) AND ((IsAddOn IS NOT NULL) AND (IsAddOn = 1)))
-- PERFORMANCE SO the WorkOrderAssembly trigger is not fired twice, we do it above.
/*
UPDATE WorkOrderAssembly SET Released = 1
WHERE ((WorkOrderAssemblyPK = @WorkOrderAssemblyPK)
AND ((Released IS NULL) OR (Released =0 ))
AND ((IsNested IS NULL) OR (IsNested =0 ))
AND ((DropFlag IS NULL) OR (DropFlag =0 ))
);
*/
END
ELSE
BEGIN
DELETE FROM WorkOrderAssembly WHERE (WorkOrderAssemblyPK = @WorkOrderAssemblyPK)
END
END
FETCH NEXT FROM SelectRouterWorkCenter_Cursor1
INTO
@RouterWorkCenterPK,
@ItemRouterFK,
@WorkCenterFK,
@ItemFK,
@OperationFK,
@SupplierFK,
@UnitOfMeasureSetFK,
@DefaultWIPInventoryLocationFK,
@UserFK,
@AgainstGrain,
@BlankLength,
@BlankWidth,
@CertificationsRequired,
@Color,
@Comment,
@DaysOut,
@DoubleSided,
@GoodUntil,
@Instructions,
@LagTime,
@LeadTime,
@MinimumPiecePrice,
@Nestable,
@NonAmortizedItem,
@OveragePercentage,
@CoveragePercentage,
@Overlap,
@PartWidth,
@PartLength,
@PartsPerBlank,
@PartsPerBlankScrapPercentage,
@PartsRequired,
@PiecesPerHour,
@PieceWeight,
@Price,
@ProductOrder,
@SetStatusToPullFromInventory,
@RouterWorkCenterQuantityRequired,
@QuantityPerInverse,
@RunEmployees,
@SetupPieces,
@SafetyStockLength,
@SafetyStockWidth,
@SequenceNumber,
@SetupEmployees,
@SplitMachines,
@StockLength,
@StockWidth,
@StockPieces,
@StockPiecesScrapPercentage,
@UnattendedPercentage,
@StopSequence,
@MinutesPerPart,
@SetupTime,
@StagingTime,
@TransitTime,
@TearDownTime,
@Thickness,
@Tolerance,
@ToolingComment,
@UnattendedOperation,
@UseExactMaterialCalculation,
@UserDefined1,
@UserDefined2,
@UserDefined3,
@UserDefinedChar1,
@UserDefinedChar2,
@VendorUnit,
@MarkNumber,
@WaitTime,
@WeightFactor,
@BatchSize,
@OrderBy,
@LotPrice,
@RequiredComment,
@SetupCharge,
@Minimum,
@CommonSetupDescription
END
CLOSE SelectRouterWorkCenter_Cursor1
DEALLOCATE SelectRouterWorkCenter_Cursor1
----------------------------------------------------------------------------
-- BOM ITEM UPATES/INSERTS
--
-- THIS LOOP GOES THROUGH THE RouterWorkCenter table for all items which are
-- bill of material. This loops and creates BOM items.
----------------------------------------------------------------------------
DECLARE SelectRouterWorkCenter_Cursor2 CURSOR FAST_FORWARD LOCAL
FOR SELECT
ItemRouterFK,
WorkCenterFK,
ItemFK,
OperationFK,
SupplierFK,
UnitOfMeasureSetFK,
DefaultWIPInventoryLocationFK,
UserFK,
AgainstGrain,
BlankLength,
BlankWidth,
CertificationsRequired,
Color,
Comment,
DaysOut,
DoubleSided,
GoodUntil,
Instructions,
LagTime,
LeadTime,
MinimumPiecePrice,
Nestable,
NonAmortizedItem,
OveragePercentage,
CoveragePercentage,
Overlap,
PartWidth,
PartLength,
PartsPerBlank,
PartsPerBlankScrapPercentage,
PartsRequired,
PiecesPerHour,
PieceWeight,
Price,
Price2,
Price3,
Price4,
Price5,
Price6,
Price7,
Price8,
Price9,
Price10,
Price11,
Price12,
Quantity1,
Quantity2,
Quantity3,
Quantity4,
Quantity5,
Quantity6,
Quantity7,
Quantity8,
Quantity9,
Quantity10,
Quantity11,
Quantity12,
ProductOrder,
SetStatusToPullFromInventory,
QuantityRequired,
QuantityPerInverse,
RunEmployees,
SafetyStockLength,
SafetyStockWidth,
SequenceNumber,
SetupEmployees,
SplitMachines,
StockLength,
StockWidth,
StockPieces,
StockPiecesScrapPercentage,
UnattendedPercentage,
StopSequence,
MinutesPerPart,
SetupTime,
StagingTime,
TransitTime,
TearDownTime,
Thickness,
Tolerance,
ToolingComment,
UnattendedOperation,
UseExactMaterialCalculation,
UserDefined1,
UserDefined2,
UserDefined3,
UserDefinedChar1,
UserDefinedChar2,
VendorUnit,
MarkNumber,
WaitTime,
WeightFactor,
DropPercentage,
ProgramNumber,
[BatchSize],
OrderBy,
LotPrice,
SetupCharge,
DoNotPrintBOM,
Minimum,
CommonSetupDescription
FROM
RouterWorkCenter
WHERE
((RouterFK = @RouterFK) AND (WorkCenterFK IS NULL))
ORDER BY
SequenceNumber
OPEN SelectRouterWorkCenter_Cursor2
FETCH NEXT FROM SelectRouterWorkCenter_Cursor2
INTO
@ItemRouterFK,
@WorkCenterFK,
@ItemFK,
@OperationFK,
@SupplierFK,
@UnitOfMeasureSetFK,
@DefaultWIPInventoryLocationFK,
@UserFK,
@AgainstGrain,
@BlankLength,
@BlankWidth,
@CertificationsRequired,
@Color,
@Comment,
@DaysOut,
@DoubleSided,
@GoodUntil,
@Instructions,
@LagTime,
@LeadTime,
@MinimumPiecePrice,
@Nestable,
@NonAmortizedItem,
@OveragePercentage,
@CoveragePercentage,
@Overlap,
@PartWidth,
@PartLength,
@PartsPerBlank,
@PartsPerBlankScrapPercentage,
@PartsRequired,
@PiecesPerHour,
@PieceWeight,
@Price,
@Price2,
@Price3,
@Price4,
@Price5,
@Price6,
@Price7,
@Price8,
@Price9,
@Price10,
@Price11,
@Price12,
@Quantity1,
@Quantity2,
@Quantity3,
@Quantity4,
@Quantity5,
@Quantity6,
@Quantity7,
@Quantity8,
@Quantity9,
@Quantity10,
@Quantity11,
@Quantity12,
@ProductOrder,
@SetStatusToPullFromInventory,
@RouterWorkCenterQuantityRequired,
@QuantityPerInverse,
@RunEmployees,
@SafetyStockLength,
@SafetyStockWidth,
@SequenceNumber,
@SetupEmployees,
@SplitMachines,
@StockLength,
@StockWidth,
@StockPieces,
@StockPiecesScrapPercentage,
@UnattendedPercentage,
@StopSequence,
@MinutesPerPart,
@SetupTime,
@StagingTime,
@TransitTime,
@TearDownTime,
@Thickness,
@Tolerance,
@ToolingComment,
@UnattendedOperation,
@UseExactMaterialCalculation,
@UserDefined1,
@UserDefined2,
@UserDefined3,
@UserDefinedChar1,
@UserDefinedChar2,
@VendorUnit,
@MarkNumber,
@WaitTime,
@WeightFactor,
@DropPercentage,
@ProgramNumber,
@BatchSize,
@OrderBy,
@LotPrice,
@SetupCharge,
@DoNotPrintBOM,
@Minimum,
@CommonSetupDescription
SET @LastSequenceNumber=-1
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @WorkOrderAssemblyLaborStatusPK = NULL
SET @WorkOrderAssemblyBOMStatusPK = 1 --(SELECT TOP 1 WorkOrderAssemblyBOMStatusPK FROM WorkOrderAssemblyBOMStatus WHERE Code = '001')
SELECT @PartNumber=PartNumber,@ItemTypeFK=ItemTypeFK,@BOMRevision=Revision FROM Item WHERE ItemPK = @ItemFK
-- !!!!!
-- THE @QuantityToFabricate, etc are directly from WorkOrderRelease
-- WE RESET THESE VALUES AND IF ITS A ItemRouter we set them to the
-- Item Router Version
-- !!!!
SET @CurrentQuantityRequired = @RouterWorkCenterQuantityRequired
SET @CurrentQuantityToPull = @QuantityToPull
SET @CurrentQuantityToFabricate = @QuantityToFabricate
SET @CurrentQuantityHardware = @QuantityHardware
SET @CurrentQuantityMaterial = @QuantityMaterial
SET @CurrentQuantityOutsideProcessing = @QuantityOutsideProcessing
-- FROM THIS POINT DOWN WE USE Current
IF (@ItemRouterFK IS NOT NULL)
BEGIN
DECLARE @WorkOrderReleaseParentFoundID int
-- IF THERE IS NO WorkOrderRelease row this item was a MERGE to put common parts on a single mfg row
-- If this is true then keep existing quantitites
IF (SELECT COUNT(*) FROM WorkOrderRelease WHERE ((WorkOrderFK = @WorkOrderPK) AND (RouterFK = @ItemRouterFK) AND (ParentRouterFK = @RouterFK)))>0
BEGIN
-- KEY, WE ONLY WANT TO GRAB THE WORK ORDER RELEASE PARENT 1 TIME!!!!!!!!!
SET @WorkOrderReleaseParentFoundID=0
SELECT TOP 1
-- (DO NOT CHANGE THIS 4/15/2015), @CurrentQuantityRequired = QuantityRequired
@CurrentQuantityToPull=QuantityToPull
,@CurrentQuantityToFabricate=QuantityToFabricate
,@CurrentQuantityHardware=QuantityHardware
,@CurrentQuantityMaterial=QuantityMaterial
,@CurrentQuantityOutsideProcessing=QuantityOutsideProcessing
,@WorkOrderReleaseParentFoundID=WorkOrderReleasePK
FROM
WorkOrderRelease
WHERE
((WorkOrderFK = @WorkOrderPK) AND (RouterFK = @ItemRouterFK) AND (ParentRouterFK = @RouterFK))
AND WorkOrderReleasePK NOT IN (SELECT WorkOrderReleasePK FROM #WorkOrderReleaseTempTable)
IF (@WorkOrderReleaseParentFoundID>0)
BEGIN
INSERT INTO #WorkOrderReleaseTempTable (WorkOrderReleasePK) values (@WorkOrderReleaseParentFoundID)
END
END
ELSE
BEGIN
SET @CurrentQuantityRequired = @RouterWorkCenterQuantityRequired*@QuantityToFabricate;
SET @CurrentQuantityToPull = 0
SET @CurrentQuantityToFabricate = 0
SET @CurrentQuantityHardware = 0
SET @CurrentQuantityMaterial = 0
SET @CurrentQuantityOutsideProcessing = 0
END
SET @PartNumber=(SELECT TOP 1 PartNumber FROM Item WHERE ItemPK = (SELECT TOP 1 ItemFK FROM Router WHERE RouterPK=@ItemRouterFK))
SET @WorkOrderAssemblyPK = (SELECT TOP 1 WorkOrderAssemblyPK FROM WorkOrderAssembly WHERE (WorkOrderFK = @WorkOrderPK) AND (WorkOrderReleaseFK = @WorkOrderReleasePK) AND (RouterFK = @RouterFK) AND (ItemRouterFK = @ItemRouterFK))
END
ELSE
BEGIN
IF (@ItemFK IS NOT NULL)
BEGIN
--SET @CurrentQuantityToFabricate = 0
IF (@SetStatusToPullFromInventory IS NULL) OR (@SetStatusToPullFromInventory <> 1)
BEGIN
SET @CurrentQuantityToPull = 0
END
-- On update, if the Sequence Number as changed, try to find a match using a different sequence number then that on the router
-- The issue is if you have the same item multiple times on the router. Material looks for specific stock size so that is OK.
-- If you have multiple hardware items make sure you put something in part size like a count to make it unique
/*
#1. Look for matching Item, Part Size, Blank Size and sequence. If found, do an UPDATE
#2. If not found, look for matching Item, Part Size and SequenceNumber>LastSequenceNumber.
We look at the SequenceNumber because we ordered the release by sequence number and if an
earlier sequence has the item already we want to keep it because this would be a new sequence.
If found, do an UPDATE because the item moved sequences.
#3. If not found, Look for matching Item and Sequence AND if only one item on the master router / sequence,
that means the part size may have changed and its an update.
#4. Lastly, Situation, if the item was originally released as sequence 11, and now the sequence is 12 we need to update.
This will look to see if there is exactly 1 matching item in the entier RouterWorKCenter. If there is then we do an update
*/
SET @WorkOrderAssemblyPK = (SELECT TOP 1 WorkOrderAssemblyPK FROM WorkOrderAssembly
WHERE (WorkOrderFK = @WorkOrderPK)
AND (WorkOrderReleaseFK = @WorkOrderReleasePK)
AND (RouterFK = @RouterFK)
AND (ItemFK = @ItemFK)
AND (ISNULL(PartWidth,0) = ISNULL(@PartWidth,0) AND ISNULL(PartLength,0) = ISNULL(@PartLength,0))
AND (ISNULL(BlankWidth,0) = ISNULL(@BlankWidth,0) AND ISNULL(BlankLength,0) = ISNULL(@BlankLength,0))
AND ((SequenceNumber IS NULL) OR ((SequenceNumber IS NOT NULL) AND (SequenceNumber = @SequenceNumber)))
AND (ISNULL(MarkNumber,'') = ISNULL(@MarkNumber,''))
)
IF (@WorkOrderAssemblyPK IS NULL)
BEGIN
SET @WorkOrderAssemblyPK = (SELECT TOP 1 WorkOrderAssemblyPK FROM WorkOrderAssembly
WHERE (WorkOrderFK = @WorkOrderPK)
AND (WorkOrderReleaseFK = @WorkOrderReleasePK)
AND (RouterFK = @RouterFK)
AND (ItemFK = @ItemFK)
AND ISNULL(SequenceNumber,0)>@LastSequenceNumber
AND (ISNULL(PartWidth,0) = ISNULL(@PartWidth,0) AND ISNULL(PartLength,0) = ISNULL(@PartLength,0))
AND (ISNULL(BlankWidth,0) = ISNULL(@BlankWidth,0) AND ISNULL(BlankLength,0) = ISNULL(@BlankLength,0))
AND (ISNULL(MarkNumber,'') = ISNULL(@MarkNumber,''))
)
END
IF (@WorkOrderAssemblyPK IS NULL AND (SELECT COUNT(*) FROM RouterWorkCenter WHERE RouterFK=@RouterFK AND (SequenceNumber = @SequenceNumber) AND (ItemFK = @ItemFK) AND WorkCenterFK IS NULL )=1)
BEGIN
SET @WorkOrderAssemblyPK = (SELECT TOP 1 WorkOrderAssemblyPK FROM WorkOrderAssembly
WHERE (WorkOrderFK = @WorkOrderPK)
AND (WorkOrderReleaseFK = @WorkOrderReleasePK)
AND (RouterFK = @RouterFK)
AND (ItemFK = @ItemFK)
AND ((SequenceNumber IS NULL) OR ((SequenceNumber IS NOT NULL) AND (SequenceNumber = @SequenceNumber)))
)
END
IF (@WorkOrderAssemblyPK IS NULL AND (SELECT COUNT(*) FROM RouterWorkCenter WHERE RouterFK=@RouterFK AND (ItemFK = @ItemFK) AND WorkCenterFK IS NULL AND (ISNULL(PartWidth,0) = ISNULL(@PartWidth,0) AND ISNULL(PartLength,0) = ISNULL(@PartLength,0)) )=1)
BEGIN
SET @WorkOrderAssemblyPK = (SELECT TOP 1 WorkOrderAssemblyPK FROM WorkOrderAssembly
WHERE (WorkOrderFK = @WorkOrderPK)
AND (WorkOrderReleaseFK = @WorkOrderReleasePK)
AND (RouterFK = @RouterFK)
AND (ItemFK = @ItemFK)
AND (ISNULL(PartWidth,0) = ISNULL(@PartWidth,0) AND ISNULL(PartLength,0) = ISNULL(@PartLength,0))
AND (ISNULL(BlankWidth,0) = ISNULL(@BlankWidth,0) AND ISNULL(BlankLength,0) = ISNULL(@BlankLength,0))
)
END
END
ELSE
BEGIN
SET @WorkOrderAssemblyPK = NULL
END
END
SET @LastSequenceNumber=ISNULL(@SequenceNumber,-1)
-- SPECIAL CASE!!
-- PRODUCT ATTACHMENTS USE
-- AND BOM ITEMS USE PartsRequired
-- In order to get the PartsRequired for Attachments we set it here
IF (@RouterWorkCenterQuantityRequired IS NOT NULL AND @ItemRouterFK IS NOT NULL)
BEGIN
SET @PartsRequired=@RouterWorkCenterQuantityRequired
END
-- Special case to update O/P Price based on the RouterWorkCenter breakouts
IF @ItemTypeFK=5 AND @QuantityToFabricate>0
BEGIN
IF ISNULL(@Quantity12,0)>0 AND ISNULL(@Price12,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity12,0)
BEGIN
SET @Price=@Price12
END
ELSE IF ISNULL(@Quantity11,0)>0 AND ISNULL(@Price11,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity11,0)
BEGIN
SET @Price=@Price11
END
ELSE IF ISNULL(@Quantity10,0)>0 AND ISNULL(@Price10,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity10,0)
BEGIN
SET @Price=@Price10
END
ELSE IF ISNULL(@Quantity9,0)>0 AND ISNULL(@Price9,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity9,0)
BEGIN
SET @Price=@Price9
END
ELSE IF ISNULL(@Quantity8,0)>0 AND ISNULL(@Price8,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity8,0)
BEGIN
SET @Price=@Price8
END
ELSE IF ISNULL(@Quantity7,0)>0 AND ISNULL(@Price7,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity7,0)
BEGIN
SET @Price=@Price7
END
ELSE IF ISNULL(@Quantity6,0)>0 AND ISNULL(@Price6,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity6,0)
BEGIN
SET @Price=@Price6
END
ELSE IF ISNULL(@Quantity5,0)>0 AND ISNULL(@Price5,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity5,0)
BEGIN
SET @Price=@Price5
END
ELSE IF ISNULL(@Quantity4,0)>0 AND ISNULL(@Price4,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity4,0)
BEGIN
SET @Price=@Price4
END
ELSE IF ISNULL(@Quantity3,0)>0 AND ISNULL(@Price3,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity3,0)
BEGIN
SET @Price=@Price3
END
ELSE IF ISNULL(@Quantity2,0)>0 AND ISNULL(@Price2,0)>0 AND @QuantityToFabricate>=ISNULL(@Quantity2,0)
BEGIN
SET @Price=@Price2
END
END
IF (@WorkOrderAssemblyPK IS NULL)
BEGIN
IF (@QuantityToFabricate <> 0)
BEGIN
INSERT INTO WorkOrderAssembly
(
WorkOrderFK,
RouterFK,
ParentRouterFK,
ItemRouterFK,
WorkCenterFK,
ItemFK,
OperationFK,
SupplierFK,
UnitOfMeasureSetFK,
DefaultWIPInventoryLocationFK,
UserFK,
WorkOrderAssemblyBOMStatusFK,
WorkOrderAssemblyLaborStatusFK,
WorkOrderReleaseFK,
PartNumber,
AgainstGrain,
BlankLength,
BlankWidth,
CertificationsRequired,
Color,
Comment,
DaysOut,
DoubleSided,
GoodUntil,
Instructions,
LagTime,
LeadTime,
MinimumPiecePrice,
Nestable,
NonAmortizedItem,
OveragePercentage,
CoveragePercentage,
Overlap,
PartWidth,
PartLength,
PartsPerBlank,
PartsPerBlankScrapPercentage,
PartsRequired,
PiecesPerHour,
PieceWeight,
Price,
ProductOrder,
QuantityPerInverse,
RunEmployees,
SafetyStockLength,
SafetyStockWidth,
SequenceNumber,
SetupEmployees,
SplitMachines,
StockLength,
StockWidth,
StockPieces,
StockPiecesScrapPercentage,
UnattendedPercentage,
StopSequence,
MinutesPerPart,
SetupTime,
StagingTime,
TransitTime,
TearDownTime,
Thickness,
Tolerance,
ToolingComment,
UnattendedOperation,
UseExactMaterialCalculation,
UserDefined1,
UserDefined2,
UserDefined3,
UserDefinedChar1,
UserDefinedChar2,
VendorUnit,
MarkNumber,
Revision,
WaitTime,
WeightFactor,
DropPercentage,
SetStatusToPullFromInventory,
QuantityRequired,
QuantityPull,
QuantityToFabricate,
QuantityOrdered,
QuantityIssued,
QuantityScrapped,
ProgramNumber,
Inspected,
IsAddOn,
IsEdited,
Released,
[BatchSize],
OrderBy,
LotPrice,
SetupCharge,
DoNotPrintBOM,
Minimum,
CommonSetupDescription
)
VALUES
(
@WorkOrderPK,
@RouterFK,
@ParentRouterFK,
@ItemRouterFK,
@WorkCenterFK,
@ItemFK,
@OperationFK,
@SupplierFK,
@UnitOfMeasureSetFK,
@DefaultWIPInventoryLocationFK,
@UserFK,
@WorkOrderAssemblyBOMStatusPK,
@WorkOrderAssemblyLaborStatusPK,
@WorkOrderReleasePK,
@PartNumber,
@AgainstGrain,
@BlankLength,
@BlankWidth,
@CertificationsRequired,
@Color,
@Comment,
@DaysOut,
@DoubleSided,
@GoodUntil,
@Instructions,
@LagTime,
@LeadTime,
@MinimumPiecePrice,
@Nestable,
@NonAmortizedItem,
@OveragePercentage,
@CoveragePercentage,
@Overlap,
@PartWidth,
@PartLength,
@PartsPerBlank,
@PartsPerBlankScrapPercentage,
@PartsRequired,
@PiecesPerHour,
@PieceWeight,
@Price,
@ProductOrder,
@QuantityPerInverse,
@RunEmployees,
@SafetyStockLength,
@SafetyStockWidth,
@SequenceNumber,
@SetupEmployees,
@SplitMachines,
@StockLength,
@StockWidth,
@StockPieces,
@StockPiecesScrapPercentage,
@UnattendedPercentage,
@StopSequence,
@MinutesPerPart,
@SetupTime,
@StagingTime,
@TransitTime,
@TearDownTime,
@Thickness,
@Tolerance,
@ToolingComment,
@UnattendedOperation,
@UseExactMaterialCalculation,
@UserDefined1,
@UserDefined2,
@UserDefined3,
@UserDefinedChar1,
@UserDefinedChar2,
@VendorUnit,
@MarkNumber,
@BOMRevision,
@WaitTime,
@WeightFactor,
@DropPercentage,
@SetStatusToPullFromInventory,
@CurrentQuantityRequired,
@CurrentQuantityToPull,
@CurrentQuantityToFabricate,
0,
0,
0,
@ProgramNumber,
0,
0,
0,
1,
@BatchSize,
@OrderBy,
@LotPrice,
@SetupCharge,
@DoNotPrintBOM,
@Minimum,
@CommonSetupDescription
)
END
END
ELSE
BEGIN
IF (@QuantityToFabricate <> 0)
BEGIN
UPDATE WorkOrderAssembly
SET
--WorkOrderFK = @WorkOrderFK,
--RouterFK = @RouterFK,
ParentRouterFK = @ParentRouterFK,
ItemRouterFK = @ItemRouterFK,
WorkCenterFK = @WorkCenterFK,
ItemFK = @ItemFK,
OperationFK = @OperationFK,
SupplierFK = @SupplierFK,
UnitOfMeasureSetFK = @UnitOfMeasureSetFK,
DefaultWIPInventoryLocationFK = @DefaultWIPInventoryLocationFK,
UserFK = @UserFK,
WorkOrderAssemblyBOMStatusFK = @WorkOrderAssemblyBOMStatusPK,
WorkOrderAssemblyLaborStatusFK = @WorkOrderAssemblyLaborStatusPK,
--WorkOrderReleaseFK = @WorkOrderReleasePK,
PartNumber = @PartNumber,
AgainstGrain = @AgainstGrain,
[BatchSize] = @BatchSize,
BlankLength = @BlankLength,
BlankWidth = @BlankWidth,
CertificationsRequired = @CertificationsRequired,
Color = @Color,
Comment = @Comment,
DaysOut = @DaysOut,
DoubleSided = @DoubleSided,
GoodUntil = @GoodUntil,
Instructions = @Instructions,
LagTime = @LagTime,
LeadTime = @LeadTime,
LotPrice = @LotPrice,
DoNotPrintBOM=@DoNotPrintBOM,
MinimumPiecePrice = @MinimumPiecePrice,
Nestable = @Nestable,
NonAmortizedItem = @NonAmortizedItem,
OveragePercentage = @OveragePercentage,
CoveragePercentage = @CoveragePercentage,
Overlap = @Overlap,
PartWidth = @PartWidth,
PartLength = @PartLength,
PartsPerBlank = @PartsPerBlank,
PartsPerBlankScrapPercentage = @PartsPerBlankScrapPercentage,
PartsRequired = @PartsRequired,
PiecesPerHour = @PiecesPerHour,
PieceWeight = @PieceWeight,
Price = @Price,
ProductOrder = @ProductOrder,
QuantityPerInverse = @QuantityPerInverse,
RunEmployees = @RunEmployees,
SafetyStockLength = @SafetyStockLength,
SafetyStockWidth = @SafetyStockWidth,
SequenceNumber = @SequenceNumber,
SetupCharge = @SetupCharge,
SetupEmployees = @SetupEmployees,
SplitMachines = @SplitMachines,
StockLength = @StockLength,
StockWidth = @StockWidth,
StockPieces = @StockPieces,
StockPiecesScrapPercentage = @StockPiecesScrapPercentage,
UnattendedPercentage = @UnattendedPercentage,
StopSequence = @StopSequence,
MinutesPerPart = @MinutesPerPart,
SetupTime = @SetupTime,
StagingTime = @StagingTime,
TransitTime = @TransitTime,
TearDownTime = @TearDownTime,
Thickness = @Thickness,
Tolerance = @Tolerance,
ToolingComment = @ToolingComment,
UnattendedOperation = @UnattendedOperation,
UseExactMaterialCalculation = @UseExactMaterialCalculation,
UserDefined1 = @UserDefined1,
UserDefined2 = @UserDefined2,
UserDefined3 = @UserDefined3,
UserDefinedChar1 = @UserDefinedChar1,
UserDefinedChar2 = @UserDefinedChar2,
VendorUnit = @VendorUnit,
MarkNumber = @MarkNumber,
Revision=@BOMRevision,
WaitTime = @WaitTime,
WeightFactor = @WeightFactor,
DropPercentage=@DropPercentage,
SetStatusToPullFromInventory = @SetStatusToPullFromInventory,
QuantityRequired = @CurrentQuantityRequired,
QuantityPull = @CurrentQuantityToPull,
QuantityToFabricate = @CurrentQuantityToFabricate,
QuantityOrdered = 0,
QuantityIssued = 0,
QuantityScrapped = 0,
ProgramNumber = @ProgramNumber,
Inspected = 0,
IsAddOn = 0,
IsEdited = 0,
OrderBy = OrderBy,
Released = (SELECT (CASE WHEN (ISNULL(Released,0)=0 AND ISNULL(IsNested,0)=0 AND ISNULL(DropFlag,0)=0) THEN 1 ELSE 0 END)),
Minimum = @Minimum,
CommonSetupDescription = @CommonSetupDescription
WHERE
(WorkOrderAssemblyPK = @WorkOrderAssemblyPK)
-- Update Released
-- UPDATE WorkOrderAssembly SET Released = 1 WHERE ((WorkOrderAssemblyPK = @WorkOrderAssemblyPK) AND (((Released IS NULL) OR ((Released IS NOT NULL) AND (Released <> 1))) AND ((IsNested IS NULL) OR ((IsNested IS NOT NULL) AND (IsNested = 0))) AND ((DropFlag IS NULL) OR ((DropFlag IS NOT NULL) AND (DropFlag = 0)))) AND ((IsAddOn IS NOT NULL) AND (IsAddOn = 1)))
/* DONE IN ABOVE STATEMENT FOR PERFORMANCE, NO NEED TO DO MANY TIMES
UPDATE WorkOrderAssembly SET Released = 1
WHERE ((WorkOrderAssemblyPK = @WorkOrderAssemblyPK)
AND ((Released IS NULL) OR (Released =0 ))
AND ((IsNested IS NULL) OR (IsNested =0 ))
AND ((DropFlag IS NULL) OR (DropFlag =0 ))
);
*/
END
ELSE
BEGIN
DELETE FROM WorkOrderAssembly WHERE (WorkOrderAssemblyPK = @WorkOrderAssemblyPK)
END
END
FETCH NEXT FROM SelectRouterWorkCenter_Cursor2
INTO
@ItemRouterFK,
@WorkCenterFK,
@ItemFK,
@OperationFK,
@SupplierFK,
@UnitOfMeasureSetFK,
@DefaultWIPInventoryLocationFK,
@UserFK,
@AgainstGrain,
@BlankLength,
@BlankWidth,
@CertificationsRequired,
@Color,
@Comment,
@DaysOut,
@DoubleSided,
@GoodUntil,
@Instructions,
@LagTime,
@LeadTime,
@MinimumPiecePrice,
@Nestable,
@NonAmortizedItem,
@OveragePercentage,
@CoveragePercentage,
@Overlap,
@PartWidth,
@PartLength,
@PartsPerBlank,
@PartsPerBlankScrapPercentage,
@PartsRequired,
@PiecesPerHour,
@PieceWeight,
@Price,
@Price2,
@Price3,
@Price4,
@Price5,
@Price6,
@Price7,
@Price8,
@Price9,
@Price10,
@Price11,
@Price12,
@Quantity1,
@Quantity2,
@Quantity3,
@Quantity4,
@Quantity5,
@Quantity6,
@Quantity7,
@Quantity8,
@Quantity9,
@Quantity10,
@Quantity11,
@Quantity12,
@ProductOrder,
@SetStatusToPullFromInventory,
@RouterWorkCenterQuantityRequired,
@QuantityPerInverse,
@RunEmployees,
@SafetyStockLength,
@SafetyStockWidth,
@SequenceNumber,
@SetupEmployees,
@SplitMachines,
@StockLength,
@StockWidth,
@StockPieces,
@StockPiecesScrapPercentage,
@UnattendedPercentage,
@StopSequence,
@MinutesPerPart,
@SetupTime,
@StagingTime,
@TransitTime,
@TearDownTime,
@Thickness,
@Tolerance,
@ToolingComment,
@UnattendedOperation,
@UseExactMaterialCalculation,
@UserDefined1,
@UserDefined2,
@UserDefined3,
@UserDefinedChar1,
@UserDefinedChar2,
@VendorUnit,
@MarkNumber,
@WaitTime,
@WeightFactor,
@DropPercentage,
@ProgramNumber,
@BatchSize,
@OrderBy,
@LotPrice,
@SetupCharge,
@DoNotPrintBOM,
@Minimum,
@CommonSetupDescription
END
CLOSE SelectRouterWorkCenter_Cursor2
DEALLOCATE SelectRouterWorkCenter_Cursor2
-----------------
-- IF IsAddOn is set to TRUE then we need to set the sequence numbers to the max
-----------------
DECLARE @NextSequenceNumber int
SET @NextSequenceNumber = (SELECT MAX(SequenceNumber) FROM WorkOrderAssembly WHERE WorkOrderReleaseFK=@WorkOrderReleaseFK AND WorkCenterFK>0)
DECLARE SelectWorkOrderAssembly_Cursor CURSOR FAST_FORWARD LOCAL
FOR SELECT
WorkOrderAssemblyPK
FROM
WorkOrderAssembly
WHERE
(WorkOrderReleaseFK=@WorkOrderReleaseFK AND ISNULL(IsAddOn,0)=1 AND SequenceNumber IS NULL)
OPEN SelectWorkOrderAssembly_Cursor
FETCH NEXT FROM SelectWorkOrderAssembly_Cursor
INTO @WorkOrderAssemblyPK
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @NextSequenceNumber=@NextSequenceNumber+1
UPDATE WorkOrderAssembly SET SequenceNumber=@NextSequenceNumber WHERE WorkOrderAssemblyPK=@WorkOrderAssemblyPK
FETCH NEXT FROM SelectWorkOrderAssembly_Cursor
INTO @WorkOrderAssemblyPK
END
CLOSE SelectWorkOrderAssembly_Cursor
DEALLOCATE SelectWorkOrderAssembly_Cursor
UPDATE WorkOrderAssembly SET SequenceNumber=1 WHERE WorkOrderReleaseFK=@WorkOrderReleaseFK AND ItemFK>0 AND SequenceNumber IS NULL
--
-- LOOP THROUGH NEXT WORKORDERRELEASE
--
FETCH NEXT FROM SelectWorkOrderRelease_Cursor
INTO
@WorkOrderReleasePK,
@RouterFK,
@ParentRouterFK,
@QuantityRequired,
@QuantityToPull,
@QuantityToFabricate,
@QuantityHardware,
@QuantityMaterial,
@QuantityOutsideProcessing
END
CLOSE SelectWorkOrderRelease_Cursor
DEALLOCATE SelectWorkOrderRelease_Cursor
---------------------------------------------------------------------------
-- FINAL CASE IS WHERE WE NEED TO LOOK AT ALL THE WorkOrderRelease and
-- find the matching WorkOrderAssembly row. If no row, it was added
-- on the fly and we need to create the WorkOrderAssembly row to match
--
-- Part 2 we also
---------------------------------------------------------------------------
DECLARE SelectWorkOrderRelease_Cursor2 CURSOR FAST_FORWARD LOCAL
FOR SELECT
WorkOrderReleasePK,
RouterFK,
ParentRouterFK,
QuantityRequired,
QuantityToPull,
QuantityToFabricate,
QuantityHardware,
QuantityMaterial,
QuantityOutsideProcessing
FROM
WorkOrderRelease
WHERE
((WorkOrderFK = @WorkOrderPK) AND (ParentRouterFK>0))
OPEN SelectWorkOrderRelease_Cursor2
FETCH NEXT FROM SelectWorkOrderRelease_Cursor2
INTO
@WorkOrderReleasePK,
@RouterFK,
@ParentRouterFK,
@QuantityRequired2,
@QuantityToPull2,
@QuantityToFabricate2,
@QuantityHardware2,
@QuantityMaterial2,
@QuantityOutsideProcessing2
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @CurrentQuantityRequired = @QuantityRequired2
SET @CurrentQuantityToPull = @QuantityToPull2
SET @CurrentQuantityToFabricate = @QuantityToFabricate2
SET @CurrentQuantityHardware = @QuantityHardware2
SET @CurrentQuantityMaterial = @QuantityMaterial2
SET @CurrentQuantityOutsideProcessing = @QuantityOutsideProcessing2
-- NEED ParentWorkOrderReleasePK because the parent is where we attach the products.
SET @WorkOrderReleasePK=(SELECT TOP 1 WorkOrderReleasePK FROM WorkOrderRelease WHERE WorkOrderFK=@WorkOrderPK AND RouterFK=@ParentRouterFK);
SET @BOMRevision=''
-- RENUMBER SEQUENCES 1ST IF APPLICABLE BECAUSE OF AN ADD
-- $$$ NOT VERY FAST, SHOULD USE ROW FUNCTION
IF ((SELECT COUNT(*) FROM WorkOrderAssembly WHERE (WorkOrderFK = @WorkOrderPK) AND (WorkOrderReleaseFK = @WorkOrderReleasePK) AND (WorkCenterFK>0) AND SequenceNumber IS NULL)>0)
BEGIN
DECLARE @SeqCnt int
SET @SeqCnt=0
DECLARE SelectWorkOrderAssembly_Seq CURSOR LOCAL
FOR SELECT
SequenceNumber,
WorkOrderAssemblyPK
FROM
WorkOrderAssembly
WHERE
(WorkOrderFK = @WorkOrderPK) AND (WorkOrderReleaseFK = @WorkOrderReleasePK) AND (WorkCenterFK>0)
ORDER BY ISNULL(SequenceNumber,999)
OPEN SelectWorkOrderAssembly_Seq
FETCH NEXT FROM SelectWorkOrderAssembly_Seq
INTO
@SequenceNumber,
@WorkOrderAssemblyPK
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SeqCnt=@SeqCnt+1
IF @SequenceNumber IS NULL
BEGIN
UPDATE WorkOrderAssembly SET SequenceNumber=@SeqCnt WHERE WorkOrderAssemblyPK=@WorkOrderAssemblyPK
END
FETCH NEXT FROM SelectWorkOrderAssembly_Seq
INTO
@SequenceNumber,
@WorkOrderAssemblyPK
END
CLOSE SelectWorkOrderAssembly_Seq
DEALLOCATE SelectWorkOrderAssembly_Seq
END
-- This happens when ROUTER was added on the fly.
IF ((SELECT COUNT(*) FROM WorkOrderAssembly WHERE (WorkOrderFK = @WorkOrderPK) AND (WorkOrderReleaseFK = @WorkOrderReleasePK) AND (ItemRouterFK = @RouterFK))=0)
BEGIN
SET @ParentWorkOrderReleaseQuantityRequired=(SELECT QuantityRequired FROM WorkOrderRelease wor WHERE WorkOrderReleasePK=@WorkOrderReleasePK)
SET @WorkOrderAssemblyBOMStatusPK=7
SET @SequenceNumber=1 -- ### CHANGE THIS SO IT IS EDITABLE
SET @SetStatusToPullFromInventory=0
SET @PartsRequired=1
IF (@ParentWorkOrderReleaseQuantityRequired>0 AND @CurrentQuantityRequired>0)
BEGIN
SET @PartsRequired=CAST(@CurrentQuantityRequired/@ParentWorkOrderReleaseQuantityRequired AS int)
END
SET @PartNumber=(SELECT TOP 1 PartNumber FROM Item WHERE ItemPK = (SELECT TOP 1 ItemFK FROM Router WHERE RouterPK=@RouterFK))
SET @ItemRouterFK=@RouterFK
INSERT INTO WorkOrderAssembly
(
WorkOrderFK,
RouterFK,
ParentRouterFK,
ItemRouterFK,
WorkCenterFK,
ItemFK,
OperationFK,
SupplierFK,
UnitOfMeasureSetFK,
WorkOrderAssemblyBOMStatusFK,
WorkOrderAssemblyLaborStatusFK,
WorkOrderReleaseFK,
PartNumber,
AgainstGrain,
BlankLength,
BlankWidth,
CertificationsRequired,
Color,
Comment,
DaysOut,
DoubleSided,
GoodUntil,
Instructions,
LagTime,
LeadTime,
MinimumPiecePrice,
Nestable,
NonAmortizedItem,
OveragePercentage,
CoveragePercentage,
Overlap,
PartWidth,
PartLength,
PartsPerBlank,
PartsPerBlankScrapPercentage,
PartsRequired,
PiecesPerHour,
PieceWeight,
Price,
ProductOrder,
QuantityPerInverse,
RunEmployees,
SafetyStockLength,
SafetyStockWidth,
SequenceNumber,
SetupEmployees,
SplitMachines,
StockLength,
StockWidth,
StockPieces,
StockPiecesScrapPercentage,
UnattendedPercentage,
StopSequence,
MinutesPerPart,
SetupTime,
StagingTime,
TransitTime,
TearDownTime,
Thickness,
Tolerance,
ToolingComment,
UnattendedOperation,
UseExactMaterialCalculation,
UserDefined1,
UserDefined2,
UserDefined3,
UserDefinedChar1,
UserDefinedChar2,
VendorUnit,
MarkNumber,
Revision,
WaitTime,
WeightFactor,
SetStatusToPullFromInventory,
QuantityRequired,
TotalQuantityRequired,
QuantityPull,
QuantityToFabricate,
QuantityOrdered,
QuantityIssued,
QuantityScrapped,
Inspected,
IsAddOn,
IsEdited,
Released,
[BatchSize],
OrderBy,
LotPrice,
SetupCharge,
Minimum,
CommonSetupDescription
)
VALUES
(
@WorkOrderPK,
@ParentRouterFK,
NULL, -- @ParentRouterFK ?????
@ItemRouterFK,
NULL, -- @WorkCenterFK,
NULL, -- @ItemFK,
NULL, --@OperationFK,
NULL, --@SupplierFK,
NULL, --@UnitOfMeasureSetFK,
@WorkOrderAssemblyBOMStatusPK,
@WorkOrderAssemblyLaborStatusPK,
@WorkOrderReleasePK,
@PartNumber,
NULL, --@AgainstGrain,
NULL, --@BlankLength,
NULL, --@BlankWidth,
NULL, --@CertificationsRequired,
NULL, --@Color,
NULL, --@Comment,
NULL, --@DaysOut,
NULL, --@DoubleSided,
NULL, --@GoodUntil,
NULL, --@Instructions,
NULL, --@LagTime,
NULL, --@LeadTime,
NULL, --@MinimumPiecePrice,
NULL, --@Nestable,
NULL, --@NonAmortizedItem,
NULL, --@OveragePercentage,
NULL, --@CoveragePercentage,
NULL, --@Overlap,
NULL, --@PartWidth,
NULL, --@PartLength,
NULL, --@PartsPerBlank,
NULL, --@PartsPerBlankScrapPercentage,
@PartsRequired,
NULL, --@PiecesPerHour,
NULL, --@PieceWeight,
NULL, --@Price,
NULL, --@ProductOrder,
NULL, --@QuantityPerInverse,
NULL, --@RunEmployees,
NULL, --@SafetyStockLength,
NULL, --@SafetyStockWidth,
@SequenceNumber,
NULL, --@SetupEmployees,
NULL, --@SplitMachines,
NULL, --@StockLength,
NULL, --@StockWidth,
NULL, --@StockPieces,
NULL, --@StockPiecesScrapPercentage,
NULL, --@UnattendedPercentage,
NULL, --@StopSequence,
NULL, --@MinutesPerPart,
NULL, --@SetupTime,
NULL, --@StagingTime,
NULL, --@TransitTime,
NULL, --@TearDownTime,
NULL, --@Thickness,
NULL, --@Tolerance,
NULL, --@ToolingComment,
NULL, --@UnattendedOperation,
NULL, --@UseExactMaterialCalculation,
NULL, --@UserDefined1,
NULL, --@UserDefined2,
NULL, --@UserDefined3,
NULL, --@UserDefinedChar1,
NULL, --@UserDefinedChar2,
NULL, --@VendorUnit,
NULL, --@MarkNumber,
NULL, --@BOMRevision,
NULL, --@WaitTime,
NULL, --@WeightFactor,
@SetStatusToPullFromInventory,
@PartsRequired, --@CurrentQuantityRequired,
@CurrentQuantityRequired, --@CurrentQuantityRequired,
@CurrentQuantityToPull,
@CurrentQuantityToFabricate,
0,
0,
0,
0,
0,
0,
1,
@BatchSize,
@OrderBy,
@LotPrice,
@SetupCharge,
@Minimum,
@CommonSetupDescription
)
END
FETCH NEXT FROM SelectWorkOrderRelease_Cursor2
INTO
@WorkOrderReleasePK,
@RouterFK,
@ParentRouterFK,
@QuantityRequired2,
@QuantityToPull2,
@QuantityToFabricate2,
@QuantityHardware2,
@QuantityMaterial2,
@QuantityOutsideProcessing2
END
CLOSE SelectWorkOrderRelease_Cursor2
DEALLOCATE SelectWorkOrderRelease_Cursor2
---------------------------------------------------------------------------
-- CLEAN WorkOrderAssembly ItemRouterFK when it should not be listed
-- SPECIAL CASE - IF THIS ItemRouter WorkOrderRelease does not exist, the user does not want this router in their system as a FAB or PULL
-- THEREFORE we do not include it here. You get in this situation if you put a -1 in the Quantity Fab field on work order release
---------------------------------------------------------------------------
DELETE FROM WorkOrderAssembly
WHERE WorkOrderFK=@WorkOrderPK
AND ItemRouterFK>0
AND (SELECT COUNT(*) FROM WorkOrderRelease WHERE WorkOrderFK=@WorkOrderPK AND RouterFK=WorkOrderAssembly.ItemRouterFK)=0;
-----------------------------------------------------------
--SET THE WorkOrderAssemblySeqNumberFK Reference
-----------------------------------------------------------
DECLARE SetWorkOrderAssemblySeqNumber_Cursor2 CURSOR LOCAL
FOR SELECT
WorkOrderAssemblyPK,
ParentRouterFK,
SequenceNumber,
RouterFK,
WorkOrderReleaseFK
FROM
WorkOrderAssembly
WHERE
((WorkOrderFK = @WorkOrderPK) AND (WorkCenterFK IS NOT NULL))
OPEN SetWorkOrderAssemblySeqNumber_Cursor2
FETCH NEXT FROM SetWorkOrderAssemblySeqNumber_Cursor2
INTO
@WorkOrderAssemblyPK, @ParentRouterFK, @SequenceNumber, @RouterFK, @WorkOrderReleaseFK
WHILE (@@FETCH_STATUS = 0)
BEGIN
/* was
IF(@ParentRouterFK IS NULL)
BEGIN
UPDATE WorkOrderAssembly SET WorkOrderAssemblySeqNumberFK = @WorkOrderAssemblyPK WHERE WorkOrderFK = @WorkOrderPK AND WorkCenterFK IS NULL AND @SequenceNumber = SequenceNumber AND @ParentRouterFK IS NULL
END
ELSE
BEGIN
UPDATE WorkOrderAssembly SET WorkOrderAssemblySeqNumberFK = @WorkOrderAssemblyPK WHERE WorkOrderFK = @WorkOrderPK AND WorkCenterFK IS NULL AND @SequenceNumber = SequenceNumber AND @ParentRouterFK = ParentRouterFK
END
*/
UPDATE WorkOrderAssembly SET WorkOrderAssemblySeqNumberFK = @WorkOrderAssemblyPK WHERE WorkOrderReleaseFK=@WorkOrderReleaseFK AND WorkCenterFK IS NULL And SequenceNumber = @SequenceNumber
FETCH NEXT FROM SetWorkOrderAssemblySeqNumber_Cursor2
INTO
@WorkOrderAssemblyPK, @ParentRouterFK, @SequenceNumber, @RouterFK,@WorkOrderReleaseFK
END
CLOSE SetWorkOrderAssemblySeqNumber_Cursor2
DEALLOCATE SetWorkOrderAssemblySeqNumber_Cursor2
---------------------------------------------------------------------------
-- Update Released FOR IsAddOn because process above does not update it.
---------------------------------------------------------------------------
-- UPDATE WorkOrderAssembly SET Released = 1 WHERE ((WorkOrderFK = @WorkOrderPK) AND (((Released IS NULL) OR ((Released IS NOT NULL) AND (Released <> 1))) AND ((IsNested IS NULL) OR ((IsNested IS NOT NULL) AND (IsNested = 0))) AND ((DropFlag IS NULL) OR ((DropFlag IS NOT NULL) AND (DropFlag = 0)))) AND ((IsAddOn IS NOT NULL) AND (IsAddOn = 1)))
UPDATE WorkOrderAssembly SET Released = 1 WHERE ((WorkOrderFK = @WorkOrderPK) AND ISNULL(Released,0)=0 AND ISNULL(IsNested,0)=0 AND ISNULL(DropFlag,0)=0 )
UPDATE WorkOrderRelease SET Released = 1 WHERE ((WorkOrderFK = @WorkOrderPK) AND ISNULL(Released,0)=0)
SET @QuantityToFabricate = (SELECT TOP 1 QuantityToFabricate FROM WorkOrderRelease WHERE WorkOrderFK=@WorkOrderPK AND AssemblyLevel=0)
UPDATE WorkOrder SET ReleasedDate = GETDATE(), QuantityFab = ISNULL(@QuantityToFabricate,QuantityFab) WHERE (WorkOrderPK = @WorkOrderPK)
UPDATE WorkOrder SET InitialReleaseDate = ReleasedDate WHERE (WorkOrderPK = @WorkOrderPK) AND (InitialReleaseDate IS NULL)
-- Only UPDATE If not Upd
UPDATE SalesOrderLineLot SET ReleaseDate = GETDATE() WHERE SalesOrderLineLotPK IN (SELECT SalesOrderLineLotFK FROM WorkOrderJob WHERE WorkOrderFK=@WorkOrderPK) AND ReleaseDate IS NULL
UPDATE WorkOrder SET ReleaseProcessing = 0 WHERE (WorkOrderPK = @WorkOrderPK)
-- $$$ THIS MAY COST A LOT BECAUSE WE DO RECACULATIONS ON EACH ROW CHANGE
-- $$$ FIX THIS LATER TO BE FASTER
-- Final cleanup to fix disassociated entries if applicable.
UPDATE WorkOrderCollection
SET WorkOrderAssemblyNumber = (SELECT TOP 1 WorkOrderAssemblyPK FROM WorkOrderAssembly WHERE WorkOrderFK=WorkOrderCollection.WorkOrderNumber AND WorkOrderAssembly.RouterFK=WorkOrderCollection.RouterFK AND WorkOrderAssembly.WorkCenterFK=WorkOrderCollection.WorkCenterFK)
, SequenceNumber = (SELECT TOP 1 SequenceNumber FROM WorkOrderAssembly WHERE WorkOrderAssembly.RouterFK=WorkOrderCollection.RouterFK AND WorkOrderAssembly.WorkCenterFK=WorkOrderCollection.WorkCenterFK)
FROM WorkOrderCollection
where WorkOrderCollection.WorkOrderNumber=@WorkOrderPK
and WorkOrderAssemblyNumber IS NULL
AND (SELECT COUNT(*) FROM WorkOrderAssembly WHERE WorkOrderFK=WorkOrderCollection.WorkOrderNumber AND WorkOrderAssembly.RouterFK=WorkOrderCollection.RouterFK AND WorkOrderAssembly.WorkCenterFK=WorkOrderCollection.WorkCenterFK)=1
-- Final cleanup to fix disassociated entries if applicable.
UPDATE WorkOrderIssuing
SET WorkOrderAssemblyNumber = (SELECT TOP 1 WorkOrderAssemblyPK FROM WorkOrderAssembly WHERE WorkOrderFK=WorkOrderIssuing.WorkOrderNumber AND WorkOrderAssembly.RouterFK=WorkOrderIssuing.RouterFK AND WorkOrderAssembly.ItemFK=WorkOrderIssuing.ItemFK)
FROM WorkOrderIssuing
where WorkOrderIssuing.WorkOrderNumber=@WorkOrderPK
and WorkOrderAssemblyNumber IS NULL
AND (SELECT COUNT(*) FROM WorkOrderAssembly WHERE WorkOrderFK=WorkOrderIssuing.WorkOrderNumber AND WorkOrderAssembly.RouterFK=WorkOrderIssuing.RouterFK AND WorkOrderAssembly.ItemFK=WorkOrderIssuing.ItemFK)=1
-- Final cleanup to fix disassociated entries if applicable.
UPDATE WorkOrderIssuing
SET WorkOrderAssemblyNumber = (SELECT TOP 1 WorkOrderAssemblyPK FROM WorkOrderAssembly JOIN Router rt ON rt.RouterPK=WorkOrderAssembly.ItemRouterFK WHERE WorkOrderFK=WorkOrderIssuing.WorkOrderNumber AND WorkOrderAssembly.RouterFK=WorkOrderIssuing.RouterFK AND WorkOrderAssembly.ItemRouterFK IS NOT NULL AND rt.ItemFK=WorkOrderIssuing.ItemFK)
FROM WorkOrderIssuing
where WorkOrderIssuing.WorkOrderNumber=@WorkOrderPK
and WorkOrderAssemblyNumber IS NULL
AND (SELECT COUNT(*) FROM WorkOrderAssembly JOIN Router rt ON rt.RouterPK=WorkOrderAssembly.ItemRouterFK WHERE WorkOrderFK=WorkOrderIssuing.WorkOrderNumber AND WorkOrderAssembly.RouterFK=WorkOrderIssuing.RouterFK AND WorkOrderAssembly.ItemRouterFK IS NOT NULL AND rt.ItemFK=WorkOrderIssuing.ItemFK)=1
EXEC usp_CalculateWorkOrder @WorkOrderPK
DROP TABLE #WorkOrderReleaseTempTable
SET NOCOUNT OFF
END
--------------------------
Sorry, it would be better to save your code into a .sql file and attach instead. Please do that.
What does your classic asp code look like that access this?
For posting code, there is an icon to "style code" at the top of your comment box. Select all of your code, then click the code icon and your snippet will be placed in a code box. I made a quick edit for you on the previous post.
For posting code, there is an icon to "style code" at the top of your comment box. Select all of your code, then click the code icon and your snippet will be placed in a code box. I made a quick edit for you on the previous post.
The stored procedure doesn't know (or care) what language is calling it. It just takes the passed parameters and returns a result.
This is either a code issue, or a networking issue. If you can run the ASP.Net code successfully on the same machine the classic ASP code fails on, it's a code issue. If ASP.Net and classic ASP both fail on the same machine, it's probably a networking/connectivity issue.
"Yes I am able to connect to the db."
How do you know?
This is either a code issue, or a networking issue. If you can run the ASP.Net code successfully on the same machine the classic ASP code fails on, it's a code issue. If ASP.Net and classic ASP both fail on the same machine, it's probably a networking/connectivity issue.
"Yes I am able to connect to the db."
How do you know?
If your SP works in .NET or in Manger Studio, the issue is most likely how you are hitting it with vbscript in your asp code.
ASKER
ASKER
Paul MacDonald has right. We would need the ASP code where the error occurred instead.
Anyways in the procedure there is another procedure call (usp_CalculateWorkOrder) at the end however this procedure does not return any result to the ASP application (perhaps that usp_CalculateWorkOrder).
Anyways in the procedure there is another procedure call (usp_CalculateWorkOrder) at the end however this procedure does not return any result to the ASP application (perhaps that usp_CalculateWorkOrder).
ASKER
So the code for the asp page that is calling the procedure? And the code for the db connection?
ASKER
Or a link to the live page/URL where the procedure is called?
What we are looking for is code that may look something like https://www.experts-exchange.com/questions/28234393/vbscript-getting-a-recordset-back-from-a-stored-procedure.html?anchorAnswerId=39477170#a39477170
response.write(ALtest("abc123"))
Function ALtest(stockCode)
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
' Set up DB connection to use, set the type of SQL command
.ActiveConnection = MyConnectionString
.CommandType = adCmdStoredProc
.CommandText = "ALtest"
.Parameters.Append .CreateParameter("@p_StockCode",adVarChar, adParamInput,50)
.Parameters("@p_StockCode") = stockCode
set rs = .Execute
End With
ALtest= rs(0)
set cmd = nothing
set rs = nothing
end Function
"So the code for the asp page that is calling the procedure? And the code for the db connection?"
Yes. Scrub it of anything sensitive: Computer names, account names, passwords, etc.
Yes. Scrub it of anything sensitive: Computer names, account names, passwords, etc.
ASKER
And here is the actual page where it is run. The first button is the stored procedure I'm trying to get to work. The other two are test procedure calls to the database, and they work.
dmxConnect.asp
storedProcedureTest.asp
storedproceduretest.asp
dmxConnect.asp
storedProcedureTest.asp
storedproceduretest.asp
So this is actually a javascript issue, not classic ASP?
What line do you think/know your code breaks on?
What line do you think/know your code breaks on?
ASKER
I don't know which line breaks the code. I didn't write the Stored Procedure. I'm incorporating some data from our website into our manufacturing software database. How could I figure out which line breaks the code?
You suspect it breaks on a stored procedure. Where in the code is that stored procedure called?
ASKER
[code]<%@ LANGUAGE="JSCRIPT" CODEPAGE="65001"%>
<!--#include virtual="/dmxConnectLib/dmxConnect.asp" -->
<script runat="server" language="jscript">
require.setBaseUrl('/dmxConnectLib');
var app = require('lib/app');
app.define( //<<<'JSON'
{
"settings": {
"options": {}
},
"meta": {
"options": {}
},
"exec": {
"steps": [
"Connections/MieTrakSandbox",
{
"name": "storedProcedureTest",
"module": "dbupdater",
"action": "custom",
"options": {
"connection": "MieTrakSandbox",
"sql": {
"query": "EXEC usp_ReleaseWorkOrder @WorkOrderPK",
"params": [
{
"name": "@WorkOrderPK",
"value": "4875",
"test": "4875"
}
]
}
},
"output": true,
"meta": [],
"outputType": "array"
}
]
}
}
//JSON
);
</script>
[/code]
His code is produced using drag and drop from DMXZone.
Go back to the start of your question.
Which specific page is this happening on and which line number/line of code is it pointing to? Provide the code for that page. In the three pieces of code you provided, it looks like you are missing dmxAppConnect/dmxAppConnec t.js
What I would do at this point is create a brand new page and just use a very simple query like select field1 from mytable where field2=x. Just get that to work, then look at the all the code. Then move to this.
Go back to the start of your question.
Operation is not allowed when the object is closed
Which specific page is this happening on and which line number/line of code is it pointing to? Provide the code for that page. In the three pieces of code you provided, it looks like you are missing dmxAppConnect/dmxAppConnec
What I would do at this point is create a brand new page and just use a very simple query like select field1 from mytable where field2=x. Just get that to work, then look at the all the code. Then move to this.
Reading the docs, do you have included in your project?
https://www.dmxzone.com/go/33136/dmxzone-database-connector-2
https://www.dmxzone.com/go/23295/dmxzone-server-connect
https://www.dmxzone.com/go/32763/dmxzone-app-connect
Did you add your database location, name, username and password somewhere?
https://www.dmxzone.com/go/33136/dmxzone-database-connector-2
https://www.dmxzone.com/go/23295/dmxzone-server-connect
https://www.dmxzone.com/go/32763/dmxzone-app-connect
Did you add your database location, name, username and password somewhere?
ASKER
Did you add your database location, name, username and password somewhere?
ASKER
I just added the dmxAppConnect.js that has that information?
I don't know. I don't use dmx. That is a low-code software that allows you to build without much coding. The problem with these things is it can be harder to trace a problem.
If you don't remember adding a database location (localhost or ip or domain), database name, username and password anywhere, that may be the issue. If you are using dmx for .net and it works, you would most likely have to create another connection for classic asp.
What I would suggest doing is going through the documentation to create a simple connection and return some predefined data, like SELECT TOP 3 name FROM contacts or whatever simplex example they have. Just get that to work and that may help identify in this more complex app where you need to add the database.
If you don't remember adding a database location (localhost or ip or domain), database name, username and password anywhere, that may be the issue. If you are using dmx for .net and it works, you would most likely have to create another connection for classic asp.
What I would suggest doing is going through the documentation to create a simple connection and return some predefined data, like SELECT TOP 3 name FROM contacts or whatever simplex example they have. Just get that to work and that may help identify in this more complex app where you need to add the database.
ASKER
Yes I have done that and I am able to get some data. I just created a basic stored procedure that pulled some info and that worked with asp classic. I just don't know how to figure out which part of the amazingly long stored procedure that is causing the problem.
ASKER
I have an email into their tech support, so I am waiting to hear back from them. But thought I would try the experts here too.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is there a way to comment out, or will i have to just copy and paste the sections to the SP?
ASKER
Could it be a permissions thing? Like maybe the IIS .net user has rights and the asp classic user doesn't have rights? Like the asp user doesn't have permissions to run the stored procedure. I am able to run the two small SP's that I created, but it might have given them default rights of some sort versus when this other database was added it had different rights?
Yes, you can comment out by using an single quote. However, it is a short cut and there is an error somewhere. Taking the long way seems harder but I find it is always the solution. Starting off small and build.
I am mostly sure this has nothing to do with permissions unless by permissions you mean you are using a different database or same database with a different username/pass.
If you are using the same database and user, then double check your code that you are passing the correct information.
All I know to do with debugging classic asp code is to comment out code in classic asp and add a response.write "this is line xxx" so I know where it is, then another line, response.end. I think your issue is either the database connection issue or whatever data you are passing to the SP needs to be verified it is being passed and parsed.
I am mostly sure this has nothing to do with permissions unless by permissions you mean you are using a different database or same database with a different username/pass.
If you are using the same database and user, then double check your code that you are passing the correct information.
All I know to do with debugging classic asp code is to comment out code in classic asp and add a response.write "this is line xxx" so I know where it is, then another line, response.end. I think your issue is either the database connection issue or whatever data you are passing to the SP needs to be verified it is being passed and parsed.
One guess is to check your connection string for the classic asp side https://www.connectionstrings.com/
Which database is each? Oracle? MySql? MS SQL Server?
What code snippet works for .NET and what code snippet is broken in classic asp? Which exact line is the error pointing to?