A reader asked a question on how to convert the invoices from the legacy system that are already paid by the customer but have deferred (unearned revenue) to be recognized in Oracle after conversion. Thought it makes sense write a post on this topic as the topic has enough to cover.

Basically here is the scenario. Say you have sold service or subscription for entire year in advance. Example is the yearly subscription price I pay in advance for my HBR copy. I pay about 144 USD in advance. What HBR does with this advance payment I make? Basically they had an invoice created on my name for that amount. But since the magazine needs to be delivered 12 months into the future, they have to recognize the revenue appropriately every month for each issue that is delivered. They cannot recognize the revenue in one month for all the144 USD. But I have already paid for it so they apply cash to this invoice thus bringing down the receivables to zero.

Imagine if HBSP was using a legacy system when I was a subscriber and would like to move to Oracle in the middle of my subscription. My subscription is from Jan-09 to Jan-10 and if they are migrating in Aug-09, how will this invoice converted?

In general this invoice is not considered as open because I have already paid for it (usually we convert open invoices only). So the invoice that is converted should have zero outstanding balance but outstanding revenue or in other words deferred or unearned revenue.

Why is this hard? The reason is accounting. Let us take the same example and create accounting entries for the same. In the first month when the invoice is created here is the entry.

Since the one issue of the magazine will be delivered in this month they need to recognize 12 USD in the month of Jan-09. Also mind that I am paying this invoice. Accounting entries are:

When cash is applied against the invoice:

When revenue is recognized:

And this recognition continues till August in the legacy system. In August when we convert the invoice into Oracle, how to convert this? Should we convert the entire balance of receivables and unearned revenue or only the remaining unearned revenue about should be the amount in the invoice to convert?

In general cases are:

1.     Invoice is completely paid, and unearned amount is greater than receivables amount (receivables is zero)

2.     Invoice is partially paid and remaining receivable balance is less than the remaining unearned amount.

3.     Invoice is partially paid and the open balance on the invoice is more than the unearned amount.

Two important things to keep in mind are: how much should be the invoice value in all these cases? This question is relevant because receivables balance is not the same as the original invoice amount in the legacy system. And the second question is which GL date to use? And we need an accounting rule to make this happen. As invoices in the legacy can have different number of periods to recognize revenue, we define this rule as Variable type? As we schedule the revenue as soon the revenue recognition is run, we do not check the deferred flag in the accounting rule.

You can convert the invoice amount as is from the legacy and then use adjustments to reduce the receivables balance to appropriate number(zero or partial).  As for the revenue use the rule start date as the equal to the transaction date (as it is in the source). If you keep only current period open gl date will be current for all the past revenue (when revenue recognition is run) and future date for the future periods as per the schedule till the end of it. The future revenue will be recognized on that appropriate date.

But again this falls into bigger picture of your migration strategy and depends on how you convert GL balances for these accounts.

This is extension to this article. In R11i all sub-ledgers by default use gl_interface to transfer the journal entries to GL.

But in R12, when SLA transfers entries to GL, it uses multi-table insert concept in Journal Import. In Multi-table concept you can create your own table dynamically using gl_journal_import_pkg.create table and use this as a mirror for gl_interface table. Then you insert this table name into gl_interface_control against the interface_run_id and group_id. When you submit Journal Import it looks at this table for rows to import rather than gl_interface table. You can find more information on the multi-table insert for journal import in General Ledger User guide.

So the idea of updating the gl_interface table discussed in this article in journal import does not work, as this table is not used at all. We need to slightly modify the script to update the dynamic table in order to effectively use the gl_import_hook_pkg in order to achieve the objective.

Here is the requirement. We need to update the journal header name for journals coming from AP. The standard way of naming the journal headers is concatenation of reference4, user_je_category_name and currency_code. This should be changed to something else to append time stamp to it. So there are two options: get the rows into gl_interface and have a custom program to update the reference4 to our choice or use gl_import_hook_pkg.

All we need to do in the first option is to set the profile SLA: Disable Journal Import to Yes. This will not submit the journal import program when running create accounting program. All it does is to insert records into the gl_interface table. Then you can have a custom program to update what you need to and then run Journal Import program.

The second option is dynamic which uses gl_import_hook_pkg. Here you set the profile value for SLA: Disable Journal Import as No (this is standard). As this process does not use gl_interface table, we have to update the dynamic table used in this process. Here is the script for the same. Modified gl_import_hook_pkg is here.

