DynDeveloper.com

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 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

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: Duplicate Tax Node ID in the RM10601 table

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

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

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

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

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

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

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.