Decimal Places passed in on XTNDPRCE does not match setup

Error Number = 4647 Stored Procedure= taSopLineIvcInsert Error Description = Decimal Places passed in on XTNDPRCE does not match setup
Node Identifier Parameters: taSopLineIvcInsert
SOPNUMBE = 684426
SOPTYPE = 3
Related Error Code Parameters for Node : taSopLineIvcInsert
XTNDPRCE = 4.70

Resolution:

We had moved a GP company to another dynamics system db and ran check links.  Check links did not find the currency ID and auto created the currency id with 0 decimal. Comparing MC40000 and dynamics..MC40200 was the key for resolution.  Updated the decimal places for currency – the error econnect was able to process the transaction successfully.

Cheers

Sanjay

 

 

 

 

Error number = 1254 Store Procedure = taPATimeSheetLineInsert Error Description = Employee access does notexist for this project in the PA01408 table The project number is

Hi Sanjay,

I am providing you a copy of our scope agreement for your issue.

Issue Definition:

Customer is getting error message “Error number = 1254 Store Procedure = taPATimeSheetLineInsert Error Description = Employee access does notexist for this project in the PA01408 table
The project number is <NONE>” when sending a time sheet with eConnect.
Scope Agreement:

The scope of this case is to advise customer on the error message “Error number = 1254 Store Procedure = taPATimeSheetLineInsert Error Description = Employee access does notexist for this project in the PA01408 table The project number is <NONE>” when sending a time sheet with eConnect.

Assessment:
You had a phone call with Mike yesterday and are going to first attempt to mark the option Allow Add Access on the fly per below in your test company.

In Dynamics GP, go to Tools > Setup > Project > Timesheets. Click the Options button. The last line in Timesheet Setup Options is “Allow Add Access on the fly”. Be sure this is check marked and please verify you no longer receive the error.

If you do not agree with the scope defined above, or would like to amend it, please let me know as soon as possible.  Please let me know if you have any questions or concerns in the meantime. I will follow up with you in a few days if I don’t hear back. Have a great afternoon!

Best Regards,

The solution worked – however need to understand the consequence.

Follow ups:

 
1)      When the time sheet with none project was sent access to the none project was to be provided to user.
2)      The only way access to none project can be provided was to let permission be provided on fly.
3)      Because we gave the permission on for access release on fly for none projects, this is having allowing provisioning of access to all projects on the fly which is not what we want.
 
