Save below script in c:\toad.startup.sql

BEGIN
INSERT INTO FND_SESSIONS
SELECT USERENV(‘SESSIONID’), TRUNC( SYSDATE )
FROM DUAL;
COMMIT;
END;
/
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO( 101 );
END;
/
BEGIN
FND_GLOBAL.SET_NLS_CONTEXT (P_NLS_LANGUAGE => ‘AMERICAN’);
COMMIT;
END;
/
—End of script

open toad then click on view-> Toad options->schema browser->Startup(As shown below)

click on apply and restart the toad, The above script files executed automatically and set to language and org information to your toad session.

If you want execute above script for every new connecton call same file in ” File to execute on new connections: c:\toad_startup.sql ”

Convert Number to Words

Imagine you are developing a report for AP module [Infact in any module] and customer wanted to display Invoice amount in Words, then you can use this solution.

SELECT ap_amount_utilities_pkg.ap_convert_number(12345) AS amt_in_words 
  FROM dual;

Gives Output as “Twelve thousand three hundred forty-five”

Note:
1. Maximum number allowed is (10 Power 12)
2. Non Oracle Apps users can use the following query.
    select TO_CHAR(TO_DATE(&enter_a_number, ‘J’),’JSP’) FROM dual
    Limitation: Entered number should not exceed 5373484

Purpose of the blog:

The purpose of this document is to rebrand the entire R12 application to have client’s look and feel.

Introduction:

This section identifies the places needs to be rebranded. The Login page should be adapted to conform with Login page as per „Brand Identity in Graphical User Interfaces Specification“.Favicon should be introduced for Web application. Oracle colors should be replaced by colors used in Client Look and Feel OrangeTouch for Web and for Java.

Oracle logo should be removed in the forms menu. EBS should be replaced with charge@once business Enterprise Financials. About dialogs need to be adapted for Web and Java. Splash screen required for Java application.

1. Rebranding of EBS

1.1 Login page should be adapted to conform with Login page as per „Brand Identity in Graphical User Interfaces Specification“

Existing login template layout will be replaced. Web page stylesheet (*.css) and image changes would require.

Solution:

Login as SYSADMIN
Navigate to Functional Administrator;
Personalization tab;
Search criteria: “Document Path = /oracle/apps/fnd/sso/login/webui”
Select /oracle/apps/fnd/sso/login/webui/MainLoginPG
Check ‘Include’ and press Apply button
Select ‘Personalization Structure’ as Complete View
Select globalTop image to personalize
Write ‘xxGlobalLeft.bmp’ to Image URI site field and apply.
Select global image to personalize
Write ‘xxGlobalLeftBack.bmp’ to Image URI site field and apply.
Press ‘Add Item’ to Region11.
Choose Item Style: ‘Image’
ID: ‘xxGlobalRight’
Image URI: ‘xxGlobalRight.bmp’
Hide region15login by selecting personalize button and setting Rendered option to ‘false’
Make toplines.gif and toplies_dummy.gif as rendered false.
Copy the custom people.jpg to $OA_MEDIA directory.
Make the language region, accessibility region as rendered false.
Go to customize standard footer and customize the copyright message.
Backup ‘people.jpg’, ‘lightBlue_back.jpg’, ‘topLines.gif’ files in $OA_MEDIA directory

1.2 Product banner should be introduced for Web and Desktop application

EBS Customized look and feel template (CLAF) will be configured which includes background image changes, tab layout, logo changes (e.g. Oracle logo in web form below could be changed separately) together with text link stylesheet (*.css) definition and Oracle forms changes (Oracle logo) would take 12 man days.
Assumption: Icons, logos and backgrounds (*.jpeg, *.gif images) are provided already in preprocessed shape (all necessary sizing and cropping etc..).

Solution:
LOGO installation and configuration:
1. Copy XX_Logo_(130×58).gif file to OA_MEDIA (/opt/oracle/product/12.0/apps/apps_st/comn/java/classes/oracle/apps/media)
2. System Administrator –> Profile System Values –> Change “Corporate Branding Image for Oracle Applications” value to “/OA_MEDIA/XX_Logo_(130×58).gif”

1.3 Oracle colors should be replaced by colors used in Client Look and Feel OrangeTouch for Web and for Java

Oracle EBS look and feel personalization functionality will be used. To accommodate color scheme changes careful walkthrough through all used modules needed. Every web and Java forms control (tab view, table/grid view, master-detail view etc..) needs detailed color scheme tuning in OAF (Oracle Applications framework).

Client look and feel (LAF) installation and configuration:
1. Copy myxx-desktop.xml and myxx-desktop-metadata.xml files to OA_HTML/cabo/lafs (/opt/oracle/product/12.0/apps/apps_st/comn/webapps/oacore/html/cabo/lafs)
2. Copy myxx-desktop.xss file to OA_HTML/cabo/styles (/opt/oracle/product/12.0/apps/apps_st/comn/webapps/oacore/html/cabo/styles)
3. Add “myxx-desktop” value to APPLICATIONS_LOOK_AND_FEEL lookup type

