Tuesday, December 23, 2014

Interesting Features of OAF RUI - R12.1

Below are the profile options introduced as part of R12.1 which give rich User Interface experience to the end users.

FND: Disable Configurable Home
Determines whether the Configurable Home page is enabled or disabled. Valid values are:
  • True - Disables the Configurable Home page.
  • False - Enables the Configurable Home page.
Default Value : False as Site Level
FND: Disable Navigator and Favorites Rich Menu
Determines whether the Navigator and Favorites Pull-down Menus feature is enabled or disabled. Valid values are:
  • True - Disables the Navigator and Favorites Pull-down menus.
  • False - Enables the Navigator and Favorites Pull-down menus.
Default Value : False as Site Level

FND: Disable Look Ahead LOV


Determines whether the Look Ahead LOV feature is enabled or disabled at the Site or Application level. Valid values include:
  • false - Look Ahead LOV feature is enabled.
  • true - Look Ahead LOV is disabled.
Default Value : False as Site Level


When Set to False, the system suggests the values as you type.
FND: Disable Inline Attachments


Determines whether the Inline Attachments feature is enabled or disabled at the Site or Application level. Valid values include:
  • false - Inline Attachments is enabled.
  • true - Inline Attachments is disabled.
Default Value : False as Site Level
This option eliminates the navigation between pages. As shown in the screenshot, the attachment can be added through a pop up like page, also through the similar pop up page it can be viewed.

Know applcust.txt

The $APPL_TOP/admin/applcust.txt file should be used to register your customization and being used as a reference while applying patches.

There are two ways of doing a customization

Customization by Extension (CBE)
Customization by Modification (CBM)

Customization by Extension (CBE) is nothing but doing customizations to copies of the original Oracle applications files.
Customization by Modification (CBM) is doing the customizations directly in the Oracle applciations files. It is difficult to maintain

There is a format to be followed while registering your customization

#      

It is easier to understand the format with an example.Consider that the file $AR_TOP/forms/US/ARXSUVAT.fmb has been modified in the method CBE as $XXAR_TOP/forms/US/XXARXSUVAT.fmb

is nothing but the Standard product abbreviation. In simple meaning the Sprd is taken using the format $_TOP.
is always whatever after $_TOP, in our case it is forms/US
is the exact file name.
is your custom top where you have your customized/modified file.
is the directory structure after $_TOP
is the name of your custom file.

So in our example case, the format of the entry will be as follows
# ar  forms/US    ARXSUVAT.fmb   xxar    forms/US    XXARXSUVAT.fmb

If you have customized your file in the CBM method, then you can leave the last three entries as blank (as below)

# ar  forms/US    ARXSUVAT.fmb

The applcust.txt is replaced by Register Flagged Files Tool in R12. Register Flagged Files tool can be accessed through Oracle Applications Manager (OAM) responsibility in R12.

How to restrict the user from forwarding the approval notifications to other users?


Login as admin
2. Click System administrator --> Oracle Application Manager --> workflow
5. Clcik on Notification Mailers --> Edit --> Advanced --> Email Servers
6. click "edit"
7. Uncheck "Allow Forwarded Response" which will restrict the user to respond by e-mail to an e-mail notification that has been forwarded from another role.

Delegate or Transfer or Reassign ?

Today I have learnt some interesting stuff in Oracle Workflow Notifications...

Every Oracle Workflow Notifications have the feature to delegate or transfer or reassign the notification.

What all this means? and where this values are coming from?

The value is coming from the profile 'WF: Notification Reassign Mode' , the values for this profile can be Delegate, Transfer or Reassign.

Delegate
The Notification will be forwarded to the delegated employee/user, but the original recipient of the notification remains the owner.

Transfer
The Notification will be forwarded to the delegated employee/user and the new recipient becomes the owner of the notification.

Reassign
The user can decide whether to Delegate or Transfer.

Oracle Payables - Custom Hold on Invoices

