SOP change posting Date

Discussion:

(too old to reply)

dan

2009-07-03 04:56:45 UTC

Permalink

How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Janakiram M.P.

2009-07-03 06:37:01 UTC

Permalink

Dan,

If the Invoice is already posted in SOP but not posted in GL yet, You can
correct the Posting Date by the following query.

UPDATE SOP30200 SET GLPOSTDT = 'XX-XX-XXXX' WHERE SOPNUMBE = 'XXX' AND
SOPTYPE = 3
---3 is for invoices, Replace XX-XX-XXXX with your new posting date, XXX
with the invoice number.

Remember to change the new GL posting date in your GL Entry as well by going
to the General Entry and set the Transaction date to this new date and then
post otherwise GL and SOP will have two different dates.
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums at
http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Victoria [MVP]

2009-07-03 10:09:12 UTC

Permalink

My 2 cents:

I do not recommend changing transaction dates directly in the database.
Accounting is so date driven that this can get you into a lot of trouble in
the long run. If you change this in the SOP30200 table, there are other
tables that have this transaction information referenced that will now have
data that does not match. (Including GL tables, tax tables, RM tables,
summary tables in SOP, RM and Inventory.) Depending on what reporting you
do, you may run into issues with this.

How best to handle this depends greatly on the specific circumstances,
including the materiality of the transaction (it just might not be worth it
for $100), the type of reporting done, the controls in place, the dates
involved issue (is it June 2009 vs. July of 2009 or was the year entered
incorrectly which is a bigger issue), etc.

One option is to enter a return transaction with the incorrect date and
re-enter the invoice with the correct date. Another is to enter a GL
reversing entry. Both of these have pros and cons, and again, the best
solution will be different depending on circumstances.

If you absolutely must do this directly in the database, keep very good and
detailed notes on what you're doing (as this may come up as an issue in an
audit and could cast a shadow on the integrity of your system) and research
all the related tables that need to be updated.
--
Victoria Yudin
Dynamics GP MVP
Want to use Crystal Reports with GP?
http://www.flex-solutions.com/gpreports.html
blog: http://www.victoriayudin.com

Post by Janakiram M.P.
Dan,
If the Invoice is already posted in SOP but not posted in GL yet, You can
correct the Posting Date by the following query.
UPDATE SOP30200 SET GLPOSTDT = 'XX-XX-XXXX' WHERE SOPNUMBE = 'XXX' AND
SOPTYPE = 3
---3 is for invoices, Replace XX-XX-XXXX with your new posting date, XXX
with the invoice number.
Remember to change the new GL posting date in your GL Entry as well by going
to the General Entry and set the Transaction date to this new date and then
post otherwise GL and SOP will have two different dates.
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums at
http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Vaidy Mohan

2009-07-03 11:47:01 UTC

Permalink

I agree. I had faced this issue couple of months back when I had to
troubleshoot an issue involving SOP and GL balances. Later found that the
dates were entered (or manipulated) wrongly.

As suggested, we can reenter it with the correct date, since it is just one
transaction and also do a correction on GL.

Never manipulate the SQL tables (even the relevant ones), for
posted/unposted transactions, as easily as thought. It may open a can of
worms later.
--
Thanks
Vaidy Mohan
www.vaidy-dyngp.com

Post by Victoria [MVP]
I do not recommend changing transaction dates directly in the database.
Accounting is so date driven that this can get you into a lot of trouble in
the long run. If you change this in the SOP30200 table, there are other
tables that have this transaction information referenced that will now have
data that does not match. (Including GL tables, tax tables, RM tables,
summary tables in SOP, RM and Inventory.) Depending on what reporting you
do, you may run into issues with this.
How best to handle this depends greatly on the specific circumstances,
including the materiality of the transaction (it just might not be worth it
for $100), the type of reporting done, the controls in place, the dates
involved issue (is it June 2009 vs. July of 2009 or was the year entered
incorrectly which is a bigger issue), etc.
One option is to enter a return transaction with the incorrect date and
re-enter the invoice with the correct date. Another is to enter a GL
reversing entry. Both of these have pros and cons, and again, the best
solution will be different depending on circumstances.
If you absolutely must do this directly in the database, keep very good and
detailed notes on what you're doing (as this may come up as an issue in an
audit and could cast a shadow on the integrity of your system) and research
all the related tables that need to be updated.
--
Victoria Yudin
Dynamics GP MVP
Want to use Crystal Reports with GP?
http://www.flex-solutions.com/gpreports.html
blog: http://www.victoriayudin.com

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Richard Whaley -- MVP 2006-2008

