| Description |
Steps:
# Execute the provided SQL script below.
Issue:
Unable to unpost cost adjustment transaction for WO-303
SQL:
BEGIN TRY
DECLARE @intWorkOrderId INT
,@GLEntries AS RecapTableType
,@idoc INT
,@ErrMsg NVARCHAR(MAX)
,@intUserId INT
,@intTransactionCount INT
,@intManufacturingProcessId INT
,@intTransaction INT
,@strTransactionId NVARCHAR(50)
,@strCostAdjustmentBatchId NVARCHAR(50)
,@strAttributeValue NVARCHAR(50)
,@intWorkOrderProducedLotTransactionId INT
,@intInventoryAdjustmentId INT
,@strAdjustmentNo NVARCHAR(50)
,@intTransactionId INT
,@strWorkOrderNo NVARCHAR(50)
,@strBatchId NVARCHAR(50)
,@intBatchId INT
,@intYieldCostId INT
,@strYieldCostValue NVARCHAR(50)
,@intLocationId INT
,@ItemsToReserve AS dbo.ItemReservationTableType
,@intInventoryTransactionType AS INT = 8
,@intInputItemId INT
,@intProductionStageLocationId INT
,@intProductionStagingId INT
,@intConsumptionStorageLocationId INT
,@intConsumptionSubLocationId INT
,@ItemsForPost AS ItemCostingTableType
,@dtmCurrentDateTime DATETIME
,@INVENTORY_CONSUME AS INT = 8
,@ACCOUNT_CATEGORY_TO_COUNTER_INVENTORY AS NVARCHAR(255) = 'Work In Progress'
,@STARTING_NUMBER_BATCH AS INT = 3
,@dblOtherCharges DECIMAL(38, 24)
,@dblProduceQty NUMERIC(38, 20)
,@ysnCostEnabled BIT
,@intWOItemUOMId INT
,@intUnitMeasureId INT
,@strXML NVARCHAR(max)
DECLARE @intReturnValue AS INT
DECLARE @unpostCostAdjustment AS ItemCostAdjustmentTableType
DECLARE @strBatchIdForUnpost AS NVARCHAR(50)
DECLARE @strErrorMessage AS NVARCHAR(4000)
SELECT TOP 1 @ysnCostEnabled = ysnCostEnabled
FROM tblMFCompanyPreference
EXEC sp_xml_preparedocument @idoc OUTPUT
,@strXML
SELECT @intWorkOrderId = intWorkOrderId
,@intUserId = intUserId
FROM OPENXML(@idoc, 'root', 2) WITH (
intWorkOrderId INT
,intUserId INT
)
SELECT @intWorkOrderId = 303
,@intUserId = 1
SELECT @intManufacturingProcessId = intManufacturingProcessId
,@strCostAdjustmentBatchId = strCostAdjustmentBatchId
,@intLocationId = intLocationId
,@intWOItemUOMId = intItemUOMId
,@strWorkOrderNo = strWorkOrderNo
FROM tblMFWorkOrder
WHERE intWorkOrderId = @intWorkOrderId
SELECT @intUnitMeasureId = intUnitMeasureId
FROM tblICItemUOM
WHERE intItemUOMId = @intWOItemUOMId
SELECT @strAttributeValue = strAttributeValue
FROM tblMFManufacturingProcessAttribute
WHERE intManufacturingProcessId = @intManufacturingProcessId
AND intAttributeId = 20 --Is Instant Consumption
AND intLocationId = @intLocationId
SELECT @dblProduceQty = SUM(dbo.fnMFConvertQuantityToTargetItemUOM(WP.intItemUOMId, IsNULL(IU.intItemUOMId, WP.intItemUOMId), WP.dblQuantity))
FROM dbo.tblMFWorkOrderProducedLot WP
LEFT JOIN dbo.tblICItemUOM IU ON IU.intItemId = WP.intItemId
AND IU.intUnitMeasureId = @intUnitMeasureId
WHERE WP.intWorkOrderId = @intWorkOrderId
AND WP.ysnProductionReversed = 0
AND WP.intItemId IN (
SELECT intItemId
FROM dbo.tblMFWorkOrderRecipeItem
WHERE intRecipeItemTypeId = 2
AND ysnConsumptionRequired = 1
AND intWorkOrderId = @intWorkOrderId
)
SELECT @intTransactionCount = @@TRANCOUNT
SELECT @dtmCurrentDateTime = Getdate()
IF @intTransactionCount = 0
BEGIN TRANSACTION
IF @strCostAdjustmentBatchId IS NOT NULL
BEGIN
DECLARE @adjustedEntries AS ItemCostAdjustmentTableType
DECLARE @dblNewCost NUMERIC(38, 20)
,@dblNewUnitCost NUMERIC(38, 20)
,@userId INT
,@intWorkOrderProducedLotId INT
,@dblOtherCost NUMERIC(18, 6)
SELECT @dblOtherCost = 0
SELECT @intTransactionId = intBatchId
,@strBatchId = strBatchId
FROM tblMFWorkOrderConsumedLot
WHERE intWorkOrderId = @intWorkOrderId
SELECT @dblNewCost = SUM([dbo].[fnMFGetTotalStockValueFromTransactionBatch](DT.intBatchId, DT.strBatchId))
FROM (
SELECT DISTINCT intBatchId
,strBatchId
FROM tblMFWorkOrderConsumedLot
WHERE intWorkOrderId = @intWorkOrderId
) AS DT
SELECT @intWorkOrderProducedLotId = MIN(intWorkOrderProducedLotId)
FROM tblMFWorkOrderProducedLot PL
WHERE intWorkOrderId = @intWorkOrderId
AND PL.ysnProductionReversed = 0
AND PL.intItemId IN (
SELECT RI.intItemId
FROM dbo.tblMFWorkOrderRecipeItem RI
WHERE RI.intRecipeItemTypeId = 2
AND RI.ysnConsumptionRequired = 1
AND RI.intWorkOrderId = @intWorkOrderId
)
WHILE @intWorkOrderProducedLotId IS NOT NULL
BEGIN
SELECT @intTransactionId = NULL
,@strBatchId = NULL
SELECT @intTransactionId = PL.intBatchId
,@strBatchId = PL.strBatchId
FROM tblMFWorkOrderProducedLot PL
WHERE intWorkOrderProducedLotId = @intWorkOrderProducedLotId
SELECT @dblOtherCost = @dblOtherCost + ISNULL([dbo].[fnMFGetTotalStockValueFromTransactionBatch](@intTransactionId, @strBatchId), 0)
SELECT @intWorkOrderProducedLotId = MIN(intWorkOrderProducedLotId)
FROM tblMFWorkOrderProducedLot PL
WHERE intWorkOrderId = @intWorkOrderId
AND PL.ysnProductionReversed = 0
AND PL.intItemId IN (
SELECT RI.intItemId
FROM dbo.tblMFWorkOrderRecipeItem RI
WHERE RI.intRecipeItemTypeId = 2
AND RI.ysnConsumptionRequired = 1
AND RI.intWorkOrderId = @intWorkOrderId
)
AND intWorkOrderProducedLotId > @intWorkOrderProducedLotId
END
SELECT @dblOtherCharges = SUM(dblOtherCharges)
FROM tblMFWorkOrderProducedLot
WHERE intWorkOrderId = @intWorkOrderId
AND ysnProductionReversed = 0
IF @dblOtherCharges IS NOT NULL
BEGIN
SELECT @dblOtherCost = abs(@dblOtherCost) + @dblOtherCharges
END
SET @dblNewCost = ABS(@dblNewCost) + ISNULL(@dblOtherCost, 0)
SET @dblNewUnitCost = ABS(@dblNewCost) / @dblProduceQty
EXEC dbo.uspMFGeneratePatternId @intCategoryId = NULL
,@intItemId = NULL
,@intManufacturingId = NULL
,@intSubLocationId = NULL
,@intLocationId = @intLocationId
,@intOrderTypeId = NULL
,@intBlendRequirementId = NULL
,@intPatternCode = 33
,@ysnProposed = 0
,@strPatternString = @intBatchId OUTPUT
-- Get a new batch id to unpost the cost adjustment.
EXEC uspSMGetStartingNumber 3
,@strBatchIdForUnpost OUT
INSERT INTO @unpostCostAdjustment (
[intItemId]
,[intItemLocationId]
,[intItemUOMId]
,[dtmDate]
,[dblQty]
,[dblUOMQty]
,[intCostUOMId]
,[dblNewValue]
,[intCurrencyId]
,[intTransactionId]
,[intTransactionDetailId]
,[strTransactionId]
,[intTransactionTypeId]
,[intLotId]
,[intSubLocationId]
,[intStorageLocationId]
,[ysnIsStorage]
,[strActualCostId]
,[intSourceTransactionId]
,[intSourceTransactionDetailId]
,[strSourceTransactionId]
,intFobPointId
,dblVoucherCost
)
SELECT t.[intItemId]
,[intItemLocationId]
,t.[intItemUOMId]
,t.[dtmDate]
,[dblQty]
,[dblUOMQty]
,[intCostUOMId] = t.[intItemUOMId]
,[dblNewValue] = t.dblValue
,[intCurrencyId]
,[intTransactionId] = t.intRelatedTransactionId
,[intTransactionDetailId] = t.intTransactionDetailId
,[strTransactionId]
,[intTransactionTypeId] = 9
,t.[intLotId]
,t.[intSubLocationId]
,t.[intStorageLocationId]
,[ysnIsStorage] = 0
,[strActualCostId]
,[intSourceTransactionId] = t.intRelatedTransactionId
,[intSourceTransactionDetailId] = t.intTransactionDetailId --t.intTransactionDetailId
,[strSourceTransactionId] = t.strTransactionId
,intFobPointId
,dblVoucherCost = NULL
FROM tblICInventoryTransaction t
WHERE t.strBatchId = @strCostAdjustmentBatchId
AND t.ysnIsUnposted = 0
AND t.intTransactionTypeId = 26
AND t.strTransactionId = t.strRelatedTransactionId
AND t.strTransactionId = @strWorkOrderNo
SELECT *
FROM @unpostCostAdjustment
EXEC @intReturnValue = uspICPostCostAdjustment @ItemsToAdjust = @unpostCostAdjustment
,@strBatchId = @strBatchIdForUnpost
,@intEntityUserSecurityId = @userId
,@ysnPost = 0
IF @intReturnValue <> 0
BEGIN
SELECT TOP 1 @strErrorMessage = strMessage
FROM tblICPostResult
WHERE strBatchNumber = @strBatchIdForUnpost
RAISERROR (
@strErrorMessage
,11
,1
);
END
INSERT INTO @GLEntries (
dtmDate
,strBatchId
,intAccountId
,dblDebit
,dblCredit
,dblDebitUnit
,dblCreditUnit
,strDescription
,strCode
,strReference
,intCurrencyId
-- ,intCurrencyExchangeRateTypeId
,dblExchangeRate
,dtmDateEntered
,dtmTransactionDate
,strJournalLineDescription
,intJournalLineNo
,ysnIsUnposted
,intUserId
,intEntityId
,strTransactionId
,intTransactionId
,strTransactionType
,strTransactionForm
,strModuleName
,intConcurrencyId
,dblDebitForeign
,dblDebitReport
,dblCreditForeign
,dblCreditReport
,dblReportingRate
,dblForeignRate
)
EXEC dbo.uspICCreateGLEntriesOnCostAdjustment @strBatchId = @strBatchIdForUnpost
,@intEntityUserSecurityId = @intUserId
,@strGLDescription = ''
,@ysnPost = 0
,@AccountCategory_Cost_Adjustment = 'Work In Progress'
-- Flag it as unposted.
UPDATE @GLEntries
SET ysnIsUnposted = 1
IF EXISTS (
SELECT TOP 1 1
FROM @GLEntries
)
BEGIN
EXEC uspGLBookEntries @GLEntries
,1
END
END
IF @intTransactionCount = 0
COMMIT TRANSACTION
EXEC sp_xml_removedocument @idoc
END TRY
BEGIN CATCH
SET @ErrMsg = ERROR_MESSAGE()
IF XACT_STATE() != 0
AND @intTransactionCount = 0
ROLLBACK TRANSACTION
IF @idoc <> 0
EXEC sp_xml_removedocument @idoc
RAISERROR (
@ErrMsg
,16
,1
,'WITH NOWAIT'
)
END CATCH
Error:
!pastedImage_d110924_0.png! back up file
[ftp://ftp.i21server.com:2121/Databases/Zeeland/] cresto01_01182019_Prem
Acceptance: Should be able to unpost work order.
|