The Feature

When Oracle Asset Security by Book Doesn’t Work

This feature of the week is for those who operate in a multi-org environment with multiple Asset Books. Unlike other Oracle Financials modules (Payables, Receivables, Purchasing, Order Management or Projects), Oracle Assets is not operating unit specific. The way you separate your transactions is by defining the Asset Security by Book.

Summary of Setup Steps

Step 1: Define Asset Organizations.

As Fixed Assets Manager:

• Navigate to Setup > Security > Organization > Description.
• Query up the organization, for which you want to define the Asset Security. Typically, this could be your operating unit.
• Include classification ‘Asset Organization. Click ‘Other’.
• Associate the organization with the asset book. Now, this step is very peculiar. You want to find an existing asset book. But! If you ran an open query, you’d get all books, which were already associated with this organization. And if you were assigning the first book to this organization, you’d retrieve nothing.
• What you must do is submit a specific query, i.e. run it as View > Query by example > Enter, and enter the book name.
• Then you have to make a change, any change like changing the book description, in order to be able to save it. This is not a bug, it’s a feature! So be aware of it.
• Save you changes. Now your book is associated with the asset organization.

Step 2: Define Asset Security Profile

As Fixed Assets Manager:

• Navigate to Setup > Security > Security
• Define a profile, which you will later assign with user responsibilitities. The profile links together the asset book, asset organization and responsibility.
• Select Organization Hierarchy and Top Organization in it to be valid in this profile, or
• Include specific organizations, which will be valid in the profile.

Step 3: Assign FA Security Profiles

As System Administrator:

• Navigate to Profile > System.
• Enter the asset responsibility you want to assign to a specific asset book (FA security profile).
• Enter ‘FA Security Profile’ as profile.
• Enter ‘Your Asset Profile Name’ in the profile value.

Step 4: Validate Your Asset Security Setup

Log in with the responsibility, for which you just defined the Asset Security above. Navigate to Asset Workbench and select the book values. If you see more than the one book you expected, something is not right.>

When Asset Security Doesn’t Work

• Check the Organization and Organization Hierarchy Setup. The Security by Book feature enables you to designate an organizational hierarchy in which the assets of an organization can only be seen by either the organization or its parent.You may set up a number of organizations and include as many of them as you wish in the hierarchy. Make sure that your hierarchy is set up as you expect.
• Run the Security List Maintenance program. This program populates the LOV’s with the appropriate information. If you miss this important step, your LOV’s will not show the correct books that are available for each organization to access.
• Define Asset Security for all books. You will always see asset books, which are not associated with any security profile. If there is a book that already exists on the system and you have not assigned that book to a particular organization, all organizations will be able to see the book and its assets.

For more information, refer to the Oracle Assets User Guide, or this [old but good] white paper on the subject (Oracle Metalink sign-on required).

TUSC Acquired by Rolta

According to its website, the Broech Corporation, an IT consulting company known in the Oracle community as TUSC, has been acquired by Rolta, one of India’s leading IT organizations. Here is few statements from the news release:

Rolta, one of India’s leading IT companies, specializing in GIS/GeoSpatial Information Systems, Engineering Design, and Enterprise Information & Communications Technology, today announced the acquisition of Broech Corporation, doing business as “TUSC”, an IT Consulting Company specializing in ERP applications as well as Database and Business Intelligence solutions, based on Oracle technologies.

… The three founders of TUSC, Rich Niemiec, Joe Trezzo and Brad Brown, will continue to lead the company. Their leadership of various Oracle User Groups and participation in Oracle Technology advisory councils evidences their standing in the industry, duly recognized by awards, like the Ernst & Young ‘Entrepreneur of the Year’ award. With each having over 20 years of experience, this management team brings a wealth of experience to Rolta. As a wholly owned subsidiary of Rolta, TUSC will continue to lead its operations from headquarters in Chicago, USA…

…Mr. K. K. Singh, Chairman and CEO of the Rolta group, said, “We are extremely pleased with the acquisition of TUSC who shares our passion for excellence. This brings into the Rolta fold, a company that has strategic maturity, exceptional technology, commendable work ethic, established branding and a strong portfolio of high-end consulting services. We will now be able to provide Enterprise level solutions to our customers worldwide, thereby increasing the value for our stakeholders”…

You can read the full press release here. I have a special connection to TUSC, as I worked with them on a number of consulting assignments. Congratulations and good luck!

Sample Ad-Hoc Queries for Oracle Financials

