Oracle iStore - 'How To?' Part 3

Q11. How do I find the iStore patch set level?

Select patch_level, status from fnd_product_installations where patch_level like ‘%IBE%’

Status = ‘I’ means Installed, ‘S’ means Shared Install, ‘N’ means Not installed

Q12. How do I find the ASO (Oracle Order Capture) patch set level?

Select patch_level, status from fnd_product_installations where patch_level like ‘%ASO%’

Status = ‘I’ means Installed, ‘S’ means Shared Install, ‘N’ means Not installed

Q13. How to find particular Java Class file version?

Issue the below UNIX command to get the version of the file.
Strings –a | grep ‘Header’
For (e.g.)

Q14. How to enable Debug for Oracle iStore?

Set the below profiles:
IBE: Enable Debug to Yes at User level
ASO: Enable Debug to Yes at User level
OM: Debug level to 5 at user level
OM: Debug log Directory at Site level.
The value for the OM: Debug log Directory should be from any one of the value from the below query
Select value from v$parameter where name like 'utl_file_dir'

Q15. How to I get the generated Java and PL/SQL debug log ?

The PL/SQL log is generated under the directory mentioned in the profile OM: Debug log Directory.
The Java log is generated under the directory [ The java log will be in Mid-Tier/App Node]
$LOG_HOME/ora/10.1.3/j2ee/oacore
ls -ltr IBE*.log
The log file name will have the username part of it.

Oracle iStore - 'How To?' Part 2

Q6. Query to get the Item details associated to a Section.

Select unique (description),
ORDERABLE_ON_WEB_FLAG,
WEB_STATUS
From MTL_SYSTEM_ITEMS_B where inventory_item_id in (select inventory_item_id from IBE_DSP_SECTION_ITEMS where section_id = &Section_Id)

Q7. The Item is not appearing in the iStore UI, What are the parameters to be checked?

Check the Item Web Status and Orderable on web flag in the Item master for the Item that is not appearing in the iStore UI.The Web Status should be “Published” and The Orderable on the Web flag should be enabled.

Q8. Query to get all the Items listed in the minisite.

Select distinct (inventory_item_id), description from mtl_system_items_b
Where inventory_item_id in (select mini_site_section_item_id
From IBE_DSP_MSITE_SCT_ITEMS where mini_site_id = &Minisite_Id);

Q9. Query to get all the Items assigned to a particular section

Select distinct (inventory_item_id), description from mtl_system_items_b
Where inventory_item_id in (select inventory_item_id
From IBE_DSP_SECTION_ITEMS where section_id = 10022);

Q10. How to clear client Side cookies?

Browser: IE (Internet Explorer)

In the Menu, navigate to Tools --> Internet Options --> Click on Delete Cookies and Delete Files options.


Browser: Mozilla Firefox

In the Menu, navigate to Tools --> Options --> Privacy --> Cookies -->Show Cookies --> Remove All Cookies
Tools --> Options -->Privacy --> Private Data --> Clear Now

FND_UNSUCCESSFUL_LOGINS

When ever an unsuccessful attempt was made to login to the application, a record gets inserted in to the table FND_UNSUCCESSFUL_LOGINS.

Oracle iStore - Difference btw 'Delete all lines' & 'Delete Cart'

Delete Cart:

Delete Cart functionality keeps all the records in the line table and headers table.
The header table status is updated as 5, which is inactive.

Deleting all the lines:

Deleting all the lines deletes the data from the lines table and header level record is retained in the headers table with status as 5, which is inactive.

Oracle iStore - Quick How To Part 1

Q1. How to get Error stack for the Application Error message from the iStore customer UI?

Option 1: Right click on the Application error screen and select the view source from the options available.
Option 2: Click on View from the browser menu and select source.

Q2. What if the error stack is not available for the Application error?

Make ensure that the profile FND: Debug Log Enabled is set to Yes.

Q3. How to get the Template Name for the associated JSP file?

Use the below query to get the Template name for the associated JSP file.
SELECT I.ACCESS_NAME, A.FILE_NAME FROM JTF.JTF_AMV_ATTACHMENTS A, APPS.JTF_AMV_ITEMS_VL I, IBE.IBE_DSP_LGL_PHYS_MAP M, IBE.IBE_MSITES_TL S WHERE A.ATTACHMENT_ID = M.ATTACHMENT_ID AND I.ITEM_ID = M.ITEM_ID AND M.MSITE_ID = S.MSITE_ID and a.FILE_NAME like 'Jsp file name here'

Same query can be little modified to get the File name from the Template name.

SELECT I.ACCESS_NAME, A.FILE_NAME FROM JTF.JTF_AMV_ATTACHMENTS A, APPS.JTF_AMV_ITEMS_VL I, IBE.IBE_DSP_LGL_PHYS_MAP M, IBE.IBE_MSITES_TL S
WHERE A.ATTACHMENT_ID = M.ATTACHMENT_ID AND I.ITEM_ID = M.ITEM_ID AND M.MSITE_ID = S.MSITE_ID and I.ACCESS_NAME like ‘template name here’

Q4. How to change the Company Logo in the Customer UI?
Map the template STORE_LOGO_MAIN with the desired image. The Mapping can be done in the iStore Administrator UI using the navigation iStore Admin UI --> Advanced --> Template Manager.

Q5. Query to get the Price list associated with the Order.
select distinct(name) from QP_LIST_HEADERS_TL where LIST_header_id = (select PRICE_LIST_ID from oe_order_headers_all where order_number = &Order_Number)

FND Messages

I have defined a message of 5 lines in Application developer to use the message in my self service application. The message is supposed to appear as 10 different lines my self service application when invoked.

So I have defined the message text as below in Application Developer

Message Name : XXX_TOOL_TIP

Message Text :

This is message line no 1
This is message line no 2
This is message line no 3
This is message line no 4
This is message line no 5

Then I have invoked this message XXX_TOOL_TIP in my application and the message appeared as below

This is message line no 1This is message line no 2This is message line no 3This is message line no 4This is message line no 5

It is just because Oracle apps does not interpret the message as defined in the Application developer. If you want to display a blank line in the application, the message has to be defined in the Application developer accordingly.

The above message has to be defined as below to get the message in the expected format.

<br>This is message line no 1<br>This is message line no 2<br>This is message line no 3<br>This is message line no 4<br>This is message line no 5