SOP change posting Date

How do I correct posting date in SOP if the transaction has already
been posted in SOP Series?
0
7/3/2009 4:56:45 AM
greatplains 29623 articles. 3 followers. Follow

17 Replies
1791 Views

Similar Articles

[PageSpeed] 36

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


"dan" wrote:

> How do I correct posting date in SOP if the transaction has already
> been posted in SOP Series?
> 
0
janakirammp (688)
7/3/2009 6:37:01 AM
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


"Janakiram M.P." <janakirammp@gmail.com> wrote in message 
news:F284122D-7C1E-40F9-B996-661C5B27ABE9@microsoft.com...
> 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
>
>
> "dan" wrote:
>
>> How do I correct posting date in SOP if the transaction has already
>> been posted in SOP Series?
>> 

0
victoria (3340)
7/3/2009 10:09:12 AM
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


"Victoria [MVP]" wrote:

> 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
> 
> 
> "Janakiram M.P." <janakirammp@gmail.com> wrote in message 
> news:F284122D-7C1E-40F9-B996-661C5B27ABE9@microsoft.com...
> > 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
> >
> >
> > "dan" wrote:
> >
> >> How do I correct posting date in SOP if the transaction has already
> >> been posted in SOP Series?
> >> 
> 
0
VaidyMohan (20)
7/3/2009 11:47:01 AM
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



"dan" wrote:

> How do I correct posting date in SOP if the transaction has already
> been posted in SOP Series?
> 
0
info4071 (3004)
7/3/2009 1:56:04 PM
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


"Richard Whaley -- MVP 2006-2008" wrote:

> 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
> 
> 
> 
> "dan" wrote:
> 
> > How do I correct posting date in SOP if the transaction has already
> > been posted in SOP Series?
> > 
0
janakirammp (688)
7/3/2009 5:27:01 PM
On Jul 4, 1:27=A0am, Janakiram M.P. <janakira...@gmail.com> wrote:
> Dan,
>
> Everyone suggested the best they can. It all depends on the environment t=
hat
> you operate. What others have suggested makes perfect sense in a US
> Environment where you have to consider the consequences of having a perfe=
ct
> 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/partnerdynamics=
gp/threads
>
> "Richard Whaley -- MVP 2006-2008" wrote:
>
> > 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. =
=A0Thats
> > all
>
> > Changing posting dates is distorting the facts. =A0Auditors distrust ch=
anged
> > dates, IRS auditors fine people for it, stockholders sell their stock,
> > ....Its not right.
>
> > Make correcting entries with notes as to why. =A0That is explainable in=
 the
> > future when you are not there to remind every on why you changed the po=
sting
> > 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
>
> > "dan" wrote:
>
> > > 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?
0
7/4/2009 3:21:20 AM
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


"dan" wrote:

> On Jul 4, 1:27 am, Janakiram M.P. <janakira...@gmail.com> wrote:
> > 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
> >
> > "Richard Whaley -- MVP 2006-2008" wrote:
> >
> > > 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
> >
> > > "dan" wrote:
> >
> > > > 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?
> 
0
janakirammp (688)
7/4/2009 7:23:01 AM
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


"dan" wrote:

> On Jul 4, 1:27 am, Janakiram M.P. <janakira...@gmail.com> wrote:
> > 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
> >
> > "Richard Whaley -- MVP 2006-2008" wrote:
> >
> > > 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
> >
> > > "dan" wrote:
> >
> > > > 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?
> 
0
janakirammp (688)
7/4/2009 8:27:01 AM
On Jul 4, 4:27=A0pm, Janakiram M.P. <janakira...@gmail.com> wrote:
> 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/20=
09.
> Once you post the invoice, the Invoice date in SOP would be 01/07/2009 an=
d 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? Yo=
u
> can get this info when you go to Inquiry=3D>Sales Documents=3D>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 fo=
r
> 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/partnerdynamics=
gp/threads
>
> "dan" wrote:
> > On Jul 4, 1:27 am, Janakiram M.P. <janakira...@gmail.com> wrote:
> > > Dan,
>
> > > Everyone suggested the best they can. It all depends on the environme=
nt that
> > > you operate. What others have suggested makes perfect sense in a US
> > > Environment where you have to consider the consequences of having a p=
erfect
> > > Audit Trial.
>
> > > If you just want to change the posting date in SOP, that is the way t=
o 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/partnerdyna=
micsgp/threads
>
> > > "Richard Whaley -- MVP 2006-2008" wrote:
>
> > > > 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=
.. =A0Thats
> > > > all
>
> > > > Changing posting dates is distorting the facts. =A0Auditors distrus=
t changed
> > > > dates, IRS auditors fine people for it, stockholders sell their sto=
ck,
> > > > ....Its not right.
>
> > > > Make correcting entries with notes as to why. =A0That is explainabl=
e in the
> > > > future when you are not there to remind every on why you changed th=
e 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
>
> > > > "dan" wrote:
>
> > > > > How do I correct posting date in SOP if the transaction has alrea=
dy
> > > > > 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?

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
0
7/4/2009 9:29:18 AM
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


"dan" wrote:

> On Jul 4, 4:27 pm, Janakiram M.P. <janakira...@gmail.com> wrote:
> > 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
> >
> > "dan" wrote:
> > > On Jul 4, 1:27 am, Janakiram M.P. <janakira...@gmail.com> wrote:
> > > > 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
> >
> > > > "Richard Whaley -- MVP 2006-2008" wrote:
> >
> > > > > 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
> >
> > > > > "dan" wrote:
> >
> > > > > > 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?
> 
> 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
> 
0
janakirammp (688)
7/4/2009 10:26:01 AM
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


"Janakiram M.P." wrote:

> 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
> 
> 
> "dan" wrote:
> 
> > On Jul 4, 4:27 pm, Janakiram M.P. <janakira...@gmail.com> wrote:
> > > 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
> > >
> > > "dan" wrote:
> > > > On Jul 4, 1:27 am, Janakiram M.P. <janakira...@gmail.com> wrote:
> > > > > 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
> > >
> > > > > "Richard Whaley -- MVP 2006-2008" wrote:
> > >
> > > > > > 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
> > >
> > > > > > "dan" wrote:
> > >
> > > > > > > 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?
> > 
> > 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
> > 
0
janakirammp (688)
7/4/2009 10:52:01 AM
On Jul 4, 6:52=A0pm, Janakiram M.P. <janakira...@gmail.com> wrote:
> 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/partnerdynamics=
gp/threads
>
> "Janakiram M.P." wrote:
> > Dan,
>
> > Got it. You know what? I've faced exactly similar situation before duri=
ng 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 cor=
rected
> > at the backend. I understood what's happening in your case. You have po=
sted
> > all SOP Transactions with correct Invoice date. But, All transactions g=
ot
> > posted with Batch Date (1 single date) as far as Posting Date is concer=
ned.
> > 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 s=
ee you
> > SOP, all customers records will show correctly according to your invoic=
e 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 som=
e
> > return or void. It doesnt look good either. Your solution is back end o=
nly.
> > 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 =3D DOCDATE WHERE SOPNUMBE =3D 'XXXX' AND=
 SOPTYPE
