We are facing issue while upgrading from GP2010 (11.00.2320) (SP4) GP2013 SP1. The error we are getting while running the upgrade utilities
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 return(@@ERROR)
ERROR [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'PAYRCORD'.
My environment information:
OS: Win 2008 R2
SQL: MS SQL 2008 R2 SP2
We started the upgrade process from GP10, upgrade from GP10 to GP2010 was successful but when we started upgrade from GP2010 to GP2013 SP1 we started encountering the above error message.
Please help us to resolve this issue.
