So what we want to do is check every credit I booked to my Intercompany account for trading with you, has a debit in your Intercompany accounts in the same period. So our GL account balances should match and if they don't I need to find out where the differences are, which means drilldown on the balances. Or even better we do some matching of what we know is ok and show you the journals we couldn't match and you drill to just those ones to find the problem.
So we turned to XML Publisher to build some reports that we could embed in our UI. We took advantage of the XML Publisher OA Framework region and created a number of extracts with increasing degrees of detail. The top level shows the account balances for the trading partner pairs and any differences between the two.
To drill into the details of the balances or difference amounts I just click on the links and they direct you back to the OA Framework page calling the template and/or extract showing the next level of detail.
What's also neat is I can download any of these reports into Excel; sort them, order them cut and paste rows and maybe email them to somebody to investigate the differences. All the amounts in the excel spreadsheet also have the links so I just click on that link in excel to drill back into the applications and see the next level of detail.
We call this interactive reconciliation reporting and we think you might like it.
UPDATE: More details available in this post by Bidisha
Posts
20 comments:
For the more technical minded I will post again on this next week and give more detail on how we built it, with some code examples etc.
[...] have done a blog post about that and there is one due with more detail on how we did it technically with more details of [...]
[...] is a how to giving a little more detail on how the Interactive Financial reports David described here were put [...]
Dave,
This was my first visit to your blog and i'm quite impressed with the content.. I'm eagerly waiting on the next post where you said, you will share some technical details/code samples as to how to do the interactive reporting using BI Publisher and OA Framework..
Keep up the good work..
Shree !!
Shree,
Check out this post for more details.
http://davidhaimes.wordpress.com/2007/12/05/intercompany-reconciliation-reports-just-got-interactive/
Tim Dexter will also be following up with some more code on his Bipper Blog (http://blogs.oracle.com/xmlpublisher/)
David,
should it then always be a credit or can we also book a debit being the initiator (possibly resulting in a negative AR transaction and negative AP invoice)?
Dominique
You can enter a debit or credit, or a negative credit or negative debit. Just like a GL journal.
David,
Wonderful blog. I have a quick question if i may.... does the reconciliation data extract pick up invoices created by the 'Create Intercompany Invoices' processes in Inventory?
many thanks
Alastair
Hi Alastair,
Welcome to the blog and thanks for raising a very good question.
The reconciliation extract drives from the gl account balances of accounts defined in the Intercompany Accounts Set Up. It will pick up any activity at all that hits those accounts, be it an invoice from an AGIS transaction, an invoice from an Inventory Intercompany transaction or even a GL journal imported from a 3rd party system via the GL Journal Import.
The trick is to make sure whatever accounts those Inventory invoices are set up to hit, is also specified in the AGIS Intercompany accounts set up.
Hi,
I came accross this blog and it is fantastic. ....I just have a question though....How do you deal with intercompany transaction accross different entities with differency currencies. What has been my biggest headache when reconciling intercompany is the revaluation and tranlation of the different books before consolidation......
Intercodisater (interesting name)
Yes the fx transactions are the ones that can add complexity, as the example here demonstrates.
http://davidhaimes.wordpress.com/2007/12/06/what-exchange-rate-to-use-for-intercompany/
There are two options
1)Use corporate rates to book intercompany
2)Revalue INtercompany to a corporate rate before consolidation
When I say corporate rate, I mean one that will triangulate so 100 EUR -> USD will be the same amount as 100 EUR -> GBP -> USD. A corporate rate would not be the bank rate that has a hedge built in.
Dear David,
Could u pls suggest me how to process intercompany receipts In AR-R12. In the following case
Note: Inter company segment exist.
eg: client is having A and B company, and both companies having a common customer named Z, Mr.Z is having an out standing bal with A com Rs.10000 and B com Rs. 5000, now A co Received a receipt from Mr Z (Cust) for Rs.15000 to apply for both companies outstanding.
PLEAE CONSIDER THIS ONE AS URGENT
Anil,
Capitals are considered rude as they are akin to shouting.
Requesting me to give you instructions on how to implement your system (a task I assume you are being paid to perform) and then shouting at me that this is urgent is a little irritating to me.
Good site and interest topic of intercompany (appreciated):
Does R12 allow to have 2 balancing segments? I am from Oil Industry where Company and Owner codes are 2 balancing segments (different sharing for different fields). If R12 still has one balancing segments limitation, how ownercodes will be balanced? (I understand, Mass Allocation can take care of it by creating voucher of one figure i.e. owner code differences. However, this balace can not be tracked or drilled down). Any solution in mind ?
R12 is having lot of changes as compared with R11i. version. From where I can find "whats new in R12"? 2ndly, any reading stuff about Project Module? Thanks
Replying to Anil's query, if intercompany segment exists between Company A & B, then you can record the full collection in Company A, crediting Customer Z 10000 & 5000 for Co A & Co B. This entry will be generate intercompany of Rs 5,000 which you need to transfer to Co B. Transfer entry will also generate intercompany which will offset the 1st intercompany. Hope this will suffice you.
Imran
In R12 we still have the single balancing segment, I am very aware of the requirement to balance by more than one segment but we have not addressed it yet in R12.
The best palce to start learning new in R12 is the Release Content Document (RCD), this will give a product by product breakdown of what has changed. You can find the RCD on metalink.
How do you clear GL journal entries from the Cash Mgmt "Transactions Available for Recon Report" when a company moves from one set of books to multiple sets of books? In essence, no bank entry will occur however a book entry is needed in order to establish the cash account on the new set of books. These entries are hanging in limbo since there is nothing to reconcile against in the CM module.
Rodger
You should post this on the OTN forum, maybe somebody on there has experience with this. I'm not familiar with that particular report.
i want to know how to view a PDF report which is stored in unix through OAF.
Post a Comment