Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
DynDeveloper.com

DynDeveloper.com

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