Solved

GP AP Invoicing Question

Posted on 2013-05-24
18
897 Views
Last Modified: 2013-06-07
Hi all,

I'm trying to create a query in Great Plains for AP Invoices.  I need the results to include the "normal" vendor info(at least the vendorid, but name, etc), the invoice info(like number, entry date,amount, etc), and the G/L accounts to credit and debit.

I have located a few tables that contain some of the data:

pm00200 - Vendor info
pm00300 - Vendor address
pm00400 - Seems to be invoice stuff or at least part of it.
pm30200 - Seems to be paid invoice stuff(i think).

But I don't see anything that necessarily links them together(trxsorce?) and also gets me the Expense and Payable G/L accounts to credit and debit.

Any ideas?

Thank you
0
Comment
Question by:FMCA-IT
  • 9
  • 8
18 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 39194843
Unless Great Plains has changed drastically in the last couple of years, you should be starting with the general ledger table, which is GL20000 I believe. That will give you every single financial transaction, both debits and credits.

From there, you can link to the AP subledger (might be PM20000 ?), also the statement of accounts (GL1????).

I'm doing this from memory, so I can't look anything up for you, but you will definitely need to start with the GL.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39194848
I believe this code will give you what you're looking for: http://victoriayudin.com/2008/11/28/sql-view-to-show-all-gl-distributions-for-ap-transactions/

Let me know if it is missing any of the fields you're looking for and you need help finding them.

Also, here is a list of the common tables for AP transactions in Dynamics GP:
http://victoriayudin.com/gp-tables/pm-tables/
0
 
LVL 2

Author Comment

by:FMCA-IT
ID: 39195353
Thank you victoria, I think this is mostly what I am looking for.  I think I also need a few more fields:

1099Amount
1099type(royalties, rents, interest, etc)
Invoice tax amounts
Financial Period - I think I can derive this from the Document Date, can't I?
Discount Amount

Thanks
0
 
LVL 18

Accepted Solution

by:
Victoria Yudin earned 500 total points
ID: 39195632
Below is the select query from my blog with the 1099 amount added, so you can see the example, I put comments with *'s above where I made the changes:

SELECT  D.VENDORID Vendor_ID,
    N.VENDNAME Vendor_Name,
    K.DOCNUMBR Document_Number,
    D.PSTGDATE GL_Posting_Date,
    K.DOCDATE Document_Date,
    case K.DOCTYPE
         when 1 then 'Invoice'
         when 2 then 'Finance Charge'
         when 3 then 'Misc Charge'
         when 4 then 'Return'
         when 5 then 'Credit Memo'
         when 6 then 'Payment'
         end Document_Type,
    G.ACTNUMST Account_Number,
    A.ACTDESCR Account_Name,
    case D.DISTTYPE
         when 1 then 'Cash'
         when 2 then 'Payable'
         when 3 then 'Discount Available'
         when 4 then 'Discount Taken'
         when 5 then 'Finance Charge'
         when 6 then 'Purchase'
         when 7 then 'Trade Disc.'
         when 8 then 'Misc. Charge'
         when 9 then 'Freight'
         when 10 then 'Taxes'
         when 11 then 'Writeoffs'
         when 12 then 'Other'
         when 13 then 'GST Disc'
         when 14 then 'PPS Amount'
         when 16 then 'Round'
         when 17 then 'Realized Gain'
         when 18 then 'Realized Loss'
         when 19 then 'Due To'
         when 20 then 'Due From'
         end Distribution_Type,
    D.DEBITAMT Debit_Amount,
    D.CRDTAMNT Credit_Amount,
    D.DistRef Distribution_Reference,
    T.BACHNUMB Batch_ID,
    T.TRXDSCRN Trx_Description,
    T.STAT Trx_Status,
    D.VCHRNMBR Voucher_Number,
    D.CURNCYID Currency_ID,
    coalesce(D.XCHGRATE,0) Exchange_Rate,
    D.ORDBTAMT Originating_Debit_Amount,
    D.ORCRDAMT Originating_Credit_Amount,
    case T.VOIDED
         when 0 then 'No'
         when 1 then 'Yes'
         end Voided,
    T.PORDNMBR PO_Number,

