DynDeveloper.com

DynDeveloper.com
Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now! Log In
Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Archives

 
Integration Manager - set the EFT flag in a Cash Receipt after document script
This short article shows how to set the RM10201.EFTFLAG field in an RM Cash Receipt integration. Place the code below in the After Document script
.NET Development - Calling a subroutine asynchronously and waiting for the response

Calling a subroutine asynchronously and waiting for the response

I routinely need to call a stored procedure and wait 30 seconds for the response. The user is left wondering if there's anyone home. I can't open a 'please wait' dialog because the interface is frozen. Enter async.

Async calls have been around for a while, I see articles going back to 2009 and earlier, .NET 4.5 really brags about its multi-threading capabilities.

My needs are simpler. This article (written against .NET 3.5) is a short, simple block of code that show how to call a subroutine asynchronously and wait for the result.

In this example we have a second form called 'pleaseWait', it pops up and then disappears when the function returns

 

 
Sample XML for the SopTransactionType using taSopUserDefined
This article contains sample XML for the eConnect SopTransactionType showing the usage of taSopUserDefined
.NET Development - Calling a subroutine asynchronously and waiting for the response, part II

A couple of days ago, I wrote an article that showed how to run code asynchronously.

http://dyndeveloper.com/articleview.aspx?ArticleID=255

That worked as advertised, but I've since gotten the requirement to report to the user on the progress. That method won't work so well, so I developed some code using the BackgroundWorker class that does the trick.

Here's the requirement: Write a Windows form that calls a class, the class does some work that takes a while (in this case, integrating files into Dynamics). Report back to the main form periodically and allow the user to see the progress.

In our example we use a list box to report, because it's quick and easy.

 

 
SQL - Script to display the contents of one line in a table VERTICALLY

A client has asked me to write an SSRS report that will show the value of all the fields in a table. This particular table has 140 fields, that means that I would have to place a grid on the form that would have 140 columns (imagine if you tried to print that) or I'd have to place 280 text boxes on the form and format the whole think pretty. That would take hours.

I've written a script that fixes that issue.

A picture is better than a thousand words, right?

We have a single line in a table, it looks like this (same as any other table). You'll have to imagine the table extending to the right for 140 fields.

This script will 'pivot' the table and make it look like this:

I have the output coming in two sets of columns (columnName, Value, ColumnName, Value, RowID) instead of just one; but the script can be easily altered to do this for any number of columnName, Value pairs.

 
Need help with a trigger

Sirs: I need a GP trigger as follows:

 When a record is inserted into PM20000

  • If the document type =  Invoice and the payment priority on the vendor master (PM00100) = PWP
  • Then update HOLD on PM20000 to a 1

 Can anyone help?
error in lot

i have and error with a LOT i can't eraser, enter or modifity. this error appears :  This lot is this accounting
No unused Payables vouchers can be found.

When posting a batch in Payables in GP, we are getting ...

*error: No unused Payables vouchers can be found.

This is happened on both batches I have attempted to post.

I am not sure if it is a duplicate voucher number issue or what.
sql script to assign facilities and departments for a new installation of Binary stream Multi Entity Management
Has anyone created a sql script to assign facilities and departments for a new installation of Binary stream Multi Entity Management? This would replace the Assign Facility routine .
Quick SQL question

Quick question what does it mean when it says tsl? I can’t find it as a view, table, stored proc nothing!

 

 

update tsl
set                          tsl.PABILRATE = erl.PAProfitAmount
                                ,tsl.PAORIGBILLRATE = erl.PAProfitAmount
                                ,tsl.PA_Base_Billing_Rate = erl.PAProfitAmount
                                ,tsl.PAORIGBSBILLRTE = erl.PAProfitAmount
                                ,tsl.PATotalProfit = tsl.PAQtyQ * erl.paprofitamount
                                ,tsl.PAORIGTOTPROF = tsl.PAQtyQ * erl.paprofitamount
                                ,tsl.PAACREV = tsl.PAQtyQ * erl.paprofitamount
                                ,tsl.PAORIACCRREV = tsl.PAQtyQ * erl.paprofitamount
from PA30101 tsl

 
PADepartment field

where does this field come from?  in the table reference, it just says "char(7)"  ('ding' to any old apple II programmers out there)

 

but seriously, I need to get the location (dept) for the timesheet detail history records.  thanks!

 

george
inserting timesheet lines

Edit 9/17/15 View the main article for this error here 

hi all,

I have econnect setup in a winform application, and am processing timesheets (again--this is a re-write).   I want to insert timesheet lines separately, one at a time for stability.  I came across a problem where my timesheet would error out if certain conditions are met (not good).

