When you try to submit or return a timesheet in Project Time and Expense in Business Portal in Microsoft Dynamics GP, you receive the following error message:
Microsoft.Dynamics.Pma.PmaEntity.CostCategory [Cause]The database transaction has aborted due to inconsistent database operation. [Correction]Correct the data entered and retry the transaction.
Cause 1This problem may occur if the following conditions are true:
- Audit Trails in Microsoft Dynamics GP is installed.
- An audit is set up on a PDK table.
- The Business Portal user does not have access to the Audit database.
Cause 2This problem may occur if you create a timesheet template that contains a project ID and then delete the project ID in Project Accounting in Microsoft Dynamics GP. See resolution 2.
Cause 3This problem may occur if a project in a timesheet template has a closed status. See resolution 2.
Cause 4This problem may occur if damaged or duplicate records exist in the PDK10000 table or in the PDK10001 table. See resolution 3.
Cause 5This problem may occur if the Position field is not populated on the employee record. This could happen if you are using Canadian Payroll. See resolution 4.
Cause 6This problem may occur if there is an improper trigger on the PDK10000, PDK10001, PDK10500 or PDK10501 table. See resolution 5.
Cause 7This problem may occur if you have note indexes attached to your document that do not exist in the Billing Note Master table or notes in the Billing Note Master table that do not exist on the timesheet. See resolution 6.
Cause 8This problem may occur for timesheets if your reporting periods have been changed in Timesheet Setup when unprocessed timesheets exist. When you create a timesheet in BP that has the same Employee ID, Year and Reporting Period combination as has been used before, a -2 is automatically added at the end of the document number. If the First Day of Reporting Period, or Reporting Periods themselves in Timesheet Setup have been changed since timesheets were entered, and the Employee ID, Year and Reporting Period combination had been used before, we skip the regular document number of -1 and use one with a -2 at the end instead.
The timesheet document number is built using the “Employee ID-TS-Date” (i.e. 1018-TS-011609) methodology.
Note the difference from what is looked at for a duplicate timesheet in the paragraph above.
So when entering a timesheet for what is now period #2 (but was a different period before the Timesheet Setup changes were made), Business Portal did not “see” it as a duplicate, so the -2 was not put after it. As a result the error occurred because the timesheet number was already in the PDK10000 table.
See resolution 7.
Resolution 1To resolve this problem, grant access to the Audit database. To do this, follow these steps:
- Click Start, point to All Programs, point to Microsoft SQL Server, and then click Microsoft SQL Server Management Studio.
- Expand Microsoft SQL Servers, expand SQL Server Group, and then expand the instance of Microsoft SQL Server on which the Audit database is stored.
- Expand Security, and then click Logins.
- Right-click the Business Portal user, and then click Properties.
- Click the Database Access tab, and then click the Audit database.
- Click to select the Permit check box for the Audit database.
- In the Permit in Database Role list, click to select the DYNGRP check box.
- Click OK, and then exit Enterprise Manager.
Resolution 2To resolve this problem, create a new timesheet template. This will overwrite the existing one. To do this, follow these steps:
- In Business Portal, click Employee, click Project Time, and then click New.
- Create a new timesheet, and then click Save as template.
- When you are prompted to replace the existing template, click OK.
Resolution 3To resolve this problem, contact technical support for Microsoft Dynamics and related products. A support professional may be able to help you delete damaged or duplicate records in the PDK10000 table and in the PDK10001 table. For information about technical support for Microsoft Dynamics and related products, visit the following Microsoft Web site:
Resolution 4Even though you may be using Canadian Payroll, the Position (and Department) fields on the US employee card need to be populated. To resolve this problem, add the Position field on the employee card. To do this, follow these steps:
- In Microsoft Dynamics GP, click Cards, point to Payroll, and then click Employee.
- Enter the employee ID for which you receive the error message.
- Enter a valid position in the Position field.
- Click the Save button.
Resolution 5By default there are not any triggers on the PDK10000 PDK10001, PDK10500 and PDK10501 tables. Run this script (replacing the table name) in Microsoft SQL Server Management Studio to see if a trigger exists on the table. This trigger may need to be removed.
If a trigger exists, use this script to remove it and then try your Business Portal document again. In this example the name of the trigger is PDK10000Update.
ALTER TABLE PDK10000 DISABLE TRIGGER PDK10000Update
Resolution 6Using Microsoft SQL Server Management Studio, run this script against your company database to determine if there are billing notes assigned to the document that do not exist in the PDK billing note master table.
select * from PDK10001 where PDK_Billing_Note_ID not in (select PDK_Billing_Note_ID from PDK01601) and PDK_Billing_Note_ID <> ''
Then to see if there are notes that exist in the Billing Notes Master table that are not on a document, run this script.
select * from PDK01601 where PDK_Billing_Note_ID not in (select PDK_Billing_Note_ID from PDK10001) and PDK_Billing_Note_ID not in (select PDK_Billing_Note_ID from PDK10501)
If either of these scripts return results, the record will likely need to be removed from the table using a SQL delete statement. Contact Technical Support for further assistance if needed.
Resolution 7Review the timesheet documents in the PDK10000 table to see if the document number currently being used now, has been used before. The previous timesheet may need to be removed from the PDK10000 and PDK10001 tables before this new transaction can be entered if the existing reporting period setup needs to be used. The other option would be to reset the reporting period information back to what it was previously and then create a new timesheet.
Additional troubleshooting -1. Run the following script in Microsoft SQL Server Management Studio to see if there are any cost categories on the timesheet that have a status other than Open or Completed. You cannot enter transactions for cost categories in an Estimate, Closed or On Hold status.
select a.PDK_TS_No, a.PAPROJNUMBER, a.PACOSTCATID from PDK10001 a
join PA01301 b
a.PAPROJNUMBER = b.PAPROJNUMBER
and a.PACOSTCATID = b.PACOSTCATID
where b.PASTAT not in (1, 5)
2. Run the following script in Microsoft SQL Server Management Studio to see if there are any projects on the timesheet that have a status other than Open or Completed. You cannot enter transactions for projects in an Estimate, Closed or On Hold status.
select a.PDK_TS_No, a.PAPROJNUMBER from PDK10001 a
join PA01201 b
a.PAPROJNUMBER = b.PAPROJNUMBER
where b.PASTAT not in (1, 5)
3. Verify the value in the total quantities field (PDK_Total_Quantity) in the header (PDK10000) table match the sum of the lines quantity (PDK_Quantity) in the line table (PDK10001) for the timesheet. If they do not match, the timesheet is damaged and will need to be removed from the table using SQL. Contact Technical Support for further assistance if needed.