[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1337
  • Last Modified:

Finding statement causing error in stored procedure with profiler

Hi,

  Getting  the following error in a stored procedure:

"Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated. The statement has been terminated."

  Problem is, it's a *huge* stored procedure and I can't pin down where the error is occuring.  I've tried using profiler to find what statement in the stored procedure is causing the error, but it seems I either get too much info or not enough in the trace.

  Can someone please suggest what I should be looking for and what I should be tracing to figure this out (a script for a trace template would be fantastic).

Thanks,
Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)
Asked:
Jim Dettman (Microsoft MVP/ EE MVE)
  • 4
  • 4
  • 3
  • +1
1 Solution
 
rafranciscoCommented:
You should be looking for an insert or an update where the value being stored in a column is longer than what the table can accommodate.
0
 
Scott PletcherSenior DBACommented:
And the "Line 1" indicates that it should be the first, or very nearly the first, statement in the procedure.  SQL's lines numbers are not 100% in sync with what humans consider line numbers, but it's usually close enough to help narrow down the statement.
0
 
Anthony PerkinsCommented:
The best way is to copy the code from the stored procedure and paste it in SQL Query Analyzer.  Make any changes necessary and run it from there.  THis will give you the exact line.

Another alternative is to use the SQL-Debugger.  But you will need SYSADMIN permissions and you should not debug on a Production server.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentAuthor Commented:
 I guess I should have been a bit clearer.  I want to know within profiler specifically what things I should be setting to trace this through.

