Concurrent Request Output Issue

Sunddenly,in my machine I was not able to open any of the concurrent request's output.
When I clicked on View output,the page tried to open the excel sheet ( which is my output type ), then it closed by itself.

Then I found the below solution to make it work.

1. Tool--> Internet Options --> Security --> Custom Level --> Dowloads --> Automatic prompting for file downloads --> Enable
2. Close the browser.
3. Open a new browser.
4. Retest the issue.

Looks like above is the mandatory setting for a file to get downloaded from concurrent request's output.

OIC - Query to get total transaction & commission amount

Below query can be used to get the commission amount for the given period for a salesperson.

select sum(CCL.commission_amount) from cn_commission_headers_all CHR,
cn_commission_lines_all CCL
where CHR.commission_header_id = CCL.commission_header_id and
CCL.credited_salesrep_id = &Salesperson_Id and
CCL.processed_period_id = &Period_Id and
CHR.status like 'ROLL' and
CCL.status like 'CALC'

Below query can be used to get the total transaction amount for the given period for a salesperson.

select sum(CHR.transaction_amount) from cn_commission_headers_all CHR,
cn_commission_lines_all CCL
where CHR.commission_header_id = CCL.commission_header_id and
CCL.credited_salesrep_id = &Salesperson_Id and
CCL.processed_period_id = &Period_Id and
CHR.status like 'ROLL' and
CCL.status like 'CALC'

&Salesperson_Id - Can be taken from JTF_RS_SALESREPS table
&Period_Id - e.g., 2010001 for Jan 2010, 2009012 for Dec 2009

Oracle Incentive Compensation - CMs roll up

Issue :

There is an invoice for the month of Jan 2009 and a CM for that invoice is created for the month of Jul 2010.Client has implemented OIC in 2010 and started using OIC from Jan 2010 to calculate compensation.

While performing the calculation for the month of JUL 2010, the credit memos are not getting rolled up to the respective Managers.

Cause :

When a Credit Memo is included in the calculation, it tries to get the manager's name with the invoice number (invoice number is taken by the system by having Credit Memo as reference ) from the Credited Salesrep id column in the CN tables.

But in this case, the original invoice is dated on Jan 2009, as client started using the OIC from jan 2010,they would not have done the calculation for the year 2009 hence the system is not able to identify the respective manager's id to do the roll up.

Solution / Workaround :

A dummy calculation can be performed for the year 2009. so that the respective CN table will have the managers details (credited salesrep id column) for the invoice, further there will be no issues in doing the roll up for Credit memos.


Take away :

It is a good practice for any client to do the collection and calculation ( can be dummy) for the year before they plan to go live.

Oracle iStore - a study [ PART ONE ]

I am planning to write about the module the most I loved to work on, I will try to provide more details about this module as much as I know.
First let us start with the very basics of iStore, i.e what exactly it means? What really it is? What does it do?

Here you go…

Introduction – Part 1

Oracle iStore is nothing but an ecommerce module in Oracle Applications (mainly under CRM). It is the simplest module to understand functionality wise. If you used any of the ecomm sites like ebay, amazon then you have almost studied what iStore is.
In Oracle Apps term, Oracle iStore is an Order capture module which has the ability to handle B2B, B2C and walk in users. To know more about this type of users please refer here.

But Why Oracle iStore?

Oracle iStore has its own framework and very well integrated with other modules in ERP system, hence a very minimal set up is required to go live on the ecomm. Also from technical point of view, iStore can be heavily customized.
Oracle iStore can be used to sell any type of items like PTO, ATO, CTO, Services.
Oracle iStore uses the Items and UOMs defined in the Inventory, Prices defined in the Advanced Pricing, Customer defined in TCA and Order types, rules defined in the OM.
In Oracle iStore, the end users or consumers can perform the following using Customer UI
 Share the cart with another B2B user.
 Save the cart.
 Save the cart as shopping list.
 Add promotion codes.
 Maintain the default ship to, default bill to, email address, Credit card numbers.
 Track the Orders.
 Initiate the Order returns.
 Convert the Cart in to Oracle Quote.
 See the history of Orders, Quotes and Invoices.
 Place Order.