2009-07-03 13:56:04 UTC

Permalink

Lets see...it posted on Monday but I want to lie to history and say it posted
on Wednesday...NO don't

Ok so someone posted it to the wrong month. Make correcting entries. Thats
all

Changing posting dates is distorting the facts. Auditors distrust changed
dates, IRS auditors fine people for it, stockholders sell their stock,
....Its not right.

Make correcting entries with notes as to why. That is explainable in the
future when you are not there to remind every on why you changed the posting
dates on all of those transactions...and how many can you remember?
--
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users

Get our Free Tips and Tricks Newsletter and check out our books at
http://www.AccoladePublications.com

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Janakiram M.P.

2009-07-03 17:27:01 UTC

Permalink

Dan,

Everyone suggested the best they can. It all depends on the environment that
you operate. What others have suggested makes perfect sense in a US
Environment where you have to consider the consequences of having a perfect
Audit Trial.

If you just want to change the posting date in SOP, that is the way to do it
in back end which I gave. Remember that anytime back end changes mean we are
overriding the rules of accounting or Auditing.
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums at
http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by Richard Whaley -- MVP 2006-2008
Lets see...it posted on Monday but I want to lie to history and say it posted
on Wednesday...NO don't
Ok so someone posted it to the wrong month. Make correcting entries. Thats
all
Changing posting dates is distorting the facts. Auditors distrust changed
dates, IRS auditors fine people for it, stockholders sell their stock,
....Its not right.
Make correcting entries with notes as to why. That is explainable in the
future when you are not there to remind every on why you changed the posting
dates on all of those transactions...and how many can you remember?
--
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users
Get our Free Tips and Tricks Newsletter and check out our books at
http://www.AccoladePublications.com

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

dan

2009-07-04 03:21:20 UTC

Permalink

Post by Janakiram M.P.
Dan,
Everyone suggested the best they can. It all depends on the environment that
you operate. What others have suggested makes perfect sense in a US
Environment where you have to consider the consequences of having a perfect
Audit Trial.
If you just want to change the posting date in SOP, that is the way to do it
in back end which I gave. Remember that anytime back end changes mean we are
overriding the rules of accounting or Auditing.
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums athttp://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Wow so many good people here thanks for all the advise, My issue is
that all the transactions were imported from IM there are around
32,000 transactions, then posted on SOP series but not on GL. how can
I create a return or reverse an entry for 32,000 transactions
painlessly for that? I was thinking to void all of them using macro
then adjust the inventory entries without posting the inventory
adjustment transaction to the GL and then use IM to create all those
transactions again but I have to change the transaction number. With
the incorrect posting date, sales analysis are also incorrect. What is
the best solution to my problem?

Janakiram M.P.

2009-07-04 07:23:01 UTC

Permalink

Dan,

Whenever you are importing 32000 transactions like, you should have had a
backup right prior to import? Just restore the backup which will solve the
problem.

If you cannot restore a backup, it is a pain creating a return and doing
them all over again. You can use the Script that I gave you, using Mail Merge
feature, create it for 32,000 and just update the date. Remember to tkeep one
backup prior to this update and test yourself.

Otherwise, it'd be a big pain for you if it is 32K transactions. I assumed
this when you first wrote your post no one would want to come for help when
it is one or couple of transactions, you just would have created a return.
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums at
http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by dan

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Janakiram M.P.

2009-07-04 08:27:01 UTC

