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
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In

Archives

 

We don't have any sponsors on this site, so if I recommend something you can know that it came from the heart.

We have used Kellerman Software for years (mainly for the FTP suite, but there's a lot of great stuff); in my opinion it's well worth the $799 license. 

They're having a sale, that same license is $199 today. You should at least look at it...

A VAR called yesterday and asked me for a quote to upgrade a project that I'd coded... in 2012. They'd upgraded several times since then and the source code was gone. My current source code solution doesn't go that far back. 

So... what to do?

This is a short primer on how to create SSIS Packages from Visual Studio 2019. But first, some background...

Microsoft’s tool for developing Integration Services projects is named differently in different versions of the Visual Studio. For example, for the SQL Server 2008/2008R2 integration services, this tool was VS 2008’s Business Intelligence Development Studio (BIDS) and came with the SQL Server installation media. In VS 2010, the tool was renamed as the SQL Server Data Tools (SSDT) and shipped as a part of the SQL Server 2012 installation media.

In VS 2012/2013, this tool was slightly renamed as SSDT-BI and needed a separate download. In VS 2015, it was called SSDT and needed a separate download.

In VS 2017, this tool could be installed during the VS installation. It is worth mentioning that BIDS, SSDT-BI, and SSDT can be used not only for working with SSIS projects, but also SQL Server Analytical Services and SQL Server Reporting Services projects. Additionally, it is important to note that SSDT is backward compatible, which means that projects designed on the newer versions of SSDT can run on older versions of SQL Server.

So... on to VS 2019

This command will allow you to create a table on a linked server

Hi all, 

When I am using the transaction lot insert proc for negative variance the DATERECD seems to get ignored and therefore the correct lot is not assigned because the DATERECD and DTSEQNUM is not picked up from the lot master.  Note that there is no DTSEQNUM in this node.

execute taIVTransactionLotInsert

@I_vIVDOCNBR=@ivNumber,
@I_vIVDOCTYP=@docType,
@I_vITEMNMBR=@ItemNumber,
@I_vLOTNUMBR=@lotNumber,
@I_vLNSEQNBR=@lineSeq,
@I_vSERLTQTY=@useQty,
@I_vBIN=@bin,
@I_vLOCNCODE=@locnCode,
@I_vDATERECD=@lotDateReceived,
@O_iErrorState=@O_iErrorState OUTPUT,
@oErrString=@oErrString OUTPUT

Note that no errors are returned, the transaction is created fine, however cannot post as it says the lot number does not exist due to the lack of association already mentioned.

This now has me doing my own association to update the line record and lot master....

--update lot entries to match
-----------------------------------------------------------------------------------------------------
update IV10002 set DATERECD=@lotdatereceived, DTSEQNUM=@lotseq  where 
IVDOCNBR=@sIVNumber and IVDOCTYP=@doctype and LNSEQNBR=@LineSeq 

update IV00300 set ATYALLOC=ATYALLOC+@useQty where
LOTNUMBR=@lotnumber and ITEMNMBR= @ItemNumber and DATERECD=@lotdatereceived and DTSEQNUM=@lotseq and LOCNCODE=@LocnCode

set @lotsoldcheck=(select QTYRECVD-(QTYSOLD+ATYALLOC) from IV00300 where 
LOTNUMBR=@lotnumber and ITEMNMBR=@ItemNumber and DATERECD=@lotdatereceived and DTSEQNUM=@lotseq and LOCNCODE=@LocnCode)
if @lotsoldcheck=0 
begin
update IV00300 set LTNUMSLD=1 where LOTNUMBR=@lotnumber and ITEMNMBR=@ItemNumber and DATERECD=@lotdatereceived and DTSEQNUM=@lotseq and LOCNCODE=@LocnCode
end
-----------------------------------------------------------------------------------------------------

I am using GP 18.00.0438 and wondering if anyone else has this issue or is it a bug or has it been fixed in later updates or is it just me doing this incorrectly?

 

Cheers

 

 

 

I am trying to create AP invoices for open transactions from outside of GP.  I did the same thing for AR, and I had no issues.  Typically I create the tax portion (taPMTransactionTaxInsert), the distribution (taPMDistribution) and then the main transaction (taPMTransactionInsert.)  The first two work fine, but for the main transaction I get this error.  I've attached spreadsheet content that contains a section showing PM10000, PM10100 and PM10500 records for a transaction entered via the GP UI (00000000000000043) which worked fine.  The second transaction (00000000000000100) is listed also, but since I can't show the PM10000 record I put in its place the parameters being passed to taPMTransactionInsert.

I don't see the issue, so perhaps someone here might have the answer.  Thanks for any assistance.

eConnect Error ErrorCode SourceProc ErrorDesc
313 taPMTransactionInsert   Tax table detail does not equal the tax amount

Hello everyone,

I am trying to enroll employees to benefits using the eConnect.CreateTransactionEntityAsync() method call. My XML looks something like this:

<?xml version="1.0" encoding="UTF-16"?>
  -<eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  -<UPRCreateEmployeeBenefitType>
  <eConnectProcessInfo xsi:nil="true"/>
  <taRequesterTrxDisabler_Items xsi:nil="true"/>
  -<taCreateEmployeeBenefit>
    <EMPLOYID>Employee_Id_Here</EMPLOYID>
    <BENEFIT>Benefit_Id_Here</BENEFIT>
    <BNFBEGDT>6/1/2021</BNFBEGDT>
</taCreateEmployeeBenefit>
<taCreateEmployeeBenefitBasedOnPayDedCodes_Items xsi:nil="true"/>
</UPRCreateEmployeeBenefitType>
</eConnect>

This creates the employee and the benefit successfully, and I am able to verify the record was created in table UPR00600 (Payroll Benefit Master). After the eConnect call, I need to update the employee/employer cost by overriding table [BE010130]  (HR Benefit Master). The problem is that the record for my Employee with the benefit does not show up in table BE010130, and therefore I am unable to update the cost for the benefit programmatically. 

I found that going into reconcile human resources -> and triggering the update benefit enrollment, the employee that was created actually pops up in table BE010130, so that also verifies that at least my request actually goes through, at least partially. 

Using SQL Server Profiler, I was able to trace all the stored procedures that GP runs when I trigger the update benefit enrollment reconcile, but even after running most of the stored (manually, not through GP) procs that involve my employee and my benefit code, it still does not show up in the HR Benefit Master table. 

If anyone knows what I am missing, either if I should be sending a different XML, missing some stored procs, or even a way to programmatically trigger a reconcile, please let me know.

Tenemos aun GP 10.0 y FRX 6.7 dejo de funcionar. Alguien tendrá algunas consultas para generar los:

1. Estados de resultados,

2. Balance de comprobación,

3. Balance general

4. Estado de Flujo de efectivo,

5. Estado de cambio en el patrimonio.

6. Estado en el cambio de la situación financiera 

o afines y que pueda generarlos desde Excel..

 

Gracias por su colaboración.

hi there my team is trying to implement MR. we currently installed report designer on a server and some users need to log onto that server to generate reports, e.g. balance sheet or income statement. then, they need to send the link to other users to open in web browser. it seems to work in the short term. however, there doesn't seem to be any way the user can navigate the reports, say from a report ending January 31 to another one ending Feb 28. and there is no drill down from those reports. something feels missing here. also, can anyone describe how MR is deployed and used on a daily basis in your business environment? i mean, from the way we currently using MR, it doesn't seem to be a very useful tool...

can anyone shed some lights here? thank you!

Hi there another question about MR...

on our balance sheet, there's one column for current year (YEAR 2021) and one column for previous year (YEAR 2020), and there's a column called "variance", which is calculated by YEAR 2021 minus YEAR 2020. However, for account like accounts receivable, the YEAR 2021 only shows the total for transactions occurred in 2021. therefore this amount does not match the result run from the reports --> sales --> "historical trial balance" in GP. I.e., the result from GP shows the grand total from all the years, but MR only shows 1 year. 

if the user wants to see that amount matching the GP trial balance, what shall i do to achieve that?

thank you very much!

 

 

 

Hi there, I have been doing RM apply using econnect taRMApply please see below:

exec dbo.taRMApply
@I_vAPTODCNM = @APTODCNM,
@I_vAPFRDCNM = @APFRDCNM,
@I_vAPPTOAMT = @APPTOAMT,
@I_vAPFRDCTY = @APFRDCTY,
@I_vAPTODCTY = @APTODCTY,
@I_vAPPLYDATE = @APPLYDATE,
@I_vGLPOSTDT = @GLPOSTDT,
@O_iErrorState = @ErrorState output,
@oErrString = @ErrString output

now I made a mistake by setting the @APPLYDATE to the Due Date, while it actually should be the payment document date.

my question is, to make correction, can i do mass update via SQL to the RM20201 table.

thank you!

 

I have a really quick ( I hope) question.

 

We have a test environment that we are almost finishing the testing of for GP2018 18.3.1245 our production is on 18.1; the test environment only consists of 10 companies but something came up last week and i have been asked to add 6 additional company's

So the question is what is the best process

1. Create a new company in the test and restore a backup from the live environment to the test 

2. Run the GP Utilities so it updates the SQL database that i restored

 

Is the sufficient and correct?

 

thanks 

Hi,

I used to be able to schedule Integrations with Task Scheduler and bat files.

Do I still need to use AUtodim? I can get everything to run except for the IMRUN bat file.  GP Launches fine in the background via Task Scheduler, but the IM RUN does not go.

What is the best method now?  If it is Autodim, where can I get it?

I'm using GP 18.2

Thanks!
Mark







Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
site