Here is a compilation of several ad-hoc SQL queries I created over time to perform various analytical tasks withing Oracle Financials. I am by no means a SQL guru, I just know enough to keep myself out of trouble. These are simple queries, which at different occasions provided me with the information I needed. I am posting them here to find them again when I need them and to share them with those of you who may find them useful too.

Active Employees and Supervisors

Find a list of active employees and supervisors with their accounting detail. This is needed for instance when defining the AP approval limits for iExpenses.

Active Employees and Supervisors

AP Signing Limits with Employee Information

Extract AP Signing Limits with employee information.

AP Signing Limits with Employee Information

AR AutoInvoice

Analyze the AR Interface tables used by AutoInvoice.

AR AutoInvoice

Asset Categories and Lives

Get a listing of asset categories with the accounting and depreciation information.

Asset Categories and Lives

Asset Details Extracts

Extract asset detail information. Surprisingly, it is not easy to extract assets from Oracle Assets to convert them back to Oracle Assets.

Asset Details Extracts

Employee Email Address Query

Compare employee and user email information.

Employee Email Address Query

Employees and Users

Compare employee and user information. Find out which employees are not set up as users and vice versa.

Employees and Users

Employees with Default Accounting Information

Select employee information from the HR tables with the default accounting information.

Employees with Default Accounting Information

Employees, Organizations, Users and Responsibilities

Analyze by organization what users and responsibilities are set up for employees.

Employees, Organizations, Users and Responsibilities

Expense Report Template Details

Extract expense report template details.

Expense Report Template Details

Future-Dated Expense Report Items Query

Find future-dated expense report items entered incorrectly in the future. Prevent this problem by setting the respective OIE options.

Future-Dated Expense Report Items

HR Managers

Get a list of HR managers.

HR Managers

OIE Expense Report Import Query

Analyze project expense reports imported from iExpenses.

OIE Expense Reports

Update Asset Lives

Unfortunately, the Mass Additions process does not let you provide an asset life during the upload via FA_MASS_ADDITONS table. The life for converted assets always defaults to the life defined in the category. I believe this has changed in R12, but in prior release, you might need to use this script. As a workaround, we put the new life in ATTRIBUTE4 in FA_MASS_ADDTIONS, and updated FA_BOOKS table after the assets were created.

Update Asset Life

Update Email Preference

Update a default email preference.

Update Email Preference

8 Things You Don’t Need To Know

I am going to break my #1 rule to keep this blog strictly professional [breaking the rules actually feels pretty good!], and join a silly game of Blog Tag. The goal of this game is to share eight things you may not know about me and tag other Oracle bloggers to spread this movement. So, here we go:

• I was born and raised in Slovakia when it was still a part of Czechoslovakia. Like most Slovaks, I don’t like to be referred to as Czech. Not that there is anything wrong with it
• My favorite places in the world include Seattle, Prague, and Ziarska Dolina (Ziarska Valley in the Tatra Mountains), Crater Lake, Oregon Coast, Achorage, Scottsdale, New Orleans, Greenwich (England), Whistler, Austin, Bratislava, Salt Lake City and many other places in between. As you might have guessed, I love to travel.
• During college, I once bought a Greyhound student ticket and traveled by bus from Worchester, MA to Eugene, OR to “see the country”. It was an eye opening experience but, needless to say, there is no risk of repeating the trip anytime soon.
• My wife and I have been married for 15 years. We have two wonderful children [boy and girl]. Our son’s favorite game when he was little was putting his toys in boxes, which tells you we moved a lot back then.
• I try to be a very engaged parent. I love spending time with my family and our weekends are always very active and busy. I found being involved the only way to make it work and compensate for my frequent business travels.
• I enjoy any sport activities which include “cheating” the gravity - swimming, skiing, ice skating, windsurfing. However, my main ‘fix” is hiking up a mountain with a 360 degree view from the top.
• I regret I never learned to play any musical instrument [at least not yet]. My mother signed me up for piano lessons when I was ten, however, I secretly went to hockey practices instead… She was not very pleased when she found out. I played youth hockey for about ten years, and more recently, I coached hockey teams my son played on.
• I joined Oracle in 1994 as a translator. My job was to translate the meetings, documentation and software from English to Slovak. It was a major Oracle Financials and Manufacturing 10.4 implementation project in Bratislava, Slovakia with a very diverse international team. Within a couple of months of the project, I was offered a consultant position, and I never really did anything else since. In 1997, I got transferred to the US and moved my family to Seattle where we live now.

I now tag Michael Amstrong Smith, Jo Davis, Jason Howlett, Martin Millmore, Neeraj Garg, and Bob Anderson. The show must go on!

