tag:blogger.com,1999:blog-2902800390575811159.post3569932596293418329..comments2023-12-25T03:01:26.251-06:00Comments on Dynamics Confessions Blogspot: Demystifying GP Tables and Transaction FlowsDynamics Confessorhttp://www.blogger.com/profile/06555435567985477366noreply@blogger.comBlogger24125tag:blogger.com,1999:blog-2902800390575811159.post-14810172835336951432018-03-29T19:12:43.936-05:002018-03-29T19:12:43.936-05:00Mark,
Congrats to you for being able to change the...Mark,<br />Congrats to you for being able to change the workflow. It's just so easy to void the invoice in RM, I think it was a good fix if you've got non-inventory items but still want to get your reports from SOP.<br />Leslie<br />Dynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-8756536105852191382018-03-29T08:36:23.884-05:002018-03-29T08:36:23.884-05:00Ideally, the client should not void an invoice or ...Ideally, the client should not void an invoice or return that came from SOP. Better to use a debit memo or credit memo in RM, or offset with another SOP transaction that would adjust the impact on inventory. I agree that in an inventory system, it can create confusion to update the VOIDSTTS to 1. <br /><br />But if we are using this script on a regular basis, the thought is we do not need to worry about RM30101 as the issue would be captured before the document moves to history.<br /><br />In my case, the client uses non-inventory items, and they were "fixing" the issue by bad business practices, of voiding the transaction in RM and re-issuing the invoice. This had the affect of doubling the sales reports, so we used the scripts above to handle the already poor situation, and keep the sales reports accurate. Eventually, we were able to correct the user's poor workflow.<br />MarkW101https://www.blogger.com/profile/15269794593320755445noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-28043643906368611882018-03-29T00:37:39.622-05:002018-03-29T00:37:39.622-05:00If you wanted to change the SOP30200 Void status u...If you wanted to change the SOP30200 Void status using sql, I'd most likely do it in a single statement to deal with the work and history RM tables. I game up with this:<br /><br />UPDATE SOP30200<br />SET VOIDSTTS = 1<br />FROM SOP30200<br />INNER JOIN ReceivablesTransactions ON SOP30200.SOPNUMBE = ReceivablesTransactions.[Document Number]<br /> AND SOP30200.CUSTNMBR = ReceivablesTransactions.[Customer Number]<br />WHERE (ReceivablesTransactions.[Void Status] = 'Voided')<br /> AND ReceivablesTransactions.[Document Type] IN ('Sales / Invoices', 'Returns')<br />Dynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-19850116451897626352018-03-28T19:20:29.702-05:002018-03-28T19:20:29.702-05:00Hi,
Thank you for your comment and for sharing the...Hi,<br />Thank you for your comment and for sharing these scripts. If you wanted to run an update like this, you would also want to include the RM transaction history table -RM30101.<br /><br />The fact that SOP is not updated when an invoice or return is voided in RM is not a bug. It's working as designed. The void status in SOP is used when you actually void the transaction in SOP. I wouldn't suggest voiding a SOP transaction in RM because your inventory balances will not be properly updated. If you are not using 'Sales Inventory' or 'Discontinued' items, it shouldn't be a problem, but be careful of where your sales reports are coming from.<br /><br />LeslieDynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-56108274948623799922018-03-28T11:03:55.245-05:002018-03-28T11:03:55.245-05:00FYI - If you void an invoice or return in RM that ...FYI - If you void an invoice or return in RM that started out in SOP, the void status (VOIDSTTS) in SOP30200 does not get updated. I wrote a SQL job to synch with RM. <br /><br />update SOP<br /> set VOIDSTTS = 1<br />from ( select * <br /> from RM20101<br /> where RMDTYPAL = 1<br /> and VOIDSTTS = 1) RM<br />join ( select * <br /> from SOP30200<br /> where SOPTYPE = 3<br /> and VOIDSTTS = 0) SOP<br /> on RM.DOCNUMBR = SOPNUMBE<br /><br />update SOP<br /> set VOIDSTTS = 1<br />from ( select * <br /> from RM20101<br /> where RMDTYPAL = 8<br /> and VOIDSTTS = 1) RM<br />join ( select * <br /> from SOP30200<br /> where SOPTYPE = 4<br /> and VOIDSTTS = 0) SOP<br /> on RM.DOCNUMBR = SOPNUMBE<br />MarkW101https://www.blogger.com/profile/15269794593320755445noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-16435916202892982092018-03-26T19:13:34.144-05:002018-03-26T19:13:34.144-05:00Hi Sue,
First, I want to admit to you know that I ...Hi Sue,<br />First, I want to admit to you know that I have never worked with Tableau. In fact, I had to look it up to figure out what it was :)<br /><br />1. Correct, do not change any amounts anywhere in GP via SQL. Certainly not the SOP tables. SQL will let you do it, but you will not like what happens to the software and the accounting department will be really mad.<br /><br />2. Correct, the SOP History tables hold posted sales & returns and the other items you noted in the previous comments. Be sure to exclude 'voided' transactions and consider any write-offs if you have them. Don't pull things from the SOP10XXX tables because they are unposted, correct again.<br /><br />Another note, I don't know if anyone posts sales or adjustments directly in the receivables transaction entry window, but consider the RM Open and History tables if they do. SOP is not updated by anything posted through Receivables Management. Write offs, for example, are recorded in Receivables.<br /><br />Live the dream!<br />Leslie<br />Dynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-62159106221393099392018-03-26T13:32:36.603-05:002018-03-26T13:32:36.603-05:00Hi Leslie,
Thanks so much for the quick reply. I...Hi Leslie, <br /><br />Thanks so much for the quick reply. I'm currently working on the project with Tableau. I connected SQL server directly to Tableau. My understanding is :<br /><br />1. We cannot change the Return Documents amount in SQL database to negative <br />2. If I need to know the sales performance I should pull the data from SOP HISTORY table (ie. SOP30200 & SOP30300) since these are POSTED and CORRECT actual records. I should NOT pull data from SOP WORK table (SOP10100 & SOP10200) as these are unposted transactions.<br /><br />Does that make sense? Please correct me if I am wrong. <br /><br />Thanks so much for your help. <br /><br />SueAnonymoushttps://www.blogger.com/profile/12500207496289684486noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-36280036542851291152018-03-26T13:14:31.288-05:002018-03-26T13:14:31.288-05:00Hi Sue,
You are seeing exactly the correct things ...Hi Sue,<br />You are seeing exactly the correct things in the History table. While only the Invoice and Return can be posted, they are not the only transactions in the history table. The other documents automatically move to history as you've described.<br /> 1.Orders move to history when all of their items have been fully transferred to other docs.<br /> 2.Quotes move to history when any item is transferred to another document<br /> 3.Backorders move to history when all items have been fully transferred to other docs.<br />Return documents should show a positive amount, the system differentiates between what is positive and what is negative according to the document type. This is consistent with most of the modules in GP. You'll see the same thing in RM and PM.<br />Kindly,<br />Leslie<br />Dynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-84331554085392467502018-03-23T14:10:47.977-05:002018-03-23T14:10:47.977-05:00Hi Leslie,
Thanks so much for your posting as I...Hi Leslie,<br />Thanks so much for your posting as I'm very new to GP this is super helpful.<br />I have a quick question. As you mentioned in SOP unposted document (Invoice, Return, Quote, Order, and Backorder) are in the WORK (SOP10100 & SOP10200) once they are posted will automatically move to HISTORY. However, only Invoice and Return can be posted. <br />Why in the HISTORY table there will be <br /><br />Orders whose items have been fully transferred to other documents<br />Quotes that have had any item transferred to another document<br />Backorders that have been fully transferred to other documents<br /><br />What is the status mean? Should I not see Order, Backorder in HISTORY since they cannot be posted?<br /><br />Another question is how come the Return in SOP30300 show as positive amount? Shall they show as negative amount?<br /><br />Really appreciated your help.<br /><br />Sue<br /><br />Anonymoushttps://www.blogger.com/profile/12500207496289684486noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-22983507512272216942018-03-23T14:10:27.729-05:002018-03-23T14:10:27.729-05:00Hi Leslie,
Thanks so much for your posting as I...Hi Leslie,<br />Thanks so much for your posting as I'm very new to GP this is super helpful.<br />I have a quick question. As you mentioned in SOP unposted document (Invoice, Return, Quote, Order, and Backorder) are in the WORK (SOP10100 & SOP10200) once they are posted will automatically move to HISTORY. However, only Invoice and Return can be posted. <br />Why in the HISTORY table there will be <br /><br />Orders whose items have been fully transferred to other documents<br />Quotes that have had any item transferred to another document<br />Backorders that have been fully transferred to other documents<br /><br />What is the status mean? Should I not see Order, Backorder in HISTORY since they cannot be posted?<br /><br />Another question is how come the Return in SOP30300 show as positive amount? Shall they show as negative amount?<br /><br />Really appreciated your help.<br /><br />Sue<br /><br />Anonymoushttps://www.blogger.com/profile/12500207496289684486noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-5748265909933797812017-07-20T00:52:56.009-05:002017-07-20T00:52:56.009-05:00Thank you for your kind words. If you go to any of...Thank you for your kind words. If you go to any of the upcoming conferences, be sure to flag me down. I'd love to chat with you.<br />Kind regards,<br />LeslieDynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-74653088048714921702017-07-07T09:25:12.351-05:002017-07-07T09:25:12.351-05:00Great resource. You are a great contributor to the...Great resource. You are a great contributor to the Dynamics GP community. I have learned a lot from reading your posts. It was great to Meet you in Reno a few years ago at GPUG.MarkW101https://www.blogger.com/profile/15269794593320755445noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-77906957260093078562014-10-30T20:01:46.572-05:002014-10-30T20:01:46.572-05:00Anthony,
I looked in the IV10000 table for a fiel...Anthony,<br /><br />I looked in the IV10000 table for a field where the value could be 0 or 1. Which field (column) are you referring to? The SOURCEINDICATOR column stores a 0 if the transaction has not yet been saved, hence 'new'. It stores a 1 if the transaction has been saved in a batch, but not yet posted, hence 'work'. Is that the column you are referring to?<br /><br />LeslieDynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-69042597876191409692014-10-30T19:54:33.626-05:002014-10-30T19:54:33.626-05:00Hi Anthony,
A WORK transaction has not yet been p...Hi Anthony,<br /><br />A WORK transaction has not yet been posted. I expect that is the same as a new transaction. 'New' is not a term used regarding GP transactions. To my knowledge, the IV10000 table is the transaction header to unposted Inventory Transactions. The IV10001 table is the line items of the unposted transactions. You need both the IV10000 and the IV10001 to complete the unposted Inventory "WORK" transactions.<br /><br />LeslieDynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-63762967883382450692014-10-27T04:11:28.538-05:002014-10-27T04:11:28.538-05:00HI i would like to know the difference between new...HI i would like to know the difference between new transaction and unposted , 0 and 1 in IV10000Anthony Monterashttps://www.blogger.com/profile/09802060798725720003noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-58037598856747190112014-10-27T04:09:36.338-05:002014-10-27T04:09:36.338-05:00Hi i would like to know what is the difference bet...Hi i would like to know what is the difference between 0 = new transaction and 1= unposted in IV10000 <br />Anthony Monterashttps://www.blogger.com/profile/09802060798725720003noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-6326226442398758302014-10-27T04:08:42.101-05:002014-10-27T04:08:42.101-05:00Hi Leslie i would like to know what is the differe...Hi Leslie i would like to know what is the difference between 0=New Transaction and 1= unposted in IV10000?<br /><br />You stated that 0 is equivalent to now open file but when i check there is a transaction. thanksAnthony Monterashttps://www.blogger.com/profile/09802060798725720003noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-94174333125066972014-10-09T08:05:55.457-05:002014-10-09T08:05:55.457-05:00hi, recently started working in dynamics support, ...hi, recently started working in dynamics support, and you blog is really helping me. Much appreciated.Anonymoushttps://www.blogger.com/profile/01137352042723808186noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-64672220246846159722014-05-26T01:29:16.923-05:002014-05-26T01:29:16.923-05:00Thanks Soma,
This means a lot coming from you. I ...Thanks Soma,<br /><br />This means a lot coming from you. I appreciate your taking the time to read it and to comment :)<br /><br />LeslieDynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-88354468776062072702014-05-22T03:33:04.549-05:002014-05-22T03:33:04.549-05:00Leslie, nice post.Leslie, nice post.Anonymoushttps://www.blogger.com/profile/02743266315910020535noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-18466260889217678112014-01-05T14:56:47.963-06:002014-01-05T14:56:47.963-06:00Thank you Mike,
Please let me know if you have an...Thank you Mike,<br /><br />Please let me know if you have any questions, or suggestions on other things you'd like to see covered.<br /><br />Kind regards,<br /><br />LeslieDynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-3384802657357453072014-01-05T13:46:49.655-06:002014-01-05T13:46:49.655-06:00Thanks for posting this very helpful document.
Th...Thanks for posting this very helpful document.<br /><br />This is so well put together and answers a myriad of questions that save a ton of time.<br /><br />Sincerely,<br /><br />Mikemhaineshttps://www.blogger.com/profile/09537238674720472775noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-7188611446296396362012-06-22T03:14:40.076-05:002012-06-22T03:14:40.076-05:00Hi Richard,
Thank you for your kind words. You in...Hi Richard,<br /><br />Thank you for your kind words. You inspire me!<br /><br />Regarding column names. Check out Mark Polino's blog. He has an Excel spreadsheet that you can download that lists all of the columns in the tables, as well as the tables. Wow!<br /><br />Kind regards,<br /><br />LeslieDynamics Confessorhttps://www.blogger.com/profile/06555435567985477366noreply@blogger.comtag:blogger.com,1999:blog-2902800390575811159.post-73174789234032295082012-06-21T05:28:26.479-05:002012-06-21T05:28:26.479-05:00Hi,
This is a helpful post, but I have another qu...Hi,<br /><br />This is a helpful post, but I have another question if I may.<br />I need to write custom views in SQL to speed up reports. I can translate the user table names to SQL physical names using the System table SY40501 in Dynamics:<br /> SELECT * FROM dbo.SY40501<br />Is there any equivalent reference in the Dynamics tables for columns?<br />SY40503 looked promising, but wasn't what I need.<br />I have the SDK, and I can look things up from the Dynamics UI, but if such a list existed in the tables it would be so much faster.<br /><br />Regards,<br /><br />RichardAnonymousnoreply@blogger.com