Permalink

Dan,

BTW, Which date exactly are you looking to change? Is it the Invoice Date in
SOP or Posting Date in SOP? Assume you have an invoice with date 01/07/2009.
Once you post the invoice, the Invoice date in SOP would be 01/07/2009 and by
default Posting Date would also default to 01/07/2009. Posting date would
default to your GL Transaction date also. The result would be all your
customer records and Sales would show based on Invoice Date.

I need you to give 1 example of your entry integrated based on the above.
What is the Posting Date you see in Invoice? What is the Invoice Date? You
can get this info when you go to Inquiry=>Sales Documents=>Enter the Document
Number, CLick History, and then go to the SOP Entry by clicking the
drill-down. Click the little arrow next to Date in the TRX Entry Inquiry
Zoom. You'll see the dates out there. Again, What is the GL TRX date for this
transaction in GL Batch? You can find this if you go the General entry for
this transaction and see the transaction date.
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums at
http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by dan

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

dan

2009-07-04 09:29:18 UTC

Permalink

Post by Janakiram M.P.
Dan,
BTW, Which date exactly are you looking to change? Is it the Invoice Date in
SOP or Posting Date in SOP? Assume you have an invoice with date 01/07/2009.
Once you post the invoice, the Invoice date in SOP would be 01/07/2009 and by
default Posting Date would also default to 01/07/2009. Posting date would
default to your GL Transaction date also. The result would be all your
customer records and Sales would show based on Invoice Date.
I need you to give 1 example of your entry integrated based on the above.
What is the Posting Date you see in Invoice? What is the Invoice Date? You
can get this info when you go to Inquiry=>Sales Documents=>Enter the Document
Number, CLick History, and then go to the SOP Entry by clicking the
drill-down. Click the little arrow next to Date in the TRX Entry Inquiry
Zoom. You'll see the dates out there. Again, What is the GL TRX date for this
transaction in GL Batch? You can find this if you go the General entry for
this transaction and see the transaction date.
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums athttp://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by dan

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Hello Jan

Im trying to change the posting date in SOP. I think GL posting should
also be change under Transactions> Financial >Batches > Transactions.
I posted all the transaction from an SOP batch thats why it carries
the date from that batch ID. All 32k transaction are inside one batch
id. Its really a big problem now. The backup cannot help for now
because it has been several days before I knew that there is an issue
on the posting date. Meaning several days of POP transactions will be
gone if I restore the backup. posting 32k transaction in one batch
took me 2days to complete.

example invoice date: 05/01/2009, batch id: SOPBATCH batch id posting
date: 04/02/2009... then transaction>sales>series post

Janakiram M.P.

2009-07-04 10:26:01 UTC

Permalink

Dan,

Got it. You know what? I've faced exactly similar situation before during an
implementation. I had a discussion with my client so he agreed to have it
changed at the backend and we made note of all transactions getting corrected
at the backend. I understood what's happening in your case. You have posted
all SOP Transactions with correct Invoice date. But, All transactions got
posted with Batch Date (1 single date) as far as Posting Date is concerned.
Now, this posting date is carried to GL as well where all transactions are
saved with 1 TRX date in GL. I'll tell you the impact of this. If you see you
SOP, all customers records will show correctly according to your invoice date
because For Sales Series, Invoice Date holds the key. But, Due to wrong
posting date, once the GL gets posted, all Transactions will reflect in 1
single period. I know how much pain it will be to correct by way of some
return or void. It doesnt look good either. Your solution is back end only.
Here is how I do it.

1. Post the GL Batches also. By doing this, You will have the correct
Invoice Date in SOP but a wrong posting date in GL. To correct the same in
both GL and SOP for Posting dates, You need to use two scripts.

Script1: To equate the Posting Date in SOP to Invoice Date in SOP
UPDATE SOP30200 SET GLPOSTDAT = DOCDATE WHERE SOPNUMBE = 'XXXX' AND SOPTYPE
= 3
--This will make sure your Posting date is equated to Invoice Date in SOP

