Solved

How to identify an applied document that has no Document Number in Microsoft Dynamics GP

Posted on 2011-09-21
37
1,251 Views
Last Modified: 2012-05-12
We had a transaction lost (or partially) when the workstation went down and the transaction couldn't finish posting; it was a credit memo being applied to an invoice of a Vendor.

We ran Check Links and Reconcile 3 times, and still the same problem.

The problem is that when we look-up the applied credit in the vendor's window (Inquiry >> Purchases >> Transaction by Vendor) the credit memo is displayed in the Invoice applied documents, but no Document Number is found.

To be exact:

   1. We go to Inquiry >> Purchases >> Transaction by Vendor
   2. Select the Vendor
   3. Select the invoice that was paid
   4. Click on Document Number link above to display the Invoice
   5. Then click on the Apply button and all applied docs is display
       (the Invoice has various payments and one credit memo)
   7. The Credit Memo appears only with the correct Amount
       and 'Voucher/Payment Number', but no 'Document Number'

When we look-up the credit memo in GP and is not found.

We searched the PM tables and compared a good finished posted transaction and the interrupted transaction (credit memo) and found that on the finished posted transaction, the transaction are found on more tables than the interrupted one.

This leads us to believe that we must create manually the transaction in the tables that is not present.

Please advice on how to go about to identify the problem correctly and possible fixes.

Thanx
0
Comment
Question by:rayluvs
  • 20
  • 16
37 Comments
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36572699
I suggest you calling your MS Partner to help you in this, it's not an easy issue and access to the DB will be required.
0
 

Author Comment

by:rayluvs
ID: 36573815
Understood, sometimes these types of problems is not easy, but we have fixed issues similar issues in the past.  We need is a guidance to where to look.

Can you gives your opinion?
0
 

Author Comment

by:rayluvs
ID: 36593324
At least tell us where to look.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36594030
Too many tables are involved in this, GL and POP tables. If it's not in functional currency, more tables are affected.
0
 

Author Comment

