SCRIPTS - data area
- Specific document types in the IV30300 are missing a transaction source.
- Specific document types with invalid values of 0 in the DECPLCUR and DECPLQTY fields.
Specific document types in the IV30300 are missing a transaction source. Each document that is posted through Microsoft Dynamics GP should have a transaction source associated with it. Due to a posting interruptions or an integration/customization issues, this value may be missing. Use the script below to identify any documents that have a blank transaction source. All of these document types listed should have a transaction source. If they do not have a transaction source the records need to be fixed or removed.
1. Run the following statement against the company database in SQL Server Management Studio to identify transactions with this condition:
SELECT * FROM IV30300 WHERE TRXSORCE = '' and doctype in (1, 2, 3, 4, 5, 6, 7)
2. If any documents get returned using the script above, run a select statement on the document number listed against the following Inventory tables (IV30300, SEE30303, IV10200, IV10201) and investigate where the corruption is or if it’s only in the IV30300. It might be a corrupt record or it might be partially updated. Each line returned will have to be investigated and determined how to proceed with fixing. If it’s something that can be deleted, we suggest deleting based on DEX_ROW_ID.
DELETE IV30300 WHERE DEX_ROW_ID = XXX
Example from a case: Below is a select statement based on one of the documents that was returned in a case I worked on. Notice both lines being almost identical. The document should have only one line returned. I figured it must have been some type of posting interruption that caused the corruption because all the other tables in inventory posted just one line. I removed corrupt record missing the transaction source and the report printed.
Specific document types with invalid values of 0 in the DECPLCUR and DECPLQTY fields.
Note The DECPLCUR field refers to the captured currency decimal place and the DECPLQTY field refers to the captured quantity decimal place for the item. On all valid transactions this should be populated with a value.
- Run the following statement against the company database in SQL Server Management Studio to identify transactions with this condition:
Select * from IV30300 where (DECPLQTY = 0 or DECPLCUR = 0) and doctype in (1, 2, 3, 4, 5, 6, 7)
If you find documents with 0 decimal places, these need to be investigated. Correct the corrupted transaction by using SQL Query Analyzer by updating the fields to the appropriate value.
Decimal place values in the IV30300 are as follows:
|Value in table||Value shows in GP (Actual value)|
UPDATE IV30300 set DECPLQTY = 3 WHERE DEX_ROW_ID = XXX
If you still are unable to find the issue item/document, run a SQL Profile Trace and trap the error. Make sure to leave the error on the screen (don’t click past it) and then stop the trace. Typically, at the end of the trace you should see the document number that’s causing the error and can investigate from there. If you clean up the one item and still receive the error, create a new trace and see if it’s finding another culprit transaction.
Possible Workaround: Another option is to try printing with or without the “Use GL Posting date” on the Report Option to see if one option works verses the other. It might give you an option to print the report while you investigate the issue and identified the problem.
Article ID: 3210333 - Last Review: 1 Mar 2017 - Revision: 3