I figure I will just insert a header, then individually insert the timesheet lines to get around our customizations, and I have gotten the header to insert, but am having trouble inserting lines through eConnect.  Is this possible, or do you have to have a header to put a line in econnect?

second, I tried manually inserting the timesheet line with the taPATimeSheetLineInsert stored procedure, but I am getting an error 6451 back.  Where can I look up these codes?

thanks for your assistance!
taPopRcptLineInsert_ItemsTaPopRcptLineInsert Help Inserting .... any direction would be helpfull

eConnectType eConnect = new eConnectType();

// Instantiate a IVItemMasterType schema object
POPReceivingsType popRec = new POPReceivingsType();
taPopRcptLineInsert_ItemsTaPopRcptLineInsert newtaPopRcptLineInsert_ItemsTaPopRcptLineInsert = new taPopRcptLineInsert_ItemsTaPopRcptLineInsert()
{ 
    ITEMDESC = popRctLineInsert.Description, 
    ITEMNMBR = popRctLineInsert.Item, 
    LOCNCODE = popRctLineInsert.Location, 
    POLNENUM = int.Parse(popRctLineInsert.SequenceNum), 
    PONUMBER = popRctLineInsert.VendorItem, 
    QTYSHPPD = decimal.Parse(popRctLineInsert.ReceivedAmount), 
    VENDORID = popRctLineInsert.VendorItem, 
    POPTYPE = 3, 
    BOLPRONUMBER= "<![CDATA[ ]]>", 
    CMMTTEXT ="<![CDATA[ ]]>", 
    CURNCYID ="<![CDATA[ ]]>", 
    CostCatID="<![CDATA[ ]]>", 
    InventoryAccount = "<![CDATA[ ]]>", 
    JOBNUMBR ="<![CDATA[ ]]>", 
    Landed_Cost_Group_ID ="<![CDATA[ ]]>", 
    POPRCTNM ="<![CDATA[ ]]>", 
    ProjNum = "<![CDATA[ ]]>", 
    Purchase_Item_Tax_Schedu = "<![CDATA[ ]]>", 
    Purchase_Site_Tax_Schedu = "<![CDATA[ ]]>", 
    receiptdate = DateTime.Now.ToShortDateString(), 
    UOFM = "<![CDATA[ ]]>", 
    USRDEFND1 = "<![CDATA[ ]]>", 
    USRDEFND2 = "<![CDATA[ ]]>", 
    USRDEFND3 = "<![CDATA[ ]]>", 
    USRDEFND4 = "<![CDATA[ ]]>", 
    USRDEFND5 = "<![CDATA[ ]]>", 
    VNDITDSC = "<![CDATA[ ]]>", 
    VNDITNUM = "<![CDATA[ ]]>"
};

popRec.taPopRcptLineInsert_Items = new taPopRcptLineInsert_ItemsTaPopRcptLineInsert[1] { newtaPopRcptLineInsert_ItemsTaPopRcptLineInsert };

