Using SQL to be able to solve cases from the “other” side
Expense report rejected, but it cannot be resubmitted after making changes….
I recently had a case where the user submitted an expense report via EP. The company is a Canadian company and therefore needs all of the expense reports to be in CAD. The expense report was rejected in order for the user to make changes. At this point, it should have gone back to the user to make the change and resubmit.
The user made the changes and tried to submit but got an error: An unhandled error has occurred. To view details about this error, enable debugging in the web.config file or view the Windows Event logs. This was not a very descriptive error message.
To be able to get a better handle on what was happening I started testing with the actual data instead of test data.
One of the first things I did was to look at all of the posted transactions, then the pending transactions and finally the current transactions. The expense report was in none of those buckets. I needed to know if it was in the system at all.
I figured out which tables were being written to by going to an expense report that was in on of the aforementioned buckets, and did a right click to find the tables. Then, I opened up SQL management studio, selected the database, and ran a query to find out if the report data was in the tables. First I ran these 2 queries,
select * from TRVEXPTABLE where EXPNUMBER = ‘xx’
select * from TRVEXPTRANS where EXPNUMBER = ‘xx’
Those showed me that the data was there and the report was there. It also showed me that the report was in rejected status. According to this information, there should have been no reason if we went into EP as that employee, we should not be able to see it and re-submit.
I logged into EP as that employee that had submitted the report, and I could see the expense report, make changes, but could not re-submit it.
It looked like it was “hung” up between statuses. I needed to change the status back to created so that it could be re-submitted.
I went into the table from the SQL side and changed the status from rejected to created by running these queries that be able to reset it to create status.
update TRVEXPTABLE set APPROVALSTATUS = 1 where EXPNUMBER = ‘XX’ –XX representing the expense report number
update TRVEXPTRANS set APPROVALSTATUS = 1 where EXPNUMBER = ‘XX’ –XX representing the expense report number
After it was back to the created status, I was able to pull it up in EP and was able to submit.
Best practice is to solve issues from the user perspective or UI, but in this case, using the SQL management studio did the trick.
Updating data via SQL is not recommended but as a last resort it can be done in a test environment and then finish all transactions to the end. If there are no issue the process can be done in production after a current backup is in place.