555
The transaction entries that we posted in GP are showing with Originating Post date value 00-00-0000. Due to this we are unable to close the period. Can you please help in guiding us to resolve this issue?
*This post is locked for comments
Fix the original external program is the best solution. This should have been determined in a test environment before turning the external program loose in your production environment.
There are two ways you can solve this problem. The best way is to have the original external program populate these two fields when it inserts it into the table. If this is not an option then you will need to create a SQL trigger to populate these fields when the insert happens into the GL10000 table.
Thank you...The update to 20000 is fine. But can you help to fix the GL 10000 insert. Does it need TRXDATE and TRXSRC to be inserted into ORPOSTDT and ORTRXSRC? Will that solve the issue? Currently a null date and an empty string are stored in these fields.
You will still need to fix the originating program that inserts the records in the GL10000/1 tables. Here is a script that can help to clean up the existing records in the GL20000.
update GL20000
set ORPSTDDT = TRXDATE,
ORTRXSRC = TRXSORCE
where ORPSTDDT = '1/1/1900'
Thanks for your follow up response. Sorry...I went into brief hiatus..! Yes, its a custom application built in-house. Understand that they create records in GL10000/01 which when "posted" in GP moves to GL20000 till the year is open. This happens fine. I can see many records with ORTRXSRC is blank for the problematic records. Does this info help you get something to help me out?
Yes, the date of 01/01/1900 make more sense as that is the default date in SQL for GP. What do you mean by the entries were created in GP through a program? Is this Integration Manager or some custom application? Are you still using this "Program"? Perhaps the "Program" was not fully ready to be deployed when it was used to create these records?Do the other originating fields have values? With the ORTRXSRC not having a value tells me this is a mistake of was a direct import into the ledger tables. Were these transactions coming from a subledger?
Thank you very much for a quick response. While accessing the database, found that the ORPSTDDT is actually 1900-01-01 00:00:00.000 (showing zeros in front-end) and ortrxsrc is blank for such transactions. There are close to 150 entries like that. Yes, we can do an update as you suggested. But wanted to know what could have caused this. These entries were created in GP through a program (now i suspect that the program might have missed out making some updates..!)
The original post date would have been the posting date coming from the originating subledger. Assuming that the transaction date equals the originating transaction date you could run a SQL script to update the originating trnsaction date. ( UPDATE GL20000 SET ORPSTDDT = TRXDATE WHERE ORPSTDDT = '00/00/00' ). Do the other orignating fields have values(i.e. Doc Numb and MstrNmbr)? How many of these are there? Is it just a few that resulted from a bad batch posting? Create a backup before running the update and then try to close.
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