Oracle Intercompany Inside Out - Conversation with David Haimes from Oracle

David: Hi Marian

Marian: Hello there! Thank you for checking in. Are you ready to chat for a while?

Yes; I even closed down Twitter!

Happy new year! Hope you had wonderful holidays!

Thanks. I enjoyed the holidays and am looking forward to 2008.

Well, let me start with a traditional question and ask about your Oracle beginnings.

I joined Oracle UK in 1997 as an Apps Engineer. Oracle was and still is a very attractive employer in the UK because it has a significant amount of development there. I think back then working in a global team was maybe not so common but we worked as part of a team based in the UK and US building the Financials features. It was a very strong team; I was interviewed by Terrance Wampler, now VP of Strategy and I coached Tim Dexter, who at that time was a Financials engineer but is now of BI Publisher Fame. There is still a lot of application and middleware development in the UK. Many UK IT jobs are consulting or support and Oracle has one of the biggest UK development centers I am aware of.

I went to Bracknell a couple of times while working for Oracle Central Europe in mid 90s…

I started in the Bracknell ‘Ring’ Office…

I understand you live in SFO now. What are you currently responsible at Oracle?

I moved to San Francisco in early 2000. Oracle was kind enough to transfer me. I am currently responsible for the development of a couple of Financials features/products, specifically Legal Entity and Intercompany. They are part of the Financials architecture, so I get good insight into other products and work with many other development teams, which I enjoy.

I understand the legal entity and intercompany underwent pretty significant changes in R12, isn’t it right?

Yes, they were pulled out and each made into their own product in R12. Intercompany, or Advanced Global Intercompany System (AGIS), takes some features from GL in 11i and builds them out into a pretty nice product. Legal Entity was really only implied in Financials in 11i, now it is very explicit and we can use that to drive tax calculation and reporting (via eTax) and fiscal reporting, which are both very big deals for our customers.

OK, let’s pretend I am a typical R11i customer using the intra-company segment in GL. What are the key advantages of upgrading to the new R12 functionality?

So let’s assume you are in a single set of books and the balancing segments value (BSV aka Company Code) represents your LE. I am leaving aside Legal Entity for now and talking about Intercompany.

OK.

In 11i, if your transactions in the sub-ledger are out of balance by BSV, they can be balanced automatically when they hit the GL, but that makes it pretty difficult to reconcile Intercompany as the balancing only happens after transfer to GL (which might be summarized) and posting. So you really don’t know what transactions generated those intercompany journal lines you see in GL.

In R12 you get balancing of your transactions at the transaction level, when SLA accounts them. So I can go into AP for example look at a payables invoice and see the Intercompany accounting lines right there on the invoice, as you can imagine that will make reconciliation of my Intercompany somewhat easier.

That is something that comes ‘for free’ out of the box, you need no extra set up etc. We also built a very nice BI Publisher based online reporting tool. It looks at the GL balances in your Intercompany accounts and right there in the UI you can click on the balances and drill all the way to the transactions.

I am sure many of my friends who are responsible for reconciling GL to sub-ledgers will be thrilled to hear that.

I 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 the reports… When I did a demo of this functionality to some Oracle sales consultants and partners, I got a round of applause that felt pretty good. I never had that before during a demo…

Good for you! Let me get to your blog a little later. How about the Legal Entity? As far as I know, that concept was only present in HR organizations when you defined legal entities and operating units. What are the main functionality differences in R12?

In 11i, the Legal Entity was only defined in HR organizations, and many times in Financials, that Legal entity was entered as a dummy value. There were a lot of other disparate places, such as in set up for globalization features where more detailed Legal Information was stored.

What we have done in R12 is pull the Operating Unit and Legal Entity apart, so you can have many Legal Entities in a single operating unit if you choose. You can create your LE in an all new user interface, which is part of the Accounting Set Up Manager. You can also define where these LE are registered, maybe for a number of different state taxes, etc., which is going to drive calculation of taxes.

When you enter a transaction you can pick the owning LE of that transaction (or default it based on a set of rules rules) and the owning LE will determine the sales tax due, maybe the accounting and certainly the fiscal reports the transaction appears in. What we also do is allow you to map these real world Legal Entities to an accounting structure, such as my Ledgers and BSV, which will drive Intercompany

It sounds like the legal entity functionality is much more robust and flexible in R12, comparing to its “quasi” deployment in R11i. The Subledger Accounting, Legal Entity Configurator, Advanced Global Intercompany System, and E-Business Tax are all new products in R12. Could you please describe the process of how these new products got incepted? I am mostly curious about when and how the input from users and partners was incorporated…

