Archives

 

Error message when you use Web Services for Microsoft Dynamics GP: "An ISO 4217 Currency Code must be three characters long; your value is"

When you use Web Services for Microsoft Dynamics GP, a SOAP exception may occur. If you click Details to view the Exception Management console, you find the following error message:

An ISO 4217 Currency Code must be three characters long; your value is

 

 

Error message when you use Web Services for Microsoft Dynamics GP: "Instance Validation Error: "" is not a valid value for PurchaseOrderType"

Recently I was asked to take the Certified Payroll report that comes with Dynamics and add Olympic Project Cost data to it.

The query that runs the report is below.

Warning:

This script depends quite a bit on the customer's data, and needs to be modified before use. If you don't have SQL chops, don't do it.

In this article, we're going to look at using the SQL FORMAT function. 

We'll cover the simple examples, and then looks at formatting using the Dynamics GP DECPLCUR/DECPLQTY fields, that varies the decimal places for each item

 

Starting with SQL 2008, RAISERROR has supported 'variable substitution' The article explains how to format an error message that contains variables.

Our mandate is not to supply technically correct, in-depth articles on how things work. We're about quick code examples that you can read and get back to work.

Personally, when I want something explained in depth I'll search msdn or ask Pinal Dave <smiles>

I had the chance to shake hands with Beat Bucher (pronounced: B@) at GPUG Summit 2016 last week, glad to finally make the acquaintance. He's such a faithful contributor to the community.

At any rate, he's posted a SQL Security script on the GPUG user forum this morning and I'll re-blog it here so that it's always available to me. The query will show you the Dynamics users for a company and if the user is using the SQL 'Enforce Password' policy and the 'Expiration' policy. Great auditing tool.

The output looks like this

 

Well, I'm on a security kick this morning. Another request came in from a SOX company asking for a query to tell what rights (specifically what ADMIN rights) are assigned to SQL users. There are two queries below that return this info, one for the SERVER SYSADMIN role and one for the DATABASE DB_OWNER role
Here is a template Analytical Accounting GL Detail query
This is a template Dynamics GP Analytical Accounting Subledger query
This is a working SOPTransactionType document that will run in TWO. I shows how to use CREATETAXES = 1 in combination with DEFPRICING = 1 and DEFTAXSCHDS = 1
This customer was a little more difficult than most, we had to add TAXAMNT, TAXSCHID and IVITMTXB = 1 to the line and FREIGTBLE = 2 to the header to get it to go. Usually that's not required.

 

I was wondering if there are other ways to integrate data into GP other then  eConnect, Integration Manager, and web service.   It seems that these are the only options available.  Currently we are using eConnect to integrate data from Bullhorn (CRM) into GP.  We are using the Project Accounting Module.

I am looking into using BiZTalk, but it seems I still need to involve eConnect.  What I am trying to find out if there are other better options out there.

 

Thanks

 

Danny

Hi,

I'm writing an econnect import for POP Receiving Transaction Entry in Gp10.

I had it working for a while (purely doing it via SQL) - no XML.

I have recently come accross a situation where the Receipts don't match to the PO line items, but the should!   I'm at my wits end.  I setup a PO and Receipt like i usually do in test and make sure that the PO is in the system (only one line on the PO).  My values are as follows.

POPRCTNM =RCT100451

PONUMBER = MF10001 (this exists in GP with the one item "2016 BPO")

