Archives

 

When installing product in Dynamics GP, the common procedure is to drop a .cnk (chunk) file into the Dynamics directory and then launch Dynamics. Generally, that's all there is to it. The app grabs and imports the .cnk file and we're done.

New code must be included in the :C:Program Files (x86)/Microsoft Dynamics/GP XXXX/Dynamics.set dictionary. Do you wish to include new code now?

Occasionally, the process does not work. This thread will walk you through several troubleshooting techniques that will help to resolve this.

This article will show how to default the 'expansion' button on a grid in a Dynamics GP 2015

This will simulate pushing this button  every time the form opens

The approach is slightly different in earlier versions but the technique is the same

What do you do when the remember user and password box is grayed out?

 

This article discusses the error below:

 

Violation of PRIMARY KEY constraint 'PK_eConnect_Out_temp'. Cannot insert duplicate key in object 'dbo.eConnectOutTemp'. The duplicate key value is (Sales_Transaction, INV00623393, 3, , , , , , , , , , , , , ).

The statement has been terminated.

 


 

Can anyone provide me some sample  VBA code to default the hide/show button to show on scrolling windows in Dynamics GP?

Thanks

Debbie Knoebl

 

Is there ANY WAY to get the date someone actually applied a payment or credit memo to an invoice in GP?

 I have a client who is looking at RM20201 and needs to know WHEN it was actually applied, not the end user's desired apply date.

There is no created date for an apply record - or is there?

I'm not speaking about the created date of the payment or credit.... but the created date of the APPLY record in RM20201 (if it exists).

If it doesn't exists, how can I get it?

 Thanks!

Mark

What is the best method to Update PO using web-services in GP2013 SP3?

Thank you!

Donnette

First off let me say I am new to VB and my knowledge is rudimentary. Furthermore I am trying to pick up fixing someone else's code that I did not write. I apologize in advance for anything I do or say in this question that is frustrating or incomplete - it's not intentional and I'm happy to correct whatever faux pas I may be committing!

 I have a question related to the code below:

case when CONVERT(date, (e3UsageFrom.DATE1)) = '1900-01-01'then ''

ELSE convert(varchar(10),(e3UsageFrom.DATE1),101) +''+CONVERT (CHAR(8),(e3UsageFrom.DATE1), 120)

END as UsageFrom,

