Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

jcw12000 (TechnicalUser) (OP)
17 May 12 2:37
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?
VictoriaYudin (IS/IT--Management)
17 May 12 6:35
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:
http://www.GPReportsViewer.com/gpreports.html
blog: http://victoriayudin.com

jcw12000 (TechnicalUser) (OP)
18 May 12 1:44
I do understand that. Please see attachment. How is this possible. I see the data in the tables 100% but when this historical Remittance was printed the credit note is on it.

jcw12000 (TechnicalUser) (OP)
18 May 12 1:45
I forgot to add. The 3 transaction that has marks next to it is the transactions in question
VictoriaYudin (IS/IT--Management)
18 May 12 1:55
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:
http://www.GPReportsViewer.com/gpreports.html
blog: http://victoriayudin.com

jcw12000 (TechnicalUser) (OP)
18 May 12 2:00
I dont know what report is used. This is from the accounts dept. What data out of the tables would you need?
VictoriaYudin (IS/IT--Management)
18 May 12 2:08
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:
http://www.GPReportsViewer.com/gpreports.html
blog: http://victoriayudin.com

jcw12000 (TechnicalUser) (OP)
18 May 12 2:18
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?
VictoriaYudin (IS/IT--Management)
18 May 12 4:30
I don't recognize the name, sorry. I suspect this may be a custom report, unless it is from a module I do not typically use.

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:
http://www.GPReportsViewer.com/gpreports.html
blog: http://victoriayudin.com

jcw12000 (TechnicalUser) (OP)
18 May 12 4:35
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
jcw12000 (TechnicalUser) (OP)
24 May 12 5:06
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?
VictoriaYudin (IS/IT--Management)
24 May 12 6:06
From http://victoriayudin.com/gp-tables/pm-tables/:

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:
http://www.GPReportsViewer.com/gpreports.html
blog: http://victoriayudin.com

jcw12000 (TechnicalUser) (OP)
25 May 12 2:20
What data is stored in PM80500?
VictoriaYudin (IS/IT--Management)
25 May 12 4:08
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:
http://www.GPReportsViewer.com/gpreports.html
blog: http://victoriayudin.com

jpickcom (TechnicalUser)
16 Oct 12 12:35
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.
jpickcom (TechnicalUser)
16 Oct 12 16:03
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
www.jpick.com

VictoriaYudin (IS/IT--Management)
17 Oct 12 8:54
John,

In general, using the DEX_ROW_ID for anything is not recommended. Here are two good references for this:
http://dynamicsgpblogster.blogspot.com/2008/12/dex...
http://blogs.msdn.com/b/developingfordynamicsgp/ar...

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:
http://www.GPReportsViewer.com/gpreports.html
blog: http://victoriayudin.com

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close