using (GenericEconnectHelper m_geh = new GenericEconnectHelper())
{
switch (companyType)
{
case EGenConType.Kretek_International:
m_geh.ConnectionState = EGenConType.Kretek_International;
break;
// default:
//return new List<object>() { new { Error = "gpCompanyId Not recognized" } };
}

eConnect.POPReceivingsType = new POPReceivingsType[] { popRec };
string genericEconnectHelperGenericSerializeAnObject = GenericEconnectHelper.GenericSerializeAnObject(eConnect);

bool buff = this.CreateEntity(m_ConnectionString, genericEconnectHelperGenericSerializeAnObject);

//bool m_gehGenericCreateEntity = m_geh.GenericCreateEntity(genericEconnectHelperGenericSerializeAnObject);

 
HERE IS MY XML (WHICH IS VALID) Keep getting the following error "
"The Creator of this fault did not specify a reason"

the state of eConnectClient is open...


<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>3</POPTYPE>
<POPRCTNM><![CDATA[ ]]></POPRCTNM>
<PONUMBER>TEST</PONUMBER>
<ITEMNMBR>TEST</ITEMNMBR>
<ITEMDESC>Added using Check Links</ITEMDESC>
<VENDORID>TEST</VENDORID>
<VNDITNUM><![CDATA[ ]]></VNDITNUM>
<VNDITDSC><![CDATA[ ]]></VNDITDSC>
<InventoryAccount><![CDATA[ ]]></InventoryAccount>
<UOFM><![CDATA[ ]]></UOFM>
<JOBNUMBR><![CDATA[ ]]></JOBNUMBR>
<BOLPRONUMBER><![CDATA[ ]]></BOLPRONUMBER>
<QTYSHPPD>10</QTYSHPPD>
<Purchase_Item_Tax_Schedu><![CDATA[ ]]></Purchase_Item_Tax_Schedu>
<Purchase_Site_Tax_Schedu><![CDATA[ ]]></Purchase_Site_Tax_Schedu>
<Landed_Cost_Group_ID><![CDATA[ ]]></Landed_Cost_Group_ID>
<LOCNCODE>MAIN00</LOCNCODE>
<POLNENUM>16834</POLNENUM>
<receiptdate>8/23/2013</receiptdate>
<CURNCYID><![CDATA[ ]]></CURNCYID>
<ProjNum><![CDATA[ ]]></ProjNum>
<CostCatID><![CDATA[ ]]></CostCatID>
<CMMTTEXT><![CDATA[ ]]></CMMTTEXT>
<USRDEFND1><![CDATA[ ]]></USRDEFND1>
<USRDEFND2><![CDATA[ ]]></USRDEFND2>
<USRDEFND3><![CDATA[ ]]></USRDEFND3>
<USRDEFND4><![CDATA[ ]]></USRDEFND4>
<USRDEFND5><![CDATA[ ]]></USRDEFND5>
</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 xsi:nil="true" />
<taMdaUpdate_Items xsi:nil="true" />
</POPReceivingsType>
</eConnect>

 
Tax Question

Do you know what tables in SQL the TAX ID for a particular sopnumber is stored for the open and posted documents? 

 
SOP Transaction with Lots using eConnect

Edit 9/17/15 View the main article for this error here 

Hi all.

I'm trying to integrate the lots functionality to my app using eConnect.

This is the xml document I'm generating:

<?xml version="1.0" encoding="utf-16"?>
    <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>
            <taSopLotAuto>
                <SOPTYPE>3</SOPTYPE>
                <SOPNUMBE>INVSB2_10001</SOPNUMBE>
                <LNITMSEQ>0</LNITMSEQ>
                <ITEMNMBR>0000000840</ITEMNMBR>
                <LOCNCODE>MAIN</LOCNCODE>
                <QUANTITY>1</QUANTITY>
                <QTYFULFI>1</QTYFULFI>
                <ALLOCATE>1</ALLOCATE>
            </taSopLotAuto>
        </taSopLotAuto_Items>
        <taSopLineIvcInsert_Items>
            <taSopLineIvcInsert>
                <SOPTYPE>3</SOPTYPE>
                <SOPNUMBE>INVSB2_10001</SOPNUMBE>
                <CUSTNMBR>000223</CUSTNMBR>
                <DOCDATE>8/26/2013</DOCDATE>
                <LOCNCODE>MAIN</LOCNCODE>
                <ITEMNMBR>0000000840</ITEMNMBR>
                <UNITPRCE>12.00000</UNITPRCE>
                <XTNDPRCE>12.00</XTNDPRCE>
                <QUANTITY>1.000000</QUANTITY>
                <PRCLEVEL>EXTPRCLVL</PRCLEVEL>
                <ITEMDESC>Lot Number</ITEMDESC>
                <SALSTERR>POS            </SALSTERR>
                <SLPRSNID>POS</SLPRSNID>
                <IVITMTXB>1</IVITMTXB>
                <TAXSCHID>STATE</TAXSCHID>
                <PRSTADCD>MAIN</PRSTADCD>
                <ReqShipDate>8/26/2013</ReqShipDate>
                <FUFILDAT>8/26/2013</FUFILDAT>
                <ACTLSHIP>8/26/2013</ACTLSHIP>
                <SHIPMTHD>EXPRESS MAIL   </SHIPMTHD>
                <AUTOALLOCATELOT>1</AUTOALLOCATELOT>
                <QTYFULFI>1.000000</QTYFULFI>
                <CURNCYID>Z-US$</CURNCYID>
                <UOFM>1</UOFM>
            </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>
            <SOPTYPE>3</SOPTYPE>
            <SOPNUMBE>INVSB2_10001</SOPNUMBE>
            <USRDAT01>1/1/1900</USRDAT01>
            <USRDAT02>1/1/1900</USRDAT02>
            <USRTAB01>Local</USRTAB01>
            <USRDEF03>.</USRDEF03>
        </taSopUserDefined>
        <taSopDistribution_Items xsi:nil="true"/>
        <taAnalyticsDistribution_Items xsi:nil="true"/>
        <taSopMultiBin_Items xsi:nil="true"/>
        <taSopHdrIvcInsert>
            <SOPTYPE>3</SOPTYPE>
            <DOCID>INVOICE</DOCID>
            <SOPNUMBE>INVSB2_10001</SOPNUMBE>
            <TAXSCHID>STATE</TAXSCHID>
            <SHIPMTHD>EXPRESS MAIL   </SHIPMTHD>
            <LOCNCODE>MAIN</LOCNCODE>
            <DOCDATE>8/26/2013</DOCDATE>
            <CUSTNMBR>000223</CUSTNMBR>
            <CUSTNAME>Matos Vega, Vicenteaaaaaaaaaaaaaaaaa</CUSTNAME>
            <ShipToName>MATOS VEGA, VICENTE</ShipToName>
            <ADDRESS1>PLANTA FISICA</ADDRESS1>
            <FAXNUMBR>00000000000000</FAXNUMBR>
            <PHNUMBR1>00000000000000</PHNUMBR1>
            <PHNUMBR2>00000000000000</PHNUMBR2>
            <PHNUMBR3>00000000000000</PHNUMBR3>
            <SUBTOTAL>12.00</SUBTOTAL>
            <DOCAMNT>12.84</DOCAMNT>
            <SALSTERR>POS            </SALSTERR>
            <SLPRSNID>POS</SLPRSNID>
            <USER2ENT>IPAD</USER2ENT>
            <BACHNUMB>1-20130826</BACHNUMB>
            <PRBTADCD>MAIN</PRBTADCD>
            <PRSTADCD>MAIN</PRSTADCD>
            <DUEDATE>8/26/2013</DUEDATE>
            <CREATECOMM>1</CREATECOMM>
            <CREATETAXES>1</CREATETAXES>
            <CURNCYID>Z-US$</CURNCYID>
            <ReqShipDate>8/26/2013</ReqShipDate>
            <CKHOLD>1</CKHOLD>
            <PRCLEVEL>DEFAULT</PRCLEVEL>
            <DEFPRICING>1</DEFPRICING>
        </taSopHdrIvcInsert>
        <taSopToPopLink xsi:nil="true"/>
        <taSopUpdateCreateProcessHold xsi:nil="true"/>
        <taCreateSOPTrackingInfo xsi:nil="true"/>
        <taMdaUpdate_Items xsi:nil="true"/>
    </SOPTransactionType>
    <SOPProcessHoldType>
        <eConnectProcessInfo xsi:nil="true"/>
        <taRequesterTrxDisabler_Items xsi:nil="true"/>
        <taSopUpdateCreateProcessHold>
            <SOPTYPE>3</SOPTYPE>
            <SOPNUMBE>INVSB2_10001</SOPNUMBE>
            <PRCHLDID>Credit</PRCHLDID>
            <DELETE1>1</DELETE1>
            <UpdateSopIfExists>1</UpdateSopIfExists>
        </taSopUpdateCreateProcessHold>
    </SOPProcessHoldType>
    <SOPProcessHoldType>
        <eConnectProcessInfo xsi:nil="true"/>
        <taRequesterTrxDisabler_Items xsi:nil="true"/>
        <taSopUpdateCreateProcessHold>
            <SOPTYPE>3</SOPTYPE>
            <SOPNUMBE>INVSB2_10001</SOPNUMBE>
            <PRCHLDID>Price</PRCHLDID>
            <DELETE1>1</DELETE1>
            <UpdateSopIfExists>1</UpdateSopIfExists>
        </taSopUpdateCreateProcessHold>
    </SOPProcessHoldType>
</eConnect>
 

But I keep getting this error:

Sql procedure error codes returned: 

 

Error Number = 2305  Stored Procedure= taSopLineIvcInsert  Error Description = if AUTOALLOCATELOT = 1 or 2, the taSopLotAuto node must be called with a DOCID that fulfills or QTYFULFI > 0

Node Identifier Parameters: taSopLineIvcInsert

SOPNUMBE = INVSB2_10001

SOPTYPE = 3

Related Error Code Parameters for Node : taSopLineIvcInsert

AUTOALLOCATELOT = 1

 

Error Number = 4730  Stored Procedure= taSopLineIvcInsert  Error Description = AUTOALLOCATELOT = 1 and the SOP10101 qty fulfilled does not match the Sop Line fulfilled

Node Identifier Parameters: taSopLineIvcInsert

SOPNUMBE = INVSB2_10001

SOPTYPE = 3

Related Error Code Parameters for Node : taSopLineIvcInsert

AUTOALLOCATELOT = 1

 

I'm completely lost, any ideas?

Thank you very much!
EConnect RMA Serial Line: Error - unable to insert line items

I am in the process of creating a custom interface to receive items on an RMA and am using eConnect to do this. I am encountering an error that specifies the Return_Record_Type is missing. In the XML document it is clearly there. I am attaching the error and xml document along with the code that is setting it. I am using C# to perform this.

 

C# Code:

//RMA Serial Item ( GP Table: SVC05255)

detailcount = 0;

taFSRMALineSerial_ItemsTaFSRMALineSerial serialitem = new taFSRMALineSerial_ItemsTaFSRMALineSerial();

serialitem.Return_Record_Type = 1;

serialitem.RETDOCID = rmaheader.RETDOCID;

serialitem.CUSTNMBR = rmaheader.CUSTNMBR;

serialitem.LNSEQNBR = 100;

serialitem.QTYTYPE = 1;

serialitem.ITEMNMBR = "VX510 DC";

serialitem.SERLNMBR = "2-2-2-2";

serialitem.SERLTQTY = 1;

serialitem.SLTSQNUM = detailcount + 1;

serialitem.ADRSCODE = "PRIMARY";

serialitem.LOCNCODE = "RETURN";

serialitem.SVC_Original_Serial = 1;

serialitem.SVC_Original_SerialSpecified = true;

serialitem.UpdateIfExists = 1;

serialitems[detailcount] = serialitem;

eConnect Error:

Procedure or function 'taFSRMALineSerial' expects parameter '@I_vReturn_Record_Type', which was not supplied.

XMLDocument:

<?xml version="1.0" ?>

- <eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
- <FSRMAType>
  <eConnectProcessInfo xsi:nil="true" />
  <taRequesterTrxDisabler_Items xsi:nil="true" />
- <taFSRMALineSerial_Items>
- <taFSRMALineSerial>
  <Return_Record_Type>1</Return_Record_Type>
  <RETDOCID>RMA0KFP000002</RETDOCID>
  <CUSTNMBR>BBTFINANC027494</CUSTNMBR>
  <ADRSCODE>PRIMARY</ADRSCODE>
  <LNSEQNBR>100</LNSEQNBR>
  <ITEMNMBR>VX510 DC</ITEMNMBR>
  <SERLNMBR>2-2-2-2</SERLNMBR>
  <LOCNCODE>RETURN</LOCNCODE>
  <SLTSQNUM>1</SLTSQNUM>
  <SVC_Original_Serial>1</SVC_Original_Serial>
  </taFSRMALineSerial>
  <taFSRMALineSerial xsi:nil="true" />
  </taFSRMALineSerial_Items>
- <taFSRMALine_Items>
- <taFSRMALine>
  <Return_Record_Type>1</Return_Record_Type>
  <RETDOCID>RMA0KFP000002</RETDOCID>
  <RETTYPE>RP</RETTYPE>
  <RETSTAT>20</RETSTAT>
  <CUSTNMBR>BBTFINANC027494</CUSTNMBR>
  <ADRSCODE>PRIMARY</ADRSCODE>
  <ITEMNMBR>VX510 DC</ITEMNMBR>
  <ITEMDESC>0126011 - P/N M251-060-36-NAA VX510 DC 6-meg</ITEMDESC>
  <LOCNCODE>RETURN</LOCNCODE>
  <RETORIG>1</RETORIG>
  <LNITMSEQ>100</LNITMSEQ>
  <QUANTITY>1</QUANTITY>
  <UOFM>EACH</UOFM>
  <CUSTOWN>1</CUSTOWN>
  </taFSRMALine>
  <taFSRMALine xsi:nil="true" />
  </taFSRMALine_Items>
- <taFSRMAMaster>
  <Return_Record_Type>1</Return_Record_Type>
  <RETDOCID>RMA0KFP000002</RETDOCID>
  <RETORIG>1</RETORIG>
  <RETSTAT>10</RETSTAT>
  <RETTYPE>RP</RETTYPE>
  <LOCNCODE>RETURN</LOCNCODE>
  <CUSTNMBR>BBTFINANC027494</CUSTNMBR>
  <ADRSCODE>PRIMARY</ADRSCODE>
  <CUSTNAME>BB&T Financial, FSB</CUSTNAME>
  <CONTACT>Attn.: BB&T Equipment Manager</CONTACT>
  <ADDRESS1>2713 Forest Hills Road</ADDRESS1>
  <CITY>Wilson</CITY>
  <STATE>NC</STATE>
  <ZIPCODE>27893</ZIPCODE>
  <Bill_To_Customer>BBTFINANC027494</Bill_To_Customer>
  </taFSRMAMaster>
  </FSRMAType>
  </eConnect>

 

 

 
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
Archives | Table Listing | eConnect Error Database | eConnect Schema | Gold Star Locksmith
3