Error importing open AR invoices with Assisted Company Setup.
We had been getting an error on the creation of the #StgInvcOverPmt_IND1 so I added the following code the spMASAROpenInviceExt to prevent it from crashing.
Rich
USE [mas500_app]
GO
/****** Object: StoredProcedure [dbo].[spimReserveBlockInvtTran] Script Date: 12/01/2010 17:14:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spimReserveBlockInvtTran] (@oRetVal SMALLINT OUTPUT, @iReserveTranNo SMALLINT = 1)
AS
/****************************************************************************************************************
* Procedure Name: spimReserveBlockInvtTran
* Creation date: 10/09/2001
* Author: Gerard Tan-Torres
* Copyright: Copyright (c) 1995-2001 Best Software, Inc.
* Description: The purpose of the SP is to get a block of InvtTranKeys and create records in timInvtTranLog
* for the InvtTranKeys with a TranStatus = 1 (Pending).
*
* Assumptions: This SP assumes that a temp table called #InvtTranKeyList exists with the
* following columns:
* CREATE TABLE #InvtTranKeyList (
* TranIdentifier INTEGER NOT NULL, InvtTranKey INTEGER NULL,
* TranType INTEGER NOT NULL, CompanyID VARCHAR(3) NOT NULL,
* TranDate DATETIME NOT NULL, TranID VARCHAR(13) NULL,
* TranNo VARCHAR(10) NULL, TranQty DECIMAL(16,8) NOT NULL)
* Upon successful completion of the SP, the InvtTranKey column would be updated with a list
* of new InvtTranKeys where the InvtTranKey column is null.
*****************************************************************************************************************
* Parameters
* INPUT: <Optional> @iReserveTranNo
* Values : 0 --Don't reserve the TranNo, there is other process which will update the TranNo later on.
To avoid using up TranNos that will be overwritten.
* 1 --reserve the TranNo (default value)
* OUTPUT: @oRetVal = Return Value
*****************************************************************************************************************
* RETURN Codes
*
* 1 - Successful
* 0 - Unexpected Error (SP Failure)
* MessageNo - Represents the error message to display to the user by the UI.
*****************************************************************************************************************/
BEGIN
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#timBlockTranNo') IS NOT NULL
TRUNCATE TABLE #timBlockTranNo
ELSE
CREATE TABLE #timBlockTranNo (
TranKey INTEGER NOT NULL,
TranNo VARCHAR(10) NULL,
TranID VARCHAR(13) NULL)
IF OBJECT_ID('tempdb..#TranTypes') IS NOT NULL
TRUNCATE TABLE #TranTypes
ELSE
CREATE TABLE #TranTypes (
Counter INTEGER NOT NULL IDENTITY(1,1),
TranType INTEGER NOT NULL,
CompanyID VARCHAR(3) NOT NULL)
/* Declarations of Return Values. */
DECLARE
@SES_UNKNOWN_ERR INTEGER,
@SQL_SUCCESS INTEGER,
@SES_RET_SUCCESS INTEGER
/* Declarations of other parameters. */
DECLARE
@lNoOfKeys INTEGER,
@lKeyStart INTEGER,
@lKeyEnd INTEGER,
@lError INTEGER,
@lRowcount INTEGER,
@lProcessCounter INTEGER,
@lTranType INTEGER,
@lCompanyID VARCHAR(3),
@lRetVal INTEGER
/* Set the Values for the Return Value Constants. */
SELECT
@SES_UNKNOWN_ERR = -1,
@SQL_SUCCESS = 0,
@SES_RET_SUCCESS = 1
/* Initializations. */
SELECT @oRetVal = @SES_UNKNOWN_ERR -- Default to the absolute worst case.
/* Get the number of new InvtTranKeys to get. */
SELECT @lNoOfKeys = COUNT(*) FROM #InvtTranKeyList WHERE InvtTranKey IS NULL
IF @lNoOfKeys = 0
BEGIN
SELECT @oRetVal = @SES_RET_SUCCESS -- Success!
RETURN
END
/* Get a block of surrogate keys for the timInvtTranLog table */
EXEC spGetNextBlockSurrogateKey
'timInvtTranLog',
@lNoOfKeys,
@lKeyStart OUTPUT,
@lKeyEnd OUTPUT
IF (@lKeyStart IS NULL)
BEGIN
EXEC @oRetVal = spGetLocalMessageNo 'kmsgimspGeneralReserveInvtTran'
RETURN
END
/* Subtract 1 from the starting surrogate key so we don't waste one. */
SELECT @lKeyStart = @lKeyStart - 1
UPDATE #InvtTranKeyList
SET InvtTranKey = @lKeyStart,
@lKeyStart = @lKeyStart + 1
WHERE InvtTranKey IS NULL
/* Get the Tran Type ID in order to build the Tran ID */
UPDATE #InvtTranKeyList
SET TranID = LTRIM(itl.TranNo) + '-' + LTRIM(ttc.TranTypeID)
FROM #InvtTranKeyList itl WITH (NOLOCK)
JOIN tciTranTypeCompany ttc WITH (NOLOCK)
ON itl.TranType = ttc.TranType AND itl.CompanyID = ttc.CompanyID
WHERE itl.TranNo IS NOT NULL
-- Get new TranNos if needed.
IF @iReserveTranNo = 1
BEGIN
IF EXISTS (SELECT 1 FROM #InvtTranKeyList WHERE TranNo IS NULL)
BEGIN
INSERT #TranTypes (
TranType, CompanyID)
SELECT DISTINCT
TranType, CompanyID
FROM #InvtTranKeyList
SELECT @lProcessCounter = 0
WHILE 1 = 1
BEGIN
SELECT @lProcessCounter = MIN(Counter)
FROM #TranTypes
WHERE Counter > @lProcessCounter
IF @lProcessCounter IS NULL
BREAK
SELECT @lTranType = TranType,
@lCompanyID = CompanyID
FROM #TranTypes
WHERE Counter = @lProcessCounter
TRUNCATE TABLE #timBlockTranNo
INSERT #timBlockTranNo(
TranKey, TranNo, TranID)
SELECT
DISTINCT InvtTranKey, NULL, NULL
FROM #InvtTranKeyList
WHERE TranType = @lTranType
AND CompanyID = @lCompanyID
AND InvtTranKey IS NOT NULL
AND NULLIF(COALESCE(LTRIM(RTRIM(TranNo)), ''), '') IS NULL
EXEC spimGetNextBlockTranNo
@lCompanyID,
@lTranType,
@lRetVal OUTPUT
IF @lRetVal <> 0
RETURN
UPDATE itl
SET TranID = btn.TranID,
TranNo = btn.TranNo
FROM #InvtTranKeyList itl
JOIN #timBlockTranNo btn ON itl.InvtTranKey = btn.TranKey
END -- While Loop.
END -- End of getting new TranNos.
END
ELSE
BEGIN
--Set the TranID and TranNo to NULL if we don't want to reserve the TranNo
UPDATE #InvtTranKeyList
SET TranID = '', TranNo = ''
WHERE TranNo IS NULL
END
INSERT timInvtTranLog
(InvtTranKey, CompanyID, TranDate,
TranStatus, TranAmt, TranID,
TranNo, TranType, TranQty)
SELECT
tmp.InvtTranKey, tmp.CompanyID, tmp.TranDate,
1, 0, tmp.TranID,
tmp.TranNo, tmp.TranType, COALESCE(tmp.TranQty, 0)
FROM #InvtTranKeyList tmp
LEFT OUTER JOIN timInvtTranLog tl WITH (NOLOCK) ON tl.InvtTranKey = tmp.InvtTranKey
WHERE tl.InvtTranKey IS NULL
SELECT @lError = @@ERROR, @lRowcount = @@ROWCOUNT
IF (@lError <> 0) OR (@lRowcount = 0)
BEGIN
EXEC @oRetVal = spGetLocalMessageNo 'kmsgimspGeneralReserveInvtTran'
RETURN
END
/* Return Success. */
SELECT @oRetVal = @SES_RET_SUCCESS -- Success!
END
GO