DynDeveloper.com

DynDeveloper.com

Sign Up Now! Log In

Archives

 

This article will discuss the connection between GL and DTA, and tell how to relate the GL20000 table to the DTA tables in order to retrieve data.

DTA sub-distributions may be applied directly to a General Ledger distribution or may be applied in a sub ledger and will be transferred to GL when posted.

Currently, Sales Order Processing (SOP), Purchase Order Processing (POP) and Payables Management (PM) are supported.

DTA allows additional information to be stored about GL and sub ledger account entries. Entries can be grouped into many groups. Each group can be attached to the same distribution, and the sub-distributions are not required to add up to 100%. The entire sub distribution is not required, that needs to be accounted for as well.

In other words, a GL distribution for $100 might be

  • Linked to a group called Profit Center (PC) and have sub distributions for $50
  • Linked to a group called Cost Center (CC) and have sub distributions for $20 and $80

The code samples that follow take this into account. Generally we separate the groups and report on only one of them. We recommend creating business rules that require 100% distributions and then creating alerts of some sort that will show management when this rule is not followed. That way the code can assume 100% distributions.

The code below is written as a function, it returns a table. This method allows the function to be called from multiple reports and ensures consistent data.

Note that the script uses several different methods to join in the DTA10200 table. Trial and error has shown that the link is different in all the sub ledgers.

The GL join uses another function called dbo.f_GLDTAJoin to concatenate two fields so that they meet the format of the DTA10200.DTAREF field.

The different sections of the query look at the GL.SOURCDOC field to determine the sub ledger that the data originated in. There is a final query that picks up all the remaining lines that do not have a DTA sub distribution and includes them also.


 

This script will create the SQL function that is used to get the DTA distributions related to GL transactions.

This short code example shows how to code a GL Transaction in eConnect.

We show GLTransactionType, taGLTransactionHeaderInsert, taGLTransactionLineInsert

In this article, we discuss the difference between a Legacy endpoint and a Native endpoint in Dynamics GP Web Services for GP2010.

We cover how to get each of the references, and the differences between them.

This short code example shows how to code the GetCustomerByKey endpoint in Dynamics GP Web Services for GP2010. We use the legacy endpoint here.

I've had some issues with installing Web Services for GP 2010, so a good grasp on uninstalling is a real necessity.

The tasks below are a compilation from various sources, I'm trying to be as complete as possible.

After I completed the tasks below, my next install went perfectly.

I got the errors below during a recent upgrade from Web Services for GP 10 to GP 2010

General Access Denied Error


Cannot access the policy service. Verify that the configuration file exists and contains the correct path to the polcy service

Index was out of range. Must be non-negative and less then the size of the collection. Parameter name: index

An exception occurred that the exception subsystem was unable to log. Consult your System Administrator

In this article I talk about the solutions. I'll warn you in advance that you won't like some of it. Be warned.

 


 

This short example will demonstrate how to open note windows in the SOP Sales Transaction Entry form using Visual Studio Tools for Dynamics GP

The code is pretty self explanitory, we add an event handler in the Initialize method, and we show how to open 9 different windows. The hard part was figuring out what button went with what functionality, because they have semi cryptic names

 In our example below, we run of the CustomerNumberChanged event, and we open all the windows at one time.

If you use this code as-is you'll get some errors because some of the notes windows require something. For example the Batch note button requires a batch to open.

This is an XML file that shows a working POPReceivingsType. There are two noded, the taPopRctLineInsert and the taPopRcptHdrInsert.

 

I'm doing SOP to POP linking with eConnect for the second time. It can't be done with eConnect proper, but we add some code to the 'post' procedure for the PO eConnect transaction and it gets linked like that.

The first time there were issues, and we struggled with them, and then got the thing working. The issue is that you have to get the right combination of fields.

But I didn't write it down.

Now for the second time there are issues. Grrr.

Below is the taPoLinePost procedure that we use. Note that the @I_vUSERDEFND1 needs to have the Sales Order number, and @I_vUSERDEFND2 has the SOP line item sequence number

This short article will cover getting a reference to Web Services GP2010 in Visual Studio 2010

 

This is a great piece of code that shows how to repopulate the data in a RadGrid dropdown list based on data in the row. In our example we have a grid of grocery items and the second column is a dropdown list:

 

 