Invoices can be put on hold using custom validations. The custom validation can be through any API or CUSTOM.pll.
The below APIs can be used to put the invoice on hold ap_holds_pkg.insert_single_hold and the hold can be released using ap_holds_pkg.release_single_hold.
Inserting Hold
ap_holds_pkg.insert_single_hold  (X_invoice_id         IN number,  -- Invoice Id to which the Hold needs to be applied
                                 X_hold_lookup_code   IN varchar2, -- Hold Look up code (select hold_lookup_code from ap_hold_codes)
                                 X_hold_type IN varchar2 DEFAULT NULL, -- Hold Type (select hold_type from ap_hold_codes)
                                 X_hold_reason IN varchar2 DEFAULT NULL, -- Can be user defined
                                 X_held_by IN number DEFAULT NULL, --Can be user defined
                                 X_calling_sequence IN varchar2 DEFAULT NULL -- Can be user defined)
Releasing the Hold
ap_holds_pkg.release_single_hold (X_invoice_id          IN number,-- Invoice Id from which the Hold needs to be released
                                 X_hold_lookup_code    IN varchar2,-- Hold Look up code (select hold_lookup_code from ap_hold_codes) This is the Hold we are trying to release.
                                 X_release_lookup_code IN varchar2, --  Release Look up code (select hold_lookup_code from ap_hold_codes) This is the Release Hold we are using to release the Hold on invoice.
                                 X_held_by IN number DEFAULT NULL,--Can be user defined ( but it has to be the same as X_held_by when the hold is applied)
                                 X_calling_sequence IN varchar2 DEFAULT NULL-- Can be user defined)
Before executing the above two procedures, define your custom hold and release using
Payables Manager --> Set up --> Hold and Release

The Hold type for the Invoice hold should be like 'INVOICE HOLD REASON' and for the release type it should be like 'INVOICE RELEASE REASON'
Also you can mention in the set up that If the hold on invoice can be released manually or not. This will be useful when you dont want to user to bypass the validation by releasing the hold manually.

Oracle Forms Personalization - Basics Getting the Access


Enable the Diagnostics to the Form Personalization
Enable the profile FND: Diagnostics at the required level to perform the form personalization.
Navigating to Form Personalization screen
Open the form in which you want to perform the personalization.
Make sure that you are in the correct form by accessing Help--> About Oracle Applications.
You can see something like below
----------------------------------------
Current Form
----------------------------------------
Form Application : Payables
Form Name : APXINWKB
Form Path : //ap/11.5.0/forms/US/APXINWKB.fmx
Form Version : 11.5.1081
Form Last Modified : $Date: 2010/06/09 06:29  $
----------------------------------------
Once you confirmed that you are in the right form, access Help --> Diagnostics --> Custom Code --> Personalize.


 Understand 'What and When'
If you get a requirement, first thing is to understand what action to be performed and more importantly when to perform.
In the form personalization 'When to perform' goes as a Trigger event and Condition and 'what to perform' goes as Action.

Identifying 'When' ( Trigger Event)
It is important to identify the trigger event to write any form personalization.
For example you are getting a requirement like below.
In the Quick Invoice window, when the Invoice type is selected as 'Credit Memo' , make the Description field as non-editable.
So we need to identify the exact trigger being executed by form when the Invoice type is selected as 'Credit Memo'.
There is a simple way to capture that trigger.
Help --> Diagnostics --> Custom Code --> Show Custom Events.
The below message will appear
"Setting this option will cause a dialog box to be displayed whenever an event is passed to the custom library."
Click on 'OK' and proceed to identify the trigger being executed when the value is changed.
1. Open the 'Quick Invoice' Form
2. Enable display for Custom events Help --> Diagnostics --> Custom Code --> Show Custom Events.
3. Select the Invoice type as 'Credit Memo' and then tab out.
4. Note down the trigger being executed as soon as you tab'd out.
APXIISIM.INVOICES_FOLDER.INVOICE_NUM: WHEN-NEW-ITEM-INSTANCE
The above format is nothing but .:
So we have now identified the trigger event and trigger object.
Now let us access the Personalization from Help --> Diagnostics --> Custom Code --> Personalize.
Doing it...
  
