DynDeveloper.com

DynDeveloper.com

Sign Up Now! Log In

Archives

 

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

This is just one line of code, but it's a quick example of how to format a number as a percentage in SQL. 

I'm collecting FORMAT examples on the SQL menu, you might click the 'SQL General' link above and see...

(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 live reading other people's code, you learn things. 

The snippet below is from Tom Goodspeed at Olympic Project Cost, he shows a neat way to do variable assignments. 

I believe I'll be copying this formatting... <smiles>

Template code to 'pivot' a SQL table. 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

 

This is a working example that creates a SOP document using SQL Scripts

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... don't like LINQ. I do all my data access in SQL, and present the data to .NET exactly the way it needs to be. (sigh)

But sometimes I need a LINQ reference. So... here is one from RedGate

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. 

We do  not use GP Workflow for Payables Management... but I'm wondering if the Workflow_Status field in the PM10000, PM20000, and PM30200 tables may be used in our custom integration routines.

That is, we have a custom Payables invoice approval process set up as a SharePoint workflow. When the SharePoint workflow is complete, a payable invoice is approved for payment, and our integration routine takes that invoice off Hold in GP.

What I'm wondering is this: if we also set the Workflow_Status field to 6 (Approved) in our integration routine, might that value be overwritten by GP at some point in its normal operations?

When does GP stick a value in that field if we  don't use the Workflow capability?

I've checked the value of that field in all three tables and they're set to either 9 (Not Activated) or 0.

Any advice?

Sincerely,

Steve Erbach
WOW Logistics Company
Appleton, WI

 

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

Afterwards, when I looked at the list of invoices that were not on Hold, I thought I'd check for those that had a Workflow_Status of something other than 6 (Approved). I had just changed them all before 4:00 PM.

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

I have a report that looks at PA01201 reporting various fields out of this one table; I have been asked to add table

but the issue is the second table PA41302 has multiple rows for each row in PA01201.

What I am looking for is to transpose the second table.

Table PA41302 definition

PAPROJNUMBER

SGMTNUMB

SGMTNAME

SGMNTID

 

What I want to do is add 2 columns into report Cost Centre and Region

Where PA41302..SGMTNAME = Cost Centre or Region then data into report  

join is based on PAPROJNUMBER

Many thanks

     

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

First post here--not really sure how to classify it.

We are currently utilizing GP2016. We've identified some discrepancies between Customer Accounts and Documents

Right now, when we run a Sales-->Customers--> Weekly Aging Smartlist, we see:

However, the "FLINTWI" customer does NOT appear in the Sales--> AR Aging Smartlist.

We also try to search for the customer "FLINTWI" and nothing shows up at all as far as invoices (paid or unpaid) goes.

How on earth can we have a Customer Aging report with a balance yet no way to see those invoices in the customer account?

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.

 

We have recently updated this client's COA in GP and this company has MEM (Multi entity management) integrated as they are managing multiple entities in one DB. The entities are set as the first segment in the account # -i.e. 02 - 10003 (02 would be entity # and 10003 would be account #). 
 
When updating their COA, we also changed the first segment of the account, so now 02 is 2005. In GP however, it still recognizes both entities - 02 and 2005. We want it to only show 2005. 

What would be the best way to update the facility numbers? I've heard that it needs to be done in SQL, but specifically which tables need to be updated?

Thank you!

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?

Another Question I had was that if we assign our own receivables transactions - We are putting through batch entries through Smartconnect in which we need to assign our own document number - would that impact GP?

Thanks!

Pooja

Hi Guys.

 

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

 

Sincerely

 

Hi, I get the error below after installing GP 2013 on a Windows 10 PC and exiting out of GP:

Unhandled object exception: The parameter is incorrect.
EXCEPTION_CLASS_OBJECT_EXCEPTION
ExceptionSubClass:-2147024809

Anyone experienced this?

Thanks

Paul Chacko

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 a customer that has purchased several locations from another customer.  I would like to move those locations (Address ID's) from the old customer account to the new customer account with all of the history as well.  In other words, we would  like the order history for that address to move to the new customer account as well.

 

Has anyone done this before?  Is there a utility to do this?

 

Thanks.

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 am currently attempting to pass Dimensions and codes to the Analytical Accounting Module.  I am able to get 1 of the dimensions to display; however, the second seems to overwrite the first.  Please see code and screenshot below.

Snapshot of Analytics Accounting Screen

01.<?xml version="1.0" encoding="utf-8"?>
03.    <PMTransactionType>
04.        <eConnectProcessInfo xsi:nil="true" />
05.        <taRequesterTrxDisabler_Items xsi:nil="true" />
06.        <taUpdateCreateVendorRcd xsi:nil="true" />
07.        <taCreateVendorAddress_Items xsi:nil="true" />
08.        <taPMTransactionTaxInsert_Items />
09.        <taPMDistribution_Items>
10.            <taPMDistribution>
11.                <DOCTYPE>1</DOCTYPE>
12.                <VCHRNMBR>00000000000000737</VCHRNMBR>
13.                <VENDORID>ADVANCED0001</VENDORID>
14.                <DISTTYPE>6</DISTTYPE>
15.                <DistRef>TEST</DistRef>
16.                <ACTNUMST>000-1400-00</ACTNUMST>
17.                <DEBITAMT>2000</DEBITAMT>
18.            </taPMDistribution>
19.            <taPMDistribution>
20.                <DOCTYPE>1</DOCTYPE>
21.                <VCHRNMBR>00000000000000737</VCHRNMBR>
22.                <VENDORID>ADVANCED0001</VENDORID>
23.                <DISTTYPE>2</DISTTYPE>
24.                <ACTNUMST>000-2120-00</ACTNUMST>
25.                <CRDTAMNT>2000</CRDTAMNT>
26.            </taPMDistribution>
27.        </taPMDistribution_Items>
28.        <taAnalyticsDistribution_Items>
29.            <taAnalyticsDistribution>
30.                <DOCNMBR>00000000000000737</DOCNMBR>
31.                <AMOUNT>1000</AMOUNT>
32.                <DistSequence>16384</DistSequence>
33.                <ACTNUMST>000-1400-00</ACTNUMST>
34.                <DistRef>reference test</DistRef>
35.                <aaTrxDim>SHOW</aaTrxDim>
36.                <aaTrxDimCode>BACHARACH</aaTrxDimCode>
37.                <aaTrxDimCodeDate>02/17/2017</aaTrxDimCodeDate>
38.            </taAnalyticsDistribution>
39.            <taAnalyticsDistribution>
40.                <DOCNMBR>00000000000000737</DOCNMBR>
41.                <AMOUNT>1000</AMOUNT>
42.                <DistSequence>32768</DistSequence>
43.                <ACTNUMST>000-1400-00</ACTNUMST>
44.                <DistRef>reference test</DistRef>
45.                <aaTrxDim>SHOW</aaTrxDim>
46.                <aaTrxDimCode>SINATRA</aaTrxDimCode>
47.                <aaTrxDimCodeDate>02/17/2017</aaTrxDimCodeDate>
48.            </taAnalyticsDistribution>
49.        </taAnalyticsDistribution_Items>
50.        <taPMTransactionInsert>
51.            <BACHNUMB>Batch 1</BACHNUMB>
52.            <VCHNUMWK>00000000000000737</VCHNUMWK>
53.            <VENDORID>ADVANCED0001</VENDORID>
54.            <DOCNUMBR>TESTS9989</DOCNUMBR>
55.            <DOCTYPE>1</DOCTYPE>
56.            <DOCAMNT>2000</DOCAMNT>
57.            <DOCDATE>2/17/2017 12:00:00 AM</DOCDATE>
58.            <PRCHAMNT>2000</PRCHAMNT>
59.            <CHRGAMNT>2000</CHRGAMNT>
60.            <TRXDSCRN>Test Description</TRXDSCRN>
61.            <CURNCYID>Z-US$</CURNCYID>
62.            <CREATEDIST>0</CREATEDIST>
63.        </taPMTransactionInsert>
64.        <taMdaUpdate_Items xsi:nil="true" />
65.    </PMTransactionType>
66.</eConnect>

Any suggestions would be greatly appreciated.

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.

01.<?xml version="1.0" encoding="utf-8"?>
03.    <PMTransactionType>
04.        <eConnectProcessInfo xsi:nil="true" />
05.        <taRequesterTrxDisabler_Items xsi:nil="true" />
06.        <taUpdateCreateVendorRcd xsi:nil="true" />
07.        <taCreateVendorAddress_Items xsi:nil="true" />
08.        <taPMTransactionTaxInsert_Items />
09.        <taPMDistribution_Items>
10.            <taPMDistribution>
11.                <DOCTYPE>1</DOCTYPE>
12.                <VCHRNMBR>00000000000000744</VCHRNMBR>
13.                <VENDORID>ADVANCED0001</VENDORID>
14.                <DISTTYPE>6</DISTTYPE>
15.                <DistRef>TEST</DistRef>
16.                <ACTNUMST>000-1400-00</ACTNUMST>
17.                <DEBITAMT>2000</DEBITAMT>
18.            </taPMDistribution>
19.            <taPMDistribution>
20.                <DOCTYPE>1</DOCTYPE>
21.                <VCHRNMBR>00000000000000744</VCHRNMBR>
22.                <VENDORID>ADVANCED0001</VENDORID>
23.                <DSTSQNUM>16384</DSTSQNUM>
24.                <DISTTYPE>2</DISTTYPE>
25.                <ACTNUMST>000-2120-00</ACTNUMST>
26.                <CRDTAMNT>2000</CRDTAMNT>
27.            </taPMDistribution>
28.        </taPMDistribution_Items>
29.        <taAnalyticsDistribution_Items>
30.            <taAnalyticsDistribution>
31.                <DOCNMBR>00000000000000744</DOCNMBR>
32.                <AMOUNT>1000</AMOUNT>
33.                <DistSequence>0</DistSequence>
34.                <ACTNUMST>000-1400-00</ACTNUMST>
35.                <DistRef>reference test</DistRef>
36.                <aaTrxDim>SHOW</aaTrxDim>
37.                <aaTrxDimCode>BACHARACH</aaTrxDimCode>
38.                <aaTrxDimCodeDate>2/17/2017 12:00:00 AM</aaTrxDimCodeDate>
39.                <aaSubLedgerAssignID>0</aaSubLedgerAssignID>
40.            </taAnalyticsDistribution>
41.            <taAnalyticsDistribution>
42.                <DOCNMBR>00000000000000744</DOCNMBR>
43.                <AMOUNT>1000</AMOUNT>
44.                <DistSequence>0</DistSequence>
45.                <ACTNUMST>000-1400-00</ACTNUMST>
46.                <DistRef>reference test</DistRef>
47.                <aaTrxDim>SHOW</aaTrxDim>
48.                <aaTrxDimCode>SINATRA</aaTrxDimCode>
49.                <aaTrxDimCodeDate>2/17/2017 12:00:00 AM</aaTrxDimCodeDate>
50.            </taAnalyticsDistribution>
51.        </taAnalyticsDistribution_Items>
52.        <taPMTransactionInsert>
53.            <BACHNUMB>Batch 1</BACHNUMB>
54.            <VCHNUMWK>00000000000000744</VCHNUMWK>
55.            <VENDORID>ADVANCED0001</VENDORID>
56.            <DOCNUMBR>TESTS9989</DOCNUMBR>
57.            <DOCTYPE>1</DOCTYPE>
58.            <DOCAMNT>2000</DOCAMNT>
59.            <DOCDATE>2/17/2017 12:00:00 AM</DOCDATE>
60.            <PRCHAMNT>2000</PRCHAMNT>
61.            <CHRGAMNT>2000</CHRGAMNT>
62.            <TRXDSCRN>Test Description</TRXDSCRN>
63.            <CURNCYID>Z-US$</CURNCYID>
64.            <CREATEDIST>0</CREATEDIST>
65.        </taPMTransactionInsert>
66.        <taMdaUpdate_Items xsi:nil="true" />
67.    </PMTransactionType>
68.</eConnect>

Best,

-Falk0r