But, when we're on a 'Fruit' row we only want to see 'Fruit' items in the ddl.

 

Make sense?

In the past I've just attached images that I send via System.Net.Mail, but I have a requirement today to embed the image so that it shows when the email text is viewed.     

This piece of code shows how to embed an image into an email using the System.Net.Mail namespace

The f_4P_IVGetItemPrice function is a scalar function that returns the UOMPrice for an item.

It takes the ITEMNBR and CUSTNMBR for parameters, it looks up the PRCLEVEL for the customer and attempts to find a price for that item in the Base Unit of Measure (this might be changed to Selling Unit of Measure)

If the lookup fails, it returns the price for the item's default price schedule.

Failing that, it returns 0

Edit 6/19 updated to handle multiple Pricing Methods. The only one that we currently support are 1 and 3. Need more? Ask us.

 

Sql procedure error codes returned:

Error Number = 9276 Stored Procedure= taGLTransactionHeaderInsert Error Description = Decimal Places passed in to line does not match setup
Node Identifier Parameters: taGLTransactionHeaderInsert
BACHNUMB = PMM120202
JRNENTRY = 842149
REFRENCE = ISSUE
TRXDATE = 12/31/2011
TRXTYPE = 0


<taGLTransactionHeaderInsert>
<BACHNUMB>PMM120202</BACHNUMB>
<JRNENTRY>842149</JRNENTRY>
<REFRENCE>ISSUE</REFRENCE>
<TRXDATE>12/31/2011</TRXDATE>
<TRXTYPE>0</TRXTYPE>
</taGLTransactionHeaderInsert>

Anyone know how to fix this in Dynamics?
 

Can VS Tools create documents without opening the GP window?  For instance, I have a requirement to create an SOP document each time a certain distribution is keyed on a payables invoice.  Is it possible to just create the sales doc "in the background"?

 

Thanks,

Darren

Dear Srs.

I'm curious, why I can't  expand the Advance Financial Report Layout Window when creating or modifing a AF Report ? 

Thank You

 

Is there a way to join a GL and PM table using SQL?

I'm getting 'Sequence contains no elements' when submitting the document below...

<eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <POPReceivingsType>
    <eConnectProcessInfo xsi:nil="true" />
    <taRequesterTrxDisabler_Items xsi:nil="true" />
    <taUpdateCreateItemRcd xsi:nil="true" />
    <taUpdateCreateVendorRcd xsi:nil="true" />
    <taCreateVendorAddress_Items xsi:nil="true" />
    <taPopRcptLotInsert_Items xsi:nil="true" />
    <taPopRcptSerialInsert_Items xsi:nil="true" />
    <taPopRcptLineInsert_Items>
      <taPopRcptLineInsert>
        <POPTYPE>1</POPTYPE>
        <ITEMNMBR>JOE HOY-ADVANCE-H</ITEMNMBR>
        <VENDORID>Joerns</VENDORID>
        <VNDITNUM>JOE HOY-ADVANCE-H</VNDITNUM>
        <QTYSHPPD>1</QTYSHPPD>
      </taPopRcptLineInsert>
    </taPopRcptLineInsert_Items>
    <taPopRcptMultiBin_Items xsi:nil="true" />
    <taPopRcptLineTaxInsert_Items xsi:nil="true" />
    <taPopRctUserDefined xsi:nil="true" />
    <taPopDistribution_Items xsi:nil="true" />
    <taAnalyticsDistribution_Items xsi:nil="true" />
    <taPopRcptHdrInsert>
      <POPTYPE>1</POPTYPE>
      <receiptdate>2/14/2012</receiptdate>
      <BACHNUMB>ECON0214</BACHNUMB>
      <VENDORID>Joerns</VENDORID>
    </taPopRcptHdrInsert>
    <taMdaUpdate_Items xsi:nil="true" />
  </POPReceivingsType>
</eConnect>

The main article for this article is here

Problem:  Error Number = 4647

Sql procedure error codes returned: Error Number = 4647 Stored Procedure= taSopLineIvcInsert Error Description = Decimal Places passed in on XTNDPRCE does not match setup Node Identifier

 

Discussion

