I have tracked the error down this this SQL statement. It errors on 2 of the 5 companies on this statement. The error thrown is:
Msg 207, Level 16, State 1, Procedure uprCreateTrxDatafromTimecards, Line 2
Invalid column name 'PAYRCORD'.
Msg 8101, Level 16, State 1, Procedure uprCreateTrxDatafromTimecards, Line 2
An explicit value for the identity column in table 'TATX1030' can only be specified when a column list is used and IDENTITY_INSERT is ON.
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)
I am checking the definition of the TATX1030 table to see if someone changed the structure. If anyone has ever seen this please let me know.