-- **********added 1099 columns**************
	T.TEN99AMNT [1099 Amount],
	case T.TEN99TYPE 
		 when 1 then 'Not a 1099 Vendor'
		 when 2 then 'Dividend'
		 when 3 then 'Interest'
		 when 4 then 'Miscellaneous'
		 else ''
		 end [1099 Type], 
	T.TEN99BOXNUMBER [1099 Box Number]
 
FROM
-- all open trx distributions
(SELECT  VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef,
         PSTGDATE, CURNCYID, ORDBTAMT, ORCRDAMT, XCHGRATE
         FROM PM10100
UNION ALL
-- all historical trx distributions
 SELECT  P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT, P.CRDTAMNT, P.DSTINDX,
         P.DISTTYPE, P.DistRef, P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT, M.XCHGRATE
         FROM PM30600 P
         LEFT OUTER JOIN  -- historical exchange rate
       MC020103 M
       ON P.VCHRNMBR = M.VCHRNMBR
       AND P.DOCTYPE = M.DOCTYPE) D
 
-- add document number and type
LEFT OUTER JOIN
        PM00400 K
        ON D.VCHRNMBR = K.CNTRLNUM
        AND D.CNTRLTYP = K.CNTRLTYP
 
--add GL account number
LEFT OUTER JOIN
        GL00105 G
        ON D.DSTINDX = G.ACTINDX
 
--add status, batch and trx description
-- **********added 1099 columns to all tables in query below**************
LEFT OUTER JOIN
     (SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
      STAT = 'Work', VOIDED = 0, PORDNMBR, TEN99AMNT, TEN99TYPE, TEN99BOXNUMBER
      FROM PM10000
     UNION
      SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
      STAT = 'Open', VOIDED, PORDNMBR, TEN99AMNT, TEN99TYPE, TEN99BOXNUMBER
      FROM PM20000
     UNION
      SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
      STAT = 'History', VOIDED, PORDNMBR, TEN99AMNT, TEN99TYPE, TEN99BOXNUMBER
      FROM PM30200
     UNION
      SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1,
      STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
      FROM PM10300
     UNION
      SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB,
      STAT = 'Work', VOIDED = 0, PORDNMBR = '', TRXDSCRN, TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
      FROM PM10400) T
     ON T.VCHRNMBR = D.VCHRNMBR
     AND T.CNTRLTYP = D.CNTRLTYP
 
--add GL account name
LEFT OUTER JOIN
        GL00100 A
        ON A.ACTINDX = D.DSTINDX
 
--add vendor name
LEFT OUTER JOIN
        PM00200 N
        ON N.VENDORID = D.VENDORID

Open in new window


There are a number of different discount columns, you can add them the same way as the 1099 example.
Not sure what you're looking for with the taxes - if you want the total tax amount or schedule ID, that can be added the same way as the 1099 and discount info. If you're looking for the tax details, you will need to link in the TX30000 table.
For the Financial Period - I would expect you want the GL Posting Date for that, not the Document Date, but you may want to confirm that with the person requesting the report.
0
 
LVL 2

Author Comment

by:FMCA-IT
ID: 39204884
Victoria,

The query isn't working as is.  Apparently  TEN99Type and Ten99BoxNumber don't exist on all of those table.  I'm going to attempt to fix up.

Msg 207, Level 16, State 1, Line 2
Invalid column name 'TEN99TYPE'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'TEN99BOXNUMBER'.

I'm not sure if it's my GP version that's causing it.

B
0
 
LVL 2

Author Comment

by:FMCA-IT
ID: 39205037
I think i fixed the issues with the query:

SELECT  D.VENDORID Vendor_ID,
    N.VENDNAME Vendor_Name,
    K.DOCNUMBR Document_Number,
    D.PSTGDATE GL_Posting_Date,
    K.DOCDATE Document_Date,
    case K.DOCTYPE
         when 1 then 'Invoice'
         when 2 then 'Finance Charge'
         when 3 then 'Misc Charge'
         when 4 then 'Return'
         when 5 then 'Credit Memo'
         when 6 then 'Payment'
         end Document_Type,
    G.ACTNUMST Account_Number,
    A.ACTDESCR Account_Name,
    case D.DISTTYPE
         when 1 then 'Cash'
         when 2 then 'Payable'
         when 3 then 'Discount Available'
         when 4 then 'Discount Taken'
         when 5 then 'Finance Charge'
         when 6 then 'Purchase'
         when 7 then 'Trade Disc.'
         when 8 then 'Misc. Charge'
         when 9 then 'Freight'
         when 10 then 'Taxes'
         when 11 then 'Writeoffs'
         when 12 then 'Other'
         when 13 then 'GST Disc'
         when 14 then 'PPS Amount'
         when 16 then 'Round'
         when 17 then 'Realized Gain'
         when 18 then 'Realized Loss'
         when 19 then 'Due To'
         when 20 then 'Due From'
         end Distribution_Type,
    D.DEBITAMT Debit_Amount,
    D.CRDTAMNT Credit_Amount,
    D.DistRef Distribution_Reference,
    T.BACHNUMB Batch_ID,
    T.TRXDSCRN Trx_Description,
    T.STAT Trx_Status,
    D.VCHRNMBR Voucher_Number,
    D.CURNCYID Currency_ID,
    coalesce(D.XCHGRATE,0) Exchange_Rate,
    D.ORDBTAMT Originating_Debit_Amount,
    D.ORCRDAMT Originating_Credit_Amount,
    case T.VOIDED
         when 0 then 'No'
         when 1 then 'Yes'
         end Voided,
    T.PORDNMBR PO_Number,

-- **********added 1099 columns**************
	T.TEN99AMNT [1099 Amount],
	case T.TEN99TYPE 
		 when 1 then 'Not a 1099 Vendor'
		 when 2 then 'Dividend'
		 when 3 then 'Interest'
		 when 4 then 'Miscellaneous'
		 else ''
		 end [1099 Type], 
	T.TEN99BOXNUMBER [1099 Box Number]
 
FROM
-- all open trx distributions
(SELECT  VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef,
         PSTGDATE, CURNCYID, ORDBTAMT, ORCRDAMT, XCHGRATE
         FROM PM10100
UNION ALL
-- all historical trx distributions
 SELECT  P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT, P.CRDTAMNT, P.DSTINDX,
         P.DISTTYPE, P.DistRef, P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT, M.XCHGRATE
         FROM PM30600 P
         LEFT OUTER JOIN  -- historical exchange rate
       MC020103 M
       ON P.VCHRNMBR = M.VCHRNMBR
       AND P.DOCTYPE = M.DOCTYPE) D
 
-- add document number and type
LEFT OUTER JOIN
        PM00400 K
        ON D.VCHRNMBR = K.CNTRLNUM
        AND D.CNTRLTYP = K.CNTRLTYP
 
--add GL account number
LEFT OUTER JOIN
        GL00105 G
        ON D.DSTINDX = G.ACTINDX
 
--add status, batch and trx description
-- **********added 1099 columns to all tables in query below**************
LEFT OUTER JOIN
     (SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
      STAT = 'Work', VOIDED = 0, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
      FROM PM10000
     UNION
      SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
      STAT = 'Open', VOIDED, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
      FROM PM20000
     UNION
      SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN,
      STAT = 'History', VOIDED, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
      FROM PM30200
     UNION
      SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1,
      STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
      FROM PM10300
     UNION
      SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN, 
      STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
      FROM PM10400) T
     ON T.VCHRNMBR = D.VCHRNMBR
     AND T.CNTRLTYP = D.CNTRLTYP
 
--add GL account name
LEFT OUTER JOIN
        GL00100 A
        ON A.ACTINDX = D.DSTINDX
 
--add vendor name
LEFT OUTER JOIN
        PM00200 N
        ON N.VENDORID = D.VENDORID
                                            

Open in new window

0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39205065
Ah, sorry, I should have asked what version of GP you're on, those got added fairly recently. If you've got it working, great. If you need additional help, please let me know.
0
 
LVL 2

Author Comment

by:FMCA-IT
ID: 39205316
We're on version 9.

We only use 1099-Miscellenious so I took a shortcut and assumed that if there was a 1099-amount > 0 then it was a type=4.  Also, from what i was able to find on the web, the 1099box type is 7 for miscellaneous, so i took the shortcut there as well.  Both are added above as CASE statements.  Hopefully, you don't see a reason this thinking is flawed.

Thanks.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39207146
Yep, with version 9 there was only one 1099 option available per vendor. We are on GP 2010 and there you have the option of changing the box number on each transaction if you need to, so that is why I added that detail at the transaction level. I think your change will work just fine if all your vendors are the same type and use the same box number.

The alternative would be to not include the type and box number in that detail query and to get that information from the PM00200 table, which is already linked in.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Author Comment