Both the following eConnect XML examples work on the test GP System. On the production GP System the first fails with the above error but the second works correctly. Note that XTNDPRCE doesn’t appear in zero quantity items in either procedure. Attempts to force XTNDPRCE to generate with 0.00 were unsuccessful.

POXML (Fails with above error)

<?xml version="1.0" ?>

- <eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

- <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>3</SOPTYPE>

<SOPNUMBE>INV0000172</SOPNUMBE>

<CUSTNMBR>1000694</CUSTNMBR>

<DOCDATE>2/17/2012</DOCDATE>

<LOCNCODE>WAREHOUSE</LOCNCODE>

<ITEMNMBR>MISC</ITEMNMBR>

<QUANTITY>0</QUANTITY>

<PRCLEVEL>STD</PRCLEVEL>

<ITEMDESC>Shipped: 11/10/2011</ITEMDESC>

<DOCID>STDINV</DOCID>

<ReqShipDate>11/10/2011</ReqShipDate>

<ACTLSHIP>11/10/2011</ACTLSHIP>

</taSopLineIvcInsert>

- <taSopLineIvcInsert>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000172</SOPNUMBE>

<CUSTNMBR>1000694</CUSTNMBR>

<DOCDATE>2/17/2012</DOCDATE>

<LOCNCODE>WAREHOUSE</LOCNCODE>

<ITEMNMBR>WO-INV</ITEMNMBR>

<UNITPRCE>1670.7300</UNITPRCE>

<XTNDPRCE>1670.73</XTNDPRCE>

<QUANTITY>1</QUANTITY>

<PRCLEVEL>STD</PRCLEVEL>

<ITEMDESC>Gran Mattino LXW</ITEMDESC>

<DOCID>STDINV</DOCID>

<ReqShipDate>11/10/2011</ReqShipDate>

<ACTLSHIP>11/10/2011</ACTLSHIP>

</taSopLineIvcInsert>

- <taSopLineIvcInsert>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000172</SOPNUMBE>

<CUSTNMBR>1000694</CUSTNMBR>

<DOCDATE>2/17/2012</DOCDATE>

<LOCNCODE>WAREHOUSE</LOCNCODE>

<ITEMNMBR>MISC</ITEMNMBR>

<QUANTITY>0</QUANTITY>

<PRCLEVEL>STD</PRCLEVEL>

<ITEMDESC>Above is refurb cost for S/N 42300715</ITEMDESC>

<DOCID>STDINV</DOCID>

<ReqShipDate>11/10/2011</ReqShipDate>

<ACTLSHIP>11/10/2011</ACTLSHIP>

</taSopLineIvcInsert>

- <taSopLineIvcInsert>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000172</SOPNUMBE>

<CUSTNMBR>1000694</CUSTNMBR>

<DOCDATE>2/17/2012</DOCDATE>

<LOCNCODE>WAREHOUSE</LOCNCODE>

<ITEMNMBR>MISC</ITEMNMBR>

<UNITPRCE>100.0000</UNITPRCE>

<XTNDPRCE>100.00</XTNDPRCE>

<QUANTITY>1</QUANTITY>

<PRCLEVEL>STD</PRCLEVEL>

<ITEMDESC>Refurb Handling Fee</ITEMDESC>

<DOCID>STDINV</DOCID>

<ReqShipDate>11/10/2011</ReqShipDate>

<ACTLSHIP>11/10/2011</ACTLSHIP>

</taSopLineIvcInsert>

</taSopLineIvcInsert_Items>

<taSopLineIvcInsertComponent_Items xsi:nil="true" />

<taSopTrackingNum_Items xsi:nil="true" />

<taSopCommissions_Items xsi:nil="true" />

<taSopLineIvcTaxInsert_Items xsi:nil="true" />

<taCreateSopPaymentInsertRecord_Items xsi:nil="true" />

<taSopUserDefined xsi:nil="true" />

- <taSopDistribution_Items>

- <taSopDistribution>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000172</SOPNUMBE>

<DISTTYPE>2</DISTTYPE>

<ACTINDX>118</ACTINDX>

<DEBITAMT>1770.73</DEBITAMT>

<CUSTNMBR>1000694</CUSTNMBR>

</taSopDistribution>

- <taSopDistribution>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000172</SOPNUMBE>