The other UI we have not talked about here is iStore Admin UI,
iStore Admin UI is mainly used by Product managers or Product Administrators for a Business.

The following can be performed

 Creation and maintenance of Site.
 Associating Price list to the Site.
 Define Payment Terms.
 Define Shipping method.
 Adding items to sections.
 Mapping image to the product.
 Enable Multi language for the Site.

Above are just few tasks that can be performed. There are few more which are not listed here. Probably I will try to cover in the upcoming postings.
I will cover the iStore admin feature in my next posting…

Credit Memo and its invoice

I had few difficulties in finding the corresponding invoice number for a credit memo, for one my issue in Oracle incentive compensation and googled for it, but have not found anything useful

Finally i have drafted the below simple query with the help of a big query from my team mate.

select A.TRX_NUMBER CMNO,
A.TRX_DATE CMDATE,
O.TRX_NUMBER INVNO,
O.TRX_DATE INVDATE
FROM
RA_CUSTOMER_TRX_ALL A,
RA_CUSTOMER_TRX_ALL O
where
O.CUSTOMER_TRX_ID = A.PREVIOUS_CUSTOMER_TRX_ID
and A.trx_number like &enter_the_invoice_number

the above gives the corressponding the invoice number for a credit memoe with the creation date.

Steps to enable workflow list in the Oracle Apps Home Page

1. Please set the following profile options for the user who performs the Home Page personalization:
Personalize Self Service Definition - Yes
FND: Personalization Region Link - Yes

2. Login to Applications Home Page and select the 'Personalize Page' link

3. In the 'Choose Personalization Context' page, select the "Apply" button. Before step 4 is
mandatory to click Complete View.

4. In the Personalization Structure table, expand the following:
"Table Layout: (topTableLayoutContainer)" then
"Row Layout: (tableLayoutRow)" then
"Cell Format: (worklistResponsibilityLeftCell)"

5. See below "Cell Format: (worklistResponsibilityLeftCell)" and expand the following:
"Table Layout: Home Contenttable" then
"Row Layout: Home Content Row"

6. Select "Personalize" for "Row Layout: Home Content Row."

7. In the Personalization Properties region, set "Rendered" to "True" at the level you require (e.g. Site.) Then select the "Apply" button.

8. Select "Return to Application" link.
The screen should return you to the e-Business Suite home page with a Worklist displayed at the top left-hand side of the page. You should see the 5 most recent notifications for that user.

Reference : Taken from a metalink note

Oracle iStore - Browser Issue

One of my client’s user had problem in adding the product to the section through Sysadmin responsibility. When he clicks on the Add product icon (+), the pop up appears and he could be able to search for the product but when he selects the product and click on add, the pop page returns to the main page without adding the item to the section. There was no refresh as soon as the pop page closes.

Wherever he added something in the pop page, he faced this issue. To me , it looked like more of browser issue and he was able to perform the same action from some other system and browser.

So I have suggested the following action which resolved the issue.
Tools --> Internet options --> Programs --> Reset Web settings.

Which actually resets the web settings to default setting.

Oracle Apps – HTTP Trace

ieHTTPHeaders is an Explorer Bar for Microsoft Internet Explorer that will display the HTTP Headers sent and received by Internet Explorer as you surf the web.

This tool can be mainly used to understand the (HTTP) request that is being sent and the (HTTP) response received from the web server. The trace also helps to identify the basic cookies being set by the pages.

This is a generic tool and not specific to Oracle Applications, but can be mainly used to debug issues with self service page based modules in Oracle apps.

The trace has so many options where you can see all the included JSP or HTML files involved in the responses

You can download the recent version of the tool from the below link.
Download ieHTTPHeaders

Oracle OM – Advanced Pricing

