Core Financial Modules

[TMeacham]

Hi, I'm trying to pull data out of our system for an audit. They have asked us for raw data. They basically want detail posting of ALL of our transactions and the GL accounts. I'm having an issue with the Inventory Key, this doesn't fill in on the SO, or AR Invoice History. I don't see it under the IM Transaction History file either. I've tried using custom office to add the field in, but it doesn't seem to work.

My workaround is using SQL to get the data, then take it and have it look up the GL account based on that Item/Product Line. What I am concerned about is trying to capture the Inventory Key at the time of update (Sale). The COGS and Sale Account fills in, but the Inventory Key does not.

Any suggestions?

Thanks

Tina

Forgot to state that I'm on 4.40

Example

Header Record

Transaction Date, Source Module, GL #, GL Descr, Invoice #, Inv Date, Customer #, Cust Name, Journal,  Item #, Item Descrip, Qty, Unit Cost/Price, Extension

Detail         

12/21/2009, SO, 120, AR, 123, 12/21/2009, 000, ABC Co, 00001, 123,,,,,20

12/21/2009, SO, 151, INVENTORY, 123, 12/21/2009, 000, ABC Co, 00001, 123, Widget, 2, $5, $ (10)

12/21/2009, SO, 601,COGS, 123, 12/21/2009, 000, ABC Co, 00001, 123, Widget, 2, $5, $10

12/21/2009, SO, 501, NET SALES, 123, 12/21/2009, 000, ABC Co, 00001, 123, Widget, 2, $10, $(20)

[TomTarget]

Odd that the inventory key does not fill in.

Couple of thoughts for you both with my MAS hat on and my auditor hat on.

MAS hat

#1  You don't mention how you are extracting the data.   Crystal / VI / other?  Both crystal and VI export can link to the product line file to get the inventory key assuming you aren't doing warehouse/division segment substitution.

Auditor hat

#1  Do you have more than one inventory G/L account.  If not,  obviously not a big deal because it would always be the same account.

#2  From the auditor's perspective, do they really care which inventory account is hit.  It's more likely to be important which revenue/cost of sale account is being hit.   Ask them and if they say they do care,  ask them why.  How might it impact their testing/decisions?  Many times over the years when I was auditing, I ended up asking the question as to why I was doing some procedure and suddenly realized that the procedure being done might not be accomplishing the intended goal.

#3  Do they really want the info taken from the a/r module,  or would a dump from the general ledger of all the debits and credits which can then be tracked back to selected invoices in the transactions selected be just as good?

[BigLouie]

You would need to do this in a round-about way and you might need to pull all data into Access and compile. First you need to link in Product Line table which will give you the GL account key which you would link to the Key in the GL_Account file to get the actual GL account number.

[TMeacham]

Thank you for your input. I'm pulling thru an ODBC connection. When linking additional tables it slows the download and sometimes just stalls.

I've asked if they can take the GL posting, but we are dealing with the government and the answer was NO, we want/need it this way. We have one Inventory account, but we do have items that don't post to inventory. I wasn't here when we initially set up, and so I need to go backwards to obtain the data. So the GL account at time of posting is imperative.

In answer to Big Louie: If I change the Product Line Inventory GL, then I don't capture the actual GL posting, it will pull data as of today. The AR History Lines, captures the GL accounts at the time of posting in detail which is what I need. Inventory Key looks like it is reserved for AR postings and we use SO for all of our Invoicing.

I'm thinking of working from the GL down and linking backwards?? So starting with the GL and then cross reference by Journal Number for each Invoice?

Tina  

[TomTarget]

If you have only ONE G/L inventory account,  write the logic into the export to substitute that account # in for INVENTORY items only. 

Miscellaneous charges and other things that don't hit inventory would not have an inventory account so you leave it blank for those.

If you have only one inventory account,  I'm must be missing the point as to why you are worried about what the inventory account is today vs historically.

The government?  Is this an IRS audit?   Recent auditors we have had asked for unbelievable information that made no sense at all.  Essentially we could have surrounded them with boxes of information that would have filled up the room and they would have had no possibility of looking at all the info.   As it was we ended up with about 30 boxes of stuff that they spent 1 hour looking at after having the storage company carted them all over from the warehouse..

[TMeacham]

Yes it's an IRS audit, they are requiring actual downloaded data in csv format so that they can input it into their system. Also we are a small part of a much larger corporation.

My concern is this, even though today I have one inventory account, doesn't mean tomorrow I might have added two or three or that they put in a wrong GL account in the product line maintenance (which they have everywhere). We are very diverse here and we really need at least two inventory accounts.  If I'm pulling off of the master file, if that master file changes mid-year how do I know which GL account these transactions went to? I need to capture all account keys at the time of posting to ensure integrity of the data. Saying it's in my master file that is Changeable without concern for already posted data doesn't work.

I've actually contacted a friend who is going to write a script to do that. He's writing it to obtain the key at the time of the Sales Order. As we couldn't find another viable solution to meet my needs.

Thanks for all your input and advice.

Tina