<DISTTYPE>1</DISTTYPE>

<ACTINDX>81</ACTINDX>

<CRDTAMNT>1770.73</CRDTAMNT>

<CUSTNMBR>1000694</CUSTNMBR>

</taSopDistribution>

- <taSopDistribution>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000172</SOPNUMBE>

<DISTTYPE>13</DISTTYPE>

<ACTINDX>153</ACTINDX>

<CRDTAMNT>1770.73</CRDTAMNT>

<CUSTNMBR>1000694</CUSTNMBR>

</taSopDistribution>

- <taSopDistribution>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000172</SOPNUMBE>

<DISTTYPE>13</DISTTYPE>

<ACTINDX>15</ACTINDX>

<DEBITAMT>1770.73</DEBITAMT>

<CUSTNMBR>1000694</CUSTNMBR>

</taSopDistribution>

</taSopDistribution_Items>

<taAnalyticsDistribution_Items xsi:nil="true" />

<taSopMultiBin_Items xsi:nil="true" />

- <taSopHdrIvcInsert>

<SOPTYPE>3</SOPTYPE>

<DOCID>STDINV</DOCID>

<SOPNUMBE>INV0000172</SOPNUMBE>

<ORIGNUMB>0</ORIGNUMB>

<TAXSCHID>WD-IL-TAX-SCHD</TAXSCHID>

<SHIPMTHD>GROUND</SHIPMTHD>

<LOCNCODE>WAREHOUSE</LOCNCODE>

<DOCDATE>2/17/2012 12:00:00 AM</DOCDATE>

<CUSTNMBR>1000694</CUSTNMBR>

<CSTPONBR>Not Available</CSTPONBR>

<ShipToName>Walgreens #10917</ShipToName>

<ADDRESS1>6906 University Blvd</ADDRESS1>

<CITY>Moon Township</CITY>

<STATE>PA</STATE>

<ZIPCODE>15108</ZIPCODE>

<SUBTOTAL>1770.7300</SUBTOTAL>

<DOCAMNT>1770.73</DOCAMNT>

<BACHNUMB>B201202170820</BACHNUMB>

<PRBTADCD>PRIMARY</PRBTADCD>

<USINGHEADERLEVELTAXES>1</USINGHEADERLEVELTAXES>

<CREATEDIST>0</CREATEDIST>

<CURNCYID>Z-US$</CURNCYID>

<FREIGTBLE>3</FREIGTBLE>

</taSopHdrIvcInsert>

<taSopToPopLink xsi:nil="true" />

<taSopUpdateCreateProcessHold xsi:nil="true" />

<taCreateSOPTrackingInfo xsi:nil="true" />

<taMdaUpdate_Items xsi:nil="true" />

</SOPTransactionType>

</eConnect>

 

WO XML (Works OK)

<?xml version="1.0" ?>

- <eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

- <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>3</SOPTYPE>

<SOPNUMBE>INV0000173</SOPNUMBE>

<CUSTNMBR>1000694</CUSTNMBR>

<DOCDATE>2/17/2012</DOCDATE>

<LOCNCODE>WAREHOUSE</LOCNCODE>

<ITEMNMBR>Date</ITEMNMBR>

<QUANTITY>0</QUANTITY>

<PRCLEVEL>STD</PRCLEVEL>

<ITEMDESC>Completed: 11/1/2011</ITEMDESC>

<DOCID>STDINV</DOCID>

<ReqShipDate>2/17/2012</ReqShipDate>

<ACTLSHIP>2/17/2012</ACTLSHIP>

</taSopLineIvcInsert>

- <taSopLineIvcInsert>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000173</SOPNUMBE>

<CUSTNMBR>1000694</CUSTNMBR>

<DOCDATE>2/17/2012</DOCDATE>

<LOCNCODE>WAREHOUSE</LOCNCODE>

<ITEMNMBR>MISC</ITEMNMBR>

<UNITPRCE>125.0000</UNITPRCE>

<XTNDPRCE>125.00</XTNDPRCE>

<QUANTITY>1</QUANTITY>

<PRCLEVEL>STD</PRCLEVEL>

<ITEMDESC>Removal of Gran Mattino LXW S/N 54701831</ITEMDESC>

<DOCID>STDINV</DOCID>