> > =3D 3
> > --This will make sure your Posting date is equated to Invoice Date in S=
OP
>
> > Script2: To equate the Transaction Date in GL to Invoice Date in SOP
>
> > UPDATE A SET A.TRXDATE =3D B.DOCDATE
> > FROM GL20000 A
> > =A0 =A0 INNER JOIN SOP30200 B ON (A.ORDOCNUM =3D B.SOPNUMBE) AND (A.ORT=
RXTYP =3D
> > B.SOPTYPE) AND (A.ORMSTRID =3D B.CUSTNMBR)
> > WHERE A.SERIES =3D 3 AND B.BACHNUMB =3D 'SOPBATCH'
> > ---Replace SOPBATCH with your batch IDs for all transactions that you w=
ant
> > to correct
> > This script will make sure the GL TRX Date is as same as your SOP Invoi=
ce
> > Dates.
> > After running this script, You need to run Reconcile in Financial to ad=
just
> > the Period balances and also run check Links once everything is complet=
ed 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
>
> > "dan" wrote:
>
> > > On Jul 4, 4:27 pm, Janakiram M.P. <janakira...@gmail.com> wrote:
> > > > Dan,
>
> > > > BTW, Which date exactly are you looking to change? Is it the Invoic=
e 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/2=
009 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 y=
our
> > > > 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 Da=
te? You
> > > > can get this info when you go to Inquiry=3D>Sales Documents=3D>Ente=
r 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 In=
quiry
> > > > Zoom. You'll see the dates out there. Again, What is the GL TRX dat=
e for this
> > > > transaction in GL Batch? You can find this if you go the General en=
try 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 Dynamic=
s GP
> > > > partner forums athttp://social.microsoft.com/Forums/en-US/partnerdy=
namicsgp/threads
>
> > > > "dan" wrote:
> > > > > On Jul 4, 1:27 am, Janakiram M.P. <janakira...@gmail.com> wrote:
> > > > > > Dan,
>
> > > > > > Everyone suggested the best they can. It all depends on the env=
ironment that
> > > > > > you operate. What others have suggested makes perfect sense in =
a US
> > > > > > Environment where you have to consider the consequences of havi=
ng 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 change=
s 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 Dyn=
amics GP
> > > > > > partner forums athttp://social.microsoft.com/Forums/en-US/partn=
erdynamicsgp/threads
>
> > > > > > "Richard Whaley -- MVP 2006-2008" wrote:
>
> > > > > > > Lets see...it posted on Monday but I want to lie to history a=
nd say it posted
> > > > > > > on Wednesday...NO don't
>
> > > > > > > Ok so someone posted it to the wrong month. Make correcting e=
ntries.. =A0Thats
> > > > > > > all
>
> > > > > > > Changing posting dates is distorting the facts. =A0Auditors d=
istrust changed
> > > > > > > dates, IRS auditors fine people for it, stockholders sell the=
ir stock,
> > > > > > > ....Its not right.
>
> > > > > > > Make correcting entries with notes as to why. =A0That is expl=
ainable in the
> > > > > > > future when you are not there to remind every on why you chan=
ged the posting
> > > > > > > dates on all of those transactions...and how many can you rem=
ember?
>
> > > > > > > --
> > > > > > > Richard L. Whaley
> > > > > > > Author / Consultant / MVP 2006-2008
> > > > > > > Documentation for Software Users
>
> > > > > > > Get our Free Tips and Tricks Newsletter and check out our boo=
ks at
> > > > > > >http://www.AccoladePublications.com
>
> > > > > > > "dan" wrote:
>
> > > > > > > > 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 mac=
ro
> > > > > then adjust the inventory entries without posting the inventory
> > > > > adjustment transaction to the GL and then use IM to create all th=
ose
> > > > > transactions again but I have to change the transaction number. W=
ith
> > > > > the incorrect posting date, sales analysis are also incorrect. Wh=
at is
> > > > > the best solution to my problem?
>
> > > Hello Jan
>
> > > Im trying to change the posting date in SOP. I think GL posting shoul=
d
> > > 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
0
7/4/2009 7:03:40 PM
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


"dan" wrote:

> How do I correct posting date in SOP if the transaction has already
> been posted in SOP Series?
> 
0
MarianoGomez (3440)
7/4/2009 8:58:00 PM
On Jul 5, 4:58=A0am, Mariano Gomez
<MarianoGo...@discussions.microsoft.com> wrote:
> 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 screwe=
d up.
>
> Here is the script you will need to fix this problem -- and no, it is jus=
t
> not fixing it in SOP! You also have to consider RM and GL.
>
> DECLARE @myDate datetime;
> DECLARE @myBatch char(15);
>
> SET @myBatch =3D 'SOPBATCH'; -- replace with the batch you use in IM
>
> DECLARE @trxSources table(SOPNUMBE char(21), TRXSORCE CHAR(13), GLPOSTDT
> datetime);
>
> UPDATE SOP30200 SET GLPOSTDT =3D DOCDATE
> =A0 OUTPUT INSERTED.SOPNUMBE, INSERTED.TRXSORCE, INSERTED.GLPOSTDT INTO
> @trxSources
> WHERE BACHNUMB =3D @myBatch AND SOPTYPE =3D 3;
>
> UPDATE A SET A.GLPOSTDT =3D B.GLPOSTDT
> FROM SOP30100 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
> @trxSources) B ON (A.TRXSORCE =3D B.TRXSORCE);
>
> UPDATE A SET A.GLPOSTDT =3D B.GLPOSTDT
> FROM RM20101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, GLPOSTDT FROM
> @trxSources) B ON (A.DOCNUMBR =3D B.SOPNUMBE) AND (A.RMDTYPAL =3D 1);
>
> UPDATE A SET A.GLPOSTDT =3D B.GLPOSTDT
> FROM RM30502 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
> @trxSources) B ON (A.TRXSORCE =3D B.TRXSORCE);
>
> UPDATE A SET A.POSTEDDT =3D B.GLPOSTDT
> FROM RM10101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT F=
ROM
> @trxSources) B ON (A.TRXSORCE =3D B.TRXSORCE) AND (A.DOCNUMBR =3D B.SOPNU=
MBE);
>
> UPDATE A SET A.POSTEDDT =3D B.GLPOSTDT
> FROM RM30301 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT F=
ROM
> @trxSources) B ON (A.TRXSORCE =3D B.TRXSORCE) AND (A.DOCNUMBR =3D B.SOPNU=
MBE);
>
> UPDATE A SET A.TRXDATE =3D B.GLPOSTDT
> FROM GL10000 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
> @trxSources) B ON (A.BACHNUMB =3D B.TRXSORCE);
>
> UPDATE A SET A.TRXDATE =3D B.GLPOSTDT
> FROM GL20000 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPOSTDT F=
ROM
> @trxSources) B ON (A.ORTRXSRC =3D B.TRXSORCE) AND (A.ORDOCNUM =3D B.SOPNU=
MBE);
>
> You will also want to change your posting setting to grab the date from t=
he
> 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
>
> "dan" wrote:
> > 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
0
7/6/2009 3:55:25 AM
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


"dan" wrote:

> On Jul 5, 4:58 am, Mariano Gomez
> <MarianoGo...@discussions.microsoft.com> wrote:
> > 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, LLChttp://www.maximumglobalbusiness.com
> > The Dynamics GP Blogster athttp://dynamicsgpblogster.blogspot.com
> >
> > "dan" wrote:
> > > 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
> 
0
MarianoGomez (3440)
7/6/2009 3:10:01 PM
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


"Mariano Gomez" wrote:

> 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
> 
> 
> "dan" wrote:
> 
> > On Jul 5, 4:58 am, Mariano Gomez
> > <MarianoGo...@discussions.microsoft.com> wrote:
> > > 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, LLChttp://www.maximumglobalbusiness.com
> > > The Dynamics GP Blogster athttp://dynamicsgpblogster.blogspot.com
> > >
> > > "dan" wrote:
> > > > 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
> > 
0
MarianoGomez (3440)
7/6/2009 3:17:02 PM
On Jul 6, 11:17=A0pm, Mariano Gomez
<MarianoGo...@discussions.microsoft.com> wrote:
> 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 =3D @myBatch AND SOPTYPE =3D 3 AND (GLPOSTDT <> DOCDATE);
>
> UPDATE SOP30200 SET GLPOSTDT =3D DOCDATE
> WHERE (BACHNUMB =3D @myBatch) AND (SOPTYPE =3D 3) AND (GLPOSTDT <> DOCDAT=
E);
>
> Best regards,
> --
> MG.-
> Mariano Gomez, MIS, MCP, PMP
> Maximum Global Business, LLChttp://www.maximumglobalbusiness.com
> The Dynamics GP Blogster athttp://dynamicsgpblogster.blogspot.com
>
> "Mariano Gomez" wrote:
> > There is no counterpart, but this should get you what you want:
>
> > INSERT INTO @trxSources (SOPNUMBE, TRXSORCE, GLPOSTDT)
> > SELECT SOPNUMBE, TRXSORCE, GLPOSTDT
> > WHERE BACHNUMB =3D @myBatch AND SOPTYPE =3D 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 athttp://dynamicsgpblogster.blogspot.com
>
> > "dan" wrote:
>
> > > On Jul 5, 4:58 am, Mariano Gomez
> > > <MarianoGo...@discussions.microsoft.com> wrote:
> > > > 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.
>
> > > > DECLARE @myDate datetime;
> > > > DECLARE @myBatch char(15);
>
> > > > SET @myBatch =3D 'SOPBATCH'; -- replace with the batch you use in I=
M
>
> > > > DECLARE @trxSources table(SOPNUMBE char(21), TRXSORCE CHAR(13), GLP=
OSTDT
> > > > datetime);
>
> > > > UPDATE SOP30200 SET GLPOSTDT =3D DOCDATE
> > > > =A0 OUTPUT INSERTED.SOPNUMBE, INSERTED.TRXSORCE, INSERTED.GLPOSTDT =
INTO
> > > > @trxSources
> > > > WHERE BACHNUMB =3D @myBatch AND SOPTYPE =3D 3;
>
> > > > UPDATE A SET A.GLPOSTDT =3D B.GLPOSTDT
> > > > FROM SOP30100 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
> > > > @trxSources) B ON (A.TRXSORCE =3D B.TRXSORCE);
>
> > > > UPDATE A SET A.GLPOSTDT =3D B.GLPOSTDT
> > > > FROM RM20101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, GLPOSTDT FROM
> > > > @trxSources) B ON (A.DOCNUMBR =3D B.SOPNUMBE) AND (A.RMDTYPAL =3D 1=
);
>
> > > > UPDATE A SET A.GLPOSTDT =3D B.GLPOSTDT
> > > > FROM RM30502 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
> > > > @trxSources) B ON (A.TRXSORCE =3D B.TRXSORCE);
>
> > > > UPDATE A SET A.POSTEDDT =3D B.GLPOSTDT
> > > > FROM RM10101 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPO=
STDT FROM
> > > > @trxSources) B ON (A.TRXSORCE =3D B.TRXSORCE) AND (A.DOCNUMBR =3D B=
..SOPNUMBE);
>
> > > > UPDATE A SET A.POSTEDDT =3D B.GLPOSTDT
> > > > FROM RM30301 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPO=
STDT FROM
> > > > @trxSources) B ON (A.TRXSORCE =3D B.TRXSORCE) AND (A.DOCNUMBR =3D B=
..SOPNUMBE);
>
> > > > UPDATE A SET A.TRXDATE =3D B.GLPOSTDT
> > > > FROM GL10000 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
> > > > @trxSources) B ON (A.BACHNUMB =3D B.TRXSORCE);
>
> > > > UPDATE A SET A.TRXDATE =3D B.GLPOSTDT
> > > > FROM GL20000 A INNER JOIN (SELECT DISTINCT SOPNUMBE, TRXSORCE, GLPO=
STDT FROM
> > > > @trxSources) B ON (A.ORTRXSRC =3D B.TRXSORCE) AND (A.ORDOCNUM =3D 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
>
> > > > "dan" wrote:
> > > > > 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 =3D B.GLPOSTDT
FROM GL10000 A INNER JOIN (SELECT DISTINCT TRXSORCE, GLPOSTDT FROM
@trxSources) B ON (A.DTAControlNum =3D B.SOPNUMBE);

Thanks,

Dan

0
7/7/2009 6:58:56 AM
Reply:

Similar Artilces:

How to log changes in pst files for keeping track of what changes occur in pst file in outlook.
Hi, I want to track the changes or events on pst file in outlook. How to log the events like moving a folder, creating a new message, deleting a message, renaming a folder etc. As I want to do replication from a pst file to some other data file. I want to know what are changes happened in pst file, so that I will do only the incremental replication. Does mapi logs these events??? One way I find is I can implement the IMapiAdviseSink interface. In the OnNotify() method of IMapiAdviseSink I can log the event, but if before I logged the event, if outlook crashes.., how can I get the changes in...

Date and Text combination
Hi, could someone help me with getting this particular format in ONE SINGLE cell: Text 25-Sep Please note that the date part has to be variable, i.e i shuld be able to drag the last cell to create a new cell with the next date. The Text is some constant text that has to be prefixed to the date in each cell. So my row could look something like : Week of 7-Oct Week of 14-Oct Week of 21-Oct Week of 28-Oct thanx in advance!! Enter your first date into cell A1, then in B1 enter this formula: =A1+7 Copy the formula from B1 into C1:D1 (or however many columns you want to use. ...

Changing Sheets in chart
I need to be able to change the sheet reference in a chart by selectin a cell that has the sheet name. Each sheets data are in the same cells I just need to reference different sheets. Example: if the curren values are referenced by =Sheet1!$B$3:$B$8 then I need to be able t change Sheet1 to Sheet 2 without changing the other data example =Sheet2!$B$3:$b$8 or =Sheet5!$B$3:$b$ -- DBDEZYN ----------------------------------------------------------------------- DBDEZYNE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2709 View this thread: http://www.excelforum.com/...

MS Query Data Source Change
I have a series of saved queries that I use on an ODBC data source. I would like to put them on other computers that may not have the same data sources but have different ones (tables are the same in each data source). Right now I am editing the query in Notepad to change the data source and path to the correct one on the other computers but am wondering why it is not evident how to change this in the query itself. Can it be done? How? TIA I had a similiar problem which I solved with a form/macro. (I don't think you can point to a different source other than manually changing t...

How do I change format of all comments in worksheet?
I want to be able to change font and size of text within comments in a whole worksheet, without having to do them individually. Anyone any ideas? see this site http://www.contextures.com/xlcomments03.html#Reset >-----Original Message----- >I want to be able to change font and size of text within comments in a whole >worksheet, without having to do them individually. Anyone any ideas? >. > ...

Changing Screen Resolution
How can I change the resolution of the screen when the app start and return it's last resolution when the app be closing. > How can I change the resolution of the screen when the app start and return it's last resolution when the app be closing. Hector, See the ChangeDisplaySettings API documentation. If you want to do it for an arbitrary application, you could use VidRes (www.jddesign.co.uk) to launch the application and perform the resolution changes for you. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq ...

how do I put a plain border around individual dates in a calendar.
Using Publisher 2003 I can't seem to place a border around individual dates, only the whole month. When editing in word I can put a border on a date, but when I return to publisher the border isn't there. When editing in publisher, there doesn't appear to be a way to put a border around an individual date. I had no problems with Publisher 97, but I don't want to have reinstall just to do this.... The calendar function was different in Pub 97. With that version it was a table which could be edited just like any other table. With Pub2000 and later, the format is different...

Desktop shortcut changes to OE?
I've noticed this intermittent problem for a number of months and didn't really worry about it too much but it has started to become a nuisance. Basically my desktop shortcut icon to Outlook will intermittently change, on boot, to a shortcut icon to Outlook Express. The problem occurs frequently - about 1 in every 2 or perhaps 3 boots. Clicking on the icon when it presents as the OE icon takes me to OE; clicking it when it presents as the Outlook icon takes me to Outlook. It's a very odd problem (I think) - why would and how does the shortcut path change randomly? Is there...

Change lookups so that by default they don't show inactive record.
When doing any lookup in Great Plains it defaults with all master records. Would like by default only active records to show up. Hi Catherine, SmartFill from Rockton Software can do this. Thanks, Therese "Catherine McDade" wrote: > When doing any lookup in Great Plains it defaults with all master records. > Would like by default only active records to show up. ...

Transfer Order to an Invoice (SOP)
Hi, When you transfer an order to an Invoice, is there any possibility of printing the Order number on the Invoice report, as well. which field would contain the Order number from which the invoice was generated. Any help would be appreciated Thanks in Advance Sajid Saeed ...

SOP Window Error
I have updated my system to SP2 but getting an odd error with the SOP transaction entry window. If a user opens the window, but then closes it again without doing anything, an error is popping up saying "An error occurred execting update statements" I think it may be related to some security, but I don't where where as it only happens to some users. Has anyone seen this? Hits -- I found the problem running a trace. Tables SVC00660 and SVC00661 had no permissions at all, and I added them for Dyngroup. Hitesh Varsani wrote: > I have updated my system to SP2 but getting...

Payables open invoice with zero unapplied, not because of not post
Last year in v6.0 a check in a computer check batch had two items on it that showed the invoice and CM amounts, but zero unapplied. The other checks in that batch are ok. We are now on v9.0. Users voided this check in order to correct the two with zero unapplied, everything returned to open (these show up on aging and inquiry) but in manual payment Apply window, 6 items show up with invoice amounts but zero unapplied. 2 were from the original check, 4 have never been on a check. How can we fix this so that the unapplied amounts are correct? Sounds like you need to run an update to the...

ComboBox: Update a value in current record before changing the ComboBox Value
It's been a while since I have had any issues...I seem to create my own problems... I have a combo box in a sunform. I use the combobox to choice multiple items in the subform. When I pick and item in the combobox, I run an AFTER UPDATE and set a value in a table (I change a 0 to a 5). However, if I picked the wrong record and go and change it to a different record, the value I set to 5 above stays a 5 and know I have a record with a value I do not want...I want to change it back to 0 before I change the record in the combobox. I have tried Before Update, On Enter, On Get Focus...thes...

BOM changes using out date
Is it possible to change the quantity on a compnent item in a BOM and have that change be date sensitive? We have a situation where the current BOM calls for 3 of an item. Begining on a certain date that quantit will change to 2 and a new item will be added to the BOM. Haven't found a way to handle this yet, other than remembering to change the particular BOM on the specific date. Any ideas? -- Jim@TurboChef Jim, Have you tried using the Effective and Obsolete Dates? Make the item with quantity of 3 obsolete on a specific date. Add the item to the BOM a second time with a q...

sort data without formulas in other cells changing?
Hi, Can someone please help? I've got quite a lot of data that I want to sort by the persons date of birth, but because I have cells with formula in it (this works out the persons age) the sort function is changing the formulas so the formulas no longer work becuase it changes the cell where it is getting the data from. Does anyone have any ideas how this could be fixed?? Thanks! I bet you have formulas that point directly at other cells in another worksheet: =sheet2!b3 If you do, you may want to see if you can redesign your worksheet so that you can use some key column (name/i...

Money Bug 03/04: Stock Changes it's name but Maoney wont let that happen.
I have seen this in Money 2003 and now 2004. A company, such as DELL changes it's name but not is it symbol. When I download transactions Money asks if the new name is the same investment as the old name (obviously it is as they have the same symbol). I say yes it is the same. Money asks "Are You Sure Dell Computers is the same as Dell Inc". Regardless of how I answer this confirm It asks the first question again, and again and again and again until I admit that Dell Inc is NOT Dell Computer. Only then can I continue the reconcile. Every time I upgrade I wish I did not. I...

GP 10- SOP Integration with Silent MO generation
Has anyone performed a successful SOP integration with IM while utilizing the Silent MO generation option from a Sales Order? Our integration works fine until we activate this option in GP 10. Thanks! ...

How do I change x axis values in a line chart?
I am creating line charts for 12 weeks of data. Each week is represented by one data point. My y-axis is rate and my x-axis is weeks. I have data for weeks 1-6 on one graph, but need to put data for weeks 7-12 on another graph. For weeks 7-12 I would like the x-axis to be labeled 7-12, but right now it is labeled 1-6. Is there anyway for me to change those labels? Thanks for any help you can give. Elizabeth, Click once on your chart. Go to the standard toolbar and select Chart -> Source Data. The Source Data dialog box will appear. Go to the Series Tab. At the bottom of ...

how do i change "from" in the account settings
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: imap Dear All <br> I have had Microsoft Office 2008 installed on my mac for a couple of years, but haven't been using Entourage until now. <br> I have set up my accounts and all is working perfectly, however when testing the emails, when they arrive in an inbox, they have from office 2008 test drive user, before my own name, and it's not clear to the user that mail is from me. <br> I went online to register the product, and all is well, but the test drive user still appears...

Common Change
Hi, I have over a 200 files, where I need to just change my e-mail address within the file. Is there any thing that I can use/run to go automatically through the files individually, open them up, find & replace my e-mail address, then save the files again ? Hope someone can help. Thanks ...

Duplicate Dates Problem
Hello, Using Excel 2007. I have three Columns of data, similar to this: Wish to have the dates as the X axis, and show the next two Columns on the same plot. 21-Apr 143 21 26-Apr 147 93 3-May 144 91 13-May 135 128 13 May 128 81 17-May 143 93 17 May 137 86 22-May 127 89 22-May 123 83 I have the first, the Date column, formatted as: Date / 14 Mar I was hoping to graph this data, and have a line connecting "all" the points (of each column's data). For the second Column, I do have lines connecting the data with the unique dates of 21 Apr thru 3 May For some reaso...

How do I change the font on the Trial Balance Report
My client ran the Trial Balance Quick Detail and Quick Summary. The font on page 1 is ok but from page 2 the font is unreadable.They clicked Modify to change the font and they got the error "The current report cannot be modified because it was not created by the Report Writer". So is there any way to change the font on the Quick Detail and Summary reports? These reports named the RM Quick Detail Aged Trial Balance and RM Quick Summary Aged Trial Balancecan be modified using Dexterity and then brought into the multi-dictionary environment as an Alternate Great Plains Report. T...

mysql changes
AOA MySqlLib librariy has been modified.that will update the AdvMySql COM. so please update both of them. Regards Usman Jamil hi Sorry i had to mail it to someone else. please ignore this mail regards usman jamil "Usman Jamil" <usman@advcomm.net> wrote in message news:#l6Cy4#YDHA.2256@TK2MSFTNGP10.phx.gbl... > AOA > > MySqlLib librariy has been modified.that will update the AdvMySql COM. so > please update both of them. > Regards > Usman Jamil > > ...

How to: Notify that the email address has changed
Hi, my name is Javier Villegas and I have a issue to solve. I am Using Windows 2003 with Exchange 2003 like our email solution Our Company's email address changed from JVillegas@mycompany.com to Javier.Villegas@mycompany.com Both address are enable until 31/12/2004. I have to notify to people who sent me emails that my address will change. For example if you send me an email, you have to recive a notification that my address will change, but only the first time that you sent the mail. (Not all the time that you send an email to me) Could I do it ? If yes, Could I d...

changing the "markup" of a node
hi, I have an xmlFile <code> <rows> <row> <PRODUCT-TYPE>bike</PRODUCT-TYPE> <PRODUCT-DATE>01/01/2004</PRODUCT-DATE> <ADDED-NODE>"blah"<ADDED-NODE /> </row> <PRODUCT-TYPE>Car</PRODUCT-TYPE> <PRODUCT-DATE>01/01/2003</PRODUCT-DATE> <ADDED-NODE>"blah"<ADDED-NODE /> </row> </rows> </code> I want to change the markup of the PRODUCT-TYPE node. I want to change it to: <ProductType> So, my file would become <code> <row> <ProductType>bi...