'Seq' - is the sequence the personalization gets executed.
'Description'  - User entered description
'Level' - Form or Function.
The Personalization can be done either at Fuction or Form levels.
Form Level : Once you do the form personalization at the Form level, it will be reflected in all the functions,forms where
this form is included.
Function Level : if the form personalization is done at the Function level, it will be reflected only in the function. When
the same function is included in any other responsibility, the personalization will get reflected there as well.
 Choosing the Level
Choosing the level of personalization is entirely based on the requirement and user needs. But understand the impact when the
personalization is done at the Form level.
'Enabled' - When checked, the personalization will have impact on the Form.
            When Unchecked, the personalization is disabled.
For our example scenario, we have identified the trigger event and item name.
The condition for our example is : When the Invoice Type is 'Credit Memo'
When you see the above sentence there are two instance one is 'Invoice Type' and another is 'Credit Memo'.
'Invoice Type' is nothing but the form Item
'Credit Memo' is the value of that form item
Now we need the Item name for 'Invoice Type' to mention in it the Condition so that the form understands our condition.
Getting the Item Name
For any field in a Form, you can get the name of the item and the folder where it resides.
Place your cursor at the field. In our case place the cursor in the Invoice Type field then
click on Help--> Diagnostics--> Examine which will give you the field name ,block name the filed belongs to and the value.
In our case Block Name : INVOICES_FOLDER Field Name: INVOICE_TYPE
It is important to refer a field with the block name.
.
  
CONDITION
Trigger Event: WHEN-NEW-ITEM-INSTANCE
Trigger Object:INVOICES_FOLDER.INVOICE_NUM
Condition:
There are properties for each field. for example to get the value of a field, the property name is 'VALUE'. To make the field
editable/non-editable the property name is 'ENTERABLE'. Similary there are more number of properties for a field.
To get the property of any item in the form, the 'Insert 'Get' Expression' button can be used and to get the value of any
item in the form, the 'Insert Item Value' button can be used.
In our case, we need to get the value (property) of Invoice type field (Item).
   ${item.invoices_folder.invoice_type.value} ='Credit Memo'
 ACTIONS
Seq: - is the sequence the personalization action gets executed.
Type: -
Property - To change any item properties
Message - To dispaly any message. Message can be displayed as Warning, Error or Hint.
Builtin - To Launch SRS form,URL,Function, set profile option, execute procedure.
Menu - Any thing related to the Menu and Special Button.
In our case, we are trying to make the Description field read only so the type would be Property.
Object Type : Item
Target Object : INVOICES_FOLDER.DESCRIPTION
Property Name : ENTERABLE
Value : FALSE
  
So our form personalization to make the Description field read only when the Invoice type is selected as 'Credit Memo' would
be as below
 CONDITION
Trigger Event: WHEN-NEW-ITEM-INSTANCE
Trigger Object:INVOICES_FOLDER.INVOICE_NUM
Condition:
   ${item.invoices_folder.invoice_type.value} ='Credit Memo'
  
ACTIONS
Seq: 1
Type: Property
Object Type : Item
Target Object : INVOICES_FOLDER.DESCRIPTION
Property Name : ENTERABLE
Value : FALSE

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.

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 - 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.

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.

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).

OA - Enabling 'About this Page' Link

Monday, December 22, 2014

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.

Saturday, December 20, 2014

XML Bursting Example

Background / Business Requirement for Bursting
In Oracle Purchasing we have PO Print Report which can be used to print new or selected Purchase order. Organizations usually schedule this program on a daily basis to print new purchase orders. If we want to integrate this Oracle Report to XML/BI Publisher to create eye candy layouts and email the PO automatically to the suppliers, the common issue we face is :-
How to split the XML file created by report ? This is because report will create a single XML file consisting of the all the PO selected and if we apply the template on this XML file , it will create a single PDF file consisting of all the PO’s , and we definitely don’t want purchase orders sent to a supplier which is not related to him .If we have to send PO to individual supplier there has to be a way to parse the XML file generated from the PO Print Report.After parsing the XML file we can break the XML into smaller chunks based on the PO Number and then email the PO to respective Suppliers.


