Reset Transaction Date in GL20000 to match SOP30200 records

I'm not sure if this is possible. Very recently, One of our clients
posted the Sales Transactions of about 4,000 and posted to GL as
well.
After posting, he realized that all GL entries for these batches are
updated to one single date. On research, We found that the Postings
Settings are somehow changed to pick up the Posting Date from Batch
for General Entry Transactions.

Now, I'm just checking on a possibility if we could reset these
transactions dates by way of a SQL statement to copy the dates from
SOP30200 table to GL20000 for these transactions alone. Say, A
Transaction for which Document Date is 01/02/09 while GL Transaction
Date is 04/01/09 and now We need to reset the latter date with the
former.


0
janakirammp (688)
5/8/2009 3:08:33 PM
greatplains 29623 articles. 3 followers. Follow

4 Replies
712 Views

Similar Articles

[PageSpeed] 7

I can give this one a try.  Assuming you are posting in detail and do not use 
multicurrency it seems to me like it would work.  You would want to be sure 
to use enough fields to match up the correct transaction other than just the 
document number. It's possible to have the same document number in a 
different series or document type. Play around with your select statement to 
make sure you are targeting the correct GL trx. There is no primary key for 
the GL10000 table.



"Janakiram M.P." wrote:

> I'm not sure if this is possible. Very recently, One of our clients
> posted the Sales Transactions of about 4,000 and posted to GL as
> well.
> After posting, he realized that all GL entries for these batches are
> updated to one single date. On research, We found that the Postings
> Settings are somehow changed to pick up the Posting Date from Batch
> for General Entry Transactions.
> 
> Now, I'm just checking on a possibility if we could reset these
> transactions dates by way of a SQL statement to copy the dates from
> SOP30200 table to GL20000 for these transactions alone. Say, A
> Transaction for which Document Date is 01/02/09 while GL Transaction
> Date is 04/01/09 and now We need to reset the latter date with the
> former.
> 
> 
> 
0
LeslieVail (702)
5/8/2009 4:10:04 PM
You can use the follwing script:

UPDATE A SET A.TRXDATE = B.DOCDATE 
FROM GL20000 A 
    INNER JOIN SOP30200 B ON (A.ORDOCNUM = B.SOPNUMBE) AND (A.ORTRXTYP = 
B.SOPTYPE) AND (A.ORMSTRID = B.CUSTNMBR)
WHERE A.SERIES = 3 AND B.BACHNUMB = 'YourSOPBatchHere'

Run in a test environment first and make sure to reconcile your accounts to 
adjust the period balances.
 
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com


"Janakiram M.P." wrote:

> I'm not sure if this is possible. Very recently, One of our clients
> posted the Sales Transactions of about 4,000 and posted to GL as
> well.
> After posting, he realized that all GL entries for these batches are
> updated to one single date. On research, We found that the Postings
> Settings are somehow changed to pick up the Posting Date from Batch
> for General Entry Transactions.
> 
> Now, I'm just checking on a possibility if we could reset these
> transactions dates by way of a SQL statement to copy the dates from
> SOP30200 table to GL20000 for these transactions alone. Say, A
> Transaction for which Document Date is 01/02/09 while GL Transaction
> Date is 04/01/09 and now We need to reset the latter date with the
> former.
> 
> 
> 
0
MarianoGomez (3440)
5/8/2009 4:51:01 PM
Mariano you are irreplaceable.  Please never leave this forum! :)

"Mariano Gomez" wrote:

> You can use the follwing script:
> 
> UPDATE A SET A.TRXDATE = B.DOCDATE 
> FROM GL20000 A 
>     INNER JOIN SOP30200 B ON (A.ORDOCNUM = B.SOPNUMBE) AND (A.ORTRXTYP = 
> B.SOPTYPE) AND (A.ORMSTRID = B.CUSTNMBR)
> WHERE A.SERIES = 3 AND B.BACHNUMB = 'YourSOPBatchHere'
> 
> Run in a test environment first and make sure to reconcile your accounts to 
> adjust the period balances.
>  
> Best regards,
> --
> MG.-
> Mariano Gomez, MIS, MCP, PMP
> Maximum Global Business, LLC
> http://www.maximumglobalbusiness.com
> The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
> 
> 
> "Janakiram M.P." wrote:
> 
> > I'm not sure if this is possible. Very recently, One of our clients
> > posted the Sales Transactions of about 4,000 and posted to GL as
> > well.
> > After posting, he realized that all GL entries for these batches are
> > updated to one single date. On research, We found that the Postings
> > Settings are somehow changed to pick up the Posting Date from Batch
> > for General Entry Transactions.
> > 
> > Now, I'm just checking on a possibility if we could reset these
> > transactions dates by way of a SQL statement to copy the dates from
> > SOP30200 table to GL20000 for these transactions alone. Say, A
> > Transaction for which Document Date is 01/02/09 while GL Transaction
> > Date is 04/01/09 and now We need to reset the latter date with the
> > former.
> > 
> > 
> > 
0
LeslieVail (702)
5/8/2009 5:22:02 PM
Yes Leslie. You are Right. Mariano is simply the 'Best'. He's right
there always with the best solution!

Thanks
Janakiram M.P.
MCP-GP
0
janakirammp (688)
5/8/2009 7:27:20 PM
Reply:

Similar Artilces:

Receivings Transaction Entry Security
Limit the access per Document Type on the Receivings Transaction Entry window (per user). ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=bbcc5fdf-1f49-455b-a646-4b8087ba7c...

Recording Macros
How do I record a macro and NOT have my cell formulas show up in R1C1 style when I open the macro with Visual Basic oe How do I change all the cell formulas in a module once recorded ?? #1. I think that's the way the macro gets recorded. (I can't change it.) #2. depends on what you're doing...edit|replace or manually??? Richard wrote: > > How do I record a macro and NOT have my cell formulas show up in R1C1 style > when I open the macro with Visual Basic > > oe How do I change all the cell formulas in a module once recorded ?? -- Dave Peterson ...

Duplicate Customers
-- Jon Kahn Confluence Kayaks ...

Excel graphed trendline does not match derived equation
I have a scatter-point chart and I have been able to plot the linear trendline through those scatter points. I then formatted the linear trendline and got the equation for the line. However, I noticed that the y-intercept (7.9...) of the actual line that was graphed by Excel is not the same as the y-intercept (8.9573) for the equation that Excel derived from the graphed line. The actual Excel graphed line and the line the equation appear to be parallel to each other. As a result, entering data into the equation supposedly derived from the graphed line will not result in the line tha...

Transaction data truncated
When I import data from my financial institution all debit transactions start with "Share Withdrawal ATM *POS*"...and then lists the merchant. Since Money only imports the first 24 characters I can't see who the transaction was to - does anyone know of a way to change the settings so that it can import more characters? I know Quicken imports 32, but I don't like the fuctionality of that program. ...

Outlook 2003 Message column headers keep resetting
I keep seeing the column headers in the messsages pane reset to their Inbox defaults. This is really frustrating when I want to find an email I've sent as the To & Sent fields keep disappearing, to be replaced by the From & Received fields! Changing these back via the field chooser is both annoying & tedious - is there anyway to fix this issue? Create a new custom view with the layout you desire and switch between the default and the new one to change the view. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Wi...

Reset list
In Outlook2002 I can go to File, Open..., Other users folders... How can I reset that list so that it is empty? I have tried to delete the nk2-file but that didn�t seem to have any influence here. Regards Anette Open a lot of folders from someone else untill they are starting to fall of the list ;-) By recreating your e-mail profile the list will be gone. All info regarding this is kept in the registry; [HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles] Note that this key has never been created for manually editing so back-up the ke...

DATE FROM PREVIOUS RECORD
Hi, i want to show previous date on same form in another text box when i enter a new record on form . please tell me how solve this problem thanks Hi, This might work, create a button, click cancel to the wizard. Right click the button, go to properties. On the property sheet goto the Event Tab, click in the OnClick box and click the ... button. Select Code Builder. Now paste this in: 'start DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste App...