POLNENUM = 16348 (and it matches the ORD number on the PO for the same line item.

ITEMNMBR = "2016 BPO"

VNDITNUM = "2016 BPO" (noninventory number) - used to work with this value blank also.

UOFM = "Each" like on PO

*** Unit Cost DOES NOT Equal Unit Cost on the PO.  there is a difference.  Is this the culprit?

I get the error message taError code 2053:  Purchase Order Line Item does not exist."   but it does!

If anyone can assist... I am using the [taPopRcptLineInsert] proc. and I looked at the other posts here that show how to use the POLNENUM as a multiple of 16348.  

Any ideas on how to troubleshoot are welcome.

Thanks,
Mark

We have an General Journal entry that is suddenly experiencing an error preventing posting.

DOC 1 ERROR: Field 'Reference' does not have a default value.

Please advise on any other details I can provide to help resolve.

 

Thank you

 

Mark

 

 

How can I locate the transactions associated with a Batch?

 

Thanks

Mike

Hello:

Integration Manager threw up the following error, upon trying to import a noninventory item that does not exist in Item Maintenance:

Decimal Places passed in on UNITPRCE does not match setup.

We aren't registered for Inventory Control in GP 2013 R2 (12.00.1920).  But, we have service items held within the Item Maintenance window.  These are imported through Integration Manager.  Also, we're in a Multicurrency environment, even though the company that this happened in transacts only in Z-US$.  Our currency decimal placement for items is 2, as shown in both Item Currency Maintenance and in Sales Order Processing Setup for noninventory items.

Anyway, the resolution was to create a new service item in Item Maintenance.  You see, Integration Manager was trying to import--again--an item that did not exist in Item Maintenance.  True, Integration Manager indeed had given us an error saying that the item does not exist.

But, why would it also mention a decimal placement error?

To our administrators, this decimal placement error was misleading.  They spent a long time trying to figure out how to fix the decimal placement when, in reality, all that was required was to set up the new item in Item Maintenance.

The administrators told me that they want me to "fix" this error message so that it states what the actual problem is.  I tried telling them that the error messages are not customizable and that they can be misleading.  In any case, they want to know what we can do for faster identification of the real cause for issues preventing Integration Manager from successfully importing.

Any ideas?

Thanks!

John

I am getting these errors when importing a sales transaction with taxable freight. I have tried variations in my data but still get the errors. Only thing that worked was making the freight tax a line time with $0 extended.

What node and what values are required to import taxable freight on the header? I do not want GP or SmartConnect to calculate the taxes we are pushing the values from a POS system.

Thanks

Hello:

I have created a SQL query that pulls data from the following four tables:  RM00101, RM20101, RM20201, and CN00500.

The query is taking nearly two hours to execute and to display its data in SQL Management Studio.

In researching this, the majority of T-SQL experts advise placing indexes within these four tables.

Truthfully, I hesitate to do so for two reasons.  First, I have read that having additional indexes can actually degrade SQL performance.  Secondly, without the "supervision" of a Microsoft engineer, conducting modifications directly to the GP tables can corrupt those tables and, therefore, compromise GP.

Regardless, is there any harm of placing additional indexes into these tables?  If so, per Microsoft Dynamics Best Practices, what is the way to go about doing this?

My query is, in essence, a big select statement.  Would simply creating a stored procedure containing this query and executing the stored procedure allow for the data to be displayed much more quickly than two hours?

By the way, I found the article below in italics saying that, within reason, indexes can be added to the four tables.

Based on the syntax mentioned in this article, can you please advise me on what indexes I need to create?  I can test in our development environment and advise our DBA of such.

Otherwise, I have heard that using temp tables and indexing in those temp tables would be a good idea.  Does anyone have any SQL views that could be posted here to make such temp tables?  

Thank you!

John


When hundreds of thousands of records exist in a single table in the database, things can slow down a bit.  If the application is looking for information using a key, then this number of records should not be a problem.  But to look for a single customer, for example, using their phone number, when the phone number is not indexed, can take some time in a large table.
Supplemental indexes can be added to these tables!  Don't get too carried away as too many indexes will slow down the process of adding or updating records as all of the indexes need to be updated as well.  But having 3-4 additional indexes should not be a problem.
The following command is typed into the SQL Management Studio in a query window to create a new index.  Make sure to backup your database first and do this when no one else is on the system so mistakes can be corrected.
 
CREATE INDEX index_name ON table_name (column_name)

index_name is a name given to the index.  The ERP system uses the rule AKtablename99 to indicate an Alternate Key on the table and serializes the keys.  I use RLWKtablename99.  Use what you want.
table_name is the name of the table, like SOP10100 for the Sales Transaction
column_name is the name of the column the index should be built upon.  Two or more columns can be specified if needed but separate each with a comma.
If the index should be in decending order, add DESC at the end of the command.

Hi All,

I am using GP web service method "CreatePayablesInvoice" to generate a payable voice for an attorney.

I'm able to post invoice successfully from C# MVC web application, but when I go to GP client and post the batch from series post it shows error message.

Error Message :  The accounts payable distribution(s) does not equal the actual amount.The purchases distribution(s) does not equal the actual amount.

While debug I find that, the post is not able to pick payable and purchase account(attached screenshot ), which might cause the error.

Please suggest how to fix this issue.

 

Thank You,

Prakash

We've done some customizations that allow a user to launch an executable from within the GP Windows client, but we're experiencing different behaviors that we're unable to explain.

In some installations, it is required that the external executable be located within the GPRuntime\MyDevCompany directory.

In other installations, it is required that the external executable be located within the GPRuntime\Data\MyDevCompany directory. Note that default security has to be modified for this to work, as end users are denied the ability to traverse or run executables from within this directory per Windows security settings.

What determines the location from which GP is launching an external application? The issues apply to both GP 2015 and GP 2016.

Thanks.

I have this query:

USE SIZE

select e1.[Division] AS 'Division', Sum(e1.[July Emp Count]) AS 'Month1 Emp Count', Sum(e2.[Aug Emp Count]) AS 'Month2 Emp Count', Sum(e3.[Sept Emp Count]) AS 'Month3 Emp Count'

into #emp_total

from #emp e1

JOIN #emp2 e2 ON e1.Division=e2.Division

JOIN #emp3 e3 ON e1.Division=e3.Division

group by e1.division order by e1.Division

GO

select * from #emp_total order by division

--------------------------------------------------

The results in the three "Emp Count" columns doubles each time I add one of the JOINs.  Why?

For example, in this result:

Division Month1 Emp Count Month2 Emp Count Month3 Emp Count
120      68                68                76

These three counts SHOULD be 17, 17 and 19

I'm new to analytical accounting and have the following questions:

  1. Can I set up a GL Account which is automatically assigned to a dimension?
  2. Can I set up a GL Account which is automatically assigned to multiple dimensions?

 

 

Can I condition PO workflow with different approves depending on GL Account

For instance Employee Founds account approver 1 2000, aprover 2 10000....

                     Computer peripherals account  approver 2 2000, approver 3 10000

 

 

Thank you in advance.

I am currently attempting to Import a sales order to GP using eConnect.   I am able to get the document to upload but am unable to get taxes to generate using "CREATETAXES".

I have tried several methods including the references on DynDevelopers; however, I was unable to get this to work.

I am using C#.

Here is my current XML file.

<?xml version="1.0" encoding="utf-8"?>
    <SOPTransactionType>
        <taSopLineIvcInsert_Items>
            <taSopLineIvcInsert>
                <SOPTYPE>2</SOPTYPE>
                <CUSTNMBR>ADVANCED0001</CUSTNMBR>
                <DOCDATE>7/11/2016 12:00:00 AM</DOCDATE>
                <LOCNCODE>Warehouse</LOCNCODE>
                <ITEMNMBR>1-A3261A</ITEMNMBR>
                <UNITPRCE>16000</UNITPRCE>
                <XTNDPRCE>16000</XTNDPRCE>
                <QUANTITY>1</QUANTITY>
                <DOCID>STDORD</DOCID>
                <ITMTSHID>USASTCITY-6*</ITMTSHID>
                <TAXSCHID>USASTCITY-6*</TAXSCHID>
            </taSopLineIvcInsert>
        </taSopLineIvcInsert_Items>
 
        <taSopHdrIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <DOCID>STDORD</DOCID>
            <TAXSCHID>USASTCITY-6*</TAXSCHID>
            <SHIPMTHD>GROUND</SHIPMTHD>
            <LOCNCODE>Warehouse</LOCNCODE>
            <DOCDATE>7/11/2016 12:00:00 AM</DOCDATE>
            <CUSTNMBR>ADVANCED0001</CUSTNMBR>
            <ShipToName>Warehouse</ShipToName>
            <ADDRESS1>129 Church Street</ADDRESS1>
            <CITY>New Haven</CITY>
            <STATE>CT</STATE>
            <ZIPCODE>06511</ZIPCODE>
            <COUNTRY>USA</COUNTRY>
            <SUBTOTAL>16000</SUBTOTAL>
            <DOCAMNT>16000</DOCAMNT>
            <BACHNUMB>Batch 1</BACHNUMB>
            <CREATETAXES>1</CREATETAXES>
            <DEFTAXSCHDS>1</DEFTAXSCHDS>
        </taSopHdrIvcInsert>
    </SOPTransactionType>
</eConnect>

I am currently attempting to pass taxes via eConnect but keep getting the following error.

"Error Description = Payment total does not match line deposit + credit card payment total"

See XML File Below.

<?xml version="1.0" encoding="UTF-8"?>
<eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <SOPTransactionType>
      <eConnectProcessInfo xsi:nil="true" />
      <taRequesterTrxDisabler_Items xsi:nil="true" />
      <taUpdateCreateItemRcd xsi:nil="true" />
      <taUpdateCreateCustomerRcd xsi:nil="true" />
      <taCreateCustomerAddress_Items xsi:nil="true" />
      <taSopSerial_Items xsi:nil="true" />
      <taSopLotAuto_Items xsi:nil="true" />
      <taSopLineIvcInsert_Items>
         <taSopLineIvcInsert>
            <SOPTYPE>2</SOPTYPE>
            <CUSTNMBR>ADVANCED0001</CUSTNMBR>
            <DOCDATE>7/11/2016 12:00:00 AM</DOCDATE>
            <LOCNCODE>Warehouse</LOCNCODE>
            <ITEMNMBR>1-A3261A</ITEMNMBR>
            <UNITPRCE>16000</UNITPRCE>
            <XTNDPRCE>16000</XTNDPRCE>
            <QUANTITY>1</QUANTITY>
            <DOCID>STDORD</DOCID>
         </taSopLineIvcInsert>
      </taSopLineIvcInsert_Items>
      <taSopLineIvcInsertComponent_Items xsi:nil="true" />
      <taSopTrackingNum_Items xsi:nil="true" />
      <taSopCommissions_Items xsi:nil="true" />
      <taSopLineIvcTaxInsert_Items>
         <taSopLineIvcTaxInsert>
            <SOPTYPE>2</SOPTYPE>
            <CUSTNMBR>ADVANCED0001</CUSTNMBR>
            <SALESAMT>16000</SALESAMT>
            <TAXDTLID>USASTE-PS6N0</TAXDTLID>
            <STAXAMNT>960</STAXAMNT>
         </taSopLineIvcTaxInsert>
      </taSopLineIvcTaxInsert_Items>
      <taCreateSopPaymentInsertRecord_Items xsi:nil="true" />
      <taSopUserDefined xsi:nil="true" />
      <taSopDistribution_Items xsi:nil="true" />
      <taAnalyticsDistribution_Items xsi:nil="true" />
      <taSopMultiBin_Items xsi:nil="true" />
      <taSopHdrIvcInsert>
         <SOPTYPE>2</SOPTYPE>
         <DOCID>STDORD</DOCID>
         <TAXSCHID>USASTE-PS6N0</TAXSCHID>
         <SHIPMTHD>GROUND</SHIPMTHD>
         <TAXAMNT>960</TAXAMNT>
         <LOCNCODE>Warehouse</LOCNCODE>
         <DOCDATE>7/11/2016 12:00:00 AM</DOCDATE>
         <CUSTNMBR>ADVANCED0001</CUSTNMBR>
         <ShipToName>Warehouse</ShipToName>
         <ADDRESS1>129 Church Street</ADDRESS1>
         <CITY>New Haven</CITY>
         <STATE>CT</STATE>
         <ZIPCODE>06511</ZIPCODE>
         <COUNTRY>USA</COUNTRY>
         <SUBTOTAL>16000</SUBTOTAL>
         <DOCAMNT>16960</DOCAMNT>
         <PYMTRCVD>16960</PYMTRCVD>
         <BACHNUMB>Batch 1</BACHNUMB>
         <DEFPRICING>1</DEFPRICING>
      </taSopHdrIvcInsert>
      <taSopToPopLink xsi:nil="true" />
      <taSopUpdateCreateProcessHold xsi:nil="true" />
      <taCreateSOPTrackingInfo xsi:nil="true" />
      <taMdaUpdate_Items xsi:nil="true" />
   </SOPTransactionType>
</eConnect>

the ACCATNUM field of GL00100 table, describing defined to number 48 (Non-Financial Accounts). however in the chart of accounts of the company ACCATNUM reaches number 54. Where can I find the description of the ACCATNUM 49 to 54?

Error: Either Item Number or Vendor Item Number is required

I have all required fields Recipt Number Vendor Id Unit cost and Extended cost but i get this

 

Error Number = 4849  Stored Procedure= taPopEnterMatchInvLine  Error Description = Either Item Number or Vendor Item Number is required Node Identifier Parameters: taPopEnterMatchInvLine POPRCTNM = RCT24507 Related Error Code Parameters for Node : taPopEnterMatchInvLine ITEMNMBR = Note: This parameter was not passed in, no value for the parameter will be returned.

VNDITNUM = Note: This parameter was not passed in, no value for the parameter will be returned.

 

If Item number is not required why is this error happening??

 

Thanks

Vic

Hello,

  I am currently having an issue regarding Purchase Invoices.  

Scenario

  1. Purchase Order is Created
  2. Receipt is received
  3. Invoice pushed to GP via GP Web Services

The push is successful but the subtotal against the PO returns a negative value.

The subtotal against the PO SHOULD be 0 when it is closed out evenly like so.

 

When the auto invoice functionality is used, the sub total is equal 0 and the Purchase Order is closed correctly.

If the invoice is generated via GP Web Services then the subtotal is a negative on the Purchase Order.

 

As you can see in the above picture, the total is 2,500 however its leaving the PO with a negative 5,000 balance.

 

Please advise best approach.

Thanks,

--

Falk0r

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3