<<You should be looking for an insert or an update where the value being stored in a column is longer than what the table can accommodate.>>

  I already know that it's an insert or udpate that's placing data into a field that is to short, but this stored procedure has 30 or 40 inserts/updates within it (I didn't write it BTW).  I've gone through all of them by hand, but cannot find which field it is that is too short.  


<<And the "Line 1" indicates that it should be the first, or very nearly the first, statement in the procedure.  SQL's lines numbers are not 100% in sync with what humans consider line numbers, but it's usually close enough to help narrow down the statement.>>

  Already found that out.  The line number don't seem to relate to anything in the procedure.


<<The best way is to copy the code from the stored procedure and paste it in SQL Query Analyzer.  Make any changes necessary and run it from there.  THis will give you the exact line.>>

  Please be more specific about "Make any changes necessary".  I have no idea what needs to be changed at this point.  Also, how would I supply all the parameters?

<<Another alternative is to use the SQL-Debugger.  But you will need SYSADMIN permissions and you should not debug on a Production server.>>

  I tried the debugger, but get an error message that it's not available (I have sysadmin permissions) and there is no test server at this point.

Jim.
0
 
Anthony PerkinsCommented:
>>Please be more specific about "Make any changes necessary".<<
Sorry, not without seeing the stored procedure "signature":  Stored procedure header and parameters.

>>Also, how would I supply all the parameters?<<
How are you doing it now?  And what values are you supplying.

>>and there is no test server at this point.<<
Than SQL-Debugger should not be used, so this is a moot point.
0
 
Anthony PerkinsCommented:
Also, please maintain your abandoned questions.  Here are listed all your open questions:

1 03/11/2004 0 Comunicating with Experts in your TA  Open PE Adm
2 03/11/2004 0 Points given between experts - What do y...  Open PE Adm
3 03/13/2004 0 Expert Notify Question  Open Microsoft Access
4 03/13/2004 20 Expert Topic - Sample link for Expert No...  Open Microsoft Access
5 04/25/2004 50 Some Expert Topics for discussion.  Open Microsoft Access
6 04/15/2005 500 Finding statement causing error in store...  Open Microsoft SQL Server
7 05/30/2004 500 Difference between a query and view  Open FoxPro

Thanks.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentAuthor Commented:
<<>>Also, how would I supply all the parameters?<<
How are you doing it now?  And what values are you supplying.>>

  Since I can run it live, I just step throught he calling code until I hit the .execute.  The stored procedure runs, but I can't see inside it to determine where the error is.  It simply returns an error to my calling code that says it's wasn't sucessfull and here's why.

  When I researched this error message, I saw several comments that using profiler was an easy way to spot which statement was causing the error.  But my attempt with profiler either returned too little data or too much.  I don't have any real experience with profiler and am not sure what items I should be tracing in order to pin this down.

  Below is the SP.  As you can see, it's fairly extensive.  A recent change was made to the database to expand the TransID field for the sales orders from 8 to 9 characters.  Obviously, I missed a table somewhere, but I'm not sure where.  Somewhere in the process of running this SP, the insert/update fails.

Jim.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



/*-----------------------------------------------*/
ALTER  PROCEDURE dbo.qrySoTransPost
@UserID varchar(20),
@PostRun varchar (14),
@PostYear smallint,
@CurPd smallint,
@WrkStnDate datetime = null,
@PostWhat smallint,
@PostInvoice bit,
@PostCashRcpt bit,
@CurrPrec smallint = 2,
@NewBatchID varchar(6)='',
@gPrecUPrice smallint = 2,
@gPrecUCost smallint = 2,  
@BatchId varchar(6)='######',
@MsgLog0 varchar(30),
@MsgLog1 varchar(30),
@MsgLog2 varchar(30),
@MsgLog3 varchar(30),
@MsgLog4 varchar(30),
@MsgLog5 varchar(30),
@MsgLog6 varchar(30),
@MsgLog7 varchar(30),
@MsgLog8 varchar(30),
@MsgLog9 varchar(30),
@Descr1 varchar(30),
@Ref1 varchar(15),
@CompID varchar(3),
@BrYn bit=0,@GlYn bit=0,@InYn bit=0,@KitYn bit=0
AS
DECLARE @MonthPart varchar(2), @DayPart varchar(2),
  @WrkStnID varchar(20),
 @GlPostLog varchar(20),
 @RetCode smallint, @NumPd smallint
--from options
DECLARE @SalesDtlYn bit,@SalesSumYn bit,@PostAddnlDesc bit,@InvcFinch varchar(15),
 @CommYn bit,@BatchYn bit,
 @GlDetailYn bit,@GlAcctDisc varchar(40),@ApplyCreditsToOldest bit
DECLARE @CustID varchar(10), @PreCustID varchar(10),
 @InvcDate datetime, @InvcNum varchar(15), @SaleAmt decimal(20,10)
 
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 0, 'Initiate'
SET @WrkStnID = HOST_NAME()
SET @retCode=0
IF @WrkStnDate IS NULL SET @WrkStnDate = GetDate()
/** Delete log entries **/
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 1, 'Deleting Log tables'
DELETE tmpSoTransPostLogDtl WHERE UserID = @UserID AND WrkStnId = @WrkStnID
IF @@ERROR <> 0
 RETURN 1
DELETE tmpSoTransPostLogSum WHERE UserID = @UserID AND WrkStnId = @WrkStnID
IF @@ERROR <> 0
 RETURN 1
DELETE tmpSoTransPostLog WHERE UserID = @UserID AND WrkStnId = @WrkStnID
IF @@ERROR <> 0
 RETURN 1
DELETE tmpSoTransPostLog2 WHERE UserID = @UserID AND WrkStnId = @WrkStnID
IF @@ERROR <> 0
 RETURN 1
--get options
SELECT @BatchYn = BatchYn, @GlDetailYn = PostDtlYn,
 @SalesDtlYn = HistSalesDtlYn, @SalesSumYn = HistSalesSumYn,
 @PostAddnlDesc = AddlDescYn, @InvcFinch = InvcFinch,
 @CommYn = CommYn, @GlAcctDisc = GlAcctDisc,
 @ApplyCreditsToOldest= ApplyToOldestYn FROM tblSoOption
SELECT @MonthPart = LTRIM(STR(DATEPART(mm,@WrkStnDate)))
IF (DATALENGTH(@MonthPart) < 2)
 SELECT @MonthPart = '0' + @MonthPart
SELECT @DayPart = LTRIM(STR(DATEPART(dd,@WrkStnDate)))
IF (DATALENGTH(@DayPart) < 2)
 SELECT @DayPart = '0' + @DayPart
 
 /**select valid transactions**/
 SELECT DISTINCT h.TransID
  INTO #tmpTransID
  FROM (tblSoTransHeader h LEFT JOIN tblSoTransDetail D ON h.TransID = d.TransID)
   INNER JOIN tblSoTransBatchCs B On H.BatchID = B.BatchID
  WHERE  B.UserID = @UserID AND B.WrksTnID = @WrkStnID AND
   H.TransType IN (-1,1,4) AND ( D.QtyShipSell>0 OR D.QtyShipSell IS NULL)
 IF @@ERROR <> 0
  RETURN 1
 
DELETE FROM tblSoTransHeaderPost WHERE UserID=@UserID And WrkStnID=@WrkStnID
IF @@ERROR <> 0 RETURN 1
--DELETE FROM tblSoTransDetailPost WHERE UserID=@UserID And WrkStnID=@WrkStnID
IF @@ERROR <> 0 RETURN 1
 
INSERT INTO tblSoTransHeaderPost
    (TransId, UserID, WrkStnID, TransType, BatchId, LocId, CustId, CustLevel,
    ShipToID, ShipToName, ShipToAddr1, ShipToAddr2,
    ShipToCity, ShipToRegion, ShipToCountry, ShipToPostalCode,
    ShipVia, TermsCode, DistCode, InvcNum, InvcDate, TransDate,
    PODate, CustPONum, ShipNum, ReqShipDate, ActShipDate,
    Rep1Id, Rep1Pct, Rep2Id, Rep2Pct, TaxableYN, TaxOnFreight,
    TaxClassFreight, TaxClassMisc, TaxGrpID, TaxableSales,
    NonTaxableSales, SalesTax, Freight, Misc, TotCost,
    TotPmtAmt, CurrencyID, ExchRate, PickNum, PostDate,
    GLPeriod, FiscalYear, SumHistPeriod, PrintInvcStatus,
    PrintPickStatus, NetDueDate, DiscDueDate, DiscAmt,
    TaxAmtAdj, TaxAdj, TaxLocAdj, TaxClassAdj, TaxableSalesFgn,
    NonTaxableSalesFgn, SalesTaxFgn, FreightFgn, MiscFgn,
    TotCostFgn, TotPmtAmtFgn, TaxAmtAdjFgn,
 
/* Micro Services CGD 05/29/2002 - Added Next 1 Line */
    POType, DeptId, ShipToStoreId, ShipToAttn,
 

/* Micro Services CGD 07/02/2002 - EDI */
    EDIPO, EDIInvoice, SOHdrDisc,
    FOBPayMethod, FOBShipLocId, FOBDesc,
    BuyingParty, BuyerContact,
    CancelAfterDate, Routing,SOOrigin, OrderNotes,
    EDIToBeSent, EDISent, EDIManualOR, SalesType, LastUpdatedUser, LastUpdatedTS
    )
 
SELECT h.TransId, @UserID, @WrkStnID, TransType, BatchId, LocId, CustId, CustLevel,
    ShipToID, ShipToName, ShipToAddr1, ShipToAddr2,
    ShipToCity, ShipToRegion, ShipToCountry, ShipToPostalCode,
    ShipVia, TermsCode, DistCode, InvcNum, InvcDate, TransDate,
    PODate, CustPONum, ShipNum, ReqShipDate, ActShipDate,
    Rep1Id, Rep1Pct, Rep2Id, Rep2Pct,TaxableYN, TaxOnFreight,
    TaxClassFreight, TaxClassMisc, TaxGrpID, TaxableSales,
    NonTaxableSales, SalesTax, Freight,Misc, TotCost,
    TotPmtAmt, CurrencyID, ExchRate,
 PickNum, PostDate, GLPeriod, FiscalYear, SumHistPeriod, PrintInvcStatus,
    PrintPickStatus, NetDueDate, DiscDueDate, DiscAmt,
    TaxAmtAdj, TaxAdj, TaxLocAdj, TaxClassAdj, TaxableSalesFgn,
    NonTaxableSalesFgn, SalesTaxFgn, FreightFgn, MiscFgn,
    TotCostFgn, TotPmtAmtFgn, TaxAmtAdjFgn,
 
/* Micro Services CGD 05/29/2002 - Added Next 1 Line */
    h.POType, h.DeptId, h.ShipToStoreId, h.ShipToAttn,
 
/* Micro Services CGD 07/02/2002 - EDI */
    h.EDIPO, h.EDIInvoice, h.SOHdrDisc,
    h.FOBPayMethod, h.FOBShipLocId, h.FOBDesc,
    h.BuyingParty, h.BuyerContact,
    h.CancelAfterDate, h.Routing,h.SOOrigin, h.OrderNotes,
    h.EDIToBeSent, h.EDISent, h.EDIManualOR, h.SalesType, h.LastUpdatedUser, h.LastUpdatedTS
 
FROM tblSoTransHeader h INNER JOIN #tmpTransID t ON h.TransID=t.TransID
IF @@ERROR <> 0 RETURN 1
 
BEGIN TRANSACTION
 
IF (@PostWhat <> 0 AND @PostInvoice <> 0)
BEGIN
 
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 2, 'Update null invoice numbers'
 
 UPDATE tblSoTransHeaderPost
  SET InvcNum = @MonthPart + @DayPart + tblSoTransHeaderPost.TransID
  FROM tblSoTransHeaderPost
  WHERE InvcNum Is Null AND UserID = @UserID AND WrkStnId = @WrkStnId
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 2
 END
 /** Calculate Due Dates **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 3, 'Calculate Due Dates'
 SELECT @RetCode = 0
 EXEC @RetCode = qrySoCalcDueDate @CurrPrec, @UserID, @WrkStnID
 IF @RetCode <> 0 or @@ERROR <>0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 3
 END
END
 
CREATE TABLE #tmpTransCust (CustID varchar(10))
 Insert Into #tmpTransCust
 Select Distinct h.CustID
 FROM tblSoTransHeaderPost h WHERE UserID = @UserID AND WrkStnId = @WrkStnId
 
IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
BEGIN
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 4, 'Update invoice numbers in cash receipts'
 UPDATE tblArCashRcptDetail
 SET InvcNum = h.InvcNum
 FROM ((tblArCashRcptHeader C INNER JOIN tblSoTransHeaderPost H
   ON c.InvcTransID = h.TransId))
  INNER JOIN tblArCashRcptDetail
   ON c.RcptHeaderID = tblArCashRcptDetail.RcptHeaderID
 WHERE UserID = @UserID AND WrkStnId = @WrkStnId
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 4
 END
END
/** update inventory **/
if @InYn = 1
Begin
 select @retCode = 0
 EXEC @retCode = qrySoTransPostINUPD @UserID, @WrkStnID, @gPrecUPrice,
     @gPrecUCost, @CurrPrec, @CompID, @KitYn
 IF @retCode<>0 or @@ERROR <>0
 Begin
  rollback transaction
 return 1000 + @retCode
 End
End
--update kitting
if @KitYn = 1
begin
 set @retcode=0
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 2000, 'Update Kitting History...'
 EXEC @retCode = qrySoTransPost_KitHist @UserID, @WrkStnID
 IF @retCode<>0 or @@ERROR <>0
 Begin
  rollback transaction
  return 2000 + @retCode
 End
End
 
/** Update the OPEN INVOICE table (tblArOpenInvoice)**/
IF (@PostWhat <> 0 AND @PostInvoice <> 0)
BEGIN
 /** append Invoices and Credit Memos **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 6, 'Append Invoices and Credit Memos into Open Invoice table'
 INSERT tblArOpenInvoice
  (CustId, FiscalYear, GlPeriod, RecType, InvcNum, TransDate, DistCode, CurrencyId,
  ExchRate, Amt, AmtFgn, DiscAmt, DiscAmtFgn, NetDueDate,
  DiscDueDate)
 SELECT CustId, FiscalYear, GlPeriod, (CASE WHEN TransType > 0 THEN 1 ELSE -1 END), InvcNum, InvcDate, DistCode, CurrencyID,
  ExchRate, (TaxableSales+NonTaxableSales+SalesTax+TaxAmtAdj+Freight+Misc),
  (TaxableSalesFgn+NonTaxableSalesFgn+SalesTaxFgn+TaxAmtAdjFgn+FreightFgn+MiscFgn),
  DiscAmt, ROUND(CONVERT(decimal(20,10),DiscAmt*ExchRate),@CurrPrec), NetDueDate, DiscDueDate
 FROM tblSoTransHeaderPost h
 WHERE UserID = @UserID AND WrkStnId = @WrkStnId
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 6
 END
END
IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
BEGIN
 /** append Payments **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 7, 'Append Payments into Open Invoice table'
 INSERT tblArOpenInvoice (CustId, FiscalYear, GlPeriod, InvcNum, Amt, DiscAmt, DiscAmtFgn,
  AmtFgn, RecType, PmtMethodId, CheckNum, CurrencyId,
  ExchRate, TransDate, NetDueDate, DistCode )
 SELECT CustId, FiscalYear, GlPeriod, InvcNum, tblArCashRcptDetail.PmtAmt+Difference,
  Difference, DifferenceFgn,
  tblArCashRcptDetail.PmtAmtFgn+DifferenceFgn, -2,
  PmtMethodId, CheckNum, CurrencyID, ExchRate,
  tblArCashRcptHeader.PmtDate, tblArCashRcptHeader.PmtDate,
  DistCode
 FROM (tblArCashRcptHeader INNER JOIN tblSoTransBatchCs
  ON tblArCashRcptHeader.DepositID = tblSoTransBatchCs.BatchID)
  INNER JOIN tblArCashRcptDetail
  ON tblArCashRcptHeader.RcptHeaderID = tblArCashRcptDetail.RcptHeaderID
 WHERE tblArCashRcptHeader.CustId IS NOT NULL
  AND UserID = @UserID
  AND WrkStnId = @WrkStnID
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 7
 END
 /** append cc Company Invoices **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 8, 'Create Temp table for CC Company Invoices'
 SELECT tblArPmtMethod.CustId, tblArPmtMethod.PmtMethodID,
  tblArCashRcptDetail.DistCode,
  Sum(tblArCashRcptDetail.PmtAmt) SumOfPmtAmt,
  Sum(tblArCashRcptDetail.PmtAmtFgn) SumOfPmtAmtFgn,
  tblArCashRcptHeader.CurrencyID,
  tblArCashRcptHeader.ExchRate, GlPeriod, FiscalYear
 INTO #PostCCComp
 FROM tblArPmtMethod INNER JOIN ((tblSoTransBatchCs
  INNER JOIN tblArCashRcptHeader
   ON tblSoTransBatchCs.BatchID = tblArCashRcptHeader.DepositID)
  INNER JOIN tblArCashRcptDetail
   ON tblArCashRcptHeader.RcptHeaderID = tblArCashRcptDetail.RcptHeaderID)
  ON tblArPmtMethod.PmtMethodID = tblArCashRcptHeader.PmtMethodId
 WHERE tblArPmtMethod.PmtType=3
  AND UserID = @UserID
  AND WrkStnId = @WrkStnID
 GROUP BY tblArPmtMethod.CustId, tblArPmtMethod.PmtMethodID,
  tblArCashRcptDetail.DistCode, tblArCashRcptHeader.CurrencyID,
  tblArCashRcptHeader.ExchRate, FiscalYear, GlPeriod
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 8
 END
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 9, 'Append cc Company Invoices into Open Invoice table'
 INSERT tblArOpenInvoice (CustId, PmtMethodId, Amt, AmtFgn,
  CurrencyId, ExchRate, TransDate, NetDueDate, RecType,
  DistCode, InvcNum,FiscalYear, GlPeriod)
 SELECT #PostCCComp.CustId, #PostCCComp.PmtMethodID,
  #PostCCComp.SumOfPmtAmt, #PostCCComp.SumOfPmtAmtFgn,
 #PostCCComp.CurrencyID, #PostCCComp.ExchRate, @WrkStnDate,
  @WrkStnDate, 1, tblArCust.DistCode,
  'CC' + @MonthPart + @DayPart,FiscalYear, GlPeriod
 FROM #PostCCComp LEFT JOIN tblArCust
  ON #PostCCComp.CustId = tblArCust.CustId
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 9
 END
END
/**  Update Customer Master History (tblArCustHist) **/
IF (@PostWhat <> 0 AND @PostInvoice <> 0)
BEGIN
 /**create missing year/period **/
 SELECT th.CustId, p.GlYear, p.GlPeriod
 INTO #tmpPeriods
 FROM SYS..tblSmPeriodConversion p, tblSoTransHeaderPost th
 WHERE p.CompID = @CompID AND UserID = @UserID AND WrkStnId = @WrkStnID
 
 GROUP BY th.CustId, p.GlYear, p.GlPeriod
 
 INSERT INTO tblArCustHistDetail (CustID, FiscalYear, GlPeriod)
 SELECT t.CustId, t.GlYear, t.GlPeriod
 FROM #tmpPeriods t LEFT JOIN tblArCustHistDetail td
 ON t.CustId = td.CustId AND t.GlYear = td.FiscalYear AND t.GlPeriod = td.GlPeriod
 WHERE td.CustId IS NULL
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 10
 END
 
 SELECT CustID, FiscalYear, GlPeriod,
  SUM(convert(decimal(20,10),Sign(TransType))*(TaxableSales + NonTaxableSales)) Sales,
  SUM(convert(decimal(20,10),Sign(TransType)) * TotCost) COGS,
  SUM(convert(decimal(20,10),Sign(TransType)) * (TaxableSales + NonTaxableSales - TotCost)) Profit,
  SUM(CASE WHEN TransType > 0 THEN 1 ELSE 0 END) NumInvc
 INTO #tmpCustHistDet
 FROM tblSoTransHeaderPost H
 WHERE UserID = @UserID AND WrkStnId = @WrkStnId
 GROUP BY CustID, FiscalYear, GlPeriod
 
 
 UPDATE tblArCustHistDetail
 SET Sales = tblArCustHistDetail.Sales + t.Sales,
  Cogs = tblArCustHistDetail.Cogs + t.COGS,
  Profit = tblArCustHistDetail.Profit + t.Profit,
  NumInvc = tblArCustHistDetail.NumInvc + t.NumInvc
 FROM #tmpCustHistDet t
 INNER JOIN tblArCustHistDetail
 ON t.CustId = tblArCustHistDetail.CustId
 AND t.GLPeriod = tblArCustHistDetail.GLPeriod
 AND t.FiscalYear = tblArCustHistDetail.FiscalYear
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 11
 END
 
 /** Update last Invc #, Amt & Sale Date **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 12, 'Update last Invc #, Amt & Sale Date in Customer Master History table'
 SELECT @PreCustID = ''
 DECLARE curTransHdr CURSOR FOR
 SELECT CustID, InvcDate, InvcNum,
 TaxableSales+NonTaxableSales+Freight+Misc+SalesTax+TaxAmtAdj As SaleAmt
 FROM tblSoTransHeaderPost h
 WHERE h.TransType IN (1,4) AND UserID = @UserID AND WrkStnId = @WrkStnId
 Order By CustID, InvcDate DESC
 FOR READ ONLY
 OPEN curTransHdr
 FETCH NEXT FROM curTransHdr INTO @CustID, @InvcDate, @InvcNum, @SaleAmt
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
  IF (@CustID <> @PreCustID)
  BEGIN
   UPDATE tblArCustHist
   SET LastSaleInvc = @InvcNum, LastSaleDate = @InvcDate, LastSaleAmt = @SaleAmt
   WHERE CustID = @CustID
    AND (LastSaleDate IS NULL OR LastSaleDate < @InvcDate)
  END
  SELECT @PreCustID = @CustID
  FETCH NEXT FROM curTransHdr INTO @CustID, @InvcDate, @InvcNum, @SaleAmt
 END
 CLOSE curTransHdr
 DEALLOCATE curTransHdr  
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 12
 END
 
 /** Update first sale date (if is null) **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 13, 'Update first sale date in Customer Master History table'
 UPDATE tblArCustHist
 SET FirstSaleDate = InvcDate
 FROM (tblSoTransHeaderPost h INNER JOIN tblArCustHist
  ON h.CustId = tblArCustHist.CustId)
 WHERE tblArCustHist.FirstSaleDate Is Null
  AND h.TransType IN (1,4) AND UserID = @UserID AND WrkStnId = @WrkStnId
 
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 13
 END
END
IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
BEGIN
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 14, 'Update Payment History'
 UPDATE tblArCustHistDetail
 SET Pmt = tblArCustHistDetail.Pmt + d.PmtAmt,
  Disc = tblArCustHistDetail.Disc + [Difference],
  NumPmt = tblArCustHistDetail.NumPmt + Sign(d.PmtAmt)
 FROM tblSoTransBatchCs b INNER JOIN (tblArCustHistDetail
 INNER JOIN (tblArCashRcptHeader h INNER JOIN tblArCashRcptDetail d
 ON h.RcptHeaderID = d.RcptHeaderID)
 ON (tblArCustHistDetail.GLPeriod = h.GLPeriod)
 AND (tblArCustHistDetail.FiscalYear = h.FiscalYear)
 AND (tblArCustHistDetail.CustId = h.CustId))
 ON b.BatchID = h.DepositID
 WHERE b.UserID = @UserID AND b.WrkStnId = @WrkStnID
 
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 14
 END
 
 /* days to pay */
 UPDATE tblArCustHistDetail
 SET DaysToPay = tblArCustHistDetail.DaysToPay + CASE WHEN DateDiff(day,TransDate,PmtDate)>0 THEN DateDiff(day,TransDate,PmtDate) ELSE 0 END
 FROM tblArOpenInvoice o INNER JOIN (tblArCustHistDetail
 INNER JOIN (tblSoTransBatchCs b INNER JOIN (tblArCashRcptHeader h
 INNER JOIN tblArCashRcptDetail d ON h.RcptHeaderID = d.RcptHeaderID)
 ON b.BatchID = h.DepositID)
 ON tblArCustHistDetail.GLPeriod = h.GLPeriod
 AND tblArCustHistDetail.FiscalYear = h.FiscalYear
 AND tblArCustHistDetail.CustId = h.CustId)
 ON o.InvcNum = d.InvcNum AND o.CustId = h.CustId
 WHERE o.RecType = 1 AND o.Status < 4
  AND b.UserID = @UserID AND b.WrkStnId = @WrkStnID
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 15
 END
 
 SELECT crh.CustId, MAX(crh.PmtDate) MaxOfPmtDate,
  MAX(crh.CheckNum)  MaxOfCheckNum, SUM(crd.PmtAmt) SumOfPmtAmt
 INTO #Post2e
 FROM (tblArCashRcptHeader crh INNER JOIN tblArCashRcptDetail crd
  ON crh.RcptHeaderID = crd.RcptHeaderID)
  INNER JOIN tblSoTransBatchCs b ON crh.DepositID = b.BatchID
 WHERE UserID = @UserID AND WrkStnId = @WrkStnID
 GROUP BY crh.CustId
 HAVING Sum(crd.PmtAmt) > 0
 ORDER BY Max(crh.PmtDate)
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 16
 END
 
 /** Update last Check #, Pmt Date & Amount **/
 UPDATE tblArCustHist
 SET LastPayDate = MaxOfPmtDate,
  LastPayAmt = SumOfPmtAmt,
  LastPayCheckNum = MaxOfCheckNum
 FROM tblArCustHist INNER JOIN #Post2e ON tblArCustHist.CustId = #Post2e.CustId
 WHERE LastPayDate < MaxOfPmtDate OR LastPayDate IS NULL
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 19
 END
END
IF (@PostWhat <> 0 AND @PostInvoice <> 0)
BEGIN
 /** Update curr bucket for balfwrd customers **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 20,'Update curr bucket for balfwrd customers in Customer Master History table'
 UPDATE tblArCustHist
 SET CurAmtDue = CurAmtDue+(convert(decimal(20,10),SIGN(TransType))*
      (TaxableSales+NonTaxableSales+SalesTax+TaxAmtAdj+Freight+Misc))
 FROM ((tblSoTransHeaderPost h INNER JOIN tblArCust c
  ON h.CustId = c.CustId)
 INNER JOIN tblArCustHist
  ON c.CustId = tblArCustHist.CustId)
 WHERE c.AcctType=1 AND UserID = @UserID AND WrkStnId = @WrkStnId
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 20
 END
END
IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
BEGIN
/** Update balfwrd customers with pmts **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 21,'Update balfwrd customers with pmts in Customer Master History table'
 
 UPDATE tblArCustHist
 SET UnpaidFinch = UnpaidFinch-(CASE WHEN AgingPd=1
   THEN crd.PmtAmt+crd.Difference ELSE 0 END),
  CurAmtDue = CurAmtDue-(CASE WHEN AgingPd=6
   THEN crd.PmtAmt+crd.Difference ELSE 0 END),
  BalAge1 = BalAge1-(CASE WHEN AgingPd=5
   THEN crd.PmtAmt+crd.Difference ELSE 0 END),
  BalAge2 = BalAge2-(CASE WHEN AgingPd=4
   THEN crd.PmtAmt+crd.Difference ELSE 0 END),
  BalAge3 = BalAge3-(CASE WHEN AgingPd=3
   THEN crd.PmtAmt+crd.Difference ELSE 0 END),
  BalAge4 = BalAge4-(CASE WHEN AgingPd=0 OR AgingPd=2
   THEN crd.PmtAmt+crd.Difference ELSE 0 END)
 FROM (((tblArCust INNER JOIN tblArCashRcptHeader
  ON tblArCust.CustId = tblArCashRcptHeader.CustId)
 INNER JOIN tblSoTransBatchCs
  ON tblArCashRcptHeader.DepositID = tblSoTransBatchCs.BatchID)
 INNER JOIN tblArCustHist
  ON tblArCust.CustId = tblArCustHist.CustId)
 INNER JOIN tblArCashRcptDetail crd
  ON tblArCashRcptHeader.RcptHeaderID = crd.RcptHeaderID
 WHERE tblArCust.AcctType=1
  AND UserID = @UserID
  AND WrkStnId = @WrkStnID
 
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 21
 END
END
/** Do Aging **/
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 220,'Age customer balances'
SELECT @RetCode = 0
EXEC @RetCode = qryArAgeCust @UserID, @WrkStnID, @WrkStnDate, @InvcFinch, @ApplyCreditsToOldest, 'SO','','',@CurrPrec
IF @RetCode <> 0 or @@ERROR <>0
BEGIN
 ROLLBACK TRANSACTION
 RETURN 220 + @RetCode
END
/** update customer high balance **/
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 23,'Update customer high balance'
UPDATE tblArCustHist
SET HighBal = CurAmtDue+BalAge1+BalAge2+BalAge3+BalAge4-UnapplCredit
WHERE tblArCustHist.CustID IN (Select CustID FROM #tmpTransCust) AND
 (CurAmtDue+BalAge1+BalAge2+BalAge3+BalAge4-UnapplCredit>HighBal
  OR HighBal IS NULL)
IF @@ERROR <> 0
BEGIN
 ROLLBACK TRANSACTION
 RETURN 23
END
/**  Update Tax Location Detail (tblSmTaxLocDetail)**/
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 240,'Update Tax Locations'
SELECT @RetCode = 0
EXEC @RetCode = qrySoTransPostTax @UserID, @WrkStnID
IF @RetCode <> 0 or @@ERROR <>0
BEGIN
 ROLLBACK TRANSACTION
 RETURN 240 + @RetCode
END
/**  Update Methods of Payment (tblArPmtMethod)**/
IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
BEGIN
 /*create missing entries*/
 SELECT c.PmtMethodID, p.GlYear, p.GlPeriod
 INTO #tmpPmtPeriods
 FROM tblArPmtMethod c, SYS..tblSmPeriodConversion p
 WHERE p.CompID = @CompID
 
 INSERT INTO tblArPmtMethodDetail (PmtMethodID, FiscalYear, GlPeriod)
 SELECT t.PmtMethodID, t.GlYear, t.GlPeriod
 FROM #tmpPmtPeriods t LEFT JOIN tblArPmtMethodDetail
 ON t.PmtMethodID = tblArPmtMethodDetail.PmtMethodID
 AND t.GlYear = tblArPmtMethodDetail.FiscalYear
 AND t.GlPeriod = tblArPmtMethodDetail.GlPeriod
 WHERE tblArPmtMethodDetail.PmtMethodID IS NULL
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 24
 END
 
 SELECT h.PmtMethodID, h.FiscalYear, h.GlPeriod, Sum(d.PmtAmt) SumPmt
 INTO #tmpPmtMethod
 FROM tblSoTransBatchCs b INNER JOIN tblArCashRcptHeader h ON b.BatchID = h.DepositID
 INNER JOIN tblArCashRcptDetail d ON h.RcptHeaderID = d.RcptHeaderID
 WHERE UserID = @UserID AND WrkStnID = @WrkStnID
 GROUP BY h.PmtMethodID, h.FiscalYear, h.GlPeriod
 
 UPDATE tblArPmtMethodDetail
 SET Pmt = tblArPmtMethodDetail.Pmt + d.SumPmt
 FROM #tmpPmtMethod d INNER JOIN tblArPmtMethodDetail
 ON d.GLPeriod = tblArPmtMethodDetail.GLPeriod
 AND d.FiscalYear = tblArPmtMethodDetail.FiscalYear
 AND d.PmtMethodId = tblArPmtMethodDetail.PmtMethodID
 
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 25
 END
END
/**  Update HISTORY tables (tblArHistHeader & tblArHistDetail)**/
IF (@SalesDtlYn = 1)
BEGIN
 IF (@PostWhat <> 0 AND @PostInvoice <> 0)
 BEGIN
 /** Append header info to tblArHistHeader **/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 27,'Update History Header'
  INSERT INTO tblArHistHeader ( PostRun, TransId, TransType, BatchId, CustId, ShipToID,
  ShipToName, ShipToAddr1, ShipToAddr2, ShipToCity, ShipToRegion, ShipToCountry,
  ShipToPostalCode, ShipVia, TermsCode, TaxableYN, InvcNum, WhseId, OrderDate, ShipNum,
  ShipDate, InvcDate, Rep1Id, Rep1Pct, Rep2Id, Rep2Pct, TaxOnFreight, TaxClassFreight,
  TaxClassMisc, FiscalYear, GLPeriod, TaxGrpID, TaxSubtotal, TaxSubtotalFgn, NonTaxSubtotal,
  NonTaxSubtotalFgn, SalesTax, SalesTaxFgn, Freight, FreightFgn, Misc, MiscFgn, TotCost,
  TotCostFgn, TotPmtAmt, TotPmtAmtFgn, CustPONum, DistCode, CurrencyID, ExchRate,
  DiscDueDate, NetDueDate, DiscAmt, SumHistPeriod, TaxAmtAdj, TaxAmtAdjFgn, TaxAdj,
  TaxLocAdj, TaxClassAdj, CustLevel, PODate, ReqShipDate, PickNum, Source ,
 
/* Micro Services CGD 05/29/2002 - Added Next 1 Line */
  POType, DeptId, ShipToStoreId, ShipToAttn,
 
/* Micro Services CGD 07/02/2002 - EDI */
         EDIPO, EDIInvoice, SOHdrDisc,
         FOBPayMethod, FOBShipLocId, FOBDesc,
         BuyingParty, BuyerContact,
         CancelAfterDate, Routing, OrderNotes
  )
 
  SELECT @PostRun, th.TransId, (CASE WHEN th.TransType > 0 THEN 1 ELSE -1 END),
  th.BatchId, th.CustId, th.ShipToID, th.ShipToName, th.ShipToAddr1, th.ShipToAddr2,
  th.ShipToCity, th.ShipToRegion, th.ShipToCountry, th.ShipToPostalCode, th.ShipVia,
  th.TermsCode, th.TaxableYN, th.InvcNum, th.LocId, (CASE WHEN th.CustPONum IS NULL THEN th.TransDate
  ELSE th.PODate END), th.ShipNum, th.ActShipDate, th.InvcDate, th.Rep1Id, th.Rep1Pct,
  th.Rep2Id, th.Rep2Pct, th.TaxOnFreight, th.TaxClassFreight, th.TaxClassMisc, th.FiscalYear,
  th.GLPeriod, th.TaxGrpID, th.TaxableSales, th.TaxableSalesFgn, th.NonTaxableSales,
  th.NonTaxableSalesFgn, th.SalesTax+th.TaxAmtAdj, th.SalesTaxFgn+th.TaxAmtAdjFgn,
  th.Freight, th.FreightFgn, th.Misc, th.MiscFgn, th.TotCost, th.TotCostFgn, th.TotPmtAmt,
  th.TotPmtAmtFgn, th.CustPONum, th.DistCode, th.CurrencyID, th.ExchRate, th.DiscDueDate,
  th.NetDueDate, th.DiscAmt, th.SumHistPeriod, th.TaxAmtAdj, th.TaxAmtAdjFgn,
  th.TaxAdj, th.TaxLocAdj, th.TaxClassAdj, th.CustLevel, th.PODate, th.ReqShipDate,
  th.PickNum, 1,
 
/* Micro Services CGD 05/29/2002 - Added Next 1 Line */
  th.POType, th.DeptId, th.ShipToStoreId, th.ShipToAttn,
 
/* Micro Services CGD 07/02/2002 - EDI */
         th.EDIPO, th.EDIInvoice, th.SOHdrDisc,
         th.FOBPayMethod, th.FOBShipLocId, th.FOBDesc,
         th.BuyingParty, th.BuyerContact,
         th.CancelAfterDate, th.Routing, th.OrderNotes
 
  FROM tblSoTransHeaderPost th
  WHERE UserID = @UserID AND WrkStnId = @WrkStnId
 
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 27
  END
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 28,'Update History Detail'
  --skip kit components
  INSERT INTO tblArHistDetail ( PostRun, TransID, EntryNum, ItemJob, WhseId, PartId,
  PartType, JobId, PhaseId, JobCompleteYN, [Desc], AddnlDesc, CatId, TaxClass,
  AcctCode, GLAcctSales, GLAcctCOGS, GLAcctInv, QtyOrdSell, UnitsSell, UnitsBase,
  QtyShipSell, QtyShipBase, QtyBackordSell, PriceID, UnitPriceSell, UnitPriceSellFgn,
  UnitCostSell, UnitCostSellFgn, PromoID, ActShipDate, EffectiveRate, OrigOrderQty,
  BinNum, ConversionFactor, LottedYN, InItemYN, HistSeqNum, Kit ,
 
/* Micro Services CGD 05/29/2002 - Added Next 2 Lines. */
  AliasId, ReqShipDate, BusinessCodeId, PriorityCode,
  NonSalesId,
 
/* Micro Services CGD 07/02/2002 - EDI. */
  SoLineDisc, UnitPriceBase
  )
 
  SELECT @PostRun, td.TransID, td.EntryNum, td.ItemJob, td.LocId, td.ItemId,
  td.ItemType, td.JobId, td.PhaseId, td.JobCompleteYN, td.Descr,
  CASE WHEN @PostAddnlDesc = 1 THEN AddnlDescr END,
  td.CatId, td.TaxClass, td.AcctCode, td.GLAcctSales, td.GLAcctCOGS, td.GLAcctInv,
  td.QtyOrdSell, td.UnitsSell, td.UnitsBase, td.QtyShipSell, td.QtyShipBase,
  td.QtyBackordSell, td.PriceID, td.UnitPriceSell, td.UnitPriceSellFgn, td.UnitCostSell,
  td.UnitCostSellFgn, td.PromoID, td.ActShipDate, td.EffectiveRate, td.OrigOrderQty,
  td.BinNum, td.ConversionFactor, td.LottedYN, td.InItemYN, td.HistSeqNum, td.Kit,
 
/* Micro Services CGD 05/29/2002 - Added Next 2 Lines. */
  td.AliasId, td.ReqShipDate, td.BusinessCodeId, td.PriorityCode,
  td.NonSalesId,
 
/* Micro Services CGD 07/02/2002 - EDI. */
  td.SoLineDisc, td.UnitPriceBase
 

  FROM tblSoTransDetail td INNER JOIN #tmpTransID t
   ON td.TransId = t.TransID
  WHERE (td.QtyShipSell>0 or td.QtyShipSell<0) AND td.GrpId IS Null
 
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 28
  END
 
  IF @KitYn = 1
  INSERT INTO tblArHistKit ( PostRun, TransID, EntryNum, ItemJob, WhseId, PartId,
  PartType, JobId, PhaseId, JobCompleteYN, [Desc], AddnlDesc, CatId, TaxClass,
  AcctCode, GLAcctSales, GLAcctCOGS, GLAcctInv, QtyOrdSell, UnitsSell, UnitsBase,
  QtyShipSell, QtyShipBase, QtyBackordSell, PriceID, UnitPriceSell, UnitPriceSellFgn,
  UnitCostSell, UnitCostSellFgn, PromoID, ActShipDate, EffectiveRate, OrigOrderQty,
  BinNum, ConversionFactor, LottedYN, InItemYN, HistSeqNum, KitQty, GrpID )
  SELECT @PostRun, d.TransID, d.EntryNum, d.ItemJob, d.LocId, d.ItemId,
  d.ItemType, d.JobId, d.PhaseId, d.JobCompleteYN, d.Descr, d.AddnlDescr, d.CatId, d.TaxClass,
  d.AcctCode, d.GLAcctSales, d.GLAcctCOGS, d.GLAcctInv, d.QtyOrdSell, d.UnitsSell, d.UnitsBase,
  d.QtyShipSell, d.QtyShipBase, d.QtyBackordSell, d.PriceID, d.UnitPriceSell, d.UnitPriceSellFgn,
  d.UnitCostSell, d.UnitCostSellFgn, d.PromoID, d.ActShipDate, d.EffectiveRate, d.OrigOrderQty,
  d.BinNum, d.ConversionFactor, d.LottedYN, d.InItemYN, d.HistSeqNum, d.KitQty, d.GrpId
  FROM tblSoTransDetail d INNER JOIN #tmpTransID t
   ON d.TransId = t.TransID
  WHERE (d.QtyShipSell>0 or d.QtyShipSell<0) AND d.GrpId Is Not Null
 
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 280
  END
 
  --add lotted to hist; accepts kitted too
  INSERT INTO tblArHistLot
   (PostRun, TransId, EntryNum, SeqNum, ItemId, LocId, LotNum,
   QtyOrder, QtyFilled, QtyBkord, CostUnit, CostUnitFgn, HistSeqNum, Cmnt)
  SELECT @PostRun, l.TransId, l.EntryNum, l.SeqNum, l.ItemId, l.LocId, l.LotNum,
   l.QtyOrder, l.QtyFilled, l.QtyBkord, l.CostUnit, l.CostUnitFgn, l.HistSeqNum, l.Cmnt
  FROM (#tmpTransID t INNER JOIN tblSoTransLot l ON t.TransID = l.TransId)
 
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 29
  END
 
  --add serial items to history; accepts kitted too
  INSERT INTO tblArHistSer
   (PostRun, TransId, EntryNum, SeqNum, ItemId, LocId, LotNum,
   SerNum, CostUnit, PriceUnit, CostUnitFgn, PriceUnitFgn, HistSeqNum, Cmnt)
  SELECT @PostRun, s.TransId, s.EntryNum, s.SeqNum, s.ItemId, s.LocId, s.LotNum,
   s.SerNum, s.CostUnit, s.PriceUnit, s.CostUnitFgn, s.PriceUnitFgn, s.HistSeqNum, s.Cmnt
  FROM #tmpTransID t INNER JOIN tblSoTransSer s ON t.TransId = s.TransId
 
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 29
  END
 
  INSERT INTO tblArHistTax
   (PostRun, TransId, TaxLocID, TaxClass, [Level], TaxAmt, TaxAmtFgn,
   Taxable, TaxableFgn, NonTaxable, NonTaxableFgn, LiabilityAcct)
  SELECT @PostRun, x.TransId, TaxLocID, TaxClass, [Level], TaxAmt, TaxAmtFgn,
   Taxable, TaxableFgn, NonTaxable, NonTaxableFgn, LiabilityAcct
  FROM #tmpTransID t INNER JOIN tblSoTransTax x ON t.TransId = x.TransId
 
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 29
  END
 
 END
 
 IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
 BEGIN
 /** Append detail info to tblArHistPmt (skip cc comp) **/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 30,'Update Payment History'
  INSERT tblArHistPmt
   (CustId, InvcNum, TransId, CheckNum, CcNum, CcHolder,
   CcAuth, BankID, DepNum, PmtMethodId, CurrencyId,
   ExchRate, PmtDate, PmtAmt, DiffDisc, PmtAmtFgn,
   DiffDiscFgn, PmtType, GLPeriod, GLRecvAcct, Rep1Id,
   Rep2Id, DistCode, PostRun, FiscalYear)
  SELECT crh.CustId, crd.InvcNum, LTRIM(STR(crd.RcptDetailID)),
   crh.CheckNum, crh.CcNum, crh.CcHolder, crh.CcAuth,
   crh.BankID, crh.DepositID, crh.PmtMethodId,
   crh.CurrencyID, crh.ExchRate, crh.PmtDate,
   crd.PmtAmt, crd.Difference, crd.PmtAmtFgn,
   crd.DifferenceFgn,  
   (SELECT PmtType
   FROM tblArPmtMethod
   WHERE PmtMethodID=crh.PmtMethodID),
   crh.GLPeriod,
   CASE WHEN crh.CustId IS NULL
    THEN crh.GLAcct
   ELSE tblArDistCode.GLAcctReceivables END,
   tblArCust.SalesRepId1, tblArCust.SalesRepId2,
   crd.DistCode, @PostRun, crh.FiscalYear
  FROM (tblSoTransBatchCs INNER JOIN (tblArCashRcptHeader crh
   LEFT JOIN tblArCust
    ON crh.CustId = tblArCust.CustId)
    ON tblSoTransBatchCs.BatchID = crh.DepositID)
   INNER JOIN (tblArCashRcptDetail crd
   LEFT JOIN tblArDistCode
    ON crd.DistCode = tblArDistCode.DistCode)
    ON crh.RcptHeaderID = crd.RcptHeaderID
  WHERE UserID = @UserID
   AND WrkStnId = @WrkStnId
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 30
  END
 END
END
/** Update SUMMARY HISTORY Customer table (tblArSumHistCust)**/
IF (@SalesSumYn = 1)
BEGIN
 CREATE TABLE #SoSumHist
 (
 ArYear smallint NOT NULL,
 ArPeriod smallint NOT NULL
 )
 SELECT @NumPd = numsumhist FROM tblSoOption
 INSERT INTO #SoSumHist
 SELECT GlYear, GlPeriod FROM SYS..tblSmPeriodConversion
 WHERE CompID = @CompID AND GlPeriod <= @NumPd
 
 --create summary hist records for all periods for posting year
 INSERT INTO tblArSumHistCust (CustID, Year, Period, TotSales,
   TotCogs, TotDisc, NumInvc, TotDaysToPay,NumPmt,
   UnpaidFinch, CurAmtDue, BalAge1, BalAge2, BalAge3,
   BalAge4)
 SELECT h.CustID, t.ArYear, t.ArPeriod, 0,0,0,0,0,0,0,0,0,0,0,0
 FROM #tmpTransCust h, #SoSumHist t WHERE
  (t.ArPeriod NOT IN
  (SELECT Period FROM tblArSumHistCust WHERE CustID = h.CustID AND Year = t.ArYear))
 
 IF @@ERROR <> 0
 BEGIN
 
  ROLLBACK TRANSACTION
  RETURN 34
 END
 
 IF (@PostWhat <> 0 AND @PostInvoice <> 0)
 BEGIN
 /** Select customers with transactions into temp table **/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 31,'Update Summary History -- Customers'
  SELECT h.CustId, h.FiscalYear, h.SumHistPeriod,
   Sum(convert(decimal(20,10),SIGN(TransType))*(TaxableSales+NonTaxableSales)) TotSales,
   Sum(convert(decimal(20,10),SIGN(TransType))*TotCost) TotCogs,
   Sum(CASE WHEN TransType>0 THEN 1 ELSE 0 END) NumInvc
  INTO #PostSum1
  FROM tblSoTransHeaderPost h
  WHERE UserID = @UserID AND WrkStnId = @WrkStnId AND
   CustId In (SELECT CustID FROM tblArCust WHERE CcCompYn=0)
  GROUP BY CustId, FiscalYear, SumHistPeriod
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 31
  END
 /** Round amounts to base precision **/
  UPDATE #PostSum1
  SET TotSales = ROUND(CONVERT(Decimal(20,10),TotSales),@CurrPrec),
   TotCogs = ROUND(CONVERT(Decimal(20,10),TotCogs),@CurrPrec)
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 32
  END
 /** Update customers in tblArSumHistCust**/
  UPDATE tblArSumHistCust
  SET TotSales = tblArSumHistCust.TotSales+#PostSum1.TotSales,
   TotCogs = tblArSumHistCust.TotCogs+#PostSum1.TotCogs,
   NumInvc = tblArSumHistCust.NumInvc+#PostSum1.NumInvc
  FROM #PostSum1 INNER JOIN tblArSumHistCust
   ON (#PostSum1.CustId = tblArSumHistCust.CustID)
   AND (#PostSum1.FiscalYear = tblArSumHistCust.Year)
   AND (tblArSumHistCust.Period = #PostSum1.SumHistPeriod)
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 33
  END
 
 END
 IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
 BEGIN
 /** Sum Number of pmts & Tot discount into temp table **/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 36,'Update summary payment history'
  SELECT crh.CustId, crh.FiscalYear, crh.SumHistPeriod,
   crd.InvcNum, crh.PmtDate, crd.PmtAmt,
   crd.Difference  TotDisc,
   CONVERT(int,0)  TotDaysToPay
  INTO #PostSum2
  FROM (tblSoTransBatchCs INNER JOIN tblArCashRcptHeader crh
   ON tblSoTransBatchCs.BatchID = crh.DepositID)
  INNER JOIN tblArCashRcptDetail crd
   ON crh.RcptHeaderID = crd.RcptHeaderID
  WHERE crh.CustId IN (SELECT CustID FROM tblArCust
   WHERE CcCompYn=0)
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 36
  END
 
 /** Update temp table with tot days to pay **/
  UPDATE #PostSum2
  SET TotDaysToPay = TotDaysToPay+DATEDIFF(dy,TransDate,PmtDate)
  FROM #PostSum2 INNER JOIN tblArOpenInvoice
   ON (#PostSum2.CustId = tblArOpenInvoice.CustId)
   AND (#PostSum2.InvcNum = tblArOpenInvoice.InvcNum)
  WHERE #PostSum2.PmtDate>TransDate
   AND tblArOpenInvoice.RecType=1
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 37
  END
 /** Create temp table 3 **/
  SELECT CustId, FiscalYear, SumHistPeriod,
   Sum(TotDisc)  SumOfTotDisc,
   Sum(TotDaysToPay)  SumOfTotDaysToPay,
   Sum(CASE WHEN PmtAmt>0 THEN 1 ELSE 0 END)  SumOfNumPmt
  INTO #PostSum3
  FROM #PostSum2
  GROUP BY CustId, FiscalYear, SumHistPeriod
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 38
  END
 
/**update from temp **/
  UPDATE tblArSumHistCust
  SET TotDisc = TotDisc+SumOfTotDisc,
   TotDaysToPay = TotDaysToPay+SumOfTotDaysToPay,
   NumPmt = NumPmt+SumOfNumPmt
  FROM #PostSum3 INNER JOIN tblArSumHistCust
   ON (#PostSum3.CustId = tblArSumHistCust.CustID)
   AND (tblArSumHistCust.Year = #PostSum3.FiscalYear)
   AND (tblArSumHistCust.Period = #PostSum3.SumHistPeriod)
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 39

  END
 END
/** update tblArSumHistCust from tblArCustHist curr due, balage1,2,... **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 41,'Update Summary history buckets'
 UPDATE tblArSumHistCust
 SET UnpaidFinch = tblArCustHist.UnpaidFinch,
  CurAmtDue = tblArCustHist.CurAmtDue-tblArCustHist.UnapplCredit,
   BalAge1 = tblArCustHist.BalAge1,
  BalAge2 = tblArCustHist.BalAge2,
  BalAge3 = tblArCustHist.BalAge3,
  BalAge4 = tblArCustHist.BalAge4
 FROM #tmpTransCust INNER JOIN tblArCustHist ON tblArCustHist.CustID = #tmpTransCust.CustID
  INNER JOIN tblArSumHistCust
  ON tblArCustHist.CustID = tblArSumHistCust.CustId
 WHERE tblArSumHistCust.Year=@PostYear
  AND tblArSumHistCust.Period=@CurPd
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
 RETURN 41
 END
/**  Update SUMMARY HISTORY COMPANY table (tblArSumHistComp)**/
 /** Delete comp hist record for curr Year/Period **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 42,'Update Summary History -- Company'
 DELETE tblArSumHistComp
  FROM tblArSumHistComp INNER JOIN tblSoTransHeaderPost h ON
  tblArSumHistComp.year=h.fiscalyear and
  tblArSumHistComp.period=h.glperiod
  WHERE UserID = @UserID AND WrkStnId = @WrkStnId
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 42
 END
 /** Insert summary Customer hist into tblArSumHistComp **/
 INSERT tblArSumHistComp
  (Year, Period, TotSales, TotCogs, TotDisc, NumInvc,
  TotDaysToPay, NumPmt, UnpaidFinch, CurAmtDue, BalAge1,
  BalAge2, BalAge3, BalAge4)  
 SELECT Year, Period, Sum(TotSales), Sum(TotCogs),
  Sum(TotDisc), Sum(NumInvc), Sum(TotDaysToPay),
  Sum(NumPmt) , Sum(UnpaidFinch), Sum(CurAmtDue),
  Sum(BalAge1), Sum(BalAge2), Sum(BalAge3),
  Sum(BalAge4)
 FROM tblArSumHistCust s inner join (select fiscalyear, glperiod
  from tblsotransheaderpost WHERE UserID = @UserID AND WrkStnId = @WrkStnId
  group by fiscalyear,glperiod) h
  on s.year=h.fiscalyear and s.period=h.glperiod
 GROUP BY Year, Period
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 43
 END
/**
    Update SUMMARY HISTORY ITEM table (tblArSumHistItem) **/
 IF (@PostWhat <> 0 AND @PostInvoice <> 0)
 BEGIN
 /** Create temp table Post6e with item info -- skip kit components**/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 44,'Update Summary History -- Items'
  SELECT th.FiscalYear, th.SumHistPeriod Period, th.CustId,
   CASE WHEN td.ItemID IS NULL THEN 'NA' ELSE td.ItemID END PartID,
   MAX(td.UnitsBase) FirstOfUnitsBase,
   Sum(QtyShipBase*SIGN(TransType)) SumOfQtyShipBase,
   Sum(UnitPriceSell*QtyShipSell*SIGN(TransType)) SumOfTotSales,
   Sum(UnitCostSell*QtyShipSell*SIGN(TransType)) SumOfTotCogs,
   Sum(CASE WHEN TransType>0 THEN 1 ELSE 0 END) SumOfNumInvc
  INTO #Post6e
  FROM tblSoTransHeaderPost th  
  INNER JOIN tblSoTransDetail td ON th.TransId = td.TransID
  WHERE UserID = @UserID AND WrkStnId = @WrkStnId AND td.GrpID IS NULL
  GROUP BY th.FiscalYear, th.SumHistPeriod, th.CustId, td.ItemID
 
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 44
  END
 /** Round totsales/cogs to base currency in temp table Post6e **/
  UPDATE #Post6e
  SET SumOfTotSales = ROUND(CONVERT(Decimal(20,10),SumOfTotSales),@CurrPrec),
   SumOfTotCogs = ROUND(CONVERT(Decimal(20,10),SumOfTotCogs),@CurrPrec)
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 45
  END
 /** Create new records in tblArSumHistItem **/
  INSERT tblArSumHistItem ( Year, Period, CustID, PartID,
   BaseUnit)
  SELECT t.FiscalYear, t.Period, t.CustId, t.PartID, FirstOfUnitsBase
  FROM #Post6e t LEFT JOIN tblArSumHistItem S ON
  t.CustID=S.CustID AND t.FiscalYear=S.Year AND t.Period=S.Period AND t.PartID=S.PartID
  WHERE S.CustID IS NULL
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 47
  END
 /** Update tblArSumHistItem **/
  UPDATE tblArSumHistItem
  SET Qty = Qty+#Post6e.SumOfQtyShipBase,
   TotSales = TotSales+#Post6e.SumOfTotSales,
   TotCogs = TotCogs+#Post6e.SumOfTotCogs,
   NumInvc = NumInvc+#Post6e.SumOfNumInvc
  FROM tblArSumHistItem INNER JOIN #Post6e
   ON (tblArSumHistItem.Period = #Post6e.Period)
   AND (tblArSumHistItem.Year = #Post6e.FiscalYear)
   AND (tblArSumHistItem.PartID = #Post6e.PartID)
   AND (tblArSumHistItem.CustID = #Post6e.CustId)
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 46
  END
 END
END
/**   Update Sales Reps (tblArSalesRep)**/
IF (@PostWhat <> 0 AND @PostInvoice <> 0)
BEGIN
/** Update Rep 1 Ptd & Ytd Sales & Last Sales Date **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 48,'Update First Sales Rep'
 UPDATE tblArSalesRep
 SET PTDSales = PTDSales+(convert(decimal(20,10),SIGN(TransType))*
   (TaxableSales+NonTaxableSales+SalesTax+TaxAmtAdj+Freight+Misc)),
 YTDSales = YTDSales+(convert(decimal(20,10),SIGN(TransType))*
   (TaxableSales+NonTaxableSales+SalesTax+TaxAmtAdj+Freight+Misc)),
 LastSalesDate = CASE WHEN (TransType>0 AND LastSalesDate IS NULL) OR InvcDate>LastSalesDate
  THEN InvcDate
  ELSE LastSalesDate END
 FROM tblArSalesRep INNER JOIN tblSoTransHeaderPost h
  ON tblArSalesRep.SalesRepID = h.Rep1Id
 WHERE h.Rep1Id IS NOT NULL AND UserID = @UserID AND WrkStnId = @WrkStnId
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 48
 END
/** Update Rep 2 Ptd & Ytd Sales & Last Sales Date **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 49,'Update Second Sales Rep'
 UPDATE tblArSalesRep
 SET PTDSales = PTDSales+(convert(decimal(20,10),SIGN(TransType))
    *(TaxableSales+NonTaxableSales+SalesTax+TaxAmtAdj+Freight+Misc)),
  YTDSales = YTDSales+(convert(decimal(20,10),SIGN(TransType))
    *(TaxableSales+NonTaxableSales+SalesTax+TaxAmtAdj+Freight+Misc)),
  LastSalesDate = CASE WHEN (TransType>0 AND LastSalesDate IS NULL) OR InvcDate>LastSalesDate
   THEN InvcDate
   ELSE LastSalesDate END
 FROM tblArSalesRep INNER JOIN tblSoTransHeaderPost h
  ON tblArSalesRep.SalesRepID = h.Rep2Id
 WHERE h.Rep2Id IS NOT NULL AND UserID = @UserID AND WrkStnId = @WrkStnId
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 49
 END
END
/**   Update Commissions (tblArCommInvc)**/
IF (@CommYn = 1)
BEGIN
 IF (@PostWhat <> 0 AND @PostInvoice <> 0)
 BEGIN
 /** Update & add new Rep 1 commission invoice records **/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 50,'Update Commissions for First Sales Rep'
  INSERT tblArCommInvc (SalesRepID, CustId, InvcNum,
   InvcDate, PctInvc, CommRateDtl, PctOfDtl,
   BasedOnDtl, PayLines, PayTax, PayFreight, PayMisc,
   AmtLines, AmtTax, AmtFreight, AmtMisc, AmtCogs)
  SELECT sr.SalesRepID, th.CustId, th.InvcNum, th.InvcDate,
   th.Rep1Pct, sr.CommRate, sr.PctOf, sr.BasedOn,
   CONVERT(smallint,sr.PayOnLineItems),
   CONVERT(smallint,sr.PayOnSalesTax),
   CONVERT(smallint,sr.PayOnFreight),
   CONVERT(smallint,sr.PayOnMisc),
   Sum((convert(decimal(20,10),SIGN(TransType))*(TaxableSales+NonTaxableSales))),
   Sum((convert(decimal(20,10),SIGN(TransType))*SalesTax)),
   Sum((convert(decimal(20,10),SIGN(TransType))*Freight)),
   Sum((convert(decimal(20,10),SIGN(TransType))*Misc)),
   Sum((convert(decimal(20,10),SIGN(TransType))*TotCost))
  FROM tblArSalesRep sr INNER JOIN tblSoTransHeaderPost th
   ON sr.SalesRepID = th.Rep1Id
  WHERE th.Rep1Pct>0 AND UserID = @UserID AND WrkStnId = @WrkStnId
  GROUP BY sr.SalesRepID, th.CustId, th.InvcNum, th.InvcDate,
   th.Rep1Pct, sr.CommRate, sr.PctOf, sr.BasedOn,
   CONVERT(smallint,sr.PayOnLineItems),
   CONVERT(smallint,sr.PayOnSalesTax),
   CONVERT(smallint,sr.PayOnFreight),
   CONVERT(smallint,sr.PayOnMisc)
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 50
  END
 /** Update & add new Rep 2 commission invoice records **/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 51,'Update Commissions for second Sales Rep'
  INSERT tblArCommInvc (SalesRepID, CustId, InvcNum,
   InvcDate, PctInvc, CommRateDtl, PctOfDtl,
   BasedOnDtl, PayLines, PayTax, PayFreight, PayMisc,
   AmtLines, AmtTax, AmtFreight, AmtMisc, AmtCogs)
  SELECT sr.SalesRepID, th.CustId, th.InvcNum, th.InvcDate,
   th.Rep2Pct, sr.CommRate, sr.PctOf, sr.BasedOn,
   CONVERT(smallint,sr.PayOnLineItems),
   CONVERT(smallint,sr.PayOnSalesTax),
   CONVERT(smallint,sr.PayOnFreight),
   CONVERT(smallint,sr.PayOnMisc),
   Sum((convert(decimal(20,10),SIGN(TransType))*(TaxableSales+NonTaxableSales))),
   Sum((convert(decimal(20,10),SIGN(TransType))*SalesTax)),
   Sum((convert(decimal(20,10),SIGN(TransType))*Freight)),
   Sum((convert(decimal(20,10),SIGN(TransType))*Misc)),
   Sum((convert(decimal(20,10),SIGN(TransType))*TotCost))
  FROM tblArSalesRep sr INNER JOIN tblSoTransHeaderPost th
   ON sr.SalesRepID = th.Rep2Id
  WHERE th.Rep2Pct>0 AND UserID = @UserID AND WrkStnId = @WrkStnId
  GROUP BY sr.SalesRepID, th.CustId, th.InvcNum, th.InvcDate,
   th.Rep2Pct, sr.CommRate, sr.PctOf, sr.BasedOn,
   CONVERT(smallint,sr.PayOnLineItems),
   CONVERT(smallint,sr.PayOnSalesTax),
   CONVERT(smallint,sr.PayOnFreight),
   CONVERT(smallint,sr.PayOnMisc)
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 51
  END
 END
 IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
 BEGIN
 /** Update & add payments to commission invoice records **/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 52,'Update & add payments to commission invoice records'
  UPDATE tblArCommInvc
  SET AmtPmt = AmtPmt+tblArCashRcptDetail.PmtAmt+Difference
  FROM tblSoTransBatchCs INNER JOIN (tblArCashRcptHeader
  INNER JOIN (tblArCashRcptDetail
  INNER JOIN tblArCommInvc
   ON tblArCashRcptDetail.InvcNum = tblArCommInvc.InvcNum)
   ON (tblArCashRcptHeader.RcptHeaderID = tblArCashRcptDetail.RcptHeaderID)
    AND (tblArCashRcptHeader.CustId = tblArCommInvc.CustId))
   ON tblSoTransBatchCs.BatchID = tblArCashRcptHeader.DepositID
  WHERE UserID = @UserID
   AND WrkStnId = @WrkStnId
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 52
  END
 END
END
/** Update Bank Rec (tblBrMaster)**/
IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
BEGIN
 IF (@BrYn = 1)
 BEGIN
 /** Append records to tblBrMaster **/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 53,'Append records to Bank Rec'
  INSERT tblBrMaster (SourceID, BankID, CurrencyId,
   TransDate, ExchRate, TransType, Amount, AmountFgn, ClearedYn, Descr,
   Reference, SourceApp, FiscalYear, GlPeriod)
  SELECT crh.DepositID, crh.BankID,
   crh.CurrencyID, @WrkStnDate, ExchRate, 2, Sum(crd.PmtAmt),
   Sum(crd.PmtAmtFgn), 0, @Descr1, @Ref1, 'AR',crh.FiscalYear, crh.GlPeriod
  FROM (tblArCashRcptHeader crh INNER JOIN tblSoTransBatchCs
   ON crh.DepositID = tblSoTransBatchCs.BatchID)
   INNER JOIN tblArCashRcptDetail crd ON (crh.RcptHeaderID = crd.RcptHeaderID)
   AND (crh.RcptHeaderID = crd.RcptHeaderID)
   AND (crh.RcptHeaderID = crd.RcptHeaderID)
   AND (crh.RcptHeaderID = crd.RcptHeaderID)
   WHERE crh.BankID IS NOT NULL
   AND UserID = @UserID AND WrkStnId = @WrkStnId
   GROUP BY crh.DepositID, crh.BankID,
   crh.CurrencyID, crh.FiscalYear, crh.GlPeriod, ExchRate
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 53
  END
 /** Update GlAcctBal in tblBrBank **/
  EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 54,'Update Account Bal in Bank Rec'
  --Pet 39728 mlc 1/4/01 Summarize the payment amount by bank for updating (also added User/WrkStn filter)
  UPDATE tblBrBank
  SET GlAcctBal = tblBrBank.GlAcctBal + t.PmtAmt
  FROM tblBrBank INNER JOIN
   (Select crh.BankId, SUM(crd.PmtAmt) AS PmtAmt FROM tblArCashRcptHeader crh
   INNER JOIN tblArCashRcptDetail crd ON crh.RcptHeaderID = crd.RcptHeaderID
   INNER JOIN tblSoTransBatchCs b ON crh.DepositID = b.BatchID
   WHERE UserID = @UserID AND WrkStnId = @WrkStnId
   GROUP BY BankId) t ON t.BankID = tblBrBank.BankId
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 54
  END
 END
END
/** ********************************************************************
 Create and update GL Temp Log table tmpArTransPostLogDtl
******************************************************************** **/
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 551,'Create and update GL Log'
SELECT @RetCode = 0
EXEC @RetCode = qrySoTransPostGlLog @CurrPrec, @UserID, @WrkStnId,
 @GlAcctDisc, @InYn, @MsgLog0, @MsgLog1, @MsgLog2, @MsgLog3,
 @MsgLog4, @MsgLog5, @MsgLog6, @MsgLog7, @MsgLog8, @MsgLog9,
 @WrkStnDate, @GlDetailYn, @PostRun
IF (@RetCode <> 0) OR (@@ERROR<>0)
BEGIN
 ROLLBACK TRANSACTION
 RETURN 551 + @RetCode
END
IF (@GlYn = 1)
BEGIN
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 55, 'Post to GL Journal'
 IF (@GLDetailYn = 1)
 BEGIN
  INSERT tblGlJrnl (PostRun, CompId, EntryDate, TransDate, [Desc], SourceCode, Reference, AcctId,
  DebitAmt, CreditAmt, Period, Year, LinkID, LinkIDSub, LinkIDSubLine)
     SELECT @PostRun, @CompId, PostDate, TransDate, Descr, SourceCode,
     Reference, GlAcct, DR, CR, GlPeriod, Year, LinkID, LinkIDSub, LinkIDSubLine
  FROM  tmpSoTransPostLogDtl WHERE UserID=@UserID AND WrkStnID=@WrkStnID
 
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 55
  END
 END
 ELSE
    BEGIN
  INSERT tblGlJrnl (PostRun, CompId, EntryDate, TransDate, [Desc], SourceCode, Reference, AcctId,
  DebitAmt, CreditAmt, Period, Year)
     SELECT @PostRun, @CompId, PostDate, TransDate, Descr, SourceCode,
     Reference, GlAcct, DR, CR, GlPeriod, Year
  FROM  tmpSoTransPostLogSum WHERE UserID=@UserID AND WrkStnID=@WrkStnID
  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION
   RETURN 55
  END
 END
END
/**  Update Main Log table (tmpArTransPostLog and tmpArTransPostLog2)**/
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 56,'Create and Update Main Log'
EXEC @RetCode = qrySoTransPostMainLog @UserID, @WrkStnId
IF @RetCode <> 0 or @@ERROR <>0
BEGIN
 ROLLBACK TRANSACTION
 RETURN 56
END
/**    Delete all transactions from the transaction tables**/
IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
BEGIN
/** Delete transactions from tblArCashRcptHeader **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 57,'Delete Cash Receipts'
 DELETE tblArCashRcptHeader
 FROM tblSoTransBatchCs
 INNER JOIN tblArCashRcptHeader
  ON tblSoTransBatchCs.BatchID = tblArCashRcptHeader.DepositID
 WHERE UserID = @UserID
  AND WrkStnId = @WrkStnId
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 57
 END
END
IF (@PostWhat <> 0 AND @PostInvoice <> 0)
BEGIN
/** Delete transactions from tblSoTransHeader **/
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 58,'Delete Transactions'
 DELETE tblSoTransLot FROM tblSoTransLot INNER JOIN #tmpTransID T
  ON tblSoTransLot.TransID = T.TransID
 DELETE tblSoTransSer FROM tblSoTransSer INNER JOIN #tmpTransID T
  ON tblSoTransSer.TransID = T.TransID
 --delete lines with no back orders
 DELETE tblSoTransDetail FROM tblSoTransDetail INNER JOIN #tmpTransID T
  ON tblSoTransDetail.TransID = T.TransID
  WHERE tblSoTransDetail.QtyBackOrdSell=0 AND KitQtyBackOrdered=0
 DELETE tblSoTransTax FROM tblSoTransTax INNER JOIN #tmpTransID T
  ON tblSoTransTax.TransID = T.TransID
 --delete headers with no line items
 DELETE tblSoTransHeader FROM tblSoTransHeader
  INNER JOIN #tmpTransID T ON tblSoTransHeader.TransID = T.TransID
  WHERE tblSoTransHeader.TransID Not IN (SELECT TransID FROM tblSoTransDetail)
 
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 58
 END
 --update backorders
 UPDATE tblSoTransHeader
  SET TaxableSales = 0, NonTaxableSales =0,SalesTax =0,
  Freight = 0, Misc = 0, TotPmtAmt = 0, TotCost = 0, TaxAmtAdj = 0,
  PrintInvcStatus = 0, PrintPickStatus = 0, InvcNum = null, InvcDate = @WrkStnDate,
  TaxableSalesFgn=0,NonTaxableSalesFgn =0,SalesTaxFgn =0,
  FreightFgn = 0, MiscFgn = 0, TotPmtAmtFgn = 0, TotCostFgn = 0, TaxAmtAdjFgn = 0
  WHERE TransID IN (SELECT TransID FROM #tmpTransID)
 --set all in posted batch to backordered status
 UPDATE tblSoTransHeader
  SET TransType = 3
  WHERE BatchID IN (SELECT BatchID FROM tblSoTransBatchCs WHERE
   UserID=@UserID And WrkStnID = @WrkStnID) AND TransType IN (-1,1,4)
 
 Update tblSoTransDetail
  SET OrigOrderQty= CASE WHEN OrigOrderQty=0 THEN QtyOrdSell ELSE OrigOrderQty END,
  QtyOrdSell = QtyBackordSell,
  QtyShipSell = 0,
  QtyShipBase = 0, QtyBackOrdSell = 0,KitQtyBackOrdered=0
  WHERE TransID IN (SELECT TransID FROM #tmpTransID) AND GrpID IS NULL
 
 Update tblSoTransDetail
  SET KitQty = KitQtyBackOrdered, KitQtyBackOrdered = 0
  WHERE TransID IN (SELECT TransID FROM #tmpTransID) AND GrpID IS NOT NULL
 
 --move seleted batches to new batch
 IF @NewBatchID<>''
  UPDATE tblSoTransHeader
   SET BatchID = @NewBatchID
   WHERE BatchID IN (SELECT BatchID FROM tblSoTransBatchCs WHERE
    UserID=@UserID And WrkStnID = @WrkStnID)
END
--mark prepayments as posted
IF (@PostWhat <> 0 AND @PostCashRcpt <> 0)
 UPDATE tblSoTransPmt
  SET PostedYn = 1
  WHERE TransID IN (SELECT TransID FROM tblSoTransHeader H INNER JOIN
   tblSoTransBatchCs B ON  H.BatchID=B.BatchID WHERE
   UserID=@UserID And WrkStnID = @WrkStnID)
IF (@BatchYn=1)
BEGIN
 EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 60,'Delete posted batch records'
 DELETE  
 FROM tblSoBatch
 WHERE BatchID<> @BatchID AND
  BatchID IN (SELECT BatchID FROM tblSoTransBatchCs WHERE
   UserID = @UserID AND WrkStnId = @WrkStnId)
  AND BatchID Not IN (SELECT DISTINCT BatchID FROM tblSoTransHeader)
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN 60
 END
END
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 70,'Commit Post...'
 
COMMIT TRANSACTION
EXEC trvsp_SmSetFunctionStatus 'SO Transaction Post', 9999, 'Terminate'
RETURN 0
 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentAuthor Commented:
<<Also, please maintain your abandoned questions.  Here are listed all your open questions:>>

  Only one of those questions is actually a question.  I'm the page editor for the MS Access area.  The other "questions" are used for helping with that and must remain open.

Jim.
0
 
rafranciscoCommented:
In profiler, try selecting the following:

Events
- Stored Procedures - SP: StmtStarting
- Stored Procedures - SP:StmtCompleted
- TSQL - SQL:StmtStarting
- TSQL - SQL:StmtCompleted

This should give you an idea on the statements being executed by your stored procedure.  If you find a statement that has a SQL:StmtStarting but no SQL:StmtCompleted, then this is the statement that's giving you an error.

Hope this helps.
0
 
Anthony PerkinsCommented:
If you can duplicate the error every time than in SQL Query Analyzer just:
1. Replace the parameters with local variables
2. Assign the values to those variables
3. Paste all the code after the AS line in the store procedure.  

Something like this:

Declare      @UserID varchar(20),
            @PostRun varchar (14),
            @PostYear smallint,
            @CurPd smallint,
            @WrkStnDate datetime,
            @PostWhat smallint,
            @PostInvoice bit,
            @PostCashRcpt bit,
            @CurrPrec smallint,
            @NewBatchID varchar(6),
            @gPrecUPrice smallint,
            @gPrecUCost smallint,  
            @BatchId varchar(6)='######',
            @MsgLog0 varchar(30),
            @MsgLog1 varchar(30),
            @MsgLog2 varchar(30),
            @MsgLog3 varchar(30),
            @MsgLog4 varchar(30),
            @MsgLog5 varchar(30),
            @MsgLog6 varchar(30),
            @MsgLog7 varchar(30),
            @MsgLog8 varchar(30),
            @MsgLog9 varchar(30),
            @Descr1 varchar(30),
            @Ref1 varchar(15),
            @CompID varchar(3),
            @BrYn bit,
            @GlYn bit,
            @InYn bit,
            @KitYn bit

Select      @UserID = 'Insert @UserID value here',
            @PostRun = 'Insert @PostRun value here',
            @PostYear = Insert @PostYear value here,
            @CurPd = Insert @CurPd value here,
            @WrkStnDate = 'Insert @WrkStnDate value here',
            @PostWhat = 'Insert PostWhat value here,
            etc.

DECLARE @MonthPart varchar(2), @DayPart varchar(2),
  @WrkStnID varchar(20),
 @GlPostLog varchar(20),
 @RetCode smallint, @NumPd smallint
--from options
DECLARE @SalesDtlYn bit,@SalesSumYn bit,@PostAddnlDesc bit,@InvcFinch varchar(15),
 @CommYn bit,@BatchYn bit,
 @GlDetailYn bit,@GlAcctDisc varchar(40),@ApplyCreditsToOldest bit
DECLARE @CustID varchar(10), @PreCustID varchar(10),
 @InvcDate datetime, @InvcNum varchar(15), @SaleAmt decimal(20,10)

-- Rest of your code goes here

When you run this code in SQL Query Analyzer it should give your the line that is causing you grief.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentAuthor Commented:
Thanks.  The Profiler events let me pin it down, but it was still a lot to wade through.  I think acperkins method would pin it down faster, but I didn't try it as I would have to:

1.  get all the parameters exactly right
2. this was a batch of orders, so I would have had to break up the batch (I'm assuming that I would have been executing "live" if I used query analyzer).

 So is there another event out of the error category that should have been included?  Seems to me it would be nice to see a line in the trace output exactly where the error occurs.  I could have pinned it in an instant then.

Jim.

0
 
rafranciscoCommented:
I haven't played around much with the Error category but you can try the Exception and Execution Warnings events.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now