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
mark nelson 11/11/2015 10:33:27 AM

Stopping a quartly data move

I am needing to see about stopping the quarterly Great Plains data move to historical from triggering a complete resend of invoice data to one of our systems.  Here is the information of the existing query that was created around 10 years ago, long before I arrived here. This is the most information I can string together.

 

This section holds a list of keys. The attributes of these keys tells the Check Integration application the location of Company Database, where to export the check flat-to, whether the account is active and when the account is active.

The SQL used to query the Great Plains database was originally provided by Anthony Hill:

   SELECT

       dbo.CM20200.CMTrxNum,

       dbo.CM20200.CMTrxType,

       dbo.CM20200.TRXDATE,

       dbo.CM20200.TRXAMNT,

       dbo.CM20200.CMLinkID,

       dbo.CM20200.paidtorcvdfrom,

       dbo.PM30300.VENDORID,

       dbo.PM30300.APTODCNM,

       dbo.PM30200.TRXDSCRN,

       dbo.PM30200.DOCDATE,

       dbo.PM30200.DOCAMNT,

       dbo.PM30200.DOCNUMBR,

       dbo.PM30200.BACHNUMB,

       dbo.PM30200.PORDNMBR,D.DistRef

   FROM

       dbo.CM20200 // Transaction Table

       INNER JOIN dbo.PM30300 ON

           (dbo.CM20200.CMTrxNum = dbo.PM30300.APFRDCNM) AND (dbo.CM20200.CMLinkID = dbo.PM30300.VENDORID)

       INNER JOIN dbo.PM30200 ON

           (dbo.PM30300.APTODCNM = dbo.PM30200.DOCNUMBR) AND

           (dbo.PM30300.VENDORID = dbo.PM30200.VENDORID) AND

           (dbo.PM30200.DOCTYPE = 1)  // 6 - Checks, 1 - Invoices - But we need the invoice data

       LEFT OUTER JOIN

           (SELECT DISTINCT VENDORID, VCHRNMBR, DistRef FROM PM30600 WHERE CNTRLTYP = 0 AND DISTTYPE = 6) D

           ON dbo.PM30300.VENDORID = D.VENDORID

           AND dbo.PM30300.APTVCHNM = D.VCHRNMBR

   WHERE

       (dbo.PM30200.TRXDSCRN LIKE @SystemID + '%') AND

       (dbo.CM20200.TRXDATE = @TRXDATE);

The SystemID from the Custom Config Key is used for the @SystemID parameter.

Check Reconciliation Query

   SELECT

       T.CUSTNMBR,

       CM.CUSTNAME,

       T.DOCDATE,

       T.GLPOSTDT,

       T.CHEKNMBR,

       sum(A.APPTOAMT) APPTOAMT,

       A.APTODCNM,

       T.BACHNUMB,

       T.RMDTYPAL,

       T.DOCNUMBR,

       DistWorkFile.DistRef

   FROM

   -- T

   (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,

       DOCNUMBR, DEX_ROW_TS, BACHNUMB, CHEKNMBR

   FROM RM20101 -- RM Apply Open File

   WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0) and (AGNGBUKT <= 1) -- Only completed items

   UNION

   SELECT

       CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,

       DOCNUMBR, DEX_ROW_TS, BACHNUMB, CHEKNMBR

   FROM RM30101 -- RM History File

   WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0) --  Only completed items

   ) T

   INNER JOIN RM00101 CM -- Customer Master

       ON T.CUSTNMBR = CM.CUSTNMBR

   INNER JOIN

   -- A

   (SELECT tO1.CUSTNMBR, APTODCTY, APTODCNM,

       APFRDCTY, APFRDCNM,         APPTOAMT

   FROM RM20201 tO2 -- RM Apply Open File

   INNER JOIN RM20101 tO1 -- RM Open File Debit Copy

       ON tO2.APTODCTY = tO1.RMDTYPAL

           AND tO2.APTODCNM = tO1.DOCNUMBR

   WHERE (APTODCNM LIKE @SystemID + '%')

   UNION

   SELECT tH1.CUSTNMBR, APTODCTY, APTODCNM,

       APFRDCTY, APFRDCNM,         APPTOAMT

   FROM RM30201 tH2 -- RM Apply History File

   INNER JOIN RM30101 tH1 -- RM History File

       ON tH2.APTODCTY = tH1.RMDTYPAL

           AND tH2.APTODCNM = tH1.DOCNUMBR

   WHERE (APTODCNM LIKE @SystemID + '%')

   ) A

       ON A.APFRDCTY = T.RMDTYPAL and A.APFRDCNM = T.DOCNUMBR

   INNER JOIN

   -- D

   (SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF

   FROM RM20101 -- RM Open File Debit Copy

   UNION

   SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF

   FROM RM30101 -- RM History File

   ) D

       ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR        

   LEFT OUTER JOIN

   -- Distribution Work File

   (SELECT DISTINCT DOCNUMBR, RMDTYPAL, DistRef

   FROM dbo.RM10101

   WHERE DISTTYPE = 9

   ) DistWorkFile ON

       D.DOCNUMBR = DistWorkFile.DOCNUMBR and

       D.RMDTYPAL = DistWorkFile.RMDTYPAL

   WHERE

       T.DEX_ROW_TS >= @DEX_ROW_TS and T.DEX_ROW_TS <= DATEADD(D,1,@DEX_ROW_TS)

   GROUP BY

       T.CUSTNMBR,

       CM.CUSTNAME,

       T.DOCDATE,

       T.GLPOSTDT,

       T.CHEKNMBR,

       A.APTODCNM,

       T.BACHNUMB,

       T.RMDTYPAL,

       T.DOCNUMBR,

       DistWorkFile.DistRef

   ORDER BY

       A.APTODCNM;

 

 

Version: GP 2010
Section: Dynamics GP, eConnect, SQL Scripts








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