Can we first enable on the fly – get all user access to Non and then take away on the fly – this should solve the problem for now correct ?
Your analysis is correct and I understand in your 3rd remark that they do not want to allow access to on all projects by using the grant access on the fly.  This is actually a known issue (TFS#86337) that is marked to be fixed in the next release of GP16 later this year. 

Cheers!
Sanjay

eConnect – BRBankTransactionType – increase and decrease adjustment – VB.Net example

Imports System
Imports System.Xml
Imports System.Xml.Serialization
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic
Imports Microsoft.Dynamics.GP.eConnect
Imports Microsoft.Dynamics.GP.eConnect.Serialization
Imports System.Data.SqlClient
'  
Public Class Form1
    Public Sub eConnectSend(DR As DataRow, CMTrxType As Int16)
        Dim xmldocument As String
        Dim sConnectionString As String
        Dim xmlobject As String
        Using eConCall As New eConnectMethods
            Try
                SerializeObject("c:\test\xmlfile.xml ", DR, CMTrxType)
                Dim xmldoc As New Xml.XmlDocument
                xmldoc.Load("c:\test\xmlfile.xml")
                xmldocument = xmldoc.OuterXml
                sConnectionString = "data source=sanjayvm-pc;initial catalog=sanjay;integrated security=SSPI; persist security info=False;packet size=4096"
                xmlobject = eConCall.CreateTransactionEntity(sConnectionString, xmldocument)
            Catch exp As eConnectException
                MsgBox(exp.ToString)
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            Finally
                eConCall.Dispose()
                MsgBox("done")
            End Try
        End Using
    End Sub
    Public Shared Sub SerializeObject(ByVal filename As String, DR As DataRow, CMTrxType As Int16)
        'ACTNUMBR_1 ACTNUMBR_2 JRNENTRY SOURCDOC TRXDATE DEBITAMT  TRXSORCE  GPGL  CHECKBOOKID DT COMPANY
        Try
            Dim econnect As New eConnectType
            Dim dep As New BRBankTransactionType
            Dim dh As New taBRBankTransactionHeader
            With dh
                .Option = 1 'Enter Transaction
                .CMTrxType = CMTrxType
                .RcpType = 1 'Check
                .TRXDATE = CStr(DR("TRXDATE"))
                .CHEKBKID = CStr(DR("CHECKBOOKID"))
                .CMTrxNum = "JRN:" & CStr(DR("JRNENTRY")) & "-Store:" & Trim(CStr(DR("ACTNUMBR_2")))
                .paidtorcvdfrom = CStr(DR("SOURCDOC")) & "-" & CStr(DR("JRNENTRY"))
                .paidtorcvdfrom = Trim(CStr(DR("ACTDESCR")))
                .DSCRIPTN = CStr(DR("TRXSORCE"))
                .TRXAMNT = DR("DEBITAMT") + DR("CRDTAMNT")
            End With
            dep.taBRBankTransactionHeader = dh
            Dim DL(0) As taBRBankTransactionDist_ItemsTaBRBankTransactionDist
            Dim DL1 As New taBRBankTransactionDist_ItemsTaBRBankTransactionDist
            With DL1
                .Option = 1 'Enter Transaction
                .ACTNUMST = Trim(CStr(DR("ACTNUMBR_1"))) & "-" & Trim(CStr(DR("ACTNUMBR_2")))
                .DEBITAMT = DR("CRDTAMNT")
                .CRDTAMNT =DR("DEBITAMT")
                .DistRef = "DistRef"
            End With
            DL(0) = DL1
            dep.taBRBankTransactionDist_Items = DL
            ReDim Preserve econnect.BRBankTransactionType(0)
            econnect.BRBankTransactionType(0) = dep
            Dim fs As New FileStream(filename, FileMode.Create)
            Dim writer As New XmlTextWriter(fs, New UTF8Encoding)
            Dim serializer As New XmlSerializer(GetType(eConnectType))
            serializer.Serialize(writer, econnect)
            writer.Close()
        Catch ex As ApplicationException
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim SQLStr As String = "Server=localhost;Database=sanjayInterFace;Integrated Security=SSPI;"
        Dim SQLConn As SqlConnection = New SqlConnection(SQLStr)
        Dim CMTrxType As Int16
        SQLConn.Open()
        Dim cmd As SqlCommand = New SqlCommand("GetDeposit", SQLConn)
        CMTrxType = 5 'increase adjustment
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@COMPANY", "100")
        cmd.Parameters.AddWithValue("@DT", "2016-05-05 00:00:00")
        Dim adapter As New SqlDataAdapter(cmd)
        adapter.SelectCommand.CommandTimeout = 300
        Dim DS As New DataSet
        adapter.Fill(DS)
        For Each DR As DataRow In DS.Tables(0).Rows
            eConnectSend(DR, CMTrxType)
        Next
        adapter.Dispose()

        CMTrxType = 4 'decrease adjustment
        cmd = New SqlCommand("GetWithdraw", SQLConn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@COMPANY", "100")
        cmd.Parameters.AddWithValue("@DT", "2016-05-05 00:00:00")
        adapter = New SqlDataAdapter(cmd)
        adapter.SelectCommand.CommandTimeout = 300
        DS = New DataSet
        adapter.Fill(DS)
        SQLConn.Close()
        For Each DR As DataRow In DS.Tables(0).Rows
            eConnectSend(DR, CMTrxType)
        Next
        adapter.Dispose()
    End Sub
End Class
CREATE procedure GetWithdraw @COMPANY NVARCHAR(10)
	,@DT DATETIME
AS
BEGIN
SELECT			 [SANJAY].dbo.GL00105.ACTNUMBR_1 
				,[SANJAY].dbo.GL00105.ACTNUMBR_2 
				,[SANJAY].dbo.GL00100.ACTDESCR 
				,[SANJAY].dbo.GL20000.JRNENTRY 
				,[SANJAY].dbo.GL20000.SOURCDOC 
				,[SANJAY].dbo.GL20000.TRXDATE  
				,[SANJAY].dbo.GL20000.DEBITAMT 
				,[SANJAY].dbo.GL20000.CRDTAMNT
				,[SANJAY].dbo.GL20000.TRXSORCE
				,GPGL = (
					SELECT TOP 1 GPGL  FROM [SANJAYInterFace].[dbo].[VASTGPPAYLINK] WITH (NOLOCK) WHERE COMPANY = @COMPANY  AND VASTGL =  [SANJAY].dbo.GL00105.ACTNUMBR_1
				)
				,CHECKBOOKID = (
					SELECT TOP 1 CHECKBOOKID  FROM [SANJAYInterFace].[dbo].[VASTGPPAYLINK] WITH (NOLOCK) WHERE COMPANY = @COMPANY  AND VASTGL =  [SANJAY].dbo.GL00105.ACTNUMBR_1
				)
				,DT = @DT
				,COMPANY = @COMPANY
FROM            [SANJAY].dbo.GL00100  WITH (NOLOCK)  INNER JOIN
                [SANJAY].dbo.GL00105  WITH (NOLOCK)  ON [SANJAY].dbo.GL00100.ACTINDX = [SANJAY].dbo.GL00105.ACTINDX INNER JOIN
                [SANJAY].dbo.GL20000  WITH (NOLOCK)  ON [SANJAY].dbo.GL00100.ACTINDX = [SANJAY].dbo.GL20000.ACTINDX
WHERE			[SANJAY].dbo.GL20000.JRNENTRY IN (
												SELECT		[SANJAY].dbo.GL20000.JRNENTRY
												FROM        [SANJAY].dbo.GL20000 WITH (NOLOCK) INNER JOIN	
															[SANJAY].dbo.GL00105 WITH (NOLOCK) ON [SANJAY].dbo.GL20000.ACTINDX = [SANJAY].dbo.GL00105.ACTINDX
												WHERE		([SANJAY].dbo.GL20000.TRXDATE= CONVERT(DATETIME, @DT, 102)) AND [SANJAY].dbo.GL00105.ACTNUMBR_1 IN (SELECT VASTGL FROM [SANJAYInterFace].[dbo].[VASTGPPAYLINK] WITH (NOLOCK) WHERE COMPANY = @COMPANY)
												GROUP BY	[SANJAY].dbo.GL20000.SOURCDOC, [SANJAY].dbo.GL20000.JRNENTRY, [SANJAY].dbo.GL20000.TRXDATE
												HAVING		([SANJAY].dbo.GL20000.SOURCDOC = 'GPGateway')
				) AND  [SANJAY].dbo.GL00105.ACTNUMBR_1 IN ( SELECT VASTGL FROM [SANJAYInterFace].[dbo].[VASTGPPAYLINK] WITH (NOLOCK) WHERE COMPANY = @COMPANY) AND CRDTAMNT > 0

END


* FOR GetDeposit change where CRDTAMT = 0

 

 

Cheers!

Create Journal Entries using e Connect Store Procs

Obtain Next Journal Number:
USE [GP Company Database]
GO
DECLARE @return_value int,
@O_vJournalEntryNumber char(13),
@O_iErrorState int
EXEC @return_value = [dbo].[taGetNextJournalEntry]
@O_vJournalEntryNumber = @O_vJournalEntryNumber OUTPUT,
@O_iErrorState = @O_iErrorState OUTPUT
SELECT @O_vJournalEntryNumber as N’@O_vJournalEntryNumber’,
@O_iErrorState as N’@O_iErrorState’
SELECT ‘Return Value’ = @return_value >> This is the Journal number which should be used below
GO
*****************************************************************************************
Create GL Batch Record
USE [GP Company Database]
GO
DECLARE @return_value int,
@O_iErrorState int,
@oErrString varchar(255)

EXEC @return_value = [dbo].[taGLTransactionHeaderInsert]
@I_vBACHNUMB = N’batchnumb’,
@I_vJRNENTRY = 344, >> As queried above
@I_vREFRENCE = N’reference’,
@I_vTRXDATE = N’7/6/2015′,
@I_vTRXTYPE = 0,
@I_vSERIES = 2,
@O_iErrorState = @O_iErrorState OUTPUT,
@oErrString = @oErrString OUTPUT

SELECT @O_iErrorState as N’@O_iErrorState’,
@oErrString as N’@oErrString’

SELECT ‘Return Value’ = @return_value

GO
*****************************************************************************************
Create Debit Line of Journal:
USE [GP Company Database]
GO

DECLARE @return_value int,
@O_iErrorState int,
@oErrString varchar(255)

EXEC @return_value = [dbo].[taGLTransactionLineInsert]
@I_vBACHNUMB = N’batchnumb’,
@I_vJRNENTRY = 344,
@I_vACTINDX = 101,
@I_vCRDTAMNT = 0,
@I_vDEBITAMT = 100,
@I_vDSCRIPTN = N’desc’,
@O_iErrorState = @O_iErrorState OUTPUT,
@oErrString = @oErrString OUTPUT

SELECT @O_iErrorState as N’@O_iErrorState’,
@oErrString as N’@oErrString’

SELECT ‘Return Value’ = @return_value
*****************************************************************************************
Create Credit Line of Journal
USE [GP Company Database]
GO

DECLARE @return_value int,
@O_iErrorState int,
@oErrString varchar(255)

EXEC @return_value = [dbo].[taGLTransactionLineInsert]
@I_vBACHNUMB = N’batchnumb’,
@I_vJRNENTRY = 344,
@I_vACTINDX = 100,
@I_vCRDTAMNT = 100,
@I_vDEBITAMT = 0,
@I_vDSCRIPTN = N’desc’,
@O_iErrorState = @O_iErrorState OUTPUT,
@oErrString = @oErrString OUTPUT

SELECT @O_iErrorState as N’@O_iErrorState’,
@oErrString as N’@oErrString’

SELECT ‘Return Value’ = @return_value
*****************************************************************************************
This should create a valid journal entry in Dynamics GP.

Check stored proc for full parameter list.

Cheers!
Sanjay

eConnect – Inventory – Item create – example – VB.net – C#

Inventory

VB

Imports System
Imports System.Xml
Imports System.Xml.Serialization
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic
Imports Microsoft.Dynamics.GP.eConnect
Imports Microsoft.Dynamics.GP.eConnect.Serialization

Public Class Inventory
    Public Sub eConnectSend()
        'Serialized XML File         
        Dim xmldocument As String
        'Connection String         
        Dim sConnectionString As String
        'Result         
        Dim xmlobject As String
        Using eConCall As New eConnectMethods
            Try
                SerializeObject("c:\test\xmlfile.xml ")
                Dim xmldoc As New Xml.XmlDocument
                xmldoc.Load("c:\test\xmlfile.xml")
                xmldocument = xmldoc.OuterXml
                sConnectionString = "data source=sqlserver;initial catalog=two;integrated security=SSPI; persist security info=False;packet size=4096"
                xmlobject = eConCall.CreateTransactionEntity(sConnectionString, xmldocument)
            Catch exp As eConnectException
                MsgBox(exp.ToString)
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            Finally
                eConCall.Dispose()
                MsgBox("done")
            End Try
        End Using
    End Sub
    Public Shared Sub SerializeObject(ByVal filename As String)
        Try
            Dim econnect As New eConnectType
            Dim iv As New IVItemMasterType

            'inventory item
            Dim itm As New taUpdateCreateItemRcd
            With itm
                .ITEMNMBR = "ITEMNAME"
                .ITEMDESC = "Item description"
                .ITMSHNAM = "Short Description"
                .ITMGEDSC = "Generic description"
                .ITMCLSCD = "FG-ACT"
                .ITEMTYPE = 1
                .UOMSCHDL = "EACH"
                .UpdateIfExists = 1
            End With
            iv.taUpdateCreateItemRcd = itm

            ReDim Preserve econnect.IVItemMasterType(0)
            econnect.IVItemMasterType(0) = iv

            Dim fs As New FileStream(filename, FileMode.Create)
            Dim writer As New XmlTextWriter(fs, New UTF8Encoding)
            Dim serializer As New XmlSerializer(GetType(eConnectType))
            serializer.Serialize(writer, econnect)
            writer.Close()
        Catch ex As ApplicationException
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        eConnectSend()
    End Sub
End Class

C#

using System;
using System.IO;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Xml.Serialization;
using Microsoft.Dynamics.GP.eConnect;
using Microsoft.Dynamics.GP.eConnect.Serialization;

namespace eConnectSample
{
    public partial class InventoryItemCreate : Form
    {
        public InventoryItemCreate()
        {
            InitializeComponent();
        }

        private static void sendtogp()
        {
            using (eConnectMethods eConCall = new eConnectMethods())
            {
                try
                {
                    SerializeObject("c:\\test\\eConnect.xml");
                    XmlDocument xmldoc = new XmlDocument();
                    xmldoc.Load("c:\\test\\eConnect.xml");
                    string send2gp = xmldoc.OuterXml;
                    string sConnectionString = "data source=sqlserver;initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096";
                    string ivitem = eConCall.CreateTransactionEntity(sConnectionString, send2gp);
                }
                catch (eConnectException exp)
                {
                    MessageBox.Show(exp.ToString());
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    eConCall.Dispose();
                    MessageBox.Show("done");
                }
            }
        }

        private static void SerializeObject(string filename)
        {

            try
            {
                taUpdateCreateItemRcd itm = new taUpdateCreateItemRcd();
                itm.ITEMNMBR = "ITEMNAME";
                itm.ITEMDESC = "Item description";
                itm.ITMSHNAM = "Short Description";
                itm.ITMGEDSC = "Generic description";
                itm.ITMCLSCD = "FG-ACT";
                itm.ITEMTYPE = 1;
                itm.UOMSCHDL = "EACH";
                itm.UpdateIfExists = 1;

                IVItemMasterType iv = new IVItemMasterType();
                iv.taUpdateCreateItemRcd = itm;

                IVItemMasterType[] myIV = { iv };
                eConnectType eConnect = new eConnectType();
                eConnect.IVItemMasterType = myIV;

                FileStream fs = new FileStream(filename, FileMode.Create);
                XmlTextWriter writer = new XmlTextWriter(fs, new UTF8Encoding());

                XmlSerializer serializer = new XmlSerializer(eConnect.GetType());
                serializer.Serialize(writer, eConnect);
                writer.Close();
            }

            catch (eConnectException ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void Send2GP_Click(object sender, EventArgs e)
        {
            sendtogp();
        }
    }
}

Adapted from Microsoft Dynamics GP eConnect Samples installed during eConnect installation.

Line breaks are issued to fit script within screen. Remove line breaks.

THIS CODE AND INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

XML File

<?xml version="1.0" ?>
<eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <IVItemMasterType>
    <eConnectProcessInfo xsi:nil="true"/>
    <taRequesterTrxDisabler_Items xsi:nil="true"/>
    <taUpdateCreateItemRcd>
      <ITEMNMBR>
        ITEMNAME
      </ITEMNMBR>
      <ITEMDESC>
        Item description
      </ITEMDESC>
      <ITMSHNAM>
        Short Description
      </ITMSHNAM>
      <ITMGEDSC>
        Generic description
      </ITMGEDSC>
      <ITMCLSCD>
        FG-ACT
      </ITMCLSCD>
      <UOMSCHDL>
        EACH
      </UOMSCHDL>
    </taUpdateCreateItemRcd>
    <taUpdateCreateItemCurrencyRcd_Items xsi:nil="true"/>
    <taIVCreateItemPriceListLine_Items xsi:nil="true"/>
    <taIVCreateItemPriceListHeader xsi:nil="true"/>
    <taItemSite_Items xsi:nil="true"/>
    <taCreateItemVendors_Items xsi:nil="true"/>
    <taCreateKitItemRcd_Items xsi:nil="true"/>
    <taCreateInternetAddresses_Items xsi:nil="true"/>
  </IVItemMasterType>
</eConnect>

 

 

Extract from eConnect Programmers Reference Guide for immediate reference.

<taUpdateCreateItemRcd>
Element name Data type Length Required Description
ITEMNMBR string 30 Y Item number
ITEMDESC string 100 N Item description
ITMSHNAM string 15 N Short description
ITMGEDSC string 10 N Generic description
ITMCLSCD string 10 N Class ID
ITEMTYPE i4 2 N Item type
1=Sales inventory;
2=Discontinued;
3=Kit;
4=Miscellaneous charges;
5=Services;
6=Flat fee
Default is one on new record
VCTNMTHD i4 2 N Valuation method:
0=Any ITEMTYPE other than 1 or 2;
1=FIFO perpetual;
2=LIFO perpetual;
3=Average perpetual;
4=FIFO periodic;
5=LIFO periodic
Default is one on new record
TAXOPTNS i4 2 N Sales tax options:
1=Taxable;
2=Nontaxable;
3=Base on customers
Default is one on new record
ITMTSHID string 15 N Tax schedule ID
UOMSCHDL string 10 N Unit of measure schedule ID
Required for a new record, but can roll down from class
ITEMSHWT number N Shipping weight; only used when ITEMTYPE=1, 2, or 3
TCC string 30 N Tax community code; only used if the “Enable Intrastat Tracking” option is enabled in Company Setup
CNTRYORGN string 6 N Country origin; only used if the “Enable Intrastat Tracking” option is enabled in Company Setup
DECPLQTY i4 2 N Quantity Decimals; DECPLQTY can equal 0, 1, 2, 3, 4, or 5; if ITEMTYPE=3, then DECPLQTY must be set to zero
Default is zero on new record
DECPLCUR i4 2 N Currency Decimals; used for item if not registered for multi-currency or used for functional currency
If registered for multi-currency and want other currencies, use the <taUpdateCreateItemCurrencyRcd> node.
Purchase_Tax_Options i4 2 N Purchasing tax option:
1=Taxable;
2=Nontaxable;
3=Base on vendors
Default is one on new record
Purchase_Item_Tax_Schedu string 15 N Purchase tax schedule ID
STNDCOST number 21 N Standard cost
CURRCOST number 21 N Current cost
LISTPRCE number 21 N List price; used if multi-currency is not registered or used for functional currency if multi-currency is registered
NOTETEXT string 8000 N Note text
ALTITEM1 string 30 N Alternate item 1
ALTITEM2 string 30 N Alternate item 2
ITMTRKOP i4 2 N Item tracking:
1=None;
2=Serial Numbers;
3=Lot Numbers
Used when ITEMTYPE= 1 or 2
Default is one on new record
LOTTYPE string 10 N Lot category
LOTEXPWARN i4 1 N Lot Expiration Warn:
0 = no warning
1 = warnings enabled
If this item is not lot tracked, the value of this element is not used.
LOTEXPWARNDAYS i4 2 N Days Before Lot Expires:
Valid entries equal 0 – 999.
If LOTEXPWARN is 0 then LOTEXPWARNDAYS always defaults to 0. If this item is not lot tracked, the value of this element is not used.
INCLUDEINDP i4 1 N Include in Demand Planning;
0=Do Not Include;
1=Include
Default is zero on new record
MINSHELF1 i4 2 N Minimum shelf life 1:
Valid entries equal 0-9999
Use when ITMTRKOP=3
Default is zero on new record
MINSHELF2 i4 2 N Minimum shelf life 2:
Valid entries equal 0-9999
Use when ITMTRKOP=3
Default is zero on new record
ALWBKORD i4 1 N Allow back orders:
0=Do not allow back orders;
1=Allow back orders
Default is zero on new record
WRNTYDYS i4 2 N Warranty days; valid entries equal 0-9999
Default is zero on new record
ABCCODE i4 2 N ABC code:
1=None;
2=A;
3=B;
4=C
Default is one on new record
USCATVLS_1 string 10 N User category 1
USCATVLS_2 string 10 N User category 2
USCATVLS_3 string 10 N User category 3
USCATVLS_4 string 10 N User category 4
USCATVLS_5 string 10 N User category 5
USCATVLS_6 string 10 N User category 6
KPCALHST i4 1 N Maintain history—calendar year:
0=Do not keep history;
1=Keep history
Default is 0 on new record
KPERHIST i4 1 N Maintain history—fiscal year:
0=Do not keep history;
1=Keep history
Default is 0 on new record
KPTRXHST i4 1 N Maintain history—transaction:
0=Do not keep history;
1=Keep history
Default is 0 on new record
KPDSTHST i4 1 N Maintain history—distribution:
0=Do not keep history;
1=Keep history
Default is 0 on new record
IVIVACTNUMST string 75 N Inventory account
IVIVOFACTNUMST string 75 N Inventory offset account
IVCOGSACTNUMST string 75 N Cost of goods sold account
IVSLSACTNUMST string 75 N Sales account
IVSLDSACTNUMST string 75 N Markdown account
IVSLRNACTNUMST string 75 N Sales return account
IVINUSACTNUMST string 75 N In use account
IVINSVACTNUMST string 75 N In service account
IVDMGACTNUMST string 75 N Damaged account
IVVARACTNUMST string 75 N Variance account
DPSHPACTNUMST string 75 N Drop-ship items account
PURPVACTNUMST string 75 N Purchase price variance account
UPPVACTNUMST string 75 N Unrealized purchase price variance account
IVRETACTNUMST string 75 N Inventory returns account
ASMVRACTNUMST string 75 N Assembly variance account
KTACCTSR i4 2 N Cost of goods sold account source:
0=From component item;
1=From kit item
Default is zero on new record
PRCHSUOM string 8 N Purchasing unit of measure
Revalue_Inventory i4 1 N Revalue inventory for cost variance:
0=Do not revalue;
1=Revalue
Default is zero on new record
Tolerance_Percentage number 21 N Tolerance percentage
LOCNCODE string 10 N Location code; assigns site to item if it is not already assigned
PRICMTHD i4 2 N Price method:
1=Currency amount;
2=Percent of List price;
3=Percent of markup of current cost;
5=Percent margin of current cost;
6=Percent margin of standard cost
Default is one
PriceGroup string 10 N Price group
UseQtyOverageTolerance i4 1 N Use quantity overage tolerance:
0=Do not use;
1=Use
UseQtyShortageTolerance i4 1 N Use quantity shortage tolerance:
0=Do not use;
1=Use
QtyOverTolerancePercent i4 4 N Quantity over tolerance percent:
Provide a value of 0 – 99999. For example, 99999 equals 99.999.
QtyShortTolerancePercent i4 4 N Quantity short tolerance percent:
Provide a value of 0 – 99999. For example, 99999 equals 99.999.
UseItemClass i4 1 N Flag to have class setting roll down to elements that are not passed in; uses the ITMCLSCD class
UpdateIfExists i4 1 N Flag to allow item data to be updated if it exists: 0=Do not update;
1=Update if exists;
2=Create if record does not exist and exits without error if record does exist
RequesterTrx i4 2 N Requester transaction
0=False;
1=True (if True, it populates the requester shadow table)

Cheers!
Sanjay

Errors have been found in this check batch. Please correct the errors before printing

Today our AP Department called in and reported they were not able print checks, and when our AP Department calls in, some is wrong, they can handle almost any AP GP functions and issues.  Got the error messages it just reads as follows:

Check batch errors

As routine question asked the AP team, did you print the edit list and were there any errors reported on it ?  As expected got a neatly organized and crisp report – no point reviewing it, sure it would have been already done, but I have asked it have to review it,  completed the formality,  as expected it wasn’t helpful.

Based on experience knew that one of the vendor in the check batch is not active which is causing the error, the problem is how do we identify the vendor for a batch of 60  odd checks ?

Solution:
1) Generated  just in time Dex SQL Log, which did list the last vendor information when error occurred.  We were able to activate the vendor and when reprocessed the batch again the problem re appeared.

2) We realized that the recent vendor reorganization process where multiple vendors were inactivated (Yes – not from GP) is the root cause for the problem, another reason why we should not update GP tables outside GP.  There are other vendors in the check batch who needs to be activated.  Is there a easy way ?  The Dex SQL is ok, but is sequential and stops after every error.

3) Decided to write a SSRS Report quickly to check vendor status of all check batch vendors.  Which table is the payment info stored ?  Identifying the table  was not a problem,  all I had to do was to  connect to   http://www.sanjaykumar.us/index.php/category/dynamics-gp/tables-dynamics-gp/pm/
and search for the word work….
payment work

The table we needed was PM10300.   Writing the query there after was a flash….

DECLARE @batchnumb AS VARCHAR(21) 

SELECT @batchnumb = '?' 

SELECT vendorid, 
       vendname, 
       CASE vendstts 
         WHEN 1 THEN 'Active' 
         WHEN 2 THEN 'In Active' 
         WHEN 3 THEN 'Temp' 
       END AS status 
FROM   pm00200 WITH (nolock) 
WHERE  vendorid IN (SELECT vendorid 
                    FROM   pm10300 WITH (nolock) 
                    WHERE  bachnumb = @Batchnumb) 
       AND vendstts <> 1

This query returns active vendors not active at check batch and is delivered through SSRS report.  Now Our AP Department can manage this the situation themselves!  Turned out to be Happy Halloween after all.

Cheers!
Sanjay

e Connect – SOP invoice – example

Imports System
Imports System.Xml
Imports System.Xml.Serialization
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic
Imports Microsoft.Dynamics.GP.eConnect
Imports Microsoft.Dynamics.GP.eConnect.Serialization
Public Class SOPinvoice
    Public Sub eConnectSend()
        'Serialized XML File         
        Dim xmldocument As String
        'Connection String         
        Dim sConnectionString As String
        'Result         
        Dim xmlobject As String
        sConnectionString = "data source=[SQLSERVER];initial catalog=TWO;integrated security=SSPI; persist security info=False;packet size=4096"
        Dim NextNumberObject As New GetNextDocNumbers
        Dim SOPnumber As String = NextNumberObject.GetNextSOPNumber(IncrementDecrement.Increment, "STDINV", SopType.SOPInvoice, sConnectionString)

        Using eConCall As New eConnectMethods
            Try
                SerializeObject("c:\TEST\xmlfile1.xml ", SOPnumber, sConnectionString)
                Dim xmldoc As New Xml.XmlDocument
                xmldoc.Load("c:\TEST\xmlfile1.xml")
                xmldocument = xmldoc.OuterXml
                xmlobject = eConCall.CreateTransactionEntity(sConnectionString, xmldocument)
            Catch exp As eConnectException
                MsgBox(exp.ToString)
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            Finally
                eConCall.Dispose()
                MsgBox("Success," + SOPnumber + " Created")
            End Try
        End Using
    End Sub
    Public Shared Sub SerializeObject(ByVal filename As String, SOPnumber As String, sConnectionString As String)
        Try
            Dim econnect As New eConnectType
            Dim SOPINV As New SOPTransactionType
            'header record
            Dim SOPINVHEAD As New taSopHdrIvcInsert
            With SOPINVHEAD
                .SOPTYPE = 3
                .DOCID = "STDINV"
                .SOPNUMBE = SOPnumber
                .DOCDATE = "04/12/2017"
                .CUSTNMBR = "AARONFIT0001"
                .BACHNUMB = "TEST"

            End With
            SOPINV.taSopHdrIvcInsert = SOPINVHEAD

            'line record
            Dim SOPLINE(0) As taSopLineIvcInsert_ItemsTaSopLineIvcInsert
            Dim SOPLINE1 As New taSopLineIvcInsert_ItemsTaSopLineIvcInsert
            With SOPLINE1
                .SOPTYPE = 3
                .DOCID = "STDINV"
                .SOPNUMBE = SOPnumber
                .CUSTNMBR = "AARONFIT0001"
                .ITEMNMBR = "100XLG"
                .QUANTITY = 1
                .DOCDATE = "04/12/2017"
                '.Print_Phone_NumberGB = 0
                '.PRINT_PHONE_NUMBERGBSPECIFIED = False

            End With
            SOPLINE(0) = SOPLINE1
            SOPINV.taSopLineIvcInsert_Items = SOPLINE

            ReDim Preserve econnect.SOPTransactionType(0)
            econnect.SOPTransactionType(0) = SOPINV

            Dim fs As New FileStream(filename, FileMode.Create)
            Dim writer As New XmlTextWriter(fs, New UTF8Encoding)
            Dim serializer As New XmlSerializer(GetType(eConnectType))
            serializer.Serialize(writer, econnect)
            writer.Close()
        Catch ex As ApplicationException
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        eConnectSend()
    End Sub

End Class

Adapted from Microsoft Dynamics GP eConnect Samples installed during eConnect installation.

Line breaks are issued to fit script within screen. Remove line breaks.

THIS CODE AND INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE

Cheers!
Sanjay

e Connect example – Create Vendor with multiple addresses,email address and internet info

vendorcard

Imports System
Imports System.Xml
Imports System.Xml.Serialization
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic
Imports Microsoft.Dynamics.GP.eConnect
Imports Microsoft.Dynamics.GP.eConnect.Serialization

Public Class vENDORCREATE
    Public Sub eConnectSend()
        'Serialized XML File         
        Dim xmldocument As String
        'Connection String         
        Dim sConnectionString As String
        'Result         
        Dim xmlobject As String
        Using eConCall As New eConnectMethods
            Try
                SerializeObject("c:\test\xmlfile.xml ")
                Dim xmldoc As New Xml.XmlDocument
                xmldoc.Load("c:\test\xmlfile.xml")
                xmldocument = xmldoc.OuterXml
                sConnectionString = "data source=zfc-php;initial catalog=two;integrated security=SSPI; persist security info=False;packet size=4096"
                xmlobject = eConCall.CreateTransactionEntity(sConnectionString, xmldocument)
            Catch exp As eConnectException
                MsgBox(exp.ToString)
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            Finally
                eConCall.Dispose()
                MsgBox("done")
            End Try
        End Using
    End Sub
    Public Shared Sub SerializeObject(ByVal filename As String)
        Try
            Dim econnect As New eConnectType
            Dim vendor As New PMVendorMasterType
            'Vendor record
            Dim vendorrecord As New taUpdateCreateVendorRcd
            With vendorrecord
                .VENDORID = "VENDORID"
                .VENDNAME = "Name"
                .VADDCDPR = "PRIMARY"
                .VADCDSFR = "SHIPFROM"
                .VADCDTRO = "REMITTO"
                .VNDCNTCT = "PRIMARYCONTACT"
                .CreateAddress = 1
                .UpdateIfExists = 1
            End With
            vendor.taUpdateCreateVendorRcd = vendorrecord

            'Address - Primary
            Dim adressarray(2) As taCreateVendorAddress_ItemsTaCreateVendorAddress
            Dim addessobject1 As New taCreateVendorAddress_ItemsTaCreateVendorAddress
            With addessobject1
                .VENDORID = "VENDORID"
                .ADRSCODE = "PRIMARY"
                .ADDRESS1 = "Address 1"
                .ADDRESS2 = "Address 2"
                .ADDRESS3 = "Address 3"
                .VNDCNTCT = "PRIMARYCONTACT"
                .CITY = "City"
                .STATE = "CO"
                .ZIPCODE = "80526"
                .PHNUMBR1 = "2017070162"
                .PHNUMBR2 = "2017070162"
                .PHNUMBR3 = "2017070162"
                .FAXNUMBR = "2017070162"
                .CCode = "US"
                .COUNTRY = "USA"
                .CITY = "Fort Collins"
            End With
            adressarray(0) = addessobject1

            Dim addessobject2 As New taCreateVendorAddress_ItemsTaCreateVendorAddress
            With addessobject2
                .VENDORID = "VENDORID"
                .ADRSCODE = "REMITTO"
                .ADDRESS1 = "Address 1"
                .ADDRESS2 = "Address 2"
                .ADDRESS3 = "Address 3"
                .CITY = "City"
                .STATE = "CO"
                .ZIPCODE = "80526"
                .PHNUMBR1 = "2017070162"
                .PHNUMBR1 = "2017070162"
                .PHNUMBR2 = "2017070162"
                .PHNUMBR3 = "2017070162"
                .FAXNUMBR = "2017070162"
                .CCode = "US"
                .COUNTRY = "USA"
                .CITY = "Fort Collins"
                .VNDCNTCT = "REMITTOCONTACT"
            End With
            adressarray(1) = addessobject2

            Dim addessobject3 As New taCreateVendorAddress_ItemsTaCreateVendorAddress
            With addessobject3
                .VENDORID = "VENDORID"
                .ADRSCODE = "SHIPFROM"
                .ADDRESS1 = "Address 1"
                .ADDRESS2 = "Address 2"
                .ADDRESS3 = "Address 3"
                .CITY = "City"
                .STATE = "CO"
                .ZIPCODE = "80526"
                .PHNUMBR1 = "2017070162"
                .PHNUMBR1 = "2017070162"
                .PHNUMBR2 = "2017070162"
                .PHNUMBR3 = "2017070162"
                .FAXNUMBR = "2017070162"
                .CCode = "US"
                .COUNTRY = "USA"
                .CITY = "Fort Collins"
                .VNDCNTCT = "SHIPFROMCONTACT"
            End With

            adressarray(2) = addessobject3
            vendor.taCreateVendorAddress_Items = adressarray

            'Internet address
            Dim internetaddress(0) As taCreateInternetAddresses_ItemsTaCreateInternetAddresses
            Dim INERNETADDRESS1 As New taCreateInternetAddresses_ItemsTaCreateInternetAddresses
            With INERNETADDRESS1
                .ADRSCODE = "PRIMARY"
                .INET1 = "EMAIL"
                .Master_ID = "VENDOR"
                .Master_Type = "VEN"
            End With
            internetaddress(0) = INERNETADDRESS1
            vendor.taCreateInternetAddresses_Items = internetaddress

            ReDim Preserve econnect.PMVendorMasterType(0)
            econnect.PMVendorMasterType(0) = vendor

            Dim fs As New FileStream(filename, FileMode.Create)
            Dim writer As New XmlTextWriter(fs, New UTF8Encoding)
            Dim serializer As New XmlSerializer(GetType(eConnectType))
            serializer.Serialize(writer, econnect)
            writer.Close()
        Catch ex As ApplicationException
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        eConnectSend()
    End Sub
End Class

 

Element name Data type Length Required Description
CUSTNMBR string 15 Y Customer number
HOLD i4 1 N Hold status:
0=Not on hold;
1=On hold
Default is zero on new record
INACTIVE i4 1 N Inactive:
0=Active;
1=Inactive
Default is zero on new record
CUSTNAME string 64 N Customer name
SHRTNAME string 15 N Short name; if not passed in, default is CUSTNAME
STMTNAME string 64 N Statement name; if not passed in, default is CUSTNAME
CUSTCLAS string 15 N Customer class ID
CUSTPRIORITY i4 2 N Customer priority; valid input values 1 (none) to 100
Default is one on new record
ADRSCODE string 15 N Primary address ID
CNTCPRSN string 60 N Contact person
ADDRESS1 string 60 N Customer address one
ADDRESS2 string 60 N Customer address two
ADDRESS3 string 60 N Customer address three
CITY string 35 N City
STATE string 29 N State
ZIPCODE string 10 N Zip code
CCode string 6 N Country code
COUNTRY string 60 N Country
PHNUMBR1 string 21 N Phone one
PHNUMBR2 string 21 N Phone two
PHNUMBR3 string 21 N Phone three
FAX string 21 N Fax
UPSZONE string 3 N UPS zone
SHIPMTHD string 15 N Shipping method
TAXSCHID string 15 N Tax schedule ID
SHIPCOMPLETE i4 1 N Ship complete documents:
0=False;
1=True
Default is zero on new record
PRSTADCD string 15 N Primary ship to address code
PRBTADCD string 15 N Primary bill to address code
STADDRCD string 15 N Statement to address code
SLPRSNID string 15 N Salesperson ID
SALSTERR string 15 N Sales territory
USERDEF1 string 20 N User-defined one
USERDEF2 string 20 N User-defined two
COMMENT1 string 30 N Comment one
COMMENT2 string 30 N Comment two
CUSTDISC number 21 N Trade discount
PYMTRMID string 20 N Payment terms ID
DISGRPER i4 2 N Discount grace period
DUEGRPER i4 2 N Due date grace period
PRCLEVEL string 10 N Price level
NOTETEXT string 8000 N Note text
BALNCTYP i4 1 N Balance type:
0=Open item;
1=Balance forward
Default is zero on new record
FNCHATYP i4 2 N Finance charge type:
0=None;
1=Percent;
2=Amount
Default is zero for new record
FNCHPCNT number 21 N Finance charge percent; used if FNCHATYP=1
FINCHDLR number 21 N Finance charge dollar; used if FNCHATYP=2
MINPYTYP i4 2 N Minimum payment type:
0=No minimum;
1=Percent; 2=Amount
Default is zero for new record
MINPYPCT number 21 N Minimum payment percent; used if MINPYTYP=1
MINPYDLR number 21 N Minimum payment dollar amount; used if MINPYTYP=2
CRLMTTYP i4 2 N Credit limit type:
0=No credit;
1=Unlimited;
2=Amount
Default is zero on new record
CRLMTAMT number 21 N Credit limit amount; used if CRLMTTYP=2
CRLMTPER i4 2 N Credit limit period; used if CRLMTTYP=2 and the credit limit warning is used in Microsoft Dynamics GP application
CRLMTPAM number 21 N Credit limit period amount; used if CRLMTTYP=2 and the credit limit warning is used in Microsoft Dynamics GP application
MXWOFTYP i4 2 N Maximum write-off type: 0=Not allowed;
1=Unlimited;
2=Maximum
Default is zero for new record
MXWROFAM number 21 N Maximum write-off amount; used if MXWOFTYP=2
Revalue_Customer i4 1 N Revalue customer:
0=Do not revalue;
1=Revalue
Default is one for new record
Post_Results_To i4 2 N Post results to:
0=Receivables/Discount Account;
1=Sales offset
Default is zero on new records
ORDERFULFILLDEFAULT i4 1 N Order fulfillment shortage:
1=None;
2=Back order remaining;
3=Cancel remaining
Default is one on new record
INCLUDEINDP i4 1 N Include in demand planning: 0=Do not include;
1=Include
Default is one on new record
CRCARDID string 15 N Credit card ID
CRCRDNUM string 20 N Credit card number
CCRDXPDT datetime 16 N Credit card expiration date
BANKNAME string 30 N Bank name
BNKBRNCH string 20 N Bank branch
USERLANG i4 2 N User language; default is zero on new record
TAXEXMT1 string 25 N Tax exempt one
TAXEXMT2 string 25 N Tax exempt two
TXRGNNUM string 25 N Tax registration number
CURNCYID string 15 N Currency ID
RATETPID string 15 N Rate type ID
STMTCYCL i4 2 N Statement Cycle:
1=No Statement;
2=Weekly;
3=Biweekly;
4=Semimonthly;
5=Monthly;
6=Bimonthly;
7=Quarterly
KPCALHST i4 1 N Maintain history—calendar year:
0=Do not maintain history;
1=Maintain history
Default is 1 on new record
KPERHIST i4 1 N Maintain history—fiscal year:
0=Do not maintain history;
1=Maintain history
Default is 1 on new record
KPTRXHST i4 1 N Maintain history—transaction:
0=Do not maintain history;
1=Maintain history
Default is 1 on new record
KPDSTHST i4 1 N Maintain history—distribution:
0=Do not maintain history;
1=Maintain history
Default is 1 on new record
Send_Email_Statements i4 1 N Send e-mail statements:
0=Do not sent statements;
1=Send statements
Default is zero on new record
ToEmail_Recipient string 80 N To email recipient; separate each recipient with a semicolon
CcEmail_Recipient string 80 N Carbon copy email recipient; separate each recipient with a semicolon
BccEmail_Recipient string 80 N Blind carbon copy email recipient; separate each recipient with a semicolon
CHEKBKID string 15 N Checkbook ID
DEFCACTY i4 2 N Cash account from: 0=Checkbook;
1=Customer
Default is zero on new record
RMCSHACTNUMST string 75 N Cash account; only valid if DEFCACTY=1
RMARACTNUMST string 75 N Accounts receivable account
RMSLSACTNUMST string 75 N Sales account
RMCOSACTNUMST string 75 N Cost of sales account
RMIVACTNUMST string 75 N Inventory account
RMTAKACTNUMST string 75 N Terms discount taken account
RMAVACTNUMST string 75 N Terms discount available account
RMFCGACTNUMST string 75 N Finance charges account
RMWRACTNUMST string 75 N Write-offs account
RMSORACTNUMST string 75 N Sales order returns account
RMOvrpymtWrtoffACTNUMST string 75 N Overpayment write-offs account
GPSFOINTEGRATIONID string 30 N Front office integration ID
INTEGRATIONSOURCE i4 2 N Integration source
INTEGRATIONID string 30 N Integration ID
UseCustomerClass i4 1 N Flag to have class setting roll down to elements not passed in; uses the CUSTCLAS class to roll down
CreateAddress i4 1 N Flag to allow a primary address code to be created on the fly:
0=Do not create address code;
1=Create address code
UpdateIfExists i4 1 N Flag to allow customer data to be updated if it exists:
0=Do not update;
1=Update if record exists;
2=Create if record does not exist and exits without error if record does exist
RequesterTrx i4 2 N Requester transaction:
0=False;
1=True (if True, it populates the requester shadow table)

Cheers!

Sanjay

Execute e Connect stored procedure directly Example

In this example how to execute e Connect stored procedure taCreateEmployee is demonstrated.

The first step in executing the e Connect stored procedure would be to identify the parameters the stored procedure  will accept and the give out as output.  The easiest way to identify the parameters the stored procedure will accept and output is to right click the stored procedure at SSMS and execute it. execute sp

SQL Server will prompt with all parameters the stored procedure will accept or output and also provide information on  data type, size, and direction.sp params

The other option is to decrypt the stored procedure and modify the stored procedure to identify the parameters.

Once the parameters are identified, execute the stored procedures as follows:

DECLARE  @_iErrorState int    
DECLARE @_ErrString varchar(255) 
exec [dbo].[taCreateEmployee] 
@I_vEMPLOYID ="2050",
@I_vEMPLCLAS ="CLASS",       -- Class should exist in GP
@I_vLASTNAME ="Kumar",
@I_vFRSTNAME ="Sanjay",
@I_vSOCSCNUM ="000000003",
@I_vDEPRTMNT = "IT",         -- Dept should exist in GP       
@I_vJOBTITLE = "ERPADMN",    -- Title should exist in GP
@I_vUpdateIfExists = 1,
@O_iErrorState = @_iErrorState OUTPUT,
@oErrString = @_ErrString OUTPUT
SELECT  @_iErrorState as errstat , @_ErrString as errstring  --  0 errstat means all ok else errstring will give error #

In above example only selective fields which were required to send is used.

Cheers!
Sanjay