apitech
asked on
ASP.Net web application on SQL 2000 box: Cannot insert the value NULL into column
Below is the error message that I get when running a 401k ASP.Net web application on a SQL 2000 (8.00.2039) server running .Net Framework 3.5. The error message speaks to a field in our "MBSCUSTOM" database called "E401K". Modifying this field to allow for NULLs is out of the question.
This application has been running just fine for years, literally. Then, earlier this week, it stopped working and pulls the error message below. Nothing has been installed on the server over this past week, so I don't get it.
The choices, before clicking the Retrieve button, are "All", "Monthly", "Bi-Weekly", and "Weekly". What's funny is that the only time this error is retrieved is by clicking the "All" selection. The others work fine.
I seriously doubt that this is a database issue. I think that it is something related to the web app itself.
The app was originally designed to work in a .Net Framework 1.1 environment. Since that was years ago, do you suppose that the app is outdated and needs to be replaced?
Anyway, here is the error. Please let me know your thoughts:
Cannot insert the value NULL into column 'E401K', table 'MBSCUSTOM.dbo.Audit401KDe tail'; column does not allow nulls. INSERT fails. Warning: The table '#HRINFO' has been created but its maximum row size (8354) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. Warning: The table '#HRINFO' has been created but its maximum row size (8351) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
This application has been running just fine for years, literally. Then, earlier this week, it stopped working and pulls the error message below. Nothing has been installed on the server over this past week, so I don't get it.
The choices, before clicking the Retrieve button, are "All", "Monthly", "Bi-Weekly", and "Weekly". What's funny is that the only time this error is retrieved is by clicking the "All" selection. The others work fine.
I seriously doubt that this is a database issue. I think that it is something related to the web app itself.
The app was originally designed to work in a .Net Framework 1.1 environment. Since that was years ago, do you suppose that the app is outdated and needs to be replaced?
Anyway, here is the error. Please let me know your thoughts:
Cannot insert the value NULL into column 'E401K', table 'MBSCUSTOM.dbo.Audit401KDe
INSERT MBSCUSTOM.dbo.Audit401KDetail
(
hid,PNUM, PRDATE, SSN, ytdHours, ytdPay, ytd415
,[PT%], [AT%], PTDPay
,PFC, CurrPTEE, CurrPTER
,CurrAT, Loan
,LoanPMT, [ER%], EMPName, Status
,EN, HC, DOB, DOH, DOPE, DOT
,ADOH, DIV, ADD1
,ADD2, City, State, Zip, EmpType, CONV, SP
,E401K
)
SELECT @runID, '001111'
, CASE WHEN MCDATEPTEE IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATEPTEE AS datetime))
WHEN MCDATEPTER IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATEPTER AS datetime))
WHEN MCDATECURRLOAN IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATECURRLOAN AS datetime))
ELSE dbo.ConvertDate(GetDate()) END
,UP.SOCSCNUM, CASE WHEN YTDHOURS IS NOT NULL THEN RIGHT(dbo.Convert4V3Int(YTDHOURS),7) ELSE '0000000' END
, CASE WHEN YTDWAGES IS NOT NULL THEN RIGHT(dbo.ConvertS9V2Dec3(YTDWAGES),11) ELSE '00000000000' END,'00000000000'
, CASE WHEN #CurrPTEEAmounts.DEDNPRCT_1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTEEAmounts.DEDNPRCT_1),4) END, '0000'
,CASE WHEN #CurrPayAmounts.PTD_Pay IS NULL THEN '00000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPayAmounts.PTD_Pay),11) END
, CASE WHEN #CurrPTEEAmounts.PFC IS NOT NULL THEN CASE #CurrPTEEAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrPTERAmounts.PFC IS NOT NULL THEN CASE #CurrPTERAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrLoanAmounts.PFC IS NOT NULL THEN CASE #CurrLoanAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
ELSE '0' END
, CASE WHEN #CurrPTEEAmounts.PTEE IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTEEAmounts.PTEE),9) END
, CASE WHEN #CurrPTERAmounts.PTER IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTERAmounts.PTER),9) END
, '000000000', CASE WHEN #CurrLoanAmounts.LoanPMT > 0 THEN '001' ELSE '000' END
, CASE WHEN #CurrLoanAmounts.LoanPMT IS NULL THEN '000000000' WHEN #CurrLoanAmounts.LoanPMT < 0 THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrLoanAmounts.LoanPMT),9) END
, CASE WHEN #CurrPTERAmounts.BNFPRCNT_1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTERAmounts.BNFPRCNT_1),4) END , dbo.rightpad(HR.EMPNAME)
, HR.HRSTATUS
,'000000',HIGHLYCOMP, dbo.ConvertDate(CAST(UP.BRTHDATE AS datetime))
, dbo.ConvertDate(CAST(UP.STRTDATE AS datetime)), DOPE
/*
, CASE WHEN HR.DOT <> '00000000' THEN dbo.ConvertDate(CAST(HR.DOT AS datetime)) ELSE HR.DOT END*/
, CASE WHEN HR.DOT IS NOT NULL THEN dbo.ConvertDate(CAST(HR.DOT AS datetime)) ELSE '00000000' END
, ADOH,@DIV, dbo.rightpad(LTRIM(RTRIM(ADDRESS1)))
, dbo.rightpad(LTRIM(RTRIM(ADDRESS2))), CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(CITY))),18) AS CHAR(18))
, CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(STATE))),2) AS CHAR(2)), CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(ZIPCODE))),9) AS CHAR(9))
, EMPCLASS,'00000000',SP
, RIGHT(dbo.ConvertS9V2Dec3(HR.UPRTRXAM),9)
FROM LII.dbo.UPR00100 UP
/** ALTER THE INNER JOINS
INNER JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
INNER JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
INNER JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID **/
LEFT JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
LEFT JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
LEFT JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID
LEFT JOIN #CurrPTEEAmounts ON UP.EMPLOYID = #CurrPTEEAmounts.EMPLOYID
LEFT JOIN #CurrPTERAmounts ON UP.EMPLOYID = #CurrPTERAmounts.EMPLOYID
LEFT JOIN #CurrLoanAmounts ON UP.EMPLOYID = #CurrLoanAmounts.EMPLOYID
ORDER BY UP.EMPLOYID
>>Warning: The table '#HRINFO' has been created but... <<
This error message is not coming from the code you posted. I suggest you post the CREATE TABLE #HRINFO ... and INSERT #HRINFO ... code, so that we can make recomendations.
>>I seriously doubt that this is a database issue. I think that it is something related to the web app itself.<<
Actually it is neither one. It is a user error: They are entering too much info :)
This error message is not coming from the code you posted. I suggest you post the CREATE TABLE #HRINFO ... and INSERT #HRINFO ... code, so that we can make recomendations.
>>I seriously doubt that this is a database issue. I think that it is something related to the web app itself.<<
Actually it is neither one. It is a user error: They are entering too much info :)
ASKER
I'm afraid that reb73's suggestion did not work. When I tried to apply the change to the stoed proc, I get that attached error.
On acperkins, I cannot get CREATE TABLE as this table is not created ny the code. It is a temp table that SQL just creates.
Other ideas?
Gee-Whiz.docx
On acperkins, I cannot get CREATE TABLE as this table is not created ny the code. It is a temp table that SQL just creates.
Other ideas?
Gee-Whiz.docx
>> I cannot get CREATE TABLE as this table is not created ny the code. It is a temp table that SQL just creates.<<
Then post the code where "SQL just creates" it.
Then post the code where "SQL just creates" it.
ASKER
Thanks, acperkins!
Below is the code! I appreciate any help that you can offer.
CREATE PROCEDURE Produce401KLBI
( @startDate as datetime,
@endDate as datetime,
@frequency as integer,
@runID as integer
,@DIV as CHAR(4)
,@rowCount as integer OUTPUT
)
AS
SET NOCOUNT ON
SET ANSI_PADDING ON
/***************
Declarations
****************/
DECLARE @errNum INT
DECLARE @strStep AS VARCHAR(128)
DECLARE @strERR AS VARCHAR(1000)
DECLARE @currYear as CHAR(4)
/******* JD 1/23/06
Changing date extraction code for testing
SET @currYear = DATEPART(yyyy,GetDate())
Also setting a first day parm
**********/
SET @currYear = DATEPART(yyyy,@startDate)
DECLARE @dayOne as char(10)
SELECT @dayOne = @currYear + '-01-01'
SELECT @strStep = 'Create CurrPTEEAmounts'
-- Get deduction amounts from the check detail table, put in #CurrPTEEAmounts
SELECT LBI.dbo.UPR30300.EMPLOYID, LEFT(LTRIM(RTRIM(MIN(LBI.d bo.UPR3030 0.PAYROLCD ))),1) as PFC, MAX(LBI.dbo.UPR00500.DEDNP RCT_1)AS DEDNPRCT_1, MAX(CHEKDATE) AS MCDATEPTEE, Sum(CASE [PYRLRTYP] WHEN 2 THEN [UPRTRXAM] ELSE 0 END) AS PTEE
INTO #CurrPTEEAmounts
FROM (LBI.dbo.UPR00500 INNER JOIN KDedCodes ON LBI.dbo.UPR00500.DEDUCTON = KDedCodes.PAYROLCD)
INNER JOIN LBI.dbo.UPR30300 ON (LBI.dbo.UPR00500.DEDUCTON = LBI.dbo.UPR30300.PAYROLCD) AND (LBI.dbo.UPR00500.EMPLOYID = LBI.dbo.UPR30300.EMPLOYID)
WHERE LBI.dbo.UPR30300.CHEKDATE > @startDate AND LBI.dbo.UPR30300.CHEKDATE <= @endDate
-- AND LBI.dbo.UPR00500.INACTIVE <> 1
GROUP BY LBI.dbo.UPR30300.EMPLOYID
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#CurrPTEEAmounts==='
--SELECT COUNT(EMPLOYID) FROM #CurrPTEEAmounts
--SELECT * FROM #CurrPTEEAmounts ORDER BY EMPLOYID
SELECT @strStep = 'Create CurrPTERAmounts'
-- Get bebefit/employer match amounts from the check detail table, put in #CurrPTERAmounts
SELECT LBI.dbo.UPR30300.EMPLOYID, LEFT(LTRIM (RTRIM(LBI .dbo.UPR30 300.PAYROL CD)),1) as PFC,LBI.dbo.UPR00600.BNFPR CNT_1, MAX(CHEKDATE) AS MCDATEPTER, Sum(CASE [PYRLRTYP] WHEN 3 THEN [UPRTRXAM] ELSE 0 END) AS PTER
INTO #CurrPTERAmounts
FROM LBI.dbo.UPR30300
INNER JOIN (LBI.dbo.UPR00600 INNER JOIN KBenCodes ON LBI.dbo.UPR00600.BENEFIT = KBenCodes.PAYROLCD) ON (LBI.dbo.UPR30300.EMPLOYID = LBI.dbo.UPR00600.EMPLOYID)
AND (LBI.dbo.UPR30300.PAYROLCD = LBI.dbo.UPR00600.BENEFIT)
WHERE LBI.dbo.UPR30300.CHEKDATE > @startDate And LBI.dbo.UPR30300.CHEKDATE <= @endDate
-- AND LBI.dbo.UPR00600.INACTIVE< >1
GROUP BY LBI.dbo.UPR30300.EMPLOYID, LBI.dbo.UPR30300.PAYROLCD, LBI.dbo.UPR00600.BNFPRCNT_ 1
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#CurrPTERAmounts==='
--SELECT COUNT(EMPLOYID) FROM #CurrPTERAmounts
--SELECT * FROM #CurrPTERAmounts ORDER BY EMPLOYID
SELECT @strStep = 'Create CurrLoanAmounts'
-- Get loan amounts from the check detail table, put in #CurrLoanAmounts
SELECT LBI.dbo.UPR30300.EMPLOYID, LEFT(LTRIM(RTRIM(LBI.dbo.U PR30300.PA YROLCD)),1 ) as PFC, MAX(CHEKDATE) AS MCDATECURRLOAN, Sum(CASE [PYRLRTYP] WHEN 2 THEN [UPRTRXAM] ELSE 0 END) AS LoanPMT
INTO #CurrLoanAmounts
FROM LBI.dbo.UPR30300
INNER JOIN KLoanCodes ON LBI.dbo.UPR30300.PAYROLCD = KLoanCodes.PAYROLCD
WHERE LBI.dbo.UPR30300.CHEKDATE > @startDate AND LBI.dbo.UPR30300.CHEKDATE <= @endDate
GROUP BY LBI.dbo.UPR30300.EMPLOYID, LBI.dbo.UPR30300.PAYROLCD
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#CurrLoanAmounts==='
--SELECT COUNT(EMPLOYID) FROM #CurrLoanAmounts
--SELECT COUNT(DISTINCT EMPLOYID ) FROM #CurrLoanAmounts
--SELECT * FROM #CurrLoanAmounts ORDER BY EMPLOYID
SELECT @strStep = 'Create CurrPayAmounts'
-- Get current paycheck amounts for employees, put in #CurrPayAmounts
-- This portion gets ALL employees in the check detail table
SELECT LBI.dbo.UPR30300.EMPLOYID, /* LEFT(LTRIM(RTRIM(LBI.dbo.U PR30300.PA YROLCD)),1 ) as PFC,*/ MAX(CHEKDATE) AS MCDATECURRPAY, Sum(CASE [PYRLRTYP] WHEN 1 THEN [UPRTRXAM] ELSE 0 END) AS PTD_Pay
INTO #CurrPayAmounts
FROM LBI.dbo.UPR30300 INNER JOIN vw_LBIPayCodes ON LBI.dbo.UPR30300.PAYROLCD = vw_LBIPayCodes.BSDONCDE
WHERE LBI.dbo.UPR30300.CHEKDATE > @startDate AND LBI.dbo.UPR30300.CHEKDATE <= @endDate
GROUP BY LBI.dbo.UPR30300.EMPLOYID- -, LBI.dbo.UPR30300.PAYROLCD
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#CurrPayAmounts==='
--SELECT COUNT(*) FROM #CurrPayAmounts
--SELECT DISTINCT EMPLOYID FROM #CurrPayAmounts
--SELECT * FROM #CurrPayAmounts ORDER BY EMPLOYID
SELECT @strStep = 'Get Employee Totals'
-- Gets current totals. If you run the procedure for a pay period that is before the current
-- pay period, then the totals will be wrong because they have been updated already with the
-- current pay period totals
SELECT U3.EMPLOYID
, SUM(MTDHOURS_1/100 + MTDHOURS_2/100 +MTDHOURS_3/100 +MTDHOURS_4/100 +MTDHOURS_5/100 +MTDHOURS_6/100 +MTDHOURS_7/100 +MTDHOURS_8/100 +MTDHOURS_9/100 +MTDHOURS_10/100 +MTDHOURS_11/100 + MTDHOURS_12/100) as YTDHOURS
, SUM(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3 + MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6 + MTDWAGES_7 + MTDWAGES_8+ MTDWAGES_9 + MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12) AS YTDWAGES
INTO #YTDInfo
FROM LBI.dbo.UPR30301 U3 INNER JOIN LBI.dbo.UPR00100 U0 ON U3.EMPLOYID = U0.EMPLOYID
WHERE U3.PYRLRTYP=1 and U3.YEAR1=@currYear
GROUP BY U3.EMPLOYID
--**OB** removed 'AND U0.INACTIVE=0' in where statement above
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#YTDInfo==='
--SELECT COUNT(EMPLOYID) FROM #YTDInfo
--SELECT COUNT(DISTINCT EMPLOYID) FROM #YTDInfo
--SELECT * FROM #YTDInfo ORDER BY EMPLOYID
SELECT @strStep = 'Get Employee Info'
-- Get employee info from the 100 and 102 tables, gets info for ALL employees
SELECT U0.EMPLOYID
, 0 AS HIGHLYCOMP
, U0.BRTHDATE
, '00' as HRSTATUS
, U0.STRTDATE
, '00000000' AS DOPE
, NULL AS DOT
, '00000000' AS ADOH
, U1.ADDRESS1
, U1.ADDRESS2
, U1.CITY
, U1.State
, REPLACE(U1.ZIPCODE,'-','') AS ZIPCODE
, CASE RIGHT(LTRIM(RTRIM(EMPLCLAS )),1)
WHEN 'H' THEN 'B'
WHEN 'S' THEN 'C'
ELSE 'Z'
END AS EMPCLASS
, 'N' AS SP
, 0.0 AS UPRTRXAM
, LTRIM(RTRIM(U0.LastNAME))+ ', ' + LTRIM(RTRIM(U0.FRSTNAME)) + ' ' + LTRIM(RTRIM(U0.MIDLNAME)) AS EMPNAME
, U0.INACTIVE
INTO #HRINFO
FROM LBI.dbo.UPR00100 U0
INNER JOIN LBI.dbo.UPR00102 U1 ON U0.EMPLOYID = U1.EMPLOYID
WHERE U0.EMPLOYID <> 'REPRINTED'
ALTER TABLE #HRINFO ALTER COLUMN DOT VARCHAR(10)
--UPDATE TERMINATED EMPLOYEES
UPDATE HR
SET HR.HRSTATUS='30', HR.DOT=CAST(YEAR(TERMINATI ONDATE_I) AS varchar(4)) + '-' + CAST (MONTH(TERMINATIONDATE_I) AS varchar(2))+ '-' + CAST (DAY(TERMINATIONDATE_I) AS varchar(2))
FROM #HRINFO HR INNER JOIN LBI.dbo.TE024230 TER
ON HR.EMPLOYID = TER.EMPID_I and HR.INACTIVE = 1
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
ALTER TABLE #HRINFO ALTER COLUMN UPRTRXAM NUMERIC(19,5)
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
UPDATE HR
SET UPRTRXAM=(CASE WHEN U3.TOTUPRTRXAM IS NOT NULL THEN U3.TOTUPRTRXAM ELSE 0.0 END)
FROM #HRINFO HR
LEFT JOIN (SELECT EMPLOYID, SUM(UPRTRXAM) as TOTUPRTRXAM
FROM LBI.dbo.UPR30300 U3
WHERE pyrlrtyp=3 and payrolcd like '%07229' and CHEKDATE > @startDate
AND CHEKDATE <= @endDate GROUP BY EMPLOYID) U3 ON HR.EMPLOYID = U3.EMPLOYID
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '==HR INFO=='
--SELECT * FROM #HRINFO
--SELECT @strStep = 'Create Temp table to hold affected employee ids'
--SELECT EMPLOYID INTO #AFFECTEDEMPS
--FROM
--(select DISTINCT(EMPLOYID) FROM #CurrPTEEAmounts
--UNION
--select DISTINCT(EMPLOYID) FROM #CurrPTERAmounts
--UNION
--select DISTINCT(EMPLOYID) FROM #CurrLoanAmounts) as UEMPS
--SELECT '==#AFFECTEDEMPS=='
--SELECT * FROM #AFFECTEDEMPS
SELECT @strStep = 'Create 401KAggregate and audit trails'
--MOved to Extract401K
--INSERT MBSCUSTOM.dbo.Audit401KHea der (runDate, startDate, endDate, frequency,numRows)
--SELECT GetDate(),@startDate,@endD ate,@frequ ency,0
--SET @runID = @@IDENTITY
IF @frequency = 0
BEGIN
INSERT MBSCUSTOM.dbo.Audit401KDet ail
(
hid,PNUM, PRDATE, SSN, ytdHours, ytdPay, ytd415
,[PT%], [AT%], PTDPay
,PFC, CurrPTEE, CurrPTER
,CurrAT, Loan
,LoanPMT, [ER%], EMPName, Status
,EN, HC, DOB, DOH, DOPE, DOT
,ADOH, DIV, ADD1
,ADD2, City, State, Zip, EmpType, CONV, SP
,E401K
)
SELECT @runID
, '001111'
, CASE WHEN MCDATEPTEE IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT EPTEE AS datetime))
WHEN MCDATEPTER IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT EPTER AS datetime))
WHEN MCDATECURRLOAN IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT ECURRLOAN AS datetime))
ELSE dbo.ConvertDate(GetDate())
END
, UP.SOCSCNUM, CASE WHEN YTDHOURS IS NOT NULL THEN RIGHT(dbo.Convert4V3Int(YT DHOURS),7) ELSE '0000000' END
, CASE WHEN YTDWAGES IS NOT NULL THEN RIGHT(dbo.ConvertS9V2Dec3( YTDWAGES), 11) ELSE '00000000000' END,'00000000000'
, CASE WHEN #CurrPTEEAmounts.DEDNPRCT_ 1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPTEEA mounts.DED NPRCT_1),4 ) END, '0000'
, CASE WHEN #CurrPayAmounts.PTD_Pay IS NULL THEN '00000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPayAm ounts.PTD_ Pay),11) END
, CASE WHEN #CurrPTEEAmounts.PFC IS NOT NULL THEN CASE #CurrPTEEAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrPTERAmounts.PFC IS NOT NULL THEN CASE #CurrPTERAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrLoanAmounts.PFC IS NOT NULL THEN CASE #CurrLoanAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
ELSE '0'
END
, CASE WHEN #CurrPTEEAmounts.PTEE IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPTEEA mounts.PTE E),9) END
, CASE WHEN #CurrPTERAmounts.PTER IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPTERA mounts.PTE R),9) END
, '000000000'
, CASE WHEN #CurrLoanAmounts.LoanPMT > 0 THEN '001' ELSE '000' END
, CASE WHEN #CurrLoanAmounts.LoanPMT IS NULL THEN '000000000' WHEN #CurrLoanAmounts.LoanPMT < 0 THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrLoanA mounts.Loa nPMT),9) END
, CASE WHEN #CurrPTERAmounts.BNFPRCNT_ 1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPTERA mounts.BNF PRCNT_1),4 ) END , dbo.rightpad(HR.EMPNAME)
, HR.HRSTATUS
, '000000'
, HIGHLYCOMP
, dbo.ConvertDate(CAST(UP.BR THDATE AS datetime))
, dbo.ConvertDate(CAST(UP.ST RTDATE AS datetime)), DOPE
/*, CASE WHEN HR.DOT <> '00000000' THEN dbo.ConvertDate(CAST(HR.DO T AS datetime)) ELSE HR.DOT END*/
, CASE WHEN HR.DOT IS NOT NULL THEN dbo.ConvertDate(CAST(HR.DO T AS datetime)) ELSE '00000000' END
, ADOH,@DIV, dbo.rightpad(LTRIM(RTRIM(A DDRESS1)))
, dbo.rightpad(LTRIM(RTRIM(A DDRESS2))) , CAST(LEFT(dbo.rightpad(LTR IM(RTRIM(C ITY))),18) AS CHAR(18))
, CAST(LEFT(dbo.rightpad(LTR IM(RTRIM(S TATE))),2) AS CHAR(2)), CAST(LEFT(dbo.rightpad(LTR IM(RTRIM(Z IPCODE))), 9) AS CHAR(9))
, EMPCLASS,'00000000'
, SP
, RIGHT(dbo.ConvertS9V2Dec3( HR.UPRTRXA M),9)
FROM LBI.dbo.UPR00100 UP
/** ALTER THE INNER JOINS
INNER JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
INNER JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
INNER JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID **/
LEFT JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
LEFT JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
LEFT JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID
LEFT JOIN #CurrPTEEAmounts ON UP.EMPLOYID = #CurrPTEEAmounts.EMPLOYID
LEFT JOIN #CurrPTERAmounts ON UP.EMPLOYID = #CurrPTERAmounts.EMPLOYID
LEFT JOIN #CurrLoanAmounts ON UP.EMPLOYID = #CurrLoanAmounts.EMPLOYID
WHERE UP.EMPLOYID <> 'REPRINTED'
ORDER BY UP.EMPLOYID
END
ELSE
BEGIN
INSERT MBSCUSTOM.dbo.Audit401KDet ail
(
hid,PNUM, PRDATE, SSN, ytdHours, ytdPay, ytd415
,[PT%], [AT%], PTDPay
,PFC, CurrPTEE, CurrPTER
,CurrAT, Loan
,LoanPMT, [ER%], EMPName, Status
,EN, HC, DOB, DOH, DOPE, DOT
,ADOH, DIV, ADD1
,ADD2, City, State, Zip, EmpType, CONV, SP
,E401K
)
SELECT @runID, '001111'
, CASE WHEN MCDATEPTEE IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT EPTEE AS datetime))
WHEN MCDATEPTER IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT EPTER AS datetime))
WHEN MCDATECURRLOAN IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT ECURRLOAN AS datetime))
ELSE dbo.ConvertDate(GetDate())
END
, UP.SOCSCNUM
, CASE WHEN YTDHOURS IS NOT NULL THEN RIGHT(dbo.Convert4V3Int(YT DHOURS),7) ELSE '0000000' END
, CASE WHEN YTDWAGES IS NOT NULL THEN RIGHT(dbo.ConvertS9V2Dec3( YTDWAGES), 11) ELSE '00000000000' END,'00000000000'
, CASE WHEN #CurrPTEEAmounts.DEDNPRCT_ 1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPTEEA mounts.DED NPRCT_1),4 ) END, '0000'
, CASE WHEN #CurrPayAmounts.PTD_Pay IS NULL THEN '00000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPayAm ounts.PTD_ Pay),11) END
, CASE WHEN #CurrPTEEAmounts.PFC IS NOT NULL THEN CASE #CurrPTEEAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrPTERAmounts.PFC IS NOT NULL THEN CASE #CurrPTERAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrLoanAmounts.PFC IS NOT NULL THEN CASE #CurrLoanAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
ELSE '0'
END
, CASE WHEN #CurrPTEEAmounts.PTEE IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPTEEA mounts.PTE E),9) END
, CASE WHEN #CurrPTERAmounts.PTER IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPTERA mounts.PTE R),9) END
, '000000000'
, CASE WHEN #CurrLoanAmounts.LoanPMT > 0 THEN '001' ELSE '000' END
, CASE WHEN #CurrLoanAmounts.LoanPMT IS NULL THEN '000000000' WHEN #CurrLoanAmounts.LoanPMT < 0 THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrLoanA mounts.Loa nPMT),9) END
, CASE WHEN #CurrPTERAmounts.BNFPRCNT_ 1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3( #CurrPTERA mounts.BNF PRCNT_1),4 ) END , dbo.rightpad(HR.EMPNAME)
, HR.HRSTATUS
, '000000'
, HIGHLYCOMP
, dbo.ConvertDate(CAST(UP.BR THDATE AS datetime))
, dbo.ConvertDate(CAST(UP.ST RTDATE AS datetime))
, DOPE
/* , CASE WHEN HR.DOT <> '00000000' THEN dbo.ConvertDate(CAST(HR.DO T AS datetime)) ELSE HR.DOT END*/
, CASE WHEN HR.DOT IS NOT NULL THEN dbo.ConvertDate(CAST(HR.DO T AS datetime)) ELSE '00000000' END
, ADOH,@DIV, dbo.rightpad(LTRIM(RTRIM(A DDRESS1)))
, dbo.rightpad(LTRIM(RTRIM(A DDRESS2))) , CAST(LEFT(dbo.rightpad(LTR IM(RTRIM(C ITY))),18) AS CHAR(18))
, CAST(LEFT(dbo.rightpad(LTR IM(RTRIM(S TATE))),2) AS CHAR(2)), CAST(LEFT(dbo.rightpad(LTR IM(RTRIM(Z IPCODE))), 9) AS CHAR(9))
, EMPCLASS,'00000000'
, SP
, RIGHT(dbo.ConvertS9V2Dec3( HR.UPRTRXA M),9)
FROM LBI.dbo.UPR00100 UP
/** ALTER THE INNER JOINS
INNER JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
INNER JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
INNER JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID **/
LEFT JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
LEFT JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
LEFT JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID
LEFT JOIN #CurrPTEEAmounts ON UP.EMPLOYID = #CurrPTEEAmounts.EMPLOYID
LEFT JOIN #CurrPTERAmounts ON UP.EMPLOYID = #CurrPTERAmounts.EMPLOYID
LEFT JOIN #CurrLoanAmounts ON UP.EMPLOYID = #CurrLoanAmounts.EMPLOYID
WHERE (#CurrPTEEAmounts.PFC = @frequency
OR #CurrPTERAmounts.PFC = @frequency
OR #CurrLoanAmounts.PFC = @frequency)
AND UP.EMPLOYID <> 'REPRINTED'
ORDER BY UP.EMPLOYID
END
SET @rowCount = @@ROWCOUNT
--SELECT * FROM #CurrPTEEAmounts
--SELECT * FROM #CurrPTERAmounts
--SELECT * FROM #CurrPayAmounts
--SELECT * FROM #CurrLoanAmounts
--SELECT * FROM #CurrentAmounts
--SELECT * FROM #HRINFO
--SELECT @rowCount 'row Count'
--SELECT * FROM Audit401KDetail
DROP TABLE #CurrPTEEAmounts
DROP TABLE #CurrPTERAmounts
DROP TABLE #CurrPayAmounts
DROP TABLE #CurrLoanAmounts
--DROP TABLE #CurrentAmounts
--DROP TABLE #401KAggregate
DROP TABLE #YTDInfo
DROP TABLE #HRINFO
RETURN 0
SET NOCOUNT OFF
SET ANSI_PADDING OFF
ErrorHandler:
SELECT @strERR = 'Error in procedure : ' + LTRIM(RTRIM(OBJECT_NAME(@@ PROCID))) + ' during step : ' + @strStep + '. Error Number : ' + CAST(@errNum AS VARCHAR(128))
RAISERROR (@strERR, 16, 1) WITH LOG
RETURN 99
GO
Below is the code! I appreciate any help that you can offer.
CREATE PROCEDURE Produce401KLBI
( @startDate as datetime,
@endDate as datetime,
@frequency as integer,
@runID as integer
,@DIV as CHAR(4)
,@rowCount as integer OUTPUT
)
AS
SET NOCOUNT ON
SET ANSI_PADDING ON
/***************
Declarations
****************/
DECLARE @errNum INT
DECLARE @strStep AS VARCHAR(128)
DECLARE @strERR AS VARCHAR(1000)
DECLARE @currYear as CHAR(4)
/******* JD 1/23/06
Changing date extraction code for testing
SET @currYear = DATEPART(yyyy,GetDate())
Also setting a first day parm
**********/
SET @currYear = DATEPART(yyyy,@startDate)
DECLARE @dayOne as char(10)
SELECT @dayOne = @currYear + '-01-01'
SELECT @strStep = 'Create CurrPTEEAmounts'
-- Get deduction amounts from the check detail table, put in #CurrPTEEAmounts
SELECT LBI.dbo.UPR30300.EMPLOYID,
INTO #CurrPTEEAmounts
FROM (LBI.dbo.UPR00500 INNER JOIN KDedCodes ON LBI.dbo.UPR00500.DEDUCTON = KDedCodes.PAYROLCD)
INNER JOIN LBI.dbo.UPR30300 ON (LBI.dbo.UPR00500.DEDUCTON
WHERE LBI.dbo.UPR30300.CHEKDATE > @startDate AND LBI.dbo.UPR30300.CHEKDATE <= @endDate
-- AND LBI.dbo.UPR00500.INACTIVE <> 1
GROUP BY LBI.dbo.UPR30300.EMPLOYID
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#CurrPTEEAmounts==='
--SELECT COUNT(EMPLOYID) FROM #CurrPTEEAmounts
--SELECT * FROM #CurrPTEEAmounts ORDER BY EMPLOYID
SELECT @strStep = 'Create CurrPTERAmounts'
-- Get bebefit/employer match amounts from the check detail table, put in #CurrPTERAmounts
SELECT LBI.dbo.UPR30300.EMPLOYID,
INTO #CurrPTERAmounts
FROM LBI.dbo.UPR30300
INNER JOIN (LBI.dbo.UPR00600 INNER JOIN KBenCodes ON LBI.dbo.UPR00600.BENEFIT = KBenCodes.PAYROLCD) ON (LBI.dbo.UPR30300.EMPLOYID
AND (LBI.dbo.UPR30300.PAYROLCD
WHERE LBI.dbo.UPR30300.CHEKDATE > @startDate And LBI.dbo.UPR30300.CHEKDATE <= @endDate
-- AND LBI.dbo.UPR00600.INACTIVE<
GROUP BY LBI.dbo.UPR30300.EMPLOYID,
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#CurrPTERAmounts==='
--SELECT COUNT(EMPLOYID) FROM #CurrPTERAmounts
--SELECT * FROM #CurrPTERAmounts ORDER BY EMPLOYID
SELECT @strStep = 'Create CurrLoanAmounts'
-- Get loan amounts from the check detail table, put in #CurrLoanAmounts
SELECT LBI.dbo.UPR30300.EMPLOYID,
INTO #CurrLoanAmounts
FROM LBI.dbo.UPR30300
INNER JOIN KLoanCodes ON LBI.dbo.UPR30300.PAYROLCD = KLoanCodes.PAYROLCD
WHERE LBI.dbo.UPR30300.CHEKDATE > @startDate AND LBI.dbo.UPR30300.CHEKDATE <= @endDate
GROUP BY LBI.dbo.UPR30300.EMPLOYID,
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#CurrLoanAmounts==='
--SELECT COUNT(EMPLOYID) FROM #CurrLoanAmounts
--SELECT COUNT(DISTINCT EMPLOYID ) FROM #CurrLoanAmounts
--SELECT * FROM #CurrLoanAmounts ORDER BY EMPLOYID
SELECT @strStep = 'Create CurrPayAmounts'
-- Get current paycheck amounts for employees, put in #CurrPayAmounts
-- This portion gets ALL employees in the check detail table
SELECT LBI.dbo.UPR30300.EMPLOYID,
INTO #CurrPayAmounts
FROM LBI.dbo.UPR30300 INNER JOIN vw_LBIPayCodes ON LBI.dbo.UPR30300.PAYROLCD = vw_LBIPayCodes.BSDONCDE
WHERE LBI.dbo.UPR30300.CHEKDATE > @startDate AND LBI.dbo.UPR30300.CHEKDATE <= @endDate
GROUP BY LBI.dbo.UPR30300.EMPLOYID-
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#CurrPayAmounts==='
--SELECT COUNT(*) FROM #CurrPayAmounts
--SELECT DISTINCT EMPLOYID FROM #CurrPayAmounts
--SELECT * FROM #CurrPayAmounts ORDER BY EMPLOYID
SELECT @strStep = 'Get Employee Totals'
-- Gets current totals. If you run the procedure for a pay period that is before the current
-- pay period, then the totals will be wrong because they have been updated already with the
-- current pay period totals
SELECT U3.EMPLOYID
, SUM(MTDHOURS_1/100 + MTDHOURS_2/100 +MTDHOURS_3/100 +MTDHOURS_4/100 +MTDHOURS_5/100 +MTDHOURS_6/100 +MTDHOURS_7/100 +MTDHOURS_8/100 +MTDHOURS_9/100 +MTDHOURS_10/100 +MTDHOURS_11/100 + MTDHOURS_12/100) as YTDHOURS
, SUM(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3 + MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6 + MTDWAGES_7 + MTDWAGES_8+ MTDWAGES_9 + MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12) AS YTDWAGES
INTO #YTDInfo
FROM LBI.dbo.UPR30301 U3 INNER JOIN LBI.dbo.UPR00100 U0 ON U3.EMPLOYID = U0.EMPLOYID
WHERE U3.PYRLRTYP=1 and U3.YEAR1=@currYear
GROUP BY U3.EMPLOYID
--**OB** removed 'AND U0.INACTIVE=0' in where statement above
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '===#YTDInfo==='
--SELECT COUNT(EMPLOYID) FROM #YTDInfo
--SELECT COUNT(DISTINCT EMPLOYID) FROM #YTDInfo
--SELECT * FROM #YTDInfo ORDER BY EMPLOYID
SELECT @strStep = 'Get Employee Info'
-- Get employee info from the 100 and 102 tables, gets info for ALL employees
SELECT U0.EMPLOYID
, 0 AS HIGHLYCOMP
, U0.BRTHDATE
, '00' as HRSTATUS
, U0.STRTDATE
, '00000000' AS DOPE
, NULL AS DOT
, '00000000' AS ADOH
, U1.ADDRESS1
, U1.ADDRESS2
, U1.CITY
, U1.State
, REPLACE(U1.ZIPCODE,'-','')
, CASE RIGHT(LTRIM(RTRIM(EMPLCLAS
WHEN 'H' THEN 'B'
WHEN 'S' THEN 'C'
ELSE 'Z'
END AS EMPCLASS
, 'N' AS SP
, 0.0 AS UPRTRXAM
, LTRIM(RTRIM(U0.LastNAME))+
, U0.INACTIVE
INTO #HRINFO
FROM LBI.dbo.UPR00100 U0
INNER JOIN LBI.dbo.UPR00102 U1 ON U0.EMPLOYID = U1.EMPLOYID
WHERE U0.EMPLOYID <> 'REPRINTED'
ALTER TABLE #HRINFO ALTER COLUMN DOT VARCHAR(10)
--UPDATE TERMINATED EMPLOYEES
UPDATE HR
SET HR.HRSTATUS='30', HR.DOT=CAST(YEAR(TERMINATI
FROM #HRINFO HR INNER JOIN LBI.dbo.TE024230 TER
ON HR.EMPLOYID = TER.EMPID_I and HR.INACTIVE = 1
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
ALTER TABLE #HRINFO ALTER COLUMN UPRTRXAM NUMERIC(19,5)
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
UPDATE HR
SET UPRTRXAM=(CASE WHEN U3.TOTUPRTRXAM IS NOT NULL THEN U3.TOTUPRTRXAM ELSE 0.0 END)
FROM #HRINFO HR
LEFT JOIN (SELECT EMPLOYID, SUM(UPRTRXAM) as TOTUPRTRXAM
FROM LBI.dbo.UPR30300 U3
WHERE pyrlrtyp=3 and payrolcd like '%07229' and CHEKDATE > @startDate
AND CHEKDATE <= @endDate GROUP BY EMPLOYID) U3 ON HR.EMPLOYID = U3.EMPLOYID
SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler
--SELECT '==HR INFO=='
--SELECT * FROM #HRINFO
--SELECT @strStep = 'Create Temp table to hold affected employee ids'
--SELECT EMPLOYID INTO #AFFECTEDEMPS
--FROM
--(select DISTINCT(EMPLOYID) FROM #CurrPTEEAmounts
--UNION
--select DISTINCT(EMPLOYID) FROM #CurrPTERAmounts
--UNION
--select DISTINCT(EMPLOYID) FROM #CurrLoanAmounts) as UEMPS
--SELECT '==#AFFECTEDEMPS=='
--SELECT * FROM #AFFECTEDEMPS
SELECT @strStep = 'Create 401KAggregate and audit trails'
--MOved to Extract401K
--INSERT MBSCUSTOM.dbo.Audit401KHea
--SELECT GetDate(),@startDate,@endD
--SET @runID = @@IDENTITY
IF @frequency = 0
BEGIN
INSERT MBSCUSTOM.dbo.Audit401KDet
(
hid,PNUM, PRDATE, SSN, ytdHours, ytdPay, ytd415
,[PT%], [AT%], PTDPay
,PFC, CurrPTEE, CurrPTER
,CurrAT, Loan
,LoanPMT, [ER%], EMPName, Status
,EN, HC, DOB, DOH, DOPE, DOT
,ADOH, DIV, ADD1
,ADD2, City, State, Zip, EmpType, CONV, SP
,E401K
)
SELECT @runID
, '001111'
, CASE WHEN MCDATEPTEE IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT
WHEN MCDATEPTER IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT
WHEN MCDATECURRLOAN IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT
ELSE dbo.ConvertDate(GetDate())
END
, UP.SOCSCNUM, CASE WHEN YTDHOURS IS NOT NULL THEN RIGHT(dbo.Convert4V3Int(YT
, CASE WHEN YTDWAGES IS NOT NULL THEN RIGHT(dbo.ConvertS9V2Dec3(
, CASE WHEN #CurrPTEEAmounts.DEDNPRCT_
, CASE WHEN #CurrPayAmounts.PTD_Pay IS NULL THEN '00000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(
, CASE WHEN #CurrPTEEAmounts.PFC IS NOT NULL THEN CASE #CurrPTEEAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrPTERAmounts.PFC IS NOT NULL THEN CASE #CurrPTERAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrLoanAmounts.PFC IS NOT NULL THEN CASE #CurrLoanAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
ELSE '0'
END
, CASE WHEN #CurrPTEEAmounts.PTEE IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(
, CASE WHEN #CurrPTERAmounts.PTER IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(
, '000000000'
, CASE WHEN #CurrLoanAmounts.LoanPMT > 0 THEN '001' ELSE '000' END
, CASE WHEN #CurrLoanAmounts.LoanPMT IS NULL THEN '000000000' WHEN #CurrLoanAmounts.LoanPMT < 0 THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(
, CASE WHEN #CurrPTERAmounts.BNFPRCNT_
, HR.HRSTATUS
, '000000'
, HIGHLYCOMP
, dbo.ConvertDate(CAST(UP.BR
, dbo.ConvertDate(CAST(UP.ST
/*, CASE WHEN HR.DOT <> '00000000' THEN dbo.ConvertDate(CAST(HR.DO
, CASE WHEN HR.DOT IS NOT NULL THEN dbo.ConvertDate(CAST(HR.DO
, ADOH,@DIV, dbo.rightpad(LTRIM(RTRIM(A
, dbo.rightpad(LTRIM(RTRIM(A
, CAST(LEFT(dbo.rightpad(LTR
, EMPCLASS,'00000000'
, SP
, RIGHT(dbo.ConvertS9V2Dec3(
FROM LBI.dbo.UPR00100 UP
/** ALTER THE INNER JOINS
INNER JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
INNER JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
INNER JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID **/
LEFT JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
LEFT JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
LEFT JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID
LEFT JOIN #CurrPTEEAmounts ON UP.EMPLOYID = #CurrPTEEAmounts.EMPLOYID
LEFT JOIN #CurrPTERAmounts ON UP.EMPLOYID = #CurrPTERAmounts.EMPLOYID
LEFT JOIN #CurrLoanAmounts ON UP.EMPLOYID = #CurrLoanAmounts.EMPLOYID
WHERE UP.EMPLOYID <> 'REPRINTED'
ORDER BY UP.EMPLOYID
END
ELSE
BEGIN
INSERT MBSCUSTOM.dbo.Audit401KDet
(
hid,PNUM, PRDATE, SSN, ytdHours, ytdPay, ytd415
,[PT%], [AT%], PTDPay
,PFC, CurrPTEE, CurrPTER
,CurrAT, Loan
,LoanPMT, [ER%], EMPName, Status
,EN, HC, DOB, DOH, DOPE, DOT
,ADOH, DIV, ADD1
,ADD2, City, State, Zip, EmpType, CONV, SP
,E401K
)
SELECT @runID, '001111'
, CASE WHEN MCDATEPTEE IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT
WHEN MCDATEPTER IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT
WHEN MCDATECURRLOAN IS NOT NULL THEN dbo.ConvertDate(CAST(MCDAT
ELSE dbo.ConvertDate(GetDate())
END
, UP.SOCSCNUM
, CASE WHEN YTDHOURS IS NOT NULL THEN RIGHT(dbo.Convert4V3Int(YT
, CASE WHEN YTDWAGES IS NOT NULL THEN RIGHT(dbo.ConvertS9V2Dec3(
, CASE WHEN #CurrPTEEAmounts.DEDNPRCT_
, CASE WHEN #CurrPayAmounts.PTD_Pay IS NULL THEN '00000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(
, CASE WHEN #CurrPTEEAmounts.PFC IS NOT NULL THEN CASE #CurrPTEEAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrPTERAmounts.PFC IS NOT NULL THEN CASE #CurrPTERAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
WHEN #CurrLoanAmounts.PFC IS NOT NULL THEN CASE #CurrLoanAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
ELSE '0'
END
, CASE WHEN #CurrPTEEAmounts.PTEE IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(
, CASE WHEN #CurrPTERAmounts.PTER IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(
, '000000000'
, CASE WHEN #CurrLoanAmounts.LoanPMT > 0 THEN '001' ELSE '000' END
, CASE WHEN #CurrLoanAmounts.LoanPMT IS NULL THEN '000000000' WHEN #CurrLoanAmounts.LoanPMT < 0 THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(
, CASE WHEN #CurrPTERAmounts.BNFPRCNT_
, HR.HRSTATUS
, '000000'
, HIGHLYCOMP
, dbo.ConvertDate(CAST(UP.BR
, dbo.ConvertDate(CAST(UP.ST
, DOPE
/* , CASE WHEN HR.DOT <> '00000000' THEN dbo.ConvertDate(CAST(HR.DO
, CASE WHEN HR.DOT IS NOT NULL THEN dbo.ConvertDate(CAST(HR.DO
, ADOH,@DIV, dbo.rightpad(LTRIM(RTRIM(A
, dbo.rightpad(LTRIM(RTRIM(A
, CAST(LEFT(dbo.rightpad(LTR
, EMPCLASS,'00000000'
, SP
, RIGHT(dbo.ConvertS9V2Dec3(
FROM LBI.dbo.UPR00100 UP
/** ALTER THE INNER JOINS
INNER JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
INNER JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
INNER JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID **/
LEFT JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
LEFT JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
LEFT JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID
LEFT JOIN #CurrPTEEAmounts ON UP.EMPLOYID = #CurrPTEEAmounts.EMPLOYID
LEFT JOIN #CurrPTERAmounts ON UP.EMPLOYID = #CurrPTERAmounts.EMPLOYID
LEFT JOIN #CurrLoanAmounts ON UP.EMPLOYID = #CurrLoanAmounts.EMPLOYID
WHERE (#CurrPTEEAmounts.PFC = @frequency
OR #CurrPTERAmounts.PFC = @frequency
OR #CurrLoanAmounts.PFC = @frequency)
AND UP.EMPLOYID <> 'REPRINTED'
ORDER BY UP.EMPLOYID
END
SET @rowCount = @@ROWCOUNT
--SELECT * FROM #CurrPTEEAmounts
--SELECT * FROM #CurrPTERAmounts
--SELECT * FROM #CurrPayAmounts
--SELECT * FROM #CurrLoanAmounts
--SELECT * FROM #CurrentAmounts
--SELECT * FROM #HRINFO
--SELECT @rowCount 'row Count'
--SELECT * FROM Audit401KDetail
DROP TABLE #CurrPTEEAmounts
DROP TABLE #CurrPTERAmounts
DROP TABLE #CurrPayAmounts
DROP TABLE #CurrLoanAmounts
--DROP TABLE #CurrentAmounts
--DROP TABLE #401KAggregate
DROP TABLE #YTDInfo
DROP TABLE #HRINFO
RETURN 0
SET NOCOUNT OFF
SET ANSI_PADDING OFF
ErrorHandler:
SELECT @strERR = 'Error in procedure : ' + LTRIM(RTRIM(OBJECT_NAME(@@
RAISERROR (@strERR, 16, 1) WITH LOG
RETURN 99
GO
#HRINFO is created based on the table LBI.dbo.UPR00100. Then the following columns are added:
DOT VARCHAR(10)
UPRTRXAM NUMERIC(19,5)
Please post the CREATE TABLE for the table LBI.dbo.UPR00100. I suspect that is where the problem lies.
Incidentally, you reported 2 problems, I am only addressing the waring message:
INSERT fails. Warning: The table '#HRINFO' has been created but its maximum row size (8354) exceeds the maximum number of bytes per row (8060).
DOT VARCHAR(10)
UPRTRXAM NUMERIC(19,5)
Please post the CREATE TABLE for the table LBI.dbo.UPR00100. I suspect that is where the problem lies.
Incidentally, you reported 2 problems, I am only addressing the waring message:
INSERT fails. Warning: The table '#HRINFO' has been created but its maximum row size (8354) exceeds the maximum number of bytes per row (8060).
ASKER
The UPR00100 table isn't created by this code. This table was installed in SQL in the LBI database when our accounting application was installed.
It's interesting that you should mention the UPRTRXAM column, though. That first error that you said that you are not addressing speaks to that field ,as the "E401K" field in that error message depends on the results of that field. But, the UPRTRXAM does not have any NULL values. So, I don't know why it is throwing up that first error.
It's interesting that you should mention the UPRTRXAM column, though. That first error that you said that you are not addressing speaks to that field ,as the "E401K" field in that error message depends on the results of that field. But, the UPRTRXAM does not have any NULL values. So, I don't know why it is throwing up that first error.
>>The UPR00100 table isn't created by this code.<<
I did not intend to imply that it was. But it is used to create the temp table #HRINFO and that is why inspecting that should reveal the problem.
I did not intend to imply that it was. But it is used to create the temp table #HRINFO and that is why inspecting that should reveal the problem.
ASKER
Disregard.....for some mysterious reason, the web application iss working now all of a sudden!
Thanks to all for your responses!!!!!
Thanks to all for your responses!!!!!
Fair enough. I suspect you still have the problem. But it will only become apparent until a user enters more data than you are currently seeing.
In the meantime, please close this question.
In the meantime, please close this question.
>>But it will only become apparent until a user enters more data than you are currently seeing<<
But I repeat myself #24403638
But I repeat myself #24403638
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try changing line 41 in your code above as follows -
The message "The table '#HRINFO' has been created but its maximum row size (8354) exceeds the maximum number of bytes per row (8060)" is just a warning as the defined size of the table exceeds the maximum allowed size of 8060 which is more or less a page (8K). This will result in an error only if the actual contents of a record exceed 8060 characters..
Open in new window