DynDeveloper.com - Your Dynamics GP Questions, Answered

	SOP Scripts
	eConnect
	taSopDeleteDocumentWrapper
	taSopLineDeleteWrapper
	taSOPLineIvcInsert Wrapper
	taSopLineIvcInsertComponent
	taSOPLotAuto - complete procedure
	Functions
	ddf_Address
	ddf_AllHoldsOnASOPDoc
	General
	Getting Email addresses from Dynamics GP
	Move a SOP transaction from open to history
	Posting Sequences
	SOP40200 'Allow' fields query
	sopGetIDNumber
	sopTrxDeleteWork
	Unvoid a SOP document
	Stored Procedures
	dd_SOP10102_UPD
	dd_SOPGetNextDocNumber/ sopGetIDNumber.
	dd_SOPPayment_INS
	dd_SOPRound - Round a SOP Multicurrency document to two decimal places
	dd_SOPTotal
	dd_SOPTotalTaxes
	dd_taSopVoidDocumentWrapper

Archives

Error Number = 7922 Stored Procedure= taPopRcptLineInsert Error Description = Item Number/Vendor Item number does not match a line on the Purchase Order

This is hardly writing down, the answer was simple. But we like to have examples of all the errors.

In this case, I had used the wrong vendor ID. I also needed to use the VNDITNUM that was on the PO, not our company's ITEMNMBR.

CTE Example showing Recursion

I find it helpful to have a piece of sample code to start a new query, especially the complicated ones. Here is a very simple query demonstrating recursion and CTE (common table expressions)

I took the code directly from a Microsoft example, but simplified it greatly to better suit our main goal here - Rapid Application Development (RAD)

BOM query

This one is worth the price of admission, if you've ever needed a BOM query

Here is my template query, it will work for BOM items nested no more than 6 deep, it uses nested cursors to accomplish the work.

Blank the user ID when logging onto Dynamics

This article is a copy of a Microsoft KB, I've copied it here because I need it from time to time.

I've seen two techniques - this one uses VBA, the other one makes the DEX.ini read only. Don't do that one, it's troublesome.

The Quantity available for this lot is less then the passed in SERLTQTY

I discovered a subtle issue in taIVTransactionLotInsert that is worth talking about.

The error:

Sql procedure error codes returned:

Error Number = 2094 Stored Procedure= taIVTransactionLotInsert Error Description = The Quantity available for this lot is less then the passed in SERLTQTY
Node Identifier Parameters: taIVTransactionLotInsert
IVDOCNBR = 00017789
IVDOCTYP = 1
ITEMNMBR = CAROL~SHADOWGRAYVELOUR~MS
LOTNUMBR = 14-645
SERLTQTY = 9975.00000

<taIVTransactionLotInsert>
<IVDOCNBR>00017789</IVDOCNBR>
<IVDOCTYP>1</IVDOCTYP>
<ITEMNMBR>CAROL~SHADOWGRAYVELOUR~MS     </ITEMNMBR>
<LOTNUMBR>14-645              </LOTNUMBR>
<SERLTQTY>9975.00000</SERLTQTY>
<ADJTYPE>1</ADJTYPE>
<LNSEQNBR>49152</LNSEQNBR>
<LOCNCODE>004       </LOCNCODE>
<DATERECD>7/27/2016</DATERECD>
</taIVTransactionLotInsert>

    <taIVTransactionLotInsert_Items>
      <taIVTransactionLotInsert>
        <IVDOCNBR>00017789</IVDOCNBR>
        <IVDOCTYP>1</IVDOCTYP>
        <ITEMNMBR>CAROL~SHADOWGRAYVELOUR~MS     </ITEMNMBR>
        <LOTNUMBR>14-645              </LOTNUMBR>
        <SERLTQTY>9975.00000</SERLTQTY>
        <ADJTYPE>1</ADJTYPE>
        <LNSEQNBR>49152</LNSEQNBR>
        <LOCNCODE>004       </LOCNCODE>
        <DATERECD>7/27/2016</DATERECD>
      </taIVTransactionLotInsert>
    </taIVTransactionLotInsert_Items>

    <taIVTransactionLineInsert_Items>
      <taIVTransactionLineInsert>
        <IVDOCNBR>00017789</IVDOCNBR>
        <IVDOCTYP>1</IVDOCTYP>
        <ITEMNMBR>CAROL~SHADOWGRAYVELOUR~MS     </ITEMNMBR>
        <LNSEQNBR>49152.00000</LNSEQNBR>
        <UOFM>Each    </UOFM>
        <TRXQTY>-9975.00000</TRXQTY>
        <TRXLOCTN>004       </TRXLOCTN>
        <USRDEFND1>14-645                                            </USRDEFND1>
      </taIVTransactionLineInsert>
    </taIVTransactionLineInsert_Items>

    <taIVTransactionHeaderInsert>
      <BACHNUMB>STOCKCNT160727 </BACHNUMB>
      <IVDOCNBR>00017789</IVDOCNBR>
      <IVDOCTYP>1</IVDOCTYP>
      <DOCDATE>7/27/2016</DOCDATE>
      <MDFUSRID>pn      </MDFUSRID>
      <PTDUSRID>pn      </PTDUSRID>
    </taIVTransactionHeaderInsert>