The full code (which I've included below) is a stored procedure we that pulls Extender data onto a GP report writer report (among other things).

I am not receiving any errors, the code runs fine. However, when the Extender date fields print they print like this: DD/MM/YYYYYYY (for example 02/08/2016201) - in other words, the year data repeats.

 I am guessing it has to do with the varchar data type and/or the number values, but I don't know what those indicate in the first place, so it's hard to know what to change. If someone could help me dissect that part of the code, I could test it and figure it out -

What do the numbers 10, 101 and 120 indicate? What is the significance of "convert" and "CHAR"? Why is the data type varchar instead of date? Would formatting these fields as a date solve my problem?

 

The full code for the stored procedure is below.

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE proc [dbo].[dd_PA01201_SEL]

-- dd_PA01201_SEL 'sa'

@UserName varchar(30)

AS

select PAprojid, PABEndDate, pm.PAPROJNUMBER, e2.Extender_Record_ID, pm.CUSTNMBR,

isnull(e1Client.STRGA255,'') as Client,

isnull(e1Union.STRGA255,'') as UnionName,

pm.PAprjclsid, pm.PAprojname,

isnull(e3UsageFrom.DATE1,'') ,

case when CONVERT(date, (e3UsageFrom.DATE1)) = '1900-01-01'then ''

ELSE convert(varchar(10),(e3UsageFrom.DATE1),101) +''+CONVERT (CHAR(8),(e3UsageFrom.DATE1), 120)

END as UsageFrom,

isnull(e3UsageTo.DATE1,'') ,

case when CONVERT(date, (e3UsageTo.DATE1)) = '1900-01-01'then ''

ELSE convert(varchar(10),(e3UsageTo.DATE1),101) +''+CONVERT (CHAR(8),(e3UsageTo.DATE1), 120)

END as UsageTo,

isnull(e3HoldFrom.DATE1,'') ,

case when CONVERT(date, (e3HoldFrom.DATE1)) = '1900-01-01'then ''

ELSE convert(varchar(10),(e3HoldFrom.DATE1),101) +''+CONVERT (CHAR(8),(e3HoldFrom.DATE1), 120)

END as HoldFrom,

isnull(e3HoldTo.DATE1,''),

case when CONVERT(date, (e3HoldTo.DATE1)) = '1900-01-01'then ''

ELSE convert(varchar(10),(e3HoldTo.DATE1),101) +''+CONVERT (CHAR(8),(e3HoldTo.DATE1), 120)

END as HoldTo,

isnull(e1ProductionCo.STRGA255, '') as ProductionCo,

isnull(e2Signatory.STRGA255,'') as Signatory,

isnull(ub.DateRange, '') as DateRange,

isnull(ub.PACOSTCATNME,'') as PACOSTCATNME,

isnull(ub.EMPLOYID,'') as EMPLOYID,

ISNULL(ub.d1, '') as d1,

ISNULL(ub.d2, '') as d2,

100 * isnull(ub.PAQtyQ,0) as PAQtyQ

from UPR40200EXT ux

join PA01201 pm on pm.PAPROJNUMBER = ux.projectnumber --Project Master

left join EXT01100 e0 on e0.Extender_Window_ID = 'PROJECT_DETAILS' and e0.Extender_Key_Values_1 = pm.PAprojid --Extender Header

left join EXT01100 e2 on e2.Extender_Window_ID = 'PROJ_SIGNATORY' and e2.Extender_Key_Values_1 = pm.CUSTNMBR --Extender Header

left join EXT01101 e1Client on e1Client.Extender_Record_ID = e0.Extender_Record_ID and e1Client.Field_ID = 1290 --Extender client line

left join EXT01101 e1Union on e1Union.Extender_Record_ID = e0.Extender_Record_ID and e1Union.Field_ID = 1299 --Extender union line

left join EXT01102 e3UsageFrom on e3UsageFrom.Extender_Record_ID = e0.Extender_Record_ID and e3UsageFrom.Field_ID = 1269 --Extender usage from line

left join EXT01102 e3UsageTo on e3UsageTo.Extender_Record_ID = e0.Extender_Record_ID and e3UsageTo.Field_ID = 1270 --Extender usage to line

left join EXT01102 e3HoldFrom on e3HoldFrom.Extender_Record_ID = e0.Extender_Record_ID and e3HoldFrom.Field_ID = 1272 --Extender hold from line

left join EXT01102 e3HoldTo on e3HoldTo.Extender_Record_ID = e0.Extender_Record_ID and e3HoldTo.Field_ID = 1274 --Extender hold to line

left join EXT01101 e1ProductionCo on e1ProductionCo.Extender_Record_ID = e0.Extender_Record_ID and e1ProductionCo.Field_ID = 1290 --Extender ProductionCo line

left join EXT01101 e2Signatory on e2Signatory.Extender_Record_ID = e2.Extender_Record_ID and e2Signatory.Field_ID = 1296 --Extender signatory line

--THIS CAUSES THE STORED PROC TO RETURN ONE LINE FOR EACH PAYCODE

left join (

SELECT

tsd.PACOSTCATID

,cc.PACOSTCATNME

,tsh.EMPLOYID

,TSD.PAQtyQ

,convert(varchar(10),MAX(padt),101) + ' - ' + convert(varchar(10),MAX(PAexptdate),101) DateRange

,MAX(tsd.padt)d1

,case when CONVERT(date, MAX(tsd.padt)) = '1900-01-01'then ''

ELSE convert(varchar(10),MAX(tsd.PADT),101) +''+CONVERT (CHAR(8),MAX(tsd.PADT), 120)

END d3

,MAX(tsd.PAexptdate)d2

,case when CONVERT (date, MAX(tsd.PAexptdate)) = '1900-01-01' then ''

ELSE convert(varchar(10), MAX(tsd.PAexptdate),101)+''+CONVERT (CHAR(8),MAX(tsd.paexptdate), 120)

END d4

from UPR10301 ub

join PA30100 tsh on tsh.BACHNUMB = ub.BACHNUMB

join PA30101 tsd on tsd.PATSNO = tsh.PATSNO

join PA01001 cc on cc.PACOSTCATID = tsd.PACOSTCATID

where ub.MKDBYUSR = @UserName

and ub.UPRBCHMK = 1

group by ub.BACHNUMB,tsd.PACOSTCATID,cc.PACOSTCATNME, tsh.EMPLOYID,TSD.PAQtyQ,PADT,PAexptdate

) ub on 1=1

--select * from EXT01100 where Extender_Window_ID = 'PROJ_SIGNATORY ' and Extender_Key_Values_1 = 'anot0001'

--select * from EXT01101 where Extender_Record_ID = 5194

--select * from pa01201

-- update UPR40200EXT set ProjectNumber = 'ALLI2C40094 '

 

 

GO

I was given a request by our finance department to find all available GL account numbers filtering by segment two.

I naturally looked at GL40200 to see what was available by segment, however our finance department routinely activates and deactivates posting accounts.

So what I found was that not " used " were actually accounts that had descriptions and were simply marked as inactive, so these were not what I was looking for. I am trying to fulfill this request to assist our entire finance team in a massive way I am told they will need to know over 7400 accounts to be setup for internal reporting to our parent company.

 

I am honestly not sure how this would be possible without some sort of logic to locate all available accounts that have never been setup, I am looking for all available second segments in all nine of our companies. But not sure how to query something that has no record yet created.

 

Example

10100-25000-05679 Cash discount Active

10100-25002-05679 Cash rebate Inactive

10100-25001-05679 " this should be available for usage" this is the type of info I am trying to determine, I would expect a massive list of potential   account numbers.

 

Hello Everyone,

 I was wondering if its possible to house two different versions of GP in the same SQL instance.  My company is currently running GP2010 and we have acquired another company that is running GP2013.  I have the backups from our acquired company and was wondering if I can house these databases on the same SQL 2008 R2 server as my GP2010 databases or do I need to setup a whole new server with a fresh installation of SQL installed.  This may be a dumb question but couldn't seem to find anything for research purposes.

 

Thanks in advance.

Does Microsoft Dynamics GP 2015 have the ability to produce an NSF Letter that can be mailed to the customer?   I know you can process an NSF Charge, but we've never done letters up till now.

If so, anyone want to share briefly where I go to set these up?
SOPTransactionType example

Hello,

 

We currently have a PHP Webapp + Dynamics GP integration. Somethings we did it with SOAP Webservices but other we did using econnect stored procedures

 

We have a legacy version of SOAP WSDL and We have experienced problems with Sales Documents. We have no document creation errors but the money amounts always are 0.

 

 

We want to use the Econnect Stored Procedures to create our invoices on GP. Please could you point me the calling order for this Stored Procedures and/or examples about this?

 

thanks in advance

Me again!  I have an issue where my eConnect code is not updating the receivings Tax Summary correctly.  I have attached some images of what the client is expecting, and what my submission is creating

 

|And here is the XML that I submitted.  I am almost there, I am just overlooking something!

 

 

Within this images.zip file, image013.jpg is what they are expecting.  The problem I am getting is that  the TAX Id window has not populated the Gross figure and the distribution has produced an unbalanced document.  Image007 & 008 are what my code are currently producing

 

So I need to figure out what to add to my code in order to get these populated.

 

<?xml version="1.0" encoding="utf-8"?>
  <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>3</POPTYPE>
        <POPRCTNM>R000065</POPRCTNM>
        <ITEMNMBR>Laynards x 30</ITEMNMBR>
        <ITEMDESC>Laynards x 30</ITEMDESC>
        <VENDORID>ARJ001</VENDORID>
        <RCPTLNNM>1</RCPTLNNM>
        <InventoryAccount>9998-00</InventoryAccount>
        <UOFM>Each</UOFM>
        <UNITCOST>43.20</UNITCOST>
        <EXTDCOST>43.20</EXTDCOST>
        <NONINVEN>1</NONINVEN>
        <QTYSHPPD>1</QTYSHPPD>
        <QTYINVCD>1</QTYINVCD>
        <Purchase_IV_Item_Taxable>3</Purchase_IV_Item_Taxable>
        <Purchase_Site_Tax_Schedu>PSTD</Purchase_Site_Tax_Schedu>
        <LOCNCODE>NO PROJECT</LOCNCODE>
        <receiptdate>01/02/2016</receiptdate>
        <CURNCYID>GBP</CURNCYID>
        <ProjNum>DEVLIV1SC</ProjNum>
        <CostCatID>SC04</CostCatID>
      </taPopRcptLineInsert>
    </taPopRcptLineInsert_Items>
    <taPopRcptMultiBin_Items xsi:nil="true" />
    <taPopRcptLineTaxInsert_Items>
      <taPopRcptLineTaxInsert>
        <VENDORID>ARJ001</VENDORID>
        <POPRCTNM>R000065</POPRCTNM>
        <TAXDTLID>PSTD</TAXDTLID>
        <TAXTYPE>0</TAXTYPE>
        <ACTNUMST>1226-00</ACTNUMST>
        <TAXAMNT>8.64</TAXAMNT>
        <RCPTLNNM>1</RCPTLNNM>
      </taPopRcptLineTaxInsert>
    </taPopRcptLineTaxInsert_Items>
    <taPopRctUserDefined xsi:nil="true" />
    <taPopDistribution_Items xsi:nil="true" />
    <taAnalyticsDistribution_Items xsi:nil="true" />
    <taPopRcptHdrInsert>
      <POPRCTNM>R000065</POPRCTNM>
      <POPTYPE>3</POPTYPE>
      <VNDDOCNM>27300</VNDDOCNM>
      <receiptdate>01/02/2016</receiptdate>
      <BACHNUMB>16022016025926</BACHNUMB>
      <VENDORID>ARJ001</VENDORID>
      <TAXAMNT>8.64</TAXAMNT>
      <AUTOCOST>1</AUTOCOST>
      <TAXSCHID>PSTD</TAXSCHID>
      <USINGHEADERLEVELTAXES>2</USINGHEADERLEVELTAXES>
      <CURNCYID>GBP</CURNCYID>
    </taPopRcptHdrInsert>
    <taMdaUpdate_Items xsi:nil="true" />
  </POPReceivingsType>
</eConnect>

 

POPReceivingsType oPOPReceivingsType = new POPReceivingsType();
                taPopRcptHdrInsert invoiceHeader = new taPopRcptHdrInsert();
                List<taPopRcptLineInsert_ItemsTaPopRcptLineInsert> ecInvoiceLines = new List<taPopRcptLineInsert_ItemsTaPopRcptLineInsert>();
                GetNextDocNumbers transNumber = new GetNextDocNumbers();
 
                 
                Int16 lineNumber = 1, lineIndex = 0;
                String xmlFilename = "XML\\posted" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xml";
                Decimal totalTax = 0;
 
                //Get Business Unit/Supplier Details
                c2sBUnit.BU_ID = Convert.ToInt32(invoice.BU_ID);
                c2sSupplier.Supplier_ID = invoice.Supplier_ID;
 
                c2sBUnit.Fetch();
                c2sSupplier.Fetch();
 
 
 
                //build connection string to dynamics database
                dynamicsConnStr = ConfigurationManager.ConnectionStrings["eConnectDB"].ConnectionString.Replace("{DBNAME}", c2sBUnit.BU_DBName);
                Console.WriteLine(dynamicsConnStr);
 
                invoice.InvoiceNumber = transNumber.GetNextPOPReceiptNumber(IncrementDecrement.Increment, dynamicsConnStr);
                String batchNumberPlaceHolder = DateTime.Now.ToString("ddMMyyyyhhmmss");
                String vendorReference = c2sSupplier.Supplier_Acct.Substring(c2sSupplier.Supplier_Acct.IndexOf('-') + 1);
 
 
                Console.WriteLine(invoice.SupplierDocumentNumber);
 
 
                //Invoice Header Details
                invoiceHeader.POPRCTNM = invoice.InvoiceNumber;    //receipt number placeholder
                invoiceHeader.POPTYPE = 3;                                  //Shipping/invoice
                invoiceHeader.VNDDOCNM = invoice.SupplierDocumentNumber;             //invoice number
                invoiceHeader.CURNCYID = "GBP";                             //currency defaults to GBP
                invoiceHeader.TAXSCHID = invoice.TaxSchedule;               //Tax Code
                invoiceHeader.receiptdate = invoice.InvoiceDate.ToString("dd/MM/yyyy"); // invoice.InvoiceDate.ToString("dd/MM/yyyy");
                invoiceHeader.USINGHEADERLEVELTAXES = 2;
                invoiceHeader.AUTOCOST = 1;
                invoiceHeader.BACHNUMB = batchNumberPlaceHolder;
                invoiceHeader.VENDORID = vendorReference;
                //Invoice lines
                taPopRcptLineTaxInsert_ItemsTaPopRcptLineTaxInsert[] invoiceLineTax = new taPopRcptLineTaxInsert_ItemsTaPopRcptLineTaxInsert[invoice.InvoiceLines.Count];
 
                oPOPReceivingsType.taPopRcptLineInsert_Items = new taPopRcptLineInsert_ItemsTaPopRcptLineInsert[invoice.InvoiceLines.Count];
                Decimal totalInvoiceTax = 0;
 
                foreach (OutgoingInvoiceLine line in invoice.InvoiceLines)
                {
                    taPopRcptLineTaxInsert_ItemsTaPopRcptLineTaxInsert invoiceTax = new taPopRcptLineTaxInsert_ItemsTaPopRcptLineTaxInsert();
                    taPopRcptLineInsert_ItemsTaPopRcptLineInsert ecLine = new taPopRcptLineInsert_ItemsTaPopRcptLineInsert();
                    NominalIDs c2sNominal = new NominalIDs(ConfigurationManager.ConnectionStrings["DestinationDB"].ConnectionString);
 
                    ecLine.POPRCTNM = invoice.SupplierDocumentNumber;
                    ecLine.VENDORID = vendorReference;
                    ecLine.CURNCYID = "GBP";
                    ecLine.ITEMNMBR = line.ItemDescription;
                    ecLine.ITEMDESC = line.ItemDescription;
                    ecLine.UOFM = "Each";
                    ecLine.QTYINVCD = line.Quantity;
                    ecLine.QTYSHPPD = line.Quantity;
                    ecLine.UNITCOST = Decimal.Parse(line.UnitCost.ToString());
                    ecLine.EXTDCOST = Decimal.Parse(Convert.ToString(line.UnitCost * line.Quantity));
                    ecLine.Purchase_IV_Item_Taxable = 3;
                    ecLine.EXTDCOSTSpecified = true;
                    ecLine.UNITCOSTSpecified = true;
                    ecLine.NONINVEN = 1;
                    ecLine.receiptdate = "01/02/2016";
                     
                    ecLine.RCPTLNNM = lineNumber;
                    ecLine.POPTYPE = 3;
                    //ecLine.INVINDX = 315;
                    ecLine.InventoryAccount = "9998-00"; //line.NominalCode;
                    ecLine.ProjNum = line.ProjectNumber;
                    ecLine.CostCatID = line.CostCategory;
                    ecLine.LOCNCODE = "NO PROJECT"//default Location Code
                     
 
                    ecLine.Purchase_Site_Tax_Schedu = line.TaxCode;
                     
 
                    oPOPReceivingsType.taPopRcptLineInsert_Items[lineIndex] = ecLine;
 
                    totalTax = line.TaxCost;
 
                    //Invoice Tax
                    c2sNominal.Nominal_BUID = c2sBUnit.BU_ID;
                    c2sNominal.Nominal_Code = line.NominalCode;
                    c2sNominal.Fetch();
 
 
                    invoiceTax.TAXTYPE = 0;
                    invoiceTax.POPRCTNM = invoice.SupplierDocumentNumber;
                    invoiceTax.VENDORID = vendorReference;
                    invoiceTax.TAXDTLID = invoice.TaxDetail;
                    invoiceTax.TAXAMNT = totalTax;
                    invoiceTax.MSCTXAMT = Decimal.Parse("0.00");
                    invoiceTax.FRTTXAMT = Decimal.Parse("0.00");
                     
                    //invoiceTax.ACTINDX = c2sNominal.ACCTINDX;
                    invoiceTax.ACTNUMST = "1226-00";//line.NominalCode;
                    invoiceTax.RCPTLNNM = lineNumber;
                     
                    invoiceLineTax[lineIndex] = invoiceTax;
                    totalInvoiceTax += totalTax;
 
                    lineIndex++;
                    lineNumber++;
                }
 
                invoiceHeader.TAXAMNT = totalInvoiceTax;
                oPOPReceivingsType.taPopRcptHdrInsert = invoiceHeader;
 
                oPOPReceivingsType.taPopRcptLineTaxInsert_Items = invoiceLineTax;
 
                // Create an eConnect XML document object and populate it
                // with the POPReceivingsType schema object
                eConnectType eConnect = new eConnectType();
 
                eConnect.POPReceivingsType = new POPReceivingsType[1];
                eConnect.POPReceivingsType[0] = oPOPReceivingsType;
                // Create a file to hold the serialized eConnect XML document
                FileStream fs = new FileStream(xmlFilename, FileMode.Create);
                XmlTextWriter writer = new XmlTextWriter(fs, new UTF8Encoding());
 
                // Serialize the eConnect document object to the file using the XmlTextWriter.
                XmlSerializer serializer = new XmlSerializer(eConnect.GetType());
                serializer.Serialize(writer, eConnect);
                writer.Close();
 
                XmlDocument xmldoc = new XmlDocument();
                xmldoc.Load(xmlFilename);
 
                //// Create an XML string from the document object
                invoice.GeneratedXML = xmldoc.OuterXml;

Hi

 Is there any example code on how to retrieve a PO from webservices and get it into an econnect formatted xml file using any .net flavor? I am a GP admin that is working with a third party guy that is trying to get this info and I am not a developer , I have sent him all the examples and guides and he still doesn't get it. He is getting the info except for the line item comments and I am thinking it needs to be formatted with the econnect schema. I could be totally wrong  lol as I said I am not a .net guy just an SQL dude

 

Thanks

Vic

Hi, I have a proc that calls the [taPopRcptHdrInsert] to create full Shipment/Invoice transactions in GP10.

On some transactions it doesn't insert the header (in my larger script), but if run just that proc with the desired variables in Query Analyzer, it creates the record.

Any ideas on how to troubleshoot this? No errors and I'm calling the scripts from SQL (no XML or .NET).  The procs report success in all situations but sometimes no header record for my POP receivings invoice appears unless I then go manually run the script by itself.  

 

 

Hi 

I have a vendor trying to access PO's via web services and create an xml file but the PO comments are not coming through in his data (see the screen shot)

 Is there something else we should be calling or anything we are missing??

Thanks Vic 

 

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