Advantages of XMLP bursting

a. No Additional Coding, just need to create XML bursting File. ( Isn't it exciting , otherwise we would have to write java program to split the XML File and then use XMLP delivery manager API to send emails .
b. Process of Creating XML data Definition and Template remains AS – IS .

Prerequisites
a. Apply patch 5968876 if not already applied
b. Verify XMLP version is 5.6.3

Steps to Verify if XMLP is indeed 5.6.3

1. adident Header $OA_JAVA/oracle/apps/xdo/common/MetaInfo.class

Below matrix shows the the version you are and the patch to be applied to come to 5.6.3

Metainfo.class



Metainfo.class
XML Publisher
Patch Reference
120.6 XDO 5.6.3 Patch 4440000 
... for the Oracle E-Business Suite 11i   
115.28XDO 5.6.3 Patch 5472959 Note 422508.1
115.27XDO 5.6.2 (core) Patch 5097966 Note 367394.1
115.26 XDO 5.6.1Patch 4905678 Note 357308.1
115.24XDO 5.6.0 Patch 4561451Note 337999.1
115.22XDO 5.5.0 Patch 4206181 Note 316447.1
115.21XDO 5.0.1 (core) Patch 4236958  
115.19XDO 5.0.0 Patch 3822219 Note 295036.1
    



c. Set Temporary Directory in the XML Publisher Administrator- > Administration- > General to a directory to which you have write permission .

d. After the Patch is applied we will see a new Bursting control file button in the XML Publisher Data Definition Page
To check this, please go to Responsibility "XML Publisher Administrator", and search for any existing report, and click on Update. While updating an existing data-definition, you should see the button Bursting Control File.
Now since we have finished setting up , now we will see how to implement Bursting . The major component in this is bursting control file


What is a bursting file ?

Its an XML file which tells bursting engine


a) How to split the XML File created ?

b) How to deliver the Documents ?


Lets see a sample bursting file and its components


a) tag tells busting engine , bursting should be done at the occurrence of which tag ?
b) tag tells bursting engine , which delivery mechanism to be used and on what condition ?

Sample bursting file





xxp@xx.org.uk
" reply-to="xx@xx.org.uk">
Please review the attached PO ${POH_PO_NUM}









Please Note : While doing testing please change the "to address" to your email address , else it will send the documents out to that email address .
Data Definition
Create the Data Definition and Template the normal way, where the Short name of the data definition is same as the short name of concurrent program to which you are integrating XMLP.While creating Data Definition Upload you bursting XML File . If there are any parsing issues in the XML file it will give error , please correct the format of the file and try to upload again .


Template File
Create the Template and Upload your Template file



Concurrent Program Registeration

In the Concurrent Program give the Output fomat as XML and the Concurrent Program Short name should be same as the data definition name .


After Report Trigger Changes
Now the Final Step , in the After report Trigger of your .RDf add the following code to call bursting program . This will lanuch the bursting program from your report , otherwise you will have to run the Bursting program ( which is a Java Concurrent Program ) manually .


Now you are all set to do the testing , run the report , please keep in mind to change "to email address" in the bursting control file to your email id while doing the testing . you will see the PO pdf coming to the email address given in the bursting control file.

How to install new font using XML Publisher Administrator

any of you will have seen the Administrator module on the Template manager in the E Business Suite ... the new properties UI is great and much easier to use but you can now also manage those troublesome MICR, Barcode and other fonts through the manager too.

Thanks to Kevin McDermott from Oracle Support for laying out the following very useful instructions:

XML Publisher 5.6 has a new tab: Administration. This replaces the xdo.cfg configuration file. Now fonts can be uploaded and stored in the database instead of stored on the file system.

Under the Administration tab are sub tabs: Configuration, Font Mappings and Font Files and Currencies.

To install a font requires only a few steps.

1. Log in as XML Publisher Administrator.

2. Navigate to Administration->Font Files->Create Font File.

3. Fields are Font Name and File.
       For Font Name choose any descriptive name.
       File will browse your PC to locate the font file.