Script2: To equate the Transaction Date in GL to Invoice Date in SOP

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 = 'SOPBATCH'
---Replace SOPBATCH with your batch IDs for all transactions that you want
to correct
This script will make sure the GL TRX Date is as same as your SOP Invoice
Dates.
After running this script, You need to run Reconcile in Financial to adjust
the Period balances and also run check Links once everything is completed for
Sales and Financial Series.

Good Luck
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums at
http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by dan

Post by Richard Whaley -- MVP 2006-2008
Lets see...it posted on Monday but I want to lie to history and say it posted
on Wednesday...NO don't
Ok so someone posted it to the wrong month. Make correcting entries.. Thats
all
Changing posting dates is distorting the facts. Auditors distrust changed
dates, IRS auditors fine people for it, stockholders sell their stock,
....Its not right.
Make correcting entries with notes as to why. That is explainable in the
future when you are not there to remind every on why you changed the posting
dates on all of those transactions...and how many can you remember?
--
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users
Get our Free Tips and Tricks Newsletter and check out our books at
http://www.AccoladePublications.com

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Hello Jan
Im trying to change the posting date in SOP. I think GL posting should
also be change under Transactions> Financial >Batches > Transactions.
I posted all the transaction from an SOP batch thats why it carries
the date from that batch ID. All 32k transaction are inside one batch
id. Its really a big problem now. The backup cannot help for now
because it has been several days before I knew that there is an issue
on the posting date. Meaning several days of POP transactions will be
gone if I restore the backup. posting 32k transaction in one batch
took me 2days to complete.
example invoice date: 05/01/2009, batch id: SOPBATCH batch id posting
date: 04/02/2009... then transaction>sales>series post

Janakiram M.P.

2009-07-04 10:52:01 UTC

Permalink

Dan,

In my script1 correct GLPOSTDAT with GLPOSTDT
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums at
http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by Janakiram M.P.
Dan,
Got it. You know what? I've faced exactly similar situation before during an
implementation. I had a discussion with my client so he agreed to have it
changed at the backend and we made note of all transactions getting corrected
at the backend. I understood what's happening in your case. You have posted
all SOP Transactions with correct Invoice date. But, All transactions got
posted with Batch Date (1 single date) as far as Posting Date is concerned.
Now, this posting date is carried to GL as well where all transactions are
saved with 1 TRX date in GL. I'll tell you the impact of this. If you see you
SOP, all customers records will show correctly according to your invoice date
because For Sales Series, Invoice Date holds the key. But, Due to wrong
posting date, once the GL gets posted, all Transactions will reflect in 1
single period. I know how much pain it will be to correct by way of some
return or void. It doesnt look good either. Your solution is back end only.
Here is how I do it.
1. Post the GL Batches also. By doing this, You will have the correct
Invoice Date in SOP but a wrong posting date in GL. To correct the same in
both GL and SOP for Posting dates, You need to use two scripts.
Script1: To equate the Posting Date in SOP to Invoice Date in SOP
UPDATE SOP30200 SET GLPOSTDAT = DOCDATE WHERE SOPNUMBE = 'XXXX' AND SOPTYPE
= 3
--This will make sure your Posting date is equated to Invoice Date in SOP
Script2: To equate the Transaction Date in GL to Invoice Date in SOP
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 = 'SOPBATCH'
---Replace SOPBATCH with your batch IDs for all transactions that you want
to correct
This script will make sure the GL TRX Date is as same as your SOP Invoice
Dates.
After running this script, You need to run Reconcile in Financial to adjust
the Period balances and also run check Links once everything is completed for
Sales and Financial Series.
Good Luck
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums at
http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by dan

