Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

GP Remittance view as per user sees it. AP

Status
Not open for further replies.
jcw12000

jcw12000

Technical User
Joined
Sep 30, 2011
Messages
38
Location
ZA
HI

I am sitting with a issue. According to what I understand I see that a credit note will be applied to an invoice. I have used scripts from Victoria's site. It shows me that a credit was applied to 4 invoices. My PM30300 confirms this. But now if I ask the Account dept to pull me the info aka remittance advice the data doesn't look the same. The credit has no link that I can see back to the check/payment but on the RA the credit is visible.

Is there a script that will give me the same view as the remittance advice?
 
Sort by date Sort by votes
You cannot reprint a "remittance advice" that will show credit memos because a credit memo has no link to a check. In GP you can only apply credit transactions (return, credit memo or payment) to debit transactions (invoice, debit memo, finance charge). "Applying" transactions creates a link that is stored in the database and can be shown later. Since a credit memo cannot be applied to a payment/check (since they are both 'credits'), there is no link stored in the database.

What trips up a lot of companies is an optional feature in the Payables module that shows transactions applied inbetween the last check and the current check on the check stub/remittance. This optional feature is turned on if the ‘Print Previously Applied Documents on Remittance’ is selected on the Payables Management Setup window. For example:

1. You print a check to a vendor on 4/1/2012.
2. On 4/15/2012 you enter a credit memo for the vendor and apply it an invoice. If you have the ‘Print Previously Applied Documents on Remittance’ selected, this information is temporarily stored until the next time you print a check for this vendor.
3. On 5/1/2012 you print a check to the vendor, it will show the 4/15 credit memo and the invoice it was applied to. Not because the credit memo has anything to do with the check, but because it was applied to the vendor's invoice(s) after the last check was printed for the vendor. Once the check is printed the temporary information that was stored about the credit memo is cleared out, so there is no way to reprint it. The only thing that remains is the apply information for the credit memo and the invoice it was applied to. Nothing about what check stub/remittance it was 'reprinted' on.

If you want to see the information about how the credit memo was applied, you can:
1. Use my view, it sounds like you have already done this.
2. Drill down to the credit memo in GP and click on the Apply button to see what it was applied to.
However, without saving a copy of the check stub/remittance there is no way to know which check showed the credit memo information on it.

Hope this helps to answer your question.


Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:
blog:
 
Upvote 0 Downvote
I forgot to add. The 3 transaction that has marks next to it is the transactions in question
 
Upvote 0 Downvote
How are you printing a historical remittance? Is this an out-of-the-box report? Looking around, I am not finding any report that looks like this, but maybe I am missing it. If this is a custom report, where is it pulling the data from?

Without document numbers and types, it is very difficult to really understand what the attachment shows.

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:
blog:
 
Upvote 0 Downvote
I dont know what report is used. This is from the accounts dept. What data out of the tables would you need?
 
Upvote 0 Downvote
If I understood correctly, you're asking how the data is getting on this report, right? If not, can you please clarify? If yes, it would be important to find this report so you can look at where it is pulling the data from. Can you reprint this report right now showing this same data? If so, can you list the steps to navigate to this report so I can try to find it and take a look at it?

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:
blog:
 
Upvote 0 Downvote
Yes I need to know how the data gets on this report, I spoke to the account dept and they will write down the steps for me as soon as they have time. The lady told me its a Creditors recon report but she will write don the steps. I dont know if Creditors recon gives you an idea?
 
Upvote 0 Downvote
I am just waiting for confirmation but I think they are using a Report per vendor per payment date. Seems like the PayablesTransactions report/view
 
Upvote 0 Downvote
Would I use the doc type in PM30200 to know if the transaction is pos of neg? What doc types is pos and which is neg?
 
Upvote 0 Downvote
From
DOCTYPE (Document Type):
1 – Invoice
2 – Finance Charge
3 – Misc Charge
4 – Return
5 – Credit Memo
6 – Payment

GP stores all the amounts for payables transactions as positive numbers. I believe for what you're asking, DOCTYPE 1,2 and 3 are debits or "positive" and DOCTYPE 4,5 and 6 are credits or "negative".

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:
blog:
 
Upvote 0 Downvote
What data is stored in PM80500?
 
Upvote 0 Downvote
That table is called PM Reprint Posting Journal Apply To File. Based on the name, I would surmise that this is used for reprinting posting reports. Looking at the data in our GP that looks like a subset of the data in the apply tables.

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:
blog:
 
Upvote 0 Downvote
I'm working on a similar issue trying to give users a quick view/lookup of all invoices and credits documented on a check remittance to the vendor. I'm not quite sure, but it looks like you could kind of piece this together using the info from the 80500.TRXSORCE field coupled with the VendorID. Another hint to associate those credits/invoices that offset one another is the DEX_ROW_ID... At least in the example I'm looking at, the offsetting invoices/credits immediately preceed the associated apply check detail (and sequential DEX_ROW_IDs) in the 80500 table with matching VENDORID and TRXSORCE fields (default sort by DEX_ROW_ID). Warning... this is a yet untested theory... will update with a query and test results if I can figure it out.
 
Upvote 0 Downvote
I do believe a query such as the following might get one somewhat closer to the desired result...

