DynDeveloper.com

The technique used in the script below is unsupported, and should not be used in any environment without careful testing. 

That being said, I need to 'unvoid' SOP documents all the time. Below is the script that I use

(sigh)

It's a real burden living with OCD <smiles>

(Aside: know what CDO is? It's OCD, alphabetized, they way it should be)

That being said, I really don't like inserts written like this, I find them hard to read and hard to maintain. So, you have to remove all the carriage returns in order to put them the way that I like. On a table with 200 fields, that's pretty onerous. 

It turns out that SSMS will do it with one 'replace'. Read on...

I get asked all the time if I know how to PIVOT SQL data. I do, but I've never used the PIVOT keyword. The problem is that usually the query needs to be put in a report or a SmartList, and PIVOT will return a varying amount of columns. We need a fixed number of columns in reporting. 

The technique below will achieve that.

This is not 'fringe' code... you'll use this all the time. Take the time to read and understand. It's short. 

The sample will run as-is, cut and paste into SQL

Today a client reported this error

Error processing document: \\<sql box>\<folders>\test\CM1700039_20170609150741.XML db: <dbname> Cannot open database "<dbname>" requested by the login. The login failed. Login failed for user '<domain name>\<user name>'. Complete Document: 7CM17/0003909766
 

eConnect uses SSPI security, meaning that it uses the credentials of the logged on user. 

Restated: The user that is logged on has to have security assigned to them in SQL

Screen shots below will walk you through it. 

I have to write Deferred Revenue code about once a year, and I always forget what it does... 'cause I'm not an accountant <smiles>

I'm going to post this script to use as a starting point for the next project, and I'll improve it as I go. 

I decided to go ahead and test out the Workflow_Status field in the PM20000 table. I updated the Workflow_Status field to 6 for every Open payable invoice that hasn't been paid yet. Then we went ahead and altered our Integration routine to set the Workflow_Status field to 6 when we take an invoice off Hold. That routine ran at 4:00 PM Friday. Here's the UPDATE query that runs in the integration routine:

       UPDATE dbo.PM20000
SET Hold = 0
, Workflow_Status = 6
WHERE VCHRNMBR = @vchNo
AND Workflow_Status != 6
AND Hold = 1

Well, the handful of invoices that were approved at 4:00 in our Integration routine were flagged with a 9 (Not activated) in the Workflow_Status column. Crud buckets! That seems to mean that GP overrides the value in that field after we've updated it to 6.

I considered creating a SQL scheduled job that runs 5 minutes after the integration routine runs and look for any invoices that are not on Hold but have a 9 in the Workflow_Status column and change them to 6! Kinda brute force.

One of our other developers suggested a new table that contains the invoice numbers of every invoice that our integration routine takes off Hold. Then if an invoice remains unpaid the next time the integration routine runs (it runs four times during the day) it would check that new table. It will ignore any invoices it finds in that table. That is, if Accounting puts an invoice back on Hold, the integration routine will ignore it because its number has been recorded in the new table. Pretty simple.

What I can't figure out is this: I did a mass update of the Workflow_Status field on Friday afternoon before the 4:00 integration run... and, as far as I can tell, none of those invoices have had their Workflow_Statuses changed. What is it about our integration routine that would allow GP to further affect the values in the Workflow_Status field after we've updated a few invoices to 6?

I might run SQL Profiler at the same time as our integration routine runs to see when the 6 is changed to a 9. That's all I can think of. Anyone else have any ideas?

Thank you,

Steve Erbach

Hi,

I get this message "Only documents using the functional currency or the originating currency EUR can be applied" when attempting to apply a Swedish (SEK) invoice that was paid in EUR while our functional currency is USD.

Can anyone help resolve the issue?

Thanks - Paul Chacko

i'm looking for some detailed information on a manufacturing table  mrp1010  that  handles  mrp planned orders. for  purchasing.

in particular  stored procdures that should run to clear and or rebuild this ?  We are having issues with mrp not generating planned orders.

this table contains  many rows which it appears shouldnt be there, given that  those rows are outside of the time fences for our MRP.

I am currently passing different types of invoices to Great Plains.  The document number is being populated by Great Plains.  My client would like the document number to be populated with a user inputted invoice number instead of the auto generated document number.

Is this safe to do?  I was able to successfully pass a user inputted invoice number, but would this effect other functionalities within Great Plains?

Hi Guys.

I would really appreciate if any of you have  more info then in GP manual on Canadian Payroll set up.

Sincerely

Just wanted to let you know how valuable this site is - I especially appreciate the GP table and field descriptions, along with the SQL coding suggestions.

Keep up the great work!

Karen Koehle

We have recently upgraded to GP2016 from 2010.

Management had our Dynamics partner write custom code so that the finance charge percentage could be 3 places, since we were told GP2010 could only handle 2 places. 

Do you know if 2016 allows more than 2 decimal places for the finance charge percentage?

I have the following xml file.  As you can see below, I have set a date for each of the dimensions, but when viewed within Great Plains, these values are not populated.

Any suggestion would be greatly appreciated.

Best,

-Falk0r