Oracle Fusion - Documents Link

This is my post regarding Oracle Fusion in this blog, here is the link to Oracle Fusion Applications documentation.

http://docs.oracle.com/cd/E15586_01/index.htm

There are numerous training available for Oracle Partners network (OPN) on Fusion Moudles.

Oracle Applications Upgrade - Data Model Comparison Report

The EBS data model comparison report provides the database object definition changes between two EBS releases to help users to preview the database object definition changes before upgrading their instances from one release to another and understand the impact of the database object changes that may affect the customization or business flows.

Users can select a product and navigate the database object definition differences for each supported database object type.


Please access the metalink note 1290886.1 and get the report downloaded to your PC / Desktop.

The Data Model Comparison Report is available for the below upgrades.

Release 11.5.9 to Release 12.1.3
Release 11.5.10.2 to Release 12.1.3

Release 12.0.4 to Release 12.1.3
Release 12.0.6 to Release 12.1.3

Release 12.1.1 to Release 12.1.3
Release 12.1.2 to Release 12.1.3

12.1.3 - New Delivery Options

In 12.1.3, there is a new feature called 'Delivery options' for the concurrent
program submission.

The output file/report of a concurrent program can be mailed to one or more email ids thorugh this option.

But the below set up is required to make this feature work.

FND: SMTP Host - Set the SMTP Host name at the Site level.
FND: SMTP Port - Set the SMTP Port Number at the Site level.

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

External iSupplier User

We have faced a issue during our implementation, where the registered external isupplier users were not able to login to the portal.

When they log in, they get the 'Page not found error' though the POS: External URL has been set to external web server.

Later we found that setting up POS: External URL will not be sufficient and below are mandatory set ups to be performed to make external users to log in to the Supplier portal.


1. Set the profile option POS: External URL to:
http or https://(external web server machine):(port)/
2. Set the profile option POS: Internal URL to :
http or https://(internal web server machine):(port)/
3. After setting the above profile options

please run the following script using user APPS through sqlplus:
$POS_TOP/patch/115/sql/pos_upg_usr.sql
The above script updates the user level values of below profiles for all supplier users using the new values in the profile option POS: External URL

-> Applications Framework Agent
-> Application Web Agent
-> Apps Servlet Agent

Important : Every time the value of profile option POS: External URL is changed, it is necessary to run this script.

Oracle Apps - Quick How Tos

Q) How to find the Oracle Apps instance version ?
Ans - select RELEASE_NAME from fnd_product_groups

Q) How to find the Oracle Apps Database version ?
Ans - select version from v$instance

Q) How to find the hostname of an oracle apps instance ?
Ans - select host_name from v$instance

Q) How to find the instance name of an oracle apps instance ?
Ans - select instance_name from v$instance

Oracle iStore - Hide Tax Exemption

Oracle iStore determines whether to display the tax exempt part of the customer UI in the checkout billing pages based on the Oracle Receivables (AR) settings for Tax Exemptions at Customer level.

Release 11i

To disable the Customer Exemptions flag

Responsibility: Receivables Manager
Navigation: Setup > System > System Options
Go to the Tax Defaults and Rules Tab
Uncheck the "Use Customer Exemptions" field

Release 12

E-Biz Tax Module > Defaults and Controls > Select Operating Unit and Application Receivables > Application Tax Options
Uncheck the Allow overide and entry of Customer exemptions.

OA Personalization Migration

Steps to be performed at the Source Instance :

1. Set the profile option “FND: Personalization Document Root Path”.

The value of this profile option will be the directory name on mid-tier
Ensure that this directory exists on mid-tier machine of the destination environment and have proper read/write access.


2.Navigate to Functional Administrator Responsibility --> Personalization --> Import/Export.


3.Search the desired personalizations for the application name.

4.Extract the personalilzations.

5.Login to linux/unix mid-tier and cd to directory specified in profile option.