Just a quick one. Remember the colors we see when we view the requests we submitted as a concurrent request? Green when it is in pending or running status, Yellow for warning and Red for the errors? Do you know these colors are controlled by a profile called FND: Indicator Colors. If you se the value to Yes, you will these colors, if you say No, you will see no colors. Also I noticed, the prameters that are required while submiting the concurrent program usually show up in Yello color (standard for required fields). If you set this profile value to No, even they are becoming white.

Try it and feel the effect for yourself. I did not find any profile to set your own colors.

Have you ever needed something to be done after GL transfer from sources like Receivables, Payables or Inventory and before Journal Import is run? There are couple questions on the forums on this topic. Take a look at this one and also this requirement. While the first requirement is to do something before journal import is run and the second one is after before GL posting is done.

Usually we resort to solutions like creating a new program and sandwiching that program between GL transfer and Journal Import program. It works, but again, we need run the Gl interface program setting the value for the parameter Journal Import as ‘No’. This approach has inherent issues. What should we do if the custom program fails? Should we let the journal import run?  If it runs, the objective is failed. On the other hand if you want to stop the journal import from picking the rows, you need to manage the status in the interface table (one way of doing it). This gets complicated as you need to manage all the rows that go in as pair of accounting entries. On top of it the issue gets complicated based on summary posting versus detail posting. So let the journal import manage the business of balancing of accounting entries and let us do our part of managing the rows in the gl_interface table the way we want it.

So this post is to address the first one where the requirement is to update one of the references in gl_interface with the comments from the Miscellaneous Cash Receipts coming from Receivables. This update will happen after your transfer to GL from Receivables and before Journal Import is run. I can understand why this is a common requirement. Usually people who use GL as well as auditors require reasons why the miscellaneous cash was posted. Updating these reference columns makes those comments visible in journal entries screen. Just to warn you, this works only if you transfer from source in Detail and not in Summary mode. See the images of miscellaneous cash receipt and journal entries screen.

This is just an example on one use of this package. It can be used for any of your needs either be pre or post journal import program. You can take the sample code here and modified gl_import_hook_pkg here. Just make sure to compile only package body given in the gl_import_hook_pkg.

We know that if the item is serial number controlled, until R11i, we can only create one instance at a time in the installed base UI. This is because the UI automatically makes the quantity field read only with the quantity 1 placed in there. Take a look at this screen shot for example.

Take a look at this R12 screen shot. Though the item is serial number controlled, now you can create multiple serial numbers at the the same time. But the condition is that all these serial numbers share the same attributes like ownership, location and so on.

This post is one possible solution for this question in this thread in the forums. The requirement was to auto-create deliveries across the sales orders and launch pick release for the same. When pick releasing is completed, it should create one picking batch (move order) for all these deliveries.

Here are the steps to follow to test the script.

1. Create an item with and assign it to an organization (M1)
2. Create inventory for the same by receiving quantity using Miscellaneous Receipt in inventory.
3. Add item to the price list.
4. Create four different sales orders with different ship to addresses for this item. 
5. Book the orders. Scheduling happens.
6. Now if you navigate to the shipping transactions screen you will see that delivery details are created with the status of Ready To Release for all these orders.
7. Take all the delivery details for each of these orders.
8. Pass them as parameter to the first script that is available here.
9. Once this script is completed, you should see the deliveries are created and assigned to these delivery details. Since all these orders have different ship to addresses, obviously different delivery ids are created.
10. Now use the second script to launch the pick release.Download the script here.
11. This will kick off a concurrent request for Pick Selection List Generation program.
12. Once request is finished successfully, you will see a same move order is created for all the lines.

Of course you can do the same using the screen.

After the step 6 from above use the steps below:

1. If your order numbers in range pass then in From order to To order in the shipping transaction screen and click Find button.
2. You should see four lines (since each order has one line only)
3. Keep the cursor on the first line
4. Hold the shift key
5. Mouse click on the last line
6. This will select all the lines (highlighted in blue)
7. From actions use Autocreate Deliveries and press Go
8. This will create deliveries for all the four lines (different delivery numbers)
9. Select all the lines again as described above
10. From actions Launch Pick Release and Go
11. This will kick off the Pick Selection List Generation program

Remember to review your Delivery Grouping Rules, Pick Releasing Rules, Pick Slip Grouping Rules (assigned to the pick releasing rules)  for the organization where you are going to auto-create deliveries and launch pick release. They play a huge role in this process.