This goes back a long time in history, Steve Miranda started these many years ago after a lot of discussion with customers. I was first involved in Intercompany when we had a Focus Group in 2003. We got some interested customers on board and asked them about what they were looking for, what problems we needed to solve, there were Focus groups for the other initiatives too.

To give you an idea how much work went into this, we had the focus group come and test Intercompany alpha in spring 2004. They came for a week, looked at what we had so far and we got input on what we needed to change and priorities for new features. So a week of in depth on site working with customers gave us a good idea of the problems they face. We have also pushed out our designs to the Focus and had web conferences or live meetings on a regular basis.

I always find it ironic when Oracle asks customers not to customize the applications. We all hate the customizations but are often forced to do them when standard functionality is either not available or is not good enough. People planning their implementation projects are often in an information vacuum because the new functionality is often not known until a new release is actually publicaly released. The scope of R12 finalized few years ago, but only last year people actually started figuring out what is in it and why it would be beneficial to upgrade. What would be your advice to people who want to get involved in the design of the next Oracle release, i.e. Fusion?

Firstly, we don’t say do not customize, we provide a number of pretty neat ways for you to customize. The key is not to be invasive. Use flexfields, custom library, business events and workflows etc. It is fair to say though that we cannot always publicly detail features, for legal and other reasons. Also, we have a number of customer programs, advisory boards and Focus groups run by our strategy organization that [customers] can request to join. I’m not sure of the exact criteria to join, but I know one is active participation. So get in touch with Oracle Strategy about that. I believe they also have a blog. The benefit of this program is you will sign all the non disclosure agreements, so there is more that can be shared.

All new R12 products we mentioned here today are documented in the R12 Financials Content Documents, User Guides and other sources. Where can users find out more about these products and features and gain best possible knowledge to implement and use them?

These days there are more touch points with Oracle Development than ever before. I encourage feedback and questions on my blog and I have taken some of those discussions offline if there was information about the business that the poster did not want to post publicly. The reason I started the blog was that even though the Oracle Open World is a great way to come and talk to the people who built the product, it happens just once a year.

That’s where I was going with my next question…

There are a lot more bloggers now at Oracle and a lot of development and strategy people are on Oracle Mix. I think we need to do a better job of letting the Oracle community know where to find us. Jake and the AppsLab team are doing a great job, but we still have some way to go.

I guess the main message to the community is: be pro-active and get involved… What are your plans for your Intercompany blog in 2008?

I wasn’t really sure where it was going when I started it just after the OOW. I wanted to see the response. In 2008 I want to take some time to write detailed posts with explanations of new features in R12, maybe with more depth or color than in some of the documentation available. I’ll be starting with Intercompany, LE and might move on to GL and SLA and other products as I get time. That is one part. If I can get a repository of useful posts up there and give readers a chance to ask questions and add comments and opinions then that alone I will see as a success.

And THANK YOU for that. For a product manager, a blog seems like a great way to have an ongoing discussion with your user community.

I’ll keep posting on technology, Oracle news and general items too, but I won’t be offended if people skip those ones. I agree - blogs are very powerful, I would point to Tim Dexter’s BI Publisher blog as a good example of this.

Good luck with all your good intentions. It seems like all bloggers reach some kind of blogging fatigue at some point… You have already mentioned some blogs today, what are your other favorite blogs in your reader?

Thank you. One thing I have enjoyed since starting my blog is reading the great stuff written on blogs such as yours. There is a lot of great information out there that I honestly was not aware of. So this is a pat on the back to all Oracle application bloggers out there. Check out the blogs I listed on my blogroll and also look at who I follow on Twitter to get an idea of what I am reading at the moment.

To be honest, I still feel like the blogs are only followed by small community of “geeks” who read and comment on each others’ posts. Others stumble upon them only when searching for something specific… But that’s all right. It is a move forward from newsletters received by email.

I think the community is growing and there are a lot of lurkers out there who read and never comment. People like to stay current.

Well, that’s all questions I had. What else would you like to talk about? We did not mention Mix or your SIG much…

Oracle Mix is a great opportunity for me to learn and hopefully for customers to get better information and influence direction. Right now, I don’t think anyone is sure how it will work out, but that’s also very exciting for me.

Thank you very much David.

It’s been great chatting with you!

Same here. Thank you for your time and best of luck to you in 2008, especially with those two small babies at home you mentioned in your post…