by:FMCA-IT
ID: 39229060
Victoria,  

I noticed a few days ago that I'm getting vendors with null values in them.  I've been trying to figure out where they are coming from to no avail.  I'm thinking it's an outer join issue.  Have you run into this with the view/proc that you gave me above?  If so, where is the likely place that this is occurring?  It appears to happen only for a small handful of vendors(our medical and dental insurance carriers).  When I look in the GP app, it seems to show the transactions in question assigned to the proper vendor.


Thanks.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39229749
I've run this on a number of different datasets and am not seeing nulls. Which fields are showing nulls?
0
 
LVL 2

Author Comment

by:FMCA-IT
ID: 39229803
VendorID is spaces and VendorName is Null.  I've changed a little of the data to protect the vendor names and abbreviated the column names so that they'd fit as well as removing some of the columns that may not be relevant(but can repost with all if you think they are relevant).  Here is what I see:

Vendor_ID	Vendor_Name	Doc_Num	GL_Post_Date	Document_Type	Account_Number	Account_Name	Distribution_Type	Debit	Credit	Trx_Status
Shield       	Shield Inc.	Mar-13	3/7/2013	Invoice		000-2000        ACCOUNTS PAYABLE        Payable		0	566.1	History
               	NULL		Mar-13	3/7/2013	Invoice		500-8344        EMPL BENE LIFE      	Purchase	413	0	History
               	NULL		Mar-13	3/7/2013	Invoice		700-8344        EMPL BENE LIFE      	Purchase	107.74	0	History
               	NULL		Mar-13	3/7/2013	Invoice		000-1140        RECE DUE FROM          	Purchase	377.09	0	History
National        NAT'L LIFE INS  Mar-13	3/7/2013	Invoice		000-2000        ACCOUNTS PAYABLE        Payable		0	897.83	History

Open in new window


We had thousands of records, but i just grabbed a few, including one before and one after a few of the records in question.  This situation seems to be happening with about 4% of the records being returned on from the view.

Thanks,
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39229867
The Nulls are showing up because the vendor ID is coming in as blank. Can you see what these return:

select * from PM10100 where VENDORID = ''
select * from PM30600 where VENDORID = ''

Open in new window

0
 
LVL 2

Author Comment

by:FMCA-IT
ID: 39229895
The first query returned 0 records.
The second query returned 2769 records.

The view returns 2777 records with null vendors.

What is it that would make the system put these blank values in VendorID in this table for some and not other vendors?  70k+ records have vendor numbers.  Also, can we somehow join one of the columns here to pick the vendorid back up elsewhere?  The screen shows the correct vendor.

Thanks
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39229912
What this means is that for some reason you have 2769 records in the historical distribution table with no vendor ID. I would consider this an issue with your data, as this should not be happening. Are these all really old transactions? Are these imported?

In any case, you can change the code to pull those from the transaction tables to see if that fixes the reporting side of it, at least:
SELECT  T.VENDORID Vendor_ID,
    N.VENDNAME Vendor_Name,
    K.DOCNUMBR Document_Number,
    D.PSTGDATE GL_Posting_Date,
    K.DOCDATE Document_Date,
    case K.DOCTYPE
         when 1 then 'Invoice'
         when 2 then 'Finance Charge'
         when 3 then 'Misc Charge'
         when 4 then 'Return'
         when 5 then 'Credit Memo'
         when 6 then 'Payment'
         end Document_Type,
    G.ACTNUMST Account_Number,
    A.ACTDESCR Account_Name,
    case D.DISTTYPE
         when 1 then 'Cash'
         when 2 then 'Payable'
         when 3 then 'Discount Available'
         when 4 then 'Discount Taken'
         when 5 then 'Finance Charge'
         when 6 then 'Purchase'
         when 7 then 'Trade Disc.'
         when 8 then 'Misc. Charge'
         when 9 then 'Freight'
         when 10 then 'Taxes'
         when 11 then 'Writeoffs'
         when 12 then 'Other'
         when 13 then 'GST Disc'
         when 14 then 'PPS Amount'
         when 16 then 'Round'
         when 17 then 'Realized Gain'
         when 18 then 'Realized Loss'
         when 19 then 'Due To'
         when 20 then 'Due From'
         end Distribution_Type,
    D.DEBITAMT Debit_Amount,
    D.CRDTAMNT Credit_Amount,
    D.DistRef Distribution_Reference,
    T.BACHNUMB Batch_ID,
    T.TRXDSCRN Trx_Description,
    T.STAT Trx_Status,
    D.VCHRNMBR Voucher_Number,
    D.CURNCYID Currency_ID,
    coalesce(D.XCHGRATE,0) Exchange_Rate,
    D.ORDBTAMT Originating_Debit_Amount,
    D.ORCRDAMT Originating_Credit_Amount,
    case T.VOIDED
         when 0 then 'No'
         when 1 then 'Yes'
         end Voided,
    T.PORDNMBR PO_Number,
	T.TEN99AMNT [1099 Amount],
	case T.TEN99TYPE 
		 when 1 then 'Not a 1099 Vendor'
		 when 2 then 'Dividend'
		 when 3 then 'Interest'
		 when 4 then 'Miscellaneous'
		 else ''
		 end [1099 Type], 
	T.TEN99BOXNUMBER [1099 Box Number]
 