Even if you do not run the first script, second script will call autocreate deliveries (done in the fist script) and then launch pick release automatically. Give it a try.

Please note that for the second script (to launch pick releasing) I had to use group API (wsh_interface_grp) as I could not find any public API for the same. If you find one and tested it successfully, please share here. But the for the first one I used public API wsh_delivery_details_pub.autocreate_deliveries.

Release 12.1 RCD (ML note number 561580.1) for CRM has this information about some great features that are either planned to be or will be released as part of R12.1.

Enhancements are around these three areas:

• Efficient and effective way of Compensation plan building
• First cut of fusion features by integrating Peoplesoft HRMS with Oracle Resource Manager
• Integration with BI Publisher to create PDF file for the plan for approvals.

Compensation Plan building process made easy

With this release plan administrators can do the following:

Plan Modeling

Modeling a compensation plan is a complex process as it has to keep the financial and sales performance objectives in mind.  The designed plans should motivate the salesforce and at the same time should be cost effective for the company. But it so happens that we will not be able to know how they will work until we actually deploy them and see the results. With this feature, we can create multiple scenario plans with the underlying plan components like elements and rules. You can test these scenarios with historical and forecasted transaction data. Once these scenarios yield desired results, approvals can be sought and copied into a plan in production. 

Scenarios building

This is a new interface where you can group scenario plans for comparision. This facilitates the comparison of plan results or compensation costs for an assigned role in the a specific scenario plan with other. Meaning how a salesrep will be compensated in one plan can be compared with other other plan in the same scenario. These scenarios can also be associated with different operating units. This is very effective.

Producing plan report

Once the plans are finalized based on scenarios, you can use XML publisher to produce a report of complete plan for review and approvals. There is an RTF template available that can be customized per needs of the business. What is not clear is whether these are approvals are online where the approvals can be captured in the database or offline where PDF files are bouncing back and forth in the emails.

In-line copying of Plan and components

The offshoot of the above these features is copying of plans or plan components to create new. Once those scenarios are finalized and approved, they can be copied into a final plan that can be used in production. In all the search pages of plan components (Plans, Plan elements, Formulas, Rate tables, Expressions, rate dimensions) a duplicate icon is introduced which allows you to one click copy. This will be a great time saver where clients want to keep the date based compensation policies that require us to create new plans on yearly basis.

Moving the plans from one database instance to another

This is a big relief. Now you can move all (Plans, Plan elements, Formulas, Rate tables, Expressions, rate dimensions)  between instances (except plans that are customized for a specific resource (s)). It uses the XML technology. I am sure this is part of the iSetup framework where you can export setups from source instance and import into the destination. This is not clearly mentioned (may be added to iSetup RCD), but I assume that is the direction.  Will have to test this when the software is released.

Scenario simulation

We can calculate commissions and bonses against the scenario plans which are seperate from actual plans that are used for compensation. Reports can also be run against the scenario plans.

Peoplesoft Integration

Peoplesoft Employees as Oracle Resources

With SOA technology (business events, JMS queues, and BPEL) now the employee that is created in Peoplesoft HRMS will be synchronized to be created as a resource in Oracle. Both initial conversion and incremental dataloading are supported. With this integration,  I am not sure whether the employee is also created in Oracle HRMS. If not the only question is how resource will be categorized in Oracle (employee or any other type). Obviously this has to be employee type in an environment where Peoplesoft HRMS and Oracle CRM foundation are working in harmony, otherwise it becomes data redundancy. But the security model in Oracle for users expects employee to be available in Oracle HRMS, unless that is changed too.This signals data model changes to resource manager functionality as well. But will have to wait and see.

With this synchronization, job codes in Peoplesoft become salesroles and Department tree in Peoplesoft will be used in sales group hierarchy definitions. It is assumed that position hierarchy in Peoplesoft is not used for OIC purposes in Oracle hence Department hierarchy is chosen to build sales comp hierarchy. Ideally an option could have been given between the two. But I am not sure how many companies actually use department hierarchy available in Peoplesoft as Sales Compensation hierarchy in Oracle. Nevertheless now we have a platform which can be extended per needs.

Several new business events

Once these resources are created and appropriate roles have been assingned based on the Job codes on Peoplesoft HRMS, several OIC  business events are triggered. Based on the jobs (roles in Oracle) the OIC business events will automatically assign plans, assign the resources to appropriate pay groups. Also several updates like terminations in Peoplesoft trigger ending of plan assignments and holding payment statuses. We can modify these event subscriptions to match the business needs. Need to test whether these events are fired only for the Peoplesoft integration or also with Oracle eBusiness suite environment.

