DynDeveloper.com

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.

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.

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.

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.

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 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?

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.

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

The main article for this article is here

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>

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?