FROM
-- all open trx distributions
(SELECT  VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT, DSTINDX, DISTTYPE, DistRef,
         PSTGDATE, CURNCYID, ORDBTAMT, ORCRDAMT, XCHGRATE
         FROM PM10100
UNION ALL
-- all historical trx distributions
 SELECT  P.VENDORID, P.VCHRNMBR, P.CNTRLTYP, P.DEBITAMT, P.CRDTAMNT, P.DSTINDX,
         P.DISTTYPE, P.DistRef, P.PSTGDATE, P.CURNCYID, P.ORDBTAMT, P.ORCRDAMT, M.XCHGRATE
         FROM PM30600 P
         LEFT OUTER JOIN  -- historical exchange rate
       MC020103 M
       ON P.VCHRNMBR = M.VCHRNMBR
       AND P.DOCTYPE = M.DOCTYPE) D
 
-- add document number and type
LEFT OUTER JOIN
        PM00400 K
        ON D.VCHRNMBR = K.CNTRLNUM
        AND D.CNTRLTYP = K.CNTRLTYP
 
--add GL account number
LEFT OUTER JOIN
        GL00105 G
        ON D.DSTINDX = G.ACTINDX
 
--add status, batch and trx description
LEFT OUTER JOIN
     (SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, VENDORID,
      STAT = 'Work', VOIDED = 0, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
      FROM PM10000
     UNION
      SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, VENDORID,
      STAT = 'Open', VOIDED, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
      FROM PM20000
     UNION
      SELECT VCHRNMBR, CNTRLTYP, BACHNUMB, TRXDSCRN, VENDORID,
      STAT = 'History', VOIDED, PORDNMBR, TEN99AMNT, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 4
			ELSE 0
		END AS TEN99TYPE, 
		CASE 
			WHEN TEN99AMNT > 0 THEN 7
			ELSE 0
		END AS TEN99BOXNUMBER
      FROM PM30200
     UNION
      SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, COMMENT1, VENDORID,
      STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
      FROM PM10300
     UNION
      SELECT PMNTNMBR, CNTRLTYP = 1, BACHNUMB, TRXDSCRN, VENDORID,
      STAT = 'Work', VOIDED = 0, PORDNMBR = '', TEN99AMNT=0, TEN99TYPE=0, TEN99BOXNUMBER=0
      FROM PM10400) T
     ON T.VCHRNMBR = D.VCHRNMBR
     AND T.CNTRLTYP = D.CNTRLTYP
 
--add GL account name
LEFT OUTER JOIN
        GL00100 A
        ON A.ACTINDX = D.DSTINDX
 
--add vendor name
LEFT OUTER JOIN
        PM00200 N
        ON N.VENDORID = T.VENDORID

Open in new window

0
 
LVL 2

Author Comment

by:FMCA-IT
ID: 39229986
I'll try your query, but these are new transactions.  In the example above, they are from Mar 7 this year.  I have some as far back as 2004, but I also have others that were posted today.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39229996
I would recommend starting a new question to see if others can recommend some options as to why you have blanks where data should be. You might also want to consider talking to Dynamics GP support about this.
0
 
LVL 2

Author Comment

by:FMCA-IT
ID: 39230011
I shall do that.  You have been very helpful.  Thank you.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 39
Copy Database Wizard Error 3 22
Database connection opened on a machine 8 37
sql calculate averages 18 32
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now