• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 944
  • Last Modified:

Dynamics GP 10, View Credit & Debit to GL Account by PO

Is there a way we can link credit and debit transactions on one GL account number back to the PO? Basically we are looking for a way to see what POs had a different credited and debited amount to that GL #. Is that possible? I'm hearing from finance that in Great Plains 8 there was a way, and when we upgraded to Dynamics GP 10 we lost it.
0
mouseware
Asked:
mouseware
  • 3
  • 3
1 Solution
 
Victoria YudinCommented:
I can't think of a way to see this information 'out-of-the-box'.  And it would be very unusual for functionality to be taken away in a new release.  Is it possible that this was a customization you had for GP 8.0 that was not upgraded to 10.0?  Do the users remember if it was a screen (if so, how did they get to it?) or a report?  If you can find out what the customization was, perhaps it would be possible to get the original developer to upgrade it to GP 10.0 for you.

If none of that is possible or if no one remembers, you may need to create a custom report with this information.  
0
 
mousewareAuthor Commented:
Victoria, i actually just came across your website about a a month ago and I'd have it say, its fantastic and extremely helpful!

I'm not sure if this was a SQL view, access DB, report, smart list, etc... Basically Finance asked for a list of all POs that were entered that didn't match what came out of that GL #. Our old partner gave them the list of POs that didn't match, but that was it... I, and finance, had no clue where they got this list from, but it was correct and showed us what POs didn't match. Well, now we are trying to pick up the pieces, and after changing partners (so getting that past solution isn't going to be easy), we cannot seem to figure this one out.  

Loosing functionality is a touchy subject for us here because the move to 10 went smooth at first, then we found quite a few issues with loss of functionality...

However, it looks like i can see this by querying the POP30310 and GL30000 tables.

From POP30310 I can get the two Receiving/Receipt numbers. Then in the GL30000 table i can use those numbers to find the credit and debits and see if they match up.  The example below i can see that PO 4 matches, but PO 5 doesn't. This account is a wash account so they should always match, and when they don't, we need to tie that back to a PO and see why.

Thanks for your help!






/* matches */
select * from POP30310 where PONUMBER ='04' --returns RCT01 and RCT02
select * from GL30000 where ACTINDX='41' AND (ORCTRNUM='RCT01' OR ORCTRNUM='RCT012') -- for PO 04
 
/* does not match */
select * from POP30310 where PONUMBER ='05' --returns RCT03 and RCT04
select * from GL30000 where ACTINDX='41' AND (ORCTRNUM='RCT03' OR ORCTRNUM='RCT04') -- for PO 05

Open in new window

0
 
mousewareAuthor Commented:
Victoria, i actually just came across your website about a a month ago and I'd have it say, its fantastic and extremely helpful!

I'm not sure if this was a SQL view, access DB, report, smart list, etc... Basically Finance asked for a list of all POs that were entered that didn't match what came out of that GL #. Our old partner gave them the list of POs that didn't match, but that was it... I, and finance, had no clue where they got this list from, but it was correct and showed us what POs didn't match. Well, now we are trying to pick up the pieces, and after changing partners (so getting that past solution isn't going to be easy), we cannot seem to figure this one out.  

Loosing functionality is a touchy subject for us here because the move to 10 went smooth at first, then we found quite a few issues with loss of functionality...

However, it looks like i can see this by querying the POP30310 and GL30000 tables.

From POP30310 I can get the two Receiving/Receipt numbers. Then in the GL30000 table i can use those numbers to find the credit and debits and see if they match up.  The example below i can see that PO 4 matches, but PO 5 doesn't. This account is a wash account so they should always match, and when they don't, we need to tie that back to a PO and see why.

Thanks for your help!






/* matches */
select * from POP30310 where PONUMBER ='04' --returns RCT01 and RCT02
select * from GL30000 where ACTINDX='41' AND (ORCTRNUM='RCT01' OR ORCTRNUM='RCT012') -- for PO 04
 
/* does not match */
select * from POP30310 where PONUMBER ='05' --returns RCT03 and RCT04
select * from GL30000 where ACTINDX='41' AND (ORCTRNUM='RCT03' OR ORCTRNUM='RCT04') -- for PO 05

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Victoria YudinCommented:
I just threw this together, didn't do too much testing, but see if this saves you some time.  It might find a few false positives, like when you have a different mix of PO's on a Receipt vs. Invoice...but it should save you from having to do this one at a time.  Also, I made this a little more generic, so you could enter your own Account Number instead of having to use the account index.
SELECT 	PR.PONUMBER, sum(G.DEBITAMT) [DEBIT], sum(G.CRDTAMNT) [CREDIT],	
		sum(G.DEBITAMT) - sum(G.CRDTAMNT) [DIFFERENCE]
FROM 	
	(SELECT 	TRXDATE, ACTINDX, ORCTRNUM, DEBITAMT, CRDTAMNT, SERIES
	 FROM 	GL20000
	 	UNION
	 SELECT 	TRXDATE, ACTINDX, ORCTRNUM, DEBITAMT, CRDTAMNT, SERIES
	 FROM 	GL30000) G
 
	LEFT OUTER JOIN 
		GL00105 AN ON AN.ACTINDX = G.ACTINDX
	LEFT JOIN
		(SELECT DISTINCT PONUMBER, POPRCTNM
		 FROM POP10500) PR ON PR.POPRCTNM = G.ORCTRNUM
WHERE 	G.SERIES = 4
		AND PONUMBER <> ''
		AND AN.ACTNUMST = '2100-000-00'  --change this to your account #
GROUP BY PR.PONUMBER
HAVING sum(G.DEBITAMT) - sum(G.CRDTAMNT) <> 0

Open in new window

0
 
Victoria YudinCommented:
PS - thank you very much for the kind words about my blog!  
0
 
mousewareAuthor Commented:
oh wow! i'll give this a try today and see if that's what they need! Thanks for the quick reply!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now