<ReqShipDate>2/17/2012</ReqShipDate>

<ACTLSHIP>2/17/2012</ACTLSHIP>

</taSopLineIvcInsert>

</taSopLineIvcInsert_Items>

<taSopLineIvcInsertComponent_Items xsi:nil="true" />

<taSopTrackingNum_Items xsi:nil="true" />

<taSopCommissions_Items xsi:nil="true" />

<taSopLineIvcTaxInsert_Items xsi:nil="true" />

<taCreateSopPaymentInsertRecord_Items xsi:nil="true" />

<taSopUserDefined xsi:nil="true" />

- <taSopDistribution_Items>

- <taSopDistribution>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000173</SOPNUMBE>

<DISTTYPE>2</DISTTYPE>

<ACTINDX>118</ACTINDX>

<DEBITAMT>125.00</DEBITAMT>

<CUSTNMBR>1000694</CUSTNMBR>

</taSopDistribution>

- <taSopDistribution>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000173</SOPNUMBE>

<DISTTYPE>1</DISTTYPE>

<ACTINDX>81</ACTINDX>

<CRDTAMNT>125.00</CRDTAMNT>

<CUSTNMBR>1000694</CUSTNMBR>

</taSopDistribution>

- <taSopDistribution>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000173</SOPNUMBE>

<DISTTYPE>13</DISTTYPE>

<ACTINDX>153</ACTINDX>

<CRDTAMNT>125.00</CRDTAMNT>

<CUSTNMBR>1000694</CUSTNMBR>

</taSopDistribution>

- <taSopDistribution>

<SOPTYPE>3</SOPTYPE>

<SOPNUMBE>INV0000173</SOPNUMBE>

<DISTTYPE>13</DISTTYPE>

<ACTINDX>15</ACTINDX>

<DEBITAMT>125.00</DEBITAMT>

<CUSTNMBR>1000694</CUSTNMBR>

</taSopDistribution>

</taSopDistribution_Items>

<taAnalyticsDistribution_Items xsi:nil="true" />

<taSopMultiBin_Items xsi:nil="true" />

- <taSopHdrIvcInsert>

<SOPTYPE>3</SOPTYPE>

<DOCID>STDINV</DOCID>

<SOPNUMBE>INV0000173</SOPNUMBE>

<ORIGNUMB>0</ORIGNUMB>

<TAXSCHID>WD-IL-TAX-SCHD</TAXSCHID>

<SHIPMTHD>GROUND</SHIPMTHD>

<LOCNCODE>WAREHOUSE</LOCNCODE>

<DOCDATE>2/17/2012 12:00:00 AM</DOCDATE>

<CUSTNMBR>1000694</CUSTNMBR>

<CSTPONBR>Not Available</CSTPONBR>

<ShipToName>Walgreens #06320 (District 192)</ShipToName>

<ADDRESS1>2204 N. Rolling Rd.</ADDRESS1>

<CITY>Windsor Mill</CITY>

<STATE>MD</STATE>

<ZIPCODE>21244</ZIPCODE>

<SUBTOTAL>125.0000</SUBTOTAL>

<DOCAMNT>125.00</DOCAMNT>

<BACHNUMB>B201202170820</BACHNUMB>

<PRBTADCD>PRIMARY</PRBTADCD>

<USINGHEADERLEVELTAXES>1</USINGHEADERLEVELTAXES>

<CREATEDIST>0</CREATEDIST>

<CURNCYID>Z-US$</CURNCYID>

<FREIGTBLE>3</FREIGTBLE>

</taSopHdrIvcInsert>

<taSopToPopLink xsi:nil="true" />

<taSopUpdateCreateProcessHold xsi:nil="true" />

<taCreateSOPTrackingInfo xsi:nil="true" />

<taMdaUpdate_Items xsi:nil="true" />

</SOPTransactionType>

</eConnect>

 

 

  • Cannot create a connection to data source 'XXXX01'. (rsErrorOpeningConnection)
    • You have specified integrated security or credentials in the connection string for the data source, but the data source is configured to use a different credential type. To use the values in the connection string, you must configure the unattended report processing account for the report server. (rsReportServerDataProviderError)

I'm getting this error after I change the datasource for a SQL SSRS report to use a specific set of credentials.