Assisted Company Setup - Open Transactions - Open AR Invoices - Error - #StgInvcOverPmt_IND1

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