Overall, these features are worth considering for upgrade to R12.

We all know Order to Cash process. We are also aware of RMA to credit process. But now you can extend that process further to refund the customer.

• The process steps are:
• Customer orders a product
• Product is shipped and invoice is sent.
• This invoice is paid by the customer
• Now customer finds an issue with the product and would like to return.
• RMA is created and product is returned (order or invoice can be referenced in RMA).
• Now customer can ask for Refund for the return or he can tell us to keep the money as on-account credit.

Up until recently the only option we had was creating on-account credit for RMAs and manage the process from there. But isn’t it nice to have an option where you can decide to keep it on-account or refund based on customer’s request? Yes the wish is granted. Now you can manage this process completely with standard functionality starting R12 (probably in 11.5.10 as well with a patch or it is already there in that version). Please take a look at the presentation (you can view in full screen is the screens are not visible).

You can either automate this process by with setup at batch source level in AR setup or you can manually perform refund transaction this using applications screen using Refund receivables activity. Refund is a sedeed recevables activity.

There are about 39 seeded Key flexfields (KFF) when I last checked 12.0.4 vision instance. For each of these KFFs we can create countless structures (depending the KFF) under respective applications. The beauty of these flexfields is that we can create a combination of values from a number of sources and yet store it in a single field.  We have a number of features around flexfileds like using value sets that are vitually source values from any where in apps, security rules that can impose security of usage of values by a specific person or responsibility, cross validation rules that will not let you create undesired combination of values, if the combination is not created yet and more.

But no matter what Oracle gives as standard feature, we still need more. This question from an unappreciative user of forums resulted in a good find. If you have any need to write your own logic to validate creation of a combination, Oracle gives you a pre-hook for all key flexfields.You can write all your validations that you need (apart from using above mentioned features) before eBusiness suite creates one. If your validations fail, you can stop the creation of that combination.

The package name is fnd_flex_plsql. It comes with no logic in the body, but just returns boolean value of Ture. Specifically as the gl_code_combinations table does not have standard “WHO” columns, this might come handy to solve that issue.

You can find more information in Oracle support portal (note 295208.1) about its usage and support information.

OK, I should have seen it coming, but I was too naive, kept my head down and was keeping my clients happy. When My Oracle Support was announced I was curious to see what was used behind the scenes for this to work so well. Did a little more research and was stunned.

There were signs all over the place.  I guess it started with this announcement from Oracle in 2006. More significant is this news item. Results of that announcement and the appointment of this ex CEO of adobe to Oracle’s board started showing up. The new social CRM applications are the evidence, if you do not believe what I am talking about. Take a look at these blog posts in ZDnet and Oracle. These applications are stunning and extremely interactive which makes sales people more “social” even when they are using applications (against the popular complaint I keep hearing from them saying they are spending more time in data entry rather than selling). In fact they are so seamless users may not even feel that they are using Oracle applications when interacting with their leads, prospects or customers. Yes, with these applications will let them do what they are supposed to do : sell more, connect with customers quickly but leaving capturing of data to application.

Also Tim Dexter, who needs no introduction and who chided me gently for aggregating Oracle blogs that prompted me to stop, also has these two posts on tree maps and more on tree maps.

The Adobe product in focus here is Adobe Flex. I am no expert in building these applications, but these examples are dazzling. Take a look at this sample application on configurator, which we all can correlate very easily with Oracle applications. If this concept is adapted into our Configurator product, imagine the user experience and satisfaction in using the application.  You can launch that application in that same page to see the results for yourself. You can also see more examples on calendar,  organization chart and custom gantt charts for planning purposes and many more in the same page. All these examples are extremely relevant from business applications perspective that can be part of Oracle eBusiness suite or any other application of Oracle.

I do not know when these apps are going to be available, but what does this mean for all of us who play different roles in implementing these applications for clients? If I am a developer, should I embrace these adobe technologies now to be ready for these Fusion applications? Let me know your experience and share any information that you have.

A kind friend (Vaishnav) created a custom google search for information on R12. Here is the link. Also found one for documentation specifically here.

Found this advertisement in Economist magazine. Innovative use of products! Let me know what you think.

I wanted to share with you this great initiative started by Shivmohan Purohit. He is offering training in Oracle eBusiness suite for absolutely free! Just in case you do not know, check the link here.

