R12 Payables: AP events unprocessed but new header is created

Is the most important lesson from the article "R12:AP/XLA: Event Left Unprocessed and Creates a New Header Each Time Invoice is Attemped for Accounting" on MOS via document ID 2097911.1 that a custom object is at fault here?  No!  The most important lesson is the referenced documents to learn something more that might be able to be applied later when you have a problem with the AP or XLA modules!

R12 Assets: ORA-20001 and XLA-95103 errors on the Asset Workbench

Hoping to make a return to regular blog postings, tonight it might your time to read "R12 Asset Workbench Errors With ORA-20001 XLA-95103: An internal error occurred" from My Oracle Support note 1590451.1 to prepare your own alerts just in case the data matches this pattern of being in multiple books so you can get ahead of any issues!!

R12.1.3: Payables accounting date not in sync with General Ledger accounting date

I say a lot about bringing proactive data checks to Support teams, so tonight it would make a lot of sense to check out the My Oracle Support article "R12: Accounting Date And General Ledger Accounting Date Is Not In Sync" from note 1985068.1 because you will find out a setup issue that can cause a data mismatch between the EBS modules plus get a script which will allow you to identify if you are encountering this issue!

Weekend Learning: Introduction to technical details about SLA

While finishing up my presentation and whitepaper for COLLABORATE so I can present in April, I found the perfect article for Weekend Learning, saved a draft, and then promptly forgot to post it last night!  My consecutive posting streak is broken!  Oh well, I am going to post twice today and then know next year on February 29th that I have posted 366 days in a row!  Maybe it has something to do with my last post being the 666th post that was posted too!  Anyways, on with the show!

The MOS document "SLA: A TECHNICAL PERSPECTIVE OF THE AP TO GL RECONCILIATION" from note ID 605707.1 has a lot of good reading about how SubLedger Accounting (XLA) works with R12 so that the AP module no longer retains the accounting information.  Walk through the tables that retain all of the data, so that you can learn about the interconnections between modules that might need alerting!

R12: Basic information about the XLA_EVENTS table

With all of the changes that R12 has brought, XLA (Sub Ledger Accounting) is one of the biggest so it is no surprise that I am on the lookout for any new tidbits of information about it!  Tonight MOS document "R12: What is the Meaning of EVENT_STATUS_CODE and PROCESS_STATUS_CODE in XLA_EVENTS Table?" from article ID 1457611.1 gives us one more small glimpse into these XLA changes.  Might as well take stock of this information now so you have it when you need it some day!

R12: Account Analysis Report performance issue

A few months ago I shared a post about the Invoice Workbench slowing down due to XLA data volume and it appears that is not the only way XLA bloat negatively affects your system.  Per My Oracle Support article "R12 Account Analysis Report (XLAAARPT) Performance Problem" with note ID 1667405.1 it appears that with the amount of data here you need to change how the report runs by modifying the indexes a bit.

R12.1.3: Invoice Workbench freezes on saving

During our last A/P month end close we were pulled into an issue where the business was seeing the system take 5-10 minutes to save per invoice when they had to change site or payment information, and another 5-10 minutes to validate the invoice as well.  Obviously this made the system pretty much unusable for the individuals that had to perform these tasks as part of their core job functions, so we sat down and saw that the SQL_ID being passed each time was c9zkjtwxxu17t with an explain plan cost of the SQL over 40,000 GB.

Today we sat down with several individuals with the A/P team to try and brainstorm some ways to research and replicate this issue, when we found that the Invoice Workbench was freezing even if they were not trying to save or change supplier site information but one track we need to verify is if this affects only Validated invoices.  Enter my co-worker that did a search on My Oracle Support during the meeting and found the article "Performance problem in Invoice Workbench: It Takes Long Time When Changing The Supplier Site" under document ID 1572029.1 which has the patch required to fix this and details what the true cause of this issue is.

We also learned that this has existed ever since we went to R12.1.3, but over time it slowly began to take more and more time to save or validate.  Why is this?  Well, I am glad you asked!  The root cause is in XLA tables, which was introduced with R12 architecture and these tables keep growing the more business/conversion you do so if you have a bad query hitting a small amount of data it can perform "well enough" so the business does not mind the delay but if you take the same query and greatly increase the amount of data it runs again there goes your system performance.

R12: Incomplete Payables conversion from 11i

Were you aware that the data that existed in 11i within the AP_ENCUMBRANCE_LINES_ALL table was not upgraded into the new XLA tables?  I didn't know that either!  The MOS note "Enhancement Request: R12 Doe Not Show Upgraded Encumbrance Accounting Lines From Payables" from note ID 1673271.1 does reference R12.2 but since all versions of R12 do go through the XLA conversions I believe it would apply to all versions!  Food for thought if you are going through the upgrade process, or something to keep in mind if you have already upgraded just in case the business did not catch this in testing originally and suddenly realizes it after implementation.

R12: Accounting Program/Create Accounting reports stall

At 7 AM our Submit Accounting and Unposted Items Report that should have run hours earlier were just sitting in the Concurrent Manager in a Pending status.  I found that this was due to an incompatibility between these reports, the Accounting Program and Create Accounting reports but they started at 8 PM so I had to solve the puzzle for why they were still running.  What challenges did I face while trying to verify this?

• The Accounting Program and Create Accounting reports did not actually create any entries in the log files so I couldn't tell that they were alive at all.
• Even further, I tried finding these alive in gv$session but I couldn't find anything by ACTION or MODULE.
• Our monitoring that constantly runs was reporting the Create Accounting report had a particular SID/SERIAL# combination but looking at that SID in gv$session actually saw different sessions coming in and out which were not our programs.

When a co-worker came in, I was at my wit's end, and I asked him to take a look at this.  Imagine my surprise when he tells me the Accounting Program and Create Accounting reports had been completed!  What did I learn from this?  The reports don't actually log their activity while they are alive and running, until they complete so it is easy to think they are dead!  I'll be opening up an SR to see if bug 17662654 relates to this problem, and if so I'll have to get the bug reopened as it has been closed.  If this isn't related, then I'll need to research this more locally when I get a chance.

A few other items I found on MOS that may be related are as follows: 1378432.1, 1463805.1, patch 14013094, 1465689.1, 791049.1, patch 16060007.

Newsworthy A/R Articles

Continuing the spotlight on the EBS Support blog, I wanted to focus on several articles related to different modules and tonight is the Accounts Receivable module.  Have you ever encountered problems with the Autoinvoice?  If so, this article is for you!  Even if you've never had an issue with the Autoinvoice, the article has a link to the Oracle Receivables User Guide which may have some interesting information in it for you.

Have you heard of the Trading Community Architecture?  The TCA is the data model for the relationship between Parties and Customers so it should be interesting to read an article about the way it is changing with R12 right?  That's what I thought too!  Plus it has more links to help you understand the Customer Form in R12, what troubleshooting methods are in R12, and some common known issues too.

Last, but not least, is a compilation of notes for A/R ranging from XLA, to Create Accounting, to receipt application, to closing periods and duplicating Receipt Numbers!  Noteworthy notes indeed!