by:rayluvs
ID: 36594220
Hi Abdulmalek_Hamsho we have all PM and GL tables involved in a the transactions (we've just finished identifying them).  POP tables is not involved since the transaction window is in PM transactions.  We've had a similar situation with RM and the way we solved it was creating the row in the applied table (we don't have the name at hand because the programmer resigned).  

The problem we have with PM is somewhat different because the row has no Document Number; that is the Credit Memo applied to the invoice only has the correct Voucher Number and the correct amount.  The rest of the Credit memo info is not present, which is Document number, Transaction Dates, etc. It's like it's been partially saved and somehow interrupted.

And the problem that makes this more complex is that the Credit Memo doesn't exist.

We don't want you guys to solve our situation, but just guide us with your experience with these types of problems.

Thanx

0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36595107
Glad to hear this. I'm sorry, I used to refer to PM & POP as POP and RM & SOP as SOP.
0
 

Author Comment

by:rayluvs
ID: 36595648
The problem is not solved.  Can yiu guide us as to where to look for this problem?
0
 
LVL 10

Accepted Solution

by:
Abdulmalek_Hamsho earned 500 total points
ID: 36596227
I don't really know what is your situation now, and most probably you won't be able to tell me your situation. If it's only one credit memo, then delete it from every where (using SQL) and recreate it in GP. You can use SQL statements to search for the Credit memo No in the tables and remove the related rows, then Check Links and Reconcile the PM.

Here's an SQL Statement you can use to search for a value in a DB (I got it form the Internet):

{

 Declare @TN as varchar(200), @CN as varchar(200), @myValue varchar(30), @SQL as nvarchar(1000)
, @SN as varchar(200), @Exact_Match bit
 
Create Table #myTable (Table_Name varchar(200), Column_Name varchar(200), Number_Of_Rows int)
 
-- Replace @myValue with the value you're searching for in the database
Set @myValue = 'CMNo'
-- 0 for LIKE match, 1 for exact match
Set @Exact_Match = 0    
 
Declare myCursor Cursor For
Select T.Table_Name, C.Column_Name, T.Table_Schema
From INFORMATION_SCHEMA.TABLES T Inner Join INFORMATION_SCHEMA.COLUMNS C
On T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name
Where T.Table_Name <> 'dtproperties' And Table_Type = 'Base Table'
And C.Data_Type In ('varchar','char','nvarchar','nchar','sql_variant')
--And C.Data_Type In ('text','ntext')
--And C.Data_Type In ('tinyint','int','bigint','numeric','decimal','money','float','smallint','real','smallmoney')
--And C.Data_Type In ('datetime','dmalldatetime')
-- Fields not searched: image, uniqueidentifier, bit, varbinary, binary, timestamp
Open myCursor
Fetch Next From myCursor Into @TN, @CN, @SN
While @@Fetch_Status <> -1
Begin
      If @Exact_Match = 0
            Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] Like ''%' + @myValue + '%'''
          Else
            Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] = ''' + @myValue + ''''
      --Print @SQL
      Exec sp_executesql @SQL
      Fetch Next From myCursor Into @TN, @CN, @SN
End
Close myCursor
Deallocate myCursor
Select * From #myTable Where Number_Of_Rows > 0 Order By Table_Name
Drop Table #myTable
 
}

One more thing, in RM the RM Doc No is the identity of the transaction, while in PM the Vendor Doc No is the identity of the transaction (in addition to some other attributes). So you need to trace the Vendor Doc Number in the DB and remove the related rows.

It's not as simple as it seems, but this is the best I can do.
0
 

Author Comment

by:rayluvs
ID: 36596368
Thank you very much!  This is what we needed a guidance to where to look.  
0
 

Author Comment

by:rayluvs
ID: 36717470


We have traced the voucher number and the tables when: open, work and history, also the columns for status and amouns.  We successfully unapplied the bad row and set the invoice to the status prior the problem.

The following are the tables affected when posting an Invoice and a Credit Memo, also the tables affected when doing an apply:

      (note: if there something missing, please
       advice)

These are the tables affected when posting an open invoice:

   dbo.GL10001
   dbo.PM00201
   dbo.PM00400
   dbo.PM20000

These are the tables affected when posting Open Credit Memo:

   dbo.GL10001
   dbo.PM00400
   dbo.PM20000

Finally, the following are the tables-movement when doing an APPLY of a Credit Memo to a Open Invoice:

  For the Invoice:
   dbo.GL10000
   dbo.GL10001
   dbo.PM00201
   dbo.PM00400
   dbo.PM10100
   dbo.PM10200
   dbo.PM20000
   dbo.PM20100
   dbo.PM30300
   dbo.PM80600

  For the Credit Memo:
   dbo.GL10000
   dbo.GL10001
   dbo.GL10001
   dbo.PM00400
   dbo.PM00400
   dbo.PM10200
   dbo.PM10200
   dbo.PM20100
   dbo.PM20100
   dbo.PM30200
   dbo.PM30200
   dbo.PM30300
   dbo.PM30300
   dbo.PM30600
   dbo.PM80600
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36717624
Was the CM in the functional currency?

Nothing in GL20000?
0
 

Author Comment

by:rayluvs
ID: 36718464
Can you be more specific on both question?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36720504
Was the credit memo that you had created in functional currency (the default currency of the GP system)?

You did not find any related rows in table GL20000?
0
 

Assisted Solution

by:rayluvs
rayluvs earned 0 total points
ID: 36896932
Yes it was the functional currency.

FYI: we did solved the problem:

First the list of tables we gave before were from troubloshooting in GP10 and the production table is GP9 (so it was wrong), so pay no mind to those tables a top.

Nevertheless, here is how we identified the tables involved in a Credit posting/application and subsequently, fixed the problem:

First, we search the entire DB with the problem of the lost Credit Memo; found only in the following tables:

      Tables              Colum      
      dbo.AAG20000      DOCNUMBR
      dbo.PM30300      VCHRNMBR
      dbo.PM30300      APFRDCNM

Second, we went thru the tedious process of posting and leaving OPEN an INVOICE and CREDIT MEMO (didn't apply the credit).  The following were the tables affected (this helped us identify an applied document that has no Document Number ):


      POSTED ONLY INVOICE (OPEN ONLY):            
      ---------------------------------------------------------------------------------------------------------------------            
      Table                 Column         Appear
      dbo.AAG20000      DOCNUMBR      1
      dbo.GL10000      DTAControlNum      1
      dbo.GL10001      ORCTRNUM      2
      dbo.GL10001      ORDOCNUM      2
      dbo.PM00201      LSTINNUM      1
      dbo.PM00400      CNTRLNUM      1
      dbo.PM00400      DOCNUMBR      1
      dbo.PM10100      VCHRNMBR      2
      dbo.PM20000      DOCNUMBR      1
      dbo.PM20000      VCHRNMBR      1
      dbo.PM80600      VCHRNMBR      2
                  
                  
      POSTED ONLY CREDIT (OPEN ONLY):            
      ---------------------------------------------------------------------------------------------------------------------            
      Table                 Column         Appear
      dbo.GL20000      ORDOCNUM      2
      dbo.PM00400      DOCNUMBR      1
      dbo.PM20000      DOCNUMBR      1
                  
                  
      INVOICE AFTER PARTIAL APPLY OF CREDIT:            
      ---------------------------------------------------------------------------------------------------------------------            
      Table                 Column         Appear
      dbo.AAG20000      DOCNUMBR      1
      dbo.AG20000_BAK      DOCNUMBR      1
      dbo.AG20000_BAK2      DOCNUMBR      1
      dbo.GL10000      DTAControlNum      1
      dbo.GL10001      ORCTRNUM      2
      dbo.GL10001      ORDOCNUM      2
      dbo.PM00400      CNTRLNUM      1
      dbo.PM00400      DOCNUMBR      1
      dbo.PM10100      VCHRNMBR      2
      dbo.PM10200      APTODCNM      1
      dbo.PM10200      APTVCHNM      1
      dbo.PM20000      DOCNUMBR      1
      dbo.PM20000      VCHRNMBR      1
      dbo.PM20100      APTODCNM      1
      dbo.PM20100      APTVCHNM      1
      dbo.PM30300      APTODCNM      1
      dbo.PM30300      APTVCHNM      1
      dbo.PM80600      VCHRNMBR      2
                  
      CREDIT MEMO AFTER APPLY:            
      ---------------------------------------------------------------------------------------------------------------------            
      Table                 Column         Appear
      dbo.GL20000      ORDOCNUM      2
      dbo.PM00400      DOCNUMBR      1
      dbo.PM10200      APFRDCNM      1
      dbo.PM20100      APFRDCNM      1
      dbo.PM30200      DOCNUMBR      1
      dbo.PM30300      APFRDCNM      1

Finally, to fix the problem, we wanted to leave the transaction with the invoice and credit memo open so the accountant can just redo his transaction.

Next, we deleted the credit application:

      DELETE PM20100 WHERE APTODCNM = 'invoice' AND APFRDCNM = 'credit'
      DELETE PM30300 WHERE APTODCNM = 'invoice' AND APFRDCNM = 'credit'
      DELETE FROM PM30200 WHERE DOCNUMBR = 'invoice'


Then we inserted the invoice so it can be chosen again for application

      INSERT INTO PM20000 … statement & values


Created the credit memo again so it can be applied

      INSERT INTO PM20000 … statement & values

Like you said, "it's not an easy issue and access to the DB will be required" and "Too many tables are involved in this" (we worked on this for 2 straight days up to 3am), but like stated prior, we are willing to go thru the process.

Please view our process and comment on anything you think we should have done better (we seriously take in consideration all of EE recommendations and observation).

Thanx
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36897162
Have you run thw Check Links and Reconciliation for all series (specially, AA, GL, PM, POP)?
0
 

Author Comment

by:rayluvs
ID: 36897270
Yes, prior doing this fix, we ran Check Links and Reconciliation for all series
0
 

Author Comment

by:rayluvs
ID: 36897277
o wait, I havent run Check Links and Reconciliation for all series for AA.

Where do we Check Links and Reconciliation for all series AA?
0
 
LVL 10

Assisted Solution

by:Abdulmalek_Hamsho
Abdulmalek_Hamsho earned 500 total points
ID: 36897404
Check links is in Check Links window -> Financial (it's listed there "Analytical Accounting")

Unfortunately, there's no tool to reconcile AA in GP, but here's the link which helps you out in this:

http://msdynamicstips.com/2011/01/17/sql-script-to-reconcile-gl-vs-aa-balances-for-accounts-payable/

Check links for every series and reconcile PM and GL related.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rayluvs
ID: 36897444
Can't find the Checklink for AA (see pic)
Checlink.jpg
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36897544
Is this GP 9.0?

I don't think there's Checklinks for AA in GP 9.0. Just use the script I've posted above.
0
 

Author Comment

by:rayluvs
ID: 36897638
We have both, but I checked your recommendations on my notebook which is GP10 and at the office is GP9 (the pic is from GP10.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36897719
There must be something wron at your notebook, is AA installed and activated?
0
 

Author Comment

by:rayluvs
ID: 36897849
Just checked, I don't have Analytical Accounting install in my notebook.  But installed in the office.

Nevertheless, our problem has been resolve with the entry we did "10/01/11 08:32 AM, ID: 36896932" but we'll keep in mind the AA check-link.

Before closing & awarding, can you review our entry and have your observation?  That is, is it ok? We did it correctly? Any additional consideration?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36897876
Yeah, I reviewed the first time. It's logical and make sense.
0
 

Author Comment

by:rayluvs
ID: 36897893
Thank you very much, you have been very helpful
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36897923
With pleasure dear.
0
 

Author Comment

by:rayluvs
ID: 36912438
I've requested that this question be closed as follows:

Accepted answer: 250 points for Abdulmalek_Hamsho's comment http:/Q_27319343.html#36596227
Assisted answer: 0 points for Ramante's comment http:/Q_27319343.html#36896932
Assisted answer: 250 points for Abdulmalek_Hamsho's comment http:/Q_27319343.html#36897404

for the following reason:

Thanx
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36898108
Ramante, how are you closing this question? In the normal way?

Because EE are giving me the ability for objection.
0
 

Author Comment

by:rayluvs
ID: 36898132
yes normal way.  Beside selecting your comments I selected what we did as also the solution for any future user who have similar problem.

Maybe I've done it wrong?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36898139
Is allowed to repeat the closing?
0
 

Author Comment

by:rayluvs
ID: 36898203
I don't understand what id did wrong or what can i do to fix this????
0
 

Author Comment

by:rayluvs
ID: 36910490
I thought I closed this question but it still appear in my open questions.  

I have chosen Abdulmalek_Hamsho's comment #36596227, #36897404 and my entry #36896932 as the 3 entry's that defines the solution to this problem but it keeps appearing.

What should I do to close this question with the correct comments selected?

I am clicking OBJECT since its says at top "To cancel this request and generate a request in Community Support for Moderator review"
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36912439
Moderator, could you please help in colsing the question?
0
 

Author Comment

by:rayluvs
ID: 36914415
How do I close it, the same way?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36915741
Dear Operandi,

It seems you did not get the problem yet. Ramante is not able to close the question, it's not that I need more points or more allocations.

Malek.
0
 

Author Closing Comment

by:rayluvs
ID: 36960748
I've requested that this question be closed as follows:

Accepted answer: 250 points for Abdulmalek_Hamsho's comment http:/Q_27319343.html#36596227
Assisted answer: 0 points for Ramante's comment http:/Q_27319343.html#36896932
Assisted answer: 250 points for Abdulmalek_Hamsho's comment http:/Q_27319343.html#36897404

Note: we have also marked our comments (#36896932) as a solution since we described the how-to solved this problem.  With this said, a member with similar problem can take advantage of all our efforts to solve it without going through the tedious process themselves (which we think EE is all about, Available True Resources for members)

Thanx Abdulmalek_Hamsho, and EE!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

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

11 Experts available now in Live!

Get 1:1 Help Now