We struggle to make up some time to answer questions on forums and have our blogs going. But I must say that this move initiated by Shiv needs a sincere applause.

Good Job Shiv! Keep it up!

eBusiness suite implementation projects are complex, time consuming and require extreme planning. These projects vary in nature. Fresh implementations, rollout to new divisions, adding new products to the exiting portofolio, projects that are direct result of mergers and acquisitions or simply just enhancements to process and so on. Each project is unique from solution and industry perspective and has different needs of its own.

But whatever is the nature of the project we have different teams working towards a same goal (question of who is doing what). Each project goes through several stages: from gathering business requirements to production go live (question of when). In this process we go through a number of testing cycles to keep the progress in check.

These questions of who, what and when drives the demand for number instances of eBusiness suite and when they are needed. Typically we go through the following process (simplified) before we go live.

Typically we prepare the base instance at a desired application release level. We setup application parameters in this instance for basic out-of-the-box functionality testing.More the number of applications more the setups and complex are the dependendencies. In stage two we copy this instance for testing. This might lead to some patches identification. Usually we apply these patches to patch instance first then to functional instance. If the patch pass the test then it will be applied to the base instance so that the same issue will not recur again in the future cycles of testing.

All these setups have to performed manually. These setups are interdependent and need to be setup carefully as some of them are irreversible. Business Accelerators help in these setups to a great extent. You can find more information in the following links.

Oracleappsblog
Oracle’s Data Sheet
Shivmohan Purohit
Official Site for Accelerators (requires login)

During testing if we make any changes to the setups, we make sure all the changes that we have made in the testing instance are applied back to the gold (base) instance. Without iSetup we have to make sure to document these and apply them back to the gold (base instance). Mistakes can happen here too!

From here the project takes off. We copy this base instance for all different teams in the project for their needs. Development gets a copy to develop code for the conversions and extensions, DBA team gets one for patch testing, change management or system admin team for migration of code or security testing so on. Here is the issue. If the functional team decides to change any of the setups in their instance, they have to be made available for all the teams. Again without iSetup we have to make sure to go and apply the change to base instance and copy that instance all the instances again, or make change in all the instances. Both these options involve considerable amount of resource idling.

iSetup is set to change all that. Uma Prabhala has documented 10 good reasons as to why you should use iSetup. Striking among those are :

• Setup dependencies.Once setup manually, for the rest of the instances you do not have to remember the setup sequence again. The dependencies are taken care of in selection sets.
• Reporting the difference between setups among instances. Great feature to use when we want to check the changes made and migrate only what we need.
• Migrating incremental setups from one instance to another.
• Custom code migration by using custom selection sets
• Download the setups and tranform the data and upload the transformed data. Which means, if you have to change the name, you can download the data from source setups and change names and upload.
• And the FAQ document that you can find in this link tells you that it is absolutely free!

You can clearly see how isetup streamlines the process by comparing the figures with and without isetup.

Just go give an example, in a recent project, we had to collapse three operating units into one as the three different companies in the same line of business got merged.

There were three options :

• Leave the systems as they are and assign three responsibilities of three operating units to the users who are suppose to access information
• Create one more operating unit in the same production instance and convert all the existing three OUs data into newly created one.
• Create totally new instance,setup from scratch and convert what need from old instance.

We chose the third one for many reasons.

Hence we had to copy the setups one by one.If we had iSetup, this could have been painless as far as the setups are concerned (both instances were on same release, which is mandatory for iSetup to be used. It does not support upgrade, only moving the setups between instances at same patch level).

One more thought. Some more creative uses of this product include :

Conversions.The hardest part in conversions is to transfer the files from desktop to servers so that the loader programs can them up. By creating a custom selection set, you can upload the file from your desktop (say your open sales orders). The contents of this file will be loaded into a staging table using the interface method that is registered earlier (this uses the control file is called from the FNDLOAD to load the data from your data file). After it is loaded you can call custom program that either loads into interface tables or calls the Sales Order API to create orders. You can find the procedure on how to do that in user guide and some help in Reshma Shinde’s blog.

From to day to day operations perspective,you can even use this product to load say, AP invoices, from a spreadsheet to interface tables to create invoices. This is an alternative to the Web ADI.

From change management perspective, you can also migrate code from instance to another with some careful planning.

But I am sure this product has a lot of potential and is well on its way to revolutionize the way we are implementing eBusiness suite.

Read the story here.