We know Advanced Pricing has some additional features compared to Basic Pricing, but how do we say that either we are using Advanced pricing or Basic pricing.

When you have pricing module (QP) installed as shared then you are using only the limited functionalities of pricing - Basic pricing.
When you have pricing module (QP) installed as full then all the functionalities are available to you - Advanced Pricing.

How do I check whether QP is installed as Shared or Full?

Select status as “Installation Status” from fnd_product_installation where patch_level like '%QP%';

Oracle Apps – Debug method

Please find the below document drafted by me,I use the same procedure to debug all kind of error and warning messages in Oracle apps.
Message To Code Analysis

I hope this helps for you guyz as well.

If you have any issues in accessing the document, drop your mail in the comments so that I can mail you the document.

Oracle iStore – Cart to Quote

How do I convert my Shopping Cart to a Oracle Quote?

After adding items to the cart, click on the “Request for Salesrep Assistance” button to convert the shopping cart in to Quote. Then the Quote is assigned to a sales representative.

The “Request Salesrep Assistance” button is not always enabled in the checkout page, the profile IBE: Enable Salesrep assistance controls the display.

You should have Oracle Quoting integration to use this feature.

Oracle iStore - Clearing Cache

Understanding Cache : Cache is nothing but the compiled version of the JSP pages. All the JSP in the server are getting converted in to .java and .class file when compiled which is called as “Cache”. When you access the jsp files from the UI, the cache is getting invoked and not the JSP files.

When you modify an existing JSP files in Oracle iStore, the corresponding cache has to be cleared (deleted) to see the new changes. Clearing of cache is required even when you modify some of the .java files in iStore.

For 11i, the cache is stored under $COMMON_TOP/_pages, sometimes under
$COMMON_TOP/_pages/_oa__html, manual recompilation is not required to create the cache in 11i.

For R12, the cache is stored under $COMMON_TOP/_pages.
After clearing the cache, we have to recompile the JSP files manually since s_jsp_main_mode is set to justrun by default which is in turn updated in orion-web.xml.
To avoid manual compilation, the above parameter has to be set accordingly.

Caching clearance can be done from front end, steps as follows

Login as SYSADMIN Select "Functional Administrator" responsibility
Go to Core Services --> Caching Framework --> Tuning
Query the application (for example iStore) or by Name or code.
Select the component and clear the cache.

Oracle iStore – What is B2C , B2B and Guest User ?

Guest user – When the user is not logged in iStore, then you are treated as Guest user to browse the iStore sites.
The corresponding Guest user name is IBEGUEST. You can query the user in the Sysadmin-->User-->Define.

So to make iStore to work properly without logging in, you should have proper set up for the guest user otherwise you may hit an application error when you access the login page or Minisite lists page.

B2C : B2C is Business to Consumer user.
It is a user who is not tied to any organization and register themselves in iStore as “Register as an individual”
They are provided with Username and password to login iStore.

B2B: B2B is Business to Business User.
B2B users are always tied to some organization. They could be distributors or users of the organization. They register with the registry id of their organization.
Also a new organization can be registered using appropriate user types while registering.

What is registry id for B2B?
Registry id is nothing but the partner number of the organization.
Select partner_number as “Registry Id” from hz_parties where party_name like ‘’

Oracle Apps - Password mandatory?

Do you think password field in AppsLocallogin.jsp is mandatory?
If you think so, it is wrong. You can enter your password in the username field itself, please give a try.
In the username field enter the username/password and click on login.

Oracle Apps - With Windows Vista

I had a problem accessing Oracle Applications[Forms] from my laptop which has Windows Vista OS. So I googled it and found out the below solution which worked for me,I guess this might be useful for others too

Have the most updated JRE (1.6.0_03) installed in your Vista.
> Copy the file, JVM.dll => C:\Program Files\Java\jre1.6.0_03\bin\client\jvm.dll to overwrite the one in your Jinitiator Path, C:\Program Files\Oracle\JInitiator 1.3.1.28\bin\hotspot