6.Do tar –cvf XXXOAPersonalizations.tar ./*

Steps to be performed at the Destination Instance :

1. Set profile option “FND: Personalization Document Root Path”.

The value of this profile option will be the directory name on mid-tier
Ensure that this directory exists on mid-tier machine of the destination environment and have proper read/write access.

2. FTP the XXXOAPersonalizations.tar to the directory specific in above profile option.


3.Cd to that directory

4.tar –xvf XXXOAPersonalizations.tar *****

5.Navigate to Functional Administrator Responsibility --> Personalization --> Import/Export

6. Select and Import the personalizations that have been imported.

All about AIM

Business Process Architecture (BP)

BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure

Business Requirements Definition (RD)

RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements

Business Requirements Mapping

BR.010 Analyze High-Level Gaps
BR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles

Application and Technical Architecture (TA)

TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Architecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures

Module Design and Build (MD)

MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines

Data Conversion (CV)

CV.010 Define data conversion requirements and strategy
CV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data

Documentation (DO)

DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide

Business System Testing (TE)

TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test

PERFORMACE TESTING(PT)

PT.010 – Define Performance Testing Strategy
PT.020 – Identify Performance Test Scenarios
PT.030 – Identify Performance Test Transaction
PT.040 – Create Performance Test Scripts
PT.050 – Design Performance Test Transaction Programs
PT.060 – Design Performance Test Data
PT.070 – Design Test Database Load Programs
PT.080 – Create Performance Test TransactionPrograms
PT.090 – Create Test Database Load Programs
PT.100 – Construct Performance Test Database
PT.110 – Prepare Performance Test Environment
PT.120 – Execute Performance Test

Adoption and Learning (AP)

AP.010 – Define Executive Project Strategy
AP.020 – Conduct Initial Project Team Orientation
AP.030 – Develop Project Team Learning Plan
AP.040 – Prepare Project Team Learning Environment
AP.050 – Conduct Project Team Learning Events
AP.060 – Develop Business Unit Managers’Readiness Plan
AP.070 – Develop Project Readiness Roadmap
AP.080 – Develop and Execute CommunicationCampaign
AP.090 – Develop Managers’ Readiness Plan
AP.100 – Identify Business Process Impact onOrganization
AP.110 – Align Human Performance SupportSystems
AP.120 – Align Information Technology Groups
AP.130 – Conduct User Learning Needs Analysis
AP.140 – Develop User Learning Plan
AP.150 – Develop User Learningware
AP.160 – Prepare User Learning Environment
AP.170 – Conduct User Learning Events
AP.180 – Conduct Effectiveness Assessment

Production Migration (PM)

PM.010 – Define Transition Strategy
PM.020 – Design Production Support Infrastructure
PM.030 – Develop Transition and Contingency Plan
PM.040 – Prepare Production Environment
PM.050 – Set Up Applications
PM.060 – Implement Production Support Infrastructure
PM.070 – Verify Production Readiness
PM.080 – Begin Production
PM.090 – Measure System Performance
PM.100 – Maintain System
PM.110 – Refine Production System
PM.120 – Decommission Former Systems
PM.130 – Propose Future Business Direction
PM.140 – Propose Future Technical Direction


But recently Oracle AIM was replaced by Oracle OUM (Oracle unified Methodology).

Oracle Advanced Pricing - Training Link

I found this link very useful and informative to learn Oracle Advanced Pricing.

http://download.oracle.com/docs/cd/E18727_01/doc.121/e13427/toc.htm

Oracle Order Capture - an Overview.

I would say Order Capture is a virtual module in Oracle CRM. The application code for Oracle Order Capture is ASO.

Order capture is mainly used by Oracle iStore and Oracle Quoting modules. Order capture is a set of APIs being used by these modules in converting the Cart or Quote to Sales order.

The IBE or QOT purely depends on the ASO to convert the cart or quote to Sales Order in Order Management.

The two important tables in ASO,

ASO_QUOTE_HEADERS_ALL
ASO_QUOTE_LINES_ALL

ASO_QUOTE_HEADERS_ALL -- Will have the header level details for Cart or Quote
ASO_QUOTE_LINES_ALL -- will have the line level details for Cart or Quote.


When the Quote or Cart is converted to Sales order, the data from ASO_QUOTE_HEADERS_ALL flows to OE_ORDER_HEADERS_ALL and the data from ASO_QUOTE_LINES_ALL flows to OE_ORDER_LINES_ALL.


ASO_PRICE_ADJUSTMENTS is the another important table which stores the adjustments(modifier) details on the cart or Quote.

There are status column in the ASO headers table to identify the status of the Quote or Cart and the source column to identify the source of the Quote.

OM - Receivables (Non invoiceable item)

OM - Receivables ( Non invoiceable item )

The non invoiceable item when added to the Sales order will not be interfaced to AR. The INVOICE_INTERFACE_STATUS_CODE for the non invoiceable item is set to NOT_ELIGIBLE when the SO is posted to AR.

Oracle BRM - my understanding

Oracle BRM is more of a tool utilized by a communication industry to
bill their customers based on the usage of the services they offer.3

Unlike oracle application, BRM has separate tool like for each operations (like Customer Centre, Payment Centre).
BRM is a 4 tier architecture and needs more of technical intervention to understand the product.

Originally it was called as Portal, and after Oracle bought it renamed to "Oracle BRM".

Oracle BRM has standard adapter to Seible CRM for Customer centre, and can be integrated to other CRM using AIA.
so most of the BRM users do not use BRM's Customer centre.

My understanding is any customer based organization needs to address their customer complaints (like iSupport module in Oracle) so mostly they always have a very powerfull CRM tool, so having a separate customer centre does not solve their purpose. So most of the customer implements BRM with the integration with their CRM.
::::::::::::::::: Continues....

iSupplier Registration - Table

The table that stores the newly registered Supplier through iSupplier registration link is POS_SUPPLIER_REGISTRATION.

I guess this table has some extra columns in R12. Please check in metalink.

OA - Enabling 'About this Page' Link

Setting the profile FND: Diagnostics to Yes enables the About this Page link in any OA Page.

iSupplier Registration Error - POS_REG_UNEXPECTED_ERR

In iSupplier portal, I have tried to register the Supplier User using Supplier Profile Administrator and faced the below error,

We're sorry, but the service you requrested is not available at this time. Please take down the following error message and contact your system administrator: POS_REG_UNEXPECTED_ERR0_11_1_4
I was very confused by the above error, did metalink search. Got 6-7 hits.
and finally the below set up has resolved the error.

'HZ: Generate Party number' to Yes at the responsibility level has resolved the error.

Advanced Pricing - Attribute Management

I have got a requirement which forced me to learn about the Attribute management in Oracle Advanced Pricing.

There is Qualifier context for Item Name and Item Category. Our issue was, we had two different set of items in the same category and the intention is to apply the modifier to one set of items and not to the other.

But the good thing is the naming convention differenciates the item types. i.e one set of item has '_L' in its name.

For example PR and PR_L. PR belongs to one set and PR_L belongs to the other.
For any attribute mapping, you should be sure under which context you have create the new attribute, and obviously in this case it has to be Qualifier Attribute.

So Create a new Qualifier Context,
Oracle Advanced Pricing --> Set Up --> Attribute Management --> Context and Attributes.


Context :
Type : Qualifier Context
Code : User Defined Value
Attribute :
Code : Qualifier Attribute Code
Name : Qualifier Attribute Name
Column Mapped : Select the available Attribute Column.
Valueset : If you want the user to restrict the values to be entered.

Now the Attribute is ready and now comes the attribute Mapping.
Oracle Advanced Pricing --> Set Up --> Attribute Management --> Attribute Linking and Mapping.
Pricing Transaction Entity : Order Fulfillment
Context Type : Qualifier Context
Select the context that you have created in the above steps --> Link Attributes.
Select the Level ( Line or Header or Both), The attribute Mapping method is : Attribute Mapping.

Once you have selected the Attribute Mapping, the Attribute Mapping button is enabled.Click on it.

Select your Application name and request type.
I am concentration on OM, as my pricing call will be made from there.
Select the User Source Type as PL/SQL API and User Value string as the call to your custom package.

In my case, XXX_RETRIEVE_ITEM.GET_BASE_ITEM(OE_ORDER_PUB.G_LINE.ordered_item)
Peseudo Code for XXX_RETRIEVE_ITEM.GET_BASE_ITEM(OE_ORDER_PUB.G_LINE.ordered_item)
If (OE_ORDER_PUB.G_LINE.ordered_item has '_L' in its name )
RETURN 'FALSE'
ELSE
RETURN 'TRUE'

The very mandatory and important step after all this is, running the Build Attribute Mapping concurrent program.

So all my new Qualifier is ready to use now.

To make sure that the modifier is getting applied only to the non '_L' item, I have to define qualifier like this

OA Framework Personalizations- Creating a New Field

I am not OA consultant but I had to handle few personalization for an iSupplier implementation.I had a very basic knowledge on personalization, and explored few more stuff during this assignment.

I have used the below approach to create a new field in the OA Page.

Get the VO :
In any OA page, if you click on the 'About this Page' you will get clear idea on the underlying VO. Get the name of the VO and the column names. For the exact column name you have look in to the attributes table.

Create a new Item:
Click on the Personalize page link in the Page where you want to create a new field.

Select the respective region and click on Create new item icon.
Enter unique value in the ID, Prompt and some basic properties like read only, rendered, mandatory. The Item style as 'Message Text Input'.

The important part is View Attribute and View Instance.
In the view Attribute, enter the column name you want to store the value.
The column name is nothing but the attribute name you have picked by viewing 'About this Page'.

In the View Instance, enter the VO value. If the entered VO is not mapped in to the OA page, it will throw error message.

So make ensure that view instance and view attribute are appropriate for the page.

I have used the above approaches to create two new fields in the iSupplier Registration pages.

iSupplier - New Supplier Registration

I am totally new to the iSupplier module and have been asked to explore on the new supplier Self service registration process. [11i]

Here are the Steps that I have identified and being used to generate the iSupplier registration link for Self Service Registration process.

You can leave your comments if you feel anything is wrong in this.

Purchasing Super User--> Setup : Profile Management Configuration --> Organization Encryption

Click on the respective Operating Unit and Click on encrypt.

Once the Encryption string is generated, please append the encryption string in the below link in the place of '=(encryption string)'

http(s)://(server):(port)/OA_HTML/jsp/pos/suppreg/SupplierRegister.jsp?ouid=(encryption string)

Then the generated link can be used to register supplier for the selected operating unit.

Please make ensure that the responsibility 'POS Supplier Guest User' has been assigned to the GUEST user and GUEST user is active.