</IVInventoryTransactionType>
</eConnect>

ddf_FormatNumeric - formats a dollar value

This is a function that will take a numeric amount and format it as text, adding (optionally) a currency symbol and commas or the text 'hr' after the number; again with commas.

This screen shot shows how to call it with the optional dollar/hour parameter

Editors note:

Do you have a cool function that you use all the time? Why not post it, and let us put it in the SQL Menu along with our library?

Issue after loading Inactive EMPLOYEE (UPR00100) via SQL

I am loading a large number of EMPLOYEE records via SQL as part of a data conversion from a legacy HR Payroll system.

Many of the employees are inactive but need to be loaded for Pension history. When I load, I am setting the Inactive Flag, supplying a reason and an inactive date however when looking at the Employee Maintenance card if you go to a sub window such as HR Maintenance I am presented with a message indicating "If you do not want this employee to receive a check, please mark the Inactive Option on the Employee Maintenance window."

Does anyone have a suggestion what fields or tables I may have missed to eliminate this message?

Thanks in advance.

Dynamics GP SOP history information

I know that SOP30200 provides the historical information on sales order processing, however the data provided does not contain what the customer ordered...my question is, what table would I find the information about what the customer ordered?

RadWindow attached to a RadGrid as a modal popup

This is a complete working example of how to code a RadWindow that is attached to a RadGrid. Clicking on a link in the grid row will open a popup form that allows the data to be edited, clicking update will update the underlying data table and update the grid using AJAX.

We use stored procedures for data access, if you're not comfortable with that you'll want to change that.

As with all our code, this is a 'minimum needed to get it working example. The first page is in a Master Page, you'll need to provide that.

Add an event handler in code for a RadButton

Here are a couple of quick examples for adding an event handler in code for a RadButton

Example code for the ASP Dropdown list

This article contains quick examples of how to bind the ASP Dropdown list control

Export a list a tables from a form in dexterity

I am looking at a form in dexterity and the tables tab has 40 tables in it. I want to export this list of tables with physical names to text or excel etc. Is there any way to export this list without manually typing them in one by one?

Problem printing checks

We have encountered a strange problem printing checks using the 'Check With Stub on Top and Bottom' native GP Report Writer format. At some point of the check run, the header is populated with the vendor information, but then no details print below, and the actual check is made out to the previous vendor (last page). At this point, the check is misaligned. To make things more bizarre, the bottom stub header populates with the vendor information from the previous vendor (last page), and the current vendor's voucher number. Why? All subsequent check are now misaligned.

Any help or insight is greatly appreciated!

Thank you in advance.

Restoring GP Databases from Production to QA

We do a refresh of databases from Production to QA environment every 2 weeks. Whenever we do this, we have issues with SOME users logging into GP. We have to login as SA and change their password in GP and save it. Only then can the users login with the NEW password.

What process do we have to follow so the users can use the same passwords as their production account without help desk intervening after every refresh. Are there any best practices for this or some white papers?

Mass user setup

We are upgrading to GP2015 and plan to recreate all the users and assign new roles.

Is there a way to do a mass creation of users and assign the new roles without having to do them on at a time?

Update of Noteindx at CN40101

I am looking for the stored procedure that updates NOTEINDX field at CN40101 at Collection Management. Tried profiler without much of success.

company financial history table

Can someone tell me the name of the table that contains the company financial history?

Thank you in advance.

Subtotal (SUBTOTAL) does not match the line item totals

Error: Subtotal (SUBTOTAL) does not match the line item totals

Purchasing ->Purchase line items table reference

In GP (this case 2015)

Under smart list there is a "Purchasing ->Purchase line items"

I'm trying to determine which table/s is this pulling the information from.

any input is great.

Thanks in advance

Using GP 2010 and eConnect for POP receipts, can't find "taPopRcptLandedCost"

For some time I've had a successfully integrated and functioning module that runs eConnect, specifically the "taPopRcptLineInsert_ItemsTaPopRcptLineInsert" and "taPopRcptHdrInsert" objects are what I'm using in a "POPReceivingsType." Unfortunately, intellisense in Visual Studio doesn't recognize anything by the name of "taPopRcptLandedCost" and I'm not sure of the relationship of landed costs to the backing SQL tables. We have landed costs on all sorts of receipts, but there's nothing in POP10700. Can anyone point me in the right direction?