4. Navigate to Font Mappings->Create Font Mapping Set.

5. Mapping name is the name you will give to a set of fonts.

6. Mapping code is the internal name you will give to this set.

7. Type: 'PDF Form' for PDF templates. 'FO to PDF' for all other template types.

8. Create Font Mapping (this allows you to add multiple fonts to a set).

9. Font Family is the exact same name you see in Word under Fonts.
If you don't use the same name the font will not be picked up at runtime.

10. Style and weight must also match how you use the font in windows.
Normal and Normal are good defaults.

12. Navigate to Configuration General-> FO Processing->Font Mapping Set.
Can also be done at data def and template level under Edit Configuration.
Hierarchy is Site-> Data Def -> Template.

13. Select your new mapping set.

14. Make sure the font is not referenced under File->Properties->Custom in the RTF template file.

15. Upload a template that uses your special font and test using preview.

Excel Template for building BI Publisher Report

Excel Template Report:
Most of the people knows that XML Publisher Report can only be build using RTF File. However, latest version of BI Publisher 11.1.15 onward has flexibility to create XML Publisher Report in Excel too.
Advantages: a) If one of your client ask you to develop a XML Publisher report in multi-tab excel format, we generally say that it is not possible using XML Publisher. But now, it is possible using Excel Template Report.
b) If one of your client ask you to prepare a XML Publisher report where formulas should be visible as like in Excel,  we generally say that it is not possible using XML Publisher. But now, it is possible using Excel Template Report.
c) Splitting of Data into multiple tabs based on conditions
d) Split hierarchical data across multiple sheets and dynamically name the sheets
e) Create sheets of data that have master-detail relationships
f)  Use native Excel functionality
Disadvantages: a) RTF Report is easy to design in comparison with Excel Template Report.
b) RTF Report is easy to debug in comparison with Excel Template Report.
The steps
In order to design the template, we need a template builder for Excel which is installed automatically when you install the BI Publisher Desktop plug-in. Here are the 10 simple steps to create an XLS template.
1. Get the ‘BlankExcelTemplate’ from the sample Excel template which comes along with your desktop tool and save it with the name you prefer. XDO_METADATA sheet is required for BIP to process the template and add calculations. Hide this sheet before you attach the template.
sbblog_xlsbip1

2. Obtain the XML data file and identify the columns you want to display on your excel report.
3. To map the XML element in excel, we should enter the prefix XDO_? then followed by element name in the name box. For example, ‘ XDO_?Company_Name_ID12?’
4. Select the cell and enter the XML element in the name box with XDO_ prefix and enter the name of the cell in the formula bar to display text on the template then press enter.
sbblog_xlsbip2
5. Repeat the above step for remaining columns. After entering all columns, all the names will appear as shown below in Name Manger dialog box.
sbblog_xlsbip3

6. To add calculations for example, total the gross amount for each payment date, we have to define the name in the name box by selecting the cell. Total_Gross is the name defined here.
sbblog_xlsbip4

7. Once the name is defined for the calculation, add the calculation in the XDO_METADATA sheet as shown below.
sbblog_xlsbip5

8. To create group in the template like ‘for each’ in the word template, select the cells you want to repeat and enter the XML element name with the prefix XDO_GROUP_ in name box. In this example, On_Payment_ID_Internal is the level break header where we display one record for each payment.
sbblog_xlsbip6

9. In order to repeat the section for each payment date, select the rows 1, 2, 3 and 4 then enter the element name with prefix XDO_GROUP_. In the example, On_Check_Item_Date_S11 is the level break header.
sbblog_xlsbip7

10. XML file produces the date in YYYY-MM-DD format by default. In order to display the date in MM-DD-YYYY format, use the DATEVALUE function after mapping the original date element. In this example, date is mapped to column A (hidden) and used function to display the date based on column A.
sbblog_xlsbip8
Limitations:
  1. The template must be saved as Excel 97-2003 as *.xls.
  1. All tags must be manually coded, there is no tool available.
  1. Can’t burst the xls template.

XML Publisher Tips