Workaround –> Create and save “myxx-desktop” Custom Look and Feel manually (user needs responsibility “”), and copy myxx-desktop.xss file to OA_HTML/cabo/styles
4. System Administrator –> Profile System Values –> Change “Oracle Applications Look and Feel” value to “myxx-desktop”

1.4 Oracle logo should be removed in the forms menu

1. Open the file $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg2. Check the baseHTML and baseHTMLJInitiator variables inside the file.
2. They point to the appsbase.htm file used in the forms (ussually is the same file for both)
3. Open the file(s) pointed by those variables
4. Go to the “// Forms Applet Parameters” section related to the client browser you use.
5. Check if there’s a line that references to “PARAM name=logo”.

An example of how that line must be for Internet Explorer is:
IEhtml += ‘<‘ + ‘PARAM name=logo value=”‘ + xlogo + ‘”>’;
add or change that line according to what you have and what you want.

6. Go to the “// Oracle Applications default Parameters” section in the same file
7. Add or edit the definition of xlogo variable. It must be:
var xlogo = “%logo%”
8.- Save the file(s)
9.- Locate the variable “logo” in $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg file at the “Forms Server Information: servlet, port, machine name and domain” section. If it’s not there then you can create the line. The variable must point to the .gif file you want to add on the forms and point the “logo” variable to your file with:logo=noStop and start the apache and forms services.

1.5 eBS should be replaced with Product Name

Application name normally in eBS is distributed between various GUI elements and controls (Web and Java forms). Some labels could be a part of eBS product core which not necessarily is available for modification. Investigation of all locations and implementation. Some examples where application name could be located: context menu in Java forms(file, help), header/footer in web forms, text labels in web wizard type forms, web page titles etc…

Solution:
1. Go to “Application Developer” responsibility
2. Select “Messages” from the menu
3. Query the message name and then enter your message text in the “Current Message Text” field
4. Run Generate Messages program.
5. Save changes and exit6. Clear cache and bounce Apache to see the change
Message Texts:
ORACLE_APPLICATIONS – Product name

1.6 About dialogs need to be adapted for Web and Java

Software version information may reside not only in traditional help->about dialog. All possible location investigation and implementation

Solution:
1. Go to “Application Developer” responsibility
2. Select “Messages” from the menu
3. Query the message name and then enter your message text in the “Current Message Text” field
4. Run generate messages program.
5. Save changes and exit
6. Clear cache and bounce Apache to see the change

Message Texts:
VERSION_COPYRIGHT – Product name Copyright (c) 2009 XX Limited, All Rights Reserved. XX Ltd
VERSION_WINDOW_TITLE – About Product Name V2.0

Copy the FNDMENU.mmb from the /d01/oracle/PROD/apps/apps_st/appl/au/12.0.0/resource/US directory.
Open the mmb file in forms builder and change the label “About Oracle Applications” as About Product Name V2.0.
Compile the menu to generate mmx. Since it is R12 use frmcmp_batch command to compile mmb.

1.7 Splash screen required for Java application

Splash screen is shown during first Java forms load. Java forms application part is initiated through web components therefore before implementation detailed analysis of OAF (Oracle Applications framework) components will be needed.

Solution:

Rename the forms_logo.gif file in the $OA_MEDIA and place the custom Client gif file in the same folder with the name forms_logo.gif.

To change the splash screen copyright text message, edit the configuration file appsweb.cfg in the below folder:
/d01/oracle/PROD/apps/apps_st/comn/webapps/oacore/html/bin

1.8 Favicon should be introduced for Web application
For all web based application pages customized browser URL icon will be introduced.

Solution:

Favicon Icon should be generated using any online tool and keep it in the below folder as favicon.ico:

/d01/oracle/PROD/inst/apps/PROD_blrrdebs2/portal

Also add the below line in all the header part of html files in the /mnt/fs-fc/prof/applprof/inst/apps/PROF_epn-0/portal folder to activate the favicon in the respective pages.

<link rel=”shortcut icon” href=” /d01/oracle/PROD/inst/apps/PROD_blrrdebs2/portal
\favicon.ico” mce_href=” /d01/oracle/PROD/inst/apps/PROD_blrrdebs2/portal\favicon.ico”>

Mandatory: User should add the url as their favourites to activate the favicon which is the pre-requiste in IE.

Issues Faced

We referred many sites to remove the Oracle logo from the forms menu. All suggested to modify the formsweb.cfg file and it was not worked. We raised a service request with Orcle and they advised us to do the changes in appsweb.cfg and appsbast.htm files since it is R12. It worked finally.

Conclusion

Each layout control in eBS might conform to certain CLAF (Customized look and feel) rules therefore proper unit level testing is needed which multiplies to each used form in selected solution. Complexity of architecture (two different technologies are used: web and java forms) can double certain work amount (e.g. banners and common menu items).