Dates and times formulas
I have a spreadsheet with a beginning date and time, in 2 columns. And in another 2 columns, an ending date and time. I would like to try to calculate the durations between the start date and ending date. If possible I would also like to not count the weekends. -Dan B. http://www.cpearson.com/excel/datetime.htm#AddingDates -- Kind Regards, Niek Otten Microsoft MVP - Excel "DB" <DB@discussions.microsoft.com> wrote in message news:2A8647CF-8184-4760-97FC-6168BD753A70@microsoft.com... >I have a spreadsheet with a beginning date and time, in 2 columns. And in &g...

Convert date (m/dd/yyyy) in text format into an excel recognised date
is there anyway to convert a TEXT field with a date into an excel recognised date? or the other way around - convert a date in excel into text output? Try Text>Data To Columns, third page in the wizard. -- HTH Bob "Rob P" <gwatcheater@gmail.com> wrote in message news:f574bab6-e53d-4d72-9fec-8c197cf174f3@j27g2000yqn.googlegroups.com... > is there anyway to convert a TEXT field with a date into an excel > recognised date? > > or the other way around - convert a date in excel into text output? ...

Highlight a selected record
I am trying to highlight a selected record of a continuous form. Conditional Formatting is not working because it highlights individual fields. If Conditional Formatting would highlight the entire row, it would be perfect. I created a txtBackground text box to size of all my fields, sent it the back—behind the fields. Use the txtBackground as the control, but when it has focus it covers the data. Below is the code I used: Sub HighlightControl(ctl As Control) On Error Resume Next ctl.BackColor = 65535 End Sub Sub UnhighlightControl(ctl As...

Date and Timestamp in Filename
I have a spreadsheet that is modified several times a day and by several people. Is it possible to somehow insert the date and timestamp (perhaps with a macro?) into the filename? It is very difficult to get users to use abbreviations/version numbers etc to show when the last modified spreadsheet was used - many "forget". If there was a way that automatically put the date and time into the filename then users will know which is the most recent document to use - and not have any excuses! many thanks peeps! Emily, Pretty easy ActiveWorkbook.SaveAs Filename:=sFilename &...

reset a combobox
hi i'm kinda new on excel. i want to create a button command linked to combobox; so that when i click on the button, it reset the combobox to nil. thxns in advance Girlie Set the Listindex property to -1 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "girlie" <pris1_lee@yahoo.co.uk> wrote in message news:1159246903.687630.171770@d34g2000cwd.googlegroups.com... > hi > > i'm kinda new on excel. > > i want to create a button command linked to combobox; so that when i > click on the button, it reset the co...

A query that shows "deletable" records
Hello Can anyone tell me how to find records in a table that has no related records in two other tables? I want to find the records that I can delete without having to just try record after record. It woud be nice if I also could delete records from that query. Thanks in advance. Lasse T ------------ Lasse I believe the Query Wizard includes a "not in" query. It sounds like you are trying to find records in Table1 that are "not it" Table2. Regards Jeff Boyce Microsoft Office/Access MVP "Lasse T" <cokew@home.se> wrote in message news:ICDMh.36...

Adding months to dates
Is there a way to add months to dates. For example, I want to have a formula to add 48 months to 08/24/00, so the formula will present 08/24/04. If I multiply 48 * 30 + the date, I get 08/03/04. If I divide 48 by 12 times 365, I get 08/23/04. I would presume there's an easy formula for this. Help. Thank in advance. Corey Hi With date in A1: =MIN(DATE(YEAR(A1),MONTH(A1)+48,DAY(A1));DATE(YEAR(A1),MONTH(A1)+1+48,0)) Arvi Laanemets "Corey" <anonymous@discussions.microsoft.com> wrote in message news:000101c4a4b8$8e4bda50$a601280a@phx.gbl... > Is there a way to a...

reset
the mail incoming to irene ...

Transaction Matching #2
What is with the transaction matching in 2006? My payments usually clear after the date that I have them set up in 'my bills' but when I go to match, they aren't there to choose from! UGH! "FrustratedinFaribault" <FrustratedinFaribault@discussions.microsoft.com> wrote in message news:2AE409D7-293E-4E48-83FB-0A22FBEC09C5@microsoft.com... > What is with the transaction matching in 2006? My payments usually clear > after the date that I have them set up in 'my bills' but when I go to > match, > they aren't there to choose from! What...

Calculating # of Months B/T Dates
I have a spreadsheet with dates in column F and column H. I want to format a cell to calculate the number of months that have lapsed between these two columns. I tried this formula: =DATEDIF(F2,H2,"m") But it doesn't give me exactly what I want. I want the result to give me fractions of months. For example: Column F = 09/28/2000 Column H = 11/06/2000 No. of months b/t = 1.30 Please forgive me if this question has been asked and answered many times over, but I could not find anything when I performed a search. TIA It all depends on how you define months. The difference ...

Missing Transactions
I have had some transactions for a specific payee disappear for a series of months. I know the transactions were in the register in previous months as I have reconciled and created reports using the information. The items are autodrafts that I download directly from the bank. Transactions for this payee have disappeared for activity between March 2004 and April 2005. All other payee transactions in those months are still in my register. I am viewing all transactions for all dates and have no idea what would cause these transactions to go missing. I do not have a backup or archive ...

Money 2005 duplicate transactions
When I download statements directly from Bank of America, the downloaded transactions match up nicely with my manually entered transactions and I just have to "accept" them. However, when I update through Money 2005 (I believe through Yodlee), instead of retrieving the transactions directly from BofA, the downloaded entries are input into the register as new transactions creating duplicates. If I just erase the new entry, it will create another one the next time I update. I have to erase the old entry and re-input all the data into the new transaction. I want to download f...

Date Validation List not working
I have a spread sheet with Date Validation List in it and it works fine but when I send the file by email to someone else the DVL doesn't work. The little thingy to the right of the cell doesn't appear. What's wrong? I have Excel 97 and so does the other dude. Look if the other dude is using freeze panes, that is a known bug (try to unfreeze the panes) -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) <burp@easy.com> wrote in message news:q3l7e01mr1rmessvn84r1lnrlc33cmlnka@4ax.com... > I have...

employee grant + etrade
Hi All - (I'll try to keep this as short as possible.) I have configured Money 2004 Deluxe to track a 1,000 share stock option grant from my employer (details on how this is setup follow.) This grant is managed through E*Trade, so I have configured an Investment account in Money and successfully configured the corresponding online services. I recently exercised 250 options through E*Trade and I can not, for the life of me, get the downloaded online register to match up with my local register(s). In a nutshell, the "sell" from my stock option only recorded the net gain ...

Workbook reference is reset to [0] in chart data source spec
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi <br> I try to create some dynamic charts to a worksheet using named variables and it works great as long as I keep the workbook open, but when I close it and re-open it the workbook reference has been reset to [0] and the chart is not dynamic anymore. What do I do wrong? <br><br>In detail, this is what I do: <br> I define the names: <li> chtRange to =Variables!$B$4, where Variables!$B$4 contains =COUNTA(Pace!C3:CU3)<br><br><li> chtLen to =Variables!$B$3, where Var...

Date Range Search
I have a form that has several text boxes for users to enter criteria for a search. Each type of search has a separate button that opens a search form based on criteria in the text boxes (yes, I used the wizard). This all works fine, except for my date range search, because the wizard doesn't offer an option to use the "between" function. I would imagine the button should work fine if I use it and just go into the code builder and replace the criteria portion of the code to filter out the records between the "me.FromDate" and "me.ToDate" text ...

dates from database file won't reformat
I am working with data created from a database and saved as an excel file. The dates appear to be normal (ex. 12/1/03) but i nthe formula bar an apostrophe is shown. In excel these dates will not reformat with the usual command. How can I get this data to reformat to other date formats? Crystal Use DATEVALUE(A2) and format as required. Andy. "Crystal" <anonymous@discussions.microsoft.com> wrote in message news:0c5c01c3ba7c$7581fcb0$a301280a@phx.gbl... > I am working with data created from a database and saved > as an excel file. The dates appear to be normal ...