SELECT PM80500_2.TRXSORCE, PM80500_2.VCHRNMBR, PM80500_2.DOCTYPE, PM80500_2.APFRDCNM, PM80500_2.VENDORID,
PM80500_2.APTVCHNM, PM80500_2.APTODCTY, PM80500_2.APTODCNM, PM80500_2.APPLDAMT, PM80500_2.DISTKNAM, PM80500_2.WROFAMNT,
PM80500_2.PPSAMDED, PM80500_2.GSTDSAMT, PM80500_2.TAXDTLID, PM80500_2.POSTEDDT, PM80500_2.PTDUSRID, PM80500_2.TEN99AMNT,
PM80500_2.DISAVTKN, PM80500_2.DOCDATE, PM80500_2.APTODCDT, PM80500_2.KEYSOURC, PM80500_2.ORAPPAMT, PM80500_2.ORDISTKN,
PM80500_2.ORWROFAM, PM80500_2.ORDATKN, PM80500_2.RLGANLOS, PM80500_2.DEX_ROW_ID, a.DOCNUMBR AS CkNum, a.VCHRNMBR AS CkVchrnmbr
FROM dbo.PM80500 WITH (nolock) INNER JOIN
(SELECT dbo.PM30200.DOCNUMBR, dbo.PM30200.VCHRNMBR, MIN(PM80500_1.DEX_ROW_ID) AS DEX_ROW, dbo.PM30200.VENDORID
FROM dbo.PM80500 AS PM80500_1 WITH (nolock) INNER JOIN
dbo.PM30200 ON PM80500_1.VCHRNMBR = dbo.PM30200.VCHRNMBR
WHERE (dbo.PM30200.DOCTYPE = 6)
GROUP BY dbo.PM30200.VCHRNMBR, dbo.PM30200.DOCNUMBR, dbo.PM30200.VENDORID) AS a ON dbo.PM80500.DEX_ROW_ID + 1 = a.DEX_ROW AND
dbo.PM80500.VENDORID = a.VENDORID RIGHT OUTER JOIN
dbo.PM80500 AS PM80500_2 WITH (nolock) ON dbo.PM80500.KEYSOURC = CASE PM80500_2.KEYSOURC WHEN '' THEN NULL
ELSE PM80500_2.KEYSOURC END
GROUP BY a.DOCNUMBR, PM80500_2.TRXSORCE, PM80500_2.VCHRNMBR, PM80500_2.DOCTYPE, PM80500_2.APFRDCNM, PM80500_2.VENDORID, PM80500_2.APTVCHNM,
PM80500_2.APTODCTY, PM80500_2.APTODCNM, PM80500_2.APPLDAMT, PM80500_2.DISTKNAM, PM80500_2.WROFAMNT, PM80500_2.PPSAMDED,
PM80500_2.GSTDSAMT, PM80500_2.TAXDTLID, PM80500_2.POSTEDDT, PM80500_2.PTDUSRID, PM80500_2.TEN99AMNT, PM80500_2.DISAVTKN,
PM80500_2.DOCDATE, PM80500_2.APTODCDT, PM80500_2.KEYSOURC, PM80500_2.ORAPPAMT, PM80500_2.ORDISTKN, PM80500_2.ORWROFAM,
PM80500_2.ORDATKN, PM80500_2.RLGANLOS, PM80500_2.DEX_ROW_ID, a.VCHRNMBR
ORDER BY PM80500_2.DEX_ROW_ID

The query is based primarily upon the PM80500 table. Check numbers associated with previously non-associable items should show up in the CkNum column... A simple CASE statement in the "SELECT" portion of the query will allow you to consolidate check numbers into one column if desired. In my situation, I find it desirable to create a view and union a subset of this data to create separate record appearances for invoices and credits that might offset one another in summing/reconciling to the total check amount. Many thanks to Victoria and jcw12000 for turning me on to the PM80500 table. I've been wrestling with the PM30200 and PM30300 tables forever trying to get payables "history" data to look like this.

DISCLAIMER:
The query in the above form was created based on some assumptions that may not turn out to be true (and specifically may turn out to be true in some implementations and not others)... It is somewhat untested. USE AT YOUR OWN RISK.

If this helps accomplish your purposes or if it doesn't appear to work at all, I'd definitely appreciate feedback either way.


John Picklesimer CPA
 
Upvote 0 Downvote
John,

In general, using the DEX_ROW_ID for anything is not recommended. Here are two good references for this:

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:
blog:
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

appelq
  • Locked
  • Question Question
Cash Batch and Bank Fees in Dynamics GP
Replies
0
Views
1K
appelq
appelq
Blaise01
  • Locked
  • Question Question
User Profile for Wennsoft Equipment - Equipment Manger Access
Replies
0
Views
1K
Blaise01
Blaise01
Nisly
  • Locked
  • Question Question
Deleting purchases from Fixed Assets Purchases in Great Plains
Replies
0
Views
1K
Nisly
Nisly
appelq
  • Locked
  • Question Question
GP Printing AP Checks is S..L..O..W....
Replies
0
Views
1K
appelq
appelq
C.MtZ
  • Locked
  • Question Question
How to create a menu Item on Dynamics Gp menu
Replies
0
Views
1K
C.MtZ
C.MtZ

Part and Inventory Search

Sponsor

Back
Top