Archives

 

I have a client who would prefer to type in the starting and ending account number in a stock SSRS GL Trial Balance report.

The first step is to open Report Builder.

 

 

This simple script will insert missing item classes into the Item Class Master IV40400 table from another GP database

This script will search two databases and insert the missing lines from one to another.

 

Yesterday I was asked to move two tables from one database to another. Actually, it was to 'move the vendors from one database to the second database IF THEY DON'T EXIST'.

So, I wrote the code, and I blogged it. 'Cause that's what I do <smiles>

Today's request is to move 30 tables from one db to another, because of database corruption. Again, where the lines don't exist. 

So, I wrote a script that writes a script.

You enter in the from and to database, the table name, and the unique fields and it will write a script for you

You run it like this:

sp_MigrateTable '','fromdb', 'todb', 'SOP10100', 'docnumbr,rmdtypal'

You'll need several helper functions:

ddf_columnType: http://dyndeveloper.com/ArticleView.aspx?ArticleID=1707

DDReservedWords: http://dyndeveloper.com/ArticleView.aspx?ArticleID=2893 

ddv_DALTableColumnMetaData: http://dyndeveloper.com/ArticleView.aspx?ArticleID=2894  

 

This is a helper table that is used in several of our internal scripts. Nothing to see here. Move along

 

This is a view that is used in our internal scripts, it provides metadata about tables
You'll need several helper scripts

ddf_columnType: http://dyndeveloper.com/ArticleView.aspx?ArticleID=1707

ddf_columnTypeVB: http://dyndeveloper.com/ArticleView.aspx?ArticleID=2895

DDReservedWords table: http://dyndeveloper.com/ArticleView.aspx?ArticleID=2893

This is a helper function that converts SQL data types to VB datatypes
This is a working stored procedure that 'wraps' the eConnect taUpdateCreateItemRcd stored procedure and makes it easy to call

eConnect error taRMTransaction 482 Unable to calculate the discount and due dates

This was a very simple fix

This is a working stored procedure that wraps the taRMTransaction eConnect stored procedure and makes it easier to call
This is a piece of code that wraps the taPOLine eConnect stored procedure and makes it easier to call
This is a piece of code that wraps the taPOHdr eConnect stored procedure and makes it easier to call

I received an email today that asked how to run a SQL command against all company databases, so let's look at that technique

The request was to run this code:

USE DATABASE
DECLARE @O_iErrorState int, @I_dAgingDate datetime
select @I_dAgingDate = convert(varchar(10), GetDate(), 102)
EXEC dbo.rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate, 127, 0, 0, '', @O_iErrorState OUT
SELECT @O_iErrorState
How do I get form modifications loaded into Dynamics?
This post demonstrates how to call the Dynamics GP native seermHATBSRSWrapper stored procedure, and then how to add an extra field to the output

Disclaimer:

Some of this is accounting and state/federal law, neither of which I'm an expert in. I'm simply documenting a piece of code that I wrote for a consultant request. 

The request was this:

The Dynamics GP Payroll Deduction setup is in UPR40900. That table holds a max allowable deduction per year (specifically, we're concerned with 401K deductions)

When an employee turns 50, we need to increase that max deduction by $6000 in the UPR00500 Employee Deduction Setup table. 

Write a script to do that on their 50th birthday. 

So, here it is -->

 

Error: SQL error inserting into the RM Tax Work Table - RM10601

Error: Duplicate Tax Node ID in the RM10601 table

We have an integration that can update the Site Id (LOCNCODE) on a PO Line Item using econnect. We have noticed that the IV00102 Last Order Date and QTY are updated but the QTY on Order is not. This requires a reconcile to be corrected. Are we missing something or is this an eConnect bug?
How can I know which user is editing an open purchase order in gp?

Hi all,

I get the error "DOC 1 ERROR: Password required, but none specified" when I attempt to integrate SOP invoices and returns into GP. A google search came up with nothing.

Would anyone know what the reason could be?

Thanks

Paul Chacko

Error: The currency id may not be passed in when entering transactions (Option = 1)

Saludos a Todos, estamos próximos a migrar a GP 2018 y nuestro proveedor no hará la migración de datos históricos, por lo que deseaba saber si existe algún para este tipo de tarea. desagradezco cualquier recomendación.

 

Saludos,

Hello,

I'm using the stored procedures taBRBankTransactionDist and taBRBankTransactionHeader to create Bank Rec Receipts. 

They appear to be working properly (No Errors) but we don't see the BACHNUMB we are trying to create.

We've check in SY00500, GL10000 & GL10001. Also looked in CM20100, CM20200 & CM20300.

Not sure where to look, stumped.

Thanks,

Dave

Hi,

My client wants to use an excel sheet [or CSV] to upload currency exchange rates to GP 10 every morning Monday to Sunday unattended. What are my choices here?Can I set up job in SQL? How?

Any suggestions will be appreciated.

Thanks

Paul

Hola

 

Me pueden orientar, de que tablas requiero para poder ver todas las transacciones de inventarios, compras, ventas, salidas por consumo, devoluciones, ajustes, transferencias, contabilizadas y no contabilizadas.

Can you guide me, which tables I need to be able to see all the transactions of inventories, purchases, sales, exits by consumption, returns, adjustments, transfers, posted and not accounted

Saludos

Currently when we add new vendors, the Tax Type defaults to Miscellaneous. We really need it to default to Not a 1099 Vendor. I attempted to change this with modifier but now the 1099 Box is not populating... Is this not something that can be changed and if not, how do I revert the default setting to Miscellaneous? I changed the text in Modifier to show Miscellaneous but now the 1099 Box is blank. Please help

I am slowly bur surely getting a grip on calling SQL stored procedures using a dexterity prototype stored procedure.  I have been able to return a value such as a record count or a name and address.  What I need to do next is to be able to return  a data set  from a select statement to the calling Dexterity sproc.  So, if a select statement returns 36 rows, I need to be able to look at all of the information from all three rows.  Is there a way to do this?

One of the reasons I am trying to do this is we have an external process using visual studio that uses the stored procedures.  We have some processes that run inside of GP that have been developed in dexterity and I would like to be able to use the same code to run both processes.  That way, I have a single place to change any business logic regardless of whether the process is being initiated by the external process or by the GP user in Dexterity.

Here is a sample of a SQL stored procedure and the SQL script to call it that returns 36 rows.  How can I run this via a Dexterity prototype stored procedure and be able to see all 36 rows in the resulting data set?

USE [SSL]
GO
/****** Create Stored Procedure:  uspGetCustomerPOAndInvoice     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[uspGetCustomerPOAndInvoice]
(
@CustomerID varchar(20),
@InvoiceDate datetime,
@SOPType integer
)
AS

select sopnumbe as [SOP Number]
,custnmbr as [Customer ID]
,cstponbr as [Customer PO Number]
from sop30200
where custnmbr = @CustomerID
and docdate = @InvoiceDate 
and soptype = @SOPType


/*
declare @CustomerID varchar(20)
declare @InvoiceDate datetime
declare @SOPType integer

set @CustomerID = 'XXXX'
set @InvoiceDate = '2/1/2017'
set @SOPType = 3

exec uspGetCustomerPOAndInvoice @CustomerID, @InvoiceDate, @SOPType
*/

Thanks,

Lonnie

what is the field and table of unit of measurement of inventories in gp

Hi,

I have a client that setup a new Company DB, put in some opening transactions as of 12/31/2017.  Changed their mind.  Wanted to put in opening Transactions of 7/1/2018 instead.  They have posted GL transactions prior to that for sure.

they have vendors and GL accounts setup just the way they want them.

What's the best approach?

1. Remove all prior transactions and update Summary balances?  If so how?

2.  Setup a new company DB and bring over COA, vendors and all other setup items ? If so, how?

Thanks!
Mark

Good evening,

  I need to short the length of the "Next Call Number"  to 4 total places, by example right now you enter the 80 like the next service call number, automatically the system created a 0000000080 service call number, I need only something like a service call number like that 0080 but if you try to enter by example 0080 in this field when you create a service call the system created a 0000000080. In other words always GP place 8 zeroes before the next serice call number.

Any suggestion is welcome.

Thanks,

 Mr. García

 

Dynamics GP 2016 - See in Field Services Module (Tools/Setup/Services Setup/Services) 

I am not a sql expert.  I can create SSRS reports but editing stored procedures is beyond me.  The SP seermHATBSRSWrapper does not pull the customer PO number.  Does anyone know how to add the one field?  I can do it in Dynamics GP eport writer but SSRS is another animal.  I have attached a text file with the stored procedure.

Thanks
Mike

Hi

Is there a vendor invoice header and/or a vendor invoice line table in gp?

I am working on pulling data out of gp and it seems that the pm trans tables house the vendor invoice number, but any detail lines would relate to the po?

Thanks!

Dave

We recently upgraded from GP2013SP1 to GP2018 and have run into a few Smart Connect issues.  The most recent is with a PO Creation Integration we used 300+ times in GP2013 without an issue.

When we run the integration now in GP2018, the new PO Line does not natively check the INCLUDE button on the 'Purchasing Manufacturer's Item Number Entry' window, where an item was already checked as the PRIMARY, on the 'Manufacturer's Item Number Maintenance' windows, as it did in GP 2013.

When we manually enter a PO in GP2018, the PRIMARY does always automatically flow through to the the INCLUDE field 'Purchasing Manufacturer's Item Number Entry'.

I searched in the Purchase Order Processing Setup and did not see a place to have the PRIMARY automatically check INCLUDE, and as I said, this did always work in GP2013.

Hoping someone has seen this before and knows how to resolve it.

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