Dynamics Community Forum Thread Details

We are upgrading from GP2010 (11.00.2248) to GP 2015 (14.00.0524).

SQL2012 on Windows 2012 Server

We get the following error and cannot proceed. Advice please as to how to resolve

The following SQL statement produced an error:
CREATE PROCEDURE uprCreateTrxDatafromTimecards @EmpID char(15),@PaySched char(15),@YEAR1 smallint, @PERIODID smallint, @O_iErrorState INT OUTPUT AS declare @cDBName char(5), @Note_Index numeric(19,5), @CompID int, @BatchID char(15), @cGETMSG12424 char(255), @iStatus int, @CompTrxNum int, @curEmpID char(15), @curJobTitle char(7), @curDept char(7), @curDivisionCode char(15), @curTimeCode char(15), @curTrxDate datetime, @curUnitsToPay numeric (19,5), @curHrlyPayRate numeric (19,5), @curDaysWorked int, @curWeeksWorked int, @curAccureTimeBenefits tinyint, @curCompTrxNum int, @curPayCode char(7), @NextAttendanceTrx int, @curSTATECD char(3), @curLOCALTAX char(7), @curSUTASTAT char(3), @curWRKRCOMP char(7), @curSHFTCODE char(7), @curSHFTPREM numeric (19,5), @curUNIONCD char(7), @O_SQL_Error_State int select @BatchID = dbo.uprGetEmpBatchPrefix(@EmpID) select @BatchID = RTRIM(@BatchID) + '-' + CONVERT(varchar,@PERIODID) select @cDBName = db_name() exec @iStatus = DYNAMICS..smGetMsgString 12424, @cDBName, @cGETMSG12424 output, @O_SQL_Error_State output select @CompID = CMPANYID from DYNAMICS..SY01500 where INTERID = @cDBName if not exists(select * from UPR10301 where BACHNUMB=@BatchID and UPRBCHOR = 1) begin exec @iStatus = DYNAMICS..smGetNextNoteIndex @CompID, 0, @Note_Index output, @O_SQL_Error_State output insert into UPR10301 (BACHNUMB, UPRBCHOR, BCHCOMNT, UPRBCHFR, POSTEDDT, UPRBCHMK, MKDBYUSR, MSCBDINC, RECPSTGS, NOFPSTGS, CNTRLTRX, CTRLEMPCT, APPROVL, APPRVLDT, APRVLUSERID, NOTEINDX) values( @BatchID, 1, @cGETMSG12424, 1, '', 0, '', 0, 0, 0, 0, 1, 0, '', '', @Note_Index) end else update UPR10301 set CTRLEMPCT=CTRLEMPCT+1 from UPR10301 where BACHNUMB=@BatchID and UPRBCHOR = 1 DECLARE PayrollTrx CURSOR FOR select TimeCardDtl.EMPLOYID, TimeCardDtl.JOBTITLE, TimeCardDtl.DEPRTMNT, EmpMstr.DIVISIONCODE_I,TimeCardDtl.TRXDATE, TimeCardDtl.UNTSTOPY, PAYRTAMT, TimeCardDtl.DAYSWRDK, TimeCardDtl.WKSWRKD, TimeCardDtl.PayCode, TimeCardDtl.STATECD,TimeCardDtl.LOCALTAX, EmpPayCode.SUTASTAT, EmpPayCode.WRKRCOMP, EmpPayCode.SHFTCODE, SHFTPREM = case SHFTTYPE when 0 then SHFTAMT when 1 then EmpPayCode.PAYRTAMT * (ShiftCode.SHFTPCT / 100) else 0.0 end, EmpMstr.UNIONCD from UPR10501 TimeCardDtl, UPR00100 EmpMstr, UPR00400 EmpPayCode left outer join UPR41500 ShiftCode ON (EmpPayCode.SHFTCODE = ShiftCode.SHFTCODE) where TimeCardDtl.EMPLOYID = @EmpID and EmpMstr.EMPLOYID = @EmpID and EmpPayCode.EMPLOYID = @EmpID and TimeCardDtl.Pay_Schedule = @PaySched and TimeCardDtl.YEAR1 = @YEAR1 and TimeCardDtl.PERIODID = @PERIODID and TimeCardDtl.UNTSTOPY <> 0.0 and EmpPayCode.PAYRCORD = TimeCardDtl.PayCode OPEN PayrollTrx; FETCH NEXT FROM PayrollTrx INTO @curEmpID, @curJobTitle, @curDept, @curDivisionCode, @curTrxDate, @curUnitsToPay,@curHrlyPayRate, @curDaysWorked, @curWeeksWorked, @curPayCode, @curSTATECD,@curLOCALTAX, @curSUTASTAT, @curWRKRCOMP, @curSHFTCODE, @curSHFTPREM, @curUNIONCD; WHILE @@FETCH_STATUS = 0 Begin exec uprGetNextCompTrx @CompTrxNum output, @O_SQL_Error_State output exec @iStatus = DYNAMICS..smGetNextNoteIndex @CompID, 0, @Note_Index output, @O_SQL_Error_State output insert into UPR10302 values( @CompTrxNum, @BatchID, @curEmpID, 1, 0, @curPayCode, @curTrxDate, @curTrxDate, @curUnitsToPay*100, @curHrlyPayRate, 0.0, 0.0, 0.0, 0.0, @curDaysWorked, @curWeeksWorked, @curDept, @curJobTitle, @curSTATECD, @curLOCALTAX, @curSUTASTAT, @curWRKRCOMP, 'WFUSER', CONVERT(DATE,GETDATE()), '', 0, 0, @Note_Index, 0.0, @curSHFTCODE, @curSHFTPREM, '', @curUNIONCD) FETCH NEXT FROM PayrollTrx INTO @curEmpID, @curJobTitle, @curDept, @curDivisionCode, @curTrxDate, @curUnitsToPay,@curHrlyPayRate, @curDaysWorked, @curWeeksWorked, @curPayCode, @curSTATECD,@curLOCALTAX, @curSUTASTAT, @curWRKRCOMP, @curSHFTCODE, @curSHFTPREM, @curUNIONCD; end CLOSE PayrollTrx; DEALLOCATE PayrollTrx; if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TAST0130]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and (1 = (select TAACCRUE from TAST0130 where COMPANYCODE_I = @cDBName)) begin DECLARE TimeCodesTrx CURSOR FOR select TimeCardDtl.EMPLOYID, TimeCardDtl.JOBTITLE, TimeCardDtl.DEPRTMNT, EmpMstr.DIVISIONCODE_I, TimeCodes.TIMECODE_I,TimeCardDtl.TRXDATE, TimeCardDtl.UNTSTOPY, TimeCardDtl.DAYSWRDK, TimeCardDtl.WKSWRKD, TimeCodes.ACCRUETIMEBENEFITS_I, PayTrxDtl.COMPTRNM, TimeCardDtl.PayCode from UPR10501 TimeCardDtl, UPR10302 PayTrxDtl, UPR00100 EmpMstr, TATM0130 TimeCodes where TimeCardDtl.EMPLOYID = @EmpID and TimeCardDtl.Pay_Schedule = @PaySched and TimeCardDtl.YEAR1 = @YEAR1 and TimeCardDtl.PERIODID = @PERIODID and TimeCardDtl.UNTSTOPY <> 0.0 and PayTrxDtl.EMPLOYID = @EmpID and TimeCardDtl.PayCode = PayTrxDtl.UPRTRXCD and PayTrxDtl.BACHNUMB = @BatchID and TimeCardDtl.TRXDATE = PayTrxDtl.TRXBEGDT and TimeCardDtl.DEPRTMNT = PayTrxDtl.DEPRTMNT and TimeCardDtl.JOBTITLE = PayTrxDtl.JOBTITLE and TimeCardDtl.STATECD = PayTrxDtl.STATECD and TimeCardDtl.LOCALTAX = PayTrxDtl.LOCALTAX and TimeCardDtl.SHFTCODE = PayTrxDtl.SHFTCODE and EmpMstr.EMPLOYID = @EmpID and TimeCardDtl.PayCode = TimeCodes.PAYRCORD OPEN TimeCodesTrx; FETCH NEXT FROM TimeCodesTrx INTO @curEmpID, @curJobTitle, @curDept, @curDivisionCode, @curTimeCode, @curTrxDate, @curUnitsToPay, @curDaysWorked, @curWeeksWorked, @curAccureTimeBenefits, @curCompTrxNum, @curPayCode; WHILE @@FETCH_STATUS = 0 Begin exec @iStatus = dbo.hrGetNextAttendanceTrx @NextAttendanceTrx OUTPUT, @O_SQL_Error_State OUTPUT; exec @iStatus = DYNAMICS..smGetNextNoteIndex @CompID, 0, @Note_Index output, @O_SQL_Error_State output insert into TATX1030 values( @NextAttendanceTrx, @curEmpID, @curJobTitle, @curDept, @curDivisionCode, @curTimeCode, @curTrxDate, @curTrxDate, @curUnitsToPay * 100, 0, @curDaysWorked, @curWeeksWorked, 0, @curAccureTimeBenefits, 0, '', '', 1, @BatchID, @curCompTrxNum, 0, @curPayCode, 'WFUSER', CONVERT(DATE,GETDATE()), CONVERT(TIME,DateAdd(ms, -DatePart(ms,getdate()), getdate())), @Note_Index); update TATM2030 set HOURSAVAILABLE_I = @curUnitsToPay * 100 from TATM2030 TaMstrYrMax, TATX1030 TaTrxDtl where TaMstrYrMax.EMPID_I = @EmpID and TaMstrYrMax.TIMECODE_I = @curTimeCode and TaMstrYrMax.YEAR1 = @YEAR1 and TaTrxDtl.EMPID_I = @EmpID and @curTimeCode = TaTrxDtl.TIMECODE_I and TaTrxDtl.ACCFLG = 1 update TATM1030 set HOURS_I = TaMstrTypes.HOURS_I + @curUnitsToPay * 100, HOURSAVAILABLE_I = TaMstrTypes.HOURSAVAILABLE_I - @curUnitsToPay * 100, DAYSWRDK = @curDaysWorked - TaMstrTypes.DAYSWRDK, WKSWRKD = @curWeeksWorked - TaMstrTypes.WKSWRKD from TATM1030 TaMstrTypes, TATX1030 TaTrxDtl where TaMstrTypes.EMPID_I = @EmpID and TaMstrTypes.TIMECODE_I = @curTimeCode and TaTrxDtl.ACCFLG = 0 FETCH NEXT FROM TimeCodesTrx INTO @curEmpID, @curJobTitle, @curDept, @curDivisionCode, @curTimeCode, @curTrxDate, @curUnitsToPay, @curDaysWorked, @curWeeksWorked, @curAccureTimeBenefits, @curCompTrxNum, @curPayCode; end CLOSE TimeCodesTrx; DEALLOCATE TimeCodesTrx; end insert into UPR30500 select EMPLOYID, Pay_Schedule, YEAR1, PERIODID, Workflow_Status from UPR10500 where EMPLOYID = @EmpID and Pay_Schedule = @PaySched and YEAR1 = @YEAR1 and PERIODID = @PERIODID and (Workflow_Status = 3 or Workflow_Status = 6) insert into UPR30501 select EMPLOYID, Pay_Schedule, YEAR1, PERIODID, TRXNUMBER, TRXDATE, PayCode, UNTSTOPY, DEPRTMNT, JOBTITLE, STATECD, LOCALTAX, SHFTCODE, DAYSWRDK, WKSWRKD, SEQNUMBR, CMMTTEXT from UPR10501 where EMPLOYID = @EmpID and Pay_Schedule = @PaySched and YEAR1 = @YEAR1 and PERIODID = @PERIODID and UNTSTOPY <> 0.0 insert into UPR30502 select EMPLOYID, Pay_Schedule, YEAR1, PERIODID, TRXNUMBER, LNSEQNBR, STRTTIME, ENDTIME, UNTSTOPY from UPR10502 where EMPLOYID = @EmpID and Pay_Schedule = @PaySched and YEAR1 = @YEAR1 and PERIODID = @PERIODID delete UPR10500 where EMPLOYID = @EmpID and Pay_Schedule = @PaySched and YEAR1 = @YEAR1 and PERIODID = @PERIODID delete UPR10501 where EMPLOYID = @EmpID and Pay_Schedule = @PaySched and YEAR1 = @YEAR1 and PERIODID = @PERIODID delete UPR10502 where EMPLOYID = @EmpID and Pay_Schedule = @PaySched and YEAR1 = @YEAR1 and PERIODID = @PERIODID