Post by Richard Whaley -- MVP 2006-2008
Lets see...it posted on Monday but I want to lie to history and say it posted
on Wednesday...NO don't
Ok so someone posted it to the wrong month. Make correcting entries.. Thats
all
Changing posting dates is distorting the facts. Auditors distrust changed
dates, IRS auditors fine people for it, stockholders sell their stock,
....Its not right.
Make correcting entries with notes as to why. That is explainable in the
future when you are not there to remind every on why you changed the posting
dates on all of those transactions...and how many can you remember?
--
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users
Get our Free Tips and Tricks Newsletter and check out our books at
http://www.AccoladePublications.com

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Hello Jan
Im trying to change the posting date in SOP. I think GL posting should
also be change under Transactions> Financial >Batches > Transactions.
I posted all the transaction from an SOP batch thats why it carries
the date from that batch ID. All 32k transaction are inside one batch
id. Its really a big problem now. The backup cannot help for now
because it has been several days before I knew that there is an issue
on the posting date. Meaning several days of POP transactions will be
gone if I restore the backup. posting 32k transaction in one batch
took me 2days to complete.
example invoice date: 05/01/2009, batch id: SOPBATCH batch id posting
date: 04/02/2009... then transaction>sales>series post

dan

2009-07-04 19:03:40 UTC

Permalink

Post by Janakiram M.P.
Dan,
In my script1 correct GLPOSTDAT with GLPOSTDT
--
Thanks
Janakiram M.P.
MCP-GP
Note: If you are a Microsoft Partner, You can also Login to Dynamics GP
partner forums athttp://social.microsoft.com/Forums/en-US/partnerdynamicsgp/threads

Post by dan

Post by Richard Whaley -- MVP 2006-2008
Lets see...it posted on Monday but I want to lie to history and say it posted
on Wednesday...NO don't
Ok so someone posted it to the wrong month. Make correcting entries.. Thats
all
Changing posting dates is distorting the facts. Auditors distrust changed
dates, IRS auditors fine people for it, stockholders sell their stock,
....Its not right.
Make correcting entries with notes as to why. That is explainable in the
future when you are not there to remind every on why you changed the posting
dates on all of those transactions...and how many can you remember?
--
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users
Get our Free Tips and Tricks Newsletter and check out our books at
http://www.AccoladePublications.com

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Hello Jan
Im trying to change the posting date in SOP. I think GL posting should
also be change under Transactions> Financial >Batches > Transactions.
I posted all the transaction from an SOP batch thats why it carries
the date from that batch ID. All 32k transaction are inside one batch
id. Its really a big problem now. The backup cannot help for now
because it has been several days before I knew that there is an issue
on the posting date. Meaning several days of POP transactions will be
gone if I restore the backup. posting 32k transaction in one batch
took me 2days to complete.
example invoice date: 05/01/2009, batch id: SOPBATCH batch id posting
date: 04/02/2009... then transaction>sales>series post

Thank you Jan I have noted your script, But I need ask permission
first from my client if they will allow me fix it at the backend.
Again thanks very much

Mariano Gomez

2009-07-04 20:58:00 UTC

Permalink

The path to hell is paved with good intentions... try explaining to your
client that they need to enter 32,000 adjustments because some how screwed up.

Here is the script you will need to fix this problem -- and no, it is just
not fixing it in SOP! You also have to consider RM and GL.

DECLARE @myDate datetime;
DECLARE @myBatch char(15);

SET @myBatch = 'SOPBATCH'; -- replace with the batch you use in IM

DECLARE @trxSources table(SOPNUMBE char(21), TRXSORCE CHAR(13), GLPOSTDT
datetime);

UPDATE SOP30200 SET GLPOSTDT = DOCDATE
OUTPUT INSERTED.SOPNUMBE, INSERTED.TRXSORCE, INSERTED.GLPOSTDT INTO
@trxSources
WHERE BACHNUMB = @myBatch AND SOPTYPE = 3;

UPDATE A SET A.GLPOSTDT = B.GLPOSTDT
FROM SOP30100 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE);

UPDATE A SET A.GLPOSTDT = B.GLPOSTDT
FROM RM20101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, GLPOSTDT FROM
@trxSources) B ON (A.DOCNUMBR = B.SOPNUMBE) AND (A.RMDTYPAL = 1);