1) ‘IF ELSE’ condition in XML publisher 
XML Publisher supports the common programming construct “if-then-else”. This is extremely useful when you need to test a condition and conditionally show a result. For example:
IF X=0 THEN
Y=2
ELSE
Y=3
END IF
You can also nest these statements as follows:
IF X=0 THEN
Y=2
ELSE
IF X=1 THEN
Y=10
ELSE Y=100
END IF
Use the following syntax to construct an if-then-else statement in your RTF template:
For example, the following statement tests the AMOUNT element value. If the value is greater than 1000, show the word “Higher”; if it is less than 1000, show the word “Lower”; if it is equal to 1000, show “Equal”:
1000 then 'Higher'
if AMOUNT < 1000 then 'Lower'
Else

'Equal'
end if?>







  2) How to get SYSDATE in the header section dynamically when we run the report
You cannot insert form fields in the Header section, but you can just insert the code to achieve this. For example: insert this in the header section to view the sysdate: You could format the date as you would like..

 3)  What are the XML publisher tables?
 PER_GB_XDO_TEMPLATES
XDO_DS_DEFINITIONS_B
XDO_DS_DEFINITIONS_TL
XDO_DS_DEFINITIONS_VL
XDO_LOBS
XDO_TEMPLATES_B
XDO_TEMPLATES_TL

XDO_TEMPLATES_VL
XDO_TEMPLATE_FIELDS
XDO_TRANS_UNITS
XDO_TRANS_UNIT_PROPS
XDO_TRANS_UNIT_VALUES
4) How to write a loop in rtf template design?
     ……………………..
5)  How to design sub templates in rtf layout?
Using following tags..
  
          This is Last Page
                            
6)  How to call a header or footer?
Using this tag
  and
    We have to use header section and footer section of the page.
7)  How to break the page in specific condition?
8)  How to use section break?
9)  How to create multi layouts in XMLP?
          
            Your template….
          
                   
                   Your template….
                   
                        
                         Your template….
                       
10) How to submit a layout in the backend?
we have to write a procedure for this using the below code
FND_REQUEST.ADD_LAYOUT                         (     TEMPLATE_APPL_NAME      => 'application name',
TEMPLATE_CODE           => 'your template code',
TEMPLATE_LANGUAGE       => 'En',
TEMPLATE_TERRITORY      => 'US',
OUTPUT_FORMAT           => 'PDF'
);
11) How to display the images in XMLP?
url:{'http://image location'}
                 For example, enter:
                   url:{'http://www.oracle.com/images/ora_log.gif'}
                      url:{'${OA_MEDIA}/image name'}
12) How to pass the page numbers in rtf layout?
200<\PAGESTART>
....
13)  Printing Report Parameters
In report, we need to print Report Parameters and might need to use it multiple times. To declare the parameter, create new field at beginning of the report with help text
. Create one more new field with help text to print wherever parameter needs to be printed.

For example, assume P_EMPLOYEE_NAME is one of the report parameter. To use that in RTF, create new filed named P_EMP_NAME with help text
. To print this parameter value create one more new field with name as help text.
14. Fit into single pageOracle Report Layout has “Fit to Page” property. If we set this to “Yes” then if the page is not enough to fit the content then entire content will be printed in next page. This is known and wildly used property. Is this possible in RTF Template?

Yes. First identify contents that need to fit into a page. Insert a table with only one row and column. Keep the identified contents inside this table. Select Table Property. Navigate to “Row” tab. Uncheck “Allow Row to break across Pages’ check box if it is checked. This will make sure the contents won’t split across pages.
15. Font Support
You might have noticed some time Report Output won’t match with RFT Template Font. This is because PDF Engine will support only specific fonts. If you use fonts that are not supported by PDF Engine then engine will use it is default font. So try to use fonts that are PDF engine supports.

Some time we have to use specific fonts that PDF engine didn’t support. Best example is company name, logo etc. Mostly we use these in report headers. Simple workaround is copy the logo or name or any text that we want it in specific font. Convert into to image and use that image wherever we need. Of course it will affect the performance if we use too many images in a template.