UPDATE A SET A.GLPOSTDT = B.GLPOSTDT
FROM RM30502 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE);

UPDATE A SET A.POSTEDDT = B.GLPOSTDT
FROM RM10101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE) AND (A.DOCNUMBR = B.SOPNUMBE);

UPDATE A SET A.POSTEDDT = B.GLPOSTDT
FROM RM30301 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE) AND (A.DOCNUMBR = B.SOPNUMBE);

UPDATE A SET A.TRXDATE = B.GLPOSTDT
FROM GL10000 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.BACHNUMB = B.TRXSORCE);

UPDATE A SET A.TRXDATE = B.GLPOSTDT
FROM GL20000 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.ORTRXSRC = B.TRXSORCE) AND (A.ORDOCNUM = B.SOPNUMBE);

You will also want to change your posting setting to grab the date from the
transaction and not from the batch as it is probably currently set.

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

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

dan

2009-07-06 03:55:25 UTC

Permalink

On Jul 5, 4:58 am, Mariano Gomez

Post by Mariano Gomez
The path to hell is paved with good intentions... try explaining to your
client that they need to enter 32,000 adjustments because some how screwed up.
Here is the script you will need to fix this problem -- and no, it is just
not fixing it in SOP! You also have to consider RM and GL.
datetime);
UPDATE SOP30200 SET GLPOSTDT = DOCDATE
OUTPUT INSERTED.SOPNUMBE, INSERTED.TRXSORCE, INSERTED.GLPOSTDT INTO
@trxSources
UPDATE A SET A.GLPOSTDT = B.GLPOSTDT
FROM SOP30100 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE);
UPDATE A SET A.GLPOSTDT = B.GLPOSTDT
FROM RM20101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, GLPOSTDT FROM
@trxSources) B ON (A.DOCNUMBR = B.SOPNUMBE) AND (A.RMDTYPAL = 1);
UPDATE A SET A.GLPOSTDT = B.GLPOSTDT
FROM RM30502 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE);
UPDATE A SET A.POSTEDDT = B.GLPOSTDT
FROM RM10101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE) AND (A.DOCNUMBR = B.SOPNUMBE);
UPDATE A SET A.POSTEDDT = B.GLPOSTDT
FROM RM30301 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE) AND (A.DOCNUMBR = B.SOPNUMBE);
UPDATE A SET A.TRXDATE = B.GLPOSTDT
FROM GL10000 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.BACHNUMB = B.TRXSORCE);
UPDATE A SET A.TRXDATE = B.GLPOSTDT
FROM GL20000 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.ORTRXSRC = B.TRXSORCE) AND (A.ORDOCNUM = B.SOPNUMBE);
You will also want to change your posting setting to grab the date from the
transaction and not from the batch as it is probably currently set.
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLChttp://www.maximumglobalbusiness.com
The Dynamics GP Blogster athttp://dynamicsgpblogster.blogspot.com

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Thanks MG,

What is the counterpart of 'Output' Clause in SQL 2K?

Thank you in advance

Dan

Mariano Gomez

2009-07-06 15:10:01 UTC

Permalink

There is no counterpart, but this should get you what you want:

INSERT INTO @trxSources (SOPNUMBE, TRXSORCE, GLPOSTDT)
SELECT SOPNUMBE, TRXSORCE, GLPOSTDT
WHERE BACHNUMB = @myBatch AND SOPTYPE = 3 AND (GLPOSTDT <> DOCDATE);

Ah, one more thing: MAKE SURE YOU TRY THIS IN A TEST ENVIRONMENT FIRST!

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

On Jul 5, 4:58 am, Mariano Gomez

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Thanks MG,
What is the counterpart of 'Output' Clause in SQL 2K?
Thank you in advance
Dan

Mariano Gomez

2009-07-06 15:17:02 UTC

Permalink

Just reposting... note you will need to convert the UPDATE/OUTPUT into an
INSERT/UPDATE statement, as follows:

INSERT INTO @trxSources (SOPNUMBE, TRXSORCE, GLPOSTDT)
SELECT SOPNUMBE, TRXSORCE, GLPOSTDT
WHERE BACHNUMB = @myBatch AND SOPTYPE = 3 AND (GLPOSTDT <> DOCDATE);

UPDATE SOP30200 SET GLPOSTDT = DOCDATE
WHERE (BACHNUMB = @myBatch) AND (SOPTYPE = 3) AND (GLPOSTDT <> DOCDATE);

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

Post by Mariano Gomez
SELECT SOPNUMBE, TRXSORCE, GLPOSTDT
Ah, one more thing: MAKE SURE YOU TRY THIS IN A TEST ENVIRONMENT FIRST!
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

On Jul 5, 4:58 am, Mariano Gomez

Post by dan
How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?

Thanks MG,
What is the counterpart of 'Output' Clause in SQL 2K?
Thank you in advance
Dan

dan

2009-07-07 06:58:56 UTC

Permalink

On Jul 6, 11:17 pm, Mariano Gomez

Post by Mariano Gomez
Just reposting... note you will need to convert the UPDATE/OUTPUT into an
SELECT SOPNUMBE, TRXSORCE, GLPOSTDT
UPDATE SOP30200 SET GLPOSTDT = DOCDATE
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLChttp://www.maximumglobalbusiness.com
The Dynamics GP Blogster athttp://dynamicsgpblogster.blogspot.com

On Jul 5, 4:58 am, Mariano Gomez

Post by Mariano Gomez
The path to hell is paved with good intentions... try explaining to your
client that they need to enter 32,000 adjustments because some how screwed up.
Here is the script you will need to fix this problem -- and no, it is just
not fixing it inSOP! You also have to consider RM and GL.
datetime);
UPDATE SOP30200 SET GLPOSTDT = DOCDATE
OUTPUT INSERTED.SOPNUMBE, INSERTED.TRXSORCE, INSERTED.GLPOSTDT INTO
@trxSources
UPDATE A SET A.GLPOSTDT = B.GLPOSTDT
FROM SOP30100 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE);
UPDATE A SET A.GLPOSTDT = B.GLPOSTDT
FROM RM20101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, GLPOSTDT FROM
@trxSources) B ON (A.DOCNUMBR = B.SOPNUMBE) AND (A.RMDTYPAL = 1);
UPDATE A SET A.GLPOSTDT = B.GLPOSTDT
FROM RM30502 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE);
UPDATE A SET A.POSTEDDT = B.GLPOSTDT
FROM RM10101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE) AND (A.DOCNUMBR = B.SOPNUMBE);
UPDATE A SET A.POSTEDDT = B.GLPOSTDT
FROM RM30301 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.TRXSORCE = B.TRXSORCE) AND (A.DOCNUMBR = B.SOPNUMBE);
UPDATE A SET A.TRXDATE = B.GLPOSTDT
FROM GL10000 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.BACHNUMB = B.TRXSORCE);
UPDATE A SET A.TRXDATE = B.GLPOSTDT
FROM GL20000 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.ORTRXSRC = B.TRXSORCE) AND (A.ORDOCNUM = B.SOPNUMBE);
You will also want to change your posting setting to grab the date from the
transaction and not from the batch as it is probably currently set.
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLChttp://www.maximumglobalbusiness.com
The Dynamics GP Blogster athttp://dynamicsgpblogster.blogspot.com

How do I correct posting date inSOPif the transaction has already
been posted inSOPSeries?

Thanks MG,
What is the counterpart of 'Output' Clause in SQL 2K?
Thank you in advance
Dan

Thanks MG,

Got it, just one last thing Ive noticed on updating GL10000 after
updating this table the trxdate carried the last date from the
trxsorce SOP30200. Should I change the BACHNUMB to DTAControlNum
instead?

UPDATE A SET A.TRXDATE = B.GLPOSTDT
FROM GL10000 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.DTAControlNum = B.SOPNUMBE);

Thanks,

Dan